Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
3| Name | Data Type | Length | Description |
|---|---|---|---|
| @accion | varchar | 50 | |
| @p_cliente | int | 4 | |
| @contrap | int | 4 |
SQL Script
Copy
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
EXEC LN_dashboard_graficos_pre 'GRAFICO_SCORE_FINAL', 5546348
*/
CREATE OR ALTER PROCEDURE dbo.LN_dashboard_graficos_pre
@accion varchar(50),
@p_cliente int,
@contrap int = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TotalContrapartes int
DECLARE @TotalDocumentos int
DECLARE @renegocia_precio int
DECLARE @enriesgo int
DECLARE @TotalCumplimiento INT
DECLARE @rechazoAuditoria int
DECLARE @CountAltaDepEcon int
IF (@accion = 'GRAFICO_INFORME_COMERCIAL' or @accion = 'GRAFICO_SCORE_BALANCE' or @accion = 'GRAFICO_SCORE_FINAL' or @accion = 'GRAFICO_SCORE_DOCUMENTOS' or @accion='GRAFICO_SCORE_DOSSIER' or @accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
create table #rangos (
rango varchar(2),
etiqueta varchar(10),
etiqueta_texto varchar(100) NULL,
color varchar(6),
total int,
desde float,
hasta float)
END
IF (@accion = 'GRAFICOS_INDICADORES') BEGIN
select LN_productos.producto,ln.clave_indicador,LN_productos.etiqueta,
LN_productos.producto_id, LN_productos.balance, LN_productos.balance_carga,
LN_productos.balance_score, LN_productos.balance_analisis, LN_productos.balance_valoracion,
LN_productos.documentos, LN_productos.subcontratistas
from LN_cliente_indicadores lni
inner join LN_indicadores ln on ln.indicador = lni.indicador
inner join ln_productos on ln_productos.producto = ln.producto
where cliente = @p_cliente
and LN_productos.nombre_producto = 'graficos_dashboard'
and lni.activo = 1
order by lni.created asc
END
/*
IF (@accion = '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*/
IF (@accion = 'GRAPH_CONTRAPARTES_FAMILIAS') BEGIN
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 = @p_cliente
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 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 = @p_cliente
and LN_gestiones.desde <= GETDATE()
and LN_gestiones.hasta >= GETDATE()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
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
IF (@accion = 'COUNT_CONTRAPARTES')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes
inner join LN_gestiones
on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias lcf
on lcf.contraparte = LN_contrapartes.contraparte
inner join LN_familias lf
on lf.familia = lcf.familia
WHERE LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and lf.visible = 1 and lcf.activo = 1
and LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
SELECT @TotalContrapartes AS TotalContrapartes
END
IF (@accion = 'COUNT_DOCUMENTOS')
BEGIN
SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @p_cliente and LN_docs.aplica = 1 and estado_doc != 'X'
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
SELECT @TotalDocumentos AS TotalDocumentos
END
IF (@accion = 'COUNT_RENEGOCIA_PRECIO')
BEGIN
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
SELECT @renegocia_precio AS renegocia_precio
END
IF (@accion = 'COUNT_ENRIESGO')
BEGIN
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @enriesgo AS enriesgo
END
/*
select * from padrones where identificacion = 30709637084
select * from ln_contrapartes where cliente = '5546348'
select * from ln_indicadores where indicador in (
select indicador from LN_contraparte_indicadores where contraparte = '95')*/
--select * from LN_contraparte_indicadores where contraparte = '99' and indicador = 47)*/
IF (@accion = 'COUNT_RECHAZO_AUDITORIA')
BEGIN
select @rechazoAuditoria = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
select indicador from ln_indicadores where clave_indicador='HOMOLOG')
and valor_numerico = 0
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @rechazoAuditoria AS rechazoAuditoria
END
IF (@accion = 'COUNT_ALTA_DEP_ECONOMICA')
BEGIN
select @CountAltaDepEcon = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
select indicador from ln_indicadores where clave_indicador='DEP_ECON_CALC')
and valor_numerico > 70.00
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @CountAltaDepEcon AS CountAltaDepEcon
END
IF (@accion = 'COUNT-LINEA1')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1 and subcontratista = 0
SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @p_cliente and aplica=1 and estado_doc != 'X'
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
SELECT @TotalContrapartes AS TotalContrapartes,@TotalDocumentos AS TotalDocumentos,@renegocia_precio AS renegocia_precio,@enriesgo AS enriesgo
END
--FIN CONTADORES
IF (@accion = 'GRAPH_CONTRAPARTES_PAISES')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and subcontratista = 0
SELECT COUNT(CONTRAPARTE) AS CANT,LN_contrapartes.country_code,COUNTRY.country_name,@TotalContrapartes AS TotalContrapartes,(COUNT(CONTRAPARTE)*100)/@TotalContrapartes AS PORC
FROM LN_contrapartes INNER JOIN country ON LN_contrapartes.country_code = country.country_code
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
GROUP BY LN_contrapartes.country_code,COUNTRY.country_name
ORDER BY COUNTRY.country_name ASC
END
IF (@accion = 'GRAPH_CONTRAPARTES_CUMPLIMIENTO')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
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 = @p_cliente and activo=1)
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1 AND valor_numerico=100
SELECT (@TotalCumplimiento * 100)/@TotalContrapartes AS cumplimiento_porc,@TotalContrapartes as TotalContrapartes,@TotalCumplimiento as cumplimiento_cant
END
IF (@accion = 'GRAPH_CLI_PROV')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
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
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo=1
and LN_contrapartes.subcontratista=0
and LN_familias.uso_familia='C'
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
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo=1
and LN_contrapartes.subcontratista = 0
and LN_familias.uso_familia='P'
END
/*** GRAFICOS DASHBOARD ***/
IF (@accion = 'GRAFICO_INFORME_COMERCIAL') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
DECLARE @cant INT
SELECT @cant = COUNT(*)
FROM LN_contrapartes
inner join LN_gestiones
on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias lcf
on lcf.contraparte = LN_contrapartes.contraparte
inner join LN_familias lf
on lf.familia = lcf.familia
WHERE LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and lf.visible = 1 and lcf.activo = 1
AND LN_contrapartes.activo = 1
AND LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.subcontratista = 0
SELECT ISNULL(totales.clave_indicador,'SCORE_IC') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREIC.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_IC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente
) SCOREIC
GROUP BY SCOREIC.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_SCORE_BALANCE') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
SELECT ISNULL(totales.clave_indicador,'SCORE_BAL') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREBAL.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
/*when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 81 then 'R1'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 61 then 'R2'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 41 then 'R3'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 21 then 'R4'
when format(LN_contraparte_indicadores.valor_numerico,'##0') < 21 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_BAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREBAL
GROUP BY SCOREBAL.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_SCORE_FINAL') BEGIN
if (select COUNT(*) from ln_cliente_indicadores
inner join LN_indicadores on LN_indicadores.indicador = LN_cliente_indicadores.indicador
where clave_indicador = 'SCORE_FINAL'
and cliente = @p_cliente) > 0
begin
insert into #rangos
select 'R'+convert(varchar,ROW_NUMBER() OVER(ORDER BY desde ASC)) AS Row,
texto, texto, color_fondo, 0, desde, hasta
from LN_cliente_nivel where cliente = @p_cliente
and desde >= -100
and indicador in (
select distinct LN_indicadores.indicador from ln_cliente_indicadores
inner join LN_indicadores on LN_indicadores.indicador = LN_cliente_indicadores.indicador
where clave_indicador = 'SCORE_FINAL'
and cliente = @p_cliente)
order by desde desc
end
else
begin
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 20, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 40, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 60, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 80, 101)
end
SELECT ISNULL(totales.clave_indicador,'SCORE_FINAL') as clave_indicador, #rangos.rango, #rangos.etiqueta,
#rangos.etiqueta_texto, #rangos.color, ISNULL(totales.total,0) as total,
#rangos.desde, #rangos.hasta
FROM #rangos
LEFT OUTER JOIN (
SELECT SF.clave_indicador, SF.rango, SF.etiqueta, SF.etiqueta_texto, SF.color, COUNT(*) AS total
FROM
(select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
(select rango from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as rango,
(select etiqueta from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as etiqueta,
(select etiqueta_texto from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as etiqueta_texto,
(select color from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as color
from LN_contraparte_indicadores
inner join LN_contrapartes on LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
where LN_contrapartes.cliente = @p_cliente
and LN_indicadores.clave_indicador = 'SCORE_FINAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0) SF
GROUP BY SF.clave_indicador, SF.rango, SF.etiqueta, SF.etiqueta_texto, SF.color
) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
/* VERSION ANTERIOR
SELECT ISNULL(totales.clave_indicador,'SCORE_FINAL') as clave_indicador, #rangos.rango, #rangos.etiqueta,
#rangos.etiqueta_texto, #rangos.color, ISNULL(totales.total,0) as total,
#rangos.desde, #rangos.hasta
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREFIN.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'
END AS color,
COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END
AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_FINAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREFIN
GROUP BY SCOREFIN.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
*/
END
IF (@accion = 'GRAFICO_SCORE_DOCUMENTOS') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
SELECT ISNULL(totales.clave_indicador,'SCORE_DOC') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREDOC.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREDOC
GROUP BY SCOREDOC.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_FIDECHECK') BEGIN
create table #rangospep (
rango varchar(2),
etiqueta varchar(20),
color varchar(6),
total int)
insert into #rangospep VALUES ('R3','Positivo','A72B2A',0)
insert into #rangospep VALUES ('R2','Relacionado','FFB81C',0)
insert into #rangospep VALUES ('R1','Negativo','63b68c',0)
SELECT ISNULL(totales.clave_indicador,'FIDECHECK') as clave_indicador, totales.valor_texto, #rangospep.rango, #rangospep.etiqueta, #rangospep.color, ISNULL(totales.total,0) as total
FROM #rangospep
LEFT OUTER JOIN (
SELECT FIDECHECK.clave_indicador,rango, FIDECHECK.valor_texto,
CASE
when rango = 'R1' then 'Negativo'
when rango = 'R2' then 'Relacionado'
when rango = 'R3' then 'Positivo'
END AS etiqueta,
CASE
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'FFB81C'
when rango = 'R3' then 'A72B2A'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
LN_contraparte_indicadores.valor_texto,
case
when LN_contraparte_indicadores.valor_texto = 'N' then 'R1'
when LN_contraparte_indicadores.valor_texto = 'R' then 'R2'
when LN_contraparte_indicadores.valor_texto = 'P' then 'R3'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
inner join LN_familias lf
on lf.familia = LN_familia_productos.familia
WHERE LN_indicadores.clave_indicador = 'FIDECHECK'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
and lf.visible = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) FIDECHECK
GROUP BY FIDECHECK.clave_indicador, rango, FIDECHECK.valor_texto) totales ON #rangospep.rango = totales.rango
ORDER BY #rangospep.rango
DROP TABLE #rangospep
END
IF (@accion = 'GRAFICO_SCORE_DOSSIER') BEGIN
SELECT ISNULL(totales.clave_indicador,'SCORE_DOSSIER') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREDOSSIER.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOSSIER'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREDOSSIER
GROUP BY SCOREDOSSIER.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
/*
select LN_contraparte_indicadores.indicador,LN_indicadores.clave_indicador
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOSSIER'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente
*/
END
IF (@accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
SELECT ISNULL(totales.clave_indicador,'SCORE_DEP_ECON') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT DEPECON.clave_indicador, rango,
CASE
when rango = 'R3' then '71-100'
when rango = 'R2' then '51-70'
when rango = 'R1' then '0-50'
END AS etiqueta,
CASE
when rango = 'R3' then 'A72B2A'
when rango = 'R2' then 'FFB81C'
when rango = 'R1' then '63b68c'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 71 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 51 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R1'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'DEP_ECON_CALC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) DEPECON
GROUP BY DEPECON.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_CONTROL_INGRESO') BEGIN
DECLARE @contrapartes_excepcion varchar(max) = '0'
DECLARE @total_excepciones int = 0
DECLARE Excepciones CURSOR FOR
select LN_excepciones.contraparte
from LN_excepciones
inner join LN_gestiones on (LN_excepciones.contraparte = LN_gestiones.contraparte
and LN_excepciones.gestion = LN_gestiones.gestion)
inner join LN_contraparte_indicadores on (LN_gestiones.contraparte = LN_contraparte_indicadores.contraparte
and LN_gestiones.gestion = LN_contraparte_indicadores.gestion)
inner join LN_contrapartes on LN_excepciones.contraparte = LN_contrapartes.contraparte
inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_gestiones.desde <= GETDATE()
and LN_gestiones.hasta >= GETDATE()
and LN_indicadores.clave_indicador = 'EXCEPCION'
--and LN_excepciones.estado = 1
and LN_contraparte_indicadores.visible = 1
and LN_excepciones.vigencia >= GETDATE()
group by LN_excepciones.contraparte
OPEN Excepciones
FETCH NEXT FROM Excepciones INTO @contrap
WHILE @@FETCH_STATUS = 0 BEGIN
SET @total_excepciones = @total_excepciones + 1
SET @contrapartes_excepcion = @contrapartes_excepcion + ',' + convert(varchar(10),@contrap)
FETCH NEXT FROM Excepciones INTO @contrap
END
CLOSE Excepciones
DEALLOCATE Excepciones
SELECT CASE
WHEN rango != 'R2' then indicador
ELSE (select indicador from LN_indicadores where clave_indicador = 'EXCEPCION')
END AS indicador,
rango, valor_numerico as valor,
case
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'FFB81C'
when rango = 'R3' then 'A72B2A'
end as color,
count(*) as total,
case
when rango = 'R1' then 'Aprobado'
when rango = 'R2' then 'Excepción'
when rango = 'R3' then 'Rechazado'
end as etiqueta
from (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte,
iif (excepcion.Item is null, LN_contraparte_indicadores.valor_numerico, 1) as valor_numerico,
iif (excepcion.Item is null,
case
when LN_contraparte_indicadores.valor_numerico > 0 then 'R1'
when LN_contraparte_indicadores.valor_numerico = 0 then 'R3'
else NULL end,
'R2') AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
left outer join dbo.DelimitedSplit8K(@contrapartes_excepcion, ',') excepcion ON LN_contrapartes.contraparte = convert(int, excepcion.Item)
WHERE LN_indicadores.clave_indicador = 'HOMOLOG'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) data
group by indicador, rango, valor_numerico
END
IF (@accion = 'GRAFICO_INFORME_COMERCIAL' or @accion = 'GRAFICO_SCORE_BALANCE' or @accion = 'GRAFICO_SCORE_FINAL' or @accion = 'GRAFICO_SCORE_DOCUMENTOS' or @accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
DROP TABLE #rangos
END
/*** FIN GRAFICOS DASHBOARD ***/
END
GO
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
EXEC LN_dashboard_graficos_pre 'GRAFICO_SCORE_FINAL', 5546348
*/
CREATE OR ALTER PROCEDURE dbo.LN_dashboard_graficos_pre
@accion varchar(50),
@p_cliente int,
@contrap int = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TotalContrapartes int
DECLARE @TotalDocumentos int
DECLARE @renegocia_precio int
DECLARE @enriesgo int
DECLARE @TotalCumplimiento INT
DECLARE @rechazoAuditoria int
DECLARE @CountAltaDepEcon int
IF (@accion = 'GRAFICO_INFORME_COMERCIAL' or @accion = 'GRAFICO_SCORE_BALANCE' or @accion = 'GRAFICO_SCORE_FINAL' or @accion = 'GRAFICO_SCORE_DOCUMENTOS' or @accion='GRAFICO_SCORE_DOSSIER' or @accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
create table #rangos (
rango varchar(2),
etiqueta varchar(10),
etiqueta_texto varchar(100) NULL,
color varchar(6),
total int,
desde float,
hasta float)
END
IF (@accion = 'GRAFICOS_INDICADORES') BEGIN
select LN_productos.producto,ln.clave_indicador,LN_productos.etiqueta,
LN_productos.producto_id, LN_productos.balance, LN_productos.balance_carga,
LN_productos.balance_score, LN_productos.balance_analisis, LN_productos.balance_valoracion,
LN_productos.documentos, LN_productos.subcontratistas
from LN_cliente_indicadores lni
inner join LN_indicadores ln on ln.indicador = lni.indicador
inner join ln_productos on ln_productos.producto = ln.producto
where cliente = @p_cliente
and LN_productos.nombre_producto = 'graficos_dashboard'
and lni.activo = 1
order by lni.created asc
END
/*
IF (@accion = '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*/
IF (@accion = 'GRAPH_CONTRAPARTES_FAMILIAS') BEGIN
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 = @p_cliente
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 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 = @p_cliente
and LN_gestiones.desde <= GETDATE()
and LN_gestiones.hasta >= GETDATE()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
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
IF (@accion = 'COUNT_CONTRAPARTES')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes
inner join LN_gestiones
on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias lcf
on lcf.contraparte = LN_contrapartes.contraparte
inner join LN_familias lf
on lf.familia = lcf.familia
WHERE LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and lf.visible = 1 and lcf.activo = 1
and LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
SELECT @TotalContrapartes AS TotalContrapartes
END
IF (@accion = 'COUNT_DOCUMENTOS')
BEGIN
SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @p_cliente and LN_docs.aplica = 1 and estado_doc != 'X'
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
SELECT @TotalDocumentos AS TotalDocumentos
END
IF (@accion = 'COUNT_RENEGOCIA_PRECIO')
BEGIN
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
SELECT @renegocia_precio AS renegocia_precio
END
IF (@accion = 'COUNT_ENRIESGO')
BEGIN
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @enriesgo AS enriesgo
END
/*
select * from padrones where identificacion = 30709637084
select * from ln_contrapartes where cliente = '5546348'
select * from ln_indicadores where indicador in (
select indicador from LN_contraparte_indicadores where contraparte = '95')*/
--select * from LN_contraparte_indicadores where contraparte = '99' and indicador = 47)*/
IF (@accion = 'COUNT_RECHAZO_AUDITORIA')
BEGIN
select @rechazoAuditoria = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
select indicador from ln_indicadores where clave_indicador='HOMOLOG')
and valor_numerico = 0
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @rechazoAuditoria AS rechazoAuditoria
END
IF (@accion = 'COUNT_ALTA_DEP_ECONOMICA')
BEGIN
select @CountAltaDepEcon = count(distinct contraparte) from LN_contraparte_indicadores where indicador in (
select indicador from ln_indicadores where clave_indicador='DEP_ECON_CALC')
and valor_numerico > 70.00
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0 )
and visible = 1
SELECT @CountAltaDepEcon AS CountAltaDepEcon
END
IF (@accion = 'COUNT-LINEA1')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1 and subcontratista = 0
SELECT @TotalDocumentos = COUNT(*) FROM LN_docs where cliente = @p_cliente and aplica=1 and estado_doc != 'X'
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
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 contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1
SELECT @TotalContrapartes AS TotalContrapartes,@TotalDocumentos AS TotalDocumentos,@renegocia_precio AS renegocia_precio,@enriesgo AS enriesgo
END
--FIN CONTADORES
IF (@accion = 'GRAPH_CONTRAPARTES_PAISES')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and subcontratista = 0
SELECT COUNT(CONTRAPARTE) AS CANT,LN_contrapartes.country_code,COUNTRY.country_name,@TotalContrapartes AS TotalContrapartes,(COUNT(CONTRAPARTE)*100)/@TotalContrapartes AS PORC
FROM LN_contrapartes INNER JOIN country ON LN_contrapartes.country_code = country.country_code
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
GROUP BY LN_contrapartes.country_code,COUNTRY.country_name
ORDER BY COUNTRY.country_name ASC
END
IF (@accion = 'GRAPH_CONTRAPARTES_CUMPLIMIENTO')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
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 = @p_cliente and activo=1)
and contraparte in (select distinct contraparte from ln_contrapartes where cliente = @p_cliente and activo=1 and subcontratista = 0)
and visible = 1 AND valor_numerico=100
SELECT (@TotalCumplimiento * 100)/@TotalContrapartes AS cumplimiento_porc,@TotalContrapartes as TotalContrapartes,@TotalCumplimiento as cumplimiento_cant
END
IF (@accion = 'GRAPH_CLI_PROV')
BEGIN
SELECT @TotalContrapartes = COUNT(*) FROM LN_contrapartes where LN_contrapartes.cliente = @p_cliente and LN_contrapartes.activo=1 and LN_contrapartes.subcontratista = 0
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
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo=1
and LN_contrapartes.subcontratista=0
and LN_familias.uso_familia='C'
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
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo=1
and LN_contrapartes.subcontratista = 0
and LN_familias.uso_familia='P'
END
/*** GRAFICOS DASHBOARD ***/
IF (@accion = 'GRAFICO_INFORME_COMERCIAL') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
DECLARE @cant INT
SELECT @cant = COUNT(*)
FROM LN_contrapartes
inner join LN_gestiones
on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias lcf
on lcf.contraparte = LN_contrapartes.contraparte
inner join LN_familias lf
on lf.familia = lcf.familia
WHERE LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and lf.visible = 1 and lcf.activo = 1
AND LN_contrapartes.activo = 1
AND LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.subcontratista = 0
SELECT ISNULL(totales.clave_indicador,'SCORE_IC') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREIC.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_IC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente
) SCOREIC
GROUP BY SCOREIC.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_SCORE_BALANCE') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
SELECT ISNULL(totales.clave_indicador,'SCORE_BAL') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREBAL.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
/*when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 81 then 'R1'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 61 then 'R2'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 41 then 'R3'
when format(LN_contraparte_indicadores.valor_numerico,'##0') >= 21 then 'R4'
when format(LN_contraparte_indicadores.valor_numerico,'##0') < 21 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_BAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREBAL
GROUP BY SCOREBAL.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_SCORE_FINAL') BEGIN
if (select COUNT(*) from ln_cliente_indicadores
inner join LN_indicadores on LN_indicadores.indicador = LN_cliente_indicadores.indicador
where clave_indicador = 'SCORE_FINAL'
and cliente = @p_cliente) > 0
begin
insert into #rangos
select 'R'+convert(varchar,ROW_NUMBER() OVER(ORDER BY desde ASC)) AS Row,
texto, texto, color_fondo, 0, desde, hasta
from LN_cliente_nivel where cliente = @p_cliente
and desde >= -100
and indicador in (
select distinct LN_indicadores.indicador from ln_cliente_indicadores
inner join LN_indicadores on LN_indicadores.indicador = LN_cliente_indicadores.indicador
where clave_indicador = 'SCORE_FINAL'
and cliente = @p_cliente)
order by desde desc
end
else
begin
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 20, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 40, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 60, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 80, 101)
end
SELECT ISNULL(totales.clave_indicador,'SCORE_FINAL') as clave_indicador, #rangos.rango, #rangos.etiqueta,
#rangos.etiqueta_texto, #rangos.color, ISNULL(totales.total,0) as total,
#rangos.desde, #rangos.hasta
FROM #rangos
LEFT OUTER JOIN (
SELECT SF.clave_indicador, SF.rango, SF.etiqueta, SF.etiqueta_texto, SF.color, COUNT(*) AS total
FROM
(select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
(select rango from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as rango,
(select etiqueta from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as etiqueta,
(select etiqueta_texto from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as etiqueta_texto,
(select color from #rangos where LN_contraparte_indicadores.valor_numerico >= desde and LN_contraparte_indicadores.valor_numerico < hasta) as color
from LN_contraparte_indicadores
inner join LN_contrapartes on LN_contraparte_indicadores.contraparte = LN_contrapartes.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
where LN_contrapartes.cliente = @p_cliente
and LN_indicadores.clave_indicador = 'SCORE_FINAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0) SF
GROUP BY SF.clave_indicador, SF.rango, SF.etiqueta, SF.etiqueta_texto, SF.color
) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
/* VERSION ANTERIOR
SELECT ISNULL(totales.clave_indicador,'SCORE_FINAL') as clave_indicador, #rangos.rango, #rangos.etiqueta,
#rangos.etiqueta_texto, #rangos.color, ISNULL(totales.total,0) as total,
#rangos.desde, #rangos.hasta
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREFIN.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'
END AS color,
COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END
AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_FINAL'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREFIN
GROUP BY SCOREFIN.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
*/
END
IF (@accion = 'GRAFICO_SCORE_DOCUMENTOS') BEGIN
insert into #rangos VALUES ('R5','0-20', NULL,'a72b2a',0, 0, 20)
insert into #rangos VALUES ('R4','21-40', NULL,'d17127',0, 21, 40)
insert into #rangos VALUES ('R3','41-60', NULL,'fab724',0, 41, 60)
insert into #rangos VALUES ('R2','61-80', NULL,'89ab5f',0, 61, 80)
insert into #rangos VALUES ('R1','81-100', NULL,'309e99',0, 81, 100)
SELECT ISNULL(totales.clave_indicador,'SCORE_DOC') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREDOC.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREDOC
GROUP BY SCOREDOC.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_FIDECHECK') BEGIN
create table #rangospep (
rango varchar(2),
etiqueta varchar(20),
color varchar(6),
total int)
insert into #rangospep VALUES ('R3','Positivo','A72B2A',0)
insert into #rangospep VALUES ('R2','Relacionado','FFB81C',0)
insert into #rangospep VALUES ('R1','Negativo','63b68c',0)
SELECT ISNULL(totales.clave_indicador,'FIDECHECK') as clave_indicador, totales.valor_texto, #rangospep.rango, #rangospep.etiqueta, #rangospep.color, ISNULL(totales.total,0) as total
FROM #rangospep
LEFT OUTER JOIN (
SELECT FIDECHECK.clave_indicador,rango, FIDECHECK.valor_texto,
CASE
when rango = 'R1' then 'Negativo'
when rango = 'R2' then 'Relacionado'
when rango = 'R3' then 'Positivo'
END AS etiqueta,
CASE
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'FFB81C'
when rango = 'R3' then 'A72B2A'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
LN_contraparte_indicadores.valor_texto,
case
when LN_contraparte_indicadores.valor_texto = 'N' then 'R1'
when LN_contraparte_indicadores.valor_texto = 'R' then 'R2'
when LN_contraparte_indicadores.valor_texto = 'P' then 'R3'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
inner join LN_familias lf
on lf.familia = LN_familia_productos.familia
WHERE LN_indicadores.clave_indicador = 'FIDECHECK'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
and lf.visible = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) FIDECHECK
GROUP BY FIDECHECK.clave_indicador, rango, FIDECHECK.valor_texto) totales ON #rangospep.rango = totales.rango
ORDER BY #rangospep.rango
DROP TABLE #rangospep
END
IF (@accion = 'GRAFICO_SCORE_DOSSIER') BEGIN
SELECT ISNULL(totales.clave_indicador,'SCORE_DOSSIER') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT SCOREDOSSIER.clave_indicador, rango,
CASE
when rango = 'R1' then '81-100'
when rango = 'R2' then '61-80'
when rango = 'R3' then '41-60'
when rango = 'R4' then '21-40'
when rango = 'R5' then '0-20'
END AS etiqueta,
CASE
/*
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'cbc367'
when rango = 'R3' then 'FFB81C'
when rango = 'R4' then 'd37123'
when rango = 'R5' then 'A72B2A'*/
when rango = 'R1' then '309e99'
when rango = 'R2' then '89ab5f'
when rango = 'R3' then 'fab724'
when rango = 'R4' then 'd17127'
when rango = 'R5' then 'a72b2a'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 81 then 'R1'
when LN_contraparte_indicadores.valor_numerico >= 61 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 41 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 21 then 'R4'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R5'
when LN_contraparte_indicadores.valor_numerico < 0 then 'R5'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOSSIER'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) SCOREDOSSIER
GROUP BY SCOREDOSSIER.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
/*
select LN_contraparte_indicadores.indicador,LN_indicadores.clave_indicador
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
WHERE LN_indicadores.clave_indicador = 'SCORE_DOSSIER'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente
*/
END
IF (@accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
SELECT ISNULL(totales.clave_indicador,'SCORE_DEP_ECON') as clave_indicador, #rangos.rango, #rangos.etiqueta, #rangos.color, ISNULL(totales.total,0) as total
FROM #rangos
LEFT OUTER JOIN (
SELECT DEPECON.clave_indicador, rango,
CASE
when rango = 'R3' then '71-100'
when rango = 'R2' then '51-70'
when rango = 'R1' then '0-50'
END AS etiqueta,
CASE
when rango = 'R3' then 'A72B2A'
when rango = 'R2' then 'FFB81C'
when rango = 'R1' then '63b68c'
END AS color, COUNT(*) AS total
FROM (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte, LN_contraparte_indicadores.valor_numerico,
case
when LN_contraparte_indicadores.valor_numerico >= 71 then 'R3'
when LN_contraparte_indicadores.valor_numerico >= 51 then 'R2'
when LN_contraparte_indicadores.valor_numerico >= 0 then 'R1'
else NULL END AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familia_productos on (LN_contraparte_familias.familia = LN_familia_productos.familia
and LN_indicadores.producto = LN_familia_productos.producto)
WHERE LN_indicadores.clave_indicador = 'DEP_ECON_CALC'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_familia_productos.activo = 1
and LN_cliente_productos.activo = 1
AND LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) DEPECON
GROUP BY DEPECON.clave_indicador, rango) totales ON #rangos.rango = totales.rango
ORDER BY #rangos.rango
END
IF (@accion = 'GRAFICO_CONTROL_INGRESO') BEGIN
DECLARE @contrapartes_excepcion varchar(max) = '0'
DECLARE @total_excepciones int = 0
DECLARE Excepciones CURSOR FOR
select LN_excepciones.contraparte
from LN_excepciones
inner join LN_gestiones on (LN_excepciones.contraparte = LN_gestiones.contraparte
and LN_excepciones.gestion = LN_gestiones.gestion)
inner join LN_contraparte_indicadores on (LN_gestiones.contraparte = LN_contraparte_indicadores.contraparte
and LN_gestiones.gestion = LN_contraparte_indicadores.gestion)
inner join LN_contrapartes on LN_excepciones.contraparte = LN_contrapartes.contraparte
inner join LN_indicadores on LN_contraparte_indicadores.indicador = LN_indicadores.indicador
where LN_contrapartes.cliente = @p_cliente
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_gestiones.desde <= GETDATE()
and LN_gestiones.hasta >= GETDATE()
and LN_indicadores.clave_indicador = 'EXCEPCION'
--and LN_excepciones.estado = 1
and LN_contraparte_indicadores.visible = 1
and LN_excepciones.vigencia >= GETDATE()
group by LN_excepciones.contraparte
OPEN Excepciones
FETCH NEXT FROM Excepciones INTO @contrap
WHILE @@FETCH_STATUS = 0 BEGIN
SET @total_excepciones = @total_excepciones + 1
SET @contrapartes_excepcion = @contrapartes_excepcion + ',' + convert(varchar(10),@contrap)
FETCH NEXT FROM Excepciones INTO @contrap
END
CLOSE Excepciones
DEALLOCATE Excepciones
SELECT CASE
WHEN rango != 'R2' then indicador
ELSE (select indicador from LN_indicadores where clave_indicador = 'EXCEPCION')
END AS indicador,
rango, valor_numerico as valor,
case
when rango = 'R1' then '63b68c'
when rango = 'R2' then 'FFB81C'
when rango = 'R3' then 'A72B2A'
end as color,
count(*) as total,
case
when rango = 'R1' then 'Aprobado'
when rango = 'R2' then 'Excepción'
when rango = 'R3' then 'Rechazado'
end as etiqueta
from (
select LN_contraparte_indicadores.indicador, LN_indicadores.clave_indicador,
LN_contraparte_indicadores.contraparte,
iif (excepcion.Item is null, LN_contraparte_indicadores.valor_numerico, 1) as valor_numerico,
iif (excepcion.Item is null,
case
when LN_contraparte_indicadores.valor_numerico > 0 then 'R1'
when LN_contraparte_indicadores.valor_numerico = 0 then 'R3'
else NULL end,
'R2') AS rango
from LN_contraparte_indicadores
inner join LN_contrapartes ON
LN_contrapartes.contraparte = LN_contraparte_indicadores.contraparte
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_cliente_productos on LN_contrapartes.cliente = LN_cliente_productos.cliente
inner join LN_indicadores ON
(LN_cliente_productos.producto = LN_indicadores.producto
AND LN_contraparte_indicadores.indicador = LN_indicadores.indicador)
left outer join dbo.DelimitedSplit8K(@contrapartes_excepcion, ',') excepcion ON LN_contrapartes.contraparte = convert(int, excepcion.Item)
WHERE LN_indicadores.clave_indicador = 'HOMOLOG'
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_contrapartes.cliente = @p_cliente) data
group by indicador, rango, valor_numerico
END
IF (@accion = 'GRAFICO_INFORME_COMERCIAL' or @accion = 'GRAFICO_SCORE_BALANCE' or @accion = 'GRAFICO_SCORE_FINAL' or @accion = 'GRAFICO_SCORE_DOCUMENTOS' or @accion = 'GRAFICO_DEPENDENCIA_ECONOMICA') BEGIN
DROP TABLE #rangos
END
/*** FIN GRAFICOS DASHBOARD ***/
END
GO
Depends On
15- dbo.country
- dbo.LN_cliente_indicadores
- dbo.LN_cliente_nivel
- dbo.LN_cliente_productos
- dbo.LN_contraparte_familias
- dbo.LN_contraparte_indicadores
- dbo.LN_contrapartes
- dbo.LN_docs
- dbo.LN_excepciones
- dbo.LN_familia_productos
- dbo.LN_familias
- dbo.LN_gestiones
- dbo.LN_indicadores
- dbo.LN_productos
- dbo.DelimitedSplit8K
Used By
No items found