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


CREATE PROCEDURE [dbo].[CPJ_Pagos_CC]	@pJson VARCHAR(8000)
AS
SET NOCOUNT ON

PRINT @pJson

/*Variables recibidas por parametro a traves del JSON*/
DECLARE @pModoDebug			    	TINYINT
DECLARE @pId_Usuario			    SMALLINT
DECLARE @pFecha_Desde			    DATETIME
DECLARE @pFecha_Hasta			    DATETIME
DECLARE @pId_Centro_Costo			CHAR(10)
DECLARE @pId_Unid_Negocio			CHAR(10)
DECLARE @pSoloResultado	    		TINYINT			
DECLARE @pFlag_Filtrar_CC			TINYINT			
DECLARE @pFormato					VARCHAR(10)		

SET @pModoDebug					= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'ModoDebug')), 0)
SET @pId_Usuario				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Id_Usuario')), 0)
SET @pFecha_Desde				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJson,'Fecha_Desde')), '19000101')
SET @pFecha_Hasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJson,'Fecha_Hasta')), '19000101')
SET @pId_Centro_Costo			= ISNULL(CONVERT(CHAR(10), dbo.CF_JSON_VALUE(@pJson,'Id_Centro_Costo')), '')
SET @pId_Unid_Negocio			= ISNULL(CONVERT(CHAR(10), dbo.CF_JSON_VALUE(@pJson,'Id_Unid_Negocio')), '')
SET @pSoloResultado				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'SoloResultado')), 0)
SET @pFlag_Filtrar_CC			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'Flag_Filtrar_CC')), 0)
SET @pFormato				    = ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@pJson,'Tipo_Reporte')), 'CTA')
/*FIN Variables recibidas por parametro a traves del JSON*/


/*Variables Locales*/
DECLARE @SqlStr						NVARCHAR(MAX)
DECLARE @Id_Trans					INT
DECLARE @Alias						DECIMAL(12,0)
DECLARE @Nro_Cta_Contable			VARCHAR(20)
DECLARE @Debe						DECIMAL(12,2)
DECLARE @Haber						DECIMAL(12,2)
DECLARE @Fecha						DATETIME
DECLARE @Descripcion				VARCHAR(80)
DECLARE @Centro_Costo				VARCHAR(200)
DECLARE @Centro_Costo_Padre1		VARCHAR(200)
DECLARE @Centro_Costo_Padre2		VARCHAR(200)
DECLARE @Destinatario				VARCHAR(100)
DECLARE @Observacion_Orig			VARCHAR(8000)
DECLARE @Observacion_Dest			VARCHAR(8000)
DECLARE @Importe					DECIMAL(12,2)
DECLARE @Imp_Total_Fac				DECIMAL(12,2)
DECLARE @Imp_Aplicado				DECIMAL(12,2)
DECLARE @Imp_Fac					DECIMAL(12,2)
DECLARE @Coef_Aplic					DECIMAL(12,2)
DECLARE @Id_Trans_CCC				INT
DECLARE @Id_Centro_Costo_Padre1		CHAR(10)
DECLARE @Id_Centro_Costo_Padre2		CHAR(10)
DECLARE @Mes_Desde					DATETIME
DECLARE @Mes_Hasta					DATETIME
DECLARE @Mes_Nro					TINYINT
DECLARE @Suma_Debe					DECIMAL(16,2)
DECLARE @Suma_Haber					DECIMAL(16,2)
DECLARE @Suma_Saldo					DECIMAL(16,2)
DECLARE @Id_Centro_Costo			CHAR(10)
DECLARE @Id_Centro_Costo_Cur		CHAR(10)
/*FIN Variables Locales*/

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Ingreso a Stored Procedure: CPJ_BalanceCC_Rpt'
		PRINT 'Parametros: '
		PRINT '@pFormato: ' +  @pFormato
		PRINT '@pId_Usuario: ' + CONVERT(VARCHAR(10), @pId_Usuario) + '. @pFecha_Desde: ' + CONVERT(VARCHAR(10), @pFecha_Desde, 112) + '. @pFecha_Hasta: ' + CONVERT(VARCHAR(10), @pFecha_Hasta, 112) + '. @pId_Centro_Costo: ' + CONVERT(VARCHAR(10), @pId_Centro_Costo)
		PRINT '@pId_Unid_Negocio: ' + @pId_Unid_Negocio + '. @pSoloResultado: ' + CONVERT(VARCHAR(10), @pSoloResultado) + '. @pFlag_Filtrar_CC: ' + CONVERT(VARCHAR(10), @pFlag_Filtrar_CC)
	END

	/*=============================================================================================================================*/
	/*=============================================================================================================================*/

	IF(@pId_Usuario = 0)
	BEGIN
		SET @pId_Usuario = -1
	END
	
	IF(@pFormato = '')										RAISERROR ('Tipo_Reporte can not be blank.', 16,  1)
	IF(@pFecha_Desde = '19000101')								RAISERROR ('Fecha_Desde can not be blank.', 16,  1)
	IF(@pFecha_Hasta = '19000101')								RAISERROR ('Fecha_Hasta can not be blank.', 16,  1) 
	IF(@pFecha_Desde >= @pFecha_Hasta)							RAISERROR ('Fecha_Desde can not be bigger or equal than Fecha_Hasta.', 16,  1)
	IF(DATEDIFF(YEAR, @pFecha_Desde, @pFecha_Hasta) > 0)		RAISERROR ('Diference between Fecha_Desde and Fecha_Hasta can not be bigger than a year.', 16,  1) 
	

	/*=============================================================================================================================*/

	IF(@pFlag_Filtrar_CC <> 0)
	BEGIN
		SET @Id_Centro_Costo = @pId_Centro_Costo
	END

	/*=============================================================================================================================*/
	
	CREATE TABLE #Pagos_CC
	(
	Id_Trans_O						INT,
	Id_Trans_D						INT,
	Alias							DECIMAL(12,0),
	Nro_Cta_Contable				VARCHAR(20),
	Debe							DECIMAL(12,2),
	Haber							DECIMAL(12,2),
	Saldo							DECIMAL(12,2),
	Fecha							DATETIME,
	Cta_Contable					VARCHAR(80),
	Id_Centro_Costo					VARCHAR(10),
	Centro_Costo					VARCHAR(200),
	Centro_Costo2					VARCHAR(200),
	Centro_Costo3					VARCHAR(200),
	Destinatario					VARCHAR(100),
	Observacion_Orig				VARCHAR(8000),
	Observacion_Dest				VARCHAR(8000)
	)




	/*=============================================================================================================================*/

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'OP Varias'
	END

	CREATE TABLE #Pagos_CC_OP_Varias
				(
				Id_Trans						INT,
				Alias							DECIMAL(12,0),
				Nro_Cta_Contable				VARCHAR(20),
				Debe							DECIMAL(12,2),
				Haber							DECIMAL(12,2),
				Fecha							DATETIME,
				Descripcion						VARCHAR(80),
				Destinatario					VARCHAR(100),
				Observacion_Orig				VARCHAR(8000),
				Observacion_Dest				VARCHAR(8000),
				Id_Centro_Costo					VARCHAR(10)
				)
	
	/*=============================================================================================================================*/

	SET @SqlStr =	'INSERT INTO #Pagos_CC_OP_Varias (Id_Trans, Alias, Nro_Cta_Contable, Debe, Haber, Fecha, Descripcion, Destinatario, Observacion_Orig, Observacion_Dest, Id_Centro_Costo) '

	SET @SqlStr =	RTRIM(LTRIM(@SqlStr))										+ CHAR(13) + 
					'SELECT '													+ CHAR(13) + 
					'C.Id_Trans, '												+ CHAR(13) + 
					'O.Alias, '													+ CHAR(13) + 
					'O.Nro_Cta_Contable, '										+ CHAR(13) + 
					'C.Debe, '													+ CHAR(13) + 
					'C.Haber, '													+ CHAR(13) + 
					'E.Fecha, '													+ CHAR(13) + 
					'O.Descripcion AS Cta_Contable, '							+ CHAR(13) + 
					'ISNULL(E.Destinatario, '''') AS Destinatario, '			+ CHAR(13) + 
					'ISNULL(CONVERT(VARCHAR(8000), E.Observacion), '''') AS Observacion_Orig, '+ CHAR(13) + 
					'ISNULL(CONVERT(VARCHAR(8000), E.Observacion), '''') AS Observacion_Dest, '+ CHAR(13) +
					'ISNULL(C.Id_Centro_Costo, '''') AS Id_Centro_Costo '		+ CHAR(13) +
					'FROM Cbtes_Ctas_Contables C, Cbtes E,Cbtes_Tipos T,Pla_Ctas_Cuentas_Contables O ' + CHAR(13) + 
					'WHERE '													+ CHAR(13) + 
					'C.Id_Trans = E.Id_Trans '									+ CHAR(13) + 
					'AND E.Id_Tipo_Cbte = T.Id_Tipo_Cbte '						+ CHAR(13) + 	
					'AND O.Id_Cta_Contable = C.Id_Cta_Contable '				+ CHAR(13) +
					'AND ISNULL(E.Id_Proveedor, 0) = 0 '						+ CHAR(13) + 					
					'AND E.Id_Estado_Cbte NOT IN (0,3) '
					
					
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					'AND E.Fecha >= ''' + CONVERT(VARCHAR(10), @pFecha_Desde, 112) + ''' ' + CHAR(13) +
					'AND E.Fecha < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @pFecha_Hasta), 112) + ''' '

	IF(@pFlag_Filtrar_CC <> 0)
	BEGIN		
		SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						'AND C.Id_Centro_Costo = ''' + @pId_Centro_Costo + ''' '
	END
	
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					'AND T.Compromete_Pagos IN (1,-1)'

	IF(@pSoloResultado = 1)
	BEGIN
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) +
					'AND ISNULL(O.Tipo, '''') IN (''+'',''-'') '
	END

	IF(@pModoDebug = 1)
	BEGIN
		PRINT ''
		PRINT '@SqlStr (Orden Pagos Varias): '
		PRINT @SqlStr
		PRINT ''
	END

	EXECUTE sp_executesql @SqlStr

	/*=============================================================================================================================*/

		INSERT INTO #Pagos_CC
								(Id_Trans_O,
								Id_Trans_D,
								Alias,
								Nro_Cta_Contable,
								Debe,
								Haber,
								Saldo,
								Fecha,
								Cta_Contable,
								Id_Centro_Costo,
								Centro_Costo,
								Centro_Costo2,
								Centro_Costo3,
								Destinatario,
								Observacion_Orig,
								Observacion_Dest)
		SELECT 
								P.Id_Trans,
								0,
								p.Alias,
								p.Nro_Cta_Contable, 
								ISNULL(p.Debe,0), 
								ISNULL(p.Haber,0), 
								ISNULL(p.Debe,0) - ISNULL(p.Haber,0),
								p.Fecha, 
								p.Descripcion,
								ISNULL(P.Id_Centro_Costo,''),
								CC.Descripcion,
								'',
								'',
								p.Destinatario,
								p.Observacion_Orig,
								p.Observacion_Dest
								FROM #Pagos_CC_OP_Varias P,  Centro_Costos CC
								WHERE ISNULL(P.Id_Centro_Costo,'') = CC.Id_Centro_Costo


	/*=============================================================================================================================*/
	/*=============================================================================================================================*/

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'OP Proveedores'
	END

	CREATE TABLE #Pagos_CC_OP_Proveedores
			(
			Id_Trans_Aplica					INT,
			Importe							DECIMAL(12,2),
			Imp_Total_Fac					DECIMAL(12,2),
			Id_Trans_CCC					INT,
			Alias							DECIMAL(12,0),
			Nro_Cta_Contable				VARCHAR(20),
			Debe							DECIMAL(12,2),
			Haber							DECIMAL(12,2),
			Fecha_OP						DATETIME,
			Descripcion						VARCHAR(80),
			Destinatario					VARCHAR(100),
			Observacion_Orig				VARCHAR(8000),
			Observacion_Dest				VARCHAR(8000),
			Id_Centro_Costo					VARCHAR(10)
			)
	
	/*=============================================================================================================================*/

	SET @SqlStr =	'INSERT INTO #Pagos_CC_OP_Proveedores (Id_Trans_Aplica, Importe, Imp_Total_Fac, Id_Trans_CCC, Alias, Nro_Cta_Contable, Debe, Haber, Fecha_OP, Descripcion, Destinatario, Observacion_Orig, Observacion_Dest, Id_Centro_Costo) '
	
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr))											+ CHAR(13) + 
					'SELECT '														+ CHAR(13) + 
					'A.Id_Trans, '													+ CHAR(13) + 
					'A.Importe, '													+ CHAR(13) + 
					'Fac.Imp_Total, '												+ CHAR(13) + 
					'OP.Id_Trans, '													+ CHAR(13) + 
					'O.Alias, '														+ CHAR(13) +
					'O.Nro_Cta_Contable, '											+ CHAR(13) + 
					'C.Debe, '														+ CHAR(13) + 
					'C.Haber, '														+ CHAR(13) + 
					'OP.Fecha, '													+ CHAR(13) + 
					'O.Descripcion, '												+ CHAR(13) + 
					'ISNULL(Fac.Destinatario, '''') AS Destinatario, '				+ CHAR(13) + 
					'ISNULL(CONVERT(VARCHAR(8000), Fac.Observacion), '''') AS Observacion_Orig, '+ CHAR(13) + 
					'ISNULL(CONVERT(VARCHAR(8000), OP.Observacion), '''') AS Observacion_Dest, '+ CHAR(13) +
					'ISNULL(C.Id_Centro_Costo, '''') AS Id_Centro_Costo '			+ CHAR(13) +
					'FROM Cbtes_Aplica A,Cbtes_Ctas_Contables C, Cbtes OP,Cbtes Fac,Cbtes_Tipos T,Pla_Ctas_Cuentas_Contables O ' + CHAR(13) + 
					'WHERE '														+ CHAR(13) + 
					'C.id_trans = A.id_trans '										+ CHAR(13) + 
					'AND A.id_trans_D = OP.id_trans '								+ CHAR(13) + 
					'AND Fac.id_trans = C.id_trans '								+ CHAR(13) + 
					'AND OP.id_Tipo_Cbte = T.Id_Tipo_Cbte '							+ CHAR(13) + 
					'AND O.Id_Cta_Contable = C.Id_Cta_Contable '					+ CHAR(13) + 
					'AND OP.Id_Proveedor > 0 '										+ CHAR(13) + 
					'AND OP.Id_Estado_Cbte NOT IN (0,3) '							+ CHAR(13) + 
					'AND Fac.Id_Estado_Cbte NOT IN (0,3) ' 

					
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					'AND OP.Fecha >= ''' + CONVERT(VARCHAR(10), @pFecha_Desde, 112) + ''' ' + CHAR(13) +
					'AND OP.Fecha < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @pFecha_Hasta), 112) + ''' '

	IF(@pFlag_Filtrar_CC <> 0)
	BEGIN
		SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
						'AND C.Id_Centro_Costo = ''' + @pId_Centro_Costo + ''' '
	END

	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) + 
					'AND T.Compromete_Pagos IN (1,-1)'

	IF(@pSoloResultado = 1)
	BEGIN
	SET @SqlStr =	RTRIM(LTRIM(@SqlStr)) + CHAR(13) +
					'AND ISNULL(O.Tipo, '''') IN (''+'',''-'') '
	END

	IF(@pModoDebug = 1)
	BEGIN
		PRINT ''
		PRINT '@SqlStr (OP Proveedores): '
		PRINT @SqlStr
		PRINT ''
	END

	EXECUTE sp_executesql @SqlStr

	/*=============================================================================================================================*/

		INSERT INTO #Pagos_CC
				(Id_Trans_O,
				Id_Trans_D,
				Alias,
				Nro_Cta_Contable,
				Debe,
				Haber,
				Saldo,
				Fecha,
				Cta_Contable,
				Id_Centro_Costo,
				Centro_Costo,
				Centro_Costo2,
				Centro_Costo3,
				Destinatario,
				Observacion_Orig,
				Observacion_Dest)
		SELECT 
			P.Id_Trans_Aplica,
			P.Id_Trans_CCC,
			P.Alias,
			P.Nro_Cta_Contable, 
			P.Debe * P.Importe / P.Imp_Total_Fac,   
			P.Haber * P.Importe / P.Imp_Total_Fac , 
			(P.Debe * P.Importe / P.Imp_Total_Fac ) - (  P.Haber * P.Importe / P.Imp_Total_Fac) , 
			P.Fecha_OP, 
			P.Descripcion,
			P.Id_Centro_Costo,
			CC.Descripcion,
			'',
			'',
			P.Destinatario,
			P.Observacion_Orig,
			P.Observacion_Dest
			FROM #Pagos_CC_OP_Proveedores P,	 Centro_Costos CC
			WHERE ISNULL(P.Id_Centro_Costo,'') = CC.Id_Centro_Costo


	/*=============================================================================================================================*/


	/*=============================================================================================================================*/
	
	IF(@pFormato = 'D')
	BEGIN
		--Explosion por Cbtes

		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Explosion por Comprobantes'
			PRINT '@pId_Centro_Costo:' + @pId_Centro_Costo + '. @pFecha_Desde: ' + CONVERT(VARCHAR(10), @pFecha_Desde, 102) + '. @pFecha_Hasta: ' + CONVERT(VARCHAR(10), @pFecha_Hasta, 102)
		END

		SELECT
		Id_Centro_Costo,
		Centro_Costo ,
		Nro_Cta_Contable AS Nro_Cta, 
		Cta_Contable AS Cta,
		Id_Trans_O AS Trans_Origen,
		CONVERT(DATE, Fecha) AS Fecha,
		dbo.CF_Cbte_Descrip(Id_Trans_O) AS Cbte,
		Destinatario AS Destinatario,
		Observacion_Orig AS Obs,
		Id_Trans_D AS Trans_Destino,
		'' AS Fecha_Destino,
		dbo.CF_Cbte_Descrip(Id_Trans_D) AS Cbte_Destino,
		Observacion_Dest AS Obs_Destino,
		Debe,
		Haber,
		Saldo
		FROM #Pagos_CC
		WHERE Fecha >= @pFecha_Desde
		AND Fecha < DATEADD(DAY, 1, @pFecha_Hasta)
		ORDER BY Nro_Cta_Contable, Fecha
	END
	
	IF(@pFormato = 'CTA')
	BEGIN
		/*=============================================================================================================================*/
		/*=============================================================================================================================*/

		--Agrupacion por Cuentas Mensual
		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Agrupacion por Cuentas'
		END

		/*=============================================================================================================================*/
		SELECT
		Id_Centro_Costo,
		Centro_Costo ,
		Nro_Cta_Contable AS Nro_Cta, 
		Cta_Contable AS Cta,
		SUM(Debe) AS Debe,
		SUM(Haber) AS Haber,
		SUM(Saldo) AS Saldo
		FROM #Pagos_CC
		WHERE Fecha >= @pFecha_Desde
		AND Fecha < DATEADD(DAY, 1, @pFecha_Hasta)
		GROUP BY Id_Centro_Costo,Centro_Costo,Nro_Cta_Contable,Cta_Contable 
		ORDER BY 2
	

	END

	IF(@pFormato = 'CC')
	BEGIN
		/*=============================================================================================================================*/
		/*=============================================================================================================================*/

		--Agrupacion por Cuentas Mensual
		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Agrupacion por CC'
		END

		/*=============================================================================================================================*/
		SELECT
		Id_Centro_Costo,
		Centro_Costo ,
		SUM(Debe) AS Debe,
		SUM(Haber) AS Haber,
		SUM(Saldo) AS Saldo
		FROM #Pagos_CC
		WHERE Fecha >= @pFecha_Desde
		AND Fecha < DATEADD(DAY, 1, @pFecha_Hasta)
		GROUP BY Id_Centro_Costo,Centro_Costo
		ORDER BY 2
	

	END



	IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] = '#Pagos_CC_OP_Varias') 
		DROP TABLE #Pagos_CC_OP_Varias

	IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] = '#Pagos_CC_OP_Proveedores') 
		DROP TABLE #Pagos_CC_OP_Proveedores

	IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] = '#Pagos_CC') 
		DROP TABLE #Pagos_CC

	
	/*=============================================================================================================================*/
	
	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Fin Stored Procedure: CPJ_BalanceCC_Rpt'
	END

