create table risultati_performance_consulenti as(
with
dati as (
select
a.company,
a.tenant,
a.id,
a.date_start,
current_timestamp AT TIME ZONE 'Europe/Rome' AS data_ultimo_aggiornamento,
date(a.date_end) AS giorno,
min(case
when a3.date_start - a.date_start < '00:25:00'::time then '< 25 minuti'
when a3.date_start - a.date_start < '00:50:00'::time then 'fra 25 e 50 minuti'
when a3.date_start - a.date_start >= '00:50:00'::time then '> 50 minuti'
end) as distanza,
CASE
WHEN a.date_end BETWEEN current_date AND current_date + interval '1 days' THEN 'oggi'
WHEN a.date_end BETWEEN current_date - interval '1 days' AND current_date + interval '1 days' THEN 'ieri'
WHEN a.date_end BETWEEN current_date - interval '2 days' AND current_date + interval '1 days' THEN '2-3'
WHEN a.date_end BETWEEN current_date - interval '6 days' AND current_date + interval '1 days' THEN '4-7'
WHEN a.date_end BETWEEN current_date - interval '14 days' AND current_date + interval '1 days' THEN '8-15'
WHEN a.date_end BETWEEN current_date - interval '29 days' AND current_date + interval '1 days' THEN '16-30'
WHEN a.date_end BETWEEN current_date - interval '59 days' AND current_date + interval '1 days' THEN '31-60'
WHEN a.date_end BETWEEN current_date - interval '89 days' AND current_date + interval '1 days' THEN '61-90'
WHEN a.date_end BETWEEN current_date - interval '119 days' AND current_date + interval '1 days' THEN '91-120'
WHEN a.date_end BETWEEN current_date - interval '179 days' AND current_date + interval '1 days' THEN '121-180'
END AS periodo,
lower(a.company) AS azienda,
lower(a.tenant) AS area,
s.id AS id_store,
lower(s.alias) AS istituto,
u2.id AS id_op,
u2.lastname AS operatore_cognome,
u2.firstname AS operatore_nome,
operatore_cognome + ' ' + operatore_nome AS operatrice,
u3.id AS id_op_comm,
u3.lastname AS operatore_cognome_comm,
u3.firstname AS operatore_nome_comm,
operatore_cognome_comm + ' ' + operatore_nome_comm AS operatrice_comm,
a.customer_id AS id_customer,
a.id AS id_app,
case when a.user_id_comm is not null then 1 else 0 end as commerciale_operativo,
CASE
WHEN ac.visita_commerciale_id_riattr IN ('commerciale_1') THEN 'commerciale 1v'
WHEN ac.visita_commerciale_id_riattr IN ('commerciale_0') THEN 'commerciale rientro'
WHEN ac.visita_commerciale_id_riattr IN ('cliente_centro') THEN 'operativo'
WHEN ac.visita_commerciale_id_riattr IN ('porta_1') THEN 'porta 1v'
WHEN ac.visita_commerciale_id_riattr IN ('rientro_porta') THEN 'porta rientro'
WHEN ac.visita_commerciale_id_riattr IN ('contactcenter_1') THEN 'contact center 1v'
WHEN ac.visita_commerciale_id_riattr IN ('contactcenter_0') THEN 'contact center rientro'
END AS commerciale,
max(oa.date) as disp,
max(case when date(a.date_start) = date(a2.date_start) then a2.user_id end) as id_op_vendita,
CASE
WHEN a.user_times <= 1 THEN 'nuovo cliente'
ELSE 'smaltimento'
END AS visita,
tc.name AS categoria_trattamento,
CASE
WHEN t.f_contab IS NOT TRUE THEN a.id
END AS ingressi_singolo,
CASE
WHEN (a.f_ref = 0 OR a.f_ref IS NULL) THEN a.price
ELSE 0
END AS incasso_singolo,
sum(case
when date(a.date_start) = date(a2.date_start)
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
then a2.price
end) AS incasso_primo_giorno,
count(case
when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
and date(a2.date_start) > date(a.date_start)
then true
end) as ingresso_mese,
count( distinct
case
when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
and date(a2.date_start) > date(a.date_start)
then a2.customer_id
end) as clienti_mese,
sum( case
when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
and date(a2.date_start) > date(a.date_start)
AND (a2.f_ref = 0 OR a2.f_ref IS NULL)
then a2.customer_id
end) as incasso_mese,
count( distinct
case
when date(a.date_start) = date(a2.date_start)
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
and a2.price >= 0
then a2.customer_id
end) as clienti_primo_giorno_paganti,
count( distinct
case
when date(a.date_start) = date(a2.date_start)
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
and a2.price >= 100
then a2.customer_id
end) as clienti_primo_giorno_paganti_new,
sum(
case
when date(a.date_start) = date(a2.date_start)
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
-- and a2.price >= 100
then a2.price
end) as cassa_primo_giorno_paganti_tot_new,
count( distinct
case
when date(a.date_start) < date(a2.date_start)
and date(a2.date_start) <= (date(a.date_start) + interval '7 days')
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
and a2.price >= 100
then a2.customer_id
end) as clienti_sette_giorni_paganti_new,
sum( distinct
case
when date(a.date_start) < date(a2.date_start)
and date(a2.date_start) <= (date(a.date_start) + interval '7 days')
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
-- and a2.price >= 100
then a2.price
end) as cassa_sette_giorni_paganti_new,
count( distinct
case
when date(a.date_start) < date(a2.date_start)
and date(a2.date_start) <= (date(a.date_start) + interval '30 days')
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
and a2.price >= 100
then a2.customer_id
end) as clienti_trenta_giorni_paganti_new,
sum( distinct
case
when date(a.date_start) < date(a2.date_start)
and date(a2.date_start) <= (date(a.date_start) + interval '30 days')
and (a2.f_ref = 0 OR a2.f_ref IS NULL)
-- and a2.price >= 100
then a2.price
end) as cassa_trenta_giorni_paganti_new
FROM
appointments a INNER JOIN appointments_commerciale ac using (company, tenant, id)
INNER JOIN users u
ON
u.id = a.by_operator
AND u.company = a.company
AND u.tenant = a.tenant
INNER JOIN users u2
ON
u2.id = a.user_id
AND u2.company = a.company
AND u2.tenant = a.tenant
left join users u3
on
u3.id = a.user_id_comm
and u3.company = a.company
and u3.tenant = a.tenant
INNER JOIN stores s
ON
s.id = a.store_id
AND s.company = a.company
AND s.tenant = a.tenant
INNER JOIN treatments t
ON
t.id = a.treatment_id
AND t.company = a.company
AND t.tenant = a.tenant
INNER JOIN treatment_categories tc
ON
tc.id = t.treatment_category_id
AND tc.company = t.company
AND tc.tenant = t.tenant
LEFT JOIN operator_availabilities oa
ON oa.user_id = u2.id
AND oa.company = u2.company
AND oa.tenant = u2.tenant
AND oa.date BETWEEN date_trunc('month', current_date) AND last_day(current_date)
AND oa.deleted_at IS NULL
AND oa.reserved = 1
left outer join appointments AS a2
on a.customer_id = a2.customer_id
AND a.company = a2.company
AND a.tenant = a2.tenant
AND a2.deleted_at IS NULL
AND a2.paid IS TRUE
AND a2.recalled < 3
AND a.id <> a2.id
left outer join appointments AS a3
on a.user_id = a3.user_id
and a.store_id = a3.store_id
AND a.company = a3.company
AND a.tenant = a3.tenant
AND a3.deleted_at IS NULL
AND a3.paid IS TRUE
AND a3.recalled < 3
AND a.id <> a3.id
AND a3.date_start between a.date_start and (date(a.date_start) + interval '1 day')
WHERE
-- a.date_end >= '2025-10-21' -- (current_date - interval '180 days')
-- AND a.date_end < '2025-10-22' -- (current_timestamp AT TIME ZONE 'Europe/Rome') and
a.paid IS TRUE
AND a.tenant NOT IN ('Morepa', 'Lombardia', 'Bios')
-- and commerciale not in ('operativo')
AND a.company IN ('Biolaser')
-- and (a.tenant IN ('Veneto')) AND (istituto = 'verona')
AND a.deleted_at IS null
AND ( (t.f_contab IS TRUE
AND (NOT EXISTS (
SELECT
*
FROM
appointments a3
INNER JOIN treatments t2
ON
t2.id = a3.treatment_id
AND t2.company = a3.company
AND t2.tenant = a3.tenant
WHERE
a.customer_id = a3.customer_id
AND a.company = a3.company
AND a.tenant = a3.tenant
AND t2.f_contab IS NOT TRUE
AND ((a3.id <> a.id
AND date(a3.date_start) = date(a.date_start)))
AND a3.deleted_at IS NULL
AND a3.recalled < 3
AND a3.paid IS TRUE )))
OR ( t.f_contab IS NOT TRUE
AND NOT EXISTS (
SELECT
*
FROM
appointments a3
INNER JOIN treatments t2
ON
t2.id = a3.treatment_id
AND t2.company = a3.company
AND t2.tenant = a3.tenant
WHERE
a.customer_id = a3.customer_id
AND a.company = a3.company
AND a.tenant = a3.tenant
AND t2.f_contab IS NOT TRUE
AND (a3.date_start < a.date_start
AND date(a3.date_start) = date(a.date_start))
AND a3.deleted_at IS NULL
AND a3.recalled < 3
AND a3.paid IS TRUE )))
and not exists(
SELECT
*
FROM
operator_availabilities oa_prec
WHERE
oa_prec.user_id = u2.id
AND oa_prec.company = u2.company
AND oa_prec.tenant = u2.tenant
AND oa_prec.date BETWEEN date_trunc('month', current_date) AND last_day(current_date)
AND oa_prec.deleted_at IS NULL
AND oa_prec.reserved = 1
and oa_prec.date < oa.date
)
and not exists(
select * from appointments as a3_prima
where a.user_id = a3_prima.user_id
and a.store_id = a3_prima.store_id
AND a.company = a3_prima.company
AND a.tenant = a3_prima.tenant
AND a3_prima.deleted_at IS NULL
AND a3_prima.paid IS TRUE
AND a3_prima.recalled < 3
AND a.date_start < a3_prima.date_start
AND a3_prima.date_start < a3.date_start
)
group by
a.company,
a.tenant,
a.id,
a.date_start,
data_ultimo_aggiornamento,
giorno,
-- distanza,
periodo,
azienda,
area,
id_store,
istituto,
id_op,
operatore_cognome,
operatore_nome,
operatrice,
id_op_comm,
operatore_cognome_comm,
operatore_nome_comm,
operatrice_comm,
id_customer,
id_app,
commerciale_operativo,
commerciale,
visita,
categoria_trattamento,
ingressi_singolo,
incasso_singolo
),
periodi as ( SELECT 'oggi' AS tempo, ' oggi' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' ieri' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 3 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 3 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 3 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 7 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 7 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 7 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, ' 7 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 15 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 15 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 15 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, ' 15 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, ' 15 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT '16-30' AS tempo, ' 30 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT '16-30' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT '31-60' AS tempo, ' 60 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '16-30' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '31-60' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT '61-90' AS tempo, ' 90 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '16-30' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '31-60' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '61-90' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT '91-120' AS tempo, '120 giorni' AS periodi
UNION ALL SELECT 'oggi' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT 'ieri' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '2-3' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '4-7' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '8-15' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '16-30' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '31-60' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '61-90' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '91-120' AS tempo, '180 giorni' AS periodi
UNION ALL SELECT '121-180' AS tempo, '180 giorni' AS periodi
),
commerciali_operativi as (
SELECT
data_ultimo_aggiornamento,
giorno,
extract('month' FROM giorno) AS periodo,
azienda,
periodi.periodi,
commerciale,
area,
id_store,
istituto,
id_op_comm as id_op,
operatore_cognome_comm as operatore_cognome,
operatore_nome_comm as operatore,
operatrice_comm as operatrice,
id_customer,
dati.visita,
categoria_trattamento,
'commerciali_operativi' as tipo,
distanza,
sum(commerciale_operativo) as commerciale_operativo,
count(disp) AS consulente,
coalesce(count(ingressi_singolo), 0) AS ingresso_singolo,
coalesce(sum(incasso_singolo), 0) AS incasso_singolo,
coalesce(sum(incasso_primo_giorno), 0) AS incasso_primo_giorno,
coalesce(sum(incasso_singolo),0) + coalesce(sum(incasso_primo_giorno),0) AS cassa_primo_giorno,
-- coalesce(sum(cassa_primo_giorno_individuale_f), 0) AS cassa_primo_giorno_f,
sum(clienti_primo_giorno_paganti) AS clienti_primo_giorno_paganti,
sum(clienti_primo_giorno_paganti_new) AS clienti_primo_giorno_paganti_new,
coalesce(sum(cassa_primo_giorno_paganti_tot_new),0) AS cassa_primo_giorno_paganti_tot_new,
sum(clienti_sette_giorni_paganti_new) AS clienti_sette_giorni_paganti_new,
coalesce(sum(cassa_sette_giorni_paganti_new),0) AS cassa_sette_giorni_paganti_new,
sum(clienti_trenta_giorni_paganti_new) AS clienti_trenta_giorni_paganti_new,
coalesce(sum(cassa_trenta_giorni_paganti_new),0) AS cassa_trenta_giorni_paganti_new
from dati
INNER join periodi
ON dati.periodo = periodi.tempo
GROUP BY
data_ultimo_aggiornamento,
giorno,
azienda,
area,
id_store,
istituto,
commerciale,
periodi.periodi,
tipo,
id_op_comm,
operatore_cognome_comm,
operatore_nome_comm,
operatrice_comm,
id_customer,
dati.visita,
categoria_trattamento,
dati.distanza
),
performance_consulenti as (
SELECT
data_ultimo_aggiornamento,
giorno,
extract('month' FROM giorno) AS periodo,
azienda,
periodi.periodi,
commerciale,
area,
id_store,
istituto,
id_op,
operatore_cognome,
operatore_nome,
operatrice,
id_customer,
dati.visita,
categoria_trattamento,
'commerciali' as tipo,
dati.distanza,
sum(commerciale_operativo) as commerciale_operativo,
count(disp) AS consulente,
count(ingressi_singolo) AS ingresso_singolo,
coalesce(sum(incasso_singolo),0) AS incasso_singolo,
coalesce(sum(incasso_primo_giorno),0) AS incasso_primo_giorno,
coalesce(sum(incasso_singolo),0) + coalesce(sum(incasso_primo_giorno),0) AS cassa_primo_giorno,
-- coalesce(sum(incasso_singolo_f),0) + coalesce(sum(incasso_primo_giorno_f),0) AS cassa_primo_giorno_f,
sum(clienti_primo_giorno_paganti) AS clienti_primo_giorno_paganti,
sum(clienti_primo_giorno_paganti_new) AS clienti_primo_giorno_paganti_new,
coalesce(sum(cassa_primo_giorno_paganti_tot_new),0) AS cassa_primo_giorno_paganti_tot_new,
sum(clienti_sette_giorni_paganti_new) AS clienti_sette_giorni_paganti_new,
coalesce(sum(cassa_sette_giorni_paganti_new),0) AS cassa_sette_giorni_paganti_new,
sum(clienti_trenta_giorni_paganti_new) AS clienti_trenta_giorni_paganti_new,
coalesce(sum(cassa_trenta_giorni_paganti_new),0) AS cassa_trenta_giorni_paganti_new
from dati
INNER join periodi
ON dati.periodo = periodi.tempo
GROUP BY
data_ultimo_aggiornamento,
giorno,
azienda,
area,
id_store,
istituto,
commerciale,
periodi.periodi,
tipo,
id_op,
operatore_cognome,
operatore_nome,
operatrice,
id_customer,
dati.visita,
categoria_trattamento,
dati.distanza
)
-- Combining both queries with UNION ALL
SELECT * FROM commerciali_operativi
-- where giorno='2025-10-21' and periodo=10 and azienda='biolaser' and periodi='180 giorni' and commerciale not in ('operativo') and area='veneto' and istituto='verona' -- and id_op=143
where azienda='biolaser' and periodi='180 giorni'
UNION ALL
SELECT * FROM performance_consulenti
-- where giorno='2025-10-21' and periodo=10 and azienda='biolaser' and periodi='180 giorni' and commerciale not in ('operativo') and area='veneto' and istituto='verona' -- and id_op=143
where azienda='biolaser' and periodi='180 giorni'
)
;
Comments
0 B
|0 👍
/0 👎