#!/usr/bin/perl
#  Copyright 2001-2019 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 = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Sync' => 'Sync',
	   'Continue' => 'Continue',
	   'Student' => 'Student',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Records' => 'Records',
	   'Delete' => 'Delete',
	   'Nominal Roll' => 'Nominal Roll',
	   'Add' => 'Add',
	   'Not Found' => 'Not Found',
	   'Previous' => 'Previous',
	   'Year' => 'Year',
	   'Current' => 'Current',
	   'Check' => 'Check',
	   'Next Page' => 'Next Page',

	   );


use DBI;
use CGI;
use Time::JulianDay;
# use Number::Format qw(:all);


my $self = 'nrsync.pl';
my $cutoff = '-09-30'; # Sept 30; last day to be present for funding.


my @time = localtime(time);
my $year = $time[5] + 1900;
my $month = $time[4] + 1;
my $day = $time[3];

my $realdate = "$year-$month-$day"; # not overridden as below.
my $realdatejd = julian_day( split('-', $realdate));

if ( $month < 10 ) { $month = 10; $day= '01'; } # override to make date Oct 1.
my $currdate = "$year-$month-$day";

$curryear = $year;
$prevyear = $year - 1;
# $year--;
# my $startdate = "$year-09-29";
# my $enddate = "$year-10-01";

my $ppyear = $prevyear - 1; # previous previous year.
my $ppyeardate = $ppyear. '-08-01';


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

eval require "../../lib/libattend.pl";
if ( $@ ) {
    print $lex{Error}. " $self: $@<br>\n";
    die $lex{Error}. "$self: $@\n";
}



my $q = new CGI;
print $q->header; 
my %arr = $q->Vars;

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

# Page Header
my $title = qq{$lex{Sync} $lex{'Nominal Roll'} $lex{Records}};
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>\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};
    syncRecords();
} 



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

    # Form Start 
    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<h3>Synchronize the number of nominal roll records with school enrollments on Sept 30th<br>\n};
    print qq{of the current and previous school years</h3>\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{Previous} $lex{Year}</td>\n};
    print qq{<td class="la">Sept 30, <input type="text" name="prevyear" size="6" value="$prevyear"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Current} $lex{Year}</td>\n};
    print qq{<td class="la">Sept 30, <input type="text" name="curryear" size="6" value="$curryear"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Check} $lex{'Next Page'}</td>};
    print qq{<td class="la"><input type="checkbox" name="checkadd" value="1"> $lex{Add} };
    print qq{<input type="checkbox" name="checkdel" value="1"> $lex{Delete}</td></tr>\n};

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

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

    exit;

} # end of showStartPage



