#!/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}. " $@<br>\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}. " $@<br>\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{<i>$lex{'Closed'}</i>};
}


print qq{<!DOCTYPE html>\n};

print qq{<div style="font-weight:bold;font-size:120%;">Day $cycleday in Cycle</div>\n};

print qq{<form action="/cgi-bin/sasked/qryschident.pl" method="post" style="display:inline;" target="_blank">\n};
print qq{<input type="submit" value="PSN Missing $provcount Dup $provdupcount" };
print qq{title="PSN=Provincial Student Number };
if ( $provdupcount ) { print qq{ Duplicates:$provdupnames }; }
print qq{"></form>\n};



print qq{<form action="/cgi-bin/eoy/resetselect.pl" method="post" style="display:inline;" target="_blank">\n};
print qq{<input type="hidden" value="1" name="page">\n};
print qq{<input type="hidden" value="treaty" name="field">\n};
print qq{<input type="hidden" name="inputtype" value="text">\n};
print qq{<input type="hidden" name="groupvalue" value="">\n};
print qq{<input type="hidden" name="studtable" value="current">\n};
print qq{<input type="hidden" name="grouptype" value="Grade">\n};
print qq{<input type="hidden" name="displayfield" value="ethnic">\n};
print qq{<input type="submit" value="IRS Missing $irscount  Dup $dupcount" };
print qq{title="IRS=Indian Registry System (Treaty Numbers) };
if ( $dupcount ) { print qq{ Duplicates:$dupnames }; }
print qq{"></form>\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{<div style="font-weight:bold;font-size:90%;">$r{title} [$date]</div>\n};
    print qq{<div style="font-size:80%;">$r{description}</div><hr>\n};
}


#----------
sub fmtDate {
#----------
    my ( $year, $mon, $day ) = split '-', shift;
    return qq{$year-$s_month[$mon]-$day};
}

