#!/usr/local/bin/perl
# billing.pl - web cgi routine to collect shopping cart payment info
#
#  Dave Stoddard - 6/01/04
#  dgs@accelix.com
#
#  Copyright
#  ---------
#  Copyright(c) 2004, Accelix LLC.  All Rights Reserved.
#
#  Description
#  -----------
#  This program handles the process of collecting billing info 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 the billto record.  It assumes that the order master
#  record has already been created and FAILS (with a message) if that record
#  does not exist.
#
#  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
use Time::Local;			# perl time functions

# ---------------------------
# 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 %shipto   = ();			# shipto table record
my %billto   = ();			# billto table record
my %tax      = ();			# tax table hash
my %pohash   = ();			# payment option hash
my %mmhash   = ();			# expiration month hash
my %yyhash   = ();			# expiration year hash
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 $prev     = "/cgi-bin/checkout.pl";	# previous CGI program
my $curr     = "/cgi-bin/billing.pl";	# this CGI program
my $next     = "/cgi-bin/confirm.pl";	# next in the series of CGI programs

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

# payment options (to deactivate option, simply comment out)
$pohash{"V"} = "Visa";
$pohash{"M"} = "MasterCard";
$pohash{"D"} = "Discover";
$pohash{"A"} = "American Express";
# $pohash{"O"} = "Money Order";		# results in hold
# $pohash{"C"} = "Cash";		# admin only
# $pohash{"K"} = "Check";		# admin only
# $pohash{"I"} = "Invoice";		# admin only
 
# cc expiration months
$mmhash{"01"} = "January";
$mmhash{"02"} = "February";
$mmhash{"03"} = "March";
$mmhash{"04"} = "April";
$mmhash{"05"} = "May";
$mmhash{"06"} = "June";
$mmhash{"07"} = "July";
$mmhash{"08"} = "August";
$mmhash{"09"} = "September";
$mmhash{"10"} = "October";
$mmhash{"11"} = "November";
$mmhash{"12"} = "December";
 
# cc expiration years
$yyhash{"04"} = "2004";
$yyhash{"05"} = "2005";
$yyhash{"06"} = "2006";
$yyhash{"07"} = "2007";
$yyhash{"08"} = "2008";
$yyhash{"09"} = "2009";
$yyhash{"10"} = "2010";
$yyhash{"11"} = "2011";
$yyhash{"12"} = "2012";
$yyhash{"13"} = "2013";
$yyhash{"14"} = "2014";

# function prototypes
sub CheckForOrders ($);			# check for an existing order record
sub CheckForBillto ($);			# check for an existing billto record
sub InitBilltoRecord ();		# initialize a blank billto record
sub EditBilltoData (\%\%\%);		# edit form data for billto record
sub DisplayBilltoPage (\%);		# display the shipping info form
sub GetOrdersRecord ($);		# retrieve a orders record
sub GetShiptoRecord ($);		# retrieve a shipto record
sub GetBilltoRecord ($);		# retrieve a billto record
sub AddBilltoRecord (\%);		# add a billto record to the table
sub ChangeBilltoRecord (\%);		# change an existing billto 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 session information

#
# The orders record and shipto record tests are consistency checks to make
# sure the user is not messing with the system.  We use the data from these
# checks to build the billto record.
#

# make sure the orders record exists
%orders = GetOrdersRecord ($sess{sessid});
if (CheckError()) {
  DisplayError();
  ExitProgram();
}

unless (%orders) {
  ErrorMsg ("Order master record is missing -- checkout aborted.");
  DisplayError();
  ExitProgram();
}

# make sure the shipto record exists
%shipto = GetShiptoRecord ($sess{sessid});
if (CheckError()) {
  DisplayError();
  ExitProgram();
}

unless (%shipto) {
  ErrorMsg ("Shipping record is missing -- checkout aborted.");
  DisplayError();
  ExitProgram();
}

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

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

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

    # establish the billto record in the table
    if ($billto{new}) {
      # handle new billto data
      $billto{billid}   = AddBilltoRecord (%billto);
    } else {
      # update exiting billto data
      ChangeBilltoRecord (%billto);
    }

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

    # redirect to the next program
    Redirect ($next);
  } else {
    DisplayBilltoPage(%billto);
  }
} else {
  DisplayBilltoPage(%billto);
}

ExitProgram ();

### End Main Program ###


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

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

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

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

  return %billto;
}

 
###
###  InitBilltoRecord() initializes an billto record before it is loaded
###  with real values.
###
 
