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

#  This file is part of Open Admin for Schools.
#  based on IEP system file  'attendcheck_local.pl';

my $self = 'past_attscan2.pl';

my %lex = ('Attendance' => 'Attendance',
	   'Error' => 'Error',
	   'Main' => 'Main',
	   'Continue' => 'Continue',
	   'Cutoff' => 'Cutoff',
	   'Entry Value' => 'Entry Value',
	   'Percent' => 'Percent',
    );


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

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


# Get passed vars; comment this out below.
my $q = new CGI;
my %arr = $q->Vars;
print $q->header( -charset, $charset );


# Page Header
my $title = qq{Attendance Scan 2};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

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="margin:1em 3em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="/ssp.html">SSP</a> ] $currdate\n};
print qq{<h1>$title</h1>\n};


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

my ($db, $dsn, $dbh );
if ( not $arr{remote} ) { # get the remote database of choice.

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

    # Select Previous Databases for this school
    while ( my $db = $sth->fetchrow ) {
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

	if ( $db =~ m/$dbase/ ) {
	    $remotedb{$db} = 1;
	}
    }

    # Start the form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="remote" 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="bla" colspan="2">Select School Year</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">};
	print qq{<input type="radio" name="database" value="$db"> $year ($db)</td></tr>\n};
	
    }


    # Continue
    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;

} else { # we have $arr{remote}

    my $dbase = $arr{database}; # passed from function above. 
   
    $dsn = "DBI:mysql:database=$dbase;host=$remotehost";
    $dbh = DBI->connect($dsn,$remoteuser,$remotepassword);
    
    # Get the values from configuration system of this year.
    my $sth = $dbh->prepare("select datavalue from conf_system where filename = 'admin'");
    $sth->execute;
    my $failflag = 1;
    while ( my $datavalue = $sth->fetchrow ) {
#	print "DV:$datavalue<br>\n";
	if ( $datavalue =~ m/schoolstart/ or $datavalue =~ m/schoolend/ ) {
	    $failflag = 0;
	}
	eval $datavalue;
	if ( $@ ) {
	    print $lex{Error}. " $@<br>\n";
	    die $lex{Error}. " $@\n";
	}
    }
    if ( $failflag ) { # school year not defined.
	print qq{<h3>School Year not defined</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    
}


# NOTE: We have 2 additional hash values passed in %arr: database and remote;
# Any changes to attscan2.pl are noted below with a double tilde (~~)


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


# Absent/Late strings for libattend library.
my %lexi = ('Absent' => 'Absent',
	    'Late' => 'Late'
    );


my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
if (length($month) == 1){ $month = "0".$month;}
if (length($day) == 1){ $day = "0".$day;}
my $currdate = "$year-$month-$day";


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

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


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

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    # find the students that are below percentage attendance passed

    print qq{<h3>$schoolstart - $schoolend</h3>\n};

    
    # Now get the students in this school
    my %sortname;
    my %studs; # studs{dbase}{studnum} = record;
    my $sth = $dbh->prepare("select * from student");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	$sortname{"$r{lastname}$r{firstname}$r{studnum}"} = $r{studnum};
	
	# my $studnum = $r{studnum};
	$studs{$r{studnum}} = $ref;
    }

    # percentage attendance value
    my $attend = $arr{attend};
    $attend =~ s/\%//; # strip percent

    if ( not $attend or $attend < 1 or $attend > 101 ) { # fail
	print qq{<h3>$lex{Error} $lex{'Entry Value'}: $attend</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    my $attendlimit = format_number( $attend / 100, 3, 3);
#    print "Limit:$attendlimit<br>\n";

    my $templimit = $attendlimit * 100;
    print qq{<h3 style="margin:1em;">$lex{Attendance} $lex{Cutoff} $templimit%</h3>\n};


    print qq{<div style="margin-left:1em;font-weight:bold;">};
    print qq{Green Level >= $arr{levelgreen}%, Yellow Level >= $arr{levelyellow}%, };
    print qq{Red Level < $arr{levelyellow}%</div>\n}; 


    
    # Get start and end dates
    my $startdate = $schoolstart;
    my $enddate = $schoolend; # NOT current date

    my $termstart = $g_MTrackTerm{1}{1}{start};
    # print qq{Term Start: $termstart<br>\n};
    $startdate = $termstart;

    
    my %schooldays = mkSchoolDays($startdate, $enddate, $dbh);
    # These are the school days open in each month, holidays excluded from the count.
    
    # Loop through students in this school and get their attendance.
    my %sort;
    foreach my $studnum ( keys %studs ) {
	my %r = %{ $studs{$studnum} };
	$sort{"$r{lastname}$r{firstname}$studnum"} = $studnum;
    }

    my $first = 0;
    my %data;
    my %studavg;

    my %gradeavg; # gradeavg{grade}{enrol => present =>}
    my %gradeoverlimit;

   
    # Student Loop
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};

	my %r = %{ $studs{$studnum} };
	my $grade = $r{grade};

#	print qq{<div><b>$r{lastname}</b>, $r{firstname}</div>\n};
	
	my $ppd = $g_ppd{ $r{grade} };
	if ( not $ppd ) { 
	    print qq{<tr><td colspan="15">};
	    print qq{Missing Attendance Periods Per Day - SN:$studnum $r{firstname} $r{lastname}};
	    print qq{GR:$r{grade} DB:$db - SETTING PPD to 2</td></tr>\n}; 
	    $ppd = 2;
	}


	# Calculate the days enrolled for this student
	my $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh);
	my %enrol = %$ref;  # enrol{year-month} = val
	if ( not $ref ) { #student not enrolled.
	    my $skip = scalar keys %schooldays;
#	    print qq{<td colspan="$skip" style="background-color:salmon;font-weight:bold;">};
#	    print qq{Enrollment Error: No Enrollment this school year</td></tr>\n};
	    next; 
	}


	my ($currmonth,$prevmonth);
	my ($totalenrol, $totalpresent);
	
	# Loop through months
	foreach my $yrmo ( sort keys %schooldays ) {

	    my $abslate = calcMonthlyAttendance( $studnum, $yrmo, $ppd, '', \%lexi, $dbh);
	    my ($absent,$late ) = split(':', $abslate);
#	    print "ABS:$absent ";
	    
	    my $pctAttendance;
	    if ( $enrol{$yrmo}{days} ) {
		my $present = $enrol{$yrmo}{days} - $absent;
#		print "YRMO:$yrmo ENROL:$enrol{$yrmo}{days} ABS:$absent PRES:$present<br>\n";
		if ( $present < 0 ) { $present = 0; } 
		
		$totalpresent += $present;
		$totalenrol += $enrol{$yrmo}{days};

		$gradeavg{$grade}{enrolled} += $enrol{$yrmo}{days};
		$gradeavg{$grade}{present} += $present;

		$data{$studnum}{$yrmo} = "$present:$enrol{$yrmo}{days}";
		
		
		$pctAttendance = round($present * 100 / $enrol{$yrmo}{days}, 1);
	    } else { # no enrollment....
#		print qq{<td>No Enrol</td>};
		next; # month
	    }

	    $prevmonth = $currmonth;
	    $currmonth = $pctAttendance;

	    
	    my $color;
	    if ( $pctAttendance >= $arr{levelgreen} ) { 
		$color = 'lightgreen';
	    } elsif ( $pctAttendance >= $arr{levelyellow} ) {
		$color = 'yellow';
	    } else {
		$color = 'red';
	    }

#	    print qq{<td style="background-color:$color;">$pctAttendance</td>\n};

	} # end of year-month loop

	my $pctTotal;
	if ( $totalenrol ) {
	    $pctTotal = format_number($totalpresent  / $totalenrol, 3,3);
	} else {
	    $pctTotal = 0;
	}

	$studavg{$studnum} = $pctTotal;

	my $avgcolor;
	if ( $pctTotal >= $arr{levelgreen} ) { 
	    $avgcolor = 'lightgreen';
	} elsif ( $pctTotal >= $arr{levelyellow} ) {
	    $avgcolor = 'yellow';
	} else {
	    $avgcolor = 'red';
	}
	    
#	print qq{<td style="background-color:$avgcolor;font-weight:bold;">};
#	print qq{$pctTotal%  ($totalpresent / $totalenrol)</td></tr>\n};

    } # end of student loop


    # at this point we have %studs{studnum} = $ref to record,
    # %data{studnum}{$yrmo} = present:enrolled , and 
    # %studavg{studnum} = avg;
    
    
    # Printing Student  Loop
    my (%sort, @sort);
    if ( $arr{namesort} ) {
	foreach my $key ( sort keys %sortname ) {
	    my $studnum = $sortname{$key};
#	    print qq{<div>KEY:$key SN:$studnum</div>\n};
	    push @sort, $studnum;
	}
	
    } else { # average sort
	foreach my $studnum ( keys %studavg ) {
	    my $avg = $studavg{$studnum};
	    $sort{"$avg$studnum"} = $studnum;
	}
	foreach my $key ( sort {$a <=> $b} keys %sort ) {
	    my $studnum = $sort{$key};
	    push @sort, $studnum;
	}
    }

    

    #-------- Printing Loop------------------
    my $first = 1;
    
    foreach my $studnum ( @sort ) {

	my $avg = $studavg{$studnum};

	my %r = %{ $studs{$studnum} };
	my $grade = $r{grade};

	if ( $avg > $attendlimit ) {
	    $gradeoverlimit{$grade}++;  # number of students in each grade over this % level
	    next; 
	} # skip to next student

        
	if ( $first ) { # print heading.
	    print qq{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="float:left;margin:1em;">\n};
	    print qq{<tr><th>Name</th><th>Grade</th>};
	    foreach my $yrmo ( sort keys %schooldays ) {
		print qq{<th>$yrmo</th>};
	    }
	    print qq{<th>Average</th></tr>\n};

	    # Days Open
	    print qq{<tr><td colspan="2" class="bra">Days Open</td>\n};
	    foreach my $yrmo ( sort keys %schooldays ) {
		print qq{<td>$schooldays{$yrmo}</td>};
	    }
	    print qq{<td></td></tr>\n};
	    
	    $first = 0;
	}

	print qq{<tr><td><b>$r{lastname}</b>, $r{firstname}</td><td class="cn">$grade</td>\n};

	
	my ($totalenrol, $totalpresent);
	
	# Loop through months
	foreach my $yrmo ( sort keys %schooldays ) {

#	    my $abslate = calcMonthlyAttendance( $studnum, $yrmo, $ppd, '', \%lexi, $dbh);
#	    my ($absent,$late ) = split(':', $abslate);
#	    print "ABS:$absent ";

	    my ($present,$enrolled) = split(':', $data{$studnum}{$yrmo} );
	    
	    my $pctAttendance;
	    if ( $enrolled ) {
		$pctAttendance = round($present * 100 / $enrolled, 1);
	    } else { # no enrollment....
		print qq{<td>No Enrol</td>};
		next; # month
	    }

	    $totalpresent += $present;
	    $totalenrol += $enrolled;


	    my $color;
	    if ( $pctAttendance >= $arr{levelgreen} ) { 
		$color = 'lightgreen';
	    } elsif ( $pctAttendance >= $arr{levelyellow} ) {
		$color = 'yellow';
	    } else {
		$color = 'red';
	    }

	    print qq{<td style="background-color:$color;">$pctAttendance%</td>\n};

	} # end of year-month loop


	if ( $totalenrol ) {
	    $pctTotal = round($totalpresent * 100 / $totalenrol, 1);
	} else {
	    $pctTotal = '0.0';
	}

	my $avgcolor;
	if ( $pctTotal >= $arr{levelgreen} ) { 
	    $avgcolor = 'lightgreen';
	} elsif ( $pctTotal >= $arr{levelyellow} ) {
	    $avgcolor = 'yellow';
	} else {
	    $avgcolor = 'red';
	}
	    
	print qq{<td style="background-color:$avgcolor;font-weight:bold;">};
	print qq{$pctTotal%  ($totalpresent / $totalenrol)</td></tr>\n};

    } # end of student loop

    
    if ( $first ) {
	print qq{<h4>No Students Found</h4>\n};
    } else {
	print qq{</table><p></p>\n};
    }


    # Grade Averages
    my $sth = $dbh->prepare("select count(*) from student where grade = ?");
    
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="float:left;margin:1em;">\n};
    print qq{<tr><th>Grade</th><th>Attendance</th><th>Over Limit</th></tr>\n};

    my ($totpresent,$totenrol);
    foreach my $grade ( sort {$a <=> $b} keys %gradeavg ) {

	$sth->execute($grade);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $ecount = $sth->fetchrow;
	
	my $avg;
	if ( $gradeavg{$grade}{enrolled} ) {
	    $avg = round($gradeavg{$grade}{present} * 100 / $gradeavg{$grade}{enrolled}, 1);
	    $totpresent += $gradeavg{$grade}{present};
	    $totenrol += $gradeavg{$grade}{enrolled};
	}
	if ( not $gradeoverlimit{$grade} ) {  $gradeoverlimit{$grade} = '0'; }
	print qq{<tr><td>$grade</td><td>$avg%</td><td>$gradeoverlimit{$grade} / $ecount</tr>\n};
    }

    my $totalavg = round($totpresent * 100 / $totenrol, 1);
    print qq{<tr><td colspan="3" style="background-color:#DDD;">Summary Average $totalavg%</td></tr>\n};
    
    print qq{</table>\n};

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

    exit;

} # end of showReport



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


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

    # ~~  Add these 2 lines.
    print qq{<input type="hidden" name="remote" value="1">\n};
    print qq{<input type="hidden" name="database" value="$arr{database}">\n};

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

    # Percent Attendance
    print qq{<tr><td class="bla">$lex{Percent} $lex{Attendance} $lex{Cutoff} (1-101)};
    print qq{ <input type="text" size="4" name="attend"></td></tr>\n};
    print qq{<tr><td colspan="2" class="cn">};
    print qq{Set to 101 in order to see all student attendance</td></tr>\n};

    print qq{<tr><td> </td></tr>\n};
    

    print qq{<tr><td class="bla" style="vertical-align:top;" colspan="2">};
    print qq{Attendance Color Levels</td></tr>};
    
    print qq{<tr><td class="la">};
    print qq{Green &gt;= <input type="text" size="4" name="levelgreen" value="95"><br>\n};
    print qq{Yellow &gt;= <input type="text" size="4" name="levelyellow" value="90"><br>\n};
    print qq{Red is any percent attendance below this value</td></tr>\n};

    # Sort by Name
    print qq{<tr><td class="bla" colspan="2">Sort by Name?};
    print qq{ <input type="checkbox" name="namesort" value="1" checked></td></tr>\n};
    
    
    print qq{<tr><td colspan="2" class="cn"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    
    print qq{</table></form>\n};
    print qq{</body></html>\n};

    exit;
}

