diff options
Diffstat (limited to 'LedgerSMB/IC.pm')
| -rw-r--r-- | LedgerSMB/IC.pm | 2660 |
1 files changed, 1322 insertions, 1338 deletions
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm index 5594c176..943f6b36 100644 --- a/LedgerSMB/IC.pm +++ b/LedgerSMB/IC.pm @@ -1,8 +1,8 @@ #===================================================================== -# LedgerSMB +# 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. @@ -33,15 +33,14 @@ package IC; - sub get_part { - my ($self, $myconfig, $form) = @_; + my ( $self, $myconfig, $form ) = @_; - # connect to db - my $dbh = $form->{dbh}; - my $i; + # connect to db + my $dbh = $form->{dbh}; + my $i; - my $query = qq| + my $query = qq| SELECT p.*, c1.accno AS inventory_accno, c1.description AS inventory_description, c2.accno AS income_accno, @@ -54,23 +53,23 @@ sub get_part { 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| + 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, @@ -80,67 +79,65 @@ sub get_part { 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| + $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| + $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 = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); + + while ( ($key) = $sth->fetchrow_array ) { + $form->{amount}{$key} = $key; + } - $sth->finish; + $sth->finish; - my $id = $dbh->quote($form->{id}); - # is it an orphan - $query = qq| + 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 @@ -148,20 +145,21 @@ sub get_part { 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} ) = $dbh->selectrow_array($query); + $form->{orphaned} = !$form->{orphaned}; + + $form->{orphaned} = 0 if $form->{project_id}; - $form->{orphaned} = 0 if $form->{project_id}; + if ( $form->{item} eq 'assembly' ) { + if ( $form->{orphaned} ) { + $form->{orphaned} = !$form->{onhand}; + } + } - if ($form->{item} eq 'assembly') { - if ($form->{orphaned}) { - $form->{orphaned} = !$form->{onhand}; - } - } + if ( $form->{item} =~ /(part|service)/ ) { - if ($form->{item} =~ /(part|service)/) { - # get vendors - $query = qq| + # get vendors + $query = qq| SELECT v.id, v.name, pv.partnumber, pv.lastcost, pv.leadtime, pv.curr AS vendorcurr @@ -169,19 +167,19 @@ sub get_part { 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| + + $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, @@ -191,202 +189,203 @@ sub get_part { 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); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{customermatrix} }, $ref; - } - $sth->finish; - } + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{customermatrix} }, $ref; + } + $sth->finish; + } - $form->run_custom_queries('parts', 'SELECT'); - -} + $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| + 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| + 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| + $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| + $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| + $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 { + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) + || $form->dberror($query); + } + else { - for $i (1 .. - $form->{assembly_rows} - 1) { + for $i ( 1 .. $form->{assembly_rows} - 1 ) { - # update BOM, A only - for (qw(bom adj)) { - $form->{"${_}_$i"} - *= 1; - } + # update BOM, A only + for (qw(bom adj)) { + $form->{"${_}_$i"} *= 1; + } - $query = qq| + $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); - } - } + $sth = $dbh->prepare($query); + $sth->execute( + $form->{"bom_$i"}, $form->{"adj_$i"}, + $form->{id}, $form->{"id_$i"} + ) || $form->dberror($query); + } + } - $form->{onhand} += $form->{stock}; + $form->{onhand} += $form->{stock}; - } + } - # delete tax records - $query = qq|DELETE FROM partstax WHERE parts_id = ?|; + # delete tax records + $query = qq|DELETE FROM partstax WHERE parts_id = ?|; - $sth = $dbh->prepare($query); - $sth->execute($form->{id})|| $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute( $form->{id} ) || $form->dberror($query); - # delete matrix - $query = qq| + # 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| + + $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 = ?, @@ -414,212 +413,205 @@ sub save { 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| + $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| + $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| + $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"}){ + $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"}, - $form->{"adj_$i"} - ) || $form->dberror($query); + + if ( $form->{"id_$i"} && $form->{"qty_$i"} ) { + $sth->execute( + $form->{id}, $form->{"id_$i"}, + $form->{"qty_$i"}, $form->{"bom_$i"}, + $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"} ); } - } - } - # 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| + + $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| + $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; - -} + $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 = $dbh->quote($listprice ); - $formsellprice = $dbh->quote($formsellprice ); - $formlastcost = $dbh->quote($form->{lastcost}); - $lastcost = $dbh->quote($lastcost); - $weight = $dbh->quote($weight); - $id = $dbh->quote($id); - - $query = qq| + 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 = $dbh->quote($listprice); + $formsellprice = $dbh->quote($formsellprice); + $formlastcost = $dbh->quote( $form->{lastcost} ); + $lastcost = $dbh->quote($lastcost); + $weight = $dbh->quote($weight); + $id = $dbh->quote($id); + + $query = qq| UPDATE parts SET listprice = listprice + $qty * ($formlistprice - $listprice), @@ -630,47 +622,43 @@ sub update_assembly { weight = weight + $qty * ($form->{weight} - $weight) WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); + $dbh->do($query) || $form->dberror($query); + + delete $form->{$id}; - delete $form->{$id}; - } +sub retrieve_assemblies { + my ( $self, $myconfig, $form ) = @_; + # connect to database + my $dbh = $form->{dbh}; -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' + 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| + 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 @@ -678,338 +666,328 @@ sub retrieve_assemblies { AND p.assembly = '1' ORDER BY $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $query = qq| + 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| + 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; - -} + $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) = @_; + 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"}); + # connect to database + my $dbh = $form->{dbh}; - if ($form->{"qty_$i"}) { - &adjust_inventory( - $dbh, $form, $form->{"id_$i"}, - $form->{"qty_$i"}); - } - - } + for my $i ( 1 .. $form->{rowcount} ) { + $form->{"qty_$i"} = $form->parse_amount( $myconfig, $form->{"qty_$i"} ); - my $rc = $dbh->commit; + if ( $form->{"qty_$i"} ) { + &adjust_inventory( $dbh, $form, $form->{"id_$i"}, + $form->{"qty_$i"} ); + } - $rc; + } -} + 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| + # 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); + my $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + 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); - } + # is it a service item then loop + if ( !$ref->{inventory_accno_id} ) { + next if !$ref->{assembly}; + } - $sth->finish; + # adjust parts onhand + $form->update_balance( + $dbh, "parts", "onhand", + qq|id = $ref->{id}|, + $qty * $ref->{qty} * -1 + ); + } - # update assembly - $form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $qty); + $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; - -} + 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| + 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; - -} + 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, + 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| + } + + # 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 @@ -1023,65 +1001,67 @@ sub all_parts { 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| + } + + 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, @@ -1092,7 +1072,7 @@ sub all_parts { $makemodelflds $assemblyflds |; - $query = qq| + $query = qq| SELECT $flds FROM parts p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) @@ -1103,12 +1083,12 @@ sub all_parts { 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| + # 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) @@ -1120,91 +1100,92 @@ sub all_parts { $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| + } + + # 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, @@ -1215,13 +1196,11 @@ sub all_parts { 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/; - if ($form->{bought}) { - my $rflds = $flds; - $rflds =~ - s/i.qty AS onhand/i.qty * -1 AS onhand/; - - $query = qq| + $query = qq| SELECT $rflds, 'ir' AS module, '' AS type, (SELECT sell @@ -1242,12 +1221,12 @@ sub all_parts { ON (a.employee_id = e.id) $makemodeljoin WHERE $invwhere|; - $union = " + $union = " UNION ALL"; - } + } - if ($form->{sold}) { - $query .= qq| + if ( $form->{sold} ) { + $query .= qq| $union SELECT $flds, 'is' AS module, '' AS type, @@ -1269,39 +1248,39 @@ sub all_parts { ON (a.employee_id = e.id) $makemodeljoin WHERE $invwhere|; - $union = " + $union = " UNION ALL"; - } - } + } + } - if ($form->{onorder} || $form->{ordered}) { - my $ordwhere = "$where + 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| + $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, @@ -1313,8 +1292,8 @@ sub all_parts { p.notes $makemodelfld|; - if ($form->{ordered}) { - $query .= qq| + if ( $form->{ordered} ) { + $query .= qq| $union SELECT $flds, 'oe' AS module, 'sales_order' AS type, @@ -1336,12 +1315,12 @@ sub all_parts { $makemodeljoin WHERE $ordwhere AND a.customer_id > 0|; - $union = " + $union = " UNION ALL"; - } - - if ($form->{onorder}) { - $flds = qq| + } + + 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, @@ -1353,8 +1332,8 @@ sub all_parts { i.trans_id, ct.name,e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; - - $query .= qq| + + $query .= qq| $union SELECT $flds, 'oe' AS module, 'purchase_order' AS type, @@ -1376,39 +1355,38 @@ sub all_parts { $makemodeljoin WHERE $ordwhere AND a.vendor_id > 0|; - } - - } - - if ($form->{rfq} || $form->{quoted}) { - my $quowhere = "$where + } + + } + + 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| + $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, @@ -1419,8 +1397,8 @@ sub all_parts { e.name AS employee, a.curr, '0' AS till, p.notes $makemodelfld|; - if ($form->{quoted}) { - $query .= qq| + if ( $form->{quoted} ) { + $query .= qq| $union SELECT $flds, 'oe' AS module, 'sales_quotation' AS type, @@ -1443,12 +1421,12 @@ sub all_parts { $makemodeljoin WHERE $quowhere AND a.customer_id > 0|; - $union = " + $union = " UNION ALL"; - } - - if ($form->{rfq}) { - $flds = qq| + } + + 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, @@ -1461,7 +1439,7 @@ sub all_parts { a.curr, '0' AS till, p.notes $makemodelfld|; - $query .= qq| + $query .= qq| $union SELECT $flds, 'oe' AS module, 'request_quotation' AS type, @@ -1483,44 +1461,44 @@ sub all_parts { $makemodeljoin WHERE $quowhere AND a.vendor_id > 0|; - } + } - } + } - $query .= qq| + $query .= qq| ORDER BY $sortorder|; - } + } - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $query = qq| + $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| + 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, @@ -1528,108 +1506,111 @@ sub all_parts { 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| + } + 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| + } + 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); + $sth = $dbh->prepare($query) || $form->dberror($query); - for (@{ $form->{parts} }) { + 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, $_; - } + $sth->execute( $_->{id} ) || $form->dberror($query); - } else { + if ( $form->{warehouse} ne "" ) { - push @a, $_; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - if ($ref->{onhand} > 0) { - push @a, $ref; - } - } - } - - $sth->finish; - } + $ref = $sth->fetchrow_hashref(NAME_lc); + if ( $ref->{onhand} != 0 ) { + $_->{onhand} = $ref->{onhand}; + push @a, $_; + } - @{ $form->{parts} } = @a; + } + else { - } + push @a, $_; - $dbh->commit; + 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| + 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) @@ -1640,69 +1621,68 @@ sub include_assembly { $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; + 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' + 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| + } + + $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 @@ -1712,19 +1692,19 @@ sub requirements { 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| + 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 @@ -1736,22 +1716,22 @@ sub requirements { 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| + $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) @@ -1760,30 +1740,33 @@ sub requirements { 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{$_}; - } - -} + $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) = @_; + my ( $dbh, $form, $parts, $id, $qty, $where ) = @_; - # assemblies - my $query = qq| + # 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 @@ -1797,110 +1780,112 @@ sub requirements_assembly { 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; - -} + 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) = @_; + my ( $self, $module, $myconfig, $form ) = @_; + + # connect to database + my $dbh = $form->{dbh}; - # connect to database - my $dbh = $form->{dbh}; - - my $ref; + my $ref; - my $query = qq| + 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| + 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| + ( $form->{weightunit} ) = $dbh->selectrow_array($query); + $query = qq| SELECT value FROM defaults WHERE setting_key = 'curr'|; - ($form->{currencies}) = $dbh->selectrow_array($query); + ( $form->{currencies} ) = $dbh->selectrow_array($query); - } else { - # Dieter: FIXME left joins not working - $query = qq| + } + else { + + # Dieter: FIXME left joins not working + $query = qq| SELECT (SELECT value FROM defaults WHERE setting_key = 'weightunit') AS weightunit, current_date AS priceupdate, @@ -1920,46 +1905,45 @@ sub create_links { 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; + $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 ( $self, $myconfig, $form ) = @_; - my $dbh = $form->{dbh}; + my $dbh = $form->{dbh}; - my $query = qq|SELECT id, description FROM warehouse|; + my $query = qq|SELECT id, description FROM warehouse|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + 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; + while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{all_warehouse} }, $ref; + } + $sth->finish; - $dbh->commit; + $dbh->commit; } |