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' ) ;