#!/usr/bin/perl
#  Copyright 2001-2016 Leslie Richardson
#  This file is part of Open Admin for Schools.

use DBI;
use CGI;
use Time::JulianDay;

my $studenttable = 'studentall';
# alter this to 'student' if you don't want to see withdrawn students.


my %lex = ('Attendance' => 'Attendance',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Main' => 'Main',
	   'Student' => 'Student',
	   'Date' => 'Date',
	   'Subject' => 'Subject',
	   'Period' => 'Period',
	   'Reason' => 'Reason',
	   'Today' => 'Today',
	   'Yesterday' => 'Yesterday',
	   'Error' => 'Error',
	   'Not Found' => 'Not Found',
	   'Absences' => 'Absences',
	   'Start' => 'Start',
	   'Single' => 'Single',
	   'End' => 'End',
	   'Blank=Single Date' => 'Blank=Single Date',

	   );


my ($sec, $min, $hour, $mday, $mon, $year, $wday, 
 $yday, $iddst) = localtime(time);
$year = $year + 1900;
$mon++;
# $wday++;  # keep 0=Sunday, etc.

my $currdate;
my $today = $currdate = "$year-$mon-$mday";
my $jd = julian_day($year,$mon,$mday);

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

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


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

# Print Page header.
my $title = "$lex{Edit} $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} ) {
    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><a name="top"></a>\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 showStartPage {
#----------------

    # 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};
    print qq{<tr><td colspan="2">};

    # Today
    print qq{<input type="submit" name ="date" value="$lex{Today}">\n};

    # Yesterday
    print qq{<input type="submit" name="date" value="$lex{Yesterday}">\n};

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

    # Divider
    print qq{<tr><td colspan="2"><hr></td></tr>\n};
    
    print qq{<tr><td>\n};
    
    print qq{<input type="submit" name="date" value="Edit Date Range"></td></tr>\n};
    
    print qq{<tr><td class="bra">$lex{Start}/$lex{Single} $lex{Date}</td>\n};
    print qq{<td class="la"><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 class="bra">$lex{End} $lex{Date}</td><td class="la">\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};

    
    print qq{<tr><td colspan="2"><input type="submit" value="Edit Student">\n};
    print qq{<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};

    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;
}





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

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

    if ( $arr{student} ) {
	$student = $arr{student};
	if ($student =~ /\d+/) {  # we have a student number
	    push @studnum, $student; # only a single value
	} else { # we have some text.
	    ($lastname,$firstname)  = split /\,/, $student;
	    $firstname =~ s/^\s*//;
	    $lastname =~ s/^\s*//;
	    if ($lastname and $firstname){ # both entered.
		my $ln = $dbh->quote( $lastname );
		my $fn = $dbh->quote( $firstname );
		$sth = $dbh->prepare("select studnum from $studenttable
                  where lastname = $ln and firstname = $fn ");

	    } elsif ($lastname and not $firstname){ # only lastname (no comma)

		if (length($lastname) == 2){ # search by initials: fi, li.
		    my $fi = $dbh->quote( substr($lastname,0,1).'%' ); 
		    my $li = $dbh->quote( substr($lastname,1,1).'%' );
		    $sth = $dbh->prepare("select studnum from $studenttable
                      where lastname $sql{like} $li and firstname $sql{like} $fi");

		} else {
		    $ln = $dbh->quote( $lastname );
		    $sth = $dbh->prepare("select studnum from $studenttable
                      where lastname = $ln order by firstname");
		}
	    }
	}

	if ( $sth ) {  # sth should be set above in one of 3 places
	    $sth->execute;
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    while ( my $studnum = $sth->fetchrow) {
		push @studnum, $studnum;
	    }
	}

	if ( not @studnum ) { # no records
	    print qq{<h3>$lex{Attendance} $lex{'Not Found'}</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}

	printStudents( @studnum );
	
    }
    # End of student only. 


    # select by date
    if ( $arr{date} ) {

	if ( lc( $arr{date} ) eq lc( $lex{Today} ) ){

	    $sth = $dbh->prepare("select a.*,s.lastname, s.firstname from attend as a
             left join $studenttable as s 
             on a.studentid = s.studnum
             where to_days( a.absdate ) = to_days('$today') 
             order by a.absdate desc, s.lastname, s.firstname, a.period");
	}

	if ( lc( $arr{date}) eq lc( $lex{Yesterday}) ){

	    # Figure out yesterday

	    my $yesterday = $jd - 1;
	    if ( $wday == 1 ) { # If Monday
		$yesterday -= 2; # take away 2 more.
	    }

	    $yesterday = join('-', inverse_julian_day( $yesterday ));

	    $sth = $dbh->prepare("select a.*,s.lastname, s.firstname from attend as a
             left join $studenttable as s 
             on a.studentid = s.studnum
             where to_days( a.absdate ) = to_days('$yesterday') 
             order by a.absdate desc, s.lastname, s.firstname, a.period");

	    $currdate = $yesterday;
	}
    }
    
    if ( $arr{datestart} ) { # We select a date range

	if ( not $arr{dateend} ) {  # only a single date
	    
	    $sth = $dbh->prepare("select a.*,s.lastname, s.firstname from attend as a
            left join $studenttable as s 
            on a.studentid = s.studnum
            where to_days(a.absdate) = to_days('$arr{datestart}')
            order by s.lastname, s.firstname, a.period");
	    
	} else {

	    $sth = $dbh->prepare("select a.*,s.lastname, s.firstname from attend as a
             left join $studenttable as s 
             on a.studentid = s.studnum
             where to_days(a.absdate)>= to_days('$arr{datestart}') and
             to_days( a.absdate ) <= to_days( '$arr{dateend}' )  
             order by a.absdate desc, s.lastname, s.firstname, a.period");
	}

    }


    if ( not $sth ) { # if no dates, then find them ALL.

	$sth = $dbh->prepare("select a.*,s.lastname, s.firstname from attend as a
         left join $studenttable as s 
         on a.studentid = s.studnum
         order by a.absdate desc, s.lastname, s.firstname, a.period");
    }

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


    # print qq{<p>$studentname</p>};

    $sth1 = $dbh->prepare("select description from subject where subjsec = ?"); 

    my $currstud = -1;
    my $first = 1;

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

	if ( $first ) { # start table...
	    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
	    print qq{<tr><th>$lex{Student}</th><th>$lex{Date}</th><th>$lex{Subject}</th>};
	    print qq{<th>$lex{Period}/$lex{Reason}</th><th>$lex{Edit}</th>};
	    print qq{<th>$lex{Delete}</th></tr>\n};
	    $first = 0;
	}


	# Subject Description
	my $desc;
	if ( $ref->{subjsec} ) {
	    $sth1->execute( $ref->{subjsec} );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    $desc = $sth1->fetchrow;
	    $desc = $desc."($ref->{subjsec})";
	}

	$oldstud = $currstud; 
	$currstud = $ref->{studentid};
	if ($currstud != $oldstud){
	    if ($color eq "blue"){ 
		$color = "gray";
	    } else { 
		$color = "blue";} 
	}
	
	print qq{<tr class="$color"><td><b>$ref->{lastname}</b>, $ref->{firstname} };
	print qq{($ref->{studentid})</td>\n};
	print qq{<td>$ref->{absdate}</td><td>$desc</td><td>$ref->{period} - $ref->{reason}</td>\n};

	print qq{<td><a href="atted.pl?id=$ref->{attid}">$lex{Edit}</a></td>\n};
	print qq{<td><a href="attdel.pl?id=$ref->{attid}">$lex{Delete}</a></td></tr>\n};

    }

    if ( $first ) { # no records
	print qq{<h3>$lex{Attendance} $lex{'Not Found'}<br>$currdate</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

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

    exit;

} # end of showReport




#----------------
sub printStudents {
#----------------

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

    # print the anchor line of names
    if ($#studs > 0) { # Display a jump to line
	my $first = 1;
	foreach my $studnum (@studs) {
	    if ($first) { print qq{<p>[ }; $first = 0; } else { 
		print ' | ';
	    }

	    $sth->execute($studnum);
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lastname, $firstname) = $sth->fetchrow;
	    print qq{<a href="#$studnum">$firstname $lastname</a>\n};
	}
	print qq{ ]</p>\n};

    }


    foreach my $studnum ( @studs ) {

	$sth->execute( $studnum );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;


	print qq{<a name="$studnum"></a>\n};
	print qq{<div style="font-size:130%; font-weight:bold; padding:0.8em;">};
	print qq{$firstname $lastname&nbsp;&nbsp;[ <a href="#top">Top</a> ]</div>\n};

	# Get Attendance
	my $sth1 = $dbh->prepare("select attid, absdate, reason, period, subjsec from attend 
          where studentid = ? order by absdate desc, period");
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	my $sth2 = $dbh->prepare("select description from subject where subjsec = ?");
	my $currdate = -1;
	my $prevdate;
	my $first = 1;


	while ( my ( $id, $absdate, $reason, $period, $subjsec ) = $sth1->fetchrow ) {

	    if ( $first ) { # print table
		print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
		print qq{<tr><th>$lex{Date}</th><th>$lex{Subject}</th>};
		print qq{<th>$lex{Period}/$lex{Reason}</th><th>$lex{Edit}</th>};
		print qq{<th>$lex{Delete}</th></tr>\n};
		$first = 0;
	    }


	    my $desc; # subjsec description
	    if ( $subjsec ) {
		$sth2->execute( $subjsec );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		$desc = $sth2->fetchrow;
	    }

	    $prevdate = $currdate; 
	    $currdate = $absdate;

	    if ( $prevdate ne $currdate ) { # color switch
		if ( not $attrCol ) { 
		    $attrCol = qq{style="background-color:#D8D8D8;"};
		} 
	    }

	    print qq{<tr $attrCol><td>$absdate</td><td>$desc $subjsec</td>};
	    print qq{<td>$period : $reason</td>\n};
	    print qq{<td><a href="atted.pl?id=$id">$lex{Edit}</a></td>\n};
	    print qq{<td><a href="attdel.pl?id=$id">$lex{Delete}</a></td></tr>\n};

	} # End of Attendance Records

	if ( $first ) {
	    print qq{<p>$lex{Absences} $lex{'Not Found'}</p>\n};
	} else {
	    print qq{</table>\n};
	}
    }

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

    exit;

} # End of printStudents
