1.客户订单-选中订单自定义导出

执行 http://localhost:31197/testcw.aspx 中 执行订单自定义导出

导出

2.客户订单-当前搜索条件导出订单

执行 http://localhost:31197/testcw.aspx 中  执行SQL导出队列

导出

3.客户订单包裹-选中包裹自定义导出

执行 http://localhost:31197/testcw.aspx 执行包裹自定义导出

导出

4.自定义报告导出-订单产品明细表

执行 http://localhost:31197/ProfitTest.aspx 执行自定义报告导出

记录语句 需要配置

SELECT  c.ID AS 'ID' ,
        a.OrderCode AS '系统订单号' ,
        a.ClientOrderCode AS '平台订单号' ,
        CASE WHEN a.ShipTime = '2000-01-01 00:00:00.000' THEN NULL
             ELSE a.ShipTime
        END AS '发货时间' ,
        CASE WHEN a.PayTime = '2000-01-01 00:00:00.000' THEN NULL
             ELSE a.PayTime
        END AS '付款时间' ,
        w1.WareHouseName AS '发货仓库' ,
        w1.WareHouseCountry AS '发货国家' ,
        w2.WareHouseName AS '到货仓库' ,
        CASE WHEN a.IsStockOrder = 0 THEN  a.Country
             WHEN a.IsStockOrder = 1 THEN w2.WareHouseCountry
             ELSE ''
        END AS '到货国家' ,
        CASE WHEN a.IsPay = 0 THEN '未付款'
             ELSE '已付款'
        END AS '付款状态' ,
        CASE WHEN a.OrderState = 0 THEN '未发货'
             WHEN a.OrderState = 1 THEN '部分发货'
             WHEN a.OrderState = 2 THEN '全部发货'
             WHEN a.OrderState = 3 THEN '妥投'
             ELSE ''
        END AS '订单发货状态' ,
        CASE WHEN a.OrderStatus = 0 THEN '正常'
             WHEN a.OrderStatus = 1 THEN '待审核'
             WHEN a.OrderStatus = 2 THEN '作废'
             WHEN a.OrderStatus = 3 THEN '锁定'
             WHEN a.OrderStatus = 4 THEN '只锁定发货'
             WHEN a.OrderStatus = 5 THEN '已完成'
             ELSE ''
        END AS '订单状态' ,
        CASE WHEN a.IsStockOrder = 0 THEN '否'
             WHEN a.IsStockOrder = 1 THEN '是'
             ELSE ''
        END AS '是否备库' ,
        b.OrderSourceName AS '来源渠道' ,
        c.SKU AS '系统SKU' ,
        d.ClientSKU AS '自定义SKU' ,
        c.SellerSKU AS '渠道SKU' ,
        c.OrderItemId AS 'OrderItemId' ,
        a.TotalPrice AS '订单总额' ,
        a.ProductTotalPrice AS '订单产品总价' ,
        a.TransportPay AS '订单运费总额' ,
        a.PromotionDiscountAmount AS '促销折扣总额' ,
        ISNULL(c.ProductNum, 0) + ISNULL(c.ProductNumUpdate, 0) AS '销售数量' ,
        a.Currency AS '订单币种' ,
        a.ExchangeRMB AS '对人民币汇率' ,
        a.ExchangeUSD AS '对美元汇率' ,
        c.ProductPrice AS '产品售价' ,
        CAST(v.VATRatio AS VARCHAR(100)) + '%' AS 'VAT税率',
a.SalesRecordNumber AS 'eBay销售记录号',
		 a.ProductWeight AS '订单重量(g)',
 CASE 
		 WHEN (b.OrderSourceType=97 OR b.OrderSourceType=42) AND  datediff(day,a.PayTime,GETDATE())>90
		 THEN '*'
		 ELSE a.Email 
		 END AS  '收件人邮箱',
        CASE WHEN a.ProductVolume != 0 AND a.ProductWeight != 0 THEN (a.ProductVolume/6000.00)/(a.ProductWeight/1000.00) ELSE 0 END AS '抛率',
        t.TransportName AS '运输方式',
        a.TrackNumbers AS '真实追踪号',
        a.LogisticsOrderNo AS '物流商单号'
