public static function RecalculateSupportDeadline($arFilter = array())
{
global $DB;
$err_mess = CAllTicketReminder::err_mess() . "<br>Function: RecalculateSupportDeadline<br>Line: ";
$arSqlSearch = array();
if (!is_array($arFilter)) {
$arFilter = array();
}
foreach ($arFilter as $key => $val) {
if (is_array($val) && count($val) <= 0 || !is_array($val) && strlen($val) <= 0) {
continue;
}
$key = strtoupper($key);
if (is_array($val)) {
$val = implode(" | ", $val);
}
switch ($key) {
case "ID":
$arSqlSearch[] = GetFilterQuery("T.ID", $val, "N");
break;
case "SLA_ID":
$arSqlSearch[] = GetFilterQuery("T.SLA_ID", $val, "N");
break;
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.ID ID,\n\t\t\t\tT.SLA_ID,\n\t\t\t\t" . $DB->DateToCharFunction("T.DEADLINE_SOURCE_DATE", "FULL") . " DEADLINE_SOURCE_DATE,\n\t\t\t\t" . $DB->DateToCharFunction("T.D_1_USER_M_AFTER_SUP_M", "FULL") . " D_1_USER_M_AFTER_SUP_M,\n\t\t\t\tT.IS_OVERDUE,\n\t\t\t\tSLA.RESPONSE_TIME_UNIT,\n\t\t\t\tSLA.RESPONSE_TIME,\n\t\t\t\tSLA.NOTICE_TIME_UNIT,\n\t\t\t\tSLA.NOTICE_TIME\n\t\t\tFROM\n\t\t\t\tb_ticket T\n\t\t\t\tINNER JOIN b_ticket_sla SLA\n\t\t\t\t\tON T.SLA_ID = SLA.ID\n\t\t\t\t\t\tAND T.LAST_MESSAGE_BY_SUPPORT_TEAM = 'N'\n\t\t\t\t\t\tAND (T.DATE_CLOSE IS NULL)\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t";
$rsTicket = $DB->Query($strSql, false, $err_mess . __LINE__);
while ($arTicket = $rsTicket->Fetch()) {
self::RecalculateSupportDeadlineForOneTicket($arTicket);
}
}
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "LAST":
$arSqlSearch[] = $val == "Y" ? "S.DATE_STAT = curdate()" : "S.DATE_STAT<>curdate()";
break;
case "USER_AGENT":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.USER_AGENT", $val, $match);
break;
case "COUNTER1":
$arSqlSearch_h[] = "COUNTER>=" . intval($val);
break;
case "COUNTER2":
$arSqlSearch_h[] = "COUNTER<=" . intval($val);
break;
}
}
for ($i = 0; $i < count($arSqlSearch_h); $i++) {
$strSqlSearch_h .= " and (" . $arSqlSearch_h[$i] . ") ";
}
}
if ($by == "s_user_agent") {
$strSqlOrder = "ORDER BY S.USER_AGENT";
} elseif ($by == "s_counter") {
$strSqlOrder = "ORDER BY COUNTER";
} else {
$by = "s_counter";
$strSqlOrder = "ORDER BY COUNTER";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "SET OPTION SQL_BIG_SELECTS=1";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tS.USER_AGENT,\n\t\t\t\tcount(S.ID) COUNTER\n\t\t\tFROM\n\t\t\t\tb_stat_session S\n\t\t\tLEFT JOIN b_stat_browser B ON (\n\t\t\t\tlength(B.USER_AGENT)>0\n\t\t\tand B.USER_AGENT is not null\n\t\t\tand\tupper(S.USER_AGENT) like upper(B.USER_AGENT)\n\t\t\t)\n\t\t\tLEFT JOIN b_stat_searcher R ON (\n\t\t\t\tlength(R.USER_AGENT)>0\n\t\t\tand\tR.USER_AGENT is not null\n\t\t\tand\tupper(S.USER_AGENT) like upper(concat('%',R.USER_AGENT,'%'))\n\t\t\t)\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tand S.USER_AGENT is not null\n\t\t\tand S.USER_AGENT<>''\n\t\t\tand S.NEW_GUEST<>'N'\n\t\t\tand B.ID is null\n\t\t\tand R.ID is null\n\t\t\tGROUP BY S.USER_AGENT\n\t\t\tHAVING '1'='1' {$strSqlSearch_h}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch) || strlen($strSqlSearch_h) > 0;
return $res;
}
protected function initExternalValues($filterName)
{
if (!empty($_REQUEST['del_filter_' . $filterName])) {
return;
}
$this->externalValues = array('UF_DEPARTMENT' => $_REQUEST[$filterName . '_UF_DEPARTMENT'], 'POST' => isset($_REQUEST[$filterName . '_POST']) ? $_REQUEST[$filterName . '_POST'] : null, 'COMPANY' => isset($_REQUEST[$filterName . '_COMPANY']) ? $_REQUEST[$filterName . '_COMPANY'] : null, 'EMAIL' => isset($_REQUEST[$filterName . '_EMAIL']) ? $_REQUEST[$filterName . '_EMAIL'] : null, 'FIO' => isset($_REQUEST[$filterName . '_FIO']) && GetFilterQuery("TEST", $_REQUEST[$filterName . '_FIO']) ? $_REQUEST[$filterName . '_FIO'] : null, 'PHONE' => isset($_REQUEST[$filterName . '_PHONE']) ? $_REQUEST[$filterName . '_PHONE'] : null, 'UF_PHONE_INNER' => isset($_REQUEST[$filterName . '_UF_PHONE_INNER']) ? $_REQUEST[$filterName . '_UF_PHONE_INNER'] : null, 'KEYWORDS' => isset($_REQUEST[$filterName . '_KEYWORDS']) ? $_REQUEST[$filterName . '_KEYWORDS'] : null, 'IS_ONLINE' => isset($_REQUEST[$filterName . '_IS_ONLINE']) ? $_REQUEST[$filterName . '_IS_ONLINE'] : null, 'LAST_NAME' => isset($_REQUEST[$filterName . '_LAST_NAME']) ? $_REQUEST[$filterName . '_LAST_NAME'] : null, 'LAST_NAME_RANGE' => isset($_REQUEST[$filterName . '_LAST_NAME_RANGE']) ? $_REQUEST[$filterName . '_LAST_NAME_RANGE'] : null);
if ($this->externalValues['UF_DEPARTMENT'] !== null) {
if (!is_array($this->externalValues['UF_DEPARTMENT'])) {
$this->externalValues['UF_DEPARTMENT'] = array($this->externalValues['UF_DEPARTMENT']);
}
$this->externalValues['UF_DEPARTMENT'] = array_filter(array_map('intval', $this->externalValues['UF_DEPARTMENT']));
} else {
$this->externalValues['UF_DEPARTMENT'] = array();
}
}
public static function GetList($arOrder = array("ID" => "ASC"), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
foreach ($arFilter as $key => $val) {
$key = strtoupper($key);
switch ($key) {
case "ID":
case "POST_ID":
case "USER_ID":
$val = intval($val);
$arSqlSearch[] = $val > 0 ? "BPP." . $key . " IS NULL" : "BPP." . $key . "=" . $val;
break;
case "NAME":
$arSqlSearch[] = "BPP.NAME = '" . $DB->ForSql($val, 50) . "'";
break;
case "NAME_MASK":
$arSqlSearch[] = GetFilterQuery("BPP.NAME", $val);
break;
}
}
$strSql = "\n\t\t\tSELECT BPP.ID, BPP.USER_ID, BPP.POST_ID, BPP.NAME, BPP.VALUE\n\t\t\tFROM b_blog_post_param BPP\n\t\t\tWHERE 1 = 1\n\t\t\t" . (empty($arSqlSearch) ? "" : " AND (" . implode($arSqlSearch, ") AND (") . ")") . "\n\t\t";
$arSqlOrder = array();
if (is_array($arOrder)) {
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
}
if ($by == "ID") {
$arSqlOrder[$by] = " BP.ID " . $order . " ";
} elseif ($by == "USER_ID") {
$arSqlOrder[$by] = " BP.USER_ID " . $order . " ";
} elseif ($by == "POST_ID") {
$arSqlOrder[$by] = " BP.POST_ID " . $order . " ";
} elseif ($by == "NAME") {
$arSqlOrder[$by] = " BP.NAME " . $order . " ";
}
}
}
$strSqlOrder = !empty($arSqlOrder) ? "ORDER BY " . implode(", ", $arSqlOrder) : "";
return $DB->Query($strSql . $strSqlOrder, false, "FILE: " . __FILE__ . "<br> LINE: " . __LINE__);
}
private static function makeSqlSearch($arFilter)
{
$arSqlSearch = array();
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (strlen($val) <= 0) {
continue;
}
$key = strtoupper($key);
switch ($key) {
case "PAYSYSTEM_ID":
$arSqlSearch[] = $key . "=" . intval($val);
break;
case "DELIVERY_ID":
case "DELIVERY_PROFILE_ID":
$arSqlSearch[] = GetFilterQuery($key, $val, "N");
break;
}
}
}
return GetFilterSqlSearch($arSqlSearch);
}
public static function GetList($Params = array())
{
global $DB;
$arFilter = $Params['arFilter'];
$arOrder = isset($Params['arOrder']) ? $Params['arOrder'] : array('ID' => 'asc');
$arFields = array("ID" => array("FIELD_NAME" => "U.ID", "FIELD_TYPE" => "string"), "MODULE_ID" => array("FIELD_NAME" => "U.MODULE_ID", "FIELD_TYPE" => "string"), "UNDO_TYPE" => array("FIELD_NAME" => "U.UNDO_TYPE", "FIELD_TYPE" => "string"), "UNDO_HANDLER" => array("FIELD_NAME" => "U.UNDO_HANDLER", "FIELD_TYPE" => "string"), "CONTENT" => array("FIELD_NAME" => "U.CONTENT", "FIELD_TYPE" => "string"), "USER_ID" => array("FIELD_NAME" => "U.USER_ID", "FIELD_TYPE" => "int"), "TIMESTAMP_X" => array("FIELD_NAME" => "U.TIMESTAMP_X", "FIELD_TYPE" => "int"));
$err_mess = "CUndo::GetList<br>Line: ";
$arSqlSearch = array();
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
for ($i = 0, $l = count($filter_keys); $i < $l; $i++) {
$n = strtoupper($filter_keys[$i]);
$val = $arFilter[$filter_keys[$i]];
if ($n == 'ID' || $n == 'USER_ID') {
$arSqlSearch[] = GetFilterQuery("U." . $n, $val, 'N');
} elseif (isset($arFields[$n])) {
$arSqlSearch[] = GetFilterQuery($arFields[$n]["FIELD_NAME"], $val);
}
}
}
$strOrderBy = '';
foreach ($arOrder as $by => $order) {
if (isset($arFields[strtoupper($by)])) {
$strOrderBy .= $arFields[strtoupper($by)]["FIELD_NAME"] . ' ' . (strtolower($order) == 'desc' ? 'desc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS LAST" : "") : 'asc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS FIRST" : "")) . ',';
}
}
if (strlen($strOrderBy) > 0) {
$strOrderBy = "ORDER BY " . rtrim($strOrderBy, ",");
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tU.*\n\t\t\tFROM\n\t\t\t\tb_undo U\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t{$strOrderBy}";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$arResult = array();
while ($arRes = $res->Fetch()) {
$arResult[] = $arRes;
}
return $arResult;
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#referer">ссылающихся сайтов (страниц)</a>.</p>
*
*
*
*
* @param string &$by = "s_id" Поле для сортировки. В зависимости от группировки списка, набор
* доступных значений данной переменной может быть различным. <ul>
* <li>при группировке по ссылающейся странице (<i>filter</i>["<b>GROUP</b>"]="U"): <ul>
* <li> <b>s_url_from</b> - ссылающаяся страница; </li> <li> <b>s_quantity</b> - количество
* заходов с ссылающейся страницы; </li> <li> <b>s_average_hits</b> - среднее
* количество <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хитов</a>,
* производимое посетителями заходящими с той или ссылающейся
* страницы. </li> </ul> </li> <li>при группировке по ссылающемуся домену
* (<i>filter</i>["<b>GROUP</b>"]="S"): <ul> <li> <b>s_url_from</b> - ссылающийся домен; </li> <li>
* <b>s_quantity</b> - количество заходов с ссылающегося домена; </li> <li>
* <b>s_average_hits</b> - среднее количество хитов, производимое
* посетителями. </li> </ul> </li> <li>когда группировка не установлена: <ul> <li>
* <b>s_id</b> - ID записи; </li> <li> <b>s_site_id</b> - ID сайта, на который пришли; </li> <li>
* <b>s_url_from</b> - ссылающаяся страница (с которой пришли); </li> <li> <b>s_url_to</b>
* - страница на которую пришли; </li> <li> <b>s_date_hit</b> - дата; </li> <li>
* <b>s_session_id</b> - ID сессии. </li> </ul> </li> </ul>
*
*
*
* @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по
* возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul>
*
*
*
* @param array $filter = array() Массив для фильтрации результирующего списка. В массиве
* допустимы следующие ключи: <ul> <li> <b>ID</b> - ID записи; </li> <li>
* <b>ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по <b>ID</b>
* будет искаться вхождение; </li> <li> <b>SESSION_ID</b> - ID сессии; </li> <li>
* <b>SESSION_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по
* <b>SESSION_ID</b> будет искаться вхождение; </li> <li> <b>DATE1</b> - начальное
* значение интервала для поля "дата"; </li> <li> <b>DATE2</b> - конечное
* значение интервала для поля "дата"; </li> <li> <b>FROM_PROTOCOL</b> - протокол
* ссылающейся страницы; </li> <li> <b>FROM_PROTOCOL_EXACT_MATCH</b> - если значение
* равно "Y", то при фильтрации по <b>FROM_PROTOCOL</b> будет искаться точное
* совпадение; </li> <li> <b>FROM_DOMAIN</b> - домен ссылающейся страницы; </li> <li>
* <b>FROM_DOMAIN_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>FROM_DOMAIN</b> будет искаться точное совпадение; </li> <li> <b>FROM_PAGE</b> -
* ссылающаяся страница; </li> <li> <b>FROM_PAGE_EXACT_MATCH</b> - если значение равно
* "Y", то при фильтрации по <b>FROM_PAGE</b> будет искаться точное
* совпадение; </li> <li> <b>FROM</b> - протокол + домен + ссылающаяся страница;
* </li> <li> <b>FROM_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>FROM</b> будет искаться точное совпадение; </li> <li> <b>TO</b>* - страница на
* которую пришли; </li> <li> <b>TO_EXACT_MATCH</b> - если значение равно "Y", то при
* фильтрации по <b>TO</b> будет искаться точное совпадение; </li> <li>
* <b>TO_404</b> - была ли <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#404">404 ошибка</a>
* на странице, на которую пришли, возможные значения: <ul> <li> <b>Y</b> -
* была; </li> <li> <b>N</b> - не была. </li> </ul> </li> <li> <b>SITE_ID</b> - ID сайта на который
* пришли; </li> <li> <b>GROUP</b> - группировка результирующего списка;
* возможные значения: <ul> <li> <b>S</b> - группировка по ссылающемуся
* домену (сайту); </li> <li> <b>U</b> - группировка по ссылающейся странице.
* </li> </ul> </li> </ul> * - допускается <a
* href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная логика</a>
*
*
*
* @param bool &$is_filtered Флаг отфильтрованности результирующего списка. Если значение
* равно "true", то список был отфильтрован.
*
*
*
* @param int &$total Суммарные количество заходов с ссылающихся страниц.
*
*
*
* @param string &$group_by Группировка результирующего списка. Возможные значения: <ul> <li>
* <b>U</b> - группировка по ссылающейся странице; </li> <li> <b>S</b> -
* группировка по ссылающемуся домену. </li> </ul>
*
*
*
* @param int &$max Количество заходов с самой популярной ссылающейся страницы.
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* // отфильтруем только заходы с доменов "google"
* // сгруппировав по ссылающемуся домену
* $arFilter = array(
* "FROM_DOMAIN" => "google",
* "GROUP" => "S"
* );
*
* // получим список записей
* $rs = <b>CReferer::GetList</b>(
* ($by = "s_url_from"),
* ($order = "desc"),
* $arFilter,
* $is_filtered,
* $total,
* $group_by,
* $max
* );
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#referer">Термин "Ссылающийся
* сайт (страница)"</a> </li> </ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/creferer/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, &$total, &$grby, &$max)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
global $grby, $total;
$DB = CDatabase::GetModuleConnection('statistic');
$group = false;
$strSqlGroup = "GROUP BY L.PROTOCOL, L.SITE_NAME, L.URL_FROM, R.HITS, R.SESSIONS";
$url_from = $DB->Concat("L.PROTOCOL", "L.SITE_NAME", "L.URL_FROM");
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "ID":
case "SESSION_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("L." . $key, $val, $match);
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "L.DATE_HIT >= " . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "L.DATE_HIT < " . CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
}
break;
case "FROM_PROTOCOL":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.PROTOCOL", $val, $match, array("/", "\\", ":"));
break;
case "FROM_DOMAIN":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.SITE_NAME", $val, $match, array("."));
break;
case "FROM_PAGE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.URL_FROM", $val, $match, array("/", "\\", ".", "?", "#", ":", ":"));
break;
case "FROM":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery($url_from, $val, $match, array("/", "\\", ".", "?", "#", ":"), "N", "N");
break;
case "TO":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.URL_TO", $val, $match, array("/", "\\", ".", "?", "#", ":"));
break;
case "TO_404":
$arSqlSearch[] = $val == "Y" ? "L.URL_TO_404='Y'" : "L.URL_TO_404='N'";
break;
case "GROUP":
$group = true;
if ($val == "S") {
$find_group = "S";
$strSqlGroup = "GROUP BY L.SITE_NAME, R.HITS, R.SESSIONS";
$url_from = "L.SITE_NAME";
} else {
$find_group = "U";
}
break;
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("L.SITE_ID", $val, $match);
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$grby = $find_group == "U" || $find_group == "S" ? $find_group : "";
$strSqlOrder = "";
if (strlen($grby) <= 0) {
if ($by == "s_id") {
$strSqlOrder = " ORDER BY L.ID ";
} elseif ($by == "s_site_id") {
$strSqlOrder = " ORDER BY L.SITE_ID ";
} elseif ($by == "s_url_from") {
$strSqlOrder = " ORDER BY URL_FROM ";
} elseif ($by == "s_url_to") {
$strSqlOrder = " ORDER BY L.URL_TO ";
} elseif ($by == "s_date_hit") {
$strSqlOrder = " ORDER BY L.DATE_HIT ";
} elseif ($by == "s_session_id") {
$strSqlOrder = " ORDER BY L.SESSION_ID ";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY L.ID";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\t" . $url_from . " as URL_FROM,\n\t\t\t\t\tL.ID,\n\t\t\t\t\tL.SESSION_ID,\n\t\t\t\t\tL.SITE_ID,\n\t\t\t\t\t" . $DB->DateToCharFunction("L.DATE_HIT") . " DATE_HIT,\n\t\t\t\t\tL.URL_TO,\n\t\t\t\t\tL.URL_TO_404\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
} elseif (IsFiltered($strSqlSearch) || $grby == "U") {
if ($by == "s_url_from") {
$strSqlOrder = "ORDER BY URL_FROM";
} elseif ($by == "s_quantity") {
$strSqlOrder = "ORDER BY QUANTITY";
} elseif ($by == "s_average_hits") {
$strSqlOrder = "ORDER BY AVERAGE_HITS";
} else {
$by = "s_quantity";
$strSqlOrder = "ORDER BY QUANTITY";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tcount(L.ID) as COUNTER\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\t\tLEFT JOIN b_stat_referer R ON (R.ID = L.REFERER_ID)\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlGroup . "\n\t\t\t";
$c = $DB->Query($strSql, false, $err_mess . __LINE__);
$total = 0;
$arrCount = array();
while ($cr = $c->Fetch()) {
$total += intval($cr["COUNTER"]);
$arrCount[] = intval($cr["COUNTER"]);
}
if (count($arrCount) > 0) {
$max = max($arrCount);
}
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\t" . $url_from . " URL_FROM,\n\t\t\t\t\tcount(L.ID) QUANTITY,\n\t\t\t\t\t(count(L.ID)*100)/{$total} C_PERCENT,\n\t\t\t\t\tR.HITS/R.SESSIONS AVERAGE_HITS\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\t\tLEFT JOIN b_stat_referer R ON (R.ID = L.REFERER_ID)\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlGroup . "\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
} elseif ($grby == "S") {
if ($by == "s_url_from") {
$strSqlOrder = "ORDER BY URL_FROM";
} elseif ($by == "s_quantity") {
$strSqlOrder = "ORDER BY QUANTITY";
} elseif ($by == "s_average_hits") {
$strSqlOrder = "ORDER BY AVERAGE_HITS";
} else {
$by = "s_quantity";
$strSqlOrder = "ORDER BY QUANTITY";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "SELECT sum(R.SESSIONS) TOTAL, max(R.SESSIONS) MAX FROM b_stat_referer R";
$c = $DB->Query($strSql, false, $err_mess . __LINE__);
$cr = $c->Fetch();
$total = intval($cr["TOTAL"]);
$max = intval($cr["MAX"]);
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\tR.SITE_NAME URL_FROM,\n\t\t\t\t\tsum(R.SESSIONS) QUANTITY,\n\t\t\t\t\t(sum(R.SESSIONS)*100)/{$total} C_PERCENT,\n\t\t\t\t\tsum(R.HITS)/sum(R.SESSIONS) AVERAGE_HITS\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer R\n\t\t\t\tGROUP BY R.SITE_NAME\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
}
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch) || $group;
return $res;
}
public function GetList($aSort=array(), $arFilter=Array(), $getCommon=true)
{
global $DB;
$err_mess = (CAdminFilter::err_mess())."<br>Function: GetList<br>Line: ";
$arSqlSearch = Array();
if (is_array($arFilter))
{
foreach ($arFilter as $key => $val)
{
if (strlen($val)<=0 || $val=="NOT_REF")
continue;
switch(strtoupper($key))
{
case "ID":
$arSqlSearch[] = GetFilterQuery("F.ID",$val,"N");
break;
case "USER_ID":
if($getCommon)
$arSqlSearch[] = "F.USER_ID=".intval($val)." OR F.COMMON='Y'";
else
$arSqlSearch[] = "F.USER_ID = ".intval($val);
break;
case "FILTER_ID":
$arSqlSearch[] = GetFilterQuery("F.FILTER_ID", $val);
break;
case "NAME":
$arSqlSearch[] = GetFilterQuery("F.NAME", $val);
break;
case "FIELDS":
$arSqlSearch[] = GetFilterQuery("F.FIELDS", $val);
break;
case "COMMON":
$arSqlSearch[] = "F.COMMON = '".$DB->ForSql($val,1)."'";
break;
case "PRESET":
$arSqlSearch[] = "F.PRESET = '".$DB->ForSql($val,1)."'";
break;
case "LANGUAGE_ID":
$arSqlSearch[] = "F.LANGUAGE_ID = '".$DB->ForSql($val,2)."'";
break;
case "PRESET_ID":
$arSqlSearch[] = GetFilterQuery("F.PRESET_ID", $val);
break;
case "SORT":
$arSqlSearch[] = GetFilterQuery("F.SORT", $val);
break;
case "SORT_FIELD":
$arSqlSearch[] = GetFilterQuery("F.SORT_FIELD", $val);
break;
}
}
}
$sOrder = "";
foreach($aSort as $key=>$val)
{
$ord = (strtoupper($val) <> "ASC"? "DESC":"ASC");
switch (strtoupper($key))
{
case "ID": $sOrder .= ", F.ID ".$ord; break;
case "USER_ID": $sOrder .= ", F.USER_ID ".$ord; break;
case "FILTER_ID": $sOrder .= ", F.FILTER_ID ".$ord; break;
case "NAME": $sOrder .= ", F.NAME ".$ord; break;
case "FIELDS": $sOrder .= ", F.FIELDS ".$ord; break;
case "COMMON": $sOrder .= ", F.COMMON ".$ord; break;
case "PRESET": $sOrder .= ", F.PRESET ".$ord; break;
case "LANGUAGE_ID": $sOrder .= ", F.LANGUAGE_ID ".$ord; break;
case "PRESET_ID": $sOrder .= ", F.PRESET_ID ".$ord; break;
case "SORT": $sOrder .= ", F.SORT ".$ord; break;
case "SORT_FIELD": $sOrder .= ", F.SORT_FIELD ".$ord; break;
}
}
if (strlen($sOrder)<=0)
$sOrder = "F.ID ASC";
$strSqlOrder = " ORDER BY ".TrimEx($sOrder,",");
$strSqlSearch = GetFilterSqlSearch($arSqlSearch,"noFilterLogic");
$strSql = "
SELECT
F.ID, F.USER_ID, F.NAME, F.FILTER_ID, F.FIELDS, F.COMMON, F.PRESET, F.LANGUAGE_ID, F.PRESET_ID, F.SORT, F.SORT_FIELD
FROM
b_filters F
WHERE
".$strSqlSearch."
".$strSqlOrder;
$res = $DB->Query($strSql, false, $err_mess.__LINE__);
return $res;
}
/**
* <p>Метод производит выборку уведомлений с сортировкой и фильтрацией. Статичный метод.</p>
*
*
* @param array $arSort = array() Сортировка осуществляется по: <ul> <li> <b>ID</b> - идентификатору
* сообщения;</li> <li> <b>MODULE_ID</b> - идентификатору модуля, к которому
* относится сообщение.</li> </ul>
*
* @param array $arFilter = array() Фильтрация осуществляется по: <ul> <li> <b>ID</b> - идентификатору
* сообщения;</li> <li> <b>MODULE_ID</b> - идентификатору модуля, к которому
* относится сообщение;</li> <li> <b>TAG</b> - тегу;</li> <li> <b>ENABLE_CLOSE</b>-
* разрешению на ручное закрытие.</li> </ul>
*
* @return mixed <p>Возвращается экземляр класса <a
* href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> для дальней
* обработки.</p>
*
* <h4>Example</h4>
* <pre>
* CAdminNotify::GetList(array('ID' => 'DESC'), array('MODULE_ID'=>'main'));
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a> </li> </ul><a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/main/reference/cadminnotify/getlist.php
* @author Bitrix
*/
public static function GetList($arSort = array(), $arFilter = array())
{
global $DB;
$arSqlSearch = array();
$strSqlSearch = '';
$err_mess = self::err_mess() . '<br />Function: GetList<br />Line: ';
if (!is_array($arFilter)) {
$arFilter = array();
}
if (!isset($arFilter['LID'])) {
$arFilter['LID'] = LANGUAGE_ID;
}
if (!isset($arFilter['PUBLIC_SECTION'])) {
$arFilter['PUBLIC_SECTION'] = 'N';
}
$strFrom = '';
$strSelect = "AN.ID, AN.MODULE_ID, AN.TAG, AN.MESSAGE, AN.ENABLE_CLOSE, AN.PUBLIC_SECTION";
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
for ($i = 0, $ic = count($filter_keys); $i < $ic; $i++) {
$val = $arFilter[$filter_keys[$i]];
if (strlen($val) <= 0 || $val == 'NOT_REF') {
continue;
}
switch (strtoupper($filter_keys[$i])) {
case 'ID':
$arSqlSearch[] = GetFilterQuery('AN.ID', $val, 'N');
break;
case 'MODULE_ID':
$arSqlSearch[] = GetFilterQuery('AN.MODULE_ID', $val);
break;
case 'TAG':
$arSqlSearch[] = GetFilterQuery('AN.TAG', $val);
break;
case 'MESSAGE':
$arSqlSearch[] = GetFilterQuery('AN.MESSAGE', $val);
break;
case 'ENABLE_CLOSE':
$arSqlSearch[] = $val == 'Y' ? "AN.ENABLE_CLOSE='Y'" : "AN.ENABLE_CLOSE='N'";
break;
case 'LID':
$strSelect .= ", ANL.MESSAGE as MESSAGE_LANG";
$strFrom = 'LEFT JOIN b_admin_notify_lang ANL ON (AN.ID = ANL.NOTIFY_ID AND ANL.LID = \'' . $DB->ForSQL($val) . '\')';
break;
case 'PUBLIC_SECTION':
$arSqlSearch[] = $val == 'Y' ? "AN.PUBLIC_SECTION='Y'" : "AN.PUBLIC_SECTION='N'";
}
}
}
$sOrder = '';
foreach ($arSort as $key => $val) {
$ord = strtoupper($val) != 'ASC' ? 'DESC' : 'ASC';
switch (strtoupper($key)) {
case 'ID':
$sOrder .= ', AN.ID ' . $ord;
break;
case 'MODULE_ID':
$sOrder .= ', AN.MODULE_ID ' . $ord;
break;
case 'ENABLE_CLOSE':
$sOrder .= ', AN.ENABLE_CLOSE ' . $ord;
break;
}
}
if (strlen($sOrder) <= 0) {
$sOrder = 'AN.ID DESC';
}
$strSqlOrder = ' ORDER BY ' . TrimEx($sOrder, ',');
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "SELECT " . $strSelect . " FROM b_admin_notify AN " . $strFrom . " WHERE " . $strSqlSearch . " " . $strSqlOrder;
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
return $res;
}
public static function GetList($aSort = array(), $arFilter = array(), $arNavStartParams = false)
{
global $DB;
$arSqlSearch = array();
$from1 = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (!is_array($val)) {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
switch (strtoupper($key)) {
case "ID":
$arSqlSearch[] = GetFilterQuery("S.ID", $val, "N");
break;
case "EMAIL":
$arSqlSearch[] = GetFilterQuery("S.EMAIL", $val, "Y", array("@", ".", "_"));
break;
case "UPDATE_1":
$arSqlSearch[] = "S.DATE_UPDATE>=" . $DB->CharToDateFunction($val);
break;
case "UPDATE_2":
$arSqlSearch[] = "S.DATE_UPDATE<=" . $DB->CharToDateFunction($val . " 23:59:59");
break;
case "INSERT_1":
$arSqlSearch[] = "S.DATE_INSERT>=" . $DB->CharToDateFunction($val);
break;
case "INSERT_2":
$arSqlSearch[] = "S.DATE_INSERT<=" . $DB->CharToDateFunction($val . " 23:59:59");
break;
case "USER":
$arSqlSearch[] = GetFilterQuery("U.ID, U.LOGIN, U.NAME, U.LAST_NAME", $val);
break;
case "USER_ID":
$arSqlSearch[] = GetFilterQuery("S.USER_ID", $val, "N");
break;
case "ANONYMOUS":
$arSqlSearch[] = $val == "Y" ? "(S.USER_ID = 0 or S.USER_ID is null)" : "(S.USER_ID > 0 and S.USER_ID is not null)";
break;
case "CONFIRMED":
$arSqlSearch[] = $val == "Y" ? "S.CONFIRMED='Y'" : "S.CONFIRMED='N'";
break;
case "ACTIVE":
$arSqlSearch[] = $val == "Y" ? "S.ACTIVE='Y'" : "S.ACTIVE='N'";
break;
case "FORMAT":
$arSqlSearch[] = $val == "text" ? "S.FORMAT='text'" : "S.FORMAT='html'";
break;
case "RUBRIC":
case "RUBRIC_MULTI":
case "DISTRIBUTION":
if (is_array($val)) {
$val = implode(" | ", $val);
}
if (strlen($val) > 0) {
$from1 = " INNER JOIN b_subscription_rubric SR ON (SR.SUBSCRIPTION_ID=S.ID) ";
$arSqlSearch[] = GetFilterQuery("SR.LIST_RUBRIC_ID", $val, "N");
} else {
$arSqlSearch[] = " 1=0 ";
}
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$arOrder = array();
foreach ($aSort as $by => $ord) {
$by = strtoupper($by);
$ord = strtoupper($ord) != "ASC" ? "DESC" : "ASC";
switch ($by) {
case "ID":
$arOrder[$by] = "S.ID " . $ord;
break;
case "DATE_INSERT":
$arOrder[$by] = "S.DATE_INSERT " . $ord;
break;
case "DATE_UPDATE":
$arOrder[$by] = "S.DATE_UPDATE " . $ord;
break;
case "DATE_CONFIRM":
$arOrder[$by] = "S.DATE_CONFIRM " . $ord;
break;
case "ACT":
$arOrder[$by] = "S.ACTIVE " . $ord;
break;
case "CONF":
$arOrder[$by] = "S.CONFIRMED " . $ord;
break;
case "EMAIL":
$arOrder[$by] = "S.EMAIL " . $ord;
break;
case "FMT":
$arOrder[$by] = "S.FORMAT " . $ord;
break;
case "USER":
$arOrder[$by] = "S.USER_ID " . $ord;
break;
case "CONFIRM_CODE":
$arOrder[$by] = "S.CONFIRM_CODE " . $ord;
break;
}
}
if (count($arOrder) <= 0) {
$arOrder["ID"] = "S.ID DESC";
}
if (is_array($arNavStartParams)) {
$strSql = "\n\t\t\t\tSELECT count(" . ($from1 != "" ? "DISTINCT S.ID" : "'x'") . ") as C\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch;
$res_cnt = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res_cnt = $res_cnt->Fetch();
$cnt = $res_cnt["C"];
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tS.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_UPDATE") . " DATE_UPDATE,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_INSERT") . " DATE_INSERT,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_CONFIRM") . " DATE_CONFIRM,\n\t\t\t\t\tU.LOGIN USER_LOGIN,\n\t\t\t\t\tU.NAME USER_NAME,\n\t\t\t\t\tU.LAST_NAME USER_LAST_NAME\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\t" . ($from1 != "" ? "GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME" : "") . "\n\t\t\t\tORDER BY " . implode(", ", $arOrder);
$res = new CDBResult();
$res->NavQuery($strSql, $cnt, $arNavStartParams);
$res->is_filtered = IsFiltered($strSqlSearch);
return $res;
} else {
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tS.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_UPDATE") . " DATE_UPDATE,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_INSERT") . " DATE_INSERT,\n\t\t\t\t\t" . $DB->DateToCharFunction("S.DATE_CONFIRM") . " DATE_CONFIRM,\n\t\t\t\t\tU.LOGIN USER_LOGIN,\n\t\t\t\t\tU.NAME USER_NAME,\n\t\t\t\t\tU.LAST_NAME USER_LAST_NAME\n\t\t\t\tFROM\n\t\t\t\t\tb_subscription S\n\t\t\t\t\tLEFT JOIN b_user U ON (S.USER_ID=U.ID)\n\t\t\t\t\t{$from1}\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\t" . ($from1 != "" ? "GROUP BY S.ID, S.USER_ID, S.ACTIVE, S.EMAIL, S.FORMAT, S.CONFIRM_CODE, S.CONFIRMED, S.DATE_CONFIRM, S.DATE_UPDATE, S.DATE_INSERT, U.LOGIN, U.NAME, U.LAST_NAME" : "") . "\n\t\t\t\tORDER BY " . implode(", ", $arOrder);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->is_filtered = IsFiltered($strSqlSearch);
return $res;
}
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#session">сессий</a> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителей</a>, проявивших активность (совершивших <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хит</a>) на сайте за определённый интервал времени.</p>
*
*
*
*
* @param function $GetList Ссылка на переменную, которая после выполнения функции будет
* содержать количество <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#online">посетителей в online</a>.
*
*
*
* @param (&$guest_coun $t Ссылка на переменную, которая после выполнения функции будет
* содержать количество сессий посетителей в online.
*
*
*
* @param &$session_coun $t
*
*
*
* @param $arOrde $r = Array()
*
*
*
* @param $arFilte $r = Array())
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* // получим список записей
* $rs = <b>CUserOnline::GetList</b>($guest_counter, $session_counter);
*
* echo "Количество посетителей в онлайн: ".$guest_counter;
* echo "Количество сессий в онлайн: ".$session_counter;
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://www.1c-bitrix.ru/user_help/statistic/users_online.php">Отчет "Кто на сайте"</a>
* </li> </ul><a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cuseronline/getlist.php
* @author Bitrix
*/
public static function GetList(&$guest_count, &$session_count, $arOrder=Array(), $arFilter=Array())
{
$DB = CDatabase::GetModuleConnection('statistic');
$err_mess = "File: ".__FILE__."<br>Line: ";
$interval = intval(COption::GetOptionString("statistic", "ONLINE_INTERVAL"));
$arSqlSearch = Array();
$strSqlSearch = "";
if (is_array($arFilter))
{
foreach ($arFilter as $key => $val)
{
if(is_array($val))
{
if(count($val) <= 0)
continue;
}
else
{
if( (strlen($val) <= 0) || ($val === "NOT_REF") )
continue;
}
$match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch($key)
{
case "INTERVAL":
$interval = intval($val);
break;
case "ID":
case "GUEST_ID":
case "ADV_ID":
case "STOP_LIST_ID":
case "USER_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("S.".$key,$val,$match);
break;
case "COUNTRY_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("S.COUNTRY_ID",$val,$match);
break;
case "IP":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.IP_LAST",$val,$match,array("."));
break;
case "REGISTERED":
$arSqlSearch[] = ($val=="Y") ? "S.USER_ID>0" : "(S.USER_ID<=0 or S.USER_ID is null)";
break;
case "HITS1":
$arSqlSearch[] = "S.HITS>='".intval($val)."'";
break;
case "HITS2":
$arSqlSearch[] = "S.HITS<='".intval($val)."'";
break;
case "ADV":
if ($val=="Y")
$arSqlSearch[] = "(S.ADV_ID>0 and S.ADV_ID is not null)";
elseif ($val=="N")
$arSqlSearch[] = "(S.ADV_ID<=0 or S.ADV_ID is null)";
break;
case "REFERER1":
case "REFERER2":
case "REFERER3":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.".$key, $val, $match);
break;
case "STOP":
$arSqlSearch[] = ($val=="Y") ? "S.STOP_LIST_ID>0" : "(S.STOP_LIST_ID<=0 or S.STOP_LIST_ID is null)";
break;
case "COUNTRY":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.NAME", $val, $match);
$from2 = "INNER JOIN b_stat_country C ON (C.ID = S.COUNTRY_ID)";
break;
case "LAST_SITE_ID":
$arSqlSearch[] = GetFilterQuery("S.".$key, $val, "N");
break;
case "URL_LAST":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.".$key,$val,$match,array("/","\\",".","?","#",":"));
break;
case "FIRST_URL_FROM":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.".$key,$val,$match,array("/","\\",".","?","#",":"));
break;
case "ADV_BACK":
case "NEW_GUEST":
case "URL_LAST_404":
case "URL_TO_404":
case "USER_AUTH":
$arSqlSearch[] = ($val=="Y") ? "S.".$key."='Y'" : "S.".$key."='N'";
break;
case "FAVORITES":
$arSqlSearch[] = ($val=="Y") ? "G.".$key."='Y'" : "G.".$key."='N'";
break;
case "USER":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = "ifnull(S.USER_ID,0)>0";
$arSqlSearch[] = GetFilterQuery("S.USER_ID,A.LOGIN,A.LAST_NAME,A.NAME", $val, $match);
$from1 = "LEFT JOIN b_user A ON (A.ID = S.USER_ID)";
$select = " , A.LOGIN, concat(ifnull(A.NAME,''),' ',ifnull(A.LAST_NAME,'')) USER_NAME";
break;
}
}
}
if (!is_array($arOrder))
$arOrder = Array("s_id" => "desc");
foreach($arOrder as $by=>$order)
{
$by = strtolower($by);
$order = strtolower($order);
if ($order!="asc")
$order = "desc";
}
if ($by == "s_id") $strSqlOrder = "ORDER BY S.ID";
elseif ($by == "s_session_time") $strSqlOrder = "ORDER BY SESSION_TIME";
elseif ($by == "s_date_first") $strSqlOrder = "ORDER BY S.DATE_FIRST";
elseif ($by == "s_date_last") $strSqlOrder = "ORDER BY S.DATE_LAST";
elseif ($by == "s_user_id") $strSqlOrder = "ORDER BY S.USER_ID";
elseif ($by == "s_guest_id") $strSqlOrder = "ORDER BY S.GUEST_ID";
elseif ($by == "s_ip") $strSqlOrder = "ORDER BY S.IP_LAST";
elseif ($by == "s_hits") $strSqlOrder = "ORDER BY S.HITS ";
elseif ($by == "s_adv_id") $strSqlOrder = "ORDER BY S.ADV_ID ";
elseif ($by == "s_country_id") $strSqlOrder = "ORDER BY S.COUNTRY_ID ";
elseif ($by == "s_url_last") $strSqlOrder = "ORDER BY S.URL_LAST ";
elseif ($by == "s_url_to") $strSqlOrder = "ORDER BY S.URL_TO ";
else
{
$by = "s_id";
$strSqlOrder = "ORDER BY S.ID";
}
if ($order!="asc")
{
$strSqlOrder .= " desc ";
$order="desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "
SELECT
S.ID,
S.ADV_ID, S.REFERER1, S.REFERER2, S.REFERER3, S.ADV_BACK,
S.LAST_SITE_ID, S.URL_LAST, S.URL_LAST_404, S.IP_LAST, S.HITS, S.USER_AUTH,
S.STOP_LIST_ID, S.GUEST_ID, G.FAVORITES, G.LAST_USER_ID,
UNIX_TIMESTAMP(S.DATE_LAST) - UNIX_TIMESTAMP(S.DATE_FIRST) SESSION_TIME,
".$DB->DateToCharFunction("S.DATE_LAST")." DATE_LAST,
if(G.SESSIONS<=1,'Y','N') NEW_GUEST,
G.FIRST_URL_FROM,
G.FIRST_SITE_ID,
S.URL_FROM,
S.COUNTRY_ID,
C.NAME COUNTRY_NAME,
CITY.REGION REGION_NAME,
S.CITY_ID,
CITY.NAME CITY_NAME
FROM
b_stat_session S
INNER JOIN b_stat_guest G ON (G.ID = S.GUEST_ID)
INNER JOIN b_stat_country C ON (C.ID = S.COUNTRY_ID)
".$from1."
LEFT JOIN b_stat_city CITY ON (CITY.ID = S.CITY_ID)
WHERE
S.DATE_STAT >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and S.DATE_LAST > DATE_ADD(now(), INTERVAL - ".$interval." SECOND)
and ".$strSqlSearch."
".$strSqlOrder."
";
$arr = array();
$arrG = array();
$rs = $DB->Query($strSql, false, $err_mess.__LINE__);
while($ar = $rs->Fetch())
{
$arr[] = $ar;
$arrG[$ar["GUEST_ID"]] = $ar["GUEST_ID"];
}
$guest_count = count($arrG);
$session_count = count($arr);
$rs = new CDBResult;
$rs->InitFromArray($arr);
return $rs;
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хитов</a> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителей</a>. Число выводимых строк определяется в поле <b>Максимальное кол-во показываемых записей в таблицах</b> в <a href="http://dev.1c-bitrix.ru/user_help/statistic/settings.php" >настройках модуля</a> Веб-аналитика.</p>
*
*
* @param string &$by = "s_id" Поле для сортировки. Возможные значения: <ul> <li> <b>s_id</b> - ID хита; </li>
* <li> <b>s_site_id</b> - ID сайта; </li> <li> <b>s_session_id</b> - ID <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#session">сессии</a>; </li> <li> <b>s_date_hit</b> -
* время хита; </li> <li> <b>s_user_id</b> - ID <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#user">пользователя</a> под которым
* был авторизован посетитель (в момент хита или до того); </li> <li>
* <b>s_guest_id</b> - ID <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителя</a>;
* </li> <li> <b>s_ip</b> - <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#ip">IP адрес</a>
* посетителя; </li> <li> <b>s_url</b> - страница хита; </li> <li> <b>s_country_id</b> - ID
* страны посетителя. </li> </ul>
*
* @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по
* возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul>
*
* @param array $filter = array() Массив для фильтрации результирующего списка. В массиве
* допустимы следующие ключи: <ul> <li> <b>ID</b>* - ID хита; </li> <li> <b>ID_EXACT_MATCH</b> -
* если значение равно "N", то при фильтрации по <b>ID</b> будет искаться
* вхождение; </li> <li> <b>GUEST_ID</b>* - ID посетителя; </li> <li> <b>GUEST_ID_EXACT_MATCH</b> -
* если значение равно "N", то при фильтрации по <b>GUEST_ID</b> будет
* искаться вхождение; </li> <li> <b>NEW_GUEST</b> - флаг "новый посетитель",
* возможные значения: <ul> <li> <b>Y</b> - посетитель впервые на портале; </li>
* <li> <b>N</b> - посетитель уже посещал ранее портал. </li> </ul> </li> <li>
* <b>SESSION_ID</b>* - ID сессии; </li> <li> <b>SESSION_ID_EXACT_MATCH</b> - если значение равно
* "N", то при фильтрации по <b>SESSION_ID</b> будет искаться вхождение; </li> <li>
* <b>STOP_LIST_ID</b>* - ID записи <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#stop_list">стоп-листа</a> под которую
* попал посетитель (если это имело место быть); </li> <li>
* <b>STOP_LIST_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по
* <b>STOP_LIST_ID</b> будет искаться вхождение; </li> <li> <b>URL</b>* - страница хита;
* </li> <li> <b>URL_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>URL</b> будет искаться точное совпадение; </li> <li> <b>URL_404</b> - была ли <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#404">404 ошибка</a> на странице хита <ul>
* <li> <b>Y</b> - была; </li> <li> <b>N</b> - не было. </li> </ul> </li> <li> <b>USER</b>* - ID, логин,
* имя, фамилия пользователя под которым был авторизован посетитель
* в момент хита или до него; </li> <li> <b>USER_EXACT_MATCH</b> - если значение равно
* "Y", то при фильтрации по <b>USER</b> будет искаться точное совпадение;
* </li> <li> <b>REGISTERED</b> - флаг "был ли авторизован посетитель в момент
* хита или до этого", возможные значения: <ul> <li> <b>Y</b> - был; </li> <li> <b>N</b>
* - не был. </li> </ul> </li> <li> <b>DATE_1</b> - начальное значение интервала даты
* хита; </li> <li> <b>DATE_2</b> - конечное значение интервала даты хита; </li> <li>
* <b>IP</b>* - IP адрес посетителя в момент хита; </li> <li> <b>IP_EXACT_MATCH</b> - если
* значение равно "Y", то при фильтрации по <b>IP</b> будет искаться
* точное совпадение; </li> <li> <b>USER_AGENT</b>* - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#user_agent">UserAgent</a> посетителя в
* момент хита; </li> <li> <b>USER_AGENT_EXACT_MATCH</b> - если значение равно "Y", то при
* фильтрации по <b>USER_AGENT</b> будет искаться точное совпадение; </li> <li>
* <b>COUNTRY_ID</b>* - ID страны посетителя в момент хита; </li> <li>
* <b>COUNTRY_ID_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>COUNTRY_ID</b> будет искаться точное совпадение; </li> <li> <b>COUNTRY</b>* -
* название страны; </li> <li> <b>COUNTRY_EXACT_MATCH</b> - если значение равно "Y", то
* при фильтрации по <b>COUNTRY</b> будет искаться точное совпадение; </li>
* <li> <b>COOKIE</b>* - содержимое <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#cookie">Cookie</a> в момент хита; </li> <li>
* <b>COOKIE_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>COOKIE</b> будет искаться точное совпадение; </li> <li> <b>STOP</b> - <ul> <li> <b>Y</b>
* - был; </li> <li> <b>N</b> - не был. </li> </ul> </li> <li> <b>SITE_ID</b>* - ID сайта; </li> <li>
* <b>SITE_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по
* <b>SITE_ID</b> будет искаться вхождение. </li> </ul> <br> * - допускается <a
* href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная логика</a>
*
* @param bool &$is_filtered Флаг отфильтрованности списка хитов. Если значение равно "true", то
* список был отфильтрован.
*
* @return CDBResult
*
* <h4>Example</h4>
* <pre>
* <?
* // выберем хиты сессии #1056
* $arFilter = array(
* "SESSION_ID" => 1056
* );
*
* // получим список записей
* $rs = <b>CHit::GetList</b>(
* ($by = "s_id"),
* ($order = "desc"),
* $arFilter,
* $is_filtered
* );
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">Термин "Хит"</a> </li> </ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/chit/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "ID":
case "GUEST_ID":
case "SESSION_ID":
case "STOP_LIST_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("H." . $key, $val, $match);
break;
case "URL":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.URL", $val, $match, array("/", "\\", ".", "?", "#", ":"));
break;
case "URL_404":
case "NEW_GUEST":
$arSqlSearch[] = $val == "Y" ? "H." . $key . "='Y'" : "H." . $key . "='N'";
break;
case "REGISTERED":
$arSqlSearch[] = $val == "Y" ? "H.USER_ID>0" : "(H.USER_ID<=0 or H.USER_ID is null)";
break;
case "DATE_1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT >= " . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE_2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT < " . CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
}
break;
case "IP":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.IP", $val, $match, array("."));
break;
case "USER_AGENT":
case "COUNTRY_ID":
case "CITY_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H." . $key, $val, $match);
break;
case "COOKIE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.COOKIES", $val, $match);
break;
case "USER":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = $DB->IsNull("H.USER_ID", "0") . ">0";
$arSqlSearch[] = GetFilterQuery("H.USER_ID,A.LOGIN,A.LAST_NAME,A.NAME", $val, $match);
$select = ", A.LOGIN, " . $DB->Concat($DB->IsNull("A.NAME", "''"), "' '", $DB->IsNull("A.LAST_NAME", "''")) . " USER_NAME";
$from1 = "LEFT JOIN b_user A ON (A.ID = H.USER_ID)";
break;
case "COUNTRY":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.NAME", $val, $match);
$from2 = "INNER JOIN b_stat_country C ON (C.ID = H.COUNTRY_ID)";
break;
case "REGION":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("CITY.REGION", $val, $match);
break;
case "CITY":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("CITY.NAME", $val, $match);
break;
case "STOP":
$arSqlSearch[] = $val == "Y" ? "H.STOP_LIST_ID>0" : "(H.STOP_LIST_ID<=0 or H.STOP_LIST_ID is null)";
break;
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("H.SITE_ID", $val, $match);
break;
}
}
}
if ($by == "s_id") {
$strSqlOrder = "ORDER BY H.ID";
} elseif ($by == "s_site_id") {
$strSqlOrder = "ORDER BY H.SITE_ID";
} elseif ($by == "s_session_id") {
$strSqlOrder = "ORDER BY H.SESSION_ID";
} elseif ($by == "s_date_hit") {
$strSqlOrder = "ORDER BY H.DATE_HIT";
} elseif ($by == "s_user_id") {
$strSqlOrder = "ORDER BY H.USER_ID";
} elseif ($by == "s_guest_id") {
$strSqlOrder = "ORDER BY H.GUEST_ID";
} elseif ($by == "s_ip") {
$strSqlOrder = "ORDER BY H.IP";
} elseif ($by == "s_url") {
$strSqlOrder = "ORDER BY H.URL ";
} elseif ($by == "s_country_id") {
$strSqlOrder = "ORDER BY H.COUNTRY_ID ";
} elseif ($by == "s_region_name") {
$strSqlOrder = "ORDER BY CITY.REGION ";
} elseif ($by == "s_city_id") {
$strSqlOrder = "ORDER BY H.CITY_ID ";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY H.ID";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT /*TOP*/\n\t\t\t\tH.ID,\n\t\t\t\tH.SESSION_ID,\n\t\t\t\tH.GUEST_ID,\n\t\t\t\tH.NEW_GUEST,\n\t\t\t\tH.USER_ID,\n\t\t\t\tH.USER_AUTH,\n\t\t\t\tH.URL,\n\t\t\t\tH.URL_404,\n\t\t\t\tH.URL_FROM,\n\t\t\t\tH.IP,\n\t\t\t\tH.METHOD,\n\t\t\t\tH.COOKIES,\n\t\t\t\tH.USER_AGENT,\n\t\t\t\tH.STOP_LIST_ID,\n\t\t\t\tH.COUNTRY_ID,\n\t\t\t\tH.CITY_ID,\n\t\t\t\tCITY.REGION REGION_NAME,\n\t\t\t\tCITY.NAME CITY_NAME,\n\t\t\t\tH.SITE_ID,\n\t\t\t\t" . $DB->DateToCharFunction("H.DATE_HIT") . " DATE_HIT\n\t\t\t\t" . $select . "\n\t\t\tFROM\n\t\t\t\tb_stat_hit H\n\t\t\t\tLEFT JOIN b_stat_city CITY ON (CITY.ID = H.CITY_ID)\n\t\t\t" . $from1 . "\n\t\t\t" . $from2 . "\n\t\t\tWHERE\n\t\t\t" . $strSqlSearch . "\n\t\t\t" . $strSqlOrder . "\n\t\t";
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $res;
}
public static function GetGraphArray($arFilter, &$arLegend, $sort = false, $top = 0)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
global $arCityColor;
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "COUNTRY_ID":
if ($val != "NOT_REF") {
$arSqlSearch[] = GetFilterQuery("C.COUNTRY_ID", $val, "N");
}
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "D.DATE_STAT>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "D.DATE_STAT<=" . $DB->CharToDateFunction($val . " 23:59:59", "FULL");
}
break;
}
}
}
$arrDays = array();
$arLegend = array();
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . " DATE_STAT,\n\t\t\t\t" . $DB->DateFormatToDB("DD", "D.DATE_STAT") . " DAY,\n\t\t\t\t" . $DB->DateFormatToDB("MM", "D.DATE_STAT") . " MONTH,\n\t\t\t\t" . $DB->DateFormatToDB("YYYY", "D.DATE_STAT") . " YEAR,\n\t\t\t\tD.CITY_ID,\n\t\t\t\tD.SESSIONS,\n\t\t\t\tD.NEW_GUESTS,\n\t\t\t\tD.HITS,\n\t\t\t\tD.C_EVENTS,\n\t\t\t\tC.NAME,\n\t\t\t\tC.SESSIONS TOTAL_SESSIONS,\n\t\t\t\tC.NEW_GUESTS TOTAL_NEW_GUESTS,\n\t\t\t\tC.HITS TOTAL_HITS,\n\t\t\t\tC.C_EVENTS TOTAL_C_EVENTS\n\t\t\tFROM\n\t\t\t\tb_stat_city_day D\n\t\t\t\tINNER JOIN b_stat_city C ON (C.ID = D.CITY_ID)\n\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\tORDER BY\n\t\t\t\tD.DATE_STAT, D.CITY_ID\n\t\t";
$rsD = $DB->Query($strSql, false, $err_mess . __LINE__);
while ($arD = $rsD->Fetch()) {
$arrDays[$arD["DATE_STAT"]]["D"] = $arD["DAY"];
$arrDays[$arD["DATE_STAT"]]["M"] = $arD["MONTH"];
$arrDays[$arD["DATE_STAT"]]["Y"] = $arD["YEAR"];
$arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["SESSIONS"] = $arD["SESSIONS"];
$arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["NEW_GUESTS"] = $arD["NEW_GUESTS"];
$arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["HITS"] = $arD["HITS"];
$arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["C_EVENTS"] = $arD["C_EVENTS"];
$arLegend[$arD["CITY_ID"]]["CITY_ID"] = intval($arD["CITY_ID"]);
$arLegend[$arD["CITY_ID"]]["NAME"] = $arD["NAME"];
$arLegend[$arD["CITY_ID"]]["SESSIONS"] += $arD["SESSIONS"];
$arLegend[$arD["CITY_ID"]]["NEW_GUESTS"] += $arD["NEW_GUESTS"];
$arLegend[$arD["CITY_ID"]]["HITS"] += $arD["HITS"];
$arLegend[$arD["CITY_ID"]]["C_EVENTS"] += $arD["C_EVENTS"];
$arLegend[$arD["CITY_ID"]]["TOTAL_SESSIONS"] = $arD["TOTAL_SESSIONS"];
$arLegend[$arD["CITY_ID"]]["TOTAL_NEW_GUESTS"] = $arD["TOTAL_NEW_GUESTS"];
$arLegend[$arD["CITY_ID"]]["TOTAL_HITS"] = $arD["TOTAL_HITS"];
$arLegend[$arD["CITY_ID"]]["TOTAL_C_EVENTS"] = $arD["TOTAL_C_EVENTS"];
}
if ($sort) {
CStatisticSort::Sort($arLegend, $sort);
}
if ($top) {
$totals = array("CITY_ID" => 0, "NAME" => GetMessage("STAT_CITY_OTHER"), "SESSIONS" => 0, "NEW_GUESTS" => 0, "HITS" => 0, "C_EVENTS" => 0, "TOTAL_SESSIONS" => 0, "TOTAL_NEW_GUESTS" => 0, "TOTAL_HITS" => 0, "TOTAL_C_EVENTS" => 0);
$i = 0;
while (count($arLegend) > $top) {
$i++;
$tail = array_pop($arLegend);
$totals["SESSIONS"] += $tail["SESSIONS"];
$totals["NEW_GUESTS"] += $tail["NEW_GUESTS"];
$totals["HITS"] += $tail["HITS"];
$totals["C_EVENTS"] += $tail["C_EVENTS"];
$totals["TOTAL_SESSIONS"] += $tail["TOTAL_SESSIONS"];
$totals["TOTAL_NEW_GUESTS"] += $tail["TOTAL_NEW_GUESTS"];
$totals["TOTAL_HITS"] += $tail["TOTAL_HITS"];
$totals["TOTAL_C_EVENTS"] += $tail["TOTAL_C_EVENTS"];
}
if ($i) {
$arLegend[0] = $totals;
}
foreach ($arrDays as $DATE_STAT => $arDate) {
foreach ($arDate as $CITY_ID => $arCity) {
if (intval($CITY_ID) > 0) {
if (!array_key_exists($CITY_ID, $arLegend)) {
$arrDays[$DATE_STAT][0]["CITY_ID"] = 0;
$arrDays[$DATE_STAT][0]["NAME"] = GetMessage("STAT_CITY_OTHER");
$arrDays[$DATE_STAT][0]["SESSIONS"] += $arCity["SESSIONS"];
$arrDays[$DATE_STAT][0]["NEW_GUESTS"] += $arCity["NEW_GUESTS"];
$arrDays[$DATE_STAT][0]["HITS"] += $arCity["HITS"];
$arrDays[$DATE_STAT][0]["C_EVENTS"] += $arCity["C_EVENTS"];
unset($arrDays[$DATE_STAT][$CITY_ID]);
}
}
}
}
}
$total = count($arLegend);
foreach ($arLegend as $key => $arr) {
if (strlen($arCountryColor[$key]) > 0) {
$color = $arCountryColor[$key];
} else {
$color = GetNextRGB($color_getnext, $total);
$color_getnext = $color;
}
$arr["COLOR"] = $color;
$arLegend[$key] = $arr;
}
return $arrDays;
}
function GetList($aSort = array(), $arFilter = array())
{
global $DB;
$this->LAST_ERROR = "";
$arSqlSearch = array();
$arSqlSearch_h = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (!is_array($val) && (strlen($val) <= 0 || $val == "NOT_REF")) {
continue;
}
switch (strtoupper($key)) {
case "MSG_CHARSET":
$arSqlSearch[] = "P.MSG_CHARSET = '" . $DB->ForSql($val) . "'";
break;
case "ID":
$arSqlSearch[] = GetFilterQuery("P.ID", $val, "N");
break;
case "TIMESTAMP_1":
if ($DB->IsDate($val)) {
$arSqlSearch[] = "P.TIMESTAMP_X>=" . $DB->CharToDateFunction($val, "SHORT");
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_TIMESTAMP_FROM") . "<br>";
}
break;
case "TIMESTAMP_2":
if ($DB->IsDate($val)) {
$arSqlSearch[] = "P.TIMESTAMP_X<DATE_ADD(" . $DB->CharToDateFunction($val, "SHORT") . ",INTERVAL 1 DAY)";
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_TIMESTAMP_TILL") . "<br>";
}
break;
case "DATE_SENT_1":
if ($DB->IsDate($val)) {
$arSqlSearch[] = "P.DATE_SENT>=" . $DB->CharToDateFunction($val, "SHORT");
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_DATE_SENT_FROM") . "<br>";
}
break;
case "DATE_SENT_2":
if ($DB->IsDate($val)) {
$arSqlSearch[] = "P.DATE_SENT<DATE_ADD(" . $DB->CharToDateFunction($val, "SHORT") . ",INTERVAL 1 DAY)";
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_DATE_SENT_TILL") . "<br>";
}
break;
case "STATUS":
$arSqlSearch_h[] = GetFilterQuery("STATUS_TITLE, P.STATUS", $val);
break;
case "STATUS_ID":
$arSqlSearch[] = GetFilterQuery("P.STATUS", $val, "N");
break;
case "SUBJECT":
$arSqlSearch[] = GetFilterQuery("P.SUBJECT", $val);
break;
case "FROM":
$arSqlSearch[] = GetFilterQuery("P.FROM_FIELD", $val, "Y", array("@", "_", "."));
break;
case "TO":
$r = GetFilterQuery("PE.EMAIL", $val, "Y", array("@", "_", "."));
if (strlen($r) > 0) {
$arSqlSearch[] = "EXISTS (SELECT * FROM b_posting_email PE WHERE PE.POSTING_ID=P.ID AND PE.STATUS='N' AND " . $r . ")";
}
break;
case "BODY_TYPE":
$arSqlSearch[] = $val == "html" ? "P.BODY_TYPE='html'" : "P.BODY_TYPE='text'";
break;
case "RUB_ID":
if (is_array($val) && count($val) > 0) {
$rub_id = array();
foreach ($val as $i => $v) {
$v = intval($v);
if ($v > 0) {
$rub_id[$v] = $v;
}
}
if (count($rub_id)) {
$arSqlSearch[] = "EXISTS (SELECT * from b_posting_rubric PR WHERE PR.POSTING_ID = P.ID AND PR.LIST_RUBRIC_ID in (" . implode(", ", $rub_id) . "))";
}
}
break;
case "BODY":
$arSqlSearch[] = GetFilterQuery("P.BODY", $val);
break;
case "AUTO_SEND_TIME_1":
if ($DB->IsDate($val, false, false, "FULL")) {
$arSqlSearch[] = "(P.AUTO_SEND_TIME is not null and P.AUTO_SEND_TIME>=" . $DB->CharToDateFunction($val, "FULL") . " )";
} elseif ($DB->IsDate($val, false, false, "SHORT")) {
$arSqlSearch[] = "(P.AUTO_SEND_TIME is not null and P.AUTO_SEND_TIME>=" . $DB->CharToDateFunction($val, "SHORT") . " )";
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_AUTO_FROM") . "<br>";
}
break;
case "AUTO_SEND_TIME_2":
if ($DB->IsDate($val, false, false, "FULL")) {
$arSqlSearch[] = "(P.AUTO_SEND_TIME is not null and P.AUTO_SEND_TIME<=" . $DB->CharToDateFunction($val, "FULL") . " )";
} elseif ($DB->IsDate($val, false, false, "SHORT")) {
$arSqlSearch[] = "(P.AUTO_SEND_TIME is not null and P.AUTO_SEND_TIME<=" . $DB->CharToDateFunction($val, "SHORT") . " )";
} else {
$this->LAST_ERROR .= GetMessage("POST_WRONG_AUTO_TILL") . "<br>";
}
break;
}
}
}
$arOrder = array();
foreach ($aSort as $key => $ord) {
$key = strtoupper($key);
$ord = strtoupper($ord) != "ASC" ? "DESC" : "ASC";
switch ($key) {
case "ID":
$arOrder[$key] = "P.ID " . $ord;
break;
case "TIMESTAMP":
$arOrder[$key] = "P.TIMESTAMP_X " . $ord;
break;
case "SUBJECT":
$arOrder[$key] = "P.SUBJECT " . $ord;
break;
case "BODY_TYPE":
$arOrder[$key] = "P.BODY_TYPE " . $ord;
break;
case "STATUS":
$arOrder[$key] = "P.STATUS " . $ord;
break;
case "DATE_SENT":
$arOrder[$key] = "P.DATE_SENT " . $ord;
break;
case "AUTO_SEND_TIME":
$arOrder[$key] = "P.AUTO_SEND_TIME " . $ord;
break;
case "FROM_FIELD":
$arOrder[$key] = "P.FROM_FIELD " . $ord;
break;
case "TO_FIELD":
$arOrder[$key] = "P.TO_FIELD " . $ord;
break;
}
}
if (count($arOrder) <= 0) {
$arOrder["ID"] = "P.ID DESC";
}
$strSqlOrder = " ORDER BY " . implode(", ", $arOrder);
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tif(P.STATUS='S','" . $DB->ForSql(GetMessage("POST_STATUS_SENT")) . "',\n\t\t\t\tif(P.STATUS='P','" . $DB->ForSql(GetMessage("POST_STATUS_PART")) . "',\n\t\t\t\tif(P.STATUS='E','" . $DB->ForSql(GetMessage("POST_STATUS_ERROR")) . "',\n\t\t\t\tif(P.STATUS='W','" . $DB->ForSql(GetMessage("POST_STATUS_WAIT")) . "',\n\t\t\t\t'" . $DB->ForSql(GetMessage("POST_STATUS_DRAFT")) . "')))) as STATUS_TITLE\n\t\t\t\t,P.ID\n\t\t\t\t,P.STATUS\n\t\t\t\t,P.FROM_FIELD\n\t\t\t\t,P.TO_FIELD\n\t\t\t\t,P.EMAIL_FILTER\n\t\t\t\t,P.SUBJECT\n\t\t\t\t,P.BODY_TYPE\n\t\t\t\t,P.DIRECT_SEND\n\t\t\t\t,P.CHARSET\n\t\t\t\t,P.MSG_CHARSET\n\t\t\t\t,P.SUBSCR_FORMAT\n\t\t\t\t," . $DB->DateToCharFunction("P.TIMESTAMP_X") . " TIMESTAMP_X\n\t\t\t\t," . $DB->DateToCharFunction("P.DATE_SENT") . " DATE_SENT\n\t\t\tFROM b_posting P\n\t\t\tWHERE\n\t\t\t" . $strSqlSearch . "\n\t\t";
if (count($arSqlSearch_h) > 0) {
$strSqlSearch_h = GetFilterSqlSearch($arSqlSearch_h);
$strSql = $strSql . " HAVING " . $strSqlSearch_h;
}
$strSql .= $strSqlOrder;
// echo htmlspecialcharsbx($strSql);
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$res->is_filtered = IsFiltered($strSqlSearch);
return $res;
}
function GetFileList(&$by, &$order, $arFilter = array(), $checkRights = 'N')
{
$err_mess = CAllTicket::err_mess() . "<br>Function: GetFileList<br>Line: ";
global $DB, $USER;
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
$filterKeysCount = count($filter_keys);
for ($i = 0; $i < $filterKeysCount; $i++) {
$key = $filter_keys[$i];
$val = $arFilter[$filter_keys[$i]];
if (is_array($val) && count($val) <= 0 || !is_array($val) && (strlen($val) <= 0 || $val === 'NOT_REF')) {
continue;
}
$match_value_set = in_array($key . "_EXACT_MATCH", $filter_keys) ? true : false;
$key = strtoupper($key);
switch ($key) {
case "LINK_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("MF.ID", $val, $match);
break;
case "MESSAGE":
case "TICKET_ID":
case "FILE_ID":
case "HASH":
case "MESSAGE_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("MF." . $key, $val, $match);
break;
}
}
}
if ($by == "s_id") {
$strSqlOrder = "ORDER BY MF.ID";
} elseif ($by == "s_file_id") {
$strSqlOrder = "ORDER BY F.ID";
} elseif ($by == "s_message_id") {
$strSqlOrder = "ORDER BY MF.MESSAGE_ID";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY MF.ID";
}
if ($order == "desc") {
$strSqlOrder .= " desc ";
$order = "desc";
} else {
$strSqlOrder .= " asc ";
$order = "asc";
}
$messageJoin = '';
$ticketJoin = '';
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());
if ($bAdmin != 'Y' && $bSupportTeam != 'Y' && $bSupportClient != 'Y' && $bDemo != 'Y') {
return false;
}
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 = '******' . join(',', $ticketUsers) . ')';
} elseif ($bSupportClient == 'Y') {
$strSqlSearchUser = '******' . join(',', $ticketUsers) . ')';
}
if ($strSqlSearchUser) {
$ticketJoin = 'INNER JOIN b_ticket T ON (T.ID = MF.TICKET_ID)';
$arSqlSearch[] = $strSqlSearchUser;
}
}
if ($bSupportTeam != "Y" && $bAdmin != "Y") {
$messageJoin = 'INNER JOIN b_ticket_message M ON (M.ID = MF.MESSAGE_ID)';
$arSqlSearch[] = "M.IS_HIDDEN='N'";
$arSqlSearch[] = "M.IS_LOG='N'";
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tF.*, " . $DB->DateToCharFunction("F.TIMESTAMP_X") . " as TIMESTAMP_X,\n\t\t\t\tMF.ID as LINK_ID,\n\t\t\t\tMF.HASH,\n\t\t\t\tMF.MESSAGE_ID,\n\t\t\t\tMF.TICKET_ID,\n\t\t\t\tMF.EXTENSION_SUFFIX\n\t\t\tFROM\n\t\t\t\tb_ticket_message_2_file MF\n\t\t\tINNER JOIN b_file F ON (MF.FILE_ID = F.ID)\n\t\t\t{$ticketJoin}\n\t\t\t{$messageJoin}\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t{$strSqlOrder}\n\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
return $res;
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителей</a>.</p>
*
*
*
*
* @param string &$by = "s_last_date" Поле для сортировки. Возможные значения: <ul> <li> <b>s_id</b> - ID
* посетителя; </li> <li> <b>s_events</b> - суммарное кол-во <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">событий</a> сгенерированных
* посетителем; </li> <li> <b>s_sessions</b> - суммарное кол-во <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#session">сессий</a> посетителя; </li> <li>
* <b>s_hits</b> - суммарное кол-во <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хитов</a> посетителя; </li> <li>
* <b>s_first_site_id</b> - ID сайта на который впервые пришел посетитель; </li> <li>
* <b>s_first_date</b> - время первого захода на сайт; </li> <li> <b>s_first_url_from</b> -
* страница с которой впервые пришел посетитель; </li> <li> <b>s_first_url_to</b> -
* страница куда впервые пришел посетитель; </li> <li> <b>s_first_adv_id</b> - ID <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv">рекламной кампании</a> первого
* захода; </li> <li> <b>s_last_site_id</b> - ID сайта последнего захода посетителя;
* </li> <li> <b>s_last_date</b> - время последнего захода поестителя; </li> <li>
* <b>s_last_user_id</b> - ID пользователя; </li> <li> <b>s_last_url_last</b> - последняя
* страница на которую заходил посетитель; </li> <li> <b>s_last_user_agent</b> - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#user_agent">UserAgent</a> посетителя на
* последнем заходе; </li> <li> <b>s_last_ip</b> - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#ip">IP адрес</a> посетителя на
* последнем заходе; </li> <li> <b>s_last_adv_id</b> - ID рекламной кампании на
* последнем заходе; </li> <li> <b>s_last_country_id</b> - ID страны посетителя на
* последнем заходе. </li> </ul>
*
*
*
* @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по
* возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul>
*
*
*
* @param array $filter = array() Массив для фильтрации результирующего списка. В массиве
* допустимы следующие ключи: <ul> <li> <b>ID</b>* - ID посетителя; </li> <li>
* <b>ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по <b>ID</b>
* будет искаться вхождение; </li> <li> <b>REGISTERED</b> - был ли посетитель
* когда либо авторизован на сайте, возможные значения: <ul> <li> <b>Y</b> -
* был; </li> <li> <b>N</b> - не был. </li> </ul> </li> <li> <b>FIRST_DATE1</b> - начальное
* значение интервала для поля "дата первого захода на сайт"; </li> <li>
* <b>FIRST_DATE2</b> - конечное значение интервала для поля "дата первого
* захода на сайт"; </li> <li> <b>LAST_DATE1</b> - начальное значение интервала
* для поля "дата последнего захода на сайт"; </li> <li> <b>LAST_DATE2</b> -
* конечное значение интервала для поля "дата первого захода на
* сайт"; </li> <li> <b>PERIOD_DATE1</b> - начальное значение интервала для даты
* посещения посетителем сайта; </li> <li> <b>PERIOD_DATE2</b> - конечно значение
* интервала для даты посещения посетителем сайта; </li> <li> <b>SITE_ID</b>* - ID
* сайта первого либо последнего захода; </li> <li> <b>SITE_ID_EXACT_MATCH</b> - если
* значение равно "N", то при фильтрации по <b>SITE_ID</b> будет искаться
* вхождение; </li> <li> <b>FIRST_SITE_ID</b>* - ID сайта первого захода; </li> <li>
* <b>FIRST_SITE_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по
* <b>FIRST_SITE_ID</b> будет искаться вхождение; </li> <li> <b>LAST_SITE_ID</b>* - ID сайта
* последнего захода; </li> <li> <b>LAST_SITE_ID_EXACT_MATCH</b> - если значение равно
* "N", то при фильтрации по <b>LAST_SITE_ID</b> будет искаться вхождение; </li>
* <li> <b>URL</b>* - страница откуда впервые пришел посетитель, страница на
* которую впервые пришел посетитель и последняя страница
* просмотренная посетителем; </li> <li> <b>URL_EXACT_MATCH</b> - если значение
* равно "Y", то при фильтрации по <b>URL</b> будет искаться точное
* совпадение; </li> <li> <b>URL_404</b> - была ли <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#404">404 ошибка</a> на первой странице
* или на последней странице посещенной посетителем, возможные
* значения: <ul> <li> <b>Y</b> - была; </li> <li> <b>N</b> - не было. </li> </ul> </li> <li>
* <b>USER_AGENT</b>* - UserAgent посетителя на последнем заходе; </li> <li>
* <b>USER_AGENT_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>USER_AGENT</b> будет искаться точное совпадение; </li> <li> <b>ADV</b> - флаг
* "приходил ли посетитель когда либо по рекламной кампании (не
* равной NA/NA)", возможные значения: <ul> <li> <b>Y</b> - посетитель приходил
* по какой либо рекламной кампании (не равной NA/NA); </li> <li> <b>N</b> - не
* приходил никогда ни по одной рекламной кампании (не равной NA/NA).
* </li> </ul> </li> <li> <b>ADV_ID</b> - ID рекламной кампании первого либо
* последнего захода посетителя (при этом это мог быть как <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_first">прямой заход</a> так и <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_back">возврат</a> по рекламной
* кампании); </li> <li> <b>REFERER1</b>* - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_id">идентификатор</a> referer1
* рекламной кампании первого либо последнего захода посетителя;
* </li> <li> <b>REFERER1_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>REFERER1</b> будет искаться точное совпадение; </li> <li> <b>REFERER2</b>* -
* идентификатор referer2 рекламной кампании первого либо последнего
* захода посетителя; </li> <li> <b>REFERER2_EXACT_MATCH</b> - если значение равно "Y",
* то при фильтрации по <b>REFERER2</b> будет искаться точное совпадение;
* </li> <li> <b>REFERER3</b>* - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_referer3">дополнительный
* параметр</a> referer3 рекламной кампании первого либо последнего
* захода посетителя; </li> <li> <b>REFERER3_EXACT_MATCH</b> - если значение равно "Y",
* то при фильтрации по <b>REFERER3</b> будет искаться точное совпадение;
* </li> <li> <b>EVENTS1</b> - начальное значение для интервала кол-ва событий
* сгенерированных посетителем; </li> <li> <b>EVENTS2</b> - конечное значение
* для интервала кол-ва событий сгенерированных посетителем; </li> <li>
* <b>SESS1</b> - начальное значение для интервала кол-ва сессий
* сгенерированных посетителем; </li> <li> <b>SESS2</b> - конечное значение
* для интервала кол-ва сессий сгенерированных посетителем; </li> <li>
* <b>HITS1</b> - начальное значение для интервала кол-ва хитов
* сгенерированных посетителем; </li> <li> <b>HITS2</b> - конечное значение
* для интервала кол-ва хитов сгенерированных посетителем; </li> <li>
* <b>FAVORITES</b> - флаг "добавлял ли посетитель сайт в "<a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#favorites">Избранное</a>"", возможные
* значения: <ul> <li> <b>Y</b> - добавлял; </li> <li> <b>N</b> - не добавлял. </li> </ul> </li>
* <li> <b>IP</b> - IP адрес посетителя сайта в последнем заходе; </li> <li>
* <b>LANG</b> - <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#browser_lang">языки
* установленные в настройках браузера</a> посетителя в последнем
* заходе; </li> <li> <b>COUNTRY_ID</b>* - ID страны (двухсимвольный идентификатор)
* посетителя в последнем заходе; </li> <li> <b>COUNTRY_ID_EXACT_MATCH</b> - если
* значение равно "Y", то при фильтрации по <b>COUNTRY_ID</b> будет искаться
* точное совпадение; </li> <li> <b>COUNTRY</b>* - название страны; </li> <li>
* <b>COUNTRY_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>COUNTRY</b> будет искаться точное совпадение; </li> <li> <b>USER</b>* - ID, логин,
* имя, фамилия <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#user">пользователя</a>,
* под которыми посетитель последний раз был авторизован; </li> <li>
* <b>USER_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по <b>USER</b>
* будет искаться точное совпадение; </li> <li> <b>USER_ID</b>* - ID пользователя,
* под которым посетитель последний раз был авторизован; </li> <li>
* <b>USER_ID_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>USER_ID</b> будет искаться точное совпадение. </li> </ul> <br> * -
* допускается <a href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная
* логика</a>
*
*
*
* @param bool &$is_filtered Флаг отфильтрованности списка посетителей. Если значение равно
* "true", то список был отфильтрован.
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* // выберем только тех посетителей UserAgent которых содержит "Opera"
* $arFilter = array(
* "USER_AGENT" => "Opera"
* );
*
* // получим список записей
* $rs = <b>CGuest::GetList</b>(
* ($by = "s_id"),
* ($order = "desc"),
* $arFilter,
* $is_filtered
* );
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul><li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">Термин "Посетитель"</a>
* </li></ul><a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cguest/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter=Array(), &$is_filtered)
{
$err_mess = "File: ".__FILE__."<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = Array();
$strSqlSearch = "";
$bGroup = false;
$arrGroup = array(
"G.ID" => true,
"G.C_EVENTS" => true,
"G.FIRST_SITE_ID" => true,
"G.LAST_SITE_ID" => true,
"G.SESSIONS" => true,
"G.HITS" => true,
"G.FAVORITES" => true,
"G.FIRST_URL_FROM" => true,
"G.FIRST_URL_TO" => true,
"G.FIRST_URL_TO_404" => true,
"G.FIRST_ADV_ID" => true,
"G.FIRST_REFERER1" => true,
"G.FIRST_REFERER2" => true,
"G.FIRST_REFERER3" => true,
"G.LAST_ADV_ID" => true,
"G.LAST_ADV_BACK" => true,
"G.LAST_REFERER1" => true,
"G.LAST_REFERER2" => true,
"G.LAST_REFERER3" => true,
"G.LAST_USER_ID" => true,
"G.LAST_USER_AUTH" => true,
"G.LAST_URL_LAST" => true,
"G.LAST_URL_LAST_404" => true,
"G.LAST_USER_AGENT" => true,
"G.LAST_IP" => true,
"G.LAST_LANGUAGE" => true,
"G.LAST_COUNTRY_ID" => true,
"G.LAST_CITY_ID" => true,
"G.FIRST_DATE" => true,
"G.LAST_DATE" => true,
"G.FIRST_SESSION_ID" => true,
"G.LAST_SESSION_ID" => true,
"CITY.REGION" => true,
"CITY.NAME" => true,
);
if (is_array($arFilter))
{
foreach ($arFilter as $key => $val)
{
if(is_array($val))
{
if(count($val) <= 0)
continue;
}
else
{
if( (strlen($val) <= 0) || ($val === "NOT_REF") )
continue;
}
$match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch($key)
{
case "ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("G.ID",$val,$match);
break;
case "REGISTERED":
if ($val=="Y")
$arSqlSearch[] = "G.LAST_USER_ID>0 and G.LAST_USER_ID is not null";
elseif ($val=="N")
$arSqlSearch[] = "G.LAST_USER_ID<=0 or G.LAST_USER_ID is null";
break;
case "FIRST_DATE1":
if (CheckDateTime($val))
$arSqlSearch[] = "G.FIRST_DATE >= ".$DB->CharToDateFunction($val, "SHORT");
break;
case "FIRST_DATE2":
if (CheckDateTime($val))
$arSqlSearch[] = "G.FIRST_DATE < ".CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
break;
case "LAST_DATE1":
if (CheckDateTime($val))
$arSqlSearch[] = "G.LAST_DATE >= ".$DB->CharToDateFunction($val, "SHORT");
break;
case "LAST_DATE2":
if (CheckDateTime($val))
$arSqlSearch[] = "G.LAST_DATE < ".CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
break;
case "PERIOD_DATE1":
ResetFilterLogic();
if (CheckDateTime($val))
{
$arSqlSearch[] = "S.DATE_FIRST >= ".$DB->CharToDateFunction($val, "SHORT");
$from0 = " INNER JOIN b_stat_session S ON (S.GUEST_ID = G.ID) ";
$select0 = "count(S.ID) as SESS,";
$bGroup = true;
}
break;
case "PERIOD_DATE2":
ResetFilterLogic();
if (CheckDateTime($val))
{
$arSqlSearch[] = "S.DATE_LAST < ".CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
$from0 = " INNER JOIN b_stat_session S ON (S.GUEST_ID = G.ID) ";
$select0 = "count(S.ID) as SESS,";
$bGroup = true;
}
break;
case "SITE_ID":
if (is_array($val)) $val = implode(" | ", $val);
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("G.LAST_SITE_ID, G.FIRST_SITE_ID", $val, $match);
break;
case "LAST_SITE_ID":
case "FIRST_SITE_ID":
if (is_array($val)) $val = implode(" | ", $val);
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("G.".$key, $val, $match);
break;
case "URL":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.FIRST_URL_FROM,G.FIRST_URL_TO,G.LAST_URL_LAST", $val, $match, array("/","\\",".","?","#",":"));
break;
case "URL_404":
if ($val=="Y")
$arSqlSearch[] = "G.FIRST_URL_TO_404='Y' or G.LAST_URL_LAST_404='Y'";
elseif ($val=="N")
$arSqlSearch[] = "G.FIRST_URL_TO_404='N' and G.LAST_URL_LAST_404='N'";
break;
case "USER_AGENT":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.LAST_USER_AGENT", $val, $match);
break;
case "ADV":
if ($val=="Y")
{
$arSqlSearch[] = "(
G.FIRST_ADV_ID>0 and
G.FIRST_ADV_ID is not null and
G.FIRST_REFERER1<>'NA' and G.FIRST_REFERER2<>'NA'
or
G.LAST_ADV_ID>0 and
G.LAST_ADV_ID is not null and
G.LAST_REFERER1<>'NA' and G.LAST_REFERER2<>'NA'
)";
}
elseif ($val=="N")
{
$arSqlSearch[] = "((
G.FIRST_ADV_ID<=0 or
G.FIRST_ADV_ID is null or
(G.FIRST_REFERER1='NA' and G.FIRST_REFERER2='NA')
) and (
G.LAST_ADV_ID<=0 or
G.LAST_ADV_ID is null or
(G.LAST_REFERER1='NA' and G.LAST_REFERER2='NA')
))";
}
break;
case "ADV_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("G.FIRST_ADV_ID,G.LAST_ADV_ID", $val, $match);
break;
case "REFERER1":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.FIRST_REFERER1,G.LAST_REFERER1", $val, $match);
break;
case "REFERER2":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.FIRST_REFERER2,G.LAST_REFERER2", $val, $match);
break;
case "REFERER3":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.FIRST_REFERER3,G.LAST_REFERER3", $val, $match);
break;
case "EVENTS1":
$arSqlSearch[] = "G.C_EVENTS>='".intval($val)."'";
break;
case "EVENTS2":
$arSqlSearch[] = "G.C_EVENTS<='".intval($val)."'";
break;
case "SESS1":
$arSqlSearch[] = "G.SESSIONS>='".intval($val)."'";
break;
case "SESS2":
$arSqlSearch[] = "G.SESSIONS<='".intval($val)."'";
break;
case "HITS1":
$arSqlSearch[] = "G.HITS>='".intval($val)."'";
break;
case "HITS2":
$arSqlSearch[] = "G.HITS<='".intval($val)."'";
break;
case "FAVORITES":
if ($val=="Y")
$arSqlSearch[] = "G.FAVORITES='Y'";
elseif ($val=="N")
$arSqlSearch[] = "G.FAVORITES<>'Y'";
break;
case "IP":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.LAST_IP",$val,$match,array("."));
break;
case "LANG":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.LAST_LANGUAGE", $val, $match);
break;
case "COUNTRY_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.LAST_COUNTRY_ID", $val, $match);
break;
case "COUNTRY":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.NAME", $val, $match);
$select1 .= " , C.NAME LAST_COUNTRY_NAME ";
$from2 = " LEFT JOIN b_stat_country C ON (C.ID = G.LAST_COUNTRY_ID) ";
$arrGroup["C.NAME"] = true;
$bGroup = true;
break;
case "REGION":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("CITY.REGION", $val, $match);
break;
case "CITY_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.LAST_CITY_ID", $val, $match);
break;
case "CITY":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("CITY.NAME", $val, $match);
break;
case "USER":
if(COption::GetOptionString("statistic", "dbnode_id") <= 0)
{
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = $DB->IsNull("G.LAST_USER_ID","0").">0";
$arSqlSearch[] = GetFilterQuery("G.LAST_USER_ID,A.LOGIN,A.LAST_NAME,A.NAME", $val, $match);
$select1 .= ", ".$DB->Concat($DB->IsNull("A.NAME","''"), "' '", $DB->IsNull("A.LAST_NAME","''"))." USER_NAME, A.LOGIN";
$from1 = "LEFT JOIN b_user A ON (A.ID = G.LAST_USER_ID) ";
$arrGroup["A.NAME"] = true;
$arrGroup["A.LAST_NAME"] = true;
$arrGroup["A.LOGIN"] = true;
$bGroup = true;
}
break;
case "USER_ID":
if(COption::GetOptionString("statistic", "dbnode_id") <= 0)
{
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = $DB->IsNull("G.LAST_USER_ID","0").">0";
$arSqlSearch[] = GetFilterQuery("G.LAST_USER_ID", $val, $match);
$select1 .= ", ".$DB->Concat($DB->IsNull("A.NAME","''"), "' '", $DB->IsNull("A.LAST_NAME","''"))." USER_NAME, A.LOGIN";
$from1 = "LEFT JOIN b_user A ON (A.ID = G.LAST_USER_ID) ";
$arrGroup["A.NAME"] = true;
$arrGroup["A.LAST_NAME"] = true;
$arrGroup["A.LOGIN"] = true;
$bGroup = true;
}
break;
}
}
}
if ($by == "s_id") $strSqlOrder = "ORDER BY G.ID";
elseif ($by == "s_first_site_id") $strSqlOrder = "ORDER BY G.FIRST_SITE_ID";
elseif ($by == "s_last_site_id") $strSqlOrder = "ORDER BY G.LAST_SITE_ID";
elseif ($by == "s_events") $strSqlOrder = "ORDER BY G.C_EVENTS";
elseif ($by == "s_sessions") $strSqlOrder = "ORDER BY G.SESSIONS";
elseif ($by == "s_hits") $strSqlOrder = "ORDER BY G.HITS";
elseif ($by == "s_first_date") $strSqlOrder = "ORDER BY G.FIRST_DATE";
elseif ($by == "s_first_url_from") $strSqlOrder = "ORDER BY G.FIRST_URL_FROM";
elseif ($by == "s_first_url_to") $strSqlOrder = "ORDER BY G.FIRST_URL_TO";
elseif ($by == "s_first_adv_id") $strSqlOrder = "ORDER BY G.FIRST_ADV_ID";
elseif ($by == "s_last_date") $strSqlOrder = "ORDER BY ".CStatistics::DBFirstDate("G.LAST_DATE");
elseif ($by == "s_last_user_id") $strSqlOrder = "ORDER BY G.LAST_USER_ID";
elseif ($by == "s_last_url_last") $strSqlOrder = "ORDER BY G.LAST_URL_LAST";
elseif ($by == "s_last_user_agent") $strSqlOrder = "ORDER BY G.LAST_USER_AGENT";
elseif ($by == "s_last_ip") $strSqlOrder = "ORDER BY G.LAST_IP";
elseif ($by == "s_last_adv_id") $strSqlOrder = "ORDER BY G.LAST_ADV_ID";
elseif ($by == "s_last_country_id") $strSqlOrder = "ORDER BY G.LAST_COUNTRY_ID";
elseif ($by == "s_last_region_name") $strSqlOrder = "ORDER BY CITY.REGION";
elseif ($by == "s_last_city_id") $strSqlOrder = "ORDER BY G.LAST_CITY_ID";
else
{
$by = "s_last_date";
$strSqlOrder = "ORDER BY ".CStatistics::DBFirstDate("G.LAST_DATE");
}
if ($order!="asc")
{
$strSqlOrder .= " desc ";
$order="desc";
}
if($bGroup)
{
$strSqlGroup = "GROUP BY ".implode(", ", array_keys($arrGroup));
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "
SELECT /*TOP*/
".$select0."
G.ID, G.FIRST_SITE_ID, G.FIRST_SESSION_ID,
G.LAST_SESSION_ID, G.LAST_SITE_ID,
G.C_EVENTS, G.SESSIONS, G.HITS, G.FAVORITES,
G.FIRST_URL_FROM, G.FIRST_URL_TO, G.FIRST_URL_TO_404,
G.FIRST_ADV_ID, G.FIRST_REFERER1, G.FIRST_REFERER2, G.FIRST_REFERER3,
G.LAST_ADV_ID, G.LAST_ADV_BACK, G.LAST_REFERER1, G.LAST_REFERER2, G.LAST_REFERER3,
G.LAST_USER_ID, G.LAST_USER_AUTH, G.LAST_URL_LAST, G.LAST_URL_LAST_404,
G.LAST_USER_AGENT, G.LAST_IP, G.LAST_LANGUAGE, G.LAST_COUNTRY_ID,
CITY.REGION as LAST_REGION_NAME,
G.LAST_CITY_ID, CITY.NAME as LAST_CITY_NAME,
".$DB->DateToCharFunction("G.FIRST_DATE")." FIRST_DATE,
".$DB->DateToCharFunction("G.LAST_DATE")." LAST_DATE
".$select1."
FROM
b_stat_guest G
".$from0."
".$from1."
".$from2."
LEFT JOIN b_stat_city CITY ON (CITY.ID = G.LAST_CITY_ID)
WHERE
".$strSqlSearch."
".$strSqlGroup."
".$strSqlOrder."
";
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess.__LINE__);
$is_filtered = (IsFiltered($strSqlSearch));
return $res;
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search">поисковых систем</a> и количество <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search_hit">хитов</a> (проиндексированных страниц) каждой из них за все время ведения статистики, за последние 3 дня, либо за указанный интервал времени.</p>
*
*
* @param string &$by = "s_today_hits" Поле для сортировки. Возможные значения: <ul> <li> <b>s_id</b> - ID поисковой
* системы; </li> <li> <b>s_date_last</b> - дата последнего хита; </li> <li> <b>s_today_hits</b> -
* количество хитов за сегодня; </li> <li> <b>s_yesterday_hits</b> - количество
* хитов за вчера; </li> <li> <b>s_b_yesterday_hits</b> - количество хитов за
* позавчера; </li> <li> <b>s_total_hits</b> - суммарное количество хитов; </li> <li>
* <b>s_period_hits</b> - количество хитов за установленный период времени
* (<i>filter</i>["<b>DATE1</b>"], <i>filter</i>["<b>DATE2</b>"]); </li> <li> <b>s_name</b> - название
* поисковой системы; </li> <li> <b>s_user_agent</b> - <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search_useragent">UserAgent поисковой
* системы</a>. </li> </ul>
*
* @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по
* возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul>
*
* @param array $filter = array() Массив для фильтрации результирующего списка. В массиве
* допустимы следующие ключи: <ul> <li> <b>ID</b>* - ID поисковой системы; </li>
* <li> <b>ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по <b>ID</b>
* будет искаться вхождение </li> <li> <b>ACTIVE</b> - флаг активности,
* возможные значения: <ul> <li> <b>Y</b> - активна; </li> <li> <b>N</b> - не активна.
* </li> </ul> </li> <li> <b>SAVE_STATISTIC</b> - флаг "сохранять хиты поисковой системы",
* возможные значения: <ul> <li> <b>Y</b> - да; </li> <li> <b>N</b> - нет. </li> </ul> </li> <li>
* <b>DIAGRAM_DEFAULT</b> - флаг "включать в круговую диаграмму и график по
* умолчанию", возможные значения: <ul> <li> <b>Y</b> - да; </li> <li> <b>N</b> - нет. </li>
* </ul> </li> <li> <b>HITS1</b> - начальное значение интервала для поля
* "количество хитов"; </li> <li> <b>HITS2</b> - конечное значение интервала для
* поля "количество хитов"; </li> <li> <b>DATE1_PERIOD</b> - начальное значение
* значение для произвольного периода; </li> <li> <b>DATE2_PERIOD</b> - конечное
* значение значение для произвольного периода; </li> <li> <b>DATE1</b> -
* начальное значение интервала для поля "дата последнего хита
* поисковой системы"; </li> <li> <b>DATE2</b> - конечное значение интервала
* для поля "дата последнего хита поисковой системы"; </li> <li> <b>NAME</b>* -
* наименование поисковой системы; </li> <li> <b>NAME_EXACT_MATCH</b> - если
* значение равно "Y", то при фильтрации по <b>NAME</b> будет искаться
* точное совпадение; </li> <li> <b>USER_AGENT</b>* - UserAgent поисковой системы; </li>
* <li> <b>USER_AGENT_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>USER_AGENT</b> будет искаться точное совпадение. </li> </ul> * - допускается
* <a href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная логика</a>
*
* @param bool &$is_filtered Флаг отфильтрованности списка поисковых систем. Если значение
* равно "true", то список был отфильтрован.
*
* @param mixed $limit = false Максимальное количество поисковых систем которые будут выбраны
* в списке. Если значение равно false, то кол-во РК будет ограничено в
* соответствии со значением параметра "Максимальное кол-во
* показываемых записей в таблицах" из настроек модуля "Статистика".
*
* @return CDBResult
*
* <h4>Example</h4>
* <pre>
* <?
* // отфильтруем данные только для поисковой системы #20 и #21
* // а также получим дополнительные данные на декабрь 2005 года
* $arFilter = array(
* "ID" => "20 | 21",
* "DATE1_PERIOD" => "01.12.2005",
* "DATE2_PERIOD" => "31.12.2005",
* );
*
* // получим список записей
* $rs = <b>CSearcher::GetList</b>(
* ($by = "s_today_hits"),
* ($order = "desc"),
* $arFilter,
* $is_filtered
* );
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/classes/csearcher/getdropdownlist.php">CSearcher::GetDropdownList</a>
* </li> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search">Термин "Поисковая
* система"</a> </li> </ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/csearcher/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, $LIMIT = false)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array("S.ID <> 1");
$arSqlSearch_h = array();
$strSqlSearch_h = "";
$filter_period = false;
$strSqlPeriod = "";
$strT = "";
if (is_array($arFilter)) {
ResetFilterLogic();
$date1 = $arFilter["DATE1_PERIOD"];
$date2 = $arFilter["DATE2_PERIOD"];
$date_from = MkDateTime(ConvertDateTime($date1, "D.M.Y"), "d.m.Y");
$date_to = MkDateTime(ConvertDateTime($date2, "D.M.Y") . " 23:59", "d.m.Y H:i");
if (CheckDateTime($date1) && strlen($date1) > 0) {
$filter_period = true;
if (strlen($date2) > 0) {
$strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0, if(D.DATE_STAT>FROM_UNIXTIME('{$date_to}'),0,";
$strT = ")))";
} else {
$strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0,";
$strT = "))";
}
} elseif (CheckDateTime($date2) && strlen($date2) > 0) {
ResetFilterLogic();
$filter_period = true;
$strSqlPeriod = "sum(if(D.DATE_STAT>FROM_UNIXTIME('{$date_to}'),0,";
$strT = "))";
}
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("S.ID", $val, $match);
break;
case "ACTIVE":
case "SAVE_STATISTIC":
case "DIAGRAM_DEFAULT":
$arSqlSearch[] = $val == "Y" ? "S." . $key . "='Y'" : "S." . $key . "='N'";
break;
case "HITS1":
$arSqlSearch_h[] = "(sum(ifnull(D.TOTAL_HITS,0))+ifnull(S.TOTAL_HITS,0))>='" . intval($val) . "'";
break;
case "HITS2":
$arSqlSearch_h[] = "(sum(ifnull(D.TOTAL_HITS,0))+ifnull(S.TOTAL_HITS,0))<='" . intval($val) . "'";
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch_h[] = "max(D.DATE_LAST)>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch_h[] = "max(D.DATE_LAST)<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
case "NAME":
case "USER_AGENT":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S." . $key, $val, $match);
break;
}
}
}
if ($by == "s_id") {
$strSqlOrder = "ORDER BY S.ID";
} elseif ($by == "s_date_last") {
$strSqlOrder = "ORDER BY S_DATE_LAST";
} elseif ($by == "s_today_hits") {
$strSqlOrder = "ORDER BY TODAY_HITS";
} elseif ($by == "s_yesterday_hits") {
$strSqlOrder = "ORDER BY YESTERDAY_HITS";
} elseif ($by == "s_b_yesterday_hits") {
$strSqlOrder = "ORDER BY B_YESTERDAY_HITS";
} elseif ($by == "s_total_hits") {
$strSqlOrder = "ORDER BY TOTAL_HITS";
} elseif ($by == "s_period_hits") {
$strSqlOrder = "ORDER BY PERIOD_HITS";
} elseif ($by == "s_name") {
$strSqlOrder = "ORDER BY S.NAME";
} elseif ($by == "s_user_agent") {
$strSqlOrder = "ORDER BY S.USER_AGENT";
} elseif ($by == "s_chart") {
$strSqlOrder = "ORDER BY S.DIAGRAM_DEFAULT desc, TOTAL_HITS ";
} elseif ($by == "s_stat") {
$strSqlOrder = "ORDER BY TODAY_HITS desc, YESTERDAY_HITS desc, B_YESTERDAY_HITS desc, TOTAL_HITS desc, PERIOD_HITS";
} else {
$by = "s_today_hits";
$strSqlOrder = "ORDER BY TODAY_HITS desc, YESTERDAY_HITS desc, B_YESTERDAY_HITS desc, TOTAL_HITS desc, PERIOD_HITS";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$limit_sql = "LIMIT " . intval(COption::GetOptionString('statistic', 'RECORDS_LIMIT'));
if (intval($LIMIT) > 0) {
$limit_sql = "LIMIT " . intval($LIMIT);
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
foreach ($arSqlSearch_h as $sqlWhere) {
$strSqlSearch_h .= " and (" . $sqlWhere . ") ";
}
$strSql = "\n\t\tSELECT\n\t\t\tS.ID,\n\t\t\tS.TOTAL_HITS,\n\t\t\tS.USER_AGENT,\n\t\t\tS.DIAGRAM_DEFAULT,\n\t\t\t" . $DB->DateToCharFunction("max(D.DATE_LAST)") . "\t\t\t\t\t\tDATE_LAST,\n\t\t\tmax(ifnull(D.DATE_LAST,'1980-01-01'))\t\t\t\t\t\t\t\tS_DATE_LAST,\n\t\t\tsum(ifnull(D.TOTAL_HITS,0))+ifnull(S.TOTAL_HITS,0)\t\t\t\t\tTOTAL_HITS,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.TOTAL_HITS,0),0))\tTODAY_HITS,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.TOTAL_HITS,0),0))\tYESTERDAY_HITS,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.TOTAL_HITS,0),0))\tB_YESTERDAY_HITS,\n\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.TOTAL_HITS,0)' . $strT . ' PERIOD_HITS, ' : '0 PERIOD_HITS,') . "\n\t\t\tS.NAME\n\t\tFROM\n\t\t\tb_stat_searcher S\n\t\tLEFT JOIN b_stat_searcher_day D ON (D.SEARCHER_ID = S.ID)\n\t\tWHERE\n\t\t{$strSqlSearch}\n\t\tand S.ID<>1\n\t\tGROUP BY S.ID\n\t\tHAVING\n\t\t\t'1'='1'\n\t\t\t{$strSqlSearch_h}\n\t\t{$strSqlOrder}\n\t\t{$limit_sql}\n\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch) || $filter_period || strlen($strSqlSearch_h) > 0;
return $res;
}
public static function GetList($arOrder = array("ID" => "DESC"), $arFilter = array(), $arNavStartParams = false)
{
global $DB;
self::ClearErrors();
$arWherePart = array();
if (is_array($arFilter))
{
foreach ($arFilter as $key => $val)
{
$key = strtoupper($key);
switch($key)
{
case "ID":
$arWherePart[] = "U.ID=".intval($val);
break;
case "URI":
$q = GetFilterQuery("U.URI", $val);
if (!empty($q) && ($q != "0"))
$arWherePart[] = $q;
break;
case "URI_EXACT":
$arWherePart[] = "U.URI='".$DB->ForSQL($val)."'";
break;
case "URI_CRC":
$arWherePart[] = "U.URI_CRC=".intval($val);
break;
case "SHORT_URI":
$arWherePart[] = "U.SHORT_URI='".$DB->ForSQL($val)."'";
break;
case "SHORT_URI_CRC":
$arWherePart[] = "U.SHORT_URI_CRC=".intval($val);
break;
case "STATUS":
$arWherePart[] = "U.STATUS=".intval($val);
break;
case "MODIFIED_1":
$arWherePart[] = "U.MODIFIED >= FROM_UNIXTIME('".MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y")."')";
break;
case "MODIFIED_2":
$arWherePart[] = "U.MODIFIED <= FROM_UNIXTIME('".MkDateTime(FmtDate($val, "D.M.Y")." 23:59:59", "d.m.Y")."')";
break;
case "LAST_USED_1":
$arWherePart[] = "U.LAST_USED >= FROM_UNIXTIME('".MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y")."')";
break;
case "LAST_USED_2":
$arWherePart[] = "U.LAST_USED <= FROM_UNIXTIME('".MkDateTime(FmtDate($val, "D.M.Y")." 23:59:59", "d.m.Y")."')";
break;
case "NUMBER_USED":
$arWherePart[] = "U.NUMBER_USED=".intval($val);
break;
}
}
}
$strWherePart = "";
if (count($arWherePart) > 0)
{
foreach ($arWherePart as $val)
{
if ($strWherePart !== "")
$strWherePart .= " AND ";
$strWherePart .= "(".$val.")";
}
}
if ($strWherePart !== "")
$strWherePart = "WHERE ".$strWherePart;
$arOrderByPart = array();
if (is_array($arOrder))
{
foreach ($arOrder as $key => $val)
{
$key = strtoupper($key);
if (!in_array($key, array("ID", "URI", "URI_CRC", "SHORT_URI", "SHORT_URI_CRC", "STATUS", "MODIFIED", "LAST_USED", "NUMBER_USED")))
continue;
$val = strtoupper($val);
if (!in_array($val, array("ASC", "DESC")))
$val = "ASC";
if ($key == "MODIFIED")
$key = "MODIFIED1";
if ($key == "LAST_USED")
$key = "LAST_USED1";
$arOrderByPart[] = $key." ".$val;
}
}
$strOrderByPart = "";
if (count($arOrderByPart) > 0)
{
foreach ($arOrderByPart as $val)
{
if ($strOrderByPart !== "")
$strOrderByPart .= ", ";
$strOrderByPart .= $val;
}
}
if ($strOrderByPart !== "")
$strOrderByPart = "ORDER BY ".$strOrderByPart;
$strSql = "FROM b_short_uri U ".$strWherePart;
if ($arNavStartParams)
{
$dbResultCount = $DB->Query("SELECT COUNT(U.ID) as C ".$strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
$arResultCount = $dbResultCount->Fetch();
$strSql = "SELECT ID, URI, URI_CRC, SHORT_URI, SHORT_URI_CRC, STATUS, ".$DB->DateToCharFunction("MODIFIED")." MODIFIED, MODIFIED MODIFIED1, ".$DB->DateToCharFunction("LAST_USED")." LAST_USED, LAST_USED LAST_USED1, NUMBER_USED ".$strSql.$strOrderByPart;
$dbResult = new CDBResult();
$dbResult->NavQuery($strSql, $arResultCount["C"], $arNavStartParams);
}
else
{
$strSql = "SELECT ID, URI, URI_CRC, SHORT_URI, SHORT_URI_CRC, STATUS, ".$DB->DateToCharFunction("MODIFIED")." MODIFIED, MODIFIED MODIFIED1, ".$DB->DateToCharFunction("LAST_USED")." LAST_USED, LAST_USED LAST_USED1, NUMBER_USED ".$strSql.$strOrderByPart;
$dbResult = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}
return $dbResult;
}
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;
}
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "ID":
case "SEARCHER_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("H." . $key, $val, $match);
break;
case "URL":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.URL", $val, $match, array("/", "\\", ".", "?", "#", ":"));
break;
case "URL_404":
$arSqlSearch[] = $val == "Y" ? "H.URL_404='Y'" : "H.URL_404='N'";
break;
case "SEARCHER":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.NAME", $val, $match);
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT >= " . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT < " . CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
}
break;
case "IP":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.IP", $val, $match, array("."));
break;
case "USER_AGENT":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.USER_AGENT", $val, $match);
break;
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("H.SITE_ID", $val, $match);
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
if ($by == "s_id") {
$strSqlOrder = "ORDER BY H.ID";
} elseif ($by == "s_site_id") {
$strSqlOrder = "ORDER BY H.SITE_ID";
} elseif ($by == "s_date_hit") {
$strSqlOrder = "ORDER BY H.DATE_HIT";
} elseif ($by == "s_searcher_id") {
$strSqlOrder = "ORDER BY H.SEARCHER_ID";
} elseif ($by == "s_user_agent") {
$strSqlOrder = "ORDER BY H.USER_AGENT";
} elseif ($by == "s_ip") {
$strSqlOrder = "ORDER BY H.IP";
} elseif ($by == "s_url") {
$strSqlOrder = "ORDER BY H.URL ";
} else {
$by = "s_date_hit";
$strSqlOrder = "ORDER BY H.DATE_HIT";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\tSELECT /*TOP*/\n\t\t\t\tH.ID, H.SEARCHER_ID, H.URL, H.URL_404, H.IP, H.USER_AGENT, H.HIT_KEEP_DAYS, H.SITE_ID,\n\t\t\t\tS.NAME SEARCHER_NAME,\n\t\t\t\t" . $DB->DateToCharFunction("H.DATE_HIT") . " DATE_HIT\n\t\t\tFROM\n\t\t\t\tb_stat_searcher_hit H\n\t\t\tINNER JOIN b_stat_searcher S ON (S.ID = H.SEARCHER_ID)\n\t\t\tWHERE\n\t\t\t" . $strSqlSearch . "\n\t\t\t" . $strSqlOrder . "\n\t\t";
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $res;
}
public static function FilterCreateEx($fname, $vals, $type, &$bFullJoin, $cOperationType = false, $bSkipEmpty = true)
{
global $DB;
if (!is_array($vals)) {
$vals = array($vals);
}
if (count($vals) < 1) {
return "";
}
if (is_bool($cOperationType)) {
if ($cOperationType === true) {
$cOperationType = "N";
} else {
$cOperationType = "E";
}
}
if ($cOperationType == "G") {
$strOperation = ">";
} elseif ($cOperationType == "GE") {
$strOperation = ">=";
} elseif ($cOperationType == "LE") {
$strOperation = "<=";
} elseif ($cOperationType == "L") {
$strOperation = "<";
} else {
$strOperation = "=";
}
$bFullJoin = false;
$bWasLeftJoin = false;
$res = array();
for ($i = 0; $i < count($vals); $i++) {
$val = $vals[$i];
if (!$bSkipEmpty || strlen($val) > 0 || is_bool($val) && $val === false) {
switch ($type) {
case "string_equal":
if ($cOperationType == "?") {
if (strlen($val) > 0) {
$res[] = GetFilterQuery($fname, $val, "N");
}
} else {
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL OR " . $DB->Length($fname) . "<=0)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . CLdapUtil::_Upper($fname) . $strOperation . CLdapUtil::_Upper("'" . $DB->ForSql($val) . "'") . ")";
}
}
break;
case "string":
if ($cOperationType == "?") {
if (strlen($val) > 0) {
$sr = GetFilterQuery($fname, $val, "Y", array(), "N");
if ($sr != "0") {
$res[] = $sr;
}
}
} else {
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL OR " . $DB->Length($fname) . "<=0)";
} else {
if ($strOperation == "=") {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . ($DB->type == "ORACLE" ? CLdapUtil::_Upper($fname) . " LIKE " . CLdapUtil::_Upper("'" . $DB->ForSqlLike($val) . "'") . " ESCAPE '\\'" : $fname . " " . ($strOperation == "=" ? "LIKE" : $strOperation) . " '" . $DB->ForSqlLike($val) . "'") . ")";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . ($DB->type == "ORACLE" ? CLdapUtil::_Upper($fname) . " " . $strOperation . " " . CLdapUtil::_Upper("'" . $DB->ForSql($val) . "'") . " " : $fname . " " . $strOperation . " '" . $DB->ForSql($val) . "'") . ")";
}
}
}
break;
case "date":
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . $fname . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")";
}
break;
case "number":
if ($cOperationType == "?") {
$res[] = GetFilterQuery($fname, $val);
} else {
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . $fname . " " . $strOperation . " '" . DoubleVal($val) . "')";
}
}
break;
case "number_above":
if (strlen($val) <= 0) {
$res[] = ($cOperationType == "N" ? "NOT" : "") . "(" . $fname . " IS NULL)";
} else {
$res[] = ($cOperationType == "N" ? " " . $fname . " IS NULL OR NOT " : "") . "(" . $fname . " " . $strOperation . " '" . $DB->ForSql($val) . "')";
}
break;
}
// we need this conditions to do INNER JOIN
if (strlen($val) > 0 && $cOperationType != "N") {
$bFullJoin = true;
} else {
$bWasLeftJoin = true;
}
}
}
$strResult = "";
for ($i = 0; $i < count($res); $i++) {
if ($i > 0) {
$strResult .= $cOperationType == "N" ? " AND " : " OR ";
}
$strResult .= "(" . $res[$i] . ")";
}
if ($strResult != "") {
$strResult = "(" . $strResult . ")";
}
if ($bFullJoin && $bWasLeftJoin && $cOperationType != "N") {
$bFullJoin = false;
}
return $strResult;
}
public static function GetFilter($arFilter)
{
if (!is_array($arFilter))
$arFilter = Array();
$arSqlSearch = Array();
foreach ($arFilter as $key => $val)
{
$res = CLearnHelper::MkOperationFilter($key);
$key = $res["FIELD"];
$cOperationType = $res["OPERATION"];
$key = strtoupper($key);
switch ($key)
{
case "ID":
case "STUDENT_ID":
case "TEST_ID":
case "RESULT":
case "MAX_RESULT":
$arSqlSearch[] = CLearnHelper::FilterCreate("G.".$key, $val, "number", $bFullJoin, $cOperationType);
break;
case "COMPLETED":
$arSqlSearch[] = CLearnHelper::FilterCreate("G.".$key, $val, "string_equal", $bFullJoin, $cOperationType);
break;
case "USER":
$arSqlSearch[] = GetFilterQuery("U.ID, U.LOGIN, U.NAME, U.LAST_NAME",$val);
break;
}
}
return $arSqlSearch;
}
public static function PrepareSql(&$arFields, $arOrder, $arFilter, $arGroupBy, $arSelectFields)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy) > 0) {
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct)) {
if (strlen($strSqlGroupBy) > 0) {
$strSqlGroupBy .= ", ";
}
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy) == 0) {
$strSqlSelect = "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT ";
} else {
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields) > 0 && array_key_exists($arSelectFields, $arFields)) {
$arSelectFields = array($arSelectFields);
}
if (!isset($arSelectFields) || !is_array($arSelectFields) || count($arSelectFields) <= 0 || in_array("*", $arSelectFields)) {
foreach ($arFields as $FIELD_ID => $arField) {
if (isset($arField["WHERE_ONLY"]) && $arField["WHERE_ONLY"] == "Y") {
continue;
}
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if ($arField["TYPE"] == "datetime") {
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD"], "FULL") . " as " . $FIELD_ID;
} elseif ($arField["TYPE"] == "date") {
$strSqlSelect .= $DB->DateToCharFunction($arField["FIELD"], "SHORT") . " as " . $FIELD_ID;
} else {
$strSqlSelect .= $arField["FIELD"] . " as " . $FIELD_ID;
}
if (isset($arField["FROM"]) && strlen($arField["FROM"]) > 0 && !in_array($arField["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arField["FROM"];
$arAlreadyJoined[] = $arField["FROM"];
}
}
} else {
foreach ($arSelectFields as $key => $val) {
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields)) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
if (in_array($key, $arGroupByFunct)) {
$strSqlSelect .= $key . "(" . $arFields[$val]["FIELD"] . ") as " . $val;
} else {
if ($arFields[$val]["TYPE"] == "datetime") {
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL") . " as " . $val;
} elseif ($arFields[$val]["TYPE"] == "date") {
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT") . " as " . $val;
} else {
$strSqlSelect .= $arFields[$val]["FIELD"] . " as " . $val;
}
}
if (isset($arFields[$val]["FROM"]) && strlen($arFields[$val]["FROM"]) > 0 && !in_array($arFields[$val]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if (strlen($strSqlGroupBy) > 0) {
if (strlen($strSqlSelect) > 0) {
$strSqlSelect .= ", ";
}
$strSqlSelect .= "COUNT(%%_DISTINCT_%% " . $arFields[$arFieldsKeys[0]]["FIELD"] . ") as CNT";
} else {
$strSqlSelect = "%%_DISTINCT_%% " . $strSqlSelect;
}
}
// <-- SELECT
// WHERE -->
$arSqlSearch = array();
if (is_array($arFilter)) {
foreach ($arFilter as $key => $vals) {
if (!is_array($vals)) {
$vals = array($vals);
}
$key_res = CGroup::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields)) {
$arSqlSearch_tmp = array();
foreach ($vals as $val) {
if (isset($arFields[$key]["WHERE"])) {
$arSqlSearch_tmp1 = call_user_func_array($arFields[$key]["WHERE"], array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter));
if ($arSqlSearch_tmp1 !== false) {
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
} else {
if ($arFields[$key]["TYPE"] == "int") {
if (intval($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL OR " . $arFields[$key]["FIELD"] . " <= 0)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . intval($val) . " )";
}
} elseif ($arFields[$key]["TYPE"] == "double") {
$val = str_replace(",", ".", $val);
if (DoubleVal($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL OR " . $arFields[$key]["FIELD"] . " <= 0)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . DoubleVal($val) . " )";
}
} elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char") {
if ($strOperation == "QUERY") {
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, "Y");
} else {
if (strlen($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL OR LENGTH(" . $arFields[$key]["FIELD"] . ")<=0)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " '" . $DB->ForSql($val) . "' )";
}
}
} elseif ($arFields[$key]["TYPE"] == "datetime") {
if (strlen($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "FULL") . ")";
}
} elseif ($arFields[$key]["TYPE"] == "date") {
if (strlen($val) <= 0) {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? "NOT" : "") . "(" . $arFields[$key]["FIELD"] . " IS NULL)";
} else {
$arSqlSearch_tmp[] = ($strNegative == "Y" ? " " . $arFields[$key]["FIELD"] . " IS NULL OR NOT " : "") . "(" . $arFields[$key]["FIELD"] . " " . $strOperation . " " . $DB->CharToDateFunction($DB->ForSql($val), "SHORT") . ")";
}
}
}
}
if (isset($arFields[$key]["FROM"]) && strlen($arFields[$key]["FROM"]) > 0 && !in_array($arFields[$key]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
foreach ($arSqlSearch_tmp as $condition) {
if ($strSqlSearch_tmp != "") {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $condition . ")";
}
if ($strOrNull == "Y") {
if ($strSqlSearch_tmp != "") {
$strSqlSearch_tmp .= $strNegative == "Y" ? " AND " : " OR ";
}
$strSqlSearch_tmp .= "(" . $arFields[$key]["FIELD"] . " IS " . ($strNegative == "Y" ? "NOT " : "") . "NULL)";
}
if ($strSqlSearch_tmp != "") {
$arSqlSearch[] = "(" . $strSqlSearch_tmp . ")";
}
}
}
}
foreach ($arSqlSearch as $condition) {
if ($strSqlWhere != "") {
$strSqlWhere .= " AND ";
}
$strSqlWhere .= "(" . $condition . ")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = array();
foreach ($arOrder as $by => $order) {
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC") {
$order = "DESC";
}
if (array_key_exists($by, $arFields)) {
$arSqlOrder[] = " " . $arFields[$by]["FIELD"] . " " . $order . " ";
if (isset($arFields[$by]["FROM"]) && strlen($arFields[$by]["FROM"]) > 0 && !in_array($arFields[$by]["FROM"], $arAlreadyJoined)) {
if (strlen($strSqlFrom) > 0) {
$strSqlFrom .= " ";
}
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
}
$strSqlOrderBy = implode(", ", $arSqlOrder);
// <-- ORDER BY
return array("SELECT" => $strSqlSelect, "FROM" => $strSqlFrom, "WHERE" => $strSqlWhere, "GROUPBY" => $strSqlGroupBy, "ORDERBY" => $strSqlOrderBy);
}
function GetList($arSort = null, $arFilter = null)
{
$err_mess = self::err_mess() . "<br>Function: GetList<br>Line: ";
global $DB, $USER, $APPLICATION;
$table = self::TABLE;
$arSqlSearch = array();
if (!is_array($arFilter)) {
$arFilter = array();
}
foreach ($arFilter as $key => $val) {
if (is_array($val) && count($val) <= 0 || !is_array($val) && (strlen($val) <= 0 || $val === 'NOT_REF')) {
continue;
}
$key = strtoupper($key);
if (is_array($val)) {
$val = implode(" | ", $val);
}
switch ($key) {
case "ID":
$arSqlSearch[] = GetFilterQuery("T.ID", $val, "N");
break;
case "~NAME":
//case "DESCRIPTION":
$arSqlSearch[] = GetFilterQuery("T.NAME", $val, "N");
break;
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$arSort = is_array($arSort) ? $arSort : array();
if (isset($arSort["DESCRIPTION"])) {
unset($arSort["DESCRIPTION"]);
}
if (count($arSort) > 0) {
$ar1 = array_merge($DB->GetTableFieldsList($table), array());
$ar2 = array_keys($arSort);
$arDiff = array_diff($ar2, $ar1);
if (is_array($arDiff) && count($arDiff) > 0) {
foreach ($arDiff as $value) {
unset($arSort[$value]);
}
}
}
if (count($arSort) <= 0) {
$arSort = array("ID" => "asc");
}
foreach ($arSort as $by => $order) {
if (strtoupper($order) != "DESC") {
$order = "ASC";
}
$arSqlOrder[] = $by . " " . $order;
}
if (is_array($arSqlOrder) && count($arSqlOrder) > 0) {
$strSqlOrder = " ORDER BY " . implode(",", $arSqlOrder);
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\tT.*\n\t\t\tFROM\n\t\t\t\t{$table} T\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
return $rs;
}
function GetDomainList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array("P.SEARCHER_ID <> 1");
$strSqlSearch = "";
if (is_array($arFilter)) {
foreach ($arFilter as $key => $val) {
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = array_key_exists($key . "_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch ($key) {
case "ID":
case "SEARCHER_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("P." . $key, $val, $match);
break;
case "DOMAIN":
case "VARIABLE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("P." . $key, $val, $match);
break;
}
}
}
$strSqlOrder = "";
if ($by == "s_id") {
$strSqlOrder = "ORDER BY P.ID";
} elseif ($by == "s_domain") {
$strSqlOrder = "ORDER BY P.DOMAIN";
} elseif ($by == "s_variable") {
$strSqlOrder = "ORDER BY P.VARIABLE";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY P.ID";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tP.ID,\n\t\t\t\tP.DOMAIN,\n\t\t\t\tP.VARIABLE,\n\t\t\t\tP.CHAR_SET\n\t\t\tFROM\n\t\t\t\tb_stat_searcher_params P\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $rs;
}
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, $CHECK_RIGHTS = "Y")
{
$err_mess = CAdvType_all::err_mess() . "<br>Function: GetList<br>Line: ";
global $DB;
$arSqlSearch = array();
if ($CHECK_RIGHTS == "Y") {
$isAdmin = CAdvContract::IsAdmin();
$isDemo = CAdvContract::IsDemo();
$isManager = CAdvContract::IsManager();
$isAdvertiser = CAdvContract::IsAdvertiser();
} else {
$isAdmin = true;
$isDemo = true;
$isManager = true;
$isAdvertiser = true;
}
if ($isAdmin || $isDemo || $isManager || $isAdvertiser) {
if (CAdvType::CheckFilter($arFilter)) {
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
for ($i = 0, $n = count($filter_keys); $i < $n; $i++) {
$key = $filter_keys[$i];
$val = $arFilter[$filter_keys[$i]];
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = in_array($key . "_EXACT_MATCH", $filter_keys) ? true : false;
$key = strtoupper($key);
switch ($key) {
case "SID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("T.SID", $val, $match);
break;
case "DATE_MODIFY_1":
$arSqlSearch[] = "T.DATE_MODIFY>=" . $DB->CharToDateFunction($val, "SHORT");
break;
case "DATE_MODIFY_2":
$arSqlSearch[] = "T.DATE_MODIFY<=" . $DB->CharToDateFunction($val . " 23:59:59", "FULL");
break;
case "ACTIVE":
$arSqlSearch[] = $val == "Y" ? "T.ACTIVE='Y'" : "T.ACTIVE='N'";
break;
case "NAME":
case "DESCRIPTION":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("T." . $key, $val, $match);
break;
}
}
}
}
if ($by == "s_sid") {
$strSqlOrder = " ORDER BY T.SID ";
} elseif ($by == "s_date_modify") {
$strSqlOrder = " ORDER BY T.DATE_MODIFY ";
} elseif ($by == "s_modified_by") {
$strSqlOrder = " ORDER BY T.MODIFIED_BY ";
} elseif ($by == "s_date_create") {
$strSqlOrder = " ORDER BY T.DATE_CREATE ";
} elseif ($by == "s_created_by") {
$strSqlOrder = " ORDER BY T.CREATED_BY ";
} elseif ($by == "s_active") {
$strSqlOrder = " ORDER BY T.ACTIVE ";
} elseif ($by == "s_name") {
$strSqlOrder = " ORDER BY T.NAME ";
} elseif ($by == "s_banners") {
$strSqlOrder = " ORDER BY BANNER_COUNT ";
} elseif ($by == "s_description") {
$strSqlOrder = " ORDER BY T.DESCRIPTION ";
} else {
$strSqlOrder = " ORDER BY T.SORT ";
$by = "s_sort";
}
if ($order != "desc") {
$strSqlOrder .= " asc ";
$order = "asc";
} else {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strContracts = "";
if (!$isAdmin && !$isDemo && !$isManager) {
$strContracts = "0";
$arPermissions = CAdvContract::GetUserPermissions();
foreach ($arPermissions as $contract_id => $arContractPerms) {
if (is_array($arContractPerms) && !empty($arContractPerms)) {
$strContracts .= "," . $contract_id;
}
}
}
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tT.SID,\n\t\t\t\t\tT.ACTIVE,\n\t\t\t\t\tT.SORT,\n\t\t\t\t\tT.NAME,\n\t\t\t\t\tT.DESCRIPTION,\n\t\t\t\t\t" . $DB->DateToCharFunction("T.DATE_CREATE") . "\tDATE_CREATE,\n\t\t\t\t\t" . $DB->DateToCharFunction("T.DATE_MODIFY") . "\tDATE_MODIFY,\n\t\t\t\t\tT.CREATED_BY,\n\t\t\t\t\tT.MODIFIED_BY,\n\t\t\t\t\tcount(distinct B.ID)\t\t\t\t\t\t\tBANNER_COUNT\n\t\t\t\tFROM\n\t\t\t\t\tb_adv_type T\n\t\t\t\t\tLEFT JOIN b_adv_banner B ON (B.TYPE_SID=T.SID" . ($strContracts == "" ? "" : " AND B.CONTRACT_ID IN (" . $strContracts . ")") . ")\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch} " . ($strContracts == "" ? "" : "and exists(select 'x' from b_adv_contract_2_type CT where (CT.TYPE_SID=T.SID OR CT.TYPE_SID='ALL') AND CT.CONTRACT_ID IN (" . $strContracts . ")) ") . " and T.SID<>'ALL'\n\t\t\t\tGROUP BY\n\t\t\t\t\tT.SID, T.ACTIVE, T.SORT, T.NAME, T.DESCRIPTION,\tT.DATE_CREATE, T.DATE_MODIFY, T.CREATED_BY, T.MODIFIED_BY\n\t\t\t\t{$strSqlOrder}\n\t\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $res;
}
return null;
}
public static function GetAccessibilityForUsers($Params = array())
{
global $DB;
CTimeZone::Disable();
$curEventId = intVal($Params['curEventId']);
if (!is_array($Params['users']) || count($Params['users']) == 0) {
return array();
}
$users = array();
$Accessibility = array();
foreach ($Params['users'] as $userId) {
$userId = intVal($userId);
if ($userId) {
$users[] = $userId;
$Accessibility[$userId] = array();
}
}
if (count($users) == 0) {
return array();
}
$strUsers = join(',', $users);
// We fetch all events for user where it was attented
$strSql = "SELECT EVENT_ID,USER_ID,STATUS,ACCESSIBILITY FROM b_calendar_attendees WHERE USER_KEY in (" . $strUsers . ") AND STATUS<>'N'";
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$arInvIds = array();
$Attendees = array();
while ($ev = $res->Fetch()) {
$ev["STATUS"] = trim($ev["STATUS"]);
$ev["ACCESSIBILITY"] = trim($ev["ACCESSIBILITY"]);
if (!is_array($Attendees[$ev['EVENT_ID']])) {
$Attendees[$ev['EVENT_ID']] = array();
}
$Attendees[$ev['EVENT_ID']][] = $ev;
if (!in_array(intVal($ev['EVENT_ID']), $arInvIds)) {
$arInvIds[] = intVal($ev['EVENT_ID']);
}
}
$strInvIds = join(',', $arInvIds);
$from_ts = false;
$to_ts = false;
$arSqlSearch = array();
if ($Params['from']) {
$val = $Params['from'];
$from_ts = CCalendar::Timestamp($val);
if (strtoupper($DB->type) == "MYSQL") {
$arSqlSearch[] = "CE.DT_TO>=FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y"), "d.m.Y") . "')";
} elseif (strtoupper($DB->type) == "MSSQL") {
$arSqlSearch[] = "CE.DT_TO>=" . $DB->CharToDateFunction($val, "SHORT");
} elseif (strtoupper($DB->type) == "ORACLE") {
$arSqlSearch[] = "CE.DT_TO>=TO_DATE('" . FmtDate($val, "D.M.Y") . " 00:00:00','dd.mm.yyyy hh24:mi:ss')";
}
}
if ($Params['to']) {
$val = $Params['to'];
$to_ts = CCalendar::Timestamp($val);
if (date('H:i', $to_ts) == '00:00') {
$to_ts += CCalendar::DAY_LENGTH;
}
if (strtoupper($DB->type) == "MYSQL") {
$arSqlSearch[] = "CE.DT_FROM<=FROM_UNIXTIME('" . MkDateTime(FmtDate($val, "D.M.Y") . " 23:59:59", "d.m.Y H:i:s") . "')";
} elseif (strtoupper($DB->type) == "MSSQL") {
$arSqlSearch[] = "CE.DT_FROM<dateadd(day, 1, " . $DB->CharToDateFunction($val, "SHORT") . ")";
} elseif (strtoupper($DB->type) == "ORACLE") {
$arSqlSearch[] = "CE.DT_FROM<=TO_DATE('" . FmtDate($val, "D.M.Y") . " 23:59:59','dd.mm.yyyy hh24:mi:ss')";
}
}
$arSqlSearch[] = GetFilterQuery("DELETED", "N");
$q = "CE.CAL_TYPE='user' AND CE.OWNER_ID in (" . $strUsers . ")";
if (count($arInvIds) > 0) {
$q = '(' . $q . ') OR CE.ID in (' . $strInvIds . ')';
}
$arSqlSearch[] = $q;
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "\n\t\t\tSELECT CE.ID, CE.CAL_TYPE,CE.OWNER_ID, CE.NAME, " . $DB->DateToCharFunction("CE.DT_FROM") . " as DT_FROM, " . $DB->DateToCharFunction("CE.DT_TO") . " as DT_TO, CE.DT_LENGTH, CE.PRIVATE_EVENT, CE.ACCESSIBILITY, CE.IMPORTANCE,CE.IS_MEETING, CE.MEETING_HOST, CE.MEETING, CE.LOCATION, CE.RRULE, CE.EXRULE, CE.RDATE, CE.EXDATE, CES.SECT_ID, CES.REL\n\t\t\tFROM\n\t\t\t\tb_calendar_event CE\n\t\t\tINNER JOIN b_calendar_event_sect CES ON (CE.ID=CES.EVENT_ID)\n\t\t\tWHERE CE.DELETED='N' AND {$strSqlSearch}";
$res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
$arResult = array();
while ($event = $res->Fetch()) {
$event = self::PreHandleEvent($event);
if ($curEventId && $curEventId == $event['ID']) {
continue;
}
if (self::CheckRecurcion($event)) {
self::ParseRecursion($arResult, $event, array('fromLimit' => $Params["from"], 'toLimit' => $Params["to"]));
} else {
self::HandleEvent($arResult, $event);
}
}
foreach ($arResult as $event) {
$ev_to_ts = $event['DT_TO_TS'];
if ($event['DT_SKIP_TIME'] == 'Y') {
$ev_to_ts += CCalendar::DAY_LENGTH;
}
if ($from_ts && $ev_to_ts < $from_ts || $to_ts && $event['DT_FROM_TS'] > $to_ts) {
continue;
}
if (!in_array($event["ACCESSIBILITY"], array('busy', 'quest', 'free', 'absent'))) {
$event["ACCESSIBILITY"] = 'busy';
}
if (!in_array($event['IMPORTANCE'], array('high', 'normal', 'low'))) {
$event['IMPORTANCE'] = 'normal';
}
$val = array("ID" => $event["ID"], "DT_FROM" => CCalendar::CutZeroTime($event["DT_FROM"]), "DT_TO" => CCalendar::CutZeroTime($event["DT_TO"]), "ACCESSIBILITY" => $event["ACCESSIBILITY"], "IMPORTANCE" => $event["IMPORTANCE"], "FROM" => $event['DT_FROM_TS'], "TO" => $event['DT_TO_TS']);
if ($event['IS_MEETING']) {
if (is_array($Attendees[$event['ID']])) {
foreach ($Attendees[$event['ID']] as $attendee) {
if (is_array($Accessibility[$attendee['USER_ID']])) {
$val['ACCESSIBILITY'] = $attendee['ACCESSIBILITY'];
$Accessibility[$attendee['USER_ID']][] = $val;
}
}
}
} elseif ($event['CAL_TYPE'] == 'user' && is_array($Accessibility[$event['OWNER_ID']])) {
$Accessibility[$event['OWNER_ID']][] = $val;
}
}
CTimeZone::Enable();
return $Accessibility;
}
public static function GetList($Params = array())
{
global $DB, $USER;
$arFilter = $Params['arFilter'];
$arOrder = isset($Params['arOrder']) ? $Params['arOrder'] : array('ID' => 'asc');
static $arFields = array("ID" => array("FIELD_NAME" => "MLC.ID", "FIELD_TYPE" => "int"), "NAME" => array("FIELD_NAME" => "MLC.NAME", "FIELD_TYPE" => "string"), "ACTIVE" => array("FIELD_NAME" => "MLC.ACTIVE", "FIELD_TYPE" => "string"), "DATE_UPDATE" => array("FIELD_NAME" => "MLC.DATE_UPDATE", "FIELD_TYPE" => "date"), "KEYWORDS" => array("FIELD_NAME" => "MLC.KEYWORDS", "FIELD_TYPE" => "string"), "DESCRIPTION" => array("FIELD_NAME" => "MLC.DESCRIPTION", "FIELD_TYPE" => "string"), "OWNER_ID" => array("FIELD_NAME" => "MLC.OWNER_ID", "FIELD_TYPE" => "int"), "PARENT_ID" => array("FIELD_NAME" => "MLC.PARENT_ID", "FIELD_TYPE" => "int"), "ML_TYPE" => array("FIELD_NAME" => "MLC.ML_TYPE", "FIELD_TYPE" => "string"));
$err_mess = CMedialibCollection::GetErrorMess() . "<br>Function: GetList<br>Line: ";
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
for ($i = 0, $l = count($filter_keys); $i < $l; $i++) {
$n = strtoupper($filter_keys[$i]);
$val = $arFilter[$filter_keys[$i]];
if (is_string($val) && strlen($val) <= 0 || strval($val) == "NOT_REF") {
continue;
}
if ($n == 'ID' || $n == 'PARENT_ID' || $n == 'OWNER_ID') {
$arSqlSearch[] = GetFilterQuery($arFields[$n]["FIELD_NAME"], $val, 'N');
} elseif (isset($arFields[$n])) {
$arSqlSearch[] = GetFilterQuery($arFields[$n]["FIELD_NAME"], $val);
}
}
}
$strOrderBy = '';
foreach ($arOrder as $by => $order) {
if (isset($arFields[strtoupper($by)])) {
$strOrderBy .= $arFields[strtoupper($by)]["FIELD_NAME"] . ' ' . (strtolower($order) == 'desc' ? 'desc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS LAST" : "") : 'asc' . (strtoupper($DB->type) == "ORACLE" ? " NULLS FIRST" : "")) . ',';
}
}
if (strlen($strOrderBy) > 0) {
$strOrderBy = "ORDER BY " . rtrim($strOrderBy, ",");
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
if (isset($arFilter['TYPES']) && is_array($arFilter['TYPES'])) {
$strTypes = "";
for ($i = 0, $l = count($arFilter['TYPES']); $i < $l; $i++) {
$strTypes .= "," . IntVal($arFilter['TYPES'][$i]);
}
$strSqlSearch .= "\n AND ML_TYPE in (" . trim($strTypes, ", ") . ")";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\tMLC.*\n\t\t\tFROM\n\t\t\t\tb_medialib_collection MLC\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t{$strOrderBy}";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$arResult = array();
while ($arRes = $res->Fetch()) {
$arResult[] = $arRes;
}
return $arResult;
}
protected static function PrepareSql(&$arFields, $arOrder, &$arFilter, $arGroupBy, $arSelectFields)
{
global $DB;
$strSqlSelect = "";
$strSqlFrom = "";
$strSqlWhere = "";
$strSqlGroupBy = "";
$strSqlOrderBy = "";
$arGroupByFunct = array("COUNT", "AVG", "MIN", "MAX", "SUM");
$arAlreadyJoined = array();
// GROUP BY -->
if (is_array($arGroupBy) && count($arGroupBy)>0)
{
$arSelectFields = $arGroupBy;
foreach ($arGroupBy as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields) && !in_array($key, $arGroupByFunct))
{
if (strlen($strSqlGroupBy) > 0)
$strSqlGroupBy .= ", ";
$strSqlGroupBy .= $arFields[$val]["FIELD"];
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
// <-- GROUP BY
// SELECT -->
$arFieldsKeys = array_keys($arFields);
if (is_array($arGroupBy) && count($arGroupBy)==0)
{
$strSqlSelect = "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT ";
}
else
{
if (isset($arSelectFields) && !is_array($arSelectFields) && is_string($arSelectFields) && strlen($arSelectFields)>0 && array_key_exists($arSelectFields, $arFields))
$arSelectFields = array($arSelectFields);
if (!isset($arSelectFields)
|| !is_array($arSelectFields)
|| count($arSelectFields)<=0
|| in_array("*", $arSelectFields))
{
$cntFieldsKeys = count($arFieldsKeys);
for ($i = 0; $i < $cntFieldsKeys; $i++)
{
if (isset($arFields[$arFieldsKeys[$i]]["WHERE_ONLY"])
&& $arFields[$arFieldsKeys[$i]]["WHERE_ONLY"] == "Y")
{
continue;
}
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if ($arFields[$arFieldsKeys[$i]]["TYPE"] == "datetime")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($arFieldsKeys[$i], $arOrder)))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "FULL")." as ".$arFieldsKeys[$i];
}
elseif ($arFields[$arFieldsKeys[$i]]["TYPE"] == "date")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($arFieldsKeys[$i], $arOrder)))
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i]."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$arFieldsKeys[$i]]["FIELD"], "SHORT")." as ".$arFieldsKeys[$i];
}
else
$strSqlSelect .= $arFields[$arFieldsKeys[$i]]["FIELD"]." as ".$arFieldsKeys[$i];
if (isset($arFields[$arFieldsKeys[$i]]["FROM"])
&& strlen($arFields[$arFieldsKeys[$i]]["FROM"]) > 0
&& !in_array($arFields[$arFieldsKeys[$i]]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$arFieldsKeys[$i]]["FROM"];
$arAlreadyJoined[] = $arFields[$arFieldsKeys[$i]]["FROM"];
}
}
}
else
{
foreach ($arSelectFields as $key => $val)
{
$val = strtoupper($val);
$key = strtoupper($key);
if (array_key_exists($val, $arFields))
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
if (in_array($key, $arGroupByFunct))
{
$strSqlSelect .= $key."(".$arFields[$val]["FIELD"].") as ".$val;
}
else
{
if ($arFields[$val]["TYPE"] == "datetime")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($val, $arOrder)))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "FULL")." as ".$val;
}
elseif ($arFields[$val]["TYPE"] == "date")
{
if ((strtoupper($DB->type)=="ORACLE" || strtoupper($DB->type)=="MSSQL") && (array_key_exists($val, $arOrder)))
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val."_X1, ";
$strSqlSelect .= $DB->DateToCharFunction($arFields[$val]["FIELD"], "SHORT")." as ".$val;
}
else
$strSqlSelect .= $arFields[$val]["FIELD"]." as ".$val;
}
if (isset($arFields[$val]["FROM"])
&& strlen($arFields[$val]["FROM"]) > 0
&& !in_array($arFields[$val]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$val]["FROM"];
$arAlreadyJoined[] = $arFields[$val]["FROM"];
}
}
}
}
if (strlen($strSqlGroupBy) > 0)
{
if (strlen($strSqlSelect) > 0)
$strSqlSelect .= ", ";
$strSqlSelect .= "COUNT(%%_DISTINCT_%% ".$arFields[$arFieldsKeys[0]]["FIELD"].") as CNT";
}
else
$strSqlSelect = "%%_DISTINCT_%% ".$strSqlSelect;
}
// <-- SELECT
// WHERE -->
$arSqlSearch = array();
if (!is_array($arFilter))
$filter_keys = array();
else
$filter_keys = array_keys($arFilter);
$cntFilterKeys = count($filter_keys);
for ($i = 0; $i < $cntFilterKeys; $i++)
{
$vals = $arFilter[$filter_keys[$i]];
if (!is_array($vals))
$vals = array($vals);
else
$vals = array_values($vals);
$key = $filter_keys[$i];
$key_res = self::GetFilterOperation($key);
$key = $key_res["FIELD"];
$strNegative = $key_res["NEGATIVE"];
$strOperation = $key_res["OPERATION"];
$strOrNull = $key_res["OR_NULL"];
if (array_key_exists($key, $arFields))
{
$arSqlSearch_tmp = array();
$cntVals = count($vals);
for ($j = 0; $j < $cntVals; $j++)
{
$val = $vals[$j];
if (isset($arFields[$key]["WHERE"]))
{
$arSqlSearch_tmp1 = call_user_func_array(
$arFields[$key]["WHERE"],
array($val, $key, $strOperation, $strNegative, $arFields[$key]["FIELD"], $arFields, $arFilter)
);
if ($arSqlSearch_tmp1 !== false)
$arSqlSearch_tmp[] = $arSqlSearch_tmp1;
}
else
{
if ($arFields[$key]["TYPE"] == "int")
{
if ((IntVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".IntVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "double")
{
$val = str_replace(",", ".", $val);
if ((DoubleVal($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND" : "OR")." ".(($strNegative == "Y") ? "NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." 0)";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".DoubleVal($val)." )";
}
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
{
if ($strOperation == "QUERY")
{
$arSqlSearch_tmp[] = GetFilterQuery($arFields[$key]["FIELD"], $val, "Y");
}
else
{
if ((strlen($val) == 0) && (strpos($strOperation, "=") !== False))
$arSqlSearch_tmp[] = "(".$arFields[$key]["FIELD"]." IS ".(($strNegative == "Y") ? "NOT " : "")."NULL) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$DB->Length($arFields[$key]["FIELD"])." <= 0) ".(($strNegative == "Y") ? "AND NOT" : "OR")." (".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
else
$arSqlSearch_tmp[] = (($strNegative == "Y") ? " ".$arFields[$key]["FIELD"]." IS NULL OR NOT " : "")."(".$arFields[$key]["FIELD"]." ".$strOperation." '".$DB->ForSql($val)."' )";
}
}
elseif ($arFields[$key]["TYPE"] == "datetime")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "FULL").")";
}
elseif ($arFields[$key]["TYPE"] == "date")
{
if (strlen($val) <= 0)
$arSqlSearch_tmp[] = ($strNegative=="Y"?"NOT":"")."(".$arFields[$key]["FIELD"]." IS NULL)";
else
$arSqlSearch_tmp[] = ($strNegative=="Y"?" ".$arFields[$key]["FIELD"]." IS NULL OR NOT ":"")."(".$arFields[$key]["FIELD"]." ".$strOperation." ".$DB->CharToDateFunction($DB->ForSql($val), "SHORT").")";
}
}
}
if (isset($arFields[$key]["FROM"])
&& strlen($arFields[$key]["FROM"]) > 0
&& !in_array($arFields[$key]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$key]["FROM"];
$arAlreadyJoined[] = $arFields[$key]["FROM"];
}
$strSqlSearch_tmp = "";
$cntSqlSearch_tmp = count($arSqlSearch_tmp);
for ($j = 0; $j < $cntSqlSearch_tmp; $j++)
{
if ($j > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arSqlSearch_tmp[$j].")";
}
if ($strOrNull == "Y")
{
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." IS ".($strNegative=="Y" ? "NOT " : "")."NULL)";
if (strlen($strSqlSearch_tmp) > 0)
$strSqlSearch_tmp .= ($strNegative=="Y" ? " AND " : " OR ");
if ($arFields[$key]["TYPE"] == "int" || $arFields[$key]["TYPE"] == "double")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." 0)";
elseif ($arFields[$key]["TYPE"] == "string" || $arFields[$key]["TYPE"] == "char")
$strSqlSearch_tmp .= "(".$arFields[$key]["FIELD"]." ".($strNegative=="Y" ? "<>" : "=")." '')";
else
$strSqlSearch_tmp .= ($strNegative=="Y" ? " (1=1) " : " (1=0) ");
}
if ($strSqlSearch_tmp != "")
$arSqlSearch[] = "(".$strSqlSearch_tmp.")";
}
}
$cntSqlSearch = count($arSqlSearch);
for ($i = 0; $i < $cntSqlSearch; $i++)
{
if (strlen($strSqlWhere) > 0)
$strSqlWhere .= " AND ";
$strSqlWhere .= "(".$arSqlSearch[$i].")";
}
// <-- WHERE
// ORDER BY -->
$arSqlOrder = Array();
foreach ($arOrder as $by => $order)
{
$by = strtoupper($by);
$order = strtoupper($order);
if ($order != "ASC")
$order = "DESC";
else
$order = "ASC";
if (array_key_exists($by, $arFields))
{
$arSqlOrder[] = " ".$arFields[$by]["FIELD"]." ".$order." ";
if (isset($arFields[$by]["FROM"])
&& strlen($arFields[$by]["FROM"]) > 0
&& !in_array($arFields[$by]["FROM"], $arAlreadyJoined))
{
if (strlen($strSqlFrom) > 0)
$strSqlFrom .= " ";
$strSqlFrom .= $arFields[$by]["FROM"];
$arAlreadyJoined[] = $arFields[$by]["FROM"];
}
}
}
DelDuplicateSort($arSqlOrder);
$cntSqlOrder = count($arSqlOrder);
for ($i=0; $i<$cntSqlOrder; $i++)
{
if (strlen($strSqlOrderBy) > 0)
$strSqlOrderBy .= ", ";
if(strtoupper($DB->type)=="ORACLE")
{
if(substr($arSqlOrder[$i], -3)=="ASC")
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS FIRST";
else
$strSqlOrderBy .= $arSqlOrder[$i]." NULLS LAST";
}
else
$strSqlOrderBy .= $arSqlOrder[$i];
}
// <-- ORDER BY
return array(
"SELECT" => $strSqlSelect,
"FROM" => $strSqlFrom,
"WHERE" => $strSqlWhere,
"GROUPBY" => $strSqlGroupBy,
"ORDERBY" => $strSqlOrderBy
);
}
function GetMenuList($arFilter = array(), $check_rights = "Y")
{
$err_mess = CAllForm::err_mess() . "<br>Function: GetMenuList<br>Line: ";
global $DB, $USER, $strError;
$arSqlSearch = array();
$strSqlSearch = "";
if (is_array($arFilter)) {
$filter_keys = array_keys($arFilter);
for ($i = 0; $i < count($filter_keys); $i++) {
$key = $filter_keys[$i];
$val = $arFilter[$filter_keys[$i]];
if (is_array($val)) {
if (count($val) <= 0) {
continue;
}
} else {
if (strlen($val) <= 0 || $val === "NOT_REF") {
continue;
}
}
$match_value_set = in_array($key . "_EXACT_MATCH", $filter_keys) ? true : false;
$key = strtoupper($key);
switch ($key) {
case "FORM_ID":
case "LID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("L." . $key, $val, $match);
break;
case "MENU":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.MENU", $val, $match);
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
if ($check_rights == "N" || CForm::IsAdmin()) {
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tF.ID,\n\t\t\t\t\tF.NAME,\n\t\t\t\t\tL.LID,\n\t\t\t\t\tL.MENU\n\t\t\t\tFROM\n\t\t\t\t\tb_form_menu L,\n\t\t\t\t\tb_form F\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tand L.FORM_ID = F.ID\n\t\t\t\tORDER BY F.C_SORT\n\t\t\t\t";
} else {
$arGroups = $USER->GetUserGroupArray();
if (!is_array($arGroups)) {
$arGroups[] = 2;
}
$groups = implode(",", $arGroups);
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tF.ID,\n\t\t\t\t\tF.NAME,\n\t\t\t\t\tL.LID,\n\t\t\t\t\tL.MENU\n\t\t\t\tFROM\n\t\t\t\t\tb_form_menu L,\n\t\t\t\t\tb_form F,\n\t\t\t\t\tb_form_2_group G\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tand L.FORM_ID = F.ID\n\t\t\t\tand G.FORM_ID = F.ID\n\t\t\t\tand G.GROUP_ID in ({$groups})\n\t\t\t\tGROUP BY\n\t\t\t\t\tL.ID, L.LID, L.MENU, F.NAME, F.ID, F.C_SORT\n\t\t\t\tHAVING\n\t\t\t\t\tmax(G.PERMISSION)>=15\n\t\t\t\tORDER BY F.C_SORT\n\t\t\t\t";
}
//echo "<pre>".$strSql."</pre>";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
return $res;
}
