#!/usr/bin/perl
#  Copyright 2001-2020 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.

#  Description: Grade based subject enrollment. List all the students
#   for all the subjects in that grade. Deselect those that you don't
#   have in those subjects.

my %lex = ('Main' => 'Main',
	   'Student Group' => 'Student Group',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'Medical' => 'Medical',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Records' => 'Records',
	   'Student' => 'Student',
	   'Checked' => 'Checked',
	   'Blank=All' => 'Blank=All',
	   'Separate with Spaces' => 'Separate with Spaces',
	   'Category' => 'Category',
	   'New' => 'New',
	   'Next Page' => 'Next Page',
	   'Error' => 'Error',
	   'Checked' => 'Checked',
	   'Student Number' => 'Student Number',
	   'Last,First/Last/Initials/Studnum' => 'Last,First/Last/Initials/Studnum',
	   'No Student(s) Found' => 'No Student(s) Found',
	   'Delete Record(s)' => 'Delete Record(s)',
	   'No Record(s) Found' => 'No Record(s) Found',
	   'Record(s) Deleted' => 'Record(s) Deleted',
	   'Update Record(s)' => 'Update Record(s)',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Record' => 'Record',
	   'No Change' => 'No Change',
	   'or' => 'or',
	   'Not Found' => 'Not Found',
	   'Select' => 'Select',

	   );

my $self = 'meddeled.pl';

use DBI;
use CGI;

eval require "../../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 = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;

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

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

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


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

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    if ( $arr{student} ) {
	searchStudent( $arr{student} );
    } elsif ( $arr{category} ) {
	categorySelect();
    } else {
	searchGroups();
    }


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

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

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

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

} elsif ( $arr{page} == 6 ) {
    delete $arr{page};
    editById();

} 


#-----------
sub editById { # display records to edit and allow changes.
#-----------

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

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<p><input type="submit" value="$lex{'Update Record(s)'} "></p>\n};

    # Get the Category
    # Get Category
    my @category;
    my $sth = $dbh->prepare("select distinct category from student_medical where category is not NULL
      order by category");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $cat = $sth->fetchrow )  {
	push @category, $cat;
    }

    # SQL Setup.
    $sth = $dbh->prepare("select studnum, category, description from student_medical
        where id = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");


    foreach my $key ( sort keys %arr) {

	my $id = $arr{$key};  # id of the medical record to edit.

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

	print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
	print qq{<caption style="font-size:150%;font-weight:bold;">$firstname $lastname</caption>\n};

	# Show Category
	print qq{<tr><td><select name="C:$id"><option>$category</option>\n};
	foreach my $cat ( @category ) {
	    if ( $cat ne $category ) { print qq{<option>$cat</option>\n}; }
	}
	print qq{<option></option></select>\n};

	# New Category
	print qq{ $lex{or} $lex{New} $lex{Category} };
	print qq{<input type="text" name="N:$id" size="20"><br>};

	# Description
	print qq{<textarea rows="4" cols="80" name="D:$id">$description</textarea></td></tr>\n};
	$first = 0;

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

    }

    print qq{<br clear="left"><input type="submit" value="$lex{'Update Record(s)'} ">\n};
    print qq{</form></body></html>\n};

    exit;

} # end of editById




