#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# backend code for customers and vendors
#
#======================================================================

package CT;

sub create_links {

    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};
    my $query;
    my $sth;
    my $ref;
    my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap";
    my $ARAP = uc $arap;

    if ( $form->{id} ) {
        $query = qq|
			    SELECT ct.*, b.description AS business, s.*,
			           e.name AS employee, 
			           g.pricegroup AS pricegroup,
			           l.description AS language, ct.curr
			      FROM $form->{db} ct
			 LEFT JOIN business b ON (ct.business_id = b.id)
			 LEFT JOIN shipto s ON (ct.id = s.trans_id)
			 LEFT JOIN employee e ON (ct.employee_id = e.id)
			 LEFT JOIN pricegroup g ON (g.id = ct.pricegroup_id)
			 LEFT JOIN language l ON (l.code = ct.language_code)
			     WHERE ct.id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        $ref = $sth->fetchrow_hashref(NAME_lc);
        for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
        $sth->finish;

        # check if it is orphaned
        $query = qq|
			SELECT a.id
			  FROM $arap a
			  JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
			 WHERE ct.id = ?

			 UNION

			SELECT a.id
			  FROM oe a
			  JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
			 WHERE ct.id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id}, $form->{id} )
          || $form->dberror($query);

        unless ( $sth->fetchrow_array ) {
            $form->{status} = "orphaned";
        }

        $sth->finish;

        # get taxes for customer/vendor
        $query = qq|
			SELECT c.accno
			  FROM chart c
			  JOIN $form->{db}tax t ON (t.chart_id = c.id)
			 WHERE t.$form->{db}_id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            $form->{tax}{ $ref->{accno} }{taxable} = 1;
        }

        $sth->finish;

    }
    else {

        ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh);

        $query = qq|SELECT current_date|;
        ( $form->{startdate} ) = $dbh->selectrow_array($query);

    }

    # get tax labels
    $query = qq|
		   SELECT DISTINCT c.accno, c.description
		     FROM chart c
		     JOIN tax t ON (t.chart_id = c.id)
		    WHERE c.link LIKE ?
		 ORDER BY c.accno|;

    $sth = $dbh->prepare($query);
    $sth->execute("%${ARAP}_tax%") || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $form->{taxaccounts} .= "$ref->{accno} ";
        $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
    }

    $sth->finish;
    chop $form->{taxaccounts};

# get business types ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
    $query = qq|
		   SELECT *
		     FROM business
		 ORDER BY 2|;

    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_business} }, $ref;
    }

    $sth->finish;

    # employees/salespersons
    $form->all_employees( $myconfig, $dbh, undef,
        ( $form->{vc} eq 'customer' )
        ? 1
        : 0 );

# get language ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
    $query = qq|
		  SELECT *
		    FROM language
		ORDER BY 2|;

    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_language} }, $ref;
    }

    $sth->finish;

# get pricegroups ## needs fixing, this is bad (SELECT * ...) with order by 2. Yuck
    $query = qq|
		  SELECT *
		    FROM pricegroup
		ORDER BY 2|;

    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_pricegroup} }, $ref;
    }

    $sth->finish;

    # get currencies
    $query = qq|
		SELECT value AS currencies
		  FROM defaults
		  WHERE setting_key = 'curr'|;

    ( $form->{currencies} ) = $dbh->selectrow_array($query);

    $dbh->commit;

}

