#!/usr/bin/perl
#  Copyright 2001-2021 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',
	   'Error' => 'Error',
	   'Class Reading Report' => 'Class Reading Report',
	   'Reading Level' => 'Reading Level',
	   'Name' => 'Name',
	   'Date' => 'Date',
	   'Continue' => 'Continue',
	   'Select' => 'Select',
	   'Student' => 'Student',
	   'Homeroom' => 'Homeroom',
	   'Grade' => 'Grade',
	   'Blank=All' => 'Blank=All',
	   'No Students Found' => 'No Students Found',
	   'Show Only Latest Test' => 'Show Only Latest Test',
	   'Gr' => 'Gr',
	   'Levels' => 'Levels',
	   'Score' => 'Score',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'EGr' => 'EGr',
	   'Students' => 'Students',
	   'Starting Season' => 'Starting Season',
	   'Ending Season' => 'Ending Season',
	   
	   );


# Grade => Start:End Reading Level
my %levelExpect = ( K => '0:3', 1 => '3:16', 2 => '16:28', 3 => '28:38', 4 => '40:50', 
		    5 => '50:60', 6 => '60:70', 7 => '70:80', 8 => '80:80' ); 


my %shortCategory = ( 'Oral Fluency' => 'OrFlu', 'Reading Engagement' =>'RdEg',
		      'Comprehension' => 'Comp', 'Printed Language Concepts' => 'pLgCcp',
		      'Oral Reading Fluency' => 'OrFlu');


my %seasondates = ('Spring' => {'start' => '01-01', 'end' => '03-31' },
		   'Summer' => {'start' => '05-15', 'end' => '06-30' },
		   'Fall' => {'start' => '09-01', 'end' => '10-31' }
    );


use DBI;
use CGI;
use Number::Format qw(:all);
use Cwd;
use Time::JulianDay;

my $self = 'readRptClass.pl';

my @time = localtime(time);
my $year = $time[5] + 1900;
my $month = $time[4] + 1;
my $currdate = "$year-$month-$time[3]";
my $schyear = $year;
if ( $month < 7 ){ $schyear = $schyear - 1; }
my $prevyear = $schyear - 1;

my $currstartdate = "$schyear-08-01"; # Aug 1 of this school year
my $prevstartdate = "$prevyear-08-01"; # August 1 of prev year.


eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $self: $@<br>\n";
    die $lex{Error}. "$self: $@\n";
}

# Get current dir so know what CSS to display and shift to teacher settings.
my $tcgiurl = 'tcgi-bin'; # not correctly set in configuration?
if ( getcwd() !~ /tcgi/ ) { # we are in cgi
    $tchcss = $css;
    $tchpage = $homepage;
    $tchdownloaddir = $downloaddir;
    $tchwebdownloaddir = $webdownloaddir;
    $tcgiurl = 'cgi-bin';
}


my $q = new CGI;
print $q->header; 
my %arr = $q->Vars;

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);

