static function GetList($by = 's_id', $order = 'desc', $arFilter = [])
{
$err_mess = "File: ".__FILE__."
Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = Array();
$arSqlSearch_h = Array();
$strSqlSearch_h = "";
$CURRENCY = "";
if (is_array($arFilter))
{
foreach ($arFilter as $key => $val)
{
if(is_array($val))
{
if(count($val) <= 0)
continue;
}
else
{
if( ((string)$val == '') || ($val === "NOT_REF") )
continue;
}
$match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
$key = strtoupper($key);
switch($key)
{
case "ID":
case "EVENT_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.".$key,$val,$match);
break;
case "EVENT_NAME":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("V.NAME",$val, $match);
break;
case "EVENT1":
case "EVENT2":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("V.".$key,$val, $match);
break;
case "EVENT3":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.EVENT3",$val, $match);
break;
case "DATE":
if (CheckDateTime($val))
$arSqlSearch[] = "E.DATE_ENTER=".$DB->CharToDateFunction($val);
break;
case "DATE1":
if (CheckDateTime($val))
$arSqlSearch[] = "E.DATE_ENTER>=".$DB->CharToDateFunction($val, "SHORT");
break;
case "DATE2":
if (CheckDateTime($val))
$arSqlSearch[] = "E.DATE_ENTER<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
break;
case "REDIRECT_URL":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.REDIRECT_URL",$val,$match,array("/","\",".","?","#",":"));
break;
case "MONEY":
$arSqlSearch_h[] = "MONEY='".roundDB($val)."'";
break;
case "MONEY1":
$arSqlSearch_h[] = "MONEY>='".roundDB($val)."'";
break;
case "MONEY2":
$arSqlSearch_h[] = "MONEY<='".roundDB($val)."'";
break;
case "SESSION_ID":
case "GUEST_ID":
case "ADV_ID":
case "HIT_ID":
case "COUNTRY_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.".$key,$val,$match);
break;
case "ADV_BACK":
$arSqlSearch[] = ($val=="Y") ? "E.ADV_BACK='Y'" : "E.ADV_BACK='N'";
break;
case "REFERER_URL":
case "URL":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.".$key,$val,$match,array("/","\",".","?","#",":"));
break;
case "COUNTRY":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.NAME", $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("E.SITE_ID", $val, $match);
break;
case "REFERER_SITE_ID":
if (is_array($val)) $val = implode(" | ", $val);
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.REFERER_SITE_ID", $val, $match);
break;
case "CURRENCY":
$CURRENCY = $val;
break;
}
}
}
$rate = 1;
$base_currency = GetStatisticBaseCurrency();
$view_currency = $base_currency;
if ($base_currency <> '')
{
if (CModule::IncludeModule("currency"))
{
if ($CURRENCY!=$base_currency && $CURRENCY <> '')
{
$rate = CCurrencyRates::GetConvertFactor($base_currency, $CURRENCY);
$view_currency = $CURRENCY;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
foreach($arSqlSearch_h as $sqlWhere)
$strSqlSearch_h .= " and (".$sqlWhere.") ";
if ($by == "s_id") $strSqlOrder = "ORDER BY E.ID";
elseif ($by == "s_site_id") $strSqlOrder = "ORDER BY E.SITE_ID";
elseif ($by == "s_event_id" || $by == "s_type_id") $strSqlOrder = "ORDER BY E.EVENT_ID";
elseif ($by == "s_event3") $strSqlOrder = "ORDER BY E.EVENT3";
elseif ($by == "s_date_enter") $strSqlOrder = "ORDER BY E.DATE_ENTER";
elseif ($by == "s_adv_id") $strSqlOrder = "ORDER BY E.ADV_ID";
elseif ($by == "s_adv_back") $strSqlOrder = "ORDER BY E.ADV_BACK";
elseif ($by == "s_session_id") $strSqlOrder = "ORDER BY E.SESSION_ID";
elseif ($by == "s_guest_id") $strSqlOrder = "ORDER BY E.GUEST_ID";
elseif ($by == "s_hit_id") $strSqlOrder = "ORDER BY E.HIT_ID";
elseif ($by == "s_url") $strSqlOrder = "ORDER BY E.URL";
elseif ($by == "s_referer_url") $strSqlOrder = "ORDER BY E.REFERER_URL";
elseif ($by == "s_redirect_url") $strSqlOrder = "ORDER BY E.REDIRECT_URL";
elseif ($by == "s_country_id") $strSqlOrder = "ORDER BY E.COUNTRY_ID";
elseif ($by == "s_money") $strSqlOrder = "ORDER BY MONEY";
else
{
$strSqlOrder = "ORDER BY E.ID";
}
if ($order!="asc")
{
$strSqlOrder .= " desc ";
}
if($arFilter["GROUP"]=="total")
{
$strSql = "
SELECT
COUNT(1) COUNTER,
round(sum(if(E.CHARGEBACK='Y',-E.MONEY,E.MONEY)*$rate),2) MONEY,
'".$DB->ForSql($view_currency)."' CURRENCY
FROM
b_stat_event_list E
INNER JOIN b_stat_event V ON (V.ID=E.EVENT_ID)
LEFT JOIN b_stat_country C ON (C.ID=E.COUNTRY_ID)
WHERE
$strSqlSearch
HAVING
1=1
$strSqlSearch_h
";
}
else
{
$strSql = "
SELECT
E.ID, E.EVENT3, E.EVENT_ID, E.ADV_ID, E.ADV_BACK, E.COUNTRY_ID, E.SESSION_ID, E.GUEST_ID, E.HIT_ID, E.REFERER_URL, E.URL, E.REDIRECT_URL, E.CHARGEBACK, E.SITE_ID, E.REFERER_SITE_ID,
round((E.MONEY*$rate),2) MONEY,
'".$DB->ForSql($view_currency)."' CURRENCY,
".$DB->DateToCharFunction("E.DATE_ENTER")." DATE_ENTER,
V.ID TYPE_ID,
V.DESCRIPTION, V.NAME, V.EVENT1, V.EVENT2,
C.NAME COUNTRY_NAME,
if (length(V.NAME)>0, V.NAME,
concat(ifnull(V.EVENT1,''),' / ',ifnull(V.EVENT2,''))) EVENT
FROM
b_stat_event_list E
INNER JOIN b_stat_event V ON (V.ID=E.EVENT_ID)
LEFT JOIN b_stat_country C ON (C.ID=E.COUNTRY_ID)
WHERE
$strSqlSearch
HAVING
1=1
$strSqlSearch_h
$strSqlOrder
LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
";
}
$res = $DB->Query($strSql, false, $err_mess.__LINE__);
return $res;
}