#!/usr/local/bin/perl
# checkout.pl - web cgi routine to handle shopping cart checkout
#
#  Dave Stoddard - 5/29/04
#  dgs@accelix.com
#
#  Copyright
#  ---------
#  Copyright(c) 2004, Accelix LLC.  All Rights Reserved.
#
#  Description
#  -----------
#  This program begins the process of checkout for the shopping cart system.
#  The steps in the checkout process, and their associated modules, are as
#  follows:
#
#  checkout.pl  - collect shipping information for the customer's order
#  billing.pl   - show total and collect billing information
#  confirm.pl   - show total and confirm or cancel the order
#  payment.pl   - process payment and provide an invoice
#
#  This routine creates two types of records -- the orders record and the
#  shipto record.  Updates to the orderd table (which mimicks the cart table)
#  are deferred until the order is complete, valid, and accepted.  This allows
#  the user to make changes in their cart right up to the final moment.
#
#  Note: The orders table is named "orders" instead of "order" because the
#  word order is a reserved word in the database manager.
#
#  RCS Information
#  ---------------
#  $Id$
#  $Log$
#
# package main;
use strict 'vars';
use strict 'subs';
# use warnings;
# use diagnostics;

# load application modules
use cart;				# shopping cart support routines

# ---------------------------
# DECLARATIONS AND PROTOTYPES
# ---------------------------

# local elements
my %form     = ();			# data from form (name/value pairs)
my %state    = ();			# state persistence data hash
my %sess     = ();			# session id hash
my %orders   = ();			# orders table record
my %cart     = ();			# cart table record
my %shipto   = ();			# shipto table record
my %tax      = ();			# tax table hash
my @recs     = ();			# contents of the current shopping cart
my $itemtot  = 0;			# order total
my $discount = 0;			# order discount amount
my $nettotal = 0;			# net total (itemtot - discount)
my $salestax = 0;			# sales tax amount
my $shipcost = 0;			# shipping costs
my $mintotal = 150.00;			# minimum order amount
my $i        = "";			# current cart record in series
my $curr     = "/cgi-bin/checkout.pl";	# the name of this program
my $next     = "/cgi-bin/billing.pl";	# next in the series of CGI programs

# only states in this table have sales tax applied
$tax{PA} = .06;

# function prototypes
sub CheckForOrders ($);			# check for an existing order record
sub CheckForShipto ($);			# check for an existing shipto record
sub EditShiptoData (\%\%);		# edit form data for shipto record
sub CalcDiscount ($);			# calculate discounts
sub CalcSalesTax (\%$);			# calculate sales tax
sub CalcShipping (\%$);			# calculate shipping costs
sub DisplayShipToPage (\%);		# display the shipping info form
sub GetOrdersRecord ($);		# retrieve an orders record
sub InitOrdersRecord ();		# initialize a blank orders record
sub AddOrdersRecord (\%);		# add an orders record to the table
sub ChangeOrdersRecord (\%);		# change an existing orders record
sub GetShiptoRecord ($);		# retrieve a shipto record
sub InitShiptoRecord ();		# initialize a blank shipto record
sub AddShiptoRecord (\%);		# add a shipto record to the table
sub ChangeShiptoRecord (\%);		# change an existing shipto record
sub GetStates ();			# load state code hash
sub GenerateOptions (\%$$);		# create <option> blocks in html

###
###  START PROGRAM
###

# initialize the program
%form   = ReadForm ();			# read the form
%state  = GetState (%form);		# get current state data (cookies)
%sess   = GetSession (%form,%state);

# get the shopping cart contents
@recs = GetCartRecords ($sess{sessid});
if (CheckError()) {
  DisplayError();
  ExitProgram();
}

# we must have items in the shopping cart
unless (@recs) {
  ErrorMsg ("There are no items in your shopping cart to checkout with.");
  DisplayError();
  ExitProgram();
}

# accumulate totals for shopping cart
foreach $i (@recs) {
  # convert the record into a hash
  %cart    = DecodeCartRecord ($i);
  $itemtot = ($cart{price} * $cart{quantity}) + $itemtot;
  $itemtot = sprintf ("%4.2f",$itemtot);
}

# check for minimum order amount
if ($itemtot < $mintotal) {
  ErrorMsg ("Sorry, but we require a minimum order amount of \$$mintotal.");
  ErrorMsg ("The current total of \$$itemtot does not meet the minimum order requirement.");
  DisplayError();
  ExitProgram();
}

