function SeparateSLAandTimeTable($dbType)
{
global $DB;
$err_mess = (CAllSupportUpdate::err_mess())."
Function: SeparateSLAandTimeTable
Line: ";
$strUsers = implode(",", CTicket::GetSupportTeamAndAdminUsers());
$strSql0 = "
b_ticket
INNER JOIN (
SELECT
TM.TICKET_ID ID,
MIN(TM.ID) M_ID
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 (TM.IS_LOG='N' OR TM.IS_LOG IS NULL OR " . $DB->Length("TM.IS_LOG") . " <= 0)
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
GROUP BY
TM.TICKET_ID
) AS Q
ON b_ticket.ID = Q.ID
INNER JOIN b_ticket_message AS M
ON Q.M_ID = M.ID
";
$updateQueries = array(
"b_ticket_timetable,b_ticket_sla,b_ticket_sla_shedule" => array(
0 => array(
"MySQL" => "
INSERT INTO b_ticket_timetable (NAME, DESCRIPTION)
SELECT NAME, ID
FROM b_ticket_sla
",
"MSSQL" => "
INSERT INTO b_ticket_timetable (NAME, DESCRIPTION)
SELECT NAME, CAST(CAST(ID AS varchar) AS text)
FROM b_ticket_sla
",
"Oracle" => "
INSERT INTO b_ticket_timetable (ID, NAME, DESCRIPTION)
SELECT SQ_b_ticket_timetable.nextval, NAME, ID
FROM b_ticket_sla
"
),
1 => array(
"MySQL" => "
UPDATE b_ticket_sla AS S
INNER JOIN b_ticket_timetable AS T
ON (S.ID = cast(T.DESCRIPTION as UNSIGNED))
AND T.DESCRIPTION IS NOT NULL
AND S.TIMETABLE_ID IS NULL
SET S.TIMETABLE_ID = T.ID
",
"MSSQL" => "
UPDATE b_ticket_sla
SET b_ticket_sla.TIMETABLE_ID = T.ID
FROM
b_ticket_sla
INNER JOIN b_ticket_timetable AS T
ON (b_ticket_sla.ID = CAST(CAST(T.DESCRIPTION AS varchar) AS int))
AND T.DESCRIPTION IS NOT NULL
AND b_ticket_sla.TIMETABLE_ID IS NULL
",
"Oracle" => "
UPDATE b_ticket_sla SET TIMETABLE_ID = (
SELECT T.ID
FROM b_ticket_timetable T
WHERE
b_ticket_sla.ID = CAST(CAST(T.DESCRIPTION as VARCHAR2(18 CHAR)) as int)
AND T.DESCRIPTION IS NOT NULL
)
WHERE
TIMETABLE_ID IS NULL
"
),
2 => array(
"MySQL" => "
UPDATE b_ticket_sla_shedule AS SS
INNER JOIN b_ticket_timetable AS T
ON (SS.SLA_ID = cast(T.DESCRIPTION as UNSIGNED))
AND T.DESCRIPTION IS NOT NULL
SET SS.TIMETABLE_ID = T.ID
",
"MSSQL" => "
UPDATE b_ticket_sla_shedule SET TIMETABLE_ID = (
SELECT T.ID
FROM
b_ticket_timetable AS T
WHERE
b_ticket_sla_shedule.SLA_ID = CAST(CAST(T.DESCRIPTION AS varchar) AS int)
AND T.DESCRIPTION IS NOT NULL
)
",
"Oracle" => "
UPDATE b_ticket_sla_shedule SET TIMETABLE_ID = (
SELECT T.ID
FROM
b_ticket_timetable T
WHERE
b_ticket_sla_shedule.SLA_ID = CAST(CAST(T.DESCRIPTION as VARCHAR2(18 CHAR)) as int)
AND T.DESCRIPTION IS NOT NULL
)
"
),
3 => array(
"MySQL" => "
UPDATE b_ticket_timetable
SET DESCRIPTION = NULL
",
"MSSQL" => "
UPDATE b_ticket_timetable
SET DESCRIPTION = NULL
",
"Oracle" => "
UPDATE b_ticket_timetable
SET DESCRIPTION = NULL
",
),
),
"b_ticket" => array(
0 => 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 = 'N'
",
"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 = 'N'
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,
'N'
FROM ".str_replace(" AS ", " ", $strSql0)."
WHERE b_ticket.ID = T0.ID
)
",
),
),
);
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;
}