dbo.sp_proceso_la_estrella

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*

SI SE QUIERE MANDAR MAIL, mandar 1 como tercer parametro

Proceso BASE PARA ENRIQUECER de La Estrella
1) cargar en la tabla la_estrella los datos que mandan en el excel como fecha_proceso la fecha del día, los demás campos en 
dato1... dato11
INSERT INTO La_estrella (fecha_proceso, dato1,dato2,dato3,dato4,dato5,dato6,dato7,dato8,dato9,dato10,dato11)
2) exec [sp_proceso_la_estrella] <fecha>, 1
en este paso 1, se completan los cuits y se cargan en la tabla consultar_afip con la fecha mandada como parámetro y proceso = 'ESTRELLA'
3) Buscar esos cuits en afip (con el proceso de consultar_afip de siempre)
4) exec [sp_proceso_la_estrella] '2020-09-08', 2
en este paso 2, se devuelve el excel que se tiene que mandar
en ese excel controlar y corregir o completar
- los casos que no están separados los apellidos de los nombres
- los casos en los que falta sexo
- los casos en los que falta nacionalidad
*/
/*
Ejemplo:

1)
INSERT INTO La_estrella (fecha_proceso, dato1,dato2,dato3,dato4,dato5,dato6,dato7,dato8,dato9,dato10,dato11) 
values ('2020-03-08','153617','1','27','19041843','5','1','AEDO CERNA NANCY DEL CARM','F','26','1','60')

2) exec [sp_proceso_la_estrella]  '2020-04-13', 1

3) Correr el proceso de consultar_afip

4) exec [sp_proceso_la_estrella]  '2022-04-12', 2

*/
CREATE OR ALTER PROCEDURE dbo.sp_proceso_la_estrella  
@fecha_proceso date = '2020-03-09', 
@paso int = 2, 
@con_mail bit = 1
AS
BEGIN
    SET NOCOUNT ON;
   declare  @tipo_cuit varchar(100) = NULL,
    @cuit varchar(20) = NULL,
    @tipo_doc varchar(20) = null, 
    @documento varchar(20) = null, 
    @apellido varchar(200) = NULL,
    @nombre varchar(200) = NULL,
    @nombre_persona varchar(200) = NULL,
    @fecha_nacimiento date = null, 
    @anio_nacimiento varchar(4) = null, 
    @sexo varchar(1) = null, 
    @estado_civil varchar(20) = null, 
    @nacionalidad varchar(100) = null, 
    @fecha_defuncion date = null, 
    @domicilio varchar(100) = null, 
    @codpos varchar(10) = null, 
    @localidad varchar(100) = null, 
    @provincia varchar(100) = null, 
    @telefono varchar(100) = null, 
   @dato1 varchar(100) = null,
   @dato2 varchar(100) = null,
   @dato3 varchar(100) = null,
   @dato4 varchar(100) = null,
   @dato5 varchar(100) = null,
   @dato6 varchar(100) = null,
   @dato7 varchar(100) = null,
   @dato8 varchar(100) = null,
   @dato9 varchar(100) = null,
   @dato10 varchar(100) = null,
   @dato11 varchar(100) = null, 
   @id int = null


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


 declare @tipo varchar(max), 
   @tel varchar(50),
   @email varchar(200),
   @tel_formateado varchar(50)

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

 declare @telefonos table (  cuit varchar(20),
        nombre varchar(200),
        telefono varchar(50),
        telefono_formateado varchar(50))



 declare @tel_todos varchar(max), @hay char(1) = 'N', @esta bit = 0

 set @tel_todos = null


 DECLARE bn2 CURSOR

  FOR
  SELECT isnull(dato3,'0'), isnull(dato4,'0'), isnull(dato5,'0'), id, dato8, cuit
  from la_estrella
  where fecha_proceso = @fecha_proceso 
  /*and cuit = '20125653120' */
  --and apellido is not null and (nacionalidad is null or nacionalidad = '1' or nacionalidad = '0')

           
  OPEN bn2 
  Fetch next from bn2 INTO  @dato3, @dato4, @dato5, @id, @dato8,@cuit
  
  WHILE (@@FETCH_STATUS <> -1 )
 
  BEGIN 

  if (@paso = 1)
  begin 
   if @cuit is null
   begin
    if (@dato5 is null or @dato5 = '') set @dato5 = ' '
    set @cuit = @dato3 + right('00000000'+convert(varchar(8),@dato4),8) + replace(@dato5,' ','0')
   end
   update la_estrella set cuit = @cuit where id = @id 
  end  

  if (@paso = 2)
  begin
   if @cuit is null
   begin
    if (@dato5 is null or @dato5 = '') set @dato5 = ' '
     set @cuit = @dato3 + right('00000000'+convert(varchar(8),@dato4),8) + replace(@dato5,' ','0')
   end
   if (select count(*) from padrones where identificacion = @cuit and habilitado = 1) = 0 
      begin
         set @cuit = null 
         set @cuit = (select top 1 identificacion from padrones where cuerpo_clave = @dato4 and habilitado = 1) 
      end 
   if (@cuit is null or LEN(@cuit) != 11)
   begin 
    Fetch next from bn2 INTO  @dato3, @dato4, @dato5, @id, @dato8 
    continue
   end 
     
   set @nombre    = null
   set @apellido  = null 
   set @sexo      = null 

   set @nombre    = (select top 1 nombres from padrones where identificacion = @cuit and habilitado = 1)
   set @apellido  = (select top 1 apellidos from padrones where identificacion = @cuit and habilitado = 1)
   --- agregado 19/03/2024
   set @nombre_persona    = (select top 1 nombre_persona from padrones where identificacion = @cuit and habilitado = 1)
   if (@apellido is null) begin
   set @apellido = @nombre_persona
   end
   if (@nombre is null) begin
    set @nombre = (select substring(@apellido, CHARINDEX(' ', @apellido)+1, len(@apellido)-(CHARINDEX(' ', @apellido)-1)) )
    set @apellido = (select left(@apellido, CHARINDEX(' ', @apellido)) as nombre_usuario)
   end
   ----
   set @tipo_cuit = (select top 1 identificador from padrones where identificacion = @cuit and habilitado = 1)
   set @sexo = (select top 1 sexo from padrones where identificacion = @cuit and habilitado = 1)
   
   -- agregado 19/03/2024
   if (@sexo is null or @sexo not in ('F','M')) set @sexo = iif (@cuit like '20%','M',iif(@cuit like '27%','F',null))

   set @documento = (select top 1 convert(int,cuerpo_clave) from padrones where identificacion = @cuit and habilitado = 1 and identificacion like '2%')

   if (isnull(@apellido,'') = '')
   begin 
    set @apellido = (select top 1 nombre_persona from padrones where identificacion = @cuit and habilitado = 1)
    set @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 = @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 = @cuit and email = @email)
     if (@esta = 0)
     begin
      insert into @emails
      values (@cuit, 
              @nombre, 
        @email)
        end
     FETCH next from mail2 INTO @email
    END
   CLOSE mail2
   DEALLOCATE mail2 
   if (@hay = 'N')  
    begin
     insert into @emails
        values (@cuit, 
         @nombre, 
         '')
   end 

   set @email_todos = ''
   DECLARE restm CURSOR
   FOR
     select email
      from @emails
     where cuit = @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





   -- 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 = @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 = @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 (@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 (@cuit, 
         @nombre, 
         '',
         '')
   end 

   set @tel_todos = ''
   DECLARE rest CURSOR
   FOR
     select telefono_formateado
      from @telefonos
     where cuit = @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 
     if (LEN(@tel_todos) > 900) break
     FETCH next from rest INTO @tel
    END
   CLOSE rest
   DEALLOCATE rest 

         
   -- Domicilios 
   set @domicilio = '' 
   set @localidad = ''
   set @provincia = ''
   set @codpos = ''
     
    insert into @domicilios
    exec sp_obtener_domicilios @cuit, 1
   if (select count(*) from @domicilios) = 0 
    insert into @domicilios
    exec sp_obtener_domicilios @cuit, 0
   -- Toma uno, si tenemos el fiscal (F), sino el legal (L), sino el real (D), sino el electoral (E), sino otro... 
   SELECT top 1
            @domicilio = 
      isnull(d.domicilio,''),
      @localidad =
      isnull(d.loc_nombre,''), 
      @provincia = 
      isnull(d.prv_nombre,''),
      @codpos = 
      isnull(d.codpos,'')
    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)
   delete from @domicilios
   declare @nacionalidad_ok varchar(100)
   set @fecha_defuncion = null
   set @fecha_nacimiento = null 
   set @nacionalidad = null 
   select @fecha_nacimiento = c02_fecha_nac, @fecha_defuncion = fecha_defuncion, @nacionalidad_ok = nacionalidad, @nacionalidad = pai_codigo_nacionalidad 
     from datos_personas 
     where c01_cuit = @cuit 

   if (@nacionalidad_ok is not null) 
   begin
    set @nacionalidad = @nacionalidad_ok
   end
   else 
   begin
    if (isnull(@nacionalidad,'0') != '0')
    begin
     select @nacionalidad = upper(country_name) 
     from country where codigo_ifx = @nacionalidad
    end 
    if (isnull(@nacionalidad,'0') = '0' or @nacionalidad = '')
    begin
     if (@documento not between 15000000 and 15999999 
        and 
        @documento not between 60000000 and 69999999 
        and 
        @documento not between 90000000 and 99999999)
        begin
        set @nacionalidad = 'ARGENTINA'
        end 
    end
   end
