#!/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}. " $@
\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$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title

\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}
\n}; } print qq{
\n}; print qq{\n}; print qq{

\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{\n}; print qq{\n}; # Show Category print qq{\n}; $first = 0; print qq{
$firstname $lastname
\n}; # New Category print qq{ $lex{or} $lex{New} $lex{Category} }; print qq{
}; # Description print qq{

\n}; } print qq{
\n}; print qq{
\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}
\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{
\n}; # Set Page to go to next my $page; if ( $mode eq 'edit' ) { $page = 6; } else { $page = 5; } # 5=delete print qq{\n}; print qq{

\n}; print qq{
}; print qq{$lex{Category} - $arr{category}
\n}; print qq{\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{}; if ( $description ) { print qq{\n}; } else { print qq{\n}; } $studcount++; if ( $studcount % 20 == 0 ) { print qq{
\n}; } else { # delete directly. print qq{name="$id" value="1" $studcheck>\n}; } print qq{ $lastname, $firstname$description
\n}; } } print qq{
\n}; print qq{

\n}; print qq{
\n}; exit; } #---------------- sub editByStudent { # display records to edit and allow changes. #---------------- # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\n}; } print qq{
\n}; print qq{\n}; print qq{

\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{\n}; print qq{\n}; $sth->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $id, $category, $description ) = $sth->fetchrow ) { # Show Category print qq{\n}; $first = 0; } if ( $first ) { # no records found. print qq{\n}; } print qq{
$firstname $lastname
\n}; # New Category print qq{ $lex{or} $lex{New} $lex{Category} }; print qq{
}; # Get Description print qq{
$lex{'No Record(s) Found'}

\n}; } print qq{
\n}; print qq{
\n}; exit; } #---------------- sub deleteConfirm { # display records to delete #---------------- # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\n}; } print qq{
\n}; print qq{\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{
$firstname $lastname
\n}; # Loop through all med recs for this student my $first = 1; print qq{\n}; $sth->execute( $studnum ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $id, $category, $description ) = $sth->fetchrow ) { print qq{\n}; print qq{\n}; $first = 0; $globalfirst = 0; } if ( $first ) { # no records found. print qq{\n}; } print qq{
$category - $description
$lex{'No Record(s) Found'}

\n}; } if ( not $globalfirst ) { # records found print qq{\n}; } print qq{
\n}; exit; } #---------------- sub deleteRecords { # delect med records #---------------- # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\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{

$lex{'Record(s) Deleted'}

\n}; } print qq{

[ $lex{Main} ]\n}; print qq{

\n}; print qq{

\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{
\n}; print qq{\n}; print qq{\n}; print qq{\n\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Show Category print qq{\n}; print qq{\n}; # Next Page Selection (studcheck) print qq{\n\n}; print qq{
$lex{'Student Group'} }; print qq{}; print qq{\n}; print qq{\n}; print qq{$lex{'Separate with Spaces'}, $lex{'Blank=All'}
$lex{or}
$lex{Student}
\n}; print qq{$lex{'Last,First/Last/Initials/Studnum'}
$lex{or}
$lex{Category}}; print qq{
}; print qq{$lex{'Next Page'} $lex{Checked}?}; print qq{
\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; exit; } #---------------- sub searchStudent { #---------------- # print qq{
Search Student
\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{

$lex{Error}

\n}; print qq{\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
\n}; if ( $rows < 1 ) { print qq{

$lex{'No Student(s) Found'}

\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Student} }; print qq{($lex{'Last,First/Last/Initials/Studnum'})
}; print qq{
\n}; print qq{
\n}; exit; } print qq{
\n}; my $page; if ( $mode eq 'edit' ) { $page = 2; } else { $page = 4; } # 4=delete print qq{\n}; print qq{\n}; print qq{\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{\n}; } print qq{
$lex{Student}$lex{Select}
$lastname, $firstname ($studnum)\n}; if ( $count ) { print qq{\n}; } else { print $lex{'No Record(s) Found'}; } print qq{
\n}; print qq{\n}; print qq{
\n}; exit; } # end of searchStudent #--------------- sub searchGroups { # select groups of students #--------------- # print qq{
Search Groups
\n}; # foreach my $key ( sort keys %arr) { print qq{K:$key V:$arr{$key}
\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{
\n}; my $page; if ( $mode eq 'edit' ) { $page = 2; } else { $page = 4; } # 4=delete print qq{\n}; # Print Student Information. print qq{

\n}; print qq{\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{WD}; } else { $wd = ''; } print qq{\n}; $studcount++; if ( $studcount % 20 == 0 ) { print qq{
$wd$students{$studnum}
\n}; } } print qq{
\n}; print qq{

\n}; print qq{
\n}; exit; } #---------------- sub updateRecords { #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\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
\n}; next; } if ( not $count ) { print qq{\n}; print qq{\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{$lex{'Not Found'}}; } # print line for this record. print qq{}; print qq{\n}; # Update the record $sth2->execute( $arr{$ckey},$arr{$dkey}, $id ); if ( $DBI::errstr ){ print $DBI::errstr; last; } } # Next Student print qq{
$lex{Student}$lex{Medical} $lex{Record}
$count: $lastname, $firstname ($studnum)$arr{$ckey}: $arr{$dkey}
\n}; if ( not $DBI::errstr and $count ) { print qq{

$lex{'Record(s) Updated'}.

\n}; } else { print qq{

$lex{'No Change'}.

\n}; } print qq{

[ $lex{Main} ]\n}; print qq{

\n}; print qq{