#!/usr/bin/perl
#  Copyright 2001-2018 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 = 'rptdiscstat.pl';

use DBI;
use CGI;
use Number::Format qw{:all};

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

# loads calcDaysOpen subroutine
eval require "../../lib/libattend.pl";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\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'} $lex{'by Date'}";
print "$doctype\n<html><head><title>$title</title>\n";
print "<link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">\n";

print "<link rel=\"stylesheet\" type=\"text/css\" media=\"all\" ";
print "href=\"/js/calendar-blue.css\" title=\"blue\">\n";
print "<script type=\"text/javascript\" src=\"/js/calendar.js\"></script>\n";
print "<script type=\"text/javascript\" src=\"/js/lang/calendar-en.js\"></script>\n";
print "<script type=\"text/javascript\" src=\"/js/calendar-setup.js\"></script>\n";

print "$chartype\n</head><body>\n";


print "[ <a href=\"$homepage\">$lex{Main}</a> |\n";
print "<a href=\"$discpage\">$lex{Discipline}</a> ]\n";

print "<h1>$title</h1>\n";

print "<h3>$lex{'Start Date'}: $startdate $lex{'End Date'}: $enddate\n";
print " $lex{'School Days'}: $schooldays</h3>\n";


if ( $type eq 'category' ) {
    doCategory();

} elsif ( $type eq 'class' ){
    doClass();

} elsif ( $type eq 'grade' ){
    doGrade();

} elsif ( $infraction or $type eq 'all' ){
    doInfraction();

} else { #
#    print "<h3>$lex{'Entry Error'}</h3>\n";
    mkSearchForm();
    exit;
}


# Bottom of Page
print "<p>[ <a href=\"$homepage\">$lex{Main}</a> | \n";
print "<a href=\"$discpage\">$lex{Discipline}</a> ]</p>\n";

mkSearchForm();

print "</body></html>\n";


#-----------------
sub mkSearchForm {
#-----------------

    print "<form action=\"$self\" method=\"post\">\n";

    print qq{<table style="border:1px solid gray;" cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th colspan="2">$lex{Statistical} $lex{Report}</th></tr>\n};

    print "<tr><td class=\"bra\">$lex{'Start Date'}</td>\n";
    print "<td><input type=\"text\" size=\"10\" id=\"sdate\" name=\"startdate\" value=\"$startdate\">\n";
    print "<button type=\"reset\" id=\"start_trigger\">...</button>";
    print "</td></tr>\n";

    print "<tr><td class=\"bra\">$lex{'End Date'}</td>\n";
    print "<td><input type=\"text\" size=\"10\" id=\"edate\" name=\"enddate\" value=\"$enddate\">\n";
    print "<button type=\"reset\" id=\"end_trigger\">...</button>";
    print "</td></tr>\n";

    print qq{<tr><td class="bra">$lex{Type}</td><td><select name="type">\n};
    print qq{<option value="category">$lex{Category}</option>};
    print qq{<option value="grade">$lex{Grade}</option>\n};
    print qq{<option value="class">$lex{Class}</option>\n};
    print qq{<option value="all">$lex{All}</option></select></td></tr>\n};

    print "<tr><td class=\"cn\" colspan=\"2\"><input type=\"submit\" value=\"$lex{'View Report'}\"></td></tr>\n";
    print "</table></form>\n";

    print "<script type=\"text/javascript\">
     Calendar.setup({
        inputField     :    \"sdate\", // id of the input field
        ifFormat       :    \"%Y-%m-%d\", // format of the input field
        button         :    \"start_trigger\", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    });";

    print "Calendar.setup({
        inputField     :    \"edate\", // id of the input field
        ifFormat       :    \"%Y-%m-%d\", // format of the input field
        button         :    \"end_trigger\", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    });";


    print "</script>\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 "<h3>$lex{'No Records Found'}</h3>\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{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<tr><th>$lex{Infraction} $lex{Category}</th>\n};
	    print qq{<th>$lex{Count}</th><th>$lex{Demerits}</th>\n};
	    print qq{<th>$lex{Percent}</th><th>$lex{PerDay}</th></tr>\n};
	    $first = 0;
	}

	my $percent = format_number( ($infrac[1] * 100)/$total , 2, 2) ;
	my $perday =  format_number( ($infrac[1]/$schooldays), 2, 2);

	print qq{<tr><td><form action="$self" method="get">\n};
	print "<input type=\"hidden\" name=\"startdate\" value=\"$startdate\">\n";
	print "<input type=\"hidden\" name=\"enddate\" value=\"$enddate\">\n";
	print "<input type=\"submit\" name=\"infrac\" value=\"$infrac[0]\"></form></td>\n";

	$demerittotal += $infrac[2];

	print qq{<td class="cn">$infrac[1]</td><td class="cn">$infrac[2]</td>\n};
	print "<td>$percent\%</td><td>$perday</td></tr>\n";
    }


    print qq{<tr><td class="bra">$lex{Totals}</td>\n};
    print qq{<td class="bcn">$total</td><td class="bcn">$demerittotal</td><td colspan="2"></td></tr>\n};
    print qq{</table>\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{<table cellpadding="3" cellspacing="0" border="1">\n};;
    print "<tr><th>$lex{Homeroom}</th><th>$lex{Count}</th>";
    print "<th>$lex{Percent}</th><th>$lex{PerDay}</th></tr>\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 "<tr><td><b>$lastname</b>, $firstname ($infrac[0])</td>";
	print "<td>$infrac[1]</td><td>$percent\%</td><td>$perday</td></tr>\n";
    }

    print qq{<tr><td class="bra">$lex{Totals}</td><td colspan="3" class="la">$total</td></tr>\n};
    print "</table>\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{<table cellpadding="3" cellspacing="0" border="1">\n};
    print "<tr><th>$lex{Grade}</th><th>$lex{Count}</th><th>";
    print "$lex{Percent}</th><th>$lex{PerDay}</th>\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{<tr><td class="bcn">$infrac[0]</td><td class="cn">$infrac[1]</td>\n};
	print qq{<td class="la">$percent\%</td><td class="la">$perday</td></tr>\n};
    }

    print qq{<tr><td class="bra">$lex{Totals}</td>\n};
    print qq{<td colspan="3" class="bla">$total</td></tr>\n};
    print "</table>\n";
}


