#!/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.

use DBI;
use CGI;
use Time::JulianDay;

my %lex = ('View' => 'View',
	   'Eoy' => 'Eoy',
	   'Main' => 'Main',
	   'Dates' => 'Dates',
	   'School Year' => 'School Year',
	   'School Start' => 'School Start',
	   'School End' => 'School End',
	   'Term' => 'Term',
	   'Date' => 'Date',
	   'Type' => 'Type',
	   'Description' => 'Description',
	   'Track' => 'Track',
	   'Error' => 'Error',
	   'Grade' => 'Grade',
	   'Fraction' => 'Fraction',
	   'Cycle' => 'Cycle',
	   'Day' => 'Day',
	   'No' => 'No',
	   'Yes' => 'Yes',
	   'Closed' => 'Closed',
	   'Open' => 'Open',
	   'Start' => 'Start',
	   'End' => 'End',
	   'Total' => 'Total',

	   'Continue' => 'Continue',
	   'Database' => 'Database',
	   'Remote' => 'Remote',
	   'Select' => 'Select',
	   

	   );

my %dowclosed = ('0' => 1, '6' => 1); # Day 0 is Sunday, Day 6 is Saturday

my $self = 'dateview_past.pl';


# Read config variables
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";
    }
}


my $title = "$lex{View} $lex{'School Year'} - $schoolname";
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">
$chartype\n</head><body>\n};

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

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


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

} else {
    delete $arr{page};
    showSchoolYear( $dbhr );
}



#----------------
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{Remote} $lex{Database}</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;

}


#-----------------
sub showSchoolYear {
#-----------------

    my $db = $arr{db};

    # 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";
	}
    }


    # populate jdclosed with dates from dates table; used by calcTermDays;
    my %jdclosed;
    my $sth = $dbh->prepare("select date, dayfraction from dates 
     where date is not NULL and date != ''");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    
    while ( my ( $date, $dayfraction ) = $sth->fetchrow ) {
	my $jd = julian_day( split( /-/, $date ) );
	$jdclosed{$jd} = $dayfraction;
    }



    # Print Term Dates stored in configuration system.
    print qq{<div style="float:left;">\n};  # left side div

    print qq{<table cellpadding="5" border="0" cellspacing="0">\n};
    print qq{<tr><td class="ra">$lex{'School Year'}</td><td class="bla">$schoolyear</td></tr>\n};
    print qq{<tr><td class="ra">$lex{'School Start'}</td><td class="bla">$schoolstart</td></tr>\n};
    print qq{<tr><td class="ra">$lex{'School End'}</td><td class="bla">$schoolend</td></tr>\n};
    print qq{</table><p></p>\n};


# Start
# Print Terms 
# Loop through all tracks.
foreach my $trk ( sort keys %g_MTrackTerm ) {

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

    print qq{<tr><td colspan="3" class="bcn">$lex{Track} $trk - $g_TrackDisplay{$trk}</td></tr>\n};
    print qq{<tr><td colspan="3" class="bcn">$lex{Grade} };
    foreach my $grade ( sort {$a <=> $b} keys %g_MTrackTermType ) {
	if ( $g_MTrackTermType{$grade} eq $trk ) { print qq{$grade } }
    }
    print qq{</td></tr>\n};


    # Print Terms in this Track
    my $totaldays;
    print qq{<tr><th></th><th>$lex{Start}</th><th>$lex{End}</th><th>$lex{Open}</th><th>$lex{Closed}</th></tr>\n};
    foreach my $trm ( sort keys %{ $g_MTrackTerm{$trk} } ) {

	print qq{<tr><td class="bla">$lex{Term} $trm ($g_TermDisplay{$trk}{$trm})</td>};
	print qq{<td class="la">$g_MTrackTerm{$trk}{$trm}{start}</td>};
	print qq{<td class="la">$g_MTrackTerm{$trk}{$trm}{end}</td>\n};

	my $val = calcTermDays($g_MTrackTerm{$trk}{$trm}{start}, $g_MTrackTerm{$trk}{$trm}{end}, \%jdclosed );
	my ($open,$closed) = split(':', $val);
	$totaldays += $open;
	print qq{<td class="cn">$open</td><td class="cn">$closed</td></tr>\n};

    }
    
    print qq{<tr><td colspan="3" class="bra">$lex{Total}</td><td class="bcn">$totaldays</td><td></td></tr>\n};
    print qq{</table>\n};
}


print qq{</div>\n};



    # Print the Stored Dates (in Dates Table)
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="float:left;margin:0 2em;">\n};
    print qq{<caption style="font-size:130%;font-weight:bold;">$lex{Dates} $lex{Closed}</caption>\n};

    print qq{<tr><th>$lex{Date}</th><th>$lex{Day}</th><th>$lex{Type}</th>\n};
    print qq{<th>$lex{Description}</th><th>$lex{Cycle}<br>$lex{Day}</th>};
    print qq{<th>$lex{Day}<br>$lex{Fraction}</tr>\n};


    my $sth = $dbh->prepare("select * from dates order by date");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    while ( my ( $id, $date, $type, $desc1, $desc2, $dayincycle, $dayfraction ) = $sth->fetchrow ) {
	my ($year, $month, $day ) = split(/-/, $date);
	my $newdate = $year .'-'. $s_month[$month]. q{-}. $day;
	my $jd = julian_day( split( /-/, $date ) );
	my $dow = day_of_week($jd) + 1;

	if ( not $dayincycle ) { 
	    $dayincycle = $lex{No}; 
	} else { 
	    $dayincycle = $lex{Yes};
	}

	print qq{<tr><td>$newdate</td><td>$dow[$dow]</td><td>$type</td><td>$desc1</td>\n};
	print qq{<td class="cn">$dayincycle</td><td>$dayfraction</td></tr>\n};

    }

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

} # end of showSchoolYear


