#!/usr/bin/perl
#  Copyright 2001-2018 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

my %lex = ('View' => 'View',
	   'Staff' => 'Staff',
	   'Absences' => 'Absences',
	   'Main' => 'Main',
	   'No Records Found' => 'No Records Found',
	   'Error' => 'Error',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',

	   'Select' => 'Select',
	   'Year' => 'Year',
	   'Continue' => 'Continue',
	   
	   );


use DBI;
use CGI;
use Cwd;

my $self = 'staffabsview_past.pl';


eval require "../../etc/admin.conf.root";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;

my $dbtype = 'mysql';
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Load Main admin configuration ('admin')
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}


# Print Page Header
my $title = "$lex{View} $lex{Staff} $lex{Absences} - Previous Years";
print qq{$doctype\n<html><head><title>$title</title>
 <link rel="stylesheet" href="$css" type="text/css">
 </head>\n};

print qq{<body>[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="/ssp.html">SSP</a> ]\n};

print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();

} else {
    delete $arr{page};
    showReport();
}



#-------------
sub showReport {
#-------------

    my $db = $arr{db};

    my $temp = $db;
    $temp =~ s/$dbase//; # strip database name;
    my $year = $temp - 1; # previous year;
    $year = qq{$year-$temp}; # now in 2018-2019 format

    print qq{<h3>Year $year</h3>\n};
    
    # Load Remote database
    my $dsn = "DBI:mysql:database=$db;host=$remotehost";
    my $dbh = DBI->connect($dsn,$remoteuser,$remotepassword);

    # Load configuration values from that year.
    my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while (	my ($id, $datavalue) = $sth->fetchrow ) {
	eval $datavalue;
	if ( $@ ) {
	    print "$lex{Error}: $@<br>\n";
	    die "$lex{Error}: $@\n";
	}
    }


    my $sth = $dbh->prepare("show columns from staff_absent");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while (my @cols = $sth->fetchrow ) {
	if ( $cols[0] eq 'id' or $cols[0] eq 'comment' ) { next; }
	push @fields, $cols[0];
    }


    $sth = $dbh->prepare("select * from staff_absent order by lastname, firstname, adate desc");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;

    if ( $rows < 1 ) {
	print qq{<p>$lex{'No Records Found'}</p>\n};
	print qq{</body></html>\n};
	exit;
    }

    
    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};

    print qq{<tr>};
    foreach my $fld (@fields ) {
	print qq{<th>$fld</th>\n};
    }
    print qq{</th></tr>\n};

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	print qq{<tr>\n};
	foreach my $field ( @fields ) {
	    print qq{<td>$r{$field}</td>};
	}
	print qq{</tr>\n};
    }

    
    print qq{</table>\n};
    
    print qq{<p>[ <a href="$homepage">$lex{Main}</a> |\n};
    print qq{<a href="$eoypage">$lex{Eoy}</a> ]</p>\n};
    
    print qq{</body></html>\n};

    exit;
    
} # end of showReport



#----------------
sub showStartPage {
#----------------

    # Get remote databases
    my $remotedbase = 'information_schema';
    my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
    my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);


    my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my %remotedb;


    while ( my $db = $sth->fetchrow ) {
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

#	print qq{DB:$db<br>\n};
	if ( $db =~ m/$dbase/ ) {
#	    print qq{Match! $dbase - $db<br>\n";
	    $remotedb{$db} = 1;
	}

    }


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};

    print qq{<tr><td class="bra">$lex{Select} $lex{Year}</td><td></td></tr>\n};
    foreach my $db ( sort keys %remotedb ) {

	my $temp = $db;
	$temp =~ s/$dbase//; # strip database name;
	my $year = $temp - 1; # previous year;
	$year = qq{$year-$temp}; # now in 2018-2019 format
	
	print qq{<tr><td class="la"><input type="radio" name="db" value="$db"> $year ($db)</td></tr>\n};

    }

    print qq{<tr><td class="ra"><input type="submit" value="$lex{Continue}"></td></tr>\n};

    print qq{</table></form>\n};
    print qq{</body></html>\n};

    exit;

}

