Administrator Recipe: View Time Cards

Up to this point, our application overviews have been project-oriented. With the report_projectsummary page, you can get a bird's-eye view of all the available projects. The report_hoursbreakdown page offers a more detailed view of the time cards for a specified project. In this recipe, we'll build a page that shows all the time cards logged for all the projects, grouped by employee and displayed in daily, weekly, and monthly views.

Because we are using three different views of the same basic data, the construction of this page will be a bit repetitive. We'll need three recordsets to bind data into three different content tables. When that is done, we'll have to apply three Repeat Region server behaviors. It's important to realize that all the calculations needed for these three different summaries daily, weekly, and monthly are all handled through their corresponding SQL queries executed in the data source. Each of the queries are, as noted at the start of this chapter, based on another SQL query, HoursBreakdown.

Step 1: Implement View Time Cards Design

Each of the time-based views of data requires its own content area in the layout for this recipe. Although they all have essentially the same information (employee, project, date, amount of time, and billable cost), they need to be contained in separate structures to use the Repeat Region server behavior.

  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 view_timecards page from there.

  2. Add a table to the content region of your page to hold the content tables and data for the application.

    graphics/book.gif From the Snippets panel, drag the Recipes > TimeCards > Wireframes > View Time Cards - Wireframe snippet into the Content editable region.

  3. Add a table structure to allow for separation between the three separate sections.

    graphics/book.gif From the Snippets panel, drag the Recipes > TimeCards > ContentTables > View Time Cards - Shell snippet into the row beneath the words VIEW TIME CARDS.

  4. Insert three tables, each consisting of three rows and five columns. The first row is used to designate the focus of the table (Daily, Weekly, or Monthly), and the cells in the second row act as headers for the data: Employee Name, Project, Date, Hours, and Cost. The final row will hold the data itself.

    graphics/book.gif Place your cursor in the top row of the shell table and insert the Recipes > TimeCards > ContentTables > View Time Cards - Daily snippet.

    Place your cursor in the middle row of the shell table and insert the Recipes > TimeCards > ContentTables > View Time Cards - Weekly snippet.

    Place your cursor in the bottom row of the shell table and insert the Recipes > TimeCards > ContentTables > View Time Cards - Monthly snippet [r6-14].


    Figure r6-14.

    graphics/08fig14.jpg


  5. Save the file.

Step 2: Add Database Components

The three recordsets used in this recipe are very straightforward to implement. All the hard calculation work has been done within the data source itself. Each recordset relies on a different SQL view of summarized information created in the data source. The three views DailyHours, WeeklyHours, and MonthlyHours are all similarly structured. Here's the DailyHours SQL statement:

 
 SELECT HoursBreakdown.CardEmployee, Sum(HoursBreakdown.CardHours) AS Hours, Format([HoursBreakdown].[CardDate],'ddd mmm dd') AS [Day], Sum(HoursBreakdown.BillableAmount) AS BillableAmount, HoursBreakdown.ProjectName FROM HoursBreakdown WHERE (((HoursBreakdown.CardDate)>Date()-30)) GROUP BY HoursBreakdown.CardEmployee, Format([HoursBreakdown].[CardDate],'ddd mmm dd'), HoursBreakdown.ProjectName, DatePart('yyyy',[CardDate]), DatePart('m',[CardDate]), DatePart('d',[CardDate]) ORDER BY DatePart('yyyy',[CardDate]), DatePart('m',[CardDate]), DatePart('d',[CardDate]), HoursBreakdown.CardEmployee; 
 

The MySQL version is similar:

 
 SELECT hoursbreakdown.CardEmployee, Sum(hoursbreakdown.CardHours) AS Hours, DATE_FORMAT(hoursbreakdown.CardDate,"%a %b %d") AS TheDay, Sum(hoursbreakdown.BillableAmount) AS BillableAmount, hoursbreakdown.ProjectName FROM hoursbreakdown WHERE (((hoursbreakdown.CardDate)>DATE_SUB(CURDATE(), INTERVAL 30 DAY))) GROUP BY hoursbreakdown.CardEmployee, TheDay, hoursbreakdown.ProjectName, DATE_FORMAT(hoursbreakdown.CardDate,"%Y"), DATE_FORMAT(hoursbreakdown.CardDate,"%m"), DATE_FORMAT(hoursbreakdown.CardDate,"%d") ORDER BY DATE_FORMAT(hoursbreakdown.CardDate,"%Y"), DATE_FORMAT(hoursbreakdown.CardDate,"%m"), DATE_FORMAT(hoursbreakdown.CardDate,"%d"), hoursbreakdown.CardEmployee 
 

