1C-Bitrix 25.700.0
Загрузка...
Поиск...
Не найдено
support.php
См. документацию.
1<?
2require_once($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/support/classes/general/support.php");
3
4class CTicket extends CAllTicket
5{
6 public static function isnull( $field, $alternative )
7 {
8 return "ifnull(" . $field . "," . $alternative . ")";
9 }
10
11 public static function AutoClose()
12 {
13 global $DB;
14 /*$strSql = "
15 SELECT
16 T.ID
17 FROM
18 b_ticket T
19 WHERE
20 T.AUTO_CLOSE_DAYS > 0
21 and (T.DATE_CLOSE is null or length(T.DATE_CLOSE)<=0)
22 and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(T.TIMESTAMP_X))/86400 > T.AUTO_CLOSE_DAYS
23 ";*/
24
25 $nowTime = $DB->CharToDateFunction(GetTime(time() + CTimeZone::GetOffset(),"FULL"));
26 $strSql = "
27 SELECT
28 T.ID
29 FROM
30 b_ticket T
31 WHERE
32 T.AUTO_CLOSE_DAYS > 0
33 and (T.DATE_CLOSE is null or length(T.DATE_CLOSE)<=0)
34 and (UNIX_TIMESTAMP($nowTime)-UNIX_TIMESTAMP(T.LAST_MESSAGE_DATE))/86400 > T.AUTO_CLOSE_DAYS
35 and T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'Y'
36 ";//now()
37
38 $rsTickets = $DB->Query($strSql);
39 while ($arTicket = $rsTickets->Fetch())
40 {
41 $arFields = array(
42 "TIMESTAMP_X" => $DB->GetNowFunction(),
43 "DATE_CLOSE" => $DB->GetNowFunction(),
44 "MODIFIED_USER_ID" => "null",
45 "MODIFIED_GUEST_ID" => "null",
46 "MODIFIED_MODULE_NAME" => "'auto closing'",
47 //"AUTO_CLOSE_DAYS" => "null",
48 "AUTO_CLOSED" => "'Y'"
49 );
50 $DB->Update("b_ticket",$arFields,"WHERE ID='".$arTicket["ID"]."'");
51 }
52 return "CTicket::AutoClose();";
53 }
54
55 public static function CleanUpOnline()
56 {
57 global $DB;
58 $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
59 $strSql = "
60 DELETE FROM b_ticket_online WHERE
61 TIMESTAMP_X < DATE_ADD(now(), INTERVAL - $onlineInterval SECOND)
62 ";
63 $DB->Query($strSql);
64 return "CTicket::CleanUpOnline();";
65 }
66
67 public static function GetOnline($ticketID)
68 {
69 global $DB;
70 $ticketID = intval($ticketID);
71 $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
72 $strSql = "
73 SELECT
74 ".$DB->DateToCharFunction("max(T.TIMESTAMP_X)")." TIMESTAMP_X,
75 T.USER_ID,
76 T.CURRENT_MODE,
77 U.EMAIL USER_EMAIL,
78 U.LOGIN USER_LOGIN,
79 concat(ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) USER_NAME
80 FROM
81 b_ticket_online T,
82 b_user U
83 WHERE
84 T.TICKET_ID = $ticketID
85 and T.TIMESTAMP_X >= DATE_ADD(now(), INTERVAL - $onlineInterval SECOND)
86 and U.ID = T.USER_ID
87 GROUP BY
88 T.USER_ID, U.EMAIL, U.LOGIN, U.NAME, U.LAST_NAME
89 ORDER BY
90 T.USER_ID
91 ";
92
93 $z = $DB->Query($strSql);
94 return $z;
95 }
96
97 public static function DeleteMessage($ID, $checkRights="Y")
98 {
99 global $DB;
100 $ID = intval($ID);
101 if ($ID<=0) return;
102
103 $bAdmin = "N";
104 if ($checkRights=="Y")
105 {
106 $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N";
107 }
108 else
109 {
110 $bAdmin = "Y";
111 }
112
113 if ($bAdmin=="Y")
114 {
115 $strSql = "
116 SELECT
117 F.ID FILE_ID,
118 M.TICKET_ID
119 FROM
120 b_ticket_message M
121 LEFT JOIN b_ticket_message_2_file MF ON (MF.MESSAGE_ID = M.ID)
122 LEFT JOIN b_file F ON (F.ID = MF.FILE_ID)
123 WHERE
124 M.ID='$ID'
125 ";
126
127 $z = $DB->Query($strSql);
128 while ($zr = $z->Fetch())
129 {
130 $ticketID = $zr["TICKET_ID"];
131 if (intval($zr["FILE_ID"])>0)
132 {
133 CFile::Delete($zr["FILE_ID"]);
134 }
135 }
136
137 $z = $DB->Query("DELETE FROM b_ticket_message WHERE ID='$ID'");
138 if (intval($z->AffectedRowsCount())>0)
139 {
140 //CTicket::UpdateLastParams($ticketID);
141 //CTicket::UpdateLastParams2($ticketID, CTicket::DELETE);
142 CTicket::UpdateLastParamsN($ticketID, array("EVENT"=>array(CTicket::DELETE)), true, true);
143
144 if (CSupportSearch::isIndexExists())
145 {
146 CSupportSearch::reindexTicket($ticketID);
147 }
148 }
149 }
150 }
151
152 public static function UpdateMessage($MESSAGE_ID, $arFields, $checkRights="Y")
153 {
154 global $DB, $USER;
155
156 $MESSAGE_ID = intval($MESSAGE_ID);
157 $bAdmin = "N";
158 $bSupportTeam = "N";
159 if ($checkRights=="Y")
160 {
161 $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N";
162 $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N";
163 $uid = $USER->GetID();
164 }
165 else
166 {
167 $bAdmin = "Y";
168 $bSupportTeam = "Y";
169 $uid = 0;
170 }
171
172 if ($bAdmin=="Y")
173 {
174 $ownerSid = $arFields["OWNER_SID"];
175 $ownerUserID = $arFields["OWNER_USER_ID"];
176 $arFields_u = array(
177 "TIMESTAMP_X" => $DB->GetNowFunction(),
178 "C_NUMBER" => intval($arFields["C_NUMBER"]),
179 "MESSAGE" => "'".$DB->ForSql($arFields["MESSAGE"])."'",
180 "MESSAGE_SEARCH" => "'".mb_strtoupper($DB->ForSql($arFields["MESSAGE"]))."'",
181 "SOURCE_ID" => (intval($arFields["SOURCE_ID"])>0 ? intval($arFields["SOURCE_ID"]) : "null"),
182 "OWNER_SID" => "'".$DB->ForSql($ownerSid, 255)."'",
183 "OWNER_USER_ID" => (intval($ownerUserID)>0 ? intval($ownerUserID) : "null"),
184 "MODIFIED_USER_ID" => (intval($uid)>0 ? intval($uid) : "null"),
185 "MODIFIED_GUEST_ID" => (intval($_SESSION["SESS_GUEST_ID"])>0 ? intval($_SESSION["SESS_GUEST_ID"]) : "null"),
186 "EXTERNAL_ID" => (intval($arFields["EXTERNAL_ID"])>0 ? intval($arFields["EXTERNAL_ID"]) : "null"),
187 "TASK_TIME" => (intval($arFields["TASK_TIME"])>0 ? intval($arFields["TASK_TIME"]) : "null"),
188 "EXTERNAL_FIELD_1" => "'".$DB->ForSql($arFields["EXTERNAL_FIELD_1"])."'",
189 "IS_SPAM" => ($arFields["IS_SPAM"] <> '' ? "'".$arFields["IS_SPAM"]."'" : "null"),
190 "IS_HIDDEN" => ($arFields["IS_HIDDEN"]=="Y" ? "'Y'" : "'N'"),
191 "IS_LOG" => ($arFields["IS_LOG"]=="Y" ? "'Y'" : "'N'"),
192 "IS_OVERDUE" => ($arFields["IS_OVERDUE"]=="Y" ? "'Y'" : "'N'"),
193 "NOT_CHANGE_STATUS" => ($arFields["NOT_CHANGE_STATUS"]=="Y" ? "'Y'" : "'N'")
194 );
195
196 $notChangeStatus = (
197 is_set($arFields, "NOT_CHANGE_STATUS") && $arFields["NOT_CHANGE_STATUS"]=="Y"
198 ? "Y"
199 : "N"
200 );
201
202
203 $rows = $DB->Update("b_ticket_message",$arFields_u,"WHERE ID='".$MESSAGE_ID."'");
204 if (intval($rows)>0)
205 {
206 $rsMessage = CTicket::GetMessageByID($MESSAGE_ID, $checkRights);
207 if ($arMessage = $rsMessage->Fetch())
208 {
209 $ticketID = $arMessage["TICKET_ID"];
210
211 // обновим прикрепленные файлы
212 $not_image_extension_suffix = COption::GetOptionString("support", "NOT_IMAGE_EXTENSION_SUFFIX");
213 $not_image_upload_dir = COption::GetOptionString("support", "NOT_IMAGE_UPLOAD_DIR");
214 $max_size = COption::GetOptionString("support", "SUPPORT_MAX_FILESIZE");
215
216 $arrFiles = $arFields["FILES"];
217 if (is_array($arrFiles) && count($arrFiles)>0)
218 {
219 foreach ($arrFiles as $arFile)
220 {
221 if ($arFile["name"] <> '' || $arFile["del"]=="Y")
222 {
223 if ($bSupportTeam!="Y" && $bAdmin!="Y") $max_file_size = intval($max_size)*1024;
224 $fes = "";
225 $upload_dir = "support";
226 if (!CFile::IsImage($arFile["name"], $arFile["type"]))
227 {
228 $fes = $not_image_extension_suffix;
229 $arFile["name"] .= $fes;
230 $upload_dir = $not_image_upload_dir;
231 }
232
233 if (!array_key_exists("MODULE_ID", $arFile) || $arFile["MODULE_ID"] == '')
234 $arFile["MODULE_ID"] = "support";
235
236 $fid = intval(CFile::SaveFile($arFile, $upload_dir, $max_file_size));
237
238 // если стоял флаг "Удалить" то
239 if ($arFile["del"]=="Y")
240 {
241 // удалим связку
242 $strSql = "
243 DELETE FROM
244 b_ticket_message_2_file
245 WHERE
246 FILE_ID=".intval($arFile["old_file"])."
247 ";
248 $DB->Query($strSql);
249 }
250
251 // если успешно загрузили файл то
252 if ($fid>0)
253 {
254 // если это был новый файл то
255 if (intval($arFile["old_file"])<=0)
256 {
257 // добавим связку
258 $md5 = md5(uniqid(mt_rand(), true).time());
259 $arFields_fi = array(
260 "HASH" => "'".$DB->ForSql($md5, 255)."'",
261 "MESSAGE_ID" => $MESSAGE_ID,
262 "FILE_ID" => $fid,
263 "TICKET_ID" => $ticketID,
264 "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null"
265 );
266 $DB->Insert("b_ticket_message_2_file",$arFields_fi);
267 }
268 else // иначе
269 {
270 // обновим связку
271 $arFields_fu = array(
272 "FILE_ID" => $fid,
273 "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null"
274 );
275 $DB->Update("b_ticket_message_2_file", $arFields_fu, "WHERE FILE_ID = ".intval($arFile["old_file"]));
276 }
277 }
278 }
279 }
280 }
281 if ($arFields["IS_SPAM"]=="Y")
282 CTicket::MarkMessageAsSpam($MESSAGE_ID,"Y",$checkRights);
283 elseif ($arFields["IS_SPAM"]=="N")
284 CTicket::MarkMessageAsSpam($MESSAGE_ID,"N",$checkRights);
285 elseif ($arFields["IS_SPAM"]!="Y" && $arFields["IS_SPAM"]!="N")
286 CTicket::UnMarkMessageAsSpam($MESSAGE_ID,$checkRights);
287
288 //if ($notChangeStatus != "Y")
289 //CTicket::UpdateLastParams($ticketID);
290 //if ($notChangeStatus!="Y" && $hidden!="Y" && $log!="Y")
291 //{
292 //CTicketReminder::Update($ticketID);
293 //}
294
295 CSupportSearch::reindexTicket($ticketID);
296 }
297 }
298 }
299 }
300
301 public static function AddMessage($ticketID, $arFields, &$arrFILES, $checkRights="Y")
302 {
303 if ($arFields["MESSAGE"] <> '' || (is_array($arFields["FILES"]) && count($arFields["FILES"])>0))
304 {
305 global $DB, $USER;
306
307 $bAdmin = "N";
308 $bSupportTeam = "N";
309 $bSupportClient = "N";
310 if ($checkRights=="Y")
311 {
312 $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N";
313 $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N";
314 $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N";
315 $uid = intval($USER->GetID());
316 }
317 else
318 {
319 $bAdmin = "Y";
320 $bSupportTeam = "Y";
321 $bSupportClient = "Y";
322 //if (is_object($USER)) $uid = intval($USER->GetID()); else $uid = -1;
323 $uid = 0;
324 }
325 if ($bAdmin!="Y" && $bSupportTeam!="Y" && $bSupportClient!="Y")
326 {
327 return false;
328 }
329
330 $ticketID = intval($ticketID);
331 if ($ticketID<=0)
332 {
333 return 0;
334 }
335
336 $strSql = "SELECT RESPONSIBLE_USER_ID, LAST_MESSAGE_USER_ID, REOPEN, SITE_ID, TITLE FROM b_ticket WHERE ID='$ticketID'";
337 $rsTicket = $DB->Query($strSql);
338 $arTicket = $rsTicket->Fetch();
339 $currentResponsibleUserID = $arTicket["RESPONSIBLE_USER_ID"];
340 $siteID = $arTicket["SITE_ID"];
341 $tTitle = $arTicket["TITLE"];
342
343 $strSql = "SELECT max(C_NUMBER) MAX_NUMBER FROM b_ticket_message WHERE TICKET_ID='$ticketID'";
344 $z = $DB->Query($strSql);
345 $zr = $z->Fetch();
346 $maxNumber = intval($zr['MAX_NUMBER']);
347
348 if ((trim($arFields["MESSAGE_AUTHOR_SID"]) <> '' || intval($arFields["MESSAGE_AUTHOR_USER_ID"])>0 || intval($arFields["MESSAGE_CREATED_USER_ID"])>0) && ($bSupportTeam=="Y" || $bAdmin=="Y"))
349 {
350 $ownerUserID = intval($arFields["MESSAGE_AUTHOR_USER_ID"]);
351 $ownerSid = "'".$DB->ForSql($arFields["MESSAGE_AUTHOR_SID"],2000)."'";
352 $ownerGuestID = intval($arFields["MESSAGE_AUTHOR_GUEST_ID"])>0 ? intval($arFields["MESSAGE_AUTHOR_GUEST_ID"]) : "null";
353
354 $createdUserID = intval($arFields["MESSAGE_CREATED_USER_ID"])>0 ? intval($arFields["MESSAGE_CREATED_USER_ID"]) : intval($uid);
355 $createdGuestID = intval($arFields["MESSAGE_CREATED_GUEST_ID"])>0 ? intval($arFields["MESSAGE_CREATED_GUEST_ID"]) : intval($_SESSION["SESS_GUEST_ID"]);
356 }
357 else
358 {
359 $ownerUserID = intval($uid);
360 $ownerSid = "null";
361 $ownerGuestID = intval($_SESSION["SESS_GUEST_ID"]);
362
363 $createdUserID = intval($uid);
364 $createdGuestID = intval($_SESSION["SESS_GUEST_ID"]);
365 }
366
367 if (intval($ownerGuestID)<=0)
368 {
369 $ownerGuestID = "null";
370 }
371
372 $MessageBySupportTeam = "null";
373 if ($ownerUserID<=0)
374 {
375 $ownerUserID = "null";
376 }
377 else
378 {
379 $MessageBySupportTeam = "'N'";
380 if (CTicket::IsSupportTeam($ownerUserID) || CTicket::IsAdmin($ownerUserID))
381 {
382 $MessageBySupportTeam = "'Y'";
383 }
384 }
385
386 if ($createdUserID<=0)
387 {
388 $createdUserID = "null";
389 }
390 if (intval($createdGuestID)<=0)
391 {
392 $createdGuestID = "null";
393 }
394
395 $createdModuleName = ($arFields["MESSAGE_CREATED_MODULE_NAME"] <> '') ? "'".$DB->ForSql($arFields["MESSAGE_CREATED_MODULE_NAME"],255)."'" : "'support'";
396
397 $externalID = intval($arFields["EXTERNAL_ID"])>0 ? intval($arFields["EXTERNAL_ID"]) : "null";
398 $externalField1 = $arFields["EXTERNAL_FIELD_1"];
399
400 if (is_set($arFields, "HIDDEN"))
401 {
402 $hidden = ($arFields["HIDDEN"]=="Y") ? "Y" : "N";
403 }
404 elseif (is_set($arFields, "IS_HIDDEN"))
405 {
406 $hidden = ($arFields["IS_HIDDEN"]=="Y") ? "Y" : "N";
407 }
408 $hidden = ($hidden=="Y") ? "Y" : "N";
409
410 $notChangeStatus = (
411 is_set($arFields, "NOT_CHANGE_STATUS") && $arFields["NOT_CHANGE_STATUS"]=="Y"
412 ? "Y"
413 : "N"
414 );
415
416 $changeLastMessageDate = true;
417 if ($arTicket["LAST_MESSAGE_USER_ID"] == $uid && $arTicket["REOPEN"] != "Y")
418 {
419 $changeLastMessageDate = false;
420 }
421
422 $TASK_TIME = intval($arFields["TASK_TIME"])>0 ? intval($arFields["TASK_TIME"]) : "null";
423
424 if (is_set($arFields, "LOG"))
425 {
426 $log = ($arFields["LOG"]=="Y") ? "Y" : "N";
427 }
428 elseif (is_set($arFields, "IS_LOG"))
429 {
430 $log = ($arFields["IS_LOG"]=="Y") ? "Y" : "N";
431 }
432 $log = ($log=="Y") ? "Y" : "N";
433
434 if (is_set($arFields, "OVERDUE"))
435 {
436 $overdue = ($arFields["OVERDUE"]=="Y") ? "Y" : "N";
437 }
438 elseif (is_set($arFields, "IS_OVERDUE"))
439 {
440 $overdue = ($arFields["IS_OVERDUE"]=="Y") ? "Y" : "N";
441 }
442 $overdue = ($overdue=="Y") ? "Y" : "N";
443
444 $arFieldsI = array(
445 "TIMESTAMP_X" => $DB->GetNowFunction(),
446 "DAY_CREATE" => $DB->CurrentDateFunction(),
447 "C_NUMBER" => $maxNumber + 1,
448 "TICKET_ID" => $ticketID,
449 "IS_HIDDEN" => "'".$hidden."'",
450 "IS_LOG" => "'".$log."'",
451 "IS_OVERDUE" => "'".$overdue."'",
452 "MESSAGE" => "'".$DB->ForSql($arFields["MESSAGE"])."'",
453 "MESSAGE_SEARCH" => "'".$DB->ForSql(mb_strtoupper($arFields["MESSAGE"]))."'",
454 "EXTERNAL_ID" => $externalID,
455 "EXTERNAL_FIELD_1" => ($externalField1 <> '' ? "'".$DB->ForSql($externalField1)."'" : "null"),
456 "OWNER_USER_ID" => $ownerUserID,
457 "OWNER_GUEST_ID" => $ownerGuestID,
458 "OWNER_SID" => $ownerSid,
459 "SOURCE_ID" => intval($arFields["MESSAGE_SOURCE_ID"]),
460 "CREATED_USER_ID" => $createdUserID,
461 "CREATED_GUEST_ID" => $createdGuestID,
462 "CREATED_MODULE_NAME" => $createdModuleName,
463 "MODIFIED_USER_ID" => $createdUserID,
464 "MODIFIED_GUEST_ID" => $createdGuestID,
465 "MESSAGE_BY_SUPPORT_TEAM" => $MessageBySupportTeam,
466 "TASK_TIME" => $TASK_TIME,
467 "NOT_CHANGE_STATUS" => "'".$notChangeStatus."'"
468 );
469
470 CTimeZone::Disable();
471 $arFieldsI["DATE_CREATE"] = $DB->CharToDateFunction( GetTime( time() ,"FULL" ) );
472 CTimeZone::Enable();
473
474 /*if ($hidden!="Y" && $log!="Y" && $changeLastMessageDate == false)
475 {
476 if ($MessageBySupportTeam == "'Y'" || ($maxNumber <= 0 && array_key_exists('SOURCE_SID', $arFields) && $arFields['SOURCE_SID'] === 'email'))
477 {
478 $arFieldsI["NOT_CHANGE_STATUS"] = "'N'";
479 }
480 else
481 {
482 $arFieldsI["NOT_CHANGE_STATUS"] = "'Y'";
483 }
484 }*/
485
486 if (intval($currentResponsibleUserID)>0) $arFieldsI["CURRENT_RESPONSIBLE_USER_ID"] = $currentResponsibleUserID;
487
488
489
490 $mid = $DB->Insert("b_ticket_message",$arFieldsI);
491 if (intval($mid)>0)
492 {
493 $not_image_extension_suffix = COption::GetOptionString("support", "NOT_IMAGE_EXTENSION_SUFFIX");
494 $not_image_upload_dir = COption::GetOptionString("support", "NOT_IMAGE_UPLOAD_DIR");
495 $max_size = COption::GetOptionString("support", "SUPPORT_MAX_FILESIZE");
496 // сохраняем приаттаченные файлы
497 $arFILES = $arFields["FILES"];
498 if (is_array($arFILES) && count($arFILES)>0)
499 {
500 foreach ($arFILES as $arFILE)
501 {
502 if ($arFILE["name"] <> '')
503 {
504 if ($bSupportTeam!="Y" && $bAdmin!="Y")
505 {
506 $max_file_size = intval($max_size) * 1024;
507 }
508 $fes = "";
509 $upload_dir = "support";
510 if (!CFile::IsImage($arFILE["name"], $arFILE["type"]))
511 {
512 $fes = $not_image_extension_suffix;
513 $arFILE["name"] .= $fes;
514 $upload_dir = $not_image_upload_dir;
515 }
516
517 if (!array_key_exists("MODULE_ID", $arFILE) || $arFILE["MODULE_ID"] == '')
518 {
519 $arFILE["MODULE_ID"] = "support";
520 }
521
522 $fid = intval(CFile::SaveFile($arFILE, $upload_dir, $max_file_size));
523 if ($fid>0)
524 {
525 $md5 = md5(uniqid(mt_rand(), true).time());
526 $arFILE["HASH"] = $md5;
527 $arFILE["FILE_ID"] = $fid;
528 $arFILE["MESSAGE_ID"] = $mid;
529 $arFILE["TICKET_ID"] = $ticketID;
530 $arFILE["EXTENSION_SUFFIX"] = $fes;
531 $arFields_fi = array(
532 "HASH" => "'".$DB->ForSql($md5, 255)."'",
533 "MESSAGE_ID" => $mid,
534 "FILE_ID" => $fid,
535 "TICKET_ID" => $ticketID,
536 "EXTENSION_SUFFIX" => ($fes <> '') ? "'".$DB->ForSql($fes, 255)."'" : "null"
537 );
538 $link_id = $DB->Insert("b_ticket_message_2_file",$arFields_fi);
539 if (intval($link_id)>0)
540 {
541 $arFILE["LINK_ID"] = $link_id;
542 $arrFILES[] = $arFILE;
543 }
544 }
545 }
546 }
547 }
548
549 /*
550 // если это не было скрытым сообщением или сообщение лога, то
551 if ($notChangeStatus!="Y" && $hidden!="Y" && $log!="Y")
552 {
553 // обновим ряд параметров обращения
554 if (!isset($arFields["AUTO_CLOSE_DAYS"])) $RESET_AUTO_CLOSE = "Y";
555
556 CTicket::UpdateLastParams($ticketID, $RESET_AUTO_CLOSE, $changeLastMessageDate, true);
557
558 // при необходимости создадим или удалим агенты-напоминальщики
559 //CTicketReminder::Update($ticketID);
560 }*/
561
562 if ( $log!="Y" && CSupportSearch::isIndexExists())
563 {
564 CSupportSearch::reindexTicket($ticketID);
565 }
566
567 //если была установлена галочка "не изменять статус обращени" - пересчитаем количество собщений
568 if ($notChangeStatus == "Y" || $hidden == "Y")
569 CTicket::UpdateMessages($ticketID);
570 }
571 }
572 return $mid;
573 }
574
575 public static function GetStatus($ticketID)
576 {
577 global $DB, $USER;
578
579 $ticketID = intval($ticketID);
580 if ($ticketID<=0) return false;
581
582 $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N";
583 $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N";
584 $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N";
585 $bDemo = (CTicket::IsDemo()) ? "Y" : "N";
586 $uid = intval($USER->GetID());
587
588 if ($bSupportTeam=="Y" || $bAdmin=="Y" || $bDemo=="Y")
589 {
590 $lamp = "
591 if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',
592 if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green',
593 if(ifnull(T.OWNER_USER_ID,0)='$uid', 'red',
594 if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s',
595 if(ifnull(T.RESPONSIBLE_USER_ID,0)='$uid', 'red',
596 'yellow')))))
597 ";
598 }
599 else
600 {
601 $lamp = "
602 if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',
603 if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green', 'red'))
604 ";
605 }
606
607 $strSql = "
608 SELECT
609 $lamp LAMP
610 FROM
611 b_ticket T
612 WHERE
613 ID = $ticketID
614 ";
615 $rs = $DB->Query($strSql);
616 if ($ar = $rs->Fetch()) return $ar["LAMP"];
617
618 return false;
619 }
620
621 public static function GetList($by = 's_default', $order = 'desc', $arFilter = [], $isFiltered = null, $checkRights = "Y", $getUserName = "Y", $getExtraNames = "Y", $siteID = false, $arParams = [])
622 {
623 global $DB, $USER, $USER_FIELD_MANAGER;
624
626 $d_join = "";
627
628 $bAdmin = 'N';
629 $bSupportTeam = 'N';
630 $bSupportClient = 'N';
631 $bDemo = 'N';
632
634 $messJoin = "";
635
637 $searchJoin = '';
638
639 $need_group = false;
640
641 $arSqlHaving = array();
642
643 if ($checkRights=='Y')
644 {
645 $bAdmin = (CTicket::IsAdmin()) ? 'Y' : 'N';
646 $bSupportTeam = (CTicket::IsSupportTeam()) ? 'Y' : 'N';
647 $bSupportClient = (CTicket::IsSupportClient()) ? 'Y' : 'N';
648 $bDemo = (CTicket::IsDemo()) ? 'Y' : 'N';
649 $uid = intval($USER->GetID());
650 }
651 else
652 {
653 $bAdmin = 'Y';
654 $bSupportTeam = 'Y';
655 $bSupportClient = 'Y';
656 $bDemo = 'Y';
657 if (is_object($USER)) $uid = intval($USER->GetID()); else $uid = -1;
658 }
659 if ($bAdmin!='Y' && $bSupportTeam!='Y' && $bSupportClient!='Y' && $bDemo!='Y') return false;
660
661 if ($bSupportTeam=='Y' || $bAdmin=='Y' || $bDemo=='Y')
662 {
663 $lamp = "
664 if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',
665 if(ifnull(T.LAST_MESSAGE_USER_ID,0)='$uid', 'green',
666 if(ifnull(T.OWNER_USER_ID,0)='$uid', 'red',
667 if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y','green_s',
668 if(ifnull(T.RESPONSIBLE_USER_ID,0)='$uid', 'red',
669 'yellow')))))
670 ";
671 }
672 else
673 {
674 $lamp = "
675 if(ifnull(T.DATE_CLOSE,'x')<>'x', 'grey',
676 if(T.LAST_MESSAGE_BY_SUPPORT_TEAM='Y', 'red', 'green'))
677 ";
678 }
679 $bJoinSupportTeamTbl = $bJoinClientTbl = false;
680
681 $arSqlSearch = Array();
682 $strSqlSearch = "";
683
684 if (is_array($arFilter))
685 {
686 $filterKeys = array_keys($arFilter);
687 $filterKeysCount = count($filterKeys);
688 for ($i=0; $i<$filterKeysCount; $i++)
689 {
690 $key = $filterKeys[$i];
691 $val = $arFilter[$filterKeys[$i]];
692 if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF')))
693 continue;
694 $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false;
695 $key = strtoupper($key);
696 switch($key)
697 {
698 case "ID":
699 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
700 $arSqlSearch[] = GetFilterQuery("T.ID",$val,$match);
701 break;
702 case "HOLD_ON":
703 $arSqlSearch[] = ($val=="Y") ? "T.HOLD_ON='Y'" : "T.HOLD_ON = 'N'";
704 break;
705
706 case "LID":
707 case "SITE":
708 case "SITE_ID":
709 if (is_array($val)) $val = implode(" | ", $val);
710 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
711 $arSqlSearch[] = GetFilterQuery("T.SITE_ID",$val,$match);
712 break;
713 case "LAMP":
714 if (is_array($val))
715 {
716 if (count($val)>0)
717 {
718 $str = "";
719 foreach ($val as $value)
720 {
721 $str .= ", '".$DB->ForSQL($value)."'";
722 }
723 $str = trim($str, ", ");
724 $arSqlSearch[] = " ".$lamp." in (".$str.")";
725 }
726 }
727 elseif ($val <> '')
728 {
729 $arSqlSearch[] = " ".$lamp." = '".$DB->ForSQL($val)."'";
730 }
731 break;
732 case "DATE_CREATE_1":
733 if (CheckDateTime($val))
734 $arSqlSearch[] = "T.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT");
735 break;
736 case "DATE_CREATE_2":
737 if (CheckDateTime($val))
738 $arSqlSearch[] = "T.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
739 break;
740 case "DATE_TIMESTAMP_1":
741 if (CheckDateTime($val))
742 $arSqlSearch[] = "T.TIMESTAMP_X>=".$DB->CharToDateFunction($val, "SHORT");
743 break;
744 case "DATE_TIMESTAMP_2":
745 if (CheckDateTime($val))
746 $arSqlSearch[] = "T.TIMESTAMP_X<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
747 break;
748 case "DATE_CLOSE_1":
749 if (CheckDateTime($val))
750 $arSqlSearch[] = "T.DATE_CLOSE>=".$DB->CharToDateFunction($val, "SHORT");
751 break;
752 case "DATE_CLOSE_2":
753 if (CheckDateTime($val))
754 $arSqlSearch[] = "T.DATE_CLOSE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
755 break;
756 case "CLOSE":
757 $arSqlSearch[] = ($val=="Y") ? "T.DATE_CLOSE is not null" : "T.DATE_CLOSE is null";
758 break;
759 case "AUTO_CLOSE_DAYS1":
760 $arSqlSearch[] = "T.AUTO_CLOSE_DAYS>='".intval($val)."'";
761 break;
762 case "AUTO_CLOSE_DAYS2":
763 $arSqlSearch[] = "T.AUTO_CLOSE_DAYS<='".intval($val)."'";
764 break;
765 case "TICKET_TIME_1":
766 $arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)>='".(intval($val)*86400)."'";
767 break;
768 case "TICKET_TIME_2":
769 $arSqlSearch[] = "UNIX_TIMESTAMP(T.DATE_CLOSE) - UNIX_TIMESTAMP(T.DATE_CREATE)<='".(intval($val)*86400)."'";
770 break;
771 case "TITLE":
772 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
773 $arSqlSearch[] = GetFilterQuery("T.TITLE", $val, $match);
774 break;
775 case "MESSAGES1":
776 $arSqlSearch[] = "T.MESSAGES>='".intval($val)."'";
777 break;
778 case "MESSAGES2":
779 $arSqlSearch[] = "T.MESSAGES<='".intval($val)."'";
780 break;
781
782 case "PROBLEM_TIME1":
783 $arSqlSearch[] = "T.PROBLEM_TIME>='".intval($val)."'";
784 break;
785 case "PROBLEM_TIME2":
786 $arSqlSearch[] = "T.PROBLEM_TIME<='".intval($val)."'";
787 break;
788
789 case "OVERDUE_MESSAGES1":
790 $arSqlSearch[] = "T.OVERDUE_MESSAGES>='".intval($val)."'";
791 break;
792 case "OVERDUE_MESSAGES2":
793 $arSqlSearch[] = "T.OVERDUE_MESSAGES<='".intval($val)."'";
794 break;
795 case "AUTO_CLOSE_DAYS_LEFT1":
796 $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
797 TO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)) - TO_DAYS(now()) ELSE -1 END >='".intval($val)."'";
798 break;
799 case "AUTO_CLOSE_DAYS_LEFT2":
800 $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
801 TO_DAYS(ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY))-TO_DAYS(now()) ELSE 999 END <='".intval($val)."'";
802 break;
803 case "OWNER":
804 $getUserName = "Y";
805 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
806 $arSqlSearch[] = GetFilterQuery("UO.ID, UO.LOGIN, UO.LAST_NAME, UO.NAME, T.OWNER_SID", $val, $match, array("@", ".")); //T.OWNER_USER_ID,
807 break;
808 case "OWNER_USER_ID":
809 case "OWNER_SID":
810 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
811 $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match);
812 break;
813 case "SLA_ID":
814 case "SLA":
815 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
816 $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match);
817 break;
818 case "CREATED_BY":
819 $getUserName = "Y";
820 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
821 $arSqlSearch[] = GetFilterQuery("T.CREATED_USER_ID, UC.LOGIN, UC.LAST_NAME, UC.NAME, T.CREATED_MODULE_NAME", $val, $match);
822 break;
823 case "RESPONSIBLE":
824 $getUserName = "Y";
825 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
826 $arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match);
827 break;
828 case "RESPONSIBLE_ID":
829 if (intval($val)>0) $arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '".intval($val)."'";
830 elseif ($val==0) $arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)";
831 break;
832 case "CATEGORY_ID":
833 case "CATEGORY":
834 if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'";
835 elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)";
836 break;
837 case "CATEGORY_SID":
838 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
839 $arSqlSearch[] = GetFilterQuery("DC.SID", $val, $match);
840 $d_join = "
841 LEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')";
842 break;
843 case "CRITICALITY_ID":
844 case "CRITICALITY":
845 if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'";
846 elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)";
847 break;
848 case "CRITICALITY_SID":
849 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
850 $arSqlSearch[] = GetFilterQuery("DK.SID", $val, $match);
851 break;
852 case "STATUS_ID":
853 case "STATUS":
854 if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'";
855 elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)";
856 break;
857 case "STATUS_SID":
858 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
859 $arSqlSearch[] = GetFilterQuery("DS.SID", $val, $match);
860 break;
861 case "MARK_ID":
862 case "MARK":
863 if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'";
864 elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)";
865 break;
866 case "MARK_SID":
867 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
868 $arSqlSearch[] = GetFilterQuery("DM.SID", $val, $match);
869 break;
870 case "SOURCE_ID":
871 case "SOURCE":
872 if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'";
873 elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)";
874 break;
875 case "SOURCE_SID":
876 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
877 $arSqlSearch[] = GetFilterQuery("DSR.SID", $val, $match);
878 break;
879
880 case "DIFFICULTY_ID":
881 case "DIFFICULTY":
882 if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'";
883 elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)";
884 break;
885 case "DIFFICULTY_SID":
886 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
887 $arSqlSearch[] = GetFilterQuery("DD.SID", $val, $match);
888 break;
889
890
891
892 case "MODIFIED_BY":
893 $getUserName = "Y";
894 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
895 $arSqlSearch[] = GetFilterQuery("T.MODIFIED_USER_ID, T.MODIFIED_MODULE_NAME, UM.LOGIN, UM.LAST_NAME, UM.NAME", $val, $match);
896 break;
897 case "MESSAGE":
898 global $strError;
899 if( $val == '' ) break;
900
901 if(CSupportSearch::CheckModule() && CSupportSearch::isIndexExists())
902 {
903 // new indexed search
904 $searchSqlParams = CSupportSearch::getSql($val);
905 $searchOn = $searchSqlParams['WHERE'];
906 $searchHaving = $searchSqlParams['HAVING'];
907
908 if ($searchOn)
909 {
910 $searchJoin = 'INNER JOIN b_ticket_search TS ON TS.TICKET_ID = T.ID AND '.$searchOn;
911
912 if (!empty($searchHaving))
913 {
914 // 2 or more search words
915 $arSqlHaving[] = $searchHaving;
916 $need_group = true;
917 }
918 }
919
920 }
921 else
922 {
923 if ($bSupportTeam=="Y" || $bAdmin=="Y" || $bDemo=="Y")
924 {
925 $messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID)";
926 }
927 else
928 {
929 $messJoin = "INNER JOIN b_ticket_message M ON (M.TICKET_ID=T.ID and M.IS_HIDDEN='N' and M.IS_LOG='N')";
930 }
931
932 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
933 $f = new CFilterQuery("OR", "yes", $match, array(), "N", "Y", "N");
934 $query = $f->GetQueryString( "T.TITLE,M.MESSAGE_SEARCH", $val );
935 $error = $f->error;
936 if (trim($error) <> '')
937 {
938 $strError .= $error."<br>";
939 $query = "0";
940 }
941 else $arSqlSearch[] = $query;
942 }
943 break;
944 case "LAST_MESSAGE_USER_ID":
945 case "LAST_MESSAGE_SID":
946 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
947 $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match);
948 break;
949 case "LAST_MESSAGE_BY_SUPPORT_TEAM":
950 $arSqlSearch[] = "T.LAST_MESSAGE_BY_SUPPORT_TEAM= '".($val == 'Y' ? 'Y' : 'N')."'";
951 break;
952 case "SUPPORT_COMMENTS":
953 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
954 $arSqlSearch[] = GetFilterQuery("T.SUPPORT_COMMENTS", $val, $match);
955 break;
956 case "IS_SPAM":
957 $arSqlSearch[] = ($val=="Y") ? "T.IS_SPAM ='Y'" : "(T.IS_SPAM = 'N' or T.IS_SPAM is null)";
958 break;
959 case "IS_OVERDUE":
960 $arSqlSearch[] = ($val=="Y") ? "T.IS_OVERDUE ='Y'" : "(T.IS_OVERDUE = 'N' or T.IS_OVERDUE is null)";
961 break;
962 case "IS_SPAM_MAYBE":
963 $arSqlSearch[] = ($val=="Y") ? "T.IS_SPAM='N'" : "(T.IS_SPAM='Y' or T.IS_SPAM is null)";
964 break;
965
966 case 'SUPPORTTEAM_GROUP_ID':
967 case 'CLIENT_GROUP_ID':
968 if ($key == 'SUPPORTTEAM_GROUP_ID')
969 {
970 $table = 'UGS';
971 $bJoinSupportTeamTbl = true;
972 }
973 else
974 {
975 $table = 'UGC';
976 $bJoinClientTbl = true;
977 }
978 if (is_array($val))
979 {
980 $val = array_map('intval', $val);
981 $val = array_unique($val);
982 $val = array_filter($val);
983 if (count($val) > 0)
984 {
985 $arSqlSearch[] = '('.$table.'.GROUP_ID IS NOT NULL AND '.$table.'.GROUP_ID IN ('.implode(',', $val).'))';
986 }
987 }
988 else
989 {
990 $val = intval($val);
991 if ($val > 0)
992 {
993 $arSqlSearch[] = '('.$table.'.GROUP_ID IS NOT NULL AND '.$table.'.GROUP_ID=\''.$val.'\')';
994 }
995 }
996 break;
997 case 'COUPON':
998 $match = ($matchValueSet && $arFilter[$key."_EXACT_MATCH"]!="Y") ? "Y" : "N";
999 $arSqlSearch[] = GetFilterQuery("T.".$key, $val, $match);
1000 break;
1001 }
1002 }
1003 }
1004
1005 $obUserFieldsSql = new CUserTypeSQL;
1006 $obUserFieldsSql->SetEntity("SUPPORT", "T.ID");
1007 $obUserFieldsSql->SetSelect( $arParams["SELECT"] );
1008 $obUserFieldsSql->SetFilter( $arFilter );
1009 $obUserFieldsSql->SetOrder( array( $by => $order) );
1010
1011 if ($by == "s_id")
1012 {
1013 $strSqlOrder = "ORDER BY T.ID";
1014 }
1015 elseif ($by == "s_last_message_date")
1016 {
1017 $strSqlOrder = "ORDER BY T.LAST_MESSAGE_DATE";
1018 }
1019 elseif ($by == "s_site_id" || $by == "s_lid")
1020 {
1021 $strSqlOrder = "ORDER BY T.SITE_ID";
1022 }
1023 elseif ($by == "s_lamp")
1024 {
1025 $strSqlOrder = "ORDER BY LAMP";
1026 }
1027 elseif ($by == "s_is_overdue")
1028 {
1029 $strSqlOrder = "ORDER BY T.IS_OVERDUE";
1030 }
1031 elseif ($by == "s_is_notified")
1032 {
1033 $strSqlOrder = "ORDER BY T.IS_NOTIFIED";
1034 }
1035 elseif ($by == "s_date_create")
1036 {
1037 $strSqlOrder = "ORDER BY T.DATE_CREATE";
1038 }
1039 elseif ($by == "s_timestamp" || $by == "s_timestamp_x")
1040 {
1041 $strSqlOrder = "ORDER BY T.TIMESTAMP_X";
1042 }
1043 elseif ($by == "s_date_close")
1044 {
1045 $strSqlOrder = "ORDER BY T.DATE_CLOSE";
1046 }
1047 elseif ($by == "s_owner")
1048 {
1049 $strSqlOrder = "ORDER BY T.OWNER_USER_ID";
1050 }
1051 elseif ($by == "s_modified_by")
1052 {
1053 $strSqlOrder = "ORDER BY T.MODIFIED_USER_ID";
1054 }
1055 elseif ($by == "s_title")
1056 {
1057 $strSqlOrder = "ORDER BY T.TITLE ";
1058 }
1059 elseif ($by == "s_responsible")
1060 {
1061 $strSqlOrder = "ORDER BY T.RESPONSIBLE_USER_ID";
1062 }
1063 elseif ($by == "s_messages")
1064 {
1065 $strSqlOrder = "ORDER BY T.MESSAGES";
1066 }
1067 elseif ($by == "s_category")
1068 {
1069 $strSqlOrder = "ORDER BY T.CATEGORY_ID";
1070 }
1071 elseif ($by == "s_criticality")
1072 {
1073 $strSqlOrder = "ORDER BY T.CRITICALITY_ID";
1074 }
1075 elseif ($by == "s_sla")
1076 {
1077 $strSqlOrder = "ORDER BY T.SLA_ID";
1078 }
1079 elseif ($by == "s_status")
1080 {
1081 $strSqlOrder = "ORDER BY T.STATUS_ID";
1082 }
1083 elseif ($by == "s_difficulty")
1084 {
1085 $strSqlOrder = "ORDER BY T.DIFFICULTY_ID";
1086 }
1087 elseif ($by == "s_problem_time")
1088 {
1089 $strSqlOrder = "ORDER BY T.PROBLEM_TIME";
1090 }
1091 elseif ($by == "s_mark")
1092 {
1093 $strSqlOrder = "ORDER BY T.MARK_ID";
1094 }
1095 elseif ($by == "s_online")
1096 {
1097 $strSqlOrder = "ORDER BY USERS_ONLINE";
1098 }
1099 elseif ($by == "s_support_comments")
1100 {
1101 $strSqlOrder = "ORDER BY T.SUPPORT_COMMENTS";
1102 }
1103 elseif ($by == "s_auto_close_days_left")
1104 {
1105 $strSqlOrder = "ORDER BY AUTO_CLOSE_DAYS_LEFT";
1106 }
1107 elseif ($by == 's_coupon')
1108 {
1109 $strSqlOrder = 'ORDER BY T.COUPON';
1110 }
1111 elseif ($by == 's_deadline')
1112 {
1113 $strSqlOrder = 'ORDER BY T.SUPPORT_DEADLINE';
1114 }
1115 elseif( $s = $obUserFieldsSql->GetOrder($by) )
1116 {
1117 $strSqlOrder = "ORDER BY ".strtoupper($s);
1118 }
1119 else
1120 {
1121 $strSqlOrder = "ORDER BY IS_SUPER_TICKET DESC, T.IS_OVERDUE DESC, T.IS_NOTIFIED DESC, T.LAST_MESSAGE_DATE";
1122 }
1123
1124 if ($order!="asc")
1125 {
1126 $strSqlOrder .= " desc ";
1127 }
1128
1129 $arSqlSearch[] = $obUserFieldsSql->GetFilter();
1130
1131 if ($getUserName=="Y")
1132 {
1133 $u_select = "
1134 ,
1135 UO.LOGIN OWNER_LOGIN,
1136 UO.EMAIL OWNER_EMAIL,
1137 concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) OWNER_NAME,
1138 UR.LOGIN RESPONSIBLE_LOGIN,
1139 UR.EMAIL RESPONSIBLE_EMAIL,
1140 concat(ifnull(UR.NAME,''),' ',ifnull(UR.LAST_NAME,'')) RESPONSIBLE_NAME,
1141 UM.LOGIN MODIFIED_BY_LOGIN,
1142 UM.EMAIL MODIFIED_BY_EMAIL,
1143 concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_BY_NAME,
1144 UM.LOGIN MODIFIED_LOGIN,
1145 UM.EMAIL MODIFIED_EMAIL,
1146 concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_NAME,
1147 UL.LOGIN LAST_MESSAGE_LOGIN,
1148 UL.EMAIL LAST_MESSAGE_EMAIL,
1149 concat(ifnull(UL.NAME,''),' ',ifnull(UL.LAST_NAME,'')) LAST_MESSAGE_NAME,
1150 UC.LOGIN CREATED_LOGIN,
1151 UC.EMAIL CREATED_EMAIL,
1152 concat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,'')) CREATED_NAME
1153 ";
1154 $u_join = "
1155 LEFT JOIN b_user UO ON (UO.ID = T.OWNER_USER_ID)
1156 LEFT JOIN b_user UR ON (UR.ID = T.RESPONSIBLE_USER_ID)
1157 LEFT JOIN b_user UM ON (UM.ID = T.MODIFIED_USER_ID)
1158 LEFT JOIN b_user UL ON (UL.ID = T.LAST_MESSAGE_USER_ID)
1159 LEFT JOIN b_user UC ON (UC.ID = T.CREATED_USER_ID)
1160 ";
1161 }
1162 if ($getExtraNames=="Y")
1163 {
1164 $d_select = "
1165 ,
1166 DC.NAME CATEGORY_NAME,
1167 DC.DESCR CATEGORY_DESC,
1168 DC.SID CATEGORY_SID,
1169 DK.NAME CRITICALITY_NAME,
1170 DK.DESCR CRITICALITY_DESC,
1171 DK.SID CRITICALITY_SID,
1172 DS.NAME STATUS_NAME,
1173 DS.DESCR STATUS_DESC,
1174 DS.SID STATUS_SID,
1175 DM.NAME MARK_NAME,
1176 DM.DESCR MARK_DESC,
1177 DM.SID MARK_SID,
1178 DSR.NAME SOURCE_NAME,
1179 DSR.DESCR SOURCE_DESC,
1180 DSR.SID SOURCE_SID,
1181 DD.NAME DIFFICULTY_NAME,
1182 DD.DESCR DIFFICULTY_DESC,
1183 DD.SID DIFFICULTY_SID,
1184 SLA.NAME SLA_NAME
1185 ";
1186 $d_join = "
1187 LEFT JOIN b_ticket_dictionary DC ON (DC.ID = T.CATEGORY_ID and DC.C_TYPE = 'C')
1188 LEFT JOIN b_ticket_dictionary DK ON (DK.ID = T.CRITICALITY_ID and DK.C_TYPE = 'K')
1189 LEFT JOIN b_ticket_dictionary DS ON (DS.ID = T.STATUS_ID and DS.C_TYPE = 'S')
1190 LEFT JOIN b_ticket_dictionary DM ON (DM.ID = T.MARK_ID and DM.C_TYPE = 'M')
1191 LEFT JOIN b_ticket_dictionary DSR ON (DSR.ID = T.SOURCE_ID and DSR.C_TYPE = 'SR')
1192 LEFT JOIN b_ticket_dictionary DD ON (DD.ID = T.DIFFICULTY_ID and DD.C_TYPE = 'D')
1193 LEFT JOIN b_ticket_sla SLA ON (SLA.ID = T.SLA_ID)
1194 ";
1195 }
1196 if ($siteID <> '')
1197 {
1198 $dates_select = "
1199 ".$DB->DateToCharFunction("T.DATE_CREATE","FULL",$siteID,true)." DATE_CREATE,
1200 ".$DB->DateToCharFunction("T.TIMESTAMP_X","FULL",$siteID,true)." TIMESTAMP_X,
1201 ".$DB->DateToCharFunction("T.LAST_MESSAGE_DATE","FULL",$siteID,true)." LAST_MESSAGE_DATE,
1202 ".$DB->DateToCharFunction("T.DATE_CLOSE","FULL",$siteID,true)." DATE_CLOSE,
1203 ".$DB->DateToCharFunction("T.DATE_CREATE","SHORT",$siteID,true)." DATE_CREATE_SHORT,
1204 ".$DB->DateToCharFunction("T.TIMESTAMP_X","SHORT",$siteID,true)." TIMESTAMP_X_SHORT,
1205 ".$DB->DateToCharFunction("T.DATE_CLOSE","SHORT",$siteID,true)." DATE_CLOSE_SHORT,
1206 ".$DB->DateToCharFunction("T.SUPPORT_DEADLINE","FULL",$siteID,true)." SUPPORT_DEADLINE,
1207 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 "
1208 .$DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)","FULL",$siteID,true)
1209 ." ELSE NULL END AUTO_CLOSE_DATE
1210 ";
1211 }
1212 else
1213 {
1214 $dates_select = "
1215 ".$DB->DateToCharFunction("T.DATE_CREATE","FULL")." DATE_CREATE,
1216 ".$DB->DateToCharFunction("T.TIMESTAMP_X","FULL")." TIMESTAMP_X,
1217 ".$DB->DateToCharFunction("T.LAST_MESSAGE_DATE","FULL")." LAST_MESSAGE_DATE,
1218 ".$DB->DateToCharFunction("T.DATE_CLOSE","FULL")." DATE_CLOSE,
1219 ".$DB->DateToCharFunction("T.DATE_CREATE","SHORT")." DATE_CREATE_SHORT,
1220 ".$DB->DateToCharFunction("T.TIMESTAMP_X","SHORT")." TIMESTAMP_X_SHORT,
1221 ".$DB->DateToCharFunction("T.DATE_CLOSE","SHORT")." DATE_CLOSE_SHORT,
1222 ".$DB->DateToCharFunction("T.SUPPORT_DEADLINE","FULL")." SUPPORT_DEADLINE,
1223 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 "
1224 .$DB->DateToCharFunction("ADDDATE(T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY)","FULL")
1225 ." ELSE NULL END AUTO_CLOSE_DATE
1226 ";
1227 }
1228
1229 $ugroupJoin = '';
1230
1231 if ($bJoinSupportTeamTbl)
1232 {
1233 $ugroupJoin .= "
1234 LEFT JOIN b_ticket_user_ugroup UGS ON (UGS.USER_ID = T.RESPONSIBLE_USER_ID) ";
1235 $need_group = true;
1236 }
1237
1238 if ($bJoinClientTbl)
1239 {
1240 $ugroupJoin .= "
1241 LEFT JOIN b_ticket_user_ugroup UGC ON (UGC.USER_ID = T.OWNER_USER_ID) ";
1242 $need_group = true;
1243 }
1244
1245 // add permissions check
1246 if (!($bAdmin == 'Y' || $bDemo == 'Y'))
1247 {
1248 // a list of users who own or are responsible for tickets, which we can show to our current user
1249 $ticketUsers = array($uid);
1250
1251 // check if user has groups
1252 $result = $DB->Query('SELECT GROUP_ID FROM b_ticket_user_ugroup WHERE USER_ID = '.$uid.' AND CAN_VIEW_GROUP_MESSAGES = \'Y\'');
1253 if ($result)
1254 {
1255 // collect members of these groups
1256 $uGroups = array();
1257
1258 while ($row = $result->Fetch())
1259 {
1260 $uGroups[] = $row['GROUP_ID'];
1261 }
1262
1263 if (!empty($uGroups))
1264 {
1265 $result = $DB->Query('SELECT USER_ID FROM b_ticket_user_ugroup WHERE GROUP_ID IN ('.join(',', $uGroups).')');
1266 if ($result)
1267 {
1268 while ($row = $result->Fetch())
1269 {
1270 $ticketUsers[] = $row['USER_ID'];
1271 }
1272 }
1273 }
1274 }
1275
1276 // build sql
1277 $strSqlSearchUser = "";
1278
1279 if($bSupportTeam == 'Y')
1280 {
1281 $strSqlSearchUser = 'T.RESPONSIBLE_USER_ID IN ('.join(',', $ticketUsers).')';
1282 }
1283 elseif ($bSupportClient == 'Y')
1284 {
1285 $strSqlSearchUser = 'T.OWNER_USER_ID IN ('.join(',', $ticketUsers).')';
1286 }
1287
1288 $arSqlSearch[] = $strSqlSearchUser;
1289 }
1290
1291 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
1292 $onlineInterval = intval(COption::GetOptionString("support", "ONLINE_INTERVAL"));
1293
1294 $strSqlSelect = "
1295 SELECT
1296 T.*,
1297 T.SITE_ID,
1298 T.SITE_ID LID,
1299 $dates_select,
1300 UNIX_TIMESTAMP(T.DATE_CLOSE)-UNIX_TIMESTAMP(T.DATE_CREATE) TICKET_TIME,
1301 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
1302 TO_DAYS(
1303 ADDDATE(
1304 T.LAST_MESSAGE_DATE, INTERVAL T.AUTO_CLOSE_DAYS DAY
1305 )
1306 ) - TO_DAYS(now())
1307 ELSE -1 END AUTO_CLOSE_DAYS_LEFT,
1308 (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,
1309 if(T.COUPON IS NOT NULL, 1, 0) IS_SUPER_TICKET,
1310 $lamp LAMP
1311 $d_select
1312 $u_select
1313 " . $obUserFieldsSql->GetSelect();
1314
1315 $strSqlFrom = "
1316 FROM
1317 b_ticket T
1318 $u_join
1319 $d_join
1320 $messJoin
1321 $searchJoin
1322 $ugroupJoin
1323 " . $obUserFieldsSql->GetJoin("T.ID");
1324
1325 $strSqlWhere = "
1326 WHERE
1327 $strSqlSearch
1328 ";
1329
1330 $strSqlGroup = $need_group ? ' GROUP BY T.ID ' : '';
1331 $strSqlHaving = $arSqlHaving ? ' HAVING ' . join(' AND ', $arSqlHaving) . ' ' : '';
1332
1333 $strSql = $strSqlSelect . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving . $strSqlOrder;
1334
1335 if (is_array($arParams) && isset($arParams["NAV_PARAMS"]) && is_array($arParams["NAV_PARAMS"]))
1336 {
1337 $nTopCount = isset($arParams['NAV_PARAMS']['nTopCount']) ? intval($arParams['NAV_PARAMS']['nTopCount']) : 0;
1338
1339 if($nTopCount > 0)
1340 {
1341 $strSql = $DB->TopSql($strSql, $nTopCount);
1342 $res = $DB->Query($strSql);
1343 $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") );
1344 }
1345 else
1346 {
1347 $cntSql = "SELECT COUNT(T.ID) as C " . $strSqlFrom . $strSqlWhere . $strSqlGroup . $strSqlHaving;
1348
1349 if (!empty($strSqlGroup))
1350 {
1351 $cntSql = 'SELECT COUNT(1) AS C FROM ('.$cntSql.') tt';
1352 }
1353
1354 $res_cnt = $DB->Query($cntSql);
1355 $res_cnt = $res_cnt->Fetch();
1356 $res = new CDBResult();
1357 $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") );
1358 $res->NavQuery($strSql, $res_cnt["C"], $arParams["NAV_PARAMS"]);
1359 }
1360 }
1361 else
1362 {
1363 $res = $DB->Query($strSql);
1364 $res->SetUserFields( $USER_FIELD_MANAGER->GetUserFields("SUPPORT") );
1365 }
1366
1367 return $res;
1368 }
1369
1370 public static function GetSupportTeamList()
1371 {
1372 global $DB;
1373 $arrGid = CTicket::GetSupportTeamGroups();
1374 $arrAid = CTicket::GetAdminGroups();
1375 if (count($arrGid)>0)
1376 {
1377 $gid = implode(",",$arrGid);
1378 }
1379 else
1380 {
1381 $gid = 0;
1382 }
1383 if (count($arrAid)>0)
1384 {
1385 $aid = implode(",",$arrAid);
1386 }
1387 else
1388 {
1389 $aid = 0;
1390 }
1391 $strSql = "
1392 SELECT DISTINCT
1393 U.ID as REFERENCE_ID,
1394 concat('[',U.ID,'] ',' (',U.LOGIN,') ',ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) as REFERENCE,
1395 U.ACTIVE
1396 FROM
1397 b_user U,
1398 b_user_group G
1399 WHERE
1400 U.ID = G.USER_ID
1401 and G.GROUP_ID in ($gid, $aid)
1402 ORDER BY
1403 U.ID
1404 ";
1405 $res = $DB->Query($strSql);
1406 return $res;
1407 }
1408
1409 /*function GetResponsibleList($user_id)
1410 {
1411 global $DB;
1412
1413 $strSql = "
1414 SELECT DISTINCT
1415 U.ID as ID,
1416 U.LOGIN as LOGIN,
1417 concat('[',U.ID,'] ',' (',U.LOGIN,') ',ifnull(U.NAME,''),' ',ifnull(U.LAST_NAME,'')) as NAME,
1418 U.EMAIL as EMAIL
1419 FROM
1420 b_user U,
1421 b_ticket_user_ugroup TUG,
1422 b_ticket_user_ugroup TUG2
1423 WHERE
1424 TUG.USER_ID = '".intval($user_id)."'
1425 and TUG2.GROUP_ID = TUG.GROUP_ID
1426 and U.ID = TUG2.USER_ID
1427 and TUG2.CAN_MAIL_GROUP_MESSAGES = 'Y'
1428 ORDER BY
1429 U.ID
1430 ";
1431 $res = $DB->Query($strSql);
1432 return $res;
1433 }*/
1434
1435 public static function GetMessageList($by = 's_number', $order = 'asc', $arFilter = [], $isFiltered = null, $checkRights = "Y", $getUserName = "Y")
1436 {
1437 global $DB, $USER, $APPLICATION;
1438
1439 if ($checkRights=="Y")
1440 {
1441 $bAdmin = (CTicket::IsAdmin()) ? "Y" : "N";
1442 $bSupportTeam = (CTicket::IsSupportTeam()) ? "Y" : "N";
1443 $bSupportClient = (CTicket::IsSupportClient()) ? "Y" : "N";
1444 $bDemo = (CTicket::IsDemo()) ? "Y" : "N";
1445 }
1446 else
1447 {
1448 $bAdmin = "Y";
1449 $bSupportTeam = "Y";
1450 $bSupportClient = "Y";
1451 $bDemo = "Y";
1452 }
1453 if ($bAdmin!="Y" && $bSupportTeam!="Y" && $bSupportClient!="Y" && $bDemo!="Y") return false;
1454
1455 $arSqlSearch = Array();
1456 if (is_array($arFilter))
1457 {
1458 $filterKeys = array_keys($arFilter);
1459 $filterKeysCount = count($filterKeys);
1460 for ($i=0; $i<$filterKeysCount; $i++)
1461 {
1462 $key = $filterKeys[$i];
1463 $val = $arFilter[$filterKeys[$i]];
1464 if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF')))
1465 continue;
1466 $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false;
1467 $key = strtoupper($key);
1468 switch($key)
1469 {
1470 case "ID":
1471 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
1472 $arSqlSearch[] = GetFilterQuery("M.ID",$val,$match);
1473 break;
1474 case "TICKET_ID":
1475 $arSqlSearch[] = "M.TICKET_ID = ".intval($val);
1476 break;
1477 case "TICKET":
1478 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
1479 $arSqlSearch[] = GetFilterQuery("M.TICKET_ID",$val,$match);
1480 break;
1481 case "IS_MESSAGE":
1482 $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')";
1483 break;
1484 case "IS_HIDDEN":
1485 case "IS_LOG":
1486 case "IS_OVERDUE":
1487 case "NOT_CHANGE_STATUS":
1488 case "MESSAGE_BY_SUPPORT_TEAM":
1489 $arSqlSearch[] = ($val=="Y") ? "M.".$key."='Y'" : "M.".$key."='N'";
1490 break;
1491 case "EXTERNAL_FIELD_1":
1492 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
1493 $arSqlSearch[] = GetFilterQuery("M.EXTERNAL_FIELD_1", $val, $match);
1494 break;
1495 }
1496 }
1497 }
1498 if ($getUserName=="Y")
1499 {
1500 $u_select = "
1501 ,
1502 UO.EMAIL OWNER_EMAIL,
1503 UO.LOGIN OWNER_LOGIN,
1504 concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) OWNER_NAME,
1505 UO.LOGIN LOGIN,
1506 concat(ifnull(UO.NAME,''),' ',ifnull(UO.LAST_NAME,'')) NAME,
1507 UC.EMAIL CREATED_EMAIL,
1508 UC.LOGIN CREATED_LOGIN,
1509 concat(ifnull(UC.NAME,''),' ',ifnull(UC.LAST_NAME,'')) CREATED_NAME,
1510 UM.EMAIL MODIFIED_EMAIL,
1511 UM.LOGIN MODIFIED_LOGIN,
1512 concat(ifnull(UM.NAME,''),' ',ifnull(UM.LAST_NAME,'')) MODIFIED_NAME
1513 ";
1514 $u_join = "
1515 LEFT JOIN b_user UO ON (UO.ID = M.OWNER_USER_ID)
1516 LEFT JOIN b_user UC ON (UC.ID = M.CREATED_USER_ID)
1517 LEFT JOIN b_user UM ON (UM.ID = M.MODIFIED_USER_ID)
1518 ";
1519 }
1520
1521 if ($bSupportTeam!="Y" && $bAdmin!="Y")
1522 {
1523 $arSqlSearch[] = "M.IS_HIDDEN='N'";
1524 $arSqlSearch[] = "M.IS_LOG='N'";
1525 }
1526 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
1527
1528 if ($by == "s_id") $strSqlOrder = "ORDER BY M.ID";
1529 elseif ($by == "s_number") $strSqlOrder = "ORDER BY M.C_NUMBER";
1530 else
1531 {
1532 $strSqlOrder = "ORDER BY M.C_NUMBER";
1533 }
1534
1535 if ($order=="desc")
1536 {
1537 $strSqlOrder .= " desc ";
1538 }
1539 else
1540 {
1541 $strSqlOrder .= " asc ";
1542 }
1543
1544 $strSql = "
1545 SELECT
1546 M.*,
1547 T.SLA_ID,
1548 ".$DB->DateToCharFunction("M.DATE_CREATE")." DATE_CREATE,
1549 ".$DB->DateToCharFunction("M.TIMESTAMP_X")." TIMESTAMP_X,
1550 DS.NAME SOURCE_NAME
1551 $u_select
1552 FROM
1553 b_ticket_message M
1554 INNER JOIN b_ticket T ON (T.ID = M.TICKET_ID)
1555 LEFT JOIN b_ticket_dictionary DS ON (DS.ID = M.SOURCE_ID)
1556 $u_join
1557 WHERE
1558 $strSqlSearch
1559 $strSqlOrder
1560 ";
1561
1562 $res = $DB->Query($strSql);
1563 return $res;
1564 }
1565
1566 public static function GetDynamicList($by = 's_date_create', $order = 'desc', $arFilter = [])
1567 {
1568 global $DB;
1569 $arSqlSearch = Array();
1570 if (is_array($arFilter))
1571 {
1572 $filterKeys = array_keys($arFilter);
1573 $filterKeysCount = count($filterKeys);
1574 for ($i=0; $i<$filterKeysCount; $i++)
1575 {
1576 $key = $filterKeys[$i];
1577 $val = $arFilter[$filterKeys[$i]];
1578 if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF')))
1579 continue;
1580 $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false;
1581 $key = strtoupper($key);
1582 switch($key)
1583 {
1584 case "DATE_CREATE_1":
1585 if (CheckDateTime($val))
1586 $arSqlSearch[] = "T.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT");
1587 break;
1588 case "DATE_CREATE_2":
1589 if (CheckDateTime($val))
1590 $arSqlSearch[] = "T.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
1591 break;
1592 case "RESPONSIBLE":
1593 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
1594 $arSqlSearch[] = GetFilterQuery("T.RESPONSIBLE_USER_ID, UR.LOGIN, UR.LAST_NAME, UR.NAME", $val, $match);
1595 break;
1596 case "RESPONSIBLE_ID":
1597 if (intval($val)>0) $arSqlSearch[] = "T.RESPONSIBLE_USER_ID = '".intval($val)."'";
1598 elseif ($val==0) $arSqlSearch[] = "(T.RESPONSIBLE_USER_ID is null or T.RESPONSIBLE_USER_ID=0)";
1599 break;
1600 case "SLA_ID":
1601 case "SLA":
1602 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
1603 $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match);
1604 break;
1605 case "CATEGORY_ID":
1606 case "CATEGORY":
1607 if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'";
1608 elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)";
1609 break;
1610 case "CRITICALITY_ID":
1611 case "CRITICALITY":
1612 if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'";
1613 elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)";
1614 break;
1615 case "STATUS_ID":
1616 case "STATUS":
1617 if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'";
1618 elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)";
1619 break;
1620 case "MARK_ID":
1621 case "MARK":
1622 if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'";
1623 elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)";
1624 break;
1625 case "SOURCE_ID":
1626 case "SOURCE":
1627 if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'";
1628 elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)";
1629 break;
1630 case "DIFFICULTY_ID":
1631 case "DIFFICULTY":
1632 if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'";
1633 elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)";
1634 break;
1635 }
1636 }
1637 }
1638 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
1639 if ($by == "s_date_create") $strSqlOrder = "ORDER BY T.DATE_CREATE";
1640 else
1641 {
1642 $strSqlOrder = "ORDER BY T.DATE_CREATE";
1643 }
1644
1645 if ($order!="asc")
1646 {
1647 $strSqlOrder .= " desc ";
1648 }
1649
1650 $strSql = "
1651 SELECT
1652 count(T.ID) ALL_TICKETS,
1653 sum(if(T.DATE_CLOSE is null,1,0)) OPEN_TICKETS,
1654 sum(if(T.DATE_CLOSE is null,0,1)) CLOSE_TICKETS,
1655 DAYOFMONTH(T.DAY_CREATE) CREATE_DAY,
1656 MONTH(T.DAY_CREATE) CREATE_MONTH,
1657 YEAR(T.DAY_CREATE) CREATE_YEAR
1658 FROM
1659 b_ticket T
1660 LEFT JOIN b_user UR ON (T.RESPONSIBLE_USER_ID = UR.ID)
1661 WHERE
1662 $strSqlSearch
1663 and T.DAY_CREATE is not null
1664 GROUP BY
1665 TO_DAYS(T.DAY_CREATE)
1666 $strSqlOrder
1667 ";
1668
1669 $res = $DB->Query($strSql);
1670 return $res;
1671 }
1672
1673 public static function GetMessageDynamicList($by = 's_date_create', $order = 'desc', $arFilter = [])
1674 {
1675 global $DB;
1676 $arSqlSearch = Array();
1677 if (is_array($arFilter))
1678 {
1679 $filterKeys = array_keys($arFilter);
1680 $filterKeysCount = count($filterKeys);
1681 for ($i=0; $i<$filterKeysCount; $i++)
1682 {
1683 $key = $filterKeys[$i];
1684 $val = $arFilter[$filterKeys[$i]];
1685 if ((is_array($val) && count($val)<=0) || (!is_array($val) && ((string) $val == '' || $val==='NOT_REF')))
1686 continue;
1687 $matchValueSet = (in_array($key."_EXACT_MATCH", $filterKeys)) ? true : false;
1688 $key = strtoupper($key);
1689 switch($key)
1690 {
1691 case "SITE":
1692 case "SITE_ID":
1693 if (is_array($val)) $val = implode(" | ", $val);
1694 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
1695 $arSqlSearch[] = GetFilterQuery("T.SITE_ID",$val,$match);
1696 break;
1697 case "DATE_CREATE_1":
1698 if (CheckDateTime($val))
1699 $arSqlSearch[] = "M.DATE_CREATE>=".$DB->CharToDateFunction($val, "SHORT");
1700 break;
1701 case "DATE_CREATE_2":
1702 if (CheckDateTime($val))
1703 $arSqlSearch[] = "M.DATE_CREATE<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
1704 break;
1705 case "OWNER":
1706 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $matchValueSet) ? "N" : "Y";
1707 $arSqlSearch[] = GetFilterQuery("M.OWNER_USER_ID, U.LOGIN, U.LAST_NAME, U.NAME", $val, $match);
1708 break;
1709 case "OWNER_ID":
1710 if (intval($val)>0) $arSqlSearch[] = "M.OWNER_USER_ID = '".intval($val)."'";
1711 elseif ($val==0) $arSqlSearch[] = "(M.OWNER_USER_ID is null or M.OWNER_USER_ID=0)";
1712 break;
1713 case "IS_HIDDEN":
1714 case "IS_LOG":
1715 case "IS_OVERDUE":
1716 $arSqlSearch[] = ($val=="Y") ? "M.".$key."='Y'" : "M.".$key."='N'";
1717 break;
1718 case "SLA_ID":
1719 case "SLA":
1720 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $matchValueSet) ? "Y" : "N";
1721 $arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, $match);
1722 break;
1723 case "CATEGORY_ID":
1724 case "CATEGORY":
1725 if (intval($val)>0) $arSqlSearch[] = "T.CATEGORY_ID = '".intval($val)."'";
1726 elseif ($val==0) $arSqlSearch[] = "(T.CATEGORY_ID is null or T.CATEGORY_ID=0)";
1727 break;
1728 case "CRITICALITY_ID":
1729 case "CRITICALITY":
1730 if (intval($val)>0) $arSqlSearch[] = "T.CRITICALITY_ID = '".intval($val)."'";
1731 elseif ($val==0) $arSqlSearch[] = "(T.CRITICALITY_ID is null or T.CRITICALITY_ID=0)";
1732 break;
1733 case "STATUS_ID":
1734 case "STATUS":
1735 if (intval($val)>0) $arSqlSearch[] = "T.STATUS_ID = '".intval($val)."'";
1736 elseif ($val==0) $arSqlSearch[] = "(T.STATUS_ID is null or T.STATUS_ID=0)";
1737 break;
1738 case "MARK_ID":
1739 case "MARK":
1740 if (intval($val)>0) $arSqlSearch[] = "T.MARK_ID = '".intval($val)."'";
1741 elseif ($val==0) $arSqlSearch[] = "(T.MARK_ID is null or T.MARK_ID=0)";
1742 break;
1743 case "SOURCE_ID":
1744 case "SOURCE":
1745 if (intval($val)>0) $arSqlSearch[] = "T.SOURCE_ID = '".intval($val)."'";
1746 elseif ($val==0) $arSqlSearch[] = "(T.SOURCE_ID is null or T.SOURCE_ID=0)";
1747 break;
1748 case "DIFFICULTY_ID":
1749 case "DIFFICULTY":
1750 if (intval($val)>0) $arSqlSearch[] = "T.DIFFICULTY_ID = '".intval($val)."'";
1751 elseif ($val==0) $arSqlSearch[] = "(T.DIFFICULTY_ID is null or T.DIFFICULTY_ID=0)";
1752 break;
1753 }
1754 }
1755 }
1756 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
1757 if ($by == "s_date_create") $strSqlOrder = "ORDER BY M.DATE_CREATE";
1758 else
1759 {
1760 $strSqlOrder = "ORDER BY M.DATE_CREATE";
1761 }
1762
1763 if ($order!="asc")
1764 {
1765 $strSqlOrder .= " desc ";
1766 }
1767
1768 $strSql = "
1769 SELECT
1770 count(M.ID) COUNTER,
1771 sum(if(M.EXPIRE_AGENT_DONE='Y', 1, 0)) COUNTER_OVERDUE,
1772 DAYOFMONTH(M.DAY_CREATE) CREATE_DAY,
1773 MONTH(M.DAY_CREATE) CREATE_MONTH,
1774 YEAR(M.DAY_CREATE) CREATE_YEAR
1775 FROM
1776 b_ticket_message M
1777 INNER JOIN b_ticket T ON (T.ID = M.TICKET_ID)
1778 LEFT JOIN b_user U ON (M.OWNER_USER_ID = U.ID)
1779 WHERE
1780 $strSqlSearch
1781 GROUP BY
1782 TO_DAYS(M.DAY_CREATE)
1783 $strSqlOrder
1784 ";
1785
1786 $res = $DB->Query($strSql);
1787 return $res;
1788 }
1789}
$arParams
$arParams
Определения access_dialog.php:21
$APPLICATION
global $APPLICATION
Определения include.php:80
CAllTicket
Определения support.php:8
CAllTicket\GetMessageByID
static GetMessageByID($id, $checkRights="Y", $get_user_name="Y")
Определения support.php:1423
CAllTicket\IsSupportTeam
static IsSupportTeam($userID=false)
Определения support.php:114
CAllTicket\IsAdmin
static IsAdmin($userID=false)
Определения support.php:94
CAllTicket\IsSupportClient
static IsSupportClient($userID=false)
Определения support.php:121
CAllTicket\MarkMessageAsSpam
static MarkMessageAsSpam($messageID, $exactly="Y", $checkRights="Y")
Определения support.php:697
CAllTicket\DELETE
const DELETE
Определения support.php:12
CAllTicket\IsDemo
static IsDemo($userID=false)
Определения support.php:107
CAllTicket\GetAdminGroups
static GetAdminGroups()
Определения support.php:224
CAllTicket\GetSupportTeamGroups
static GetSupportTeamGroups()
Определения support.php:230
CAllTicket\UpdateMessages
static UpdateMessages($ticketID)
Определения support.php:1251
CAllTicket\UnMarkMessageAsSpam
static UnMarkMessageAsSpam($messageID, $checkRights="Y")
Определения support.php:746
CAllTicket\UpdateLastParamsN
static UpdateLastParamsN($ticketID, $dateType, $recalculateSupportDeadline=true, $setReopenDefault=true)
Определения support.php:1066
CSupportSearch\getSql
static getSql($query)
Определения search.php:11
CSupportSearch\reindexTicket
static reindexTicket($ticket, $messages=null)
Определения search.php:387
CSupportSearch\isIndexExists
static isIndexExists()
Определения search.php:258
CTicket
Определения support.php:5
CTicket\GetSupportTeamList
static GetSupportTeamList()
Определения support.php:1370
CTicket\AddMessage
static AddMessage($ticketID, $arFields, &$arrFILES, $checkRights="Y")
Определения support.php:301
CTicket\GetStatus
static GetStatus($ticketID)
Определения support.php:575
CTicket\DeleteMessage
static DeleteMessage($ID, $checkRights="Y")
Определения support.php:97
CTicket\AutoClose
static AutoClose()
Определения support.php:11
CTicket\CleanUpOnline
static CleanUpOnline()
Определения support.php:55
CTicket\GetMessageDynamicList
static GetMessageDynamicList($by='s_date_create', $order='desc', $arFilter=[])
Определения support.php:1673
CTicket\GetOnline
static GetOnline($ticketID)
Определения support.php:67
CTicket\GetMessageList
static GetMessageList($by='s_number', $order='asc', $arFilter=[], $isFiltered=null, $checkRights="Y", $getUserName="Y")
Определения support.php:1435
CTicket\isnull
static isnull( $field, $alternative)
Определения support.php:6
CTicket\GetDynamicList
static GetDynamicList($by='s_date_create', $order='desc', $arFilter=[])
Определения support.php:1566
CTicket\UpdateMessage
static UpdateMessage($MESSAGE_ID, $arFields, $checkRights="Y")
Определения support.php:152
$str
$str
Определения commerceml2.php:63
$f
$f
Определения component_props.php:52
$arFields
$arFields
Определения dblapprove.php:5
array
</td ></tr ></table ></td ></tr >< tr >< td class="bx-popup-label bx-width30"><?=GetMessage("PAGE_NEW_TAGS")?> array( $site)
Определения file_new.php:804
$bAdmin
while($group=$gr->Fetch()) $bAdmin
Определения file_new.php:154
$res
$res
Определения filter_act.php:7
GetFilterSqlSearch
GetFilterSqlSearch($arSqlSearch=array(), $FilterLogic="FILTER_logic")
Определения filter_tools.php:397
GetFilterQuery
GetFilterQuery($field, $val, $procent="Y", $ex_sep=array(), $clob="N", $div_fields="Y", $clob_upper="N")
Определения filter_tools.php:383
$zr
$zr
Определения options.php:5
$USER_FIELD_MANAGER
global $USER_FIELD_MANAGER
Определения attempt.php:6
$result
$result
Определения get_property_values.php:14
$query
$query
Определения get_search.php:11
$ID
if($ajaxMode) $ID
Определения get_user.php:27
$uid
$uid
Определения hot_keys_act.php:8
$strError
$strError
Определения options_user_settings.php:4
$_SERVER
$_SERVER["DOCUMENT_ROOT"]
Определения cron_frame.php:9
$DB
global $DB
Определения cron_frame.php:29
$siteID
$siteID
Определения cron_frame.php:12
$USER
global $USER
Определения csv_new_run.php:40
if
if( $adminSidePanelHelper->isSidePanel())
Определения csv_new_setup.php:231
$z
$z
Определения options.php:31
$mid
$mid
Определения options.php:27
GetTime
GetTime($timestamp, $type="SHORT", $site=false, $bSearchInSitesOnly=false)
Определения tools.php:1890
is_set
is_set($a, $k=false)
Определения tools.php:2133
CheckDateTime
CheckDateTime($datetime, $format=false)
Определения tools.php:398
Bitrix\Main\$table
$table
Определения mysql_to_pgsql.php:36
$order
$order
Определения payment.php:8
false
return false
Определения prolog_main_admin.php:185
elseif
if( $daysToExpire >=0 &&$daysToExpire< 60 elseif)( $daysToExpire< 0)
Определения prolog_main_admin.php:393
$ar
$ar
Определения options.php:199
$key
if(empty($signedUserToken)) $key
Определения quickway.php:257
$md5
$md5
Определения result_rec.php:12
$i
$i
Определения factura.php:643
count
</p ></td >< td valign=top style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 2.0pt 0cm 2.0pt;height:9.0pt'>< p class=Normal align=center style='margin:0cm;margin-bottom:.0001pt;text-align:center;line-height:normal'>< a name=ТекстовоеПоле54 ></a ><?=($taxRate > count( $arTaxList) > 0) ? $taxRate."%"
Определения waybill.php:936
$val
$val
Определения options.php:1793
$rows
$rows
Определения options.php:264
$error
$error
Определения subscription_card_product.php:20
$rs
$rs
Определения action.php:82
$arFilter
$arFilter
Определения user_search.php:106
break
switch( $_REQUEST[ 'bxsender'])().Authorize(<? break
Определения wrapper_popup.php:7