SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--------------------------------------------------------------
-- Author:           Zach
-- Create date: 20170612

-- Edit info:         zach-20170619
-- Description:      Fact data from order to shipment
-- Editor: Tom 2018/11/6
-- Description: add packCode=3 for kit item

-- 更改consignment的订单的macolashipdate的取值，从原来的 MacolaShippedDate更改到SystemMacolaShipDate  Lee  2019-2-1
-- Editor: Bale 2019/03/29
-- Description: add ItemCustStandardPrice
-- Editor: Tom 2019/11/25
-- Description: Pkit item在计算QtyPacked时要根据下单的item计算
-- Editor: Tom 2020/11/12
-- Description: Consignment订单是不保存macolalineno的需要根据OrderLineNo分组
------------------------------------------------------------------      
ALTER PROC [dbo].[FCT_SP_ECOMItemShipment] (@begin DATETIME,@end DATETIME)
AS
DELETE dbo.FCT_ECOMItemShipment WHERE DateID>=@begin AND DateID < @end

;WITH kititem AS(

SELECT * FROM (
SELECT g.Item_ID,g.ItemNo,h.BusinessUnit_ID,g.QtyPerPar,g.Comp_ItemNo,rn=ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY g.Comp_ItemNo)
 FROM eecsystem.dbo.tblBOMProductStructure g 
INNER JOIN eecsystem.dbo.tblItemMaster h ON g.Comp_ItemID = h.ItemID
INNER JOIN  eecsystem.dbo.tblItemMaster k ON g.Item_ID=k.ItemID AND k.PackCode=3
) a WHERE rn = 1				
)

SELECT CAST(a.ASNSentDate AS date)  AS DateID ,
        ItemNo=max(e.itemno),
        CustomerCode=MAX(a.CustomerCode ),
        OrderNo=a.SalesOrderNo, 
        e.Order_ID ,
        a.MacolaOrderNo ,
        c.Line_No AS MacolaOrderLineNo ,
        'DROPSHIP' AS ShipmentType ,
        QuantityOrdered=MAX(e.QuantityOrdered ),
        ItemUnitSellPrice=MAX(e.ItemUnitSellPrice) ,
        a.BusinessUnit_ID ,
        QtyPacked =SUM(CASE WHEN e.ItemPackCode=3 AND c.ItemNo=e.ItemNo THEN  c.QtyPacked*1.0 / ISNULL(g.QtyPerPar,1) ELSE c.QtyPacked END ) ,
        a.ShipmentNo,
		ASNSentDate=MAX(a.ASNSentDate),
		e.Loc,
		HandingFee=MAX(e.HandingFee),
		ShippingAmount=MAX(e.ShippingAmount),
		OrderCreateDate = MAX(f.CreateDate),
		Item_ID=0,
		DivisionCode=REPLICATE(' ',10),
		productcategorycode=REPLICATE(' ',10),
		DiscountAmount=MAX(e.DiscountAmount),
		ItemUnitRetailPrice = MAX(ItemUnitRetailPrice),
		OrderStatus=MAX(f.Status),
		DealerID=MAX(f.DealerID),BusinessType_ID=MAX(f.BusinessType_ID),
		RetailDiscountAmount=max(CASE WHEN e.QuantityOrdered-e.QuantityCancelled >0 THEN e.RetailDiscountAmount/(e.QuantityOrdered-e.QuantityCancelled)
									ELSE NULL end) * SUM(CASE WHEN e.ItemPackCode=3 AND c.ItemNo=e.ItemNo THEN  c.QtyPacked * 1.0 / ISNULL(g.QtyPerPar,1) ELSE c.QtyPacked END ),
		RetailHeaderDiscountPercentage=max(RetailHeaderDiscountPercentage),MAX(RTRIM(a.ShipToState)) AS ShipToState
		,ShippingMethod_ID=MAX(a.ShippingMethod_ID)
		,(CAST(b.PackID AS NVARCHAR(100))) AS CartonID
		,dealername = MAX(f.DealerCompanyName)
		,MacolaCustomerCode=MAX(f.MacolaCustomerCode)
		,CommissionRate=MAX(CommissionRate)
		,RetailShippingAmount=MAX(RetailShippingAmount) 
		,NumericField1=MAX(NumericField1)
		,OrderTotalAmountCharged=MAX(OrderTotalAmountCharged)
		,PaymentMethod=MAX(PaymentMethod),OlliixSalesPersonName=MAX(h.OlliixSalesPersonName),OlliixSalesPersonID = MAX(OlliixSalesPersonID)
		,OrderDiscountAmount=MAX(h.DiscountAmount),OrderCouponAmount=MAX(h.CouponAmount),OrderGiftCardAmount=MAX(i.GiftCardAmount),OrderRewardAmount=MAX(i.RewardAmount),MAX(ItemCustStandardPrice)ItemCustStandardPrice
		INTO #t