# Page Header
my $title = qq{$lex{'Class Reading Report'} - $schoolname};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$tchcss" type="text/css">
<style type="text/css">
th.fs6{font-size:60%;}
td { text-align:center; }
td.r { background-color:#822;color:white;font-size:120%;font-weight:bold; }
td.y { background-color:#BB1;color:white;font-size:120%;font-weight:bold; }
td.b { background-color:#228;color:white;font-size:120%;font-weight:bold; }
td.g { background-color:#282;color:white;font-size:120%;font-weight:bold; }
a {color:white; }
a.alt {color:blue; }
</style>\n};

if ( not $arr{page} ) { # calendar popup.
    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};
}

print qq{<script language="javascript" type="text/javascript">
function showhelp(type) {
  winName=window.open('/$tcgiurl/reading/showhelp.pl?id=' + type,'helpWindow',
  'height=300,width=700,screenX=100,screenY=100,resizeable');
  winName.focus();
}
</script>
$chartype\n</head><body style="padding:1em 6em 0 1em;">\n};


# Show Grade and Reading Levels Expected.
print qq{<div style="position:absolute;top:0;right:0;">\n};
print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
print qq{<tr><th>$lex{Gr}</th><th>$lex{'Levels'}</th></tr>\n};
foreach my $key ( sort keys %levelExpect ) {
    my ($start, $end ) = split(':', $levelExpect{$key} );
    if ( $start == '0' ) { $start = 'A'; }
    print qq{<tr><td>$key</td><td>$start - $end</td></tr>\n};
}
print qq{</table></div>\n};


print qq{[ <a class="alt" href="$tchpage">$lex{Main}</a> ]\n};
if ( getcwd() !~ /tcgi/ ) { # we are in cgi
    print qq{[ <a href="/ssp.html" style="color:blue;">SSP</a> ]\n};
}

print qq{<h1 style="text-align:left;margin:0;padding:0.5em;">$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showReport();
} 



#----------
sub fmtDate {
#----------

    my ( $year, $mon, $day ) = split '-', shift;
    return "$year-$s_month[$mon]-$day";
}


#----------------
sub showStartPage {
#----------------

    # Get grades and homerooms
    my (@homerooms, @grades );
    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;

    # 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;

=head    
    # Distinct Seasons
    my (@seasons, %seasons);
    $sth = $dbh->prepare("select distinct season from read_test where season is not NULL");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my %seasons;
    while ( my $season = $sth->fetchrow ) {
	my ($y,$s) = split('-',$season);
	if ( $s eq 'Fall' ) { $season = "$y-ZZZ"; } # fix sorting order.
	$seasons{$season} =  1; # Spring, Summer or Fall
    }
    @seasons = sort {$b cmp $a}  keys %seasons; # with 'aaa' rather than 'Spring'.
    @revseasons = sort {$a cmp $b}  keys %seasons; # with 'aaa' rather than 'Spring'. 
=cut
    
    # Form Start - Student Group
    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="1">\n};


    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};

    # Select Grade
    print qq{<tr><td class="bra">$lex{Select} $lex{Grade}</td>\n};
    print qq{<td class="la"><select name="grade"><option></option>\n};
    foreach my $grade ( @grades ) {
	print qq{<option>$grade</option>\n};
    }
    print qq{</select></td></tr>\n};


    print qq{<tr><td class="bra">OR</td><td></td></tr>\n};

    # Select Homeroom
    my $sth = $dbh->prepare("select lastname, firstname from staff s, staff_multi sm 
      where s.userid = sm.userid and field_name = 'homeroom' and field_value = ?");


    print qq{<tr><td class="bra">$lex{Select} $lex{Homeroom}</td>\n};
    print qq{<td class="la"><select name="homeroom"><option></option>\n};
    foreach my $hr ( @homerooms ) {
	$sth->execute($hr);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;
	my $hrname = $hr;
	if ( $lastname ) { $hrname = "$hr ($firstname $lastname)"; }

	print qq{<option value="$hr">$hrname</option>\n};
    }
    print qq{</select></td></tr>\n};

    # Show Withdrawn?
    print qq{<tr><td  class="bra">Show Withdrawn Students</td>};
    print qq{<td class="la"><input type="checkbox" name="showwithdrawn" value="1"></td></tr>\n};


    # Show Only Latest
    #print qq{<tr><td class="bra">$lex{'Show Only Latest Test'}</td>\n};
    #print qq{<td class="la">\n};
    #print qq{<input type="checkbox" name="onlylatest" value="1" checked="checked">\n};
    #print qq{</td></tr>\n};

    # Default to always only the latest value.
    print qq{<tr><td colspan="2"><input type="hidden" name="onlylatest" value="1"></td></tr>\n};

#    print qq{<tr><td colspan="2"><hr></td></tr>\n};
    
    # Season (override dates)
    my ($sy,$ey) = split('-', $schoolyear);
    my $currjd = julian_day( split('-',$currdate));

    # Start Date for current school year seasons.
    my $falljd = julian_day($sy,'09','01'); # Sept 1
    my $springjd = julian_day($ey,'01','03'); # Jan 1
    my $summerjd = julian_day($ey,'05','15'); # May 15

    print qq{<tr><td class="bra">Season</td><td class="la">};
    print qq{<select name="season"><option></option>\n};

    if ( $currjd >= $summerjd ) {
	print qq{<option value="$ey-Summer">$ey Summer (May 15 - June 30)</option>\n};
    }
    if ( $currjd >= $springjd ) {
	print qq{<option value="$ey-Spring">$ey Spring (Jan 1 - March 31)</option>\n};
    }

    for( my $yr = $sy; $yr >= 2011; $yr-- ) {
	print qq{<option value="$yr-Fall">$yr Fall (Sept 1 - Oct 31)</option>\n};
	print qq{<option value="$yr-Summer">$yr Summer (May 15 - June 30)</option>\n};
	print qq{<option value="$yr-Spring">$yr Spring (Jan 1 - March 31)</option>\n};
    }
    print qq{</select> (Override Dates)</td></tr>\n\n};

    
    # Start Date
    print qq{<tr><td class="bra">$lex{'Start Date'}</td><td class="la"><input type="text" };
    print qq{name="startdate" id="sdate" size="10" value="$currstartdate">\n};
    print qq{<button type="reset" id="start_trigger">...</button>\n};
    print qq{</td></tr>\n\n};


    # End Date
    print qq{<tr><td class="bra">$lex{'End Date'}</td><td class="la"><input type="text" };
    print qq{name="enddate" id="edate" size="10" value="$currdate">\n};
    print qq{<button type="reset" id="end_trigger">...</button></td></tr>\n\n};



=head    
    # Select Single Season
    print qq{<tr><td class="bra">$lex{'Starting Season'}</td>\n};
    print qq{<td class="la"><select name="startseason"><option></option>\n};
    foreach my $season ( @revseasons ) {
	if ( $season eq 'Undefined' ) { next; } # remove undefined.
	$season =~ s/ZZZ/Fall/;
	print qq{<option>$season</option>\n};
    }
    print qq{</select></td></tr>\n};


    # Select Ending Season
    print qq{<tr><td class="bra">$lex{'Ending Season'}</td>\n};
    print qq{<td class="la"><select name="endseason"><option></option>\n};
    foreach my $season ( @seasons ) {
	if ( $season eq 'Undefined' ) { next; } # remove undefined.
	$season =~ s/ZZZ/Fall/;
	print qq{<option>$season</option>\n};
    }
    print qq{</select></td></tr>\n};
=cut
    
    
    print qq{<tr><td></td><td class="la"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</table></form>\n};

    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :    "sdate", 
        ifFormat       :    "%Y-%m-%d",
        button         :    "start_trigger",
        singleClick    :    false,
        step           :    1
    });

    Calendar.setup({
        inputField     :    "edate",
        ifFormat       :    "%Y-%m-%d",
        button         :    "end_trigger",
        singleClick    :    false,
        step           :    1
    });
    </script>\n};

    
    print qq{</body></html>\n};

    exit;

} # end of showStartPage



