2.3 MOD: Auto-assign Categories Based on Piped Email Address

[Lisaweb]

Colin65 created this excellent Mod, which I found buried in a closed topic. It is way too good to go unnoticed and therefore unused, so I am re-posting it here. In the original mod he also edited email_parser.php to correct some html issues, but that's no longer an issue in the final release, as far as I can see. I implemented it on my system and it is working perfectly.

Enjoy!

Auto-assign Categories Based on Piped Email Address

Subject: HESK e-mail piping public BETA (test version)

For the benefit of anyone wanting to duplicate my email piping hack ... here is my modified code. Please be gentle .. I'm not a PHP programmer so if you have fixes or improvements I'd really appreciate them.

For some reason I could not make my new code work if I included the hesk_dbEscape wrapper around the column names. Probably missed something simple so, if you're more familiar with PHP than I am, please post a modification that works.

However, this has been working reliably for me for a while now ..

This is the modified section of /inc/mail/hesk_pipe.php ..

Code: Select all

/* parse the incoming e-mail */
$results = parser();

/* Variables */
$categorymail = $results['to'][0]['address'];
if ($categorymail) {
   $sql = "SELECT `category_id` FROM `".$hesk_settings['db_pfix']."category_email` WHERE `email` = '".$categorymail."' LIMIT 1";
   $result = hesk_dbQuery($sql);
}

if ($myresult = hesk_dbFetchAssoc($result)) {
   if ($myresult['category_id']) {
      $tmpvar['category'] = $myresult['category_id'];
   }
}
else {
   $tmpvar['category'] = 1;
}

$tmpvar['name']	    = hesk_input($results['from'][0]['name']) or $tmpvar['name'] = $hesklang['unknown'];
$tmpvar['email']	= hesk_validateEmail($results['from'][0]['address'],'ERR',0);
$tmpvar['priority'] = 3;
$tmpvar['subject']  = hesk_input($results['subject']) or $tmpvar['subject'] = '['.$hesklang['unknown'].']';
$tmpvar['message']  = hesk_input($results['message']);
$_SERVER['REMOTE_ADDR'] = $hesklang['unknown'];
$IS_REPLY = 0;
$trackingID = '';

For this code to work you'll need to create the category_email table in your Hesk database (don't forget to add your hesk prefix - default naming would be hesk_category_email). I did this via cpanel / phpmyadmin (at the bottom of the "structure" tab). You'll need two fields in your table.

category_id (smallint) - this is your primary key
email (varchar(150)) - this holds the email address (make this whatever length you feel is appropriate).

I also added a unique index on the email field so that each email address can only be associated with a single category.


I hope this helps ...

[Lisaweb]

Would anyone out there be willing to take mercy on me, and help me see how I can also add assigning the User to this mod?

It seems simple enough. I know how to add another column in the database table, with the user's ID.

Example:
category_email_id,category_id,email,user_id
1,7,ecommerce@support.com,1
2,5,techsupport@support.com,2
3,2,orders@support.com,3

That's the easy part.

I just need to know how to add the php in the above mod post so that it also assigns not just the category, but the user as well.

I am begging for mercy here. And I am happy to throw a few beers around for the effort.

[Klemen]

Don't buy any beers, I'm only helping because a) you shared a lot of mods and b) quite a few people asked for something like this

Quick, easy and dirty solution would be to simply hard-code category binds into the hesk_autoAssignTicket function (inc/common.inc.php).

Just below

Code: Select all

$autoassign_owner = array(); 

add something like

Code: Select all

	$assign_to = 0;

	switch ($ticket_category)
	{
    	case 1:				// If category ID is 1 ...
		$assign_to = 12;	// Assign to user ID 12
		break;
    	case 2:				// If category ID is 2 ...
		$assign_to = 3;		// Assign to user ID 3
		break;
    	case 5:				// If category ID is 5 ...
		$assign_to = 6;		// Assign to user ID 6
		break;
	}

	// If $assign_to has a value respect it...
	if ($assign_to)
	{
		$sql = "SELECT `id`,`user`,`name`,`email`,`notify_assigned` 
			FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."users` 
			WHERE `id`=".hesk_dbEscape($assign_to)." LIMIT 1";
		$res = hesk_dbQuery($sql);

		if (hesk_dbNumRows($res) == 1)
		{
			$autoassign_owner = hesk_dbFetchAssoc($res);
			return $autoassign_owner;
		}
	}

This should assign tickets to selected user in a category. If no preference for the category is set in the switch statement it will continue using original auto-assign functionality.

I haven't tested this, please let us know if it works for you.

[Klemen]

To expand the idea a bit further - if you want to assign several users to a category you would use this in the switch statement:

Code: Select all

case 1:
	$possible = array(3,5,7,13);
	$assign_to = $possible[array_rand($possible)];
	break;

The above code would randomly assign tickets from category ID 1 to users with IDs 3, 5, 7 and 13.

[Lisaweb]