#----------------
sub categorySelect { # select category group records to edit.
#----------------

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

    my ($mode, $modetext);
    if ( $arr{edit} ) {
	$mode = 'edit';
	$modetext = $lex{Edit};
    } else {
	$mode = 'delete';
	$modetext = $lex{Delete};
    }

    my $studcheck;
    if ( $arr{studcheck} ) {
	$studcheck = 'checked="checked"';
    }

    # Print Form start
    print qq{<form action="$self" method="post">\n};

    # Set Page to go to next
    my $page;
    if ( $mode eq 'edit' ) { $page = 6; } else { $page = 5; } # 5=delete
    print qq{<input type="hidden" name="page" value="$page">\n};

    print qq{<p><input type="submit" value="$modetext - $lex{Continue}"></p>\n};
    print qq{<div style="font-size:130%;font-weight:bold;padding:0.5em;">};
    print qq{$lex{Category} - $arr{category}</div>\n};

    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;">\n};


    # Get the records for this category, sorted by student
    my $sth = $dbh->prepare("select s.lastname, s.firstname, s.studnum, sm.id, sm.description 
       from studentall as s, student_medical as sm 
       where s.studnum = sm.studnum and category = ?
       order by lastname, firstname");
    $sth->execute( $arr{category} );
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

    my $studcount = 0;

    while ( my ( $lastname, $firstname, $studnum, $id, $description ) = $sth->fetchrow ) {

	print qq{<tr><td><input type="checkbox" };
	if ( $mode eq 'edit' ) {
	    print qq{name="$lastname$firstname$studnum$id" value="$id" $studcheck>\n};
	} else { # delete directly.
	    print qq{name="$id" value="1" $studcheck>\n};
	}

	print qq{ <b>$lastname</b>, $firstname</td>};
	if ( $description ) { print qq{<td>$description</td></tr>\n}; }
	else { print qq{</tr>\n}; }

	$studcount++;
	if ( $studcount % 20 == 0 ) {
	    print qq{</table><table cellpadding="3" cellspacing="0" border="1" style="float:left;">\n};
	}

    }

    print qq{</table>\n};
    print qq{<br clear="left"><p><input type="submit" value="$modetext - $lex{Continue}"></p>\n};
    print qq{</form></table></body></html>\n};
    
    exit;

}


#----------------
sub editByStudent { # display records to edit and allow changes.
#----------------

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

    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<p><input type="submit" value="$lex{'Update Record(s)'} "></p>\n};

    # Get the Category
    # Get Category
    my @category;
    my $sth = $dbh->prepare("select distinct category from student_medical order by category");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $ref = $sth->fetchall_arrayref;
    foreach my $val ( @$ref ) {
	if ( $val->[0] ) { push @category, $val->[0]; }
    }


    # SQL Setup.
    $sth = $dbh->prepare("select id, category, description from student_medical
        where studnum = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");


    foreach my $key ( sort keys %arr) {
	my ($ignore, $studnum ) = split ':', $key;

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

	# Loop through all med recs for this student
	my $first = 1;
	print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.5em;">\n};
	print qq{<caption style="font-size:150%;font-weight:bold;">$firstname $lastname</caption>\n};
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $id, $category, $description ) = $sth->fetchrow ) {

	    # Show Category
	    print qq{<tr><td><select name="C:$id"><option>$category</option>\n};
	    foreach my $cat ( @category ) {
		if ( $cat ne $category ) { print qq{<option>$cat</option>\n}; }
	    }
	    print qq{</select>\n};

	    # New Category
	    print qq{ $lex{or} $lex{New} $lex{Category} };
	    print qq{<input type="text" name="N:$id" size="20"><br>};


	    # Get Description
	    print qq{<textarea rows="4" cols="80" name="D:$id">$description</textarea></td></tr>\n};
	    $first = 0;
	}

	if ( $first ) { # no records found.
	    print qq{<tr><td colspan="2">$lex{'No Record(s) Found'}</td></tr>\n};
	}
	print qq{</table><p></p>\n};

    }

    print qq{<br clear="left"><input type="submit" value="$lex{'Update Record(s)'} ">\n};
    print qq{</form></body></html>\n};

    exit;

}



#----------------
sub deleteConfirm { # display records to delete
#----------------
 
    # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}<br>\n}; }

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

    # SQL Setup.
    my $sth = $dbh->prepare("select id, category, description from student_medical
          where studnum = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $globalfirst = 1;

    foreach my $key ( sort keys %arr) {

	my ($ignore, $studnum ) = split /:/, $key;

	# Get student name
	$sth1->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	print qq{<div style="font-size:150%;font-weight:bold;">$firstname $lastname</div>\n};

	# Loop through all med recs for this student
	my $first = 1;
	print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	$sth->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $id, $category, $description ) = $sth->fetchrow ) {
	    print qq{<tr><td><input type="checkbox" name="$id" value="1"></td>\n};
	    print qq{<td><b>$category</b> - $description</td></tr>\n};
	    $first = 0;
	    $globalfirst = 0;
	}

	if ( $first ) { # no records found.
	    print qq{<tr><td colspan="2">$lex{'No Record(s) Found'}</td></tr>\n};
	}

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

    }

    if ( not $globalfirst ) { # records found
	print qq{<input type="submit" value="$lex{'Delete Record(s)'} ">\n};
    }
    print qq{</form></body></html>\n};

    exit;

}


