#! /usr/bin/perl
#  Copyright 2001-2018 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',
	   'Continue' => 'Continue',
	   'Grade' => 'Grade',
	   'Select by' => 'Select by',
	   'Error' => 'Error',
	   'Sort by' => 'Sort by',
	   'Name' => 'Name',
	   'Common Math Assessment' => 'Common Math Assessment',
	   'School Year' => 'School Year',
	   'Show Withdrawn' => 'Show Withdrawn',


	   );

my $self = 'feederMathView.pl';

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

my @strands = qw(P N SS SP);
my %strandnames = ('P' => 'Patterns and Relations', 
		   'N' => 'Numbers and Operations',
		   'SS' => 'Shape and Space',
		   'SP' => 'Stats and Prob'
    );

my $teachermode;
my $configpath = '../..';
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $teachermode = 1;
    $configpath = '..';
}


# only load passwords and users
eval require "$configpath/etc/admin.conf.root";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


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



# Load Configuration Variables;
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}


# Load global user and password
if ( not -e "$globdir/global.conf" ) {
    print qq{Cannot read the global.conf file!\n};
    print qq{</body></html>\n};
    exit;
}

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

if ( $oldbase{$dbase} ) { # We are an onion lake school
    %dbase = %oldbase;
}



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


my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time);
$year = $year + 1900;
$wday++; $mon++;
my $currdate = "$dow[$wday], $month[$mon] $mday, $year";


# Get current dir so know what CSS to display;
if ( $teachermode ) { # running on teacher site
    $css = $tchcss;
    $homepage = $tchpage;
    $downloaddir = $tchdownloaddir;
    $webdownloaddir = $tchwebdownloaddir;
}

my $title = "View $lex{'Common Math Assessment'}";
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 style="padding:1em 5em;">\n};

print qq{[ <a href="$homepage">$lex{Main}</a> \n};
if ( not $teachermode ) {
    print qq{| <a href="/ssp.html">SSP</a> \n};
}
print qq{]\n<h1>$title</h1>\n};


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

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

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


#---------------
sub selectGroup {
#---------------
    
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # passed db to view.

    print qq{<h3>Feeder School - $dbase{$arr{db}}</h3>\n};

    
    my $dbtype = 'mysql';
    my $dsn = "DBI:$dbtype:dbname=$arr{db}";
    my $dbh = DBI->connect($dsn,$guser,$gpassword);
    
    
    my (@grades, @schoolyears );

    # Get Grades
    $sth = $dbh->prepare("select distinct grade from studentall 
      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;

    
    # Get School Years
    $sth = $dbh->prepare("select distinct schoolyear from mathca_scores");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $yr = $sth->fetchrow ) {
	push @schoolyears, $yr;
    }
    @schoolyears = reverse sort @schoolyears;

    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="db" value="$arr{db}">\n};
    
    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};


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

    
    # School Year
    print qq{<tr><td class="bra">$lex{'School Year'}</td>};
    print qq{<td><select name="schoolyear">\n};
    foreach my $yr ( @schoolyears ) {
	print qq{<option>$yr</option>\n};
    }
    print qq{</select></td></tr>\n};

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


    # Show Students without Discpline Records
#    print qq{<tr><td class="bra">Skip Students without Records</td>};
#    print qq{<td><input type="checkbox" name="skipblanks" value="1" checked="checked"></td></tr>\n};

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

    exit;

}


