From 4189e38298109e7e923970771a17c56b2973c79e Mon Sep 17 00:00:00 2001 From: einhverfr Date: Thu, 28 Sep 2006 04:31:54 +0000 Subject: FInished whitespace and sql-injection cleanup for OE.pm git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@162 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/OE.pm | 1579 ++++++++++++++++++++++++++++++------------------------- 1 file changed, 871 insertions(+), 708 deletions(-) diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm index 4e38b7bc..9e87055b 100755 --- a/LedgerSMB/OE.pm +++ b/LedgerSMB/OE.pm @@ -23,7 +23,7 @@ # #====================================================================== # -# This file has undergone PARTIAL (66%) whitespace cleanup To line 1806 +# This file has undergone whitespace cleanup # #====================================================================== # @@ -1806,905 +1806,1068 @@ sub order_details { sub assembly_details { - my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_; - - my $sm = ""; - my $spacer; - - $form->{stagger}++; - if ($form->{format} eq 'html') { - $spacer = " " x (3 * ($form->{stagger} - 1)) if $form->{stagger} > 1; - } - if ($form->{format} =~ /(postscript|pdf)/) { - if ($form->{stagger} > 1) { - $spacer = ($form->{stagger} - 1) * 3; - $spacer = '\rule{'.$spacer.'mm}{0mm}'; - } - } - - # get parts and push them onto the stack - my $sortorder = ""; - - if ($form->{grouppartsgroup}) { - $sortorder = qq|ORDER BY pg.partsgroup, a.$oid|; - } else { - $sortorder = qq|ORDER BY a.$oid|; - } - - my $where = ($form->{formname} eq 'work_order') ? "1 = 1" : "a.bom = '1'"; - - my $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, - pg.partsgroup, p.partnumber AS sku, p.assembly, p.id, p.bin - FROM assembly a - JOIN parts p ON (a.parts_id = p.id) - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where - AND a.id = '$id' - $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + my ($myconfig, $form, $dbh, $id, $oid, $qty) = @_; + + my $sm = ""; + my $spacer; + + $form->{stagger}++; + if ($form->{format} eq 'html') { + $spacer = " " x (3 * ($form->{stagger} - 1)) + if $form->{stagger} > 1; + } + if ($form->{format} =~ /(postscript|pdf)/) { + if ($form->{stagger} > 1) { + $spacer = ($form->{stagger} - 1) * 3; + $spacer = '\rule{'.$spacer.'mm}{0mm}'; + } + } + + # get parts and push them onto the stack + my $sortorder = ""; + + if ($form->{grouppartsgroup}) { + $sortorder = qq|ORDER BY pg.partsgroup, a.id|; + } else { + $sortorder = qq|ORDER BY a.id|; + } + + my $where = ($form->{formname} eq 'work_order') + ? "1 = 1" + : "a.bom = '1'"; + + my $query = qq| + SELECT p.partnumber, p.description, p.unit, a.qty, + pg.partsgroup, p.partnumber AS sku, p.assembly, p.id, + p.bin + FROM assembly a + JOIN parts p ON (a.parts_id = p.id) + LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) + WHERE $where + AND a.id = ? + $sortorder|; + my $sth = $dbh->prepare($query); + $sth->execute($id) || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - for (qw(partnumber description partsgroup)) { - $form->{"a_$_"} = $ref->{$_}; - $form->format_string("a_$_"); - } + for (qw(partnumber description partsgroup)) { + $form->{"a_$_"} = $ref->{$_}; + $form->format_string("a_$_"); + } - if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) { - for (qw(taxrates number sku unit qty runningnumber ship bin serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") } - $sm = ($form->{"a_partsgroup"}) ? $form->{"a_partsgroup"} : ""; - push(@{ $form->{description} }, "$spacer$sm"); + if ($form->{grouppartsgroup} && $ref->{partsgroup} ne $sm) { + for (qw( + taxrates number sku unit qty runningnumber ship + bin serialnumber requiredate projectnumber + sellprice listprice netprice discount + discountrate linetotal weight itemnotes) + ) { + + push(@{ $form->{$_} }, ""); + } + $sm = ($form->{"a_partsgroup"}) + ? $form->{"a_partsgroup"} : ""; + push(@{ $form->{description} }, "$spacer$sm"); - push(@{ $form->{lineitems} }, { amount => 0, tax => 0 }); + push(@{ $form->{lineitems} }, + { amount => 0, tax => 0 }); - } + } - if ($form->{stagger}) { + if ($form->{stagger}) { - push(@{ $form->{description} }, qq|$spacer$form->{"a_partnumber"}, $form->{"a_description"}|); - for (qw(taxrates number sku runningnumber ship serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") } + push(@{ $form->{description} }, + qq|$spacer$form->{"a_partnumber"}, |. + qq|$form->{"a_description"}|); + + for (qw( + taxrates number sku runningnumber ship + serialnumber requiredate projectnumber + sellprice listprice netprice discount + discountrate linetotal weight itemnotes) + ) { + + push(@{ $form->{$_} }, "") + } - } else { + } else { - push(@{ $form->{description} }, qq|$form->{"a_description"}|); - push(@{ $form->{sku} }, $form->{"a_partnumber"}); - push(@{ $form->{number} }, $form->{"a_partnumber"}); + push(@{ $form->{description} }, + qq|$form->{"a_description"}|); + push(@{ $form->{sku} }, $form->{"a_partnumber"}); + push(@{ $form->{number} }, $form->{"a_partnumber"}); - for (qw(taxrates runningnumber ship serialnumber requiredate projectnumber sellprice listprice netprice discount discountrate linetotal weight itemnotes)) { push(@{ $form->{$_} }, "") } + for (qw( + taxrates runningnumber ship serialnumber + requiredate projectnumber sellprice listprice + netprice discount discountrate linetotal weight + itemnotes) + ) { + + push(@{ $form->{$_} }, "") + } - } + } - push(@{ $form->{lineitems} }, { amount => 0, tax => 0 }); + push(@{ $form->{lineitems} }, { amount => 0, tax => 0 }); - push(@{ $form->{qty} }, $form->format_amount($myconfig, $ref->{qty} * $qty)); - for (qw(unit bin)) { - $form->{"a_$_"} = $ref->{$_}; - $form->format_string("a_$_"); - push(@{ $form->{$_} }, $form->{"a_$_"}); - } - - if ($ref->{assembly} && $form->{formname} eq 'work_order') { - &assembly_details($myconfig, $form, $dbh, $ref->{id}, $oid, $ref->{qty} * $qty); - } + push(@{ $form->{qty} }, $form->format_amount( + $myconfig, $ref->{qty} * $qty)); + + for (qw(unit bin)) { + $form->{"a_$_"} = $ref->{$_}; + $form->format_string("a_$_"); + push(@{ $form->{$_} }, $form->{"a_$_"}); + } + + if ($ref->{assembly} && $form->{formname} eq 'work_order') { + &assembly_details( + $myconfig, $form, $dbh, $ref->{id}, $oid, + $ref->{qty} * $qty); + } - } - $sth->finish; + } + $sth->finish; - $form->{stagger}--; + $form->{stagger}--; } sub project_description { - my ($self, $dbh, $id) = @_; + my ($self, $dbh, $id) = @_; - my $query = qq|SELECT description - FROM project - WHERE id = $id|; - ($_) = $dbh->selectrow_array($query); + my $query = qq| + SELECT description + FROM project + WHERE id = $id|; + ($_) = $dbh->selectrow_array($query); - $_; + $_; } sub get_warehouses { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); - # setup warehouses - my $query = qq|SELECT id, description - FROM warehouse - ORDER BY 2|; + my $dbh = $form->dbconnect($myconfig); + # setup warehouses + my $query = qq| + SELECT id, description + FROM warehouse + ORDER BY 2|; - 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->disconnect; + $dbh->disconnect; } sub save_inventory { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my ($null, $warehouse_id) = split /--/, $form->{warehouse}; - $warehouse_id *= 1; + my ($null, $warehouse_id) = split /--/, $form->{warehouse}; + $warehouse_id *= 1; - my $ml = ($form->{type} eq 'ship_order') ? -1 : 1; + my $ml = ($form->{type} eq 'ship_order') ? -1 : 1; - my $dbh = $form->dbconnect_noauto($myconfig); - my $sth; - my $wth; - my $serialnumber; - my $ship; + my $dbh = $form->dbconnect_noauto($myconfig); + my $sth; + my $wth; + my $serialnumber; + my $ship; - my ($null, $employee_id) = split /--/, $form->{employee}; - ($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id; + my ($null, $employee_id) = split /--/, $form->{employee}; + ($null, $employee_id) = $form->get_employee($dbh) if ! $employee_id; - $query = qq|SELECT serialnumber, ship - FROM orderitems - WHERE trans_id = ? - AND id = ? - FOR UPDATE|; - $sth = $dbh->prepare($query) || $form->dberror($query); + $query = qq| + SELECT serialnumber, ship + FROM orderitems + WHERE trans_id = ? + AND id = ? + FOR UPDATE|; + $sth = $dbh->prepare($query) || $form->dberror($query); - $query = qq|SELECT sum(qty) - FROM inventory - WHERE parts_id = ? - AND warehouse_id = ?|; - $wth = $dbh->prepare($query) || $form->dberror($query); + $query = qq| + SELECT sum(qty) + FROM inventory + WHERE parts_id = ? + AND warehouse_id = ?|; + $wth = $dbh->prepare($query) || $form->dberror($query); - for my $i (1 .. $form->{rowcount}) { + for my $i (1 .. $form->{rowcount}) { - $ship = (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"})) ? $form->{"qty_$i"} : $form->{"ship_$i"}; + $ship = (abs($form->{"ship_$i"}) > abs($form->{"qty_$i"})) + ? $form->{"qty_$i"} + : $form->{"ship_$i"}; - if ($warehouse_id && $form->{type} eq 'ship_order') { + if ($warehouse_id && $form->{type} eq 'ship_order') { - $wth->execute($form->{"id_$i"}, $warehouse_id) || $form->dberror; + $wth->execute($form->{"id_$i"}, $warehouse_id) + || $form->dberror; - ($qty) = $wth->fetchrow_array; - $wth->finish; + ($qty) = $wth->fetchrow_array; + $wth->finish; - if ($ship > $qty) { - $ship = $qty; - } - } + if ($ship > $qty) { + $ship = $qty; + } + } - if ($ship) { - - $ship *= $ml; - $query = qq|INSERT INTO inventory (parts_id, warehouse_id, - qty, trans_id, orderitems_id, shippingdate, employee_id) - VALUES ($form->{"id_$i"}, $warehouse_id, - $ship, $form->{"id"}, - $form->{"orderitems_id_$i"}, '$form->{shippingdate}', - $employee_id)|; - $dbh->do($query) || $form->dberror($query); + if ($ship) { + + if (!$form->{shippingdate}){ + $form->{shippingdate} = undef; + } + + $ship *= $ml; + $query = qq| + INSERT INTO inventory + (parts_id, warehouse_id, qty, trans_id, + orderitems_id, shippingdate, + employee_id) + VALUES + (?, ?, ?, ?, ?, ?, ?)|; + $sth2 = $dbh->prepare($query); + $sth2->execute( + $form->{"id_$i"}, $warehouse_id, + $ship, $form->{"id"}, + $form->{"orderitems_id_$i"}, + $form->{shippingdate}, + $employee_id + ) || $form->dberror($query); + $sth2->finish; - # add serialnumber, ship to orderitems - $sth->execute($form->{id}, $form->{"orderitems_id_$i"}) || $form->dberror; - ($serialnumber, $ship) = $sth->fetchrow_array; - $sth->finish; - - $serialnumber .= " " if $serialnumber; - $serialnumber .= qq|$form->{"serialnumber_$i"}|; - $ship += $form->{"ship_$i"}; - - $query = qq|UPDATE orderitems SET - serialnumber = '$serialnumber', - ship = $ship, - reqdate = '$form->{shippingdate}' - WHERE trans_id = $form->{id} - AND id = $form->{"orderitems_id_$i"}|; - $dbh->do($query) || $form->dberror($query); + # add serialnumber, ship to orderitems + $sth->execute( + $form->{id}, $form->{"orderitems_id_$i"}) + || $form->dberror; + ($serialnumber, $ship) = $sth->fetchrow_array; + $sth->finish; + + $serialnumber .= " " if $serialnumber; + $serialnumber .= qq|$form->{"serialnumber_$i"}|; + $ship += $form->{"ship_$i"}; + + $query = qq| + UPDATE orderitems SET + serialnumber = '$serialnumber', + ship = $ship, + reqdate = '$form->{shippingdate}' + WHERE trans_id = $form->{id} + AND id = $form->{"orderitems_id_$i"}|; + $sth2 = $dbh->prepare($query); + $sth2->execute( + $serialnumber, $ship, + $form->{shippingdate}, $form->{id}, + $form->{"orderitems_id_$i"} + ) || $form->dberror($query); + $sth2->finish; - # update order with ship via - $query = qq|UPDATE oe SET - shippingpoint = |.$dbh->quote($form->{shippingpoint}).qq|, - shipvia = |.$dbh->quote($form->{shipvia}).qq| - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - # update onhand for parts - $form->update_balance($dbh, - "parts", - "onhand", - qq|id = $form->{"id_$i"}|, - $form->{"ship_$i"} * $ml); - - } - } - - my $rc = $dbh->commit; - $dbh->disconnect; - - $rc; + # update order with ship via + $query = qq| + UPDATE oe SET + shippingpoint = ?, + shipvia = ? + WHERE id = ?|; + $sth2 = $dbh->prepare($query); + $sth2->execute( + $form->{shippingpoint}, $form->{shipvia}, + $form->{id} + ) || $form->dberror($query); + $sth2->finish; + + # update onhand for parts + $form->update_balance($dbh, + "parts", + "onhand", + qq|id = $form->{"id_$i"}|, + $form->{"ship_$i"} * $ml); + + } + } + + my $rc = $dbh->commit; + + $rc; } sub adj_onhand { - my ($dbh, $form, $ml) = @_; + my ($dbh, $form, $ml) = @_; - my $query = qq|SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly - FROM orderitems oi - JOIN parts p ON (p.id = oi.parts_id) - WHERE oi.trans_id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $query = qq| + SELECT oi.parts_id, oi.ship, p.inventory_accno_id, p.assembly + FROM orderitems oi + JOIN parts p ON (p.id = oi.parts_id) + WHERE oi.trans_id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $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 $ath = $dbh->prepare($query) || $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 $ath = $dbh->prepare($query) || $form->dberror($query); - my $ref; + my $ref; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - if ($ref->{inventory_accno_id} || $ref->{assembly}) { + if ($ref->{inventory_accno_id} || $ref->{assembly}) { - # do not update if assembly consists of all services - if ($ref->{assembly}) { - $ath->execute($ref->{parts_id}) || $form->dberror($query); + # do not update if assembly consists of all services + if ($ref->{assembly}) { + $ath->execute($ref->{parts_id}) + || $form->dberror($query); - my ($inv, $assembly) = $ath->fetchrow_array; - $ath->finish; + my ($inv, $assembly) = $ath->fetchrow_array; + $ath->finish; - next unless ($inv || $assembly); + next unless ($inv || $assembly); - } + } - # adjust onhand in parts table - $form->update_balance($dbh, - "parts", - "onhand", - qq|id = $ref->{parts_id}|, - $ref->{ship} * $ml); - } - } + # adjust onhand in parts table + $form->update_balance($dbh, + "parts", + "onhand", + qq|id = $ref->{parts_id}|, + $ref->{ship} * $ml); + } + } - $sth->finish; + $sth->finish; } sub adj_inventory { - my ($dbh, $myconfig, $form) = @_; - - my %oid = ( 'Pg' => 'id', - 'PgPP' => 'id', - 'Oracle' => 'rowid', - 'DB2' => '1=1' - ); - - # increase/reduce qty in inventory table - my $query = qq|SELECT oi.id, oi.parts_id, oi.ship - FROM orderitems oi - WHERE oi.trans_id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $query = qq|SELECT qty, - (SELECT SUM(qty) FROM inventory - WHERE trans_id = $form->{id} - AND orderitems_id = ?) AS total - FROM inventory - WHERE trans_id = $form->{id} - AND orderitems_id = ?|; - my $ith = $dbh->prepare($query) || $form->dberror($query); - - my $qty; - my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1; - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - - $ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query); - - my $ship = $ref->{ship}; - while (my $inv = $ith->fetchrow_hashref(NAME_lc)) { - - if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) { - $qty = $inv->{qty} * $ml if ($qty > ($inv->{qty} * $ml)); + my ($dbh, $myconfig, $form) = @_; + + + # increase/reduce qty in inventory table + my $query = qq| + SELECT oi.id, oi.parts_id, oi.ship + FROM orderitems oi + WHERE oi.trans_id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + my $id = $dbh->quote($form->{id}); + $query = qq| + SELECT qty, + (SELECT SUM(qty) FROM inventory + WHERE trans_id = $id + AND orderitems_id = ?) AS total + FROM inventory + WHERE trans_id = $id + AND orderitems_id = ?|; + my $ith = $dbh->prepare($query) || $form->dberror($query); + + my $qty; + my $ml = ($form->{type} =~ /(ship|sales)_order/) ? -1 : 1; + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + + $ith->execute($ref->{id}, $ref->{id}) || $form->dberror($query); + + my $ship = $ref->{ship}; + while (my $inv = $ith->fetchrow_hashref(NAME_lc)) { + + if (($qty = (($inv->{total} * $ml) - $ship)) >= 0) { + $qty = $inv->{qty} * $ml + if ($qty > ($inv->{qty} * $ml)); - $form->update_balance($dbh, - "inventory", - "qty", - qq|$oid{$myconfig->{dbdriver}} = $inv->{oid}|, - $qty * -1 * $ml); - $ship -= $qty; - } - } - $ith->finish; - - } - $sth->finish; - - # delete inventory entries if qty = 0 - $query = qq|DELETE FROM inventory - WHERE trans_id = $form->{id} - AND qty = 0|; - $dbh->do($query) || $form->dberror($query); + $form->update_balance($dbh, + "inventory", + "qty", + qq|$oid{$myconfig->{dbdriver}} |. + qq|= $inv->{oid}|, + $qty * -1 * $ml); + $ship -= $qty; + } + } + $ith->finish; + + } + $sth->finish; + + # delete inventory entries if qty = 0 + $query = qq| + DELETE FROM inventory + WHERE trans_id = ? + AND qty = 0|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); } sub get_inventory { - my ($self, $myconfig, $form) = @_; - - my $where; - my $query; - my $null; - my $fromwarehouse_id; - my $towarehouse_id; - my $var; - - my $dbh = $form->dbconnect($myconfig); - - if ($form->{partnumber} ne "") { - $var = $form->like(lc $form->{partnumber}); - $where .= " - AND lower(p.partnumber) LIKE '$var'"; - } - if ($form->{description} ne "") { - $var = $form->like(lc $form->{description}); - $where .= " - AND lower(p.description) LIKE '$var'"; - } - if ($form->{partsgroup} ne "") { - ($null, $var) = split /--/, $form->{partsgroup}; - $where .= " - AND pg.id = $var"; - } - - - ($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse}; - $fromwarehouse_id *= 1; - - ($null, $towarehouse_id) = split /--/, $form->{towarehouse}; - $towarehouse_id *= 1; - - my %ordinal = ( partnumber => 2, - description => 3, - partsgroup => 5, - warehouse => 6, + my ($self, $myconfig, $form) = @_; + + my $where; + my $query; + my $null; + my $fromwarehouse_id; + my $towarehouse_id; + my $var; + + my $dbh = $form->{dbh}; + + if ($form->{partnumber} ne "") { + $var = $dbh->quote($form->like(lc $form->{partnumber})); + $where .= " + AND lower(p.partnumber) LIKE '$var'"; + } + if ($form->{description} ne "") { + $var = $dbh->quote($form->like(lc $form->{description})); + $where .= " + AND lower(p.description) LIKE '$var'"; + } + if ($form->{partsgroup} ne "") { + ($null, $var) = split /--/, $form->{partsgroup}; + $var = $dbh->quote($var); + $where .= " + AND pg.id = $var"; + } + + + ($null, $fromwarehouse_id) = split /--/, $form->{fromwarehouse}; + $fromwarehouse_id = $dbh->quote($fromwarehouse_id); + + ($null, $towarehouse_id) = split /--/, $form->{towarehouse}; + $towarehouse_id = $dbh->quote($towarehouse_id); + + my %ordinal = ( + partnumber => 2, + description => 3, + partsgroup => 5, + warehouse => 6, ); - my @a = (partnumber, warehouse); - my $sortorder = $form->sort_order(\@a, \%ordinal); - - if ($fromwarehouse_id) { - if ($towarehouse_id) { - $where .= " - AND NOT i.warehouse_id = $towarehouse_id"; - } - $query = qq|SELECT p.id, p.partnumber, p.description, - sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, - pg.partsgroup, w.description AS warehouse, i.warehouse_id - FROM inventory i - JOIN parts p ON (p.id = i.parts_id) - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - JOIN warehouse w ON (w.id = i.warehouse_id) - WHERE i.warehouse_id = $fromwarehouse_id - $where - GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id - ORDER BY $sortorder|; - } else { - if ($towarehouse_id) { - $query = qq| - SELECT p.id, p.partnumber, p.description, - p.onhand, (SELECT SUM(qty) FROM inventory i WHERE i.parts_id = p.id) AS qty, - pg.partsgroup, '' AS warehouse, 0 AS warehouse_id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE p.onhand > 0 - $where - UNION|; - } - - $query .= qq| - SELECT p.id, p.partnumber, p.description, - sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, - pg.partsgroup, w.description AS warehouse, i.warehouse_id - FROM inventory i - JOIN parts p ON (p.id = i.parts_id) - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - JOIN warehouse w ON (w.id = i.warehouse_id) - WHERE i.warehouse_id != $towarehouse_id - $where - GROUP BY p.id, p.partnumber, p.description, pg.partsgroup, w.description, i.warehouse_id - ORDER BY $sortorder|; - } - - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{qty} = $ref->{onhand} - $ref->{qty}; - push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0; - } - $sth->finish; - - $dbh->disconnect; + my @a = (partnumber, warehouse); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + if ($fromwarehouse_id) { + if ($towarehouse_id) { + $where .= " + AND NOT i.warehouse_id = $towarehouse_id"; + } + $query = qq| + SELECT p.id, p.partnumber, p.description, + sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, + pg.partsgroup, w.description AS warehouse, + i.warehouse_id + FROM inventory i + JOIN parts p ON (p.id = i.parts_id) + LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) + JOIN warehouse w ON (w.id = i.warehouse_id) + WHERE i.warehouse_id = $fromwarehouse_id + $where + GROUP BY p.id, p.partnumber, p.description, + pg.partsgroup, w.description, i.warehouse_id + ORDER BY $sortorder|; + } else { + if ($towarehouse_id) { + $query = qq| + SELECT p.id, p.partnumber, p.description, + p.onhand, + (SELECT SUM(qty) + FROM inventory i + WHERE i.parts_id = p.id) AS qty, + pg.partsgroup, '' AS warehouse, + 0 AS warehouse_id + FROM parts p + LEFT JOIN partsgroup pg + ON (p.partsgroup_id = pg.id) + WHERE p.onhand > 0 + $where + UNION|; + } + $query .= qq| + SELECT p.id, p.partnumber, p.description, + sum(i.qty) * 2 AS onhand, sum(i.qty) AS qty, + pg.partsgroup, w.description AS warehouse, + i.warehouse_id + FROM inventory i + JOIN parts p ON (p.id = i.parts_id) + LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) + JOIN warehouse w ON (w.id = i.warehouse_id) + WHERE i.warehouse_id != $towarehouse_id + $where + GROUP BY p.id, p.partnumber, p.description, + pg.partsgroup, w.description, i.warehouse_id + ORDER BY $sortorder|; + } + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{qty} = $ref->{onhand} - $ref->{qty}; + push @{ $form->{all_inventory} }, $ref if $ref->{qty} > 0; + } + $sth->finish; + + $dbh->commit; } sub transfer { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->dbconnect_noauto($myconfig); - ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); + ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); - my @a = localtime; - $a[5] += 1900; - $a[4]++; - $a[4] = substr("0$a[4]", -2); - $a[3] = substr("0$a[3]", -2); - $shippingdate = "$a[5]$a[4]$a[3]"; - - my %total = (); + my @a = localtime; + $a[5] += 1900; + $a[4]++; + $a[4] = substr("0$a[4]", -2); + $a[3] = substr("0$a[3]", -2); + $shippingdate = "$a[5]$a[4]$a[3]"; - my $query = qq|INSERT INTO inventory - (warehouse_id, parts_id, qty, shippingdate, employee_id) - VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|; - $sth = $dbh->prepare($query) || $form->dberror($query); + my %total = (); - my $qty; - - for my $i (1 .. $form->{rowcount}) { - $qty = $form->parse_amount($myconfig, $form->{"transfer_$i"}); - - $qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"}); + - if ($qty > 0) { - # to warehouse - if ($form->{warehouse_id}) { - $sth->execute($form->{warehouse_id}, $form->{"id_$i"}, $qty) || $form->dberror; - $sth->finish; - } + my $query = qq| + INSERT INTO inventory + (warehouse_id, parts_id, qty, shippingdate, employee_id) + VALUES (?, ?, ?, '$shippingdate', $form->{employee_id})|; + $sth = $dbh->prepare($query) || $form->dberror($query); + + my $qty; + + for my $i (1 .. $form->{rowcount}) { + $qty = $form->parse_amount($myconfig, $form->{"transfer_$i"}); + + $qty = $form->{"qty_$i"} if ($qty > $form->{"qty_$i"}); + + if ($qty > 0) { + # to warehouse + if ($form->{warehouse_id}) { + $sth->execute($form->{warehouse_id}, + $form->{"id_$i"}, $qty, + $shippingdate, $form->{employee_id}) + || $form->dberror; + $sth->finish; + } - # from warehouse - if ($form->{"warehouse_id_$i"}) { - $sth->execute($form->{"warehouse_id_$i"}, $form->{"id_$i"}, $qty * -1) || $form->dberror; - $sth->finish; - } - } - } + # from warehouse + if ($form->{"warehouse_id_$i"}) { + $sth->execute($form->{"warehouse_id_$i"}, + $form->{"id_$i"}, $qty * -1) + || $form->dberror; + $sth->finish; + } + } + } - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; + $dbh->commit; - $rc; + $rc; } sub get_soparts { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + # connect to database + my $dbh = $form->{dbh}; - my $id; - my $ref; + my $id; + my $ref; - # store required items from selected sales orders - my $query = qq|SELECT p.id, oi.qty - oi.ship AS required, - p.assembly - FROM orderitems oi - JOIN parts p ON (p.id = oi.parts_id) - WHERE oi.trans_id = ?|; - my $sth = $dbh->prepare($query) || $form->dberror($query); + # store required items from selected sales orders + my $query = qq| + SELECT p.id, oi.qty - oi.ship AS required, p.assembly + FROM orderitems oi + JOIN parts p ON (p.id = oi.parts_id) + WHERE oi.trans_id = ?|; + my $sth = $dbh->prepare($query) || $form->dberror($query); - for (my $i = 1; $i <= $form->{rowcount}; $i++) { + for (my $i = 1; $i <= $form->{rowcount}; $i++) { - if ($form->{"ndx_$i"}) { + if ($form->{"ndx_$i"}) { - $sth->execute($form->{"ndx_$i"}); + $sth->execute($form->{"ndx_$i"}); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - &add_items_required("", $dbh, $form, $ref->{id}, $ref->{required}, $ref->{assembly}); - } - $sth->finish; - } + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + &add_items_required( + "", $dbh, $form, $ref->{id}, + $ref->{required}, $ref->{assembly}); + } + $sth->finish; + } - } + } - $query = qq|SELECT current_date FROM defaults|; - ($form->{transdate}) = $dbh->selectrow_array($query); + $query = qq|SELECT current_date FROM defaults|; + ($form->{transdate}) = $dbh->selectrow_array($query); - # foreign exchange rates - &exchangerate_defaults($dbh, $form); + # foreign exchange rates + &exchangerate_defaults($dbh, $form); - $dbh->disconnect; + $dbh->disconnect; } sub add_items_required { - my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_; - - my $query; - my $sth; - my $ref; - - if ($assembly) { - $query = qq|SELECT p.id, a.qty, p.assembly - FROM assembly a - JOIN parts p ON (p.id = a.parts_id) - WHERE a.id = $parts_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my ($self, $dbh, $form, $parts_id, $required, $assembly) = @_; + + my $query; + my $sth; + my $ref; + + if ($assembly) { + $query = qq| + SELECT p.id, a.qty, p.assembly + FROM assembly a + JOIN parts p ON (p.id = a.parts_id) + WHERE a.id = ?|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - &add_items_required("", $dbh, $form, $ref->{id}, $required * $ref->{qty}, $ref->{assembly}); - } - $sth->finish; + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + &add_items_required( + "", $dbh, $form, $ref->{id}, + $required * $ref->{qty}, $ref->{assembly}); + } + $sth->finish; - } else { - - $query = qq|SELECT partnumber, description, lastcost - FROM parts - WHERE id = $parts_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{orderitems}{$parts_id}{$_} = $ref->{$_} } - $sth->finish; - - $form->{orderitems}{$parts_id}{required} += $required; - - $query = qq|SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr, - pv.vendor_id, v.name - FROM partsvendor pv - JOIN vendor v ON (v.id = pv.vendor_id) - WHERE pv.parts_id = ?|; - $sth = $dbh->prepare($query) || $form->dberror($query); - - # get cost and vendor - $sth->execute($parts_id); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - for (keys %$ref) { $form->{orderitems}{$parts_id}{partsvendor}{$ref->{vendor_id}}{$_} = $ref->{$_} } - } - $sth->finish; + } else { + + $query = qq| + SELECT partnumber, description, lastcost + FROM parts + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($parts_id) || $form->dberror($query); + $ref = $sth->fetchrow_hashref(NAME_lc); + for (keys %$ref) { + $form->{orderitems}{$parts_id}{$_} = $ref->{$_}; + } + $sth->finish; + + $form->{orderitems}{$parts_id}{required} += $required; + + $query = qq| + SELECT pv.partnumber, pv.leadtime, pv.lastcost, pv.curr, + pv.vendor_id, v.name + FROM partsvendor pv + JOIN vendor v ON (v.id = pv.vendor_id) + WHERE pv.parts_id = ?|; + $sth = $dbh->prepare($query) || $form->dberror($query); + + # get cost and vendor + $sth->execute($parts_id); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + for (keys %$ref) { + $form->{orderitems}{$parts_id}{partsvendor} + {$ref->{vendor_id}}{$_} = $ref->{$_}; + } + } + $sth->finish; - } + } } sub generate_orders { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my $i; - my %a; - my $query; - my $sth; + my $i; + my %a; + my $query; + my $sth; - for ($i = 1; $i <= $form->{rowcount}; $i++) { - for (qw(qty lastcost)) { $form->{"${_}_$i"} = $form->parse_amount($myconfig, $form->{"${_}_$i"}) } + for ($i = 1; $i <= $form->{rowcount}; $i++) { + for (qw(qty lastcost)) { + $form->{"${_}_$i"} = $form->parse_amount( + $myconfig, $form->{"${_}_$i"}); + } - if ($form->{"qty_$i"}) { - ($vendor, $vendor_id) = split /--/, $form->{"vendor_$i"}; - if ($vendor_id) { - $a{$vendor_id}{$form->{"id_$i"}}{qty} += $form->{"qty_$i"}; - for (qw(curr lastcost)) { $a{$vendor_id}{$form->{"id_$i"}}{$_} = $form->{"${_}_$i"} } - } - } - } + if ($form->{"qty_$i"}) { + ($vendor, $vendor_id) = + split /--/, $form->{"vendor_$i"}; + if ($vendor_id) { + $a{$vendor_id}{$form->{"id_$i"}}{qty} + += $form->{"qty_$i"}; + for (qw(curr lastcost)) { + $a{$vendor_id}{$form->{"id_$i"}}{$_} + = $form->{"${_}_$i"}; + } + } + } + } - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); - # foreign exchange rates - &exchangerate_defaults($dbh, $form); + # foreign exchange rates + &exchangerate_defaults($dbh, $form); - my $amount; - my $netamount; - my $curr = ""; - my %tax; - my $taxincluded = 0; - my $vendor_id; + my $amount; + my $netamount; + my $curr = ""; + my %tax; + my $taxincluded = 0; + my $vendor_id; - my $description; - my $unit; + my $description; + my $unit; - my $sellprice; + my $sellprice; - foreach $vendor_id (keys %a) { + foreach $vendor_id (keys %a) { - %tax = (); + %tax = (); - $query = qq|SELECT v.curr, v.taxincluded, t.rate, c.accno - FROM vendor v - LEFT JOIN vendortax vt ON (v.id = vt.vendor_id) - LEFT JOIN tax t ON (t.chart_id = vt.chart_id) - LEFT JOIN chart c ON (c.id = t.chart_id) - WHERE v.id = $vendor_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $curr = $ref->{curr}; - $taxincluded = $ref->{taxincluded}; - $tax{$ref->{accno}} = $ref->{rate}; - } - $sth->finish; - - $curr ||= $form->{defaultcurrency}; - $taxincluded *= 1; + $query = qq| + SELECT v.curr, v.taxincluded, t.rate, c.accno + FROM vendor v + LEFT JOIN vendortax vt ON (v.id = vt.vendor_id) + LEFT JOIN tax t ON (t.chart_id = vt.chart_id) + LEFT JOIN chart c ON (c.id = t.chart_id) + WHERE v.id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($vendor_id) || $form->dberror($query); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $curr = $ref->{curr}; + $taxincluded = $ref->{taxincluded}; + $tax{$ref->{accno}} = $ref->{rate}; + } + $sth->finish; + + $curr ||= $form->{defaultcurrency}; + $taxincluded *= 1; - my $uid = localtime; - $uid .= "$$"; - - $query = qq|INSERT INTO oe (ordnumber) - VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); + my $uid = localtime; + $uid .= "$$"; + + # TODO: Make this function insert as much as possible + $query = qq| + INSERT INTO oe (ordnumber) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); - $query = qq|SELECT id FROM oe - WHERE ordnumber = '$uid'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my ($id) = $sth->fetchrow_array; - $sth->finish; - - $amount = 0; - $netamount = 0; + $query = qq|SELECT id FROM oe WHERE ordnumber = '$uid'|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + my ($id) = $sth->fetchrow_array; + $sth->finish; + + $amount = 0; + $netamount = 0; - foreach my $parts_id (keys %{ $a{$vendor_id} }) { - - if (($form->{$curr} * $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) { - $sellprice = $a{$vendor_id}{$parts_id}{lastcost} / $form->{$curr} * $form->{$a{$vendor_id}{$parts_id}{curr}}; - } else { - $sellprice = $a{$vendor_id}{$parts_id}{lastcost}; - } - $sellprice = $form->round_amount($sellprice, 2); + foreach my $parts_id (keys %{ $a{$vendor_id} }) { + + if (($form->{$curr} + * $form->{$a{$vendor_id}{$parts_id}{curr}}) > 0) { + + $sellprice = $a{$vendor_id}{$parts_id}{lastcost} + / $form->{$curr} + * $form->{$a{$vendor_id}{$parts_id} + {curr}}; + } else { + $sellprice = + $a{$vendor_id}{$parts_id}{lastcost}; + } + $sellprice = $form->round_amount($sellprice, 2); - my $linetotal = $form->round_amount($sellprice * $a{$vendor_id}{$parts_id}{qty}, 2); + my $linetotal = $form->round_amount( + $sellprice * $a{$vendor_id}{$parts_id}{qty}, 2); - $query = qq|SELECT p.description, p.unit, c.accno FROM parts p - LEFT JOIN partstax pt ON (p.id = pt.parts_id) - LEFT JOIN chart c ON (c.id = pt.chart_id) - WHERE p.id = $parts_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $query = qq| + SELECT p.description, p.unit, c.accno + FROM parts p + LEFT JOIN partstax pt ON (p.id = pt.parts_id) + LEFT JOIN chart c ON (c.id = pt.chart_id) + WHERE p.id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($parts_id) || $form->dberror($query); - my $rate = 0; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $description = $ref->{description}; - $unit = $ref->{unit}; - $rate += $tax{$ref->{accno}}; - } - $sth->finish; - - $netamount += $linetotal; - if ($taxincluded) { - $amount += $linetotal; - } else { - $amount += $form->round_amount($linetotal * (1 + $rate), 2); - } + my $rate = 0; + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $description = $ref->{description}; + $unit = $ref->{unit}; + $rate += $tax{$ref->{accno}}; + } + $sth->finish; + + $netamount += $linetotal; + if ($taxincluded) { + $amount += $linetotal; + } else { + $amount += $form->round_amount( + $linetotal * (1 + $rate), 2); + } - $description = $dbh->quote($description); - $unit = $dbh->quote($unit); - $query = qq|INSERT INTO orderitems (trans_id, parts_id, description, - qty, ship, sellprice, unit) VALUES - ($id, $parts_id, $description, - $a{$vendor_id}{$parts_id}{qty}, 0, $sellprice, $unit)|; - $dbh->do($query) || $form->dberror($query); + $query = qq| + INSERT INTO orderitems + (trans_id, parts_id, description, + qty, ship, sellprice, unit) + VALUES + (?, ?, ?, ?, 0, ?, ?)|; + $sth = $dbh->prepare($query); + $sth->execute( + $id, $parts_id, $description, + $a{vendor_id}{parts_id}{qty}, $sellprice, + $unit + ) || $form->dberror($query); - } + } - my $ordnumber = $form->update_defaults($myconfig, 'ponumber'); + my $ordnumber = $form->update_defaults($myconfig, 'ponumber'); - my $null; - my $employee_id; - my $department_id; + my $null; + my $employee_id; + my $department_id; - ($null, $employee_id) = $form->get_employee($dbh); - ($null, $department_id) = split /--/, $form->{department}; - $department_id *= 1; + ($null, $employee_id) = $form->get_employee($dbh); + ($null, $department_id) = split /--/, $form->{department}; + $department_id *= 1; - $query = qq|UPDATE oe SET - ordnumber = |.$dbh->quote($ordnumber).qq|, - transdate = current_date, - vendor_id = $vendor_id, - customer_id = 0, - amount = $amount, - netamount = $netamount, - taxincluded = '$taxincluded', - curr = '$curr', - employee_id = $employee_id, - department_id = '$department_id', - ponumber = |.$dbh->quote($form->{ponumber}).qq| - WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); + $query = qq| + UPDATE oe SET + ordnumber = ?, + transdate = current_date, + vendor_id = ?, + customer_id = 0, + amount = ?, + netamount = ?, + taxincluded = ?, + curr = ?, + employee_id = ?, + department_id = ?, + ponumber = ? + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + $ordnumber, $vendor_id, $amount, $netamount, + $taxincluded, $curr, $employee_id, + $department_id, $form->{ponumber}, $id + ) || $form->dberror($query); - } + } - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub consolidate_orders { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + # connect to database + my $dbh = $form->{dbh}; - my $i; - my $id; - my $ref; - my %oe = (); + my $i; + my $id; + my $ref; + my %oe = (); - my $query = qq|SELECT * FROM oe - WHERE id = ?|; - my $sth = $dbh->prepare($query) || $form->dberror($query); + my $query = qq|SELECT * FROM oe WHERE id = ?|; + my $sth = $dbh->prepare($query) || $form->dberror($query); - for ($i = 1; $i <= $form->{rowcount}; $i++) { - # retrieve order - if ($form->{"ndx_$i"}) { - $sth->execute($form->{"ndx_$i"}); + for ($i = 1; $i <= $form->{rowcount}; $i++) { + # retrieve order + if ($form->{"ndx_$i"}) { + $sth->execute($form->{"ndx_$i"}); - $ref = $sth->fetchrow_hashref(NAME_lc); - $ref->{ndx} = $i; - $oe{oe}{$ref->{curr}}{$ref->{id}} = $ref; + $ref = $sth->fetchrow_hashref(NAME_lc); + $ref->{ndx} = $i; + $oe{oe}{$ref->{curr}}{$ref->{id}} = $ref; - $oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++; - $sth->finish; - } - } + $oe{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++; + $sth->finish; + } + } - $query = qq|SELECT * FROM orderitems - WHERE trans_id = ?|; - $sth = $dbh->prepare($query) || $form->dberror($query); + $query = qq|SELECT * FROM orderitems WHERE trans_id = ?|; + $sth = $dbh->prepare($query) || $form->dberror($query); - foreach $curr (keys %{ $oe{oe} }) { + foreach $curr (keys %{ $oe{oe} }) { - foreach $id (sort { $oe{oe}{$curr}{$a}->{ndx} <=> $oe{oe}{$curr}{$b}->{ndx} } keys %{ $oe{oe}{$curr} }) { + foreach $id (sort { + $oe{oe}{$curr}{$a}->{ndx} + <=> $oe{oe}{$curr}{$b}->{ndx} } + keys %{ $oe{oe}{$curr} }) { - # retrieve order - $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"}; + # retrieve order + $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"}; - if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) { + if ($oe{vc}{$oe{oe}{$curr}{$id}->{curr}}{$vc_id} > 1) { - push @{ $oe{orders}{$curr}{$vc_id} }, $id; + push @{ $oe{orders}{$curr}{$vc_id} }, $id; - $sth->execute($id); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $oe{orderitems}{$curr}{$id} }, $ref; - } - $sth->finish; + $sth->execute($id); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $oe{orderitems}{$curr}{$id} }, + $ref; + } + $sth->finish; - } - } - } + } + } + } - my $ordnumber = $form->{ordnumber}; - my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber'; + my $ordnumber = $form->{ordnumber}; + my $numberfld = ($form->{vc} eq 'customer') ? 'sonumber' : 'ponumber'; - my ($department, $department_id) = $form->{department}; - $department_id *= 1; + my ($department, $department_id) = $form->{department}; + $department_id *= 1; - my $uid = localtime; - $uid .= "$$"; + my $uid = localtime; + $uid .= "$$"; - my @orderitems = (); + my @orderitems = (); - foreach $curr (keys %{ $oe{orders} }) { + foreach $curr (keys %{ $oe{orders} }) { - foreach $vc_id (sort { $a <=> $b } keys %{ $oe{orders}{$curr} }) { - # the orders - @orderitems = (); - $form->{customer_id} = $form->{vendor_id} = 0; - $form->{"$form->{vc}_id"} = $vc_id; - $amount = 0; - $netamount = 0; + foreach $vc_id (sort { + $a <=> $b + } keys %{ $oe{orders}{$curr} }) { + + # the orders + @orderitems = (); + $form->{customer_id} = $form->{vendor_id} = 0; + $form->{"$form->{vc}_id"} = $vc_id; + $amount = 0; + $netamount = 0; - foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) { + foreach $id (@{ $oe{orders}{$curr}{$vc_id} }) { - # header - $ref = $oe{oe}{$curr}{$id}; + # header + $ref = $oe{oe}{$curr}{$id}; - $amount += $ref->{amount}; - $netamount += $ref->{netamount}; + $amount += $ref->{amount}; + $netamount += $ref->{netamount}; - foreach $item (@{ $oe{orderitems}{$curr}{$id} }) { - push @orderitems, $item; - } + $id = $dbh->quore($id); + foreach $item + (@{ $oe{orderitems}{$curr}{$id} }) { - # close order - $query = qq|UPDATE oe SET - closed = '1' - WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); - - # reset shipped - $query = qq|UPDATE orderitems SET - ship = 0 - WHERE trans_id = $id|; - $dbh->do($query) || $form->dberror($query); - - } + push @orderitems, $item; + } - $ordnumber ||= $form->update_defaults($myconfig, $numberfld, $dbh); - - $query = qq|INSERT INTO oe (ordnumber) - VALUES ($uid)|; - $dbh->do($query) || $form->dberror($query); + # close order + $query = qq| + UPDATE oe SET + closed = '1' + WHERE id = $id|; + $dbh->do($query) || $form->dberror($query); + + # reset shipped + $query = qq| + UPDATE orderitems SET + ship = 0 + WHERE trans_id = $id|; + $dbh->do($query) || $form->dberror($query); + } + + $ordnumber ||= $form->update_defaults( + $myconfig, $numberfld, $dbh); + + #fixme: Change this + $query = qq| + INSERT INTO oe (ordnumber) VALUES ($uid)|; + $dbh->do($query) || $form->dberror($query); - $query = qq|SELECT id - FROM oe - WHERE ordnumber = '$uid'|; - ($id) = $dbh->selectrow_array($query); + $query = qq| + SELECT id + FROM oe + WHERE ordnumber = '$uid'|; + ($id) = $dbh->selectrow_array($query); - $ref->{employee_id} *= 1; + $ref->{employee_id} *= 1; - $query = qq|UPDATE oe SET - ordnumber = |.$dbh->quote($ordnumber).qq|, - transdate = current_date, - vendor_id = $form->{vendor_id}, - customer_id = $form->{customer_id}, - amount = $amount, - netamount = $netamount, - reqdate = |.$form->dbquote($ref->{reqdate}, SQL_DATE).qq|, - taxincluded = '$ref->{taxincluded}', - shippingpoint = |.$dbh->quote($ref->{shippingpoint}).qq|, - notes = |.$dbh->quote($ref->{notes}).qq|, - curr = '$curr', - employee_id = $ref->{employee_id}, - intnotes = |.$dbh->quote($ref->{intnotes}).qq|, - shipvia = |.$dbh->quote($ref->{shipvia}).qq|, - language_code = '$ref->{language_code}', - ponumber = |.$dbh->quote($form->{ponumber}).qq|, - department_id = $department_id - WHERE id = $id|; - $dbh->do($query) || $form->dberror($query); + $query = qq| + UPDATE oe SET + ordnumber = |.$dbh->quote($ordnumber).qq|, + transdate = current_date, + vendor_id = $form->{vendor_id}, + customer_id = $form->{customer_id}, + amount = $amount, + netamount = $netamount, + reqdate = |.$form->dbquote($ref->{reqdate}, SQL_DATE).qq|, + taxincluded = '$ref->{taxincluded}', + shippingpoint = |.$dbh->quote($ref->{shippingpoint}).qq|, + notes = |.$dbh->quote($ref->{notes}).qq|, + curr = '$curr', + employee_id = $ref->{employee_id}, + intnotes = |.$dbh->quote($ref->{intnotes}).qq|, + shipvia = |.$dbh->quote($ref->{shipvia}).qq|, + language_code = '$ref->{language_code}', + ponumber = |.$dbh->quote($form->{ponumber}).qq|, + department_id = $department_id + WHERE id = $id|; + $sth = $dbh->prepare($query); + $sth->execute ( + $department_id, $id + ) || $form->dberror($query); - # add items - foreach $item (@orderitems) { - for (qw(qty sellprice discount project_id ship)) { $item->{$_} *= 1 } - $query = qq|INSERT INTO orderitems ( - trans_id, parts_id, description, - qty, sellprice, discount, - unit, reqdate, project_id, - ship, serialnumber, notes) - VALUES ( - $id, $item->{parts_id}, |.$dbh->quote($item->{description}).qq|, - $item->{qty}, $item->{sellprice}, $item->{discount}, - |.$dbh->quote($item->{unit}).qq|, |.$form->dbquote($item->{reqdate}, SQL_DATE).qq|, $item->{project_id}, - $item->{ship}, |.$dbh->quote($item->{serialnumber}).qq|, |.$dbh->quote($item->{notes}).qq|)|; - - $dbh->do($query) || $form->dberror($query); + # add items + foreach $item (@orderitems) { + for (qw( + qty sellprice discount project_id ship) + ) { + $item->{$_} *= 1; + } + $query = qq| + INSERT INTO orderitems + (trans_id, parts_id, description, + qty, sellprice, discount, unit, reqdate, + project_id, ship, serialnumber, notes) + VALUES + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; + + $sth = $dbh->prepare($query); + $sth->execute( + $id, $item->{parts_id}, $item->{description}, + $item->{qty}, $item->{sellprice}, + $item->{discount}, $item->{unit}, + $form->{reqdate}, $item->{project_id}, + $item->{ship}, $item->{serialnumber}, + $item->{notes} + ) || $form->dberror($query); - } - } - } + } + } + } - $rc = $dbh->commit; - $dbh->disconnect; + $rc = $dbh->commit; - $rc; + $rc; } -- cgit v1.2.3