
/****** Object:  StoredProcedure [dbo].[DSS_ACC_EstadoResultados]    Script Date: 09/23/2012 11:05:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
===========================================================
-- Autor:       Francis Antonio Bolaños Arias
-- Fecha:       23/09/2012 11:05:20
-- Descripción: Procedimiento que obtiene la información
--              para generar el informe de Cuentas por 
--              Edades para DSS
-- ========================================================
*/
DROP PROCEDURE [dbo].[DSS_ACC_CuentasXEdades]
GO

CREATE PROCEDURE [dbo].[DSS_ACC_CuentasXEdades]
(
    @Modo         INT, -- 2 - Totalizado, 1 - Resumido (Estructural), 0 - Detallado (Plan)
    @Filtro       INT, -- 0 - Ingresos, 1 - Costos, 2 - Gastos  
    @id_ejer      INT, 
    @id_peri      INT, 
    @id_empr      INT,
    @cuenta       CHAR(20),
    @Cobrar       TINYINT,
    @id_mone      INT,
    @Tasa         NUMERIC(13, 8)
)
AS
BEGIN 

	DECLARE @Orden INT, @TipoCuenta INT, @fec_fin DATETIME

	SELECT @Orden   = orden,
	       @fec_fin = hasta
	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 Edades
	
	SELECT  cuenta, 
	        cto_gto  = COALESCE(cto_gto, ' '), 
            tit_cta  = MAX(tit_cta), 
            tit_ctgt = MAX(tit_ctgt), 
            di.doc, 
            fecha    = MIN(di.fec_val),
            Pla      = '2',
            credito  = MAX(credito), 
            pl.deudora, 
            dm.id_mone,
            importe  = SUM(di.importe * dm.tasa * @Tasa * CASE WHEN di.debito = 1 THEN 1 
                                                                            ELSE -1 
                                                                            END), 
            impo_mb  = SUM(di.importe * dm.tasa * CASE WHEN di.debito = 1 THEN 1 
                                                                   ELSE -1 
                                                                   END), 
            impo_mf  = SUM(di.importe * CASE WHEN di.debito = 1 THEN 1 
                                                   ELSE -1 
                                                   END) 
    INTO       #Edades
    FROM       CONTDIAM dm 
    INNER JOIN CONTDIAR di ON dm.id_diam   = di.id_diam
    INNER JOIN CONTLIBR li ON dm.id_libr   = li.id_libr 
    INNER JOIN CONTPLAN pl ON di.id_cuenta = pl.id_cuenta 
    LEFT  JOIN CONTCTGT cg ON pl.id_ctgt   = cg.id_ctgt 
    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 dm.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   dm.id_ejer = @id_ejer
		AND pl.id_empr = dm.id_empr
        AND em.id_empr = @id_empr
        AND Libro != ')' 
        AND di.id_cuenta NOT IN (SELECT id_cuenta 
                                 FROM       CONTEMCE ec
                                 INNER JOIN CONTCESP ce ON ec.id_cesp = ce.id_cesp 
                                 WHERE   ec.id_cuenta = di.id_cuenta 
								     AND ec.id_empr   = @id_empr 
                                     AND ce.cod_cesp  = '03')
        AND(
			tc.cod_tcta      IN        ((CASE WHEN @Cobrar = 1 THEN '02' 
											ELSE '01' 
											END)
									   ,(CASE WHEN @Cobrar = 1 THEN '18' 
											ELSE '01' 
											END))  
			OR di.id_cuenta  IN (SELECT id_cuenta 
							     FROM       CONTEMCE ec
							     INNER JOIN CONTCESP ce ON ec.id_cesp = ce.id_cesp 
							     WHERE    ec.id_cuenta   = di.id_cuenta 
									  AND ec.id_empr   = @id_empr
									  AND ce.cod_cesp IN ((CASE WHEN @Cobrar = 1 THEN '30' 
									                                 ELSE '31' 
									                                 END),
									                            (CASE WHEN @Cobrar = 1 THEN '18' 
									                                 ELSE '31' 
									                                 END)))
        )
        AND DATEDIFF(DAY, fec_con, SUBSTRING(CONVERT(char(19), @fec_fin), 1, 11)) >= 0  
    GROUP BY cuenta, cto_gto, di.doc, pl.deudora, dm.id_mone 
    HAVING ABS(SUM(di.importe * dm.tasa * CASE WHEN di.debito = 1 THEN 1 
                                                           ELSE -1 
                                                           END)) > 0.00001
                                                           
    SELECT cuenta, 
           cto_gto,  
           doc,  
           SUM(importe) AS importe,  
           MIN(fecha) AS fecha,
           tit_cta,  
           tit_ctgt, 
           pla,  
           deudora, 
           credito  
    INTO   #Edades2
    FROM   #Edades
    GROUP BY cuenta, cto_gto, doc, tit_cta, tit_ctgt, pla, deudora, credito  
    HAVING ABS(SUM(importe)) >= 1.00  
          
    SELECT LTRIM(RTRIM(cuenta)) cuenta, 
           LTRIM(RTRIM(cto_gto)) cto_gto,
           LTRIM(RTRIM(doc)) doc,
           fecha,
           fecha + credito AS fec_venc,
           SUM(CASE WHEN (DATEDIFF(DAY, fecha, @fec_fin)  + 1  <= credito)  THEN importe 
                    ELSE 0 
                    END) AS corriente,  
           SUM(CASE WHEN ((DATEDIFF(DAY, fecha, @fec_fin) + 1   > credito) AND (DATEDIFF(DAY, fecha, @fec_fin) + 1 <= credito + 30)) THEN importe 
                    ELSE 0 
                    END) AS h_30,  
           SUM(CASE WHEN ((DATEDIFF(DAY, fecha, @fec_fin) + 1   > credito + 30) AND (DATEDIFF(DAY, fecha, @fec_fin) + 1 <= credito + 60)) THEN importe
					ELSE 0 
					END) AS h_60,  
           SUM(CASE WHEN ((DATEDIFF(DAY, fecha, @fec_fin) + 1   > credito + 60) AND (DATEDIFF(DAY, fecha, @fec_fin) + 1 <= credito + 90)) THEN importe
					ELSE 0 
					END) AS h_90,  
           SUM(CASE WHEN ((DATEDIFF(DAY, fecha, @fec_fin) + 1   > credito + 90) AND (DATEDIFF(DAY, fecha, @fec_fin) + 1 <= credito + 120)) THEN importe
					ELSE 0 
					END) AS h_120,  
           SUM(CASE WHEN ((DATEDIFF(DAY, fecha, @fec_fin) + 1   > credito + 120)) THEN importe 
					ELSE 0 
					END) AS m_120,  
           tit_cta,  
           tit_ctgt 
    INTO  #Edades3
    FROM  #Edades2 
    GROUP BY cuenta, cto_gto, fecha, doc, credito, tit_cta, tit_ctgt, pla, deudora
    
	-- 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 @Moneda      CHAR(18)    = (SELECT desc_mone FROM SICLAMON WHERE id_mone = @id_mone)
	
	IF LEN(@cuenta) = 0
	BEGIN
	
		SELECT *,
			   @Ejercicio  AS Ejercicio,
			   @Periodo    AS Periodo,
			   @TitEmpresa AS Empresa,
			   @Moneda     AS Moneda,
			   @Cobrar     AS PorCobrar 
		FROM #Edades3
		ORDER BY cuenta ASC
	
	END
	ELSE
	BEGIN
		SELECT *,
			   @Ejercicio  AS Ejercicio,
			   @Periodo    AS Periodo,
			   @TitEmpresa AS Empresa,
			   @Moneda     AS Moneda,
			   @Cobrar     AS PorCobrar 
		FROM   #Edades3
		WHERE  cuenta = @Cuenta
	END

	--IF @Modo = 0
	--BEGIN
	
	--	SELECT er.cuenta,
	--		   er.tit_cta,
	--	       er.sal_ini,
	--	       er.sal_per,
	--		   er.tit_ctaest,
	--		   er.id_tcta,
	--		   @Ejercicio     AS Ejercicio,
	--		   @Periodo       AS Periodo,
	--		   @TitEmpresa    AS TitEmpresa,
	--		   1              AS Sumar
	--	FROM        #EstRes  er
	--	WHERE    er.Orden   = @TipoCuenta
	--	ORDER BY er.cuenta ASC
	
	--END
	--ELSE
	--BEGIN
	
	--	SELECT er.cuenta,
	--		   er.tit_cta,
	--	       er.sal_ini,
	--	       er.sal_per,
	--		   er.tit_ctaest,
	--		   er.id_tcta,
	--		   @Ejercicio     AS Ejercicio,
	--		   @Periodo       AS Periodo,
	--		   @TitEmpresa    AS TitEmpresa,
	--		   1              AS Sumar,
	--		   er.id_estr,
	--		   er.cuenta_est
	--    INTO        #EstRes2
	--	FROM        #EstRes  er
	--	WHERE    er.Orden   = @TipoCuenta
	--	--ORDER BY er.cuenta ASC
	
	--	IF @Modo = 1
	--	BEGIN
		
	--		SELECT MAX(er.cuenta_est) AS cuenta,
	--			   MAX(er.tit_ctaest) AS tit_cta,
	--			   SUM(er.sal_ini)    AS sal_ini,
	--			   SUM(er.sal_per)    AS sal_per,
	--			   MAX(er.tit_ctaest) AS tit_ctaest,
	--			   MAX(er.id_tcta)    AS id_tcta,
	--			   MAX(Ejercicio)     AS Ejercicio,
	--			   MAX(Periodo)       AS Periodo,
	--			   MAX(TitEmpresa)    AS TitEmpresa,
	--			   1                  AS Sumar
	--		FROM        #EstRes2  er
	--		GROUP BY er.cuenta_est
	--		--ORDER BY er.cuenta ASC
		
		
	--	END
	--	ELSE
	--	BEGIN
	--		SELECT MAX(es.cuenta_est) AS cuenta,
	--			   MAX(es.tit_ctaest) AS tit_cta,
	--			   SUM(er.sal_ini)    AS sal_ini,
	--			   SUM(er.sal_per)    AS sal_per,
	--			   MAX(es.tit_ctaest) AS tit_ctaest,
	--			   MAX(er.id_tcta)    AS id_tcta,
	--			   MAX(Ejercicio)     AS Ejercicio,
	--			   MAX(Periodo)       AS Periodo,
	--			   MAX(TitEmpresa)    AS TitEmpresa,
	--			   1                  AS Sumar
	--		FROM        #EstRes2 er
	--		INNER JOIN  CONTESTR es ON SUBSTRING(er.cuenta, 1, @co_maydig) = es.cuenta_est
	--		WHERE    es.id_empr = @id_empr
	--		GROUP BY es.cuenta_est
	--		--ORDER BY er.cuenta ASC
	--	END
		
	--END

	RETURN

END
