#!/usr/bin/perl
#  Copyright 2001-2019 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 = ('Main' => 'Main',
	   'Timetable' => 'Timetable',
	   'Edit' => 'Edit',
	   'Terms' => 'Terms',
	   'Update' => 'Update',
	   'Term' => 'Term',
	   'Day' => 'Day',
	   'Per' => 'Per',
	   'Subject-Section' => 'Subject-Section',
	   'Period' => 'Period',
	   'Record' => 'Record',
	   'Backings' => 'Backings',
	   'Add' => 'Add',
	   'Additional' => 'Additional',
	   'Adding' => 'Adding',
	   'Missing' => 'Missing',
	   'Continue' => 'Continue',
	   'Delete' => 'Delete',
	   'Contact' => 'Contact',
	   'Separate With Spaces' => 'Separate With Spaces',
	   'Error' => 'Error',
	   'Record(s) Stored' => 'Record(s) Stored',
	   'Staff' => 'Staff',
	   'Not Found' => 'Not Found',
	   'Update' => 'Update',
	   'Configuration' => 'Configuration',
	   'Record Exists' => 'Record Exists',
	   'Course' => 'Course',
	   'Skipping' => 'Skipping',


	   );

# max number of backed classes per cycle. 
#(control display and entry)
my $maxbackings = 8; 

use DBI;
use CGI;

my $self = "schedit.pl";


my @time = localtime(time);
my $year = $time[5] + 1900;
my $month = $time[4] + 1;
my $currdate = "$year-$month-$time[3]";

# Set prepath for config file:
my $prepath = '../..';
eval require "$prepath/etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


my $q = new CGI;
my %arr = $q->Vars;

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

print $q->header( -charset, $charset );


# print page header.
my $title = "$lex{Edit} $lex{Timetable}";

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

