#! /usr/bin/perl # Copyright 2001-2024 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. my %lex = ('Main' => 'Main', 'Continue' => 'Continue', 'Grade' => 'Grade', 'Select by' => 'Select by', 'Error' => 'Error', 'Sort by' => 'Sort by', 'Name' => 'Name', 'Common Math Assessment' => 'Common Math Assessment', 'School Year' => 'School Year', 'Show Withdrawn' => 'Show Withdrawn', ); my $self = 'feederMathView.pl'; use DBI; use CGI; use Cwd; use Number::Format qw(:all); 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 $teachermode; my $configpath = '../..'; if ( getcwd() =~ /tcgi/ ){ # we are in tcgi $teachermode = 1; $configpath = '..'; } # only load passwords and users eval require "$configpath/etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # Load global user and password if ( not -e "$globdir/global.conf" ) { print qq{Cannot read the global.conf file!\n}; print qq{\n}; exit; } eval require "$globdir/global.conf"; if ( $@ ) { print qq{$lex{Error} $@
\n}; die $lex{Error}. " $@
\n"; } if ( $oldbase{$dbase} ) { # We are an onion lake school %dbase = %oldbase; } my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $wday++; $mon++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; # Get current dir so know what CSS to display; if ( $teachermode ) { # running on teacher site $css = $tchcss; $homepage = $tchpage; $downloaddir = $tchdownloaddir; $webdownloaddir = $tchwebdownloaddir; } my $title = "View $lex{'Common Math Assessment'}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} \n}; if ( not $teachermode ) { print qq{| SSP \n}; } print qq{]\n

$title

\n}; if ( not $arr{page} ) { selectSchool(); } elsif ( $arr{page} == 1) { delete $arr{page}; selectGroup(); } elsif ( $arr{page} == 2) { delete $arr{page}; showReport(); } #--------------- sub selectGroup { #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # passed db to view. print qq{

Feeder School - $dbase{$arr{db}}

\n}; my $dsn = "DBI:$dbtype:dbname=$arr{db}"; my $dbh = DBI->connect($dsn,$guser,$gpassword); my (@grades, @schoolyears ); # Get Grades $sth = $dbh->prepare("select distinct grade from studentall where grade is not NULL and grade != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort {$a <=> $b} @grades; # Get School Years $sth = $dbh->prepare("select distinct schoolyear from mathca_scores"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $yr = $sth->fetchrow ) { push @schoolyears, $yr; } @schoolyears = reverse sort @schoolyears; print qq{\n}; exit; } #---------------- sub selectSchool { #---------------- if (not @g_FeederSchools ) { print qq{

No Feeder Schools Found

\n}; print qq{\n}; exit; } print qq{

Select Feeder School

\n}; print qq{\n}; print qq{\n}; exit; } #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } my $studtable = 'studentwd'; if ( $arr{showcurrent} ) { $studtable = 'studentall'; } my $dsn = "DBI:$dbtype:dbname=$arr{db}"; my $dbh = DBI->connect($dsn,$guser,$gpassword); my ($select, $selectval, @grades, $classname); $select = "where grade = ?"; $selectval = $arr{grade}; push @grades, $arr{grade}; $classname = "Grade $arr{grade}"; print qq{

$dbase{$arr{db}} - $classname – School Year $arr{schoolyear}

