Outils pour utilisateurs


Qualité de la base de données

Indicateurs du tableau de bord

Fournisseur non renseigné

select name from product_template where (maker_description is null) and sale_ok = 'true' and active = 'true'

Problème de catégorie

select name from product_template where (categ_id is null or categ_id = '1') and sale_ok = 'true' and active = 'true'

Problème de description

select name from product_template where (description_sale is null) and sale_ok = 'true' and active = 'true'

Articles sans position fiscal, taxe à l'achat ou à la vente

select pp.Name_template as "Article" from product_product pp
left join product_template pt on pp.product_tmpl_id = pt.id
where fiscal_classification_id is null and pt.sale_ok is true and pt.active is true
select pp name_template
from product_product pp
left join product_template pt on pp.product_tmpl_id = pt.id
where pp.product_tmpl_id not in(select prod_id from product_taxes_rel) and pt.sale_ok is true and pt.active is true
select pp name_template
from product_product pp
left join product_template pt on pp.product_tmpl_id = pt.id
where pp.product_tmpl_id not in(select prod_id from product_supplier_taxes_rel) and pt.sale_ok is true and pt.active is true

Produits sans code-barre

select pp.Name_template from product_product pp
left join product_template pt on pp.product_tmpl_id = pt.id
where pp.barcode is null and pt.sale_ok is true and pt.active is true

Prix de vente et coût d'achat théorique incohérent avec les coefficients

select name from product_template where (has_theoritical_price_different is true) and sale_ok = 'true' and active = 'true'
select name from product_template where (has_theoritical_cost_different) and sale_ok = 'true' and active = 'true'

Marge non conforme

select pp.Name_template as "Article" from product_product pp
left join product_template pt on pp.product_tmpl_id = pt.id
where coeff1_id <> 1 and pt.sale_ok is true and pt.active is true

Articles non vendu

select name from product_template where (available_in_pos is not true) and sale_ok = 'true' and active = 'true'

Méthode d'achat exotique

select name, purchase_method from product_template where (purchase_method <> 'receive') and sale_ok = 'true' and active = 'true'

Articles vendables mais non achetables

select name from product_template where (purchase_ok is null or purchase_ok is false) and sale_ok = 'true' and active = 'true'

Articles qui ne sont pas des produits

select pt.type, pt.name from product_template pt
where type <> 'product' and sale_ok is true and active is true
group by pt.type, pt.name
order by pt.type, pt.name

Taxes achats et ventes non standards

select at.name as "taxe achat", pt.name as "Article"
from product_product pp
left join product_template pt on pt.id = pp.product_tmpl_id
left join product_supplier_taxes_rel pst on pst.prod_id=pt.id
left join account_tax at on at.id = pst.tax_id
where pt.active is true and pt.purchase_ok is true and (at.id <> 11 and at.id <> 13 and at.id <> 14)
group by at.name, pt.name
order by at.name, pt.name
select at.name as "taxe vente", pt.name as "Article"
from product_product pp
left join product_template pt on pt.id = pp.product_tmpl_id
left join product_taxes_rel pst on pst.prod_id=pt.id
left join account_tax at on at.id = pst.tax_id
where pt.active is true and pt.purchase_ok is true and (at.id <> 3 and at.id <> 4 and at.id <> 1)
group by at.name, pt.name
order by at.name, pt.name

Problème incohérence taxe-compte

select aml.name "Article", at.name "Taxe", aa.name "Compte"
from account_move_line aml
left join account_move_line_account_tax_rel amlatr on amlatr.account_move_line_id = aml.id
left join account_tax at on at.id = amlatr.account_tax_id
left join account_account aa on aml.account_id=aa.id
left join product_product pp on aml.product_id = pp.id
left join product_template pt on pp.product_tmpl_id=pt.id
where pt.active is true and pt.sale_ok is true and (at.id,aa.id) not in ((1, 626), (3, 324), (4, 624), (11, 754), (13, 319), (14,442)) 
group by aml.name, at.name, aa.name
order by at.name, aa.name

Produits par catégories

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 "categorie", pt.name 
from product_template pt
left join product_category cat on cat.id=pt.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 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, pt.name
order 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, pt.name