Module: ".$module_id." (".$arModuleVersion["VERSION"].")
Class: CAllSupportUpdate
File: ".__FILE__;
}
function GetUpdateVersion()
{
return 12000004;
}
function CurrentVersionLowerThanUpdateVersion()
{
$supUpdVer = intval(COption::GetOptionString("support", "SUPPORT_UPDATE_VERSION"));
return ($supUpdVer < CSupportUpdate::GetUpdateVersion());
}
function ChangeCurrentVersion()
{
COption::SetOptionString("support", "SUPPORT_UPDATE_VERSION", CSupportUpdate::GetUpdateVersion());
}
function Update()
{
if(CSupportUpdate::CurrentVersionLowerThanUpdateVersion())
{
$dbType = CSupportUpdate::GetBD();
$res = self::AlterTables($dbType);
if(!$res) return false;
$res = self::SeparateSLAandTimeTable($dbType);
if(!$res) return false;
CSupportTimetableCache::toCache();
CTicketReminder::RecalculateSupportDeadline();
CTicketReminder::StartAgent();
CSupportUpdate::ChangeCurrentVersion();
self::SetHotKeys();
}
}
function AlterTables($dbType)
{
global $DB;
$err_mess = (CAllSupportUpdate::err_mess())."
Function: AlterTables
Line: ";
//add tables
$addTables = array(
"b_ticket_timetable" => array(
"MySQL" => "
CREATE TABLE b_ticket_timetable
(
ID INT(18) not null auto_increment,
NAME varchar(255) not null,
DESCRIPTION text,
PRIMARY KEY (ID)
)
",
"MSSQL" => "
CREATE TABLE b_ticket_timetable
(
ID int NOT NULL IDENTITY (1, 1),
NAME varchar(255) NOT NULL,
DESCRIPTION TEXT NULL
)
GO
ALTER TABLE b_ticket_timetable ADD CONSTRAINT PK_b_ticket_timetable PRIMARY KEY (ID)
GO
",
"Oracle" => "
CREATE TABLE b_ticket_timetable
(
ID NUMBER(18) NOT NULL,
NAME VARCHAR2(255 CHAR) NULL,
DESCRIPTION CLOB NULL,
PRIMARY KEY (ID)
)
/
CREATE SEQUENCE SQ_b_ticket_timetable START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"
),
"b_ticket_holidays" => array(
"MySQL" => "
CREATE TABLE b_ticket_holidays
(
ID INT(18) not null auto_increment,
NAME varchar(255) not null,
DESCRIPTION text,
OPEN_TIME varchar(10) not null default 'HOLIDAY',
DATE_FROM datetime not null,
DATE_TILL datetime not null,
PRIMARY KEY (ID)
)
",
"MSSQL" => "
CREATE TABLE b_ticket_holidays
(
ID int NOT NULL IDENTITY (1, 1),
NAME varchar(255) NOT NULL,
DESCRIPTION TEXT NULL,
OPEN_TIME varchar(255) NOT NULL,
DATE_FROM datetime NOT NULL,
DATE_TILL datetime NOT NULL
)
GO
ALTER TABLE b_ticket_holidays ADD CONSTRAINT PK_b_ticket_holidays PRIMARY KEY (ID)
GO
ALTER TABLE b_ticket_holidays ADD CONSTRAINT DF_b_ticket_holidays_OPEN_TIME DEFAULT 'HOLIDAY' FOR OPEN_TIME
GO
",
"Oracle" => "
CREATE TABLE b_ticket_holidays
(
ID NUMBER(18) NOT NULL,
NAME VARCHAR2(255 CHAR) NULL,
DESCRIPTION CLOB NULL,
OPEN_TIME VARCHAR2(10 CHAR) DEFAULT ('HOLIDAY') NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TILL DATE NOT NULL,
PRIMARY KEY (ID)
)
/
CREATE SEQUENCE SQ_b_ticket_holidays START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"
),
"b_ticket_sla_2_holidays" => array(
"MySQL" => "
CREATE TABLE b_ticket_sla_2_holidays
(
SLA_ID INT(18) not null,
HOLIDAYS_ID INT(18) not null
)
",
"MSSQL" => "
CREATE TABLE b_ticket_sla_2_holidays
(
SLA_ID int NOT NULL,
HOLIDAYS_ID int NOT NULL
)
",
"Oracle" => "
CREATE TABLE b_ticket_sla_2_holidays
(
SLA_ID NUMBER(18) NOT NULL,
HOLIDAYS_ID NUMBER(18) NOT NULL
)
/
"
),
"b_ticket_search" => array(
"MySQL" => "
CREATE TABLE b_ticket_search
(
MESSAGE_ID INT(18) not null,
SEARCH_WORD varchar(70) not null
);
ALTER TABLE b_ticket_search ADD INDEX UX_b_ticket_search(SEARCH_WORD)
",
"MSSQL" => "
CREATE TABLE b_ticket_search
(
MESSAGE_ID int NOT NULL,
SEARCH_WORD varchar(70) NOT NULL
)
GO
CREATE INDEX UX_b_ticket_search ON b_ticket_search (SEARCH_WORD)
GO
",
"Oracle" => "
CREATE TABLE b_ticket_search
(
MESSAGE_ID NUMBER(18) NOT NULL,
SEARCH_WORD VARCHAR2(70 CHAR) NULL
)
/
CREATE INDEX UX_b_ticket_search ON b_ticket_search(SEARCH_WORD)
/
"
),
"b_ticket_timetable_cache" => array(
"MySQL" => "
CREATE TABLE b_ticket_timetable_cache
(
ID INT(18) not null auto_increment,
SLA_ID INT(18) not null,
DATE_FROM datetime not null,
DATE_TILL datetime not null,
W_TIME INT(18) not null,
W_TIME_INC INT(18) not null,
PRIMARY KEY (ID)
)
",
"MSSQL" => "
CREATE TABLE b_ticket_timetable_cache
(
ID int NOT NULL IDENTITY (1, 1),
SLA_ID int NOT NULL,
DATE_FROM datetime NOT NULL,
DATE_TILL datetime NOT NULL,
W_TIME int NOT NULL,
W_TIME_INC int NOT NULL
)
GO
ALTER TABLE b_ticket_timetable_cache ADD CONSTRAINT PK_b_ticket_timetable_cache PRIMARY KEY (ID)
GO
",
"Oracle" => "
CREATE TABLE b_ticket_timetable_cache
(
ID NUMBER(18) NOT NULL,
SLA_ID NUMBER(18) NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TILL DATE NOT NULL,
W_TIME NUMBER(18) NOT NULL,
W_TIME_INC NUMBER(18) NOT NULL,
PRIMARY KEY (ID)
)
/
CREATE SEQUENCE SQ_b_ticket_timetable_cache START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"
),
);
//delete fields
$deleteFields = array(
"b_ticket" => array(
"DATE_OF_FIRST_USER_MSG_AFTER_SUP_MSG",
"ID_OF_FIRST_USER_MSG_AFTER_SUP_MSG",
"DATE_FIRST_USER_M_AFTER_SUP_M",
"ID_FIRST_USER_M_AFTER_SUP_M",
),
);
//add fields
$addFields = array(
"b_ticket" => array(
array("FIELD" => "SUPPORT_DEADLINE", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
array("FIELD" => "SUPPORT_DEADLINE_NOTIFY", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
array("FIELD" => "D_1_USER_M_AFTER_SUP_M", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
array("FIELD" => "ID_1_USER_M_AFTER_SUP_M", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
),
"b_ticket_sla"=> array(
array("FIELD" => "TIMETABLE_ID", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
),
"b_ticket_sla_shedule" => array(
array("FIELD" => "TIMETABLE_ID", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
),
"b_ticket_user_ugroup" => array(
array("FIELD" => "CAN_MAIL_UPDATE_GROUP_MESSAGES", "MySQL" => "char(1) NOT NULL default 'N'", "MSSQL" => "char(1) NOT NULL DEFAULT 'N'", "Oracle" => "CHAR(1 CHAR) DEFAULT 'N' NOT NULL",),
),
);
if($DB->TableExists("b_ticket"))
{
foreach($addTables as $table => $arr)
{
if(!$DB->TableExists($table))
{
$arQuery = $DB->ParseSQLBatch(str_replace("\r", "", $arr[$dbType]));
foreach($arQuery as $i => $sql)
{
$res = $DB->Query($sql, true);
if(!$res) return false;
}
}
}
}
foreach($deleteFields as $table => $arr)
{
if($DB->TableExists($table))
{
foreach($arr as $n => $FN)
{
if($DB->Query("select $FN from $table WHERE 1=0", true))
{
$res = $DB->Query("ALTER TABLE $table DROP $FN", true);
if(!$res) return false;
}
}
}
}
foreach($addFields as $table => $arr)
{
if($DB->TableExists($table))
{
foreach($arr as $n => $arrF)
{
$FN = $arrF["FIELD"];
$FT = $arrF[$dbType];
if(!$DB->Query("select $FN from $table WHERE 1=0", true))
{
$res = $DB->Query("ALTER TABLE $table ADD $FN $FT", true);
if(!$res) return false;
}
}
}
}
return true;
}
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;
}
function SetHotKeys()
{
$arHK = array(
"B" => "Alt+66",
"I" => "Alt+73",
"U" => "Alt+85",
"QUOTE" => "Alt+81",
"CODE" => "Alt+67",
"TRANSLIT" => "Alt+84",
);
$hkc = new CHotKeysCode;
foreach($arHK as $s => $hk)
{
$className = "TICKET_EDIT_$s";
$arHKC = array (
CLASS_NAME => $className,
CODE => "var d=document.getElementById('$s'); if (d) d.click();",
NAME => " ($id)",
TITLE_OBJ => "TICKET_EDIT_" . $s . "_T",
IS_CUSTOM => "1"
);
$objK = $hkc->GetList(array(), Array("CLASS_NAME"=>$className));
if($arK = $objK->Fetch())
{
$hkc->Update($arK["ID"],$arHKC);
}
else
{
$id = $hkc->Add($arHKC);
if($id > 0)
{
$result = CHotKeys::GetInstance()->AddDefaultKeyToAll($id, $hk);
}
}
}
}
}
?>