Jump to content
calindoudou

Export de données par requete SQL / CSV [Resolu]

Recommended Posts

Bonjour,

j'essaie depuis quelques jours de créer un fichier d'export csv
j'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 by calindoudou (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
order
by oi.`id_order_invoice` DESC

Share this post


Link to post
Share on other sites

Bonjour rvcat,

 

Navrée de ne pas avoir répondu avant, je n'avais pas vu :(

Et super merci pour cette réponse :)

 

Cdt

Share this post


Link to post
Share on other sites

 

 

group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14

 

?????

Share this post


Link to post
Share on other sites

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 by Eolia (see edit history)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (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 by petisyl (see edit history)

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More