dbo.balances_reporte

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
exec balances_reporte 2017, 5546348,0,114
exec balances_reporte 2017, null,null,null,'AR'
*/
CREATE OR ALTER PROCEDURE dbo.balances_reporte  
@desde int = 2017,
@cliente int = null, 
@servicio int = null, 
@sector int = null,
@pais varchar(2) = null, 
@indice varchar(50) = 'ind_utilfin'
AS
BEGIN
    SET NOCOUNT ON;
 declare @identificador varchar(100)
 declare @cuit varchar(20)
 declare @nombre varchar(500)
 declare @anio int 
 declare @rentabilidad decimal(20,2)
 declare @solvencia    decimal(20,2)

 declare @clae int  = null
 declare @descripcion varchar(500) = null 
 declare @sectorn int
 declare @sectord varchar(500)
 declare @hasta int = @desde + 4
 declare @fecha_desde date = convert(date,convert(char(4),@desde) + '-01-01') 
 declare @fecha_hasta date = convert(date,convert(char(4),@hasta) + '-12-31')

 declare @anio1 int = @desde
 declare @anio2 int = @anio1 + 1
 declare @anio3 int = @anio1 + 2
 declare @anio4 int = @anio1 + 3
 declare @anio5 int = @anio1 + 4

 if @pais = '0' set @pais = null 
 if @servicio = 0 set @servicio = null 

 declare @identificadores table (identificador varchar(100))
 if (@pais is null)
  insert into @identificadores 
  select distinct identificador from identificadores
 else 
  insert into @identificadores 
  select distinct identificador from identificadores where country_code = @pais

 declare @servicios table (cod int, nombre varchar(200))
 insert into @servicios exec productos_led
 if (@servicio is not null) 
 begin
  delete from @servicios where cod != @servicio
 end 

 declare @reporte table (clientes_servicios varchar(max), 
                         identificador varchar(100), cuit varchar(100), 
                         nombre varchar(500), clae int, descripcion varchar(500),
                         sectorn int, sectord varchar(500), 
                         anio1 int, rentabilidad1 decimal(20,2),
                         anio2 int, rentabilidad2 decimal(20,2),
                         anio3 int, rentabilidad3 decimal(20,2),
                         anio4 int, rentabilidad4 decimal(20,2),
                         anio5 int, rentabilidad5 decimal(20,2))

 if (@cliente is null and @servicio is null)
 begin
 DECLARE C_cursor CURSOR FOR
  select identificador, cuit, /*fecha_balance,*/ year(fecha_balance) as anio, nombre_persona, 
   --iif(res_vtanet!=0,(ganancia / res_vtanet) * 100,0) as rentabilidad,
   ind_utilfin,ind_solvencia_pat, sectores.cod_clae, sectores.clae, sectores.cod_ramo, sectores.ramo 
   from balances_contables
   inner join padrones on (padrones.identificacion = balances_contables.cuit and habilitado = 1 )
   OUTER APPLY
   (SELECT TOP 1 
      cod_clae,   
      clae.descripcion as clae,
      r1.cod_ramo, 
      r2.descripcion as ramo
     from clae
     left join ramos r1 on r1.cod_subramo = clae.cod_clae 
     left join ramos r2 on (r2.cod_ramo = r1.cod_ramo and r2.tipo_ramo = 'R')
     where clae.cuit = balances_contables.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
     order by principal desc
   ) as sectores
   where balances_contables.visible = 1 and estado  = 'PUBLICADO' 
   and fecha_balance between @fecha_desde and @fecha_hasta 
   and bal_manifestacion = 'B'
   and identificador in (select identificador from @identificadores)
 end
 else
 begin 
 DECLARE C_cursor CURSOR FOR

  select identificador, cuit, /*fecha_balance,*/ year(fecha_balance) as anio, nombre_persona, 
   --iif(res_vtanet!=0,(ganancia / res_vtanet) * 100,0) as rentabilidad,
   ind_utilfin, ind_solvencia_pat, sectores.cod_clae, sectores.clae, sectores.cod_ramo, sectores.ramo 
   from balances_contables
   inner join padrones on (padrones.identificacion = balances_contables.cuit and habilitado = 1 ) 
   
   inner join ln_contrapartes on padrones.id_padron = ln_contrapartes.id_padron 
   inner join ln_gestiones on ln_gestiones.contraparte = ln_contrapartes.contraparte
   inner join ln_contraparte_familias on ln_contraparte_familias.contraparte = ln_contrapartes.contraparte
   inner join ln_familias on ln_familias.familia = ln_contraparte_familias.familia

   OUTER APPLY
   (SELECT TOP 1 
      cod_clae,   
      clae.descripcion as clae,
      r1.cod_ramo, 
      r2.descripcion as ramo
     from clae
     left join ramos r1 on r1.cod_subramo = clae.cod_clae 
     left join ramos r2 on (r2.cod_ramo = r1.cod_ramo and r2.tipo_ramo = 'R')
     where clae.cuit = balances_contables.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
     order by principal desc
   ) as sectores

   where  balances_contables.visible = 1 and estado  = 'PUBLICADO' 
   and fecha_balance between @fecha_desde and @fecha_hasta 
   and bal_manifestacion = 'B'

   and ln_contrapartes.cliente = @cliente and ln_contrapartes.activo = 1 
   and ln_gestiones.desde <= getdate() and ln_gestiones.hasta >= getdate() and ln_gestiones.vigente = 1
   and ln_familias.visible = 1
   and ln_contrapartes.servicio_contratado in (select cod from @servicios) 
   and identificador in (select identificador from @identificadores)

 end 
 
 OPEN C_cursor
 FETCH NEXT FROM C_cursor INTO @identificador, @cuit, @anio, @nombre, @rentabilidad, @solvencia, @clae, @descripcion, @sectorn, @sectord
 WHILE @@fetch_status = 0
 BEGIN
  if (@sector is not null and @sector != isnull(@sectorn,0))
   begin 
    FETCH NEXT FROM C_cursor INTO @identificador, @cuit, @anio, @nombre, @rentabilidad, @solvencia, @clae, @descripcion, @sectorn, @sectord
    continue
   end 

  declare @clientes_servicios   varchar(max) = '' 
  declare @nombre_cliente    varchar(500)
  declare @cod_cliente    int 
  declare @servicio_contratado  varchar(200)
  declare @cliente_ant varchar(500) = '' 
  DECLARE res2 CURSOR
  FOR
      select distinct clientes.cliente, clientes.nombre_cliente, (select nombre from @servicios where cod = ln_contrapartes.servicio_contratado) as servcio
   from ln_contrapartes 
   inner join clientes on clientes.cliente = ln_contrapartes.cliente 
   inner join padrones on padrones.id_padron = ln_contrapartes.id_padron 
   inner join ln_gestiones on ln_gestiones.contraparte = ln_contrapartes.contraparte
   inner join ln_contraparte_familias on ln_contraparte_familias.contraparte = ln_contrapartes.contraparte
   inner join ln_familias on ln_familias.familia = ln_contraparte_familias.familia
   where ln_contrapartes.activo = 1 
   and ln_gestiones.desde <= getdate() and ln_gestiones.hasta >= getdate() and ln_gestiones.vigente = 1
   and ln_familias.visible = 1
   and identificacion = @cuit and ln_contrapartes.servicio_contratado in (select cod from @servicios)
   order by clientes.nombre_cliente

  OPEN res2 
  FETCH next from res2 INTO @cod_cliente, @nombre_cliente, @servicio_contratado
  WHILE (@@FETCH_STATUS <> -1 )
   BEGIN
    if (@cliente is not null and @cod_cliente != @cliente)
    begin
     FETCH next from res2 INTO  @cod_cliente, @nombre_cliente, @servicio_contratado 
     continue
    end 
    if (@clientes_servicios = '')
    begin
     set @clientes_servicios =  @nombre_cliente + ' [' + @servicio_contratado + ']'
    end
    else
    begin
    if (@nombre_cliente = @cliente_ant)
     set @clientes_servicios = @clientes_servicios + ' [' + @servicio_contratado + ']'
    else 
     set @clientes_servicios = @clientes_servicios + '; ' + @nombre_cliente + ' [' + @servicio_contratado + ']'
    end
    set @cliente_ant = @nombre_cliente
    FETCH next from res2 INTO  @cod_cliente, @nombre_cliente, @servicio_contratado 
   END
  CLOSE res2
  DEALLOCATE res2 

  if @clientes_servicios = '' set @clientes_servicios = null 

  if (@indice = 'ind_solvencia_pat') set @rentabilidad = @solvencia

  if (@anio = @anio1) 
  begin
  if not exists (select * from @reporte where cuit = @cuit)
   insert into @reporte (clientes_servicios,identificador,cuit, nombre, anio1, rentabilidad1,clae,descripcion,sectorn,sectord) 
   values (@clientes_servicios, @identificador,@cuit,@nombre,@anio1,@rentabilidad,@clae,@descripcion,@sectorn,@sectord)
  else 
   update @reporte set anio1 = @anio1, rentabilidad1 = @rentabilidad 
   where cuit = @cuit
  end 

  if (@anio = @anio2) 
  begin
  if not exists (select * from @reporte where cuit = @cuit)
   insert into @reporte (clientes_servicios,identificador, cuit, nombre, anio2, rentabilidad2,clae,descripcion,sectorn,sectord) 
   values (@clientes_servicios,@identificador,@cuit,@nombre,@anio2,@rentabilidad,@clae,@descripcion,@sectorn,@sectord)
  else 
   update @reporte set anio2 = @anio2, rentabilidad2 = @rentabilidad 
   where cuit = @cuit
  end 

  if (@anio = @anio3) 
  begin
  if not exists (select * from @reporte where cuit = @cuit)
   insert into @reporte (clientes_servicios,identificador, cuit, nombre, anio3, rentabilidad3,clae,descripcion,sectorn,sectord) 
   values (@clientes_servicios,@identificador,@cuit,@nombre,@anio3,@rentabilidad,@clae,@descripcion,@sectorn,@sectord)
  else 
   update @reporte set anio3 = @anio3, rentabilidad3 = @rentabilidad 
   where cuit = @cuit
  end 
   
  if (@anio = @anio4) 
  begin
  if not exists (select * from @reporte where cuit = @cuit)
   insert into @reporte (clientes_servicios,identificador, cuit, nombre, anio4, rentabilidad4,clae,descripcion,sectorn,sectord) 
   values (@clientes_servicios,@identificador,@cuit,@nombre,@anio4,@rentabilidad,@clae,@descripcion,@sectorn,@sectord)
  else 
   update @reporte set anio4 = @anio4, rentabilidad4 = @rentabilidad 
   where cuit = @cuit
  end 

  if (@anio = @anio5) 
  begin
  if not exists (select * from @reporte where cuit = @cuit)
   insert into @reporte (clientes_servicios,identificador, cuit, nombre, anio5, rentabilidad5,clae,descripcion,sectorn,sectord) 
   values (@clientes_servicios,@identificador, @cuit,@nombre,@anio5,@rentabilidad,@clae,@descripcion,@sectorn,@sectord)
  else 
   update @reporte set anio5 = @anio5, rentabilidad5 = @rentabilidad 
   where cuit = @cuit
  end 
        
  FETCH NEXT FROM C_cursor INTO @identificador, @cuit, @anio, @nombre, @rentabilidad, @solvencia, @clae, @descripcion, @sectorn, @sectord

 END
 CLOSE C_cursor
 DEALLOCATE C_cursor

 select reporte.* from @reporte reporte order by nombre
END
GO