/****** Object:  StoredProcedure [dbo].[AccWareNotDeliverdSerials]    Script Date: 02/11/2022 11:09:13 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
	@Type=1 : Remain-Detail
	@Type=2 : Remain-Group By Form
	@Type=3 : Remain-Group By Order
	@Type=4 : Delievred but not exists-Detail
*/

--select * from ViwAccWareHouse  
-- [AccWareNotDeliverdSerials] 1
CREATE PROCEDURE [dbo].[AccWareNotDeliverdSerials]
	(
		@Type as int,
		@StartDate as datetime,
		@EndDate as datetime,
		@Filter as NvarChar(2000)=''
	)
AS
	set nocount on
	--select * from ViwAccWareHouse  
	DECLARE @WarehouseID as bigint
	DECLARE @DetailID as bigint
	DECLARE @SerialFrom as NCHAR(12)
	DECLARE @SerialTo as NCHAR(12)
	DECLARE @SerialLeft as NCHAR(7)
	DECLARE @NSerialFrom as int
	DECLARE @NSerialTo as int
	DECLARE @Serial as int

	DECLARE myCursor CURSOR FOR 
	select ID, WarehouseID, SerialFrom, SerialTo from AccWarehouseDetails with(nolock)
	where  not SerialFrom is null and not SerialTo is null and Len(SerialFrom)>0 and Len(Serialfrom)=Len(SerialTo) 
		and WarehouseID in (Select ID from AccWarehouse with(nolock) where InOROut=0 AND [Date] between @StartDate AND @EndDate)
		


	--drop table #WareIn 
	create table #WareIn
	(
		ID bigint IDENTITY(1,1) NOT NULL,
		WarehouseID bigint, 
		DetailID bigint, 
		Serial Varchar(12)
	)

	OPEN myCursor
	FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--print @SerialFrom + '|' + @SerialTo
		set @NSerialFrom = CAST ( Right(@SerialFrom, 5) as integer)
		set @NSerialTo = CAST ( Right(@SerialTo, 5) as integer)
		set @SerialLeft = Left(@SerialFrom, Len(@SerialFrom)-5)
		--set @NSerialTo=@NSerialTo+10

		--print Str(@NSerialFrom) + '|' + Str(@NSerialTo)
		
		SET @Serial=@NSerialFrom
		while @Serial <= @NSerialTo
		BEGIN
			--print @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
			Insert Into #WareIn(DetailID,WarehouseID,Serial) Values(@DetailID, @WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5))
			SET @Serial=@Serial+1
		END

		FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
	END
	--select 'All', * from #WareIn where Serial like 'IN2005004953%' or Serial = 'IN2201060100'

	CLOSE myCursor
	DEALLOCATE myCursor


	Print '---Deleteing..... '

	DECLARE myCursor CURSOR FOR 
	select ID, WareDetailsID, VSerialFrom, VSerialTo from AccWarehouseDelivery with(nolock)
	where flags=0 and not VSerialFrom is null and not VSerialTo is null and Len(VSerialFrom)>0 and Len(VSerialfrom)=Len(VSerialTo) 
		and WareDetailsID in (Select ID from AccWarehouseDetails with(nolock)) AND VDate between @StartDate AND @EndDate

	DECLARE @DELETED as int
	DECLARE @N as integer

	--drop table #NotExist 
	create table #NotExist
	(
		ID bigint IDENTITY(1,1) NOT NULL,
		WareDetailsID bigint, 
		Serial Varchar(12)
	)
	--drop table #WareOut 
	create table #WareOut
	(
		ID bigint IDENTITY(1,1) NOT NULL,
		WarehouseID bigint, 
		DetailID bigint, 
		Serial Varchar(12)
	)


	SET @DELETED=0
	OPEN myCursor
	FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--print '@SerialFrom'
		--print @SerialFrom + '|' + @SerialTo
		set @NSerialFrom = CAST ( Right(@SerialFrom, 5) as integer)
		set @NSerialTo = CAST ( Right(@SerialTo, 5) as integer)
		set @SerialLeft = Left(@SerialFrom, Len(@SerialFrom)-5)
		--set @NSerialTo=@NSerialTo+10

		--print Str(@NSerialFrom) + '|' + Str(@NSerialTo)
		
		SET @Serial=@NSerialFrom
		while @Serial <= @NSerialTo
		BEGIN
			--print @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
			
			Insert Into #WareOut(DetailID,WarehouseID,Serial) Values(@DetailID, @WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5))

			Select @N=Count(*) from  #WareIn where Serial=@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
			if @N > 0
			BEGIN
				if @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) = 'IN2005004953'
					print 'Deleteing ' +@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) +'  '+Str(@WarehouseID)
				Delete #WareIn where Serial=@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
			END
			else
			BEGIN
				if @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) = 'IN2005004953'
					print 'NotExist ' +@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) +'  '+Str(@WarehouseID)
				Insert Into #NotExist(WareDetailsID,Serial) Values(@WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5))
			END

			SET @Serial=@Serial+1
			SET @DELETED = @DELETED + 1
		END

		FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
	END
	--select 'Deleted', * from #WareIn where Serial like 'IN2005004953%' or Serial = 'IN2201060100'
	--select 'Delivered', * from #WareOut where Serial like 'IN2005004953%' or Serial = 'IN2201060100'
	--select 'NotExist', * from #NotExist where Serial like 'IN2005004953%' or Serial = 'IN2201060100'
	print @DELETED

