#!/usr/bin/perl
#  Copyright 2001-2023 Leslie Richardson

#  This file is part of Open Admin for Schools.


my %lex = ('Main' => 'Main',
	   'Student' => 'Student',
	   'Student(s)' => 'Student(s)',
	   'Edit' => 'Edit',
	   'Add' => 'Add',
	   'Delete' => 'Delete',
	   'Category' => 'Category',
	   'New' => 'New',
	   'Description' => 'Description',
	   'Record(s) Stored' => 'Record(s) Stored',
	   'Contact Log' => 'Contact Log',
	   'Error' => 'Error',
	   'or' => 'or',
	   'Please Log In' => 'Please Log In',
	   'Save' => 'Save',
	   'Date' => 'Date',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Student Group' => 'Student Group',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Or' => 'Or',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'Next Page' => 'Next Page',
	   'Check' => 'Check',
	   'Continue' => 'Continue',
	   'Select' => 'Select',
	   'Mode' => 'Mode',
	   'Not Found' => 'Not Found',
	   'New' => 'New',
	   'No Record(s) Found' => 'No Record(s) Found',
	   'More' => 'More',
	   'Record(s)' => 'Record(s)',
	   'Deleted' => 'Deleted',

	   );

# for attendance function 
my %lexattend = ('Absent' => 'Absent',
		 'Late' => 'Late');

my $self = 'rptLearnProfile.pl';

use DBI;
use CGI;
use Cwd;
use Number::Format;

# Get current dir so know what path for config files.
my $configpath = '..';
my $teachermode;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $teachermode = 1;
}


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

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

eval require "$configpath/lib/libattend.pl";
if ( $@ ) {
    print $lex{Error}. " $self: $@<br>\n";
    die $lex{Error}. "$self: $@\n";
}

eval require "$configpath/lib/libreading.pl";
if ( $@ ) {
    print $lex{Error}. " $self: $@<br>\n";
    die $lex{Error}. "$self: $@\n";
}



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

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


# Get Current Date
my @tim = localtime(time);
my $year = $tim[5] + 1900;
my $month = $tim[4] + 1;
my $day = $tim[3];
my $currdate = "$year-$month-$day";
my $formatdate = qq{$s_month[$month] $day, $year};
my $curryear = $year;
if ( $month > 7 ) {
    $curryear++; # increment to get correct school year
    # ie; 2023-08 is really a 2024 school year (ie. 2023-2024) for database name on backup sever
}
    
my $userid = $ENV{'REMOTE_USER'};

# Get User Name
my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
$sth->execute( $userid );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ( $lastname, $firstname ) = $sth->fetchrow;
if ( not $lastname ) { $lastname = $userid; };


if ( $teachermode ) { # running on teacher site
    $css = $tchcss;
    $homepage = $tchpage;
}


# Print Page Header
my $title = qq{Cumulative Learning Profile};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

print qq{$chartype\n</head><body>\n};

print qq{<div>[ <a href="$homepage">$lex{Main}</a> };
if (not $teachermode ) { print qq{| <a href="/ssp.html">SSP</a>}; }
print qq{] };
print qq{<span style="font-weight:normal;font-size:normal;">$formatdate</span> };
print qq{| $firstname $lastname</div>\n};
print qq{<h3>$title </h3>\n};

# foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }


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

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    if ( $arr{student} ) {
	searchStudent( $arr{student} );
    } else {
	searchGroup( $arr{group}, $arr{groupid} );
    }

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

}



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

    print qq{<h1>$lex{Select} $lex{'Student(s)'}</h1>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellspacing="0" cellpadding="3" border="0">\n};

    print qq{<tr><td class="bra">$lex{'Student Group'}</td>\n<td>};
    print qq{<select name="group"><option value="grade">$lex{Grade}</option>\n};
    print qq{<option value="homeroom">$lex{Homeroom}</option></select>\n };
    print qq{<input type="text" name="groupid" size="6">};

    print qq{<tr><td class="ra">$lex{Or}</td><td></td></tr>\n};

    print qq{<tr><td class="bra">$lex{Student}</td>};
    print qq{<td><input type="text" name="student" size="30"></tr>\n};
    print qq{<tr><td></td><td class="cn">$lex{'Last,First/Last/Initials/Studnum'}</td></tr>\n};

    print qq{<tr><td class="bra">$lex{Check} $lex{'Next Page'}</td>};
    print qq{<td><input type="checkbox" name="check"></td></tr>\n};

    print qq{<tr><td></td><td class="la">};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

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

}



