#! /usr/bin/perl # Copyright 2001-2022 Leslie Richardson # This file is part of Open Admin for Schools. # based on the central site rptaandc1.pl script my %lex = ('Attendance Report' => 'Attendance Report', 'Main' => 'Main', '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', 'Mother' => 'Mother', 'School Days' => 'School Days', 'Error' => 'Error', 'Date' => 'Date', 'Continue' => 'Continue', 'Not Defined' => 'Not Defined', 'Grade' => 'Grade', 'Student' => 'Student', 'Not Found' => 'Not Found', 'Band' => 'Band', 'Month' => 'Month', 'Periods per Day' => 'Periods per Day', 'Select' => 'Select', 'Database' => 'Database', 'Blank=All' => 'Blank=All', 'Check Next Page' => 'Check Next Page', 'Show Withdrawn' => 'Show Withdrawn', 'Type' => 'Type', 'Enrollment' => 'Enrollment', 'Previous Years' => 'Previous Years', 'Students' => 'Students', 'Default' => 'Default', 'Withdrawn' => 'Withdrawn', 'School Year' => 'School Year', ); my $self = 'attendview_past.pl'; use DBI; use CGI; use Number::Format qw(:all); my $defaultmaxrecords = 20; # Maximum records per PDF page. my $group = 'grade'; my $pdf = '1'; my $html; # Read config variables eval require "../../etc/admin.conf.root"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../../lib/libattend.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # Local connection my $dsn = "DBI:mysql:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Load Main admin configuration ('admin') my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $datavalue) = $sth->fetchrow ) { eval $datavalue; if ( $@ ) { print "$lex{Error}: $@
\n"; die "$lex{Error}: $@\n"; } } my $currdownloaddir = $downloaddir; my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); 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"; # Load Remote database my $dbhr; if ( $arr{db} ) { # from start page my $db = $arr{db}; my $dsn = "DBI:mysql:database=$db;host=$remotehost"; $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword); } # HTML Header my $title = qq{$lex{'Previous Years'} $lex{'Attendance Report'} }; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title }; if ( $schoolname ) { print qq{ – $schoolname}; } print qq{

\n}; #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; getGrades(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; selectStudents(); } elsif ( $arr{page} == 3 ) { delete $arr{page}; reportPDF(); } #------------ sub getGrades { #------------ # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( not $arr{db} ) { print qq{

No Database Selected!

\n}; print qq{\n}; exit; } my $checked; if ( $arr{checknextpage} ) { $checked = qq{checked="checked"}; delete $arr{checknextpage}; } my @grades; my $sth = $dbhr->prepare("select distinct grade from student"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $grade = $sth->fetchrow ) { push @grades, $grade; } @grades = sort {$a cmp $b} @grades; # Form Header print qq{
\n}; print qq{\n}; foreach my $key ( sort keys %arr ) { print qq{\n}; } print qq{
\n}; print qq{\n}; print qq{\n}; foreach my $grade ( @grades ) { print qq{\n}; } # Check Next Page (Students) print qq{\n}; print qq{
Select Grades
}; print qq{Grade $grade
}; print qq{ $lex{'Check Next Page'} (Students)
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub showStartPage { #---------------- # Get remote databases my $remotedbase = 'information_schema'; my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost"; my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword); my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my %remotedb; while ( my $db = $sth->fetchrow ) { if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; } # print qq{DB:$db
\n}; if ( $db =~ m/$dbase/ ) { # print qq{Match! $dbase - $db
\n}; $remotedb{$db} = 1; } } print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; foreach my $db ( sort keys %remotedb ) { my $temp = $db; $temp =~ s/$dbase//; # strip database name; my $year = $temp - 1; # previous year; $year = qq{$year-$temp}; # now in 2018-2019 format print qq{\n}; } print qq{\n}; # Grade - separate page now # print qq{\n}; # print qq{\n}; # Check next Page print qq{\n}; # Show Withdrawn print qq{\n}; # PPD Override print qq{\n}; # Students per Page print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{'School Year'}
$year ($db)
$lex{Grade} Separate with Spaces / }; # print qq{$lex{'Blank=All'}
}; print qq{$lex{'Check Next Page'} (Grades)
$lex{'Show Withdrawn'}
}; print qq{$lex{Default} $lex{'Periods per Day'}
Maxiumum Students per Page (PDF)
\n}; print qq{\n}; exit; } #----------------- sub selectStudents { #----------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( not $arr{db} ) { print qq{

