package BookDB;
#
#  File:         /usr/local/lib/site_perl/BookDB.pm Book database common routines
#  Environment:  CGI.pm 2.48
#                DBI.pm
#                Pg.pm 1.7.3
#                PostgreSQL 7.2
#                Apache 1.3.23, 2.0.50
#                GNU/Linux
#  Author:       Louis B. Moore
#
# {{{ Copyright
#
#  Copyright (C)   1999, 2000, 2001, 2002, 2003, 2004, 2005  Louis B. Moore
#
#  This program is free software; you can redistribute it and/or
#  modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2
#  of the License, or (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#  Revision:
#       $Id: BookDB.pm 232 2005-05-02 03:09:28Z lbmoore $
#
# }}} Copyright
# {{{ package

use Carp;
use DBI;
use Pg;
use CGI qw(:all);
use vars qw($VERSION @ISA @EXPORT);
require Exporter;

@ISA = qw(Exporter);

@EXPORT = qw(
	     BookCatalog
	     BookABECatalog
	     BookEbayCatalog
	     BookMenu
	     getAcquisitionRec
	     getBook
	     getBookFieldTypes
	     getButtonBar
	     getDealerRec
	     getMap
	     getOneField
	     getOneKey
	     getSelectList
	     getSortedMap
	     incrAcqCount
	     makeMap
	     openBookDB
	     pushPickList
	     pushTextArea
	     pushTextField
	     pushCkandTextField
	     pushCkandSelectList
	     pushCkandRadioTrue
	     pushSelectListandText
	     setBookGetBookBack
	     setDBToText
	     setMapRecGetRecBack
	     setTextToDB
	     );

$BookDB::VERSION = '1.1';

# }}} package

#
# openBookDB ($dbhandle, dbname, user, autocommit)
#    to replace with BookDBConnect as shift to DBI progresses
#
# {{{ openBookDB

sub openBookDB (\$$$$) {

    local *dbhandle = shift;
    my    $dbname   = shift;
    my    $user     = shift;
    my    $autocomm = shift;

    if ($autocomm != 0) { $autocomm = 1; }

    $dbhandle = DBI->connect( "dbi:Pg:dbname=$dbname",
			      "$user",
			      "",
			      {PrintError => 0, RaiseError => 0, AutoCommit => $autocomm});

    if ( $dbhandle ) {

	return 1;

    } else {

	warn "Can't connect to books because:\n    ", $DBI::errstr, "\n\n";
	return $DBI::err;

    }

}

# }}} openBookDB

#
# setDBToText takes a text field and manipulates it for display in forms.
#
# {{{ setDBToText


sub setDBToText($;\$) {

    use HTML::Entities;

    my    $in  = shift;
    local *siz = shift;

    decode_entities($in);

    $siz = int(length($in)/68);

    $in =~  s/<[bB][rR]>/<BR>\n/g;
    $in =~  s/<[Pp]>/\n\n<P>/g;

    return($in);

}
# }}} setDBToText


#
# setTextToDB takes a text field and manipulates it for saving in DB
#
# {{{ setTextToDB


sub setTextToDB($) {

    use HTML::Entities;

    my $in  = shift;

    $in =~ s/^~//;       # I wish i remember why i did this
    $in =~ s/\n/ /g;     # no need for newlines (use <BR>)
    $in =~ s/\r/ /g;     # no need for carriage returns
    $in =~ s/  / /g;     # no need for extra spaces
    $in =~ s/\'/\'\'/g;  # double up single quotes

    encode_entities($in);

    return($in);

}

# }}} setTextToDB



#
# getBook
#
# {{{ getBook

sub getBook {

    my $dbh       = shift;
    my $uid       = shift;

    $sql  = "SELECT * FROM books WHERE bookuid = " . $uid . ";";

    $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $sql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $sql BECAUSE ", $sth->errstr(), "\n"; }

    return $sth->fetchrow_hashref("NAME_lc");

}

# }}} getBook


#
# setBookGetBookBack
#
# {{{ setBookGetBookBack

sub setBookGetBookBack {

    my $dbh  = shift;
    my $sql  = shift;
    my $uid  = shift;
    my $dbg  = shift;
    my %dat  = ();

    if ($dbg > 0) { print "<BR><BR><tt>$sql<tt><BR><BR>"; }

    my $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $sql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $sql BECAUSE ", $sth->errstr(), "\n"; }


    $sql = "SELECT * FROM books WHERE bookuid = " . $uid . ";";


    $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $sql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $sql BECAUSE ", $sth->errstr(), "\n"; }

    return $sth->fetchrow_hashref("NAME_lc");

}

# }}} setBookGetBookBack

#
# BookCatalog
#
# {{{ BookCatalog