FROM    RB_OrderInfo (NOLOCK) a
        LEFT JOIN dbo.RB_OrderSource (NOLOCK) b ON a.CustomerID = b.CustomerID
                                                   AND a.OrderSourceID = b.ID
        LEFT JOIN dbo.RB_OrderList (NOLOCK) c ON a.CustomerID = c.CustomerID
                                                 AND a.OrderCode = c.OrderCode
        LEFT JOIN dbo.RB_Product (NOLOCK) d ON c.CustomerID = d.CustomerID
                                                 AND c.SKU = d.SKU
        LEFT JOIN dbo.RB_WareHouse (NOLOCK) w1 ON a.CustomerID = w1.CustomerID
                                                 AND a.WareHouseID = w1.ID
        LEFT JOIN dbo.RB_WareHouse (NOLOCK) w2 ON a.CustomerID = w2.CustomerID
                                                 AND a.ToWareHouseID = w2.ID
        LEFT JOIN dbo.RB_OrderSourceSales_VAT (NOLOCK) v ON a.CustomerID = v.CustomerID
                                                            AND a.OrderSourceID = v.OrderSourceID
                                                            AND w1.WareHouseCountry = v.DeliveryCountry
                                                            AND w2.WareHouseCountry = v.ArriveCountry
LEFT JOIN RB_Transport (NOLOCK) t ON a.CustomerID = t.CustomerID
                                                 AND a.TransportID = t.ID
WHERE   a.CustomerID = @CustomerID
        AND {{a.shiptime >= @Start_Shiptime}}
        AND {{a.shiptime <= @End_Shiptime}}
        AND {{a.Paytime >= @StartTime}}
        AND {{a.Paytime <= @EndTtime}}
        AND {{a.Ispay =  @Ispay }}
        AND {{a.OrderState in (@OrderState) }}
        AND {{a.OrderStatus in (@OrderStatus) }}
        AND {{a.IsStockOrder =  @IsStockOrder }}
        AND {{a.OrderSourceID in (@OrderSourceID) }}
        AND {{b.OrderSourceType in (@OrderSourceType) }}
        AND {{a.OrderCode = @OrderCode}}
        AND {{a.ClientOrderCode = @ClientOrderCode}}
        AND {{c.OrderItemId= @OrderItemId}}

配置语句

SELECT
  oi.OrderCode as '系统订单号',
  oi.ClientOrderCode as '平台订单号',
  case
    when os.OrderSourceType = 57 then oi.PayerId
    else oi.ClientUserAccount
  end as '客户ID',
  case
    when os.OrderSourceType = 1
    and oi.PayTime < Dateadd(DAY, -30, Getdate())
    and oi.IsStockOrder = 0 then ''
    else oi.Email
  end as '平台用户邮箱',
  os.OrderSourceName as '订单来源渠道',
  t.TransportName as '运输方式',
  wh.WareHouseName as '发货仓库',
  oi.Currency as '订单使用币种',
  oi.ExchangeRMB as '兑换人民币汇率',
  oi.ExchangeUSD as '兑换美元汇率',
  convert(decimal(18, 2), oi.TransportPay) as '支付运费',
  case
    oi.IsPay
    when '1' then '已支付'
    when '0' then '未支付'
  end '是否支付',
  oi.PayTime as '支付时间',
  convert(decimal(18, 2), oi.TotalPrice) as '订单总金额',
  convert(decimal(18, 2), oi.ProductTotalPrice) as '产品总金额',
  oi.OrderDescription as '订单备注',
  case
    when os.OrderSourceType = 1
    and oi.PayTime < Dateadd(DAY, -30, Getdate())
    and oi.IsStockOrder = 0 then ''
    else oi.Country
  end as '收货国家缩写',
  case
    oi.OrderStatus
    when 0 then '正常'
    when 1 then '待审核'
    when 2 then '作废'
    when 3 then '锁定'
    when 4 then '只锁定发货'
    when 5 then '已完成'
    when 6 then '待确认'
  end as '订单状态',
  case
    oi.OrderState
    when 0 then '未发货'
    when 1 then '部分发货'
    when 2 then '全部发货'
    when 3 then '妥投'
  end as '发货状态',
  case
    when os.OrderSourceType = 1
    and oi.PayTime < Dateadd(DAY, -30, Getdate())
    and oi.IsStockOrder = 0 then ''
	WHEN (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,oi.PayTime,GETDATE())>90 THEN '*'	
    else e.Country_cn
  end as '收货国家中文名',
  case
    when os.OrderSourceType = 1
    and oi.PayTime < Dateadd(DAY, -30, Getdate())
    and oi.IsStockOrder = 0 then ''
	WHEN (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,oi.PayTime,GETDATE())>90 THEN '*'	
    else e.Country_en
  end as '收货国家英文名',
  case
    when os.OrderSourceType = 1
    and oi.PayTime < Dateadd(DAY, -30, Getdate())
    and oi.IsStockOrder = 0 then ''
	WHEN (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,oi.PayTime,GETDATE())>90 THEN '*'	
    else oi.Province
  end as '收货省份',
  ol.SKU as '发货产品SKU',
  p.ClientSKU as '发货产品自定义SKU',
  popo.ProductNum as '发货产品数量',
  oi.TrackNumbers as '发货追踪号',
  oi.ShipTime as '发货时间',
  oi.TrackNumberTemp as '本地追踪号',
  pop.ID as '包裹号',
  ol.ASIN as '发货产品ASIN',
  convert(decimal(18, 2), pop.FinalShippingFee) as '发货包裹运费',
  oi.SalesRecordNumber as 'Ebay订单号',
  ol.SellerSKU as '发货产品渠道SKU',
  oi.AddTime as '添加时间',
  oi.TransactionId as '交易号',
  convert(decimal(18, 2), oi.RefundPrice) as '退款金额',
  oi.AmazonShipmentId as 'ShipmentID',
  ol.OrderItemId as 'OrderItemId',
  oi.PayerId as '买家名称',
  oi.[UpdatePlatformTime] as '标记发货时间',
   CASE 
		 WHEN (os.OrderSourceType=97 OR os.OrderSourceType=42) AND  datediff(day,oi.PayTime,GETDATE())>90
		 THEN '*'
		 ELSE oi.PostCode 
  END  AS '邮编',
  oi.LatestDeliveryDate AS '速卖通最后发货时间',
  pop.SecurityReturnTime as '安检退回时间'
