-- =============================================
-- Author:		OSCAR NAVARRO
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_CONTROL_CLIENTES](@IDCLIENTE BIGINT)
AS
BEGIN
	DECLARE @ATTR_CABECERA VARCHAR(MAX) = '',@SQL VARCHAR(MAX), @ATTR_PVT VARCHAR(MAX);
SET @ATTR_CABECERA = (SELECT STUFF((SELECT ' ISNULL(SUM("'+X.NOMBRE_CONTROL+'"),0) "' + X.NOMBRE_CONTROL +'", ' FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = @IDCLIENTE ORDER BY X.ORDEN FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''));
SET @ATTR_PVT = (SELECT STUFF((SELECT ' "'+X.NOMBRE_CONTROL+'", ' FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = @IDCLIENTE ORDER BY X.ORDEN FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''));
SET @SQL = '
SELECT (SELECT CAST( ( CAST(COUNT(C.VALOR) AS FLOAT) / TOTAL_CONTROLES_SOL) * 100 AS NUMERIC(19,2)) FROM MS_CONFIG_CLIENTE A
			INNER JOIN MS_CONTROL_CLIENTE B ON A.ID_CLIENTE = B.ID_CLIENTE
			INNER JOIN MS_CONTROL_CLIENTE_VALUES C ON A.ID = C.ID_CONFIG AND B.ID = C.ID_INICIATIVA
			WHERE A.RESPONSABILIDAD = 1
			AND C.VALOR = 1
			AND A.ID_CLIENTE = '+ CAST(@IDCLIENTE AS VARCHAR(20)) + '
			AND B.ID = IDINICIATIVA) CUMPLIMIENTO_SOL
,(SELECT CAST( ( CAST(COUNT(C.VALOR) AS FLOAT) / TOTAL_CONTROLES_CLI) * 100 AS NUMERIC(19,2)) FROM MS_CONFIG_CLIENTE A
			INNER JOIN MS_CONTROL_CLIENTE B ON A.ID_CLIENTE = B.ID_CLIENTE
			INNER JOIN MS_CONTROL_CLIENTE_VALUES C ON A.ID = C.ID_CONFIG AND B.ID = C.ID_INICIATIVA
			WHERE A.RESPONSABILIDAD = 0
			AND C.VALOR = 1
			AND A.ID_CLIENTE = '+ CAST(@IDCLIENTE AS VARCHAR(20)) + '
			AND B.ID = IDINICIATIVA) CUMPLIMIENTO_CLI
,*
FROM (
    SELECT
			B.NOMBRE_INICIATIVA
			,A.NOMBRE_CONTROL
			,C.VALOR
			,B.ID AS IDINICIATIVA
			,(SELECT COUNT(1) FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = A.ID_CLIENTE AND X.RESPONSABILIDAD = 1) TOTAL_CONTROLES_SOL
			,(SELECT COUNT(1) FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = A.ID_CLIENTE AND X.RESPONSABILIDAD = 0) TOTAL_CONTROLES_CLI
			,(SELECT COUNT(1) FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = A.ID_CLIENTE) TOTAL_CONTROLES
			FROM MS_CONFIG_CLIENTE A
			INNER JOIN MS_CONTROL_CLIENTE B ON A.ID_CLIENTE = B.ID_CLIENTE
			INNER JOIN MS_CONTROL_CLIENTE_VALUES C ON A.ID = C.ID_CONFIG AND B.ID = C.ID_INICIATIVA
			WHERE A.ID_CLIENTE = '+ CAST(@IDCLIENTE AS VARCHAR(20)) + '

) AS DATOS PIVOT
(
    SUM(VALOR)
    FOR NOMBRE_CONTROL IN (#CONTROLES#)
)AS PVT_TABLE
ORDER BY IDINICIATIVA DESC';

SET @ATTR_PVT		= SUBSTRING(@ATTR_PVT,0,LEN(@ATTR_PVT))
SET @SQL			= REPLACE(@SQL,'#CONTROLES#', @ATTR_PVT);
EXEC(@SQL);

END