dbo.sp_plan_ovalo_salida

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER PROCEDURE dbo.sp_plan_ovalo_salida   
@cliente int =  1,
@fecha_proceso date = '2018-08-17',
@retomar char(1) = 'N' ,
@buscar_datos_personales char(1) = 'N',
@buscar_domicilios char(1) = 'N', 
@buscar_telefonos char(1) = 'S',
@buscar_mails char(1) = 'S',
@buscar_dependencia char(1) = 'N'
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),
   @fecha_nac date, 
   @fecha_def date, 
   @edad tinyint

    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)

 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)
 if (@retomar = 'N')
 begin
  delete from procesos_plan_ovalo_respuesta where cliente = @cliente and fecha_proceso = @fecha_proceso 
 end 
 DECLARE C_padron CURSOR FOR
   select distinct a.cuit_asignado,
       b.id_padron, 
       b.identificador, 
       b.nombre_persona,
       a.dni, 
       a.ref_cliente
    from posiciones_fijas a
    inner join padrones b on identificacion = cuit_asignado and habilitado = 1 and identificador in (select identificador from identificadores where country_code = 'AR')
    where a.cliente = @cliente 
    and a.fecha_proceso = @fecha_proceso
    and isnull(a.cuit_asignado,'') != ''
    and not exists (select * from procesos_plan_ovalo_respuesta r
                     where r.cliente = @cliente and r.fecha_proceso = @fecha_proceso and r.cuit = a.cuit_asignado)
    --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_asignado
 
 OPEN C_Padron
 FETCH NEXT FROM C_Padron INTO   @cuit,
         @id_padron, 
         @identificador, 
         @nombre,
         @dni, 
         @ref_cliente
           
 WHILE @@fetch_status = 0
 BEGIN 
  set @fecha_nac = null
  set @edad = null
  set @fecha_def = null
if (@buscar_datos_personales = 'S') 
BEGIN
  select @fecha_nac = c.c02_fecha_nac,
         @edad      = (cast(datediff(dd, c.c02_fecha_nac,GETDATE()) / 365.25 as int)),
      @fecha_def = c.fecha_defuncion
    from datos_personas c
   where c.c01_cuit = @cuit and c.visible = 1
  if (@fecha_def is not null) set @edad = null
END 
  --> Telefonos 
  set @tel = null
if (@buscar_telefonos = 'S') 
BEGIN
  insert into @telefonos 
    exec sp_obtener_telefonos @cuit
  set @tel = (select telefono from @telefonos) 
  delete from @telefonos
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_plan_ovalo_respuesta
                   (cliente,
     fecha_proceso,
     id_padron,
     identificador,
     cuit,
     nombre, 
     fecha_nac,
     edad,
     fecha_defuncion,
     tel,
     email,
     dependencia,
     dni,
     ref_cliente)
   values (@cliente, @fecha_proceso, @id_padron, @identificador, @cuit,
     @nombre,
     @fecha_nac,
     @edad,
     @fecha_def, 
     @tel,
     @email_todos,
     @dependencia,
     @dni,
     @ref_cliente)
  set @id = (SELECT @@IDENTITY)

  --> Domicilios F y D

if (@buscar_domicilios = 'S') 
BEGIN
  insert into @domicilios
    exec sp_obtener_domicilios @cuit 
  if (select count(*) from @domicilios where tipo_domicilio like '%[FD]%' 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
      from @domicilios where tipo_domicilio like '%[FD]%' 
       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%')
   end 
   else
   begin
   DECLARE dom CURSOR FOR
    select distinct domicilio,
        pai_nombre, 
        prv_nombre, 
        loc_nombre,
        codpos
      from @domicilios 
      where tipo_domicilio like '%[FD]%'
        and (isnull(prv_nombre,'') not like 'NO INFORMA%' and isnull(loc_nombre,'') not like 'NO INFORMA%')
   end 
   OPEN dom
   set @cant_dom = 0 
   FETCH NEXT FROM dom INTO @domicilio,
        @pai_nombre, 
        @prv_nombre, 
        @loc_nombre,
        @codpos
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
    set @cant_dom = @cant_dom + 1
    if (@cant_dom = 1)
    begin
     update  procesos_plan_ovalo_respuesta
        set  domicilio = @domicilio,
       pai_nombre = @pai_nombre,
       prv_nombre = @prv_nombre,
       loc_nombre = @loc_nombre,
       codpos = @codpos 
      where  id = @id
    end 
    else 
    begin
    insert into procesos_plan_ovalo_respuesta
             (cliente,
        fecha_proceso,
        id_padron,
        identificador,
        cuit,
        nombre, 
        domicilio,
        pai_nombre,
        prv_nombre,
        loc_nombre,
        codpos,
        dni,
        ref_cliente)
      values (@cliente, @fecha_proceso, @id_padron, @identificador, @cuit,
        @nombre,
        @domicilio,
        @pai_nombre,
        @prv_nombre,
        @loc_nombre,
        @codpos,
        @dni,
        @ref_cliente)
    end 
    delete from @domicilios
    FETCH NEXT FROM dom INTO @domicilio,
          @pai_nombre, 
          @prv_nombre, 
          @loc_nombre,
          @codpos
   END 
   CLOSE dom
   DEALLOCATE dom 
END 
   FETCH NEXT FROM C_Padron INTO   @cuit,
          @id_padron, 
          @identificador, 
          @nombre,
          @dni, 
          @ref_cliente
          
 END
 CLOSE C_Padron
 DEALLOCATE C_Padron
 /* Generar la salida
 
  declare @cliente int =  1,
             @fecha_proceso date = '2018-08-17'  
 
  select   r.cuit   as 'CUIT/CUIL',
  isnull(r.nombre,'') as 'Nombre',
  isnull(CONVERT(varchar(10),convert(date,r.fecha_nac),103),'') as 'Fecha de 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.pai_nombre,'')  as 'País',
  isnull(r.codpos,'') as 'CP'
  from procesos_plan_ovalo_respuesta r
  where cliente = @cliente and fecha_proceso = @fecha_proceso
  order by 1, 3 desc
 
  */

 /* Algunos ajustes que hicimos en procesos anteriores 
  declare @cliente int =  1,
             @fecha_proceso date = '2018-08-09'
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'BUENOS AIRES' where prv_nombre = 'ARGENTINA'
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'BUENOS AIRES' where prv_nombre = 'AMBA SUR'
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'BUENOS AIRES' where prv_nombre = 'AMBA OESTE'
 
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'CIUDAD AUTONOMA DE BUENOS AIRES', loc_nombre = null where prv_nombre IN ('CIUDAD DE BUENOS AIRES','CIUDAD AUTONOMA BUENOS AIRES')
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'CIUDAD AUTONOMA DE BUENOS AIRES', loc_nombre = null where prv_nombre = 'CAPITAL Y GBA NORTE'
  UPDATE procesos_plan_ovalo_respuesta SET prv_nombre = 'CIUDAD AUTONOMA DE BUENOS AIRES', loc_nombre = null where prv_nombre = 'CAPITAL Y GBA NORTE'
 
  */    
END 

GO