#! /usr/bin/perl
#  Copyright 2001-2022 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',
	   'Grade' => 'Grade',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'School Year' => 'School Year',
	   'Check Next Page' => 'Check Next Page',
	   'Show Withdrawn' => 'Show Withdrawn',
	   'Select' => 'Select',
	   'Student' => 'Student',

	   );

use DBI;
use CGI;

my $self = 'markview3_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'} 3 - By Grade";
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};
    selectGrades();

} 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 selectGrades {
#---------------

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

    # 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{<h3>Select Grades</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};

    
    my $first = 1;
    
    foreach my $grade ( sort {$a <=> $b } @grades ) {

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

	    $first = 0;
	}
	    
	print qq{<tr><td><input type="checkbox" name="$grade" value="1" $checked></td>};
	print qq{<td class="cn">$grade</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.
    # only grades left here in %arr

    # Setup SQL scripts
    my $sth = $dbhr->prepare("select * from subject where grade = ? order by description");

    my $sth1 = $dbhr->prepare("select studnum, lastname, firstname from student where grade = ?
			      order by lastname, firstname");
    
    my $sth2 = $dbhr->prepare("select a1 from eval where studnum = ? and subjcode = ? and term = ?");
    
    my $sth3 = $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");

    

    foreach my $grade ( sort keys %arr ) {

	my (%course, @course, %studname, %enrol, @studsort);
	# course{subjsec} = ref; $studname{$studnum} = name; eval{studnum}{$subjsec} = mark;

	# Get Courses in this grade
	$sth->execute( $grade );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    $course{ $ref->{subjsec} } = $ref; # index by subjsec
	    push @course, $ref->{subjsec};
	}

	# Get Students in this grade
	$sth1->execute( $grade );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ($studnum, $lastname, $firstname) = $sth1->fetchrow ) {
	    $studname{ $studnum } = qq{<b>$lastname</b>, $firstname};
	    push @studsort, $studnum;
	}


	# Find any student outliers (possibly in other grades)
	foreach my $subjsec ( @course ) {
	    $sth3->execute( $subjsec );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    while ( my ($studnum, $lastname, $firstname) = $sth3->fetchrow ) {
		if ( not $studname{ $studnum } ) { # another grade student; add him/her
		    $studname{ $studnum } = qq{<b>$lastname</b>, $firstname};
		    push @studsort, $studnum;
		}
	    }
	}

	# Print the Grade Table.
	my $first = 1;
	foreach my $studnum ( @studsort ) { # each row
	    
	    if ( $first ) { # start the table.
		print qq{<table cellspacing="0" cellpadding="3" border="1" };
		print qq{style="float:left;margin:1em;">\n};
		print qq{<caption style="font-weight:bold;font-size:120%;">};
		print qq{Grade $grade</caption>\n};

		# print Course Names;
		print qq{<tr><th></th>\n};
		foreach my $subjsec ( @course ) {
		    print qq{<th>$course{$subjsec}->{smdesc}</th>};
		}
		print qq{</tr>\n};
		
		$first = 0;
	    }

	    print qq{<tr><td>$studname{$studnum}</td>};
	    
	    foreach my $subjsec ( @course ) {

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

		print qq{<td>$mark</td>\n};
	    }
	    print qq{</tr>\n}; # end of course loop / this row
	    
	} # end of student loop

	print qq{</table>\n};

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

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

    exit;

}
