#!/usr/bin/perl # Copyright 2001-2008 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', 'Report Card' => 'Report Card', 'Select a Student' => 'Select a Student', 'Select a Record' => 'Select a Record', 'Save Record' => 'Save Record', 'Edit Data Record' => 'Edit Data Record', 'Record Updated' => 'Record Updated', 'Edit Transcript Records' => 'Edit Transcript Records', 'Record Deleted' => 'Record Deleted', 'New Student Edit' => 'New Student Edit', 'Delete the Record' => 'Delete the Record', 'Confirm Record Delete' => 'Confirm Record Delete', 'Code' => 'Code', 'Subject' => 'Subject', 'Area' => 'Area', 'Term' => 'Term', 'Grade' => 'Grade', 'Difficulty' => 'Difficulty', 'School Year' => 'School Year', 'Description' => 'Description', 'Date' => 'Date', 'Credit' => 'Credit', 'Mark' => 'Mark', 'Edit' => 'Edit', 'Delete' => 'Delete', 'Error' => 'Error', 'Student Number' => 'Student Number', 'First Name' => 'First Name', 'Middle Name' => 'Middle Name', 'Last Name' => 'Last Name', 'Birthdate' => 'Birthdate', 'Student Id' => 'Student Id', 'Edit Identity' => 'Edit Identity', ); my $self = 'tscdeled.pl'; use DBI; use CGI; eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $q = CGI->new; print $q->header; my %arr = $q->Vars; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # Page Header print "$doctype\n". $lex{'Edit Transcript Records'}. " $chartype\n\n"; print "[ ". $lex{Main}. " |\n"; print "". $lex{'Report Card'}. " ]\n"; if ( $arr{studid} ) { # We have a selected student. showSelectedStudent( $arr{studid} ); } elsif ( $arr{dataid} ) { editDataRecord( $arr{dataid} ); } elsif ( $arr{identid} ) { editIdentRecord( $arr{identid} ); } elsif ( $arr{writeidentflag} ) { delete $arr{writeidentflag}; writeIdentRecord(); } elsif ( $arr{delid} ) { confirmDataDelete( $arr{delid} ); } elsif ( $arr{writedataflag} ) { delete $arr{writedataflag}; writeDataRecord(); } elsif ( $arr{deletedataflag} ) { delete $arr{deletedataflag}; deleteDataRecord( $arr{id} ); } # Student Selection my $sth = $dbh->prepare("select id, studnum, lastname, middlename, firstname from tscriptident order by lastname, firstname"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Print Start of Selection. print "

". $lex{'Edit Transcript Records'}. "

\n"; print ""; print "\n"; my $count = 1; print ""; while ( my ( $id, $studnum, $lastname, $middlename, $firstname ) = $sth->fetchrow ) { print "\n"; if (not ($count % 3)){ print "\n";} $count++; } if ($count % 3 == 2){ print "\n"; } if ($count % 3 == 0){ print "\n"; } print "
". $lex{'Select a Student'}. "
$lastname, "; print "$firstname $middlename ($studnum)
\n"; #---------------------- sub showSelectedStudent { #---------------------- my $id = shift; # Get Student Ident my $sth = $dbh->prepare("select studnum, lastname, middlename, firstname from tscriptident where id = ?"); $sth->execute( $id ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($studnum, $lastname, $middlename, $firstname) = $sth->fetchrow; # Get Student Data $sth = $dbh->prepare("select id, subjecttext, subjectcode, subjectarea, schoolyear, term_desc from tscriptdata where studnum = ? order by schoolyear, term"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } print "

". $lex{'Edit Transcript Records'}. " – "; print "$firstname $middlename $lastname

\n"; print "
\n"; print "\n"; print "
\n"; print ""; print "\n"; while (my ( $id, $subjectcode, $subjecttext, $subjectarea, $schoolyear, $term_desc ) = $sth->fetchrow ) { print ""; print ""; print "\n"; } print "
". $lex{'Select a Record'}. "
$subjecttext ($subjectcode)$subjectarea$schoolyear - $term_desc". $lex{Edit}. "\n"; print ""; print "". $lex{Delete}. ""; print "
\n"; exit; } #----------------- sub editIdentRecord { #----------------- my $identid = shift; # Get Ident Record my $sth = $dbh->prepare("select * from tscriptident where id = ?"); $sth->execute( $identid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($id,$studnum, $lastname, $firstname, $middlename, $birthdate, $studentid ) = $sth->fetchrow; # Note: $studentid may be a state or provincial identity number. NOT studnum (local). # The field is set in the transcript posting script. print "

". $lex{'Edit Identity'}. " — $firstname $lastname

\n"; # Now print the form. print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n"; print "
". $lex{'Student Number'}. ""; print "$studnum
". $lex{'First Name'}. ""; print "
". $lex{'Middle Name'}. ""; print ""; print "
". $lex{'Last Name'}. ""; print "
". $lex{'Birthdate'}. ""; print "
". $lex{'Student Id'}. ""; print "
\n"; exit; } # End of editIdentRecord #------------------ sub writeIdentRecord { #------------------ my $studid = $arr{id}; # for passing value below foreach my $key (keys %arr ) { $arr{$key} = $dbh->quote( $arr{$key} ); #print "K:$key V:$arr{$key}
\n"; } my $sth = $dbh->prepare("update tscriptident set firstname = $arr{firstname}, middlename = $arr{middlename}, lastname = $arr{lastname}, birthdate = $arr{birthdate}, studentid = $arr{studentid} where id = $arr{id} "); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } print "

