set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ADP_SP_GetBalanzaDetalles](
	@pfechaInicio as nvarchar (30),
	@pfechaFin as nvarchar (30),
	@pprimerNivel as int,
	@pultimoNivel as int,
	@pcantSubCtas as int,
	@psDB as nvarchar(254)=''
	)

	as



	declare @SQLString nvarchar (max)
	DECLARE @ParmDefinition nvarchar(500);

	SET @ParmDefinition = N'@fechaInicio datetime, @fechaFin datetime, @primerNivel int, @ultimoNivel int, @cantSubCtas int';

	set @SQLString = N'declare @saldoInicial table (
			SysAccountCode varchar (50) collate SQL_Latin1_General_CP850_CI_AS,
			saldoInicial decimal (16, 6)
			)


	declare @cargosAbonos table (
		SysAccountCode varchar (50) collate SQL_Latin1_General_CP850_CI_AS,
		Cargo decimal (19,6),
		Abono decimal (19, 6)
		)

	declare @balanzaComp table (
		SysAccountCode varchar (50),
		AccountCode varchar (50),
		FatherNum varchar (50),
		Levels int,
		Nivel int,
		NumHijos int,
		SaldoInicial decimal (19, 6),
		Cargo decimal (19, 6),
		Abono decimal (19, 6),
		SaldoFinal decimal (19, 6),
		CodAgrup varchar (10)
		)

	declare @LevelCount int,
			@LevelCount2 int,
			@NumHijos int


	--Obtiene el saldo inicial de las cuentas afectadas
	insert into @saldoInicial(SysAccountCode, SaldoInicial)
	select ac.AcctCode, sum (j1.Debit - j1.Credit) SaldoInicial
	from [' + @psDB+ '].dbo.OACT ac left outer join [' + @psDB + '].dbo.JDT1 j1 on j1.Account = ac.AcctCode
	where j1.RefDate < @fechaInicio
	group by ac.AcctCode

	--Obtiene cargos y abonos de las cuentas afectadas
	insert into @cargosAbonos(SysAccountCode, Cargo, Abono)
	select j1.Account, sum (j1.Debit), sum (j1.Credit)
	from [' + @psDB+ '].dbo.OJDT jd inner join [' + @psDB+ '].dbo.JDT1 j1
		on jd.TransId = j1.TransId
	where j1.RefDate between @fechaInicio and @fechaFin
	and jd.AdjTran = ''N''
	group by j1.Account

	insert into @balanzaComp(SysAccountCode, AccountCode, FatherNum, Levels, Nivel, NumHijos,
								SaldoInicial, Cargo, Abono, saldoFinal, CodAgrup)
	select ac.AcctCode, ac.FormatCode, isnull (ac2.FormatCode, ac2.AcctCode), ac.Levels, 1, 0, isnull (bB.saldoInicial, 0) saldoInicial,
	isnull (dC.Cargo, 0) Debit, isnull (dC.Abono, 0) Credit,
	isnull (bB.saldoInicial, 0) + isnull (dC.Cargo, 0) - isnull (dC.Abono, 0) FinalBalance,
	ac.U_ADP_CODAGRUP
	from [' + @psDB + '].dbo.OACT ac left outer join [' + @psDB+ '].dbo.OACT ac2 on ac.FatherNum = ac2.AcctCode
	left outer join @saldoInicial bB on ac.AcctCode = bB.SysAccountCode
	left outer join @cargosAbonos dC on ac.AcctCode = dC.SysAccountCode
    where ac.U_ADP_CODAGRUP is not null


	select @LevelCount = max (Levels) from [' + @psDB+ '].dbo.OACT

	while @LevelCount >= 1
	begin
		update @balanzaComp
		set NumHijos = sB.NumHijos + 1
		from @balanzaComp tB inner join (select FatherNum, NumHijos
											from @balanzaComp
											where Levels = @LevelCount) sB on tB.SysAccountCode = sB.FatherNum

		select @LevelCount = @LevelCount - 1
	end

	update @balanzaComp
	set Nivel = abs((NumHijos - @cantSubCtas)) + 1,
	FatherNum = case when abs (NumHijos - @cantSubCtas) + 1 = 1 then NULL else FatherNum end
	where Levels between @primerNivel and @ultimoNivel
	and NumHijos <= @cantSubCtas


	--Contabiliza heredando las sumas de los hijos hacia los niveles padre
	select @LevelCount = max(Nivel)
	from @balanzaComp
	where Levels between @primerNivel and @ultimoNivel
	and NumHijos <= @cantSubCtas

	while @LevelCount >= 1
	begin
		update @balanzaComp
		set SaldoInicial = sB.sumaInicial,
		Cargo = sB.sumaCargo,
		Abono = sB.sumaAbono,
		SaldoFinal = SB.sumaFinal
		from @balanzaComp tB inner join (select FatherNum, sum (saldoInicial) sumaInicial,
											sum (Cargo) sumaCargo, sum (Abono) sumaAbono,
											sum (SaldoFinal) sumaFinal
											from @balanzaComp
											where Nivel = @LevelCount
											and Levels between @primerNivel and @ultimoNivel
											and NumHijos <= @cantSubCtas
											group by FatherNum) sB on tB.SysAccountCode = sB.FatherNum

		select @LevelCount = @LevelCount - 1
	end


	select SysAccountCode, isnull (AccountCode, SysAccountCode) Accountcode,
	FatherNum, Levels Nivel, NumHijos,
	Convert(Decimal(19, 2), LTrim(Str(SaldoInicial, 19, 2))) SaldoInicial,
	Convert(Decimal(19, 2), LTrim(Str(Cargo, 19, 2))) Cargo,
	Convert(Decimal(19, 2), LTrim(Str(Abono, 19, 2))) Abono,
	Convert(Decimal(19, 2), LTrim(Str(SaldoFinal, 19, 2))) SaldoFinal,
	CodAgrup
	from @balanzaComp
	where Levels between @primerNivel and @ultimoNivel
	and NumHijos <= @cantSubCtas
	order by 2'

	print  @SQLString

	EXECUTE sp_executesql @SQLString, @ParmDefinition, @fechaInicio = @pfechaInicio, @fechaFin = @pfechaFin, @primerNivel = @pprimerNivel, @ultimoNivel = @pultimoNivel, @cantSubCtas = @pcantSubCtas;