calindoudou Posted July 28, 2015 Share Posted July 28, 2015 (edited) Bonjour, j'essaie depuis quelques jours de créer un fichier d'export csvj'ai réussi a récupérer un code qui me convient assez (malgré que je ne comprend pas vraiment comment ca fonctionne) et j'aimerai y ajouté 4 éléments (pays / email / état de la commande / référence pdt commandé) je mets le code que j'ai récupéré select oi.`id_order_invoice` as Facture, oi.`id_order` as Commande, oi.`date_add` as Date_facture, c.`lastname` as Nom, c.`firstname` as Prenom, oi.`total_products` as Total_produits_HT, (oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as TVA, (oi.`total_paid_tax_incl` - oi.`total_shipping_tax_incl`) as Total_produits_TTC, oi.`total_shipping_tax_excl` as Frais_d_expedition, oi.`total_paid_tax_incl` as Total_paye,op.`payment_method`as Methode from ps_order_invoice oi,ps_customer.email, left outer join ps_order_invoice_payment oip on oi.`id_order_invoice` =oip.`id_order_invoice` left outer join ps_order_payment op on oip.`id_order_payment` =op.`id_order_payment` left outer join ps_orders o on oi.`id_order` = o.`id_order` left outer join ps_customer c on o.`id_customer` = c.`id_customer` order by oi.`id_order_invoice` DESC Merci à toutes et tous pour vos réponses Edited August 2, 2015 by calindoudou (see edit history) Link to comment Share on other sites More sharing options...
calindoudou Posted August 2, 2015 Author Share Posted August 2, 2015 Bonjour Voici le code final au cas ou ca interresse quelqu'un select oi.`id_order_invoice` as Facture, oi.`id_order` as Commande, oi.`date_add` as Date_facture, c.`lastname` as Nom, c.`firstname` as Prenom, c.`email` as Email, oi.`total_products` as Total_produits_HT, (oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as TVA, (oi.`total_paid_tax_incl` - oi.`total_shipping_tax_incl`) as Total_produits_TTC, oi.`total_shipping_tax_excl` as Frais_d_expedition, oi.`total_paid_tax_incl` as Total_paye, op.`payment_method`as Methode, addrlang.`name` as Pays, statelang.`name` as Etat_commande, refprod.`product_reference` as Ref_Produit from ps_order_invoice oi left outer join ps_order_invoice_payment oip on oi.`id_order_invoice` =oip.`id_order_invoice` left outer join ps_order_payment op on oip.`id_order_payment` =op.`id_order_payment` left outer join ps_orders o on oi.`id_order` = o.`id_order` left outer join ps_customer c on o.`id_customer` = c.`id_customer` left outer join ps_address addr on addr.`id_address` = o.`id_address_delivery` left outer join ps_country_lang addrlang on addrlang.`id_country` = addr.`id_country` left outer join ps_order_state_lang statelang on statelang.`id_order_state` = o.`current_state` left outer join ps_order_detail refprod on refprod.`id_order` = o.`id_order` order by oi.`id_order_invoice` DESC Link to comment Share on other sites More sharing options...
rvcat Posted August 6, 2015 Share Posted August 6, 2015 salut, ta requete est bien sauf que si ta facture contient plusieurs produits alors tu auras autant de lignes que tu as de produits par facture. pour eviter ça, utilise la fonction mysql group_concat (cf google pour les détails de la fonction) si tu veux avoir 1 ligne par facture select oi.`id_order_invoice` as Facture, oi.`id_order` as Commande, oi.`date_add` as Date_facture, c.`lastname` as Nom, c.`firstname` as Prenom, c.`email` as Email, oi.`total_products` as Total_produits_HT, (oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as TVA, (oi.`total_paid_tax_incl` - oi.`total_shipping_tax_incl`) as Total_produits_TTC, oi.`total_shipping_tax_excl` as Frais_d_expedition, oi.`total_paid_tax_incl` as Total_paye, op.`payment_method`as Methode, addrlang.`name` as Pays, statelang.`name` as Etat_commande, group_concat(refprod.`product_reference`) as Ref_Produit from ps_order_invoice oileft outer join ps_order_invoice_payment oip on oi.`id_order_invoice` =oip.`id_order_invoice`left outer join ps_order_payment op on oip.`id_order_payment` =op.`id_order_payment`left outer join ps_orders o on oi.`id_order` = o.`id_order`left outer join ps_customer c on o.`id_customer` = c.`id_customer`left outer join ps_address addr on addr.`id_address` = o.`id_address_delivery`left outer join ps_country_lang addrlang on addrlang.`id_country` = addr.`id_country`left outer join ps_order_state_lang statelang on statelang.`id_order_state` = o.`current_state`left outer join ps_order_detail refprod on refprod.`id_order` = o.`id_order` group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14order by oi.`id_order_invoice` DESC Link to comment Share on other sites More sharing options...
calindoudou Posted August 11, 2015 Author Share Posted August 11, 2015 Bonjour rvcat, Navrée de ne pas avoir répondu avant, je n'avais pas vu Et super merci pour cette réponse Cdt Link to comment Share on other sites More sharing options...
Eolia Posted August 11, 2015 Share Posted August 11, 2015 group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 ????? Link to comment Share on other sites More sharing options...
Eolia Posted August 11, 2015 Share Posted August 11, 2015 (edited) Cette commande est plus rapide (les left join sont inutiles ici) et évite la duplication des références produits au cas où un même produit est en plusieurs quantités dans la même commande: select oi.`id_order_invoice` as Facture, oi.`id_order` as Commande, oi.`date_add` as Date_facture, c.`lastname` as Nom, c.`firstname` as Prenom, c.`email` as Email, oi.`total_products` as Total_produits_HT, (oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as Taxes, (oi.`total_paid_tax_incl` - oi.`total_shipping_tax_incl`) as Total_produits_TTC, oi.`total_shipping_tax_excl` as Frais_d_expedition, oi.`total_paid_tax_incl` as Total_paye, op.`payment_method`as Methode, addrlang.`name` as Pays, statelang.`name` as Etat_commande, group_concat(DISTINCT refprod.`product_reference`) as Ref_Produit from ps_order_invoice oi inner join ps_order_invoice_payment oip on oi.`id_order_invoice` =oip.`id_order_invoice` inner join ps_order_payment op on oip.`id_order_payment` =op.`id_order_payment` inner join ps_orders o on oi.`id_order` = o.`id_order` inner join ps_customer c on o.`id_customer` = c.`id_customer` inner join ps_address addr on addr.`id_address` = o.`id_address_delivery` inner join ps_country_lang addrlang on addrlang.`id_country` = addr.`id_country` inner join ps_order_state_lang statelang on statelang.`id_order_state` = o.`current_state` inner join ps_order_detail refprod on refprod.`id_order` = o.`id_order` group by oi.`id_order_invoice` order by oi.`id_order_invoice` DESC Edited August 11, 2015 by Eolia (see edit history) Link to comment Share on other sites More sharing options...
TiPimousse Posted October 27, 2015 Share Posted October 27, 2015 Bonjour à tous, c'est vraiment super cette requête et vraiment merci de votre investissement. Elle va vraiment me servir ! le comptable vient de me demander exactement ça... En revanche, vous croyais qu'il soit possible de rajouter N colonnes : ventilation par taux de TVA. Dans mon cas, j'ai de la TVA à 20% et de la TVA à 5.5% Et là je vous épouse ! Encore merci Matthieu Link to comment Share on other sites More sharing options...
petisyl Posted June 1, 2020 Share Posted June 1, 2020 (edited) Bonjour, Ce topic m'a grandement aidé pour sortir des données compta, juste une petite chose que je n'arrive pas à trouver. comment intégrer l'éco-taxe à la requête? Merci d'avance Edited June 1, 2020 by petisyl (see edit history) Link to comment Share on other sites More sharing options...
Nice-side Posted December 14, 2020 Share Posted December 14, 2020 Bonjour, je cherche la commande SQL qui me permettrai d'exporter mes commandes contenant le nom des produits et non pas la référence. merci Link to comment Share on other sites More sharing options...
kikilamule Posted August 12, 2021 Share Posted August 12, 2021 Bonjour et désolé de déterrer ce post, mais les infos sont toujours d'actu. La requete d'eolia est installé chez moi et me rends bien service chaque mois, mais j'aurai besoin d'un parametre en plus qui consiste à choisir les statuts de commandes à exporter. Pour info, certaines commandes annulées n'ont pas de facture car pas de rentré financière liée. Donc pas besoin de générer d'avoir. mais si j'exporte avec cette commande, il me faut faire le tri après sinon ma comptable me ruine la journée avec des correctifs et du tri à faire. Comment faire pour choisir les statuts de commande à inclure ou à exclure qui serait encore mieux. Par avance merci Link to comment Share on other sites More sharing options...
Eolia Posted August 12, 2021 Share Posted August 12, 2021 ajoutez WHERE o.valid = 1 et vous n'aurez que les commandes payées^^ select oi.`id_order_invoice` as Facture, oi.`id_order` as Commande, oi.`date_add` as Date_facture, c.`lastname` as Nom, c.`firstname` as Prenom, c.`email` as Email, oi.`total_products` as Total_produits_HT, (oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as Taxes, (oi.`total_paid_tax_incl` - oi.`total_shipping_tax_incl`) as Total_produits_TTC, oi.`total_shipping_tax_excl` as Frais_d_expedition, oi.`total_paid_tax_incl` as Total_paye, op.`payment_method`as Methode, addrlang.`name` as Pays, statelang.`name` as Etat_commande, group_concat(DISTINCT refprod.`product_reference`) as Ref_Produit from ps_order_invoice oi inner join ps_order_invoice_payment oip on oi.`id_order_invoice` =oip.`id_order_invoice` inner join ps_order_payment op on oip.`id_order_payment` =op.`id_order_payment` inner join ps_orders o on oi.`id_order` = o.`id_order` inner join ps_customer c on o.`id_customer` = c.`id_customer` inner join ps_address addr on addr.`id_address` = o.`id_address_delivery` inner join ps_country_lang addrlang on addrlang.`id_country` = addr.`id_country` inner join ps_order_state_lang statelang on statelang.`id_order_state` = o.`current_state` inner join ps_order_detail refprod on refprod.`id_order` = o.`id_order` where o.valid = 1 group by oi.`id_order_invoice` order by oi.`id_order_invoice` DESC Link to comment Share on other sites More sharing options...
kikilamule Posted August 12, 2021 Share Posted August 12, 2021 Super pour la réponse, il ne me manque plus que le filtrage par date, j'ai tenté de raccrocher les wagons avec Citation where o.valid = 1 AND a.`date_add` >= '2021-07-01 0:0:0' AND a.`date_add` <= '2021-07-31 23:59:59' mais ca veux pas Link to comment Share on other sites More sharing options...
Eolia Posted August 12, 2021 Share Posted August 12, 2021 Ben.... - a. correspond à l'alias de quelle table ??? (utilisez soit oi, soit op, soit o ) - ' 0:0:0' n'existe pas en timestamp SQL, c'est 00:00:00 Link to comment Share on other sites More sharing options...
kikilamule Posted August 12, 2021 Share Posted August 12, 2021 En fait, ma requete ressemble plus à ca Citation 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 `ehf991orders` 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, ca.name as carrier_name FROM `ehf991orders` a LEFT JOIN `ehf991customer` c ON (c.`id_customer` = a.`id_customer`) LEFT JOIN `ehf991address` address ON address.id_address = a.id_address_delivery LEFT JOIN `ehf991country` country ON address.id_country = country.id_country LEFT JOIN `ehf991country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1) LEFT JOIN `ehf991order_state` os ON (os.`id_order_state` = a.`current_state`) LEFT JOIN `ehf991order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1) LEFT JOIN `ehf991carrier` ca ON (ca.`id_carrier` = a.`id_carrier`) WHERE 1 AND a.`date_add` >= '2021-07-01 0:0:0' AND a.`date_add` <= '2021-07-31 23:59:59' ORDER BY a.`id_order` DESC Link to comment Share on other sites More sharing options...
doekia Posted August 13, 2021 Share Posted August 13, 2021 SQL_CALC_FOUND_ROWS ne ramène pas de résultat, juste le nombre d'enregistrements trouvés. Et il y a toujours '2021-07-01 0:0:0' au lieu de '2021-07-01 00:00:00' Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now