
/****** Object:  StoredProcedure [dbo].[DSS_ACC_ExtractoCuentas]    Script Date: 12/04/2013 14:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DSS_ACC_ExtractoCuentas]
(
	@id_empr   INT,
	@id_ejer   INT,
	@id_cuenta INT,
	@FecIni    DATE,
	@FecFin    DATE
	--,
	--@id_mone   INT,
    --@Tasa      NUMERIC(13, 8)
)
AS
BEGIN 

	
	
	DECLARE @id_peri INT = (SELECT id_peri 
                            FROM   CONTPERI 
                            WHERE  id_ejer = @id_ejer 
						       AND orden = 0)
	
	DECLARE @SaldoAnt    NUMERIC(21, 6)
	DECLARE @Ejercicio   SMALLINT    = (SELECT ejercicio FROM CONTEJER WHERE id_ejer = @id_ejer)
	DECLARE @Periodo     VARCHAR(100) = (SELECT titulo    FROM CONTPERI WHERE id_peri = @id_peri)
	DECLARE @TitEmpresa  VARCHAR(100) = (SELECT tit_emp   FROM CONTEMPR WHERE id_empr = @id_empr)
	--DECLARE @id_cuenta   INT = (SELECT TOP 1 id_cuenta   FROM CONTPLAN WHERE LTRIM(RTRIM(cuenta)) = LTRIM(RTRIM(@cuenta)))
	DECLARE @id_mone     INT = (SELECT id_mone FROM CONTEMPR WHERE id_empr = @id_empr)
	
	DECLARE @Tasa        NUMERIC(13, 8) = (SELECT tasa FROM SICLAMON WHERE id_mone = @id_mone)
	DECLARE @Moneda      CHAR(100)    = (SELECT desc_mone FROM SICLAMON WHERE id_mone = @id_mone)
	
	SET @Tasa = CASE @Tasa 
				     WHEN 0 THEN 1
				     ELSE 1 / @Tasa
				     END 

	SELECT  @SaldoAnt = ISNULL(SUM(ISNULL(CASE deudora WHEN 1 THEN debe ELSE haber END, 0)) 
		- SUM(ISNULL(CASE deudora WHEN 1 THEN haber ELSE debe END, 0)), 0) 
			--debe = SUM(debe),  
			--haber = SUM(haber),   
			--tdebe_mb = SUM(tdebe_mb), 
			--thaber_mb = SUM(thaber_mb)   
	FROM(
		SELECT   debe  = CASE WHEN vdiario.id_mone = contplan.id_mone THEN debe 
					          ELSE tdebe_mb  * COALESCE(siclamon.tasa, @Tasa) / @Tasa  
						      END,  
				 haber = CASE WHEN vdiario.id_mone = contplan.id_mone THEN haber 
						      ELSE thaber_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa 
						      END,  
				 tdebe_mb  = tdebe_mb, 
				 thaber_mb = thaber_mb,
				 CONTPLAN.deudora   
		FROM       vdiario 
		INNER JOIN contplan ON vdiario.id_cuenta = contplan.id_cuenta  
		LEFT  JOIN siclamon ON contplan.id_mone  = siclamon.id_mone  
		WHERE   vdiario.id_empr = @id_empr 
			AND vdiario.id_ejer = @id_ejer    
			AND (DATEDIFF(DAY,vdiario.fec_con, @FecIni) > 0 OR vdiario.id_peri = @id_peri)  
			AND vdiario.id_cuenta = @id_cuenta   
		UNION ALL 
		SELECT   debe  = CASE WHEN vastospdtes.id_mone = contplan.id_mone THEN debe 
					          ELSE tdebe_mb  * COALESCE(siclamon.tasa, @Tasa) / @Tasa  
					          END,  
				 haber = CASE WHEN vastospdtes.id_mone = contplan.id_mone THEN haber 
						      ELSE thaber_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa  
						      END,  
				 tdebe_mb  = tdebe_mb, 
				 thaber_mb = thaber_mb,
				 CONTPLAN.deudora  
		FROM       vastospdtes 
		INNER JOIN contplan ON vastospdtes.id_cuenta = contplan.id_cuenta  
		LEFT  JOIN siclamon ON contplan.id_mone      = siclamon.id_mone  
		WHERE   vastospdtes.id_empr = @id_empr   
			AND vastospdtes.id_ejer = @id_ejer   
			AND (DATEDIFF(DAY, vastospdtes.fec_con, @FecIni) > 0 OR vastospdtes.id_peri = @id_peri)  
			AND vastospdtes.id_cuenta = @id_cuenta
	) todo
	
	--SELECT @SaldoAnt AS SaldoAnt
	
	CREATE TABLE #ExtractoA
	(
		cuenta      CHAR(100),
		cto_gto     CHAR(100),
		tit_cta     VARCHAR(100),
		fec_con     DATETIME,
		compro_show CHAR(100),
		cta_cto     VARCHAR(100),
		fec_val     DATETIME,
		descrip     VARCHAR(255),
		doc         VARCHAR(100),
		debe_l      NUMERIC(21, 2),
		haber_l     NUMERIC(21, 2),
		SaldoAnt    NUMERIC(21, 2),
		Pendiente   BIT,
		deudora     BIT
	)
	
	INSERT  #ExtractoA
	SELECT  --vdiario.*, 
	        LTRIM(RTRIM(VDIARIO.cuenta)) cuenta,
	        LTRIM(RTRIM(VDIARIO.cto_gto)) cto_gto,
	        LTRIM(RTRIM(VDIARIO.tit_cta)) tit_cta,
	        vdiario.fec_con,
	        vdiario.compro_show,
	        LTRIM(RTRIM(vdiario.cta_cto)) cta_cto,
	        vdiario.fec_val,
	        LTRIM(RTRIM(vdiario.descrip)) descrip,
	        LTRIM(RTRIM(vdiario.doc)) doc,
			debe_l  = CASE WHEN vdiario.id_mone = contplan.id_mone THEN debe 
						   ELSE ROUND(tdebe_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa, 2) 
						   END,  
			haber_l = CASE WHEN vdiario.id_mone = contplan.id_mone THEN haber 
					       ELSE thaber_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa  
					       END,  
			--tasa_mone = siclamon.tasa, 
			--mone_cta  = contplan.id_mone,
			@SaldoAnt AS SaldoAnt,
			0 AS Pendiente,
			CONTPLAN.deudora  
	FROM       vdiario 
	INNER JOIN contplan ON vdiario.id_cuenta = contplan.id_cuenta  
	LEFT  JOIN siclamon ON contplan.id_mone  = siclamon.id_mone 
	WHERE vdiario.id_empr = @id_empr 
		AND id_ejer = @id_ejer 
		AND DATEDIFF(DAY, fec_con, @FecIni) <= 0 
		AND DATEDIFF(DAY, fec_con, @FecFin) >= 0  
		AND vdiario.id_cuenta = @id_cuenta  
		AND id_peri != @id_peri 
		
	--UNION ALL 
	
	INSERT  #ExtractoA
	SELECT  --vastospdtes.*, 
	        LTRIM(RTRIM(vastospdtes.cuenta)) cuenta,
	        LTRIM(RTRIM(vastospdtes.cto_gto)) cto_gto,
	        LTRIM(RTRIM(vastospdtes.tit_cta)) tit_cta,
	        vastospdtes.fec_con,
	        vastospdtes.compro_show,
	        LTRIM(RTRIM(vastospdtes.cta_cto)) cta_cto,
	        vastospdtes.fec_val,
	        LTRIM(RTRIM(vastospdtes.descrip)) descrip,
	        LTRIM(RTRIM(vastospdtes.doc)) doc,
			debe_l  = CASE WHEN vastospdtes.id_mone = contplan.id_mone THEN debe 
						   ELSE ROUND(tdebe_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa, 2) 
						   END,  
			haber_l = CASE WHEN vastospdtes.id_mone = contplan.id_mone THEN haber 
						   ELSE thaber_mb * COALESCE(siclamon.tasa, @Tasa) / @Tasa  
						   END,  
			--tasa_mone = siclamon.tasa, 
			--mone_cta  = contplan.id_mone,
			@SaldoAnt AS SaldoAnt,
			1 AS Pendiente,
			CONTPLAN.deudora   
	FROM       vastospdtes 
	INNER JOIN contplan ON vastospdtes.id_cuenta = contplan.id_cuenta  
	LEFT  JOIN siclamon ON contplan.id_mone      = siclamon.id_mone 
	WHERE vastospdtes.id_empr = @id_empr 
		AND id_ejer = @id_ejer   
		AND DATEDIFF(DAY, fec_con, @FecIni) <= 0  
		AND DATEDIFF(DAY, fec_con, @FecFin) >= 0  
		AND vastospdtes.id_cuenta = @id_cuenta  
		AND id_peri != @id_peri  
	
	
	SELECT IDENTITY(INT, 1, 1) AS Id,
	       * 
	INTO   #ExtractoB
	FROM   #ExtractoA
	ORDER BY fec_con, compro_show, cta_cto
	
	SELECT b.*,
	       b.SaldoAnt + (SELECT SUM(CASE deudora WHEN 1 THEN debe_l  ELSE haber_l END) FROM #ExtractoB b2 WHERE b2.Id <= b.Id) 
	                  - (SELECT SUM(CASE deudora WHEN 1 THEN haber_l ELSE debe_l  END) FROM #ExtractoB b2 WHERE b2.Id <= b.Id) AS Saldo
	                  ,
	       @TitEmpresa AS TitEmpresa,
	       @Ejercicio  AS Ejercicio,
	       'Del ' + CONVERT(VARCHAR(15), @FecIni, 103) + ' al ' + CONVERT(VARCHAR(15), @FecFin, 103)  AS Periodo,
	       @Moneda     AS Moneda,
	       CONVERT(VARCHAR(100), LTRIM(RTRIM(b.cuenta)) + ' ' + LTRIM(RTRIM(ISNULL(b.cto_gto, ''))) + ' ' + LTRIM(RTRIM(b.tit_cta))) AS TitCuenta
    FROM   #ExtractoB b
	
	RETURN

END
