#! /usr/bin/perl # Copyright 2001-2021 Leslie Richardson # This file is part of Open Admin for Schools. # based on IEP system file 'attendcheck_local.pl'; my $self = 'past_attscan2.pl'; my %lex = ('Attendance' => 'Attendance', 'Error' => 'Error', 'Main' => 'Main', 'Continue' => 'Continue', 'Cutoff' => 'Cutoff', 'Entry Value' => 'Entry Value', 'Percent' => 'Percent', ); use DBI; use CGI; use Number::Format qw(:all); use Cwd; use Time::JulianDay; # Read config variables eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } # Get passed vars; comment this out below. my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); # Page Header my $title = qq{Attendance Scan 2}; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{ SSP ] $currdate\n}; print qq{

$title

\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($db, $dsn, $dbh ); if ( not $arr{remote} ) { # get the remote database of choice. # 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; # Select Previous Databases for this school while ( my $db = $sth->fetchrow ) { if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; } if ( $db =~ m/$dbase/ ) { $remotedb{$db} = 1; } } # Start the form 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}; } # Continue print qq{\n}; print qq{
Select School Year
}; print qq{ $year ($db)
\n}; print qq{\n}; exit; } else { # we have $arr{remote} my $dbase = $arr{database}; # passed from function above. $dsn = "DBI:mysql:database=$dbase;host=$remotehost"; $dbh = DBI->connect($dsn,$remoteuser,$remotepassword); # Get the values from configuration system of this year. my $sth = $dbh->prepare("select datavalue from conf_system where filename = 'admin'"); $sth->execute; my $failflag = 1; while ( my $datavalue = $sth->fetchrow ) { # print "DV:$datavalue
\n"; if ( $datavalue eq 'schoolstart' or $datavalue eq 'schoolend' ) { $failflag = 0; } eval $datavalue; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } } if ( $failflag ) { # school year not defined. print qq{

School Year not defined

\n}; print qq{\n}; exit; } } # NOTE: We have 2 additional hash values passed in %arr: database and remote; # Any changes to attscan2.pl are noted below with a double tilde (~~) # load attendance library eval require "../../lib/libattend.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } # Absent/Late strings for libattend library. my %lexi = ('Absent' => 'Absent', 'Late' => 'Late' ); 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 $currdate = "$year-$month-$day"; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; showReport(); } #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # find the students that are below percentage attendance passed print qq{

$schoolstart - $schoolend

\n}; # Now get the students in this school my %sortname; my %studs; # studs{dbase}{studnum} = record; my $sth = $dbh->prepare("select * from student"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $sortname{"$r{lastname}$r{firstname}$r{studnum}"} = $r{studnum}; # my $studnum = $r{studnum}; $studs{$r{studnum}} = $ref; } # percentage attendance value my $attend = $arr{attend}; $attend =~ s/\%//; # strip percent if ( not $attend or $attend < 1 or $attend > 101 ) { # fail print qq{

$lex{Error} $lex{'Entry Value'}: $attend

\n}; print qq{\n}; exit; } my $attendlimit = format_number( $attend / 100, 3, 3); # print "Limit:$attendlimit
\n"; my $templimit = $attendlimit * 100; print qq{

$lex{Attendance} $lex{Cutoff} $templimit%

