#! /usr/bin/perl # Copyright 2001-2022 Leslie Richardson # This file is part of Open Admin for Schools. # Based on the central site rptatt3global.pl 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', 'CSV' => 'CSV', 'Show' => 'Show', 'Gender' => 'Gender', 'Raw' => 'Raw', 'Results' => 'Results', 'Percentage' => 'Percentage', 'Database' => 'Database', 'Not Found' => 'Not Found', 'Remote' => 'Remote', 'Attendance' => 'Attendance', ); my $self = 'rptAttStat3.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. 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"; # HTML Header my $title = qq{Statistical $lex{'Attendance Report'} - Previous Years}; print qq{$doctype\n$title \n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{ $lex{Attendance} ]\n}; print qq{

$title

\n}; #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 { #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( $arr{showk} ) { undef %skipgrades; delete $arr{showk}; } # Load Remote database if ( not $arr{db} ) { print qq{

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

\n}; print qq{\n}; exit; } my $db = $arr{db}; my $dsn = "DBI:mysql:database=$db;host=$remotehost"; my $dbh = DBI->connect($dsn,$remoteuser,$remotepassword); # Load Configuration Variables; 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 ) { # print qq{ID:$id Val:$datavalue
\n}; 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; # Functions from LibAttend %schooldays = mkSchoolDays( $startdate, $enddate, $dbh ); my @schoolmonths = sort keys %schooldays; #~~ print qq{School Months", @schoolmonths, "
\n}; # returns hash of schooldays in month. key is yyyy-mm and value is schooldays in month #~~ print qq{School Days", %schooldays, "
\n}; =head1 if ( $pdf ) { # Open TEX file, and print Header $logfile = "pdflog$$.txt"; $shortname = "inac$$"; $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; print_tex_doc_start(); # Setup the start of the file. } =cut # 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; $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; } } } #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}; 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); } print qq{\n}; $gtotalAtt += $monthAtt{$ym}; $gtotalEn += $monthEn{$ym}; } 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} ) { print qq{$attend / $enrolled}; } if ( $arr{showpercent} ) { print qq{ $average$percentsymbol }; } if ( $arr{showgender} ) { print qq{
B:$boyaverage$percentsymbol G:$girlaverage$percentsymbol}; } print '
}; 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} ) { print qq{$monthAtt{$ym} / $monthEn{$ym} }; } if ( $arr{showpercent} ) { print qq{ $monthAverage$percentsymbol }; } print qq{}; if ( $arr{showraw} ) { print qq{$gtotalAtt / $gtotalEn }; } if ( $arr{showpercent} ) { print qq{ $gtotalAverage$percentsymbol }; } print qq{
\n}; print qq{

 

\n}; print qq{\n}; } #---------------- sub showStartPage { #---------------- # Get remote databases and years my $remotedbase = 'information_schema'; my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost"; my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword); #my $dbh = DBI->connect($dsn,'tsecremote','ts99ec4'); 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 ) { # print qq{DB:$db
\n}; if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; } if ( $db =~ m/$dbase/ ) { # print qq{Match:$db
\n}; $remotedb{$db} = 1; } } 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{\n}; print qq{\n}; print qq{\n}; foreach my $db ( sort keys %remotedb ) { 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}
$lex{Show} K,PK
$lex{Select} $lex{Remote} $lex{Database}
$db
\n}; print qq{\n}; exit; }