/** * <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type">типов событий</a>.</p> * * * * * @param string &$by = "s_today_counter" Поле для сортировки. Возможные значения: <ul> <li> <b>s_id</b> - ID типа * события; </li> <li> <b>s_date_last</b> - дата последнего <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">события</a> данного типа; </li> <li> * <b>s_date_enter</b> - дата первого события данного типа; </li> <li> <b>s_today_counter</b> * - количество событий данного типа за сегодня; </li> <li> <b>s_yesterday_counter</b> * - количество событий данного типа за вчера; </li> <li> <b>s_b_yesterday_counter</b> - * количество событий данного типа за позавчера; </li> <li> <b>s_total_counter</b> - * суммарное количество событий данного типа; </li> <li> <b>s_period_counter</b> - * количество событий данного типа за указанный период * <nobr>(<i>filter</i>[<b>DATE1_PERIOD</b>], <i>filter</i>[<b>DATE2_PERIOD</b>])</nobr>; </li> <li> <b>s_name</b> - * название типа события; </li> <li> <b>s_event1</b> - <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type_id">идентификатор event1</a> типа * события; </li> <li> <b>s_event2</b> - идентификатор event2 типа события; </li> <li> * <b>s_event12</b> - сортировка по "EVENT1, EVENT2"; </li> <li> <b>s_chart</b> - сортировка по * "DIAGRAM_DEFAULT desc, TOTAL_COUNTER"; </li> <li> <b>s_stat</b> - сортировка по "TODAY_COUNTER desc, * YESTERDAY_COUNTER desc, B_YESTERDAY_COUNTER desc, TOTAL_COUNTER desc, PERIOD_COUNTER". </li> </ul> * * * * @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по * возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul> * * * * @param array $filter = array() Массив для фильтрации результирующего списка. В массиве * допустимы следующие ключи: <ul> <li> <b>ID</b>* - ID типа события; </li> <li> * <b>ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по <b>ID</b> * будет искаться вхождение; </li> <li> <b>EVENT1</b>* - идентификатор event1 типа * события; </li> <li> <b>EVENT1_EXACT_MATCH</b> - если значение равно "Y", то при * фильтрации по <b>EVENT1</b> будет искаться точное совпадение; </li> <li> * <b>EVENT2</b>* - идентификатор event2 типа события; </li> <li> <b>EVENT2_EXACT_MATCH</b> - * если значение равно "Y", то при фильтрации по <b>EVENT2</b> будет * искаться точное совпадение; </li> <li> <b>NAME</b>* - название типа события; * </li> <li> <b>NAME_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по * <b>NAME</b> будет искаться точное совпадение; </li> <li> <b>DESCRIPTION</b>* - * описание типа события; </li> <li> <b>DESCRIPTION_EXACT_MATCH</b> - если значение * равно "Y", то при фильтрации по <b>DESCRIPTION</b> будет искаться точное * совпадение; </li> <li> <b>DATE_ENTER_1</b> - начальное значение интервала для * поля "дата первого события данного типа"; </li> <li> <b>DATE_ENTER_2</b> - * конечное значение интервала для поля "дата первого события * данного типа"; </li> <li> <b>DATE_LAST_1</b> - начальное значение интервала для * поля "дата последнего события данного типа"; </li> <li> <b>DATE_LAST_2</b> - * конечное значение интервала для поля "дата последнего события * данного типа"; </li> <li> <b>DATE1_PERIOD</b> - начальное значение значение для * произвольного периода; </li> <li> <b>DATE2_PERIOD</b> - конечное значение * значение для произвольного периода; </li> <li> <b>COUNTER1</b> - начальное * значение интервала для поля "суммарное количество событий * данного типа"; </li> <li> <b>COUNTER2</b> - конечное значение интервала для * поля "суммарное количество событий данного типа"; </li> <li> <b>ADV_VISIBLE</b> * - флаг включать ли статистику по данному типу события в отчет по * рекламным кампаниям, возможные значения: <ul> <li> <b>Y</b> - включать; </li> * <li> <b>N</b> - не включать. </li> </ul> </li> <li> <b>DIAGRAM_DEFAULT</b> - флаг включать ли * данный тип события в круговую диаграмму и график по умолчанию, * возможные значения: <ul> <li> <b>Y</b> - включать; </li> <li> <b>N</b> - не включать. * </li> </ul> </li> <li> <b>KEEP_DAYS1</b> - начальное значение интервала для поля * "количество дней отведенное для хранения событий данного типа"; * </li> <li> <b>KEEP_DAYS2</b> - конечное значение интервала для поля * "количество дней отведенное для хранения событий данного типа"; * </li> <li> <b>DYNAMIC_KEEP_DAYS1</b> - начальное значение интервала для поля * "количество дней отведенное для хранения статистики по данному * типу события в разрезе по дням"; </li> <li> <b>DYNAMIC_KEEP_DAYS2</b> - конечное * значение интервала для поля "количество дней отведенное для * хранения статистики по данному типу события в разрезе по дням"; * </li> <li> <b>MONEY1</b> - начальное значение интервала для поля "суммарная * денежная сумма для данного типа событий"; </li> <li> <b>MONEY2</b> - конечное * значение интервала для поля "суммарная денежная сумма для * данного типа событий"; </li> <li> <b>CURRENCY</b> - трехсимвольный * идентификатор валюты для денежной суммы; </li> <li> <b>GROUP</b> - * группировка результирующего списка, возможные значения: <ul> <li> * <b>event1</b> - группировка по <i>event1</i>; </li> <li> <b>event2</b> - группировка по * <i>event2</i>. </li> </ul> </li> </ul> * - допускается <a * href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная логика</a> * * * * @param bool &$is_filtered Флаг отфильтрованности результирующего списка. Если значение * равно "true", то список был отфильтрован. * * * * @param mixed $limit = false Максимальное число типов событий которые будут выбраны в списке. * Если значение равно "false", то кол-во РК будет ограничено в * соответствии со значением параметра <b>Максимальное кол-во * показываемых записей в таблицах</b> из настроек модуля * "Статистика". * * * * @return CDBResult * * * <h4>Example</h4> * <pre> * <? * // получим данные только по тем типам событий * // у которых event1 = "download" * // а также получим дополнительные данные на декабрь 2007 года * $arFilter = array( * "DATE1_PERIOD" => "01.12.2007", * "DATE2_PERIOD" => "31.12.2007", * "EVENT1" => "download" * ); * * // получим список записей * $rs = <b>CStatEventType::GetList</b>( * ($by = "s_today_counter"), * ($order = "desc"), * $arFilter, * $is_filtered * ); * * // выведем все записи * while ($ar = $rs->Fetch()) * { * echo "<pre>"; print_r($ar); echo "</pre>"; * } * ?> * </pre> * * * * <h4>See Also</h4> * <ul> <li> <a * href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cstateventtype/getdynamiclist.php">CStatEventType::GetDynamicList</a> * </li> <li> <a * href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cstateventtype/getsimplelist.php">CStatEventType::GetSimpleList</a> * </li> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type">Термин "Тип * события"</a> </li> </ul> </ht<a name="examples"></a> * * * @static * @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cstateventtype/getlist.php * @author Bitrix */ public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, $LIMIT = false) { $err_mess = "File: " . __FILE__ . "<br>Line: "; $DB = CDatabase::GetModuleConnection('statistic'); $find_group = $arFilter["GROUP"]; if ($find_group != "event1" && $find_group != "event2" && $find_group != "total") { $find_group = ""; } $arSqlSearch = array(); $arSqlSearch_h = array(); $strSqlSearch_h = ""; $filter_period = false; $strSqlPeriod = ""; $strT = ""; $CURRENCY = ""; if (is_array($arFilter)) { ResetFilterLogic(); $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 (strlen($date1) > 0) { $filter_period = true; if (strlen($date2) > 0) { $strSqlPeriod = "if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0, if(D.DATE_STAT>FROM_UNIXTIME('{$date_to}'),0,"; $strT = "))"; } else { $strSqlPeriod = "if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0,"; $strT = ")"; } } elseif (strlen($date2) > 0) { $filter_period = true; $strSqlPeriod = "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 (strlen($val) <= 0 || $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." . $key, $val, $match); break; case "DATE_ENTER_1": if (CheckDateTime($val)) { $arSqlSearch[] = "E.DATE_ENTER>=" . $DB->CharToDateFunction($val, "SHORT"); } break; case "DATE_ENTER_2": if (CheckDateTime($val)) { $arSqlSearch[] = "E.DATE_ENTER<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY"; } break; case "DATE_LAST_1": if (CheckDateTime($val)) { $arSqlSearch_h[] = "max(D.DATE_LAST)>=" . $DB->CharToDateFunction($val, "SHORT"); } break; case "DATE_LAST_2": if (CheckDateTime($val)) { $arSqlSearch_h[] = "max(D.DATE_LAST)<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY"; } break; case "EVENT1": case "EVENT2": $match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("E." . $key, $val, $match); break; case "COUNTER1": $arSqlSearch_h[] = "TOTAL_COUNTER>='" . intval($val) . "'"; break; case "COUNTER2": $arSqlSearch_h[] = "TOTAL_COUNTER<='" . intval($val) . "'"; break; case "MONEY1": $arSqlSearch_h[] = "TOTAL_MONEY>='" . roundDB($val) . "'"; break; case "MONEY2": $arSqlSearch_h[] = "TOTAL_MONEY<='" . roundDB($val) . "'"; break; case "ADV_VISIBLE": case "DIAGRAM_DEFAULT": $arSqlSearch[] = $val == "Y" ? "E." . $key . "='Y'" : "E." . $key . "='N'"; break; case "DESCRIPTION": case "NAME": $match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y"; $arSqlSearch[] = GetFilterQuery("E." . $key, $val, $match); break; case "KEEP_DAYS1": $arSqlSearch[] = "E.KEEP_DAYS>='" . intval($val) . "'"; break; case "KEEP_DAYS2": $arSqlSearch[] = "E.KEEP_DAYS<='" . intval($val) . "'"; break; case "DYNAMIC_KEEP_DAYS1": $arSqlSearch[] = "E.DYNAMIC_KEEP_DAYS>='" . intval($val) . "'"; break; case "DYNAMIC_KEEP_DAYS2": $arSqlSearch[] = "E.DYNAMIC_KEEP_DAYS<='" . intval($val) . "'"; break; case "CURRENCY": $CURRENCY = $val; break; } } } $rate = 1; $base_currency = GetStatisticBaseCurrency(); $view_currency = $base_currency; if (strlen($base_currency) > 0) { if (CModule::IncludeModule("currency")) { if ($CURRENCY != $base_currency && strlen($CURRENCY) > 0) { $rate = CCurrencyRates::GetConvertFactor($base_currency, $CURRENCY); $view_currency = $CURRENCY; } } } if ($by == "s_id" && $find_group == "") { $strSqlOrder = "ORDER BY E.ID"; } elseif ($by == "s_date_last") { $strSqlOrder = "ORDER BY E_DATE_LAST"; } elseif ($by == "s_date_enter") { $strSqlOrder = "ORDER BY DATE_ENTER"; } elseif ($by == "s_today_counter") { $strSqlOrder = "ORDER BY TODAY_COUNTER"; } elseif ($by == "s_yesterday_counter") { $strSqlOrder = "ORDER BY YESTERDAY_COUNTER"; } elseif ($by == "s_b_yesterday_counter") { $strSqlOrder = "ORDER BY B_YESTERDAY_COUNTER"; } elseif ($by == "s_total_counter") { $strSqlOrder = "ORDER BY TOTAL_COUNTER"; } elseif ($by == "s_period_counter") { $strSqlOrder = "ORDER BY PERIOD_COUNTER"; } elseif ($by == "s_name" && $find_group == "") { $strSqlOrder = "ORDER BY E.NAME"; } elseif ($by == "s_event1" && $find_group == "") { $strSqlOrder = "ORDER BY E.EVENT1"; } elseif ($by == "s_event1" && $find_group == "event1") { $strSqlOrder = "ORDER BY E.EVENT1"; } elseif ($by == "s_event2" && $find_group == "") { $strSqlOrder = "ORDER BY E.EVENT2"; } elseif ($by == "s_event2" && $find_group == "event2") { $strSqlOrder = "ORDER BY E.EVENT2"; } elseif ($by == "s_event12" && $find_group == "") { $strSqlOrder = "ORDER BY E.EVENT1, E.EVENT2"; } elseif ($by == "s_chart" && $find_group == "") { $strSqlOrder = "ORDER BY E.DIAGRAM_DEFAULT desc, TOTAL_COUNTER "; } elseif ($by == "s_stat") { $strSqlOrder = "ORDER BY TODAY_COUNTER desc, YESTERDAY_COUNTER desc, B_YESTERDAY_COUNTER desc, TOTAL_COUNTER desc, PERIOD_COUNTER"; } else { $by = "s_today_counter"; $strSqlOrder = "ORDER BY TODAY_COUNTER desc, YESTERDAY_COUNTER desc, B_YESTERDAY_COUNTER desc, TOTAL_COUNTER desc, PERIOD_COUNTER"; } if ($order != "asc") { $strSqlOrder .= " desc "; $order = "desc"; } $strSqlSearch = GetFilterSqlSearch($arSqlSearch); foreach ($arSqlSearch_h as $sqlWhere) { $strSqlSearch_h .= " and (" . $sqlWhere . ") "; } $limit_sql = "LIMIT " . intval(COption::GetOptionString('statistic', 'RECORDS_LIMIT')); if (intval($LIMIT) > 0) { $limit_sql = "LIMIT " . intval($LIMIT); } if ($find_group == "") { $strSql = "\n\t\t\tSELECT\n\t\t\t\tE.ID,\n\t\t\t\tE.EVENT1,\n\t\t\t\tE.EVENT2,\n\t\t\t\tE.COUNTER,\n\t\t\t\tE.DIAGRAM_DEFAULT,\n\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\t\t\t\t\tCURRENCY,\n\t\t\t\t" . $DB->DateToCharFunction("E.DATE_ENTER") . "\t\t\t\t\t\tDATE_ENTER,\n\t\t\t\t" . $DB->DateToCharFunction("max(D.DATE_LAST)") . "\t\t\t\t\t\tDATE_LAST,\n\t\t\t\tmax(ifnull(D.DATE_LAST,'1980-01-01'))\t\t\t\t\t\t\tE_DATE_LAST,\n\t\t\t\tsum(ifnull(D.COUNTER,0))+ifnull(E.COUNTER,0)\t\t\t\t\t\tTOTAL_COUNTER,\n\t\t\t\tsum(round(ifnull(D.MONEY,0)*{$rate},2))+round(ifnull(E.MONEY,0)*{$rate},2)\t\t\tTOTAL_MONEY,\n\t\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.COUNTER,0),0))\t\t\tTODAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.COUNTER,0),0))\t\tYESTERDAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.COUNTER,0),0))\t\tB_YESTERDAY_COUNTER,\n\t\t\t\tsum(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.COUNTER,0)' . $strT : 0) . ")\t\tPERIOD_COUNTER,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=0,ifnull(D.MONEY,0),0)*{$rate},2))\tTODAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.MONEY,0),0)*{$rate},2))\tYESTERDAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.MONEY,0),0)*{$rate},2))\tB_YESTERDAY_MONEY,\n\t\t\t\tsum(round(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.MONEY,0)' . $strT : 0) . "*{$rate},2))\tPERIOD_MONEY,\n\t\t\t\tE.NAME,\n\t\t\t\tE.DESCRIPTION,\n\t\t\t\tif (length(E.NAME)>0, E.NAME, concat(E.EVENT1,' / ',ifnull(E.EVENT2,'')))\t\tEVENT\n\t\t\tFROM\n\t\t\t\tb_stat_event E\n\t\t\tLEFT JOIN b_stat_event_day D ON (D.EVENT_ID = E.ID)\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY E.ID\n\t\t\tHAVING\n\t\t\t\t'1'='1'\n\t\t\t\t{$strSqlSearch_h}\n\t\t\t{$strSqlOrder}\n\t\t\t{$limit_sql}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); } elseif ($find_group == "total") { $arResult = array("TOTAL_COUNTER" => 0, "TOTAL_MONEY" => 0, "TODAY_COUNTER" => 0, "YESTERDAY_COUNTER" => 0, "B_YESTERDAY_COUNTER" => 0, "PERIOD_COUNTER" => 0, "TODAY_MONEY" => 0, "YESTERDAY_MONEY" => 0, "B_YESTERDAY_MONEY" => 0, "PERIOD_MONEY" => 0); $strSql = "\n\t\t\tSELECT\n\t\t\t\tsum(ifnull(D.COUNTER,0))\t\t\t\t\t\t\t\tTOTAL_COUNTER,\n\t\t\t\tsum(round(ifnull(D.MONEY,0)*{$rate},2))\t\t\t\t\t\t\tTOTAL_MONEY,\n\t\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.COUNTER,0),0))\t\t\tTODAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.COUNTER,0),0))\t\tYESTERDAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.COUNTER,0),0))\t\tB_YESTERDAY_COUNTER,\n\t\t\t\tsum(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.COUNTER,0)' . $strT : 0) . ")\t\tPERIOD_COUNTER,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=0,ifnull(D.MONEY,0),0)*{$rate},2))\tTODAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.MONEY,0),0)*{$rate},2))\tYESTERDAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.MONEY,0),0)*{$rate},2))\tB_YESTERDAY_MONEY,\n\t\t\t\tsum(round(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.MONEY,0)' . $strT : 0) . "*{$rate},2))\tPERIOD_MONEY\n\t\t\tFROM\n\t\t\t\tb_stat_event E\n\t\t\t\tLEFT JOIN b_stat_event_day D ON (D.EVENT_ID = E.ID)\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\tHAVING\n\t\t\t\t'1'='1'\n\t\t\t\t{$strSqlSearch_h}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); if ($ar = $res->Fetch()) { foreach ($ar as $k => $v) { $arResult[$k] += $v; } } $strSql = "\n\t\t\tSELECT\n\t\t\t\tsum(ifnull(E.COUNTER,0))\t\tTOTAL_COUNTER,\n\t\t\t\tsum(round(ifnull(E.MONEY,0)*{$rate},2))\tTOTAL_MONEY\n\t\t\tFROM\n\t\t\t\tb_stat_event E\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); if ($ar = $res->Fetch()) { foreach ($ar as $k => $v) { $arResult[$k] += $v; } } $arResult["CURRENCY"] = $view_currency; $res = new CDBResult(); $res->InitFromArray(array($arResult)); } else { $arResult = array(); if ($find_group == "event1") { $group = "E.EVENT1"; } else { $group = "E.EVENT2"; } $strSql = "\n\t\t\tSELECT\n\t\t\t\t{$group}\t\t\t\t\t\t\t\t\t\t\tGROUPING_KEY,\n\t\t\t\t{$group},\n\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\t\t\t\t\tCURRENCY,\n\t\t\t\t" . $DB->DateToCharFunction("min(E.DATE_ENTER)") . "\t\t\t\t\tDATE_ENTER,\n\t\t\t\t" . $DB->DateToCharFunction("max(D.DATE_LAST)") . "\t\t\t\t\t\tDATE_LAST,\n\t\t\t\tmax(ifnull(D.DATE_LAST,'1980-01-01'))\t\t\t\t\t\t\tE_DATE_LAST,\n\t\t\t\tsum(ifnull(D.COUNTER,0))\t\t\t\t\t\t\t\tTOTAL_COUNTER,\n\t\t\t\tsum(round(ifnull(D.MONEY,0)*{$rate},2))\t\t\t\t\t\t\tTOTAL_MONEY,\n\t\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.COUNTER,0),0))\t\t\tTODAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.COUNTER,0),0))\t\tYESTERDAY_COUNTER,\n\t\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.COUNTER,0),0))\t\tB_YESTERDAY_COUNTER,\n\t\t\t\tsum(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.COUNTER,0)' . $strT : 0) . ")\t\tPERIOD_COUNTER,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=0,ifnull(D.MONEY,0),0)*{$rate},2))\tTODAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.MONEY,0),0)*{$rate},2))\tYESTERDAY_MONEY,\n\t\t\t\tsum(round(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.MONEY,0),0)*{$rate},2))\tB_YESTERDAY_MONEY,\n\t\t\t\tsum(round(" . ($filter_period ? $strSqlPeriod . 'ifnull(D.MONEY,0)' . $strT : 0) . "*{$rate},2))\tPERIOD_MONEY\n\t\t\tFROM\n\t\t\t\tb_stat_event E\n\t\t\tLEFT JOIN b_stat_event_day D ON (D.EVENT_ID = E.ID)\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY {$group}\n\t\t\tHAVING\n\t\t\t\t'1'='1'\n\t\t\t\t{$strSqlSearch_h}\n\t\t\t{$strSqlOrder}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); while ($ar = $res->Fetch()) { $arResult[$ar["GROUPING_KEY"]] = $ar; } $strSql = "\n\t\t\tSELECT\n\t\t\t\t{$group}\t\t\t\t\t\t\tGROUPING_KEY,\n\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\tCURRENCY,\n\t\t\t\tsum(ifnull(E.COUNTER,0))\t\t\t\tCOUNTER,\n\t\t\t\tsum(round(ifnull(E.MONEY,0)*{$rate},2))\t\t\tTOTAL_MONEY,\n\t\t\t\t" . $DB->DateToCharFunction("min(E.DATE_ENTER)") . "\tDATE_ENTER\n\t\t\tFROM\n\t\t\t\tb_stat_event E\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tGROUP BY {$group}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); while ($ar = $res->Fetch()) { if (array_key_exists($ar["GROUPING_KEY"], $arResult)) { $arResult[$ar["GROUPING_KEY"]]["TOTAL_COUNTER"] += $ar["COUNTER"]; $arResult[$ar["GROUPING_KEY"]]["TOTAL_MONEY"] += $ar["MONEY"]; } else { $arResult[$ar["GROUPING_KEY"]] = array("GROUPING_KEY" => $ar["GROUPING_KEY"], $find_group == "event1" ? "EVENT1" : "EVENT2" => $ar["GROUPING_KEY"], "CURRENCY" => $ar["CURRENCY"], "DATE_ENTER" => $ar["DATE_ENTER"], "TOTAL_COUNTER" => $ar["COUNTER"], "TOTAL_MONEY" => $ar["MONEY"], "TODAY_COUNTER" => 0, "YESTERDAY_COUNTER" => 0, "B_YESTERDAY_COUNTER" => 0, "PERIOD_COUNTER" => 0, "TODAY_MONEY" => 0, "YESTERDAY_MONEY" => 0, "B_YESTERDAY_MONEY" => 0, "PERIOD_MONEY" => 0); /*DATE_LAST,E_DATE_LAST,*/ } } $res = new CDBResult(); $res->InitFromArray($arResult); } $is_filtered = IsFiltered($strSqlSearch) || $filter_period || strlen($strSqlSearch_h) > 0 || $find_group != ""; return $res; }
$arParams['CART_PAGE'] = trim($arParams['CART_PAGE']); $arParams['PAY_IMMED'] = isset($arParams['PAY_IMMED']) && $arParams['PAY_IMMED'] == 'Y'; $arResult = array(); $arResult['ACCOUNT'] = array(); $arResult['CURRENCIES'] = array(); $arResult['ERROR'] = ''; $arResult['LANG_CURRENCY'] = CSaleLang::GetLangCurrency(SITE_ID); $arResult['REQUEST_AMOUNT'] = $_REQUEST['amount'] > 0 ? round(str_replace(',', '.', trim($_REQUEST['amount'])), 2) : ''; $arResult['REQUEST_ACCOUNT'] = isset($_REQUEST['account']) ? $_REQUEST['account'] : $arParams['DEFAULT_CURRENCY']; $arResult['REQUEST_PAY_SYSTEM'] = isset($_REQUEST['pay_system']) ? $_REQUEST['pay_system'] : 0; $i = 0; $rsCurrency = CCurrency::GetList($by = 'name', $order = 'desc', LANGUAGE_ID); while ($arCurrency = $rsCurrency->Fetch()) { $arCurrency['ID'] = ++$i; if ($arCurrency['CURRENCY'] != $arResult['LANG_CURRENCY']) { $arCurrency['FACTOR'] = round(CCurrencyRates::GetConvertFactor($arCurrency['CURRENCY'], $arResult['LANG_CURRENCY']), 2); } else { $arCurrency['FACTOR'] = 1; } $arResult['CURRENCIES'][$arCurrency['CURRENCY']] = $arCurrency; if (empty($arParams['ALLOWED_CURRENCY']) || in_array($arCurrency['CURRENCY'], $arParams['ALLOWED_CURRENCY'])) { $arResult['ACCOUNT'][$arCurrency['CURRENCY']] = array(); } } if ($arParams['PAY_IMMED']) { if (isset($arParams['PERSON_TYPE']) && $arParams['PERSON_TYPE'] > 0) { $arResult['PAY_SYSTEMS'] = array(); $dbPaySysAction = CSalePaySystemAction::GetList(array(), array('PS_ACTIVE' => 'Y', 'PERSON_TYPE_ID' => $arParams['PERSON_TYPE']), false, false, array('*')); while ($arPaySysAction = $dbPaySysAction->fetch()) { if (intval($arPaySysAction['LOGOTIP']) > 0) { $arPaySysAction['LOGOTIP'] = CFile::GetFileArray($arPaySysAction['LOGOTIP']);
protected static function updateBaseRates($currency = '', $updateCurrency = '') { global $DB; if ($currency === '') { $currency = (string) Currency\CurrencyManager::getBaseCurrency(); } if ($currency === '') { return; } if ($updateCurrency != '') { $factor = 1; if ($updateCurrency != $currency) { $factor = CCurrencyRates::GetConvertFactor($updateCurrency, $currency); } $query = "update b_catalog_currency set CURRENT_BASE_RATE = " . (double) $factor . " where CURRENCY = '" . $updateCurrency . "'"; $DB->Query($query, false, 'File: ' . __FILE__ . '<br>Line: ' . __LINE__); } else { $query = "select CURRENCY from b_catalog_currency"; $currencyIterator = $DB->Query($query, false, 'File: ' . __FILE__ . '<br>Line: ' . __LINE__); while ($oneCurrency = $currencyIterator->Fetch()) { $factor = 1; if ($oneCurrency['CURRENCY'] != $currency) { $factor = CCurrencyRates::GetConvertFactor($oneCurrency['CURRENCY'], $currency); } $query = "update b_catalog_currency set CURRENT_BASE_RATE = " . (double) $factor . " where CURRENCY = '" . $oneCurrency['CURRENCY'] . "'"; $DB->Query($query, false, 'File: ' . __FILE__ . '<br>Line: ' . __LINE__); } } }
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, $limit = "", &$arrGROUP_DAYS, &$strSql_res) { $err_mess = "File: " . __FILE__ . "<br>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 (strlen($date1) > 0) { $filter_period = true; if (strlen($date2) > 0) { $strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0, if(D.DATE_STAT>FROM_UNIXTIME('{$date_to}'),0,"; $strT = ")))"; } else { $strSqlPeriod = "sum(if(D.DATE_STAT<FROM_UNIXTIME('{$date_from}'),0,"; $strT = "))"; } } elseif (strlen($date2) > 0) { $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 (strlen($val) <= 0 || $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 (strlen($base_currency) > 0) { if (CModule::IncludeModule("currency")) { if ($CURRENCY != $base_currency && strlen($CURRENCY) > 0) { $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 = strlen($find_group) <= 0 ? "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"; } $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 "; } $by = "BY_DEFAULT"; } $strSqlOrder .= " " . $order; $limit = intval($limit) > 0 ? intval($limit) : intval(COption::GetOptionString('statistic', 'RECORDS_LIMIT')); $sqlDays = "\n\t\t\t-- TODAY\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY,0),0))\t\t\tGUESTS_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.NEW_GUESTS,0),0))\t\t\tNEW_GUESTS_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES,0),0))\t\t\tFAVORITES_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.C_HOSTS_DAY,0),0))\t\t\tC_HOSTS_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS,0),0))\t\t\t\tSESSIONS_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS,0),0))\t\t\t\t\tHITS_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.GUESTS_DAY_BACK,0),0))\t\tGUESTS_BACK_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.FAVORITES_BACK,0),0))\t\tFAVORITES_BACK_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HOSTS_DAY_BACK,0),0))\t\tHOSTS_BACK_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.SESSIONS_BACK,0),0))\t\tSESSIONS_BACK_TODAY,\n\t\t\tsum(if(to_days(curdate())=to_days(D.DATE_STAT),ifnull(D.HITS_BACK,0),0))\t\t\tHITS_BACK_TODAY,\n\n\t\t\t-- YESTERDAY\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY,0),0))\t\t\tGUESTS_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.NEW_GUESTS,0),0))\t\t\tNEW_GUESTS_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES,0),0))\t\t\tFAVORITES_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.C_HOSTS_DAY,0),0))\t\tC_HOSTS_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS,0),0))\t\t\tSESSIONS_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS,0),0))\t\t\t\tHITS_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.GUESTS_DAY_BACK,0),0))\tGUESTS_BACK_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.FAVORITES_BACK,0),0))\t\tFAVORITES_BACK_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HOSTS_DAY_BACK,0),0))\t\tHOSTS_BACK_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.SESSIONS_BACK,0),0))\t\tSESSIONS_BACK_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=1,ifnull(D.HITS_BACK,0),0))\t\t\tHITS_BACK_YESTERDAY,\n\n\t\t\t-- THE DAY BEFORE YESTERDAY\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY,0),0))\t\t\tGUESTS_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.NEW_GUESTS,0),0))\t\t\tNEW_GUESTS_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES,0),0))\t\t\tFAVORITES_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.C_HOSTS_DAY,0),0))\t\tC_HOSTS_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS,0),0))\t\t\tSESSIONS_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS,0),0))\t\t\t\tHITS_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.GUESTS_DAY_BACK,0),0))\tGUESTS_BACK_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.FAVORITES_BACK,0),0))\t\tFAVORITES_BACK_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HOSTS_DAY_BACK,0),0))\t\tHOSTS_BACK_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.SESSIONS_BACK,0),0))\t\tSESSIONS_BACK_BEF_YESTERDAY,\n\t\t\tsum(if(to_days(curdate())-to_days(D.DATE_STAT)=2,ifnull(D.HITS_BACK,0),0))\t\t\tHITS_BACK_BEF_YESTERDAY,\n\t\t\t"; if ($find_group == "NOT_REF") { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tA.ID, A.REFERER1, A.REFERER2, A.PRIORITY, A.EVENTS_VIEW, A.DESCRIPTION,\n\t\t\t\t\tA.DATE_FIRST C_TIME_FIRST,\n\t\t\t\t\tA.DATE_LAST C_TIME_LAST,\n\t\t\t\t\t'" . $DB->ForSql($view_currency) . "' CURRENCY,\n\t\t\t\t\t" . $DB->DateToCharFunction("A.DATE_FIRST", "SHORT") . " DATE_FIRST,\n\t\t\t\t\t" . $DB->DateToCharFunction("A.DATE_LAST", "SHORT") . " DATE_LAST,\n\t\t\t\t\tUNIX_TIMESTAMP(ifnull(A.DATE_LAST,0))-UNIX_TIMESTAMP(ifnull(A.DATE_FIRST,0)) ADV_TIME,\n\t\t\t\t\t{$sqlDays}\n\n\t\t\t\t\t-- PERIOD\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.GUESTS,0)' . $strT : 'A.GUESTS') . " GUESTS_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.C_HOSTS,0)' . $strT : 'A.C_HOSTS') . " C_HOSTS_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.NEW_GUESTS,0)' . $strT : 'A.NEW_GUESTS') . " NEW_GUESTS_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.FAVORITES,0)' . $strT : 'A.FAVORITES') . " FAVORITES_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.SESSIONS,0)' . $strT : 'A.SESSIONS') . " SESSIONS_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HITS,0)' . $strT : 'A.HITS') . " HITS_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.GUESTS_DAY_BACK,0)' . $strT : 'A.GUESTS_BACK') . " GUESTS_BACK_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HOSTS_DAY_BACK,0)' . $strT : 'A.HOSTS_BACK') . " HOSTS_BACK_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.FAVORITES_BACK,0)' . $strT : 'A.FAVORITES') . " FAVORITES_BACK_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.SESSIONS_BACK,0)' . $strT : 'A.SESSIONS_BACK') . " SESSIONS_BACK_PERIOD,\n\t\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HITS_BACK,0)' . $strT : 'A.HITS_BACK') . " HITS_BACK_PERIOD,\n\n\t\t\t\t\t-- TOTAL\n\t\t\t\t\tA.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,\n\n\t\t\t\t\t-- AUDIENCE\n\t\t\t\t\tif(A.SESSIONS>0,round(A.HITS/A.SESSIONS,2),-1) ATTENT,\n\t\t\t\t\tif(A.SESSIONS_BACK>0,round(A.HITS_BACK/A.SESSIONS_BACK,2),-1) ATTENT_BACK,\n\t\t\t\t\tif(A.GUESTS>0,round((A.NEW_GUESTS/A.GUESTS)*100,2),-1) NEW_VISITORS,\n\t\t\t\t\tif(A.GUESTS>0,round((A.GUESTS_BACK/A.GUESTS)*100,2),-1) RETURNED_VISITORS,\n\t\t\t\t\tif(\n\t\t\t\t\tround((((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,\n\n\t\t\t\t\t-- FINANCES\n\t\t\t\t\tround(round(A.COST,2)*{$rate},2) COST,\n\t\t\t\t\tround(round(A.REVENUE,2)*{$rate},2) REVENUE,\n\t\t\t\t\tround(round(A.REVENUE-A.COST,2)*{$rate},2) BENEFIT,\n\t\t\t\t\tround(round(if(A.SESSIONS>0,A.COST/A.SESSIONS,0),2)*{$rate},2) SESSION_COST,\n\t\t\t\t\tround(round(if(A.GUESTS>0,A.COST/A.GUESTS,0),2)*{$rate},2) VISITOR_COST,\n\t\t\t\t\tif(A.COST>0,round(((A.REVENUE-A.COST)/A.COST)*100,2),-1) ROI\n\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_adv A\n\t\t\t\tLEFT JOIN b_stat_adv_day D ON (D.ADV_ID = A.ID)\n\t\t\t\tWHERE\n\t\t\t\t\t{$strSqlSearch}\n\t\t\t\tGROUP BY\n\t\t\t\t\tA.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\n\t\t\t"; } else { if ($find_group == "referer1") { $group = "REFERER1"; } else { $group = "REFERER2"; } // total data $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tA.{$group},\n\t\t\t\t\tmin(A.DATE_LAST)\t\t\t\t\t\t\t\t\t\t\tC_TIME_FIRST,\n\t\t\t\t\tmax(A.DATE_LAST)\t\t\t\t\t\t\t\t\t\t\tC_TIME_LAST,\n\t\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\t\t\t\t\t\t\tCURRENCY,\n\t\t\t\t\t" . $DB->DateToCharFunction("min(A.DATE_FIRST)", "SHORT") . "\tDATE_FIRST,\n\t\t\t\t\t" . $DB->DateToCharFunction("max(A.DATE_LAST)", "SHORT") . "\t\tDATE_LAST,\n\t\t\t\t\tUNIX_TIMESTAMP(max(ifnull(A.DATE_LAST,0)))-UNIX_TIMESTAMP(min(ifnull(A.DATE_FIRST,0)))\tADV_TIME,\n\n\t\t\t\t\t-- TOTAL\n\t\t\t\t\tsum(A.GUESTS)\t\t\tGUESTS,\n\t\t\t\t\tsum(A.NEW_GUESTS)\t\tNEW_GUESTS,\n\t\t\t\t\tsum(A.FAVORITES)\t\tFAVORITES,\n\t\t\t\t\tsum(A.C_HOSTS)\t\t\tC_HOSTS,\n\t\t\t\t\tsum(A.SESSIONS)\t\t\tSESSIONS,\n\t\t\t\t\tsum(A.HITS)\t\t\t\tHITS,\n\t\t\t\t\tsum(A.GUESTS_BACK)\t\tGUESTS_BACK,\n\t\t\t\t\tsum(A.FAVORITES_BACK)\tFAVORITES_BACK,\n\t\t\t\t\tsum(A.HOSTS_BACK)\t\tHOSTS_BACK,\n\t\t\t\t\tsum(A.SESSIONS_BACK)\tSESSIONS_BACK,\n\t\t\t\t\tsum(A.HITS_BACK)\t\tHITS_BACK,\n\n\t\t\t\t\t-- AUDIENCE\n\t\t\t\t\tif(sum(A.SESSIONS)>0,round(sum(A.HITS)/sum(A.SESSIONS),2),-1)\t\t\t\t\tATTENT,\n\t\t\t\t\tif(sum(A.SESSIONS_BACK)>0,round(sum(A.HITS_BACK)/sum(A.SESSIONS_BACK),2),-1)\tATTENT_BACK,\n\t\t\t\t\tif(sum(A.GUESTS)>0,round((sum(A.NEW_GUESTS)/sum(A.GUESTS))*100,2),-1)\t\t\tNEW_VISITORS,\n\t\t\t\t\tif(sum(A.GUESTS)>0,round((sum(A.GUESTS_BACK)/sum(A.GUESTS))*100,2),-1)\t\t\tRETURNED_VISITORS,\n\t\t\t\t\tif(\n\t\t\t\t\tround((((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,\n\n\t\t\t\t\t-- FINANCES\n\t\t\t\t\tround(round(sum(A.COST),2)*{$rate},2)\t\t\t\t\t\t\t\t\t\t\t\tCOST,\n\t\t\t\t\tround(round(sum(A.REVENUE),2)*{$rate},2)\t\t\t\t\t\t\t\t\t\t\tREVENUE,\n\t\t\t\t\tround(round((sum(A.REVENUE)-sum(A.COST)),2)*{$rate},2)\t\t\t\t\t\t\tBENEFIT,\n\t\t\t\t\tround(round(if(sum(A.SESSIONS)>0,sum(A.COST)/sum(A.SESSIONS),0),2)*{$rate},2)\t\tSESSION_COST,\n\t\t\t\t\tround(round(if(sum(A.GUESTS)>0,sum(A.COST)/sum(A.GUESTS),0),2)*{$rate},2)\t\t\tVISITOR_COST,\n\t\t\t\t\tif(sum(A.COST)>0,round(((sum(A.REVENUE)-sum(A.COST))/sum(A.COST))*100,2),-1)\tROI\n\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_adv A\n\t\t\t\tWHERE\n\t\t\t\t\t{$strSqlSearch}\n\t\t\t\tGROUP BY\n\t\t\t\t\tA.{$group}\n\t\t\t"; // period data $strSql_days = "\n\t\t\t\tSELECT\n\t\t\t\tA.{$group},\n\t\t\t\t{$sqlDays}\n\n\t\t\t\t-- PERIOD\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.GUESTS,0)' . $strT : 'sum(A.GUESTS)') . "\t\t\t\tGUESTS_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.C_HOSTS,0)' . $strT : 'sum(A.C_HOSTS)') . "\t\t\t\tC_HOSTS_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.NEW_GUESTS,0)' . $strT : 'sum(A.NEW_GUESTS)') . "\t\tNEW_GUESTS_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.FAVORITES,0)' . $strT : 'sum(A.FAVORITES)') . "\t\t\tFAVORITES_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.SESSIONS,0)' . $strT : 'sum(A.SESSIONS)') . "\t\t\tSESSIONS_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HITS,0)' . $strT : 'sum(A.HITS)') . "\t\t\t\t\tHITS_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.GUESTS_BACK,0)' . $strT : 'A.GUESTS_BACK') . "\t\t\tGUESTS_BACK_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HOSTS_BACK,0)' . $strT : 'A.HOSTS_BACK') . "\t\t\tHOSTS_BACK_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.FAVORITES_BACK,0)' . $strT : 'sum(A.FAVORITES)') . "\tFAVORITES_BACK_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.SESSIONS_BACK,0)' . $strT : 'sum(A.SESSIONS_BACK)') . "\t\tSESSIONS_BACK_PERIOD,\n\t\t\t\t" . ($filter_period ? $strSqlPeriod . 'ifnull(D.HITS_BACK,0)' . $strT : 'sum(A.HITS_BACK)') . "\t\t\tHITS_BACK_PERIOD\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_adv_day D\n\t\t\t\tLEFT JOIN b_stat_adv A ON (D.ADV_ID = A.ID)\n\t\t\t\tGROUP BY\n\t\t\t\t\tA.{$group}\n\t\t\t\t"; $z = $DB->Query($strSql_days, false, $err_mess . __LINE__); while ($zr = $z->Fetch()) { $arrGROUP_DAYS[$zr[$group]] = $zr; } } $strSql_res = $strSql; $strSql .= "\n\t\t\t\tHAVING\n\t\t\t\t\t1=1\n\t\t\t\t{$strSqlSearch_h}\n\t\t\t\t{$strSqlOrder}\n\t\t\t\tLIMIT {$limit}\n\t\t\t"; $res = $DB->Query($strSql, false, $err_mess . __LINE__); $is_filtered = IsFiltered($strSqlSearch) || strlen($strSqlSearch_h) > 0 || $group || $filter_period; return $res; }
if ($EVENTS_VIEW == "NOT_REF") { $EVENTS_VIEW = ""; } $statDB->PrepareFields("b_stat_adv"); $str_REFERER1 = trim($str_REFERER1); $str_REFERER2 = trim($str_REFERER2); $arFields = array("REFERER1" => strlen($str_REFERER1) <= 0 ? "null" : "'" . $str_REFERER1 . "'", "REFERER2" => strlen($str_REFERER1) <= 0 ? "null" : "'" . $str_REFERER2 . "'", "EVENTS_VIEW" => "'" . $str_EVENTS_VIEW . "'", "PRIORITY" => intval($str_PRIORITY), "DESCRIPTION" => "'" . $str_DESCRIPTION . "'"); if (CheckFields()) { $rate_cost = 1; $rate_revenue = 1; if ($currency_module == "Y") { if ($CURRENCY_COST != $base_currency && strlen($CURRENCY_COST) > 0) { $rate_cost = CCurrencyRates::GetConvertFactor($CURRENCY_COST, $base_currency); } if ($CURRENCY_REVENUE != $base_currency && strlen($CURRENCY_REVENUE) > 0) { $rate_revenue = CCurrencyRates::GetConvertFactor($CURRENCY_REVENUE, $base_currency); } } $arFields["COST"] = round(doubleval($COST) * $rate_cost, 2); $arFields["REVENUE"] = round(doubleval($REVENUE) * $rate_revenue, 2); if ($ID > 0) { $statDB->Update("b_stat_adv", $arFields, "WHERE ID = " . $ID, $err_mess . __LINE__); } else { $ID = $statDB->Insert("b_stat_adv", $arFields, $err_mess . __LINE__); } if (strlen($strError) <= 0) { $statDB->Query("DELETE FROM b_stat_adv_searcher WHERE ADV_ID = " . $ID, false, $err_mess . __LINE__); if (is_array($arSEARCHERS)) { foreach ($arSEARCHERS as $searcher_id) { $arFields = array("ADV_ID" => "'" . intval($ID) . "'", "SEARCHER_ID" => "'" . intval($searcher_id) . "'"); $statDB->Insert("b_stat_adv_searcher", $arFields, $err_mess . __LINE__);
public static function RecountBaseCurrency($new_base_currency) { $err_mess = "File: ".__FILE__."<br>Line: "; $DB = CDatabase::GetModuleConnection('statistic'); $base_currency = GetStatisticBaseCurrency(); if ($base_currency!="xxx" && strlen($base_currency)>0) { if (CModule::IncludeModule("currency")) { if (CCurrency::GetByID($base_currency)) { $rate = CCurrencyRates::GetConvertFactor($base_currency, $new_base_currency); if ($rate!=1 && $rate>0) { $arUpdate = array( array("TABLE" => "b_stat_adv", "FIELDS" => array("COST", "REVENUE")), array("TABLE" => "b_stat_event", "FIELDS" => array("MONEY")), array("TABLE" => "b_stat_event_day", "FIELDS" => array("MONEY")), array("TABLE" => "b_stat_event_list", "FIELDS" => array("MONEY")) ); set_time_limit(0); ignore_user_abort(true); $DB->StartTransaction(); foreach ($arUpdate as $arr) { $arFields = $arr["FIELDS"]; $strSql = "UPDATE ".$arr["TABLE"]." SET "; $i = 0; $str = ""; foreach ($arFields as $field) { if ($i>0) $str .= ", "; $str .= $field." = round(".$field."*".$rate.",2)"; $i++; } $DB->Query($strSql.$str, false, $err_mess.__LINE__); } $DB->Commit(); } } } } }
function CalculateAffiliate($affiliate, $dateFrom = false, $dateTo = false, $datePlanFrom = false, $datePlanTo = false) { global $DB; // Prepare function params - affiliate $arAffiliate = CSaleAffiliate::CheckAffiliateFunc($affiliate); if (!$arAffiliate) { return False; } $db_events = GetModuleEvents("sale", "OnBeforeAffiliateCalculate"); while ($arEvent = $db_events->Fetch()) { if (ExecuteModuleEventEx($arEvent, array(&$arAffiliate, &$dateFrom, &$dateTo, &$datePlanFrom, &$datePlanTo)) === false) { return false; } } $affiliateID = IntVal($arAffiliate["ID"]); if (!$dateFrom || StrLen($dateFrom) <= 0) { if (StrLen($arAffiliate["LAST_CALCULATE"]) > 0) { $dateFrom = $arAffiliate["LAST_CALCULATE"]; } else { $dateFrom = date($DB->DateFormatToPHP(CSite::GetDateFormat("FULL")), mktime(0, 0, 0, 1, 1, 1990)); } } if (!$dateTo || StrLen($dateTo) <= 0) { $dateTo = date($DB->DateFormatToPHP(CSite::GetDateFormat("FULL")), time() + CTimeZone::GetOffset()); } // Get affiliate plan $arAffiliatePlan = CSaleAffiliate::SetAffiliatePlan($arAffiliate, $datePlanFrom, $datePlanTo); if (!$arAffiliatePlan) { return False; } if ($arAffiliatePlan && !is_array($arAffiliatePlan)) { return true; } // Get affiliate plan params $arPlanSections = array(); $dbPlanSection = CSaleAffiliatePlanSection::GetList(array(), array("PLAN_ID" => $arAffiliate["PLAN_ID"]), false, false, array("ID", "MODULE_ID", "SECTION_ID", "RATE", "RATE_TYPE", "RATE_CURRENCY")); while ($arPlanSection = $dbPlanSection->Fetch()) { $arPlanSections[$arPlanSection["MODULE_ID"] . $arPlanSection["SECTION_ID"]] = $arPlanSection; } // Get affiliate parents $arAffiliateParents = array(); $affiliateParent = IntVal($arAffiliate["AFFILIATE_ID"]); $count = 0; while ($affiliateParent > 0 && $count < 5) { $dbAffiliateParent = CSaleAffiliate::GetList(array(), array("ID" => $affiliateParent, "ACTIVE" => "Y"), false, false, array("ID", "AFFILIATE_ID")); if ($arAffiliateParent = $dbAffiliateParent->Fetch()) { $count++; $arAffiliateParents[] = $affiliateParent; $affiliateParent = IntVal($arAffiliateParent["AFFILIATE_ID"]); } else { $affiliateParent = 0; } } // Get tier if (!array_key_exists("SALE_AFFILIATE_TIER_TMP_CACHE", $GLOBALS)) { $GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"] = array(); } if (!array_key_exists($arAffiliate["SITE_ID"], $GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"])) { $dbAffiliateTier = CSaleAffiliateTier::GetList(array(), array("SITE_ID" => $arAffiliate["SITE_ID"]), false, false, array("RATE1", "RATE2", "RATE3", "RATE4", "RATE5")); if ($arAffiliateTier = $dbAffiliateTier->Fetch()) { $GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"][$arAffiliate["SITE_ID"]] = array(DoubleVal($arAffiliateTier["RATE1"]), DoubleVal($arAffiliateTier["RATE2"]), DoubleVal($arAffiliateTier["RATE3"]), DoubleVal($arAffiliateTier["RATE4"]), DoubleVal($arAffiliateTier["RATE5"])); } else { $GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"][$arAffiliate["SITE_ID"]] = array(0, 0, 0, 0, 0); } } // Orders cicle $affiliateSum = 0; $affiliateCurrency = CSaleLang::GetLangCurrency($arAffiliate["SITE_ID"]); $dbOrders = \Bitrix\Sale\Internals\OrderTable::getList(array('filter' => array("=ALLOW_DELIVERY" => 'Y', ">=DATE_ALLOW_DELIVERY" => $dateFrom, "<DATE_ALLOW_DELIVERY" => $dateTo, "=AFFILIATE_ID" => $affiliateID, "=LID" => $arAffiliate["SITE_ID"], "=CANCELED" => 'N'), 'select' => array("ID", "LID", "PRICE_DELIVERY", "PRICE", "CURRENCY", "TAX_VALUE", "AFFILIATE_ID", "BASKET_QUANTITY" => 'BASKET.QUANTITY', "BASKET_PRODUCT_ID" => 'BASKET.PRODUCT_ID', "BASKET_MODULE" => 'BASKET.MODULE', "BASKET_PRICE" => 'BASKET.PRICE', "BASKET_CURRENCY" => 'BASKET.CURRENCY', "BASKET_DISCOUNT_PRICE" => 'BASKET.DISCOUNT_PRICE'), 'order' => array('ID' => 'ASC'))); $fOrderId = ""; while ($arOrder = $dbOrders->fetch()) { $arProductSections = array(); if (!array_key_exists("SALE_PRODUCT_SECTION_CACHE", $GLOBALS)) { $GLOBALS["SALE_PRODUCT_SECTION_CACHE"] = array(); } if (array_key_exists($arOrder["BASKET_MODULE"] . $arOrder["BASKET_PRODUCT_ID"], $GLOBALS["SALE_PRODUCT_SECTION_CACHE"])) { $arProductSections = $GLOBALS["SALE_PRODUCT_SECTION_CACHE"][$arOrder["BASKET_MODULE"] . $arOrder["BASKET_PRODUCT_ID"]]; unset($GLOBALS["SALE_PRODUCT_SECTION_CACHE"][$arOrder["BASKET_MODULE"] . $arOrder["BASKET_PRODUCT_ID"]]); $GLOBALS["SALE_PRODUCT_SECTION_CACHE"] = $GLOBALS["SALE_PRODUCT_SECTION_CACHE"] + array($arOrder["BASKET_MODULE"] . $arOrder["BASKET_PRODUCT_ID"] => $arProductSections); } else { if ($arOrder["BASKET_MODULE"] == "catalog") { CModule::IncludeModule("catalog"); $arSku = CCatalogSku::GetProductInfo($arOrder["BASKET_PRODUCT_ID"]); if ($arSku && count($arSku) > 0) { $elementId = $arSku["ID"]; } else { $elementId = $arOrder["BASKET_PRODUCT_ID"]; } $arProductSections = CCatalogProduct::GetProductSections($elementId); } else { $events = GetModuleEvents("sale", "OnAffiliateGetSections"); if ($arEvent = $events->Fetch()) { $arProductSections = ExecuteModuleEventEx($arEvent, array($arOrder["BASKET_MODULE"], $arOrder["BASKET_PRODUCT_ID"])); } } $GLOBALS["SALE_PRODUCT_SECTION_CACHE"] = $GLOBALS["SALE_PRODUCT_SECTION_CACHE"] + array($arOrder["BASKET_MODULE"] . $arOrder["BASKET_PRODUCT_ID"] => $arProductSections); if (count($GLOBALS["SALE_PRODUCT_SECTION_CACHE"]) > 20) { array_shift($GLOBALS["SALE_PRODUCT_SECTION_CACHE"]); } } $realRate = $arAffiliatePlan["BASE_RATE"]; $realRateType = $arAffiliatePlan["BASE_RATE_TYPE"]; $realRateCurrency = $arAffiliatePlan["BASE_RATE_CURRENCY"]; $coountArProd = count($arProductSections); for ($i = 0; $i < $coountArProd; $i++) { if (array_key_exists($arOrder["BASKET_MODULE"] . $arProductSections[$i], $arPlanSections)) { $realRate = $arPlanSections[$arOrder["BASKET_MODULE"] . $arProductSections[$i]]["RATE"]; $realRateType = $arPlanSections[$arOrder["BASKET_MODULE"] . $arProductSections[$i]]["RATE_TYPE"]; $realRateCurrency = $arPlanSections[$arOrder["BASKET_MODULE"] . $arProductSections[$i]]["RATE_CURRENCY"]; break; } } if ($realRateType == "P") { if ($arOrder["CURRENCY"] != $affiliateCurrency) { if (!array_key_exists("SALE_CONVERT_CURRENCY_CACHE", $GLOBALS)) { $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"] = array(); } if (!array_key_exists($arOrder["CURRENCY"] . "-" . $affiliateCurrency, $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"])) { $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"][$arOrder["CURRENCY"] . "-" . $affiliateCurrency] = CCurrencyRates::GetConvertFactor($arOrder["CURRENCY"], $affiliateCurrency); } if ($fOrderId != $arOrder["ID"]) { $affiliateSum += \Bitrix\Sale\PriceMaths::roundPrecision(($arOrder["PRICE"] - $arOrder["PRICE_DELIVERY"]) * $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"][$arOrder["CURRENCY"] . "-" . $affiliateCurrency] * $realRate / 100); $fOrderId = $arOrder["ID"]; } } else { if ($fOrderId != $arOrder["ID"]) { $affiliateSum += \Bitrix\Sale\PriceMaths::roundPrecision(($arOrder["PRICE"] - $arOrder["PRICE_DELIVERY"]) * $realRate / 100); $fOrderId = $arOrder["ID"]; } } } else { if ($realRateCurrency != $affiliateCurrency) { if (!array_key_exists("SALE_CONVERT_CURRENCY_CACHE", $GLOBALS)) { $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"] = array(); } if (!array_key_exists($realRateCurrency . "-" . $affiliateCurrency, $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"])) { $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"][$realRateCurrency . "-" . $affiliateCurrency] = CCurrencyRates::GetConvertFactor($realRateCurrency, $affiliateCurrency); } $affiliateSum += roundEx($realRate * $GLOBALS["SALE_CONVERT_CURRENCY_CACHE"][$realRateCurrency . "-" . $affiliateCurrency], SALE_VALUE_PRECISION); } else { $affiliateSum += roundEx($realRate, SALE_VALUE_PRECISION); } } } $arFields = array("=PENDING_SUM" => "PENDING_SUM + " . $affiliateSum, "LAST_CALCULATE" => $dateTo); $res = CSaleAffiliate::Update($affiliateID, $arFields); if (!$res) { return False; } if ($affiliateSum > 0) { $cnt = min(count($arAffiliateParents), count($GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"][$arAffiliate["SITE_ID"]])); for ($i = 0; $i < $cnt; $i++) { $affiliateSumTmp = roundEx($affiliateSum * $GLOBALS["SALE_AFFILIATE_TIER_TMP_CACHE"][$arAffiliate["SITE_ID"]][$i] / 100, SALE_VALUE_PRECISION); $arFields = array("=PENDING_SUM" => "PENDING_SUM + " . $affiliateSumTmp); CSaleAffiliate::Update($arAffiliateParents[$i], $arFields); } } $events = GetModuleEvents("sale", "OnAfterAffiliateCalculate"); while ($arEvent = $events->Fetch()) { ExecuteModuleEventEx($arEvent, array($affiliateID)); } return True; }
/** * <p>Функция переводит сумму valSum из валюты curFrom в валюту curTo по курсу, установленному на дату valDate </p> * * * * * @param float $valSum Сумма в валюте curFrom, которую нужно перевести в валюту curTo * * * * @param string $curFrom Исходная валюта. * * * * @param string $curTo Конечная валюта. * * * * @param string $valDate = "" Дата, по курсу на которую нужно осуществить перевод. Если дата * пуста, то перевод идет по текущему курсу. Необязательный * параметр. * * * * @return float <p>Сумма в новой валюте </p> <a name="examples"></a> * * * <h4>Example</h4> * <pre> * <? * // предполагаем, что валюты USD и EUR существуют в базе * $val = 11.95; // сумма в USD * $newval = CCurrencyRates::ConvertCurrency($val, "USD", "EUR"); * echo $val." USD = ".$newval." EUR"; * ?> * * * <? * // способ конвертации валюты для списка * if (CModule::IncludeModule('currency')) { * $factor = CCurrencyRates::GetConvertFactor('UEE', 'RUB'); * } else { * $factor = 1; * } * * foreach ($arResult['ITEMS'] as $i => &$arItem) { * $arItem['PROPERTY_PRICE_VALUE'] = number_format($arItem['PROPERTY_PRICE_VALUE'] * $factor, 0, '.', ' '); * } * ?&gt * </pre> * * * @static * @link http://dev.1c-bitrix.ru/api_help/currency/developer/ccurrencyrates/ccurrencyrates__convertcurrency.930a5544.php * @author Bitrix */ public static function ConvertCurrency($valSum, $curFrom, $curTo, $valDate = "") { return doubleval(doubleval($valSum) * CCurrencyRates::GetConvertFactor($curFrom, $curTo, $valDate)); }
/** * <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">событий</a>.</p> * * * @param string &$by = "s_id" Поле для сортировки. Возможные значения: <ul> <li> <b>s_id</b> - ID события; * </li> <li> <b>s_site_id</b> - ID сайта; </li> <li> <b>s_type_id</b> - ID <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type">типа события</a>; </li> <li> * <b>s_event3</b> - <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event3">дополнительный * параметр event3</a> события; </li> <li> <b>s_date_enter</b> - время создания события; * </li> <li> <b>s_adv_id</b> - ID <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv">рекламной * кампании</a>; </li> <li> <b>s_adv_back</b> - флаг <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_back">возврата</a> либо <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#adv_first">прямого захода</a> по * рекламной кампании; </li> <li> <b>s_session_id</b> - ID <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#session">сессии</a>; </li> <li> <b>s_guest_id</b> - ID * <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителя</a>; </li> <li> * <b>s_hit_id</b> - ID <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хита</a>; </li> <li> * <b>s_url</b> - страница где зафиксированно событие; </li> <li> <b>s_referer_url</b> - <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#referer">ссылающаяся страница</a>; </li> * <li> <b>s_redirect_url</b> - страница куда был перенаправлен посетитель после * фиксации события; </li> <li> <b>s_country_id</b> - ID страны посетителя; </li> <li> * <b>s_money</b> - денежная сумма. </li> </ul> * * @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по * возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul> * * @param array $filter = array() Массив для фильтрации результирующего списка. В массиве * допустимы следующие ключи: <ul> <li> <b>ID</b>* - ID события; </li> <li> * <b>ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по <b>ID</b> * будет искаться вхождение; </li> <li> <b>EVENT_ID</b>* - ID типа события; </li> <li> * <b>EVENT_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по * <b>EVENT_ID</b> будет искаться вхождение; </li> <li> <b>EVENT_NAME</b>* - название * типа события; </li> <li> <b>EVENT_NAME_EXACT_MATCH</b> - если значение равно "Y", то * при фильтрации по <b>EVENT_NAME</b> будет искаться точное совпадение; </li> * <li> <b>EVENT1</b>* - <a * href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type_id">идентификатор event1</a> типа * события; </li> <li> <b>EVENT1_EXACT_MATCH</b> - если значение равно "Y", то при * фильтрации по <b>EVENT1</b> будет искаться точное совпадение; </li> <li> * <b>EVENT2</b>* - <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type_id">идентификатор * event2</a> типа события; </li> <li> <b>EVENT2_EXACT_MATCH</b> - если значение равно "Y", * то при фильтрации по <b>EVENT2</b> будет искаться точное совпадение; </li> * <li> <b>EVENT3</b>* - дополнительный параметр event3 события; </li> <li> * <b>EVENT3_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по * <b>EVENT3</b> будет искаться точное совпадение; </li> <li> <b>DATE</b> - время * события (точное совпадение); </li> <li> <b>DATE1</b> - начальное значение * интервала для поля "дата события"; </li> <li> <b>DATE2</b> - начальное * значение интервала для поля "дата события"; </li> <li> <b>MONEY</b> - * денежная сумма события (точное совпадение); </li> <li> <b>MONEY1</b> - * начальное значение интервала для поля "денежная сумма"; </li> <li> * <b>MONEY2</b> - конечное значение интервала для поля "денежная сумма"; * </li> <li> <b>CURRENCY</b> - трехсимвольный идентификатор валюты для * денежной суммы; </li> <li> <b>SESSION_ID</b>* - ID сессии; </li> <li> <b>SESSION_ID_EXACT_MATCH</b> - * если значение равно "N", то при фильтрации по <b>SESSION_ID</b> будет * искаться вхождение; </li> <li> <b>GUEST_ID</b>* - ID посетителя; </li> <li> * <b>GUEST_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по * <b>GUEST_ID</b> будет искаться вхождение; </li> <li> <b>ADV_ID</b>* - ID рекламной * кампании; </li> <li> <b>ADV_ID_EXACT_MATCH</b> - если значение равно "N", то при * фильтрации по <b>ADV_ID</b> будет искаться вхождение; </li> <li> <b>ADV_BACK</b> - * флаг "возврат по рекламной кампании", возможные значения: <ul> <li> * <b>Y</b> - был возврат; </li> <li> <b>N</b> - был прямой заход. </li> </ul> </li> <li> * <b>HIT_ID</b>* - ID хита; </li> <li> <b>HIT_ID_EXACT_MATCH</b> - если значение равно "N", то * при фильтрации по <b>HIT_ID</b> будет искаться вхождение; </li> <li> * <b>COUNTRY_ID</b>* - ID страны посетителя сгенерировавшего событие; </li> <li> * <b>COUNTRY_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по * <b>COUNTRY_ID</b> будет искаться вхождение; </li> <li> <b>COUNTRY</b>* - название * страны посетителя сгенерировавшего событие; </li> <li> <b>COUNTRY_EXACT_MATCH</b> * - если значение равно "Y", то при фильтрации по <b>COUNTRY</b> будет * искаться точное совпадение; </li> <li> <b>REFERER_URL</b>* - ссылающаяся * страница; </li> <li> <b>REFERER_URL_EXACT_MATCH</b> - если значение равно "Y", то при * фильтрации по <b>REFERER_URL</b> будет искаться точное совпадение; </li> <li> * <b>REFERER_SITE_ID</b> - ID сайта для ссылающейся страницы; </li> <li> <b>URL</b>* - * страница на которой было зафиксировано событие; </li> <li> * <b>URL_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по <b>URL</b> * будет искаться точное совпадение; </li> <li> <b>SITE_ID</b> - ID сайта для * страницы на которой было зафиксировано событие; </li> <li> <b>REDIRECT_URL</b>* * - страница куда был перенаправлен посетитель после фиксации * события; </li> <li> <b>REDIRECT_URL_EXACT_MATCH</b> - если значение равно "Y", то при * фильтрации по <b>REDIRECT_URL</b> будет искаться точное совпадение. </li> </ul> * * - допускается <a href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная * логика</a> * * @param bool &$is_filtered Флаг отфильтрованности результирующего списка. Если значение * равно "true", то список был отфильтрован. * * @return CDBResult * * <h4>Example</h4> * <pre> * <? * // выберем все неудаленные события посетителя #1025 * $arFilter = array( * "GUEST_ID" => "1025" * ); * * // получим список записей * $rs = <b>CStatEvent::GetList</b>( * ($by = "s_id"), * ($order = "desc"), * $arFilter, * $is_filtered * ); * * // выведем все записи * while ($ar = $rs->Fetch()) * { * echo "<pre>"; print_r($ar); echo "</pre>"; * } * ?> * </pre> * * * <h4>See Also</h4> * <ul> <li> <a * href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cstatevent/getlistbyguest.php">CStatEvent::GetListByGuest</a> * </li> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">Термин "Событие"</a> </li> * </ul> <a name="examples"></a> * * * @static * @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cstatevent/getlist.php * @author Bitrix */ public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered) { $err_mess = "File: " . __FILE__ . "<br>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 (strlen($val) <= 0 || $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 (strlen($base_currency) > 0) { if (CModule::IncludeModule("currency")) { if ($CURRENCY != $base_currency && strlen($CURRENCY) > 0) { $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 { $by = "s_id"; $strSqlOrder = "ORDER BY E.ID"; } if ($order != "asc") { $strSqlOrder .= " desc "; $order = "desc"; } if ($arFilter["GROUP"] == "total") { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOUNT(1)\t\t\t\t\t\t\tCOUNTER,\n\t\t\t\t\tround(sum(if(E.CHARGEBACK='Y',-E.MONEY,E.MONEY)*{$rate}),2)\tMONEY,\n\t\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\t\tCURRENCY\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_event_list E\n\t\t\t\tINNER JOIN b_stat_event V ON (V.ID=E.EVENT_ID)\n\t\t\t\tLEFT JOIN b_stat_country C ON (C.ID=E.COUNTRY_ID)\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tHAVING\n\t\t\t\t\t1=1\n\t\t\t\t\t{$strSqlSearch_h}\n\t\t\t\t"; } else { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tE.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,\n\t\t\t\t\tround((E.MONEY*{$rate}),2)\t\t\t\t\t\t\t\t\t\tMONEY,\n\t\t\t\t\t'" . $DB->ForSql($view_currency) . "'\t\t\t\t\t\t\t\t\t\t\t\tCURRENCY,\n\t\t\t\t\t" . $DB->DateToCharFunction("E.DATE_ENTER") . "\t\t\t\t\t\tDATE_ENTER,\n\t\t\t\t\tV.ID\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTYPE_ID,\n\t\t\t\t\tV.DESCRIPTION, V.NAME, V.EVENT1, V.EVENT2,\n\t\t\t\t\tC.NAME\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCOUNTRY_NAME,\n\t\t\t\t\tif (length(V.NAME)>0, V.NAME,\n\t\t\t\t\t\tconcat(ifnull(V.EVENT1,''),' / ',ifnull(V.EVENT2,'')))\t\tEVENT\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_event_list E\n\t\t\t\tINNER JOIN b_stat_event V ON (V.ID=E.EVENT_ID)\n\t\t\t\tLEFT JOIN b_stat_country C ON (C.ID=E.COUNTRY_ID)\n\t\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\t\tHAVING\n\t\t\t\t\t1=1\n\t\t\t\t\t{$strSqlSearch_h}\n\t\t\t\t{$strSqlOrder}\n\t\t\t\tLIMIT " . intval(COption::GetOptionString('statistic', 'RECORDS_LIMIT')) . "\n\t\t\t\t"; } $res = $DB->Query($strSql, false, $err_mess . __LINE__); $is_filtered = IsFiltered($strSqlSearch) || strlen($strSqlSearch_h) > 0; return $res; }
/** * <p>Добавляет <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">событие</a> используя текущие параметры <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителя</a>. Если <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type">типа события</a> с <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event_type_id">идентификаторами</a> <i>event1</i>, <i>event2</i> не существует, то он будет автоматически создан с указанными идентификаторами.</p> * * * @param string $event1 Идентификатор типа события event1. * * @param string $event2 = "" Идентификатор типа события event2. * * @param string $event3 = "" <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event3">Дополнительный параметр * event3</a> события. * * @param mixed $money = "" Денежная сумма. * * @param string $currency = "" Трехсимвольный идентификатор валюты. Идентификаторы валют * задаются в модуле "Валюты". * * @param string $goto = "" Адрес страницы куда перешел посетитель. Как правило используется * в скриптах вида /bitrix/redirect.php, перенаправляющих посетителей на * другие страницы с одновременной фиксацией события. * * @param string $chargeback = "N" Флаг отрицательной суммы. Используется когда необходимо * зафиксировать событие о возврате денег (chargeback). Возможные * значения: <ul> <li> <b>Y</b> - денежная сумма отрицательная; </li> <li> <b>N</b> - * денежная сумма положительная. </li> </ul> * * @param mixed $site_id = false ID сайта к которому будет привязано будущее событие. * * @return array <p>Метод возвращает массив вида: <br><br></p> <pre class="syntax">Array ( [TYPE_ID] => ID * типа события [EID] => ID добавленного события )</pre> <p></p> * * <h4>Example</h4> * <pre> * <? * // зафиксируем событие типа "Просмотр спец. страницы" (view/special_page) * <b>CStatEvent::AddCurrent</b>("softkey", "out", $GLOBALS["APPLICATION"]->GetCurPage()); * ?> * * <? * // зафиксируем событие типа "Уход на оплату заказа на Софткее" (softkey/out) * // если такого типа не существует, то он будет автоматически создан * // событие будет фиксироваться по параметрам текущего посетителя сайта * * // в данной переменной может быть задана страница на которую осуществляется переход * $goto = "http://www.softkey.ru/catalog/basket.php?prodid=902&quantity=1&referer1=ritlabs_site&referer2=BITRIX_SM.OTk1LjgyLk4wLjI1Lk4ucnU%3D"; * * <b>CStatEvent::AddCurrent</b>("softkey", "out", "", "", "", $goto); * ?> * * <? * // зафиксируем событие типа "Скачивание файла manual.chm" (download/manual) * // если такого типа не существует, то он будет автоматически создан * // событие будет фиксироваться по параметрам текущего посетителя сайта * * // сначала проверим не скачивал ли уже текущий посетитель этот файл * // в течение последнего часа * * // получим ID типа события * $rs = CStatEventType::GetByEvents($event1, $event2); * if ($ar = $rs->Fetch()) * { * // теперь получим все события данного типа для текущего посетителя сайта * // произошедшие за последний час (3600 секунд) * $rs = CStatEvent::GetListByGuest($_SESSION["SESS_GUEST_ID"], $ar["TYPE_ID"], "", 3600); * * // если таких событий не было то * if (!($ar=$rs->Fetch())) * { * // добавляем данное событие * <b>CStatEvent::AddCurrent</b>("download", "manual"); * } * } * ?> * </pre> * * * <h4>See Also</h4> * <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cstatevent/add.php">CStatEvent::Add</a> </li> <li> * <a href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cstatevent/addbyevents.php">CStatEvent::AddByEvents</a> * </li> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event">Термин "Событие"</a> </li> * <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#event3">Термин "Дополнительный * параметр события (event3)"</a> </li> </ul> <a name="examples"></a> * * * @static * @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cstatevent/addcurrent.php * @author Bitrix */ public static function AddCurrent($event1, $event2 = "", $event3 = "", $money = "", $currency = "", $goto = "", $chargeback = "N", $site_id = false) { $err_mess = "File: " . __FILE__ . "<br>Line: "; global $APPLICATION; $DB = CDatabase::GetModuleConnection('statistic'); $event1 = trim($event1); $event2 = trim($event2); $event3 = trim($event3); if (strlen($event1) <= 0 && strlen($event2) <= 0) { return array("EVENT_ID" => 0, "TYPE_ID" => 0, "EID" => 0); } //Check if register event for searcher if (intval($_SESSION["SESS_SEARCHER_ID"]) > 0 && COption::GetOptionString("statistic", "SEARCHER_EVENTS") != "Y") { return array("EVENT_ID" => 0, "TYPE_ID" => 0, "EID" => 0); } // lookup event type ID $EVENT_ID = CStatEvent::SetEventType($event1, $event2, $arEventType); // return if it's unknown if ($EVENT_ID <= 0) { return array("EVENT_ID" => 0, "TYPE_ID" => 0, "EID" => 0); } if ($site_id === false) { if (defined("ADMIN_SECTION") && ADMIN_SECTION === true) { $sql_site = "null"; $site_id = false; } elseif (defined("SITE_ID")) { $sql_site = "'" . $DB->ForSql(SITE_ID, 2) . "'"; $site_id = SITE_ID; } else { $sql_site = "null"; $site_id = false; } } else { if (strlen(trim($site_id)) > 0) { $sql_site = "'" . $DB->ForSql($site_id, 2) . "'"; } else { $sql_site = "null"; $site_id = false; } } $money = doubleval($money); // convert when currency specified if (strlen(trim($currency)) > 0) { $base_currency = GetStatisticBaseCurrency(); if (strlen($base_currency) > 0) { if ($currency != $base_currency) { if (CModule::IncludeModule("currency")) { $rate = CCurrencyRates::GetConvertFactor($currency, $base_currency); if ($rate > 0 && $rate != 1) { $money = $money * $rate; } } } } } $money = round($money, 2); $chargeback = $chargeback == "Y" ? "Y" : "N"; $goto = preg_replace("/#EVENT_GID#/i", CStatEvent::GetGID($site_id), $goto); $sql_KEEP_DAYS = intval($arEventType["KEEP_DAYS"]) > 0 ? intval($arEventType["KEEP_DAYS"]) : "null"; $arr = false; $referer_url = strlen($_SERVER["HTTP_REFERER"]) <= 0 ? $_SESSION["SESS_HTTP_REFERER"] : $_SERVER["HTTP_REFERER"]; if (strlen($referer_url) > 0) { if ($url = @parse_url($referer_url)) { $rs = CSite::GetList($v1 = "LENDIR", $v2 = "DESC", array("ACTIVE" => "Y", "DOMAIN" => "%" . $url["host"], "IN_DIR" => $url["path"])); $arr = $rs->Fetch(); } } $sql_referer_site_id = is_array($arr) && strlen($arr["ID"]) > 0 ? "'" . $DB->ForSql($arr["ID"], 2) . "'" : "null"; $HIT_ID = CKeepStatistics::GetCuurentHitID(); $arFields = array("EVENT_ID" => "'" . $EVENT_ID . "'", "EVENT3" => "'" . $DB->ForSql($event3, 255) . "'", "MONEY" => $money, "DATE_ENTER" => $DB->GetNowFunction(), "REFERER_URL" => "'" . $DB->ForSql($referer_url, 2000) . "'", "URL" => "'" . $DB->ForSql(__GetFullRequestUri(), 2000) . "'", "REDIRECT_URL" => "'" . $DB->ForSql($goto, 2000) . "'", "SESSION_ID" => intval($_SESSION["SESS_SESSION_ID"]) > 0 ? intval($_SESSION["SESS_SESSION_ID"]) : "null", "GUEST_ID" => intval($_SESSION["SESS_GUEST_ID"]) > 0 ? intval($_SESSION["SESS_GUEST_ID"]) : "null", "ADV_ID" => $_SESSION["SESS_LAST_ADV_ID"] > 0 ? $_SESSION["SESS_LAST_ADV_ID"] : "null", "HIT_ID" => $HIT_ID > 0 ? $HIT_ID : "NULL", "COUNTRY_ID" => "'" . $DB->ForSql($_SESSION["SESS_COUNTRY_ID"], 2) . "'", "KEEP_DAYS" => $sql_KEEP_DAYS, "CHARGEBACK" => "'" . $chargeback . "'", "SITE_ID" => $sql_site, "REFERER_SITE_ID" => $sql_referer_site_id); if (intval($_SESSION["SESS_LAST_ADV_ID"]) > 0 && intval($_SESSION["SESS_ADV_ID"]) <= 0) { $arFields["ADV_BACK"] = "'Y'"; } $eid = $DB->Insert("b_stat_event_list", $arFields, $err_mess . __LINE__); // in case of first occurence if (strlen($arEventType["DATE_ENTER"]) <= 0) { // set date of the first event $arFields = array("DATE_ENTER" => $DB->GetNowFunction()); $DB->Update("b_stat_event", $arFields, "WHERE ID='" . $EVENT_ID . "'", $err_mess . __LINE__); } // day counter update $arFields = array("DATE_LAST" => $DB->GetNowFunction(), "COUNTER" => "COUNTER + 1", "MONEY" => "MONEY + " . $money); $rows = $DB->Update("b_stat_event_day", $arFields, "WHERE EVENT_ID='" . $EVENT_ID . "' and " . CStatistics::DBDateCompare("DATE_STAT"), $err_mess . __LINE__); // there was no records updated if (intval($rows) <= 0) { // so add one $arFields_i = array("DATE_STAT" => $DB->GetNowDate(), "DATE_LAST" => $DB->GetNowFunction(), "EVENT_ID" => $EVENT_ID, "COUNTER" => 1, "MONEY" => $money); $DB->Insert("b_stat_event_day", $arFields_i, $err_mess . __LINE__); } elseif (intval($rows) > 1) { // delete $strSql = "SELECT ID FROM b_stat_event_day WHERE EVENT_ID='" . $EVENT_ID . "' and " . CStatistics::DBDateCompare("DATE_STAT") . " ORDER BY ID"; $i = 0; $rs = $DB->Query($strSql, false, $err_mess . __LINE__); while ($ar = $rs->Fetch()) { $i++; if ($i > 1) { $strSql = "DELETE FROM b_stat_event_day WHERE ID = " . $ar["ID"]; $DB->Query($strSql, false, $err_mess . __LINE__); } } } // guest counter $arFields = array("C_EVENTS" => "C_EVENTS+1"); $DB->Update("b_stat_guest", $arFields, "WHERE ID=" . intval($_SESSION["SESS_GUEST_ID"]), $err_mess . __LINE__, false, false, false); // session counter $arFields = array("C_EVENTS" => "C_EVENTS+1"); $DB->Update("b_stat_session", $arFields, "WHERE ID=" . intval($_SESSION["SESS_SESSION_ID"]), $err_mess . __LINE__, false, false, false); // events counter $arFields = array("C_EVENTS" => "C_EVENTS+1"); $DB->Update("b_stat_day", $arFields, "WHERE " . CStatistics::DBDateCompare("DATE_STAT"), $err_mess . __LINE__, false, false, false); // when site defined if (strlen($site_id) > 0) { // update site $arFields = array("C_EVENTS" => "C_EVENTS+1"); $DB->Update("b_stat_day_site", $arFields, "WHERE SITE_ID='" . $DB->ForSql($site_id, 2) . "' and " . CStatistics::DBDateCompare("DATE_STAT"), $err_mess . __LINE__); } // there is advertising compaign defined if (intval($_SESSION["SESS_ADV_ID"]) > 0 || intval($_SESSION["SESS_LAST_ADV_ID"]) > 0) { // increase revenue if ($money != 0) { $sign = $chargeback == "Y" ? "-" : "+"; $arFields = array("REVENUE" => "REVENUE " . $sign . " " . $money); $DB->Update("b_stat_adv", $arFields, "WHERE ID='" . intval($_SESSION["SESS_LAST_ADV_ID"]) . "'", $err_mess . __LINE__, false, false, false); } if (intval($_SESSION["SESS_ADV_ID"]) > 0) { $arFields = array("COUNTER" => "COUNTER + 1", "MONEY" => "MONEY + " . $money); } else { $arFields = array("COUNTER_BACK" => "COUNTER_BACK + 1", "MONEY_BACK" => "MONEY_BACK + " . $money); } $rows = $DB->Update("b_stat_adv_event", $arFields, "WHERE ADV_ID='" . intval($_SESSION["SESS_LAST_ADV_ID"]) . "' and EVENT_ID='" . $EVENT_ID . "'", $err_mess . __LINE__); if (intval($rows) <= 0) { $arFields = array("ADV_ID" => "'" . $_SESSION["SESS_LAST_ADV_ID"] . "'", "EVENT_ID" => "'" . $EVENT_ID . "'"); if (intval($_SESSION["SESS_ADV_ID"]) > 0) { $arFields["COUNTER"] = "1"; $arFields["MONEY"] = $money; } else { $arFields["COUNTER_BACK"] = "1"; $arFields["MONEY_BACK"] = $money; } $DB->Insert("b_stat_adv_event", $arFields, $err_mess . __LINE__); } if (intval($_SESSION["SESS_ADV_ID"]) > 0) { $arFields = array("COUNTER" => "COUNTER + 1", "MONEY" => "MONEY + " . $money); } else { $arFields = array("COUNTER_BACK" => "COUNTER_BACK + 1", "MONEY_BACK" => "MONEY_BACK + " . $money); } $rows = $DB->Update("b_stat_adv_event_day", $arFields, "WHERE ADV_ID='" . intval($_SESSION["SESS_LAST_ADV_ID"]) . "' and EVENT_ID='" . $EVENT_ID . "' and " . CStatistics::DBDateCompare("DATE_STAT"), $err_mess . __LINE__, false, false, false); if (intval($rows) <= 0) { $arFields = array("ADV_ID" => $_SESSION["SESS_LAST_ADV_ID"] > 0 ? $_SESSION["SESS_LAST_ADV_ID"] : "null", "EVENT_ID" => "'" . $EVENT_ID . "'", "DATE_STAT" => $DB->GetNowDate()); if (intval($_SESSION["SESS_ADV_ID"]) > 0) { $arFields["COUNTER"] = "1"; $arFields["MONEY"] = $money; } else { $arFields["COUNTER_BACK"] = "1"; $arFields["MONEY_BACK"] = $money; } $DB->Insert("b_stat_adv_event_day", $arFields, $err_mess . __LINE__); } elseif (intval($rows) > 1) { $strSql = "SELECT ID FROM b_stat_adv_event_day WHERE ADV_ID='" . intval($_SESSION["SESS_LAST_ADV_ID"]) . "' and EVENT_ID='" . $EVENT_ID . "' and " . CStatistics::DBDateCompare("DATE_STAT") . " ORDER BY ID"; $i = 0; $rs = $DB->Query($strSql, false, $err_mess . __LINE__); while ($ar = $rs->Fetch()) { $i++; if ($i > 1) { $strSql = "DELETE FROM b_stat_adv_event_day WHERE ID = " . $ar["ID"]; $DB->Query($strSql, false, $err_mess . __LINE__); } } } } // todays traffic counters CTraffic::IncParam(array("EVENT" => 1), array("EVENT" => 1)); if (strlen($_SESSION["SESS_COUNTRY_ID"]) > 0) { CStatistics::UpdateCountry($_SESSION["SESS_COUNTRY_ID"], array("C_EVENTS" => 1)); } if ($_SESSION["SESS_CITY_ID"] > 0) { CStatistics::UpdateCity($_SESSION["SESS_CITY_ID"], array("C_EVENTS" => 1)); } return array("EVENT_ID" => intval($EVENT_ID), "TYPE_ID" => intval($EVENT_ID), "EID" => intval($eid)); }
function LoadEventsBySteps($csvfile, $time_step, $next_line, &$step_processed, &$step_loaded, &$step_duplicate, $check_unique = "Y", $base_currency = "", &$next_pos) { if ($fp = fopen($csvfile, "rb")) { if ($next_pos > 0) { fseek($fp, $next_pos); } $start = getmicrotime(); $all_loaded = ""; $next_line = intval($next_line); $read_lines = 0; $step_loaded = 0; $step_processed = 0; $step_duplicate = 0; while (!feof($fp)) { $arrCSV = fgetcsv($fp, 4096, ","); if (is_array($arrCSV) && count($arrCSV) > 0) { array_walk($arrCSV, "CleanUpResultCsv"); $read_lines++; $step_processed++; $EVENT_ID = $arrCSV[0]; $EVENT3 = $arrCSV[1]; $DATE_ENTER = $arrCSV[2]; $PARAMETER = $arrCSV[3]; $MONEY = $arrCSV[4]; $CURRENCY = $arrCSV[5]; $CHARGEBACK = $arrCSV[6]; $RES_MONEY = $MONEY; $EVENT_ID = intval($EVENT_ID); $CHARGEBACK = $CHARGEBACK == "Y" ? "Y" : "N"; if ($EVENT_ID > 0) { if (strlen($base_currency) <= 0) { $base_currency = GetStatisticBaseCurrency(); } if (strlen($base_currency) > 0) { if ($CURRENCY != $base_currency && strlen(trim($CURRENCY)) > 0) { if (CModule::IncludeModule("currency")) { $stmp = MkDateTime(ConvertDateTime($DATE_ENTER, "D.M.Y H:I:S"), "d.m.Y H:i:s"); $valDate = date("Y-m-d", $stmp); $rate = CCurrencyRates::GetConvertFactor($CURRENCY, $base_currency, $valDate); if ($rate > 0) { $RES_MONEY = $MONEY * $rate; } } } } $RES_MONEY = round($RES_MONEY, 2); $add_event = "Y"; if ($check_unique == "Y") { $arr = CStatEvent::DecodeGid($PARAMETER); $arFilter = array("EVENT_ID" => $EVENT_ID, "EVENT3" => $EVENT3, "DATE" => $DATE_ENTER, "SESSION_ID" => $arr["SESSION_ID"], "GUEST_ID" => $arr["GUEST_ID"], "COUNTRY_ID" => $arr["COUNTRY_ID"], "ADV_ID" => $arr["ADV_ID"], "ADV_BACK" => $arr["ADV_BACK"], "SITE_ID" => $arr["SITE_ID"]); $rsEvents = CStatEvent::GetListUniqueCheck($arFilter); if ($arEvent = $rsEvents->Fetch()) { $add_event = "N"; $step_duplicate++; } } if ($add_event == "Y") { CStatEvent::AddByID($EVENT_ID, $EVENT3, $DATE_ENTER, $PARAMETER, $RES_MONEY, "", $CHARGEBACK); $step_loaded++; } $end = getmicrotime(); if (intval($time_step) > 0 && $end - $start > intval($time_step)) { $all_loaded = "N"; break; } } } } if ($all_loaded == "N") { $next_pos = ftell($fp); } else { $next_pos = 0; } @fclose($fp); if ($all_loaded != "N") { $all_loaded = "Y"; @unlink($csvfile); } return $all_loaded; } }