static function GetList($by = '', $order = 'desc', $arFilter = [], &$is_filtered = false, $limit = '', &$arrGROUP_DAYS = [], &$strSql_res = '')
{
$err_mess = "File: ".__FILE__."
Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$find_group = $arFilter["GROUP"];
$arSqlSearch = Array();
$arSqlSearch_h = Array();
$strSqlSearch_h = "";
$filter_period = false;
$strSqlPeriod = "";
$strT = "";
$CURRENCY = "";
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(D.DATE_STATFROM_UNIXTIME('$date_to'),0,";
$strT = ")))";
}
else
{
$strSqlPeriod = "sum(if(D.DATE_STAT '')
{
$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( ((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("A.".$key,$val,$match);
break;
case "DATE1_FIRST":
if (CheckDateTime($val))
$arSqlSearch_h[] = "C_TIME_FIRST >= ".$DB->CharToDateFunction($val, "SHORT");
break;
case "DATE2_FIRST":
if (CheckDateTime($val))
$arSqlSearch_h[] = "C_TIME_FIRST < ".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
break;
case "DATE1_LAST":
if (CheckDateTime($val))
$arSqlSearch_h[] = "C_TIME_LAST >= ".$DB->CharToDateFunction($val, "SHORT");
break;
case "DATE2_LAST":
if (CheckDateTime($val))
$arSqlSearch_h[] = "C_TIME_LAST < ".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
break;
case "REFERER1":
case "REFERER2":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
break;
case "PRIORITY1":
$arSqlSearch[] = "A.PRIORITY>='".intval($val)."'";
break;
case "PRIORITY2":
$arSqlSearch[] = "A.PRIORITY<='".intval($val)."'";
break;
case "NEW_GUESTS1":
$arSqlSearch_h[] = "NEW_GUESTS>='".intval($val)."'";
break;
case "NEW_GUESTS2":
$arSqlSearch_h[] = "NEW_GUESTS<='".intval($val)."'";
break;
case "GUESTS1":
if ($arFilter["GUESTS_BACK"]=="Y")
$arSqlSearch_h[] = "GUESTS_BACK>='".intval($val)."'";
else
$arSqlSearch_h[] = "GUESTS>='".intval($val)."'";
break;
case "GUESTS2":
if ($arFilter["GUESTS_BACK"]=="Y")
$arSqlSearch_h[] = "GUESTS_BACK<='".intval($val)."'";
else
$arSqlSearch_h[] = "GUESTS<='".intval($val)."'";
break;
case "FAVORITES1":
if ($arFilter["FAVORITES_BACK"]=="Y")
$arSqlSearch_h[] = "FAVORITES_BACK>='".intval($val)."'";
else
$arSqlSearch_h[] = "FAVORITES>='".intval($val)."'";
break;
case "FAVORITES2":
if ($arFilter["FAVORITES_BACK"]=="Y")
$arSqlSearch_h[] = "FAVORITES_BACK<='".intval($val)."'";
else
$arSqlSearch_h[] = "FAVORITES<='".intval($val)."'";
break;
case "HOSTS1":
if ($arFilter["HOSTS_BACK"]=="Y")
$arSqlSearch_h[] = "HOSTS_BACK>='".intval($val)."'";
else
$arSqlSearch_h[] = "C_HOSTS>='".intval($val)."'";
break;
case "HOSTS2":
if ($arFilter["HOSTS_BACK"]=="Y")
$arSqlSearch_h[] = "HOSTS_BACK<='".intval($val)."'";
else
$arSqlSearch_h[] = "C_HOSTS<='".intval($val)."'";
break;
case "SESSIONS1":
if ($arFilter["SESSIONS_BACK"]=="Y")
$arSqlSearch_h[] = "SESSIONS_BACK>='".intval($val)."'";
else
$arSqlSearch_h[] = "SESSIONS>='".intval($val)."'";
break;
case "SESSIONS2":
if ($arFilter["SESSIONS_BACK"]=="Y")
$arSqlSearch_h[] = "SESSIONS_BACK<='".intval($val)."'";
else
$arSqlSearch_h[] = "SESSIONS<='".intval($val)."'";
break;
case "HITS1":
if ($arFilter["HITS_BACK"]=="Y")
$arSqlSearch_h[] = "HITS_BACK>='".intval($val)."'";
else
$arSqlSearch_h[] = "HITS>='".intval($val)."'";
break;
case "HITS2":
if ($arFilter["HITS_BACK"]=="Y")
$arSqlSearch_h[] = "HITS_BACK<='".intval($val)."'";
else
$arSqlSearch_h[] = "HITS<='".intval($val)."'";
break;
case "COST1":
$arSqlSearch_h[] = "COST>='".doubleval($val)."'";
break;
case "COST2":
$arSqlSearch_h[] = "COST<='".doubleval($val)."'";
break;
case "REVENUE1":
$arSqlSearch_h[] = "REVENUE>='".doubleval($val)."'";
break;
case "REVENUE2":
$arSqlSearch_h[] = "REVENUE<='".doubleval($val)."'";
break;
case "BENEFIT1":
$arSqlSearch_h[] = "BENEFIT>='".doubleval($val)."'";
break;
case "BENEFIT2":
$arSqlSearch_h[] = "BENEFIT<='".doubleval($val)."'";
break;
case "ROI1":
$arSqlSearch_h[] = "ROI>='".doubleval($val)."'";
break;
case "ROI2":
$arSqlSearch_h[] = "ROI<='".doubleval($val)."'";
break;
case "ATTENT1":
if ($arFilter["ATTENT_BACK"]=="Y")
$arSqlSearch_h[] = "ATTENT_BACK>='".doubleval($val)."'";
else
$arSqlSearch_h[] = "ATTENT>='".doubleval($val)."'";
break;
break;
case "ATTENT2":
if ($arFilter["ATTENT_BACK"]=="Y")
$arSqlSearch_h[] = "ATTENT_BACK<='".doubleval($val)."'";
else
$arSqlSearch_h[] = "ATTENT<='".doubleval($val)."'";
break;
break;
case "VISITORS_PER_DAY1":
$arSqlSearch_h[] = "VISITORS_PER_DAY>='".doubleval($val)."'";
break;
case "VISITORS_PER_DAY2":
$arSqlSearch_h[] = "VISITORS_PER_DAY<='".doubleval($val)."'";
break;
case "DURATION1":
$arSqlSearch_h[] = "ADV_TIME>=".doubleval($val)."*86400";
break;
case "DURATION2":
$arSqlSearch_h[] = "ADV_TIME<=".doubleval($val)."*86400";
break;
case "CURRENCY":
$CURRENCY = $val;
break;
case "DESCRIPTION":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
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.") ";
$group = false;
$find_group = ($find_group == '') ? "NOT_REF" : $find_group;
$arrFields_1 = array(
"C_TIME_FIRST", "C_TIME_LAST", "CURRENCY",
"DATE_FIRST", "DATE_LAST", "ADV_TIME",
"GUESTS", "NEW_GUESTS", "FAVORITES",
"C_HOSTS", "SESSIONS", "HITS",
"GUESTS_BACK", "FAVORITES_BACK", "HOSTS_BACK",
"SESSIONS_BACK", "HITS_BACK", "ATTENT",
"ATTENT_BACK", "NEW_VISITORS", "RETURNED_VISITORS",
"VISITORS_PER_DAY", "COST", "REVENUE",
"BENEFIT", "SESSION_COST", "VISITOR_COST", "ROI",
);
if ($find_group=="referer1") array_push($arrFields_1, "REFERER1");
if ($find_group=="referer2") array_push($arrFields_1, "REFERER2");
$arrFields_2 = array(
"GUESTS_TODAY", "NEW_GUESTS_TODAY", "FAVORITES_TODAY",
"C_HOSTS_TODAY", "SESSIONS_TODAY", "HITS_TODAY",
"GUESTS_BACK_TODAY", "FAVORITES_BACK_TODAY", "HOSTS_BACK_TODAY",
"SESSIONS_BACK_TODAY", "HITS_BACK_TODAY", "GUESTS_YESTERDAY",
"NEW_GUESTS_YESTERDAY", "FAVORITES_YESTERDAY", "C_HOSTS_YESTERDAY",
"SESSIONS_YESTERDAY", "HITS_YESTERDAY", "GUESTS_BACK_YESTERDAY",
"FAVORITES_BACK_YESTERDAY", "HOSTS_BACK_YESTERDAY", "SESSIONS_BACK_YESTERDAY",
"HITS_BACK_YESTERDAY", "GUESTS_BEF_YESTERDAY", "NEW_GUESTS_BEF_YESTERDAY",
"FAVORITES_BEF_YESTERDAY", "C_HOSTS_BEF_YESTERDAY", "SESSIONS_BEF_YESTERDAY",
"HITS_BEF_YESTERDAY", "GUESTS_BACK_BEF_YESTERDAY", "FAVORITES_BACK_BEF_YESTERDAY",
"HOSTS_BACK_BEF_YESTERDAY", "SESSIONS_BACK_BEF_YESTERDAY", "HITS_BACK_BEF_YESTERDAY",
"A.ID", "REFERER1", "REFERER2",
"A.PRIORITY", "A.EVENTS_VIEW", "A.DESCRIPTION",
"GUESTS_PERIOD", "C_HOSTS_PERIOD", "NEW_GUESTS_PERIOD",
"FAVORITES_PERIOD", "SESSIONS_PERIOD", "HITS_PERIOD",
"GUESTS_BACK_PERIOD", "HOSTS_BACK_PERIOD", "FAVORITES_BACK_PERIOD",
"SESSIONS_BACK_PERIOD", "HITS_BACK_PERIOD",
);
$arrFields = $arrFields_1;
if ($find_group=="NOT_REF")
$arrFields = array_merge($arrFields, $arrFields_2);
if ($order != "asc")
{
$order = "desc";
}
else
{
$order = "asc";
}
$key = array_search(strtoupper($by), $arrFields);
if ($key===NULL || $key===false)
$key = array_search("A.".strtoupper($by), $arrFields);
if ($key!==NULL && $key!==false)
$strSqlOrder = " ORDER BY ".$arrFields[$key];
elseif ($by == "s_dropdown")
$strSqlOrder = "ORDER BY A.ID desc, A.REFERER1, A.REFERER2";
elseif ($by == "s_referers")
$strSqlOrder = "ORDER BY A.REFERER1, A.REFERER2";
else
{
if ($find_group=="NOT_REF")
{
$strSqlOrder = " ORDER BY SESSIONS_TODAY $order, SESSIONS_YESTERDAY $order, SESSIONS_BEF_YESTERDAY $order, SESSIONS_PERIOD $order, SESSIONS ";
}
else
{
$strSqlOrder = " ORDER BY SESSIONS ";
}
}
$strSqlOrder .= " ".$order;
$limit = (intval($limit)>0) ? intval($limit) : intval(COption::GetOptionString('statistic','RECORDS_LIMIT'));
$sqlDays = "
-- TODAY
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY,0),0)) GUESTS_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES,0),0)) FAVORITES_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS,0),0)) SESSIONS_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS,0),0)) HITS_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_TODAY,
sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS_BACK,0),0)) HITS_BACK_TODAY,
-- YESTERDAY
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY,0),0)) GUESTS_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES,0),0)) FAVORITES_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS,0),0)) SESSIONS_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS,0),0)) HITS_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS_BACK,0),0)) HITS_BACK_YESTERDAY,
-- THE DAY BEFORE YESTERDAY
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY,0),0)) GUESTS_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES,0),0)) FAVORITES_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS,0),0)) SESSIONS_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS,0),0)) HITS_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_BEF_YESTERDAY,
sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS_BACK,0),0)) HITS_BACK_BEF_YESTERDAY,
";
if ($find_group=="NOT_REF") // no grouping
{
$strSql = "
SELECT
A.ID, A.REFERER1, A.REFERER2, A.PRIORITY, A.EVENTS_VIEW, A.DESCRIPTION,
A.DATE_FIRST C_TIME_FIRST,
A.DATE_LAST C_TIME_LAST,
'".$DB->ForSql($view_currency)."' CURRENCY,
".$DB->DateToCharFunction("A.DATE_FIRST","SHORT")." DATE_FIRST,
".$DB->DateToCharFunction("A.DATE_LAST","SHORT")." DATE_LAST,
UNIX_TIMESTAMP(ifnull(A.DATE_LAST,0))-UNIX_TIMESTAMP(ifnull(A.DATE_FIRST,0)) ADV_TIME,
$sqlDays
-- PERIOD
".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS,0)'.$strT : 'A.GUESTS')." GUESTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.C_HOSTS,0)'.$strT : 'A.C_HOSTS')." C_HOSTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.NEW_GUESTS,0)'.$strT : 'A.NEW_GUESTS')." NEW_GUESTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES,0)'.$strT : 'A.FAVORITES')." FAVORITES_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS,0)'.$strT : 'A.SESSIONS')." SESSIONS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HITS,0)'.$strT : 'A.HITS')." HITS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS_DAY_BACK,0)'.$strT : 'A.GUESTS_BACK')." GUESTS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HOSTS_DAY_BACK,0)'.$strT : 'A.HOSTS_BACK')." HOSTS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES_BACK,0)'.$strT : 'A.FAVORITES')." FAVORITES_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS_BACK,0)'.$strT : 'A.SESSIONS_BACK')." SESSIONS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HITS_BACK,0)'.$strT : 'A.HITS_BACK')." HITS_BACK_PERIOD,
-- TOTAL
A.GUESTS, A.NEW_GUESTS, A.FAVORITES, A.C_HOSTS, A.SESSIONS, A.HITS, A.GUESTS_BACK, A.FAVORITES_BACK, A.HOSTS_BACK, A.SESSIONS_BACK, A.HITS_BACK,
-- AUDIENCE
if(A.SESSIONS>0,round(A.HITS/A.SESSIONS,2),-1) ATTENT,
if(A.SESSIONS_BACK>0,round(A.HITS_BACK/A.SESSIONS_BACK,2),-1) ATTENT_BACK,
if(A.GUESTS>0,round((A.NEW_GUESTS/A.GUESTS)*100,2),-1) NEW_VISITORS,
if(A.GUESTS>0,round((A.GUESTS_BACK/A.GUESTS)*100,2),-1) RETURNED_VISITORS,
if(
round((((UNIX_TIMESTAMP(ifnull(A.DATE_LAST,0))-UNIX_TIMESTAMP(ifnull(A.DATE_FIRST,0)))/86400)),0)>=1, round(A.GUESTS/((UNIX_TIMESTAMP(ifnull(A.DATE_LAST,0)) - UNIX_TIMESTAMP(ifnull(A.DATE_FIRST,0)))/86400),2),-1) VISITORS_PER_DAY,
-- FINANCES
round(round(A.COST,2)*$rate,2) COST,
round(round(A.REVENUE,2)*$rate,2) REVENUE,
round(round(A.REVENUE-A.COST,2)*$rate,2) BENEFIT,
round(round(if(A.SESSIONS>0,A.COST/A.SESSIONS,0),2)*$rate,2) SESSION_COST,
round(round(if(A.GUESTS>0,A.COST/A.GUESTS,0),2)*$rate,2) VISITOR_COST,
if(A.COST>0,round(((A.REVENUE-A.COST)/A.COST)*100,2),-1) ROI
FROM
b_stat_adv A
LEFT JOIN b_stat_adv_day D ON (D.ADV_ID = A.ID)
WHERE
$strSqlSearch
GROUP BY
A.ID, A.REFERER1, A.REFERER2, A.COST, A.REVENUE, A.PRIORITY, A.EVENTS_VIEW, A.DESCRIPTION, A.DATE_FIRST, A.DATE_LAST, A.GUESTS, A.NEW_GUESTS, A.FAVORITES, A.C_HOSTS, A.SESSIONS, A.HITS, A.GUESTS_BACK, A.FAVORITES_BACK, A.HOSTS_BACK, A.SESSIONS_BACK, A.HITS_BACK
";
}
else
{
if ($find_group=="referer1")
$group = "REFERER1";
else
$group = "REFERER2";
// total data
$strSql = "
SELECT
A.$group,
min(A.DATE_LAST) C_TIME_FIRST,
max(A.DATE_LAST) C_TIME_LAST,
'".$DB->ForSql($view_currency)."' CURRENCY,
".$DB->DateToCharFunction("min(A.DATE_FIRST)","SHORT")." DATE_FIRST,
".$DB->DateToCharFunction("max(A.DATE_LAST)","SHORT")." DATE_LAST,
UNIX_TIMESTAMP(max(ifnull(A.DATE_LAST,0)))-UNIX_TIMESTAMP(min(ifnull(A.DATE_FIRST,0))) ADV_TIME,
-- TOTAL
sum(A.GUESTS) GUESTS,
sum(A.NEW_GUESTS) NEW_GUESTS,
sum(A.FAVORITES) FAVORITES,
sum(A.C_HOSTS) C_HOSTS,
sum(A.SESSIONS) SESSIONS,
sum(A.HITS) HITS,
sum(A.GUESTS_BACK) GUESTS_BACK,
sum(A.FAVORITES_BACK) FAVORITES_BACK,
sum(A.HOSTS_BACK) HOSTS_BACK,
sum(A.SESSIONS_BACK) SESSIONS_BACK,
sum(A.HITS_BACK) HITS_BACK,
-- AUDIENCE
if(sum(A.SESSIONS)>0,round(sum(A.HITS)/sum(A.SESSIONS),2),-1) ATTENT,
if(sum(A.SESSIONS_BACK)>0,round(sum(A.HITS_BACK)/sum(A.SESSIONS_BACK),2),-1) ATTENT_BACK,
if(sum(A.GUESTS)>0,round((sum(A.NEW_GUESTS)/sum(A.GUESTS))*100,2),-1) NEW_VISITORS,
if(sum(A.GUESTS)>0,round((sum(A.GUESTS_BACK)/sum(A.GUESTS))*100,2),-1) RETURNED_VISITORS,
if(
round((((UNIX_TIMESTAMP(max(ifnull(A.DATE_LAST,0)))-UNIX_TIMESTAMP(min(ifnull(A.DATE_FIRST,0))))/86400)),0)>=1, round(sum(A.GUESTS)/((UNIX_TIMESTAMP(max(ifnull(A.DATE_LAST,0))) - UNIX_TIMESTAMP(min(ifnull(A.DATE_FIRST,0))))/86400),2),-1) VISITORS_PER_DAY,
-- FINANCES
round(round(sum(A.COST),2)*$rate,2) COST,
round(round(sum(A.REVENUE),2)*$rate,2) REVENUE,
round(round((sum(A.REVENUE)-sum(A.COST)),2)*$rate,2) BENEFIT,
round(round(if(sum(A.SESSIONS)>0,sum(A.COST)/sum(A.SESSIONS),0),2)*$rate,2) SESSION_COST,
round(round(if(sum(A.GUESTS)>0,sum(A.COST)/sum(A.GUESTS),0),2)*$rate,2) VISITOR_COST,
if(sum(A.COST)>0,round(((sum(A.REVENUE)-sum(A.COST))/sum(A.COST))*100,2),-1) ROI
FROM
b_stat_adv A
WHERE
$strSqlSearch
GROUP BY
A.$group
";
// period data
$strSql_days = "
SELECT
A.$group,
$sqlDays
-- PERIOD
".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS,0)'.$strT : 'sum(A.GUESTS)')." GUESTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.C_HOSTS,0)'.$strT : 'sum(A.C_HOSTS)')." C_HOSTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.NEW_GUESTS,0)'.$strT : 'sum(A.NEW_GUESTS)')." NEW_GUESTS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES,0)'.$strT : 'sum(A.FAVORITES)')." FAVORITES_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS,0)'.$strT : 'sum(A.SESSIONS)')." SESSIONS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HITS,0)'.$strT : 'sum(A.HITS)')." HITS_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS_BACK,0)'.$strT : 'A.GUESTS_BACK')." GUESTS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HOSTS_BACK,0)'.$strT : 'A.HOSTS_BACK')." HOSTS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES_BACK,0)'.$strT : 'sum(A.FAVORITES)')." FAVORITES_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS_BACK,0)'.$strT : 'sum(A.SESSIONS_BACK)')." SESSIONS_BACK_PERIOD,
".($filter_period ? $strSqlPeriod.'ifnull(D.HITS_BACK,0)'.$strT : 'sum(A.HITS_BACK)')." HITS_BACK_PERIOD
FROM
b_stat_adv_day D
LEFT JOIN b_stat_adv A ON (D.ADV_ID = A.ID)
GROUP BY
A.$group
";
$z = $DB->Query($strSql_days, false, $err_mess.__LINE__);
while ($zr = $z->Fetch())
{
$arrGROUP_DAYS[$zr[$group]] = $zr;
}
}
$strSql_res = $strSql;
$strSql .= "
HAVING
1=1
$strSqlSearch_h
$strSqlOrder
LIMIT $limit
";
$res = $DB->Query($strSql, false, $err_mess.__LINE__);
$is_filtered = (IsFiltered($strSqlSearch) || $strSqlSearch_h <> '' || $group || $filter_period);
return $res;
}