<?php
require 'stuff.php';
require 'vendor/autoload.php';
$config = parse_ini_file($argv[1]);
if (!$config) {
echo "Couldn't parse the config file.";
exit(1);
}
$filename = getTempNam();
$year = date("Y");
if (!file_get_contents_curl("ftp://geoftp.ibge.gov.br/organizacao_territorial/divisao_territorial/" . $year . "/dtb_" . $year . ".zip", $filename)) {
exit(1);
}
$filename1 = "DTB_" . $year . "_Municipio.xls";
$zip = new Zip_Manager();
$zip->open($filename);
$zip->filteredExtractTo('./', array('/^' . $filename1 . '$/'));
$zip->close();
unlink($filename);
$pdo = getPDOConnection($config);
$pdo->beginTransaction();
$st1 = $pdo->prepare('select id from city a1 where a1.id = ?');
$st2 = $pdo->prepare('insert into city (id, state_id, name, stat) values (?,' . '(select a1.id from state a1 inner join country a2 on a1.country_id = a2.id where a2.iso2 = \'BR\' and a1.stat = left(cast(? as varchar), 2))' . ', ?, ?)');
$st3 = $pdo->prepare('update city set state_id = COALESCE((select a1.id from state a1 inner join country a2 on a1.country_id = a2.id where a2.iso2 = \'BR\' and a1.stat = left(cast(? as varchar), 2)), state_id)' . ', name = ?, stat = ? where id = ?');
PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp, array('memoryCacheSize' => '2GB'));
$xls = PHPExcel_IOFactory::load($filename1);
$sheet = $xls->getSheet(0);
foreach ($sheet->getRowIterator(2) as $row) {
$bdRow = array();
foreach ($row->getCellIterator() as $idx2 => $cell) {
<?php
require 'stuff.php';
$config = parse_ini_file($argv[1]);
if (!$config) {
echo "Couldn't parse the config file.";
exit(1);
}
$filename = getTempNam();
if (!file_get_contents_curl("http://www.geopostcodes.com/inc/download.php?f=ISO3166-2&t=9", $filename)) {
exit(1);
}
$zip = new Zip_Manager();
$zip->open($filename);
$zip->filteredExtractTo('./');
$zip->close();
unlink($filename);
$filename = "GeoPC_ISO3166-2.csv";
$f = fopen($filename, 'rb');
if (!$f) {
exit(1);
}
$row = fgetcsv($f, null, ';');
if ($row != array('iso', 'country', 'code', 'name', 'altname')) {
exit(1);
}
$pdo = getPDOConnection($config);
$pdo->beginTransaction();
$st1 = $pdo->prepare('select id id from state a1 where a1.iso = ?');
$st2 = $pdo->prepare('select a1.id id from state a1 inner join country a2 on a1.country_id = a2.id where a2.iso = ? and translate(lower(a1.name),\'áàâãäāéèêëíìïóòôõöúùûüūÁÀÂÃÄĀÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜŪçÇ‘\',\'aaaaaaeeeeiiiooooouuuuuAAAAAAEEEEIIIOOOOOUUUUUcC\') = translate(lower(?),\'áàâãäāéèêëíìïóòôõöúùûüūÁÀÂÃÄĀÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜŪçÇ‘\',\'aaaaaaeeeeiiiooooouuuuuAAAAAAEEEEIIIOOOOOUUUUUcC\') ');
$st3 = $pdo->prepare('update state set iso = ?, name = ?, country_id = (select id from country where iso = ?) where id = ?');
function updateByNsg($config)
{
$filename = getTempNam();
if (!file_get_contents_curl('https://nsgreg.nga.mil/NSGDOC/files/doc/Document/GENC%20Standard%20Index%20XML%20Ed2.0.zip', $filename, 'https://nsgreg.nga.mil/doc/view?i=2382')) {
return false;
}
$zip = new Zip_Manager();
$zip->open($filename);
$zip->filteredExtractTo('./', array('/^GENC Standard Index Ed2.0.xml$/'));
$zip->close();
unlink($filename);
$filename = realpath("./GENC Standard Index Ed2.0.xml");
$dom = new DOMDocument();
if (!$dom->load($filename)) {
return false;
}
$dom->documentElement->removeAttributeNS('http://api.nsgreg.nga.mil/schema/genc/2.0', 'genc');
$xpath = new DOMXPath($dom);
// $nodes = $xpath->query('//GENCStandardBaselineIndex/GeopoliticalEntity[encoding/char3Code][encoding/char2Code][encoding/numericCode][name]');
$nodes = $xpath->query('//GENCStandardBaselineIndex/GeopoliticalEntity');
if (!$nodes || !$nodes->length) {
return false;
}
$pdo = getPDOConnection($config);
$st1 = $pdo->prepare('select id from country where iso3 = ?');
$st2 = $pdo->prepare('update country set name = ? where id = ?');
$st3 = $pdo->prepare('insert into country (id, name, iso2, iso3, iso_num, reviewed) values (nextval(\'country_id_seq\'), ?,?,?,?, 0)');
$pdo->beginTransaction();
foreach ($nodes as $node) {
$char3Code = $node->getElementsByTagName('char3Code')->item(0)->nodeValue;
if (!$st1->execute(array($char3Code))) {
print_r($char3Code);
print_r($pdo->errorInfo());
return false;
}
if ($r = $st1->fetchAll()) {
if (!$st2->execute(array($node->getElementsByTagName('name')->item(0)->nodeValue, $r[0]['id']))) {
print_r($char3Code);
print_r($pdo->errorInfo());
return false;
}
} else {
if (!$st3->execute(array($node->getElementsByTagName('name')->item(0)->nodeValue, $node->getElementsByTagName('char2Code')->item(0)->nodeValue, $char3Code, $node->getElementsByTagName('numericCode')->item(0)->nodeValue))) {
print_r($char3Code);
print_r($pdo->errorInfo());
return false;
}
}
/*
* echo $node->getElementsByTagName('char3Code')->item(0)->nodeValue; echo $node->getElementsByTagName('char2Code')->item(0)->nodeValue; echo $node->getElementsByTagName('numericCode')->item(0)->nodeValue; echo $node->getElementsByTagName('name')->item(0)->nodeValue;
*/
}
$st1->closeCursor();
$st2->closeCursor();
$st3->closeCursor();
$st1 = $pdo->prepare('select id id from state a1 where a1.iso6 = ?');
$st2 = $pdo->prepare('select a1.id id from state a1 inner join country a2 on a1.country_id = a2.id where a2.iso3 = ? and a1.name = ?');
$st3 = $pdo->prepare('update state set iso6 = ?, name = ?, country_id = (select id from country where iso3 = ?) where id = ?');
$st4 = $pdo->prepare('insert into state (id, country_id, iso6, name, reviewed) values (nextval(\'state_id_seq\'), (select id from country where iso3 = ?), ?, ?, 0)');
$xpath = new DOMXPath($dom);
// $nodes = $xpath->query('//GENCStandardBaselineIndex/AdministrativeSubdivision[country][encoding/char6Code][name]');
$nodes = $xpath->query('//GENCStandardBaselineIndex/AdministrativeSubdivision');
if (!$nodes || !$nodes->length) {
return false;
}
foreach ($nodes as $node) {
$char6Code = $node->getElementsByTagName('char6Code')->item(0)->nodeValue;
if (!$st1->execute(array($char6Code))) {
print_r($char6Code);
print_r($pdo->errorInfo());
exit(1);
}
if ($r = $st1->fetchAll()) {
if (!$st3->execute(array($char6Code, $node->getElementsByTagName('name')->item(0)->nodeValue, $node->getElementsByTagName('country')->item(0)->nodeValue, $r[0]['id']))) {
print_r($char6Code);
print_r($pdo->errorInfo());
exit(1);
}
} else {
if (!$st2->execute(array($node->getElementsByTagName('country')->item(0)->nodeValue, $node->getElementsByTagName('name')->item(0)->nodeValue))) {
print_r($char6Code);
print_r($pdo->errorInfo());
exit(1);
}
if ($r = $st2->fetchAll()) {
if (!$st3->execute(array($char6Code, $node->getElementsByTagName('name')->item(0)->nodeValue, $node->getElementsByTagName('country')->item(0)->nodeValue, $r[0]['id']))) {
print_r($char6Code);
print_r($pdo->errorInfo());
exit(1);
}
} else {
if (!$st4->execute(array($node->getElementsByTagName('country')->item(0)->nodeValue, $char6Code, $node->getElementsByTagName('name')->item(0)->nodeValue))) {
print_r($char6Code);
print_r($pdo->errorInfo());
exit(1);
}
}
}
}
/*
* echo $node->getElementsByTagName('country')->item(0)->nodeValue; echo $node->getElementsByTagName('char6Code')->item(0)->nodeValue; echo $node->getElementsByTagName('name')->item(0)->nodeValue;
*/
$pdo->commit();
return true;
}