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

#  This file is part of Open Admin for Schools.
#  Based on the central site rptatt3global.pl

my $percentsymbol = '%';

my %lex = ('Attendance Report' => 'Attendance Report',
	   'Main' => 'Main',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'Grade' => 'Grade',
	   'Student' => 'Student',
	   'HTML' => 'HTML',
	   'Report Type' => 'Report Type',
	   'Missing' => 'Missing',
	   'Days Open' => 'Days Open',
	   'Total' => 'Total',
	   'Month' => 'Month',
	   'Average' => 'Average',
	   'Division' => 'Division',
	   'Periods per Day' => 'Periods per Day',
	   'Select' => 'Select',
	   'CSV' => 'CSV',
	   'Show' => 'Show',
	   'Gender' => 'Gender',
	   'Raw' => 'Raw',
	   'Results' => 'Results',
	   'Percentage' => 'Percentage',
	   'Database' => 'Database',
	   'Not Found' => 'Not Found',
	   'Remote' => 'Remote',
	   'Attendance' => 'Attendance',
	   
	   );


my $self = 'rptAttStat3.pl';

my $oabase = '/opt/openadmin';


use DBI;
use CGI;
use Number::Format qw(:all);

# Constants
# Grade Divisions: map Grade => Division.
my %grDiv = ('K' => '1', '1' => '1', '2' => '1', '3' => '1', '4' => '2', '5' => '2',
'6' => '2', '7' => '3', '8' => '3', '9' => '3', '10' => '4', '11' => '4', '12' => '4');

my %skipgrades = ('K' => '1', 'PK' => '1'); # grades to skip over.



my $defmaxrecords = 28; # Maximum records per PDF page.
my $group = 'grade';
my $pdf = '1';
my $html;

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

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

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

# @dbase = sort keys %alldbase; # global records.


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



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 $currsdate = "$year-$month-$day";
my $currdate = "$month[$month] $day, $year";


# HTML Header
my $title = qq{Statistical $lex{'Attendance Report'} - Previous Years};
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">\n};

print qq{$chartype\n</head><body style="margin:1em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$attpage">$lex{Attendance}</a> ]\n};

print qq{<h1>$title</h1>\n};
#if ( $schoolname ) { print qq{ &ndash; $schoolname}; }
#print qq{</h1>\n};

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

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

    if ( $arr{reporttype} eq 'html' ) {
	mkHTMLReport();
    } elsif ( $arr{reporttype} eq 'csv' ) {
	mkCSVReport();
    }

}





