#!/usr/bin/perl

#####################################################
# sql2ov2 - Write SQL to OV2 files for Tom Tom Navigator
# (C) 2007, Hugo van der Kooij
#####################################################

use DBI;
use File::Basename;
use Getopt::Std;
use Switch;

getopt('f');
# or die "\nFatal error!\nrequires -f <filename> option to parse <filename>\n\n";
$file = $opt_f;

print "\n\n";

my $database = 'DBI:mysql:apmap:localhost';
my $db = DBI->connect($database,'apmap','apmap');

open(FILE1, ">$file-open.ov2");
binmode(FILE1);

my $sql = "SELECT SSID,Latitude,Lontitude FROM apmap WHERE WEP='N'";
my $query = $db->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$query->execute() or die "Couldn't execute query '$sql': $DBI::errstr\n";
$south = 9000000;
$north = -9000000;
$east = 18000000;
$west = -18000000;
$size = 0;
while (($SSID,$Lat,$Lon) = $query->fetchrow_array()) {
   $length = length($SSID)+14;
   if ($Lat < $south) { $south = $Lat }
   if ($Lat > $north) { $north = $Lat }
   if ($Lon < $east) { $east = $Lon }
   if ($Lon > $west) { $west = $Lon }
   $size = $size + $length;
   }
print "Lat: $north - $south\n";
print "Lon: $west - $east\n";
$type = "\1";
$size = $size + 21;
$len = pack("L",$size);
$west = pack("l",$west);
$south = pack("l",$south);
$east = pack("l",$east);
$north = pack("l",$north);
$buffer = $type.$len.$west.$north.$east.$south;
#$buffer = $type.$len.$west.$south.$east.$north;
#$buffer = $type.$len.$east.$north.$west.$south;
print FILE1 $buffer;

my $sql = "SELECT SSID,Latitude,Lontitude FROM apmap WHERE WEP='N'";
my $query = $db->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$query->execute() or die "Couldn't execute query '$sql': $DBI::errstr\n";
while (($SSID,$Lat,$Lon) = $query->fetchrow_array()) {
   $type = "\2";
   $SSID = $SSID."\0";
   $length = length($SSID)+13;
   $len = pack("L",$length);
   $lat = pack("l",$Lat);
   $lon = pack("l",$Lon);
   $buffer = $type.$len.$lon.$lat.$SSID;
   print FILE1 $buffer;
#   print "[$Lat|$Lon]\t$length\t$SSID\n";
   }
print "Size = $size\n";

close(FILE1);

open(FILE2, ">$file-closed.ov2");
binmode(FILE2);

my $sql = "SELECT SSID,Latitude,Lontitude FROM apmap WHERE WEP='Y'";
my $query = $db->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$query->execute() or die "Couldn't execute query '$sql': $DBI::errstr\n";
$south = 9000000;
$north = -9000000;
$east = 18000000;
$west = -18000000;
$size = 0;
while (($SSID,$Lat,$Lon) = $query->fetchrow_array()) {
   $length = length($SSID)+14;
   if ($Lat < $south) { $south = $Lat }
   if ($Lat > $north) { $north = $Lat }
   if ($Lon < $east) { $east = $Lon }
   if ($Lon > $west) { $west = $Lon }
   $size = $size + $length;
   }
print "Lat: $north - $south\n";
print "Lon: $west - $east\n";
$type = "\1";
$size = $size + 21;
$len = pack("L",$size);
$west = pack("l",$west);
$south = pack("l",$south);
$east = pack("l",$east);
$north = pack("l",$north);
$buffer = $type.$len.$west.$south.$east.$north;
print FILE2 $buffer;

my $sql = "SELECT SSID,Latitude,Lontitude FROM apmap WHERE WEP='Y'";
my $query = $db->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$query->execute() or die "Couldn't execute query '$sql': $DBI::errstr\n";
while (($SSID,$Lat,$Lon) = $query->fetchrow_array()) {
   $type = "\2";
   $SSID = $SSID."\0";
   $length = length($SSID)+13;
   $len = pack("L",$length);
   $lat = pack("l",$Lat);
   $lon = pack("l",$Lon);
   $buffer = $type.$len.$lon.$lat.$SSID;
   print FILE2 $buffer;
#   print "[$Lat|$Lon]\t$length\t$SSID\n";
   }
print "Size = $size\n";

close(FILE2);