客户订单导出：

测试界面：

备注：

 1、【 自定义报告-FBA备库订单签收上架时间统计】未开发，因为在正式环境未找到这个模板

2、配置的sql语句

SELECT p.ID AS 'OrderBy',
       'D' + CAST(p.ID AS VARCHAR) AS '包裹号',
       wh.WareHouseName 发货仓库,
       t.ChuKouYiType 运输方式代码,
       o.OrderCode 客户订单号,
       o.ClientOrderCode 平台订单号,
       pt.SKU AS '产品sku',
       pt.ClientSKU AS '自定义SKU',
       pt.ProductGroupSKU AS '母体SKU',
       t.TransportName 运输方式名称,
       p.TrackNumber 跟踪号,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE c.Country_en END 收件人国家英文名,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE ISNULL(o.FirstName, '') + ISNULL(o.LastName, '') END AS 收件人,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE o.CompanyName END 收件人公司,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.Address END 收货地址,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.City END 城市,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.Province END [收件人省/州],
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.PostCode END 邮编,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.Telephone END 收件人电话,
       os.VATNumber 税号,
       --fs.SiteName [发件人姓名(寄件联系人)] ,
       --fs.ClientAgreement [发件人公司(寄件单位)] ,
       --ISNULL(fs.SiteAddress1, '') + ISNULL(fs.SiteAddress2, '')
       --+ ISNULL(fs.SiteAddress3, '') AS [发货地址] ,
       --fs.SiteCity 发件人城市 ,
       --fs.SiteProvince [发件人省/州] ,
       --fs.SitePostCode 发件人邮编 ,
       --fs.SiteTelephone 发件人电话 ,
       CASE ISNULL(p.SecurityReturn, 0)
           WHEN 0 THEN
               '正常'
           WHEN 1 THEN
               '安检退回'
           ELSE
               ''
       END AS [是否退回],
       '' 保险类型,
       '' 保险价值RMB,
       '' 敏感货品,
       '' 申报类型,
       pd.ProductNum 包裹详情SKU数量,
       p.ProductNumberWithPackage 件数,
       p.PackageLength 包裹单边长,
       p.PackageHeight 包裹单边高,
       p.PackageWidth 包裹单边宽,
       p.PackageWeightDefault [包裹总重量(kg)],
       p.SystemWeight [系统总重量(kg)],
       pd.DeclarationName 申报名称,
       pd.DeclarationNameCN 申报中文名称,
       pd.DeclarationCode 海关编码,
       pd.ProductNum 包裹数量,
       pd.DeclarationPrice 报关单价,
       l.ProductPrice 订单产品单价,
       l.ShippingPrice 订单产品运费,
       l.ActivityMinusPrice 订单产品优惠金额,
       pd.SystemWeight 单位净重,
       '' 收件人完整地址,
       p.DeclarationPriceSum 申报总价,
       t.TransportNameEn 运输方式英文名称,
       p.ShippingFee 包裹运费,
       p.DeclarationPrice [包裹申报总价],
       pt.SalePrice 售价,
       --ol.ASIN ,
       o.TransportPay 客户支付运费,
       'D' + CAST(p.ID AS VARCHAR) + 'C' + CAST(p.CustomerID AS VARCHAR) AS 带商户号的包裹号,
       o.AddTime 下单时间,
       p.AddTime 包裹生成时间,
       p.DistributionTime 配货时间,
       p.PrintTime 打印时间,
       p.PickingTime 拣货时间,
       p.PackingTime 包装时间,
       p.CheckTime AS '复核时间',
       p.ShipTime AS '称重发货时间',
       p.CompleteTime AS '交付时间',
       p.GetTrackNumberTime 追踪号时间,
       p.TallyTime 理货时间,
       p.PrintAdminName AS '打单人员',
       p.PickingPersonName 拣货人员,
       p.PackagingPersonName 包装人员,
       p.CheckName 复核人员,
       p.WeighingPersonName 称重人员,
       dp.CompleteAdminName AS '交付人员',
       os.SaleAdminName AS '销售人员',
       p.TallyPersonName AS '理货人员',
       p.BillingPersonName AS '制单人员',
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE p.Country END 收件人国家二字简码,
       os.OrderSourceName 渠道来源名称,
       o.Currency 售价币种,
       o.ClientUserAccount 客户ID,
       o.TransactionId PayPal交易号,
       p.PrintBatchNo 打印批次,
       CASE
           WHEN p.PackageState = 0 THEN
               '正常'
           WHEN p.PackageState = 1 THEN
               '已配货'
           WHEN p.PackageState = 2 THEN
               '已打印'
           WHEN p.PackageState = 3 THEN
               '已发货'
           WHEN p.PackageState = 3 THEN
               '已作废'
           WHEN p.PackageState = 3 THEN
               '已拣货'
       END AS '包裹状态',
       CASE
           WHEN p.PrintType = 0 THEN
               '默认'
           WHEN p.PrintType = 1 THEN
               '按条码打单'
           WHEN p.PrintType = 2 THEN
               '二次分拣'
       END AS 打单类型,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE c.Country_cn END 收件人国家中文名称,
       o.AmazonShipmentId ShipmentID,
       p.LogisticsOrderNo 物流商单号,
       o.TotalPrice 订单总金额,
       o.ProductTotalPrice 产品总金额,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE o.Address1 END 地址1,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE o.Address2 END 地址2,
       case when (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,o.PayTime,GETDATE())>90 THEN '*' ELSE o.Address3 END 地址3
