dbo.obtener_sintomas_de_vitalidad

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec [obtener_sintomas_de_vitalidad] '30611397077'
exec [obtener_sintomas_de_vitalidad_DEV] '30611397077'
*/
-- =============================================
-- Author: <Samuel González>
-- Create date: <17 OCT 2017>
-- Description: <Detectar síntomas de vitalidad>
-- =============================================
CREATE OR ALTER PROCEDURE  dbo.obtener_sintomas_de_vitalidad
 -- Add the parameters for the stored procedure here
 @p_cuit varchar(30)

AS
BEGIN
 SET NOCOUNT ON;
    
    declare @vitalidad int = 0
/*
Pedido por Felix: 24/07/2018: 
Si una empresa solo está como Formulario 150 o 454 y no como 883 --> se considera que la empresa está muerta
(entonces no busca todas las demás condiciones)
*/
if (select count(*) from clae a
     where a.cuit = @p_cuit and a.cuit like '3%'
    and (a.formulario like '%454%' or a.formulario like '%150%') 
       and not exists (select * from clae b where b.cuit = a.cuit and b.formulario like '%883%')) > 0 
begin 
 select 0 as vitalidad, 'formulario' as motivo
end 

else 

begin 

    -- obtener_bancos, VERIFICAR SITUACION 1 ------------------------------------------------------------------------------------------------------------------------

    declare @p_5anios date = getdate() - (5*365)
 declare @p_2anios date = getdate() - (2*365)
 declare @p_3meses date = getdate() - (90) 
 declare @dtStart date, @dtEnd date, @dtEnd_cuit date,@periodo_maximo varchar(7),@fec_periodo_minimo varchar(11),@periodo_minimo varchar(7);
 
 select @dtEnd = (select max(fecha_informacion) from fuentes_procesadas where visible = '1' and fuente = 'cendeu')
 select @dtEnd_cuit = convert(date,isnull(max(cendeu.fec_inf),'200112')+'01') from cendeu where iden = @p_cuit  and vigente = 1
 
    if (@dtEnd_cuit > @dtEnd) 
 begin 
  set @dtEnd = @dtEnd_cuit
 end

 set @dtStart = dateadd(month,-23, @dtEnd)
 set @periodo_maximo = replace(Convert(varchar(7),@dtEnd),'-','')
 set @fec_periodo_minimo = @dtStart
 set @periodo_minimo = replace(Convert(varchar(7),@dtStart),'-','')
 set @dtStart = dateadd(month,-23, @dtEnd)
 if(@periodo_maximo is not null)
    begin
        set @fec_periodo_minimo = dateadd(MM, -23,convert(date,@periodo_maximo+'01'))
        set @periodo_minimo = convert(varchar(4),DATEPART(yyyy,@fec_periodo_minimo))+right('0'+convert(varchar(2),DATEPART(mm,@fec_periodo_minimo)),2)
    end

    set @vitalidad = (select count(*) from (
        select distinct 
         fec_inf,
         isnull(nombre_corto,nombre) as nombre,
         situacion,
         ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
     from cendeu inner join EntidadesBancarias on
     convert(int,cendeu.codigo_ent) = EntidadesBancarias.id_banco
     where iden = @p_Cuit and cendeu.fec_inf between @periodo_minimo and @periodo_maximo 
     and vigente = 1  and situacion = 1
     and not exists (select * from cendeu b where b.iden = cendeu.iden and cast(b.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b.fec_inf = cendeu.fec_inf and b.vigente = 1 and b.id_fuente = 2 and b.id_cendeu != cendeu.id_cendeu)
     and not exists (
            select * from cendeu c 
   where c.iden = cendeu.iden
   and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
   and c.fec_inf = cendeu.fec_inf
   and c.vigente = cendeu.vigente
   and (isnull(c.id_fuente,1)) = (isnull(cendeu.id_fuente,1))
   and c.id_cendeu < cendeu.id_cendeu
        )
     and not 
        (
         (cast(fec_inf+'01' as date) < @p_5anios) 
         OR
         (cast(fec_inf+'01' as date) < @p_2anios 
             AND 
             --not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
             not exists (select * from cendeu z where z.iden = cendeu.iden and cast(z.codigo_ent as int) = cast(cendeu.codigo_ent as int) and (convert(date,z.created) > @p_3meses))
            )
         OR 
         (cast(fec_inf+'01' as date) < @p_2anios 
             and 
             exists (
                    select * from cendeu_cancela c 
           where c.iden = cendeu.iden 
        and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int) 
        and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
        and c.vigente = '1'
                )
            )
     )
     union all
    
        select distinct 
         fec_inf,
         isnull(nombre_corto,nombre) as nombre,
         situacion,
         ((prestamos+particip+garotor+o_cptos+deuda_total)) as total
     from cendeu_temp inner join EntidadesBancarias on
     convert(int,cendeu_temp.codigo_ent) = EntidadesBancarias.id_banco
     where iden = @p_Cuit and cendeu_temp.fec_inf between @periodo_minimo and @periodo_maximo 
     and vigente = 1 and situacion = 1
     and not exists (select * from cendeu_temp b where b.iden = cendeu_temp.iden and cast(b.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and b.fec_inf = cendeu_temp.fec_inf and b.vigente = 1 and b.id_fuente = 2 and b.id_cendeu != cendeu_temp.id_cendeu)
     and not exists (
            select * from cendeu_temp c 
      where c.iden = cendeu_temp.iden
      and cast(c.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
      and c.fec_inf = cendeu_temp.fec_inf
      and c.vigente = cendeu_temp.vigente
      and (isnull(c.id_fuente,1)) = (isnull(cendeu_temp.id_fuente,1))
      and c.id_cendeu < cendeu_temp.id_cendeu
        )
     and not (
      (cast(fec_inf+'01' as date) < @p_5anios) 
      OR
      (cast(fec_inf+'01' as date) < @p_2anios 
      and 
      --not (convert(date,created) > @p_3meses and convert(date,modified) > @p_3meses))
      not exists (select * from cendeu_temp z where z.iden = cendeu_temp.iden and cast(z.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and (convert(date,z.created) > @p_3meses)))
      OR 
      (
                (cast(fec_inf+'01' as date) < @p_2anios)
          and 
          exists (
                    select * from cendeu_cancela c 
           where c.iden = cendeu_temp.iden 
           and cast(c.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) 
           and cast(fec_inf+'01' as date) between convert(date,c.fec_inf_desde) and convert(date,c.fec_inf_hasta)
           and c.vigente = '1'
                )
            )
     )
    ) situaciones_1)

    if (@vitalidad = 0) -- obtener_cheques, CHEQUES RECHAZADOS A NOMBRE DE LA EMPRESA ------------------------------------------------------------------------------------
    begin
        set @p_5anios = getdate() - 1825
        set @p_2anios = getdate() - 730
        DECLARE @p_1anio  smalldatetime = getdate() - 365

        -- Los Cheques que vienen a nombre de la empresa
     set @vitalidad = (SELECT COUNT(*)
        FROM
            ChequesBCRA
            LEFT JOIN padrones AS pf ON (pf.identificacion = cuit_firmante)    --info firmante
            INNER JOIN Cheques_causales ON (ChequesBCRA.id_causa = Cheques_causales.id_causa)
        WHERE (Cuit_firmante = @p_Cuit)
      AND id_cheque = (
                select max(id_cheque) from chequesBCRA b 
    where b.cuit_firmante = chequesBCRA.cuit_firmante 
    and b.nro_cheque = chequesBCRA.nro_cheque 
    and b.visible = 1
            ) 
            AND (visible = 1)
         AND 
        (
          (
              (
                  (
                   (fecha_levantamiento IS NULL) 
                   AND
                   (fecha_rechazo >= @p_5anios)
                  ) 
                  OR 
                  (
                   (
                       (fecha_levantamiento >= @p_5anios)
                       AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 15 ) 
                   ) 
                   AND 
                   (
                       ((fecha_pago IS NOT NULL) AND (fecha_levantamiento >= (GETDATE() - 2))) 
                       OR
                       (((fecha_pago IS NULL) AND (fecha_levantamiento >= (GETDATE() - 2))) AND (fecha_rechazo >= @p_5anios)) 
                   ) 
                  ) 
              ) 
              OR 
              (
                  ((fecha_levantamiento >= @p_5anios) 
                  AND (((datediff(day,fecha_rechazo,fecha_levantamiento)) >= 16 ) AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 90)) 
                  ) 
                  AND 
                  (((fecha_pago IS NOT NULL) AND (fecha_levantamiento >= @p_1anio))
               OR (((fecha_pago IS NULL) AND (fecha_levantamiento >= @p_1anio)) AND (fecha_rechazo >= @p_5anios))
                  ) 
              ) 
          )
          OR 
          (
              (
                        ((fecha_levantamiento >= @p_5anios) AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) > 90)) AND (fecha_levantamiento >= @p_2anios) 
              ) 
              AND (fecha_rechazo >= @p_5anios) 
          )
      )
        )
    end

    if (@vitalidad = 0) -- obtener_juicios_demandado_actor, JUICIOS COMO ACTOR y DEMANDADO ---------------------------------------------------------------------------------------
    begin
        declare @p_meses int = 60
        --declare @from_date date=CAST(DATEADD(month, (-1)*@p_meses, GETDATE()) AS DATE)

        set @vitalidad = (
            
          SELECT COUNT(*)       FROM juicios 
          left join tipos_causas on
              tipos_causas.id_tipo_causa = juicios.id_tipo_causa
          left join tiempo_causas on
              tiempo_causas.cau_codigo   = juicios.id_tipo_causa

          WHERE ( cuit_actor = @p_cuit or cuit_demandado = @p_cuit )
          AND   (juicios.visible = 1 and juicios.fecha_causa > GETDATE()-(isnull(tiempo,5)*365))

        )
    end

    if (@vitalidad = 0) -- obtener_comex, VERIFICAR SI TIENE DATOS DE COMEX IMPORTACIONES
    begin
        declare @max_periodo date
        set @max_periodo  = (select max(fecha_informacion) from fuentes_procesadas where fuente='comercio_exterior' and visible =1)

        set @vitalidad = (select COUNT(*)
        from comercio_exterior 
        where periodo between  DATEADD(MONTH, -24, @max_periodo ) and @max_periodo 
        and cuit = @p_cuit)
    end

    if (@vitalidad = 0) -- obtener_grafico_exportaciones, VERIFICAR SI TIENE DATOS DE EXPORTACIONES
    begin
        declare @min_periodo date
        set @max_periodo  = (select max(fecha_informacion) as fecha_informacion from fuentes_procesadas where fuente='comex_pais_rubro' and visible =1)
        set @min_periodo  = (select DATEADD(month, -24, max(fecha_informacion)) as fecha_informacion from fuentes_procesadas where fuente='comex_pais_rubro' and visible =1)

  set @vitalidad = (select COUNT(*)
  from comex_exportaciones 
  where cuit = @p_Cuit
  and fecha between @min_periodo and @max_periodo)
    end

 --Agregado 30-05-2018 Leonel Turchetto, se fija si tiene info fiscal.
  if (@vitalidad = 0) 
    begin
 declare @id_padron int
 set @id_padron = (select id_padron from padrones where identificacion = @p_cuit and habilitado = 1)
 if(@id_padron > 0)
  begin
  set @vitalidad = (select COUNT(*)
   from padron_impuestos 
   where id_padron = @id_padron
   and estado='ACTIVO')
  end
  
    end

 if (@vitalidad = 0) 
    begin
  declare @ultimo_mes_empleados date = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
  declare @periodo_empleados_hasta int = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
  declare @mes_empleados date = '2018-06-01'-- DATEADD(M,-6,@ultimo_mes_empleados) -- antes decia -2
  declare @periodo_empleados_desde int = convert(int,substring(convert(char,@mes_empleados),3,2)+substring(convert(char,@mes_empleados),6,2))
  set @vitalidad = (select COUNT(*)
  from empleados
  where cuil = @p_Cuit
  and visible = 1
  and periodo = (select max(periodo) from empleados b
   where b.cuil = @p_cuit 
   and b.visible = 1 
   and b.periodo between @periodo_empleados_desde and @periodo_empleados_hasta))
    end
 select @vitalidad as vitalidad, 'todo' as motivo
end 
END

GO