Module: ".$module_id."
Class: CTicket
File: ".__FILE__; } public static function AutoClose() { $err_mess = (CTicket::err_mess())."
Function: AutoClose
Line: "; global $DB; /*$strSql = " SELECT T.ID FROM b_ticket T WHERE T.AUTO_CLOSE_DAYS > 0 and (T.DATE_CLOSE is null or length(T.DATE_CLOSE)<=0) and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(T.TIMESTAMP_X))/86400 > T.AUTO_CLOSE_DAYS ";*/ $nowTime = $DB->CharToDateFunction(GetTime(time() + CTimeZone::GetOffset(),"FULL")); $strSql = " SELECT T.ID FROM b_ticket T WHERE T.AUTO_CLOSE_DAYS > 0 and (T.DATE_CLOSE is null or length(T.DATE_CLOSE)<=0) and (UNIX_TIMESTAMP($nowTime)-UNIX_TIMESTAMP(T.LAST_MESSAGE_DATE))/86400 > T.AUTO_CLOSE_DAYS and T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' ";//now() $rsTickets = $DB->Query($strSql, false, $err_mess.__LINE__); while ($arTicket = $rsTickets->Fetch()) { $arFields = array( "TIMESTAMP_X" => $DB->GetNowFunction(), "DATE_CLOSE" => $DB->GetNowFunction(), "MODIFIED_USER_ID" => "null", "MODIFIED_GUEST_ID" => "null", "MODIFIED_MODULE_NAME" => "'auto closing'", //"AUTO_CLOSE_DAYS" => "null", "AUTO_CLOSED" => "'Y'" ); $DB->Update("b_ticket",$arFields,"WHERE ID='".$arTicket["ID"]."'",$err_mess.__LINE__); } return "CTicket::AutoClose();"; } public static function CleanUpOnline() { $err_mess = (CTicket::err_mess())."
Function: CleanUpOnline
Line: "; global $DB; $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL")); $strSql = " DELETE FROM b_ticket_online WHERE TIMESTAMP_X < DATE_ADD(now(), INTERVAL - $onlineInterval SECOND) "; $DB->Query($strSql,false,$err_mess.__LINE__); return "CTicket::CleanUpOnline();"; } public static function GetOnline($ticketID) { $err_mess = (CTicket::err_mess())."
Function: GetOnline
Line: "; global $DB; $ticketID = intval($ticketID); $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL")); $strSql = " SELECT ".$DB->DateToCharFunction("max(T.TIMESTAMP_X)")." TIMESTAMP_X, T.USER_ID, T.CURRENT_MODE, U.EMAIL USER_EMAIL, U.LOGIN USER_LOGIN, concat(ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) USER_NAME FROM b_ticket_online T, b_user U WHERE T.TICKET_ID = $ticketID and T.TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - $onlineInterval SECOND) and U.ID = T.USER_ID GROUP BY T.USER_ID, U.EMAIL, U.LOGIN, U.NAME, U.LAST_NAME ORDER BY T.USER_ID "; $z = $DB->Query($strSql, false, $err_mess.__LINE__); return $z; } public static function DeleteMessage($ID, $checkRights="Y") { $err_mess = (CTicket::err_mess())."
Function: DeleteMessage
Line: "; global $DB; $ID = intval($ID); if ($ID<=0) return; $bAdmin = "N"; if ($checkRights=="Y") { $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N"; } else { $bAdmin = "Y"; } if ($bAdmin=="Y") { $strSql = " SELECT F.ID FILE_ID, M.TICKET_ID FROM b_ticket_message M LEFT JOIN b_ticket_message_2_file MF ON (MF.MESSAGE_ID = M.ID) LEFT JOIN b_file F ON (F.ID = MF.FILE_ID) WHERE M.ID='$ID' "; $z = $DB->Query($strSql, false, $err_mess.__LINE__); while ($zr = $z->Fetch()) { $ticketID = $zr["TICKET_ID"]; if (intval($zr["FILE_ID"])>0) { CFile::Delete($zr["FILE_ID"]); } } $z = $DB->Query("DELETE FROM b_ticket_message WHERE ID='$ID'", false, $err_mess.__LINE__); if (intval($z->AffectedRowsCount())>0) { //CTicket::UpdateLastParams($ticketID); //CTicket::UpdateLastParams2($ticketID, CTicket::DELETE); CTicket::UpdateLastParamsN($ticketID, array("EVENT"=>array(CTicket::DELETE)), true, true); if (CSupportSearch::isIndexExists()) { CSupportSearch::reindexTicket($ticketID); } } } } public static function UpdateMessage($MESSAGE_ID, $arFields, $checkRights="Y") { $err_mess = (CTicket::err_mess())."
Function: UpdateMessage
Line: "; global $DB, $USER; $MESSAGE_ID = intval($MESSAGE_ID); $bAdmin = "N"; $bSupportTeam = "N"; if ($checkRights=="Y") { $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N"; $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N"; $uid = $USER->GetID(); } else { $bAdmin = "Y"; $bSupportTeam = "Y"; $uid = 0; } if ($bAdmin=="Y") { $ownerSid = $arFields["OWNER_SID"]; $ownerUserID = $arFields["OWNER_USER_ID"]; $arFields_u = array( "TIMESTAMP_X" => $DB->GetNowFunction(), "C_NUMBER" => intval($arFields["C_NUMBER"]), "MESSAGE" => "'".$DB->ForSql($arFields["MESSAGE"])."'", "MESSAGE_SEARCH" => "'".ToUpper($DB->ForSql($arFields["MESSAGE"]))."'", "SOURCE_ID" => (intval($arFields["SOURCE_ID"])>0 ? intval($arFields["SOURCE_ID"]) : "null"), "OWNER_SID" => "'".$DB->ForSql($ownerSid, 255)."'", "OWNER_USER_ID" => (intval($ownerUserID)>0 ? intval($ownerUserID) : "null"), "MODIFIED_USER_ID" => (intval($uid)>0 ? intval($uid) : "null"), "MODIFIED_GUEST_ID" => (intval($_SESSION["SESS_GUEST_ID"])>0 ? intval($_SESSION["SESS_GUEST_ID"]) : "null"), "EXTERNAL_ID" => (intval($arFields["EXTERNAL_ID"])>0 ? intval($arFields["EXTERNAL_ID"]) : "null"), "TASK_TIME" => (intval($arFields["TASK_TIME"])>0 ? intval($arFields["TASK_TIME"]) : "null"), "EXTERNAL_FIELD_1" => "'".$DB->ForSql($arFields["EXTERNAL_FIELD_1"])."'", "IS_SPAM" => ($arFields["IS_SPAM"] <> '' ? "'".$arFields["IS_SPAM"]."'" : "null"), "IS_HIDDEN" => ($arFields["IS_HIDDEN"]=="Y" ? "'Y'" : "'N'"), "IS_LOG" => ($arFields["IS_LOG"]=="Y" ? "'Y'" : "'N'"), "IS_OVERDUE" => ($arFields["IS_OVERDUE"]=="Y" ? "'Y'" : "'N'"), "NOT_CHANGE_STATUS" => ($arFields["NOT_CHANGE_STATUS"]=="Y" ? "'Y'" : "'N'") ); $notChangeStatus = ( is_set($arFields, "NOT_CHANGE_STATUS") && $arFields["NOT_CHANGE_STATUS"]=="Y" ? "Y" : "N" ); $rows = $DB->Update("b_ticket_message",$arFields_u,"WHERE ID='".$MESSAGE_ID."'",$err_mess.__LINE__); if (intval($rows)>0) { $rsMessage = CTicket::GetMessageByID($MESSAGE_ID, $checkRights); if ($arMessage = $rsMessage->Fetch()) { $ticketID = $arMessage["TICKET_ID"]; // обновим прикрепленные файлы $not_image_extension_suffix = COption::GetOptionString("support", "NOT_IMAGE_EXTENSION_SUFFIX"); $not_image_upload_dir = COption::GetOptionString("support", "NOT_IMAGE_UPLOAD_DIR"); $max_size = COption::GetOptionString("support", "SUPPORT_MAX_FILESIZE"); $arrFiles = $arFields["FILES"]; if (is_array($arrFiles) && count($arrFiles)>0) { foreach ($arrFiles as $arFile) { if ($arFile["name"] <> '' || $arFile["del"]=="Y") { if ($bSupportTeam!="Y" && $bAdmin!="Y") $max_file_size = intval($max_size)*1024; $fes = ""; $upload_dir = "support"; if (!CFile::IsImage($arFile["name"], $arFile["type"])) { $fes = $not_image_extension_suffix; $arFile["name"] .= $fes; $upload_dir = $not_image_upload_dir; } if (!array_key_exists("MODULE_ID", $arFile) || $arFile["MODULE_ID"] == '') $arFile["MODULE_ID"] = "support"; $fid = intval(CFile::SaveFile($arFile, $upload_dir, $max_file_size)); // если стоял флаг "Удалить" то if ($arFile["del"]=="Y") { // удалим связку $strSql = " DELETE FROM b_ticket_message_2_file WHERE FILE_ID=".intval($arFile["old_file"])." "; $DB->Query($strSql, false, $err_mess.__LINE__); } // если успешно загрузили файл то if ($fid>0) { // если это был новый файл то if (intval($arFile["old_file"])<=0) { // добавим связку $md5 = md5(uniqid(mt_rand(), true).time()); $arFields_fi = array( "HASH" => "'".$DB->ForSql($md5, 255)."'", "MESSAGE_ID" => $MESSAGE_ID, "FILE_ID" => $fid, "TICKET_ID" => $ticketID, "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null" ); $DB->Insert("b_ticket_message_2_file",$arFields_fi, $err_mess.__LINE__); } else // иначе { // обновим связку $arFields_fu = array( "FILE_ID" => $fid, "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null" ); $DB->Update("b_ticket_message_2_file", $arFields_fu, "WHERE FILE_ID = ".intval($arFile["old_file"]),$err_mess.__LINE__); } } } } } if ($arFields["IS_SPAM"]=="Y") CTicket::MarkMessageAsSpam($MESSAGE_ID,"Y",$checkRights); elseif ($arFields["IS_SPAM"]=="N") CTicket::MarkMessageAsSpam($MESSAGE_ID,"N",$checkRights); elseif ($arFields["IS_SPAM"]!="Y" && $arFields["IS_SPAM"]!="N") CTicket::UnMarkMessageAsSpam($MESSAGE_ID,$checkRights); //if ($notChangeStatus != "Y") //CTicket::UpdateLastParams($ticketID); //if ($notChangeStatus!="Y" && $hidden!="Y" && $log!="Y") //{ //CTicketReminder::Update($ticketID); //} CSupportSearch::reindexTicket($ticketID); } } } } public static function AddMessage($ticketID, $arFields, &$arrFILES, $checkRights="Y") { if ($arFields["MESSAGE"] <> '' || (is_array($arFields["FILES"]) && count($arFields["FILES"])>0)) { $err_mess = (CTicket::err_mess())."
Function: AddMessage
Line: "; global $DB, $USER; $bAdmin = "N"; $bSupportTeam = "N"; $bSupportClient = "N"; if ($checkRights=="Y") { $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N"; $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N"; $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N"; $uid = intval($USER->GetID()); } else { $bAdmin = "Y"; $bSupportTeam = "Y"; $bSupportClient = "Y"; //if (is_object($USER)) $uid = intval($USER->GetID()); else $uid = -1; $uid = 0; } if ($bAdmin!="Y" && $bSupportTeam!="Y" && $bSupportClient!="Y") { return false; } $ticketID = intval($ticketID); if ($ticketID<=0) { return 0; } $strSql = "SELECT RESPONSIBLE_USER_ID, LAST_MESSAGE_USER_ID, REOPEN, SITE_ID, TITLE FROM b_ticket WHERE ID='$ticketID'"; $rsTicket = $DB->Query($strSql, false, $err_mess.__LINE__); $arTicket = $rsTicket->Fetch(); $currentResponsibleUserID = $arTicket["RESPONSIBLE_USER_ID"]; $siteID = $arTicket["SITE_ID"]; $tTitle = $arTicket["TITLE"]; $strSql = "SELECT max(C_NUMBER) MAX_NUMBER FROM b_ticket_message WHERE TICKET_ID='$ticketID'"; $z = $DB->Query($strSql, false, $err_mess.__LINE__); $zr = $z->Fetch(); $maxNumber = intval($zr['MAX_NUMBER']); if ((trim($arFields["MESSAGE_AUTHOR_SID"]) <> '' || intval($arFields["MESSAGE_AUTHOR_USER_ID"])>0 || intval($arFields["MESSAGE_CREATED_USER_ID"])>0) && ($bSupportTeam=="Y" || $bAdmin=="Y")) { $ownerUserID = intval($arFields["MESSAGE_AUTHOR_USER_ID"]); $ownerSid = "'".$DB->ForSql($arFields["MESSAGE_AUTHOR_SID"],2000)."'"; $ownerGuestID = intval($arFields["MESSAGE_AUTHOR_GUEST_ID"])>0 ? intval($arFields["MESSAGE_AUTHOR_GUEST_ID"]) : "null"; $createdUserID = intval($arFields["MESSAGE_CREATED_USER_ID"])>0 ? intval($arFields["MESSAGE_CREATED_USER_ID"]) : intval($uid); $createdGuestID = intval($arFields["MESSAGE_CREATED_GUEST_ID"])>0 ? intval($arFields["MESSAGE_CREATED_GUEST_ID"]) : intval($_SESSION["SESS_GUEST_ID"]); } else { $ownerUserID = intval($uid); $ownerSid = "null"; $ownerGuestID = intval($_SESSION["SESS_GUEST_ID"]); $createdUserID = intval($uid); $createdGuestID = intval($_SESSION["SESS_GUEST_ID"]); } if (intval($ownerGuestID)<=0) { $ownerGuestID = "null"; } $MessageBySupportTeam = "null"; if ($ownerUserID<=0) { $ownerUserID = "null"; } else { $MessageBySupportTeam = "'N'"; if (CTicket::IsSupportTeam($ownerUserID) || CTicket::IsAdmin($ownerUserID)) { $MessageBySupportTeam = "'Y'"; } } if ($createdUserID<=0) { $createdUserID = "null"; } if (intval($createdGuestID)<=0) { $createdGuestID = "null"; } $createdModuleName = ($arFields["MESSAGE_CREATED_MODULE_NAME"] <> '') ? "'".$DB->ForSql($arFields["MESSAGE_CREATED_MODULE_NAME"],255)."'" : "'support'"; $externalID = intval($arFields["EXTERNAL_ID"])>0 ? intval($arFields["EXTERNAL_ID"]) : "null"; $externalField1 = $arFields["EXTERNAL_FIELD_1"]; if (is_set($arFields, "HIDDEN")) { $hidden = ($arFields["HIDDEN"]=="Y") ? "Y" : "N"; } elseif (is_set($arFields, "IS_HIDDEN")) { $hidden = ($arFields["IS_HIDDEN"]=="Y") ? "Y" : "N"; } $hidden = ($hidden=="Y") ? "Y" : "N"; $notChangeStatus = ( is_set($arFields, "NOT_CHANGE_STATUS") && $arFields["NOT_CHANGE_STATUS"]=="Y" ? "Y" : "N" ); $changeLastMessageDate = true; if ($arTicket["LAST_MESSAGE_USER_ID"] == $uid && $arTicket["REOPEN"] != "Y") { $changeLastMessageDate = false; } $TASK_TIME = intval($arFields["TASK_TIME"])>0 ? intval($arFields["TASK_TIME"]) : "null"; if (is_set($arFields, "LOG")) { $log = ($arFields["LOG"]=="Y") ? "Y" : "N"; } elseif (is_set($arFields, "IS_LOG")) { $log = ($arFields["IS_LOG"]=="Y") ? "Y" : "N"; } $log = ($log=="Y") ? "Y" : "N"; if (is_set($arFields, "OVERDUE")) { $overdue = ($arFields["OVERDUE"]=="Y") ? "Y" : "N"; } elseif (is_set($arFields, "IS_OVERDUE")) { $overdue = ($arFields["IS_OVERDUE"]=="Y") ? "Y" : "N"; } $overdue = ($overdue=="Y") ? "Y" : "N"; $arFieldsI = array( "TIMESTAMP_X" => $DB->GetNowFunction(), "DAY_CREATE" => $DB->CurrentDateFunction(), "C_NUMBER" => $maxNumber + 1, "TICKET_ID" => $ticketID, "IS_HIDDEN" => "'".$hidden."'", "IS_LOG" => "'".$log."'", "IS_OVERDUE" => "'".$overdue."'", "MESSAGE" => "'".$DB->ForSql($arFields["MESSAGE"])."'", "MESSAGE_SEARCH" => "'".$DB->ForSql(ToUpper($arFields["MESSAGE"]))."'", "EXTERNAL_ID" => $externalID, "EXTERNAL_FIELD_1" => ($externalField1 <> '' ? "'".$DB->ForSql($externalField1)."'" : "null"), "OWNER_USER_ID" => $ownerUserID, "OWNER_GUEST_ID" => $ownerGuestID, "OWNER_SID" => $ownerSid, "SOURCE_ID" => intval($arFields["MESSAGE_SOURCE_ID"]), "CREATED_USER_ID" => $createdUserID, "CREATED_GUEST_ID" => $createdGuestID, "CREATED_MODULE_NAME" => $createdModuleName, "MODIFIED_USER_ID" => $createdUserID, "MODIFIED_GUEST_ID" => $createdGuestID, "MESSAGE_BY_SUPPORT_TEAM" => $MessageBySupportTeam, "TASK_TIME" => $TASK_TIME, "NOT_CHANGE_STATUS" => "'".$notChangeStatus."'" ); CTimeZone::Disable(); $arFieldsI["DATE_CREATE"] = $DB->CharToDateFunction( GetTime( time() ,"FULL" ) ); CTimeZone::Enable(); /*if ($hidden!="Y" && $log!="Y" && $changeLastMessageDate == false) { if ($MessageBySupportTeam == "'Y'" || ($maxNumber <= 0 && array_key_exists('SOURCE_SID', $arFields) && $arFields['SOURCE_SID'] === 'email')) { $arFieldsI["NOT_CHANGE_STATUS"] = "'N'"; } else { $arFieldsI["NOT_CHANGE_STATUS"] = "'Y'"; } }*/ if (intval($currentResponsibleUserID)>0) $arFieldsI["CURRENT_RESPONSIBLE_USER_ID"] = $currentResponsibleUserID; $mid = $DB->Insert("b_ticket_message",$arFieldsI, $err_mess.__LINE__); if (intval($mid)>0) { $not_image_extension_suffix = COption::GetOptionString("support", "NOT_IMAGE_EXTENSION_SUFFIX"); $not_image_upload_dir = COption::GetOptionString("support", "NOT_IMAGE_UPLOAD_DIR"); $max_size = COption::GetOptionString("support", "SUPPORT_MAX_FILESIZE"); // сохраняем приаттаченные файлы $arFILES = $arFields["FILES"]; if (is_array($arFILES) && count($arFILES)>0) { foreach ($arFILES as $arFILE) { if ($arFILE["name"] <> '') { if ($bSupportTeam!="Y" && $bAdmin!="Y") { $max_file_size = intval($max_size) * 1024; } $fes = ""; $upload_dir = "support"; if (!CFile::IsImage($arFILE["name"], $arFILE["type"])) { $fes = $not_image_extension_suffix; $arFILE["name"] .= $fes; $upload_dir = $not_image_upload_dir; } if (!array_key_exists("MODULE_ID", $arFILE) || $arFILE["MODULE_ID"] == '') { $arFILE["MODULE_ID"] = "support"; } $fid = intval(CFile::SaveFile($arFILE, $upload_dir, $max_file_size)); if ($fid>0) { $md5 = md5(uniqid(mt_rand(), true).time()); $arFILE["HASH"] = $md5; $arFILE["FILE_ID"] = $fid; $arFILE["MESSAGE_ID"] = $mid; $arFILE["TICKET_ID"] = $ticketID; $arFILE["EXTENSION_SUFFIX"] = $fes; $arFields_fi = array( "HASH" => "'".$DB->ForSql($md5, 255)."'", "MESSAGE_ID" => $mid, "FILE_ID" => $fid, "TICKET_ID" => $ticketID, "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null" ); $link_id = $DB->Insert("b_ticket_message_2_file",$arFields_fi, $err_mess.__LINE__); if (intval($link_id)>0) { $arFILE["LINK_ID"] = $link_id; $arrFILES[] = $arFILE; } } } } } /* // если это не было скрытым сообщением или сообщение лога, то if ($notChangeStatus!="Y" && $hidden!="Y" && $log!="Y") { // обновим ряд параметров обращения if (!isset($arFields["AUTO_CLOSE_DAYS"])) $RESET_AUTO_CLOSE = "Y"; CTicket::UpdateLastParams($ticketID, $RESET_AUTO_CLOSE, $changeLastMessageDate, true); // при необходимости создадим или удалим агенты-напоминальщики //CTicketReminder::Update($ticketID); }*/ if ( $log!="Y" && CSupportSearch::isIndexExists()) { CSupportSearch::reindexTicket($ticketID); } //если была установлена галочка "не изменять статус обращени" - пересчитаем количество собщений if ($notChangeStatus == "Y" || $hidden == "Y") CTicket::UpdateMessages($ticketID); } } return $mid; } public static function GetStatus($ticketID) { $err_mess = (CTicket::err_mess())."
Function: GetStatus
Line: "; global $DB, $USER; $ticketID = intval($ticketID); if ($ticketID<=0) return false; $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N"; $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N"; $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N"; $bDemo = (CTicket::IsDemo()) ? "Y" : "N"; $uid = intval($USER->GetID()); if ($bSupportTeam=="Y" || $bAdmin=="Y" || $bDemo=="Y") { $lamp = " if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey', if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green', if(ifnull(T.OWNER_USER_ID,0)='$uid', 'red', if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s', if(ifnull(T.RESPONSIBLE_USER_ID,0)='$uid', 'red', 'yellow'))))) "; } else { $lamp = " if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey', if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green', 'red')) "; } $strSql = " SELECT $lamp LAMP FROM b_ticket T WHERE ID = $ticketID "; $rs = $DB->Query($strSql, false, $err_mess.__LINE__); if ($ar = $rs->Fetch()) return $ar["LAMP"]; return false; } public static function GetList($by = 's_default', $order = 'desc', $arFilter = [], $isFiltered = null, $checkRights = "Y", $getUserName = "Y", $getExtraNames = "Y", $siteID = false, $arParams = []) { $err_mess = (CTicket::err_mess())."
Function: GetList
Line: "; global $DB, $USER, $USER_FIELD_MANAGER; /** @var string $d_join Dictionary join */ $d_join = ""; $bAdmin = 'N'; $bSupportTeam = 'N'; $bSupportClient = 'N'; $bDemo = 'N'; /** @var string $messJoin Messages join */ $messJoin = ""; /** @var string $searchJoin Search table join */ $searchJoin = ''; $need_group = false; $arSqlHaving = array(); if ($checkRights=='Y') { $bAdmin = (CTicket::IsAdmin()) ? 'Y' : 'N'; $bSupportTeam = (CTicket::IsSupportTeam()) ? 'Y' : 'N'; $bSupportClient = (CTicket::IsSupportClient()) ? 'Y' : 'N'; $bDemo = (CTicket::IsDemo()) ? 'Y' : 'N'; $uid = intval($USER->GetID()); } else { $bAdmin = 'Y'; $bSupportTeam = 'Y'; $bSupportClient = 'Y'; $bDemo = 'Y'; if (is_object($USER)) $uid = intval($USER->GetID()); else $uid = -1; } if ($bAdmin!='Y' && $bSupportTeam!='Y' && $bSupportClient!='Y' && $bDemo!='Y') return false; if ($bSupportTeam=='Y' || $bAdmin=='Y' || $bDemo=='Y') { $lamp = " if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey', if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green', if(ifnull(T.OWNER_USER_ID,0)='$uid', 'red', if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s', if(ifnull(T.RESPONSIBLE_USER_ID,0)='$uid', 'red', 'yellow'))))) "; } else { $lamp = " if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey', if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y', 'red', 'green')) "; } $bJoinSupportTeamTbl = $bJoinClientTbl = false; $arSqlSearch = Array(); $strSqlSearch = ""; if (is_array($arFilter)) { $filterKeys = array_keys($arFilter); $filterKeysCount = count($filterKeys); for ($i=0; $i<$filterKeysCount; $i++) { $key = $filterKeys[$i]; $val = $arFilter[$filterKeys[$i]]; if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF'))) continue; $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false; $key = strtoupper($key); switch($key) { case "ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.ID",$val,$match); break; case "HOLD_ON": $arSqlSearch[] = ($val=="Y") ? "T.HOLD_ON='Y'" : "T.HOLD_ON = 'N'"; break; case "LID": case "SITE": case "SITE_ID": if (is_array($val)) $val = implode(" | ", $val); $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.SITE_ID",$val,$match); break; case "LAMP": if (is_array($val)) { if (count($val)>0) { $str = ""; foreach ($val as $value) { $str .= ", '".$DB->ForSQL($value)."'"; } $str = TrimEx($str, ","); $arSqlSearch[] = " ".$lamp." in (".$str.")"; } } elseif ($val <> '') { $arSqlSearch[] = " ".$lamp." = '".$DB->ForSQL($val)."'"; } break; case "DATE_CREATE_1": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CREATE_2": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "DATE_TIMESTAMP_1": if (CheckDateTime($val)) $arSqlSearch[] = "T.TIMESTAMP_X>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_TIMESTAMP_2": if (CheckDateTime($val)) $arSqlSearch[] = "T.TIMESTAMP_X<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "DATE_CLOSE_1": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CLOSE>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CLOSE_2": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CLOSE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "CLOSE": $arSqlSearch[] = ($val=="Y") ? "T.DATE_CLOSE is not null" : "T.DATE_CLOSE is null"; break; case "AUTO_CLOSE_DAYS1": $arSqlSearch[] = "T.AUTO_CLOSE_DAYS>='".intval($val)."'"; break; case "AUTO_CLOSE_DAYS2": $arSqlSearch[] = "T.AUTO_CLOSE_DAYS<='".intval($val)."'"; break; case "TICKET_TIME_1": $arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)>='".(intval($val)*86400)."'"; break; case "TICKET_TIME_2": $arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)<='".(intval($val)*86400)."'"; break; case "TITLE": $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.TITLE", $val, $match); break; case "MESSAGES1": $arSqlSearch[] = "T.MESSAGES>='".intval($val)."'"; break; case "MESSAGES2": $arSqlSearch[] = "T.MESSAGES<='".intval($val)."'"; break; case "PROBLEM_TIME1": $arSqlSearch[] = "T.PROBLEM_TIME>='".intval($val)."'"; break; case "PROBLEM_TIME2": $arSqlSearch[] = "T.PROBLEM_TIME<='".intval($val)."'"; break; case "OVERDUE_MESSAGES1": $arSqlSearch[] = "T.OVERDUE_MESSAGES>='".intval($val)."'"; break; case "OVERDUE_MESSAGES2": $arSqlSearch[] = "T.OVERDUE_MESSAGES<='".intval($val)."'"; break; case "AUTO_CLOSE_DAYS_LEFT1": $arSqlSearch[] = "CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN TO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)) - TO_DAYS(now()) ELSE -1 END >='".intval($val)."'"; break; case "AUTO_CLOSE_DAYS_LEFT2": $arSqlSearch[] = "CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN TO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now()) ELSE 999 END <='".intval($val)."'"; break; case "OWNER": $getUserName = "Y"; $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("UO.ID, UO.LOGIN, UO.LAST_NAME, UO.NAME, T.OWNER_SID", $val, $match, array("@", ".")); //T.OWNER_USER_ID, break; case "OWNER_USER_ID": case "OWNER_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match); break; case "SLA_ID": case "SLA": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match); break; case "CREATED_BY": $getUserName = "Y"; $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.CREATED_USER_ID, UC.LOGIN, UC.LAST_NAME, UC.NAME, T.CREATED_MODULE_NAME", $val, $match); break; case "RESPONSIBLE": $getUserName = "Y"; $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match); break; case "RESPONSIBLE_ID": if (intval($val)>0) $arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)"; break; case "CATEGORY_ID": case "CATEGORY": if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)"; break; case "CATEGORY_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DC.SID", $val, $match); $d_join = " LEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')"; break; case "CRITICALITY_ID": case "CRITICALITY": if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)"; break; case "CRITICALITY_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DK.SID", $val, $match); break; case "STATUS_ID": case "STATUS": if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)"; break; case "STATUS_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DS.SID", $val, $match); break; case "MARK_ID": case "MARK": if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)"; break; case "MARK_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DM.SID", $val, $match); break; case "SOURCE_ID": case "SOURCE": if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)"; break; case "SOURCE_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DSR.SID", $val, $match); break; case "DIFFICULTY_ID": case "DIFFICULTY": if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)"; break; case "DIFFICULTY_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("DD.SID", $val, $match); break; case "MODIFIED_BY": $getUserName = "Y"; $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.MODIFIED_USER_ID, T.MODIFIED_MODULE_NAME, UM.LOGIN, UM.LAST_NAME, UM.NAME", $val, $match); break; case "MESSAGE": global $strError; if( $val == '' ) break; if(CSupportSearch::CheckModule() && CSupportSearch::isIndexExists()) { // new indexed search $searchSqlParams = CSupportSearch::getSql($val); $searchOn = $searchSqlParams['WHERE']; $searchHaving = $searchSqlParams['HAVING']; if ($searchOn) { $searchJoin = 'INNER JOIN b_ticket_search TS ON TS.TICKET_ID = T.ID AND '.$searchOn; if (!empty($searchHaving)) { // 2 or more search words $arSqlHaving[] = $searchHaving; $need_group = true; } } } else { if ($bSupportTeam=="Y" || $bAdmin=="Y" || $bDemo=="Y") { $messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID)"; } else { $messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID and M.IS_HIDDEN='N' and M.IS_LOG='N')"; } $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $f = new CFilterQuery("OR", "yes", $match, array(), "N", "Y", "N"); $query = $f->GetQueryString( "T.TITLE,M.MESSAGE_SEARCH", $val ); $error = $f->error; if (trim($error) <> '') { $strError .= $error."
"; $query = "0"; } else $arSqlSearch[] = $query; } break; case "LAST_MESSAGE_USER_ID": case "LAST_MESSAGE_SID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match); break; case "LAST_MESSAGE_BY_SUPPORT_TEAM": $arSqlSearch[] = "T.LAST_MESSAGE_BY_SUPPORT_TEAM= '".($val == 'Y' ? 'Y' : 'N')."'"; break; case "SUPPORT_COMMENTS": $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.SUPPORT_COMMENTS", $val, $match); break; case "IS_SPAM": $arSqlSearch[] = ($val=="Y") ? "T.IS_SPAM ='Y'" : "(T.IS_SPAM = 'N' or T.IS_SPAM is null)"; break; case "IS_OVERDUE": $arSqlSearch[] = ($val=="Y") ? "T.IS_OVERDUE ='Y'" : "(T.IS_OVERDUE = 'N' or T.IS_OVERDUE is null)"; break; case "IS_SPAM_MAYBE": $arSqlSearch[] = ($val=="Y") ? "T.IS_SPAM='N'" : "(T.IS_SPAM='Y' or T.IS_SPAM is null)"; break; case 'SUPPORTTEAM_GROUP_ID': case 'CLIENT_GROUP_ID': if ($key == 'SUPPORTTEAM_GROUP_ID') { $table = 'UGS'; $bJoinSupportTeamTbl = true; } else { $table = 'UGC'; $bJoinClientTbl = true; } if (is_array($val)) { $val = array_map('intval', $val); $val = array_unique($val); $val = array_filter($val); if (count($val) > 0) { $arSqlSearch[] = '('.$table.'.GROUP_ID IS NOT NULL AND '.$table.'.GROUP_ID IN ('.implode(',', $val).'))'; } } else { $val = intval($val); if ($val > 0) { $arSqlSearch[] = '('.$table.'.GROUP_ID IS NOT NULL AND '.$table.'.GROUP_ID=\''.$val.'\')'; } } break; case 'COUPON': $match = ($matchValueSet && $arFilter[$key."_EXACT_MATCH"]!="Y") ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match); break; } } } $obUserFieldsSql = new CUserTypeSQL; $obUserFieldsSql->SetEntity("SUPPORT", "T.ID"); $obUserFieldsSql->SetSelect( $arParams["SELECT"] ); $obUserFieldsSql->SetFilter( $arFilter ); $obUserFieldsSql->SetOrder( array( $by => $order) ); if ($by == "s_id") { $strSqlOrder = "ORDER BY T.ID"; } elseif ($by == "s_last_message_date") { $strSqlOrder = "ORDER BY T.LAST_MESSAGE_DATE"; } elseif ($by == "s_site_id" || $by == "s_lid") { $strSqlOrder = "ORDER BY T.SITE_ID"; } elseif ($by == "s_lamp") { $strSqlOrder = "ORDER BY LAMP"; } elseif ($by == "s_is_overdue") { $strSqlOrder = "ORDER BY T.IS_OVERDUE"; } elseif ($by == "s_is_notified") { $strSqlOrder = "ORDER BY T.IS_NOTIFIED"; } elseif ($by == "s_date_create") { $strSqlOrder = "ORDER BY T.DATE_CREATE"; } elseif ($by == "s_timestamp" || $by == "s_timestamp_x") { $strSqlOrder = "ORDER BY T.TIMESTAMP_X"; } elseif ($by == "s_date_close") { $strSqlOrder = "ORDER BY T.DATE_CLOSE"; } elseif ($by == "s_owner") { $strSqlOrder = "ORDER BY T.OWNER_USER_ID"; } elseif ($by == "s_modified_by") { $strSqlOrder = "ORDER BY T.MODIFIED_USER_ID"; } elseif ($by == "s_title") { $strSqlOrder = "ORDER BY T.TITLE "; } elseif ($by == "s_responsible") { $strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID"; } elseif ($by == "s_messages") { $strSqlOrder = "ORDER BY T.MESSAGES"; } elseif ($by == "s_category") { $strSqlOrder = "ORDER BY T.CATEGORY_ID"; } elseif ($by == "s_criticality") { $strSqlOrder = "ORDER BY T.CRITICALITY_ID"; } elseif ($by == "s_sla") { $strSqlOrder = "ORDER BY T.SLA_ID"; } elseif ($by == "s_status") { $strSqlOrder = "ORDER BY T.STATUS_ID"; } elseif ($by == "s_difficulty") { $strSqlOrder = "ORDER BY T.DIFFICULTY_ID"; } elseif ($by == "s_problem_time") { $strSqlOrder = "ORDER BY T.PROBLEM_TIME"; } elseif ($by == "s_mark") { $strSqlOrder = "ORDER BY T.MARK_ID"; } elseif ($by == "s_online") { $strSqlOrder = "ORDER BY USERS_ONLINE"; } elseif ($by == "s_support_comments") { $strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS"; } elseif ($by == "s_auto_close_days_left") { $strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT"; } elseif ($by == 's_coupon') { $strSqlOrder = 'ORDER BY T.COUPON'; } elseif ($by == 's_deadline') { $strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE'; } elseif( $s = $obUserFieldsSql->GetOrder($by) ) { $strSqlOrder = "ORDER BY ".strtoupper($s); } else { $strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE"; } if ($order!="asc") { $strSqlOrder .= " desc "; } $arSqlSearch[] = $obUserFieldsSql->GetFilter(); if ($getUserName=="Y") { $u_select = " , UO.LOGIN OWNER_LOGIN, UO.EMAIL OWNER_EMAIL, concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) OWNER_NAME, UR.LOGIN RESPONSIBLE_LOGIN, UR.EMAIL RESPONSIBLE_EMAIL, concat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,'')) RESPONSIBLE_NAME, UM.LOGIN MODIFIED_BY_LOGIN, UM.EMAIL MODIFIED_BY_EMAIL, concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_BY_NAME, UM.LOGIN MODIFIED_LOGIN, UM.EMAIL MODIFIED_EMAIL, concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_NAME, UL.LOGIN LAST_MESSAGE_LOGIN, UL.EMAIL LAST_MESSAGE_EMAIL, concat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,'')) LAST_MESSAGE_NAME, UC.LOGIN CREATED_LOGIN, UC.EMAIL CREATED_EMAIL, concat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,'')) CREATED_NAME "; $u_join = " LEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID) LEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID) LEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID) LEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID) LEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID) "; } if ($getExtraNames=="Y") { $d_select = " , DC.NAME CATEGORY_NAME, DC.DESCR CATEGORY_DESC, DC.SID CATEGORY_SID, DK.NAME CRITICALITY_NAME, DK.DESCR CRITICALITY_DESC, DK.SID CRITICALITY_SID, DS.NAME STATUS_NAME, DS.DESCR STATUS_DESC, DS.SID STATUS_SID, DM.NAME MARK_NAME, DM.DESCR MARK_DESC, DM.SID MARK_SID, DSR.NAME SOURCE_NAME, DSR.DESCR SOURCE_DESC, DSR.SID SOURCE_SID, DD.NAME DIFFICULTY_NAME, DD.DESCR DIFFICULTY_DESC, DD.SID DIFFICULTY_SID, SLA.NAME SLA_NAME "; $d_join = " LEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C') LEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K') LEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S') LEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M') LEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR') LEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D') LEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID) "; } if ($siteID <> '') { $dates_select = " ".$DB->DateToCharFunction("T.DATE_CREATE","FULL",$siteID,true)." DATE_CREATE, ".$DB->DateToCharFunction("T.TIMESTAMP_X","FULL",$siteID,true)." TIMESTAMP_X, ".$DB->DateToCharFunction("T.LAST_MESSAGE_DATE","FULL",$siteID,true)." LAST_MESSAGE_DATE, ".$DB->DateToCharFunction("T.DATE_CLOSE","FULL",$siteID,true)." DATE_CLOSE, ".$DB->DateToCharFunction("T.DATE_CREATE","SHORT",$siteID,true)." DATE_CREATE_SHORT, ".$DB->DateToCharFunction("T.TIMESTAMP_X","SHORT",$siteID,true)." TIMESTAMP_X_SHORT, ".$DB->DateToCharFunction("T.DATE_CLOSE","SHORT",$siteID,true)." DATE_CLOSE_SHORT, ".$DB->DateToCharFunction("T.SUPPORT_DEADLINE","FULL",$siteID,true)." SUPPORT_DEADLINE, CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " .$DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)","FULL",$siteID,true) ." ELSE NULL END AUTO_CLOSE_DATE "; } else { $dates_select = " ".$DB->DateToCharFunction("T.DATE_CREATE","FULL")." DATE_CREATE, ".$DB->DateToCharFunction("T.TIMESTAMP_X","FULL")." TIMESTAMP_X, ".$DB->DateToCharFunction("T.LAST_MESSAGE_DATE","FULL")." LAST_MESSAGE_DATE, ".$DB->DateToCharFunction("T.DATE_CLOSE","FULL")." DATE_CLOSE, ".$DB->DateToCharFunction("T.DATE_CREATE","SHORT")." DATE_CREATE_SHORT, ".$DB->DateToCharFunction("T.TIMESTAMP_X","SHORT")." TIMESTAMP_X_SHORT, ".$DB->DateToCharFunction("T.DATE_CLOSE","SHORT")." DATE_CLOSE_SHORT, ".$DB->DateToCharFunction("T.SUPPORT_DEADLINE","FULL")." SUPPORT_DEADLINE, CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN " .$DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)","FULL") ." ELSE NULL END AUTO_CLOSE_DATE "; } $ugroupJoin = ''; if ($bJoinSupportTeamTbl) { $ugroupJoin .= " LEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) "; $need_group = true; } if ($bJoinClientTbl) { $ugroupJoin .= " LEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) "; $need_group = true; } // add permissions check if (!($bAdmin == 'Y' || $bDemo == 'Y')) { // a list of users who own or are responsible for tickets, which we can show to our current user $ticketUsers = array($uid); // check if user has groups $result = $DB->Query('SELECT GROUP_ID FROM b_ticket_user_ugroup WHERE USER_ID = '.$uid.' AND CAN_VIEW_GROUP_MESSAGES = \'Y\''); if ($result) { // collect members of these groups $uGroups = array(); while ($row = $result->Fetch()) { $uGroups[] = $row['GROUP_ID']; } if (!empty($uGroups)) { $result = $DB->Query('SELECT USER_ID FROM b_ticket_user_ugroup WHERE GROUP_ID IN ('.join(',', $uGroups).')'); if ($result) { while ($row = $result->Fetch()) { $ticketUsers[] = $row['USER_ID']; } } } } // build sql $strSqlSearchUser = ""; if($bSupportTeam == 'Y') { $strSqlSearchUser = 'T.RESPONSIBLE_USER_ID IN ('.join(',', $ticketUsers).')'; } elseif ($bSupportClient == 'Y') { $strSqlSearchUser = 'T.OWNER_USER_ID IN ('.join(',', $ticketUsers).')'; } $arSqlSearch[] = $strSqlSearchUser; } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL")); $strSqlSelect = " SELECT T.*, T.SITE_ID, T.SITE_ID LID, $dates_select, UNIX_TIMESTAMP(T.DATE_CLOSE)-UNIX_TIMESTAMP(T.DATE_CREATE) TICKET_TIME, CASE WHEN (UNIX_TIMESTAMP(T.DATE_CLOSE) IS NULL OR UNIX_TIMESTAMP(T.DATE_CLOSE) = 0) AND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y' THEN TO_DAYS( ADDDATE( T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY ) ) - TO_DAYS(now()) ELSE -1 END AUTO_CLOSE_DAYS_LEFT, (SELECT COUNT(DISTINCT USER_ID) FROM b_ticket_online WHERE TICKET_ID = T.ID AND TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - ".$onlineInterval." SECOND)) USERS_ONLINE, if(T.COUPON IS NOT NULL, 1, 0) IS_SUPER_TICKET, $lamp LAMP $d_select $u_select " . $obUserFieldsSql->GetSelect(); $strSqlFrom = " FROM b_ticket T $u_join $d_join $messJoin $searchJoin $ugroupJoin " . $obUserFieldsSql->GetJoin("T.ID"); $strSqlWhere = " WHERE $strSqlSearch "; $strSqlGroup = $need_group ? ' GROUP BY T.ID ' : ''; $strSqlHaving = $arSqlHaving ? ' HAVING ' . join(' AND ', $arSqlHaving) . ' ' : ''; $strSql = $strSqlSelect . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving . $strSqlOrder; if (is_array($arParams) && isset($arParams["NAV_PARAMS"]) && is_array($arParams["NAV_PARAMS"])) { $nTopCount = isset($arParams['NAV_PARAMS']['nTopCount']) ? intval($arParams['NAV_PARAMS']['nTopCount']) : 0; if($nTopCount > 0) { $strSql = $DB->TopSql($strSql, $nTopCount); $res = $DB->Query($strSql, false, $err_mess.__LINE__); $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") ); } else { $cntSql = "SELECT COUNT(T.ID) as C " . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving; if (!empty($strSqlGroup)) { $cntSql = 'SELECT COUNT(1) AS C FROM ('.$cntSql.') tt'; } $res_cnt = $DB->Query($cntSql); $res_cnt = $res_cnt->Fetch(); $res = new CDBResult(); $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") ); $res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]); } } else { $res = $DB->Query($strSql, false, $err_mess.__LINE__); $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") ); } return $res; } public static function GetSupportTeamList() { $err_mess = (CTicket::err_mess())."
Function: GetSupportTeamList
Line: "; global $DB; $arrGid = CTicket::GetSupportTeamGroups(); $arrAid = CTicket::GetAdminGroups(); if (count($arrGid)>0) { $gid = implode(",",$arrGid); } else { $gid = 0; } if (count($arrAid)>0) { $aid = implode(",",$arrAid); } else { $aid = 0; } $strSql = " SELECT DISTINCT U.ID as REFERENCE_ID, concat('[',U.ID,'] ',' (',U.LOGIN,') ',ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) as REFERENCE, U.ACTIVE FROM b_user U, b_user_group G WHERE U.ID = G.USER_ID and G.GROUP_ID in ($gid, $aid) ORDER BY U.ID "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; } /*function GetResponsibleList($user_id) { $err_mess = (CTicket::err_mess())."
Function: GetSupportTeamMailList
Line: "; global $DB; $strSql = " SELECT DISTINCT U.ID as ID, U.LOGIN as LOGIN, concat('[',U.ID,'] ',' (',U.LOGIN,') ',ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) as NAME, U.EMAIL as EMAIL FROM b_user U, b_ticket_user_ugroup TUG, b_ticket_user_ugroup TUG2 WHERE TUG.USER_ID = '".intval($user_id)."' and TUG2.GROUP_ID = TUG.GROUP_ID and U.ID = TUG2.USER_ID and TUG2.CAN_MAIL_GROUP_MESSAGES = 'Y' ORDER BY U.ID "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; }*/ public static function GetMessageList($by = 's_number', $order = 'asc', $arFilter = [], $isFiltered = null, $checkRights = "Y", $getUserName = "Y") { $err_mess = (CTicket::err_mess())."
Function: GetMessageList
Line: "; global $DB, $USER, $APPLICATION; if ($checkRights=="Y") { $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N"; $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N"; $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N"; $bDemo = (CTicket::IsDemo()) ? "Y" : "N"; } else { $bAdmin = "Y"; $bSupportTeam = "Y"; $bSupportClient = "Y"; $bDemo = "Y"; } if ($bAdmin!="Y" && $bSupportTeam!="Y" && $bSupportClient!="Y" && $bDemo!="Y") return false; $arSqlSearch = Array(); if (is_array($arFilter)) { $filterKeys = array_keys($arFilter); $filterKeysCount = count($filterKeys); for ($i=0; $i<$filterKeysCount; $i++) { $key = $filterKeys[$i]; $val = $arFilter[$filterKeys[$i]]; if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF'))) continue; $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false; $key = strtoupper($key); switch($key) { case "ID": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("M.ID",$val,$match); break; case "TICKET_ID": $arSqlSearch[] = "M.TICKET_ID = ".intval($val); break; case "TICKET": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("M.TICKET_ID",$val,$match); break; case "IS_MESSAGE": $arSqlSearch[] = ($val=="Y") ? "(M.IS_HIDDEN = 'N' and M.IS_LOG='N' and M.IS_OVERDUE='N')" : "(M.IS_HIDDEN = 'Y' or M.IS_LOG='Y' or M.IS_OVERDUE='Y')"; break; case "IS_HIDDEN": case "IS_LOG": case "IS_OVERDUE": case "NOT_CHANGE_STATUS": case "MESSAGE_BY_SUPPORT_TEAM": $arSqlSearch[] = ($val=="Y") ? "M.".$key."='Y'" : "M.".$key."='N'"; break; case "EXTERNAL_FIELD_1": $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("M.EXTERNAL_FIELD_1", $val, $match); break; } } } if ($getUserName=="Y") { $u_select = " , UO.EMAIL OWNER_EMAIL, UO.LOGIN OWNER_LOGIN, concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) OWNER_NAME, UO.LOGIN LOGIN, concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) NAME, UC.EMAIL CREATED_EMAIL, UC.LOGIN CREATED_LOGIN, concat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,'')) CREATED_NAME, UM.EMAIL MODIFIED_EMAIL, UM.LOGIN MODIFIED_LOGIN, concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_NAME "; $u_join = " LEFT JOIN b_user UO ON (UO.ID = M.OWNER_USER_ID) LEFT JOIN b_user UC ON (UC.ID = M.CREATED_USER_ID) LEFT JOIN b_user UM ON (UM.ID = M.MODIFIED_USER_ID) "; } if ($bSupportTeam!="Y" && $bAdmin!="Y") { $arSqlSearch[] = "M.IS_HIDDEN='N'"; $arSqlSearch[] = "M.IS_LOG='N'"; } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); if ($by == "s_id") $strSqlOrder = "ORDER BY M.ID"; elseif ($by == "s_number") $strSqlOrder = "ORDER BY M.C_NUMBER"; else { $strSqlOrder = "ORDER BY M.C_NUMBER"; } if ($order=="desc") { $strSqlOrder .= " desc "; } else { $strSqlOrder .= " asc "; } $strSql = " SELECT M.*, T.SLA_ID, ".$DB->DateToCharFunction("M.DATE_CREATE")." DATE_CREATE, ".$DB->DateToCharFunction("M.TIMESTAMP_X")." TIMESTAMP_X, DS.NAME SOURCE_NAME $u_select FROM b_ticket_message M INNER JOIN b_ticket T ON (T.ID = M.TICKET_ID) LEFT JOIN b_ticket_dictionary DS ON (DS.ID = M.SOURCE_ID) $u_join WHERE $strSqlSearch $strSqlOrder "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; } public static function GetDynamicList($by = 's_date_create', $order = 'desc', $arFilter = []) { $err_mess = (CTicket::err_mess())."
Function: GetDynamicList
Line: "; global $DB; $arSqlSearch = Array(); if (is_array($arFilter)) { $filterKeys = array_keys($arFilter); $filterKeysCount = count($filterKeys); for ($i=0; $i<$filterKeysCount; $i++) { $key = $filterKeys[$i]; $val = $arFilter[$filterKeys[$i]]; if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF'))) continue; $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false; $key = strtoupper($key); switch($key) { case "DATE_CREATE_1": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CREATE_2": if (CheckDateTime($val)) $arSqlSearch[] = "T.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "RESPONSIBLE": $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match); break; case "RESPONSIBLE_ID": if (intval($val)>0) $arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)"; break; case "SLA_ID": case "SLA": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match); break; case "CATEGORY_ID": case "CATEGORY": if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)"; break; case "CRITICALITY_ID": case "CRITICALITY": if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)"; break; case "STATUS_ID": case "STATUS": if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)"; break; case "MARK_ID": case "MARK": if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)"; break; case "SOURCE_ID": case "SOURCE": if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)"; break; case "DIFFICULTY_ID": case "DIFFICULTY": if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)"; break; } } } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); if ($by == "s_date_create") $strSqlOrder = "ORDER BY T.DATE_CREATE"; else { $strSqlOrder = "ORDER BY T.DATE_CREATE"; } if ($order!="asc") { $strSqlOrder .= " desc "; } $strSql = " SELECT count(T.ID) ALL_TICKETS, sum(if(T.DATE_CLOSE is null,1,0)) OPEN_TICKETS, sum(if(T.DATE_CLOSE is null,0,1)) CLOSE_TICKETS, DAYOFMONTH(T.DAY_CREATE) CREATE_DAY, MONTH(T.DAY_CREATE) CREATE_MONTH, YEAR(T.DAY_CREATE) CREATE_YEAR FROM b_ticket T LEFT JOIN b_user UR ON (T.RESPONSIBLE_USER_ID = UR.ID) WHERE $strSqlSearch and T.DAY_CREATE is not null GROUP BY TO_DAYS(T.DAY_CREATE) $strSqlOrder "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; } public static function GetMessageDynamicList($by = 's_date_create', $order = 'desc', $arFilter = []) { $err_mess = (CTicket::err_mess())."
Function: GetMessageDynamicList
Line: "; global $DB; $arSqlSearch = Array(); if (is_array($arFilter)) { $filterKeys = array_keys($arFilter); $filterKeysCount = count($filterKeys); for ($i=0; $i<$filterKeysCount; $i++) { $key = $filterKeys[$i]; $val = $arFilter[$filterKeys[$i]]; if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF'))) continue; $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false; $key = strtoupper($key); switch($key) { case "SITE": case "SITE_ID": if (is_array($val)) $val = implode(" | ", $val); $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.SITE_ID",$val,$match); break; case "DATE_CREATE_1": if (CheckDateTime($val)) $arSqlSearch[] = "M.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT"); break; case "DATE_CREATE_2": if (CheckDateTime($val)) $arSqlSearch[] = "M.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY"; break; case "OWNER": $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("M.OWNER_USER_ID, U.LOGIN, U.LAST_NAME, U.NAME", $val, $match); break; case "OWNER_ID": if (intval($val)>0) $arSqlSearch[] = "M.OWNER_USER_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(M.OWNER_USER_ID is null or M.OWNER_USER_ID=0)"; break; case "IS_HIDDEN": case "IS_LOG": case "IS_OVERDUE": $arSqlSearch[] = ($val=="Y") ? "M.".$key."='Y'" : "M.".$key."='N'"; break; case "SLA_ID": case "SLA": $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N"; $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match); break; case "CATEGORY_ID": case "CATEGORY": if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)"; break; case "CRITICALITY_ID": case "CRITICALITY": if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)"; break; case "STATUS_ID": case "STATUS": if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)"; break; case "MARK_ID": case "MARK": if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)"; break; case "SOURCE_ID": case "SOURCE": if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)"; break; case "DIFFICULTY_ID": case "DIFFICULTY": if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'"; elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)"; break; } } } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); if ($by == "s_date_create") $strSqlOrder = "ORDER BY M.DATE_CREATE"; else { $strSqlOrder = "ORDER BY M.DATE_CREATE"; } if ($order!="asc") { $strSqlOrder .= " desc "; } $strSql = " SELECT count(M.ID) COUNTER, sum(if(M.EXPIRE_AGENT_DONE='Y', 1, 0)) COUNTER_OVERDUE, DAYOFMONTH(M.DAY_CREATE) CREATE_DAY, MONTH(M.DAY_CREATE) CREATE_MONTH, YEAR(M.DAY_CREATE) CREATE_YEAR FROM b_ticket_message M INNER JOIN b_ticket T ON (T.ID = M.TICKET_ID) LEFT JOIN b_user U ON (M.OWNER_USER_ID = U.ID) WHERE $strSqlSearch GROUP BY TO_DAYS(M.DAY_CREATE) $strSqlOrder "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; } }