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

#  This file is part of Open Admin for Schools.

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

my %lex = ('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 = 'progplan_required.pl';


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


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



# 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 ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}


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



my (%saskedgrade, %saskedname);

my $title = "Program Planning - Required Course Offerings";
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};
    findRequirements();

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


#-------------------
sub findRequirements {
#-------------------

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

    my (%totals, %currenrol );

   
    # load the sasked ed courses with grade;
    my $sth = $dbh->prepare("select * from sasked_courses");
    $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};
    }

    

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

    my $first = 1;

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

	if ( $r{provnum} ) {

	    # Grade 10 requirements
	    my @result = checkCredit10( $r{provnum}, $dbh );
	    foreach my $res ( @result ) {
		$totals{10}{$res}++;
	    }

	    # Grade 11 requirements
	    my @result11 = checkCredit11( $r{provnum}, $dbh );
	    foreach my $res ( @result11 ) {
		$totals{11}{$res}++;
	    }

	    # Grade 12 requirements;
	    my @result12 = checkCredit12( $r{provnum}, $dbh );
	    foreach my $res ( @result12 ) {
		$totals{12}{$res}++;
	    }
	    
	} else {  
	    print qq{<div style="color:red;">Missing Provincial Number - $r{firstname} $r{lastname}</div>\n};
	}

    } # end student loop

    
    # Print Totals Row
    print qq{<h3>Course Requirements for Current Grade 10-12 students</h3>\n};

    print qq{<div>Not including grade 9 students for grade 10 courses and<br>};
    print qq{courses students are currently enrolled in</div>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>Course</th><th>Count</th></tr>\n};
    
    foreach my $grade ( 10..12 ) {
	print qq{<tr style="background-color:#DDD;"><td colspan="2">Grade $grade</td></tr>\n};
	foreach my $key ( sort keys %{ $totals{$grade} } ) {
	    print qq{<tr><td>$key</td><td>$totals{$grade}{$key}</td></tr>\n};
	}
    }


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

    
} # end of findRequirements



#-------------
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 checkCredit10 { # check grade 10 completion list; return any errors (missing requirements)
#----------------

    # 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} = 1;
    }

    my @incomplete; # return text based codes

    my %ignore = qw(4017 4018 4214 4424 4423 4307 4306 4309); # courses required to skip in finding electives.

    # Go through the completion requirements;

    # 1. ELA A10 (4017)
    if ( not $completed{4017} ) {
	push @incomplete, 'ELA A10';
    }

    # 2. ELA B10 (4018)
    if ( not $completed{4018} ) {
	push @incomplete, 'ELA B10';
    }

    # 3. Science 10 (4214)
    if ( not $completed{4214} ) {
	push @incomplete, 'Science 10';
    }


    # 4. A Math (4424 (Foundations), or 4423 (Workplace))
    if ( not $completed{4424} and not $completed{4423} ) { # haven't done either math
	push @incomplete, '10 Math';
    }

    # 5. A Social Science  SS10 (4307) ,Hist10 (4306) , Native Stud 10 (4309)
    if ( not $completed{4307} and not $completed{4306} and not $completed{4309} ) { # haven't done any social
	push @incomplete, '10 Social';
    }

    # 6. 3 more electives at 10 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) { 
	my $grade = $saskedgrade{$courseid};

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 10 ) { next; } # lower grade level

	push @electives, $courseid;
    }

    if ( @electives < 3 ) { # error
	push @incomplete, '10 Electives';
    }

    return @incomplete;

} # end of checkCredit10



