Add Auto Staff Time Sheet Feature into osTicket v1.6 RC5

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.

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;
```

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:

// 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:

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

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

            <?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:

            <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:

<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'])?> -  posted by <?=$row['source']?></th></tr>

then replace with this following code:

278	<tr><th><?=Format::db_daydatetime($row['created'])?> -  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"> <?=$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'); ?>">

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'])){

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:

    // 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:

        $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:

    //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!

Print This Post

Comments

Durga says:

Friday, 13 November 2009 at 06:05:48

very good Mod, it is working nice.

Masino Sinaga says:

Friday, 13 November 2009 at 11:21:12

@Durga

Thanks. Glad to know it works and useful for you.

David says:

Monday, 16 November 2009 at 23:05:08

Very cool mod, it works like a charm!

A reporting-function for the admin would be nice to see how many time a staff member spend in total and for each customer. Is there a solution?

Masino Sinaga says:

Tuesday, 17 November 2009 at 08:27:11

@David

Thanks for the feedback. I will try to implement it if I only had free-time.

Dave says:

Monday, 7 December 2009 at 22:45:56

hi on number 4 you have Open your \include\class.ticket.inc.php file, and its actually class.ticket.php (no inc) i sent you a pm but i guess you have them off…. thanks really nice work, and works perfectily ….. great job..

Masino Sinaga says:

Monday, 7 December 2009 at 22:52:17

Hi Dave,

Good catch, anyway. Okay, done. I have fixed it.
Thanks for your kind feedback. Glad to know it is useful for you.
Have a nice day!

Masino Sinaga says:

Monday, 7 December 2009 at 22:54:11

@David

Add Time Spent at the Bottom of Each Staff’s Page of osTicket v1.6 RC5.

lolita says:

Thursday, 31 December 2009 at 21:56:33

i don’t find the lines of code on 4.

Masino Sinaga says:

Sunday, 3 January 2010 at 07:03:51

@lolita

Which lines of code do you mean? I have rechecked the whole original code and comparing to the original of osTicket v1.6 RC5, and the code are there.

akhrikas says:

Tuesday, 2 February 2010 at 10:26:03

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

Chris says:

Tuesday, 2 March 2010 at 02:41:10

Masino,

The OS Ticket Forum that set your creative genius ablaze to create this, very cool by the way, modification was used by yours truly. Since upgrading to the stable release of version 1.6 I can not get that one to work. I have now moved pre-production testing to another site and have implemented this modification – again I love it.

The problem is with the old modification if I worked 30 minutes on a problem I would simply type 30 into the time spent box. Now my 30 minutes is 30 seconds and this is throwing off reporting somewhat. Any suggestions?

Once I move this to production we have hours worth of work that needs to be credited to our staff. I thank you for your time.

Masino Sinaga says:

Thursday, 4 March 2010 at 13:32:44

Chris :
The problem is with the old modification if I worked 30 minutes on a problem I would simply type 30 into the time spent box. Now my 30 minutes is 30 seconds and this is throwing off reporting somewhat. Any suggestions?

Simply convert your old tickets which have time_spent value in “minutes” become “seconds” value. For example (as in your case), an old ticket #321567 which has time_spent value = 30 minutes, then change that value become: 1800 seconds.

Please note that you have to carefully doing this just in case if you have already the new tickets which generated by using “minutes” value.

raoul says:

Tuesday, 18 May 2010 at 04:06:15

Do this work on v1.6 ST ?

anonymous says:

Saturday, 19 June 2010 at 01:17:10

There’s an error in your postNote and postResponse code. Since assigning a ticket to a staffmember also creates a note it will fail adding that note since $start_time is undefined

So changing the function by adding =0 to the parameter sets the value to 0 if not set.
Then you need to add a check to make sure it doesn’t start calculating when the value is set to 0 otherwise you’d been working for a long time on one ticket

$time_spent = 0;

if ($start_time != 0) {
$sql_duration=’SELECT TIMESTAMPDIFF(SECOND,”‘.$start_time.’”,NOW());’;
$result_duration = db_query($sql_duration);
$row = mysql_fetch_row($result_duration);
$time_spent = $row[0];
}

Jorge Jordao says:

Thursday, 26 August 2010 at 03:41:07

I’ve installed like you show here, but when i post a note the timesheet shows like this:

Staff Member Total Time Spent
Admin Admin -1 Day(s), -2 Hour(s), -60 Minute(s), -41 Second(s).

EKO says:

Tuesday, 7 September 2010 at 08:57:25

Saya pun mengalami hal yang serupa dengan Jorge Jordao ..

Staff Member Total Time Spent
Admin Admin -1 Day(s), -2 Hour(s), -60 Minute(s), -41 Second(s).

Apakah ada yang salah dengan script saya …?

Kyriacos Aristodemou says:

Tuesday, 4 January 2011 at 02:38:59

Is it possible to change this mod to have it calcualte time spend on any kind of reply you post. instead of only work with internal post?

Tonya Harris says:

Tuesday, 2 August 2011 at 12:44:32

love this one too. Question – it seems to calculate based on time that the ticket is open on the screen. Is there any way to manually add time? Let’s say there is offline research or work being done that doesn’t require the request to be open. Would still love to capture that overall time.

Todd says:

Wednesday, 31 August 2011 at 01:56:49

Hi Masino – I love this mod! But I’m having some trouble with it.

Please see the following screenshot:
http://i.imgur.com/QbJNE.png

How come it’s saying ‘system’ instead of displaying my Staff Member’s name? I tested multiple staff accounts.

Thanks Masino!

-Todd

- Masino Sinaga says:
  
  Wednesday, 31 August 2011 at 09:27:33
  
  Todd,
  
  Open your ost_ticket_response table, and make sure in the staff_name field, there is no record which has value of system. Afterwards, open your ost_staff table, and make sure in the username and or firstname or lastname field, there is no record which has value of system.
  
Todd says:

Wednesday, 31 August 2011 at 22:28:58

Thank you for your reply Masino.

However, after looking at those tables, I can’t find any trace of the value of ‘system’.

I have also double checked all of my php code that I edited from your mod.

Any other ideas? Thanks!

-Todd

- Todd says:
  
  Wednesday, 31 August 2011 at 23:36:53
  
  Hi Masino, I ended up finding this in the database, under the ost_ticket_note table:
  
  http://i.imgur.com/D5s2E.png
  
  But I’m not sure where to find and remove that ‘system’ value.
  
  Any additional help would be appreciated, thanks!
  
  -Todd
  
  - Masino Sinaga says:
    
    Thursday, 1 September 2011 at 07:32:08
    Hi Todd,
    
    You can find this following code in your /include/class.ticket.php:
    return $this->postNote($title,$note,false,'system');
    Afterwards, find the logActivity keyword in your /scp/tickets.php file. There you can see that the function call is triggered from this file.
    
    I think that’s a normal when there is no the staff name information when the ticket note is saved, then osTicket will save “system” as the source of it.
    Does it make sense for you?
    - Lucas says:
      
      Thursday, 1 December 2011 at 16:19:35
      
      Dear Masino,
      That makes sense.
      I have question: how to hide info from “system”?
      As You see below, in some tickets I’ve got only system info in time sheet.
      I think that this is unnecessary info.
      http://imageshack.us/photo/my-images/846/timesheet.jpg/
      
      By the way, I love that MOD..!!
      
      Cheers,
      Lucas
      
      - Masino Sinaga says:
        
        Friday, 2 December 2011 at 09:46:29
        
        If you do not want to display the record which has the value of “system”, then customize the SQL and add the clause of
        
        source <> 'system'
        
        into the WHERE section of the SQL.
        Hope it helps you.

Add Auto Staff Time Sheet Feature into osTicket v1.6 RC5

Comments

Speak Your Mind Cancel reply

Your Ads

Languages

Your References

Recent Posts

Useful Resources

Useful Information

Popular Search Terms

Recent Search Terms

Recent Comments

Donate

Alexa & Page Rank

Blogroll

Add Auto Staff Time Sheet Feature into osTicket v1.6 RC5

Related Posts:

Comments

Speak Your Mind Cancel reply

Your Ads

Languages

Your References

Recent Posts

Useful Resources

Useful Information

Popular Search Terms

Recent Search Terms

Recent Comments

Donate

Alexa & Page Rank

Blogroll