SQL Reporte de Ventas

[Dalinet]

Amigos he recibido mucha de la comunidad Prestashop y es por ello que he decidido poner a su disposición, este SQL que escribir el cual devuelve un reporte muy completo sobre los pedidos de su tienda, se que les será de mucha utilidad y espero les ayude en sus implementaciones. se los dejo escrito aquí abajo y ademas se los adjunto como un archivo .sql

Un abrazo a todos.

SELECT SQL_CALC_FOUND_ROWS a.`id_order` AS '_ID_', a.`date_add` AS `Date` , a.`reference` AS 'Order ID',
        os.id_order_state AS 'Status ID' , osl.`name` AS 'Status Name' ,a.payment AS 'payment_form' , order_payment.transaction_id AS 'Payment Ref. Number' ,
        products.reference AS 'SKU', product_lang.name AS `Product` , cart_product.quantity AS `Quantity`,  FORMAT(products.price, 2) AS `Unit Price`,
        FORMAT(a.`total_shipping_tax_excl`,2) AS `Shipping`,
        FORMAT(a.`total_discounts_tax_excl`,2) AS `Discount`,
        FORMAT(a.`total_paid_tax_excl`,2) AS `Total`,
        CONVERT(address_delivery.alias USING utf8) AS `Ship-to Name`,
        c.email AS `Email`,
        DATE_FORMAT(c.birthday, '%d-%m-%Y') AS `Date of Birth` ,
        address_delivery.phone AS `Telephone`,
        CONVERT(address_delivery.address1 USING utf8) AS `Ship-to Street`,
        address_delivery.num_ext AS `Ship-to Ext. Number`,
        address_delivery.num_int AS `Ship-to Int. Number`,
        CONVERT(address_delivery.address2 USING utf8) AS `Ship-to Borough`,
        CONVERT(address_delivery.city USING utf8) AS `Ship-to City`,
        delivery_state.ISO_CODE AS `Ship-to State`,
        address_delivery.postcode AS `Ship-to Zip Code`,
         a.shipping_number AS `Ship-to Tracking Number`,
        CONCAT(CONVERT(adderess_invoice.firstname USING utf8), ' ', CONVERT(adderess_invoice.lastname USING utf8)) AS `Name on Invoice`,
        adderess_invoice.vat_number AS `Tax ID`,
        CONVERT(adderess_invoice.address1 USING utf8) AS `Invoice Street`,
        adderess_invoice.num_ext AS `Invoice Ext. Number`,
        adderess_invoice.num_int AS `Invoice Int. Number`,
        CONVERT(adderess_invoice.address2 USING utf8) AS `Invoice Borough`,
        adderess_invoice.city AS `Invoice City`,
        invoice_state.ISO_CODE AS `Invoice State`,
        adderess_invoice.postcode AS `Invoice Zip Code`,
        '' AS `Source`,
        messages.message AS `Observ.`,
        adderess_invoice.cfdi AS `CFDI Use`

        FROM `ps_orders` a
        LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
        INNER JOIN `ps_address` address_delivery ON address_delivery.id_address = a.id_address_delivery
        INNER JOIN `ps_address` adderess_invoice ON adderess_invoice.id_address = a.id_address_invoice
        INNER JOIN `ps_country` country ON address_delivery.id_country = country.id_country
        INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1)
        LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
        LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1)
        LEFT JOIN `ps_order_carrier` order_carrier ON a.`id_order` = order_carrier.`id_order`
        LEFT JOIN `ps_order_payment` order_payment ON order_carrier.id_order_invoice = order_payment.id_order_payment
         LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop`
        LEFT JOIN `ps_cart_product` cart_product ON a.id_cart = cart_product.id_cart
        LEFT JOIN `ps_product` products ON products.id_product = cart_product.id_product
        LEFT JOIN `ps_product_lang` product_lang ON product_lang.id_product = products.id_product AND product_lang.id_lang = 1
        LEFT JOIN `ps_state` delivery_state ON delivery_state.id_state = address_delivery.id_state
        LEFT JOIN `ps_state` invoice_state ON invoice_state.id_state = adderess_invoice.id_state
        LEFT JOIN `ps_message` messages ON messages.id_cart = a.id_cart

        WHERE 1  AND a.id_shop IN (1)
         ORDER BY a.id_order DESC

 

sql-orders.sql

[Dalinet]

Se me paso mencionarles que tiene las direcciones tienen campos adicionales que agregue en mi implementación, solo quitemos para que funcione correctamente