#===================================================================== # 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;