blitzortung.mobi DB update

blitzortung_data.class.php

<?php
/* (c) Ingmar Runge 2011 */

class blitzortung_data
{
	protected $cache_dir = NULL;
	protected $ch = NULL;
	protected $bo_user = '', $bo_pass = '';
	protected $interface = '';

	protected $buf_participants, $buf_stations;

	protected $stations = array();
	protected $participants = array();


	public function __construct($cache_dir, $interface = '')
	{
		$this->cache_dir = $cache_dir;
		$this->interface = $interface;

		$this->ch = curl_init();
	}

	public function setLogin($user, $pass)
	{
		$this->bo_user = $user;
		$this->bo_pass = $pass;
	}

	public function retrieve()
	{
		curl_setopt($this->ch, CURLOPT_HEADER, 1);
		curl_setopt($this->ch, CURLOPT_FAILONERROR, 1);
		curl_setopt($this->ch, CURLOPT_RETURNTRANSFER, 1);

		if(!empty($this->interface))
		{
			curl_setopt($this->ch, CURLOPT_INTERFACE, $this->interface);
		}

		//curl_setopt($this->ch, CURLOPT_VERBOSE, 1);

		if(!empty($this->bo_user))
		{
			curl_setopt($this->ch, CURLOPT_USERPWD, $this->bo_user . ':' . $this->bo_pass);
		}

		$this->buf_participants = $this->retrieveFile('participants.txt');
		$this->buf_stations = $this->retrieveFile('stations.txt');

		if(!empty($this->buf_participants) && !empty($this->buf_stations))
		{
			return $this->parse();
		}

		return false;
	}

	protected function retrieveFile($file)
	{
		if(!preg_match('~^[\w-]+\.\w+$~', $file))
		{
			return false;
		}

		$url = 'http://blitzortung.tmt.de/Data/Protected/' . $file;
		$local_file = $this->cache_dir . '/' . $file;
		$etag_file = $local_file . '.etag';
		$etag = '';

		if(file_exists($local_file) && file_exists($etag_file))
		{
			$etag = @file_get_contents($etag_file);
		}

		curl_setopt($this->ch, CURLOPT_URL, $url);

		if(!empty($etag))
		{
			if(preg_match('~^W/(.+?)$~', $etag, $match))
			{
				$etag = $match[1];
			}
			curl_setopt($this->ch, CURLOPT_HTTPHEADER, array('If-None-Match: ' . $etag));
		}
		else
		{
			curl_setopt($this->ch, CURLOPT_HTTPHEADER, array());
		}

		$response = curl_exec($this->ch);

		$http_code = (int)curl_getinfo($this->ch, CURLINFO_HTTP_CODE);

		if($http_code == 200)
		{
			$response = preg_split('~\r?\n\r?\n~', $response, 2);
			$etag = '';

			if(preg_match('~^ETag: (.+?)$~m', $response[0], $match))
			{
				$etag = trim($match[1]);
			}

			file_put_contents($local_file, $response[1]);
			file_put_contents($etag_file, $etag);

			return $response[1];
		}
		elseif($http_code == 304)
		{
			return file_get_contents($local_file);
		}
	
		return false;
	}

	protected function _fix_col($s)
	{
		$s = str_replace('\null', '', $s);
		$s = str_replace("\\'", "'", $s);
		$s = str_replace('&nbsp;', ' ', $s);
		return html_entity_decode($s, ENT_COMPAT, 'UTF-8');
	}

	protected function _to_time($s)
	{
		if(preg_match('~^(.+)(\.\d+)$~', $s, $match))
		{
			$t = (string)strtotime($match[1]) . $match[2];
			if($t < 0) $t = 0;
			return $t;
		}

		return (string)strtotime($s);
	}

	protected function parse()
	{
		if(empty($this->buf_participants) || empty($this->buf_stations))
		{
			return false;
		}

		$stations = array();

		foreach(explode("\n", $this->buf_stations) as $line)
		{
			list($num_id, $idf, $owner, $city, $country, $lat, $lon, $last_signal, $status, $client, $signals) = explode(' ', $line);

			if(empty($idf) || $num_id == 0)
			{
				continue;
			}

			$new = new stdClass();

			$new->num_id = (int)$num_id;
			$new->idf = $idf;
			$new->owner = self::_fix_col($owner);
			$new->city = self::_fix_col($city);
			$new->country = self::_fix_col($country);
			$new->lat = (float)$lat;
			$new->lon = (float)$lon;
			$new->status = $status;
			$new->last_signal = self::_to_time(self::_fix_col($last_signal));
			$new->client = self::_fix_col($client);
			$new->signals = (int)$signals;

			$stations[$idf] = $new;
		}

		$this->stations = $stations;

		$particips = array();

		foreach(explode("\n", $this->buf_participants) as $line)
		{
			$cols = explode(' ', $line);
			list($date, $time, $lat, $lon, $ampere, , $km, $num_part) = $cols;

			$new = new stdClass();
			$new->stations = array_slice($cols, 8);
			$new->time = self::_to_time($date . ' ' . $time);
			$new->lat = (float)$lat;
			$new->lon = (float)$lon;

			if(count($new->stations) > 0)
			{
				$particips[] = $new;
			}
		}

		$this->participants = $particips;

		return true;
	}

	public function resultStations()
	{
		return $this->stations;
	}

	public function resultStrikes()
	{
		return $this->participants;
	}

	public function __destruct()
	{
		if($this->ch)
		{
			curl_close($this->ch);
		}
	}
}

db-update.php

<?php
/* (c) Ingmar Runge 2011 */

date_default_timezone_set('Europe/Berlin');