sub _save_vc {
    ($form) = @_;
    my $dbh = $form->{dbh};
    my $updated = 0;
    if ($form->{vc} eq 'customer'){
       $form->{vc}  = 'customer';
       $form->{entity_class} = 2;
    } else {
       $form->{vc} = 'vendor';
       $form->{entity_class} = 1;
    }

    if ( $form->{id} ) {
        $query = qq|
		DELETE FROM $form->{vc}tax
		 WHERE entity_id = 
		        (select entity_id from $form->{vc} 
		         WHERE id = ?)|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        $query = qq|
			SELECT id 
			  FROM $form->{vc}
			 WHERE id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        if ( $sth->fetchrow_array ) {
            $sth->finish;
            $query = qq|
		UPDATE $form->{vc}
		SET discount = ?
			taxincluded = ?
			creditlimit = ?
			terms = ?
			$form->{vc}number = ?
			cc = ?
			bcc = ?
			business_id = ?
			sic_code = ?
			language_code = ?
			pricegroup_id = ?
			curr = ?
			startdate = ?
			enddate = ?
			invoice_notes = ?
			bic = ?
			iban = ?
		WHERE id = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{discount}, $form->{taxincluded}, $form->{creditlimit},
                $form->{terms}, $form->{"$form->{vc}number"}, $form->{cc},
                $form->{bcc}, $form->{business_id}, $form->{sic_code}, 
                $form->{language_code}, $form->{pricegroup_id},
                $form->{curr}, $form->{startdate}, $form->{enddate},
                $form->{invoice_notes}, $form->{bic}, $form->{iban}, $form->{id}
            ) || $form->dberror(__FILE__.":".__LINE__.":$query");
            $updated = 1;
        }
    }
    if (!$updated){
            # Creating Entity
            $query = qq|INSERT INTO entity (name, entity_class) VALUES (?, ?)|;
            $sth = $dbh->prepare($query);
            $sth->execute($form->{name}, $form->{entity_class});
            $sth->finish;
            ($form->{entity_id}) = 
		$dbh->selectrow_array("SELECT currval('entity_id_seq')");
            # Creating LOCATION
            $query = qq|
		INSERT INTO location
			(line_one, line_two, city_province, mail_code, 
			country_id)
		VALUES
			(?, ?, ?, ?, 
				(SELECT id FROM country
				WHERE short_name = ? OR name = ?))
            |;
            $sth = $dbh->prepare($query);
            $sth->execute($form->{address1}, $form->{address2}, 
                  "$form->{city}, $form->{state}", $form->{zipcode},
                  $form->{country}, $form->{country}
            ) || $form->dberror($query);
            
            ($form->{location_id}) = 
		$dbh->selectrow_array("SELECT currval('location_id_seq')");
            #Creating company
            $query = qq|
		INSERT INTO company
			(entity_id, entity_class_id, legal_name, 
			primary_location_id, tax_id)
		VALUES
			(?, ?, ?, ?, ?)
            |;
            $sth = $dbh->prepare($query) || $form->dberror($query);
            $sth->execute($form->{entity_id}, $form->{entity_class}, 
                  $form->{name}, 
                  $form->{location_id}, $form->{taxnumber});
            #Creating customer record
            $query = qq|
		INSERT INTO customer 
			(entity_id, discount, taxincluded, creditlimit, terms,
				customernumber, cc, bcc, business_id, sic_code,
				language_code, pricegroup_id, curr, startdate,
				enddate, invoice_notes, bic, iban)
					
		VALUES (?, ?, ?, ?, ?,
			?, ?, ?, ?, ?,
			?, ?, ?, ?,
			?, ?, ?, ?)|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                 $form->{entity_id}, $form->{discount}, $form->{taxincluded},
                 $form->{creditlimit}, 
                 $form->{terms}, $form->{"$form->{vc}number"}, $form->{cc},
                 $form->{bcc}, $form->{business_id}, $form->{sic_code},
                 $form->{language_code}, $form->{pricegroup_id}, $form->{curr},
                 $form->{startdate} || undef, $form->{enddate} || undef, 
                 $form->{invoice_notes},
                 $form->{bic}, $form->{iban}
            ) || $form->dberror($query);
    }
}