All three SQL views are based on the HoursBreakdown query; PHP will again require some custom code to simulate the queries.

Dreamweaver displays recordsets in the order in which they are defined, so let's start with the daily view.

  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 DailyHours 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 DailyHours.

  5. Leave the Columns option set to All.

  6. Make sure the Filter is set to None.

  7. Keep the Sort option set to None and click OK to close the dialog.

  8. Save the page after the recordset is inserted.

Next we'll create the recordset to hold the weekly data.

  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 WeeklyHours 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 WeeklyHours.

  5. Leave the Columns option set to All.

  6. Make sure the Filter is set to None.

  7. Keep the Sort option set to None and click OK to close the dialog.

  8. Save the page after the recordset is inserted.

Finally, let's build the monthly hours 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 MonthlyHours 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 MonthlyHours.

  5. Leave the Columns option set to All.

  6. Make sure the Filter is set to None.

  7. Keep the Sort option set to None and click OK to close the dialog.

  8. Save the page after the recordset is inserted.

As with several other pages in this recipe, additional code is required for PHP:

  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 graphics/ccc.gif.CardJobTypeID = 0) GROUP BY projecttimecards.ProjectID ORDER graphics/ccc.gif BY projecttimecards.CardID DESC"; @mysql_query($sql,$Recipes); // Daily Hours Temporary Table $sql = "DELETE FROM dailyhours"; @mysql_query($sql,$Recipes); $sql = 'INSERT INTO dailyhours SELECT hoursbreakdown graphics/ccc.gif.CardEmployee, Sum(hoursbreakdown.CardHours) AS Hours, graphics/ccc.gif DATE_FORMAT(hoursbreakdown.CardDate,"%a %b %d") AS TheDay, graphics/ccc.gif Sum(hoursbreakdown.BillableAmount) AS BillableAmount, graphics/ccc.gif hoursbreakdown.ProjectName FROM hoursbreakdown WHERE (( graphics/ccc.gif(hoursbreakdown.CardDate)> DATE_SUB(CURDATE(), INTERVAL 30 graphics/ccc.gif DAY))) GROUP BY hoursbreakdown.CardEmployee, TheDay, graphics/ccc.gif hoursbreakdown.ProjectName, DATE_FORMAT(hoursbreakdown graphics/ccc.gif.CardDate,"%Y"), DATE_FORMAT(hoursbreakdown.CardDate,"%m"), graphics/ccc.gif DATE_FORMAT(hoursbreakdown.CardDate,"%d") ORDER BY graphics/ccc.gif DATE_FORMAT(hoursbreakdown.CardDate,"%Y"), DATE_FORMAT graphics/ccc.gif(hoursbreakdown.CardDate,"%m"), DATE_FORMAT(hoursbreakdown graphics/ccc.gif.CardDate,"%d"), hoursbreakdown.CardEmployee'; @mysql_query($sql,$Recipes); // Weekly Hours Temporary Table $sql = "DELETE FROM weeklyhours"; @mysql_query($sql,$Recipes); $sql = 'INSERT INTO weeklyhours SELECT hoursbreakdown graphics/ccc.gif.CardEmployee, Sum(hoursbreakdown.CardHours) AS Hours, CONCAT graphics/ccc.gif(DATE_FORMAT(DATE_ADD(hoursbreakdown.CardDate, INTERVAL (-1 * graphics/ccc.gif WEEKDAY(NOW())) DAY),"%m/%d-"), DATE_FORMAT(DATE_ADD graphics/ccc.gif(hoursbreakdown.CardDate, INTERVAL (7 - WEEKDAY(NOW())) DAY) graphics/ccc.gif,"%m/%d "), DATE_FORMAT(DATE_ADD(hoursbreakdown.CardDate, graphics/ccc.gif INTERVAL (7 - WEEKDAY(NOW())) DAY),"%Y")) AS Week, Sum graphics/ccc.gif(hoursbreakdown.BillableAmount) AS BillableAmount, graphics/ccc.gif hoursbreakdown.ProjectName FROM hoursbreakdown WHERE  graphics/ccc.gif(hoursbreakdown.CardDate > DATE_SUB(NOW(),INTERVAL 6 MONTH) graphics/ccc.gif &&hoursbreakdown.CardDate < DATE_ADD(NOW(),INTERVAL 6 graphics/ccc.gif MONTH)) GROUP BY hoursbreakdown.CardEmployee, Week, graphics/ccc.gif hoursbreakdown.ProjectName, DATE_FORMAT(hoursbreakdown graphics/ccc.gif.CardDate,"%Y"), DATE_FORMAT(hoursbreakdown.CardDate,"%u") graphics/ccc.gif ORDER BY Week, hoursbreakdown.CardEmployee'; @mysql_query($sql,$Recipes); // Monthly Hours Temporary Table $sql = "DELETE FROM monthlyhours"; @mysql_query($sql,$Recipes); $sql = 'INSERT INTO monthlyhours SELECT hoursbreakdown graphics/ccc.gif.CardEmployee, SUM(hoursbreakdown.CardHours) AS Hours, graphics/ccc.gif DATE_FORMAT(hoursbreakdown.CardDate,"%M %Y") AS Month, SUM graphics/ccc.gif(hoursbreakdown.BillableAmount) AS BillableAmount, graphics/ccc.gif hoursbreakdown.ProjectName FROM hoursbreakdown WHERE  graphics/ccc.gif(hoursbreakdown.CardDate > DATE_SUB(NOW(),INTERVAL 6 MONTH)&&hoursbreakdown.CardDate < graphics/ccc.gif DATE_ADD(NOW(),INTERVAL 6 MONTH)) GROUP BY hoursbreakdown graphics/ccc.gif.CardEmployee, Month, hoursbreakdown.ProjectName, DATE_FORMAT graphics/ccc.gif(hoursbreakdown.CardDate,"%Y"), DATE_FORMAT(hoursbreakdown graphics/ccc.gif.CardDate,"%m") ORDER BY DATE_FORMAT(hoursbreakdown.CardDate,"%Y"), graphics/ccc.gif DATE_FORMAT(hoursbreakdown.CardDate,"%m"), hoursbreakdown graphics/ccc.gif.CardEmployee'; @mysql_query($sql,$Recipes); ?> 

  2. After the code is inserted, save your page.

