#! /usr/bin/perl # Copyright 2001-2020 Leslie Richardson # 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 = ('Report' => 'Report', 'Main' => 'Main', 'Attendance' => 'Attendance', 'Periods Per Day' => 'Periods Per Day', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', 'Last Name' => 'Last Name', 'First' => 'First', 'Middle' => 'Middle', 'Birthdate' => 'Birthdate', 'Status' => 'Status', 'Reserve' => 'Reserve', 'Mother' => 'Mother', 'School Days' => 'School Days', 'Error' => 'Error', 'Date' => 'Date', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Continue' => 'Continue', 'Records per Page' => 'Records per Page', 'Not Defined' => 'Not Defined', 'Grade' => 'Grade', 'Student' => 'Student', 'Not Found' => 'Not Found', 'Band' => 'Band', 'Font Size' => 'Font Size', 'Group' => 'Group', 'HTML' => 'HTML', 'Homeroom' => 'Homeroom', 'PDF' => 'PDF', 'Separate with Spaces' => 'Separate with Spaces', 'Report Type' => 'Report Type', 'Year End' => 'Year End', 'Total' => 'Total', 'Percent' => 'Percent', 'Days' => 'Days', 'Enrolled' => 'Enrolled', 'Show Withdrawn Students' => 'Show Withdrawn Students', ); use DBI; use CGI; use Number::Format qw(:all); use Time::JulianDay; use Cwd; # Constants my $self = 'rptattyear2.pl'; my $defmaxrecords = 28; # Maximum records per PDF page. # store yrmo totals for page bottom. my %globAtt, %globEnrol; # Default to creating PDF report my $pdf = '1'; my $html; # Get current dir so know what path for config files. my $configpath; my $teachermode; if ( getcwd() =~ /tcgi/ ){ # we are in tcgi $teachermode = 1; $configpath = '..'; # go back one to get to etc. } else { $configpath = '../..'; # go back two to get to etc. } # main config file eval require "$configpath/etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "$configpath/lib/libattend.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "$configpath/lib/libDate.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "$configpath/lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $dsn = "DBI:mysql:$dbase"; $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; 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 $currsdate = "$year-$month-$day"; my $currdate = "$month[$month] $day, $year"; my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); # Get current dir so know what CSS to display and shift to teacher settings. if ( getcwd() =~ /tcgi/ ) { # we are in tcgi $css = $tchcss; $homepage = $tchpage; $downloaddir = $tchdownloaddir; $webdownloaddir = $tchwebdownloaddir; } # HTML Header my $title = "$lex{'Year End'} $lex{Attendance} $lex{Report} 2"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; print qq{ \n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]}; if (not $teachermode ) { print qq{[ $lex{Attendance} ]\n}; } print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; } my $enddate; if ($arr{date}){ $enddate = $arr{date}; } else { $enddate = $currsdate; } my $startdate = $schoolstart; # Function from LibAttend my %schooldays = mkSchoolDays( $startdate, $enddate, $dbh ); # returns hash of schooldays in month. key is yyyy-mm and value is schooldays in month if ( not $arr{homeroom} ) { print qq{

$lex{Missing} $lex{Homeroom}

