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"; }
if (strlen($FILE_NAME) > 1 && $FILE_NAME === "/" . $filename && $APPLICATION->GetFileAccessPermission($FILE_NAME) >= "W") { $ABS_FILE_NAME = $_SERVER["DOCUMENT_ROOT"] . $FILE_NAME; } } if (!$ABS_FILE_NAME) { echo GetMessage('STATWIZ_IMPORT_ERROR_FILE'); } else { switch ($STEP) { case 0: echo GetMessage('STATWIZ_IMPORT_FILE_LOADING'); echo "<script>Import(1)</script>"; break; case 1: $fp = fopen($ABS_FILE_NAME, "r"); if ($fp) { $file_type = CCity::GetCSVFormatType($fp); } else { $file_type = false; } if ($file_type === "IP-TO-COUNTRY") { i2c_create_db($total_reindex, $reindex_success, $step_reindex, $_SESSION["STATWIZ_INT_PREV"], 5, $FILE_NAME, "ip-to-country.com"); } elseif ($file_type === "MAXMIND-IP-COUNTRY") { i2c_create_db($total_reindex, $reindex_success, $step_reindex, $_SESSION["STATWIZ_INT_PREV"], 5, $FILE_NAME, "maxmind.com"); } elseif ($file_type === "MAXMIND-CITY-LOCATION") { $reindex_success = "Y"; } elseif ($file_type === "MAXMIND-IP-LOCATION") { $reindex_success = "Y"; } else { $reindex_success = "Y"; } if ($reindex_success === "Y") {