Ajouter le Customer ID à mon export des commandes ?(requête SQL)

[Pedr0]

Bonjour à tous,

J'aimerai pouvoir ajouter l'ID du client à mon export de commandes via le backoffice en modifiant la requête SQL. 

Voici ma requête actuelle (qui est celle de base de Prestashop sur le backend des commandes).

SELECT SQL_CALC_FOUND_ROWS a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add` , a.id_currency, a.id_order AS id_pdf, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, osl.`name` AS `osname`, os.`color`, IF((SELECT so.id_order FROM `arva_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, country_lang.name as cname, IF(a.valid, 1, 0) badge_success, shop.name as shop_name FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery INNER JOIN `ps_country` country ON address.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_shop` shop ON a.`id_shop` = shop.`id_shop` WHERE 1 AND a.`date_add` >= '2022-07-01 0:0:0' AND a.`date_add` <= '2023-06-30 23:59:59' AND a.id_shop IN (1, 3, 4) ORDER BY a.id_order DESC LIMIT 0, 1000

Est-ce que cela est possible, et si oui, quel paramètre ajouter pour avoir l'export que je souhaite?

je suis sur 1.7.4.2

Merci de votre aide.

 

 

[Shonen]

A tester car dans ta requête il y'a des éléments non natifs à Prestashop, donc c'est intestable (table arva_orders ?)

SELECT SQL_CALC_FOUND_ROWS a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add` , a.id_currency, a.id_order AS id_pdf, c.`id_customer` as `id_client`, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, osl.`name` AS `osname`, os.`color`, IF((SELECT so.id_order FROM `arva_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, country_lang.name as cname, IF(a.valid, 1, 0) badge_success, shop.name as shop_name FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery INNER JOIN `ps_country` country ON address.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_shop` shop ON a.`id_shop` = shop.`id_shop` WHERE 1 AND a.`date_add` >= '2022-07-01 0:0:0' AND a.`date_add` <= '2023-06-30 23:59:59' AND a.id_shop IN (1, 3, 4) ORDER BY a.id_order DESC LIMIT 0, 1000

 

Edited August 25, 2022 by Shonen (see edit history)

[Pedr0]

Merci Shonen, j'ai un peu adapté et j'ai pu arriver à un résultat satisfaisant.

En effet j'ai oublié de changé le nom des tables mais arva_ doit se lire ps_

SELECT SQL_CALC_FOUND_ROWS a.`id_order`, `reference`, a.`id_customer`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add` , a.id_currency, a.id_order AS id_pdf, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, osl.`name` AS `osname`, os.`color`, IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, country_lang.name as cname, IF(a.valid, 1, 0) badge_success, shop.name as shop_name FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery INNER JOIN `ps_country` country ON address.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_shop` shop ON a.`id_shop` = shop.`id_shop` WHERE 1 AND a.`date_add` >= '2021-07-01 0:0:0' AND a.`date_add` <= '2022-06-30 23:59:59' AND a.id_shop IN (1, 6, 7, 8, 9, 10, 11) ORDER BY a.id_order DESC LIMIT 0, 1000;