Manually merging in pricematrix changes for ticket 1897245 because codebase is too far off for automatic merge

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2271 4979c152-3d1c-0410-bac9-87ea11338e46

4 files changed, 56 insertions, 133 deletions

diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm
index 9371229d..f1ab883f 100644
--- 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 d2ade36d..5167b079 100644
--- a/LedgerSMB/IR.pm
+++ b/LedgerSMB/IR.pm
@@ -1180,6 +1180,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);
             $ref->{qty} *= -1 if $form->{reverse};
             my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ );
@@ -1204,8 +1206,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;
@@ -1304,6 +1304,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+)/ );
@@ -1321,8 +1323,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 aa6d31f9..51edf0ed 100644
--- a/LedgerSMB/IS.pm
+++ b/LedgerSMB/IS.pm
@@ -1996,6 +1996,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);
             $ref->{qty} *= -1 if $form->{reverse};
             my ($dec) = ( $ref->{fxsellprice} =~ /\.(\d+)/ );
@@ -2017,8 +2019,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}
@@ -2124,6 +2124,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+)/ );
@@ -2142,8 +2144,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 41226ee6..af2dc8d8 100644
--- a/LedgerSMB/PriceMatrix.pm
+++ b/LedgerSMB/PriceMatrix.pm
@@ -46,52 +46,52 @@ sub price_matrix_query {
     if ( $form->{customer_id} ) {
         my $defaultcurrency = $form->{dbh}->quote( $form->{defaultcurrency} );
         $query = qq|
-			SELECT p.id AS parts_id, 0 AS entity_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 AS entity_id, 
-				p.pricegroup_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 = $entity_id
+				AND coalesce(p.validfrom, $transdate) <= 
+					$transdate
+				AND coalesce(p.validto, $transdate) >= 
+					$transdate
+				AND p.customer_id = $entity_id
 
 			UNION
 
-    			SELECT p.parts_id, p.customer_id AS  entity_id, 
+    			SELECT p.parts_id, p.customer_id AS entity_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 entity_credit_account c ON (c.pricegroup_id = g.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.entity_id = $entity_id
+				AND coalesce(p.validfrom, $transdate) <= 
+					$transdate
+				AND coalesce(p.validto, $transdate) >= 
+					$transdate
+				AND c.id = $entity_id
 
 			UNION
 
-    			SELECT p.parts_id, p.customer_id  AS entity_id, 
+    			SELECT p.parts_id, p.customer_id AS entity_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 entity_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);
     }
@@ -99,7 +99,7 @@ sub price_matrix_query {
 
         # price matrix and vendor's partnumber
         $query = qq|
-			SELECT partnumber
+			SELECT partnumber, lastcost
 			FROM partsvendor
 			WHERE parts_id = ?
 			AND entity_id = $entity_id|;
@@ -111,7 +111,6 @@ sub price_matrix_query {
 
 sub price_matrix {
     my ( $pmh, $ref, $transdate, $decimalplaces, $form, $myconfig ) = @_;
-    $ref->{pricematrix} = "";
     my $customerprice;
     my $pricegroupprice;
     my $sellprice;
@@ -120,102 +119,26 @@ 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') ) {
-
-            $form->db_parse_numeric(sth=>$pmh, 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;
-            }
-
-        }
-        $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}, $ref->{id}, $ref->{id} );
+    } elsif ( $form->{vendor_id} ) {
         $pmh->execute( $ref->{id} );
-
-        $mref = $pmh->fetchrow_hashref('NAME_lc');
-        $form->db_parse_numeric(sth=>$pmh, 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}";
-
+    } else {
+        $form->error('Invalid Form');
+        return;
+    }
+    if ( $mref = $pmh->fetchrow_hashref(NAME_lc) ) {
+       if ($form->{customer_id}){
+            $form->db_parse_numeric(sth=>$sth, hashref=>$mref);
+            $sellprice = $mref->{sellprice} || $ref->{sellprice};
+            if ($mref->{pricebreak}){
+		$sellprice = $sellprice
+                           - ($sellprice * ($mref->{pricebreak} / 100));
+            }
+            $ref->{sellprice} = $sellprice;
+       } elsif ($form->{vendor_id}){
+            $sellprice = $mref->{lastcost} || $ref->{sellprice};
+            $ref->{sellprice} = $sellprice;    
+       }
     }
 
 }