# check for existing orders record (returns rec{new} = 1 if none)
%orders = CheckForOrders ($sess{sessid});

# check for existing shipto record (returns rec{new} = 1 if none)
%shipto = CheckForShipto ($sess{sessid});

# use phase to determine what to do
if (defined $form{phase}) {
  if ($form{phase} == 1) {

    # edit the form data and build a shipto record
    %shipto = EditShiptoData (%form,%shipto);
    if (CheckError()) {
      DisplayError();
      ExitProgram();
    }

    # compute discount
    $discount = CalcDiscount ($itemtot);
    $nettotal = $itemtot - $discount;

    # compute sales tax
    $salestax = CalcSalesTax (%shipto,$nettotal);

    # compute shipping
    $shipcost = CalcShipping (%shipto,$nettotal);

    # build the orders record
    $orders{sessid}    = $sess{sessid};
    $orders{itemtot}   = $itemtot;
    $orders{discount}  = $discount;
    $orders{tax}       = $salestax;
    $orders{shipcost}  = $shipcost;
    $orders{total}     = $itemtot - $discount + $salestax + $shipcost;
    $orders{refund}    = 0.00;
    $orders{status}    = "0";

    # establish the orders record in the table
    if ($orders{new}) {
      # handle new orders
      $orders{orderid} = AddOrdersRecord (%orders);
    } else {
      # update exiting order
      ChangeOrdersRecord (%orders);
    }

    # make sure our update was successful
    if (CheckError()) {
      ErrorMsg ("Unable to update order control information.");
      DisplayError();
      ExitProgram();
    }

    # establish the shipto record in the table
    if ($shipto{new}) {
      # handle new shipto data
      $shipto{orderid}  = $orders{orderid};
      $shipto{shiptoid} = AddShiptoRecord (%shipto);
    } else {
      # update exiting shipto data
      ChangeShiptoRecord (%shipto);
    }

    # make sure our update was successful
    if (CheckError()) {
      ErrorMsg ("Unable to update ship to information.");
      DisplayError();
      ExitProgram();
    }

    # redirect to the next program
    Redirect ($next);
  } else {
    DisplayShipToPage(%shipto);
  }
} else {
  DisplayShipToPage(%shipto);
}

ExitProgram ();

### End Main Program ###


###
###  CheckForOrders() uses the current session id to see if we have an existing
###  orders record.  If so, we use it.  If not, we initialize the record and
###  set an element "new" to 1.  A hash of the orders record is returned.
###

sub
CheckForOrders ($) {
  my $sessid   = shift;			# session id
  my %orders   = ();			# orders table record

  # check for existing orders record
  %orders = GetOrdersRecord ($sessid);
  if (CheckError()) {
    DisplayError();
    ExitProgram();
  }

  # if we have no orders record, create a template
  unless (%orders) {
    %orders = InitOrdersRecord();
    $orders{new} = 1;
  } else {
    # orders record exists
    $orders{new} = 0;
  }

  return %orders;
}


###
###  CheckForShipto() uses the current session id to see if we have an existing
###  shipto record.  If so, we use it.  If not, we initialize the record and
###  set an element "new" to 1.  A hash of the shipto record is returned.
###

sub
CheckForShipto ($) {
  my $sessid   = shift;			# session id
  my %shipto   = ();			# shipto table record

  # check for existing shipto record
  %shipto = GetShiptoRecord ($sessid);
  if (CheckError()) {
    DisplayError();
    ExitProgram();
  }

  # if we have no shipto record, create a template
  unless (%shipto) {
    %shipto = InitShiptoRecord();
    $shipto{new} = 1;
  } else {
    # shipto record exists
    $shipto{new} = 0;
  }

  return %shipto;
}


###
###  The EditShiptoData() function accepts the current form and an existing
###  shipto record hash (if any) and creates an edited shipto record for output.
###  If errors are detected in the data, they are written to the application
###  error table for subsequent reporting.
###

