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