dbo.sp_procesos_cuit_enrichment_simples

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec sp_procesos_cuit_enrichment_simples '1353182658', '2020-08-03', 'N','N','S','S','S','N','N','N','N',0
*/

CREATE OR ALTER PROCEDURE dbo.sp_procesos_cuit_enrichment_simples   
@cliente int =  1353182658,
@fecha_proceso date = '2020-08-05',
@retomar char(1) = 'N' ,
@buscar_datos_personales char(1) = 'S',
@buscar_domicilios char(1) = 'S', 
@buscar_telefonos char(1) = 'S',
@buscar_mails char(1) = 'S',
@buscar_dependencia char(1) = 'N',
@buscar_empleadores char(1) = 'N',
@buscar_cant_empleados char(1) = 'N',
@buscar_inicio_actividad char(1) = 'N',
@con_sueldo bit = 0
AS 
BEGIN
    SET NOCOUNT ON;

 declare @cuit varchar(20),
   @identificador varchar(10), 
   @id_padron int, 
   @nombre varchar(100),
   @dni varchar(8), 
   @ref_cliente varchar(20) = null, 
   @fecha_nac date, 
   @anio_nac int, 
   @fecha_def date, 
   @edad int

    declare @domicilios table (id int, domicilio varchar(200), pai_nombre varchar(20), prv_nombre varchar(100), loc_nombre varchar(100), codpos varchar(10), tipo_domicilio varchar(10), created datetime)
 
 declare @domicilio  varchar(200),
      @pai_nombre varchar(100), 
      @prv_nombre varchar(100), 
   @loc_nombre varchar(100),
   @codpos     varchar(10),
   @orden  int

 declare @tel varchar(max),
         @email varchar(200),
   @id int,
   @cant_dom int 

 declare @telefonos table (  cuit varchar(20),
        telefono varchar(max))
 declare @emails table ( cuit varchar(20),
       nombre varchar(200),
       email varchar(200))
 declare @email_todos varchar(max),
   @dependencia varchar(2)
 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 @hay char(1), @emp char(1)
 declare @empleadores_todos varchar(max)

 declare @empleados_cuit  table (
      hombres decimal(10,2),
    mujeres decimal(10,2),
    total   decimal(10,2),
    fecha   varchar(10))

 declare @cant_empleados int = 0
 declare @fecha_inicio_actividad date 
 

 if (@retomar = 'N')
 begin
  delete from procesos_cuit_enrichment_datos_simples where cliente = @cliente and fecha_proceso = @fecha_proceso 
 end 
 DECLARE C_padron CURSOR FOR
   select distinct a.cuit,
       b.id_padron, 
       b.identificador, 
       b.nombre_persona, 
       b.cuerpo_clave
   from procesos_cuit a
    inner join padrones b on identificacion = a.cuit and habilitado = 1 and identificador in (select identificador from identificadores where country_code = 'AR')
    where a.cliente = @cliente 
    and a.fecha = @fecha_proceso
    and not exists (select * from procesos_cuit_enrichment_datos_simples r
                     where r.cliente = @cliente and r.fecha_proceso = @fecha_proceso and r.cuit = a.cuit)
    --and exists (select * from consultar_afip c where c.cuit = a.cuit_asignado and c.procesado = 1 and proceso = 'PLAN OVALO')
   order by a.cuit
 
 OPEN C_Padron
 FETCH NEXT FROM C_Padron INTO   @cuit,
         @id_padron, 
         @identificador, 
         @nombre, @dni
           
 WHILE @@fetch_status = 0
 BEGIN 
  set @fecha_nac = null
  set @anio_nac = null
  set @edad = null
  set @fecha_def = null
    declare @defuncion char(2) = null 
if (@buscar_datos_personales = 'S') 
BEGIN
  select @anio_nac  = c.c02_anio_nac, 
         @fecha_nac = c.c02_fecha_nac,
         @edad      = (cast(datediff(dd, c.c02_fecha_nac,GETDATE()) / 365.25 as int)),
           @defuncion  = isnull(@defuncion,'0'),
      @fecha_def = c.fecha_defuncion
    from datos_personas c
   where c.c01_cuit = @cuit and c.visible = 1
  if (@edad is null and @anio_nac is not null) set @edad = convert(int, year(GETDATE())) - @anio_nac 
  if (@fecha_def is not null or @defuncion = 1) set @edad = null
    if (@defuncion = '1')
    begin 
      set @defuncion = 'SI'
    end 
    ELSE
    begin
      set @defuncion = 'NO'
    end 
END 
  --> Telefonos 
set @tel = null
DECLARE @fijotel VARCHAR(max) = null, @moviltel varchar(max) = null, @otrotel varchar(max) = null

