dbo.sp_procesos_bdd_juridicas

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- exec sp_procesos_bdd_juridicas
-- Lo corrimos para cliente 1, fecha = '2017-12-01' --> poner lo que corresponda en el declare
-- esta tomando empleados_temp, en vez de empleados y como fecha 2017-09 !!

/*
select      Cuit as 'CUIT' ,
Nombre as 'Razon Social',
Empleados as 'Cant. Empleados',
Actividades as 'Actividad',
Domicilio as 'Domicilio',   
telefonos as 'Telefonos',
'' as 'mail',
convert(int,Monto_sit1) as 'Monto Sit 1 Ultimo mes',  
Bancos as 'Bancos Sit 1 Ultimo mes',
score as 'Score'
from procesos_bdd_juridicas
where cliente = 1 and fecha_proceso = '2017-12-15'
*/
/*
personas jurídicas con 
CUIT, 
razón social, 
código actividad,
descripción actividad, 
cantidad de empleados, 
domicilio, 
cp, 
teléfonos, 
mails si hay, 
y score
*/

CREATE OR ALTER PROCEDURE dbo.sp_procesos_bdd_juridicas
-- esta tomando empleados_temp, en vez de empleados y como fecha 2017-09 !!
AS
BEGIN
SET NOCOUNT ON;
/* cliente y fecha que corresponda */
declare @id_cliente int = 1
declare @fecha_proceso date = convert(date, getdate())
/* */
declare @p_Cuit varchar(20)
declare @p_cuit_empleado varchar(20)
declare @p_periodo int 
declare @empleados int 
declare @periodo date   
declare @periodo_am varchar(6)
  --set @periodo = (select max(fecha_informacion) from fuentes_procesadas where fuente = 'cendeu' and visible = 1)
     --set @periodo_am = right(convert(varchar(4),DATEPART(yyyy,@periodo)),4)+right('0'+convert(varchar(2),DATEPART(mm,@periodo)),2)
  --select @periodo, @periodo_am
declare @cant1 int, @cant2 int, @cant3 int, @cant4 int
declare @monto_sit1 decimal(10,2)
declare @bancos varchar(max)
declare @banco  varchar(200)
declare @razon_social varchar(100)
declare @total int = 0
declare @p_id_padron int
declare @actividades    varchar(max)
declare @actividad      varchar(200)
declare @cod_actividad  varchar(10)
declare @tipo_domicilio char(1)
declare @domicilio_completo varchar(600)
declare @domicilio varchar(200)
declare @pcia varchar(100)
declare @localidad varchar(200) 
declare @pais varchar(100) 
declare @codpos varchar(10)
declare @telefono varchar(100)
declare @telefono_completo varchar(100)
declare @error char(1)

set @periodo = (select max(fecha_informacion) from fuentes_procesadas where fuente = 'empleados' and month(fecha_informacion) not in (6,12)) -- para que no tome los meses de aguinaldo
set @periodo_am = right(convert(varchar(4),DATEPART(yyyy,@periodo)),2)+right('0'+convert(varchar(2),DATEPART(mm,@periodo)),2)

DECLARE recorre1 CURSOR FOR
 SELECT distinct top 5000 periodo, cuit FROM empleados_temp a 
  where a.periodo = '1710' -- convert(int,@periodo_am)
    and exists (select * from TE_tasa_temp where cuits = cuit)
    --and cuit like '3%'
    --and cuit like '2%'

    
OPEN recorre1 
Fetch next from recorre1 INTO @p_periodo, @p_cuit

