
/****** Object:  StoredProcedure [dbo].[DSS_ACC_EstadoResultados]    Script Date: 03/03/2014 14:23:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DSS_ACC_EstadoResultados]
(
    @Modo         INT, -- 1 - Gráfico 0 - Informe 
    @Filtro       INT, -- 0 - Ingresos, 1 - Costos, 2 - Gastos  
    @id_ejer      INT, 
    @id_peri      INT, 
    @id_empr      INT,
    @id_mone      INT,
    @Tasa         NUMERIC(13, 8)
)
AS
BEGIN 

	DECLARE @Orden INT, @TipoCuenta INT

	SELECT @Orden = orden
	FROM   CONTPERI
	WHERE  id_peri = @id_peri
	
	SET @Tasa = (1 / CASE @Tasa WHEN 0 THEN 1 ELSE @Tasa END)
	
	SELECT @TipoCuenta = CASE @Filtro
						 WHEN 0 THEN 1 -- Ingresos
						 WHEN 1 THEN 2 -- Costos
						 WHEN 2 THEN 3 -- Gastos
						 ELSE 3
						 END
						 
	
						 
	--SELECT @Tasa = (SELECT tasa FROM IHSecurity..SICLAMON WHERE refer_mone = 1)
	
	-- Consulta modificada del procedimiento b_result para evitar valores duplicados
	
	SELECT  MAX(pl.id_cuenta) AS id_cuenta,
			MAX(cuenta)  AS cuenta, 
			MAX(tit_cta) AS tit_cta,
			sal_ini  = CONVERT(NUMERIC(21,6), SUM(CASE WHEN sa.id_peri != CONVERT(CHAR(16), @id_peri) 
													   THEN debe - haber 
													   ELSE 0 
													   END * CONVERT(CHAR(13), @Tasa)))
					   ,
			sal_per  = CONVERT(NUMERIC(21,6), SUM(CASE WHEN sa.id_peri  = CONVERT(CHAR(16), @id_peri)
													   THEN debe - haber 
													   ELSE 0 
													   END * CONVERT(CHAR(13), @Tasa)))
					   ,
			MAX(CASE pl.deudora
					 WHEN 1 THEN 1
					 ELSE 0
					 END) AS deudora, 
			Orden = CASE WHEN cod_tcta = '06' THEN 2 
						 WHEN cod_tcta = '12' THEN 1 
						 ELSE 3 END,
			MAX(es.id_estr) AS id_estr,
			MAX(es.cuenta_est) AS cuenta_est,
			MAX(es.tit_ctaest) AS tit_ctaest,
			MAX(tc.id_tcta) AS id_tcta
	INTO       #EstRes
	FROM       CONTSALD sa   
	INNER JOIN CONTPLAN pl ON sa.id_cuenta = pl.id_cuenta 
	LEFT  JOIN CONTCTGT cg ON pl.id_ctgt   = cg.id_ctgt
	INNER JOIN CONTPERI pe ON sa.id_peri   = pe.id_peri
	INNER JOIN CONTESTR es ON pl.id_estr   = es.id_estr
	INNER JOIN CONTTCTA tc ON es.id_tcta   = tc.id_tcta
	INNER JOIN CONTEMPR em ON pl.id_empr   = em.id_empr
	INNER JOIN SICLAMON m1 ON em.id_mone   = m1.id_mone
	LEFT  JOIN SICLAMON m2 ON pl.id_mone   = m2.id_mone
	WHERE   sa.id_ejer = CONVERT(CHAR(16), @id_ejer)
		AND em.id_empr = @id_empr
		AND pe.orden  <= CONVERT(CHAR(16) , @orden)
		AND cod_tcta  IN ('12','06','10')
	GROUP BY cuenta, cod_tcta 
	ORDER BY cuenta
	
	-- Se obtienen valores especiales
	
	DECLARE @Ejercicio   SMALLINT    = (SELECT ejercicio FROM CONTEJER WHERE id_ejer = @id_ejer)
	DECLARE @Periodo     VARCHAR(20) = (SELECT titulo    FROM CONTPERI WHERE id_peri = @id_peri)
	DECLARE @TitEmpresa  VARCHAR(30) = (SELECT tit_emp   FROM CONTEMPR WHERE id_empr = @id_empr)
	DECLARE @co_maydig   TINYINT     = (SELECT co_maydig FROM CONTTABL WHERE id_tabl = 1)
	DECLARE @co_mossal   INT         = (SELECT TOP 1 co_mossal FROM CONTTABL)
	DECLARE @Moneda      CHAR(18)    = (SELECT desc_mone FROM IHSecurity..SICLAMON WHERE id_mone = @id_mone)

	IF @Modo = 0
	BEGIN
	
		SELECT er.cuenta,
			   LTRIM(RTRIM(er.tit_cta)) tit_cta,
			   LTRIM(RTRIM(MAX(es.cuenta_est))) AS cuenta_may,
			   LTRIM(RTRIM(MAX(es.tit_ctaest))) AS tit_ctamay,
		       er.sal_ini * CASE @co_mossal WHEN 1 THEN CASE er.deudora WHEN 1 THEN 1 ELSE -1 END ELSE 1 END AS sal_ini,
		       er.sal_per * CASE @co_mossal WHEN 1 THEN CASE er.deudora WHEN 1 THEN 1 ELSE -1 END ELSE 1 END AS sal_per,
			   LTRIM(RTRIM(er.tit_ctaest)) tit_ctaest,
			   er.id_tcta,
			   @Ejercicio     AS Ejercicio,
			   @Periodo       AS Periodo,
			   @TitEmpresa    AS TitEmpresa,
			   @Moneda        AS Moneda,
			   1              AS Sumar,
			   ISNULL((SELECT SUM(importe) 
			           FROM       CONTPRES pr
			           INNER JOIN CONTPLAN pl ON pr.id_cuenta = pl.id_cuenta
			           WHERE  pl.cuenta = er.cuenta AND pr.id_peri = @id_peri AND pl.id_empr = @id_empr AND activa = 1), 0) * @Tasa AS Presupuesto,
			   er.Orden
		INTO        #EstRes2
		FROM        #EstRes  er
		INNER JOIN  CONTESTR es ON SUBSTRING(er.cuenta, 1, @co_maydig) = es.cuenta_est
		WHERE    er.Orden   = @TipoCuenta
		GROUP BY es.cuenta_est, er.cuenta, er.tit_cta, er.sal_ini,
		         er.sal_per, er.tit_ctaest, er.id_tcta, er.Orden, er.deudora
		
		
		SELECT er.*,
		       (SELECT SUM(sal_per) FROM #EstRes WHERE Orden = 1) AS Ingresos,
		       (SELECT SUM(Presupuesto) FROM #EstRes2 WHERE Orden = 1) AS PresIngresos,
			   (SELECT SUM(sal_ini + sal_per) FROM #EstRes WHERE Orden = 1) AS TotIngresos,
			   (SELECT SUM(sal_per) FROM #EstRes WHERE Orden = 2) AS Costos,
			   (SELECT SUM(Presupuesto) FROM #EstRes2 WHERE Orden = 2) AS PresCostos,
			   (SELECT SUM(sal_ini + sal_per) FROM #EstRes WHERE Orden = 2) AS TotCostos,
			   (SELECT SUM(sal_per) FROM #EstRes WHERE Orden = 3) AS Gastos,
			   (SELECT SUM(Presupuesto) FROM #EstRes2 WHERE Orden = 3) AS PresGastos,
			   (SELECT SUM(sal_ini + sal_per) FROM #EstRes WHERE Orden = 3) AS TotGastos
	    FROM     #EstRes2 er
	    ORDER BY er.cuenta ASC
	
	END
	ELSE
	BEGIN
	
		-- Gráfico
	
		RETURN
		
	END

	RETURN

END
