ip_addr = $arDBRecord["IPA"]; if(array_key_exists("IPN", $arDBRecord)) $this->ip_number = $arDBRecord["IPN"]; if(array_key_exists("COC", $arDBRecord)) $this->country_code = $arDBRecord["COC"]; if(array_key_exists("COS", $arDBRecord)) $this->country_short_name = $arDBRecord["COS"]; if(array_key_exists("COF", $arDBRecord)) $this->country_full_name = $arDBRecord["COF"]; if(array_key_exists("REN", $arDBRecord)) $this->region_name = $arDBRecord["REN"]; if(array_key_exists("CIN", $arDBRecord)) $this->city_name = $arDBRecord["CIN"]; if(array_key_exists("CID", $arDBRecord)) $this->city_id = $arDBRecord["CID"]; } else { $ip = $this->ip_addr = $_SERVER["REMOTE_ADDR"]; $this->ip_number = ip2number($ip); } } /** * @param array[string]string $arDBRecord * @return CCityLookup */ public static function OnCityLookup($arDBRecord = /*.(array[string]string).*/array()) { return new CCityLookup($arDBRecord); } /** * @return array[string]string */ function ArrayForDB() { $ar = /*.(array[string]string).*/array(); if($this->ip_addr <> '') $ar["IPA"] = $this->ip_addr; if($this->ip_number <> '') $ar["IPN"] = $this->ip_number; if($this->country_code <> '') $ar["COC"] = $this->country_code; if($this->country_short_name <> '') $ar["COS"] = $this->country_short_name; if($this->country_full_name <> '') $ar["COF"] = $this->country_full_name; if($this->region_name <> '') $ar["REN"] = $this->region_name; if($this->city_name <> '') $ar["CIN"] = $this->city_name; if($this->city_id <> '') $ar["CID"] = $this->city_id; return $ar; } /** * @return array[string][string]string */ function GetFullInfo() { return array( "IP_ADDR" => array( "TITLE" => GetMessage("STAT_CITY_IP_ADDR"), "VALUE~" => $this->ip_addr, "VALUE" => htmlspecialcharsbx($this->ip_addr), ), "COUNTRY_CODE" => array( "TITLE" => GetMessage("STAT_CITY_COUNTRY_CODE"), "VALUE~" => $this->country_code, "VALUE" => htmlspecialcharsbx($this->country_code), ), "COUNTRY_NAME" => array( "TITLE" => GetMessage("STAT_CITY_COUNTRY_NAME"), "VALUE~" => $this->country_full_name, "VALUE" => htmlspecialcharsbx($this->country_full_name), ), "REGION_NAME" => array( "TITLE" => GetMessage("STAT_CITY_REGION_NAME"), "VALUE~" => $this->region_name, "VALUE" => htmlspecialcharsbx($this->region_name), ), "CITY_NAME" => array( "TITLE" => GetMessage("STAT_CITY_CITY_NAME"), "VALUE~" => $this->city_name, "VALUE" => htmlspecialcharsbx($this->city_name), ), ); } /** * @return array[string]mixed */ function GetDescription() { $res = /*.(array[string]mixed).*/array(); $res["ID"] = "CCityLookup"; $res["DESCRIPTION"] = ""; $res["IS_INSTALLED"] = false; $res["CAN_LOOKUP_COUNTRY"] = true; $res["CAN_LOOKUP_CITY"] = false; return $res; } /** * @return bool */ function IsInstalled() { return false; } /** * @return void */ function Lookup() { $this->country_code = "N0"; $this->country_short_name = "N00"; $this->country_full_name = "NA"; } } class CStatRegion { /** * @param array[string]string $arOrder * @param array[string]string $arFilter * @return CDBResult */ function GetList($arOrder = /*.(array[string]string).*/array(), $arFilter = /*.(array[string]string).*/array()) { $DB = CDatabase::GetModuleConnection('statistic'); $arQueryOrder = array(); if(is_array($arOrder)) { foreach($arOrder as $strColumn => $strDirection) { $strColumn = mb_strtoupper($strColumn); $strDirection = mb_strtoupper($strDirection) === "ASC"? "ASC": "DESC"; switch($strColumn) { case "COUNTRY_ID": case "COUNTRY_SHORT_NAME": case "COUNTRY_NAME": case "REGION_NAME": $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; break; case "REGION": $arQueryOrder["COUNTRY_ID"] = "COUNTRY_ID ".$strDirection; $arQueryOrder["REGION_NAME"] = "REGION_NAME ".$strDirection; break; default: break; } } } $obQueryWhere = new CSQLWhere; $obQueryWhere->SetFields(array( "COUNTRY_ID" => array( "TABLE_ALIAS" => "R", "FIELD_NAME" => "R.COUNTRY_ID", "FIELD_TYPE" => "string", "JOIN" => "", ), "COUNTRY_SHORT_NAME" => array( "TABLE_ALIAS" => "C", "FIELD_NAME" => "C.SHORT_NAME", "FIELD_TYPE" => "string", "JOIN" => "", ), "COUNTRY_NAME" => array( "TABLE_ALIAS" => "C", "FIELD_NAME" => "C.NAME", "FIELD_TYPE" => "string", "JOIN" => "", ), "REGION_NAME" => array( "TABLE_ALIAS" => "R", "FIELD_NAME" => "R.REGION", "FIELD_TYPE" => "string", "JOIN" => "", ), )); $strSql = " SELECT R.COUNTRY_ID ,C.SHORT_NAME COUNTRY_SHORT_NAME ,C.NAME COUNTRY_NAME ,R.REGION REGION_NAME FROM b_stat_city R INNER JOIN b_stat_country C on C.ID = R.COUNTRY_ID "; $strQueryWhere = $obQueryWhere->GetQuery($arFilter); if($strQueryWhere <> '') { $strSql .= " WHERE ".$strQueryWhere." "; } $strSql .= " GROUP BY R.COUNTRY_ID, R.REGION, C.SHORT_NAME, C.NAME "; if(count($arQueryOrder) > 0) { $strSql .= " ORDER BY ".implode(", ", $arQueryOrder)." "; } return $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__); } } class CCity { private $lookup_class = ""; private $lookup = /*.(CCityLookup).*/null; private $country_code = ""; private $city_id = ""; /** * @param string $dbRecord */ function __construct($dbRecord = "") { if($dbRecord <> '') $arDBRecord = unserialize($dbRecord, ['allowed_classes' => false]); else $arDBRecord = false; if(is_array($arDBRecord)) { $this->lookup_class = $arDBRecord["LC"]; if(!$this->lookup_class || !class_exists(mb_strtolower($this->lookup_class))) $this->lookup_class = "CCityLookup"; $this->lookup = call_user_func_array(array($this->lookup_class, "OnCityLookup"), array($arDBRecord["LD"])); $this->country_code = $arDBRecord["CC"]; $this->city_id = $arDBRecord["CI"]; } else { $this->lookup_class = $this->GetHandler(); if(!$this->lookup_class || !class_exists(mb_strtolower($this->lookup_class))) $this->lookup_class = "CCityLookup"; $ob = call_user_func_array(array($this->lookup_class, "OnCityLookup"), array()); if(!$ob || !$ob->IsInstalled()) { $this->lookup_class = "CCityLookup"; $this->lookup = new CCityLookup; } else { $this->lookup = $ob; } } } /** * @param array[string]string $arOrder * @param array[string]string $arFilter * @return CDBResult */ public static function GetList($arOrder = /*.(array[string]string).*/array(), $arFilter = /*.(array[string]string).*/array()) { $DB = CDatabase::GetModuleConnection('statistic'); if(!is_array($arOrder)) $arOrder = array(); $arQueryOrder = array(); foreach($arOrder as $strColumn => $strDirection) { $strColumn = mb_strtoupper($strColumn); $strDirection = mb_strtoupper($strDirection) === "ASC"? "ASC": "DESC"; switch($strColumn) { case "COUNTRY_ID": case "COUNTRY_SHORT_NAME": case "COUNTRY_NAME": case "REGION_NAME": case "CITY_NAME": $arQueryOrder[$strColumn] = $strColumn." ".$strDirection; break; case "CITY": $arQueryOrder["COUNTRY_ID"] = "COUNTRY_ID ".$strDirection; $arQueryOrder["REGION_NAME"] = "REGION_NAME ".$strDirection; $arQueryOrder["CITY_NAME"] = "CITY_NAME ".$strDirection; break; default: break; } } $obQueryWhere = new CSQLWhere; $obQueryWhere->SetFields(array( "CITY_ID" => array( "TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.ID", "FIELD_TYPE" => "int", "JOIN" => "", ), "COUNTRY_ID" => array( "TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.COUNTRY_ID", "FIELD_TYPE" => "string", "JOIN" => "", ), "COUNTRY_SHORT_NAME" => array( "TABLE_ALIAS" => "C", "FIELD_NAME" => "C.SHORT_NAME", "FIELD_TYPE" => "string", "JOIN" => "", ), "COUNTRY_NAME" => array( "TABLE_ALIAS" => "C", "FIELD_NAME" => "C.NAME", "FIELD_TYPE" => "string", "JOIN" => "", ), "REGION_NAME" => array( "TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.REGION", "FIELD_TYPE" => "string", "JOIN" => "", ), "CITY_NAME" => array( "TABLE_ALIAS" => "CITY", "FIELD_NAME" => "CITY.NAME", "FIELD_TYPE" => "string", "JOIN" => "", ), )); $strSql = " SELECT CITY.ID CITY_ID ,CITY.COUNTRY_ID ,C.SHORT_NAME COUNTRY_SHORT_NAME ,C.NAME COUNTRY_NAME ,CITY.REGION REGION_NAME ,CITY.NAME CITY_NAME FROM b_stat_city CITY INNER JOIN b_stat_country C on C.ID = CITY.COUNTRY_ID "; $strQueryWhere = $obQueryWhere->GetQuery($arFilter); if($strQueryWhere <> '') { $strSql .= " WHERE ".$strQueryWhere." "; } if(count($arQueryOrder) > 0) { $strSql .= " ORDER BY ".implode(", ", $arQueryOrder)." "; } return $DB->Query($strSql, false, "File: ".__FILE__."
Line: ".__LINE__); } /** * @return string */ function ForSQL() { $DB = CDatabase::GetModuleConnection('statistic'); return $DB->ForSQL(serialize(array( "LC" => $this->lookup_class, "LD" => $this->lookup->ArrayForDB(), "CC" => $this->country_code, "CI" => $this->city_id, ))); } function GetFullInfo() { $this->GetCityID(); return $this->lookup->GetFullInfo(); } public static function GetHandler() { $selected = COption::GetOptionString("statistic", "IP_LOOKUP_CLASS", ""); if(!$selected) { $arResolvers = array(); foreach(GetModuleEvents("statistic", "OnCityLookup", true) as $arEvent) { $ob = ExecuteModuleEventEx($arEvent); $ar = $ob->GetDescription(); $arResolvers[$ar["CLASS"]] = $ob; } if(!array_key_exists($selected, $arResolvers)) { foreach($arResolvers as $ID => $ob) { if($ob->IsInstalled()) { $selected = $ID; break; } } } COption::SetOptionString("statistic", "IP_LOOKUP_CLASS", $selected); } return $selected; } function GetCountryCode() { if(!$this->country_code) { $this->lookup->Lookup(); $this->country_code = $this->lookup->country_code; } return $this->country_code? $this->country_code: "N0"; } function Recode($str) { if($str && $this->lookup->charset) { global $APPLICATION; $str = $APPLICATION->ConvertCharset($str, $this->lookup->charset, LANG_CHARSET); } return $str; } function GetCityID() { $DB = CDatabase::GetModuleConnection('statistic'); $country_code = $this->GetCountryCode(); if(!$this->city_id) $this->city_id = $this->lookup->city_id; if(!$this->city_id) { $city_name = $this->Recode($this->lookup->city_name); $region_name = $this->Recode($this->lookup->region_name); $rs = $DB->Query(" SELECT ID FROM b_stat_city WHERE COUNTRY_ID = '".$DB->ForSQL($country_code, 2)."' AND ".($region_name? "REGION = '".$DB->ForSQL($region_name, 255)."'": "REGION IS NULL")." AND ".($city_name? "NAME = '".$DB->ForSQL($city_name, 255)."'": "NAME IS NULL")." "); $ar = $rs->Fetch(); if($ar) { $this->city_id = $ar["ID"]; } else { $rs = $DB->Query(" SELECT ID FROM b_stat_country WHERE ID = '".$DB->ForSQL($country_code, 2)."' "); $ar = $rs->Fetch(); if(!$ar) { $country_short_name = $this->Recode($this->lookup->country_short_name); $country_full_name = $this->Recode($this->lookup->country_full_name); $DB->Query(" INSERT INTO b_stat_country (ID, SHORT_NAME, NAME) VALUES ( '".$DB->ForSql($country_code, 2)."', ".($country_short_name? "'".$DB->ForSql($country_short_name, 3)."'": "'N00'").", ".($country_full_name? "'".$DB->ForSql($country_full_name, 50)."'": "'NA'")." ) "); } $this->city_id = $DB->Add("b_stat_city", array( "COUNTRY_ID" => $country_code, "REGION" => $region_name ? $region_name: false, "NAME" => $city_name ? $city_name: false, )); } } return $this->city_id > 0? intval($this->city_id): ""; } public static function GetGraphArray($arFilter, &$arLegend, $sort = false, $top = 0) { $err_mess = "File: ".__FILE__."
Line: "; global $arCityColor; $DB = CDatabase::GetModuleConnection('statistic'); $arSqlSearch = Array(); if(is_array($arFilter)) { foreach ($arFilter as $key => $val) { if(is_array($val)) { if(count($val) <= 0) continue; } else { if( ((string)$val == '') || ($val === "NOT_REF") ) continue; } $match_value_set = array_key_exists($key."_EXACT_MATCH", $arFilter); $key = strtoupper($key); switch($key) { case "COUNTRY_ID": if ($val!="NOT_REF") $arSqlSearch[] = GetFilterQuery("C.COUNTRY_ID",$val,"N"); break; 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." 23:59:59", "FULL"); break; } } } $arrDays = array(); $arLegend = array(); $strSqlSearch = GetFilterSqlSearch($arSqlSearch); $strSql = " SELECT ".$DB->DateToCharFunction("D.DATE_STAT","SHORT")." DATE_STAT, ".$DB->DateFormatToDB("DD", "D.DATE_STAT")." DAY, ".$DB->DateFormatToDB("MM", "D.DATE_STAT")." MONTH, ".$DB->DateFormatToDB("YYYY", "D.DATE_STAT")." YEAR, D.CITY_ID, D.SESSIONS, D.NEW_GUESTS, D.HITS, D.C_EVENTS, C.NAME, C.SESSIONS TOTAL_SESSIONS, C.NEW_GUESTS TOTAL_NEW_GUESTS, C.HITS TOTAL_HITS, C.C_EVENTS TOTAL_C_EVENTS FROM b_stat_city_day D INNER JOIN b_stat_city C ON (C.ID = D.CITY_ID) WHERE ".$strSqlSearch." ORDER BY D.DATE_STAT, D.CITY_ID "; $rsD = $DB->Query($strSql, false, $err_mess.__LINE__); while ($arD = $rsD->Fetch()) { $arrDays[$arD["DATE_STAT"]]["D"] = $arD["DAY"]; $arrDays[$arD["DATE_STAT"]]["M"] = $arD["MONTH"]; $arrDays[$arD["DATE_STAT"]]["Y"] = $arD["YEAR"]; $arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["SESSIONS"] = $arD["SESSIONS"]; $arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["NEW_GUESTS"] = $arD["NEW_GUESTS"]; $arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["HITS"] = $arD["HITS"]; $arrDays[$arD["DATE_STAT"]][$arD["CITY_ID"]]["C_EVENTS"] = $arD["C_EVENTS"]; $arLegend[$arD["CITY_ID"]]["CITY_ID"] = intval($arD["CITY_ID"]); $arLegend[$arD["CITY_ID"]]["NAME"] = $arD["NAME"]; $arLegend[$arD["CITY_ID"]]["SESSIONS"] += $arD["SESSIONS"]; $arLegend[$arD["CITY_ID"]]["NEW_GUESTS"] += $arD["NEW_GUESTS"]; $arLegend[$arD["CITY_ID"]]["HITS"] += $arD["HITS"]; $arLegend[$arD["CITY_ID"]]["C_EVENTS"] += $arD["C_EVENTS"]; $arLegend[$arD["CITY_ID"]]["TOTAL_SESSIONS"] = $arD["TOTAL_SESSIONS"]; $arLegend[$arD["CITY_ID"]]["TOTAL_NEW_GUESTS"] = $arD["TOTAL_NEW_GUESTS"]; $arLegend[$arD["CITY_ID"]]["TOTAL_HITS"] = $arD["TOTAL_HITS"]; $arLegend[$arD["CITY_ID"]]["TOTAL_C_EVENTS"] = $arD["TOTAL_C_EVENTS"]; } if($sort) { CStatisticSort::Sort($arLegend, $sort); } if($top) { $totals = array( "CITY_ID" => 0, "NAME" => GetMessage("STAT_CITY_OTHER"), "SESSIONS" => 0, "NEW_GUESTS" => 0, "HITS" => 0, "C_EVENTS" => 0, "TOTAL_SESSIONS" => 0, "TOTAL_NEW_GUESTS" => 0, "TOTAL_HITS" => 0, "TOTAL_C_EVENTS" => 0, ); $i = 0; while(count($arLegend) > $top) { $i++; $tail = array_pop($arLegend); $totals["SESSIONS"] += $tail["SESSIONS"]; $totals["NEW_GUESTS"] += $tail["NEW_GUESTS"]; $totals["HITS"] += $tail["HITS"]; $totals["C_EVENTS"] += $tail["C_EVENTS"]; $totals["TOTAL_SESSIONS"] += $tail["TOTAL_SESSIONS"]; $totals["TOTAL_NEW_GUESTS"] += $tail["TOTAL_NEW_GUESTS"]; $totals["TOTAL_HITS"] += $tail["TOTAL_HITS"]; $totals["TOTAL_C_EVENTS"] += $tail["TOTAL_C_EVENTS"]; } if($i) $arLegend[0] = $totals; foreach($arrDays as $DATE_STAT => $arDate) { foreach($arDate as $CITY_ID => $arCity) { if(intval($CITY_ID) > 0) { if(!array_key_exists($CITY_ID, $arLegend)) { $arrDays[$DATE_STAT][0]["CITY_ID"] = 0; $arrDays[$DATE_STAT][0]["NAME"] = GetMessage("STAT_CITY_OTHER"); $arrDays[$DATE_STAT][0]["SESSIONS"] += $arCity["SESSIONS"]; $arrDays[$DATE_STAT][0]["NEW_GUESTS"] += $arCity["NEW_GUESTS"]; $arrDays[$DATE_STAT][0]["HITS"] += $arCity["HITS"]; $arrDays[$DATE_STAT][0]["C_EVENTS"] += $arCity["C_EVENTS"]; unset($arrDays[$DATE_STAT][$CITY_ID]); } } } } } $color_getnext = ''; $total = count($arLegend); foreach($arLegend as $key => $arr) { if ($arCityColor[$key] <> '') { $color = $arCityColor[$key]; } else { $color = GetNextRGB($color_getnext, $total); $color_getnext = $color; } $arr["COLOR"] = $color; $arLegend[$key] = $arr; } return $arrDays; } public static function FindFiles($type = 'country', $path = '/bitrix/modules/statistic/ip2country') { $arFiles = array(); $handle = opendir($_SERVER["DOCUMENT_ROOT"].$path); if($handle) { while(false!==($fname = readdir($handle))) { if (is_file($_SERVER["DOCUMENT_ROOT"].$path."/".$fname) && $fname!="." && $fname!="..") { $ext = mb_substr(mb_strtolower($fname), -4); if($ext === ".csv" || $ext === ".txt") { $arFiles[] = $fname; } } } closedir($handle); } $arResult = array(); foreach($arFiles as $file) { $fp = fopen($_SERVER["DOCUMENT_ROOT"].$path."/".$file, "r"); if($fp) { switch(CCity::GetCSVFormatType($fp)) { case "MAXMIND-IP-COUNTRY": if($type == 'country') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "MAXMIND-IP-COUNTRY", ); break; case "IP-TO-COUNTRY": if($type == 'country') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "IP-TO-COUNTRY", ); break; case "MAXMIND-IP-LOCATION": if($type == 'city') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "MAXMIND-IP-LOCATION", ); break; case "MAXMIND-CITY-LOCATION": if($type == 'city') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "MAXMIND-CITY-LOCATION", ); break; case "IPGEOBASE": if($type == 'city') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "IPGEOBASE", ); break; case "IPGEOBASE2": if($type == 'city') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "IPGEOBASE2", ); break; case "IPGEOBASE2-CITY": if($type == 'city') $arResult[] = array( "FILE" => $file, "SIZE" => filesize($_SERVER["DOCUMENT_ROOT"].$path."/".$file), "SOURCE" => "IPGEOBASE2-CITY", ); break; } fclose($fp); } } return $arResult; } public static function GetCSVFormatType($fp) { $line = trim(fgets($fp, 1024), " \t\n\r"); if(preg_match('/maxmind/i', $line)) $line = trim(fgets($fp, 1024), " \t\n\r"); if( preg_match('/^"(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})","(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})","(\d+)","(\d+)","..",".*?"$/', $line, $match) && (ip2number($match[1]) == $match[3]) && (ip2number($match[2]) == $match[4]) ) { return "MAXMIND-IP-COUNTRY"; } elseif( preg_match('/^"\d+","\d+","..","...",".*?"$/', $line) ) { return "IP-TO-COUNTRY"; } elseif( preg_match('/^startIpNum,endIpNum,locId$/', $line) ) { return "MAXMIND-IP-LOCATION"; } elseif( preg_match('/^locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode$/', $line) ) { return "MAXMIND-CITY-LOCATION"; } elseif( preg_match('/^(\d+)\t(\d+)\t(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) - (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\t(..)\t(.+?)\t(.+?)\t(.+?)\t(.+?)\t(.+)$/', $line, $match) && (ip2number($match[3]) == $match[1]) && (ip2number($match[4]) == $match[2]) ) { return "IPGEOBASE"; } elseif( preg_match('/^(\d+)\t(\d+)\t(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) - (\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\t(..)/', $line, $match) && (ip2number($match[3]) == $match[1]) && (ip2number($match[4]) == $match[2]) ) { return "IPGEOBASE2"; } elseif( preg_match('/^\d+\t(.+?)\t(.+?)\t(.+?)\t([0-9.]+?)\t([0-9.]+?)$/', $line) ) { return "IPGEOBASE2-CITY"; } else { return "UNKNOWN"; } } public static function ResolveIPRange($newStartIP, $newEndIP) { global $DB; $rsConflictIP = $DB->Query(" select * from b_stat_city_ip where start_ip between ".$newStartIP." and ".$newEndIP." union select * from b_stat_city_ip where end_ip between ".$newStartIP." and ".$newEndIP." union select * from b_stat_city_ip where START_IP = ( SELECT MAX(START_IP) FROM b_stat_city_ip WHERE START_IP <= ".$newStartIP." ) AND END_IP >= ".$newStartIP." "); $arToUpdate = false; while($arConflictIP = $rsConflictIP->Fetch()) { //Exact match if( $arConflictIP["START_IP"] == $newStartIP && $arConflictIP["END_IP"] == $newEndIP ) { $arToUpdate = $arConflictIP; } //Left overlap elseif( $newStartIP <= $arConflictIP["START_IP"] && $newEndIP < $arConflictIP["END_IP"] ) { //Move conflict to the right $rs = $DB->Query(" UPDATE b_stat_city_ip SET START_IP = '".($newEndIP+1)."' WHERE START_IP = '".$arConflictIP["START_IP"]."' ", true); //Delete if there is new conflict raises if(!$rs) $rs = $DB->Query(" DELETE FROM b_stat_city_ip WHERE START_IP = '".$arConflictIP["START_IP"]."' "); } //Full overlap elseif( $newStartIP <= $arConflictIP["START_IP"] && $arConflictIP["END_IP"] < $newEndIP ) { //Delete $rs = $DB->Query(" DELETE FROM b_stat_city_ip WHERE START_IP = '".$arConflictIP["START_IP"]."' "); } //Right overlap elseif( $arConflictIP["START_IP"] < $newStartIP && $arConflictIP["END_IP"] <= $newEndIP ) { //Move conflict to the left $rs = $DB->Query(" UPDATE b_stat_city_ip SET END_IP = '".($newStartIP-1)."' WHERE START_IP = '".$arConflictIP["START_IP"]."' "); } //Inside else/*if( $arConflictIP["START_IP"] < $newStartIP && $newEndIP < $arConflictIP["END_IP"] )*/ { //Split $rs = $DB->Query(" UPDATE b_stat_city_ip SET END_IP = '".($newStartIP-1)."' WHERE START_IP = '".$arConflictIP["START_IP"]."' "); $rs = $DB->Query(" INSERT INTO b_stat_city_ip (START_IP, END_IP, COUNTRY_ID, CITY_ID) VALUES ('".($newEndIP+1)."', '".$arConflictIP["END_IP"]."', '".$arConflictIP["COUNTRY_ID"]."', '".$arConflictIP["CITY_ID"]."') ", true); //Delete if there is new conflict raises if(!$rs) $rs = $DB->Query(" DELETE FROM b_stat_city_ip WHERE START_IP = '".$arConflictIP["START_IP"]."' "); } } return $arToUpdate; } 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) && $value <> '' ) { $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"]) && $value <> '' ) { $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"; } }