sub
EditShiptoData (\%\%) {
  my $fref	= shift;		# reference to form hash
  my $rref	= shift;		# reference to shipto record hash
  my %form	= %$fref;		# form hash
  my %shipto	= %$rref;		# shipto hash

  # first name
  if (defined $form{fname}) {
    if ($form{fname} =~ /\w+/) {
      $shipto{fname} = $form{fname};
    } else {
      ErrorMsg ("First name is a required field.");
    }
  } else {
    ErrorMsg ("First name is a required field.");
  }

  # middle name
  if (defined $form{mname}) {
    $shipto{mname} = $form{mname};
  }

  # last name
  if (defined $form{lname}) {
    if ($form{lname} =~ /\w+/) {
      $shipto{lname} = $form{lname};
    } else {
      ErrorMsg ("Last name is a required field.");
    }
  } else {
    ErrorMsg ("Last name is a required field.");
  }

  # company name
  if (defined $form{company}) {
    $shipto{company} = $form{company};
  }

  # address line 1
  if (defined $form{address1}) {
    if ($form{address1} =~ /\w+/) {
      $shipto{address1} = $form{address1};
    } else {
      ErrorMsg ("Address line 1 is a required field.");
    }
  } else {
    ErrorMsg ("Address line 1 is a required field.");
  }

  # address line 2
  if (defined $form{address2}) {
    $shipto{address2} = $form{address2};
  }

  # city name
  if (defined $form{city}) {
    if ($form{city} =~ /\w+/) {
      $shipto{city} = $form{city};
    } else {
      ErrorMsg ("City is a required field.");
    }
  } else {
    ErrorMsg ("City is a required field.");
  }

  # state abbreviation
  if (defined $form{state}) {
    if ($form{state} =~ /\w+/) {
      $shipto{state} = $form{state};
    } else {
      ErrorMsg ("State is a required field.");
    }
  } else {
    ErrorMsg ("State is a required field.");
  }

  # zipcode
  if (defined $form{zipcode}) {
    if ($form{zipcode} =~ /^\d\d\d\d\d$/) {
      $shipto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d-\d\d\d\d$/) {
      $shipto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d \d\d\d\d$/) {
      $shipto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d\d\d\d\d$/) {
      $shipto{zipcode} = $form{zipcode};
    } else {
      ErrorMsg ("The zip code is either missing or has an invalid format.");
    }
  } else {
    ErrorMsg ("Zip code is a required field.");
  }

  # country
  if (defined $form{country}) {
    if ($form{country} =~ /^USA$/) {
      $shipto{country} = $form{country};
    } else {
      ErrorMsg ("Country is a required field and must be USA.");
    }
  } else {
    ErrorMsg ("Country is a required field and must be USA.");
  }

  # email address 1
  if (defined $form{email1}) {
    if ($form{email1} =~ /\w+/) {
      $shipto{email1} = $form{email1};
    } else {
      ErrorMsg ("The first email address field is required.");
    }
  } else {
    ErrorMsg ("The first email address field is required.");
  }

  # email address 2
  if (defined $form{email2}) {
    $shipto{email2} = $form{email2};
  }

  # day time phone number
  if (defined $form{phoneday}) {
    if ($form{phoneday} =~ /\d\d\d\d/) {
      $shipto{phoneday} = $form{phoneday};
    } else {
      ErrorMsg ("A valid daytime phone number is required.");
    }
  } else {
    ErrorMsg ("A valid daytime phone number is required.");
  }

  # evening phone number
  if (defined $form{phoneeve}) {
    $shipto{phoneeve} = $form{phoneeve};
  }

  # shipping notes
  if (defined $form{shipnotes}) {
    $shipto{shipnotes} = $form{shipnotes};
  }

  return %shipto;
}


###
###  CalcDiscount() is used to calculate discounts.  In its current form,
###  this routine simply returns zero.
###

sub
CalcDiscount ($) {
  my $itemtot	= shift;
  my $discount	= "0.00";
  return $discount;
}


###
###  CalcSalesTax() is used to calculate sales tax based on a hash named %tax.
###  If no entry exists in tha has, no sales tax is computed.
###

sub
CalcSalesTax (\%$) {
  my $rref	= shift;		# reference to shipto record hash
  my $nettotal	= shift;		# net item total
  my %shipto	= %$rref;		# shipto record hash
  my $salestax	= 0;			# sales tax

  if (defined $tax{$shipto{state}}) {
    $salestax = $tax{$shipto{state}} * $nettotal;
  } else {
    $salestax = "0.00";
  }
  return $salestax;
}


###
###  CalcShipping() is used to compute sales tax.  This implementation is for
###  HAZMAT shipments and is based on the order total, but it can be rewritten
###  to handle much more sophisticated problems.
###

