$techrow = mysql_fetch_array($techquery);
$creatorquery = mysql_query("SELECT * FROM tusers WHERE id LIKE '{$_SESSION['user_id']}'");
$creatorrow = mysql_fetch_array($creatorquery);
$querystate = mysql_query("SELECT name FROM tstates WHERE id LIKE '{$globalrow['state']}'");
$staterow = mysql_fetch_array($querystate);
$querycat = mysql_query("SELECT * FROM tcategory WHERE id LIKE '{$globalrow['category']}'");
$catrow = mysql_fetch_array($querycat);
$querysubcat = mysql_query("SELECT * FROM tsubcat WHERE id LIKE '{$globalrow['subcat']}'");
$subcatrow = mysql_fetch_array($querysubcat);
//text format
$description = str_replace("\n", "<br>", $globalrow['description']);
$resolution = str_replace("\n", "<br>", $globalrow['resolution']);
//Dates conversions
$date_create = date_cnv("{$globalrow['date_create']}");
$date_hope = date_cnv("{$globalrow['date_hope']}");
$date_res = date_cnv("{$globalrow['date_res']}");
//Mail object for states
$qobject = mysql_query("SELECT * FROM tstates WHERE id LIKE '{$globalrow['state']}'");
$robject = mysql_fetch_array($qobject);
$objet = "{$robject['mail_object']} pour le ticket n°{$_GET['id']}: {$globalrow['title']}";
$destinataire = "{$userrow['mail']}";
$emetteur = "{$creatorrow['mail']}";
//interger link parameter
if ($rparameters['mail_link'] == 1) {
$link = ", ou suivez votre ticket sur ce lien: <a href=\"http://{$_SERVER['SERVER_NAME']}\">http://{$_SERVER['SERVER_NAME']}</a>";
} else {
$link = ".";
}
$msg = "\r\n\t<html>\r\n\t\t<head>\r\n\t\t</head>\r\n\t\t<body>\r\n\t\t\t<font face=\"Arial\">\r\n\t\t\t\t<table width=\"820px\" cellspacing=\"0\" >\r\n\t\t\t\t\t<tr bgcolor=\"{$rparameters['mail_color_title']}\" >\r\n\t\t\t\t\t <th><br /><font size=\"4px\" color=\"FFFFFF\"> {$objet} </font><br /><br /></th>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t<tr bgcolor=\"{$rparameters['mail_color_bg']}\" >\r\n\t\t\t\t\t <td>\r\n\t\t\t\t\t\t<br /><br />\r\n\t\t\t\t\t\t{$rparameters['mail_txt']}<br />\r\n\t\t\t\t\t\t<br />\r\n\t\t\t\t\t\t<table border=\"1\" bordercolor=\"0075A4\" cellspacing=\"0\" width=\\800px\">\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td><font color=\"{$rparameters['mail_color_text']}\"><b>Titre:</b></b> {$globalrow['title']}</font></td>\r\n\t\t\t\t\t\t\t\t<td><font color=\"{$rparameters['mail_color_text']}\"><b>Catégorie:</b></b> {$catrow['1']} - {$subcatrow['2']}</td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td width=\"400px\"><font color=\"{$rparameters['mail_color_text']}\"><b>Demandeur:</b></b> {$userrow['lastname']} {$userrow['firstname']}</font></td>\r\n\t\t\t\t\t\t\t\t<td width=\"400px\"><font color=\"{$rparameters['mail_color_text']}\"><b>Technicien en charge:</b> {$techrow['lastname']} {$techrow['firstname']}</font></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td><font color=\"{$rparameters['mail_color_text']}\"><b>Etat:</b> {$staterow['0']}</font></td>\r\n\t\t\t\t\t\t\t\t<td><font color=\"{$rparameters['mail_color_text']}\"><b>Date de la demande:</b> {$date_create}</font></td>\t\r\n\t\t\t\t\t\t\t</tr> \r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td colspan=\"2\"><font color=\"{$rparameters['mail_color_text']}\"><b>Description:</b><br /> {$description}</font></td>\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td colspan=\"2\"><font color=\"{$rparameters['mail_color_text']}\"><b>Résolution:</b><br /> {$resolution}</font></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t\t<tr>\r\n\t\t\t\t\t\t\t\t<td width=\"400px\"><font color=\"{$rparameters['mail_color_text']}\"><b>Date estimée de résolution:</b></b> {$date_hope}</font></td>\r\n\t\t\t\t\t\t\t\t<td width=\"400px\"><font color=\"{$rparameters['mail_color_text']}\"><b>Date de résolution:</b> {$date_res}</font></td>\r\n\t\t\t\t\t\t\t</tr>\r\n\t\t\t\t\t\t</table>\r\n\t\t\t\t\t\t<br /><br /><br /><br />\r\n\t\t\t\t\t\t<hr />\r\n\t\t\t\t\t\tPour toutes informations complémentaires sur votre ticket, vous pouvez joindre {$techrow['firstname']} {$techrow['lastname']} au {$techrow['phone']}\r\n\t\t\t\t\t\t{$link}\r\n\t\t\t\t\t\t<hr />\r\n\t\t\t\t\t </td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t</table>\r\n\t\t\t</font>\r\n\t\t</body>\r\n\t</html>" . "\r\n";
if ($send == 1) {
require "components/PHPMailer_v5.1/class.phpmailer.php";
//Select name of user
$queryuser = mysql_query("SELECT * FROM tusers WHERE id LIKE '{$row['user']}'");
$resultuser = mysql_fetch_array($queryuser);
//Select name of technician
$querytech = mysql_query("SELECT * FROM tusers WHERE id LIKE '{$row['technician']}'");
$resulttech = mysql_fetch_array($querytech);
//Select name of category
$querycat = mysql_query("SELECT * FROM tcategory WHERE id LIKE '{$row['category']}'");
$resultcat = mysql_fetch_array($querycat);
//Select name of subcategory
$queryscat = mysql_query("SELECT * FROM tsubcat WHERE id LIKE '{$row['subcat']}'");
$resultscat = mysql_fetch_array($queryscat);
//cut first letter of firstame
$Fname = substr($resultuser['firstname'], 0, 1);
$Ftname = substr($resulttech['firstname'], 0, 1);
$rowdate = date_cnv($row['date_create']);
//date hope
$img = '';
if (!isset($row['date_hope'])) {
$row['date_hope'] = '';
}
$date_hope = $row['date_hope'];
$querydiff = mysql_query("SELECT DATEDIFF(NOW(), '{$date_hope}') ");
$resultdiff = mysql_fetch_array($querydiff);
if ($resultdiff[0] > 0 && ($row['state'] == '1' || $row['state'] == '2')) {
$img = "<img align=\"left\" border=\"0\" title=\"{$resultdiff['0']} jours de retard\" src=\"./images/clock.png\" />";
}
// Display line color
$bgcolor = "";
//query 30 days
$query15 = mysql_query("SELECT count(*) FROM `tincidents` WHERE TO_DAYS(NOW()) - TO_DAYS(date_create) >= {$rparameters['lign_yellow']} and TO_DAYS(NOW()) - TO_DAYS(date_create) <= 45 and (state LIKE '2' or state LIKE '1') and date_create LIKE '{$row['date_create']}'");