The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
#!/usr/bin/perl

=head1 NAME

display_device.cgi - Display tracking data from tracking database

=cut

use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

#This key is good for all URLs in this directory: http://localhost/
my $googlekey='ABQIAAAAlToaDcu4F2r77c0w1h_ZOhT2yXp_ZAY8_ufC3CFXhHIE1NvwkxRyIIIPjGds4QvqilphZic8BRt33A';

my $query = new CGI;

my $dbname="gis";
my $dbuser=undef; #"apache";
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser, "")
           or die($DBI::errstr);
my $sth='';

unless ($query->param('device')) {
  $query->param(-name=>'device', -value=>getlastdevice($dbh) || 1);
}

my $device=int($query->param('device'));
my $limit=int($query->param('limit') || 10);

$sth=$dbh->prepare("SELECT id,device,lat,lon,dtg,speed,heading
                    FROM tracking
                    WHERE device=?
                    ORDER BY dtg
                    DESC LIMIT ?;");
$sth->execute($device, $limit) or die($DBI::errstr);
my $data=$sth->fetchall_arrayref({});#[{},{},...]
my $trackcount=scalar(@$data);

$sth=$dbh->prepare("SELECT AVG(lat) as latavg, AVG(lon) as lonavg, MAX(lat) as latmax, MAX(lon) as lonmax, MIN(lat) as latmin, MIN(lon) as lonmin FROM tracking WHERE device=?;");
$sth->execute($device) or die($DBI::errstr);
my $devicestats=$sth->fetchall_arrayref({});#[{},{},...]
$devicestats=$devicestats->[0]; #{}
#print "latavg: ", $devicestats->{'latavg'}, "\n";
my $latavg=$devicestats->{'latavg'};
my $lonavg=$devicestats->{'lonavg'};
my $latmin=$devicestats->{'latmin'};
my $lonmin=$devicestats->{'lonmin'};
my $latmax=$devicestats->{'latmax'};
my $lonmax=$devicestats->{'lonmax'};

$sth=$dbh->prepare("SELECT device, count(*)
                    FROM tracking
                    GROUP BY device
                    ORDER BY device;");
$sth->execute() or die($DBI::errstr);
my $devicelist=$sth->fetchall_arrayref(); #[[],[],...]
my %devicecount=map {$_->[0]=>$_->[1]} @$devicelist;
$sth->finish;
$dbh->disconnect();

my $layer='';
my $icon='icon0';
my @table=([qw{id device lat lon dtg speed heading}]);

foreach (@$data) {
  my $id=$_->{'id'};
  my $device=$_->{'device'};
  my $lat=$_->{'lat'};
  my $lon=$_->{'lon'};
  my $dtg=$_->{'dtg'};
  my $speed=$_->{'speed'};
  my $heading=$_->{'heading'};

 #//createInfoText($lat,$lon,$device,$dtg,$speed,$heading)
  $layer.=
  qq{
    point = new GPoint($lon, $lat);
    points.push(point);
    html = "<p>ID: $id<br/>Device: $device<br/>Lat: $lat<br/>Lon: $lon<br/>DTG: $dtg</p>";
    marker = createMarker(point, $icon, html);
    map.addOverlay(marker);
  };
  push @table, [$id,$device,$lat,$lon,$dtg,$speed,$heading];
  $icon='icon1';
}
my $lat=$data->[0]->{'lat'};
my $lon=$data->[0]->{'lon'};
my $dtg=$data->[0]->{'dtg'};
my $speed=$data->[0]->{'speed'};
my $heading=$data->[0]->{'heading'};
my $device=$data->[0]->{'device'};
my $form=join "", 
         $query->start_form(-action=>$query->script_name()),
         "Device: ",
         $query->popup_menu(-name=>'device',
	                    -values=>[map {$_->[0]} @$devicelist]),
         $query->submit(-value=>"Go!"),
         $query->end_form();

unless (defined($data)) {
print $query->header(q{text/html}),
      $query->p("Error: database returned bad data.");
} else {
print $query->header(q{text/html}),
q{<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:v="urn:schemas-microsoft-com:vml">
  <head>},
qq{<script src="http://maps.google.com/maps?file=api&v=1&key=$googlekey" type="text/javascript"></script>
    <style type="text/css">v\:* {behavior:url(#default#VML);}</style>
  </head>
}, #end of q{}
qq{
  <body>
    <table>
      <tr>
        <td colspan="2">
          $form
        </td>
      </tr>
      <tr>
        <td valign="top">
          <div id="map" style="width: 480px; height: 360px"></div>
        </td>
        <td>
          <table>
            <tr>
              <td colspan="2">Current Track Properties</td>
            </tr>
            <tr>
              <td>Device</td><td><div id="track_device"></div></td>
            </tr>
            <tr>
              <td>Lat</td><td><div id="track_lat"></div></td>
            </tr>
            <tr>
              <td>Lon</td><td><div id="track_lon"></div></td>
            </tr>
            <tr>
              <td>DTG</td><td><div id="track_dtg"></div></td>
            </tr>
            <tr>
              <td>Speed</td><td><div id="track_speed"></div></td>
            </tr>
            <tr>
              <td>Heading</td><td><div id="track_heading"></div></td>
            </tr>
            <tr>
              <td>Lat Average</td><td><div id="track_latavg"></div></td>
            </tr>
            <tr>
              <td>Lon Average</td><td><div id="track_lonavg"></div></td>
            </tr>
            <tr>
              <td>Lat Min</td><td><div id="track_latmin"></div></td>
            </tr>
            <tr>
              <td>Lon Min</td><td><div id="track_lonmin"></div></td>
            </tr>
            <tr>
              <td>Lat Max</td><td><div id="track_latmax"></div></td>
            </tr>
            <tr>
              <td>Lon Max</td><td><div id="track_lonmax"></div></td>
            </tr>
          </table>
          <table>
            <tr>
              <td colspan="2">Current Map Properties</td>
            </tr>
            <tr>
              <td>Lat</td><td><div id="lat"></div></td>
            </tr>
            <tr>
              <td>Lon</td><td><div id="lon"></div></td>
            </tr>
            <tr>
              <td>maxX</td><td><div id="maxX"></div></td>
            </tr>
            <tr>
              <td>maxY</td><td><div id="maxY"></div></td>
            </tr>
            <tr>
              <td>minX</td><td><div id="minX"></div></td>
            </tr>
            <tr>
              <td>minY</td><td><div id="minY"></div></td>
            </tr>
          </table>
        </td>
      </tr>
   </table>
    <script type="text/javascript">
    //<![CDATA[
    
    var map = new GMap(document.getElementById("map"));
    GEvent.addListener(map, 'move',
      function() {
        var center = map.getCenterLatLng();
        document.getElementById("lat").innerHTML = center.y;
        document.getElementById("lon").innerHTML = center.x;
      }
    );
    GEvent.addListener(map, 'moveend',
      function(overlay) {
        var bounds = map.getBoundsLatLng();
        var center = map.getCenterLatLng();
        document.getElementById("maxX").innerHTML = bounds.maxX;
        document.getElementById("maxY").innerHTML = bounds.maxY;
        document.getElementById("minX").innerHTML = bounds.minX;
        document.getElementById("minY").innerHTML = bounds.minY;
      }
    );
    map.addControl(new GLargeMapControl());
    map.addControl(new GMapTypeControl());
    map.centerAndZoom(new GPoint($lon, $lat), 2);

    function createMarker(point, icon, html) {
      var marker = new GMarker(point, icon);
      GEvent.addListener(marker, 'click', function() {
        marker.openInfoWindowHtml(html);
      });
      return marker;
    }

    var icon0 = new GIcon();
    icon0.image = "http://www.google.com/mapfiles/marker.png";
    icon0.shadow = "http://www.google.com/mapfiles/shadow50.png";
    icon0.iconSize = new GSize(20, 34);
    icon0.shadowSize = new GSize(37, 34);
    icon0.iconAnchor = new GPoint(9, 34);
    icon0.infoWindowAnchor = new GPoint(9, 2);

    var icon1 = new GIcon();
    icon1.image = "http://maps.davisnetworks.com/google/icons/blue-dot-5.png";
    icon1.iconSize = new GSize(5, 5);
    icon1.iconAnchor = new GPoint(3, 3);
    icon1.infoWindowAnchor = new GPoint(3, 1);

    var points = [];
    var marker;
    var point;
    var html;

    $layer
    map.addOverlay(new GPolyline(points));

    document.getElementById("track_lat").innerHTML = "$lat";
    document.getElementById("track_lon").innerHTML = "$lon";
    document.getElementById("track_device").innerHTML = "$device";
    document.getElementById("track_dtg").innerHTML = "$dtg";
    document.getElementById("track_speed").innerHTML = "$speed";
    document.getElementById("track_heading").innerHTML = "$heading";
    document.getElementById("track_latavg").innerHTML = "$latavg";
    document.getElementById("track_lonavg").innerHTML = "$lonavg";
    document.getElementById("track_latmin").innerHTML = "$latmin";
    document.getElementById("track_lonmin").innerHTML = "$lonmin";
    document.getElementById("track_latmax").innerHTML = "$latmax";
    document.getElementById("track_lonmax").innerHTML = "$lonmax";

    //]]>
    </script>},
    $query->p({-align=>"center"}, "Track history for device $device"),
    $query->p({-align=>"center"}, "(Last $trackcount records of", $devicecount{$device}, "reports)"),
    $query->table({-border=>1, -width=>"100%"},
      $query->Tr([map {$query->td($_)} @table])
    ),
qq{
  </body>
</html>
}; #qq
} #if/unless

sub getlastdevice {
  my $dbh=shift();
  $sth=$dbh->prepare("SELECT device FROM tracking ORDER BY dtg DESC LIMIT 1;");
  $sth->execute() or die($DBI::errstr);
  my $data=$sth->fetchall_arrayref();#[{},{},...]
  #print $data->[0]->[0], " ----------------------\n";
  return $data->[0]->[0];
}