#!/usr/bin/perl
#  Copyright 2001-2019 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 = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Student' => 'Student',
	   'Continue' => 'Continue',
	   'Save' => 'Save',
	   'Date' => 'Date',
	   'No User Id' => 'No User Id',
	   'No Password' => 'No Password',
	   'Please Log In' => 'Please Log In',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Select' => 'Select',
	   'Students' => 'Students',
	   'Missing Value' => 'Missing Value',
	   'Score' => 'Score',
	   'Added' => 'Added',
	   'OR' => 'OR',
	   'Time' => 'Time',
	   'Event' => 'Event',
	   'Location' => 'Location',
	   'Not Selected' => 'Not Selected',
	   'Description' => 'Description',
	   'Author' => 'Author',
	   'All' => 'All',
	   'Select by' => 'Select by',
	   'Check Next Page' => 'Check Next Page',
	   'Update' => 'Update',
	   'Discipline' => 'Discipline',
	   'Reason' => 'Reason',
	   'Name' => 'Name',
	   'Last,First/Last/Initials/Studnum', 'Last,First/Last/Initials/Studnum',
	   
	   );

use DBI;
use CGI;
use CGI::Session;
use Cwd;

my $self = 'hvEdit.pl';
my $userid = $ENV{'REMOTE_USER'};

# 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.
}


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


my $q = new CGI;
my %arr = $q->Vars;


my @time = localtime(time);
my $year = $time[5] + 1900;
my $month = $time[4] + 1;
my $day = $time[3];
if ( length $month == 1 ) { $month = '0'. $month; }
if ( length $day == 1 ) { $day = '0'. $day; }
my $currdate = "$year-$month-$day";
my $currtime = "$time[2]:$time[1]";

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


# Get current dir so know what CSS to display and shift to teacher settings.
if ( getcwd() =~ /tcgi/ ) { # we are in tcgi
    $css = $tchcss;
    $homepage = $tchpage;
    $schpage = $tchpage;
}
    
print $q->header( -charset, $charset );

    
# Page Header
my $title = qq{$lex{Edit}/$lex{Delete} Home Visit};

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

if ( not $arr{page} ) {
    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 style="padding:1em 2em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> \n};
if ( not $teachermode ) {
    print qq{| <a href="$discpage">$lex{Discipline}</a> \n};
}
print qq{] <h1>$title</h1>\n};

