Generating Sales Reports with Custom MySQL Queries in PrestaShop

Generating Sales Reports with Custom MySQL Queries in PrestaShop

A PrestaShop merchant sought assistance in creating a custom sales report to list products and their quantities within a specific date range, filtered by orders marked as 'Payment Received'. The original module used for this purpose was no longer maintained, prompting the need for a direct MySQL query solution.

The forum thread highlights a common challenge: extracting specific data from the PrestaShop database for reporting purposes when existing modules fall short or become outdated. While some replies offered general assistance, one provided a practical solution involving a MySQL query.

Solution: MySQL Query for Sales Report

The provided solution involves two steps:

1. Identify the Order Status ID: The query first identifies the id_order_state associated with the 'Payment Received' status. This is crucial for filtering orders based on their status. The following query helps identify the correct ID:

   SELECT id_order_state, name FROM ps_order_state_lang WHERE name LIKE '%Payment%' OR name LIKE '%Received%';

2. Construct the Sales Report Query: The main query retrieves product details and their total quantities for orders with the 'Payment Received' status within a specified date range. Here's the query:

   SELECT od.product_id, od.product_reference, od.product_name, SUM(od.product_quantity) AS qty_sold FROM ps_orders o JOIN ps_order_detail od ON od.id_order = o.id_order WHERE o.current_state IN (/* put id_order_state(s) here */) AND o.date_add >= '2026-01-01 00:00:00' AND o.date_add <= '2026-01-31 23:59:59' GROUP BY od.product_id, od.product_reference, od.product_name ORDER BY qty_sold DESC;

   Important Considerations:

   • Replace /* put id_order_state(s) here */ with the actual id_order_state value(s) obtained from the first query. Multiple IDs can be included, separated by commas (e.g., o.current_state IN (3, 5)).
   • Adjust the date_add values ('2026-01-01 00:00:00' and '2026-01-31 23:59:59') to match the desired date range for the report.
   • The table prefixes (ps_orders, ps_order_detail, ps_order_state_lang) might vary depending on the PrestaShop installation. Verify these prefixes in your database.

This solution provides a starting point for creating custom sales reports in PrestaShop using MySQL. Merchants can further customize this query to include additional data, such as customer information or order totals, by joining other relevant tables.

Disclaimer: Always back up your database before running custom queries. Incorrect queries can potentially damage your data.