FROM    Warehouse.dbo.WH_ShipmentHeader a WITH(NOLOCK)
        INNER JOIN Warehouse.dbo.WH_OrderPack b WITH(NOLOCK) ON a.ShipmentID = b.Shipment_ID
        INNER JOIN Warehouse.dbo.WH_PackContents c WITH(NOLOCK) ON b.PackID = c.Pack_ID
        INNER JOIN Warehouse.dbo.WH_OrderLineItems e WITH(NOLOCK) ON a.MacolaOrderNo = e.MacolaOrderNo AND e.MacolaLineNo = c.Line_No AND (CASE WHEN e.ItemPackCode=3 THEN e.ItemNo ELSE c.itemno end)=e.itemno
		INNER JOIN Warehouse.dbo.WH_OrderHeader f WITH(NOlock) ON e.Order_ID = f.OrderID
		LEFT  JOIN kititem g  ON e.BusinessUnit_ID=g.BusinessUnit_ID AND c.ItemNo=g.Comp_ItemNo
		LEFT JOIN warehouse.dbo.WH_OrderLineItemsAdditionalInfo h ON h.Order_ID = f.OrderID AND e.OrderLineNo=h.OrderLineNo
		LEFT JOIN warehouse.dbo.WH_OrderHeaderAdditionalInfo i ON f.OrderID=i.Order_ID
WHERE   a.ASNSentDate >=@begin 
        AND a.ASNSentDate <  @end AND (g.item_id IS NOT NULL OR e.itempackcode <>3)
		AND NOT EXISTS (SELECT 1 FROM dbo.FCT_ECOMItemShipment h WHERE a.ShipmentNo = h.ShipmentNo AND  a.MacolaOrderNo = h.MacolaOrderNo AND c.Line_No = h.MacolaOrderLineNo)
