#!/usr/bin/perl
#  Copyright 2001-2024 Leslie Richardson

#  This file is part of Open Admin for Schools.

# Load current courses and find teacher and student loading.


use DBI;
use CGI;
use Time::JulianDay;

my %lex = ('Main' => 'Main',
	   'Grade' => 'Grade',
	   'Continue' => 'Continue',
	   'Section' => 'Section',
	   'Course' => 'Course',
	   'Teacher' => 'Teacher',
	   'Error' => 'Error',

	   );

my $self = 'preplancoursemaster.pl';


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


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

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


my $title = "Current Course Offerings - Teacher and Student";
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 style="padding:1em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$reppage">Report Card</a> ]\n};

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



# load the sasked ed courses (they have a grade field).
my (%saskedgrade, %saskedname);
my $sth = $dbh->prepare("select * from sasked_courses");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( $ref = $sth->fetchrow_hashref ) {
    my %r = %$ref;
    $saskedgrade{$r{code}} = $r{grade};
    $saskedname{$r{code}} = $r{title};
}

my $graderef = \%saskedgrade;


# Load staff into hash; not restricted to classroom teachers.
my (%teachers, %teachername );
my $sth = $dbh->prepare("select lastname, firstname, userid from staff 
     	order by lastname, firstname"); 
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) {
    $teachers{"$lastname, $firstname ($userid)"} = $userid;
    $teachername{$userid} = "$lastname, $firstname";
}


# get teachers of all courses from grade 10 to 12; used to check backings
my %courseteacher;
my $sth = $dbh->prepare("select subjsec, teacher from subject where grade = 10 or grade = 11 or grade = 12");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( my ($subjsec, $teacher ) = $sth->fetchrow ) {
    $courseteacher{$subjsec} = $teacher;
}


# Check for enrollments and student grades same as in course master
# Do we have enrollments
my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
# Get students in course, in prep for grade check.
my $sth2 = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
# Get grade of the student to see if same as course setting.
my $sth3 = $dbh->prepare("select grade from studentall where studnum = ?");
# Course Info - when no enrollments
my $sth4 = $dbh->prepare("select * from subject where subjsec = ?");


# Get all current courses
my $sth = $dbh->prepare("select * from subject");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

my %crs; # course hash
my %altgrades; # alternate grades for a course; (ie. Law 30, grade 12 course offered to grade 10's)
# $altgrades{subjsec}{grades} = 1;

while ( $ref = $sth->fetchrow_hashref ) {
    
    my %c = %$ref; # current record
    my $subjsec = $c{subjsec}; # course code and section ie. 4017-1
    if ( $subjsec =~ m/comment/ ) { next; } # skip any comment courses
    if ( $c{grade} < 10 ) { next; } # skip lower grades
    
    # Check for enrollments
    $sth1->execute($subjsec);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $enrolcount = $sth1->fetchrow;
    if ( not $enrolcount ) {
	$sth4->execute($subjsec);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $cref = $sth4->fetchrow_hashref;
	my %c = %$cref;  # %c is the course fields
	my $objcount = 0; # count objectives
	for my $idx (1..20) {
	    my $fld = qq{q.$idx};
	    if ( $c{fld} ) { # something in the objective
		$objcount++;
	    }
	}
	
	my ($code,$section) = split('-', $subjsec);
	print qq{<div>No Course Enrollments for <b>$saskedname{$code}</b> ($subjsec)};
	print qq{ Objectives:<b>$objcount</b></div>\n};
	next;
    }

    # Check for grade offerings.
    $sth2->execute($subjsec);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while (my $studnum = $sth2->fetchrow ) { # get student in this course
	# Get grade
	$sth3->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $grade = $sth3->fetchrow;
	if ( $grade ne $c{grade} ) {
	    $altgrades{$subjsec}{$grade} = 1;
#	    print qq{<div>Alternate Grade for course $subjsec ($c{grade}) - $grade</div>\n};

	}
    }

    # Add to course (crs) hash
    $crs{ $c{startrptperiod} }{ $c{endrptperiod} }{ "$subjsec:$c{teacher}" } = 1;
    

} # end course loop loading up %crs hash.

# check alt grades.
if (  %altgrades ) {
    foreach my $subjsec ( sort keys %altgrades ) {
	foreach my $gr ( sort keys %{ $altgrades{$subjsec} } ) {
#	    print qq{<div>Alt Grades $subjsec - $gr</div>\n};
	}
    }
}


# Find largest ending term so we can loop from term 1.
my $sth = $dbh->prepare("select max(endrptperiod) from subject");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $maxendterm = $sth->fetchrow;

#print qq{<div>Max End Term is: $maxendterm</div>\n};


my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
my $sth2 = $dbh->prepare("select count(*) from schedat where subjsec = ? and term = ?");

