#! /usr/bin/perl 
#  Copyright 2001-2024 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.


use DBI;
use CGI;

my %lex = ('Student' => 'Student',
	   'Main' => 'Main',
	   'Timetables' => 'Timetables',
	   'Timetable' => 'Timetable',
	   'Cannot open tex file' => 'Cannot open tex file',
	   'View/Download' => 'View/Download',
	   'View Log File' => 'View Log File',
	   'Select' => 'Select',
	   'Lastname, Firstname' => 'Lastname, Firstname',
	   'Homeroom, Lastname, Firstname' => 'Homeroom, Lastname, Firstname',
	   'Grade, Lastname, Firstname' => 'Grade, Lastname, Firstname',
	   'Band, Lastname, Firstname' => 'Band, Lastname, Firstname',
	   'Sort by' => 'Sort by',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'No Staff Found' => 'No Staff Found',
	   'Error' => 'Error',
	   'Blank=All' => 'Blank=All',
	   'Term' => 'Term',
	   'Period' => 'Period',
	   'Day' => 'Day',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Terms' => 'Terms',
	   'Missing' => 'Missing',
	   'Band' => 'Band',
	   'Student Number' => 'Student Number',
	   
	);

my $self = "rptStudentTtb.pl";


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


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

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


# print page header
my $title = qq{$lex{Student} $lex{Timetable} Report 1};
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="$schpage">$lex{Timetable}</a> ]\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();  # main options
    
} elsif ($arr{page} == 1 ) {
    delete $arr{page};
    selectStudents(); # students to print.

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


#-----------------
sub selectStudents {
#-----------------

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

    if ( not $arr{term} ) {
	print qq{<h3>No Terms</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    
    my @terms = split(/\s+/, $arr{term});
    print qq{<h3>Terms:@terms</h3>\n};

    
    my $checked;
    if ( $arr{checknextpage} ) {
	$checked = qq{checked="checked"};
	delete $arr{checknextpage};
    }
    
    my ($grouptype, $groupval) = ($arr{grouptype}, $arr{groupval} );
    delete $arr{grouptype};
    delete $arr{groupval};
    if ( not $groupval ) {
	print qq{<h3>No Students Selected</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    # %arr values left are passed on

    # Set Sort Order
    my $sortorder;
    if ( $arr{sortorder} eq 'name' ) {
	$sortorder = "order by lastname, firstname";
	
    } elsif ( $arr{sortorder} eq 'homeroom' ) {
	$sortorder = "order by homeroom, lastname, firstname";
	
    } else { # must be grade
	$sortorder = "order by grade, lastname, firstname";
    }
	    
    
    # Start the form, pass values
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    foreach my $key ( keys %arr ) {
	print qq{<input type="hidden" name="$key" value="$arr{$key}">\n};
    }

    print qq{<p><input type="submit" value="$lex{Continue}"></p>\n};
    
        
    # Passed: grouptype (ie. Grade,Homeroom) and groupval is the value.
    my $select;
    if ( $grouptype eq $lex{Grade} ) { # Find this grade;
	$select = "where grade = ?";
    } else {
	$select = "where homeroom = ?";
    }

    my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, studnum
       from student $select $sortorder");
    my @groups = split(/\s+/, $groupval);

    my $first = 1;
    
    foreach my $grp ( @groups ) {
	$sth->execute( $grp );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;

	    if ( $first ) {
		print qq{<table cellspacing="0" border="1" cellpadding="3" };
		print qq{style="margin-bottom:1em;">\n};
		print qq{<colgroup><col class="la"><col class="cn"><col style="text-align:center;">};
		print qq{</colgroup>\n};
		print qq{<tr><th>Student</th><th>Grade</th><th>Homeroom</th></tr>\n};
		
		$first = 0;
	    }

	    print qq{<tr><td><input type="checkbox" name="$r{studnum}" value="1" $checked> };
	    print qq{<b>$r{lastname}</b>, $r{firstname} ($r{studnum})</td><td>$r{grade}</td>};
	    print qq{<td>$r{homeroom}</td></tr>\n};
    
	}
    }

    if ( $first ) {
	print qq{<h3>No Students Found!</h3>\n};
    } else {
	print qq{</table>\n};
    }

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

} 
    



#-------------
sub showReport {
#-------------

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

    # Outline:
    # Get terms and students in the grade/homeroom.
    # Get total courses for this group and those with no course enrollments.
    # Get courses by term
    
    
    if ( not $arr{term} ) {
	print qq{<h3>No Terms</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    
    my @terms = split(/\s+/, $arr{term});
    delete $arr{term};
    print qq{<h3>Terms:@terms</h3>\n};

    my @students;
    foreach my $key ( keys %arr ) { # student numbers.
	if ( $key =~ /\d/ ) {
	    push @students, $key;
	}
    }

    my %studname; 
    my %nocourse;  # no course enrollments
    my %notimetable; # no timetable entries, though enrolled in courses.
    my %course; # course{subjsec}
    my %courseByTerm; # {term}{subjsec}
    my %coursename;

    
    # Get student names for these student numbers; %studname
    my (%sort, @sort);  # these will be all students even those without course/ttb
    my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from studentall
       where studnum  = ?");

    # Create %sort hash; populate %studname
    foreach my $studnum ( @students ) {
	
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($ln,$fn, $hr, $gr) = $sth->fetchrow;

	if ( $arr{sortorder} eq 'name' ) {
	    $sort{"$ln$fn$studnum"} = $studnum;
	    
	} elsif ( $arr{sortorder} eq 'homeroom' ) {
	    $sort{"$hr$ln$fn$studnum"} = $studnum;

	} else { # grade, lastname, firstname, studnum
	    $sort{"$gr$ln$fn$studnum"} = $studnum;
	}

	$studname{$studnum} = qq{$fn $ln (Gr $gr/Hr $hr)};
    }

    # test sort.
    #foreach my $key ( sort keys %sort ) {
    # print "K:$key VAL:$sort{$key}<br>\n";
    #}
    
    # Get the course enrollments for each student, in order to get an overall list.
    my $sth = $dbh->prepare("select distinct subjcode from eval where studnum  = ?");
    foreach my $studnum ( @students ) {
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ecount = 0;
	while ( my $subjsec = $sth->fetchrow ) {
	    $course{$subjsec} = 1;
	    $ecount++;
	}
	if ( $ecount < 1 ) {
	    $nocourse{$studnum} = 1
	}
    }
    
    # we now have %course populated with all courses to check, and also have a %nocourse listing
    if ( %nocourse ) {
	print qq{<div style="font-weight:bold;">No Course Enrollments</div>\n};
	foreach my $studnum ( sort keys %nocourse ) {
	    print qq{$studname{$studnum} ($studnum)<br>\n};
	}
    }

       
    # create a courseByTerm hash %term and %subjsec; loop over %course
    my $sth = $dbh->prepare("select description, startrptperiod, endrptperiod from subject
              		     where subjsec = ?");
    foreach my $subjsec ( keys %course ) {
	$sth->execute( $subjsec );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($desc,$startterm,$endterm) = $sth->fetchrow;
	$coursename{$subjsec} = $desc;
	foreach my $term ($startterm..$endterm ) {
	    $courseByTerm{$term}{$subjsec} = $desc;
	}
    }

    foreach my $term ( @terms ) { # sort keys %courseByTerm ) { # these may be outside passed terms.
	foreach my $subjsec ( sort keys %{ $courseByTerm{$term}} ) {
#	    print qq{T:$term SUB:$subjsec<br>\n};
	}
    }

    print qq{<br>\n};

    
    my $sth = $dbh->prepare("select distinct subjcode from eval where term = ? and studnum = ?");
    my $sth1 = $dbh->prepare("select day,period from schedat where term = ? and subjsec = ?");

    
    foreach my $studnum ( @students ) {
	if ( $nocourse{$studnum} ) { next; }
	
	foreach my $term ( @terms ) {
	    my %ttb; # timetable for this term, this student
	    
	    # Loop over student courses for this term.
	    $sth->execute( $term, $studnum );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $subjsec = $sth->fetchrow ) {
		# now find timetable entries for this course, this term.
		$sth1->execute( $term, $subjsec );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		while ( my ($day,$period)  = $sth1->fetchrow ) {
		    $ttb{$day}{$period}{$subjsec} = 1;
		}
	    }
#	    print qq{<br>SN:$studnum Term:$term<br>\n};
	    if ( not %ttb ) {
		$notimetable{$studnum} = 1;
#		print "No Timetable for term $term $studname{$studnum} ($studnum)<br>\n";
	    } else {
		foreach my $day ( keys %ttb ) {
		    foreach my $period ( sort keys %{ $ttb{$day}} ) {
			foreach my $subjsec ( sort keys %{ $ttb{$day}{$period}} ) {
#			    print qq{D:$day Per:$period Sub:$subjsec<br>\n};
			}
		    }
		}
	    }
	} # end of term loop
    } # end of student loop

    # Display those without a timetable.
    if ( %notimetable ) {
	print qq{<div style="font-weight:bold;">No Timetable</div>\n};
	foreach my $studnum ( sort keys %notimetable ) {
	    print qq{$studname{$studnum} ($studnum)<br>\n};
	}
    }    

    
    if ( $nocourse{$studnum} or $notimetable{$studnum} ) {
	print qq{<p style="page-break-after:always;"></p>\n};
    }
    
    
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};
	if ( $nocourse{$studnum} or $notimetable{$studnum} ) { next; }
	

	# get this student's Courses
	my %studentcourses;
	my $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ?");
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $subjsec = $sth->fetchrow ) {
	    $studentcourses{$subjsec} = 1;
	}

	
	# Setup for Get Subject Information
	my $sth2 = $dbh->prepare("select description, smdesc, location, teacher, grade
          from subject where subjsec = ?");
	my $sth3 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");

	print qq{<h2>$studname{$studnum} ($studnum)</h2>\n};

	
	foreach my $term ( @terms ) {

	    # Clean up global on later interations.
	    foreach my $key ( keys %timetable ) {
		delete $timetable{$key};
	    }
	    my %timetable; # timetable{period}{day}{subjsec} = 1
	    my $maxdays = 0;

	    my $sth = $dbh->prepare("select * from subject where subjsec = ?");
	    
	    my $sth1 = $dbh->prepare("select day, period from schedat
              where term = ? and subjsec = ? order by day, period");

	    my %periodmap; # stores the mapping values for period times;
	    
	    foreach my $subjsec ( keys %{ $courseByTerm{$term} } ) {
		if ( not $studentcourses{$subjsec} ) { next; } # doesn't take this.
		
		# Get Course Info
		$sth->execute( $subjsec ) ;
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $cref = $sth->fetchrow_hashref;
		my %c = %$cref;

		# grade from course, or should be from student?
		$periodmap{ $g_PeriodMap{$c{grade}} } = 1; # the key has the map value.
		
		# load each timetable record for this course
		$sth1->execute( $term, $subjsec ) ;
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		while ( my ( $day, $period ) = $sth1->fetchrow ) {
		    if ( $day > $maxdays ) { $maxdays = $day; }
		    push @{ $timetable{$period}{$day} }, $subjsec; # more than one course possible
		}
	    }

	    # Setup Period Times (into %periodTime{$period} = combined value.
	    my @vals = keys %periodmap;
	    my $mapvalue = pop @vals;
	    my %periodTime;
	    my %tempTime = %{ $g_PeriodTime{$mapvalue} };
	    foreach my $p ( keys %tempTime ) {
		my $start = $tempTime{$p}{s};
		my $end = $tempTime{$p}{e};
		my ($start,$end) = conv24to12($start,$end);
		my $val = qq{$start-$end};
		$periodTime{$p} = $val;
	    }

	    # print qq{<div>MapValue:$mapvalue Periods:}, %periodTime, "<br>\n";

	    
=head
	    # Testing %timetable
	    print qq{<h3>Timetable Hash</h3>\n};
	    foreach my $period (keys %timetable ) {
		foreach my $day ( keys %{ $timetable{$period} } ) {
		    print qq{Day:$day Period:$period Courses:}, @{ $timetable{$period}{$day} }, qq{<br>\n};
		}
	    }
=cut
	
#	    if ( not %timetable ) { next; } # term

	    # Start Table.
	    print qq{<table cellspacing="0" border="1" cellpadding="3" style="margin-bottom:1em;">\n};
	
	    # Print Top Row here.
	    print qq{<tr><th>$lex{Term} $term / $lex{Day}</th>\n};

	    my $dayvalue;
	    for my $day ( 1 .. $maxdays ) { 
		if ( $g_DaysPerCycle == 5 or $g_DaysPerCycle eq 'W' or $g_DaysPerCycle eq 'w' ) {
		    # weekly
		    $dayvalue = $dow[$day + 1];
		} else {
		    $dayvalue = $day;
		}
		print qq{<th>$dayvalue</th>};
	    }
	    print qq{</tr>\n};

	    my @periods = sort { $a <=> $b } keys %timetable;
	    my $maxperiods = $periods[-1];
	
	    foreach my $period ( 1 .. $maxperiods ) {

		print qq{<tr><td>$lex{Period} $period<br>$periodTime{$period}</td>};
	    
		foreach my $day ( 1 .. $maxdays ) {

		    print qq{<td>};
		    while ( my $subjsec = pop @{ $timetable{$period}{$day} } ) {

			# Load subject information.
			$sth2->execute( $subjsec );
			if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
			my $ref = $sth2->fetchrow_hashref;
			%r = %$ref;
			if ( not $r{smdesc} ) {
			    $r{smdesc} = substr( $r{description}, $maxchars ); # defined at top.
			}
			    
			# Load course enrollment
			$sth3->execute( $subjsec );
			if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
			$studcount = $sth3->fetchrow;

			# $arr{desctype} is either 'description' or 'smdesc' field
			my $coursedesc = $r{"$arr{desctype}"};
			if ( length $coursedesc > $arr{descsize} ) { # truncate.
			    $coursedesc = substr( $coursedesc, 0, $arr{descsize} );
			}
		    
			my $cellvalue = qq{<b>$coursedesc</b><br>};
			if ( $arr{includeSubjsec} ) { $cellvalue .= qq{ $subjsec }; }
			if ( $arr{includeStudcount} and $studcount ) {
			    $cellvalue .= qq{ ($studcount)};
			}
			if ( $arr{includeLocation} and $r{location} ) {
			    $cellvalue .= qq{<br>$r{location}};
			}

			print qq{$cellvalue<br>};
		    }
		    print qq{</td>};

		} # end of day loop.
		print qq{</tr>\n};
		
	    } # end of periods loop

	    print qq{</table>\n};

	} # end of Term Loop

	if ( $arr{includePageBreak} ) {
	    print qq{<p style="page-break-after:always;"></p>\n};
	}
	## new page for next teacher.
	
    } # next userid

    return @skipped;

} # end of prTimetable



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

    
    my (@homerooms, @grades );
    # Get Homerooms
    my $sth = $dbh->prepare("select distinct homeroom from student 
      where homeroom is not NULL and homeroom != ''");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $hr = $sth->fetchrow ) {
	push @homerooms, $hr;
    }
    @homerooms = sort {$a <=> $b} @homerooms;

    # Get 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; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    @grades = sort {$a <=> $b} @grades;
    
    
    # print sortorder and selection input form.
    # print qq{<h1>$lex{'Student Timetables'}</h1>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="margin-bottom:0.4em; border:1px solid gray;padding:0.3em;">\n};
    
    print qq{<tr><td class="bra">Current Grades</td>};
    print qq{<td style="la">};
    foreach my $gr ( @grades ) {
	print "$gr ";
    }
    print qq{</td></tr>\n};

    print qq{<tr><td class="bra">Current Homerooms</td>};
    print qq{<td>};
    foreach my $hr ( @homerooms ) {
	print qq{$hr };
    }
    print qq{</td></tr>\n};
    print qq{</table>\n};

    
    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" 
		 style="border:1px solid gray;padding:0.3em;">\n};
    
    print qq{<tr><td class="bra">$lex{'Sort by'}</td>\n};
    print qq{<td><select name="sortorder"><option value="name">$lex{'Lastname, Firstname'}</option>\n};
    print qq{<option value="homeroom">$lex{'Homeroom, Lastname, Firstname'}</option>\n};
    print qq{<option value="grade">$lex{'Grade, Lastname, Firstname'}</option>\n};
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Select}</td>\n};
    print qq{<td><select name="grouptype"><option>$lex{Grade}</option>\n};
    print qq{<option>$lex{Homeroom}</option>\n};
    print qq{</select>\n};
    print qq{<input type="text" name="groupval" style="width:6em;"></td></tr>\n};

    # Now do the term(s)
    print qq{<tr><td class="bra">$lex{Terms}</td><td class="bla">};
    print qq{<input type="text" name="term" size="12">\n};
    print qq{$lex{'Separate with Spaces'}</td></tr>\n};


    my $checked = qq{checked="checked"};
    print qq{<tr><td></td><td class="bla">};
    print qq{<input type="checkbox" name="includeSubjsec" value="1" $checked>};
    print qq{ Include Course Code-Section</td></tr>\n};

    print qq{<tr><td></td><td class="bla" ><input type="checkbox" name="includeLocation" value="1">};
    print qq{ Include Room # / Location</td></tr>\n};

    print qq{<tr><td></td><td class="bla" >};
    print qq{<input type="checkbox" name="checknextpage" value="1" $checked>};
    print qq{ Check Next Page</td></tr>\n};

    print qq{<tr><td></td><td class="bla" >};
    print qq{<input type="checkbox" name="includePageBreak" value="1" $checked>};
    print qq{ Add Page Break after each Student</td></tr>\n};

    
    print qq{<tr><td></td><td class="bla">};
    print qq{<select name="desctype"><option value="description">Full</option>};
    print qq{<option value="smdesc">Short</option>};
    print qq{</select> Course Description Size</td></tr>\n};

    print qq{<tr><td></td><td class="bla">};
    print qq{<input type="text" name="descsize" style="width:2em;" value="25">\n};
    print qq{ Max Description Characters (for Full Description)</td></tr>\n};
    

    print qq{<tr><td></td><td class="la">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

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

    exit;

}


#-------------
sub conv24to12 {
#-------------

    my @times;
    while ( my $time = shift ) {

#	if ( not $time ) { return }
	$time =~ s/\s+//g; # strip any spaces.
	my ($hr, $min) = split(':', $time);
	my $ampm = 'AM';
	if ( $hr == 12 ) { $ampm = 'PM'; }
	if ( $hr == 0 ) { $hr = 12; };

	if ( $hr > 12 ) { 
	    $hr -= 12;
	    $ampm = 'PM';
	}

	my $t = qq{$hr:$min$ampm};
	push @times, $t;
    }

    return @times;

}


