#!/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 = ('Main' => 'Main',
	   'Attendance' => 'Attendance',
	   'No Absences found' => 'No Absences found',
	   'Student Not Found' => 'Student Not Found',
	   'View' => 'View',
	   'Name' => 'Name',
	   'Date' => 'Date',
	   'Reason' => 'Reason',
	   'Period' => 'Period',
	   'Homeroom' => 'Homeroom',
	   'Phone' => 'Phone',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Student' => 'Student',
	   'Home' => 'Home',
	   'Work' => 'Work',
	   'Error' => 'Error',
	   'Course' => 'Course',
	   'by Student' => 'by Student',
	   'by Date' => 'by Date',
	   'by Course' => 'by Course',
	   'Start' => 'Start',
	   'Single' => 'Single',
	   'End' => 'End',
	   'Blank=Single Date' => 'Blank=Single Date',
	   
	   );

my $self = 'attview.pl';

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;

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


my $title = "$lex{View} $lex{Attendance}";

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

if ( not $arr{page} ) { # load javascript
    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};
}

print qq{$chartype\n</head><body>\n};
print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$attpage">$lex{Attendance}</a> ]\n};

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


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

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


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

    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    
    my ( $header, $student, $sth );

    my $orderby = "s.lastname, s.firstname, a.absdate desc, a.period";
    my $orderbystud = "a.absdate desc, s.lastname, s.firstname, a.period";

    my $sth = $dbh->prepare("select @fields from attend, studentall
      where attend.studentid = studentall.studnum $select order by $orderby");

    
    if ( $arr{startdate} and $arr{enddate} ) {

	$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
           where a.studentid = s.studnum and 
           to_days(attend.absdate) >= to_days( ? ) and 
	   to_days(attend.absdate) <= to_days( ?) order by $orderby");
	$sth->execute( $arr{startdate}, $arr{enddate} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	
	$header = "<em>$lex{'Start Date'}:</em> $arr{startdate} <em>".
	    "$lex{'End Date'}:</em>$arr{enddate}";

    } elsif ( $arr{startdate} ) {  # startdate only; a single day

	$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
           where a.studentid = s.studnum and 
           to_days(attend.absdate) = to_days( ? ) by $orderby");
	$sth->execute( $arr{startdate} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	
	$header = "<em>$lex{'Start Date'}</em> $arr{startdate}";
	

    } elsif ( $arr{course} ) {
	
	$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
           where a.studentid = s.studnum and subjsec = ? order by $orderby");
	$sth->execute( $arr{course} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	$header = "$lex{Course}/$lex{Homeroom} $arr{course}";

	
    } elsif ( $arr{student} ) {
	$student = $arr{student};

	my $studnum;
	
	# Setup the Search
	if ($student =~ /\d+/) {  # we have a student number
	    
	    $studnum = $student;
	    
	} else { # we have words hopefully with a comma
	    
	    ($lastname,$firstname)  = split /\,/, $student;
	    $firstname =~ s/^\s*//;
	    $lastname =~ s/^\s*//;
	    if ($lastname and $firstname){ # both entered.
		$select = "s.lastname = '$lastname' and s.firstname = '$firstname'";
	    } elsif ($lastname and not $firstname){ # only lastname (no comma)
		if (length($lastname) == 2){ # search by initials: fi, li.
		    $fi = substr($lastname,0,1); $li = substr($lastname,1,1);
		    $select = "s.lastname like '$li%' and  s.firstname like '$fi%'";
		} else {
		    $select = "s.lastname = '$lastname'";
		}
		
	    } else { # print an error....
		print qq{<b>$lex{'Student Not Found'}</b>\n};
		print qq{</body></html>\n};
		exit; 
	    }

	    # Find the student number that matches
	    $sth = $dbh->prepare("select studnum from studentall s where $select");
	    $sth->execute;
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    $studnum = $sth->fetchrow;

	    
	} # Last Else


	$sth = $dbh->prepare("select a.*, s.lastname, s.firstname from attend a, studentall s
           where a.studentid = s.studnum and studnum = ? order by $orderby");
	$sth->execute($studnum);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	$header = "<em>$lex{Student}: <b>$firstname $lastname</b> ($arr{student})</em>\n";
	
    }
    

    my $sth1 = $dbh->prepare("select * from studentall where studnum = ? ");
    my $sth2 = $dbh->prepare("select description, teacher from subject where subjsec = ?");
    my $sth3 = $dbh->prepare("select lastname, firstname from staff s, staff_multi sm 
      where s.userid = sm.userid and sm.field_name = 'homeroom' and sm.field_value = ?");

    my $first = 1;

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


	if ( $first ) {
	    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	    print qq{<caption style="font-weight:bold;font-size:120%;">$header</caption>\n};
	    print qq{<tr><th>$lex{Name}</th><th>$lex{Date}</th>};
	    print qq{<th>$lex{Reason}</th>\n<th>$lex{Period}</th>};
	    print qq{<th>$lex{Course}/$lex{Homeroom}</th><th>$lex{Phone}</th></tr>\n};
	    $first = 0;
	}

	
	# Get Student Information
	$sth1->execute( $r{studentid} );
	if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
	my $sref = $sth1->fetchrow_hashref;
	my %s = %{$sref};
	
	
	# Get Course/Teacher Information
	my $course_or_homeroom;
	if ( $r{subjsec} !~ m/^HR:/ and $r{subjsec} ){  # not a homeroom value
	    $sth2->execute( $r{subjsec} );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my ($desc, $teacher) = $sth2->fetchrow;
	    $course_or_homeroom = "$desc - $teacher";
	    
	} else { # we have a homeroom;
	    my $homeroom;
	    if ( $r{subjsec} ) {
		$homeroom = $r{subjsec};
		$homeroom =~ s/^HR://; # strip leading HR:
	    } else { # look in his/her student record
		$homeroom = $s{homeroom};
	    }

	    $sth3->execute( $homeroom );
	    if ($DBI::errstr){print $DBI::errstr; die $DBI::errstr;}
	    my ($lname, $fname) = $sth3->fetchrow;
	    $course_or_homeroom = "$homeroom - $fname $lname";
	}

	print qq{<tr><td><a href="$self?student=$s{studnum}&page=1">};
	print qq{<b>$s{lastname}</b>, $s{firstname}</a></td>\n};
	print qq{<td>$r{absdate}</td><td>$r{reason}</td><td>$r{period}</td>\n};

	# Print either subject (if given) or homeroom (if found)
	print qq{<td>$course_or_homeroom</td>\n};
	
	print qq{<td>$lex{Home} 1: $s{hphone1} $lex{Work} 2: $s{wphone1}<br>\n};
	print qq{$lex{Home} 2: $s{hphone2} $lex{Work} 2: $s{wphone2}</td></tr>\n};

    }


    if ($first ) {
	print qq{<h1>$header</h1>\n};
	print qq{<h3>$lex{'No Absences found'}</h3>\n};
	
    } else {
	print qq{</table>\n};
    }

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

    exit;

} # end of showReport



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

    my (%sort, %courses );

    my $sth1 = $dbh->prepare("select description, smdesc, startrptperiod, endrptperiod from subject 
			     where subjsec = ?");
    
    my $sth = $dbh->prepare("select distinct subjsec from attend 
			    where subjsec is not NULL and subjsec != ''");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    
    while ( my $subjsec = $sth->fetchrow ) {
	$sth1->execute($subjsec);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ( $desc, $smdesc, $sterm, $eterm ) = $sth1->fetchrow;
	if ( not $smdesc ) { $smdesc = $desc; };

	my $key = "$smdesc$subjsec";

	if ( not $desc ) { # not a course
	    $key = $subjsec; # not really a subjsec, a homeroom;
	    $courses{$key} = $key;
	} else { # we have a course
	    $courses{$subjsec} = "$smdesc ($sterm-$eterm)";
	}

	$sort{$key} = $subjsec;

    }

    
#    foreach my $key ( sort keys %sort ) { print "K:$key V:$sort{$key}<br>\n"; }
#    foreach my $key ( sort keys %courses ) { print "K:$key V:$courses{$key}<br>\n"; }
    
	    
    # Start Form
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

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

    # View by Student
    print qq{<tr><td class="ra"><input type="submit" value="$lex{View} $lex{'by Student'}"></td>\n};
    print qq{<td><input type="text" name="student" size="25"></td></tr>\n};
    print qq{<tr><td></td><td class="bla">Last,First/Last/Initials/Studnum</td></tr>\n};

    # Divider
    print qq{<tr><td colspan="2"><hr></td></tr>\n};
    
    # View by Date Range
    print qq{<tr><td class="ra">\n};
    print qq{<input type="submit" name="date" value="$lex{View} $lex{'by Date'}"></td>\n};
    
    print qq{<td class="bla">$lex{Start}/$lex{Single} $lex{Date} \n};
    print qq{<input type="text" id="datestart" name="datestart" size="12">\n};
    print qq{<button type="reset" id="start_trigger">...</button></td></tr>\n};
    
    print qq{<tr><td></td><td class="bla">$lex{End} $lex{Date}\n};
    print qq{<input type="text" id="dateend" name="dateend" size="12">\n};
    print qq{<button type="reset" id="end_trigger">...</button> $lex{'Blank=Single Date'}</td></tr>\n};

    # Divider
    print qq{<tr><td colspan="2"><hr></td></tr>\n};

    # View by Course / Homeroom
    print qq{<tr><td class="ra">\n};
    print qq{<input type="submit" value="$lex{View} $lex{'by Course'}/$lex{Homeroom}"></td>\n};

    print qq{<td class="la"><select name="course"><option></option>\n};
    foreach my $key ( sort keys %sort ) {
	my $subjsec = $sort{$key};
	print qq{<option value="$subjsec">$courses{$subjsec}</option>\n};
    }
    print qq{</select></td></tr>\n};
    print qq{</table>\n};
    print qq{</form>\n};
    
    print qq{<script type="text/javascript">
 Calendar.setup({
  inputField  : "datestart",
  ifFormat    : "%Y-%m-%d",
  button      : "start_trigger",
  singleClick : false,
  step : 1
  });
</script>
<script type="text/javascript">
 Calendar.setup({
  inputField  : "dateend",
  ifFormat    : "%Y-%m-%d",
  button      : "end_trigger",
  singleClick : false,
  step : 1
  });
</script>
\n};

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

    exit;
}


