#!/usr/bin/perl # Copyright 2001-2023 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. my %lex = ('Enrollment Summary' => 'Enrollment Summary', 'Main' => 'Main', 'Room' => 'Room', 'Teacher' => 'Teacher', 'Grade' => 'Grade', 'Male' => 'Male', 'Female' => 'Female', 'Young' => 'Young', 'HRm' => 'HRm', 'Total' => 'Total', 'Gr' => 'Gr', 'Totals' => 'Totals', 'Error' => 'Error', 'Continue' => 'Continue', ); my $self = 'rptenrolhroom_past.pl'; use DBI; use CGI; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $mon++; $wday++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # 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 = qq{$schoolname Year End $lex{'Enrollment Summary'}}; print qq{$doctype\n$title\n}; print qq{$chartype\n}; print qq{\n}; print qq{[ $lex{Main} | \n}; print qq{SSP ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } else { delete $arr{page}; showReport(); } #---------------- sub showStartPage { #---------------- # 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 qq{Match! $dbase - $db
\n}; $remotedb{$db} = 1; } } print qq{\n}; print qq{\n}; exit; } #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print "K:$key VAL:$arr{$key}
\n"; } my $temp = $arr{db}; $temp =~ s/$dbase//; # strip database name; my $year = $temp - 1; # previous year; $year = qq{$year-$temp}; # now in 2018-2019 format print qq{

School Year $year

\n}; # Get the Grades my %grades; my $sth = $dbhr->prepare("select distinct grade from student where grade is not NULL and grade != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $grade = $sth->fetchrow ) { $grades{$grade} = 1; } my @grades = ( sort { $a <=> $b } keys %grades ); #foreach my $gr ( @grades ) { print "G:$gr
\n"; } $sth = $dbhr->prepare("select homeroom, grade, sex, youngest from student where grade = ? order by homeroom, lastname,firstname"); print qq{\n}; print qq{\n\n}; print qq{\n}; my $youngest = 0; my $youngtot = 0; my $boytotal = 0; my $girltotal = 0; my $boycount = 0; my $girlcount = 0; my $boygrade = 0; my $girlgrade = 0; my $curroom = -1; $currgrade = -1; my $first = 1; foreach my $grade ( @grades ) { $sth->execute( $grade ); if ($DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $hroom, $gr, $sex, $young ) = $sth->fetchrow ) { $oldroom = $curroom; $oldgrade = $currgrade; $curroom = $hroom; $currgrade = $gr; if (($oldroom ne $curroom and not $first ) or ( $oldroom eq $curroom and $oldgrade ne $currgrade and not $first )) { # we have a new room (or same room but different grade) $hrtotal = $boycount + $girlcount; my ($sal, $firstname, $lastname); if ( $oldroom ){ # If oldroom present, find it's teacher. my $sth1 = $dbhr->prepare("select sal, firstname, lastname from staff as s, staff_multi as sm where s.userid = sm.userid and sm.field_name = ? and sm.field_value = ?"); $sth1->execute( 'homeroom', $oldroom ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } ($sal, $firstname, $lastname) = $sth1->fetchrow; } # Print out teacher name, totals for this room and then reset the variables. print qq{}; print qq{\n}; print qq{}; print qq{\n}; $girlcount = 0; $boycount = 0; $youngest = 0; } if ( $oldgrade ne $currgrade and not $first ) { # we have a new grade also $grtotal = $boygrade + $girlgrade; print qq{\n}; print qq{\n}; print qq{\n}; $girlgrade = 0; $boygrade = 0; } $first = 0; if ( "\U$sex" eq "F") { $girlcount++; $girltotal++; $girlgrade++; } else { $boycount++; $boytotal++; $boygrade++; } if ( $young ) { # if has any value. $youngest++; $youngtot++; } } # End of Main Loop } my $total = $boycount + $girlcount; if ( $curroom ) { my $sth1 = $dbhr->prepare("select sal, firstname, lastname from staff as s, staff_multi as sm where s.userid = sm.userid and sm.field_name = ? and sm.field_value = ?"); $sth1->execute('homeroom', $curroom ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($sal, $firstname, $lastname) = $sth1->fetchrow; print qq{\n}; print qq{\n}; print qq{\n}; } my $grtotal = $boygrade + $girlgrade; print qq{\n}; print qq{\n}; my $grandtotal = $boytotal + $girltotal; print qq{}; print qq{\n}; print qq{\n}; print qq{

$lex{Room} - $lex{Teacher}	}; print qq{$lex{Grade}	$lex{Male}	$lex{Female}	$lex{Young}	$lex{HRm} $lex{Total}	$lex{Gr} $lex{Total}
$oldroom - $sal $firstname $lastname	$oldgrade	$boycount	$girlcount	$youngest	$hrtotal
	$oldgrade	$boygrade	$girlgrade			$grtotal
$curroom - $sal $firstname $lastname	$currgrade	$boycount	$girlcount	$youngest	$total
	$currgrade	$boygrade	$girlgrade			$grtotal
$lex{Totals}		$boytotal	$girltotal	$youngtot		$grandtotal

\n}; exit; }

$lex{Select} $lex{'School Year'}
$year ($db)