#! /usr/bin/perl # Copyright 2001-2022 Leslie Richardson # This file is part of Open Admin for Schools. # A single site version of central site rptatt2global.pl - May 2022 my $percentsymbol = '%'; my %lex = ('Attendance Report' => 'Attendance Report', 'Main' => 'Main', 'Error' => 'Error', 'Continue' => 'Continue', 'Grade' => 'Grade', 'Student' => 'Student', 'HTML' => 'HTML', 'Report Type' => 'Report Type', 'Missing' => 'Missing', 'Days Open' => 'Days Open', 'Total' => 'Total', 'Month' => 'Month', 'Average' => 'Average', 'Division' => 'Division', 'Periods per Day' => 'Periods per Day', 'Select' => 'Select', 'School' => 'School', 'CSV' => 'CSV', 'Show' => 'Show', 'Gender' => 'Gender', 'Raw' => 'Raw', 'Results' => 'Results', 'Percentage' => 'Percentage', 'Database' => 'Database', 'Attendance' => 'Attendance', ); my $self = 'rptAttStat2.pl'; my $oabase = '/opt/openadmin'; use DBI; use CGI; use Number::Format qw(:all); # Constants # Grade Divisions: map Grade => Division. my %grDiv = ('K' => '1', '1' => '1', '2' => '1', '3' => '1', '4' => '2', '5' => '2', '6' => '2', '7' => '3', '8' => '3', '9' => '3', '10' => '4', '11' => '4', '12' => '4'); my %skipgrades = ('K' => '1', 'PK' => '1'); # grades to skip over. my $defmaxrecords = 28; # Maximum records per PDF page. my $group = 'grade'; my $pdf = '1'; my $html; # Read config variables eval require "../../etc/admin.conf"; 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 "$globdir/global.conf"; }; if ( $@ ) { print $lex{Error}. " $self: $@
\n"; die $lex{Error}. "$self: $@\n"; } # @dbase = sort keys %alldbase; # global records. #eval require "../../lib/liblatex.pl"; #if ( $@ ) { # print $lex{Error}. ": $@
\n"; # die $lex{Error}. ": $@\n"; #} my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); my $dbtype = 'mysql'; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; #my ($dsn, $dbh, $schoolname ); #if ( $arr{db} ) { # my $database = $arr{db}; # $dsn = "DBI:$dbtype:dbname=$database"; # $dbh = DBI->connect($dsn,$guser,$gpassword); # $schoolname = $alldbase{"$arr{db}"}; # Get the g_ppd Record from configuration system # my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?"); # $sth->execute( 'g_ppd' ); # my $datavalue = $sth->fetchrow; # eval $datavalue; # if ( $@ ) { # print $lex{Error}. " $@
\n"; # die $lex{Error}. " $@\n"; # } #} 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"; # HTML Header my $title = "Statistical $lex{'Attendance Report'} - Current"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{ $lex{Attendance} ]\n}; print qq{

