PrestaShop 1.6 PayPal Shipping Discrepancy: A Deep Dive into Diagnostic SQL

PrestaShop 1.6 & PayPal: Unraveling Shipping Option Display Errors

E-commerce merchants often face intricate challenges, especially when dealing with payment gateways and shipping options. A recent discussion on the PrestaShop forum highlighted a peculiar issue affecting PrestaShop 1.6 users: a discrepancy between the displayed shipping option and the actual amount charged by PayPal, specifically when a secondary shipping option is selected. While the customer is charged correctly, both the customer's receipt and the back office order details incorrectly display the cheaper, primary shipping option, leading to confusion and manual verification for merchants.

The Problem: 'Paid X Instead of Y' with PayPal and Multiple Shipping Options

The user, BlizzardUK, detailed a persistent problem with their PrestaShop 1.6 store, which uses a custom theme and the Agile PayPal module. When offering two shipping options, the second (more expensive) option consistently caused a visual error with PayPal payments. Customers selecting the second option would be correctly charged, but their order confirmation and the back office order summary would show the details of the first, cheaper shipping option. The only indication of the correct charge was a red error message in the back office stating, for example, "paid £22.99 instead of £22.10," where the difference (89p in this case) corresponded to the price difference between the two shipping options.

Crucially, this issue was exclusive to PayPal; other credit card payment modules processed both shipping options without any display errors. The PayPal module developer had previously stated the issue wasn't with their module, leaving the merchant in a diagnostic limbo. The core confusion stemmed from the system expecting the postage to be option 1, even when option 2 was chosen and correctly charged.

The Diagnostic Approach: A Deep Dive into PrestaShop's Database

Forum member El Patron offered a comprehensive diagnostic strategy, emphasizing the importance of comparing carrier and payment data directly within the PrestaShop database. This approach is vital for pinpointing whether the carrier selection is being lost during the PayPal validation process or if the PayPal module has a calculation issue.

The key steps for diagnosis involve:

• Comparing Carrier IDs: Check if the selected carrier in ps_cart.id_carrier matches the carrier stored in ps_orders.id_carrier and ps_order_carrier.id_carrier. A mismatch here suggests the carrier selection is being lost.
• Verifying Payment Amounts: Compare ps_order_payment.amount with ps_orders.total_paid and ps_orders.total_paid_real. Discrepancies here might point to a rounding, tax, or line-item calculation problem within the PayPal module.
• Checking Carrier History: Investigate ps_carrier records. If a postage option was edited, PrestaShop might create a new id_carrier with the same id_reference, which can confuse older modules that don't correctly respect carrier references.

To facilitate this, El Patron provided a powerful SQL query designed to extract all relevant information for a specific order:

SELECT 
    o.id_order, 
    o.reference, 
    o.id_cart, 
    c.id_carrier AS cart_id_carrier, 
    cart_carrier.name AS cart_carrier_name, 
    cart_carrier.id_reference AS cart_carrier_reference, 
    o.id_carrier AS order_id_carrier, 
    order_carrier.name AS order_carrier_name, 
    order_carrier.id_reference AS order_carrier_reference, 
    oc.id_carrier AS order_carrier_table_id_carrier, 
    oc_carrier.name AS order_carrier_table_name, 
    oc_carrier.id_reference AS order_carrier_table_reference, 
    oc.shipping_cost_tax_incl, 
    o.total_shipping_tax_incl, 
    o.total_paid, 
    o.total_paid_real, 
    op.amount AS payment_amount, 
    op.payment_method, 
    op.transaction_id, 
    ROUND(o.total_paid - op.amount, 6) AS total_paid_difference, 
    ROUND(o.total_paid_real - op.amount, 6) AS total_paid_real_difference 
FROM ps_orders o 
LEFT JOIN ps_cart c ON c.id_cart = o.id_cart 
LEFT JOIN ps_order_carrier oc ON oc.id_order = o.id_order 
LEFT JOIN ps_order_payment op ON op.order_reference = o.reference 
LEFT JOIN ps_carrier cart_carrier ON cart_carrier.id_carrier = c.id_carrier 
LEFT JOIN ps_carrier order_carrier ON order_carrier.id_carrier = o.id_carrier 
LEFT JOIN ps_carrier oc_carrier ON oc_carrier.id_carrier = oc.id_carrier 
WHERE o.reference = 'ORDER_REFERENCE_HERE';

This query, executed via a tool like phpMyAdmin, allows merchants or developers to systematically compare the carrier IDs and payment amounts at different stages of the order process, providing crucial insights into where the discrepancy originates. The user, initially unfamiliar with direct database queries, was guided on how to use phpMyAdmin to execute the SQL, demonstrating the community's helpfulness in technical troubleshooting.

Conclusion

This thread exemplifies the complex nature of e-commerce debugging, especially with older PrestaShop versions and custom module interactions. While a direct fix wasn't provided, the detailed diagnostic SQL query offers an invaluable starting point for any PrestaShop merchant or developer facing similar payment gateway and shipping option discrepancies. Understanding the flow of data through PrestaShop's database tables is key to resolving such intricate issues.