Administrator Recipe: Hours Breakdown

Although the Project Summary page is great for getting a bird's-eye view of 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 tells you the employee, job code, and number of hours for each time card. Need more detail? Select the employee name link to show 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.

    graphics/book.gif 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.

    graphics/book.gif 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.

    graphics/book.gif 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.

    graphics/book.gif 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 [r6-8].


    Figure r6-8.

    graphics/08fig08.jpg


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.

    graphics/book.gif Enter HoursBreakdown in the Name field.

  3. Select the desired data source connection.

    graphics/book.gif Choose Recipes from the Connection (Data Source) list.

  4. Choose the needed table.

    graphics/book.gif 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:

    graphics/441fig01.gif

  7. Set up the recordset to sort according to date, showing the oldest first.

    graphics/book.gif 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.

    graphics/book.gif Enter ProjectInfo in the Name field.

  3. Select the desired data source connection.

    graphics/book.gif Choose Recipes from the Connection (Data Source) list.

  4. Choose the needed table.

    graphics/book.gif From the Tables list, select Projects (projects for PHP).

  5. From the Columns option, choose Selected and select only the field you'll need.

    graphics/book.gif From the Columns list, choose ProjectHours.

  6. In the Filter area of the Recordset dialog, set the four Filter list elements like this:

    graphics/441fig01.gif

  7. Leave the Sort option set to None.

  8. Click OK when you're done.

  9. Save the page after the recordset is inserted.

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.

    graphics/book.gif From the Snippets panel, insert the Recipes > TimeCards > CustomCode_PHP > Report Hoursbreakdown - Temporary Table snippet.

    graphics/php.gif

    [View full width]

     <?php // ProjectTimeCards Temporary Table mysql_select_db($database_Cookbook_PHP, $Recipes); $sql = "DELETE FROM projecttimecards"; @mysql_query($sql,$Recipes); $sql = "INSERT INTO projecttimecards SELECT projects.*, graphics/ccc.gif timecard.*,jobtypes.JobCode FROM projects, jobtypes LEFT graphics/ccc.gif JOIN timecard ON projects.ProjectID=timecard.CardProject graphics/ccc.gif 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 graphics/ccc.gif.ProjectID, projecttimecards.ProjectName, projecttimecards graphics/ccc.gif.ProjectManager, projecttimecards.ProjectHours, jobtypes.JobRate*projecttimecards.CardHours AS graphics/ccc.gif BillableAmount, ROUND((TO_DAYS(projecttimecards.ProjectDate) graphics/ccc.gif - TO_DAYS(projecttimecards.CardDate))/7) AS WeeksIn, graphics/ccc.gif projecttimecards.CardID,projecttimecards.CardDate, graphics/ccc.gif projecttimecards.CardDescription, projecttimecards graphics/ccc.gif.CardEmployee, projecttimecards.CardLogged, projecttimecards graphics/ccc.gif.CardJobTypeID, jobtypes.JobCode, SUM(timecard.CardHours) AS graphics/ccc.gif TotalHoursIn, projecttimecards.CardHours, projecttimecards graphics/ccc.gif.CardHours AS HoursIn, projecttimecards.CardProject, CONCAT graphics/ccc.gif('$',FORMAT(jobtypes.JobRate,2)) AS JobRate FROM graphics/ccc.gif projecttimecards,jobtypes,timecard WHERE (projecttimecards graphics/ccc.gif.CardJobTypeID = jobtypes.JobTypeID AND timecard.CardProject graphics/ccc.gif = projecttimecards.ProjectID) OR (projecttimecards.CardJobTypeID = 0) GROUP BY graphics/ccc.gif projecttimecards.ProjectID ORDER BY projecttimecards.CardID graphics/ccc.gif 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.

    graphics/book.gif Select the placeholder [ProjectName] and drag ProjectName over it.

    Skip the Budgeted Hours cell for now; we'll add that in a moment.

    graphics/book.gif 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.

    graphics/book.gif Drag ProjectHours into the cell next to the Budgeted Hours label.

  5. When you're done, save the page [r6-9].


    Figure r6-9.

    graphics/08fig09.jpg


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.

    graphics/book.gif Choose HoursBreakdown.CardEmployee in the content table.

  2. Select the folder symbol 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.

    graphics/book.gif Enter ID in the Name column.

  6. In the Value column, enter the dynamic value of the current project's ID.

    graphics/book.gif Select the lightning bolt 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.

    graphics/book.gif 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.

    graphics/book.gif 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.

    graphics/book.gif 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 desired recordset.

    graphics/book.gif Choose HoursBreakdown from the Recordset list.

  5. Set the Show option to display however many records you'd like.

    graphics/book.gif 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 [r6-10].


Figure r6-10.

graphics/08fig10.jpg




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