#!/usr/bin/perl
#  Copyright 2001-2019 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 = ('Dates' => 'Dates',
	   'Eoy' => 'Eoy',
	   'Main' => 'Main',
	   'Date' => 'Date',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Error' => 'Error',
	   'Not Found' => 'Not Found',
	   'Closed' => 'Closed',
	   'Frac' => 'Frac',
	   'Description' => 'Description',
	   'Type' => 'Type',
	   'Continue' => 'Continue',
	   'Save' => 'Save',
	   'Day In Cycle' => 'Day In Cycle',
	   'Statutory Holiday' => 'Statutory Holiday',
	   'Inservice' => 'Inservice',
	   'Holiday' => 'Holiday',
	   'Other' => 'Other',
	   'Description' => 'Description',
	   'No' => 'No',
	   'Yes' => 'Yes',
	   'Fraction' => 'Fraction',
	   'Periods' => 'Periods',
	   
	   );

use DBI;
use CGI;

my $self = qq{dateDelEd.pl};


# Read config variables
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);
$dbh->{mysql_enable_utf8} = 1;


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

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$eoypage">$lex{Eoy}</a> ]\n};
print qq{<h1>$lex{Edit}/$lex{Delete} $lex{Dates}</h1>\n};


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

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

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    updateEdits();
}



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

    my $first = 1;
    
    my $sth = $dbh->prepare("select * from dates order by date");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }

    # Start the Form.
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="$lex{Continue}">\n\n};
    
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
    	# $id, $date, $type, $desc1 

	# Put in a text month value.
	my @dt = split('-', $r{date});
	$dt[1] = $s_month[$dt[1]];
	my $newdate = join('-', @dt);
	
	if ( $first ) { 
	    print qq{<table cellpadding="3" border="1" cellspacing="0">\n};
	    print qq{<tr><th>$lex{Date}</th><th>$lex{Description}</th><th>$lex{Type}</th>};
	    print qq{<th>$lex{Closed}<br>$lex{Frac}</th><th>Edit</th><th>Delete</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td class="bla">$newdate</td><td>$r{desc1}</td><td>$r{type}</td>\n};
	print qq{<td>$r{dayfraction}</td>};

	print qq{<td><input type="checkbox" name="edit:$r{id}" value="1">Edit</td>\n};

	print qq{<td><input type="checkbox" name="delete:$r{id}" value="1">Delete</td></tr>\n};

    }

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

    print qq{<input type="submit" value="$lex{Continue}">\n\n};
    print qq{</form>\n};
    print qq{</body></html>\n};
    
    exit;

}


