[MOD] Auto-Assignment Rules

scottro

The following is a MOD that I wanted to have for work and I noticed that several people have requested it in different threads on the forum over the years. This isn't 100% final and I look forward to comments so that I can further tweak it for all of us to use.

Purpose: Auto-assign tickets that are submitted via email based on their from address or Subject. If a ticket is assigned to a staff member it will automatically also be assigned to the department they are in.

Note: I am currently running version 1.6 RC5 on both my test and production servers. Someone please let me know if there are any tweaks needed for it to run on 1.6 ST.

Step 1: Update tickets database with the following;

CREATE TABLE ost_ticket_rules (id TINYINT AUTO_INCREMENT PRIMARY KEY,

isenabled TEXT NOT NULL COLLATE utf8_general_ci,

Category varchar(125) NOT NULL COLLATE utf8_general_ci,

Criteria TEXT NOT NULL COLLATE utf8_general_ci,

Action TEXT NOT NULL COLLATE utf8_general_ci,

Department TEXT NOT NULL COLLATE utf8_general_ci,

Staff TEXT NOT NULL COLLATE utf8_general_ci,

updated DATETIME NOT NULL,

created DATETIME NOT NULL);

Step 2: Create /var/www/tickets/scp/rules.php

<?php

require('staff.inc.php');

$page='';

$answer=null; //clean start.

$nav->setTabActive('rules');

$nav->addSubMenu(array('desc'=>'Rules','href'=>'rules.php','iconclass'=>'premade'));

$nav->addSubMenu(array('desc'=>'New Rule','href'=>'add_rule.php','iconclass'=>'newPremade'));

require_once(STAFFINC_DIR.'header.inc.php');

$query=sprintf("SELECT * FROM ost_ticket_rules;");

$result=mysql_query($query);

// Count table rows

$count=mysql_num_rows($result) or die(mysql_error());;

print "$count rules found";

?>

<table width="100%" border="0" cellspacing="0" cellpadding="0">

<form name="form1" method="POST" action="rules.php">

<tr>

<td>

<table width="100%" border="0" cellspacing="0" cellpadding="5">

<tr>

<td align="center"><strong>ID</strong></td>

<td align="center"><strong>Delete</strong></td>

<td align="center"><strong>Enabled</strong></td>

<td align="center"><strong>Category</strong></td>

<td align="center"><strong>Contains</strong></td>

<td align="center"><strong>Assign To</strong></td>

<td align="center"><strong>Department</strong></td>

<td align="center"><strong>Staff</strong></td>

<td align="center"><strong>Last Updated</strong></td>

</tr>

<?php

$class = 'row1';

$counter = 0;

while($rows=mysql_fetch_array($result)){

?>

<tr class="<?=$class?>">

<td align="center"><?=$rows; ?></td>

<td align="center"><input type="checkbox" name="deleteRule" value="<?=$rows; ?>"></td>

<td align="center"><select name="isenabled"><option value="on" <?if($rows == "on"){print "SELECTED";}?> >On</option><option value="off" <?if($rows == "off"){print "SELECTED";}?>>Off</option></select></td>

<td align="center">

<select name="Category"><option value="subject" <? if($rows == "subject"){print "SELECTED";}?>>Subject</option><option value="email" <? if($rows == "email"){print "SELECTED";}?>>Email</option></select>

</td>

<td align="center"><input name="Criteria" type="text" id="Criteria" value="<? echo $rows; ?>"></td>

<td align="center">

<select name="Action"><option value=""> </option><option value="deptId" <? if($rows == "deptId"){print "SELECTED";}?>>Department</option><option value="staffId" <? if($rows == "staffId"){print "SELECTED";}?>>Staff</option></select>

</td>

<td align="center">

<select name="Department">

<option value=0> </option>

<?

$depts= db_query('SELECT dept_id,dept_name FROM '.DEPT_TABLE.'');

while (list($deptId,$deptName) = db_fetch_row($depts)){?>

<option value="<?=$deptId?>" <? if($deptId == $rows){print SELECTED;}?>><?=$deptName?></option>

<?}?>

</select>

</td>

<td align="center">

<select id="Staff" name="Staff" >

<option value="0" selected="selected"> </option>

<?

$sql=' SELECT staff_id,CONCAT_WS(", ",lastname,firstname) as name FROM '.STAFF_TABLE.

' WHERE isactive=1 AND onvacation=0 ';

$depts= db_query($sql.' ORDER BY lastname,firstname ');

while (list($staffId,$staffName) = db_fetch_row($depts)){?>

<option value="<?=$staffId?>" <? if($staffId == $rows){print SELECTED;}?>><?=$staffName?></option>

<?}?>

</select>

</td>

 <td align="center"><? echo $rows; ?></td> 
</tr>
<?php
        $class = ($class =='row2') ?'row1':'row2';
        $counter = $counter +1;
}?>
<tr>
<td colspan="7" align="right"><input type="submit" name="Submit" class="button" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
$ISENABLED = $_POST;
$CATEGORY = $_POST;
$CRITERIA = $_POST;
$ACTION = $_POST; 
$DEPARTMENT = $_POST; 
$STAFF = $_POST; 
$DELETE = $_POST;
// Check if button name "Submit" is active, do this
if(isset($_POST)){
 for($i=0;$i<$count;$i++){
  $sql1="UPDATE ost_ticket_rules SET isenabled='$ISENABLED', Category='$CATEGORY', Criteria='$CRITERIA', Action='$ACTION', Department='$DEPARTMENT', Staff='$STAFF', updated=NOW() WHERE id='$id'";
  $result=mysql_query($sql1);
if(isset($DELETE)){
   $sql2="DELETE from ost_ticket_rules WHERE id='$DELETE'";
   $result2=mysql_query($sql2);
  }
}?>
<SCRIPT language="JavaScript">
window.location="rules.php";
//
</SCRIPT><?
}mysql_close();
require_once(STAFFINC_DIR.'footer.inc.php');

