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

#  This file is part of Open Admin for Schools.

# Get Year-Month and export attendance

my %lex = ('Error' => 'Error',
	   'Main' => 'Main',
	   'Attendance' => 'Attendance',
	   'Month' => 'Month',
	   'Continue' => 'Continue',
	   'Select' => 'Select',
	   'Reason' => 'Reason',
	   'Missing' => 'Missing',
	   'Value' => 'Value',
	   'WD' => 'WD',
	   'Period' => 'Period',
	   'Database' => 'Database',

	   );

my $self = 'sdsexport.pl';


use DBI;
use CGI;
use Cwd;
use Time::JulianDay;
use Number::Format qw(:all);

use Text::CSV_XS;


# Get current dir so know what path for config files.
my $configpath;
my $teachermode;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $teachermode = 1;
    $configpath = '..'; # go back one to get to etc.
} else {
    $configpath = '../..'; # go back two to get to etc.
}

# only load passwords and users
eval require "$configpath/etc/admin.conf.root";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

eval require "$configpath/lib/libattend.pl";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

eval require "$configpath/lib/libschedule.pl";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

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


# Load Configuration Variables;
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}

=head
my $sth = $dbh->prepare("select id, datavalue from conf_system where dataname = ?");
foreach my $field ( css ) {
    $sth->execute( $field );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while (	my ($id, $datavalue) = $sth->fetchrow ) {
	eval $datavalue;
	if ( $@ ) {
	    print "$lex{Error}: $@<br>\n";
	    die "$lex{Error}: $@\n";
	}
    }
}
=cut


# Teachermode
if ( $teachermode ) { # running on teacher site
    $css = $tchcss;
    $homepage = $tchpage;
    $downloaddir = $tchdownloaddir;
    $webdownloaddir = $tchwebdownloaddir;
}

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


# Set Date
my @tim = localtime(time);
my $cyear = @tim[5] + 1900;
my $cmonth = @tim[4] + 1;
my $cday = @tim[3];
my $currdate = "$cyear-$cmonth-$cday";
my $currjd = julian_day( split('-', $currdate) );
my $curryrmo = "$cyear-$cmonth";


# Load Remote database
my $dbhr;
if ( $arr{db} ) { # from start page
    my $db = $arr{db};
    my $dsn = "DBI:mysql:database=$db;host=$remotehost";
    $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword);
}


my $title = "$lex{Attendance} Export - SDS";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style>th.dr, td.dr { border-right-style:double; border-right-width:3px; }</style>\n};

print qq{$chartype\n</head><body style="padding:1em;">\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} or $arr{page} == 2 ) {  # 2 value for remote processing.
    showStartPage();

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

    if ( $arr{previousyear} ) {
	selectPreviousYear();
    }
    
    showReport();
}


#---------------------
sub selectPreviousYear {
#---------------------

    # Get remote databases
    my $remotedbase = 'information_schema';
    my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost";
    my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword);


    my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my %remotedb;


    while ( my $db = $sth->fetchrow ) {
	if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; }

