git.roojs.org Git - Pman.Core/blob - 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         $this->log("Processing : Continent - {$row['CONTINENT_NAME']}({$row['CONTINENT_CODE']}) | Country - {$row['COUNTRY_NAME']}({$row['COUNTRY_ISO_CODE']}) | Division - {$row['SUBDIVISION_ISO_CODE']}({$row['SUBDIVISION_NAME']}) | City - {$row['CITY_NAME']}");
 160
 161         $continent = $this->processContinent($row['CONTINENT_CODE'], $row['CONTINENT_NAME']);
 162
 163         $country = $this->processCountry($row['COUNTRY_ISO_CODE'], $row['COUNTRY_NAME'], $continent);
 164
 165         $division = $this->processDivision($row['SUBDIVISION_ISO_CODE'], $row['SUBDIVISION_NAME']);
 166
 167         $city = $this->processCity($row['CITY_NAME'], $row['METRO_CODE'], $row['TIME_ZONE'], $country, $division);
 168
 169         if(!empty($city) && !empty($city->id)){
 170             $this->id_mapping[$row['GEONAME_ID']] = $city->id;
 171         }
 172     }
 173
 174     function processContinent($code, $name)
 175     {
 176         if(empty($code)){
 177             return false;
 178         }
 179
 180         $continent = DB_DataObject::factory('core_geoip_continent');
 181         if(!$continent->get('code', $code)){
 182             $continent->setFrom(array(
 183                 'code' => $code,
 184                 'name' => (!empty($name)) ? $name : $code
 185             ));
 186
 187             $continent->insert();
 188         }
 189
 190         return $continent;
 191     }
 192
 193     function processCountry($code, $name, $continent)
 194     {
 195         if(empty($code)){
 196             return false;
 197         }
 198
 199         $country = DB_DataObject::factory('core_geoip_country');
 200         if(!$country->get('code', $code)){
 201             $country->setFrom(array(
 202                 'code' => $code,
 203                 'name' => (!empty($name)) ? $name : $code,
 204                 'continent_id' => (!empty($continent) && !empty($continent->id)) ? $continent->id : 0
 205             ));
 206
 207             $country->insert();
 208         }
 209
 210         return $country;
 211     }
 212
 213     function processDivision($code, $name)
 214     {
 215         if(empty($code)){
 216             return false;
 217         }
 218
 219         $division = DB_DataObject::factory('core_geoip_division');
 220         if(!$division->get('code', $code)){
 221             $division->setFrom(array(
 222                 'code' => $code,
 223                 'name' => (!empty($name)) ? $name : $code
 224             ));
 225
 226             $division->insert();
 227         }
 228
 229         return $division;
 230     }
 231
 232     function processCity($name, $metro_code, $time_zone, $country, $division)
 233     {
 234         if(empty($name)){
 235             return false;
 236         }
 237
 238         $city = DB_DataObject::factory('core_geoip_city');
 239
 240         if($city->get('name', $name)){
 241             return $city;
 242         }
 243
 244         $city->setFrom(array(
 245             'name' => $name,
 246             'metro_code' => $metro_code,
 247             'time_zone' => $time_zone,
 248             'country_id' => (!empty($country) && !empty($country->id)) ? $country->id : 0,
 249             'division_id' => (!empty($division) && !empty($division->id)) ? $division->id : 0
 250         ));
 251
 252         $city->insert();
 253
 254         return $city;
 255
 256     }
 257
 258     function processBlock($row)
 259     {
 260         if(empty($this->id_mapping[$row['GEONAME_ID']])){
 261             $this->log("Missing mapping for {$row['GEONAME_ID']}");
 262             $this->log("IP : {$row['NETWORK_START_IP']}");
 263             return;
 264         }
 265
 266         $network_mapping = DB_DataObject::factory('core_geoip_network_mapping');
 267
 268         $start_ip = array_pop(explode(":", $row['NETWORK_START_IP']));
 269
 270         $network_mapping->setFrom(array(
 271             'start_ip' => $start_ip,
 272             'mask_length' => pow(2, (128 - $row['NETWORK_MASK_LENGTH'])),
 273             'city_id' => $this->id_mapping[$row['GEONAME_ID']]
 274         ));
 275
 276         if(!$network_mapping->find(true)){
 277             $network_mapping->insert();
 278         }
 279
 280         $location = DB_DataObject::factory('core_geoip_location');
 281         if(!$location->get('city_id', $network_mapping->city_id)){
 282             $location->setFrom(array(
 283                 'latitude' => $row['LATITUDE'],
 284                 'longitude' => $row['LONGITUDE'],
 285                 'city_id' => $network_mapping->city_id
 286             ));
 287         }
 288
 289
 290         if(!empty($row['POSTAL_CODE'])){
 291             $city = DB_DataObject::factory('core_geoip_city');
 292             if($city->get($network_mapping->city_id)){
 293                 return;
 294             }
 295
 296             $oc = clone($city);
 297             $city->postal_code = $row['POSTAL_CODE'];
 298
 299             $city->update($oc);
 300         }
 301
 302     }
 303
 304     function log($str)
 305     {
 306         echo "$str \n";
 307     }
 308 }