#!/usr/bin/perl # Copyright 2001-2024 Leslie Richardson # This file is part of Open Admin for Schools. my %lex = ('User Id' => 'User Id', 'Password' => 'Password', 'Duration' => 'Duration', 'Login' => 'Login', 'Log In' => 'Log In', 'Log Out' => 'Log Out', 'min' => 'min', 'No Userid Found' => 'No Userid Found', 'Incorrect Password' => 'Incorrect Password', 'Logged In' => 'Logged In', 'Error' => 'Error', 'Continue' => 'Continue', 'User' => 'User', 'Teacher Administration' => 'Teacher Administration', 'Announcements' => 'Announcements', 'Hover=Full View' => 'Hover on Title to see Full Text', 'View All' => 'View All', 'Logged Out' => 'Logged Out', 'Main' => 'Main', 'No Announcements' => 'No Announcements', ); my $self = 'rptWorkSchool.pl'; use DBI; use CGI; use Number::Format qw(:all); use Time::JulianDay; my $q = CGI->new; my %arr = $q->Vars; print $q->header; eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # get the hash that maps schools to FN's eval require "/opt/openadmin/global/schoolmap.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # get global user/password. eval require "$globdir/global.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $title = qq{First Nation Contract Report}; # Global user access to consultant DB. my $newdbase = 'consultant'; my $dsn = "DBI:$dbtype:dbname=$newdbase"; my $dbh = DBI->connect($dsn,$guser,$gpassword); print qq{\n}; print qq{$title\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # anchor for the top of page print qq{

$title

\n}; my $fn = $schoolmap{$dbase}; # dbase maps to a FN; my $school = $dbase; #print "School:$dbase FN:$fn
\n"; showReport($fn); #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Passed the First Nation as text. # $schoolmap is hash: school => First Nation. my $firstnation = $_[0]; # Get Users from Apache consultant password file. use Apache::Htpasswd; # to read the consultant password file. my $basepath = '/etc/apache2/auth_users'; my $file = 'consultant'; my $ph = new Apache::Htpasswd("$basepath/$file"); my %names; my @users = $ph->fetchUsers(); foreach my $userid ( sort @users ) { my $name = $ph->fetchInfo($userid); $names{$userid} = $name; # print qq{$userid - $name
\n}; } # we can now get work records for this school, in all categories. my %work; my $sth = $dbh->prepare("select * from worklog where school = ?"); $sth->execute( $school ); if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $work{ $school }{ $r{category} } += $r{hours}; } #print qq{

}, ucfirst($school), qq{

\n}; print qq{

$firstnation

\n}; # Display Contract Values my $sth1 = $dbh->prepare("select hours from worklog where school = ? and category = ?"); my $sth = $dbh->prepare("select * from contract where firstnation = ?"); # Table Layout print qq{\n}; print qq{}; print qq{\n}; # \n}; # Load the contract record. $sth->execute( $firstnation ); # which is the first nation. if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }; my $ref = $sth->fetchrow_hashref; my %contract = %r = %$ref; my @category; foreach my $cat ( sort keys %contract ) { if ( $cat eq 'firstnation' or $cat eq 'id' ) { next; } push @category, $cat; } my ($total, $totalused,$totalleft); foreach my $category ( @category ) { my $hoursused; # print qq{S:$school C:$category WORK VAL:$work{$school}{$category}
\n}; # print qq{S:$school C:$category DRIVE VAL:$schoolhr{$school}{$category}
\n}; $hoursused += $work{$school}{$category}; $hoursused += $schoolhr{$school}{$category}; my $daysused = round($hoursused / 8, 1); $totalused += $daysused; my $daysleft = round( $r{$category} - $daysused, 1); $totalleft += $daysleft; print qq{}; print qq{\n}; print qq{\n}; $total += $r{$category}; } # Totals print qq{}; print qq{}; print qq{\n}; print qq{
CategoryContract
Days		Days UsedDays
Remaining
Details
$category$r{$category}$daysused$daysleft
Totals$total$totalused$totalleft
\n}; # Detail Tables for each Category my $sth = $dbh->prepare("select * from worklog where school = ? and category = ? order by date, userid, id"); foreach my $category ( @category ) { my $cathours; my $first = 1; # See if we have any contract values and any work values. $sth->execute( $school, $category); if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; }; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $userid = $r{userid}; if ( $first ) { # setup table # Table Layout print qq{\n}; print qq{}; print qq{}; print qq{\n}; $first = 0; } # Record lines (2) print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; $totalhours += $r{hours}; $cathours += $r{hours}; } if ( not $first ) { my $days = $cathours / 8; $days = round($days, 1); print qq{\n}; print qq{
NameDateSchoolHoursWorkType
}; print qq{$school - $category
$names{$userid} ($userid)$r{date}$r{school}$r{hours}$r{worktype}
$r{description}
}; print qq{Total Category Hours $cathours ($days Days)
\n}; } } # end of category loop, details. print qq{\n}; exit; } # end of showReport();