/*
EXEC dbo.CPJ_Pagos_CC '{
							"ModoDebug":"1",
							"Id_Usuario":"-1",
							"Tipo_Reporte":"D",
							"Fecha_Desde":"20210101",
							"Fecha_Hasta":"20211231",
							"Id_Centro_Costo":"",
							"Id_Unid_Negocio":"",
							"SoloResultado":"0",
							"Flag_Filtrar_CC":"0"
							}'

EXEC CPJ_BalanceCC_Pagos_Rpt '{
							"ModoDebug":"1",
							"Id_Usuario":"-1",
							"Tipo_Reporte":"AGRUP",
							"Fecha_Desde":"20210101",
							"Fecha_Hasta":"20211231",
							"Id_Centro_Costo":"2",
							"Id_Alias_Cta_Cont":"0",
							"Id_Plan_Cta":"1",
							"Id_Unid_Negocio":"",
							"SoloResultado":"0",
							"Flag_Filtrar_CC":"0"
							}'


EXEC CPJ_BalanceCC_Pagos_Rpt '{
							"ModoDebug":"1",
							"Id_Usuario":"-1",
							"Tipo_Reporte":"AGRUP",
		                 	"Fecha_Desde":"20210401",
		                 	"Fecha_Hasta":"20210430",
							"Id_Centro_Costo":"ADM01",
							"Id_Alias_Cta_Cont":"0",
							"Id_Plan_Cta":"16",
							"Id_Unid_Negocio":"",
							"SoloResultado":"1",
							"Flag_Filtrar_CC":"1"
							}'


*/
