Modifikasi berikut akan menambahkan fitur baru berupa tiket yang otomatis ditugaskan ke seorang staf untuk setiap departemen ke dalam osTicket v1.6 RC5. Anda dapat menentukan staf yang akan mendapat penugasan otomatis tiket berdasarkan departemennya. Setiap kali sebuah tiket baru dibuat, maka staf tadi akan otomatis ditugaskan untuk menangani tiket tadi. Berikut ini modifikasi yang sudah saya buat berdasarkan modifikasi yang sama yang pernah saya buat untuk osTicket v1.6 RC4.
- Pertama sekali, pastikan Anda sudah mengimplementasikan modifikasi lainnya yang sudah saya buat mengenai Menampilkan Help Topic di Form Tiket Baru untuk Staf di osTicket v1.6 RC5. Modifikasi ini merupakan prasyarat sebelum Anda melakukan modifikasi di bawah ini.
- Unduh file auto-assigned-ticket.zip, ekstrak, ada dua file di dalamnya, yaitu: helptopicsautoassignlist.inc.php dan topicautoassign.inc.php. Salin kedua file tadi ke dalam sub direktori \include\staff\ dari osTicket Anda.
-
Buah sebuah tabel baru bernama ost_help_topic_auto_assign dengan menggunakan skrip SQL berikut (sesuaikan awalan nama tabel dengan yang Anda gunakan, saya menggunakan awalan: ost_):
CREATE TABLE `ost_help_topic_auto_assign` ( `auto_assign_id` int(11) NOT NULL auto_increment, `topic_id` int(11) NOT NULL, `dept_id` int(11) NOT NULL, `staff_id` int(10) NOT NULL, `created` datetime NOT NULL default '0000-00-00 00:00:00', `updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`auto_assign_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
-
Buka file main.inc.php, dan cari kode ini:
12
define('TOPIC_TABLE',TABLE_PREFIX.'help_topic');
setelah baris tersebut, tambahkan kode berikut ini:
13
define('TOPIC_AUTO_ASSIGN_TABLE',TABLE_PREFIX.'help_topic_auto_assign');
-
Buka file \scp\admin.php, dan cari kode ini:
399 400
case 'groups': include_once(INCLUDE_DIR.'class.group.php');
sebelum baris pertama kode tadi, tambahkan kode berikut ini:
398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449
case 'topicautoassign': if($_POST['members']) { $staff_id = $_POST['members']; $cekstaff = db_query('SELECT staff_id FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE staff_id='.$staff_id.' AND dept_id='.$_POST['dept_id'].' AND topic_id='.$_POST['topic_id'].''); $staffcount = db_num_rows($cekstaff); if ($staffcount) { $sql='UPDATE '.TOPIC_AUTO_ASSIGN_TABLE.' SET updated=NOW()'; } else { $sql='INSERT INTO '.TOPIC_AUTO_ASSIGN_TABLE.' SET created=NOW(),'. 'staff_id='.$staff_id.','. 'dept_id='.$_POST['dept_id'].','. 'topic_id='.$_POST['topic_id']; if(!db_query($sql) or !($autoassignid=db_insert_id())) { $errors['err']='Unable to insert auto-assign help topic'; } $sql='DELETE FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE dept_id='.$_POST['dept_id'].' AND topic_id='.$_POST['topic_id'].' AND staff_id <> '.$staff_id; if(db_query($sql) && ($num=db_affected_rows())) { $msg="$num ".S_A_OF." $count auto-assign help topic(s) deleted."; } else { $errors['err']='Could not remove auto-assign help topic.'; } } header('Location: admin.php?t=helptopicsautoassignlist'); } else { $errors['err']='No auto-assign help topic selected!'; } break; case 'helptopicsautoassignlist': $do=strtolower($_POST['do']); switch($do){ case 'mass_process': if(!$_POST['tids'] || !is_array($_POST['tids'])) { $errors['err']='You must select at least one topic'; }else{ $count=count($_POST['tids']); $ids=implode(',',$_POST['tids']); if($_POST['delete']){ $sql='DELETE FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE auto_assign_id IN ('.$ids.')'; if(db_query($sql) && ($num=db_affected_rows())) $msg="$num of $count selected topic(s) deleted"; else $errors['err']='Unable to delete selected topics'; } } break; default: $errors['err']='Unknown Topic Action.'; } break;
Cari lagi kode yang ini:
657 658 659 660 661 662 663 664 665 666 667 668 669 670 671
case 'topics': require_once(INCLUDE_DIR.'class.topic.php'); $topic=null; $nav->setTabActive('topics'); $nav->addSubMenu(array('desc'=>'Help Topics','href'=>'admin.php?t=topics','iconclass'=>'helpTopics')); $nav->addSubMenu(array('desc'=>'Add New Topic','href'=>'admin.php?t=topics&a=new','iconclass'=>'newHelpTopic')); if(($id=$_REQUEST['id']?$_REQUEST['id']:$_POST['topic_id']) && is_numeric($id)) { $topic= new Topic($id); if(!$topic->load() && $topic->getId()==$id) { $topic=null; $errors['err']='Unable to fetch info on topic #'.$id; } } $page=($topic or ($_REQUEST['a']=='new' && !$topicID))?'topic.inc.php':'helptopics.inc.php'; break;
lalu timpa dengan kode berikut:
657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693
case 'topics': case 'topicautoassign': case 'helptopicsautoassignlist': require_once(INCLUDE_DIR.'class.topic.php'); $topic=null; $nav->setTabActive('topics'); $nav->addSubMenu(array('desc'=>'Help Topics','href'=>'admin.php?t=topics','iconclass'=>'helpTopics')); $nav->addSubMenu(array('desc'=>'Add New Topic','href'=>'admin.php?t=topics&a=new','iconclass'=>'newHelpTopic')); $nav->addSubMenu(array('desc'=>'Auto-Assign Help Topic','href'=>'admin.php?t=helptopicsautoassignlist','iconclass'=>'helpTopics')); switch(strtolower($_REQUEST['t'])){ case 'topics': default: if(($id=$_REQUEST['id']?$_REQUEST['id']:$_POST['topic_id']) && is_numeric($id)) { $topic= new Topic($id); if(!$topic->load() && $topic->getId()==$id) { $topic=null; $errors['err']='Unable to fetch info on topic #'.$id; } } $page=($topic or ($_REQUEST['a']=='new' && !$topicID))?'topic.inc.php':'helptopics.inc.php'; break; case 'helptopicsautoassignlist': $page='helptopicsautoassignlist.inc.php'; break; case 'topicautoassign': default: if(($id=$_REQUEST['id']?$_REQUEST['id']:$_POST['topic_id']) && is_numeric($id)) { $topic= new Topic($id); if(!$topic->load() && $topic->getId()==$id) { $topic=null; $errors['err']='Unable to fetch info on topic #'.$id; } } $page=($topic or ($_REQUEST['a']=='newedit' && !$topicID))?'topicautoassign.inc.php':'helptopicsautoassignlist.inc.php'; break; } break;
-
Buka file \include\staff\helptopics.inc.php Anda, dan cari kode ini:
19 20
<th>Help Topic</th> <th>Status</th>
lalu timpa dengan kode berikut:
19 20 21
<th>Help Topic</th> <th>Auto Assign</th> <th>Status</th>
Cari lagi kode yang ini:
42
<td><a href="admin.php?t=topics&id=<?=$row['topic_id']?>"><?=Format::htmlchars(Format::truncate($row['topic'],30))?></a></td>
lalu timpa dengan kode berikut:
42 43
<td><a href="admin.php?t=topics&id=<?php echo $row['topic_id']?>"><?php echo Format::htmlchars(Format::truncate($row['topic'],30))?></a></td> <td><a href="admin.php?t=topicautoassign&a=newedit&topic_id=<?php echo $row['topic_id'] ?>&dept_id=<?php echo $row['dept_id']; ?>">Add/Edit Auto Assign</a></td>
-
Buka file \include\class.ticket.php Anda, dan cari kode ini:
1424
$ipaddress=$var['ip']?$var['ip']:$_SERVER['REMOTE_ADDR'];
setelah baris tersebut, tambahkan kode berikut ini:
1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445
// Begin of MOD Auto-Assigned Ticket, by Masino Sinaga, May 8, 2009 // Check first staffid based on deptid in staff table $sqlstaff = 'SELECT staff.staff_id FROM '.STAFF_TABLE.' staff WHERE staff.dept_id='.$deptId; $resstaff = db_query($sqlstaff); while (list($staff_id)=db_fetch_row($resstaff)) { $staffid1 = $staff_id; } // Then check this staff whether exists in help_topic_auto_assign table // and if exists then get this staff, but if not, no auto-assigned staff. $sqltas = 'SELECT tas.staff_id FROM '.TOPIC_AUTO_ASSIGN_TABLE.' tas WHERE tas.dept_id='.$deptId.' AND tas.topic_id='.$topicId; $restas = db_query($sqltas); while (list($staff_id)=db_fetch_row($restas)) { $staffid2 = $staff_id; } if ($staffid1==$staffid2) { $staffid = $staffid1; } // End of MOD Auto-Assigned Ticket, by Masino Sinaga, May 8, 2009
Cari lagi kode yang ini:
1456
',priority_id='.db_input($priorityId).
lalu timpa dengan kode berikut ini:
1456 1457
',priority_id='.db_input($priorityId). ',staff_id='.db_input($staffid). // // MOD Auto Assigned Ticket by Masino Sinaga, October 28, 2009
Itulah semuanya. Semoga bermanfaat. 
Kalo ada pesan error ini:
[SELECT tas.staff_id FROM fpsost_help_topic_auto_assign tas
WHERE tas.dept_id=
AND tas.topic_id=]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND tas.topic_id=’ at line 3
Kira2 salahnya apa ya mas?
trims
Pastikan semua instruksi sudah diikuti dengan teliti dan benar, khususnya langkah nomor 1 di atas. Saya sudah mencoba dan segalanya berjalan dengan lancar di tempat saya.
Saya baca di forum:
http://www.osticket.com/forums/showthread.php?t=2085
Di situ, ditulis :
Before apply the MOD above, make sure you have already done the MOD that I made via these 2 links below:
- [MOD] Help Topic in New Ticket for Client and Staff
- [MOD] Insert Staff ID Into Ticket Table
Tapi kalo baca di sini cuma perlu module ‘Help Topic in New Ticket for Client and Staff’.
Apa Module yang kedua juga diperlukan module ini?
trims
Betul. Di topik pada forum diskusi tersebut, ada dua modifikasi yang sebelumnya harus diimplementasikan terlebih dulu sebelum mengimplementasikan modifikasi Auto-Assigned Ticket yang mirip dengan yang dibahas di artikel ini. Mengapa 2? Karena itu untuk osTicket versi 1.6 RC4.
Sedangkan untuk osTicket v1.6 RC5, syaratnya Anda cukup mengikuti petunjuk yang ada di artikel ini saja. Artinya, tidak perlu mengikuti petunjuk yang terdapat di topik yang membahas pada versi 1.6 RC4. Saya sengaja menyampaikan informasi yang terkait dengan versi 1.6 RC4 tersebut, sebagai informasi saja bahwa modifikasi di artikel ini saya buat berdasarkan topik di forum diskusi osTicket tersebut. Mudah-mudahan jelas sekarang.
Hi Masino,
I’ve been following your 2 MODS.
Displaying MOD works fine while I’ve got pb with the auto assigned feature.
In OsTicket, when trying to auto assign to a staff, I have :
Unknown command!
There is no member for this department or help topic, yet.
In the System Logs I have:
Tuesday 12 January 2010 15:46 DB Error #1064
[SELECT dept_id,dept_name FROM ost_department WHERE dept_id=]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
Tuesday 12 January 2010 15:46 DB Error #1064
[SELECT topic_id, topic FROM ost_help_topic WHERE topic_id=]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
Tuesday 12 January 2010 15:46 DB Error #1064
[SELECT staff_id, firstname, lastname FROM ost_staff WHERE dept_id = ORDER BY staff_id]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER BY staff_id’ at line 1
Do you have any idea why I don’t get the values in the SQL requests?
I’ve double checked the modifications I made and everything follows your instructions.
Also in include/class.ticket.php, what do you mean by:
“// Check this staff whether exists in help_topic_auto_assign table
// and if exists then get this staff, but if not, no auto-assigned staff.” ?
Regards
@Francois
Usually, if the value of your topic_id and or dept_id in your SQL is blank or it has no value, then it is related to the “Displaying Help Topic in New Ticket Form for Staff in osTicket v1.6 RC5″ MOD in the first step above. FYI, I had tested this MOD (and also the other one I mentioned before), and everything worked fine.
Masino,
Sorry I don’t understand what you mean.
I don’t see what is related to the “Displaying …” vs “Add Auto …”.
I’ve created values in the DB for topic_id, dept_id and staff_id and DB errors have gone though no assignment is done.
Could you provide me with a scenario of the workflow as you did for the “Password Needed to Activate A Ticket” MOD ?
thanks
@Francois
It means that make sure you have already done the “Displaying Help Topic in New Ticket Form for Staff in osTicket v1.6 RC5″ MOD before implement this current MOD: “Add Auto Assigned Ticket Feature into osTicket v1.6 RC5″ as mentioned in the first step above.
You can see this MOD’s workflow and related information regarding the last MOD via this link. Please note that the link will show you the same workflow as I did the same modification in 1.6 RC4 version. Do not follow the steps from that osTicket forum, as it was made only for 1.6 RC4. That link will only show you about the idea or the workflow you asked for. Hope it helps you.
@Francois
Hi,
I have the exact same problem. Did you solve it?
Thanks Lukas
Hi Lukas,
Please re-apply the modification above, especially number 5 and 7. There are some lines of code that missing. Now it has already there. Sorry for that.
Hi Lukas,
No I’ve not solved it yet.
I’m first focused on #1 (Displaying Help topic…), see my comment(s) there.
How many Masino’s mods did you implement?
I’ve made 13 of them, so we may not have the same pb even though it looks like it is.
Double check what you’ve done and report any idea or strange behaviour you may face.
Look also in the OST forum.
Masino answers pretty quickly and he has a knowledge of the OST architecture, php and probably other things that I don’t have.
We’re learning and he tries to help us the best he can.
May be I should have just answered: “No, I’ve not solved it, did you ?”
Cheers
I inserted values into the help_topic_auto_assign table but I still get the DB errors mentioned in post #5
Is it possible that ‘Displaying Help Topic in New Ticket Form for Staff in osTicket v1.6 RC5′ mod has to be exercised a bit before this mod is used?
In a nut shell I’m on this page – tickets/scp/admin.php?t=topicautoassign&a=newedit&topic_id=3&dept_id=1
I select a staff and it fails – the DB in the log indicate that the topic_id and dept_id values are not passed along – this is what I would gather from the DB errors listed in logs.
Please assist.
Hi Curt,
Please re-apply the modification above, especially number 5 and 7. There are some lines of code that missing. Now it has already there. Sorry for the inconvenience.
I have the exact problem. MY DB warns me about an error.
I did a clean installation of osticket 1.6 RC5 then i modified the code with ‘Displaying Help Topic in New Ticket Form for Staff in osTicket v1.6 RC5′ an dI checked it worked.
After that i modified again the code with “Add Auto Assigned Ticket Feature into osTicket v1.6 RC5″ and I still get the error.
I did it twice so I am quite sure I did missed any step.
Any ideas?
Thank you
Please re-apply the modification above, especially number 5 and 7. There are some lines of code that missing. Now it has already there.
sam ewangono aku . . .
biar bisa nge ‘show’ yang juga d beri sisipan if clause gmn ??
masalahnya gini . . .
ada dua field database yang salah satu nya di isi maka salah satunya kosong, begitu juga sebalik nya (form php nya memang disuruh inputannya kayak gitu .) . .
gmn pas d show itu field yang kosong tidak d tampilkan tetapi yang ada isinyalah yang di tampilkan . . .
sintaks logikanya kayak gini
if field A == null then show field B
if field B == null then show field A
gmn caranya bos ??? (mohon bantuannya + tolong kirim ke email ku biar bisa lebih mengenal satu sama lain . . )
hi,
i really like this mod, but get a error when adding a rule from admin panel;
[SELECT dept_id,dept_name FROM _department WHERE dept_id=]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
it works ok, because when i add a line in the sql database, it’s ok.
i would really like to be able to add from admin.
can anybody help me?
thanks!
Make sure you have already implemented the Displaying Help Topic in New Ticket Form for Staff mod before implementing that mod above.
@Ruben
hi ruben,
did you fix the issue? got the same errors as you!
thanks a lot,
freddy
I have fixed the causes, please re-apply the modification above, especially number 5 and 7.
indeed the error of speaking above seems to me that you use both GET and POST methods interchangeably.
thanks
hi, im sorry, but i cant find the insert code like “INSERT INTO ost_help_topic_auto_assign…”
Hi walter,
Please re-apply the modification above, especially number 5 and 7. There are some lines of code that missing. Now it has already there. Sorry for that.
I have done this mod and the required mod 2 times over now and continue to get 3 errors in the log each time I try to use it. the ticket never gets assigned.
the error are
Can you give any guidance?
That SQL error caused by the missing dept_id and topic_id values. Make sure you have already implemented the Displaying Help Topic in New Ticket Form for Staff mod before implementing that mod above.
Masino,
Thanks for your great mods and code. I also continue to get the same database errors as mentioned about. I did apply the Displaying Help Topic in New Ticket Form for Staff (and it works) and have also reapplied steps 5 & 7 of this mod.
Do you have any other suggestions?
One thing I just discovered is that after applying the steps again, I don’t get the database errors but the new ticket does not get auto assigned.