sub
CalcShipping (\%$) {
  my $rref	= shift;		# reference to shipto record hash
  my $nettotal	= shift;		# net item total
  my %shipto	= %$rref;		# shipto record hash
  my $shipcost	= 0;			# shipping cost
  my $pct	= 0;			# calculation percentage

  if ($nettotal < 150.01) {
    $pct = .85;
  } elsif ($nettotal < 200.01) {
    $pct = .50;
  } elsif ($nettotal < 400.01) {
    $pct = .18;
  } elsif ($nettotal < 700.01) {
    $pct = .18;
  } elsif ($nettotal < 1200.01) {
    $pct = .12;
  } elsif ($nettotal < 2000.01) {
    $pct = .09;
  } elsif ($nettotal < 3500.01) {
    $pct = .08;
  } elsif ($nettotal < 7000.01) {
    $pct = .05;
  } elsif ($nettotal < 12000.01) {
    $pct = .04;
  } else {
    $pct = .03;
  }

  $shipcost = $pct * $nettotal;
  if ($shipcost < 85) {
    $shipcost = "85.00";
  }

  return $shipcost;
}


###
###  DisplayShipToPage() displays the "ship to" page to the customer.  If any
###  existing data for the customer exists, it is displayed on the form for the
###  user to modify.
###

sub
DisplayShipToPage (\%) {
  my $rref	= shift;		# reference to shipto hash
  my %shipto	= %$rref;		# shipto hash
  my %sthash	= ();			# state code hash

  # load description hashes
  %sthash = GetStates ();

  # output the top part of the page
  SetPageSize (560,560);
  SetPageName ("_xcheckout");
  OutputPageTop("Shipping Information");
 
  # put a heading on the page
  print <<EOF;
<form action="$curr" method="post">
<input type="hidden" name="orderid" value="$shipto{orderid}">
<input type="hidden" name="memid" value="$shipto{memid}">
<input type="hidden" name="phase" value="1">
<table valign="top" width="500" cellpadding="0" cellspacing="0">
<tr>
  <td width="50%" align="left">
  <img src="/cart/opencart-165x39.gif" border="0"><br>
  </td>
  <td width="50%" align="right" valign="baseline" class="head">
  Shipping Info<br>
  </td>
</tr>
</table>
 
<table valign="top" width="500" cellpadding="0" cellspacing="0">
<tr>
  <td align="left" class="ltext">
  <hr size="2" noshade>
  &nbsp;<br>
  Please enter your shipping information in the form below.  Shipments to
  businesses will be delivered directly to the business address.  Shipments
  to residences will be delivered to the nearest shipping terminal.  Note that
  fields marked with an asterisk are required fields.<br>
  &nbsp;<br>
  </td>
</tr>
</table>

<table valign="top" width="500" cellpadding="0" cellspacing="0">
<tr>
  <td align="left" width="22%" class="fttext">
  First Name:&nbsp;*
  </td>
  <td align="left" width="78%" class="ftdata">
  <input type="text" name="fname" value="$shipto{fname}" size="20">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Middle Initial:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="mname" value="$shipto{mname}" size="2">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Last Name:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="lname" value="$shipto{lname}" size="20">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Company:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="company" value="$shipto{company}" size="40"><br>
  If this shipment is to a business address, please enter the name of the
  business in this field, otherwise we will assume the shipment is being
  delivered to a residence via the nearest trucking terminal.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Address 1:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="address1" value="$shipto{address1}" size="40"><br>
  This must be a valid street address.  We can not deliver fireworks
  to P.O. Boxes.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Address 2:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="address2" value="$shipto{address2}" size="40">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  City:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="city" value="$shipto{city}" size="20">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  State:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <select name="state" size="1" class="text">
EOF
 
  # generate state code select menu
  GenerateOptions (%sthash,"",$shipto{state});
 
  print <<EOF;
  </select><br>
  If your state is not in this list, then we can not ship fireworks
  to your state.  Instead, enter the address of an Overnite Transportation
  truck terminal in a nearby state to have your fireworks delivered.  You
  can find a list of trucking terminals on the <a href="http://www.overnite.com"
  target="_overnite" class="link">Overnite Transportation</a> web site.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Zip Code:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="zipcode" value="$shipto{zipcode}" size="12"
  maxlength="10"><br>
  Zip code format is xxxxx or xxxxx-xxxx (zip+4).
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Country:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="hidden" name="country" value="$shipto{country}" size="20">
  USA<br>
  Sorry, but we can not ship fireworks to addresses outside the United States.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Email 1:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="email1" value="$shipto{email1}" size="20"><br>
  We need your email address to send you your order invoice and to send you
  notice of your shipment with tracking number.  We do not share your email
  address with any third party.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Email 2:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="email2" value="$shipto{email2}" size="20"><br>
  If you enter a second email address here, a copy of all email correspondence
  for this order will be sent here in addition to your primary email address.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Daytime Phone:&nbsp;*
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="phoneday" value="$shipto{phoneday}" size="12"><br>
  A working daytime phone number is required in order for the shipping company
  to call you to arrange delivery of your order.
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Evening Phone:
  </td>
  <td align="left" width="78%" class="fdata">
  <input type="text" name="phoneeve" value="$shipto{phoneeve}" size="12">
  </td>
</tr>

<tr>
  <td align="left" width="22%" class="ftext">
  Special Instructions:
  </td>
  <td align="left" width="78%" class="fdata">
  <textarea name="shipnotes" cols="40" rows="6" wrap="virtual" class="text">$shipto{shipnotes}</textarea><br>
  If you have any instructions that will aid in the delivery of your shipment,
  please enter that information in this field.
  </td>
</tr>
</table>
  
<table valign="top" width="500" cellpadding="0" cellspacing="0">
<tr>
  <td align="center" class="text">
  <hr size="2" noshade>
  &nbsp;<br>
  <input type="image" src="/cart/continue-100x23.gif" border="0"
  width="100" height="23"><br>
  &nbsp;<br>
  </td>
</tr>
</table>

EOF

  OutputPageBottom();
  return 1; 
}

 
###
###  GetOrdersRecord() retrieves the order record from the database.  It
###  returns a hash of the fields returned, or undef if an error occurs.
###  Error messages are returned through the application error table.  This
###  function requires the session id as a parameter.
###
 
