[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)!!!





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

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:

PHP Code:

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


AFTER, ADD:

PHP Code:

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


Open \scp\admin.php, FIND:

PHP Code:

    case 'groups':
        $do=strtolower($_POST['do']); 


BEFORE, ADD:

PHP Code:

    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']=S_A_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 ".S_A_OF." $count ".S_A_SELECTED_TOPICS_DELETED;
                      else
                         $errors['err']=S_A_UNABLE_TO_DELETE_SELECTED_TOPICS;
                   }
                }
                break;
            default:
                $errors['err']=S_A_UNKNOWN_TOPIC_ACTION;
        }    
        break; 


FIND:

PHP Code:

    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; 


REPLACE WITH:

PHP Code:

    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['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']=S_A_UNABLE_TO_FETCH_INFO_ON_TOPICNO.$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']=S_A_UNABLE_TO_FETCH_INFO_ON_TOPICNO.$id;
                  }
                }
                $page=($topic or ($_REQUEST['a']=='newedit' && !$topicID))?'topicautoassign.inc.php':'helptopicsautoassignlist.inc.php';
                break;
        }
        break; 


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

PHP Code:

            <th>Help Topic</th>
            <th>Status</th> 


REPLACE WITH:

PHP Code:

            <th><?php echo (I_S_HELP_HELP_TOPIC); ?></th>
            <th>Auto Assign</th>
          <th><?php echo (I_S_HELP_STATUS); ?></th>

FIND:

PHP Code:

                <td><a href="admin.php?t=topics&id=<?=$row['topic_id']?>"><?=Format::htmlchars(Format::truncate($row['topic'],30))?></a></td>

REPLACE WITH:

PHP Code:

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

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

PHP Code:

          //Any error above is fatal.
        if($errors) { return 0; } 


AFTER, ADD:

PHP Code:

        $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

[masino_sinaga]

Important!!!

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

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.



Enjoy it..., and Cheers!

Best regards,
Masino Sinaga

[masino_sinaga]

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

PHP Code:

          //Any error above is fatal.
        if($errors) { return 0; } 


FIND:

PHP Code:

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

PHP Code:

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



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

[Ron Chandy]

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:

[SELECT staff_id FROM ost_help_topic_auto_assign WHERE dept_id= AND 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 topic_id=' at line 1

[masino_sinaga]

Originally Posted by neogen

I getting following error:

[SELECT staff_id FROM ost_help_topic_auto_assign WHERE dept_id= AND 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 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