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

#  This file is part of Open Admin for Schools.

# Read Report - reference script that will read student data into data
# structure and then report by student, grade, or school. There can
# then be a variety of reports from this.


my %lex = ('Error' => 'Error',
	   'Main' => 'Main',
	   'Grade' => 'Grade',
	   'Blank=All' => 'Blank=All',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Split' => 'Split',
	   'Date' => 'Date',
	   'Continue' => 'Continue',
	   'Mean' => 'Mean',
	   'Median' => 'Median',
	   'Range' => 'Range',
	   'StdDev' => 'StdDev',
	   'Previous' => 'Previous',
	   'Current' => 'Current',
	   'School' => 'School',
	   'Database' => 'Database',
	   'Reading' => 'Reading',
	   'Report' => 'Report',
	   'Literacy Intervention' => 'Literacy Intervention',

	   );


my $self = 'lliRpt1.pl';

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

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


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



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.


my $q = CGI->new;
my %arr = $q->Vars;
print $q->header( -charset, $charset );


# Script Heading Descriptor
my $title = "LLI $lex{Report} 1";

# Print Page Heading

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\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{$chartype\n</head><body style="padding:0.4em 2em;">\n};
print qq{[ <a class="alt" href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\n};

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

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


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

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

    # 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">};
    print qq{<button type="reset" id="start_trigger">...</button>\n};
    print qq{</td></tr>\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">};
    print qq{<button type="reset" id="end_trigger">...</button></td></tr>\n};

    # Show Details.
    print qq{<tr><td class="bra">Show Details</td>};
    print qq{<td class="la"><input type="checkbox" name="showdetail" value="1"></td></tr>\n};

    print qq{<tr><td></td><td class="la"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</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 assembleData {
#---------------

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

    my $startdate = $arr{startdate};
    my $enddate = $arr{enddate};
    print qq{<div style="font-size:130%;font-weight:bold;">};
    print qq{Start Date:$startdate End Date:$enddate</div>\n};


    my %datacount; # grade -> studcount, 
    my %datagain; # grade ->  year gain in reading level;
    my %datahours; # grade -> 
    my %datamin;
    my %datamax; 


    my $grandtotalstudents;

    @dbase = sort keys %alldbase; # global records.

    foreach my $database ( @dbase ) {

	my %localcount; # grade -> studcount, 
	my %localgain; # grade ->  year gain in reading level;
	my %localhours; # grade -> 
	my %localmin;
	my %localmax; 

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

	my $schoolname = $alldbase{$database};
	print qq{<h1>$schoolname</h1>\n};


	# Get Student Record
	$sth1 = $dbh->prepare("select * from lint_student where progid = ?");

	# Count student records
	$sth4 = $dbh->prepare("select count(*) from lint_student where progid = ?");

	# Get the test scores
	my $sth3 = $dbh->prepare("select sum(score), count(*) from read_test_score 
         where testid = ?");

	# Get the student name
	my $sth6 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");


	# id, programhours, groupdesc, startdate from lint_program 
	$sth = $dbh->prepare("select * from lint_program 
          where to_days(startdate) >= to_days('$startdate') and 
          to_days(enddate) <= to_days('$enddate') order by startdate, enddate");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	my $first = 1;
	my $totalstudents;
	my @errors;

	# Program Loop
	while ( my $pref = $sth->fetchrow_hashref ) {
	    my %pr = %$pref;

	    if ( $first ) {
		print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
		print qq{<tr><th>Description</th><th>Start Date</th><th>Students</th></tr>\n};
		$first = 0;
	    }

	    if ( $arr{showdetail} ) {
		print qq{<div style="color:blue;font-size:100%;padding:0.1em 0;">};
		print qq{$pr{groupdesc} - Start Date:$pr{startdate} ID:$pr{id}</div>\n};
	    }

	    my $studcount; # local to this program.
	    
	    
	    # Student Count
#	    $sth4->execute( $pr{id} );
#	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
#	    my $studcount = $sth4->fetchrow;
#	    $totalstudents += $studcount;

#	    print qq{<tr><td>$pr{groupdesc} ($pr{id})</td><td>$pr{startdate}</td><td>$studcount</td></tr>\n};

	    my $prstartdate = $pr{startdate};


	    # Student Loop
	    $sth1->execute( $pr{id} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }


	    # Loop through all students
	    while ( my $ref = $sth1->fetchrow_hashref ) {

                my %r = %$ref;
    
#		print qq{Student:}, %r, qq{<br>\n};

		if ( $r{dropped} ) { 
		    # Get Student Name
		    $sth6->execute( $r{studnum} );
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my ($lastname, $firstname) = $sth6->fetchrow;
		    push @errors,  qq{<div style="color:green;font-weight:bold;">}.
		      qq{Dropped:$firstname $lastname ($r{studnum})</div>\n};
		    if ( $arr{showdetails} ) {
			print qq{<div style="color:green;font-weight:bold;">};
			print qq{Dropped: $firstname $lastname ($r{studnum})</div>\n};
		    }

		    next; # student
		}


		my $sth2 = $dbh->prepare("select * from read_test where studnum = ? and
                 to_days( tdate ) <= to_days('$prstartdate') order by tdate desc ");

		$sth2->execute( $r{studnum} );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $pretest_ref = $sth2->fetchrow_hashref;

		if ( not $pretest_ref ) { # move the date to look at first test WITHIN date range
		    my $sth5 = $dbh->prepare("select * from read_test where studnum = ? and
                   to_days( tdate ) > to_days('$prstartdate') and  
                   to_days( tdate ) <= to_days('$pr{enddate}')
                   order by tdate "); # first one within this date range.
		    $sth5->execute( $sr{studnum} );
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    $pretest_ref = $sth5->fetchrow_hashref;
		}

		if ( not $pretest_ref->{id} ) {
		    # Get Student Name
		    $sth6->execute( $r{studnum} );
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my ($lastname, $firstname) = $sth6->fetchrow;
		    push @errors, qq{<div style="color:purple;font-weight:bold;">}.
			qq{No Pretest: $firstname $lastname ($r{studnum})</div>\n};

		    if ( $arr{showdetails} ) {
			print qq{<div style="color:purple;font-weight:bold;">};
			print qq{No Pretest: $firstname $lastname ($r{studnum})</div>\n}; 
		    }

		    next; # student
		}

		my %pretest = %$pretest_ref;

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

		# Get the pretest Scores
		$sth3->execute( $pretest{id} );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my ( $prescoretotal, $prescorecount ) = $sth3->fetchrow;
		my $prepossibletotal = $prescorecount * 4;

		my $equivgrade = scoreToGrade( $prescoretotal, $pretest{readlevel} );

		
		# Find the Posttest.
		$sth2 = $dbh->prepare("select * from read_test where studnum = ? and
                  to_days( tdate ) >= to_days( '$pr{enddate}' ) ");
		$sth2->execute( $r{studnum} );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $posttest_ref = $sth2->fetchrow_hashref;

		if ( not $posttest_ref ) { # move the date to look at first test WITHIN date range
		    my $sth5 = $dbh->prepare("select * from read_test where studnum = ? and
                      to_days( tdate ) <= to_days( '$pr{enddate}' ) and 
                      to_days( tdate ) >= to_days( '$pr{startdate}' )
                      order by tdate desc");
		    $sth5->execute( $r{studnum} );
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    $posttest_ref = $sth5->fetchrow_hashref;

		    if ( $posttest_ref->{id} == $pretest_ref->{id} ) { # same test; bad!
			$posttest_ref = undef;
		    }
		}

		if ( not $posttest_ref->{id} ) { 
		    # Get Student Name
		    $sth6->execute( $r{studnum} );
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my ($lastname, $firstname) = $sth6->fetchrow;
		    my $err = qq{<div style="color:purple;font-weight:bold;">}.
			qq{No Post Test: $firstname $lastname ($r{studnum})</div>\n}; 
		    push @errors, $err; 
		    next; # student
		}

		my %posttest = %$posttest_ref;

#		print qq{PostTEST:<br>", %posttest, "<br>\n}; 

		# Get the posttest Scores
		$sth3->execute( $posttest{id} );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my ( $postscoretotal, $postscorecount ) = $sth3->fetchrow;
		my $postpossibletotal = $postscorecount * 4;

		my $postequivgrade = scoreToGrade( $postscoretotal, $posttest{readlevel} );
		if ( not $postequivgrade or not $equivgrade ) { 
		    print qq{Missing Data - $r{studnum}<br>}; 
		    next;
		}

		my $delta = $postequivgrade - $equivgrade;
		my $grade = $posttest{tgrade};

#		print qq{Student:$r{studnum}  Initial:$equivgrade Final:$postequivgrade  Delta:$delta };
#		print qq{Grade:$posttest{tgrade} <br>\n}; #Hours:$programhours<br>\n};

		my $programhours = $pr{programhours};
#		print qq{Hours:Program: $programhours };
		if ( $r{programhours} ) {
		    $programhours = $r{programhours};
		}
#		print qq{- Actual:$programhours<br>\n};

		if ( not $grade) { 
		    print qq{<p>Missing Grade for student:$studnum for postLLI test</p>\n};
		    next; # student
		}

		$datacount{$grade} += 1;
		$datagain{$grade} += $delta;
		$datahours{$grade} += $programhours;

		if ( not $datamin{$grade} ) { $datamin{$grade} = $delta; }
		if ( $delta < $datamin{$grade} ) { 
		    $datamin{$grade} = $delta; 
		}

		if ( $delta > $datamax{$grade} ) { 
		    $datamax{$grade} = $delta; 
		}

		$studcount++;   # we only increment if we get to bottom of the loop.


		# Local School Data
		$localcount{$grade} += 1;
		$localgain{$grade} += $delta;
		$localhours{$grade} += $programhours;

		if ( not exists $localmin{$grade} ) { $localmin{$grade} = $delta; }
		if ( $delta < $localmin{$grade} ) {
		    $localmin{$grade} = $delta; 
		}

		if ( $delta > $localmax{$grade} ) { 
		    $localmax{$grade} = $delta; 
		}

		
	    } # end of students loop


	    # Total Students at this school in the programs.
	    $totalstudents += $studcount;

	    print qq{<tr><td>$pr{groupdesc} (ID$pr{id})</td><td>$pr{startdate}</td>};
	    print qq{<td class="cn">$studcount</td></tr>\n};


	    
	} # end of programs loop

	
	if ( $first ) { 
	    print qq{<div>No Programs for this school found.</div>\n};
	    next; # school
	    
	} else {
	    
	    $grandtotalstudents += $totalstudents;
	    print qq{<tr><td colspan="2" class="bra">Student Count</td><td class ="bcn">$totalstudents</td></tr>\n};
	    print qq{</table>\n};
	    if ( @errors ) { # print them out.
		foreach my $error ( @errors ) {
		    print $error, "\n";
		}
	    }

	}

#	foreach my $gr ( sort keys %datacount ) {
#	    print qq{Grade:$gr Count:$datacount{$gr} Gain:$datagain{$gr} Hours:$datahours{$gr}<br>\n};
#	}
#	print qq{<br>\n};


	print qq{<h3>$schoolname Summary Results</h3>\n};
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<tr><th>Grade</th><th>Number of<br>Students</th><th>Min Gain</th>\n};
	print qq{<th>Avg. Gain<br>(years)</th><th>Max Gain</th><th>Avg. Instr.<br>Hours</th></tr>\n};

	foreach my $grade ( sort {$a <=> $b} keys %localcount ) {
	    
	    print qq{<tr><td class="bcn">$grade</td><td class="bcn">$localcount{$grade}</td>};

	    my $localminYM =  yearToYearMonth( format_number( $localmin{$grade}, 2, 2 ));
	    my $gainYM =  yearToYearMonth( format_number( $localgain{$grade} / $localcount{$grade}, 2, 2 ));
	    my $localmaxYM =  yearToYearMonth( format_number( $localmax{$grade}, 2, 2 ));

	    print qq{<td class="bcn">$localminYM</td>};
	    print qq{<td class="bcn">$gainYM</td>};
	    print qq{<td class="bcn">$localmaxYM</td>};
	    
	    print qq{<td class="bcn">}. format_number( $localhours{$grade} / $localcount{$grade}, 2, 2 );
	    print qq{</td></tr>\n};
	    
	}

	my ($totcount, $totgain, $tothours);
	foreach my $grade ( keys %datacount ) {
	    $totcount += $localcount{$grade};
	    $totgain += $localgain{$grade};
	    $tothours += $localhours{$grade};
	}
	print qq{<tr style="background-color:#CCF;">};
	print qq{<td class="bcn">Summary</td><td class="bcn">$totcount</td><td></td>};
	if ($totcount ) {
	    my $avggainYM = yearToYearMonth( format_number( $totgain / $totcount, 2,2 ));
	    print qq{<td class="bcn">$avggainYM</td><td></td>\n};
	    
	    print qq{<td class="bcn">}. format_number( $tothours / $totcount, 2,2 ). qq{</td>\n};
	}
	print qq{</tr>\n};    
	print qq{</table>\n};
	

    } # end of school loop

    
=head
    my $count;
    foreach my $key ( sort keys %datagain ) {
	print qq{K:$key V:$datagain{$key} $datacount{$key}<br>\n};
	$count += $datacount{$key};
    }
    print qq{Count:$count<br>\n};
=cut


    print qq{<h1>Overall Results</h1>\n};
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>Grade</th><th>Number of<br>Students</th><th>Min Gain</th>\n};
    print qq{<th>Avg. Gain<br>(years)</th><th>Max Gain</th><th>Avg. Instr.<br>Hours</th></tr>\n};

    foreach my $grade ( sort {$a <=> $b} keys %datacount ) {
	print qq{<tr><td class="bcn">$grade</td><td class="bcn">$datacount{$grade}</td>};

	my $dataminYM =  yearToYearMonth( format_number( $datamin{$grade}, 2, 2 ));
	my $datagainYM;
	if ( $datacount{$grade} ) {
	    $datagainYM = yearToYearMonth( format_number( $datagain{$grade} / $datacount{$grade}, 2, 2 ));
	}
	my $datamaxYM =  yearToYearMonth( format_number( $datamax{$grade}, 2, 2 ));

	
	# print qq{<td class="bcn">}. format_number( $datamin{$grade}, 2, 2 ). qq{</td>\n};
	print qq{<td class="bcn">$dataminYM</td>\n};

	# my $datagain;
	# if ( $datacount{$grade} ) {
	#    $datagain = format_number( $datagain{$grade} / $datacount{$grade}, 2, 2 );
	#}
	# print qq{<td class="bcn">$datagain</td>\n};
	print qq{<td class="bcn">$datagainYM</td>\n};
	
	# print qq{<td class="bcn">}. format_number( $datamax{$grade}, 2, 2 ). qq{</td>\n};
	print qq{<td class="bcn">$datamaxYM</td>\n};

	
	my $datahr;
	if ( $datacount{$grade} ) {
	    $datahr = format_number( $datahours{$grade} / $datacount{$grade}, 2, 2 );
	}
	print qq{<td class="bcn">$datahr</td>\n};
	print qq{</tr>\n};
    }

    my ($totcount, $totgain, $tothours);
    foreach my $grade ( keys %datacount ) {
	$totcount += $datacount{$grade};
	$totgain += $datagain{$grade};
	$tothours += $datahours{$grade};
    }
    print qq{<tr style="background-color:#CCF;"><td class="bcn">Summary</td><td class="bcn">$totcount</td><td></td>};
    if ($totcount ) {
	print qq{<td class="bcn">}. yearToYearMonth(format_number( $totgain / $totcount, 2,2 ));
	print qq{</td><td></td>\n};
	print qq{<td class="bcn">}. format_number( $tothours / $totcount, 2,2 ). qq{</td>\n};
    }
    print qq{</tr>\n};    


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


    exit;

}