\n}; # Build outcomes data structure my %outcomes; # lists outcomes. my %odesc; # outcomes description. 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 ) { my %r = %$ref; my $oid = $r{oid}; my $grade = $r{grade}; my ($cat, $seq) = split(/\./, $oid); $cat =~ s/$grade$//; # remove grade $outcomes{$grade}{$cat}{$seq} = $oid; $odesc{$oid} = $r{odesc}; } # Done building data structure. foreach my $grade ( @grades ) { # Now build a structure for this grade my %strandcount; # $catcount{category} = number in this category. foreach my $cat ( sort keys %{ $outcomes{$grade} } ) { foreach my $seq ( sort keys %{ $outcomes{$grade}{$cat} } ) { if ( $seq > $strandcount{$cat} ) { $strandcount{$cat} = $seq; } } } # Now Select the students to display; skip any withdrawn kids w/o data. my $sth = $dbh->prepare("select lastname, firstname, studnum, grade from $studtable $select order by lastname, firstname"); if ( $arr{homeroom} ) { # add on grade selector to only get part of the class. $sth->execute( $selectval, $grade ); } else { $sth->execute( $selectval ); } if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my %withdrawn; my %data; # holds all data $data{studnum}{outcome}{prepost} = score; my @students; # provides order of students my %pass; # $pass{studnum} = count of passes ( >= 3) my %strandpass; my %names; # store student names; my %tdates; # store test dates for outcomes and pre/post $tdates{outcome}{pretest/posttest}; # Get Student Data my $sth1 = $dbh->prepare("select prepost, outcome, score, tdate from mathca_scores where schoolyear = ? and studnum = ? order by tdate"); # Queries for Withdrawn and Having Data my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?"); my $sth3 = $dbh->prepare("select count(*) from mathca_scores where schoolyear = ? and studnum = ?"); # Loop through all students; build data structure. while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $studnum = $r{studnum}; # Check for withdrawn and having any data this year. if ( $arr{showwithdrawn} ) { # check if student is withdrawn # Withdrawn? $sth2->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $wdcount = $sth2->fetchrow; # Have Data? $sth3->execute($arr{schoolyear}, $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $datacount = $sth3->fetchrow; if ( $wdcount > 0 and $datacount < 1 ) { # withdrawn and no data next; } } push @students, $studnum; $names{$r{studnum}} = "$r{lastname}, $r{firstname}"; # Get this student's data $sth1->execute( $arr{schoolyear}, $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($prepost, $outcome, $score, $tdate) = $sth1->fetchrow ) { # Store passes; if ( $prepost eq 'posttest' ) { if ( $score >= 3 ) { $pass{$studnum}{'pass'}++; $strandpass{$studnum}{$outcome}{'pass'}++; } $pass{$studnum}{'count'}++; $strandpass{$studnum}{$outcome}{'count'}++; } $data{$studnum}{$outcome}{$prepost} = $score; } } # end of student loop print qq{

Grade $grade

\n}; print qq{\n}; print qq{\n}; # header section. print qq{}; foreach my $strand ( @strands ) { if ( $strandcount{$strand} ) { # print IF we have that strand print qq{\n}; } } print qq{\n}; # Second Line: Desc; print qq{}; foreach my $strand ( @strands ) { if ( $strandcount{$strand} ) { # print IF we have that strand print qq{}; print qq{}; print qq{\n}; print qq{\n}; } } print qq{\n}; # used for summary stats at the end; my (%strandtotalcount, %strandtotalsum ); # Loop through each student, finding averages for each strand foreach my $studnum ( @students ) { my ($pretotalcount, $pretotalsum, $posttotalcount, $posttotalsum); print qq{\n}; foreach my $strand ( @strands ) { if ( not $strandcount{$strand} ) { next; } # skip any strands not covered in this grade. # Loop through all tests and find averages for this strand of outcomes. my ( $preavg, $presum, $precount, $postavg,$postsum, $postcount, $passsum, $passcount ); foreach my $seq ( sort keys %{ $outcomes{$grade}{$strand} } ) { my $outcome = $outcomes{$grade}{$strand}{$seq}; $passsum += $strandpass{$studnum}{$outcome}{pass}; $passcount += $strandpass{$studnum}{$outcome}{count}; my $pretest = $data{$studnum}{$outcome}{'pretest'}; my $posttest = $data{$studnum}{$outcome}{'posttest'}; if ( $pretest and $posttest or ( $grade eq '2' or $grade eq '1' or $grade eq 'K' or $grade eq 'PK')) { $presum += $pretest; $precount++; $postsum += $posttest; $postcount++; } else { next; } } # end of outcome if ( $precount ) { $preavg = format_number( $presum / $precount, 2); $pretotalcount += $precount; $pretotalsum += $presum; } if ( $postcount ) { $postavg = format_number( $postsum / $postcount, 2); $posttotalcount += $postcount; $posttotalsum += $postsum; } my $passString; if ( $passcount ) { $passString = "$passsum / $passcount"; } $strandtotalcount{$strand} += $postcount; $strandtotalsum{$strand} += $postsum; print qq{}; print qq{\n}; } # end of this strand # now totals for this student my ($pretotalavg, $posttotalavg); if ( $pretotalcount ) { $pretotalavg = format_number( $pretotalsum / $pretotalcount, 2); } if ( $posttotalcount ) { $posttotalavg = format_number( $posttotalsum / $posttotalcount, 2); } my $diff = format_number( $posttotalavg - $pretotalavg, 2); print qq{}; print qq{\n}; # Passes; my $passpercent; if ( $pass{$studnum}{'count'} and $pass{$studnum}{'pass'} ) { $passpercent = format_number( $pass{$studnum}{'pass'} * 100 / $pass{$studnum}{'count'}, 2); } if ( $passpercent ) { print qq{\n}; } elsif ( $pass{$studnum}{'count'} ) { # have count, but no passes print qq{\n}; } else { # print blank. print qq{\n}; } } # end of student loop print qq{

