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

#  This file is part of Open Admin for Schools.

#  Subject/Summary Attendance Report 
# Get Start date of current term. Calculate all student's attendance
# per subject (and grand total). Display descending list by all students
#  per class or per total. Data comes first from attendance linked to other
#  tables. Attend is definitive, not eval (which may change).

# Note: $lateUnexcused and $absentUnexcused (line 206) are loaded from admin.conf
#  and MUST be set correctly for this to work...

my %lex = ('Attendance' => 'Attendance',
	   'Not Found' => 'Not Found',
	   'Student' => 'Student',
	   'Course' => 'Course',
	   'Absent' => 'Absent',
	   'Unexcused' => 'Unexcused',
	   'Other' => 'Other',
	   'Lates' => 'Lates',
	   'Red' => 'Red',
	   'Withdrawn' => 'Withdrawn',
	   'Days' => 'Days',
	   'Periods' => 'Periods',
	   'Error' => 'Error',
	   'WD' => 'WD',
	   'Terms' => 'Terms',
	   'Continue' => 'Continue',
	   'Main' => 'Main',

	   );


use DBI;
use CGI;
use Number::Format qw{round};

my $self = 'rptattstudsubj.pl';

# Ratio of Lates Equivalent to 1 Absence.
my $latesEquiv = 3;  # 3 Late = 1 Absence



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


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

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

# Get Additional Comment values from configuration system, in order to skip...
my $sth = $dbh->prepare("select datavalue from conf_system 
  where dataname = 'r_AdditionalComments'");
$sth->execute;
my $datavalue = $sth->fetchrow;
eval $datavalue;
if ( $@ ) {
    print "$lex{Error}: $@<br>\n";
    die "$lex{Error}: $@\n";
}



my $title = "$lex{Course} $lex{Attendance} - $lex{Student}";

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style type="text/css">td { text-align:center; }</style>\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();
}


# Setup Attendance Reasons and Matching Points
my %attendPoints = ();
for my $idx ( 1..@attend ) {
    $attendPoints{"$attend[$idx]"} = $points[$idx];
}


my ($sterm, $eterm) = split('-', $arr{term});


print qq{<p>$lex{Terms}: $sterm &ndash; $eterm \n};
print qq{&nbsp;&nbsp;<span style="color:red">$lex{Red}=$lex{Withdrawn}</span></p>\n};


# First find subjects in this term or term block.
my (@subjects, %subjects);
my $sth = $dbh->prepare("select subjsec, description from subject where
 startrptperiod = ? and endrptperiod = ? order by description");
$sth->execute($sterm, $eterm);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my ( $subjsec, $desc) = $sth->fetchrow ) {

    my ( $subjcode, $dud ) = split(/-/, $subjsec);
    # skip if member of %r_AdditionalComments hash - NEW
    if ( $r_AdditionalComments{$subjsec} or $r_AdditionalComments{$subjcode} ) {
	next; 
    }

    push @subjects, $subjsec; # @subjects gives us sort order
    $subjects{$subjsec} = $desc;
}


# Now find students in those subjects
my $sth1 = $dbh->prepare("select lastname, firstname, grade from student where studnum = ?");
my $sth2 = $dbh->prepare("select lastname, firstname, grade  from studentwd where studnum = ?");

foreach my $subjsec ( @subjects ) {

    
    my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
    $sth->execute( $subjsec );
    if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }

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

	# Get name
	$sth1->execute( $studnum );
	my ($lastname, $firstname, $grade ) = $sth1->fetchrow;
	my $wdflag;

	if ( not $lastname ) { # check in withdrawn.

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

	    $wdflag = 1;
	    if ( not $lastname ) {
		$lastname = $lex{'Not Found'};
	    }
	}
	push @students, "$lastname:$firstname:$studnum:$subjsec:$wdflag:$grade";
    }
}
# now have all students in @students array.

print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
print qq{<tr><th>$lex{Course}</th><th>$lex{Absent}<br>$lex{Unexcused}</th>};
print qq{<th>$lex{Absent}<br>$lex{Other}</th><th>$lex{Lates}</th>};
print qq{<th>$lex{Periods}</th><th>$lex{Days}</th></tr>\n};

my ($currstud, $prevstud, $totalDays);
$currstud = -1;

my $sth3 = $dbh->prepare("select description, smdesc from subject where subjsec = ?");

# Now loop through all students, printing classes and number missed.

foreach my $stud ( sort @students ) {

    my ($lastname, $firstname, $studnum, $subjsec, $wdflag, $grade) = split(':', $stud);

    my $ppd = $g_ppd{ $grade };
    if ( not $ppd ) { $ppd = 1; }

    # Get Subject Name
    my $description;
    $sth3->execute( $subjsec );
    my ($desc, $smdesc) = $sth3->fetchrow;

    if (not $smdesc) {
	$description = substr($desc, 0, 10);
    } else {
	$description = $smdesc;
    }

    if ( $wdflag ){ # withdrawn students
	$lastname = qq{<span style="color:red;">$lastname</span>};
	$firstname = qq{(<b>$lex{WD}</b>) <span style="color:red;">$firstname</span>};
    }

    $prevstud = $currstud;
    $currstud = $studnum;
    if ( $currstud != $prevstud ) { # New student...
	print qq{<tr class="gray"><td colspan="6">$firstname $lastname ($studnum)</td></tr>\n};
    }


    # Get attendance reasons and count;
    my $sth= $dbh->prepare("select distinct reason, count(reason) from attend
      where subjsec = ? and studentid = ? group by reason");
    $sth->execute( $subjsec, $studnum );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

    my ($totalCount, $absentCount, $lateCount, $otherCount);

    while ( my ($reason, $count) = $sth->fetchrow ) {

	if ( $reason eq $absentUnexcused ) {
	    $absentCount = $count;
	} elsif ( $reason eq $lateUnexcused ) {
	    $lateCount = $count;
	} elsif ( $reason =~ $absentString )  { # one of the other absences.
	    $otherCount += $count;
	}
    }



    $totalCount = $otherCount + int( $lateCount / $latesEquiv ) + $absentCount;

    my $dayCount;
    $dayCount = $totalCount / $ppd;
    $dayCount = round( $dayCount, 2);

    # print stuff
    print qq{<tr><td>$description ($subjsec)</td><td>$absentCount</td><td>$otherCount</td>\n};
    print qq{<td>$lateCount</td><td>$totalCount</td><td><b>$dayCount</b></td></tr>\n};
    
}

# Print last of last record
print qq{<b>$totalDays</b></td></tr>\n};


print qq{</table><p>[ <a href="$attpage">$lex{Attendance}</a> ]</p>\n};
print qq{</body></html>\n};




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

    # Find all of the term patterns.
    my $sth = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject
			    order by startrptperiod, endrptperiod");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

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

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

    # Term Select
    print qq{<tr><td class="bra">$lex{Terms}</td><td class="la">};
    print qq{<select name="term"><option></option>\n};
    my %terms = ();
    while ( my ( $startterm, $endterm ) = $sth->fetchrow ) {
	    print qq{<option value="$startterm-$endterm">};
	    print qq{$startterm-$endterm</option>\n};
	    # $terms{$startterm} = 1;
	    # $terms{$endterm} = 1;
    }
    print qq{</select></td></tr>\n};

    # Continue
    print qq{<tr><td colspan="2" class="cn"><input type="submit" value="$lex{Continue}">\n};
    print qq{</td></tr>\n};

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

    exit;

}
