Import/Core_geoip.php
[Pman.Core] / Import / Core_geoip.php
1 <?php
2
3 require_once 'Pman/Roo.php';
4
5 class Pman_Core_Import_Core_geoip extends Pman_Roo
6 {
7     static $cli_desc = "Insert the geoip database";
8     
9     static $cli_opts = array();
10     
11     var $id_mapping = array();
12
13     function getAuth()
14     {
15         $ff = HTML_FlexyFramework::get();
16         if (!$ff->cli) {
17             die("access denied");
18         }
19         HTML_FlexyFramework::ensureSingle(__FILE__, $this);
20         return true;
21     }
22     
23     function post()
24     {
25         $this->get();
26     }
27     
28     function get()
29     {
30         
31         PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
32         
33         $location = '/tmp/GeoLite2-City-Locations.csv';
34         $block = '/tmp/GeoLite2-City-Blocks.csv';
35         
36         if(!file_exists($location) || !file_exists($block)){
37             $this->jerr('GeoLite2-City-Locations.csv OR GeoLite2-City-Blocks.csv does not exists?!');
38         }
39         
40         $this->log("Insert location data start");
41         
42         $this->insertLocation($location);
43         
44         $this->log("Insert Block data start");
45         
46         $this->insertBlock($block);
47         
48         $this->jok("DONE");
49     }
50     
51     function insertLocation($csv)
52     {
53         ini_set("auto_detect_line_endings", true);
54         
55         $fh = fopen($csv, 'r');
56         if (!$fh) {
57             $this->jerr("invalid location file");
58         }
59         
60         $req = array(
61             'GEONAME_ID', 'CONTINENT_CODE', 'CONTINENT_NAME',
62             'COUNTRY_ISO_CODE', 'COUNTRY_NAME', 'SUBDIVISION_ISO_CODE',
63             'SUBDIVISION_NAME', 'CITY_NAME', 'METRO_CODE',
64             'TIME_ZONE'
65         );
66         
67         $cols = false;
68         
69         while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
70             if(!array_filter($n)){ // empty row
71                 continue;
72             }
73             
74             if (!$cols) {
75                 $cols = array();
76                 foreach($n as $k) {
77                     $cols[] = strtoupper(trim($k));
78                 }
79                 
80                 if (empty($cols)) {
81                     continue;
82                 }
83                 foreach($req as $r) {
84                     if (!in_array($r,$cols)) {
85                         $cols = false;
86                         break;
87                     }
88                 }
89                 continue;
90             }
91             
92             $row = array();
93             
94             foreach($cols as $i=>$k) {
95                 $row[$k] = trim($n[$i]);
96             }
97             
98             $this->processLocation($row);
99         }
100         
101     }
102     
103     function insertBlock($csv)
104     {
105         ini_set("auto_detect_line_endings", true);
106         
107         $fh = fopen($csv, 'r');
108         if (!$fh) {
109             $this->jerr("invalid location file");
110         }
111         
112         $req = array(
113             'NETWORK_START_IP', 'NETWORK_MASK_LENGTH', 'GEONAME_ID',
114             'REGISTERED_COUNTRY_GEONAME_ID', 'REPRESENTED_COUNTRY_GEONAME_ID', 'POSTAL_CODE',
115             'LATITUDE', 'LONGITUDE', 'IS_ANONYMOUS_PROXY',
116             'IS_SATELLITE_PROVIDER'
117         );
118         
119         $cols = false;
120         
121         while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
122             if(!array_filter($n)){ // empty row
123                 continue;
124             }
125             
126             if (!$cols) {
127                 $cols = array();
128                 foreach($n as $k) {
129                     $cols[] = strtoupper(trim($k));
130                 }
131                 
132                 if (empty($cols)) {
133                     continue;
134                 }
135                 foreach($req as $r) {
136                     if (!in_array($r,$cols)) {
137                         $cols = false;
138                         break;
139                     }
140                 }
141                 continue;
142             }
143             
144             $row = array();
145             
146             foreach($cols as $i=>$k) {
147                 $row[$k] = trim($n[$i]);
148             }
149             
150             $this->processBlock($row);
151         }
152         
153     }
154     
155     
156     
157     function processLocation($row)
158     {
159         $continent = $this->processContinent($row['CONTINENT_CODE'], $row['CONTINENT_NAME']);
160         
161         $country = $this->processCountry($row['COUNTRY_ISO_CODE'], $row['COUNTRY_NAME'], $continent);
162         
163         $division = $this->processDivision($row['SUBDIVISION_ISO_CODE'], $row['SUBDIVISION_NAME']);
164         
165         $city = $this->processCity($row['CITY_NAME'], $row['METRO_CODE'], $row['TIME_ZONE'], $country, $division);
166         
167         if(!empty($city) && !empty($city->id)){
168             $this->id_mapping[$row['GEONAME_ID']] = $city->id;
169         }
170     }
171     
172     function processContinent($code, $name)
173     {
174         if(empty($code)){
175             return false;
176         }
177         
178         $continent = DB_DataObject::factory('core_geoip_continent');
179         if(!$continent->get('code', $code)){
180             $continent->setFrom(array(
181                 'code' => $code,
182                 'name' => (!empty($name)) ? $name : $code
183             ));
184
185             $continent->insert();
186         }
187         
188         return $continent;
189     }
190     
191     function processCountry($code, $name, $continent)
192     {
193         if(empty($code)){
194             return false;
195         }
196         
197         $country = DB_DataObject::factory('core_geoip_country');
198         if(!$country->get('code', $code)){
199             $country->setFrom(array(
200                 'code' => $code,
201                 'name' => (!empty($name)) ? $name : $code,
202                 'continent_id' => (!empty($continent) && !empty($continent->id)) ? $continent->id : 0
203             ));
204
205             $country->insert();
206         }
207         
208         return $country;
209     }
210     
211     function processDivision($code, $name)
212     {
213         if(empty($code)){
214             return false;
215         }
216         
217         $division = DB_DataObject::factory('core_geoip_division');
218         if(!$division->get('code', $code)){
219             $division->setFrom(array(
220                 'code' => $code,
221                 'name' => (!empty($name)) ? $name : $code
222             ));
223
224             $division->insert();
225         }
226         
227         return $division;
228     }
229     
230     function processCity($name, $metro_code, $time_zone, $country, $division)
231     {
232         if(empty($name)){
233             return false;
234         }
235         
236         $city = DB_DataObject::factory('core_geoip_city');
237         
238         if($city->get('name', $name)){
239             return $city;
240         }
241         
242         $city->setFrom(array(
243             'name' => $name,
244             'metro_code' => $metro_code,
245             'time_zone' => $time_zone,
246             'country_id' => (!empty($country) && !empty($country->id)) ? $country->id : 0,
247             'division_id' => (!empty($division) && !empty($division->id)) ? $division->id : 0
248         ));
249         
250         $city->insert();
251         
252         return $city;
253         
254     }
255     
256     function processBlock($row)
257     {
258         if(empty($this->id_mapping[$row['GEONAME_ID']])){
259             $this->log("Missing mapping for {$row['GEONAME_ID']}");
260             $this->log("IP : {$row['NETWORK_START_IP']}");
261             return;
262         }
263         
264         $network_mapping = DB_DataObject::factory('core_geoip_network_mapping');
265         
266         $start_ip = array_pop(explode(":", $row['NETWORK_START_IP']));
267         
268         $network_mapping->setFrom(array(
269             'start_ip' => $start_ip,
270             'mask_length' => pow(2, (128 - $row['NETWORK_MASK_LENGTH'])),
271             'city_id' => $this->id_mapping[$row['GEONAME_ID']]
272         ));
273         
274         if(!$network_mapping->find(true)){
275             $network_mapping->insert();
276         }
277         
278         $location = DB_DataObject::factory('core_geoip_location');
279         if(!$location->get('city_id', $network_mapping->city_id)){
280             $location->setFrom(array(
281                 'latitude' => $row['LATITUDE'],
282                 'longitude' => $row['LONGITUDE'],
283                 'city_id' => $network_mapping->city_id
284             ));
285         }
286         
287         
288         if(!empty($row['POSTAL_CODE'])){
289             $city = DB_DataObject::factory('core_geoip_city');
290             if($city->get($network_mapping->city_id)){
291                 return;
292             }
293
294             $oc = clone($city);
295             $city->postal_code = $row['POSTAL_CODE'];
296             
297             $city->update($oc);
298         }
299         
300     }
301     
302     function log($str)
303     {
304         echo "$str \n";
305     }
306 }