#!/usr/bin/perl
#  Copyright 2001-2022 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',
	   'Attendance' => 'Attendance',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'Absent' => 'Absent',
	   'Late' => 'Late',
	   
	   );

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

my $self = 'rptCreditsEarned.pl';

my @grades = (10,11,12);


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";
}


my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
if ( length( $mon ) == 1 ) { $mon = '0'. $mon; }
if ( length( $mday ) == 1 ) { $mday = '0'. $mday; }

my $currdate = "$year-$mon-$mday";

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


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




my $title = qq{Attendance vs Credits Earned Report\n};

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} ) { # load javascript library
    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>\n};
print qq{<body>[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$attpage">$lex{Attendance}</a> ]\n};

print qq{<h1>$title</h1>\n};



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}; }
    # passed a single start:end value
    
    my ( $startterm, $endterm ) = split(':', $arr{terms});
    
    print qq{<h3>Terms $startterm - $endterm</h3>\n};

    # Passed Start and End term; verify they are in the correct
    # order. then get the dates based on the track they are in. Using
    # those dates, loop through each grade in turn, and get their
    # enrollments and attendance values and classify into one of the
    # %attendance blocks.
    
    if ( not $startterm ) {
	print qq{<h3>Term Error</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    # Get the term dates for each grade; getTermDates is in libattend
    my %termdates;
    foreach my $gr ( @grades ) {
	my ($ss,$se) = getTermDates($gr, $startterm );
	my ($es,$ee) = getTermDates($gr, $endterm );
	$termdates{$gr} = {'start' => $ss, 'end' => $ee };
    }

    # Get the end term date for grade 12 (should be same as 10,11)
    my ($ey,$em,$ed) = split('-', $termdates{12}{end} );

   
    # Get the Completed courses for this reporting period.
    my %completed; # completed courses in this month.
    my $sth = $dbh->prepare("select * from sasked_completedcourses 
      where year(courseenddate) = '$ey' and month(courseenddate) = '$em' order by courseenddate");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) { 
	my %r = %$ref;

	$completed{$r{provnum}}{ $r{courseid} } = $r{creditsearned};
#	print "<div>$r{courseenddate} - $r{provnum} - $r{courseid}</div>\n";

    }


    my $sth = $dbh->prepare("select studnum from student where grade = ?");

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

#    print qq{<table border="0" cellpadding="3" cellspacing="0" };
#    print qq{style="margin:1em 0em;border:1px solid gray;padding:0.4em;">\n};
#    print qq{<tr><th>Grade</th><th>Student-Days<br>Enrolled</th><th>Student-Days<br>Absent</th>};
#    print qq{<th>Student-Days<br>Present</th><th>% Present</th></tr>\n};

 
    my %attgroup; # $attgroup{grade}{group} = @studnums  group is 70,50,49

   
    foreach my $grade ( @grades ) {
	
	# term dates
	my ($startdate,$se) = getTermDates($grade, $startterm);
	my ($es,$enddate) = getTermDates($grade, $endterm);

	# ppd for this grade;  periods per day (ppd) allows us to convert to days absent;
	my $ppd = $g_ppd{$grade};
	if ( not $ppd ) {
	    print qq{<h3>Attendance Periods Per Day not found for grade $grade<</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}

	# students in this grade;
	$sth->execute( $grade );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	my ($grAbsentDay,$grEnrolledDay); # don't need lates here.

	
	while ( my $studnum = $sth->fetchrow ) {

	    my ($studabsentday,$studlatecount, $studdaysenrolled);
	    
	    # Get enrolled and attended in this date range.
	    my @enrolblocks = findEnrollmentBlocks( $studnum, $startdate, $enddate, $dbh );
	    
	    if ( not $enrolblocks[0] ) { # no enrollment in this time period
		next;
	    }

	    # Loop through each enrollment block
	    foreach my $ref ( @enrolblocks ) {
		my $startblock = $ref->{start};
		my $endblock = $ref->{end};

		# Get Absents and Lates
		my $res = myCalcAttendance( $studnum, $startblock, $endblock, $ppd );
		my ($absday,$latecount) = split(':', $res);
		$studabsentday += $absday;
		$studlatecount += $latecount;
		
		# Get Days enrolled, which are the days open in this time period.
		my $daysenrolled = calcDaysOpen( $startblock, $endblock, $dbh);
		# print qq{Days Open:$daysenrolled<br>\n};
		$studdaysenrolled += $daysenrolled;

	    } # enrollment block loop for this student

	    my $pctAtt;
	    my $presentday = $studdaysenrolled - $studabsentday;
	    if ( $studdaysenrolled ) {
		$pctAtt = round( 100 * $presentday / $studdaysenrolled, 1);
	    }


#	    print "ATT:$pctAtt \n";
	    
	    if ( $pctAtt >= 70 ) {
		push @{ $attgroup{$grade}{'70'} }, $studnum;
	    
	    } elsif ( $pctAtt >= 50 ) {
		push @{ $attgroup{$grade}{'50'} }, $studnum;
		
	    } else { # less than 50%
		push @{ $attgroup{$grade}{'49'} }, $studnum;
	    }


	} # end of student loop
	
    } # end of Grade Loop

    # we now have to find, for each grade and attendance group, their possible credits and actual credits.
    my @groups = ('70','50','49');
    my %grouptext = ( '70' => '70% or Higher', '50' => '50-70%', '49' => 'Less than 50%' );
    
#    foreach my $grade ( @grades ) {
#	foreach my $group ( @groups ) {
#	    my @studs = @{ $attgroup{$grade}{$group} };
#	    foreach my $studnum ( @studs ) {
#		print "GR:$grade Group:$group SN:$studnum<br>\n";
#	    }
#	}
#    }

    my (%totalpossible, %totalearned);

    
    foreach my $grade ( @grades ) {

	my ($grpossible, $grearned);
	# Start Table for this Grade;
	print qq{<table cellpadding="5" cellspacing="0" border="0" style="border:1px solid gray;">\n};
	print qq{<caption style="font-weight:bold;font-size:120%;">Grade $grade</caption>\n};
	print qq{<tr><th>Attendance</th><th>Credits<br>Possible</th><th>Credits<br>Earned</th>};
	print qq{<th>Credit Ratio<br>Earned/Possible</th></tr>\n};
	
	foreach my $group ( @groups ) {  # one line for each 
	    my @studs = @{ $attgroup{$grade}{$group} };
	    my ( $possible, $earned);

	    # Selections from DB
	    my $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ? and 
                term = $endterm");

	    my $sth1 = $dbh->prepare("select provnum from studentall where studnum = ?");

	    
	    foreach my $studnum ( @studs ) {
		
		# Get Provincial Number
		$sth1->execute( $studnum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my $provnum = $sth1->fetchrow;
		
		
		# Get their course enrollment for this term, then find their credits, add to credits.
		$sth->execute( $studnum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		while ( my $subjsec = $sth->fetchrow ) {

		    my ($subjcode, $section) = split('-', $subjsec);
		    
		    # is it a subject to skip?
		    my ($tsubjsec, $dud) = split('-', $subjsec );
		    my $skipflag;
		    if ( $r_SupressSubject{$subjcode} or $r_SupressSubject{$subjsec} or
			 $r_AdditionalComments{$subjcode} or $r_AdditionalComments{$subjsec} ) {
			next; # skip this course code
		    }

		    $possible++; # they have a possible credit.
		    $grpossible++;
		    $totalpossible{$group}++;

		    if ( exists $completed{$provnum}{$subjcode} ) { # we have those keys
			if ( $completed{$provnum}{$subjcode} ) { # if we have a credit;
			    $earned++;
			    $grearned++;
			    $totalearned{$group}++;
			} else {
#			    print qq{No Credit for $subjcode - $studnum<br>\n};
			}
		    }
		} # end of courses loop
	    } # end of students loop

	    my $pct = 0;
	    if ( $possible ) {
		$pct = round($earned * 100 / $possible, 1);
	    }
	    
	    print qq{<tr><td class="cn">$grouptext{$group}</td><td class="cn">$possible</td>};
	    print qq{<td class="cn">$earned</td><td class="cn">$pct %</td></tr>\n};
		    
	} # end of attendance groups loop
	
	# print totals line and end the table.
	my $grpct = 0;
	if ( $grpossible ) {
	    $grpct = round($grearned * 100 / $grpossible, 1);
	}

	print qq{<tr style="background-color:#DDD;">};
	print qq{<td class="cn">Total Overall</td><td class="cn">$grpossible</td>};
	print qq{<td class="cn">$grearned</td><td class="cn">$grpct %</td></tr>\n};
	print qq{</table><p></p>\n};

    } # end of grade loop


    # Now do the totals
    my $grades = join(',',@grades);
    print qq{<table cellpadding="5" cellspacing="0" border="0" style="border:1px solid gray;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Grades $grades</caption>\n};
    print qq{<tr><th>Attendance</th><th>Credits<br>Possible</th><th>Credits<br>Earned</th>};
    print qq{<th>Credit Ratio<br>Earned/Possible</th></tr>\n};

    my ($tearn, $tposs); # hold grand totals for all grades, all attendance groups
    foreach my $group ( @groups ) {  # one line for each

	$tearn += $totalearned{$group};
	$tposs += $totalpossible{$group};
	
	my $pct = 0;
	if ( $totalpossible{$group} ) {
	    $pct = round($totalearned{$group} * 100 / $totalpossible{$group}, 1);
	}
	    
	print qq{<tr><td class="cn">$grouptext{$group}</td><td class="cn">$totalpossible{$group}</td>};
	print qq{<td class="cn">$totalearned{$group}</td><td class="cn">$pct %</td></tr>\n};
    }

    my $pct = 0;
    if ( $tposs ) {
	$pct = round($tearn * 100 / $tposs, 1);
    }
   
    print qq{<tr style="background-color:#DDD;"><td class="cn">Total Overall</td><td class="cn">$tposs</td>};
    print qq{<td class="cn">$tearn</td><td class="cn">$pct %</td></tr>\n};
    print qq{</table><p></p>\n};

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

    exit;

}




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


    # get all the course enrolled terms
    my @terms;
    my $sth = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject 
       order by startrptperiod, endrptperiod");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($s,$e) = $sth->fetchrow ) {
	push @terms, "$s:$e";
    }
    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellpadding="4" cellspacing="0" border="0" style="margin:1em 2em;border:1px solid gray;">\n};
    # print qq{<caption style="font-weight:bold;font-size:120%;">Select Terms</caption>\n};

=head    # leave the date selectors just in case.
    print qq{<tr><td class="bra">$lex{'Start Date'}</td>\n<td><input type="text" };
    print qq{name="startdate" id="sdate" size="10">};
    print qq{<button type="reset" id="start_trigger">...</button>};
    print qq{</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'End Date'}</td>\n<td><input type="text" };
    print qq{name="enddate" id="edate" size="10" value="$currdate">};
    print qq{<button type="reset" id="end_trigger">...</button></td></tr>\n};
=cut

    print qq{<tr><th>Select Terms</th></tr>\n};
    foreach my $val ( @terms ) {
	my ($start,$end) = split(':', $val);
	print qq{<tr><td class="la"><input type="radio" name="terms" value="$val">\n};
	print qq{$start - $end</td></tr>\n};
    }	
    
    print qq{<tr><td class="la">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

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

=head    
    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :  "sdate", // 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 
    });

    Calendar.setup({
        inputField     :  "edate", // id of the input field
        ifFormat       :  "%Y-%m-%d", // format of the input field
        button         :  "end_trigger", // trigger for the calendar (button ID)
        singleClick    :  false,        // double-click mode
        step           :  1             // show all years in drop-down boxes
    });
   </script>\n};
=cut

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

    exit;

}



#-----------------
sub myCalcAttendance {
#-----------------
    my ( $studnum, $startdate, $enddate, $periodsperday ) = @_;
    my $sth = $dbh->prepare("select * from attend 
     where studentid = ? and 
     to_days(absdate) >= to_days('$startdate') and 
     to_days(absdate) <= to_days('$enddate') 
     order by absdate,period");
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    $sth->execute( $studnum );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $absent = 0; my $late = 0;
    while ( my @absence = $sth->fetchrow ) {
	if ( $absence[3] =~ /$lex{Absent}/) { $absent++; }
	if ( $absence[3] =~ /$lex{Late}/) { $late++; }
    }
    if ( $periodsperday ) {
	$absent = $absent / $periodsperday; #convert periods into days
    }
    return "$absent:$late";
}

