/****** Object:  StoredProcedure [dbo].[WareReComputeNetPriceRealMean]    Script Date: 01/02/1405 03:49:14 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--RealMean==1
ALTER PROCEDURE [dbo].[WareReComputeNetPriceRealMean]
	(
		@WarehouseNo bigint=0,
		@WriteErrors bit=0,
		@UseHaml bit=1,
		@UseTakhfif bit=1,
		@Decimal as int=3
	)
AS
	SET NOCOUNT ON
	DECLARE @DEBUG as int
	SET @DEBUG = 0

	if @DEBUG > 0 Print '@WarehouseNo:'+Str(@WarehouseNo)+'@UseHaml:'+Str(@UseHaml)+'@UseTakhfif'+Str(@UseTakhfif)
	if @DEBUG > 0 Print @WriteErrors

	DECLARE @ChangeCount as int
	SET @ChangeCount=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 @Used Decimal(38,10)
	DECLARE @FormNum int

	--select @Used = 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 @Used is null
	--	set @Used=0
	--if @FormNum is null
	--	select @FormNum=max(Number)+1 from AccWarehouse WITH(NOLOCK)
	--if @DEBUG > 0 Print '@FormNum' +Str(@FormNum)

	SET @TSumPrice = 0
	SET @TSumNum = 0
-- IF @MeanType=1 or @MeanType=10 --True Mean

/*
select * from AccwarehouseDetails where id=6056
SELECT ID, FormType, WarehouseID, WarehouseNo, InOrOut, Number, COALESCE(Request,0) as Request, TotalPrice, NetPrice, RequestIn,RequestOut, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, [Date]
			FROM dbo.ViwAccWareHouse WITH(NOLOCK)
			where WarehouseNo=3 and OrderControlID=77
			Order by OrderTotalID,OrderControlID,OrderDetailID1,OrderDetailID2,  [Date], RequestIn Desc, Number
--select * from AccDetail where id in (42,410)
-- Select * from Acccontrol where id=77
			*/
	DECLARE MyCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
		SELECT ID, FormType, WarehouseID, WarehouseNo, InOrOut, Number, COALESCE(Request,0) as Request, TotalPrice+(CASE WHEN @UseHaml=0 THEN 0 ELSE COALESCE(DetailHaml,0) END)+(CASE WHEN @UseTakhfif=0 THEN 0 ELSE COALESCE(DetailTakhfif,0) END) as TotalPrice, NetPrice, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, [Date]
			FROM dbo.ViwAccWareHouse WITH(NOLOCK)
			WHERE FormType<1000 AND (@WarehouseNo=0 OR WarehouseNo=@WarehouseNo)
			--where WarehouseNo=3 and OrderControlID=77
			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 @Used as Decimal(38,10)
		DECLARE @ID as bigint
		DECLARE @TotalPrice as Decimal(38,10)
		DECLARE @NetPrice 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 @ID, @FormType, @TheWarehouseID, @TheWarehouseNo, @InOrOut, @WarehouseNumber, @Used, @TotalPrice, @NetPrice, @OrderTotalID, @OrderControlID, @OrderDetailID1, @OrderDetailID2, @Date
		SET @OldOrderTotalID=@OrderTotalID
		SET @OldOrderControlID=@OrderControlID
		SET @OldOrderDetailID1=@OrderDetailID1
		SET @OldOrderDetailID2=@OrderDetailID2
		if @DEBUG > 0 Print Str(@OldOrderTotalID)+Str(@OLDOrderControlID)+Str(@OLDOrderDetailID1)+Str(@OldOrderDetailID2)
		if @DEBUG > 0 Print '                   نوع     TheWarehouse   Number  TotalPrice, NetPrice   @Used   @SumPrice   @SumNum   Mean! @TSumPrice,   @TSumNum'
		WHILE @@FETCH_STATUS = 0 
		BEGIN
		if @DEBUG > 0 Print 'Processing.... '+Str(@FormType) + '-' + Str(@TheWarehouseID)   + '-' + Str(@TheWarehouseNo)  + '-' + @InOrOut  + '-' + Str(@WarehouseNumber)  + '-' + Str(@Used)  + '-' + Str(@TotalPrice) + '-' + Str(@NetPrice)  + '-' + Str(@OrderTotalID)  + '-' + Str(@OrderControlID)  + '-' + Str(@OrderDetailID1)  + '-' + Str(@OrderDetailID2)  
			--if @TheWarehouseID=@WarehouseID
			--begin
			--	if @DEBUG > 0 Print '***** BREAK: @TheWarehouseID=' + Str(@TheWarehouseID)
			--	break
			--end
			--if @DEBUG > 0 Print '***>>>>>'+Str(@TotalPrice)+Str(@NetPrice)+Str(@Used)+Str(@OrderTotalID)+Str(@OrderControlID)+Str(@OrderDetailID1)+Str(@OrderDetailID2)
