ID
Nom de la requête SQL
Requête SQL
Actions

25	Commandes expédiées sans numéro de suivi (+30 jours)	
SELECT o.id_order, o.reference, o.date_upd AS date_mise_a_jour, osl.name AS statut_commande, CONCAT(c.firstname, ' ', c.lastname) AS nom_client, c.email AS email_client, oc.tracking_number, ac.name AS nom_transporteur FROM ae_orders o INNER JOIN ae_order_state os ON o.current_state = os.id_order_state INNER JOIN ae_order_state_lang osl ON os.id_order_state = osl.id_order_state INNER JOIN ae_customer c ON o.id_customer = c.id_customer LEFT JOIN ae_order_carrier oc ON o.id_order = oc.id_order LEFT JOIN ae_carrier ac ON oc.id_carrier = ac.id_carrier LEFT JOIN ae_lang al ON o.id_lang = al.id_lang LEFT JOIN ae_configuration aconf ON aconf.name = 'PS_LANG_DEFAULT' WHERE o.date_upd >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND os.id_order_state = 4 AND COALESCE(oc.tracking_number, '') = '' AND al.active = 1;	

24	Ebay Pickup	
SELECT o.id_order, o.reference, o.payment, c.firstname AS customer_firstname, c.lastname AS customer_lastname, o.date_add, oc.id_carrier FROM ae_orders o LEFT JOIN ae_order_carrier oc ON o.id_order = oc.id_order LEFT JOIN ae_customer c ON o.id_customer = c.id_customer WHERE o.payment = 'ebay' AND oc.id_carrier = 131;	

23	Retrait entrepôt	
SELECT o.id_order, o.reference, os.name AS order_status FROM ae_orders o LEFT JOIN ae_order_carrier oc ON o.id_order = oc.id_order LEFT JOIN ae_order_state_lang os ON o.current_state = os.id_order_state WHERE oc.id_carrier = 132 AND os.id_lang = 1;	

22	Donnée liées aux alerte mail stock	
SELECT m.id_product, m.id_product_attribute, m.customer_email, p.name AS product_name FROM ae_mailalert_customer_oos m JOIN ae_product_lang p ON m.id_product = p.id_product WHERE p.id_lang = 2;	

21	Clients récemment modifiés	
SELECT id_customer AS customer_id, firstname AS first_name, lastname AS last_name, email, date_add AS date_created, date_upd AS last_update FROM ae_customer WHERE date_upd > (NOW() - INTERVAL 2 DAY)	

20	Groupe de clients modifiés	
SELECT c.id_customer AS customer_id, c.firstname AS first_name, c.lastname AS last_name, c.email AS email, c.date_upd AS last_update, gl_current.name AS current_group, gl_previous.name AS previous_group FROM ae_customer c JOIN ae_group_lang gl_current ON c.id_default_group = gl_current.id_group AND gl_current.id_lang = 1 JOIN ( SELECT id_customer, MAX(id_customer) AS max_id_customer FROM ae_customer GROUP BY id_customer ) c_previous_max ON c.id_customer = c_previous_max.id_customer JOIN ae_group_lang gl_previous ON c_previous_max.max_id_customer = gl_previous.id_group AND gl_previous.id_lang = 1 WHERE c.date_upd <> c.date_add AND gl_current.name <> gl_previous.name;	

19	Test panier abandonnés web	
SELECT c.id_customer AS 'Numéro du client', ca.id_cart AS 'ID Panier', CONCAT(c.firstname, ' ', c.lastname) AS 'Nom du Client', c.email AS 'Email client', CONCAT(c.firstname, ' ', c.lastname) AS 'Nom complet du client', a.phone AS 'Téléphone', COUNT(cp.quantity) AS 'Quantité totale de produit dans le panier', SUM(cp.quantity * p.price) AS 'Montant total du panier' FROM ae_cart ca LEFT JOIN ae_customer c ON (ca.id_customer = c.id_customer) LEFT JOIN ae_address a ON (ca.id_address_delivery = a.id_address) LEFT JOIN ae_cart_product cp ON (ca.id_cart = cp.id_cart) LEFT JOIN ae_product p ON (cp.id_product = p.id_product) WHERE ca.id_cart NOT IN (SELECT id_cart FROM ae_orders WHERE id_cart IS NOT NULL) AND ca.date_upd >= DATE_SUB(NOW(), INTERVAL 48 HOUR) AND ca.delivery_option != '' AND ca.id_customer != 0 AND ca.id_guest = 0 GROUP BY ca.id_cart;	

