
/****** Object:  StoredProcedure [dbo].[DSS_ACC_BalanceGeneral]    Script Date: 02/15/2013 10:48:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE [dbo].[DSS_ACC_BalanceGeneral]
GO

CREATE PROCEDURE [dbo].[DSS_ACC_BalanceGeneral]
(
    @Modo         INT, -- 0 - Resumido (Estructural), 1 - Detallado (Plan)
    @Filtro       INT, -- 11 - Activo Circulante, 12 - Activo Fijo, 13 - Otros Activos, etc.
    @id_ejer      INT, 
    @id_peri      INT, 
    @id_empr      INT,
    @id_mone      INT,
    @Tasa         NUMERIC(13, 8)
)
AS
BEGIN 

	DECLARE @Orden INT, @TipoOrden CHAR(2)

	SELECT @Orden = orden
	FROM   CONTPERI
	WHERE  id_peri = @id_peri
	
	SET @Tasa = (1 / CASE @Tasa WHEN 0 THEN 1 ELSE @Tasa END)

	SELECT @TipoOrden  = CONVERT(CHAR(2), @Filtro)
						 
	--SELECT @Tasa = (SELECT tasa FROM SICLAMON WHERE refer_mone = 1)

	SELECT orden = cod_bala, 
		   descripcion, 
		   cuenta, 
		   cto_gto  = COALESCE(cto_gto,' '), 
		   tit_cta,
		   tit_ctgt = COALESCE(tit_ctgt,' '),
		   sal_ini  = CONVERT (NUMERIC(21, 6), SUM((debe - haber) * CONVERT(CHAR(13), @Tasa))),
		   pl.deudora,
		   MAX(es.id_estr)     AS id_estr,
		   MAX(es.cuenta_est)  AS cuenta_est,
		   MAX(es.tit_ctaest)  AS tit_ctaest,
		   MAX(tc.cod_tcta)    AS cod_tcta
    INTO       #BalGen
	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
	INNER JOIN CONTBALA ba ON es.id_bala   = ba.id_bala
	LEFT JOIN  SICLAMON m2 ON pl.id_mone   = m2.id_mone
	WHERE   sa.id_ejer    =   CONVERT(CHAR(16), @id_ejer)
		AND tc.cod_tcta NOT IN ('19', '20')
		AND em.id_empr    =   @id_empr
		AND pe.orden     <=   CONVERT(CHAR(16), @orden)
		--AND SUBSTRING(cod_bala, 1, 2) = @TipoOrden
	GROUP BY cod_bala, descripcion, cuenta, cto_gto, tit_cta, tit_ctgt, pl.deudora
	ORDER BY cuenta, cto_gto
	
	
	Select orden = cod_bala, 
	       descripcion, 
	       cuenta, 
	       cto_gto = Coalesce(cto_gto,' '), 
	       tit_cta,
           tit_ctgt = Coalesce(tit_ctgt,' '),
           sal_ini  = Convert(Numeric(21,6),Sum( 
                      Case when contplan.deudora = 1 then importe 
                           else -importe  
                           end  /* / siclamon.tasa */ * @Tasa)),
           Pla = '2', contplan.deudora, cod_tcta
    INTO   #Presupuestos
    From       contpres 
    inner join contplan on contpres.id_cuenta = contplan.id_cuenta 
    left  join contctgt on contplan.id_ctgt = contctgt.id_ctgt
    inner join contperi on contpres.id_peri = contperi.id_peri
    inner join contestr on contplan.id_estr = contestr.id_estr
    inner join conttcta on contestr.id_tcta = conttcta.id_tcta
    inner join contempr on contplan.id_empr = contempr.id_empr
    inner join siclamon on contempr.id_mone = siclamon.id_mone
    inner join contbala on contestr.id_bala = contbala.id_bala
    left  join siclamon mon2 on contplan.id_mone = mon2.id_mone
    Where contperi.id_ejer = @id_ejer
          and conttcta.cod_tcta not in ('19','20')
          and contempr.id_empr = @id_empr
          and contperi.orden <= @Orden
    Group by cod_bala, descripcion, cuenta, 
             cto_gto, 
             tit_cta, 
             tit_ctgt,
             contplan.deudora, cod_tcta
    Order by cuenta,cto_gto 



	-- 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   TINYINT     = (SELECT co_mossal FROM CONTTABL WHERE id_tabl = 1)
	DECLARE @Moneda      CHAR(18)    = (SELECT desc_mone FROM SICLAMON WHERE id_mone = @id_mone)
	
	SELECT bg.orden,
		   bg.descripcion,
	       ISNULL(bg.cuenta, pr.cuenta) AS cuenta,
	       ISNULL(bg.cto_gto, pr.cto_gto) AS cto_gto,
		   LTRIM(RTRIM(bg.tit_cta)) + ' ' + LTRIM(RTRIM(bg.tit_ctgt)) AS tit_cta,
		   bg.tit_ctgt,
		   ISNULL(bg.sal_ini, 0) AS sal_ini,
		   @Ejercicio      AS Ejercicio,
		   @Periodo        AS Periodo,
		   @TitEmpresa     AS TitEmpresa,
		   ISNULL(bg.cod_tcta, pr.cod_tcta) AS cod_tcta,
		   1 AS Sumar,
		   bg.id_estr,
		   bg.cuenta_est,
		   bg.tit_ctaest,
		   --0 AS Presupuesto
		   ISNULL(pr.sal_ini, 0) AS Presupuesto
    INTO   #BalGen4
	FROM   #BalGen  bg
	FULL OUTER JOIN #Presupuestos pr ON LTRIM(RTRIM(bg.cuenta))  = LTRIM(RTRIM(pr.cuenta)) AND 
	                                    LTRIM(RTRIM(bg.cto_gto)) = LTRIM(RTRIM(pr.cto_gto))
	
	--select * from #BalGen4 order by cuenta asc
	
	UPDATE #BalGen4 
	SET    orden = CASE cod_tcta
	               WHEN '05' THEN '111'
				   WHEN '18' THEN '112'
	               WHEN '03' THEN '113'
				   WHEN '02' THEN '114'
				   WHEN '13' THEN '115'
				   WHEN '16' THEN '116'
				   WHEN '11' THEN '121'  
				   WHEN '04' THEN '122'
				   WHEN '15' THEN '132'            
				   WHEN '01' THEN '211'
				   WHEN '17' THEN '212'
				   WHEN '14' THEN '221'
				   WHEN '07' THEN '311' 
				   WHEN '08' THEN '311' 
				   WHEN '09' THEN '311'   
				   WHEN '12' THEN '321'
				   WHEN '06' THEN '321'
				   WHEN '10' THEN '321'
				   END

	IF @Modo = 0
	BEGIN
	
		IF @co_mossal <> 2
		BEGIN
			UPDATE #BalGen4 
			SET sal_ini     = sal_ini     * -1, 
			    Presupuesto = Presupuesto * -1
			WHERE orden in ('211', '212', '221', '311', '321')
		END
					   
		SELECT bg.orden,
			   LTRIM(RTRIM(bg.descripcion)) descripcion,
		       LTRIM(RTRIM(bg.cuenta)) cuenta,
		       LTRIM(RTRIM(bg.cto_gto)) cto_gto,
			   LTRIM(RTRIM(bg.tit_cta)) AS tit_cta,
			   LTRIM(RTRIM(bg.tit_ctgt)) tit_ctgt,
			   LTRIM(RTRIM(MAX(es.cuenta_est))) AS cuenta_may,
			   LTRIM(RTRIM(MAX(es.tit_ctaest))) AS tit_ctamay,
			   bg.sal_ini AS sal_ini,
			   @Ejercicio      AS Ejercicio,
			   @Periodo        AS Periodo,
			   @TitEmpresa     AS TitEmpresa,
			   @Moneda         AS Moneda,
			   LTRIM(RTRIM(bg.cod_tcta)) cod_tcta,
			   1 AS Sumar,
			   MAX(bg.Presupuesto) AS Presupuesto,
			   @co_mossal AS co_mossal
		FROM        #BalGen4  bg
		INNER JOIN  CONTESTR es ON SUBSTRING(bg.cuenta, 1, @co_maydig) = es.cuenta_est
		WHERE  SUBSTRING(bg.orden, 1, 2) = @TipoOrden
		GROUP BY bg.cuenta, bg.cto_gto, bg.tit_cta, bg.tit_ctaest, bg.tit_ctgt, bg.sal_ini, bg.cod_tcta, bg.orden, bg.descripcion, bg.Presupuesto 
		
		--SELECT SUM(Presupuesto) FROM #BalGen4 WHERE  SUBSTRING(orden, 1, 2) = @TipoOrden
	
	END
	ELSE
	BEGIN
		
		RETURN
		
	END

	RETURN

END
