function UpdateLastParamsN($ticketID, $dateType, $recalculateSupportDeadline = true, $setReopenDefault = true)
{
$err_mess = CAllTicket::err_mess() . "<br>Function: UpdateLastParamsN<br>Line: ";
global $DB, $USER;
$ticketID = intval($ticketID);
if ($ticketID <= 0) {
return;
}
$arSupportTeam = CTicket::GetSupportTeamAndAdminUsers();
$arFields = array("LAST_MESSAGE_DATE" => "null", "LAST_MESSAGE_USER_ID" => "null", "LAST_MESSAGE_GUEST_ID" => "null", "LAST_MESSAGE_SID" => "null", "D_1_USER_M_AFTER_SUP_M" => "null", "ID_1_USER_M_AFTER_SUP_M" => "null", "LAST_MESSAGE_BY_SUPPORT_TEAM" => "'Y'");
if ($setReopenDefault) {
$arFields["REOPEN"] = "'N'";
}
$DB->StartUsingMasterOnly();
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.ID,\n\t\t\t\tT.SLA_ID,\n\t\t\t\tT.DATE_CLOSE,\n\t\t\t\t" . $DB->DateToCharFunction("T.DEADLINE_SOURCE_DATE", "FULL") . " DEADLINE_SOURCE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.D_1_USER_M_AFTER_SUP_M", "FULL") . " DATE_OLD,\n\t\t\t\tT.IS_OVERDUE,\n\t\t\t\tSLA.RESPONSE_TIME_UNIT,\n\t\t\t\tSLA.RESPONSE_TIME,\n\t\t\t\tSLA.NOTICE_TIME_UNIT,\n\t\t\t\tSLA.NOTICE_TIME\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t\tINNER JOIN b_ticket_sla SLA\n\t\t\t\t\tON T.SLA_ID = SLA.ID\n\t\t\t\t\t\tAND T.ID = {$ticketID}\n\t\t\t";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
$arTicket = $rs->Fetch();
if (!$arTicket) {
$DB->StopUsingMasterOnly();
return;
}
$arMessagesAll = array();
$arLastMess = null;
$arFirstUserMessAfterSupportMess = null;
$allTime = 0;
$messages = 0;
$messAfterSupportMess = true;
$strSql = "\n\t\t\tSELECT\n\t\t\t\tID,\n\t\t\t\t" . $DB->DateToCharFunction("DATE_CREATE", "FULL") . " DATE_CREATE,\n\t\t\t\tOWNER_USER_ID,\n\t\t\t\tOWNER_GUEST_ID,\n\t\t\t\tOWNER_SID,\n\t\t\t\tTASK_TIME,\n\t\t\t\tIS_OVERDUE,\n\t\t\t\tIS_HIDDEN,\n\t\t\t\tNOT_CHANGE_STATUS\n\t\t\tFROM\n\t\t\t\tb_ticket_message\n\t\t\tWHERE\n\t\t\t\tTICKET_ID={$ticketID}\n\t\t\tAND(NOT(IS_LOG='Y'))\n\t\t\tORDER BY\n\t\t\t\tC_NUMBER\n\t\t\t";
//NOT_CHANGE_STATUS
//IS_HIDDEN
//IS_OVERDUE
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
$DB->StopUsingMasterOnly();
while ($arM = $rs->Fetch()) {
$arMessagesAll[] = $arM;
if ($arM["IS_OVERDUE"] !== 'Y') {
if ($arM["IS_HIDDEN"] !== 'Y') {
if ($arM["NOT_CHANGE_STATUS"] !== 'Y') {
$arLastMess = $arM;
}
$messages++;
}
$allTime += intval($arM["TASK_TIME"]);
}
if ($arM["IS_HIDDEN"] !== 'Y' && $arM["NOT_CHANGE_STATUS"] !== 'Y') {
if (in_array(intval($arM["OWNER_USER_ID"]), $arSupportTeam)) {
$arFirstUserMessAfterSupportMess = null;
$messAfterSupportMess = true;
} elseif ($messAfterSupportMess) {
$arFirstUserMessAfterSupportMess = $arM;
$messAfterSupportMess = false;
}
}
}
if ($arLastMess !== null) {
$arFields["LAST_MESSAGE_USER_ID"] = $arLastMess["OWNER_USER_ID"];
//if ($changeLastMessageDate)
//{
$arFields["LAST_MESSAGE_DATE"] = $DB->CharToDateFunction($arLastMess["DATE_CREATE"]);
//}
$arFields["LAST_MESSAGE_GUEST_ID"] = intval($arLastMess["OWNER_GUEST_ID"]);
$arFields["LAST_MESSAGE_SID"] = "'" . $DB->ForSql($arLastMess["OWNER_SID"], 255) . "'";
}
$arFields["MESSAGES"] = $messages;
$arFields["PROBLEM_TIME"] = $allTime;
if ($arFirstUserMessAfterSupportMess !== null) {
$arFields["D_1_USER_M_AFTER_SUP_M"] = $DB->CharToDateFunction($arFirstUserMessAfterSupportMess["DATE_CREATE"]);
$arFields["ID_1_USER_M_AFTER_SUP_M"] = intval($arFirstUserMessAfterSupportMess["ID"]);
$arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] = "'N'";
}
if (is_array($dateType["EVENT"]) && in_array(CTicket::REOPEN, $dateType["EVENT"])) {
$arFields["DEADLINE_SOURCE_DATE"] = $DB->CharToDateFunction(GetTime(time() + CTimeZone::GetOffset(), "FULL"));
} elseif ($arTicket["IS_OVERDUE"] == "Y") {
$recalculateSupportDeadline = false;
}
$recalculateSupportDeadline = $recalculateSupportDeadline && intval($arTicket["DATE_CLOSE"]) <= 0 && $arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] == "'N'";
if (!$recalculateSupportDeadline) {
if ($arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] == "'Y'" || intval($arTicket["DATE_CLOSE"]) > 0) {
$arFields["SUPPORT_DEADLINE_NOTIFY"] = "null";
$arFields["SUPPORT_DEADLINE"] = "null";
$arFields["IS_OVERDUE"] = "'N'";
$arFields["IS_NOTIFIED"] = "'N'";
}
}
$DB->Update("b_ticket", $arFields, "WHERE ID='" . $ticketID . "'", $err_mess . __LINE__);
if ($recalculateSupportDeadline) {
$arTicket["M_ID"] = $arFirstUserMessAfterSupportMess["ID"];
$arTicket["D_1_USER_M_AFTER_SUP_M"] = $arFirstUserMessAfterSupportMess["DATE_CREATE"];
CTicketReminder::RecalculateSupportDeadlineForOneTicket($arTicket, $arFields, $dateType);
}
/*
LAST_MESSAGE_DATE
LAST_MESSAGE_USER_ID
LAST_MESSAGE_GUEST_ID
LAST_MESSAGE_SID
MESSAGES
REOPEN
PROBLEM_TIME
D_1_USER_M_AFTER_SUP_M
ID_1_USER_M_AFTER_SUP_M
LAST_MESSAGE_BY_SUPPORT_TEAM
DEADLINE_SOURCE_DATE
SUPPORT_DEADLINE_NOTIFY
SUPPORT_DEADLINE
IS_OVERDUE
IS_NOTIFIED
*/
}
function UpdateLastParams2($ticketID, $dateType)
{
global $DB;
$strUsers = implode(",", CTicket::GetSupportTeamAndAdminUsers());
$err_mess = CAllTicket::err_mess() . "<br>Function: UpdateLastParams2<br>Line: ";
$arFields = array();
$arFields["D_1_USER_M_AFTER_SUP_M"] = "null";
$arFields["ID_1_USER_M_AFTER_SUP_M"] = "null";
$arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] = "'Y'";
$arFields["SUPPORT_DEADLINE_NOTIFY"] = "null";
$arFields["SUPPORT_DEADLINE"] = "null";
$arFields["IS_OVERDUE"] = "'N'";
$arFields["IS_NOTIFIED"] = "'N'";
// Get last support response
$M_ID = 0;
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.ID ID,\n\t\t\t\tMAX(TM.ID) M_ID\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t\tINNER JOIN b_ticket_message TM\n\t\t\t\t\tON T.ID = TM.TICKET_ID\n\t\t\t\t\t\tAND T.ID = {$ticketID}\n\t\t\t\t\t\tAND (TM.IS_LOG='N' OR TM.IS_LOG IS NULL OR " . $DB->Length("TM.IS_LOG") . " <= 0)\n\t\t\t\t\t\tAND TM.OWNER_USER_ID IN({$strUsers})\n\t\t\t\t\n\t\t\tGROUP BY\n\t\t\t\tT.ID";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($arrRs = $rs->Fetch()) {
if (intval($arrRs["M_ID"]) > 0) {
$M_ID = intval($arrRs["M_ID"]);
}
}
// Get first user request after last support response
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.SLA_ID,\n\t\t\t\tT.DATE_CLOSE,\n\t\t\t\tSLA.RESPONSE_TIME_UNIT,\n\t\t\t\tSLA.RESPONSE_TIME,\n\t\t\t\tSLA.NOTICE_TIME_UNIT,\n\t\t\t\tSLA.NOTICE_TIME,\n\t\t\t\tPZ2.M_ID,\n\t\t\t\tPZ2.D_1_USER_M_AFTER_SUP_M,\n\t\t\t\t" . $DB->DateToCharFunction("T.D_1_USER_M_AFTER_SUP_M", "FULL") . " DATE_OLD\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t\tINNER JOIN b_ticket_sla SLA\n\t\t\t\t\tON T.SLA_ID = SLA.ID\n\t\t\t\t\t\tAND T.ID = {$ticketID}\n\t\t\t\tLEFT JOIN (SELECT\n\t\t\t\t\tTM.ID M_ID,\n\t\t\t\t\tTM.TICKET_ID,\n\t\t\t\t\t" . $DB->DateToCharFunction("TM.DATE_CREATE", "FULL") . " D_1_USER_M_AFTER_SUP_M\n\t\t\t\tFROM\n\t\t\t\t\tb_ticket_message TM\n\t\t\t\t\tINNER JOIN (SELECT\n\t\t\t\t\t\t\tT.ID ID,\n\t\t\t\t\t\t\tMIN(TM.ID) M_ID\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tb_ticket T\n\t\t\t\t\t\t\tINNER JOIN b_ticket_message TM\n\t\t\t\t\t\t\t\tON T.ID = TM.TICKET_ID\n\t\t\t\t\t\t\t\tAND T.ID = {$ticketID}\n\t\t\t\t\t\t\t\tAND (NOT(TM.IS_LOG='Y'))\n\t\t\t\t\t\t\t\tAND TM.ID > {$M_ID}\n\t\t\t\t\t\t\t\n\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\tT.ID) PZ\n\t\t\t\t\t\tON TM.ID = PZ.M_ID) PZ2\n\t\t\t\t\t\tON T.ID = PZ2.TICKET_ID\n\t\t\t\t\t\t\n\t\t";
//AND (NOT(TM.IS_HIDDEN='Y'))
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
if (!($arrRs = $rs->Fetch())) {
return;
}
if (intval($arrRs["M_ID"]) > 0) {
$arFields["D_1_USER_M_AFTER_SUP_M"] = $DB->CharToDateFunction($arrRs["D_1_USER_M_AFTER_SUP_M"]);
$arFields["ID_1_USER_M_AFTER_SUP_M"] = intval($arrRs["M_ID"]);
$arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] = "'N'";
}
if (intval($arrRs["DATE_CLOSE"]) <= 0 && $arFields["LAST_MESSAGE_BY_SUPPORT_TEAM"] == "'N'") {
$arrRs["ID"] = $ticketID;
CTicketReminder::RecalculateSupportDeadlineForOneTicket($arrRs, $arFields, $dateType);
} else {
$DB->Update("b_ticket", $arFields, "WHERE ID='" . $ticketID . "'", $err_mess . __LINE__);
}
}
