For this project you are to adapt the HBInventory application to work on a different database: Salesdb. The Sales database contains customers and sales data for various products provided. The script...

For this project you are to adapt the HBInventory application to work on a different database: Salesdb.

The Sales database contains customers and sales data for various products provided. The script for building the database inside MySQL is included with the project. As with HBInventory, the application will have a selection page for customer, and a results page showing sales. To complete the assignment with full credit you must also use ajax (as with HBInventory).

IS265 – Scripting Technologies IS265 – Scripting Technologies Final Project #2: Sales DB 2021 For this project you are to adapt the HBInventory application to work on a different database: Salesdb. The Sales database contains customers and sales data for various products provided. The script for building the database inside MySQL is included with the project. As with HBInventory, the application will have a selection page for customer, and a results page showing sales. To complete the assignment with full credit you must also use ajax (as with HBInventory). The first page of the application will allow the user to select a customer from an initial drop-down list, and then press ‘go’ in order to see all sales on file for that customer. As shown below: Note that the entries in this drop-down list are read from the customer table of the database. The value assigned to each drop-down entry is the Customer_ID field from that table. When the GO button is pressed the selection page should submit itself to another PHP page. This second PHP page will receive the Customer ID and run SQL queries against the database to pull both the full customer information and also the sales data for that customer. Sales data comes from the sales_order table. The selected data (if shown on a separate page) would be: When ajax is implemented, the final result would look like this: Note that it is also possible that a paricular customer has no sales on file. Such a result would be: The SQL for the main page is just a simple select of all records in the Customer table returned in name order: SELECT * FROM customer ORDER BY NAME Then, in the top part of the second page, you select the specific customer chosen by the user for review; thus, the SQL for that operation is: SELECT * FROM customer WHERE CUSTOMER_ID = ‘customer id’ Note that the above SQL statement the ‘customer id’ value must be replaced by the actual Customer ID that is the value entry of the selected drop-down item (from the first page). Please reflect on this important difference between the Sales application and what was done in HBInventory: the customer id in this database is alphanumeric, (in HenryBooks the value sent to the second page was numeric). Because of this you must have the single apostrophes around the customer id value (as shown above), and the handling of the value in the PHP code will be slightly different from HBInventory. So, as an example, the SQL command built looking for data for Century Shop would be: SELECT * FROM customer WHERE CUSTOMER_ID = ‘C226’ In the sales list you also show the transactions which come from the sales_order table in the database, again based on the specific customer id. The transactions are shown sorted by sales date, but with the most recent sales first. The SQL for this operation is: SELECT * FROM sales_order WHERE customer_id = 'customer id' ORDER BY order_date DESC Again, the ‘customer id’ entry shown above would be replaced by the actual ID value of the selected customer and would include the apostrophes. Recall that you use the “\.” (backslash-dot) command inside the MySQL command window in order to run a script (to load a database). So if I had the SalesDB.sql script loaded in the C:/IS265 folder on my virtual machine, the command inside the MySQL command window would be: \. C:\is265\salesdb.sql Extra Credit (5 pts): (5 points) You will note in the example above that the sales amount output is formatted to 2 decimal places and has a $ in front. This formatting is not required for the basic program (although the right-justification in the table field is required). For 5 extra credit points, provide the formatting as shown in the example. (Hint: this is a PHP format operation; you can’t just put a literal $ in front of the value you display).