CREATE PROCEDURE [dbo].[FifoPricing] AS

UPDATE dbo.Havaleh SET Avg_Price = 0 

UPDATE Havaleh SET Avg_Price = Price_KHarid FROM dbo.Havaleh
INNER JOIN dbo.headH ON headh.Id_Havaleh = havaleh.Id_Havaleh
WHERE Kind_Flag IN(2,3,6,8,0,1)


-----------------------------------------(Create Virtual Factors)---------------------------------------------
DECLARE @Input AS TABLE
(
  DetailId BIGINT,
  Qty MONEY,
  MainQty MONEY,
  Price MONEY,
  Sender VARCHAR(5),
  Reciver VARCHAR(5)
)

DECLARE @Output AS TABLE
(
  DetailId BIGINT,
  Qty MONEY,
  Price MONEY,
  Sender VARCHAR(5),
  Kind CHAR(1)
)

-----------------------------------------------------(Start Fetch On Warehouse)----------------------------------------------
DECLARE 
@OutPutId BIGINT, 
@InputId BIGINT, 
@OutPutQty MONEY,
@InputQty MONEY,
@InputPrice MONEY,
@SumPrice MONEY = 0,
@CacheQty MONEY,
@OutSender VARCHAR(5),
@InReciver VARCHAR(5),
@OutKind CHAR(1),
 @GoodsCode VARCHAR(15),
 @Sender VARCHAR(5),
 @A_Code VARCHAR(5)

DECLARE WareHouseCursor CURSOR FOR 
SELECT K_Code FROM Anbar with (nolock)
WHERE Exit_Fa > 0 OR Resive_Fa > 0 -- جديد 980331 فقط براي گردش دارن
GROUP By K_Code

OPEN WareHouseCursor
FETCH NEXT FROM WareHouseCursor INTO @GoodsCode
WHILE @@FETCH_STATUS = 0 
BEGIN

---===================================
---------------قيمت برگشتي ها با اخرين خريد ان پر مي شود ---------------------------
--- جديد 980331 برگشتي به قيمت خريد نه فروش 
	DECLARE @Price MONEY
	DECLARE @CachePrice MONEY
	DECLARE @Kind TINYINT
	DECLARE @Autoid BIGINT 
	DECLARE CursorFactor CURSOR For
	SELECT AutoId, K_Code, Price_KHarid,kind_Flag,sender
	FROM headH with (nolock)
		INNER JOIN dbo.Havaleh with (nolock)
			ON Havaleh.Id_Havaleh = headH.Id_Havaleh
	WHERE K_Code = @GoodsCode 
   and Kind_flag IN(2,3,6,8,0,1)		
--	AND Kind_Flag IN(3,6,8,0,1)
	ORDER BY
                               --   reciver, -- NEW 991228
                                 Tarikh_faktor, CASE
			   WHEN Kind_Flag = 1 
			   AND Len(Sender) <>3 THEN 1
			   WHEN Kind_Flag = 2  THEN 2
			   WHEN Kind_Flag = 3  THEN 3
			   WHEN Kind_Flag = 6  THEN 4
			   WHEN Kind_Flag = 8  THEN 5
			   WHEN Kind_Flag = 0  THEN 6
			   WHEN Kind_Flag = 1  THEN 7
			   WHEN Kind_Flag = 4  THEN 8
			   WHEN Kind_Flag = 5  THEN 9
			   WHEN Kind_Flag = 7  THEN 10
			   WHEN Kind_Flag = 9  THEN 11
		   END, AutoId  
	OPEN CursorFactor
    SET @CachePrice = 0 ---- new 991228
	FETCH NEXT FROM CursorFactor INTO @Autoid, @GoodsCode, @Price, @Kind,@sender
	WHILE @@FETCH_STATUS = 0 
	BEGIN
	  IF (@Kind in(2 , 3) ) Or (@Kind = 1 AND Len(@Sender) <>3)
        SET @CachePrice = @Price
	  ELSE 
	    UPDATE Havaleh SET Avg_Price = @CachePrice WHERE AutoId = @Autoid AND @CachePrice > 0
	  FETCH NEXT FROM CursorFactor INTO @Autoid,  @GoodsCode, @Price, @Kind,@sender
    END
    CLOSE CursorFactor
	DEALLOCATE CursorFactor
---------------------------------------------------------------------------------

