/**
* Gets the billable contract ID for a contact, if multiple exist then the first one is choosen
* @author Paul Heaney
* @param int $contactid - The contact ID you want to find the contract for
* @return int the ID of the contract, -1 if not found
*/
function get_billable_contract_id($contactid)
{
global $now;
$return = -1;
$siteid = contact_siteid($contactid);
$sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
$sql .= "WHERE m.servicelevelid = sl.id AND sl.timed = 'yes' AND m.site = {$siteid} ";
$sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
if (mysql_num_rows($result) > 0) {
$return = mysql_fetch_object($result)->id;
}
return $return;
}
/**
* The main "auto_create" function
* The function is specific to rules sent to our clients regarding the naming of
* their email Subject:
* For this reason the plugin is specific but not impossible to adapt
* Clients send their email like this:
* "Hardware [Productname] Serial number Reference"
* or "Software [Softwarename] Version Reference"
* or "Warranty [Productname] Serial number Reference"
* This helps to simplify the creation, and detection of this function
* @author Nico du toit
* @return Returns the newly created incidentid to the inbopund script to
* continue processing
* OR returns nothing and the new update goes to the holding queue as before
*/
function auto_create_incidents($params)
{
unset($GLOBALS['plugin_reason']);
$incidentid = $params['incidentid'];
$contactid = $params['contactid'];
$subject = $params['subject'];
$decoded = $params['decoded'];
$send_email = 1;
global $CONFIG, $dbIncidents, $now;
debug_log("incident ID : " . $incidentid . " \n Contactid: " . $contactid . "\n" . "Subject : " . $subject);
if ($incidentid > 0) {
return $incidentid;
}
if (in_array($contactid, $CONFIG['auto_create_contact_exclude'])) {
debug_log("For this client : " . $contactid . " autocreate is forbidden! see the config file");
$GLOBALS['plugin_reason'] = 'Contact BLOCKED';
return;
}
if ($contactid < 1) {
$GLOBALS['plugin_reason'] = 'Contact not in DataBase';
return;
}
$cc = find_cc_decoded($decoded);
if (stristr($cc, $CONFIG['support_email'])) {
debug_log("Support was in the copy of the email!!");
$GLOBALS['plugin_reason'] = 'Support in CC of email';
return;
}
debug_log("Redirecting to function checking for duplicates ... ");
//Check if duplicates exists in the incidents DB
$create_incident = check_for_duplicates($subject, $contactid);
if ($create_incident == "NO") {
debug_log("case 0: more than 1 duplicate");
$GLOBALS['plugin_reason'] = 'Possible DUPLICATE';
return;
}
if ($create_incident != "YES" && $create_incident != "NO") {
debug_log("case 1: only 1 duplicate");
debug_log("The duplicate incidentID =: " . $create_incident);
return $create_incident;
}
if ($create_incident == "YES") {
debug_log("case 2: No duplicates found");
debug_log("Proceeding to - Auto create");
$ccemail = $cc;
$origsubject = mysql_real_escape_string($subject);
$subject = strtolower($subject);
//Check if any of our keywords exists TODO: Need to make the keywords
//configurable in the config file
$warranty = preg_match("/warranty|warrantee|waranty|diagnostic/i", $subject);
$hard = preg_match("/hardware|hard/i", $subject);
$soft = preg_match("/soft|software/i", $subject);
if ($warranty == 1 && $hard == 0 && $soft == 0) {
$case = 0;
debug_log("Match for Warranty in : " . $subject);
} elseif ($hard == 1 && $soft == 0 && $warranty == 0) {
$case = 1;
debug_log("Match for Hardware in : " . $subject);
} elseif ($soft == 1 && $hard == 0 && $warranty == 0) {
$case = 2;
debug_log("Match for Software in : " . $subject);
} else {
$case = 3;
debug_log("NO match found for any keyword in : " . $subject);
}
switch ($case) {
case 0:
debug_log("Case type Warranty : ");
$product = 9;
//Hardware
//$software = 41;//Warranty clam
$software = find_tags_in_subject($subject);
debug_log("The software tag returned is : " . $software);
if (!$software || !in_array($software, $CONFIG['auto_create_warranty_include'])) {
debug_log("The soft is not in the accepted warranty list or is 0 :");
$software = 41;
//if no tags are found use the normal warranty claim
}
$servicelevel = $CONFIG['default_service_level'];
$siteid = contact_siteid($contactid);
$sql = "SELECT id FROM `sit_maintenance` WHERE site='{$siteid}' AND product='{$product}' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$row = mysql_fetch_row($result);
$contrid = $row[0];
//Create the incident based on the info we have
$incidentid = '';
$incidentid = create_incident($origsubject, $contactid, $servicelevel, $contrid, $product, $software, $priority = 1, $owner = 0, $status = 1, $productversion = '', $productservicepacks = '', $opened = '', $lastupdated = '');
debug_log("Incident ID created : " . $incidentid);
debug_log("CC address(es) found : " . $ccemail);
//If we have some cc addresses, then we can update them into the case
if ($ccemail) {
$sql = "UPDATE `{$dbIncidents}` ";
$sql .= "SET ccemail='{$ccemail}', lastupdated='{$now}' WHERE id='{$incidentid}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
if (!$result) {
debug_log("Update to the incident cc email succesfull!!");
}
}
if ($incidentid > 0) {
// Insert the first SLA update, this indicates the start of an incident
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$sit[2]}', '1', 'show', 'opened','The incident is open and awaiting action.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
// Insert the first Review update, this indicates the review period of an incident has started
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reviewmet', '{$now}', '{$sit[2]}', '1', 'hide', 'opened','')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_CREATED', array('incidentid' => $incidentid, 'sendemail' => $send_email));
debug_log("Succesfully created incident: " . $incidentid);
//Insert the initial response as we see the first email as the initial response
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$owner}', '1', 'show', 'initialresponse','The Initial Response has been made by the automated tracker email.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
}
//In case we have no incident ID it means the function failed - FALSE returned by function
if ($incidentid == FALSE) {
debug_log("Incident auto create failed: " . $subject);
return;
}
$send_email = 1;
$owner = suggest_reassign_userid($incidentid, $exceptuserid = 0);
if ($owner > 0) {
//Update owner in incidents
$sql = "UPDATE `sit_incidents` SET owner='{$owner}', lastupdated='{$now}' WHERE id='{$incidentid}'";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_ASSIGNED', array('userid' => $owner, 'incidentid' => $incidentid));
// add update
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, nextaction) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reassigning', '{$now}', '{$owner}', '1', '{$nextaction}')";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
debug_log("Incident re-assigned to: " . $owner);
}
return $incidentid;
case 1:
debug_log("Case type Hardware : ");
$product = 9;
//Hardware product
//Try to recover the product name that is in the email subject (only if the sender followed the rules!)
$recovprod = trim(recup_prodName($subject, '[', ']'));
if ($recovprod) {
$prodword = $recovprod;
} elseif (!$recovprod) {
//$prodword = 'HARDWARE NOT AUTOMATICALLY RECOGNISED';
$prodword = software_name(find_tags_in_subject($subject));
if (!$prodword) {
$GLOBALS['plugin_reason'] = 'Hardware [Skill-Product not set]';
return;
}
}
$sql = "SELECT LOWER(id) FROM `sit_software` WHERE name LIKE '%{$prodword}%' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$numresults = mysql_num_rows($result);
$row = mysql_fetch_row($result);
//No match found
if ($numresults == 0) {
//Set the software to a precreated value in the DB
//$software = 53;
$GLOBALS['plugin_reason'] = 'Hardware [Skill-Product not set]';
return;
}
//Multiple matches found, take the first one
//TODO: Improve this as it is not that accurate
if ($numresults > 0) {
$software = $row[0];
}
$servicelevel = $CONFIG['default_service_level'];
$siteid = contact_siteid($contactid);
//Find the hardware contract for this contact
$sql = "SELECT id FROM `sit_maintenance` WHERE site='{$siteid}' AND product='{$product}' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$row = mysql_fetch_row($result);
$contrid = $row[0];
/*TODO: This was not working correctly, but can be fixed i am sure...
* The idea is to change the priority according to the product or skill
* if ($software == 4||5||6||7||8||13||17||18||37||45||48)
{
$priority = 3;
}
else
{
$priority = 2;
}
echo "CONTRACT";
echo $contrid;
echo "<br>";
//Allthese are created as priority medium - Need still to fix the above
//$priority = 2;*/
$incidentid = '';
$incidentid = create_incident($origsubject, $contactid, $servicelevel, $contrid, $product, $software, $priority = 2, $owner = 0, $status = 1, $productversion = '', $productservicepacks = '', $opened = '', $lastupdated = '');
debug_log("Incident ID created : " . $incidentid);
debug_log("CC address(es) found : " . $ccemail);
//If we have some cc addresses, then we can update them into the case
if ($ccemail) {
$sql = "UPDATE `{$dbIncidents}` ";
$sql .= "SET ccemail='{$ccemail}', lastupdated='{$now}' WHERE id='{$incidentid}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
if (!$result) {
debug_log("Update to the incident cc email succesfull!!");
}
}
// If we have an incident id - we can now do the updates
if ($incidentid > 0) {
// Insert the first SLA update, this indicates the start of an incident
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$sit[2]}', '1', 'show', 'opened','The incident is open and awaiting action.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
// Insert the first Review update, this indicates the review period of an incident has started
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reviewmet', '{$now}', '{$sit[2]}', '1', 'hide', 'opened','')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_CREATED', array('incidentid' => $incidentid, 'sendemail' => $send_email));
debug_log("Succesfully created incident: " . $incidentid);
//Insert the initial response as we see the first email as the initial response
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$owner}', '1', 'show', 'initialresponse','The Initial Response has been made by the automated tracker email.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
}
//In case we have no incident ID it means the function failed - FALSE returned by function
if ($incidentid == FALSE) {
debug_log("Incident auto create failed: " . $subject);
return;
}
$send_email = 1;
$owner = suggest_reassign_userid($incidentid, $exceptuserid = 0);
//update the Db with the suggested user to reassign to
if ($owner > 0) {
$sql = "UPDATE `sit_incidents` SET owner='{$owner}', lastupdated='{$now}' WHERE id='{$incidentid}'";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_ASSIGNED', array('userid' => $owner, 'incidentid' => $incidentid));
// add update
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, nextaction) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reassigning', '{$now}', '{$owner}', '1', '{$nextaction}')";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
debug_log("Incident re-assigned to: " . $owner);
}
return $incidentid;
case 2:
debug_log("Case type Software : ");
$product = 10;
//Software product
//Try to recover the product name that is in the email subject (only if the sender followed the rules!)
$recovprod = trim(recup_prodName($subject, '[', ']'));
if ($recovprod) {
$prodword = $recovprod;
} elseif (!$recovprod) {
//$prodword = 'SOFTWARE NOT AUTOMATICALLY RECOGNISED';
$prodword = software_name(find_tags_in_subject($subject));
if (!$prodword) {
$GLOBALS['plugin_reason'] = 'Software [Skill-Product not set]';
return;
}
}
$sql = "SELECT LOWER(id) FROM `sit_software` WHERE name LIKE '%{$prodword}%' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$numresults = mysql_num_rows($result);
$row = mysql_fetch_row($result);
//No match found
if ($numresults == 0) {
//$software = 54;
$GLOBALS['plugin_reason'] = 'Software [Skill-Product not set]';
return;
}
//Multiple matches found, take the first one
//TODO: Improve this as it is not that accurate
if ($numresults > 0) {
$software = $row[0];
}
$servicelevel = $CONFIG['default_service_level'];
$siteid = contact_siteid($contactid);
//Find the software contract for this contact
$sql = "SELECT id FROM `sit_maintenance` WHERE site='{$siteid}' AND product='{$product}' ";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_WARNING);
}
$row = mysql_fetch_row($result);
$contrid = $row[0];
/*TODO: This was not working correctly, but can be fixed i am sure...
* The idea is to change the priority according to the product or skill
echo $contrid;
echo "<br>";
if ($software == 54||36||38)
{
$priority = 1;
}
else
{
$priority = 2;
}
//Allthese are created as priority medium - Need still to fix the above
//$priority = 2;*/
$incidentid = '';
$incidentid = create_incident($origsubject, $contactid, $servicelevel, $contrid, $product, $software, $priority = 2, $owner = 0, $status = 1, $productversion = '', $productservicepacks = '', $opened = '', $lastupdated = '');
debug_log("Incident ID created : " . $incidentid);
debug_log("CC address(es) found : " . $ccemail);
//If we have some cc addresses, then we can update them into the case
if ($ccemail) {
$sql = "UPDATE `{$dbIncidents}` ";
$sql .= "SET ccemail='{$ccemail}', lastupdated='{$now}' WHERE id='{$incidentid}'";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
if (!$result) {
debug_log("Update to the incident cc email succesfull!!");
}
}
if ($incidentid > 0) {
// Insert the first SLA update, this indicates the start of an incident
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$sit[2]}', '1', 'show', 'opened','The incident is open and awaiting action.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
// Insert the first Review update, this indicates the review period of an incident has started
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reviewmet', '{$now}', '{$sit[2]}', '1', 'hide', 'opened','')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_CREATED', array('incidentid' => $incidentid, 'sendemail' => $send_email));
debug_log("Succesfully created incident: " . $incidentid);
//Insert the initial response as we see the first email as the initial response
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, customervisibility, sla, bodytext) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'slamet', '{$now}', '{$owner}', '1', 'show', 'initialresponse','The Initial Response has been made by the automated tracker email.')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
}
//In case we have no incident ID it means the function failed - FALSE returned by function
if ($incidentid == FALSE) {
debug_log("Incident auto create failed: " . $subject);
return;
}
$send_email = 1;
$owner = suggest_reassign_userid($incidentid, $exceptuserid = 0);
//Update the Db with the suggested owner
if ($owner > 0) {
$sql = "UPDATE `sit_incidents` SET owner='{$owner}', lastupdated='{$now}' WHERE id='{$incidentid}'";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
trigger('TRIGGER_INCIDENT_ASSIGNED', array('userid' => $owner, 'incidentid' => $incidentid));
// add update
$sql = "INSERT INTO `sit_updates` (incidentid, userid, type, timestamp, currentowner, currentstatus, nextaction) ";
$sql .= "VALUES ('{$incidentid}', '{$sit[2]}', 'reassigning', '{$now}', '{$owner}', '1', '{$nextaction}')";
$result = mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
debug_log("Incident re-assigned to: " . $owner);
}
return $incidentid;
case 3:
$GLOBALS['plugin_reason'] = 'INCORRECTLY formatted';
debug_log("There was no reference found for the correct skill in: " . $subject);
return;
}
}
}
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
if (!empty($inventory) and $inventory != 0) {
$sql = "INSERT INTO `{$dbLinks}`(linktype, origcolref, linkcolref, direction, userid) ";
$sql .= "VALUES(7, '{$incidentid}', '{$inventory}', 'left', '{$sit[2]}')";
mysql_query($sql);
if (mysql_error()) {
trigger_error("MySQL Query Error " . mysql_error(), E_USER_ERROR);
}
}
plugin_do('incident_created');
// Decrement free support, where appropriate
if ($type == 'free') {
decrement_free_incidents(contact_siteid($contactid));
plugin_do('incident_created_site');
} else {
// decrement contract incident by incrementing the number of incidents used
increment_incidents_used($maintid);
plugin_do('incident_created_contract');
}
$html .= "<h3>{$strIncident}: {$incidentid}</h3>";
$html .= "<p align='center'>";
$html .= sprintf($strIncidentLoggedEngineer, $incidentid);
$html .= "</p>\n";
$suggested_user = suggest_reassign_userid($incidentid);
trigger('TRIGGER_INCIDENT_CREATED', array('incidentid' => $incidentid, 'sendemail' => $send_email));
if ($CONFIG['auto_assign_incidents']) {
html_redirect("incident_add.php?action=reassign&userid={$suggested_user}&incidentid={$incidentid}");
exit;
}
if (!$result) {
$addition_errors = 1;
$addition_errors_string .= "<p class='error'>Update of incident failed</p>\n";
}
if ($addition_errors == 0) {
// dump details to incident update
if ($oldtitle != $title) {
$header .= "Title: {$oldtitle} -> <b>{$title}</b>\n";
}
if ($oldcontact != $contact) {
$contactname = contact_realname($contact);
$contactsite = contact_site($contact);
$header .= "Contact: " . contact_realname($oldcontact) . " -> <b>{$contactname}</b>\n";
$maintsiteid = maintenance_siteid(incident_maintid($id));
if ($maintsiteid > 0 and contact_siteid($contact) != $maintsiteid) {
$maintcontactsite = site_name($maintsiteid);
$header .= "Assigned to <b>{$contactname} of {$contactsite}</b> on behalf of {$maintcontactsite} (The contract holder)\n";
}
}
if ($oldexternalid != $externalid) {
$header .= "External ID: ";
if ($oldexternalid != '') {
$header .= $oldexternalid;
} else {
$header .= "None";
}
$header .= " -> <b>";
if ($externalid != '') {
$header .= $externalid;
} else {
