#=====================================================================
# SQL-Ledger Accounting
# Copyright (C) 2000
#
#  Author: Dieter Simader
#   Email: dsimader@sql-ledger.org
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
# 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., 675 Mass Ave, Cambridge, MA 02139, USA.
#======================================================================
#
# backend code for customers and vendors
#
#======================================================================

package CT;


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

  my $dbh = $form->dbconnect($myconfig);
  my $query;
  my $sth;
  my $ref;

  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 = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);
  
    $ref = $sth->fetchrow_hashref(NAME_lc);
  
    map { $form->{$_} = $ref->{$_} } keys %$ref;

    $sth->finish;

    # check if it is orphaned
    my $arap = ($form->{db} eq 'customer') ? "ar" : "ap";
    $query = qq|SELECT a.id
              FROM $arap a
	      JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
	      WHERE ct.id = $form->{id}
	    UNION
	      SELECT a.id
	      FROM oe a
	      JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
	      WHERE ct.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $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 = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $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 FROM defaults|;
    ($form->{startdate}) = $dbh->selectrow_array($query);

  }

  # get tax labels
  $query = qq|SELECT c.accno, c.description
              FROM chart c
	      JOIN tax t ON (t.chart_id = c.id)
	      WHERE c.link LIKE '%CT_tax%'
	      ORDER BY c.accno|;
  $sth = $dbh->prepare($query);
  $sth->execute || $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
  $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;
  
  # this is for the salesperson
  $query = qq|SELECT id, name
              FROM employee
	      WHERE sales = '1'
	      ORDER BY 2|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_employee} }, $ref;
  }
  $sth->finish;

  # get language
  $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
  $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 curr AS currencies
              FROM defaults|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  ($form->{currencies}) = $sth->fetchrow_array;
  $sth->finish;

  $dbh->disconnect;

}


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

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);
  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->{id}) {
    $query = qq|DELETE FROM customertax
                WHERE customer_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);

    $query = qq|DELETE FROM shipto
                WHERE trans_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);

    # retrieve enddate
    if ($form->{type} && $form->{enddate}) {
      my $now;
      $query = qq|SELECT enddate, current_date AS now FROM customer|;
      ($form->{enddate}, $now) = $dbh->selectrow_array($query);
      $form->{enddate} = $now if $form->{enddate} lt $now;
    }

  } else {
    my $uid = time;
    $uid .= $form->{login};

    $query = qq|INSERT INTO customer (name)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);
    
    $query = qq|SELECT id FROM customer
                WHERE name = '$uid'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    ($form->{id}) = $sth->fetchrow_array;
    $sth->finish;

  }

  my $employee_id;
  ($null, $employee_id) = split /--/, $form->{employee};
  $employee_id *= 1;
  
  my $pricegroup_id;
  ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
  $pricegroup_id *= 1;

  my $business_id;
  ($null, $business_id) = split /--/, $form->{business};
  $business_id *= 1;

  my $language_code;
  ($null, $language_code) = split /--/, $form->{language};

  $form->{customernumber} = $form->update_defaults($myconfig, "customernumber", $dbh) if ! $form->{customernumber};
  
  $query = qq|UPDATE customer SET
              customernumber = |.$dbh->quote($form->{customernumber}).qq|,
	      name = |.$dbh->quote($form->{name}).qq|,
	      address1 = |.$dbh->quote($form->{address1}).qq|,
	      address2 = |.$dbh->quote($form->{address2}).qq|,
	      city = |.$dbh->quote($form->{city}).qq|,
	      state = |.$dbh->quote($form->{state}).qq|,
	      zipcode = |.$dbh->quote($form->{zipcode}).qq|,
	      country = |.$dbh->quote($form->{country}).qq|,
	      contact = |.$dbh->quote($form->{contact}).qq|,
	      phone = '$form->{phone}',
	      fax = '$form->{fax}',
	      email = '$form->{email}',
	      cc = '$form->{cc}',
	      bcc = '$form->{bcc}',
	      notes = |.$dbh->quote($form->{notes}).qq|,
	      discount = $form->{discount},
	      creditlimit = $form->{creditlimit},
	      terms = $form->{terms},
	      taxincluded = '$form->{taxincluded}',
	      business_id = $business_id,
	      taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
	      sic_code = '$form->{sic}',
	      iban = '$form->{iban}',
	      bic = '$form->{bic}',
	      employee_id = $employee_id,
	      pricegroup_id = $pricegroup_id,
	      language_code = '$language_code',
	      curr = '$form->{curr}',
	      startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
	      enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|
	      WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  # save taxes
  foreach $item (split / /, $form->{taxaccounts}) {
    if ($form->{"tax_$item"}) {
      $query = qq|INSERT INTO customertax (customer_id, chart_id)
		  VALUES ($form->{id}, (SELECT id
				        FROM chart
				        WHERE accno = '$item'))|;
      $dbh->do($query) || $form->dberror($query);
    }
  }
  
  # add shipto
  $form->add_shipto($dbh, $form->{id});

  $dbh->commit;
  $dbh->disconnect;

}


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

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);

  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});
 
  
  if ($form->{id}) {
    $query = qq|DELETE FROM vendortax
                WHERE vendor_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);

    $query = qq|DELETE FROM shipto
                WHERE trans_id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);
  } else {
    my $uid = time;
    $uid .= $form->{login};
    
    $query = qq|INSERT INTO vendor (name)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);
   
    $query = qq|SELECT id FROM vendor
                WHERE name = '$uid'|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    ($form->{id}) = $sth->fetchrow_array;
    $sth->finish;

  }
   
  my $employee_id;
  ($null, $employee_id) = split /--/, $form->{employee};
  $employee_id *= 1;
  
  my $pricegroup_id;
  ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
  $pricegroup_id *= 1;

  my $business_id;
  ($null, $business_id) = split /--/, $form->{business};
  $business_id *= 1;

  my $language_code;
  ($null, $language_code) = split /--/, $form->{language};
 
  $form->{vendornumber} = $form->update_defaults($myconfig, "vendornumber", $dbh) if ! $form->{vendornumber};
  
  $query = qq|UPDATE vendor SET
              vendornumber = |.$dbh->quote($form->{vendornumber}).qq|,
	      name = |.$dbh->quote($form->{name}).qq|,
	      address1 = |.$dbh->quote($form->{address1}).qq|,
	      address2 = |.$dbh->quote($form->{address2}).qq|,
	      city = |.$dbh->quote($form->{city}).qq|,
	      state = |.$dbh->quote($form->{state}).qq|,
	      zipcode = |.$dbh->quote($form->{zipcode}).qq|,
	      country = |.$dbh->quote($form->{country}).qq|,
	      contact = |.$dbh->quote($form->{contact}).qq|,
	      phone = '$form->{phone}',
	      fax = '$form->{fax}',
	      email = '$form->{email}',
	      cc = '$form->{cc}',
	      bcc = '$form->{bcc}',
	      notes = |.$dbh->quote($form->{notes}).qq|,
	      terms = $form->{terms},
	      discount = $form->{discount},
	      creditlimit = $form->{creditlimit},
	      taxincluded = '$form->{taxincluded}',
	      gifi_accno = '$form->{gifi_accno}',
	      business_id = $business_id,
	      taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
	      sic_code = '$form->{sic}',
	      iban = '$form->{iban}',
	      bic = '$form->{bic}',
	      employee_id = $employee_id,
	      language_code = '$language_code',
	      pricegroup_id = $pricegroup_id,
	      curr = '$form->{curr}',
	      startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
	      enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|
	      WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

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

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

  $dbh->commit;
  $dbh->disconnect;

}



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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  # delete customer/vendor
  my $query = qq|DELETE FROM $form->{db}
	         WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  $dbh->disconnect;

}


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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  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 email);
  
  foreach $item (@a) {
    if ($form->{$item}) {
      $var = $form->like(lc $form->{$item});
      $where .= " AND lower(ct.$item) LIKE '$var'";
    }
  }
  if ($form->{address}) {
    $var = $form->like(lc $form->{address});
    $where .= " AND (lower(ct.address1) LIKE '$var' OR lower(ct.address2) LIKE '$var')";
  }

  if ($form->{status} eq 'orphaned') {
    $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id
                                    FROM oe o, $form->{db} cv
		 	            WHERE cv.id = o.$form->{db}_id)|;
    if ($form->{db} eq 'customer') {
      $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
                                      FROM ar a, customer cv
				      WHERE cv.id = a.customer_id)|;
    }
    if ($form->{db} eq 'vendor') {
      $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
                                      FROM ap a, vendor cv
				      WHERE cv.id = a.vendor_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 >= '$form->{transdatefrom}'" if $form->{transdatefrom};
      $transwhere .= " AND a.transdate <= '$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
		  FROM $form->{db} ct
		JOIN $ar a ON (a.$form->{db}_id = ct.id)
	        LEFT JOIN business b ON (ct.business_id = b.id)
		  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 >= '$form->{transdatefrom}'" if $form->{transdatefrom};
      $transwhere .= " AND a.transdate <= '$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
		  FROM $form->{db} ct
		JOIN $ar a ON (a.$form->{db}_id = ct.id)
	        LEFT JOIN business b ON (ct.business_id = b.id)
		  WHERE $where
		  AND a.invoice = '1'
		  $transwhere
		  $openarap
		  |;
  
      $union = qq|
              UNION|;
      
    }
    
    if ($form->{l_ordnumber}) {
      
      $transwhere = "";
      $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
      $transwhere .= " AND o.transdate <= '$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
		  FROM $form->{db} ct
		JOIN oe o ON (o.$form->{db}_id = ct.id)
	        LEFT JOIN business b ON (ct.business_id = b.id)
		  WHERE $where
		  AND o.quotation = '0'
		  $transwhere
		  $openoe
		  |;
  
      $union = qq|
              UNION|;

    }

    if ($form->{l_quonumber}) {

      $transwhere = "";
      $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
      $transwhere .= " AND o.transdate <= '$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
		  FROM $form->{db} ct
		JOIN oe o ON (o.$form->{db}_id = ct.id)
	        LEFT JOIN business b ON (ct.business_id = b.id)
		  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);

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

  $sth->finish;
  $dbh->disconnect;

}