#	print qq{DB:$db<br>\n};
	if ( $db =~ m/$dbase/ ) {
#	    print "Match! $dbase - $db<br>\n";
	    $remotedb{$db} = 1;
	}

    }

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

    print qq{<table cellpadding="3" cellspacing="0" border="0" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};

    print qq{<tr><td class="bcn">$lex{Select} $lex{Database}</td></tr>\n};
    
    foreach my $db ( sort keys %remotedb ) {
	my $datab = $db;
	$datab =~ s/^$dbase//;
	if ( $datab =~ m/^20.+/ ) { # older format 2016 (ie. 2015-2016)
	    my $prevyr = $datab - 1;
	    $datab = "$prevyr-". $datab;
	} else { # newer format (ie. 1617
	    my @t = split('', $datab);
	    $datab = "20$t[0]$t[1]". '-' . "20$t[2]$t[3]";
	}
	    
	print qq{<tr><td class="la"><input type="radio" name="db" value="$db">$datab ($db)</td></tr>\n};
    }


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

    print "</body></html>\n";

    exit;

} # end of selectPreviousYear




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

    # Setup Year-Months.
    my @months;
    my %months;


    my $database = $dbase;
    if ( $dbhr ) { # we have a remote database instead
	$database = $dbhr;

	# load configuration values for this database.
	my $sth = $dbhr->prepare("select id, datavalue from conf_system where filename = 'admin'");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while (	my ($id, $datavalue) = $sth->fetchrow ) {
	    eval $datavalue;
	    if ( $@ ) {
		print "$lex{Error}: $@<br>\n";
		die "$lex{Error}: $@\n";
	    }
	}
    }
    

    my ($sy, $sm, $sd) = split('-', $schoolstart); # schoolstart is global var from config.
    my $yrmo = "$sy-$sm";
    push @months, $yrmo;
    $months{$yrmo} = "$s_month[$sm]-$sy";

    for my $i (1..10) {
	my $mo = $sm + $i;
	my $yr = $sy;
	if ( $mo > 12 ) {
	    $mo = $mo - 12;
	    $yr++;
	}
	my $yrmo = "$yr-$mo";
	push @months, $yrmo;
	$months{$yrmo} = "$s_month[$mo]-$yr";
    }


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

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

    # Get Month
    print qq{<tr><td class="bra">Select $lex{Month}</td>};
    print qq{<td class="la"><select name="month"><option></option>\n}; 

    foreach my $mo ( @months ) {
	print qq{<option value="$mo">$months{$mo}</option>\n};
    }
    print qq{</select></td></tr>\n};

    if ( not $dbhr ) { # display only if using local db
    
	# OR
	print qq{<tr><td class="bra">OR</td>};
	print qq{<td class="la"></td></tr>\n};
    

	# Previous Year?
	print qq{<tr><td class="bra">Previous Year?</td>};
	print qq{<td class="la"><input type="checkbox" name="previousyear" value="1"></td></tr>\n};

    } else { # pass the remote db along
	print qq{<tr><td><input type="hidden" name="db" value="$arr{db}"></td></tr>\n};
    }
	

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

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

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

    exit;

}


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

    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    
    # Check for missing values.
    if ( not $arr{month} ) {
	print "<h3>$lex{Missing} $lex{Value}</h3>\n";
	print "</body></html>\n";
	exit;
    }


    # load configuration values for remote database, if selected.
    if ( $dbhr ) { # we have a remote database instead

	# load configuration values for this database.
	my $sth = $dbhr->prepare("select id, datavalue from conf_system where filename = 'admin'");
	$sth->execute;
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while (	my ($id, $datavalue) = $sth->fetchrow ) {
	    eval $datavalue;
	    if ( $@ ) {
		print "$lex{Error}: $@<br>\n";
		die "$lex{Error}: $@\n";
	    }
	}

	$dbh = $dbhr; # all access uses the remote database.
	
    }

    
    my $studtable = 'studentall';


    # Check Year, Month of attendance
    my ($year, $month) = split('-', $arr{month});
    my $monthfilename = uc($s_month[$month]). $year;
    
    print qq{<p>Month:$monthfilename</p>\n};
    
    if ( ($year == $cyear and $month > $cmonth ) or $year > $cyear ) { #Error
	print "<h3>Month Not Allowed</h3>\n";
	print "</body></html>\n";
	exit;
    }
    my $monthstart = $arr{month}. qq{-01};
    
    print "Starting Date: $monthstart<br>\n";


    # Get the grades for each attendance entry method.
    my (%elemgrades, %secgrades);
    foreach my $gr ( keys %g_AttendanceEntryMethod ) {
    	if ( $g_AttendanceEntryMethod{$gr} eq 'homeroom' ) {
	    $elemgrades{$gr} = 1;
	} elsif ( $g_AttendanceEntryMethod{$gr} eq 'subject' ) {
	    $secgrades{$gr} = 1;
	} else {
	    print "No attendance entry method found for this grade: $gr<br>\n";
	    print "Exiting<br>\n";
	    exit;
	}
    }

    if ( $elemgrades{'PK'} ) { delete $elemgrades{'PK'}; }


