summaryrefslogtreecommitdiff
path: root/LedgerSMB/IC.pm
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-04-12 20:46:20 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-04-12 20:46:20 +0000
commitdf073d6e09c0f87fb2e88cc215ace843a5851d4a (patch)
tree1c15ce909d3e0353b21bbd909e6720fd485a3a53 /LedgerSMB/IC.pm
parent65458125b8f3814fd6ef4d07b55ab69f62f5a528 (diff)
Formatting with Perltidy
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.2@1068 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'LedgerSMB/IC.pm')
-rwxr-xr-xLedgerSMB/IC.pm2654
1 files changed, 1319 insertions, 1335 deletions
diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index 6bcfdd80..35614446 100755
--- 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,210 +413,203 @@ 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"}){
+
+ 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);
+ $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"} );
}
- }
- }
- # 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);
- $formsellprice = $dbh->quote($formsellprice - $sellprice);
- $formlastcost = $dbh->quote($form->{lastcost} - $lastcost);
- $weight = $dbh->quote($form->{weight} - $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 );
+ $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),
@@ -628,47 +620,43 @@ sub update_assembly {
weight = weight +
$qty * cast($weight AS numeric)
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
@@ -676,338 +664,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
@@ -1021,65 +999,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,
@@ -1090,7 +1070,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)
@@ -1101,12 +1081,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)
@@ -1118,91 +1098,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,
@@ -1213,13 +1194,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
@@ -1240,12 +1219,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,
@@ -1267,39 +1246,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,
@@ -1311,8 +1290,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,
@@ -1334,12 +1313,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,
@@ -1351,8 +1330,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,
@@ -1374,39 +1353,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,
@@ -1417,8 +1395,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,
@@ -1441,12 +1419,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,
@@ -1459,7 +1437,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,
@@ -1481,44 +1459,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,
@@ -1526,108 +1504,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)
@@ -1638,69 +1619,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
@@ -1710,19 +1690,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
@@ -1734,22 +1714,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)
@@ -1758,30 +1738,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
@@ -1795,110 +1778,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,
@@ -1918,46 +1903,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;
}