GROUP BY CAST(a.ASNSentDate AS date),  a.MacolaOrderNo ,c.Line_No ,a.BusinessUnit_ID ,a.ShipmentNo,e.Order_ID,SalesOrderNo,e.Loc,CAST(b.PackID AS NVARCHAR(100)) 
UNION all
SELECT  CAST(a.ASNEmailSentDate AS date)  AS DateID ,
        ItemNo=MAX(c.ItemNo) ,
        CustomerCode=MAX(a.CustomerCode ),
        e.OrderNo ,
        e.Order_ID ,
        c.MacolaOrderNo ,
        c.MacolaLineno ,
        'WHLSE' AS ShipmentType ,
        QuantityOrdered=MAX(e.QuantityOrdered) ,
        ItemUnitSellPrice =MAX(e.ItemUnitSellPrice) ,
        c.BusinessUnit_ID ,
        QtyPacked=SUM(c.QtyPerPack ),
        a.ShipmentNo,
		MAX(a.ASNEmailSentDate),
		a.Location,
		HandingFee=MAX(e.HandingFee),
		ShippingAmount=MAX(e.ShippingAmount),
		OrderCreateDate = MAX(f.CreateDate),
		Item_ID=0,
		DivisionCode='',
		productcategorycode='',
		DiscountAmount=MAX(e.DiscountAmount),
		ItemUnitRetailPrice=MAX(ItemUnitRetailPrice),
		OrderStatus=MAX(f.Status),
		DealerID=MAX(f.DealerID),BusinessType_ID=MAX(f.BusinessType_ID),
		RetailDiscountAmount=max(CASE WHEN e.QuantityOrdered-e.QuantityCancelled >0 THEN e.RetailDiscountAmount/(e.QuantityOrdered-e.QuantityCancelled)
									ELSE NULL end) * SUM(c.QtyPerPack ),
		RetailHeaderDiscountPercentage=max(RetailHeaderDiscountPercentage),MAX(RTRIM(a.ShipToState))
		,MAX(a.ShipVia)
		,CAST(c.CartonID AS NVARCHAR(100)) AS CartonID
		,dealername = MAX(f.DealerCompanyName)
		,MacolaCustomerCode=MAX(f.MacolaCustomerCode)
		,CommissionRate=MAX(CommissionRate)
		,RetailShippingAmount=MAX(RetailShippingAmount) 
		,NumericField1=MAX(NumericField1)
		,OrderTotalAmountCharged=MAX(OrderTotalAmountCharged)
		,PaymentMethod=MAX(PaymentMethod),OlliixSalesPersonName=MAX(h.OlliixSalesPersonName),OlliixSalesPersonID = MAX(OlliixSalesPersonID)
		,OrderDiscountAmount=MAX(h.DiscountAmount),OrderCouponAmount=MAX(h.CouponAmount),OrderGiftCardAmount=MAX(i.GiftCardAmount),OrderRewardAmount=MAX(i.RewardAmount),MAX(ItemCustStandardPrice)ItemCustStandardPrice
FROM    Warehouse.dbo.Whlse_Shipment a WITH(NOLOCK)
        INNER JOIN Warehouse.dbo.Whlse_ShipmentCarton b WITH(NOLOCK) ON b.Shipment_ID = a.ShipmentID
        INNER JOIN Warehouse.dbo.Whlse_Carton c WITH(NOLOCK) ON c.CartonID = b.Carton_ID
        INNER JOIN Warehouse.dbo.WH_OrderLineItems e WITH(NOLOCK) ON c.MacolaOrderNo = e.MacolaOrderNo
                                                        AND e.MacolaLineNo = c.MacolaLineno
		INNER JOIN Warehouse.dbo.WH_OrderHeader f WITH(NOlock) ON e.Order_ID = f.OrderID
		LEFT JOIN warehouse.dbo.WH_OrderLineItemsAdditionalInfo h ON h.Order_ID = f.OrderID AND e.OrderLineNo=h.OrderLineNo
		LEFT JOIN warehouse.dbo.WH_OrderHeaderAdditionalInfo i ON f.OrderID=i.Order_ID
WHERE   a.ASNEmailSentDate >=@begin 
        AND a.ASNEmailSentDate <  @end 
       -- AND c.CustomerCode IN ( 'OLLIIX', 'DESINC' )
	   AND a.BusinessType_ID IN (7,11)
		AND NOT EXISTS (SELECT 1 FROM dbo.FCT_ECOMItemShipment h WHERE a.ShipmentNo = h.ShipmentNo AND  c.MacolaOrderNo = h.MacolaOrderNo AND c.MacolaLineno = h.MacolaOrderLineNo)
GROUP BY CAST(a.ASNEmailSentDate AS date) ,c.MacolaOrderNo,c.MacolaLineno,c.BusinessUnit_ID,a.ShipmentNo,e.Order_ID,e.OrderNo,a.Location
	,CAST(c.CartonID AS NVARCHAR(100))
