#===================================================================== # LedgerSMB # Small Medium Business Accounting software # http://www.ledgersmb.org/ # # Copyright (C) 2006 # This work contains copyrighted information from a number of sources all used # with permission. # # This file contains source code included with or based on SQL-Ledger which # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed # under the GNU General Public License version 2 or, at your option, any later # version. For a full list including contact information of contributors, # maintainers, and copyright holders, see the CONTRIBUTORS file. # # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork): # Copyright (C) 2000 # # Author: DWS Systems Inc. # Web: http://www.sql-ledger.org # # Contributors: # #====================================================================== # # This file has NOT undergone whitespace cleanup. # #====================================================================== # # Inventory Control backend # #====================================================================== package IC; sub get_part { my ($self, $myconfig, $form) = @_; # connect to db my $dbh = $form->{dbh}; my $i; my $query = qq| SELECT p.*, c1.accno AS inventory_accno, c1.description AS inventory_description, c2.accno AS income_accno, c2.description AS income_description, c3.accno AS expense_accno, c3.description AS expense_description, pg.partsgroup FROM parts p LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id) LEFT JOIN chart c2 ON (p.income_accno_id = c2.id) LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE p.id = ?|; my $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); my $ref = $sth->fetchrow_hashref(NAME_lc); # copy to $form variables for (keys %$ref) { $form->{$_} = $ref->{$_} } $sth->finish; # part, service item or labor $form->{item} = ($form->{inventory_accno_id}) ? 'part' : 'service'; $form->{item} = 'labor' if ! $form->{income_accno_id}; if ($form->{assembly}) { $form->{item} = 'assembly'; # retrieve assembly items $query = qq| SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, a.qty, a.bom, a.adj, p.unit, p.lastcost, p.listprice, pg.partsgroup, p.assembly, p.partsgroup_id FROM parts p JOIN assembly a ON (a.parts_id = p.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE a.id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); $form->{assembly_rows} = 0; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { $form->{assembly_rows}++; foreach my $key ( keys %{ $ref } ) { $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key}; } } $sth->finish; } # setup accno hash for <option checked> # {amount} is used in create_links for (qw(inventory income expense)) { $form->{amount}{"IC_$_"} = { accno => $form->{"${_}_accno"}, description => $form->{"${_}_description"} }; }; if ($form->{item} =~ /(part|assembly)/) { if ($form->{makemodel} ne "") { $query = qq| SELECT make, model FROM makemodel WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{makemodels} }, $ref; } $sth->finish; } } # now get accno for taxes $query = qq| SELECT c.accno FROM chart c, partstax pt WHERE pt.chart_id = c.id AND pt.parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); while (($key) = $sth->fetchrow_array) { $form->{amount}{$key} = $key; } $sth->finish; my $id = $dbh->quote($form->{id}); # is it an orphan $query = qq| SELECT parts_id FROM invoice WHERE parts_id = $id UNION SELECT parts_id FROM orderitems WHERE parts_id = $id UNION SELECT parts_id FROM assembly WHERE parts_id = $id UNION SELECT parts_id FROM jcitems WHERE parts_id = $id|; ($form->{orphaned}) = $dbh->selectrow_array($query); $form->{orphaned} = !$form->{orphaned}; $form->{orphaned} = 0 if $form->{project_id}; if ($form->{item} eq 'assembly') { if ($form->{orphaned}) { $form->{orphaned} = !$form->{onhand}; } } if ($form->{item} =~ /(part|service)/) { # get vendors $query = qq| SELECT v.id, v.name, pv.partnumber, pv.lastcost, pv.leadtime, pv.curr AS vendorcurr FROM partsvendor pv JOIN vendor v ON (v.id = pv.vendor_id) WHERE pv.parts_id = ? ORDER BY 2|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{vendormatrix} }, $ref; } $sth->finish; } # get matrix if ($form->{item} ne 'labor') { $query = qq| SELECT pc.pricebreak, pc.sellprice AS customerprice, pc.curr AS customercurr, pc.validfrom, pc.validto, c.name, c.id AS cid, g.pricegroup, g.id AS gid FROM partscustomer pc LEFT JOIN customer c ON (c.id = pc.customer_id) LEFT JOIN pricegroup g ON (g.id = pc.pricegroup_id) WHERE pc.parts_id = ? ORDER BY c.name, g.pricegroup, pc.pricebreak|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{customermatrix} }, $ref; } $sth->finish; } $form->run_custom_queries('parts', 'SELECT'); } sub save { my ($self, $myconfig, $form) = @_; ($form->{inventory_accno}) = split(/--/, $form->{IC_inventory}); ($form->{expense_accno}) = split(/--/, $form->{IC_expense}); ($form->{income_accno}) = split(/--/, $form->{IC_income}); my $dbh = $form->{dbh}; # undo amount formatting for (qw(rop weight listprice sellprice lastcost stock)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}); } $form->{makemodel} = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0; $form->{assembly} = ($form->{item} eq 'assembly') ? 1 : 0; for (qw(alternate obsolete onhand)) { $form->{$_} *= 1 } my $query; my $sth; my $i; my $null; my $vendor_id; my $customer_id; if ($form->{id}) { # get old price $query = qq| SELECT id, listprice, sellprice, lastcost, weight, project_id FROM parts WHERE id = ?|; my $sth = $dbh->prepare($query); $sth->execute($form->{id}); my ($id, $listprice, $sellprice, $lastcost, $weight, $project_id) = $sth->fetchrow_array(); if ($id) { if (!$project_id) { # if item is part of an assembly # adjust all assemblies $query = qq| SELECT id, qty, adj FROM assembly WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); while (my ($id, $qty, $adj) = $sth->fetchrow_array) { &update_assembly( $dbh, $form, $id, $qty, $adj, $listprice * 1, $sellprice * 1, $lastcost * 1, $weight * 1); } $sth->finish; } if ($form->{item} =~ /(part|service)/) { # delete partsvendor records $query = qq| DELETE FROM partsvendor WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } if ($form->{item} !~ /(service|labor)/) { # delete makemodel records $query = qq| DELETE FROM makemodel WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } if ($form->{item} eq 'assembly') { if ($form->{onhand}) { &adjust_inventory( $dbh, $form, $form->{id}, $form->{onhand} * -1); } if ($form->{orphaned}) { # delete assembly records $query = qq| DELETE FROM assembly WHERE id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } else { for $i (1 .. $form->{assembly_rows} - 1) { # update BOM, A only for (qw(bom adj)) { $form->{"${_}_$i"} *= 1; } $query = qq| UPDATE assembly SET bom = ?, adj = ? WHERE id = ? AND parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute( $form->{"bom_$i"}, $form->{"adj_$i"}, $form->{id}, $form->{"id_$i"} )|| $form->dberror( $query); } } $form->{onhand} += $form->{stock}; } # delete tax records $query = qq|DELETE FROM partstax WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id})|| $form->dberror($query); # delete matrix $query = qq| DELETE FROM partscustomer WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id})|| $form->dberror($query); } else { $query = qq|INSERT INTO parts (id) VALUES (?)|; $sth = $dbh->prepare($query); $sth->execute($form->{id})|| $form->dberror($query); } } if (!$form->{id}) { my $uid = localtime; $uid .= "$$"; $query = qq|INSERT INTO parts (partnumber) VALUES ('$uid')|; $dbh->do($query) || $form->dberror($query); $query = qq|SELECT id FROM parts WHERE partnumber = '$uid'|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); ($form->{id}) = $sth->fetchrow_array; $sth->finish; $form->{orphaned} = 1; $form->{onhand} = ($form->{stock} * 1) if $form->{item} eq 'assembly'; } my $partsgroup_id; ($null, $partsgroup_id) = split /--/, $form->{partsgroup}; $partsgroup_id *= 1; $form->{partnumber} = $form->update_defaults( $myconfig, "partnumber", $dbh) if ! $form->{partnumber}; if (!$form->{priceupdate}){ $form->{priceupdate} = 'now'; } $query = qq| UPDATE parts SET partnumber = ?, description = ?, makemodel = ?, alternate = ?, assembly = ?, listprice = ?, sellprice = ?, lastcost = ?, weight = ?, priceupdate = ?, unit = ?, notes = ?, rop = ?, bin = ?, inventory_accno_id = (SELECT id FROM chart WHERE accno = ?), income_accno_id = (SELECT id FROM chart WHERE accno = ?), expense_accno_id = (SELECT id FROM chart WHERE accno = ?), obsolete = ?, image = ?, drawing = ?, microfiche = ?, partsgroup_id = ? WHERE id = ?|; $sth = $dbh->prepare($query); $sth->execute( $form->{partnumber}, $form->{description}, $form->{makemodel}, $form->{alternate}, $form->{assembly}, $form->{listprice}, $form->{sellprice}, $form->{lastcost}, $form->{weight}, $form->{priceupdate}, $form->{unit}, $form->{notes}, $form->{rop}, $form->{bin}, $form->{inventory_accno}, $form->{income_accno}, $form->{expense_accno}, $form->{obsolete}, $form->{image}, $form->{drawing}, $form->{microfiche}, $partsgroup_id, $form->{id} ) || $form->dberror($query); # insert makemodel records if ($form->{item} =~ /(part|assembly)/) { $query = qq| INSERT INTO makemodel (parts_id, make, model) VALUES (?, ?, ?)|; $sth = $dbh->prepare($query) || $form->dberror($query); for $i (1 .. $form->{makemodel_rows}) { if (($form->{"make_$i"} ne "") || ($form->{"model_$i"} ne "")) { $sth->execute( $form->{id}, $form->{"make_$i"}, $form->{"model_$i"} ) || $form->dberror($query); } } } # insert taxes $query = qq| INSERT INTO partstax (parts_id, chart_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?))|; $sth = $dbh->prepare($query); for (split / /, $form->{taxaccounts}) { if ($form->{"IC_tax_$_"}) { $sth->execute($form->{id}, $_) || $form->dberror($query); } } @a = localtime; $a[5] += 1900; $a[4]++; $a[4] = substr("0$a[4]", -2); $a[3] = substr("0$a[3]", -2); my $shippingdate = "$a[5]$a[4]$a[3]"; ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); # add assembly records if ($form->{item} eq 'assembly' && !$project_id) { if ($form->{orphaned}) { $query = qq| INSERT INTO assembly (id, parts_id, qty, bom, adj) VALUES (?, ?, ?, ?, ?)|; $sth = $dbh->prepare($query); for $i (1 .. $form->{assembly_rows}) { $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"}); if(!$form->{"bom_$i"}){ $form->{"bom_$i"} = undef; } if ($form->{"id_$i"} && $form->{"qty_$i"}){ $sth->execute( $form->{id}, $form->{"id_$i"}, $form->{"qty_$i"}, $form->{"bom_$i"} || 0, $form->{"adj_$i"} ) || $form->dberror($query); } } } # adjust onhand for the parts if ($form->{onhand}) { &adjust_inventory( $dbh, $form, $form->{id}, $form->{onhand}); } } # add vendors if ($form->{item} ne 'assembly') { $updparts{$form->{id}} = 1; for $i (1 .. $form->{vendor_rows}) { if (($form->{"vendor_$i"} ne "") && $form->{"lastcost_$i"}) { ($null, $vendor_id) = split /--/, $form->{"vendor_$i"}; for (qw(lastcost leadtime)) { $form->{"${_}_$i"} = $form->parse_amount( $myconfig, $form->{"${_}_$i"}); } $query = qq| INSERT INTO partsvendor (vendor_id, parts_id, partnumber, lastcost, leadtime, curr) VALUES (?, ?, ?, ?, ?, ?)|; $sth = $dbh->prepare($query); $sth->execute( $vendor_id, $form->{id}, $form->{"partnumber_$i"}, $form->{"lastcost_$i"}, $form->{"leadtime_$i"}, $form->{"vendorcurr_$i"} )|| $form->dberror($query); } } } # add pricematrix for $i (1 .. $form->{customer_rows}) { for (qw(pricebreak customerprice)) { $form->{"${_}_$i"} = $form->parse_amount( $myconfig, $form->{"${_}_$i"}); } if ($form->{"customerprice_$i"}) { ($null, $customer_id) = split /--/, $form->{"customer_$i"}; $customer_id *= 1; ($null, $pricegroup_id) = split /--/, $form->{"pricegroup_$i"}; my $validfrom; my $validto; $validfrom = $form->{"validfrom_$i"} if $form->{"validfrom_$i"}; $validto = $form->{"validto_$i"} if $form->{"validto_$i"}; $query = qq| INSERT INTO partscustomer (parts_id, customer_id, pricegroup_id, pricebreak, sellprice, curr, validfrom, validto) VALUES (?, ?, ?, ?, ?, ?, ?, ?)|; $sth = $dbh->prepare($query); $sth->execute( $form->{id}, $customer_id, $pricegroup_id, $form->{"pricebreak_$i"}, $form->{"customerprice_$i"}, $form->{"customercurr_$i"}, $validfrom, $validto )|| $form->dberror($query); } } my $rc = $dbh->commit; $form->run_custom_queries('parts', 'UPDATE'); $rc; } sub update_assembly { my ($dbh, $form, $id, $qty, $adj, $listprice, $sellprice, $lastcost, $weight) = @_; my $formlistprice = $form->{listprice}; my $formsellprice = $form->{sellprice}; if (!$adj) { $formlistprice = $listprice; $formsellprice = $sellprice; } my $query = qq|SELECT id, qty, adj FROM assembly WHERE parts_id = ?|; my $sth = $dbh->prepare($query); $sth->execute($id) || $form->dberror($query); $form->{$id} = 1; # Not sure what this is for... # In fact, we don't seem to use it... Chris T while (my ($pid, $aqty, $aadj) = $sth->fetchrow_array) { &update_assembly($dbh, $form, $pid, $aqty * $qty, $aadj, $listprice, $sellprice, $lastcost, $weight) if !$form->{$pid}; } $sth->finish; $qty = $dbh->quote($qty); $formlistprice = $dbh->quote($formlistprice - $listprice); $formsellprice = $dbh->quote($formsellprice - $sellprice); $formlastcost = $dbh->quote($form->{lastcost} - $lastcost); $weight = $dbh->quote($form->{weight} - $weight); $id = $dbh->quote($id); $query = qq| UPDATE parts SET listprice = listprice + $qty * cast($formlistprice AS numeric), sellprice = sellprice + $qty * cast($formsellprice AS numeric), lastcost = lastcost + $qty * cast($formlastcost AS numeric), weight = weight + $qty * cast($weight AS numeric) WHERE id = $id|; $dbh->do($query) || $form->dberror($query); delete $form->{$id}; } sub retrieve_assemblies { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->{dbh}; my $where = '1 = 1'; if ($form->{partnumber} ne "") { my $partnumber = $dbh->quote($form->like( lc $form->{partnumber})); $where .= " AND lower(p.partnumber) LIKE $partnumber"; } if ($form->{description} ne "") { my $description = $dbh->($form->like(lc $form->{description})); $where .= " AND lower(p.description) LIKE $description"; } $where .= qq| AND p.obsolete = '0' AND p.project_id IS NULL|; my %ordinal = ( 'partnumber' => 2, 'description' => 3, 'bin' => 4 ); my @a = qw(partnumber description bin); my $sortorder = $form->sort_order(\@a, \%ordinal); # retrieve assembly items my $query = qq| SELECT p.id, p.partnumber, p.description, p.bin, p.onhand, p.rop FROM parts p WHERE $where AND p.assembly = '1' ORDER BY $sortorder|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $query = qq| SELECT sum(p.inventory_accno_id), p.assembly FROM parts p JOIN assembly a ON (a.parts_id = p.id) WHERE a.id = ? GROUP BY p.assembly|; my $svh = $dbh->prepare($query) || $form->dberror($query); my $inh; if ($form->{checkinventory}) { $query = qq| SELECT p.id, p.onhand, a.qty FROM parts p JOIN assembly a ON (a.parts_id = p.id) WHERE (p.inventory_accno_id > 0 OR p.assembly) AND p.income_accno_id > 0 AND a.id = ?|; $inh = $dbh->prepare($query) || $form->dberror($query); } my %available = (); my %required; my $ref; my $aref; my $stock; my $howmany; my $ok; while ($ref = $sth->fetchrow_hashref(NAME_lc)) { $svh->execute($ref->{id}); ($ref->{inventory}, $ref->{assembly}) = $svh->fetchrow_array; $svh->finish; if ($ref->{inventory} || $ref->{assembly}) { $ok = 1; if ($form->{checkinventory}) { $inh->execute($ref->{id}) || $form->dberror($query);; $ok = 0; %required = (); while ($aref = $inh->fetchrow_hashref(NAME_lc)) { $available{$aref->{id}} = (exists $available{$aref->{id}}) ? $available{$aref->{id}} : $aref->{onhand}; $required{$aref->{id}} = $aref->{qty}; if ($available{$aref->{id}} >= $aref->{qty}) { $howmany = ($aref->{qty}) ? int $available{ $aref->{id}} /$aref->{qty} : 1; if ($stock) { $stock = ($stock > $howmany) ? $howmany : $stock; } else { $stock = $howmany; } $ok = 1; $available{$aref->{id}} -= $aref->{qty} * $stock; } else { $ok = 0; for (keys %required) { $available{$_} += $required{$_} * $stock; } $stock = 0; last; } } $inh->finish; $ref->{stock} = $stock; } push @{ $form->{assembly_items} }, $ref if $ok; } } $sth->finish; $dbh->commit; } sub restock_assemblies { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->{dbh}; for my $i (1 .. $form->{rowcount}) { $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"}); if ($form->{"qty_$i"}) { &adjust_inventory( $dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"}); } } my $rc = $dbh->commit; $rc; } sub adjust_inventory { # Private method. Do not commit transaction at end of function... my ($dbh, $form, $id, $qty) = @_; my $query = qq| SELECT p.id, p.inventory_accno_id, p.assembly, a.qty FROM parts p JOIN assembly a ON (a.parts_id = p.id) WHERE a.id = ?|; my $sth = $dbh->prepare($query); $sth->execute($id) || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { # is it a service item then loop if (! $ref->{inventory_accno_id}) { next if ! $ref->{assembly}; } # adjust parts onhand $form->update_balance( $dbh, "parts", "onhand", qq|id = $ref->{id}|, $qty * $ref->{qty} * -1); } $sth->finish; # update assembly $form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $qty); } sub delete { my ($self, $myconfig, $form) = @_; my $dbh = $form->{dbh}; my $query; $query = qq|DELETE FROM parts WHERE id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); $query = qq|DELETE FROM partstax WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); if ($form->{item} ne 'assembly') { $query = qq|DELETE FROM partsvendor WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } # check if it is a part, assembly or service if ($form->{item} ne 'service') { $query = qq|DELETE FROM makemodel WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } if ($form->{item} eq 'assembly') { $query = qq|DELETE FROM assembly WHERE id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); } $query = qq|DELETE FROM inventory WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); $query = qq|DELETE FROM partscustomer WHERE parts_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); $query = qq|DELETE FROM translation WHERE trans_id = ?|; $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query); # commit my $rc = $dbh->commit; $rc; } sub assembly_item { my ($self, $myconfig, $form) = @_; my $dbh = $form->{dbh}; my $i = $form->{assembly_rows}; my $var; my $null; my $where = "p.obsolete = '0'"; if ($form->{"partnumber_$i"} ne "") { $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"})); $where .= " AND lower(p.partnumber) LIKE $var"; } if ($form->{"description_$i"} ne "") { $var = $dbh->quote($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 = $dbh->quote($var); $where .= qq| AND p.partsgroup_id = $var|; } if ($form->{id}) { $where .= " AND p.id != ".$dbh->quote($form->{id}); } if ($form->{"description_$i"} ne "") { $where .= " ORDER BY p.description"; } else { $where .= " ORDER BY p.partnumber"; } my $query = qq| SELECT p.id, p.partnumber, p.description, p.sellprice, p.weight, p.onhand, p.unit, p.lastcost, pg.partsgroup, p.partsgroup_id FROM parts p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE $where|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{item_list} }, $ref; } $sth->finish; } sub all_parts { my ($self, $myconfig, $form) = @_; $dbh = $form->{dbh}; my $where = '1 = 1'; my $null; my $var; my $ref; for (qw(partnumber drawing microfiche)) { if ($form->{$_} ne "") { $var = $dbh->quote($form->like(lc $form->{$_})); $where .= " AND lower(p.$_) LIKE $var"; } } # special case for description if ($form->{description} ne "") { unless ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered} || $form->{rfq} || $form->{quoted} ) { $var = $dbh->quote($form->like( lc $form->{description})); $where .= " AND lower(p.description) LIKE $var"; } } # assembly components my $assemblyflds; if ($form->{searchitems} eq 'component') { $assemblyflds = qq|, p1.partnumber AS assemblypartnumber, a.id AS assembly_id|; } # special case for serialnumber if ($form->{l_serialnumber}) { if ($form->{serialnumber} ne "") { $var = $dbh->quote( $form->like(lc $form->{serialnumber})); $where .= " AND lower(i.serialnumber) LIKE $var"; } } if (($form->{warehouse} ne "") || $form->{l_warehouse}) { $form->{l_warehouse} = 1; } if ($form->{searchitems} eq 'part') { $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id > 0"; } if ($form->{searchitems} eq 'assembly') { $form->{bought} = ""; $where .= " AND p.assembly = '1'"; } if ($form->{searchitems} eq 'service') { $where .= " AND p.assembly = '0' AND p.inventory_accno_id IS NULL"; } if ($form->{searchitems} eq 'labor') { $where .= " AND p.inventory_accno_id > 0 AND p.income_accno_id IS NULL"; } # items which were never bought, sold or on an order if ($form->{itemstatus} eq 'orphaned') { $where .= qq| AND p.onhand = 0 AND p.id NOT IN (SELECT p.id FROM parts p JOIN invoice i ON (p.id = i.parts_id)) AND p.id NOT IN (SELECT p.id FROM parts p JOIN assembly a ON (p.id = a.parts_id)) AND p.id NOT IN (SELECT p.id FROM parts p JOIN orderitems o ON (p.id = o.parts_id)) AND p.id NOT IN (SELECT p.id FROM parts p JOIN jcitems j ON (p.id = j.parts_id))|; } if ($form->{itemstatus} eq 'active') { $where .= " AND p.obsolete = '0'"; } if ($form->{itemstatus} eq 'obsolete') { $where .= " AND p.obsolete = '1'"; } if ($form->{itemstatus} eq 'onhand') { $where .= " AND p.onhand > 0"; } if ($form->{itemstatus} eq 'short') { $where .= " AND p.onhand < p.rop"; } my $makemodelflds = qq|, '', ''|;; my $makemodeljoin; if (($form->{make} ne "") || $form->{l_make} || ($form->{model} ne "") || $form->{l_model}) { $makemodelflds = qq|, m.make, m.model|; $makemodeljoin = qq|LEFT JOIN makemodel m ON (m.parts_id = p.id)|; if ($form->{make} ne "") { $var = $dbh->quote($form->like(lc $form->{make})); $where .= " AND lower(m.make) LIKE $var"; } if ($form->{model} ne "") { $var = $dbh->quote($form->like(lc $form->{model})); $where .= " AND lower(m.model) LIKE $var"; } } if ($form->{partsgroup} ne "") { ($null, $var) = split /--/, $form->{partsgroup}; $where .= qq| AND p.partsgroup_id = | . $dbh->quote($var); } my %ordinal = ( 'partnumber' => 2, 'description' => 3, 'bin' => 6, 'priceupdate' => 13, 'drawing' => 15, 'microfiche' => 16, 'partsgroup' => 18, 'make' => 21, 'model' => 22, 'assemblypartnumber' => 23 ); my @a = qw(partnumber description); my $sortorder = $form->sort_order(\@a, \%ordinal); my $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|; my ($curr) = $dbh->selectrow_array($query); $curr =~ s/:.*//; $curr = $dbh->quote($curr); my $flds = qq| p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.avgcost, p.weight, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, $curr AS curr, c1.accno AS inventory, c2.accno AS income, c3.accno AS expense, p.notes $makemodelflds $assemblyflds |; $query = qq| SELECT $flds FROM parts p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id) LEFT JOIN chart c2 ON (c2.id = p.income_accno_id) LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id) $makemodeljoin WHERE $where ORDER BY $sortorder|; # redo query for components report if ($form->{searchitems} eq 'component') { $flds =~ s/p.onhand/a.qty AS onhand/; $query = qq| SELECT $flds FROM assembly a JOIN parts p ON (a.parts_id = p.id) JOIN parts p1 ON (a.id = p1.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id) LEFT JOIN chart c2 ON (c2.id = p.income_accno_id) LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id) $makemodeljoin WHERE $where ORDER BY $sortorder|; } # rebuild query for bought and sold items if ($form->{bought} || $form->{sold} || $form->{onorder} || $form->{ordered} || $form->{rfq} || $form->{quoted} ) { $form->sort_order(); @a = qw(partnumber description curr employee name serialnumber id); push @a, "invnumber" if ($form->{bought} || $form->{sold}); push @a, "ordnumber" if ($form->{onorder} || $form->{ordered}); push @a, "quonumber" if ($form->{rfq} || $form->{quoted}); %ordinal = ( 'partnumber' => 2, 'description' => 3, 'serialnumber' => 4, 'bin' => 7, 'priceupdate' => 14, 'partsgroup' => 19, 'invnumber' => 20, 'ordnumber' => 21, 'quonumber' => 22, 'name' => 24, 'employee' => 25, 'curr' => 26, 'make' => 29, 'model' => 30 ); $sortorder = $form->sort_order(\@a, \%ordinal); my $union = ""; $query = ""; if ($form->{bought} || $form->{sold}) { my $invwhere = "$where"; my $transdate = ($form->{method} eq 'accrual') ? "transdate" : "datepaid"; $invwhere .= " AND i.assemblyitem = '0'"; $invwhere .= " AND a.$transdate >= " . $dbh->quote($form->{transdatefrom}) if $form->{transdatefrom}; $invwhere .= " AND a.$transdate <= " . $dbh->quote($form->{transdateto}) if $form->{transdateto}; if ($form->{description} ne "") { $var = dbh->quote( $form->like(lc $form->{description})); $invwhere .= " AND lower(i.description) LIKE $var"; } if ($form->{open} || $form->{closed}) { if ($form->{open} && $form->{closed}) { if ($form->{method} eq 'cash') { $invwhere .= " AND a.amount = a.paid"; } } else { if ($form->{open}) { if ($form->{method} eq 'cash') { $invwhere .= " AND a.id = 0"; } else { $invwhere .= " AND NOT a.amount = a.paid"; } } else { $invwhere .= " AND a.amount = a.paid"; } } } else { $invwhere .= " AND a.id = 0"; } my $flds = qq| p.id, p.partnumber, i.description, i.serialnumber, i.qty AS onhand, i.unit, p.bin, i.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.avgcost, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, a.invnumber, a.ordnumber, a.quonumber, i.trans_id, ct.name, e.name AS employee, a.curr, a.till, p.notes $makemodelfld|; if ($form->{bought}) { my $rflds = $flds; $rflds =~ s/i.qty AS onhand/i.qty * -1 AS onhand/; $query = qq| SELECT $rflds, 'ir' AS module, '' AS type, (SELECT sell FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.$transdate) AS exchangerate, i.discount FROM invoice i JOIN parts p ON (p.id = i.parts_id) JOIN ap a ON (a.id = i.trans_id) JOIN vendor ct ON (a.vendor_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $invwhere|; $union = " UNION ALL"; } if ($form->{sold}) { $query .= qq| $union SELECT $flds, 'is' AS module, '' AS type, (SELECT buy FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.$transdate) AS exchangerate, i.discount FROM invoice i JOIN parts p ON (p.id = i.parts_id) JOIN ar a ON (a.id = i.trans_id) JOIN customer ct ON (a.customer_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $invwhere|; $union = " UNION ALL"; } } if ($form->{onorder} || $form->{ordered}) { my $ordwhere = "$where AND a.quotation = '0'"; $ordwhere .= " AND a.transdate >= ". $dbh->quote($form->{transdatefrom}) if $form->{transdatefrom}; $ordwhere .= " AND a.transdate <= ". $dbh->quote($form->{transdateto}) if $form->{transdateto}; if ($form->{description} ne "") { $var = $dbh->quote( $form->like(lc $form->{description})); $ordwhere .= " AND lower(i.description) LIKE $var"; } if ($form->{open} || $form->{closed}) { unless ($form->{open} && $form->{closed}) { $ordwhere .= " AND a.closed = '0'" if $form->{open}; $ordwhere .= " AND a.closed = '1'" if $form->{closed}; } } else { $ordwhere .= " AND a.id = 0"; } $flds = qq| p.id, p.partnumber, i.description, i.serialnumber, i.qty AS onhand, i.unit, p.bin, i.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.avgcost, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber, i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; if ($form->{ordered}) { $query .= qq| $union SELECT $flds, 'oe' AS module, 'sales_order' AS type, (SELECT buy FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.transdate) AS exchangerate, i.discount FROM orderitems i JOIN parts p ON (i.parts_id = p.id) JOIN oe a ON (i.trans_id = a.id) JOIN customer ct ON (a.customer_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $ordwhere AND a.customer_id > 0|; $union = " UNION ALL"; } if ($form->{onorder}) { $flds = qq| p.id, p.partnumber, i.description, i.serialnumber, i.qty AS onhand, i.unit, p.bin, i.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.avgcost, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber, i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; $query .= qq| $union SELECT $flds, 'oe' AS module, 'purchase_order' AS type, (SELECT sell FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.transdate) AS exchangerate, i.discount FROM orderitems i JOIN parts p ON (i.parts_id = p.id) JOIN oe a ON (i.trans_id = a.id) JOIN vendor ct ON (a.vendor_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $ordwhere AND a.vendor_id > 0|; } } if ($form->{rfq} || $form->{quoted}) { my $quowhere = "$where AND a.quotation = '1'"; $quowhere .= " AND a.transdate >= ". $dbh->quote($form->{transdatefrom}) if $form->{transdatefrom}; $quowhere .= " AND a.transdate <= ". $dbh->quote($form->{transdateto}) if $form->{transdateto}; if ($form->{description} ne "") { $var = $dbh->quote( $form->like(lc $form->{description})); $quowhere .= " AND lower(i.description) LIKE $var"; } if ($form->{open} || $form->{closed}) { unless ($form->{open} && $form->{closed}) { $ordwhere .= " AND a.closed = '0'" if $form->{open}; $ordwhere .= " AND a.closed = '1'" if $form->{closed}; } } else { $ordwhere .= " AND a.id = 0"; } $flds = qq| p.id, p.partnumber, i.description, i.serialnumber, i.qty AS onhand, i.unit, p.bin, i.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.avgcost, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber, i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; if ($form->{quoted}) { $query .= qq| $union SELECT $flds, 'oe' AS module, 'sales_quotation' AS type, (SELECT buy FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.transdate) AS exchangerate, i.discount FROM orderitems i JOIN parts p ON (i.parts_id = p.id) JOIN oe a ON (i.trans_id = a.id) JOIN customer ct ON (a.customer_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $quowhere AND a.customer_id > 0|; $union = " UNION ALL"; } if ($form->{rfq}) { $flds = qq| p.id, p.partnumber, i.description, i.serialnumber, i.qty AS onhand, i.unit, p.bin, i.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.avgcost, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, '' AS invnumber, a.ordnumber, a.quonumber, i.trans_id, ct.name, e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; $query .= qq| $union SELECT $flds, 'oe' AS module, 'request_quotation' AS type, (SELECT sell FROM exchangerate ex WHERE ex.curr = a.curr AND ex.transdate = a.transdate) AS exchangerate, i.discount FROM orderitems i JOIN parts p ON (i.parts_id = p.id) JOIN oe a ON (i.trans_id = a.id) JOIN vendor ct ON (a.vendor_id = ct.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) LEFT JOIN employee e ON (a.employee_id = e.id) $makemodeljoin WHERE $quowhere AND a.vendor_id > 0|; } } $query .= qq| ORDER BY $sortorder|; } my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $query = qq| SELECT c.accno FROM chart c JOIN partstax pt ON (pt.chart_id = c.id) WHERE pt.parts_id = ? ORDER BY accno|; my $pth = $dbh->prepare($query) || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { $pth->execute($ref->{id}); while (($accno) = $pth->fetchrow_array) { $ref->{tax} .= "$accno "; } $pth->finish; push @{ $form->{parts} }, $ref; } $sth->finish; @a = (); # include individual items for assembly if (($form->{searchitems} eq 'assembly') && $form->{individual}) { if ($form->{sold} || $form->{ordered} || $form->{quoted}) { $flds = qq| p.id, p.partnumber, p.description, p.onhand AS perassembly, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.avgcost, p.weight, p.priceupdate, p.image, p.drawing, p.microfiche, p.assembly, pg.partsgroup, p.notes $makemodelflds $assemblyflds |; } else { # replace p.onhand with a.qty AS onhand $flds =~ s/p\.onhand/a.qty AS perassembly/; } for (@{ $form->{parts} }) { push @a, $_; $_->{perassembly} = 1; $flds =~ s/p\.onhand*AS perassembly/p\.onhand, a\.qty AS perassembly/; push @a, &include_assembly( $dbh, $myconfig, $form, $_->{id}, $flds, $makemodeljoin); push @a, {id => $_->{id}, assemblyitem => 1}; } # copy assemblies to $form->{parts} @{ $form->{parts} } = @a; } @a = (); if (($form->{warehouse} ne "") || $form->{l_warehouse}) { if ($form->{warehouse} ne "") { my ($desc, $var) = split /--/, $form->{warehouse}; $var = $dbh->quote($var); $desc = $dbh->quote($desc); $query = qq| SELECT SUM(qty) AS onhand, $desc AS description FROM inventory WHERE warehouse_id = $var AND parts_id = ?|; } else { $query = qq| SELECT SUM(i.qty) AS onhand, w.description AS warehouse FROM inventory i JOIN warehouse w ON (w.id = i.warehouse_id) WHERE i.parts_id = ? GROUP BY w.description|; } $sth = $dbh->prepare($query) || $form->dberror($query); for (@{ $form->{parts} }) { $sth->execute($_->{id}) || $form->dberror($query); if ($form->{warehouse} ne "") { $ref = $sth->fetchrow_hashref(NAME_lc); if ($ref->{onhand} != 0) { $_->{onhand} = $ref->{onhand}; push @a, $_; } } else { push @a, $_; while ($ref = $sth->fetchrow_hashref(NAME_lc)) { if ($ref->{onhand} > 0) { push @a, $ref; } } } $sth->finish; } @{ $form->{parts} } = @a; } $dbh->commit; } sub include_assembly { my ($dbh, $myconfig, $form, $id, $flds, $makemodeljoin) = @_; $form->{stagger}++; if ($form->{stagger} > $form->{pncol}) { $form->{pncol} = $form->{stagger}; } $form->{$id} = 1; my @a = qw(partnumber description bin); if ($form->{sort} eq 'partnumber') { $sortorder = "TRUE"; } else { @a = grep !/$form->{sort}/, @a; $sortorder = "$form->{sort} $form->{direction}, ". join ',', @a; } @a = (); my $query = qq| SELECT $flds FROM parts p JOIN assembly a ON (a.parts_id = p.id) LEFT JOIN partsgroup pg ON (pg.id = p.id) LEFT JOIN chart c1 ON (c1.id = p.inventory_accno_id) LEFT JOIN chart c2 ON (c2.id = p.income_accno_id) LEFT JOIN chart c3 ON (c3.id = p.expense_accno_id) $makemodeljoin WHERE a.id = ? ORDER BY $sortorder|; my $sth = $dbh->prepare($query); $sth->execute($id) || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { $ref->{assemblyitem} = 1; $ref->{stagger} = $form->{stagger}; push @a, $ref; if ($ref->{assembly} && !$form->{$ref->{id}}) { push @a, &include_assembly( $dbh, $myconfig, $form, $ref->{id}, $flds, $makemodeljoin); if ($form->{stagger} > $form->{pncol}) { $form->{pncol} = $form->{stagger}; } } } $sth->finish; $form->{$id} = 0; $form->{stagger}--; @a; } sub requirements { my ($self, $myconfig, $form) = @_; my $dbh = $form->{dbh}; my $null; my $var; my $ref; my $where = qq|p.obsolete = '0'|; my $dwhere; for (qw(partnumber description)) { if ($form->{$_} ne "") { $var = $dbh->quote($form->like(lc $form->{$_})); $where .= qq| AND lower(p.$_) LIKE $var|; } } if ($form->{partsgroup} ne "") { ($null, $var) = split /--/, $form->{partsgroup}; $var = $dbh->quote($var); $where .= qq| AND p.partsgroup_id = $var|; } # connect to database my ($transdatefrom, $transdateto); if ($form->{year}) { ($transdatefrom, $transdateto) = $form->from_to($form->{year}, '01', 12); $dwhere = qq| AND a.transdate >= '$transdatefrom' AND a.transdate <= '$transdateto'|; } $query = qq| SELECT p.id, p.partnumber, p.description, sum(i.qty) AS qty, p.onhand, extract(MONTH FROM a.transdate) AS month, '0' AS so, '0' AS po FROM invoice i JOIN parts p ON (p.id = i.parts_id) JOIN ar a ON (a.id = i.trans_id) WHERE $where $dwhere AND p.inventory_accno_id > 0 GROUP BY p.id, p.partnumber, p.description, p.onhand, extract(MONTH FROM a.transdate)|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); my %parts; while ($ref = $sth->fetchrow_hashref(NAME_lc)) { $parts{$ref->{id}} = $ref; } $sth->finish; my %ofld = ( customer => so, vendor => po ); for (qw(customer vendor)) { $query = qq| SELECT p.id, p.partnumber, p.description, sum(qty) - sum(ship) AS $ofld{$_}, p.onhand, 0 AS month FROM orderitems i JOIN parts p ON (p.id = i.parts_id) JOIN oe a ON (a.id = i.trans_id) WHERE $where AND p.inventory_accno_id > 0 AND p.assembly = '0' AND a.closed = '0' AND a.${_}_id > 0 GROUP BY p.id, p.partnumber, p.description, p.onhand, month|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { if (exists $parts{$ref->{id}}->{$ofld{$_}}) { $parts{$ref->{id}}->{$ofld{$_}} += $ref->{$ofld{$_}}; } else { $parts{$ref->{id}} = $ref; } } $sth->finish; } # add assemblies from open sales orders $query = qq| SELECT DISTINCT a.id AS orderid, b.id, i.qty - i.ship AS qty FROM parts p JOIN assembly b ON (b.parts_id = p.id) JOIN orderitems i ON (i.parts_id = b.id) JOIN oe a ON (a.id = i.trans_id) WHERE $where AND (p.inventory_accno_id > 0 OR p.assembly = '1') AND a.closed = '0'|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { &requirements_assembly( $dbh, $form, \%parts, $ref->{id}, $ref->{qty}, $where) if $ref->{qty}; } $sth->finish; $dbh->commit; for (sort { $parts{$a}->{$form->{sort}} cmp $parts{$b}->{$form->{sort}}} keys %parts) { push @{ $form->{parts} }, $parts{$_}; } } sub requirements_assembly { my ($dbh, $form, $parts, $id, $qty, $where) = @_; # assemblies my $query = qq| SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, p.onhand, p.assembly, p.partsgroup_id FROM assembly a JOIN parts p ON (p.id = a.parts_id) WHERE $where AND a.id = ? AND p.inventory_accno_id > 0 UNION SELECT p.id, p.partnumber, p.description, a.qty * ? AS so, p.onhand, p.assembly, p.partsgroup_id FROM assembly a JOIN parts p ON (p.id = a.parts_id) WHERE a.id = ? AND p.assembly = '1'|; my $sth = $dbh->prepare($query); $sth->execute($qty, $id, $qty, $id) || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { if ($ref->{assembly}) { &requirements_assembly( $dbh, $form, $parts, $ref->{id}, $ref->{so}, $where); next; } if (exists $parts->{$ref->{id}}{so}) { $parts->{$ref->{id}}{so} += $ref->{so}; } else { $parts->{$ref->{id}} = $ref; } } $sth->finish; } sub create_links { my ($self, $module, $myconfig, $form) = @_; # connect to database my $dbh = $form->{dbh}; my $ref; my $query = qq| SELECT accno, description, link FROM chart WHERE link LIKE ? ORDER BY accno|; my $sth = $dbh->prepare($query); $sth->execute("%$module%") || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { foreach my $key (split /:/, $ref->{link}) { if ($key =~ /$module/) { push @{ $form->{"${module}_links"}{$key} }, { accno => $ref->{accno}, description => $ref->{description} }; } } } $sth->finish; if ($form->{item} ne 'assembly') { $query = qq|SELECT count(*) FROM vendor|; my ($count) = $dbh->selectrow_array($query); if ($count < $myconfig->{vclimit}) { $query = qq|SELECT id, name FROM vendor ORDER BY name|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{all_vendor} }, $ref; } $sth->finish; } } # pricegroups, customers $query = qq|SELECT count(*) FROM customer|; ($count) = $dbh->selectrow_array($query); if ($count < $myconfig->{vclimit}) { $query = qq|SELECT id, name FROM customer ORDER BY name|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{all_customer} }, $ref; } $sth->finish; } $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY pricegroup|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{all_pricegroup} }, $ref; } $sth->finish; if ($form->{id}) { $query = qq| SELECT value FROM defaults WHERE setting_key = 'weightunit'|; ($form->{weightunit}) = $dbh->selectrow_array($query); $query = qq| SELECT value FROM defaults WHERE setting_key = 'curr'|; ($form->{currencies}) = $dbh->selectrow_array($query); } else { # Dieter: FIXME left joins not working $query = qq| SELECT (SELECT value FROM defaults WHERE setting_key = 'weightunit') AS weightunit, current_date AS priceupdate, (SELECT value FROM defaults WHERE setting_key = 'curr') AS currencies, c1.accno AS inventory_accno, c1.description AS inventory_description, c2.accno AS income_accno, c2.description AS income_description, c3.accno AS expense_accno, c3.description AS expense_description FROM chart c1, chart c2, chart c3 WHERE c1.id IN (SELECT value FROM defaults WHERE setting_key = 'inventory_accno_id') AND c2.id IN (SELECT value FROM defaults WHERE setting_key = 'income_accno_id') AND c3.id IN (SELECT value FROM defaults WHERE setting_key = 'expense_accno_id')|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $ref = $sth->fetchrow_hashref(NAME_lc); for (qw(weightunit priceupdate currencies)) { $form->{$_} = $ref->{$_}; } # setup accno hash, {amount} is used in create_links for (qw(inventory income expense)) { $form->{amount}{"IC_$_"} = { accno => $ref->{"${_}_accno"}, description => $ref->{"${_}_description"} }; } $sth->finish; } $dbh->commit; } sub get_warehouses { my ($self, $myconfig, $form) = @_; my $dbh = $form->{dbh}; my $query = qq|SELECT id, description FROM warehouse|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{all_warehouse} }, $ref; } $sth->finish; $dbh->commit; } 1;