#!/usr/bin/perl
#  Copyright 2001-2022 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 = ( 'Student #' => 'Student #',
	    'Associate Pictures and Students' => 'Associate Pictures and Students',
	    'Associate' => 'Associate',
	    'No Pictures Found' => 'No Pictures Found',
	    'No Student(s) Found' => 'No Student(s) Found',
	    'Main' => 'Main',
	    'Export' => 'Export',
	    'Error' => 'Error',
	    'Delete Failed' => 'Delete Failed',
	    'File already exists' => 'File already exists',
	    'Deleted' => 'Deleted',
	    'Renaming' => 'Renaming',

	   );

use CGI;
use DBI;


my $tempimage = "temp$$.jpg";
my $self = 'associate.pl';

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

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

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

my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) =
localtime(time);
$year = $year + 1900;
$mon++;
my $currdate = "$year-$mon-$mday";
$year = $year - 2; # go back 2 years to find withdrawn students for association in withdrawn
my $startdate = "$year-$mon-$mday";



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


my $title = $lex{'Associate Pictures and Students'};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" type="text/css" href="$css">\n};
print qq{<style type="text/css">body { background-color: #DC9;}
div.pic { float:left; height:$divheight px; width:$divwidth px; padding: 10px; 
 border: 1px solid; border-color:#AAA #AAA #444 #444; margin:3px; 
 background-color: #EEE;
}\n};
print qq{</style>\n};
print qq{$chartype\n</head><body>\n};

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

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

if ( $arr{associateflag} ) {
    delete $arr{associateflag};
    associate();  # do associate function.
}


# Read Files in bigpic folder
my $largemode = '1';
opendir (DH,"$picdir") || die "Cannot open $picdir directory!\n";
my @files = grep !/^\./, readdir DH;
close DH;

if ( not @files ) { # Try the Thumbnail folder instead.
    opendir (DH,"$tndir") || die "Cannot open $tndir directory!\n";
    @files = grep !/^\./, readdir DH;
    close DH;
    $largemode = 0;
}

if ( not @files ) { # Still no files... fail.
    print qq{<p>$lex{'No Pictures Found'}</p></body></html>\n};
    exit;
}

# Now find those files with and without numeric value. Numerics values
# are student numbers and those that are still text need to be
# associated. The files become numeric and the pic field gets set to 'Y'.
my (@doneFiles, @undoneFiles, @doneStudents); # undone still are text; done are studentnumber.jpg.
foreach my $file ( @files ) {
    my ($fn,$ext) = split /\./,$file;
    if ( $fn =~ m/\d+/ ) { # match one or more digits
	push @doneFiles, $file;
	push @doneStudents, $fn;
    } else {
    	push @undoneFiles, $file;
    }
}
    
# Verify all DONE files that we have the 'pic' field set.
my $sth = $dbh->prepare("select studid, pic from student where studnum = ?");
my $sth1 = $dbh->prepare("select studid, pic from studentwd where studnum = ?");

my $sth2 = $dbh->prepare("update student set pic = 'Y' where studid = ?");
my $sth3 = $dbh->prepare("update studentwd set pic = 'Y' where studid = ?");

my $verifyflag = 1;
foreach my $studnum ( @doneStudents ) { # matches @doneFiles
    $sth->execute($studnum);
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
    my ($studid, $pic) = $sth->fetchrow;
    
    if ( not $studid ) { # must be in withdrawn student table.
	$sth1->execute($studnum);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	my ($studid, $pic) = $sth1->fetchrow;
	if ( not $studid ) {
	    print qq{<div>Student not found for $studnum</div>\n};
	    next;
	} else { # we have a studid value
	    if ( not $pic ) { # we must set pic field in wd student table.
		$sth3->execute($studid);
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
		$verifyflag = 0;
		print qq{<div>Picture field set in withdrawn student table: $studnum</div>\n};
	    } # otherwise ok.
	}
    } else { #studid in the main student table.
	if ( not $pic ) { # we must set pic field in wd student table.
	    $sth2->execute($studid);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    print qq{<div>Picture field set in student table: $studnum</div>\n};
	    $verifyflag = 0;
	} # otherwise ok.
    }
}
if ( $verifyflag ) { #
    print qq{<h3>All associated student pictures have a matched pic field. All OK</h3>\n};
}



# Populate Hash with student numbers, studentname for students w/o pic field set
my (%studnum, %studname);
my $sth = $dbh->prepare("select lastname, firstname, studnum from student where pic != 'Y'");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
while ( my ( $lastname, $firstname, $studnum ) = $sth->fetchrow ){
    $lastname = lc $lastname;
    $firstname = lc $firstname;
    $studname{"$firstname$lastname"} = $studnum;
    $studnum{$studnum} = '1';
#    print qq{<div>$firstname $lastname $studnum</div>\n};
}

# now do withdrawn students in this school year.
my $sth = $dbh->prepare("select s.lastname, s.firstname, s.studnum from studentwd s, transfer t
			where t.studnum = s.studnum and t.type = 'withdraw' and 
			to_days(date) > to_days('$startdate')");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
while ( my ( $lastname, $firstname, $studnum ) = $sth->fetchrow ){
    $lastname = lc $lastname;
    $firstname = lc $firstname;
    $studname{"$firstname$lastname"} = $studnum;
    $studnum{$studnum} = '1';
#    print qq{<div>$firstname $lastname $studnum</div>\n};
}

#print "<div>KEYS to ADD</div>";
#foreach my $key ( sort keys %studname ) {
#    print qq{<div>$key VAL:$studname{$key}</div>\n};
#}



# These are the lookup values to match a massaged name key to a studnum value
#foreach my $key ( sort keys %studname ) {
#    print qq{<div>K:$key VAL:$studname{$key}</div>\n};
#}

=head

# Go through @files (pictures) and remove those that exist in student
# hash (ie. already associated.)
my @newfiles;
foreach my $file ( @files ) {
    my ($fn,$ext) = split /\./,$file;
    if ( not $studnum{$fn} ){ # if NOT already associated...
	push @newfiles, $file;
#	print qq{<div>NEW:$file</div>\n};
    }
}

=cut


@files = @undoneFiles;

if ( not @files ) {
    print qq{<h3>All students have associated Pictures</h3>\n};
    print qq{</body></html>\n};
    exit;
}


# Now Display the pictures...
print qq{<form action="$self" method="post">\n};
print qq{<input type="hidden" name="associateflag" value="1">\n};
print qq{<input type="submit" value="$lex{Associate}"><br>\n};

foreach my $file ( @files ){ # Do files one after another.

    my ($fname, $ext ) = split /\./, $file;
    
    my $value; # input value
    # OLD if ( $fname =~ m/\d+/ ) { $value = $fname; } # is it a digit?
    
    # if the filename matches a joined firstnamelastname value put in student number
    if ( $studname{$fname} ) {
	$value = $studname{$fname};
    }

    
    if ( $largemode ) {
	print qq{<div class="pic"><img src="$picdirurl/$file" alt="$file" };
    } else {
	print qq{<div class="pic"><img src="$tndirurl/$file" alt="$file" };
    }
    print qq{height="$thumbheight"><br>$file<br>\n};

    print qq{<input type="text" name="$file" style="width:10ch;" value="$value">\n};
    print qq{<br>$lex{'Student #'}</div>\n\n};  # studnum

}

print qq{<div style="clear:left;">};
print qq{<input type="submit" value="$lex{Associate}"></div>\n};
print qq{</form></body></html>\n};



#-----------
sub doDelete { # Delete Picture files
#-----------

    my $filename = shift;
    my $result = system ("rm -f $picdir/$filename");
    if ( $result ){ 
	print qq{$lex{Error} $lex{'Delete Failed'} $filename:  $?<br>\n};
    } else {
	print qq{<div>$filename $lex{Deleted}</div>\n};
    }

}



#------------
sub associate {  # do the association
#------------

    #foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}<br>\n}; }
    
    # passed filenames as key and matching student number as value

    # for each key:value picture:student passed 1) check for digits or
    # name and look up student. 2) If present, then check for existing
    # image file. 3) If not present then rename passed key file to
    # student#.jpg, and update student record to Y in pic field.

    my $sth = $dbh->prepare("select studid from student where studnum = ?");

    my $sth1 = $dbh->prepare("select studid from studentwd where studnum = ?");


    
    foreach my $key (keys %arr){

	if ( $arr{$key} ) { # We have a passed value for student; $key is filename.

	    my $studnum = $arr{$key}; # just so we're clear
	    my $filename = $key; 
	    my $table = 'student';
	    
	    # Find which table to update with a pic field = 'Y';
	    $sth->execute( $studnum );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    my $studid = $sth->fetchrow;
	    
	    if ( not $studid ) { # check in withdrawn table.
		$sth1->execute( $studnum );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
		$studid = $sth1->fetchrow;
		$table = 'studentwd';
		
	    } elsif ( not $studid )  { # Still...No student found.
		print qq{<br>$lex{'No Student(s) Found'}: $key - ($arr{$key})<br>\n};
		next;
	    }

	    
	    # rename the image file, if necessary.
	    my $newfilename = qq{$studnum.jpg};
	    #print qq{Filename: $newfilename <br>\n};

	    #check for existence of file already.
	    if (-e $newfilename){
		print qq{$lex{'File already exists'}: $firstname $lastname };
		print qq{($newfilename)<br>\n};
		next;
		
	    } else { 

              # We can rename current file and update student pic field to 'Y';
		print qq{$lex{Renaming}: $key &gt; $newfilename<br>\n};
		rename "$picdir/$key", "$picdir/$newfilename";
		rename "$tndir/$key", "$tndir/$newfilename";

		# update the pic field in student or studentwd table.
		my $sth = $dbh->prepare("update $table set pic = 'Y' where studnum = ?");
		$sth->execute( $studnum );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
	    }

	} # End of If passed a value

    } # End of Loop of %arr hash.


    
}
