CREATE PROCEDURE [dbo].[Sp_OnlineProductTracking] @OrderNumber BIGINT , @RefreshId BIGINT
AS 

DECLARE @FactorId BIGINT 
DECLARE @Factor AS TABLE
(
   FactorId BIGINT DEFAULT(0), 
   NuOrder INT,
   OrderDate VARCHAR(10),
   OrderTime VARCHAR(10),
   Description NVARCHAR(max),
   Family NVARCHAR(60),
   Mobile VARCHAR(11),
   Address NVARCHAR(max),
   DeliveryDate nVARCHAR(200)
)

INSERT INTO @Factor(NuOrder,OrderDate,OrderTime,Description,Mobile,Address,Family,DeliveryDate)
SELECT  NuOrder, dbo.MiladiToShamsi(OrderDate), CONVERT(VARCHAR(10), OrderDate, 108),Description, Mobile, Address, Family, DeliveryDate FROM dbo.TrackingHeader WITH (nolock) 
WHERE NuOrder = @OrderNumber


SET @FactorId = (SELECT Top 1 Id_Havaleh FROM TmpHeadh WHERE Nu_Havaleh = @OrderNumber AND Kind_Flag = 7)
UPDATE @Factor SET FactorId = @FactorId
SELECT * FROM @Factor

SELECT Autoid Id, Radif Row, Sender WarehouseCode ,Kalaid.K_code GoodsCode, Name1 GoodsName, LocateKala, K_Qty1 Qty, TmpHavaleh.Price_Forosh SalePrice, K_Qty1 * TmpHavaleh.Price_Forosh TotSalePrice, 
EditQty_Flag Status FROM TmpHavaleh WITH (nolock) 
INNER JOIN dbo.KalaId WITH (nolock)  ON KalaId.K_Code = TmpHavaleh.K_Code
INNER JOIN anbar WITH (nolock)  ON anbar.A_Code = Sender AND Anbar.K_Code = Kalaid.K_Code
WHERE Id_Havaleh =  CASE
                      WHEN @RefreshId = 0 THEN @FactorId
					  ELSE @RefreshId
					END
ORDER BY AutoId

IF @RefreshId = 0
BEGIN 
	SELECT Over_Under.Name, 
	CASE
	  WHEN Over_Under.Type = 0 THEN N'کاهشي'
	  WHEN Over_Under.Type = 1 THEN N'افزايشي'
	END AS Status,price  FROM Tmp_Over_Under_Factor WITH (nolock) 
	INNER JOIN dbo.Over_Under WITH (nolock)  ON Over_Under.AutoId = Tmp_Over_Under_Factor.Code_Over_Under
	WHERE Id_Havaleh = @FactorId
	AND Price <> 0
	ORDER BY Over_Under.Type
END
ELSE
	SELECT Over_Under.Name, 
	CASE
	  WHEN Over_Under.Type = 0 THEN N'کاهشي'
	  WHEN Over_Under.Type = 1 THEN N'افزايشي'
	END AS Status,price  FROM Tmp_Over_Under_Factor WITH (nolock) 
	INNER JOIN dbo.Over_Under WITH (nolock)  ON Over_Under.AutoId = Tmp_Over_Under_Factor.Code_Over_Under
	WHERE Id_Havaleh = @RefreshId
	AND Price <> 0
	ORDER BY Over_Under.Type

SELECT TrackingDetail.Id, Status,
CASE
  WHEN Status = 0 THEN N'در صف بررسي'
  WHEN Status = 1 THEN N'در حال بسته بندي'
  WHEN Status = 2 THEN N'آماده ارسال'
  WHEN Status = 3 THEN N'ارسال به مشتري'
  WHEN Status = 4 THEN N'تحويل به مشتري'
  WHEN Status = 8 THEN N'لغو توسط مشتري'
  WHEN Status = 9 THEN N'لغو توسط فروشگاه'
END AS StatusName, Dbo.MiladiToShamsi(dbo.TrackingDetail.TrackingDate) FactorDate, 
CONVERT(VARCHAR(10), TrackingDate,108) FactorTime, IsDone 
 FROM dbo.TrackingHeader WITH (nolock) 
INNER JOIN dbo.TrackingDetail WITH (nolock)  ON TrackingDetail.TrackingHeaderId = TrackingHeader.Id
WHERE NuOrder = @OrderNumber
ORDER BY Status
