====== Inventaire et stock ======

===== Les écarts d'inventaire =====

{{:thematiques:informatique:capture_du_2018-12-28_08-13-28.png?400|}}

<file>
SELECT sum("public"."stock_inventory_line"."product_qty") AS "sum", date_trunc('month', CAST("public"."stock_inventory_line"."create_date" AS timestamp)) AS "create_date", "public"."stock_inventory_line"."product_name" AS "product_name"
FROM "public"."stock_inventory_line"
GROUP BY date_trunc('month', CAST("public"."stock_inventory_line"."create_date" AS timestamp)), "public"."stock_inventory_line"."product_name"
ORDER BY date_trunc('month', CAST("public"."stock_inventory_line"."create_date" AS timestamp)) ASC, "public"."stock_inventory_line"."product_name" ASC
</file>

===== Ecarts d'inventaire par catégorie (étendue) et par mois =====

{{:thematiques:informatique:capture_du_2018-12-28_08-25-08.png?400|}}

<file>
select to_char(date_trunc('month', sil.write_date),'YYYY-MM'), 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) as sum
from stock_inventory_line sil
left join product_product prod on prod.id=sil.product_id
left join product_template temp on temp.id=prod.product_tmpl_id
left join product_category cat on cat.id=temp.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
group by to_char(date_trunc('month', sil.write_date),'YYYY-MM'), 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 to_char(date_trunc('month', sil.write_date),'YYYY-MM'), 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;
</file>

===== Ecarts d'inventaire par catégorie étendue valorisés =====

{{:thematiques:informatique:capture_du_2018-12-28_08-27-03.png?400|}}

<file>
select to_char(date_trunc('month', sil.write_date),'YYYY-MM') as date, 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) as valo
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
group by to_char(date_trunc('month', sil.write_date),'YYYY-MM'), 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 to_char(date_trunc('month', sil.write_date),'YYYY-MM'), 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;
</file>

===== Tous les produits en vente =====

{{:thematiques:informatique:capture_du_2018-12-28_08-28-28.png?400|}}

<file>
select prod.product_tmpl_id, prod.name_template, prod.active, rp.name, prod.barcode
from product_product prod
left join product_template pt on prod.product_tmpl_id = pt.id
left join product_supplierinfo psi on pt.id = psi.product_tmpl_id
left join res_partner rp on rp.id = psi.name
where prod.active=true and pt.sale_ok=true and (pt.type='product'OR pt.type='consu')
</file>