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

#  This file is part of Open Admin for Schools.

# Find students with 'collisions' where they are scheduled into more
# than one course in the same day and period.

# 1. Find all courses in the schedat table and their terms. (schedule)
# %course{term}{course} = 1;

# 2. Find all students in those courses. student{studnum}{term}{course};

# 3. Loop through each student, for each term, get their courses, and populate the timetable.
# %timetable{$period}{$day} = @course codes.



use DBI;
use CGI;

my %lex = ('Main' => 'Main',
	   'Timetable' => 'Timetable',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   
	);

my $self = "ttCollision.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);

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


# print page header
my $title = qq{Find Student Timetable Collisions};
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();

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



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

    # Get Terms and Grades to check for.

    my $grades; # grades with subject attendance
    foreach my $gr ( sort {$a <=> $b} keys %g_AttendanceEntryMethod ) {
#	print qq{GR:$gr Method:$g_AttendanceEntryMethod{$gr}<br>\n};
	if ($g_AttendanceEntryMethod{$gr} eq 'subject' ) {
	    $grades .= qq{$gr };
	}
    }
    

    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="border:1px solid gray;margin:1em;padding:0.4em;">\n};

    # Grades
    print qq{<tr><td class="bra">Grades to check</td>};
    print qq{<td><input type="text" name="grades" style="width:12ch;" value="$grades"> \n};
    print qq{Separate with Spaces</td></tr>\n};

    # Terms
    print qq{<tr><td class="bra">Terms to check</td>\n};
    print qq{<td><input type="text" name="terms" style="width:12ch;"> };
    print qq{Separate with Spaces</td></tr>\n};

    # Continue
    print qq{<tr><td class="la" colspan="2">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
    
    print qq{</table></form>\n</body></html>\n};

    exit;
}


