\n};
} else { # we have a season value; find dates.
# We will need to get start/end dates for this season in order to do the exceptions.
my ($yr,$sn) = split('-', $season);
my $startmo = $seasondates{$sn}{start}; # start month,day
my $endmo = $seasondates{$sn}{end}; # end month,day
$startdate = qq{$yr-$startmo};
$enddate = qq{$yr-$endmo};
print qq{
Season $season ($startdate / $enddate)};
print qq{
\n};
}
# Get IEP Database
my ($sy,$ey) = split('-', $schoolyear);
$sy =~ s/20//;
$ey =~ s/20//;
my $iepdbase = qq{iep$sy$ey};
my $dsniep = "DBI:$dbtype:dbname=$iepdbase";
my $dbhiep = DBI->connect($dsniep,$guser,$gpassword);
print qq{
IEP Dbase $iepdbase
\n};
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
# Find all IEP students, to remove from evaluation, match by studnum and/or name
# (since may not be unique)
my (%iepstudents, %iepnames);
my $sth = $dbhiep->prepare("select lastname, firstname, studnum from special
where school = '$dbase'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) {
# print qq{IEP SN:$studnum\n};
$iepstudents{$studnum} = "$lastname, $firstname";
$iepnames{"$lastname$firstname"} = $studnum;
}
# Get all students who have tests
# Get Name and Grade (all possible students)
my $sth2 = $dbh->prepare("select lastname, firstname, grade from studentall where studnum = ?");
my (%studentname,%sort, %studentgrade);
my $sth = $dbh->prepare("select distinct studnum from read_test");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
# Get Name, Grade
$sth2->execute( $studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname,$firstname,$grade) = $sth2->fetchrow;
if ( not $lastname) { # skip them, too old.
next;
}
if ( $iepstudents{$studnum} or $iepnames{"$lastname$firstname"} ) {
# if ( $showiep ) {
# print qq{Skip IEP Student: $firstname $lastname ($studnum) };
# print qq{Grade $grade \n};
# }
# print "Skip IEP $studnum $firstname $lastname $grade \n";
next; # skip IEP students
}
$sort{"$lastname$firstname$studnum"} = $studnum;
$studentname{$studnum} = "$lastname, $firstname"; # store in case needed later.
$studentgrade{$studnum} = $grade;
}
if ( not %sort ) {
print qq{
No Student Tests Found
\n};
next;
}
# Now loop through all students printing results; setup queries first
my $sth1;
if ( $season ) {
$sth1 = $dbh->prepare("select id, tdate, readlevel, tgrade, dratype from read_test
where studnum = ? and season = '$season' order by tdate desc");
} else {
$sth1 = $dbh->prepare("select id, tdate, readlevel, tgrade, dratype from read_test
where studnum = ? and to_days(tdate) >= to_days('$startdate') and
to_days(tdate) <= to_days('$enddate') order by tdate desc");
}
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $sth2 = $dbh->prepare("select sum(score) from read_test_score where testid = ?");
# Withdrawn?
my $sth4 = $dbh->prepare("select count(*) from studentwd where studnum = ?");
# Hidden?
my $sth5 = $dbh->prepare("select count(*) from student_temp where studnum = ?");
foreach my $key ( keys %schooldata ) { $schooldata{$key} = NULL; }
my %schooldata;
my %lowdelta; # lowdelta{tgrade}{delta} = @studnums; store students with very low delta values.
foreach my $key ( sort keys %sort ) {
my $studnum = $sort{$key};
# Get Name, grade
# $sth3->execute($studnum);
# if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
# my ( $lastname, $firstname, $grade ) = $sth3->fetchrow;
my $name = $studentname{$studnum};
my $grade = $studentgrade{$studnum};
# Withdrawn?
$sth4->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $wdcount = $sth4->fetchrow;
if ( $wdcount and not $arr{includewithdrawn} ) { next; }
# Hidden?
$sth5->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $hidcount = $sth5->fetchrow;
# Get Tests
$sth1->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
# Only use latest value for the student.
my $trows = $sth1->rows;
if ( $trows < 1 ) { next; }
# if $onlylatest, limit to 1 test
# if ( $trows > 1 ) {
# print qq{More than one test: $studnum \n};
# $trows = 1;
# }
$trows = 1; # only the latest version.
for ( 1 .. $trows ) {
my ( $testid, $tdate, $readlevel, $tgrade, $dratype ) = $sth1->fetchrow;
# Get the score total;
$sth2->execute( $testid );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $total = $sth2->fetchrow;
# Equivalent Grade print
my $equivgrade = scoreToGrade($total, $readlevel, $dratype );
my $grdelta;
if ( not $tgrade ) {
$sth3->execute( $studnum );
my ( $lastname, $firstname ) = $sth3->fetchrow;
print qq{Test Grade Missing: $lastname, $firstname ($studnum) - $tdate \n};
next;
} else { # Find grade delta
$grdelta = $equivgrade - $tgrade;
}
# if ( $grdelta > 1 ) {
# print qq{
$name ($grade) / $grdelta
\n};
# }
if ( $grdelta >= 0 ) { $grdelta = int $grdelta; } # truncate.
else { $grdelta = int $grdelta - 1; }
# Store kids below threshold.
if ( $mindelta and $grdelta <= $mindelta ) { # add this student to the list.
push @{ $lowdelta{$tgrade}{$grdelta} }, $studnum;
}
$schooldata{$tgrade}{$grdelta}++;
$overall{$tgrade}{$grdelta}++;
} # end of trows loop
} # end of student loop
=head
# Test School Data;
foreach my $grade ( sort keys %schooldata ) {
foreach my $delta ( sort keys %{ $schooldata{$grade} } ) {
print qq{GR:$grade Delta:$delta VAL: $schooldata{$grade}{$delta} \n};
}
}
=cut
=head
if ( $mindelta ) { # display students below minimum delta value.
my $first = 1;
foreach my $tg ( sort keys %lowdelta ) {
foreach my $delta ( sort keys %{ $lowdelta{$tg} } ) {
foreach my $sn ( @{ $lowdelta{$tg}{$delta} } ) {
if ( $first ) { # start table.
print qq{
\n};
print qq{
Students Below Grade Level
\n};
print qq{
Grade
Diff
Student
\n};
$first = 0;
}
print qq{
$tg
$delta
$studentname{$sn} ($sn)
\n};
}
}
}
if ( not $first ) { # we have records
print qq{
\n};
}
}
=cut
# Skip if no tests in this date range
if ( not %schooldata ) {
print qq{
No tests in this Date Range
\n};
next;
}
# Get MIN, MAX values of Delta Grade (How far above/below grade level.
my ($max, $min, $studcount );
my %dcount;
foreach my $grade ( sort keys %schooldata ) {
foreach my $delta ( sort keys %{ $schooldata{$grade} } ) {
if ( $delta > $max ) { $max = $delta; }
if ( $delta < $min ) { $min = $delta; }
$studcount += $schooldata{$grade}{$delta};
$dcount{$delta} += $schooldata{$grade}{$delta};
# print qq{G:$grade D:$delta Count:$schooldata{$grade}{$delta} \n};
}
}
# print qq{
Min:$min Max:$max
\n};
my %divdata; # Grade Divisions Data
if ( $grouping eq 'division' ) { # populate this structure
foreach my $grade ( sort {$a <=> $b} keys %schooldata ) {
my $div = $divmap{$grade};
if ( not $div ) {
print qq{$lex{Error}: No Div for $lex{Grade}:$grade \n};
next;
}
foreach my $delta ( $min .. $max ) {
$divdata{$div}{$delta} += $schooldata{$grade}{$delta};
# print qq{D:$delta DIV:$div GRD:$grade -- $divdata{$div}{$delta} };
# print qq{-- $schooldata{$grade}{$delta} \n};
}
}
}
# Division Level Output
if ( $grouping eq 'division' ) {
print qq{
\n};
# Exceptions table for this time period.
my $sth1 = $dbh->prepare("select lastname, firstname from studentall3 where studnum = ?");
my $sth = $dbh->prepare("select * from ssp_exceptions where to_days(tdate) >= to_days('$startdate')
and to_days(tdate) <= to_days('$enddate') and ssptype = 'dra'
order by tdate");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
my $studnum = $r{studnum};
my ($wd, $hid);
# WD
$sth4->execute($studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
if ( $count = $sth4->fetchrow ) {
$wd = qq{WD\n};
}
# HIDDEN
$sth5->execute($studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
if ( $count = $sth5->fetchrow ) {
$hid = qq{HID\n};
}
if ( $first ) { # start the table.
print qq{
\n};
print qq{
Reasons for Students Missing Test
\n};
# Table Heading
print qq{
Date
Student
Reason
New Tab
};
$first = 0;
}
if (not $studentname{$r{studnum}} ) { # find name and add to hash.
$sth1->execute($r{studnum});
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname,$firstname) = $sth1->fetchrow;
$studentname{$r{studnum}} = "$lastname, $firstname"; # store in case needed later.
}
print qq{
\n};
# end of exceptions.
# print qq{\n};
if ( $mindelta ) { # display students below minimum delta value.
my $first = 1;
foreach my $tg ( sort keys %lowdelta ) {
foreach my $delta ( sort keys %{ $lowdelta{$tg} } ) {
foreach my $sn ( @{ $lowdelta{$tg}{$delta} } ) {
if ( $first ) { # start table.
print qq{
\n};
print qq{
Students Below Grade Level
\n};
print qq{
Grade
Diff
Student
\n};
$first = 0;
}
print qq{
$tg
$delta
$studentname{$sn} ($sn)
\n};
}
}
}
if ( not $first ) { # we have records
print qq{
\n};
}
}
# print IEP Tables of students
if ( %iepstudents or %iepnames ) {
if ( $showiep ) {
print qq{
\n};
print qq{
IEP Students Skipped
\n};
print qq{
Grade
Student
\n};
# Get Grade
my $sth = $dbhiep->prepare("select grade from special where school = '$dbase'
and studnum = ?");
foreach my $key ( sort keys %iepnames ) {
my $studnum = $iepnames{$key};
my $name = $iepstudents{$studnum};
# Get grade
$sth->execute($studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $grade = $sth->fetchrow;
print qq{
$grade
$name ($studnum)
\n};
}
print qq{
\n};
}
}
print qq{\n};
exit;
} # end of selectReading
#----------
sub fmtDate {
#----------
my ( $year, $mon, $day ) = split /-/, shift;
return "$year-$s_month[$mon]-$day";
}
#----------------
sub showStartPage {
#----------------
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my $sth = $dbh->prepare("select count(*) from read_test where season = ?");
print qq{\n};
print qq{\n};
print qq{