\n";
mkSearchForm();
print "\n";
#-----------------
sub mkSearchForm {
#-----------------
print "\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), sum(demerit) 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; }
my $first = 1;
my $demerittotal;
# Count the types and numbers.
while ( my @infrac = $sth->fetchrow ) {
if ( $first ) {
print qq{
\n};
}
#----------
sub doClass { # Discipline by Class
#----------
# Get the total records first.
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 qq{
\n};;
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 as s, staff_multi as sm
where s.userid = sm.userid and field_name = 'homeroom' and field_value = ?");
$sth1->execute( $infrac[0] );
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 qq{
$lex{Totals}
$total
\n};
print "
\n";
}
#----------
sub doGrade { # Discipline by Grade
#----------
# Get the total records first, for the total for percentages.
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 qq{
\n};
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 qq{
$infrac[0]
$infrac[1]
\n};
print qq{
$percent\%
$perday
\n};
}
print qq{
$lex{Totals}
\n};
print qq{
$total
\n};
print "
\n";
}
#--------------
sub doInfraction { # Discipline by Infraction
#--------------
#foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
my %studinf; # student infractions data structure.
my %studname;
my %infractions;
if ( $arr{type} eq 'all' ) {
print "
$lex{Infraction}: $lex{All}
\n";
# Get All infractions.
my $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') and infraction is not NULL and infraction != ''
group by infraction");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my ($inf, $count ) = $sth->fetchrow ) {
$infractions{$inf} = $count;
}
} else { # individual infraction
print "
$lex{Infraction}: $infraction
\n";
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 = ? ");
$sth->execute( $infraction );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
$total = $sth->fetchrow;
if ( not $total ){
print qq{
$lex{'No Records Found'}
\n};
return;
} else {
$infractions{$infraction} = $total;
}
}
# We now have infractions and counts in %infractions.
foreach my $inf ( sort keys %infractions ) {
my $inftotal = $infractions{$inf};
my $dtotal; # demerit total.
# Get records for each infraction
$sth = $dbh->prepare("select distinct userid, count(date), sum(demerit)
from discipline d left outer join studentall s on d.userid = s.studnum where
to_days(d.date) >= to_days('$startdate') and
to_days(d.date) <= to_days('$enddate') and
d.infraction = ? group by userid order by s.lastname, s.firstname");
$sth->execute( $inf ) ;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
while ( my ($studnum, $count, $demerits ) = $sth->fetchrow ) {
# store student information in structure.
$studinf{$studnum}{$inf}{'count'} = $count;
$studinf{$studnum}{$inf}{'demerits'} = $demerits;
$dtotal += $demerits;
if ( $first ) { # put in table header
print qq{
\n};
print qq{
$inf
\n};
print qq{
$lex{Student}
$lex{Count}
$lex{Demerits}
$lex{Percent}
$lex{PerDay}
\n};
$first = 0;
}
# Find student name
$sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
$sth1->execute( $studnum );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname) = $sth1->fetchrow;
if ( not $studname{$studnum} ) {
$studname{$studnum} = "$lastname, $firstname ($studnum)";
}
my $percent = round( (($count * 100)/$inftotal), 2);
my $perday = round( ($count/$schooldays), 2);
print qq{