#----------------
sub checkCredit11 { # check grade 11 completion list; return any errors (missing requirements)
#----------------

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

    my ($provnum,$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;
#    print qq{<b>Credit 11:$provnum</b><br>\n};
    while ( my $courseid = $sth->fetchrow ) {
#	print "$saskedname{$courseid} ($courseid)/ ";
	$completed{$courseid} = 1;
    }


    my @incomplete; # return text based codes
    my %ignore; # codes to ignore (since credit already given)


    # Go through the completion requirements;

    # 1. ELA 20 (6017)
    if ( not $completed{6017} ) {
	push @incomplete, 'ELA 20';
    } else {
	$ignore{6017};
    }

    # 2. Sciences ( Enviro 6246, Physical 6247, Health 6245, Computer 6702 ) (#physics 20 (6213) and chem 20 (6212) no longer allowed
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid ( qw(6245 6246 6247 6702 6212 6213)) {  
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Science';
    }


    # 3. A Math ( 6423 Workplace, 6425 Foundations, 6426 Pre-Calculus )
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid (qw(6423 6425 6426)) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Math';
    }


    # 4. A Social Science (big list)
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid ( qw(6306 6309 6307 2140 2150 6340 8306 8321 8307 8339 3140 3150 8340 )) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0;
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Social';
    }


    # 5. 3 more electives at 11 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) { 
	my $grade = $saskedgrade{$courseid};
	# print "Course:$courseid Grade:$grade<br>\n";

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 11 ) { next; } # lower grade level
	push @electives, $courseid;
    }

    if ( @electives < 3 ) { # error
	push @incomplete, '11 Electives';
    }

    return @incomplete;


} # end of checkCredit11


#----------------
sub checkCredit12 { # check grade 12 completion list; return any errors (missing requirements)
#----------------

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

    my ($provnum,$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;
#    print qq{<b>Credit 11:$provnum</b><br>\n};
    while ( my $courseid = $sth->fetchrow ) {
#	print "$saskedname{$courseid} ($courseid)/ ";
	$completed{$courseid} = 1;
    }


    my @incomplete; # return text based codes
    my %ignore; # codes to ignore (since credit already given)


    # Go through the completion requirements;

    # 1. ELA A30
    if ( not $completed{8017} ) {
	push @incomplete, 'ELA A30';
    } else {
	$ignore{8017};
    }


    # 2. ELA B30
    if ( not $completed{8018} ) {
	push @incomplete, 'ELA B30';
    } else {
	$ignore{8018};
    }


=head
    # 3. Sciences ( Enviro 6246, Physical 6247, Health 6245, Computer 6702 ) (#physics 20 (6213) and chem 20 (6212) no longer allowed
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid qw(6245 6246 6247 6702 6212 6213) {  
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Science';
    }


    # 3. A Math ( 6423 Workplace, 6425 Foundations, 6426 Pre-Calculus )
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid qw(6423 6425 6426) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Math';
    }
=cut


    # 4. A Social Science
    my ($cdnflag, $g10flag, $g1112flag); # flags to show if done both gr 10 credit and 11/12 credit for these other SS courses.
    foreach my $cid ( qw(8306 8321 8307)) {
	if ( $completed{$cid} ) { 
	    $cdnflag = 1;
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }

    # Now check the rest
    foreach my $cid ( qw(4306 4307 4309 6306 6307 6309 2140 2150 6340 8339 3140 3150 8340 )) {
	if ( $completed{$cid} ) { 
	    if ( $saskedgrade{$cid} == 10 ) { $g10flag = 1; }
	    if ( $saskedgrade{$cid} == 11 or $saskedgrade{$cid} == 12 ) { $g1112flag = 1; }
	    $ignore{$cid} = 1;
	}
    }

    if ( not $cdnflag or not $g10flag or not $g1112flag ) {
	push @incomplete, '12 Social';
    }


    # 5. Electives: 6 more electives at 11 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) {
	my $grade = $saskedgrade{$courseid};

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 11 ) { next; } # lower grade level
	push @electives, $courseid;
    }

    if ( @electives < 6 ) { # error
	push @incomplete, '12 Electives';
    }

    return @incomplete;


} # end of checkCredit12




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

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

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

    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">\n};
#    print qq{<tr><td class="la" colspan="2"><input type="submit" value="$lex{Continue}"></td></tr>\n};

    print qq{<tr><td class="bla">$lex{Grade} <input type="text" name="grade" size="4"></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 "<h3>Student Not found</h3>\n"; print "</body></html>\n"; exit; }


    
    # Get Course Info from Sask Ed courses.
    my $sth1 = $dbh->prepare("select title, grade 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>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="5" class="bra">};
	    print qq{Current Credits</td><td  class="cn">$totalcredits</td></tr>\n};
	}
	$first = 0;
	
	# Get Course Title if not stored
	my ($title, $grade);
	$sth1->execute( $r{courseid} );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade) = $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>$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="5" class="bra">Total Credits</td>};
    print qq{<td class="cn">$totalcredits</td></tr>\n};
    print qq{</table>\n};

=head

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

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

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

}