/*
	@Type=1 : Remain-Detail
	@Type=2 : Remain-Group By Form
	@Type=3 : Remain-Group By Order
	@Type=4 : Delievred but not exists-Detail
*/
	if @type=1 --remain-Detail
	--10466
		select  #WareIn.Serial as SerialFrom, Main.id, Main.Number, main.Date, main.WarehouseID as WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project, 
				Detail.ID as DetailID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		from AccWarehouseDetails as Detail with(nolock)
				right outer join #WareIn on Detail.id= #WareIn.DetailID 
				Left Outer Join AccWarehouse as Main with(nolock) on Main.id= Detail.WarehouseID 
--		from AccWarehouse as Main right outer join #WareIn on Main.id= #WareIn.WarehouseID 
		group by #WareIn.Serial, Main.id, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project,
			Detail.ID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		
	else if @type=2 --Remain-Group By Form
	--3
		select  Min(#WareIn.Serial) as SerialFrom, Main.id, Main.Number, main.Date, main.WarehouseID as WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project
		from AccWarehouse as Main right outer join #WareIn on Main.id= #WareIn.WarehouseID 
		group by Main.id, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project 
		
	else if @type=3 --Delievred-Detail
		select Min(#WareOut.Serial) as SerialFrom, Main.id, Main.Number, main.Date, main.WarehouseID as WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project,
				Detail.ID as DetailID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		from AccWarehouseDetails as Detail 
				right outer join #WareOut on Detail.id= #WareOut.WarehouseID  
				Left Outer Join AccWarehouse as Main on Main.id= Detail.WarehouseID 
		group by Main.id, #WareOut.DetailID, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project
				, Detail.ID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
	else if @type=4 --Delievred-Group By Form
		select Min(#WareOut.Serial) as SerialFrom, Main.id, Main.Number, main.Date, main.WarehouseID as WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project
				--, Detail.ID as DetailID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		from AccWarehouseDetails as Detail 
				right outer join #WareOut on Detail.id= #WareOut.WarehouseID 
				Left Outer Join AccWarehouse as Main on Main.id= Detail.WarehouseID 
		group by Main.id, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project
				--, Detail.ID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
	else if @type=5 --Errors - Delievred but not exists-Detail
--		select distinct  #NotExist.Serial as SerialFrom, Main.WarehouseID as WarehouseNo, Main.InOROut, Main.Number,  Main.UserNumber, main.Date, Main.DatePer, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Description, main.UpdateDate, main.Updater,Main.PersonTitle, Main.Flags, Main.CostCenter, Main.Project
		--select distinct  #NotExist.Serial,main.SerialFrom,main.SerialTo,  Main.*
				--Detail.ID as DetailID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		select Min(#NotExist.Serial) as SerialFrom, Main.id, Main.Number, main.Date, main.WarehouseID as WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project,
				Detail.ID as DetailID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		from AccWarehouseDetails as Detail with(nolock)
				--right outer join #WareIn on Detail.id= #WareIn.DetailID 
				--Left Outer Join AccWarehouse as Main with(nolock) on Main.id= Detail.WarehouseID 
				right outer join #NotExist on Detail.id = #NotExist.WareDetailsID  
				Left Outer Join AccWarehouse as Main with(nolock) on Main.ID = Detail.WarehouseID 
		group by Main.id, #NotExist.WareDetailsID, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project
				, Detail.ID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
		--group by #NotExist.Serial, Main.WarehouseID, Main.InOROut, Main.Number,  Main.UserNumber, main.Date, Main.DatePer, main.WarehouseNo, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Description, main.UpdateDate, main.Updater,Main.PersonTitle, Main.Flags, Main.CostCenter, Main.Project
			--	#NotExist.Serial, Detail.ID, Detail.Request, Detail.NetPrice, Detail.TotalPrice, Detail.OrderTotalID, Detail.OrderControlID ,Detail.OrderDetailID1 ,Detail.OrderDetailID2 , Detail.VDate, Detail.VRequest, Detail.VUpdater 
--[AccWareNotDeliverdSerials] 4
--select * from AccWarehouseDetails  where id in (1067,1463)
--select  * from AccWarehouseDelivery where WareDetailsID in(1067,1463)
	CLOSE myCursor
	DEALLOCATE myCursor
	drop table #WareIn 
	drop table #WareOut
	drop table #NotExist 

	--select * from AccWarehouseDetails where id = 1067
	--select * from AccWarehouseDelivery where WareDetailsID = 1067
	--select * from AccWarehouseDetails where SerialFrom like 'IN2201051%'

return