Magento – 1.9.2 Catalogue Price Rules rounding (when more then one rule applied with ‘Stop further rules..’)

catalog-price-rulesmagento-1.9rounding

In some cases of catalog price rules i have noticed, that the final price after rule being applied is rounded incorrectly.

Example:
Nett Price for one unit: 22.67 EUR
Rule: 50% of original price

Final price: 11.000
It should be:11.335

Changing rule to 47%

Final price: 12.000
It should be 12.0151

This rule is applied to 2 categories, some products are ok, some with above problems, I can’t find the clue.

IMPORTANT the problem occur if more than one rule is applied for the same product, even if they have "no further rule processing" enabled.

Any ideas? I assume the problem is with the "stop further rule processing".

If it is limitation to community edition, please let me know at least.

Best Answer

Had exactly same issue. If final price was ending with .50 or lower, it would drop the decimal part, and if .51 and higher, it would keep it.

This started happening as we updated from magento CE 1.9.0.1 to 1.9.2.4.

After spending a day digging, I found that it happens in mysql. Portion of the sql code that produces error:

SELECT 
            `cppt`.`customer_group_id`, 
            `cppt`.`product_id`,
            CASE  
                WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @price := 
                    CASE `cppt`.`action_operator` 
                        WHEN 'to_percent' THEN cppt.price * cppt.action_amount/100
                        WHEN 'by_percent' THEN cppt.price * (1 - cppt.action_amount/100)
                        WHEN 'to_fixed' THEN IF((cppt.action_amount < cppt.price), cppt.action_amount, cppt.price)
                        WHEN 'by_fixed' THEN IF((0 > cppt.price - cppt.action_amount), 0, cppt.price - cppt.action_amount) 
                    END                         
                WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id AND IFNULL((@action_stop), 0) = 0 THEN @price := 
                    CASE `cppt`.`action_operator`
                        WHEN 'to_percent' THEN @price * cppt.action_amount/100 
                        WHEN 'by_percent' THEN @price * (1 - cppt.action_amount/100) 
                        WHEN 'to_fixed' THEN IF((cppt.action_amount < @price), cppt.action_amount, @price)
                        WHEN 'by_fixed' THEN IF((0 > @price - cppt.action_amount), 0, @price - cppt.action_amount) 
                    END 
                ELSE @price := @price -- <= THIS LINE CAUSES decimal into string conversion with rounding
            END AS `rule_price`,
            `cppt`.`from_date` AS `latest_start_date`, 
            `cppt`.`to_date` AS `earliest_end_date`, 
            CASE
                WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @action_stop := cppt.action_stop
                WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id THEN @action_stop := IFNULL((@action_stop), 0) + cppt.action_stop 
            END,
            @group_id := cppt.grouped_id, 
            `cppt`.`from_time`, 
            `cppt`.`to_time` 

        FROM `catalogrule_product_price_tmp` AS `cppt` 
        ORDER BY 
            `cppt`.`grouped_id` ASC, 
            `cppt`.`sort_order` ASC, 
            `cppt`.`rule_product_id` ASC;

The line that was causing the error was: @price := @price

The solution is to edit the file app/code/core/Mage/CatalogRule/Model/Action/Index/Refresh.php, in method _calculatePrice() around line 440:

                       $byFixed   => $this->_connection->getCheckSql(
                        new Zend_Db_Expr('0 > @price - cppt.action_amount'),
                        new Zend_Db_Expr('0'),
                        new Zend_Db_Expr('@price - cppt.action_amount')
                    ),
                )
            )
        ),
        '@price := @price' // <-- the culprit
    );
}

replace with: 

                       $byFixed   => $this->_connection->getCheckSql(
                        new Zend_Db_Expr('0 > @price - cppt.action_amount'),
                        new Zend_Db_Expr('0'),
                        new Zend_Db_Expr('@price - cppt.action_amount')
                    ),
                )
            )
        ),
        '@price' // remove ':= @price' to fix rounding bug
    );
}
Related Topic