#!/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.

my %lex = ('Discipline Records' => 'Discipline Records',
	   'View' => 'View',
	   'Main' => 'Main',
	   'Discipline' => 'Discipline',
	   'Description' => 'Description',
	   'Error' => 'Error',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'Select by' => 'Select by',
	   'No Selection' => 'No Selection',
	   'Student' => 'Student',
	   'Count' => 'Count',
	   'OR' => 'OR',
	   'Author' => 'Author',
	   'Date' => 'Date',
	   'Not Found' => 'Not Found',
	   'Students' => 'Students',
	   'Location' => 'Location',
	   'Infraction' => 'Infraction',
	   'Comment' => 'Comment',
	   'Actions' => 'Actions',
	   
	  );

use DBI;
use CGI;
use Cwd;


# Get current dir so know what path for config files.
my $configpath;
my $teachermode;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $teachermode = 1;
    $configpath = '..'; # go back one to get to etc.
} else {
    $configpath = '../..'; # go back two to get to etc.
}

# only load passwords and users
eval require "$configpath/etc/admin.conf.root";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


my $dbtype = 'mysql';
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);



# Load Configuration Variables;
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}

# Load global user and password
if ( not -e "$globdir/global.conf" ) {
    print qq{Cannot read the global.conf file!\n};
    print qq{</body></html>\n};
    exit;
}

eval require "$globdir/global.conf";
if ( $@ ) {
    print qq{$lex{Error} $@<br>\n};
    die $lex{Error}. " $@<br>\n";
}

if ( $oldbase{$dbase} ) { # We are an onion lake school
    %dbase = %oldbase;
}


my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;


# Teachermode
if ( $teachermode ) { # running on teacher site
    $css = $tchcss;
    $homepage = $tchpage;
    $downloaddir = $tchdownloaddir;
    $webdownloaddir = $tchwebdownloaddir;
}

my $title = qq{$lex{View} $lex{'Discipline Records'}};

print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

if ( $arr{page} == 3 or $arr{page} == 7) {
    print qq{<link rel="stylesheet" type="text/css" media="all" };
    print qq{href="/js/calendar-blue.css" title="blue">\n};
    print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
    print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};
}

print qq{$chartype\n</head><body>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> \n};
if ( not $teachermode ) {
    print qq{| <a href="/ssp.html">SSP</a> \n};
}
print qq{]\n<h1>$title</h1>\n};


if ( not $arr{page} ) {
    selectSchool();

} elsif ( $arr{page} == 1) {
    delete $arr{page};
    selectGroup();

} elsif ( $arr{page} == 2) {
    delete $arr{page};
    showRecords();
} 


