function GetResponsibleList($userID, $CMGM = null, $CMUGM = null, $SG = null) { $condition = ""; if ($CMGM != null) { $condition .= "\n\t\t\t\t\t\t\tAND TUG2.CAN_MAIL_GROUP_MESSAGES = '" . ($CMGM == "Y" ? "Y" : "N") . "'"; } if ($CMUGM != null) { $condition .= "\n\t\t\t\t\t\t\tAND TUG2.CAN_MAIL_UPDATE_GROUP_MESSAGES = '" . ($CMUGM == "Y" ? "Y" : "N") . "'"; } $condition2 = ""; if ($SG != null) { $condition2 .= "\n\t\t\t\t\t\t\tAND TG.IS_TEAM_GROUP = '" . ($SG == "Y" ? "Y" : "N") . "'"; } $err_mess = CTicket::err_mess() . "<br>Function: GetSupportTeamMailList<br>Line: "; global $DB; $strSql = "\n\t\t\tSELECT\n\t\t\t\tU.ID as ID,\n\t\t\t\tU.LOGIN as LOGIN,\n\t\t\t\t" . $DB->Concat(CTicket::isnull("U.LAST_NAME", "''"), CTicket::isnull("U.NAME", "''"), 'U.LOGIN') . " as NAME,\n\t\t\t\tU.EMAIL as EMAIL\n\t\t\tFROM\n\t\t\t\t(\n\t\t\t\tSELECT\n\t\t\t\t\tTUG2.USER_ID AS USER_ID\t\t\t\t\n\t\t\t\tFROM\n\t\t\t\t\tb_ticket_ugroups TG\n\t\t\t\t\tINNER JOIN b_ticket_user_ugroup TUG\n\t\t\t\t\t\tON TG.ID = TUG.GROUP_ID" . $condition2 . "\n\t\t\t\t\tINNER JOIN b_ticket_user_ugroup TUG2\n\t\t\t\t\t\tON TUG.USER_ID = '" . intval($userID) . "'\n\t\t\t\t\t\t\tAND TUG.GROUP_ID = TUG2.GROUP_ID" . $condition . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tTUG2.USER_ID\n\t\t\t\t) TU\n\t\t\t\tINNER JOIN b_user U\n\t\t\t\t\tON TU.USER_ID = U.ID\n\t\t\t\tORDER BY\n\t\t\t\t\tU.ID\n\t\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); return $res; }
function GetMessageDynamicList(&$by, &$order, $arFilter = array()) { $err_mess = CTicket::err_mess() . "<br>Function: GetMessageDynamicList<br>Line: "; global $DB; $arSqlSearch = array(); $strSqlSearch = ""; if (is_array($arFilter)) { $filterKeys = array_keys($arFilter); for ($i = 0; $i < count($filterKeys); $i++) { $key = $filterKeys[$i]; $val = $arFilter[$filterKeys[$i]]; if (is_array($val) && count($val) <= 0 || !is_array($val) && (strlen($val) <= 0 || $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 { $by = "s_date_create"; $strSqlOrder = "ORDER BY M.DATE_CREATE"; } if ($order != "asc") { $strSqlOrder .= " desc "; $order = "desc"; } $strSql = "\n\t\t\tSELECT\n\t\t\t\tcount(M.ID)\t\t\t\t\t\t\t\tCOUNTER,\n\t\t\t\tsum(if(M.EXPIRE_AGENT_DONE='Y', 1, 0))\tCOUNTER_OVERDUE,\n\t\t\t\tDAYOFMONTH(M.DAY_CREATE)\t\t\t\tCREATE_DAY,\n\t\t\t\tMONTH(M.DAY_CREATE)\t\t\t\t\t\tCREATE_MONTH,\n\t\t\t\tYEAR(M.DAY_CREATE)\t\t\t\t\t\tCREATE_YEAR\n\t\t\tFROM\n\t\t\t\tb_ticket_message M\n\t\t\tINNER JOIN b_ticket T ON (T.ID = M.TICKET_ID)\n\t\t\tLEFT JOIN b_user U ON (M.OWNER_USER_ID = U.ID)\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY\n\t\t\t\tTO_DAYS(M.DAY_CREATE)\n\t\t\t{$strSqlOrder}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); return $res; }
public static function GetResponsibleList($userID, $CMGM = null, $CMUGM = null, $SG = null) { $condition = ""; if($CMGM != null) $condition .= " AND TUG2.CAN_MAIL_GROUP_MESSAGES = '" . ($CMGM == "Y" ? "Y" : "N") . "'"; if($CMUGM != null) $condition .= " AND TUG2.CAN_MAIL_UPDATE_GROUP_MESSAGES = '" . ($CMUGM == "Y" ? "Y" : "N") . "'"; $condition2 = ""; if($SG != null) $condition2 .= " AND TG.IS_TEAM_GROUP = '" . ($SG == "Y" ? "Y" : "N") . "'"; $err_mess = (CTicket::err_mess())."<br>Function: GetSupportTeamMailList<br>Line: "; global $DB; $strSql = " SELECT U.ID as ID, U.LOGIN as LOGIN, ". $DB->Concat(CTicket::isnull("U.LAST_NAME", "''"), CTicket::isnull("U.NAME", "''"), 'U.LOGIN')." as NAME, U.EMAIL as EMAIL FROM ( SELECT TUG2.USER_ID AS USER_ID FROM b_ticket_ugroups TG INNER JOIN b_ticket_user_ugroup TUG ON TG.ID = TUG.GROUP_ID" . $condition2 . " INNER JOIN b_ticket_user_ugroup TUG2 ON TUG.USER_ID = '" . intval($userID) . "' AND TUG.GROUP_ID = TUG2.GROUP_ID" . $condition . " GROUP BY TUG2.USER_ID ) TU INNER JOIN b_user U ON TU.USER_ID = U.ID ORDER BY U.ID "; $res = $DB->Query($strSql, false, $err_mess.__LINE__); return $res; }