sub save_customer {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};
    my $query;
    my $sth;
    my $null;

    # remove double spaces
    $form->{name} =~ s/  / /g;

    # remove double minus and minus at the end
    $form->{name} =~ s/--+/-/g;
    $form->{name} =~ s/-+$//;

    # assign value discount, terms, creditlimit
    $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
    $form->{discount} /= 100;
    $form->{terms}       *= 1;
    $form->{taxincluded} *= 1;
    $form->{creditlimit} =
      $form->parse_amount( $myconfig, $form->{creditlimit} );
    if ( !$form->{creditlimit} ) {
        $form->{creditlimit} = 0;
    }
    &_save_vc($form);
    # save taxes
    foreach $item ( split / /, $form->{taxaccounts} ) {

        if ( $form->{"tax_$item"} ) {
            $query = qq|
				INSERT INTO customertax (customer_id, chart_id)
				     VALUES (?, (SELECT id
				                   FROM chart
				                  WHERE accno = ?))|;

            $sth = $dbh->prepare($query);
            $sth->execute( $form->{id}, $item )
              || $form->dberror($query);
        }
    }

    # add shipto
    $form->add_shipto( $dbh, $form->{id} );

    $dbh->commit;
}

sub save_vendor {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $query;
    my $sth;
    my $null;

    # remove double spaces
    $form->{name} =~ s/  / /g;

    # remove double minus and minus at the end
    $form->{name} =~ s/--+/-/g;
    $form->{name} =~ s/-+$//;

    $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} );
    $form->{discount} /= 100;
    $form->{terms}       *= 1;
    $form->{taxincluded} *= 1;
    $form->{creditlimit} =
      $form->parse_amount( $myconfig, $form->{creditlimit} );
    &_save_vc($form);

    # save taxes
    foreach $item ( split / /, $form->{taxaccounts} ) {
        if ( $form->{"tax_$item"} ) {
            $query = qq|
				INSERT INTO vendortax (vendor_id, chart_id)
				     VALUES (?, (SELECT id
				                   FROM chart
				                  WHERE accno = ?))|;

            $sth = $dbh->prepare($query);
            $sth->execute( $form->{id}, $item )
              || $form->dberror($query);
        }
    }

    # add shipto
    $form->add_shipto( $dbh, $form->{id} );

    $dbh->commit;

}

sub delete {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    # delete customer/vendor
    my $query = qq|DELETE FROM $form->{db}
					WHERE id = ?|;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    $dbh->commit;

}

sub search {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $where = "1 = 1";
    $form->{sort} = ( $form->{sort} ) ? $form->{sort} : "name";
    my @a         = qw(name);
    my $sortorder = $form->sort_order( \@a );

    my $var;
    my $item;

    @a = ("$form->{db}number");
    push @a, qw(name contact city state zipcode country notes phone email);

    if ( $form->{employee} ) {
        $var = $form->like( lc $form->{employee} );
        $where .= " AND lower(e.name) LIKE '$var'";
    }

    foreach $item (@a) {

        if ( $form->{$item} ne "" ) {
            $var = $form->like( lc $form->{$item} );
            $where .= " AND lower(ct.$item) LIKE '$var'";
        }
    }

    if ( $form->{address} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{address} ) );
        $where .=
