/****** Object:  StoredProcedure [dbo].[AccWareNotDeliverdSerials]    Script Date: 22/01/1402 09:14:51 ب.ظ ******/
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
ALTER PROCEDURE [dbo].[AccWareNotDeliverdSerials]
	(
		@Type as int,
		@StartDate as date,
		@EndDate as date,
		@OrderTotalID as bigint,
		@OrderControlID as bigint,
		@OrderDetailID1 as bigint,
		@OrderDetailID2 as bigint,
		@WarehouseNum as bigint,
		@Filter as NvarChar(2000)=''
	)
AS
	set nocount on
	--select * from ViwAccWareHouse  
	DECLARE @WarehouseID as bigint
	DECLARE @DetailID as bigint
	DECLARE @SerialFrom as NVARCHAR(20)
	DECLARE @SerialTo as NVARCHAR(20)
	DECLARE @SerialLeft as NVarCHAR(15)
	DECLARE @NSerialFrom as int
	DECLARE @NSerialTo as int
	DECLARE @Serial as int
	DECLARE @Date as Date
	DECLARE @MyRowCount as int

	print '1: ' + convert(varchar(25), CURRENT_TIMESTAMP, 121) 
	IF not @Type IN (3,4)
	BEGIN
		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 (@OrderTotalID=0 OR OrderTotalID=@OrderTotalID)
			and (@OrderControlID=0 OR OrderControlID=@OrderControlID)
			and (@OrderDetailID1=0 OR OrderDetailID1=@OrderDetailID1)
			and (@OrderDetailID2=0 OR OrderDetailID2=@OrderDetailID2)
			and (@WarehouseNum=0 OR WarehouseNo=@WarehouseNum)
			and WarehouseID in (Select ID from AccWarehouse with(nolock) where InOROut in (0,2,4,6,7,10,13,12,121) AND cast([Date] as date) between @StartDate AND @EndDate)

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

		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SerialFrom=RTRIM(@SerialFrom)
			SET @SerialTo=RTRIM(@SerialTo)
			--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
				select @Date = [Date] from AccWarehouse where id = @WarehouseID
				--print @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				Insert Into #WareIn(DetailID,WarehouseID,Serial, InDate) Values(@DetailID, @WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5), @Date)
				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

		--Select Count(*), '#WareIn' from #WareIn 
		Print '---Deleteing..... '
	END --IF @Type IN (3,4)

	print '2: ' + convert(varchar(25), CURRENT_TIMESTAMP, 121) 
	DECLARE myCursor CURSOR FOR 
	select ID, WareDetailsID, VSerialFrom, VSerialTo, VDate 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) 
				Where (@OrderTotalID=0 OR OrderTotalID=@OrderTotalID)
				and (@OrderControlID=0 OR OrderControlID=@OrderControlID)
				and (@OrderDetailID1=0 OR OrderDetailID1=@OrderDetailID1)
				and (@OrderDetailID2=0 OR OrderDetailID2=@OrderDetailID2)
				and (@WarehouseNum=0 OR WarehouseNo =@WarehouseNum)
			)
		AND cast(VDate as date) between @StartDate AND @EndDate
	union all
	select 0 as ID, ID as WareDetailsID, SerialFrom as VSerialFrom, SerialTo as VSerialTo, VDate 
		from AccWarehouseDetails with(nolock)
		where not SerialFrom is null and not SerialTo is null and Len(SerialFrom)>0 and Len(Serialfrom)=Len(SerialTo) 
			and (@OrderTotalID=0 OR OrderTotalID=@OrderTotalID)
				and (@OrderControlID=0 OR OrderControlID=@OrderControlID)
				and (@OrderDetailID1=0 OR OrderDetailID1=@OrderDetailID1)
				and (@OrderDetailID2=0 OR OrderDetailID2=@OrderDetailID2)
			and WarehouseID in (select id from AccWarehouse where InOROut in (1,3,5,8,9,11,12,120))  
		--AND cast(VDate as date) 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(20),
		InDate Date
	)
	--drop table #WareOut 
	create table #WareOut
	(
		ID bigint IDENTITY(1,1) NOT NULL,
		DeliveryID bigint, 
		WareDetailsID bigint, 
		Serial Varchar(20),
		DelvDate Date
	)


	SET @DELETED=0
	OPEN myCursor
	FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo, @Date
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SerialFrom=RTRIM(@SerialFrom)
		SET @SerialTo=RTRIM(@SerialTo)
		--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)

		--print @NSerialFrom 
		--print @NSerialTo 
		--print @SerialLeft 

		--set @NSerialTo=@NSerialTo+10

		--print Str(@NSerialFrom) + '|' + Str(@NSerialTo)
		
		SET @Serial=@NSerialFrom
		while @Serial <= @NSerialTo
		BEGIN
			--print @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
			IF @Type IN (3,4)
				Insert Into #WareOut(DeliveryID,WareDetailsID,Serial,DelvDate) Values(@DetailID, @WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5), @Date)
			ELSE
			BEGIN

				Delete top(1) #WareIn where Serial=@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				SET @MyRowCount = @@ROWCOUNT
