#! /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 = 'markview2_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);



# 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'} 2";
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};
    selectCourses();

} 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} for Courses</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 selectCourses {
#----------------

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


    if ( not $arr{grade} ) { #
	print qq{<h3>Missing Grade!</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    print qq{<h3>Grade $arr{grade} Courses</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};

    
    # Get Courses for the grade
    my $sth = $dbhr->prepare("select * from subject where grade = ? 
        order by startrptperiod, endrptperiod, description");
    $sth->execute( $arr{grade} );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    my $sth1 = $dbhr->prepare("select  count(distinct studnum) from eval where subjcode = ?");

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

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

	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<tr><th></th><th>Course</th><th>Number</th><th>Teacher</th><th>Terms</th>};
	    print qq{<th>Enrolled</th></tr>\n};
	    $first = 0;
	}
	    
	my %c = %$ref;

	# find enrollment
	$sth1->execute( $c{subjsec} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ecount = $sth1->fetchrow;

	# get teacher name;
	$sth2->execute( $c{teacher} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($ln,$fn) = $sth2->fetchrow;
	my $teacher = qq{<b>$ln</b>, $fn};
	
	
	if ( $ecount ) {
	    print qq{<tr><td><input type="checkbox" name="$c{subjsec}" value="1" $checked></td>};
	} else {
	    print qq{<tr><td></td>};
	}
	
	print qq{<td>$c{description}</td><td>$c{subjsec}</td><td>$teacher</td>\n};
	print qq{<td class="cn">$c{startrptperiod}-$c{endrptperiod}</td>};
	print qq{<td class="cn">$ecount</td></tr>\n};

    }

    print qq{</table>\n};
    print qq{<div style="margin:1em;"><input type="submit" value="$lex{Continue}"></div>\n};
    print qq{</form></body></html>\n};

    exit;

} # end of selectCourses



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


    my (%courses, %students, %enrol);
    # courses{subjsec} = ref; $students{$studnum} = name; enrol{$subjsec} = @studnum;
    
    # Get Course Info.
    my $sth = $dbhr->prepare("select * from subject where subjsec = ?");

    my $sth1 = $dbhr->prepare("select distinct e.studnum, s.lastname, s.firstname 
      from eval e, studentall s where subjcode = ? and e.studnum = s.studnum 
      order by s.lastname, s.firstname");

    my $sth2 = $dbhr->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?");
   
    
    foreach my $subjsec ( keys %arr ) {
    
	$sth->execute( $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	$courses{ $ref->{subjsec} } = $ref; # index by subjsec


	# get students in the course, sort by name
	$sth1->execute( $subjsec );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while (	my ($sn,$ln,$fn) = $sth1->fetchrow ) {
	    push @{ $enrol{$subjsec} }, qq{$sn};
	    $students{$sn} = qq{<b>$ln</b>, $fn};
	}

    }

   

    # now print courses.
    #    my $first = 1;
    my $count;
    foreach my $subjsec ( keys %courses ) {

	my %c = %{ $courses{$subjsec}};

	my $finalterm = $c{endrptperiod};
	
#	if ( $first ) {
	    print qq{<table cellspacing="0" cellpadding="3" border="1" style="float:left;margin:1em;">\n};
	    print qq{<caption style="font-weight:bold;font-size:120%;">$c{description}<br>};
	    print qq{$subjsec $c{teacher} $c{startrptperiod}-$c{endrptperiod}</caption>\n};
	    
	    print qq{<tr><th>Student</th><th>$lex{Mark}</th></tr>\n};
#	    $first = 0;
#	}

	foreach my $studnum ( @{ $enrol{$subjsec} } ) {

	    # Get Student Mark
	    $sth2->execute( $studnum, $subjsec, $finalterm  );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $mark = $sth2->fetchrow;

	    print qq{<tr><td>$students{$studnum}</td><td>$mark</td></tr>\n};
	}

	print qq{</table>\n};

	$count++;
	if ( $count % 4 == 0 ) {
	    print qq{<br clear="left">\n};
	}
	
    }


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

    exit;

}