" AND (lower(ct.address1) ILIKE $var)";
    }

    if ( $form->{startdatefrom} ) {
        $where .=
          " AND ct.startdate >= " . $dbh->quote( $form->{startdatefrom} );
    }

    if ( $form->{startdateto} ) {
        $where .= " AND ct.startdate <= " . $dbh->quote( $form->{startdateto} );
    }

    if ( $form->{status} eq 'active' ) {
        $where .= " AND ct.enddate IS NULL";
    }

    if ( $form->{status} eq 'inactive' ) {
        $where .= " AND ct.enddate <= current_date";
    }

    if ( $form->{status} eq 'orphaned' ) {
        $where .= qq| 
			AND ct.id NOT IN (SELECT o.$form->{db}_id
			                    FROM oe o, $form->{db} vc
			                   WHERE vc.id = o.$form->{db}_id)|;

        if ( $form->{db} =~ /(^customer$|^vendor$)/ ) {
            $where .= qq| AND ct.id NOT IN (SELECT a.entity_id
											  FROM ar a, customer vc
											 WHERE vc.entity_id = a.entity_id)|;
        }


        $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
    }

    my $query = qq|
		   SELECT ct.*, b.description AS business,
		          e.name AS employee, g.pricegroup, 
		          l.description AS language, m.name AS manager
		     FROM $form->{db} ct
		LEFT JOIN business b ON (ct.business_id = b.id)
		LEFT JOIN employee e ON (ct.employee_id = e.id)
		LEFT JOIN employee m ON (m.id = e.managerid)
		LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
		LEFT JOIN language l ON (l.code = ct.language_code)
		    WHERE $where|;

    # redo for invoices, orders and quotations
    if (   $form->{l_transnumber}
        || $form->{l_invnumber}
        || $form->{l_ordnumber}
        || $form->{l_quonumber} )
    {

        my ( $ar, $union, $module );
        $query = "";
        my $transwhere;
        my $openarap = "";
        my $openoe   = "";

        if ( $form->{open} || $form->{closed} ) {
            unless ( $form->{open} && $form->{closed} ) {
                $openarap = " AND a.amount != a.paid"
                  if $form->{open};
                $openarap = " AND a.amount = a.paid"
                  if $form->{closed};
                $openoe = " AND o.closed = '0'"
                  if $form->{open};
                $openoe = " AND o.closed = '1'"
                  if $form->{closed};
            }
        }

        if ( $form->{l_transnumber} ) {

            $ar = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
            $module = $ar;

            $transwhere = "";
            $transwhere .=
              " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
              if $form->{transdatefrom};
            $transwhere .=
              " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
              if $form->{transdateto};

            $query = qq|
				    SELECT ct.*, b.description AS business,
				           a.invnumber, a.ordnumber, 
				           a.quonumber, 
				           a.id AS invid, '$ar' AS module, 
				           'invoice' AS formtype, 
				           (a.amount = a.paid) AS closed, 
				           a.amount,
				           a.netamount, e.name AS employee, 
				           m.name AS manager
				      FROM $form->{db} ct
				      JOIN $ar a ON (a.$form->{db}_id = ct.id)
				 LEFT JOIN business b ON (ct.business_id = b.id)
				 LEFT JOIN employee e ON (a.employee_id = e.id)
				 LEFT JOIN employee m ON (m.id = e.managerid)
				     WHERE $where
				           AND a.invoice = '0'
				           $transwhere
				           $openarap |;

            $union = qq| UNION |;

        }

        if ( $form->{l_invnumber} ) {
            $ar     = ( $form->{db} eq 'customer' ) ? 'ar' : 'ap';
            $module = ( $ar         eq 'ar' )       ? 'is' : 'ir';

            $transwhere = "";
            $transwhere .=
              " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
              if $form->{transdatefrom};
            $transwhere .=
              " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
              if $form->{transdateto};

            $query .= qq|
				$union
				   SELECT ct.*, b.description AS business,
				          a.invnumber, a.ordnumber, a.quonumber,
				          a.id AS invid,
				          '$module' AS module, 
				          'invoice' AS formtype,
				          (a.amount = a.paid) AS closed, 
				          a.amount, a.netamount,
				          e.name AS employee, m.name AS manager
				     FROM $form->{db} ct
				     JOIN $ar a ON (a.$form->{db}_id = ct.id)
				LEFT JOIN business b ON (ct.business_id = b.id)
				LEFT JOIN employee e ON (a.employee_id = e.id)
				LEFT JOIN employee m ON (m.id = e.managerid)
				    WHERE $where
				          AND a.invoice = '1'
				          $transwhere
				          $openarap |;

            $union = qq| UNION|;

        }

        if ( $form->{l_ordnumber} ) {

            $transwhere = "";
            $transwhere .=
              " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
              if $form->{transdatefrom};
            $transwhere .=
              " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
              if $form->{transdateto};

            $query .= qq|
				$union
				   SELECT ct.*, b.description AS business,
				          ' ' AS invnumber, o.ordnumber, 
				          o.quonumber, o.id AS invid,
				          'oe' AS module, 'order' AS formtype,
				          o.closed, o.amount, o.netamount,
				          e.name AS employee, m.name AS manager
				     FROM $form->{db} ct
				     JOIN oe o ON (o.$form->{db}_id = ct.id)
				LEFT JOIN business b ON (ct.business_id = b.id)
				LEFT JOIN employee e ON (o.employee_id = e.id)
				LEFT JOIN employee m ON (m.id = e.managerid)
				    WHERE $where
				      AND o.quotation = '0'
				          $transwhere
				          $openoe |;

            $union = qq| UNION|;

        }

        if ( $form->{l_quonumber} ) {

            $transwhere = "";
            $transwhere .=
              " AND o.transdate >= " . $dbh->quote( $form->{transdatefrom} )
              if $form->{transdatefrom};
            $transwhere .=
              " AND o.transdate <= " . $dbh->quote( $form->{transdateto} )
              if $form->{transdateto};

            $query .= qq|
				$union
				   SELECT ct.*, b.description AS business,
				          ' ' AS invnumber, o.ordnumber, 
				          o.quonumber, o.id AS invid,
				          'oe' AS module, 
				          'quotation' AS formtype,
				          o.closed, o.amount, o.netamount,
				          e.name AS employee, m.name AS manager
				     FROM $form->{db} ct
				     JOIN oe o ON (o.$form->{db}_id = ct.id)
				LEFT JOIN business b ON (ct.business_id = b.id)
				LEFT JOIN employee e ON (o.employee_id = e.id)
				LEFT JOIN employee m ON (m.id = e.managerid)
				    WHERE $where
				          AND o.quotation = '1'
				          $transwhere
				          $openoe |;

        }

        $sortorder .= ", invid";
    }

    $query .= qq| ORDER BY $sortorder|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    # accounts
    $query = qq|
		SELECT c.accno
		  FROM chart c
		  JOIN $form->{db}tax t ON (t.chart_id = c.id)
		 WHERE t.$form->{db}_id = ?|;

    my $tth = $dbh->prepare($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $tth->execute( $ref->{id} );

        while ( ($item) = $tth->fetchrow_array ) {
            $ref->{taxaccount} .= "$item ";
        }

        $tth->finish;
        chop $ref->{taxaccount};

        $ref->{address} = "";

        for (qw(address1 address2 city state zipcode country)) {
            $ref->{address} .= "$ref->{$_} ";
        }
        push @{ $form->{CT} }, $ref;
    }

    $sth->finish;
    $dbh->commit;

}

