#!/usr/bin/perl -w

# Program Name:  emp-list.cgi

# Primary Programmer:  Spenser

# Description of Program:
# This is a Perl (vs. 5) script which lists
# employees in a web format with hyper-links
# to another script/web page containing details.

# Set Perl Modules & Initial Variables
use strict;
use CGI qw/:standard/;
use DBI;

my $q = new CGI;
my $sort = param("sort") || "emp_last";
my $style = "<link rel='stylesheet' href='/styles/hr.css' type='text/css' />";

# Extract list of employees from mySQL
my $dbh = DBI->connect("DBI:mysql:sys_main:localhost", "user", "password")
          || die "Could not connect to database: " . DBI->errstr;
my $sql_stmnt = "SELECT emp_id, CONCAT(emp_last, ', ', emp_first), dept_name
                 FROM sys_main.humans, sys_main.depts 
                 WHERE status='AV' AND dept_id=dept
                 ORDER BY '$sort'";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();

# Create web page for displaying data to user
print
   $q->header( -type=>'text/html'), "\n\n",

   $q->start_html(-title=>'Employee Table', -bgcolor=>'#FFFFFF', 

                  -link=>'#4682B4', -vlink=>'#5F9EA0',
                  -alink=>'#1E90FF'), "\n\n",
   $style, "\n\n",

   $q->start_table({-width=>'450', -border=>'0',
                    -cellpadding=>'2', -cellspacing=>'0'}), "\n\n",

   $q->start_Tr,
   $q->start_td({-align=>'left', -width=>'100%',
                 -colspan=>'4'}),  "\n",
   "<span class=section-heading>Employee Table</span>",
   $q->hr, "\n\n",
   $q->p("To access an employee\'s records, just click on their name
          in the table below. &nbsp;To re-sort the list, click
          on the columnn heading to sort by"),  "\n",

   $q->hr, $q->end_td, $q->end_Tr, "\n\n",

   $q->start_Tr, $q->start_td({-align=>'left', -width=>'20%'}), "\n",
   $q->a({-href=>"emp-list.cgi?sort=emp_last"},
   "<span class=col-heading>Emp. ID</span>"),  "\n",

   $q->end_td, "\n",

   $q->start_td({-align=>'left', -width=>'45%', -colspan=>'2'}),
   $q->a({-href=>"emp-list.cgi?sort=emp_id"},
   "<span class=col-heading>Employee Name</span>"), "\n",
   $q->end_td, "\n",

   $q->start_td({-align=>'left', -width=>'35%'}),
   $q->a({-href=>"emp-list.cgi?sort=dept"},
   "<span class=col-heading>Department</span>"), "\n",
   $q->end_td, $q->end_Tr, "\n\n",

   $q->start_Tr,
   $q->start_td({-align=>'left', -width=>'100%',
                 -colspan=>'4'}), "\n",
   $q->hr, $q->end_td, $q->end_Tr, "\n\n";

# Loop through employee data and display info.
while (@_ = $sth->fetchrow_array()) {
   $emp_id = $_[0];
   $emp_name = $_[1];
   $dept = $_[2];

   print
     $q->start_Tr,
     $q->start_td({-align=>'left', -width=>'20%'}), "\n",
     $q->a({-href=>"emp-view.cgi?emp_id=$emp_id"}, "$emp_id"), "\n",
     $q->end_td, "\n",

     $q->start_td({-align=>'left', -width=>'45%', -colspan=>'2'}),
     $q->a({-href=>"emp-view.cgi?emp_id=$emp_id"}, "$emp_name"), "\n",
     $q->end_td, "\n",

     $q->start_td({-align=>'left', -width=>'35%'}), "\n",
     "$dept", $q->end_td, $q->end_Tr, "\n\n";  }

$sth->finish();
$dbh->disconnect();

print
   $q->start_Tr,
   $q->start_td({-align=>'right', -width=>'100%', -colspan=>'4'}),
   $q->hr, "\n",
   $q->img({-src=>'/images/poweredbymysql.gif',
            -align=>'right', -border=>'0',
            -alt=>'This page was created by Perl & mySQL'}),
   $q->end_td, $q->end_Tr, "\n\n",

   $q->end_table, "\n",
   $q->end_html;

exit;