#----------------
sub delEditDates {
#----------------

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

    use Time::JulianDay;    

    # Delete any records to start
    my $sth = $dbh->prepare("delete from dates where id = ?");
    my $sth1 = $dbh->prepare("delete from dates_periods where date = ?");
    my $sth2 = $dbh->prepare("select date from dates where id = ?");
    
    # Delete any selected records
    foreach my $key ( keys %arr ) {
	my ($action, $id) = split(':', $key);
	if ( $action eq 'edit' ) { next; }

	# check for id
	if ( not $id ) {
	    print qq{<h3>Error: Record ID not found for deletion</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}
	
	# Get Date
	$sth2->execute( $id );
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $date = $sth2->fetchrow;
	if ( not $date ) { 
	    print qq{<h3>Error: Date not found for deletion</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}

	# Delete any periods for this date
	$sth1->execute( $date );
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	# Delete the main record
	$sth->execute( $id );
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }

	# Delete this entry from hash.
	delete $arr{$key}; 

	print qq{<h3>Date Closed record for $date deleted</h3>\n};

    }


    if ( not %arr ) { # end of any changes if only deletes.
	print qq{</body></html>\n};
	exit;
    }

    # Setup for Editing; Periods
    
    # Get Attendance Periods.
    my %ppdData;
    foreach my $grade (keys %g_ppd ) {
	my $periods = $g_ppd{$grade};
	$ppdData{$periods}{$grade} = 1;
    }

    my %periodgroup; # grade group with minimum and max for each attendance periods.
    foreach my $p ( keys %ppdData ) {
	@gr = sort {$a <=> $b} keys %{ $ppdData{$p} };
	if ($ppdData{$p}{PK}) { $gr[0] = 'PK'; }; # since a K value will show instead.
	$val = "$gr[0]-$gr[-1]";
	$periodgroup{$p} = $val;
    }

# Check Data    
#    foreach my $p ( sort keys %periodgroup ) {
#	print "Periods:$p Group:$periodgroup{$p}<br>\n";
#    }

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

    print qq{<input type="submit" value="$lex{Save}">\n};
    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};
    print qq{<tr><td colspan="2"><hr></td></tr>\n};

    my $sth = $dbh->prepare("select * from dates where id = ?" );
    my $sth1 = $dbh->prepare("select grades, period from dates_periods where date = ?");
	
    
    foreach my $key ( sort keys %arr ) {
			     
	my ($action, $id) = split(':', $key);

	if ( $action ne 'edit' ) {
	    print qq{<h3>Error: Edit action not found. Action is: $action</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}
			     
	# Load the main record and any period records
	$sth->execute( $id );
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %r = %$ref;
	my $date = $r{date};
	my $jd = julian_day( split('-', $date));
			     
	# Load any Periods;
	my %periods;
	$sth1->execute( $date );
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	while  ( my ( $grades, $period ) = $sth1->fetchrow ) {
	    $periods{$grades}{$period} = 1;
	}
	
	my $dow = day_of_week($jd);
			     
	# Date
	print qq{<tr><td class="bra">$lex{Date}</td>\n};
	print qq{<td class="bla"><input type="text" name="$id:date" value="$date" style="width:10ch;">\n};
	print qq{$dowstd[$dow]</td></tr>\n};

	# Type
	print qq{<tr><td class="bra">$lex{Type}</td>\n};
	print qq{<td class="la"><select name="$id:type"><option>$r{type}</option>\n};
	print qq{<option>$lex{'Statutory Holiday'}</option><option>$lex{Inservice}</option>\n};
	print qq{<option>$lex{School} $lex{Holiday}</option><option>$lex{Other}</option>\n};
	print qq{</select></td></tr>\n};

	# Description
	print qq{<tr><td class="bra">$lex{Description}</td>\n};
	print qq{<td class="la"><input type="text" name="$id:desc1" size="30" value="$r{desc1}"></td></tr>\n};

	# Day In Cycle
	# convert from 0/1 to No/Yes
	if ( $r{dayincycle} ) { $r{dayincycle} = $lex{Yes}; } else { $r{dayincycle} = $lex{No}; }
	print qq{<tr><td class="bra">$lex{'Day In Cycle'}?</td>\n};
	print qq{<td class="la"><select name="$id:dayincycle"><option>$r{dayincycle}</option>\n};
	print qq{<option>$lex{No}</option><option>$lex{Yes}</option></select></td></tr>\n};

	# Fraction Closed
	print qq{<tr><td class="bra">$lex{Fraction} $lex{Closed}</td>\n};
	print qq{<td class="la"><input type="text" Name="$id:dayfraction" size="6" value="$r{dayfraction}">\n};
	print qq{</td></tr>\n};
			     
	# Periods Closed
	print qq{<tr><td class="bra">$lex{Periods} $lex{Closed}</td><td>(if less than full day closed)</td></tr>\n};
	foreach my $periods ( sort keys %periodgroup ) {
	    my $group = $periodgroup{$periods};
	    print qq{<tr><td>Grades $group</td><td>};
	    foreach my $p ( 1.. $periods ) {
		my $checked;
		if ( $periods{$group}{$p} ) { # we have a record for this...
		    $checked = qq{checked="checked"};
		}
		print qq{<input type="checkbox" name="$id:$group:$p" value="1" $checked>P$p };
	    }
	    print qq{</td></tr>\n};
	}

	# spacer.
	print qq{<tr><td colspan="2"><hr></td></tr>\n};
	
    } # end of edit loop

    print qq{</table>};
    print qq{<input type="submit" value="$lex{Save}"></form>\n};
    print qq{</body></html>\n};

    exit;
}
    


#--------------
sub updateEdits {
#--------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    # Get Attendance Periods. %periodgroup is used below
    my %ppdData;
    foreach my $grade (keys %g_ppd ) {
	my $periods = $g_ppd{$grade};
	$ppdData{$periods}{$grade} = 1;
    }

    my %periodgroup; # grade group with minimum and max for each attendance periods.
    foreach my $p ( keys %ppdData ) {
	@gr = sort {$a <=> $b} keys %{ $ppdData{$p} };
	if ($ppdData{$p}{PK}) { $gr[0] = 'PK'; }; # since a K value will show instead.
	$val = "$gr[0]-$gr[-1]";
	$periodgroup{$p} = $val;
    }
    my %gradegroup = reverse %periodgroup;

    
    # Add/Delete/Update Periods in dates_periods
    my %updated;  # a list of records that are already checked for periods, and processed.
    
    foreach my $key ( keys %arr ) {
	my ($id, $field, $period ) = split(':', $key );
#	print "ID:$id Field:$field Period:$period<br>\n";

	if ( $updated{$id} ) { next; }

	# check for a partial day fraction closed;
	if ( $arr{"$id:dayfraction"} > 0.99 ) { # skip
	    next;
	}

	# Get Date
	my $date = $arr{"$id:date"};
#	print "Date:$date<br>\n";
	
	# setup statment handles
	my $sth = $dbh->prepare("insert into dates_periods (date,grades,period) values(?,?,?)");
	my $sth1 = $dbh->prepare("delete from dates_periods where date = ? and grades = ? and period = ?");
	my $sth2 = $dbh->prepare("select count(*) from dates_periods where date = ? and grades = ? and period = ?");
	
	# Read in period records for this id/date
	foreach my $gradegroup ( keys %gradegroup ) {
	    foreach my $period ( 1..$gradegroup{$gradegroup}) {
#		print "ID:$id Group:$gradegroup Period:$period<br>\n";
		
		# Do we have a checkbox for this?
		my $havecheckbox;
		if ( $arr{"$id:$gradegroup:$period"} ) {
		    $havecheckbox = 1;
		}
		
		# Do we have a record for this?
		$sth2->execute( $date, $gradegroup, $period );
		if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		my $count = $sth2->fetchrow;

		if ( $count and not $havecheckbox ) { # Delete the record.
		    $sth1->execute($date, $gradegroup, $period);
		    if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		    print qq{<div>Period record DELETED for $date, Grades $gradegroup, Period $period<br>\n};

		} elsif ( not $count and $havecheckbox ) { # add a record
		    $sth->execute($date, $gradegroup, $period);
		    if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
		    print qq{<div>Period record ADDED for $date, Grades $gradegroup, Period $period<br>\n};
		} # otherwise we have matching records and checkboxes.

		if ( $havecheckbox ) {
		    delete $arr{"$id:$gradegroup:$period"};
		}
		
#		print "Group:$gradegroup Period:$period<br>\n";
	    }
	}

	$updated{$id} = 1;
	    
    }
    # End of Period Updates

#    print qq{<h3>Now Date Updates</h3>\n};
#    foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    
    # Update Date Records
    foreach my $key ( keys %arr ) {
	my ($id, $field, $period ) = split(':', $key );
	if ( $period ) { # ERROR
	    print qq{<h3 style="color:red;">Error - Periods Closed not allowed for full days closed!</h3>\n};
	    print qq{</body></html>\n};
	    exit;
	}

	# Convert dayincycle to Yes = 1, No = 0 format.
	if ( $field eq 'dayincycle' ) {
	    if ( $arr{$key} eq $lex{Yes} ) {
		$arr{$key} = '1';
	    } else {
		$arr{$key} = '0';
	    }
	}

	# update the record
	my $sth = $dbh->prepare("update dates set $field = ? where id = ?");
	if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	$sth->execute( $arr{$key}, $id);
	if ( not $DBI::errstr ) {
	    print qq{<div>Updated $field to $arr{$key}</div>};
	} else {
	    print qq{<h3>$lex{Error}: $DBI::errstr</h3>\n};
	}
    }
	
    
    print qq{<p>[ <a href="$eoypage">$lex{Eoy}</a> |\n};
    print qq{ <a href="$self">$lex{'Add Another'} $lex{Date}</a> ]\n};
    print qq{</p></body></html>\n};

    exit;

}



#------------
sub checkDate {
#------------

    use Time::JulianDay;
    
    my $date = shift;

    my ($y,$m,$d) = split('-',$date);
    
    if ( not $d ) { return 255; }  # wrong format.
    if ( $m > 12 or $m < 1 ) { return 255; }
    if ( $d > 31 or $d < 1 ) { return 255; }

    my $datejd = julian_day( $y, $m, $d );

    my $startjd = julian_day( split('-', $schoolstart ));
    my $endjd = julian_day( split('-', $schoolend ));
    
#    print "DATE:$date - $datejd Start:$schoolstart - $startjd  End:$schoolend - $endjd<br>\n";

    if ( $datejd > $endjd  or $datejd < $startjd ) {
	return 1; # different code for date outside of current year;
    }
    
    return 0;

}
