dbo.sp_procesos_nivel_socio_economico

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
CUIT
RAZON SOCIAL
DOMICILIO COMPLETO
DATOS DE TELEFONO
ACTIVIDAD
FACTURACION
DEUDA EN EL SISTEMA FINANCIERO Y SITUACION (ultima actualizada de BCRA) con apertura de líneas y bancos
CAPITAS EN ANSES (últimos 3 meses)
OPERATORIA DE COMEX (impo/expo, monto últimos 6 meses)
MARCA DE SI ES O NO CLIENTE MACRO
Datos de 5 Mayores sueldos de la nómina (cuit y datos de contacto-tel o celular)
*/
/*
[sp_procesos_nivel_socio_economico]   '263625984', '2018-08-06', 'N'
[sp_procesos_nivel_socio_economico]   '263625984', '2018-08-06', 'S'
*/
CREATE OR ALTER PROCEDURE dbo.[sp_procesos_nivel_socio_economico ]  
@cliente int, 
@fecha_proceso date,
@retomar char(1) = 'N'
AS
BEGIN
    SET NOCOUNT ON;

 declare @id_padron      int, 
         @nombre         varchar(100),
         @identificador  varchar(10), 
   @cuit           varchar(20),
   @rango_desde    decimal(20,2),
   @rango_hasta    decimal(20,2),
   @comex_importaciones decimal(20,2),
   @comex_exportaciones decimal(20,2),
   @facturacion         decimal(20,2),
   @es_cliente          bit 

 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),
      @prv_nombre varchar(100), 
   @loc_nombre varchar(100),
   @codpos     varchar(10)

 declare @telefonos table (  cuit varchar(20),
        telefono varchar(max))


 declare @tel varchar(max),
      @cod_clae varchar(10), 
   @descripcion varchar(512),
   @principal bit,
   @formulario varchar(10),
   @cant_empleados int

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

 declare @ult_mes_bcra char(7)
 declare @ult_mes_bcra2 char(7)
 declare @cant_bcra int = 0
 declare @monto_bcra decimal(16,2)
 declare @monto decimal(16,2)
 declare @bcra_temp table (codigo_ent int)
 declare @p_codigo_ent int
 declare @cuil varchar(20)


 declare @fec_periodo_minimo varchar(11),
         @periodo_minimo varchar(7)

 set @fec_periodo_minimo = dateadd(MM, -23,getdate())
    set @periodo_minimo = convert(varchar(4),DATEPART(yyyy,@fec_periodo_minimo))+right('0'+convert(varchar(2),DATEPART(mm,@fec_periodo_minimo)),2)

--> Inicio 
    
 if (@retomar = 'N') 
 begin
  delete from procesos_nivel_socio_economico_respuesta  where cliente = @cliente and fecha_proceso = @fecha_proceso
  delete from procesos_nivel_socio_economico_bancos     where cliente = @cliente and fecha_proceso = @fecha_proceso
  delete from procesos_nivel_socio_economico_empleados  where cliente = @cliente and fecha_proceso = @fecha_proceso
  delete from procesos_nivel_socio_economico_domicilios_todos where cliente = @cliente and fecha_proceso = @fecha_proceso
 end 

 DECLARE C_padron CURSOR FOR
   select distinct /* top 10 */
                      procesos_nivel_socio_economico.id_padron, 
                          padrones.nombre_persona,
          padrones.identificador,
          padrones.identificacion,
          procesos_nivel_socio_economico.rango_desde,
          procesos_nivel_socio_economico.rango_hasta,
          procesos_nivel_socio_economico.comex_importaciones,
          procesos_nivel_socio_economico.comex_exportaciones,
          procesos_nivel_socio_economico.facturacion,
          procesos_nivel_socio_economico.es_cliente 

   from procesos_nivel_socio_economico 
   inner join padrones on procesos_nivel_socio_economico.id_padron = padrones.id_padron
   where rango_desde > 30000000 and rango_hasta <= 600000000
   and cliente = @cliente
   and fecha_proceso = @fecha_proceso
   and not exists (select * from procesos_nivel_socio_economico_respuesta
           where procesos_nivel_socio_economico_respuesta.id_padron = procesos_nivel_socio_economico.id_padron
             and cliente = @cliente
                      and fecha_proceso = @fecha_proceso
       )
   order by padrones.nombre_persona asc

 OPEN C_Padron
 FETCH NEXT FROM C_Padron INTO   @id_padron,
                                 @nombre,
         @identificador, 
         @cuit,
         @rango_desde,
         @rango_hasta,
         @comex_importaciones,
         @comex_exportaciones,
         @facturacion,
         @es_cliente         
 WHILE @@fetch_status = 0
 BEGIN 

