#!/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$title\n}; print qq{\n}; if ( not $arr{page} ) { # load javascript print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{ $lex{Attendance} ]\n}; print qq{

$title

\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{\n}; print qq{}; print qq{\n}; print qq{\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{\n}; print qq{\n}; # Print either subject (if given) or homeroom (if found) print qq{\n}; print qq{\n}; } if ($first ) { print qq{

$header

\n}; print qq{

$lex{'No Absences found'}

\n}; } else { print qq{
$header
$lex{Name}$lex{Date}$lex{Reason}$lex{Period}$lex{Course}/$lex{Homeroom}$lex{Phone}
}; print qq{$s{lastname}, $s{firstname}$r{absdate}$r{reason}$r{period}$course_or_homeroom$lex{Home} 1: $s{hphone1} $lex{Work} 2: $s{wphone1}
\n}; print qq{$lex{Home} 2: $s{hphone2} $lex{Work} 2: $s{wphone2}
\n}; } print qq{\n}; exit; } # end of showReport #---------------- sub showStartPage { #---------------- my (%sort, %courses ); my $sth1 = $dbh->prepare("select description, smdesc, startrptperiod, endrptperiod from subject where subjsec = ?"); my $sth = $dbh->prepare("select distinct subjsec from attend where subjsec is not NULL and subjsec != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} while ( my $subjsec = $sth->fetchrow ) { $sth1->execute($subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my ( $desc, $smdesc, $sterm, $eterm ) = $sth1->fetchrow; if ( not $smdesc ) { $smdesc = $desc; }; my $key = "$smdesc$subjsec"; if ( not $desc ) { # not a course $key = $subjsec; # not really a subjsec, a homeroom; $courses{$key} = $key; } else { # we have a course $courses{$subjsec} = "$smdesc ($sterm-$eterm)"; } $sort{$key} = $subjsec; } # foreach my $key ( sort keys %sort ) { print "K:$key V:$sort{$key}
\n"; } # foreach my $key ( sort keys %courses ) { print "K:$key V:$courses{$key}
\n"; } # Start Form print qq{
\n}; print qq{\n}; # Start Table print qq{\n}; # View by Student print qq{\n}; print qq{\n}; print qq{\n}; # Divider print qq{\n}; # View by Date Range print qq{\n}; print qq{\n}; print qq{\n}; # Divider print qq{\n}; # View by Course / Homeroom print qq{\n}; print qq{\n}; print qq{
Last,First/Last/Initials/Studnum
\n}; print qq{$lex{Start}/$lex{Single} $lex{Date} \n}; print qq{\n}; print qq{
$lex{End} $lex{Date}\n}; print qq{\n}; print qq{ $lex{'Blank=Single Date'}
\n}; print qq{
\n}; print qq{
\n}; print qq{ \n}; print qq{\n}; exit; }