BeEasy - Reports (2024-03-14) [BeCloud Internal Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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: \\
\\
1) 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**
\\
2) 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**
\\
3) Broni Stradella: 
    - **Riepilogo Annuo Chiamate Inbound BRST** --> **Mail3Liv** --> IN CARICO AD HDA, da non aggiungere
\\
4) ISIDATA: 
    - **Riepilogo Annuo Chiamate Outbound Isidata**
\\    
5) Metano Nord: 
    - **Report Semestrale ARERA Metano Nord**
    - **Report Annuo Chiamate Inbound per Commessa** 
\\    
6) 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**
\\    
7) Tari: 
    - **Riepilogo Annuo Chiamate Inbound Annuo per Albonese, Cassolnovo, FerreraE, Gravellona, Pieve**
\\
8) 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
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
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
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
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
        1
END)) as ls_percentage,
100 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
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
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
        1
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
        1
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
        1
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
	1
END) AS calls_made,
COUNT(CASE
	WHEN talk_time > 0 THEN
	1
END) as calls_answered,
COUNT(CASE
	WHEN media_type = 'CHAT' and service_name LIKE '%SMS%' THEN 
	1
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} \\