Formatting Timesheet Records in Weekly/Task Style View 1

[FateFirst]

Displaying Timesheet Records in Calendar Style View

hey peeps!

Currently having a little problem with trying to produce something and wonder if anyone out there can help.

I have 2 tables which contain the following fields;

# TABLE: time #
time_id
time_duration
time_task
time_datestamp
time_staff_id
time_project_id
time_task_id
time_desc

# TABLE: task #
task_id
task_title
task_desc

These tables are linked on IDs which im sure everyone can easily see from the names.

I want to pull the data from the tables and display the results so it looks something like:

[tt]

TASK/JOB | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|04/07/2004|05/07/2004|06/07/2004|07/07/2004|08/07/2004|09/07/2004|10/07/2004|
--------------------------------------------------------------------------------------------------
Task ID: 2 | Add | 4.5hrs | 3hrs | Add | Add | 2hrs | Add |
Desc: Doing summin | | | | | | | |
--------------------------------------------------------------------------------------------------
Task ID: 4 | 2.3hrs | 1.5hrs | Add | Add | 8hrs | 4hrs | 1hrs |
Desc: Doing summin2 | | | | | | | |
--------------------------------------------------------------------------------------------------
[/tt]

I just want to know what would be the best way to achieve this?

Like loop through the current week and select the data from the database each time i move onto a new day.

Or pull all the data, for the displayed week, from the database (WHERE clause time_datestamp >= 04/07/2004 AND <= 10/07/2004). I am currenly pulling all the data from the database using the WHERE clause above but when it comes to displaying the data as above I am unsure as to how to achieve this.

Any tips, hints would be much appreciated. EVen if its to say im going about it completely wrong. Something is better then nothing.

Thanks peeps!

- FateFirst

 

[FateFirst]

NOTE: I didnt really want to do the 'query to the database for each day' method as I kinda wanted to keep the querying down to a minimum...but of course, if this is the best way to do it then I may just have to go down that route

- FateFirst

 

[Tarwn]

How about letting the SQL do the work for you?

SELECT [task].task_id, time_datestamp, total(time_duration) as total_time, task_title FROM [time] INNER JOIN [task] ON [time].time_id = [task].time_id GROUP BY time_datestamp, task_title ORDER BY task_title, time_datestamp


This should return the records so trhat the rows are ordered by task then date for easier looping. In the event that you have multiple durations for the same date and task they will be totalled (which is why I did that). I assume you will probably be pulling out a specific date range so that would have to be added to the SQL statement as a WHERE clause.

You could then do something like this for the output:

Dim cur_task, cur_date

'--- assumptions
'  recordset has been retrived into rs_tasks
'  start date is coming from variable called date_start
'  assume there is also a variable called date_end
'  it also assumed that you will always be doing Sun-Sat
'     some of the code will scale, but some relies on this assumption
'--- end assumptions

cur_date = date_start

'--- output top row of table
Response.Write "<table><tr>"
Do While cur_date <= date_end
   Response.Write "<th>" & Left(WeekDayName(WeekDay(cur_date)),3) & "<br>"
   Response.Write FormatDateTime(cur_date,2) & "</td>"
   cur_date = DateAdd("d",1,cur_date)
Loop
'--- end top row

Dim i

'--- start output of database info
If Not rs_tasks.EOF Then rs_tasks.MoveFirst
Do Until rs_tasks.EOF
   'if task has changed or first loop, start row
   If cur_task <> rs("task_id") Then
      'if the previous week wasn't ended we need to end it
      If WeekDay(cur_date) <> 1 Then
         For i = WeekDay(cur_date) to 7
            Response.Write "<td><a href=""AddTime.asp?task_id=" & cur_task & "&date=" & FormatDateTime(cur_date,2) & """>Add Time</a></td>"
            cur_date = DateAdd("d",1,cur_date)
         Next
      End If

      cur_date = start_date
      cur_task = rs_task("task_id")
      Response.Write "<tr><th>Task ID: " & rs_task("task_id") & "<br>" & _
                     "Desc: " & rs_task("task_title") & "</th>"
   End If
   
   'if the queued row in the recordset is the wrong date, put out add links until we catch up or hit the end of the week
   Do Until cur_date > end_date Or Day(rs_task("time_datestamp")) = Day(cur_date)
      Response.Write "<td><a href=""AddTime.asp?task_id=" & cur_task & "&date=" & FormatDateTime(cur_date,2) & """>Add Time</a></td>"
      cur_date = DateAdd("d",1,cur_date)
   Loop

   'put out the info for this day/task
   Response.Write "<td>" & rs_task("total_time") & "</td>"

   'increment the expected date
   cur_date = DateAdd("d",1,cur_date)

   'increment the recordset
   rs_task.MoveNext
Loop

'take care of any outstanding columns in last row
For i = WeekDay(cur_date) to 7
   Response.Write "<td><a href=""AddTime.asp?task_id=" & cur_task & "&date=" & FormatDateTime(cur_date,2) & """>Add Time</a></td>"
   cur_date = DateAdd("d",1,cur_date)
Next

'finish the table
Response.Write "</table>"

ok, so there are likely some syntax or spelling errors in there, but it should be fairly easy to follow with the commenting. There are ways to compact that code further, but the ones I considered would have been harder to follow, so I left it as is. Hope it helps,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[FateFirst]

Very useful.

Thanks!

- FateFirst