#! /usr/bin/perl
# Copyright 2001-2009 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.
# ======= Monthly Student Attendance Report =======
# Get passed date for end of calendar month or school month.
# Calculate days in the month and number of days of school hols.
# Get student absences for that month, order by group,lastname,firstname.
# Do # of present, absent, late for month.
# Absent strings for matching set in admin.conf; Comparison at line
# 305 in this file.
my %lex = ('Monthly Attendance Report' => 'Monthly Attendance Report',
'Missing Date' => 'Missing Date',
'Main' => 'Main',
'Attendance' => 'Attendance',
'Schooldays' => 'Schooldays',
'Weekdays' => 'Weekdays',
'Hols/Inserv' => 'Hols/Inserv',
'Red = Withdrawn' => 'Red = Withdrawn',
'Grand Totals' => 'Grand Totals',
'Periods Per Day not defined for' => 'Periods Per Day not defined for',
'Name' => 'Name',
'Enrolled' => 'Enrolled',
'Attended' => 'Attended',
'Days' => 'Days',
'Absent' => 'Absent',
'Unexec' => 'Unexec',
'Excuse' => 'Excuse',
'Other' => 'Other',
'Total' => 'Total',
'Late' => 'Late',
'Times' => 'Times',
'Grade' => 'Grade',
'homeroom' => 'homeroom',
'band' => 'band',
'Total School Days not calculated' => 'Total School Days not calculated',
'Student-Days' => 'Student-Days',
'Enrollment Error for student' => 'Enrollment Error for student',
'Records are not consistent' => 'Records are not consistent',
'Pg' => 'Pg',
'Top' => 'Top',
'View Log File' => 'View Log File',
'Error' => 'Error',
'View/Download' => 'View/Download',
'Report Type' => 'Report Type',
'PDF' => 'PDF',
'HTML' => 'HTML',
'Date' => 'Date',
'Group' => 'Group',
'Grade' => 'Grade',
'Homeroom' => 'Homeroom',
'Continue' => 'Continue',
'WD' => 'WD',
'Separate with Spaces' => 'Separate with Spaces',
'Font Size' => 'Font Size',
'Paper Size' => 'Paper Size',
'Letter' => 'Letter',
'Legal' => 'Legal',
'A4' => 'A4',
'Records per Page' => 'Records per Page',
);
use DBI;
use CGI;
use Date::Business;
use Number::Format qw(:all);
my $defmaxrecords = 24; # Default maximum records per PDF page.
# Read config variables
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
if ( length($month) == 1 ){ $month = "0".$month;}
if ( length($day) == 1 ){ $day = "0".$day;}
my $currdate = "$year-$month-$day";
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;
# Page Header...
print "$doctype\n". $lex{'Monthly Attendance Report'}. "
$chartype\n\n";
print "[ ". $lex{Main}. " |\n";
print "". $lex{'Attendance'}. " ]
\n";
if ( not $arr{page} ) {
showStartPage();
}
# Setup the TYPE of report, HTML or PDF.
my ($html, $pdf );
if ( $arr{reporttype} eq $lex{HTML} ) {
$html = 1;
} else {
$pdf = 1;
}
if ( not $arr{date} ) {
print $lex{'Missing Date'}. " (YYYY-MM-DD)\n";
print "\n";
exit;
}
my ($year, $month, $day) = split /-/, $arr{date};
my $enddate = "$year$month$day";
# not the best solution, yet...
my $group; # Either 'grade', 'homeroom', or 'band'.
if ( $arr{groupname} eq $lex{Grade} ) {
$group = 'grade';
} elsif ( $arr{groupname} eq $lex{Homeroom} ) {
$group = 'homeroom';
} else {
$group = 'band';
}
my @groupvals = split /\s/, $arr{groupvalue};
my $select;
if ( @groupvals ) {
$select = "where ";
my $first = 1;
foreach my $grpval ( @groupvals ) {
if ( not $first ) { $select .= ' or '; }
$first = 0;
$grpval = $dbh->quote( $grpval );
$select .= "$group = $grpval";
}
}
#print "Select: $select \n";
# Check to make sure start date can really be the first of month.
my ($startyear, $startmonth, $startday) = split /-/, $schoolstart;
if ( $month == $startmonth and $year == $startyear ) {
$startdate = $schoolstart;
} else {
$startdate = "$year-$month-01";
}
$startdate =~ s/-//g; #remove hyphens for DATE::Business fn.
my $end = new Date::Business(DATE=>$enddate);
my $start = new Date::Business(DATE=>$startdate);
my $schooldays = $end->diffb($start,'prev','next');
$schooldays++;
# Yup, this is the right way to do the date calcs...checked..
my $sth = $dbh->prepare("select * from dates
where to_days(date) >= to_days('$startdate') and
to_days(date) <= to_days('$enddate')");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $holidays = $sth->rows;
my $daysinsession = $schooldays - $holidays;
if ( $pdf ) { # Open TEX file, and print header.
$shortname = "monthatt$$";
$fileName = "$shortname.tex";
open(TEX,">$fileName") || die "Can't open tex file";
print_tex_doc_start(); # Setup the start of the file.
}
if ( $html ) { # print top of page
print '
\n";
}
$sth = $dbh->prepare("select lastname, firstname, initial, studnum, grade, homeroom, band
from studentall $select
order by $group, lastname, firstname");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die;}
my $studrows = $sth->rows;
# Setup statement handle to find which table student is in.
my $sth5 = $dbh->prepare("select studnum from student where studnum = ?");
my $curroom = -1; # Watch! Not a value entered for a reason...hopefully.
my $firstflag = 1; # Flag to watch for first "real" person (not withdrawn)
my $studentname;
# *********** Start of Main Loop ***********
while ( my ( $lastname, $firstname, $initial, $studnum, $grade, $homeroom, $band ) = $sth->fetchrow ) {
#$student[1] =~ s/\\/\//g;
# NOTE: Add LaTeX filtering.
$studentname = "$lastname, $firstname $initial";
# Now let's find which table student from: student or studentwd
$sth5->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $active = $sth5->rows; # returns 1 if in student, 0 if not.
if ( not $active and $html ) {
$studentname = "". $lex{WD}. " $studentname";
}
# Calculate the days enrolled for this student
# Return blocks of time when student was enrolled.
#(An array of hash pointers)
my @enrolblocks = ();
@enrolblocks = find_enrollment_blocks( $studnum,$active );
$schooldays = 0;
foreach my $hashref ( @enrolblocks ) {
my $startenroldate = $hashref->{start};
my $endenroldate = $hashref->{end};
$startenroldate =~ s/-//g; $endenroldate =~ s/-//g; # strip hyphens
$endenrolobj = new Date::Business(DATE=>$endenroldate);
$startenrolobj = new Date::Business(DATE=>$startenroldate);
# The other objects are $start and $end.
# Now figure out the overlap and actual reporting period dates.
my $startdiff = $start->diffb($startenrolobj,'prev','next');
my $enddiff = $end->diffb($endenrolobj,'prev','next');
#print "SEnrol: $startenroldate EEnrol: $endenroldate \n";
#print "SReport: ",$start->image," EReport:",$end->image," \n";
#print "SD: $startdiff ED: $enddiff \n";
if ($startdiff < 0){ # Actual starting time is later.
$startrpobj = new Date::Business(DATE =>$startenroldate); }
else { $startrpobj = new Date::Business(DATE=>$startdate);}
if ($enddiff > 0){ # Actual ending date is earlier.
$endrpobj = new Date::Business(DATE=>$endenroldate); }
else { $endrpobj = new Date::Business(DATE=>$enddate);}
my $rpdays = $endrpobj->diffb($startrpobj,'prev','next');
$rpdays++; if ($rpdays < 0){ $rpdays = 0;}
my $startrp = $startrpobj->image;
my $endrp = $endrpobj->image;
#print "StartRP: $startrp EndRP: $endrp \n";
#print "RP Days: $rpdays \n";
if ($rpdays){
# Now find number of holidays during this same period.
$sth4 = $dbh->prepare("select * from dates
where to_days(date) >= to_days('$startrp') and
to_days(date) <= to_days('$endrp')");
$sth4->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
$rphols = $sth4->rows;
} else { $rphols = 0;}
#print "RPHols: $rphols
\n";
$schooldays = $schooldays + $rpdays - $rphols;
#print "School Days: $schooldays \n";
} # End of Loop finding Student Enrollment
if ( $schooldays < 1 ){ next; }
# Put Header printing here since we have to skip some students ====
$oldroom = $curroom;
if ( $group eq 'homeroom' ) {
$curroom = $homeroom; # homeroom field.
# Get Teacher Name for the room.
$sth1 = $dbh->prepare("select lastname, firstname from staff
where homeroom = '$curroom' ");
$sth1->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($fn, $ln) = $sth1->fetchrow;
$teachername = '$fn $ln';
} elsif ($group eq 'band') {
$curroom = $band; # band
} else {
$curroom = $grade; # grade field.
}
if ( $curroom ne $oldroom ) {
if (not $firstflag) {
if ($html){
print_html_class_totals();
} else {
print_tex_class_totals();
$linecount=0;
}
$totschooldays = 0; $totattended = 0;
$totlatedayun = 0; $totlatedayex = 0;
$totabsentdayun = 0; $totabsentdayex = 0;
$totother = 0; $totabsenttot = 0;
}
if ( $html ){
print ""; print_html_header();
} else {
print_tex_header();
}
# Recalc the number of periods per day when class changes.
$periods = $g_ppd{$grade}; # student[5] is grade.
if ( not $periods ){
if ( not $grade ){ $student[5] = 'Undefined!';}
print '
'. $lex{'Periods Per Day not defined for'}. ' '. $lex{Grade}. " $grade\n";
print "$firstname $lastname