* 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

5 files changed, 56 insertions, 130 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