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__);
}
}
}
