====== Principaux indicateurs ======

===== Volume de vente par mois =====

{{:thematiques:informatique:volume_de_ventes_par_mois.png?400|}}

Requête SQL :

<file>
SELECT sum("public"."report_pos_order"."price_total") AS "sum", date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) AS "date"
FROM "public"."report_pos_order"
GROUP BY date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp))
ORDER BY date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) ASC
</file>

===== Nombre d'acheteurs par mois =====

{{:thematiques:informatique:capture_du_2018-12-27_17-43-17.png?400|}}

Requête SQL :

<file>
SELECT count(distinct "pos_order__via__order_id"."partner_id") AS "count", date_trunc('month', CAST("public"."pos_order_line"."create_date" AS timestamp)) AS "create_date"
FROM "public"."pos_order_line"
LEFT JOIN "public"."pos_order" "pos_order__via__order_id" ON "public"."pos_order_line"."order_id" = "pos_order__via__order_id"."id"
GROUP BY date_trunc('month', CAST("public"."pos_order_line"."create_date" AS timestamp))
ORDER BY date_trunc('month', CAST("public"."pos_order_line"."create_date" AS timestamp)) ASC
</file>

===== Evolution du manier moyen mensuel =====

{{:thematiques:informatique:evolution_du_panier_moyen_mensuel.png?400|}}

Requête SQL :

<file>
SELECT avg("inner_query"."sum"), "inner_query"."date"
FROM
(SELECT sum("public"."report_pos_order"."price_total") AS "sum", "public"."report_pos_order"."partner_id" AS "partner_id", date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) AS "date"
FROM "public"."report_pos_order"
GROUP BY "public"."report_pos_order"."partner_id", date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp))
ORDER BY "public"."report_pos_order"."partner_id" ASC, date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) ASC) AS "inner_query"
GROUP BY "inner_query"."date"
</file>

===== Nouveaux membres par mois =====

{{:thematiques:informatique:capture_du_2018-12-27_17-46-35.png?400|}}

Requête SQL :

<file>
SELECT count(*) AS "count", date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp)) AS "create_date"
FROM "public"."res_partner"
WHERE CAST("public"."res_partner"."create_date" AS date) > CAST(? AS date)
GROUP BY date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp))
ORDER BY date_trunc('month', CAST("public"."res_partner"."create_date" AS timestamp)) ASC
</file>

===== Nombre de références =====

{{:thematiques:informatique:nombre_de_references_2.png?400|}}


===== Ecarts d'inventaire par catégorie =====

{{:thematiques:informatique:ecarts_d_inventaire_par_categorie.png?400|}}

Requête :

<file>
select case when (parent3cat.id=1 or parent3cat.id=2 or parent3cat.id is null) then case when (parent2cat.id=1 or parent2cat.id=2 or parent2cat.id is null) then concat(parentcat.name,' / ',cat.name) else concat(parent2cat.name,' / ',parentcat.name,' / ',cat.name) end else concat(parent3cat.name,' / ',parent2cat.name,' / ',parentcat.name,' / ',cat.name) end as category, sum(sil.product_qty*tmp.base_price)
from stock_inventory_line sil
left join product_product prod on prod.id=sil.product_id
left join product_template tmp on tmp.id=prod.product_tmpl_id
left join product_category cat on cat.id=tmp.categ_id
left join product_category parentcat on parentcat.id=cat.parent_id
left join product_category parent2cat on parent2cat.id=parentcat.parent_id
left join product_category parent3cat on parent3cat.id=parent2cat.parent_id
where sil.write_date >= '2018-08-26'
group by case when (parent3cat.id=1 or parent3cat.id=2 or parent3cat.id is null) then case when (parent2cat.id=1 or parent2cat.id=2 or parent2cat.id is null) then concat(parentcat.name,' / ',cat.name) else concat(parent2cat.name,' / ',parentcat.name,' / ',cat.name) end else concat(parent3cat.name,' / ',parent2cat.name,' / ',parentcat.name,' / ',cat.name) end
order by sum(sil.product_qty*tmp.base_price) DESC
</file>

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

{{:thematiques:informatique:repartition_des_achats_par_fournisseur.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>