#!/usr/bin/perl
#  Copyright 2001-2021 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',
	   'Student' => 'Student',
	   'No User Id' => 'No User Id',
	   'No Password' => 'No Password',
	   'Please Log In' => 'Please Log In',
	   'Select' => 'Select',
	   'Select' => 'Select',
	   'Students' => 'Students',
	   'No Students Found' => 'No Students Found',
	   'Group' => 'Group',
	   'Students' => 'Students',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Yes' => 'Yes',
	   'Confirm' => 'Confirm',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Hours' => 'Hours',
	   'Record(s) Deleted' => 'Record(s) Deleted',
	   'More' => 'More',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Update' => 'Update',
	   'Program' => 'Program',
	   'Comment' => 'Comment',
	   'Dropped' => 'Dropped',
	   'Kit' => 'Kit',
	   'Lesson' => 'Lesson',
	   'Number' => 'Number',
	   'Initial' => 'Initial',
	   'Attended' => 'Attended',
	   'Not Found' => 'Not Found',

	   );

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


my $self = 'lintStudDelEd.pl';

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

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


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

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

# Session Setup Here
my $session = new CGI::Session("driver:mysql;serializer:FreezeThaw",
 undef,{Handle => $dbh}) or die CGI::Session->errstr;

my ( $userid, $duration);
# Get/Set  Session Values (a defined userid means it was passed)
if ( $arr{userid} ){ # we want to login, passed userid/password pair.

    # Check password/userid against database (-1 no user, -2 wrong password);
    my $error = checkPassword($arr{userid}, $arr{password}, $dbh);
    if ($error == -1){ print $q->header( -charset, $charset ); login($lex{'No User Id'}); }
    if ($error == -2){ print $q->header( -charset, $charset ); login($lex{'No Password'}); }

    $cookietime = checkCookieTime( $arr{duration} );

    # Set values for userid and logged_in in session
    $session->param( 'logged_in','1');
    $session->expire( 'logged_in', $cookietime );
    $session->param( 'userid',$arr{userid} );
    $session->param( 'duration',$cookietime );

    $userid = $arr{userid};


} else { # check logged_in value in session

    if ( not $session->param('logged_in') ){
	$userid = $session->param('userid');
        print $q->header( -charset, $charset );
	print qq{<p>[ <a href="$tchpage">$lex{Main}</a> ]</p>\n};
        print qq{<h3>$lex{'Please Log In'}</h3></body></html>\n}; 
        exit;
    }
    # Ok, we have a login. Values below we have in environment.

    $userid = $session->param('userid');
    $duration = $session->param('duration');

    if ( not ($duration =~ /\+/)) { # if not in +20m format, do so.
	$duration = checkCookieTime( $duration );
    }

    $session->expire('logged_in', $duration );


} # End of check for logged_in value

print $session->header( -charset, $charset );
# Session Setup End


# Page Header
my $title = "$lex{Edit}/$lex{Delete} $lex{Students}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$tchcss" type="text/css">\n};

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{<style type="text/css">td.ra { text-align:right; }
td.bcn { text-align:center;font-weight:bold;font-size:120%; }
</style>\n};

print qq{$chartype\n</head><body style="padding:1em 2em;">\n};
print qq{<p>[ <a href="$tchpage">$lex{Main}</a> ]</p>\n};


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

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

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

} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    confirmDelete();

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

} elsif ( $arr{page} == 5 ) {
    delete $arr{page};
    deleteRecords();
}


