--USE [SevenDSS]
--GO
/****** Object:  StoredProcedure [dbo].[ACC_GetEstado]    Script Date: 11/17/2016 11:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================================
-- Author:		Francis Antonio Bolaños Arias
-- Create date: 03/10/2012 05:06:22
-- Description:	Procedimiento para obtener el 
--              estado de Accouting (Asientos sin integrar por módulo
-- ====================================================================

ALTER PROCEDURE [dbo].[ACC_GetEstado]
	@vBDAccounting VARCHAR(100),
	@Ejercicio     INT,
	@Empresa       INT
AS
BEGIN
	DECLARE @vSql VARCHAR(4000)
	SELECT  @vSql = 'SELECT COUNT(am.id_asim)   AS Total, 
	                        ISNULL(mo.id_modulo, 0) AS Modulo, 
	                        MAX(mo.desc_modulo) AS Nombre,
	                        MAX(pe.orden) AS id_peri,
	                        pe.titulo AS Periodo,
							ej.ejercicio AS Ejercicio,
	                        0 AS Orden
	                 FROM       {Accounting}..CONTASIM am
	                 INNER JOIN {Accounting}..CONTPERI pe ON am.fec_con BETWEEN pe.desde AND pe.hasta
					 INNER JOIN {Accounting}..CONTEJER ej ON pe.id_ejer   = ej.id_ejer
	                 INNER JOIN IHSecurity..Datos      da ON am.id_dato   = da.id_datos
	                 INNER JOIN IHSecurity..Modulo     mo ON da.id_modulo = mo.id_modulo
	                 WHERE am.id_empr = @Empresa
	                 GROUP BY   ej.ejercicio, pe.titulo, mo.id_modulo
	                 ORDER BY   MAX(pe.id_peri) ASC'
	                 
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	SET @vSql = REPLACE(@vSql, '@Empresa', CONVERT(VARCHAR(50), @Empresa))
	
	CREATE TABLE #TempAccEst
	(
		Total      INT,
	    Modulo     INT,
	    Nombre     VARCHAR(50),
	    id_peri    INT,
	    Periodo    VARCHAR(20),
	    Ejercicio  SMALLINT,
	    Orden      INT
	)
	
	INSERT INTO #TempAccEst
	EXEC(@vSql)
	
	SELECT  @vSql = 'SELECT pe.orden AS id_peri,
	                        pe.titulo AS Periodo,
							ej.ejercicio AS Ejercicio
	                 FROM       {Accounting}..CONTPERI pe 
					 INNER JOIN {Accounting}..CONTEJER ej ON pe.id_ejer   = ej.id_ejer'
	                 
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	
	CREATE TABLE #TempPeriodos
	(
		id_peri    INT,
	    Periodo    VARCHAR(20),
	    Ejercicio  SMALLINT
	)
	
	INSERT INTO #TempPeriodos
	EXEC(@vSql)
	
	SELECT IDENTITY(INT, 1, 1) AS Id, *
	INTO #TempPeriodos2
	FROM #TempPeriodos
	
	SELECT IDENTITY(INT, 1, 1) AS Id, *
	INTO   #TempAccEst2
	FROM   #TempAccEst
	WHERE  Ejercicio = @Ejercicio
	
	DROP TABLE #TempPeriodos
	DROP TABLE #TempAccEst
	
	UPDATE #TempAccEst2
	SET    Nombre = 'Asientos Manuales'
	WHERE  Modulo = 2 --Accounting
	
	DECLARE @Inicio    INT = (SELECT MIN(Id) FROM #TempAccEst2)
	DECLARE @Final     INT = (SELECT MAX(Id) FROM #TempAccEst2)
	DECLARE @Cuenta    INT = @Inicio
	DECLARE @IdPeriodo INT = 0
	DECLARE @Periodo   VARCHAR(20) = ''
	DECLARE @Modulo    INT = 0
	DECLARE @Nombre    VARCHAR(50) = ''
	
	WHILE @Cuenta <= @Final
	BEGIN
		
		DECLARE @PInicio  INT = (SELECT MIN(Id) FROM #TempPeriodos2)
		DECLARE @PFinal   INT = (SELECT MAX(Id) FROM #TempPeriodos2)
		DECLARE @PCuenta  INT = @Inicio
		DECLARE @Existe   INT = 0
		
		SET @Modulo = (SELECT Modulo FROM #TempAccEst2 WHERE Id = @Cuenta)
		SET @Nombre = (SELECT Nombre FROM #TempAccEst2 WHERE Id = @Cuenta)
		
		WHILE @PCuenta <= @PFinal
		BEGIN
			SET @Periodo   = (SELECT Periodo  FROM #TempPeriodos2 WHERE Id = @PCuenta)
			SET @IdPeriodo = (SELECT id_peri  FROM #TempPeriodos2 WHERE Id = @PCuenta)
			
			SET @Existe  = (SELECT COUNT(*) FROM #TempAccEst2   WHERE Nombre = @Nombre AND Periodo = @Periodo)
			
			IF @Existe = 0
			BEGIN
				INSERT INTO #TempAccEst2
				(
					Total,
					Modulo,
					Nombre,
					id_peri,
					Periodo,
					Ejercicio,
					Orden 
				)VALUES
				(
					0,
					@Modulo,
					@Nombre,
					@IdPeriodo,
					@Periodo,
					@Ejercicio,
					0
				)
			END
			
			SET @PCuenta = @PCuenta + 1
		END
		
		SET @Cuenta = @Cuenta + 1
		
	END
	
	SELECT * FROM #TempAccEst2 es
	ORDER BY Orden, Ejercicio, Nombre, id_peri ASC
	
	SELECT  @vSql = 'SELECT ej.ejercicio
	                 FROM   {Accounting}..CONTEJER ej WHERE id_ejer = ' + CAST(@Ejercicio AS VARCHAR)
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	EXEC(@vSql)
	
	SELECT  @vSql = 'SELECT em.tit_emp
	                 FROM   {Accounting}..CONTEMPR em WHERE id_empr = ' + CAST(@Empresa AS VARCHAR)
	SET @vSql = REPLACE(@vSql, '{Accounting}', @vBDAccounting)
	EXEC(@vSql)
	
END