#---------------
sub mkHTMLReport {
#---------------

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


    if ( $arr{showk} ) {
	undef %skipgrades;
	delete $arr{showk};
    }
   

    # Load Remote database
    if ( not $arr{db} ) {
	print qq{<h3>$lex{Database} $lex{'Not Found'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    my $db = $arr{db};
    my $dsn = "DBI:mysql:database=$db;host=$remotehost";
    my $dbh = DBI->connect($dsn,$remoteuser,$remotepassword);


    # Load Configuration Variables;
    my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while (	my ($id, $datavalue) = $sth->fetchrow ) {
	# print qq{ID:$id Val:$datavalue<br>\n};
	eval $datavalue;
	if ( $@ ) {
	    print qq{$lex{Error}: $@<br>\n};
	    die "$lex{Error}: $@\n";
	}
    }

    print qq{<h3>School Start:$schoolstart | \n};
    print qq{School End:$schoolend</h3>\n};


    my $enddate = $schoolend;
    my $startdate = $schoolstart;


    # Functions from LibAttend
    %schooldays = mkSchoolDays( $startdate, $enddate, $dbh );
    my @schoolmonths = sort keys %schooldays;
    #~~ print qq{School Months", @schoolmonths, "<br>\n};
    
    # returns hash of schooldays in month. key is yyyy-mm and value is schooldays in month
    #~~ print qq{School Days", %schooldays, "<br>\n};

=head1
if ( $pdf ) { # Open TEX file, and print Header
    $logfile = "pdflog$$.txt";
    $shortname = "inac$$";
    $filename = "$shortname.tex";
    open(TEX,">$filename") || die "Can't open tex file";
    print_tex_doc_start(); # Setup the start of the file.
}
=cut


    # Find the Students, first get current students
    my %students;
    my $sth = $dbh->prepare("select studid, studnum from student");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($id, $studnum) = $sth->fetchrow ) {
	$students{$studnum} = 1;
    }



    # select to see if they have any student record.
    my $sth1 = $dbh->prepare("select count(*) from studentall where studnum = ?");
    # check for attendance records
    my $sth2 = $dbh->prepare("select count(*) from attend where studentid = ?");


    # now find any withdrawn ones, that were here this year.
    my $sth = $dbh->prepare("select id, studnum from transfer 
     where type = 'withdraw' and to_days( date ) >= to_days( '$startdate' ) and
     to_days( date ) <= to_days( '$enddate' )");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($id, $studnum) = $sth->fetchrow ) {

	# Check to make sure they have a student record
	$sth1->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

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

	if ( not $count and $attcount ) { # have attendance recs but no student rec
	    print qq{$lex{Error}: Attendance records exist for missing student: $studnum<br>\n};
	}

	if ( $count ) {
	    $students{$studnum} = 1;
	}
    }


    my %studgrade = ();
    my %gender = ();
    $sth = $dbh->prepare("select grade, sex from studentall where studnum = ?");
    foreach my $studnum ( keys %students ) {

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

	if ( $skipgrades{$grade} ) { next; } # next student

	if ( not $grade ) { 
	    print qq{$lex{Missing} $lex{Grade}. $lex{Student}: $studnum<br>\n};
	    #delete $students{$studnum}; # No Grade = Remove.
	} else { # put in the grade value
	    if ( not $studgrade{$grade} ) { $studgrade{$grade} = []; }
	    push @{ $studgrade{$grade} }, $studnum;
	    $gender{$studnum} = $gender;
	}
    }

# use Data::Dumper;
# print Dumper %studgrade;


    my %lexi = ('Absent' => 'Absent',
		'Late' => 'Late');

    # Now Loop through all grades
    my %attend = ();
    my %genderatt = ();
    my %divatt = (); # Division Attendance
    my $failflag = (); # fail before printing.

    foreach my $grade ( sort {$a <=> $b} keys %studgrade ) {


	my $ppd = $g_ppd{ $grade };
	if ( not $ppd ) {
	    print qq{$lex{Missing} $lex{'Periods per Day'} - $lex{Grade}:$grade:<br>\n}; 
	    $failflag = 1; # fail before output.
	}
	my $division = $grDiv{$grade}; # The Grade Division 1-4


	# Students in this grade
	foreach my $studnum ( @{ $studgrade{$grade} } ) {
    
	    my $sex = lc( $gender{$studnum});

	    my $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh );
	    my %enrolYM = %$ref;
	    # format:  $enrolYM{yearmonth}-> start, end, days.

	    foreach my $ym ( sort keys %enrolYM ) {

		my $result = calcMonthlyAttendance( $studnum, $ym, $ppd, '',\%lexi, $dbh ); # no end date req'd.
		my ($absent, $late) = split(/:/, $result);

		my $enrolled = $enrolYM{$ym}->{days};
		my $present = $enrolled - $absent;

		$attend{$grade}{$ym}->{attend} += $present;
		$attend{$grade}{$ym}->{enrol} += $enrolled;

		$genderatt{$grade}{$ym}{$sex}->{attend} += $present;
		$genderatt{$grade}{$ym}{$sex}->{enrol} += $enrolled;

		$divatt{$division}{$ym}->{attend} += $present;
		$divatt{$division}{$ym}->{enrol} += $enrolled;

	    }
	}
    }

#use Data::Dumper;
#print Dumper %attend;
#print qq{<p></p>\n};

    if ( $failflag ) {
	print qq{</body></html>\n};
	exit;
    }


    # Now print it out!
    # First Row: Days Open, Then each grade for each month; Year:Month along the top.
    print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
    print qq{<tr><th></th>};
    for my $idx ( 1 .. $#schoolmonths ) {
	print qq{<th>$schoolmonths[$idx]</th>};
    }
    print qq{<th>$lex{Total}</th></tr>\n};


    # Days Open
    print qq{<tr style="font-weight:bold;"><td>$lex{'Days Open'}</td>};
    my $totaldays;
    for my $idx ( 1 .. $#schoolmonths ) {
	my $ym = $schoolmonths[$idx];
	print qq{<td>$schooldays{$ym}</td>};
	$totaldays += $schooldays{$ym};
    }
    print qq{<td>$totaldays</td></tr>\n};


    # Grades
    my (%monthAtt, %monthEn);
    my $highestgrade; # track largest grade value for division printing
    foreach my $grade ( sort {$a <=> $b} keys %studgrade ) {

	$highestgrade = $grade;
	if ( $grade == 4 or $grade == 7 or $grade == 10 ) { # print division line
	    my $div = $grDiv{ $grade - 1 };
	    if ( %{$divatt{$div}} and $arr{showdivision} ) { # we have values to print
		
		my ( $divAttend, $divEnrolled );
		print qq{<tr style="background-color:#CCC;"><td><b>$lex{Division} $div</b></td>};
		for my $idx ( 1 .. $#schoolmonths ) {
		    my $ym = $schoolmonths[$idx];
		    
		    my $attend =  $divatt{$div}{$ym}->{attend};
		    my $enrolled = $divatt{$div}{$ym}->{enrol};

		    # for totals at far right of row
		    $divAttend += $attend;
		    $divEnrolled += $enrolled;

		    my $average;
		    if ( $enrolled ) {
			$average = format_number( $attend / $enrolled * 100, 1,1); 
		    }

		    print qq{<td>};
		    if ( $arr{showraw} ) { 
			print qq{$attend / $enrolled };
		    }
		    if ( $arr{showpercent} ) {
			print qq{ $average$percentsymbol };
		    }
		    print qq{</td>};

		}

		# Division totals
		my $divAverage;
		if ( $divEnrolled ) { 
		    $divAverage = format_number( $divAttend / $divEnrolled * 100, 1,1); 
		}
		print qq{<td>};
		if ( $arr{showraw} ) {
		    print qq{$divAttend / $divEnrolled };
		}
		if ( $arr{showpercent} ) {
		    print qq{ $divAverage$percentsymbol };
		}
		print qq{</td></tr>\n};

	    }
	} # end of Division printing Row


	my ($totalAttend, $totalEnrolled);
	print qq{<tr><td>$lex{Grade} $grade</td>};
	for my $idx ( 1 .. $#schoolmonths ) {
	    my $ym = $schoolmonths[$idx];
	    
	    my $attend =  $attend{$grade}{$ym}->{attend};
	    my $enrolled = $attend{$grade}{$ym}->{enrol};

	    my $boyattend =  $genderatt{$grade}{$ym}{'m'}->{attend};
	    my $boyenrolled = $genderatt{$grade}{$ym}{'m'}->{enrol};

	    my $girlattend =  $genderatt{$grade}{$ym}{'f'}->{attend};
	    my $girlenrolled = $genderatt{$grade}{$ym}{'f'}->{enrol};


	    $totalAttend += $attend;
	    $totalEnrolled += $enrolled;

	    $monthAtt{$ym} += $attend;
	    $monthEn{$ym} += $enrolled;

	    my ($boyaverage, $girlaverage);
	    if ( $boyenrolled ) { 
		$boyaverage = format_number( $boyattend / $boyenrolled * 100, 1,1); 
	    }
	    if ( $girlenrolled ) { 
		$girlaverage = format_number( $girlattend / $girlenrolled * 100, 1,1); 
	    }

	    my $average;
	    if ( $enrolled ) {
		$average = format_number( $attend / $enrolled * 100, 1,1); 
	    }


	    print '<td>';
	    if ( $arr{showraw} ) {
		print qq{$attend / $enrolled};
	    } 
	    if ( $arr{showpercent} ) {
		print qq{ $average$percentsymbol };
	    }
	    if ( $arr{showgender} ) {
		print qq{<br>B:$boyaverage$percentsymbol G:$girlaverage$percentsymbol};
	    }
	    print '</td>';


	}

	# Grade totals
	my $gradeAverage;
	if ( $totalEnrolled ) { 
	    $gradeAverage = format_number( $totalAttend / $totalEnrolled * 100, 1,1); 
	}

	print qq{<td>};
	if ( $arr{showraw} ) {
	    print qq{$totalAttend / $totalEnrolled };
	}
	if ( $arr{showpercent} ) {
	    print qq{ $gradeAverage$percentsymbol};
	}
	print qq{</td></tr>\n};

    }


    # Division Row 
    my $div = $grDiv{ $highestgrade };
    if ( %{$divatt{$div}} and $arr{showdivision} ) { # we have values to print

	my ( $divAttend, $divEnrolled );
	print qq{<tr style="background-color:#CCC;"><td><b>$lex{Division} $div</b></td>};
	for my $idx ( 1 .. $#schoolmonths ) {
	    my $ym = $schoolmonths[$idx];
	    
	    my $attend =  $divatt{$div}{$ym}->{attend};
	    my $enrolled = $divatt{$div}{$ym}->{enrol};
	    
	    # for totals at far right of row
	    $divAttend += $attend;
	    $divEnrolled += $enrolled;

	    my $average;
	    if ( $enrolled ) {
		$average = format_number( $attend / $enrolled * 100, 1,1); 
	    }

	    print qq{<td>};
	    if ( $arr{showraw} ) {
		print qq{$attend / $enrolled };
	    }
	    if ( $arr{showpercent} ) {
		print qq{ $average$percentsymbol };
	    }
	    print qq{</td>};

	}

	# Division totals
	my $divAverage;
	if ( $divEnrolled ) { 
	    $divAverage = format_number( $divAttend / $divEnrolled * 100, 1,1); 
	}

	print qq{<td>};
	if ( $arr{showraw} ) { 
	    print qq{$divAttend / $divEnrolled };
	} 
	if ( $arr{showpercent} ) {
	    print qq{ $divAverage$percentsymbol };
	}
	print qq{</td></tr>\n};


    } # end of Division row



    # Monthly Stats:
    print qq{<tr style="background-color:#DDD;"><td>$lex{Month} $lex{Average}</td>};
    for my $idx ( 1 .. $#schoolmonths ) {
	my $ym = $schoolmonths[$idx];

	if ( $monthEn{$ym} ) { 
	    $monthAverage = format_number( $monthAtt{$ym} / $monthEn{$ym} * 100, 1,1); 
	}

	print qq{<td>};
	if ( $arr{showraw} ) {
	    print qq{$monthAtt{$ym} / $monthEn{$ym} };
	}
	if ( $arr{showpercent} ) {
	    print qq{ $monthAverage$percentsymbol };
	}
	print qq{</td>\n};


	$gtotalAtt += $monthAtt{$ym};
	$gtotalEn += $monthEn{$ym};
    }

    if ( $gtotalEn ) { 
	$gtotalAverage = format_number( $gtotalAtt / $gtotalEn * 100, 1,1); 
    }
    print qq{<td>};
    if ( $arr{showraw} ) {
	print qq{$gtotalAtt / $gtotalEn };
    }
    if ( $arr{showpercent} ) {
	print qq{ $gtotalAverage$percentsymbol };
    }
    print qq{</td></tr>\n};


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

}




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

    # Get remote databases and years
    my $remotedbase = 'information_schema';
    my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
    my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);
    #my $dbh = DBI->connect($dsn,'tsecremote','ts99ec4');

    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;

    while ( my $db = $sth->fetchrow ) {
#	print qq{DB:$db<br>\n};
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

	if ( $db =~ m/$dbase/ ) {
#	    print qq{Match:$db<br>\n};
	    $remotedb{$db} = 1;
	}
    }


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" 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 colspan="2" class="cn"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    
    print qq{<tr><td class="bra">$lex{'Report Type'}</td>\n};
    print qq{<td><select name="reporttype"><option value="html">$lex{HTML}</option>\n};
    print qq{<option value="csv">$lex{CSV}</option></select></td></tr>\n};


    print qq{<tr><td class="bra">$lex{Show} $lex{Gender} $lex{Results}</td>\n};
    print qq{<td><input type="checkbox" name="showgender" value="1" checked="checked"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Show} $lex{Raw} $lex{Results}</td>\n};
    print qq{<td><input type="checkbox" name="showraw" value="1" checked="checked"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Show} $lex{Percentage} $lex{Results}</td>\n};
    print qq{<td><input type="checkbox" name="showpercent" value="1" checked="checked"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Show} $lex{Division}</td>\n};
    print qq{<td><input type="checkbox" name="showdivision" value="1" checked="checked"></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Show} K,PK</td>\n};
    print qq{<td><input type="checkbox" name="showk" value="1" ></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Select} $lex{Remote} $lex{Database}</td><td></td></tr>\n};
    foreach my $db ( sort keys %remotedb ) { 
	print qq{<tr><td class="la"><input type="radio" name="db" value="$db"> $db</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;

}

