public static function RecalculateLastMessageDeadline($RSD = true) { global $DB, $DBType; $err_mess = self::err_mess() . "<br>Function: RecalculateLastMessage<br>Line: "; $DB->StartUsingMasterOnly(); $strSql = "SELECT count(*) C FROM b_ticket"; $rs = $DB->Query($strSql, false, $err_mess . __LINE__); $resC = $rs->Fetch(); if (!is_array($resC)) { return true; } if (!isset($resC["C"]) || $resC["C"] == 0) { return true; } $strUsers = implode(",", CTicket::GetSupportTeamAndAdminUsers()); $strSql0 = "\n\t\t\t\tb_ticket\n\t\t\t\tINNER JOIN (\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tT.ID TID,\n\t\t\t\t\t\tM.DATE_CREATE DATE_CREATE,\n\t\t\t\t\t\tM.ID ID,\n\t\t\t\t\t\t" . CTicket::isnull("Q.LMBS", "'Y'") . " LMBS\n\t\t\t\t\tFROM\n\t\t\t\t\t\tb_ticket as T\n\t\t\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tTM.TICKET_ID ID,\n\t\t\t\t\t\t\t\tMIN(TM.ID) M_ID,\n\t\t\t\t\t\t\t\t'N' LMBS\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tb_ticket_message TM\n\t\t\t\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\tT.ID ID,\n\t\t\t\t\t\t\t\t\t\tMAX(" . CTicket::isnull("TM.ID", "0") . ") M_ID\n\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\tb_ticket T\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN b_ticket_message TM\n\t\t\t\t\t\t\t\t\t\t\tON T.ID = TM.TICKET_ID\n\t\t\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.IS_LOG='Y'))\n\t\t\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.IS_HIDDEN='Y'))\n\t\t\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.NOT_CHANGE_STATUS='Y'))\n\t\t\t\t\t\t\t\t\t\t\t\tAND TM.OWNER_USER_ID IN ({$strUsers})\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tT.DATE_CLOSE IS NULL\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tT.ID\n\t\t\t\t\t\t\t\t) AS Q\n\t\t\t\t\t\t\t\t\tON TM.TICKET_ID = Q.ID\n\t\t\t\t\t\t\t\t\t\tAND TM.ID > Q.M_ID\n\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.IS_LOG='Y'))\n\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.IS_HIDDEN='Y'))\n\t\t\t\t\t\t\t\t\t\tAND (NOT(TM.NOT_CHANGE_STATUS='Y'))\n\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\tTM.TICKET_ID\n\t\t\t\t\t\t) AS Q\n\t\t\t\t\t\t\tON T.ID = Q.ID\n\t\t\t\t\t\tLEFT JOIN b_ticket_message AS M\n\t\t\t\t\t\t\tON Q.M_ID = M.ID\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tT.DATE_CLOSE IS NULL\n\t\t\t\t) AS M\n\t\t\t\t\tON b_ticket.ID = M.TID\n\t\t"; $arS = array("MySQL" => "\n\tUPDATE {$strSql0}\n\tSET\n\tb_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE,\n\tb_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID,\n\tb_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = M.LMBS\n\t\t\t\t\t", "MSSQL" => "\n\tUPDATE b_ticket\n\tSET\n\tb_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE,\n\tb_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID,\n\tb_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = M.LMBS\n\tFROM {$strSql0}\n\t\t\t\t\t", "Oracle" => "\n\tUPDATE b_ticket T0\n\tSET (D_1_USER_M_AFTER_SUP_M, ID_1_USER_M_AFTER_SUP_M, LAST_MESSAGE_BY_SUPPORT_TEAM) = (\n\t\tSELECT\n\t\t\tM.DATE_CREATE,\n\t\t\tM.ID,\n\t\t\tM.LMBS\n\t\tFROM " . str_replace(" AS ", " ", $strSql0) . "\n\t\tWHERE b_ticket.ID = T0.ID\n\t)\n\t\t\t\t\t"); $res = $DB->Query($arS[$DBType], true); $res = $DB->Query("UPDATE b_ticket SET SUPPORT_DEADLINE = null, SUPPORT_DEADLINE_NOTIFY = null WHERE LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y'", true); if ($RSD) { self::RecalculateSupportDeadline(); } $DB->StopUsingMasterOnly(); }
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 SeparateSLAandTimeTable($dbType) { global $DB; $err_mess = CAllSupportUpdate::err_mess() . "<br>Function: SeparateSLAandTimeTable<br>Line: "; $strUsers = implode(",", CTicket::GetSupportTeamAndAdminUsers()); $strSql0 = "\n\t\t\t\tb_ticket\n\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tTM.TICKET_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_message TM\n\t\t\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\tT.ID ID,\n\t\t\t\t\t\t\t\t\tMAX(" . CTicket::isnull("TM.ID", "0") . ") M_ID\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tb_ticket T\n\t\t\t\t\t\t\t\t\tLEFT JOIN b_ticket_message TM\n\t\t\t\t\t\t\t\t\t\tON T.ID = TM.TICKET_ID\n\t\t\t\t\t\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\t\t\t\t\t\tAND TM.OWNER_USER_ID IN ({$strUsers})\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tT.DATE_CLOSE IS NULL\n\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\tT.ID\n\t\t\t\t\t\t\t) AS Q\n\t\t\t\t\t\t\t\tON TM.TICKET_ID = Q.ID\n\t\t\t\t\t\t\t\t\tAND TM.ID > Q.M_ID\n\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\tTM.TICKET_ID\n\t\t\t\t\t) AS Q\n\t\t\t\t\t\tON b_ticket.ID = Q.ID\n\t\t\t\t\tINNER JOIN b_ticket_message AS M\n\t\t\t\t\t\tON Q.M_ID = M.ID\n\t\t\t"; $updateQueries = array("b_ticket_timetable,b_ticket_sla,b_ticket_sla_shedule" => array(0 => array("MySQL" => "\n\tINSERT INTO b_ticket_timetable (NAME, DESCRIPTION)\n\t\tSELECT NAME, ID\n\t\tFROM b_ticket_sla\n\t\t\t\t\t", "MSSQL" => "\n\tINSERT INTO b_ticket_timetable (NAME, DESCRIPTION)\n\t\tSELECT NAME, CAST(CAST(ID AS varchar) AS text)\n\t\tFROM b_ticket_sla\n\t\t\t\t\t", "Oracle" => "\n\tINSERT INTO b_ticket_timetable (ID, NAME, DESCRIPTION)\n\t\tSELECT SQ_b_ticket_timetable.nextval, NAME, ID \n\t\tFROM b_ticket_sla\n\t\t\t\t\t"), 1 => array("MySQL" => "\n\tUPDATE b_ticket_sla AS S\n\t\tINNER JOIN b_ticket_timetable AS T\n\t\t\tON (S.ID = cast(T.DESCRIPTION as UNSIGNED))\n\t\t\t\tAND T.DESCRIPTION IS NOT NULL\n\t\t\t\tAND S.TIMETABLE_ID IS NULL\n\tSET S.TIMETABLE_ID = T.ID\n\t\t\t\t\t", "MSSQL" => "\n\tUPDATE b_ticket_sla\n\tSET b_ticket_sla.TIMETABLE_ID = T.ID\n\tFROM\n\t\tb_ticket_sla\n\t\tINNER JOIN b_ticket_timetable AS T\n\t\t\tON (b_ticket_sla.ID = CAST(CAST(T.DESCRIPTION AS varchar) AS int))\n\t\t\t\tAND T.DESCRIPTION IS NOT NULL\n\t\t\t\tAND b_ticket_sla.TIMETABLE_ID IS NULL\n\t\t\t\t\t", "Oracle" => "\n\tUPDATE b_ticket_sla SET TIMETABLE_ID = (\n\t\tSELECT T.ID\n\t\tFROM b_ticket_timetable T\n\t\tWHERE\n\t\t\tb_ticket_sla.ID = CAST(CAST(T.DESCRIPTION as VARCHAR2(18 CHAR)) as int)\n\t\t\tAND T.DESCRIPTION IS NOT NULL\n\t\t)\n\tWHERE\n\t\tTIMETABLE_ID IS NULL\n\t\t\t\t\t"), 2 => array("MySQL" => "\n\tUPDATE b_ticket_sla_shedule AS SS\n\t\tINNER JOIN b_ticket_timetable AS T\n\t\t\tON (SS.SLA_ID = cast(T.DESCRIPTION as UNSIGNED)) \n\t\t\t\tAND T.DESCRIPTION IS NOT NULL\n\tSET SS.TIMETABLE_ID = T.ID\n\t\t\t\t\t", "MSSQL" => "\n\tUPDATE b_ticket_sla_shedule SET TIMETABLE_ID = (\n\t\tSELECT T.ID\n\t\tFROM \n\t\t\tb_ticket_timetable AS T\n\t\tWHERE\n\t\t\tb_ticket_sla_shedule.SLA_ID = CAST(CAST(T.DESCRIPTION AS varchar) AS int)\n\t\t\tAND T.DESCRIPTION IS NOT NULL\n\t\t)\n\t\t\t\t\t", "Oracle" => "\n\tUPDATE b_ticket_sla_shedule SET TIMETABLE_ID = (\n\t\tSELECT T.ID\n\t\tFROM \n\t\t\tb_ticket_timetable T\n\t\tWHERE\n\t\t\tb_ticket_sla_shedule.SLA_ID = CAST(CAST(T.DESCRIPTION as VARCHAR2(18 CHAR)) as int)\n\t\t\tAND T.DESCRIPTION IS NOT NULL\n\t\t)\n\t\t\t\t\t"), 3 => array("MySQL" => "\n\tUPDATE b_ticket_timetable\n\tSET DESCRIPTION = NULL\n\t\t\t\t\t", "MSSQL" => "\n\tUPDATE b_ticket_timetable\n\tSET DESCRIPTION = NULL\n\t\t\t\t\t", "Oracle" => "\n\tUPDATE b_ticket_timetable\n\tSET DESCRIPTION = NULL\n\t\t\t\t\t")), "b_ticket" => array(0 => array("MySQL" => "\n\tUPDATE {$strSql0}\n\tSET\n\tb_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE,\n\tb_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID,\n\tb_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = 'N'\n\t\t\t\t\t", "MSSQL" => "\n\tUPDATE b_ticket\n\tSET\n\tb_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE,\n\tb_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID,\n\tb_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = 'N'\n\tFROM {$strSql0}\n\t\t\t\t\t", "Oracle" => "\n\tUPDATE b_ticket T0\n\tSET (D_1_USER_M_AFTER_SUP_M, ID_1_USER_M_AFTER_SUP_M, LAST_MESSAGE_BY_SUPPORT_TEAM) = (\n\t\tSELECT\n\t\t\tM.DATE_CREATE,\n\t\t\tM.ID,\n\t\t\t'N'\n\t\tFROM " . str_replace(" AS ", " ", $strSql0) . "\n\t\tWHERE b_ticket.ID = T0.ID\n\t)\n\t\t\t\t\t"))); foreach ($updateQueries as $checkTables => $arT) { $arCT = explode(",", $checkTables); $skipU = false; foreach ($arCT as $n => $t) { if (!$DB->TableExists($t)) { $skipU = true; } } if (!$skipU) { foreach ($arT as $n1 => $arQ) { $arQuery = $DB->ParseSQLBatch(str_replace("\r", "", $arQ[$dbType])); foreach ($arQuery as $i => $sql) { $res = $DB->Query($sql, true); if (!$res) { return false; } } } } } return true; }
function RecalculateLastMessageDeadline($RSD = true) { global $DB, $DBType; $err_mess = (self::err_mess())."<br>Function: RecalculateLastMessage<br>Line: "; $DB->StartUsingMasterOnly(); $strSql = "SELECT count(*) C FROM b_ticket"; $rs = $DB->Query($strSql, false, $err_mess.__LINE__); $resC = $rs->Fetch(); if(!is_array($resC)) { return true; } if(!isset($resC["C"]) || $resC["C"] == 0) { return true; } $strUsers = implode(",", CTicket::GetSupportTeamAndAdminUsers()); $strSql0 = " b_ticket INNER JOIN ( SELECT T.ID TID, M.DATE_CREATE DATE_CREATE, M.ID ID, " . CTicket::isnull("Q.LMBS", "'Y'") . " LMBS FROM b_ticket as T LEFT JOIN ( SELECT TM.TICKET_ID ID, MIN(TM.ID) M_ID, 'N' LMBS FROM b_ticket_message TM INNER JOIN ( SELECT T.ID ID, MAX(" . CTicket::isnull("TM.ID", "0") . ") M_ID FROM b_ticket T LEFT JOIN b_ticket_message TM ON T.ID = TM.TICKET_ID AND (NOT(TM.IS_LOG='Y')) AND (NOT(TM.IS_HIDDEN='Y')) AND (NOT(TM.NOT_CHANGE_STATUS='Y')) AND TM.OWNER_USER_ID IN ($strUsers) WHERE T.DATE_CLOSE IS NULL GROUP BY T.ID ) AS Q ON TM.TICKET_ID = Q.ID AND TM.ID > Q.M_ID AND (NOT(TM.IS_LOG='Y')) AND (NOT(TM.IS_HIDDEN='Y')) AND (NOT(TM.NOT_CHANGE_STATUS='Y')) GROUP BY TM.TICKET_ID ) AS Q ON T.ID = Q.ID LEFT JOIN b_ticket_message AS M ON Q.M_ID = M.ID WHERE T.DATE_CLOSE IS NULL ) AS M ON b_ticket.ID = M.TID "; $arS = array( "MySQL" => " UPDATE $strSql0 SET b_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE, b_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID, b_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = M.LMBS ", "MSSQL" => " UPDATE b_ticket SET b_ticket.D_1_USER_M_AFTER_SUP_M = M.DATE_CREATE, b_ticket.ID_1_USER_M_AFTER_SUP_M = M.ID, b_ticket.LAST_MESSAGE_BY_SUPPORT_TEAM = M.LMBS FROM $strSql0 ", "Oracle" => " UPDATE b_ticket T0 SET (D_1_USER_M_AFTER_SUP_M, ID_1_USER_M_AFTER_SUP_M, LAST_MESSAGE_BY_SUPPORT_TEAM) = ( SELECT M.DATE_CREATE, M.ID, M.LMBS FROM ".str_replace(" AS ", " ", $strSql0)." WHERE b_ticket.ID = T0.ID ) ", ); $res = $DB->Query($arS[$DBType], true); $res = $DB->Query("UPDATE b_ticket SET SUPPORT_DEADLINE = null, SUPPORT_DEADLINE_NOTIFY = null WHERE LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y'", true); if($RSD) { self::RecalculateSupportDeadline(); } $DB->StopUsingMasterOnly(); }
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__); } }