static function GetEventList($ID, $by = 's_counter', $order = 'desc', $arFilter = [])
{
$err_mess = "File: ".__FILE__."
Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$find_group = $arFilter["GROUP"];
$ID = intval($ID);
$arSqlSearch = Array();
$arSqlSearch_h = Array();
$strSqlSearch_h = "";
$filter_period = false;
$strSqlPeriod = "";
$strT = "";
if (is_array($arFilter))
{
$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 ($date1 <> '')
{
$filter_period = true;
if ($date2 <> '')
{
$strSqlPeriod = "sum(if(AE.DATE_STATFROM_UNIXTIME('$date_to'),0,";
$strT=")))";
}
else
{
$strSqlPeriod = "sum(if(AE.DATE_STAT '')
{
$filter_period = true;
$strSqlPeriod = "sum(if(AE.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
$strT="))";
}
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":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("E.ID", $val, $match);
break;
case "EVENT1":
case "EVENT2":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.".$key, $val, $match);
break;
case "KEYWORDS":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("E.DESCRIPTION, E.NAME", $val, $match);
break;
case "COUNTER_PERIOD_1":
$arSqlSearch_h[] = "COUNTER_PERIOD>='".intval($val)."'";
break;
case "COUNTER_PERIOD_2":
$arSqlSearch_h[] = "COUNTER_PERIOD<='".intval($val)."'";
break;
case "COUNTER_BACK_PERIOD_1":
$arSqlSearch_h[] = "COUNTER_BACK_PERIOD>='".intval($val)."'";
break;
case "COUNTER_BACK_PERIOD_2":
$arSqlSearch_h[] = "COUNTER_BACK_PERIOD<='".intval($val)."'";
break;
case "COUNTER_ADV_DYNAMIC_LIST":
$arSqlSearch_h[] = "(COUNTER_PERIOD>='".intval($val)."' or COUNTER_BACK_PERIOD>='".intval($val)."')";
break;
case "MONEY1":
$arSqlSearch_h[] = "(MONEY+MONEY_BACK)>='".roundDB($val)."'";
break;
case "MONEY2":
$arSqlSearch_h[] = "(MONEY+MONEY_BACK)<='".roundDB($val)."'";
break;
case "MONEY_PERIOD_1":
$arSqlSearch_h[] = "(MONEY_PERIOD+MONEY_BACK_PERIOD)>='".roundDB($val)."'";
break;
case "MONEY_PERIOD_2":
$arSqlSearch_h[] = "(MONEY_PERIOD+MONEY_BACK_PERIOD)<='".roundDB($val)."'";
break;
}
}
}
if ($by == "s_id") $strSqlOrder = "ORDER BY E.ID";
elseif ($by == "s_event1") $strSqlOrder = "ORDER BY E.EVENT1";
elseif ($by == "s_event2") $strSqlOrder = "ORDER BY E.EVENT2";
elseif ($by == "s_sort") $strSqlOrder = "ORDER BY C_SORT";
elseif ($by == "s_name") $strSqlOrder = "ORDER BY E.NAME";
elseif ($by == "s_description") $strSqlOrder = "ORDER BY E.DESCRIPTION";
elseif ($by == "s_counter") $strSqlOrder = "ORDER BY COUNTER";
elseif ($by == "s_counter_back") $strSqlOrder = "ORDER BY COUNTER_BACK";
elseif ($by == "s_counter_period") $strSqlOrder = "ORDER BY COUNTER_PERIOD";
elseif ($by == "s_counter_back_period") $strSqlOrder = "ORDER BY COUNTER_BACK_PERIOD";
elseif ($by == "s_counter_today") $strSqlOrder = "ORDER BY COUNTER_TODAY";
elseif ($by == "s_counter_back_today") $strSqlOrder = "ORDER BY COUNTER_BACK_TODAY";
elseif ($by == "s_counter_yestoday") $strSqlOrder = "ORDER BY COUNTER_YESTERDAY";
elseif ($by == "s_counter_back_yestoday") $strSqlOrder = "ORDER BY COUNTER_BACK_YESTERDAY";
elseif ($by == "s_counter_bef_yestoday") $strSqlOrder = "ORDER BY COUNTER_BEF_YESTERDAY";
elseif ($by == "s_counter_back_bef_yestoday") $strSqlOrder = "ORDER BY COUNTER_BACK_BEF_YESTERDAY";
elseif ($by == "s_def")
{
$strSqlOrder = "
ORDER BY
E.C_SORT desc,
COUNTER_TODAY desc, COUNTER_BACK_TODAY desc,
COUNTER_YESTERDAY desc, COUNTER_BACK_YESTERDAY desc,
COUNTER_BEF_YESTERDAY desc, COUNTER_BACK_BEF_YESTERDAY desc,
".($filter_period? "COUNTER_PERIOD desc, COUNTER_BACK_PERIOD desc,": "")."
COUNTER desc, COUNTER_BACK
";
}
else
{
$strSqlOrder = "ORDER BY COUNTER";
}
if ($order != "asc")
{
$strSqlOrder .= " desc ";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
foreach($arSqlSearch_h as $sqlWhere)
$strSqlSearch_h .= " and (".$sqlWhere.") ";
$find_group = ($find_group == '') ? "NOT_REF" : $find_group;
$sqlDays = "
sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER,0),0)) COUNTER_TODAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER,0),0)) COUNTER_YESTERDAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER,0),0)) COUNTER_BEF_YESTERDAY,
sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_TODAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_BEF_YESTERDAY,
".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER,0)'.$strT : 'sum(AE.COUNTER)')." COUNTER_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER_BACK,0)'.$strT : 'sum(AE.COUNTER_BACK)')." COUNTER_BACK_PERIOD,
sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY,0),0)) MONEY_TODAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY,0),0)) MONEY_YESTERDAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY,0),0)) MONEY_BEF_YESTERDAY,
sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_TODAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_BEF_YESTERDAY,
".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY,0)'.$strT : 'sum(AE.MONEY)')." MONEY_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY_BACK,0)'.$strT : 'sum(AE.MONEY_BACK)')." MONEY_BACK_PERIOD,
";
if ($find_group=="NOT_REF") // no grouping
{
$strSql = "
SELECT
E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION,
sum(AE.COUNTER) COUNTER,
sum(AE.COUNTER_BACK) COUNTER_BACK,
sum(AE.MONEY) MONEY,
sum(AE.MONEY_BACK) MONEY_BACK,
$sqlDays
if (length(E.NAME)>0, E.NAME,
concat(ifnull(E.EVENT1,''),' / ',ifnull(E.EVENT2,''))) EVENT
FROM
b_stat_event E,
b_stat_adv_event_day AE
WHERE
$strSqlSearch
and E.ADV_VISIBLE = 'Y'
and AE.ADV_ID = '$ID'
and AE.EVENT_ID = E.ID
GROUP BY E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION
HAVING
1=1
$strSqlSearch_h
$strSqlOrder
LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
";
}
else
{
if ($find_group=="event1")
$group = "E.EVENT1";
else
$group = "E.EVENT2";
$strSql = "
SELECT
$group,
sum(E.C_SORT) C_SORT,
$sqlDays
sum(AE.COUNTER) COUNTER,
sum(AE.COUNTER_BACK) COUNTER_BACK,
sum(AE.MONEY) MONEY,
sum(AE.MONEY_BACK) MONEY_BACK
FROM
b_stat_event E,
b_stat_adv_event_day AE
WHERE
$strSqlSearch
and E.ADV_VISIBLE = 'Y'
and AE.ADV_ID = '$ID'
and AE.EVENT_ID = E.ID
GROUP BY $group
HAVING
1=1
$strSqlSearch_h
$strSqlOrder
LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
";
}
$res = $DB->Query($strSql, false, $err_mess.__LINE__);
return $res;
}