#!/usr/bin/perl
#  Copyright 2001-2020 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 = ('School Enrollment' => 'School Enrollment',
	   'Main' => 'Main',
	   'Enrollment' => 'Enrollment',
	   'Current Date' => 'Current Date',
	   'Aging Date' => 'Aging Date',
	   'Current Enrollment' => 'Current Enrollment',
	   'Student' => 'Student',
	   'Grade' => 'Grade',
	   'Date' => 'Date',
	   'Type' => 'Type',
	   'Description' => 'Description',
	   'Enrolled' => 'Enrolled',
	   'Not Found' => 'Not Found',
	   'Error' => 'Error',
	   'Continue' => 'Continue',

	   );

use DBI;
use CGI;

my $self = 'rptenroldate.pl';

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

my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, 
    $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
$wday++;
#my $currsdate = "$year-$mon-$mday";
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";

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

my $title = "$lex{Enrollment} at $lex{Date}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body style="padding:1em 2em;">\n};

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

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


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

#foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

my $date = $arr{date};


my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;

# First count how many currently enrolled (in student table)
my %enrollments;
my $sth = $dbh->prepare("select distinct grade, count(grade) from student 
  where grade is not NULL and grade != '' group by grade");
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my ($grade, $gcount) = $sth->fetchrow ) {
    $enrollments{$grade} = $gcount;
}

print qq{<h3>Current $lex{Enrollment}</h3>\n};
print qq{<table cellpadding="4" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
print qq{<tr><th>$lex{Grade}</th><th>$lex{Enrollment}</th></tr>\n};
my $enroltotal;
foreach my $grade ( sort {$a <=> $b} keys %enrollments ) {
    print qq{<tr><td class="cn">$grade</td><td class="cn">$enrollments{$grade}</td></tr>\n};
    $enroltotal += $enrollments{$grade};
}
print qq{<tr style="background-color:#DDD;"><td class="bcn">Total</td>};
print qq{<td class="bcn">$enroltotal</td></tr>\n};
print qq{</table>\n};

my %currenrol = %enrollments;


# Now count backwards through entry records until we reach date of interest
my $sth1 = $dbh->prepare("select * from transfer where to_days(date) >= to_days('$date') 
  order by date desc");
$sth1->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }

# Setup query to get grade
my $sth2 = $dbh->prepare("select lastname, firstname, grade from studentall where studnum = ?");

while ( my $ref = $sth1->fetchrow_hashref ) {

    my %tr = %$ref;

    # Find student grade
    $sth2->execute( $tr{studnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($ln, $fn, $grade)  = $sth2->fetchrow;
    if ( not $grade ) {
	print qq{<div>$lex{Error}: $fn $ln $tr{studnum} $lex{'Not Found'}: };
	print qq{$tr{date} - $tr{type}</div>\n};
	next;
    }

#    print qq{<div>$tr{date} - $tr{type} - $tr{studnum} - $grade</div>\n};

    # Change Enrollment Numbers
    if ( $tr{type} ne 'withdraw' ) { # then was an enrol
	$enrollments{$grade}--;
	print qq{<div>$tr{date} - <b>Enrol $grade</b> $fn $ln $grade $tr{date}</div>\n};
    } else {
	$enrollments{$grade}++;
	print qq{<div>$tr{date} - <b>Withdraw $grade</b> $fn $ln $grade $tr{date}</div>\n};
    }
}

# Now display the enrollment by grade
print qq{<h3>$lex{Date} $date</h3>\n};
print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
print qq{<tr><th>$lex{Grade}</th><th>$lex{Enrollment}</th><th>Change</th></tr>\n};
my ($enroltotal,$difftotal);
foreach my $grade ( sort {$a <=> $b} keys %enrollments ) {
    my $diff = $enrollments{$grade} - $currenrol{$grade};
    print qq{<tr><td class="cn">$grade</td><td class="cn">$enrollments{$grade}</td>};
    print qq{<td class="cn">$diff</td></tr>\n};
    $enroltotal += $enrollments{$grade};
    $difftotal += $diff;
    
}
print qq{<tr style="background-color:#DDD;"><td class="bcn">Total</td>};
print qq{<td class="bcn">$enroltotal</td><td class="bcn">$difftotal</td></tr>\n};
print qq{</table></body></html>\n};




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

    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};

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

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

    print qq{<tr><td class="bra">$lex{'School Enrollment'} $lex{Date}</td><td class="la">\n};
    print qq{<input type="text" name="date" size="12" id="date">\n};
    print qq{<button type="reset" id="start_trigger">...</button></td></tr>\n};

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

    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :    "date", // id of the input field
        ifFormat       :    "%Y-%m-%d", // format of the input field
        button         :    "start_trigger", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    })};

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

    exit;

}
