#! /usr/bin/perl
# Copyright 2001-2018 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.
# based on report 8 from central site.
my %lex = ('Main' => 'Main',
'Continue' => 'Continue',
'Homeroom' => 'Homeroom',
'Grade' => 'Grade',
'Select by' => 'Select by',
'Error' => 'Error',
'Sort by' => 'Sort by',
'Name' => 'Name',
'Common Math Assessment' => 'Common Math Assessment',
'Report' => 'Report',
'OR' => 'OR',
'No Selection' => 'No Selection',
'School Year' => 'School Year',
'Show Withdrawn' => 'Show Withdrawn',
);
my $self = 'cmaRpt10.pl';
my $pct = '%';
use DBI;
use CGI;
use Cwd;
use Number::Format qw(:all);
use Time::JulianDay;
my @strands = qw(P N SS SP);
my %strandnames = ('P' => 'Patterns and Relations',
'N' => 'Numbers and Operations',
'SS' => 'Shape and Space',
'SP' => 'Stats and Prob'
);
my $configpath = '../../..';
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
$configpath = '../..';
}
eval require "$configpath/etc/admin.conf";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
if ( getcwd() !~ /tcgi/ ) { # we are in cgi
$tchcss = $css;
$tchpage = $homepage;
}
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
if ( $arr{nopct} ) {
$pct = '';
}
my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time);
$year = $year + 1900;
$wday++; $mon++;
my $currdate = "$year-$mon-$mday";
my $title = "$lex{'Common Math Assessment'} $lex{Report} 10 - Students and Mathematics Completion(%) by Homeroom";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{
\n";
# Now convert to short version
my ($sy, $ey) = split('-', $schoolyear);
$schoolyear = $ey;
=head
# Get the school dates from configuration system
my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
foreach my $val ( qw(schoolstart schoolend )) {
$sth->execute( $val );
my $datavalue = $sth->fetchrow;
eval $datavalue;
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
}
=cut
# populate jdclosed with dates from dates table; used by calcSchoolDays;
my %jdclosed;
my $sth = $dbh->prepare("select date, dayfraction from dates
where date is not NULL and date != ''");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my ( $date, $dayfraction ) = $sth->fetchrow ) {
my $jd = julian_day( split( /-/, $date ) );
$jdclosed{$jd} = $dayfraction;
}
# Load School Year, and figure out where we are in it. (teaching days).
# Need Total Teaching Days, and what day we are currently at.
my ($currschoolday, $totalschooldays ) = split(':',
calcSchoolDays($schoolstart, $schoolend, $currdate, \%jdclosed ));
my $completed = format_number( $currschoolday * 100 / $totalschooldays, 1 );
print qq{
\n};
print qq{
Total School Days
$totalschooldays
\n};
print qq{
Current School Day
$currschoolday
\n};
print qq{
Percentage of School Year Complete
$completed%
\n};
print qq{
\n};
# Load all outcomes.
my %outcomes;
my %outcomesgr; # outcomes by grade;
my $sth = $dbh->prepare("select * from mathca_outcomes");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
while ( my $ref = $sth->fetchrow_hashref ) {
%r = %$ref;
$outcomes{$r{oid}} = $r{odesc};
$outcomesgr{$r{grade}}{$r{oid}} = 1;
}
# Get the homerooms, and their grades.
my %gradehomeroom;
my $sth = $dbh->prepare("select distinct grade, homeroom from student");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
while ( my ($gr, $hr) = $sth->fetchrow ) {
$gradehomeroom{$gr}{$hr} = 1;
}
# foreach my $gr ( sort keys %gradehomeroom ) {
# foreach my $hr ( sort keys %{ gradehomeroom{$gr}} ) {
# print "Grade:$gr Homeroom:$hr \n";
# }
# }
# Load the tests by homeroom,grade and outcomes,
my (%data,%scores, %studinfo);
# $data{grade}{homeroom}{studnum} = outcomes count (outcount)
# $scores{grade}{homeroom} = average score;
# $scores{grade}{'total'} = average score;
my $sth1 = $dbh->prepare("select distinct studnum, count(distinct outcome) from mathca_scores
where schoolyear = ? and tgrade = ? and prepost = 'posttest' group by studnum");
my $sth2 = $dbh->prepare("select distinct studnum, avg(score) from mathca_scores
where schoolyear = ? and tgrade = ? and prepost = 'posttest' group by studnum");
my $sth3 = $dbh->prepare("select grade, homeroom from studentall where studnum = ?");
my $sth = $dbh->prepare("select distinct tgrade from mathca_scores where schoolyear = ? and
tgrade is not null and tgrade != '' and prepost = 'posttest'");
$sth->execute( $schoolyear);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
while ( my $grade = $sth->fetchrow ) {
my (@scores, %hrscores); # @scores is total for grade.
foreach my $key ( keys %hrscores ) {
delete $hrscores{$key};
}
# Get Outcome Score Counts for this grade
$sth1->execute( $schoolyear, $grade );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
while ( my ($studnum, $outcount ) = $sth1->fetchrow ) {
# Get Homeroom data;
my $hr = $studinfo{$studnum}{'homeroom'};
if ( not $hr ) { # load it and store also
$sth3->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
my ($tempgr, $temphr ) = $sth3->fetchrow;
$studinfo{$studnum}{homeroom} = $temphr;
$studinfo{$studnum}{grade} = $tempgr;
$hr = $temphr;
}
$data{$grade}{$hr}{$studnum} = $outcount; # outcomes completed by the student.
}
# foreach my $gr ( sort {$a <=> $b} keys %data ) {
# foreach my $hr ( sort keys %{ $data{$gr}} ) {
# foreach my $studnum ( sort keys %{ $data{$gr}{$hr} } ) {
# print "GR:$gr HR:$hr SN:$studnum \n";
# }
# }
# }
# print "
\n";
# Get Average Score for each student in this grade, homeroom
$sth2->execute( $schoolyear, $grade );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
while ( my ($studnum, $avgscore ) = $sth2->fetchrow ) {
my $hr = $studinfo{$studnum}{'homeroom'};
if ( not $hr ) { # load it and store also
$sth3->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; };
my ($tempgr, $temphr ) = $sth3->fetchrow;
$studinfo{$studnum}{homeroom} = $temphr;
$studinfo{$studnum}{grade} = $tempgr;
$hr = $temphr;
}
push @scores, $avgscore; # total for this grade
push @{ $hrscore{$hr} }, $avgscore;
}
# Get average score for this grade.
my $sum;
foreach my $score ( @scores ) {
$sum += $score;
}
my $avg;
if ( @scores ) {
$avg = $sum / @scores;
}
$scores{$grade}{total} = $avg;
# Homeroom scores for this grade.
foreach my $hr ( keys %hrscore ) {
my $sum;
foreach my $score ( @{ $hrscore{$hr} } ) {
$sum += $score;
}
my $avg;
my $count = scalar @{ $hrscore{$hr} };
if ( $count ) {
$avg = $sum / $count;
}
# print "GRADE:$grade HR:$hr Scores:$avg \n";
$scores{$grade}{$hr} = $avg;
}
} # end of grade loop
# foreach my $gr ( sort {$a <=> $b} keys %data ) {
# foreach my $hr ( sort keys %{ $data{$gr}} ) {
# print qq{Gr:$gr HR:$hr Scores:$scores{$gr}{$hr} \n};
# }
# print qq{Total Scores for GR:$gr Score:$scores{$gr}{total} \n};
# }
print qq{
\n};
print qq{
Grade
HRoom
# of Outcomes
# of Students
};
print qq{
Number of Students with this Percentage of Completion
\n};
my @totalwtavg;
my %data1;
my %gradeaverage; # $gradeaverage{$grade}{count/total}
my %hroomaverage; # hroomaverage{$grade}{$hr}{count/total}
# Now rewrite the data.
foreach my $grade ( sort keys %data ) {
my $outcometotal = scalar keys %{ $outcomesgr{$grade}};
foreach my $hr ( keys %{ $data{$grade} } ) {
foreach my $studnum ( sort keys %{ $data{$grade}{$hr} } ) {
my $count = $data{$grade}{$hr}{$studnum};
my $percent;
if ( $outcometotal ) {
$percent = format_number( $count * 100 / $outcometotal, 1 );
}
$hroomaverage{$grade}{$hr}{'total'} += $percent;
$hroomaverage{$grade}{$hr}{'count'} += 1;
$gradeaverage{$grade}{'total'} += $percent;
$gradeaverage{$grade}{'count'} += 1;
$data1{$grade}{$hr}{$studnum} = $percent;
}
}
}
my %topcount; # topcount{decade} = count
my ( $tavgtotal, $tavgcount );
foreach my $grade ( sort {$a <=> $b} keys %data1 ) {
my $outcometotal = scalar keys %{ $outcomesgr{$grade}};
foreach my $hr ( sort {$a <=> $b} keys %{ $data1{$grade} } ) {
my $studenttotal = scalar keys %{ $data{$grade}{$hr} };
my $binsum;
print qq{
$grade
$hr
$outcometotal
};
print qq{
$studenttotal
\n};
foreach my $bottom ( qw( 0 10 20 30 40 50 60 70 80 90 )) {
my $top = $bottom + 10;
my $binavg = ( $top + $bottom ) / 2;
my $count;
foreach my $studnum ( keys %{ $data1{$grade}{$hr} } ) {
my $topper = $top;
if ( $top == 100 ) { $topper = 100.01; } # to solve the 100% issue
if ( $data1{$grade}{$hr}{$studnum} >= $bottom and $data1{$grade}{$hr}{$studnum} < $topper ) {
$count++;
}
}
if ( $count ) { # we have students in this 'bin'.
my $spercent = format_number( $count * 100 / $studenttotal, 0);
$binsum += ( $count * $top ); #~~ was $binavg
# print qq{
$spercent$pct
}; # changed this line to count line below
print qq{
$count
};
$topcount{$top} += $count;
} else {
print qq{
};
}
} # end decade loop
# NEEDED when doing a total row for the grade; not done YET
# my $gpercent = 0;
# if ( $gradeaverage{$grade}{'count'} ) {
# $gpercent = format_number( $gradeaverage{$grade}{'total'} / $gradeaverage{$grade}{'count'}, 1);
# }
# $tavgtotal += $gpercent;
# $tavgcount ++;
my $hpercent = 0;
if ( $hroomaverage{$grade}{$hr}{'count'} ) {
$hpercent = format_number( $hroomaverage{$grade}{$hr}{'total'} /
$hroomaverage{$grade}{$hr}{'count'}, 1);
}
$tavgtotal += $hpercent;
$tavgcount ++;
my $wtavg = format_number( $binsum / $studenttotal, 1);
push @totalwtavg, $wtavg; # used at bottom
my $compdiff = format_number($hpercent - $completed, 1);
my $avgscore = format_number( $scores{$grade}{$hr}, 2,2);
my $pctscore = format_number( $scores{$grade}{$hr} * 25, 0);
print qq{
$wtavg$pct
$avgscore - $pctscore$pct
\n};
} # end of homeroom loop
} # end of grade loop
# BOTTOM ROW
print qq{
Totals
\n};
# Get sum of all %topcount values
my ($topcountsum, $binsum);
foreach my $key ( sort keys %topcount) {
$topcountsum += $topcount{$key};
}
foreach my $top ( qw( 10 20 30 40 50 60 70 80 90 100 )) {
# print "TOP:$top Count:$topcount{$top} SUM:$topcountsum \n";
my $tpercent = 0;
if ( $topcountsum ) {
$tpercent = format_number( $topcount{$top} * 100 / $topcountsum, 1);
}
print qq{
$tpercent$pct
};
}
# Weighted average calc for completion
my ($wcount,$wsum );
foreach my $avg ( @totalwtavg ) {
$wcount++;
$wsum += $avg;
}
my $wtavg = 0;
if ( $wcount ) {
$wtavg = format_number( $wsum / $wcount, 1 );
}
print qq{
$wtavg$pct
\n};
# Now find the average score for all grades;
my $sum;
foreach my $gr ( keys %scores ) {
$sum += $scores{$gr}{total}
}
my $avgtotalscore = format_number( $sum / keys %scores, 2,2 );
my $pctscore = format_number( $avgtotalscore * 25, 0);
print qq{
$avgtotalscore - $pctscore$pct
\n};
print qq{
\n};
print qq{\n};
return;
} # end of showReport
#---------------
sub calcSchoolDays { # calculate the current day in school year and also total length of school year
#---------------
# passed a start date and an end date for a term, and a ref to a
# hash storing the closure dates in julian day format, it will
# return a count of the days open in the month.
my %dowclosed = ('0' => 1, '6' => 1); # Day 0 is Sunday, Day 6 is Saturday
# outline: convert start/end dates to jd. Loop over all days. Use modulus function to ident weekend days.
my ($startdate, $enddate, $currdate, $closeref ) = @_;
my %jdclosed = %$closeref;
=head
print "JDClosed \n";
foreach my $key ( sort keys %jdclosed ) {
print "K:$key V:$jdclosed{$key} \n";
}
print "End JDClosed \n";
=cut
my $startjd = julian_day(split(/-/,$startdate));
my $endjd = julian_day(split(/-/,$enddate));
my $currjd = julian_day(split(/-/,$currdate));
my %modclosed; # modulus for days closed
foreach my $inc ( 0..6 ) {
my $testjd = $startjd + $inc;
my $dow = day_of_week($testjd);
if ( $dowclosed{$dow} ) { # $testjd is a closed dow
my $mod = $testjd % 7;
$modclosed{$mod} = 1;
}
}
# foreach my $d ( sort keys %modclosed ) {
# print "Closed Mod: $d \n";
# }
my ($currcount, $totalcount);
my ($daysopen, $daysclosed);
foreach my $jd ( $startjd..$endjd ) {
my $dow = day_of_week( $jd );
my $mod = $jd % 7;
if ( $jd == $currjd ) {
$currcount = $daysopen;
}
if ( $modclosed{$mod} ) { # closed for weekend
next;
}
# check if a day closed
my $cl = $jdclosed{ $jd };
if ( $cl ) { # this gives us the fraction closed
$daysclosed += $cl;
# only add on the fraction of day open
$daysopen += (1 - $cl );
} else {
$daysopen += 1;
}
}
$totalcount = $daysopen;
return "$currcount:$totalcount";
}