UNION all
SELECT   CAST(a.ASNSentDate AS date)  AS DateID ,
        ItemNo=MAX(c.ItemNo) ,
        CustomerCode=MAX(a.CustomerCode ),
        e.OrderNo ,
        e.Order_ID ,
        c.MacolaOrderNo ,
        c.MacolaLineno ,
        'WHLSE' AS ShipmentType ,
        QuantityOrdered=MAX(e.QuantityOrdered) ,
        ItemUnitSellPrice =MAX(e.ItemUnitSellPrice) ,
        c.BusinessUnit_ID ,
        QtyPacked=SUM(c.QtyPerPack ),
        a.ShipmentNo,
		MAX(a.ASNSentDate),
		a.Location,
		HandingFee=MAX(e.HandingFee),
		ShippingAmount=MAX(e.ShippingAmount),
		OrderCreateDate = MAX(f.CreateDate),
		Item_ID=0,
		DivisionCode='',
		productcategorycode='',
		DiscountAmount=MAX(e.DiscountAmount),
		ItemUnitRetailPrice=MAX(ItemUnitRetailPrice),
		OrderStatus=MAX(f.Status),
		DealerID=MAX(f.DealerID)
		,BusinessType_ID=MAX(f.BusinessType_ID),
		RetailDiscountAmount=max(e.RetailDiscountAmount),
		RetailHeaderDiscountPercentage=max(RetailHeaderDiscountPercentage)
		,MAX(RTRIM(a.ShipToState))
		,MAX(a.ShipVia)
		,CAST(c.CartonID AS NVARCHAR(100)) AS CartonID
		,dealername = MAX(f.DealerCompanyName)
		,MacolaCustomerCode=MAX(f.MacolaCustomerCode)
		,CommissionRate=MAX(CommissionRate)
		,RetailShippingAmount=MAX(RetailShippingAmount) 
		,NumericField1=MAX(NumericField1)
		,OrderTotalAmountCharged=MAX(OrderTotalAmountCharged)
		,PaymentMethod=MAX(PaymentMethod),OlliixSalesPersonName=MAX(h.OlliixSalesPersonName),OlliixSalesPersonID = MAX(OlliixSalesPersonID)
		,OrderDiscountAmount=MAX(h.DiscountAmount),OrderCouponAmount=MAX(h.CouponAmount),OrderGiftCardAmount=MAX(i.GiftCardAmount),OrderRewardAmount=MAX(i.RewardAmount),MAX(ItemCustStandardPrice)ItemCustStandardPrice
FROM    Warehouse.dbo.Whlse_Shipment a WITH(NOLOCK)
        INNER JOIN Warehouse.dbo.Whlse_ShipmentCarton b WITH(NOLOCK) ON b.Shipment_ID = a.ShipmentID
        INNER JOIN Warehouse.dbo.Whlse_Carton c WITH(NOLOCK) ON c.CartonID = b.Carton_ID
        INNER JOIN Warehouse.dbo.WH_OrderLineItems e WITH(NOLOCK) ON c.MacolaOrderNo = e.MacolaOrderNo
                                                        AND e.MacolaLineNo = c.MacolaLineno
		INNER JOIN Warehouse.dbo.WH_OrderHeader f WITH(NOlock) ON e.Order_ID = f.OrderID
		LEFT JOIN warehouse.dbo.WH_OrderLineItemsAdditionalInfo h ON h.Order_ID = f.OrderID AND e.OrderLineNo=h.OrderLineNo
		LEFT JOIN warehouse.dbo.WH_OrderHeaderAdditionalInfo i ON f.OrderID=i.Order_ID
WHERE   a.ASNSentDate >=@begin 
        AND a.ASNSentDate <  @end 
        --AND 
		AND (a.BusinessType_ID IN (7,11) OR a.CustomerCode IN ( 'AMAZON','AMAZONPT' ))
		AND NOT EXISTS (SELECT 1 FROM dbo.FCT_ECOMItemShipment h WHERE a.ShipmentNo = h.ShipmentNo AND  c.MacolaOrderNo = h.MacolaOrderNo AND c.MacolaLineno = h.MacolaOrderLineNo)
GROUP BY CAST(a.ASNSentDate AS date) ,c.MacolaOrderNo,c.MacolaLineno,c.BusinessUnit_ID,a.ShipmentNo,e.Order_ID,e.OrderNo,a.Location,CAST(c.CartonID AS NVARCHAR(100))

