#!/usr/bin/perl # Copyright 2001-2023 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. # Function: Display discipline info by date range and by type. # Accept: startdate, enddate, type, infrac my %lex = ('Student Infractions' => 'Student Infractions', 'by Date' => 'by Date', 'Main' => 'Main', 'Discipline' => 'Discipline', 'Start Date' => 'Start Date', 'End Date' => 'End Date', 'School Days' => 'School Days', 'Category' => 'Category', 'Class' => 'Class', 'Grade' => 'Grade', 'Totals' => 'Totals', 'Infraction' => 'Infraction', 'Student' => 'Student', 'Count' => 'Count', 'Percent' => 'Percent', 'PerDay' => 'PerDay', 'View Report' => 'View Report', 'Type' => 'Type', 'No Records Found' => 'No Records Found', 'Homeroom' => 'Homeroom', 'Error' => 'Error', 'Statistical' => 'Statistical', 'Report' => 'Report', 'Demerits' => 'Demerits', 'Entry Error' => 'Entry Error', 'All' => 'All', ); my $self = 'rptDisc2.pl'; use DBI; use CGI; use Number::Format qw{:all}; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # Not used,since partial days open requires a grade value # loads calcDaysOpen subroutine #eval require "../../lib/libattend.pl"; #if ( $@ ) { # print $lex{Error}. ": $@
\n"; # die $lex{Error}. ": $@\n"; #} my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my $currdate; my @time = localtime(time); my $year = $time[5] + 1900; my $month = $time[4] + 1; if (length($month) == 1 ) { $month = '0'. $month; } if (length($time[3]) == 1 ) { $time[3] = '0'. $time[3]; } $currdate = "$year-$month-$time[3]"; my $startdate = $schoolstart; if ( $arr{startdate} ) { $startdate = $arr{startdate}; } my $enddate = $currdate; if ( $arr{enddate} ) { $enddate = $arr{enddate}; } my $type = $arr{type}; my $infraction = $arr{infrac}; # generated by a loop. my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # my $schooldays = calcDaysOpen($startdate, $enddate, $dbh); my $title = "$lex{'Student Infractions'}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{Discipline} ]}; # D# = Demerits\n}; print qq{

$title

\n}; my $start = formatDate($startdate); my $end = formatDate($enddate); print qq{\n}; print qq{\n}; print qq{\n}; #print qq{\n}; print qq{
$lex{'Start Date'} $start
$lex{'End Date'}$end
$lex{'School Days'}$schooldays
\n}; if ( $type eq 'category' ) { doCategory(); } elsif ( $type eq 'homeroom' ){ doHomeroom(); } elsif ( $type eq 'grade' ){ doGrade(); } elsif ( $type eq 'infraction' or $type eq 'all' ){ doInfraction(); } else { # show entry form. mkSearchForm(); exit; } # Bottom of Page print qq{

[ $lex{Main} | \n}; print qq{$lex{Discipline} ]

\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}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Statistical} $lex{Report}
$lex{'Start Date'}\n}; print qq{}; print qq{
$lex{'End Date'}\n}; print qq{}; print qq{
$lex{Type}
}; 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}; print qq{\n}; print qq{\n}; print qq{\n}; $first = 0; } my $percent = format_number( ($count * 100)/ $total , 2, 2) ; # my $perday = format_number( ($count / $schooldays), 2, 2); print qq{\n}; print qq{\n}; } print qq{\n}; print qq{}; print qq{\n}; print qq{
$lex{Infraction} $lex{Category}$lex{Count}$lex{Percent}
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$count$percent\%
$lex{Totals}$total
\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{}; print qq{\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{}; print qq{\n}; #\n}; } print qq{}; print qq{\n}; print qq{
$lex{Homeroom}StudentsEvents$lex{Percent}
$name $hr$studcount$eventcount$percent\%
$perday
Event Totals$totalevents
\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{}; print qq{\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{}; print qq{}; print qq{\n}; #$perday\n}; } print qq{}; print qq{\n}; print qq{
$lex{Grade}StudentsEvents$lex{Percent}
$gr$studcount$eventcount$percent\%
Event Totals$totalevents
\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{\n}; print qq{\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{}; my $id = $r{id}; # event id. print qq{\n}; } # end of single infraction loop print qq{
}; print qq{$infraction
EventStudents/ActionsDescription
$date $time
$r{location}
}; print qq{$fn $ln		}; # 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 }; if ( $s{demerit} ) { print qq{ (Demerit $s{demerit})}}; print qq{
\n}; } print qq{		$r{description}
\n}; print qq{
\n}; print qq{\n}; exit; } # Otherwise all infractions (ie. type = all ) my %studinf; # student infractions data structure. my %studname; my %infractions; my %staffname; # Otherwise doing all infraction types. print qq{

