#!/usr/bin/perl
#  Copyright 2001-2019 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',
	   'Reading Level' => 'Reading Level',
	   'Category' => 'Category',
	   'Name' => 'Name',
	   'Date' => 'Date',
	   'Author' => 'Author',
	   'Score' => 'Score',
	   'No Records Found' => 'No Records Found',
	   'Continue' => 'Continue',
	   'Select' => 'Select',
	   'Student' => 'Student',
	   'Chk' => 'Chk',
	   'Homeroom' => 'Homeroom',
	   'Grade' => 'Grade',
	   'Edit' => 'Edit',
	   'Tests' => 'Tests',
	   'Delete' => 'Delete',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Equivalent' => 'Equivalent',
	   'Next Page' => 'Next Page',
	   'Report' => 'Report',
	   'Progress' => 'Progress',
	   'Starting Season' => 'Starting Season',
	   'Ending Season' => 'Ending Season',
	   'Age' => 'Age',
	   'Level' => 'Level',
	   'Test' => 'Test',
	   
    );


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 Cwd;
use Number::Format qw(:all);

my $self = 'readRptProgress2.pl';

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

my %feeder;  # %feeder{schooldb}{studnum} = ref;
if ( @g_FeederSchools ) { # open links to those DRA records and suck in, converting them to provnum values.

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

    foreach my $schooldb ( @g_FeederSchools ) {
	# open a connection
	my $dsn1 = "DBI:$dbtype:dbname=$dbase";
	my $dbh1 = DBI->connect($dsn1,$guser,$gpassword);

	# read all reading tests in along with student info.
	my $sth = $dbh1->prepare("select s.lastname, s.firstname, s.grade, s.birthdate, s.provnum, r.* 
           from studentall s, read_test r where s.studnum = r.studnum");
	my $sth1 = $dbh1->prepare("select sum(score) from read_test_score where testid = ?");


	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my $id = $ref->{id};
	    $sth1->execute( $id );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $totalscore = $sth1->fetchrow;

	    if ( $totalscore ) {
		$ref->{score} = $totalscore;
		$feeder{$schooldb}{$id} = $ref;
	    }
	}
    }
}



# Get current dir so know what CSS to display and shift settings.
if ( getcwd() !~ /tcgi/ ) { # we are in cgi
    $tchcss = $css;
    $tchpage = $homepage;
    $tchdownloaddir = $downloaddir;
    $tchwebdownloaddir = $webdownloaddir;
}


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{Progress} $lex{Report} 2};

print qq{$doctype\n<html><head><title>$title</title>\n}; 
print qq{<link rel="stylesheet" href="$tchcss" type="text/css">\n};

print qq{$chartype\n</head><body style="padding:1em 2em;">\n};