#----------------
sub searchStudent {
#----------------

    my $checked;
    if ( $arr{check} ) {
	$checked = q{checked = "checked"};
    }

    my $student = shift;

    # Setup the Search
    if ($student =~ /\d+/) {  # we have a student number
	$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
          where studnum = ?");
	$sth->execute( $student );

    } else { # we have words possibly with a comma
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ($lastname and $firstname){ # both entered.
	    $sth = $dbh->prepare("select  lastname, firstname, studnum from studentall 
             where lastname = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname );

	} elsif ($lastname and not $firstname){ # only lastname (no comma)
	    if (length($lastname) == 2){ # search by initials: fi, li.
		my $fi = substr($lastname,0,1). '%'; 
		my $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                 where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );

	    } else {
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                  where lastname = ? order by firstname");
		$sth->execute( $lastname );
	    }
	} else { # print an error....
	    print qq{<h1>$lex{Error}</h1>\n};
	    print qq{</body></html>\n};
	    exit;
	}

    } # Last Else
    # We should now have a $sth defined.


    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;
    # print qq{Rows: $rows<br>\n};


    if ( $rows < 1 ) { 
	print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};
	print qq{<form action="$self" method="post">\n};
	print qq{<input type="hidden" name="page" value="1">\n};
	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};

	print qq{<tr><td colspan="2" class="cn">$lex{Student} };
	print qq{($lex{'Last,First/Last/Initials/Studnum'})<br>};
	print qq{<input type="text" name="student" size="30"></td></tr>\n};

	print qq{<tr><td colspan="2" class="cn">\n};
	print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
	print qq{</table></form></body></html>\n};
	exit; 
    }

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};

    # Select Students
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};

    print qq{<tr><td colspan="2" class="cn">\n};
    print qq{<input type="submit" value="$lex{'Continue'}"></td></tr>\n};

    print qq{<tr><th>$lex{Select}</th><th>$lex{Student}</th></tr>\n};


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

    my $first = 1;
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {

	# Find any contacts
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	print qq{<tr><td class="cn"><input type="checkbox" name="$studnum" value="1" $checked></td>\n};
	print qq{<td class="la"><b>$lastname</b>, $firstname ($studnum)</td>};
	print qq{</tr>\n};


    }

    print qq{<tr><td colspan="2" class="cn">\n};
    print qq{<input type="submit" value="$lex{'Continue'}"></td></tr>\n};

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

    exit;

} # end of searchStudent



#--------------
sub searchGroup {
#--------------

    # search for a group of students (by grade(s) or homeroom(s))
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my $checked;
    if ( $arr{check} ) {
	$checked = q{checked = "checked"};
    }

    my ( $group, $groupid ) = @_;

    # Select Students
    my @students;
    my $sort = 'name'; # hold type of sort.

    if ( $groupid ) { # then we have to do something...

	my @groups;
	@groups = split /\s+/, $arr{groupid};
	if ( $group eq 'grade' ) {
	    $group = 'grade';
	    $sort = 'grade';
	} else {
	    $group = 'homeroom';
	    $sort = 'homeroom';
	}

	my $sth = $dbh->prepare("select studnum from student where $group = ? 
         order by $group, lastname, firstname");
	foreach my $grp ( @groups ) {
	    $sth->execute( $grp );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $studnum = $sth->fetchrow ) {
		push @students, $studnum;
	    }
	}

    } else { # all students

	my $sortorder = "lastname, firstname";
	if ( $arr{sortorder} eq $lex{Homeroom} ) {
	    $sortorder = "homeroom, lastname, firstname";
	    $sort = 'homeroom';
	} elsif ( $arr{sortorder} eq $lex{Grade} ) {
	    $sortorder = "grade, lastname, firstname";
	    $sort = 'grade';
	}

	$sth = $dbh->prepare("select studnum from student order by $sortorder");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth->fetchrow ) {
	    push @students, $studnum;
	}
    }

    # Now loop through all students
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};