#----------------
sub deleteRecords { # delect med records
#----------------

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

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

    foreach my $key ( keys %arr) {
	$sth->execute( $key );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
    }
    
    if ( not $DBI::errstr ) {
	print qq{<p>$lex{'Record(s) Deleted'}</p>\n};
    }

    print qq{<p>[ <a href="$homepage">$lex{Main}</a> ]\n};
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="submit" value="};
    print qq{$lex{Edit}/$lex{Delete} $lex{Medical} $lex{Records}};
    print qq{"></form></p>\n};

    exit;

}


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

    # Get the Category
    my @category;
    my $sth = $dbh->prepare("select distinct category from student_medical where category is not NULL
      order by category");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $cat = $sth->fetchrow )  {
	push @category, $cat;
    }


    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};

    print qq{<tr><td style="text-align:right;font-weight:bold;">$lex{'Student Group'} };
    print qq{</td>\n<td align="left">};
    print qq{<select name="group"><option>$lex{Grade}</option>\n<option>};
    print qq{$lex{Homeroom}</option><option>$lex{'Student Number'}</option></select>\n};
    print qq{<input type="input" name="groupid" size="12">\n};
    print qq{$lex{'Separate with Spaces'}, $lex{'Blank=All'}</td></tr>\n};

    print qq{<tr><td></td><td class="bla">$lex{or}</td></tr>\n};

    print qq{<tr><td style="text-align:right;font-weight:bold;">$lex{Student}</td>\n};
    print qq{<td><input type="text" name="student" size="30"><br>\n};
    print qq{$lex{'Last,First/Last/Initials/Studnum'}</td></tr>\n};

    print qq{<tr><td></td><td class="bla">$lex{or}</td></tr>\n};

    # Show Category
    print qq{<tr><td style="text-align:right;font-weight:bold;">$lex{Category}</td>\n};
    print qq{<td class="la">};
    print qq{<select name="category"><option></option>\n};
    foreach my $cat ( @category ) {
	print qq{<option>$cat</option>\n};
    }
    print qq{</select></td></tr>\n};    

    # Next Page Selection (studcheck)
    print qq{<tr><td style="text-align:right;font-weight:bold;">};
    print qq{$lex{'Next Page'} $lex{Checked}?</td>\n<td>};
    print qq{<input type="checkbox" name="studcheck" value="1"></td></tr>\n};

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

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

    exit;

}