#-------------
sub showReport {
#-------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # Passed: startdate, enddate, season, grade or homeroom, onlylatest(always).
    # Previously I was using starting and ending season without any date ranges.
    
    # Load the reading library containing the scoreToGrade function.
    eval require "../../lib/libreading.pl";
    if ( $@ ) {
	print $lex{Error}. " $self: $@<br>\n";
	die $lex{Error}. "$self: $@\n";
    }

    my $sdate = fmtDate( $arr{startdate} );
    my $startdate = $arr{startdate};
    delete $arr{startdate}; 

    my $edate = fmtDate( $arr{enddate} );
    my $enddate = $arr{enddate};
    delete $arr{enddate};

    if ( $arr{season} ) { # override start and end dates. Only use those dates below (no season)
	# Passed "Year-Season" values; convert into dates
	# Spring = 1, Summer = 2, Fall = 3
	my %seasonText2Num = ('Spring' => 1, 'Summer' => 2, 'Fall' => 3 );
	my %seasonNum2Text = reverse %seasonText2Num;
    
	my ($yr,$ss) = split('-', $season );
	$startdate = $seasondates{$ss}{start};
	$startdate = qq{$yr-$startdate};
	$enddate = $seasondates{$ss}{end};
	$enddate = qq{$yr-$enddate};
    }
    delete $arr{season};
    # remove season.
    

    my $studtable = 'student';
    if ( $arr{showwithdrawn} ) {
	$studtable = 'studentall';
    };


    # Get Students with this season / dates  (all school) subselect for homeroom/grade below
    my $sth;
    if ( $season ) {
	$sth = $dbh->prepare("select studnum from read_test 
               where season = '$season' order by tdate desc");

    } else {
	$sth = $dbh->prepare("select studnum from read_test 
               where to_days(tdate) >= to_days('$startdate') and 
               to_days(tdate) <= to_days('$enddate') order by tdate desc");
    }
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    my %studtest; # students with tests in this date range (all school)
    while ( my $studnum = $sth->fetchrow ) {
	$studtest{$studnum} = 1;
    }


    # Display Dates;
    print qq{<h3>$lex{'Start Date'} $sdate | $lex{'End Date'} $edate</h3>\n};

    # Student Select / Display
    my ($select,$selectvalue);  # value of homeroom or grade
    print qq{<h3 style="text-align:left;font-size:120%;margin:0;padding:0.3em;">};
    
    if ( $arr{grade} ) {
	print qq{$lex{Grade} $arr{grade}};
	$selectvalue = $dbh->quote($arr{grade});
	$select = "where grade = $selectvalue";

    } elsif  ( $arr{homeroom} ) {

	print qq{$lex{Homeroom} $arr{homeroom}};
	$selectvalue = $dbh->quote( $arr{homeroom} );
	$select = "where homeroom = $selectvalue";

    } else {
	print qq{All Students\n};
    }


    # Get Teacher Name if a homeroom
    if ( $arr{homeroom} ) {
	my $sth = $dbh->prepare("select lastname, firstname from staff as s, staff_multi as sm
          where sm.userid = s.userid and field_name = 'homeroom' and field_value = ?");
        $sth->execute( $arr{homeroom} );
        if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth->fetchrow;
	print qq{ &ndash; $firstname $lastname};
    }
    print qq{</h3>\n}; # finish description line

    # Next Description Line
    print qq{<div style="font-size:120%;text-align:left;">};
    print qq{<span style="color:red;">**</span> = Prev LLI program, };
    print qq{<span style="color:red;">***</span> = Current LLI program</div>\n};

	
    # Now get student names, grade/homeroom selection and reading levels
    my $sth1 = $dbh->prepare("select * from read_test where studnum = ? and 
			     to_days(tdate) >= to_days('$startdate') and 
			     to_days(tdate) <= to_days('$enddate') order by tdate desc");

    my $sth = $dbh->prepare("select lastname, firstname, studnum from $studtable $select 
			    order by lastname, firstname");
    $sth->execute;
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

   
    my %readDra; # reading level and dra type
    my (@students, %sort, %notests);  # notests is hash for kids without tests this time period.
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {
	push @students, $studnum;
	$sort{"$lastname$firstname$studnum"} = $studnum;

	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth1->fetchrow_hashref;
	if ( not $ref ) { # no tests in this time period
	    $notests{$studnum} = 1;
	    next; # student
	}
	my %r = %$ref;

	if ( not $r{dratype} ) { $r{dratype} = 2; }

#	print "RL:$r{readlevel} DRA:$r{dratype} SN:$studnum<br>\n";
	
	
	$readDra{ $r{readlevel} }{ $r{dratype} }{ $studnum } = 1;
    }

#    print "<br>\n";
    
#    use Data::Dumper;
#    print Dumper %readDra;
    
    foreach my $rl ( sort {$a <=> $b} keys %readDra ) {
	foreach my $dratype ( sort keys %{ $readDra{$rl} } ) {
#	    print "RL:$rl DRA:$dratype STUD:". %{$readDra{$rl}{$dratype}}. "<br>\n";
	    my @temp = keys %{$readDra{$rl}{$dratype}};
#	    print @temp, "<br>\n";
	    
	    showReadingLevel( $rl, $arr{onlylatest}, \@temp, $startdate, $enddate, $dratype );
	}
    }

    

    print qq{</body></html>\n};

    exit;

}




#-------------------
sub showReadingLevel {  # now we must add the DRA type.
#-------------------
    my ( $readinglevel, $onlylatest, $stud_ref, $startdate, $enddate, $dratype ) = @_;

#    print "SD:$startdate ED:$enddate<br>\n";
    
    my $startjd = julian_day(split('-',$startdate));
    my $endjd = julian_day(split('-',$enddate));

    my @students = @$stud_ref;


    # Check that at least one test exists. If not, return
    my $sth = $dbh->prepare("select id, dratype from read_test 
     where studnum = ? and readlevel = ? and to_days( tdate ) >= to_days( '$startdate' ) 
     and to_days( tdate ) <= to_days( '$enddate')"); # get tests

    my $testflag = 0;
    foreach my $studnum ( @students ) {
	# Check for Tests with correct DRA
	$sth->execute( $studnum, $readinglevel );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $id, $dra ) = $sth->fetchrow ) {
	    if ( not $dra ) { $dra = 2; }
#	    print "ID:$id DRA:$dra<br>\n";
	    if ( $dra == $dratype ) {
		$testflag = 1; last;}
	}
    }
    if ( not $testflag ) { return; }  # we need at least one test

    

    # Setup for Reading Level Heading, Get Category and Name, based on dratype
    my $readtable = 'read_level_dra2';
    if ( $dratype == 3 ) {
	$readtable = 'read_level_dra3';
    }
	
    my $sth = $dbh->prepare("select name, category, seq from $readtable where readlevel = ?");

    my %objectives;
    my %catcount;

    $sth->execute( $readinglevel );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    while ( my ( $name, $category, $seq ) = $sth->fetchrow ) {
	# $catcount{ $category } = $catcount{$category} + 1;
	$catcount{ $category }++; # increment count
	$objectives{ $seq } = $category. '::'. $name;
    }

    # foreach my $cat ( keys %catcount ) { print "CAT:$cat VAL:$catcount{$cat}<br>\n"; }


    # Set Index values for all objectives
    my $count = 0;
    my $startcount;
    my %skip;
    my $currcat = '';

    # Print out First Heading Line with the Categories
    my $colspan = 4 + keys %catcount; # %catcount holds categories and number of types in each.

    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
    print qq{<tr><th colspan="$colspan">$lex{'Reading Level'} $readinglevel - DRA $dratype</th>\n};

    my @catorder; # ordering of categories
    my %temp; # used to track cat in loop

    foreach my $key ( sort { $a <=> $b } keys %objectives ) { # numeric sort 

	my ( $category, $name ) = split(/::/, $objectives{$key} );

	if ( not $temp{$category} ) {
	    push @catorder, $category;
	    $temp{$category} = 1;
	}

	$oldcat = $currcat;
	$currcat = $category;

	if ( $oldcat ne $currcat and $count ) {
	    $diff = $count - $startcount;
	    print qq{<th colspan="$diff" style="font-size:100%;">$oldcat</th>\n};
	    $skip{$oldcat} = $diff;
	    $startcount = $count;
	}
	
	$count++;

    }
    $diff = $count - $startcount;
    $skip{$currcat} = $diff;
    print qq{<th colspan="$diff" style="font-size:100%;">$currcat</th></tr>\n};


    # Print Out Second Heading Line
    print qq{<tr><th>$lex{Name}</th><th>$lex{Date}</th><th title="Equivalent Grade">$lex{EGr}</th>\n};


    foreach my $key ( @catorder ) { # key is really category
	print qq{<th>$shortCategory{$key}<br>$lex{Score}</th>};
    }
    print qq{<th>Overall<br>Score</th>\n};


    # Query reading level table for help; $readtable set above
    my $sth1 = $dbh->prepare("select id, help1 from $readtable where readlevel = ? and
      category = ? and name = ?");

    foreach my $key ( sort { $a <=> $b } keys %objectives ) { # numeric sort 
	my ( $category, $name ) = split(/::/, $objectives{$key} );
	
	# Get the record id and help for this, if it exists
	$sth1->execute( $readinglevel, $category, $name );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($id, $help1 ) = $sth1->fetchrow;
	print qq{<th class="fs6">};
	if ( $help1 ) {
	    print qq{<a href="javascript:showhelp($id)">$name</a>};
	} else {
	    print qq{$name};
	}
	print qq{</th>\n};

    }
    print qq{</tr>\n};
    # End of second row of heading.


    # Now loop through all students printing results; setup queries first
    $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    $sth1 = $dbh->prepare("select id, tdate from read_test 
     where studnum = ? and readlevel = ? and to_days( tdate ) >= to_days( '$startdate' ) 
     and to_days( tdate ) <= to_days( '$enddate') order by tdate desc"); # get tests

    my $sth2 = $dbh->prepare("select category, name, score, seq from read_test_score 
      where testid = ? order by seq");

    my $sth3 = $dbh->prepare("select progid from lint_student where studnum = ? and 
      progid is not NULL and progid != 0");
    my $sth4 = $dbh->prepare("select startdate, enddate from lint_program where id = ?");

    my $sth5 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    # startdate of program is > start date of this report AND
    # start of program is < end date of this report


    foreach my $studnum ( @students ) {

        # Get Name
	$sth->execute( $studnum );
	my ( $lastname, $firstname ) = $sth->fetchrow;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	# Check if withdrawn
	my $wd;
	$sth5->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my  $wdcount = $sth5->fetchrow;
	if ( $wdcount ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">WD</span>\n};
	}

	# Check if he/she has had LLI help during this period
	# First.... does the student have a program
	my ($lliflag, $prevlli );
	$sth3->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( $progid = $sth3->fetchrow ) {

	    # Get Dates of the program... do they overlap with this reporting period?
	    $sth4->execute($progid);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($pstart,$pend) = $sth4->fetchrow;
#	    print "Program Start:$pstart End:$pend<br>\n";
	    my $progstartjd = julian_day(split('-',$pstart));
	    my $progendjd = julian_day(split('-',$pend));
	    
	    if ( ($progstartjd >= $startjd and $progstartjd <= $endjd ) or
		 ($progendjd >= $startjd and $progendjd <= $endjd ) or
		 ($progstartjd >= $startjd and $progendjd <= $endjd ) ) {
		$lliflag = qq{<span style="color:red;font-weight:bold;">***</span>};
		# ie program start overlaps or program overlaps the reporting dates, we have a match.
	    }

	    if ( $progendjd < $startjd ) { # it ended previously to this time period
		$prevlli = 1;
	    }
	    
	}

	if ( not $lliflag and $prevlli ) { 
	    $lliflag = qq{<span style="color:red;font-weight:bold;">**</span>};
	}
	

	# Get Tests within date range
	$sth1->execute( $studnum, $readinglevel );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $trows = $sth1->rows; # test rows

	# if $onlylatest, limit to 1 test
	if ( $onlylatest and $trows > 1 ) { $trows = 1; } 

	for ( 1 .. $trows ) {

	    my ( $testid, $tdate ) = $sth1->fetchrow;

	    print qq{<tr><td class="la">$wd <b>$lastname</b>, $firstname $lliflag };
	    
	    print qq{<form action="/$tcgiurl/reading/readRptStudent.pl" method="post" };
	    print qq{style="display:inline;" target="_blank">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    print qq{<input type="hidden" name="studnum" value="$studnum">\n};
	    print qq{<input type="submit" value="All">\n};
	    print qq{</form>\n};

	    print qq{</td><td>};
	    print fmtDate($tdate). qq{</td>\n};
	    
	    # Loop through all the items.
	    $sth2->execute( $testid );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my @studata;
	    my $total = 0;
	    my $count = 0;
	    my %cattotal;
	    my %catcount;
	    

	    while ( my ( $category, $name, $score, $seq ) = $sth2->fetchrow ) {

		# Check objective
	        my ( $tcat, $tname ) = split(/::/, $objectives{ $seq } );

		$cattotal{$category} += $score;
		$catcount{$category} += 1;

		$studata[$seq] = $score;
		$total += $score;
		$count++;
	    }


	    # Print Equivalent Grade
	    my $equivgrade = scoreToGrade($total, $readinglevel );
	    print qq{<td>$equivgrade</td>\n};

	    
	    # Category Scores
	    my $totalscore;
	    my $totalpossible;

	    foreach my $cat ( @catorder ) {
		my $tot = $catcount{$cat} * 4;
		print qq{<td>$cattotal{$cat}/$tot</td>};
		$totalscore += $cattotal{$cat};
		$totalpossible += $catcount{$cat};
	    }
	    $totalpossible *= 4;
	    print qq{<td>$totalscore/$totalpossible</td>};


	    # Print Scores
	    my %class = ( 1 => 'r', 2 => 'y', 3 => 'b', 4 => 'g' );
	    foreach my $key ( sort { $a <=> $b } keys %objectives ) { # numeric sort 
		my $val = $studata[ $key ];
		my $cl = $class{ $val };
		print qq{<td class="$cl">$val</td>};
	    }
	    print qq{</tr>\n};
	} # end of test printing loop (1 row)

    } # end of students loop.

    print qq{</table>\n};
    return;

}
