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

#  This file is part of Open Admin for Schools.


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);


# First count how many currently enrolled (in student table)
my %enrollments;  # enrollments{grade}{sex};
my ($mtotal,$ftotal); # male and female totals

#my $sth = $dbh->prepare("select distinct grade, count(grade) from student 
#  where grade is not NULL and grade != '' group by grade");
my $sth = $dbh->prepare("select distinct grade from student 
   where grade is not NULL and grade != ''");
my $sth1 = $dbh->prepare("select count(*) from student where grade = ? and sex = 'F'");
my $sth2 = $dbh->prepare("select count(*) from student where grade = ? and sex = 'M'");

$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my $grade = $sth->fetchrow ) {

    $sth1->execute($grade);
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
    my $female = $sth1->fetchrow;
    $enrollments{$grade}{'F'} = $female;
    $ftotal += $female;
    
    $sth2->execute($grade);
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
    my $male = $sth2->fetchrow;
    $enrollments{$grade}{'M'} = $male;
    $mtotal += $male;

}

# Test result in %enrollments
#foreach my $gr ( sort keys %enrollments ) {
#    print qq{GR:$gr Male: $enrollments{$gr}{'M'} Female: $enrollments{$gr}{'F'}<br>\n};
#}
#print qq{Total M:$mtotal F:$ftotal<br>\n};


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>Boys</th><th>Girls</th></tr>\n};

foreach my $grade ( sort {$a <=> $b} keys %enrollments ) {
    print qq{<tr><td class="cn">$grade</td>};
    print qq{<td class="cn">$enrollments{$grade}{M}</td>};
    print qq{<td class="cn">$enrollments{$grade}{F}</td></tr>\n};
#    $enroltotal += $enrollments{$grade};
}
print qq{<tr style="background-color:#DDD;"><td class="bcn">Total</td>};
print qq{<td class="bcn">$mtotal</td>\n};
print qq{<td class="bcn">$ftotal</td></tr>\n};
print qq{</table>\n};

# copy the 2D hash.
my %currenrol;
foreach my $gr ( sort keys %enrollments ) {
    foreach my $sex ( keys %{$enrollments{$gr}} ) {
	$currenrol{$gr}{$sex} = $enrollments{$gr}{$sex};
    }
}

#foreach my $gr ( sort {$a <=> $b} keys %currenrol ) {
#    print qq{GR:$gr Male: $currenrol{$gr}{'M'} Female: $currenrol{$gr}{'F'}<br>\n};
#}


# 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 and gender
my $sth2 = $dbh->prepare("select lastname, firstname, grade, sex from studentall where studnum = ?");
my @transfers; # array for transfer values


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

    my %tr = %$ref;

    # Find student grade, gender
    $sth2->execute( $tr{studnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($ln, $fn, $grade,$sex)  = $sth2->fetchrow;
    if ( not $grade ) {
	print qq{<div>Enrollment $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 (NOTE: NE!)
	if ($sex eq 'M' or $sex eq 'm' ) {
	    $enrollments{$grade}{'M'}++;
	} elsif ( $sex eq 'F' or $sex eq 'f' ) {
	    $enrollments{$grade}{'F'}++;
	}
	push @transfers, qq{<div>$tr{date} - <b>Enrol $grade</b> $fn $ln $grade }.
	  qq{Gender:$sex $tr{date}</div>\n};
	
    } else {
	if ($sex eq 'M' or $sex eq 'm' ) {
	    $enrollments{$grade}{'M'}--;
	} elsif ( $sex eq 'F' or $sex eq 'f' ) {
	    $enrollments{$grade}{'F'}--;
	}
	
	push @transfers, qq{<div>$tr{date} - <b>Withdraw $grade</b> $fn $ln $grade }.
	  qq{Gender:$sex $tr{date}</div>\n};
    }
}


# Test for Changes to %enrollments hash
#foreach my $gr ( sort {$a <=> $b} keys %enrollments ) {
#    print qq{GR:$gr Male: $enrollments{$gr}{'M'} Female: $enrollments{$gr}{'F'}<br>\n};
#}


# Now display the enrollment by grade
print qq{<h3>Enrollment on $date</h3>\n};
print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
print qq{<tr><th>$lex{Grade}</th><th>Boys</th><th>Change</th><th>Girls</th><th>Change</tr>\n};
my ($mtotdiff, $ftotdiff); # male/female total change
my $mtotal = 0;
my $ftotal = 0; # totals for each gender
foreach my $grade ( sort {$a <=> $b} keys %enrollments ) {
    print qq{<tr><td class="cn">$grade</td>};
    my $mdiff = $enrollments{$grade}{'M'} - $currenrol{$grade}{'M'};
    my $fdiff = $enrollments{$grade}{'F'} - $currenrol{$grade}{'F'};
    $mtotal += $enrollments{$grade}{'M'};
    $ftotal += $enrollments{$grade}{'F'};
#    print "Grade:$grade $enrollments{$grade}{F} and F Total:$ftotal<br>\n";
    
    $mtotdiff += $mdiff; # store total changes
    $ftotdiff += $fdiff;
    print qq{<td class="cn">$enrollments{$grade}{M}</td>};
    if ( $mdiff != 0 ) {
	print qq{<td class="bcn">$mdiff</td>};
    } else {
	print qq{<td class="cn">$mdiff</td>};
    }

    print qq{<td class="cn">$enrollments{$grade}{F}</td>};
    if ( $fdiff != 0 ) {
	print qq{<td class="bcn">$fdiff</td>};
    } else {
	print qq{<td class="cn">$fdiff</td>};
    }
    print qq{</tr>\n};
}
print qq{<tr style="background-color:#DDD;"><td class="bcn">Total</td>};
print qq{<td class="bcn">$mtotal</td><td class="bcn">$mtotdiff</td>};
print qq{<td class="bcn">$ftotal</td><td class="bcn">$ftotdiff</td></tr>\n};
print qq{</table>\n};

print qq{<h3 style="margin-bottom:0.3em;">Enrollment Changes</h3>\n};
foreach my $rec (@transfers) {
    print $rec;
}

print qq{</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;

}
