Recipe Overview: Data Source Tables and Queries

The Time Cards recipe is somewhat different from the others in this book. Many of the application pages, especially those displaying time card summaries, are calculation intensive. Although it's possible to include the required complex SQL statements on the Web page, it's far more efficient and far less error-prone to construct and execute the SQL within the database itself.

The Time Cards recipe follows the approach of keeping the complex SQL in the data source. Consequently, creating the actual application pages is fairly straightforward with a minimum of hand-coding required. However, to truly understand how the recipe works, we'll need to take a little time to examine the component parts of this application: the data source tables and the queries.

As noted in the "Ingredients" section, this recipe uses four tables: TimeCard, Projects, Employees, and JobTypes. Of these four, only the TimeCard table is specific to this application; the other tables are also used elsewhere in other recipes. The Employees and JobType tables are used to create a new time card, thus ensuring that only current employees and valid job codes are used. The Projects table tracks projects, managers, and hours, both estimated and final.

Much of the power found in the Time Cards recipe is derived from the six interrelated SQL queries used in the application. One query, ProjectTimeCards, creates a virtual table by combining data from two tables, Projects and TimeCard. The ProjectTimeCards query relies on the SQL operation LEFT JOIN. As you might recall from SQL-related discussions in other recipes, a JOIN gathers data from two SQL objects (such as tables or views) to create a new recordset based on some criteria. A LEFT JOIN guarantees that all the records from the first table (the one on the left side of the SQL statement) are included regardless of the criteria. Thus, the ProjectTimeCards query for ASP and ColdFusion is as follows:

 
 SELECT Projects.*, TimeCard.* FROM Projects LEFT JOIN TimeCard ON Projects.ProjectID = TimeCard.CardProject; 
 

For MySQL we use the following:

 
 SELECT projects.*, timecard.*,jobtypes.JobCode FROM projects, jobtypes LEFT JOIN timecard ON projects.ProjectID=timecard.CardProject WHERE timecard.CardJobTypeID = jobtypes.JobTypeID ORDER BY ProjectID 
 

These SQL statements will include all the records from the Projects table as well as the records from the TimeCard table where there is a matching project. This is done so that new time cards can be assigned to any project.

Note

You can find a discussion of a similar SQL operation, the RIGHT JOIN, in Recipe 4, "In/Out Dashboard."


The most complex SQL query in the recipe and the basis for the other four queries used relies on the ProjectSummary query. The HoursBreakDown SQL statement is referred to as "a query of a query" because it combines the results of the ProjectSummary query and the JobTypes table. Again, a LEFT JOIN is used to get all the records from the ProjectSummary query and only the designated ones from the JobTypes table, as you can see by examining the FROM clause of the SQL statement used in ASP and ColdFusion:

 
 SELECT ProjectTimeCards.ProjectID, ProjectTimeCards.ProjectName, ProjectTimeCards.ProjectManager, ProjectTimeCards.ProjectHours, Sum([JobTypes].[JobRate]*[ProjectTimeCards].[CardHours]) AS BillableAmount, ProjectTimeCards.CardDate, ProjectTimeCards.CardDescription, ProjectTimeCards.CardEmployee, ProjectTimeCards.CardLogged, JobTypes.JobCode, (SELECT Sum(TimeCard2.CardHours) FROM TimeCard AS TimeCard2 WHERE TimeCard2.CardProject = ProjectTimeCards.ProjectID) AS TotalHoursIn, ProjectTimeCards.CardHours, ProjectTimeCards.CardProject, ProjectTimeCards.CardID, DateDiff('w',[ProjectDate],[CardDate]) AS WeeksIn, Sum(ProjectTimeCards.CardHours) AS HoursIn, JobTypes.JobRate FROM ProjectTimeCards LEFT JOIN JobTypes ON ProjectTimeCards.CardJobTypeID = JobTypes.JobTypeID GROUP BY ProjectTimeCards.ProjectID, ProjectTimeCards.ProjectName, ProjectTimeCards.ProjectManager, ProjectTimeCards.ProjectHours, ProjectTimeCards.CardDate, ProjectTimeCards.CardDescription, ProjectTimeCards.CardEmployee, ProjectTimeCards.CardLogged, JobTypes.JobCode, ProjectTimeCards.CardHours, ProjectTimeCards.CardProject, ProjectTimeCards.CardID, DateDiff('w',[ProjectDate],[CardDate]), JobTypes.JobRate ORDER BY ProjectTimeCards.CardID DESC; 
 