#    foreach my $gr ( sort keys %elemgrades ) { print "$gr "; }
#    print "<br><br>SEC:\n";
#    foreach my $gr ( sort keys %secgrades ) { print "$gr "; }
#    print "<br>\n";


    # Now get the students for this year, month
    my (%elemstudents, %secstudents);
    my $sth = $dbh->prepare("select studnum, grade from student");
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($studnum, $grade ) = $sth->fetchrow ) {
	if ( $elemgrades{$grade} ) { # put in elementary file
	    $elemstudents{$studnum} = 1;
	} elsif ( $secgrades{$grade} ) { # pu in secondary file.
	    $secstudents{$studnum} = 1;
	} else { # skipping
	    print qq{Skipping Grade:$grade SN:$studnum<br>\n};
	}
    }


    my $sth1 = $dbh->prepare("select grade from studentall where studnum = ?");

    
    # Now look for withdraw transfers since that month start to present;
    my $sth = $dbh->prepare("select lastname, firstname, studnum from transfer where type = 'withdraw' and 
       to_days(date) >= to_days('$monthstart')");
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) {

	# Get Grade
	$sth1->execute($studnum);
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $grade = $sth1->fetchrow;

	if ( $elemgrades{$grade} ) { # put in elementary file
	    $elemstudents{$studnum} = 1;
	} elsif ( $secgrades{$grade} ) { # put in secondary file.
	    $secstudents{$studnum} = 1;
	} else { # skipping
	    print qq{Skipping Grade:$grade SN:$studnum<br>\n};
	}
    }
    
    # We now have all students that were present at start of the month
    # of interest in 2 hashes, elementary and secondary;
    

    # Get the Days Open in the month and also Days Closed (to indicate school closed). We also use PPD for this grade.
    my %daysclosed;
    my $daysclosed; # scalar to count days closed
    my $sth = $dbh->prepare("select date, dayfraction from dates where month(date) = $month and year(date) = $year"); 
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($dt, $fraction ) = $sth->fetchrow ) {
	my ($y,$m,$d) = split('-',$dt);
	$d =~ s/^0//; # strip leading zeros.
	$daysclosed{$d} = $fraction;
	$daysclosed += $fraction;
    }
    # foreach my $key ( sort keys %daysclosed ) { print "K:$key V:$daysclosed{$key}<br>\n"; }

    
    # Get the number of days in the month. Set $lastday value
    my $nextmonth = $month +1;
    my $nextyear = $year;
    if ( $nextmonth > 12 ) {
	$nextmonth = 1;
	$nextyear++;
    }
    my $nextfirstdayjd = julian_day( $nextyear, $nextmonth, '1');
    my $lastday;
    if ( $currjd < ( $nextfirstdayjd - 1 ) ) {
	my @temp = inverse_julian_day( $currjd );
	$lastday = $temp[2];
    } else {
	my @temp  = inverse_julian_day( $nextfirstdayjd - 1 );
	$lastday = $temp[2];
    }

    
    my $monthstartjd = julian_day($year, $month, '1');
    my $monthendjd = julian_day($year, $month, $lastday);


    
    # Get Course Descriptions and Teachers - complete.
    my (%coursename, %teachercourse);
    my $sth = $dbh->prepare("select description, teacher, subjsec from subject"); # where subjsec = ? ");

    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ( $desc, $userid, $subjsec ) = $sth->fetchrow ) {
	$coursename{$subjsec} = $desc;
	$teachercourse{$subjsec} = $userid; # get teacher userid from the course (subjsec)
    }
    # foreach my $key ( sort keys %coursename ) { print "K:$key V:$coursename{$key}<br>\n"; }
    # foreach my $key ( sort keys %teachercourse ) { print "K:$key V:$teachercourse{$key}<br>\n"; }


    # Create a hash for day in cycle for this month.
    my %dayincycle;
    for my $d ( 1..$lastday ) {
	my $dic = findDayInCycle( "$year-$month-$d", $dbh );
	#print "Day of Month:$d  Cycle Day:$dic<br>\n";
	$dayincycle{$d} = $dic;
    }

    

    # Get the type of attendance done (homeroom or subject).
    my %teacherattendance;
    my %currterm; # structure to hold term based on grade and and dom (day of month)
    my @terms; # the terms for this month;
    my @grades; # the grades of this group of students.

 
   
    my $elemfile = "ELEM\_$schoolnumber\_$monthfilename\_$$.csv";
    
    my $secfile = "SEC\_$schoolnumber\_$monthfilename\_$$.csv";
    my $filetype;



    # open file handle
    open(my $fh, ">",$elemfile);
    
    my %students = %elemstudents;
    my $attmethod = 'homeroom';

    my $csv = Text::CSV_XS->new( {binary => 1} );
	
    my %grades = %elemgrades;
    
	
    # Get Teacher Attendance Entry for the Month; populate teacherattendance
    my $sth = $dbh->prepare("select * from tattend where month(attdate) = $month and year(attdate) = $year"); 
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    # prepare to get homeroom for a teacher.
    my $sth1 = $dbh->prepare("select field_value from staff_multi where field_name = 'homeroom' and userid = ?");

    while ( my $ref = $sth->fetchrow_hashref ) {
	my ($y,$m,$day) = split('-', $ref->{attdate});
	$day =~ s/^0//; # strip leading zero
	my (@per, @subj);
	    
	my $userid = $ref->{userid};
	my $period = $ref->{periods};
	if ($period =~ m/,/ ) { # if period contains commas, then we have to split
	    @per = split(',',$period);
	} else {
	    push @per, $period;
	}

	my $subject = $ref->{subjects};
	if ( not $subject ) { # look up homeroom and populate with that instead
	    $sth1->execute($userid);
	    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $hr = $sth1->fetchrow;
	    if ( not $hr) { 
		print qq{<h4>$lex{Error}: Missing Subject/Homeroom field for userid:$userid Day:$day</h4>\n};
	    } else { # populate the subject field;
		$subject = 'HR:'.$hr;
	    }
	}

	foreach my $per ( @per ) {
	    $teacherattendance{$day}{$per}{$subject} = $userid;  # day, period, subject should be unique.
	}

    }



    # Find DOW of first of month
    my %weekend; # stores weekend days in month
    my $firstsunday;
    
    my $firstjd = julian_day( $year, $month, '1');
    my $lastjd = julian_day( $year, $month, $lastday);
    
    my $julianbase = $firstjd - 1;
    my $daysopen; # first just gross days; take away closed days later
    for my $d ( 1..$lastday ) {
	my $jd = $julianbase + $d;
	my $dow = day_of_week( $jd );
	if ( $dow == 0 or $dow == 6 ) {
	    $weekend{$d} = $dow;
	} else { # open
	    $daysopen++;
	}
    }
    
    $daysopen -= $daysclosed;

    # print "Days Open: $daysopen Closed:$daysclosed<br>\n";


    # SETUP for STUDENT LOOP
    # get attendance values
    my $sth1 = $dbh->prepare("select * from attend where month(absdate) = $month and year(absdate) = $year 
      and studentid = ?");

    # wd status check
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    # get enrollment changes (ie. transfers)
    my $sth3 = $dbh->prepare("select date, type from transfer where month(date) = $month and year(date) = $year 
        and studnum = ? order by date");

    # Get Student Course Enrollments
    my $sth4 = $dbh->prepare("select subjcode from eval where studnum = ? and term = ?");

    # Get student timetable values
    my $sth5 = $dbh->prepare("select day,period from schedat where subjsec = ? and term = ?");

    # Get student demographic data
    my $sth10 = $dbh->prepare("select lastname, firstname, grade, homeroom, provnum, birthdate 
      from studentall where studnum = ?");

   

	
    # STUDENT LOOP
    foreach my $studnum ( keys %students ) {

	# Get Student Info
	$sth10->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname, $grade, $homeroom, $provnum, $birthdate) = $sth10->fetchrow;
	my $name = "<b>$lastname</b>, $firstname";

	my $ppd = $g_ppd{$grade};


	# Get Enrollment for the year data.
	my @enrolblocks = findEnrollmentBlocks($studnum, $schoolstart, $schoolend, $dbh);
	my ($enrolstart, $enrolend );  # used to write to the CSV file
	foreach my $blockref ( @enrolblocks ) {
	    my $estartjd = julian_day( split('-', $blockref->{start} ));
	    # my ($sy,$sm,$sd) = split('-', $blockref->{start} );
	    my $eendjd = julian_day( split('-', $blockref->{end} ));

	    # if we have an enrollment block around the first of the month, then use these values for the CSV
	    if ( $estartjd <= $firstjd and $eendjd >= $firstjd or
		 $estartjd > $firstjd and $estartjd <= $lastjd ) {  # starts within the month
		$enrolstart = $blockref->{start};
		$enrolend = $blockref->{end};
		last;
	    }
	}

	if ( not $enrolstart ) {
	    $enrolstart = $schoolstart;
	    $enrolend = $schoolend;
	}
	    
	

	# Get Attendance Data
	my %att;
	$sth1->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	
	while ( my $r = $sth1->fetchrow_hashref ) {
	    my ($y,$m,$day) = split('-', $r->{absdate} );
	    $day =~ s/^0//;
	    my $period = $r->{period};
	
	    $att{$day}{$period}{'reason'} = $r->{reason};
	    $att{$day}{$period}{'subjsec'} = $r->{subjsec};
	}


	# Get wd status: current(curr) or withdrawn(wd)
	my $wdflag;
	$sth2->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ecount = $sth2->fetchrow;
	if ( $ecount ) { $wdflag = 1; } # flag set (==1) is withdrawn;

	    
	# Get Enrollment Data
	my %enrol;
	$sth3->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($date, $type) = $sth3->fetchrow ) {
	    my ($y,$m,$day) = split('-', $date );
	    $day =~ s/^0//; # strip leading zero
	    if ( exists $enrol{$day} ) { 
		print "<h3>Enrollment Error: $name ($studnum) - $date</h3>\n";
	    }
	    $enrol{$day} = $type;
	}

	    
	# Set Initial Enrollment Status value ($estatus)
	my $estatus;
	if ( not %enrol ) { # no enrollment changes
	    if ( not $wdflag ) { $estatus = 1; }
	} else { # we have enrollment changes.
	    my @tmp = sort {$a <=> $b} keys %enrol;
	    my $etype = $enrol{$tmp[0]}; # first enrollment change value in the month;
	    if ( $etype eq 'withdraw' ) {
		$estatus = 1; # must have been enrolled at start of the month.
		# Note: even if withdrawn on the 1st, he/she would have been there for that day.
		# If type is enrol, then she/he must have not been enrolled at start of the month.
	    }
	}

	my $wd;
	if ( $wdflag ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">$lex{WD}</span>};
	}

	my ( $periodspresent, $daysenrolled, $attendclosure ); #attendclose:
	    
	# DAY LOOP - loop through the month
	foreach my $day ( 1..$lastday ) {
	    if ( exists $weekend{$day} ) { next; }

	    if ( $daysclosed{$day} > 0.99 ) {
		
		# also watch for enrollment changes ($estatus)
		if ( $enrol{$day} eq 'withdraw' ) { # status change
		    $estatus = 0;
		}
		if ( $enrol{$day} eq 'enrol' ) { # status change
		    $estatus = 1;
		}

		next;
	    } 


	    # update enrollment status ($estatus), if necessary
	    if ( $enrol{$day} eq 'enrol' ) { # status change
		$estatus = 1;
	    }
	    
	    if ( not $estatus ) { # not enrolled
		# print qq{<td style="background-color:$notenrolledcolor;" class="bcn" colspan="$ppd"></td>\n};
		next;
	    }

	    # If here, then student is enrolled;
	    $daysenrolled += (1 - $daysclosed{$day} );

	    
	    my $dayincycle = $dayincycle{$day}; # used for subject attendance
	    my $currterm = $currterm{$grade}{$day}; # grade of student, day of month

	    # the part of day absent cannot be greater than the part of the day open (ie. 1-daysclosed{$day})

	    if ( $daysclosed{$day} ) { # we have a partial day closure; full day closed is skipped above.
		my $temp; # find out how many periods marked absent.
		for my $per ( 1..$ppd ) {
		    my $reason = $att{$day}{$per}{'reason'};
		    if ( $reason =~ m/Absent/ ) {  # add to both enrolled and absent.
			my $subjsec = $studenttimetable{$currterm}{$dayincycle}{$per};
			$crsenrol{$subjsec}++;
			$crsabsent{$subjsec}++;
			
			$temp++;  # not really used anymore
		    }
		}

		
		my $dayfractionabsent = $temp / $ppd;
		if ( $dayfractionabsent > $daysclosed{$day} ) {
		    #print "Too much absent! Day:$day Student:$studnum<br>\n";
		    $attendclosure += (1 - $dayfractionabsent);
		} else {
		    $attendclosure += $daysclosed{$day};
		}
	    }


	
	    # PERIOD LOOP - loop through all periods
	    for my $per ( 1..$ppd ) {


		my ($subject, $late);

		my $val;


		my $hrsubject = 'HR:'.$arr{homeroom}; # what will be in the 'subject' field of array
		if ( $teacherattendance{$day}{$per}{$hrsubject} ){
		    $val = qq{<td $partclosedstyle $class>P</td>}; 
		} else {
		    $missingatt{$day} = 1; # missing attendance
		    $val = qq{<td $partclosedstyle title="No Attendance Entry:$teachername{$homeroomteacher} }.
			qq{($homeroomteacher)" $class><b>NE</b></td>};
		    $statsflag = 0;
		}

		    
		if ( $reason = $att{$day}{$per}{'reason'} ) { # if we have a record for that day and period.
		    my $rsn = q{NA};
		    if ( $reason =~ m/Absent/ ) {
			$rsn = 'A';
		    } elsif ( $reason =~ m/Late/ ) {
			$rsn = 'L';
			$periodspresent++; # still consider them present; could add a variable and check minutes late.
		    }
		    my $attid = $att{$day}{$per}{'id'};
		    $val = qq{<td title="$lex{Reason}:$reason $subject $late $lex{Period}:$per" }.
			    qq{style="font-weight:bold;$partclosed" $class>}.
			    qq{<a href="atted.pl?id=$attid" style="color:red;">$rsn</a></td>};

		} else { # child was present
		    if ( not $nocourseenrollment ) { # if enrolled in a course (doesn't affect homeroom)
			$periodspresent++;
		    }
		}

	    } # end of period loop


	    # update enrollment status ($estatus), if necessary
	    if ( $enrol{$day} eq 'withdraw' ) { # status change
		$estatus = 0;
	    }

	} # end of day loop

	    
	my $dayspresent = format_number( ($periodspresent / $ppd) - $attendclosure ,1,1);
	if ( $dayspresent < 0 ) { $dayspresent = 0; }
	my $daysenrolled = format_number( $daysenrolled, 1, 1);

	my $daysabsent = $daysenrolled - $dayspresent;
	if ( $daysabsent < 0 ) { $daysabsent = 0; }


	# Convert date format
	$birthdate = convertDate($birthdate);
	$enrolstart = convertDate($enrolstart);
	$enrolend = convertDate($enrolend);

	
	my @vals;
	push @vals, $schoolnumber, $schoolnumber, $provnum, $birthdate, $enrolstart, $enrolend, 
	$grade, $daysenrolled, $daysabsent;  # removed blank before days enrolled for course, empty

	
	# WRITE CSV DATA HEre.
	if ($csv->combine( @vals )) {
	    my $record = $csv->string;
	    print $fh $record, "\r\n";
	} else {
	    my $err = $csv->error_input;
	    print qq{CSV Join Failed:$err\n\n};
	}

    
    } # end of student loop

    close $fh;
    # end of elem grades


    
    
    
    # START of SECONDARY
    open(my $fh, ">",$secfile);
    my %students = %secstudents;
    my $attmethod = 'subject';

    my $csv = Text::CSV_XS->new( {binary => 1} );

    my %grades = %secgrades;
    if ( not %grades ) { next; } # skip any missing hash group.

    my @grades;
    foreach my $gr ( sort {$a <=> $b} keys %grades ) {
	push @grades, $gr;
    }

    
    # Now find matching tracks for grade(s).
    my @tracks;
    foreach my $gr ( @grades ) {
	push @tracks, $g_MTrackTermType{$gr};
    }

	
    # Tempterm is needed since we find out dates in a 2 step process, ending up with data in currterm hash.
    my ( %tempterm ); # multiple dimensions; $tempterm{grade}{dom}
    # = term; tempterm holds start:end pair
    # instead of dom.
	
    # Loop through all terms of all tracks and get start/end dates, and populate %tempterm;
    foreach my $gr ( @grades ) {
	my $trk = $g_MTrackTermType{$gr};
	my %temp = %{$g_MTrackTerm{$trk}};
	foreach my $term ( sort keys %temp ) {
	    my $start = $temp{$term}{start};
	    my $end = $temp{$term}{end};
	    
	    # Check using monthstartjd, monthendjd;
	    my $startjd = julian_day( split('-', $start));
	    my $endjd = julian_day( split('-', $end));
	    if ( $startjd < $monthstartjd and $endjd > $monthendjd ) { # term straddles this month
		$tempterm{$gr}{"1:$lastday"} = $term;
		    
	    } elsif ( $startjd >= $monthstartjd and $startjd <= $monthendjd ) { # term starts sometime
		my ( $sy, $sm, $sd ) = split('-', $start);
		$tempterm{$gr}{"$sd:$lastday"} = $term;

	    } elsif ( $endjd >= $monthstartjd and $endjd <= $monthendjd ) {  # term ends in this month.
		my ( $ey, $em, $ed ) = split('-', $end);
		$tempterm{$gr}{"1:$ed"} = $term;
	    }
	}
    }

	
    # populate the %currterm hash, so we know what term it is for any grade on any day this month.
    foreach my $gr ( keys %tempterm ) {
	foreach my $pair ( keys %{ $tempterm{$gr}} ) {
	    my ( $start, $end ) = split(':', $pair);
	    $start =~ s/^0//;  # strip any leading zeros
	    $end =~ s/^0//;
	    for my $day ( $start..$end ) {
		$currterm{$gr}{$day} = $tempterm{$gr}{$pair};
	    }
	}
    }  

=head  # Testing

	foreach my $gr ( keys %tempterm ) {
	    my %temp = %{$tempterm{$gr}};
	    foreach my $day ( sort {$a <=> $b} keys %temp ) {
		print "TEMP Grade|$gr Day|$day Term: $temp{$day}<br>\n";
	    }
	}  

    # Test 
    foreach my $gr ( keys %currterm ) {
	my %temp = %{$currterm{$gr}};
	foreach my $day ( sort {$a <=> $b} keys %temp ) {
	    print "Grade|$gr Day|$day Term: $temp{$day}<br>\n";
	}
    }  
=cut


	
    # Get Teacher Attendance Entry for the Month; populate teacherattendance
    my $sth = $dbh->prepare("select * from tattend where month(attdate) = $month and year(attdate) = $year"); 
    $sth->execute;
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

    
    while ( my $ref = $sth->fetchrow_hashref ) {
	my ($y,$m,$day) = split('-', $ref->{attdate});
	$day =~ s/^0//; # strip leading zero
	my (@per, @subj);
	    
	my $userid = $ref->{userid};
	my $period = $ref->{periods};
	my $subject = $ref->{subjects};
	
	$teacherattendance{$day}{$per}{$subject} = $userid;  # day, period, subject should be unique.

    }



    # Find DOW of first of month
    my %weekend; # stores weekend days in month
    my $firstsunday;
    my $firstjd = julian_day( $year, $month, '1');
    my $julianbase = $firstjd - 1;
    my $daysopen; # first just gross days; take away closed days later
    for my $d ( 1..$lastday ) {
	my $jd = $julianbase + $d;
	my $dow = day_of_week( $jd );
	if ( $dow == 0 or $dow == 6 ) {
	    $weekend{$d} = $dow;
	} else { # open
	    $daysopen++;
	}
    }
    
    $daysopen -= $daysclosed;

    # print "Days Open: $daysopen Closed:$daysclosed<br>\n";


    # SETUP for STUDENT LOOP
    # get attendance values
    my $sth1 = $dbh->prepare("select * from attend where month(absdate) = $month and year(absdate) = $year 
       and studentid = ?");

    # wd status check
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    # get enrollment changes (ie. transfers)
    my $sth3 = $dbh->prepare("select date, type from transfer where month(date) = $month and year(date) = $year 
      and studnum = ? order by date");

    # Get Student Course Enrollments
    my $sth4 = $dbh->prepare("select subjcode from eval where studnum = ? and term = ?");

    # Get student timetable values
    my $sth5 = $dbh->prepare("select day,period from schedat where subjsec = ? and term = ?");

    # Get student demographic data
    my $sth10 = $dbh->prepare("select lastname, firstname, grade, homeroom, provnum, birthdate from studentall 
      where studnum = ?");


	
    # STUDENT LOOP
    foreach my $studnum ( keys %students ) {

	# Get Student Info
	$sth10->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname, $grade, $homeroom, $provnum, $birthdate) = $sth10->fetchrow;
	my $name = "<b>$lastname</b>, $firstname";

	my $ppd = $g_ppd{$grade};

    
	my %trms; # get current terms for this student;
	foreach my $d ( 1..$lastday ) {
	    my $t = $currterm{$grade}{$d};
	    $trms{$t} = 1;
	}

	my $statsflag = 1; # print the attendance stats in final
		       # column IF we have all teacher
		       # attendance entries done.

	my %studenttimetable;
	# get student timetable:  term/day/period/ = subjsec;

	# First get enrolled subjects, then get timetable values for those subjects.
	foreach my $trm (sort keys %trms) { # loop over all terms this month for this student
	    # Get enrolled courses
	    $sth4->execute($studnum, $trm);
	    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    while ( my $subjsec = $sth4->fetchrow ) {
		# Get day/periods
		$sth5->execute($subjsec, $trm);
		if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		while ( my ($day,$period) = $sth5->fetchrow ) {
		    $studenttimetable{$trm}{$day}{$period} = $subjsec;
		}
	    }
	} # end of getting student timetable, for subject attendance.


	# Get Enrollment for the year data. NOTE: We're only taking
	# the FIRST HIT enrollment block that falls within the month
	# of interest. If there are more then we don't care for the
	# record, since the rest is handled correctly for courses and
	# periods enrolled and periods absent.
	
	my @enrolblocks = findEnrollmentBlocks($studnum, $schoolstart, $schoolend, $dbh);
	
	my ($enrolstart, $enrolend );  # used to write to the CSV file
	
	foreach my $blockref ( @enrolblocks ) {
	    my $estartjd = julian_day( split('-', $blockref->{start} ));
	    # my ($sy,$sm,$sd) = split('-', $blockref->{start} );
	    my $eendjd = julian_day( split('-', $blockref->{end} ));

	    # if we have an enrollment block around the first of the month, then use these values for the CSV
	    if ( $estartjd <= $firstjd and $eendjd >= $firstjd or
		 $estartjd > $firstjd and $estartjd <= $lastjd ) {  # starts within the month
		$enrolstart = $blockref->{start};
		$enrolend = $blockref->{end};
		last;
	    }
	}

	if ( not $enrolstart ) {
	    $enrolstart = $schoolstart;
	    $enrolend = $schoolend;
	}

	
	# Get Attendance Data for the student.
	my %att;
	$sth1->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    
	while ( my $r = $sth1->fetchrow_hashref ) {
	    my ($y,$m,$day) = split('-', $r->{absdate} );
	    $day =~ s/^0//;
	    my $period = $r->{period};
	
	    $att{$day}{$period}{'reason'} = $r->{reason};
	    $att{$day}{$period}{'subjsec'} = $r->{subjsec};
	    $att{$day}{$period}{'late'} = $r->{late};
	    $att{$day}{$period}{'id'} = $r->{attid};
	}


	# Get wd status: current(curr) or withdrawn(wd)
	my $wdflag;
	$sth2->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ecount = $sth2->fetchrow;
	if ( $ecount ) { $wdflag = 1; } # flag set (==1) is withdrawn;

	    
	# Get Enrollment Data
	my %enrol;
	$sth3->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($date, $type) = $sth3->fetchrow ) {
	    my ($y,$m,$day) = split('-', $date );
	    $day =~ s/^0//; # strip leading zero
	    if ( exists $enrol{$day} ) { 
		print "<h3>Enrollment Error: $name ($studnum) - $date</h3>\n";
	    }
	    $enrol{$day} = $type;
	}

	    
	# Set Initial Enrollment Status value ($estatus)
	my $estatus;
	if ( not %enrol ) { # no enrollment changes
	    if ( not $wdflag ) { $estatus = 1; }
	} else { # we have enrollment changes.
	    my @tmp = sort {$a <=> $b} keys %enrol;
	    my $etype = $enrol{$tmp[0]}; # first enrollment change value in the month;
	    if ( $etype eq 'withdraw' ) {
		$estatus = 1; # must have been enrolled at start of the month.
		# Note: even if withdrawn on the 1st, he/she would have been there for that day.
		# If type is enrol, then she/he must have not been enrolled at start of the month.
	    }
	}

	my $wd;
	if ( $wdflag ) {
	    $wd = qq{<span style="color:red;font-weight:bold;">$lex{WD}</span>};
	}

	my ( $periodspresent, $daysenrolled, $attendclosure );
	    
	my (%crsenrol, %crsabsent); # periods for each  
	

	# DAY LOOP - loop through the month
	foreach my $day ( 1..$lastday ) {
	    if ( exists $weekend{$day} ) { next; }

	    if ( $daysclosed{$day} > 0.99 ) {
		    
		# also watch for enrollment changes ($estatus)
		if ( $enrol{$day} eq 'withdraw' ) { # status change
		    $estatus = 0;
		}
		if ( $enrol{$day} eq 'enrol' ) { # status change
		    $estatus = 1;
		}
		next; #day
	    } 


	    # update enrollment status ($estatus), if necessary
	    if ( $enrol{$day} eq 'enrol' ) { # status change
		$estatus = 1;
	    }
	    if ( not $estatus ) { # not enrolled
		next;
	    }

	    # If here, then student is enrolled;
	    $daysenrolled += (1 - $daysclosed{$day} );

	    
	    my $dayincycle = $dayincycle{$day}; # used for subject attendance
	    my $currterm = $currterm{$grade}{$day}; # grade of student, day of month

	    
	    # the part of day absent cannot be greater than the part of the day open (ie. 1-daysclosed{$day})
	    if ( $daysclosed{$day} ) { # we have a partial day closure; full day closed is skipped above.
		my $temp; # find out how many periods marked absent.
		for my $per ( 1..$ppd ) {
		    my $reason = $att{$day}{$per}{'reason'};
		    if ( $reason =~ m/Absent/ ) {  # then update both (abs,enrol) for the course
			my $subjsec = $studenttimetable{$currterm}{$dayincycle}{$per};
			$crsenrol{$subjsec}++;
			$crsabsent{$subjsec}++;
			
			$temp++;  # not really used anymore
		    }
		}
		
		my $dayfractionabsent = $temp / $ppd;
		if ( $dayfractionabsent > $daysclosed{$day} ) {
		    #print "Too much absent! Day:$day Student:$studnum<br>\n";
		    $attendclosure += (1 - $dayfractionabsent);
		} else {
		    $attendclosure += $daysclosed{$day};
		}
	    }


	
	    # PERIOD LOOP - loop through all periods
	    for my $per ( 1..$ppd ) {

		# Find the course for this period
		my $subjsec = $studenttimetable{$currterm}{$dayincycle}{$per};

		if ( not $subjsec ) { next; } # not enrolled this period.

		$crsenrol{$subjsec}++;
		    

=head		# We're not checking...
		# Check for a teacher attendance entry
		my $courseoffered = $studenttimetable{$currterm}{$dayincycle}{$per};
		my $userid = $teacherattendance{$day}{$per}{$courseoffered};
		
		if ( $userid ) { # a userid indicates things are done.
		    my $coursename = $coursename{$courseoffered};
		    my $teachername = $teachername{$userid};

		} elsif ( not $courseoffered ) {
		    $nocourseenrollment = 1;
		} else {
		    $missingatt{$day} = 1; # missing attendance
		    $statsflag = 0;
		}
=cut
		
		if ( $reason = $att{$day}{$per}{'reason'} ) { # if we have a record for that day and period.
		    if ( $reason =~ m/Absent/ ) {
			$crsabsent{$subjsec}++;
		    }
		}

	    } # end of periods loop

	    # update enrollment status ($estatus), if necessary
	    if ( $enrol{$day} eq 'withdraw' ) { # status change
		$estatus = 0;
	    }

	} # end of day loop

	
	# Convert date format
	$birthdate = convertDate($birthdate);
	$enrolstart = convertDate($enrolstart);
	$enrolend = convertDate($enrolend);

	
	foreach my $subjsec ( keys %crsenrol ) {

	    # Populate blank absences with a zero.
	    if ( not $crsabsent{$subjsec} ) { $crsabsent{$subjsec} = '0'; }
	    
#	    print "STUD:$studnum SUB:$subjsec - E:$crsenrol{$subjsec} - ABS:$crsabsent{$subjsec}<br>\n";
	    
	    my ($course, $section) = split('-', $subjsec);

	    my @vals;
	    push @vals, $schoolnumber, $schoolnumber, $provnum, $birthdate, $enrolstart, $enrolend, 
	      $grade, $course, $crsenrol{$subjsec}, $crsabsent{$subjsec};

	
	    # WRITE CSV DATA Here.
	    if ($csv->combine( @vals )) {
		my $record = $csv->string;
		print $fh $record, "\r\n";
	    } else {
		my $err = $csv->error_input;
		print qq{CSV Join Failed:$err\n\n};
	    }
	}


	# Empty the student timetable values
	foreach my $t ( sort keys %studenttimetable ) {
	    foreach my $d ( sort keys %{ $studenttimetable{$t} } ) {
		foreach my $p ( sort keys %{ $studenttimetable{$t}{$d} } ) {
		    # print qq{ Term:$t Day:$d Period:$p Subject:$studenttimetable{$t}{$d}{$p}<br>\n};
		    delete $studenttimetable{$t}{$d}{$p};
		}
		delete $studenttimetable{$t}{$d};
	    }
	    delete $studenttimetable{$t};
	}

    } # end of student loop
    
    close $fh;
    #END of SECONDARY Section

    # Compress into zip and show download option.
    my $zipname = qq{$schoolnumber\_$monthfilename\_$$};
    
    system("zip $zipname.zip $elemfile $secfile  >> pdflog$$.txt");
    system("mv $zipname.zip $downloaddir");
    system("mv pdflog$$.txt $downloaddir");
    unlink $elemfile, $secfile or warn "<h3>Could not delete csv files:$!</h3>\n";
    
    print qq{<table cellspacing="0" cellpadding="3" border="0" };
    print qq{style="padding:1em;margin:0.5em;border:1px solid gray;">\n};
    print qq{<tr><td><h1>[ <a href="$webdownloaddir/$zipname.zip">\n};
    print qq{Download Attendance File</a> ]</h1>\n};
    print qq{</td></tr></table>\n};

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

    exit;

} # end of showReport



#--------------
sub convertDate {
#--------------
    
    my $date = shift;
    my ($y,$m,$d) = split('-', $date );
    return "$m/$d/$y";

}



#--------------
sub findCourses {
#--------------

    # Find the courses this term for these students, passed a list of studnums, after the term to check.
    my ( $term, @students ) = @_;
    if ( not $term ) {
	print "<div>Missing Term for students</div>\n";
	return;
    }

    my %courses;

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

    foreach my $studnum ( @students ) {

	$sth->execute( $studnum );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $subjsec = $sth->fetchrow ) {
	    $courses{$subjsec} = 1;
	}
    }

    return keys %courses;

}

#---------------------
sub findCourseTeachers {
#---------------------

    # Find the teachers for these courses, passed a list of studnums
    my @courses = @_;
    my @teachers;

    my $sth = $dbh->prepare("select teacher from subject where subjsec = ?");

    foreach my $subjsec ( @courses ) {
	$sth->execute( $subjsec );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $userid = $sth->fetchrow;
	push @teachers, $userid;
    }

    return @teachers;

}
