1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Inventory Control backend
33 #======================================================================
40 $main::lxdebug->enter_sub();
42 my ($self, $myconfig, $form) = @_;
45 my $dbh = $form->dbconnect($myconfig);
51 c1.accno AS inventory_accno,
52 c2.accno AS income_accno,
53 c3.accno AS expense_accno,
56 LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
57 LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
58 LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
59 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
61 my $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
63 # copy to $form variables
64 map { $form->{$_} = $ref->{$_} } (keys %{$ref});
66 my %oid = ('Pg' => 'a.oid',
67 'Oracle' => 'a.rowid');
69 # part or service item
70 $form->{item} = ($form->{inventory_accno}) ? 'part' : 'service';
71 if ($form->{assembly}) {
72 $form->{item} = 'assembly';
74 # retrieve assembly items
76 qq|SELECT p.id, p.partnumber, p.description,
77 p.sellprice, p.weight, a.qty, a.bom, p.unit,
80 JOIN assembly a ON (a.parts_id = p.id)
81 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
83 ORDER BY $oid{$myconfig->{dbdriver}}|;
84 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
86 $form->{assembly_rows} = 0;
87 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
88 $form->{assembly_rows}++;
89 foreach my $key (keys %{$ref}) {
90 $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
97 # setup accno hash for <option checked> {amount} is used in create_links
98 $form->{amount}{IC} = $form->{inventory_accno};
99 $form->{amount}{IC_income} = $form->{income_accno};
100 $form->{amount}{IC_sale} = $form->{income_accno};
101 $form->{amount}{IC_expense} = $form->{expense_accno};
102 $form->{amount}{IC_cogs} = $form->{expense_accno};
104 my @pricegroups = ();
105 my @pricegroups_not_used = ();
109 qq|SELECT p.parts_id, p.pricegroup_id, p.price,
110 (SELECT pg.pricegroup
112 WHERE pg.id = p.pricegroup_id) AS pricegroup
115 ORDER BY pricegroup|;
116 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
120 while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
121 $form->{"price_$i"}, $form->{"pricegroup_$i"})
122 = $sth->fetchrow_array()) {
123 $form->{"price_$i"} = $form->round_amount($form->{"price_$i"}, 5);
124 $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
125 push @pricegroups, $form->{"pricegroup_id_$i"};
132 $query = qq|SELECT id, pricegroup FROM pricegroup|;
133 $form->{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query);
135 #find not used pricegroups
136 while ($tmp = pop(@{ $form->{PRICEGROUPS} })) {
138 foreach my $item (@pricegroups) {
139 if ($item eq $tmp->{id}) {
144 push(@pricegroups_not_used, $tmp) unless ($in_use);
147 # if not used pricegroups are avaible
148 if (@pricegroups_not_used) {
150 foreach $name (@pricegroups_not_used) {
151 $form->{"klass_$i"} = "$name->{id}";
152 $form->{"price_$i"} = $form->round_amount($form->{sellprice}, 5);
153 $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
154 $form->{"pricegroup_id_$i"} = "$name->{id}";
155 $form->{"pricegroup_$i"} = "$name->{pricegroup}";
161 $form->{price_rows} = $i - 1;
163 unless ($form->{item} eq 'service') {
166 if ($form->{makemodel}) {
167 $query = qq|SELECT m.make, m.model FROM makemodel m | .
168 qq|WHERE m.parts_id = ?|;
169 @values = ($form->{id});
170 $sth = $dbh->prepare($query);
171 $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
174 while (($form->{"make_$i"}, $form->{"model_$i"}) = $sth->fetchrow_array)
179 $form->{makemodel_rows} = $i - 1;
185 $form->{language_values} = "";
186 $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
187 my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
188 while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
189 $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
193 # now get accno for taxes
196 FROM chart c, partstax pt
197 WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|;
198 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
199 while (($key) = $sth->fetchrow_array) {
200 $form->{amount}{$key} = $key;
209 WHERE (i.parts_id = ?)
215 WHERE (o.parts_id = ?)
221 WHERE (a.parts_id = ?)|;
222 @values = (conv_i($form->{id}), conv_i($form->{id}), conv_i($form->{id}));
223 ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
224 $form->{orphaned} = !$form->{orphaned};
226 $form->{"unit_changeable"} = 1;
227 foreach my $table (qw(invoice assembly orderitems inventory license)) {
228 $query = qq|SELECT COUNT(*) FROM $table WHERE parts_id = ?|;
229 my ($count) = selectrow_query($form, $dbh, $query, conv_i($form->{"id"}));
232 $form->{"unit_changeable"} = 0;
239 $main::lxdebug->leave_sub();
242 sub get_pricegroups {
243 $main::lxdebug->enter_sub();
245 my ($self, $myconfig, $form) = @_;
247 my $dbh = $form->dbconnect($myconfig);
250 my $query = qq|SELECT id, pricegroup FROM pricegroup|;
251 my $pricegroups = selectall_hashref_query($form, $dbh, $query);
254 foreach $pg (@{ $pricegroups }) {
255 $form->{"klass_$i"} = "$pg->{id}";
256 $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2);
257 $form->{"pricegroup_id_$i"} = "$pg->{id}";
258 $form->{"pricegroup_$i"} = "$pg->{pricegroup}";
263 $form->{price_rows} = $i - 1;
267 $main::lxdebug->leave_sub();
270 sub retrieve_buchungsgruppen {
271 $main::lxdebug->enter_sub();
273 my ($self, $myconfig, $form) = @_;
277 my $dbh = $form->dbconnect($myconfig);
279 # get buchungsgruppen
280 $query = qq|SELECT id, description FROM buchungsgruppen ORDER BY sortkey|;
281 $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, $query);
283 $main::lxdebug->leave_sub();
287 $main::lxdebug->enter_sub();
289 my ($self, $myconfig, $form) = @_;
291 # connect to database, turn off AutoCommit
292 my $dbh = $form->dbconnect_noauto($myconfig);
295 # make up a unique handle and store in partnumber field
296 # then retrieve the record based on the unique handle to get the id
297 # replace the partnumber field with the actual variable
298 # add records for makemodel
300 # if there is a $form->{id} then replace the old entry
301 # delete all makemodel entries and add the new ones
303 # undo amount formatting
304 map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
305 qw(rop weight listprice sellprice gv lastcost stock);
307 my $makemodel = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
309 $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0;
316 $query = qq|SELECT sellprice, weight FROM parts WHERE id = ?|;
317 my ($sellprice, $weight) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
319 # if item is part of an assembly adjust all assemblies
320 $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
321 $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
322 while (my ($id, $qty) = $sth->fetchrow_array) {
323 &update_assembly($dbh, $form, $id, $qty, $sellprice * 1, $weight * 1);
327 if ($form->{item} ne 'service') {
328 # delete makemodel records
329 do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
332 if ($form->{item} eq 'assembly') {
333 if ($form->{onhand} != 0) {
334 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1);
337 # delete assembly records
338 do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
340 $form->{onhand} += $form->{stock};
344 do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id}));
346 # delete translations
347 do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
350 my ($count) = selectrow_query($form, $dbh, qq|SELECT COUNT(*) FROM parts WHERE partnumber = ?|, $form->{partnumber});
352 $main::lxdebug->leave_sub();
356 ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
357 do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id});
359 $form->{orphaned} = 1;
360 $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly';
361 if ($form->{partnumber} eq "" && $form->{"item"} eq "service") {
362 $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber");
364 if ($form->{partnumber} eq "" && $form->{"item"} ne "service") {
365 $form->{partnumber} = $form->update_defaults($myconfig, "articlenumber");
369 my $partsgroup_id = 0;
371 if ($form->{partsgroup}) {
372 ($partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
375 my ($subq_inventory, $subq_expense, $subq_income);
376 if ($form->{"item"} eq "part") {
378 qq|(SELECT bg.inventory_accno_id
379 FROM buchungsgruppen bg
380 WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
382 $subq_inventory = "NULL";
385 if ($form->{"item"} ne "assembly") {
387 qq|(SELECT bg.expense_accno_id_0
388 FROM buchungsgruppen bg
389 WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
391 $subq_expense = "NULL";
411 buchungsgruppen_id = ?,
413 inventory_accno_id = $subq_inventory,
414 income_accno_id = (SELECT bg.income_accno_id_0 FROM buchungsgruppen bg WHERE bg.id = ?),
415 expense_accno_id = $subq_expense,
423 not_discountable = ?,
427 @values = ($form->{partnumber},
428 $form->{description},
429 $makemodel ? 't' : 'f',
430 $form->{assembly} ? 't' : 'f',
435 conv_date($form->{priceupdate}),
441 conv_i($form->{buchungsgruppen_id}),
442 conv_i($form->{payment_id}),
443 conv_i($form->{buchungsgruppen_id}),
444 $form->{obsolete} ? 't' : 'f',
447 $form->{shop} ? 't' : 'f',
451 $form->{not_discountable} ? 't' : 'f',
453 conv_i($partsgroup_id),
456 do_query($form, $dbh, $query, @values);
458 # delete translation records
459 do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
461 if ($form->{language_values} ne "") {
462 foreach $item (split(/---\+\+\+---/, $form->{language_values})) {
463 my ($language_id, $translation, $longdescription) = split(/--\+\+--/, $item);
464 if ($translation ne "") {
465 $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription)
466 VALUES ( ?, ?, ?, ? )|;
467 @values = (conv_i($form->{id}), conv_i($language_id), $translation, $longdescription);
468 do_query($form, $dbh, $query, @values);
473 # delete price records
474 do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id}));
476 # insert price records only if different to sellprice
477 for my $i (1 .. $form->{price_rows}) {
478 if ($form->{"price_$i"} eq "0") {
479 $form->{"price_$i"} = $form->{sellprice};
482 ( $form->{"price_$i"}
483 || $form->{"klass_$i"}
484 || $form->{"pricegroup_id_$i"})
485 and $form->{"price_$i"} != $form->{sellprice}
487 #$klass = $form->parse_amount($myconfig, $form->{"klass_$i"});
488 $price = $form->parse_amount($myconfig, $form->{"price_$i"});
490 $form->parse_amount($myconfig, $form->{"pricegroup_id_$i"});
491 $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) | .
493 @values = (conv_i($form->{id}), conv_i($pricegroup_id), $price);
494 do_query($form, $dbh, $query, @values);
498 # insert makemodel records
499 unless ($form->{item} eq 'service') {
500 for my $i (1 .. $form->{makemodel_rows}) {
501 if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
502 map { $form->{"${_}_$i"} =~ s/\'/\'\'/g } qw(make model);
504 $query = qq|INSERT INTO makemodel (parts_id, make, model) | .
505 qq|VALUES (?, ?, ?)|;
506 @values = (conv_i($form->{id}), $form->{"make_$i"}, $form->{"model_$i"});
507 do_query($form, $dbh, $query, @values);
513 foreach $item (split(/ /, $form->{taxaccounts})) {
514 if ($form->{"IC_tax_$item"}) {
516 qq|INSERT INTO partstax (parts_id, chart_id)
517 VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
518 @values = (conv_i($form->{id}), $item);
519 do_query($form, $dbh, $query, @values);
523 # add assembly records
524 if ($form->{item} eq 'assembly') {
526 for my $i (1 .. $form->{assembly_rows}) {
527 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
529 if ($form->{"qty_$i"} != 0) {
530 $form->{"bom_$i"} *= 1;
531 $query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
532 qq|VALUES (?, ?, ?, ?)|;
533 @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
534 do_query($form, $dbh, $query, @values);
538 # adjust onhand for the parts
539 if ($form->{onhand} != 0) {
540 &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand});
546 my $shippingdate = "$a[5]-$a[4]-$a[3]";
548 $form->get_employee($dbh);
550 # add inventory record
552 qq|INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id)
553 VALUES (0, ?, ?, '$shippingdate', ?)|;
554 @values = (conv_i($form->{id}), $form->{stock}, conv_i($form->{employee_id}));
555 do_query($form, $dbh, $query, @values);
559 #set expense_accno=inventory_accno if they are different => bilanz
561 ($form->{expense_accno} != $form->{inventory_accno})
562 ? $form->{inventory_accno}
563 : $form->{expense_accno};
565 # get tax rates and description
567 ($form->{vc} eq "customer") ? $form->{income_accno} : $vendor_accno;
569 qq|SELECT c.accno, c.description, t.rate, t.taxnumber
571 WHERE (c.id = t.chart_id) AND (t.taxkey IN (SELECT taxkey_id FROM chart where accno = ?))
573 $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
575 $form->{taxaccount} = "";
576 while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
577 $form->{taxaccount} .= "$ptr->{accno} ";
578 if (!($form->{taxaccount2} =~ /$ptr->{accno}/)) {
579 $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
580 $form->{"$ptr->{accno}_description"} = $ptr->{description};
581 $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
582 $form->{taxaccount2} .= " $ptr->{accno} ";
587 my $rc = $dbh->commit;
590 $main::lxdebug->leave_sub();
595 sub update_assembly {
596 $main::lxdebug->enter_sub();
598 my ($dbh, $form, $id, $qty, $sellprice, $weight) = @_;
600 my $query = qq|SELECT id, qty FROM assembly WHERE parts_id = ?|;
601 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
603 while (my ($pid, $aqty) = $sth->fetchrow_array) {
604 &update_assembly($dbh, $form, $pid, $aqty * $qty, $sellprice, $weight);
609 qq|UPDATE parts SET sellprice = sellprice + ?, weight = weight + ?
611 @values = ($qty * ($form->{sellprice} - $sellprice),
612 $qty * ($form->{weight} - $weight), conv_i($id));
613 do_query($form, $dbh, $query, @values);
615 $main::lxdebug->leave_sub();
618 sub retrieve_assemblies {
619 $main::lxdebug->enter_sub();
621 my ($self, $myconfig, $form) = @_;
623 # connect to database
624 my $dbh = $form->dbconnect($myconfig);
626 my $where = qq|NOT p.obsolete|;
629 if ($form->{partnumber}) {
630 $where .= qq| AND (p.partnumber ILIKE ?)|;
631 push(@values, '%' . $form->{partnumber} . '%');
634 if ($form->{description}) {
635 $where .= qq| AND (p.description ILIKE ?)|;
636 push(@values, '%' . $form->{description} . '%');
639 # retrieve assembly items
641 qq|SELECT p.id, p.partnumber, p.description,
642 p.bin, p.onhand, p.rop,
643 (SELECT sum(p2.inventory_accno_id)
644 FROM parts p2, assembly a
645 WHERE (p2.id = a.parts_id) AND (a.id = p.id)) AS inventory
647 WHERE NOT p.obsolete AND p.assembly $where|;
649 $form->{assembly_items} = selectall_hashref_query($form, $dbh, $query, @values);
653 $main::lxdebug->leave_sub();
656 sub restock_assemblies {
657 $main::lxdebug->enter_sub();
659 my ($self, $myconfig, $form) = @_;
661 # connect to database
662 my $dbh = $form->dbconnect_noauto($myconfig);
664 for my $i (1 .. $form->{rowcount}) {
666 $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
668 if ($form->{"qty_$i"} != 0) {
669 &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"});
674 my $rc = $dbh->commit;
677 $main::lxdebug->leave_sub();
682 sub adjust_inventory {
683 $main::lxdebug->enter_sub();
685 my ($dbh, $form, $id, $qty) = @_;
688 qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty
689 FROM parts p, assembly a
690 WHERE (a.parts_id = p.id) AND (a.id = ?)|;
691 my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
693 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
695 my $allocate = $qty * $ref->{qty};
697 # is it a service item, then loop
698 $ref->{inventory_accno_id} *= 1;
699 next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly});
701 # adjust parts onhand
702 $form->update_balance($dbh, "parts", "onhand",
710 my $rc = $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $qty, $id);
712 $main::lxdebug->leave_sub();
718 $main::lxdebug->enter_sub();
720 my ($self, $myconfig, $form) = @_;
721 my @values = (conv_i($form->{id}));
722 # connect to database, turn off AutoCommit
723 my $dbh = $form->dbconnect_noauto($myconfig);
725 my %columns = ( "assembly" => "id", "alternate" => "id", "parts" => "id" );
727 for my $table (qw(prices partstax makemodel inventory assembly parts)) {
728 my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
729 do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
733 my $rc = $dbh->commit;
736 $main::lxdebug->leave_sub();
742 $main::lxdebug->enter_sub();
744 my ($self, $myconfig, $form) = @_;
746 my $i = $form->{assembly_rows};
748 my $where = qq|1 = 1|;
751 my %columns = ("partnumber" => "p", "description" => "p", "partsgroup" => "pg");
753 while (my ($column, $table) = each(%columns)) {
754 next unless ($form->{"${column}_$i"});
755 $where .= qq| AND ${table}.${column} ILIKE ?|;
756 push(@values, '%' . $form->{"${column}_$i"} . '%');
760 $where .= qq| AND NOT (p.id = ?)|;
761 push(@values, conv_i($form->{id}));
765 $where .= qq| ORDER BY p.partnumber|;
767 $where .= qq| ORDER BY p.description|;
770 # connect to database
771 my $dbh = $form->dbconnect($myconfig);
774 qq|SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, p.onhand, p.unit, pg.partsgroup
776 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
778 $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
782 $main::lxdebug->leave_sub();
786 $main::lxdebug->enter_sub();
788 my ($self, $myconfig, $form) = @_;
790 my $where = qq|1 = 1|;
791 my (@values, $var, $flds, $group, $limit);
793 foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
796 if ($form->{$column}) {
797 $where .= qq| AND (${item} ILIKE ?)|;
798 push(@values, '%' . $form->{$column} . '%');
802 # special case for description
803 if ($form->{description}
804 && !( $form->{bought} || $form->{sold} || $form->{onorder}
805 || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
806 $where .= qq| AND (p.description ILIKE ?)|;
807 push(@values, '%' . $form->{description} . '%');
810 # special case for serialnumber
811 if ($form->{l_serialnumber} && $form->{serialnumber}) {
812 $where .= qq| AND (p.serialnumber ILIKE ?)|;
813 push(@values, '%' . $form->{serialnumber} . '%');
816 if ($form->{searchitems} eq 'part') {
817 $where .= qq| AND (p.inventory_accno_id > 0) |;
820 if ($form->{searchitems} eq 'assembly') {
821 $form->{bought} = "";
822 $where .= qq| AND p.assembly|;
825 if ($form->{searchitems} eq 'service') {
826 $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|;
828 # irrelevant for services
829 $form->{make} = $form->{model} = "";
832 # items which were never bought, sold or on an order
833 if ($form->{itemstatus} eq 'orphaned') {
834 $form->{onhand} = $form->{short} = 0;
835 $form->{bought} = $form->{sold} = 0;
836 $form->{onorder} = $form->{ordered} = 0;
837 $form->{rfq} = $form->{quoted} = 0;
839 $form->{transdatefrom} = $form->{transdateto} = "";
842 qq| AND (p.onhand = 0)
845 SELECT DISTINCT parts_id FROM invoice
847 SELECT DISTINCT parts_id FROM assembly
849 SELECT DISTINCT parts_id FROM orderitems
853 if ($form->{itemstatus} eq 'active') {
854 $where .= qq| AND (p.obsolete = '0')|;
855 } elsif ($form->{itemstatus} eq 'obsolete') {
856 $where .= qq| AND (p.obsolete = '1')|;
857 $form->{onhand} = $form->{short} = 0;
858 } elsif ($form->{itemstatus} eq 'onhand') {
859 $where .= qq| AND (p.onhand > 0)|;
860 } elsif ($form->{itemstatus} eq 'short') {
861 $where .= qq| AND (p.onhand < p.rop)|;
865 foreach my $column (qw(make model)) {
866 push @subcolumns, $column if $form->{$column};
869 $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE |;
870 $where .= join " AND ", map { "($_ ILIKE ?)"; } @subcolumns;
872 push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
875 if ($form->{l_soldtotal}) {
876 $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|;
878 qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|;
881 $limit = qq| LIMIT 100| if ($form->{top100});
883 # connect to database
884 my $dbh = $form->dbconnect($myconfig);
886 my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand
887 invnumber ordnumber quonumber name drawing microfiche
888 serialnumber soldtotal deliverydate);
890 my $sortorder = "partnumber";
891 $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
892 $sortorder .= " DESC" if ($form->{revers});
896 if ($form->{l_soldtotal}) {
897 $form->{soldtotal} = 'soldtotal';
899 qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice,
900 p.lastcost, p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal FROM parts
901 p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i
908 qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
909 p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
910 p.priceupdate, p.image, p.drawing, p.microfiche,
913 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
920 my @all_values = @values;
922 # rebuild query for bought and sold items
928 || $form->{quoted}) {
933 if ($form->{bought} || $form->{sold}) {
935 my @invvalues = @values;
936 my $invwhere = "$where";
937 $invwhere .= qq| AND i.assemblyitem = '0'|;
939 if ($form->{transdatefrom}) {
940 $invwhere .= qq| AND a.transdate >= ?|;
941 push(@invvalues, $form->{transdatefrom});
944 if ($form->{transdateto}) {
945 $invwhere .= qq| AND a.transdate <= ?|;
946 push(@invvalues, $form->{transdateto});
949 if ($form->{description}) {
950 $invwhere .= qq| AND i.description ILIKE ?|;
951 push(@invvalues, '%' . $form->{description} . '%');
955 qq|p.id, p.partnumber, i.description, i.serialnumber,
956 i.qty AS onhand, i.unit, p.bin, i.sellprice,
957 p.listprice, p.lastcost, p.rop, p.weight,
958 p.priceupdate, p.image, p.drawing, p.microfiche,
960 a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
961 ct.name, i.deliverydate|;
963 if ($form->{bought}) {
965 qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate
967 JOIN parts p ON (p.id = i.parts_id)
968 JOIN ap a ON (a.id = i.trans_id)
969 JOIN vendor ct ON (a.vendor_id = ct.id)
970 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
973 $union = qq| UNION |;
975 push(@all_values, @invvalues);
982 SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate
984 JOIN parts p ON (p.id = i.parts_id)
985 JOIN ar a ON (a.id = i.trans_id)
986 JOIN customer ct ON (a.customer_id = ct.id)
987 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
989 $union = qq| UNION |;
991 push(@all_values, @invvalues);
995 if ($form->{onorder} || $form->{ordered}) {
996 my @ordvalues = @values;
997 my $ordwhere = $where . qq| AND o.quotation = '0'|;
999 if ($form->{transdatefrom}) {
1000 $ordwhere .= qq| AND o.transdate >= ?|;
1001 push(@ordvalues, $form->{transdatefrom});
1004 if ($form->{transdateto}) {
1005 $ordwhere .= qq| AND o.transdate <= ?|;
1006 push(@ordvalues, $form->{transdateto});
1009 if ($form->{description}) {
1010 $ordwhere .= qq| AND oi.description ILIKE ?|;
1011 push(@ordvalues, '%' . $form->{description} . '%');
1014 if ($form->{ordered}) {
1018 SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1019 oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1020 p.listprice, p.lastcost, p.rop, p.weight,
1021 p.priceupdate, p.image, p.drawing, p.microfiche,
1023 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1024 ct.name, NULL AS deliverydate,
1025 'oe' AS module, 'sales_order' AS type,
1026 (SELECT buy FROM exchangerate ex
1027 WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate
1029 JOIN parts p ON (oi.parts_id = p.id)
1030 JOIN oe o ON (oi.trans_id = o.id)
1031 JOIN customer ct ON (o.customer_id = ct.id)
1032 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1033 WHERE $ordwhere AND (o.customer_id > 0)|;
1034 $union = qq| UNION |;
1036 push(@all_values, @ordvalues);
1039 if ($form->{onorder}) {
1043 SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1044 oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1045 p.listprice, p.lastcost, p.rop, p.weight,
1046 p.priceupdate, p.image, p.drawing, p.microfiche,
1048 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1049 ct.name, NULL AS deliverydate,
1050 'oe' AS module, 'purchase_order' AS type,
1051 (SELECT sell FROM exchangerate ex
1052 WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate
1054 JOIN parts p ON (oi.parts_id = p.id)
1055 JOIN oe o ON (oi.trans_id = o.id)
1056 JOIN vendor ct ON (o.vendor_id = ct.id)
1057 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1058 WHERE $ordwhere AND (o.vendor_id > 0)|;
1059 $union = qq| UNION |;
1061 push(@all_values, @ordvalues);
1066 if ($form->{rfq} || $form->{quoted}) {
1067 my $quowhere = $where . qq| AND o.quotation = '1'|;
1068 my @quovalues = @values;
1070 if ($form->{transdatefrom}) {
1071 $quowhere .= qq| AND o.transdate >= ?|;
1072 push(@quovalues, $form->{transdatefrom});
1075 if ($form->{transdateto}) {
1076 $quowhere .= qq| AND o.transdate <= ?|;
1077 push(@quovalues, $form->{transdateto});
1080 if ($form->{description}) {
1081 $quowhere .= qq| AND oi.description ILIKE ?|;
1082 push(@quovalues, '%' . $form->{description} . '%');
1085 if ($form->{quoted}) {
1090 p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1091 oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
1092 p.listprice, p.lastcost, p.rop, p.weight,
1093 p.priceupdate, p.image, p.drawing, p.microfiche,
1095 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1096 ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type,
1097 (SELECT buy FROM exchangerate ex
1098 WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1100 JOIN parts p ON (oi.parts_id = p.id)
1101 JOIN oe o ON (oi.trans_id = o.id)
1102 JOIN customer ct ON (o.customer_id = ct.id)
1103 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1105 AND o.customer_id > 0|;
1106 $union = qq| UNION |;
1108 push(@all_values, @quovalues);
1115 SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
1116 oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
1117 p.listprice, p.lastcost, p.rop, p.weight,
1118 p.priceupdate, p.image, p.drawing, p.microfiche,
1120 '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
1121 ct.name, NULL AS deliverydate,
1122 'oe' AS module, 'request_quotation' AS type,
1123 (SELECT sell FROM exchangerate ex
1124 WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate
1126 JOIN parts p ON (oi.parts_id = p.id)
1127 JOIN oe o ON (oi.trans_id = o.id)
1128 JOIN vendor ct ON (o.vendor_id = ct.id)
1129 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1131 AND o.vendor_id > 0|;
1133 push(@all_values, @quovalues);
1137 $query .= qq| ORDER BY | . $sortorder;
1141 $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
1144 # include individual items for assemblies
1145 if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
1147 qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
1149 p.sellprice, p.listprice, p.lastcost,
1150 p.rop, p.weight, p.priceupdate,
1151 p.image, p.drawing, p.microfiche
1152 FROM parts p, assembly a
1153 WHERE (p.id = a.parts_id) AND (a.id = ?)|;
1154 $sth = prepare_query($form, $dbh, $query);
1156 foreach $item (@{ $form->{parts} }) {
1157 push(@assemblies, $item);
1158 do_statement($form, $sth, $query, conv_i($item->{id}));
1160 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
1161 $ref->{assemblyitem} = 1;
1162 push(@assemblies, $ref);
1166 push(@assemblies, { id => $item->{id} });
1170 # copy assemblies to $form->{parts}
1171 $form->{parts} = \@assemblies;
1175 $main::lxdebug->leave_sub();
1179 $main::lxdebug->enter_sub();
1181 my ($self, $myconfig, $form) = @_;
1183 my $where = '1 = 1';
1191 if ($item ne 'make') {
1192 foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) {
1194 $column =~ s/.*\.//;
1195 next unless ($form->{$column});
1196 $where .= qq| AND $item ILIKE ?|;
1197 push(@where_values, '%' . $form->{$column} . '%');
1201 # special case for description
1202 if ($form->{description}
1203 && !( $form->{bought} || $form->{sold} || $form->{onorder}
1204 || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
1205 $where .= qq| AND (p.description ILIKE ?)|;
1206 push(@where_values, '%' . $form->{description} . '%');
1209 # special case for serialnumber
1210 if ($form->{l_serialnumber} && $form->{serialnumber}) {
1211 $where .= qq| AND serialnumber ILIKE ?|;
1212 push(@where_values, '%' . $form->{serialnumber} . '%');
1216 # items which were never bought, sold or on an order
1217 if ($form->{itemstatus} eq 'orphaned') {
1218 $form->{onhand} = $form->{short} = 0;
1219 $form->{bought} = $form->{sold} = 0;
1220 $form->{onorder} = $form->{ordered} = 0;
1221 $form->{rfq} = $form->{quoted} = 0;
1223 $form->{transdatefrom} = $form->{transdateto} = "";
1226 qq| AND (p.onhand = 0)
1229 SELECT DISTINCT parts_id FROM invoice
1231 SELECT DISTINCT parts_id FROM assembly
1233 SELECT DISTINCT parts_id FROM orderitems
1237 if ($form->{itemstatus} eq 'active') {
1238 $where .= qq| AND p.obsolete = '0'|;
1241 if ($form->{itemstatus} eq 'obsolete') {
1242 $where .= qq| AND p.obsolete = '1'|;
1243 $form->{onhand} = $form->{short} = 0;
1246 if ($form->{itemstatus} eq 'onhand') {
1247 $where .= qq| AND p.onhand > 0|;
1250 if ($form->{itemstatus} eq 'short') {
1251 $where .= qq| AND p.onhand < p.rop|;
1254 foreach my $column (qw(make model)) {
1255 next unless ($form->{$colum});
1256 $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|;
1257 push(@where_values, '%' . $form->{$column} . '%');
1260 # connect to database
1261 my $dbh = $form->dbconnect_noauto($myconfig);
1263 for my $column (qw(sellprice listprice)) {
1264 next if ($form->{$column} eq "");
1266 my $value = $form->parse_amount($myconfig, $form->{$column});
1269 if ($form->{"${column}_type"} eq "percent") {
1270 $value = ($value / 100) + 1;
1275 qq|UPDATE parts SET $column = $column $operator ?
1279 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1281 do_query($from, $dbh, $query, $value, @where_values);
1285 qq|UPDATE prices SET price = price + ?
1289 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1290 WHERE $where) AND (pricegroup_id = ?)|;
1291 my $sth_add = prepare_query($form, $dbh, $q_add);
1294 qq|UPDATE prices SET price = price * ?
1298 LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
1299 WHERE $where) AND (pricegroup_id = ?)|;
1300 my $sth_multiply = prepare_query($form, $dbh, $q_multiply);
1302 for my $i (1 .. $form->{price_rows}) {
1303 next if ($form->{"price_$i"} eq "");
1305 my $value = $form->parse_amount($myconfig, $form->{"price_$i"});
1307 if ($form->{"pricegroup_type_$i"} eq "percent") {
1308 do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1310 do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"}));
1315 $sth_multiply->finish();
1317 my $rc= $dbh->commit;
1320 $main::lxdebug->leave_sub();
1326 $main::lxdebug->enter_sub();
1328 my ($self, $module, $myconfig, $form) = @_;
1330 # connect to database
1331 my $dbh = $form->dbconnect($myconfig);
1333 my @values = ('%' . $module . '%');
1337 qq|SELECT c.accno, c.description, c.link, c.id,
1338 p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
1339 FROM chart c, parts p
1340 WHERE (c.link LIKE ?) AND (p.id = ?)
1342 push(@values, conv_i($form->{id}));
1346 qq|SELECT c.accno, c.description, c.link, c.id,
1347 d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
1348 FROM chart c, defaults d
1353 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1354 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1355 foreach my $key (split(/:/, $ref->{link})) {
1356 if ($key =~ /$module/) {
1357 if ( ($ref->{id} eq $ref->{inventory_accno_id})
1358 || ($ref->{id} eq $ref->{income_accno_id})
1359 || ($ref->{id} eq $ref->{expense_accno_id})) {
1360 push @{ $form->{"${module}_links"}{$key} },
1361 { accno => $ref->{accno},
1362 description => $ref->{description},
1363 selected => "selected" };
1364 $form->{"${key}_default"} = "$ref->{accno}--$ref->{description}";
1366 push @{ $form->{"${module}_links"}{$key} },
1367 { accno => $ref->{accno},
1368 description => $ref->{description},
1376 # get buchungsgruppen
1377 $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM buchungsgruppen|);
1380 $form->{payment_terms} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM payment_terms ORDER BY sortkey|);
1383 ($form->{priceupdate}) = selectrow_query($form, $dbh, qq|SELECT current_date|);
1387 $main::lxdebug->leave_sub();
1390 # get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
1392 $main::lxdebug->enter_sub();
1394 my ($self, $myconfig, $form, $sortorder) = @_;
1395 my $dbh = $form->dbconnect($myconfig);
1396 my $order = qq| p.partnumber|;
1397 my $where = qq|1 = 1|;
1400 if ($sortorder eq "all") {
1401 $where .= qq| AND (partnumber ILIKE ?) AND (description ILIKE ?)|;
1402 push(@values, '%' . $form->{partnumber} . '%', '%' . $form->{description} . '%');
1404 } elsif ($sortorder eq "partnumber") {
1405 $where .= qq| AND (partnumber ILIKE ?)|;
1406 push(@values, '%' . $form->{partnumber} . '%');
1408 } elsif ($sortorder eq "description") {
1409 $where .= qq| AND (description ILIKE ?)|;
1410 push(@values, '%' . $form->{description} . '%');
1411 $order = "description";
1416 qq|SELECT id, partnumber, description, unit, sellprice
1418 WHERE $where ORDER BY $order|;
1420 my $sth = prepare_execute_query($form, $dbh, $query, @values);
1423 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1424 if (($ref->{partnumber} eq "*") && ($ref->{description} eq "")) {
1429 $form->{"id_$j"} = $ref->{id};
1430 $form->{"partnumber_$j"} = $ref->{partnumber};
1431 $form->{"description_$j"} = $ref->{description};
1432 $form->{"unit_$j"} = $ref->{unit};
1433 $form->{"sellprice_$j"} = $ref->{sellprice};
1434 $form->{"soldtotal_$j"} = get_soldtotal($dbh, $ref->{id});
1440 $main::lxdebug->leave_sub();
1445 # gets sum of sold part with part_id
1447 $main::lxdebug->enter_sub();
1449 my ($dbh, $id) = @_;
1451 my $query = qq|SELECT sum(qty) FROM invoice WHERE parts_id = ?|;
1452 my ($sum) = selectrow_query($form, $dbh, $query, conv_i($id));
1455 $main::lxdebug->leave_sub();
1458 } #end get_soldtotal
1460 sub retrieve_languages {
1461 $main::lxdebug->enter_sub();
1463 my ($self, $myconfig, $form) = @_;
1465 # connect to database
1466 my $dbh = $form->dbconnect($myconfig);
1471 if ($form->{language_values} ne "") {
1473 qq|SELECT l.id, l.description, tr.translation, tr.longdescription
1475 LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|;
1476 @values = (conv_i($form->{id}));
1479 $query = qq|SELECT id, description FROM language|;
1482 my $languages = selectall_hashref_query($form, $dbh, $query, @values);
1486 $main::lxdebug->leave_sub();
1491 sub follow_account_chain {
1492 $main::lxdebug->enter_sub(2);
1494 my ($self, $form, $dbh, $transdate, $accno_id, $accno) = @_;
1496 my @visited_accno_ids = ($accno_id);
1501 qq|SELECT c.new_chart_id, date($transdate) >= c.valid_from AS is_valid, | .
1504 qq|LEFT JOIN chart cnew ON c.new_chart_id = cnew.id | .
1505 qq|WHERE (c.id = ?) AND NOT c.new_chart_id ISNULL AND (c.new_chart_id > 0)|;
1506 $sth = prepare_query($form, $dbh, $query);
1509 do_statement($form, $sth, $query, $accno_id);
1510 $ref = $sth->fetchrow_hashref();
1511 last unless ($ref && $ref->{"is_valid"} &&
1512 !grep({ $_ == $ref->{"new_chart_id"} } @visited_accno_ids));
1513 $accno_id = $ref->{"new_chart_id"};
1514 $accno = $ref->{"accno"};
1515 push(@visited_accno_ids, $accno_id);
1518 $main::lxdebug->leave_sub(2);
1520 return ($accno_id, $accno);
1523 sub retrieve_accounts {
1524 $main::lxdebug->enter_sub(2);
1526 my ($self, $myconfig, $form, $parts_id, $index, $copy_accnos) = @_;
1528 my ($query, $sth, $dbh);
1530 $form->{"taxzone_id"} *= 1;
1532 $dbh = $form->dbconnect($myconfig);
1535 if ($form->{type} eq "invoice") {
1536 if (($form->{vc} eq "vendor") || !$form->{deliverydate}) {
1537 $transdate = $form->{invdate};
1539 $transdate = $form->{deliverydate};
1541 } elsif ($form->{type} eq "credit_note") {
1542 $transdate = $form->{invdate};
1544 $transdate = $form->{transdate};
1547 if ($transdate eq "") {
1548 $transdate = "current_date";
1550 $transdate = $dbh->quote($transdate);
1555 qq| p.inventory_accno_id AS is_part, | .
1556 qq| bg.inventory_accno_id, | .
1557 qq| bg.income_accno_id_$form->{taxzone_id} AS income_accno_id, | .
1558 qq| bg.expense_accno_id_$form->{taxzone_id} AS expense_accno_id, | .
1559 qq| c1.accno AS inventory_accno, | .
1560 qq| c2.accno AS income_accno, | .
1561 qq| c3.accno AS expense_accno | .
1563 qq|LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id | .
1564 qq|LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id | .
1565 qq|LEFT JOIN chart c2 ON bg.income_accno_id_$form->{taxzone_id} = c2.id | .
1566 qq|LEFT JOIN chart c3 ON bg.expense_accno_id_$form->{taxzone_id} = c3.id | .
1568 my $ref = selectfirst_hashref_query($form, $dbh, $query, $parts_id);
1572 return $main::lxdebug->leave_sub(2);
1575 $ref->{"inventory_accno_id"} = undef unless ($ref->{"is_part"});
1578 foreach my $type (qw(inventory income expense)) {
1579 next unless ($ref->{"${type}_accno_id"});
1580 ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
1581 $self->follow_account_chain($form, $dbh, $transdate,
1582 $ref->{"${type}_accno_id"},
1583 $ref->{"${type}_accno"});
1586 map({ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} }
1587 qw(inventory income expense));
1589 my $inc_exp = $form->{"vc"} eq "customer" ? "income" : "expense";
1590 my $accno_id = $accounts{"${inc_exp}_accno_id"};
1593 qq|SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber | .
1595 qq|LEFT JOIN chart c ON c.id = t.chart_id | .
1596 qq|WHERE t.id IN | .
1597 qq| (SELECT tk.tax_id | .
1598 qq| FROM taxkeys tk | .
1599 qq| WHERE tk.chart_id = ? AND startdate <= | . quote_db_date($transdate) .
1600 qq| ORDER BY startdate DESC LIMIT 1) |;
1601 $ref = selectfirst_hashref_query($form, $dbh, $query, $accno_id);
1604 $main::lxdebug->leave_sub(2);
1608 $form->{"taxaccounts_$index"} = $ref->{"accno"};
1609 if ($form->{"taxaccounts"} !~ /$ref->{accno}/) {
1610 $form->{"taxaccounts"} .= "$ref->{accno} ";
1612 map({ $form->{"$ref->{accno}_${_}"} = $ref->{$_}; }
1613 qw(rate description taxnumber));
1615 # $main::lxdebug->message(0, "formvars: rate " . $form->{"$ref->{accno}_rate"} .
1616 # " description " . $form->{"$ref->{accno}_description"} .
1617 # " taxnumber " . $form->{"$ref->{accno}_taxnumber"} .
1618 # " || taxaccounts_$index " . $form->{"taxaccounts_$index"} .
1619 # " || taxaccounts " . $form->{"taxaccounts"});
1621 $main::lxdebug->leave_sub(2);