sub get_history {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  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"}) {
    $var = $form->like(lc $form->{"$form->{db}number"});
    $where .= " AND lower(ct.$form->{db}number) LIKE '$var'";
  }
  if ($form->{name}) {
    $var = $form->like(lc $form->{name});
    $where .= " AND lower(ct.name) LIKE '$var'";
  }
  if ($form->{address}) {
    $var = $form->like(lc $form->{address});
    $where .= " AND lower(ct.address1) LIKE '$var'";
  }
  if ($form->{city}) {
    $var = $form->like(lc $form->{city});
    $where .= " AND lower(ct.city) LIKE '$var'";
  }
  if ($form->{state}) {
    $var = $form->like(lc $form->{state});
    $where .= " AND lower(ct.state) LIKE '$var'";
  }
  if ($form->{zipcode}) {
    $var = $form->like(lc $form->{zipcode});
    $where .= " AND lower(ct.zipcode) LIKE '$var'";
  }
  if ($form->{country}) {
    $var = $form->like(lc $form->{country});
    $where .= " AND lower(ct.country) LIKE '$var'";
  }
  if ($form->{contact}) {
    $var = $form->like(lc $form->{contact});
    $where .= " AND lower(ct.contact) LIKE '$var'";
  }
  if ($form->{notes}) {
    $var = $form->like(lc $form->{notes});
    $where .= " AND lower(ct.notes) LIKE '$var'";
  }
  if ($form->{email}) {
    $var = $form->like(lc $form->{email});
    $where .= " AND lower(ct.email) LIKE '$var'";
  }

  $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
  $where .= " AND a.transdate <= '$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;
  
  if ($form->{db} eq 'customer') {
    $buysell = "buy";
    if ($form->{type} eq 'invoice') {
      $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
      $table = 'ar';
    } 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';
    } 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'|;
  }


  if ($form->{history} eq 'summary') {
    $query = qq|SELECT curr FROM defaults|;
    my ($curr) = $dbh->selectrow_array($query);
    $curr =~ s/:.*//;
    
    %ordinal = ( partnumber	=> 8,
                 description	=> 9
	       );
    $sortorder = "2 $form->{direction}, 1, $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, i.description, p.unit,
		sum(i.qty) AS qty, sum(i.sellprice) AS sellprice,
		'$curr' AS curr,
		ct.zipcode, ct.country
		FROM $form->{db} ct
		JOIN $table a ON (a.$form->{db}_id = ct.id)
		$invjoin
		JOIN parts p ON (p.id = i.parts_id)
		WHERE $where
		GROUP BY ct.id, ct.name, ct.address1, ct.address2, ct.city,
		ct.state, ct.zipcode, ct.country,
		p.id, p.partnumber, i.description, p.unit
		ORDER BY $sortorder|;
  } else {
    %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, i.discount,
		i.$deldate, i.serialnumber, pr.projectnumber,
		e.name AS employee, ct.zipcode, ct.country, i.unit|;
    $query .= qq|, i.fxsellprice| if $form->{type} eq 'invoice';

    if ($form->{type} ne 'invoice') {
      if ($form->{l_curr}) {
	$query .= qq|, (SELECT $buysell FROM exchangerate ex
	                WHERE a.curr = ex.curr
			AND a.transdate = ex.transdate) AS exchangerate|;
      }
    }
	
    $query .= qq|
                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 unless $ref->{exchangerate};
    map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
    $ref->{id} = $ref->{ctid};
    push @{ $form->{CT} }, $ref;
  }

  $sth->finish;
  $dbh->disconnect;

}