\n}; print qq{\n}; exit; } my $table = 'student'; if ( $arr{showwithdrawn} ) { $table = 'studentall'; } my $select = qq{where homeroom = ?}; my $sth = $dbh->prepare("select * from $table $select order by lastname, firstname"); $sth->execute( $arr{homeroom} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $studrows = $sth->rows; # Get Homeroom Teacher Name my $sth1 = $dbh->prepare("select userid from staff_multi where field_name = 'homeroom' and field_value = ?"); $sth1->execute( $arr{homeroom} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $userid = $sth1->fetchrow; $sth1 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth1->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname) = $sth1->fetchrow; # Check that Teacher attendance was done up to this date. checkAttEntry( $enddate, $arr{homeroom} ); # Open TEX file, and print Header if ( $pdf ) { $logfile = "pdflog$$.txt"; $shortname = "rptattyear2-$$"; $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; print_tex_doc_start(); # Setup the start of the file. } # Setup statements my $sth5 = $dbh->prepare("select count(*) from student where studnum = ?"); my $sth4 = $dbh->prepare("select count(*) from attend where studentid = ?"); print_tex_header(); while ( $sref = $sth->fetchrow_hashref ){ # Check that they have some attendance records, to verify withdrawn students enrolled this year. if ( $arr{showwithdrawn} ) { $sth4->execute( $sref->{studnum} ); # count attendance records if ($DBI::errstr) { print $DBI::errstr; die;} my $attcount = $sth4->fetchrow; if ( $attcount < 1 ) { next; } } # LaTeX filter the student record foreach my $key ( keys %$sref ) { ( $sref->{$key} ) = latex_filter( $sref->{$key} ); } my %sr = %$sref; $studentname = "$sr{lastname}, $sr{firstname} $sr{initial}"; $studnum = $sr{studnum}; # Now let's find which table student from: student or studentwd if ( $html ) { $sth5->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die;} my $active = $sth5->rows; # returns 1 if in student, 0 if not. if ( not $active ){ $studentname = qq{$studentname}; } } # Print this student's record doAttSetup( $sref ); print_tex_record( $sref ); $linecount++; if ( $linecount >= $arr{maxrecords} ){ # New page. print TEX "\\end{tabular}\\newpage\n\n"; print_tex_header(); $linecount = 0; } } # End of student loop # Attendance Row print TEX "\\rowcolor[gray]{0.90}"; print TEX "\\multicolumn{2}{|r|}{\\bf $lex{Student}-$lex{Days} $lex{Attendance} }"; my $asum; foreach my $yrmo ( sort keys %schooldays ){ print TEX "& \\bf $globAtt{$yrmo} "; $asum += $globAtt{$yrmo}; } print TEX "& $asum & \\\\ \\hline\n\n"; # Enrollment Row print TEX "\\rowcolor[gray]{0.90}"; print TEX "\\multicolumn{2}{|r|}{\\bf $lex{Student}-$lex{Days} $lex{Enrolled} }"; my $esum; foreach my $yrmo ( sort keys %schooldays ){ print TEX "& \\bf $globEnrol{$yrmo} "; $esum += $globEnrol{$yrmo}; } print TEX "& $esum & \\\\ \\hline\n\n"; # Percent Attendance Row print TEX "\\rowcolor[gray]{0.90}"; print TEX "\\multicolumn{2}{|r|}{\\bf $lex{Percent} $lex{Attendance}}"; foreach my $yrmo ( sort keys %schooldays ){ my $percent; if ( $globEnrol{$yrmo} ) { $percent = format_number($globAtt{$yrmo} / $globEnrol{$yrmo} * 100, 1); } else { $percent = 0; } print TEX "& \\bf $percent\\% "; } my $globPercent; if ( $esum ) { $globPercent = format_number($asum / $esum * 100, 1); } else { $globPercent = 0; } print TEX "& \\bf\\large $globPercent\\% & \\\\ \\hline\n\n"; print TEX "% print_tex_end here\n"; print TEX "\\end{tabular}\n\n\\end{document}"; close TEX; system("$pdflatex $filename > $logfile"); system("mv $shortname.pdf $downloaddir"); system("mv $logfile $downloaddir"); system("rm -f $shortname.*"); print qq{

}; print qq{$lex{'View/Download'} $title

\n}; print qq{

[ $lex{Main} | }; if ( not $teachermode ) { print qq{$lex{Attendance} | }; } print qq{$lex{'View Log File'} ]

\n}; print qq{\n}; #---------------------- sub print_tex_doc_start { #---------------------- #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ( $papersize, $textwidth, $textheight ); if ( $arr{papersize} eq $lex{Letter} ) { $papersize = 'letterpaper'; $textwidth = $g_letterpaper_textwidth; $textheight = $g_letterpaper_textheight; } elsif ( $arr{papersize} eq $lex{Legal} ) { $papersize = 'legalpaper'; $textwidth = $g_legalpaper_textwidth; $textheight = $g_legalpaper_textheight; } elsif ( $arr{papersize} eq $lex{A4} ) { $papersize = 'a4paper'; $textwidth = $g_a4paper_textwidth; $textheight = $g_a4paper_textheight; } print TEX "\\documentclass[10pt,$papersize,oneside]{article} \\usepackage{array,colortbl,inputenc} \\usepackage[landscape]{geometry} $a_latex_header \\renewcommand{\\familydefault}{\\sfdefault} \\pagestyle{empty} \\setlength{\\textwidth}{$textheight} \\setlength{\\textheight}{$textwidth} \\setlength{\\hoffset}{-35mm} \\setlength{\\voffset}{-20mm} \\setlength{\\headsep}{10pt} \\setlength{\\headheight}{14pt} \\setlength{\\topmargin}{0pt} \\setlength{\\parindent}{0pt} \\setlength{\\tabcolsep}{5pt} \\setlength{\\extrarowheight}{3pt} \\pagestyle{headings} \\markright{$schoolname - $schoolyear \\hfill {\\bf $title } \\hfill $currdate -- Pg } \n\n \\begin{document}\n"; } # End of Print Tex Doc Start #------------------- sub print_tex_header { #------------------- print TEX "{\\Large $lex{Homeroom} $arr{homeroom} -- $firstname $lastname} \n\n"; print TEX "\\begin{tabular}{|p{3cm}|p{2cm}|"; foreach my $yrmo ( sort keys %schooldays ){ print TEX "p{12mm}|"; } print TEX "p{18mm}|p{14mm}}\\hline\n\n"; # Now print the column header line. print TEX "\\rowcolor[gray]{0.90}{\\bf $lex{'Last Name'}} & {\\bf $lex{First}} "; foreach my $yrmo ( sort keys %schooldays ){ my ($ty,$tm) = split(/-/,$yrmo); print TEX "& {\\bf $s_month[$tm]}"; } print TEX "& $lex{Total} & $lex{Percent} \\\\ \\hline\n\n"; # Now print the schooldays in month print TEX "\\rowcolor[gray]{0.90}"; print TEX "\\multicolumn{2}{|r|}{\\bf $lex{'School Days'}}"; my $totaldays; foreach my $yrmo ( sort keys %schooldays ){ print TEX "& {\\bf $schooldays{$yrmo}}"; $totaldays += $schooldays{$yrmo}; } print TEX "& \\bf $totaldays \\\\ \\hline\n\n"; } #--------------- sub doAttSetup { # Version with monthly attendance info #--------------- # Needs globals: @schoolmonths - ordering of school months. # %schooldays - days in each month in hash. # $periodsperday - number of periods in the school day. my $sref = shift; %sr = %$sref; my $studnum = $sr{studnum}; %absent = (); %tardy = (); %enrol = (); my $periodsperday = $g_ppd{ $sr{grade} }; if ( not $periodsperday ){ if ( not $sr{grade} ){ $sr{grade} = $lex{'Not Found'}; } print qq{

