Outils pour utilisateurs
Table des matières
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}}
