Principaux indicateurs

Requête SQL :

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

Requête SQL :

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

Requête SQL :

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"

Requête SQL :

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

Requête :

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

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