#!/usr/bin/perl
#  Copyright 2001-2016 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 = ('View' => 'View',
	   'Nominal Roll' => 'Nominal Roll',
	   'Main' => 'Main',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Error' => 'Error',
	   'Student' => 'Student',
	   'Grade' => 'Grade',
	   'Records' => 'Records',
	   'Continue' => 'Continue',
	   'All' => 'All',
	   'Fields' => 'Fields',
	   'Select' => 'Select',
	   'Students' => 'Students',
	   'Homeroom' => 'Homeroom',
	   'Check' => 'Check',
	   'Next Page' => 'Next Page',
	   'Blank=All' => 'Blank=All',
	   'Not Found' => 'Not Found',
	   'Record' => 'Record',
	   'Group' => 'Group',

	   );


use DBI;
use CGI;

my $self = 'nrview1.pl';
$nrtable = 'student_inac';


eval require "../../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 $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Read student inac fields, and defaults into fields and %fieldname hash.
my $sth = $dbh->prepare("select fieldid, fieldname
 from meta where tableid = 'student_inac' order by fieldname");
$sth->execute;
my %fieldnames = ();
my @fields = ();

while ( ( my $fieldid, $fieldname ) = $sth->fetchrow ) {
    if ( $fieldid eq 'id' ) { next; } # skip id field.
    $fieldname =~ s/\(//g;
    $fieldname =~ s/\)//g; # strip parentheses. (sp?)
    push @fields, $fieldid;
    $fieldnames{ $fieldid } = $fieldname;
}


# Print Page Header
my $title = "$lex{View} $lex{'Nominal Roll'} $lex{'Records'} 1";
print "$doctype\n<html><head><title>$title</title>\n";
print "<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">
<style type=\"text/css\">td.ra { text-align:right; }</style>
$chartype\n</head><body>\n";

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


viewStudents();



#---------------
sub viewStudents { #all
#---------------

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

#    my $sth1 = $dbh->prepare("select lastname, firstname, grade, treaty from studentall where studnum = ?");

    # Read student inac fields, and defaults into fields and %fieldname hash.
    my $sth = $dbh->prepare("select defaultvalue from meta where tableid = 'student_inac' and 
       fieldid = 'serviceprovision'");
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my %sp;
    my $dv = $sth->fetchrow;
    %sp = split(/\s+/, $dv);
    foreach my $key ( sort keys %sp ) { 
	$sp{$key} =~ s/\_/ /g;
    }

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

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


    # Get Service Provision
    $sth = $dbh->prepare("select distinct serviceprovision from $nrtable 
      where serviceprovision != 0 and serviceprovision != ''");
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my @servicepro;
    while ( my $sp = $sth->fetchrow ) {
	push @servicepro, $sp;
    }
    @servicepro = sort { $a <=> $b } @servicepro;


        
    # Get Transfer Info
    my $sth2 = $dbh->prepare("select date, type from transfer where studnum = ? order by date desc");
    
    
    # Get Nominal Roll info (along with student info).
    my $sth = $dbh->prepare("select s.lastname, s.firstname, s.studnum 
      from $nrtable n , studentall s
      where s.studnum = n.studnum and n.serviceprovision = ? and s.grade = ? order by lastname, firstname");

    
    foreach my $serviceprovision ( @servicepro ) {

	my $count = 0;
	my $color1 = '#FFF';
	my $color2 = '#DDD';
	my $bgcolor;

	my $spval = 0;
	my $grd = 0;

	
	print qq{<h3>$sp{$serviceprovision} ($serviceprovision)</h3>\n};
	print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
	if ($serviceprovision eq '02' ) {
	    print qq{<caption style="font-weight:bold;">Continued Enrollment = Current Year Students</caption>\n};
	}
	print "<tr><th>Name</th><th>Grade</th><th></th><th>Enrollment</th></tr>\n";
	
	foreach my $grade ( @grades ) {

	    $sth->execute($serviceprovision, $grade );
	    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }


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

		$count++;

		# Toggle color on grade change.
		if ( $grd ne $grade ) { # toggle color;
		    if ( $bgcolor eq $color1 ) { $bgcolor = $color2; } else { $bgcolor = $color1; }
		}
		$grd = $grade;

		# Get Last 2 Transfers (Enrol/Withdraw)
		my ($transfer1, $transfer2);
		my $res = $sth2->execute( $studnum );
		if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my ($date,$type) = $sth2->fetchrow;
		if ( $type ) {
		    $transfer1 = "$type (". fmtdate($date). ")";
		}
		if ( $res > 1 ) {
		    ($date,$type) = $sth2->fetchrow;
		    if ( $type ) {
			$transfer2 = "<br>$type (". fmtdate($date). ")";
		    }
		}


#		my $spvalue = "$sp{$serviceprovision} ($serviceprovision)";
#		if ( not $serviceprovision ) {
#		    $spvalue = qq{<span style="color:red;font-weight:bold;">No Value</span>};
#		}
		print qq{<tr style="background-color:$bgcolor;">};
		print qq{<td>$count. <b>$lastname</b>, $firstname ($studnum)</td><td>$grade</td>};

		print qq{<td><form action="nrdeled.pl" method="post" style="display:inline;">};
		print qq{<input type="hidden" name="page" value="2">};
		print qq{<input type="hidden" name="type" value="Edit">};
		print qq{<input type="hidden" name="field" value="all">};
		print qq{<input type="hidden" name="$studnum" value="1"><input type="submit" value="Edit"></form>\n};

		print qq{<form action="nrdeled.pl" method="post" style="display:inline;">};
		print qq{<input type="hidden" name="page" value="2">};
		print qq{<input type="hidden" name="type" value="Delete">};
		print qq{<input type="hidden" name="field" value="all">};
		print qq{<input type="hidden" name="$studnum" value="1">};
		print qq{<input type="submit" value="Delete"></form></td>\n};

		print qq{<td>$transfer1$transfer2</td></tr>\n};

	    } # end of student loop for this SP and Grade

	} # end of grades loop.
	    
	print "</table>\n";

    } # end of service provision
	
    print "</body></html>\n";

} # end of viewStudents


#----------
sub fmtdate {
#----------
    my $date = shift;
    my ($yr,$mo,$da) = split('-', $date);
    my $month = $s_month[$mo];
    return $date = $yr. $month. $da;

}