if (@buscar_telefonos = 'S') 
BEGIN
  insert into @telefonos 
    exec sp_obtener_telefonos @cuit
  set @tel = (select telefono from @telefonos) 
  delete from @telefonos

  EXEC [sp_obtener_telefonos_tipo] @cuit,  @fijotel output, @moviltel output, @otrotel output 
  
END 
  --> emails
  set @email_todos = ''
if (@buscar_mails = 'S') 
BEGIN
  DECLARE mail2 CURSOR
   FOR
       select distinct email 
       From emails d
    where id_padron = @id_padron
    and isnull(email,'') != ''
   OPEN mail2 
   FETCH next from mail2 INTO @email
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@email_todos = '') 
     begin
      set @email_todos = @email
     end
     else
     begin
      set @email_todos = @email_todos + ' / ' + @email
     end 
     FETCH next from mail2 INTO @email
    END
   CLOSE mail2
   DEALLOCATE mail2 
   if (@email_todos = '') set @email_todos = null
END 
  --> Relacion de dependencia 2018 si/no
  set @dependencia = null

if (@buscar_dependencia = 'S') 
BEGIN
  if ((select count(*) from empleados where cuil = @cuit and periodo > '1801') > 0)
  begin
   set @dependencia = 'SI'
  end 
  else
  begin
   set @dependencia = 'NO'
  end 
END   
  insert into procesos_cuit_enrichment_datos_simples
                   (cliente,
     fecha_proceso,
     id_padron,
     identificador,
     cuit,
     nombre, 
     fecha_nac,
     edad,
     anio_nac,
     fecha_defuncion,
     defuncion,
     tel, telfijo, telmovil, telotro, 
     email,
     dependencia,
     dni,
     ref_cliente)
   values (@cliente, @fecha_proceso, @id_padron, @identificador, @cuit,
     @nombre,
     @fecha_nac,
     @edad,
     @anio_nac, 
     @fecha_def, 
     @defuncion, 
     @tel,@fijotel, @moviltel, @otrotel, 
     @email_todos,
     @dependencia,
     @dni,
     @ref_cliente)
  set @id = (SELECT @@IDENTITY)

if (@buscar_empleadores = 'S') 
BEGIN
--  Empleadores

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

   set @empleadores_todos = ''
   DECLARE res3 CURSOR
   FOR
     select cuit_empleador, nombre_empleador, mes, sueldo
      from @empleadores
     where cuit = @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 $ ' + convert(varchar(20),@sueldo)
        
      end 
     end 
     FETCH next from res3 INTO @cuit_empleador, @nombre_empleador, @periodo_char,@sueldo
    END
   CLOSE res3
   DEALLOCATE res3 
   update  procesos_cuit_enrichment_datos_simples
      set  empleadores = @empleadores_todos
    where  id = @id

END 
set @cant_empleados = null 
if (@buscar_cant_empleados = 'S') 
BEGIN
  insert into @empleados_cuit
    exec obtener_empleados @cuit
  set @cant_empleados = (select total from @empleados_cuit) 
  delete from @empleados_cuit 
  update  procesos_cuit_enrichment_datos_simples
     set  cant_empleados = @cant_empleados
   where  id = @id
END 
set @fecha_inicio_actividad = null 
if (@buscar_inicio_actividad = 'S') 
BEGIN
  select @fecha_inicio_actividad = convert(varchar(50),min(fecha_inscripcion))
     from padron_impuestos
    where id_padron = (select id_padron from padrones where identificacion = @Cuit and habilitado = 1) 
      and fecha_inscripcion != '1901-01-01'

  update  procesos_cuit_enrichment_datos_simples
     set  fecha_inicio_actividad = @fecha_inicio_actividad
   where  id = @id
END 


--> Domicilios F y D

update domicilios_todos 
 set codpos = id_fuente, id_fuente = codpos 
 where cuit = @cuit 
 and visible = 1 
 and nombre_fuente = 'MOVISTAR' and LEN(codpos) > 4

