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"; }