Menambahkan Fitur Waktu Kerja Staf secara Otomatis ke osTicket v1.6 RC5

Fitur berikut ini saya buat dan tambahkan ke osTicket karena saya melihat MOD yang mirip pada Forum Diskusi osTicket. Perbedaan utama antara MOD tersebut dengan yang saya buat ini adalah: yang saya buat akan otomatis menyimpan waktu kerja staf ke database, sedangkan yang sebelumnya akan menyimpan data waktu kerja staf ke database secara manual dengan memasukkan jumlah waktu dalam satuan menit setiap kali staf membalas atau mengirim internal note. Di samping itu, yang saya buat ini menggunakan detik sebagai nilai waktu yang disimpan di database, sedangkan yang dibuat di atas menggunakan menit sebagai nilai waktu. Meskipun demikian, format waktu yang digunakan di sebuah tiket akan ditampilkan dalam format tanggal dan waktu durasi yang lengkap, seperti: A Day(s), B Hour(s), C Minute(s), D Second(s). Sekarang Anda dapat mengetahui waktu kerja staf termasuk juga semua staf yang terlibat bekerja dalam sebuah tiket pada halaman viewticket di panel staff.

Pertama sekali, ubah tabel ost_ticket_note dan ost_ticket_response dengan menggunakan skrip SQL berikut. Perlu diketahui bahwa ost_ adalah nama awalan tabel. Nama awalan ini bisa saja berbeda dengan nama awalan tabel Anda. Jika berbeda, silahkan sesuaikan sendiri dan harap maklum!
```
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;
```

Buka file \include\staff\viewticket.inc.php, dan cari kode ini:

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

Cari lagi kode ini:

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

setelah baris terakhir dari kode tadi, tambahkan kode berikut ini:

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

Cari lagi kode ini:

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

setelah baris terakhir dari kode tersebut, tambahkan kode berikut:

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

Cari lagi kode ini:

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

lalu timpa dengan kode berikut:

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

Cari lagi kode yang ini:

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

setelah baris terakhir dari kode tadi, tambahkan kode berikut:

274
275
276

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

Cari lagi kode yang ini:

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

lalu timpa dengan kode berikut:

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

Cari lagi kode yang ini:

314
315
316

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

setelah baris terakhir dari kode tadi, tambahkan kode berikut ini:

317
318
319

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

Cari lagi kode yang ini:

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

lalu timpa dengan kode berikut:

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

Cari lagi kode yang ini:

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

sebelum baris tersebut, tambahkan kode berikut:

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

Cari lagi kode yang ini:

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

setelah baris tersebut, tambahkan kode berikut:

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

Buka file \scp\tickets.php Anda, dan cari kode yang ini:

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

lalu ganti dengan kode berikut:

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

Cari lagi kode yang ini:

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

lalu timpa dengan kode berikut:

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

Buka file \include\class.ticket.phpAnda, dan cari kode yang ini:

684
685

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

lalu timpa dengan kode berikut:

    // 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){

Cari lagi kode yang ini:

699
700

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

setelah baris terakhir dari kode tersebut, tambahkan kode berikut ini:

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

Cari lagi kode yang ini:

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

lalu timpa dengan kode berikut:

745
746

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

Cari lagi kode yang ini:

778
779
780

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

lalu timpa dengan kode berikut:

    //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];

Cari lagi kode yang ini:

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

lalu timpa dengan kode berikut:

791
792

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

Selamat menikmati hasilnya, seperti yang bisa Anda lihat melalui cuplikan gambar ini!

Artikel Terkait:

Cetak Artikel Ini

Komentar

Durga mengatakan:

Friday, 13 November 2009 pada 06:05:48

very good Mod, it is working nice.

Masino Sinaga mengatakan:

Friday, 13 November 2009 pada 11:21:12

@Durga

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

David mengatakan:

Monday, 16 November 2009 pada 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 mengatakan:

Tuesday, 17 November 2009 pada 08:27:11

@David

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

Dave mengatakan:

Monday, 7 December 2009 pada 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 mengatakan:

Monday, 7 December 2009 pada 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 mengatakan:

Monday, 7 December 2009 pada 22:54:11

@David

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

lolita mengatakan:

Thursday, 31 December 2009 pada 21:56:33

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

Masino Sinaga mengatakan:

Sunday, 3 January 2010 pada 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 mengatakan:

Tuesday, 2 February 2010 pada 10:26:03

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

Chris mengatakan:

Tuesday, 2 March 2010 pada 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 mengatakan:

Thursday, 4 March 2010 pada 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 mengatakan:

Tuesday, 18 May 2010 pada 04:06:15

Do this work on v1.6 ST ?

anonymous mengatakan:

Saturday, 19 June 2010 pada 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 mengatakan:

Thursday, 26 August 2010 pada 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 mengatakan:

Tuesday, 7 September 2010 pada 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 mengatakan:

Tuesday, 4 January 2011 pada 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 mengatakan:

Tuesday, 2 August 2011 pada 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 mengatakan:

Wednesday, 31 August 2011 pada 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 mengatakan:
  
  Wednesday, 31 August 2011 pada 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 mengatakan:

Wednesday, 31 August 2011 pada 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 mengatakan:
  
  Wednesday, 31 August 2011 pada 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 mengatakan:
    
    Thursday, 1 September 2011 pada 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 mengatakan:
      
      Thursday, 1 December 2011 pada 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 mengatakan:
        
        Friday, 2 December 2011 pada 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.

Menambahkan Fitur Waktu Kerja Staf secara Otomatis ke osTicket v1.6 RC5

Komentar

Utarakan pikiran Anda Cancel reply

Iklan Anda

Bahasa

Referensi Anda

Tulisan Terakhir

Useful Resources

Useful Information

Istilah yang Paling Sering Dicari

Istilah yang Terakhir Dicari

Komentar Terakhir

Donate

Alexa & Page Rank

Blogroll