FROM
  RB_OrderInfo oi (nolock)
  LEFT JOIN RB_OrderSource os WITH(nolock) ON oi.CustomerID = os.CustomerID
  AND oi.OrderSourceID = os.ID
  LEFT JOIN E_Country e WITH(nolock) ON oi.Country = e.Shorthand
  LEFT JOIN RB_Transport t WITH(nolock) ON oi.CustomerID = t.CustomerID
  AND oi.TransportID = t.ID
  LEFT JOIN RB_WareHouse wh WITH(nolock) ON oi.CustomerID = wh.CustomerID
  AND oi.WareHouseID = wh.ID
  LEFT JOIN RB_OrderList ol (nolock) ON oi.CustomerID = ol.CustomerID
  AND oi.OrderCode = ol.OrderCode
  AND ol.ProductNum + ol.[ProductNumUpdate] > 0
  LEFT JOIN RB_Product p(nolock) ON p.CustomerID = ol.CustomerID
  AND p.SKU = ol.SKU
  LEFT JOIN RB_Procurement_Order_Package_OrderDetail popo (nolock) ON popo.CustomerID = oi.CustomerID
  AND popo.OrderCode = oi.OrderCode
  AND popo.SKU = ol.SKU
  AND popo.OrderListID = ol.ID
  LEFT JOIN RB_Procurement_Order_Package pop (nolock) ON pop.CustomerID = oi.CustomerID
  AND pop.MergerPackageNo = oi.OrderCode
WHERE
  oi.CustomerID = @CustomerID
  AND oi.IsStockOrder = 0
  AND oi.AddTime >= @StartTime
  AND oi.AddTime <= @EndStartTime
  AND {{ oi.OrderCode = @OrderCode }}
  AND {{ oi.ClientOrderCode = @ClientOrderCode }}
  AND {{ oi.OrderSourceID in(@OrderSourceID) }}
  AND {{ oi.OrderStatus in (@OrderStatus) }}
  AND {{ oi.IsPay = @IsPay }}
  AND {{ oi.OrderState = @OrderState }}
  AND {{ oi.PayTime >= @StartPayTime }}
  AND {{ oi.PayTime <= @EndStartPayTime }}
  AND {{ oi.ShipTime >= @StartShipTime }}
  AND {{ oi.ShipTime <= @EndStartShipTime }}
  AND {{ oi.WareHouseID IN (@WareHouseID) }}
  AND {{ oi.TransportID IN (@TransportID) }}
  AND {{ oi.LogisticsOrderNo != oi.TrackNumberTemp
  AND oi.TrackNumbers != oi.TrackNumberTemp
  AND oi.LogisticsOrderNo1 != oi.TrackNumberTemp
  AND oi.MainOrderCode != oi.TrackNumberTemp
  AND ISNULL(oi.TrackNumberTemp,'') != ''
  AND 1 = @TrackNumberTemp }}

导出