public static function GetLastByID($ID)
{
$DB = CDatabase::GetModuleConnection('statistic');
$ID = intval($ID);
if($ID>0)
{
$strSql = "
SELECT
G.ID,
G.FAVORITES,
G.LAST_USER_ID,
A.ID as LAST_ADV_ID,
if(to_days(curdate())=to_days(G.LAST_DATE), 'Y', 'N') LAST
FROM b_stat_guest G
LEFT JOIN b_stat_adv A ON A.ID = G.LAST_ADV_ID
WHERE G.ID='$ID'
";
$res = $DB->Query($strSql, false, "File: ".__FILE__."<br>Line: ".__LINE__);
}
else
{
$res = new CDBResult;
$res->InitFromArray(array());
}
return $res;
}
function DelayedStemIndex()
{
$DB = CDatabase::GetModuleConnection('search');
$etime = time() + intval(COption::GetOptionString("search", "agent_duration"));
do {
$stemQueue = $DB->Query($DB->TopSql("\n\t\t\t\tSELECT SEARCH_CONTENT_ID ID\n\t\t\t\tFROM b_search_content_stem\n\t\t\t\tWHERE SEARCH_CONTENT_ID < 0\n\t\t\t", 1));
if ($stemTask = $stemQueue->Fetch()) {
$ID = -$stemTask["ID"];
$sites = array();
$rsSite = $DB->Query("\n\t\t\t\t\tSELECT SITE_ID, URL\n\t\t\t\t\tFROM b_search_content_site\n\t\t\t\t\tWHERE SEARCH_CONTENT_ID = " . $ID . "\n\t\t\t\t");
while ($arSite = $rsSite->Fetch()) {
$sites[$arSite["SITE_ID"]] = $arSite["URL"];
}
if (BX_SEARCH_VERSION > 1) {
$sql = "SELECT SEARCHABLE_CONTENT from b_search_content_text WHERE SEARCH_CONTENT_ID = {$ID}";
} else {
$sql = "SELECT SEARCHABLE_CONTENT from b_search_content WHERE ID = {$ID}";
}
$rsContent = $DB->Query($sql);
if ($arContent = $rsContent->Fetch()) {
$DB->Query("DELETE FROM b_search_content_stem WHERE SEARCH_CONTENT_ID = " . $ID);
CSearch::StemIndex($sites, $ID, $arContent["SEARCHABLE_CONTENT"]);
}
$DB->Query("DELETE FROM b_search_content_stem WHERE SEARCH_CONTENT_ID = " . $stemTask["ID"]);
} else {
//Cancel the agent
return "";
}
} while ($etime >= time());
return "CSearchStemTable::DelayedStemIndex();";
}
public function Lookup()
{
$DB = CDatabase::GetModuleConnection('statistic');
if ($this->city_avail && $this->ip_number) {
$rs = $DB->Query("\n\t\t\t\tSELECT *\n\t\t\t\tFROM b_stat_city_ip\n\t\t\t\tWHERE START_IP = (\n\t\t\t\t\tSELECT MAX(START_IP)\n\t\t\t\t\tFROM b_stat_city_ip\n\t\t\t\t\tWHERE START_IP <= " . $this->ip_number . "\n\t\t\t\t)\n\t\t\t\tAND END_IP >= " . $this->ip_number . "\n\t\t\t", true);
if ($rs) {
$ar = $rs->Fetch();
if ($ar) {
$this->country_code = $ar["COUNTRY_ID"];
$this->city_id = $ar["CITY_ID"];
}
} else {
//Here is mysql 4.0 version which does not supports subqueries
//and not smart to optimeze query
$rs = $DB->Query("\n\t\t\t\t\tSELECT START_IP\n\t\t\t\t\tFROM b_stat_city_ip\n\t\t\t\t\tWHERE START_IP <= " . $this->ip_number . "\n\t\t\t\t\tORDER BY START_IP DESC\n\t\t\t\t\tLIMIT 1\n\t\t\t\t");
$ar = $rs->Fetch();
if ($ar && strlen($ar["START_IP"]) > 0) {
$rs = $DB->Query("\n\t\t\t\t\t\tSELECT *\n\t\t\t\t\t\tFROM b_stat_city_ip\n\t\t\t\t\t\tWHERE START_IP = " . $ar["START_IP"] . "\n\t\t\t\t\t\tAND END_IP >= " . $this->ip_number . "\n\t\t\t\t\t");
$ar = $rs->Fetch();
if ($ar) {
$this->country_code = $ar["COUNTRY_ID"];
$this->city_id = $ar["CITY_ID"];
}
}
}
}
if (!$this->country_code && $this->country_avail) {
$this->country_code = i2c_get_country();
}
}
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
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 "LAST":
$arSqlSearch[] = $val == "Y" ? "S.DATE_STAT = curdate()" : "S.DATE_STAT<>curdate()";
break;
case "USER_AGENT":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.USER_AGENT", $val, $match);
break;
case "COUNTER1":
$arSqlSearch_h[] = "COUNTER>=" . intval($val);
break;
case "COUNTER2":
$arSqlSearch_h[] = "COUNTER<=" . intval($val);
break;
}
}
for ($i = 0; $i < count($arSqlSearch_h); $i++) {
$strSqlSearch_h .= " and (" . $arSqlSearch_h[$i] . ") ";
}
}
if ($by == "s_user_agent") {
$strSqlOrder = "ORDER BY S.USER_AGENT";
} elseif ($by == "s_counter") {
$strSqlOrder = "ORDER BY COUNTER";
} else {
$by = "s_counter";
$strSqlOrder = "ORDER BY COUNTER";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "SET OPTION SQL_BIG_SELECTS=1";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tS.USER_AGENT,\n\t\t\t\tcount(S.ID) COUNTER\n\t\t\tFROM\n\t\t\t\tb_stat_session S\n\t\t\tLEFT JOIN b_stat_browser B ON (\n\t\t\t\tlength(B.USER_AGENT)>0\n\t\t\tand B.USER_AGENT is not null\n\t\t\tand\tupper(S.USER_AGENT) like upper(B.USER_AGENT)\n\t\t\t)\n\t\t\tLEFT JOIN b_stat_searcher R ON (\n\t\t\t\tlength(R.USER_AGENT)>0\n\t\t\tand\tR.USER_AGENT is not null\n\t\t\tand\tupper(S.USER_AGENT) like upper(concat('%',R.USER_AGENT,'%'))\n\t\t\t)\n\t\t\tWHERE\n\t\t\t{$strSqlSearch}\n\t\t\tand S.USER_AGENT is not null\n\t\t\tand S.USER_AGENT<>''\n\t\t\tand S.NEW_GUEST<>'N'\n\t\t\tand B.ID is null\n\t\t\tand R.ID is null\n\t\t\tGROUP BY S.USER_AGENT\n\t\t\tHAVING '1'='1' {$strSqlSearch_h}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch) || strlen($strSqlSearch_h) > 0;
return $res;
}
function Search($phrase = "", $nTopCount = 5, $arParams = array(), $bNotFilter = false, $order = "")
{
$DB = CDatabase::GetModuleConnection('search');
$this->_arPhrase = stemming_split($phrase, LANGUAGE_ID);
if(!empty($this->_arPhrase))
{
$nTopCount = intval($nTopCount);
if($nTopCount <= 0)
$nTopCount = 5;
$arId = CSearchFullText::GetInstance()->searchTitle($phrase, $this->_arPhrase, $nTopCount, $arParams, $bNotFilter, $order);
if (!is_array($arId))
{
return $this->searchTitle($phrase, $nTopCount, $arParams, $bNotFilter, $order);
}
elseif (!empty($arId))
{
$strSql = "
SELECT
sc.ID
,sc.MODULE_ID
,sc.ITEM_ID
,sc.TITLE
,sc.PARAM1
,sc.PARAM2
,sc.DATE_CHANGE
,L.DIR
,L.SERVER_NAME
,sc.URL as URL
,scsite.URL as SITE_URL
,scsite.SITE_ID
,if(locate('".$DB->ForSQL(ToUpper($phrase))."', upper(sc.TITLE)) > 0, 1, 0) RANK1
FROM
b_search_content sc
INNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID
INNER JOIN b_lang L ON scsite.SITE_ID = L.LID
WHERE
sc.ID in (".implode(",", $arId).")
and scsite.SITE_ID = '".SITE_ID."'
ORDER BY ".(
$bOrderByRank?
"RANK1 DESC, TITLE":
"DATE_CHANGE DESC, RANK1 DESC, TITLE"
)."
";
$r = $DB->Query($DB->TopSql($strSql, $nTopCount+1));
parent::CDBResult($r);
return true;
}
}
else
{
return false;
}
}
public static function GetURLs($site_id, $ID, $limit = 0)
{
$DB = CDatabase::GetModuleConnection('search');
$strSql = "\n\t\tSELECT\n\t\t\tsc.ID\n\t\t\t,sc.MODULE_ID\n\t\t\t,sc.ITEM_ID\n\t\t\t,sc.TITLE\n\t\t\t,sc.PARAM1\n\t\t\t,sc.PARAM2\n\t\t\t,sc.UPD\n\t\t\t,sc.DATE_FROM\n\t\t\t,sc.DATE_TO\n\t\t\t,L.DIR\n\t\t\t,L.SERVER_NAME\n\t\t\t,sc.URL as URL\n\t\t\t,scsite.URL as SITE_URL\n\t\t\t,scsite.SITE_ID\n\t\t\t," . $DB->DateToCharFunction("sc.DATE_CHANGE") . " as FULL_DATE_CHANGE\n\t\t\t," . $DB->DateToCharFunction("sc.DATE_CHANGE", "SHORT") . " as DATE_CHANGE\n\t\tFROM\tb_search_content sc\n\t\t\tINNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID\n\t\t\tINNER JOIN b_lang L ON scsite.SITE_ID=L.LID\n\t\t\tINNER JOIN b_search_content_right scg ON sc.ID=scg.SEARCH_CONTENT_ID\n\t\tWHERE\n\t\t\tscg.GROUP_CODE='G2'\n\t\t\tAND scsite.SITE_ID='" . $DB->ForSQL($site_id, 2) . "'\n\t\t\tAND (sc.DATE_FROM is null OR sc.DATE_FROM <= " . $DB->CurrentTimeFunction() . ")\n\t\t\tAND (sc.DATE_TO is null OR sc.DATE_TO >= " . $DB->CurrentTimeFunction() . ")\n\t\t\tAND sc.ID > " . intval($ID) . "\n\t\tORDER BY\n\t\t\tsc.ID\n\t\t";
if (intval($limit) > 0) {
$strSql .= "LIMIT " . intval($limit);
}
$r = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
parent::CDBResult($r->result);
}
function SetGroups($arGroups)
{
$DB = CDatabase::GetModuleConnection('search');
$dbCodes = $DB->Query("\n\t\t\tSELECT GROUP_CODE\n\t\t\tFROM b_search_user_right\n\t\t\tWHERE USER_ID = " . $this->_user_id . "\n\t\t", false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
while ($dbCode = $dbCodes->Fetch()) {
if (!in_array($dbCode["GROUP_CODE"], $arGroups)) {
$DB->Query("\n\t\t\t\t\tDELETE FROM b_search_user_right\n\t\t\t\t\tWHERE USER_ID = " . $this->_user_id . "\n\t\t\t\t\tAND GROUP_CODE = '" . $DB->ForSQL($dbCode["GROUP_CODE"]) . "'\n\t\t\t\t", false, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
}
$this->AddGroups($arGroups);
}
function addAsSearcher($mask)
{
$statDB = CDatabase::GetModuleConnection('statistic');
if (!checkIfBrowser($mask) && !checkIfSearcher($mask)) {
$arFields = array("ACTIVE" => "'Y'", "SAVE_STATISTIC" => "'Y'", "NAME" => "'" . $statDB->ForSql($mask, 255) . "'", "USER_AGENT" => "'" . $statDB->ForSql($mask, 255) . "'");
$statDB->Insert("b_stat_searcher", $arFields);
return 1;
} else {
return 0;
}
}
function AddGroups($arGroups)
{
$DB = CDatabase::GetModuleConnection('search');
$arToInsert = array();
foreach ($arGroups as $group_code) {
if (strlen($group_code)) {
$arToInsert[$group_code] = $group_code;
}
}
foreach ($arToInsert as $group_code) {
$DB->Query("\n\t\t\t\tINSERT INTO b_search_user_right\n\t\t\t\t(USER_ID, GROUP_CODE)\n\t\t\t\tVALUES\n\t\t\t\t(" . $this->_user_id . ", '" . $DB->ForSQL($group_code, 100) . "')\n\t\t\t", true, "File: " . __FILE__ . "<br>Line: " . __LINE__);
}
}
function Search($phrase = "", $nTopCount = 5, $arParams = array(), $bNotFilter = false, $order = "")
{
$DB = CDatabase::GetModuleConnection('search');
$this->_arPhrase = stemming_split($phrase, LANGUAGE_ID);
if (!empty($this->_arPhrase)) {
$nTopCount = intval($nTopCount);
if ($nTopCount <= 0) {
$nTopCount = 5;
}
$arId = CSearchFullText::GetInstance()->searchTitle($phrase, $this->_arPhrase, $nTopCount, $arParams, $bNotFilter, $order);
if (!is_array($arId)) {
return $this->searchTitle($phrase, $nTopCount, $arParams, $bNotFilter, $order);
} elseif (!empty($arId)) {
$strSql = "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tsc.ID\n\t\t\t\t\t\t,sc.MODULE_ID\n\t\t\t\t\t\t,sc.ITEM_ID\n\t\t\t\t\t\t,sc.TITLE\n\t\t\t\t\t\t,sc.PARAM1\n\t\t\t\t\t\t,sc.PARAM2\n\t\t\t\t\t\t,sc.DATE_CHANGE\n\t\t\t\t\t\t,L.DIR\n\t\t\t\t\t\t,L.SERVER_NAME\n\t\t\t\t\t\t,sc.URL as URL\n\t\t\t\t\t\t,scsite.URL as SITE_URL\n\t\t\t\t\t\t,scsite.SITE_ID\n\t\t\t\t\t\t," . $this->getRankFunction($phrase) . " RANK1\n\t\t\t\t\tFROM\n\t\t\t\t\t\tb_search_content sc\n\t\t\t\t\t\tINNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID\n\t\t\t\t\t\tINNER JOIN b_lang L ON scsite.SITE_ID = L.LID\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tsc.ID in (" . implode(",", $arId) . ")\n\t\t\t\t\t\tand scsite.SITE_ID = '" . SITE_ID . "'\n\t\t\t\t\tORDER BY " . $this->getSqlOrder($bOrderByRank) . "\n\t\t\t\t";
$r = $DB->Query($DB->TopSql($strSql, $nTopCount + 1));
parent::CDBResult($r);
return true;
}
} else {
return false;
}
}
function Search($phrase = "", $nTopCount = 5, $arParams = array(), $bNotFilter = false, $order = "")
{
$DB = CDatabase::GetModuleConnection('search');
$this->_arPhrase = stemming_split($phrase, LANGUAGE_ID);
$bOrderByRank = $order == "rank";
if (!empty($this->_arPhrase)) {
$nTopCount = intval($nTopCount);
if ($nTopCount <= 0) {
$nTopCount = 5;
}
$sqlHaving = array();
$sqlWords = array();
foreach (array_reverse($this->_arPhrase, true) as $word => $pos) {
if (empty($sqlWords) && !preg_match("/[\\n\\r \\t]\$/", $phrase)) {
$s = $sqlWords[] = "ct.WORD like '" . $DB->ForSQL($word) . "%'";
} else {
$s = $sqlWords[] = "ct.WORD = '" . $DB->ForSQL($word) . "'";
}
$sqlHaving[] = "(sum(" . $s . ") > 0)";
}
$bIncSites = false;
$strSqlWhere = CSearch::__PrepareFilter($arParams, $bIncSites);
if ($bNotFilter) {
if (!empty($strSqlWhere)) {
$strSqlWhere = "NOT (" . $strSqlWhere . ")";
} else {
$strSqlWhere = "1=0";
}
}
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tsc.ID\n\t\t\t\t\t,sc.MODULE_ID\n\t\t\t\t\t,sc.ITEM_ID\n\t\t\t\t\t,sc.TITLE\n\t\t\t\t\t,sc.PARAM1\n\t\t\t\t\t,sc.PARAM2\n\t\t\t\t\t,sc.DATE_CHANGE\n\t\t\t\t\t,L.DIR\n\t\t\t\t\t,L.SERVER_NAME\n\t\t\t\t\t,sc.URL as URL\n\t\t\t\t\t,scsite.URL as SITE_URL\n\t\t\t\t\t,scsite.SITE_ID\n\t\t\t\t\t,if(locate('" . $DB->ForSQL(ToUpper($phrase)) . "', upper(sc.TITLE)) > 0, 1, 0) RANK1\n\t\t\t\t\t,count(1) RANK2\n\t\t\t\t\t,min(ct.POS) RANK3\n\t\t\t\tFROM\n\t\t\t\t\tb_search_content_title ct\n\t\t\t\t\tINNER JOIN b_lang L ON ct.SITE_ID = L.LID\n\t\t\t\t\tinner join b_search_content sc on sc.ID = ct.SEARCH_CONTENT_ID\n\t\t\t\t\tINNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID and ct.SITE_ID = scsite.SITE_ID\n\t\t\t\tWHERE\n\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\tAND ct.SITE_ID = '" . SITE_ID . "'\n\t\t\t\t\tAND (" . implode(" OR ", $sqlWords) . ")\n\t\t\t\t\t" . (!empty($strSqlWhere) ? "AND " . $strSqlWhere : "") . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tID, MODULE_ID, ITEM_ID, TITLE, PARAM1, PARAM2, DATE_CHANGE, DIR, SERVER_NAME, URL, SITE_URL, SITE_ID\n\t\t\t\t" . (count($sqlHaving) > 1 ? "HAVING " . implode(" AND ", $sqlHaving) : "") . "\n\t\t\t\tORDER BY " . ($bOrderByRank ? "RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE" : "DATE_CHANGE DESC, RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE") . "\n\t\t\t\tLIMIT 0, " . ($nTopCount + 1) . "\n\t\t\t";
$r = $DB->Query($strSql);
parent::CDBResult($r);
return true;
} else {
return false;
}
}
function CheckFDate($date, $mess)
{
global $strError;
if (strlen($date) > 0) {
$str = "";
if (!CheckDateTime($date)) {
$str .= $mess . "<br>";
}
$strError .= $str;
if (strlen($str) > 0) {
return false;
}
}
return true;
}
$statDB = CDatabase::GetModuleConnection('statistic');
$err_mess = "FILE: " . __FILE__ . "<br>\nLINE: ";
$module_id = "statistic";
$STAT_RIGHT = $APPLICATION->GetGroupRight($module_id);
$strError = "";
if ($STAT_RIGHT >= "R") {
$aTabs = array(array("DIV" => "edit1", "TAB" => GetMessage("MAIN_TAB_SET"), "ICON" => "statistic_settings", "TITLE" => GetMessage("MAIN_TAB_TITLE_SET")), array("DIV" => "edit6", "TAB" => GetMessage("STAT_OPT_TAB_ADV"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_ADV_TITLE")), array("DIV" => "edit7", "TAB" => GetMessage("STAT_OPT_TAB_CITY"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_CITY_TITLE")), array("DIV" => "edit2", "TAB" => GetMessage("STAT_OPT_TAB_STORAGE"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_STORAGE_TITLE")), array("DIV" => "edit3", "TAB" => GetMessage("STAT_OPT_TAB_TIME"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_TIME_TITLE")), array("DIV" => "edit4", "TAB" => GetMessage("STAT_OPT_TAB_SKIP"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_SKIP_TITLE")), array("DIV" => "edit5", "TAB" => GetMessage("MAIN_TAB_RIGHTS"), "ICON" => "statistic_settings", "TITLE" => GetMessage("MAIN_TAB_TITLE_RIGHTS")));
$tabControl = new CAdminTabControl("tabControl", $aTabs);
$aTabs = array(array("DIV" => "fedit2", "TAB" => GetMessage("STAT_OPT_TAB_CLEANUP"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_CLEANUP_TITLE")));
if (strtolower($statDB->type) == "mysql") {
$aTabs[] = array("DIV" => "fedit3", "TAB" => GetMessage("STAT_OPT_TAB_OPTIMIZE"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_OPTIMIZE_TITLE"));
}
if ($STAT_RIGHT >= "W" && ($bCheckForDDL = CStatistics::CheckForDDL())) {
$aTabs[] = array("DIV" => "fedit4", "TAB" => GetMessage("STAT_OPT_TAB_INDEX"), "ICON" => "statistic_settings", "TITLE" => GetMessage("STAT_OPT_TAB_INDEX_TITLE"));
}
$tabControl2 = new CAdminTabControl("tabControl2", $aTabs, true, true);
function IncParam($arParam, $arParamSite = false, $SITE_ID = false, $DATE = false, $DATE_FORMAT = "FULL", $SIGN = "+")
{
$DB = CDatabase::GetModuleConnection('statistic');
if ($DATE == false) {
$strWhere = "WHERE " . CStatistics::DBDateCompare("DATE_STAT");
$stmp = time();
} else {
$stmp = MakeTimeStamp($DATE, $DATE_FORMAT == "SHORT" ? FORMAT_DATE : FORMAT_DATETIME);
$strWhere = "WHERE " . CStatistics::DBDateCompare("DATE_STAT", ConvertTimeStamp($stmp));
}
$HOUR = date("G", $stmp);
// 0..23
$WEEKDAY = date("w", $stmp);
// 0..6
$MONTH = date("n", $stmp);
// 1..12
static $arKeys = array("HOUR", "WEEKDAY", "MONTH");
static $arPreKeys = array("HITS" => 0, "FAVORITES" => 0, "SESSIONS" => 0, "C_HOSTS" => 0, "GUESTS" => 0, "NEW_GUESTS" => 0);
$rows = false;
if (is_array($arParam) && count($arParam) > 0) {
if (array_key_exists("TOTAL_HOSTS", $arParam)) {
unset($arParam["TOTAL_HOSTS"]);
}
$arFields = array();
foreach ($arParam as $name => $value) {
if (array_key_exists($name, $arPreKeys)) {
$arFields[$name] = $name . " + " . intval($value);
} else {
foreach ($arKeys as $key) {
$k = $key . "_" . $name . "_" . ${$key};
$arFields[$k] = "{$k} " . ($SIGN === "-" ? "-" : "+") . " " . intval($value);
}
}
}
if (count($arFields) > 0) {
$rows = $DB->Update("b_stat_day", $arFields, $strWhere);
}
}
if ($SITE_ID === false) {
$SITE_ID = "";
if (defined("ADMIN_SECTION") && ADMIN_SECTION === true) {
$SITE_ID = "";
} elseif (defined("SITE_ID")) {
$SITE_ID = SITE_ID;
}
}
if (strlen($SITE_ID) > 0 && is_array($arParamSite) && count($arParamSite) > 0) {
$arFields = array();
foreach ($arParamSite as $name => $value) {
if (array_key_exists($name, $arPreKeys)) {
$arFields[$name] = $name . " + " . intval($value);
} else {
foreach ($arKeys as $key) {
$k = $key . "_" . $name . "_" . ${$key};
$arFields[$k] = "{$k} " . ($SIGN === "-" ? "-" : "+") . " " . intval($value);
}
}
}
if (count($arFields) > 0) {
$rows = $DB->Update("b_stat_day_site", $arFields, $strWhere . " AND SITE_ID='" . $DB->ForSql($SITE_ID, 2) . "'");
}
}
return $rows;
}
function SetNewDay($HOSTS = 0, $TOTAL_HOSTS = 0, $SESSIONS = 0, $HITS = 0, $NEW_GUESTS = 0, $GUESTS = 0, $FAVORITES = 0)
{
__SetNoKeepStatistics();
if ($_SESSION["SESS_NO_AGENT_STATISTIC"] != "Y" && !defined("NO_AGENT_STATISTIC")) {
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$strSql = "SELECT D.ID FROM b_stat_day D WHERE D.DATE_STAT=CURDATE()";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
if (!$rs->Fetch()) {
$arFields = array("DATE_STAT" => "curdate()", "C_HOSTS" => intval($HOSTS), "SESSIONS" => intval($SESSIONS), "GUESTS" => intval($GUESTS), "HITS" => intval($HITS), "FAVORITES" => intval($FAVORITES), "NEW_GUESTS" => intval($NEW_GUESTS));
$ID = $DB->Insert("b_stat_day", $arFields, $err_mess . __LINE__, false, "", true);
}
//Calculate attentiveness for yesturday
$strSql = "\n\t\t\t\tSELECT D.ID, " . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . " DATE_STAT\n\t\t\t\tFROM b_stat_day D\n\t\t\t\tWHERE D.DATE_STAT=DATE_SUB(CURDATE(),INTERVAL 1 DAY)\n\t\t\t";
$rs = $DB->Query($strSql, false, $err_mess . __LINE__);
if ($ar = $rs->Fetch()) {
$arF = CSession::GetAttentiveness($ar["DATE_STAT"]);
if (is_array($arF)) {
$DB->Update("b_stat_day", $arF, "WHERE ID='" . $ar["ID"] . "'", $err_mess . __LINE__);
}
}
}
return "CStatistics::SetNewDay();";
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#session">сессий</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#hit">хит</a>) на сайте за определённый интервал времени.</p>
*
*
*
*
* @param function $GetList Ссылка на переменную, которая после выполнения функции будет
* содержать количество <a
* href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#online">посетителей в online</a>.
*
*
*
* @param (&$guest_coun $t Ссылка на переменную, которая после выполнения функции будет
* содержать количество сессий посетителей в online.
*
*
*
* @param &$session_coun $t
*
*
*
* @param $arOrde $r = Array()
*
*
*
* @param $arFilte $r = Array())
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* // получим список записей
* $rs = <b>CUserOnline::GetList</b>($guest_counter, $session_counter);
*
* echo "Количество посетителей в онлайн: ".$guest_counter;
* echo "Количество сессий в онлайн: ".$session_counter;
*
* // выведем все записи
* while ($ar = $rs->Fetch())
* {
* echo "<pre>"; print_r($ar); echo "</pre>";
* }
* ?>
* </pre>
*
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://www.1c-bitrix.ru/user_help/statistic/users_online.php">Отчет "Кто на сайте"</a>
* </li> </ul><a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cuseronline/getlist.php
* @author Bitrix
*/
public static function GetList(&$guest_count, &$session_count, $arOrder=Array(), $arFilter=Array())
{
$DB = CDatabase::GetModuleConnection('statistic');
$err_mess = "File: ".__FILE__."<br>Line: ";
$interval = intval(COption::GetOptionString("statistic", "ONLINE_INTERVAL"));
$arSqlSearch = Array();
$strSqlSearch = "";
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 "INTERVAL":
$interval = intval($val);
break;
case "ID":
case "GUEST_ID":
case "ADV_ID":
case "STOP_LIST_ID":
case "USER_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("S.".$key,$val,$match);
break;
case "COUNTRY_ID":
$match = ($arFilter[$key."_EXACT_MATCH"]=="N" && $match_value_set) ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("S.COUNTRY_ID",$val,$match);
break;
case "IP":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.IP_LAST",$val,$match,array("."));
break;
case "REGISTERED":
$arSqlSearch[] = ($val=="Y") ? "S.USER_ID>0" : "(S.USER_ID<=0 or S.USER_ID is null)";
break;
case "HITS1":
$arSqlSearch[] = "S.HITS>='".intval($val)."'";
break;
case "HITS2":
$arSqlSearch[] = "S.HITS<='".intval($val)."'";
break;
case "ADV":
if ($val=="Y")
$arSqlSearch[] = "(S.ADV_ID>0 and S.ADV_ID is not null)";
elseif ($val=="N")
$arSqlSearch[] = "(S.ADV_ID<=0 or S.ADV_ID is null)";
break;
case "REFERER1":
case "REFERER2":
case "REFERER3":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.".$key, $val, $match);
break;
case "STOP":
$arSqlSearch[] = ($val=="Y") ? "S.STOP_LIST_ID>0" : "(S.STOP_LIST_ID<=0 or S.STOP_LIST_ID is null)";
break;
case "COUNTRY":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("C.NAME", $val, $match);
$from2 = "INNER JOIN b_stat_country C ON (C.ID = S.COUNTRY_ID)";
break;
case "LAST_SITE_ID":
$arSqlSearch[] = GetFilterQuery("S.".$key, $val, "N");
break;
case "URL_LAST":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.".$key,$val,$match,array("/","\\",".","?","#",":"));
break;
case "FIRST_URL_FROM":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("G.".$key,$val,$match,array("/","\\",".","?","#",":"));
break;
case "ADV_BACK":
case "NEW_GUEST":
case "URL_LAST_404":
case "URL_TO_404":
case "USER_AUTH":
$arSqlSearch[] = ($val=="Y") ? "S.".$key."='Y'" : "S.".$key."='N'";
break;
case "FAVORITES":
$arSqlSearch[] = ($val=="Y") ? "G.".$key."='Y'" : "G.".$key."='N'";
break;
case "USER":
$match = ($arFilter[$key."_EXACT_MATCH"]=="Y" && $match_value_set) ? "N" : "Y";
$arSqlSearch[] = "ifnull(S.USER_ID,0)>0";
$arSqlSearch[] = GetFilterQuery("S.USER_ID,A.LOGIN,A.LAST_NAME,A.NAME", $val, $match);
$from1 = "LEFT JOIN b_user A ON (A.ID = S.USER_ID)";
$select = " , A.LOGIN, concat(ifnull(A.NAME,''),' ',ifnull(A.LAST_NAME,'')) USER_NAME";
break;
}
}
}
if (!is_array($arOrder))
$arOrder = Array("s_id" => "desc");
foreach($arOrder as $by=>$order)
{
$by = strtolower($by);
$order = strtolower($order);
if ($order!="asc")
$order = "desc";
}
if ($by == "s_id") $strSqlOrder = "ORDER BY S.ID";
elseif ($by == "s_session_time") $strSqlOrder = "ORDER BY SESSION_TIME";
elseif ($by == "s_date_first") $strSqlOrder = "ORDER BY S.DATE_FIRST";
elseif ($by == "s_date_last") $strSqlOrder = "ORDER BY S.DATE_LAST";
elseif ($by == "s_user_id") $strSqlOrder = "ORDER BY S.USER_ID";
elseif ($by == "s_guest_id") $strSqlOrder = "ORDER BY S.GUEST_ID";
elseif ($by == "s_ip") $strSqlOrder = "ORDER BY S.IP_LAST";
elseif ($by == "s_hits") $strSqlOrder = "ORDER BY S.HITS ";
elseif ($by == "s_adv_id") $strSqlOrder = "ORDER BY S.ADV_ID ";
elseif ($by == "s_country_id") $strSqlOrder = "ORDER BY S.COUNTRY_ID ";
elseif ($by == "s_url_last") $strSqlOrder = "ORDER BY S.URL_LAST ";
elseif ($by == "s_url_to") $strSqlOrder = "ORDER BY S.URL_TO ";
else
{
$by = "s_id";
$strSqlOrder = "ORDER BY S.ID";
}
if ($order!="asc")
{
$strSqlOrder .= " desc ";
$order="desc";
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$strSql = "
SELECT
S.ID,
S.ADV_ID, S.REFERER1, S.REFERER2, S.REFERER3, S.ADV_BACK,
S.LAST_SITE_ID, S.URL_LAST, S.URL_LAST_404, S.IP_LAST, S.HITS, S.USER_AUTH,
S.STOP_LIST_ID, S.GUEST_ID, G.FAVORITES, G.LAST_USER_ID,
UNIX_TIMESTAMP(S.DATE_LAST) - UNIX_TIMESTAMP(S.DATE_FIRST) SESSION_TIME,
".$DB->DateToCharFunction("S.DATE_LAST")." DATE_LAST,
if(G.SESSIONS<=1,'Y','N') NEW_GUEST,
G.FIRST_URL_FROM,
G.FIRST_SITE_ID,
S.URL_FROM,
S.COUNTRY_ID,
C.NAME COUNTRY_NAME,
CITY.REGION REGION_NAME,
S.CITY_ID,
CITY.NAME CITY_NAME
FROM
b_stat_session S
INNER JOIN b_stat_guest G ON (G.ID = S.GUEST_ID)
INNER JOIN b_stat_country C ON (C.ID = S.COUNTRY_ID)
".$from1."
LEFT JOIN b_stat_city CITY ON (CITY.ID = S.CITY_ID)
WHERE
S.DATE_STAT >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and S.DATE_LAST > DATE_ADD(now(), INTERVAL - ".$interval." SECOND)
and ".$strSqlSearch."
".$strSqlOrder."
";
$arr = array();
$arrG = array();
$rs = $DB->Query($strSql, false, $err_mess.__LINE__);
while($ar = $rs->Fetch())
{
$arr[] = $ar;
$arrG[$ar["GUEST_ID"]] = $ar["GUEST_ID"];
}
$guest_count = count($arrG);
$session_count = count($arr);
$rs = new CDBResult;
$rs->InitFromArray($arr);
return $rs;
}
/**
* <p>Возвращает данные по указанному <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хиту</a> <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителя</a>.</p>
*
*
* @param int $hit_id ID хита.
*
* @return CDBResult
*
* <h4>Example</h4>
* <pre>
* <?
* $hit_id = 1;
* if ($rs = <b>CHit::GetByID</b>($hit_id))
* {
* $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/terms.php#hit">Термин "Хит"</a> </li></ul> <a
* name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/chit/getbyid.php
* @author Bitrix
*/
public static function GetByID($ID)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$ID = intval($ID);
$res = $DB->Query("\n\t\t\tSELECT\n\t\t\t\tH.*,\n\t\t\t\t" . $DB->DateToCharFunction("H.DATE_HIT") . " DATE_HIT,\n\t\t\t\tC.NAME COUNTRY_NAME,\n\t\t\t\tCITY.REGION REGION_NAME,\n\t\t\t\tCITY.NAME CITY_NAME\n\t\t\tFROM\n\t\t\t\tb_stat_hit H\n\t\t\t\tINNER JOIN b_stat_country C ON (C.ID = H.COUNTRY_ID)\n\t\t\t\tLEFT JOIN b_stat_city CITY ON (CITY.ID = H.CITY_ID)\n\t\t\tWHERE\n\t\t\t\tH.ID = '{$ID}'\n\t\t", false, $err_mess . __LINE__);
$res = new CStatResult($res);
return $res;
}
/**
* <p>Возвращает количество <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search_hit">хитов</a> (проиндексированных страниц), для указанной <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search">поисковой системы</a> в разрезе по дням.</p>
*
*
* @param int $searcher_id ID поисковой системы. </ht
*
* @param string &$by = "s_date" Поле для сортировки. Возможные значения: <ul><li> <b>s_date</b> - дата. </li></ul>
*
* @param string &$order = "desc" Порядок сортировки. Возможные значения: <ul> <li> <b>asc</b> - по
* возрастанию; </li> <li> <b>desc</b> - по убыванию. </li> </ul>
*
* @param array &$max_min Ссылка на массив содержащий максимальную и минимальную даты
* результирующего списка. Структура данного массива: <pre> Array (
* [DATE_FIRST] => минимальная дата [MIN_DAY] => номер дня для минимальной
* даты (1-31) [MIN_MONTH] => номер месяца для минимальной даты (1-12) [MIN_YEAR] =>
* номер года для минимальной даты [DATE_LAST] => максимальная дата [MAX_DAY]
* => номер дня для максимальной даты (1-31) [MAX_MONTH] => номер месяца для
* максимальной даты (1-12) [MAX_YEAR] => номер года для максимальной даты
* )</pre>
*
* @param array $filter = array() Массив для фильтрации результирующего списка. В массиве
* допустимы следующие ключи: <ul> <li> <b>DATE1</b> - начальное значение
* интервала для поля "дата"; </li> <li> <b>DATE2</b> - конечное значение
* интервала для поля "дата". </li> </ul>
*
* @return CDBResult
*
* <h4>Example</h4>
* <pre>
* <?
* $searcher_id = 1;
*
* // установим фильтр на декабрь 2005 года
* $arFilter = array(
* "DATE1" => "01.12.2005",
* "DATE2" => "31.12.2005"
* );
*
* // получим набор записей
* $rs = <b>CSearcher::GetDynamicList</b>(
* $searcher_id,
* ($by="s_date"),
* ($order="desc"),
* $arMaxMin,
* $arFilter
* );
*
* // выведем массив с максимальной и минимальной датами
* echo "<pre>"; print_r($arMaxMin); echo "</pre>";
*
* // выведем все записи
* 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/terms.php#search_hit">Термин "Хит
* поисковой системы"</a> </li></ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/csearcher/getdynamiclist.php
* @author Bitrix
*/
public static function GetDynamicList($SEARCHER_ID, &$by, &$order, &$arMaxMin, $arFilter = array())
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$SEARCHER_ID = intval($SEARCHER_ID);
$arSqlSearch = array();
$strSqlSearch = "";
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;
}
}
$key = strtoupper($key);
switch ($key) {
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "D.DATE_STAT>=" . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "D.DATE_STAT<" . $DB->CharToDateFunction($val, "SHORT") . " + INTERVAL 1 DAY";
}
break;
}
}
}
foreach ($arSqlSearch as $sqlWhere) {
$strSqlSearch .= " and (" . $sqlWhere . ") ";
}
if ($by == "s_date") {
$strSqlOrder = "ORDER BY D.DATE_STAT";
} else {
$by = "s_date";
$strSqlOrder = "ORDER BY D.DATE_STAT";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . "\t\tDATE_STAT,\n\t\t\t\tDAYOFMONTH(D.DATE_STAT)\t\t\t\t\t\t\t\t\tDAY,\n\t\t\t\tMONTH(D.DATE_STAT)\t\t\t\t\t\t\t\t\t\tMONTH,\n\t\t\t\tYEAR(D.DATE_STAT)\t\t\t\t\t\t\t\t\t\tYEAR,\n\t\t\t\tD.TOTAL_HITS\n\t\t\tFROM\n\t\t\t\tb_stat_searcher_day D\n\t\t\tWHERE\n\t\t\t\tD.SEARCHER_ID = {$SEARCHER_ID}\n\t\t\t{$strSqlSearch}\n\t\t\t{$strSqlOrder}\n\t\t\t";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
$strSql = "\n\t\t\tSELECT\n\t\t\t\tmax(D.DATE_STAT)\t\t\t\tDATE_LAST,\n\t\t\t\tmin(D.DATE_STAT)\t\t\t\tDATE_FIRST,\n\t\t\t\tDAYOFMONTH(max(D.DATE_STAT))\tMAX_DAY,\n\t\t\t\tMONTH(max(D.DATE_STAT))\t\t\tMAX_MONTH,\n\t\t\t\tYEAR(max(D.DATE_STAT))\t\t\tMAX_YEAR,\n\t\t\t\tDAYOFMONTH(min(D.DATE_STAT))\tMIN_DAY,\n\t\t\t\tMONTH(min(D.DATE_STAT))\t\t\tMIN_MONTH,\n\t\t\t\tYEAR(min(D.DATE_STAT))\t\t\tMIN_YEAR\n\t\t\tFROM\n\t\t\t\tb_stat_searcher_day D\n\t\t\tWHERE\n\t\t\t\tD.SEARCHER_ID = {$SEARCHER_ID}\n\t\t\t{$strSqlSearch}\n\t\t\t";
$a = $DB->Query($strSql, false, $err_mess . __LINE__);
$ar = $a->Fetch();
$arMaxMin["MAX_DAY"] = $ar["MAX_DAY"];
$arMaxMin["MAX_MONTH"] = $ar["MAX_MONTH"];
$arMaxMin["MAX_YEAR"] = $ar["MAX_YEAR"];
$arMaxMin["MIN_DAY"] = $ar["MIN_DAY"];
$arMaxMin["MIN_MONTH"] = $ar["MIN_MONTH"];
$arMaxMin["MIN_YEAR"] = $ar["MIN_YEAR"];
return $res;
}
public static function GetGraphArray_SQL($strSqlSearch)
{
$DB = CDatabase::GetModuleConnection('statistic');
$strSql = "\n\t\t\tSELECT\n\t\t\t\t" . $DB->DateToCharFunction("D.DATE_STAT", "SHORT") . " DATE_STAT,\n\t\t\t\tDAYOFMONTH(D.DATE_STAT) DAY,\n\t\t\t\tMONTH(D.DATE_STAT) MONTH,\n\t\t\t\tYEAR(D.DATE_STAT) YEAR,\n\t\t\t\tD.COUNTER,\n\t\t\t\tD.MONEY,\n\t\t\t\tD.EVENT_ID,\n\t\t\t\tE.NAME,\n\t\t\t\tE.EVENT1,\n\t\t\t\tE.EVENT2\n\t\t\tFROM\n\t\t\t\tb_stat_event_day D\n\t\t\tINNER JOIN b_stat_event E ON (E.ID = D.EVENT_ID)\n\t\t\tWHERE\n\t\t\t\t{$strSqlSearch}\n\t\t\tORDER BY\n\t\t\t\tD.DATE_STAT, D.EVENT_ID\n\t\t\t";
return $strSql;
}
function ShowStep()
{
$DB = CDatabase::GetModuleConnection('statistic');
$rs = $DB->Query("SELECT count(*) CNT FROM b_stat_country");
$arCountry = $rs->Fetch();
$rs = $DB->Query("SELECT count(*) CNT FROM b_stat_city");
$arCity = $rs->Fetch();
$rs = $DB->Query("SELECT count(*) CNT FROM b_stat_city_ip");
$arCityIP = $rs->Fetch();
$this->content = '<ul>';
$this->content .= '<li>' . GetMessage('STATWIZ_FINALSTEP_COUNTRIES', array("#COUNT#" => $arCountry["CNT"])) . '</li>';
$this->content .= '<li>' . GetMessage('STATWIZ_FINALSTEP_CITIES', array("#COUNT#" => $arCity["CNT"])) . '</li>';
$this->content .= '<li>' . GetMessage('STATWIZ_FINALSTEP_CITY_IPS', array("#COUNT#" => $arCityIP["CNT"])) . '</li>';
$this->content .= '</ul>';
}
function CleanCache()
{
$DB = CDatabase::GetModuleConnection('statistic');
$file = $_SERVER["DOCUMENT_ROOT"] . BX_PERSONAL_ROOT . "/managed_cache/" . $DB->type . "/b_stop_list";
if (file_exists($file)) {
unlink($file);
}
}
/**
* <p>Возвращает количество <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#search">поисковых фраз</a> за весь период ведения статистики, за последние 3 дня, а также за произвольный указанный интервал времени.</p>
*
*
* @param string &$by = "s_today" Поле для сортировки. Возможные значения: <ul> <li> <b>s_phrase</b> -
* поисковая фраза; </li> <li> <b>s_today</b> - количество поисковых фраз за
* сегодня; </li> <li> <b>s_yesterday</b> - количество поисковых фраз за вчера; </li>
* <li> <b>s_bef_yesterday</b> - количество поисковых фраз за позавчера; </li> <li>
* <b>s_total</b> - суммарное количество поисковых фраз; </li> <li> <b>s_period</b> -
* количество поисковых фраз за установленный период времени
* <nobr>(<i>filter</i>[<b>DATE1</b>], <i>filter</i>[<b>DATE2</b>])</nobr>. </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>SITE_ID</b> - ID сайта для которого
* необходимо получить статистику по поисковым фразам; </li> <li> <b>DATE1</b>
* - начальная дата; </li> <li> <b>DATE2</b> - конечная дата. </li> </ul>
*
* @param bool &$is_filtered Флаг отфильтрованности списка поисковых фраз. Если значение
* равно "true", то список был отфильтрован.
*
* @param mixed $limit = 10 Максимальное число записей результирующего списка. Если задано
* число >0, то число записей будет ограничено, иначе ограничений не
* будет.
*
* @return CDBResult
*
* <h4>Example</h4>
* <pre>
* <?
* // получим дополнительные данные за декабрь 2007 года
* $arFilter = array(
* "DATE1" => "01.12.2007",
* "DATE2" => "31.12.2007"
* );
*
* // получим список поисковых фраз и статистику по ним
* $rs = CTraffic::GetPhraseList($by="s_today",
* $order="desc",
* $arFilter,
* $is_filtered,
* false);
* while ($ar = $rs->Fetch())
* {
* echo "поисковая фраза: ".$ar["PHRASE"]."<br>";
* echo "всего заходов с данной поисковой фразой: ".
* $ar["TOTAL_PHRASES"].
* "<br>";
* echo "сегодня: ".$ar["TODAY_PHRASES"]."<br>";
* echo "вчера: ".$ar["YESTERDAY_PHRASES"]."<br>";
* echo "позавчера: ".$ar["B_YESTERDAY_PHRASES"]."<br>";
* echo "в течение декабря 2005 года: ".
* $ar["PERIOD_PHRASES"]."<br>";
* }
* ?>
* </pre>
*
*
* <h4>See Also</h4>
* <ul> <li> <a href="http://dev.1c-bitrix.ru/api_help/statistic/classes/cphrase/index.php">Класс "CPhrase"</a> </li>
* <li> <a href="http://www.1c-bitrix.ru/user_help/statistic/stat_list.php">Отчет "Сводная
* статистика"</a> </li> </ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/ctraffic/getphraselist.php
* @author Bitrix
*/
public static function GetPhraseList(&$s_by, &$s_order, $arFilter = array(), &$is_filtered, $limit = 10)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$strSqlSearch = "";
$site_id = $arFilter["SITE_ID"];
if (strlen($site_id) > 0 && $site_id != "NOT_REF") {
$is_filtered = true;
$strSqlSearch = " and SITE_ID = '" . $DB->ForSql($site_id, 2) . "' ";
}
$date1 = $arFilter["DATE1"];
$date2 = $arFilter["DATE2"];
$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) {
$date_filtered = $is_filtered = true;
if (strlen($date2) > 0) {
$strSqlPeriod = " sum(if(DATE_HIT<FROM_UNIXTIME('{$date_from}'),0, if(date_hit>FROM_UNIXTIME('{$date_to}'),0,1)))";
} else {
$strSqlPeriod = " sum(if(DATE_HIT<FROM_UNIXTIME('{$date_from}'),0,1))";
}
} elseif (strlen($date2) > 0) {
$date_filtered = $is_filtered = true;
$strSqlPeriod = " sum(if(DATE_HIT>FROM_UNIXTIME('{$date_to}'),0,1))";
} else {
$date_filtered = false;
$strSqlPeriod = "";
}
if ($s_by == "s_phrase") {
$strSqlOrder = " ORDER BY PHRASE ";
} elseif ($s_by == "s_today") {
$strSqlOrder = " ORDER BY TODAY_PHRASES ";
} elseif ($s_by == "s_yesterday") {
$strSqlOrder = " ORDER BY YESTERDAY_PHRASES ";
} elseif ($s_by == "s_bef_yesterday") {
$strSqlOrder = " ORDER BY B_YESTERDAY_PHRASES ";
} elseif ($s_by == "s_total") {
$strSqlOrder = " ORDER BY TOTAL_PHRASES ";
} elseif ($s_by == "s_period" && $date_filtered) {
$strSqlOrder = " ORDER BY PERIOD_PHRASES ";
} else {
$s_by = "s_today";
$strSqlOrder = " ORDER BY TODAY_PHRASES desc, YESTERDAY_PHRASES desc, B_YESTERDAY_PHRASES desc, TOTAL_PHRASES ";
}
if ($s_order != "asc") {
$strSqlOrder .= " desc ";
$s_order = "desc";
}
$strSql = "\n\t\t\tSELECT\n\t\t\t\tPHRASE,\n\t\t\t\tcount('x') TOTAL_PHRASES,\n\t\t\t\tsum(if(to_days(curdate())-to_days(DATE_HIT)=0,1,0)) TODAY_PHRASES,\n\t\t\t\tsum(if(to_days(curdate())-to_days(DATE_HIT)=1,1,0)) YESTERDAY_PHRASES,\n\t\t\t\tsum(if(to_days(curdate())-to_days(DATE_HIT)=2,1,0)) B_YESTERDAY_PHRASES\n\t\t\t\t" . ($date_filtered ? ',' . $strSqlPeriod . ' PERIOD_PHRASES ' : '') . "\n\t\t\tFROM\n\t\t\t\tb_stat_phrase_list\n\t\t\tWHERE\n\t\t\t\t1=1\n\t\t\t\t" . $strSqlSearch . "\n\t\t\tGROUP BY\n\t\t\t\tPHRASE\n\t\t\t" . $strSqlOrder . "\n\t\t";
if (intval($limit) > 0) {
$strSql .= " LIMIT " . intval($limit);
}
return $DB->Query($strSql, false, $err_mess . __LINE__);
}
function i2c_load_countries($file_name = false, $file_type = false)
{
$DB = CDatabase::GetModuleConnection('statistic');
$err_mess = "FILE: " . __FILE__ . "<br>LINE: ";
if ($file_name === false) {
$file_name = IP_DEFAULT_SOURCE_FILENAME;
}
if ($file_type === false) {
$file_type = IP_DEFAULT_SOURCE_TYPE;
}
if ($fp = fopen($_SERVER["DOCUMENT_ROOT"] . $file_name, "rb")) {
set_time_limit(0);
ignore_user_abort(true);
$arFields = array("NAME" => "'NA'", "SHORT_NAME" => "'N00'");
$rows = $DB->Update("b_stat_country", $arFields, "WHERE ID='N0'", $err_mess . __LINE__);
if (intval($rows) <= 0) {
$strSql = "INSERT INTO b_stat_country (ID, SHORT_NAME, NAME) VALUES ('N0','N00','NA')";
$DB->Query($strSql, false, $err_mess . __LINE__);
}
$arrUpdated = array();
$bHeader = true;
$bMaxMind = $file_type == "maxmind.com";
if ($bMaxMind) {
$ix_countryCode = 5;
$ix_countryName = 6;
} else {
$ix_countryCode = 2;
$ix_countryName = 4;
}
while (!feof($fp)) {
$arr = fgetcsv($fp, 4096);
if (is_array($arr) && count($arr) > 0) {
if ($bMaxMind && $bHeader) {
foreach ($arr as $key => $value) {
$value = trim($value);
if ($value == "countryCode") {
$bHeader = false;
$ix_countryCode = intval($key);
} elseif ($value == "countryName") {
$bHeader = false;
$ix_countryName = intval($key);
}
}
}
//Check if was hanled with "dirty" $country_id
if (array_key_exists($arr[$ix_countryCode], $arrUpdated)) {
continue;
}
$country_id = trim($arr[$ix_countryCode], "\"");
if (!array_key_exists($country_id, $arrUpdated)) {
$country_name = trim($arr[$ix_countryName], "\"");
if ($bMaxMind) {
if (strlen($country_id) != 2) {
continue;
}
$country_short_name = "";
} else {
$country_short_name = trim($arr[3], "\"");
}
$arFields["NAME"] = "'" . $DB->ForSql($country_name, 50) . "'";
$arFields["SHORT_NAME"] = "'" . $DB->ForSql($country_short_name, 3) . "'";
$rows = $DB->Update("b_stat_country", $arFields, "WHERE ID='" . $DB->ForSql(strtoupper($country_id), 2) . "'", $err_mess . __LINE__);
if (intval($rows) <= 0 && strlen($country_id) > 0 && strlen($country_name) > 0) {
$strSql = "\n\t\t\t\t\t\t\tINSERT INTO b_stat_country (ID, SHORT_NAME, NAME) VALUES (\n\t\t\t\t\t\t\t\t'" . $DB->ForSql($country_id, 2) . "',\n\t\t\t\t\t\t\t\t" . $arFields["SHORT_NAME"] . ",\n\t\t\t\t\t\t\t\t" . $arFields["NAME"] . "\n\t\t\t\t\t\t\t)";
$DB->Query($strSql, false, $err_mess . __LINE__);
}
$arrUpdated[$country_id] = true;
}
}
}
fclose($fp);
}
}
function OnEpilog()
{
if(isset($_REQUEST["sphrase_id"]))
{
$phrase_id = intval($_REQUEST["sphrase_id"]);
if($phrase_id)
{
$DB = CDatabase::GetModuleConnection('search');
$rs = $DB->Query("
SELECT *
FROM b_search_phrase
WHERE ID = ".$phrase_id."
AND SESSION_ID = '".$DB->ForSQL(freetrix_sessid())."'
AND URL_TO IS NULL
");
if($ar = $rs->Fetch())
{
$URL_TO = $DB->ForSQL(CSearchStatistic::GetCurrentURL(), 2000);
$DB->Query("
UPDATE b_search_phrase
SET URL_TO = '".$URL_TO."'
,URL_TO_404 = '".(defined("ERROR_404")? "Y": "N")."'
,URL_TO_SITE_ID = ".(defined("SITE_ID")? "'".$DB->ForSQL(SITE_ID, 2)."'": "null")."
WHERE ID = ".$phrase_id."
");
}
}
}
}
function BuildWhereClause($word)
{
$DB = CDatabase::GetModuleConnection('search');
$this->cnt++;
if ($this->cnt > 10) {
return "1=1";
}
if (isset($this->m_kav[$word])) {
$word = $this->m_kav[$word];
$bInQuotes = true;
} else {
$bInQuotes = false;
}
$this->m_words[] = $word;
$word = $DB->ForSql($word, 100);
if ($this->bTagsSearch) {
if (strpos($word, "%") === false) {
//We can optimize query by doing range scan
if (is_array($this->m_tags_words)) {
$this->m_tags_words[] = $word;
}
$op = "=";
} else {
//Optimization is not possible
$this->m_tags_words = false;
$op = "like";
}
return "(sum(stags.name " . $op . " '" . $word . "')>0)";
} elseif ($this->bStemming && !$bInQuotes) {
$word = ToUpper($word);
$this->m_stemmed_words[] = $word;
if (BX_SEARCH_VERSION > 1) {
$rs = $DB->Query("select ID from b_search_stem where STEM='" . $DB->ForSQL($word) . "'");
$ar = $rs->Fetch();
$this->m_stemmed_words_id[] = intval($ar["ID"]);
return "(sum(st.stem = " . intval($ar["ID"]) . ")>0)";
} else {
return "(sum(st.stem = '" . $word . "')>0)";
}
} else {
if (BX_SEARCH_VERSION > 1) {
$this->bText = true;
return "(sct.searchable_content LIKE '%" . str_replace(array("%", "_"), array("\\%", "\\_"), ToUpper($word)) . "%')";
} else {
return "(sc.searchable_content LIKE '%" . str_replace(array("%", "_"), array("\\%", "\\_"), ToUpper($word)) . "%')";
}
}
}
function GetList(&$by, &$order, $arFilter = array(), &$is_filtered)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$arSqlSearch = array();
$strSqlSearch = "";
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 "SEARCHER_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("H." . $key, $val, $match);
break;
case "URL":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.URL", $val, $match, array("/", "\\", ".", "?", "#", ":"));
break;
case "URL_404":
$arSqlSearch[] = $val == "Y" ? "H.URL_404='Y'" : "H.URL_404='N'";
break;
case "SEARCHER":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("S.NAME", $val, $match);
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT >= " . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "H.DATE_HIT < " . CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
}
break;
case "IP":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.IP", $val, $match, array("."));
break;
case "USER_AGENT":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("H.USER_AGENT", $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("H.SITE_ID", $val, $match);
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
if ($by == "s_id") {
$strSqlOrder = "ORDER BY H.ID";
} elseif ($by == "s_site_id") {
$strSqlOrder = "ORDER BY H.SITE_ID";
} elseif ($by == "s_date_hit") {
$strSqlOrder = "ORDER BY H.DATE_HIT";
} elseif ($by == "s_searcher_id") {
$strSqlOrder = "ORDER BY H.SEARCHER_ID";
} elseif ($by == "s_user_agent") {
$strSqlOrder = "ORDER BY H.USER_AGENT";
} elseif ($by == "s_ip") {
$strSqlOrder = "ORDER BY H.IP";
} elseif ($by == "s_url") {
$strSqlOrder = "ORDER BY H.URL ";
} else {
$by = "s_date_hit";
$strSqlOrder = "ORDER BY H.DATE_HIT";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\tSELECT /*TOP*/\n\t\t\t\tH.ID, H.SEARCHER_ID, H.URL, H.URL_404, H.IP, H.USER_AGENT, H.HIT_KEEP_DAYS, H.SITE_ID,\n\t\t\t\tS.NAME SEARCHER_NAME,\n\t\t\t\t" . $DB->DateToCharFunction("H.DATE_HIT") . " DATE_HIT\n\t\t\tFROM\n\t\t\t\tb_stat_searcher_hit H\n\t\t\tINNER JOIN b_stat_searcher S ON (S.ID = H.SEARCHER_ID)\n\t\t\tWHERE\n\t\t\t" . $strSqlSearch . "\n\t\t\t" . $strSqlOrder . "\n\t\t";
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch);
return $res;
}
<?php
require_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/main/include/prolog_admin_before.php";
require_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/search/prolog.php";
IncludeModuleLangFile(__FILE__);
/** @global CMain $APPLICATION */
global $APPLICATION;
/** @var CAdminMessage $message */
$searchDB = CDatabase::GetModuleConnection('search');
$POST_RIGHT = $APPLICATION->GetGroupRight("search");
if ($POST_RIGHT == "D") {
$APPLICATION->AuthForm(GetMessage("ACCESS_DENIED"));
}
$res = false;
$bFull = !isset($_REQUEST["Full"]) || $_REQUEST["Full"] != "N";
if ($_SERVER["REQUEST_METHOD"] == "POST" && $_REQUEST["Reindex"] == "Y") {
CUtil::JSPostUnescape();
@set_time_limit(0);
require_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/main/include/prolog_admin_js.php";
if (array_key_exists("NS", $_POST) && is_array($_POST["NS"])) {
$NS = $_POST["NS"];
} else {
$NS = array();
$max_execution_time = intval($max_execution_time);
if ($max_execution_time <= 0) {
$max_execution_time = '';
}
COption::SetOptionString("search", "max_execution_time", $max_execution_time);
if (!$bFull) {
if (isset($_REQUEST["site_id"]) && $_REQUEST["site_id"] != "") {
$NS["SITE_ID"] = $_REQUEST["site_id"];
function GetByID($ID)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
$DB = CDatabase::GetModuleConnection('statistic');
$ID = intval($ID);
$strSql = "SELECT S.* FROM b_stat_searcher S WHERE S.ID = '{$ID}'";
$res = $DB->Query($strSql, false, $err_mess . __LINE__);
return $res;
}
/**
* <p>Возвращает данные по указанному <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#guest">посетителю</a>.</p>
*
*
*
*
* @param int $guest_id ID посетителя.
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* $guest_id = 1;
* if ($rs = <b>CGuest::GetByID</b>($guest_id))
* {
* $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/terms.php#guest">Термин "Посетитель"</a>
* </li></ul><a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/cguest/getbyid.php
* @author Bitrix
*/
public static function GetByID($ID)
{
$DB = CDatabase::GetModuleConnection('statistic');
$ID = intval($ID);
$res = $DB->Query("
SELECT
G.*,
".$DB->DateToCharFunction("G.FIRST_DATE")." FIRST_DATE,
".$DB->DateToCharFunction("G.LAST_DATE")." LAST_DATE,
".CStatistics::DBDateDiff("FS.DATE_LAST","FS.DATE_FIRST")." FSESSION_TIME,
".CStatistics::DBDateDiff("LS.DATE_LAST","LS.DATE_FIRST")." LSESSION_TIME,
FS.HITS FSESSION_HITS,
LS.HITS LSESSION_HITS,
C.NAME COUNTRY_NAME,
CITY.REGION REGION_NAME,
CITY.NAME CITY_NAME,
G.LAST_CITY_INFO
FROM
b_stat_guest G
INNER JOIN b_stat_country C ON (C.ID = G.LAST_COUNTRY_ID)
LEFT JOIN b_stat_session FS ON (FS.ID = G.FIRST_SESSION_ID)
LEFT JOIN b_stat_session LS ON (LS.ID = G.LAST_SESSION_ID)
LEFT JOIN b_stat_city CITY ON (CITY.ID = G.LAST_CITY_ID)
WHERE
G.ID = '$ID'
", false, "File: ".__FILE__."<br>Line: ".__LINE__);
$res = new CStatResult($res);
return $res;
}
/**
* <p>Возвращает список <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#referer">ссылающихся сайтов (страниц)</a>.</p>
*
*
*
*
* @param string &$by = "s_id" Поле для сортировки. В зависимости от группировки списка, набор
* доступных значений данной переменной может быть различным. <ul>
* <li>при группировке по ссылающейся странице (<i>filter</i>["<b>GROUP</b>"]="U"): <ul>
* <li> <b>s_url_from</b> - ссылающаяся страница; </li> <li> <b>s_quantity</b> - количество
* заходов с ссылающейся страницы; </li> <li> <b>s_average_hits</b> - среднее
* количество <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#hit">хитов</a>,
* производимое посетителями заходящими с той или ссылающейся
* страницы. </li> </ul> </li> <li>при группировке по ссылающемуся домену
* (<i>filter</i>["<b>GROUP</b>"]="S"): <ul> <li> <b>s_url_from</b> - ссылающийся домен; </li> <li>
* <b>s_quantity</b> - количество заходов с ссылающегося домена; </li> <li>
* <b>s_average_hits</b> - среднее количество хитов, производимое
* посетителями. </li> </ul> </li> <li>когда группировка не установлена: <ul> <li>
* <b>s_id</b> - ID записи; </li> <li> <b>s_site_id</b> - ID сайта, на который пришли; </li> <li>
* <b>s_url_from</b> - ссылающаяся страница (с которой пришли); </li> <li> <b>s_url_to</b>
* - страница на которую пришли; </li> <li> <b>s_date_hit</b> - дата; </li> <li>
* <b>s_session_id</b> - ID сессии. </li> </ul> </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>SESSION_ID</b> - ID сессии; </li> <li>
* <b>SESSION_ID_EXACT_MATCH</b> - если значение равно "N", то при фильтрации по
* <b>SESSION_ID</b> будет искаться вхождение; </li> <li> <b>DATE1</b> - начальное
* значение интервала для поля "дата"; </li> <li> <b>DATE2</b> - конечное
* значение интервала для поля "дата"; </li> <li> <b>FROM_PROTOCOL</b> - протокол
* ссылающейся страницы; </li> <li> <b>FROM_PROTOCOL_EXACT_MATCH</b> - если значение
* равно "Y", то при фильтрации по <b>FROM_PROTOCOL</b> будет искаться точное
* совпадение; </li> <li> <b>FROM_DOMAIN</b> - домен ссылающейся страницы; </li> <li>
* <b>FROM_DOMAIN_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>FROM_DOMAIN</b> будет искаться точное совпадение; </li> <li> <b>FROM_PAGE</b> -
* ссылающаяся страница; </li> <li> <b>FROM_PAGE_EXACT_MATCH</b> - если значение равно
* "Y", то при фильтрации по <b>FROM_PAGE</b> будет искаться точное
* совпадение; </li> <li> <b>FROM</b> - протокол + домен + ссылающаяся страница;
* </li> <li> <b>FROM_EXACT_MATCH</b> - если значение равно "Y", то при фильтрации по
* <b>FROM</b> будет искаться точное совпадение; </li> <li> <b>TO</b>* - страница на
* которую пришли; </li> <li> <b>TO_EXACT_MATCH</b> - если значение равно "Y", то при
* фильтрации по <b>TO</b> будет искаться точное совпадение; </li> <li>
* <b>TO_404</b> - была ли <a href="http://dev.1c-bitrix.ru/api_help/statistic/terms.php#404">404 ошибка</a>
* на странице, на которую пришли, возможные значения: <ul> <li> <b>Y</b> -
* была; </li> <li> <b>N</b> - не была. </li> </ul> </li> <li> <b>SITE_ID</b> - ID сайта на который
* пришли; </li> <li> <b>GROUP</b> - группировка результирующего списка;
* возможные значения: <ul> <li> <b>S</b> - группировка по ссылающемуся
* домену (сайту); </li> <li> <b>U</b> - группировка по ссылающейся странице.
* </li> </ul> </li> </ul> * - допускается <a
* href="http://dev.1c-bitrix.ru/api_help/main/general/filter.php">сложная логика</a>
*
*
*
* @param bool &$is_filtered Флаг отфильтрованности результирующего списка. Если значение
* равно "true", то список был отфильтрован.
*
*
*
* @param int &$total Суммарные количество заходов с ссылающихся страниц.
*
*
*
* @param string &$group_by Группировка результирующего списка. Возможные значения: <ul> <li>
* <b>U</b> - группировка по ссылающейся странице; </li> <li> <b>S</b> -
* группировка по ссылающемуся домену. </li> </ul>
*
*
*
* @param int &$max Количество заходов с самой популярной ссылающейся страницы.
*
*
*
* @return CDBResult
*
*
* <h4>Example</h4>
* <pre>
* <?
* // отфильтруем только заходы с доменов "google"
* // сгруппировав по ссылающемуся домену
* $arFilter = array(
* "FROM_DOMAIN" => "google",
* "GROUP" => "S"
* );
*
* // получим список записей
* $rs = <b>CReferer::GetList</b>(
* ($by = "s_url_from"),
* ($order = "desc"),
* $arFilter,
* $is_filtered,
* $total,
* $group_by,
* $max
* );
*
* // выведем все записи
* 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/terms.php#referer">Термин "Ссылающийся
* сайт (страница)"</a> </li> </ul> <a name="examples"></a>
*
*
* @static
* @link http://dev.1c-bitrix.ru/api_help/statistic/classes/creferer/getlist.php
* @author Bitrix
*/
public static function GetList(&$by, &$order, $arFilter = array(), &$is_filtered, &$total, &$grby, &$max)
{
$err_mess = "File: " . __FILE__ . "<br>Line: ";
global $grby, $total;
$DB = CDatabase::GetModuleConnection('statistic');
$group = false;
$strSqlGroup = "GROUP BY L.PROTOCOL, L.SITE_NAME, L.URL_FROM, R.HITS, R.SESSIONS";
$url_from = $DB->Concat("L.PROTOCOL", "L.SITE_NAME", "L.URL_FROM");
$arSqlSearch = array();
$strSqlSearch = "";
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 "SESSION_ID":
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("L." . $key, $val, $match);
break;
case "DATE1":
if (CheckDateTime($val)) {
$arSqlSearch[] = "L.DATE_HIT >= " . $DB->CharToDateFunction($val, "SHORT");
}
break;
case "DATE2":
if (CheckDateTime($val)) {
$arSqlSearch[] = "L.DATE_HIT < " . CStatistics::DBDateAdd($DB->CharToDateFunction($val, "SHORT"), 1);
}
break;
case "FROM_PROTOCOL":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.PROTOCOL", $val, $match, array("/", "\\", ":"));
break;
case "FROM_DOMAIN":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.SITE_NAME", $val, $match, array("."));
break;
case "FROM_PAGE":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.URL_FROM", $val, $match, array("/", "\\", ".", "?", "#", ":", ":"));
break;
case "FROM":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery($url_from, $val, $match, array("/", "\\", ".", "?", "#", ":"), "N", "N");
break;
case "TO":
$match = $arFilter[$key . "_EXACT_MATCH"] == "Y" && $match_value_set ? "N" : "Y";
$arSqlSearch[] = GetFilterQuery("L.URL_TO", $val, $match, array("/", "\\", ".", "?", "#", ":"));
break;
case "TO_404":
$arSqlSearch[] = $val == "Y" ? "L.URL_TO_404='Y'" : "L.URL_TO_404='N'";
break;
case "GROUP":
$group = true;
if ($val == "S") {
$find_group = "S";
$strSqlGroup = "GROUP BY L.SITE_NAME, R.HITS, R.SESSIONS";
$url_from = "L.SITE_NAME";
} else {
$find_group = "U";
}
break;
case "SITE_ID":
if (is_array($val)) {
$val = implode(" | ", $val);
}
$match = $arFilter[$key . "_EXACT_MATCH"] == "N" && $match_value_set ? "Y" : "N";
$arSqlSearch[] = GetFilterQuery("L.SITE_ID", $val, $match);
break;
}
}
}
$strSqlSearch = GetFilterSqlSearch($arSqlSearch);
$grby = $find_group == "U" || $find_group == "S" ? $find_group : "";
$strSqlOrder = "";
if (strlen($grby) <= 0) {
if ($by == "s_id") {
$strSqlOrder = " ORDER BY L.ID ";
} elseif ($by == "s_site_id") {
$strSqlOrder = " ORDER BY L.SITE_ID ";
} elseif ($by == "s_url_from") {
$strSqlOrder = " ORDER BY URL_FROM ";
} elseif ($by == "s_url_to") {
$strSqlOrder = " ORDER BY L.URL_TO ";
} elseif ($by == "s_date_hit") {
$strSqlOrder = " ORDER BY L.DATE_HIT ";
} elseif ($by == "s_session_id") {
$strSqlOrder = " ORDER BY L.SESSION_ID ";
} else {
$by = "s_id";
$strSqlOrder = "ORDER BY L.ID";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\t" . $url_from . " as URL_FROM,\n\t\t\t\t\tL.ID,\n\t\t\t\t\tL.SESSION_ID,\n\t\t\t\t\tL.SITE_ID,\n\t\t\t\t\t" . $DB->DateToCharFunction("L.DATE_HIT") . " DATE_HIT,\n\t\t\t\t\tL.URL_TO,\n\t\t\t\t\tL.URL_TO_404\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
} elseif (IsFiltered($strSqlSearch) || $grby == "U") {
if ($by == "s_url_from") {
$strSqlOrder = "ORDER BY URL_FROM";
} elseif ($by == "s_quantity") {
$strSqlOrder = "ORDER BY QUANTITY";
} elseif ($by == "s_average_hits") {
$strSqlOrder = "ORDER BY AVERAGE_HITS";
} else {
$by = "s_quantity";
$strSqlOrder = "ORDER BY QUANTITY";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tcount(L.ID) as COUNTER\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\t\tLEFT JOIN b_stat_referer R ON (R.ID = L.REFERER_ID)\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlGroup . "\n\t\t\t";
$c = $DB->Query($strSql, false, $err_mess . __LINE__);
$total = 0;
$arrCount = array();
while ($cr = $c->Fetch()) {
$total += intval($cr["COUNTER"]);
$arrCount[] = intval($cr["COUNTER"]);
}
if (count($arrCount) > 0) {
$max = max($arrCount);
}
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\t" . $url_from . " URL_FROM,\n\t\t\t\t\tcount(L.ID) QUANTITY,\n\t\t\t\t\t(count(L.ID)*100)/{$total} C_PERCENT,\n\t\t\t\t\tR.HITS/R.SESSIONS AVERAGE_HITS\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer_list L\n\t\t\t\t\tLEFT JOIN b_stat_referer R ON (R.ID = L.REFERER_ID)\n\t\t\t\tWHERE\n\t\t\t\t" . $strSqlSearch . "\n\t\t\t\t" . $strSqlGroup . "\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
} elseif ($grby == "S") {
if ($by == "s_url_from") {
$strSqlOrder = "ORDER BY URL_FROM";
} elseif ($by == "s_quantity") {
$strSqlOrder = "ORDER BY QUANTITY";
} elseif ($by == "s_average_hits") {
$strSqlOrder = "ORDER BY AVERAGE_HITS";
} else {
$by = "s_quantity";
$strSqlOrder = "ORDER BY QUANTITY";
}
if ($order != "asc") {
$strSqlOrder .= " desc ";
$order = "desc";
}
$strSql = "SELECT sum(R.SESSIONS) TOTAL, max(R.SESSIONS) MAX FROM b_stat_referer R";
$c = $DB->Query($strSql, false, $err_mess . __LINE__);
$cr = $c->Fetch();
$total = intval($cr["TOTAL"]);
$max = intval($cr["MAX"]);
$strSql = "\n\t\t\t\tSELECT /*TOP*/\n\t\t\t\t\tR.SITE_NAME URL_FROM,\n\t\t\t\t\tsum(R.SESSIONS) QUANTITY,\n\t\t\t\t\t(sum(R.SESSIONS)*100)/{$total} C_PERCENT,\n\t\t\t\t\tsum(R.HITS)/sum(R.SESSIONS) AVERAGE_HITS\n\t\t\t\tFROM\n\t\t\t\t\tb_stat_referer R\n\t\t\t\tGROUP BY R.SITE_NAME\n\t\t\t\t" . $strSqlOrder . "\n\t\t\t";
}
$res = $DB->Query(CStatistics::DBTopSql($strSql), false, $err_mess . __LINE__);
$is_filtered = IsFiltered($strSqlSearch) || $group;
return $res;
}
public static function LoadCSV($file_name, $step, &$file_position)
{
global $APPLICATION;
$DB = CDatabase::GetModuleConnection('statistic');
$arCache = array();
$arLookupCache = array();
$arCountryCache = array();
$fp = fopen($_SERVER["DOCUMENT_ROOT"] . $file_name, "rb");
if (!$fp) {
return "Y";
}
$file_format = CCity::GetCSVFormatType($fp);
$file_position = intval($file_position);
fseek($fp, $file_position);
if ($step > 0) {
$end_time = getmicrotime() + $step;
} else {
$end_time = 0;
}
switch ($file_format) {
case "MAXMIND-IP-COUNTRY":
$delimiter = ",";
$char_set = LANG_CHARSET;
$table_name = "b_stat_country";
$arFieldsMap = array("ID" => array("key" => true, "index" => 4, "type" => "varchar", "len" => 2, "default" => "NA"), "NAME" => array("index" => 5, "type" => "varchar", "len" => 50, "default" => "N00", "enc" => true));
break;
case "IP-TO-COUNTRY":
$delimiter = ",";
$char_set = LANG_CHARSET;
$table_name = "b_stat_country";
$arFieldsMap = array("ID" => array("key" => true, "index" => 2, "type" => "varchar", "len" => 2, "default" => "NA"), "SHORT_NAME" => array("index" => 3, "type" => "varchar", "len" => 3, "default" => "N00"), "NAME" => array("index" => 4, "type" => "varchar", "len" => 50, "enc" => true));
break;
case "MAXMIND-IP-LOCATION":
$delimiter = ",";
$char_set = LANG_CHARSET;
$table_name = "b_stat_city_ip";
$arFieldsMap = array("START_IP" => array("key" => true, "index" => 0, "type" => "ipnum"), "END_IP" => array("index" => 1, "type" => "ipnum"), "XML_ID" => array("index" => 2, "type" => "lookup"), "COUNTRY_ID" => array("index" => -1, "type" => "varchar"), "CITY_ID" => array("index" => -2, "type" => "number"));
//Some files need to skip first line
if ($file_position <= 0) {
fgets($fp, 4096);
fgets($fp, 4096);
}
break;
case "MAXMIND-CITY-LOCATION":
$delimiter = ",";
$char_set = "iso-8859-1";
$table_name = "b_stat_city";
$arFieldsMap = array("COUNTRY_ID" => array("index" => 1, "type" => "varchar", "len" => 2, "default" => "NA"), "REGION" => array("index" => 2, "type" => "varchar", "len" => 255, "enc" => true), "NAME" => array("index" => 3, "type" => "varchar", "len" => 255, "enc" => true), "XML_ID" => array("key" => true, "index" => 0, "type" => "varchar", "len" => 255));
//Some files need to skip first line
if ($file_position <= 0) {
fgets($fp, 4096);
fgets($fp, 4096);
}
break;
case "IPGEOBASE":
$delimiter = "\t";
$char_set = "Windows-1251";
$table_name = "b_stat_city_ip";
$arFieldsMap = array("START_IP" => array("key" => true, "index" => 0, "type" => "ipnum"), "END_IP" => array("index" => 1, "type" => "ipnum"), "COUNTRY_ID" => array("index" => 3, "type" => "varchar", "len" => 2, "default" => "NA"), "XML_ID" => array("index" => array(3, 4, 5), "type" => "upsert", "enc" => true, "master" => array("COUNTRY_ID" => array("index" => 3, "type" => "varchar", "len" => 2, "default" => "NA"), "REGION" => array("index" => 5, "type" => "varchar", "len" => 255, "enc" => true), "NAME" => array("index" => 4, "type" => "varchar", "len" => 255, "enc" => true))), "CITY_ID" => array("index" => -1, "type" => "number"));
break;
case "IPGEOBASE2":
$delimiter = "\t";
$char_set = "Windows-1251";
$table_name = "b_stat_city_ip";
$arFieldsMap = array("START_IP" => array("key" => true, "index" => 0, "type" => "ipnum"), "END_IP" => array("index" => 1, "type" => "ipnum"), "COUNTRY_ID" => array("index" => 3, "type" => "varchar", "len" => 2, "default" => "NA", "update_city" => 4), "XML_ID" => array("index" => 4, "type" => "lookup"), "CITY_ID" => array("index" => -1, "type" => "number"));
break;
case "IPGEOBASE2-CITY":
$delimiter = "\t";
$char_set = "Windows-1251";
$table_name = "b_stat_city";
$arFieldsMap = array("COUNTRY_ID" => array("skip_update" => true, "index" => 100, "type" => "varchar", "len" => 2, "default" => "NA"), "REGION" => array("index" => 2, "type" => "varchar", "len" => 255, "enc" => true), "NAME" => array("index" => 1, "type" => "varchar", "len" => 255, "enc" => true), "XML_ID" => array("key" => true, "index" => 0, "type" => "varchar", "len" => 255));
break;
default:
return "Y";
}
$bConv = $char_set != LANG_CHARSET;
while (!feof($fp)) {
//$arr = fgetcsv($fp, 4096, $delimiter);
$arr = fgets($fp, 4096);
if ($bConv && preg_match("/[^a-zA-Z0-9 \t\n\r]/", $arr)) {
$arr = $APPLICATION->ConvertCharset($arr, $char_set, LANG_CHARSET);
}
$arr = preg_split("/" . $delimiter . "/" . BX_UTF_PCRE_MODIFIER, $arr);
$arAllSQLFields = array();
$arFields = array();
$strUpdate = "";
$strWhere = "";
$strInsert1 = "";
$strInsert2 = "";
$bEmptyKey = false;
foreach ($arFieldsMap as $FIELD_ID => $arField) {
if (is_array($arField["index"])) {
$value = "";
foreach ($arField["index"] as $index) {
$value .= trim($arr[$index], "\" \n\r\t");
}
$value = md5($value);
} else {
$value = trim($arr[$arField["index"]], "\" \n\r\t");
}
//if($bConv && $arField["enc"] && preg_match("/[^a-zA-Z0-9 \t\n\r]/", $value))
// $value = $APPLICATION->ConvertCharset($value, $char_set, LANG_CHARSET);
if (!$value && $arField["default"]) {
$value = $arField["default"];
}
if ($arField["type"] == "upsert") {
if (!array_key_exists($value, $arLookupCache)) {
$rs = $DB->Query("SELECT ID as CITY_ID FROM b_stat_city WHERE XML_ID = '" . $DB->ForSQL($value) . "'");
$ar = $rs->Fetch();
if (!$ar) {
$arNewMaster = array("XML_ID" => $value);
foreach ($arField["master"] as $MASTER_FIELD_ID => $arMasterField) {
$m_value = trim($arr[$arMasterField["index"]], "\"");
//if($bConv && $arMasterField["enc"] && preg_match("/[^a-zA-Z0-9 \t\n\r]/", $m_value))
// $m_value = $APPLICATION->ConvertCharset($m_value, $char_set, LANG_CHARSET);
if (!$m_value && $arMasterField["default"]) {
$m_value = $arMasterField["default"];
}
$arNewMaster[$MASTER_FIELD_ID] = $m_value;
}
$ar = array("CITY_ID" => $DB->Add("b_stat_city", $arNewMaster));
}
$arLookupCache[$value] = $ar;
}
foreach ($arLookupCache[$value] as $key => $val) {
$arr[$arFieldsMap[$key]["index"]] = $val;
}
continue;
}
if ($arField["type"] == "lookup") {
if (!array_key_exists($value, $arLookupCache)) {
$rs = $DB->Query("SELECT COUNTRY_ID, ID as CITY_ID FROM b_stat_city WHERE XML_ID = '" . $DB->ForSQL($value) . "'");
$ar = $rs->Fetch();
if (!$ar) {
$ar = array("COUNTRY_ID" => "NA", "CITY_ID" => 0);
}
$arLookupCache[$value] = $ar;
}
foreach ($arLookupCache[$value] as $key => $val) {
$arr[$arFieldsMap[$key]["index"]] = $val;
}
continue;
}
if ($FIELD_ID === "COUNTRY_ID" && !array_key_exists($value, $arCountryCache) && strlen($value) > 0) {
$cid = $DB->ForSQL($value, 2);
$rs = $DB->Query("SELECT ID FROM b_stat_country WHERE ID = '" . $cid . "'");
if (!$rs->Fetch()) {
$DB->Query("insert into b_stat_country (ID) values ('" . $cid . "')");
}
$arCountryCache[$value] = true;
}
if ($FIELD_ID === "COUNTRY_ID" && isset($arField["update_city"]) && strlen($value) > 0) {
$city_id = $DB->ForSQL(trim($arr[$arField["update_city"]], "\" \n\r\t"));
$cid = $DB->ForSQL($value, 2);
$rs = $DB->Query("UPDATE b_stat_city SET COUNTRY_ID = '" . $cid . "' WHERE XML_ID = '" . $city_id . "'");
}
switch ($arField["type"]) {
case "varchar":
$sql_value = "'" . $DB->ForSQL($value, $arField["len"]) . "'";
break;
case "ipnum":
case "number":
$sql_value = preg_replace("/[^0-9]/", "", $value);
break;
default:
$sql_value = "'" . $DB->ForSQL($value) . "'";
break;
}
$arAllSQLFields[$FIELD_ID] = $sql_value;
if ($arField["key"]) {
if ($value) {
if ($strWhere) {
$strWhere .= " AND ";
}
$strWhere .= $FIELD_ID . " = " . $sql_value;
} else {
$bEmptyKey = true;
}
} else {
$arFields[$FIELD_ID] = $value;
if ($strUpdate) {
$strUpdate .= ", ";
}
$strUpdate .= $FIELD_ID . " = " . $sql_value;
}
if ($strInsert1) {
$strInsert1 .= ", ";
}
$strInsert1 .= $FIELD_ID;
if ($strInsert2) {
$strInsert2 .= ", ";
}
$strInsert2 .= $sql_value;
}
if (!$bEmptyKey && $strWhere && $strUpdate && !array_key_exists($strWhere, $arCache)) {
if ($table_name == "b_stat_city_ip" && $arAllSQLFields["START_IP"] && $arAllSQLFields["END_IP"]) {
$arToUpdate = CCity::ResolveIPRange($arAllSQLFields["START_IP"], $arAllSQLFields["END_IP"]);
} else {
$rs = $DB->Query("SELECT * FROM {$table_name} WHERE {$strWhere}");
$arToUpdate = $rs->Fetch();
}
if ($arToUpdate) {
$bNeedUpdate = false;
foreach ($arFields as $UPD_FIELD_ID => $upd_value) {
if (!isset($arFieldsMap[$UPD_FIELD_ID]["skip_update"])) {
if ($upd_value != $arToUpdate[$UPD_FIELD_ID]) {
$bNeedUpdate = true;
break;
}
}
}
if ($bNeedUpdate) {
$DB->Query("UPDATE {$table_name} SET {$strUpdate} WHERE {$strWhere}");
}
} else {
$DB->Query("INSERT INTO {$table_name} ({$strInsert1}) VALUES ({$strInsert2})");
}
$arCache[$strWhere] = true;
}
if ($end_time && getmicrotime() > $end_time) {
$file_position = ftell($fp);
return "N";
}
}
$file_position = ftell($fp);
return "Y";
}
