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:

1. Export Massivo ARERA
2. Riepilogo Annuo Chiamate Inbound per servizio utenti e TICSI
   1. Servizio UTENTI wait custom fields –> TBD.
3. Report Semestrale ARERA servizio utenti
4. Report Giornaliero ABs
5. Report 30 minuti ABs

2) ASM Voghera:

1. Riepilogo Annuo Chiamate Inbound Annuo ASM Voghera
2. Riepilogo Annuo ASM Voghera Credito
3. Report Semestrale ARERA ASM Voghera
4. Report Annuo Autoletture ASM Voghera

2) ASM Voghera:

1. Riepilogo Annuo Chiamate Inbound Annuo ASM Voghera
2. Riepilogo Annuo ASM Voghera Credito
3. Report Semestrale ARERA ASM Voghera
4. Report Annuo Autoletture ASM Voghera

3) Broni Stradella:

1. Riepilogo Annuo Chiamate Inbound BRST

4) ISIDATA:

1. Riepilogo Annuo Chiamate Outbound Isidata

5) Metano Nord:

1. Report Semestrale ARERA Metano Nord
2. (*TBD*) Report Annuo Chiamate Inbound per Commessa

6) Pavia Acque:

1. Export Massivo ARERA
2. Riepilogo Annuo Chiamate Inbound per Pavia Acque
3. Riepilogo Chiamate Annuo Pavia Acque per servizio Credito
4. Report Semestrale ARERA Pavia Acque
5. Report Annuo Autoletture Pavia Acque
6. Riepilogo Annuo Chiamate Outbound Pavia Acque

7) Tari:

1. Riepilogo Annuo Chiamate Inbound Annuo per Albonese, Cassolnovo, FerreraE, Gravellona, Pieve

8) Utilità:

1. Riepilogo Annuo Chiamate Inbound per Utilità
2. Report Semestrale ARERA
3. 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:

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