dbo.sp_brg

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- [sp_brg] 4
CREATE OR ALTER PROCEDURE dbo.sp_brg  -- ver el select de los cursores (bn2 y res) y cambiarlos al que se necesite
        -- y el ultimo select, que es el resultado que se necesita
@proceso int = 1
AS
BEGIN
    SET NOCOUNT ON;
 
 declare @r_cuit varchar(20)
 declare @nombre varchar(200)

 declare @cod_clae varchar(10), 
   @descripcion varchar(512), 
   @fecha_inicio_actividad date,
   @principal bit,
   @tipo varchar(max)
 declare @respuesta table (  cuit varchar(20),
        nombre varchar(200),
                             cod_clae varchar(10), 
        descripcion varchar(512))

 declare @empleadores table (cuit varchar(20),
        nombre varchar(200),
                             cuit_empleador varchar(20), 
        nombre_empleador varchar(200),
        mes varchar(7))
 declare @cuit_empleador varchar(20), 
      @nombre_empleador varchar(200), 
   @periodo int,
   @periodo_char varchar(7)
 declare @esta int
 declare @actividades varchar(max)
 declare @empleadores_todos varchar(max)
 declare @resultado table (cuit varchar(20), nombre varchar(200), tipo varchar(max), actividades varchar(max),empleadores varchar(max))

 declare @clae char(1), @emp char(1)
  
 DECLARE bn2 CURSOR

  FOR
  /*
   select distinct cuit_prov , razon_social_prov
     from screening_prov_out2 
    where cliente = 7777
    and nro_proceso = @proceso
   */

  select distinct cuit, razon_social, 'Ex-employee' as tipo
     from screening_prov 
    where cliente = 7777
    and nro_proceso = @proceso
    and tipo_reg = 'PRV' 
    and lower(relacion) like 'ex-employee%'
  union 
  select distinct a.cuit, a.razon_social, 'FAMILIAR DE ' + a.cuit_titular + ' ' +  p.nombre_persona as tipo
       from screening_prov a
    inner join padrones p on identificacion = a.cuit_titular and habilitado = 1
    where a.cliente = 7777
    and a.nro_proceso = @proceso
    and a.tipo_reg = 'FAM'
    and not exists (select * from screening_prov b 
                     where b.cuit = a.cuit 
         and   b.tipo_reg = 'PRV' and lower(relacion) like 'employee%')

  
  /*
   select a.cuil, b.nombre_persona
   from brg3_worldsys a
   inner join padrones b on b.identificacion = a.cuil and b.habilitado = 1
   where a.cuil not in (select cuit from procesos_cuit where cliente = 7777 and fecha = '2018-04-17')
   */
  OPEN bn2 
  Fetch next from bn2 INTO  @r_cuit, @nombre, @tipo

  WHILE (@@FETCH_STATUS <> -1 )
 
  BEGIN 
  -- Clae 
   DECLARE clae2 CURSOR
   FOR
     select distinct
      clae.cod_clae, 
      clae.descripcion, 
      principal
      from clae
     where clae.cuit = @r_cuit 
       and clae.visible = 1
       and clae.descripcion is not null
     order by principal desc

   OPEN clae2 
   set @clae = 'N'
   FETCH next from clae2 INTO @cod_clae, @descripcion, @principal
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
        set @clae = 'S'
     set @esta = 0
     set @esta = (select count(*) from @respuesta where cuit = @r_cuit and nombre = @nombre and cod_clae = @cod_clae)
     if (@esta = 0)
     begin
      insert into @respuesta 
      values (@r_cuit, 
              @nombre, 
        @cod_clae,
        @descripcion)
        end
     FETCH next from clae2 INTO @cod_clae, @descripcion, @principal
    END
   CLOSE clae2
   DEALLOCATE clae2 
   if (@clae = 'N')  
    begin
     insert into @respuesta 
        values (@r_cuit, 
         @nombre, 
         '',
         '')
   end 
   --  Empleadores

   DECLARE emp CURSOR
   FOR
     select distinct cuit, 
      nombre_persona, 
      periodo
      from empleados
      left join padrones on padrones.identificacion = empleados.cuit and habilitado = 1
     where cuil = @r_cuit 
       and periodo = (select MAX(periodo) from empleados where cuil = @r_cuit and periodo >= '1701')
     
   set @emp = 'N'
   OPEN emp 
   FETCH next from emp INTO @cuit_empleador, @nombre_empleador, @periodo
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     set @emp = 'S'
     begin
         set @periodo_char = @periodo
      set @periodo_char = '20' + SUBSTRING(@periodo_char,1,2) + '-' +SUBSTRING(@periodo_char,3,2)
      insert into @empleadores 
      values (@r_cuit, 
              @nombre, 
        @cuit_empleador, 
        @nombre_empleador,
        @periodo_char)
        end
     FETCH next from emp INTO @cuit_empleador, @nombre_empleador, @periodo
    END
   CLOSE emp
   DEALLOCATE emp 
   if (@emp = 'N')  
    begin
     insert into @empleadores 
      values (@r_cuit, 
              @nombre, 
        '', 
        '',
        '')
   end 
    

   Fetch next from bn2 INTO  @r_cuit, @nombre, @tipo

  END
  
  CLOSE bn2
  DEALLOCATE bn2 


  DECLARE res CURSOR

  FOR
   /*
    select distinct cuit_prov , razon_social_prov
      from screening_prov_out2 
     where cliente = 7777
     and nro_proceso = @proceso
    */
  
  select distinct cuit, razon_social, 'Ex-employee' as tipo
     from screening_prov 
    where cliente = 7777
    and nro_proceso = @proceso
    and tipo_reg = 'PRV' 
    and lower(relacion) like 'ex-employee%'
  union 
  select distinct a.cuit, a.razon_social, 'FAMILIAR DE ' + a.cuit_titular + ' ' +  p.nombre_persona as tipo
       from screening_prov a
    inner join padrones p on identificacion = a.cuit_titular and habilitado = 1
    where a.cliente = 7777
    and a.nro_proceso = @proceso
    and a.tipo_reg = 'FAM'
    and not exists (select * from screening_prov b 
                     where b.cuit = a.cuit 
         and   b.tipo_reg = 'PRV' and lower(relacion) like 'employee%')
  
  /*
   select a.cuil, b.nombre_persona
   from brg3_worldsys a
   inner join padrones b on b.identificacion = a.cuil and b.habilitado = 1
   where a.cuil not in (select cuit from procesos_cuit where cliente = 7777 and fecha = '2018-04-17')
   */
   
  OPEN res 
  Fetch next from res INTO  @r_cuit, @nombre, @tipo

  WHILE (@@FETCH_STATUS <> -1 )
 
  BEGIN 
   set @actividades = ''
   DECLARE res2 CURSOR
   FOR
     select cod_clae, descripcion
      from @respuesta
     where cuit = @r_cuit

   OPEN res2 
   FETCH next from res2 INTO @cod_clae, @descripcion
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@cod_clae != '')
     begin
     if (@actividades = '') 
     begin
      set @actividades = '[' + @cod_clae + '] ' + replace(@descripcion,'-',' ')
     end
     else
     begin
      set @actividades = @actividades + ' - [' + @cod_clae + '] ' + @descripcion
     end 
     end 
     FETCH next from res2 INTO @cod_clae, @descripcion
    END
   CLOSE res2
   DEALLOCATE res2 

   set @empleadores_todos = ''
   DECLARE res3 CURSOR
   FOR
     select cuit_empleador, nombre_empleador, mes
      from @empleadores
     where cuit = @r_cuit

   OPEN res3 
   FETCH next from res3 INTO @cuit_empleador, @nombre_empleador, @periodo_char
   WHILE (@@FETCH_STATUS <> -1 )
    BEGIN
     if (@periodo_char != '')
     begin
     if (@empleadores_todos = '') 
     begin
      set @empleadores_todos = '[' + @periodo_char + '] ' + replace(@cuit_empleador,'-',' ') + ' ' + replace(@nombre_empleador,'-',' ')
     end
     else
     begin
      set @empleadores_todos = @empleadores_todos + ' - ' +  @cuit_empleador + ' ' + @nombre_empleador
     end 
     end 
     FETCH next from res3 INTO @cuit_empleador, @nombre_empleador, @periodo_char
    END
   CLOSE res3
   DEALLOCATE res3 

   insert into @resultado values (@r_cuit, @nombre, @tipo, @actividades,@empleadores_todos)
   Fetch next from res INTO  @r_cuit, @nombre, @tipo

  END
  
  CLOSE res
  DEALLOCATE res 

  /*
   select @proceso as 'Nro Proceso', 
          r.cuit as 'CUIT', 
      isnull(sp.vendorid,'') as 'VendorId',
      r.nombre  as 'Nombre Prov',
      r.actividades as 'Actividades',
      r.empleadores as 'Empleadores' 
     from @resultado r
     left join screening_prov sp
      on sp.cliente = 7777
     and sp.cuit = r.cuit
     and sp.nro_proceso = @proceso
       */
  select * from @resultado
 
   
END 
 

GO