function getCountryPostal($config) { $filename = getTempNam(); if (!file_get_contents_curl("http://www.geopostcodes.com/inc/download.php?f=Countries&t=9", $filename)) { return false; } $zip = new Zip_Manager(); $zip->open($filename); $zip->filteredExtractTo('./'); $zip->close(); unlink($filename); $filename = "GeoPC_Countries.csv"; $f = fopen($filename, 'rb'); if (!$f) { return false; } $row = fgetcsv($f, null, ';'); if ($row != array('iso', 'country', 'sovereign', 'postalformat', 'postalname', 'geopc')) { return false; } $pdo = getPDOConnection($config); $pdo->beginTransaction(); $st1 = $pdo->prepare('select id from country where iso2 = ?'); $st2 = $pdo->prepare('update country set postal_format = ?, postal_name = ? where id = ?'); $st3 = $pdo->prepare('insert into country (id, name, postal_format, postal_name, iso2) values (nextval(\'country_id_seq\'), ?,?,?,?)'); while ($row = fgetcsv($f, null, ';')) { if (!$st1->execute(array($row[0]))) { return false; } if ($r = $st1->fetchAll()) { $st2->bindParam(1, trim2($row[3]), PDO::PARAM_STR | PDO::PARAM_NULL); $st2->bindParam(2, trim2($row[4]), PDO::PARAM_STR | PDO::PARAM_NULL); $st2->bindParam(3, $r[0]['id'], PDO::PARAM_INT); if (!$st2->execute()) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } else { $st3->bindParam(1, trim2($row[1]), PDO::PARAM_STR | PDO::PARAM_NULL); $st3->bindParam(2, trim2($row[3]), PDO::PARAM_STR | PDO::PARAM_NULL); $st3->bindParam(3, trim2($row[4]), PDO::PARAM_STR | PDO::PARAM_NULL); $st3->bindParam(4, trim($row[0]), PDO::PARAM_STR | PDO::PARAM_NULL); if (!$st3->execute()) { print_r($row); print_r($pdo->errorInfo()); return false; } } } $pdo->commit(); unlink($filename); return true; }
} if ($r = $st1->fetchAll()) { if (!$st3->execute(array(trim2($row[2]), trim2($row[3]), trim2($row[5]), trim2($row[0]), $r[0]['id']))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } else { if (!$st2->execute(array($row[0], $row[3]))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } if ($r = $st2->fetchAll()) { if (!$st3->execute(array(trim2($row[2]), trim2($row[3]), trim2($row[0]), $r[0]['id']))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } else { if (true || !$st4->execute(array(trim2($row[0]), trim2($row[2]), trim2($row[3])))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } } } $pdo->commit(); unlink($filename); exit(0);
$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) { $bdRow[$idx2] = utf8_encode_recursivo($cell->getValue()); } if (!$st1->execute(array($bdRow[7]))) { print_r($bdRow); print_r($pdo->errorInfo()); exit(1); } if ($r = $st1->fetchAll()) { if (!$st3->execute(array(trim2($bdRow[7]), trim2($bdRow[8]), trim2($bdRow[7]), $r[0]['id']))) { print_r($bdRow); print_r($pdo->errorInfo()); exit(1); } } else { if (!$st2->execute(array(trim2($bdRow[7]), trim2($bdRow[7]), trim2($bdRow[8]), trim2($bdRow[7])))) { print_r($bdRow); print_r($pdo->errorInfo()); exit(1); } } } $pdo->commit(); unlink($filename1);
} if ($r = $st1->fetchAll()) { if (!$st3->execute(array(trim2($row[3]), trim2($row[4]), trim2($row[5]), trim2($row[0]), $r[0]['id']))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } else { if (!$st2->execute(array($row[0], $row[4]))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } if ($r = $st2->fetchAll()) { if (!$st3->execute(array(trim2($row[3]), trim2($row[4]), trim2($row[5]), trim2($row[0]), $r[0]['id']))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } else { if (!$st4->execute(array(trim2($row[0]), trim2($row[3]), trim2($row[4]), trim2($row[5])))) { print_r($row); print_r($pdo->errorInfo()); exit(1); } } } } $pdo->commit(); unlink($filename); exit(0);