Outils pour utilisateurs


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

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

Nombre d'acheteurs par semaine

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

Ventes par créneau horaire

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

Nouveaux produits (hors fruits et légumes)

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

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

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

Questions complémentaires sur l'activité du magasin

Passage par créneau en caisse entre 2 dates

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

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

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