#!/usr/bin/perl
#  Copyright 2001-2022 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.

# This is still the klunky old stuff not based on meta or templates, yet.

my %lex = ('View' => 'View',
	   'Main' => 'Main',
	   'Eoy' => 'Eoy',
	   'Staff' => 'Staff',
	   'Preregistration' => 'Preregistration',
	   'Name' => 'Name',
	   'Position' => 'Position',
	   'UserID' => 'UserID',
	   'Password' => 'Password',
	   'Homeroom' => 'Homeroom',
	   'Grade' => 'Grade',
	   'E-mail' => 'E-mail',
	   'Phone' => 'Phone',
	   'Address' => 'Address',
	   'Att' => 'Att',
	   'Emergency' => 'Emergency',
	   'Error' => 'Error',
	   'Edit' => 'Edit',
	   'Withdrawn' => 'Withdrawn',

	   );

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;

# Select table to view; staff or prereg_staff;
my ( $table, $tableview, $multitable );
if ( $arr{tbl} ){ # view prereg_staff table
    $table = 'prereg_staff';
    $multitable = 'prereg_staff_multi';
    $tableview = qq{<span style="color:red;">$lex{Preregistration} $lex{Staff}</span>};
    
} else {
    $table = 'staff';
    $multitable = 'staff_multi';
    $tableview = $lex{Staff};
}




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


# Get Sorting Info into %sort hash.
my $sort = 'name';
if ( $arr{sort} ) {  # one of name,grade, homeroom,position
    $sort = $arr{sort};
}
delete $arr{sort};
my %sort;


# get name first.
my $sth = $dbh->prepare("select userid, lastname, firstname from $table");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };

while ( my ($userid,$lastname,$firstname) = $sth->fetchrow ){
    $name{$userid} = qq{$lastname,$firstname};

    # get multitable value for this user
    my @multi; # sorting values from multi table.
    if ( $sort ne 'name' ) {
	my $sth1 = $dbh->prepare("select field_value from $multitable 
				 where userid = ? and field_name = ? order by field_value");
	$sth1->execute($userid, $sort);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };
	while ( my $fvalue = $sth1->fetchrow ) {
	    push @multi, $fvalue;
	}
    }
    my $multistring = join('',@multi);
    if ( $sort eq 'grade' and $multistring eq 'K' or $multistring eq 'PK' ) { # prepend a zero.
	$multistring = '0'.$multistring;
    }
    if ( $sort eq 'homeroom' and $multistring eq 'K' or $multistring eq 'PK' ) { # prepend a zero.
	$multistring = '0'.$multistring;
    }
#    print "Multistring:$multistring - @multi<br>\n";
    $sort{"$multistring$lastname$firstname"} = $userid;
}

#foreach my $key ( sort {$a <=> $b} keys %sort ) {
#    print qq{K:$key VAL:$sort{$key}<br>\n};
#}


my $title = qq{$lex{View} $lex{Staff} };
print qq{$doctype\n<html><head><title>$title</title>};

print qq{<link rel="stylesheet" href="$css" type="text/css">};
print qq{$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$eoypage">$lex{Eoy}</a> |\n};
print qq{ <a href="#withdrawn">$lex{Withdrawn} $lex{Staff}</a> ]\n};

# Optional sorts
print qq{<div style="display:inline;">Sort by </div>\n};
# Grade Sort
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="sort" value="grade">\n};
print qq{<input type="submit" value="Grade"></form>\n};

# Homeroom Sort
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="sort" value="homeroom">\n};
print qq{<input type="submit" value="Homeroom"></form>\n};

# Position Sort
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="sort" value="position">\n};
print qq{<input type="submit" value="Position"></form>\n};

#print qq{SORT:$sort<br>\n};


print qq{<h1>$schoolname - $tableview</h1>\n};




# Table Layout
print qq{<table cellpadding="3" border="1" cellspacing="0" style="margin-bottom:2em;">\n};
my $count;		   

# Query Setup
my $sth1 = $dbh->prepare("select field_value from $multitable where userid = ? and field_name = ?");

my $sth = $dbh->prepare("select * from $table where userid = ?");