No Database Selected!

\n}; print qq{\n}; exit; } # get the grades; my @grades; foreach my $key ( keys %arr ) { my ($sigil,$gr) = split(':',$key); if ( $sigil eq 'G' ) { # we have a grade; push @grades, $gr; delete $arr{$key}; # no problems deleting since we are just getting the keys } } if ( not @grades ) { print qq{

No Grades Selected!

\n}; print qq{\n}; exit; } my $studenttable = 'student'; if ( $arr{showwithdrawn} ) { $studenttable = 'studentall'; } delete $arr{showwithdrawn}; my $checked; if ( $arr{checknextpage} ) { $checked = qq{checked="checked"}; delete $arr{checknextpage}; } # Form Header print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; my $sth = $dbhr->prepare("select lastname, firstname, studnum, grade from $studenttable where grade = ? order by lastname, firstname"); # Check for withdrawn student my $sth1 = $dbhr->prepare("select count(*) from studentwd where studnum = ?"); # Loop Over all Students in those grades, foreach my $grade ( sort {$a cmp $b} @grades ) { print qq{
$lex{Grade} $grade
\n}; # Get students $sth->execute( $grade ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } # print qq{
\n}; # print qq{

$lex{Select} $lex{Students}

\n}; print qq{\n}; print qq{\n}; print qq{\n}; while ( my ( $lastname, $firstname, $studnum, $grade ) = $sth->fetchrow ) { # Find out if withdrawn my $wd; $sth1->execute( $studnum ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $wdcount = $sth1->fetchrow; if ( $wdcount ) { $wd = qq{WD}; } print qq{}; print qq{}; print qq{\n}; } print qq{
}; print qq{WD = $lex{Withdrawn}
$lex{Student}Number$lex{Grade}
$wd $lastname, $firstname$studnum$grade
\n}; print qq{
\n}; } # end of grades loop print qq{
\n}; exit; } # end of selectStudents #------------ sub reportPDF { #------------ # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my $maxrecords = $arr{maxrecords}; delete $arr{maxrecords}; # only remaining values in %arr hash will be student numbers. # Check Remote database if ( not $arr{db} ) { print qq{

$lex{Database} $lex{'Not Found'}

