#!perl

BEGIN { chdir('..'); }

our $header_color = "#eeeeee";
our $non_period_color = "#eeeeee";
our $bgcolor_today = "beige";
our $bgcolor_other_days = "white";
our $cell_width = 110;
our $cell_width_banner = 60;
our $cell_width_banner_recess = 10;
our $cell_padding = 2;
our $cell_padding_banner = 1;
our $font_size_subject = +0;
our $font_size_room = -1;
our $font_size_teacher_or_form = -2;
our $font_size_banner = -2;
our $sep_color = "gray";
our $bgcolor_duty = "#ffc0c0";
our $bgcolor_recess = $header_color;

our $sep = qq{<td width="1" height="5" cellpadding="0"></td>};

use strict; use warnings;
use IO::File;
use CGI ':standard';
use CGI::Carp 'fatalsToBrowser';
require "lib_pl";
use Date;
use File::Copy;
use CGI::Cookie;

set_program_name("the timetable program");

# session variables
our $session_data = {};
our ($uid, $password, $token, $session_code, $authenticated, $ou_allowed, $user);

our $script_name;

our ($name1, $name2, $fullname);

# other vars from lib_pl
our ($program, $submit_i, $file_i, $password_i, @month, @weekday);
our ($common_conf);
our ($ldap);
our %N;

# other global vars
our ($days, $periods);

our $banner = param("banner");
if ($banner) { param("embed", 1); }

our $db;

our (%abbrev) = (
	"Physical" => "Phys",
	"Education" => "Ed",
	"Unit 1&2" => "1&2",
	"Unit 3&4" => "3&4",
	"Studies in " => "",
	"Recreation" => "Rec",
	"Technology" => "Tech",
	"MUSIC" => "Music",  # !!!
	"FOOD" => "Food",
	"Further Maths" => "Further",
	"Foundation Maths" => "Foundation",
	"General Maths" => "General",
	" (Correspondence)" => "",
	"VET Hospitality" => "Hospitality", 
	"Marketing & Publicity" => "Marketing",
	"Health & HD" => "Health",
	"Design Fabric" => "Fabric",
	"Design Wood" => "Wood",
	"Visual Design" => "Visual",
	"Legal Studies" => "Legal",
	"LIT/NUM SUPPORT" => "LIT/NUM",
);

load_session();

unless ($uid) {
	$uid = param("uid");
}

hdr("Timetable");

$user = auth();

print "<center>\n";

($name1, $name2) = ldap_get_name($user);

my $is_teacher = $user->{is_staff};
my $is_student = $user->{is_student};

$fullname = "$name1 $name2";

our $weekday = uc(Date->today->{weekday});

#test_ldap();
timetable();

ftr();


sub selectors {
	my ($students) = sql_rows("SELECT firstname, familyname FROM tblStudent WHERE deleted=0 ORDER BY familyname, firstname");
	my ($teachers) = sql_rows("SELECT firstname, familyname FROM tblTeacher WHERE deleted=0 ORDER BY familyname, firstname");
	for (@$students, @$teachers) {
		for (@$_) {
			$_ = lc($_);
			s/\b([a-z])/uc($1)/ge;
		}
		$_ = "$$_[1], $$_[0]";
	}
	space(3);
	print dropdown({prompt=>"students..."}, "student", $students);
	space(3);
	print dropdown({prompt=>"teachers..."}, "teacher", $teachers);
	space(3);
	print buttons("load");
}