sub pricelist {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  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 = $form->{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 = $form->{id}
		ORDER BY p.partnumber|;
  }

  my $sth;
  my $ref;

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

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

  $query = qq|SELECT curr FROM defaults|;
  ($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->disconnect;

}


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

  my $dbh = $form->dbconnect_noauto($myconfig);
  
  my $query = qq|DELETE FROM parts$form->{db}
                 WHERE $form->{db}_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

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

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

      if ($form->{db} eq 'customer') {
	map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(pricebreak sellprice);
	
	$query = qq|INSERT INTO parts$form->{db} (parts_id, customer_id,
		    pricebreak, sellprice, validfrom, validto, curr)
		    VALUES ($form->{"id_$i"}, $form->{id},
		    $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},|
		    .$form->dbquote($form->{"validfrom_$i"}, SQL_DATE) .qq|,|
		    .$form->dbquote($form->{"validto_$i"}, SQL_DATE) .qq|,
		    '$form->{"curr_$i"}')|;
      } else {
	map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(leadtime lastcost);
	
	$query = qq|INSERT INTO parts$form->{db} (parts_id, vendor_id,
		    partnumber, lastcost, leadtime, curr)
		    VALUES ($form->{"id_$i"}, $form->{id},
		    '$form->{"partnumber_$i"}', $form->{"lastcost_$i"},
		    $form->{"leadtime_$i"}, '$form->{"curr_$i"}')|;

      }
      $dbh->do($query) || $form->dberror($query);
    }

  }

  $_ = $dbh->commit;
  $dbh->disconnect;

}



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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

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

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

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

  if ($form->{"partsgroup_$i"}) {
    ($null, $var) = split /--/, $form->{"partsgroup_$i"};
    $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
		 |;
  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->disconnect;

}


1;