". $lex{'Record Updated'}. "

\n"; print "[ ". $lex{'Edit Transcript Records'}. " |\n"; print " ". $lex{'New Student Edit'}. " ]\n"; print "\n"; exit; } # end of writeIdentRecord #----------------- sub editDataRecord { #----------------- my $dataid = shift; require "../../etc/transcript.conf" or die "Cannot open transcript.conf!"; # Get Data Record my $sth = $dbh->prepare("select * from tscriptdata where id = ?"); $sth->execute( $dataid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($id,$studnum, $subjectcode, $subjecttext, $subjectarea, $score_mark, $score_letter, $score_diff, $schoolyear, $crdate, $credit, $term, $term_desc ) = $sth->fetchrow; $sth = $dbh->prepare("select lastname, firstname from tscriptident where studnum = ?"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname) = $sth->fetchrow; print "

". $lex{'Edit Data Record'}. " — $firstname $lastname

\n"; # Now print the form. print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n\n"; print "\n\n"; print "\n\n"; } else { print "\n"; } print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n"; print "\n"; print "\n"; print "
". $lex{Subject}. q{ }. $lex{Code}. ""; print "
". $lex{Subject}. ""; print "
". $lex{Subject}. q{ }. $lex{Area}. ""; if ( %gradRequirements ) { print "
". $lex{Mark}. ""; print "
". $lex{Letter}. q{ }. $lex{Grade}. ""; print "
". $lex{Difficulty}. ""; print "
". $lex{'School Year'}. ""; print "
". $lex{Date}. ""; print "
". $lex{Credit}. ""; print "
". $lex{Term}. ""; print "
". $lex{Term}. q{ }. $lex{Description}. ""; print "
\n"; exit; } #------------------ sub writeDataRecord { #------------------ foreach my $key (keys %arr ) { $arr{$key} = $dbh->quote( $arr{$key} ); #print "K:$key V:$arr{$key}
\n"; } my $sth = $dbh->prepare("update tscriptdata set subjectcode = $arr{subjectcode}, subjecttext = $arr{subjecttext}, subjectarea = $arr{subjectarea}, score_mark = $arr{score_mark}, score_letter = $arr{score_letter}, score_diff = $arr{score_diff}, schoolyear = $arr{schoolyear}, crdate = $arr{crdate}, credit = $arr{credit}, term = $arr{term}, term_desc = $arr{term_desc} where id = $arr{id} "); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } print "

". $lex{'Record Updated'}. "

\n"; # Now get student identid for record $sth = $dbh->prepare("select id from tscriptident where studnum = $arr{studnum}"); $sth->execute; my $studid = $sth->fetchrow; print "[ ". $lex{'Edit Transcript Records'}. " |\n"; print " ". $lex{'New Student Edit'}. " ]\n"; print "\n"; exit; } #------------------ sub deleteDataRecord { #------------------ my $id = shift; #foreach my $key (keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Get student ident record id from data record id (2 steps). my $sth = $dbh->prepare("select studnum from tscriptdata where id = ?"); $sth->execute( $id ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $studnum = $sth->fetchrow; $sth = $dbh->prepare("select id from tscriptident where studnum = ?"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $studid = $sth->fetchrow; # Delete the data record. $sth = $dbh->prepare("delete from tscriptdata where id = ?"); $sth->execute( $id ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Response print "

". $lex{'Record Deleted'}. "

\n"; print "[ ". $lex{'Edit Transcript Records'}. " |\n"; print " ". $lex{'New Student Edit'}. " ]\n"; print "\n"; exit; } #------------------ sub confirmDataDelete { #------------------ my $id = shift; #foreach my $key (keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Get Data Record my $sth = $dbh->prepare("select * from tscriptdata where id = ?"); $sth->execute( $id ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($id,$studnum, $subjectcode, $subjecttext, $subjectarea, $score_mark, $score_letter, $score_diff, $schoolyear, $crdate, $credit, $term, $term_desc ) = $sth->fetchrow; # Get Student Info $sth = $dbh->prepare("select id,lastname, firstname from tscriptident where studnum = ?"); $sth->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($studid, $lastname, $firstname) = $sth->fetchrow; print "

". $lex{'Confirm Record Delete'}. " — $firstname $lastname

\n"; # Now print the form. print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n\n"; print "\n"; print "
". $lex{Subject}. q{ }. $lex{Code}. ""; print "$subjectcode
". $lex{Subject}. ""; print "$subjecttext
". $lex{Subject}. q{ }. $lex{Area}. ""; print "$subjectarea
". $lex{Mark}. ""; print "$score_mark
". $lex{Letter}. q{ }. $lex{Grade}. ""; print "$score_letter
". $lex{Difficulty}. ""; print "$score_diff
". $lex{'School Year'}. ""; print "$schoolyear
". $lex{Term}. q{ }. $lex{Description}. ""; print "$term_desc
\n"; print "[ ". $lex{'Edit Transcript Records'}. " |\n"; print " ". $lex{'New Student Edit'}. " ]\n"; print "\n"; exit; }