#!/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}. ": $@<br>\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<html><head><title>$title</title>\n};

print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style type="text/css">body { margin: 0 2em;}</style>\n};
print qq{$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1><div>$currdate</div>\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{<h3>$lex{'Students'}: $total</h3>\n};

print qq{<table border="1" cellspacing="0" cellpadding="3" style="float:left;margin:0.5em;"><tr>\n};
print qq{<th>$lex{Grade}</th><th>$lex{Ethnicity}</th>};
print qq{<th>$lex{Gender}</th><th>$lex{Count}</th></tr>\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{<tr><td>$grade</td><td>$ethnic</td><td>$sex</td><td>$count</td></tr>\n};
	$total += $count;
    }
    print qq{<tr style="background-color:#DDD;"><td colspan="3" class="ra">};
    print qq{$lex{Total}</td><td>$total</td></tr>\n};
    
}
print qq{</table>\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{<table border="1" cellspacing="0" cellpadding="3" style="float:left;margin:0.5em;">\n};
print qq{<tr><th>$lex{'Ethnic Category'}</th><th>$lex{Count}</th>\n};
print qq{<th>$lex{Percent}</th></tr>\n};


foreach my $key (keys %ethnic) {
    my $percent = ( $ethnic{$key} / $total ) * 100;
    print qq{<tr><td>$key</td><td>$ethnic{$key}</td><td class="ra">};
    print round( $percent, 2 );
    print qq{%</td></tr>\n};
}
print qq{</table>\n};

print qq{</body></html>\n};