\n}; print qq{
}; print qq{Green Level >= $arr{levelgreen}%, Yellow Level >= $arr{levelyellow}%, }; print qq{Red Level < $arr{levelyellow}%
\n}; # Get start and end dates my $startdate = $schoolstart; my $enddate = $schoolend; # NOT current date my $termstart = $g_MTrackTerm{1}{1}{start}; # print qq{Term Start: $termstart
\n}; $startdate = $termstart; my %schooldays = mkSchoolDays($startdate, $enddate, $dbh); # These are the school days open in each month, holidays excluded from the count. # Loop through students in this school and get their attendance. my %sort; foreach my $studnum ( keys %studs ) { my %r = %{ $studs{$studnum} }; $sort{"$r{lastname}$r{firstname}$studnum"} = $studnum; } my $first = 0; my %data; my %studavg; my %gradeavg; # gradeavg{grade}{enrol => present =>} my %gradeoverlimit; # Student Loop foreach my $key ( sort keys %sort ) { my $studnum = $sort{$key}; my %r = %{ $studs{$studnum} }; my $grade = $r{grade}; # print qq{
$r{lastname}, $r{firstname}
\n}; my $ppd = $g_ppd{ $r{grade} }; if ( not $ppd ) { print qq{}; print qq{Missing Attendance Periods Per Day - SN:$studnum $r{firstname} $r{lastname}}; print qq{GR:$r{grade} DB:$db - SETTING PPD to 2\n}; $ppd = 2; } # Calculate the days enrolled for this student my $ref = calcMonthlyEnrollment( $studnum, $startdate, $enddate, $dbh); my %enrol = %$ref; # enrol{year-month} = val if ( not $ref ) { #student not enrolled. my $skip = scalar keys %schooldays; # print qq{}; # print qq{Enrollment Error: No Enrollment this school year\n}; next; } my ($currmonth,$prevmonth); my ($totalenrol, $totalpresent); # Loop through months foreach my $yrmo ( sort keys %schooldays ) { my $abslate = calcMonthlyAttendance( $studnum, $yrmo, $ppd, '', \%lexi, $dbh); my ($absent,$late ) = split(':', $abslate); # print "ABS:$absent "; my $pctAttendance; if ( $enrol{$yrmo}{days} ) { my $present = $enrol{$yrmo}{days} - $absent; # print "YRMO:$yrmo ENROL:$enrol{$yrmo}{days} ABS:$absent PRES:$present
\n"; if ( $present < 0 ) { $present = 0; } $totalpresent += $present; $totalenrol += $enrol{$yrmo}{days}; $gradeavg{$grade}{enrolled} += $enrol{$yrmo}{days}; $gradeavg{$grade}{present} += $present; $data{$studnum}{$yrmo} = "$present:$enrol{$yrmo}{days}"; $pctAttendance = round($present * 100 / $enrol{$yrmo}{days}, 1); } else { # no enrollment.... # print qq{No Enrol}; next; # month } $prevmonth = $currmonth; $currmonth = $pctAttendance; my $color; if ( $pctAttendance >= $arr{levelgreen} ) { $color = 'lightgreen'; } elsif ( $pctAttendance >= $arr{levelyellow} ) { $color = 'yellow'; } else { $color = 'red'; } # print qq{$pctAttendance\n}; } # end of year-month loop my $pctTotal; if ( $totalenrol ) { $pctTotal = format_number($totalpresent / $totalenrol, 3,3); } else { $pctTotal = 0; } $studavg{$studnum} = $pctTotal; my $avgcolor; if ( $pctTotal >= $arr{levelgreen} ) { $avgcolor = 'lightgreen'; } elsif ( $pctTotal >= $arr{levelyellow} ) { $avgcolor = 'yellow'; } else { $avgcolor = 'red'; } # print qq{}; # print qq{$pctTotal% ($totalpresent / $totalenrol)\n}; } # end of student loop # at this point we have %studs{studnum} = $ref to record, # %data{studnum}{$yrmo} = present:enrolled , and # %studavg{studnum} = avg; # Printing Student Loop my (%sort, @sort); if ( $arr{namesort} ) { foreach my $key ( sort keys %sortname ) { my $studnum = $sortname{$key}; # print qq{
KEY:$key SN:$studnum
\n}; push @sort, $studnum; } } else { # average sort foreach my $studnum ( keys %studavg ) { my $avg = $studavg{$studnum}; $sort{"$avg$studnum"} = $studnum; } foreach my $key ( sort {$a <=> $b} keys %sort ) { my $studnum = $sort{$key}; push @sort, $studnum; } } #-------- Printing Loop------------------ my $first = 1; foreach my $studnum ( @sort ) { my $avg = $studavg{$studnum}; my %r = %{ $studs{$studnum} }; my $grade = $r{grade}; if ( $avg > $attendlimit ) { $gradeoverlimit{$grade}++; # number of students in each grade over this % level next; } # skip to next student if ( $first ) { # print heading. print qq{\n}; print qq{}; foreach my $yrmo ( sort keys %schooldays ) { print qq{}; } print qq{\n}; # Days Open print qq{\n}; foreach my $yrmo ( sort keys %schooldays ) { print qq{}; } print qq{\n}; $first = 0; } print qq{\n}; my ($totalenrol, $totalpresent); # Loop through months foreach my $yrmo ( sort keys %schooldays ) { # my $abslate = calcMonthlyAttendance( $studnum, $yrmo, $ppd, '', \%lexi, $dbh); # my ($absent,$late ) = split(':', $abslate); # print "ABS:$absent "; my ($present,$enrolled) = split(':', $data{$studnum}{$yrmo} ); my $pctAttendance; if ( $enrolled ) { $pctAttendance = round($present * 100 / $enrolled, 1); } else { # no enrollment.... print qq{}; next; # month } $totalpresent += $present; $totalenrol += $enrolled; my $color; if ( $pctAttendance >= $arr{levelgreen} ) { $color = 'lightgreen'; } elsif ( $pctAttendance >= $arr{levelyellow} ) { $color = 'yellow'; } else { $color = 'red'; } print qq{\n}; } # end of year-month loop if ( $totalenrol ) { $pctTotal = round($totalpresent * 100 / $totalenrol, 1); } else { $pctTotal = '0.0'; } my $avgcolor; if ( $pctTotal >= $arr{levelgreen} ) { $avgcolor = 'lightgreen'; } elsif ( $pctTotal >= $arr{levelyellow} ) { $avgcolor = 'yellow'; } else { $avgcolor = 'red'; } print qq{\n}; } # end of student loop if ( $first ) { print qq{

No Students Found

\n}; } else { print qq{
NameGrade$yrmoAverage
Days Open$schooldays{$yrmo}
$r{lastname}, $r{firstname}$gradeNo Enrol$pctAttendance%}; print qq{$pctTotal% ($totalpresent / $totalenrol)

\n}; } # Grade Averages my $sth = $dbh->prepare("select count(*) from student where grade = ?"); print qq{\n}; print qq{\n}; my ($totpresent,$totenrol); foreach my $grade ( sort {$a <=> $b} keys %gradeavg ) { $sth->execute($grade); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $ecount = $sth->fetchrow; my $avg; if ( $gradeavg{$grade}{enrolled} ) { $avg = round($gradeavg{$grade}{present} * 100 / $gradeavg{$grade}{enrolled}, 1); $totpresent += $gradeavg{$grade}{present}; $totenrol += $gradeavg{$grade}{enrolled}; } if ( not $gradeoverlimit{$grade} ) { $gradeoverlimit{$grade} = '0'; } print qq{\n}; } my $totalavg = round($totpresent * 100 / $totenrol, 1); print qq{\n}; print qq{
GradeAttendanceOver Limit
$grade$avg%$gradeoverlimit{$grade} / $ecount
Summary Average $totalavg%
\n}; print qq{\n}; exit; } # end of showReport #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{\n}; # ~~ Add these 2 lines. print qq{\n}; print qq{\n}; print qq{\n}; # Percent Attendance print qq{\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; # Sort by Name print qq{\n}; print qq{\n}; print qq{
$lex{Percent} $lex{Attendance} $lex{Cutoff} (1-101)}; print qq{
}; print qq{Set to 101 in order to see all student attendance
}; print qq{Attendance Color Levels
}; print qq{Green >=
\n}; print qq{Yellow >=
\n}; print qq{Red is any percent attendance below this value
Sort by Name?}; print qq{
\n}; print qq{\n}; exit; }