sub timetable {
	sql_connect();

	my $can_select = !$is_student;

	if ($can_select and (param("teacher") || param("student"))) {
		if (param("teacher") and param("student")) {
			msg("please select either a teacher or a student - not both!");
		} elsif (param("teacher")) {
			($name2, $name1) = split /, */, param("teacher"), 2;
			$is_teacher = 1; $is_student = 0;
		} elsif (param("student")) {
			($name2, $name1) = split /, */, param("student"), 2;
			$is_teacher = 0; $is_student = 1;
		}
		$fullname = "$name1 $name2";
	}
#	elsif (!($name1 and $name2)) {
#		$name1 = ""; $name2 = "";
#		get_user_name();
#	}

	if ($banner) {
		print hidden("teacher", param("teacher")) if param("teacher");
		print hidden("student", param("student")) if param("student");
	}

	if ($can_select && !$banner) {
		print qq{<table><tr><td align="center" valign="top"><center><h2>$name1 $name2</h2></center></td>\n};
		print qq{<td valign="top">}; selectors(); print "</td>";
	}

	print qq{</tr></table>\n};

#	my ($ID) = sql_item("SELECT ID FROM tblStudent WHERE firstname='Danielle' and familyname='Hradsky'");
#	my ($subjects) = sql_col("SELECT Name FROM tblSubject, tblSubjectChoice WHERE StudentID='$ID' and tblSubjectChoice.SubjectID = tblSubject.ID");

	($days) = sql_col("SELECT Name FROM tblDay WHERE deleted=0 ORDER BY SortOrder");
	($periods) = sql_col("SELECT Name FROM tblPeriod WHERE deleted=0 ORDER BY SortOrder");

	if (!($name1 and $name2)) {
		if (!$banner) {
			msg("Your name is not in the active directory yet.")
		}
	} else {
		if ($is_student) {
			my $name1_esc = escape_quote($name1);
			my $name2_esc = escape_quote($name2);
			my ($students) = sql_rows("SELECT ID FROM tblStudent WHERE firstname='$name1_esc' and familyname='$name2_esc' and deleted=0");

			if (@$students == 0) {
				msg("We don't have timetable information for the student $fullname.");
			}
			if (@$students > 1) {
				msg("Apparently there is more than one student with the name $fullname!");
			}

			for (@$students) {
				my ($ID) = @$_;
				display_student_timetable($ID);
			}

		} elsif ($is_teacher) {
			my ($teachers) = sql_rows("SELECT ID FROM tblTeacher WHERE firstname='$name1' and familyname='$name2' and deleted=0");
			if (@$teachers == 0) {
				msg("We don't have timetable information for the teacher $fullname.");
			}
			if (@$teachers > 1) {
				msg("Apparently there is more than one teacher with the name $fullname!");
			}

			for (@$teachers) {
				my ($ID) = @$_;
				display_teacher_timetable($ID);
			}

		} else {
			if (!$banner) {
				msg("You are apparently neither a student nor a teacher, we have no timetable information for you!");
			}
		}
	}

	print "</center>\n";
}

sub escape_quote {
	my ($x) = @_;
	$x =~ s/'/''/g;
	return $x;
}

sub display_student_timetable {
	my ($ID) = @_;

	my ($timetable) = sql_rows(<<End);
SELECT ItemName1 AS subject, tblDay.Name AS day, tblPeriod.Name AS period, ItemCode3 AS room, ItemCode2 AS teacher

FROM qryTimetableStudentSub3('$ID')
JOIN tblPeriod ON qryTimetableStudentSub3.RowID = tblPeriod.ID
JOIN tblDay ON qryTimetableStudentSub3.ColID = tblDay.ID

ORDER BY tblDay.SortOrder, tblPeriod.SortOrder
End

	display_timetable($timetable);
}

our (%tt, @wd, $wday, $is_today, $bgcolor);

sub day_vars {
	$is_today = $wd[$wday] eq $weekday;
	$bgcolor = "white";
	if ($is_today) {
		$bgcolor = $bgcolor_today;
	}
}


