1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # backend code for customers and vendors
27 #======================================================================
33 my ($self, $myconfig, $form) = @_;
35 my $dbh = $form->dbconnect($myconfig);
41 $query = qq|SELECT ct.*, b.description AS business, s.*,
42 e.name AS employee, g.pricegroup AS pricegroup,
43 l.description AS language, ct.curr
45 LEFT JOIN business b ON (ct.business_id = b.id)
46 LEFT JOIN shipto s ON (ct.id = s.trans_id)
47 LEFT JOIN employee e ON (ct.employee_id = e.id)
48 LEFT JOIN pricegroup g ON (g.id = ct.pricegroup_id)
49 LEFT JOIN language l ON (l.code = ct.language_code)
50 WHERE ct.id = $form->{id}|;
51 $sth = $dbh->prepare($query);
52 $sth->execute || $form->dberror($query);
54 $ref = $sth->fetchrow_hashref(NAME_lc);
56 map { $form->{$_} = $ref->{$_} } keys %$ref;
60 # check if it is orphaned
61 my $arap = ($form->{db} eq 'customer') ? "ar" : "ap";
62 $query = qq|SELECT a.id
64 JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
65 WHERE ct.id = $form->{id}
69 JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
70 WHERE ct.id = $form->{id}|;
71 $sth = $dbh->prepare($query);
72 $sth->execute || $form->dberror($query);
74 unless ($sth->fetchrow_array) {
75 $form->{status} = "orphaned";
80 # get taxes for customer/vendor
81 $query = qq|SELECT c.accno
83 JOIN $form->{db}tax t ON (t.chart_id = c.id)
84 WHERE t.$form->{db}_id = $form->{id}|;
85 $sth = $dbh->prepare($query);
86 $sth->execute || $form->dberror($query);
88 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
89 $form->{tax}{$ref->{accno}}{taxable} = 1;
95 ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
97 $query = qq|SELECT current_date FROM defaults|;
98 ($form->{startdate}) = $dbh->selectrow_array($query);
103 $query = qq|SELECT c.accno, c.description
105 JOIN tax t ON (t.chart_id = c.id)
106 WHERE c.link LIKE '%CT_tax%'
108 $sth = $dbh->prepare($query);
109 $sth->execute || $form->dberror($query);
111 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
112 $form->{taxaccounts} .= "$ref->{accno} ";
113 $form->{tax}{$ref->{accno}}{description} = $ref->{description};
116 chop $form->{taxaccounts};
123 $sth = $dbh->prepare($query);
124 $sth->execute || $form->dberror($query);
126 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
127 push @{ $form->{all_business} }, $ref;
131 # this is for the salesperson
132 $query = qq|SELECT id, name
136 $sth = $dbh->prepare($query);
137 $sth->execute || $form->dberror($query);
139 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
140 push @{ $form->{all_employee} }, $ref;
148 $sth = $dbh->prepare($query);
149 $sth->execute || $form->dberror($query);
151 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
152 push @{ $form->{all_language} }, $ref;
160 $sth = $dbh->prepare($query);
161 $sth->execute || $form->dberror($query);
163 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
164 push @{ $form->{all_pricegroup} }, $ref;
169 $query = qq|SELECT curr AS currencies
171 $sth = $dbh->prepare($query);
172 $sth->execute || $form->dberror($query);
174 ($form->{currencies}) = $sth->fetchrow_array;
183 my ($self, $myconfig, $form) = @_;
185 # connect to database
186 my $dbh = $form->dbconnect_noauto($myconfig);
191 # remove double spaces
192 $form->{name} =~ s/ / /g;
193 # remove double minus and minus at the end
194 $form->{name} =~ s/--+/-/g;
195 $form->{name} =~ s/-+$//;
197 # assign value discount, terms, creditlimit
198 $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
199 $form->{discount} /= 100;
201 $form->{taxincluded} *= 1;
202 $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
206 $query = qq|DELETE FROM customertax
207 WHERE customer_id = $form->{id}|;
208 $dbh->do($query) || $form->dberror($query);
210 $query = qq|DELETE FROM shipto
211 WHERE trans_id = $form->{id}|;
212 $dbh->do($query) || $form->dberror($query);
215 if ($form->{type} && $form->{enddate}) {
217 $query = qq|SELECT enddate, current_date AS now FROM customer|;
218 ($form->{enddate}, $now) = $dbh->selectrow_array($query);
219 $form->{enddate} = $now if $form->{enddate} lt $now;
224 $uid .= $form->{login};
226 $query = qq|INSERT INTO customer (name)
228 $dbh->do($query) || $form->dberror($query);
230 $query = qq|SELECT id FROM customer
231 WHERE name = '$uid'|;
232 $sth = $dbh->prepare($query);
233 $sth->execute || $form->dberror($query);
235 ($form->{id}) = $sth->fetchrow_array;
241 ($null, $employee_id) = split /--/, $form->{employee};
245 ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
249 ($null, $business_id) = split /--/, $form->{business};
253 ($null, $language_code) = split /--/, $form->{language};
255 $form->{customernumber} = $form->update_defaults($myconfig, "customernumber", $dbh) if ! $form->{customernumber};
257 $query = qq|UPDATE customer SET
258 customernumber = |.$dbh->quote($form->{customernumber}).qq|,
259 name = |.$dbh->quote($form->{name}).qq|,
260 address1 = |.$dbh->quote($form->{address1}).qq|,
261 address2 = |.$dbh->quote($form->{address2}).qq|,
262 city = |.$dbh->quote($form->{city}).qq|,
263 state = |.$dbh->quote($form->{state}).qq|,
264 zipcode = |.$dbh->quote($form->{zipcode}).qq|,
265 country = |.$dbh->quote($form->{country}).qq|,
266 contact = |.$dbh->quote($form->{contact}).qq|,
267 phone = '$form->{phone}',
268 fax = '$form->{fax}',
269 email = '$form->{email}',
271 bcc = '$form->{bcc}',
272 notes = |.$dbh->quote($form->{notes}).qq|,
273 discount = $form->{discount},
274 creditlimit = $form->{creditlimit},
275 terms = $form->{terms},
276 taxincluded = '$form->{taxincluded}',
277 business_id = $business_id,
278 taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
279 sic_code = '$form->{sic}',
280 iban = '$form->{iban}',
281 bic = '$form->{bic}',
282 employee_id = $employee_id,
283 pricegroup_id = $pricegroup_id,
284 language_code = '$language_code',
285 curr = '$form->{curr}',
286 startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
287 enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|
288 WHERE id = $form->{id}|;
289 $dbh->do($query) || $form->dberror($query);
292 foreach $item (split / /, $form->{taxaccounts}) {
293 if ($form->{"tax_$item"}) {
294 $query = qq|INSERT INTO customertax (customer_id, chart_id)
295 VALUES ($form->{id}, (SELECT id
297 WHERE accno = '$item'))|;
298 $dbh->do($query) || $form->dberror($query);
303 $form->add_shipto($dbh, $form->{id});
312 my ($self, $myconfig, $form) = @_;
314 # connect to database
315 my $dbh = $form->dbconnect_noauto($myconfig);
321 # remove double spaces
322 $form->{name} =~ s/ / /g;
323 # remove double minus and minus at the end
324 $form->{name} =~ s/--+/-/g;
325 $form->{name} =~ s/-+$//;
327 $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
328 $form->{discount} /= 100;
330 $form->{taxincluded} *= 1;
331 $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
335 $query = qq|DELETE FROM vendortax
336 WHERE vendor_id = $form->{id}|;
337 $dbh->do($query) || $form->dberror($query);
339 $query = qq|DELETE FROM shipto
340 WHERE trans_id = $form->{id}|;
341 $dbh->do($query) || $form->dberror($query);
344 $uid .= $form->{login};
346 $query = qq|INSERT INTO vendor (name)
348 $dbh->do($query) || $form->dberror($query);
350 $query = qq|SELECT id FROM vendor
351 WHERE name = '$uid'|;
352 $sth = $dbh->prepare($query);
353 $sth->execute || $form->dberror($query);
355 ($form->{id}) = $sth->fetchrow_array;
361 ($null, $employee_id) = split /--/, $form->{employee};
365 ($null, $pricegroup_id) = split /--/, $form->{pricegroup};
369 ($null, $business_id) = split /--/, $form->{business};
373 ($null, $language_code) = split /--/, $form->{language};
375 $form->{vendornumber} = $form->update_defaults($myconfig, "vendornumber", $dbh) if ! $form->{vendornumber};
377 $query = qq|UPDATE vendor SET
378 vendornumber = |.$dbh->quote($form->{vendornumber}).qq|,
379 name = |.$dbh->quote($form->{name}).qq|,
380 address1 = |.$dbh->quote($form->{address1}).qq|,
381 address2 = |.$dbh->quote($form->{address2}).qq|,
382 city = |.$dbh->quote($form->{city}).qq|,
383 state = |.$dbh->quote($form->{state}).qq|,
384 zipcode = |.$dbh->quote($form->{zipcode}).qq|,
385 country = |.$dbh->quote($form->{country}).qq|,
386 contact = |.$dbh->quote($form->{contact}).qq|,
387 phone = '$form->{phone}',
388 fax = '$form->{fax}',
389 email = '$form->{email}',
391 bcc = '$form->{bcc}',
392 notes = |.$dbh->quote($form->{notes}).qq|,
393 terms = $form->{terms},
394 discount = $form->{discount},
395 creditlimit = $form->{creditlimit},
396 taxincluded = '$form->{taxincluded}',
397 gifi_accno = '$form->{gifi_accno}',
398 business_id = $business_id,
399 taxnumber = |.$dbh->quote($form->{taxnumber}).qq|,
400 sic_code = '$form->{sic}',
401 iban = '$form->{iban}',
402 bic = '$form->{bic}',
403 employee_id = $employee_id,
404 language_code = '$language_code',
405 pricegroup_id = $pricegroup_id,
406 curr = '$form->{curr}',
407 startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
408 enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|
409 WHERE id = $form->{id}|;
410 $dbh->do($query) || $form->dberror($query);
413 foreach $item (split / /, $form->{taxaccounts}) {
414 if ($form->{"tax_$item"}) {
415 $query = qq|INSERT INTO vendortax (vendor_id, chart_id)
416 VALUES ($form->{id}, (SELECT id
418 WHERE accno = '$item'))|;
419 $dbh->do($query) || $form->dberror($query);
424 $form->add_shipto($dbh, $form->{id});
434 my ($self, $myconfig, $form) = @_;
436 # connect to database
437 my $dbh = $form->dbconnect($myconfig);
439 # delete customer/vendor
440 my $query = qq|DELETE FROM $form->{db}
441 WHERE id = $form->{id}|;
442 $dbh->do($query) || $form->dberror($query);
450 my ($self, $myconfig, $form) = @_;
452 # connect to database
453 my $dbh = $form->dbconnect($myconfig);
456 $form->{sort} = ($form->{sort}) ? $form->{sort} : "name";
458 my $sortorder = $form->sort_order(\@a);
463 @a = ("$form->{db}number");
464 push @a, qw(name contact city state zipcode country notes email);
467 if ($form->{$item}) {
468 $var = $form->like(lc $form->{$item});
469 $where .= " AND lower(ct.$item) LIKE '$var'";
472 if ($form->{address}) {
473 $var = $form->like(lc $form->{address});
474 $where .= " AND (lower(ct.address1) LIKE '$var' OR lower(ct.address2) LIKE '$var')";
477 if ($form->{status} eq 'orphaned') {
478 $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id
479 FROM oe o, $form->{db} cv
480 WHERE cv.id = o.$form->{db}_id)|;
481 if ($form->{db} eq 'customer') {
482 $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
483 FROM ar a, customer cv
484 WHERE cv.id = a.customer_id)|;
486 if ($form->{db} eq 'vendor') {
487 $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
489 WHERE cv.id = a.vendor_id)|;
491 $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
495 my $query = qq|SELECT ct.*, b.description AS business,
496 e.name AS employee, g.pricegroup, l.description AS language,
499 LEFT JOIN business b ON (ct.business_id = b.id)
500 LEFT JOIN employee e ON (ct.employee_id = e.id)
501 LEFT JOIN employee m ON (m.id = e.managerid)
502 LEFT JOIN pricegroup g ON (ct.pricegroup_id = g.id)
503 LEFT JOIN language l ON (l.code = ct.language_code)
506 # redo for invoices, orders and quotations
507 if ($form->{l_transnumber} || $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
509 my ($ar, $union, $module);
515 if ($form->{open} || $form->{closed}) {
516 unless ($form->{open} && $form->{closed}) {
517 $openarap = " AND a.amount != a.paid" if $form->{open};
518 $openarap = " AND a.amount = a.paid" if $form->{closed};
519 $openoe = " AND o.closed = '0'" if $form->{open};
520 $openoe = " AND o.closed = '1'" if $form->{closed};
524 if ($form->{l_transnumber}) {
525 $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
529 $transwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
530 $transwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
533 $query = qq|SELECT ct.*, b.description AS business,
534 a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
535 '$ar' AS module, 'invoice' AS formtype,
536 (a.amount = a.paid) AS closed, a.amount, a.netamount
538 JOIN $ar a ON (a.$form->{db}_id = ct.id)
539 LEFT JOIN business b ON (ct.business_id = b.id)
551 if ($form->{l_invnumber}) {
552 $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
553 $module = ($ar eq 'ar') ? 'is' : 'ir';
556 $transwhere .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
557 $transwhere .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
560 SELECT ct.*, b.description AS business,
561 a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
562 '$module' AS module, 'invoice' AS formtype,
563 (a.amount = a.paid) AS closed, a.amount, a.netamount
565 JOIN $ar a ON (a.$form->{db}_id = ct.id)
566 LEFT JOIN business b ON (ct.business_id = b.id)
578 if ($form->{l_ordnumber}) {
581 $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
582 $transwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
584 SELECT ct.*, b.description AS business,
585 ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
586 'oe' AS module, 'order' AS formtype,
587 o.closed, o.amount, o.netamount
589 JOIN oe o ON (o.$form->{db}_id = ct.id)
590 LEFT JOIN business b ON (ct.business_id = b.id)
592 AND o.quotation = '0'
602 if ($form->{l_quonumber}) {
605 $transwhere .= " AND o.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
606 $transwhere .= " AND o.transdate <= '$form->{transdateto}'" if $form->{transdateto};
608 SELECT ct.*, b.description AS business,
609 ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
610 'oe' AS module, 'quotation' AS formtype,
611 o.closed, o.amount, o.netamount
613 JOIN oe o ON (o.$form->{db}_id = ct.id)
614 LEFT JOIN business b ON (ct.business_id = b.id)
616 AND o.quotation = '1'
623 $sortorder .= ", invid";
627 ORDER BY $sortorder|;
629 my $sth = $dbh->prepare($query);
630 $sth->execute || $form->dberror($query);
632 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
633 $ref->{address} = "";
634 map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
635 push @{ $form->{CT} }, $ref;
645 my ($self, $myconfig, $form) = @_;
647 # connect to database
648 my $dbh = $form->dbconnect($myconfig);
652 $form->{sort} = "partnumber" unless $form->{sort};
653 my $sortorder = $form->{sort};
661 if ($form->{"$form->{db}number"}) {
662 $var = $form->like(lc $form->{"$form->{db}number"});
663 $where .= " AND lower(ct.$form->{db}number) LIKE '$var'";
666 $var = $form->like(lc $form->{name});
667 $where .= " AND lower(ct.name) LIKE '$var'";
669 if ($form->{address}) {
670 $var = $form->like(lc $form->{address});
671 $where .= " AND lower(ct.address1) LIKE '$var'";
674 $var = $form->like(lc $form->{city});
675 $where .= " AND lower(ct.city) LIKE '$var'";
677 if ($form->{state}) {
678 $var = $form->like(lc $form->{state});
679 $where .= " AND lower(ct.state) LIKE '$var'";
681 if ($form->{zipcode}) {
682 $var = $form->like(lc $form->{zipcode});
683 $where .= " AND lower(ct.zipcode) LIKE '$var'";
685 if ($form->{country}) {
686 $var = $form->like(lc $form->{country});
687 $where .= " AND lower(ct.country) LIKE '$var'";
689 if ($form->{contact}) {
690 $var = $form->like(lc $form->{contact});
691 $where .= " AND lower(ct.contact) LIKE '$var'";
693 if ($form->{notes}) {
694 $var = $form->like(lc $form->{notes});
695 $where .= " AND lower(ct.notes) LIKE '$var'";
697 if ($form->{email}) {
698 $var = $form->like(lc $form->{email});
699 $where .= " AND lower(ct.email) LIKE '$var'";
702 $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
703 $where .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
705 if ($form->{open} || $form->{closed}) {
706 unless ($form->{open} && $form->{closed}) {
707 if ($form->{type} eq 'invoice') {
708 $where .= " AND a.amount != a.paid" if $form->{open};
709 $where .= " AND a.amount = a.paid" if $form->{closed};
711 $where .= " AND a.closed = '0'" if $form->{open};
712 $where .= " AND a.closed = '1'" if $form->{closed};
717 my $invnumber = 'invnumber';
718 my $deldate = 'deliverydate';
721 if ($form->{db} eq 'customer') {
723 if ($form->{type} eq 'invoice') {
724 $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
728 if ($form->{type} eq 'order') {
729 $invnumber = 'ordnumber';
730 $where .= qq| AND a.quotation = '0'|;
732 $invnumber = 'quonumber';
733 $where .= qq| AND a.quotation = '1'|;
735 $deldate = 'reqdate';
738 if ($form->{db} eq 'vendor') {
740 if ($form->{type} eq 'invoice') {
741 $where .= qq| AND a.invoice = '1' AND i.assemblyitem = '0'|;
745 if ($form->{type} eq 'order') {
746 $invnumber = 'ordnumber';
747 $where .= qq| AND a.quotation = '0'|;
749 $invnumber = 'quonumber';
750 $where .= qq| AND a.quotation = '1'|;
752 $deldate = 'reqdate';
757 JOIN invoice i ON (i.trans_id = a.id)|;
759 if ($form->{type} eq 'order') {
761 JOIN orderitems i ON (i.trans_id = a.id)|;
763 if ($form->{type} eq 'quotation') {
765 JOIN orderitems i ON (i.trans_id = a.id)|;
766 $where .= qq| AND a.quotation = '1'|;
770 if ($form->{history} eq 'summary') {
771 $query = qq|SELECT curr FROM defaults|;
772 my ($curr) = $dbh->selectrow_array($query);
775 %ordinal = ( partnumber => 8,
778 $sortorder = "2 $form->{direction}, 1, $ordinal{$sortorder} $form->{direction}";
780 $query = qq|SELECT ct.id AS ctid, ct.name, ct.address1,
781 ct.address2, ct.city, ct.state,
782 p.id AS pid, p.partnumber, i.description, p.unit,
783 sum(i.qty) AS qty, sum(i.sellprice) AS sellprice,
785 ct.zipcode, ct.country
787 JOIN $table a ON (a.$form->{db}_id = ct.id)
789 JOIN parts p ON (p.id = i.parts_id)
791 GROUP BY ct.id, ct.name, ct.address1, ct.address2, ct.city,
792 ct.state, ct.zipcode, ct.country,
793 p.id, p.partnumber, i.description, p.unit
794 ORDER BY $sortorder|;
796 %ordinal = ( partnumber => 9,
803 $sortorder = "2 $form->{direction}, 1, 11, $ordinal{$sortorder} $form->{direction}";
805 $query = qq|SELECT ct.id AS ctid, ct.name, ct.address1,
806 ct.address2, ct.city, ct.state,
807 p.id AS pid, p.partnumber, a.id AS invid,
808 a.$invnumber, a.curr, i.description,
809 i.qty, i.sellprice, i.discount,
810 i.$deldate, i.serialnumber, pr.projectnumber,
811 e.name AS employee, ct.zipcode, ct.country, i.unit|;
812 $query .= qq|, i.fxsellprice| if $form->{type} eq 'invoice';
814 if ($form->{type} ne 'invoice') {
815 if ($form->{l_curr}) {
816 $query .= qq|, (SELECT $buysell FROM exchangerate ex
817 WHERE a.curr = ex.curr
818 AND a.transdate = ex.transdate) AS exchangerate|;
824 JOIN $table a ON (a.$form->{db}_id = ct.id)
826 JOIN parts p ON (p.id = i.parts_id)
827 LEFT JOIN project pr ON (pr.id = i.project_id)
828 LEFT JOIN employee e ON (e.id = a.employee_id)
830 ORDER BY $sortorder|;
834 my $sth = $dbh->prepare($query);
835 $sth->execute || $form->dberror($query);
837 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
838 $ref->{address} = "";
839 $ref->{exchangerate} = 1 unless $ref->{exchangerate};
840 map { $ref->{address} .= "$ref->{$_} "; } qw(address1 address2 city state zipcode country);
841 $ref->{id} = $ref->{ctid};
842 push @{ $form->{CT} }, $ref;
852 my ($self, $myconfig, $form) = @_;
854 # connect to database
855 my $dbh = $form->dbconnect($myconfig);
859 if ($form->{db} eq 'customer') {
860 $query = qq|SELECT p.id, p.partnumber, p.description,
861 p.sellprice, pg.partsgroup, p.partsgroup_id,
862 m.pricebreak, m.sellprice,
863 m.validfrom, m.validto, m.curr
865 JOIN parts p ON (p.id = m.parts_id)
866 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
867 WHERE m.customer_id = $form->{id}
868 ORDER BY partnumber|;
870 if ($form->{db} eq 'vendor') {
871 $query = qq|SELECT p.id, p.partnumber AS sku, p.description,
872 pg.partsgroup, p.partsgroup_id,
873 m.partnumber, m.leadtime, m.lastcost, m.curr
875 JOIN parts p ON (p.id = m.parts_id)
876 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
877 WHERE m.vendor_id = $form->{id}
878 ORDER BY p.partnumber|;
885 $sth = $dbh->prepare($query);
886 $sth->execute || $form->dberror($query);
888 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
889 push @{ $form->{all_partspricelist} }, $ref;
894 $query = qq|SELECT curr FROM defaults|;
895 ($form->{currencies}) = $dbh->selectrow_array($query);
897 $query = qq|SELECT id, partsgroup FROM partsgroup
898 ORDER BY partsgroup|;
900 $sth = $dbh->prepare($query);
901 $sth->execute || $self->dberror($query);
903 $form->{all_partsgroup} = ();
904 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
905 push @{ $form->{all_partsgroup} }, $ref;
915 my ($self, $myconfig, $form) = @_;
917 my $dbh = $form->dbconnect_noauto($myconfig);
919 my $query = qq|DELETE FROM parts$form->{db}
920 WHERE $form->{db}_id = $form->{id}|;
921 $dbh->do($query) || $form->dberror($query);
924 foreach $i (1 .. $form->{rowcount}) {
926 if ($form->{"id_$i"}) {
928 if ($form->{db} eq 'customer') {
929 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(pricebreak sellprice);
931 $query = qq|INSERT INTO parts$form->{db} (parts_id, customer_id,
932 pricebreak, sellprice, validfrom, validto, curr)
933 VALUES ($form->{"id_$i"}, $form->{id},
934 $form->{"pricebreak_$i"}, $form->{"sellprice_$i"},|
935 .$form->dbquote($form->{"validfrom_$i"}, SQL_DATE) .qq|,|
936 .$form->dbquote($form->{"validto_$i"}, SQL_DATE) .qq|,
937 '$form->{"curr_$i"}')|;
939 map { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } qw(leadtime lastcost);
941 $query = qq|INSERT INTO parts$form->{db} (parts_id, vendor_id,
942 partnumber, lastcost, leadtime, curr)
943 VALUES ($form->{"id_$i"}, $form->{id},
944 '$form->{"partnumber_$i"}', $form->{"lastcost_$i"},
945 $form->{"leadtime_$i"}, '$form->{"curr_$i"}')|;
948 $dbh->do($query) || $form->dberror($query);
961 my ($self, $myconfig, $form) = @_;
963 # connect to database
964 my $dbh = $form->dbconnect($myconfig);
966 my $i = $form->{rowcount};
970 my $where = "WHERE p.obsolete = '0' AND p.income_accno_id > 0";
972 if ($form->{"partnumber_$i"}) {
973 $var = $form->like(lc $form->{"partnumber_$i"});
974 $where .= " AND lower(p.partnumber) LIKE '$var'";
976 if ($form->{"description_$i"}) {
977 $var = $form->like(lc $form->{"description_$i"});
978 $where .= " AND lower(p.description) LIKE '$var'";
981 if ($form->{"partsgroup_$i"}) {
982 ($null, $var) = split /--/, $form->{"partsgroup_$i"};
983 $where .= qq| AND p.partsgroup_id = $var|;
987 my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
988 p.lastcost, p.unit, pg.partsgroup, p.partsgroup_id
990 LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
993 my $sth = $dbh->prepare($query);
994 $sth->execute || $form->dberror($query);
997 $form->{item_list} = ();
998 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
999 push @{ $form->{item_list} }, $ref;