=head    
    # Get the Mode:  Add, Edit, Delete.
    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};
    print qq{<tr><th colspan="2">$lex{Mode}</th></tr>\n};
    print qq{<tr><td class="la"><input type="radio" name="mode" value="add"> $lex{Add}</td></tr>\n};
    print qq{<tr><td class="la"><input type="radio" name="mode" value="edit"> $lex{Edit}</td></tr>\n};
    print qq{<tr><td class="la"><input type="radio" name="mode" value="delete"> $lex{Delete}</td>};
    print qq{</tr>\n};
    print qq{</table><p></p>\n};
=cut
    
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><td colspan="3" class="cn">\n};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{<tr><th>$lex{Select}</th><th>$lex{Student}</th></tr>\n};

    my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth1 = $dbh->prepare("select count(*) from contactlog where studnum = ?");

    my $first = 1;

    foreach my $studnum ( @students ) {

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

	# Find any transactions; otherwise skip
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	print qq{<tr><td class="cn"><input type="checkbox" name="$studnum" value="1" $checked></td>\n};
	print qq{<td class="la"><b>$lastname</b>, $firstname ($studnum)</td>};
	print qq{</tr>\n};

    }

    print qq{<tr><td colspan="3" class="cn">\n};
    print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};

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

    exit;

} # end of selectGroup



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

    # print qq{<div>Show Report</div>\n};
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # Passed: student numbers in keys


    # Get all math outcomes;
    %outcomes; 
    my $sth = $dbh->prepare("select oid,odesc,grade from mathca_outcomes");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    while (my ($oid,$odesc,$grade) = $sth->fetchrow ) {
	$outcomes{$grade}{$oid} = $odesc;
    }

#    foreach my $gr ( sort keys %outcomes ) {
#	foreach my $oid ( sort keys %{ $outcomes{$gr}} ) {
#	    print "GR:$gr OID:$oid DESC:$outcomes{$gr}{$oid}<br>\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{<div style="font-weight:bold;">IEP Database $iepdbase</div>\n};
    my $sth1 = $dbhiep->prepare("select count(*) from special where school = '$dbase' and studnum = ?");
    # end of getting IEP database.

   
    # get name and sorted list by name
    my $sth = $dbh->prepare("select lastname, firstname,grade from studentall where studnum = ?");
    my (%sorted,%name,%grade);
    foreach my $studnum ( keys %arr ) {
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname,$firstname,$grade) = $sth->fetchrow;
	$sorted{"$lastname$firstname$studnum"} = $studnum;
	$name{$studnum} = qq{<b>$lastname</b>, $firstname};
	$grade{$studnum} = $grade;
    }


    my %prevatt; # $prevatt{studnum}{$year} = $grade:$attendance; # get prev attendance for students
    foreach my $offset (1..2) { # year offset from current school year

	my $prevyear = $curryear - $offset;  # curryear set at top of script
	my $remotedbase = $dbase. $prevyear;
#	print qq{<div>Remote DB:$remotedbase</div>\n};
	
	# Get remote databases
	# my $remotedbase = 'information_schema';
	my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
	my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);

	# load configuration for start,end,ppd
	my @fieldnames = qw(schoolstart schoolend g_ppd);
	my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
	foreach my $var ( @fieldnames ) {
	    $sth->execute( $var );
	    my $datavalue = $sth->fetchrow;
	    eval $datavalue;
	    if ( $@ ) {
		print "$lex{Error}: $@<br>\n";
		die "$lex{Error}: $@\n";
	    }
	}

	my $sth = $dbhr->prepare("select lastname,firstname,grade from studentall where studnum = ?");

	foreach my $key ( sort keys %sorted ) {
	    my $studnum = $sorted{$key};

	    $sth->execute($studnum);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($ln,$fn,$grade) = $sth->fetchrow;
	    if ( not $ln and not $grade ) { next; } # not found here.
	    my $ppd = $g_ppd{$grade};
#	    print "SN:$studnum GR:$grade PPD:$ppd<br>\n";
	    
	    my $daysopen = calcDaysOpen($schoolstart,$schoolend,$dbhr,"GR:$grade");
	    my $absval = calcAttendance($studnum,$schoolstart,$schoolend,$ppd,\%lexattend,$dbhr);
	    my ($absent,$late) = split(':',$absval);
	    my $present = ($daysopen - $absent) / $daysopen;
	    my $percent = round( $present * 100,1);
#	    print "SNUM:$studnum Absent:$absent Present:$present Open:$daysopen<br>\n";

	    
	    $prevatt{$studnum}{$prevyear} = qq{$grade:$percent};
	}
	
    }

