SQL Query Export multiple Orders with details

[Eutanasio]

Hi,

I'm not sure how to approach this. I'd like an SQL query for PS 1.7 to export orders by Date & Order Status. I need this so later I can use another program to generate multiple invoices.

The export must contain for each order all possible details (order details, payment, customer, products, totals, shipping, discounts, gift wrapping...)
 

is it possible? or way too much and complex?

[Eutanasio]

Thanks ChatGPT!

SELECT
    o.id_order AS order_id,
    o.reference AS order_reference,
    c.firstname AS customer_firstname,
    c.lastname AS customer_lastname,
    a.address1,
    a.address2,
    a.postcode,
    a.city,
    cl.name AS country,
    s.name AS state,
    GROUP_CONCAT(CONCAT(od.product_name, ' (Qty: ', od.product_quantity, ', Price: ', od.product_price, ')') SEPARATOR '; ') AS product_details,
    o.total_shipping_tax_incl AS shipping_cost,
    o.total_wrapping_tax_incl AS wrapping_cost,
    payment.payment_details,
    o.total_paid_tax_excl,
    o.total_paid_tax_incl,
    (o.total_paid_tax_incl - o.total_products_wt - o.total_shipping_tax_incl - o.total_wrapping_tax_incl) AS order_discount,
    o.date_add AS order_date
FROM
    ps_orders o
JOIN
    ps_customer c ON o.id_customer = c.id_customer
JOIN
    ps_address a ON o.id_address_invoice = a.id_address
JOIN
    ps_country_lang cl ON a.id_country = cl.id_country AND o.id_lang = cl.id_lang
LEFT JOIN
    ps_state s ON a.id_state = s.id_state
JOIN
    ps_order_detail od ON o.id_order = od.id_order
LEFT JOIN
    (
        SELECT
            order_reference,
            GROUP_CONCAT(CONCAT(payment_method, ': ', amount) SEPARATOR '; ') AS payment_details
        FROM
            ps_order_payment
        GROUP BY
            order_reference
    ) AS payment ON o.reference = payment.order_reference
WHERE
    o.reference IN ('OrderID1', 'OrderID2', 'OrderID3')
GROUP BY
    o.id_order
ORDER BY
    o.id_order;

 

[Kytor]

In case others stumble upon this post in the future, I have expanded upon what Eutanasio put together.

This SQL Query for Prestashop 1.7.8.5 will export a complete list of all orders with extensive data including Order ID, Reference, Invoice, Customer Phone, Email, Name, Address, ID, Registration Date, Payment, Transaction ID, Order Date, Shipping Number, Delivery Date, and Message.

In other words, this is a comprehensive SQL Query to export all of your orders with significant information needed for record keeping.

 

SELECT
    o.id_order AS order_id,
    o.reference AS order_reference,
    o.invoice_number AS invoice_number,
    c.id_customer AS customer_id,
    c.date_add AS registration_date,
    c.firstname AS customer_firstname,
    c.lastname AS customer_lastname,
    a.phone,
    c.email AS email,
    a.address1,
    a.address2,
    a.postcode,
    a.city,
    cl.name AS country,
    s.name AS state,
    GROUP_CONCAT(CONCAT(od.product_reference, ' (Qty: ', od.product_quantity, ')') SEPARATOR '; ') AS product_reference,
    GROUP_CONCAT(CONCAT(od.product_name, ' (Ref: ', od.product_reference, ', Qty: ', od.product_quantity, ', Price: ', od.product_price, ')') SEPARATOR '; ') AS product_details,
    o.total_shipping_tax_incl AS shipping_cost,
    o.total_wrapping_tax_incl AS wrapping_cost,
    payment.payment_details,
    op.transaction_id AS transaction_id,
    o.total_paid_tax_excl,
    o.total_paid_tax_incl,
    (o.total_paid_tax_incl - o.total_products_wt - o.total_shipping_tax_incl - o.total_wrapping_tax_incl) AS order_discount,
    o.date_add AS order_date,
    o.shipping_number AS shipping_number,
    o.delivery_date AS delivery_date,
    m.message AS message
FROM
    ps_orders o
JOIN
    ps_customer c ON o.id_customer = c.id_customer
JOIN
    ps_address a ON o.id_address_invoice = a.id_address
JOIN
    ps_country_lang cl ON a.id_country = cl.id_country AND o.id_lang = cl.id_lang
LEFT JOIN
    ps_state s ON a.id_state = s.id_state
JOIN
    ps_order_detail od ON o.id_order = od.id_order
LEFT JOIN
    (
        SELECT
            order_reference,
            GROUP_CONCAT(CONCAT(payment_method, ': ', amount) SEPARATOR '; ') AS payment_details
        FROM
            ps_order_payment
        GROUP BY
            order_reference
    ) AS payment ON o.reference = payment.order_reference
LEFT JOIN
    ps_order_payment op ON o.id_order = op.id_order_payment
LEFT JOIN
    ps_message m ON o.id_order = m.id_order
GROUP BY
    o.id_order
ORDER BY
    o.id_order;

 

Edited July 7, 2023 by Kytor (see edit history)