# Library function for attendance scripts.

my %lex = ( 'Error' => 'Error',
	    'Transfer' => 'Transfer',
	    'Student' => 'Student',
	    'Date' => 'Date',
	    'Missing' => 'Missing',
	    'Value' => 'Value',

    );


# Update: Feb 28, 2018 - Remove mk SchoolMonths sub. Fix other stuff

# Check Teacher Attendance for a homeroom.
# checkHomeroomAttEntry (date,homeroom, pclosedref, dbh); RETURNS: a list of periods that have been entered.

# Check Teacher Attendance for a course. RETURNS: a list of Date:Period elements missed for entire course;
# checkCourseAttEntry ( enddate, subjsec, pclosedref, dbh ); 

# getTermDates( $grade, $term );  returns: startdate, enddate.

# mkSchoolDays($startdate, $enddate, $dbh) - Return %schooldays hash of
#   days in month school is open.  key is 'yyyy-mm' format and value
#   is schooldays in the month.


# calcMonthlyEnrollment($studnum, $startdate, $enddate, $dbh, $nsdhomeroom ) calculate
#   student attendance by month for several months. Returns: hash of start/end/enrolled for several months

# calcMonthlyAttendance($studnum, $yearmonth, $periodsperday, $enddate, $lexiref) - calculate
#   the attendance for the student for the month. Return: daysabsent:timeslate
#  Update: add a new optional parameter for date in month to stop at.

# calcAttendance { # calculate absences,lates for 1 child for a date range

# calcDaysOpen($startdate,$enddate, $dbh) - calculate the number of
#   school days open within 2 dates (inclusive). Return: $daysopen

# findEnrollmentBlocks($studnum, $startdate, $enddate, $dbh ) - return time blocks student is
#   enrolled.


#------------------------
sub checkHomeroomAttEntry { # check that attendance has been done for a particular homeroom.
#------------------------

    use Time::JulianDay;
    
    # Passed: enddate - end date to check up to, homeroom to check, 
    # reference to periods closed hash, and dbh (database handle)
    my ($enddate, $homeroom, $pclosedref, $dbh) = @_;

    my %pclosed = %$pclosedref; # {date}{grade}{period};
    # Note: All pclosed dates are 2 digit for month and day;
    my $hrfield = qq{HR:$homeroom};

    # use Data::Dumper;
    # print Dumper %pclosed;

    
    # Check for configured values
    if ( not $schoolstart or not %g_ppd ) {
	# Read in values from conf_system;
	my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?");
	foreach my $dataname ( qw( g_ppd schoolstart )) {
	    $sth->execute( $dataname );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	    my $datavalue = $sth->fetchrow;
	    eval $datavalue;
	    if ( $@ ) {
		print "$lex{Error} $@<br>\n";
		die "$lex{Error} $@\n";
	    }
	}
    }

    # Get homeroom grades, then attendance periods per day (max) aka $ppd
    my ($ppd, $grade); # a $grade is needed for the periods closed hash that is passed in.
    my $sth = $dbh->prepare("select distinct grade from student where homeroom = ?");
    $sth->execute( $homeroom );
    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	if ( $g_ppd{$gr} > $ppd ) { $ppd = $g_ppd{$gr}; }
	$grade = $gr;
    }


    # Non School Day (NSD) closures.  Add to pclosed hash, if so.
    # Is this homeroom an NSD one?
    my $sth = $dbh->prepare("select count(*) from dates_homeroom where homeroom = ?");
    $sth->execute( $homeroom );
    if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
    my $nsdcount = $sth->fetchrow;
    
    if ( $nsdcount ) { # add to pclosed hash; just get dates within range.
    	my $sth = $dbh->prepare("select date,period from dates_homeroom where homeroom = ? and 
          to_days(date) >= to_days('$schoolstart') and to_days(date) <= to_days('$enddate')");
	$sth->execute( $homeroom );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my ($date,$period) = $sth->fetchrow ) {
#	    print qq{HR:$homeroom Date:$date Period:$period<br>\n};
	    $pclosed{$date}{$grade}{$period} = 1;
	}
    }

    
=head
    # Get Teacher of the homeroom.
    my $sth = $dbh->prepare("select userid from staff_multi where field_name = 'homeroom' and field_value = ?");
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    $sth->execute( $homeroom );
    my $userid = $sth->fetchrow;
    if ( not $userid ) { return undef; }
