#!/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}. ": $@
\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$lex{Edit}/$lex{Delete} $lex{Dates}\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{Eoy} ]\n}; print qq{

$lex{Edit}/$lex{Delete} $lex{Dates}

\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{
\n}; print qq{\n}; print qq{\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{\n}; print qq{}; print qq{\n}; $first = 0; } print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; } if ( $first ) { print qq{

$lex{Dates} $lex{'Not Found'}

\n}; } else { print qq{
$lex{Date}$lex{Description}$lex{Type}$lex{Closed}
$lex{Frac}		EditDelete
$newdate$r{desc1}$r{type}$r{dayfraction}EditDelete
\n}; } print qq{\n\n}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub delEditDates { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\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{

Error: Record ID not found for deletion

\n}; print qq{\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{

Error: Date not found for deletion

\n}; print qq{\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{

Date Closed record for $date deleted

\n}; } if ( not %arr ) { # end of any changes if only deletes. print qq{\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}
\n"; # } # Start Form for Edit print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\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{

Error: Edit action not found. Action is: $action

\n}; print qq{\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{\n}; print qq{\n}; # Type print qq{\n}; print qq{\n}; # Description print qq{\n}; print qq{\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{\n}; print qq{\n}; # Fraction Closed print qq{\n}; print qq{\n}; # Periods Closed print qq{\n}; foreach my $periods ( sort keys %periodgroup ) { my $group = $periodgroup{$periods}; print qq{\n}; } # spacer. print qq{\n}; } # end of edit loop print qq{
$lex{Date}\n}; print qq{$dowstd[$dow]
$lex{Type}
$lex{Description}
$lex{'Day In Cycle'}?
$lex{Fraction} $lex{Closed}\n}; print qq{
$lex{Periods} $lex{Closed}(if less than full day closed)
Grades $group}; foreach my $p ( 1.. $periods ) { my $checked; if ( $periods{$group}{$p} ) { # we have a record for this... $checked = qq{checked="checked"}; } print qq{P$p }; } print qq{
}; print qq{
\n}; print qq{\n}; exit; } #-------------- sub updateEdits { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\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
\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
\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
\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{
Period record DELETED for $date, Grades $gradegroup, Period $period
\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{
Period record ADDED for $date, Grades $gradegroup, Period $period
\n}; } # otherwise we have matching records and checkboxes. if ( $havecheckbox ) { delete $arr{"$id:$gradegroup:$period"}; } # print "Group:$gradegroup Period:$period
\n"; } } $updated{$id} = 1; } # End of Period Updates # print qq{

Now Date Updates

\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Update Date Records foreach my $key ( keys %arr ) { my ($id, $field, $period ) = split(':', $key ); if ( $period ) { # ERROR print qq{

Error - Periods Closed not allowed for full days closed!

\n}; print qq{\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{
Updated $field to $arr{$key}
}; } else { print qq{

$lex{Error}: $DBI::errstr

\n}; } } print qq{

[ $lex{Eoy} |\n}; print qq{ $lex{'Add Another'} $lex{Date} ]\n}; print qq{

\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
\n"; if ( $datejd > $endjd or $datejd < $startjd ) { return 1; # different code for date outside of current year; } return 0; }