====== Mes indicateurs personnels ======

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

==== Vérifier que mon code correspond à mon nom ====

{{:thematiques:informatique:capture_du_2018-12-31_07-48-32.png?400|}}

<file>
SELECT "public"."res_partner"."name" AS "name", "public"."res_partner"."active" AS "active", "public"."res_partner"."barcode" AS "barcode", "public"."res_partner"."is_blocked" AS "is_blocked", "public"."res_partner"."phone" AS "phone", "public"."res_partner"."property_account_receivable_software" AS "property_account_receivable_software"
FROM "public"."res_partner"
WHERE ("public"."res_partner"."active" = TRUE
   AND "public"."res_partner"."barcode" = {{Codebarre}})
LIMIT 2000
</file>


==== Mes achats par mois ====

{{:thematiques:informatique:capture_du_2018-12-31_07-51-02.png?400|}}

<file>
SELECT date_trunc('month', CAST("public"."report_pos_order"."date" AS timestamp)) AS "date", sum("public"."report_pos_order"."price_total") AS "Volume d'achats"
FROM "public"."report_pos_order"
LEFT JOIN "public"."product_product" "product_product__via__product_" ON "public"."report_pos_order"."product_id" = "product_product__via__product_"."id"
LEFT JOIN "public"."res_partner" "res_partner_name_via_res_partner" on "report_pos_order"."partner_id" = "res_partner_name_via_res_partner"."id"
WHERE "res_partner_name_via_res_partner"."barcode" = {{Code_barre}}
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>

==== Le détail de tous mes achats ====

{{:thematiques:informatique:capture_du_2018-12-31_07-52-30.png?400|}}

<file>
SELECT "public"."pos_order_line"."create_date" AS "create_date", "product_product__via__product_"."name_template" AS "name_template", "public"."pos_order_line"."price_unit" AS "price_unit", "public"."pos_order_line"."qty" AS "qty" 
FROM "public"."pos_order_line"
LEFT JOIN "public"."product_product" "product_product__via__product_" ON "public"."pos_order_line"."product_id" = "product_product__via__product_"."id"
LEFT JOIN "public"."pos_order" "pos_order_via_pos_session_table" on "public"."pos_order_line"."order_id" = "pos_order_via_pos_session_table"."id"
LEFT JOIN "public"."res_partner" "res_partner_name_via_res_partner" on "pos_order_via_pos_session_table"."partner_id" = "res_partner_name_via_res_partner"."id"
WHERE "res_partner_name_via_res_partner"."barcode" = {{Code_barre}}
</file>

==== Mes achats par catégorie ====

{{:thematiques:informatique:capture_du_2018-12-31_07-56-19.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 category, sum("public"."report_pos_order"."price_total") AS "Volume d'achats" 
FROM "public"."report_pos_order"
LEFT JOIN "public"."product_product" "product_product__via__product_" ON "public"."report_pos_order"."product_id" = "product_product__via__product_"."id"
LEFT JOIN "public"."res_partner" "res_partner_name_via_res_partner" on "report_pos_order"."partner_id" = "res_partner_name_via_res_partner"."id"
left join product_category "cat" on "cat"."id"="public"."report_pos_order"."product_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 "res_partner_name_via_res_partner"."barcode" = {{Code_barre}} AND "public"."report_pos_order"."date" >= {{Depuis_Date}}
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("public"."report_pos_order"."price_total") DESC, 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>