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)

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)

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

CREATE TABLE #Reporte (
	Id_Concepto INT
	,Rubro VARCHAR(100) DEFAULT('')
	,SubRubro VARCHAR(100) DEFAULT('')
	,Concepto VARCHAR(100) DEFAULT('')
	,Importe DECIMAL(16,2)
	,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

	DECLARE MiCurPagos CURSOR FOR 
		SELECT	O.Id_Concepto
				,O2.Descripcion	AS Rubro 
				,O1.Descripcion	AS SubRubro 
				,O.Descripcion	AS Concepto
				,SUM(ISNULL(CO.Importe,0))
			FROM Cajas_Mov_Conceptos CO
				, Cbtes C
				, 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
	OPEN MiCurPagos
	FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe
	WHILE (@@FETCH_STATUS=0)
	BEGIN
		IF @Mes = 1
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes1)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes1	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 2
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes2)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes2	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 3
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes3)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes3	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 4
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes4)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes4	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 5
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes5)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes5	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 6
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes6)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes6	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 7
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes7)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes7	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 8
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes8)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes8	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 9
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes9)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes9	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 10
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes10)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes10	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 11
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes11)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes11	= @Importe WHERE Id_Concepto = @Id_Concepto
		END
		IF @Mes = 12
		BEGIN
			IF NOT EXISTS (SELECT 1 FROM #Reporte WHERE Id_Concepto = @Id_Concepto )
				INSERT INTO #Reporte  (Id_Concepto, Rubro, SubRubro, Concepto,Mes12)
				VALUES (@Id_Concepto,@Rubro,@SubRubro,@Concepto,@Importe)
			ELSE
				UPDATE #Reporte SET Mes12	= @Importe WHERE Id_Concepto = @Id_Concepto
		END


		FETCH NEXT FROM MiCurPagos INTO @Id_Concepto,@Rubro,@SubRubro,@Concepto,@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
DROP TABLE #Reporte


/*

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

*/