sub
GetOrdersRecord ($) {
  my $sessid   = shift;                 # session id of shopping cart
  my %out      = ();                    # result output hash
  my %db1      = ();                    # database connection hash
  my $ref      = "";                    # hash reference
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return %out;
  }

  # obtain the connection info
  %db1 = GetDatabaseInfo();
 
  # prepare the select statement
  $db1{sth} = $db1{dbh}->prepare (qq{
    SELECT *
    FROM orders
    WHERE
      sessid = "$sessid"
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Unable to prepare select: $DBI::err : $DBI::errstr");
    return %out;
  }
 
  # execute the statement
  $db1{sth}->execute ();
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Unable to execute select: $DBI::err : $DBI::errstr");
    return %out;
  }
 
  # retrieve a pointer to the record hash
  $ref = $db1{sth}->fetchrow_hashref;
 
  # check for record not found
  if (!defined $ref) {
    return %out;
  }
 
  %out = %{$ref};
  $db1{sth}->finish ();
 
  return %out;
}

 
###
###  InitOrdersRecord() initializes an orders record before it is loaded
###  with real values.
###
 
sub
InitOrdersRecord () {
  my %rec = ();                         # record hash (returned)
 
  $rec{orderid}    = 0;
  $rec{sessid}     = $sess{sessid};
  $rec{memid}      = 0;
  $rec{itemtot}    = 0.00;
  $rec{discount}   = 0.00;
  $rec{tax}        = 0.00;
  $rec{shipcost}   = 0.00;
  $rec{total}      = 0.00;
  $rec{refund}     = 0.00;
  $rec{status}     = 0;
  $rec{updatedate} = "";
  $rec{createdate} = "";
 
  return %rec;
}
 
 
###
###  AddOrdersRecord() will add a new record to the database.  If an error is
###  detected during the request, it is returned in the error table.  This
###  routine returns the orderid of the inserted record, or 0 if an error
###  occurred.
###
 