sub
InitBilltoRecord () {
  my %rec	= ();			# record hash (returned)
  my $name	= "";			# name field
  my ($sc,$mn,$hr,$dd,$mm,$yy,$wd,$yd,$isdst);
 
  # convert time
  ($sc,$mn,$hr,$dd,$mm,$yy,$wd,$yd,$isdst) = localtime (time);
  ++$mm;
 
  # format year for 2000
  $yy -= 100;
 
  # create the name
  $name  = $shipto{fname};
  $name .= " $shipto{mname}" if ($shipto{mname});
  $name .= " $shipto{lname}" if ($shipto{lname});

  # initialize the record
  $rec{billid}     = 0;
  $rec{orderid}    = $orders{orderid};
  $rec{sessid}     = $sess{sessid};
  $rec{memid}      = $orders{memid};
  $rec{name}       = $name;
  $rec{company}    = $shipto{company};
  $rec{address1}   = $shipto{address1};
  $rec{address2}   = $shipto{address2};
  $rec{city}       = $shipto{city};
  $rec{state}      = $shipto{state};
  $rec{zipcode}    = $shipto{zipcode};
  $rec{country}    = $shipto{country};
  $rec{paymeth}    = "V";
  $rec{ccnumber}   = "";
  $rec{ccseccode}  = "";
  $rec{ccexpmm}    = $mm;
  $rec{ccexpyy}    = $yy;
  $rec{cctransid}  = "";
  $rec{approval}   = "";
  $rec{orderamt}   = $orders{total};
  $rec{paid}       = "0.00";
  $rec{due}        = $orders{total};
  $rec{refund}     = "0.00";
  $rec{notes}      = "";
  $rec{updatedate} = "";
  $rec{createdate} = "";
 
  return %rec;
}
 

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

