| Although the Project Summary page is great for getting a bird's-eye view of the hours spent on all of your projects, it's also helpful to be able to see all the details for a specific one. The Hours Breakdown page displays a table of all the time cards logged for a single project. With this page, just a glance shows you the employee, job code, and number of hours for each time card. Need more detail? Select the employee name link to reveal the full time card record. Step 1: Implement Hours Breakdown Design We want to accomplish two main goals with this layout. First, we want to display a summary of the hours budgeted and those used. Although this information is available in the Project Summary page, it's helpful to have it repeated on this page. Second, we need to show information for all of a project's time cards. The layout handles both requirements by combining a static area for the hours summary and a repeating region for the time card list. 1. | In the server model of your choice, create a dynamic page.
 In the TimeCards folder, locate the folder for your server model and open the report_hoursbreakdown page from there.
| 2. | Add a table to the content region of your page to contain the form elements for the application.
From the Snippets panel, drag the Recipes > TimeCards > Wireframes > Hours Breakdown Report - Wireframe snippet into the Content editable region.
| 3. | Add a table structure to allow for separation between repeating rows.
From the Snippets panel, drag the Recipes > TimeCards > ContentTables > Hours Breakdown Shell - Content Table snippet into the first row beneath the words HOURS BREAKDOWN REPORT.
| 4. | Insert a three-row-by-three-column table, where the first row is used to title the table (Hours Breakdown), the second row to hold the labels (Employee Name, Job Code, and Hours), and the third to hold the dynamic data.
Place your cursor in the bottom row of the just-added snippet and insert the Recipes > TimeCards > ContentTables > Hours Breakdown Report - Content Table snippet.
| 5. | Save the file [r5-8].
r5-8. 
|
For PHP PHP users need to add functions to convert the date data into the proper format. This is handled in two lines of code which first convert the value entered in the TaskDate form field to a time format and then applies PHP's date function to massage that value into the proper format. 1. | In Code view, place your cursor at the top of the page, just after the <!--include > connection statement.
| 2. | Insert the following code:
 From the Snippets panel, insert the Recipes > TimeCards > CustomCode_PHP > Add Timecard - Convert Date snippet.
