dbo.sp_procesos_cuit_enrichment_mas_datos_ant

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- alter table procesos_cuit_enrichment_mas add emails varchar(max) null
-- sp_procesos_cuit_enrichment_mas_datos '8888','2018-05-15',1
CREATE OR ALTER PROCEDURE dbo.sp_procesos_cuit_enrichment_mas_datos_ant  
@id_cliente int, 
@fecha_proceso date,
@con_sueldo bit = 0
AS
BEGIN
    SET NOCOUNT ON;
 
 declare @r_cuit varchar(20)
 declare @nombre varchar(200)
 declare @nrodoc varchar(10)
  
 declare @cod_clae varchar(10), 
   @descripcion varchar(512), 
   @fecha_inicio_actividad date,
   @principal bit,
   @tipo varchar(max), 
   @tel varchar(50),
   @email varchar(200),
   @tel_formateado varchar(50)

 declare @respuesta table (  cuit varchar(20),
        nombre varchar(200),
                             cod_clae varchar(10), 
        descripcion varchar(512))
    declare @telefonos table (  cuit varchar(20),
        nombre varchar(200),
        telefono varchar(50),
        telefono_formateado varchar(50))

 declare @emails table (  cuit varchar(20),
        nombre varchar(200),
        email varchar(200))

 declare @empleadores table (cuit varchar(20),
        nombre varchar(200),
                             cuit_empleador varchar(20), 
        nombre_empleador varchar(200),
        mes varchar(7),
        sueldo decimal(20,2))
 declare @cuit_empleador varchar(20), 
      @nombre_empleador varchar(200), 
   @periodo int,
   @periodo_char varchar(7),
   @sueldo decimal(20,2)
 declare @esta int
 declare @actividades varchar(max)
 declare @empleadores_todos varchar(max)
 declare @tel_todos varchar(max)
 declare @email_todos varchar(max)

 declare @domicilio varchar(200), 
         @localidad varchar(100), 
   @provincia varchar(100), 
   @codpos    varchar(10)                

 declare @hay char(1), @emp char(1)
 
 delete procesos_cuit_enrichment_mas
  where cliente = @id_cliente 
    and fecha_proceso = @fecha_proceso
  
 DECLARE bn2 CURSOR

 
  FOR
  SELECT distinct cuit
    FROM procesos_cuit
  where cliente = @id_cliente
      and fecha = @fecha_proceso

  OPEN bn2 
  Fetch next from bn2 INTO  @r_cuit
  
  WHILE (@@FETCH_STATUS <> -1 )
 
  BEGIN 

   set @nombre = (select top 1 nombre_persona from padrones where identificacion = @r_cuit and habilitado = 1)
   set @nrodoc = (select top 1 convert(int,cuerpo_clave) from padrones where identificacion = @r_cuit and habilitado = 1 and identificacion like '2%')
  
  -- Clae 
  -- traemos los nuevos que se compleatareon desde afip que son los que tienen completo el campo formulario, si no encontramos ninguno trae los que tenga...
   DECLARE clae2 CURSOR
   FOR
     select distinct
      right('000000' + rtrim(replace(clae.cod_clae,'"','')),6) as cod_clae,   
      clae.descripcion, 
      principal
      from clae
     where clae.cuit = @r_cuit
       and clae.visible = 1
       and clae.descripcion is not null
       and clae.formulario is not null -- son las que se cargaron actualizadas de afip
           
       union 

     select distinct
      right('000000' + rtrim(replace(clae.cod_clae,'"','')),6) as cod_clae,   
      clae.descripcion, 
      principal
      from clae
     where clae.cuit = @r_cuit
       and clae.visible = 1
       and clae.descripcion is not null
       and clae.formulario is null 
       and not exists (select * from clae clae2 where clae2.cuit = clae.cuit and clae2.visible = 1 and clae2.formulario is not null) -- son las que se cargaron actualizadas de afip


     order by principal desc


   OPEN clae2 
   set @hay = 'N'
   FETCH next from clae2 INTO @cod_clae, @descripcion, @principal
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
        set @hay = 'S'
     set @esta = 0
     set @esta = (select count(*) from @respuesta where cuit = @r_cuit and nombre = @nombre and cod_clae = @cod_clae)
     if (@esta = 0)
     begin
      insert into @respuesta 
      values (@r_cuit, 
              @nombre, 
        @cod_clae,
        @descripcion)
        end
     FETCH next from clae2 INTO @cod_clae, @descripcion, @principal
    END
   CLOSE clae2
   DEALLOCATE clae2 
   if (@hay = 'N')  
    begin
     insert into @respuesta 
        values (@r_cuit, 
         @nombre, 
         '',
         '')
   end 
    
   -- emails 
   DECLARE mail2 CURSOR
   FOR
       select distinct email 
       From emails d
    where id_padron = (select top 1 id_padron from padrones
         WHERE padrones.identificacion = @r_cuit
         and habilitado = 1)
  
   OPEN mail2 
   set @hay = 'N'
   FETCH next from mail2 INTO @email
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
        set @hay = 'S'
     set @esta = 0
     set @esta = (select count(*) from @emails where cuit = @r_cuit and email = @email)
     if (@esta = 0)
     begin
      insert into @emails
      values (@r_cuit, 
              @nombre, 
        @email)
        end
     FETCH next from mail2 INTO @email
    END
   CLOSE mail2
   DEALLOCATE mail2 
   if (@hay = 'N')  
    begin
     insert into @emails
        values (@r_cuit, 
         @nombre, 
         '')
   end 

   -- Telefonos 
   DECLARE tel2 CURSOR
   FOR
       SELECT distinct d.telefono
    FROM telefonos AS d
         inner join padrones on padrones.id_padron = d.id_padron 
      WHERE padrones.identificacion = @r_cuit
  
   OPEN tel2 
   set @hay = 'N'
   FETCH next from tel2 INTO @tel
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
        set @hay = 'S'
     set @esta = 0
     set @esta = (select count(*) from @telefonos where cuit = @r_cuit and telefono = @tel)
     if (@esta = 0)
     begin
      set @tel_formateado = null

       set @tel_formateado = (select top 1 '('+iif(len(ttt.TLD)<4,'0'+ttt.TLD,ttt.tld) +') '+PRF+'-'+SFJ
                               from TE_tasa_temp ttt where ttt.TEL_N = @tel)
      
      if (@tel_formateado is null)
      begin

      set @tel_formateado = (select top 1 '('+tpt.prefijo+') '+'15'+tpt.LINEA_SIN_PREFIJO
             from te_personal_temp tpt where tpt.LINEA = @tel)

      end 
      
      if (@tel_formateado is null)
      begin
         set @tel_formateado = @tel
      end
       
      insert into @telefonos
      values (@r_cuit, 
              @nombre, 
        @tel,
        @tel_formateado)
        end
     FETCH next from tel2 INTO @tel
    END
   CLOSE tel2
   DEALLOCATE tel2 
   if (@hay = 'N')  
    begin
     insert into @telefonos
        values (@r_cuit, 
         @nombre, 
         '',
         '')
   end 
   
   --  Empleadores

   DECLARE emp CURSOR
   FOR
     select distinct cuit, 
      nombre_persona, 
      periodo,
      remimp
      from empleados
      left join padrones on padrones.identificacion = empleados.cuit and habilitado = 1
     where cuil = @r_cuit 
       and periodo = (select MAX(periodo) from empleados where cuil = @r_cuit and periodo >= '1701')
     
   set @emp = 'N'
   OPEN emp 
   FETCH next from emp INTO @cuit_empleador, @nombre_empleador, @periodo, @sueldo
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     set @emp = 'S'
     begin
         set @periodo_char = @periodo
      set @periodo_char = '20' + SUBSTRING(@periodo_char,1,2) + '-' +SUBSTRING(@periodo_char,3,2)
      insert into @empleadores 
      values (@r_cuit, 
              @nombre, 
        @cuit_empleador, 
        @nombre_empleador,
        @periodo_char,
        @sueldo)
        end
     FETCH next from emp INTO @cuit_empleador, @nombre_empleador, @periodo, @sueldo
    END
   CLOSE emp
   DEALLOCATE emp 
   if (@emp = 'N')  
    begin
     insert into @empleadores 
      values (@r_cuit, 
              @nombre, 
        '', 
        '',
        '',
        0)
   end 
    

   Fetch next from bn2 INTO  @r_cuit

  END
  
  CLOSE bn2
  DEALLOCATE bn2 


  DECLARE res CURSOR FOR
  SELECT distinct cuit
    FROM procesos_cuit
  where cliente = @id_cliente
      and fecha = @fecha_proceso
  
   
  OPEN res 
  Fetch next from res INTO  @r_cuit 

  WHILE (@@FETCH_STATUS <> -1 )
 
  BEGIN 
  
   set @nombre = (select top 1 nombre_persona from padrones where identificacion = @r_cuit and habilitado = 1)
   set @nrodoc = (select top 1 convert(int,cuerpo_clave) from padrones where identificacion = @r_cuit and habilitado = 1 and identificacion like '2%')
   set @actividades = ''
   DECLARE res2 CURSOR
   FOR
     select cod_clae, descripcion
      from @respuesta
     where cuit = @r_cuit

   OPEN res2 
   FETCH next from res2 INTO @cod_clae, @descripcion
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@cod_clae != '')
     begin
     if (@actividades = '') 
     begin
      set @actividades = '[' + @cod_clae + '] ' + replace(@descripcion,'-',' ')
     end
     else
     begin
      set @actividades = @actividades + ' - [' + @cod_clae + '] ' + @descripcion
     end 
     end 
     FETCH next from res2 INTO @cod_clae, @descripcion
    END
   CLOSE res2
   DEALLOCATE res2 

   set @tel_todos = ''
   DECLARE rest CURSOR
   FOR
     select telefono_formateado
      from @telefonos
     where cuit = @r_cuit

   OPEN rest 
   FETCH next from rest INTO @tel
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@tel != '')
     begin
     if (@tel_todos = '') 
     begin
      set @tel_todos = @tel
     end
     else
     begin
      set @tel_todos = @tel_todos + ' / ' + @tel
     end 
     end 
     FETCH next from rest INTO @tel
    END
   CLOSE rest
   DEALLOCATE rest 

   set @email_todos = ''
   DECLARE restm CURSOR
   FOR
     select email
      from @emails
     where cuit = @r_cuit

   OPEN restm
   FETCH next from restm INTO @email
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@email != '')
     begin
     if (@email_todos = '') 
     begin
      set @email_todos = @email
     end
     else
     begin
      set @email_todos = @email_todos + ' / ' + @email
     end 
     end 
     FETCH next from restm INTO @email
    END
   CLOSE restm
   DEALLOCATE restm


   set @empleadores_todos = ''
   DECLARE res3 CURSOR
   FOR
     select cuit_empleador, nombre_empleador, mes, sueldo
      from @empleadores
     where cuit = @r_cuit

   OPEN res3 
   FETCH next from res3 INTO @cuit_empleador, @nombre_empleador, @periodo_char,@sueldo
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@periodo_char != '')
     begin
      if (@empleadores_todos = '') 
      begin
       set @empleadores_todos = '[' + @periodo_char + '] ' + replace(@cuit_empleador,'-',' ') + ' ' + replace(@nombre_empleador,'-',' ')
      end
      else
      begin
       set @empleadores_todos = @empleadores_todos + ' - ' +  replace(@cuit_empleador,'-',' ') + ' ' + replace(@nombre_empleador,'-',' ')
      end 
      if (@con_sueldo = 1)
      begin
        set @empleadores_todos = @empleadores_todos + ': Sueldo $ ' + @sueldo
      end 
     end 
     FETCH next from res3 INTO @cuit_empleador, @nombre_empleador, @periodo_char,@sueldo
    END
   CLOSE res3
   DEALLOCATE res3 
         
   -- Domicilios 
   set @domicilio = '' 
   set @localidad = ''
   set @provincia = ''
   set @codpos = ''

       SELECT top 1
            @domicilio = isnull(d.domicilio,''),
      @localidad = isnull(d.loc_nombre,''), 
      @provincia = isnull(d.prv_nombre,''), 
      @codpos = isnull(d.codpos,'')
    FROM domicilios_todos AS d
      WHERE d.cuit = @r_cuit
        AND d.tipo_domicilio = 'F'

   if (@domicilio = '')
   begin

      SELECT top 1
            @domicilio = isnull(d.domicilio,''),
      @localidad = isnull(d.loc_nombre,''), 
      @provincia = isnull(d.prv_nombre,''), 
      @codpos = isnull(d.codpos,'')
    FROM domicilios_todos AS d
      WHERE d.cuit = @r_cuit
        AND d.tipo_domicilio = 'D'
     AND d.prv_nombre is not null
     
   end 
   
   if (@domicilio = '')
   begin

      SELECT top 1
            @domicilio = isnull(d.domicilio,''),
      @localidad = isnull(d.loc_nombre,''), 
      @provincia = isnull(d.prv_nombre,''), 
      @codpos = isnull(d.codpos,'')
    FROM domicilios_todos AS d
      WHERE d.cuit = @r_cuit
        AND d.tipo_domicilio = 'D'
          
   end 
     
   declare @Cod_OS varchar(10) = null
   declare @nombre_OS varchar(200) = null
   declare @Obra_Social varchar(250) = null


   select top 1 @Cod_OS = a.CodOS,
                @Nombre_OS = OS_Nombre
   from obrassocial_activos a
   inner join obras_sociales on OS_codigo = a.CodOS
   where cuil_activo = @r_cuit

   if (@Cod_OS is null) 
   begin
    select top 1 @Cod_OS = a.codobr,
        @Nombre_OS = OS_Nombre
    from obras_sociales_beneficiarios a
    inner join obras_sociales on OS_codigo = CODOBR
    where a.cuilbe = @r_cuit
    and not exists (select * from obras_sociales_beneficiarios b
    where b.cuilbe = a.cuilbe and b.tipben < a.tipben)
   end 

   set @Obra_Social = @Cod_OS + ' - ' + @Nombre_OS

   declare @monotributo varchar(10) = null
   select @monotributo = 
    CASE monotributo
    WHEN 'NI' THEN 'NO INSCRIPTO'
    WHEN 'N'  THEN 'NO INSCRIPTO'
    WHEN 'AC' THEN 'ACTIVO'
    WHEN 'S'  THEN 'ACTIVO'
    WHEN 'EX' THEN 'EXENTO'
    WHEN 'NA' THEN 'NO ALCANZADO'
    WHEN 'XN' THEN 'EXENTO NO ALCANZADO'
    WHEN 'AN' THEN 'ACTIVO NO ALCANZADO'
    WHEN 'NC' THEN 'NO CORRESPONDE'
    ELSE substring(monotributo,1,1)
   END 
   from inscripciones  
   where inscripciones.cuit = @r_Cuit 
      and inscripciones.visible = 1
      and inscripciones.id_inscripcion = (select max(b.id_inscripcion) from inscripciones b where b.cuit = @r_Cuit and b.visible = 1)

   

   insert into procesos_cuit_enrichment_mas
   values
   (
   @id_cliente, 
   @fecha_proceso,
   @r_cuit, 
   @nrodoc, 
   @nombre, 
   @domicilio,  @localidad, @provincia, @codpos,@tel_todos, @actividades,@empleadores_todos,isnull(@Obra_Social,''),isnull(@monotributo,''),@email_todos)
   Fetch next from res INTO  @r_cuit
    
  END
  
  CLOSE res
  DEALLOCATE res 

  /*
   select @proceso as 'Nro Proceso', 
          r.cuit as 'CUIT', 
      isnull(sp.vendorid,'') as 'VendorId',
      r.nombre  as 'Nombre Prov',
      r.actividades as 'Actividades',
      r.empleadores as 'Empleadores' 
     from @resultado r
     left join screening_prov sp
      on sp.cliente = 7777
     and sp.cuit = r.cuit
     and sp.nro_proceso = @proceso
       */
  /*
   select * from procesos_cuit_enrichment_mas
   where cliente = @id_cliente 
     and fecha_proceso = @fecha_proceso
   */
   
END 
GO