#!/usr/local/bin/perl
use CGI;
use DBI;
# Make up a pulldown menu of all the employees for reports to field
$db_handle = DBI -> connect("DBI:Pg:dbname=northwind;host=localhost", "postgres", "postgres", {'RaiseError' => 1});
$query = "select \"EmployeeID\" AS repto, \"FirstName\"::text || ' ' ||\"LastName\"::text as name from \"Employees\"";
$db_handle->do("SET search_path to northwind") or die;
$qh = $db_handle->prepare($query);
$qh->execute;
while (@row = $qh->fetchrow) {
$hh .= "\n";
}
$qry = "select \"EmployeeID\" AS empid, \"FirstName\"::text || ' ' ||\"LastName\"::text as name from \"Employees\"";
$qq = $db_handle->prepare($qry);
$qq->execute;
while (@row = $qq->fetchrow) {
$tt .= "\n";
}
# Send out the header and form
print "content-type: text/html\n\n";
print <<"HEADER";
Updte en employee record
Please update an employee record
HEADER
# Read form inputs
read(STDIN,$buffer,$ENV{CONTENT_LENGTH});
@pairs = split(/&/,$buffer);
foreach (@pairs) {
($n,$v) = split(/=/);
$v =~ tr/+/ / ;
$v =~ s/%(..)/pack("C",hex($1))/ge;
$input{$n} = $v;
}
# If the form has been completed, save data entered
if ($input{"go"} eq "FETCH") {
$query = "SELECT
\"EmployeeID\" AS ei,
\"LastName\" AS ln,
\"FirstName\" AS fn,
\"Title\" AS tl,
\"TitleOfCourtesy\" AS tc,
\"BirthDate\" AS bd,
\"HireDate\" AS hd,
\"Address\" As ad,
\"City\" AS ct,
\"Region\" AS rg,
\"PostalCode\" AS pc,
\"Country\" AS cy,
\"HomePhone\" AS hp,
\"Extension\" AS xt,
\"Notes\" AS nt,
\"ReportsTo\" AS rt
FROM
\"Employees\"
WHERE
\"EmployeeID\"="." \'$input{empid}\'";
$ins = $db_handle->prepare($query);
$ins->execute;
while (@row = $ins->fetchrow) {
$ei .= "\n";
$ln .= "\n";
$fn .= "\n";
$tl .= "\n";
$tc .= "\n";
$bd .= "\n";
$hd .= "\n";
$ad .= "\n";
$ct .= "\n";
$rg .= "\n";
$pc .= "\n";
$cy .= "\n";
$hp .= "\n";
$xt .= "\n";
$nt .= "\n";
$rt .= "\n";
}
#$db_handle -> do($ins);
$action = "Record saved - $ins";
# If no form entered, ask user to complete one
} else {
$action = "Please complete form";
}
# If the form has been completed, save data entered
if ($input{"go"} eq "UPDATE") {
$query = "UPDATE
\"Employees\"
SET
\"LastName\" = "." \'$input{lastname}\',
\"FirstName\" = "." \'$input{firstname}\',
\"Title\" = "." \'$input{title}\',
\"TitleOfCourtesy\" = "." \'$input{toc}\',
\"BirthDate\" = "." \'$input{dob}\',
\"HireDate\" = "." \'$input{doh}\',
\"Address\" = "." \'$input{address}\',
\"City\" = "." \'$input{city}\',
\"Region\" = "." \'$input{region}\',
\"PostalCode\" = "." \'$input{pcode}\',
\"Country\" = "." \'$input{country}\',
\"HomePhone\" = "." \'$input{homephone}\',
\"Extension\" = "." \'$input{ext}\',
\"Notes\" = "." \'$input{notes}\',
\"ReportsTo\" = "." \'$input{repto}\'
WHERE
\"EmployeeID\"="." \'$input{employeeid}\'";
$db_handle -> do($query);
$action = "Record saved - $query";
# If no form entered, ask user to complete one
} else {
$action = "Please complete form";
}
# Links to other pages in the application
print <<"FOOTER";
$action
Jump to - View Employees Listing
Jump to - Delete an Employee
Jump to - Add or update Employee Photo
Edited by Terry on July, 06 2014.
FOOTER