/****** Object:  StoredProcedure [dbo].[ReorderSellFactorNumbers]    Script Date: 30/06/2021 09:41:18 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



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

	BEGIN TRAN

	if exists (select * from dbo.sysindexes where name = N'IX_AccSellFactorNumber' and id = object_id(N'[dbo].[AccSellFactor]'))
		drop index [dbo].[AccSellFactor].[IX_AccSellFactorNumber]
	--ALTER TABLE [dbo].[AccSellFactor] DROP CONSTRAINT [IX_AccSellFactor]

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

	DECLARE myCursor CURSOR FOR SELECT [ID], Number from dbo.AccSellFactor WHERE ID>0 and Number Between @FromNumber AND @ToNumber order by OrderDate, 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
--	print @OldNumber
--	print @NewNumber
END
		UPDATE dbo.AccSellFactor SET Number=@NewNumber where ID=@ID

		FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	END

	CLOSE myCursor
	DEALLOCATE myCursor

	if exists(Select Count(*) from [dbo].[AccSellFactor] WHERE ID>0 and Number Between @FromNumber AND @ToNumber group by Number having count(*)>1)
	begin
		rollback
		Select -1
	end
	else
	begin
		CREATE  UNIQUE  INDEX [IX_AccSellFactorNumber] ON [dbo].[AccSellFactor]([Number]) ON [PRIMARY]
		COMMIT
		Select @Count	
	end

RETURN 

--select ID, Number from dbo.AccDocHeader order by DocDate

