/****** Object:  StoredProcedure [dbo].[ReorderFactorNumbers]    Script Date: 30/06/2021 09:39:17 ق.ظ ******/
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]=@FormType 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

	CLOSE myCursor
	DEALLOCATE myCursor

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

SET ANSI_NULLS ON