#!/usr/bin/perl
#  Copyright 2004-2014 Leslie Richardson

#  This file is part of Open Admin for Schools.


my %lex = ('Error' => 'Error',
	   'Main' => 'Main',
	   'View' => 'View',
	   'Select' => 'Select',
	   'Schools' => 'Schools',
	   'Continue' => 'Continue',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Details' => 'Details',
	   'Check All' => 'Check All',

    );

my %programs = ('DRA' => 'read_test',
		'PPVT' => 'ppvt_test',
		'FSIM' => 'math_fstep',
		'CMA' => 'mathca_scores',
    );

my $self = 'sspview.pl';


use DBI;
use CGI;
use Data::Dumper;

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


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


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



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


my $title = "$lex{View} SSP Programs";
print qq{$doctype\n<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{</head><body>\n};
print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};

print qq{<h1>$title</h1>\n};

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

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

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

} else {
    delete $arr{page};
}


#---------------
sub showPrograms {
#---------------

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

    my $startdate = $arr{startdate};
    delete $arr{startdate};
    my $enddate = $arr{enddate};
    delete $arr{enddate};

    print qq{<h3>Program from $startdate to $enddate</h3>\n};


    # Find distinct grades
    my @grades;
    my $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;


    # Find Grade Enrollments
    my %enrollments;
    my $sth = $dbh->prepare("select count(*) from student where grade = ?");
    foreach my $gr ( @grades ) {
	$sth->execute($gr);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth->fetchrow;
	$enrollments{$gr} = $count;
    }


    # Create Student Data Structure
    my %studentgrade;
    my $sth = $dbh->prepare("select studnum, grade from student");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($studnum, $grade ) = $sth->fetchrow ) {
	$studentgrade{$grade}{$studnum} = 1;
    }


    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption><h3>$schoolname</h3></caption>\n};
    print qq{<tr><th>Program<br>Grade</th>};
    foreach my $prg ( sort keys %programs ) {
	print qq{<th>$prg</th>};
    }
    print qq{</tr>\n};

    # Get Student Name
    my $sth2 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth3 = $dbh->prepare("select studnum from student where grade = ?");

    foreach my $grade ( @grades ) {

	print qq{<tr><td class="la">$grade ($enrollments{$grade})</td>};

	# MasterMissing hash - populate
	my %mst_missing;
	$sth3->execute($grade);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $studnum = $sth3->fetchrow ) {
	    $mst_missing{$studnum} = 1;
	}

	foreach my $prg ( sort keys %programs ) {

	    my $sth1 = $dbh->prepare("select distinct studnum, tgrade from $programs{$prg} where tdate = ? and tgrade = ?");

	    # Get Test Dates
	    my $sth = $dbh->prepare("select distinct tdate, count(tdate) from $programs{$prg} 
             where to_days(tdate) >= to_days('$startdate') and to_days(tdate) <= to_days('$enddate') and 
             tgrade = '$grade' group by tdate");
	    $sth->execute;
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    my $first = 1;
	    print qq{<td style="vertical-align:top;">};

	    my %missing = %mst_missing;

	    my $totalcount;
	    while ( my ($tdate, $tcount) = $sth->fetchrow ) {

		# Reset tracker for students
		$sth1->execute($tdate, $grade);
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		while ( my ($studnum,$tgrade) = $sth1->fetchrow ) {
		    if ( $missing{$studnum} ) { # present in this grade
			$missing{$studnum} = 0;
		    }
		}

		if ( not $first ) { print qq{<br>\n}; } else { $first = 0; }
		print qq{$tdate - $tcount};
		$totalcount += $tcount;
	    }

	    if ( $totalcount ) {
		print qq{<br><b>Total Tests:</b> $totalcount\n};
	    } else { # no tests
		print qq{</td>\n};
		next; # prg
	    }


	    # now check for any students without any tests.
	    my $first = 1;
	    my $count = 1;
	    foreach my $sn ( keys %missing ) {
		if ( $missing{$sn} ) { # no tests done.

		    if ( $first ) {
			print qq{<br><b>Missing Students</b><br>\n};
			$first = 0;
		    }

		    $sth2->execute($sn);
		    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		    my ($lastname, $firstname) = $sth2->fetchrow;
		    print qq{$count.$firstname $lastname ($sn)<br>};
		    $count++;
		}
	    }
	    print qq{[ <a href="$self?gr=$grade&start=$startdate&end=$enddate&prg=$prg&page=2">$lex{Details}</a> ]};
	    print qq{</td>\n};
	
	} # end of different programs.
	print qq{</tr>\n};

    } # end of grade loop

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

    exit;

} # end of showPrograms;





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