sub display_timetable {
	my ($timetable) = @_;
	%tt = (); @wd = ();

	$wday = 0;
	for (@$days) {
		my $x = nbspify($_);
		$wd[$wday] = uc $_;
		++$wday;
	}

	for (@$timetable) {
		my ($subject, $day, $period, $room, $teacher) = @$_;
		$tt{$day}{$period} = [$subject, $room, $teacher];
	}

	if ($banner) {
		$cell_width = $cell_width_banner;
		$cell_padding = $cell_padding_banner;
		$font_size_subject = $font_size_banner;
		$font_size_room = $font_size_banner;
		$font_size_teacher_or_form = $font_size_banner;

		print "<table><tr><td>";
	}

	my $last_was_sep = 0;

	print qq{<table cellpadding="$cell_padding" cellspacing="0" bgcolor="$sep_color" border=1>};
	!$banner and print qq{<tr><td bgcolor="$header_color">&nbsp;</td>};
	$wday = 0;
	for (@$days) {
		my $x = $_;
		day_vars();
		if ($is_today) { $x = "<i>$x</i>"; } else { $bgcolor = $header_color; }
		!$banner and print qq{<th bgcolor="$bgcolor">$x</th>};
		++$wday;
	}
	!$banner and print "</tr>\n";
	$banner and print "<tr>\n";
	for my $period (@$periods) {
		my $is_class = $period =~ /period|home group/i;
		if (!$banner && $is_student && !$is_class) {
			# horizontal sep
			if (!$last_was_sep && $period !~ /^bus duty$/i) {
				print qq{<tr>$sep</tr>\n};
				$last_was_sep = 1;
			} else {
				$last_was_sep = 0;
			}
		} else {
			if (!$banner) {
				my $period_display = $is_class ? " $period " : " ";
				$period_display =~ s/PERIOD/Period/i;
				print qq{<tr><th bgcolor="$header_color">}.nbspify($period_display)."</th>\n";
			}
			$wday = 0;
			for my $day (@$days) {
				day_vars();
				if (!$banner || $is_today) {
					my $entry = $tt{$day}{$period};
					my $is_sep = $banner && !$is_class && !$entry;
					if (!$is_sep) {
						if ($banner) { $cell_width = $is_class ? $cell_width_banner : $cell_width_banner_recess; }
						if (!$is_class) { $bgcolor = $entry ? $bgcolor_duty : $bgcolor_recess; }
						print qq{<td bgcolor="$bgcolor" width="$cell_width">};
						if ($entry) {
							my ($subject, $room, $teacher_or_form) = @$entry;
							$subject = subject_abbrev($subject);

							for ($subject, $room, $teacher_or_form) { $_ = nbspify($_); }
							font_size($subject, $font_size_subject);
							font_size($room, $font_size_room);
							font_size($teacher_or_form, $font_size_teacher_or_form);
							print "$subject";
							print "<br>", left_right($room, $teacher_or_form) if $is_class;
						} else {
							print "&nbsp;";
						}
						print "</td>\n";
					}
					if ($is_sep && !$last_was_sep && $period !~ /^bus duty$/i) {
						print qq{$sep}; #<td bgcolor="$sep_color">&nbsp;</td>};
						$last_was_sep = 1;
					} else {
						$last_was_sep = 0;
					}
				}
				++$wday;
			}
			!$banner and print "</tr>\n";
		}
	}
	$banner and print "</tr>\n";
	print "</table>\n";
	if ($banner) {
		print "</td><td>";
		print qq{<input type="submit" value="&nbsp;+&nbsp;">};
		print "</td></tr></table>";
	}
}

sub font_size {
	my $size = $_[1] > 0 ? "+$_[1]" : $_[1];
	if ($size == 0) { return; }
	$size = $size == 0 ? "" : qq{ size="$_[1]"};
	$_[0] = qq{<font face="arial"$size>$_[0]</font>};
}

