osTicket > Auto-Assignment Rules

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 has been tested and is functional with 1.6RC5 AND 1.6ST

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.

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 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['id']; ?></td>-->
<td align="center"><input type="checkbox" name="deleteRule[]" value="<?=$rows['id']; ?>"></td>
<td align="center"><select name="isenabled[]"><option value="on" <?if($rows['isenabled'] == "on"){print "SELECTED";}?> >On</option><option value="off" <?if($rows['isenabled'] == "off"){print "SELECTED";}?>>Off</option></select></td>
<td align="center">
<select name="Category[]"><option value="subject" <? if($rows['Category'] == "subject"){print "SELECTED";}?>>Subject</option><option value="email" <? if($rows['Category'] == "email"){print "SELECTED";}?>>Email</option></select>

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

<select name="Action[]"><option value=""> </option><option value="deptId" <? if($rows['Action'] == "deptId"){print "SELECTED";}?>>Department</option><option value="staffId" <? if($rows['Action'] == "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['Department']){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['Staff']){print SELECTED;}?>><?=$staffName?></option>
                            <?}?>
                            </select>
</td>

<td align="center"><? echo $rows['updated']; ?></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['isenabled'];
$CATEGORY = $_POST['Category'];
$CRITERIA = $_POST['Criteria'];
$ACTION = $_POST['Action'];
$DEPARTMENT = $_POST['Department'];
$STAFF = $_POST['Staff'];
$DELETE = $_POST['deleteRule'];

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

if(isset($_POST['Submit'])){
 for($i=0;$i<$count;$i++){
  $sql1="UPDATE ost_ticket_rules SET isenabled='$ISENABLED[$i]', Category='$CATEGORY[$i]', Criteria='$CRITERIA[$i]', Action='$ACTION[$i]', Department='$DEPARTMENT[$i]', Staff='$STAFF[$i]', updated=NOW() WHERE id='$id[$i]'";
  $result=mysql_query($sql1);
if(isset($DELETE[$i])){
   $sql2="DELETE from ost_ticket_rules WHERE id='$DELETE[$i]'";
   $result2=mysql_query($sql2);
  }
}

?>
<SCRIPT language="JavaScript">
<!--
window.location="rules.php";
//-->
</SCRIPT><?
}

mysql_close();
require_once(STAFFINC_DIR.'footer.inc.php');
?>

Step 3: Create scp/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['isenabled'];
$CATEGORY = $_POST['Category'];
$CRITERIA = $_POST['Criteria'];
$ACTION = $_POST['Action'];
$DEPARTMENT = $_POST['Department'];
$STAFF = $_POST['Staff']; 

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

if(isset($_POST['Submit'])){
$sql="INSERT INTO ost_ticket_rules (isenabled, Category, Criteria, Action, Department, Staff, updated, created)
VALUES ('$_POST[isenabled]','$_POST[Category]','$_POST[Criteria]','$_POST[Action]','$_POST[Department]','$_POST[Staff]', NOW(), NOW())";
if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
echo "Rule added";
}

?>
<!--<SCRIPT language="JavaScript">
<!--
window.location="rules.php";
//-->
</SCRIPT><?

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['ip']?$var['ip']:$_SERVER['REMOTE_ADDR'];

        //We are ready son...hold on to the rails.
        $extId=Ticket::genExtRandID();
        $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['email']).
                ',name='.db_input(Format::striptags($var['name'])).
                ',subject='.db_input(Format::striptags($var['subject'])).
                ',topic='.db_input(Format::striptags($topicDesc)).
                ',phone='.db_input($var['phone']).
                ',phone_ext='.db_input($var['phone_ext']?$var['phone_ext']:'').
                ',ip_address='.db_input($ipaddress).
                ',source='.db_input($source);

and replace with

// BEGIN - sudobash.net Auto-Assignment Rules MOD  //
        //Last minute checks
        $priorityId=$priorityId?$priorityId:$cfg->getDefaultPriorityId();
        $deptId=$deptId?$deptId:$cfg->getDefaultDeptId();
        $ipaddress=$var['ip']?$var['ip']:$_SERVER['REMOTE_ADDR'];

        $subject=(Format::striptags($var['subject']));
        $email=($var['email']);

      $qry = sprintf("SELECT * FROM ost_ticket_rules;");
      $result=mysql_query($qry);
      while ( $row = mysql_fetch_assoc($result)){

      $isenabled=$row['isenabled'];
      $Category=$row['Category'];
      $Criteria=$row['Criteria'];
      $Action=$row['Action'];
      $Department=$row['Department'];
      $Staff=$row['Staff'];

      if( "$isenabled" == "on" )
      {
        if( "$Category" == "subject" )
        {
        if (preg_match("/$Criteria/i", $subject))
           {
             if( "$Action" == "deptId" )
              {
                $deptId=$Department;
              }
             elseif( "$Action" == "staffId" )
              {
                $staffId=$Staff;
                $staffId=preg_replace( '/\n/', '', trim($staffId) );
                 $qry = sprintf("SELECT dept_id FROM `ost_staff` WHERE staff_id=$Staff;");
                 $result=mysql_query($qry);
                 while ( $row = mysql_fetch_assoc($result)){
                  $deptId=$row['dept_id'];
                  }
              }
           }
        }
        elseif ( "$Category"  == "email" )
        {
        if (preg_match("/$Criteria/i", $email))
           {
             if( "$Action" == "deptId" )
              {
                $deptId=$Department;
              }
             elseif( "$Action" == "staffId" )
              {
                $staffId=$Staff;
                $staffId=preg_replace( '/\n/', '', trim($staffId) );
                 $qry = sprintf("SELECT dept_id FROM `ost_staff` WHERE staff_id=$Staff;");
                 $result=mysql_query($qry);
                 while ( $row = mysql_fetch_assoc($result)){
                  $deptId=$row['dept_id'];
                  }
              }
           }
        }
        }
        } // End while loop

        //We are ready son...hold on to the rails.
        $extId=Ticket::genExtRandID();
        $sql=   'INSERT INTO '.TICKET_TABLE.' SET created=NOW() '.
                ',ticketID='.db_input($extId).
                ',priority_id='.db_input($priorityId).
                ',email='.db_input($var['email']).
                ',name='.db_input(Format::striptags($var['name'])).
                ',subject='.db_input($subject).
                ',dept_id='.db_input($deptId).
                ',staff_id='.db_input($staffId).
                ',topic='.db_input(Format::striptags($topicDesc)).
                ',phone='.db_input($var['phone']).
                ',phone_ext='.db_input($var['phone_ext']?$var['phone_ext']:'').
                ',ip_address='.db_input($ipaddress).
                ',source='.db_input($source);

// END - sudobash.net Auto-Assignment Rules MOD  //

1.6ST: If you are on 1.6ST then change the topic line above to the below:

',helptopic='.db_input(Format::striptags($topicDesc)).

Step 5: Modify include/class.nav.php

Add the following line wherever in the $tabs lineup you would like to see it (note that you may cause your tabs to overflow if you put it in with the other Admin only tabs).

$tabs['rules']=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!