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

CREATE  PROCEDURE dbo.CPJ_Datos_Resumen @pJsonRecibido VARCHAR(8000)
AS 
SET NOCOUNT ON

DECLARE 	@pAnio INT

SET @pAnio = ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Anio')), 0)

DECLARE @Datos TABLE (	Orden SMALLINT, Concepto VARCHAR(200)	
						,Mes_1 INT
						,Mes_2 INT
						,Mes_3 INT
						,Mes_4 INT
						,Mes_5 INT
						,Mes_6 INT
						,Mes_7 INT
						,Mes_8 INT
						,Mes_9 INT
						,Mes_10 INT
						,Mes_11 INT
						,Mes_12 INT
						,Total INT
						)



/*Remitos Compra*/

INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 1,'Remitos de Compras', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Remitos_Com IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;

/*Compras*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 2,'Compras', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Compras IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;


/*Pagos*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 3,'Pagos', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Pagos IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;

----------------------------------------------------------------------------------------------
/*Remitos Venta*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 4,'Remitos de Venta', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Remitos IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;


/*Ventas*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 5,'Ventas', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Factura IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;


/*Cobranzas*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 6,'Cobranzas', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Recibos IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;

/*Trans.Fondos*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 7,'Movimientos de Fondos', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND T.Compromete_Trans_Fondos  IN (1,-1)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;


/*Otros*/
INSERT INTO @Datos (Orden,Concepto,Mes_1,Mes_2,Mes_3,Mes_4,Mes_5,Mes_6,Mes_7,Mes_8,Mes_9,Mes_10,Mes_11,Mes_12)
	SELECT 8,'Otros', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes, C.Id_Trans FROM Cbtes C, Cbtes_Tipos T,Transacciones O
			WHERE O.Id_Trans = C.Id_Trans 
			AND T.Id_Tipo_Cbte = C.Id_Tipo_Cbte
			AND DATEPART(year,O.Fecha_Mov) = @pAnio
			AND NOT (T.Compromete_Remitos_Com IN (1,-1)
			OR T.Compromete_Compras IN (1,-1)
			OR T.Compromete_Pagos IN (1,-1)
			OR T.Compromete_Remitos IN (1,-1)
			OR T.Compromete_Factura IN (1,-1)
			OR T.Compromete_Recibos IN (1,-1)
			OR T.Compromete_Trans_Fondos  IN (1,-1) 
			)
				) pv
	PIVOT(
	   COUNT(Id_Trans) 
		FOR Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
	) AS pivot_table;

UPDATE @Datos SET Total = ISNULL(Mes_1,0) +
						ISNULL(Mes_2,0) +
						ISNULL(Mes_3,0) +
						ISNULL(Mes_4,0) +
						ISNULL(Mes_5,0) +
						ISNULL(Mes_6,0) +
						ISNULL(Mes_7,0) +
						ISNULL(Mes_8,0) +
						ISNULL(Mes_9,0) +
						ISNULL(Mes_10,0) +
						ISNULL(Mes_11,0) +
						ISNULL(Mes_12,0) 

SELECT * FROM @Datos ORDER BY Orden


/*
EXEC CPJ_Datos_Resumen '{"Anio":"2023"}'

*/