function GetStatList($by, $order, $arFilter) { $err_mess = CAdvBanner::err_mess() . "<br>Function: GetDynamicList<br>Line: "; global $DB; $arSqlSearch = array(); if (CAdvBanner::CheckDynamicFilter($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; } } $key = strtoupper($key); switch ($key) { case "DATE_1": $arSqlSearch[] = "D.DATE_STAT>=" . $DB->CharToDateFunction($val, "SHORT"); break; case "DATE_2": $arSqlSearch[] = "D.DATE_STAT<=" . $DB->CharToDateFunction($val . " 23:59:59", "FULL"); break; } } if (!empty($arFilter['BANNER_ID'])) { $arSqlSearch[] = CSQLWhere::_NumberIN("D.BANNER_ID", $arFilter['BANNER_ID']); } } } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); if ($by == "s_date") { $strSqlOrder = " ORDER BY D.DATE_STAT "; } elseif ($by == "s_visitors") { $strSqlOrder = " ORDER BY VISITOR_COUNT "; } elseif ($by == "s_clicks") { $strSqlOrder = " ORDER BY CLICK_COUNT "; } elseif ($by == "s_ctr") { $strSqlOrder = " ORDER BY CTR"; } elseif ($by == "s_show") { $strSqlOrder = " ORDER BY SHOW_COUNT "; } elseif ($by == "s_id") { $strSqlOrder = " ORDER BY D.BANNER_ID"; } else { $strSqlOrder = " ORDER BY DATE_STAT"; $by = "s_date"; } if ($order != "asc") { $strSqlOrder .= " desc "; } if ($by != "s_date") { $strSqlOrder .= ', DATE_STAT ASC'; } if ($arFilter['BANNER_SUMMA'] == 'Y') { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\t" . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . "\t\tDATE_STAT,\n\t\t\t\t\tSUM(D.SHOW_COUNT)\t\t\t\t\t\t\t\t\t\tSHOW_COUNT,\n\t\t\t\t\tSUM(D.CLICK_COUNT)\t\t\t\t\t\t\t\t\t\tCLICK_COUNT,\n\t\t\t\t\tSUM(D.VISITOR_COUNT)\t\t\t\t\t\t\t\t\tVISITOR_COUNT,\n\t\t\t\t\t" . CAdvBanner::getCTRSQL() . "\n\t\t\t\tFROM\n\t\t\t\t\tb_adv_banner_2_day D\n\t\t\t\tINNER JOIN b_adv_banner B ON (D.BANNER_ID = B.ID)\n\t\t\t\tINNER JOIN b_adv_contract C ON (B.CONTRACT_ID = C.ID)\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tGROUP by DATE_STAT\n\t\t\t\t{$strSqlOrder}\n\t\t\t\t"; } else { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\t" . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . "\t\tDATE_STAT,\n\t\t\t\t\tSUM(D.SHOW_COUNT)\t\t\t\t\t\t\t\t\t\tSHOW_COUNT,\n\t\t\t\t\tSUM(D.CLICK_COUNT)\t\t\t\t\t\t\t\t\t\tCLICK_COUNT,\n\t\t\t\t\tSUM(D.VISITOR_COUNT)\t\t\t\t\t\t\t\t\tVISITOR_COUNT,\n\t\t\t\t\tD.BANNER_ID,\n\t\t\t\t\tB.NAME\t\t\t\t\t\t\t\t\t\t\t\t\tBANNER_NAME,\n\t\t\t\t\t" . CAdvBanner::getCTRSQL() . "\n\t\t\t\tFROM\n\t\t\t\t\tb_adv_banner_2_day D\n\t\t\t\tINNER JOIN b_adv_banner B ON (D.BANNER_ID = B.ID)\n\t\t\t\tINNER JOIN b_adv_contract C ON (B.CONTRACT_ID = C.ID)\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tGROUP by D.DATE_STAT, D.BANNER_ID, B.NAME\n\t\t\t\t{$strSqlOrder}\n\t\t\t\t"; } return $DB->Query($strSql, false, $err_mess . __LINE__); }
public static function CleanUpDynamics() { set_time_limit(0); ignore_user_abort(true); $err_mess = CAdvBanner::err_mess() . "<br>Function: CleanUpDynamics<br>Line: "; global $DB; $DAYS = intval(COption::GetOptionString("advertising", "BANNER_DAYS")); $strSql = "DELETE FROM b_adv_banner_2_day WHERE to_days(now())-to_days(DATE_STAT)>={$DAYS}"; $DB->Query($strSql, false, $err_mess . __LINE__); $strSql = "OPTIMIZE TABLE b_adv_banner_2_day"; $DB->Query($strSql, false, $err_mess . __LINE__); return "CAdvBanner::CleanUpDynamics();"; }