mysql/core_enum_name.sql

[Pman.Core] / mysql / core_cities_merge.sql

diff --git a/mysql/core_cities_merge.sql b/mysql/core_cities_merge.sql
index d4426c3..edea689 100644 (file)
--- a/mysql/core_cities_merge.sql
+++ b/mysql/core_cities_merge.sql
@@ -116,7 +116,23 @@ CREATE FUNCTION core_cities_merge_division()  RETURNS INT DETERMINISTIC
                 
             END IF;
 
-            SELECT id INTO v_division_id FROM core_geoip_division ;
+            SELECT 
+                    id 
+            INTO 
+                    v_division_id 
+            FROM 
+                    core_geoip_division 
+            WHERE 
+                    (
+                            name = v_local_name 
+                        OR 
+                            (
+                                    name LIKE (CONCAT('%', v_local_name, '%')) 
+                                AND 
+                                    code = SUBSTRING_INDEX(v_iso, '-', -1)
+                            ) 
+                    )
+                    AND country_id = v_country_id;
 
             IF v_division_id = 0 THEN
                 
@@ -155,13 +171,16 @@ CREATE FUNCTION core_cities_merge_city()  RETURNS INT DETERMINISTIC
         DECLARE v_geo_lat INT DEFAULT 0;
         DECLARE v_geo_lng INT DEFAULT 0;
 
-        DECLARE v_id_tmp INT DEFAULT 0;
+        
         DECLARE v_iso_tmp TEXT DEFAULT '';
+        DECLARE v_iso_tmp_tmp TEXT DEFAULT '';
         DECLARE v_local_name_tmp TEXT DEFAULT '';
         DECLARE v_type_tmp TEXT DEFAULT '';
         DECLARE v_in_location_tmp INT DEFAULT 0;
 
-        DECLARE v_id_tmp_tmp INT DEFAULT 0;
+        DECLARE v_country_id INT DEFAULT 0;
+        DECLARE v_division_id INT DEFAULT 0;
+        DECLARE v_city_id INT DEFAULT 0;
 
         DECLARE ci_csr CURSOR FOR 
         SELECT 
@@ -181,41 +200,53 @@ CREATE FUNCTION core_cities_merge_city()  RETURNS INT DETERMINISTIC
             
             SET v_count = v_count + 1;
 
-            SET v_id_tmp = 0;
-            SET v_id_tmp_tmp = 0;
+            SET v_country_id = 0;
+            SET v_division_id = 0;
+            SET v_city_id = 0;
 
-            SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
+            SET v_iso_tmp = '';
+            SET v_iso_tmp_tmp = '';
+            SET v_local_name_tmp = '';
+            SET v_type_tmp = '';
+            SET v_in_location_tmp = 0;
+            
+            IF v_in_location IS NOT NULL THEN
+                SELECT iso, local_name, type, in_location INTO v_iso_tmp, v_local_name_tmp, v_type_tmp, v_in_location_tmp FROM meta_location WHERE id = v_in_location;
 
-            IF(v_id_tmp = 0) THEN
-                IF v_in_location IS NOT NULL THEN
+                IF v_type_tmp = 'CO' THEN
+                    SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
+                END IF;
 
-                    SELECT iso, local_name, type, in_location INTO v_iso_tmp, v_local_name_tmp, v_type_tmp, v_in_location_tmp FROM meta_location WHERE id = v_in_location;
-                    
-                    IF v_type_tmp = 'CO' THEN
-                        SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
-                        
-                        INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
-                    END IF;
+                IF v_type_tmp = 'RE' THEN
+                    IF v_in_location_tmp != 0 THEN
+
+                        SELECT iso INTO v_iso_tmp_tmp FROM meta_location WHERE id = v_in_location_tmp;
+
+                        SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp_tmp;
 
-                    IF v_type_tmp = 'RE' THEN
-                        SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
-                        
-                        SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location_tmp;
-                        
-                        SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
-                        
-                        INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
                     END IF;
-                    
+
+                    SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_local_name_tmp AND country_id = v_country_id;
+
                 END IF;
 
-                IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
-                    SET v_id_tmp = LAST_INSERT_ID();
+            END IF;
+            
+
 
-                    INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);
+            SELECT id INTO v_city_id FROM core_geoip_city WHERE name = v_local_name AND country_id = v_country_id AND division_id = v_division_id;
+
+            IF(v_city_id = 0) THEN
+                        
+                INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_country_id, v_division_id);
+                   
+                SET v_city_id = LAST_INSERT_ID();
+
+                IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
+                    
+                    INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_city_id);
                 END IF;
                 
-                
             END IF;
 
             IF v_count = v_total THEN