WHILE (@@FETCH_STATUS <> -1 )
BEGIN
  set @razon_social  = null
  set @empleados   = null
  set @bancos    = null
  set @monto_sit1   = null
  set @actividades  = null
  set @domicilio_completo = null
  set @telefono_completo = null
  
 -- 1) Razon Social
  select @razon_social = padrones.nombre_persona
   from padrones 
   where identificacion = @p_cuit
     and habilitado = 1
     and identificador = 'CUIT'
  if (@razon_social is null) 
  begin
      Fetch next from recorre1 INTO @p_periodo, @p_cuit
   continue
  end 
      
 -- 2) Empleados 
  set @empleados = (select count(distinct cuil)
         from empleados_temp 
           where cuit = @p_cuit
          and periodo = @p_periodo)
  if (@empleados is null) 
  begin
   Fetch next from recorre1 INTO @p_periodo, @p_cuit
   continue
  end 

 -- 3) Actividades
  set @p_id_padron = (select id_padron from padrones where identificacion = @p_cuit)
  set @actividades = ''
  DECLARE recorre3 CURSOR FOR
     select distinct padron_actividades.codigo_actividad as cod_clae, 
      actividades.nombre_actividad as descripcion
      from padron_actividades
     inner join actividades 
     on actividades.codigo_actividad = padron_actividades.codigo_actividad
     where id_padron = @p_id_padron
  OPEN recorre3
  Fetch next from recorre3 INTO @cod_actividad, @actividad
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
       if len(@actividades) > 1 
    begin
     set @actividades = rtrim(ltrim(@actividades)) + ' | ' + rtrim(ltrim(@cod_actividad)) + ' - ' + rtrim(ltrim(@actividad))
    end 
    else
    begin 
     set @actividades = rtrim(ltrim(@cod_actividad)) + ' - ' + rtrim(ltrim(@actividad))
    end 
    Fetch next from recorre3 INTO @cod_actividad, @actividad
   END 
   CLOSE recorre3
   DEALLOCATE recorre3
  if (@actividades = '')
  begin
   set @actividades = ''
   DECLARE recorre31 CURSOR FOR
        select clae.cod_clae, clae.descripcion
         from clae
        where clae.cuit = @p_cuit
          and clae.descripcion like '(F.883)%'
   OPEN recorre31
   Fetch next from recorre31 INTO @cod_actividad, @actividad
    WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if len(@actividades) > 1 
     begin
      set @actividades = rtrim(ltrim(@actividades)) + ' | ' + rtrim(ltrim(@cod_actividad)) + ' - ' + rtrim(ltrim(@actividad))
     end 
     else
     begin 
      set @actividades = rtrim(ltrim(@cod_actividad)) + ' - ' + rtrim(ltrim(@actividad))
     end 
    Fetch next from recorre31 INTO @cod_actividad, @actividad
   END 
   CLOSE recorre31
   DEALLOCATE recorre31
  end 

 -- 4) Domicilios y telefonos
  DECLARE recorre4 CURSOR FOR
   SELECT  CASE tipo_domicilio
      WHEN 'F' THEN 1
      WHEN 'D' THEN 2
       WHEN 'L' THEN 3
      ELSE 4
     END as tipo, 
     domicilio,
     prv_nombre, 
     loc_nombre, 
     pai_nombre, 
     codpos, 
     telefono 
    FROM domicilios_todos AS d 
    WHERE d.cuit = @p_Cuit 
      and d.visible = 1
    order by tipo asc, d.id_domicilio desc
   OPEN recorre4 
   Fetch next from recorre4 INTO @tipo_domicilio,
                                 @domicilio,
            @pcia, 
            @localidad, 
            @pais, 
            @codpos, 
            @telefono
   set @domicilio_completo = null
   set @telefono_completo = null
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
       if (@domicilio_completo is null) 
    begin
        if (len(@domicilio) > 3 and (len(@localidad) > 3 or len(@pcia) > 3) and len(@codpos) > 3)
     begin
      if (len(@domicilio) > 3) 
      begin
         set @domicilio_completo = rtrim(ltrim(@domicilio))
         if (len(@localidad) > 3) 
         begin
          set  @domicilio_completo = rtrim(ltrim(@domicilio_completo)) + ', ' + rtrim(ltrim(@localidad))
         end 
         if (len(@pcia) > 3) 
         begin
          set  @domicilio_completo = rtrim(ltrim(@domicilio_completo)) + ', ' + rtrim(ltrim(@pcia))
         end 
         if (len(@pais) > 3) 
         begin
          set  @domicilio_completo = rtrim(ltrim(@domicilio_completo)) + ', ' + rtrim(ltrim(@pais))
         end 
         if (len(@codpos) > 3) 
         begin
          set  @domicilio_completo = rtrim(ltrim(@domicilio_completo)) + ', Cod. Pos.' + rtrim(ltrim(@codpos))
         end 
      end
     end
    end 
    /*
     if (len(@telefono) > 5)
     begin 
          if len(@telefono_completo) > 1 
     begin
     set @telefono_completo = rtrim(ltrim(@telefono_completo)) + ' | ' + rtrim(ltrim(@telefono))
     end 
     else
     begin 
     set @telefono_completo = rtrim(ltrim(@telefono))
     end 
     end 
     */
    Fetch next from recorre4 INTO @tipo_domicilio,
                                 @domicilio,
            @pcia, 
            @localidad, 
            @pais, 
            @codpos, 
            @telefono
   END 
   CLOSE recorre4
   DEALLOCATE recorre4
  if (@domicilio_completo is null) 
  begin
    Fetch next from recorre1 INTO @p_periodo, @p_cuit
       continue -- si no tiene domicilios no lo muestra
  end 

  -- 5.1) telefonos de la tabla te_tasa_temp
  set @telefono_completo = null

  DECLARE recorre41 CURSOR FOR
   SELECT tel_n
    FROM TE_tasa_temp  
    WHERE cuits = @p_Cuit 
   OPEN recorre41
   Fetch next from recorre41 INTO @telefono
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
     if len(@telefono_completo) > 1 
     begin
      set @telefono_completo = rtrim(ltrim(@telefono_completo)) + ' | ' + rtrim(ltrim(@telefono))
     end 
     else
     begin 
      set @telefono_completo = rtrim(ltrim(@telefono))
     end 
    
    Fetch next from recorre41 INTO @telefono
   END 
   CLOSE recorre41
   DEALLOCATE recorre41
  if (@telefono_completo is null) 
  begin
     Fetch next from recorre1 INTO @p_periodo, @p_cuit
       continue -- si no tiene TELEFONOS no lo muestra
  end 
    
    
    -- 6) que tenga situacion 1 en el ultimo mes, y no otra situacion
  set @periodo = (select max(fecha_informacion) from fuentes_procesadas where fuente = 'cendeu' and visible = 1)
     set @periodo_am = right(convert(varchar(4),DATEPART(yyyy,@periodo)),4)+right('0'+convert(varchar(2),DATEPART(mm,@periodo)),2)
  set @cant1 = 0
  set @cant2 = 0
  set @cant3 = 0
  set @cant4 = 0
  select @cant1 = (select count(*) from cendeu where iden = @p_cuit and fec_inf = @periodo_am and substring(convert(char(2),convert(int,cendeu.situacion)),1,1) ='1')
  select @cant2 = (select count(*) from cendeu_temp cendeu where iden = @p_cuit and fec_inf = @periodo_am and substring(convert(char(2),convert(int,cendeu.situacion)),1,1) ='1')
  
  select @cant3 = (select count(*) from cendeu      where iden = @p_cuit and fec_inf = @periodo_am and substring(convert(char(2),convert(int,cendeu.situacion)),1,1) != '1')
  select @cant4 = (select count(*) from cendeu_temp cendeu where iden = @p_cuit and fec_inf = @periodo_am and substring(convert(char(2),convert(int,cendeu.situacion)),1,1)  != '1')

  if (@cant1 = 0 and @cant2 = 0) 
     begin
       Fetch next from recorre1 INTO @p_periodo, @p_cuit
       continue -- si no tiene sit 1 en el ultimo mes no lo muestra
     end
     
  if (@cant3 != 0 or @cant4 != 0) --tiene otra situacion en el ultimo mes 
      begin
     Fetch next from recorre1 INTO @p_periodo, @p_cuit
        continue -- si tiene sit > 1 en el ultimo mes no lo muestra
     end
 
     if (@cant2 != 0) -- tiene en cendeu_temp
  begin

      if (select count(*) from cendeu_temp cendeu  
      where iden = @p_cuit and fec_inf = @periodo_am 
     and substring(convert(char(2),convert(int,cendeu.situacion)),1,1) = '1' 
     and not exists (select * from EntidadesBancarias where EntidadesBancarias.cod_banco = convert(int,cendeu.codigo_ent))) > 0 
     begin
      Fetch next from recorre1 INTO @p_periodo, @p_cuit
      continue -- si falta algun nombre de Banco, lo salteo
        end 
     
   set @monto_sit1 = (select sum(prestamos+particip+garotor+o_cptos+deuda_total) from cendeu_temp cendeu
         where iden = @p_cuit 
           and fec_inf = @periodo_am 
           and vigente = 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_temp 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)
              )
   set @bancos = ''
   DECLARE recorre2 CURSOR FOR
     select distinct isnull(EntidadesBancarias.nombre_corto,EntidadesBancarias.nombre) as banco
       from cendeu_temp cendeu
       inner join EntidadesBancarias on EntidadesBancarias.cod_banco = convert(int,codigo_ent)
      where iden = @p_cuit 
     and fec_inf = @periodo_am 
     and vigente = 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_temp 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)
   OPEN recorre2 
   Fetch next from recorre2 INTO @banco
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
       if len(@bancos) > 1 
    begin
     set @bancos = rtrim(ltrim(@bancos)) + ' | ' + rtrim(ltrim(@banco))
    end 
    else
    begin 
     set @bancos = rtrim(ltrim(@banco))
    end 
    Fetch next from recorre2 INTO @banco
   END 
   CLOSE recorre2
   DEALLOCATE recorre2
              
  end 
  else 
  begin 
   if (select count(*) from cendeu  
      where iden = @p_cuit and fec_inf = @periodo_am 
     and substring(convert(char(2),convert(int,cendeu.situacion)),1,1) = '1' 
     and not exists (select * from EntidadesBancarias where EntidadesBancarias.cod_banco = convert(int,cendeu.codigo_ent))) > 0 
     begin
      Fetch next from recorre1 INTO @p_periodo, @p_cuit
      continue -- si falta algun nombre de Banco, lo salteo
        end
   set @monto_sit1 = (select sum(prestamos+particip+garotor+o_cptos+deuda_total) from cendeu cendeu
         where iden = @p_cuit 
           and fec_inf = @periodo_am 
           and vigente = 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)
              )
   set @bancos = ''
   DECLARE recorre21 CURSOR FOR
     select distinct isnull(EntidadesBancarias.nombre_corto,EntidadesBancarias.nombre) as banco
       from cendeu
       left join EntidadesBancarias on EntidadesBancarias.cod_banco = convert(int,codigo_ent)
      where iden = @p_cuit 
     and fec_inf = @periodo_am 
     and vigente = 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)
   OPEN recorre21 
   set @error = 'N'
   Fetch next from recorre21 INTO @banco
   WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
    if @banco is null 
    begin
         set @error = 'S'
    end 
    if len(@bancos) > 1 
    begin
     set @bancos = rtrim(ltrim(@bancos)) + ' | ' + rtrim(ltrim(@banco))
    end 
    else
    begin 
     set @bancos = rtrim(ltrim(@banco))
    end 
    Fetch next from recorre21 INTO @banco
   END 
   CLOSE recorre21
   DEALLOCATE recorre21 
  end 
  set @monto_sit1 = @monto_sit1 * 1000
  if (@monto_sit1 is null or @bancos is null or @bancos = '')
  begin
       Fetch next from recorre1 INTO @p_periodo, @p_cuit
       continue
  end
  
  set @total = @total + 1

 insert into procesos_bdd_juridicas
       (Cliente,
     Fecha_Proceso,
     Cuit,
     Nombre,
     empleados,
     actividades,
     domicilio,   
     telefonos,  
     e_mail, 
     monto_sit1,  
     bancos,     
     score)

    values

    (@id_cliente, 
     @fecha_proceso, 
        @p_cuit,
     @razon_social,
     @empleados,
     @actividades,
     @domicilio_completo,
     @telefono_completo,
     null,
     @monto_sit1,
     @bancos,
     null)

    if (@total >= 2000)
   begin
    break 
   end
 

  Fetch next from recorre1 INTO @p_periodo, @p_cuit
END 
CLOSE recorre1
DEALLOCATE recorre1
/*
 select *
   from procesos_cuit_enrichment 
  where cliente = @id_cliente and fecha_proceso = @fecha_proceso 
*/
END

GO