#!/usr/bin/perl # Copyright 2001-2019 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. # Monthly report for Enrollment change. # PDF Output # Version 2 for Grade/Homeroom reporting. my %lex = ('Monthly Enrollment' => 'Monthly Enrollment', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', 'Main' => 'Main', 'Blank=Previous Month' => 'Blank=Previous Month', 'No Enrollment Changes' => 'No Enrollment Changes', 'Student' => 'Student', 'Reason' => 'Reason', 'Teacher' => 'Teacher', 'Grade' => 'Grade', 'Error' => 'Error', 'Continue' => 'Continue', 'Month' => 'Month', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Font Size' => 'Font Size', 'Report' => 'Report', 'Days Open' => 'Days Open', 'Homeroom' => 'Homeroom', 'Missing' => 'Missing', 'Periods Per Day' => 'Periods Per Day', ); # show Principal Signature Line; set to 0 to turn off my $teacherSign = 1; my $self = 'rptenrol2.pl'; use DBI; use CGI; use Time::JulianDay; use Number::Format qw(:all); eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } eval require "../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@
\n"; } eval require "../lib/libattend.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@
\n"; } # Used for the category titles my $shortname = "rptenrol1$$"; # my $shortname = "test"; my $fileName = "$shortname.tex"; my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # Database setup my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Set Dates #my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, # $iddst) = localtime(time); #$year = $year + 1900; #$mon++; #$wday++; #my $currdate = "$year-$mon-$mday"; # Set Date my @tim = localtime(time); my $cyear = @tim[5] + 1900; my $cmonth = @tim[4] + 1; my $cday = @tim[3]; my $currdate = "$cyear-$cmonth-$cday"; my $currjd = julian_day( split('-', $currdate) ); my $curryrmo = "$cyear-$cmonth"; # Print Page Head. my $title = "$lex{Homeroom} $lex{'Monthly Enrollment'} $lex{Report}"; print qq{$doctype\n$title\n}; print qq{\n}; if ( not $arr{page} ) { print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$schoolname
$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; showReport(); } #---------------- sub showStartPage { #---------------- # Get the homerooms. my (%homerooms, %staffname); my $sth = $dbh->prepare("select userid, field_value from staff_multi where field_name = 'homeroom'"); my $sth1 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($userid, $homeroom ) = $sth->fetchrow ) { $sth1->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth1->fetchrow; $homerooms{$homeroom} = $userid; $staffname{$userid} = qq{$lastname, $firstname}; } # Setup Year-Months. my (@months, %months ); my ($sy, $sm, $sd) = split('-', $schoolstart); # schoolstart is global var from config. my $yrmo = "$sy-$sm"; push @months, $yrmo; $months{$yrmo} = "$s_month[$sm]-$sy"; for my $i (1..10) { my $mo = $sm + $i; my $yr = $sy; if ( $mo > 12 ) { $mo = $mo - 12; $yr++; } if ( length $mo == 1 ) { $mo = '0'. $mo; } my $yrmo = "$yr-$mo"; push @months, $yrmo; $months{$yrmo} = "$s_month[$mo]-$yr"; if ( $yr == $cyear and $mo == $cmonth ) { # done last; } } print qq{
\n}; print qq{\n}; print qq{\n}; # Get Homeroom print qq{}; print qq{\n}; print qq{\n}; # Get Month print qq{}; print qq{\n}; print qq{\n}; # Font size print qq{\n}; print qq{\n}; print qq{
$lex{Homeroom}
$lex{Month}
$lex{'Font Size'}\n}; print qq{
$lex{'Paper Size'}}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } #-------------- sub showReport { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # exit; # Passed: homeroom, month, papersize, fontsize if ( not $arr{month} or not $arr{homeroom}) { print qq{

No Month or Homeroom Selected

