#!/usr/bin/perl
#  Copyright 2001-2016 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 = ('School Enrollment' => 'School Enrollment',
	   'Main' => 'Main',
	   'Enrollment' => 'Enrollment',
	   'Current Date' => 'Current Date',
	   'Aging Date' => 'Aging Date',
	   'Current Enrollment' => 'Current Enrollment',
	   'Student' => 'Student',
	   'Grade' => 'Grade',
	   'Date' => 'Date',
	   'Type' => 'Type',
	   'Description' => 'Description',
	   'Enrolled' => 'Enrolled',
	   'Not Found' => 'Not Found',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'Teacher' => 'Teacher',
	   'Walkthrough' => 'Walkthrough',
	   'Curr' => 'Curr',
	   'Prev' => 'Prev',
	   'Staff' => 'Staff',
	   'Day Fraction' => 'Day Fraction',
	   'Days Closed' => 'Days Closed',
	   'Select Month' => 'Select Month',
	   'Missing' => 'Missing',
	   'School' => 'School',
	   'Month' => 'Month',

	   );

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

my $self = 'sdsrpt1_local.pl';

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

# load global vals to get global password
require "$globdir/global.conf" or die "Cannot open global.conf!\n";

# Central site access to get grade
my $dbname = 'central';
my $dsn1 = "DBI:$dbtype:dbname=$dbname";
my $dbh1 = DBI->connect($dsn1,$guser,$gpassword);


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

my $title = "SDS Report 1 - Completed Courses";
print "$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> ]\n};
print qq{<h1>$title</h1>\n};


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

#} else {
#    delete $arr{page};
    showReport();
#}



#-------------
sub showReport {
#-------------


    # Load Grade from database
    my $sth1 = $dbh1->prepare("select grade from sasked_courses where code = ?");


    # Load completed courses data
    my (%data, %coursegrade, %student );
    my $sth = $dbh->prepare("select * from sasked_completedcourses order by schoolyear, provnum");
    $sth->execute;
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	# get the grade for the course.
	my $grade;
	if ( $coursegrade{$r{courseid}} ) {
	    $grade = $coursegrade{ $r{courseid} };
	    
	} else { # load from database
	    $sth1->execute( $r{courseid} );
	    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
	    $grade = $sth1->fetchrow;
	}

	if ( not $grade ) { # lookup failure
	    print qq{<div>Missing Grade for: $r{coursetitle} - $r{courseid}</div>\n};

	} else { # we have a grade; add to data structure.
	    $data{ $r{schoolyear} }{$grade}{count}++;
#	    print qq{Year:$r{schoolyear} - $r{provnum}<br>\n};

	    $student{ $r{schoolyear}}{ $grade }{ $r{provnum} }++; # student count.
	    if ( $r{creditsearned} ) {
		$data{ $r{schoolyear} }{$grade}{credit} += $r{creditsearned};
	    }
	}
    }

#    use Data::Dumper;
#    print Dumper %att;

    # Now display 
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>School Year</th><th>Grade</th><th>Course<br>Enrol</th><th>Credit<br>Earned</th>};
    print qq{<th>#<br>Students</th><th>Completion<br>Rate (%)</th></tr>\n};

    my ($curryear, $prevyear, $yearcount);
    foreach my $schoolyear ( sort keys %data ) {
	$yearcount++;

	$prevyear = $curryear;
	$curryear = $schoolyear;
	if ( $curryear ne $prevyear and $prevyear ) {
	    print qq{<tr><td style="background-color:#AAA;" colspan="6"></td></tr>\n};
	}

	if ( $yearcount % 7 == 0 ) { # reprint heading
	    print qq{<tr><th>School Year</th><th>Grade</th><th>Course<br>Enrol</th><th>Credit<br>Earned</th>};
	    print qq{<th>#<br>Students</th><th>Completion<br>Rate (%)</th></tr>\n};
	}

	foreach my $grade ( sort keys %{ $data{$schoolyear}} ) {
	    my $studcount = keys %{ $student{$schoolyear}{$grade} };

#		foreach my $pn ( keys %{ $student{$schoolyear}{$grade} } ) {
#		    print "$schoolyear - Grade: $grade PN:$pn Enrollments: $student{$schoolyear}{$grade}{$pn}<br>\n"; 
#		}
 
	    my $count = $data{$schoolyear}{$grade}{count};
	    my $credit = $data{$schoolyear}{$grade}{credit};
	    my $comprate;
	    if ( $count ) {
		$comprate = $credit / $count * 100;
		$comprate = round( $comprate, 1);
	    }
	    print qq{<tr><td>$schoolyear</td><td>$grade</td><td>$count</td><td>$credit</td>};
	    print qq{<td>$studcount</td><td>$comprate</td></tr>\n};
	    
	}
#	    print "<br>\n";

    }

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

    # now remove the datastructure
    foreach my $schoolyear ( keys %data ) {
	foreach my $grade ( keys %{ $data{$schoolyear}} ) {
	    delete $data{$schoolyear}{$grade};
	    foreach my $pn ( keys %{ $data{$schoolyear}{$grade} } ) {
		delete $student{$schoolyear}{$grade}{$pn};
	    }
	}
    }


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

    exit;

}


# Not needed, but leave just in case.
#----------------
sub showStartPage {
#----------------

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

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


    print qq{<form action="$self" method="post" style="display:inline;margin:1em;">\n};
    print qq{<input type="hidden" name="checked" value="all">\n};
    print qq{<input type="submit" value="Check All Schools" style="display:inline;"></form>\n};
    print qq{<p></p>\n};


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


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


    print qq{<tr><td class="bla">Select Schools</td></tr>\n};
    print qq{<tr><td class="la" colspan="2"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    
    foreach my $db ( @dbase ) {
	print qq{<tr><td class="la" colspan="2">};
	print qq{<input type="checkbox" name="$db" value="1" $checked>\n};
	print qq{$alldbase{$db} ($db)</td></tr>\n};
    }


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

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

    exit;

}
