function normalize_ids($url="localhost",$login="******",$password=""){
$conn = mysql_connect($url,$login,$password);
if (!$conn)
die('Could not connect: ' . mysql_error());
mysql_select_db("t3s",$conn);
$sql = "SELECT * FROM ".TZS_COUNTRIES_TABLE;
$result = mysql_query($sql,$conn);
/* Замена id-шек стран
*/
while($row_country = mysql_fetch_array($result)) {
$country_id_new = substr(preg_replace('~\D+~','',sha1(md5($row_country['title_ru']))),0,8);
$country_id_old = $row_country['country_id'];
if($country_id_new != $country_id_old){
//echo 'false '; echo $country_id_new; echo ' ';echo $country_id_old; echo '<br>';
$sql = "UPDATE `".TZS_COUNTRIES_TABLE."` SET country_id=".$country_id_new." WHERE country_id=".$country_id_old;
echo $sql.'<br>';
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_REGIONS_TABLE." SET country_id=".$country_id_new." WHERE country_id=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_CITIES_TABLE." SET country_id=".$country_id_new." WHERE country_id=".$country_id_old;
//echo $sql.'<br>';
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET from_cid=".$country_id_new." WHERE from_cid=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET from_cid=".$country_id_new." WHERE from_cid=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET from_cid=".$country_id_new." WHERE from_cid=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET to_cid=".$country_id_new." WHERE to_cid=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET to_cid=".$country_id_new." WHERE to_cid=".$country_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET to_cid=".$country_id_new." WHERE to_cid=".$country_id_old;
mysql_query($sql,$conn);
}
}
/* Замена id-шек регионов
*/
$sql = "SELECT * FROM ".TZS_REGIONS_TABLE;
$result = mysql_query($sql,$conn);
while($row_region = mysql_fetch_array($result)) {
$region_id_new = (int)substr(preg_replace('~\D+~','',sha1(md5($row_region['title_ru']))),0,8);
$region_id_old = $row_region['region_id'];
if($region_id_new != $region_id_old){
//echo 'false '; echo $country_id_new; echo ' ';echo $country_id_old; echo '<br>';
$sql = "UPDATE ".TZS_REGIONS_TABLE." SET region_id=".$region_id_new." WHERE region_id=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_CITIES_TABLE." SET region_id=".$region_id_new." WHERE region_id=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET from_rid=".$region_id_new." WHERE from_rid=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET from_rid=".$region_id_new." WHERE from_rid=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET from_rid=".$region_id_new." WHERE from_rid=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET to_rid=".$region_id_new." WHERE to_rid=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET to_rid=".$region_id_new." WHERE to_rid=".$region_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET to_rid=".$region_id_new." WHERE to_rid=".$region_id_old;
mysql_query($sql,$conn);
}
}
normalize_coordinates($conn);
/* Замена id-шек городов
*/
$sql = "SELECT * FROM ".TZS_CITIES_TABLE;
$result = mysql_query($sql,$conn);
while($row_city = mysql_fetch_array($result)) {
$city_id_new = substr(preg_replace('~\D+~','',sha1(md5($row_city['title_ru'].number_format($row_city['lat'],3).number_format($row_city['lng'],3)))),0,8);
$city_id_old = $row_city['city_id'];
if($city_id_new != $city_id_old){
$sql = "UPDATE ".TZS_CITIES_TABLE." SET city_id=".$city_id_new." WHERE city_id=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET from_sid=".$city_id_new." WHERE from_sid=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET from_sid=".$city_id_new." WHERE from_sid=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET from_sid=".$city_id_new." WHERE from_sid=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_TRUCK_TABLE." SET to_sid=".$city_id_new." WHERE to_sid=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_SHIPMENT_TABLE." SET to_sid=".$city_id_new." WHERE to_sid=".$city_id_old;
mysql_query($sql,$conn);
$sql = "UPDATE ".TZS_PRODUCTS_TABLE." SET to_sid=".$city_id_new." WHERE to_sid=".$city_id_old;
mysql_query($sql,$conn);
}
}
/* Замена в таблице ids
*/
$sql = "SELECT * FROM ".TZS_CITY_IDS_TABLE;
$result = mysql_query($sql,$conn);
while($row_title = mysql_fetch_array($result)) {
$ids_old = $row_title['ids'];
$city_str = $row_title['title'];
$url = "https://geocode-maps.yandex.ru/1.x/?format=json&results=1000&geocode=$city_str";
$ch = curl_init();
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, $url);
$result_=curl_exec($ch);
curl_close($ch);
$res = json_decode($result_, true);
$cities = find_all_1($res,'name');
$kinds = find_all_1($res,'kind');
$latitude_longitude = find_all_1($res,'pos');
$ids = array();
for($i = 0; $i < count($cities); $i++){
$pieces = explode(' ',$latitude_longitude[$i]);
$lat = substr($pieces[0],0,6);
$lng = substr($pieces[1],0,6);
$ids[] = (int)substr(preg_replace('~\D+~','',sha1(md5($cities[$i].$lat.$lng))),0,8);
}
$ids_new = implode(' ',$ids);
//echo $ids; echo '<br>';
//echo $ids_old; echo '-'; echo $ids_new; echo '<br>';
if($ids_new != $ids_old){
$sql = "UPDATE ".TZS_CITY_IDS_TABLE." SET ids='".$ids_new."' WHERE title='".$city_str."'";
// echo $city_str; echo ' '; echo $sql; echo '<br>';
mysql_query($sql,$conn);
}
}
}
function tzs_yahoo_convert1($key, $city_str)
{
//print("<h1>$city_str</h1>");
//$url = "http://where.yahooapis.com/v1/places.q('".urlencode($city_str)."');start=0;count=1000?format=json&lang=ru&appid=$key";
$url = "https://geocode-maps.yandex.ru/1.x/?format=json&results=1000&geocode={$city_str}";
$ch = curl_init();
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, $url);
$result = curl_exec($ch);
curl_close($ch);
$res = json_decode($result, true);
if (find($res, 'found') == 0) {
return array("error" => 'Город на найден');
}
$cities = find_all_1($res, 'name');
//print_r($cities); echo '<br>';
$latitude_longitude = find_all_1($res, 'pos');
//print_r($latitude_longitude); echo '<br>';
$ids = array();
for ($i = 0; $i < count($cities); $i++) {
$pieces = explode(' ', $latitude_longitude[$i]);
$lat = substr($pieces[0], 0, 6);
$lng = substr($pieces[1], 0, 6);
$ids[] = (int) substr(preg_replace('~\\D+~', '', sha1(md5($cities[$i] . $lat . $lng))), 0, 8);
}
//print_r($ids); echo '<br>';
return array('ids' => $ids);
// $url = "http://where.yahooapis.com/v1/places.q('".urlencode($city_str)."');start=0;count=1000?format=json&lang=ru&appid=$key";
// $ch = curl_init();
// curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
// curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
// curl_setopt($ch, CURLOPT_URL, $url);
// $result=curl_exec($ch);
// curl_close($ch);
// $res = json_decode($result, true);
// if (isset($res["error"])) {
// return array("error" => $res["error"]["description"]);
// }
// $ids = array();
// if (isset($res["places"]) && isset($res["places"]["count"]) && $res["places"]["count"] > 0) {
// foreach ($res["places"]["place"] as $rec) {
// $city_id = isset($rec["locality1 attrs"]) && isset($rec["locality1 attrs"]["woeid"]) ? $rec["locality1 attrs"]["woeid"] : NULL;
// if ($city_id != null)
// array_push($ids, $city_id);
// }
// }
// return array('ids' => $ids);
}
