ESF Inventory - Administration

#!/usr/local/bin/perl #Administrator Page use DBI; use CGI qw(:standard); my $q = new CGI; #Get their username so if they do any update/add's to the database it is recorded for stats and functions. my $username=$q->remote_user; #Variables my $errmsg; my $errtitle; my $seconds = 3; #Database Connection my $dbh = DBI->connect("dbi:Oracle:Secert.Universe", "secertusername", "secertpassword"); #Get everything in the database related to records. Dont grab stats stuff. my $st = $dbh->prepare(" select EQUIP_ID, EQUIP_DESC, EQUIP_QTY, EQUIP_UOM, EQUIP_DEPT, FROM_STORE, TO_CHAR(FROM_DATE,'MM/DD/YYYY'), RESERVED_STORE, RECEIVING_STORE, TO_CHAR(RECEIVING_DATE,'MM/DD/YYYY'), COMMENTS FROM secerttable.ENGINEERING_EQUIPMENT order by EQUIP_DESC ASC "); $st->execute(); open (ACCESS, ".htaccess"); my $ACCESS = ; close(ACCESS); unless (param()) { #Input Pages print header(), start_html('ESF Inventory - Administration'); print " ESF Inventory - Administration

$\"Admin$
Edit Mode - Add Mode - Exit Admin

"; #List out the different fields. while (my ($EQUIP_ID, $EQUIP_DESC, $EQUIP_QTY, $EQUIP_UOM, $EQUIP_DEPT, $FROM_STORE, $FROM_DATE, $RESERVED_STORE, $RECEIVING_STORE, $RECEIVING_DATE, $COMMENTS) = $st->fetchrow_array) { print" "; } print "

Inventory Edit Mode
Qty	Type	Equipment	Department	From Store	Date Received	Reserved Store	Receiving Store	Date Delivered	Comment	Delete Record	Update Record

"; #Stats #Get Top 5 Users my $gs = $dbh->prepare(" select * from (select EQUIP_DESC, UPDATE_DATE, UPDATE_ID from secerttable.ENGINEERING_EQUIPMENT ORDER BY UPDATE_DATE DESC ) where rownum <= 5 "); $gs->execute(); print " "; my $sCounter = 1; #Get last who updated on while (my ($sEQUIP_DESC, $sUPDATE_DATE, $sUPDATE_ID) = $gs->fetchrow_array) { print" "; $sCounter++; } #Get the number of inventory items my $wo = $dbh->prepare(" select rownum from secerttable.ENGINEERING_EQUIPMENT ORDER BY rownum DESC "); $wo->execute(); #Got the rownumbers. my $rownumbers = $wo->fetchrow_array; print " "; #Find the last updated date. my $ah = $dbh->prepare(" select UPDATE_DATE from secerttable.ENGINEERING_EQUIPMENT WHERE rownum <= 5 ORDER BY UPDATE_DATE DESC "); $ah->execute(); my $lastupdate = $ah->fetchrow_array; $ACCESS =~ s/\b\w+\W+\w+//; print "

System Stats

Last 5 User Updates:

$sCounter) $sUPDATE_ID updated \"$sEQUIP_DESC\" on $sUPDATE_DATE

Number of inventory items: $rownumbers

Inventory last updated on: $lastupdate

Users allowed to use ESF: $ACCESS