my $sth3 = $dbh->prepare("select distinct e.studnum, s.grade from eval e, studentall s where
   subjcode = ? and e.studnum = s.studnum");


foreach my $term (1..$maxendterm ) {

    print qq{<table style="margin:1em;border:1px solid black;padding:0.4em;float:left;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">};
    print qq{Term $term  <span style="font-weight:normal;">};
    print qq{Bold Grade is Course Grade, Others are actual enrolled grades</span></caption>\n};

    print qq{<tr><th>Grades</th><th>Course</th><th>Teacher</th><th>Enrolled</th></tr>\n};

    my $bref = createBacking($term);
    %back = %$bref;
    # %back{subjsec} = totalenrol (all backed courses in this term)
    # TEST DATA
#    print qq{<div>BACK Term $term<br>\n};
#    foreach my $subjsec ( sort keys %back ) {
#	my ($code, $section) = split('-', $subjsec);
#	print qq{CRS $saskedname{$code} ($subjsec) Enrolled:$back{$subjsec}<br>\n};
#    }
#    print qq{</div>\n};
    
    
    my (%load, %offr, @sort, %sort); # teacher load, course offerings
    foreach my $sterm ( sort keys %crs ) {
	foreach my $eterm ( sort keys %{ $crs{$sterm} } ) {
	    # now check if current term is within these parameters. 
	    if ( $term >= $sterm and $term <= $eterm ) { # have a course here.
		my $ref = $crs{$sterm}{$eterm};
		my %val = %$ref;
		foreach my $val ( sort keys %val ) {

		    my ($subjsec,$teacher) = split(':', $val);
		    my ($code, $section) = split('-', $subjsec);
		    
		    $sort{"$saskedgrade{$code}$saskedname{$code}$subjsec"} = $val;

		    
		    # Get course enrollment total
#		    $sth1->execute($subjsec);
#		    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
#		    my $enrolcount = $sth1->fetchrow;

#		    print qq{<tr><td>$saskedname{$code} ($subjsec)</td>};
#		    print qq{<td>$teachername{$teacher} ($teacher)</td><td><b>$saskedgrade{$code}</b>};
#		    my @grades = keys %{ $altgrades{$subjsec} };
#		    print qq{ @grades</td><td>$enrolcount</td></tr>\n};
		}
	    }
	}
    }

    @sort = sort keys %sort;

    foreach my $key ( @sort ) {
	my $val = $sort{$key};
	my ($subjsec,$teacher) = split(':', $val);
	my ($code, $section) = split('-', $subjsec);

	push @{ $load{$teacher} }, $subjsec;

	# Get course enrollment total
	$sth1->execute($subjsec);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $enrolcount = $sth1->fetchrow;

	# Get Per Grade enrollment
	my %grEnrol;
	$sth3->execute($subjsec);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	while ( my $gr = $sth3->fetchrow ) {
	    $grEnrol{$gr}++;
	}

	print qq{<tr><td><b>$saskedgrade{$code}</b>};
	my @grades = keys %{ $altgrades{$subjsec} };
	print qq{ @grades</td>};
	print qq{<td>$saskedname{$code} ($subjsec)</td>};
	print qq{<td>$teachername{$teacher} ($teacher)</td>};
	print qq{<td class="la"><b>$enrolcount</b> };
	foreach my $gr ( sort keys %grEnrol ){
	    print qq{/GR$gr:$grEnrol{$gr} };
	}
	print qq{</td></tr>\n};
    }

    # Teacher Loading
    print qq{<tr><td colspan="4"><hr></td></tr>\n};


    foreach my $teacher ( sort keys %load ) {
	my $pcount; # period count
	print qq{<tr><td colspan="4"><b>$teachername{$teacher}</b> ($teacher)<br>\n};
	my @crs = @{ $load{$teacher} };
	foreach my $subjsec ( @crs ) {
	    my ($code, $section) = split('-', $subjsec);
	    # Get periods for this course
	    $sth2->execute($subjsec, $term);
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    my $periods = $sth2->fetchrow;
	    print qq{$saskedname{$code} <b>$periods</b>P\n};
	    if ( $back{$subjsec} ) {
		print qq{Backed $back{$subjsec} Total Enrolled};
	    }
	    print qq{<br>\n};
	    $pcount += $periods;
	}
#	print qq{<b>Total Periods: $pcount</b></td></tr>\n};
	#print qq{<tr><td colspan="4">@crs</td></tr>\n};
	print qq{</td></tr>\n};
    }
        
    print qq{</table>\n};

    if ( $term % 2 == 0 ) {
	print qq{<div style="clear:left;"></div>\n};
    }



    
} # end of terms loop



#--------------
sub createBacking {  # create a backing hash %back{subjsec} = totalenrollment (all courses together)
#--------------

    my $term = @_[0];
    my %backed;

    # get the courses this term
    my $sth = $dbh->prepare("select distinct subjsec from schedat where term = ?");

    # get the days, periods for courses this term
    my $sth1 = $dbh->prepare("select day,period from schedat where subjsec = ? and term = ?");

    # loop over the day, period, term 
    my $sth2 = $dbh->prepare("select subjsec from schedat where term = ? and day = ? and period = ?");

    # get course enrollment
    my $sth3 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
    
    $sth->execute($term);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $subjsec = $sth->fetchrow ) { # loop over all courses this term

	$sth1->execute($subjsec, $term);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	while ( my ($day, $period) = $sth1->fetchrow ) { # loop over day, period

	    # Loop over all courses for this period (day, term) looking for common teacher (ie. backed)
	    my %tch; # tch{userid} = @subjsec
	    $sth2->execute($term, $day, $period);
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    while ( my $subjsec = $sth2->fetchrow ) {
		my $teacher = $courseteacher{$subjsec};
		push @{$tch{$teacher}}, $subjsec;
	    }

	    foreach my $teacher ( keys %tch ) {
		# check number of course entries for today
		my $ccount = @{ $tch{$teacher}};
		if ( $ccount > 1 ) { # backings
		    my $totalenrol;
		    foreach my $s ( @{ $tch{$teacher}} ) {
			$sth3->execute($s);
			if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
			my $enrol = $sth3->fetchrow;
			$totalenrol += $enrol;
		    }
		    foreach my $s ( @{ $tch{$teacher}} ) { # populate for all courses backed.
			$backed{$s} = $totalenrol;
		    }
	    
#		    print qq{<div>CHK:Term $term SUB $subjsec CNT:$ccount Day:$day Period:$period</div>\n};
		}
		# find total enrollment for this backed period
		my $totalenrol;

	    }
	}
    } # end of subjsec loop


    my $bref = \%backed;
    return $bref; # ref to the hash.

}