--> Domicilios 
/*
insert into @domicilios
exec sp_obtener_domicilios @cuit 
set @domicilio  = null
set @prv_nombre = null
set @loc_nombre = null
set @codpos     = null
--> domicilio Fiscal (F) de Buenos Aires a CABA
select top 1 @domicilio  = domicilio,
             @prv_nombre = prv_nombre,
 @loc_nombre = loc_nombre,
 @codpos     = codpos
from  @domicilios where tipo_domicilio like '%F%' and prv_nombre like '%BUENOS AIRES%' order by created desc
--> si no tiene, entonces Domicilio (D)
if (@domicilio is null)
begin
select top 1 @domicilio  = domicilio,
@prv_nombre = prv_nombre,
@loc_nombre = loc_nombre,
@codpos     = codpos
from  @domicilios where tipo_domicilio like '%D%' and prv_nombre like '%BUENOS AIRES%' order by created desc
end 
    delete from @domicilios
*/
/*
    ---- Lo cambiamos para que guarde todos los domicilios F o D, no solo uno
--> Domicilio Fiscal (F) de Buenos Aires a CABA

insert into @domicilios
exec sp_obtener_domicilios @cuit 

insert into procesos_nivel_socio_economico_domicilios
select distinct @cliente, @fecha_proceso, @id_padron, @cuit,  
domicilio,
prv_nombre,
loc_nombre,
codpos,
'F',
1
  from @domicilios where tipo_domicilio like '%F%' and prv_nombre like '%BUENOS AIRES%' 

--> Domicilio (D) de Buenos Aires a CABA

insert into procesos_nivel_socio_economico_domicilios
select distinct @cliente, @fecha_proceso, @id_padron, @cuit, 
domicilio,
prv_nombre,
loc_nombre,
codpos,
'D',
1
from  @domicilios where tipo_domicilio like '%D%' and tipo_domicilio not like '%F%' and prv_nombre like '%BUENOS AIRES%' 
    ----
    delete from @domicilios
*/
    ---- Lo cambiamos para que guarde los domicilios F, D, y otros
 --> Domicilios 
  insert into @domicilios
  exec sp_obtener_domicilios @cuit 
    
  --> Domicilios(F) y (D) de Buenos Aires a CABA
  insert into procesos_nivel_socio_economico_domicilios_todos
  select distinct @cliente, @fecha_proceso, @id_padron, @cuit,  
      domicilio,
      prv_nombre, 
      loc_nombre,
      codpos,
      'F', 
      1
    from @domicilios 
    where tipo_domicilio like '%F%' and prv_nombre like '%BUENOS AIRES%' 

  insert into procesos_nivel_socio_economico_domicilios_todos
  select distinct @cliente, @fecha_proceso, @id_padron, @cuit, 
      domicilio,
      prv_nombre, 
      loc_nombre,
      codpos,
      'D',
      1
   from  @domicilios 
   where tipo_domicilio like '%D%' 
   /*and tipo_domicilio not like '%F%' */
   and prv_nombre like '%BUENOS AIRES%' 

  --> Otros Domicilio (O), o que no son de Buenos Aires y CABA

  insert into procesos_nivel_socio_economico_domicilios_todos
  select distinct @cliente, @fecha_proceso, @id_padron, @cuit, 
      domicilio,
      prv_nombre, 
      loc_nombre,
      codpos,
      'O',
      1
   from  @domicilios 
   where (tipo_domicilio not like '%D%' and tipo_domicilio not like '%F%' ) or prv_nombre not like '%BUENOS AIRES%'
    ----

     delete from @domicilios