--if @DEBUG > 0 Print  '***** @TheWarehouseID=' + Str(@TheWarehouseID)
	if @TotalPrice <= 1 
		set @TotalPrice=0
	
--					if @DEBUG > 0 Print '11b@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,18)  + Str(@NetPrice,12) + Str(@Used,12) + str(@SumPrice,12) + Str(@SumNum,12)
			--if @FormType in (0,2,13,4,6,7,9,120,121)
--          CASE Viw.InOROut WHEN 0 THEN N'رسيد' WHEN 1 THEN N'حواله' WHEN 2 THEN N'اول دوره' WHEN 3 THEN N'ب خريد' WHEN 4 THEN N'ب مصرف' WHEN 5 THEN N'اماني ما' WHEN
--           6 THEN N'ب اماني ما' WHEN 7 THEN N'اماني ديگران' WHEN 8 THEN N'ب اماني ديگران' WHEN 9 THEN N'توليد' WHEN 10 THEN N'ب توليد' WHEN 11 THEN N'حواله فروش' WHEN 12 THEN
--           N'انتقالی از' WHEN 12 THEN N'انتقالی به' WHEN 121 THEN N'ب فروش' ELSE N'نامشخص' END AS InOrOut, Viw.Number, Viw.UserNumber, Viw.Date, dbo.FARSI_DATE2(Viw.Date) AS DatePer, 
if @DEBUG > 0 Print @InOrOut + '@TotalPrice:'+Str(@TotalPrice) +'@TheWarehouseID:'+ Str(@TheWarehouseID)
			if @InOrOut in (N'رسيد', N'اول دوره', N'ب مصرف', N'ب اماني ما', N'اماني ديگران', N'توليد', N'انتقالی به', N'ب فروش')
			begin
				set @SumPrice=@SumPrice+@TotalPrice
				set @SumNum=@SumNum+@Used
				if @DEBUG > 0 Print '#### In '+str(@SumPrice,24,5)+Str(@SumNum,24,5)
			end
			else 
			begin
				if @DEBUG > 0 Print '#### Out '+str(@SumPrice,24,5)+Str(@SumNum,24,5)
				if @SumNum <> 0 
					SET @Mean=@SumPrice/@SumNum
				else
					SET @Mean=0

				SET @NetPrice=ROUND(@NetPrice,@Decimal) 
				SET @Mean=ROUND(@Mean,@Decimal)
				if ROUND(@NetPrice,@Decimal-1)  <> ROUND(@Mean,@Decimal-1)
				BEGIN
					update AccWarehouseDetails SET NetPrice=@Mean, TotalPrice=Request*@Mean where ID=@ID 
					if @DEBUG > 0 Print '**** NetPrice Changed! @ID='+str(@ID)+' From:' +Str(@NetPrice,18,5)+' TO:'+Str(@Mean,18,5)
					
					SET @TotalPrice=@TotalPrice - @NetPrice*@Used
					if @TotalPrice < 1 
						SET @TotalPrice=0
					if @TotalPrice <> 0 and @DEBUG>0 print '**>>@TotalPrice=' + Str(@TotalPrice)

					SET @TotalPrice = @TotalPrice + @Mean*@Used
					SET @ChangeCount=@ChangeCount+1
				END


				set @SumPrice=@SumPrice-@TotalPrice
				set @SumNum=@SumNum-@Used
					if @DEBUG > 0 Print '#### 2 '+str(@SumPrice)+Str(@SumNum)+Str(@TotalPrice)
			END
		--	if @DEBUG > 0 Print ' @SumPrice='+str(@SumPrice)+'    @SumNum='+Str(@SumNum)
			--if @SumNum<>0 
			--	if @TSumNum <> 0 
			--		if @DEBUG > 0 Print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Used,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(@SumPrice/@SumNum,12) + Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(@TSumPrice/@TSumNum, 12)
			--	else
			--		if @DEBUG > 0 Print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Used,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(@SumPrice/@SumNum,12) + Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(0, 12)
			--else
			--	if @TSumNum <> 0 
			--		if @DEBUG > 0 Print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Used,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(0,12)+ Str(@TSumPrice,12) + STR(@TSumNum, 12)+ Str(@TSumPrice/@TSumNum, 12)
			--	else
			--		if @DEBUG > 0 Print '11@FormType' + Str(@FormType,12) + Str(@TheWarehouseNo,12) + Str(@WarehouseNumber,12) + Str(@TotalPrice,12) + Str(@Used,12) + str(@SumPrice,12) + Str(@SumNum,12)+str(0,12)+ Str(@TSumPrice,12) + STR(@TSumNum, 12) + Str(0, 12)

		FETCH NEXT FROM MyCursor INTO @ID, @FormType, @TheWarehouseID, @TheWarehouseNo, @InOrOut, @WarehouseNumber, @Used, @TotalPrice, @NetPrice, @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

			if @DEBUG > 0 Print '3##########################'+Str(@Mean,12)+Str(@SumNum,12)+str(@SumPrice,12)+Str(@SumNum,12)


				--if @Mean=0 and @SumNum>0
				--begin
				--	--if @DEBUG > 0 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)
				--	if @DEBUG > 0 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
				--	if @DEBUG > 0 Print '>>>>>>>>>>>>@Mean is set to:' + Str(@Mean,30,3)
				--END	
				--if @SumNum < 0
				--begin
				--	set @Mean=0
				--end
	
			--if @DEBUG > 0 Print '4#### 4 '+Str(@Mean,12)+Str(@SumNum,12)+str(@SumPrice,12)
	
		if @DEBUG > 0 Print '5@SumNum='+Str(@SumNum)+ '		@SumPrice=' + Str(@SumPrice,12) + '		@SumNum=' + STR(@SumNum,12)
		if @DEBUG > 0 Print '6@SumNum='+Str(@SumNum)+ '		@TSumPrice=' + Str(@TSumPrice,12) + '		@TSumNum=' + STR(@TSumNum,12)
		if @DEBUG > 0 Print '***********************************************'
		if @DEBUG > 0 Print '7  '+Str(@OldOrderTotalID)+Str(@OLDOrderControlID)+Str(@OLDOrderDetailID1)+Str(@OldOrderDetailID2)
				
				SET @TSumPrice = @TSumPrice + @Mean*@SumNum
				SET @TSumNum = @TSumNum + @SumNum

		--if @DEBUG > 0 Print '@Mean='+Str(@Mean)+ '		@SumNum=' + Str(@SumNum)

		if @DEBUG > 0 Print '8@SumNum='+Str(@SumNum)+ '		@SumPrice=' + Str(@SumPrice,12) + '		@SumNum=' + STR(@SumNum)
		if @DEBUG > 0 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
		--	--if @DEBUG > 0 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)
		--	--if @DEBUG > 0 Print '@Mean is set to:' + Str(@Mean,30,3)
		--end
		if @DEBUG > 0 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
		if @DEBUG > 0 Print 'b##########################'+Str(@Mean)+Str(@SumNum)+str(@SumPrice)+Str(@SumNum)
		if @DEBUG > 0 Print 'cEND @SumNum='+Str(@SumNum)+ '	@TSumPrice=' + Str(@TSumPrice,12) + '	@TSumNum=' + STR(@TSumNum, 12)

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

		--if @DEBUG > 0 Print '@Mean='+Str(@Mean)

		--if @Mean=0 and @SumNum>0
		--begin
		--	--if @DEBUG > 0 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)
		--	--if @DEBUG > 0 Print '@Mean is set to:' + Str(@Mean,30,3)
		--end

		if @DEBUG > 0 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,4,6,7,9,120,121)
	--				Order by [Date] desc, RequestIn asc
	--
	if @DEBUG > 0 Print 'e@SumNum='+Str(@SumNum)+ '		@TSumPrice=' + Str(@TSumPrice,12) + '		@TSumNum=' + STR(@TSumNum)


	if @DEBUG > 0 Print 'End! @ChangeCount='+Str(@ChangeCount)

	Select @ChangeCount as ChangeCount
	RETURN @ChangeCount