#!/usr/bin/perl
#  Copyright 2001-2021 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.

use DBI;
use CGI;
use Time::JulianDay;

my %lex = ('Graduation Requirements' => 'Graduation Requirements',
	   'Credits' => 'Credits',
	   'Check' => 'Check', 
	   'Main' => 'Main',
	   'Provincial Number' => 'Provincial Number',
	   'Duplicate' => 'Duplicate',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'Check All' => 'Check All',
	   'Schools' => 'Schools',
	   'Select' => 'Select',
	   'Missing' => 'Missing',

	   );


my $self = 'checkgrad_local.pl';


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


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



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);
$dbh->{mysql_enable_utf8} = 1;


my (%saskedgrade, %saskedname);

my $title = "$lex{Check} $lex{Credits}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style>td.v { vertical-align:top;align:center; }</style>\n};
print qq{$chartype\n</head><body style="padding:1em;">\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};
    checkSchool();

}  elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    viewStudentCredits();
}


#--------------
sub checkSchool {
#--------------

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

    my %totals;
    my $studcount;
    
    if ( not $arr{grade} ) {
	print qq{<h3>$lex{Missing} $lex{Grade}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    my $grade = $arr{grade};
    delete $arr{grade};

    # load the sasked ed courses with grade;
    my $sth = $dbh->prepare("select * from sasked_courses where grade = 10 or 
      grade = 11 or grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	$saskedgrade{$r{code}} = $r{grade};
	$saskedname{$r{code}} = $r{title};
    }

    my $graderef = \%saskedgrade;
    
    print qq{<p>[ <a href="#totals" style="font-size:130%;">View Requirement Totals</a> ]</p>\n};

    # Load 'Program' hash
    my $sth = $dbh->prepare("select defaultvalue from meta where fieldid = 'program'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $progdata = $sth->fetchrow;
    my %program = split(/\s+/, $progdata);

       
    
    # Get students in that grade.
    my $sth = $dbh->prepare("select studnum, lastname, firstname, provnum, program 
       from student where grade = ? 
       order by lastname, firstname");
    $sth->execute( $grade );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my $first = 1;

    while ( $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	if ( not $r{program} ) { $r{program} = 10; } # normal program
	my $programtext = $program{ $r{program} };
	$programtext =~ s/_/ /g;
	
	$studcount++;

	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<tr><th>Student</th><th>Missing<br>Gr 10 Req</th><th>Missing<br>Gr 11 Req</th>};
	    print qq{<th>Missing<br>Gr 12 Req</th><th>Grade<br>Credits</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td class="v"><b>$r{lastname}</b>, $r{firstname} - $r{provnum}<br>\n};
	print qq{ $programtext<br>\n};


	if ( $r{provnum} ) {
	    print qq{<form action="$self" method="post" style="display:inline;">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    print qq{<input type="hidden" name="provnum" value="$r{provnum}">\n};
	    print qq{<input type="submit" value="View"></form>\n};
	}
	print qq{</td>};

	if ( $r{program} == 9 ) {
	    my @result = checkCreditAdult12( $r{provnum}, $graderef, $dbh );
	    print q{<td></td><td></td>}; # nothing in grade 10 or grade 11 rows.

	    print qq{<td>};
	    if ( not @result ) {
		print qq{<b>Complete</b>}; 
	    } else { #print incompletes
		foreach my $res ( @result ) {
		    $totals{12}{$res}++;
		    print qq{$res<br>\n}; 
		}
	    }
	    print qq{</td>\n};
	    
	    # Print completed Credits
	    my $cref = getCredits( $r{provnum}, $dbh );
	    my %credits = %$cref;
	    my %gcred; 
	    foreach my $cid ( keys %credits ) {
		$gcred{ $credits{$cid} }++;
	    }
	    print q{<td class="v">};
	    my $csum = '0';
	    foreach my $grade ( sort keys %gcred ) {
		print qq{Gr $grade: $gcred{$grade}<br>\n};
		$csum += $gcred{$grade};
	    }
	    print qq{<b>Total $csum</b></td></tr>\n};


	} elsif ( $r{provnum} ) {
	    my @result = checkCredit10( $r{provnum}, $graderef, $dbh );
	    print q{<td class="v">};
	    if ( not @result ) {
		print qq{<b>Complete</b>}; 
	    } else { #print incompletes
		foreach my $res ( @result ) {
		    $totals{10}{$res}++;
		    print qq{$res<br>\n}; 
		}
	    }

	    print q{</td><td class="v">};

	    my @result11 = checkCredit11( $r{provnum}, $graderef, $dbh );
	    if ( not @result11 ) { 
		print qq{<b>Complete</b>};
		
	    } else { #print incompletes
		foreach my $res ( @result11 ) {
		    $totals{11}{$res}++;
		    print qq{$res<br>\n}; 
		}
	    }


	    print q{</td><td class="v">};

	    my @result12 = checkCredit12( $r{provnum}, $graderef, $dbh );
	    if ( not @result12 ) {  # they COULD be done IF they have 24+ credits.
		# Check total credits to make sure.
		my $cref = getCredits( $r{provnum}, $dbh );
		my %credits = %$cref;
		my (%gcred, $csum); 
		foreach my $cid ( keys %credits ) {
		    $gcred{ $credits{$cid} }++;
		}
		foreach my $grade ( sort keys %gcred ) {
		    $csum += $gcred{$grade};
		}
		if ( $csum < 24 ) { # not enough credits yet.
		    print qq{12 Electives<br>\n};
		} else { # they have enough credits to graduate.
		    print qq{<b>Complete</b>};
		}
		
	    } else { #print incompletes
		foreach my $res ( @result12 ) {
		    $totals{12}{$res}++;
		    print qq{$res<br>\n}; 
		}
	    }
	    print qq{</td>\n};

	    
	    # Print completed Credits
	    my $cref = getCredits( $r{provnum}, $dbh );
	    my %credits = %$cref;
	    my %gcred; 
	    foreach my $cid ( keys %credits ) {
		$gcred{ $credits{$cid} }++;
	    }
	    print q{<td class="v">};
	    my $csum = '0';
	    foreach my $grade ( sort keys %gcred ) {
		print qq{Gr $grade: $gcred{$grade}<br>\n};
		$csum += $gcred{$grade};
	    }
	    print qq{<b>Total $csum</b></td></tr>\n};


	} else {
	    print qq{<td colspan="4" class="bla">Missing Provincial Number</td></tr>\n};
	}

    } # end student loop

    # Print Totals Row

    print qq{<tr><td class="v" style="font-weight:bold;font-size:130%;">};
    print qq{<a name="totals">Requirement<br>Totals</a><br>Students:$studcount</td> };
    
    foreach my $grade ( 10..12 ) {
	print q{<td class="v">};
	foreach my $key ( sort keys %{ $totals{$grade} } ) {
	    print qq{$key - $totals{$grade}{$key}<br>\n};
	}
	print qq{</td>\n};
    }
    print qq{<td></td></tr>\n};
    

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

    
} # end of checkSchool



#-------------
sub getCredits { 
#-------------

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

    my ($provnum, $dbh) = @_; # passed provincial number, dbh

    # Load their completed courses
    my $sth = $dbh->prepare("select courseid from sasked_completedcourses where 
       provnum = $provnum and creditsearned = '1'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my %completed;
    while ( my $courseid = $sth->fetchrow ) {
	$completed{$courseid} = $saskedgrade{$courseid};
    }

    return \%completed;

} # end of getCredits



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

    my $checked;
    if ( $arr{checked} ) {
	$checked = qq{checked="checked"};
    }

    print qq{<h3>$lex{Select} $lex{Grade}</h3>\n};

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

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

    # Grade
    print qq{<tr><td class="bla">$lex{Grade} <input type="text" name="grade" style="width:4ch;"></td></tr>\n};
   
    print qq{<tr><td class="la" colspan="2"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</table>\n};
    print qq{</form></body></html>\n};

    exit;

}


#---------------------
sub viewStudentCredits {
#---------------------

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

    if ( not $arr{provnum} ) { 
	print qq{<h3>Student Not found</h3>\n}; 
	print qq{</body></html>\n}; 
	exit; 
    }

    
    # Get Course Info from Sask Ed courses.
    my $sth1 = $dbh->prepare("select title, grade, ctype from sasked_courses where code = ?");


    # Load 'Program' hash
    my $sth = $dbh->prepare("select defaultvalue from meta where fieldid = 'program'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $progdata = $sth->fetchrow;
    my %program = split(/\s+/, $progdata);


    
    # Student Name, Grade, Program
    my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, program from studentall where provnum = ?");
    $sth->execute( $arr{provnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my ($lastname, $firstname, $studnum, $grade, $programcode ) = $sth->fetchrow;
    my $assumeflag;
    if ( not $programcode ) { $programcode = 10; $assumeflag = '(Assumed)'; }
    my $programtext = $program{$programcode};
    $programtext =~ s/_/ /g;
    
    print qq{<h3>$firstname $lastname  Grade:$grade</h3>\n};
    print qq{<div><b>Program</b> $programtext $assumeflag</div>\n};


    # Load their completed courses
    my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? 
      order by schoolyear, courseenddate");
    $sth->execute( $arr{provnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Grade</th><th>Type</th><th>School<br>Year</th><th>Mark</th>};
    print qq{<th>Credits<br>Earned</th></tr>\n};

    my $totalcredits;
    my ($curryear, $prevyear);
    my $first = 1;

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	$prevyear = $curryear;
	$curryear = $r{schoolyear};
	if ( $prevyear ne $curryear and not $first ) {
	    print qq{<tr style="background-color:#EEE;"><td colspan="6" class="bra">};
	    print qq{Current Credits</td><td  class="cn">$totalcredits</td></tr>\n};
	}
	$first = 0;
	
	# Get Course Title, grade, type
	$sth1->execute( $r{courseid} );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade, $ctype) = $sth1->fetchrow;

	my $creditcolor = 'green';
	if ( not $r{creditsearned} ) {
	    $creditcolor = 'red';
	}

	print qq{<tr><td>$title</td><td>$r{courseid}</td><td>$grade</td><td>$ctype</td><td>$r{schoolyear}</td>};
	print qq{<td>$r{finalmark}</td>};
	print qq{<td style="color:$creditcolor;" class="cn">$r{creditsearned}</td></tr>\n};
	$totalcredits += $r{creditsearned};

    }

    print qq{<tr style="background-color:#EEE;"><td colspan="6" class="bra">Total Credits</td>};
    print qq{<td class="cn">$totalcredits</td></tr>\n};
    print qq{</table>\n};


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

}
