dbo.sp_procesos_cuit_enrichment_allianz

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.sp_procesos_cuit_enrichment_allianz  
@id_cliente int = 1,
@fecha_proceso date = '2018-11-07'
/*
1) cargar Cuits en procesos_cuit
2) correr sp_procesos_cuit_enrichment
3) correr sp_procesos_cuit_enrichment_mas_datos
4) correr sp_procesos_cuit_enrichment_datos_simples
5) http://www.backend7.int/lista_cuit.php (antes ponerle la lista de cuits que corresponde)
update procesos_cuit_enrichment set score = ... where cuit =... and cliente = ... and fecha_proceso = ...
6) http://www.backend7.int/nivel_socio_economico.php (antes ponerle la lista de cuits que corresponde)
insert into procesos_nivel_socio_economico (cliente, fecha_proceso, cuit, comex_exportaciones, comex_importaciones, rango_desde, rango_hasta) values ()
*/
AS
BEGIN
  SET NOCOUNT ON;
  
  declare @bancos       table (cuit varchar(20), banco varchar(100))
  declare @bancos_todos table (cuit varchar(20), bancos varchar(max))
  declare @cuit     varchar(20)
  declare @banco    varchar(100) 
  declare @cuit_ant varchar(20) 
  declare @bcos     varchar(max)

/* Busca Bancos con los que opera/ba */

  insert into @bancos
  select distinct cuit, nombre_corto 
  from procesos_cuit
 inner join cendeu on iden = cuit and cendeu.fec_inf >= '2016-11' and vigente = 1  
  inner join EntidadesBancarias on convert(int,cendeu.codigo_ent) = EntidadesBancarias.id_banco
 where cliente = @id_cliente and fecha = @fecha_proceso
  union 
  select distinct cuit, nombre_corto 
  from procesos_cuit
 inner join cendeu_temp on iden = cuit and cendeu_temp.fec_inf >= '2016-11' and vigente = 1  
  inner join EntidadesBancarias on convert(int,cendeu_temp.codigo_ent) = EntidadesBancarias.id_banco
 where cliente = @id_cliente and fecha = @fecha_proceso

  DECLARE res2 CURSOR
   FOR
     select cuit, rtrim(ltrim(banco))
      from @bancos
          order by cuit
        
   OPEN res2 
   set @cuit_ant = ''
      set @bcos = ''

   FETCH next from res2 INTO @cuit, @banco
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
          if (@cuit != @cuit_ant and @cuit_ant != '')
          begin
            insert into @bancos_todos values (@cuit_ant, @bcos)
            set @bcos = ''
          end 
          set @cuit_ant = @cuit
          set @bcos = @bcos + @banco + ' - ' 
     FETCH next from res2 INTO @cuit, @banco
    END
   CLOSE res2
   DEALLOCATE res2 
      insert into @bancos_todos values (@cuit_ant, @bcos)

/* Vinculacion sociedades */
declare @tipo char(1)
declare @vinculaciones table (
                              idNodoGrafo     int, 
                              cuit_vinc       varchar(20),
                              nombre_persona varchar(100),
                              CentroGrafo     varchar(100),
                              IDCentroGrafo   int, 
                              seccion         varchar(100)
                             )   
declare @vinc_todos table (
                              cuit       varchar(20),
                              vinculaciones varchar(max)
                             )   
declare @cuit_vinc varchar(20), 
        @nombre    varchar(100), 
        @seccion   varchar(100),
        @vinc      varchar(max),
        @id_padron int 

  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 @cuit
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
          if (@cuit like '3%') 
          BEGIN 
            set @tipo = 'j' 
          END
          ELSE
          BEGIN
            set @tipo = 'f' 
          END 
        insert into @vinculaciones
             exec grafo_sociedades @cuit, @tipo
          FETCH next from res INTO @cuit
        END 
    CLOSE res
  DEALLOCATE res
   
    DECLARE res1 CURSOR
   FOR
     select v.IDCentroGrafo,
                v.cuit_vinc ,      
                ltrim(rtrim(v.nombre_persona)),
                replace(ltrim(rtrim(v.seccion)),'-',' ')
      from @vinculaciones v
          where ltrim(rtrim(v.seccion)) != 'EMPLEADO_DE'
          order by v.IDCentroGrafo
        
   OPEN res1
   set @cuit_ant = ''
      set @vinc = ''

   FETCH next from res1 INTO @id_padron, @cuit_vinc, @nombre, @seccion 
     
   WHILE (@@FETCH_STATUS = 0)
    BEGIN
          set @cuit = (select identificacion from padrones where id_padron = @id_padron)
 
          if (@cuit != @cuit_ant and @cuit_ant != '')
          begin
            insert into @vinc_todos values (@cuit_ant, @vinc)
            set @vinc = ''
          end 
          set @cuit_ant = @cuit
          set @vinc = @vinc + @seccion + ': ' + @cuit_vinc + ' ' + @nombre +  ';'
  
     FETCH next from res1 INTO @id_padron, @cuit_vinc, @nombre, @seccion 
    END
      
   CLOSE res1
   DEALLOCATE res1
      insert into @vinc_todos values (@cuit_ant, @vinc)

/* RESULTADO */

select 
--c.identificador as 'Identificador',
a.Cuit as 'Cuit',
a.Nro_documento as 'Nro documento',
a.Nombre as 'Nombre',
iif(p.sexo='P','',p.sexo) as 'Sexo',

isnull(CONVERT(varchar(10),convert(date,c.fecha_nac),103),'') as 'Fecha Nac',
c.edad as 'Edad',
c.defuncion as 'Defunción',
isnull(CONVERT(varchar(10),convert(date,c.fecha_defuncion),103),'') as 'Fecha Defunción',

a.Peticion_Concurso_Preventivo as 'Peticion Concurso Preventivo',
a.Concurso_Preventivo_Proveido as 'Concurso Preventivo Proveido',
a.Concurso_Concluido as 'Concurso Concluido',
a.Instancias_Concursos_y_Quiebras as 'Instancias Concursos y Quiebras',
a.Pedidos_de_Quiebras as 'Pedidos de Quiebras',
a.Quiebras as 'Quiebras',
a.Juicios_Ejecutivos_Ultimo_Anio as 'Juicios Ejecutivos Ultimo Año',
a.Juicios_por_Honorarios_Ultimo_Anio as 'Juicios por Honorarios Ultimo Año',
a.Causas_Judiciales_Ultimo_Anio as 'Causas Judiciales Ultimo Año',
a.Ultimo_Mes_BCRA as 'Ultimo Mes BCRA',
a.Cant_Bancos_Ultimo_Mes_BCRA as 'Cant Bancos Ultimo Mes BCRA',
a.Monto_Total_Ultimo_Mes_BCRA as 'Monto Total Ultimo Mes BCRA',
a.Peor_situacion_Ultimos_24_meses as 'Peor situacion Ultimos 24 meses',
IIF(ISNULL(a.Peor_situacion_Ultimos_24_meses,0)>1,'SI','NO') as 'Sit Bancaria Diferente a Normal',
a.Monto_Peor_Situacion_Ultimos_24_meses as 'Monto Peor Situacion Ultimos 24 meses',
a.Cant_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio as 'Cant Cheques Sin Fondos Sin Levantar Ultimo Año',
a.Monto_Cheques_Sin_Fondos_Sin_Levantar_Ultimo_Anio as 'Monto Cheques Sin Fondos Sin Levantar Ultimo Año',
a.Cant_Cheques_Sin_Fondos_Levantados_Ultimo_Anio as 'Cant Cheques Sin Fondos Levantados Ultimo Año',
a.Monto_Cheques_Sin_Fondos_Levantados_Ultimo_Anio as 'Monto Cheques Sin Fondos Levantados Ultimo Año',
a.Cant_Cheques_Sin_Fondos_Sin_Levantar_Anios_Anteriores as 'Cant Cheques Sin Fondos Sin Levantar Años Anteriores',
a.Monto_Cheqeus_Sin_Fondos_Sin_Levantar_Anios_Anteriores as 'Monto Cheqeus Sin Fondos Sin Levantar Años Anteriores',
a.Cant_Cheques_Sin_Fondos_Levantados_Anios_Anteriores as 'Cant Cheques Sin Fondos Levantados Años Anteriores',
a.Monto_Cheques_Sin_Fondos_Levantados_Anios_Anteriores as 'Monto Cheques Sin Fondos Levantados Años Anteriores',

a.score as 'Score',


z.bancos as 'Bancos con los que opera/ba', 
w.vinculaciones as 'Sociedades / Personas Vinculadas',

b.actividades as 'CLAE',
a.cant_empleados as 'Cant Empleados',

c.dependencia as 'Relación de Dependencia',
b.empleadores as 'Empleadores',

--b.Obra_Social as 'Obra Social',

iif (
  (select count(*) from padron_impuestos pi where pi.id_padron = c.id_padron and pi.codigo_impuesto = '30' and visible = 1 and estado = 'ACTIVO') = 0
  ,'SI','NO') as 'Responsable Inscripto',

b.monotributo as 'Monotributo',
b.monotributo_autonomo as 'Monotributo autonomo',
b.monotributo_seg_social as 'Monotributo seg social',
b.monotributo_integrante_sociedad as 'Monotributo integrante sociedad',

c.tel as  'Teléfonos',
c.email as  'E-mail',

c.domicilio as  'Domicilio 1 ',
c.pai_nombre as  'País ',
c.prv_nombre as  'Provincia ',
c.loc_nombre as  'Localidad',
c.codpos as  'Cod Pos',
c.domicilio2 as  'Domicilio 2 ',
c.pai_nombre2 as  'País ',
c.prv_nombre2 as  'Provincia ',
c.loc_nombre2 as  'Localidad',
c.codpos2 as  'Cod Pos', 

n.comex_importaciones as 'Importaciones',
n.comex_exportaciones as 'Exportaciones', 
n.rango_desde as 'NSE desde', 
n.rango_hasta as 'NSE hasta', 


iif ((select count(*) from apocrifas where apocrifas.cuit = a.cuit)=0, 'NO', 'SI') as 'Apocrifas',
iif ((select count(*) from sircreb where sircreb.cuit = a.cuit and Fecha_Vigencia_Hasta >= dateadd(year,-2,getdate()))= 0,'NO','SI') as 'Riesgo Fiscal SIRCREB', 
iif ((select count(*) from pep where pep.cuit = a.cuit)=0, 'NO', 'SI') as 'PEP',
iif ((select count(*) from clae where clae.cuit = a.cuit and clae.visible = 1 and clae.formulario = 883 and clae.cod_clae in (select cod_clae from clae_provincia_seguros))= 0,'NO','SI') as 'Sujeto Obligado'

from procesos_cuit_enrichment a
inner join procesos_cuit_enrichment_mas b on b.cuit = a.cuit
inner join procesos_cuit_enrichment_datos_simples c on c.cuit = a.cuit 
inner join procesos_nivel_socio_economico n on n.cuit = a.cuit 
inner join padrones p on p.identificacion = a.cuit and p.habilitado = 1 and p.identificador in (select identificador from identificadores where country_code = 'AR')
left join @bancos_todos z on z.cuit = a.cuit 
left join @vinc_todos w on w.cuit = a.cuit 


where a.cliente = @id_cliente  
and a.fecha_proceso = @fecha_proceso
and b.cliente = @id_cliente
and b.fecha_proceso = @fecha_proceso
and c.cliente = @id_cliente
and c.fecha_proceso = @fecha_proceso
and n.cliente = @id_cliente
and n.fecha_proceso = @fecha_proceso

END 

GO