\n}; print qq{\n}; exit; } delete $arr{db}; # not needed # Note: defaultppd key still in %arr, along with all student numbers. # Get student numbers into name sorted order. my %sorted; my $sth = $dbhr->prepare("select lastname, firstname, grade from studentall where studnum = ?"); foreach my $studnum ( keys %arr ) { if ( $studnum eq 'defaultppd' ) { next; } # skip default ppd global $sth->execute($studnum); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($ln, $fn,$gr) = $sth->fetchrow; $sorted{$gr}{"$ln$fn$studnum"} = $studnum; } # %sorted contains sorting key and student number value. # Load Configuration Variables: Admin my $sth = $dbhr->prepare("select id, datavalue from conf_system where filename = 'admin'"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $datavalue) = $sth->fetchrow ) { eval $datavalue; if ( $@ ) { print qq{$lex{Error}: $@
\n}; die "$lex{Error}: $@\n"; } } print qq{

School Start:$schoolstart | \n}; print qq{School End:$schoolend

\n}; my $enddate = $schoolend; my $startdate = $schoolstart; # School Days Open my %schooldays = mkSchoolDays( $startdate, $enddate, $dbhr ); # returns hash of schooldays in month. key is yyyy-mm and value is schooldays in month my ($logfile, $shortname, $filename); if ( $pdf ) { # Open TEX file, and print Header $logfile = "pdflog$$.txt"; $shortname = "attendprev$$"; $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; print_tex_doc_start(); # Setup the start of the file. } my $sth = $dbhr->prepare("select * from studentall where studnum = ?"); my $sth5 = $dbhr->prepare("select studnum from student where studnum = ?"); # my $oldroom; # my $curroom = -1; # Watch! Not a value entered for a reason...hopefully. # my $firstflag = 1; # Flag to watch for first "real" person (not withdrawn) foreach my $grade ( sort {$a cmp $b} keys %sorted ) { print qq{
Grade $grade
\n}; foreach my $key ( sort keys %{ $sorted{$grade} } ) { my $studnum = $sorted{$grade}{$key}; # Get student info $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $sref = $sth->fetchrow_hashref; # LaTeX filter the student record foreach my $key ( keys %$sref ) { ( $sref->{$key} ) = latex_filter( $sref->{$key} ); } my %sr = %$sref; my $studentname = qq{$sr{lastname}, $sr{firstname} $sr{initial}}; print qq{
$studentname ($studnum) $sr{grade}
\n}; # $oldroom = $curroom; # $curroom = $sr{grade}; # if ( $curroom ne $oldroom ) { # New Page Heading (band or group changes) # if ( not $firstflag ) { # $firstflag = 0; # End of Header printing section # Print this student's record printStudent( $sref, $dbhr, \%schooldays ); $linecount++; if ( $linecount >= $maxrecords ){ # New page. print TEX "\\newpage\n\n"; $linecount = 0; } } # end of this grade's students # print TEX "\\hfil{\\bf Attended/Enrolled}\\hfil\n"; $linecount = 0; print TEX "\\newpage\n\n"; } # end of grade loop print TEX "% print_tex_end here\n"; print TEX "\\end{document}\n"; close TEX; system("$pdflatex $filename > $logfile"); system("mv $shortname.pdf $currdownloaddir"); system("mv $logfile $currdownloaddir"); system("rm -f $shortname.*"); print qq{
}; print qq{[ }; print qq{$lex{'View/Download'} $lex{'Attendance Report'} ]
\n}; print qq{

[ $lex{Main} |\n}; print qq{$lex{'View Log File'} ]

\n}; print qq{\n}; exit; } end of reportPDF; #---------------------- sub print_tex_doc_start { #---------------------- print TEX "\\documentclass[10pt,legalpaper,oneside,landscape]{article} \\usepackage{array,colortbl,rotating,inputenc} $a_latex_header \\renewcommand{\\familydefault}{\\sfdefault} \\pagestyle{empty} \\setlength{\\textwidth}{13in} \\setlength{\\textheight}{7.7in} \\setlength{\\hoffset}{-4.3in} \\setlength{\\voffset}{-0.8in} \\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 { #------------------- # Up to 18 Cols: (8 + 10 months) # Grade, Lastname, Firstname, Middlename, Birthdate print TEX "\\begin{tabular}{|p{1cm}|p{3cm}|p{1.6cm}|p{2cm}|p{1.8cm}"; # Treaty, Band, Parent, Up to 10 Months. print TEX "|p{1.7cm}|p{2.2cm}|p{3.2cm}|"; foreach my $yrmo ( sort keys %schooldays ){ print TEX "p{9mm}|"; } print TEX "}\\hline\n\n"; # Now print the column header line. print TEX "\\rowcolor[gray]{0.90}{\\bf Grade}"; print TEX "& {\\bf $lex{'Last Name'}} & {\\bf $lex{First}} &"; print TEX "{\\bf $lex{Middle}} &{\\bf $lex{Birthdate}} &"; print TEX "{\\bf IRS\\#} "; # &{\\bf $lex{Band}} & \n"; # print TEX "{\\bf ". $lex{Mother}. "}"; foreach my $yrmo ( sort keys %schooldays ){ my ($ty,$tm) = split(/-/,$yrmo); print TEX "& {\\bf $s_month[$tm]}"; } print TEX "\\\\ \\hline\n\n"; # Now print the schooldays in month print TEX "\\rowcolor[gray]{0.90}"; print TEX "& & & & &". $lex{'School Days'}; foreach my $yrmo ( sort keys %schooldays ){ print TEX "& {\\bf $schooldays{$yrmo}}"; } print TEX "\\\\ \\hline\n\n"; } #--------------- sub printStudent { # Calc attendance, then print record(s) #--------------- my ($sref, $dbh, $schooldaysref ) = @_; my %sr = %$sref; my %schooldays = %$schooldaysref; my $studnum = $sr{studnum}; # print qq{PPD Hash
\n}; # foreach my $key ( sort keys %g_ppd ) { # print qq{Grade:$key PPD:$g_ppd{$key}
\n}; # } my ( %absent, %enrol ); my $periodsperday = $g_ppd{ $sr{grade} }; if ( not $periodsperday ){ $periodsperday = $defaultppd; # 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}:$sr{firstname} $sr{lastname} ($studnum)

