osTicket Community Forums

drozenski · #1 10-23-2009, 12:52 PM

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

PHP Code:


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

To

PHP Code:


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

Find

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Replace With

PHP Code:


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

Add after

PHP Code:


			
global $thisuser,$cfg;

PHP Code:


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

Find

PHP Code:


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

Add After

PHP Code:


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

Include\staff\viewticket.php

Find

PHP Code:


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

Add after

PHP Code:


			
$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

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Add After

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

Find

PHP Code:


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

Replace With

PHP Code:


			
$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

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Add After

PHP Code:


			
<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

PHP Code:


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

Add After

PHP Code:


			
&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

PHP Code:


			
<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

PHP Code:


			
&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

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Replace With

PHP Code:


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

Find

PHP Code:


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

Add After

PHP Code:


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

Find

PHP Code:


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

Replace With

PHP Code:


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

Results

snowscar · #2 10-23-2009, 08:06 PM

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

warwickw · #3 10-24-2009, 12:57 PM

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 ?

ibw007 · #4 10-24-2009, 01:52 PM

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

ibw007 · #5 10-24-2009, 02:04 PM

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?

drozenski · #6 10-26-2009, 08:15 AM

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

PHP Code:


			
//Begin2 Add
//End2 Add 

//Begin Add
//End Add 

<!-- Begin Add -->
<!-- End Add -->

drozenski · #7 11-06-2009, 02:36 PM

bump to the top

ringo380 · #8 11-06-2009, 06:03 PM

hey IBW007 can you please explain how you fixed that DB error? I'm getting the same problem.

masino_sinaga · #9 11-07-2009, 04:26 AM

Quote:

Originally Posted by ibw007

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!

Sincerely,
Masino Sinaga

beeman · #10 11-09-2009, 05:11 AM

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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode