dbo.socios_enriquecer27052024

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
insert into clientes_enriquecer_socios values (5546348) 
select * from socios where c01_cuit = '800847903'
exec [socios_enriquecer] 'clientes_enriquecer_socios_asignados'
exec [socios_enriquecer] 'clientes_enriquecer_socios_disponibles'
exec [socios_enriquecer] 'fechas', 2046807993,150,'2024-03-01'
exec [socios_enriquecer] 'cuits', 431136419,150,'2024-05-23' 

select * from socios where c01_cuit = '30708006080'

select * from clientes where cli_codigo = 97 

--select distinct cliente from procesos_cuit where fecha in ( '2023-05-16' , '2023-05-17') 

*/


CREATE OR ALTER PROCEDURE dbo.socios_enriquecer27052024
@accion varchar(50) = 'select',
@cliente int = null, 
@dias int = 150 , 
@p_fecha date = null
AS


BEGIN
 SET NOCOUNT ON;



 if (@accion = 'fechas') begin

  select procesos_cuit.cliente, clientes.nombre_cliente, fecha, count(distinct procesos_cuit.cuit) as cantidad 
   from procesos_cuit 
   inner join padrones on (padrones.identificacion = procesos_cuit.cuit and padrones.tipo_persona = 'J' and padrones.habilitado = 1) 
   left join identificadores on identificadores.identificador = padrones.identificador 
   left join clientes on clientes.cliente = procesos_cuit.cliente
   where procesos_cuit.cliente = @cliente and fecha > getdate() - @dias
   and identificadores.country_code = 'AR'


  group by procesos_cuit.cliente, clientes.nombre_cliente, fecha
  order by procesos_cuit.cliente, fecha desc

 end 


 if (@accion = 'clientes_enriquecer_socios_disponibles') begin
 
   select c.cliente, c.nombre_cliente, c.cli_codigo 
   from clientes_productos p
   inner join clientes c on c.cliente = p.cliente 
   where p.id_producto = 47 and p.cliente = c.cliente and p.activo = 1
   and c.estado_cliente = 'A'
   and not exists (select * from clientes_enriquecer_socios a where a.cliente = c.cliente)  
   order by nombre_cliente

 end

 if (@accion = 'clientes_enriquecer_socios_asignados') begin
   select c.cliente, c.nombre_cliente, c.cli_codigo 
   from clientes_productos p
   inner join clientes c on c.cliente = p.cliente 
   where p.id_producto = 47 and p.cliente = c.cliente and p.activo = 1
   and c.estado_cliente = 'A'
   and exists (select * from clientes_enriquecer_socios a where a.cliente = c.cliente)  
   order by nombre_cliente
 end

 if (@accion = 'agregar') begin

  insert into clientes_enriquecer_socios 
   select cliente
   from clientes  
   where cliente = @cliente
   and not exists (select * from clientes_enriquecer_socios where cliente = @cliente) 

 end

 if (@accion = 'sacar') begin
  delete from clientes_enriquecer_socios 
   where cliente = @cliente
 end
 
 declare @fecha date = dateadd(day,@dias*(-1),getdate())

 if (@accion = 'select') begin
  select distinct ln_contrapartes.cliente as COD_CLIENTE, clientes.nombre_cliente as CLIENTE, clientes.cli_codigo as CLI_CODIGO,
  padrones.identificacion CUIT, padrones.nombre_persona as CONTRAPARTE, ln_contrapartes.country_code, upper(country.country_name) as pais, ln_familias.nombre_familia, 
