#! /usr/bin/perl -w
use Tk;
use Tk::widgets qw/Dialog ErrorDialog ROText/;
use strict;
use DBI;

my %dbinfo = ( Database => 'logbook', 
	       Host => 'bigmac', 
	       User => 'www', 
	       Password => "" );

my $serial = 0;

my $dbconnect = sprintf("dbi:Pg:dbname=%s;host=%s",$dbinfo{Database},$dbinfo{Host});

my $dbh = DBI->connect($dbconnect, $dbinfo{User}, $dbinfo{Password}) or warn "Connect failed: $!" ;

my $mw = MainWindow->new;
$mw->CmdLine;
$mw->title("Logbook");
$mw->configure(-menu => my $menubar = $mw->Menu);

my $file = $menubar->cascade(qw/-label File -underline 0 -menuitems/ =>
    [
#     [command    => '~Open', -command => [\&load_igc]],
     [command    => '~Exit', -command => [\&exit]],
    ]);

my $db = $menubar->cascade(qw/-label Database -underline 0 -menuitems/ =>
    [
     [command    => 'Connect', -command => [\&dbconnect]],
     [command    => 'Disconnect', -command => [\&dbdisconnect]],
     [command    => 'Configure', -command => [\&dbconfig]],
    ]);

my $report = $menubar->cascade(qw/-label Reports -underline 0 -menuitems/ =>
    [
     [command    => '~Hours', -command => [\&hours]],
     [command    => '~Launch', -command => [\&launch]],
     [command    => '~Gliders', -command => [\&gliders]],
     [command    => '~Types', -command => [\&types]],
     [command    => '~Sites', -command => [\&sites]],
     [command    => '~Weekly', -command => [\&weekly]],
     [command    => '~Logbook', -command => [\&logpage]],
    ]);

my $help = $menubar->cascade(qw/-label Help -underline 0 -menuitems/ =>
    [
      [command    => '~About'],
    ]);

my $DIALOG_ABOUT = $mw->Dialog(
    -title          => 'About widget',
    -bitmap         => 'info',
    -default_button => 'OK',
    -buttons        => ['OK'],
    -text           => "Glider Pilot's Logbook\nPhilip Plane\n" .
		       "\nPerl Version $]" .
                       "\nTk Version $Tk::VERSION\n\n2005/03/28",
);
$help->cget(-menu)->entryconfigure('About',
    -command => [$DIALOG_ABOUT => 'Show'],
);

my $text = $mw->Scrolled("ROText")->pack();

sub launch {
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration,launch from logbook group by launch});
    my $header = "Launch               Flights      Time\n\n";
    flights($sql,$header,'launch');
}

sub sites {
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration, location from logbook group by location});
    my $header = "Location             Flights       Time\n\n";
    flights($sql,$header,'location');
}

sub gliders {
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration, registration from logbook group by registration});
    my $header = "Reg                  Flights       Time\n\n";
    flights($sql,$header,'registration');
}

sub types {
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration, type from logbook group by type});
    my $header = "Type                 Flights       Time\n\n";
    flights($sql,$header,'type');
}

sub hours {
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration, crew from logbook group by crew});
    my $header = "                     Flights       Time\n\n";
    flights($sql,$header,'crew');
}

sub flights {
    my $sql = shift @_;
    my $header = shift @_;
    my $ftype = shift @_;
    if ( ! defined $dbh ){
	# no db
    }
    $text->delete("1.0",'end');
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my $flights = 0;
    my $duration = 0;
    $text->insert('end', $header);
    while (my $row = $sth->fetchrow_hashref ){
	$flights += $row->{flights};
	$duration += $row->{duration};
	$text->insert('end',sprintf("%-20s\t%4d\t%7s\n", $row->{$ftype},  $row->{flights}, sprintf('%d:%02d',int($row->{duration}/60),$row->{duration}%60)));
    }
    $text->insert('end',sprintf("\nTotals             \t%4d\t%7s\n", $flights, sprintf('%d:%02d',int($duration/60),$duration%60)));
    if ($sth->err) {
	$text->insert('end',$sth->errstr);
    }
    $sth->finish;
}


sub dbconnect {
    if ( defined $dbh ){
	$dbh->disconnect;
    }
    $dbconnect = sprintf("dbi:Pg:dbname=%s;host=%s",$dbinfo{Database},$dbinfo{Host});
    $dbh = DBI->connect($dbconnect, $dbinfo{User}, $dbinfo{Password}) or warn "Connect failed: $!";
}

sub dbdisconnect {
    $dbh->disconnect;
}