print qq{[ <a href="$homepage">$lex{Main}</a> | <a href="$schpage">$lex{Timetable}</a> ]\n};

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


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

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

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

} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    printBackingsForm( $arr{userid}, $arr{terms} );

}  elsif ( $arr{page} == 4 ) {
    delete $arr{page};
    addBackings();
}


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

    # Load terms from schedat;
    my @terms;
    my $sth = $dbh->prepare("select distinct term from schedat
      where term != '' order by term");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $trm = $sth->fetchrow ){	push @terms, $trm; }

    # recover terms from subject if not any terms in schedat
    if ( not @terms ) {
	$sth = $dbh->prepare("select min(startrptperiod) from subject");
        $sth->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $startterm = $sth->fetchrow;

	$sth = $dbh->prepare("select max(endrptperiod) from subject");
        $sth->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $endterm = $sth->fetchrow;

	foreach my $trm ( $startterm .. $endterm ) {
	    push @terms, $trm;
	}
    }


    # Load Courses
    my %courses; # courses{subjsec} = teacher (ie. userid)
    $sth = $dbh->prepare("select * from subject");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	$courses{$r{subjsec}} = $r{teacher};
    }


    # Load Timetable
    my %teachers;
    $sth = $dbh->prepare("select distinct subjsec from schedat");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $subjsec = $sth->fetchrow ) {
	my $userid = $courses{$subjsec};
	if ( not $userid ) { next; } # skip any blanks
	$teachers{$userid} = 1;
    }

    if ( not %teachers ) { # no timetable entries
	print qq{<h3>No Timetable Found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    
    
    # Get Teachers Name
    $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    
    my (%sort, %teachername);
    foreach my $userid ( keys %teachers ) {
	$sth->execute($userid);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;

	$sort{"$lastname$firstname$userid"} = $userid;
	$teachername{$userid} = "$lastname, $firstname ($userid)";
    }


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

    print qq{<div style="padding:0.4em;">$lex{Staff} };
    print qq{ <select name="staff"><option></option>\n};
    foreach my $key ( sort keys %sort ) {
	my $userid = $sort{$key};
	my $name = $teachername{$userid};
	print qq{<option value="$userid">$name</option>\n}; 
    }
    print qq{</select></div>\n};


    print qq{<div style="padding:0.4em;">$lex{Term} };
    print qq{<select name="term"><option></option>\n};
    foreach my $trm (@terms){
	print qq{<option>$trm</option>\n};
    }
    print qq{</select></div>\n};


    print qq{<p>A <b>term</b> is a period of time with tests at the end.</p>\n};

    print qq{<p style="width:40%;">The terms are the
    periods of time these entries in a timetable are valid. In a K-6
    school with 3 terms for the year, the answer is all 3 terms. In a
    high school with 2 semesters and 2 terms in each semester, the
    first semester timetable entries would be for term 1 and 2 while
    the second semester would be term 3 and 4.</p>\n};

    print qq{<p><input type="submit" value="$lex{Continue}"></p>};
    print qq{</form>\n};

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

    exit;

} # end of showStartPage



#---------------
sub setTimetable {
#---------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # Passed: staff userid and term.

    # check for required values.
    if ( not $arr{staff} or not $arr{term} ) {
	print qq{<h3>$lex{Staff}/$lex{Terms} - $lex{'Not Found'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    # Get Name
    my $userid = $arr{staff};
    $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    $sth->execute( $userid );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($lastname, $firstname ) = $sth->fetchrow;


    # Load Courses for a teacher.
    $sth = $dbh->prepare("select * from subject where teacher = ?");
    $sth->execute( $userid );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    
    # Find subjects and put into hash for selection. 
    my %courses;
    while ( my $ref = $sth->fetchrow_hashref ){
	my %r = %$ref;

	if ( $arr{term}  < $r{startrptperiod} or $arr{term} > $r{endrptperiod} ) {
	    next;
	}
	
	# use truncated longer description if no small description.
	if (not $r{smdesc} ){ $r{smdesc} = substr($r{description},0,8);} 
	$courses{$r{subjsec}} = "$r{smdesc} ($r{subjsec}) T$r{startrptperiod}-$r{endrptperiod}";
    }

    # Find maximum number of periods for table from schedat;
    $sth = $dbh->prepare("select max(period) from schedat");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $maxPPD = $sth->fetchrow;


    # Print Form Start
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="userid" value="$userid">\n};
    print qq{<input type="hidden" name="currterm" value="$arr{term}">\n};


    # print top section above table: Name and Term(s)
    print qq{<h3>$firstname $lastname - $lex{Term} $arr{term}</h3>\n};
    print qq{<p>$lex{Update} $lex{Additional} $lex{Terms} \n};
    print qq{<input type="text" name="terms" size="4">\n};
    print qq{ $lex{'Separate With Spaces'}</p>\n};

    print qq{<input type="submit" value="$lex{Update}">\n};
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};

    if ( not $g_DaysPerCycle ) {
	print qq{<h3>g_DaysPerCycle $lex{'Not Found'}</h3>\n};
	print qq{<h3 style="color:red;">$lex{Update} $lex{Configuration}</h3>\n};
	print qq{</body></html>\n};
	exit;

    }

    my $days = $g_DaysPerCycle; # One for each Day.
    if ( $days eq 'w' or $days eq 'W' ) { $days = 5; } # weekly schedule.

    # print qq{Days:$days - $g_DaysPerCycle<br>\n};

    # Print Header
    print qq{<tr><th></th>};
    for my $d ( 1 .. $days ) { print qq{<th>$lex{Day} $d</th>}; }
    print qq{</tr>\n};

    for my $ppd ( 1..$maxPPD ){
	print qq{<tr>};
	for (0..$days){
	    if ($_ == 0){ 
		print qq{<td><b>$lex{Per} $ppd</b></td>};

	    } else {
		print qq{<td>};
		my $id = "$ppd:$_";
		prSelections($id, $arr{term}, \%courses );
		print qq{</td>};
	    }
	}
	print qq{</tr>\n};
    }

    my $span = $days + 1;
    print qq{</table><input type="submit" value="$lex{Update}">\n};
    print qq{</form></body></html>\n};

    exit;

} # end of setTimetable




#---------------
sub prSelections {  # print selection list for subject array
#---------------

    my ($pid, $term, $ref ) = @_;

    my %courses = %$ref;
    my ($period,$day) = split(':', $pid );
    my $found;

    # Remove courses not offered this term;


    
    my %sort = reverse %courses;
    
    # find existing values for this period...
    $sth = $dbh->prepare("select id, subjsec from schedat 
       where day = ? and period = ? and term = ?");
    $sth->execute($day,$period,$term);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($id, $subjsec) = $sth->fetchrow ) {
	if ( $courses{$subjsec} ) { # this is a subject that teacher teaches..
	    $found = 1;
	    print qq{<select name="$pid:$id">\n<option value="$subjsec">$courses{$subjsec}</option>\n};
	    foreach my $key ( sort keys %sort ){
		my $subjsec = $sort{$key};
		print qq{<option value="$subjsec">$courses{$subjsec}</option>\n};
	    }
	    print qq{<option></option></select>\n\n};

	}
    }

    
    if ( not $found ) { # no subjects found... (no teacher subjects in this period)
	print qq{<select name="$pid:-1"><option></option>\n};
	foreach my $key ( sort keys %sort ){
	    my $subjsec = $sort{$key};
	    print qq{<option value="$subjsec">$courses{$subjsec}</option>};
	}
	print qq{<option></option></select>\n};
    }

}


#----------------
sub updateRecords { # update the records in schedat table.
#----------------

    # passed: currterm, terms
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    if ( not $arr{currterm} ) { 
	print qq{<h3>$lex{Terms} $lex{'Not Found'}</h3>\n};
	print qq{</body></html>\n};
	exit; 
    }

    my $currterm = $arr{currterm};
    delete $arr{currterm};

    my @terms = split(/\s+/, $arr{terms} ); # split additional terms into array; currterm still def'd.
    my $terms = $arr{terms};
    delete $arr{terms};


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

    # Setup for finding grade(s) of each subject (if any).
    my $sth = $dbh->prepare("select grade from subject where subjsec = ?");
    my $sth1 = $dbh->prepare("select subjsec from schedat where id = ?");

    foreach my $key ( sort keys %arr ) {

	my ( $period, $day, $id ) = split(/:/, $key);

	my  $subjsec = $arr{$key};

	my $origsubjsec;
	if ( $id != -1 ) { # id exists
	    $sth1->execute($id);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    $origsubjsec = $sth1->fetchrow;
	}

	# print qq{ Subj:$subjsec Orig:$origsubjsec Day:$day Period:$period };
	# print qq{Id:$id Currtrm:$currterm<br>\n};

	if ( $id == -1 and $subjsec ) { # Add record since no id, but subjsec exists

	    print qq{<b>$lex{Record} $lex{Add}: $subjsec</b> };
	    print qq{$lex{Term}: $currterm \n};
	    print qq{$lex{Day}: $day \n};
	    print qq{$lex{Period}: $period<br>\n};

	    my $sth2 = $dbh->prepare("insert into schedat ( day,period,subjsec,term )
              values (?,?,?,?)");
	    $sth2->execute($day, $period, $subjsec, $currterm);
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	    
	} elsif ( $id != -1 and not $subjsec ) {  # Delete since id exists but no subject 

	    print qq{<b>$lex{Record} $lex{Delete}: $origsubjsec</b> };
	    print qq{$lex{Term}:$currterm \n};
	    print qq{$lex{Day}:$day \n};
	    print qq{$lex{Period}:$period<br>\n};

	    my $sth2 = $dbh->prepare("delete from schedat where id = ?");
	    $sth2->execute( $id );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	} elsif ( $id != -1 and $subjsec and $origsubjsec ne $subjsec ) { # changed record, update

	    print qq{<b>$lex{Record} $lex{Update}: $subjsec</b> };
	    print qq{$lex{Term}: $currterm \n};
	    print qq{$lex{Day}: $day \n};
	    print qq{$lex{Period}: $period<br>\n};

	    my $sth2 = $dbh->prepare("update schedat set subjsec = ? where id = ? ");
	    $sth2->execute( $subjsec, $id );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	}
	# End of Current Term; now update other terms.


	my $sth3 = $dbh->prepare("select id from schedat
          where subjsec = ? and day = ? and period = ? and term = ?");

	# now do rest of terms 
	foreach my $trm ( @terms ) {
	    
	    # Look for original subjsec and new subjsec in current term, day, period.
	    $sth3->execute($origsubjsec, $day, $period, $trm);
	    if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $oldid = $sth3->fetchrow;

	    # A check to see if this subjsec is already in this time
	    # slot. Not required with current edit, since this would
	    # display, but not with other terms.
	    $sth3->execute($subjsec, $day, $period, $trm);
	    if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $newid = $sth3->fetchrow;

	    # print qq{Subj: $subjsec Orig: $origsubjsec Day: $day Period: $period };
	    # print qq{Trm: $trm NewId: $newid OldID: $oldid<br>\n};

	    if ( not $subjsec and $oldid ) { # Delete if orig exists, but subjsec is not.

		print qq{<b>$lex{Record} $lex{Delete}: $origsubjsec</b> };
		print qq{$lex{Term}: $trm \n};
		print qq{$lex{Day}: $day \n};
		print qq{$lex{Period}: $period<br>\n};

		my $sth2 = $dbh->prepare("delete from schedat where id = ?");
		$sth2->execute( $oldid );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	    } elsif ( $subjsec and $oldid and $subjsec ne $origsubjsec ) { # Modify: both, but subjsec changed

		print qq{<b>$lex{Record} $lex{Update}: $subjsec</b> };
		print qq{$lex{Term}: $trm \n};
		print qq{$lex{Day}: $day \n};
		print qq{$lex{Period}: $period<br>\n};

		my $sth2 = $dbh->prepare("update schedat set subjsec = ? where id = ? ");
		$sth2->execute($subjsec, $oldid);
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	    } elsif ( not $newid and $subjsec ) { # Add if not currently existing, and subjsec 

		print qq{<b>$lex{Record} $lex{Add}: $subjsec</b> };
		print qq{$lex{Term}: $trm \n};
		print qq{$lex{Day}: $day \n};
		print qq{$lex{Period}": $period<br>\n};

		my $sth2 = $dbh->prepare("insert into schedat ( day,period,subjsec,term )
                  values (?,?,?,?)");
		$sth2->execute($day, $period, $subjsec, $trm);
		if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

	    }

	} # End of Term Loop

    } # End of keys loop.


    if ( not $DBI::errstr ) {
	print qq{<h3>$lex{'Record(s) Stored'}.</h3>\n};
    } else {
	print qq{<h3>$lex{Error}: $DBI::errstr<br>\n};
	print qq{$lex{Contact} <a href="mailto:$adminemail">$adminemail</a></h3>\n};

    }

    print qq{[ <form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<input type="hidden" name="terms" value="$currterm $terms">\n};

    if ( $userid ) {
	print qq{<input type="hidden" name="userid" value="$userid">\n};
    }

    print qq{<input type="submit" value="$lex{Add} $lex{Backings}">\n};
    print qq{</form>\n};

    print qq{ | <a href="$schpage">$lex{Timetable}</a> ] \n};
    print qq{</body></html>\n};

} # end of updateRecords



#--------------------
sub printBackingsForm {
#--------------------

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

    # my $userid (already defined at top of script)
    my $terms = $arr{terms};
    $terms =~ s/^\s+//;
    $terms =~ s/\s+$//;
    my @terms = split(/\s+/, $terms );

    # Get Teacher Name
    my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    $sth->execute($userid);
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($lastname, $firstname) = $sth->fetchrow;

    # Load Subject-Sections for teacher.
    my $sth = $dbh->prepare("select description, smdesc, subjsec, startrptperiod, endrptperiod, grade from subject
     where teacher = ?");
    $sth->execute( $userid );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

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

    my @subjects;
    while (my ($description,$smdesc, $subjsec, $startterm, $endterm, $grade) = $sth->fetchrow){

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

	# use truncated longer description if no small description. 
	if (not $smdesc){ $smdesc = substr($description,0,8);}

	$subjects{$subjsec} = "$smdesc ($subjsec) T:$startterm-$endterm E:$ecount";
    }


    print qq{<b>$firstname $lastname</b> ($userid) $lex{Terms} $terms\n};

    print qq{<form action="$self" method="post" style="padding:1em;">\n};
    print qq{<input type="hidden" name="page" value="4">\n};
    print qq{<input type="hidden" name="terms" value="$terms">\n};

    #print qq{<p><b>$lex{Add} $lex{Backings}</b></p>\n};
    print qq{<input type="submit" value="$lex{Add} $lex{Backings}">\n};
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{'Subject-Section'}</th><th>$lex{Day}</th>};
    print qq{<th>$lex{Period}</th></tr>\n};

    for my $rec (1..$maxbackings){
	print qq{<tr><td><select name="$rec"><option></option>\n};
	foreach my $subjsec (sort keys  %subjects) {
	    print qq{<option value="$subjsec">$subjects{$subjsec}</option>};
	}
	print qq{</select></td>\n};
	print qq{<td><input type="text" name="d$rec" size="6"></td>\n};
	print qq{<td><input type="text" name="p$rec" size="6"></td>\n};
	print qq{</tr>\n};
    }
    print qq{</table>};
    print qq{<input type="submit" value="$lex{Add} $lex{Backings}">\n};
    print qq{</form></body></html>\n};

}


#--------------
sub addBackings {
#--------------

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

    my $terms = $arr{terms};
    my @terms = split(/\s+/, $arr{terms});
    delete $arr{terms};

    print qq{<h1>$lex{Add} $lex{Backings}</h1>\n};

    if ( not $terms ) { 
	print qq{<h3>$lex{Terms} $lex{'Not Found'}</h3>\n};;
	print qq{</body></html>\n};
	exit;
    }

    my $sth = $dbh->prepare("select description from subject where subjsec = ?");
    my $sth1 = $dbh->prepare("select count(*) from schedat where day = ? and 
      period = ? and subjsec = ? and term = ?");
    my $sth2 = $dbh->prepare("insert into schedat ( day,period,subjsec,term ) values (?,?,?,?)");


KEY:
    foreach my $key (sort keys %arr ) {

	if ( $key =~ m/^(\d)/ and $arr{$key} ) {

	    #print qq{K:$key V:$arr{$key}<br>\n};

	    my $di = "d$1"; 
	    my $pi = "p$1";

	    if ( not $arr{$di} or not $arr{$pi} ) {
		print qq{$lex{Missing} $lex{Period}/$lex{Day}<br>\n};
		exit;
	    }

	    my $subjsec  = $arr{$key};

	    $sth->execute($subjsec);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $description = $sth->fetchrow;

	    print qq{$lex{'Adding'} $description ($subjsec) - $lex{Day} $arr{$di} };
	    print qq{ - $lex{Period} $arr{$pi}<br>\n};

	    foreach my $trm ( @terms ) {

		# Check for existing values
		$sth1->execute($arr{$di}, $arr{$pi}, $subjsec, $trm);
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $count = $sth1->fetchrow;

		if ( $count ) {
		    print qq{<div>$lex{'Record Exists'}: $lex{Day}:$arr{$di} $lex{Per}:$arr{$pi} };
		    print qq{$lex{Course}:$subjsec $lex{Term}:$trm};
		    print qq{ <b>$lex{Skipping}</div>\n};

		} else { # insert the record
		    $sth2->execute($arr{$di}, $arr{$pi}, $subjsec, $trm);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		}
	    }

	} # End of IF 

    } # End of foreach key loop

    print qq{<p>[ <a href="$schpage">$lex{Timetable}</a> ]</p>\n};
    print qq{</body></html>\n};

} # End of addBackings
