Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[ MOD ] Staff Time Sheet / Tracking

Ever wanted to input time for jobs in osTicket or be able to track your staff and see how long it takes to do a particular job?

Then this mod is for you!

If you like the mod, Please rate the thread. Thanks. Will help other people to find it as time goes on.

You will need to add the following fields to your DB

Table - ticket_response @ ticket_note
Field - time_spent
Type - INT
Length / Value = 11
Null = No
Default = 0


\include\Class.ticket.php

Change Line 685

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


To

function postResponse($msgid,$response,$signature='none',$attachment=false,$canalert=true, $time_spent=0){


Find

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


Add After

        if (!is_numeric($time_spent)){
return 0;
}


Find


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


Add After

',time_spent='.db_input($time_spent).


Find

function postNote($title,$note,$alert=true,$poster='') {        


Replace With

    function postNote($title,$note,$alert=true,$poster='', $time_spent=0) {


Add after
global $thisuser,$cfg;


        if (!is_numeric($time_spent)){
return 0;
}


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


Add After
',time_spent='.db_input($time_spent).


Include\staff\viewticket.php

Find
if($ticket->isOverdue())
$warn.='<span class=\"Icon overdueTicket\">Marked overdue!</span>';


Add after
$sql = 'SELECT
(SELECT SUM( time_spent )
FROM '.TICKET_RESPONSE_TABLE.'
WHERE ticket_id='.db_input($id).')
+(SELECT SUM( time_spent )
FROM '.TICKET_NOTE_TABLE.'
WHERE ticket_id='.db_input($id).')
AS total_time';
$result = db_query($sql);
$cursor = mysql_fetch_row($result);
$total_time = $cursor[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'];
}


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


Add After
            <tr>
<th>Total Time Spent:</th>
<td><?=$total_time?> minutes</td>
</tr>


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


Add After
<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>
<td><?=$value['time']?> minutes</td>
</tr>
<?php } ?>
</table>


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


Replace With
$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

<tr class=\"header\"><td><?=Format::display($row['title'])?></td></tr>
<?} ?>
<tr><td><?=Format::display($row['note'])?></td></tr>


Add After
<tr><td>Time Spent: <?=Format::display($row['time_spent'])?> minutes</td></tr>


Find
<td><?=$ticket->getAttachmentStr($respID,'R')?></td></tr>
<?}?>
<tr><td> <?=Format::display($resp_row['response'])?></td></tr>


Add After
<tr><td>Time Spent: <?=Format::display($resp_row['time_spent'])?> minutes</td></tr>

<!-- Begin2 Comment out -->
<!-- <tr><td>Total Time Spent: <?=Format::display($total_times[$resp_row['staff_id']])?> minutes</td></tr>-->
<!-- End2 Comment out -->


Find
</select>&nbsp;&nbsp;&nbsp;<input type='checkbox' value='1' name=append checked=\"true\" />Append
<?}?>


Add After
&nbsp;&nbsp;&nbsp;<label for=\"time_spent\">Time Spent: </label><input type=\"text\" name=\"time_spent\" value=\"\" id=\"time_spent\">
<!-- End Add -->

<!-- Begin2 Add -->
<font class=\"error\">&nbsp;<?=$errors['time_spent']?></font>


Find
<label for=\"title\">Note Title:</label>
<input type=\"text\" name=\"title\" id=\"title\" value=\"<?=$info['title']?>\" size=30px />
</select><font class=\"error\">*&nbsp;<?=$errors['title']?></font>


Add After
&nbsp;&nbsp;&nbsp;<label for=\"time_spent\">Time Spent: </label><input type=\"text\" name=\"time_spent\" value=\"\" id=\"time_spent\">
<font class=\"error\">*&nbsp;<?=$errors['time_spent']?></font>


scp\tickets.php

Find
$fields['response']     = array('type'=>'text', 'required'=>1, 'error'=>'Response message required');


Add After
$fields['time_spent']   = array('type'=>'int', 'required'=>1, 'error'=>'Time Spent required');


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


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