sub dbconfig {
    $text->delete("1.0",'end');
    foreach(qw/Host Database User Password/){
	my $w = $text->Label(-text => "$_:", 
			  -relief => 'groove',
			  -anchor => 'e', 
			  -width => 15);
	$text->windowCreate('end', -window => $w);
	$w = $text->Entry(-width => 20, -textvariable => \$dbinfo{$_});
	$text->windowCreate('end', -window => $w);
	$text->insert('end',"\n");
    }
}

sub newentry {
# get a fresh logbook entry

#    Column    |         Type         | Modifiers 
#--------------+----------------------+-----------
# serial       | integer              | 
# date         | date                 | 
# type         | text                 | 
# registration | character varying(6) | 
# location     | text                 | 
# launch       | character(1)         | 
# crew         | character varying(2) | 
# duration     | integer              | 
# comment      | text                 | 
# igc          | character(12)        |
    my %log = ( Serial => "", 
	       Date => "", 
	       Type => "", 
	       Registration => "", 
	       Location => "", 
	       Launch => "", 
	       Crew => "", 
	       Duration => "", 
	       Comment => "", 
	       IGC => "" );

    $text->delete("1.0",'end');
    my $w = $text->Label(-text => "Serial", 
			 -relief => 'groove',
			 -anchor => 'e', 
			 -width => 15);
    $text->windowCreate('end', -window => $w);
    $w = $text->Entry(-width => 20, -textvariable => \$log{Serial});
    $text->windowCreate('end', -window => $w);
    $text->insert('end',"\n");
}

sub weekly {
    my $ftype = 'date';
    my $start = '20050314';
    $text->delete("1.0",'end');
    my $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration, date from logbook where crew = 'P1' and date >= %s group by date },$dbh->quote($start));
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    if ($sth->err) {
	print $sth->errstr;
    }
    while (my $row = $sth->fetchrow_hashref ){
	$text->insert('end',sprintf("%-20s\t%4d\t%6s\n", $row->{$ftype} ,$row->{flights}, sprintf('%d:%02d',int($row->{duration}/60),$row->{duration}%60)));
    }
    $sth->finish;

    $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration from logbook where crew = 'P1' and date >= %s },$dbh->quote($start));
    $sth = $dbh->prepare($sql);
    $sth->execute;
    if ($sth->err) {
	print $sth->errstr;
    }
    my $row = $sth->fetchrow_hashref;
    $text->insert('end',sprintf("\t\t\t%4d\t%6s\n", $row->{flights}, sprintf('%d:%02d', int($row->{duration}/60),$row->{duration}%60)));
    $sth->finish;
}

sub logpage {
    my $page = 19;
    $text->delete("1.0",'end');
    my $sql = sprintf(qq{select serial, date, type, registration, location, launch, crew, duration, comment from logbook where serial > %d order by serial limit %d  },$serial,$page);
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    if ($sth->err) {
	print $sth->errstr;
    }
    while (my $row = $sth->fetchrow_hashref ){
	$text->insert('end',
		      sprintf("%4d %s %15s %2s %15s %s %2s %6s\n", 
			      $row->{serial} ,
			      $row->{date},
			      $row->{type}, 
			      $row->{registration}, 
			      $row->{location}, 
			      $row->{launch}, 
			      $row->{crew}, 
			      sprintf('%d:%02d',int($row->{duration}/60), $row->{duration}%60) 
			      ));
    }
    $sth->finish;

    $serial += $page;
    $text->insert('end',"\n");

    $sql = sprintf(qq{select count(serial) as flights, sum(duration) as duration from logbook where serial <= %s },$dbh->quote($serial));
    $sth = $dbh->prepare($sql);
    $sth->execute;
    if ($sth->err) {
	print $sth->errstr;
    }
    my $row = $sth->fetchrow_hashref;
	$text->insert('end',sprintf("\t\tTotal\t%4d\t%6s\n", $row->{flights}, sprintf('%d:%02d', int($row->{duration}/60),$row->{duration}%60)));

    $sql = sprintf(qq{select crew, count(serial) as flights, sum(duration) as duration from logbook where serial <= %s group by crew},$dbh->quote($serial));
    $sth = $dbh->prepare($sql);
    $sth->execute;
    if ($sth->err) {
	print $sth->errstr;
    }
    while (my $row = $sth->fetchrow_hashref ){
	$text->insert('end',sprintf("\t\t%s\t%4d\t%6s\n", $row->{crew}, $row->{flights}, sprintf('%d:%02d', int($row->{duration}/60),$row->{duration}%60)));
    }
    $sth->finish;


}
MainLoop;