--use [test 1300] 
--exec FillOtherArticle @BaseID=165
--select * from AccDocItem where id in (165, 169)
--SELECT  * FROM   AccDocumentGoodsPhase2 WITH (NOLOCK) Where  not TotalID is null  AND TotalID=70 AND ControlID=484 AND DetailID1=2052 and Flags <> 0 
Drop PROCEDURE dbo.FillOtherArticle 
GO

CREATE PROCEDURE dbo.FillOtherArticle 
   @BaseID as bigint
AS

	--DECLARE @BaseID as bigint
	--select @BaseID = max(id) from AccDocItem 
	--select max(id) from AccDocItem 
	--Select * from AccDocumentGoods where ItemID=10971
	--Select * from AccDocItem where id =10971

	DECLARE @Credit as decimal, @Debit as decimal
	DECLARE @ID as bigint, @HeaderID as bigint, @ShowOrder as int
	Select @HeaderID=Headerid, @ShowOrder=ShowOrder, @Credit=Credit, @Debit=Debit from AccDocItem with(nolock) where id=@BaseID 

	DECLARE @C as decimal, @D as decimal
	DECLARE @HID as bigint, @SO as int
	DECLARE @CreditSum as decimal, @DebitSum as decimal

	if @Credit > 0
	BEGIN
		Select @ID=ID, @Debit=Debit from AccDocItem with(nolock) where HeaderID=@HeaderID and ShowOrder=@ShowOrder+1 
		if @Debit = @Credit 
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ItemID=@ID
			Return 1;
		END

		Select @ID=ID, @Debit=Debit from AccDocItem with(nolock) where HeaderID=@HeaderID and ShowOrder=@ShowOrder-1
		if @Debit = @Credit 
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ItemID=@ID 
			Return 1;
		END

		--not One-By-One - search before
		DECLARE myCursor CURSOR FOR SELECT ID, Headerid, ShowOrder, Credit, Debit from dbo.AccDocItem with(nolock) WHERE HeaderID=@HeaderID and ShowOrder<@ShowOrder order by ShowOrder desc
		OPEN myCursor
		SET @DebitSum=0
		FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if @C > 0
				BREAK; --not Found
			SET @DebitSum=@DebitSum+@D
			if @Credit=@DebitSum
				BREAK; --Found
			if @Credit<@DebitSum
				BREAK; --not Found
			FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		END
		CLOSE myCursor
		DEALLOCATE myCursor
		if @Credit=@DebitSum
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ID=@HeaderID and ShowOrder between @SO and @ShowOrder-1 
			Return 1;
		END

		--not One-By-One - search Afte
		DECLARE myCursor CURSOR FOR SELECT ID, Headerid, ShowOrder, Credit, Debit from dbo.AccDocItem with(nolock) WHERE HeaderID=@HeaderID and ShowOrder>@ShowOrder order by ShowOrder desc
		OPEN myCursor
		SET @DebitSum=0
		FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if @C > 0
				BREAK; --not Found
			SET @DebitSum=@DebitSum+@D
			if @Credit=@DebitSum
				BREAK; --Found
			if @Credit<@DebitSum
				BREAK; --not Found
			FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		END
		CLOSE myCursor
		DEALLOCATE myCursor
		if @Credit=@DebitSum
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ID=@HeaderID and ShowOrder between @ShowOrder+1 and @SO
			Return 1;
		END

	END
	else --@Debit > 0
	BEGIN
		Select @ID=ID, @Credit=Credit from AccDocItem with(nolock) where HeaderID=@HeaderID and ShowOrder=@ShowOrder+1 
		if @Debit = @Credit 
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ItemID=@ID
			Return 1;
		END
		Select @ID=ID, @Credit=Credit from AccDocItem with(nolock) where HeaderID=@HeaderID and ShowOrder=@ShowOrder-1
		if @Debit = @Credit 
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ItemID=@ID
			Return 1;
		END
		--not One-By-One - search before
		DECLARE myCursor CURSOR FOR SELECT ID, Headerid, ShowOrder, Credit, Debit from dbo.AccDocItem with(nolock) WHERE HeaderID=@HeaderID and ShowOrder<@ShowOrder order by ShowOrder desc
		OPEN myCursor
		SET @DebitSum=0
		FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if @D > 0
				BREAK; --not Found
			SET @CreditSum=@CreditSum+@C
			if @Debit = @CreditSum
				BREAK; --Found
			if @Debit < @CreditSum
				BREAK; --not Found
			FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		END
		CLOSE myCursor
		DEALLOCATE myCursor
		if @Debit = @CreditSum
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ID=@HeaderID and ShowOrder between @SO and @ShowOrder-1 
			Return 1;
		END

		--not One-By-One - search Afte
		DECLARE myCursor CURSOR FOR SELECT ID, Headerid, ShowOrder, Credit, Debit from dbo.AccDocItem with(nolock) WHERE HeaderID=@HeaderID and ShowOrder>@ShowOrder order by ShowOrder desc
		OPEN myCursor
		SET @DebitSum=0
		FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if @D > 0
				BREAK; --not Found
			SET @CreditSum=@CreditSum+@C
			if @Debit = @CreditSum
				BREAK; --Found
			if @Debit < @CreditSum
				BREAK; --not Found
			FETCH NEXT FROM myCursor INTO @ID, @HID, @SO, @C, @D
		END
		CLOSE myCursor
		DEALLOCATE myCursor
		if @Debit = @CreditSum
		BEGIN
			Select * from AccDocumentGoodsPhase2 with(nolock) where ID=@HeaderID and ShowOrder between @ShowOrder+1 and @SO
			Return 1;
		END
	END

	Return 0;
