#!/usr/bin/perl
#  Copyright 2001-2016 Leslie Richardson
#  This file is part of Open Administration for Schools

use DBI;
use CGI;

# Pull completed courses from local copy of data.

my $self = 'viewcompletedcourses.pl';

my %lex = ( 'Main' => 'Main',
	    'Error' => 'Error',
	    'Grade' => 'Grade',
	    'Homeroom' => 'Homeroom',
	    'Group' => 'Group',
	    'Separate with Spaces' => 'Separate with Spaces',
	    'Blank=All' => 'Blank=All',
	    'Continue' => 'Continue',
	    'Report Card' => 'Report Card',
	    'School' => 'School',
	    'View' => 'View',
	    'Completed Courses' => 'Completed Courses',
	    'Select' => 'Select',
	    'Student' => 'Student',
	    'Blank=All' => 'Blank=All',
	    'Report' => 'Report',
	    'View/Download' => 'View/Download',
	    'View Log File' => 'View Log File',

	    );


my $grayscale = '0.92';

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


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

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


my @tim = localtime(time);
my $year = $tim[5] + 1900;
$tim[4]++;
for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}}
my $currdate = "$year-$tim[4]-$tim[3]";
my $currtime = "$tim[2]:$tim[1]:$tim[0]";

my $title = "$lex{View} $lex{'Completed Courses'}";

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

print qq{$chartype\n};
print qq{</head><body>\n};
print qq{[ <a href="$reppage">Report Card</a> ]\n};
print qq{<h1>$title</h1>\n};

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

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

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

    if ( $arr{htmlreport} ) {
	delete $arr{htmlreport};
	getCourseHistory();

    } else { # pdf report
	delete $arr{pdfreport};
	courseReport();
    }
}


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

    # Setup the form and start of table.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellpadding="3" border="0" cellspacing="0">\n};
    
    print qq{<tr><td class="bla">$lex{Select} $lex{Student} $lex{Group}, $lex{'Blank=All'}</td></tr>};

    print qq{<tr><td class="la"><select name="grouptype"><option value="grade">$lex{Grade}</option>\n};
    print qq{<option value="homeroom">$lex{Homeroom}</option></select>\n};
    print qq{ <input type="text" name="groupvalue" size="8"></td></tr>\n};

    print qq{<tr><td class="la">Check Next Page <input type="checkbox" name="chk" value="1"></td></tr>\n};

    print qq{<tr><td class="la" colspan="2">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

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

    exit;

}