=cut

    # Start and End Dates.
    my $startjd = julian_day(split('-', $schoolstart));  # is this defined yet?
    my $endjd = julian_day(split('-', $enddate));

    
    # Check for an attendance entry by date, homeroom  ('subjects' field below will be 'HR:$homeroom' value)
    my $sth2 = $dbh->prepare("select subjects, periods from tattend where subjects = ? and attdate = ?");

    # Check for closed school
    my $sth3 = $dbh->prepare("select id, dayfraction from dates where date = ?");

    my %missing; # hash keys will be returned. date:period vals.

    foreach my $jd ($startjd..$endjd) {

	# Check if a weekend
	my $dow = day_of_week( $jd );
	if ( $dow == 0 or $dow == 6 ) { next; } # skip weekends.


	# Check if school closed - dates table only; periods version below;
	# Dates must have 2 digit month and day to match in the pclosed hash.
	my ($y,$m,$d) =  inverse_julian_day( $jd );
	if (length $m == 1 ) { $m = '0'. $m; }
	if (length $d == 1 ) { $d = '0'. $d; }
	my $date = qq{$y-$m-$d};
	

	$sth3->execute( $date );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($id, $dayfraction) = $sth3->fetchrow;
	if ( $id and $dayfraction > 0.99 ) { # school is closed; skip
	    next; 
	}

	
	# Get Periods fields for attendance on this date
	my %periodsentered;
	$sth2->execute( $hrfield, $date );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $period = $sth2->fetchrow ) { 
	    $periodsentered{$period} = 1;
	}

	
	foreach my $period ( 1..$ppd ) {
	    # We can now check to see if teacher entered
	    # attendance for this period on this day for this homeroom

	    if ( $pclosed{$date}{$grade}{$period} ) { next; } # skip this date.

	    if ( not $periodsentered{$period} ) {
#		print "Missing:$date P $period<br>\n";
		my $key = "$date:$period";
		$missing{$key} = $period;
	    }
	} # end of periods loop
	# print "Entered:", %periodsentered, "<br>\n";

    } # end of days loop

    return sort keys %missing;

}


