#!/usr/bin/perl

use IO::Socket;
use DBI;
$| = 1;

$dbh = DBI->connect('DBI:mysql:mydb', 'tracker', '') || die "Could not connect to database: $DBI::errstr";

my $sock = IO::Socket::INET->new(LocalHost => 'localhost', LocalPort => '12345', Proto => 'tcp',  Listen => 5, Reuse => 1) or die "Could not create socket: $!\n" unless $sock;


sub sendtomysql
{
        my $data = $_;
        chomp($data);
        # 0908242216,0033663282263,GPRMC,212442.000,A,4849.0475,N,00219.4763,E,2.29,,220809,,,A*70,F,imei:359587017313647,101Q,
        my @datas = split(/,/, $data);
        # $dbh->do('INSERT INTO exmpl_tbl VALUES(1, ?)', undef, 'Hello');

        my $trackerdate                         = $dbh->quote($datas[0]);
        my $phone                               = $dbh->quote($datas[1]);
        my $gprmc                               = $dbh->quote($datas[2]);
        my $satelliteDerivedTime                = $dbh->quote($datas[3]);
        my $satelliteFixStatus                  = $dbh->quote($datas[4]);
        my $latitudeDecimalDegrees              = $dbh->quote($datas[5]);
        my $latitudeHemisphere                  = $dbh->quote($datas[6]);
        my $longitudeDecimalDegrees             = $dbh->quote($datas[7]);
        my $longitudeHemisphere                 = $dbh->quote($datas[8]);
        my $speed                               = $dbh->quote($datas[9]);
        my $bearing                             = $dbh->quote($datas[10]);
        my $utcDate                             = $dbh->quote($datas[11]);
        # = $datas[12];
        # = $datas[13];
        my $checksum                            = $dbh->quote($datas[14]);
        my $gpsSignalIndicator                  = $dbh->quote($datas[15]);
        if($datas[16] =~ /imei/)
        {
                $imei                           = $datas[16];
                $other                          = $dbh->quote($datas[17].' '.$datas[18]);
        }
        else
        {
                $imei                           = $datas[17];
                $other                          = $dbh->quote($datas[18].' '.$datas[19]);
        }

        my $imei = $dbh->quote(substr($imei,5));

                print "INSERT INTO gprmc (date, imei, phone, trackerdate, satelliteDerivedTime, satelliteFixStatus, latitudeDecimalDegrees, latitudeHemisphere, longitudeDecimalDegrees, longitudeHemisphere, speed, Bearing, utcDate, Checksum, gpsSignalIndicator, other) VALUES (now(), $imei, $phone, $trackerdate, $satelliteDerivedTime, $satelliteFixStatus, $latitudeDecimalDegrees, $latitudeHemisphere, $longitudeDecimalDegrees, $longitudeHemisphere, $speed, $bearing, $utcDate, $checksum, $gpsSignalIndicator, $other)";
        if($gpsSignalIndicator == "'F'")
        {
                $dbh->do("INSERT INTO gprmc (date, imei, phone, trackerdate, satelliteDerivedTime, satelliteFixStatus, latitudeDecimalDegrees, latitudeHemisphere, longitudeDecimalDegrees, longitudeHemisphere, speed, Bearing, utcDate, Checksum, gpsSignalIndicator, other) VALUES (now(), $imei, $phone, $trackerdate, $satelliteDerivedTime, $satelliteFixStatus, $latitudeDecimalDegrees, $latitudeHemisphere, $longitudeDecimalDegrees, $longitudeHemisphere, $speed, $bearing, $utcDate, $checksum, $gpsSignalIndicator, $other)");
        }
        else
        {
                print $gpsSignalIndicator;
        }
}

my $new_sock;
while($new_sock = $sock->accept())
{
        while(<$new_sock>)
        {
                sendtomysql($_);
        }
}
close($sock);
$dbh->disconnect();