#!/usr/bin/perl
# Copyright 2001-2022 Leslie Richardson
# This file is part of Open Admin for Schools.
# Get Year-Month and export attendance
my %lex = ('Error' => 'Error',
'Main' => 'Main',
'Attendance' => 'Attendance',
'Month' => 'Month',
'Continue' => 'Continue',
'Select' => 'Select',
'Reason' => 'Reason',
'Missing' => 'Missing',
'Value' => 'Value',
'WD' => 'WD',
'Period' => 'Period',
'Database' => 'Database',
);
my $self = 'sdsexport.pl';
use DBI;
use CGI;
use Cwd;
use Time::JulianDay;
use Number::Format qw(:all);
use Text::CSV_XS;
# Get current dir so know what path for config files.
my $configpath;
my $teachermode;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
$teachermode = 1;
$configpath = '..'; # go back one to get to etc.
} else {
$configpath = '../..'; # go back two to get to etc.
}
# only load passwords and users
eval require "$configpath/etc/admin.conf.root";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
eval require "$configpath/lib/libattend.pl";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
eval require "$configpath/lib/libschedule.pl";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
my $dbtype = 'mysql';
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
# Load Configuration Variables;
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";
}
}
=head
my $sth = $dbh->prepare("select id, datavalue from conf_system where dataname = ?");
foreach my $field ( css ) {
$sth->execute( $field );
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";
}
}
}
=cut
# Teachermode
if ( $teachermode ) { # running on teacher site
$css = $tchcss;
$homepage = $tchpage;
$downloaddir = $tchdownloaddir;
$webdownloaddir = $tchwebdownloaddir;
}
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
# Set Date
my @tim = localtime(time);
my $cyear = @tim[5] + 1900;
my $cmonth = @tim[4] + 1;
my $cday = @tim[3];
my $currdate = "$cyear-$cmonth-$cday";
my $currjd = julian_day( split('-', $currdate) );
my $curryrmo = "$cyear-$cmonth";
# Load Remote database
my $dbhr;
if ( $arr{db} ) { # from start page
my $db = $arr{db};
my $dsn = "DBI:mysql:database=$db;host=$remotehost";
$dbhr = DBI->connect($dsn,$remoteuser,$remotepassword);
}
my $title = "$lex{Attendance} Export - SDS";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ $lex{Main} |\n};
print qq{ $lex{Attendance} ]\n};
print qq{
$title
\n};
if ( not $arr{page} or $arr{page} == 2 ) { # 2 value for remote processing.
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
if ( $arr{previousyear} ) {
selectPreviousYear();
}
showReport();
}
#---------------------
sub selectPreviousYear {
#---------------------
# Get remote databases
my $remotedbase = 'information_schema';
my $dsnr = "DBI:mysql: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 "Match! $dbase - $db \n";
$remotedb{$db} = 1;
}
}
print qq{\n};
print "\n";
exit;
} # end of selectPreviousYear
#----------------
sub showStartPage {
#----------------
# Setup Year-Months.
my @months;
my %months;
my $database = $dbase;
if ( $dbhr ) { # we have a remote database instead
$database = $dbhr;
# load configuration values for this database.
my $sth = $dbhr->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 ($sy, $sm, $sd) = split('-', $schoolstart); # schoolstart is global var from config.
my $yrmo = "$sy-$sm";
push @months, $yrmo;
$months{$yrmo} = "$s_month[$sm]-$sy";
for my $i (1..10) {
my $mo = $sm + $i;
my $yr = $sy;
if ( $mo > 12 ) {
$mo = $mo - 12;
$yr++;
}
my $yrmo = "$yr-$mo";
push @months, $yrmo;
$months{$yrmo} = "$s_month[$mo]-$yr";
}
print qq{\n};
print qq{