sub get_history {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $query;
    my $where = "1 = 1";
    $form->{sort} = "partnumber" unless $form->{sort};
    my $sortorder = $form->{sort};
    my %ordinal   = ();
    my $var;
    my $table;

    # setup ASC or DESC
    $form->sort_order();

    if ( $form->{"$form->{db}number"} ne "" ) {
        $var = $dbh->( $form->like( lc $form->{"$form->{db}number"} ) );
        $where .= " AND lower(ct.$form->{db}number) LIKE $var";
    }

    if ( $form->{address} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{address} ) );
        $where .= " AND lower(ct.address1) ILIKE $var";
    }

    for (qw(name contact email phone notes city state zipcode country)) {

        if ( $form->{$_} ne "" ) {
            $var = $dbh->quote( $form->like( lc $form->{$_} ) );
            $where .= " AND lower(ct.$_) LIKE $var";
        }
    }

    if ( $form->{employee} ne "" ) {
        $var = $form->like( lc $form->{employee} );
        $where .= " AND lower(e.name) LIKE '$var'";
    }

    $transwhere .=
      " AND a.transdate >= " . $dbh->quote( $form->{transdatefrom} )
      if $form->{transdatefrom};
    $transwhere .= " AND a.transdate <= " . $dbh->quote( $form->{transdateto} )
      if $form->{transdateto};

    if ( $form->{open} || $form->{closed} ) {

        unless ( $form->{open} && $form->{closed} ) {

            if ( $form->{type} eq 'invoice' ) {
                $where .= " AND a.amount != a.paid"
                  if $form->{open};
                $where .= " AND a.amount = a.paid"
                  if $form->{closed};
            }
            else {
                $where .= " AND a.closed = '0'"
                  if $form->{open};
                $where .= " AND a.closed = '1'"
                  if $form->{closed};
            }
        }
    }

    my $invnumber = 'invnumber';
    my $deldate   = 'deliverydate';
    my $buysell;
    my $sellprice = "sellprice";

    if ( $form->{db} eq 'customer' ) {
        $buysell = "buy";

        if ( $form->{type} eq 'invoice' ) {
            $where .= qq|
				AND a.invoice = '1' AND i.assemblyitem = '0'|;
            $table     = 'ar';
            $sellprice = "fxsellprice";
        }
        else {
            $table = 'oe';

            if ( $form->{type} eq 'order' ) {
                $invnumber = 'ordnumber';
                $where .= qq| AND a.quotation = '0'|;
            }
            else {
                $invnumber = 'quonumber';
                $where .= qq| AND a.quotation = '1'|;
            }

            $deldate = 'reqdate';
        }
    }

    if ( $form->{db} eq 'vendor' ) {

        $buysell = "sell";

        if ( $form->{type} eq 'invoice' ) {

            $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
            $table     = 'ap';
            $sellprice = "fxsellprice";

        }
        else {

            $table = 'oe';

            if ( $form->{type} eq 'order' ) {
                $invnumber = 'ordnumber';
                $where .= qq| AND a.quotation = '0'|;
            }
            else {
                $invnumber = 'quonumber';
                $where .= qq| AND a.quotation = '1'|;
            }

            $deldate = 'reqdate';
        }
    }

    my $invjoin = qq| JOIN invoice i ON (i.trans_id = a.id)|;

    if ( $form->{type} eq 'order' ) {
        $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
    }

    if ( $form->{type} eq 'quotation' ) {
        $invjoin = qq| JOIN orderitems i ON (i.trans_id = a.id)|;
        $where .= qq| AND a.quotation = '1'|;
    }

    %ordinal = (
        partnumber    => 9,
        description   => 12,
        "$deldate"    => 16,
        serialnumber  => 17,
        projectnumber => 18
    );

    $sortorder =
      "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";

    $query = qq|
		  SELECT ct.id AS ctid, ct.name, ct.address1,
		         ct.address2, ct.city, ct.state,
		         p.id AS pid, p.partnumber, a.id AS invid,
		         a.$invnumber, a.curr, i.description,
		         i.qty, i.$sellprice AS sellprice, i.discount,
		         i.$deldate, i.serialnumber, pr.projectnumber,
		         e.name AS employee, ct.zipcode, ct.country, i.unit,
		         (SELECT $buysell 
			    FROM exchangerate ex
			   WHERE a.curr = ex.curr
		                 AND a.transdate = ex.transdate) AS exchangerate
		     FROM $form->{db} ct
		     JOIN $table a ON (a.$form->{db}_id = ct.id)
		          $invjoin
		     JOIN parts p ON (p.id = i.parts_id)
		LEFT JOIN project pr ON (pr.id = i.project_id)
		LEFT JOIN employee e ON (e.id = a.employee_id)
				 WHERE $where
			  ORDER BY $sortorder|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $ref->{address} = "";
        $ref->{exchangerate} ||= 1;
        for (qw(address1 address2 city state zipcode country)) {
            $ref->{address} .= "$ref->{$_} ";
        }
        $ref->{id} = $ref->{ctid};
        push @{ $form->{CT} }, $ref;
    }

    $sth->finish;
    $dbh->commit;

}

