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

#  This file is part of Open Admin for Schools.

# File: rptenrolmon.pl
# Monthly Enrollment report for Ethnic change reports
# 
# Outline: 1) Find current enrollment 
#  2) Count back to month of interest.
#  3) Find number of recorded transfers in that month and categorize into 
#     non-native, metis/status native, etc. on the basis of enrol/withdraw.

my $ignoregrade = 'PK'; # grade to ignore in calculations.
# Comment out if you want all grades counted in this report.

my %lex = ('Monthly Enrollment Change' => 'Monthly Enrollment Change',
	   'Start of month' => 'Start of month',
	   'Change' => 'Change',
	   'End of month' => 'End of month',
	   'Enrollment' => 'Enrollment',
	   'Main' => 'Main',
	   'Category' => 'Category',
	   'Enrollments' => 'Enrollments',
	   'Withdrawals' => 'Withdrawals',
	   'Error' => 'Error',
	   'Date in Month' => 'Date in Month',
	   'Continue' => 'Continue',
	   'Blank=Previous Month' => 'Blank=Previous Month',
	   'Summary' => 'Summary',

	   );


use DBI;
use CGI;

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

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

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


my $title = "$lex{'Monthly Enrollment Change'}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

if ( not $arr{page} ) {
    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{$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();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showReport();

}


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


    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{'Date in Month'}</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> $lex{'Blank=Previous Month'} };
    print qq{</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;

}



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


    # Date Setup
    my ($yr, $mo, $da);
    if ( not $arr{date} ) { # We'll go back 1 month.
	if ( $mon > 1 ){ # if not January (ie. 1)
	    $mo = $mon - 1;
	    $yr = $year;
	} else {  # month is 1, go to prev dec.
	    $mo = 12;
	    $yr = $year - 1;
	}
	$da = "01";
    } else { # We have been passed a date.
	($yr, $mo, $da) = split /-/, $arr{date};
	$da = "01";
    }

    my $agingdate = "$yr-$mo-$da";

    # Find start date of next month.
    my $nextmonth = $mo + 1;
    my $nextyear = $yr;
    if ( $nextmonth > 12 ) {
	$nextmonth = 1;
	$nextyear++;
    }
    my $nextdate = "$nextyear-$nextmonth". '-01';

    # print qq{Next Date: $nextdate<br>\n};
    # print qq{Aging Date: $agingdate<br>\n};

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


    # populate the @ethnic hash...
    my @ethnic;
    my $sth = $dbh->prepare("select distinct ethnic from student 
      where ethnic != '' and ethnic is not NULL order by ethnic");
    $sth->execute;
    while ( my $eth = $sth->fetchrow ) {
	push @ethnic, $eth;
    }

    # Configure var
    my %ethnicount;
    foreach my $type ( @ethnic ){
	$ethnicount{$type} = [0,0];
    }


    my $select;
    if ( $ignoregrade ){ # if defined, skip this grade.
	$select = "where grade != '$ignoregrade'";
    }

    # First count how many currently enrolled (in student table)
    my $sth = $dbh->prepare("select count(*) from student $select");
    $sth->execute;
    my $curenrol = $sth->fetchrow;
    #print qq{Current Enrollment: $curenrol<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('$agingdate') order by date desc");
    # NOTE: Quotes are needed inside the to_days function for aging date!
    $sth1->execute;
    if ($DBI::errstr) { print $DBI::errstr; die; }

    while ( my @arr = $sth1->fetchrow){

	if ( $arr[3] ne 'withdraw' ) { # an enrol
	    $curenrol--;
	} elsif ( $arr[3] eq 'withdraw') {
	    $curenrol++;
	} else {
	    # We have a programming error!
	    print qq{Programming Error! $arr[3] for student $arr[1] on $arr[2]};
	    die;
	}
    }

    $startenrol = $curenrol;
    #print qq{Start Enrol: $startenrol<br>\n};
    
    # $startenrol Should have the enrollment on the first day of the month 
    #  BEFORE any enrollment on that first day. 

    # Now let's find all records in that month, and calc the ethnic results.
    my $sth1 = $dbh->prepare("select * from transfer 
     where to_days(date) >= to_days('$agingdate') and 
     to_days(date) < to_days('$nextdate') order by date desc");
    $sth1->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    $entryrows = $sth1->rows;

    #print qq{Date: $searchdate Entryrows: $entryrows<br>\n};

    my $change = 0;

    # Loop through the month adding up the counts in each ethnic category.
    while ( @arr = $sth1->fetchrow ){
	$sth2 = $dbh->prepare("select * from student where studnum = '$arr[1]'");
	$sth2->execute;
	if ($DBI::errstr) {print $DBI::errstr; die;}
	$studentrows = $sth2->rows;

	if ( $studentrows < 1 ) {  # Student not found in student table; look in other
	    $sth2 = $dbh->prepare("select * from studentwd where studnum = ?");
	    $sth2->execute( $arr[1] );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}
	@student = $sth2->fetchrow;

	# Skip PK or other set grade.
	if ( $student[5] eq $ignoregrade ) { 
	    #print qq{$student[1] $student[2] G:$student[5] D:$arr[2]<br>\n};
	    next;
	} 
  
	foreach $type (@ethnic){  # Go through each ethnic type
	    if ($student[12] eq $type) {  # We have an ethnic type match.
		if ($arr[3] eq 'enrol' or $arr[3] eq 're-enrol') {
		    $ethnicount{$type}->[0]++;  # Zero are enrollment of this type
		    $change++;
		} elsif ($arr[3] eq 'withdraw') {
		    $ethnicount{$type}->[1]++;  # Ones are withdrawals of this type.
		    $change--;
		}
	    }
	}
	
    } # End of Loop to count all transfers in month and evaluate

    $endenrol = $startenrol + $change;

    print qq{<h3>$month[$mo], $year</h3>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Category}</th><th>$lex{Enrollments}\n};
    print qq{</th><th>$lex{Withdrawals}</th></tr>\n};

    # Now print out what was happening in each category...
    foreach my $type ( @ethnic ) {  # Go through each ethnic type
	print qq{<tr><td>$type</td>\n};
	print qq{<td class="cn">$ethnicount{$type}->[0]</td>\n};
	print qq{<td class="cn">$ethnicount{$type}->[1]</td></tr>\n};
    }

    print qq{</table><p></p>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th colspan="2">$lex{Enrollment} $lex{Summary}</th></tr>\n};

    print qq{<tr><td class="bra">$lex{Enrollment} - $lex{'Start of month'}</td><td class="la">$startenrol</td></tr>\n};
    print qq{<tr><td class="bra">$lex{Enrollment} - $lex{Change}</td><td class="la">$change</td></tr>\n};
    print qq{<tr><td class="bra">$lex{Enrollment} - $lex{'End of month'}</td><td class="la">$endenrol</td></tr>\n};

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

}
