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:

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

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:

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

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:

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

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
1. → 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:

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

WAIT FOR CUSTOM FIELDS

Report –> Isidata Outbound

Query:

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

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}