[PhpHelpdesk-devel] Enter Tickets via Email

[Matthew C. G. <mi...@ci...>]

Here is a script that allows you to enter tickets to phphelpdesk via an email.
Assumed is that you have phphelpdesk up and running, and that you have php 
compiled for command line scripts.  Also, you need to have qmail running, 
and have an email account that can run this forward script.
For example, I have an email account called helpdesk.  I have a .qmail file 
which contains the command

|/home/helpdesk/mail_to_db.php

Copy this script in, edit the user and the category, put the .qmail file in 
/home/helpdesk, and whenever this user receives email, it will show up 
instantly in the support database.

This will run the command that is needed.
Note that this does not add the email as the user.  All email tickets are 
added under one account.  It would be simple enough to parse out the user's 
email address, and submit it that way, but this is definitely simpler.  It 
might be an idea to create multiple category email addresses, and have 
emails categorized that way.
Follows is the mail_to_db.php script.
You will need to edit the variables for such things as what user do you 
want the email to be added to, what department, company, category, etc..

------------------------------------------
#!/usr/bin/php -q
<?php
#This connects to your database - put your mysql server, user, & pass here
$link = mysql_connect("localhost", "root", "")
	or die ("could not connect\n");
mysql_select_db ("phphelpdesk")
	or die ("could not select database\n");
$fp = fopen("./helplog", "a");

#This makes it so that all emails sent to this email address show up under
#the same user in PHPHelpdesk
$ctgry = "Other";
$tbl = "ticket";
$usr = "admin";
$dpt = "crag";
$loc = "idunno";
$nowtime = date('Y-m-d H:i:s');
$priority = "1";
$e_stat = "REGISTERED";
$e_des = "Ticket Assigned";
#Read the entire email, including headers.  It is being passed to stdin.
#The last 2 lines carve out only the message body, and trim leading and
#trailing whitespaces.
$fd = fopen("php://stdin","r");
while (!feof($fd)) {
$data = fgets($fd, 4096);
$testdata = "$testdata$data";
}
$body = strstr($testdata, "\n\n");
$body = trim ($body);
#This builds the query that adds the ticket to the ticket table.
$qry = "INSERT INTO $tbl SET t_category='$ctgry', t_detail='$body', 
t_user='$usr', t_priority='$priority', t_timestamp_opened='$nowtime', 
t_department='$dpt', t_location='$loc', t_summary='$UFLINE'";
$testresult = fwrite ($fp, $qry);
$testresult = fwrite ($fp, "\n");
#This executes the query that adds the ticket to the ticket table.
$result = mysql_query ($qry)
	or die ("Query Failed\n");
#This saves the Ticket ID created.  It is needed to create an Authorize Event.
$t_id = mysql_insert_id ($link);
#	or die ("Query Tid Failed\n");
$testresult = fwrite ($fp, $t_id);
$testresult = fwrite ($fp, "\n");
$tbl = "events";
#This builds the query that adds a new Authorize Event to the Event table.
#An Authorize Event in the event table is needed for the ticket to show up.
$qry2 = "INSERT INTO $tbl SET t_id='$t_id', s_user='$usr', 
e_assignedto='$usr', e_status='$e_stat', e_description='$e_des'";
$testresult = fwrite($fp, $qry2);
$testresult = fwrite($fp, "\n");
#This executes the query that adds a new Authorize Event to the Event table.
$result = mysql_query ($qry2)
	or die ("Query2 Failed\n");
#This closes the link to the database.
mysql_close ($link);
?>