dbo.sp_procesos_bdd_fisicas2

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

/*
select      Cuit as 'CUIT' , sueldo, 
Nombre as 'Nombre',
Nro_documento as 'Nro. Documento',
Sexo as 'Sexo',
CONVERT(varchar(10),fecha_nacimiento,103) as 'Fecha Nacimiento',
Edad as 'Edad',
Empleador as 'Empleador',
Actividades as 'Actividad',
Domicilio as 'Domicilio',   
'' as 'Telefonos',
convert(int,Monto_sit1) as 'Monto Sit 1 Ultimo mes',  
Bancos as 'Bancos Sit 1 Ultimo mes',
score as 'Score'
from procesos_bdd_fisicas
where cliente = 1 and fecha_proceso = '2017-11-30'
and sueldo > 10000
*/
/*
INGRESOS ENTRE 4 mil y 15 mil POR MES:
- nombre y apellido, 
- CUIT/CUIL, 
- DNI, 
- fecha de nacimiento, 
- edad, 
- actividad, 
- sexo, 
- domicilio, 
- todos los teléfonos q haya si hay, 
- monto sit 1 último mes, 
- bancos, 
- empleador, 
o categoría monotributo, 
o jubilado, 
score
*/

CREATE OR ALTER PROCEDURE dbo.sp_procesos_bdd_fisicas2
-- 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 = 2
declare @fecha_proceso date = convert(date, getdate())
/* */
declare @p_Cuit varchar(20)
declare @p_cuit_empleador varchar(20)
declare @p_periodo int 
declare @empleador varchar(200)
declare @periodo date   
declare @periodo_am varchar(6)
declare @cant1 int, @cant2 int, @cant3 int, @cant4 int
declare @monto_sit1 decimal(10,2)
declare @bancos varchar(max)
declare @banco  varchar(200)
declare @nombre_y_apellido varchar(100)
declare @fecha_nacimiento  date
declare @fecha_nacimiento_dmy varchar(10)
declare @edad int 
declare @total int = 0
declare @sexo  char(1)
declare @dni   varchar(10)
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 @sueldo decimal(12,2)
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 top 20000 periodo, cuit, cuil FROM empleados_temp a 
  where a.periodo = '1709' -- convert(int,@periodo_am)
  --and a.remtot between 4000 and 15000
  and cuil in (select cuit from cuits_nombres)
    
OPEN recorre1 
Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit

WHILE (@@FETCH_STATUS <> -1 )
BEGIN
  set @nombre_y_apellido = null
  set @fecha_nacimiento = null
  set @fecha_nacimiento_dmy  = null
  set @edad    = null
  set @empleador   = null
  set @bancos    = null
  set @monto_sit1   = null
  set @sexo    = null
  set @dni    = null
  set @actividades  = null
  set @domicilio_completo = null
  set @telefono_completo = null
  set @sueldo = null
  
  if (select count(*) from empleados_temp b
   where b.cuil  = @p_cuit 
     and b.cuit != @p_cuit_empleador 
        and b.periodo = @p_periodo) > 0 
     begin
    -- tiene otro empleador
       Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
    continue
     end 

  set @sueldo = (select top 1 remtot from empleados_temp b
   where b.cuil = @p_cuit 
     and b.cuit = @p_cuit_empleador 
        and b.periodo = @p_periodo)
  
 -- 1) nombre y apellido, sexo, dni
  select @nombre_y_apellido = padrones.nombre_persona, 
         @sexo = padrones.sexo,
      @dni  = padrones.cuerpo_clave 
   from padrones 
   where identificacion = @p_cuit
     and habilitado = 1
     and identificador = 'CUIT'
  if (@nombre_y_apellido is null) 
  begin
      Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
   continue
  end 
  if (@sexo is null) 
  begin
   if (substring(@p_cuit,1,2) = '27')
   begin
       set @sexo = 'F'
   end 
   if (substring(@p_cuit,1,2) = '20')
   begin
       set @sexo = 'M'
   end 
  end 
  set @dni = convert(int,@dni)
  if (@sexo is null) 
  begin
      Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
   continue
  end 

 -- 2) fecha de nacimiento, edad, sexo
  set @fecha_nacimiento = (select c02_fecha_nac
                from DATOS_PERSONAS 
         where c01_cuit = @p_cuit
           and visible = 1)
    if (@fecha_nacimiento is null)
  begin
    Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
       continue -- si no tiene fecha nacimiento no lo muestra
  end 
  
  set @edad = DATEDIFF(yy,@fecha_nacimiento,getdate())
  if (@edad is null or  @edad > 65)
  begin
    Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
       continue -- si no tiene fecha nacimiento no lo muestra
  end 
  
  set @fecha_nacimiento_dmy = CONVERT(varchar(10),@fecha_nacimiento,103) 
    
 -- 3) Empleador 
  set @empleador = (select nombre_persona
         from padrones 
           where identificacion = @p_cuit_empleador
          and habilitado = 1
          and identificador = 'CUIT')
  if (@empleador is null) 
  begin
   Fetch next from recorre1 INTO @p_periodo, @p_cuit_empleador, @p_cuit
   continue
  end 

 -- 4) 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 

 -- 5) 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_empleador, @p_cuit
       continue -- si no tiene fecha nacimiento 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_empleador, @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_empleador, @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_empleador, @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_empleador, @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_empleador, @p_cuit
       continue
  end
  
  set @total = @total + 1
  /*
   select   @sueldo as sueldo, 
            @p_cuit as cuit, 
    @nombre_y_apellido as nombre, 
    @dni as dni, 
    @sexo as sexo, 
    @fecha_nacimiento_dmy  as fecha_nacimiento,
    @edad as edad, 
    @empleador as empleador,
    @actividades as actividades, 
    @domicilio_completo as domicilio,
    @telefono_completo as telefono, 
    @monto_sit1 as situacion1, 
    @bancos as bancos
   */
 

 insert into procesos_bdd_fisicas
       (Cliente,
     Fecha_Proceso,
     Cuit,
     Nombre,
     Nro_documento,
     sexo,
     fecha_nacimiento,
     edad,
     empleador,
     sueldo,
     actividades,
     domicilio,   
     telefonos,  
     monto_sit1,  
     bancos,     
     score)

    values

    (@id_cliente, 
     @fecha_proceso, 
        @p_cuit,
     @nombre_y_apellido,
     @dni,
     @sexo, 
     @fecha_nacimiento,
     @edad,
     @empleador,
     @sueldo,
     @actividades,
     @domicilio_completo,
     @telefono_completo,
     @monto_sit1,
     @bancos,
     null)

    if (@total >= 700)
   begin
    break 
   end
 

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

GO