#!/usr/bin/perl
#
# radar2wp.pl by Stefan Tomanek <stefan@pico.ruhr.de>
#
# Inserts speedtraps from radarfalle.de into the waypoints database

use WWW::Mechanize;
use HTML::Entities;
use Unicode::String qw(utf8 latin1);
use DBI();
use Data::Dumper;

sub connect_db {
    my $host = "localhost";
    my $user = "gast";
    my $password = "gast";
    my $database = "geoinfo";
    return DBI->connect("DBI:mysql:database=$database;host=$host", $user, $password, {'RaiseError' => 1});
}

sub insert_trap($$) {
    my ($db, $trap) = @_;
    
    my $name = "[".$trap->{limit}."] ".$trap->{street};
    $name =~ s/ /_/g;
    my $uname = latin1($name)->utf8();
    
    my $comment = latin1($trap->{description})->utf8();
    
    my $sth = $db->prepare('DELETE FROM waypoints WHERE lat=? AND lon=? AND type="SPEEDTRAP"');
    
    $sth->execute($trap->{lati}, $trap->{long});
    $sth->finish();
    
    my $sth2 = $db->prepare( 'INSERT INTO waypoints (name, type, lat, lon, comment) VALUES (?, "SPEEDTRAP", ?, ?, ?);' );
    $sth2->execute($name, $trap->{lati}, $trap->{long}, $comment);
    $sth2->finish();

    print "$name ($comment)\n";
}

sub search_radars($) {
    my ($city) = @_;

    my $db = connect_db();
    my $mech = WWW::Mechanize->new();
    my $url = "http://www.radarfalle.de/radarfallen/";
    $mech->get( $url );
    
    $mech->submit_form(
	form_number => 2,
	fields      => {
			query    => $city,
			art      => "GA"
			}
	);
    my $page = $mech->content();

    my %loc = ();
    foreach (split /\n/, $page) {
	if (/<a href="JavaScript:test\('test','search\.cgi\?query=(\d+)&amp;func=id&amp;cs=ein&amp;art=GA&amp;skin=detail'\);" onmouseover="window\.status='';return true;" onmouseout="window\.status='';return true;">/) {
	   %loc = ();
	   $loc{id} = $1;
	   get_trap($mech, \%loc);
	   insert_trap($db, \%loc);
	}

    }

    $db->disconnect();
}

sub get_trap($\%) {
    my ($mech, $loc) = @_;
    my $id = $loc->{id};
    $mech->get("http://www.radarfalle.de/cgi-bin/db/search.cgi?query=$id&amp;func=id&amp;cs=ein&amp;art=GA&amp;skin=detail" );
    $_ = $mech->content();
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>Beschreibung<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2"> ?(.*?)<\/font><\/td>/s) {
        $loc->{description} = latin1($1)->utf8();
    } 
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>PLZ Ort<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2">(.*?)<\/font><\/td>/s) {
        $loc->{city} = latin1($1)->utf8();
    }
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>Ortsteil<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2"> (.*?)<\/font><\/td>/s) {
        $loc->{part} = latin1($1)->utf8();
    }
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>Straße<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2"> (.*?)<\/font><\/td>/s) {
        $loc->{street} = latin1($1)->utf8();
    }
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>Art<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2"> <img src=\/images\/ges(\d+|fr)\.gif width=25 height=25 alt="Vmax: (\d+|fr) hm\/h"><\/font><\/td>/s) {
        if ($1 eq "fr") {
	    $loc->{limit} = "~";
	} else {
	    $loc->{limit} = $1;
	}
    }
    if (/<td bgcolor="#E4E4E4"><font face="Arial" size="2"><b>Art<\/b><\/font><\/td>.*?<td bgcolor="#E4E4E4"><font face="Arial" size="2"> <img src=\/images\/ampel\.gif width=9 height=25 alt=Ampelüberwachung><\/TD><\/font><\/td>/s) {
        $loc->{limit} = "#";
    }
    if (/<a target=mapquest href="http:\/\/www\.mapquest\.com\/maps\/map\.adp\?zoom=10&latlongtype=decimal&latitude=([0-9.]+)&longitude=([0-9.]+)">/s) {
	$loc->{lati} = $1;
	$loc->{long} = $2;
    }
}

if ($ARGV[0]) {
    search_radars($ARGV[0]);
} else {
    print STDERR 'radar2wp.pl by Stefan "tommie" Tomanek <stefan@pico.ruhr.de>'."\n";
    print STDERR "Usage:\n";
    print STDERR "    radar2wp.pl City\n";
}