I've set up a cron job every friday to email my customers with their ticket activity. Thought I'd share if it is any use to anyone feel free to rip apart.
I've created a file called email.php with this. Please note I've ripped the code apart in genhtml to remove my adverts so it won't be pretty but it gives the idea.
You may also need to specify the location of mail.php and mail/mime.php
<?php
require_once "Mail.php";
include_once('Mail/mime.php');
$dbh=mysql_connect ("server", "db", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db");
$sql="select distinct (email) from ost_ticket ticket ";
$adate=date("Y-m-d",strtotime("-7 days"));
$date=date("d-m-Y",strtotime("-7 days"));
$sql=$sql . "where (status='open' or ticket.closed>='" . $adate . "')";
$sql=$sql . " order by email";
//echo $sql;
// I'm using the simple status mod from the forums so ignore status_arr, the progress column and the work variable if you are not using this.
$status_arr = array("To Be Scheduled","Scheduled","Being Worked On","Waiting for Client","Quotation Sent","Awaiting Payment","Complete");
$rs=mysql_query($sql);
echo "Technical Support Summary sent to: \r\n\r\n";
while($rsa=mysql_fetch_array($rs)){
echo $rsa . ", \r\n";
$str=gethtml($rsa);
emailsupport($rsa,$str);
}
echo "\r\n\r\nBatch Completed";
function gethtml($email){
// I've stripped out my meta tags and company info from the html doc shown below.
$h="";
$h="<!<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">><html xmlns=\"http://www.w3.org/1999/xhtml\" xml=\"en-GB\" lang=\"en-GB\"><head><meta http-equiv=\"Content-Type\" content=\"text/html;charset=utf-8\"/></head>";
$h=$h . "<body \">";
$h=$h . "Support Calls Raised By: " . $email;
$h=$h . "<br/><br/><span style=\"color;font-size;\">This is a list of all your support tickets that are either open or have been completed within the last seven days.";
$h=$h . "<br/>If you would like to amend any of these tickets please click on the Ticket ID and follow the on-screen instructions.";
$h=$h . "<br/><br/>If you wish to raise a new ticket please email. <br/><br/>";
$h=$h . "</span>";
$h=$h . "</td></tr>";
$h=$h . "<tr><th>Ticket ID</th><th>Created</th><th>Department</th><th>Answered</th><th>Subject</th><th>Name</th><th>Status</th><th>Progress</th><th>Completed</th></tr>";
$sql="SELECT ticket.ticket_id,ticket.ticketID,ticket.dept_id,ticket.closed,if(isanswered=false,'No','Yes') as isanswered,ispublic,subject,name,email ,work,dept_name,status,source,priority_id ,";
$sql=$sql . "ticket.created ,count(attach_id) as attachments FROM ost_ticket ticket LEFT JOIN ost_department dept ON ticket.dept_id=dept.dept_id ";
$sql=$sql . "LEFT JOIN ost_ticket_attachment attach ON ticket.ticket_id=attach.ticket_id WHERE email='" . $email . "'";
$adate=date("Y-m-d",strtotime("-7 days"));
$date=date("d-m-Y",strtotime("-7 days"));
$sql=$sql . " and (status='open' or ticket.closed>='" . $adate . "')";
$sql=$sql ." GROUP BY ticket.ticket_id ORDER BY status,ticket.created asc";
$rs1=mysql_query($sql);
$status="ZZZ";
while($rs1a=mysql_fetch_array($rs1)){
if($status!==$rs1a){
if($rs1a=='closed'){
$h=$h . "<tr><td colspan=\"8\">";
$h=$h . "Support Calls Completed Since " . $date;
$h=$h . "</td></tr>";
}
else {
$h=$h . "<tr><td colspan=\"8\">";
$h=$h . "Open Support Calls ";
$h=$h . "</td></tr>";
}
$status=$rs1a;
}
$h=$h . "<tr>";
$h=$h . "<td>";
$h=$h . "<a style=\"font-size;\" href=\"http://blahblah.co.uk/techsupport/view.php?e=" . $rs1a . "&t=" . $rs1a . "\">";
$h=$h . $rs1a;
$h=$h . "</a>";
$h=$h . "</td>";
$h=$h . "<td style=\"font-size;\" >" . date("d/m/Y G",strtotime($rs1a)) . "</td>";
$h=$h . "<td style=\"font-size;\" >" . $rs1a . "</td>";
$h=$h . "<td style=\"font-size;\" >" . $rs1a . "</td>";
$h=$h . "<td style=\"font-size;\" >" . $rs1a . "</td>";
$h=$h . "<td style=\"font-size;\" >" . $rs1a . "</td>";
$h=$h . "<td style=\"font-size;\" >" . $rs1a . "</td>";
if($rs1a=='closed'){
$h=$h . "<td style=\"font-size;\" >Completed</td>";
}
else {
$h=$h . "<td style=\"font-size;\" >" . $status_arr[$rs1a . "</td>";
}
if(date("Y",strtotime($rs1a))>1970 && $rs1a=='closed'){
$h=$h . "<td style=\"font-size;\" >" . date("d/m/Y G",strtotime($rs1a)) . "</td>";
}
else
{
$h=$h . "<td> </td>";
}
$h=$h . "</tr>";
}
$h=$h . "</span></td></tr></table></body></html>";
return $h;
}
function emailsupport($email,$message){
$from = "blahblah@blah.com";
$subject = "Solutions Ltd - Tech Support Issues";
$text = 'Text version of email';
$html = $message;
$crlf = "\n";
$hdrs = array('From' => 'blah@blah.co.uk','Subject' => 'Software Solutions Ltd - Technical Support','Bcc' => 'blah@blah.co.uk');
$mime = new Mail_mime($crlf);
$mime->setTXTBody($text);
$mime->setHTMLBody($html);
$body = $mime->get();
$hdrs = $mime->headers($hdrs);
$mail =& Mail:('mail');
$mail->send($email, $hdrs, $body);
return "";
}
?>
