SET TERM ^ ;

CREATE OR ALTER function FN_CUSTO_MINUTO (
    ESTABELECIMENTO_ID integer,
    ANO integer,
    MES integer,
    TIPO_CUSTO integer,
    QTD_UPS integer,
    MINUTOS integer,
    CCUSTO varchar(10))
returns numeric(18,8)
AS
declare variable vvalor numeric(18,8);
declare variable data1 date;
declare variable data2 date;
begin

     data1 = dateadd(month, -11, cast(fn_start_of_month('01.'||MES||'.'||ANO) as date));
     data2 = cast(FN_END_OF_MONTH('01.'||MES||'.'||ANO) as date);

     --exception "..." data1||' - '||data2;

     if (TIPO_CUSTO = 1) then
         Begin

          vValor = coalesce((
                Select Sum(A.VALOR) / (sum(a.MINUTOS) * 1.00000)
                  From TBCENTRO_DE_CUSTO CC,
                      (Select ESTABELECIMENTO_ID, ANO, MES,
                              COLABORADOR_ID, CCUSTO,
                              IIF(COLABORADOR=1, :MINUTOS ,0) MINUTOS,
                              COLABORADOR, VALOR
                         From VWFOLPAG a
                         Where cast(fn_start_of_month('01.'||A.Mes||'.'||A.Ano) as date) between :data1 and :data2) A
                 Where A.Estabelecimento_id = :ESTABELECIMENTO_ID
                   and A.CCusto = CC.Codigo
                   and A.CCusto like Replace(:CCusto,'*','%')
                   and CC.TIPO_CUSTO = :TIPO_CUSTO),0);

         End else

     if (TIPO_CUSTO = 2) then
         Begin

         vValor =
         coalesce((Select Sum(A.VALOR)
            From TBCENTRO_DE_CUSTO CC,
                  (Select ESTABELECIMENTO_ID, ANO, MES,
                          COLABORADOR_ID, CCUSTO,
                          COLABORADOR, VALOR
                     From VWFOLPAG a
                     Where cast(fn_start_of_month('01.'||A.Mes||'.'||A.Ano) as date) between :data1 and :data2) A
             Where A.Estabelecimento_id = :ESTABELECIMENTO_ID
               and A.CCusto = CC.Codigo
               and A.CCusto like Replace(:CCusto,'*','%')
               and CC.TIPO_CUSTO = :TIPO_CUSTO),0)
         +
         coalesce((SELECT
                    Sum(d.valor)
                from vwrateamento_detalhe d
                where d.ccusto like Replace(:CCusto,'*','%')
                and d.grupo_rateamento = 1
                and cast(fn_start_of_month('01.'||d.Mes||'.'||d.Ano) as date) between :data1 and :data2
                and d.origem_tipo = 'CONTA'),0);


         vValor = vValor / :Minutos;

         End else

     if (TIPO_CUSTO = 3) then
         Begin
             vValor = coalesce((Select Sum(A.Absorvido) / (:Minutos * 1.00000)
                   From TBRATEAMENTO_HISTORICO A
                  Where a.rateamento_grupo = 1
                  and cast(fn_start_of_month('01.'||A.Mes||'.'||A.Ano) as date) between :data1 and :data2
                  and A.CCusto like Replace(:CCusto,'*','%')),0);
         End

         RETURN vValor;

end
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPC_MINUTOS_DISPONIVEIS (
    FAMILIA_ID integer,
    GP_ID varchar(10),
    PERFIL_UP varchar(10),
    MES integer,
    ANO integer)
returns (
    ESTACOES integer,
    DIAS integer,
    MINUTOS integer,
    MINUTOS_DIA integer,
    MINUTOS_NOITE integer,
    TOTAL_MINUTOS_DIA integer,
    TOTAL_MINUTOS_NOITE integer)
as
declare variable DATA_INICIO date;
declare variable DATA_FIM date;
begin

  data_inicio = Cast('01.'||mes||'.'||ano as Date);
  data_fim    = Cast(FN_END_OF_MONTH(Data_Inicio) as Date);
  data_inicio = dateadd(month, -11, cast(fn_start_of_month('01.'||MES||'.'||ANO) as date));

  --exception "..." data_inicio||' - '||data_fim;
for
Select
    ESTACOES,
    DIAS,
    MINUTOS,
    MINUTOS_DIA,
    MINUTOS_NOITE,
    (MINUTOS_DIA * DIAS) / 12.00 TOTAL_MINUTOS_DIA,
    (MINUTOS_NOITE * DIAS) / 12.00 TOTAL_MINUTOS_NOITE
From (
    Select
        COUNT(ESTACAO_ID) ESTACOES, TRUNC(AVG(DIAS)) DIAS, SUM(MINUTOS) MINUTOS,
        (Select First 1 Cast(Coalesce(N.Valor_Ext,'528') as Integer) From TbControle_N N Where Id = 108) Minutos_Dia,
        (Select First 1 Cast(Coalesce(N.Valor_Ext,'486') as Integer) From TbControle_N N Where Id = 109) Minutos_Noite
    From (
        Select GP_ID, GP_DESCRICAO, UP_ID, UP_DESCRICAO, ESTACAO_ID, Count(DATA) Dias, Sum(MINUTOS) MINUTOS
        From (
            Select Distinct
                C.GP_ID,
                E.Descricao Gp_Descricao,
                C.Up_Id,
                U.Descricao Up_Descricao,
                U.Perfil,
                C.Estacao_Id,
                CE.Data,
                CE.Minutos
              From TBCALENDARIO_ESTACAO C, TbSetor U, TbEsteira E, TbCalendario_Esteira CE
            Where C.DATA Between :data_inicio and :data_fim
               and C.Up_Id = U.Codigo
               and E.Codigo = C.Gp_Id
               and CE.Esteira_id = C.GP_ID
               and C.Data = CE.Data
               and CE.Minutos > 0
               and E.Familia_Codigo = :Familia_Id
               and C.Gp_Id like Replace(:Gp_Id,'*','%')
               and U.Perfil like Replace(:Perfil_Up,'*','%') 
        ) Group By GP_ID, GP_DESCRICAO, UP_ID, UP_DESCRICAO, ESTACAO_ID
    )
)
  into
    :ESTACOES,
    :DIAS,
    :MINUTOS,
    :MINUTOS_DIA,
    :MINUTOS_NOITE,
    :TOTAL_MINUTOS_DIA,
    :TOTAL_MINUTOS_NOITE

  do
  begin
  post_event 'Connection attempt by blocked user.';
    suspend;
  end
end
^

SET TERM ; ^