--====================================
    DELETE FROM @Input
    DELETE FROM @Output
	----------------------------------(Cache Data Input And OutPut Factors)-----------------------------------
	INSERT INTO @Input(DetailId,Qty,MainQty,Price,Sender,Reciver)
	SELECT AutoId, K_Qty1, K_Qty1, 
	--Price_KHarid
	Avg_price,Sender,Reciver
	FROM headH with (nolock)
		INNER JOIN dbo.Havaleh with (nolock)
			ON Havaleh.Id_Havaleh = headH.Id_Havaleh
	WHERE K_Code = @GoodsCode
	AND Kind_Flag IN(2,3,6,8,0,1)
	ORDER BY Tarikh_faktor, CASE
			   WHEN Kind_Flag = 1 
			   AND Len(Sender) <>3 THEN 1
			   WHEN Kind_Flag = 2  THEN 2
			   WHEN Kind_Flag = 3  THEN 3
			   WHEN Kind_Flag = 6  THEN 4
			   WHEN Kind_Flag = 8  THEN 5
			   WHEN Kind_Flag = 0  THEN 6
			   WHEN Kind_Flag = 1  THEN 7
			   WHEN Kind_Flag = 4  THEN 8
			   WHEN Kind_Flag = 5  THEN 9
			   WHEN Kind_Flag = 7  THEN 10
			   WHEN Kind_Flag = 9  THEN 11
		   END, AutoId 

	INSERT INTO @Output(DetailId,Qty,Price,Sender,Kind)
	SELECT AutoId, K_Qty1, Avg_Price,Sender, Kind_Flag
	FROM headH with (nolock)
		INNER JOIN dbo.Havaleh with (nolock)
			ON Havaleh.Id_Havaleh = headH.Id_Havaleh
	WHERE K_Code = @GoodsCode
	AND Kind_Flag IN(4,5,7,9,1)
	AND LEN(Sender) =3
	ORDER BY Tarikh_faktor,CASE
			   WHEN Kind_Flag = 2  THEN  1
			   WHEN Kind_Flag = 3  THEN  2
			   WHEN Kind_Flag = 6  THEN  3
			   WHEN Kind_Flag = 8  THEN  4
			   WHEN Kind_Flag = 0  THEN  5
			   WHEN Kind_Flag = 1 
			   AND LEN(Sender) = 3 THEN  6
			   WHEN Kind_Flag = 4  THEN  7
			   WHEN Kind_Flag = 5  THEN  8
			   WHEN Kind_Flag = 7  THEN  9
			   WHEN Kind_Flag = 9  THEN  10
		   END, AutoId 
	----------------------------------(Cache Data Input And OutPut Factors)-----------------------------------

    ----------------------------------(Start Perform pricing operations)--------------------------------------
	DECLARE CursorOutput CURSOR FOR
	SELECT DetailId, Qty, Sender, Kind  FROM @Output
	OPEN CursorOutput
	FETCH NEXT FROM CursorOutput INTO @OutPutId, @OutPutQty, @OutSender, @OutKind
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  SET @CacheQty = @OutPutQty
	  DECLARE CursorInput CURSOR FOR
	  SELECT DetailId, Qty, Price, Reciver FROM @Input
	  WHERE Qty <> 0 AND Reciver = @OutSender
	  OPEN CursorInput
	  FETCH NEXT FROM CursorInput INTO @InputId, @InputQty, @InputPrice,@InReciver
	  WHILE @@FETCH_STATUS = 0 
	  BEGIN
		IF @OutPutQty <= @InputQty
		BEGIN 
			UPDATE @Input SET Qty = @InputQty - @OutPutQty
			WHERE DetailId = @InputId
			IF @SumPrice > 0 
			BEGIN 
   			  SET @SumPrice = @SumPrice + (@OutPutQty * @InputPrice)
			  SET @InputPrice = @SumPrice
			  --IF @OutKind = 1  AND LEN(@InReciver) = 3
				--UPDATE @Input SET Price = @SumPrice/@CacheQty WHERE DetailId = @OutPutId 
			  SET @SumPrice = 0
           
			END 
			ELSE 
			  SET @CacheQty = 1
			UPDATE @Output SET Price = @InputPrice/@CacheQty WHERE DetailId = @OutPutId
            IF (@OutKind = 1  AND LEN(@InReciver) = 3)
              	UPDATE @Input SET Price = @InputPrice/@CacheQty WHERE DetailId = @OutPutId 
			Break
		END 
		ELSE
		BEGIN
			UPDATE @Input SET Qty = 
			CASE
			WHEN @InputQty - @OutPutQty  < 0 THEN 0 
			ELSE @OutPutQty - @InputQty 
			End
			WHERE DetailId = @InputId
			SET @OutPutQty = @OutPutQty - @InputQty
			SET @SumPrice = @SumPrice + (@InputQty * @InputPrice) 
		END
		FETCH NEXT FROM CursorInput INTO @InputId, @InputQty, @InputPrice,@InReciver
	  END
	  CLOSE CursorInput
	  DEALLOCATE CursorInput
	  SET @OutPutQty = 0
	  IF @SumPrice > 0 
	  Begin
		UPDATE @Output SET Price = @SumPrice/@CacheQty WHERE DetailId = @OutPutId
		SET @SumPrice = 0
	  End
	FETCH NEXT FROM CursorOutput INTO @OutPutId, @OutPutQty, @OutSender, @OutKind
	END
	CLOSE CursorOutput
	DEALLOCATE CursorOutput
	----------------------------(End Perform pricing operations)--------------------------------

	-----------------------------(Update Avg Price For WareHouse And Factors)--------------------
	UPDATE dbo.Havaleh SET Avg_Price = [@Output].Price FROM dbo.Havaleh
	INNER JOIN @Output ON [@Output].DetailId = AutoId


	UPDATE Anbar SET Avg_Price_Omdeh = 
	CASE
	  WHEN Mojodi > 0  THEN T.Differ/Mojodi ELSE 0 
	END
	  FROM dbo.Anbar with (nolock)
	INNER JOIN (
	SELECT T.ACode, ISNULL(SumInput,0) - ISNULL(SumOutPut,0) Differ FROM (
	SELECT Reciver ACode, SUM(MainQty*Price) SumInput FROM @Input
	GROUP BY Reciver
	) AS T
	LEFT OUTER JOIN
	(
	SELECT Sender ACode, SUM(ROUND(Qty*Price,0)) SumOutPut FROM @Output
	GROUP BY Sender
	) AS T1 ON T.ACode = T1.ACode
	) AS T ON T.ACode = Anbar.A_Code AND K_Code = @GoodsCode
   ----------------------------------------------------------------------------------
   FETCH NEXT FROM WareHouseCursor INTO @GoodsCode
END
CLOSE WareHouseCursor
DEALLOCATE WareHouseCursor
-----------------------------------------------------(End Fetch On Warehouse)----------------------------------------------
