CREATE OR REPLACE FUNCTION FUN_ASDK_SPECIALIST_TIME (
       p_i_with_group IN NUMBER,
       p_i_case_id IN NUMBER,
       p_i_user_id IN NUMBER,    -- Si la variable @i_with_group es 0 entonces aqui se debe enviar el id del especialista, si es 1 entonces aqui se debe emviar el ID del grupo especialista
       p_i_with_calendar IN NUMBER -- 0= Calculo sin Calendario   1= Calculo con Calendario   2= Calculo teniendo en cuenta estados y calendario
) RETURN NUMBER AS
       v_Tiempo number; 
       v_Tiempo2 number;
       v_cnt_serv number;
       v_cnt_inci number;
       v_cnt_chan number;
    

--SOLO ESPECIALISTAS
--SIN CALENDARIO
       BEGIN
       IF(p_i_with_calendar=0 and p_i_with_group=0 )
       THEN

       --v_Codreceptor NUMBER;
            


             with Tpe as( 
             select rohi_id,achi_item_id, rohi_old_group, rohi_new_group,  rohi_old_user, rohi_new_user, achi_created,ROW_NUMBER() OVER(ORDER BY rohi_id) ord 
             from asdk_router_hist
             join asdk_action_hist on asdk_router_hist.rohi_action_hist_id = asdk_action_hist.achi_id 
             where achi_item_id= p_i_case_id --ID Caso
             )
             
             select ROUND(SUM(TMP2.Tiempo)) into v_Tiempo
             from(
                    select  NVL(SUM((tmp.fin -tmp.ini) * 1440),0) Tiempo
                    from(
                           select A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, A.achi_created ini,
                           CASE WHEN NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) is null then NVL(B.achi_created,SYSDATE) else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord-1
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id
                           )tmp
                    WHERE tmp.rohi_new_user=p_i_user_id --ID Usuario
                union all

                    -- Aqui se tiene en cuenta si el usuario fue al que se le asigno inicialmente el caso
                    select  NVL(SUM((tmp.fin -tmp.ini) * 1440),0) Tiempo
                    from(
                           select A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, 
                           CASE WHEN NVL(NVL(serv_codreceptor,inci_codreceptor),chan_codreceptor) = p_i_user_id --ID Usuario
                           then NVL(NVL(serv_opened_date,inci_opened_date),chan_opened_date) else null end ini
                           ,CASE WHEN B.achi_created is not null then B.achi_created else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id where rownum <= 1
                           )tmp
                    WHERE tmp.rohi_old_user=p_i_user_id--ID Usuario

                    group by tmp.achi_item_id
             )tmp2;
             
                 select count(*) into v_cnt_serv from asdk_service_call where serv_id = p_i_case_id --ID Caso
                    and serv_codreceptor=p_i_user_id;
                    
                    select count(*)into v_cnt_inci from asdk_incident where inci_id = p_i_case_id --ID Caso
                            and inci_codreceptor=p_i_user_id;
                            
                    select count(*)into v_cnt_chan from asdk_change where chan_id = p_i_case_id --ID Caso
                            and chan_codreceptor=p_i_user_id;
                
                 IF(v_Tiempo=0 and v_cnt_serv >0 )
                THEN
                           select NVL(serv_colsed_date,sysdate)- serv_opened_date into v_Tiempo2    from asdk_service_call where serv_id=p_i_case_id;
                           v_Tiempo := v_Tiempo2; 

                 ELSIF (v_Tiempo=0 and v_cnt_inci>0 )
                    THEN
                           select NVL(inci_colsed_date,sysdate)- inci_opened_date into v_Tiempo2    from asdk_incident where inci_id=p_i_case_id;
                           v_Tiempo := v_Tiempo2; 
                           
                 ELSIF (v_Tiempo=0 and v_cnt_chan>0 )
                    THEN
                           select NVL(chan_colsed_date,sysdate)- chan_opened_date into v_Tiempo2    from asdk_change where chan_id=p_i_case_id;
                           v_Tiempo := v_Tiempo2; 
                END IF;
                
            END IF;
            
            
     --CON CALENDARIO
       IF(p_i_with_calendar=1 and p_i_with_group=0 )
       THEN

             with Tpe as( 
                 select rohi_id,achi_item_id, rohi_old_group, rohi_new_group,  rohi_old_user, rohi_new_user, achi_created,ROW_NUMBER() OVER(ORDER BY rohi_id) ord 
                 from asdk_router_hist
                 join asdk_action_hist on asdk_router_hist.rohi_action_hist_id = asdk_action_hist.achi_id 
                 where achi_item_id= p_i_case_id --ID Caso
             )

             select ROUND(NVL(SUM(TMP2.Tiempo),0)) into v_Tiempo
             from(
                    select  SUM( DISTINCT
                           FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
                           tmp.ini,
                           tmp.fin )

                           )/60 Tiempo
                    from(
                           select A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, A.achi_created ini,
                           CASE WHEN NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) is null then NVL(B.achi_created,SYSTIMESTAMP) else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord-1
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id
                           )tmp
                           left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
                           left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
                            join asdk_service on asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID 
											   or asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID
											   or ASDK_INCIDENT.inci_service_id = asdk_service.FL_INT_SERVICE_ID
                    WHERE tmp.rohi_new_user=p_i_user_id --ID Usuario

                    union all

                    -- Aqui se tiene en cuenta si el usuario fue al que se le asigno inicialmente el caso
                    select  ROUND(NVL((SUM( DISTINCT
                           FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
                           tmp.ini,
                           tmp.fin )
                           )/60 ),0))  Tiempo
                    from(
                           select  A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, 
                           CASE WHEN NVL(NVL(serv_codreceptor,inci_codreceptor),chan_codreceptor) = p_i_user_id --ID Usuario
                           then NVL(NVL(serv_opened_date,inci_opened_date),chan_opened_date) else null end ini
                           ,CASE WHEN B.achi_created is not null then B.achi_created else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id
                           WHERE ROWNUM=1
                           )tmp
                    left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
                    left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
                    left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
                    join asdk_service on asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID or asdk_service_call.serv_service_id =  asdk_service.FL_INT_SERVICE_ID or 
                                                       asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID 
                    WHERE tmp.rohi_old_user=p_i_user_id--ID Usuario
                    

                    group by tmp.achi_item_id
             )tmp2;

                    select count(*) into v_cnt_serv from asdk_service_call where serv_id = p_i_case_id --ID Caso
                            and serv_codreceptor=p_i_user_id;
                    
                    select count(*)into v_cnt_inci from asdk_incident where inci_id = p_i_case_id --ID Caso
                            and inci_codreceptor=p_i_user_id;
                            
                    select count(*)into v_cnt_chan from asdk_change where chan_id = p_i_case_id --ID Caso
                            and chan_codreceptor=p_i_user_id;

             IF(v_Tiempo=0 and v_cnt_serv >0 )
             THEN
                           select FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
                           serv_opened_date,
                           NVL(serv_colsed_date,systimestamp))/60 into v_Tiempo2
                           from asdk_service_call join asdk_service on  
                           asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID  where serv_id=p_i_case_id;
                           
                           v_Tiempo := v_Tiempo2; 

             ELSIF(v_Tiempo=0 and v_cnt_inci>0 )
             THEN
                           select  FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
                           inci_opened_date,
                           NVL(inci_colsed_date,systimestamp))/60 into v_Tiempo2  from asdk_incident join asdk_service on  
                           asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID  where inci_id=p_i_case_id;
                           
                           v_Tiempo := v_Tiempo2; 

             ELSIF(v_Tiempo=0 and v_cnt_chan>0 )
             THEN
                           select  FUN_ASDK_TOTAL_TIME(asdk_service.fl_int_calendar_id,
                           chan_opened_date,
                           NVL(chan_colsed_date,systimestamp))/60 into v_Tiempo2         from asdk_change join asdk_service on  
                           asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID  where chan_id=p_i_case_id;

                           v_Tiempo := v_Tiempo2; 
             END IF;

       END IF;
       
       --CON CALENDARIO Y ESTADOS
       IF(p_i_with_calendar=2 and p_i_with_group=0 )
       THEN

       with Tpe as( 
             select rohi_id,achi_item_id, rohi_old_group, rohi_new_group,  rohi_old_user, rohi_new_user, achi_created,ROW_NUMBER() OVER(ORDER BY rohi_id) ord from asdk_router_hist
             join asdk_action_hist on asdk_router_hist.rohi_action_hist_id = asdk_action_hist.achi_id 
             where achi_item_id= p_i_case_id --ID Caso
             )

             select NVL(SUM(TMP2.Tiempo),0) into v_Tiempo
             from(
                    select  ROUND(SUM( DISTINCT
                           FUN_ASDK_REAL_TIME (tmp.achi_item_id,tmp.ini,
                           asdk_service.fl_int_calendar_id,
                           NVL(NVL(asdk_service_call.serv_sdb_last_shot, asdk_incident.inci_sdb_last_shot),asdk_change.chan_sdb_last_shot),
                           NVL(NVL(asdk_service_call.serv_status_type, asdk_incident.inci_status_type),asdk_change.chan_status_type), 
                           NVL(NVL(asdk_service_call.serv_status_id, asdk_incident.inci_status_id),asdk_change.chan_status_id))

                           )/60) Tiempo
                    from(
                           select A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, A.achi_created ini,
                           CASE WHEN NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) is null then NVL(B.achi_created,SYSTIMESTAMP) else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord-1
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id
                           )tmp
                           left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
                           left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
                             join asdk_service on asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID 
											   or asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID
											   or ASDK_INCIDENT.inci_service_id = asdk_service.FL_INT_SERVICE_ID

                    WHERE tmp.rohi_new_user=p_i_user_id --ID Usuario

                    union all

                    -- Aqui se tiene en cuenta si el usuario fue al que se le asigno inicialmente el caso
                    select ROUND (NVL((SUM( DISTINCT
                           FUN_ASDK_REAL_TIME(tmp.achi_item_id,tmp.ini,
                           asdk_service.fl_int_calendar_id,
                           tmp.fin,
                           NVL(NVL(asdk_service_call.serv_status_type, asdk_incident.inci_status_type),asdk_change.chan_status_type), 
                           NVL(NVL(asdk_service_call.serv_status_id, asdk_incident.inci_status_id),asdk_change.chan_status_id))

                           )/60),0)) Tiempo
                    from(
                           select A.rohi_id,A.achi_item_id, A.rohi_old_user, A.rohi_new_user, 
                           CASE WHEN NVL(NVL(serv_codreceptor,inci_codreceptor),chan_codreceptor) = p_i_user_id --ID Usuario
                           then NVL(NVL(serv_opened_date,inci_opened_date),chan_opened_date) else null end ini
                           ,CASE WHEN B.achi_created is not null then B.achi_created  else NVL(NVL(serv_colsed_date,inci_colsed_date),chan_colsed_date) end fin
                           from Tpe A
                           left join Tpe B on A.ord = B.ord
                           left join asdk_service_call on asdk_service_call.serv_id = A.achi_item_id
                           left join asdk_incident on asdk_incident.inci_id = A.achi_item_id
                           left join asdk_change on asdk_change.chan_id = A.achi_item_id
                           WHERE ROWNUM=1
                           )tmp
                    left join asdk_service_call on asdk_service_call.serv_id = tmp.achi_item_id
                    left join asdk_incident on asdk_incident.inci_id = tmp.achi_item_id
                    left join asdk_change on asdk_change.chan_id = tmp.achi_item_id
                    join asdk_service on asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID or asdk_service_call.serv_service_id =  asdk_service.FL_INT_SERVICE_ID or 
                                                       asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID 
                    WHERE tmp.rohi_old_user=p_i_user_id--ID Usuario
                    

                    group by tmp.achi_item_id
             )tmp2;

                    select count(*) into v_cnt_serv from asdk_service_call where serv_id = p_i_case_id --ID Caso
                            and serv_codreceptor=p_i_user_id;
                    
                    select count(*)into v_cnt_inci from asdk_incident where inci_id = p_i_case_id --ID Caso
                            and inci_codreceptor=p_i_user_id;
                            
                    select count(*)into v_cnt_chan from asdk_change where chan_id = p_i_case_id --ID Caso
                            and chan_codreceptor=p_i_user_id;

             IF(v_Tiempo=0 and v_cnt_serv>0 )
             THEN
                           select FUN_ASDK_REAL_TIME(serv_id,serv_opened_date,
                           asdk_service.fl_int_calendar_id,
                           asdk_service_call.serv_sdb_last_shot,
                           asdk_service_call.serv_status_type, 
                           asdk_service_call.serv_status_id)/60 into v_Tiempo2
                           from asdk_service_call join asdk_service on  
                           asdk_service_call.serv_service_id = asdk_service.FL_INT_SERVICE_ID  where serv_id=p_i_case_id;
                           
                           v_Tiempo := v_Tiempo2; 

             ELSIF(v_Tiempo=0 and v_cnt_inci>0 )
             THEN
                           select  FUN_ASDK_REAL_TIME(inci_id,inci_opened_date,
                           asdk_service.fl_int_calendar_id,
                           asdk_incident.inci_sdb_last_shot,
                           asdk_incident.inci_status_type, 
                           asdk_incident.inci_status_id)/60 into v_Tiempo2
                           from asdk_incident join asdk_service on  
                           asdk_incident.inci_service_id = asdk_service.FL_INT_SERVICE_ID  where inci_id=p_i_case_id;
                           
                           v_Tiempo := v_Tiempo2; 

             ELSIF(v_Tiempo=0 and v_cnt_chan>0 )
             THEN
                           select  FUN_ASDK_REAL_TIME(chan_id,chan_opened_date,
                           asdk_service.fl_int_calendar_id,
                           asdk_change.chan_sdb_last_shot,
                           asdk_change.chan_status_type, 
                           asdk_change.chan_status_id)/60 into v_Tiempo2          
                           from asdk_change join asdk_service on  
                           asdk_change.chan_service_id = asdk_service.FL_INT_SERVICE_ID  where chan_id=p_i_case_id;

                           v_Tiempo := v_Tiempo2; 
             END IF;
       END IF;
           
       RETURN v_Tiempo;
END;