#----------------
sub showStartPage {
#----------------

    print qq{<h1>$title</h1>\n}; # both edit and delete

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

    print qq{<caption style="font-weight:bold;text-align:left;">$lex{Select} $lex{Group}</caption>\n};

    # Edit or Delete
    print qq{<tr><td colspan="4"><input type="submit" name="edit" value="$lex{Edit}"> };
    print qq{<input type="submit" name="delete" value="$lex{Delete}"></td></tr>\n};

    print qq{<tr><th>Name</th><th>Start Date</th><th>End Date</th><th>Description</th></tr>\n};


    # Load existing programs (ie. lint groups)
    my $sth = $dbh->prepare("select p.*, s.lastname,s.firstname from lint_program p
      left join staff s on s.userid = p.userid  order by startdate desc, s.lastname, s.firstname");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

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

	# Get Staff Member Name; part of main query.
	my $teachername;
	if ( $r{lastname} ) {
	    $teachername = qq{<b>$r{lastname}</b>, $r{firstname}};
	} else {
	    $teachername = qq{<span style="color:red;font-weight:bold;">};
	    print qq{$lex{'Not Found'}:$r{userid}</span>}
	}

	print qq{<tr><td><input type="radio" name="program" value="$r{id}">$teachername</td>\n};
	print qq{<td>$r{startdate}</td><td>$r{enddate}</td><td>$r{groupdesc}</td></tr>\n};

    }

    # Edit or Delete
    print qq{<tr><td colspan="4"><input type="submit" name="edit" value="$lex{Edit}"> };
    print qq{<input type="submit" name="delete" value="$lex{Delete}"></td></tr>\n};

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

    exit;

} # end of showStartPage



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

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

    my $mode eq 'edit';
    if ( $arr{delete} ) {
	$mode = 'delete';
    }

    my $title = "$lex{Edit} $lex{Students}";
    my $page = 2; # edit 
    if ( $mode eq 'delete' ) {
	$title = "$lex{Delete} $lex{Students}";
	$page = 3; # delete
    }


    print qq{<h1>$title</h1>\n};

    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="$page">\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Student}</th><th>$lex{'Start Date'}</th><th>$lex{'End Date'}</th>};
    print qq{<th>$lex{Hours}</th></tr>\n};

    my $sth = $dbh->prepare("select l.*,s.lastname,s.firstname from lint_student l, studentall s
      where s.studnum = l.studnum and progid = ? order by s.lastname, s.firstname, l.id");
    $sth->execute( $arr{program} );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %sr = %$ref;
	print qq{<tr><td class="la"><input type="checkbox" name="$sr{id}" value="1"> \n};
	print qq{<b>$sr{lastname}</b>, $sr{firstname}</td><td>$sr{startdate}</td>};
	print qq{<td>$sr{enddate}</td><td class="cn">$sr{programhours}</td></tr>\n};
    }

    if ( $first ) {
	print qq{</table></form><h1>$lex{'No Students Found'}</h1>\n};
	print qq{</body></html>\n};
	exit;
    }

    # Submit
    print qq{<tr><td class="la" colspan="4"><input type="submit" value="$title"></td></tr>\n};

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

    exit;

} # end of selectStudents



