Database and PHP Reporting Page

The business requires an internal dashboard page to review the statistics from the web application. The statistics will be generated from the database as a series of views or stored procs. The dashboard should provide a simple single php page to query and display the data. - 21 views are described in the document. - The php page requires only the code backend as it will be integrated into a template at a later time. ## Deliverables Schema and create DB script are in the zip file. Here are the detailed requirements: Overview The business requires an internal dashboard page to review the statistics from the web application. The statistics will be generated from the database as a series of views or stored procs. The dashboard should provide a simple single php page to query and display the data. Requirements There are two main deliverables for this project. 1. A Series of Views/Stored Procs for the Database The views will all be done so that they can run in MySQL 5.x+ format. Each view must have the capability to select a start and end date. View would be preferable but if necessary we will accept stored procedures. In some cases it would seem to be easier to accomplish this using a simple query. However, because this database is much more involved than the information provided the view or stored procedures is a requirement. The database EER and demo create script is included in this package. There should be enough information to complete all the tasks. Unfortunately we cannot release any of the data for testing. The following views or procedures are required (italics indicate physical table name) broken up by area: Testers ID View or Procedure Description T1 Total Testers The count of the number of new testers rows over a date range T2 Total Handsets The count of regdevices over a date range T3 Total Networks The count of networks (No date range) T4 Total Countries The count of countries (No date range) T5 Average Daily New Testers The average number of new testers daily over a date range. T6 Average Weekly New Testers The average number of new testers per week over a date range. T7 Tester Summary The entire testers table (as a view, certain fields need to be removed later) Projects ID View or Procedure Description P1 Total Projects The count of the number of new projects rows over a date range P2 Count of Project Transactions by Status Transactions table has a status field. We will need a count of the total row counts for each status field. (See db image) P3 Average Transaction Status Per Project Average row counts for each status field by project. (Each project has multiple transactions and I would like to know what the average for each status is over all the projects) P4 Average Number of Tests Per Project Average of the count of tests for a project P5 Average Submitted Bid Per Project Average of the bidAmount in the transactions status=100 for a project (Average of the average bidAmount by project) P6 Average Accepted Bid Per Project Average of the bidAmount in the transactions status=200 (Average of the average bidAmount by project) P7 Project Summary The entire projects table (as a view, certain fields need to be removed later) Developers ID View or Procedure Description D1 Total Developers The count of the number of new developers rows over a date range D2 Average Daily New Developers The average number of new developers daily over a date range. D3 Average Weekly New Developers The average number of new developers per week over a date range. D4 Average Number of Tests Per Project Average of the count of tests for a project D5 Average Submitted Bid Per Project Average of the bidAmount in the transactions status=100 for a project (Average of the average bidAmount by project) D6 Average Accepted Bid Per Project Average of the bidAmount in the transactions status=200 (Average of the average bidAmount by project) D7 Developer Summary The entire developers table (as a view, certain fields need to be removed later) 2. A PHP Page to Query the Views The second deliverable is a php page that can query each of the views. The design is not as important as the functionality. We will be importing the php section into an existing template so only the functioning code is required. The code must be able to: 1. Select the start and end date/time for the queries. Each query will be required to filter based on the start and end date. 2. Compare the results over a specified time period. For the display of each query we need to compare those results against the same query from a different time period. The comparison will be done on a percentage basis. For example: Average New Testers Per day: 12 -3% Per week: 38 +4% Where the -3% and +4% represent the change over the time period. For 1&2 we will require start and end date selection boxes. 3. A method to export the data as csv. The T7,P7 and D7 queries are to be exported to a csv file based on the start and end date selected. The field names should be in the first row.