summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xLedgerSMB/OE.pm334
1 files changed, 179 insertions, 155 deletions
diff --git a/LedgerSMB/OE.pm b/LedgerSMB/OE.pm
index 44d3dcd4..29ec339f 100755
--- a/LedgerSMB/OE.pm
+++ b/LedgerSMB/OE.pm
@@ -23,7 +23,7 @@
#
#======================================================================
#
-# This file has undergone PARTIAL (30%) whitespace cleanup To line 638
+# This file has undergone PARTIAL (47%) whitespace cleanup To line 1194
#
#======================================================================
#
@@ -980,191 +980,215 @@ sub retrieve {
sub price_matrix_query {
- my ($dbh, $form) = @_;
+ my ($dbh, $form) = @_;
- my $query;
- my $sth;
+ my $query;
+ my $sth;
- if ($form->{customer_id}) {
- $query = qq|SELECT p.id AS parts_id, 0 AS customer_id, 0 AS pricegroup_id,
- 0 AS pricebreak, p.sellprice, NULL AS validfrom, NULL AS validto,
- '$form->{defaultcurrency}' AS curr, '' AS pricegroup
- FROM parts p
- WHERE p.id = ?
+ my @queryargs;
- UNION
-
- SELECT p.*, g.pricegroup
- FROM partscustomer p
- LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
- WHERE p.parts_id = ?
- AND p.customer_id = $form->{customer_id}
-
- UNION
-
- SELECT p.*, g.pricegroup
- FROM partscustomer p
- LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
- JOIN customer c ON (c.pricegroup_id = g.id)
- WHERE p.parts_id = ?
- AND c.id = $form->{customer_id}
-
- UNION
-
- SELECT p.*, '' AS pricegroup
- FROM partscustomer p
- WHERE p.customer_id = 0
- AND p.pricegroup_id = 0
- AND p.parts_id = ?
-
- ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak
- |;
- $sth = $dbh->prepare($query) || $form->dberror($query);
- }
-
- if ($form->{vendor_id}) {
- # price matrix and vendor's partnumber
- $query = qq|SELECT partnumber
- FROM partsvendor
- WHERE parts_id = ?
- AND vendor_id = $form->{vendor_id}|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
- }
+ if ($form->{customer_id}) {
+ my $defaultcurrency = $form->{dbh}->quote(
+ $form->{defaultcurrency});
+ my $customer_id = $form->{dbh}->quote($form->{customer_id});
+ $query = qq|
+ SELECT p.id AS parts_id, 0 AS customer_id,
+ 0 AS pricegroup_id, 0 AS pricebreak,
+ p.sellprice, NULL AS validfrom, NULL AS validto,
+ $defaultcurrency AS curr, '' AS pricegroup
+ FROM parts p
+ WHERE p.id = ?
+
+ UNION
+
+ SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ WHERE p.parts_id = ?
+ AND p.customer_id = $customer_id
+
+ UNION
+
+ SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ JOIN customer c ON (c.pricegroup_id = g.id)
+ WHERE p.parts_id = ?
+ AND c.id = $customer_id
+
+ UNION
+
+ SELECT p.*, '' AS pricegroup
+ FROM partscustomer p
+ WHERE p.customer_id = 0
+ AND p.pricegroup_id = 0
+ AND p.parts_id = ?
+
+ ORDER BY customer_id DESC, pricegroup_id DESC,
+ pricebreak
+ |;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
+ } elsif ($form->{vendor_id}) {
+ my $vendor_id = $form->{dbh}->quote($form->{vendor_id});
+ # price matrix and vendor's partnumber
+ $query = qq|
+ SELECT partnumber
+ FROM partsvendor
+ WHERE parts_id = ?
+ AND vendor_id = $vendor_id|;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
+ }
- $sth;
-
+ $sth;
}
sub price_matrix {
- my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig) = @_;
-
- $ref->{pricematrix} = "";
- my $customerprice;
- my $pricegroupprice;
- my $sellprice;
- my $mref;
- my %p = ();
-
- # depends if this is a customer or vendor
- if ($form->{customer_id}) {
- $pmh->execute($ref->{id}, $ref->{id}, $ref->{id}, $ref->{id});
-
- while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {
-
- # check date
- if ($mref->{validfrom}) {
- next if $transdate < $form->datetonum($myconfig, $mref->{validfrom});
- }
- if ($mref->{validto}) {
- next if $transdate > $form->datetonum($myconfig, $mref->{validto});
- }
+ my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig) = @_;
+ $ref->{pricematrix} = "";
+ my $customerprice;
+ my $pricegroupprice;
+ my $sellprice;
+ my $mref;
+ my %p = ();
+
+ # depends if this is a customer or vendor
+ if ($form->{customer_id}) {
+ $pmh->execute($ref->{id}, $ref->{id}, $ref->{id}, $ref->{id});
+
+ while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {
+
+ # check date
+ if ($mref->{validfrom}) {
+ next if $transdate < $form->datetonum(
+ $myconfig, $mref->{validfrom});
+ }
+ if ($mref->{validto}) {
+ next if $transdate > $form->datetonum(
+ $myconfig, $mref->{validto});
+ }
- # convert price
- $sellprice = $form->round_amount($mref->{sellprice} * $form->{$mref->{curr}}, $decimalplaces);
+ # convert price
+ $sellprice = $form->round_amount($mref->{sellprice}
+ * $form->{$mref->{curr}}, $decimalplaces);
- if ($mref->{customer_id}) {
- $ref->{sellprice} = $sellprice if !$mref->{pricebreak};
- $p{$mref->{pricebreak}} = $sellprice;
- $customerprice = 1;
- }
+ if ($mref->{customer_id}) {
+ $ref->{sellprice} = $sellprice
+ if !$mref->{pricebreak};
+ $p{$mref->{pricebreak}} = $sellprice;
+ $customerprice = 1;
+ }
- if ($mref->{pricegroup_id}) {
- if (! $customerprice) {
- $ref->{sellprice} = $sellprice if !$mref->{pricebreak};
- $p{$mref->{pricebreak}} = $sellprice;
- }
- $pricegroupprice = 1;
- }
+ if ($mref->{pricegroup_id}) {
+ if (! $customerprice) {
+ $ref->{sellprice} = $sellprice
+ if !$mref->{pricebreak};
+ $p{$mref->{pricebreak}} = $sellprice;
+ }
+ $pricegroupprice = 1;
+ }
- if (!$customerprice && !$pricegroupprice) {
- $p{$mref->{pricebreak}} = $sellprice;
- }
+ if (!$customerprice && !$pricegroupprice) {
+ $p{$mref->{pricebreak}} = $sellprice;
+ }
- }
- $pmh->finish;
+ }
+ $pmh->finish;
- if (%p) {
- if ($ref->{sellprice}) {
- $p{0} = $ref->{sellprice};
- }
- for (sort { $a <=> $b } keys %p) { $ref->{pricematrix} .= "${_}:$p{$_} " }
- } else {
- if ($init) {
- $ref->{sellprice} = $form->round_amount($ref->{sellprice}, $decimalplaces);
- } else {
- $ref->{sellprice} = $form->round_amount($ref->{sellprice} * (1 - $form->{tradediscount}), $decimalplaces);
- }
- $ref->{pricematrix} = "0:$ref->{sellprice} " if $ref->{sellprice};
- }
- chop $ref->{pricematrix};
+ if (%p) {
+ if ($ref->{sellprice}) {
+ $p{0} = $ref->{sellprice};
+ }
+ for (sort { $a <=> $b } keys %p) {
+ $ref->{pricematrix} .= "${_}:$p{$_} ";
+ }
+ } else {
+ if ($init) {
+ $ref->{sellprice} = $form->round_amount(
+ $ref->{sellprice}, $decimalplaces);
+ } else {
+ $ref->{sellprice} = $form->round_amount(
+ $ref->{sellprice} *
+ (1 - $form->{tradediscount}),
+ $decimalplaces);
+ }
+ $ref->{pricematrix} = "0:$ref->{sellprice} "
+ if $ref->{sellprice};
+ }
+ chop $ref->{pricematrix};
- }
+ }
- if ($form->{vendor_id}) {
- $pmh->execute($ref->{id});
+ if ($form->{vendor_id}) {
+ $pmh->execute($ref->{id});
- $mref = $pmh->fetchrow_hashref(NAME_lc);
+ $mref = $pmh->fetchrow_hashref(NAME_lc);
- if ($mref->{partnumber} ne "") {
- $ref->{partnumber} = $mref->{partnumber};
- }
+ if ($mref->{partnumber} ne "") {
+ $ref->{partnumber} = $mref->{partnumber};
+ }
- if ($mref->{lastcost}) {
- # do a conversion
- $ref->{sellprice} = $form->round_amount($mref->{lastcost} * $form->{$mref->{curr}}, $decimalplaces);
- }
- $pmh->finish;
+ if ($mref->{lastcost}) {
+ # do a conversion
+ $ref->{sellprice} = $form->round_amount(
+ $mref->{lastcost} * $form->{$mref->{curr}},
+ $decimalplaces);
+ }
+ $pmh->finish;
- $ref->{sellprice} *= 1;
+ $ref->{sellprice} *= 1;
- # add 0:price to matrix
- $ref->{pricematrix} = "0:$ref->{sellprice}";
+ # add 0:price to matrix
+ $ref->{pricematrix} = "0:$ref->{sellprice}";
- }
+ }
}
sub exchangerate_defaults {
- my ($dbh, $form) = @_;
+ my ($dbh2, $form) = @_;
+ $dbh = $form->{dbh};
+ my $var;
+ my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
+
+ # get default currencies
+ my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ ($form->{defaultcurrency}, $form->{currencies})
+ = $dbh->selectrow_array($query);
- my $var;
- my $buysell = ($form->{vc} eq "customer") ? "buy" : "sell";
-
- # get default currencies
- my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
- ($form->{defaultcurrency}, $form->{currencies}) = $dbh->selectrow_array($query);
-
- $query = qq|SELECT $buysell
- FROM exchangerate
- WHERE curr = ?
- AND transdate = ?|;
- my $eth1 = $dbh->prepare($query) || $form->dberror($query);
- $query = qq~SELECT max(transdate || ' ' || $buysell || ' ' || curr)
- FROM exchangerate
- WHERE curr = ?~;
- my $eth2 = $dbh->prepare($query) || $form->dberror($query);
-
- # get exchange rates for transdate or max
- foreach $var (split /:/, substr($form->{currencies},4)) {
- $eth1->execute($var, $form->{transdate});
- ($form->{$var}) = $eth1->fetchrow_array;
- if (! $form->{$var} ) {
- $eth2->execute($var);
+ $query = qq|
+ SELECT $buysell
+ FROM exchangerate
+ WHERE curr = ?
+ AND transdate = ?|;
+ my $eth1 = $dbh->prepare($query) || $form->dberror($query);
+ $query = qq~
+ SELECT max(transdate || ' ' || $buysell || ' ' || curr)
+ FROM exchangerate
+ WHERE curr = ?~;
+ my $eth2 = $dbh->prepare($query) || $form->dberror($query);
+
+ # get exchange rates for transdate or max
+ foreach $var (split /:/, substr($form->{currencies},4)) {
+ $eth1->execute($var, $form->{transdate});
+ ($form->{$var}) = $eth1->fetchrow_array;
+ if (! $form->{$var} ) {
+ $eth2->execute($var);
- ($form->{$var}) = $eth2->fetchrow_array;
- ($null, $form->{$var}) = split / /, $form->{$var};
- $form->{$var} = 1 unless $form->{$var};
- $eth2->finish;
- }
- $eth1->finish;
- }
+ ($form->{$var}) = $eth2->fetchrow_array;
+ ($null, $form->{$var}) = split / /, $form->{$var};
+ $form->{$var} = 1 unless $form->{$var};
+ $eth2->finish;
+ }
+ $eth1->finish;
+ }
- $form->{$form->{currency}} = $form->{exchangerate} if $form->{exchangerate};
- $form->{$form->{currency}} ||= 1;
- $form->{$form->{defaultcurrency}} = 1;
+ $form->{$form->{currency}} = $form->{exchangerate}
+ if $form->{exchangerate};
+ $form->{$form->{currency}} ||= 1;
+ $form->{$form->{defaultcurrency}} = 1;
}