--> Telefonos 

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

--> Actividad

  -- traemos los nuevos que se compleataron desde afip que son los que tienen completo el campo formulario, si no encontramos ninguno trae los que tenga...
   DECLARE clae2 CURSOR
   FOR
     select distinct
      right('000000' + rtrim(replace(clae.cod_clae,'"','')),6) as cod_clae,   
      clae.descripcion, 
      principal,
      formulario
      from clae
     where clae.cuit = @cuit
       and clae.visible = 1
       and clae.descripcion is not null
       and clae.formulario is not null -- son las que se cargaron actualizadas de afip
           
       union 

     select distinct
      right('000000' + rtrim(replace(clae.cod_clae,'"','')),6) as cod_clae,   
      clae.descripcion, 
      principal,
      formulario
      from clae
     where clae.cuit = @cuit
       and clae.visible = 1
       and clae.descripcion is not null
       and clae.formulario is null 
       and not exists (select * from clae clae2 where clae2.cuit = clae.cuit and clae2.visible = 1 and clae2.formulario is not null) -- son las que se cargaron actualizadas de afip

     order by principal desc

      set @cod_clae = null
   set @descripcion = null
   set @principal = null
   set @formulario = null

   OPEN clae2 
   FETCH next from clae2 INTO @cod_clae, @descripcion, @principal, @formulario
   if (@descripcion like '(F.883%' and @formulario is null) set @formulario = '883'
   CLOSE clae2
   DEALLOCATE clae2 

--> Cantidad Empleados 

  insert into @empleados_cuit
    exec obtener_empleados @cuit
  set @cant_empleados = (select total from @empleados_cuit) 
  delete from @empleados_cuit 

--> Respuesta
  insert into procesos_nivel_socio_economico_respuesta
  values (@cliente, @fecha_proceso, @id_padron, 
          @nombre,
          @identificador, 
          @cuit,
          @rango_desde,
          @rango_hasta, 
          --@domicilio,
          --@prv_nombre,
       --@loc_nombre,
       --@codpos,
    @tel,
    @cod_clae, 
       @descripcion,
    @formulario,
    @cant_empleados,
    @comex_importaciones,
    @comex_exportaciones,
    @facturacion,
    @es_cliente,
    1,null
       )

