#!/usr/bin/perl
#  Copyright 2001-2022 Leslie Richardson

#  This file is part of Open Admin for Schools.

my %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'Discipline' => 'Discipline',

    );


use DBI;
use CGI;

my $self = 'hvReport1.pl';

# Read config variables
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);


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


my $title = "Home Visits - Report 1";
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 style="padding:1em 2em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> | <a href="$discpage">$lex{Discipline}</a> ]\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showReport();

}


#----------------
sub showStartPage {
#----------------

    # find all authors in homevisits table.
    my $sth = $dbh->prepare("select distinct author from homevisit 
			    where author is not null and author != '' order by author");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die; }

    # get author names
    my $sth1 = $dbh->prepare("select firstname, lastname from staff where userid = ?");

    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    print qq{<table cellspacing="0" border="0" cellpadding="3">\n};

    print qq{<tr><td class="bra">Select Author</td><td class="la">\n};
    print qq{<select name="author"><option value=""></option>\n};

    while ( my $author = $sth->fetchrow ) {

	$sth1->execute($author);
	if ($DBI::errstr) { print $DBI::errstr; die; }
	my ($firstname, $lastname) = $sth1->fetchrow;
	
	print qq{<option value="$author">$firstname $lastname ($author)</option>\n};
    }
    
    print qq{<tr><td></td><td class="la"><input type="submit" value="$lex{Continue}">\n};
    print qq{</td></tr></table></form>\n};

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

    exit;

}



#-------------
sub showReport  {
#-------------

    # foreach my $key ( sort keys %arr ) { print "K:$key Val:$arr{$key}<br>\n"; }
    # Passed author.

    my $sth = $dbh->prepare("select * from homevisit where author = ? order by date desc");
    $sth->execute( $arr{author} );
    if ($DBI::errstr) {print $DBI::errstr; die;}

    # Get Student Name
    my $sth1 = $dbh->prepare("select firstname, lastname from studentall where studnum = ?");
    
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<caption>Ordered by Descending Date (New to Old)</caption>\n};
    print qq{<tr><th style="width:10ch;">Date</th><th style="width:24ch;">Student</th>};
    print qq{<th>Reason</th><th>Description</th></tr>\n};

    my $count;
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	$sth1->execute($r{studnum});
	if ($DBI::errstr) { print $DBI::errstr; die; }
	my ($firstname, $lastname) = $sth1->fetchrow;
	
	print qq{<tr><td>$r{date}</td><td>$firstname $lastname ($r{studnum})</td>};
	print qq{<td>$r{reason}</td><td>$r{description}</td></tr>\n};
	$count++;
    }

    print qq{<tr><td colspan="4" class="bla">$count Records</td></tr>\n};

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

}
