Jump to content

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

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

  • 2 months later...

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

  • 4 years later...
  • 6 months later...
  • 7 months later...

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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  

 

Share this post


Link to post
Share on other sites

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

 

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
 Share

×
×
  • Create New...

Important Information

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