#----------------
sub searchStudent {
#----------------


    # print qq{<div>Search Student</div>\n};
    
    # Set Mode: Edit or Delete
    my ($mode, $modetext);
    if ( $arr{edit} ) {
	$mode = 'edit';
	$modetext = $lex{Edit};
    } else {
	$mode = 'delete';
	$modetext = $lex{Delete};
    }

    my $student = shift;

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

    } else { # we have words possibly with a comma
	($lastname,$firstname)  = split /\,/, $student;
	$firstname =~ s/^\s*//;
	$lastname =~ s/^\s*//;
	if ($lastname and $firstname){ # both entered.
	    $sth = $dbh->prepare("select  lastname, firstname, studnum from studentall 
             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 lastname, firstname, studnum from studentall 
                 where lastname $sql{like} ? and firstname $sql{like} ?");
		$sth->execute( $li, $fi );

	    } else {
		$sth = $dbh->prepare("select lastname, firstname, studnum from studentall 
                  where lastname = ? order by firstname");
		$sth->execute( $lastname );
	    }
	} else { # print an error....
	    print qq{<h1>$lex{Error}</h1>\n};
	    print qq{</body></html>\n};
	    exit;
	}

    } # Last Else
    # We should now have a $sth defined.


    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $rows = $sth->rows;
    # print qq{Rows: $rows<br>\n};


    if ( $rows < 1 ) { 
	print qq{<h1>$lex{'No Student(s) Found'}</h1>\n};
	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};
	print qq{<form action="$self" method="post">\n};
	print qq{<input type="hidden" name="page" value="1">\n};

	print qq{<table cellspacing="0" cellpadding="3" border="0">\n};

	print qq{<tr><td colspan="2" style="text-align:center;">$lex{Student} };
	print qq{($lex{'Last,First/Last/Initials/Studnum'})<br>};
	print qq{<input type="text" name="student" size="30"></td></tr>\n};

	print qq{<tr><td colspan="2" style="text-align:center;">\n};
	print qq{<input type="submit" value="$lex{Continue}"></td></tr>\n};
	print qq{</table></form></body></html>\n};
	exit; 
    }

    print qq{<form action="$self" method="post">\n};

    my $page;
    if ( $mode eq 'edit' ) { $page = 2; } else { $page = 4; } # 4=delete
    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{Select}</th></tr>\n};

    my $sth1 = $dbh->prepare("select count(*) from student_medical where studnum = ?");

    my $first = 1;
    while ( my ($lastname, $firstname, $studnum) = $sth->fetchrow ) {

	# Find any transactions; otherwise skip
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	print qq{<tr><td><b>$lastname</b>, $firstname ($studnum)</td><td>\n};
	if ( $count ) {
	    print qq{<input type="checkbox" name="$lastname$firstname$studnum:$studnum" value="1">\n};
	} else {
	    print $lex{'No Record(s) Found'};
	}
	print qq{</td></tr>\n};

    }

    print qq{</table>\n};
    print qq{<input type="submit" value="$modetext - $lex{'Continue'}">\n};
    print qq{</form></body></html>\n};

    exit;

} # end of searchStudent


