#!/usr/bin/perl
#  Copyright 2001-2024 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/Edit' => 'View/Edit',
	   'Transfer Records' => 'Transfer Records',
	   'Name' => 'Name',
	   'Birthdate' => 'Birthdate',
	   'Identity' => 'Identity',
	   'Date' => 'Date',
	   'Type' => 'Type',
	   'Description' => 'Description',
	   'Delete' => 'Delete',
	   'Not Found' => 'Not Found',
	   'Main' => 'Main',
	   'Student' => 'Student',
	   'Error' => 'Error',
	   'Edit' => 'Edit',
	   'Sort by' => 'Sort by',

	   );

my $self = 'transview.pl';
my $allowDelete = 0;

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;


if ( $arr{del} ) {
    $allowDelete = 1;
}

# Set current date
my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
my $currdate = "$year-$month-$day";

my ($y,$m,$d) = split('-',$schoolstart);
$y -= 3; # start 3 years back.
my $startdate = "$y-$m-$d";
my $select = qq{where to_days(date) > to_days('$startdate') };
if ( $arr{nodate} ) {  # turn off the 3 year back option.
    #delete $arr{nodate};
    $select = '';
}

# Sorting.
my $sort = 'order by lastname, firstname,studnum, date desc';
if ( $arr{sort} eq $lex{Name} ){
    $sort = 'order by lastname, firstname,studnum, date desc';

} elsif ( $arr{sort} eq $lex{Date} ) {
    $sort = 'order by date desc, lastname, firstname';
}

# Setup Dbase
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



my $title = qq{$lex{'View/Edit'} $lex{'Transfer Records'}};
print qq{$doctype\n<html><head><title>$title</title>\n};
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};

if ( $arr{page} == 1 ) {
    delete $arr{page};
    showIndividual();
}


# Sort by Date
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="sort" value="$lex{Date}">\n};
if ($allowDelete) {
    print qq{<input type="hidden" name="del" value="1">\n};
}
print qq{<input type="submit" value="$lex{'Sort by'} $lex{Date}"></form>\n};

# Sort by Student
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="sort" value="$lex{Name}">\n};
if ($allowDelete) {
    print qq{<input type="hidden" name="del" value="1">\n};
}
print qq{<input type="submit" value="$lex{'Sort by'} $lex{Student}"></form>\n};

# Show All Records by Student
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="nodate" value="1">\n};
print qq{<input type="hidden" name="sort" value="$lex{Name}">\n};
print qq{<input type="submit" value="Show All Records by Student"></form>\n};

# Show All Records by Date
print qq{<form action="$self" method="post" style="display:inline;">\n};
print qq{<input type="hidden" name="nodate" value="1">\n};
print qq{<input type="hidden" name="sort" value="$lex{Date}">\n};
print qq{<input type="submit" value="Show All Records by Date"></form>\n};



print qq{<h1>$title</h1>\n};

print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
if ( not $arr{nodate} ) {
    print qq{<caption style="font-weight:bold;font-size:130%;text-align:left;color:red;">};
    print qq{Starting Date $startdate (3 years ago)</caption>\n};
}

print qq{<tr><th>$lex{Name}</th><th>$lex{Birthdate}/Provnum</th>};
print qq{<th>$lex{Date}</th><th>$lex{Type}</th><th>$lex{Description}</th><th></th></tr>\n};

$currdate = -1;
my $olddate;

#while ( my ( $id, $studnum, $date, $type, $description, $entrytype,
#  $exittype, $prov, $country, $utag, $lastname, $firstname, $middlename,
#  $birthdate, $provnum ) = $sth->fetchrow) {

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

$sth = $dbh->prepare("select * from transfer $select $sort");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}

while ( my $ref = $sth->fetchrow_hashref ) {
    my %r = %$ref;
    
    $olddate = $currdate;
    if ( $arr{sort} eq $lex{Date} ){ 
	$currdate = $r{date}; 
    } else { 
	$currdate = $r{studnum}; 
    } # studnum

    if ( $currdate ne $olddate ) {
	if ($color eq "blue"){ $color = "gray";} else { $color = "blue"; } 
    }
    
    if ( not $r{lastname} ) { # no lastname...

	# Search studentall for lastname
	$sth1->execute( $r{studnum} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	if ( not $lastname ) { # still no lastname....
	    $r{lastname} = qq{<span style="color:red;">$lex{'Not Found'}</span>};
	}
    }

    print qq{<tr class="$color"><td class="la">};
    print qq{<b>$r{lastname}</b>, $r{firstname} $r{middlename} ($r{studnum})</td>\n};
    print qq{<td>$r{birthdate}/$r{provnum}</td><td>$r{date}</td><td>$r{type}</td>};
    print qq{<td style="width:60ch;">$r{description}</td>\n<td>};

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

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

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

}

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

#-----------------
sub showIndividual {
#-----------------

    my $studnum = $arr{studnum};

    # Get Name from studentall table
    my $sth = $dbh->prepare("select lastname, firstname, initial from studentall where studnum = ?");
    $sth->execute( $studnum );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my ( $lastname, $firstname, $middlename ) = $sth->fetchrow;

    # Check if withdrawn
    my $wd;
    my $sth = $dbh->prepare("select count(*) from studentwd where studnum = ?");
    $sth->execute( $studnum );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my $count = $sth->fetchrow;
#    if ( $count ) { $wd = qq{<span style="font-weight:bold;font-size:120%;color:red;">WD</span>\n}; }

    
    # Start Table.
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;text-align:left;">};
    print qq{$wd $firstname $middlename $lastname - Sorted by Date Descending (Latest First)};
    print qq{</caption>\n};
    print qq{<tr><th>$lex{Name}</th><th>$lex{Birthdate}/Provnum</th>};
    print qq{<th>$lex{Date}</th><th>$lex{Type}</th><th>$lex{Description}</th><th></th></tr>\n};

    my $sth = $dbh->prepare("select * from transfer where studnum = ? order by date desc");
    $sth->execute( $studnum );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	print qq{<tr class="blue"><td class="la">};
	print qq{<b>$r{lastname}</b>, $r{firstname} $r{middlename} ($r{studnum})</td>\n};
	print qq{<td>$r{birthdate}/$r{provnum}</td><td>$r{date}</td><td>$r{type}</td>};
	print qq{<td style="width:60ch;">$r{description}</td>\n<td>};

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

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

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

    }

    print qq{</table>\n};

    # Current Student or Withdrawn
    if ( $count ) { # withdrawn
	print qq{<div style="margin:1em;">$firstname is a };
	print qq{<span style="font-weight:bold;font-size:120%;color:red;">};
	print qq{Withdrawn</span> student.</p>\n};
	
    } else {
	
	print qq{<div style="margin:1em;">$firstname is a };
	print qq{<span style="font-weight:bold;font-size:120%;color:green;">};
	print qq{Current</span> student.</div>\n};
    }

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

    exit;
}