sub BookCatalog(\%) {

    local *Data = shift;
       my $desc = "";

    if ($Data{"bookvolumes"} > 1) {

	$desc = $desc . $Data{"bookvolumes"} . " vols. ";

    }

    if (length($Data{"bookillustrator"}) > 0) {

	$desc = $desc . " Illustrated by " . $Data{"bookillustrator"} . ". \n";

    }

    if (length($Data{"bookcity"}) > 0) {

	$desc = $desc . $Data{"bookcity"} . ": \n";

    }

    if (length($Data{"bookpublisher"}) > 0) {

	$desc = $desc . $Data{"bookpublisher"} . ", \n";

    }

    if (length($Data{"bookdate"}) > 0) {

	$desc = $desc . $Data{"bookdate"} . ". \n";

    } else {

	$desc = $desc . "n/d. \n";

    }

    if ($Data{"sizedesc"} eq "Not Applicable") { $Data{"sizedesc"} = ""; };

    if (length($Data{"bookdimensions"}) > 0 and length($Data{"sizedesc"}) > 0) {

	$desc = $desc . $Data{"sizedesc"} . ", \n" . $Data{"bookdimensions"} . ". \n";

    } elsif (length($Data{"bookdimensions"}) > 0) {

	$desc = $desc . $Data{"bookdimensions"} . ". \n";

    } elsif (length($Data{"sizedesc"}) > 0) {

	$desc = $desc .  $Data{"sizedesc"} . ". \n";

    }

    if (length($Data{"bookbinding"}) > 0) {

	$desc = $desc . $Data{"bookbinding"} . ". \n";

    }
    if (length($Data{"bookedition"}) > 0) {

	$desc = $desc . $Data{"bookedition"} . ". \n";

    }
    if (length($Data{"bookcondition"}) > 0) {

	$desc = $desc . $Data{"bookcondition"} . "\n";

    }
    if (length($Data{"bookdescription"}) > 0) {

	$desc = $desc . $Data{"bookdescription"}."\n";

    }
    $desc =~ s/  / /g;

#
#  LOUIS - need to correctly allow <B></B> type pairs and still send through &nbsp; &quot; type stuff
#

    $desc =~ s/\&lt;([\w =#]+?)\&gt;/<$1>/gi;
    $desc =~ s/\&lt;(\/[\w =#]+?)\&gt;/<$1>/gi;
    $Data{"booktitle"} =~ s/\&lt;([\w =#]+?)\&gt;/<$1>/gi;
    $Data{"booktitle"} =~ s/\&lt;(\/[\w =#]+?)\&gt;/<$1>/gi;


    if ($Data{"booktitle"} =~ m/\.$/) {

	$desc =  "<B><I>" . $Data{"booktitle"} . "</I></B>\&nbsp;\&nbsp;\&nbsp; " .$desc;

    } else {

	$desc =  "<B><I>" . $Data{"booktitle"} . ".</I></B>\&nbsp;\&nbsp;\&nbsp; " .$desc;

    }

#
# Book Author
#

    if ($Data{"bookauthor"} =~ m/\.$/) {

	$desc = "<B>" . $Data{"bookauthor"} . "</B>\&nbsp;\&nbsp;\&nbsp; " . $desc;

    } else {

	$desc = "<B>" . $Data{"bookauthor"} . ".</B>\&nbsp;\&nbsp;\&nbsp; " . $desc;

    }

    return($desc);

}
# }}} BookCatalog


#
# BookABECatalog
#
# {{{ BookABECatalog


sub BookABECatalog(\%) {


    use HTML::Entities;

    local *Data    =  shift;
    my    $i       =  0;
    my    $k       =  "";
    my    $desc    =  $Data{"typedesc"} . " ~ ";

    if (length($Data{"bookcity"}) > 0) {

	$desc = $desc . $Data{"bookcity"} . ": ";

    }

    if (length($Data{"bookpublisher"}) > 0) {

	$desc = $desc . $Data{"bookpublisher"} . ", ";

    }

    if (length($Data{"bookdate"}) > 0) {

	$desc = $desc . $Data{"bookdate"} . ". ~ ";

    } else {

	$desc = $desc . "n/d. ~ ";

    }


    if ($Data{"bookvolumes"} > 1) {

	$desc = $desc . $Data{"bookvolumes"} . " vols. ";

    }

    if (length($Data{"bookillustrator"}) > 0) {

	$desc = $desc . " Illustrated by " . $Data{"bookillustrator"} . ". ";

    }


    if ($Data{"sizedesc"} eq "Not Applicable") { $Data{"sizedesc"} = ""; };

    if (length($Data{"bookdimensions"}) > 0 and length($Data{"sizedesc"}) > 0) {

	$desc = $desc . $Data{"sizedesc"} . ", " . $Data{"bookdimensions"} . ". ";

    } elsif (length($Data{"bookdimensions"}) > 0) {

	$desc = $desc . $Data{"bookdimensions"} . ". ";

    } elsif (length($Data{"sizedesc"}) > 0) {

	$desc = $desc .  $Data{"sizedesc"} . ". ";

    }

    if (length($Data{"bookbinding"}) > 0) {

	$desc = $desc . $Data{"bookbinding"} . ". ";

    }
    if (length($Data{"bookedition"}) > 0) {

	$desc = $desc . $Data{"bookedition"} . ". ";

    }
    if (length($Data{"bookcondition"}) > 0) {

	$desc = $desc . $Data{"bookcondition"} . " ";

    }
    if (length($Data{"bookdescription"}) > 0) {

	$desc = $desc . $Data{"bookdescription"}." ~ ";

    }
    $desc =~ s/  / /g;

    if ($Data{"booktitle"} =~ m/\.$/) {

	$desc =  $Data{"booktitle"} . " ~ " .$desc;

    } else {

	$desc =  $Data{"booktitle"} . ". ~ " .$desc;

    }

    if ($Data{"bookauthor"} =~ m/\.$/) {

	$desc = $Data{"bookauthor"} . " ~ " . $desc;

    } else {

	$desc = $Data{"bookauthor"} . ". ~ " . $desc;

    }

    $desc = $Data{"bookuid"} . " ~ " . $desc;

    for ($i = 1; $i <= 4; $i++) {

	unless  ($Data{"kw" . $i . "desc"} eq "None") { $k =  $k . $Data{"kw" . $i . "desc"} . ", "; }

    }

    $k =~ s/(.+), $/$1 ~ /;

    $desc = $desc . $k . $Data{"booklistprice"} . " ~";

    decode_entities($desc);

    return($desc);

}

# }}} BookABECatalog


#
# BookEbayCatalog
#
# {{{ BookEbayCatalog

sub BookEbayCatalog(\%) {

    local *Data = shift;
       my $desc = "";
       my $pix  = 0;
       my $pixt = "";
       my $pixn = "";
       my $beg  = "&lt;center&gt;<br>
&lt;table&gt;<br>
&lt;tr&gt;<br>
&lt;td colspan=2&gt;<br>
";
       my $end  = "<br>&lt;/td&gt;&lt;/tr&gt;<br>
&lt;tr&gt;&lt;td colspan=&quot;2&quot; align=&quot;left&quot;&gt;<br>
&lt;!-- Begin PayPal Logo --&gt;<br>
&lt;A HREF=&quot;https://secure.paypal.com/auction/pal=lbmoore%40hethcote.com&quot;
 target=&quot;_blank&quot;&gt;<br>
&lt;IMG SRC=&quot;http://images.paypal.com/images/lgo/logo6.gif&quot; BORDER=&quot;0&quot;<br>
 ALT=&quot;I accept PayPal, the #1 payment service in online auctions!&quot;&gt;&lt;/a&gt;<br>
&lt;!-- End PayPal Logo --&gt;<br>
&lt;/td&gt;&lt;/tr&gt;<br>
&lt;/table&gt;<br>
&lt;/center&gt;<br>
";

    if ($Data{"bookvolumes"} > 1) {

	$desc = $desc . $Data{"bookvolumes"} . " vols. ";

    }

    if (length($Data{"bookillustrator"}) > 0) {

	$desc = $desc . " Illustrated by " . $Data{"bookillustrator"} . ". \n";

    }

    if (length($Data{"bookcity"}) > 0) {

	$desc = $desc . $Data{"bookcity"} . ": \n";

    }

    if (length($Data{"bookpublisher"}) > 0) {

	$desc = $desc . $Data{"bookpublisher"} . ", \n";

    }

    if (length($Data{"bookdate"}) > 0) {

	$desc = $desc . $Data{"bookdate"} . ". \n";

    } else {

	$desc = $desc . "n/d. \n";

    }

    if ($Data{"sizedesc"} eq "Not Applicable") { $Data{"sizedesc"} = ""; };

    if (length($Data{"bookdimensions"}) > 0 and length($Data{"sizedesc"}) > 0) {

	$desc = $desc . $Data{"sizedesc"} . ", \n" . $Data{"bookdimensions"} . ". \n";

    } elsif (length($Data{"bookdimensions"}) > 0) {

	$desc = $desc . $Data{"bookdimensions"} . ". \n";

    } elsif (length($Data{"sizedesc"}) > 0) {

	$desc = $desc .  $Data{"sizedesc"} . ". \n";

    }

    if (length($Data{"bookbinding"}) > 0) {

	$desc = $desc . $Data{"bookbinding"} . ". \n";

    }
    if (length($Data{"bookedition"}) > 0) {

	$desc = $desc . $Data{"bookedition"} . ". \n";

    }
    if (length($Data{"bookcondition"}) > 0) {

	$desc = $desc . $Data{"bookcondition"} . "\n";

    }
    if (length($Data{"bookdescription"}) > 0) {

	$desc = $desc . $Data{"bookdescription"}."\n";

    }
    $desc =~ s/  / /g;

    if ($Data{"booktitle"} =~ m/\.$/) {

	$desc =  "&lt;B&rt;&lt;I&gt;" . $Data{"booktitle"} . "&lt;/I&gt;&lt;/B&gt;\&nbsp;\&nbsp;\&nbsp; " .$desc;

    } else {

	$desc =  "&lt;B&gt;&lt;I&gt;" . $Data{"booktitle"} . ".&lt;/I&gt;&lt;/B&gt;\&nbsp;\&nbsp;\&nbsp; " .$desc;

    }

#
# Book Author
#

    if ($Data{"bookauthor"} =~ m/\.$/) {

	$desc = "&lt;B&gt;" . $Data{"bookauthor"} . "&lt;/B&gt;\&nbsp;\&nbsp;\&nbsp; " . $desc;

    } else {

	$desc = "&lt;B&gt;" . $Data{"bookauthor"} . ".&lt;/B&gt;\&nbsp;\&nbsp;\&nbsp; " . $desc;

    }

    $desc =~ s/\"/\&amp;quot;/g;
    $desc = $desc . "&lt;br&gt; &lt;br&gt;Winner pays \$3.50 towards priority mail, international " .
	"shipping at cost. Colorado residents will have to pay sales tax, as appropriate for their " .
	    "county. &lt;br&gt;";

    $desc = $desc . "&lt;br&gt;We can accept Paypal, money orders, cashiers checks and personal checks. &lt;br&gt;";

    $desc = $desc . "&lt;br&gt;<br>&lt;a href=&quot;http://cgi3.ebay.com/aw-cgi/eBayISAPI.dll?ViewListedItems&userid=hethcote&quot;&gt;&lt;b&gt;View our other auctions&lt;/b&gt;&lt;/a&gt;&lt;br&gt;";

    if ($Data{"bookimages"} > 1) {

	$pixt = "<br><br>&lt;tr&gt;";
	for ($pix=1; $pix<=($Data{"bookimages"}-1); $pix++) {

	    $pixn = sprintf("http://www.hethcote.com/bookpix/j%04d-%02d.jpg",$Data{"bookuid"},$pix+1);

	    $pixt = $pixt . "&lt;td&gt;&lt;img src=$pixn&gt;&lt;/td&gt;<br>";

	    if ($pix%2 == 0 && $pix != $Data{"bookimages"}) {

		$pixt =  $pixt . "&lt;/tr&gt;\n";

	    }
	}

	$pixt =  $pixt . "&lt;/tr&gt;\n\n";

    }

    $desc = $beg . $desc . $pixt . $end .

	sprintf("<br><br>www.hethcote.com/bookpix/j%04d-%02d.jpg",$Data{"bookuid"},1);


    return($desc);

}
# }}} BookEbayCatalog


#
# getBookFieldTypes, load hash with proper prefix and postfix field qoutes.
#
# %hash = ();
# *hash = getBookFieldTypes();
#
# {{{ getBookFieldTypes

sub getBookFieldTypes {

    my %fld = ();

    $fld{"type"}             = "";
    $fld{"bookauthor"}       = "'";
    $fld{"booksortauthor"}   = "'";
    $fld{"bookauthor2"}      = "'";
    $fld{"booktitle"}        = "'";
    $fld{"booksorttitle"}    = "'";
    $fld{"bookillustrator"}  = "'";
    $fld{"bookcity"}         = "'";
    $fld{"bookpublisher"}    = "'";
    $fld{"bookdate"}         = "'";
    $fld{"bookbinding"}      = "'";
    $fld{"bookedition"}      = "'";
    $fld{"size"}             = "";
    $fld{"bookdimensions"}   = "'";
    $fld{"bookvolumes"}      = "'";
    $fld{"bookcondition"}    = "'";
    $fld{"bookcost"}         = "'";
    $fld{"booklistprice"}    = "'";
    $fld{"discount"}         = "";
    $fld{"status"}           = "";
    $fld{"acquisition"}      = "";
    $fld{"shelf"}            = "";
    $fld{"bookdescription"}  = "'";
    $fld{"bookinsured"}      = "'";
    $fld{"bookcatalognum"}   = "'";
    $fld{"bookoutofprint"}   = "'";
    $fld{"keyword1"}         = "";
    $fld{"keyword2"}         = "";
    $fld{"keyword3"}         = "";
    $fld{"keyword4"}         = "";
    $fld{"bookfreekeywords"} = "'";
    $fld{"bookimages"}       = "";

    return \%fld;
}
# }}} getBookFieldTypes

#
# getOneField
#
# {{{ getOneField

sub getOneField {

    my $dbh      = shift;
    my $sth;
    my $tbl      = shift;
    my $fld      = shift;
    my $rec      = shift;
    my @rec      = ();

    my $getsql   = "SELECT " . $fld . " FROM " . $tbl . " WHERE " . $tbl . "uid = " . $rec . ";";

    $sth = $dbh->prepare( $getsql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $getsql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $getsql BECAUSE ", $sth->errstr(), "\n"; }

    @rec = $sth->fetchrow_array;

    $rec = $rec[0];

    return $rec;

}
# }}} getOneField


#
# getOneKey
#
# {{{ getOneKey

sub getOneKey {

    my $dbh      = shift;
    my $sth;
    my $tbl      = shift;
    my $fld      = shift;
    my $rec      = shift;
    my @rec      = ();

    my $getsql   = "SELECT " . $tbl . "uid FROM " . $tbl . " WHERE " . $fld . " = '" . $rec . "';";

    $sth = $dbh->prepare( $getsql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $getsql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $getsql BECAUSE ", $sth->errstr(), "\n"; }

    @rec = $sth->fetchrow_array;

    $rec = $rec[0];

    return $rec;

}
# }}} getOneKey

#
# incrAcqCount
#
# {{{ incrAcqCount

sub incrAcqCount {

    my $dbh      = shift;
    my $sth;
    my $acq      = shift;
    my @rec      = ();

    my $getsql   = "SELECT acquisitionitems FROM acquisition WHERE acquisitionuid = " . $acq . ";";

    $sth = $dbh->prepare( $getsql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $getsql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $getsql BECAUSE ", $sth->errstr(), "\n"; }

    @rec = $sth->fetchrow_array;

    $rec[0] = int($rec[0]);
    $rec[0]++;

    my $setsql  = "UPDATE acquisition SET acquisitionitems = " . $rec[0] . " WHERE acquisitionuid = " . $acq . ";";

    $sth = $dbh->prepare( $setsql );
    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $setsql BECAUSE ", $dbh->errstr(), "\n"; }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $setsql BECAUSE ", $sth->errstr(), "\n"; }

    return $rec[0];

}
# }}} incrAcqCount

#
# setMapRecGetRecBack (DBI.pm) replaces BookMapSetNGet (Pg.pm)
#
# {{{ setMapRecGetRecBack

sub setMapRecGetRecBack {

    my $dbh      = shift;
    my $table    = shift;
    my $mapuid   = shift;
    my $mapval   = shift;
    my $default  = shift;
    my $sth;
    my $setsql   = "INSERT INTO " . $table . "(\"" . $table . "uid\",\"" . $table . "desc\") " .
                   "VALUES (NEXTVAL('" . $table . "_serial'), '" . $mapval . "');";
    my $getsql   = "SELECT " . $table . "uid FROM " . $table . " WHERE " . $table . "desc = '" . $mapval . "';";
    my @map      = ();


    if ($mapuid == 0)  {         # in other words, a NEW map record: uid-->value

	if ($mapval ne '')  {    # and there is actually data.

	    $sth = $dbh->prepare( $setsql );
	    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $setsql BECAUSE ", $dbh->errstr(), "\n"; }

	    $sth->execute();
	    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $setsql BECAUSE ", $sth->errstr(), "\n"; }

	    $sth = $dbh->prepare( $getsql );
	    if ( !$sth ) { $dbh->disconnect(); die "Failed to get prepare $getsql BECAUSE ", $dbh->errstr(), "\n"; }

	    $sth->execute();
	    if ( !$sth ) { $dbh->disconnect() ; die "Failed to get execute $getsql BECAUSE ", $sth->errstr(), "\n"; }

	    @map = $sth->fetchrow_array;
	    $mapuid = $map[0];

	} else { # mapval ne ''

	    $mapuid = $default;

	}

    } #mapuid==0

    return $mapuid;

}
# }}} setMapRecGetRecBack


#
# makeMap  ($dbh, $table, field, \@array);
#
# {{{ makeMap

sub makeMap {

    my $dbh       = shift;
    my $tbl       = shift;
    my $fld       = shift;
    my $array_ref = shift;
    my $sql       = " select distinct " . $fld . " from " . $tbl . " order by " . $fld . ";";
    my @rec       = ();
    my $i         = 1;

    $$array_ref[0] = "Enter";

    my $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect() ; return $dbh->errstr(); }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; return $sth->errstr(); }

    while ( @rec = $sth->fetchrow_array ) {

	$$array_ref[$i++] = $rec[0];

    }

    return 1;

}
# }}} makeMap

#
# pushTextField (\@rows, $desc, $fieldname, $default, $size)
#
# {{{ pushTextField

sub pushTextField {

    my $rowref  = shift;
    my $desc    = shift;
    my $field   = shift;
    my $default = shift;
    my $size    = shift;
    my $len     = 1;

    $default = setDBToText($default,$len);

    push (@$rowref, td([b($desc),textfield(-name    => $field,
					   -default => $default,
					   -size    => $size)]));

    return;

}
# }}} pushTextField


#
# pushCkandTextField (\@rows, $desc, $fieldname, $default, $size, $checked)
#
# {{{ pushCkandTextField

sub pushCkandTextField {

    my $rowref   = shift;
    my $desc     = shift;
    my $field    = shift;
    my $default  = shift;
    my $size     = shift;
    my $checked  = shift;
    my $selfield = "sel-".$field;
    my $len      = 1;

    $default = setDBToText($default,$len);

    push (@$rowref, td([b(checkbox(-name => $selfield,
				 -checked=> $checked,
				 -label=> "   ".$desc)),
			textfield(-name    => $field,
				  -default => $default,
				  -size    => $size)]));

    return;

}
# }}} pushCkandTextField

#
# pushPickList(\@rows, \@values, $desc, $field, $default, $size)
#
# {{{ pushPickList

sub pushPickList {

    my $rowref = shift;
    my $valref = shift;
    my $desc   = shift;
    my $field  = shift;
    my $def    = shift;
    my $size   = shift;

    push (@$rowref, td([b($desc),scrolling_list(-name    =>  $field,
						-values  =>  $valref,
						-default =>  $def,
						-size    =>  $size)]));


    return 1;

}


# }}} pushPickList

#
# getSelectList(\@rows, $dbh, $desc, $field, $table, $addtitle, $default, $size)
#
# {{{ getSelectList
#
#
# \@rows     is a reference to an array holding table rows for a CGI.pm call
#  $dbh      is a database handle from a openBookDB() call which is a DBI.pm call
#  $desc     becomes the bold-faced label element in a {LABEL,[select-list]} column pair
#  $field    is the cgi variable name that will hold the value selected
#  $table    is the database table with a tableuid and tabledesc elements to select from
#  $addtitle is added to the beginning of the list and assigned value==0
#  $default  is the VALUE to be highlighted as default, 0==$addtitle...
#            often the existing field value.
#
#  $size    visible size of the select list; 1, 3 and 5 are comfortable.
#
# Should be renamed to pushSelectList in the future since it adds a row to the rowref
#
#

sub getSelectList {

    my $rowref   = shift;
    my $dbh      = shift;
    my $desc     = shift;
    my $field    = shift;
    my $table    = shift;
    my $title    = shift;
    my $default  = shift;
    my $size     = shift;
    my $status;
    my %labels = ();
    my @values = ();
    $labels{"0"} = $title;
    $values[0]   = 0;

    getSortedMap($dbh, $table, 0, \%labels, \@values);

    push (@$rowref, td(["<b>$desc</b><br>[ $labels{$default} ]",scrolling_list(-name    =>  $field,
									    -values  =>  \@values,
									    -labels  =>  \%labels,
									    -default =>  $default,
									    -size    =>  $size)]));

    return 1;


}

# }}} getSelectList

#
# pushSelectListandText(\@rows, $dbh, $desc, $field, $table, $addtitle, $default, $size, $txtdesc, $txtfield, $txtdefault, $txtsize)
#
# {{{ pushSelectListandText
#
#
# \@rows     is a reference to an array holding table rows for a CGI.pm call
#  $dbh      is a database handle from a openBookDB() call which is a DBI.pm call
#  $desc     becomes the bold-faced label element in a {LABEL,[select-list]} column pair
#  $field    is the cgi variable name that will hold the value selected
#  $table    is the database table with a tableuid and tabledesc elements to select from
#  $addtitle is added to the beginning of the list and assigned value==0
#  $default  is the VALUE to be highlighted as default, 0==$addtitle...
#            often the existing field value.
#
#  $size    visible size of the select list; 1, 3 and 5 are comfortable.
#
#

sub pushSelectListandText {

    my $rowref   = shift;
    my $dbh      = shift;
    my $desc     = shift;
    my $field    = shift;
    my $table    = shift;
    my $title    = shift;
    my $default  = shift;
    my $size     = shift;
    my $txtdesc  = shift;
    my $txtfield = shift;
    my $txtdefault = shift;
    my $txtsize  = shift;
    my $len      = 1;
    my $status;
    my %labels = ();
    my @values = ();
    $labels{"0"} = $title;
    $values[0]   = 0;
    $txtdefault = setDBToText($txtdefault,$len);

    getSortedMap($dbh, $table, 0, \%labels, \@values);

    push (@$rowref,td({-align=>LEFT,-valign=>CENTER},
		      ["<b>$desc</b><br>[ $labels{$default} ]" , scrolling_list(-name    =>  $field,
										-values  =>  \@values,
										-labels  =>  \%labels,
										-default =>  $default,
										-size    =>  $size) .
		       "&nbsp;&nbsp;&nbsp;" . b($txtdesc) . "&nbsp;&nbsp;" . textfield(-name    => $txtfield,
										       -default => $txtdefault,
										       -size    => $txtsize)]));

    return 1;


}

# }}} pushSelectListandText


#
# pushCkandSelectList(\@rows, $dbh, $desc, $field, $table, $size)
#
# {{{ pushCkandSelectList
#
#
# \@rows     is a reference to an array holding table rows for a CGI.pm call
#  $dbh      is a database handle from a openBookDB() call which is a DBI.pm call
#  $desc     becomes the bold-faced label element in a {LABEL,[select-list]} column pair
#  $field    is the cgi variable name that will hold the value selected
#  $table    is the database table with a tableuid and tabledesc elements to select from
#  $size    visible size of the select list; 1, 3 and 5 are comfortable.
#
#

sub pushCkandSelectList {

    my $rowref   = shift;
    my $dbh      = shift;
    my $desc     = shift;
    my $field    = shift;
    my $table    = shift;
    my $size     = shift;
    my $selfield = "sel-".$field;
    my $checked  = "";
    my $status;
    my %labels = ();
    my @values = ();

    getSortedMap($dbh, $table, 0, \%labels, \@values);

    @values = @values[1 .. $#values]; #Adjust for no 0th element

    push (@$rowref, td([b(checkbox(-name    => $selfield,
				   -checked => $checked,
				   -label=> "   ".$desc)),
			scrolling_list(-name    =>  $field,
				       -values  =>  \@values,
				       -labels  =>  \%labels,
				       -size    =>  $size)]));

    return 1;

}

# }}} pushCkandSelectList

#
# pushCkandRadioTrue(\@rows, $dbh, $desc, $field, $table, $size)
#
# {{{ pushCkandRadioTrue
#
#
# \@rows     is a reference to an array holding table rows for a CGI.pm call
#  $dbh      is a database handle from a openBookDB() call which is a DBI.pm call
#  $desc     becomes the bold-faced label element in a {LABEL,[select-list]} column pair
#  $field    is the cgi variable name that will hold the value selected
#  $table    is the database table with a tableuid and tabledesc elements to select from
#  $size    visible size of the select list; 1, 3 and 5 are comfortable.
#
#

sub pushCkandRadioTrue {

    my $rowref   = shift;
    my $desc     = shift;
    my $field    = shift;
    my $selfield = "sel-".$field;
    my $checked  = shift;
    my $status;
    my %labels = ('f' => 'False', 't' => 'True');
    my @values = ('f','t');

    push (@$rowref, td([b(checkbox(-name    => $selfield,
				   -checked => $checked,
				   -label=> "   ".$desc)),
			radio_group(-name    =>  $field,
				    -values  =>  \@values,
				    -labels  =>  \%labels,
				    -rows    =>  1,
				    -columns =>  2)]));

    return 1;

}

# }}} pushCkandRadioTrue


#
#
# pushTextArea (\@rows, $desc, $fieldname, $default, $rows, $cols)
#
#
# {{{ pushTextArea

sub pushTextArea {

    my $rowref  = shift;
    my $desc    = shift;
    my $field   = shift;
    my $default = shift;
    my $rows    = shift;
    my $cols    = shift;
    my $len     = 1;

    $default = setDBToText($default,$len);

    $rows = $rows + $len;

    push (@$rowref, td([b($desc),textarea(-name    => $field,
					  -default => $default,
					  -wrap    => 'on',
					  -rows    => $rows,
					  -cols    => $cols)]));

    return;

}

# }}} pushTextArea


#
# BookMenu
#
# {{{ BookMenu

sub BookMenu  {

    @menu = ();

    push (@menu,th(["\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;"]));
    push (@menu,th([h3(a({href=>"/BookBiz.html"},"Book Business")),
		    h3(a({href=>"/books/index.html"},"Book Home")),
		    h3(a({href=>"/cgi-bin/BookAcq.cgi"},"Book Acquisition")),
		    h3(a({href=>"/cgi-bin/BookSearch.cgi"},"Book Search")),
		    h3(a({href=>"/cgi-bin/BookBook.cgi"},"Book Entry/Edit")),
		    h3(a({href=>"/cgi-bin/BookEbay.cgi"},"Book Ebay Edit")),
		    ]));
    push (@menu,th(["\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;"]));


    return(\@menu);


}

# }}} BookMenu

#
# getButtonBar
#
# {{{ getButtonBar

sub getButtonBar  {

    @menu = ();

    push (@menu,th(["\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;"]));
    push (@menu,th([
		    a({href=>"/books/index.html"},"<img src=\"/books/btn-home.png\" border=0>"),
		    a({href=>"/cgi-bin/BookAcq.cgi"},"<img src=\"/books/btn-acq.png\" border=0>"),
		    a({href=>"/cgi-bin/BookSearch.cgi"},"<img src=\"/books/btn-search.png\" border=0>"),
		    a({href=>"/cgi-bin/BookBook.cgi"},"<img src=\"/books/btn-book.png\" border=0>"),
		    a({href=>"/cgi-bin/BookEbay.cgi"},"<img src=\"/books/btn-ebay-edit.png\" border=0>"),
		    ]));
    push (@menu,th(["\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;","\&nbsp;"]));


    return(\@menu);


}

# }}} getButtonBar

#
# getDealerRec
#
# {{{ getDealerRec

sub getDealerRec ($) {

    my $rec   = shift;
    my @dlr   = split(/\|/,$rec);
    my @rows  = ();
    my %tf    = ( "t" => "True",
		  "f" => "False" );
    my @tf    = ('f','t');

    push (@rows,"\n",td(["Name/Description: ",
			 textfield(-name     => 'dealerdesc',
				   'default' => setDBToText($dlr[1]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Contact: ",
			 textfield(-name     => 'dealercontact',
				   'default' => setDBToText($dlr[8]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Address1: ",
			 textfield(-name     => 'dealeraddr1',
				   'default' => setDBToText($dlr[2]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Address2: ",
			 textfield(-name     => 'dealeraddr2',
				   'default' => setDBToText($dlr[3]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Address3 (SL-City): ",
			 textfield(-name     => 'dealeraddr3',
				   'default' => setDBToText($dlr[4]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Address4 (SL-State): ",
			 textfield(-name     => 'dealeraddr4',
				   'default' => setDBToText($dlr[5]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Address5 (SL-Zip): ",
			 textfield(-name     => 'dealeraddr5',
				   'default' => setDBToText($dlr[6]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Country: ",
			 textfield(-name     => 'dealercountry',
				   'default' => setDBToText($dlr[7]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Phone: ",
			 textfield(-name     => 'dealerphone',
				   'default' => setDBToText($dlr[9]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Fax: ",
			 textfield(-name     => 'dealerfax',
				   'default' => setDBToText($dlr[10]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Email: ",
			 textfield(-name     => 'dealeremail',
				   'default' => setDBToText($dlr[11]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Web: ",
			 textfield(-name     => 'dealerweb',
				   'default' => setDBToText($dlr[12]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["CC: ",
			 textfield(-name     => 'dealercc',
				   'default' => setDBToText($dlr[13]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["BCC: ",
			 textfield(-name     => 'dealerbcc',
				   'default' => setDBToText($dlr[14]),
				   'size'    => '60')
                       ]));
    push (@rows,"\n",td(["Notes: ",
			 textfield(-name     => 'dealernotes',
				   'default' => setDBToText($dlr[15]),
				   'size'    => '60')
                       ]));

    push (@rows,td(["Dealer in SQL-Ledger",radio_group(-name=> 'dealerinsl',
						     -values=>['t','f'],
						     -default=>$tf[$dlr[16]],
						     -labels=>\%tf,
						     -rows=>2,-columns=>1)]));

    return \@rows;

}



# }}} getDealerRec


#
# getAcquisitionRec
#
# {{{ getAcquisitionRec

sub getAcquisitionRec ($$) {

    my $rec     = shift;
    my $dbh     = shift;
    my @acq     = split(/\|/,$rec);
    my @rows    = ();
    my %tf      = ( "t" => "True",
		    "f" => "False" );
    my @tf      = ("f","t");
    my %dlabels = ();
    my %dvalues = ();
    my %slabels = ();
    my %svalues = ();
    my $tmp;

    getSortedMap($dbh, "dealer", 0, \%dlabels, \@dvalues);
    getSortedMap($dbh, "source", 0, \%slabels, \@svalues);

    push (@rows,"\n",td(["Acquisition Date: ",
			 textfield(-name     => 'acquisitiondate',
				   'default' => setDBToText($acq[1]),
				   'size'    => '60')
                       ]));

    push (@rows,"\n",td(["Dealer: ",
			 scrolling_list(-name    =>  'dealer',
					-values  =>  \@dvalues,
					-labels  =>  \%dlabels,
					-default =>  $acq[2],
					-size    =>  3)]));

    push (@rows,"\n",td(["Source: ",
			 scrolling_list(-name    =>  'source',
					-values  =>  \@svalues,
					-labels  =>  \%slabels,
					-default =>  $acq[7],
					-size    =>  3)]));

    push (@rows,"\n",td(["Acquisition Info: ",
			 textfield(-name     => 'acquisitioninfo',
				   'default' => setDBToText($acq[3]),
				   'size'    => '60')
                       ]));

    push (@rows,"\n",td(["Acquisition Items: ",
			 textfield(-name     => 'acquisitionitems',
				   'default' => setDBToText($acq[4]),
				   'size'    => '60')
                       ]));

    push (@rows,"\n",td(["Acquisition Cost: ",
			 textfield(-name     => 'acquisitioncost',
				   'default' => setDBToText($acq[5]),
				   'size'    => '60')
                       ]));

    push (@rows,td(["Acquistion Tax Exempt?",radio_group(-name=> 'acquisitiontaxed',
						     -values=>['t','f'],
						     -default=>$tf[$acq[6]],
						     -labels=>\%tf,
						     -rows=>2,-columns=>1)]));

    push (@rows,td(["Acquistion Exported to SL?",radio_group(-name=> 'acquisitionexported',
						     -values=>['t','f'],
						     -default=>$tf[$acq[8]],
						     -labels=>\%tf,
						     -rows=>2,-columns=>1)]));

    push (@rows,td(["Acquistion Exportable to SL?",radio_group(-name=> 'acquisitionexportable',
						     -values=>['t','f'],
						     -default=>$tf[$acq[9]],
						     -labels=>\%tf,
						     -rows=>2,-columns=>1)]));

    return \@rows;

}



# }}} getAcquisitionRec


#
# getMap ($dbh, $table, {1=name=>number, 0=number=>name}  , \%HASH);
#
# replaces BookGetMap (Pg.pm)
#
# {{{ getMap

sub getMap {

    my $dbh      = shift;
    my $map      = shift;
    my $dir      = shift;
    my $hash_ref = shift;
    my $sql      = "SELECT " . $map . "uid," . $map . "desc FROM " . $map . ";";
    my $cnt      = 1;

    my $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect() ; return $dbh->errstr(); }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; return $sth->errstr(); }

    while ( @rec = $sth->fetchrow_array ) {

	if ($dir == 1) {
	    $$hash_ref{$rec[1]}=$rec[0];
	} else {
	    $$hash_ref{$rec[0]}=$rec[1];
	}
    }

    return 1;
}

# }}} getMap


#
# getSortedMap ($dbh, $table, {1=name=>number, 0=number=>name}  , \%LABLARR, \@VALUEARR);
#
# replaces BookGetSortMap (Pg.pm)
#
# {{{ getSortedMap

sub getSortedMap {

    my $dbh      = shift;
    my $map      = shift;
    my @rec      = ();
    my $dir      = shift;
    my $labl_ref = shift;
    my $valu_ref = shift;
    my %hash     = ();
    my $cnt      = 1;
    my $sql      = "SELECT " . $map . "uid," . $map . "desc FROM " . $map . " ORDER BY " . $map . "desc;";

    my $sth = $dbh->prepare( $sql );
    if ( !$sth ) { $dbh->disconnect() ; return $dbh->errstr(); }

    $sth->execute();
    if ( !$sth ) { $dbh->disconnect() ; return $sth->errstr(); }

    while ( @rec = $sth->fetchrow_array ) {

	if ($dir == 1) {
	    $hash{$rec[1]}=$rec[0];
	} else {
	    $hash{$rec[0]}=$rec[1];
	}
    }

    foreach $dir (sort {$hash{$a} cmp $hash{$b}} keys %hash) {

	$$labl_ref{$dir}   = $hash{$dir};
	$$valu_ref[$cnt++] = $dir;
    }

    return 1;
}

# }}} getSortedMap




#
# Return true, or else compile-time errors.
#

1;
