There are 6 parts to this project:
I have a requirement to develop an admin application (PHP) and to access and manipulate a Pre Porduction MySQL database that drives product prices on our website.
A robot to query prices on competitor websites and populate results in the Pre Prod MySQL database
Buinsess logic and formula transferred from excel spreadsheet to Pre Prod MySQL database
Publish utility that transfers all data from Pre Prod to live database
Test Scripts to ensure all data is good and web output is correct
Migration (If necessary) and implementation
Currently our website is pre production but we wish to go live in the next 2 weeks. We currently use an excel spreadsheet to enter all of our product information, including sales prices, competitor prices etc. There is then some simple formula that calculates the price of our products. We currently save the spreadsheet as CSV and use an upload utility to populate all of our online products.
Business Logic transfer and Admin Application
We have multiple staff constantly updating data in the spreadsheet as sales prices, costs etc change on a daily basis. Currently the spreadsheet performs all the logic which we then upload onto the website daily to display all of our prices and product information. We want all the staff to access a database directly via the new application in order to enter data into the database. Some staff need to access the selling price fields (which drive our prices) and we also need to be able to make small changes to the logic using the admin application.
Multiple levels of security is required, some staff just need access to the sales price field, some to change the logic/formula and the sales price and an admin user who can change everything and also still upload CSV file to overwrite the entire database if necessary.
All the business logic and formula currently done by the spreadsheet needs to be performed directly by the database so all this needs to be developed in MySQL.
Finally we require a utility that will publish all the relevant data to the live website database
We currently manually check approx 8 competitor sites for their prices which we enter into our pricing spreadsheet. These numbers are also used in the formula that drive our online prices. We need a robot to automatically query the websites and populate the Pre Prod database with the results.
Test Scripts & Backup
We need to have confidence that the prices shown on our website following all the changes above are always correct . We need some simple error checking scripts/routines that constantly run and alert if anything doesn’t look right! We will need the supplier (you) to figure out the necessary logic and write and implement script. We also require a simple script that we can run or schedule daily to back up the entire database (live and pre prod) and a mechanism to restore if we need to.
We currently host the site on a hosting package from a large ISP. If because of this new functionality we need to move the site to a dedicated server or a new platform, the migration of current website and implementation of new functionality is part of the requirement for this project.