18	Fonction récupération des paniers abandonnés (24h) fonctionnelle	
SELECT c.id_customer AS 'Numéro du client', ca.id_cart AS 'ID Panier', CONCAT(c.firstname, ' ', c.lastname) AS 'Nom du Client', SUM(cp.quantity * p.price) AS 'Total Commande Base', -- Prix de base sans taxes ni réductions COUNT(cp.id_product) AS 'Nombre de Produits', ca.date_upd as 'dateupdate', c.email as 'email', -- Ajouté cette ligne pour avoir l'email du client a.phone as 'telephone' -- Ajouté cette ligne pour avoir le numéro de téléphone du client FROM ae_cart ca JOIN ae_customer c ON (ca.id_customer = c.id_customer) LEFT JOIN ae_orders o ON (o.id_cart = ca.id_cart) JOIN ae_address a ON (c.id_customer = a.id_customer AND a.active = 1) JOIN ae_cart_product cp ON (cp.id_cart = ca.id_cart) JOIN ae_product p ON (p.id_product = cp.id_product) WHERE (TIMESTAMPDIFF(HOUR, ca.date_upd, NOW()) <= 24) AND -- Dernières 24 heures. o.id_order IS NULL -- Aucune commande liée à ce panier. GROUP BY ca.id_cart, 'dateupdate' HAVING `Total Commande Base` > 300; -- Paniers supérieurs à 300€ avec un total basé sur le prix initial des produits.	

16	test montant total panier	
SELECT c.id_customer, c.email, CONCAT(c.firstname, ' ', c.lastname) AS fullname, a.phone_mobile, a.phone, cart.date_upd, cart.id_cart AS cart_reference, COUNT(cp.quantity) AS total_units, SUM(cp.quantity * p.price) AS total_amount FROM ae_cart cart LEFT JOIN ae_customer c ON (cart.id_customer = c.id_customer) LEFT JOIN ae_address a ON (cart.id_address_delivery = a.id_address) LEFT JOIN ae_cart_product cp ON (cart.id_cart = cp.id_cart) LEFT JOIN ae_product p ON (cp.id_product = p.id_product) WHERE cart.id_cart NOT IN (SELECT id_cart FROM ae_orders WHERE id_cart IS NOT NULL) AND cart.date_upd >= DATE_SUB(NOW(), INTERVAL 96 HOUR) AND cart.delivery_option != '' AND cart.id_customer != 0 AND cart.id_guest = 0 GROUP BY cart.id_cart;	

15	Paniers non valides test	
SELECT c.id_customer, c.email, CONCAT(c.firstname, ' ', c.lastname) AS fullname, a.phone_mobile, a.phone, cart.date_upd, cart.id_cart AS cart_reference, COUNT(cp.quantity) AS total_units, SUM(cp.quantity * p.price) AS total_amount FROM ae_cart cart LEFT JOIN ae_customer c ON (cart.id_customer = c.id_customer) LEFT JOIN ae_address a ON (cart.id_address_delivery = a.id_address) LEFT JOIN ae_cart_product cp ON (cart.id_cart = cp.id_cart) LEFT JOIN ae_product p ON (cp.id_product = p.id_product) WHERE cart.id_cart NOT IN (SELECT id_cart FROM ae_orders WHERE id_cart IS NOT NULL) AND cart.date_upd >= DATE_SUB(NOW(), INTERVAL 48 HOUR) AND cart.delivery_option != '' AND cart.id_customer != 0 AND cart.id_guest = 0 AND cart.total_products_wt >= 300 AND cart.total_quantity >= 1 GROUP BY cart.id_cart ORDER BY c.id_customer ASC;


ID
Nom de la requête SQL
Requête SQL
Actions

14	référence fournisseur	
SELECT `product_supplier_reference` FROM `ae_product_supplier` WHERE `id_product`=5873 AND `id_product_attribute` =0 AND `id_supplier` =1	

