====== Qualité de la base de données ======

===== Indicateurs du tableau de bord =====

==== Fournisseur non renseigné ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-09-18.png?400|}}

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

==== Problème de catégorie ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-12-59.png?400|}}

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

==== Problème de description ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-16-03.png?400|}}

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

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

{{:thematiques:informatique:capture_du_2019-02-03_14-07-45.png?400|}}

<file>
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
</file>

<file>
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
</file>

<file>
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
</file>

==== Produits sans code-barre ====

{{:thematiques:informatique:capture_du_2019-02-03_14-06-12.png?400|}}

<file>
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
</file>

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

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-19-37.png?400|}}

<file>
select name from product_template where (has_theoritical_price_different is true) and sale_ok = 'true' and active = 'true'
</file>

<file>
select name from product_template where (has_theoritical_cost_different) and sale_ok = 'true' and active = 'true'
</file>

==== Marge non conforme ====

{{:thematiques:informatique:capture_du_2019-02-03_14-02-47.png?200|}}

<file>
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
</file>

==== Articles non vendu ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-23-05.png?400|}}

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

==== Méthode d'achat exotique ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-25-20.png?400|}}

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

==== Articles vendables mais non achetables ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-26-36.png?400|}}

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

==== Articles qui ne sont pas des produits ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-28-01.png?400|}}

<file>
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
</file>

==== Taxes achats et ventes non standards ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-30-17.png?400|}}

<file>
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
</file>

<file>
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
</file>

==== Problème incohérence taxe-compte ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-07_22-51-32.png?400|}}

<file>
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</file>

====  Produits par catégories ====

{{:thematiques:informatique:capture_d_ecran_du_2019-02-05_09-34-09.png?400|}}

<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 "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
</file>