From 57617cdf31a98a34f46f717749b677a4e84f3868 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Sat, 16 Aug 2008 20:56:10 +0000 Subject: * Fixing price matrix logic (1897245) git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.2@2270 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/IC.pm | 2 +- LedgerSMB/IR.pm | 8 +-- LedgerSMB/IS.pm | 8 +-- LedgerSMB/PriceMatrix.pm | 166 +++++++++++++---------------------------------- LedgerSMB/Sysconfig.pm | 2 +- bin/ic.pl | 2 +- 6 files changed, 57 insertions(+), 131 deletions(-) diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm index 09bc0177..8cfabdb2 100755 --- a/LedgerSMB/IC.pm +++ b/LedgerSMB/IC.pm @@ -543,7 +543,7 @@ sub save { $form->parse_amount( $myconfig, $form->{"${_}_$i"} ); } - if ( $form->{"customerprice_$i"} ) { + if ( $form->{"customerprice_$i"} || $form->{"pricebreak_$i"} ) { ( $null, $customer_id ) = split /--/, $form->{"customer_$i"}; $customer_id *= 1; diff --git a/LedgerSMB/IR.pm b/LedgerSMB/IR.pm index 1de8155a..117cf61d 100755 --- a/LedgerSMB/IR.pm +++ b/LedgerSMB/IR.pm @@ -1165,6 +1165,8 @@ sub retrieve_invoice { my $ptref; while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + PriceMatrix::price_matrix( $pmh, $ref, '', $decimalplaces, $form, + $myconfig ); $form->db_parse_numeric(sth=>$sth, hashref=>$ref); my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ ); @@ -1189,8 +1191,6 @@ sub retrieve_invoice { $form->round_amount( $ref->{fxsellprice} * $form->{ $form->{currency} }, $decimalplaces ); - PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form, - $myconfig ); $ref->{sellprice} = $ref->{fxsellprice}; $ref->{qty} *= -1; @@ -1289,6 +1289,8 @@ sub retrieve_item { my $ptref; while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + PriceMatrix::price_matrix( $pmh, $ref, '', $decimalplaces, $form, + $myconfig ); $form->db_parse_numeric(sth=>$sth, hashref=>$ref); my ($dec) = ( $ref->{sellprice} =~ /\.(\d+)/ ); @@ -1306,8 +1308,6 @@ sub retrieve_item { chop $ref->{taxaccounts}; # get vendor price and partnumber - PriceMatrix::price_matrix( $pmh, $ref, $decimalplaces, $form, - $myconfig ); $ref->{description} = $ref->{translation} if $ref->{translation}; diff --git a/LedgerSMB/IS.pm b/LedgerSMB/IS.pm index 7c873e9e..b577f72c 100755 --- a/LedgerSMB/IS.pm +++ b/LedgerSMB/IS.pm @@ -1977,6 +1977,8 @@ sub retrieve_invoice { my $ptref; while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + PriceMatrix::price_matrix( $pmh, $ref, $form->{transdate}, + $decimalplaces, $form, $myconfig ); $form->db_parse_numeric(sth=>$sth, hashref => $ref); my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ ); $dec = length $dec; @@ -1997,8 +1999,6 @@ sub retrieve_invoice { # price matrix $ref->{sellprice} = ( $ref->{fxsellprice} * $form->{ $form->{currency} } ); - PriceMatrix::price_matrix( $pmh, $ref, $form->{transdate}, - $decimalplaces, $form, $myconfig ); $ref->{sellprice} = $ref->{fxsellprice}; $ref->{partsgroup} = $ref->{partsgrouptranslation} @@ -2104,6 +2104,8 @@ sub retrieve_item { my $transdate = $form->datetonum( $myconfig, $form->{transdate} ); while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + PriceMatrix::price_matrix( $pmh, $ref, $transdate, $decimalplaces, + $form, $myconfig ); $form->db_parse_numeric(sth => $sth, hashref => $ref); my ($dec) = ( $ref->{sellprice} =~ /\.(\d+)/ ); @@ -2122,8 +2124,6 @@ sub retrieve_item { chop $ref->{taxaccounts}; # get matrix - PriceMatrix::price_matrix( $pmh, $ref, $transdate, $decimalplaces, - $form, $myconfig ); $ref->{description} = $ref->{translation} if $ref->{translation}; diff --git a/LedgerSMB/PriceMatrix.pm b/LedgerSMB/PriceMatrix.pm index 2c6cc332..f4482164 100644 --- a/LedgerSMB/PriceMatrix.pm +++ b/LedgerSMB/PriceMatrix.pm @@ -41,54 +41,55 @@ sub price_matrix_query { my $sth; my @queryargs; + my $transdate = $form->{dbh}->quote( $form->{transdate} ); 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, - (SELECT substr(value,1,3) FROM defaults WHERE - setting_key = 'curr') AS curr, - '' AS pricegroup - FROM parts p - WHERE p.id = ? - - UNION - - SELECT p.parts_id, p.customer_id, p.pricegroup_id, + SELECT p.parts_id, p.customer_id, NULL AS pricegroup_id, p.pricebreak, p.sellprice, p.validfrom, - p.validto, p.curr, g.pricegroup + p.validto, p.curr, NULL AS pricegroup, + 1 as priority FROM partscustomer p - LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id) WHERE p.parts_id = ? - AND p.customer_id = $customer_id + AND coalesce(p.validfrom, $transdate) <= + $transdate + AND coalesce(p.validto, $transdate) >= + $transdate + AND p.customer_id = $customer_id UNION SELECT p.parts_id, p.customer_id, p.pricegroup_id, p.pricebreak, p.sellprice, p.validfrom, - p.validto, p.curr, g.pricegroup + p.validto, p.curr, g.pricegroup, 2 AS priority FROM partscustomer p - LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id) + 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 + AND coalesce(p.validfrom, $transdate) <= + $transdate + AND coalesce(p.validto, $transdate) >= + $transdate + AND c.id = $customer_id UNION SELECT p.parts_id, p.customer_id, p.pricegroup_id, p.pricebreak, p.sellprice, p.validfrom, - p.validto, p.curr, g.pricegroup + p.validto, p.curr, g.pricegroup, 3 AS priority FROM partscustomer p LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id) WHERE p.customer_id = 0 - AND p.pricegroup_id = 0 - AND p.parts_id = ? - - ORDER BY customer_id DESC, pricegroup_id DESC, - pricebreak + AND p.pricegroup_id = 0 + AND coalesce(p.validfrom, $transdate) <= + $transdate + AND coalesce(p.validto, $transdate) >= + $transdate + AND p.parts_id = ? + + ORDER BY priority LIMIT 1; |; $sth = $dbh->prepare($query) || $form->dberror($query); } @@ -97,10 +98,10 @@ sub price_matrix_query { # price matrix and vendor's partnumber $query = qq| - SELECT partnumber + SELECT partnumber, lastcost FROM partsvendor WHERE parts_id = ? - AND vendor_id = $vendor_id|; + AND vendor_id = $vendor_id|; $sth = $dbh->prepare($query) || $form->dberror($query); } @@ -109,7 +110,6 @@ sub price_matrix_query { sub price_matrix { my ( $pmh, $ref, $transdate, $decimalplaces, $form, $myconfig ) = @_; - $ref->{pricematrix} = ""; my $customerprice; my $pricegroupprice; my $sellprice; @@ -118,102 +118,28 @@ sub price_matrix { # 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) ) { + $pmh->execute( $ref->{id}, $ref->{id}, $ref->{id} ); + } elsif ( $form->{vendor_id} ) { + $pmh->execute( $ref->{id} ); + } else { + $form->debug(); + $form->error(); + return; + } + if ( $mref = $pmh->fetchrow_hashref(NAME_lc) ) { + if ($form->{customer_id}){ $form->db_parse_numeric(sth=>$sth, hashref=>$mref); - # 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 ); - - 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 ( !$customerprice && !$pricegroupprice ) { - $p{ $mref->{pricebreak} } = $sellprice; + $sellprice = $mref->{sellprice} || $ref->{sellprice}; + if ($mref->{pricebreak}){ + $sellprice = $sellprice + - ($sellprice * ($mref->{pricebreak} / 100)); } - - } - $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 ( $form->{vendor_id} ) { - $pmh->execute( $ref->{id} ); - - $mref = $pmh->fetchrow_hashref(NAME_lc); - $form->db_parse_numeric(sth=>$sth, hashref=>$mref); - - 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; - - $ref->{sellprice} *= 1; - - # add 0:price to matrix - $ref->{pricematrix} = "0:$ref->{sellprice}"; - + $ref->{sellprice} = $sellprice; + } elsif ($form->{vendor_id}){ + $sellprice = $mref->{lastcost} || $ref->{sellprice}; + $ref->{sellprice} = $sellprice; + } } } diff --git a/LedgerSMB/Sysconfig.pm b/LedgerSMB/Sysconfig.pm index 8d4c281b..94d85a35 100644 --- a/LedgerSMB/Sysconfig.pm +++ b/LedgerSMB/Sysconfig.pm @@ -15,7 +15,7 @@ $pathsep = ':'; $session = 'DB'; $logging = 0; # No logging on by default - +$max_post_size = 1024 * 1024; # Default to 1MB @io_lineitem_columns = qw(unit onhand sellprice discount linetotal); # Whitelist for redirect destination diff --git a/bin/ic.pl b/bin/ic.pl index fc940b39..c42cf0ef 100755 --- a/bin/ic.pl +++ b/bin/ic.pl @@ -3324,7 +3324,7 @@ sub check_customer { $form->parse_amount( \%myconfig, $form->{"${_}_$i"} ); } - if ( $form->{"customerprice_$i"} ) { + if ( $form->{"customerprice_$i"} || $form->{"pricebreak_$i"} ) { if ( $form->{"pricebreak_$i"} || $form->{"customer_$i"} || $form->{"pricegroup_$i"} ) -- cgit v1.2.3