#--------------
sub doInfraction { # Discipline by Infraction
#--------------

    #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }

    my %studinf; # student infractions data structure.
    my %studname; 

    my %infractions;

    if ( $arr{type} eq 'all' ) {
	print "<h3>$lex{Infraction}: $lex{All}</h3>\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 "<h3>$lex{Infraction}: $infraction</h3>\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{<h3>$lex{'No Records Found'}</h3>\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{<table cellpadding="3" cellspacing="0" border="1">\n};
		print qq{<caption style="font-size:120%;font-weight:bold;">$inf</caption>\n};
		print qq{<tr><th>$lex{Student}</th><th>$lex{Count}</th><th>$lex{Demerits}</th><th>$lex{Percent}</th><th>$lex{PerDay}</th></tr>\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{<tr><td class="bla"><a href="rptstud.pl?student=$studnum">\n};
	    print qq{$lastname</b>, $firstname</a> ($studnum)</td>\n};
	    print qq{<td class="bla">$count</td><td>$demerits</td><td>$percent\%</td><td>$perday</td></tr>\n};

	}

	print qq{<tr><td class="bra">Totals</td><td class="bla">$inftotal</td><td>$dtotal</td><td colspan="3"></td></tr>\n};
	print "</table>\n";

    } # end of infraction loop


    print "<hr>\n";

    # Now do 'per student' analysis.
    print "<h3>Student Results</h3>\n";

    my %revstudname = reverse %studname;

    foreach my $key ( sort keys %revstudname ) {
	my $studnum = $revstudname{$key};
	#print "$studnum || $key<br>\n";

	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	print qq{<caption style="font-size:120%;">$key</caption>\n};
	print qq{<tr><th>$lex{Infraction}</th><th>$lex{Count}</th><th>$lex{Demerits}</th></tr>\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{<tr><td class="bra">$inf</td><td class="cn">$count</td><td class="cn">$demerits</td></tr>\n};
	    $c += $count;
	    $d += $demerits;
	}

	print qq{<tr style="background:#DDD;"><td class="bra">$lex{Totals}</td>};
	print qq{<td class="cn">$c</td><td class="cn">$d</td></tr>\n};

	print qq{</table><p></p>\n};

    }

    print "</body></html>\n";
    exit;


} # end of doInfraction