$lex{'Periods Per Day'} $lex{'Not Defined'}
}; print qq{$lex{Grade}:$sr{grade} $lex{Student}:$studentname

\n}; print qq{\n}; exit; } # Pass Absent/Late strings to the library. my %lexi = ('Absent' => $absentString, 'Late' => $lateString ); # calculate attendance, one for each month. foreach my $yrmo ( sort keys %schooldays ) { $absrec = calcMonthlyAttendance( $studnum, $yrmo, $periodsperday, $enddate, \%lexi, $dbh ); ( $absent{$yrmo}, $tardy{$yrmo} ) = split(/:/,$absrec); $absent{ $yrmo } = format_number( $absent{ $yrmo }, 1); } # calculate enrollment, one for each month my $ymref = calcMonthlyEnrollment( $studnum, $schoolstart, $currsdate, $dbh ); %enrol = %$ymref; # yyyy-mm -> start,end,days # just used to set zero values; ie. format number. foreach my $yrmo ( sort keys %schooldays ) { $enrol{ $yrmo }{'days'} = format_number( $enrol{ $yrmo }{'days'}, 1); } } #------------------- sub print_tex_record { #------------------- my $sref = shift; my %sr = %$sref; # Grade, Lastname, Firstname, Middlename print TEX "$sr{lastname} & $sr{firstname}"; my $days = $schooldays - $absentdaytot; #Now print Monthly Records my ($attTotal, $enrolTotal); foreach my $yrmo ( sort keys %schooldays ) { my $present = format_number($enrol{$yrmo}{'days'} - $absent{$yrmo}, 1); if ( $present < 0 ) { print qq{$lex{Error}: $sr{firstname} $sr{lastname}: YearMonth:$yrmo }; print qq{Absent:$absent{$yrmo} Enrolled:$enrol{$yrmo}{'days'}
\n}; $present = '\\bf Err'; } else { # update totals if present is positive number. $enrolTotal += $enrol{$yrmo}{'days'}; $attTotal += $present; $globEnrol{$yrmo} += $enrol{$yrmo}{'days'}; $globAtt{$yrmo} += $present; } print TEX "& {\\small $present/$enrol{$yrmo}{'days'}}"; } my $percent; if ( $enrolTotal ) { $percent = format_number( $attTotal/$enrolTotal * 100, 2); } else { $percent = '0.0'; } print TEX "& $attTotal/$enrolTotal &$percent\\%"; print TEX " \\\\ \\hline\n"; } #---------------- sub showStartPage { #---------------- # Get Homerooms my $sth = $dbh->prepare("select distinct homeroom from student where homeroom != '' and homeroom is not NULL order by homeroom"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my @homerooms; my $sth1 = $dbh->prepare("select userid from staff_multi where field_name = 'homeroom' and field_value = ?"); my $sth2 = $dbh->prepare("select distinct grade from student where homeroom = ?"); HOMEROOM: while ( my $hr = $sth->fetchrow ) { # Do we have a teacher for this homeroom? $sth1->execute( $hr ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $userid = $sth1->fetchrow; if ( not $userid ) { next; } # Get Grade(s) of homeroom and check that they use 'homeroom' attendance entry method # $sth2->execute( $hr ); # if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # while ( my $gr = $sth2->fetchrow ) { # if ( $g_AttendanceEntryMethod{$gr} ne 'homeroom' ) { # next HOMEROOM; # } # } push @homerooms, $hr; } =head # Get Grades $sth = $dbh->prepare("select distinct grade from student where grade != '' and grade is not NULL"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my @grades; while ( my $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort {$a <=> $b} @grades; =cut print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n }; # Show Withdrawn Students print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Homeroom}
$lex{Date}\n}; print qq{\n}; print qq{
$lex{'Paper Size'}
$lex{'Show Withdrawn Students'}\n}; print qq{
$lex{'Records per Page'}
\n}; print qq{\n}; print qq{\n}; exit; } #----------------------- sub checkAttEntry { # check attendance entry for all year to date. #----------------------- my ($enddate, $homeroom) = @_; # pass end date to stop at, and homeroom # Find partial day closures from dates_periods for entire year. my %pclosed; my $sth = $dbh->prepare("select * from dates_periods"); $sth->execute; if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; # date, grades, period my $cref = parseGradesPeriod( $r{date}, $dbh); %cl = %$cref; # periods closed for this date cl{grade}{period} = 1 foreach my $gr ( keys %cl ) { foreach my $per ( sort keys %{ $cl{$gr} } ) { # if we have a value. $pclosed{ $r{date} }{ $gr }{$per} = 1; } } } # we now have %pclosed{date}{grade}{period} = 1; # Test %pclosed; =head foreach my $date ( sort keys %pclosed ) { foreach my $grade ( sort keys %{ $pclosed{$date} } ) { foreach my $period ( sort keys %{ $pclosed{$date}{$grade} } ) { print qq{Date:$date Grade:$grade Period:$period
\n}; } } } =cut # Check if attendance done by teacher my @missing = checkHomeroomAttEntry( $enddate, $homeroom, \%pclosed, $dbh ); if ( @missing ) { print qq{

Missing Attendance Entries
Homeroom $homeroom\n}; print qq{ – $firstname $lastname

\n}; print qq{\n}; print qq{\n}; foreach my $val ( @missing ) { my ($date,$period) = split(':', $val); print qq{\n}; } print qq{
DatePeriod
$date$period
\n}; } return; }