function GetListEx($arOrder = array(), $arFilter = array(), $arParams = array()) { global $DB; $arSqlSearch = array(); $strSqlSearch = ""; $arSqlOrder = array(); foreach ($arFilter as $key => $val) { if (strlen($val) <= 0) { continue; } $val = $DB->ForSql($val); $key_res = CEventType::GetFilterOperation($key); $key = strToUpper($key_res["FIELD"]); $strNegative = $key_res["NEGATIVE"]; $strOperation = $key_res["OPERATION"]; switch ($key) { case "EVENT_NAME": case "TYPE_ID": if ($strOperation == "LIKE") { $val = "%" . $val . "%"; } $arSqlSearch[] = ($strNegative == "Y" ? " #TABLE_ID#.EVENT_NAME IS NULL OR NOT " : "") . "(#TABLE_ID#.EVENT_NAME " . $strOperation . " '" . $val . "' )"; break; case "DESCRIPTION": case "NAME": if ($strOperation == "LIKE") { $val = "%" . $val . "%"; } $arSqlSearch[] = ($strNegative == "Y" ? " ET." . $key . " IS NULL OR NOT " : "") . "(ET." . $key . " " . $strOperation . " '" . $val . "' )"; break; case "LID": $arSqlSearch[] = ($strNegative == "Y" ? " ET." . $key . " IS NULL OR NOT " : "") . "(ET." . $key . " " . $strOperation . " '" . $val . "' )"; break; case "ID": $arSqlSearch[] = ($strNegative == "Y" ? " ET." . $key . " IS NULL OR NOT " : "") . "(ET." . $key . " " . $strOperation . " " . intVal($val) . " )"; break; case "MESSAGE_ID": $arSqlSearch[] = ($strNegative == "Y" ? " ET.ID IS NULL OR NOT " : "") . "(EM.ID " . $strOperation . " " . intVal($val) . " )"; break; } } if (count($arSqlSearch) > 0) { $strSqlSearch = "WHERE (" . implode(") AND (", $arSqlSearch) . ") "; } if (is_array($arOrder)) { foreach ($arOrder as $by => $order) { $by = strtoupper($by); $order = strtoupper($order); $order = $order != "DESC" ? "ASC" : "DESC"; if ($by == "EVENT_NAME" || $by == "ID") { $arSqlOrder["EVENT_NAME"] = "EVENT_NAME " . $order; } } } if (empty($arSqlOrder)) { $arSqlOrder["EVENT_NAME"] = "EVENT_NAME ASC"; } $strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder); $strSql = "\n\t\t\tSELECT EM.EVENT_NAME AS ID, EM.EVENT_NAME AS EVENT_NAME\n\t\t\tFROM b_event_message EM\n\t\t\tLEFT JOIN b_event_type ET ON (ET.EVENT_NAME = EM.EVENT_NAME)\n\t\t\t" . str_replace("#TABLE_ID#", "EM", $strSqlSearch) . "\n\t\t\tUNION\n\t\t\tSELECT ET.EVENT_NAME AS ID, ET.EVENT_NAME\n\t\t\tFROM b_event_type ET\n\t\t\tLEFT JOIN b_event_message EM ON (ET.EVENT_NAME = EM.EVENT_NAME)\n\t\t\t" . str_replace("#TABLE_ID#", "ET", $strSqlSearch) . "\n\t\t\t" . $strSqlOrder; $db_res = $DB->Query($strSql, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__); $db_res = new _CEventTypeResult($db_res, $arParams); return $db_res; }
function GetListEx($arOrder = array(), $arFilter = array(), $arParams = array()) { global $DB; $arSearch = $arSearch1 = $arSearch2 = array(); $arSelect = array(); $arSqlSearch = array(); $strSqlSearch = ""; $arSqlOrder = array(); foreach ($arFilter as $key => $val) { if (strlen($val) <= 0) { continue; } $val = $DB->ForSql($val); $key_res = CEventType::GetFilterOperation($key); $key = strToUpper($key_res["FIELD"]); $strNegative = $key_res["NEGATIVE"]; $strOperation = $key_res["OPERATION"]; $strNOperation = $key_res["NOPERATION"]; $arOperationReplace = array('LIKE' => '=%', 'QUERY' => '', 'IN' => ''); switch ($key) { case "EVENT_NAME": case "TYPE_ID": if ($strOperation == "LIKE") { $val = "%" . $val . "%"; } $arSearch[] = array($strNOperation . 'EVENT_NAME' => $val); break; case "DESCRIPTION": case "NAME": if ($strOperation == "LIKE") { $val = "%" . $val . "%"; } $arSearch1[] = array($strNOperation . 'EVENT_MESSAGE_TYPE.' . $key => $val); $arSearch2[] = array($strNOperation . $key => $val); break; case "LID": $arSearch1[] = array($strNOperation . 'EVENT_MESSAGE_TYPE.' . $key => $val); $arSearch2[] = array($strNOperation . $key => $val); break; case "ID": $val = intVal($val); $arSearch1[] = array($strNOperation . 'EVENT_MESSAGE_TYPE.' . $key => $val); $arSearch2[] = array($strNOperation . $key => $val); break; case "MESSAGE_ID": $val = intVal($val); $arSearch1[] = array($strNOperation . "ID" => $val); $arSearch2[] = array($strNOperation . 'EVENT_MESSAGE.ID' => $val); break; } } if (is_array($arOrder)) { foreach ($arOrder as $by => $order) { $by = strtoupper($by); $order = strtoupper($order); $order = $order != "DESC" ? "ASC" : "DESC"; if ($by == "EVENT_NAME" || $by == "ID") { $arSqlOrder["EVENT_NAME"] = "EVENT_NAME1 " . $order; } } } if (empty($arSqlOrder)) { $arSqlOrder["EVENT_NAME"] = "EVENT_NAME1 ASC"; } $strSqlOrder = " ORDER BY " . implode(", ", $arSqlOrder); $arSearch['!EVENT_NAME'] = null; $arQuerySelect = array('ID1' => 'EVENT_NAME', 'EVENT_NAME1' => 'EVENT_NAME'); $query1 = new \Bitrix\Main\Entity\Query(Mail\Internal\EventMessageTable::getEntity()); $query1->setSelect($arQuerySelect); $query1->setFilter(array_merge($arSearch, $arSearch1)); $query1->registerRuntimeField('EVENT_MESSAGE_TYPE', array('data_type' => 'Bitrix\\Main\\Mail\\Internal\\EventType', 'reference' => array('=this.EVENT_NAME' => 'ref.EVENT_NAME'))); $query2 = new \Bitrix\Main\Entity\Query(Mail\Internal\EventTypeTable::getEntity()); $query2->setSelect($arQuerySelect); $query2->setFilter(array_merge($arSearch, $arSearch2)); $query2->registerRuntimeField('EVENT_MESSAGE', array('data_type' => 'Bitrix\\Main\\Mail\\Internal\\EventMessage', 'reference' => array('=this.EVENT_NAME' => 'ref.EVENT_NAME'))); $connection = \Bitrix\Main\Application::getConnection(); $strSql = $query1->getQuery() . " UNION " . $query2->getQuery() . " " . $strSqlOrder; $db_res = $connection->query($strSql); $db_res->addFetchDataModifier(array('CEventType', 'GetListExFetchDataModifier')); $db_res = new _CEventTypeResult($db_res, $arParams); return $db_res; }