?>

scottro

Step 3: Create add_rule.php

<?php

require('staff.inc.php');

$page='';

$answer=null; //clean start.

$nav->setTabActive('rules');

$nav->addSubMenu(array('desc'=>'Rules','href'=>'rules.php','iconclass'=>'premade'));

$nav->addSubMenu(array('desc'=>'New Rule','href'=>'add_rule.php','iconclass'=>'newPremade'));

require_once(STAFFINC_DIR.'header.inc.php');

?>

<table width="100%" border="0" cellspacing="1" cellpadding="0">

<form name="form1" method="POST" action="add_rule.php">

<tr>

<td>

<table width="100%" border="0" cellspacing="1" cellpadding="5">

<tr>

<td align="center"><strong>ID</strong></td>

<td align="center"><strong>Enabled</strong></td>

<td align="center"><strong>Category</strong></td>

<td align="center"><strong>Contains</strong></td>

<td align="center"><strong>Assign To</strong></td>

<td align="center"><strong>Department</strong></td>

<td align="center"><strong>Staff</strong></td>

</tr>

<tr>

<td width="50" align="center"><select name="isenabled"><option value="on">On</option><option value="off">Off</option></select></td>

<td align="center">

<select name="Category"><option value="subject">Subject</option><option value="email">Email</option></select>

</td>

<td align="center"><input name="Criteria" type="text" id="Criteria"></td>

<td align="center"><select name="Action"><option value=""> </option><option value="deptId">Department</option><option value="staffId" >Staff</option></select></td>

<td align="center">

<select name="Department">

<option value=0> </option>

<?

$depts= db_query('SELECT dept_id,dept_name FROM '.DEPT_TABLE.'');

while (list($deptId,$deptName) = db_fetch_row($depts)){?>

<option value="<?=$deptId?>" ><?=$deptName?></option>

<?}?>

</select>

</td>

<td align="center">

<select id="Staff" name="Staff" >

<option value="0" selected="selected"> </option>

<?

$sql=' SELECT staff_id,CONCAT_WS(", ",lastname,firstname) as name FROM '.STAFF_TABLE.

' WHERE isactive=1 AND onvacation=0 ';

$depts= db_query($sql.' ORDER BY lastname,firstname ');

while (list($staffId,$staffName) = db_fetch_row($depts)){?>

<option value="<?=$staffId?>" ><?=$staffName?></option>

<?}?>

</select>

</td>

<tr>

<td colspan="7" align="right"><input type="submit" name="Submit" class="button" value="Submit"></td>

</tr>

</table>

</td>

</tr>

</form>

</table>

<?php

$ISENABLED = $_POST;

$CATEGORY = $_POST;

$CRITERIA = $_POST;

$ACTION = $_POST;

$DEPARTMENT = $_POST;

$STAFF = $_POST;