#----------------
sub confirmDelete {
#----------------

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

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

    # print qq{<h1>$lex{Confirm} $lex{Delete}</h1>\n};

    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="5">\n};

    print qq{<table cellpadding="3" cellspacing="0" border="0">\n};
    print qq{<tr><th>$lex{Confirm} $lex{Delete}</th></tr>\n};

    foreach my $id ( keys  %arr ) { 

	# Get lint record
	$sth1->execute( $id );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth1->fetchrow_hashref;
	my $studnum = $ref->{studnum};

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

	print qq{<tr><td><input type="hidden" name="$id" value="1">$firstname $lastname</td></tr>\n };

    }


     print qq{</table>};
     print qq{<p><input type="submit" value="$lex{Yes}, $lex{Delete} $lex{Students}"></p>\n};

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

    exit;

}


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

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

    print qq{<h1>$lex{Edit} $lex{Students}</h1>\n};

    # Start Form
    print qq{<form action="$self" method="post"> \n};
    print qq{<input type="hidden" name="page" value="4">\n};

    print qq{<p><input type="submit" value="$lex{Update}"></p>\n};

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

    foreach my $id ( keys %arr ) {

	# Get Lint Record
	$sth->execute( $id );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $ref = $sth->fetchrow_hashref;
	my %sr = %$ref;
	my $id = $sr{id};

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

	# Start Table
	print qq{<table cellpadding="3" cellspacing="0" border="0" };
	print qq{style="padding:0.5em;border:1px solid gray;">\n};

	# Student Name
	print qq{<tr><td class="la" colspan="2" style="font-weight:bold;">};
	print qq{$firstname $lastname</td></tr>\n};

	# Hours
	print qq{<tr><td class="ra">$lex{Program} $lex{Hours} $lex{Attended}</td>\n};
	print qq{<td><input type="text" name="$id:programhours" size="4" value="$sr{programhours}">\n};
	print qq{</td></tr>\n};


	# Kit
	print qq{<tr><td class="ra">$lex{Initial} $lex{Kit}</td>\n};
	print qq{<td><select name="$id:initialkit">\n};
	print qq{<option>$sr{initialkit}</option>};
	print qq{<option>Orange</option><option>Green</option><option>Blue</option>\n};
	print qq{<option>Red</option><option>Gold</option><option>Purple</option>};
	print qq{<option>Intermediate</option>\n};
	print qq{<option>Middle Years</option><option>Teal</option><option></option>\n};
	print qq{</select></td></tr>\n};

	# Lesson Number
	print qq{<tr><td class="ra">$lex{Initial} $lex{Lesson} $lex{Number}</td>\n};
	print qq{<td><input type="text" name="$id:initiallesson" size="4" value="$sr{initiallesson}" };
	print qq{maxlength="5"></td></tr>\n};


	# Comment
	print qq{<tr><td class="ra">$lex{Comment}</td>\n};
	print qq{<td><textarea name="$id:comment" rows="4" cols="60">};
	print qq{$sr{comment}</textarea></td></tr>\n};

	# Override Text.
        print qq{<tr><td class="cn" colspan="2">};
	print qq{The following values are only entered if a student enters \n};
	print qq{the program <b>late</b> or exits the program <b>early</b> (ie. drops)</td></tr>\n};


	# Start Date - Late Entry
	print qq{<tr><td class="ra">$lex{'Start Date'}</td>\n};
	print qq{<td><input type="text" name="$id:startdate" id="$id:sdate" };
	print qq{size="10" value="$sr{startdate}">\n};
	print qq{<button type="reset" id="$id:start_trigger">...</button>\n};
	print qq{</td></tr>\n};

	# Attendance - Program Hours Enrolled - Late Entry
	print qq{<tr><td class="ra">Program Hours Enrolled</td>\n};
	print qq{<td><input type="text" name="$id:attendance" size="4" };
	print qq{value="$sr{attendance}"> Late Entry\n};
	print qq{</td></tr>\n};

	# Withdraw Type
	my %wdtype = (dropped => Dropped, moved => Moved );
	print qq{<tr><td class="ra">Withdrawal Type</td>\n};
	print qq{<td><select name="$id:withdrawtype">\n};
	print qq{<option value="$sr{withdrawtype}">$wdtype{$sr{withdrawtype}}</option>};
	
	foreach my $key ( keys %wdtype ) {
	    print qq{<option value="$key">$wdtype{$key}</option>\n};
	}
	print qq{<option></option></select></td></tr>\n};

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

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


	print qq{<script type="text/javascript">
        Calendar.setup({
           inputField     :    "$id:sdate", 
           ifFormat       :    "%Y-%m-%d",
           button         :    "$id:start_trigger",
           singleClick    :    false,
           step           :    1
        });

        Calendar.setup({
            inputField     :    "$id:edate",
            ifFormat       :    "%Y-%m-%d",
            button         :    "$id:end_trigger",
            singleClick    :    false,
            step           :    1
        });
        </script>\n};

    }

    print qq{<p><input type="submit" value="$lex{Update}"></p>\n};
    print qq{</form>\n};

    print qq{[ <a href="$tchpage">$lex{Main}</a> ]\n};
    print qq{</body></html>\n};

    exit;

} # end of editRecords


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

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

    foreach my $key ( keys %arr ) {

	my ( $id, $field) = split(/:/, $key);
	if ( $field eq 'attendance' ) { $arr{$key} =~ s/\%//; } # Strip % if found.
	
	my $sth;
	if ( $arr{$key} eq '' or not defined $arr{$key} ) {
	    $sth = $dbh->prepare("update lint_student set $field = NULL where id = ?");
	    $sth->execute( $id);
	} else {
	    $sth = $dbh->prepare("update lint_student set $field = ? where id = ?");
	    $sth->execute( $arr{$key}, $id);
	}


	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

    }
    
    print qq{<h3>$lex{'Record(s) Updated'}</h3>\n};

    print qq{[ <a href="$tchpage">$lex{Main}</a> ]\n};
    print qq{</body></html>\n};

    exit;

} # end of updateRecords



#----------------
sub deleteRecords { # delete student records
#----------------

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

    my $sth = $dbh->prepare("delete from lint_student where id = ?");

    foreach my $key ( keys %arr) {
	$sth->execute( $key );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    }
    
    print qq{<p>$lex{'Record(s) Deleted'}</p>\n};
    
    # print qq{<p>[ <a href="$tchpage">$lex{Main}</a> ]\n};

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="submit" value="$lex{More}"></form></p>\n};

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

    exit;

}