sub display_teacher_timetable {
	my ($ID) = @_;

# I'm not sure how to call "procedures" so I copied the SQL code here and edited it

	my ($timetable) = sql_rows(<<End);
SELECT	tblSubject.Name AS subject, tblDay.Name AS day, tblPeriod.Name AS period, tblRoom.Code AS room,
	CASE WHEN qryGeneralExpandAllocsAndBlockItems.BlockID IS NULL THEN tblForm.Code ELSE tblBlock.Code END AS form

FROM	qryGeneralExpandAllocsAndBlockItems
	LEFT JOIN tblBlock ON qryGeneralExpandAllocsAndBlockItems.BlockID = tblBlock.ID
	JOIN tblSubject ON qryGeneralExpandAllocsAndBlockItems.SubjectID = tblSubject.ID
	LEFT JOIN tblTeacher ON qryGeneralExpandAllocsAndBlockItems.TeacherID = tblTeacher.ID
	LEFT JOIN tblRoom ON qryGeneralExpandAllocsAndBlockItems.RoomID = tblRoom.ID
	LEFT JOIN tblForm ON qryGeneralExpandAllocsAndBlockItems.FormID = tblForm.ID
	JOIN tblPeriod ON qryGeneralExpandAllocsAndBlockItems.PeriodID = tblPeriod.ID
	JOIN tblDay ON qryGeneralExpandAllocsAndBlockItems.DayID = tblDay.ID

WHERE	qryGeneralExpandAllocsAndBlockItems.TeacherID = '$ID'

ORDER BY	tblDay.SortOrder, tblPeriod.SortOrder
End

	display_timetable($timetable);
}

# {0327053D-C01C-4073-A5D1-147ADAB8A733}

#use DBI;

#	my $DSN = 'driver={SQL Server};Server=goanna;database=GOANNA/HUMANEDGE;uid=SA;pwd=CITYROAD417;';
#	my $dbh = DBI->connect("dbi:ODBC:$DSN") or
#		die "$DBI::errstr\n";
#	$dbh->disconnect();


# test ldap directory

# TODO update first name / last name in active directory for students ???

#sub test_ldap {
#	my %uids;
#	my $filter = "";
#	for my $domain ('ou=users,ou=healesville-hs') {
#		my $users = ldap_lookup($domain, $filter, [$common_conf->{ldap_uid_field}]);
#		if (ref $users) {
#			for my $user (@$users) {
#				my $uid_full = lc $user->get_value($common_conf->{ldap_uid_field});
#				my $uid;
#				if (length($uid_full) > 3) {
#					$uid = substr($uid_full, 0, 3);
#				} else {
#					$uid = $uid_full;
#				}
#				if ($uids{$uid}) {
#					msg("duplicate uid (first 3 chars): $uids{$uid}, $uid_full");
#				}
#				$uids{$uid} = $uid_full;
#			}
#		} else {
#			die "bad: ldap_lookup in domain $domain returned $users";
#		}
#	}
#	d([sort keys %uids]);
#}

#sub get_user_name {
#	unless (param("name1") and param("name2")) {
#		msg("Please enter your name, to help us complete our database.");
#		msg("Please use capitals and lower-case correctly.");
#		msg("PLEASE DO NOT enter a false name, or someone else's name!");
#		print br();
#		print tbl_tsv(<<End);
#Given-name:	<input name="name1" type="text">
#Surname:	<input name="name2" type="text">
#	$submit_i
#End
#		
#		ftr();
#		exit();
#	} else {
#		$name1 = param("name1");
#		$name2 = param("name2");
#		ldap_set_fullname($user, $name1, $name2);
#	}
#}

sub subject_abbrev {
	my ($subject) = @_;
	for (keys %abbrev) { $subject =~ s/\b\Q$_\E\b/$abbrev{$_}/eg; }
	$subject =~ s/Unit 3&\$/3&4/;  # for whatever reason this doesn't work in the abbrev list!
	return $subject;
}

#sub dump_subjects {
#	my ($subjects) = sql_col("SELECT Name FROM tblSubject WHERE deleted=0");
#	for my $subject (@$subjects) {
#		$subject = subject_abbrev($subject);
#	}
#	for (sort { length($b) <=> length($a) } @$subjects) {
#		print "$_<br>";
#	}
#}