\n};
mkSearchForm();
print qq{\n};
#-----------------
sub mkSearchForm {
#-----------------
# Setup Calendar
print qq{\n};
print qq{\n};
print qq{\n};
print qq{\n};
print qq{\n};
print qq{\n};
} # end of mkSearchForm
#-------------
sub doCategory { # Discipline by Category of Infraction.
#-------------
# foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}\n}; }
print qq{
Infractions by Category
\n};
# Get the total records first, the ugly way...
my $sth = $dbh->prepare("select count(*) from disc_event where
to_days(date) >= to_days('$startdate') and
to_days(date) <= to_days('$enddate')");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $total = $sth->fetchrow;
if ( not $total ) {
print qq{
$lex{'No Records Found'}
\n};
return;
}
# Now get records again, and loop through results, printing.
$sth = $dbh->prepare("select distinct infraction, count(infraction)
from disc_event where
to_days(date) >= to_days('$startdate') and
to_days(date) <= to_days('$enddate')
group by infraction order by infraction");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
# Count the types and numbers.
while ( my ($infrac,$count,$demerits) = $sth->fetchrow ) {
if ( $first ) {
print qq{
\n};
} # end of doCategory
#-------------
sub doHomeroom { # Discipline Event by Homeroom
#-------------
print qq{
Infractions by Homeroom
\n};
# Get the event records
my $sth = $dbh->prepare("select * from disc_event d where
to_days(d.date) >= to_days('$startdate') and
to_days(d.date) <= to_days('$enddate') ");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my %events; # events{id} = ref;
while ( my $ref = $sth->fetchrow_hashref ) {
$events{$ref->{id}} = $ref;
}
if ( not %events ) {
print $lex{'No Records Found'};
return;
}
my $totalevents = keys %events;
# Find all students associated with this event
my $sth1 = $dbh->prepare("select * from disc_ident where eventid = ?");
my $sth2 = $dbh->prepare("select * from studentall where studnum = ?");
# Find the students, and their homerooms.
my %homerooms; # homerooms{hr}{studnum}{eventid};
foreach my $eid ( keys %events ) {
# Get their ident.
$sth1->execute($eid);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $iref = $sth1->fetchrow_hashref; # ident ref
my $studnum = $iref->{studnum};
# print "SN:$studnum \n";
# Get student record
$sth2->execute($studnum);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $sref = $sth2->fetchrow_hashref; # student ref
my $hr = $sref->{homeroom};
if ( not $hr ) { $hr = 'None'; }
$homerooms{$hr}{$studnum}{$eid} = 1;
}
# use Data::Dumper;
# print Dumper %homerooms;
print qq{
\n};;
print qq{
$lex{Homeroom}
Students
Events
};
print qq{
$lex{Percent}
$lex{PerDay}
\n};
my $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 = ?");
# Loop over homerooms counting students and their events;
foreach my $hr ( sort keys %homerooms ) {
# print "HR: $hr \n";
my ($studcount, $eventcount);
foreach my $sn ( sort keys %{ $homerooms{$hr} } ) {
# print "SN:$sn \n";
$eventcount += keys %{ $homerooms{$hr}{$sn} };
$studcount++;
}
# Find homeroom teacher(s), if any
$sth1->execute( $hr );
if ($DBI::errstr) {print qq{Error: $DBI::errstr}; }
my ($lastname, $firstname) = $sth1->fetchrow;
my $name;
if ( $lastname ) {
$name = qq{$lastname, $firstname};
}
my $percent = round( ( $eventcount * 100) /$totalevents, 2) ;
my $perday = round( ($eventcount/$schooldays), 2);
print qq{
$name $hr
};
print qq{
$studcount
$eventcount
$percent\%
$perday
\n};
}
print qq{
Event Totals
};
print qq{
$totalevents
\n};
print qq{
\n};
}
#----------
sub doGrade { # Discipline by Grade
#----------
print qq{
Infractions by Grade
\n};
# Get the event records
my $sth = $dbh->prepare("select * from disc_event d where
to_days(d.date) >= to_days('$startdate') and
to_days(d.date) <= to_days('$enddate') ");
$sth->execute;
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my %events; # events{id} = ref;
while ( my $ref = $sth->fetchrow_hashref ) {
$events{$ref->{id}} = $ref;
}
if ( not %events ) {
print $lex{'No Records Found'};
return;
}
my $totalevents = keys %events;
# Find all students associated with this event
my $sth1 = $dbh->prepare("select * from disc_ident where eventid = ?");
my $sth2 = $dbh->prepare("select * from studentall where studnum = ?");
# Find the students, and their grades
my %grades; # grades{gr}{studnum}{eventid};
foreach my $eid ( keys %events ) {
# Get their ident.
$sth1->execute($eid);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $iref = $sth1->fetchrow_hashref; # ident ref
my $studnum = $iref->{studnum};
# print "SN:$studnum \n";
# Get student record
$sth2->execute($studnum);
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $sref = $sth2->fetchrow_hashref; # student ref
my $gr = $sref->{grade};
if ( not $hr ) { $hr = 'None'; }
$grades{$gr}{$studnum}{$eid} = 1;
}
# use Data::Dumper;
# print Dumper %homerooms;
print qq{
\n};;
print qq{
$lex{Grade}
Students
Events
};
print qq{
$lex{Percent}
$lex{PerDay}
\n};
# Loop over grades counting students and their events;
foreach my $gr ( sort {$a <=> $b} keys %grades ) {
# print "GR: $gr \n";
my ($studcount, $eventcount);
foreach my $sn ( sort keys %{ $grades{$gr} } ) {
# print "SN:$sn \n";
$eventcount += keys %{ $grades{$gr}{$sn} };
$studcount++;
}
my $percent = round( ( $eventcount * 100) /$totalevents, 2) ;
my $perday = round( ($eventcount/$schooldays), 2);
print qq{
$gr
};
print qq{
$studcount
$eventcount
};
print qq{
$percent\%
$perday
\n};
}
print qq{
Event Totals
};
print qq{
$totalevents
\n};
print qq{
\n};
} # end of Do Grade
#---------------
sub doInfraction { # Discipline by Infraction; called as secondary to category
#---------------
# foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
# passed: start date, enddate, infrac, type.
if ( $arr{infrac} ) { # individual infraction passed.
# print qq{
$lex{Infraction}: $infraction
\n};
my $sth1 = $dbh->prepare("select * from disc_ident where eventid = ?");
my $sth2 = $dbh->prepare("select * from disc_action where eventid = ? and studnum = ?");
my $sth3 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
my $sth4 = $dbh->prepare("select lastname, firstname from staff where userid = ?");
my $sth = $dbh->prepare("select * from disc_event d where
to_days(d.date) >= to_days('$startdate') and
to_days(d.date) <= to_days('$enddate') and
infraction = ? order by date desc");
$sth->execute( $infraction );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
my $date = formatDate($r{date});
my $time = formatTime($r{time});
if ( $first ) { # put in table header
print qq{
\n};
print qq{
};
print qq{$infraction
\n};
print qq{
Event
Students/Actions
Description
\n};
$first = 0;
}
# Get Staff Name
$sth4->execute( $r{author} );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($ln,$fn) = $sth4->fetchrow;
print qq{
$date $time $r{location} };
print qq{$fn $ln
};
my $id = $r{id}; # event id.
print qq{
};
# Get Students and Actions
$sth1->execute( $id );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
while ( my $sref = $sth1->fetchrow_hashref ) {
my %s = %$sref; # student from ident table.
my $studnum = $s{studnum};
# Get his/her name
$sth3->execute( $studnum );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my ($lastname,$firstname) = $sth3->fetchrow;
# Get Any actions for this student for this event;
$sth2->execute( $id,$studnum );
if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; }
my @actions;
while ( my $aref = $sth2->fetchrow_hashref ) {
my %a = %$aref; # actions.
# my ($y,$m,$d) = split('-',$a{date}); # date removed here.
push @actions, $a{action}; # $s_month[$m] $d
}
my $actions = join('/',@actions);
print qq{$firstname $lastname };
print qq{ $actions \n};
}
print qq{