
/****** Object:  StoredProcedure [dbo].[ReorderFactorNumbers]    Script Date: 2/28/2024 10:32:25 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[ReorderFactorNumbers]
	@FormType int=1,
	@StartNumber int=1,
	@FromNumber int=1,
	@ToNumber int=1
AS
	SET NOCOUNT ON 

	BEGIN TRAN

	if @StartNumber < 0
		SET @StartNumber = @FromNumber 
	if @ToNumber< 0 
		select @ToNumber=MAX(Number) from dbo.AccFactor

	DECLARE myCursor CURSOR FOR SELECT [ID], Number from dbo.AccFactor WHERE ID>0 and [Type] in (@FormType,@FormType+1000) and Number between @FromNumber and @ToNumber  order by Date, Number
	OPEN myCursor

	DECLARE @ID as bigint, @OldNumber as bigint, @NewNumber as bigint
	DECLARE @Count as int

	SET @NewNumber=@StartNumber-1
	SET @Count=0

	FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		SET @NewNumber=@NewNumber+1

		IF @OldNumber <> @NewNumber 
		BEGIN
			SET @Count=@Count+1
		END
		
		UPDATE dbo.AccFactor SET Number=@NewNumber where ID=@ID

		FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	END

	if exists(Select Count(*) from [dbo].[AccFactor] WHERE ID>0 and [Type] in (@FormType, @FormType+1000) and Number between @FromNumber and @ToNumber group by Number having count(*)>1)
	begin
		CLOSE myCursor
		DEALLOCATE myCursor
		rollback
		Select -1
	end
	else
	begin
		CLOSE myCursor
		DEALLOCATE myCursor
	
		COMMIT

		Select @Count	
	end
RETURN 

SET ANSI_NULLS ON