#-------------
sub syncRecords {
#-------------

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

    my %current;
    my $sth = $dbh->prepare("select lastname, firstname, studnum from student");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {
	$current{$studnum} = "$lastname, $firstname";
    }

    
    my ($checkadd, $checkdel);
    if ( $arr{checkadd} ) {
	$checkadd = qq{checked="checked"};
	delete $arr{checkadd};
    }
    if ( $arr{checkdel} ) {
	$checkdel = qq{checked="checked"};
	delete $arr{checkdel};
    }


    my $cyeardate = $arr{curryear}. $cutoff;
    my $pyeardate = $arr{prevyear}. $cutoff;


    my $pyearjd = julian_day( split('-', $pyeardate));
    my $cyearjd = julian_day( split('-', $cyeardate));


    # Get previous year kids to add in to active hash
    my $startjd = $pyearjd - 1;
    my $endjd = $pyearjd + 1;

    my $startdate = join('-', inverse_julian_day( $startjd ));
    my $enddate = join('-', inverse_julian_day( $endjd ));

    #print qq{Start:$startdate<br>\n};
    #print qq{End:$enddate<br>\n};
    

    # list of all students in nomroll or current.
    my %active; # current students + previous year ones
    my %master; # master list to check all.

    %active = %current;  # current students are all active, by definition
    if ( $realdatejd > $cyearjd ) { # We're after Sept 30th, this year, add/remove kids to get Sept 30th kids
	
	my $sth = $dbh->prepare("select * from transfer where date >  '$cyeardate'");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    if ( $r{type} eq 'withdraw' ) { # add them in.
		$active{$r{studnum}} = 1;
	    } else { # remove them
		delete $active{$r{studnum}};
	    }
	}
    }
	

    %current = %active; # Current will only be current, active have both current and prev year.

        
    # Find students enrolled PREVIOUS Sept 30th, checking all transfer records.
    my $sth = $dbh->prepare("select distinct studnum from transfer 
      where studnum != '' and studnum is not NULL and 
      to_days(date) > to_days('$pyeardate') and 
      to_days(date) <= to_days('$cyeardate')");
 
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $studnum = $sth->fetchrow ) {
	if ( not $active{$studnum} ) {
#	    print "New:$studnum<br>\n";
	    $active{$studnum} = 1;
	}
    }

    
    # Get Previous Kids list
    my %previous;
    foreach my $studnum ( keys %active ) {
	if ( not $current{$studnum} ) { # not here anymore
	    $previous{$studnum} = 1;
	}
    }
	
    my $currcount = keys %current;
    my $prevcount = keys %previous;

    print qq{<h3>Current Students $currcount, Previous Students (no longer here) $prevcount</h3>\n};

    %master = %active;  # start populating master.
    

    
    # Load nominal roll student records
    my %nomroll;
    my $sth = $dbh->prepare("select studnum from student_inac where studnum != 0 and studnum is not NULL");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $studnum = $sth->fetchrow ) {
	$nomroll{$studnum} = 1;
	$master{$studnum} = 1;  # also put into master, so we have ANY student including 2 years ago;

    }


    # Loop through master and delete any current students who already have a nominal roll record.
    foreach my $studnum ( keys %master ) {
	if ( $active{$studnum} and $nomroll{$studnum} ) { # in both lists
	    delete $active{$studnum};
	    delete $nomroll{$studnum};
	}
    }

    # Students left in %active need to have NR records added.
    # Students left in %nomroll need to be deleted from there (since no longer active)

    my $sth = $dbh->prepare("select lastname, firstname, birthdate, provnum, treaty from studentall 
      where studnum = ?");

    my $sth1 = $dbh->prepare("select * from transfer where studnum = ? order by date desc");


    # Records to DELETE
    if ( %nomroll ) {

	print qq{<form action="nrdeled.pl" method="post"> \n};
	print qq{<input type="hidden" name="page" value="2">\n};
	print qq{<input type="hidden" name="type" value="$lex{Delete}">\n};

	print qq{<table cellpadding="3" cellspacing="0" border="0" };
	print qq{style="padding:0em;margin:1em;float:left;">\n};
	print qq{<caption style="font-size:120%;font-weight:bold;">};
	print qq{$lex{Delete} $lex{'Nominal Roll'} $lex{Records}</caption>\n};
	print qq{<tr><th>$lex{Student} (local - province - treaty)</th><th>$lex{Delete}</th></tr>\n};
	print qq{<tr><td colspan="2" class="cn"><input type="submit" value="$lex{Delete}"></td></tr>\n};

	
	my %sorted;
	foreach my $studnum ( sort keys %nomroll ) {

	    # find ident info, first studentall, then transfer
	    $sth->execute( $studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $studref = $sth->fetchrow_hashref;
	    
	    # Get transfer info for nameinfo
	    if ( not $studref ) {
		$sth1->execute( $studnum);
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $tref = $sth1->fetchrow_hashref;
		if ( $tref ) {
		    $studref->{lastname} = $tref{lastname}; 
		    $studref->{firstname} = $tref{firstname};
		}
		
	    }
	    
	    if ( not $studref ) {
		$studref->{lastname} = qq{<span style="color:red;">$lex{'Not Found'}</span>};
		$studref->{firstname} = '';
		$found = 0;
	    }

	    $studref->{studnum} = $studnum;
	    my $index = qq{$studref->{lastname}$studref->{firstname}$studnum};
	    $sorted{$index} = $studref;

	}

	my $count = 1;
	foreach my $key ( sort keys %sorted ) {

	    my $ref = $sorted{$key};
	    my %sr = %$ref;
	    my $studnum = $sr{studnum};

	    print qq{<tr><td class="la">$count. <b>$sr{lastname},</b> $sr{firstname} ($sr{studnum} };
	    print qq{- $sr{provnum} - $sr{treaty})</td>\n};
	    print qq{<td class="cn"><input type="checkbox" name="$sr{studnum}" value="1" $checkdel></td></tr>\n};

	    # Enrollment Records
	    $sth1->execute( $studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $tcount;
	    print qq{<tr><td class="la" colspan="2">\n};
	    while ( my $tref = $sth1->fetchrow_hashref ) {
		my %t = %$tref;
		print qq{<i>$t{type}</i> - $t{date}<br>\n};
		$tcount++;
		if ( $tcount > 2 ) {last; }
	    }
	    print qq{</td></tr>\n};
	    
	    
	    $count++;
	}

	print qq{<tr><td colspan="2" class="cn"><input type="submit" value="$lex{Delete}"></td></tr>\n};
	print qq{</table></form><p></p>\n};


    } else { # no students to delete.
	print qq{<div style="margin:2em 0.2em;">};
	print qq{<span style="border:1px solid black;padding:0.4em;font-size:130%;font-weight:bold;">};
	print qq{No $lex{'Nominal Roll'} $lex{Records} To Delete</span></div>\n};
    }


    # print qq{ACTIVE<br>\n};
    # foreach my $key ( sort keys %active ) { print qq{K:$key V:$active{$key}<br>\n}; }
	
    # Records to ADD 
    if ( %active ) {

	print qq{<form action="nradd.pl" method="post"> \n};
	print qq{<input type="hidden" name="page" value="2">\n};

	print qq{<table cellpadding="3" cellspacing="0" border="1"> };
#	print qq{style="">\n};
	
	print qq{<caption style="font-size:120%;font-weight:bold;">};
	print qq{$lex{Add} $lex{'Nominal Roll'} $lex{Records}</caption>\n};
	
	print qq{<tr><th>$lex{Student}</th><th>Prov#</th><th>IRS#</th></tr>\n};
	print qq{<tr><td colspan="3" class="la"><input type="submit" value="$lex{Add}"></td></tr>\n};


	my %sorted;
	foreach my $studnum ( sort keys %active ) {

	    my $found = 1;

	    # find ident info, first studentall, then transfer
	    $sth->execute( $studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $studref = $sth->fetchrow_hashref;

	    if ( not $studref ) { # try transfer table.
		$sth1->execute( $studnum);
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		$studref = $sth1->fetchrow_hashref;
	    }

	    if ( not $studref ) { # Not Found
		next;
	    }

	    $studref->{studnum} = $studnum;
	    my $index = "$studref->{lastname}$studref->{firstname}$studnum";
	    $sorted{$index} = $studref;
	}

	my $count = 1;
	foreach my $key ( sort keys %sorted ) {

	    my $ref = $sorted{$key};
	    my %sr = %$ref;
	    my $studnum = $sr{studnum};
	    
	    print qq{<tr><td class="la">$count. <input type="checkbox" name="$sr{studnum}" value="1" $checkadd> };
	    print qq{<b>$sr{lastname},</b> $sr{firstname} ($sr{studnum})</td>};
	    print qq{<td>$sr{provnum}</td><td>$sr{treaty}</td></tr>\n};

	    # Enrollment Records
	    $sth1->execute( $studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $tcount;
	    print qq{<tr><td class="la" colspan="3">\n};
	    while ( my $tref = $sth1->fetchrow_hashref ) {
		my %t = %$tref;
		print qq{<i>$t{type}</i> - $t{date}<br>\n};
		$tcount++;
		if ( $tcount > 2 ) {last; }
	    }
	    print qq{</td></tr>\n};
	    
	    $count++;
	}

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


    } else { # no students to add;
	print qq{<div style="margin:2em 0.2em;">};
	print qq{<span style="border:1px solid black;padding:0.4em;font-size:130%;font-weight:bold;">};
	print qq{No $lex{'Nominal Roll'} $lex{Records} To Add</span></div>\n};
    }

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

    exit;

}



#---------------
sub findStudents {
#---------------

    my $currcutoff = shift; # current cutoff for this year. (yyyy-mm-dd)
    my $cutoffjd = julian_day( split('-', $currcutoff));
    
#    print qq{PrevPrevYear:$ppyeardate Curr:$currdate<br>\n};

    my @students;

    # Search All Students
    my $sth = $dbh->prepare("select distinct studnum from transfer 
      where studnum != '' and studnum is not NULL");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }


    # Current students in student table?
    my $sth1 = $dbh->prepare("select count(*) from student where studnum = ?");
    
    # Any students in studentall table?
    my $sth2 = $dbh->prepare("select count(*) from studentall where studnum = ?");

    
    my $count;
    while ( my $studnum = $sth->fetchrow ) {
	my @blocks = findEnrollmentBlocks( $studnum, $ppyeardate, $currdate, $dbh );
	foreach my $ref ( @blocks ) {
	    my $startjd = julian_day( split('-', $ref->{start} ));
	    my $endjd = julian_day( split('-', $ref->{end} ));
#	    print qq{$studnum - S:$startjd E:$endjd<br>\n};
	    
	    if ( $startjd <= $cutoffjd and $endjd >= $cutoffjd ) { # enrollment block has date within

		# Are they current?
		$sth1->execute($studnum);
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $currcount = $sth1->fetchrow;


		$sth2->execute($studnum);
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $anycount = $sth2->fetchrow;
	

		if ( not $currcount ) { # not in student table;
		    print qq{<h3 style="color:red;">Missing in Current:$studnum - $currcount :ALL-$anycount</h3>\n};
		    next;
		}
		
		push @students, $studnum;
		$count++;
#		print qq{$count. $studnum - $ref->{start}:$ref->{end}<br>\n};

	    }
	}
    }

    return \@students;

}
