CREATE OR ALTER PROCEDURE SPC_PARADAS_ESTACAO_V2 (
    i_gp_id integer,
    i_up_id integer,
    i_estacao varchar(100),
    i_data_inicio date,
    i_data_fim date,
    i_flag integer)
returns (
    tempo_parado integer,
    motivo_parada varchar(100),
    iniciado varchar(20),
    parado varchar(20),
    estacao integer,
    marcar integer,
    tipo integer,
    data_hora timestamp,
    operador varchar(100),
    estacao_descricao varchar(100),
    data_inicio timestamp,
    data_fim timestamp)
as
declare variable data1 timestamp;
declare variable data2 timestamp;
declare variable ultimo_registro timestamp;
declare variable data3 timestamp;
begin

    for
        select
            cast(cast(:i_data_inicio as date) ||' '|| (select first 1 tt.hora_inicio from tbturno_producao tt where tt.dia_semana = extract(weekday from cast(:i_data_inicio as date))) as timestamp) as DATA1,
            cast(dateadd(day, 1 ,cast(:i_data_fim as date)) ||' '|| (select first 1 dateadd(second,-1,tt.hora_inicio) from tbturno_producao tt where tt.dia_semana = extract(weekday from cast(:i_data_inicio as date))) as timestamp) as DATA2,
            cast((select first 1 k.data_producao from tbturno_producao k where k.turno_corrente = 1) ||' '|| (select first 1 tt.hora_inicio from tbturno_producao  tt where tt.dia_semana = extract(weekday from cast(:i_data_inicio as date))) as timestamp) as DATA3
        from rdb$database
    into
        :data1,
        :data2,
        :data3
    do begin end

    if(:I_FLAG = 1)then
    begin
        :data1 = :data3;
        :data2 = dateadd(day,1,dateadd(minute,-1,:data3));
    end

  for
    select * from split(:I_ESTACAO,',')
  into
    :ESTACAO
  do
  begin
     :ultimo_registro = (select first 1 r.datahora_realizado_fim from tbprogramacao_requisicao r, tbprogramacao c
                    
                            where r.datahora_fim <= :data1
                            and r.datahora_inicio >= dateadd(day,-3,:data1)
                            and c.id = r.programacao_id
                            and c.estacao = :ESTACAO
                            and c.gp_id   = :i_gp_id
                            and c.up_id   = :i_up_id
                            
                            order by r.datahora_fim desc);

     if(:ultimo_registro is null)then begin :ultimo_registro = :data1; end

     DATA_HORA = :ultimo_registro;

     for
    
            select
            
                jj.*
            
            from(
                select
                    TEMPO_PARADO,
                    MOTIVO_PARADA,
                    iif(DATAHORA2 is null, null, formatdatetime(DATAHORA2)) as INICIADO,
                    formatdatetime(DATAHORA1) as PARADO,
                    ESTACAO,
                    MARCAR,
                    tipo,
                    OPERADOR,
                    ESTACAO_DESCRICAO
                
                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.marcar,
                            j.tipo,
                            o.codigo||' - '||o.nome as OPERADOR,
                            e.descricao as ESTACAO_DESCRICAO
                        
                        from
                            tbregistro_parada p,
                            tbjustificativa j,
                            tboperador o,
                            tbsub_up e

                        where p.tabela = 'ESTACAO'
                            and p.vinculo_id = :I_GP_ID
                            and p.subvinculo_id = :I_UP_ID
                            and p.tabela_id = :ESTACAO

                            and e.up_id = p.subvinculo_id
                            and e.id = p.tabela_id

                            and o.codigo = p.operador_id
                
                            AND p.hora_registro BETWEEN :DATA_HORA and :DATA2
                        
                            and j.id = p.status
                        
                            order by p.tabela_id,p.ID
                
                    ) y
                ) xx where xx.STATUS1 > 0 and xx.TEMPO_PARADO > 0
            ) jj
      into
        :TEMPO_PARADO,
        :MOTIVO_PARADA,
        :INICIADO,
        :PARADO,
        :ESTACAO,
        :MARCAR,
        :TIPO,
        :OPERADOR,
        :ESTACAO_DESCRICAO
      do
      begin

         :DATA_INICIO = :DATA1;
         :DATA_FIM    = :DATA2;
    
         suspend;
    
      end

  end


end;