UNION ALL
SELECT CAST(a.SystemMacolaShipDate AS date) AS dateid
	,ItemNo=MAX(c.ItemNo),Customer=MAX(d.Customer)
	,a.OrderNo
	,d.OrderID
	,a.MacolaOrderNo
	,OrderLineNo AS MacolaLineNo 
	,'Consignment' ShipmentType 
	,QuantityOrdered=MAX(e.QuantityOrdered)
	,ItemUnitSellPrice=MAX(e.ItemUnitSellPrice)
	,BusinessUnit=MAX(a.BusinessUnit)
	,QtyPacked=SUM(c.ShipedQty)
	,a.CMShipmentNo
	,ShippedDate=CAST(a.SystemMacolaShipDate AS date)
	,e.Loc
	,HandingFee=MAX(d.HandlingFee)
	,ShippingAmount=MAX(d.ShippingAmount)
	,OrderCreateDate=MAX(d.CreateDate)
	,Item_ID=0
	,DivisionCode=REPLICATE(' ',10)
	,productcategorycode=REPLICATE(' ',10)
	,DiscountAmount=MAX(e.DiscountAmount)
	,ItemUnitRetailPrice=MAX(e.ItemUnitRetailPrice)
	,OrderStatus=MAX(d.Status)
	,DealerID=MAX(d.DealerID)
	,BusinessType_ID=MAX(d.BusinessType_ID)
	,RetailDiscountAmount=max(CASE WHEN e.QuantityOrdered-e.QuantityCancelled >0 THEN e.RetailDiscountAmount/(e.QuantityOrdered-e.QuantityCancelled)
									ELSE NULL end) * SUM(c.ShipedQty)
	,RetailHeaderDiscountPercentage=max(RetailHeaderDiscountPercentage)
	,ShipToState=''
	,ShippingMethod_ID=0
	,CMCartonID=CAST(b.CMCartonID AS NVARCHAR(100))
	,DealerName=MAX(d.DealerCompanyName)
	,MacolaCustomerCode=MAX(d.MacolaCustomerCode)
	,CommissionRate=MAX(e.CommissionRate)
	,RetailShippingAmount=MAX(RetailShippingAmount) 
		,NumericField1=MAX(NumericField1)
		,OrderTotalAmountCharged=MAX(OrderTotalAmountCharged)
		,PaymentMethod=MAX(PaymentMethod),OlliixSalesPersonName='',OlliixSalesPersonID = NULL
		,OrderDiscountAmount=null,OrderCouponAmount=null,OrderGiftCardAmount=null,OrderRewardAmount=null,MAX(ItemCustStandardPrice)ItemCustStandardPrice
 FROM warehouse.dbo.CM_ShipmentHeader a
	inner join warehouse.dbo.CM_ShipmentCarton b ON a.CMShipmentID = b.CMShipmentID
	INNER join warehouse.dbo.CM_CartonItem c ON b.CMCartonID = c.CMCartonID
	INNER JOIN warehouse.dbo.WH_OrderHeader d ON a.OrderNo = d.OrderNo
	INNER JOIN warehouse.dbo.WH_OrderLineItems e ON e.OrderLineNo = c.ItemLineNo AND d.OrderID = e.Order_ID
WHERE a.SystemMacolaShipDate>=@begin AND a.SystemMacolaShipDate < @end
AND a.Uploaded=1
GROUP BY CAST(a.SystemMacolaShipDate AS date),a.MacolaOrderNo,e.OrderLineNo/*Consignment订单是不保存macolalineno的需要根据OrderLineNo分组*/,d.BusinessUnit_ID,a.CMShipmentNo,d.OrderID,a.OrderNo,e.Loc,CAST(b.CMCartonID AS NVARCHAR(100))

--UPDATE #t SET rn =ROW_NUMBER() over(partition BY dateid,MacolaOrderNo,MacolaOrderLineNo,BusinessUnit_ID,ShipmentNo,OrderNo ORDER BY dateid,MacolaOrderNo,MacolaOrderLineNo)

UPDATE a SET a.OrderCreateDate = b.CreateDate from #t a
	INNER JOIN warehouse.dbo.WH_OrderHeader b ON a.Order_ID = b.OrderID