Step 3: Data Binding Process

Applying the dynamic data to the content tables is purely a drag-and-drop affair for this recipe. However, because there are three recordsets, it's important that you be careful and drag the data elements from the proper recordset.

  1. From the Bindings panel, expand the DailyHours recordset.

  2. Place the data fields on the page in their proper places.

    graphics/book.gif Drag CardEmployee into the cell under the Employee Name label.

    Drag ProjectName into the cell under the Project label.

    Drag Day into the cell next to the Date label.

    Drag Hours into the cell next to the Hours label.

    Drag BillableAmount into the cell next to the Cost label.

  3. From the Bindings panel, expand the WeeklyHours recordset.

  4. Place the data fields on the page in their proper places.

    graphics/book.gif Drag CardEmployee into the cell under the Employee Name label.

    Drag ProjectName into the cell under the Project label.

    Drag Week into the cell next to the Date label.

    Drag Hours into the cell next to the Hours label.

    Drag BillableAmount into the cell next to the Cost label.

  5. From the Bindings panel, expand the MonthlyHours recordset.

  6. Place the data fields on the page in their proper places.

    graphics/book.gif Drag CardEmployee into the cell under the Employee Name label.

    Drag ProjectName into the cell under the Project label.

    Drag Month into the cell next to the Date label.

    Drag Hours into the cell next to the Hours label.

    Drag BillableAmount into the cell next to the Cost label.

  7. When you're done, save the page [r6-15].


    Figure r6-15.

    graphics/08fig15.jpg


Step 4: Add the Repeat Regions

The final step for the View Time Cards recipe is to apply a Repeat Region server behavior to a row in each of the content tables. The three separate server behaviors will allow all the relevant data to be displayed.

We'll work our way down the page, starting with the Daily view.

  1. Select any of the dynamic data fields in the Daily content table.

  2. From the Tag Selector, choose the appropriate table row tag.

    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 DailyHours 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 to close the dialog.

Next, let's apply the server behavior to the Weekly content table.

  1. Select any of the dynamic data fields in the Weekly content table.

  2. From the Tag Selector, choose the appropriate table row tag.

    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 WeeklyHours 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 to close the dialog.

Finally, we'll add the server behavior to the Monthly table.

  1. Select any of the dynamic data fields in the Monthly content table.

  2. From the Tag Selector, choose the appropriate table row tag.

    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 MonthlyHours 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 to close the dialog.

  7. Save the page when you're done [r6-16].


    Figure r6-16.

    graphics/08fig16.jpg


Try out the entire application by entering different time cards for various projects.



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