Open Script Solution

This following feature I created since I saw the similar MOD in the osTicket Discussion Forum. The main difference between that one and mine is: the one I created will automatically save the staff time to database, whereas the previous one will save the staff time manually by entering the time each time staff post a reply or post an internal note. Besides that, mine uses seconds as the time value, whereas the previous one uses minutes as the time value will be saved in database. However, the time spent format that I created will be displayed in a full date and time format, such as: A Day(s), B Hour(s), C Minute(s), D Second(s) instead of only the minutes value. Now you are able to know the current staff time sheet including all staffs time sheet who involved to the current ticket in viewticket page in staff panel.

1. First of all, alter your ost_ticket_note and ost_ticket_response tables by using this following SQL script. Please note that the ost_ is the table prefix name. It could be different with yours. In case it differs with yours, please adjust it by yourself, and please be kind of it!

   ALTER TABLE ost_ticket_note
   ADD COLUMN `time_spent` int(11) default 0 NOT NULL;
    
   ALTER TABLE ost_ticket_response
   ADD COLUMN `time_spent` int(11) default 0 NOT NULL;

2. Open your \include\staff\viewticket.inc.php file, and find this code:

   25	$warn.='<span class="Icon overdueTicket">Marked overdue!</span>';

   after that line, please insert this following code:

   26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

   // Begin of MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 // We separate the SQL become two because if we make it become one SQL // then if there is no record regarding the ticket in one of the tables, // then the $total_time will give result NULL (0 + NULL) or (NULL + 0) $sql1 = 'SELECT SUM( time_spent ) FROM '.TICKET_RESPONSE_TABLE.' WHERE ticket_id='.db_input($id).''; $sql2 = 'SELECT SUM( time_spent ) FROM '.TICKET_NOTE_TABLE.' WHERE ticket_id='.db_input($id).''; $res1 = db_query($sql1); $res2 = db_query($sql2); $row1 = mysql_fetch_row($res1); $row2 = mysql_fetch_row($res2); $total_time = $row1[0] + $row2[0];   // This is for summing each staff members contribution $sql = 'SELECT staff_id, staff_name, SUM( time_spent ) as time FROM '.TICKET_RESPONSE_TABLE.' WHERE ticket_id='.db_input($id).' GROUP BY staff_id UNION SELECT staff_id, source as staff_name, SUM( time_spent ) as time FROM '.TICKET_NOTE_TABLE.' WHERE ticket_id='.db_input($id).' GROUP BY staff_id'; $result = db_query($sql);   $total_time_staff = array(); while($row=db_fetch_array($result)){ $total_time_staff[$row['staff_id']]['name'] = $row['staff_name']; $total_time_staff[$row['staff_id']]['time'] = $total_time_staff[$row['staff_id']]['time'] + $row['time']; } // End of MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009

   Find again this code:

   116 117 118 119

   <tr> <th>Source:</th> <td><?=$ticket->getSource()?></td> </tr>

   after the last line of that code, please insert this following code:

   120 121 122 123 124 125 126

   <?php // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 $totaltimespent = Ticket::formatStaffTimeSheet($total_time); ?> <tr> <th>Total Time Spent:</th> <td><?=$totaltimespent // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 ?></td> </tr>

   Find again this code:

   174 175 176 177 178 179 180

   <tr><th nowrap>Last Message:</th> <td><?=Format::db_datetime($ticket->getLastMessageDate())?></td> </tr> </table> </td> </tr> </table>

   after the last line of that code, please insert this following code:

   182 183 184 185 186 187 188 189 190 191 192 193 194 195

   <h2 class="msg">Time Sheet</h2> <table align="center" class="ticketinfo" cellspacing="1" cellpadding="3" width="100%" border=0> <tr><th>Staff Member</th><th>Total Time Spent</th></tr>   <?php while(list($key, $value) = each($total_time_staff)){ ?> <tr> <td><?=$value['name']?></td> <?php // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 $timespentstaff = Ticket::formatStaffTimeSheet($value['time']); ?> <td><?php echo $timespentstaff; // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 ?></td> </tr> <?php } ?> </table>

   Find again this code:

   266	$sql ='SELECT note_id,title,note,source,created FROM '.TICKET_NOTE_TABLE.' WHERE ticket_id='.db_input($id).' ORDER BY created DESC';

   then replace with this following code:

   266	$sql ='SELECT note_id,title,note,source,created,time_spent FROM '.TICKET_NOTE_TABLE.' WHERE ticket_id='.db_input($id).' ORDER BY created DESC';

   Find again this code:

   273	while($row=db_fetch_array($resp)) {?>

   after that line, please insert this following code:

   274 275 276

   <?php // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 $timespentstaff = Ticket::formatStaffTimeSheet($row['time_spent']); ?>

   Find again this code:

   278	<tr><th><?=Format::db_daydatetime($row['created'])?>&nbsp;-&nbsp; posted by <?=$row['source']?></th></tr>

   then replace with this following code:

   278	<tr><th><?=Format::db_daydatetime($row['created'])?>&nbsp;-&nbsp; posted by <?=$row['source']?>, Time Spent: <?php echo $timespentstaff; ?></th></tr>

   Find again this code:

   314 315 316

   while ($resp_row = db_fetch_array($resp)) { $respID=$resp_row['response_id']; ?>

   after the last line of that code, please insert this following code:

   317 318 319

   <?php // MOD Auto Staff Time Sheet, by Masino Sinaga, November 7, 2009 $timespentstaff = Ticket::formatStaffTimeSheet($resp_row['time_spent']); ?>

   Find again this code:

   321	<?=$resp_row['staff_name']?></th></tr>

   then replace with this following code:

   321	<?=$resp_row['staff_name']?>, Time Spent: <?php echo $timespentstaff; ?></th></tr>

   Find again this code:

   352	<div><font class="error">&nbsp;<?=$errors['response']?></font></div>

   before that line, please insert this following code:

   351	<input type="hidden" name="start_time" value="<?php echo date('Y-m-d H:i:s'); ?>">

   Find again this code:

   420	<input type="hidden" name="a" value="postnote">

   after that line, please insert this following code:

   421	<input type="hidden" name="start_time" value="<?php echo date('Y-m-d H:i:s'); ?>">

3. Open your \scp\tickets.php file, and find this code:

   84	if(!$errors && ($respId=$ticket->postResponse($_POST['msg_id'],$_POST['response'],$_POST['signature'],$_FILES['attachment']))){

   then replace with this following code:

   84	if(!$errors && ($respId=$ticket->postResponse($_POST['msg_id'],$_POST['response'],$_POST['signature'],$_FILES['attachment'],true,$_POST['start_time']))){

   Find again this code:

   168	if(!$errors && $ticket->postNote($_POST['title'],$_POST['note'])){

   then replace with this following code:

   168	if(!$errors && $ticket->postNote($_POST['title'],$_POST['note'],true,'',$_POST['start_time'])){

4. Open your \include\class.ticket.php file, and find this code:

   684 685

   //Insert Staff Reply function postResponse($msgid,$response,$signature='none',$attachment=false,$canalert=true){

   then replace with this following code:

   684 685 686 687 688 689 690 691 692 693 694 695 696

   // MOD Auto Staff Time Sheet by Masino Sinaga, November 7, 2009 function formatStaffTimeSheet($value) { $diffday = intval(floor($value/86400)); $modday = ($value%86400); $diffhour = intval(floor($modday/3600)); $diffminute = intval(floor(($modday%3600)/60)); $diffsecond = ($modday%60); return round($diffday)." Day(s), ".round($diffhour)." Hour(s), ".round($diffminute,0)." Minute(s), ".round($diffsecond,0)." Second(s)."; }   //Insert Staff Reply //function postResponse($msgid,$response,$signature='none',$attachment=false,$canalert=true){ function postResponse($msgid,$response,$signature='none',$attachment=false,$canalert=true,$start_time){

   Find again this code:

   699 700

   if(!$thisuser || !$thisuser->getId() || !$thisuser->isStaff()) //just incase return 0;

   after the last line of that code, please insert this following code:

   700 701 702 703

   $sql_duration='SELECT TIMESTAMPDIFF(SECOND,"'.$start_time.'",NOW());'; $result_duration = db_query($sql_duration); $row = mysql_fetch_row($result_duration); $time_spent = $row[0];

   Find again this code:

   745	',staff_name='.db_input($thisuser->getName()).

   then replace with this following code:

   745 746

   ',staff_name='.db_input($thisuser->getName()). ',time_spent='.db_input($time_spent).

   Find again this code:

   778 779 780

   //Insert Internal Notes function postNote($title,$note,$alert=true,$poster='') { global $thisuser,$cfg;

   then replace with this following code:

   778 779 780 781 782 783 784 785

   //Insert Internal Notes function postNote($title,$note,$alert=true,$poster='',$start_time) { global $thisuser,$cfg;   $sql_duration='SELECT TIMESTAMPDIFF(SECOND,"'.$start_time.'",NOW());'; $result_duration = db_query($sql_duration); $row = mysql_fetch_row($result_duration); $time_spent = $row[0];

   Find again this code:

   791	',staff_id='.db_input($thisuser?$thisuser->getId():0).

   then replace with this following code:

   791 792

   ',staff_id='.db_input($thisuser?$thisuser->getId():0). ',time_spent='.db_input($time_spent).

Enjoy the result, as you can see it via this screenshot!