print qq{[ <a href="$tchpage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\n};

=head
# show feeder Data
print qq{<p>Feeder</p>\n};
foreach my $db ( keys %feeder ) {
    foreach my $id ( sort keys %{ $feeder{$db} } ) {
	print "<br><br>K:$id V:$feeder{$db}{$id}<br>\n";
	foreach my $key ( sort keys %{ $feeder{$db}{$id} } ) {
	    print "K:$key V:$feeder{$db}{$id}{$key} ";
	}
    }
    print "<br><br>\n\n";
}
=cut


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

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

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

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

    my @tim = localtime(time);
    my $year = $tim[5] + 1900;
    my $month = $tim[4] + 1;
    my $currdate = "$year-$month-$tim[3]";


    # Find by Student Group
    my (@homerooms, @grades, @seasons );
    my $sth = $dbh->prepare("select distinct homeroom from student");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $hr = $sth->fetchrow ) {
	push @homerooms, $hr;
    }

    # Grades
    $sth = $dbh->prepare("select distinct grade from student");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    @grades = sort {$a <=> $b} @grades;


    # 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'. 

=head    
    print qq{Local Seasons<br>};
    foreach my $s ( sort keys %seasons ) {
	print qq{Season:$s<br>\n};
    }
    print qq{<br>\n};
=cut


    if ( @g_FeederSchools ) { # find the seasons available there
	my %fs;  # feeder seasons
	foreach my $db ( keys %feeder ) {
	    foreach my $id ( sort keys %{ $feeder{$db} } ) {
		$fs{ $feeder{$db}{$id}{season} } = 1;
	    }
	}

	foreach my $season ( keys %fs ) {
	    my ($y,$s) = split('-',$season);
	    if ( $s eq 'Spring' ) { $season = "$y-AAA"; }
	    $seasons{$season} = 1; # update the seasons hash.
	}

	#print "Feeder Seasons<br>";
	#foreach my $s ( sort keys %fs ) {
	#    print "Season:$s<br>\n";
	#}

    } # end of feeder schools



    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" 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><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><select name="homeroom"><option></option>\n};
    foreach my $hr ( @homerooms ) {
	$sth->execute;
	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 "</select></td></tr>\n";

    print qq{<tr><td colspan="2"><hr></td></tr>\n};

    # Select Starting Season
    print qq{<tr><td class="bra">$lex{'Starting Season'}</td>\n};
    print qq{<td><select name="startseason"><option></option>\n};
    foreach my $season ( @seasons ) {
	$season =~ s/ZZZ/Fall/;
	print qq{<option>$season</option>\n};
    }
    print "</select></td></tr>\n";


    # Select Ending Season
    print qq{<tr><td class="bra">$lex{'Ending Season'}</td>\n};
    print qq{<td><select name="endseason"><option></option>\n};
    foreach my $season ( @seasons ) {
	$season =~ s/ZZZ/Fall/;
	print qq{<option>$season</option>\n};
    }
    print "</select></td></tr>\n";


    # check next page?
    print qq{<tr><td class="bra">$lex{Chk} $lex{'Next Page'}</td>\n};
    print qq{<td><input type="checkbox" name="chk" value="1" checked="checked">\n};
    print qq{</td></tr>\n};


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

    print "</body></html>\n";

    exit;

} # end of showStartPage


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

    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    # passed: startseason, endseason, all student numbers;

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

    
    # Spring = 1, Summer = 2, Fall = 3
    my %seasonText2Num = ('Spring' => 1, 'Summer' => 2, 'Fall' => 3 );
    my %seasonNum2Text = reverse %seasonText2Num;
    
    my ($sy,$ss) = split('-', $arr{startseason});
    my ($ey,$es) = split('-', $arr{endseason} );

    # print qq{Start Date:$startdate End Date:$enddate<br>\n};
 
    my $first = 1;
    my (@seasons, %seasons, %seasonsRev);
    foreach my $y ( $sy..$ey ) {
	foreach my $s ( 1..3 ) {
	    
	    if ( $first and  $seasonNum2Text{$s} ne $ss ) {
		next ; # skip until we get a start;
	    } else { $first = 0; }; 

	    push @seasons, "$y-$s";
	    $seasons{"$y-$s"} = "$y-$seasonNum2Text{$s}";

	    if ( $y == $ey and $seasonNum2Text{$s} eq $es ) {
		last;
	    }
	}
    }

    if ( not @seasons ) {
	print qq{<h3>No Seasons Found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }


=head    
    foreach my $season ( @seasons ) {
	print qq{Season:$season<br>\n};
	print qq{Text:$seasons{$season}<br>\n};
    }
=cut
    
    delete $arr{startseason};
    delete $arr{endseason};


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

    my ( %studname, %sort, %provnum);
    foreach my $studnum  ( keys %arr ) {
	# Get Student Info
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname,$grade, $provnum ) = $sth->fetchrow;
	$studname{$studnum} = "<b>$lastname</b>, $firstname";
	$sort{"$grade$lastname$firstname$studnum"} = $studnum;
	$provnum{$studnum} = $provnum;
    }

    if ( not %sort ) {
	print qq{<h3>No Students Found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    my $sth = $dbh->prepare("select * from read_test where studnum = ? and season = ?  order by tdate desc, id");
    my $sth1 = $dbh->prepare("select sum(score) from read_test_score where testid = ?");

    my %data;

    # loop through students and get scores for seasons.
    foreach my $key ( keys %sort ) {
	my $studnum = $sort{$key};
	my $provnum = $provnum{$studnum};

	foreach my $season ( @seasons ) {

	    $season =~ s/-1/-Spring/;
	    $season =~ s/-2/-Summer/;
	    $season =~ s/-3/-Fall/;
	    $sth->execute( $studnum, $season );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $ref = $sth->fetchrow_hashref;
	    my %r = %$ref;
	    
	    # Get Score;
	    $sth1->execute($r{id} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $score = $sth1->fetchrow;
	    if ( $score ) {
		my $egr = scoreToGrade($score, $r{readlevel});
		$data{$studnum}{$season} = $egr;
	    }
	}

    } # end of student number loop

    my $sth1 = $dbh->prepare("select * from ssp_exceptions where ssptype = 'dra' and studnum = ? and
       to_days(tdate) >= to_days(?) and to_days(tdate) <= to_days(?)");


    # Display Table of Equiv Grades.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="padding:0.5em;border:1px solid gray;">\n};
    print qq{<caption style="font-size:140%;font-weight:bold;">Equivalent Grade</caption>\n};
    print qq{<tr><th>Student</th>\n};
    foreach my $season ( @seasons ) {
	my ($y,$s ) = split('-', $season);
	print qq{<th>$y<br>$s</th>};
    }
    print qq{</tr>\n};
    print qq{<tr><td colspan="6"><span style="color:red;">M</span> = Missing Entry; };
    print qq{<span style="color:blue;">E</span> = Missing Entry, Exception reason added</td></tr>\n};
    
    my $count;
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};

	print qq{<tr><td class="ra">$studname{$studnum}</td>};
	my $first = 1; # first value;
	my ($curregr, $prevegr); 
	foreach my $season ( @seasons ) {

	    my ($startdate, $enddate);
	    my ($sy,$ss) = split('-', $season );
	    if ( $ss eq 'Fall' ) {
		$startdate = "$sy-08-01";
		my $y = $sy + 1;
		$enddate = "$y-02-01";
	    } else {  # Spring
		$startdate = "$sy-02-01";
		$enddate = "$sy-08-01";
	    }

	    my $egr = $data{$studnum}{$season};
	    my $diff;
	    if ( not $egr ) {
		#  check for exception
		$sth1->execute($studnum, $startdate, $enddate);
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $eref = $sth1->fetchrow_hashref;
		%e = %$eref;

		if ( not $eref ) {
		    print  qq{<td class="cn" style="color:red;" title="Missing Entry; No Exception">M</td>};
		} else { # display the exception
		    print qq{<td class="cn" style="color:blue;" title="$tdate Exception:$eref->{reasoncode}">E</td>};
		}
		
	    } else { # set diff
		$prevegr = $curregr;
		$curregr = $egr;
		if ( $prevegr and $curregr ) { $diff = round($curregr - $prevegr, 1); }

		print qq{<td class="cn" title="Difference:$diff">$data{$studnum}{$season}</td>\n};
	    }
	}
	print qq{</tr>\n\n};
	$count++;

	if ($count % 15 == 0 ) { # display header again.
	    print qq{<tr><th>Student</th>\n};
	    foreach my $season ( @seasons ) {
		my ($y,$s ) = split('-', $season);
		print qq{<th>$y<br>$s</th>};
	    }
	    print qq{</tr>\n};
	}

    }

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

} # end of showReport



#-----------
sub showGraph {
#-----------


    my ($studref, $seasonref ) = @_;
    my @students = @$studref;
    my @seasons = @$seasonref;

    use GD::Graph::bars;
    use GD::Graph::colour qw(:colours);


    my $graph = GD::Graph::bars->new;

    $graph->set( dclrs => [ qw{blue green red dblue dgreen dred} ]);    


    my @data = ( \@category, \@scores );

    $biggest++;
    $count++;
    $graph->set(
		x_label => 'Category Index',
		y_label => 'Average Score',
		title  => $lex{Level}. " $readlevel - ". $lex{Test}. " $tdate ". $lex{Age}. " $age",
		y_max_value => $biggest,
		x_max_value => $count,
		) or die $graph->error;


    my $gd = $graph->plot(\@data) or die $graph->error;


    $filename = "$id-$$.png";
    open(IMG,">$filename");
    binmode IMG;
    print IMG $gd->png;
    close IMG;

    system("mv $filename $tchdownloaddir");

    print qq{<div style=\"float:left;border:1px solid blue;padding:0.4em;};
    print qq{background-color:#DDC;margin:0.2em;\">\n};
    print qq{<img src=\"$tchwebdownloaddir/$filename\">\n};

    print "<ul>";
    foreach my $key ( sort keys %cat ) {
	print "<li>$key : $cat{$key}</li>\n";
    }
    print qq{</ul></div>\n};


} # end of showGraph



#----------------
sub selectStudents {
#----------------

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

    my $chk;
    if ( $arr{chk} ) {
	$chk = 'CHECKED';
    }
    delete $arr{chk};


    my ($select, $value);
    if ( $arr{grade} ) {
	$select = 'where grade = ?';
	$value = $arr{grade};
    } elsif ( $arr{homeroom} ) {
	$select = 'where homeroom = ?';
	$value = $arr{homeroom};
    }

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

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

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{Tests}</th></tr>\n};
    print qq{<tr><td class="la" colspan="2"><input type="submit" value="$lex{Continue}"></td></tr>};

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

    while ( my ( $lastname, $firstname, $studnum ) = $sth->fetchrow ) {
	
	# Count - All Tests
	$sth1->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;
	my $scheck = $chk if $count;

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

    }

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

    exit;

} # end of selectStudents