Thanks sooo much Klemen!

I will test this tonight (when no one is using the system) and get back to you on it.

Thanks again!

[Lisaweb]

Hi Klemen,

I added the mod, but it didn't work. No errors, but no auto-assign either. The only thing I changed is the code below:

Code: Select all

 switch ($ticket_category)
      {
           case 2:            // If category ID is Orders ...
          $assign_to = 5;   // Assign to user Mick
          break;
           case 3:            // If category ID is CS ...
          $assign_to = 5;      // Assign to user Mick
          break;
           case 5:            // If category ID is Tech Support ...
          $assign_to = 4;      // Assign to user Ray
          break;
					 case 7:            // If category ID is Ecommerce ...
          $assign_to = 1;      // Assign to user Lisa
          break;	
       }

Could it be that I need to put this into the hesk_pipe.php instead? (I am using piping). If so, where should I insert it?

Thanks again!

[Klemen]

I just tested it myslef and seems to work fine for me.

Check your WHERE clause in the code, it should be

Code: Select all

WHERE `id`=".hesk_dbEscape($assign_to)." LIMIT 1

- check if you have $assign_to or $id (I originally posted $id but changed to $assign_to after a few minutes when I noticed the error).

[Lisaweb]

Okay, but could it also be that I need to put this into the hesk_pipe.php instead? (Cause I am using piping, and this is where I put the original code in this mod.)

[Klemen]

No, it should go to common.inc.php, as the piping module uses the auto-assign function from common.inc.php.

Try modifying common.inc.php again, then test it by submitting the web form. If it works, try with email piping, if not, something is wrong in the code.

[johnpuddephatt]

Has anyone had any success doing the same thing but with POP3 fetching rather than email piping?

I'm with 1&1 and had no joy setting up email piping with them, so resorted to POP3 instead. I'm new to Hesk but I gather that I'd need to be looking at hesk_pop3.php rather than hesk_pipe.php and thought the above code might work fine - but thought I'd check to see if anyone had tried it.

Thanks!

[Lisaweb]

Hi Klemen,

I'd like to impliment this mod on the current version, 2.4.1 - but I know there have been lots of changes since then. Is there any updates I need to make, or do you think it will work as coded?

Thanks!
Lisa

Colin65 created this excellent Mod, which I found buried in a closed topic. It is way too good to go unnoticed and therefore unused, so I am re-posting it here. In the original mod he also edited email_parser.php to correct some html issues, but that's no longer an issue in the final release, as far as I can see. I implemented it on my system and it is working perfectly.

Enjoy!

Auto-assign Categories Based on Piped Email Address

Subject: HESK e-mail piping public BETA (test version)

For the benefit of anyone wanting to duplicate my email piping hack ... here is my modified code. Please be gentle .. I'm not a PHP programmer so if you have fixes or improvements I'd really appreciate them.

For some reason I could not make my new code work if I included the hesk_dbEscape wrapper around the column names. Probably missed something simple so, if you're more familiar with PHP than I am, please post a modification that works.

However, this has been working reliably for me for a while now ..

This is the modified section of /inc/mail/hesk_pipe.php ..

Code: Select all

/* parse the incoming e-mail */
$results = parser();

/* Variables */
$categorymail = $results['to'][0]['address'];
if ($categorymail) {
   $sql = "SELECT `category_id` FROM `".$hesk_settings['db_pfix']."category_email` WHERE `email` = '".$categorymail."' LIMIT 1";
   $result = hesk_dbQuery($sql);
}

if ($myresult = hesk_dbFetchAssoc($result)) {
   if ($myresult['category_id']) {
      $tmpvar['category'] = $myresult['category_id'];
   }
}
else {
   $tmpvar['category'] = 1;
}

$tmpvar['name']	    = hesk_input($results['from'][0]['name']) or $tmpvar['name'] = $hesklang['unknown'];
$tmpvar['email']	= hesk_validateEmail($results['from'][0]['address'],'ERR',0);
$tmpvar['priority'] = 3;
$tmpvar['subject']  = hesk_input($results['subject']) or $tmpvar['subject'] = '['.$hesklang['unknown'].']';
$tmpvar['message']  = hesk_input($results['message']);
$_SERVER['REMOTE_ADDR'] = $hesklang['unknown'];
$IS_REPLY = 0;
$trackingID = '';

For this code to work you'll need to create the category_email table in your Hesk database (don't forget to add your hesk prefix - default naming would be hesk_category_email). I did this via cpanel / phpmyadmin (at the bottom of the "structure" tab). You'll need two fields in your table.

category_id (smallint) - this is your primary key
email (varchar(150)) - this holds the email address (make this whatever length you feel is appropriate).

I also added a unique index on the email field so that each email address can only be associated with a single category.


I hope this helps ...

[Klemen]

I'm afraid such modifications are out of the scope of my support.

The code has indeed changed in 2.4.1 and has been moved to "inc/pipe_functions.inc.php"