#---------------------
sub checkCourseAttEntry { # check that ALL attendance has been done for a particular subject-section (ie. course)
#---------------------

    use Time::JulianDay;

    # Passed: enddate - end date to check up to, course to check (and matching teacher), 
    # reference to periods closed hash, and dbh (database handle)
    my ($enddate,$subjsec, $pclosedref, $dbh) = @_;

    my %pclosed = %$pclosedref; # {date}{grade}{period};

    # We'll call the end date as the 'cutoff' date to differentiate from end date for each term.
    my $cutoffjd = julian_day(split('-', $enddate));


    # Get Subject Info
    my $sth = $dbh->prepare("select teacher, startrptperiod, endrptperiod, description, grade 
      from subject where subjsec = ?");
    $sth->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($userid, $startterm, $endterm, $description, $grade ) = $sth->fetchrow;
    if ( not $userid ) { return -1; } # negative values are failures.

    # Get Teacher Name
    my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    $sth->execute( $userid );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my ($lastname, $firstname) = $sth->fetchrow;

    # Load Timetable values for a subject in a term.
    my $sth1 = $dbh->prepare("select day, period from schedat where subjsec = ? and term = ?");

    # Check for an attendance entry.
    my $sth2 = $dbh->prepare("select subjects, periods from tattend where userid = ? and attdate = ?");

    # Check for closed school
    my $sth3 = $dbh->prepare("select id, dayfraction from dates where date = ?");

    my %missing;

    foreach my $term ( $startterm..$endterm ) {

	# Get Start and End Dates; assume no holes between terms.
	my ($startdate, $enddate) = getTermDates($grade, $term);

	my $startjd = julian_day(split('-', $startdate));
	my $endjd = julian_day(split('-', $enddate));
	if ( $endjd > $cutoffjd ) { $endjd = $cutoffjd; }

	# Load Timetable for this term for this subject
	$sth1->execute( $subjsec, $term );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my %ttbl;
	while (	my ($day, $period) = $sth1->fetchrow ) {
	    $ttbl{$day}{$period} = 1;
	}

	# Check Timetable
	# print qq{Subjsec: $subjsec Term:$term<br>\n};
	# foreach my $day ( sort keys %ttbl ) {
	    # print qq{Day:$day };
	    # foreach my $period ( sort keys %{ $ttbl{$day}} ) {
	    #print qq{ P:$period };
	    #}
	    #print qq{<br>\n};
	# }
	

	foreach my $jd ( $startjd..$endjd ) {

	    # Check if a weekend
	    my $dow = day_of_week( $jd );
	    if ( $dow == 0 or $dow == 6 ) { next; } # skip weekends.


	    # Get Day in Cycle to check timetable.
	    my $date = join('-', inverse_julian_day($jd));
	    my $dayincycle = findDayInCycle( $date );
	    my $ref = $ttbl{$dayincycle};
	    # print qq{Day:$dayincycle<br>\n};
	    # foreach my $p ( sort keys %{ $ref } ) {
	    #   print "P:$p ";
	    # }
	    # print qq{<br>\n};
	    
	    
	    if ( not keys %{$ref} ) { 
		# print "<div>Not Scheduled - $subjsec - Date:$date Day:$dayincycle</div>\n";
		next; 
	    } # skip if no keys in hash.


	    # Check if school closed - dates table only; periods version below;
	    my ($y, $m, $d) =  inverse_julian_day( $jd );
	    if ( length $m == 1 ) { $m = '0'. $m; }
	    if ( length $d == 1 ) { $d = '0'. $d; }
	    my $date = "$y-$m-$d";

	    $sth3->execute( $date );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    my ($id, $dayfraction) = $sth3->fetchrow;
	    if ( $id and $dayfraction > 0.99 ) { # school is closed; skip
		next; 
	    }
	    
	    
#	    print "<b>$subjsec - TTBL:$dayincycle:</b>";
#	    foreach my $key ( sort keys %{$ref} ) { print "K:$key "; }
#	    print "<br>\n";

	    # Get Subject and Period fields for attendance
	    $sth2->execute( $userid, $date );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

	    my %periodsentered;
	    while ( my ($subject, $period) = $sth2->fetchrow ) { 
		# don't care about subject since all same
		$periodsentered{$period} = 1;
	    }

	    foreach my $period ( sort keys %{$ref} ) {  # from timetable.
		# We can now check to see if teacher entered
		# attendance for this period on this day for this subject (based on
		# timetable)

		if ( $pclosed{$date}{$grade}{$period} ) { next; } # skip this date.

		if ( not $periodsentered{$period} ) {
		    my $key = "$date:$period";
		    $missing{$key} = $period;
		}
	    }

	} # end of term jd loop
    } # end of terms loop

    # my $missingperiods = keys %missing;

    # print qq{$description ($subjsec) $firstname $lastname Periods Missed:$missingperiods<br>\n};
    # foreach my $key ( sort keys %missing ) {
    #   my ($date, $period ) = split(':', $key);
    #   print "Date:$date Period:$period<br>\n";
    # }

#    return $missingperiods;

    # my $retval = join(' ', keys %missing );
    return keys %missing;
}



#---------------
sub getTermDates { # abstract away the track issues.
#---------------
    # assume all admin.conf globals have been loaded to work with.
    my ( $grade, $term ) = @_;
    my $track = $g_MTrackTermType{$grade};

    $startdate = $g_MTrackTerm{$track}{$term}{'start'};
    $enddate = $g_MTrackTerm{$track}{$term}{'end'};


    return ($startdate, $enddate);

}


#---------------
sub mkSchoolDays { # Make a %schooldays hash containing yyyy-mm:schoolday vals 
#---------------

    use Number::Format qw(:all);
    use Time::JulianDay;

    # Loop through all months and create a hash %schooldays
    #  with yyyy-mm key and value containing schooldays in that month.
    #  Stop at month (and Day!) with $enddate.

    # Passed: $startdate, $enddate in iso format yyyy-mm-dd
    my ($startdate,$enddate, $dbh, $nsdhomeroom ) = @_;
    # nsdhomeroom is a nonschool day homeroom (PK, K) with closed dates in dates_homeroom table.

    my %schooldays; # structure to return

    my ($syr, $smo, $sda ) = split('-', $startdate);
    my ($eyr, $emo, $eda ) = split('-', $enddate);

    my $startjd = julian_day( split('-', $startdate) );
    my $endjd = julian_day( split('-', $enddate) );

    my $indexjd = $endjd; # start from the end

    while ( $indexjd >= $startjd ) {

	my $monthendjd = $indexjd;

	my @edate = inverse_julian_day( $monthendjd );
	if ( length $edate[1] == 1 ) { $edate[1] = '0'. $edate[1]; } # fix month length
	my $yearmonth = "$edate[0]-$edate[1]";
	my $enddate = join('-',@edate);

	my $monthstartjd = $monthendjd - ($edate[2] - 1); # set to first of the month.
	if ( $monthstartjd < $startjd ) { $monthstartjd = $startjd; } # start
	my $startdate = join('-', inverse_julian_day( $monthstartjd ));


	my $opendays = calcDaysOpen( $startdate, $enddate, $dbh, $nsdhomeroom);
	if ( $opendays == 0 ) { $opendays = 0; }
	    
	# Only need these other things if we want the dates in the month...??
	#my $ref = {};
	#$ref->{start} = $monthstartjd;
	#$ref->{end} = $monthendjd;
	#$ref->{days} = $opendays;
	# $schooldays{$yearmonth} = $ref; # replaced
	$schooldays{$yearmonth} = $opendays;

	$indexjd = $monthstartjd - 1; # now set to previous month end

    } # end of loop

    return %schooldays;

} # End of mkSchoolDays



#------------------------
sub calcMonthlyEnrollment { # calculate enrollment for several months for 1 child
#------------------------

    use Time::JulianDay;

    # Passed: student number, start and end dates for period of interest.
    my ( $studnum, $startdate, $enddate, $dbh, $nsdhomeroom ) = @_; # same as findEnrollmentBlocks

    # find enrollment blocks for this time period, split up into months.
    my @enrolblocks = findEnrollmentBlocks( $studnum, $startdate, $enddate, $dbh );


    if ( not $enrolblocks[0] ) { # print "No Enrollment for $studnum<br>\n"; 
	return undef; 
    }


    my %enrolYM;  # holds results $enrolYM{$yearmonth} -> {start},{end},{days}

    # Loop through each enrollment block
    foreach my $ref ( @enrolblocks ) {
	my $startdate = $ref->{start};
	my $enddate = $ref->{end};

	# print "Studnum:$studnum Start:|$startdate| End:|$enddate|<br>\n";

	my ($syr, $smo, $sda ) = split('-', $startdate);
	my ($eyr, $emo, $eda ) = split('-', $enddate);
	my $blockstartjd = julian_day( split('-', $startdate) );
	my $blockendjd = julian_day( split('-', $enddate) );
	my $indexjd = $blockendjd; # start from the end of the block

	while ( $indexjd >= $blockstartjd ) {

	    my $monthendjd = $indexjd;

	    my @edate = inverse_julian_day( $monthendjd );
	    if ( length $edate[1] == 1 ) { $edate[1] = '0'. $edate[1]; } # fix month length
	    my $yearmonth = "$edate[0]-$edate[1]";
	    my $enddate = join('-',@edate);

	    my $monthstartjd = $monthendjd - ($edate[2] - 1); # this is the first of the month.
	    if ( $monthstartjd < $blockstartjd ) { $monthstartjd = $blockstartjd; } # start of block
	    my $startdate = join('-', inverse_julian_day( $monthstartjd ));

	    my $enroldays = calcDaysOpen( $startdate, $enddate, $dbh, $nsdhomeroom);
	    if ( $enroldays == 0 ) { $enroldays = 0; }


	    # Check if an entry already exists
	    if ( $enrolYM{$yearmonth} ) { # update total
		my $prevdays = $enrolYM{$yearmonth}->{days};
		my $newdays = $prevdays + $enroldays;
		$enrolYM{$yearmonth}->{days} = $newdays;
		if ( $enrolYM{$yearmonth}->{start} > $monthstartjd ) {
		    $enrolYM{$yearmonth}->{start} = $monthstartjd;
		}
		if ( $enrolYM{$yearmonth}->{end} < $monthendjd ) {
		    $enrolYM{$yearmonth}->{end} = $monthendjd;
		}

	    } else { # make a new entry
		my $ref = {};
		$ref->{start} = $monthstartjd;
		$ref->{end} = $monthendjd;
		$ref->{days} = $enroldays;
		$enrolYM{$yearmonth} = $ref;
	    }

	    $indexjd = $monthstartjd - 1; # now set to previous month end

	} # end of this enrollment block
    } # end of enrollment blocks


    return \%enrolYM;

} # End of NEW calcMonthlyEnrollment 



#------------------------
sub calcMonthlyAttendance { # calculate absences,lates for 1 month for 1 child
#------------------------

    my ( $studnum, $yearmonth, $periodsperday, $enddate, $lexref, $dbh ) = @_;

    # $enddate added for the final date in month to use, if present);
    # Previously it always did the entire month...
    my %lexi = %{ $lexref };

    # yearmonth will have yyyy-mm format
    $yearmonth =~ s/-//; # remove single dash

    my $sth = $dbh->prepare("select absdate, reason from attend 
     where studentid = ? and extract(year_month from absdate) = ?
     order by absdate,period");
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    $sth->execute( $studnum, $yearmonth );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    # print "CalcMonthlyAttendance: SN: $studnum YM: $yearmonth Rows: $rows<br>\n";

    if ( not $lexi{Absent} ) { 
	print "Missing Lex for Absent"; die "Missing Lex for Absent"; 
    }


    my $absentcount = 0;
    my $latecount = 0;

    my ( $eyr, $emo, $eda ) = split '-', $enddate;
    if ( length( $emo ) == 1 ) { $emo = '0'. $emo; }
    if ( length( $eda ) == 1 ) { $eda = '0'. $eda; }
    $enddate = $eyr . $emo. $eda;

    while ( my ( $absdate, $reason ) = $sth->fetchrow ){
	$absdate =~ s/-//g; # strip hyphens
	if ( $enddate and $absdate > $enddate ) {
	    #print "Abs:$absdate  End:$enddate<br>\n";
	    last; }
	if ( $reason =~ m/$lexi{Absent}/ ) { $absentcount++; }
	if ( $reason =~ /$lexi{Late}/ ) { $latecount++; }
    }

    my $absentdays;
    if ( $periodsperday ) { 
	$absentdays = round($absentcount / $periodsperday, 2); #convert periods into days
	#$absentdays = $absentcount / $periodsperday;
    } else {
	$absentdays = -1;  # Flag error condition ??
    }

    return "$absentdays:$latecount";
}

#-----------------
sub calcAttendance { # calculate absences,lates for 1 child for a date range
#-----------------

    use Time::JulianDay;
    
    my ( $studnum, $startdate, $enddate, $ppd, $lexref, $dbh ) = @_;

#    print "SN:$studnum S:$startdate E:$enddate PPD:$ppd<br>\n";
    
    my %lexi = %{ $lexref };

   if ( not $lexi{Absent} ) { 
	print "Missing Lex for Absent"; die "Missing Lex for Absent"; 
    }

    my $absentcount = 0;
    my $latecount = 0;
   
    my $sth = $dbh->prepare("select absdate, reason from attend 
      where studentid = ? and to_days(absdate) >= to_days('$startdate') and
      to_days(absdate) <= to_days('$enddate') order by absdate,period");
    $sth->execute( $studnum );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    while ( my ( $absdate, $reason ) = $sth->fetchrow ){
	if ( $reason =~ m/$lexi{Absent}/ ) { $absentcount++; }
	if ( $reason =~ /$lexi{Late}/ ) { $latecount++; }
    }

    my $absentdays;
    if ( $ppd ) { 
	$absentdays = round($absentcount / $ppd, 2); #convert periods into days
	
    } else {
	print qq{<h3>PPD Not Found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    return "$absentdays:$latecount";
}




#---------------
sub calcDaysOpen {
#---------------
    
    # this calculates the number of schooldays in a date range
    #  taking school holidays into account. (complete day closed)
    # Requires: $dbh

    # Outline: It will check both the start and end dates inclusive to
    # make sure that they are open days also.
    
    use Time::JulianDay;
    
    my ($startdate, $enddate, $dbh, $nsdhomeroom ) = @_;

    my $startjd = julian_day( split('-', $startdate));
    my $endjd = julian_day( split('-', $enddate));

    if ( $startjd > $endjd ) {
	print qq{<div>($startdate - $enddate)  Starting Date is later than Ending Date</div>\n};
	return;
    }

    # count the number of days skipping the weekends.
    my $daycount;
    for my $jd ( $startjd .. $endjd ) {
	my $dow = day_of_week( $jd );
	if ( $dow != 0 and $dow != 6 ) {
	    $daycount++;
	}
    }
    
    # We now have the number of school days
    # excluding holidays/pd days (and nonschooldays for K,PK)

    my %nonschoolday;
    if ( $nsdhomeroom ) {
    	my $sth = $dbh->prepare("select * from dates_homeroom where homeroom = ? and 
          to_days(date) >= to_days('$startdate') and to_days(date) <= to_days('$enddate')");
	$sth->execute( $nsdhomeroom );
	if ( DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    $nonschoolday{$r{date}}++; # don't worry about the period, just count them.
	}
    }

    
    # Now find holidays ( day closed > 0.99)
    # Find number of days school not in session this period.
    my $sth = $dbh->prepare("select id, date, dayfraction from dates 
     where to_days(date) >= to_days('$startdate') and 
     to_days(date) <= to_days('$enddate')");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    
    my $holidays;
    while ( my ($id, $date, $dayfraction) = $sth->fetchrow ) {
	if ( $dayfraction > 0.99 ) {
	    if ( $nonschoolday{ $date } ) { # we have this ALSO in nsd hash... delete it.
		delete $nonschoolday{ $date }; # we really don't need the IF, but it makes things explicit
	    }
	    $holidays++;  # don't worry about partial days closed.
	}
    }

    foreach my $date ( keys %nonschoolday ) {
	my $closed = $nonschoolday{$date} / 2;
	$holidays += $closed;
    }
    
    my $tmpdays = $daycount - $holidays;

#    print "NEW:$startdate - $enddate OPEN:$tmpdays<br>\n";
    
    return $tmpdays;
}



#-----------------------
sub findEnrollmentBlocks {
#-----------------------

    # Return a list of hash pointers to enrollment blocks.

    my ( $studnum, $startdate, $enddate, $dbh ) = @_;

    my @enrollment = ();

    # Check for Transfer Records, this time period
    my $sth = $dbh->prepare("select count(*) from transfer where studnum = ? and 
      to_days(date) >= to_days('$startdate') and 
      to_days(date) <= to_days('$enddate')");
    $sth->execute( $studnum );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $count = $sth->fetchrow;

    if ( not $count ) {

	# check to see if active, during this time period
	# Check for next following record.
	my $sth = $dbh->prepare("select type from transfer where studnum = ? and 
          to_days(date) > to_days('$enddate') order by date");
	$sth->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $followingtype = $sth->fetchrow;

	if ( $followingtype ) {
	    if ( $followingtype ne 'withdraw' ) { # they weren't enrolled
		return undef;
	    } else {
		my $hashref = {};
		$hashref->{start} = $startdate;
		$hashref->{end} = $enddate;
		push @enrollment, $hashref;
		return @enrollment;
	    }
	}
	    
	# Check for any previous enrollment record.
	$sth = $dbh->prepare("select type from transfer where studnum = ? and 
         to_days(date) < to_days('$startdate') order by date desc");
	$sth->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $leadingtype = $sth->fetchrow;

	if ( $leadingtype ) {
	    if ( $leadingtype eq 'withdraw' ) { # they aren't enrolled anymore
		return undef;
	    } else {
		my $hashref = {};
		$hashref->{start} = $startdate;
		$hashref->{end} = $enddate;
		push @enrollment, $hashref;
		return @enrollment;
	    }
	}


	$sth = $dbh->prepare("select count(*) from student where studnum = ?"); 
	$sth->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my $count = $sth->fetchrow;
	if ( not $count ) { return undef; } # not active, return undef
    
	# we have an active student, return full block enrolled
	my $hashref = {};
	$hashref->{start} = $startdate;
	$hashref->{end} = $enddate;
	push @enrollment, $hashref;
	return @enrollment;

    } # end of no enrollment records.
    

    # We have enrollment records...
    # Read enrollment recs from the transfer table.
    $sth = $dbh->prepare("select id, date, type from transfer 
      where studnum = ? and to_days(date) >= to_days('$startdate')
      and to_days(date) <= to_days('$enddate')
      order by date, type");

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

    # put records into enrolblock data structure.
    my @recids = ();
    my %enrolblock = ();
    my ($maxid, $minid); # record ids

    while ( my ($id, $date, $type) = $sth->fetchrow ) {

	# Transfer Records for this student.
	# print "ID:$id  Date:$date  Type:$type<br>\n"; # transfer records

	if ( $id > $maxid ) { $maxid = $id; }
	$enrolblock{$id} = { 'date' => $date, 'type' => $type };
	push @recids, $id;
    }



    # check the start entry
    my $startid = $recids[0];
    if ( $enrolblock{$startid}->{type} eq 'withdraw' ) { # add new start or remove entry.

	if ( $enrolblock{$startid}->{date} eq $startdate ) { # remove entry
	    delete $enrolblock{$startid};
	    shift @recids;
	    if ( not @recids ) {
		return undef;
	    }
	} else { # add a new entry
	    $maxid += 10; # get a new larger value.
	    $enrolblock{$maxid} = { 'date' => $startdate, 'type' => 'enrol' };
	    unshift @recids, $maxid;
	}
    }


    # check the end entry
    my $endid = $recids[-1];
    if ( $enrolblock{$endid}->{type} ne 'withdraw' ) { # add new end or remove entry.
	
	if ( $enrolblock{$endid}->{date} eq $enddate ) { # remove entry
	    delete $enrolblock{$endid};
	    my $end = pop @recids;
	    if ( not @recids ) {
		return undef;
	    }
	    
	} else {
	    $maxid += 10; # get a new larger value.
	    $enrolblock{$maxid} = { 'date' => $enddate, 'type' => 'withdraw' };
	    push @recids, $maxid;
	}
    }


    # Record ID's printout
    #	print "<p>Recs:";
    #	foreach my $r (@recids) { print "$r "; }
    #	print "</p>\n";

    # Check Enrollment block data structure
    # print "Studnum:$studnum<br>\n";
    # foreach my $id ( @recids ) { print "Id:$id  Date:$enrolblock{$id}->{date} ";
        # print qq{Type:$enrolblock{$id}->{type} <b>||</b> \n"; }
    # print "<br><br>\n";

    # run through the structure checking for matching pairs
    my $first = 1;
    my ($prevtype, $currtype);


    foreach my $id ( @recids ) {

	if ( $first ) {
	    # Check that currtype is 'enrol'
	    if ( $enrolblock{$id}->{type} eq 'withdraw' ) { # error
		print qq{<p>$lex{Transfer} $lex{Error} - $lex{Student}:$studnum ID:$id };
		print qq{$lex{Date}:$enrolblock{$id}->{date}</p>\n};
		return undef;
	    }
	    $first = 0;
	    $currtype = 'enrol';
	    
	} else {
	    $prevtype = $currtype; 
	    $currtype = $enrolblock{$id}->{type};
	    
	    if ( ( $currtype eq 'withdraw' and $prevtype eq 'withdraw' ) or 
		 ( $currtype ne 'withdraw' and $prevtype ne 'withdraw' ) ) { # both same values
		
		print "<p>$lex{Transfer} $lex{Error} - $lex{Student}:$studnum ID:$id ";
		print "$lex{Date}:$enrolblock{$id}->{date}</p>\n";
		return undef;
	    }
	}

    } # end of enrollment records loop.

    
    while ( @recids ) { # put values into array structure

	my $startid = shift @recids;
	my $endid = shift @recids;

#	print "Start:$startid - $enrolblock{$startid}->{type} $endid End: $enrolblock{$endid}->{type}<br>\n";

	# Check values again.
	if ( ( $enrolblock{$startid}->{type} eq 'withdraw' ) or 
	     ( $enrolblock{$endid}->{type} ne 'withdraw' ) ) { # error

	    print "<p>$lex{Transfer} $lex{Error} - $lex{Student}:$studnum ID:$id ";
	    print "$lex{Date}:$enrolblock{$id}->{date}</p>\n";
	    return undef;
	}

	my $hashref = {};
	$hashref->{start} = $enrolblock{$startid}->{date};
	$hashref->{end} = $enrolblock{$endid}->{date};
	push @enrollment, $hashref;
    }


    # Show Result:
    #    foreach my $bl ( @enrollment ){
    #	print "SN:$studnum  ST:$bl->{start} ";
    #   	print "ED:$bl->{end}<br>\n";
    # }
 
    return @enrollment;

} # End of findEnrollmentBlocks


# Return true.
1;