--WHERE a.DateID = @begin


UPDATE a SET a.item_id = b.ItemID,a.DivisionCode=b.CommCode,a.productcategorycode=b.ProductCat  FROM #t a
	INNER JOIN EECSystem.dbo.tblItemMaster b ON a.ItemNo = b.ItemNo AND a.BusinessUnit_ID = b.BusinessUnit_ID
--WHERE a.DateID = @begin


;WITH  a AS (SELECT DISTINCT Order_ID FROM #t ) 
, b AS(	
	SELECT h.Order_ID,OrderDiscountAmount=sum(h.DiscountAmount),OrderCouponAmount=sum(h.CouponAmount)
	,OrderGiftCardAmount=max(i.GiftCardAmount),OrderRewardAmount=max(i.RewardAmount)
	FROM 
	 warehouse.dbo.WH_OrderLineItemsAdditionalInfo h 
		LEFT JOIN warehouse.dbo.WH_OrderHeaderAdditionalInfo i ON h.Order_ID=i.Order_ID
	WHERE h.Order_ID IN (SELECT h.Order_ID FROM a)
	GROUP BY h.Order_ID
	)


UPDATE a SET a.OrderDiscountAmount=b.OrderDiscountAmount,a.OrderCouponAmount=b.OrderCouponAmount
	,a.OrderGiftCardAmount=b.OrderGiftCardAmount,a.OrderRewardAmount=b.OrderRewardAmount
 FROM #t a
	INNER JOIN b ON a.order_id = b.Order_ID

	 


INSERT INTO dbo.FCT_ECOMItemShipment
        ( DateID ,
          ItemNo ,
          CustomerCode ,
          OrderNo ,
          Order_ID ,
          MacolaOrderNo ,
          MacolaOrderLineNo ,
          ShipmentType ,
          QuantityOrdered ,
          ItemUnitSellPrice ,
          BusinessUnit_ID ,
          QtyPacked ,
          ShipmentNo ,
          ASNSentDate ,
          OrderCreateDate ,
          Loc ,
          Item_ID ,
          DivisionCode ,
          ProductCategoryCode ,
          HandingFee ,
          ShippingAmount,DiscountAmount,ItemUnitRetailPrice,OrderStatus,DealerID,BusinessType_Id,RetailDiscountAmount,RetailHeaderDiscountPercentage,ShipToState
		  ,ShippingMethod_ID,CartonID,dealername,MacolaCustomerCode,CommissionRate,RetailShippingAmount ,NumericField1,OrderTotalAmountCharged,PaymentMethod,OlliixSalesPersonName,OlliixSalesPersonID
		  ,OrderDiscountAmount ,
       OrderCouponAmount ,
       OrderGiftCardAmount ,
       OrderRewardAmount ,
	   ItemCustStandardPrice
        )
SELECT DateID ,
          ItemNo ,
          CustomerCode ,
          OrderNo ,
          Order_ID ,
          MacolaOrderNo ,
          MacolaOrderLineNo ,
          ShipmentType ,
          QuantityOrdered ,
          ItemUnitSellPrice ,
          BusinessUnit_ID ,
          QtyPacked ,
          ShipmentNo ,
          ASNSentDate ,
          OrderCreateDate ,
          Loc ,
          Item_ID ,
          DivisionCode ,
          ProductCategoryCode ,
          HandingFee ,
          ShippingAmount
		  ,DiscountAmount
		  ,ItemUnitRetailPrice,OrderStatus,DealerID,BusinessType_Id,RetailDiscountAmount,RetailHeaderDiscountPercentage,ShipToState 
		  ,ShippingMethod_ID,CartonID,dealername,MacolaCustomerCode,CommissionRate,RetailShippingAmount ,NumericField1,OrderTotalAmountCharged,PaymentMethod,OlliixSalesPersonName,OlliixSalesPersonID
		  ,OrderDiscountAmount ,
       OrderCouponAmount ,
       OrderGiftCardAmount ,
       OrderRewardAmount ,ItemCustStandardPrice
		  FROM #t
GO