\n}; print qq{\n}; exit; } my $homeroom = $arr{homeroom}; # to simplify expressions. # Get Teacher Name for this homeroom. my $sth = $dbh->prepare("select userid from staff_multi where field_value = ? and field_name = ?"); $sth->execute( $homeroom, 'homeroom' ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $userid = $sth->fetchrow; my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute($userid); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth->fetchrow; my $startdate = $arr{month}. '-01'; # start of the month. my $startjd = julian_day( split('-', $startdate )); my ($year,$month) = split('-', $arr{month}); my $yearmonth = $arr{month}; # format YYYY-MM # Get end of month date, and then days open my @mdays = (0,31,28,31,30,31,30,31,31,30,31,30,31); if ($month == 2) { if ($year % 4 != 0) { $mdays[2] = 28; } elsif ($year % 400 == 0) { $mdays[2] = 29; } elsif ($year % 100 == 0) { $mdays[2] = 28; } else { $mdays[2] = 29; } } my $enddate = $arr{month}. qq{-$mdays[$month]}; # end of the month. # print "End Date:$enddate\n"; # Do Days Open for both including the PK/K system. my $daysopen = calcDaysOpen($startdate,$enddate, $dbh); # print qq{
Days Open: $daysopen
\n}; # check for any nsd (non school days) for PK/K my %daysopennsd; my $sth = $dbh->prepare("select distinct homeroom from dates_nonschool where homeroom is not NULL and homeroom != ''"); $sth->execute; while ( my $homeroom = $sth->fetchrow ) { $daysopennsd{$homeroom} = calcDaysOpen($startdate,$enddate, $dbh, $homeroom); # print qq{
Homeroom:$homeroom Open: $daysopennsd{$homeroom}
\n}; } # Get Current Students and then move backwards to starting date to get students at that point. my %students; my $sth = $dbh->prepare("select studnum from student where studnum is not NULL and studnum != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { $students{$studnum} = 1; } $sth = $dbh->prepare("select studnum, type from transfer where to_days(date) >= to_days('$startdate') order by date desc"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum, $type) = $sth->fetchrow ) { if ( $type eq 'withdraw' ) { # add the student, since they must have been present earlier $students{$studnum} = 1; } else { # since enrolling we must remove them, since were not there earlier. delete $students{$studnum}; } } # The %students hash should now have all students that were enrolled on the first of the month of interest. # Get the enrollment counts for these %students by grade and room. my %grRooms; my $sth = $dbh->prepare("select grade, homeroom, sex from studentall where studnum = ?"); my %gender; # %gender{homeroom} foreach my $studnum ( keys %students ) { $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($grade, $homeroom, $sex ) = $sth->fetchrow; $gender{$homeroom}{$sex}++; $grRooms{$grade}{$homeroom}++; # not needed. } # Test # foreach my $gr ( sort {$a <=> $b} keys %grRooms ) { # foreach my $hr ( sort {$a <=> $b} keys %{ $grRooms{$gr}} ) { # print "Grade $gr Homeroom $hr Count:$grRooms{$gr}{$hr}
\n"; # } # } # We now have enrollment counts for the start of the month, along with those students. # Now find the enrollment changes for this month only. my %changesMo; my $sth1 = $dbh->prepare("select homeroom, sex from studentall where studnum = ?"); my $sth = $dbh->prepare("select * from transfer where month(date) = $month and year(date) = $year order by date desc"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $tref = $sth->fetchrow_hashref ) { my %t = %$tref; # Get Grade, Homeroom, Sex for this student $sth1->execute( $t{studnum} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ( $hr, $sex) = $sth1->fetchrow; if ( $hr ne $homeroom ) { next; } # skip if not this homeroom if ( $t{type} eq 'withdraw' ) { $changesMo{'wd'}{$sex}++; } else { $changesMo{'en'}{$sex}++; } } # We now have all enrollment changes for the month by gender, also # Show Month print qq{

$firstname $lastname - $month[$month] $year

\n}; # Show Days Open # print qq{

