[MOD] Auto Assigned Ticket

masino_sinaga

Hi,

I made MOD for auto assigned ticket. If client or staff create a new ticket to a certain help topic, then the default staff who has been choosen before from Admin Panel, will be assigned to that ticket automatically.

I also made the MOD in Admin Panel, which will help you to manage which staff you can choose or edit/change/switch as a default staff for a certain auto-assigned help topic. This default staff will automatically be auto-assigned when ticket is being created by client or even by staff.

WARNING!!! Before doing this MOD, please follow my instruction at my #2 post below (scroll your mouse to the #2 post below)

blank

You can also easily delete one or some auto-assigned staff if no longer needed, and this will not remove the staff record from staff table.

Just FYI, you will find in some part of my code there are a lots of constants for language translation, because I implemented multi-languate in my osTicket. You can change that constants to the string based on the constant name or the original version of osTicket. Therefore, please be kind of this. ;)

Before doing this, please BACKUP ALL OF YOUR FILES as I am not responsible for any risk you will get further!

Okay. Let's getting started now.

First: Create new table named "ost_help_topic_auto_assign"

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

`updated` datetime NOT NULL default '0000-00-00 00',

PRIMARY KEY (`auto_assign_id`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Second: Download the attachment file, extract the zip file, and put the .php files to the proper directory: \include\staff\. There are 2 files:

- topicautoassign.inc.php

- helptopicsautoassignlist.inc.php

Third: Time to play with code... :)

Open main.inc.php, FIND:

define('TOPIC_TABLE',TABLE_PREFIX.'help_topic');

AFTER, ADD:

define('TOPIC_AUTO_ASSIGN_TABLE',TABLE_PREFIX.'help_topic_auto_assign');

Open \scp\admin.php, FIND:

case 'groups':

$do=strtolower($_POST);

BEFORE, ADD:

case 'topicautoassign':

if($_POST) {

$staff_id = $_POST;

$cekstaff = db_query('SELECT staff_id FROM '.TOPIC_AUTO_ASSIGN_TABLE.'

WHERE staff_id='.$staff_id.' AND dept_id='.$_POST.'

AND topic_id='.$_POST.'');

$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.','.

'topic_id='.$_POST;

if(!db_query($sql) or !($autoassignid=db_insert_id())) {

$errors='Unable to insert auto-assign help topic';

}

$sql='DELETE FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE dept_id='.$_POST.'

AND topic_id='.$_POST.' 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='Could not remove auto-assign help topic.';

}

header('Location: admin.php?t=helptopicsautoassignlist');

} else {

$errors='No auto-assign help topic selected!';

}

break;

case 'helptopicsautoassignlist':

$do=strtolower($_POST);