#-----------------
sub selectStudents {
#-----------------

    my $studenttable = 'student';
    if ( $arr{showwithdrawn} ) {
	$studenttable = 'studentall';
    }

    my $skipblanks = $arr{skipblanks};
    delete $arr{skipblanks};

    
    my ($select, $selectval, $groupname);
    if ( $arr{grade} ) {
	$select = "where grade = ?";
	$selectval = $arr{grade};
	$groupname = $lex{Grade};

    } elsif ( $arr{homeroom} ) {
	$select = "where homeroom = ?";
	$selectval = $arr{homeroom};
	$groupname = $lex{Homeroom};

    } else {
	print qq{<h3>$lex{'No Selection'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }


    print qq{<h3>$groupname $selectval</h3>\n};


    # Loop through all students in this grade/homeroom
    my $sth = $dbh->prepare("select lastname, firstname, studnum from $studenttable
       $select order by lastname, firstname");
    $sth->execute( $selectval );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }


    # Check for discipline events
    my $sth1 = $dbh->prepare("select count(*) from disc_ident where studnum = ?");

    # Check for Withdrawn
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");

    my $first = 1;

    # Student Loop
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	
	# Check for discipline records
	$sth1->execute( $r{studnum} );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $disccount = $sth1->fetchrow;
	if ( not $disccount and $skipblanks ) { next; }

	
	if ( $first ) {
	    # Start Form
	    print qq{<form action="$self" method="post">\n};
	    print qq{<input type="hidden" name="page" value="2">\n};
	    
	    print qq{<div><input type="submit" value="$lex{Continue}"></div>\n};

	    # Start Table
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<caption style="color:red;">Red = Withdrawn</caption>\n};
	    print qq{<tr><th></th><th>$lex{Student}</th><th>$lex{Discipline}<br>$lex{Count}</th></tr>\n};

	    $first = 0;
	}

	
	my $wd;
	if ( $arr{showwithdrawn} ) { # check if withdrawn
	    $sth2->execute( $r{studnum} );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    $wd = $sth2->fetchrow;
	}


	print qq{<tr>};
	if ( $disccount ) { 
	    print qq{<td><input type="checkbox" name="$r{studnum}" value="1"></td>\n}; 
	} else { 
	    print qq{<td></td>\n}; 
	}

	my $name;
	if ( $wd ) {
	    $name = qq{<span style="color:red;"><b>$r{lastname}</b>, $r{firstname}</span>};
	} else {
	    $name = qq{<b>$r{lastname}</b>, $r{firstname}};
	}


	print qq{<td>$name ($r{studnum})</td><td class="cn">$disccount</td></tr>\n};
			   
    }


    if ( $first ) { # no Students
	print qq{<h3>$lex{Students} $lex{'Not Found'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    

    print qq{</table>\n};
    print qq{<div><input type="submit" value="$lex{Continue}"></div>\n};

    print qq{</form></body></html>\n};

    exit;

}



#----------------
sub selectSchool { 
#----------------

    if (not @g_FeederSchools ) {
	print qq{<h3>No Feeder Schools Found</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    print qq{<h3>Select Feeder School</h3>\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    
    foreach my $db ( @g_FeederSchools ) {
	print qq{<div><input type="radio" name="db" value="$db">$dbase{$db}</div>\n};
    }

    print qq{<p><input type="submit" value="Continue"></p>\n};
    print qq{</form>\n};
    
    print qq{</body></html>\n};
    
    exit;

}

    

#---------------
sub selectGroup {
#---------------
    
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # passed db to view.

    print qq{<h3>Feeder School - $dbase{$arr{db}}</h3>\n};

    
    my $dbtype = 'mysql';
    my $dsn = "DBI:$dbtype:dbname=$arr{db}";
    my $dbh = DBI->connect($dsn,$guser,$gpassword);
    
    
    my (@grades, @schoolyears );

    # Get Grades
    $sth = $dbh->prepare("select distinct grade from studentall 
      where grade is not NULL and grade != ''");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $gr = $sth->fetchrow ) {
	push @grades, $gr;
    }
    @grades = sort {$a <=> $b} @grades;


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="db" value="$arr{db}">\n};
    
    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};


    # Select Grade
    print qq{<tr><td class="bra">$lex{'Select by'} $lex{Grade}</td>};
    print qq{<td><select name="grade"><option></option>\n};
    foreach my $gr ( @grades ) {
	print qq{<option>$gr</option>\n};
    }
    print qq{</select></td></tr>\n};

    # OR
#    print qq{<tr><td colspan="2" style="text-align:center;">$lex{OR}</td></tr>\n};


    # Select Homeroom
#    print qq{<tr><td class="bra">$lex{'Select by'} $lex{Homeroom}</td>};
#    print qq{<td><select name="homeroom"><option></option>\n};
#    foreach my $hr ( @homerooms ) {#
#	print qq{<option>$hr</option>\n};
#    }
#    print qq{</select></td></tr>\n};


    # Show Current Students
    print qq{<tr><td class="bra">Show Current Students</td>};
    print qq{<td><input type="checkbox" name="showcurrent" value="1"></td></tr>\n};


    # Show Students without Discpline Records
#    print qq{<tr><td class="bra">Skip Students without Records</td>};
#    print qq{<td><input type="checkbox" name="skipblanks" value="1" checked="checked"></td></tr>\n};

    
    # Continue
    print qq{<tr><td class="ra"><input type="submit" value="$lex{Continue}"></td></tr>\n};
    print qq{</table>\n};
    print qq{</form></body></html>\n};

    exit;

}



#--------------
sub showRecords {
#--------------

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

    my $studtable = 'studentwd';
    if ( $arr{showcurrent} ) {
	$studtable = 'studentall';
    }
    
    my $dbtype = 'mysql';
    my $dsn = "DBI:$dbtype:dbname=$arr{db}";
    my $dbh = DBI->connect($dsn,$guser,$gpassword);
    
    # Get students in that grade
    my @students;
    my $sth = $dbh->prepare("select studnum from $studtable where grade = ? order by lastname, firstname");
    $sth->execute( $arr{grade} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $studnum = $sth->fetchrow ) {
	push @students, $studnum;
    }
    

    my $sth = $dbh->prepare("select i.*,e.* from disc_ident i, disc_event e where studnum = ? and e.id = i.eventid
      order by e.date desc");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?");

    my $sth3 = $dbh->prepare("select * from disc_action where studnum  = ? and eventid = ?");
    my $sth4 = $dbh->prepare("select studnum from disc_ident where eventid = ?"); # find other students


    # my @students = keys %arr;
    # my $students = join(',',@students);  # for passing to next sub
    

    foreach my $studnum ( @students ) {

	# Get the Name
	$sth1->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;

	print qq{<div style="font-size:120%;font-weight:bold;">$firstname $lastname ($studnum)</div>\n};

	
	# Get the Discipline Records for this student
	$sth->execute($studnum);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	
	my $first = 1;
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;

	    if ( $first ) {
		# Heading
		print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
		print qq{<tr><th>$lex{Date} $lex{Location} $lex{Author}</th>\n};
		print qq{<th>$lex{Infraction} - $lex{Description}</th><th>$lex{Comment}</th>};
		print qq{<th>$lex{Students} / $lex{Actions}</th></tr>\n};
		$first = 0;

	    }

	    
	    if ( $teachermode and $r{private} ) { next; } # skip any private recs in teachermode

	    # remove the seconds from time.
	    my $time;
	    if ( $r{time} ) {
		my @time = split(':', $r{time});
		$time = qq{$time[0]:$time[1]};
	    }

	    # get author name.
	    my $author;
	    $sth2->execute( $r{author} );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    my ($lname, $fname ) = $sth2->fetchrow;
	    $author = "$fname $lname";

	    
	    print qq{<tr><td style="vertical-align:top;width:15%;">$r{date} $time<br>$r{location}<br>\n};;
	    print qq{$author</td>\n};

	    # Infraction and Description of Event
	    print qq{<td style="vertical-align:top;width:25%;">};
	    print qq{<span style="font-weight:bold;font-size:120%;">$r{infraction}</span><br>$r{description}\n};
	    print qq{</td>\n};
	    
	    # Ident Comment field
	    print qq{<td style="vertical-align:top;width:25%;">$r{comment}</td>\n};

	    
	    # All Students  (involved with this event)
	    my @allstudents;
	    $sth4->execute($r{eventid} );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	    
	    print qq{<td style="vertical-align:top;">\n};
	    
	    while ( my $snum = $sth4->fetchrow ) {

		# Get Student Name
		$sth1->execute( $snum );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		my ($ln, $fn ) = $sth1->fetchrow;

		print qq{<b>$ln</b>, $fn - };
		
		# Loop through all actions for this student for this event
		$sth3->execute( $snum, $r{eventid} );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		
		my $first = 1;
		while ( my $aref = $sth3->fetchrow_hashref ) {
		    my %a = %$aref;
		    print qq{ $a{action}<br>};
		    $first = 0;
		} # end of action loop
		if ( $first ) { print qq{<b>No Actions</b>\n}; }
		
		print qq{<hr>\n};
		
	    } # end of this student loop
	    
	    print qq{</td></tr>\n};
	}

	if ( $first ) { 
	    print qq{<div>No Records Found</div>\n};
	} else {
	    print qq{</table><p></p>\n};
	}

    } # end of all students loop

    print qq{</body></html>\n};

    exit;

}
