#!/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 = ('Withdraw' => 'Withdraw',
	   'Staff' => 'Staff',
	   'Not Found' => 'Not Found',
	   'Withdrawn' => 'Withdrawn',
	   'Main' => 'Main',
	   'Error' => 'Error',
	   'Search' => 'Search',
	   'Name' => 'Name',
	   'Birthdate' => 'Birthdate',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Description' => 'Description',
	   'Reason' => 'Reason',
	   'Code' => 'Code',
	   'Transfer' => 'Transfer',
	   'Date' => 'Date',
	   'Yes' => 'Yes',
	   'Delete' => 'Delete',
	   'Insert' => 'Insert',
	   'Select' => 'Select',
	   'Userid' => 'Userid',
	   'Position' => 'Position',
	   'Eoy' => 'Eoy',

	   'Restore' => 'Restore',
	   'Reinstate' => 'Reinstate',
   
	   
	   );

use DBI;
use CGI;

my $self = 'staffrestore.pl';

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

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

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



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

if ( $arr{page} == 1 ) { # calendar popup.
    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>\n};
if ( not $arr{page} ) {
    print qq{<body onload="document.forms[0].elements[1].focus()">\n};
} else {
    print qq{<body>\n};
}


print qq{<div>[ <a href="$homepage">$lex{Main}</a> | <a href="$eoypage">$lex{Eoy}</a> ]</div>\n};
print qq{<h1>$title</h1>\n};


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

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

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




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

    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" };
    print qq{style="padding:0.5em;border:1px solid gray;">\n};
    print qq{<tr><th></th><th>$lex{Staff}</th><th>User Id</th></tr>\n};
    print qq{<caption>$lex{Select} $lex{Staff} to $lex{Restore}</caption>\n};

    
    # Get all withdrawn staff members.
    my $sth = $dbh->prepare("select * from staffwd order by lastname, firstname");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	print qq{<tr><td class="ra"><input type="radio" name="userid" value="$r{userid}"></td>\n};
	print qq{<td><b>$r{lastname}</b>, $r{firstname}</td><td>$r{userid}</td></tr>\n};
    }

    print qq{<tr><td class="la" colspan="3"><input type="submit" value="$lex{Restore}"></td></tr>\n};
    print qq{</table>\n};
    print qq{</form>\n};
    print qq{</body></html>\n};

    exit;

} # end of showStartPage


#-----------------
sub confirmRestore {
#-----------------

    # foreach my $key (keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # only userid passed.
    
    my $userid = $arr{userid};

    # Set current time
    my @tim = localtime(time);
    my $year = @tim[5] + 1900;
    my $month = @tim[4] + 1;
    my $day = @tim[3];
    my $currdate = qq{$year-$month-$day};


    # Read the staff data.
    my $sth = $dbh->prepare("select * from staffwd where userid = ?"); 
    $sth->execute( $userid );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $ref = $sth->fetchrow_hashref;
    my %r = %$ref;

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

    
    # Check for userid collisions
    my $sth = $dbh->prepare("select count(*) from staff where userid = ?"); 
    $sth->execute( $userid );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $count = $sth->fetchrow;
    if ( $count ) {
	print qq{<h3>This userid:$userid exists!</h3>\n};
	print qq{<div>New Unique Userid <input type="text" style="width:100px;" name="newuserid"></div>\n};
    }
    
    print qq{<table cellspacing="0" cellpadding="3" border="0">\n};
    print qq{<tr><td class="bra">$lex{Name}</td><td class="la">$r{firstname} $r{lastname}</td></tr>\n};
    print qq{<tr><td class="bra">$lex{Userid}</td><td class="la">$userid</td></tr>\n};


    # Blank row
    # print qq{<tr><td class="ra">&nbsp;</td><td class="la"></td></tr>\n};

    
    # Withdraw Date
    print qq{<tr><td class="bra">$lex{Restore} $lex{Date}</td>\n};
    print qq{<td class="la"><input type="text" name="date" id="date" size="10" value="$currdate">\n};
    print qq{<button type="reset" id="start_trigger">...</button>\n};
    print qq{</td></tr>\n};

    # Description
    print qq{<tr><td class="bra">$lex{Description}</td>\n};
    print qq{<td class="la"><textarea name="description" rows="3" cols="50"></textarea></td></tr>\n};

    # Submit
    print qq{<tr><td colspan="2" class="cn"><input type="submit" };
    print qq{value = "$lex{Yes}, $lex{Restore} $r{firstname} $r{lastname}"></td></tr>\n};
    
    print qq{</table></form>\n};

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

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

    exit;
}