$title

}; #if ( $schoolname ) { print qq{ - $schoolname}; } # print qq{\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; if ( $arr{reporttype} eq 'html' ) { mkHTMLReport(); } elsif ( $arr{reporttype} eq 'csv' ) { mkCSVReport(); } } #--------------- sub mkHTMLReport { #--------------- my $enddate; if ($arr{date}){ $enddate = $arr{date}; } else { $enddate = $currsdate; } my $startdate = $schoolstart; # print qq{Start:$startdate End:$enddate DBH:$dbh
\n}; # Functions from LibAttend # Uses $schoolstartdate for starting date. %schooldays = mkSchoolDays( $startdate, $enddate, $dbh ); my @schoolmonths = sort keys %schooldays; # print qq{School Months}, @schoolmonths, qq{
\n}; # returns hash of schooldays in month. key is yyyy-mm and value is schooldays in month #~~ print qq{School Days", %schooldays, "
\n}; # Find the Students, first get current students my %students; my $sth = $dbh->prepare("select studid, studnum from student"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $studnum) = $sth->fetchrow ) { $students{$studnum} = 1; } # select to see if they have any student record. my $sth1 = $dbh->prepare("select count(*) from studentall where studnum = ?"); # check for attendance records my $sth2 = $dbh->prepare("select count(*) from attend where studentid = ?"); # now find any withdrawn ones, that were here this year. my $sth = $dbh->prepare("select id, studnum from transfer where type = 'withdraw' and to_days( date ) >= to_days( '$startdate' ) and to_days( date ) <= to_days( '$enddate' )"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $studnum) = $sth->fetchrow ) { # Check to make sure they have a student record $sth1->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; $sth2->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $attcount = $sth2->fetchrow; if ( not $count and $attcount ) { # have attendance recs but no student rec print qq{$lex{Error}: Attendance records exist for missing student: $studnum
\n}; } if ( $count ) { $students{$studnum} = 1; } } my %studgrade; my %gender; $sth = $dbh->prepare("select grade, sex from studentall where studnum = ?"); foreach my $studnum ( keys %students ) { $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($grade, $gender) = $sth->fetchrow; if ( $skipgrades{$grade} ) { next; } # next student if ( not $grade ) { print qq{$lex{Missing} $lex{Grade}. $lex{Student}: $studnum
\n}; #delete $students{$studnum}; # No Grade = Remove. } else { # put in the grade value if ( not $studgrade{$grade} ) { $studgrade{$grade} = []; } push @{ $studgrade{$grade} }, $studnum; $gender{$studnum} = $gender; } } # use Data::Dumper; # print Dumper %studgrade; my %lexi = ('Absent' => 'Absent', 'Late' => 'Late'); # Now Loop through all grades my %attend = (); my %genderatt = (); my %divatt = (); # Division Attendance my $failflag = (); # fail before printing. foreach my $grade ( sort {$a <=> $b} keys %studgrade ) { my $ppd = $g_ppd{ $grade }; if ( not $ppd ) { print qq{$lex{Missing} $lex{'Periods per Day'} - $lex{Grade}:$grade:
\n}; $failflag = 1; # fail before output. } my $division = $grDiv{$grade}; # The Grade Division 1-4 # Students in this grade foreach my $studnum ( @{ $studgrade{$grade} } ) { my $sex = lc( $gender{$studnum}); my $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh ); my %enrolYM = %$ref; # format: $enrolYM{yearmonth}-> start, end, days. foreach my $ym ( sort keys %enrolYM ) { my $result = calcMonthlyAttendance( $studnum, $ym, $ppd, '',\%lexi, $dbh ); # no end date req'd. my ($absent, $late) = split(/:/, $result); my $enrolled = $enrolYM{$ym}->{days}; my $present = $enrolled - $absent; # print qq{SN:$studnum - $result - $absent - $present
\n}; $attend{$grade}{$ym}->{attend} += $present; $attend{$grade}{$ym}->{enrol} += $enrolled; $genderatt{$grade}{$ym}{$sex}->{attend} += $present; $genderatt{$grade}{$ym}{$sex}->{enrol} += $enrolled; $divatt{$division}{$ym}->{attend} += $present; $divatt{$division}{$ym}->{enrol} += $enrolled; } } # end of student loop } #use Data::Dumper; #print Dumper %attend; #print qq{

