Achats : tableau de bord et questions complémentaires

Achats : tableau de bord

Question : Répartition des achats par fournisseur

Requête :

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

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

}}

Requête :

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

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

Requête :

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

Tous les achats par fournisseur et par mois

Requête :

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

Achats : questions complémentaires

Identifiant d'un produit

Requête :

select product_product.product_tmpl_id, product_product.name_template  from product_product where product_product.name_template like {{Produit}}

historique stock produire par jour

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