Hover on Column Titles to see text descriptions
Student	}; print qq{$strandnames{$strand} ($strandcount{$strand})				Score Averages
	Out	Pre Avg	Post Avg	}; print qq{Pass	Total Outcomes	Pre	Post	Difference	Pass
$names{$studnum}	$postcount	$preavg	$postavg	$passString	$posttotalcount	$pretotalavg	$posttotalavg	$diff	$passpercent% ($pass{$studnum}{'pass'}/$pass{$studnum}{'count'})
0% (0/$pass{$studnum}{'count'})

\n}; # Now Summary of all students in the grade print qq{\n}; print qq{\n}; # Heading print qq{\n}; # Data Section foreach my $strand ( @strands ) { my $avg = '0'; if ( $strandtotalcount{$strand} ) { $avg = format_number( $strandtotalsum{$strand} / $strandtotalcount{$strand}, 2); } print qq{\n}; } print qq{

Summary
Strand	Average Score
$strandnames{$strand} ($strandcount{$strand})	$avg

\n}; } # end of grade loop print qq{\n}; exit; } # end of showReport #---------------- sub showStartPage { # Entry Values for Custom Script #---------------- my (@homerooms, @grades, @schoolyears ); # Get Homerooms my $sth = $dbh->prepare("select distinct homeroom from student where homeroom is not NULL and homeroom != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $hr = $sth->fetchrow ) { push @homerooms, $hr; } @homerooms = sort {$a <=> $b} @homerooms; # Get Grades $sth = $dbh->prepare("select distinct grade from student where grade is not NULL and grade != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort {$a <=> $b} @grades; # Get School Years $sth = $dbh->prepare("select distinct schoolyear from mathca_scores"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $yr = $sth->fetchrow ) { push @schoolyears, $yr; } @schoolyears = reverse sort @schoolyears; # Start Form print qq{

\n}; print qq{\n}; print qq{\n}; # Select Grade print qq{}; print qq{\n}; # OR print qq{\n}; # Select Homeroom print qq{}; print qq{\n}; # School Year print qq{}; print qq{\n}; # Withdrawn print qq{}; print qq{\n}; =head # Sort Order print ""; print ""; print "\n"; =cut print qq{\n}; print qq{

$lex{'Select by'} $lex{Grade}
$lex{OR}
$lex{'Select by'} $lex{Homeroom}
$lex{'School Year'}
$lex{'Show Withdrawn'}
$lex{'Sort by'}

\n}; print qq{

\n}; exit; }

$lex{'Select by'} $lex{Grade}
$lex{'School Year'}
Show Current Students
Skip Students without Records