IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CPJ_Conceptos_Rpt_Anual') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE CPJ_Conceptos_Rpt_Anual
END
GO

CREATE PROCEDURE CPJ_Conceptos_Rpt_Anual @pJson VARCHAR(8000)
AS 
DECLARE @Anio INT
	,@Circuito CHAR(1)
	,@Flag_Prov TINYINT

SET NOCOUNT ON 
SET @Anio				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Anio')), 0)
SET @Circuito			= ISNULL(CONVERT(CHAR(1), dbo.CF_JSON_VALUE(@pJson,'Circuito')), 0)
SET @Flag_Prov			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'Flag_Prov')), 0)

DECLARE @Mes SMALLINT
	,@Id_Concepto INT
	,@Rubro VARCHAR(100) 
	,@SubRubro VARCHAR(100) 
	,@Concepto VARCHAR(100) 
	,@Importe DECIMAL(16,2)
	,@Id_Proveedor INT 
	,@Proveedor VARCHAR(100)

CREATE TABLE #Reporte (
	Id_Concepto INT
	,Rubro VARCHAR(100) DEFAULT('')
	,SubRubro VARCHAR(100) DEFAULT('')
	,Concepto VARCHAR(100) DEFAULT('')
	,Id_Proveedor INT DEFAULT(0)
	,Proveedor VARCHAR(100) DEFAULT('')
	,Importe DECIMAL(16,2)  DEFAULT(0)
	,Mes1 DECIMAL(16,2) DEFAULT(0)
	,Mes2 DECIMAL(16,2) DEFAULT(0)
	,Mes3 DECIMAL(16,2) DEFAULT(0)
	,Mes4 DECIMAL(16,2) DEFAULT(0)
	,Mes5 DECIMAL(16,2) DEFAULT(0)
	,Mes6 DECIMAL(16,2) DEFAULT(0)
	,Mes7 DECIMAL(16,2) DEFAULT(0)
	,Mes8 DECIMAL(16,2) DEFAULT(0)
	,Mes9 DECIMAL(16,2) DEFAULT(0)
	,Mes10 DECIMAL(16,2) DEFAULT(0)
	,Mes11 DECIMAL(16,2) DEFAULT(0)
	,Mes12 DECIMAL(16,2) DEFAULT(0)
	,Total DECIMAL(16,2) DEFAULT(0)
)


SET @Mes = 1
WHILE @Mes <= 12
BEGIN
	IF @Flag_Prov = 0
	BEGIN

		DECLARE MiCurPagos CURSOR FOR 
			SELECT	O.Id_Concepto
					,O2.Descripcion	AS Rubro 
					,O1.Descripcion	AS SubRubro 
					,O.Descripcion	AS Concepto
					,0 AS Id_Proveedor
					,'' AS Proveedor
					,SUM(ISNULL(CO.Importe,0))
				FROM Cajas_Mov_Conceptos CO
					, Cbtes C
					LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
					, Cbtes_Tipos T
					, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
					LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
				
				WHERE CO.Id_Trans = C.Id_Trans
				AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
				AND CO.Id_Concepto = O.Id_Concepto
				AND C.Id_Estado_Cbte IN (2,4)
				AND T.Compromete_Pagos IN (1,-1)
				AND DATEPART(YEAR,C.Fecha) = @Anio
				AND DATEPART(MONTH,C.Fecha) = @Mes
				GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion
				ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion
    END
	ELSE
	BEGIN
		DECLARE MiCurPagos CURSOR FOR 
			SELECT	O.Id_Concepto
					,O2.Descripcion	AS Rubro 
					,O1.Descripcion	AS SubRubro 
					,O.Descripcion	AS Concepto
					,ISNULL(C.Id_Proveedor,0) AS Id_Proveedor
					,ISNULL(P.Razon_Social,'') AS Proveedor
					,SUM(ISNULL(CO.Importe,0))
				FROM Cajas_Mov_Conceptos CO
					, Cbtes C
					LEFT JOIN Proveedores P ON C.Id_Proveedor = P.Id_Proveedor
					, Cbtes_Tipos T
					, Conceptos O LEFT JOIN Conceptos O1 ON O1.Id_Concepto = O.Id_Concepto_Padre
					LEFT JOIN Conceptos O2 ON O2.Id_Concepto = O1.Id_Concepto_Padre
				WHERE CO.Id_Trans = C.Id_Trans
				AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
				AND CO.Id_Concepto = O.Id_Concepto
				AND C.Id_Estado_Cbte IN (2,4)
				AND T.Compromete_Pagos IN (1,-1)
				AND DATEPART(YEAR,C.Fecha) = @Anio
				AND DATEPART(MONTH,C.Fecha) = @Mes
				GROUP BY O.Id_Concepto,O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
				ORDER BY O2.Descripcion, O1.Descripcion, O.Descripcion,C.Id_Proveedor,P.Razon_Social
	END

	OPEN MiCurPagos
	FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe
	WHILE (@@FETCH_STATUS=0)
	BEGIN
		IF @Mes = 1
		BEGIN
			
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes1)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes1	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 2
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes2)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes2	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 3
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes3)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes3	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 4
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes4)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes4	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 5
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes5)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes5	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 6
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes6)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes6	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 7
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes7)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes7	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 8
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes8)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes8	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 9
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes9)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes9	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 10
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes10)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes10	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 11
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes11)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes11	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END
		IF @Mes = 12
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor)
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Id_Proveedor,Proveedor,Mes12)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe)
			ELSE
				UPDATE #Reporte SET Mes12	= @Importe WHERE Id_Concepto = @Id_Concepto AND Id_Proveedor = @Id_Proveedor
		END


		FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@Id_Proveedor,@Proveedor,@Importe
	END
	CLOSE MiCurPagos
	DEALLOCATE MiCurPagos
	SET @Mes += 1
END
--ALTER TABLE #Reporte DROP COLUMN Importe
UPDATE #Reporte SET Total = Mes1 + Mes2 + Mes3 + Mes4 + Mes5 + Mes6 + Mes7 + Mes8 + Mes9 + Mes10 + Mes11 + Mes12

SELECT * FROM #Reporte ORDER BY Rubro,SubRubro,Concepto,Proveedor
DROP TABLE #Reporte


/*

EXEC CPJ_Conceptos_Rpt_Anual  '{"Anio":"2021","Tipo":"P"}'
EXEC CPJ_Conceptos_Rpt_Anual  '{"Anio":"2021","Tipo":"P","Flag_Prov":"1"}'

*/