sub pricelist {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $query;

    if ( $form->{db} eq 'customer' ) {
        $query = qq|SELECT p.id, p.partnumber, p.description,
						   p.sellprice, pg.partsgroup, p.partsgroup_id,
						   m.pricebreak, m.sellprice,
						   m.validfrom, m.validto, m.curr
					  FROM partscustomer m
					  JOIN parts p ON (p.id = m.parts_id)
				 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
					 WHERE m.customer_id = ?
				  ORDER BY partnumber|;
    }

    if ( $form->{db} eq 'vendor' ) {
        $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
						   pg.partsgroup, p.partsgroup_id,
						   m.partnumber, m.leadtime, m.lastcost, m.curr
					  FROM partsvendor m
					  JOIN parts p ON (p.id = m.parts_id)
				 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
					 WHERE m.vendor_id = ?
				  ORDER BY p.partnumber|;
    }

    my $sth;
    my $ref;

    if ( $form->{id} ) {

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} ) || $form->dberror($query);

        while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            push @{ $form->{all_partspricelist} }, $ref;
        }

        $sth->finish;
    }

    $query = qq|SELECT value FROM defaults where setting_key = 'curr'|;
    ( $form->{currencies} ) = $dbh->selectrow_array($query);

    $query = qq|SELECT id, partsgroup 
				  FROM partsgroup
			  ORDER BY partsgroup|;

    $sth = $dbh->prepare($query);
    $sth->execute || $self->dberror($query);

    $form->{all_partsgroup} = ();

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{all_partsgroup} }, $ref;
    }

    $sth->finish;

    $dbh->commit;

}

