#!/usr/bin/perl
# Copyright 2001-2021 Leslie Richardson
# This file is part of Open Admin for Schools.
# showStatus - called by iframe on main page. Show missing IRS, Duplicates,
# Missing ASN, and Status Warnings and Advice.
use DBI;
use CGI;
use Time::JulianDay;
my %lex = ('Closed' => 'Closed',
);
eval require "../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. " $@
\n";
die $lex{Error}. " $@\n";
}
my $q = CGI->new;
print $q->header;
my $dbtype = 'mysql';
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
# Get Missing Provincial Numbers
my $sth = $dbh->prepare("select count(*) from student where provnum = '' or provnum is NULL");
$sth->execute;
if ( $DBI::errstr ) { print DBI::errstr; die $DBI::errstr; }
my $provcount = $sth->fetchrow;
my $sth1 = $dbh->prepare("select lastname, firstname from studentall where provnum = ?");
# PSN Duplicates
my %provnumbers;
my $provdupcount = 0;
my $sth = $dbh->prepare("select provnum, studnum from student
where provnum is not NULL and provnum != '' order by treaty");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $provdupnames;
while ( my ($provnum, $studnum) = $sth->fetchrow ) {
if ( $provnumbers{$provnum} ) {
$provdupcount++;
# Get the Duplicate Names
$sth1->execute($provnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($lastname, $firstname) = $sth1->fetchrow ) {
$provdupnames .= " $firstname $lastname ($provnum)/";
}
}
$provnumbers{$provnum} = $studnum;
}
# Get Missing IRS numbers (Indian Registry System)
my $sth = $dbh->prepare("select count(*) from student
where (treaty = '' or treaty is NULL) and ethnic = 'Status Native' ");
$sth->execute;
if ( $DBI::errstr ) { print DBI::errstr; die $DBI::errstr; }
my $irscount = $sth->fetchrow;
my $sth1 = $dbh->prepare("select lastname, firstname from studentall where treaty = ?");
# IRS Duplicates
my %irsnumbers;
my $dupcount = 0;
my $sth = $dbh->prepare("select treaty, studnum from student
where treaty is not NULL and treaty != '' and ethnic = 'Status Native'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $dupnames;
while ( my ($irsnum, $studnum) = $sth->fetchrow ) {
if ( $irsnumbers{$irsnum} ) {
$dupcount++;
# Get the Duplicate Names
$sth1->execute($irsnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($lastname, $firstname) = $sth1->fetchrow ) {
$dupnames .= " $firstname $lastname ($irsnum)/";
}
}
$irsnumbers{$irsnum} = $studnum;
}
# Set Date
my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
my $currdate = "$year-$month-$day";
# find Day In cycle.
eval require "../lib/libschedule.pl";
if ( $@ ) {
print $lex{Error}. " $@
\n";
die $lex{Error}. " $@\n";
}
my $cycleday = findDayInCycle($currdate, $dbh);
# check if closed.
my $sth = $dbh->prepare("select * from dates where date = ?");
$sth->execute( $currdate );
if ( $DBI::errstr ) { print DBI::errstr; die $DBI::errstr; }
my $ref = $sth->fetchrow_hashref;
my %r = %$ref;
if ( $r{dayfraction} > 0.99 or $cycleday == 0 ) { # closed all day
$cycleday = qq{$lex{'Closed'}};
}
print qq{\n};
print qq{Day $cycleday in Cycle
\n};
print qq{\n};
print qq{\n};
# Display OA updates
my $sth = $dbh->prepare("select * from oaupdate order by date desc, id desc");
$sth->execute;
if ( $DBI::errstr ) { print DBI::errstr; die $DBI::errstr; }
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
my $date = fmtDate($r{date});
print qq{$r{title} [$date]
\n};
print qq{$r{description}
\n};
}
#----------
sub fmtDate {
#----------
my ( $year, $mon, $day ) = split '-', shift;
return qq{$year-$s_month[$mon]-$day};
}