Global $lex{'Days Open'}:$daysopen }; my %kdays; if ( %daysopennsd ) { # combine if possible foreach my $homeroom ( sort keys %daysopennsd ) { my $dopen = $daysopennsd{$homeroom}; if ( $kdays{$dopen} ) { # append. $kdays{$dopen} .= ",$homeroom"; } else { $kdays{$dopen} = $homeroom; } } # print "
K/PK Days Open: "; # foreach my $dopen ( sort keys %kdays ) { # print "$kdays{$dopen} - $dopen Days
\n"; # } # print qq{

\n}; } open(TEX,">$fileName") || die "Failed tex file open"; # Set Sizes # Letter paper size is 279mm x 216mm # 200mm wide - 50mm name, 20mm extra fld, leaves 130mm for columns. # a4 paper is 297mm x 210mm; legal is 356mm x 216mm my $fontsize = $arr{fontsize}. 'pt'; 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; } delete $arr{papersize}; # no longer needed. print TEX "\\documentclass[$fontsize,$papersize]{article} \\usepackage{array,inputenc,rotating,colortbl,helvet} %\\usepackage[landscape]{geometry} %$a_latex_header \\renewcommand{\\familydefault}{\\sfdefault} \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\hoffset}{-32mm} \\setlength{\\voffset}{-25mm} \\setlength{\\parindent}{0pt} \\setlength{\\tabcolsep}{5pt} \\renewcommand{\\familydefault}{\\sfdefault} \\setlength{\\extrarowheight}{6pt}\n"; print TEX "\\begin{document}\\begin{center}\n"; print TEX "{\\LARGE\\sf $schoolname} \n\n$title\n\n"; print TEX "\\medskip\n{\\Large\\sf $firstname $lastname - $homeroom}\\hfil"; print TEX "{\\Large\\sf $month[$month] $year}\\hfill\n\n"; print TEX "\\hrulefill\n\\medskip\n\n"; # # Show Days Open # print TEX "{\\bf Global $lex{'Days Open'}} $daysopen \\\\ \n"; =head if ( %daysopennsd ) { print TEX "\\hspace{6mm}{\\bf K/PK Days Open} "; foreach my $dopen ( sort keys %kdays ) { print TEX "$kdays{$dopen} - $dopen Days\\hspace{6mm}\n"; } # foreach my $homeroom ( sort keys %daysopennsd ) { # print TEX qq{\\hspace{6mm} {\\bf $homeroom} $lex{'Days Open'} $daysopennsd{$homeroom}\n}; #} } print TEX "\\\\\n\\hrulefill\\medskip\n\n"; print TEX "\\begin{tabular}{|l"; foreach my $grade ( sort {$a <=> $b} keys %grRooms ) { foreach my $homeroom ( sort {$a <=> $b} keys %{ $grRooms{$grade}} ) { print TEX "|c"; } } print TEX "|} \\hline\n\n"; # Grade Headings print TEX qq{\\rowcolor[gray]{0.92} Grades }; foreach my $grade ( sort {$a <=> $b} keys %grRooms ) { foreach my $homeroom ( sort {$a <=> $b} keys %{ $grRooms{$grade}} ) { print TEX "& $grade "; } } print TEX "\\\\ \\hline\n\n"; # Homeroom Headings print TEX qq{\\rowcolor[gray]{0.92} Homeroom }; foreach my $grade ( sort {$a <=> $b} keys %grRooms ) { foreach my $homeroom ( sort {$a <=> $b} keys %{ $grRooms{$grade}} ) { if ( $homeroom eq 'none' ) { $homeroom = ' '; } print TEX "& $homeroom"; } } print TEX "\\\\ \\hline\n\n"; # Start of Month enrollments my $totalstart; print TEX qq{Month Start }; foreach my $grade ( sort {$a <=> $b} keys %grRooms ) { foreach my $homeroom ( sort {$a <=> $b} keys %{ $grRooms{$grade}} ) { print TEX "& $grRooms{$grade}{$homeroom} "; $totalstart += $grRooms{$grade}{$homeroom}; } } print TEX "\\\\ \\hline\n\n"; =cut # End Centering print TEX "\\end{center}\n\n"; # Start Tabular and Title print TEX "\\begin{tabular}{|l|c|c|c|} \\hline\n\n"; print TEX qq{\\rowcolor[gray]{0.92} $homeroom Enrollment & Boys & Girls & Total \\\\ \\hline\n\n }; my $startboys = $gender{ $homeroom }{M}; my $startgirls = $gender{ $homeroom }{F}; my $starttotal = $startboys + $startgirls; # Start of Month enrollments print TEX "Starting Enrollment - beginning of Month & $startboys & $startgirls & $starttotal \\\\ \\hline\n\n"; my $endboys = $startboys + $changesMo{en}{M} - $changesMo{wd}{M}; my $endgirls = $startgirls + $changesMo{en}{F} - $changesMo{wd}{F}; my $endtotal = $endboys + $endgirls; # End of Month enrollments print TEX "Ending Enrollment - end of Month & $endboys & $endgirls & $endtotal \\\\ \\hline\n\n"; print TEX "\\end{tabular}\n\\bigskip\n\n"; my (%enrolMonth, %withdrawMonth); my $sth1 = $dbh->prepare("select * from studentall where studnum = ?"); my $sth = $dbh->prepare("select * from transfer where month(date) = $month and year(date) = $year order by date"); # New Enrollments print TEX "{\\Large Enrollments}\\smallskip\n\n"; print TEX "\\begin{tabular}{|l|l|l|l|l|} \\hline\n\n"; print TEX qq{\\rowcolor[gray]{0.92} Name & Treaty & Birthdate & Date & Transfer from \\\\ \\hline\n\n }; my $first = 1; $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $tref = $sth->fetchrow_hashref ) { my %t = %$tref; if ( $t{type} eq 'withdraw' ) { next; } # skip any withdrawals # Get Student Record $sth1->execute( $t{studnum} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $sref = $sth1->fetchrow_hashref; my %s = %$sref; if ( $s{homeroom} ne $homeroom ) { next; } # skip if not this homeroom print TEX "$s{firstname} $s{lastname} & $s{treaty} & $s{birthdate} & $t{date} & $t{description} "; print TEX "\\\\ \\hline\n\n"; $first = 0; } if ( $first ) { # no enrollments; print TEX "\\multicolumn{5}{|l|}{No Enrollments Found} \\\\ \\hline\n\n"; } print TEX "\\end{tabular}\\bigskip\n\n"; # New Withdrawals print TEX "{\\Large Withdrawals}\\smallskip\n\n"; print TEX "\\begin{tabular}{|l|l|l|l|l|} \\hline\n\n"; print TEX qq{\\rowcolor[gray]{0.92} Name & Treaty & Birthdate & Date & Transfer to \\\\ \\hline\n\n }; my $first = 1; $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $tref = $sth->fetchrow_hashref ) { my %t = %$tref; if ( $t{type} ne 'withdraw' ) { next; } # skip any enrollments # Get Student Record $sth1->execute( $t{studnum} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $sref = $sth1->fetchrow_hashref; my %s = %$sref; if ( $s{homeroom} ne $homeroom ) { next; } # skip if not this homeroom print TEX "$s{firstname} $s{lastname} & $s{treaty} & $s{birthdate} & $t{date} & $t{description} "; print TEX "\\\\ \\hline\n\n"; $first = 0; } if ( $first ) { # no withdrawals print TEX "\\multicolumn{5}{|l|}{No Withdrawals Found} \\\\ \\hline\n\n"; } print TEX "\\end{tabular}\\bigskip\n\n"; # If K/PK, reset the $daysopen variable. if ( $daysopennsd{$homeroom} ) { # $homeroom is passed $daysopen = $daysopennsd{$homeroom}; } # Attendance rows. Total Pupil Days Attended / Total Pupil Days Enrolled. # Loop through all students, set enrolled and attended. my (%enrol, %attend ); # %enrol{grade}{homeroom} = count of total pupil days. # For finding attendance via calcMonthlyAttendance, below my %lexi = ('Absent' => 'Absent', 'Late' => 'Late'); my $sth = $dbh->prepare("select grade, homeroom from studentall where studnum = ?"); my ($totaldayenrol, $totaldayattend); foreach my $studnum ( keys %students ) { # Get their grade and homeroom $sth->execute( $studnum ) ; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($grade, $hr) = $sth->fetchrow; if ( $hr ne $homeroom ) { next; } # skip any not in this homeroom. my $ppd = $g_ppd{ $grade }; if ( not $ppd ) { print qq{

