dbo.LN_dashboard_graficos

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*

EXEC LN_dashboard_graficos 'GRAPH-CLI-PROV',3920694
EXEC LN_dashboard_graficos 'COUNT-CONTRAPARTES',3920694
EXEC LN_dashboard_graficos 'COUNT-DOCUMENTOS',3920694
EXEC LN_dashboard_graficos 'COUNT-CONTRAPARTES-RIESGO',3920694
EXEC LN_dashboard_graficos 'COUNT-CONTRAPARTES-RENEGOCIA-PRECIO',3920694
EXEC LN_dashboard_graficos 'COUNT-LINEA1',3920694
EXEC LN_dashboard_graficos 'GRAPH-CONTRAPARTES-CUMPLIMIENTO',3920694
EXEC LN_dashboard_graficos 'GRAPH-CONTRAPARTES-PAISES',3920694

*/

CREATE OR ALTER PROCEDURE dbo.LN_dashboard_graficos 
 @paccion varchar(50),
 @pcliente int
    
 
AS
BEGIN
 SET NOCOUNT ON;

 IF (@paccion = 'GRAPH_')
 BEGIN

  create table #rangos (
   rango     varchar(2),
   etiqueta    varchar(10),
   color     varchar(6),
   total     int)

  insert into #rangos VALUES ('R5','0-20','A72B2A',0)
  insert into #rangos VALUES ('R4','21-40','d37123',0)
  insert into #rangos VALUES ('R3','41-60','FFB81C',0)
  insert into #rangos VALUES ('R2','61-80','cbc367',0)
  insert into #rangos VALUES ('R1','81-100','63b68c',0)

 END

 DECLARE @TotalContrapartes int
 DECLARE @TotalDocumentos int
 DECLARE @renegocia_precio int
 DECLARE @enriesgo int
 DECLARE @TotalCumplimiento INT

 SELECT @TotalContrapartes = COUNT(*) 
 FROM LN_contrapartes
 inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte 
 inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
 inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
 where LN_contrapartes.cliente = @pcliente 
 and LN_gestiones.desde <= GETDATE()
 and LN_gestiones.hasta >= GETDATE()  
 and LN_contrapartes.activo=1
 and LN_contraparte_familias.activo = 1
 and LN_familias.visible = 1



 IF (@paccion = 'COUNT-LINEA1') 
 BEGIN

  SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @pcliente/* and aplica=1*/

  /*select @renegocia_precio = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
   select indicador from ln_indicadores where clave_indicador='RENTABILIDAD_OPERATIVA') 
   and valor_numerico > 20 and contraparte in
   (select distinct ln_contrapartes.contraparte 
   from ln_contrapartes inner join LN_gestiones on ln_contrapartes.contraparte = LN_gestiones.contraparte 
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1)
   and visible = 1*/

  select @renegocia_precio = count(*)
  from LN_contrapartes
  inner join LN_contraparte_familias on
     LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join LN_contraparte_indicadores on
    (LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
    and LN_gestiones.gestion = LN_contraparte_indicadores.gestion)
  inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
  where ln_contrapartes.cliente = @pcliente 
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and ln_contrapartes.activo = 1
  and LN_contraparte_familias.activo = 1
  and LN_familias.visible = 1
  and LN_indicadores.clave_indicador = 'BAL_RENTABILIDAD_VENTAS'
  and LN_contraparte_indicadores.valor_numerico > 20
  
   
   
  select @enriesgo = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
  select indicador from ln_indicadores where clave_indicador='SCORE_FINAL') 
  and valor_numerico < 50 and contraparte in
   (select distinct ln_contrapartes.contraparte 
   from ln_contrapartes inner join LN_gestiones on ln_contrapartes.contraparte = LN_gestiones.contraparte 
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1)
  
  
  SELECT @TotalContrapartes AS TotalContrapartes,@TotalDocumentos AS TotalDocumentos,@renegocia_precio AS renegocia_precio,@enriesgo AS enriesgo
 END


 IF (@paccion = 'GRAPH-CONTRAPARTES-PAISES') 
 BEGIN
 
  SELECT COUNT(LN_contrapartes.contraparte) AS PORC,LN_contrapartes.country_code,COUNTRY.country_name,
    @TotalContrapartes AS TotalContrapartes,(COUNT(LN_contrapartes.contraparte)*100)/@TotalContrapartes AS CANT
  FROM LN_contrapartes INNER JOIN country ON LN_contrapartes.country_code = country.country_code
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
  where LN_contrapartes.cliente = @pcliente 
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_contrapartes.activo=1
  and LN_contraparte_familias.activo = 1
  and LN_familias.visible = 1
  GROUP BY LN_contrapartes.country_code,COUNTRY.country_name
  ORDER BY COUNTRY.country_name ASC

 END


 IF (@paccion = 'GRAPH-CONTRAPARTES-CUMPLIMIENTO') 
 BEGIN
 
  select @TotalCumplimiento = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
  select INDICADOR from ln_indicadores INNER JOIN LN_cliente_productos ON LN_indicadores.producto=LN_cliente_productos.producto
  where clave_indicador='CUMPL_DOC' AND cliente = @pcliente and activo=1) 
  and contraparte in 
   (select distinct ln_contrapartes.contraparte 
   from ln_contrapartes inner join LN_gestiones on ln_contrapartes.contraparte = LN_gestiones.contraparte 
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1)
  and visible = 1 AND valor_numerico=100

  SELECT (@TotalCumplimiento * 100)/@TotalContrapartes AS cumplimiento_porc,@TotalContrapartes as TotalContrapartes,@TotalCumplimiento as cumplimiento_cant

 END



 IF (@paccion = 'COUNT-CONTRAPARTES-RENEGOCIA-PRECIO') 
 BEGIN
  /*select count(distinct contraparte) AS renegocia_precio from LN_contraparte_indicadores where indicador in (
   select indicador from ln_indicadores where clave_indicador='RENTABILIDAD_OPERATIVA') 
   and valor_numerico < 20 and contraparte in
   (select distinct ln_contrapartes.contraparte 
   from ln_contrapartes inner join LN_gestiones on ln_contrapartes.contraparte = LN_gestiones.contraparte 
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1)
   and visible = 1*/

  select count(*) AS renegocia_precio
  from LN_contrapartes 
  where LN_contrapartes.contraparte IN 
   (select ln_contrapartes.contraparte from LN_contrapartes
   inner join LN_contraparte_familias on
      LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_indicadores on
     (LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
     and LN_gestiones.gestion = LN_contraparte_indicadores.gestion)
   inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1
   and LN_indicadores.clave_indicador = 'BAL_RENTABILIDAD_VENTAS'
   and LN_contraparte_indicadores.valor_numerico > 20
   group by LN_contrapartes.contraparte)
  

  
 END


 IF (@paccion = 'COUNT-CONTRAPARTES-RIESGO') 
 BEGIN
  select count(distinct contraparte) as enriesgo from LN_contraparte_indicadores where indicador in (
  select indicador from ln_indicadores where clave_indicador='SCORE_FINAL') 
  and valor_numerico < 50 and contraparte in
   (select distinct ln_contrapartes.contraparte 
   from ln_contrapartes inner join LN_gestiones on ln_contrapartes.contraparte = LN_gestiones.contraparte 
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_contraparte_familias.familia = LN_familias.familia
   where ln_contrapartes.cliente = @pcliente 
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE()
   and ln_contrapartes.activo=1
   and LN_contraparte_familias.activo = 1
   and LN_familias.visible = 1)
  and visible = 1
 END

 IF (@paccion = 'COUNT-CONTRAPARTES') 
 BEGIN
  /*SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @pcliente and LN_contrapartes.activo=1*/
  SELECT @TotalContrapartes AS TotalContrapartes
 END

 IF (@paccion = 'COUNT-DOCUMENTOS') 
 BEGIN
  SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @pcliente/* and aplica=1*/
  SELECT @TotalDocumentos AS TotalDocumentos
 END






 IF (@paccion = 'GRAPH-CLI-PROV') 
 
 BEGIN

  SELECT null as rango, 'CLIENTES' as etiqueta,'A72B2A' as color, count(distinct LN_contrapartes.contraparte) as valor,@TotalContrapartes as total 
  from LN_familias
  inner join LN_contraparte_familias on LN_familias.familia = LN_contraparte_familias.familia
  inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  where LN_contrapartes.cliente = @pcliente
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_contrapartes.activo=1
  and LN_familias.uso_familia='C' 
  and LN_familias.visible = 1
  UNION
  SELECT null as rango, 'PROVEEDORES' as etiqueta,'FFB81C' as color, count(distinct LN_contrapartes.contraparte) as valor,@TotalContrapartes as total 
  from LN_familias
  inner join LN_contraparte_familias on LN_familias.familia = LN_contraparte_familias.familia
  inner join LN_contrapartes on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  where LN_contrapartes.cliente = @pcliente
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_contrapartes.activo=1
  and LN_familias.uso_familia='P' 
  and LN_familias.visible = 1
 
 END



 IF (@paccion = 'GRAPH-CONTRAPARTES-FAMILIAS') BEGIN

  SELECT COUNT(LN_contrapartes.contraparte) AS CANT, LN_familias.familia, LN_familias.nombre_familia,
    @TotalContrapartes AS TotalContrapartes,
    (COUNT(LN_contrapartes.contraparte)*100)/@TotalContrapartes AS PORC
  FROM LN_contrapartes 
  INNER JOIN LN_contraparte_familias ON LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
  INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  where LN_contrapartes.cliente = @pcliente 
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE()
  and LN_contrapartes.activo = 1
  and LN_familias.visible = 1
  and LN_contraparte_familias.activo = 1

  --and LN_contraparte_familias.familia in (124,125,126)

  GROUP BY LN_familias.familia, LN_familias.nombre_familia
  ORDER BY LN_familias.nombre_familia ASC

 END


END

GO