Page 1 of 1
2.3 MOD: Auto-assign Categories Based on Piped Email Address
Posted: Tue Sep 27, 2011 9:09 pm
by 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)
colin65 wrote: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 ...
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Wed Oct 12, 2011 6:50 pm
by 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.
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Wed Oct 12, 2011 8:09 pm
by 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
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.
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Wed Oct 12, 2011 8:28 pm
by 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.
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Wed Oct 12, 2011 9:41 pm
by 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!
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Thu Oct 13, 2011 1:11 pm
by 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!
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Fri Oct 14, 2011 3:20 pm
by 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).
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Fri Oct 14, 2011 3:41 pm
by 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.)
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Fri Oct 14, 2011 3:56 pm
by 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.
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Wed Sep 05, 2012 11:01 am
by 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!
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Sat Dec 08, 2012 2:28 pm
by 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
Lisaweb wrote: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)
colin65 wrote: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 ...
Re: 2.3 MOD: Auto-assign Categories Based on Piped Email Add
Posted: Sun Dec 09, 2012 9:47 am
by 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"