13	Commandes du mois	
SELECT CONCAT(LEFT(cu.`firstname`, 1), '. ', cu.`lastname`) AS `customer`, o.id_order, o.reference, o.total_paid_tax_incl, os.paid, osl.name AS osname, o.id_currency, cur.iso_code, o.current_state, o.id_customer, cu.`id_customer` IS NULL as `deleted_customer`, os.color, o.payment, s.name AS shop_name, o.date_add, cu.company, cl.name AS country_name, o.invoice_number, o.delivery_number, IF ((SELECT so.id_order FROM ae_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) > 0, 0, 1) AS new FROM ae_orders o LEFT JOIN ae_customer cu ON o.id_customer = cu.id_customer LEFT JOIN ae_currency cur ON o.id_currency = cur.id_currency INNER JOIN ae_address a ON o.id_address_delivery = a.id_address LEFT JOIN ae_order_state os ON o.current_state = os.id_order_state LEFT JOIN ae_shop s ON o.id_shop = s.id_shop INNER JOIN ae_country c ON a.id_country = c.id_country INNER JOIN ae_country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = 1 LEFT JOIN ae_order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = 1 WHERE (o.`id_shop` IN ('1')) AND (o.`date_add` >= '2023-02-01 0:0:0') AND (o.`date_add` <= '2023-02-28 23:59:59') ORDER BY o.id_order DESC LIMIT 10000	

12	Données sur demande de notifications	
SELECT id_product,id_product_attribute,customer_email FROM ae_mailalert_customer_oos	

11	ExportProduitsVendusMois	
select o.date_add as DateCmnde, p.reference as RefAE, p.supplier_reference as RefFabriq, od.product_name as NomProd, p.ean13 as EAN, m.name as Marque, od.product_quantity as Qtévendue, p.price as PrixHT, aec.name as CatProd, p.id_category_default as IDCat, aes.quantity as QtéDispo, p.active as status, aepl.delivery_in_stock as DélaiSotck, aepl.delivery_out_stock as DélainoStock, o.id_order as numCmde, c.email as EmailClient from ae_orders o left join ae_customer c on c.id_customer=o.id_customer left join ae_order_detail od on od.id_order = o.id_order left join ae_product p on p.id_product=od.product_id left join ae_product_lang aepl on aepl.id_product = p.id_product left join ae_manufacturer m on m.id_manufacturer = p.id_manufacturer left join ae_category_lang aec on aec.id_category = p.id_category_default left join ae_stock_available aes on aes.id_stock_available = p.id_product where year(o.date_add)=2023 and month(o.date_add)=1 AND aec.id_lang=1 AND aepl.id_lang=1 group by o.date_add, p.reference,od.product_name, od.product_quantity	

10	shoppingfee preloading	
select * from ae_shoppingfeed_preloading	

8	CB3	
select o.date_add as date,o.id_order as num_cde,o.id_cart as num_panier,oc.firstname as prenom ,oc.lastname as nom,email as email,oc.company as societe,o.payment, o.total_paid as montant, o.total_paid/3 as mensualite, osl.name as etat, '' as datepaiement1, '' as montant1 , '' as datepaiement2, '' as montant2, '' as datepaiement3, '' as montant3 from ae_orders o left join ae_customer oc on o.id_customer=oc.id_customer left join ae_order_state_lang osl on osl.id_order_state=o.current_state and osl.id_lang=1 left join ae_message m on m.id_order=o.id_order and m.message like '%N° de panier%' where year(o.date_add)=2022 and o.payment like '%plusieurs fois%'	

7	taxes	
select * from ae_tax_rules_group	

6	Cmandes expédiées + 5j après date achat	
Select aeo.id_order, aeo.invoice_date, aeoh.date_add, total_paid_tax_excl from ae_orders aeo INNER JOIN ae_order_detail aeod ON aeo.id_order = aeod.id_order INNER JOIN ae_order_history aeoh ON aeo.id_order = aeoh.id_order where DATEDIFF (aeoh.date_add, aeo.invoice_date) >= 5 AND aeoh.id_order_state = 4 GROUP BY aeo.id_order;	

5	select atoo key	SELECT * FROM ae_atoosync_gescom_key	

4	Commande statut expédié par date	
SELECT h.id_order, o.reference, s.name, h.date_add, c.company, c.firstname, c.lastname, o.total_paid, o.payment, oc.name AS 'Mode livraison' FROM ae_order_history h LEFT JOIN ae_orders o ON o.id_order = h.id_order LEFT JOIN ae_customer c ON c.id_customer = o.id_customer LEFT JOIN ae_order_state_lang s ON s.id_order_state = h.id_order_state LEFT JOIN ae_carrier oc ON oc.id_carrier = o.id_carrier LEFT JOIN ae_order_state_lang osl ON osl.id_order_state = o.current_state AND osl.id_lang = 1 WHERE s.name LIKE '%xpé%' AND s.id_lang = 1 AND h.date_add >= '2023-11-16 08:00'