#    my $checked;
#    if ( $arr{checked} ) {
#	$checked = qq{checked="checked"};
#    }

#    print qq{<form action="$self" method="post" style="display:inline;margin:1em;">\n};
#    print qq{<input type="hidden" name="checked" value="1">\n};
#    print qq{<input type="submit" value="$lex{'Check All'}" style="display:inline;"></form>\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">\n};

=head
    print qq{<tr><td class="bra">$lex{Select} $lex{Schools}</td><td></td></tr>\n};
    foreach my $db ( sort keys %alldbase ) { # hash now found in admin.conf
	print qq{<tr><td></td><td class="la">};
	print qq{<input type="checkbox" name="$db" value="1" $checked>\n};
	print qq{$alldbase{$db} ($db)</td></tr>\n};
    }
=cut

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


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

    # passed: program, grade, start/end dates
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my $program = $arr{prg};
    my $grade = $arr{gr};
    my $startdate = $arr{start};
    my $enddate = $arr{end};

    my $table = $programs{$program};


    print qq{<h3>$schoolname &mdash; Grade $grade &mdash; $program &mdash; $startdate/$enddate</h3>\n};


    # Get Current Student Enrollment in grade and names.
    my %currstudents;
    my %names;
    my @studsort;
    my $sth = $dbh->prepare("select lastname, firstname, studnum from student where grade = ? order by lastname, firstname");
    $sth->execute( $grade );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    my $sth1 = $dbh->prepare("select count(*) from lint_student s, lint_program p where s.studnum = ? and s.progid = p.id and 
      to_days(p.startdate) >= to_days('$startdate') and to_days(p.enddate) <= to_days('$enddate')"); 


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

	# Check for LLI Program
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $llicount = $sth1->fetchrow;
	my $llistudent;
	if ( $llicount > 0) { $llistudent = qq{<span style="color:red;">**</span>}; }

	$currstudents{$studnum} = '0';
	$names{$studnum} = "<b>$llistudent$lastname</b>, $firstname";
	push @studsort, $studnum;
    }


    my %wdstudents;

    # Get Tests
    my $sth = $dbh->prepare("select * from $table
      where to_days(tdate) >= to_days('$startdate') and to_days(tdate) <= to_days('$enddate') and 
      tgrade = '$grade' order by tdate");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }


    my %reading; # reading levels of tests
    while ( my $ref = $sth->fetchrow_hashref ) {
#	my %r = %$ref;
#	print %r;
	my $studnum = $ref->{studnum};
	$reading{$studnum} = $reading{$studnum}. "$ref->{readlevel} ";
	if ( exists $currstudents{$studnum} ) {
	    $currstudents{$studnum}++;
	} elsif ( exists $wdstudents{$studnum} ) {
	    $wdstudents{$studnum}++;
	} else {
	    $wdstudents{$studnum} = 1;
	}
    }

    # Now print out students and their tests.
    my $first = 1;

    for my $sn ( @studsort ) {

	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<caption>RL=Reading Level (Sorted by Date)<br>Test=Test Count, <span style="color:red;">**</span> = LLI Student</caption>\n};
	    print qq{<tr><th>Student</th><th>Test</th><th>RL</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td class="la">$names{$sn} ($sn)</td><td class="la">};
	if ( not $currstudents{$sn} ) {
	    print qq{<span style="color:red;font-weight:bold;">0</span>};
	} else {
	    print $currstudents{$sn};
	}
	print qq{</td><td>$reading{$sn}</td></tr>\n};
    }

    if ( $first ) {
	print qq{<h3>No Records Found</h3>\n};
    } else {
	print qq{</table><p></p>\n};
    }


    # Now do any withdrawn student tests.
    my $sth = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    $first = 1;

    foreach my $sn ( keys %wdstudents ) {

	if ( $first ) { # print heading.
	    print qq{<h3>Withdrawn Students</h3>\n};
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<caption>RL = Reading Level (Date Sorted), Test = Test Count</caption>\n};
	    print qq{<tr><th>Student</th><th>Test</th><th>RL</th></tr>\n};
	    $first = 0;
	}

	# Look up name
	$sth->execute($sn);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($ln, $fn) = $sth->fetchrow;

	print qq{<tr><td class="la"><b>$ln</b>, $fn</td><td>$wdstudents{$sn}</td><td>$reading{$sn}</td></tr>\n};
    }

    if ( $first ) {
	print qq{<h3>No Withdrawn Student Records Found</h3>\n};
    } else {
	print qq{</table><p></p>\n};
    }

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

    exit;

}