/*
select @tipo_cuit, 
@cuit, 
'DNI', 
@documento, 
@apellido, 
@nombre, 
@fecha_nacimiento,  
DATEPART(year,@fecha_nacimiento), 
@sexo, 
'', 
@nacionalidad, 
@fecha_defuncion,  
@domicilio, 
@codpos, 
@localidad, 
@provincia, 
@tel_todos
*/


 
   update la_estrella 
   set tipo_cuit = @tipo_cuit, 
    cuit = iif(@apellido is null, null, @cuit),
    tipo_doc = 'DNI', 
    documento = @documento, 
    apellido = @apellido, 
    nombre = @nombre, 
    fecha_nacimiento = @fecha_nacimiento,  
    anio_nacimiento = DATEPART(year,@fecha_nacimiento), 
    sexo =  isnull(@sexo,@dato8), 
    estado_civil = '', 
    nacionalidad = @nacionalidad, 
    fecha_defuncion = @fecha_defuncion,  
    domicilio = @domicilio, 
    codpos = @codpos, 
    localidad = @localidad, 
    provincia = @provincia, 
    telefono = @tel_todos, 
    email = @email_todos
    
   where id = @id 
  end 
  Fetch next from bn2 INTO  @dato3, @dato4, @dato5, @id, @dato8, @cuit
  END
  
  CLOSE bn2
  DEALLOCATE bn2 

  if (@paso = 1) 
  begin 
   insert into consultar_afip(proceso, cuit, fecha, procesado,prioridad)
   select 'ESTRELLA', cuit, fecha_proceso, 0, 1
     from la_estrella 
   where fecha_proceso = @fecha_proceso

   select * from consultar_afip where proceso = 'ESTRELLA' and fecha = @fecha_proceso
  end 

  if (@paso = 2)
  begin
   select 
   isnull(dato1,'') as ' ', 
   isnull(dato2,'') as ' ', 
   isnull(dato3,'') as ' ', 
   isnull(dato4,'') as ' ', 
   isnull(dato5,'') as ' ', 
   isnull(dato6,'') as ' ', 
   isnull(dato7,'') as ' ',
   isnull(dato8,'') as ' ', 
   isnull(dato9,'') as ' ', 
   isnull(dato10,'') as ' ', 
   isnull(dato11,'') as ' ', 
   isnull(tipo_cuit,'') as 'TIPO CUIT', 
   isnull(cuit,'') as 'CUIT', 
   isnull(tipo_doc,'') as 'TIPO DOC.', 
   isnull(documento,'') as 'DOCUMENTO', 
   isnull(apellido,'') as 'APELLIDO', 
   isnull(nombre,'') as 'NOMBRE', 
   isnull(convert(varchar(10),fecha_nacimiento,103),'') as 'FECHA NACIMIENTO',
   isnull(anio_nacimiento,'') as 'AÑO NACIMIENTO', 
   isnull(sexo,'') as 'SEXO', 
   isnull(estado_civil,'') as 'ESTADO CIVIL', 
   isnull(nacionalidad,'') as 'NACIONALIDAD', 
   isnull(convert(varchar(10),fecha_defuncion,103),'') as 'FECHA DEFUNCION', 
   isnull(domicilio,'') AS 'DOMICILIO', 
   isnull(codpos,'') AS 'CP', 
   isnull(localidad,'') as 'LOCALIDAD',
   isnull(provincia,'') as 'PROVINCIA', 
   isnull(telefono,'') as 'TELEFONO',
   iif (@con_mail = 1,isnull(email,''),'') as ' ' 

    from la_estrella
   where fecha_proceso = @fecha_proceso 
  
   order by id 
  end

   
END 
/*
select * from padrones where identificacion in (select cuit from procesos_cuit where fecha = '2020-03-09')
select * from consultar_afip where  procesado = '0' and proceso = 'ESTRELLA'
*/
GO