#!/usr/bin/perl

#
#	netchaser2sql - Convert NetChaser CSV files to SQL
#

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

sub ToWGS84 {
   my $temp = $_[0];
   my @field = split(/ /,$temp);
   switch (@field[1]) {
      case "N" { $sign = 1 }
      case "S" { $sign = -1 }
      case "W" { $sign = -1 }
      case "E" { $sign = 1 }
      }
   my @position = split(/\./,@field[0]);
   if (length(@position[0]) == 5) {
      $deg = substr(@position[0],0,3);
      $min = substr(@position[0],3,2);
      }
   elsif(length(@position[0]) == 4)  {
      $deg = substr(@position[0],0,2);
      $min = substr(@position[0],2,2);
      }
    my $WGS84 = int(($sign * ($deg + ($min + @position[1]/10000)/60)) * 100000);
#   my $WGS84 = "$deg $min ".@position[1];
#   my $WGS84 = "[".@field[0]." ".@position[0]."]";
#   my $WGS84 = @position[0];
   return $WGS84;
   }

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

open(FILE, "<$filename");

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

my @stats = stat($filename);
my @date = localtime($stats[9]);
my $filedate = sprintf("%04d-%02d-%02d",$date[5]+1900,$date[4]+1,$date[3]);

my $sql = $db->prepare("SELECT COUNT(mac_address) FROM apmap");
$sql->execute();
($count) = $sql->fetchrow_array();
$sql->finish();

#print "Number of records: $count\n";
#print "File: $filename ($filedate)\n";
print ".";
$new = 0;

$counter = 1;
while ($ThisLine = <FILE>) {
   chomp($ThisLine);
#   @fields = split(/,/,$ThisLine);
   @parts = quotewords(',',0,$ThisLine);
   $loop = 0;
   foreach (@parts) {
      $fields[$loop] = $_;
      $loop++;
      }
   $mac_address = @fields[0];
   if (!($mac_address =~ /:/ )) { next; }
   $mac_address =~ s/"//g;
   $SSID = @fields[1];
   $SSID =~ s/"//g;
   $SSID =~ s/'/\\'/g;
   $channel = @fields[2];
   $channel =~ s/"//g;
   $signal = @fields[3];
   $signal =~ s/"//g;
   $WEP = @fields[4];
   $WEP =~ s/"//g;
   $Latitude = @fields[5];
   if ($Latitude =~ /""/) { next; }
   $Latitude =~ s/"//g;
   $Lontitude = @fields[6];
   if ($Lontitude =~ /""/) { next; }
   $Lontitude =~ s/"//g;
#   $Lontitude =~ s/^"//;
#   $Lontitude =~ s/".*$//;
   chomp($Lontitude);
   $Lat = &ToWGS84($Latitude);
   $Lon = &ToWGS84($Lontitude);
#         print "$counter\t$mac_address\t$WEB $channel\t$signal\t$Lat\t$Lon\t$SSID\n";
   my $sql = "SELECT COUNT(mac_address) FROM apmap WHERE mac_address='$mac_address'";
   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";
   ($count) = $query->fetchrow_array();
   if ( ($count == 0) ) {
      $db->do("INSERT INTO apmap (mac_address,SSID,channel,signal,WEP,Latitude,Lontitude,date) VALUES('$mac_address','$SSID',$channel,$signal,'$WEP',$Lat,$Lon,'$filedate')");
      if ($new == 0) { print "\n"; }
      print "$counter\t$mac_address\t$WEB $channel\t$signal\t$Lat\t$Lon\t$SSID\n";
      $new = 1;
      }
   else {
      my $sql = "SELECT signal FROM apmap WHERE mac_address='$mac_address'";
      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";
      ($strength) = $query->fetchrow_array();
      if ($strength < $signal) {
         $db->do("UPDATE apmap set SSID='$SSID',channel=$channel,signal=$signal,WEP='$WEP',Latitude=$Lat,Lontitude=$Lon,date='$filedate' WHERE mac_address='$mac_address'");
         if ($new == 0) { print "\n"; }
         print "$counter\t$mac_address\t$WEB $channel\t$signal\t$Lat\t$Lon\t$SSID\n";
         $new = 1;
         }
      }
   $counter++;
   }
my $sql = "DELETE FROM apmap WHERE Latitude=0";
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";

$db->disconnect();

close(FILE);