Top "; } else { #Completed Page #Do an action, but first get the values from the form sub read_input { local ($buffer, @pairs, $pair, $name, $value, %FORM); # Read in text $ENV{'REQUEST_METHOD'} =~ tr/a-z/A-Z/; if ($ENV{'REQUEST_METHOD'} eq "POST") { read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'}); } else { $buffer = $ENV{'QUERY_STRING'}; } # Split information into name/value pairs @pairs = split(/&/, $buffer); foreach $pair (@pairs) { ($name, $value) = split(/=/, $pair); $value =~ tr/+/ /; $value =~ s/%(..)/pack("C", hex($1))/eg; $FORM{$name} = $value; } %FORM; } my %incoming = &read_input; # Read information into associated # array %incoming. my $qty = $incoming{'qty'}; # Fetch the text from the array. my $type = $incoming{'type'}; # Fetch the text from the array. my $equip = $incoming{'equip'}; # Fetch the text from the array. my $dept = $incoming{'dept'}; # Fetch the text from the array. my $fstore = $incoming{'fstore'}; # Fetch the text from the array. my $dreceived = $incoming{'dreceived'}; # Fetch the text from the array. my $rstore = $incoming{'rstore'}; # Fetch the text from the array. my $ringstore = $incoming{'ringstore'}; # Fetch the text from the array. my $dated = $incoming{'dated'}; # Fetch the text from the array. my $comment = $incoming{'comment'}; # Fetch the text from the array. my $id = $incoming{'id'}; # Fetch the text from the array. my $action = $incoming{'action'}; # Fetch the text from the array. #House Keeping #If the user left mm/dd/yyyy in the boxes for dated and dreceived we need to clear the values. if ($dreceived eq 'mm/dd/yyyy') { $dreceived = ''; } if ($dated eq 'mm/dd/yyyy') { $dated = ''; } #--------connect to the database my $dbh = DBI->connect( 'dbi:Oracle:Itoldyouitwasasecert.Universe', 'MysteryMan', 'abc123', { RaiseError => 1 } ) || die "Database connection not made: $DBI::errstr"; #VALIDATION! my $passfail = 1; #0 = FAIL 1= PASS! if ($rstore =~ /^[0-9]*\z/ || $rstore eq '') { #They passed - Do nothing } else { $passfail = 0; #User failed, RSTORE doesnt have a number! $errmsg = 'The Reserved Store box is for 3 digit numbers only!'; $errtitle = 'Invaild Value'; $seconds = 7; } if ($ringstore =~ /^[0-9]*\z/ || $ringstore eq '') { #They passed - Do nothing } else { $passfail = 0; #User failed, RSTORE doesnt have a number! $errmsg = 'The Reserved Store box is for 3 digit numbers only!'; $errtitle = 'Invaild Value'; $seconds = 7; } if ($equip eq '') { $passfail = 0; #User failed, RSTORE doesnt have a number! $errmsg = 'You must give that item an equipment name in order for it to be processed.'; $errtitle = 'Validation Error'; $seconds = 7; } #Even though oracle will check these dates for us later, lets just make sure at least #they have the right format before moving to the next item. if ($dated =~ /^(?:[01]\d\/[0-3]\d\/(?:19|20)\d\d)?$/) { #They passed - Do nothing } else { #User entered an invaild date. $passfail = 0; $errmsg = 'Unable to use date format. Please use "MM/DD/YYYY" or MM-DD-YYYY for the Date Delivered Field'; $errtitle = 'Validation Error'; $seconds = 7; } #Even though oracle will check these dates for us later, lets just make sure at least #they have the right format before moving to the next item if ($dreceived =~ /^(?:[01]\d\/[0-3]\d\/(?:19|20)\d\d)?$/) { #They passed - Do nothing } else { #User entered an invaild date. $passfail = 0; $errmsg = 'Unable to use date format. Please use "MM/DD/YYYY" or MM-DD-YYYY on the Date Received field'; $errtitle = 'Validation Error'; $seconds = 7; } #Activate Passfail under variable of 1, if the user passed everything attempt t0 #do what they wanted with the database. if ($passfail == 1) { if ($action eq 'Add') { $errmsg = 'That item has been sucessfully added to ESFI.'; $errtitle = 'Action Completed'; my $sl = $dbh->prepare(" select EQUIP_ID FROM secerttable.ENGINEERING_EQUIPMENT where rownum <= 1 order by EQUIP_ID DESC "); $sl->execute(); my $highnum = $sl->fetchrow_array; $highnum++; my $mmid = $highnum; my $in = $dbh->prepare(" INSERT INTO secerttable.ENGINEERING_EQUIPMENT (EQUIP_ID, EQUIP_DESC, EQUIP_QTY, EQUIP_UOM, EQUIP_DEPT, FROM_STORE, FROM_DATE, RESERVED_STORE, RECEIVING_STORE, RECEIVING_DATE, COMMENTS, UPDATE_DATE, UPDATE_ID) VALUES (:1, upper(:2), :3, :4, :5, :6, TO_DATE(:7,'mm/dd/yyyy'), :8, :9, TO_DATE(:10,'mm/dd/yyyy'), :11, SYSDATE, :12) "); eval { $in->execute($mmid, $equip, $qty, $type, $dept, $fstore, $dreceived, $rstore, $ringstore, $dated, $comment, $username); }; } #--EVAL - - EVALUATE THE STATEMENT WHILE ITS DOING ITS THING - That means if it fails oracle will grab the error codes and stuff in $@ #Update the selected record. if ($action eq ' U ') { $errmsg = 'The item you selected has been sucessfully updated.'; $errtitle = 'Action Completed'; #This is currently broken, haha :( my $ud = $dbh->prepare(" UPDATE secerttable.ENGINEERING_EQUIPMENT SET EQUIP_DESC = upper(?), EQUIP_QTY = ?, EQUIP_UOM = ?, EQUIP_DEPT = ?, FROM_STORE = ?, FROM_DATE = TO_DATE(?,'mm-dd-yyyy'), RESERVED_STORE = ?, RECEIVING_STORE = ?, RECEIVING_DATE = TO_DATE(?,'mm-dd-yyyy'), COMMENTS = ?, UPDATE_DATE = SYSDATE, UPDATE_ID = ? WHERE equip_id = '$id'"); eval { $ud->execute($equip, $qty, $type, $dept, $fstore, $dreceived, $rstore, $ringstore, $dated, $comment, $username); }; } #Deleted the selected record. if ($action eq ' X ') { $errmsg = 'The item you selected has been sucessfully deleted.'; $errtitle = 'Action Completed'; my $td = $dbh->prepare(" DELETE FROM secerttable.ENGINEERING_EQUIPMENT WHERE EQUIP_ID = '$id' "); eval { $td->execute(); }; } }else { } #Check for database error if ($@) { $passfail = 0; #There was a database error. $errmsg = "An error has occurred because you might have entered invaild data in a field that requires numbers only or a specific date format(MM/DD/YYYY).

Database Error: $@"; $errtitle = 'Unexpected Error Occurred'; $seconds = 10; } print header(), start_html('ESF Inventory - Administration'); print " ESF Inventory - Administration

$errtitle

$errmsg

"; } exit;