#----------------
sub selectSchool { 
#----------------

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

    print qq{<h3>Select Feeder School</h3>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    
    foreach my $db ( @g_FeederSchools ) {
	print qq{<div><input type="radio" name="db" value="$db">$dbase{$db}</div>\n};
    }

    print qq{<p><input type="submit" value="Continue"></p>\n};
    print qq{</form>\n};
    
    print qq{</body></html>\n};
    
    exit;

}


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

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

    my $studtable = 'studentwd';
    if ( $arr{showcurrent} ) {
	$studtable = 'studentall';
    }
    
    my $dbtype = 'mysql';
    my $dsn = "DBI:$dbtype:dbname=$arr{db}";
    my $dbh = DBI->connect($dsn,$guser,$gpassword);


    my ($select, $selectval, @grades, $classname);
    $select = "where grade = ?";
    $selectval = $arr{grade};
    push @grades, $arr{grade};
    $classname = "Grade $arr{grade}";

    
    print qq{<h3>$dbase{$arr{db}} - $classname  &ndash; School Year $arr{schoolyear}</h3>\n};


    # Build outcomes data structure
    my %outcomes; # lists outcomes.
    my %odesc; # outcomes description.

    my $sth = $dbh->prepare("select * from mathca_outcomes");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	my $oid = $r{oid};
	my $grade = $r{grade};

	my ($cat, $seq)  = split(/\./, $oid);
	$cat =~ s/$grade$//; # remove grade

	$outcomes{$grade}{$cat}{$seq} = $oid;
	$odesc{$oid} = $r{odesc};

    } # Done building data structure.
    
    
    foreach my $grade ( @grades ) {
    
	# Now build a structure for this grade
	my %strandcount;  # $catcount{category} = number in this category.
	foreach my $cat ( sort keys %{ $outcomes{$grade} } ) {
	    foreach my $seq ( sort keys %{ $outcomes{$grade}{$cat} } ) {
		if ( $seq > $strandcount{$cat} ) { $strandcount{$cat} = $seq; }
	    }
	}


	# Now Select the students to display; skip any withdrawn kids w/o data.
	my $sth = $dbh->prepare("select lastname, firstname, studnum, grade from $studtable
           $select order by lastname, firstname");
	if ( $arr{homeroom} ) { # add on grade selector to only get part of the class.
	    $sth->execute( $selectval, $grade );
	} else {
	    $sth->execute( $selectval );
	}
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	
	
	my %withdrawn;
	my %data; # holds all data $data{studnum}{outcome}{prepost} = score;
	my @students;  # provides order of students
	my %pass; # $pass{studnum} = count of passes ( >= 3)
	my %strandpass; 

	my %names; # store student names;
	my %tdates; # store test dates for outcomes and pre/post  $tdates{outcome}{pretest/posttest};

	# Get Student Data
	my $sth1 = $dbh->prepare("select prepost, outcome, score, tdate from mathca_scores
         where schoolyear = ? and studnum = ? order by tdate");

	# Queries for Withdrawn and Having Data
	my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

	my $sth3 = $dbh->prepare("select count(*) from mathca_scores
          where schoolyear = ? and studnum = ?");



	# Loop through all students; build data structure.
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;

	    my $studnum = $r{studnum};

	    # Check for withdrawn and having any data this year.
	    if ( $arr{showwithdrawn} ) { # check if student is withdrawn
		# Withdrawn?
		$sth2->execute( $studnum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my $wdcount = $sth2->fetchrow;

		# Have Data?
		$sth3->execute($arr{schoolyear}, $studnum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my $datacount = $sth3->fetchrow;

		if ( $wdcount > 0 and $datacount < 1 ) { # withdrawn and no data
		    next;
		}
	    }


	    push @students, $studnum; 
	    $names{$r{studnum}} = "<b>$r{lastname}</b>, $r{firstname}";

	    # Get this student's data
	    $sth1->execute( $arr{schoolyear}, $studnum );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	    while ( my ($prepost, $outcome, $score, $tdate) = $sth1->fetchrow ) {
		# Store passes;
		if ( $prepost eq 'posttest' ) {
		    if ( $score >= 3 ) { 
			$pass{$studnum}{'pass'}++; 
			$strandpass{$studnum}{$outcome}{'pass'}++; 
		    }
		    $pass{$studnum}{'count'}++;
		    $strandpass{$studnum}{$outcome}{'count'}++;
		}

		$data{$studnum}{$outcome}{$prepost} = $score;
	    }
	} # end of student loop


	print qq{<div style="font-size:120%;font-weight:bold;margin-top:1em;">Grade $grade</div>\n};


	print qq{<table cellpadding="2" cellspacing="0" border="1">\n};
	print qq{<caption>Hover on Column Titles to see text descriptions</caption>\n};

	# header section.
	print qq{<tr><th>Student</th>};
	foreach my $strand ( @strands ) {
	    if ( $strandcount{$strand} ) { # print IF we have that strand
		print qq{<th colspan="4" };
		print qq{title="Number in brackets is number of outcomes in this strand for this grade">};
		print qq{$strandnames{$strand} ($strandcount{$strand})</th><th></th>\n};
	    }
	}

	print qq{<th colspan="5">Score Averages</th></tr>\n};


	# Second Line: Desc;
	print qq{<tr><td></td>};
	foreach my $strand ( @strands ) {
	    if ( $strandcount{$strand} ) { # print IF we have that strand
		print qq{<td title="Number of Outcomes Covered">Out</td>};
		print qq{<td title="Pretest Average Score">Pre<br>Avg</td>};
		print qq{<td title="Posttest Average Score">Post<br>Avg</td>\n};
		print qq{<td title="Number of Passes (3 or 4 score) over the numbers of tests in strand">};
		print qq{Pass</td><td></td>\n};
	    }
	}
	print qq{<td>Total<br>Outcomes</td><td>Pre</td><td>Post</td><td>Difference</td><td>Pass</td></tr>\n};

	# used for summary stats at the end;
	my (%strandtotalcount, %strandtotalsum );

	# Loop through each student, finding averages for each strand
	foreach my $studnum ( @students ) {

	    my ($pretotalcount, $pretotalsum, $posttotalcount, $posttotalsum);

	    print qq{<tr><td>$names{$studnum}</td>\n};
	    foreach my $strand ( @strands ) {

		if ( not $strandcount{$strand} ) {  next; } # skip any strands not covered in this grade.

		# Loop through all tests and find averages for this strand of outcomes.
		my (  $preavg, $presum, $precount, $postavg,$postsum, $postcount, $passsum, $passcount );

		foreach my $seq ( sort keys %{ $outcomes{$grade}{$strand} } ) {
		    my $outcome = $outcomes{$grade}{$strand}{$seq};
		    
		    $passsum += $strandpass{$studnum}{$outcome}{pass};
		    $passcount += $strandpass{$studnum}{$outcome}{count};

		    my $pretest = $data{$studnum}{$outcome}{'pretest'};
		    my $posttest = $data{$studnum}{$outcome}{'posttest'};



		    if ( $pretest and $posttest  or 
			 ( $grade eq '2' or $grade eq '1' or $grade eq 'K' or $grade eq 'PK')) {
			$presum += $pretest;
			$precount++;
			$postsum += $posttest;
			$postcount++;

		    } else {
			next;
		    }

		} # end of outcome

		if ( $precount ) {
		    $preavg = format_number( $presum / $precount, 2);
		    $pretotalcount += $precount;
		    $pretotalsum += $presum;
		}
		if ( $postcount ) {
		    $postavg = format_number( $postsum / $postcount, 2);
		    $posttotalcount += $postcount;
		    $posttotalsum += $postsum;
		}

		my $passString;
		if ( $passcount ) {
		    $passString = "$passsum / $passcount";
		}

		$strandtotalcount{$strand} += $postcount;
		$strandtotalsum{$strand} += $postsum;

		print qq{<td class="cn">$postcount</td><td class="cn">$preavg</td>};
		print qq{<td class="cn">$postavg</td><td>$passString</td><td></td>\n};

	    } # end of this strand


	    # now totals for this student
	    my ($pretotalavg, $posttotalavg);
	    if ( $pretotalcount ) {
		$pretotalavg = format_number( $pretotalsum / $pretotalcount, 2);
	    }

	    if ( $posttotalcount ) {
		$posttotalavg = format_number( $posttotalsum / $posttotalcount, 2);
	    }

	    my $diff = format_number( $posttotalavg - $pretotalavg, 2);

	    print qq{<td class="cn">$posttotalcount</td><td class="cn">$pretotalavg</td>};
	    print qq{<td class="cn">$posttotalavg</td><td class="cn">$diff</td>\n};

	    # Passes;
	    my $passpercent;
	    if ( $pass{$studnum}{'count'} and $pass{$studnum}{'pass'} ) {
		$passpercent = format_number( $pass{$studnum}{'pass'} * 100 / $pass{$studnum}{'count'}, 2);
	    }
	
	    if ( $passpercent ) {
		print qq{<td>$passpercent% ($pass{$studnum}{'pass'}/$pass{$studnum}{'count'})</td></tr>\n};
	    } elsif ( $pass{$studnum}{'count'} ) { # have count, but no passes
		print qq{<td>0% (0/$pass{$studnum}{'count'})</td></tr>\n};
	    } else { # print blank.
		print qq{<td></td></tr>\n};
	    }


	} # end of student loop

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


	# Now Summary of all students in the grade
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<caption style="font-weight:bold;font-size:120%;">Summary</caption>\n};

	# Heading
	print qq{<tr><th>Strand</th><th>Average Score</th></tr>\n};

	# Data Section
	foreach my $strand ( @strands ) {
	    my $avg = '0';
	    if ( $strandtotalcount{$strand} ) {
		$avg = format_number( $strandtotalsum{$strand} / $strandtotalcount{$strand}, 2);
	    }
	    print qq{<tr><td>$strandnames{$strand} ($strandcount{$strand})</td><td>$avg</td></tr>\n}; 
	}
	print qq{</table>\n};

	
    } # end of grade loop
	

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

    exit;

} # end of showReport



#----------------
sub showStartPage { # Entry Values for Custom Script
#----------------

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

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

    # Get School Years
    $sth = $dbh->prepare("select distinct schoolyear from mathca_scores");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $yr = $sth->fetchrow ) {
	push @schoolyears, $yr;
    }
    @schoolyears = reverse sort @schoolyears;


    # Start Form
    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">\n};


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

    # OR
    print qq{<tr><td colspan="2" style="text-align:center;">$lex{OR}</td></tr>\n};


    # Select Homeroom
    print qq{<tr><td class="bra">$lex{'Select by'} $lex{Homeroom}</td>};
    print qq{<td><select name="homeroom"><option></option>\n};
    foreach my $hr ( @homerooms ) {
	print qq{<option>$hr</option>\n};
    }
    print qq{</select></td></tr>\n};


    # School Year
    print qq{<tr><td class="bra">$lex{'School Year'}</td>};
    print qq{<td><select name="schoolyear">\n};
    foreach my $yr ( @schoolyears ) {
	print qq{<option>$yr</option>\n};
    }
    print qq{</select></td></tr>\n};

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


=head
    # Sort Order
    print "<tr><td class=\"ra\">$lex{'Sort by'}</td>";
    print "<td><select name=\"sortorder\">";
    print "<option value=\"grade\">$lex{Grade}</option>";
    print "<option value=\"homeroom\">$lex{Homeroom}</option>";
    print "<option value=\"name\">$lex{Name}</option>";
    print "</select></td>";
    print "<td></td></tr>\n";
=cut


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

    exit;

}

