-
-
Save iguana007/2204473 to your computer and use it in GitHub Desktop.
UIR-ADR update
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<? | |
$dbHost = 'localhost'; | |
$dbUser = '...'; | |
$dbPass = '...'; | |
$dbDatabase = '...'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<? | |
/** | |
* Parse czech date and return format for mysql | |
*/ | |
function parseDate($czDate) { | |
if(preg_match('/^(\d{2})\.(\d{2})\.(\d{4})(?:-(\d{2}:\d{2}:\d{2}))?$/', $czDate, $m)) { | |
$date = "{$m[3]}-{$m[2]}-{$m[1]}"; | |
if(isset($m[4])) $date .= " {$m[4]}"; | |
return $date; | |
} | |
else throw new Exception("Unable to parse date '$czDate'"); | |
} | |
/** | |
* Parse parameters | |
*/ | |
function getParameters(&$row, $fields) { | |
$dateFields = array('vznik_dne', 'zanik_dne', 'plati_do'); // Columns with date format | |
$cnt = sizeof($row); | |
$ret = array(); | |
for($i = 3; $i < $cnt; ++$i) { | |
list($num, $val) = explode('=', $row[$i], 2); | |
if(isset($fields[$num])) { | |
if($val == '') $val = null; | |
// Convert date format | |
elseif(in_array($fields[$num], $dateFields)) $val = parseDate($val); | |
// Store | |
$ret[$fields[$num]] = $val; | |
} | |
} | |
return $ret; | |
} | |
/** | |
* Import row | |
*/ | |
function importRow($table, &$row, $fields) { | |
$params = getParameters($row, $fields); // Get parameters | |
$pKey = $fields[1]; // Ger primary key | |
$pKeyVal = $params[$pKey]; | |
// Take action | |
switch($row[1]) { | |
// Delete | |
case 0: | |
q("DELETE FROM `$table` WHERE `$pKey`='$pKeyVal'"); | |
break; | |
// Insert | |
case 1: | |
sql_add($table, $params); | |
break; | |
// Update | |
case 2: | |
sql_update($table, $pKey, $pKeyVal, $params); | |
break; | |
default: | |
throw new Exception("Unsupported command {$row[1]}"); | |
} | |
} | |
/** | |
* Execute SQL query | |
* @param string $sql SQL query | |
* @return resource MySQL resource | |
*/ | |
function q($sql) { | |
/* global $fpSql; | |
fwrite($fpSql, "$sql;\n"); | |
*/ | |
$ret = mysql_query($sql); | |
// Check for errors | |
if($err = mysql_error()) echo "MySQL error: $err\n"; | |
return $ret; | |
} | |
function mfo($res) { | |
return mysql_fetch_object($res); | |
} | |
/** | |
* Add row to database | |
* @param string $table Table name | |
* @param array $params Associative array with parameters | |
* @return resource MySQL resource | |
*/ | |
function sql_add($table, $params) { | |
$sql = "INSERT INTO `$table` SET " . make_sql($params); | |
return q($sql); | |
} | |
/** | |
* Updates row in db | |
* @param string $table Table name | |
* @param string $pKey Primary key column name | |
* @param int $pKeyVal Value of primary key | |
* @param array $params Associative array with parameters | |
* @return resource MySQL resource | |
*/ | |
function sql_update($table, $pKey, $pKeyVal, $params) { | |
$sql = "UPDATE `$table` SET " . make_sql($params) . " WHERE `$pKey`='$pKeyVal'"; | |
return q($sql); | |
} | |
/** | |
* Create SQL fraction from parameters | |
* @param array $params Associative array with parameters | |
* @return string | |
*/ | |
function make_sql($params) { | |
$ret = array(); | |
// mysql_real_escape_string | |
foreach($params as $k => $v) { | |
$code = "`$k`="; | |
if(is_null($v)) $code .= "null"; | |
else $code .= "'" . addslashes($v) . "'"; | |
$ret[] = $code; | |
} | |
return implode(', ', $ret); | |
} | |
/** | |
* Connect to MySQL database | |
*/ | |
function dbConnect() { | |
if(!mysql_connect($GLOBALS['dbHost'], $GLOBALS['dbUser'], $GLOBALS['dbPass'])) return false; | |
if(!mysql_select_db($GLOBALS['dbDatabase'])) return false; | |
if(!mysql_query("SET CHARSET CP1250")) return false; | |
return true; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* copy&paste'd from a system so it won't work by itself, need some fixes | |
*/ | |
// find new updates on UIR | |
function akce_adr_update() { | |
@ob_end_flush(); ob_implicit_flush(true); | |
echo '<pre>'; | |
$url = "http://forms.mpsv.cz/uir/view.jsp?D=Verze_42"; | |
$br = new browser; | |
if(!$data = $br->request($url)) return ajax_ret(0, 'Nepodarilo se otevrit URL'); | |
if(!$data = strcut($data, '<TABLE ', '</TABLE>')) return ajax_ret(0, 'Nenalezena tabulka s vypisem souboru'); | |
if(!preg_match_all('|<TR>(.+)</TR>|Usm', $data, $match)) return ajax_ret(0, 'Nepodarilo se nalezt radky tabulky'); | |
// Remove first three rows | |
$rows = $match[1]; | |
array_splice($rows, 0, 3); | |
array_pop($rows); // And last one | |
$rows = array_reverse($rows); | |
// Read rows | |
$updates = 0; | |
foreach($rows as $k => $row) { | |
if(!preg_match('|<TD.+<a href\s*="(?<file>[^"]+)">.*</TD>\s*<TD.*>\s*(?<date>[0-9.]+)\s*</TD>\s*<TD.*>\s*(?<time>[0-9:]+)\s*</TD>|Usmi', $row, $match)) { | |
echo "Unable to parse row $k\n"; | |
continue; | |
} | |
if(!startsWith($match['file'], '../uir/')) { | |
echo "Necekany odkaz - {$match['file']}\n"; | |
continue; | |
} | |
$verze = (int) substr($match['file'], -9, 5); | |
$file = 'http://forms.mpsv.cz/' . substr($match['file'], 3); | |
list($d, $m, $y) = explode('.', $match['date']); | |
$date = date('Y-m-d H:i:s', strtotime("$y-$m-$d {$match['time']}")); | |
// Kontrola zda uz je v nasi databazi | |
$row2 = mfo(q("select * from `verze` where `ver_cislo`='$verze'", 'adresy')); | |
if($row2) { | |
if($row2->cas_uzav) continue; // Uz mame ulozenou a uzavrenou | |
else { | |
$this->adr_update($file); // Updatujeme | |
$updates++; | |
} | |
} | |
else { | |
// Not in DB, try previous version | |
$v2 = $verze - 1; | |
if(!mr("select count(*) from `verze` where `ver_cislo`='$v2'", 'adresy')) { | |
echo "Verze $verze neleze naimportovat, v databazi nam chybi $v2\n"; | |
} | |
else { | |
$this->adr_update($file); // Updatujeme | |
$updates++; | |
} | |
} | |
} | |
// Final msg | |
if($updates) echo "<b>Bylo provedeno celkem $updates updatu</b>\n"; | |
else echo "<b>Dneska zadne updaty</b>\n"; | |
} | |
// process a particual update (given by URL) | |
function adr_update($file) { | |
set_time_limit(60); | |
// Download | |
echo "Downloading...\n"; | |
$dst = "/tmp/" . basename($file); | |
passthru("wget -q -O '$dst' '$file'"); | |
// Run update | |
echo "Running import script...\n"; | |
passthru("php updater.php '$dst'"); | |
// Remove temp file | |
@unlink($dst); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* CLI utility which does the update | |
*/ | |
error_reporting(E_ALL); | |
chdir(dirname(__FILE__)); | |
require('./functions.inc.php'); | |
require('./config.inc.php'); | |
$file = @$_SERVER['argv'][1]; | |
if(empty($file)) die("Pouzijte: 'php updater.php update-file'\n"); | |
if(!file_exists($file)) die("Soubor '$file' neexistuje\n"); | |
echo "Importing $file\n"; | |
// Uncompress first | |
if(strtolower(substr($file, -4)) == '.zip') { | |
echo "Extracting ZIP file...\n"; | |
passthru("unzip -LL -j -o -d tmp '$file'"); | |
$x = strtolower(substr(basename($file), 0, -4)); | |
if(!file_exists($file = "./tmp/$x.txt")) die("Nepodarilo se najit soubor $file\n"); | |
else echo "Using $file\n"; | |
} | |
$crc = 0; | |
$fp = fopen($file, 'r'); | |
// $fpSql = fopen($fileOut = "./sql/" . basename($file), 'w'); | |
// echo "Writing to $fileOut\n"; | |
// Defining variables | |
$hasHeader = $finished = false; | |
for($rowNo = 1; !feof($fp); ++$rowNo) { | |
$row = fgetcsv($fp, 2000, ';'); | |
if(!$row) { | |
if(!$finished) throw new Exception("Row $rowNo is empty"); | |
else continue; | |
} | |
// Ending with backslash | |
while(substr($row[$lst = sizeof($row) - 1], -1) == '\\') { | |
$row2 = fgetcsv($fp, 2000, ';'); | |
$txt = ltrim(array_shift($row2)); | |
$row[$lst] = substr($row[$lst], 0, -1) . $txt; | |
foreach($row2 as $k => $v) $row[] = $v; | |
} | |
if(($rowNo % 10) == 0) echo '.'; // Progress bar :) | |
if(!is_numeric($row[0])) throw new Exception("First parameter must be numeric"); | |
// Action according to first parameter | |
switch((int) $row[0]) { | |
// ChangeLog header | |
case 0: | |
if($row[1] != 'UIR-ADR') throw new Exception("Change log is for '{$row[1]}' application, not for UIR-ADR"); | |
if($row[2] != 4) throw new Exception("Change log version {$row[2]} is not supported"); | |
$version1 = $row[3]; $version2 = $row[4]; | |
echo "Importing version $version1.$version2\n"; | |
$dataVersion = $row[5]; | |
$dataVersionZSJ = $row[6]; | |
$dataVersionClosed = parseDate($row[7]); | |
$hasHeader = true; | |
// Connect to MySQL database | |
if(!dbConnect()) throw new Exception("Unable to connect to database"); | |
// Check for version | |
$row2 = mfo(q("select * from `verze` where `ver_cislo`='$dataVersion'")); | |
if($row2) { | |
if($row2->cas_uzav) throw new Exception("Version $dataVersion is already imported and closed"); | |
} | |
else { | |
// Not in DB, try previous version | |
$v2 = $dataVersion - 1; | |
if(!mfo(q("select * from `verze` where `ver_cislo`='$v2'"))) throw new Exception("Verze $dataVersion neleze naimportovat, v databazi nam chybi $v2"); | |
} | |
break; | |
// Okres | |
case 1: | |
$fields = array(1 => 'okres_kod', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info', 'nuts4', 'kraj_kod'); | |
importRow('okres', $row, $fields); | |
break; | |
// Okres - history | |
case 2: | |
$fields = array(1 => 'okres_kod', 'plati_do', 'nazev', 'zkratka', 'info', 'nuts4', 'kraj_kod'); | |
importRow('okres_h', $row, $fields); | |
break; | |
// Obec | |
case 3: | |
$fields = array(1 => 'obec_kod', 'okres_kod', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info', 'nuts5', 'pou_kod'); | |
importRow('obec', $row, $fields); | |
break; | |
// Obec - history | |
case 4: | |
$fields = array(1 => 'obec_kod', 'plati_do', 'okres_kod', 'nazev', 'zkratka', 'info', 'nuts5', 'pou_kod'); | |
importRow('obec_h', $row, $fields); | |
break; | |
// Cast obce | |
case 5: | |
$fields = array(1 => 'cobce_kod', 'obec_kod', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info'); | |
importRow('cobce', $row, $fields); | |
break; | |
// Cast obce - history | |
case 6: | |
$fields = array(1 => 'cobce_kod', 'plati_do', 'obec_kod', 'nazev', 'zkratka', 'info'); | |
importRow('cobce_h', $row, $fields); | |
break; | |
// Ulice | |
case 7: | |
$fields = array(1 => 'ulice_kod', 'obec_kod', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info'); | |
importRow('ulice', $row, $fields); | |
break; | |
// Ulice - history | |
case 8: | |
$fields = array(1 => 'ulice_kod', 'plati_do', 'obec_kod', 'nazev', 'zkratka', 'info'); | |
importRow('ulice_h', $row, $fields); | |
break; | |
// Objekt | |
case 9: | |
$fields = array(1 => 'objekt_kod', 'cobce_kod', 'cisdom_typ', 'cisdom_hod', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info', 'mcast_kod', 'idob'); | |
importRow('objekt', $row, $fields); | |
break; | |
// Objekt - history | |
case 10: | |
$fields = array(1 => 'objekt_kod', 'plati_do', 'cobce_kod', 'cisdom_typ', 'cisdom_hod', 'info', 'mcast_kod'); | |
importRow('objekt_h', $row, $fields); | |
break; | |
// Adresa | |
case 11: | |
$fields = array(1 => 'adresa_kod', 'objekt_kod', 'ulice_kod', 'cisor_hod', 'cisor_pis', 'psc', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info', 'pcd', 'x', 'y'); | |
importRow('adresa', $row, $fields); | |
break; | |
// Adresa - history | |
case 12: | |
$fields = array(1 => 'adresa_kod', 'plati_do', 'objekt_kod', 'ulice_kod', 'cisor_hod', 'cisor_pis', 'psc', 'info'); | |
importRow('adresa_h', $row, $fields); | |
break; | |
// Posta | |
case 13: | |
$fields = array(1 => 'psc', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info'); | |
importRow('posta', $row, $fields); | |
break; | |
// Posta - history | |
case 14: | |
$fields = array(1 => 'psc', 'plati_do', 'nazev', 'zkratka', 'info'); | |
importRow('ulice_h', $row, $fields); | |
break; | |
// Prazsky obvod, mestska cast | |
case 15: | |
case 16: | |
case 17: | |
case 18: | |
// Skip | |
break; | |
// Oblast | |
case 19: | |
$fields = array(1 => 'oblast_kod', 'nuts2', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info'); | |
importRow('oblast', $row, $fields); | |
break; | |
// Oblast - history | |
case 20: | |
$fields = array(1 => 'oblast_kod', 'plati_do', 'nuts2', 'nazev', 'zkratka', 'info'); | |
importRow('oblast_h', $row, $fields); | |
break; | |
// Kraj | |
case 21: | |
$fields = array(1 => 'kraj_kod', 'nuts3', 'oblast_kod', 'nazev', 'zkratka', 'stav', 'vznik_dne', 'vznik_info', 'zanik_dne', 'zanik_info'); | |
importRow('kraj', $row, $fields); | |
break; | |
// Kraj - history | |
case 22: | |
$fields = array(1 => 'kraj_kod', 'plati_do', 'nuts3', 'oblast_kod', 'nazev', 'zkratka', 'info'); | |
importRow('kraj_h', $row, $fields); | |
break; | |
// Spravni obvody, NUTS4 obvody, Obec s rozsirenou pusobnosti, obec s poverenym uradem | |
case 23: | |
case 24: | |
case 25: | |
case 26: | |
case 27: | |
case 28: | |
case 29: | |
case 30: | |
// Skip | |
break; | |
// Kontaktni udaje na obec (obec_d) | |
case 54: | |
// Skip | |
break; | |
// Vazba (cobce x ulice....) | |
case 55: | |
$fields = array(1 => 'vazba_id', 'mcast_kod', 'cobce_kod', 'ulice_kod', 'psc'); | |
importRow('vazba', $row, $fields); | |
break; | |
// cob_prev - prevod mezi kody obci | |
case 56: | |
// Skip | |
break; | |
// Konec souboru | |
case 999: | |
$finished = true; | |
echo "\nImport is complete, CRC is $crc\n"; | |
// Update DB state | |
q("REPLACE INTO `verze` SET `ver_cislo`='$dataVersion', `ver_zsj`='$dataVersionZSJ', `cas_uzav`='$dataVersionClosed'"); | |
break; | |
default: | |
echo "Unknown row: "; print_r($row);echo "\n\n"; | |
} | |
// Update CRC | |
// TODO: dodelat | |
} | |
echo "---------------------------------------\n\n"; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment