#!/usr/bin/perl
#  Copyright 2001-2023 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 = ('Courses' => 'Courses',
	   'Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Teacher' => 'Teacher',
	   'Term' => 'Term',
	   'Grade' => 'Grade',
	   'Sort by' => 'Sort by',
	   'No Teacher' => 'No Teacher',
	   'Code-Sec' => 'Code-Sec',
	   'Terms' => 'Terms',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Clone' => 'Clone',
	   'SmDesc' => 'SmDesc',
	   'Enrol' => 'Enrol',
	   'Error' => 'Error',
	   'Fix' => 'Fix',
	   'Field' => 'Field',
	   'Credit' => 'Credit',
	   'Difficulty' => 'Diff',
	   'Course' => 'Course',
	   'Title' => 'Title',
	   'Master' => 'Master',

	   );

my $teacherFixFlag; # flag teacher field containing name (still).


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

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

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


my $sortorder;
if ($arr{sortorder} eq $lex{Title} ){
    $sortorder = 'description, grade, teacher';
} elsif ($arr{sortorder} eq $lex{Teacher} ){
    $sortorder = 'teacher, description, grade';
} elsif ($arr{sortorder} eq $lex{Grade} ){
    $sortorder = 'grade, description, teacher';
} elsif ($arr{sortorder} eq $lex{Term}){
    $sortorder = 'startrptperiod, endrptperiod, grade, description, teacher';
} else {
    $sortorder = 'description,grade,teacher';
    $arr{sortorder} = $lex{Title};
}

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


# Get the Courses in sorted order.
my @subjects;
if ( $arr{sortorder} eq $lex{Grade} ){
    my @grades;
    my $sth = $dbh->prepare("select distinct grade from subject 
     where grade is not NULL and grade != ''");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    my @grades = sort {$a <=> $b} @grades; # get into correct order. 

    foreach my $grade ( @grades ) {

	my $sth = $dbh->prepare("select subjsec from subject where grade = ? 
				order by description, teacher");
	$sth->execute( $grade );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $subjsec = $sth->fetchrow ) {
	    push @subjects, $subjsec;
	}
    }

} else { # normal
    my $sth = $dbh->prepare("select subjsec from subject order by $sortorder");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $subjsec = $sth->fetchrow ) {
	push @subjects, $subjsec;
    }
}
# Now have courses in @subjects.



my $title = qq{$lex{Edit}/$lex{Delete} $lex{Course} $lex{Master}};

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>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$reppage">$lex{'Report Card'}</a> ]\n};


# Sorting Form
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="submit" value="$lex{'Sort by'}">\n};
print qq{<select name="sortorder">\n};
if ( $arr{sortorder} ) {
    print qq{<option>$arr{sortorder}</option>\n};
}
print qq{<option>$lex{Title}</option><option>$lex{Teacher}</option>\n};
print qq{<option>$lex{Grade}</option><option>$lex{Term}</option>\n};
print qq{</select></form>\n};

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

print qq{<table cellpadding="3" border="1" cellspacing="0">};
print qq{<tr><th>$lex{Course}</th><th>$lex{'Code-Sec'}};
print qq{</th><th>$lex{SmDesc}</th><th>$lex{Grade}};
print qq{</th><th>$lex{Teacher}</th><th>$lex{Terms}</th>};
# print qq{<th>$lex{Credit}<br>$lex{Difficulty}</th>\n};
print qq{<th>$lex{Enrol}</th><th>$lex{Edit}</th>\n};
print qq{<th>$lex{Delete}</th><th>$lex{Clone}</th></tr>\n};

my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?");

my $sth = $dbh->prepare("select id, description, smdesc, grade, 
 teacher, startrptperiod, endrptperiod, credit, difficulty 
 from subject where subjsec = ?");


foreach my $subjsec ( @subjects ) {

    # Get course info
    $sth->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ( $id, $description, $smdesc, $grade, $teacher, 
	 $startterm, $endterm, $credit, $difficulty ) = $sth->fetchrow;
    
    # Get Eval record count
    $sth1->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $count = $sth1->fetchrow;

    my $terms = ($endterm - $startterm) + 1;
    if ($terms < 1) { $terms = 1; }
    my $enrollments = round( $count / $terms, 2);

    # Get Teacher Record
    $sth2->execute( $teacher );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($lastname, $firstname) = $sth2->fetchrow;
    if ( $lastname ) {
	$teachername = qq{$lastname, $firstname ($teacher)};
    } else { # no teacher found.
	my $userid;
	if ( $teacher ) { $userid = qq{($teacher)}; }
    	$teachername = qq{<span style="color:red;font-weight:bold;">$lex{'No Teacher'} $userid</span>}; 
    }

    print qq{<tr><td>$description</td><td class="cn">$subjsec</td><td>$smdesc</td>\n};
    print qq{<td class="cn">$grade</td><td>$teachername</td><td >$startterm - $endterm</td>};
    # print qq{<td>$credit<br>$difficulty</td>};
    print qq{<td class="cn">$enrollments</td>\n};

    print qq{<td><form action="subjed.pl" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="id" value="$id">\n};
    print qq{<input type="submit" value="$lex{Edit}"></form></td>\n};

    print qq{<td><form action="subjdel.pl" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="id" value="$id">\n};
    print qq{<input type="submit" value="$lex{Delete}"></form></td>\n};

    print qq{<td><form action="subjadd.pl" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="subjsec" value="$subjsec">\n};
    print qq{<input type="submit" value="$lex{Clone}"></form></td>\n};

}

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