#! /usr/bin/perl
# Copyright 2001-2007 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.
# Subject/Summary Attendance Report
# Get Start date of current term. Calculate all student's attendance
# per subject (and grand total). Display descending list by all students
# per class or per total. Data comes first from attendance linked to other
# tables. Attend is definitive, not eval (which may change).
# Note: $lateUnexcused and $absentUnexcused (line 206) are loaded from admin.conf
# and MUST be set correctly for this to work...
my %lex = ('Subject Attendance Report' => 'Subject Attendance Report',
'Attendance' => 'Attendance',
'No Term Found' => 'No Term Found',
'Make sure that termdates correctly set in the global configuration file' =>
'Make sure that termdates correctly set in the global configuration file',
'The date is' => 'The date is',
'Student Subject Attendance - by Term' => 'Student Subject Attendance - by Term',
'Subject' => 'Subject',
'Abs Unex' => 'Abs Unex',
'Abs Oth' => 'Abs Oth',
'Lates' => 'Lates',
'Not Found' => 'Not Found',
'Absent Unexcused' => 'Absent Unexcused',
'Late Unexcused' => 'Late Unexcused',
'Periods per Day' => 'Periods per Day',
'Lates equal one Period Absent' => 'Lates equal one Period Absent',
'The term is' => 'The term is',
'Red = Withdrawn Student' => 'Red = Withdrawn Student',
'Days' => 'Days',
'Per' => 'Per',
);
my $ppd = 4; # periods per day, used to translate into days missed.
my $latesEquiv = 3; # how many lates = 1 period missed.
use DBI;
use CGI;
use Date::Business;
# Read config variables
require '../etc/admin.conf' || die "Cannot read admin.conf!";
# Set Date thingy's.
my @month = ('','January','February','March','April','May','June','July',
'August','September','October','November','December');
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;}
$currdate = "$year-$month-$day";
$currsdate = "$year$month$day";
$currdate1 = "@month[$month] $day, $year";
my $q = new CGI;
my %arr = $q->Vars;
print $q->header;
print "$doctype\n". $lex{'Subject Attendance Report'}. "\n";
print "\n";
print "$chartype\n[ ". $lex{Attendance}. " ]
\n";
print "$latesEquiv ". $lex{'Lates equal one Period Absent'}. "& $ppd ". $lex{'Periods per Day'}. "\n";
my $date = $currsdate; # Short Date, no hyphens
$dateobj = new Date::Business(DATE=>$date);
# Figure out the current term, if not passed term
my $term;
if (not $arr{term}){ # No Passed Term, figure it out from currdate.
$term = 0;
for (1..12){ # 12 is worst case scenario.. what's reasonable?
my $startdate = $g_termstart{$_}; $startdate =~ s/-//g;
my $startobj = new Date::Business(DATE => $startdate);
my $enddate = $g_termend{$_}; $enddate =~ s/-//g;
my $endobj = new Date::Business(DATE => $enddate);
$startoffset = $dateobj->diffb($startobj,'prev','next');
$endoffset = $dateobj->diffb($endobj,'prev','next');
# print "StartOffset: $startoffset Endoffset: $endoffset \n";
if ($startoffset >= 0 and $endoffset <= 0){
$term = $_; last;
}
} # End of term figure out.
} else { $term = $arr{term}; }
if (not $term){ # No term found! Configuration Error?
print '
'. $lex{'No Term Found'}. "
\n";
print '
'. $lex{'Make sure that termdates correctly set in the global configuration file'};
print $lex{'The date is'}. ": $date
\n";
print "\n";
die;
}
print '
'. $lex{'Student Subject Attendance - by Term'}. "
\n";
print '
'. $lex{'The date is'}. ": $date\n";
print $lex{'The term is'}. ": $term \n";
print "". $lex{'Red = Withdrawn Student'}. "
\n";
# Do All terms or just this one?
if (not $arr{all}){
$startdate = $g_termstart{$term};
} else {
$startdate = $g_termstart{1};
}
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
#$sth = $dbh->prepare("select distinct attend.studentid, student.lastname,
# student.firstname from attend
# left outer join student on attend.studentid = student.studnum
# where attend.reason = '$absentunexcused' and
# to_days(attend.absdate) >= to_days('$startdate')
# order by student.lastname, student.firstname");
# First find subjects in this term..
my (@subjects, %subjects);
my $sth = $dbh->prepare("select subjsec, description from subject where
startrptperiod <= ? and endrptperiod >= ? order by description");
$sth->execute($term, $term);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my ( $subjsec, $desc) = $sth->fetchrow ) {
push @subjects, $subjsec; # @subjects gives us sort order
$subjects{$subjsec} = $desc;
}
# Now find students in those subjects
my $sth1 = $dbh->prepare("select lastname, firstname from student where studnum = ?");
my $sth2 = $dbh->prepare("select lastname, firstname from studentwd where studnum = ?");
foreach my $subjsec ( @subjects ) {
my $sth = $dbh->prepare("select distinct studnum from eval where
subjcode = ? and studnum > 0");
$sth->execute( $subjsec );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my ( $studnum ) = $sth->fetchrow ) {
# Get name
$sth1->execute( $studnum );
my ($lastname, $firstname ) = $sth1->fetchrow;
$wdflag = 0;
if (not $lastname) {
$sth2->execute( $studnum );
( $lastname, $firstname ) = $sth2->fetchrow;
$wdflag = 1;
if (not $lastname) {
$lastname = $lex{'Not Found'};
}
}
push @students, "$lastname:$firstname:$studnum:$subjsec:$wdflag";
}
}
# now have all students in @students array.
print "
\n";
print '
'. $lex{Subject}. '
'. $lex{'Abs Unex'}. '
';
print $lex{'Abs Oth'}. '
'. $lex{Lates}. "
". $lex{Days};
print " (". $lex{Per}. ")
\n";
my ($currstud, $prevstud, $totalDays);
$currstud = -1;
my $sth3 = $dbh->prepare("select description, smdesc from subject where subjsec = ?");
# Now loop through all students, printing classes and number missed.
foreach my $stud ( sort @students ) {
my ($lastname, $firstname, $studnum, $subjsec, $wdflag) = split /:/, $stud;
# Get Subject Name
$sth3->execute( $subjsec );
my ($desc, $smdesc) = $sth3->fetchrow;
if (not $smdesc) {
$description = substr($description, 0, 10);
} else {
$description = $smdesc;
}
if ( $wdflag ){ # withdrawn students
$lastname = "$lastname";
$firstname = "$firstname";
}
$prevstud = $currstud;
$currstud = $studnum;
if ( $currstud != $prevstud ) { # New student...
if ($prevstud != -1 ) { print "
$totalDays
\n"; }
$totalDays = 0;
print "
$firstname $lastname ($studnum)
\n";
}
# Get attendance reasons and count;
my $sth= $dbh->prepare("select distinct reason, count(reason) from attend
where subjsec = ? and studentid = ? group by reason");
$sth->execute( $subjsec, $studnum );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my ($totalCount, $absentCount, $lateCount, $otherCount);
while ( my ($reason, $count) = $sth->fetchrow ) {
if ( $reason eq $absentUnexcused ) {
$absentCount = $count;
$totalCount = $count;
} elsif ( $reason eq $lateUnexcused ) {
$lateCount = $count;
$totalCount += int( $count / $latesEquiv )
} else {
$otherCount += $count;
}
}
if ( $ppd ) {
$dayCount = $totalCount / $ppd;
}
$dayCount = sprintf("%3.2f", $dayCount);
$totalDays += $dayCount;
# print stuff
print "