SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--------------------------------------------------------------
-- Author:           Zach
-- Create date: 20170706

-- Edit info:         
-- Description:       data for order
-- Editor: Tom 2019/5/5
-- Description: add uk data
-- Editor: Tom 2020/1/16
-- Description: Retail清洗BusinessType为Retail/Dealer Small Package/Customer Service/Sync Dealer/Dealer Wholesale/ECom Wholesale/Ecom Consignment的数据
--Tom 02/01/2024 Desc:Hard Code Desinc to BusinessPartner
------------------------------------------------------------------      
ALTER PROC [dbo].[RPT_SP_ECOMOrderReport] (@begin DATETIME,@end DATETIME)
AS

DELETE RPT_ECOMOrderReport WHERE DateID>=@begin AND DateID < @end

DECLARE @Macola001 VARCHAR(100),@Macola900 VARCHAR(100),@sql VARCHAR(max)
SET @Macola001=Warehouse.dbo.fn_GetMacolaDBByBusinessUnit(1);
SET @Macola900=Warehouse.dbo.fn_GetMacolaDBByBusinessUnit(7);

SET @sql='
INSERT INTO RPT_ECOMOrderReport
SELECT CAST(f.CreateDate AS DATE),e.ItemNo,f.Customer,SUM(QuantityOrdered),SUM(QuantityOrdered* case when g.BusinessPartner=''2'' then itemunitretailprice+e.RetailDiscountAmount/(CASE WHEN e.QuantityOrdered>0 THEN e.QuantityOrdered ELSE 1 END)  else ItemUnitSellPrice end),f.BusinessUnit_ID,BusinessType_ID,
sum(ItemCustStandardPrice*QuantityOrdered)/IIF(SUM(QuantityOrdered)=0,NULL,SUM(QuantityOrdered))ItemCustStandardPrice
FROM  Warehouse.dbo.WH_OrderLineItems e WITH(nolock)
INNER JOIN Warehouse.dbo.WH_OrderHeader f WITH(NOlock) ON e.Order_ID = f.OrderID
LEFT JOIN EECSystem.dbo.tblCustomers g WITH(NOlock) ON f.Customer=g.CustomerCode AND f.BusinessUnit_ID=g.BusinessUnit_ID
WHERE f.CreateDate>='''+cast(@begin as varchar)+''' AND CreateDate < '''+cast(@end as varchar)+''' AND (f.Customer IN (''AMAZON'',''AMAZONPT'') OR  BusinessType_ID in (1,3,5,6,7,11,12)
)AND e.LineItemStatus<>2 AND e.ItemNo IS NOT null
GROUP BY CAST(f.CreateDate AS DATE),ItemNo,f.Customer,f.BusinessUnit_ID,BusinessType_ID
UNION ALL

SELECT dateid,itemno,CustomerGroup,SUM(OrderQty),SUM(SalesAmount),BusinessUnit_ID,BusinessType_ID,MAX(ItemCustStandardPrice)
FROM (
SELECT DISTINCT DateID,ItemNo,CustomerGroup,OrderQty,SalesAmount,BusinessUnit_ID ,MacolaOrderNo,MacolaOrderLineNo,BusinessType_ID,ItemCustStandardPrice
FROM dbo.FCT_MacolaItemShipment WHERE DateID>='''+cast(@begin as varchar)+''' AND DateID < '''+cast(@end as varchar)+'''
UNION ALL
SELECT   cast(entered_dt AS DATE) AS entered_dt,
       
       ltrim( rtrim(item_no)) ,
		
        CASE  WHEN ewc.DisplayName IS NOT NULL THEN ewc.DisplayName
            ELSE ltrim( rtrim(Hdr.cus_no))
        END   ,

       ( qty_ordered * uom_ratio )AS Qty_Shipped ,
        (qty_ordered * uom_ratio * unit_price * ( 1 - Itm.discount_pct
                                                    / 100 ) )AS ItemRevenue ,
        BusinessUnit_ID=1,Hdr.ord_no,Itm.line_seq_no,7,0
FROM    '+@Macola001+'.dbo.oeordhdr_sql Hdr ( NOLOCK )
        INNER JOIN '+@Macola001+'.dbo.oeordlin_sql Itm ( NOLOCK ) ON Hdr.ord_no = Itm.ord_no
		inner JOIN dbo.Sys_EcomWholeCustomer ewc ON hdr.cus_no=ewc.CustomerCode 
WHERE   Hdr.ord_type = ''O''
        AND entered_dt>='''+cast(@begin as varchar)+''' AND Hdr.entered_dt < '''+cast(@end as varchar)+'''
        AND item_no NOT LIKE ''**%''
UNION all
SELECT  cast(entered_dt AS DATE) AS entered_dt,
       
       ltrim( rtrim(item_no)) ,
		
       ltrim( rtrim(Hdr.cus_no)),

       ( qty_ordered * uom_ratio )AS Qty_Shipped ,
        (qty_ordered * uom_ratio * unit_price * ( 1 - Itm.discount_pct
                                                    / 100 ) )AS ItemRevenue ,
        BusinessUnit_ID=7,Hdr.ord_no,Itm.line_seq_no,7,0
FROM    '+@Macola900+'.dbo.oeordhdr_sql Hdr ( NOLOCK )
        INNER JOIN '+@Macola900+'.dbo.oeordlin_sql Itm ( NOLOCK ) ON Hdr.ord_no = Itm.ord_no
WHERE   Hdr.ord_type = ''O''
        AND entered_dt>='''+cast(@begin as varchar)+''' AND Hdr.entered_dt < '''+cast(@end as varchar)+'''
        AND item_no NOT LIKE ''**%''
) a
GROUP BY dateid,ItemNo,CustomerGroup,BusinessUnit_ID,BusinessType_ID'

EXEC (@sql)

GO