
/****** Object:  StoredProcedure [dbo].[ReorderDocumentNumbers]    Script Date: 30/06/2021 09:38:26 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[ReorderDocumentNumbers]
	@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_AccDocHeaderNumber' and id = object_id(N'[dbo].[AccDocHeader]'))
		drop index [dbo].[AccDocHeader].[IX_AccDocHeaderNumber]

	ALTER TABLE [dbo].[AccDocHeader] DROP CONSTRAINT [IX_AccDocHeader]

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

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

		FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	END

	CLOSE myCursor
	DEALLOCATE myCursor
	
	if exists(Select Count(*) from [dbo].[AccDocHeader] where Number Between @FromNumber AND @ToNumber group by Number having count(*)>1)
	begin
		rollback
		Select -1
	end
	else
	begin
		ALTER TABLE [dbo].[AccDocHeader] ADD CONSTRAINT [IX_AccDocHeader] UNIQUE  NONCLUSTERED 
		(
			[Number]
		)  ON [PRIMARY] 

		COMMIT

		Select @Count	
	end

RETURN

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