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

CREATE  PROCEDURE dbo.CPJ_Importes_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  DECIMAL(20,2)
						,Mes_2  DECIMAL(20,2)
						,Mes_3  DECIMAL(20,2)
						,Mes_4  DECIMAL(20,2)
						,Mes_5  DECIMAL(20,2)
						,Mes_6  DECIMAL(20,2)
						,Mes_7  DECIMAL(20,2)
						,Mes_8  DECIMAL(20,2)
						,Mes_9  DECIMAL(20,2)
						,Mes_10 DECIMAL(20,2)
						,Mes_11 DECIMAL(20,2)
						,Mes_12 DECIMAL(20,2)
						,Total  DECIMAL(20,2)
						)



/*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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Remitos_Com) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Remitos_Com IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END ) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Compras IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Pagos) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Pagos IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Remitos) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Remitos IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Factura) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Factura IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Recibos) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Recibos IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Trans_Fondos) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Trans_Fondos IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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,'Pedidos', * FROM (
		SELECT DATEPART(month,Fecha_Mov) AS Mes
				, (C.Imp_Total * CASE ISNULL(C.Cotizacion,1) WHEN 0 THEN 1 ELSE ISNULL(C.Cotizacion,1) END * T.Compromete_Pedido) AS Imp_Total
				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 C.Id_Estado_Cbte NOT IN (0,3)
			AND T.Compromete_Pedido IN (1,-1)
				) pv
	PIVOT(
	   SUM(Imp_Total) 
		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_Importes_Resumen '{"Anio":"2023"}'

*/