Created
March 26, 2012 10:52
-
-
Save juzna/2204421 to your computer and use it in GitHub Desktop.
Revisions
-
juzna revised this gist
Mar 26, 2012 . 1 changed file with 5 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,5 @@ <? $dbHost = 'localhost'; $dbUser = '...'; $dbPass = '...'; $dbDatabase = '...'; -
Mar 26, 2012 . 3 changed files with 424 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,146 @@ <? /** * 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 hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -3,6 +3,8 @@ * 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>'; @@ -65,4 +67,22 @@ function akce_adr_update() { // 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 hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,257 @@ <?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"; -
Mar 26, 2012 .There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,68 @@ <?php /** * copy&paste'd from a system so it won't work by itself, need some fixes */ 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"; }