--print '@@ROWCOUNT'+Str(@MyRowCount)
if @MyRowCount <> 1
	print '@@ROWCOUNT'+Str(@MyRowCount)
			
				if @MyRowCount > 0
				BEGIN
					SET @DELETED = @DELETED + 1
				END
				else
				BEGIN
					Insert Into #NotExist(WareDetailsID,Serial) Values(@WarehouseID, @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5))
				END

--SET @N=@MyRowCount
--Select @N=Count(*) from #WareIn
--print 'DEL  ' + @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)+Str(@N)+Str(@DELETED)+Str(@WarehouseID)

				--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'
				--	SET @DELETED = @DELETED + 1
				--		print 'Deleteing ' +@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) +'  '+Str(@WarehouseID)
				--	--Delete top(1) #WareIn where Serial=@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				--END
				--else
				--BEGIN
				--	--if @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5) = 'IN2005004953'
				--	SET @DELETED = @DELETED + 1
				--		--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
			END
			SET @Serial=@Serial+1
		END
			--print '			22: ' + Str(@WarehouseID) +' '+ convert(varchar(25), CURRENT_TIMESTAMP, 121) 
		FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo, @Date
	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 '3: ' + convert(varchar(25), CURRENT_TIMESTAMP, 121) 
	print @DELETED
	CLOSE myCursor
	DEALLOCATE myCursor
	 
	/******************************************************
	 ** Delete SellReturn's from Delivered (#WareOut)    **
	 ******************************************************/
 	IF  @Type IN (3,4)
	BEGIN
		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 (@OrderTotalID=0 OR OrderTotalID=@OrderTotalID)
			and (@OrderControlID=0 OR OrderControlID=@OrderControlID)
			and (@OrderDetailID1=0 OR OrderDetailID1=@OrderDetailID1)
			and (@OrderDetailID2=0 OR OrderDetailID2=@OrderDetailID2)
			and (@WarehouseNum=0 OR WarehouseNo=@WarehouseNum)
			and WarehouseID in (Select ID from AccWarehouse with(nolock) where InOROut in (121) AND cast([Date] as date) between @StartDate AND @EndDate)

	 	SET @DELETED=0
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @DetailID, @WarehouseID, @SerialFrom, @SerialTo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SerialFrom=RTRIM(@SerialFrom)
			SET @SerialTo=RTRIM(@SerialTo)
			print 'Return @Serial'
			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)

			print @NSerialFrom 
			print @NSerialTo 
			print @SerialLeft 
	
			SET @Serial=@NSerialFrom
			while @Serial <= @NSerialTo
			BEGIN
				--print @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				Delete top(1) #WareOut where Serial=@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				SET @MyRowCount = @@ROWCOUNT
				print '@@ROWCOUNT'+Str(@MyRowCount)
			
				if @MyRowCount > 0
				BEGIN
					SET @DELETED = @DELETED + 1
				END
				else
				BEGIN
					print '**not found Returned'+@SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)
				END

				SET @N=@MyRowCount
				print 'DEL  Return ' + @SerialLeft+RIGHT('00000'+LTRIM(Str(@Serial)),5)+Str(@DELETED)+Str(@WarehouseID)

				SET @Serial=@Serial+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

		CLOSE myCursor
		DEALLOCATE myCursor
	END
	print '4: ' + convert(varchar(25), CURRENT_TIMESTAMP, 121) 

	--IF not @Type IN (3,4) 
		--Select Count(*), '#WareIn' from #WareIn 
	--Select Count(*), '#WareOut' from #WareOut 
	--Select Count(*), '#NotExist' from #NotExist 

/*
	@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, main.FormID as FactorID, main.FromForm
				, 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, main.FormID, main.FromForm
			, 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, main.FormID as FactorID, main.FromForm
		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, main.FormID, main.FromForm
		
	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, main.FormID as FactorID, main.FromForm,
				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 #WareOut 
			left Outer Join AccWarehouseDetails as Detail on Detail.id= #WareOut.WareDetailsID  
			left Outer Join AccWarehouse as Main on Main.id= Detail.WarehouseID 
		group by Main.id, #WareOut.ID, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project, main.FormID, main.FromForm
				, 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, main.FormID, main.FromForm
				--,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 #WareOut 
			left Outer Join AccWarehouseDetails as Detail on Detail.id= #WareOut.WareDetailsID  
			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, main.FormID, main.FromForm
				--, 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, main.FormID as FactorID, main.FromForm
		--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, main.FormID as FactorID, main.FromForm
				, 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.id, #NotExist.WareDetailsID, Main.Number, main.Date, main.WarehouseID, Main.PersonTotalID , Main.PersonControlID , Main.PersonDetailID1 , Main.PersonDetailID2, Main.Flags, Main.CostCenter, Main.Project, main.FormID, main.FromForm
				, 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, main.FormID as FactorID, main.FromForm
			--	#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)
	
	IF not @Type IN (3,4)
		drop table #WareIn 
	drop table #WareOut
	drop table #NotExist 

		print '5: ' + convert(varchar(25), CURRENT_TIMESTAMP, 121) 

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

return