switch($do){

case 'mass_process':

if(!$_POST || !is_array($_POST)) {

$errors=S_A_YOU_MUST_SELECT_AT_LEAST_ONE_TOPIC;

}else{

$count=count($_POST);

$ids=implode(',',$_POST);

if($_POST){

$sql='DELETE FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE auto_assign_id IN ('.$ids.')';

if(db_query($sql) && ($num=db_affected_rows()))

$msg="$num ".S_A_OF." $count ".S_A_SELECTED_TOPICS_DELETED;

else

$errors=S_A_UNABLE_TO_DELETE_SELECTED_TOPICS;

}

break;

default:

$errors=S_A_UNKNOWN_TOPIC_ACTION;

}

break;

FIND:

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?$_REQUEST:$_POST) && is_numeric($id)) {

$topic= new Topic($id);

if(!$topic->load() && $topic->getId()==$id) {

$topic=null;

$errors='Unable to fetch info on topic #'.$id;

}

$page=($topic or ($_REQUEST=='new' && !$topicID))?'topic.inc.php':'helptopics.inc.php';

break;

REPLACE WITH:

case 'topics':

case 'topicautoassign':

case 'helptopicsautoassignlist':

require_once(INCLUDE_DIR.'class.topic.php');

$topic=null;

$nav->setTabActive('topics');

$nav->addSubMenu(array('desc'=>S_A_HELP_TOPICS,'href'=>'admin.php?t=topics','iconclass'=>'helpTopics'));

$nav->addSubMenu(array('desc'=>S_A_ADD_NEW_TOPIC,'href'=>'admin.php?t=topics&a=new','iconclass'=>'newHelpTopic'));

$nav->addSubMenu(array('desc'=>I_S_HELP_HELP_TOPIC_AUTO_ASSIGN,'href'=>'admin.php?t=helptopicsautoassignlist','iconclass'=>'helpTopics'));

switch(strtolower($_REQUEST)){

case 'topics':

default:

if(($id=$_REQUEST?$_REQUEST:$_POST) && is_numeric($id)) {

$topic= new Topic($id);

if(!$topic->load() && $topic->getId()==$id) {

$topic=null;

$errors=S_A_UNABLE_TO_FETCH_INFO_ON_TOPICNO.$id;

}

$page=($topic or ($_REQUEST=='new' && !$topicID))?'topic.inc.php':'helptopics.inc.php';

break;

case 'helptopicsautoassignlist':

$page='helptopicsautoassignlist.inc.php';

break;

case 'topicautoassign':

default:

if(($id=$_REQUEST?$_REQUEST:$_POST) && is_numeric($id)) {

$topic= new Topic($id);

if(!$topic->load() && $topic->getId()==$id) {

$topic=null;

$errors=S_A_UNABLE_TO_FETCH_INFO_ON_TOPICNO.$id;

}

$page=($topic or ($_REQUEST=='newedit' && !$topicID))?'topicautoassign.inc.php':'helptopicsautoassignlist.inc.php';

break;

}

break;

Open \include\staff\helptopics.inc.php, FIND:

<th>Help Topic</th>

<th>Status</th>

REPLACE WITH:

<th><?php echo (I_S_HELP_HELP_TOPIC); ?></th>

<th>Auto Assign</th>

<th><?php echo (I_S_HELP_STATUS); ?></th>

FIND:

<td><a href="admin.php?t=topics&id=<?=$row?>"><?=Format:(Format:($row,30))?></a></td>

REPLACE WITH:

<td><a href="admin.php?t=topics&id=<?php echo $row?>"><?php echo Format:(Format:($row,30))?></a></td>

<td><a href="admin.php?t=topicautoassign&a=newedit&topic_id=<?php echo $row ?>&dept_id=<?php echo $row; ?>">Add/Edit Auto Assign</a></td>

Open \include\class.ticket.php, FIND:

//Any error above is fatal.

if($errors) { return 0; }

AFTER, ADD:

$sqlstaff = db_query('SELECT staff_id FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE dept_id='.$deptId.' AND topic_id='.$topicId);

if (db_num_rows($sqlstaff)) {

while (list($staff_id)=db_fetch_row($sqlstaff)) {

$staffid = $staff_id;

}

That's all. I have tested this before, and it works good in mine.

Let me know your feedback. Thanks.

Best regards,

Masino Sinaga

[topicautoassign.inc.zip](https://forum.osticket.com/assets/files/migrated/e/34af52d3e64f65c42caa35f0d7e0925.zip)

[helptopicsautoassignlist.inc.zip](https://forum.osticket.com/assets/files/migrated/8/41789667ae1b76f62c639eeed3663e8.zip)

masino_sinaga

Another important information and screenshot

Important!!!

Before apply the MOD above, make sure you have already done the MOD that I made via these 2 links below:

- ( Help Topic in New Ticket for Client and Staff)

- ( Insert Staff ID Into Ticket Table)

The screenshot below is for monitoring; who are the staffs that have been assigned to available help topic. From this panel, you can also select which staff you want to delete in order not being auto-assigned to the specified help topic.

Alternative way, you can also delete one staff from being auto-assigned and switch him/her to another staff in the same department from the screenshot number two in my first post above.

blank

Enjoy it..., and Cheers!

Best regards,

Masino Sinaga

masino_sinaga

Updated for list and staff validation

Hi,

I updated this MOD by adding new code for validating the staff whether has been moved from one department to the new department.

In my previous code for those of you who has implemented the MOD at my first post above (this code below that we want to update is located in \include\class.ticket.php file after the lines of this code):

//Any error above is fatal.

if($errors) { return 0; }

FIND:

$sqlstaff = db_query('SELECT staff_id FROM '.TOPIC_AUTO_ASSIGN_TABLE.' WHERE dept_id='.$deptId.' AND topic_id='.$topicId);

if (db_num_rows($sqlstaff)) {

while (list($staff_id)=db_fetch_row($sqlstaff)) {

$staffid = $staff_id;

}

REPLACE WITH:

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

After that, I also change the help topic auto-assigned list. Let's take a look at this screenshot below:

blank

You see that there is a new column named "Valid?". This column will tell you which help topic auto-assigned record is valid or not. This column value will "No" or invalid, if the value between "Dept Assigned" is different with "Current Dept" column. "Dept Assigned" is department where the staff exist while he/she was being assigned to this help topic before, meanwhile "Current Dept" is the new or current department of that staff. In other words, we can see that this staff named "Masino Sinaga" has been moved to a new department. His old department is "Data Center", and his new department is: "Fourth Division".

Why does this column exist? Because there is always a possibility a staff being moved to new department from Staff menu in Admin Panel, whereas his/her record in the help_topic_auto_assign table has not been changed automatically! You have to manage this auto-assigned staff from this, because we cannot predict to which new help topic this staff will be assigned in his new department, right? ;)

By updating the code above in this my current post, osTicket will check and compare whether this staff is valid or not. If not valid, then there will be no help topic would be auto-assigned to this staff.

To update this list, please download the attachment file below, extract, and copy this file to \include\staff sub directory. If you have already the old file before, just replace it with this update file.

Hope this will helpful for you. Thanks.

Best regards,

Masino Sinaga

[helptopicsautoassignlist.inc_updated.zip](https://forum.osticket.com/assets/files/migrated/7/e0c1823e6162fea3919600dcb443c85.zip)

Ron Chandy

What do you mean by create a new table ?

That is a wonder MOD I think, Though I have not been able to use it as yet. You have mentioned First create a new table and have given the code. What do you mean by create a new table ?

Please help:

Thanks - Ron

masino_sinaga

Hi Ron,

"Create a new table" means that you have to create first an additional table in your osticket database.

This step you must to do in order this MOD working as good as we want.

I hope this helpful for you.

Best regards,

Masino Sinaga

neogen

I getting following error:

- 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 topic_id=' at line 1

masino_sinaga

Please read again my #1 and #2 post above...

I getting following error:
- 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 topic_id=' at line 1

Please read again my #1 and #2 post above. It should be working now if you follow all instructions above.

Cheers!

Best regards,

Masino Sinaga

neogen

Please read again my #1 and #2 post above. It should be working now if you follow all instructions above.
Cheers!
Best regards,
Masino Sinaga

Thanks..

I have already applied those two MODs as mentioned in post #2

In fact Help topic is working pretty fine..

Dont know how to check the second one (but has been already applied)

eddieburroughs

Unknown Command!

I have installed the mod and when I assign a staff person I get "Unknown command!"

I followed your directions from your website.

http://www.openscriptsolution.com/2009/10/28/add-auto-assigned-ticket-feature-into-osticket-v1-6-rc5(http://www.openscriptsolution.com/2009/10/28/add-auto-assigned-ticket-feature-into-osticket-v1-6-rc5)

Added the mod you mentioned in your intro.

I am running 1.6 RC5

I know I missed something or didn't add somthing someplace but was hoping you could tell me.

Thanks

eddieburroughs

I know I missed something or didn't add somthing someplace but was hoping you could tell me.
Thanks

I found 2 database errors:

Error 1064

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

and

Error 1146

Table 'w8n4him_ostt1.ost_help_topic_auto_assign' doesn't exist

then I looked and realized I added the sql table to an old ticket system and not the current one.

So I added the table and am still getting the "Unknown Command!"

thanks for your help in advance!

underpope

I've installed this mod and the two additional mods that are required for this one to work, and I continue to receive this DB error when submitting a ticket:

SELECT staff_id FROM ost_help_topic_auto_assign WHERE dept_id= AND topic_id=

which looks like it's being thrown in topicautoassign.inc.php; it seems the values of $dept_id and $topic_id are not being passed.

Any thoughts on how to approach this one?

valentin.marzac

errors

gettiinh the same errors

masino_sinaga

Please re-apply the mod

@[deleted],

Please re-apply (this mod) especially the step number 5 and 7. I have found the causes, it seems there are some lines of code that are missing. Now they have already there. Sorry for the inconveniences. ;)

Sincerely,

Masino Sinaga

P.S.: That mod also works fine in 1.6 ST as I have just tested it a few minutes ago.

gulatinaveen

is this MOD for v1.6 ST as well

@ Masino

Hey,

will this MOD work on v1.6ST as well??

Regards

Naveen

ZeroEffect

Still having issues.

@valentin.marzac,
@[deleted],
@[deleted],
Please re-apply (this mod) especially the step number 5 and 7. I have found the causes, it seems there are some lines of code that are missing. Now they have already there. Sorry for the inconveniences. ;)
Sincerely,
Masino Sinaga
P.S.: That mod also works fine in 1.6 ST as I have just tested it a few minutes ago.

I have just installed this MOD using the link above. and I am receiving the following error messages.

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

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

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

This is happening when I try to apply the selected user.

I am going to go through the steps again.

Thanks for your help

ZeroEffect

I am using version 1.6ST It looks like this issue is when the data is read from the data base. The table that the help topics are listed in is not correct.

Still hunting this down.

Thanks

ZeroEffect

I have just installed this MOD using the link above. and I am receiving the following error messages.
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
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
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
This is happening when I try to apply the selected user.
I am going to go through the steps again.
Thanks for your help
ZeroEffect

Ok I have found the problem but don't know how to fix it. Using myphpadmin I ran the above command and got the same message. I ran it again adding a department ID.

SELECT dept_id,dept_name FROM ost_department WHERE dept_id='Engineering'

and it worked perfectly. Looks like the problem is the variable for the "WHERE" statement is empty. I'm trying to locate how to fix that.

stacehamilton

Does anyone have this working fully? I have performed the mod as well as the two prerequisite mods numerous times and still have trouble. Would love to have this capability. thank you.

ZeroEffect

Currently it works for me but....

Does anyone have this working fully? I have performed the mod as well as the two prerequisite mods numerous times and still have trouble. Would love to have this capability. thank you.

Currently this does work for me but I get three emails every time I setup an auto assign or when someone submits a ticket. Telling me about the errors I mentioned above. I'll be able to move forward once this is resolved.

ZeroEffect

*****Update*****

I noticed that I did not do the help topic mod. I followed the steps of the help topic mod and now the auto assign does not work. Side note I could not complete step 2 of the help topic mod. The lines I was asked to change are not in that document. (OST1.6ST) I have gone back and removed the help topic mod and still auto assign is not working. Rolling back to the setup I had before the auto assign mod and try again.

Out of all the MOD this is the one I wanted the most. I hope this can be resolved.

ZeroEffect