====== Activité du magasin ======

===== Tableau de bord sur l'activité du magasin =====

==== Nombre de références ====

Cf principaux indicateurs

==== Nombre d'acheteurs par mois ====

Cf principaux indicateurs

==== Evolution du volume de ventes (TTC) par semaine cumulé par jour de la semaine ====

{{:thematiques:informatique:capture_du_2018-12-28_07-40-24.png?400|}}

<file>
SELECT sum("public"."report_pos_order"."price_total") AS "sum", (CAST(extract(dow from CAST("public"."report_pos_order"."date" AS timestamp)) AS integer) + 1) AS "jour", (date_trunc('week', CAST((CAST("public"."report_pos_order"."date" AS timestamp) + INTERVAL '1 day') AS timestamp)) - INTERVAL '1 day') AS "semaine"
FROM "public"."report_pos_order"
GROUP BY (CAST(extract(dow from CAST("public"."report_pos_order"."date" AS timestamp)) AS integer) + 1), (date_trunc('week', CAST((CAST("public"."report_pos_order"."date" AS timestamp) + INTERVAL '1 day') AS timestamp)) - INTERVAL '1 day')
ORDER BY (CAST(extract(dow from CAST("public"."report_pos_order"."date" AS timestamp)) AS integer) + 1) ASC, (date_trunc('week', CAST((CAST("public"."report_pos_order"."date" AS timestamp) + INTERVAL '1 day') AS timestamp)) - INTERVAL '1 day') ASC
</file>

==== Nombre d'acheteurs par semaine ====

{{:thematiques:informatique:capture_du_2018-12-28_07-42-44.png?400|}}

<file>
SELECT count(distinct "pos_order__via__order_id"."partner_id") AS "count", date_trunc('week', 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('week', CAST("public"."pos_order_line"."create_date" AS timestamp))
ORDER BY date_trunc('week', CAST("public"."pos_order_line"."create_date" AS timestamp)) ASC
</file>

==== Ventes par créneau horaire ====

{{:thematiques:informatique:capture_du_2018-12-28_07-44-48.png?400|}}

<file>
ELECT to_char(date,'D-Day HH24H') as "Date", sum(price_total) AS "Montant" 
FROM report_pos_order
WHERE date >= '2018-08-26'
GROUP BY to_char(date,'D-Day HH24H')
ORDER BY to_char(date,'D-Day HH24H') ASC
</file>

==== Nouveaux produits (hors fruits et légumes) ====

{{:thematiques:informatique:capture_du_2018-12-28_07-47-02.png?400|}}

<file>
SELECT "public"."product_product"."id" AS "id", "public"."product_product"."active" AS "active", "public"."product_product"."barcode" AS "barcode", "public"."product_product"."barcode_base" AS "barcode_base", "public"."product_product"."barcode_rule_id" AS "barcode_rule_id", "public"."product_product"."create_date" AS "create_date", "public"."product_product"."create_uid" AS "create_uid", "public"."product_product"."default_code" AS "default_code", "public"."product_product"."last_history_day" AS "last_history_day", "public"."product_product"."last_history_month" AS "last_history_month", "public"."product_product"."last_history_week" AS "last_history_week", "public"."product_product"."message_last_post" AS "message_last_post", "public"."product_product"."name_template" AS "name_template", "public"."product_product"."number_of_periods_real" AS "number_of_periods_real", "public"."product_product"."product_tmpl_id" AS "product_tmpl_id", "public"."product_product"."scale_group_id" AS "scale_group_id", "public"."product_product"."scale_logo_code" AS "scale_logo_code", "public"."product_product"."scale_sequence" AS "scale_sequence", "public"."product_product"."scale_tare_weight" AS "scale_tare_weight", "public"."product_product"."to_print" AS "to_print", "public"."product_product"."volume" AS "volume", "public"."product_product"."weight" AS "weight", "public"."product_product"."write_date" AS "write_date", "public"."product_product"."write_uid" AS "write_uid"
FROM "public"."product_product"
LEFT JOIN "public"."product_template" "product_template__via__product" ON "public"."product_product"."product_tmpl_id" = "product_template__via__product"."id"
WHERE (CAST("public"."product_product"."create_date" AS date) BETWEEN CAST((NOW() + INTERVAL '-15 day') AS date)
   AND CAST((NOW() + INTERVAL '-1 day') AS date) AND "public"."product_product"."active" = TRUE AND "product_template__via__product"."pos_categ_id" <> 613)
LIMIT 2000
</file>

==== Passages en caisse par créneau horaire (depuis 26 août 2018) ====

{{:thematiques:informatique:capture_du_2018-12-28_07-49-09.png?400|}}

<file>
SELECT to_char("pos_order_line"."create_date",'D-Day HH24H') as "Date", count(distinct "pos_order__via__order_id"."partner_id") AS "count"
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"
WHERE "pos_order_line"."create_date" >= '2018-08-26'
GROUP BY to_char("pos_order_line"."create_date",'D-Day HH24H')
ORDER BY to_char("pos_order_line"."create_date",'D-Day HH24H') ASC
</file>

===== Questions complémentaires sur l'activité du magasin =====

==== Passage par créneau en caisse entre 2 dates ====

{{:thematiques:informatique:capture_du_2018-12-28_07-54-52.png?400|}}

<file>
SELECT to_char("pos_order_line"."create_date",'D-Day HH24H') as "Date", count(distinct "pos_order__via__order_id"."partner_id") AS "count"
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"
WHERE "pos_order_line"."create_date" >= {{Apres_le}}  AND "pos_order_line"."create_date" <= {{Avant_le}}
GROUP BY to_char("pos_order_line"."create_date",'D-Day HH24H')
ORDER BY to_char("pos_order_line"."create_date",'D-Day HH24H') ASC
</file>

==== Ventes par créneau horaire entre deux dates (incluses) ====

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

<file>
SELECT to_char(date,'D-Day HH24H') as "Date", sum(price_total) AS "Montant" 
FROM report_pos_order
WHERE  date >= {{Apres_le}} AND Date <= {{Avant_le}}
GROUP BY to_char(date,'D-Day HH24H')
ORDER BY to_char(date,'D-Day HH24H') ASC
</file>