#!/usr/bin/perl
#  Copyright 2001-2021 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 = ('Withdrawn Students' => 'Withdrawn Students',
	   'Name' => 'Name',
	   'Number' => 'Number',
	   'Grade' => 'Grade',
	   'Gender' => 'Gender',
	   'Birthdate' => 'Birthdate',
	   'Ident' => 'Ident',
	   'Main' => 'Main',
	   'Error' => 'Error',
	   'View' => 'View',
	   'Last,First/Last/First/Initials', 'Last,First/Last/First/Initials',
	   'School' => 'School',
	   'Search' => 'Search',
	   'Student' => 'Student',
	   
	   );

use DBI;
use CGI;

my $self = 'globview.pl';

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

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

if ( not -e "$globdir/global.conf" ) {
    print "Cannot read the global.conf file!";
    print "</body></html>\n";
    die;
}

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

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


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


# HTML Heading
my $title = qq{$lex{View} Other Schools $lex{'Withdrawn Students'}};
print qq{$doctype\n<html><head><title>$title</title>
<link rel="stylesheet" href="$css" type="text/css">\n};

print qq{$chartype\n</head>\n};
print qq{<body style="margin:1em;">[ <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};
    showSchool();

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


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

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

    print qq{<div style="margin:2em 0em;">\n};
    print qq{<form action="$self" method="post" >\n};
    print qq{<div style="font-size:120%;font-weight:bold;">};
    print qq{$lex{'Last,First/Last/First/Initials'}</div>\n};
    print qq{<input type="text" name="student" style="width:40ch;">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="submit" value="$lex{Search} for $lex{Student}"></form>\n};
    print qq{</div>\n};
    
    print qq{<hr style="width:40ch;margin:1em 0em;">\n};

    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="$lex{View} $lex{School}">\n};	
    
    print qq{<table border="1" cellspacing="0" cellpadding="3">\n};
    print qq{<tr><th>Select</th><th>School</th></tr>\n};
    
    foreach $database (sort keys %dbase) { #defined in global config file.
	print qq{<tr><td class="cn"><input type="checkbox" name="$database" value="1"></td>\n};
	print qq{<td>$dbase{$database}</td></tr>\n};
    }
    print qq{</table>\n};
    print qq{<input type="submit" value="$lex{View} $lex{School}">\n};	
    print qq{</form>\n};

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

}


#-------------
sub showSchool {
#-------------

    # passed school database (if any), otherwise display all;
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    foreach $database ( sort keys %dbase) { #defined in global config file.
	if ( %arr and not $arr{$database} ) { next; }
	
	$dsn = "DBI:$dbtype:dbname=$database";
	$dbh = DBI->connect($dsn,$guser,$gpassword);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	$dbh->{mysql_enable_utf8} = 1;

	my $select;
	if ( $arr{grade} ) {
	    my $gr = $dbh->quote( $arr{grade} );
	    $select = "where grade = $gr";
	}

	$sortorder = "lastname, firstname";
	if ($arr{sortorder} eq "birthdate") {
	    $sortorder = "birthdate";
	} 

	$sth = $dbh->prepare("select * from studentwd $select order by $sortorder");
	$sth->execute;
	if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr; }
    
    
	print qq{<table border="1" cellspacing="0" cellpadding="3" style="float:left;margin:0.5em;">\n};
	print qq{<caption style="font-weight:bold;font-size:120%;">$dbase{$database}</caption>\n};
	print qq{<tr><th>$lex{Name}</th><th>$lex{Number}</th>\n};
	print qq{<th>$lex{Grade}</th><th>$lex{Gender}</th><th>$lex{Birthdate}</th>\n};
	print qq{<th>$lex{Ident}</th></tr>\n};
	
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    print qq{<tr><td><b>$r{lastname}</b>, $r{firstname} $r{initial}</td>\n};
	    print qq{<td>$r{studnum}</td><td>$r{grade}</td><td>$r{sex}</td>\n};
	    print qq{<td>$r{birthdate}</td><td>$r{provnum}</td></tr>\n\n};
	}
	print qq{</table>\n};
    
    }  # End of Database reading Loop.

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

} # end of showSchool


