Initial Import

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 1080 insertions, 0 deletions

diff --git a/LedgerSMB/CT.pm b/LedgerSMB/CT.pm
new file mode 100755
index 00000000..a99c7292
--- /dev/null
+++ b/LedgerSMB/CT.pm
@@ -0,0 +1,1080 @@
+#=====================================================================
+# LedgerSMB 
+# Small Medium Business Accounting software
+# 
+# See COPYRIGHT file for copyright information
+#======================================================================
+#
+# This file has undergone whitespace cleanup.
+#
+#======================================================================
+#
+# 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;
+	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 = $form->{id}|;
+
+		$sth = $dbh->prepare($query);
+		$sth->execute || $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 = $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 DISTINCT c.accno, c.description
+				  FROM chart c
+				  JOIN tax t ON (t.chart_id = c.id)
+				 WHERE c.link LIKE '%${ARAP}_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 ## 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 curr AS currencies
+				  FROM defaults|;
+
+	($form->{currencies}) = $dbh->selectrow_array($query);
+
+	$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);
+
+		$query = qq|SELECT id 
+					  FROM customer
+					 WHERE id = $form->{id}|;
+
+		if (! $dbh->selectrow_array($query)) {
+			$query = qq|INSERT INTO customer (id)
+						VALUES ($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 = localtime;
+		$uid .= "$$";
+
+		$query = qq|INSERT INTO customer (name)
+					VALUES ('$uid')|;
+
+		$dbh->do($query) || $form->dberror($query);
+
+		$query = qq|SELECT id 
+					  FROM customer
+					 WHERE name = '$uid'|;
+
+		($form->{id}) = $dbh->selectrow_array($query);
+
+	}
+
+	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_code}',
+					   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);
+
+		$query = qq|SELECT id 
+					  FROM vendor
+					 WHERE id = $form->{id}|;
+
+		if (! $dbh->selectrow_array($query)) {
+			$query = qq|INSERT INTO vendor (id)
+						VALUES ($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 vendor|;
+			($form->{enddate}, $now) = $dbh->selectrow_array($query);
+			$form->{enddate} = $now if $form->{enddate} lt $now;
+		}
+
+	} else {
+		my $uid = localtime;
+		$uid .= "$$";
+
+		$query = qq|INSERT INTO vendor (name)
+					VALUES ('$uid')|;
+
+		$dbh->do($query) || $form->dberror($query);
+
+		$query = qq|SELECT id 
+					  FROM vendor
+					 WHERE name = '$uid'|;
+
+		($form->{id}) = $dbh->selectrow_array($query);
+
+	}
+
+	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_code}',
+					   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 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 = $form->like(lc $form->{address});
+		$where .= " AND (lower(ct.address1) LIKE '$var' OR lower(ct.address2) LIKE '$var')";
+	}
+
+	if ($form->{startdatefrom}) {
+		$where .= " AND ct.startdate >= '$form->{startdatefrom}'";
+	}
+
+	if ($form->{startdateto}) {
+		$where .= " AND ct.startdate <= '$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} eq 'customer') {
+			$where .= qq| AND ct.id NOT IN (SELECT a.customer_id
+											  FROM ar a, customer vc
+											 WHERE vc.id = a.customer_id)|;
+		}
+	
+		if ($form->{db} eq 'vendor') {
+			$where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
+											  FROM ap a, vendor vc
+											 WHERE vc.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,
+							   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 >= '$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,
+								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 >= '$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,
+								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 >= '$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,
+								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->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"} ne "") {
+		$var = $form->like(lc $form->{"$form->{db}number"});
+		$where .= " AND lower(ct.$form->{db}number) LIKE '$var'";
+	}
+
+	if ($form->{address} ne "") {
+		$var = $form->like(lc $form->{address});
+		$where .= " AND lower(ct.address1) LIKE '$var'";
+	}
+
+	for (qw(name contact email phone notes city state zipcode country)) {
+
+		if ($form->{$_} ne "") {
+			$var = $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'";
+	}
+
+	$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;
+	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->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') {
+
+				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 ($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 {
+
+				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 ($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'";
+
+	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 = $form->like(lc $form->{"partnumber_$i"});
+		$where .= " AND lower(p.partnumber) LIKE '$var'";
+	}
+
+	if ($form->{"description_$i"} ne "") {
+		$var = $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 *= 1;
+		$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->disconnect;
+}
+
+
+1;
+