Database and PHP Reporting Page(repost)

Completed Posted Jun 15, 2009 Paid on delivery
Completed Paid on delivery

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.

Engineering MySQL PHP Software Architecture Software Testing

Project ID: #3940986

About the project

11 proposals Remote project Active Jun 16, 2009

Awarded to:

boratahirvw

See private message.

$153 USD in 14 days
(24 Reviews)
4.0

11 freelancers are bidding on average $139 for this job

maxmagento

See private message.

$170 USD in 14 days
(63 Reviews)
6.0
salmanakramvw

See private message.

$85 USD in 14 days
(29 Reviews)
4.2
pranz

See private message.

$144.5 USD in 14 days
(6 Reviews)
2.6
rungxanh252000

See private message.

$170 USD in 14 days
(12 Reviews)
2.6
rohanbhagat83

See private message.

$148.75 USD in 14 days
(8 Reviews)
2.2
makramvw

See private message.

$170 USD in 14 days
(1 Review)
2.0
ChirilaGeorgeSB

See private message.

$85 USD in 14 days
(3 Reviews)
0.0
vw7254142vw

See private message.

$136 USD in 14 days
(6 Reviews)
0.0
abhishekpro

See private message.

$144.5 USD in 14 days
(0 Reviews)
0.0
tlass

See private message.

$127.5 USD in 14 days
(0 Reviews)
0.0