#!/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}. ": $@
\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$title}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title

\n}; if ( not $arr{student} ) { # display entry form print qq{

\n}; displaySearchForm(); print qq{\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 = ; 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{WD}; } else { $wd = ''; } $first = 0; print qq{\n}; print qq{\n}; if ($pic eq 'Y') { print qq{\n}; } prStudentRecord( $studnum ); print qq{
}; print qq{$wd $firstname $lastname
}; print qq{}; print qq{ $firstname $lastname
\n\n}; } # End of Student Loop if ( $first ) { print qq{

$lex{'No Student(s) Found'}

\n}; } print qq{

[ $lex{Main} ]

\n}; displaySearchForm(); print qq{\n}; #------------------- sub displaySearchForm { #------------------- print qq{
\n}; print qq{
}; print qq{$lex{'Last,First/Last/First/Initials/Studnum'}
\n}; print qq{\n}; print qq{
\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