# Test %prevatt    
#    foreach my $studnum ( sort keys %prevatt ) {
#	foreach my $year ( sort keys %{ $prevatt{$studnum}} ) {
#	    print "STUD:$studnum YR:$year VAL:$prevatt{$studnum}{$year}<br>\n";
#	}
#    }


    # Starting H Rule
    print qq{<hr style="margin-left:0;width:100ch;height:4px;margin-top:1em;};
    print qq{background-color:black;" />\n};
    
    # Loop through all students;
    foreach my $key ( sort keys %sorted ) {
	my $studnum = $sorted{$key};

	# check for IEP.
	my $iep;
	$sth1->execute($studnum);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	if ($count ) {
	    $iep = qq{- <span style="color:green;">IEP Student</span>};
	}

	print qq{<h3>$name{$studnum} };
	print qq{(<span style="font-size:normal;">Gr$grade{$studnum}</span>) $iep</h3>\n};

	# attendance - multi year; do current first.
	my %attendance; # attendance{grade} = percent.
	# current attendance.
	
	my $ppd = $g_ppd{$grade{$studnum}};
#	print qq{<div>Grade $grade{$studnum} PPD:$ppd</div>\n};
	
	my $daysopen = calcDaysOpen($schoolstart,$schoolend,$dbh,"GR:$grade");
	my $absent = calcAttendance($studnum,$schoolstart,$schoolend,$ppd,\%lexattend,$dbh);
	my $present = ($daysopen - $absent) / $daysopen;
	my $percent = round( $present * 100,1);
	
#	print qq{<div>Open:$daysopen ABS:$absent Present:$percent%</div>\n};

	print qq{<h3>Attendance</h3>\n};
	
	# Display attendance table.
	print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
#	print qq{<caption style="font-size:120%;font-weight:bold;">Attendance</caption>\n};
	print qq{<tr>};
	foreach my $year ( sort keys %{ $prevatt{$studnum}} ) {
	    my ($gr,$percent) = split(':', $prevatt{$studnum}{$year});
	    print qq{<th>Grade $gr<br>$year</th>};
	}
	print qq{<th>Current</th></tr>\n};
	
	# now attendance data row
	print qq{<tr>};
	foreach my $year ( sort keys %{ $prevatt{$studnum}} ) {
	    my ($gr,$percent) = split(':', $prevatt{$studnum}{$year});
	    print qq{<td>$percent%</td>};
	}
	print qq{<td>$percent%</td></tr>\n};
	print qq{</table>\n};

	
	# Reading
	my %reading; # reading{year} = grade:equivgrade
	my $sth1 = $dbh->prepare("select sum(score) from read_test_score where testid = ?");
	
	my $sth = $dbh->prepare("select * from read_test where studnum = ? and season like '%-Spring'");
	$sth->execute($studnum);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    my $id = $r{id};

	    my $year = $r{season};
	    $year =~ s/-Spring//; # strip out tail to get year only.
	    
	    # get sum of scores
	    $sth1->execute($id); # id of the readtest.
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $sum = $sth1->fetchrow;

	    # Get equivalent grade
	    my $equivgrade = scoreToGrade($sum, $r{readlevel},$r{dratype});

	    $reading{$year} = qq{$r{tgrade}:$equivgrade};
	}
	
	# print Reading table.
	print qq{<h3>Reading</h3>\n};
	print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
	# print qq{<caption style="font-size:120%;font-weight:bold;">Reading</caption>\n};

	if ( %reading ) { # we have values
	    print qq{<tr>};
	    foreach my $yr ( sort keys %reading ) {
		my ($grade,$egr) = split(':', $reading{$yr});
		print qq{<th>Grade $grade<br>$yr</th>};
	    }
	    print qq{</tr><tr>\n};
	    foreach my $yr ( sort keys %reading ) {
		my ($grade,$egr) = split(':', $reading{$yr});
		print qq{<td title="Equivalent Grade">$egr</td>};
	    }
	    print qq{</tr>\n};
	} else { # no data
	    print qq{<tr><th>No Records</th></tr>\n};
	}
	print qq{</table>\n};

	
	
	# Math
	print qq{<h3>Mathematics };
	print qq{<span style="font-size:80%;">Hover on title for description</span></h3>\n};
	my %math; # math{year}{outcome} = score
	
	my $sth = $dbh->prepare("select * from mathca_scores where studnum = ? 
				and prepost = 'posttest'");
	$sth->execute($studnum);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    $math{ $r{tgrade} }{ $r{outcome} } = qq{$r{schoolyear}:$r{score}};
	}


	foreach my $grade ( sort {$a <=> $b} keys %math ) {
	    
	    # start table and print outcomes for this grade;
	    print qq{<table cellspacing="0" cellpadding="3" border="1" style="margin:0.5em;">\n};

	    my @out = sort keys %{ $outcomes{$grade}};
	    
	    # print outcomes row
	    print qq{<tr><th>Year</th>};
	    foreach my $oid ( @out ) {
		print qq{<th title="$outcomes{$grade}{$oid}">$oid</th>};
	    }
	    print qq{</tr>\n<tr>};

	    # collect years for these outcomes
	    my %years;
	    foreach my $oid ( @out ) {
		my ($yr,$score) = split(':', $math{$grade}{$oid} );
		if ( $yr ) { $years{$yr} = 1; }
	    }
	    my $years = join(',', keys %years);

	    
	    # print these outcomes;
	    print qq{<td>$years</td>};
	    foreach my $oid ( @out ) {
		my ($yr,$score) = split(':', $math{$grade}{$oid} );
		print qq{<td>$score</td>};
	    }
	    print qq{</tr></table>\n};
	}
	# end of math

	
	# Discipline
	print qq{<h3>Discipline</h3>\n};
	
	my (%disc,%years); # disc{infraction}{year} = count
	my $sth1 = $dbh->prepare("select date, infraction from disc_event where id = ?");
	
	my $sth = $dbh->prepare("select eventid from disc_ident where studnum = ?");
	$sth->execute($studnum);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $eventid = $sth->fetchrow ) {

	    $sth1->execute($eventid);
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my ($date,$infraction) = $sth1->fetchrow;
	    my ($y,$m,$d) = split('-',$date);
	    my $syear = $y; # school year = year
	    if ( $m > 7 ) { $syear++; } # school year is the next year IF in fall.
	    
	    $years{$syear} = 1;
	    $disc{$infraction}{$syear}++;
	}

	#foreach my $inf ( keys %disc ) {
	#    foreach my $yr ( keys %{ $disc{$inf}} ) {
	#	print "INF:$inf YR:$yr VAL: $disc{$inf}{$yr}<br>\n";
	#    }
	#}

	
	my @years = sort {$a <=> $b} keys %years;

	# start table 
	print qq{<table cellspacing="0" cellpadding="3" border="1" style="margin:0.5em;">\n};
	if ( @years ) { # we have some data
	    print qq{<tr><th>Years</th>};
	    foreach my $yr ( @years ) {
		print qq{<th>$yr</th>};
	    }
	    
	    print qq{</tr>\n<tr>};
	    foreach my $inf ( sort keys %disc ) {
		print qq{<tr><td>$inf</td>};
		foreach my $yr ( @years ) {
		    print qq{<td>$disc{$inf}{$yr}</td>};
		}
		print qq{</tr>\n};
	    }
	} else { # no data
	    print qq{<tr><th>No Records Found</th></tr>\n};
	}
	
	# Close table
	print qq{</table>\n};
	
	# end of page
	print qq{<hr style="margin-left:0;width:100ch;height:4px;margin-top:1em;};
	print qq{background-color:black;" />\n};
	
	print qq{<div style="page-break-after:always;"></div>\n};
	
    } # end of student loop

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

    exit;

}
