Differences

This shows you the differences between two versions of the page.

--- reports:beeasy [2024/03/18 10:37] – created sysadm
+++ reports:beeasy [2024/04/09 13:33] (current) – changelog antonio.andriychuk
@@ Line 1: / Line 1: @@
-====== ToChange ======
+====== BeEasy - Reports (2024-03-14) ======
+In data 13/03/2024 è stato richiesto la creazione di N custom report. Questi custom reports sono stati prima studiati da noi (BeCloud) per poter verificare la fattibilità del progetto e a seguito dello studio sono stati identificati i seguenti reports per commessa: \\
+\\
+) Acque Bresciane:
+    - **Export Massivo ARERA**
+    - **Riepilogo Annuo Chiamate Inbound per servizio utenti e TICSI** --> Ticket2Liv in carico a HDA PAT
+    - **Report Semestrale ARERA servizio utenti**
+    - **Report Giornaliero ABs**
+    - Report 30 minuti ABs --> **WAIT**
+\\
+) ASM Voghera:
+    - **Riepilogo Annuo Chiamate Inbound Annuo ASM Voghera**
+    - **Riepilogo Annuo ASM Voghera Credito**
+    - **Report Semestrale ARERA ASM Voghera**
+    - Report Annuo Autoletture ASM Voghera --> **Wait config Autolettura**
+\\
+) Broni Stradella:
+    - **Riepilogo Annuo Chiamate Inbound BRST** --> **Mail3Liv** --> IN CARICO AD HDA, da non aggiungere
+\\
+) ISIDATA:
+    - **Riepilogo Annuo Chiamate Outbound Isidata**
+\\
+) Metano Nord:
+    - **Report Semestrale ARERA Metano Nord**
+    - **Report Annuo Chiamate Inbound per Commessa**
+\\
+) Pavia Acque:
+    - **Export Massivo ARERA**
+    - **Riepilogo Annuo Chiamate Inbound per Pavia Acque** --> Ticket2o Livello in carico a HDA PAT
+    - **Riepilogo Chiamate Annuo Pavia Acque per servizio Credito**
+    - **Report Semestrale ARERA Pavia Acque**
+    - Report Annuo Autoletture Pavia Acque --> **Wait config Autolettura**
+    - Riepilogo Annuo Chiamate Outbound Pavia Acque  --> **Wait config Servizi OUT**
+\\
+) Tari:
+    - **Riepilogo Annuo Chiamate Inbound Annuo per Albonese, Cassolnovo, FerreraE, Gravellona, Pieve**
+\\
+) Utilità:
+    - **Riepilogo Annuo Chiamate Inbound per Utilità**
+    - **Report Semestrale ARERA**
+    - **Report Giornaliero Inbound Utilita**
+\\
+Report --> **Export Massimo**
+\\
+Query:
+\\
+<code>
+SELECT
+    hex(global_interaction_id) callId,
+    from_phone as ani,
+    start_time as TStampIn,
+    CASE
+        WHEN queue_time > 0 AND talk_time > 0 THEN
+            TIMESTAMPADD(SECOND, COALESCE(ivr_time, 0), start_time)
+        ELSE NULL
+    END AS TStampTransferred,
+    CASE
+        WHEN queue_time > 0 AND talk_time > 0 THEN
+            TIMESTAMPADD(SECOND,
+                COALESCE(pending_time, 0) +
+                COALESCE(queue_time, 0) +
+                COALESCE(ivr_time, 0),
+            start_time)
+        ELSE NULL
+    END AS TStampAnswered,
+    duration,
+    custom3 as OVFL_Varzi,
+    TIMESTAMPADD(SECOND, duration, start_time) AS TStampEnd
+FROM beeasy_it.call_detail
+WHERE  start_time >= $P{start_time} AND start_time < $P{end_time}
+AND service_name LIKE 'Acque Bresciane%'  **<-- sostituire con 'Pavia Acque%' per PVAcq**
+AND media_type <> 'EMAIL'
+ORDER BY initial_start_time, initial_call_id, start_time
+</code>
+\\
+Field in Jasper: \\
+\\
+ConnID --> $P{FORMATTER_SCRIPTLET}.formatGUID($F{callId}) \\
+Ani --> $F{ani} \\
+TStampIn --> $F{TStampIn} \\
+TStampTransferred --> $F{TStampTransferred} \\
+TStampAnswered --> $F{TStampAnswered} \\
+Durata --> $F{duration} \\
+OVFL_Varzi --> $F{OVFL_Varzi} \\
+TStampEnd --> $F{TStampEnd} \\
+\\
+\\
+Report Caricati in piattaforma con i seguenti nomi:
+    - Export Massivo ARERA ABs / Export Massivo ARERA PVAcq
+\\
+\\
+Report --> **Riepilogo_ABs_Credito_TICSI** \\
+\\
+Query:
+\\
+<code>
+SELECT
+start_time,
+YEAR(start_time) AS year,
+MONTH(start_time) AS month,
+service_name,
+count(*) as totale_ivr,
+COUNT(CASE
+    WHEN (queue_time > 0 OR pending_time > 0) AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS offered,
+COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS answered,
+(COUNT(CASE
+            WHEN (queue_time > 0 OR pending_time > 0) AND talk_time > 0 AND disposition NOT LIKE 'ABANDONED%' THEN 1
+        END) * 100.0 / COUNT(*)) AS percentage_answered,
+COUNT(CASE
+	WHEN disposition LIKE 'ABANDONED%' OR disposition LIKE 'SYSTEM%' THEN 1
+END
+) as abandoned
+FROM beeasy_it.call_detail cd
+WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
+AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED')
+AND service_name = 'Acque Bresciane - TICSI'
+AND (media_type <> 'EMAIL' or media_type is null)
+GROUP BY year, month
+</code>
+\\
+\\
+Field in Jasper: \\ \\
+Mese --> $F{month} == 1 ? "Gennaio" : ($F{month} == 2 ? "Febbraio" : ($F{month} == 3 ? "Marzo" : ($F{month} == 4 ? "Aprile" : ($F{month} == 5 ? "Maggio" : ($F{month} == 6 ? "Giugno" : ($F{month} == 7 ? "Luglio" : ($F{month} == 8 ? "Agosto" : ($F{month} == 9 ? "Settembre" : ($F{month} == 10 ? "Ottobre" : ($F{month} == 11 ? "Novembre" : ($F{month} == 12 ? "Dicembre" : "Invalid Month"))))))))))) \\
+Totale IVR --> $F{totale_ivr} \\
+Offerte Operatore --> $F{offered} \\
+Risposte --> $F{answered} \\
+% Risposte --> $F{percentage_answered} \\
+Abbandonate --> $F{abandoned} \\
+\\
+\\
+\\
+Report --> **Report Semestrale ARERA servizio utenti**\\
+\\
+Query:\\
+\\
+<code>
+SELECT
+start_time,
+YEAR(start_time) AS year,
+MONTH(start_time) AS month,
+service_name,
+COUNT(CASE
+    WHEN (queue_time > 0 OR pending_time > 0) AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS offered,
+COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS answered,
+((COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END)) * 100 / COUNT(CASE
+    WHEN (queue_time > 0 OR pending_time > 0) AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END)) as ls_percentage,
+as indice_as,
+avg(queue_time) as tma,
+NULL as op_auto
+FROM beeasy_it.call_detail cd
+WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
+AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED')
+AND service_name = 'Acque Bresciane'
+AND (media_type <> 'EMAIL' or media_type is null)
+GROUP BY year, month
+</code>
+\\
+Field in Jasper:\\ \\
+Offerte Op. : $F{offered}\\
+Risposte: $F{answered}\\
+% Risposte (Indice LS): $F{ls_percentage}\\
+Indice AS: $F{indice_as}\\
+TMA: $F{tma}\\
+Op. Auto: $F{op_auto}\\
+\\ Creato un "Group" per dividere le row Semestralmente
+    --> In "Outline" > Groups > Total
+        --> Creato Expression: ($F{month} <= 6) ? "First Semester" : "Second Semester"
+Allo stesso tempo, creato delle variabili per fare la somma per colonna, nel "Reset Type" ho aggiunto "[Group] Total" in modo che il report fornisca solo le somme/avg per semestre. \\ \\
+\\
+Report --> Report Giornaliero ABs \\
+Query: \\ \\
+<code>
+SELECT
+start_time,
+YEAR(start_time) AS year,
+MONTH(start_time) AS month,
+DAY(start_time) AS day,
+DAYNAME(start_time) AS nameday,
+service_name,
+COUNT(CASE
+    WHEN (queue_time > 0 OR pending_time > 0) AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS offered,
+COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND
+         disposition NOT LIKE 'ABANDONED%' THEN
+END) AS answered,
+(COALESCE(((COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND talk_time > 0
+		AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END)) * 100.0 / NULLIF(COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END), 0)), 0)) AS answer_percentage,
+COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND
+         disposition NOT LIKE 'ABANDONED%' and custom5 = 'ROVATO' THEN
+END) AS answered_rovato,
+(COALESCE(((COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND talk_time > 0 AND custom5 = 'ROVATO'
+		AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END)) * 100.0 / NULLIF(COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END), 0)), 0)) AS answer_percentage_rovato,
+COUNT(CASE
+    WHEN (queue_time > 0 OR pending_time > 0) AND
+         disposition NOT LIKE 'ABANDONED%' AND custom5 = 'OVFL' THEN
+END) AS offered_ovfl,
+COUNT(CASE
+	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND
+         disposition NOT LIKE 'ABANDONED%' AND custom5 = 'OVFL' THEN
+END) AS answered_ovfl,
+(COALESCE(((COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND disposition NOT LIKE 'ABANDONED%'
+		AND custom5 = 'OVFL' THEN 1
+	END)) * 100.0 / NULLIF(COUNT(CASE
+		WHEN (queue_time > 0 OR pending_time > 0)
+			AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END), 0)), 0)) AS offered_percentage_ovfl,
+(COALESCE(((COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND talk_time > 0
+		AND disposition NOT LIKE 'ABANDONED%'
+		AND custom5 = 'OVFL' THEN 1
+END)) * 100.0 / NULLIF(COUNT(CASE
+	WHEN (queue_time > 0 OR pending_time > 0)
+		AND disposition NOT LIKE 'ABANDONED%' THEN 1
+END), 0)), 0)) AS answer_percentage_ovfl
+FROM beeasy_it.call_detail cd
+WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
+AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED')
+AND service_name = 'Acque Bresciane'
+AND (media_type <> 'EMAIL' or media_type is null)
+GROUP BY year, month, day
+</code>
+**WAIT FOR CUSTOM FIELDS** \\ \\ \\
+Report --> Isidata Outbound \\
+\\
+Query: \\
+\\
+<code>
+SELECT
+start_time,
+YEAR(start_time) AS year,
+MONTH(start_time) AS month,
+service_name,
+COUNT(CASE
+	WHEN pending_time > 0 THEN
+END) AS calls_made,
+COUNT(CASE
+	WHEN talk_time > 0 THEN
+END) as calls_answered,
+COUNT(CASE
+	WHEN media_type = 'CHAT' and service_name LIKE '%SMS%' THEN
+END) as sms_sent,
+null as sms_ongoing
+FROM beeasy_it.call_detail cd
+WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
+AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED')
+AND service_name LIKE 'Isidata%'
+AND caller_phone_type = 'INTERNAL'
+AND (media_type <> 'EMAIL' or media_type is null)
+GROUP BY year, month
+</code> \\ \\
+Field in Jasper: \\
+\\
+Mese --> $F{mese} \\
+Chiamate Effettuate --> $F{calls_made} \\
+Chiamate Risposte --> $F{calls_answered} \\
+SMS inviati --> $F{sms_sent} \\
+SMS in corso --> $F{sms_ongoing} \\