
/****** Object:  StoredProcedure [dbo].[ReorderWarehouseNumbers]    Script Date: 30/06/2021 09:42:24 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



ALTER PROCEDURE [dbo].[ReorderWarehouseNumbers]
	@FormType int=1,
	@WarehouseNo bigint=1,
	@StartNumber int=1
AS
	SET NOCOUNT ON 

	BEGIN TRAN

	DECLARE myCursor CURSOR FOR SELECT [ID], Number from dbo.AccWarehouse WHERE ID>0 and InOROut=@FormType and WarehouseID=@WarehouseNo order by Date, Number
	OPEN myCursor

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

	SET @NewNumber=@StartNumber
	SET @Count=0

	FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		IF @OldNumber <> @NewNumber 
		BEGIN
			SET @Count=@Count+1
		END
		
		UPDATE dbo.AccWarehouse SET Number=@NewNumber where ID=@ID

		SET @NewNumber=@NewNumber+1
		FETCH NEXT FROM myCursor INTO @ID, @OldNumber
	END

	CLOSE myCursor
	DEALLOCATE myCursor
	
	if exists(Select Count(*) from [dbo].[AccWarehouse] WHERE ID>0 and InOROut=@FormType and WarehouseID=@WarehouseNo group by Number having count(*)>1)
	begin
		rollback
		Select -1
	end
	else
	begin
		COMMIT
		Select @Count	
	end

RETURN 


SET QUOTED_IDENTIFIER ON 