--> BANCOS: Ultimo mes BCRA

  set @ult_mes_bcra  = (select max(fec_inf) from cendeu where iden = @cuit and vigente = 1 and fec_inf >= @periodo_minimo) 
  set @ult_mes_bcra2 = (select max(fec_inf) from cendeu_temp where iden = @cuit and vigente = 1 and fec_inf >= @periodo_minimo)
  if (@ult_mes_bcra  is null)  set @ult_mes_bcra  = '200101'
  if (@ult_mes_bcra2 is null)  set @ult_mes_bcra2 = '200101'
  if (@ult_mes_bcra2 > @ult_mes_bcra) begin set @ult_mes_bcra = @ult_mes_bcra2 end
  insert into procesos_nivel_socio_economico_bancos
  select distinct @cliente, @fecha_proceso, @id_padron, 
      iden as cuit, 
   SUBSTRING(@ult_mes_bcra,1,4) + '-' +  SUBSTRING(@ult_mes_bcra,5,2) as periodo,
   isnull(nombre_corto,nombre) as banco,
   substring(convert(char(2),convert(int,cendeu.situacion)),1,1) as situacion, 
   ((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as deuda,
   1 as visible
   from cendeu inner join EntidadesBancarias on
   convert(int,cendeu.codigo_ent) = EntidadesBancarias.id_banco
    where iden = @cuit 
    and fec_inf = @ult_mes_bcra 
    and vigente = 1  
    and not exists (select * from cendeu b where b.iden = cendeu.iden and cast(b.codigo_ent as int) = cast(cendeu.codigo_ent as int) and b.fec_inf = cendeu.fec_inf and b.vigente = 1 and b.id_fuente = 2 and b.id_cendeu != cendeu.id_cendeu)
    and not exists (select * from cendeu c 
          where c.iden = cendeu.iden
         and cast(c.codigo_ent as int) = cast(cendeu.codigo_ent as int)
         and c.fec_inf = cendeu.fec_inf
         and c.vigente = cendeu.vigente
         and (isnull(c.id_fuente,1)) = (isnull(cendeu.id_fuente,1))
         and c.id_cendeu < cendeu.id_cendeu)
  union all
    select distinct @cliente, @fecha_proceso, @id_padron, 
   iden as cuit, 
   SUBSTRING(@ult_mes_bcra,1,4) + '-' +  SUBSTRING(@ult_mes_bcra,5,2) as periodo,
   isnull(nombre_corto,nombre) as banco,
   substring(convert(char(2),convert(int,cendeu_temp.situacion)),1,1) as situacion, 
   ((prestamos+particip+garotor+o_cptos+deuda_total)*1000) as deuda, 
   1 as visible
   from cendeu_temp inner join EntidadesBancarias on
   convert(int,cendeu_temp.codigo_ent) = EntidadesBancarias.id_banco
    where iden = @cuit 
    and fec_inf = @ult_mes_bcra 
    and vigente = 1  
    and not exists (select * from cendeu b where b.iden = cendeu_temp.iden and cast(b.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int) and b.fec_inf = cendeu_temp.fec_inf and b.vigente = 1 and b.id_fuente = 2 and b.id_cendeu != cendeu_temp.id_cendeu)
    and not exists (select * from cendeu_temp c 
          where c.iden = cendeu_temp.iden
         and cast(c.codigo_ent as int) = cast(cendeu_temp.codigo_ent as int)
         and c.fec_inf = cendeu_temp.fec_inf
         and c.vigente = cendeu_temp.vigente
         and (isnull(c.id_fuente,1)) = (isnull(cendeu_temp.id_fuente,1))
         and c.id_cendeu < cendeu_temp.id_cendeu)

--> EMPLEADOS: Datos de 5 Mayores sueldos de la nómina (cuit y datos de contacto-tel o celular)

  insert into procesos_nivel_socio_economico_empleados (cliente, fecha_proceso, id_padron, cuit, cuil, nombre_empleado, periodo, sueldo,visible) 
  select distinct top 5 @cliente, @fecha_proceso, @id_padron, 
         a.cuit, a.cuil, p.nombre_persona, 
         '20' + SUBSTRING(convert(char(6),a.periodo),1,2) + '-' +  SUBSTRING(convert(char(6),a.periodo),3,2) as periodo,
            a.remimp, 1
    from empleados a 
    inner join padrones p on identificacion = a.cuil and p.habilitado = 1 and p.identificador in ('CUIL','CUIT')
   where a.cuit = @cuit 
     and a.periodo = (select max(periodo) from empleados where cuit = @cuit and periodo not like '%12' and periodo not like '%06')
     and a.fechapres = (select MAX(fechapres) from empleados b where b.cuit = a.cuit and b.cuil = a.cuil and b.periodo = a.periodo)
   order by a.remimp desc

   --> Telefonos empleados 
  DECLARE tel_empleados CURSOR FOR
   select cuil from procesos_nivel_socio_economico_empleados where cliente = @cliente and fecha_proceso = @fecha_proceso and cuit = @cuit
  OPEN tel_empleados
  FETCH NEXT FROM tel_empleados INTO @cuil
  WHILE @@fetch_status = 0
  BEGIN 
   
   set @tel = ''
   insert into @telefonos 
   exec sp_obtener_telefonos @cuil
   set @tel = (select telefono from @telefonos) 
   delete from @telefonos
   update procesos_nivel_socio_economico_empleados 
      set telefono = @tel 
    where cliente = @cliente and fecha_proceso = @fecha_proceso and cuit = @cuit and cuil = @cuil
  END 
  CLOSE tel_empleados
  DEALLOCATE tel_empleados 


  FETCH NEXT FROM C_Padron INTO   @id_padron,
          @nombre,
          @identificador, 
          @cuit,
          @rango_desde,
          @rango_hasta,
          @comex_importaciones,
          @comex_exportaciones,
          @facturacion,
          @es_cliente 

 END  
 CLOSE C_Padron
 DEALLOCATE C_Padron

 /*
 declare @cliente int = '263625984'
 declare @fecha_proceso date = '2018-08-06'
  select  top 1000 
          isnull(nombre,'')         as 'Razon Social',
      isnull(identificador, '') as 'Identificador',
      isnull(cuit,'')           as 'CUIT',
      isnull(rango_desde,0)     as 'Rango desde', 
      isnull(rango_hasta,0)     as 'Rango hasta', 
  isnull(telefonos,'')      as 'Teléfonos',
  isnull(cod_clae,'')       as 'Cod CIUU', 
  isnull(descripcion,'')    as 'CIUU',
  isnull(formulario,'')     as 'Formulario',
  isnull(cant_empleados,0)  as 'Cant. Empleados'
   from procesos_nivel_socio_economico_respuesta
   where cliente = @cliente 
     and fecha_proceso = @fecha_proceso
     order by cuit
 
     declare @cliente int = '263625984'
 declare @fecha_proceso date = '2018-08-06'
 
    select  
      isnull(d.cuit,'')           as 'CUIT',
  isnull(r.nombre,'')         as 'Razon Social',
      isnull(d.domicilio,'')      as 'Domicilio', 
      isnull(d.prv_nombre,'')     as 'Provincia',
  isnull(d.loc_nombre,'')     as 'Localidad',
  isnull(d.codpos,'')         as 'CP'
 
   from procesos_nivel_socio_economico_domicilios d
   inner join procesos_nivel_socio_economico_respuesta r on r.id_padron = d.id_padron
   where d.cliente = @cliente 
     and d.fecha_proceso = @fecha_proceso
     and d.cuit in (select top 1000 cuit from procesos_nivel_socio_economico_respuesta order by cuit)
 
         declare @cliente int = '263625984'
 declare @fecha_proceso date = '2018-08-06'
  select    isnull(d.cuit,'')           as 'CUIT',
  isnull(r.nombre,'')         as 'Razon Social',
          periodo,
      banco,
  situacion,
  deuda
    from procesos_nivel_socio_economico_bancos d
     inner join procesos_nivel_socio_economico_respuesta r on r.id_padron = d.id_padron
   where d.cliente = @cliente 
     and d.fecha_proceso = @fecha_proceso
     and d.cuit in (select top 1000 cuit from procesos_nivel_socio_economico_respuesta order by cuit)
 
             declare @cliente int = '263625984'
 declare @fecha_proceso date = '2018-08-06'
  select  isnull(d.cuit,'')           as 'CUIT',
  isnull(r.nombre,'')         as 'Razon Social',
     cuil, 
     nombre_empleado,
     periodo,
        sueldo,
     isnull(telefono,'') as 'Teléfonos'
    from procesos_nivel_socio_economico_empleados d
    inner join procesos_nivel_socio_economico_respuesta r on r.id_padron = d.id_padron
   where d.cliente = @cliente 
     and d.fecha_proceso = @fecha_proceso
     and d.cuit in (select top 1000 cuit from procesos_nivel_socio_economico_respuesta order by cuit)
 
   */ 
END
GO