#!/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 = ('No Student(s) Found' => 'No Student(s) Found',
	   'Student Search' => 'Student Search',
	   'Main' => 'Main',
	   'Last,First/Last/First/Initials/Studnum', 'Last,First/Last/First/Initials/Studnum',
	   'Search' => 'Search',
	   'Cannot open' => 'Cannot open',
	   'Error' => 'Error',

	   );

use DBI;
use CGI;

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);
$dbh->{mysql_enable_utf8} = 1;

# print HTML heading
my $title = $lex{'Student Search'};
print qq{$doctype\n<html><head><title>$title</title>};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body>\n};

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


if ( not $arr{student} ) { # display entry form
    print qq{<p></p>\n};
    displaySearchForm();
    print qq{</body></html>\n};
    exit;
}

my $student = $arr{student};


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

# Setup the Search
if ($student =~ /\d+/) {  # we have a student number
    $studentnumber = $student;
    $sth = $dbh->prepare("select $fields from studentall where studnum = ?");
    $sth->execute( $student );

} else { # we have words hopefully with a comma

    my ( $lastname,$firstname )  = split(',', $student);

    if ( $lastname and $firstname ){ # both entered.
	$sth = $dbh->prepare("select $fields from studentall 
         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 $fields from studentall 
              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 $fields from studentall 
             where lastname = ? or firstname = ? order by lastname, firstname");
	    $sth->execute( $lastname, $lastname );
	}
    }

} # Last Else

if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }


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


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

# Check if withdrawn;
my $sth1= $dbh->prepare("select count(*) from studentwd where studnum = ?");

# Loop through and display all records.
my $first = 1;
while ( my ( $lastname, $firstname, $studnum, $studid, $pic ) = $sth->fetchrow ){

    $sth1->execute( $studnum);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $wd = $sth1->fetchrow;
    if ( $wd ) {
	$wd = qq{<span style="color:red;font-weight:bold;">WD</span>};
    } else {
	$wd = '';
    }
    
    $first = 0;
    print qq{<table border="1" cellspacing="0" cellpadding="3" style="margin-bottom:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:144%;text-align:left;">};
    print qq{$wd $firstname $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( $studnum );

    print qq{</table>\n\n};
    
} # End of Student Loop

if ( $first ) {
    print qq{<h2>$lex{'No Student(s) Found'}</h2>\n};
}

print qq{</table><p>[ <a href="$homepage">$lex{Main}</a> ]</p>\n};

displaySearchForm();

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


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

    print qq{<form action="studsearch.pl" method="post">\n};
    print qq{<div style="font-size:120%;font-weight:bold;">};
    print qq{$lex{'Last,First/Last/First/Initials/Studnum'}</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 {
#-------------------

    # print the student record (as a row in a table)
    # Passed in values: $studnum, (global: $picdirurl, $cgiurl )
    # Also read in template value.

    my $studnum = shift;

    # Read in Field Values
    my $sth = $dbh->prepare("select * from studentall where studnum = ?");
    $sth->execute( $studnum );
    if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
    my $studref = $sth->fetchrow_hashref;
    my %studval = %$studref;

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


} # End of PrStudentRecord
