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

	   'No Students Found' => 'No Students Found',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'Search' => 'Search',
	   'Tests' => 'Tests',
	   'Attendance' => 'Attendance',
	   'View' => 'View',
	   'Continue' => 'Continue',
	   
	   );

my $self = 'cmaRpt12.pl';

use DBI;
use CGI;
use Cwd;

my %colormap = ( 1 => 'r',
		 2 => 'y',
		 3 => 'b',
		 4 => 'g'
    );


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

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

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

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


# Get current dir so know what CSS to display;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $css = $tchcss;
    $homepage = $tchpage;
}


my $title = "$lex{'Common Math Assessment'} $lex{Report} 12 - $schoolname";
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{<div>[ <a href="$homepage">$lex{Main}</a> ]</div>\n};

print qq{<h1>$title</h1>\n};
print qq{<p style="font-weight:bold;font-size:110%;">};
print qq{Show all math records (all years) per student</p>\n};

print qq{<style>\n};
print qq{td.r { background-color:#822;color:white;font-size:120%;font-weight:bold;text-align:center;}\n};
print qq{td.y { background-color:#BB1;color:white;font-size:120%;font-weight:bold;text-align:center;}\n};
print qq{td.b { background-color:#228;color:white;font-size:120%;font-weight:bold;text-align:center;}\n};
print qq{td.g { background-color:#282;color:white;font-size:120%;font-weight:bold;text-align:center;}\n};
print qq{</style>\n};



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

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


#-------------
sub showReport { # by grade / homeroom / student
#-------------

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

    # sorting of students by name
    my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");    
    my %sort;
    foreach my $studnum ( keys %arr ) {
	$sth->execute( $studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($lastname, $firstname)  = $sth->fetchrow ) {
	    $sort{"$lastname$firstname$studnum"} = $studnum;
	}
    }

    
    # Load outcomes
    my (%outcomes,%revoutcomes);
    my $sth = $dbh->prepare("select * from mathca_outcomes order by oid");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	$outcomes{ "$r{oid}:$r{odesc}" } =  $r{grade};
	$revoutcomes{ $r{grade} }{$r{oid}} = $r{odesc};
    }

#    foreach my $key ( sort  keys %outcomes ) {
#	my ($id,$desc) = split(':', $key);
#	print qq{$id - $desc - $outcomes{$key}<br>\n};
#    }

    # get all student records.
    my $sth = $dbh->prepare("select * from mathca_scores where studnum = ? order by schoolyear");
    # get student name
    my $sth1 = $dbh->prepare("select lastname, firstname, grade, homeroom from studentall 
			     where studnum = ?");

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

	# get student name
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname, $grade, $homeroom ) = $sth1->fetchrow;
	if ( not $lastname ) {
	    $lastname = qq{No Name Found};
	}

	my %data; # holds all data $data{schoolyear}{outcome}{prepost} = score;
	# get all this student's math records
	$sth->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	# get Student Math Records
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    $data{ "$r{schoolyear}:$r{tgrade}" }{ $r{outcome} } = "$r{score}:$r{prepost}";
	}

	# print the data structure as a table;
	print qq{<h3 style="margin-bottom:0;">$firstname $lastname</h3>\n};
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<caption style="font-size:80%;">};
	print qq{Hover on <span style="font-weight:bold;">Outcomes</span>};
	print qq{ to see Description; <span style="font-weight:bold;">Score</span>};
	print qq{ for Pre/Post Test</caption>\n};


	foreach my $key ( sort keys %data ) { # all records for this student;

	    my ($yr,$gr) = split(':', $key);
	    
	    # Outcomes for this grade level.
	    my (@outcomes, %desc);
	    foreach my $id ( sort keys %{$revoutcomes{$gr}} ) {
		my $desc = $revoutcomes{$gr}{$id};
		push @outcomes, $id;
		$desc{$id} = $desc; # lookup for this id.
#		print "ID:$id DESC:$desc<br>\n";
	    }


	    # print Header row
	    print qq{<tr><th>$yr Gr $gr</th>};
	    foreach my $id ( @outcomes ) {
		print qq{<th title="$desc{$id}">$id</th>};
	    }
	    print qq{</tr>\n\n};

	    # print scores row
	    print qq{<tr><td></td>};
	    foreach my $id ( @outcomes ) {
		my $val = $data{$key}{$id};
		if ( $val ) {
		    my ($score,$prepost) = split(':', $val);
		    my $class = $colormap{$score};
		    print qq{<td class="$class" title="$prepost">$score</td>};

		} else {
		    print qq{<td></td>};
		}
	    }
	    print qq{</tr>\n};
		
	}
	print qq{</table>\n};
	
    } # end of student loop
	

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

    exit;


} # end of showReport



#----------------
sub showStartPage { # Entry Values 
#----------------

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

    
    # SELECT by NAME, etc.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    # Go to show students matched function.

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

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

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

    

    print qq{\n<p>OR</p>\n\n};
    
    
    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 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};

    # 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 selectStudent {
#----------------

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

    if ( not $arr{grade} and not $arr{homeroom} and not $arr{student} ) {
	print qq{<h3>$lex{'No Selection'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    } 
    
    my $studenttable = 'student';
    if ( $arr{showwithdrawn} ) {
	$studenttable = 'studentall';
    }

    
    my ($student, $sth);
    if ( $arr{student} ) {
	$student = $arr{student};

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

	} else { # we have words hopefully 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, grade, homeroom
				     from $studenttable where lastname = ? and firstname = ?
				     order by lastname, firstname");
		$sth->execute( $lastname, $firstname );
		
	    } elsif ( $lastname and not $firstname ){ # only lastname (no comma)
		if (length($lastname) == 2){ # search by initials: fi, li.

		    $fi = substr($lastname,0,1); 
		    $li = substr($lastname,1,1);
		    $fi .= '%';
		    $li .= '%';
		    $sth = $dbh->prepare("select lastname,firstname, studnum, grade, homeroom
					 from $studenttable
					 where lastname $sql{like} ? and firstname $sql{like} ?
					 order by lastname, firstname");
		    $sth->execute( $li, $fi );
		} else {
		    $sth = $dbh->prepare("select lastname, firstname, studnum, grade,homeroom 
					 from $studenttable where lastname = ? 
					 order by lastname, firstname");
		    $sth->execute( $lastname );
		}
	    } # initials end
	} # Last Else; words with comma or length 2
	
	if ( $DBI::errstr ) { print $lex{Error}. ": $DBI::errstr"; die $DBI::errstr; }	

    } else { # homeroom or grade

	if ( $arr{grade} ) {
	    $sth = $dbh->prepare("select lastname, firstname, studnum, grade, homeroom 
				    from student where grade = ? order by lastname, firstname");
	    $sth->execute( $arr{grade} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    
	} elsif ( $arr{homeroom} ) {
	    $sth = $dbh->prepare("select lastname, firstname, studnum, grade, homeroom 
				    from student where homeroom = ? order by lastname, firstname");
	    $sth->execute( $arr{homeroom} );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}
    }


    my $first = 1;
    
    my $sth1 = $dbh->prepare("select count(*) from mathca_scores where studnum = ?");
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    
    # Loop through each student.
    while ( my ( $lastname, $firstname,$studnum, $grade, $homeroom ) = $sth->fetchrow ) {

	if ( $first ) {

	    print qq{<form action="$self" method="post">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    
	    print qq{<input type="submit" value="$lex{Continue}">\n};
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    if ( $arr{showwithdrawn} ) {
		print qq{<caption style="color:red;font-weight:bold;">};
		print qq{WD = Withdrawn Student</caption>\n};
	    }
	    print qq{<tr><th title="Select Student">Sel</th><th title="Student Name">$lex{Name}</th>};
	    print qq{<th title="Grade Homeroom">Gr Hr</th>};
	    #print qq{<th title="attendance">Att</th>};
	    print qq{<th title="Number of Tests">#$lex{Tests}</th></tr>\n};
	    $first = 0;
	}

	
	# Math Scores?
	$sth1->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $testcount = $sth1->fetchrow;

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

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

	# Tests
	print qq{<td class="cn">$testcount</td></tr>\n};

    }

    if ( $first ) { # no students
	print qq{<h3 style="color:red;">$lex{'No Students Found'}</h3>\n};
	showStartPage();
	
    } else { # we have students
	print qq{</table>\n};
	print qq{<input type="submit" value="$lex{Continue}">\n};
	print qq{</form>\n};

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

    exit;

} # end of selectStudent

