SET TERM ^ ;

CREATE OR ALTER procedure SPC_PARADA_ESTACAO (
    I_GP_ID integer,
    I_UP_ID integer,
    I_ESTACAO integer,
    I_DATA_INICIO timestamp,
    I_DATA_FIM timestamp)
returns (
    TEMPO_PARADO integer,
    MOTIVO_PARADA varchar(100),
    INICIADO varchar(20),
    PARADO varchar(20),
    ESTACAO integer,
    TIPO integer,
    PROGRAMACAO_ID integer,
    REQUISICAO_ID integer,
    TEMPO_PARADO_EM_TALAO integer,
    TEMPO_PARADO_EXTRA integer)
as
begin

  for
        select
        
            gg.*,
            (gg.TEMPO_PARADO - gg.TEMPO_PARADO_EM_TALAO) as TEMPO_PARADO_EXTRA
    
        
        from(
    
        select
        
            jj.*,
        
            coalesce((select
                   sum(coalesce(TEMPO_PARADO,0)) as TEMPO_PARADO
                from(
                    select
                        iif((y.STATUS1 = 1) and (y.STATUS2 = 0) AND (rec = 0), (select sum(cast(PRODUTIVO as integer)) from spc_calendario_estacao(up_id,estacao_id,y.datahora1,y.datahora2)),0) as TEMPO_PARADO
                    from
                    (
                        SELECT
                            lead(x.status) over (order by x.datahora ascending) STATUS2,
                            lead(x.datahora) over (order by x.datahora ascending) datahora2,
                            iif((lag(X.requisicao_id) over (order by X.datahora ascending) = X.requisicao_id),0,1) AS rec,
                            x.status as status1,
                            x.datahora as datahora1,
                            u.up_id,
                            u.estacao as estacao_id,
                            u.gp_id
                        FROM tbprogramacao_registro x,
                             tbprogramacao u
                
                        WHERE u.id = jj.PROGRAMACAO_ID
                        and x.requisicao_id = jj.requisicao_id
                
                        AND x.status in (0,1)
                        and u.id = x.programacao_id
                        order by x.datahora ascending
                    ) y
                )z),0) AS TEMPO_PARADO_EM_TALAO
        
        from(
            select
                TEMPO_PARADO,
                MOTIVO_PARADA,
                iif(DATAHORA2 is null, null, formatdatetime(DATAHORA2)) as INICIADO,
                formatdatetime(DATAHORA1) as PARADO,
                ESTACAO,
                tipo,
            
                coalesce((SELECT first 1 x.programacao_id
                        FROM tbprogramacao_registro x, tbprogramacao s
                        WHERE x.datahora between dateadd(minute,-2,xx.DATAHORA1) and dateadd(minute,2,xx.DATAHORA2)
                        AND x.status = 1
                        and s.gp_id = xx.gp_id
                        and s.up_id = xx.up
                        and s.estacao = xx.estacao
                        and x.programacao_id = s.id
                        order by x.datahora ascending
            
                    ),0) AS PROGRAMACAO_ID,
            
                    coalesce((SELECT first 1 x.requisicao_id
                        FROM tbprogramacao_registro x, tbprogramacao s
                        WHERE x.datahora between dateadd(minute,-2,xx.DATAHORA1) and dateadd(minute,2,xx.DATAHORA2)
                        AND x.status = 1
                        and s.gp_id = xx.gp_id
                        and s.up_id = xx.up
                        and s.estacao = xx.estacao
                        and x.programacao_id = s.id
                        order by x.datahora ascending
            
                    ),0) AS requisicao_id
            
            from(
                select
            
                    iif((y.STATUS1 > 0) and (y.STATUS2 = 0), (select sum(cast(PRODUTIVO as integer))
                    from spc_calendario_estacao(y.up,y.estacao,y.datahora1,y.datahora2)),0) as TEMPO_PARADO,
                
                    y.*
                
                from(
                    select
                    
                        j.descricao as MOTIVO_PARADA,
                        lead(p.status) over (order by p.tabela_id,p.ID ascending) STATUS2,
                        lead(p.hora_registro) over (order by p.tabela_id,p.ID ascending) datahora2,
                        p.status as status1,
                        p.hora_registro as datahora1,
                        p.subvinculo_id as up,
                        p.tabela_id  as estacao,
                        p.vinculo_id as gp_id,
                        --p.*,
                        j.tipo
                    
                    from
                        tbregistro_parada p,
                        tbjustificativa j
                    where true
                    
                        and p.tabela = 'ESTACAO'
                        and p.vinculo_id = :I_GP_ID
                        and p.subvinculo_id = :I_UP_ID
                        and p.tabela_id = :I_ESTACAO
            
                        AND p.hora_registro BETWEEN :i_data_inicio and :i_data_fim
                    
                        and j.id = p.status
                    
                        order by p.tabela_id,p.ID
            
                ) y
            ) xx where xx.STATUS1 > 0 and xx.TEMPO_PARADO > 0 and xx.tipo = '1'
        ) jj
    )gg
  into
    :TEMPO_PARADO,
    :MOTIVO_PARADA,
    :INICIADO,
    :PARADO,
    :ESTACAO,
    :TIPO,
    :PROGRAMACAO_ID,
    :REQUISICAO_ID,
    :TEMPO_PARADO_EM_TALAO,
    :TEMPO_PARADO_EXTRA
  do
  begin

     suspend;
  end


end
^

SET TERM ; ^

