API tools faq
paste
cldscchttn

Filiera diretta

cldscchttn
Nov 11th, 2025 (edited)
6,189
0
Never
3
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 12.66 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE VIEW public.filiera_diretta
  2. AS SELECT timezone('Europe/Rome'::text, 'now'::text::timestamp with time zone) AS data_ultimo_aggiornamento, dati.company, upper(dati.category::text) AS fonte, upper(dati.categoria_lead_d) AS categoria, upper(dati.treatment::text) AS treatment, upper(dati.pricing2::text) AS pricing, upper(dati.campagna::text) AS campagna, id_italia, to_char(dati.first_assign_at, 'YYYY-MM-DD'::text) AS giorno, to_char(dati.created_at_l, 'YYYY-MM-DD'::text) AS giorno_c, upper(dati.first_user_id::text) AS operatore, lower(dati.lastname::text) AS cognome, lower(dati.firstname::text) AS nome, dati.bot, dati.stato_bot, dati.dettaglio_stato_bot, sum(
  3.         CASE
  4.             WHEN dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL THEN 1
  5.             ELSE 0
  6.         END) AS num_lead_comm_ass, sum(
  7.         CASE
  8.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN 1
  9.             ELSE 0
  10.         END) AS num_app, sum(
  11.         CASE
  12.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 AND NOT (dati.recalled = 3 AND dati.cancelled_at::date = dati.created_at_a::date) THEN date_diff('day'::text, dati.created_at_a, dati.date_start)
  13.             ELSE NULL::bigint
  14.         END) AS delay_agendati, sum(
  15.         CASE
  16.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
  17.             ELSE 0
  18.         END) AS num_app_past, sum(
  19.         CASE
  20.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 4 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
  21.             ELSE 0
  22.         END) AS num_app_inagdisd, sum(
  23.         CASE
  24.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled = 3 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
  25.             ELSE 0
  26.         END) AS num_app_disd, sum(
  27.         CASE
  28.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled = 4 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
  29.             ELSE 0
  30.         END) AS num_app_spost, sum(
  31.         CASE
  32.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
  33.             ELSE 0
  34.         END) AS num_app_pres, sum(
  35.         CASE
  36.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN dati.price
  37.             ELSE 0::numeric
  38.         END) AS incasso, sum(
  39.         CASE
  40.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN dati.incasso_succ_tot
  41.             ELSE 0::numeric
  42.         END) AS incasso_succ, sum(
  43.         CASE
  44.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text <> 'da chiamare'::text THEN 1
  45.             ELSE 0
  46.         END) AS num_lead_lav, sum(
  47.         CASE
  48.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND (dati.call_state::text = 'fake'::text OR dati.call_state::text = 'fuori zona'::text OR dati.call_state::text = 'non interessato'::text OR dati.call_state::text = 'no download'::text) THEN 1
  49.             ELSE 0
  50.         END) AS num_lead_scarto, sum(
  51.         CASE
  52.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'fake'::text THEN 1
  53.             ELSE 0
  54.         END) AS num_lead_scarto_fak, sum(
  55.         CASE
  56.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'fuori zona'::text THEN 1
  57.             ELSE 0
  58.         END) AS num_lead_scarto_fz, sum(
  59.         CASE
  60.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'non interessato'::text THEN 1
  61.             ELSE 0
  62.         END) AS num_lead_scarto_ni, sum(
  63.         CASE
  64.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'no download'::text THEN 1
  65.             ELSE 0
  66.         END) AS num_lead_scarto_nd, sum(
  67.         CASE
  68.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND (dati.call_state::text = 'NR'::text OR dati.call_state::text = 'NR 2'::text OR dati.call_state::text = 'NR 3'::text OR dati.call_state::text = 'NR 4'::text OR dati.call_state::text = 'NR 5'::text OR dati.call_state::text = 'NR 6'::text OR dati.call_state::text = 'NR 7'::text OR dati.call_state::text = 'NR 8'::text OR dati.call_state::text = 'NR 9'::text OR dati.call_state::text = 'NR 10'::text) THEN 1
  69.             ELSE 0
  70.         END) AS num_lead_nr,
  71.         CASE
  72.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.alias
  73.             ELSE NULL::character varying
  74.         END AS store,
  75.         CASE
  76.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.name
  77.             ELSE NULL::character varying
  78.         END AS categoria_lead,
  79.         conversation_analysis_results.has_price_request,
  80.         conversation_analysis_results.appointment_taken_after_price,
  81.         rimpalli_per_sessione.ai_rimpalli,
  82.         rimpalli_per_sessione.human_rimpalli,
  83.         rimpalli_per_sessione.has_preferred_call_time
  84.    FROM ( SELECT l.id, l.f_oper, l.f_dup, l.f_dup_wrk, l.f_dup_regen, l.f_dup_mkt, l.customer_id AS id_customer, l.call_state,
  85.                 CASE
  86.                     WHEN l.chatbot_process_state IS NOT NULL THEN 'Valeria'::text
  87.                     ELSE 'No Valeria'::text
  88.                 END AS bot,
  89.                 CASE
  90.                     WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_richiamare'::text THEN 'richiamo fissato'::text
  91.                     WHEN l.chatbot_process_state IS NOT NULL AND (l.chatbot_call_state::text <> 'da_richiamare'::text OR l.chatbot_call_state IS NULL) THEN 'richiamo non fissato'::text
  92.                     ELSE 'No Valeria'::text
  93.                 END AS stato_bot,
  94.                 CASE
  95.                     WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_richiamare'::text THEN 'richiamo fissato'::text
  96.                     WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_chiamare'::text THEN 'interagisce ma no app'::text
  97.                     WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'non_risposto'::text THEN 'no interagisce'::text
  98.                     WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text <> 'non_risposto'::text AND l.chatbot_call_state::text <> 'da_chiamare'::text AND l.chatbot_call_state::text <> 'da_richiamare'::text THEN 'scarto'::text
  99.                     ELSE 'No Valeria'::text
  100.                 END AS dettaglio_stato_bot, a.f_ref, a.paid, tc.name,
  101.                 CASE
  102.                     WHEN l.f_dup = 0 AND l.f_oper = 0 AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date THEN 1
  103.                     ELSE 0
  104.                 END AS num_app_paid, a.price, a.customer_id, a.date_start, a.date_end, a.recalled, a.cancelled_at, a.created_at AS created_at_a, s.alias, l.company, l.first_user_id, regexp_substr(l.uid::text, '[0-9]+'::text) AS  id_italia, l.tenant, l."region", l.category,
  105.                 CASE
  106.                     WHEN lower(l.campagna::text) ~~* '%corpo%'::text THEN 'corpo'::text
  107.                     WHEN lower(l.campagna::text) ~~* '%laser%'::text THEN 'laser'::text
  108.                     ELSE 'altro'::text
  109.                 END AS categoria_lead_d, l.treatment, l.pricing2, l.campagna, l.first_assign_at, l.created_at AS created_at_l, users.firstname, users.lastname, l.created_at, ( SELECT sum(
  110.                         CASE
  111.                             WHEN appointments_tmp.paid = 1 THEN 1
  112.                             ELSE NULL::integer
  113.                         END) AS sum
  114.                    FROM appointments appointments_tmp
  115.                   WHERE appointments_tmp.customer_id = a.customer_id AND appointments_tmp.company::text = a.company::text AND appointments_tmp.tenant::text = a.tenant::text AND appointments_tmp.paid = 1 AND appointments_tmp.date_end > a.date_end AND appointments_tmp.date_end < add_months(a.date_end, 3::bigint) AND l.f_dup = 0 AND l.f_oper = 0 AND a.recalled IS NOT NULL AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date) AS app_succ_tot, ( SELECT sum(appointments_tmp.price) AS sum
  116.                    FROM appointments appointments_tmp
  117.                   WHERE appointments_tmp.customer_id = a.customer_id AND appointments_tmp.company::text = a.company::text AND appointments_tmp.tenant::text = a.tenant::text AND appointments_tmp.paid = 1 AND appointments_tmp.id <> a.id AND date(appointments_tmp.date_end) = date(a.date_end) AND l.f_dup_mkt = 0 AND a.recalled IS NOT NULL AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date) AS incasso_succ_tot
  118.            FROM leads l
  119.       JOIN customers c ON l.customer_id = c.id AND l.company::text = c.company::text AND l.tenant::text = c.tenant::text
  120.    LEFT JOIN appointments a ON l.id = a.lead_id AND l.company::text = a.company::text AND (l.tenant::text = a.tenant::text OR l.tenant::text = 'Italia'::text)
  121.    LEFT JOIN users ON l.first_user_id = users.id AND l.company::text = users.company::text AND l.tenant::text = users.tenant::text
  122.    LEFT JOIN stores s ON s.id = a.store_id AND s.company::text = a.company::text AND s.tenant::text = a.tenant::text
  123.    LEFT JOIN treatments t ON t.id = a.treatment_id AND t.company::text = a.company::text AND t.tenant::text = a.tenant::text
  124.    LEFT JOIN treatment_categories tc ON t.treatment_category_id = tc.id AND t.company::text = tc.company::text AND t.tenant::text = tc.tenant::text
  125.   WHERE l.first_assign_at >= '2025-04-01 00:00:00'::timestamp without time zone AND l.first_assign_at < 'now'::text::date AND lower(l.category::text) <> 'confermacomm'::text AND lower(l.category::text) <> 'confermaop'::text AND (l.f_dup_mkt = 0 OR l.f_dup_mkt IS NULL) AND NOT (EXISTS ( SELECT a4.id
  126.    FROM appointments a4
  127.   WHERE a4.recalled < 4 AND a4.id < a.id AND a4.lead_id = a.lead_id AND a4.company::text = a.company::text AND a4.tenant::text = a.tenant::text AND a4.deleted_at IS NULL)) AND users.active IS TRUE AND l.deleted_at IS NULL AND a.deleted_at IS NULL AND (a.recalled IS NULL OR a.recalled < 4) AND (l.company::text = 'Biolaser'::text OR l.company::text = 'Biosmed'::text)) dati
  128.    LEFT JOIN conversation_analysis_results ON dati.id_italia=conversation_analysis_results.lead_id
  129.    LEFT JOIN rimpalli_per_sessione ON dati.id_italia=rimpalli_per_sessione.lead_id
  130.   GROUP BY dati.company, dati.bot, upper(dati.category::text), to_char(dati.first_assign_at, 'YYYY-MM-DD'::text), to_char(dati.created_at_l, 'YYYY-MM-DD'::text), upper(dati.first_user_id::text), dati.firstname, dati.lastname, dati.treatment, upper(dati.pricing2::text), dati.campagna,
  131.         CASE
  132.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.alias
  133.             ELSE NULL::character varying
  134.         END,
  135.         CASE
  136.             WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.name
  137.             ELSE NULL::character varying
  138.         END, dati.stato_bot, dati.dettaglio_stato_bot, dati.id_italia, upper(dati.categoria_lead_d),
  139.         conversation_analysis_results.has_price_request,
  140.         conversation_analysis_results.appointment_taken_after_price,
  141.         rimpalli_per_sessione.ai_rimpalli,
  142.         rimpalli_per_sessione.human_rimpalli,
  143.         rimpalli_per_sessione.has_preferred_call_time
  144.   ORDER BY dati.company, to_char(dati.first_assign_at, 'YYYY-MM-DD'::text) DESC, upper(dati.first_user_id::text), lower(dati.lastname::text), lower(dati.firstname::text);
  145.  
  146.  
  147.  
Advertisement
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!