UIR-ADR update

config.php

<?
$dbHost = 'localhost';
$dbUser = '...';
$dbPass = '...';
$dbDatabase = '...';

functions.inc.php

<?

/**
* 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;
}

uir.php

<?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);
	}

updater.php

<?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";