sub save_pricelist {

    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

    my $query = qq|
		DELETE FROM parts$form->{db}
		 WHERE $form->{db}_id = ?}|;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

    foreach $i ( 1 .. $form->{rowcount} ) {

        if ( $form->{"id_$i"} ) {

            if ( $form->{db} eq 'customer' ) {

                for (qw(pricebreak sellprice)) {
                    $form->{"${_}_$i"} =
                      $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
                }

                $query = qq|
					INSERT INTO parts$form->{db} 
					            (parts_id, customer_id,
					            pricebreak, sellprice, 
					            validfrom, validto, curr)
					     VALUES (?, ?, ?, ?, ?, ?, ?)|;
                @queryargs = (
                    $form->{"id_$i"},         $form->{id},
                    $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},
                    $form->{"validfrom_$i"},  $form->{"validto_$i"},
                    $form->{"curr_$i"}
                );
            }
            else {

                for (qw(leadtime lastcost)) {
                    $form->{"${_}_$i"} =
                      $form->parse_amount( $myconfig, $form->{"${_}_$i"} );
                }

                $query = qq|
					INSERT INTO parts$form->{db} 
					            (parts_id, vendor_id,
					            partnumber, lastcost, 
					            leadtime, curr)
					     VALUES (?, ?, ?, ?, ?, ?)|;
                @queryargs = (
                    $form->{"id_$i"},         $form->{id},
                    $form->{"partnumber_$i"}, $form->{"lastcost_$i"},
                    $form->{"leadtime_$i"},   $form->{"curr_$i"}
                );

            }
            $sth = $dbh->prepare($query);
            $sth->execute(@queryargs) || $form->dberror($query);
        }

    }

    $_ = $dbh->commit;

}

sub retrieve_item {

    my ( $self, $myconfig, $form ) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my $i = $form->{rowcount};
    my $var;
    my $null;

    my $where = "WHERE p.obsolete = '0'";

    if ( $form->{db} eq 'vendor' ) {

        # parts, services, labor
        $where .= " AND p.assembly = '0'";
    }

    if ( $form->{db} eq 'customer' ) {

        # parts, assemblies, services
        $where .= " AND p.income_accno_id > 0";
    }

    if ( $form->{"partnumber_$i"} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{"partnumber_$i"} ) );
        $where .= " AND lower(p.partnumber) LIKE $var";
    }

    if ( $form->{"description_$i"} ne "" ) {
        $var = $dbh->quote( $form->like( lc $form->{"description_$i"} ) );
        $where .= " AND lower(p.description) LIKE $var";
    }

    if ( $form->{"partsgroup_$i"} ne "" ) {
        ( $null, $var ) = split /--/, $form->{"partsgroup_$i"};
        $var = $dbh->quote($var);
        $where .= qq| AND p.partsgroup_id = $var|;
    }

    my $query = qq|
		   SELECT p.id, p.partnumber, p.description, p.sellprice,
		          p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
		     FROM parts p
		LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
		          $where
		 ORDER BY partnumber|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
    my $ref;
    $form->{item_list} = ();

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{item_list} }, $ref;
    }

    $sth->finish;
    $dbh->commit;
}

1;