define('SYSTEMDIR', dirname(__FILE__) . '/../');

require SYSTEMDIR . 'config.php';
require SYSTEMDIR . 'lib/utils.php';
require SYSTEMDIR . 'lib/blitzortung_data.php';

function blitzortung_sync_to_db()
{
	$dbh = new PDO('mysql:host=127.0.0.1;dbname=' . Config::db_name, Config::db_user, Config::db_pass);
	$dbh->exec('SET CHARACTER SET utf8');

	$src = new blitzortung_data(SYSTEMDIR . 'cache/txt', Config::outbound_interface);
	$src->setLogin(Config::bo_user, Config::bo_pass);

	if($src->retrieve())
	{
		$set_sql = 'owner = :owner, city = :city,
			country = :country, latitude = :lat, longitude = :lon, last_signal = :last_signal,
			status = :status, client = :client, signals = :signals';

		// transfer stations:

		$stmt = $dbh->prepare('INSERT INTO stations SET idf = :idf, ' . $set_sql .
			' ON DUPLICATE KEY UPDATE ' . $set_sql);

		foreach($src->resultStations() as $stn)
		{
			$stmt->bindValue(':idf', $stn->idf);
			$stmt->bindValue(':owner', $stn->owner);
			$stmt->bindValue(':city', $stn->city);
			$stmt->bindValue(':country', $stn->country);
			$stmt->bindValue(':lat', $stn->lat);
			$stmt->bindValue(':lon', $stn->lon);
			$stmt->bindValue(':status', $stn->status[0]);
			$stmt->bindValue(':last_signal', (int)$stn->last_signal);
			$stmt->bindValue(':client', $stn->client);
			$stmt->bindValue(':signals', $stn->signals);

			$stmt->execute();

			$err = $stmt->errorInfo();
			if($err[1] != 0)
			{
				die($err[2]);
			}
		}

		$stmt = NULL;

		// transfer strikes:

		$stmt = $dbh->prepare('INSERT INTO strikes SET time = :time, time_actual = :time, latitude = :lat, longitude = :lon
			ON DUPLICATE KEY UPDATE latitude = :lat, longitude = :lon, time_actual = :time');

		foreach($src->resultStrikes() as $stk)
		{
			$stmt->bindValue(':time', $stk->time);
			$stmt->bindValue(':lat', $stk->lat);
			$stmt->bindValue(':lon', $stk->lon);

			$stmt->execute();

			$new_id = (int)$dbh->lastInsertId();
			if($new_id > 0)
			{
				$dbh->exec('DELETE FROM strike_stations WHERE strike_id = ' . $new_id);
				$parti_stmt = $dbh->prepare('INSERT INTO strike_stations SET strike_id = :strike_id,
					strike_time = :time, station_id = (SELECT my_id FROM stations WHERE idf = :idf)');

				foreach($stk->stations as $station_idf)
				{
					$parti_stmt->bindValue(':strike_id', $new_id);
					$parti_stmt->bindValue(':time', (int)$stk->time);
					$parti_stmt->bindValue(':idf', $station_idf);

					$parti_stmt->execute();
				}

				$parti_stmt = NULL;

				$dbh->exec('UPDATE strikes SET num_stations = (SELECT COUNT(*) FROM strike_stations sss WHERE sss.strike_id = strikes.my_id) WHERE my_id = ' . $new_id);
			}
		}

		$stmt = NULL;

		// purge strikes that have been replaced by more accurate entries:

		$first_stk = $src->resultStrikes();
		$first_stk = $first_stk[0];

		$stmt = $dbh->query('SELECT my_id, time FROM strikes WHERE time_actual > ' . ((int)$first_stk->time));

		while($row = $stmt->fetch(\PDO::FETCH_ASSOC))
		{
			$found = false;
			foreach($src->resultStrikes() as $stk)
			{
				if($stk->time === $row['time'])
				{
					$found = true;
				}
			}

			if(!$found)
			{
				$stk_id = (int)$row['my_id'];
				$dbh->exec('DELETE FROM strikes WHERE my_id = ' . $stk_id);
				$dbh->exec('DELETE FROM strike_stations WHERE strike_id = ' . $stk_id);
			}
		}

		$stmt = NULL;

		// update ranking:

		$stmt = $dbh->query('SELECT COUNT(*) FROM strikes WHERE time_actual >= ' . (gmtime() - 3600));
		$total_1h_strikes = (int)$stmt->fetchColumn(0);
		$stmt = NULL;

		$dbh->beginTransaction();
		$dbh->exec('DELETE FROM station_ranking');
		$dbh->exec('INSERT INTO station_ranking (station_id, strike_count, strike_ratio, signal_count, efficiency) SELECT
			my_id, (SELECT COUNT(*) FROM strike_stations ss WHERE ss.station_id = s.my_id AND strike_time >= ' . (gmtime() - 3600) . '),
			0, signals, 0 FROM stations s WHERE signals > 0');
		$dbh->exec('DELETE FROM station_ranking WHERE strike_count = 0');
		$dbh->exec('UPDATE station_ranking SET efficiency = (strike_count / ' . $total_1h_strikes . ' * 100 + strike_count / signal_count * 100) / 2,
			strike_ratio = strike_count / ' . $total_1h_strikes . ' * 100');
		$dbh->exec('UPDATE station_ranking SET efficiency = 100 WHERE efficiency > 100');
		$dbh->exec('UPDATE station_ranking SET strike_ratio = 100 WHERE strike_ratio > 100');
		$dbh->commit();
	}
	else
	{
		die('retrieve() failed!');
	}

	$dbh = NULL;
}

blitzortung_sync_to_db();