# print qq{<h3>User:$userid</h3>\n};


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

}  elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectStudents();
    
}  elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    selectRecords();

}  elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    editRecords();
    
} elsif ( $arr{page} == 4 ) {
    delete $arr{page};
    updateRecords();
}


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

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

    my $checked;
    if ( $arr{checknextpage} ) {
	$checked = qq{checked="checked"};
	delete $arr{checknextpage};
    }

    my $studenttable = 'studentall';

    my $skipblanks = 1;

    my $fields = 'lastname, firstname, studnum';
    
    my $sth;
    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};

    } elsif ( $arr{student} ) {
	my $student = $arr{student};

	# Setup the Search
	if ($student =~ /\d+/) {  # we have a student number
	    $studentnumber = $student;
	    $sth = $dbh->prepare("select $fields from student where studnum = ?");
	    $sth->execute( $student );

	} else { # we have words hopefully with a comma

	    my ( $lastname,$firstname )  = split(',', $student);
	    $firstname =~ s/^\s*//;
	    $lastname =~ s/^\s*//;
	    if ( $lastname and $firstname ){ # both entered.
		$sth = $dbh->prepare("select $fields from student 
                  where lastname = ? and firstname = ?");
		$sth->execute( $lastname, $firstname );

	    } elsif ($lastname and not $firstname){ # only lastname (no comma)

		if ( length($lastname) == 2 ){ # search by initials: fi, li.
		    my $fi = substr($lastname,0,1). '%'; 
		    my $li = substr($lastname,1,1). '%';
		    $sth = $dbh->prepare("select $fields from student 
                     where lastname $sql{like} ? and firstname $sql{like} ?");
		    $sth->execute( $li, $fi );

		} else {
		    $sth = $dbh->prepare("select $fields from student 
                      where lastname = ? order by firstname");
		    $sth->execute( $lastname );
		}
	    } else { # print an error....
		print qq{<p><b>$lex{Error}:$lex{'No Student(s) Found'}</b></p>\n};
		print qq{</body></html>\n};
		exit; 
	    }

	} # Last Else in word search.

	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	$rows = $sth->rows;

	if ( $rows < 1 ) { 
	    print qq{<p><b>$lex{Error}:$lex{'No Student(s) Found'}</b></p>\n};
	    print qq{</body></html>\n};
	    exit; 
	} 

    } else { # end of student selection
	
	# no selection
	print qq{<h3>$lex{'No Selection'}</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

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


    # Loop through all students in this grade/homeroom / student select
    if ( not $arr{student} ) { # grade or homeroom
	$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 home visits
    my $sth1 = $dbh->prepare("select count(*) from homevisit 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 visit 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>Home Visit<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" $checked></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 showStartPage {
#----------------

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

    # Get Grades
    $sth = $dbh->prepare("select distinct grade from student 
      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="1">\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};

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


    # Student Name or Initial
    print qq{<tr><td class="bra">$lex{'Select by'} $lex{Name}</td>};
    print qq{<td><input type="text" name="student" style="width:20ch;"></td></tr>\n};
    print qq{<tr><td colspan="2" class="ra" style="font-size:90%;">$lex{'Last,First/Last/Initials/Studnum'}</td></tr>\n};
    print qq{<tr><td colspan="2"><hr></td></tr>\n};
    
    
    # Check Next Page
    print qq{<tr><td class="bra">$lex{'Check Next Page'}</td>};
    print qq{<td><input type="checkbox" name="checknextpage" value="1"></td></tr>\n};
    

    # Withdrawn
#    print qq{<tr><td class="bra">$lex{'Show Withdrawn'}</td>};
#    print qq{<td><input type="checkbox" name="showwithdrawn" value="1"></td></tr>\n};


    # Show Students without Homevisits
#    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 selectRecords {
#----------------

    # print qq{<div>Select Records</div>\n};
    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    # Passed Student Numbers, only.

  
    if ( not %arr ) {
	print qq{<h3>$lex{Error}: No Student Selection</h3>\n};
	print qq{</body></html>\n};
	exit;
    }

    
    my (%sort, %student);
    my $sth = $dbh->prepare("select * from studentall where studnum = ?");
    foreach my $studnum  (keys %arr ) {
	$sth->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	$student{$studnum} = $ref;
	my %r = %{ $ref };
	$sort{"$r{lastname}$r{firstname}$studnum"} = $studnum;
    }

    # Prep for Record Select
    my $sth1 = $dbh->prepare("select * from homevisit where studnum = ?");
    
    
    # Print Form Heading
    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="3">\n};

#    print qq{<input type="hidden" name="author" value="$author">\n};
    
    print qq{<div class="la"><input type="submit" value="$lex{Continue}"></div>\n};

    # Student Loop
    foreach my $key ( sort keys %sort ) {
	my $studnum = $sort{$key};
	my %r = %{ $student{$studnum}};

	my $first = 1;
	
	# Now get their records
	$sth1->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth1->fetchrow_hashref ) {
	    my %h = %$ref;

	    if ( $first ) {
		print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin:1em;">\n};
		print qq{<caption style="font-size:130%;font-weight:bold;text-align:left;padding:0.3em;">};
		print qq{$r{lastname}, $r{firstname} ($studnum)</caption>\n};
		print qq{<tr><th>$lex{Edit}</th><th>$lex{Delete}</th><th>$lex{Date}</th>};
		print qq{<th>$lex{Description}</th></tr>\n};
		$first = 0;
	    }

	    if ( $teachermode and $h{author} ne $userid ) {
		print qq{<tr><td colspan="2" style="color:red;">User Not Author</td>};
	    } else { # normal edit.
		print qq{<tr><td><input type="checkbox" name="E:$h{id}" value="1"></td>\n};
		print qq{<td><input type="checkbox" name="D:$h{id}" value="1"></td>\n};
	    }
	    
	    print qq{<td>$h{date}</td><td>$h{description}</td></tr>\n};
	    
	} # end of record loop
	
	print qq{</table>\n};
	
    } # end of student loop

    print qq{<div class="la"><input type="submit" value="$lex{Continue}"></div>\n};
    print qq{</form></body></html>\n};

    exit;

} # end of selectRecords



#----------------
sub editRecords {
#----------------

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

    if ( not %arr ) {
	print qq{<h3>$lex{Error}: No Selection</h3>\n};
	print qq{</body></html>\n};
	exit;
    }


    # Parse into Edit and Delete Records.
    my (%edit, %delete);
    foreach my $key ( sort keys %arr ) {
	my ($type, $id) = split(':', $key);
	if ( $type eq 'D' ) {
	    $delete{$id} = 1;
	} elsif ( $type eq 'E' ) {
	    $edit{$id} = 1;
	} else { # an error!
	    print qq{<h3>Error for K:$key V:$arr{$key}</h3>\n};
	    exit;
	}
    }


    my $sth = $dbh->prepare("select * from homevisit where id = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");

    
    # Display the Delete records.
    my $first = 1;
    foreach my $id ( sort keys %delete ) {

	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin:1em;">\n};
	    print qq{<caption style="font-size:130%;font-weight:bold;text-align:left;padding:0.3em;">};
	    print qq{Records to Delete</caption>\n};
	    print qq{<tr><th>$lex{Student}</th><th>$lex{Date}</th><th>$lex{Description}</th></tr>\n};
	    $first = 0;
	}
	
	$sth->execute( $id );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	%h = %$ref;
	my $studnum = $h{studnum};

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

	print qq{<tr><td><b>$lastname</b>, $firstname</td><td>$h{date}</td>};
	print qq{<td style="width:70ch;">$h{description}</td></tr>\n};

#	if ( $teachermode and $h{author} ne $userid ) {

	
    }
    if ( not $first ) {
	print qq{</table>\n};
    } else {
	print qq{<h3 style="margin:1em;">No Records to Delete</h3>\n};
    }
    # end of displaying delete records.

    
    # Start Form 
    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="4">\n};
    foreach my $id ( keys %delete ) {
	print qq{<input type="hidden" name="DEL:$id" value="1">\n};
    }
    
    print qq{<div class="la"><input type="submit" value="$lex{Update}/$lex{Delete}"></div>\n};

    # Edit Loop
    my $first = 1;
    foreach my $id ( sort keys %edit ) {
	
	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin:1em;">\n};
	    print qq{<caption style="font-size:130%;font-weight:bold;text-align:left;padding:0.3em;">};
	    print qq{Records to Edit</caption>\n};
	    print qq{<tr><th>$lex{Student}</th><th>$lex{Date}</th><th>$lex{Reason}</th>};
	    print qq{<th>$lex{Description}</th></tr>\n};
	    $first = 0;
	}
	
	$sth->execute( $id );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	%h = %$ref;
	my $studnum = $h{studnum};

	# Get Student Name;
	$sth1->execute( $studnum );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth1->fetchrow;
	
	print qq{<tr><td><b>$lastname</b>, $firstname</td>};
	print qq{<td><input type="text" name="$id:date" value="$h{date}" style="width:12ch;"></td>};
	print qq{<td><select name="$id:reason"><option>$h{reason}</option>};
	foreach my $rsn ( @g_HomeVisitReason ) {
	    if ( $rsn eq $h{reason} ) { next; }
	    print qq{<option>$rsn</option>\n};
	}
	print qq{<option></option></select></td>\n};
	print qq{<td><textarea name="$id:description" style="width:60ch;height:auto;">};
	print qq{$h{description}</textarea></td></tr>\n};

    }
    if ( not $first ) {
	print qq{</table>\n};
    } else {
	print qq{<h3>No Records to Edit</h3>\n};
    }
    # end of edit loop
	
    print qq{<div class="la"><input type="submit" value="$lex{Update}/$lex{Delete}"></div>\n};
    print qq{</form></body></html>\n};

    exit;

} # end of editRecords



#----------------
sub updateRecords {
#----------------

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

    # Delete Records first    
    my $sth = $dbh->prepare("delete from homevisit where id = ?");
    foreach my $key ( keys %arr ) {
	my ( $type, $id ) = split(':', $key);
	if ( $type eq 'DEL' ) { # delete the record.
	    $sth->execute( $id );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    delete $arr{$key}; # remove the delete values from hash.
	}
    }
 
#   foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    
    # Loop over remaining keys in %arr, and update.
    foreach my $key ( sort keys %arr ) {
	if ( not $arr{$key} ) { next; } # skip any blanks, because they deleted them above!
	
	my ( $id, $field ) = split(':', $key);
	
	# Update Record
	my $sth = $dbh->prepare("update homevisit set $field = ? where id = ?");
	$sth->execute( $arr{$key}, $id );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    } # all loop

    print qq{<h3>Records Updated</h3>\n};

    
    print qq{<p>[ <a href="$homepage">$lex{Main}</a> \n};
    if ( not $teachermode ) {
	print qq{| <a href="$discpage">$lex{Discipline}</a> \n};
    }
    print qq{]</p>\n};
    print qq{</body></html>\n};
    exit;

} # end of updateRecords



#------------
sub checkDate {
#------------

    use Time::JulianDay;
    
    my $date = shift;

    my ($y,$m,$d) = split('-',$date);
    
    if ( not $d ) { return 255; }  # wrong format.
    if ( $m > 12 or $m < 1 ) { return 255; }
    if ( $d > 31 or $d < 1 ) { return 255; }

    my $datejd = julian_day( $y, $m, $d );

    my $startjd = julian_day( split('-', $schoolstart ));
    my $endjd = julian_day( split('-', $schoolend ));
    
#    print "DATE:$date - $datejd Start:$schoolstart - $startjd  End:$schoolend - $endjd<br>\n";

    if ( $datejd > $endjd  or $datejd < $startjd ) {
	return 1; # different code for date outside of current year;
    }
    
    return 0;

}
