#!/usr/bin/perl # Copyright 2001-2018 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. use DBI; use CGI; use Time::JulianDay; my %lex = ('View' => 'View', 'Eoy' => 'Eoy', 'Main' => 'Main', 'Dates' => 'Dates', 'School Year' => 'School Year', 'School Start' => 'School Start', 'School End' => 'School End', 'Term' => 'Term', 'Date' => 'Date', 'Type' => 'Type', 'Description' => 'Description', 'Track' => 'Track', 'Error' => 'Error', 'Grade' => 'Grade', 'Fraction' => 'Fraction', 'Cycle' => 'Cycle', 'Day' => 'Day', 'No' => 'No', 'Yes' => 'Yes', 'Closed' => 'Closed', 'Open' => 'Open', 'Start' => 'Start', 'End' => 'End', 'Total' => 'Total', 'Continue' => 'Continue', 'Database' => 'Database', 'Remote' => 'Remote', 'Select' => 'Select', ); my %dowclosed = ('0' => 1, '6' => 1); # Day 0 is Sunday, Day 6 is Saturday my $self = 'dateview_past.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); # Load Main admin configuration ('admin') 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}: $@
\n"; die "$lex{Error}: $@\n"; } } my $title = "$lex{View} $lex{'School Year'} - $schoolname"; print qq{$doctype\n$title $chartype\n\n}; print qq{[ $lex{Main} | \n}; print qq{$lex{Eoy} ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; showSchoolYear( $dbhr ); } #---------------- sub showStartPage { #---------------- # Get remote databases my $remotedbase = 'information_schema'; my $dsnr = "DBI:$dbtype: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
\n}; if ( $db =~ m/$dbase/ ) { # print qq{Match! $dbase - $db
\n}; $remotedb{$db} = 1; } } print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; foreach my $db ( sort keys %remotedb ) { my $temp = $db; $temp =~ s/$dbase//; # strip database name; my $year = $temp - 1; # previous year; $year = qq{$year-$temp}; # now in 2018-2019 format print qq{\n}; } print qq{\n}; print qq{
$lex{Select} $lex{Remote} $lex{Database}
$year ($db)
\n}; print qq{\n}; exit; } #----------------- sub showSchoolYear { #----------------- my $db = $arr{db}; # Load Remote database my $dsn = "DBI:$dbtype:database=$db;host=$remotehost"; my $dbh = DBI->connect($dsn,$remoteuser,$remotepassword); # Load configuration values from that year. 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}: $@
\n"; die "$lex{Error}: $@\n"; } } # populate jdclosed with dates from dates table; used by calcTermDays; my %jdclosed; my $sth = $dbh->prepare("select date, dayfraction from dates where date is not NULL and date != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $date, $dayfraction ) = $sth->fetchrow ) { my $jd = julian_day( split( /-/, $date ) ); $jdclosed{$jd} = $dayfraction; } # Print Term Dates stored in configuration system. print qq{
\n}; # left side div print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{'School Year'}$schoolyear
$lex{'School Start'}$schoolstart
$lex{'School End'}$schoolend

\n}; # Start # Print Terms # Loop through all tracks. foreach my $trk ( sort keys %g_MTrackTerm ) { print qq{\n}; print qq{\n}; print qq{\n}; # Print Terms in this Track my $totaldays; print qq{\n}; foreach my $trm ( sort keys %{ $g_MTrackTerm{$trk} } ) { print qq{}; print qq{}; print qq{\n}; my $val = calcTermDays($g_MTrackTerm{$trk}{$trm}{start}, $g_MTrackTerm{$trk}{$trm}{end}, \%jdclosed ); my ($open,$closed) = split(':', $val); $totaldays += $open; print qq{\n}; } print qq{\n}; print qq{
$lex{Track} $trk - $g_TrackDisplay{$trk}
$lex{Grade} }; foreach my $grade ( sort {$a <=> $b} keys %g_MTrackTermType ) { if ( $g_MTrackTermType{$grade} eq $trk ) { print qq{$grade } } } print qq{
$lex{Start}$lex{End}$lex{Open}$lex{Closed}
$lex{Term} $trm ($g_TermDisplay{$trk}{$trm})$g_MTrackTerm{$trk}{$trm}{start}$g_MTrackTerm{$trk}{$trm}{end}$open$closed
$lex{Total}$totaldays
\n}; } print qq{
\n}; # Print the Stored Dates (in Dates Table) print qq{\n}; print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; my $sth = $dbh->prepare("select * from dates order by date"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $id, $date, $type, $desc1, $desc2, $dayincycle, $dayfraction ) = $sth->fetchrow ) { my ($year, $month, $day ) = split(/-/, $date); my $newdate = $year .'-'. $s_month[$month]. q{-}. $day; my $jd = julian_day( split( /-/, $date ) ); my $dow = day_of_week($jd) + 1; if ( not $dayincycle ) { $dayincycle = $lex{No}; } else { $dayincycle = $lex{Yes}; } print qq{\n}; print qq{\n}; } print qq{
$lex{Dates} $lex{Closed}
$lex{Date}$lex{Day}$lex{Type}$lex{Description}$lex{Cycle}
$lex{Day}		$lex{Day}
$lex{Fraction}
$newdate$dow[$dow]$type$desc1$dayincycle$dayfraction
\n}; } # end of showSchoolYear #--------------- sub calcTermDays { #--------------- # passed a start date and an end date for a term, and a ref to a # hash storing the closure dates in julian day format, it will # return a count of the days open in the month. # outline: convert start/end dates to jd. Loop over all days. Use modulus function to ident weekend days. my ($startdate, $enddate, $closeref ) = @_; my %jdclosed = %$closeref; =head print qq{JDClosed
\n}; foreach my $key ( sort keys %jdclosed ) { print qq{K:$key V:$jdclosed{$key}
\n}; } print qq{End JDClosed
\n}; =cut my $startjd = julian_day(split(/-/,$startdate)); my $endjd = julian_day(split(/-/,$enddate)); my %modclosed; # modulus for days closed foreach my $inc ( 0..6 ) { my $testjd = $startjd + $inc; my $dow = day_of_week($testjd); if ( $dowclosed{$dow} ) { # $testjd is a closed dow my $mod = $testjd % 7; $modclosed{$mod} = 1; } } # foreach my $d ( sort keys %modclosed ) { # print qq{Closed Mod: $d
\n}; # } my ($daysopen, $daysclosed); foreach my $jd ( $startjd..$endjd ) { my $dow = day_of_week( $jd ); my $mod = $jd % 7; if ( $modclosed{$mod} ) { # closed for weekend next; } # check if a day closed my $cl = $jdclosed{ $jd }; if ( $cl ) { # this gives us the fraction closed $daysclosed += $cl; # only add on the fraction of day open $daysopen += (1 - $cl ); # print qq{
INSIDE
\n}; } else { $daysopen += 1; } # print qq{JD:$jd Count:$daycount
\n}; } # print qq{ST:$startdate END:$enddate Count:$daycount
\n}; return "$daysopen:$daysclosed"; }