#!/usr/local/bin/perl

use DBI;
use CGI;
use strict;
use HTML::Template;

my $CGI = CGI->new();

my $dbh = dbh(); # connect to db
$dbh->do("SET search_path to northwind") or die;

# grab the stuff from the database
my $sql = qq!SELECT
                 a."OrderID", b."CompanyName" AS "CustomerName", 
                 c."FirstName"::text || ' ' ||c."LastName"::text AS "EmployeeName",
                 a."OrderDate", a."RequiredDate", a."ShippedDate", d."CompanyName" AS "ShipVia",
                 a."Freight", a."ShipName", a."ShipAddress", a."ShipCity", a."ShipRegion",
                 a."ShipPostalCode", a."ShipCountry" 
             FROM
                 "Orders" a, "Customers" b, "Employees" c, "Shippers" d
             WHERE
                 a."CustomerID" = b."CustomerID" AND
                 a."EmployeeID" = c."EmployeeID" AND
                 a."ShipVia"    = d."ShipperID"
             ORDER BY 1 !;
my $sth = $dbh->prepare($sql);
$sth->execute();

# prepare a data structure for HTML::Template
my $rows;
push @{$rows}, $_ while $_ = $sth->fetchrow_hashref();

# instantiate the template and substitute the values
my $template = HTML::Template->new(filename => 'Orders.tmpl');
$template->param(ROWS => $rows);

print $CGI->header();

print $template->output();


$dbh->disconnect();

# connect to database
sub dbh {
  my $dsn = 'DBI:Pg:dbname=northwind;host=localhost';
  my $user = 'postgres';
  my $pwd  = 'postgres';
  my $dbh = DBI -> connect($dsn,$user,$pwd,{'RaiseError' => 1});
  return $dbh;
}