<?php if (isset($_POST["TaskDate"])) { $_POST['TaskDate'] = strtotime($_POST['TaskDate']); $_POST['TaskDate'] = date("YmdHis",$_POST['TaskDate']); } ?>
| 3. | Save your page.
|
Step 2: Add Database Components You'll recall from the beginning of this chapter that the HoursBreakdown SQL statement is among the more complex. However, because all of the required calculations are already handled in the database, creating the recordset for this page is very easy. In addition to the HoursBreakdown recordset, which will be used to populate most of the dynamic text on the page, a second recordset is needed. It's possible for the HoursBreakdown recordset to return no records, if no time cards have been entered for a given project. In that situation, we still want to show the budgeted hours, and we'll get that value from another recordset that relies on the Project table. As with the Project Summary page, PHP users will need to add some custom code to simulate the views and queries. Let's start with the HoursBreakdown recordset: 1. | From the Bindings panel, choose Add (+) and select Recordset (Query).
| 2. | In the simple Recordset dialog, enter an appropriate name.
Enter HoursBreakdown in the Name field.
| 3. | Select the data source connection.
Choose Recipes from the Connection (Data Source) list.
| 4. | Choose the needed table.
From the Tables list, select HoursBreakdown (hoursbreakdown for PHP).
| 5. | Leave the Columns option set to All.
| 6. | In the Filter area of the Recordset dialog, set the four Filter list elements like this:
ProjectID | = (Equals) | URL Parameter | ID |
| 7. | Set up the recordset to sort according to date, showing the oldest first.
In the Sort area, choose CardDate from the first list and Ascending from the second.
| 8. | Save the page after the recordset is inserted.
|
Now we'll insert our single entry recordset to get the budgeted hours: 1. | From the Bindings panel, choose Add (+) and select Recordset (Query).
| 2. | In the simple Recordset dialog, enter an appropriate name.
Enter ProjectInfo in the Name field.
| 3. | Select the data source connection.
Choose Recipes from the Connection (Data Source) list.
| 4. | Choose the needed table.
From the Tables list, select Projects (projects for PHP).
| 5. | From the Columns option, choose Selected and select only the field you'll need.
From the Columns list, choose ProjectHours.
| 6. | In the Filter area of the Recordset dialog, set the four Filter list elements like this:
ProjectID | = (Equals) | URL Parameter | ID |
| 7. | Leave the Sort option set to None.
| 8. | Click OK when you're done.
| 9. | Save the page after the recordset is inserted.
|
For PHP The following is for PHP users only: 1. | In Code view, place your cursor near the top of the page, after the line that begins <?php require_once, and insert the following code.
From the Snippets panel, insert the Recipes > TimeCards > CustomCode_PHP > Report Hoursbreakdown - Temporary Table snippet.
| [View full width] <?php // ProjectTimeCards Temporary Table mysql_select_db($database_Recipes, $Recipes); $sql = "DELETE FROM projecttimecards"; @mysql_query($sql,$Recipes); $sql = "INSERT INTO projecttimecards SELECT  projects.*, timecard.*,jobtypes.JobCode FROM projects, jobtypes LEFT JOIN timecard ON projects .ProjectID=timecard.CardProject GROUP BY ProjectID ORDER BY ProjectID"; @mysql_query($sql,$Recipes); // Hours Breakdown Temporary Table $sql = "DELETE FROM hoursbreakdown"; @mysql_query($sql,$Recipes); $sql = "INSERT INTO hoursbreakdown SELECT projecttimecards.ProjectID, projecttimecards .ProjectName, p rojecttimecards.ProjectManager, projecttimecards.ProjectHours, jobtypes .JobRate*projecttimecards.CardHours AS BillableAmount, ROUND((TO_DAYS(projecttimecards.ProjectDate) - TO_DAYS (projecttimecards.CardDate))/7) AS WeeksIn , projecttimecards.CardID,projecttimecards.CardDate , projecttimecards.CardDescription, projecttimecards.CardEmployee, projecttimecards .CardLogged, projecttimecards.CardJobTypeID, jobtypes.JobCode, SUM(timecard.CardHours) AS TotalHoursIn, projecttimecards.CardHours, projecttimecards.CardHours AS HoursIn, projecttimecards.CardProject, CONCAT('$',FORMAT (jobtypes.JobRate,2)) AS JobRate FROM projecttimecards,jobtypes,timecard WHERE (projecttimecards.CardJobTypeID = jobtypes .JobTypeID AND timecard.CardProject = projecttimecards.ProjectID) OR (projecttimecards .CardJobTypeID = 0) GROUP BY projecttimecards .ProjectID ORDER BY projecttimecards.CardID DESC"; @mysql_query($sql,$Recipes); ?>
|
| 2. | Save your page.
|
Step 3: Data Binding Process It's time to make use of the recordsets we created. In addition to putting data in the content table, we'll also bind the project name to the heading at the top of the page to make it absolutely clear which project is being covered. 1. | From the Bindings panel, expand the HoursBreakdown recordset.
| 2. | Place the data fields on the page in their proper places.
Select the placeholder [ProjectName] and drag ProjectName over it. Skip the Budgeted Hours cell for now; we'll add that in a moment.
| Drag TotalHoursIn into the cell next to the Hours Used label. | | | Drag CardEmployee into the cell under the Employee Name label. | | | Drag JobCode into the cell under the Job Code label. | | | Drag CardHours into the cell under the Hours label. |
| 3. | Expand the ProjectInfo recordset.
| 4. | Place the data field from this recordset in the appropriate place.
Drag ProjectHours into the cell next to the Budgeted Hours label.
| 5. | When you're done, save the page [r5-9].
r5-9. 
|
Step 4: Link to Time Card Details Page This recipe calls for making another level of detail available: the time card record. To reach this information, generated on the task_detail page, we'll add a link to the employee name. 1. | Select the text or image you want to link to the detail page.
Choose HoursBreakdown.CardEmployee in the content table.
| 2. | Click the folder icon next to the Link field in the Property inspector.
The Select File dialog opens.
| 3. | Make sure the dialog is set to Select File Name From File System.
| 4. | Choose Parameters at the bottom of the dialog.
| 5. | In the Name column of the Parameters dialog, enter the variable name.
Enter ID in the Name column.
| 6. | In the Value column, enter the dynamic value of the current project's ID.
Click the lightning bolt icon in the Value column and, from the Dynamic Data dialog, expand the HoursBreakdown recordset and choose CardID. When you're done, click OK once to close the Dynamic Data dialog, and again to close the Parameters dialog.
| 7. | In the Select File dialog, select the file that will be used to display the details of hours logged.
Choose task_detail in the TimeCards folder for your server model.
| 8. | When you're done, click OK to insert the link.
|
Step 5: Adding Repeat Region To complete the Hours Breakdown page, we'll add a Repeat Region server behavior to the content table. 1. | Select any of the dynamic data fields in the content table.
Choose the CardEmployee data field from the Hours Breakdown content table.
| 2. | From the Tag Selector, choose the appropriate table row tag that encompasses the entire table.
Select the <tr> tag to the left of the selected tag in the Tag Selector.
| 3. | From the Server Behaviors panel, choose Add (+) and select Repeat Region.
| 4. | In the Repeat Region dialog, choose the recordset.
Choose HoursBreakdown from the Recordset list.
| 5. | Set the Show option to display however many records you'd like.
Choose Show All Records.
| 6. | Click OK when you're done.
| 7. | Save your page.
|
Test your page in the browser or in Live Data view in Dreamweaver [r5-10]. r5-10. 
|
