\n";
print "
| Statistical Reports |
\n";
print "
\n";
}
#-------------
sub doCategory { # Discipline by Category of Infraction.
#-------------
# Get the total records first, the ugly way...
my $sth = $dbh->prepare("select count(*) from discipline where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate')");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
if ( not $total ) {
print $lex{'No Records Found'}. ".\n";
return;
}
# Now get records again, and loop through results, printing.
$sth = $dbh->prepare("select distinct infraction, count(infraction) from discipline where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') group by infraction order by infraction");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
print "
\n";
print "| ". $lex{Infraction}. q{ }. $lex{Category}. " | ". $lex{Count};
print " | ". $lex{Percent}. " | ". $lex{PerDay}. " |
|---|
\n";
# Count the types and numbers.
while ( my @infrac = $sth->fetchrow ) {
my $percent = format_number( ($infrac[1] * 100)/$total , 2, 2) ;
my $perday = format_number( ($infrac[1]/$schooldays), 2, 2);
print "\n";
print "| $infrac[1] | ";
print "$percent\% | $perday |
\n";
}
print "| ". $lex{Totals}. " | \n";
print "$total |
\n";
print "
\n";
}
#--------------
sub doClass { # Discipline by Class
#--------------
# Get the total records first, the ugly way...
my $sth = $dbh->prepare("select count(*) from discipline where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') ");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
if ( not $total ) {
print $lex{'No Records Found'};
return;
}
# Now get records again, and loop through results, printing.
$sth = $dbh->prepare("select distinct studentall.homeroom, count(discipline.date)
from studentall left outer join discipline
on discipline.userid = studentall.studnum where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') group by homeroom");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
print "
";
print "| ". $lex{Homeroom}. " | ". $lex{Count}. " | ";
print $lex{Percent}. " | ". $lex{PerDay}. " | \n";
# Count the types and numbers.
while (my @infrac = $sth->fetchrow){
# Find teacher(s)
$sth1 = $dbh->prepare("select lastname, firstname from staff where
homeroom = '$infrac[0]'");
$sth1->execute;
if ($DBI::errstr) {print "Error: $DBI::errstr"; }
my ($lastname, $firstname) = $sth1->fetchrow;
my $percent = round( ($infrac[1] * 100) /$total, 2) ;
my $perday = round( ($infrac[1]/$schooldays), 2);
print "| $lastname, $firstname ($infrac[0]) | ";
print "$infrac[1] | $percent\% | $perday |
\n";
}
print "| ". $lex{Totals}. " |
$total |
\n";
print "
\n";
}
#--------------
sub doGrade { # Discipline by Grade
#--------------
# Get the total records first, the ugly way...
my $sth = $dbh->prepare("select count(*) from discipline where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') ");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
if ( not $total ) {
print $lex{'No Records Found'}. ".\n";
return;
}
# Now get records again, and loop through results, printing.
$sth = $dbh->prepare("select distinct studentall.grade, count(discipline.date)
from studentall left outer join discipline
on discipline.userid = studentall.studnum where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') group by grade");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
print "
";
print "| ". $lex{Grade}. " | ". $lex{Count}. " | ";
print $lex{Percent}. " | ". $lex{PerDay}. " | \n";
# Count the types and numbers.
while ( my @infrac = $sth->fetchrow ) {
my $percent = round( (($infrac[1] * 100)/$total), 2);
my $perday = round( ($infrac[1]/$schooldays), 2);
print "| $infrac[0] | ";
print "$infrac[1] | $percent\% | $perday |
\n";
}
print "| ". $lex{Totals}. " | \n";
print "$total |
\n";
print "
\n";
}
#--------------
sub doInfraction { # Discipline by Infraction
#--------------
print "
". $lex{Infraction}. ": $infraction
\n";
# Get the total records first, the ugly way...
my $sth = $dbh->prepare("select count(*) from discipline where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') and
infraction = '$infraction'");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
if (not $total){
print $lex{'No Records Found'}. ".\n";
return;
}
# Now get records again, and loop through results, printing.
$sth = $dbh->prepare("select distinct studentall.studnum,
count(discipline.date) as counter
from studentall left outer join discipline
on discipline.userid = studentall.studnum where
to_days(discipline.date) >= to_days('$startdate') and
to_days(discipline.date) <= to_days('$enddate') and
infraction = '$infraction'
group by studnum
order by counter desc, studentall.lastname, studentall.firstname");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
print "
";
print "| ". $lex{Student}. " | ". $lex{Count}. " | ". $lex{Percent};
print " | ". $lex{PerDay}. " | \n";
# Count the types and numbers.
while ( my @infrac = $sth->fetchrow ) {
# Find student name
$sth1 = $dbh->prepare("select lastname, firstname from studentall
where studnum = ?");
$sth1->execute( $infrac[0] );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname) = $sth1->fetchrow;
my $percent = round( (($infrac[1] * 100)/$total), 2);
my $perday = round( ($infrac[1]/$schooldays), 2);
print "| \n";
print "$lastname, $firstname ($infrac[0]) | \n";
print "$infrac[1] | ";
print "$percent\% | $perday |
\n";
}
print "| Totals |
$total |
\n";
print "
\n";
}
#---------------
sub calcTermDays {
#---------------
# this calculates the number of schooldays in a date range
# taking school holidays into account.
my ($startdate, $enddate) = @_;
# It likes it in yyyymmdd format...
$startdate =~ s/-//g; # strip out hyphens.
$enddate =~ s/-//g;
if (length($startdate) == 6){ # add 20 to date
$startdate = '20'.$startdate;
}
if (length($enddate) == 6){ # add 20 to date
$enddate = '20'.$enddate;
}
$end = new Date::Business(DATE=>$enddate);
$start = new Date::Business(DATE=>$startdate);
$schooldays = $end->diffb($start,'prev','next');
$schooldays++;
# We now have the number of school days in current term
# excluding holidays/pd days.
# Now find holidays
$sth = $dbh->prepare("select * from dates where
to_days(date) >= to_days('$startdate') and
to_days(date) <= to_days('$enddate')");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die;}
$holidays = $sth->rows;
# Number of holidays in current term.
$termdays = $schooldays - $holidays;
return $termdays;
}