Database and PHP Reporting Page

Cancelled Posted Jun 9, 2009 Paid on delivery
Cancelled 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: #3932021

About the project

16 proposals Remote project Active Jun 15, 2009

16 freelancers are bidding on average $138 for this job

vw1621783vw

See private message.

$85 USD in 14 days
(187 Reviews)
6.0
tiborveres

See private message.

$170 USD in 14 days
(28 Reviews)
5.5
landrum

See private message.

$144.5 USD in 14 days
(38 Reviews)
4.4
salmanakramvw

See private message.

$93.5 USD in 14 days
(29 Reviews)
4.2
BearSoftware

See private message.

$170 USD in 14 days
(22 Reviews)
4.0
adhd

See private message.

$170 USD in 14 days
(15 Reviews)
4.0
coderstanislavvw

See private message.

$136 USD in 14 days
(13 Reviews)
3.9
DoubleBit

See private message.

$161.5 USD in 14 days
(7 Reviews)
3.6
ronitoberoy87

See private message.

$170 USD in 14 days
(5 Reviews)
2.6
prabhum10vw

See private message.

$140.25 USD in 14 days
(0 Reviews)
0.0
grobert9

See private message.

$102 USD in 14 days
(0 Reviews)
0.0
ibercea

See private message.

$85 USD in 14 days
(0 Reviews)
0.0
EngrNoel

See private message.

$150.45 USD in 14 days
(0 Reviews)
0.0
ideafoundation

See private message.

$170 USD in 14 days
(1 Review)
0.0
gandhipurav

See private message.

$157.25 USD in 14 days
(0 Reviews)
0.0
ayseda

See private message.

$102 USD in 14 days
(0 Reviews)
0.0