#!/usr/bin/perl
# Copyright 2001-2022 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.
my %lex = ('Main' => 'Main',
'Attendance' => 'Attendance',
'No Absences found' => 'No Absences found',
'Student Not Found' => 'Student Not Found',
'View' => 'View',
'Name' => 'Name',
'Date' => 'Date',
'Reason' => 'Reason',
'Period' => 'Period',
'Homeroom' => 'Homeroom',
'Phone' => 'Phone',
'Start Date' => 'Start Date',
'End Date' => 'End Date',
'Student' => 'Student',
'Home' => 'Home',
'Work' => 'Work',
'Error' => 'Error',
'Course' => 'Course',
'by Student' => 'by Student',
'by Date' => 'by Date',
'by Course' => 'by Course',
'Start' => 'Start',
'Single' => 'Single',
'End' => 'End',
'Blank=Single Date' => 'Blank=Single Date',
);
my $self = 'attview.pl';
use DBI;
use CGI;
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
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;
my $title = "$lex{View} $lex{Attendance}";
print qq{$doctype\n
\n};
if (not $arr{page} ) {
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
showReport();
}
#-------------
sub showReport {
#-------------
# foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
my ( $header, $student, $sth );
my $orderby = "s.lastname, s.firstname, a.absdate desc, a.period";
my $orderbystud = "a.absdate desc, s.lastname, s.firstname, a.period";
my $sth = $dbh->prepare("select @fields from attend, studentall
where attend.studentid = studentall.studnum $select order by $orderby");
if ( $arr{startdate} and $arr{enddate} ) {
$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
where a.studentid = s.studnum and
to_days(attend.absdate) >= to_days( ? ) and
to_days(attend.absdate) <= to_days( ?) order by $orderby");
$sth->execute( $arr{startdate}, $arr{enddate} );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$header = "$lex{'Start Date'}: $arr{startdate} ".
"$lex{'End Date'}:$arr{enddate}";
} elsif ( $arr{startdate} ) { # startdate only; a single day
$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
where a.studentid = s.studnum and
to_days(attend.absdate) = to_days( ? ) by $orderby");
$sth->execute( $arr{startdate} );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$header = "$lex{'Start Date'} $arr{startdate}";
} elsif ( $arr{course} ) {
$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
where a.studentid = s.studnum and subjsec = ? order by $orderby");
$sth->execute( $arr{course} );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$header = "$lex{Course}/$lex{Homeroom} $arr{course}";
} elsif ( $arr{student} ) {
$student = $arr{student};
my $studnum;
# Setup the Search
if ($student =~ /\d+/) { # we have a student number
$studnum = $student;
} else { # we have words hopefully with a comma
($lastname,$firstname) = split /\,/, $student;
$firstname =~ s/^\s*//;
$lastname =~ s/^\s*//;
if ($lastname and $firstname){ # both entered.
$select = "s.lastname = '$lastname' and s.firstname = '$firstname'";
} elsif ($lastname and not $firstname){ # only lastname (no comma)
if (length($lastname) == 2){ # search by initials: fi, li.
$fi = substr($lastname,0,1); $li = substr($lastname,1,1);
$select = "s.lastname like '$li%' and s.firstname like '$fi%'";
} else {
$select = "s.lastname = '$lastname'";
}
} else { # print an error....
print qq{$lex{'Student Not Found'}\n};
print qq{\n};
exit;
}
# Find the student number that matches
$sth = $dbh->prepare("select studnum from studentall s where $select");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$studnum = $sth->fetchrow;
} # Last Else
$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
where a.studentid = s.studnum and studnum = ? order by $orderby");
$sth->execute($studnum);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$header = "$lex{Student}: $firstname $lastname ($arr{student})\n";
}
my $sth1 = $dbh->prepare("select * from studentall where studnum = ? ");
my $sth2 = $dbh->prepare("select description, teacher from subject where subjsec = ?");
my $sth3 = $dbh->prepare("select lastname, firstname from staff s, staff_multi sm
where s.userid = sm.userid and sm.field_name = 'homeroom' and sm.field_value = ?");
my $first = 1;
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %{$ref};
if ( $first ) {
print qq{
\n};
print qq{
$header
\n};
print qq{
$lex{Name}
$lex{Date}
};
print qq{
$lex{Reason}
\n
$lex{Period}
};
print qq{
$lex{Course}/$lex{Homeroom}
$lex{Phone}
\n};
$first = 0;
}
# Get Student Information
$sth1->execute( $r{studentid} );
if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
my $sref = $sth1->fetchrow_hashref;
my %s = %{$sref};
# Get Course/Teacher Information
my $course_or_homeroom;
if ( $r{subjsec} !~ m/^HR:/ and $r{subjsec} ){ # not a homeroom value
$sth2->execute( $r{subjsec} );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my ($desc, $teacher) = $sth2->fetchrow;
$course_or_homeroom = "$desc - $teacher";
} else { # we have a homeroom;
my $homeroom;
if ( $r{subjsec} ) {
$homeroom = $r{subjsec};
$homeroom =~ s/^HR://; # strip leading HR:
} else { # look in his/her student record
$homeroom = $s{homeroom};
}
$sth3->execute( $homeroom );
if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
my ($lname, $fname) = $sth3->fetchrow;
$course_or_homeroom = "$homeroom - $fname $lname";
}
print qq{