// Check if button name "Submit" is active, do this

if(isset($_POST)){

$sql="INSERT INTO ost_ticket_rules (isenabled, Category, Criteria, Action, Department, Staff, updated, created)

VALUES ('$_POST','$_POST','$_POST','$_POST','$_POST','$_POST', NOW(), NOW())";

if (!mysql_query($sql))

{

die('Error: ' . mysql_error());

}

echo "Rule added";

}?>
<SCRIPT language="JavaScript">
&lt;!<!--
window.location="rules.php";
//
</SCRIPT>&gt;<?
mysql_close();
require_once(STAFFINC_DIR.'footer.inc.php');

?>

Step 4: Modify include/class.ticket.php

Remove the following

//Last minute checks

$priorityId=$priorityId?$priorityId:$cfg->getDefaultPriorityId();

$deptId=$deptId?$deptId:$cfg->getDefaultDeptId();

$ipaddress=$var?$var:$_SERVER;

//We are ready son...hold on to the rails.

$extId=Ticket:();

$sql= 'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.

',ticketID='.db_input($extId).

',dept_id='.db_input($deptId).

',priority_id='.db_input($priorityId).

',email='.db_input($var).

',name='.db_input(Format:($var)).

',subject='.db_input(Format:($var)).

',topic='.db_input(Format:($topicDesc)).

',phone='.db_input($var).

',phone_ext='.db_input($var?$var:'').

',ip_address='.db_input($ipaddress).

',source='.db_input($source);

and replace with

// HACKED //

//Last minute checks

$priorityId=$priorityId?$priorityId:$cfg->getDefaultPriorityId();

$deptId=$deptId?$deptId:$cfg->getDefaultDeptId();

$ipaddress=$var?$var:$_SERVER;

$subject=(Format:($var));

$email=($var);

$qry = sprintf("SELECT * FROM ost_ticket_rules;");

$result=mysql_query($qry);