foreach my $key ( sort keys %sort ) {
    
    my $userid = $sort{$key};
    
    $sth->execute($userid);
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };
    my $ref = $sth->fetchrow_hashref;

    my %r = %$ref;
    #    my $userid = $r{userid};

    if ( $count % 12 == 0 ) {
	print qq{<tr><th>$lex{Name}/$lex{'E-mail'}</th>};
	print qq{<th>$lex{Position}</th><th>$lex{Emergency}</th>\n};
	print qq{<th>$lex{UserID}</th><th>$lex{Homeroom}</th><th>};
	print qq{$lex{Grade}</th><th>$lex{Phone}</th><th>$lex{Address}</th>};
	print qq{<th>$lex{Att}</th></tr>\n};
    }
    $count++;
    
    
    print qq{<tr style="line-height:120%;">\n<td>};

    # Edit
    print qq{<form action="staffed.pl" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="id" value="$r{id}">\n};
    if ( $r{tbl} ) {
	print qq{<input type="hidden" name="tbl" value="prereg">\n};
    }
    print qq{<input type="submit" value="$lex{Edit}"></form>\n};

    # Name Column
    print qq{ <b>$r{lastname}</b>,\n};
    print qq{$r{sal} $r{firstname} $r{middlename}};
    if ( $r{emailwork} ) {
	print qq{<br>$r{emailwork} <b>W</b>\n};
    }
    if ( $r{emailpersonal} ) {
	print qq{<br>$r{emailpersonal} <b>P</b>\n};
    }
    print qq{</td>\n};
    

    # Lookup positions and print.
    my $first = 1;
    $sth1->execute( $userid, 'position' );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    
    print qq{<td>};
    while ( my $position = $sth1->fetchrow ) {
	if ( not $first ) { print '<br>'; } else { $first = 0; }
	print $position;
    }
    print qq{</td>\n};

    
    # Emergency
    print qq{<td>$r{emergency_contact_name}<br>\n};
    print qq{$r{emergency_contact_phone}</td>\n};

    # Userid / Password
    print qq{<td>$r{userid}</td>\n};
    #<br>$r{password}</td>\n};

    # Homeroom Lookup
    $first = 1;
    $sth1->execute( $userid, 'homeroom' );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    print qq{<td>};
    
    while ( my $homeroom = $sth1->fetchrow ) {
	if ( not $first ) { print '<br>'; } else { $first = 0; }
	print $homeroom;
    }

    print qq{</td>\n<td>};

    $first = 1;
    $sth1->execute( $userid, 'grade' );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    while ( my $grade = $sth1->fetchrow ) {
	if ( not $first ) { print '<br>'; } else { $first = 0; }
	print $grade;
    }
    print qq{</td>};


    print qq{<td>$r{home_phone}H<br>$r{cell_phone}C</td>};
    print qq{<td>$r{street} $r{city}<br>$r{prov} $r{pcode}</td>};
    print qq{<td>$r{doatt}</td></tr>\n};
}

print qq{</table>\n};


# WITHDRAWN Staff
print qq{<a name="withdrawn"></a>\n};
print qq{<h3>Withdrawn Staff</h3>\n};

my $sth1 = $dbh->prepare("select * from staff_transfer where userid = ?");

my $sth = $dbh->prepare("select * from staffwd order by lastname, firstname");$
sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };

# Table Layout
print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
my $count;		   

while ( my $ref = $sth->fetchrow_hashref ){

    my %r = %$ref;
    my $userid = $r{userid};

    if ( $count % 12 == 0 ) {
	print qq{<tr><th>$lex{Name}/$lex{'E-mail'}</th>};
	print qq{<th>$lex{Emergency}</th>\n};
	print qq{<th>$lex{UserID}</th>};
	print qq{<th>$lex{Phone}</th><th>$lex{Address}</th><th>$lex{Att}</th><th>Transfers</th></tr>\n};
    }
    $count++;
    
    
    print qq{<tr style="line-height:120%;">\n<td>};

    # Name Column
    print qq{ <b>$r{lastname}</b>,\n};
    print qq{$r{sal} $r{firstname} $r{middlename}};
    print qq{<br>$r{email}</td>\n};

    
    # Emergency
    print qq{<td>$r{emergency_contact_name}<br>\n};
    print qq{$r{emergency_contact_phone}</td>\n};

    # Userid / Password
    print qq{<td>$r{userid}</td>\n};
    #<br>$r{password}</td>\n};

    print qq{<td>H:$r{home_phone}<br>C:$r{cell_phone}</td>};
    print qq{<td>$r{street} $r{city}<br>$r{prov} $r{pcode}</td>};
    print qq{<td>$r{doatt}</td>\n};

    # Lookup Transfers
    $sth1->execute( $userid);
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}

    my $first = 1;
    print qq{<td>};
    while ( my $ref = $sth1->fetchrow_hashref ) {
	my %r = %$ref;
	if ( not $first ) { print '<br>'; } else { $first = 0; }
	print qq{$r{type} - $r{date} - $r{description}\n};
    }
    print qq{</td></tr>\n};

}

print qq{</table>\n};
# End of showing withdrawn.


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