-- 22/02/2024 agregamos provincia (prioritariamente Fiscal, sino Legal) 
     (select top 1 prv_nombre 
   from domicilios_todos 
     where cuit = padrones.identificacion 
    and visible = 1 
    and domicilio not like '-[0-9][0-9],%'
    and domicilio not like 'RESIDE EN' 
    and domicilio not like 'DOMICILIADA EN%'
    and domicilio not like 'SIN DOMICILIO%'
    and (tipo_domicilio = 'L' or tipo_domicilio = 'F') and len(prv_nombre) > 2
     order by tipo_domicilio, created desc) as provincia,

     (select top 1 tabla.fecha from 
  (select iif(max(socios.created) is null and max(socios.modified) is null, MAX(convert(date,c04_fecha_act)), 
     iif(MAX(isnull(socios.created, '1900-01-01')) > MAX(isnull(socios.modified, '1900-01-01'))
     ,convert(date,MAX(socios.created)),
     convert(date,MAX(socios.modified)))) as fecha 
  from socios where socios.c01_cuit = padrones.identificacion
  union 
  select fecha from ultima_revision_socios where cuit = padrones.identificacion) tabla 
  order by fecha desc)
    
  as ultima_actualizacion , 

  (select MAX(fecha_publicacion) 
    from sociedades_constituciones
   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
   ) as boletin_ultima_fecha, 
  (select top 1 IIF(provincias.prv_nombre is null, sociedades_constituciones.provincia_sociedad, provincias.prv_nombre)
    from sociedades_constituciones
      left join provincias on provincias.prv_codigo = provincia_boletin and pai_codigo_ifx = 54

   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
         and fuente is not null 
      and fecha_publicacion = (select MAX(fecha_publicacion) 
            from sociedades_constituciones
           where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
           )
   ) as boletin_provincia 

  from ln_contrapartes
  left join country on (country.country_code = ln_contrapartes.country_code)
  inner join clientes on (clientes.cliente = ln_contrapartes.cliente and clientes.estado_cliente = 'A')
  inner join ln_contraparte_familias on (ln_contraparte_familias.contraparte = ln_contrapartes.contraparte and LN_contraparte_familias.activo = 1) 
  inner join ln_familias on (ln_familias.familia = ln_contraparte_familias.familia and LN_familias.visible = 1 and LN_familias.servicio_contratado = 47)  
  inner join padrones on (padrones.id_padron = ln_contrapartes.id_padron and padrones.tipo_persona = 'J') 
  
  where ln_contrapartes.cliente in (select cliente from clientes_enriquecer_socios) 
  and ln_contrapartes.activo = 1 
  and ln_contrapartes.subcontratista = 0 
  and 
  (
  not exists (select * from socios where socios.c01_cuit = padrones.identificacion and visible = 1)
  or 
  (
   (select MAX(isnull(socios.created, '1900-01-01')) from socios where socios.c01_cuit = padrones.identificacion and visible = 1) < @fecha
   and 
   (select MAX(isnull(socios.modified, '1900-01-01')) from socios where socios.c01_cuit = padrones.identificacion and visible = 1) < @fecha
  )
  )
  and 
  (
  not exists (select * from ultima_revision_socios where cuit = padrones.identificacion)
  or (select fecha from ultima_revision_socios where cuit = padrones.identificacion) < @fecha
  )

  and ln_contrapartes.country_code = 'AR'
  --and padrones.identificacion = '30709151041'
  order by clientes.nombre_cliente, padrones.nombre_persona
 end 


 
 if (@accion = 'cuits') begin

   

  select distinct clientes.cliente as COD_CLIENTE, clientes.nombre_cliente as CLIENTE, clientes.cli_codigo as CLI_CODIGO,
  padrones.identificacion CUIT, padrones.nombre_persona as CONTRAPARTE, identificadores.country_code, upper(country.country_name) as pais, 'SCREENING' as nombre_familia, 
  -- 22/02/2024 agregamos provincia (prioritariamente Fiscal, sino Legal) 
     (select top 1 prv_nombre 
   from domicilios_todos 
     where cuit = padrones.identificacion 
    and visible = 1 
    and domicilio not like '-[0-9][0-9],%'
    and domicilio not like 'RESIDE EN' 
    and domicilio not like 'DOMICILIADA EN%'
    and domicilio not like 'SIN DOMICILIO%'
    and (tipo_domicilio = 'L' or tipo_domicilio = 'F') and len(prv_nombre) > 2
     order by tipo_domicilio, created desc) as provincia,
     (select top 1 tabla.fecha from 
  (select iif(max(socios.created) is null and max(socios.modified) is null, MAX(convert(date,c04_fecha_act)), 
     iif(MAX(isnull(socios.created, '1900-01-01')) > MAX(isnull(socios.modified, '1900-01-01'))
     ,convert(date,MAX(socios.created)),
     convert(date,MAX(socios.modified)))) as fecha
  from socios where socios.c01_cuit = padrones.identificacion
  union 
  select fecha from ultima_revision_socios where cuit = padrones.identificacion) tabla 
  order by fecha desc)
    
  as ultima_actualizacion , 

  (select MAX(fecha_publicacion) 
    from sociedades_constituciones
   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
   ) as boletin_ultima_fecha, 
  (select top 1 IIF(provincias.prv_nombre is null, sociedades_constituciones.provincia_sociedad, provincias.prv_nombre)
    from sociedades_constituciones
      left join provincias on provincias.prv_codigo = provincia_boletin and pai_codigo_ifx = 54

   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
         and fuente is not null 
      and fecha_publicacion = (select MAX(fecha_publicacion) 
            from sociedades_constituciones
           where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
           )
   ) as boletin_provincia
   --, [dbo].[Eliminar_omitidos_sinonimos](padrones.nombre_persona) as nombre_contains 

  from procesos_cuit
  inner join padrones on (padrones.identificacion = procesos_cuit.cuit and padrones.tipo_persona = 'J' and padrones.habilitado = 1) 
  left join identificadores on identificadores.identificador = padrones.identificador 
  left join country on (country.country_code = identificadores.country_code)
  inner join clientes on (clientes.cliente = procesos_cuit.cliente)
  
  where procesos_cuit.cliente = @cliente and procesos_cuit.fecha = @p_fecha  
  /*
   and 
   (
   not exists (select * from socios where socios.c01_cuit = padrones.identificacion and visible = 1)
   or 
   (
   (select MAX(isnull(socios.created, '1900-01-01')) from socios where socios.c01_cuit = padrones.identificacion and visible = 1) < @fecha
   and 
   (select MAX(isnull(socios.modified, '1900-01-01')) from socios where socios.c01_cuit = padrones.identificacion and visible = 1) < @fecha
   )
   )
   and 
   (
   not exists (select * from ultima_revision_socios where cuit = padrones.identificacion)
   or (select fecha from ultima_revision_socios where cuit = padrones.identificacion) < @fecha
   )
   */
  and identificadores.country_code = 'AR'
  --and padrones.identificacion = '30709151041'
  
  -- Si tiene tilde de menos de 45 dias quedan afuera 
  -- (definido por Alejandro el 28/03/2024 porque felix decia que si tiene tilde ya esta, ya se reviso, queda afuera, no se entiende que puede estar revisado por un screening anterior)
  and 
  (
  (select max(fecha) from ultima_revision_socios where cuit = padrones.identificacion) is null 
  or 
  (select max(fecha) from ultima_revision_socios where cuit = padrones.identificacion) < getdate() - 45
  ) 
  --
  

  and 
  
  -- Que la fecha del ultimo boletin sea posterior a la ultima actualizacion de directorios 
  (
  (select MAX(fecha_publicacion) 
    from sociedades_constituciones
   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
   ) 
   
   >   

  (select top 1 tabla.fecha from 
  (select iif(max(socios.created) is null and max(socios.modified) is null, MAX(convert(date,c04_fecha_act)), 
     iif(MAX(isnull(socios.created, '1900-01-01')) > MAX(isnull(socios.modified, '1900-01-01'))
     ,convert(date,MAX(socios.created)),
     convert(date,MAX(socios.modified)))) as fecha 
  from socios where socios.c01_cuit = padrones.identificacion
  union 
  select fecha from ultima_revision_socios where cuit = padrones.identificacion) tabla 
  order by fecha desc)

/* ESTO */

  or  
  -- que la fecha de ultimo boletin este vacia 
  (
  (select MAX(fecha_publicacion) 
    from sociedades_constituciones
   where (documento_soc = padrones.identificacion or cuit_int = padrones.identificacion)
   ) is null 
  )

  or 
  -- que la fecha de ultima actulizacion de directorio este vacia 
  (select top 1 tabla.fecha from 
  (select iif(max(socios.created) is null and max(socios.modified) is null, MAX(convert(date,c04_fecha_act)), 
     iif(MAX(isnull(socios.created, '1900-01-01')) > MAX(isnull(socios.modified, '1900-01-01'))
     ,convert(date,MAX(socios.created)),
     convert(date,MAX(socios.modified)))) as fecha 
  from socios where socios.c01_cuit = padrones.identificacion
  union 
  select fecha from ultima_revision_socios where cuit = padrones.identificacion) tabla 
  order by fecha desc) is null


/* Invento 24/05/2024: */
  /*
   or 
  
   (select COUNT(*) 
   from sociedades_constituciones  
   where (documento_soc is null or documento_soc = '') and (contains(razon_social,nombre_contains)) and sociedades_constituciones.visible = 1) > 0
   or 
   (select COUNT(*) 
   from sociedades_constituciones  
   where (documento_soc is null or documento_soc = '') and (contains(contenido_archivo,nombre_contains)) and sociedades_constituciones.visible = 1) > 0 
   */
 

  ) 
   

  order by clientes.nombre_cliente, padrones.nombre_persona
 end 



END 

GO