Find
$fields['note']     = array('type'=>'string',   'required'=>1, 'error'=>'Note message required');


Add After
$fields['time_spent']     = array('type'=>'int',   'required'=>1, 'error'=>'Time Spent required');


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


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




Results

image
«1

Comments

  • thats a lot of editing. Could you please post the modified file as an attachment so that we can just download and use it please.

    Anyways awesome mod. Just installed osticket and loving it
  • This looks like a great mod, awesome work!

    Please post the updated files in a zip or tar.gz, its a lot of editing and I'm sure people will make mistakes. Also is this related to 1.6 rc5 version ?
  • I'm getting the following error

    i'm getting the following error.

    [SELECT
    (SELECT SUM( time_spent )
    FROM ost_ticket_response
    WHERE ticket_id=32)
    +(SELECT SUM( time_spent )
    FROM ost_ticket_note
    WHERE ticket_id=32)
    AS total_time]

    Unknown column 'time_spent' in 'field list'

    the field has beened to the table i can't figure it out
  • i figured it out.

    how about a real timer. from the time its opened by a staff member to the time they post, close or a pre-definited timer in case they leave it open on their desktop?
  • The mod can now be downloaded here

    http://drozenski.com/osTicket_TimeSpent.zip

    Or by clicking the attachment in this post


    Every change is marked with

    //Begin2 Add
    //End2 Add

    //Begin Add
    //End Add

    <!-- Begin Add -->
    <!-- End Add -->
    Time spent.zip
    22K
  • bump to the top
  • same problem as ibw007

    hey IBW007 can you please explain how you fixed that DB error? I'm getting the same problem.
  • Solution
    ibw007;10783 said:
    i figured it out.

    how about a real timer. from the time its opened by a staff member to the time they post, close or a pre-definited timer in case they leave it open on their desktop?
    I have successfully created that MOD:
    [MOD] Auto Staff Time Sheet

    Hopefully this will be helpful for osTicket community. Cheers! :D

    Sincerely,
    Masino Sinaga
  • Hi Masino,

    It looks like some code of this MOD overwrites some portion/s of the code of the other MOD (Add Post Number, Sender Name, and Time Duration in Each Message of osTicket); that you created earlier. Are the two MOD's compatable? Can they work together on the same osTicket installation?

    Regards
    Beeman
  • Completely different

    Hi Beeman,

    Thanks for the question.

    In order to give the explanation accordingly with the related thread, I will answer your question on my thread above. Please be kind. Thanks. ;)

    Sincerely,
    Masino Sinaga
  • Is it possible to have this MOD for internal notes also? Staff often reports job steps as internal note because the customer shouldn't get involved at any step (as it would be we send ticket replies).

    ###EDIT###
    The issue is still actual. My edit yesterday was because of a different topic - I just mistakenly posted it here. So, help is needed!
  • This is exactly what I was looking for. I love osTicket, and I love my invoicing system, but I was getting tired of keeping the times I worked on a ticket in my Project manager...

    Real quick, what exactly needs to be added to the DB? Is there a SQL Query I can run instead?
  • A long modification but still great with great stuff................
    [right]Sales tracking sales tracking software Sales lead tracking software[/right]
  • Awesome - Thanks!

    Really appreciate you sharing this MOD it will help us greatly. Thanks again!:)
  • could you please clarify what needs to be added to the db? "Table - ticket_response @ ticket_note" which table is this? both the ticket_response AND ticket_note? I'm confused.
  • Sorry for the late reply to questions. Been busy here at work.

    What i was saying for the DB stuff was this

    Table - ticket_response @ ticket_note
    Field - time_spent
    Type - INT
    Length / Value = 11
    Null = No
    Default = 0


    You need to add a new field to two Tables.
    The "ticket_response" table and the "ticket_note table"

    The new field must have the following structure

    Field Name: time_spent
    Type: INT
    Length / Value = 11
    Null = No
    Default = 0
  • thanks

    Great Mod just what I was looking for.

    I was wanting to know if you cold tell me what to modify in view.php so that users can see the time spent on their job as well

    thanks

    AB
  • How can I send total time spent on ticket with ticket response?

    Hi,

    I may have missed this somehow, but how can I send the total time spent so far on the ticket to the user when I respond to the ticket?

    I want it to be added to the email response that is sent. Is that possible?

    Thank you for your help!!
  • great mod, but one question

    Thanks for the mod, it works great and is almost perfectly what i need.

    Is it possible to have this mod also input the total time spent on this ticket into the dbtable "_ticket"

    I extract the data from that table for monthly reports and tracking
  • Question

    Hello,

    How can you send the total time spent on the ticket so far on the email sent to the user??

    I followed the mod and it works fine but it is useless (to me) if I'm not able to let the client know how much time I've worked on their ticket.

    I want this value (the total time) to be added automatically to each email that is sent to the user. For instance, when I reply to a ticket from OSticket, I would like the email to contain the amount of time spent on the ticket during my response and maybe the total time spent thus far.

    Thanks
  • You should be able to!
    In include/class.ticket.php, around line 488, is
        function replaceTemplateVars($text){

    This is the function used to replace the variable placeholders in the email templates with their real value (ie it turns %subject into "My Ticket Subject").

    There are two things to change. You need to find
    $search = array('/%id/',

    Before the '/%id' insert your placeholder, so it may look like this:
    $search = array('/%time_spent/', '/%id/',


    Next you need to find
            $replace = array($this->getId(),
    $this->getExtId(),

    and reference the function to return the number of hours:
            $replace = array($time_spent, $this->getId(),
    $this->getExtId(),



    I have used this before with other variables, so it should work. It would be a lot neater if there was a get_time_spent() function, but that would take a fair bit more editting, and this should get the job done.
  • Doesn't Seem to work

    Hi,

    Thank you for your response. I cannot seem to get this to work. I spent about 3 hours playing with it but to no avail.

    The variable comes up empty, nothing is displayed. Do I have to assign the minutes value to the variable elsewhere?

    Thank you for your help!
  • Verry big thanks for this Mod

    I have just a question:

    How can I ad a new collumn with the total time spent on the tickets page (staff) with a total at the bottom?

    We need to have stastistics with the total spent time per clients, per month, ...

    Thanks for your reply
  • Hi Guys,

    maybe somebody encountered the same problem and has a solution:

    I have implemented this MOD in osT 1.6 ST and it works great, with one exception: When i want to add a Canned Response to a Ticket, i got a error message "invalid form or tag" whenever i choose a premade reply from the options field.

    Anybody got a idea how to fix this?

    thanks
  • after the implementation i got the following error:

    Tue, Mar 9 2010 @ 12:33 DB Error #1054

    --------------------------------------------------------------------------------
    [INSERT INTO ost_ticket SET created=NOW() ,ticketID=196330,dept_id=1,priority_id=2,email='myadress',name='Test-Kunde',subject='Test',topic='',phone='',phone_ext='',ip_address=xxxxxxx,source='Phone']

    Unknown column 'topic' in 'field list'
  • bump :-) and hope for help
  • This mod was created for 1.6 RC 5 not the current release.

    I have not done any development on this mod or any other projects i was working on for quite some time now. I have since moved jobs and they have no need for a new ticketing system.

    Im sorry but i will not be able to troubleshoot bugs with this mod in the newest release. I just dont have the time with a new jobs, getting married, buying a home and a baby on the way. :eek:
  • tanks for your Information and all the best... i will try RC5 to use your MOD
  • Updated class.ticket.php

    Berlinger,

    I ran into the same problem and I believe I modified the class.ticket.php to work with the newest release.

    Attached is the updated version.

    Hope it helps..

    Bill
    class.ticket.zip
    14K
  • Hi William

    thanks for this, but 2h ago i inst. the "old" version... :-)
Sign In or Register to comment.