#!/usr/bin/perl # Copyright 2004-2014 Leslie Richardson # This file is part of Open Admin for Schools. my %lex = ('Error' => 'Error', 'Main' => 'Main', 'View' => 'View', 'Select' => 'Select', 'Schools' => 'Schools', 'Continue' => 'Continue', 'Start Date' => 'Start Date', 'End Date' => 'End Date', 'Details' => 'Details', 'Check All' => 'Check All', ); my %programs = ('DRA' => 'read_test', 'PPVT' => 'ppvt_test', 'FSIM' => 'math_fstep', 'CMA' => 'mathca_scores', ); my $self = 'sspview.pl'; use DBI; use CGI; use Data::Dumper; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $q = CGI->new; my %arr = $q->Vars; print $q->header( -charset, $charset ); my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); my @time = localtime(time); my $year = $time[5] + 1900; my $month = $time[4] + 1; my $currdate = "$year-$month-$time[3]"; my $title = "$lex{View} SSP Programs"; print qq{$doctype\n$title\n}; print qq{\n}; if ( not $arr{page} ) { # calendar popup. print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ($arr{page} == 1) { delete $arr{page}; showPrograms(); } elsif ($arr{page} == 2) { delete $arr{page}; showDetails(); } else { delete $arr{page}; } #--------------- sub showPrograms { #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my $startdate = $arr{startdate}; delete $arr{startdate}; my $enddate = $arr{enddate}; delete $arr{enddate}; print qq{

Program from $startdate to $enddate

\n}; # Find distinct grades my @grades; my $sth = $dbh->prepare("select distinct grade from student"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort { $a <=> $b } @grades; # Find Grade Enrollments my %enrollments; my $sth = $dbh->prepare("select count(*) from student where grade = ?"); foreach my $gr ( @grades ) { $sth->execute($gr); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth->fetchrow; $enrollments{$gr} = $count; } # Create Student Data Structure my %studentgrade; my $sth = $dbh->prepare("select studnum, grade from student"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum, $grade ) = $sth->fetchrow ) { $studentgrade{$grade}{$studnum} = 1; } print qq{\n}; print qq{\n}; print qq{}; foreach my $prg ( sort keys %programs ) { print qq{}; } print qq{\n}; # Get Student Name my $sth2 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); my $sth3 = $dbh->prepare("select studnum from student where grade = ?"); foreach my $grade ( @grades ) { print qq{}; # MasterMissing hash - populate my %mst_missing; $sth3->execute($grade); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth3->fetchrow ) { $mst_missing{$studnum} = 1; } foreach my $prg ( sort keys %programs ) { my $sth1 = $dbh->prepare("select distinct studnum, tgrade from $programs{$prg} where tdate = ? and tgrade = ?"); # Get Test Dates my $sth = $dbh->prepare("select distinct tdate, count(tdate) from $programs{$prg} where to_days(tdate) >= to_days('$startdate') and to_days(tdate) <= to_days('$enddate') and tgrade = '$grade' group by tdate"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $first = 1; print qq{\n}; next; # prg } # now check for any students without any tests. my $first = 1; my $count = 1; foreach my $sn ( keys %missing ) { if ( $missing{$sn} ) { # no tests done. if ( $first ) { print qq{
Missing Students
\n}; $first = 0; } $sth2->execute($sn); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname) = $sth2->fetchrow; print qq{$count.$firstname $lastname ($sn)
}; $count++; } } print qq{[ $lex{Details} ]}; print qq{\n}; } # end of different programs. print qq{\n}; } # end of grade loop print qq{

$schoolname