\n}; if ( $failflag ) { print qq{\n}; exit; } # Now print it out! # First Row: Days Open, Then each grade for each month; Year:Month along the top. print qq{\n}; print qq{}; for my $idx ( 1 .. $#schoolmonths ) { print qq{}; } print qq{\n}; # Days Open print qq{}; my $totaldays; for my $idx ( 1 .. $#schoolmonths ) { my $ym = $schoolmonths[$idx]; print qq{}; $totaldays += $schooldays{$ym}; } print qq{\n}; # Grades my (%monthAtt, %monthEn); my $highestgrade; # track largest grade value for division printing foreach my $grade ( sort {$a <=> $b} keys %studgrade ) { $highestgrade = $grade; if ( $grade == 4 or $grade == 7 or $grade == 10 ) { # print division line my $div = $grDiv{ $grade - 1 }; if ( %{$divatt{$div}} and $arr{showdivision} ) { # we have values to print my ( $divAttend, $divEnrolled ); print qq{}; for my $idx ( 1 .. $#schoolmonths ) { my $ym = $schoolmonths[$idx]; my $attend = $divatt{$div}{$ym}->{attend}; my $enrolled = $divatt{$div}{$ym}->{enrol}; # for totals at far right of row $divAttend += $attend; $divEnrolled += $enrolled; my $average; if ( $enrolled ) { $average = format_number( $attend / $enrolled * 100, 1,1); } print qq{}; } # Division totals my $divAverage; if ( $divEnrolled ) { $divAverage = format_number( $divAttend / $divEnrolled * 100, 1,1); } print qq{\n}; } } # end of Division printing Row my ($totalAttend, $totalEnrolled); print qq{}; for my $idx ( 1 .. $#schoolmonths ) { my $ym = $schoolmonths[$idx]; my $attend = $attend{$grade}{$ym}->{attend}; my $enrolled = $attend{$grade}{$ym}->{enrol}; # print qq{GR:$grade YM:$ym ATT:$attend EN:$enrolled
\n}; my $boyattend = $genderatt{$grade}{$ym}{'m'}->{attend}; my $boyenrolled = $genderatt{$grade}{$ym}{'m'}->{enrol}; my $girlattend = $genderatt{$grade}{$ym}{'f'}->{attend}; my $girlenrolled = $genderatt{$grade}{$ym}{'f'}->{enrol}; $totalAttend += $attend; $totalEnrolled += $enrolled; $monthAtt{$ym} += $attend; $monthEn{$ym} += $enrolled; my ($boyaverage, $girlaverage); if ( $boyenrolled ) { $boyaverage = format_number( $boyattend / $boyenrolled * 100, 1,1); } if ( $girlenrolled ) { $girlaverage = format_number( $girlattend / $girlenrolled * 100, 1,1); } my $average; if ( $enrolled ) { $average = format_number( $attend / $enrolled * 100, 1,1); } print ''; } # Grade totals my $gradeAverage; if ( $totalEnrolled ) { $gradeAverage = format_number( $totalAttend / $totalEnrolled * 100, 1,1); } print qq{\n}; } # Division Row my $div = $grDiv{ $highestgrade }; if ( %{$divatt{$div}} and $arr{showdivision} ) { # we have values to print my ( $divAttend, $divEnrolled ); print qq{}; for my $idx ( 1 .. $#schoolmonths ) { my $ym = $schoolmonths[$idx]; my $attend = $divatt{$div}{$ym}->{attend}; my $enrolled = $divatt{$div}{$ym}->{enrol}; # for totals at far right of row $divAttend += $attend; $divEnrolled += $enrolled; my $average; if ( $enrolled ) { $average = format_number( $attend / $enrolled * 100, 1,1); } print qq{}; } # Division totals my $divAverage; if ( $divEnrolled ) { $divAverage = format_number( $divAttend / $divEnrolled * 100, 1,1); } print qq{\n}; } # end of Division row # Monthly Stats: print qq{}; for my $idx ( 1 .. $#schoolmonths ) { my $ym = $schoolmonths[$idx]; if ( $monthEn{$ym} ) { $monthAverage = format_number( $monthAtt{$ym} / $monthEn{$ym} * 100, 1,1); } $gtotalAtt += $monthAtt{$ym}; $gtotalEn += $monthEn{$ym}; print qq{\n}; } if ( $gtotalEn ) { $gtotalAverage = format_number( $gtotalAtt / $gtotalEn * 100, 1,1); } print qq{\n}; print qq{
$schoolmonths[$idx]$lex{Total}
$lex{'Days Open'}$schooldays{$ym}$totaldays
$lex{Division} $div}; if ( $arr{showraw} ) { print qq{$attend / $enrolled }; } if ( $arr{showpercent} ) { print qq{ $average$percentsymbol }; } print qq{}; if ( $arr{showraw} ) { print qq{$divAttend / $divEnrolled }; } if ( $arr{showpercent} ) { print qq{ $divAverage$percentsymbol }; } print qq{
$lex{Grade} $grade'; if ( $arr{showraw} ) { $attend = round( $attend, 1); $enrolled = round( $enrolled, 1); print qq{$attend / $enrolled}; } if ( $arr{showpercent} ) { print qq{ $average$percentsymbol }; } if ( $arr{showgender} ) { print qq{
B:$boyaverage$percentsymbol G:$girlaverage$percentsymbol}; } print '		}; $totalAttend = round( $totalAttend, 1); $totalEnrolled = round( $totalEnrolled, 1); if ( $arr{showraw} ) { print qq{$totalAttend / $totalEnrolled }; } if ( $arr{showpercent} ) { print qq{ $gradeAverage$percentsymbol}; } print qq{
$lex{Division} $div}; if ( $arr{showraw} ) { print qq{$attend / $enrolled }; } if ( $arr{showpercent} ) { print qq{ $average$percentsymbol }; } print qq{}; if ( $arr{showraw} ) { print qq{$divAttend / $divEnrolled }; } if ( $arr{showpercent} ) { print qq{ $divAverage$percentsymbol }; } print qq{
$lex{Month} $lex{Average}}; if ( $arr{showraw} ) { $monthAtt{$ym} = round( $monthAtt{$ym}, 2); $monthEn{$ym} = round( $monthEn{$ym}, 2); print qq{$monthAtt{$ym} / $monthEn{$ym} }; } if ( $arr{showpercent} ) { print qq{ $monthAverage$percentsymbol }; } print qq{}; if ( $arr{showraw} ) { $gtotalAtt = round( $gtotalAtt, 1); $gtotalEn = round( $gtotalEn, 1); print qq{$gtotalAtt / $gtotalEn }; } if ( $arr{showpercent} ) { print qq{ $gtotalAverage$percentsymbol }; } print qq{
\n}; print qq{

 

\n}; print qq{\n}; } #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{'Report Type'}
$lex{Show} $lex{Gender} $lex{Results}
$lex{Show} $lex{Raw} $lex{Results}
$lex{Show} $lex{Percentage} $lex{Results}
$lex{Show} $lex{Division}
\n}; print qq{\n}; exit; }