#---------------
sub calcTermDays {
#---------------

    # passed a start date and an end date for a term, and a ref to a
    # hash storing the closure dates in julian day format, it will
    # return a count of the days open in the month.

    # outline: convert start/end dates to jd. Loop over all days. Use modulus function to ident weekend days.

    my ($startdate, $enddate, $closeref ) = @_;
    my %jdclosed = %$closeref;

=head
    print qq{JDClosed<br>\n};
    foreach my $key ( sort keys %jdclosed ) {
	print qq{K:$key V:$jdclosed{$key}<br>\n};
    }
    print qq{End JDClosed<br>\n};
=cut

    my $startjd = julian_day(split(/-/,$startdate));
    my $endjd = julian_day(split(/-/,$enddate));

    my %modclosed; # modulus for days closed
    foreach my $inc ( 0..6 ) {
	my $testjd = $startjd + $inc;
	my $dow = day_of_week($testjd);
	if ( $dowclosed{$dow} ) { # $testjd is a closed dow
	    my $mod = $testjd % 7;
	    $modclosed{$mod} = 1;
	}
    }

#    foreach my $d ( sort keys %modclosed ) {
#	print qq{Closed Mod: $d<br>\n};
#    }

    my ($daysopen, $daysclosed);
    foreach my $jd ( $startjd..$endjd ) {

	my $dow = day_of_week( $jd );
	my $mod = $jd % 7;

	if ( $modclosed{$mod} ) { # closed for weekend
	    next;
	}

	# check if a day closed
	my $cl = $jdclosed{ $jd };
	if ( $cl  ) { # this gives us the fraction closed
	    $daysclosed += $cl;
	    # only add on the fraction of day open
	    $daysopen += (1 - $cl );
#	    print qq{<br>INSIDE<br>\n};
	} else {
	    $daysopen += 1;
	}
#	print qq{JD:$jd Count:$daycount<br>\n};

    }

    # print qq{ST:$startdate END:$enddate  Count:$daycount<br>\n};

    return "$daysopen:$daysclosed";

}
