#!/usr/bin/perl
# Copyright 2001-2019 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.
use DBI;
use CGI;
use Number::Format qw(round);
my %lex = ('Main' => 'Main',
'Students' => 'Students',
'Grade' => 'Grade',
'Ethnicity' => 'Ethnicity',
'Gender' => 'Gender',
'Count' => 'Count',
'Total' => 'Total',
'Error' => 'Error',
'Ethnic Category' => 'Ethnic Category',
'Percent' => 'Percent',
);
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;
my $title = "Ethnicity Report";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ $lex{Main} ]\n};
print qq{
$title
$currdate
\n};
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;
# First get total student count for school
my $sth = $dbh->prepare("select count(*) from student");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
# Now get a grade count and push into array
$sth = $dbh->prepare("select distinct grade from student
where grade != '' order by grade");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $gr = $sth->fetchrow ) {
push @grades, $gr;
}
print qq{
$lex{'Students'}: $total
\n};
print qq{
\n};
print qq{
$lex{Grade}
$lex{Ethnicity}
};
print qq{
$lex{Gender}
$lex{Count}
\n};;
my $sth = $dbh->prepare("select distinct ethnic, sex, count(*) from student
where grade = ? group by ethnic, sex");
foreach my $grade ( sort {$a <=> $b} @grades ) {
$sth->execute($grade);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $total;
while ( my ($ethnic, $sex, $count ) = $sth->fetchrow ) {
print qq{
$grade
$ethnic
$sex
$count
\n};
$total += $count;
}
print qq{
};
print qq{$lex{Total}
$total
\n};
}
print qq{
\n};
# Now do summary table
my $sth1 = $dbh->prepare("select ethnic, count(*) from student
group by ethnic order by ethnic");
$sth1->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($ethnic, $count) = $sth1->fetchrow) {
if (not $ethnic) { $ethnic = qq{'No Data'}; }
$ethnic{$ethnic} = $count;
}
# Print Ethnic Category Table
print qq{
\n};
print qq{
$lex{'Ethnic Category'}
$lex{Count}
\n};
print qq{
$lex{Percent}
\n};
foreach my $key (keys %ethnic) {
my $percent = ( $ethnic{$key} / $total ) * 100;
print qq{