sub update_timetable_from_first_class {
	sql_connect();
	ldap_connect();

	my ($teacher_names, $teacher_ids, $rooms, $room, $timetable);
	my ($conf);
	my $bookings_conf_file = "bookings.conf";

	$conf = read_data($bookings_conf_file);

	($teacher_names, $teacher_ids) = lookup_staff_etc();

	my $teacher_id_by_name = {};
	while (my ($id, $name) = each %$teacher_names) {
		$teacher_id_by_name->{uc($name)} = $id;
	}

#	check_teacher_names();

	my $room_resources = room_resources();

#	($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");

	my $index = {};
	for my $room_resource (@$room_resources) {
		my ($room, $resource) = @$room_resource;
		$index->{$resource} = $room;
	}

	load_calendar_data();

	my $static_new = [];
	@$static_new = grep {
		my $resource_full = $_->[2];
		!$index->{$resource_full};
	} @$static;

	my $static_info_new = [];
	@$static_info_new = grep {
		my $resource_full = $_->[2];
		!$index->{$resource_full};
	} @$static_info;

	for my $room_resource (@$room_resources) {
		my ($room, $resource) = @$room_resource;

		$timetable = room_timetable($room);

		if (!@$timetable) {
			msg("Room `$room' is apparently not in the timetable database.");
		}

		for (@$timetable) {
			my ($subject, $day, $period, $room, $firstname, $familyname, $form) = @$_;
			if ($period !~ /period/i) { next; }  # e.g. Home Group
			my $fullname = uc("$firstname $familyname");
			my ($id) = $teacher_id_by_name->{$fullname};
			if (!$id) {
				msg("teacher `$fullname' is not in the directory with that name, please fix this.\n");
}
			else {
				push @$static_new, [$day, $period, $resource, $id];
#				print join "\t", $day, $period, $resource, lc($id);
#				print "\n";
			}
		}
	}

	d($static_new);
}

#display_timetable($timetable);

sub check_teacher_names {
	my $tt_names = timetable_teacher_names();
	my $errors = 0;
	for (@$tt_names) {
		my ($firstname, $familyname) = @$_;
		my $fullname = uc("$firstname $familyname");
		my ($id) = $teacher_id_by_name->{$fullname};
		if (!$id) {
			print STDERR "teacher `$fullname' in the timetable is not in the directory with that name.\n"; $errors = 1;
			$errors = 1;
		}
	}
	if ($errors) {
		die "please fix this inconsistency\n";
	}
}

sub timetable_teacher_names {
	my ($names) = sql_rows(<<End);
SELECT	tblTeacher.firstname AS firstname, tblTeacher.familyname AS familyname
FROM	tblTeacher
WHERE	tblTeacher.deleted = 0

ORDER BY	tblTeacher.familyname, tblTeacher.firstname
End

	return $names;
}

sub room_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, tblTeacher.firstname AS firstname, tblTeacher.familyname AS familyname,
	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	tblRoom.Code = '$ID'
  AND   tblTeacher.deleted = 0

ORDER BY	tblDay.SortOrder, tblPeriod.SortOrder
End

	return $timetable;
}

sub room_resources {
	my $room_resources = [];
	for my $cat (@{$conf->{resources}{__order}}) {
		for my $res (@{$conf->{resources}{$cat}{__order}}) {
			my $res_full = "$cat : $res";
			if ($res_full =~ /\b(rm|room)s?\b/i) {
				my $code = $res_full;
				$code =~ s/: \.$//;
				$code =~ s/.* //;
				push @$room_resources, [$code, $res_full];
			}
		}
	}
	return $room_resources;
}

1