#!/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.

my %lex = ('Reserve Resident Students' => 'Reserve Resident Students',
	   'Main' => 'Main',
	   'Native Bands' => 'Native Bands',
	   'Band Name' => 'Band Name',
	   'Band Number' => 'Band Number',
	   'Grade' => 'Grade',
	   'Student' => 'Student',
	   'Reserve' => 'Reserve',
	   'Parents' => 'Parent(s)',
	   'Error' => 'Error',

	   );

use DBI;
use CGI;

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

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

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


# Page Header
my $title = $lex{'Reserve Resident Students'};
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><a name="top"></a>\n};

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

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

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

print qq{<table border="1" cellpadding="3" cellspacing="0" style="float:left;margin:0.5em;">\n};
print qq{<caption><b>Bands</b> - Both Name and Number</caption>\n};
print qq{<tr><th>$lex{'Band Name'}</th><th>$lex{'Band Number'}</th></tr>\n};

$sth = $dbh->prepare("select distinct band, bandnum from student order by band, bandnum");
# where band is not NULL and band != '' order by band");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my ( $bandname,$bandnumber) = $sth->fetchrow){
    if ( not $bandname and not $bandnumber ) { next; }
    print qq{<tr><td>$bandname</td><td>$bandnumber</td></tr>\n};
}
print qq{</table>\n};


# my $select = "where reserveres != 'On'";
my $sortby = 'cast(grade as signed integer), lastname, firstname';

$sth = $dbh->prepare("select lastname, firstname, initial, studnum,
  birthdate, treaty, band, bandnum, name1, name2, grade, reserveres from student
  order by $sortby");

$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

$currgrade = -1;
$firstflag = 1;

while ( my ($lastname, $firstname, $initial, $studnum, $birthdate, $treaty,
	    $band, $bandnum, $name1, $name2, $grade, $reserveres) = $sth->fetchrow ) {

    $oldgrade = $currgrade;
    $currgrade = $grade;

    if ($oldgrade ne $currgrade) { # we have a new grade
	if (not $firstflag){ print qq{</table>\n}; } else { $firstflag = 0; }
	print qq{<table border="1" cellpadding="3" cellspacing="0" style="float:left;margin:0.5em;">\n};

	print qq{<caption><b>$lex{Grade} $currgrade</b></caption>\n};
	print qq{<tr><th>$lex{Student}</th><th>$lex{Reserve}</th>\n};
	print qq{<th>$lex{Parents}</th><th>Reserve<br>Resident</th></tr>\n};
    }

    print qq{<tr><td><b>$lastname</b>, $firstname $initial<br>};
    print qq{$birthdate ($studnum)</td>\n};
    # Student name (1,2,3) Student num (4) and birthdate (8)

    print qq{<td>$treaty<br>$band $bandnum</td>\n};
    # Treaty number (unique), Band Name, Band number

    if ( $reserveres == 1 ) {
	$reserveres = 'Yes';
    } elsif ( $reserveres == 0 ) {
	$reserveres = '';
    }
    print qq{<td>$name1<br>$name2</td><td class="cn">$reserveres</td></tr>\n};
    # Name1 and Name2 fields

} # End of Main Loop

print qq{</table><br clear="left">\n};

print qq{[ <a href="#top">Top</a> ]</body></html>\n};
