====== Achats : tableau de bord et questions complémentaires ======

===== Achats : tableau de bord =====

==== Question : Répartition des achats par fournisseur ====

{{:achats:capture_du_2018-12-28_05-55-14.png?400|}}

Requête : 

<file>
SELECT sum("public"."purchase_order"."amount_total") AS "sum", "res_partner__via__partner_id"."name" AS "name"
FROM "public"."purchase_order"
LEFT JOIN "public"."res_partner" "res_partner__via__partner_id" ON "public"."purchase_order"."partner_id" = "res_partner__via__partner_id"."id"
GROUP BY "res_partner__via__partner_id"."name"
ORDER BY "sum" DESC, "res_partner__via__partner_id"."name" ASC
</file>

==== Question : Répartition des achats par fournisseur sur les 30 derniers jours ====

{{:achats:capture_du_2018-12-28_05-57-47.png?400|}}}}

Requête :

<file>
SELECT sum("public"."purchase_order"."amount_total") AS "sum", "res_partner__via__partner_id"."name" AS "name"
FROM "public"."purchase_order"
LEFT JOIN "public"."res_partner" "res_partner__via__partner_id" ON "public"."purchase_order"."partner_id" = "res_partner__via__partner_id"."id"
WHERE CAST("public"."purchase_order"."create_date" AS date) BETWEEN CAST((NOW() + INTERVAL '-30 day') AS date)
   AND CAST((NOW() + INTERVAL '-1 day') AS date)
GROUP BY "res_partner__via__partner_id"."name"
ORDER BY "sum" DESC, "res_partner__via__partner_id"."name" ASC
</file>

==== Achats par fournisseurs cumulés par mois (limité aux plus grosses commandes) ====

{{:achats:capture_du_2018-12-28_06-00-34.png?400|}}

Requête :

<file>
SELECT sum("public"."purchase_order"."amount_total") AS "sum", date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)) AS "create_date", "res_partner__via__partner_id"."name" AS "name"
FROM "public"."purchase_order"
LEFT JOIN "public"."res_partner" "res_partner__via__partner_id" ON "public"."purchase_order"."partner_id" = "res_partner__via__partner_id"."id"
GROUP BY date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)), "res_partner__via__partner_id"."name"
ORDER BY "sum" DESC, date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)) ASC, "res_partner__via__partner_id"."name" ASC
LIMIT 100
</file>

==== Tous les achats par fournisseur et par mois ====

{{:achats:capture_du_2018-12-28_07-00-12.png?400|}}

Requête :

<file>
SELECT sum("public"."purchase_order"."amount_total") AS "sum", date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)) AS "create_date", "res_partner__via__partner_id"."name" AS "name"
FROM "public"."purchase_order"
LEFT JOIN "public"."res_partner" "res_partner__via__partner_id" ON "public"."purchase_order"."partner_id" = "res_partner__via__partner_id"."id"
GROUP BY date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)), "res_partner__via__partner_id"."name"
ORDER BY date_trunc('month', CAST("public"."purchase_order"."create_date" AS timestamp)) ASC, "res_partner__via__partner_id"."name" ASC
</file>

===== Achats : questions complémentaires =====

==== Identifiant d'un produit ====

{{:achats:capture_du_2018-12-28_07-05-20.png?400|}}

Requête :
<file>
select product_product.product_tmpl_id, product_product.name_template  from product_product where product_product.name_template like {{Produit}}
</file>

==== historique stock produire par jour ====

{{:achats:historique_inventaire_par_jour.png?400|}}

<file>
SELECT "public"."product_history"."from_date" AS "date de début", "public"."product_history"."purchase_qty" AS "achat", "public"."product_history"."sale_qty" "vente", "public"."product_history"."loss_qty" AS "Perte", "public"."product_history"."to_date" AS "date de fin"
FROM "public"."product_history"
left join product_template pt on pt.id = product_history.product_tmpl_id
WHERE "public"."product_history"."product_tmpl_id" = {{Numero_produit}} AND "public"."product_history"."history_range" = 'days' AND "public"."product_history"."from_date" >= {{date_debut}} AND "public"."product_history"."to_date" <= {{date_fin}}
</file>