FROM RB_Procurement_Order_Package p WITH (NOLOCK)
    INNER JOIN RB_Procurement_Order_Package_Detail pd WITH (NOLOCK)
        ON p.ID = pd.PackageID
           AND p.CustomerID = pd.CustomerID
    LEFT JOIN RB_Transport t WITH (NOLOCK)
        ON p.TransportID = t.ID
           AND p.CustomerID = t.CustomerID
    LEFT JOIN RB_Transport_API api WITH (NOLOCK)
        ON t.Transport_API_ID = api.ID
           AND t.CustomerID = api.CustomerID
    LEFT JOIN RB_OrderInfo o WITH (NOLOCK)
        ON p.MergerPackageNo = o.OrderCode
           AND p.CustomerID = o.CustomerID
    LEFT JOIN
    (
        SELECT CustomerID,
               OrderCode,
               SKU,
               MAX(ProductPrice) ProductPrice,
               MAX(ActivityMinusPrice) ActivityMinusPrice,
               MAX(ShippingPrice) ShippingPrice
        FROM dbo.RB_OrderList (NOLOCK)
        GROUP BY CustomerID,
                 OrderCode,
                 SKU
    ) l
        ON l.CustomerID = o.CustomerID
           AND l.OrderCode = o.OrderCode
           AND l.SKU = pd.SKU
    LEFT JOIN RB_OrderSource os WITH (NOLOCK)
        ON os.CustomerID = o.CustomerID
           AND os.ID = o.OrderSourceID
    LEFT JOIN E_Country c WITH (NOLOCK)
        ON p.Country = c.Shorthand
    LEFT JOIN RB_Transport_ReturnAddress trs WITH (NOLOCK)
        ON p.CustomerID = trs.CustomerID
           AND p.TransportID = trs.TransportID
           AND t.DefaultSupplierID = trs.SupplierID
    LEFT JOIN RB_Product pt WITH (NOLOCK)
        ON pd.CustomerID = pt.CustomerID
           AND pd.SKU = pt.SKU
    LEFT JOIN dbo.RB_WareHouse AS wh WITH (NOLOCK)
        ON wh.CustomerID = o.CustomerID
           AND o.WareHouseID = wh.ID
    LEFT JOIN RB_Package_DeliverySupplier_Package sp WITH (NOLOCK)
        ON p.CustomerID = sp.CustomerID
           AND p.ID = sp.PackageID
    LEFT JOIN RB_Package_DeliverySupplier_Batch dp WITH (NOLOCK)
        ON sp.CustomerID = dp.CustomerID
           AND sp.BatchNo = dp.BatchNo
WHERE   p.CustomerID = @CustomerID
        AND o.IsStockOrder=@IsStockOrder
        AND {{p.ID = @ID}}
        AND {{pt.SKU = @SKU}}
        AND {{p.PickingPersonID = @PickingPersonName}}
        AND {{p.PackageState = @PackageState}}
        AND {{o.OrderSourceID IN (@OrderSourceName)}}
        AND {{p.TransportID in (@transportID)}}
        AND p.AddTime > = @StartTime
        AND p.AddTime < = @EndTime
        AND {{o.AddTime >=@StartAddTime}}
        AND {{o.AddTime <=@EndAddTime}}
        AND {{p.DistributionTime>=@StartDistributionTime}}
        AND {{p.DistributionTime<=@EndDistributionTime}}
        AND {{p.PrintTime>=@StartPrintTime}}
        AND {{p.PrintTime<=@EndPrintTime}}
        AND {{p.PickingTime>=@StartPickingTime}}
        AND {{p.PickingTime<=@EndPickingTime}}
        AND {{p.PackingTime>=@StartPackingTime}}
        AND {{p.PackingTime<=@EndPackingTime}}
        AND {{p.ShipTime >=@StartShipTime}}
        AND {{p.ShipTime <=@EndShipTime}}
        AND {{p.GetTrackNumberTime>=@StartGetTime}}
        AND {{p.GetTrackNumberTime<=@EndGetTime}}
        AND {{p.TallyTime>=@StartTallyTime}}
        AND {{p.TallyTime<=@EndTallyTime}}
        AND {{o.WareHouseID IN (@WareHouseID)}}