#!/usr/bin/perl
#  Copyright 2001-2018 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 = ('View' => 'View',
	   'Enrollment' => 'Enrollment',
	   'Report Card' => 'Report Card',
	   'Grades' => 'Grades',
	   'Withdrawn' => 'Withdrawn',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'Terms' => 'Terms',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Blank=All' => 'Blank=All',
	   'Teacher' => 'Teacher',
	   'Sort by' => 'Sort by',
	   'Term' => 'Term',
	   'Grade' => 'Grade',
	   'Description' => 'Description',
	   'Enrol' => 'Enrol',
	   'Main' => 'Main',
	   'Code' => 'Code',
	   'Student' => 'Student',
	   'Count' => 'Count',
	   'Std Dev' => 'Std Dev',
	   'Mean' => 'Average',
	   'Success' => 'Success',
	   'Rate' => 'Rate',
	   'Report' => 'Report',
	   'Course' => 'Course',
	   'Not Found' => 'Not Found',

	   );

my $self = 'rptcourse1.pl';
my $markfield = 'a1'; # marks normally stored in first field.


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

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $self: $@<br>\n";
    die $lex{Error}. " $self: $@\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;


# Print HTML Page Header
my $title = "$lex{Course} $lex{Report} 1";

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</head><body style="padding:1em 2em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> | };
print qq{<a href="$reppage">$lex{'Report Card'}</a> ]\n};

print "<h1>$title</h1>\n";


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

} else {
    delete $arr{page};
    showCourses();
}



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

    # Find all of the term patterns.
    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; }

    # Find Teachers
    my $sth1 = $dbh->prepare("select distinct s.teacher, st.lastname, st.firstname from subject s, staff st
     where s.teacher = st.userid order by st.lastname, st.firstname");
    $sth1->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }


    print "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"1\">\n";

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

    # Grade Select
    print "<tr><td class=\"bla\">$lex{Grades}</td>\n";
    print "<td class=\"la\">";
    print "<input type=\"text\" name=\"grade\" size=\"10\"> ";
    print "$lex{'Separate with Spaces'}, $lex{'Blank=All'}</td></tr>\n";


    # Term Select
    print "<tr><td class=\"bla\">$lex{Terms}</td><td>";
    while ( my ( $startterm, $endterm ) = $sth->fetchrow ) {
	    print qq{<input type="radio" value="$startterm-$endterm" name="terms">\n};
	    print qq{$startterm-$endterm &nbsp;\n};
    }
    print qq{ $lex{'Blank=All'}</td></tr>\n};

    # Teacher Select
    print "<tr><td class=\"bla\">$lex{Teacher}</td><td>";
    print "<select name=\"teacher\"><option></option>\n";
    while ( my ( $userid, $lastname, $firstname ) = $sth1->fetchrow ) {
	print "<option value=\"$userid\">$lastname, $firstname</option>\n";
    }
    print "</select></td></tr>\n";


    # Sorting Order
    my %sorttext = ('term' => "$lex{Term}", grade => "$lex{Grade}", teacher => "$lex{Teacher}", description => "$lex{Description}");
    my @sort = qw (term grade teacher description);
    my $default1 = 'term';
    my $default2 = 'grade';
    my $default3 = 'description';

    print "<tr><td class=\"bla\">$lex{'Sort by'}</td><td>";

    # Sort 1
    print "<select name=\"sort1\"><option value=\"$default1\">$sorttext{$default1}</option>\n";
    foreach my $val ( @sort ) {
	if ( $val eq $default1 ) { next; }
	print "<option value=\"$val\">$sorttext{$val}</option>";
    }
    print "</select>&nbsp;&nbsp;\n";

    # Sort 2
    print "<select name=\"sort2\"><option value=\"$default2\">$sorttext{$default2}</option>\n";
    foreach my $val ( @sort ) {
	if ( $val eq $default2 ) { next; }
	print "<option value=\"$val\">$sorttext{$val}</option>";
    }
    print "</select>&nbsp;&nbsp;\n";

    # Sort 3
    print "<select name=\"sort3\"><option value=\"$default3\">$sorttext{$default3}</option>\n";
    foreach my $val ( @sort ) {
	if ( $val eq $default3 ) { next; }
	print "<option value=\"$val\">$sorttext{$val}</option>";
    }
    print "</select>&nbsp;\n";

    print "</td></tr>\n";



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

    print "</table></form></body></html>\n";

    exit;

}




