Consulta SQL para pedidos + dirección de entrega - Prestashop 1.7

[CarlosAlvarez]

Hola! A ver si alguien puede ayudarme, necesito hacer una colsulta SQL de los pedidos realizados en mi tienda para ingresarlos en un documento de Excel y pasarlo al departamento de envíos y procese los envíos. El problema que tengo es que cuando tengo una venta que va a ser un regalo y va a ser enviado a otra dirección, hago la consulta y me muestra el nombre del cliente con su dirección (dirección de facturación) y luego repite la información del pedido con el nombre de la persona que recibe y su dirección (dirección de envío), por lo que tengo que irme al pedido del cliente y ver cuál es la dirección correcta para poder copiarla en el documento de Excel.

La consulta que estoy realizando es la siguiente:

SELECT o.`id_order` AS `Orden #`,  o.`reference` AS `Referencia`, os.`name` AS `Estado`, o. `gift` AS `Empaque regalo`, o. `gift_message` AS `Mensaje Regalo`, ca. `name` AS `Transportista`,
ad. `firstname` AS `Nombre`,
ad. `lastname` AS `Apellido`,
     o.`payment` AS `Pago`,
   od.`product_name` AS `Producto`,
   od.`product_quantity`AS `Cantidad`,
    o.`date_add` AS `Fecha`,
ad. `dni` AS `DNI`,
ad. `address1` AS `Direccion`,
ad. `address2` AS `Dir2`,
ad. `postcode` AS `CP`,   
ad. `city` AS `Ciudad`,
ad. `phone` AS `Tel`,
m. `message` AS `Mensaje de orden`,
o. `id_address_delivery` AS `dirorden`,
c. `email` AS `Mail`
FROM `ps_orders` o
LEFT JOIN `ps_order_detail` od ON (od.id_order = o.id_order)
LEFT JOIN `ps_order_carrier` oc ON (oc.id_order = o.id_order)
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN `ps_address` ad ON ( o. `id_customer` = ad. `id_customer`)
LEFT JOIN `ps_message` m ON (o.id_order = m.id_order)
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state )
WHERE  o.`date_add` >= '2020-05-21 0:0:0' AND o.`date_add` <= '2020-05-24 14:59:59'  and os.id_lang =1

Me muestra todo lo que quiero, pero me gustaría saber si se puede y cómo, mostrar sólo los datos y la dirección de envío de cada pedido.

Muchas gracias de antemano por su ayuda.

[Dalinet]

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`,
        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`,
        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.`,

        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

Tal vez esto te pueda servir

[CarlosAlvarez]

hace 1 hora, Dalinet dijo:

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`,
        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`,
        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.`,

        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

Tal vez esto te pueda servir

Hola! Muchas gracias por tu respuesta.

Me indica el siguiente mensaje "La consulta SQL no tiene resultados".

Mi consulta SQL inicial me funciona, lo único que necesito es que en vez de mostrarme la dirección de facturación, me muestre la de envío.

Saludos.