#--------------
sub showStudent {
#--------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    my $student = $arr{student};
    
    # Get Local database for meta values
    my $dsn = "DBI:$dbtype:dbname=$dbase";
    my $dbh = DBI->connect($dsn,$user,$password);
    $dbh->{mysql_enable_utf8} = 1;
    
    # Read in Meta Values
    my %metaval;

    my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = 'student'");
    $sth->execute;
    if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
    while ( my ($fid, $fname ) = $sth->fetchrow ) {
	$metaval{ $fid } = $fname;
    }

#    my $fields = 'lastname, firstname, studnum, studid, pic';
    my %stud; # hash to store the information.
    my ( $lastname,$firstname )  = split(',', $student);
    
      # Loop through all databases looking for the student;
    foreach $database ( sort keys %dbase) { #defined in global config file.
	
	$dsn = "DBI:$dbtype:dbname=$database";
	$dbh = DBI->connect($dsn,$guser,$gpassword);
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	$dbh->{mysql_enable_utf8} = 1;

    
	if ( $lastname and $firstname ){ # both entered.
	    $sth = $dbh->prepare("select * from studentwd where lastname = ? and firstname = ?");
	    $sth->execute( $lastname, $firstname );

	} elsif ($lastname and not $firstname){ # only lastname (no comma)

	    if ( length($lastname) == 2 ){ # search by initials: fi, li.
		my $fi = substr($lastname,0,1). '%'; 
		my $li = substr($lastname,1,1). '%';
		$sth = $dbh->prepare("select * from studentwd 
				     where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );

	    } else { # a single value; could be lastname or firstname
		$sth = $dbh->prepare("select * from studentwd where lastname = ? 
				     or firstname = ? order by lastname, firstname");
		$sth->execute( $lastname, $lastname );
	    }
	}

	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ){
	    my %r = %$ref;
	    my $studnum = $r{studnum};
	    $stud{"$database:$studnum"} = $ref;
	}

    } # end of database loop

	
   # Read in Template
    unless (open (FH,"<../../template/globview.tpl")) {
	print $lex{'Cannot open'}. " template file: $!\n";
	die $lex{'Cannot open'}. " template file: $!\n";
    }
    $text; # now a global
    { local $/; $text = <FH>; close FH;}


    # Replace meta values.
    $text =~ s{\<\*(.*?)\*\>}
    { exists($metaval{$1}) 
	  ? $metaval{$1} 
    : $1
    }gsex;

    # start table


    # Loop through and display all records.
    foreach my $key ( sort keys %stud ) {
	my %r = %{ $stud{$key}};
	my ($db,$studnum) = split(':', $key);

	print qq{<table border="1" cellspacing="0" cellpadding="3" };
	print qq{style="border-bottom:2em;margin:1em;">\n};
	print qq{<caption style="font-weight:bold;font-size:144%;text-align:left;">};
	print qq{$dbase{$db} - $r{firstname} $r{lastname}</caption>\n};
    
#	if ($pic eq 'Y') {
#	    print qq{<tr><td colspan="5" style="background-color:#DDD; text-align:left;">};
#	    print qq{<img src="/pic-sm/$studnum.jpg" align="middle">};
#	    print qq{ <span style="font-size:200%;font-weight:bold;">$firstname $lastname</td></tr>\n};
#	}

	prStudentRecord( $stud{$key} );  # passed a ref;
	print qq{</table>\n};
	
    } # End of Student Loop

    if ( not %stud ) {
	print qq{<h3>No Students Found!</h3>\n};
    }

    
    print qq{<p>[ <a href="$homepage">$lex{Main}</a> ]</p>\n};
    
    displaySearchForm();
    
    print qq{</body></html>\n};

} # end of showStudent



#-------------------
sub displaySearchForm {
#-------------------

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<div style="font-size:120%;font-weight:bold;">};
    print qq{$lex{'Last,First/Last/First/Initials'}</div>\n};
    print qq{<input type="text" name="student" style="width:40ch;">\n};
    print qq{<input type="submit" value="$lex{Search}"></form>\n};

    return;

}

#-------------------
sub prStudentRecord {
#-------------------

    my $ref = shift;
#    print "REF:$ref<br>\n";
    
    # Read in Field Values
    my %studval = %$ref;

    my $formtext = $text; # text defined before function call.
    # Now put replacement text back in.
    $formtext =~ s{\<\@(.*?)\@\>}
              { exists($studval{$1}) 
		  ? $studval{$1} 
	          : $1
	      }gsex;
    print $formtext,"\n";

    return;
    
} # End of PrStudentRecord