if (@buscar_domicilios = 'S') 
BEGIN
 
  insert into @domicilios
   exec sp_obtener_domicilios @cuit, 1

  if ((select count(*) from @domicilios where isnull(domicilio,'') != '') = 0)
  begin 
   -- Si no encontro domicilios de AFIP busca otros
   insert into @domicilios
   exec sp_obtener_domicilios @cuit, 0
  end 
  /*
   if (select count(*) from @domicilios where tipo_domicilio like '%[FDLE]%' and (loc_nombre is not null or prv_nombre is not null) and (isnull(prv_nombre,'') not like 'NO INFORMA%' and isnull(loc_nombre,'') not like 'NO INFORMA%')) > 0
   begin*/
   DECLARE dom CURSOR FOR
      select distinct upper(domicilio),
        upper(pai_nombre), 
        upper(prv_nombre), 
        upper(loc_nombre),
        codpos, 
        case 
          when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1' 
          when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '')  then '2' 
          when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3' 
       when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '')  then '4' 
       when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
       WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
       WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
       when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '')  then '8'
       WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '')  then '9'
       WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '')  then '10'
       else '11'
          end as orden 
      from @domicilios d
      where 
     d.domicilio not like 'RESIDE EN' 
    --and d.domicilio not like '%ZONA RURAL%'
    --and d.domicilio not like '%SIN NOMBRE%'
    and d.domicilio not like 'DOMICILIADA EN'
    order by (case 
          when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') != '') then '1' 
          when (d.tipo_domicilio LIKE '%F%' and isnull(d.codpos,'') = '')  then '2' 
          when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') != '') then '3' 
       when (d.tipo_domicilio LIKE '%L%' and isnull(d.codpos,'') = '')  then '4' 
       when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') != '') then '5'
       WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') != '') then '6'
       WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') != '') then '7'
       when (d.tipo_domicilio LIKE '%D%' and isnull(d.codpos,'') = '')  then '8'
       WHEN (d.tipo_domicilio LIKE '%O%' and isnull(d.codpos,'') = '')  then '9'
       WHEN (d.tipo_domicilio LIKE '%E%' and isnull(d.codpos,'') = '')  then '10'
       else '11'
          end)
      /*where tipo_domicilio like '%[FDLE]%' 
       and domicilio not like 'RESIDE %' and domicilio not like '% DOMICILIO%'*/       
  /* end 
   else
   begin
   DECLARE dom CURSOR FOR
   select distinct domicilio,
   pai_nombre, 
   prv_nombre,
   loc_nombre,
   codpos, '1' as orden
     from @domicilios 
     where tipo_domicilio like '%[FDLE]%'
       and domicilio not like 'RESIDE %' and domicilio not like '% DOMICILIO%'
   end 
   */
   OPEN dom
   set @cant_dom = 0 
   FETCH NEXT FROM dom INTO @domicilio,
        @pai_nombre, 
        @prv_nombre, 
        @loc_nombre,
        @codpos,
        @orden
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
    set @cant_dom = @cant_dom + 1

    if (@cant_dom = 1)
    begin
     update  procesos_cuit_enrichment_datos_simples
        set  domicilio = @domicilio,
       pai_nombre = @pai_nombre,
       prv_nombre = @prv_nombre,
       loc_nombre = @loc_nombre,
       codpos = @codpos 
      where  id = @id
    end

    if (@cant_dom = 2)
    begin
     update  procesos_cuit_enrichment_datos_simples
        set  domicilio2 = @domicilio,
       pai_nombre2 = @pai_nombre,
       prv_nombre2 = @prv_nombre,
       loc_nombre2 = @loc_nombre,
       codpos2 = @codpos 
      where  id = @id
    end 
    delete from @domicilios
    FETCH NEXT FROM dom INTO @domicilio,
          @pai_nombre, 
          @prv_nombre, 
          @loc_nombre,
          @codpos,
          @orden
   END 
   CLOSE dom
   DEALLOCATE dom 
END 
   FETCH NEXT FROM C_Padron INTO   @cuit,
          @id_padron, 
          @identificador, 
          @nombre, @dni
          
 END
 CLOSE C_Padron
 DEALLOCATE C_Padron
 /* Generar la salida
 
  declare @cliente int = 0 ,
             @fecha_proceso date = '2018-10-12'  
 
  select  r.cuit   as 'CUIT/CUIL',
  r.dni as 'DNI', 
  isnull(r.nombre,'') as 'Nombre',
  isnull(CONVERT(varchar(10),convert(date,r.fecha_nac),103),'') as 'Fecha de Nacimiento',
  anio_nac as 'Año Nacimiento', 
  isnull(convert(varchar,r.edad),'') as 'Edad',
  isnull(CONVERT(varchar(10),convert(date,r.fecha_defuncion),103),'') as 'Fecha de Fallecimiento',
  isnull(iif (len(r.tel) > 32000, substring(r.tel, 1, CHARINDEX ('/', r.tel, 32000)),tel),'') as 'Teléfonos',
  isnull(r.email,'')       as 'Emails',
  --isnull(r.dependencia,'') as 'Relación de dependencia 2018',
  isnull(r.domicilio,'')   as 'Domicilio',
  isnull(r.prv_nombre,'')  as 'Provincia',
  isnull(r.loc_nombre,'')  as 'Localidad',
  isnull(r.codpos,'') as 'CP',
  isnull(r.domicilio2,'')   as 'Domicilio 2',
  isnull(r.prv_nombre2,'')  as 'Provincia 2',procesos_cuit_enrichment_datos_simples
  isnull(r.loc_nombre2,'')  as 'Localidad 2',
  isnull(r.codpos2,'') as 'CP 2',
  isnull(r.empleadores,'') as 'Empleadores'
 
  from procesos_cuit_enrichment_datos_simples r
  where --cliente = @cliente and 
  fecha_proceso = @fecha_proceso
  order by 1
 
  */
   
END 

GO