$lex{All} $lex{Infraction}s

\n}; # Get All infractions and their counts my $sth = $dbh->prepare("select distinct infraction, count(infraction) from disc_event d where to_days(d.date) >= to_days('$startdate') and to_days(d.date) <= to_days('$enddate') and infraction is not NULL and infraction != '' group by infraction order by infraction"); $sth->execute; if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } while ( my ($inf, $count ) = $sth->fetchrow ) { $infractions{$inf} = $count; } # We now have infractions and counts in %infractions. 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 = ?"); foreach my $inf ( sort keys %infractions ) { # sorted my $inftotal = $infractions{$inf}; my $dtotal; # demerit total. # Get event records $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 d.infraction = ?"); $sth->execute( $inf ) ; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; # event record if ( $first ) { # put in table header print qq{\n}; print qq{\n}; print qq{\n}; $first = 0; } my $date = formatDate($r{date}); my $time = formatTime($r{time}); # Get Staff Name $sth4->execute( $r{author} ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn) = $sth4->fetchrow; print qq{}; my $id = $r{id}; # event id. print qq{\n}; } # end of event loop print qq{
}; print qq{$inf
EventStudents/ActionsDescription
$date $time
$r{location}
}; print qq{$fn $ln		}; # 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 if not already in studname hash. if ( not $studname{$studnum} ) { $sth3->execute( $studnum ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } my ($lastname,$firstname) = $sth3->fetchrow; $studname{$studnum} = qq{$lastname, $firstname}; } print qq{$studname{$studnum}
\n}; =head # Get Any actions for this student for this event; my $actions; $sth2->execute( $id,$studnum ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } while ( my $aref = $sth2->fetchrow_hashref ) { my %a = %$aref; # actions. my ($y,$m,$d) = split('-',$a{date}); $actions .= qq{ $s_month[$m] $d $a{action}/}; } =cut # store student information in structure. $studinf{$studnum}{$inf}{'count'}++; $studinf{$studnum}{$inf}{'demerits'} += $s{demerit}; $dtotal += $demerits; } # end of student loop for this infraction print qq{		$r{description}
\n}; } # end of infraction loop =head if ( $first ) { # put in table header print qq{\n}; print qq{\n}; print qq{}; print qq{\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{\n}; print qq{}; print qq{\n}; } print qq{}; print qq{\n}; print qq{
$inf
$lex{Student}$lex{Count}$lex{Demerits}$lex{Percent}$lex{PerDay}
\n}; print qq{$lastname, $firstname ($studnum)$count$demerits$percent\%$perday
Totals$inftotal$dtotal
\n}; } # end of infraction loop =cut # Now do 'per student' analysis. print qq{
Student Infractions
\n}; print qq{
\n}; my %revstudname = reverse %studname; my $count; foreach my $key ( sort keys %revstudname ) { my $studnum = $revstudname{$key}; #print qq{$studnum || $key
\n}; print qq{\n}; print qq{\n}; print qq{\n}; my %inf = %{ $studinf{$studnum}}; my ($c, $d); foreach my $inf ( keys %inf ) { my $count = $inf{$inf}{'count'}; my $demerits = $inf{$inf}{'demerits'}; print qq{}; print qq{\n}; $c += $count; $d += $demerits; } print qq{}; print qq{\n}; print qq{
$key
$lex{Infraction}$lex{Count}$lex{Demerits}
$inf$count$demerits
$lex{Totals}$c$d
\n}; $count++; if ( $count % 4 == 0 ) { print qq{
\n}; } } print qq{\n}; exit; } # end of doInfraction #------------- sub formatDate { #------------- my $date = $_[0]; my ($y,$m,$d) = split('-', $date); my $month = $s_month[$m]; $date = qq{$month $d, $y}; return $date; } #------------- sub formatTime { #------------- my $time = $_[0]; my ($h,$m,$s) = split(':', $time); my $suffix = 'AM'; if ( $h > 11 ) { $suffix = 'PM'; } if ( $h > 12 ) { $h = $h - 12; } $time = qq{$h:$m $suffix}; return $time; }