#! /usr/bin/perl # Copyright 2001-2021 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 = ('Teacher' => 'Teacher', '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', 'Font Size' => 'Font Size', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Missing' => 'Missing', ); # maxchars and field info passed into page 1. #my $maxchars = 10; # maximum characters for subject description (if not small desc field) #my $descfield = 'smdesc'; # either smdesc or description value #my $logowidth = '20mm'; #my $logofile = 'schoollogo.jpg'; my $self = "rptStaffTtb.pl"; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\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{Teacher} $lex{Timetables} Report 1}; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} | \n}; print qq{$lex{Timetable} ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; showReport(); } #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } my %staffnames; # global if ( not $arr{term} ) { print qq{

No Terms

\n}; print qq{\n}; exit; } my @terms = split(/\s+/, $arr{term}); delete $arr{term}; print qq{
Terms:@terms
\n}; my @staff = keys %arr; my @skipped = prTimetable( \@staff, \@terms ); # print Timetables if ( @skipped ) { print qq{

$lex{Missing} $lex{Timetable}

\n}; print @skipped, qq{
\n}; } print qq{\n}; } #-------------- sub prTimetable { #-------------- my ($staffref, $termref) = @_; my @terms = @$termref; my @staff = @$staffref; my @skipped; # my $includeLocation = $arr{includeLocation}; # my $includeSubjsec = $arr{includeSubjsec}; # my $includeStudcount = $arr{includeStudcount}; # delete $arr{includeLocation}; # delete $arr{includeSubjsec}; # delete $arr{includeStudcount}; my (%sort, %staffname ); my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); foreach my $userid ( @staff ) { $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn) = $sth->fetchrow; $sort{"$ln$fn$userid"} = $userid; $staffname{$userid} = qq{$ln, $fn}; } foreach my $key ( sort keys %sort ) { my $userid = $sort{$key}; # Get staff info my $sth = $dbh->prepare("select * from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; # Check for a timetable, all terms. my $skipflag = 1; foreach my $term ( @terms ) { my $sth = $dbh->prepare("select count(t.subjsec) from subject s, schedat t where s.teacher = ? and startrptperiod <= '$term' and endrptperiod >= '$term' and s.subjsec = t.subjsec"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $tcount = $sth->fetchrow; if ( $tcount ) { # timetable count means we have entries for this term, at least. $skipflag = 0; } } if ( $skipflag ) { # no timetable (and courses) for these staff members. push @skipped, "$r{firstname} $r{lastname}
"; next; } # 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{

$r{sal} $r{firstname} $r{lastname}

\n}; foreach my $term ( @terms ) { # Get their courses this term; $sth = $dbh->prepare("select * from subject where teacher = ? and startrptperiod <= '$term' and endrptperiod >= '$term'"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } # 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 $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; # loop through all courses this term for this teacher. while ( my $ref = $sth->fetchrow_hashref ) { my %s = %$ref; my $subjsec = $s{subjsec}; $periodmap{ $g_PeriodMap{$s{grade}} } = 1; # the key has the map value. # load each timetable record for this subject. $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{
MapValue:$mapvalue Periods:}, %periodTimes, "
\n"; =head # Testing %timetable print qq{

Timetable Hash

\n}; foreach my $period (keys %timetable ) { foreach my $day ( keys %{ $timetable{$period} } ) { print qq{Day:$day Period:$period Courses:}, @{ $timetable{$period}{$day} },}; print qq{
\n}; } } =cut if ( not %timetable ) { next; } # term # Start Table. print qq{\n}; # Print Top Row here. print qq{\n}; my $dayvalue; for my $day ( 1 .. $maxdays ) { if ( $g_DaysPerCycle == 5 or $g_DaysPerCycle eq 'W' or $g_DaysPerCycle eq 'w' ) { # W|w = weekly $dayvalue = $dow[$day + 1]; } else { $dayvalue = $day; } print qq{}; } print qq{\n}; my @periods = sort { $a <=> $b } keys %timetable; my $maxperiods = $periods[-1]; foreach my $period ( 1 .. $maxperiods ) { print qq{}; foreach my $day ( 1 .. $maxdays ) { print qq{}; } # end of day loop. print qq{\n}; } # end of periods loop print qq{
$lex{Term} $term / $lex{Day}$dayvalue
$lex{Period} $period
$periodTime{$period}		}; 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} ) { # which may be chosen field $r{smdesc} = substr( $r{description},0, arr{descsize} ); } # 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} ); } # Append info to cell value my $cellvalue = qq{$coursedesc
}; if ( $arr{includeSubjsec} ) { $cellvalue .= qq{ $subjsec }; } if ( $arr{includeStudcount} and $studcount ) { $cellvalue .= qq{ ($studcount)}; } if ( $arr{includeLocation} and $r{location} ) { $cellvalue .= qq{
$r{location}}; } print qq{$cellvalue
}; } print qq{
\n}; } # end of Term Loop if ( $arr{includePageBreak} ) { print qq{

\n}; } ## new page for next teacher. } # next userid return @skipped; } # end of prTimetable #----------------- sub showStartPage { #----------------- my $checkteacher = 1; if ( $arr{nocheck} ) { $checkteacher = 0; } print qq{
\n}; print qq{\n}; print qq{
\n\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Get staff and real teachers are checked. my $sth1 = $dbh->prepare("select field_value from staff_multi where field_name = 'position' and userid = ?"); my $sth = $dbh->prepare("select * from staff order by lastname, firstname"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $userid = $r{userid}; $sth1->execute($userid); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($checked, @position); while ( my $position = $sth1->fetchrow ) { push @position, $position; if ( $position eq 'Classroom Teacher' and $checkteacher ) { $checked = qq{checked="checked"}; } } my $pos = join(',',@position); print qq{\n}; } print qq{
Staff MemberPosition
\n}; print qq{$r{lastname}, $r{firstname}$pos
\n}; print qq{\n}; print qq{\n}; # Now do the term print qq{\n}; # Things to Include: location, count, subjsec my $checked = qq{checked="checked"}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
Program Options
$lex{'Terms'}}; print qq{ \n}; print qq{$lex{'Separate with Spaces'}
}; print qq{ Include Course Code-Section
}; print qq{ Include Student Count
}; print qq{ Include Room # / Location
}; print qq{ Add Page Break after each Staff Member
Course Description Size
Max Description Characters (for Full Description)
}; print qq{
\n}; print qq{\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; }