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") {