\n}; # return; } # Start the table print TEX "\\begin{tabular}{|p{30mm}|p{20mm}|p{20mm}|p{20mm}|p{80mm}|}\n\n"; print TEX "\\multicolumn{3}{l}{\\Large\\bf $sr{lastname}, $sr{firstname} $sr{initial} }\n"; print TEX "& \\multicolumn{2}{l}{ DOB:$sr{birthdate}, IRS\\#:$sr{treaty})}\n\n"; print TEX "\\\\ \\hline\n\n"; # Now print the column header line. print TEX "\\rowcolor[gray]{0.90}{\\bf $lex{Month}}"; print TEX "& {\\bf Teaching} & {\\bf Enrol} & {\\bf Present} &\\bf Absent (Reasons) "; print TEX "\\\\ \\hline\n\n"; my $sth = $dbh->prepare("select absdate, reason from attend where studentid = ? and extract(year_month from absdate) = ? order by absdate,period"); # calculate attendance, one for each month of this school year. my %absent; foreach my $yrmo ( sort keys %schooldays ) { # print qq{SN:$studnum YrMo:$yrmo
\n"; # yearmonth will have yyyy-mm format my $tempyrmo = $yrmo; $tempyrmo =~ s/-//; # remove single dash $sth->execute( $studnum, $tempyrmo ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } #$absrec = calcMonthlyAttendance( $studnum, $yrmo, $periodsperday, # $enddate, \%lexi, $dbh ); while ( my ($absdate, $reason) = $sth->fetchrow ) { if ( $reason =~ m/.*$absentString.*/ ) { $absent{$yrmo}{$reason}++; } } # Print Data Structure for this month. # foreach my $ym ( sort keys %absent ) { # foreach my $key ( sort keys %{ $absent{$ym} } ) { # print qq{YM:$ym RSN:$key VAL: $absent{$ym}{$key}
\n}; # } # } } # Convert data structure from periods into days using $periodsperday value foreach my $ym ( sort keys %absent ) { # print qq{$ym - $sr{firstname} $sr{lastname} ($studnum) PPD:$periodsperday
\n}; foreach my $reason ( sort keys %{ $absent{$ym} } ) { # print qq{$ym: $reason - Count:$absent{$ym}{$reason} ||\n}; $absent{$ym}{$reason} = format_number( $absent{$ym}{$reason} / $periodsperday, 2 ); # print qq{Days:$absent{$ym}{$reason}
\n}; } # print qq{
\n}; } # 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. foreach my $yrmo ( sort keys %schooldays ) { $enrol{ $yrmo }{'days'} = format_number( $enrol{ $yrmo }{'days'}, 1); } # Total Absences for each month my %absenttotal; foreach my $ym ( sort keys %absent ) { my $total; foreach my $key ( sort keys %{ $absent{$ym} } ) { $total += $absent{$ym}{$key}; } $absenttotal{$ym} = $total; } # Now print the rows, one for each month foreach my $yrmo ( sort keys %schooldays ) { my $present = 0; if ( $enrol{$yrmo}{'days'} > 0 ) { $present = $schooldays{$yrmo} - $absenttotal{$yrmo}; } my $presentpercent = 0; if ( $schooldays{$yrmo} and $enrol{$yrmo}{'days'} > 0 ) { $presentpercent = format_number( $present / $schooldays{$yrmo}, 3) * 100; } $presentpercent .= '\%'; # escaped percent symbol print TEX "\\hfil $yrmo & $schooldays{$yrmo} & $enrol{$yrmo}{'days'} & "; print TEX "$present ($presentpercent) &"; foreach my $key ( sort keys %{ $absent{$yrmo} } ) { print TEX "$key - $absent{$yrmo}{$key}\n"; } print TEX " \\\\ \\hline\n"; } print TEX "\\end{tabular}\\bigskip\n\n"; # Now print the Enrollment records. # Start the table print TEX "\\begin{tabular}{|l|l|}\n\n"; print TEX "\\multicolumn{2}{l}{\\large\\bf $lex{Enrollment}}\n"; print TEX "\\\\ \\hline\n\n"; # Now print the column header line. print TEX "\\rowcolor[gray]{0.90}{\\bf $lex{Date}}"; print TEX "& {\\bf $lex{Type}} "; print TEX "\\\\ \\hline\n\n"; # print the enrollment records, reverse order. my $sth = $dbh->prepare("select * from transfer where studnum = ? order by date"); $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %t = %$ref; print TEX qq{$t{date} & $t{type} }; print TEX qq{ \\\\ \\hline\n}; } print TEX "\\end{tabular}\\bigskip\n\n"; } # end of printStudent