#-----------------
sub findCollisions {
#-----------------

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

    # Check for missing values
    if ( not $arr{grades} or not $arr{terms} ) {
	print qq{<h3>Error: Missing Grades or Terms</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

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

    print qq{<h3 style="margin-left:1em;">Grades - @grades<br>Terms - @terms</h3>\n};

    
    
    # find courses in each grade and their terms in preparation
    my %coursegrade; # grade,term,subjsec;
    my $sth = $dbh->prepare("select description, subjsec,startrptperiod,endrptperiod from subject
			    where grade = ?");

    foreach my $grade ( @grades ) {
	$sth->execute($grade);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $description, $subjsec, $startterm,$endterm ) = $sth->fetchrow ){
	    foreach my $trm ( $startterm..$endterm ) {
		$coursegrade{$grade}{$trm}{$subjsec} = $description;
	    }
	}
    }
    # we now have course master information about all grades of interest.

    

    
    my %studname; 
    my %course; # course{term}{subjsec}
    my %coursename;
    my %student; # studnum{studnum}{term}{course}

    
    # find courses in each grade and their terms in preparation
    my %coursegrade; # grade,term,subjsec;
    my $sth = $dbh->prepare("select description, subjsec,startrptperiod,endrptperiod from subject
			    where grade = ?");

    foreach my $grade ( @grades ) {
	$sth->execute($grade);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $description, $subjsec, $startterm,$endterm ) = $sth->fetchrow ){
	    foreach my $trm ( $startterm..$endterm ) {
		$coursegrade{$grade}{$trm}{$subjsec} = $description;
	    }
	    $coursename{$subjsec} = $description;
	}
    }
    # we now have course master information about all grades of interest.


=head    
    # Get the courses and term in schedule table (schedat)
#    my $sth = $dbh->prepare("select distinct subjsec, term from schedat");
    my $sth = $dbh->prepare("select distinct subjsec,startrptperiod, endrptperiod
       from subject where grade > 6");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($subjsec,$sterm,$eterm) = $sth->fetchrow ) {
	foreach my $trm ($sterm..$eterm) {
	    $course{$trm}{$subjsec} = 1;
	}
    }

    # test courses hash
#    foreach my $trm ( sort keys %course ) {
#	foreach my $subjsec ( sort keys %{$course{$trm}} ) {
#	    print "<div>Term:Trm SUB:$subjsec</div>\n";
#	}
#    }

    
    
    # Get the course names;
    my $sth = $dbh->prepare("select description from subject where subjsec = ?");  # was sasked_courses
    foreach my $t ( keys %course ) {
	foreach my $subjsec ( sort keys %{ $course{$t}} ) {
	    my ($code,$section) = split('-', $subjsec);
	    $sth->execute( $subjsec );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $title = $sth->fetchrow;
	    $coursename{$subjsec} = $title;
	    if ( not $title ) {
		print "TITLE:$title Sub:$subjsec<br>\n";
	    }
	}
    }
=cut
    
    # Get students in those courses, by term.
    my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ? and term = ?");
    foreach my $grade ( sort keys %coursegrade ) {
	foreach my $term ( sort keys %{$coursegrade{$grade}}  ) {
	    foreach my $subjsec ( keys %{ $coursegrade{$grade}{$term}} ) {
		
		$sth->execute( $subjsec,$term );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		while ( my $studnum = $sth->fetchrow ) {
		    $student{$studnum}{$term}{$subjsec} = 1;
		    #		print "T:$term S:$subjsec SN:$studnum\n";
		}
	    }
	}
    }

    # Get student name info, populate %sort
    my %sort;
    my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from studentall
       	      		     where studnum = ?");
    # Create %sort hash; populate %studname
    foreach my $studnum ( keys %student ) {
	
	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($ln,$fn, $hr, $gr) = $sth->fetchrow;
	$sort{"$gr$ln$fn$studnum"} = $studnum;

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

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

    
    # Loop over all students, looking for collisions.
    my $sth = $dbh->prepare("select period,day from schedat where term = ? and subjsec = ?");
    my $sth1 = $dbh->prepare("select subjsec from schedat where term = ? and day = ? and period = ?");
    my $sth2 = $dbh->prepare("select description from subject where subjsec = ?");
    my $nocollisions = 1;
    
    foreach my $key (sort keys %sort ) {
	my $studnum = $sort{$key};
	my $first = 1;
	
	foreach my $term ( sort keys %{ $student{$studnum}} ) {

	    # clear timetable hash. - only for 1 student for 1 term.
	    foreach (keys %timetable) {
		delete $timetable{$_};
	    }
	    
	    foreach my $subjsec ( keys %{ $student{$studnum}{$term}} ) {
		# add this course to the timetable of this student.
		$sth->execute( $term, $subjsec );
		if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

		while ( my ( $period,$day ) = $sth->fetchrow ) {
		    push @{ $timetable{$period}{$day} }, $subjsec;
		    # print qq{P:$period D:$day CRS:$subjsec\n};
		}
	    }
	    # Timetable now fully populated for this student, this term.

	    
	    # Check for an array bigger than 1 for this course.
	    foreach my $period ( sort keys %timetable ) {
		foreach my $day ( sort keys %{ $timetable{$period}} ) {
		    my $val = scalar @{ $timetable{$period}{$day}};
#		    print @{ $timetable{$period}{$day}}, qq{ CRS/\n};
		    # print qq{VAL:$val /T:$term /SN:$studnum\n};
		    if ( $val > 1 ) { 
			# start the table,

			
			if ( $first ) {
			    print qq{<table cellpadding="4" cellspacing="0" border="1" };
			    print qq{style="margin:1em;">\n};
			    print qq{<tr><th>Name/Grade</th><th>Term</th><th>Day</th><th>Period</th>};
			    print qq{<th>Courses</th></tr>\n};
			    $first = 0;
			    $nocollisions = 0;
			}

			print qq{<tr><td>$studname{$studnum} ($studnum)</td><td class="cn">$term</td>};
			print qq{<td class="cn">$day</td><td class="cn">$period</td>};
			print qq{<td>};
			foreach my $crs ( @{$timetable{$period}{$day}} ) {
			    
			    if ( not $coursename{$crs} ) { # get the title if course not scheduled.
				$sth2->execute($crs); 
				if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
				my $title = $sth2->fetchrow;
				$coursename{$crs} = qq{$title <span style="font-weight:bold;">UNSCH</span>};
			    }
			    
			    print qq{$coursename{$crs} ($crs)<br>};
			}
			print qq{</td></tr>\n};
			
		    }

		}
	    }
	    
	} # $end of this term;
	if ( not $first ) {
	    print qq{</table>\n\n};
	}	    
	
    } # end of this student;

    if ( $nocollisions ) {
	print qq{<h3>No Collisions Found (Same Student, Same Period, 2 or more Courses)</h3>\n};
    }

    
    print qq{</body></html>\n};
    exit;
    
} # end of findCollisions
