Jump to content

Requête SQL pour sortir les factures avec le nom du client et le montant de la TVA


omyha

Recommended Posts

Bonjour à tous !

 

Alors voilà, j'ai concocté la requête SQL ci-dessous qui me permet de sortir les factures avec :

  • id_order_invoice
  • id_order
  • total_products
  • total_paid_tax_excl
  • total_shipping_tax_excl
  • total_paid_tax_incl
  • date_add
select `id_order_invoice`, `id_order`, `total_products`, `total_paid_tax_excl`, `total_shipping_tax_excl`, `total_paid_tax_incl`, `date_add` from ps_order_invoice order by  `id_order_invoice` DESC

Je souhaite ajouter dans ce tableau :

  • le prénom et le nom du client correspondant à la facture
  • le montant de la TVA
  • et en bonus : le taux de TVA appliqué

 

 

Mes connaissances en SQL étant limitées, je n'ai pas réussi à joindre les autres tables dans lesquelles aller chercher ces données :

  • avec id_order, on récupère id_customer dans ps_order
  • et avec id_customer, on récupère firstname et lastname dans ps_customer

 

Mais voilà, je ne sais pas l'écrire en SQL...

Pouvez-vous m'aider ?

 

Merci à tous :)

Alexis

 

Link to comment
Share on other sites

Essayez cette requête, mais comme vous pouvez le voir, le résultat n'est pas tout à fait raison. En particulier, le taux d'imposition est faux parce que la requête est sur ​​les factures et taux d'imposition dépend de chaque poste de commande unique.

 

Try this query, but as you can see, the result is not quite right. Especially the tax rate is wrong because the query is about invoices and tax rate depends on every single order item.

select oi.`id_order_invoice`, oi.`id_order`, oi.`total_products`, oi.`total_paid_tax_excl`, oi.`total_shipping_tax_excl`, oi.`total_paid_tax_incl`, 
(oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as total_vat, 
round(((oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`)/oi.`total_paid_tax_excl`)*100, 2) as tax_rate, 
oi.`date_add`, c.`firstname`, c.`lastname` 
from ps_order_invoice oi 
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
  • Like 1
Link to comment
Share on other sites

Merci Tuk66 !!!

 

Tu as raison : il faudrait ressortir le montant et le taux de la TVA pour chaque produit de la facture.

Je ferai ça prochainement.

 

En attendant, voici ma requête finale qui pourra servir à d'autres j'espère :)

 

Exporter les factures :
N_facture / N_commande / Total_produits_HT / TVA / Total_produits_TTC / Frais_d_expedition / Total_paye / Date_facture / Prenom / Nom
 
 
 
select oi.`id_order_invoice` as N_facture, oi.`id_order` as N_commande, 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, 
oi.`date_add` as Date_facture, c.`firstname` as Prenom, c.`lastname` as Nom
from ps_order_invoice oi 
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
Link to comment
Share on other sites

  • 1 year later...

D'abord merci pour cette requête bien pratique qui me sert pour ma compta.

Par contre pour être complet il y manque les annulations et remboursements.

 

Quelqu'un a t-il une suggestion pour ajouter les avoirs (order_slip)?

 

Merci

Link to comment
Share on other sites

  • 2 weeks later...

est ce que en peut ajouter la colonne statut dans cette requête


 select oi.`id_order_invoice` as N_facture, oi.`id_order` as N_commande, 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, 

oi.`date_add` as Date_facture, c.`firstname` as Prenom, c.`lastname` as Nom
from ps_order_invoice oi 
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 d'avance ;)

 

Link to comment
Share on other sites

  • 1 year later...

 

Essayez cette requête, mais comme vous pouvez le voir, le résultat n'est pas tout à fait raison. En particulier, le taux d'imposition est faux parce que la requête est sur ​​les factures et taux d'imposition dépend de chaque poste de commande unique.

 

Try this query, but as you can see, the result is not quite right. Especially the tax rate is wrong because the query is about invoices and tax rate depends on every single order item.

select oi.`id_order_invoice`, oi.`id_order`, oi.`total_products`, oi.`total_paid_tax_excl`, oi.`total_shipping_tax_excl`, oi.`total_paid_tax_incl`, 
(oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as total_vat, 
round(((oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`)/oi.`total_paid_tax_excl`)*100, 2) as tax_rate, 
oi.`date_add`, c.`firstname`, c.`lastname` 
from ps_order_invoice oi 
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

Hi Vladimir,

is it possible to add tax code ( dni ) and VAT number from the client details?

Edited by affaridanoi (see edit history)
Link to comment
Share on other sites

Try this query

select oi.`id_order_invoice`, oi.`id_order`, oi.`total_products`, oi.`total_paid_tax_excl`, oi.`total_shipping_tax_excl`, oi.`total_paid_tax_incl`,
(oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as total_vat,
round(((oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`)/oi.`total_paid_tax_excl`)*100, 2) as tax_rate,
oi.`date_add`, c.`firstname`, c.`lastname`, a.`dni`, a.`vat_number`
from ps_order_invoice oi
left outer join ps_orders o on oi.`id_order` = o.`id_order`
left outer join ps_address a on o.`id_address_invoice` = a.`id_address`
left outer join ps_customer c on o.`id_customer` = c.`id_customer`
order by  oi.`id_order_invoice` DESC
Link to comment
Share on other sites

 

Try this query

select oi.`id_order_invoice`, oi.`id_order`, oi.`total_products`, oi.`total_paid_tax_excl`, oi.`total_shipping_tax_excl`, oi.`total_paid_tax_incl`,
(oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`) as total_vat,
round(((oi.`total_paid_tax_incl` - oi.`total_paid_tax_excl`)/oi.`total_paid_tax_excl`)*100, 2) as tax_rate,
oi.`date_add`, c.`firstname`, c.`lastname`, a.`dni`, a.`vat_number`
from ps_order_invoice oi
left outer join ps_orders o on oi.`id_order` = o.`id_order`
left outer join ps_address a on o.`id_address_invoice` = a.`id_address`
left outer join ps_customer c on o.`id_customer` = c.`id_customer`
order by  oi.`id_order_invoice` DESC

 

That's so AMAZING.

Thanks Vladimir!!!

Link to comment
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...