#--------------
sub showCourses {
#--------------

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

    # Grade Select
    my $gradeSelect;
    if ( $arr{grade} ) {
	my @grades = split(/\s+/, $arr{grade});
	my $first = 1;
	my $gs;
	foreach my $gr ( @grades ) {
	    $gr = $dbh->quote( $gr );
	    if ( not $first ) { $gs .= ' or '; } else { $first = 0; }
	    $gs .= "grade = $gr ";
	}
	$gradeSelect = $gs;
    }
    # delete $arr{grade};


    # Teacher Select
    my ($lastname, $firstname);
    my $teacherSelect;
    if ( $arr{teacher} ) {
	my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
	$sth->execute( $arr{teacher} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	($lastname, $firstname) = $sth->fetchrow;

	my $tch = $dbh->quote( $arr{teacher} );
	$teacherSelect = "teacher = $tch";
    }
    # delete $arr{teacher};


    # Term Select
    my ($startterm, $endterm);
    my $termSelect;
    if ( not $arr{terms} ) {
	$startterm = 1;

	my $sth = $dbh->prepare("select max(endrptperiod) from subject");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	$endterm = $sth->fetchrow;

    } else {

	( $startterm, $endterm ) = split('-', $arr{terms});
	$start = $dbh->quote( $startterm );
	$end = $dbh->quote( $endterm );
	$termSelect = "startrptperiod = $start and endrptperiod = $end";
    }

    my $globalSelect;
    if ( $teacherSelect ) {
	$globalSelect = $teacherSelect; 
    }
    if ( $gradeSelect ) {
	if ( $globalSelect ) { $globalSelect .= ' and '; }
	$globalSelect .= "( $gradeSelect )";
    }

    if ( $termSelect ) {
	if ( $globalSelect ) { $globalSelect .= ' and '; }
	$globalSelect .= "( $termSelect )";
    }

    print qq{<h3>$lex{Terms} $startterm - $endterm };
    if ( $arr{grade} ) { print qq{$lex{Grades} $arr{grade}\n}; }
    if ( $arr{teacher} ) { print qq{$lex{Teacher} $firstname $lastname ($arr{teacher})\n}; }
    print qq{</h3>\n};


    # replace term text with rptperiod.
    for my $i (1..3) {
	my $key = 'sort'. $i;
	if ( $arr{$key} eq 'term' ) {
	    $arr{$key} = 'startrptperiod, endrptperiod';
	}
    }


#    print "Global Sel:$globalSelect<br>\n";
#    print "Grade Sel: $gradeSelect<br>\n";
#    print "Term Sel: $termSelect<br>\n";
    
    if ( $globalSelect ) { $globalSelect = "where $globalSelect"; } # prepend where
    my $sth = $dbh->prepare("select * from subject $globalSelect
      order by $arr{sort1}, $arr{sort2}, $arr{sort3}");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

    my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
    my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    my $sth3 = $dbh->prepare("select $markfield, studnum from eval where subjcode = ? and term = ?");


    my $first = 1;
    while ( my $ref = $sth->fetchrow_hashref ) {


	if ( $first ) { # start the table
	    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
	    print qq{<tr><th>$lex{Description}</th><th>$lex{Code}</th><th>$lex{Student}<br>$lex{Count}</th>};
	    for my $t ( $startterm..$endterm ) {
		print qq{<th>$lex{Term} $t<br>$lex{Mean} (%)</th><th>$lex{Term} $t<br>$lex{'Std Dev'} (%)</th>};
	    }
	    print qq{<th>$lex{Success}#</th><th>$lex{Success}<br>$lex{Rate}</th></tr>\n};
	    $first = 0;
	}


	my %r = %$ref;
	my $subjsec = $r{subjsec};

	my (%marks, %avg, %sdev );
	my $success;
	foreach my $term ( $startterm..$endterm ) {

	    # Get any marks
	    $sth3->execute( $subjsec, $term );  # end term has final marks
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    while ( my ($mark,$studnum) = $sth3->fetchrow ) {
		$mark =~ s/\D+|%//g;  # strip out percents or text.
		if ( not $mark ) { next; }
		if ( $mark =~ m/\d/) {
		    if ( $term == $endterm and $mark >= 50 ) { $success++; } 
		    $marks{$subjsec}{$term}{$studnum} = $mark;
		}
	    }

	    my @marks = values %{ $marks{$subjsec}{$term} };
	    if ( @marks ) {
		$avg{$term} = round( mean( @marks ), 1);
		$sdev{$term} = round ( stddev( @marks ), 1);
	    }
	}
    
	# Enrollment Count
	$sth1->execute( $r{subjsec} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my $ecount = $sth1->fetchrow;
	# if (not $ecount ) { next; } # no students; skip
	
	print qq{<tr><td>$r{description}</td><td>$r{subjsec}</td><td class="cn">$ecount</td>};
	foreach my $term ( $startterm..$endterm ) {
	    print qq{<td class="cn">$avg{$term}</td><td class="cn">$sdev{$term}</td>};
	}
	my $rate;
	if ( $success ) {
	    $rate = round ($success / $ecount * 100, 1). '%';
	}

	print qq{<td class="cn">$success</td><td>$rate</td></tr>\n};

    } # end of course loop

    if ( $first ) {
	print qq{<h3>$lex{Course} $lex{'Not Found'}</h3>\n};
    } else {
	print qq{</table>\n};
    }


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

} # end of showEnrollment.