$lex{Missing} Attendance $lex{'Periods Per Day'}: $lex{Grade} }; print qq{$grade Student $studnum

\n}; } # get enrollment my $ref; if ( $grade eq 'K' or $grade eq 'PK' ) { $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh, $homeroom); } else { # normal enrollment check. # Calculate the days enrolled for this student $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh); } if ( not $ref ) { next; } #student my $schooldays = $ref->{$yearmonth}->{'days'}; if ( not $schooldays ) { next; } #student $enrol{$homeroom} += $schooldays; # add days enrolled to this. $totaldayenrol += $schooldays; # total pupil-days enrolled # get attendance my $result = calcMonthlyAttendance( $studnum, $yearmonth, $ppd, '',\%lexi, $dbh ); # no end date req'd. my ($absent, $late) = split(':', $result); my $dayspresent; if ( $grade eq 'K' or $grade eq 'PK' ) { # nsd days open my $daysopen = $daysopennsd{$homeroom}; $dayspresent = $daysopen - $absent; } else { $dayspresent = $daysopen - $absent; } $attend{$homeroom} += $dayspresent; $totaldayattend += $dayspresent; # print qq{SN:$studnum Grade:$grade Enrolled:$schooldays Present:$dayspresent Absent:$absent
\n}; } # We now have total pupil-days enroll and pupil-days attended in %attend and %enrol for each room. print TEX "{\\Large Attendance}\\smallskip\n\n"; print TEX "\\begin{tabular}{|l|c|} \\hline\n\n"; print TEX qq{\\rowcolor[gray]{0.92} Days Open & $daysopen \\\\ \\hline\n\n }; print TEX qq{\\rowcolor[gray]{0.92} Student-Days Enrolled & $enrol{$homeroom} \\\\ \\hline\n\n }; print TEX qq{\\rowcolor[gray]{0.92} Student-Days Attended & $attend{$homeroom} \\\\ \\hline\n\n }; my $percent = 0; if ( $enrol{$homeroom} ) { $percent = round(100 * $attend{$homeroom} / $enrol{$homeroom}, 1); } print TEX qq{\\rowcolor[gray]{0.92} Percent Attendance & $percent\\% \\\\ \\hline\n\n }; print TEX qq{\\end{tabular}\\bigskip\n\n}; =head # Pupil Days Enrolled print TEX qq{Days Enrolled}; print TEX "$enrol{$homeroom}\n\n"; # print TEX "\\\\ \\hline\n\n"; # Pupil Days Attended print TEX qq{Days Attended}; print TEX "$attend{$homeroom}\n\n"; # print TEX "\\\\ \\hline\n\n"; # Percentage Attendance print TEX qq{\\rowcolor[gray]{0.92} Percent Attendance}; foreach my $grade ( sort {$a <=> $b} keys %grRooms ) { foreach my $homeroom ( sort {$a <=> $b} keys %{ $grRooms{$grade}} ) { my $enrol = $enrol{$grade}{$homeroom}; my $attend = $attend{$grade}{$homeroom}; my $percent = 0; if ( $enrol ) { $percent = round(100 * $attend / $enrol, 1); } print TEX "& $percent\\% "; } } print TEX "\\\\ \\hline\n\n"; print TEX "\\end{tabular}\n\n"; print TEX "\\medskip\n\n"; # Now do the total table. print TEX qq{\\begin{tabular}{|l|l|} \\hline\n}; print TEX qq{Students at Start of Month&$totalstart \\\\ \\hline\n}; print TEX qq{Students at End of Month & $totalend \\\\ \\hline\n}; print TEX qq{Students Enrolled during Month & $totalenrol \\\\ \\hline\n}; print TEX qq{Students Withdrawn during Month & $totalwd \\\\ \\hline\n}; print TEX qq{Total Pupil Days Enrolled & $totaldayenrol \\\\ \\hline\n}; print TEX qq{Total Pupil Days Attended & $totaldayattend \\\\ \\hline\n}; my $percent = 0; if ( $totaldayenrol ) { $percent = round(100 * $totaldayattend / $totaldayenrol, 1); } print TEX qq{Percentage Attendance & $percent\\% \\\\ \\hline\n}; =cut # print TEX qq{\\end{tabular}\n}; if ( $teacherSign ) { print TEX "\n\\vspace{10mm}\n\\medskip\n\\underline{\\hspace{3in}}\n\n"; print TEX "{\\footnotesize $lex{Teacher} }\n\n"; } print TEX "\\end{document}\n"; close TEX; system("$pdflatex $fileName >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print qq{

\n}; print qq{$lex{'View/Download'} $lex{'Monthly Enrollment'} $lex{Report}

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