sub
EditBilltoData (\%\%\%) {
  my $fref	= shift;		# reference to form hash
  my $rref	= shift;		# reference to billto record hash
  my $oref	= shift;		# reference to billto record hash
  my %form	= %$fref;		# form hash
  my %billto	= %$rref;		# billto hash
  my %orders	= %$oref;		# orders hash
  my $len	= 0;			# field length

  # first name
  if (defined $form{name}) {
    if ($form{name} =~ /\w+/) {
      $billto{name} = $form{name};
    } else {
      ErrorMsg ("The name on the credit card is a required field.");
    }
  } else {
    ErrorMsg ("The name on the credit card is a required field.");
  }

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

  # address line 1
  if (defined $form{address1}) {
    if ($form{address1} =~ /\w+/) {
      $billto{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}) {
    $billto{address2} = $form{address2};
  }

  # city name
  if (defined $form{city}) {
    if ($form{city} =~ /\w+/) {
      $billto{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+/) {
      $billto{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$/) {
      $billto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d-\d\d\d\d$/) {
      $billto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d \d\d\d\d$/) {
      $billto{zipcode} = $form{zipcode};
    } elsif ($form{zipcode} =~ /^\d\d\d\d\d\d\d\d\d$/) {
      $billto{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$/) {
      $billto{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.");
  }

  # payment method
  if (defined $form{paymeth}) {
    if ($form{paymeth} =~ /^[VMDA]$/) {
      $billto{paymeth} = $form{paymeth};
    } else {
      ErrorMsg ("The payment method is invalid.");
    }
  } else {
    ErrorMsg ("The payment method is a required field.");
  }

  # credit card number
  if (defined $form{ccnumber}) {
    if ($form{ccnumber} =~ /^\d+$/) {
      $len = length ($form{ccnumber});
      if ($billto{paymeth} eq "A") {
        # amex is 15 digits
        if ($len == 15) {
          $billto{ccnumber} = $form{ccnumber};
        } else {
          ErrorMsg ("American Express cards must have 15 digits.");
        }
      } else {
        # all others are 16 digits
        if ($len == 16) {
          $billto{ccnumber} = $form{ccnumber};
        } else {
          ErrorMsg ("The credit card number must be 16 digits.");
        }
      }
    } else {
      ErrorMsg ("Your credit card number is invalid.");
    }
  } else {
    ErrorMsg ("The credit card number is a required field.");
  }

  # credit card security code
  if (defined $form{ccseccode}) {
    $billto{ccseccode} = $form{ccseccode};
  } else {
    if ($form{paymeth} =~ /^[VM]$/) {
      ErrorMsg ("The credit card security code is required.");
    }
  }

  # cc expiration month
  if (defined $form{ccexpmm}) {
    $billto{ccexpmm} = $form{ccexpmm};
  }

  # cc expiration year
  if (defined $form{ccexpyy}) {
    $billto{ccexpyy} = $form{ccexpyy};
  }

  # snag the current order amount values
  $billto{orderamt} = $orders{total};
  $billto{paid}     = "0.00";
  $billto{due}      = $orders{total};
  $billto{refund}   = "0.00";

  # we do not edit/move the following fields here:
  # 
  # cctransid
  # approval
  # notes
  # updatedate
  # createdate

  return %billto;
}


###
###  DisplayBilltoPage() 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
DisplayBilltoPage (\%) {
  my $rref	= shift;		# reference to billto hash
  my %billto	= %$rref;		# billto hash
  my %sthash	= ();			# state code hash

  # load description hashes
  %sthash = GetStates ();

  # output the top part of the page
  # SetPageSize (560,560);
  # SetPageName ("_xcheckout");
  OutputPageTop("Billing Information");
 
  # put a heading on the page
  print <<EOF;
<form action="$curr" method="post">
<input type="hidden" name="orderid" value="$billto{orderid}">
<input type="hidden" name="memid" value="$billto{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">
  Billing 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 billing information in the form below.  You must have a
  valid credit card to order online.  To order using money order, please call
  us to order over the phone.  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="25%" class="fttext">
  Name on Credit Card:&nbsp;*
  </td>
  <td align="left" width="75%" class="ftdata">
  <input type="text" name="name" value="$billto{name}" size="20"><br>
  This name must match the same name that is printed on the front of your
  credit card.
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Company:
  </td>
  <td align="left" width="75%" class="fdata">
  <input type="text" name="company" value="$billto{company}" size="40">
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Address 1:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <input type="text" name="address1" value="$billto{address1}" size="40"><br>
  This must be the same street address that you receive your credit card
  statement at each month.
  </td>
</tr>

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

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

<tr>
  <td align="left" width="25%" class="ftext">
  State:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <select name="state" size="1" class="text">
EOF
 
  # generate state code select menu
  GenerateOptions (%sthash,"",$billto{state});
 
  print <<EOF;
  </select>
  </td>
</tr>

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

<tr>
  <td align="left" width="25%" class="ftext">
  Country:
  </td>
  <td align="left" width="75%" class="fdata">
  <input type="hidden" name="country" value="$billto{country}">
  USA
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Payment Method:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <select name="paymeth" size="1" class="text">
EOF
 
  # generate payment method select menu
  GenerateOptions (%pohash,"",$billto{paymeth});
 
  print <<EOF;
  </select>
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Credit Card Number:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <input type="text" name="ccnumber" value="$billto{ccnumber}"
  maxlength="16" size="20"><br>
  Please enter your credit card number without any spaces or dashes between
  the digits.
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Security Code:
  </td>
  <td align="left" width="75%" class="fdata">
  <input type="text" name="ccseccode" value="$billto{ccseccode}"
  maxlength="4" size="2"><br>
  The security code is a three or four digit number printed in the signature
  block on the back of your card.
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Expiration Month:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <select name="ccexpmm" size="1" class="text">
EOF
 
  # generate month select menu
  GenerateOptions (%mmhash,"",$billto{ccexpmm});
 
  print <<EOF;
  </select>
  </td>
</tr>

<tr>
  <td align="left" width="25%" class="ftext">
  Expiration Year:&nbsp;*
  </td>
  <td align="left" width="75%" class="fdata">
  <select name="ccexpyy" size="1" class="text">
EOF
 
  # generate year select menu
  GenerateOptions (%yyhash,"",$billto{ccexpyy});
 
  print <<EOF;
  </select>
  </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;
}


###
###  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.
###
###  Data from this record is used to populate new billing records.
###
 
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;
}


###
###  GetBilltoRecord() retrieves the billto 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
GetBilltoRecord ($) {
  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 billto
    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;
}

 
###
###  AddBilltoRecord() 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
AddBilltoRecord (\%) {
  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 billto
    SET
      billid     = NULL,
      orderid    = $fields{orderid},
      sessid     = $fields{sessid},
      memid      = $fields{memid},
      name       = $fields{name},
      company    = $fields{company},
      address1   = $fields{address1},
      address2   = $fields{address2},
      city       = $fields{city},
      state      = $fields{state},
      zipcode    = $fields{zipcode},
      country    = $fields{country},
      paymeth    = $fields{paymeth},
      ccnumber   = $fields{ccnumber},
      ccseccode  = $fields{ccseccode},
      ccexpmm    = $fields{ccexpmm},
      ccexpyy    = $fields{ccexpyy},
      cctransid  = $fields{cctransid},
      approval   = $fields{approval},
      orderamt   = $fields{orderamt},
      paid       = $fields{paid},
      due        = $fields{due},
      refund     = $fields{refund},
      notes      = $fields{notes},
      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 billto 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 billto table.");
    return 0;
  }
 
  return $rc;
}
 
 
###
###  ChangeBilltoRecord() will modify the contents of an existing billto
###  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
ChangeBilltoRecord (\%) {
  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
      billto
    SET
      name       = $fields{name},
      company    = $fields{company},
      address1   = $fields{address1},
      address2   = $fields{address2},
      city       = $fields{city},
      state      = $fields{state},
      zipcode    = $fields{zipcode},
      country    = $fields{country},
      paymeth    = $fields{paymeth},
      ccnumber   = $fields{ccnumber},
      ccseccode  = $fields{ccseccode},
      ccexpmm    = $fields{ccexpmm},
      ccexpyy    = $fields{ccexpyy},
      cctransid  = $fields{cctransid},
      approval   = $fields{approval},
      orderamt   = $fields{orderamt},
      paid       = $fields{paid},
      due        = $fields{due},
      refund     = $fields{refund},
      notes      = $fields{notes},
      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;
}