Program
Grade		$prg
$grade ($enrollments{$grade})}; my %missing = %mst_missing; my $totalcount; while ( my ($tdate, $tcount) = $sth->fetchrow ) { # Reset tracker for students $sth1->execute($tdate, $grade); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum,$tgrade) = $sth1->fetchrow ) { if ( $missing{$studnum} ) { # present in this grade $missing{$studnum} = 0; } } if ( not $first ) { print qq{
\n}; } else { $first = 0; } print qq{$tdate - $tcount}; $totalcount += $tcount; } if ( $totalcount ) { print qq{
Total Tests: $totalcount\n}; } else { # no tests print qq{
\n}; print qq{\n}; exit; } # end of showPrograms; #---------------- sub showStartPage { #---------------- # my $checked; # if ( $arr{checked} ) { # $checked = qq{checked="checked"}; # } # print qq{
\n}; # print qq{\n}; # print qq{
\n}; print qq{
\n}; print qq{\n}; print qq{\n}; =head print qq{\n}; foreach my $db ( sort keys %alldbase ) { # hash now found in admin.conf print qq{\n}; } =cut # Start Date print qq{\n}; # End Date print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{Schools}
}; print qq{\n}; print qq{$alldbase{$db} ($db)
$lex{'Start Date'}}; print qq{\n}; print qq{
$lex{'End Date'}}; print qq{
\n}; print qq{\n}; print qq{\n}; exit; } # end of showStartPage #-------------- sub showDetails { #-------------- # passed: program, grade, start/end dates # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my $program = $arr{prg}; my $grade = $arr{gr}; my $startdate = $arr{start}; my $enddate = $arr{end}; my $table = $programs{$program}; print qq{

$schoolname — Grade $grade — $program — $startdate/$enddate

\n}; # Get Current Student Enrollment in grade and names. my %currstudents; my %names; my @studsort; my $sth = $dbh->prepare("select lastname, firstname, studnum from student where grade = ? order by lastname, firstname"); $sth->execute( $grade ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $sth1 = $dbh->prepare("select count(*) from lint_student s, lint_program p where s.studnum = ? and s.progid = p.id and to_days(p.startdate) >= to_days('$startdate') and to_days(p.enddate) <= to_days('$enddate')"); while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) { # Check for LLI Program $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $llicount = $sth1->fetchrow; my $llistudent; if ( $llicount > 0) { $llistudent = qq{**}; } $currstudents{$studnum} = '0'; $names{$studnum} = "$llistudent$lastname, $firstname"; push @studsort, $studnum; } my %wdstudents; # Get Tests my $sth = $dbh->prepare("select * from $table where to_days(tdate) >= to_days('$startdate') and to_days(tdate) <= to_days('$enddate') and tgrade = '$grade' order by tdate"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my %reading; # reading levels of tests while ( my $ref = $sth->fetchrow_hashref ) { # my %r = %$ref; # print %r; my $studnum = $ref->{studnum}; $reading{$studnum} = $reading{$studnum}. "$ref->{readlevel} "; if ( exists $currstudents{$studnum} ) { $currstudents{$studnum}++; } elsif ( exists $wdstudents{$studnum} ) { $wdstudents{$studnum}++; } else { $wdstudents{$studnum} = 1; } } # Now print out students and their tests. my $first = 1; for my $sn ( @studsort ) { if ( $first ) { print qq{\n}; print qq{\n}; print qq{\n}; $first = 0; } print qq{\n}; } if ( $first ) { print qq{

No Records Found

\n}; } else { print qq{
RL=Reading Level (Sorted by Date)
Test=Test Count, ** = LLI Student
StudentTestRL
$names{$sn} ($sn)}; if ( not $currstudents{$sn} ) { print qq{0}; } else { print $currstudents{$sn}; } print qq{$reading{$sn}

\n}; } # Now do any withdrawn student tests. my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); $first = 1; foreach my $sn ( keys %wdstudents ) { if ( $first ) { # print heading. print qq{

Withdrawn Students

\n}; print qq{\n}; print qq{\n}; print qq{\n}; $first = 0; } # Look up name $sth->execute($sn); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ln, $fn) = $sth->fetchrow; print qq{\n}; } if ( $first ) { print qq{

No Withdrawn Student Records Found

\n}; } else { print qq{
RL = Reading Level (Date Sorted), Test = Test Count
StudentTestRL
$ln, $fn$wdstudents{$sn}$reading{$sn}

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