#---------------
sub searchGroups { # select groups of students
#---------------

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

    my ($mode, $modetext);
    if ( $arr{edit} ) {
	$mode = 'edit';
	$modetext = $lex{Edit};
    } else {
	$mode = 'delete';
	$modetext = $lex{Delete};
    }

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

    $table = 'studentall'; # student data source
    my $group;
    if ( $arr{group} eq $lex{Grade} ) {
	$group = 'grade';
    } elsif ( $arr{group} eq $lex{Homeroom} ) {
	$group = 'homeroom';
    } elsif (  $arr{group} eq $lex{'Student Number'} ) {
	$group = 'studnum';
    } else { $group = 'grade'; } # default to grade.

    my @groups = split(/\s/, $arr{groupid});

    my $select;
    my $orderby;
    my @students;
    if ( not @groups ) { # select all students 
	my $sth = $dbh->prepare("select lastname, firstname, studnum from $table
          order by lastname, firstname");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my ( $lastname, $firstname, $studnum ) = $sth->fetchrow ) {
	    push @students, $studnum;
	    $students{$studnum} = "$lastname, $firstname";
	}

	
    } elsif ( $group eq 'studnum' ) { # student numbers

	my $sth = $dbh->prepare("select lastname, firstname, grade from $table 
          where studnum = ?");
	foreach my $studnum ( @groups ) {
	    $sth->execute( $studnum );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my ( $lastname, $firstname, $grade ) = $sth->fetchrow;
	    push @students, $studnum;
	    $students{$studnum} = "$lastname, $firstname ($grade)";
	}
	@students = @groups;
	

    } else { # we have groups of students 

	my $sth = $dbh->prepare("select lastname, firstname, studnum, $group from $table
          where $group = ? order by lastname, firstname");

	foreach my $grp ( @groups ) {
	    $sth->execute( $grp );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    while ( my ( $lastname, $firstname, $studnum, $grp ) = $sth->fetchrow ) {
		push @students, $studnum;
		$students{$studnum} = "$lastname, $firstname ($grp)";
	    }
	}
    }
    # All students now in @students and %students;


    # Print Form start
    print qq{<form action="$self" method="post">\n};

    my $page;
    if ( $mode eq 'edit' ) { $page = 2; } else { $page = 4; } # 4=delete
    print qq{<input type="hidden" name="page" value="$page">\n};

    # Print Student Information.
    print qq{<p><input type="submit" value="$modetext - $lex{Continue}"></p>\n};
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:0.4em;">\n};

    my $sth1 = $dbh->prepare("select count(*) from student_medical where studnum = ?");
    my $sth2 = $dbh->prepare("select count(*) from studentwd where studnum = ?");
    
    my $studcount = 0;
    foreach my $studnum ( @students ){  #loop through students

	# Check if they have records.
	$sth1->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $reccount = $sth1->fetchrow;
	if ( not $reccount ) { next; } # skip with no record.

	# Check if withdrawn
	$sth2->execute( $studnum );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $wd = $sth2->fetchrow;
	if ( $wd ) { $wd = qq{<span style="color:red;font-size:120%;">WD</span>};
	} else { $wd = ''; }

	
	print qq{<tr><td> $wd<input type="checkbox" name="$students{$studnum}:$studnum" };
	print qq{value="1" $studcheck>$students{$studnum}</td></tr>\n};

	
	$studcount++;
	if ( $studcount % 20 == 0 ) {
	    print qq{</table><table cellpadding="3" cellspacing="0" border="1" };
	    print qq{style="float:left;margin:0.4em;">\n};
	}

    }

    print qq{</table>\n};
    print qq{<br clear="left"><p><input type="submit" value="$modetext - $lex{Continue}"></p>\n};
    print qq{</form></body></html>\n};

    exit;

}


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

    my $sth = $dbh->prepare("select category, description, studnum from student_medical where id = ?");
    my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?");
    my $sth2 = $dbh->prepare("update student_medical set category = ?, description = ? where id = ?");

    my $count = 0;
    foreach my $key ( sort keys %arr ) {

	my ( $field, $id ) = split(/:/,$key);
	if ( $field eq 'D' or $field eq 'N' ) { next; } # skip description(D) and new category(N).

	my $ckey = $key;
	my $dkey = 'D:'. $id;
	my $nkey = 'N:'. $id;

	if ( $arr{$nkey} ) { $ckey = $nkey; } # new category replaces existing category.

	# Check for update
	$sth->execute( $id );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my ( $cat, $desc, $studnum ) = $sth->fetchrow;


	if ( $cat eq $arr{$ckey} and $desc eq $arr{$dkey} ) {
	    # print qq{No Change<br>\n};
	    next;
	} 
	if ( not $count ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
	    print qq{<tr><th>$lex{Student}</th><th>$lex{Medical} $lex{Record}</th></tr>\n};
	}

	$count++; 

	# Get Student Name
	$sth1->execute( $studnum );
	if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my ( $lastname, $firstname ) = $sth1->fetchrow;
	if ( not $lastname ) { 
	    $lastname = qq{<span style="color:red;">$lex{'Not Found'}</span>}; 
	}
	
	# print line for this record.
	print qq{<tr><td>$count: <b>$lastname</b>, $firstname ($studnum)</td>};
	print qq{<td><b>$arr{$ckey}</b>: $arr{$dkey}</tr>\n};

	# Update the record
	$sth2->execute( $arr{$ckey},$arr{$dkey}, $id );
	if ( $DBI::errstr ){ print $DBI::errstr; last; }
	
    } # Next Student

    print qq{</table>\n};
    if ( not $DBI::errstr and $count ) {
	print qq{<h3>$lex{'Record(s) Updated'}.</h3>\n};
    } else {
	print qq{<h3>$lex{'No Change'}.</h3>\n};
    }

    print qq{<p>[ <a href="$homepage">$lex{Main}</a> ]\n};
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="submit" value="};
    print qq{$lex{Edit}/$lex{Delete} $lex{Medical} $lex{Records}};
    print qq{></form></p>\n};

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

    exit;

} # End of UpdateRecords