#---------------
sub restoreStaff {
#---------------

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

    # Date, Userid, Description passed, (could have newuserid also )

    # Get withdrawn staff member data
    my $sth = $dbh->prepare("select * from staffwd where userid = ?"); 
    $sth->execute( $arr{userid} );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $ref = $sth->fetchrow_hashref;
    my %r = %$ref;

    if ( not $ref ) { #Error!
	print qq{<h1>$lex{Error}: $lex{'Not Found'}!</h1>\n};
	print qq{</body></html>\n};
	exit;
    }

    delete $r{id};

    # Check for userid collision
    my $failflag;
    my $sth = $dbh->prepare("select count(*) from staff where userid = ?"); 
    $sth->execute( $userid );
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $count = $sth->fetchrow;
    if ( $count ) {
	print qq{<h3>This userid:$userid exists!</h3>\n};
	$failflag = 1;
    }
    
    if ( $arr{newuserid} ) { # try that userid
	$sth->execute( $arr{newuserid} );
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth->fetchrow;
	if ( $count ) {
	    print qq{<h3>This userid:$arr{newuserid} exists also!</h3>\n};
	    
	} else { # no count, clear failflag, change userid;
	    $failflag = 0;
	    $r{userid} = $arr{newuserid};
	}
    }

    if ( $failflag ) {
	print qq{<h2>Userid already exists in the staff table. Cannot Restore! Try a different New Userid</h2>\n};
	print qq{</body></html>\n};
	exit;
    }
    

   
    # Insert WD Staff Record into Staff table
    my (@fields, @qst, @values);
    foreach my $fld (keys %r ) {
	if ( defined $r{$fld} ) {
	    push @fields, $fld;
	    push @qst, '?';
	    push @values, $r{$fld};
	}
    }
    my $fields = join(',', @fields);
    my $qst = join(',', @qst);

    $sth = $dbh->prepare("insert into staff ( $fields ) values ( $qst )");
    $sth->execute( @values );

    if ( $DBI::errstr) {
	print qq{<h3>$lex{Insert} $lex{Staff} $lex{Error}:};
	print qq{$lex{Error}:$DBI::errstr</h3>\n};
	die qq{$lex{Error}:$self: $DBI::errstr\n};
    }
    
    print qq{<h3>$lex{Withdrawn} $lex{Staff} -> $lex{Staff}</h3>\n};


    # Delete Staff Record from withdrawn staff table
    $sth = $dbh->prepare("delete from staffwd where userid = ?"); 
    $sth->execute( $arr{userid} );

    if ( $DBI::errstr) {
	print qq{<h3>$lex{Delete} $lex{Withdrawn} $lex{Staff} $lex{Error}:};
	print qq{$lex{Error}:$DBI::errstr</h3>\n};
	die qq{$lex{Delete} $lex{Staff} $lex{Error}:$self: $DBI::errstr\n};
    }



    # Insert a restore/withdraw record.
    $sth = $dbh->prepare("insert into staff_transfer 
      ( userid, date, type, description, lastname, firstname, certificatenumber ) 
      values ( ?, ?, ?, ?, ?, ?, ? )");

    $sth->execute( $arr{userid}, $arr{date}, 'restore', $arr{description},
		   $r{lastname}, $r{firstname}, $r{certification1} ); 

    
    if ($DBI::errstr) {
	print qq{<h3>$lex{Insert} $lex{Error}:};
	print qq{$lex{Error}:$DBI::errstr</h3>\n};
	die qq{$lex{Insert} $lex{Error}:$self: $DBI::errstr\n};
    }


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

    exit;
    
} # end of restoreStaff