You might note that the SELECT clause is one of the most complex used in this book. In addition to the calculated fields like BillableAmount and WeeksIn, a nested SQL statement is also included to create the TotalHoursIn field.

The SQL statements for MySQL are similar but slightly more complex because MySQL has no support for stored queries or views. For this reason, we need to use a custom PHP code block to populate temporary tables that simulate views for our needs. We do this using three SQL statements. The first simulates the ProjectTimeCards query:

 
 DELETE FROM projecttimecards; INSERT INTO projecttimecards SELECT projects.*, timecard.*,jobtypes.JobCode FROM projects, jobtypes LEFT JOIN timecard ON projects.ProjectID=timecard.CardProject WHERE timecard.CardJobTypeID = jobtypes.JobTypeID ORDER BY ProjectID 
 

Here's the PHP code needed to simulate the HoursBreakDown query:

 
 INSERT INTO hoursbreakdown SELECT projecttimecards.ProjectID, projecttimecards.ProjectName, projecttimecards.ProjectManager, projecttimecards.ProjectHours, Sum(jobtypes.JobRate*projecttimecards.CardHours) AS BillableAmount, ROUND((TO_DAYS(projecttimecards.ProjectDate) - TO_DAYS(projecttimecards.CardDate))/7) AS WeeksIn, projecttimecards.CardDate, projecttimecards.CardDescription, projecttimecards.CardEmployee, projecttimecards.CardLogged, jobtypes.JobCode, SUM(timecard.CardHours) AS TotalHoursIn, projecttimecards.CardHours, projecttimecards.CardHours AS HoursIn, projecttimecards.CardProject, CONCAT('$', FORMAT(jobtypes.JobRate,2)) AS JobRate FROM projecttimecards LEFT JOIN jobtypes ON projecttimecards.CardJobTypeID = jobtypes.JobTypeID INNER JOIN timecard ON timecard.CardProject = projecttimecards.ProjectID GROUP BY projecttimecards.ProjectID, projecttimecards.ProjectName, projecttimecards.ProjectManager, projecttimecards.ProjectHours, projecttimecards.CardDate, projecttimecards.CardDescription, projecttimecards.CardEmployee, projecttimecards.CardLogged, jobtypes.JobCode, projecttimecards.CardHours, projecttimecards.CardProject, projecttimecards.CardID, WeeksIn, jobtypes.JobRate HAVING BillableAmount IS NOT NULL ORDER BY projecttimecards.CardID DESC 
 

And finally, to simulate the ProjectSummary view:

 
 INSERT INTO projectsummary SELECT Sum(HoursBreakdown.CardHours) AS ProjectHoursUsed, Sum(HoursBreakdown.BillableAmount) AS ProjectDue, HoursBreakdown.ProjectHours, HoursBreakdown.ProjectManager, HoursBreakdown.ProjectName, HoursBreakdown.ProjectID FROM HoursBreakdown GROUP BY HoursBreakdown.ProjectHours, HoursBreakdown.ProjectManager, HoursBreakdown.ProjectName, HoursBreakdown.ProjectID ORDER BY HoursBreakdown.ProjectID; 
 

The other four queries referenced in this recipe ProjectSummary, DailyHours, WeeklyHours, and MonthlyHours are all based on the HoursBreakdown query. The ProjectSummary query, as the name implies, summarizes data calculated in HoursBreakdown. Similarly, the DailyHours, WeeklyHours, and MonthlyHours queries each extract only the information needed for daily, weekly, and monthly reports, respectively.



Macromedia Dreamweaver MX 2004 Web Application Recipes
Macromedia Dreamweaver MX 2004 Web Application Recipes
ISBN: 0735713200
EAN: 2147483647
Year: 2003
Pages: 131

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net