#! /usr/bin/perl
#  Copyright 2001-2020 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 = ('Marks' => 'Marks',
	   'Main' => 'Main',
	   'No Record(s) Found' => 'No Record(s) Found',
	   'Grade' => 'Grade',
	   'Error' => 'Error',
	   'Students' => 'Students',
	   'Continue' => 'Continue',
	   'School Year' => 'School Year',
	   'Check Next Page' => 'Check Next Page',
	   'Show Withdrawn' => 'Show Withdrawn',
	   'Select' => 'Select',
	   'Withdrawn' => 'Withdrawn',
	   'Student' => 'Student',
	   'Course' => 'Course',
	   'Terms' => 'Terms',
	   'Mark' => 'Mark',

	   );

use DBI;
use CGI;

my $self = 'markview_past.pl';

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

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

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

# Local connection
my $dsn = "DBI:mysql:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;


# Load Main admin configuration ('admin')
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";
    }
}

# Get the values from configuration system
my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
foreach my $val ( qw( r_MarkField r_SupressSubject r_AdditionalComments )) {
    $sth->execute( $val  );
    my $datavalue = $sth->fetchrow;
    eval $datavalue;
    if ( $@ ) {
	print $lex{Error}. " $@<br>\n";
	die $lex{Error}. " $@\n";
    }
}
# Capture this, since may change in older configs.
my $currdownloaddir = $downloaddir;


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


my @tim = localtime(time);
my $year = $tim[5] + 1900;
my $month = $tim[4] + 1;
my $day = $tim[3];
if (length($month) == 1){ $month = "0".$month;}
if (length($day) == 1){ $day = "0".$day;}
my $currsdate = "$year-$month-$day";
my $currdate = "$month[$month] $day, $year";


# Load Remote database
my $dbhr;
if ( $arr{db} ) { # from start page
    my $db = $arr{db};
    my $dsn = "DBI:mysql:database=$db;host=$remotehost";
    $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword);

    # Get the values from configuration system of this year.
    my $sthr = $dbhr->prepare("select datavalue from conf_system where dataname = ?");
    foreach my $val ( qw( schoolyear )) {
	$sthr->execute( $val  );
	my $datavalue = $sthr->fetchrow;
	eval $datavalue;
	if ( $@ ) {
	    print $lex{Error}. " $@<br>\n";
	    die $lex{Error}. " $@\n";
	}
    }
}



my $title = "$lex{Student} $lex{'Marks'}";
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">
$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="/ssp.html">SSP</a> ]\n};

print qq{<h1>$title</h1>\n};


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

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

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



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

    # Get remote databases
    my $remotedbase = 'information_schema';
    my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
    my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);


    my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my %remotedb;

    # Select Previous Databases for this school
    while ( my $db = $sth->fetchrow ) {
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

	if ( $db =~ m/$dbase/ ) {
	    $remotedb{$db} = 1;
	}
    }


    # Get Student Grades
    my @grades;
    $sth = $dbh->prepare("select distinct grade from student 
      where grade is not NULL and grade != ''");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }
    while ( my $grade = $sth->fetchrow ) {
	push @grades, $grade;
    }

    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" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};

    print qq{<tr><td class="bla" colspan="2">$lex{Select} $lex{'School Year'}</td></tr>\n};
    foreach my $db ( sort keys %remotedb ) {

	my $temp = $db;
	$temp =~ s/$dbase//; # strip database name;
	my $year = $temp - 1; # previous year;
	$year = qq{$year-$temp}; # now in 2018-2019 format
	
	print qq{<tr><td class="la"><input type="radio" name="db" value="$db"> $year ($db)</td></tr>\n};
	
    }

    print qq{<tr><td colspan="2"><hr></td></tr>\n};


    # Get Grade
    print qq{<tr><td class="bla">$lex{Select} $lex{Grade}</td><td class="la">};
    print qq{<select name="grade"><option></option>\n};
    foreach my $grade ( sort {$a <=> $b} @grades ) {
	print qq{<option>$grade</option>};
    }
    print qq{\n</select></td></tr>\n};


    # Check next Page
    print qq{<tr><td class="bla">$lex{'Check Next Page'}</td>\n};
    print qq{<td class="la"><input type="checkbox" name="checknextpage" value="1"></td></tr>\n};

    # Show Withdrawn
    print qq{<tr><td class="bla">$lex{'Show Withdrawn'}</td>\n};
    print qq{<td class="la"><input type="checkbox" name="showwithdrawn" };
    print qq{value="1" checked="checked"></td></tr>\n};

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

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

    exit;

}


