1C-Bitrix 25.700.0
Загрузка...
Поиск...
Не найдено
adv.php
См. документацию.
1<?php
2require_once($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/statistic/classes/general/adv.php");
3
4class CAdv extends CAllAdv
5{
6 public static function GetAnalysisGraphArray_SQL($strSqlSearch, $DATA_TYPE)
7 {
8 $DB = CDatabase::GetModuleConnection('statistic');
9 switch ($DATA_TYPE)
10 {
11 case "EVENT_SUMMA":
12 case "EVENT":
13 case "EVENT_BACK":
14 case "EVENT_MONEY_SUMMA":
15 case "EVENT_MONEY":
16 case "EVENT_MONEY_BACK":
17 $strSql = "
18 SELECT
19 ".$DB->DateToCharFunction("D.DATE_STAT","SHORT")." DATE_STAT,
20 DAYOFMONTH(D.DATE_STAT) DAY,
21 MONTH(D.DATE_STAT) MONTH,
22 YEAR(D.DATE_STAT) YEAR,
23 sum(D.COUNTER) EVENTS,
24 sum(D.COUNTER_BACK) EVENTS_BACK,
25 sum(D.MONEY) MONEY,
26 sum(D.MONEY_BACK) MONEY_BACK,
27 D.ADV_ID,
28 A.REFERER1,
29 A.REFERER2
30 FROM
31 b_stat_adv_event_day D
32 INNER JOIN b_stat_event E ON (E.ID = D.EVENT_ID)
33 INNER JOIN b_stat_adv A ON (A.ID = D.ADV_ID)
34 WHERE
35 $strSqlSearch
36 GROUP BY
37 D.DATE_STAT, D.ADV_ID, A.REFERER1, A.REFERER2
38 ORDER BY
39 D.DATE_STAT
40 ";
41 break;
42 default:
43 $strSql = "
44 SELECT
45 ".$DB->DateToCharFunction("D.DATE_STAT","SHORT")." DATE_STAT,
46 DAYOFMONTH(D.DATE_STAT) DAY,
47 MONTH(D.DATE_STAT) MONTH,
48 YEAR(D.DATE_STAT) YEAR,
49 max(D.GUESTS_DAY) GUESTS,
50 max(D.NEW_GUESTS) NEW_GUESTS,
51 max(D.FAVORITES) FAVORITES,
52 max(D.C_HOSTS_DAY) C_HOSTS,
53 max(D.SESSIONS) SESSIONS,
54 max(D.HITS) HITS,
55 max(D.GUESTS_DAY_BACK) GUESTS_BACK,
56 max(D.FAVORITES_BACK) FAVORITES_BACK,
57 max(D.HOSTS_DAY_BACK) HOSTS_BACK,
58 max(D.SESSIONS_BACK) SESSIONS_BACK,
59 max(D.HITS_BACK) HITS_BACK,
60 D.ADV_ID,
61 A.REFERER1,
62 A.REFERER2
63 FROM
64 b_stat_adv_day D
65 INNER JOIN b_stat_adv A ON (A.ID = D.ADV_ID)
66 WHERE
67 $strSqlSearch
68 GROUP BY
69 D.DATE_STAT, D.ADV_ID, A.REFERER1, A.REFERER2
70 ORDER BY
71 D.DATE_STAT
72 ";
73 break;
74 }
75 return $strSql;
76 }
77
78 public static function GetList($by = '', $order = 'desc', $arFilter = [], &$is_filtered = false, $limit = '', &$arrGROUP_DAYS = [], &$strSql_res = '')
79 {
80 $DB = CDatabase::GetModuleConnection('statistic');
81 $find_group = $arFilter["GROUP"] ?? '';
82 $arSqlSearch = Array();
83 $arSqlSearch_h = Array();
84 $strSqlSearch_h = "";
85 $filter_period = false;
86 $strSqlPeriod = "";
87 $strT = "";
88 $CURRENCY = "";
89
90 if (is_array($arFilter))
91 {
92 $date1 = $arFilter["DATE1_PERIOD"] ?? '';
93 $date2 = $arFilter["DATE2_PERIOD"] ?? '';
94 $date_from = MkDateTime(ConvertDateTime($date1,"D.M.Y"),"d.m.Y");
95 $date_to = MkDateTime(ConvertDateTime($date2,"D.M.Y")." 23:59","d.m.Y H:i");
96 if ($date1 <> '')
97 {
98 $filter_period = true;
99 if ($date2 <> '')
100 {
101 $strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('$date_from'),0, if(D.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
102 $strT = ")))";
103 }
104 else
105 {
106 $strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('$date_from'),0,";
107 $strT = "))";
108 }
109 }
110 elseif ($date2 <> '')
111 {
112 $filter_period = true;
113 $strSqlPeriod = "sum(if(D.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
114 $strT = "))";
115 }
116
117 foreach ($arFilter as $key => $val)
118 {
119 if(is_array($val))
120 {
121 if(count($val) <= 0)
122 continue;
123 }
124 else
125 {
126 if( ((string)$val == '') || ($val === "NOT_REF") )
127 continue;
128 }
129 $match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
130 $key = strtoupper($key);
131 switch($key)
132 {
133 case "ID":
134 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
135 $arSqlSearch[] = GetFilterQuery("A.".$key,$val,$match);
136 break;
137 case "DATE1_FIRST":
138 if (CheckDateTime($val))
139 $arSqlSearch_h[] = "C_TIME_FIRST >= ".$DB->CharToDateFunction($val, "SHORT");
140 break;
141 case "DATE2_FIRST":
142 if (CheckDateTime($val))
143 $arSqlSearch_h[] = "C_TIME_FIRST < ".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
144 break;
145 case "DATE1_LAST":
146 if (CheckDateTime($val))
147 $arSqlSearch_h[] = "C_TIME_LAST >= ".$DB->CharToDateFunction($val, "SHORT");
148 break;
149 case "DATE2_LAST":
150 if (CheckDateTime($val))
151 $arSqlSearch_h[] = "C_TIME_LAST < ".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
152 break;
153 case "REFERER1":
154 case "REFERER2":
155 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
156 $arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
157 break;
158 case "PRIORITY1":
159 $arSqlSearch[] = "A.PRIORITY>='".intval($val)."'";
160 break;
161 case "PRIORITY2":
162 $arSqlSearch[] = "A.PRIORITY<='".intval($val)."'";
163 break;
164 case "NEW_GUESTS1":
165 $arSqlSearch_h[] = "NEW_GUESTS>='".intval($val)."'";
166 break;
167 case "NEW_GUESTS2":
168 $arSqlSearch_h[] = "NEW_GUESTS<='".intval($val)."'";
169 break;
170 case "GUESTS1":
171 if ($arFilter["GUESTS_BACK"]=="Y")
172 $arSqlSearch_h[] = "GUESTS_BACK>='".intval($val)."'";
173 else
174 $arSqlSearch_h[] = "GUESTS>='".intval($val)."'";
175 break;
176 case "GUESTS2":
177 if ($arFilter["GUESTS_BACK"]=="Y")
178 $arSqlSearch_h[] = "GUESTS_BACK<='".intval($val)."'";
179 else
180 $arSqlSearch_h[] = "GUESTS<='".intval($val)."'";
181 break;
182 case "FAVORITES1":
183 if ($arFilter["FAVORITES_BACK"]=="Y")
184 $arSqlSearch_h[] = "FAVORITES_BACK>='".intval($val)."'";
185 else
186 $arSqlSearch_h[] = "FAVORITES>='".intval($val)."'";
187 break;
188 case "FAVORITES2":
189 if ($arFilter["FAVORITES_BACK"]=="Y")
190 $arSqlSearch_h[] = "FAVORITES_BACK<='".intval($val)."'";
191 else
192 $arSqlSearch_h[] = "FAVORITES<='".intval($val)."'";
193 break;
194 case "HOSTS1":
195 if ($arFilter["HOSTS_BACK"]=="Y")
196 $arSqlSearch_h[] = "HOSTS_BACK>='".intval($val)."'";
197 else
198 $arSqlSearch_h[] = "C_HOSTS>='".intval($val)."'";
199 break;
200 case "HOSTS2":
201 if ($arFilter["HOSTS_BACK"]=="Y")
202 $arSqlSearch_h[] = "HOSTS_BACK<='".intval($val)."'";
203 else
204 $arSqlSearch_h[] = "C_HOSTS<='".intval($val)."'";
205 break;
206 case "SESSIONS1":
207 if ($arFilter["SESSIONS_BACK"]=="Y")
208 $arSqlSearch_h[] = "SESSIONS_BACK>='".intval($val)."'";
209 else
210 $arSqlSearch_h[] = "SESSIONS>='".intval($val)."'";
211 break;
212 case "SESSIONS2":
213 if ($arFilter["SESSIONS_BACK"]=="Y")
214 $arSqlSearch_h[] = "SESSIONS_BACK<='".intval($val)."'";
215 else
216 $arSqlSearch_h[] = "SESSIONS<='".intval($val)."'";
217 break;
218 case "HITS1":
219 if ($arFilter["HITS_BACK"]=="Y")
220 $arSqlSearch_h[] = "HITS_BACK>='".intval($val)."'";
221 else
222 $arSqlSearch_h[] = "HITS>='".intval($val)."'";
223 break;
224 case "HITS2":
225 if ($arFilter["HITS_BACK"]=="Y")
226 $arSqlSearch_h[] = "HITS_BACK<='".intval($val)."'";
227 else
228 $arSqlSearch_h[] = "HITS<='".intval($val)."'";
229 break;
230 case "COST1":
231 $arSqlSearch_h[] = "COST>='".doubleval($val)."'";
232 break;
233 case "COST2":
234 $arSqlSearch_h[] = "COST<='".doubleval($val)."'";
235 break;
236 case "REVENUE1":
237 $arSqlSearch_h[] = "REVENUE>='".doubleval($val)."'";
238 break;
239 case "REVENUE2":
240 $arSqlSearch_h[] = "REVENUE<='".doubleval($val)."'";
241 break;
242 case "BENEFIT1":
243 $arSqlSearch_h[] = "BENEFIT>='".doubleval($val)."'";
244 break;
245 case "BENEFIT2":
246 $arSqlSearch_h[] = "BENEFIT<='".doubleval($val)."'";
247 break;
248 case "ROI1":
249 $arSqlSearch_h[] = "ROI>='".doubleval($val)."'";
250 break;
251 case "ROI2":
252 $arSqlSearch_h[] = "ROI<='".doubleval($val)."'";
253 break;
254 case "ATTENT1":
255 if ($arFilter["ATTENT_BACK"]=="Y")
256 $arSqlSearch_h[] = "ATTENT_BACK>='".doubleval($val)."'";
257 else
258 $arSqlSearch_h[] = "ATTENT>='".doubleval($val)."'";
259 break;
260 break;
261 case "ATTENT2":
262 if ($arFilter["ATTENT_BACK"]=="Y")
263 $arSqlSearch_h[] = "ATTENT_BACK<='".doubleval($val)."'";
264 else
265 $arSqlSearch_h[] = "ATTENT<='".doubleval($val)."'";
266 break;
267 break;
268 case "VISITORS_PER_DAY1":
269 $arSqlSearch_h[] = "VISITORS_PER_DAY>='".doubleval($val)."'";
270 break;
271 case "VISITORS_PER_DAY2":
272 $arSqlSearch_h[] = "VISITORS_PER_DAY<='".doubleval($val)."'";
273 break;
274 case "DURATION1":
275 $arSqlSearch_h[] = "ADV_TIME>=".doubleval($val)."*86400";
276 break;
277 case "DURATION2":
278 $arSqlSearch_h[] = "ADV_TIME<=".doubleval($val)."*86400";
279 break;
280 case "CURRENCY":
281 $CURRENCY = $val;
282 break;
283 case "DESCRIPTION":
284 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
285 $arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
286 break;
287 }
288 }
289 }
290
291 $rate = 1;
292 $base_currency = GetStatisticBaseCurrency();
293 $view_currency = $base_currency;
294 if ($base_currency <> '')
295 {
296 if (CModule::IncludeModule("currency"))
297 {
298 if ($CURRENCY!=$base_currency && $CURRENCY <> '')
299 {
300 $rate = CCurrencyRates::GetConvertFactor($base_currency, $CURRENCY);
301 $view_currency = $CURRENCY;
302 }
303 }
304 }
305
306 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
307 foreach($arSqlSearch_h as $sqlWhere)
308 $strSqlSearch_h .= " and (".$sqlWhere.") ";
309
310 $group = false;
311 $find_group = ($find_group == '') ? "NOT_REF" : $find_group;
312
313 $arrFields_1 = array(
314 "C_TIME_FIRST", "C_TIME_LAST", "CURRENCY",
315 "DATE_FIRST", "DATE_LAST", "ADV_TIME",
316 "GUESTS", "NEW_GUESTS", "FAVORITES",
317 "C_HOSTS", "SESSIONS", "HITS",
318 "GUESTS_BACK", "FAVORITES_BACK", "HOSTS_BACK",
319 "SESSIONS_BACK", "HITS_BACK", "ATTENT",
320 "ATTENT_BACK", "NEW_VISITORS", "RETURNED_VISITORS",
321 "VISITORS_PER_DAY", "COST", "REVENUE",
322 "BENEFIT", "SESSION_COST", "VISITOR_COST", "ROI",
323 );
324 if ($find_group=="referer1") array_push($arrFields_1, "REFERER1");
325 if ($find_group=="referer2") array_push($arrFields_1, "REFERER2");
326
327 $arrFields_2 = array(
328 "GUESTS_TODAY", "NEW_GUESTS_TODAY", "FAVORITES_TODAY",
329 "C_HOSTS_TODAY", "SESSIONS_TODAY", "HITS_TODAY",
330 "GUESTS_BACK_TODAY", "FAVORITES_BACK_TODAY", "HOSTS_BACK_TODAY",
331 "SESSIONS_BACK_TODAY", "HITS_BACK_TODAY", "GUESTS_YESTERDAY",
332 "NEW_GUESTS_YESTERDAY", "FAVORITES_YESTERDAY", "C_HOSTS_YESTERDAY",
333 "SESSIONS_YESTERDAY", "HITS_YESTERDAY", "GUESTS_BACK_YESTERDAY",
334 "FAVORITES_BACK_YESTERDAY", "HOSTS_BACK_YESTERDAY", "SESSIONS_BACK_YESTERDAY",
335 "HITS_BACK_YESTERDAY", "GUESTS_BEF_YESTERDAY", "NEW_GUESTS_BEF_YESTERDAY",
336 "FAVORITES_BEF_YESTERDAY", "C_HOSTS_BEF_YESTERDAY", "SESSIONS_BEF_YESTERDAY",
337 "HITS_BEF_YESTERDAY", "GUESTS_BACK_BEF_YESTERDAY", "FAVORITES_BACK_BEF_YESTERDAY",
338 "HOSTS_BACK_BEF_YESTERDAY", "SESSIONS_BACK_BEF_YESTERDAY", "HITS_BACK_BEF_YESTERDAY",
339 "A.ID", "REFERER1", "REFERER2",
340 "A.PRIORITY", "A.EVENTS_VIEW", "A.DESCRIPTION",
341 "GUESTS_PERIOD", "C_HOSTS_PERIOD", "NEW_GUESTS_PERIOD",
342 "FAVORITES_PERIOD", "SESSIONS_PERIOD", "HITS_PERIOD",
343 "GUESTS_BACK_PERIOD", "HOSTS_BACK_PERIOD", "FAVORITES_BACK_PERIOD",
344 "SESSIONS_BACK_PERIOD", "HITS_BACK_PERIOD",
345 );
346
347 $arrFields = $arrFields_1;
348 if ($find_group=="NOT_REF")
349 $arrFields = array_merge($arrFields, $arrFields_2);
350
351 if ($order != "asc")
352 {
353 $order = "desc";
354 }
355 else
356 {
357 $order = "asc";
358 }
359
360 $key = array_search(strtoupper($by), $arrFields);
361 if ($key===NULL || $key===false)
362 $key = array_search("A.".strtoupper($by), $arrFields);
363
364 if ($key!==NULL && $key!==false)
365 $strSqlOrder = " ORDER BY ".$arrFields[$key];
366 elseif ($by == "s_dropdown")
367 $strSqlOrder = "ORDER BY A.ID desc, A.REFERER1, A.REFERER2";
368 elseif ($by == "s_referers")
369 $strSqlOrder = "ORDER BY A.REFERER1, A.REFERER2";
370 else
371 {
372 if ($find_group=="NOT_REF")
373 {
374 $strSqlOrder = " ORDER BY SESSIONS_TODAY $order, SESSIONS_YESTERDAY $order, SESSIONS_BEF_YESTERDAY $order, SESSIONS_PERIOD $order, SESSIONS ";
375 }
376 else
377 {
378 $strSqlOrder = " ORDER BY SESSIONS ";
379 }
380 }
381 $strSqlOrder .= " ".$order;
382
383 $limit = (intval($limit)>0) ? intval($limit) : intval(COption::GetOptionString('statistic','RECORDS_LIMIT'));
384
385 $sqlDays = "
386 -- TODAY
387 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY,0),0)) GUESTS_TODAY,
388 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_TODAY,
389 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES,0),0)) FAVORITES_TODAY,
390 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_TODAY,
391 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS,0),0)) SESSIONS_TODAY,
392 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS,0),0)) HITS_TODAY,
393 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_TODAY,
394 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_TODAY,
395 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_TODAY,
396 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_TODAY,
397 sum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS_BACK,0),0)) HITS_BACK_TODAY,
398
399 -- YESTERDAY
400 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY,0),0)) GUESTS_YESTERDAY,
401 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_YESTERDAY,
402 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES,0),0)) FAVORITES_YESTERDAY,
403 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_YESTERDAY,
404 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS,0),0)) SESSIONS_YESTERDAY,
405 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS,0),0)) HITS_YESTERDAY,
406 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_YESTERDAY,
407 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_YESTERDAY,
408 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_YESTERDAY,
409 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_YESTERDAY,
410 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS_BACK,0),0)) HITS_BACK_YESTERDAY,
411
412 -- THE DAY BEFORE YESTERDAY
413 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY,0),0)) GUESTS_BEF_YESTERDAY,
414 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.NEW_GUESTS,0),0)) NEW_GUESTS_BEF_YESTERDAY,
415 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES,0),0)) FAVORITES_BEF_YESTERDAY,
416 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.C_HOSTS_DAY,0),0)) C_HOSTS_BEF_YESTERDAY,
417 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS,0),0)) SESSIONS_BEF_YESTERDAY,
418 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS,0),0)) HITS_BEF_YESTERDAY,
419 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY_BACK,0),0)) GUESTS_BACK_BEF_YESTERDAY,
420 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES_BACK,0),0)) FAVORITES_BACK_BEF_YESTERDAY,
421 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HOSTS_DAY_BACK,0),0)) HOSTS_BACK_BEF_YESTERDAY,
422 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS_BACK,0),0)) SESSIONS_BACK_BEF_YESTERDAY,
423 sum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS_BACK,0),0)) HITS_BACK_BEF_YESTERDAY,
424 ";
425 if ($find_group=="NOT_REF") // no grouping
426 {
427 $strSql = "
428 SELECT
429 A.ID, A.REFERER1, A.REFERER2, A.PRIORITY, A.EVENTS_VIEW, A.DESCRIPTION,
430 A.DATE_FIRST C_TIME_FIRST,
431 A.DATE_LAST C_TIME_LAST,
432 '".$DB->ForSql($view_currency)."' CURRENCY,
433 ".$DB->DateToCharFunction("A.DATE_FIRST","SHORT")." DATE_FIRST,
434 ".$DB->DateToCharFunction("A.DATE_LAST","SHORT")." DATE_LAST,
435 UNIX_TIMESTAMP(ifnull(A.DATE_LAST,0))-UNIX_TIMESTAMP(ifnull(A.DATE_FIRST,0)) ADV_TIME,
436 $sqlDays
437
438 -- PERIOD
439 ".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS,0)'.$strT : 'A.GUESTS')." GUESTS_PERIOD,
440 ".($filter_period ? $strSqlPeriod.'ifnull(D.C_HOSTS,0)'.$strT : 'A.C_HOSTS')." C_HOSTS_PERIOD,
441 ".($filter_period ? $strSqlPeriod.'ifnull(D.NEW_GUESTS,0)'.$strT : 'A.NEW_GUESTS')." NEW_GUESTS_PERIOD,
442 ".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES,0)'.$strT : 'A.FAVORITES')." FAVORITES_PERIOD,
443 ".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS,0)'.$strT : 'A.SESSIONS')." SESSIONS_PERIOD,
444 ".($filter_period ? $strSqlPeriod.'ifnull(D.HITS,0)'.$strT : 'A.HITS')." HITS_PERIOD,
445 ".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS_DAY_BACK,0)'.$strT : 'A.GUESTS_BACK')." GUESTS_BACK_PERIOD,
446 ".($filter_period ? $strSqlPeriod.'ifnull(D.HOSTS_DAY_BACK,0)'.$strT : 'A.HOSTS_BACK')." HOSTS_BACK_PERIOD,
447 ".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES_BACK,0)'.$strT : 'A.FAVORITES')." FAVORITES_BACK_PERIOD,
448 ".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS_BACK,0)'.$strT : 'A.SESSIONS_BACK')." SESSIONS_BACK_PERIOD,
449 ".($filter_period ? $strSqlPeriod.'ifnull(D.HITS_BACK,0)'.$strT : 'A.HITS_BACK')." HITS_BACK_PERIOD,
450
451 -- TOTAL
452 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,
453
454 -- AUDIENCE
455 if(A.SESSIONS>0,round(A.HITS/A.SESSIONS,2),-1) ATTENT,
456 if(A.SESSIONS_BACK>0,round(A.HITS_BACK/A.SESSIONS_BACK,2),-1) ATTENT_BACK,
457 if(A.GUESTS>0,round((A.NEW_GUESTS/A.GUESTS)*100,2),-1) NEW_VISITORS,
458 if(A.GUESTS>0,round((A.GUESTS_BACK/A.GUESTS)*100,2),-1) RETURNED_VISITORS,
459 if(
460 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,
461
462 -- FINANCES
463 round(round(A.COST,2)*$rate,2) COST,
464 round(round(A.REVENUE,2)*$rate,2) REVENUE,
465 round(round(A.REVENUE-A.COST,2)*$rate,2) BENEFIT,
466 round(round(if(A.SESSIONS>0,A.COST/A.SESSIONS,0),2)*$rate,2) SESSION_COST,
467 round(round(if(A.GUESTS>0,A.COST/A.GUESTS,0),2)*$rate,2) VISITOR_COST,
468 if(A.COST>0,round(((A.REVENUE-A.COST)/A.COST)*100,2),-1) ROI
469
470 FROM
471 b_stat_adv A
472 LEFT JOIN b_stat_adv_day D ON (D.ADV_ID = A.ID)
473 WHERE
474 $strSqlSearch
475 GROUP BY
476 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
477 ";
478 }
479 else
480 {
481 if ($find_group=="referer1")
482 $group = "REFERER1";
483 else
484 $group = "REFERER2";
485
486 // total data
487 $strSql = "
488 SELECT
489 A.$group,
490 min(A.DATE_LAST) C_TIME_FIRST,
491 max(A.DATE_LAST) C_TIME_LAST,
492 '".$DB->ForSql($view_currency)."' CURRENCY,
493 ".$DB->DateToCharFunction("min(A.DATE_FIRST)","SHORT")." DATE_FIRST,
494 ".$DB->DateToCharFunction("max(A.DATE_LAST)","SHORT")." DATE_LAST,
495 UNIX_TIMESTAMP(max(ifnull(A.DATE_LAST,0)))-UNIX_TIMESTAMP(min(ifnull(A.DATE_FIRST,0))) ADV_TIME,
496
497 -- TOTAL
498 sum(A.GUESTS) GUESTS,
499 sum(A.NEW_GUESTS) NEW_GUESTS,
500 sum(A.FAVORITES) FAVORITES,
501 sum(A.C_HOSTS) C_HOSTS,
502 sum(A.SESSIONS) SESSIONS,
503 sum(A.HITS) HITS,
504 sum(A.GUESTS_BACK) GUESTS_BACK,
505 sum(A.FAVORITES_BACK) FAVORITES_BACK,
506 sum(A.HOSTS_BACK) HOSTS_BACK,
507 sum(A.SESSIONS_BACK) SESSIONS_BACK,
508 sum(A.HITS_BACK) HITS_BACK,
509
510 -- AUDIENCE
511 if(sum(A.SESSIONS)>0,round(sum(A.HITS)/sum(A.SESSIONS),2),-1) ATTENT,
512 if(sum(A.SESSIONS_BACK)>0,round(sum(A.HITS_BACK)/sum(A.SESSIONS_BACK),2),-1) ATTENT_BACK,
513 if(sum(A.GUESTS)>0,round((sum(A.NEW_GUESTS)/sum(A.GUESTS))*100,2),-1) NEW_VISITORS,
514 if(sum(A.GUESTS)>0,round((sum(A.GUESTS_BACK)/sum(A.GUESTS))*100,2),-1) RETURNED_VISITORS,
515 if(
516 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,
517
518 -- FINANCES
519 round(round(sum(A.COST),2)*$rate,2) COST,
520 round(round(sum(A.REVENUE),2)*$rate,2) REVENUE,
521 round(round((sum(A.REVENUE)-sum(A.COST)),2)*$rate,2) BENEFIT,
522 round(round(if(sum(A.SESSIONS)>0,sum(A.COST)/sum(A.SESSIONS),0),2)*$rate,2) SESSION_COST,
523 round(round(if(sum(A.GUESTS)>0,sum(A.COST)/sum(A.GUESTS),0),2)*$rate,2) VISITOR_COST,
524 if(sum(A.COST)>0,round(((sum(A.REVENUE)-sum(A.COST))/sum(A.COST))*100,2),-1) ROI
525
526 FROM
527 b_stat_adv A
528 WHERE
529 $strSqlSearch
530 GROUP BY
531 A.$group
532 ";
533
534 // period data
535 $strSql_days = "
536 SELECT
537 A.$group,
538 $sqlDays
539
540 -- PERIOD
541 ".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS,0)'.$strT : 'sum(A.GUESTS)')." GUESTS_PERIOD,
542 ".($filter_period ? $strSqlPeriod.'ifnull(D.C_HOSTS,0)'.$strT : 'sum(A.C_HOSTS)')." C_HOSTS_PERIOD,
543 ".($filter_period ? $strSqlPeriod.'ifnull(D.NEW_GUESTS,0)'.$strT : 'sum(A.NEW_GUESTS)')." NEW_GUESTS_PERIOD,
544 ".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES,0)'.$strT : 'sum(A.FAVORITES)')." FAVORITES_PERIOD,
545 ".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS,0)'.$strT : 'sum(A.SESSIONS)')." SESSIONS_PERIOD,
546 ".($filter_period ? $strSqlPeriod.'ifnull(D.HITS,0)'.$strT : 'sum(A.HITS)')." HITS_PERIOD,
547 ".($filter_period ? $strSqlPeriod.'ifnull(D.GUESTS_BACK,0)'.$strT : 'A.GUESTS_BACK')." GUESTS_BACK_PERIOD,
548 ".($filter_period ? $strSqlPeriod.'ifnull(D.HOSTS_BACK,0)'.$strT : 'A.HOSTS_BACK')." HOSTS_BACK_PERIOD,
549 ".($filter_period ? $strSqlPeriod.'ifnull(D.FAVORITES_BACK,0)'.$strT : 'sum(A.FAVORITES)')." FAVORITES_BACK_PERIOD,
550 ".($filter_period ? $strSqlPeriod.'ifnull(D.SESSIONS_BACK,0)'.$strT : 'sum(A.SESSIONS_BACK)')." SESSIONS_BACK_PERIOD,
551 ".($filter_period ? $strSqlPeriod.'ifnull(D.HITS_BACK,0)'.$strT : 'sum(A.HITS_BACK)')." HITS_BACK_PERIOD
552 FROM
553 b_stat_adv_day D
554 LEFT JOIN b_stat_adv A ON (D.ADV_ID = A.ID)
555 GROUP BY
556 A.$group
557 ";
558
559 $z = $DB->Query($strSql_days);
560 while ($zr = $z->Fetch())
561 {
562 $arrGROUP_DAYS[$zr[$group]] = $zr;
563 }
564 }
565 $strSql_res = $strSql;
566
567 $strSql .= "
568 HAVING
569 1=1
570 $strSqlSearch_h
571 $strSqlOrder
572 LIMIT $limit
573 ";
574
575 $res = $DB->Query($strSql);
576 $is_filtered = (IsFiltered($strSqlSearch) || $strSqlSearch_h <> '' || $group || $filter_period);
577 return $res;
578 }
579
580 public static function GetByID($ID)
581 {
582 $DB = CDatabase::GetModuleConnection('statistic');
583 $ID = intval($ID);
584 $strSql = "
585 SELECT
586 A.*,
587 round(A.COST,2) COST,
588 round(A.REVENUE,2) REVENUE,
589 ".$DB->DateToCharFunction("A.DATE_FIRST")." DATE_FIRST,
590 ".$DB->DateToCharFunction("A.DATE_LAST")." DATE_LAST
591 FROM
592 b_stat_adv A
593 WHERE
594 A.ID = '$ID'
595 ";
596 $res = $DB->Query($strSql);
597 return $res;
598 }
599
600 public static function GetEventList($ID, $by = 's_counter', $order = 'desc', $arFilter = [])
601 {
602 $DB = CDatabase::GetModuleConnection('statistic');
603 $find_group = $arFilter["GROUP"];
604 $ID = intval($ID);
605 $arSqlSearch = Array();
606 $arSqlSearch_h = Array();
607 $strSqlSearch_h = "";
608 $filter_period = false;
609 $strSqlPeriod = "";
610 $strT = "";
611 if (is_array($arFilter))
612 {
613 $date1 = $arFilter["DATE1_PERIOD"];
614 $date2 = $arFilter["DATE2_PERIOD"];
615 $date_from = MkDateTime(ConvertDateTime($date1,"D.M.Y"),"d.m.Y");
616 $date_to = MkDateTime(ConvertDateTime($date2,"D.M.Y")." 23:59","d.m.Y H:i");
617 if ($date1 <> '')
618 {
619 $filter_period = true;
620 if ($date2 <> '')
621 {
622 $strSqlPeriod = "sum(if(AE.DATE_STAT<FROM_UNIXTIME('$date_from'),0, if(AE.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
623 $strT=")))";
624 }
625 else
626 {
627 $strSqlPeriod = "sum(if(AE.DATE_STAT<FROM_UNIXTIME('$date_from'),0,";
628 $strT="))";
629 }
630 }
631 elseif ($date2 <> '')
632 {
633 $filter_period = true;
634 $strSqlPeriod = "sum(if(AE.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
635 $strT="))";
636 }
637 foreach ($arFilter as $key => $val)
638 {
639 if(is_array($val))
640 {
641 if(count($val) <= 0)
642 continue;
643 }
644 else
645 {
646 if( ((string)$val == '') || ($val === "NOT_REF") )
647 continue;
648 }
649 $match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
650 $key = strtoupper($key);
651 switch($key)
652 {
653 case "ID":
654 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
655 $arSqlSearch[] = GetFilterQuery("E.ID", $val, $match);
656 break;
657 case "EVENT1":
658 case "EVENT2":
659 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
660 $arSqlSearch[] = GetFilterQuery("E.".$key, $val, $match);
661 break;
662 case "KEYWORDS":
663 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
664 $arSqlSearch[] = GetFilterQuery("E.DESCRIPTION, E.NAME", $val, $match);
665 break;
666 case "COUNTER_PERIOD_1":
667 $arSqlSearch_h[] = "COUNTER_PERIOD>='".intval($val)."'";
668 break;
669 case "COUNTER_PERIOD_2":
670 $arSqlSearch_h[] = "COUNTER_PERIOD<='".intval($val)."'";
671 break;
672 case "COUNTER_BACK_PERIOD_1":
673 $arSqlSearch_h[] = "COUNTER_BACK_PERIOD>='".intval($val)."'";
674 break;
675 case "COUNTER_BACK_PERIOD_2":
676 $arSqlSearch_h[] = "COUNTER_BACK_PERIOD<='".intval($val)."'";
677 break;
678 case "COUNTER_ADV_DYNAMIC_LIST":
679 $arSqlSearch_h[] = "(COUNTER_PERIOD>='".intval($val)."' or COUNTER_BACK_PERIOD>='".intval($val)."')";
680 break;
681 case "MONEY1":
682 $arSqlSearch_h[] = "(MONEY+MONEY_BACK)>='".roundDB($val)."'";
683 break;
684 case "MONEY2":
685 $arSqlSearch_h[] = "(MONEY+MONEY_BACK)<='".roundDB($val)."'";
686 break;
687 case "MONEY_PERIOD_1":
688 $arSqlSearch_h[] = "(MONEY_PERIOD+MONEY_BACK_PERIOD)>='".roundDB($val)."'";
689 break;
690 case "MONEY_PERIOD_2":
691 $arSqlSearch_h[] = "(MONEY_PERIOD+MONEY_BACK_PERIOD)<='".roundDB($val)."'";
692 break;
693 }
694 }
695 }
696
697 if ($by == "s_id") $strSqlOrder = "ORDER BY E.ID";
698 elseif ($by == "s_event1") $strSqlOrder = "ORDER BY E.EVENT1";
699 elseif ($by == "s_event2") $strSqlOrder = "ORDER BY E.EVENT2";
700 elseif ($by == "s_sort") $strSqlOrder = "ORDER BY C_SORT";
701 elseif ($by == "s_name") $strSqlOrder = "ORDER BY E.NAME";
702 elseif ($by == "s_description") $strSqlOrder = "ORDER BY E.DESCRIPTION";
703 elseif ($by == "s_counter") $strSqlOrder = "ORDER BY COUNTER";
704 elseif ($by == "s_counter_back") $strSqlOrder = "ORDER BY COUNTER_BACK";
705 elseif ($by == "s_counter_period") $strSqlOrder = "ORDER BY COUNTER_PERIOD";
706 elseif ($by == "s_counter_back_period") $strSqlOrder = "ORDER BY COUNTER_BACK_PERIOD";
707 elseif ($by == "s_counter_today") $strSqlOrder = "ORDER BY COUNTER_TODAY";
708 elseif ($by == "s_counter_back_today") $strSqlOrder = "ORDER BY COUNTER_BACK_TODAY";
709 elseif ($by == "s_counter_yestoday") $strSqlOrder = "ORDER BY COUNTER_YESTERDAY";
710 elseif ($by == "s_counter_back_yestoday") $strSqlOrder = "ORDER BY COUNTER_BACK_YESTERDAY";
711 elseif ($by == "s_counter_bef_yestoday") $strSqlOrder = "ORDER BY COUNTER_BEF_YESTERDAY";
712 elseif ($by == "s_counter_back_bef_yestoday") $strSqlOrder = "ORDER BY COUNTER_BACK_BEF_YESTERDAY";
713 elseif ($by == "s_def")
714 {
715 $strSqlOrder = "
716 ORDER BY
717 E.C_SORT desc,
718 COUNTER_TODAY desc, COUNTER_BACK_TODAY desc,
719 COUNTER_YESTERDAY desc, COUNTER_BACK_YESTERDAY desc,
720 COUNTER_BEF_YESTERDAY desc, COUNTER_BACK_BEF_YESTERDAY desc,
721 ".($filter_period? "COUNTER_PERIOD desc, COUNTER_BACK_PERIOD desc,": "")."
722 COUNTER desc, COUNTER_BACK
723 ";
724 }
725 else
726 {
727 $strSqlOrder = "ORDER BY COUNTER";
728 }
729
730 if ($order != "asc")
731 {
732 $strSqlOrder .= " desc ";
733 }
734
735 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
736 foreach($arSqlSearch_h as $sqlWhere)
737 $strSqlSearch_h .= " and (".$sqlWhere.") ";
738
739 $find_group = ($find_group == '') ? "NOT_REF" : $find_group;
740
741 $sqlDays = "
742 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER,0),0)) COUNTER_TODAY,
743 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER,0),0)) COUNTER_YESTERDAY,
744 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER,0),0)) COUNTER_BEF_YESTERDAY,
745 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_TODAY,
746 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_YESTERDAY,
747 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_BEF_YESTERDAY,
748 ".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER,0)'.$strT : 'sum(AE.COUNTER)')." COUNTER_PERIOD,
749 ".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER_BACK,0)'.$strT : 'sum(AE.COUNTER_BACK)')." COUNTER_BACK_PERIOD,
750
751 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY,0),0)) MONEY_TODAY,
752 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY,0),0)) MONEY_YESTERDAY,
753 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY,0),0)) MONEY_BEF_YESTERDAY,
754 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_TODAY,
755 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_YESTERDAY,
756 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_BEF_YESTERDAY,
757 ".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY,0)'.$strT : 'sum(AE.MONEY)')." MONEY_PERIOD,
758 ".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY_BACK,0)'.$strT : 'sum(AE.MONEY_BACK)')." MONEY_BACK_PERIOD,
759 ";
760
761 if ($find_group=="NOT_REF") // no grouping
762 {
763 $strSql = "
764 SELECT
765 E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION,
766 sum(AE.COUNTER) COUNTER,
767 sum(AE.COUNTER_BACK) COUNTER_BACK,
768 sum(AE.MONEY) MONEY,
769 sum(AE.MONEY_BACK) MONEY_BACK,
770 $sqlDays
771 if (length(E.NAME)>0, E.NAME,
772 concat(ifnull(E.EVENT1,''),' / ',ifnull(E.EVENT2,''))) EVENT
773 FROM
774 b_stat_event E,
775 b_stat_adv_event_day AE
776 WHERE
777 $strSqlSearch
778 and E.ADV_VISIBLE = 'Y'
779 and AE.ADV_ID = '$ID'
780 and AE.EVENT_ID = E.ID
781 GROUP BY E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION
782 HAVING
783 1=1
784 $strSqlSearch_h
785 $strSqlOrder
786 LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
787 ";
788 }
789 else
790 {
791 if ($find_group=="event1")
792 $group = "E.EVENT1";
793 else
794 $group = "E.EVENT2";
795
796 $strSql = "
797 SELECT
798 $group,
799 sum(E.C_SORT) C_SORT,
800 $sqlDays
801 sum(AE.COUNTER) COUNTER,
802 sum(AE.COUNTER_BACK) COUNTER_BACK,
803 sum(AE.MONEY) MONEY,
804 sum(AE.MONEY_BACK) MONEY_BACK
805 FROM
806 b_stat_event E,
807 b_stat_adv_event_day AE
808 WHERE
809 $strSqlSearch
810 and E.ADV_VISIBLE = 'Y'
811 and AE.ADV_ID = '$ID'
812 and AE.EVENT_ID = E.ID
813 GROUP BY $group
814 HAVING
815 1=1
816 $strSqlSearch_h
817 $strSqlOrder
818 LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
819 ";
820 }
821
822 $res = $DB->Query($strSql);
823
824 return $res;
825 }
826
827 public static function GetEventListByReferer($value, $arFilter)
828 {
829 $DB = CDatabase::GetModuleConnection('statistic');
830 if ($arFilter["GROUP"]=="referer1")
831 $group = "A.REFERER1";
832 else
833 $group = "A.REFERER2";
834
835 $where = "";
836 $filter_period = false;
837 $strSqlPeriod = "";
838 $strT = "";
839
840 if (is_array($arFilter))
841 {
842 $date1 = $arFilter["DATE1_PERIOD"];
843 $date2 = $arFilter["DATE2_PERIOD"];
844 $date_from = MkDateTime(ConvertDateTime($date1,"D.M.Y"),"d.m.Y");
845 $date_to = MkDateTime(ConvertDateTime($date2,"D.M.Y")." 23:59","d.m.Y H:i");
846 if ($date1 <> '')
847 {
848 $filter_period = true;
849 if ($date2 <> '')
850 {
851 $strSqlPeriod = "sum(if(AE.DATE_STAT<FROM_UNIXTIME('$date_from'),0, if(AE.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
852 $strT=")))";
853 }
854 else
855 {
856 $strSqlPeriod = "sum(if(AE.DATE_STAT<FROM_UNIXTIME('$date_from'),0,";
857 $strT="))";
858 }
859 }
860 elseif ($date2 <> '')
861 {
862 $filter_period = true;
863 $strSqlPeriod = "sum(if(AE.DATE_STAT>FROM_UNIXTIME('$date_to'),0,";
864 $strT="))";
865 }
866 }
867
868 $arFilter["GROUP"]="";
869 $a = CAdv::GetList('', '', $arFilter, $is_filtered);
870 if ($is_filtered)
871 {
872 $str_id = "0";
873 while ($ar = $a->Fetch()) $str_id .= ",".intval($ar["ID"]);
874 $where = "and A.ID in ($str_id)";
875 }
876
877 $strSql = "
878 SELECT
879 E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION,
880 sum(AE.COUNTER) COUNTER,
881 sum(AE.COUNTER_BACK) COUNTER_BACK,
882 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER,0),0)) COUNTER_TODAY,
883 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER,0),0)) COUNTER_YESTERDAY,
884 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER,0),0)) COUNTER_BEF_YESTERDAY,
885 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_TODAY,
886 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_YESTERDAY,
887 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.COUNTER_BACK,0),0)) COUNTER_BACK_BEF_YESTERDAY,
888 ".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER,0)'.$strT : 'sum(AE.COUNTER)')." COUNTER_PERIOD,
889 ".($filter_period ? $strSqlPeriod.'ifnull(AE.COUNTER_BACK,0)'.$strT : 'sum(AE.COUNTER_BACK)')." COUNTER_BACK_PERIOD,
890
891 sum(AE.MONEY) MONEY,
892 sum(AE.MONEY_BACK) MONEY_BACK,
893 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY,0),0)) MONEY_TODAY,
894 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY,0),0)) MONEY_YESTERDAY,
895 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY,0),0)) MONEY_BEF_YESTERDAY,
896 sum(if(to_days(curdate())=to_days(AE.DATE_STAT),ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_TODAY,
897 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=1,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_YESTERDAY,
898 sum(if(to_days(curdate())-to_days(AE.DATE_STAT)=2,ifnull(AE.MONEY_BACK,0),0)) MONEY_BACK_BEF_YESTERDAY,
899 ".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY,0)'.$strT : 'sum(AE.MONEY)')." MONEY_PERIOD,
900 ".($filter_period ? $strSqlPeriod.'ifnull(AE.MONEY_BACK,0)'.$strT : 'sum(AE.MONEY_BACK)')." MONEY_BACK_PERIOD,
901
902 if (length(E.NAME)>0, E.NAME,
903 concat(ifnull(E.EVENT1,''),' / ',ifnull(E.EVENT2,''))) EVENT
904 FROM
905 b_stat_adv A,
906 b_stat_adv_event_day AE,
907 b_stat_event E
908 WHERE
909 1=1
910 $where
911 and $group='".$DB->ForSql($value,255)."'
912 and AE.ADV_ID = A.ID
913 and E.ID = AE.EVENT_ID
914 and E.ADV_VISIBLE = 'Y'
915 GROUP BY
916 E.ID, E.EVENT1, E.EVENT2, E.C_SORT, E.NAME, E.DESCRIPTION
917 ORDER BY
918 E.C_SORT desc,
919 COUNTER_TODAY desc, COUNTER_BACK_TODAY desc,
920 COUNTER_YESTERDAY desc, COUNTER_BACK_YESTERDAY desc,
921 COUNTER_BEF_YESTERDAY desc, COUNTER_BACK_BEF_YESTERDAY desc,
922 COUNTER_PERIOD desc, COUNTER_BACK_PERIOD desc,
923 COUNTER desc, COUNTER_BACK
924 LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
925 ";
926
927 $res = $DB->Query($strSql);
928 return $res;
929 }
930
931 public static function GetDynamicList($ADV_ID, $by = 's_date', $order = 'desc', &$arMaxMin = [], $arFilter = [])
932 {
933 $DB = CDatabase::GetModuleConnection('statistic');
934 $ADV_ID = intval($ADV_ID);
935 $arSqlSearch = Array();
936 $strSqlSearch = "";
937 if (is_array($arFilter))
938 {
939 foreach ($arFilter as $key => $val)
940 {
941 if(is_array($val))
942 {
943 if(count($val) <= 0)
944 continue;
945 }
946 else
947 {
948 if( ((string)$val == '') || ($val === "NOT_REF") )
949 continue;
950 }
951
952 $key = strtoupper($key);
953 switch($key)
954 {
955 case "DATE1":
956 if (CheckDateTime($val))
957 $arSqlSearch[] = "D.DATE_STAT>=".$DB->CharToDateFunction($val, "SHORT");
958 break;
959 case "DATE2":
960 if (CheckDateTime($val))
961 $arSqlSearch[] = "D.DATE_STAT<".$DB->CharToDateFunction($val, "SHORT")." + INTERVAL 1 DAY";
962 break;
963 }
964 }
965 }
966
967 foreach($arSqlSearch as $sqlWhere)
968 $strSqlSearch .= " and (".$sqlWhere.") ";
969
970 if ($by == "s_date")
971 $strSqlOrder = "ORDER BY D.DATE_STAT";
972 else
973 {
974 $strSqlOrder = "ORDER BY D.DATE_STAT";
975 }
976
977 if ($order!="asc")
978 {
979 $strSqlOrder .= " desc ";
980 }
981
982 $strSql = "
983 SELECT
984 ".$DB->DateToCharFunction("D.DATE_STAT","SHORT")." DATE_STAT,
985 DAYOFMONTH(D.DATE_STAT) DAY,
986 MONTH(D.DATE_STAT) MONTH,
987 YEAR(D.DATE_STAT) YEAR,
988 D.GUESTS_DAY GUESTS,
989 D.NEW_GUESTS NEW_GUESTS,
990 D.FAVORITES FAVORITES,
991 D.C_HOSTS_DAY C_HOSTS,
992 D.SESSIONS SESSIONS,
993 D.HITS HITS,
994 D.GUESTS_DAY_BACK GUESTS_BACK,
995 D.FAVORITES_BACK FAVORITES_BACK,
996 D.HOSTS_DAY_BACK HOSTS_BACK,
997 D.SESSIONS_BACK SESSIONS_BACK,
998 D.HITS_BACK HITS_BACK
999 FROM
1000 b_stat_adv_day D
1001 WHERE
1002 D.ADV_ID = $ADV_ID
1003 $strSqlSearch
1004 GROUP BY
1005 D.ADV_ID, D.DATE_STAT
1006 $strSqlOrder
1007 ";
1008 $res = $DB->Query($strSql);
1009
1010 $strSql = "
1011 SELECT
1012 max(D.DATE_STAT) DATE_LAST,
1013 min(D.DATE_STAT) DATE_FIRST,
1014 DAYOFMONTH(max(D.DATE_STAT)) MAX_DAY,
1015 MONTH(max(D.DATE_STAT)) MAX_MONTH,
1016 YEAR(max(D.DATE_STAT)) MAX_YEAR,
1017 DAYOFMONTH(min(D.DATE_STAT)) MIN_DAY,
1018 MONTH(min(D.DATE_STAT)) MIN_MONTH,
1019 YEAR(min(D.DATE_STAT)) MIN_YEAR
1020 FROM
1021 b_stat_adv_day D
1022 WHERE
1023 D.ADV_ID = $ADV_ID
1024 $strSqlSearch
1025 ";
1026
1027 $a = $DB->Query($strSql);
1028 $ar = $a->Fetch();
1029 $arMaxMin["MAX_DAY"] = $ar["MAX_DAY"];
1030 $arMaxMin["MAX_MONTH"] = $ar["MAX_MONTH"];
1031 $arMaxMin["MAX_YEAR"] = $ar["MAX_YEAR"];
1032 $arMaxMin["MIN_DAY"] = $ar["MIN_DAY"];
1033 $arMaxMin["MIN_MONTH"] = $ar["MIN_MONTH"];
1034 $arMaxMin["MIN_YEAR"] = $ar["MIN_YEAR"];
1035
1036 return $res;
1037 }
1038
1039 public static function GetDropDownList($strSqlOrder="ORDER BY REFERER1, REFERER2")
1040 {
1041 $DB = CDatabase::GetModuleConnection('statistic');
1042 $strSql = "
1043 SELECT
1044 ID as REFERENCE_ID,
1045 concat(ifnull(REFERER1,''),' / ',ifnull(REFERER2,''),' [',ID,']') as REFERENCE
1046 FROM
1047 b_stat_adv
1048 $strSqlOrder
1049 ";
1050 $res = $DB->Query($strSql);
1051 return $res;
1052 }
1053
1054 public static function GetSimpleList($by = 's_referer1', $order = 'asc', $arFilter = [])
1055 {
1056 $DB = CDatabase::GetModuleConnection('statistic');
1057 $arSqlSearch = Array();
1058 if (is_array($arFilter))
1059 {
1060 foreach ($arFilter as $key => $val)
1061 {
1062 if(is_array($val))
1063 {
1064 if(count($val) <= 0)
1065 continue;
1066 }
1067 else
1068 {
1069 if( ((string)$val == '') || ($val === "NOT_REF") )
1070 continue;
1071 }
1072 $match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter);
1073 $key = strtoupper($key);
1074 switch($key)
1075 {
1076 case "ID":
1077 $match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
1078 $arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
1079 break;
1080 case "REFERER1":
1081 case "REFERER2":
1082 case "DESCRIPTION":
1083 $match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
1084 $arSqlSearch[] = GetFilterQuery("A.".$key, $val, $match);
1085 break;
1086 }
1087 }
1088 }
1089
1090 $strSqlSearch = GetFilterSqlSearch($arSqlSearch);
1091
1092 $order = ($order != "desc" ? "asc" : "desc");
1093
1094 if ($by == "s_id") $strSqlOrder = "ORDER BY A.ID ".$order;
1095 elseif ($by == "s_referer1") $strSqlOrder = "ORDER BY A.REFERER1 ".$order.", A.REFERER2";
1096 elseif ($by == "s_referer2") $strSqlOrder = "ORDER BY A.REFERER2 ".$order;
1097 elseif ($by == "s_description") $strSqlOrder = "ORDER BY A.DESCRIPTION ".$order;
1098 else
1099 {
1100 $strSqlOrder = "ORDER BY A.REFERER1 ".$order.", A.REFERER2";
1101 }
1102
1103 $strSql = "
1104 SELECT
1105 A.ID,
1106 A.REFERER1,
1107 A.REFERER2,
1108 A.DESCRIPTION
1109 FROM
1110 b_stat_adv A
1111 WHERE
1112 $strSqlSearch
1113 $strSqlOrder
1114 LIMIT ".intval(COption::GetOptionString('statistic','RECORDS_LIMIT'))."
1115 ";
1116
1117 $res = $DB->Query($strSql);
1118 $is_filtered = (IsFiltered($strSqlSearch));
1119 return $res;
1120 }
1121}
CAdv
Определения adv.php:5
CAdv\GetAnalysisGraphArray_SQL
static GetAnalysisGraphArray_SQL($strSqlSearch, $DATA_TYPE)
Определения adv.php:6
CAdv\GetDropDownList
static GetDropDownList($strSqlOrder="ORDER BY REFERER1, REFERER2")
Определения adv.php:1039
CAdv\GetList
static GetList($by='', $order='desc', $arFilter=[], &$is_filtered=false, $limit='', &$arrGROUP_DAYS=[], &$strSql_res='')
Определения adv.php:78
CAdv\GetByID
static GetByID($ID)
Определения adv.php:580
CAdv\GetEventListByReferer
static GetEventListByReferer($value, $arFilter)
Определения adv.php:827
CAdv\GetDynamicList
static GetDynamicList($ADV_ID, $by='s_date', $order='desc', &$arMaxMin=[], $arFilter=[])
Определения adv.php:931
CAdv\GetEventList
static GetEventList($ID, $by='s_counter', $order='desc', $arFilter=[])
Определения adv.php:600
CAdv\GetSimpleList
static GetSimpleList($by='s_referer1', $order='asc', $arFilter=[])
Определения adv.php:1054
CAllAdv
Определения adv.php:4
CAllCurrencyRates\GetConvertFactor
static GetConvertFactor($curFrom, $curTo, $valDate="")
Определения currency_rate.php:407
$base_currency
$base_currency
Определения commerceml_g_run_cur.php:2
array
</td ></tr ></table ></td ></tr >< tr >< td class="bx-popup-label bx-width30"><?=GetMessage("PAGE_NEW_TAGS")?> array( $site)
Определения file_new.php:804
$res
$res
Определения filter_act.php:7
GetFilterSqlSearch
GetFilterSqlSearch($arSqlSearch=array(), $FilterLogic="FILTER_logic")
Определения filter_tools.php:397
GetFilterQuery
GetFilterQuery($field, $val, $procent="Y", $ex_sep=array(), $clob="N", $div_fields="Y", $clob_upper="N")
Определения filter_tools.php:383
IsFiltered
IsFiltered($strSqlSearch)
Определения filter_tools.php:337
$zr
$zr
Определения options.php:5
$ID
if($ajaxMode) $ID
Определения get_user.php:27
$_SERVER
$_SERVER["DOCUMENT_ROOT"]
Определения cron_frame.php:9
$DB
global $DB
Определения cron_frame.php:29
$z
$z
Определения options.php:31
ConvertDateTime
ConvertDateTime($datetime, $to_format=false, $from_site=false, $bSearchInSitesOnly=false)
Определения tools.php:724
CheckDateTime
CheckDateTime($datetime, $format=false)
Определения tools.php:398
MkDateTime
MkDateTime($strDT, $format="d.m.Y H:i:s")
Определения tools.php:1977
$order
$order
Определения payment.php:8
elseif
if( $daysToExpire >=0 &&$daysToExpire< 60 elseif)( $daysToExpire< 0)
Определения prolog_main_admin.php:393
$ar
$ar
Определения options.php:199
$key
if(empty($signedUserToken)) $key
Определения quickway.php:257
count
</p ></td >< td valign=top style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 2.0pt 0cm 2.0pt;height:9.0pt'>< p class=Normal align=center style='margin:0cm;margin-bottom:.0001pt;text-align:center;line-height:normal'>< a name=ТекстовоеПоле54 ></a ><?=($taxRate > count( $arTaxList) > 0) ? $taxRate."%"
Определения waybill.php:936
$a
else $a
Определения template.php:137
$val
$val
Определения options.php:1793
$CURRENCY
$CURRENCY
Определения result.php:6
GetStatisticBaseCurrency
GetStatisticBaseCurrency()
Определения stat_tools.php:368
$arFilter
$arFilter
Определения user_search.php:106