/****** Object:  StoredProcedure [dbo].[WareComputeNetPrice]    Script Date: 5/22/2021 01:40:40 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- Batch submitted through debugger: 637WareComputeNetPrice.sql|5|0|E:\Projects\AccWin\AccWin\WinUI\bin\New Changes\637WareComputeNetPrice.sql


ALTER  PROCEDURE [dbo].[WareComputeNetPrice]
	(
		@TotalID bigint=0,
		@ControlID bigint=0,
		@DetailID1 bigint=0,
		@DetailID2 bigint=0,
		@UptoDate datetime,
		@MeanType int=1,
		@WarehouseID bigint=0,
		@WarehouseNo bigint=0,
		@MeanOnly bit=0,
		@TheMean Decimal(38,10) OUTPUT,
		@WriteErrors bit=0
	)
AS
	SET NOCOUNT ON
	--Print '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'
--print @WriteErrors
	--Alavi SET	@WarehouseID = 0

	DECLARE @SumPrice as Decimal(38,10), @SumNum as Decimal(38,10), @OutNum as Decimal(38,10), @InNum as Decimal(38,10)
	DECLARE @TSumPrice as Decimal(38,10), @TSumNum as Decimal(38,10)
	DECLARE @WarehouseNumber as bigint, @OldWarehouseNumber as bigint
	--DECLARE @TheMean as decimal(38,10)
	DECLARE @Request Decimal(38,10)
	DECLARE @FormNum int

	select @Request = Request from AccWarehouseDetails WITH(NOLOCK) 
			where WarehouseID=@WarehouseID 
			AND (@TotalID=0 OR OrderTotalID=@TotalID)
			AND (@ControlID=0 OR OrderControlID=@ControlID) 
			AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
	select @FormNum = Number from AccWarehouse WITH(NOLOCK) where id=@WarehouseID 
	if @Request is null
		set @Request=1
	if @FormNum is null
		select @FormNum=max(Number)+1 from AccWarehouse WITH(NOLOCK)
print '@FormNum' +Str(@FormNum)

	SET @TSumPrice = 0
	SET @TSumNum = 0

	IF @MeanType=-2
	BEGIN
		SELECT @SumPrice=Sum(TotalPrice)+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0)), @SumNum=COALESCE(Sum(Request),0) FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
								  WHERE (not InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13)) and InOrOut <> 12 and [Date] <= @UptoDate 
									and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo) )
					
		SELECT @SumPrice=Sum(TotalPrice)+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0))-@SumPrice, @SumNum=COALESCE(Sum(Request),0)-@SumNum FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
			  WHERE (InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13)) and [Date] <= @UptoDate and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo))

		IF @SumNum is null SET @SumNum=0
		IF @SumPrice is null SET @SumPrice=0

		if @SumNum <> 0 
			SET @TheMean=@SumPrice/@SumNum
		else
			SET @TheMean=0

		if @MeanOnly=0 
			SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
	END
	ELSE IF @MeanType=-1 
	BEGIN
		SELECT @SumPrice=Sum(TotalPrice)+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0)), @SumNum=COALESCE(Sum(Request),0) FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
			  WHERE (not InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13)) and [Date] <= @UptoDate
					and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo))

		if @SumNum > 0 
			SET @TheMean=@SumPrice/@SumNum
		else
			SET @TheMean=0

		if @MeanOnly=0 
			SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
	END

	ELSE IF @MeanType=0 --Simple Mean
	BEGIN
		SELECT @SumPrice=Sum(TotalPrice)+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0)), @SumNum=COALESCE(Sum(Request),0) FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
			  WHERE InOROut in (0,2) and [Date] <= @UptoDate
					and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo))

		if @SumNum <> 0 
			SET @TheMean=@SumPrice/@SumNum
		else
			SET @TheMean=0

		if @MeanOnly=0 
			SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
	END

	ELSE IF @MeanType=1 or @MeanType=10 --True Mean
	BEGIN
		DECLARE MyCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
			SELECT FormType, WarehouseID, WarehouseNo, InOrOut, Number, COALESCE(Request,0), TotalPrice+COALESCE(DetailHaml,0)+COALESCE(DetailTakhfif,0) as TotalPrice, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, [Date]
				FROM dbo.ViwAccWareHouse WITH(NOLOCK)
				WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
				  AND (@ControlID=0 OR OrderControlID=@ControlID) 
				  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
				  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				  and [Date] <= @UptoDate
				  and (@WarehouseNo=0 OR WarehouseNo=@WarehouseNo)
				Order by OrderTotalID,OrderControlID,OrderDetailID1,OrderDetailID2,  [Date], RequestIn Desc, Number


-- InOROut in (N'رسيد',N'اول دوره',N'ب مصرف',N'ب اماني ما',N'اماني ديگران',N'توليد')

		OPEN MyCursor

		SET @OldWarehouseNumber=0

		DECLARE @FormType as int
		DECLARE @InOrOut as nvarchar(100)
		--DECLARE @Request as Decimal(38,10)
		DECLARE @TotalPrice as Decimal(38,10)
		DECLARE @TheWarehouseID as bigint
		DECLARE @Mean as Decimal(38,10)
		DECLARE @OldOrderTotalID as bigint, @OldOrderControlID as bigint, @OldOrderDetailID1 as bigint, @OldOrderDetailID2 as bigint
		DECLARE @OrderTotalID as bigint, @OrderControlID as bigint, @OrderDetailID1 as bigint, @OrderDetailID2 as bigint
		DECLARE @TheWarehouseNo as bigint
		DECLARE @Date as datetime
		set @SumPrice = 0
		SET @SumNum = 0

		FETCH NEXT FROM MyCursor INTO @FormType, @TheWarehouseID, @TheWarehouseNo, @InOrOut, @WarehouseNumber, @Request, @TotalPrice, @OrderTotalID, @OrderControlID, @OrderDetailID1, @OrderDetailID2, @Date
		SET @OldOrderTotalID=@OrderTotalID
		SET @OldOrderControlID=@OrderControlID
		SET @OldOrderDetailID1=@OrderDetailID1
		SET @OldOrderDetailID2=@OrderDetailID2
--		print Str(@OldOrderTotalID)+Str(@OLDOrderControlID)+Str(@OLDOrderDetailID1)+Str(@OldOrderDetailID2)
		print '                   نوع     TheWarehouse   Number  TotalPrice   @Request   @SumPrice   @SumNum   Mean! @TSumPrice,   @TSumNum'
		WHILE @@FETCH_STATUS = 0 
		BEGIN
		--print 'Processing.... '+Str(@FormType) + '-' + Str(@TheWarehouseID)   + '-' + Str(@TheWarehouseNo)  + '-' + @InOrOut  + '-' + Str(@WarehouseNumber)  + '-' + Str(@Request)  + '-' + Str(@TotalPrice)  + '-' + Str(@OrderTotalID)  + '-' + Str(@OrderControlID)  + '-' + Str(@OrderDetailID1)  + '-' + Str(@OrderDetailID2)  
			if @TheWarehouseID=@WarehouseID
			begin
				print '***** BREAK: @TheWarehouseID=' + Str(@TheWarehouseID)
				break
			end
			--print '***>>>>>'+Str(@TotalPrice)+Str(@Request)+Str(@OrderTotalID)+Str(@OrderControlID)+Str(@OrderDetailID1)+Str(@OrderDetailID2)
--print  '***** @TheWarehouseID=' + Str(@TheWarehouseID)
if @TotalPrice < 1 
	set @TotalPrice=0
	
--					print '11b@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Request,12) + str(@SumPrice,12) + Str(@SumNum,12)
			if @FormType in (0,2,13,120,4,6,7,9)
			begin
				set @SumPrice=@SumPrice+@TotalPrice
				set @SumNum=@SumNum+@Request
				--print '#### In '+str(@SumPrice)+Str(@SumNum)
			end
			else 
			begin
				--print '#### Out '+str(@SumPrice)+Str(@SumNum)
				if @MeanType=10 or @MeanType=1
				begin
					set @SumPrice=@SumPrice-@TotalPrice
					set @SumNum=@SumNum-@Request
					--print '#### 2 '+str(@SumPrice)+Str(@SumNum)+Str(@TotalPrice)
				end
				else
				begin
					--print '#### 4 '+str(@TotalPrice)+Str(Round(@SumPrice/@SumNum,0)*@Request)
					if @SumNum <> 0 
						set @SumPrice=@SumPrice-Round(@SumPrice/@SumNum,0)*@Request
					--else
					--begin
						--if @SumPrice <> 0 then
							--set @SumPrice=@SumPrice-@SumPrice/@SumNum*@Request
					--end
					set @SumNum=@SumNum-@Request
					print '2#### 3 '+str(@SumPrice)+Str(@SumNum)
				end
			end
			--if @SumNum<>0 
			--	if @TSumNum <> 0 
			--		print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Request,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(@SumPrice/@SumNum,12) + Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(@TSumPrice/@TSumNum, 12)
			--	else
			--		print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Request,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(@SumPrice/@SumNum,12) + Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(0, 12)
			--else
			--	if @TSumNum <> 0 
			--		print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Request,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(0,12)+ Str(@TSumPrice,12) + STR(@TSumNum, 12)+ Str(@TSumPrice/@TSumNum, 12)
			--	else
			--		print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Request,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(0,12)+ Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(0, 12)

			FETCH NEXT FROM MyCursor INTO @FormType, @TheWarehouseID, @TheWarehouseNo, @InOrOut, @WarehouseNumber, @Request, @TotalPrice, @OrderTotalID, @OrderControlID, @OrderDetailID1, @OrderDetailID2, @Date
			if		@OldOrderTotalID<>@OrderTotalID 
				OR @OldOrderControlID<>@OrderControlID  
				OR @OldOrderDetailID1<>@OrderDetailID1
				OR @OldOrderDetailID2<>@OrderDetailID2
			BEGIN
				if @SumNum <> 0 
					SET @Mean=@SumPrice/@SumNum
				else
					SET @Mean=0

		print '3##########################'+Str(@Mean,12)+Str(@SumNum,12)+str(@SumPrice,12)+Str(@SumNum,12)


				--if @Mean=0 and @SumNum>0
				--begin
				--	--print '@Mean is Zero, Coorect it'
				--	SELECT @Mean=Price FROM dbo.AccWarehouseInfo 
				--		WHERE (@TotalID=0 OR TotalID=@TotalID)
				--		  AND (@ControlID=0 OR ControlID=@ControlID) 
				--		  AND (@DetailID1=0 OR DetailID1=@DetailID1)
				--		  AND (@DetailID2=0 OR DetailID2=@DetailID2)
				--	print '>>>>>>>>>>>>@Mean is set to:' + Str(@Mean,30,3)
				--end
				
				--if @MeanOnly=0 and @Mean = 0 and @SumNum>0
				--BEGIN
				--		SELECT top 1 @Mean=NetPrice 
				--			FROM dbo.ViwAccWareHouse WITH(NOLOCK)
				--			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
				--			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
				--			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
				--			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				--			  and [Date] <= @UptoDate
				--			  and (@WarehouseNo=0 OR WarehouseNo=@WarehouseNo)
				--			  and not FormType in (0,2,13,120,4,6,7,9)
				--			Order by [Date], RequestIn asc
				--	print '>>>>>>>>>>>>@Mean is set to:' + Str(@Mean,30,3)
				--END	
				--if @SumNum < 0
				--begin
				--	set @Mean=0
				--end
	
							print '4#### 4 '+Str(@Mean,12)+Str(@SumNum,12)+str(@SumPrice,12)
	
		print '5@SumNum='+Str(@SumNum)+ '		@SumPrice=' + Str(@SumPrice,12) + '		@SumNum=' + STR(@SumNum,12)
		print '6@SumNum='+Str(@SumNum)+ '		@TSumPrice=' + Str(@TSumPrice,12) + '		@TSumNum=' + STR(@TSumNum,12)
		print '***********************************************'
		print '7  '+Str(@OldOrderTotalID)+Str(@OLDOrderControlID)+Str(@OLDOrderDetailID1)+Str(@OldOrderDetailID2)
				
				SET @TSumPrice = @TSumPrice + @Mean*@SumNum
				SET @TSumNum = @TSumNum + @SumNum

		--print '@Mean='+Str(@Mean)+ '		@SumNum=' + Str(@SumNum)

		print '8@SumNum='+Str(@SumNum)+ '		@SumPrice=' + Str(@SumPrice,12) + '		@SumNum=' + STR(@SumNum)
		print '9@SumNum='+Str(@SumNum)+ '		@TSumPrice=' + Str(@TSumPrice,12) + '		@TSumNum=' + STR(@TSumNum)
				
				SET @SumPrice=0
				SET @SumNum=0	
				SET @OldOrderTotalID=@OrderTotalID
				SET @OldOrderControlID=@OrderControlID
				SET @OldOrderDetailID1=@OrderDetailID1
				SET @OldOrderDetailID2=@OrderDetailID2
			END

		END

		CLOSE MyCursor
		DEALLOCATE MyCursor

--587,421,170,655.82 = 587,421,170,655.82
--589,101,697,401.91  = 584,664,545,647.07

		if @SumNum <> 0 
			SET @Mean=@SumPrice/@SumNum
		else
			SET @Mean=0

		--if @Mean=0 and @SumNum>0
		--begin
		--	--print '@Mean is Zero, Coorect it'
		--	SELECT @Mean=Price FROM dbo.AccWarehouseInfo 
		--		WHERE (@TotalID=0 OR TotalID=@TotalID)
		--		  AND (@ControlID=0 OR ControlID=@ControlID) 
		--		  AND (@DetailID1=0 OR DetailID1=@DetailID1)
		--		  AND (@DetailID2=0 OR DetailID2=@DetailID2)
		--	--print '@Mean is set to:' + Str(@Mean,30,3)
		--end
		print 'a@SumNum='+Str(@SumNum)+ '	@TSumPrice=' + Str(@TSumPrice,12) + '	@TSumNum=' + STR(@TSumNum,12)

				if @SumNum < 0
				begin
					set @Mean=0
				end


		SET @TSumPrice = @TSumPrice + @Mean*@SumNum
		SET @TSumNum = @TSumNum + @SumNum
		print 'b##########################'+Str(@Mean)+Str(@SumNum)+str(@SumPrice)+Str(@SumNum)
		print 'cEND @SumNum='+Str(@SumNum)+ '	@TSumPrice=' + Str(@TSumPrice,12) + '	@TSumNum=' + STR(@TSumNum, 12)

		if @TSumNum <> 0 
			SET @Mean=@TSumPrice/@TSumNum
		else
			SET @Mean=0

		--print '@Mean='+Str(@Mean)

		if @Mean=0 and @SumNum>0
		begin
			--print '@Mean is Zero, Coorect it'
			SELECT @Mean=Price FROM dbo.AccWarehouseInfo 
				WHERE (@TotalID=0 OR TotalID=@TotalID)
				  AND (@ControlID=0 OR ControlID=@ControlID) 
				  AND (@DetailID1=0 OR DetailID1=@DetailID1)
				  AND (@DetailID2=0 OR DetailID2=@DetailID2)
			--print '@Mean is set to:' + Str(@Mean,30,3)
		end

		print 'd@SumNum='+Str(@SumNum)+ '	@TSumPrice=' + Str(@TSumPrice,12) + '	@TSumNum=' + STR(@TSumNum,12)

		SET @TheMean=@Mean
		if @MeanOnly=0 
			if @Mean <> 0 
			begin
				SELECT @TSumPrice as SumPrice, @TSumNum as SumNum, @Mean as Mean
			end
			else
				SELECT top 1 @TSumPrice as SumPrice, @TSumNum as SumNum, NetPrice as Mean
					FROM dbo.ViwAccWareHouse WITH(NOLOCK)
					WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
					  AND (@ControlID=0 OR OrderControlID=@ControlID) 
					  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
					  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
					  and [Date] <= @UptoDate
					  and (@WarehouseNo=0 OR WarehouseNo=@WarehouseNo)
					  and not FormType in (0,2,13,120,4,6,7,9)
					Order by [Date], RequestIn asc
		print 'e@SumNum='+Str(@SumNum)+ '		@TSumPrice=' + Str(@TSumPrice,12) + '		@TSumNum=' + STR(@TSumNum)
		END

	ELSE IF @MeanType=2 --Minimum

	BEGIN
		SELECT @SumPrice=Sum(COALESCE(TotalPrice,0))+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0)), @SumNum=COALESCE(Sum(Request),0) FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
								  WHERE (not InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13)) and InOrOut <> 12 and [Date] <= @UptoDate 
									and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo) )
					
		SELECT @SumPrice=Sum(COALESCE(TotalPrice,0))+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0))-@SumPrice, @SumNum=COALESCE(Sum(Request),0)-@SumNum 
			FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
			  WHERE (InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13, 120)) and [Date] <= @UptoDate and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo))

		SELECT @OutNum=Min((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))/dbo.AccWarehouseDetails.Request) 
			FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND InOrOut in (0,2,4,6,7,9,13,120) and [Date] <= @UptoDate --AND dbo.AccWarehouse.Number > @WarehouseNumber
				AND dbo.AccWarehouseDetails.Request>0

			IF @SumNum is null SET @SumNum=0
			IF @SumPrice is null SET @SumPrice=0
			IF @OutNum is null SET @OutNum=0
			SELECT @SumPrice as SumPrice,@SumNum as SumNum, @OutNum as Mean
	END

	ELSE IF @MeanType=3 --Maximum

	BEGIN
		SELECT @SumPrice=Sum(COALESCE(TotalPrice,0))+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0)), @SumNum=COALESCE(Sum(Request),0) FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
								  WHERE (not InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13)) and InOrOut <> 12 and [Date] <= @UptoDate 
									and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo) )
					
		SELECT @SumPrice=Sum(COALESCE(TotalPrice,0))+Sum(COALESCE(Haml,0))+Sum(COALESCE(Takhfif,0))-@SumPrice, @SumNum=COALESCE(Sum(Request),0)-@SumNum FROM dbo.AccWarehouseDetails WITH(NOLOCK)
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			  AND WarehouseID in (SELECT ID FROM dbo.AccWarehouse WITH(NOLOCK) 
			  WHERE (InOrOut in (0,2 ,4 ,6 ,7 ,9 ,13,120)) and [Date] <= @UptoDate and (@WarehouseNo=0 OR WarehouseID=@WarehouseNo))

		SELECT @OutNum=Max((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))/dbo.AccWarehouseDetails.Request) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND InOrOut in (0,2,4,6,7,9,13,120) and [Date] <= @UptoDate --AND dbo.AccWarehouse.Number > @WarehouseNumber
				AND dbo.AccWarehouseDetails.Request>0

			IF @SumNum is null SET @SumNum=0
			IF @SumPrice is null SET @SumPrice=0
			IF @OutNum is null SET @OutNum=0
			SELECT @SumPrice as SumPrice,@SumNum as SumNum, @OutNum as Mean
	END

	ELSE IF @MeanType=4 --FIFO, First In First Out
	BEGIN
		print 'FIFO'

		DECLARE @OutPrice as Decimal(38,10)
		DECLARE @InPrice as Decimal(38,10)
		DECLARE @ID  as bigint
		SELECT @OutPrice=COALESCE(Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))),0), @OutNum=COALESCE(Sum(dbo.AccWarehouseDetails.Request),0) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (not InOrOut in (0,2,4,6,7,9,13,120)) and ([Date] < @UptoDate or ([Date] = @UptoDate and dbo.AccWarehouse.Number<@FormNum))
				and (@WarehouseNo=0 OR dbo.AccWarehouse.WarehouseID=@WarehouseNo) 
		IF @OutNum is null SET @OutNum=0
		IF @OutPrice is null SET @OutPrice=0
		
		SET @WarehouseNumber=-1

print '@OutNum'+Str(@OutNum)
print '@OutPrice'
print @OutPrice
print ''
		DECLARE MyCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
			SELECT dbo.AccWarehouseDetails.Request, (COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0)) as TotalPrice, dbo.AccWarehouse.Number, dbo.AccWarehouseDetails.ID, dbo.AccWarehouse.InOrOut 
			FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
		WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			AND (@ControlID=0 OR OrderControlID=@ControlID) 
			AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
			and (@WarehouseNo=0 OR dbo.AccWarehouse.WarehouseID=@WarehouseNo) 
			AND (InOrOut in (0,2,4,6,7,9,13,120)) 
		Order by dbo.AccWarehouse.[Date], dbo.AccWarehouse.[Number] 

		OPEN MyCursor

		FETCH NEXT FROM MyCursor INTO @InNum, @InPrice, @WarehouseNumber, @ID, @InOrOut
		WHILE @@FETCH_STATUS = 0 AND not @InNum is null 
		BEGIN
		print ' 	@OutNum'+Str(@OutNum)
		print ' 	@OutPrice'
		print	@OutPrice
		print '		@InNum(Request) '+Str(@InNum)
		print '		TotalPrice+Haml+Takhfif '+Str(@InPrice)
		print '		@ID- '+Str(@ID)
		print '		@InOrOut '+Str(@InOrOut)
			IF (@OutNum-@InNum) <= -@Request -- از این رسید استفاده شود
			BEGIN
	print '		Use @ID'+Str(@ID)
				IF (@OutNum) = 0-- -@Request -- فقط از این رسید استفاده شود 
				BEGIN
		print '		Use One @ID'+Str(@ID)
					SELECT @InPrice as SumPrice, @InNum as SumNum, @InPrice/@InNum as Mean
					CLOSE MyCursor
					DEALLOCATE MyCursor
					return
				END
				ELSE -- از رسید قبلی نیز کمک گرفته شود
				BEGIN
		print '		Use @ID'+Str(@ID)+'  AND ... '
					set @OutNum=@OutNum-@InNum
					set @OutPrice=@OutPrice-@InPrice 
					SELECT -@OutPrice as SumPrice, -@OutNum as SumNum, @OutPrice/@OutNum as Mean
					CLOSE MyCursor
					DEALLOCATE MyCursor
					return
				END
			end
			set @OutNum=@OutNum-@InNum
			set @OutPrice=@OutPrice-@InPrice 
		print '		Continue.... '
			FETCH NEXT FROM MyCursor INTO @InNum, @InPrice, @WarehouseNumber, @ID, @InOrOut
		END
print '*********End***********'
		print ' 	@OutNum'+Str(@OutNum)
		print ' 	@OutPrice'
		print	@OutPrice
		print '		@InNum(Request) '+Str(@InNum)
		print '		TotalPrice+Haml+Takhfif '+Str(@InPrice)
		print '		@ID- '+Str(@ID)
		print '		@InOrOut '+Str(@InOrOut)

		if @OutNum <> 0 
			SELECT -@OutPrice as SumPrice, -@OutNum as SumNum, @OutPrice/@OutNum as Mean
		else
			SELECT -@OutPrice as SumPrice, -@OutNum as SumNum, 0 as Mean
		--SELECT @InPrice as SumPrice, @InNum as SumNum, @InPrice/@InNum as Mean
		CLOSE MyCursor
		DEALLOCATE MyCursor
		return
	END
	ELSE IF @MeanType=5 --LIFO, Last In First Out
	BEGIN

		print 'LIFO'

		SELECT @OutNum=COALESCE(Sum(dbo.AccWarehouseDetails.Request),0) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (not InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate

		IF @OutNum is null SET @OutNum=0

		SET @WarehouseNumber=-1
		IF @OutNum > 0 
		BEGIN
			DECLARE MyCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
				SELECT dbo.AccWarehouseDetails.Request, dbo.AccWarehouse.Number  FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
		         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
						AND dbo.AccWarehouse.InOrOut in (0,2,4,6,7,9,13,120) and dbo.AccWarehouse.[Date] <= @UptoDate
			Order by dbo.AccWarehouse.[Date], dbo.AccWarehouse.[Number]

			OPEN MyCursor

			SET @OldWarehouseNumber=-1

			FETCH NEXT FROM MyCursor INTO @SumPrice, @WarehouseNumber -- Temporary use of @SumPrice
			WHILE @@FETCH_STATUS = 0 AND not @SumPrice is null and (@OutNum > 0 OR @OldWarehouseNumber = @WarehouseNumber)
			BEGIN
			SET @OutNum=@OutNum-@SumPrice
				SET @OldWarehouseNumber=@WarehouseNumber
				FETCH NEXT FROM MyCursor INTO @SumPrice, @WarehouseNumber
			END

			set @WarehouseNumber=@OldWarehouseNumber
			CLOSE MyCursor
			DEALLOCATE MyCursor
		END
		ELSE
			SELECT @WarehouseNumber=Max(dbo.AccWarehouse.Number)  FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
	         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
					AND dbo.AccWarehouse.InOrOut in (0,2,4,6,7,9,13,120) and dbo.AccWarehouse.[Date] <= @UptoDate

		IF @OutNum < 0
		BEGIN -- Use Portions of @WarehouseNumber
			SELECT @SumPrice=Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))), @SumNum=COALESCE(Sum(dbo.AccWarehouseDetails.Request),0) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
   	      dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
					AND InOrOut in (0,2,4,6,7,9,13,120) and [Date] <= @UptoDate AND dbo.AccWarehouse.Number = @WarehouseNumber
			if @SumNum > 0 
				SET @TheMean= @SumPrice/@SumNum 
			if @MeanOnly=0 
				SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
		END
		ELSE
		begin
				SET @TheMean= 0
				if @MeanOnly=0 
					SELECT 0 as SumPrice,-@OutNum as SumNum,0 as Mean
		end
	END

	ELSE IF @MeanType=6 --آخرین خرید
	BEGIN

		SET @SumNum=0
		SET @SumPrice=0
		SET @TheMean=Null
		SELECT @SumNum=COALESCE(Sum(dbo.AccWarehouseDetails.Request),0), @SumPrice=Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
			
		--	print @SumNum	
		
		SELECT @SumNum=@SumNum-COALESCE(Sum(dbo.AccWarehouseDetails.Request),0), @SumPrice=@SumPrice-Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (not InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
				and @WarehouseID<>dbo.AccWarehouse.ID

		--	print '@SumNum:' + Str(@SumNum)

		SELECT TOP 1 @TheMean=dbo.AccWarehouseDetails.NetPrice FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
			Order by dbo.AccWarehouse.Number DESC

			print @SumPrice	

		IF @TheMean is null SET @TheMean=0
		SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
	END
	ELSE IF @MeanType=7 --آخرین فروش
	BEGIN
		SET @SumNum=0
		SET @SumPrice=0
		SET @TheMean=Null
		SELECT @SumNum=COALESCE(Sum(dbo.AccWarehouseDetails.Request),0), @SumPrice=Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
				
		SELECT @SumNum=@SumNum-COALESCE(Sum(dbo.AccWarehouseDetails.Request),0), @SumPrice=@SumPrice-Sum((COALESCE(dbo.AccWarehouseDetails.TotalPrice,0)+COALESCE(dbo.AccWarehouseDetails.Haml,0)+COALESCE(dbo.AccWarehouseDetails.Takhfif,0))) FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (not InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
				and @WarehouseID<>dbo.AccWarehouse.ID

		SELECT TOP 1 @TheMean=dbo.AccWarehouseDetails.NetPrice FROM dbo.AccWarehouse WITH (NOLOCK) LEFT OUTER JOIN
         dbo.AccWarehouseDetails ON dbo.AccWarehouse.ID = dbo.AccWarehouseDetails.WarehouseID
			WHERE (@TotalID=0 OR OrderTotalID=@TotalID)
			  AND (@ControlID=0 OR OrderControlID=@ControlID) 
			  AND (@DetailID1=0 OR OrderDetailID1=@DetailID1)
			  AND (@DetailID2=0 OR OrderDetailID2=@DetailID2)
				AND (NOT InOrOut  in (0,2,4,6,7,9,13,120)) and [Date] <= @UptoDate
			Order by dbo.AccWarehouse.Number DESC

		IF @TheMean is null SET @TheMean=0
		SELECT @SumPrice as SumPrice, @SumNum as SumNum, @TheMean as Mean
	END

	ELSE --Error in Calling!, Reserved!
	begin
			SET @TheMean= 0
			if @MeanOnly=0 
				SELECT 0 as SumPrice,0 as SumNum,0 as Mean
	end
	--Print 'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE'

	RETURN @TheMean
		