\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);
print qq{
$schoolname
\n};
# Find 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 "IEP $studnum $firstname $lastname $grade \n";
next;
}
$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 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 = ?");
# Are they withdrawn?
my $sth4 = $dbh->prepare("select count(*) from studentwd 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; }
# 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 >= 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
if ( $mindelta ) { # display students below minimum delta value.
foreach my $tg ( sort keys %lowdelta ) {
foreach my $delta ( sort keys %{ $lowdelta{$tg} } ) {
foreach my $sn ( @{ $lowdelta{$tg}{$delta} } ) {
print qq{
\n};
# Now add the exceptions table for this time period.
my $sth1 = $dbh->prepare("select lastname, firstname from studentall 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;
if ( $first ) { # start the table.
print qq{
\n};
print qq{
Reasons for Students Missing Test
\n};
# Table Heading
print qq{
Date
Student
Reason
};
$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{