#----------------
sub selectStudents {
#----------------                                                                                                    

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

    my $studenttable = 'student';
    if ( $arr{showwithdrawn} ) {
	$studenttable = 'studentall';
    }
    delete $arr{showwithdrawn};


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

    my ($select, $gradetext);
    
    if ( $arr{grade} ) { # we're picking a grade
	$select = 'where grade = ?';
	$gradetext = qq{&ndash; $lex{Grade} $arr{grade}};
    }


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

    my $sth1 = $dbhr->prepare("select count(*) from studentwd where studnum = ?");

    print qq{<h3>$lex{Select} $lex{Students} $gradetext \n};
    print qq{<span style="color:red;font-size:80%;">(WD = $lex{Withdrawn})</span></h3>\n};

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

    print qq{<h3>Final Term Override <input type="text" name="finalterm" style="width:2ch;">};
    print qq{ Blank = Default</h3>\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><th></th><th>$lex{Student}</th><th>Number</th><th>$lex{Grade}</th></tr>\n};


    while ( my ( $lastname, $firstname, $studnum, $grade ) = $sth->fetchrow ) {

	# Find out if withdrawn
	my $wd;
	$sth1->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $wdcount = $sth1->fetchrow;
	if ( $wdcount ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">WD</span>};
	}

        print qq{<tr><td class="cn"><input type="checkbox" name="$studnum" value="1" $checked></td> };
	print qq{<td class="la">$wd <b>$lastname</b>, $firstname</td>};
	print qq{<td>$studnum</td><td>$grade</td></tr>\n};

    }

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

    exit;

} # end of selectStudents                                                                                            



#-------------
sub showMarks {
#-------------

    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    delete $arr{db}; # not needed, since used at top of script.
    
    my $finalterm = $arr{finalterm};
    delete $arr{finalterm};

    if ( $finalterm ) {
	print qq{<h3>Final Term Override: $finalterm</h3>\n};
    }

    
    # Student Info
    my $sth = $dbhr->prepare("select lastname, firstname, grade from studentall where studnum = ?");

    my (%sortedname, %studname, %studgrade);
    foreach my $studnum ( keys %arr ) {
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname, $grade ) = $sth->fetchrow;
	$sortedname{"$lastname$firstname$studnum"} = $studnum;
	$studname{$studnum} = "$lastname, $firstname";
	$studgrade{$studnum} = $grade;
    }


    # Course Codes for this student

    # Subject (Course) Info
    my $sth2 = $dbhr->prepare("select * from subject where subjsec = ?");

    # Mark for this student
    my $sth3 = $dbhr->prepare("select * from eval where studnum = ? and subjcode = ? and term = ?");

    # Name of the Teacher of this course
    my $sth4 = $dbhr->prepare("select lastname, firstname from staff where userid = ?");

    

    my %courses; # course info: $courses{subjsec} = $ref;

    # Loop through each student, getting mark, subject, and demographic info.
    foreach my $key ( sort keys %sortedname ) {
	my $studnum = $sortedname{$key};

	my $first = 1;

	print qq{<h3>$studname{$studnum} ($studnum) $lex{Grade} $studgrade{$studnum}\n};
	print qq{ - $schoolyear</h3>\n};

	# Get student's courses (look into eval)
	my $sth = $dbhr->prepare("select distinct subjcode from eval where studnum = ?");
	$sth->execute($studnum);

	while ( my $subjsec = $sth->fetchrow ) {

	    if ( $first ) {
		print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
		print qq{<tr><th>$lex{Course}</th><th>Teacher</th><th>$lex{Terms}</th>};
		print qq{<th>$lex{Mark}</th></tr>\n};
		$first = 0;
	    }

	    
	    # Get Course Info, if not already loaded
	    my %c;
	    if ( not $courses{$subjsec} ) {
		$sth2->execute( $subjsec );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $cref = $sth2->fetchrow_hashref;
		%c = %$cref;
		$courses{$subjsec} = $cref; # put into cache.
	    } else { 
		%c = %{ $courses{$subjsec}};
#		print "From Cache<br>\n";
#		foreach my $key ( sort keys %c ) { print "K:$key V:$c{$key}<br>\n"; }
	    }

	    $sth4->execute( $c{teacher} );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my ($ln,$fn) = $sth4->fetchrow;
	    my $teacher = qq{<b>$ln</b>, $fn};

	    
	    # Allow override if no values in end reporting period.
	    my $endterm = $c{endrptperiod};
	    if ( $finalterm ) { $endterm = $finalterm; }
	    
	    $sth3->execute( $studnum, $subjsec, $endterm);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $eref = $sth3->fetchrow_hashref;
	    my %e = %$eref;

	    print qq{<tr><td><b>$c{description}</b> ($subjsec)</td><td>$teacher</td>};
	    print qq{<td>$c{startrptperiod}-$c{endrptperiod}</td>\n};
	    print qq{<td>$e{a1}</td></tr>\n};

	} # subject loop

	if ( $first ) {
	    print qq{<p>No Courses Found</p>\n};
	} else {
	    print qq{</table>\n};
	}

    } # studnum loop

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

    exit;

}