while ( $row = mysql_fetch_assoc($result)){

$isenabled=$row;

$Category=$row;

$Criteria=$row;

$Action=$row;

$Department=$row;

$Staff=$row;

if( "$isenabled" == "on" )

{

if( "$Category" == "subject" )

{

if (preg_match("/$Criteria/i", $subject))

{

if( "$Action" == "deptId" )

{

$deptId=$Department;

}

elseif( "$Action" == "staffId" )

{

$staffId=$Staff;

$qry = sprintf("SELECT dept_id FROM `ost_staff` WHERE staff_id=$Staff;");

$result=mysql_query($qry);

while ( $row = mysql_fetch_assoc($result)){

$deptId=$row;

}

elseif ( "$Category" == "email" )

{

if (preg_match("/$Criteria/i", $email))

{

if( "$Action" == "deptId" )

{

$deptId=$Department;

}

elseif( "$Action" == "staffId" )

{

$staffId=$Staff;

$qry = sprintf("SELECT dept_id FROM `ost_staff` WHERE staff_id=$Staff;");

$result=mysql_query($qry);

while ( $row = mysql_fetch_assoc($result)){

$deptId=$row;

}

} // End while loop

//We are ready son...hold on to the rails.

$extId=Ticket:();

$sql= 'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.

',ticketID='.db_input($extId).

',priority_id='.db_input($priorityId).

',email='.db_input($var).

',name='.db_input(Format:($var)).

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

',dept_id='.db_input($deptId).

',staff_id='.db_input($staffId).

',topic='.db_input(Format:($topicDesc)).

',phone='.db_input($var).

',phone_ext='.db_input($var?$var:'').

',ip_address='.db_input($ipaddress).

',source='.db_input($source);

// END HACKED

Step 5: Modify class.nav.php

Add the following line wherever in the $tabs lineup you would like to see it

$tabs=array('desc'=>'Rules','href'=>'rules.php','title'=>'Rules');

Step 6: Create rules and test

I have this working on both my test server and now my production osTicket server. All this code was copy and pasted direct from my ssh session. If you run into any errors please first check to make sure that you've followed the steps in order then make sure that nothing errored out because of copy/paste issues.

I look forward to comments, thank you!

scottro

I'm working on getting wildcards to work for this as well, once I get it all figured out I will update the code and make a note of it.

scottro

I wasn't able to get wildcards to work, however, I have changed the code to check if the subject or email CONTAINS the criteria instead of MATCHING the criteria. Its also case insensitive, therefore if you have the following rule:

Enabled = On

Category = Subject

Contains = DNS Request

Assign To = Systems

This will now catch all the following emails instead of just those with the specific subject of 'DNS Request':

DNS Request

DNS request

DNS Request for Bobs Domain

How do I make a DNS request?

etc etc.

Also for email you could set it to capture anything from X domain to be assigned to a particular department or email address. Or have all emails from "postermaster@" go to one dept/staff member, etc etc.

scottro

I found a bug:

When deleting a rule it doesn't just delete the selected rule, it deletes them all. I'm investigating it right now.

scottro

I found a bug:
When deleting a rule it doesn't just delete the selected rule, it deletes them all. I'm investigating it right now.

This has been resolved and the code updated.

scottro

This has been resolved and the code updated.

Bah, just noticed this isn't fully fixed yet. It now deletes the first X rules, X being the count of checkmarks you have selected :

Hang tight, I'll be back...

scottro

Bah, just noticed this isn't fully fixed yet. It now deletes the first X rules, X being the count of checkmarks you have selected :
Hang tight, I'll be back...

Ok, everythings functioning as intended now and the original post has been updated.

scottro

Footers have been added to both rules pages.

sternen

WOW... 284 views and not a single comment.

We don't do submission of ticket by email, but I am starting to rethink that.

scottro

hehe, yea I've actually been kinda surprised by it. The reason I made it was half because I needed it and half because I found several forum topics requesting it or asking if it was already in place. We actually only allow for ticket submission from email but since I've created this I've already got 5 rules in place and it surprisingly saves a lot of monotony if not time.

sternen

I've decided to implement this and was looking through the code. How hard would it be to add Help Topic to criteria that it checks for and assigns automatically depending on content?

Also, does this just check the email Subject line, or the body as well?

scottro

I've decided to implement this and was looking through the code. How hard would it be to add Help Topic to criteria that it checks for and assigns automatically depending on content?
Also, does this just check the email Subject line, or the body as well?

Thats a good idea, I'll check out the Help Topic issue on Monday when I'm back in front of my work PC. At present it checks either the Subject line or the email address based on which criteria you have selected.

sternen

I am betting Subject line will be perfect 99% of the time.

I installed the mod and I am not able to get staff assignments to work. Was there a change in the way staff id's were handled between the version you built this on and the 1.6 stable release?

scottro

Not sure there, I haven't used 1.6ST. I installed it once to take a look at it but its not installed right now and I didn't notice any change. I kind of doubt it as that would be a lot of code changes for no apparent reason. Is the department update working ok and just not the staff? If you can, check your mysql logs and see what is running when a rule is applied.

sternen

Ok - I ran some tests and department assignment works as it should, but staff assignment is not.

Really, to be honest with you, for my purposes and the way our shop is set up department and staff assignment could be handled manually. It is more important to me that I get help topic working. You see, I have a lot of custom fields and I have customized my viewticket to only show certain blocks of those custom fields depending on what help topic is chosen. I am still learning PHP, but have my if/else logic down after finishing that little project. :)

sternen

Scottro - I have tried to determine this, but haven't. Would you be willing to look through the code and see which references to StaffId call directly to the database?

I checked and the heading on the column for 1.6 Stable is definitely "Staff_Id", but I wasn't able to find that at all in your code. Looks like, for some reason, there was a late change to the name of the field in the DB.

Peter

scottro

Scottro - I have tried to determine this, but haven't. Would you be willing to look through the code and see which references to StaffId call directly to the database?
I checked and the heading on the column for 1.6 Stable is definitely "Staff_Id", but I wasn't able to find that at all in your code. Looks like, for some reason, there was a late change to the name of the field in the DB.
Peter

Yup, np. The staff_id stuff is located in the rules.php and add_rule.php files in the following select (dropdown)

In the code its a (mysql) SELECT to retrieve all the staff ids and then its POSTed using PHP to the "Staff" variable and then its finally INSERTed in the final code. If that doesn't help any then just email me at scott (at) sudobash.net

kevinb

Installed and working but....

Installed on 1.6ST and seems to be working ok. However, as I have a few other mods installed the button is now on a second row under the others and looks crappy. Does anyone know how I can enlarge the admin frame so all the tabs are on one line? Thanks!

Kevin

scottro

Can you send me a copy of your file(s) and a screenshot?

scott (at) sudobashnet (dot) net