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 GetResponsibleList($userID, $CMGM = null, $CMUGM = null, $SG = null) { $condition = ""; if ($CMGM != null) { $condition .= "\n\t\t\t\t\t\t\tAND TUG2.CAN_MAIL_GROUP_MESSAGES = '" . ($CMGM == "Y" ? "Y" : "N") . "'"; } if ($CMUGM != null) { $condition .= "\n\t\t\t\t\t\t\tAND TUG2.CAN_MAIL_UPDATE_GROUP_MESSAGES = '" . ($CMUGM == "Y" ? "Y" : "N") . "'"; } $condition2 = ""; if ($SG != null) { $condition2 .= "\n\t\t\t\t\t\t\tAND TG.IS_TEAM_GROUP = '" . ($SG == "Y" ? "Y" : "N") . "'"; } $err_mess = CTicket::err_mess() . "<br>Function: GetSupportTeamMailList<br>Line: "; global $DB; $strSql = "\n\t\t\tSELECT\n\t\t\t\tU.ID as ID,\n\t\t\t\tU.LOGIN as LOGIN,\n\t\t\t\t" . $DB->Concat(CTicket::isnull("U.LAST_NAME", "''"), CTicket::isnull("U.NAME", "''"), 'U.LOGIN') . " as NAME,\n\t\t\t\tU.EMAIL as EMAIL\n\t\t\tFROM\n\t\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t\tTUG2.USER_ID AS USER_ID\t\t\t\t\n\t\t\t\tFROM\n\t\t\t\t\tb_ticket_ugroups TG\n\t\t\t\t\tINNER JOIN b_ticket_user_ugroup TUG\n\t\t\t\t\t\tON TG.ID = TUG.GROUP_ID" . $condition2 . "\n\t\t\t\t\tINNER JOIN b_ticket_user_ugroup TUG2\n\t\t\t\t\t\tON TUG.USER_ID = '" . intval($userID) . "'\n\t\t\t\t\t\t\tAND TUG.GROUP_ID = TUG2.GROUP_ID" . $condition . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tTUG2.USER_ID\n\t\t\t\t) TU\n\t\t\t\tINNER JOIN b_user U\n\t\t\t\t\tON TU.USER_ID = U.ID\n\t\t\t\tORDER BY\n\t\t\t\t\tU.ID\n\t\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); return $res; }
public static function GetResponsibleList($userID, $CMGM = null, $CMUGM = null, $SG = null) { $condition = ""; if($CMGM != null) $condition .= " AND TUG2.CAN_MAIL_GROUP_MESSAGES = '" . ($CMGM == "Y" ? "Y" : "N") . "'"; if($CMUGM != null) $condition .= " AND TUG2.CAN_MAIL_UPDATE_GROUP_MESSAGES = '" . ($CMUGM == "Y" ? "Y" : "N") . "'"; $condition2 = ""; if($SG != null) $condition2 .= " AND TG.IS_TEAM_GROUP = '" . ($SG == "Y" ? "Y" : "N") . "'"; $err_mess = (CTicket::err_mess())."<br>Function: GetSupportTeamMailList<br>Line: "; global $DB; $strSql = " SELECT U.ID as ID, U.LOGIN as LOGIN, ". $DB->Concat(CTicket::isnull("U.LAST_NAME", "''"), CTicket::isnull("U.NAME", "''"), 'U.LOGIN')." as NAME, U.EMAIL as EMAIL FROM ( SELECT TUG2.USER_ID AS USER_ID FROM b_ticket_ugroups TG INNER JOIN b_ticket_user_ugroup TUG ON TG.ID = TUG.GROUP_ID" . $condition2 . " INNER JOIN b_ticket_user_ugroup TUG2 ON TUG.USER_ID = '" . intval($userID) . "' AND TUG.GROUP_ID = TUG2.GROUP_ID" . $condition . " GROUP BY TUG2.USER_ID ) TU INNER JOIN b_user U ON TU.USER_ID = U.ID ORDER BY U.ID "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; }
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(); }
public static function InsertDefaultValues() { global $DB; $err_mess = self::err_mess() . "<br>Function: InsertDefaultValues<br>Line: "; $t_sla_shedule = self::SLA_SHEDULE; $arInsStr = array("SLA_ID" => 0, "WEEKDAY_NUMBER" => 0, "OPEN_TIME" => "'24H'", "MINUTE_FROM" => 0, "MINUTE_TILL" => 0, "TIMETABLE_ID" => 0); $strSql = "\n\t\t\tSELECT\n\t\t\t\tR.TIMETABLE_ID\n\t\t\tFROM\n\t\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t\tT.ID TIMETABLE_ID,\n\t\t\t\t\tSUM(" . CTicket::isnull("S.ID", 0) . ") V\n\t\t\t\tFROM\n\t\t\t\t\tb_ticket_timetable T\n\t\t\t\t\tLEFT JOIN {$t_sla_shedule} S\n\t\t\t\t\t\tON T.ID = S.TIMETABLE_ID AND S.TIMETABLE_ID > 0\n\t\t\t\tGROUP BY\n\t\t\t\tT.ID\n\t\t\t\t) R\n\t\t\tWHERE\n\t\t\t\tR.V = 0\n\t\t\t"; $q = $DB->Query($strSql, false, $err_mess . __LINE__); while ($arrR = $q->Fetch()) { for ($i = 0; $i <= 6; $i++) { $arInsStr["WEEKDAY_NUMBER"] = $i; $arInsStr["TIMETABLE_ID"] = intval($arrR["TIMETABLE_ID"]); $DB->Insert($t_sla_shedule, $arInsStr, $err_mess . __LINE__); } } }