#----------------
sub selectStudent {
#----------------

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

    my $chk;
    if ( $arr{chk} ) { $chk = 'checked="checked"'; }
    delete $arr{chk};


    my $select;
    my $groupname;
    if ( $arr{groupvalue} ) { # we're picking a group
	if ( $arr{grouptype} eq 'grade' ) {
	    $select = 'where grade = ?';
	    $groupname = $lex{Grade};
	} else {
	    $select = 'where homeroom = ?';
	    $groupname = $lex{Homeroom};
	}
    }

    my $sth = $dbh->prepare("select lastname, firstname, studnum, provnum from student
     $select order by lastname, firstname");
    if ( $select ) {
	$sth->execute( $arr{groupvalue} );
    } else {
	$sth->execute;
    }
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    print qq{<h3>$groupname $arr{groupvalue}</h3>\n};

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

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

    print qq{<tr><td class="la"><input type="submit" name="pdfreport" value="PDF $lex{Report}">};
    print qq{<input type="submit" name="htmlreport" value="HTML $lex{Report}"></td></tr>\n};

    while ( my ( $lastname, $firstname, $studnum, $provnum ) = $sth->fetchrow ) {
	print qq{<tr><td class="la"><input type="checkbox" name="$provnum" value="1" $chk> };
	print qq{<b>$lastname</b>, $firstname ($studnum)</td></tr>\n};
    }

    print qq{<tr><td class="la"><input type="submit" name="pdfreport" value="PDF $lex{Report}">};
    print qq{<input type="submit" name="htmlreport" value="HTML $lex{Report}"></td></tr>\n};

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

    exit;

} # end of selectStudent




#-------------------
sub getCourseHistory {
#-------------------

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

    # Setup to get student data from table
    my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? 
      order by schoolyear, courseenddate, coursetitle");
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

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

    my $sth2 = $dbh->prepare("select distinct eval.subjcode from eval, subject where eval.subjcode = subject.subjsec 
     and studnum = ? order by startrptperiod, description");
    my $sth3 = $dbh->prepare("select description, endrptperiod from subject where subjsec = ?");
    my $sth4 = $dbh->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?");


    my %sortedstudent;
    # sort the students by name;
    foreach my $provnum ( keys %arr ) {
	$sth1->execute( $provnum );
	my ($lastname, $firstname ) = $sth1->fetchrow;
	$sortedstudent{"$lastname$firstname$provnum"} = $provnum;
    }


    foreach my $key ( sort keys %sortedstudent ) {

	my $provnum = $sortedstudent{$key};
	my %courses;

	# Get Completed Courses
	$sth->execute( $provnum );
	 if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	
	# Get Name, Grade, Local Number
	$sth1->execute( $provnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade, $studnum ) = $sth1->fetchrow;


	my $first = 1;
	my $totalcredits;    
	my $prevschoolyear = -1;
	my $currschoolyear;
	my $colora = '#CCC';
	my $colorb = '#FFF';

	print qq{<h3>$firstname $lastname<br>Grade:$grade &ndash; $provnum</h3>\n};
	

	while ( my $cref = $sth->fetchrow_hashref ) {

	    my %c = %$cref;


	    if ( $first ) { # start the table.
		$first = 0;
		print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
		print qq{<tr><th>Course Title</th><th>Code</th><th>School Year</th>};
		print qq{<th>End Date</th><th>Mark</th><th>Credits</th></tr>\n};

	    }

	    $prevschoolyear = $currschoolyear;
	    $currschoolyear = $c{courseenddate};

	    if ( $currschoolyear ne $prevschoolyear ) { # toggle color
		if ( $bgcolor eq $colora ) { 
		    $bgcolor = $colorb;
		} else {
		    $bgcolor = $colora;
		}
	    }
		
	    $courses{"$c{courseid}"} = $c{creditsearned};
	    print qq{<tr style="background-color:$bgcolor;"><td>$c{coursetitle}</td><td>$c{courseid}</td><td>$c{schoolyear}</td>};
	    print qq{<td>$c{courseenddate}</td><td>$c{finalmark}</td>};


	    $totalcredits += $c{creditsearned};
	    if ( $c{creditsearned} == 0 ) { 
		$c{creditsearned} = qq{<span style="color:red;font-weight:bold;">$c{creditsearned}</span>};
	    }

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

	}


	if ( not $first ) {
	    print qq{<tr><td class="bra">Credits Earned</td><td colspan="5" class="la">};
	    print qq{<span style="font-size:130%;font-weight:bold;">$totalcredits</span></td></tr>\n};
	    print qq{</table><p></p>\n};
	} else {
	    print qq{<h3>No Courses Found</h3>\n};
	}



	# Now Do the Local Courses,
	print qq{<h3>Current Courses</h3>\n};

	my $first = 1;
	my $localcredits;

	# Get Course Codes in Eval (Course Marks)
	$sth2->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	while ( my $subjsec = $sth2->fetchrow ) { # got the subjsecs

	    my ( $code, $section ) = split(/-/, $subjsec);

	    if ( $courses{$code} == 1 ) {
		print qq{<div>Skipping $subjsec. Already Recorded.<br>\n};
		next;
	    }


	    # Get the Course Info
	    $sth3->execute( $subjsec );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($desc, $endterm) = $sth3->fetchrow;

	    my $track = $g_MTrackTermType{$grade};
	    my $enddate = $g_MTrackTerm{$track}{$endterm}{'end'};


	    # Get the Mark.
	    $sth4->execute( $studnum, $subjsec, $endterm );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $mark = $sth4->fetchrow;

	    if ( $first ) { # start the table.
		$first = 0;
		print qq{<table cellpadding="4" cellspacing="0" border="1" style="background-color:#444;color:white;">\n};
		print qq{<tr><th>Course Title</th><th>Code</th><th>School Year</th>};
		print qq{<th>End Date</th><th>Mark</th><th>Credits</th></tr>\n};

	    }

	    print qq{<tr><td>$desc</td><td>$subjsec</td><td>Current</td>};
	    print qq{<td>$enddate</td><td>$mark</td>};

	    my $creditsearned;
	    if ( $mark > 49 ) { $creditsearned = 1 } else { $creditsearned = 0; }

	    $localcredits += $creditsearned;
	    if ( $creditsearned == 0 ) { 
		$creditsearned = qq{<span style="color:red;font-weight:bold;">$creditsearned</span>};
	    }

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

	} # end of local subjects loop


	my $grandtotal = $localcredits + $totalcredits;

	if ( not $first ) {
	    print qq{<tr><td class="bra">Credits Earned</td><td colspan="5" class="la">};
	    print qq{<span style="font-size:130%;font-weight:bold;">$localcredits</span></td></tr>\n};

	    print qq{<tr><td class="bra">Grand TotalCredits Earned</td><td colspan="5" class="la">};
	    print qq{<span style="font-size:130%;font-weight:bold;">$grandtotal</span></td></tr>\n};

	    print qq{</table><p></p>\n};
	} else {
	    print qq{<h3>No Courses Found</h3>\n};
	}


    } # end of student loop.

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

} # end of getCourseHistory



#---------------
sub courseReport {
#---------------

    my @tim = localtime(time);
    my $year = $tim[5] + 1900;
    $tim[4]++;
    for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}}
    my $currdate = "$year-$tim[4]-$tim[3]";
    # my $currtime = "$tim[2]:$tim[1]:$tim[0]";

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

    # Setup to get student data from table
    my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? 
      order by schoolyear, courseenddate, coursetitle");
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my $sth1 = $dbh->prepare("select lastname, firstname, grade, birthdate from studentall where provnum = ?");

    # Sask Ed Course Names
    my $sth2 = $dbh->prepare("select title from sasked_courses where code = ?");



    # Setup for PDF / TEX output
    my $shortname = "completedcourses$$";
    my $filename = "$shortname.tex";

    open(TEX,">$filename") || die "Can't open tex file";

    my $papersize;
    if ( $defaultpapersize ) {
	$papersize = $defaultpapersize;
    } else {
	$papersize = 'letterpaper';
    }
    
    print TEX "\\documentclass[12pt,$papersize]{article}
\\usepackage{array,inputenc,colortbl,fancyhdr}
$a_latex_header
\\renewcommand{\\familydefault}{\\sfdefault}

\\setlength{\\headsep}{10pt}
\\setlength{\\headheight}{14pt}
\\setlength{\\topmargin}{0mm}

\\setlength{\\textwidth}{7.50in}
\\setlength{\\textheight}{9.5in}
\\setlength{\\hoffset}{-13mm}
\\setlength{\\voffset}{-20mm}
\\setlength{\\parindent}{0in}
\\setlength{\\evensidemargin}{0in}
\\setlength{\\oddsidemargin}{0in}
\\setlength{\\extrarowheight}{4pt}

\\pagestyle{fancy}
\\lhead{$schoolname -- $schoolyear}
\\rfoot{ Printed: $currdate }
\\cfoot{ }
\n";

print TEX "\\begin{document}\n";


    my %sortedstudent;
    # sort the students by name;
    foreach my $provnum ( keys %arr ) {
	$sth1->execute( $provnum );
	my ($lastname, $firstname ) = $sth1->fetchrow;
	$sortedstudent{"$lastname$firstname$provnum"} = $provnum;
    }

    foreach my $key ( sort keys %sortedstudent ) {

	my $provnum = $sortedstudent{$key};

	# Get Completed Courses
	$sth->execute( $provnum );
	 if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	
	# Get Name, Grade
	$sth1->execute( $provnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade, $birthdate ) = $sth1->fetchrow;


	my $first = 1;
	my $totalcredits;    
	my $prevschoolyear = -1;
	my $currschoolyear;
	my $colora = "\\rowcolor[gray]{$grayscale}";
	my $colorb = '1';


	print TEX "\n{\\normalsize Completed Courses}\n\\medskip\n\n";
	print TEX "{\\large $firstname $lastname} Grade $grade -- $birthdate ($provnum)\n\n";


	while ( my $cref = $sth->fetchrow_hashref ) {

	    my %c = %$cref;

	    # Get Course Title, if not present
	    if ( not $c{coursetitle} ) {
		$sth2->execute( $c{courseid} );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my  $title = $sth2->fetchrow;
		$c{coursetitle} = $title; # since some records don't have the course title.
	    }


	    if ( $first ) { # start the table.
		$first = 0;
		# Start Table: 6 columns
		print TEX "\\begin{tabular}{|p{70mm}|p{10mm}|p{23mm}|p{22mm}|p{10mm}|p{10mm}|}\\hline\n";
		print TEX qq{\\bf Course Title &\\bf Code &\\bf{School~Year} &\\bf End Date &\\bf Mark &\\bf{Credits}\\\\ \\hline\n};

	    }

	    $prevschoolyear = $currschoolyear;
	    $currschoolyear = $c{courseenddate};

	    if ( $currschoolyear ne $prevschoolyear ) { # toggle color
		if ( $bgcolor eq $colora ) { 
		    $bgcolor = '';
		} else {
		    $bgcolor = $colora;
		}
	    }
		
	    print TEX "$bgcolor $c{coursetitle} &$c{courseid} &$c{schoolyear} ";
	    print TEX "&$c{courseenddate} &$c{finalmark}";


	    $totalcredits += $c{creditsearned};
	    if ( $c{creditsearned} == 0 ) {
		$c{creditsearned} = "{\\bf $c{creditsearned} }";
	    }

	    print TEX "&$c{creditsearned}\\\\\ \\hline\n";

	}


	if ( not $first ) {
	    print TEX "\\bf{Credits Earned} &\\bf $totalcredits & & & & \\\\ \\hline\n";
	    print TEX "\\end{tabular}\n\n";
	    print TEX "\\newpage\n";
	} else {
	    print TEX "No Courses Found\n";
	}

    } # end of student loop.


    print TEX "\\end{document}\n";
    close TEX;

    system("$pdflatex $filename >pdflog$$.txt");
    system("mv $shortname.pdf $downloaddir");
    system("mv pdflog$$.txt $downloaddir");
    system("rm -f $shortname.*");

    print qq{<h1><a href="$webdownloaddir/$shortname.pdf">\n};
    print qq{$lex{'View/Download'} $title</a></h1>\n};
    print qq{<p>[ <a href="$webdownloaddir/pdflog$$.txt">$lex{'View Log File'}};
    print qq{</a>\n | <a href="$homepage">$lex{Main}</a> ]</p>\n};
    print qq{</body></html>\n};


} # end of courseReport