sub
AddOrdersRecord (\%) {
  my ($arg1)   = @_;                    # reference to hash
  my %fields   = %{$arg1};              # field hash
  my %db1      = ();                    # database connection hash
  my $rows     = 0;                     # row counter
  my $rc       = 1;                     # return value
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return 0;
  }
 
  # obtain the connection info
  %db1 = GetDatabaseInfo();
 
  # preformat the data for entry into the database
  foreach (keys %fields) {
    unless (defined $fields{$_}) {
      $fields{$_} = "";
    }
    $fields{$_} = $db1{dbh}->quote($fields{$_});
  }
 
  # execute the insert
  $rows = $db1{dbh}->do (qq{
    INSERT
      INTO orders
    SET
      orderid    = NULL,
      sessid     = $fields{sessid},
      memid      = $fields{memid},
      itemtot    = $fields{itemtot},
      discount   = $fields{discount},
      tax        = $fields{tax},
      shipcost   = $fields{shipcost},
      total      = $fields{total},
      refund     = $fields{refund},
      status     = $fields{status},
      updatedate = NOW(),
      createdate = NOW()
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("error: unable to perform insert: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  # prepare a statement to retrieve the last sequence r
  $db1{sth} = $db1{dbh}->prepare (qq{
    SELECT LAST_INSERT_ID()
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Prepare for select on orders last insert id failed: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  # execute the statement
  $db1{sth}->execute ();
  $rc = ($db1{sth}->fetchrow_array())[0];
  $db1{sth}->finish ();
 
  unless ($rc) {
    ErrorMsg ("Unable to retrieve last insert id from orders table.");
    return 0;
  }
 
  return $rc;
}
 
 
###
###  ChangeOrdersRecord() will modify the contents of an existing orders
###  record.  It returns 1 if the update was successful, or 0 if it failed.
###  If an error does occur, the error message can be found in the application
###  error table.
###
 
sub
ChangeOrdersRecord (\%) {
  my ($arg1)   = @_;                    # reference to hash
  my %fields   = %{$arg1};              # field hash
  my %db1      = ();			# database connection hash
  my $rows     = 0;			# row counter
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return 0;
  }
 
  # obtain the connection info
  %db1 = GetDatabaseInfo();

  # preformat the data for entry into the database
  foreach (keys %fields) {
    unless (defined $fields{$_}) {
      $fields{$_} = "";
    }
    $fields{$_} = $db1{dbh}->quote($fields{$_});
  }
 
  # execute the update (orderid, sessid, and memid are not affected)
  $rows = $db1{dbh}->do (qq{
    UPDATE
      orders
    SET
      itemtot    = $fields{itemtot},
      discount   = $fields{discount},
      tax        = $fields{tax},
      shipcost   = $fields{shipcost},
      total      = $fields{total},
      refund     = $fields{refund},
      status     = $fields{status},
      updatedate = NOW()
    WHERE
      sessid     = $fields{sessid}
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("error: unable to perform update: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  return 1;
}
 
 
###
###  GetShiptoRecord() retrieves the shipto record from the database.  It
###  returns a hash of the fields returned, or undef if an error occurs.
###  Error messages are returned through the application error table.  This
###  function requires the session id as a parameter.
###
 
sub
GetShiptoRecord ($) {
  my $sessid   = shift;                 # session id of shopping cart
  my %out      = ();                    # result output hash
  my %db1      = ();                    # database connection hash
  my $ref      = "";                    # hash reference
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return %out;
  }

  # obtain the connection info
  %db1 = GetDatabaseInfo();
 
  # prepare the select statement
  $db1{sth} = $db1{dbh}->prepare (qq{
    SELECT *
    FROM shipto
    WHERE
      sessid = "$sessid"
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Unable to prepare select: $DBI::err : $DBI::errstr");
    return %out;
  }
 
  # execute the statement
  $db1{sth}->execute ();
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Unable to execute select: $DBI::err : $DBI::errstr");
    return %out;
  }
 
  # retrieve a pointer to the record hash
  $ref = $db1{sth}->fetchrow_hashref;
 
  # check for record not found
  if (!defined $ref) {
    return %out;
  }
 
  %out = %{$ref};
  $db1{sth}->finish ();
 
  return %out;
}

 
###
###  InitShiptoRecord() initializes an shipto record before it is loaded
###  with real values.
###
 
sub
InitShiptoRecord () {
  my %rec = ();                         # record hash (returned)

  $rec{shiptoid}   = 0;
  $rec{orderid}    = 0;
  $rec{sessid}     = $sess{sessid};
  $rec{memid}      = 0;
  $rec{fname}      = "";
  $rec{mname}      = "";
  $rec{lname}      = "";
  $rec{company}    = "";
  $rec{address1}   = "";
  $rec{address2}   = "";
  $rec{city}       = "";
  $rec{state}      = "";
  $rec{zipcode}    = "";
  $rec{country}    = "USA";
  $rec{email1}     = "";
  $rec{email2}     = "";
  $rec{phoneday}   = "";
  $rec{phoneeve}   = "";
  $rec{shipnotes}  = "";
  $rec{status}     = "";
  $rec{dateship}   = 0;
  $rec{method}     = "";
  $rec{trackno}    = "";
  $rec{updatedate} = "";
  $rec{createdate} = "";
 
  return %rec;
}
 
 
###
###  AddShiptoRecord() will add a new record to the database.  If an error is
###  detected during the request, it is returned in the error table.  This
###  routine returns the orderid of the inserted record, or 0 if an error
###  occurred.
###
 
sub
AddShiptoRecord (\%) {
  my ($arg1)   = @_;                    # reference to hash
  my %fields   = %{$arg1};              # field hash
  my %db1      = ();                    # database connection hash
  my $rows     = 0;                     # row counter
  my $rc       = 1;                     # return value
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return 0;
  }
 
  # obtain the connection info
  %db1 = GetDatabaseInfo();
 
  # preformat the data for entry into the database
  foreach (keys %fields) {
    unless (defined $fields{$_}) {
      $fields{$_} = "";
    }
    $fields{$_} = $db1{dbh}->quote($fields{$_});
  }
 
  # execute the insert
  $rows = $db1{dbh}->do (qq{
    INSERT
      INTO shipto
    SET
      shiptoid   = NULL,
      orderid    = $fields{orderid},
      sessid     = $fields{sessid},
      memid      = $fields{memid},
      fname      = $fields{fname},
      mname      = $fields{mname},
      lname      = $fields{lname},
      company    = $fields{company},
      address1   = $fields{address1},
      address2   = $fields{address2},
      city       = $fields{city},
      state      = $fields{state},
      zipcode    = $fields{zipcode},
      country    = $fields{country},
      email1     = $fields{email1},
      email2     = $fields{email2},
      phoneday   = $fields{phoneday},
      phoneeve   = $fields{phoneeve},
      shipnotes  = $fields{shipnotes},
      status     = $fields{status},
      dateship   = $fields{dateship},
      method     = $fields{method},
      trackno    = $fields{trackno},
      updatedate = NOW(),
      createdate = NOW()
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("error: unable to perform insert: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  # prepare a statement to retrieve the last sequence r
  $db1{sth} = $db1{dbh}->prepare (qq{
    SELECT LAST_INSERT_ID()
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("Prepare for select on shipto last insert id failed: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  # execute the statement
  $db1{sth}->execute ();
  $rc = ($db1{sth}->fetchrow_array())[0];
  $db1{sth}->finish ();
 
  unless ($rc) {
    ErrorMsg ("Unable to retrieve last insert id from shipto table.");
    return 0;
  }
 
  return $rc;
}
 
 
###
###  ChangeShiptoRecord() will modify the contents of an existing shipto
###  record.  It returns 1 if the update was successful, or 0 if it failed.
###  If an error does occur, the error message can be found in the application
###  error table.
###
 
sub
ChangeShiptoRecord (\%) {
  my ($arg1)   = @_;                    # reference to hash
  my %fields   = %{$arg1};              # field hash
  my %db1      = ();			# database connection hash
  my $rows     = 0;			# row counter
 
  # make sure we are connected to the database
  unless (ConnectDatabase()) {
    return 0;
  }
 
  # obtain the connection info
  %db1 = GetDatabaseInfo();

  # preformat the data for entry into the database
  foreach (keys %fields) {
    unless (defined $fields{$_}) {
      $fields{$_} = "";
    }
    $fields{$_} = $db1{dbh}->quote($fields{$_});
  }
 
  # execute the update (orderid, sessid, and memid are not affected)
  $rows = $db1{dbh}->do (qq{
    UPDATE
      shipto
    SET
      fname      = $fields{fname}, 
      mname      = $fields{mname},
      lname      = $fields{lname},
      company    = $fields{company},
      address1   = $fields{address1},
      address2   = $fields{address2},
      city       = $fields{city},
      state      = $fields{state},
      zipcode    = $fields{zipcode},
      country    = $fields{country},
      email1     = $fields{email1},
      email2     = $fields{email2},
      phoneday   = $fields{phoneday},
      phoneeve   = $fields{phoneeve},
      shipnotes  = $fields{shipnotes},
      status     = $fields{status},
      dateship   = $fields{dateship},
      method     = $fields{method},
      trackno    = $fields{trackno},
      updatedate = NOW()
    WHERE
      sessid     = $fields{sessid}
  });
 
  # check for error
  if ($DBI::err) {
    ErrorMsg ("error: unable to perform update: $DBI::err : $DBI::errstr");
    return 0;
  }
 
  return 1;
}

 
###
###  GetStates() will return a hash of valid state names keyed by their
###  state abbreviation.
###
 
sub
GetStates () {
  my %shash = ();
 
  $shash{""} = "Make A Selection";
  # $shash{"XX"} = "Outside United States and Canada";
  $shash{"AL"} = "Alabama";
  # $shash{"AK"} = "Alaska";
  $shash{"AZ"} = "Arizona";
  $shash{"AR"} = "Arkansas";
  $shash{"CA"} = "California";
  $shash{"CO"} = "Colorado";
  # $shash{"CT"} = "Connecticut";
  $shash{"DE"} = "Delaware";
  $shash{"DC"} = "District Of Columbia";
  $shash{"FL"} = "Florida";
  $shash{"GA"} = "Georgia";
  # $shash{"HI"} = "Hawaii";
  $shash{"ID"} = "Idaho";
  $shash{"IL"} = "Illinois";
  $shash{"IN"} = "Indiana";
  $shash{"IA"} = "Iowa";
  $shash{"KS"} = "Kansas";
  $shash{"KY"} = "Kentucky";
  $shash{"LA"} = "Louisiana";
  $shash{"ME"} = "Maine";
  $shash{"MD"} = "Maryland";
  # $shash{"MA"} = "Massachusetts";
  $shash{"MI"} = "Michigan";
  $shash{"MN"} = "Minnesota";
  $shash{"MS"} = "Mississippi";
  $shash{"MO"} = "Missouri";
  $shash{"MT"} = "Montana";
  $shash{"NE"} = "Nebraska";
  $shash{"NV"} = "Nevada";
  $shash{"NH"} = "New Hampshire";
  # $shash{"NJ"} = "New Jersey";
  $shash{"NM"} = "New Mexico";
  # $shash{"NY"} = "New York";
  $shash{"NC"} = "North Carolina";
  $shash{"ND"} = "North Dakota";
  $shash{"OH"} = "Ohio";
  $shash{"OK"} = "Oklahoma";
  $shash{"OR"} = "Oregon";
  $shash{"PA"} = "Pennsylvania";
  $shash{"RI"} = "Rhode Island";
  $shash{"SC"} = "South Carolina";
  $shash{"SD"} = "South Dakota";
  $shash{"TN"} = "Tennessee";
  $shash{"TX"} = "Texas";
  $shash{"UT"} = "Utah";
  $shash{"VT"} = "Vermont";
  $shash{"VA"} = "Virginia";
  $shash{"WA"} = "Washington";
  $shash{"WV"} = "West Virginia";
  $shash{"WI"} = "Wisconsin";
  $shash{"WY"} = "Wyoming";
  # $shash{"AB"} = "Alberta";
  # $shash{"AS"} = "American Samoa";
  # $shash{"AA"} = "Armed Forces Americas";
  # $shash{"AE"} = "Armed Forces Europe";
  # $shash{"AP"} = "Armed Forces Pacific";
  # $shash{"BC"} = "British Columbia";
  # $shash{"GU"} = "Guam";
  # $shash{"MB"} = "Manitoba";
  # $shash{"NB"} = "New Brunswick";
  # $shash{"NF"} = "Newfoundland";
  # $shash{"MP"} = "Northern Mariana Is";
  # $shash{"NT"} = "Northwest Territories";
  # $shash{"NS"} = "Nova Scotia";
  # $shash{"ON"} = "Ontario";
  # $shash{"PW"} = "Palau";
  # $shash{"PE"} = "Prince Edward Island";
  # $shash{"PQ"} = "Province du Quebec";
  # $shash{"PR"} = "Puerto Rico";
  # $shash{"SK"} = "Saskatchewan";
  # $shash{"VI"} = "Virgin Islands";
  # $shash{"YT"} = "Yukon Territory";
  return (%shash);
}

 
###
###  GenerateOptions() will create option statements based on a hash
###  that is passed to the routine.  The routine uses three parameters:
###
###    hash    : contains codes and descriptions for the select
###    default : default value (if none specified)
###    select  : value of selected item (if blank, uses default)
###
###  In order to use this routine you must provide the <select> tag in
###  the HTML before calling the routine (this allows you to have control
###  over the variable name, the size of the select box, and its attributes
###  like multiple select).  After calling the routine, you must close
###  the html with a </select> tag.
###
 
sub
GenerateOptions (\%$$) {
  my ($arg1,$default,$select) = @_;
  my %hash = %{$arg1};                          # code/description hash
  my $sel  = "";                                # selected verb
  my $i    = "";                                # iteration item
 
  # initialize the selected item
  $select = $default unless $hash{$select};
 
  # handle field selection
  foreach $i (sort keys %hash) {
    if ($i eq $select) {
      $sel = "selected";
    } else {
      $sel = "";
    }
 
    print "      <option value=\"$i\" $sel>$hash{$i}</option>\n";
  }
 
  return 1;
}