修改了 指派给，旧值为 "songyu"，新值为 ""。

修改了 Bug标题 ，区别为：

001- 在自定义报告页面选择【Cdiscount平台订单退款分析报告】页面一直在加载中
001+ zanwu

001- 在自定义报告页面选择【Cdiscount平台订单退款分析报告】页面一直在加载中
001+ zanwu

修改了 重现步骤，区别为：

001- [步骤]
001+
002- 1.在测试环境自定义报告配置中配置内容如下：
003-
004- 配置的sql：
005- DECLARE @OrderSourceType int=35CREATE TABLE #OrderInfoFundReceipt( ID INT, AudiState INT, Currency VARCHAR(50), FundMoney MONEY, EbayFundType INT, CustomerID INT, ReasonTypes VARCHAR(100), FundHandleType INT, OrderCode VARCHAR(50), ClientOrderCode VARCHAR(50), OrderSourceID INT, Country VARCHAR(50), ProductTotalPrice MONEY, TransportPay MONEY, ServiceAdminName VARCHAR(50), OrderState INT, TransportID INT, ExchangeUSD MONEY, IsFBAOrder BIT, OrderSourceType INT, OrderSourceName VARCHAR(50), RefundTime DATETIME, ShipTime DATETIME, PayTime DATETIME, WareHouseID INT, TotalPrice MONEY, RefundCauseID1 INT, RefundCauseID2 INT, RefundCauseName1 VARCHAR(200), RefundCauseName2 VARCHAR(200));----------------退款数据IF @type=1INSERT INTO #OrderInfoFundReceipt ( ID, AudiState, Currency, FundMoney, EbayFundType, CustomerID, ReasonTypes, RefundTime, FundHandleType, OrderCode, ClientOrderCode, OrderSourceID, Country, ProductTotalPrice, TransportPay, ServiceAdminName, OrderState, TransportID, ExchangeUSD, OrderSourceType, OrderSourceName, ShipTime, PayTime, IsFBAOrder, WareHouseID, TotalPrice ) SELECT Refund.ID,Refund.AudiState,Refund.Currency,Refund.FundMoney,Refund.EbayFundType,Refund.CustomerID,Refund.ReasonTypes,Refund.AddTime,Refund.FundHandleType, info.OrderCode,info.ClientOrderCode,info.OrderSourceID,info.Country,info.ProductTotalPrice,info.TransportPay,os.ServiceAdminName,info.OrderState ,info.TransportID,info.ExchangeUSD,os.OrderSourceType,os.OrderSourceName,info.ShipTime,info.PayTime,info.IsFBAOrder,info.WareHouseID,info.TotalPrice FROM RB_OrderInfo_FundReceipt Refund (nolock) inner join RB_OrderInfo info (nolock) on Refund.OrderCode=info.OrderCode and Refund.CustomerID=info.CustomerID LEFT JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = info.CustomerID AND info.OrderSourceID=os.ID where Refund.AudiState!=3 AND Refund.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType AND info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and Refund.AddTime between @startDate and @endDate+' 23:59:59' and info.IsStockOrder = 0; ELSEINSERT INTO #OrderInfoFundReceipt ( ID, AudiState, Currency, FundMoney, EbayFundType, CustomerID, ReasonTypes, RefundTime, FundHandleType, OrderCode, ClientOrderCode, OrderSourceID, Country, ProductTotalPrice, TransportPay, ServiceAdminName, OrderState, TransportID, ExchangeUSD, OrderSourceType, OrderSourceName, ShipTime, PayTime, IsFBAOrder, WareHouseID, TotalPrice ) SELECT Refund.ID,Refund.AudiState,Refund.Currency,Refund.FundMoney,Refund.EbayFundType,Refund.CustomerID,Refund.ReasonTypes,Refund.AddTime,Refund.FundHandleType, info.OrderCode,info.ClientOrderCode,info.OrderSourceID,info.Country,info.ProductTotalPrice,info.TransportPay,os.ServiceAdminName,info.OrderState ,info.TransportID,info.ExchangeUSD,os.OrderSourceType,OrderSourceName,info.ShipTime,info.PayTime,info.IsFBAOrder,info.WareHouseID,info.TotalPrice FROM RB_OrderInfo_FundReceipt Refund (nolock) inner join RB_OrderInfo info (nolock) on Refund.OrderCode=info.OrderCode and Refund.CustomerID=info.CustomerID LEFT JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = info.CustomerID AND info.OrderSourceID=os.ID where Refund.AudiState!=3 AND Refund.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType AND info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and info.PayTime between @startDate and @endDate+' 23:59:59' and info.IsStockOrder=0; UPDATE #temp set RefundCauseID1=rc1.CauseID,RefundCauseName1=rc1.CauseName, RefundCauseID2=ISNULL(rc2.CauseID,0),RefundCauseName2=ISNULL(rc2.CauseName,'其它原因')FROM #OrderInfoFundReceipt #temp LEFT JOIN RB_OrderInfo_RefundCause AS rc2 ON rc2.CustomerID = #temp.CustomerID AND rc2.CauseID IN(select columnName from dbo.fn_StrToTable(#temp.ReasonTypes) where columnName <> '') LEFT JOIN RB_OrderInfo_RefundCause AS rc1 ON rc1.CustomerID = #temp.CustomerID AND rc2.ParentID=rc1.CauseID;--明细数据SELECT Refund.ID,Refund.CustomerID,Refund.OrderCode,ISNULL(oss.AddAdminName,'') AddAdminName,fp.ProductSKU AS SKU,p.ProductNameCN,ol.SellerSKU,fp.ProductPrice,fp.ProductNum,(ol.ProductNum + ol.[ProductNumUpdate]) AS OrderProductNum,fp.OrderListID,ol.BusinessAdminID,ISNULL(ol.BusinessAdminName,'') BusinessAdminName,ol.BusinessTeamID,ISNULL(ol.BusinessTeamName,'') BusinessTeamNameINTO #OrderInfoFundReceiptListFROM #OrderInfoFundReceipt Refund (nolock)inner join RB_Fund_Products fp (nolock) on Refund.ID=fp.ReceiptID and Refund.CustomerID=fp.CustomerIDINNER JOIN dbo.RB_OrderList AS ol (NOLOCK) ON ol.CustomerID = Refund.CustomerID AND ol.OrderCode = Refund.OrderCode AND fp.OrderListID=ol.IDLEFT JOIN dbo.RB_Product AS p(NOLOCK) ON p.CustomerID = fp.CustomerID AND p.SKU = ol.SKULEFT JOIN RB_Product_OrderSourceSKU AS oss(NOLOCK) ON oss.CustomerID = fp.CustomerID AND oss.OrderSourceSKU=ol.SellerSKUwhere Refund.CustomerID=@CustomerID;---------Sheet2CREATE TABLE #TempProductStatistics(CustomerID INT,BusinessTeamName varchar(50),OrderSourceName varchar(50),AddAdminName varchar(50),SKU varchar(50),ProductNameCN varchar(1000),OrderSourceID INT,ReturnProductNum INT DEFAULT 0,SaleProductNum INT DEFAULT 0,ReturnNumProportion varchar(10), ReturnMoney MONEY DEFAULT 0,SaleMoney MONEY DEFAULT 0, ReturnMoneyProportion varchar(10), ReturnOrderNum INT DEFAULT 0,SaleOrderNum INT DEFAULT 0,ReturnOrderNumProportion varchar(20),FBAReturnMoney MONEY DEFAULT 0,FBASaleMoney MONEY DEFAULT 0, FBAReturnMoneyProportion varchar(10),NoFBAReturnMoney MONEY DEFAULT 0,NoFBASaleMoney MONEY DEFAULT 0, NoFBAReturnMoneyProportion varchar(10));--Sheet2（ebay,速卖通,wish版）：按上架人员SKU统计（重新整合）INSERT INTO #TempProductStatistics( CustomerID, BusinessTeamName, OrderSourceName, AddAdminName, SKU, ProductNameCN, OrderSourceID, ReturnProductNum, ReturnMoney, ReturnOrderNum, FBAReturnMoney, NoFBAReturnMoney)SELECT f.CustomerID, fp.BusinessTeamName,f.OrderSourceName,fp.AddAdminName,fp.SKU,ProductNameCN,f.OrderSourceID ,SUM(fp.ProductNum) AS ReturnProductNum ,SUM(fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ReturnMoney ,COUNT(DISTINCT f.OrderCode) ReturnOrderNum ,SUM(CASE f.IsFBAOrder WHEN 1 THEN (fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ELSE 0 END) FBAReturnMoney ,SUM(CASE f.IsFBAOrder WHEN 0 THEN (fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ELSE 0 END) NoFBAReturnMoneyFROM #OrderInfoFundReceipt AS fINNER JOIN #OrderInfoFundReceiptList AS fp ON fp.CustomerID = f.CustomerID AND fp.ID = f.ID AND fp.OrderCode = f.OrderCodeGROUP BY f.CustomerID,fp.BusinessTeamName,f.OrderSourceName,fp.AddAdminName,fp.SKU,ProductNameCN,f.OrderSourceID; select ol.CustomerID,ol.SKU,ISNULL(ol.BusinessTeamName,'') BusinessTeamName,ol.SellerSKU,oi.OrderSourceID, SUM(ol.ProductPrice*(ol.ProductNum + ol.[ProductNumUpdate])/(case when oi.ExchangeUSD =0 then 1 else oi.ExchangeUSD end)) SaleMoney ,SUM((ol.ProductNum + ol.[ProductNumUpdate])) SaleProductNum ,COUNT(oi.OrderCode) AS SaleOrderNum INTO #orderinfo2 from RB_OrderList ol (nolock) inner join RB_OrderInfo oi (nolock) on ol.OrderCode = oi.OrderCode and ol.CustomerID=oi.CustomerID left join RB_OrderSource os (nolock) ON os.ID= oi.OrderSourceID and os.CustomerID=oi.CustomerID where oi.PayTime between @startDate and @endDate+' 23:59:59' and oi.IsStockOrder=0 AND [DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and ol.CustomerID=@CustomerID and os.OrderSourceType=@OrderSourceType group by ol.CustomerID,ol.SKU,ISNULL(ol.BusinessTeamName,''),ol.SellerSKU,oi.OrderSourceID; UPDATE #temp SET SaleProductNum=ISNULL(a.SaleProductNum,0) ,#temp.SaleMoney=ISNULL(a.SaleMoney,0) ,#temp.SaleOrderNum=ISNULL(a.SaleOrderNum,0) FROM #TempProductStatistics AS #temp INNER JOIN ( SELECT a.CustomerID,a.SKU,a.BusinessTeamName,a.OrderSourceID,ISNULL(AddAdminName,'')AddAdminName ,SUM(SaleMoney)SaleMoney,SUM(SaleProductNum) SaleProductNum,SUM(SaleOrderNum)SaleOrderNum FROM #orderinfo2 AS a LEFT JOIN RB_Product_OrderSourceSKU AS oss(NOLOCK) ON oss.CustomerID = a.CustomerID AND oss.OrderSourceSKU=a.SellerSKU GROUP BY a.CustomerID,a.SKU,a.BusinessTeamName,a.OrderSourceID,ISNULL(AddAdminName,'') ) AS a ON a.CustomerID = #temp.CustomerID AND a.BusinessTeamName = #temp.BusinessTeamName AND a.SKU = #temp.SKU AND a.AddAdminName = #temp.AddAdminName AND a.OrderSourceID = #temp.OrderSourceID; update #TempProductStatistics setReturnMoneyProportion=(case when SaleMoney=0 then '0' else ReturnMoney * 100 /SaleMoney end),ReturnNumProportion = (case when SaleProductNum=0 then '0' else CONVERT(MONEY,ReturnProductNum)*100 /SaleProductNum end),ReturnOrderNumProportion = (case when SaleOrderNum=0 then '0' else CONVERT(MONEY,ReturnOrderNum)*100 /SaleOrderNum end),FBAReturnMoneyProportion = (case when FBASaleMoney=0 then '0' else FBAReturnMoney *100 /FBASaleMoney end),NoFBAReturnMoneyProportion = (case when NoFBASaleMoney=0 then '0' else NoFBAReturnMoney *100 /NoFBASaleMoney end);update #TempProductStatistics setReturnMoneyProportion=CASE WHEN ReturnMoneyProportion='0'OR ReturnMoneyProportion='0.00' THEN '-' ELSE ReturnMoneyProportion + '%' END,ReturnNumProportion =CASE WHEN ReturnNumProportion='0'OR ReturnNumProportion='0.00' THEN '-' ELSE ReturnNumProportion + '%' END,ReturnOrderNumProportion = CASE WHEN ReturnOrderNumProportion='0'OR ReturnOrderNumProportion='0.00' THEN '-' ELSE ReturnOrderNumProportion + '%' END,FBAReturnMoneyProportion = CASE WHEN FBAReturnMoneyProportion='0'OR FBAReturnMoneyProportion='0.00' THEN '-' ELSE FBAReturnMoneyProportion + '%' END,NoFBAReturnMoneyProportion = CASE WHEN NoFBAReturnMoneyProportion='0'OR NoFBAReturnMoneyProportion='0.00' THEN '-' ELSE NoFBAReturnMoneyProportion + '%' END;----------------Sheet3: 按账号仓库角度统计CREATE TABLE #TempWareHouseStatistics( CustomerID INT, OrderSourceID INT, OrderSourceName varchar(50), WareHouseID INT, WareHouseName varchar(50), ShipedTotalPrice MONEY DEFAULT 0, ShipedOrderCodeNum INT DEFAULT 0, TotalRefundMoney MONEY DEFAULT 0, TotalRefundOrderNum INT DEFAULT 0, RefundMoneyProportion varchar(10), RefundMoneyAccounted varchar(10), RefundOrderAccounted varchar(10), RefundOrderProportion varchar(10)); INSERT INTO #TempWareHouseStatistics (CustomerID,OrderSourceID,OrderSourceName,WareHouseID,WareHouseName,TotalRefundMoney,TotalRefundOrderNum) SELECT rp.CustomerID,rp.OrderSourceID,rp.OrderSourceName,rp.WareHouseID,wh.WareHouseName ,SUM(rp.TotalPrice/case when rp.ExchangeUSD=0 then 1 else ExchangeUSD end) AS TotalRefundMoney ,COUNT(rp.OrderCode) AS TotalRefundOrderNum FROM #OrderInfoFundReceipt AS rp LEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON wh.CustomerID = rp.CustomerID AND rp.WareHouseID=wh.ID GROUP BY rp.CustomerID,rp.OrderSourceID,rp.OrderSourceName,rp.WareHouseID,wh.WareHouseName; UPDATE #temp SET ShipedTotalPrice=ISNULL(a.ShipedTotalPrice,0),ShipedOrderCodeNum=ISNULL(a.ShipedOrderCodeNum,0) FROM #TempWareHouseStatistics AS #temp INNER JOIN ( SELECT oi.CustomerID, oi.OrderSourceID,oi.WareHouseID ,SUM(CASE WHEN oi.OrderState=2 THEN (oi.TotalPrice/case when oi.ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) AS ShipedTotalPrice ,SUM(CASE WHEN oi.OrderState=2 THEN 1 ELSE 0 end) AS ShipedOrderCodeNum FROM RB_OrderInfo AS oi(NOLOCK) INNER JOIN dbo.RB_OrderSource AS os ON os.CustomerID = oi.CustomerID AND oi.OrderSourceID=os.ID WHERE oi.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType AND oi.PayTime between @startDate and @endDate+' 23:59:59' and oi.IsStockOrder=0 GROUP BY oi.CustomerID, oi.OrderSourceID,oi.WareHouseID )AS a ON a.CustomerID = #temp.CustomerID AND a.WareHouseID = #temp.WareHouseID AND a.OrderSourceID = #temp.OrderSourceID; UPDATE #temp SET RefundOrderAccounted=(case when ShipedOrderCodeNum=0 then '1' ELSE convert(money,#temp.TotalRefundOrderNum) /ShipedOrderCodeNum end) * 100 , RefundMoneyAccounted = (case when ShipedTotalPrice=0 then '1' else #temp.TotalRefundMoney /ShipedTotalPrice end) * 100 , RefundOrderProportion = (case when a.TotalRefundOrderNum=0 then '0' else convert(money,#temp.TotalRefundOrderNum) *100 /a.TotalRefundOrderNum end), RefundMoneyProportion = (case when a.TotalRefundMoney=0 then '0' else #temp.TotalRefundMoney *100 /a.TotalRefundMoney end) FROM #TempWareHouseStatistics AS #temp INNER JOIN ( SELECT CustomerID, SUM(TotalRefundOrderNum) TotalRefundOrderNum ,SUM(TotalRefundMoney) AS TotalRefundMoney FROM #TempWareHouseStatistics GROUP BY CustomerID ) AS a ON a.CustomerID = #temp.CustomerID; UPDATE #temp SET RefundOrderAccounted=CASE WHEN RefundOrderAccounted='0'OR RefundOrderAccounted='0.00' THEN '-' ELSE RefundOrderAccounted + '%' END, RefundMoneyAccounted = CASE WHEN RefundMoneyAccounted='0'OR RefundMoneyAccounted='0.00' THEN '-' ELSE RefundMoneyAccounted + '%' END, RefundOrderProportion = CASE WHEN RefundOrderProportion='0'OR RefundOrderProportion='0.00' THEN '-' ELSE RefundOrderProportion + '%' END, RefundMoneyProportion =CASE WHEN RefundMoneyProportion='0'OR RefundMoneyProportion='0.00' THEN '-' ELSE RefundMoneyProportion + '%' END FROM #TempWareHouseStatistics AS #temp; ---------------Sheet4:退款原因角度统计SELECT ofp.CustomerID, ISNULL(ofp.RefundCauseName1,ofp.RefundCauseName2) RefundCauseName1, ofp.RefundCauseName2,SUM(CASE WHEN wh.WareHouseType=1 THEN (ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) ELSE 0 END) BenWareHouseFundMoney,SUM(CASE WHEN wh.WareHouseType<>1 THEN (ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) ELSE 0 END) HaiWareHouseFundMoney,COUNT(1) ComplaintsNum,CONVERT(varchar(10),'') RefundCauseProportion,SUM(ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) FundMoney,COUNT( ofp.OrderCode) OrderCodeNum,CONVERT(varchar(10),'') AS FundMoneyProportionINTO #TempRefundReasonStatisticsFROM #OrderInfoFundReceipt AS ofpLEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON ofp.WareHouseID=wh.ID AND wh.CustomerID = ofp.CustomerIDGROUP BY ofp.CustomerID,ISNULL(ofp.RefundCauseName1,ofp.RefundCauseName2),ofp.RefundCauseName2;UPDATE #temp SET RefundCauseProportion = (case when TotalComplaintsNum=0 then '0' else convert(money,ComplaintsNum) * 100 /TotalComplaintsNum end), FundMoneyProportion = (case when TotalFundMoney=0 then '0' else FundMoney * 100 /TotalFundMoney end)FROM #TempRefundReasonStatistics AS #tempINNER JOIN ( SELECT CustomerID, SUM(ComplaintsNum) TotalComplaintsNum,SUM(FundMoney) TotalFundMoney FROM #TempRefundReasonStatistics GROUP BY CustomerID)AS a ON a.CustomerID = #temp.CustomerID;UPDATE #temp SET RefundCauseProportion =CASE WHEN RefundCauseProportion='0'OR RefundCauseProportion='0.00' THEN '-' ELSE RefundCauseProportion + '%' END, FundMoneyProportion =CASE WHEN FundMoneyProportion='0'OR FundMoneyProportion='0.00' THEN '-' ELSE FundMoneyProportion + '%' ENDFROM #TempRefundReasonStatistics AS #temp;----------------Sheet5：物流渠道角度统计CREATE TABLE #TempLogisticsStatistics( id [int] PRIMARY KEY NOT NULL identity(1,1), CustomerID int, TransportID int, TotalOrderNum INT DEFAULT 0, TransportName varchar(300) , RefundMoney money DEFAULT 0, ShipedTotalPrice money DEFAULT 0, RefundMoneyProportion varchar(100) , RefundMoneyAccounted varchar(100) , ComplaintsNum int DEFAULT 0, ComplaintsNumProportion varchar(100), ComplaintsNumAccounted varchar(100), ShipedOrderNum INT DEFAULT 0, ShipedRefundOrderNum INT DEFAULT 0, RefundOrderNum INT DEFAULT 0, ComplaintsRefundOrderNum INT DEFAULT 0, ShipedRefundOrderProportion varchar(100), ShipedRefundOrderAccounted varchar(100) );INSERT INTO #TempLogisticsStatistics(CustomerID,TransportID,TransportName,RefundMoney,ShipedRefundOrderNum,RefundOrderNum)SELECT f.CustomerID,f.TransportID,ISNULL(t.TransportName,'') ,SUM(TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) as RefundMoney ,SUM(CASE WHEN f.OrderState=2 THEN 1 ELSE 0 end) AS ShipedRefundOrderNum ,COUNT(f.OrderCode) RefundOrderNum FROM #OrderInfoFundReceipt AS f LEFT JOIN RB_Transport t (nolock) on f.TransportID=t.ID and f.CustomerID=t.CustomerID GROUP BY f.CustomerID,f.TransportID,ISNULL(t.TransportName,'');UPDATE #temp SET ShipedTotalPrice=a.ShipedTotalPrice,ShipedOrderNum=a.ShipedOrderNum ,TotalOrderNum=a.TotalOrderNumFROM #TempLogisticsStatistics AS #tempINNER JOIN ( select info.CustomerID,TransportID, SUM(CASE WHEN info.OrderState=2 THEN (TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 end) as ShipedTotalPrice, SUM(CASE WHEN info.OrderState=2 THEN 1 ELSE 0 end) ShipedOrderNum, COUNT(info.OrderCode) TotalOrderNum from RB_OrderInfo info (nolock) left join RB_OrderSource os (nolock) on os.ID= info.OrderSourceID and os.CustomerID=info.CustomerID where info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and info.PayTime between @startDate and @endDate+' 23:59:59' AND info.CustomerID=@CustomerID and os.OrderSourceType=@OrderSourceType and info.IsStockOrder=0 group by info.TransportID,info.CustomerID) AS a ON a.CustomerID = #temp.CustomerID AND a.TransportID = #temp.TransportID;UPDATE #temp SET ComplaintsRefundOrderNum=a.ComplaintsRefundOrderNum ,ComplaintsNum=a.ComplaintsNumFROM #TempLogisticsStatistics AS #tempINNER JOIN ( SELECT f.CustomerID,f.TransportID ,COUNT(f.OrderCode) ComplaintsRefundOrderNum ,COUNT(1) ComplaintsNum FROM #OrderInfoFundReceipt AS f WHERE f.RefundCauseID2 IN(9,10,11) GROUP BY f.CustomerID,f.TransportID) AS a ON a.CustomerID = #temp.CustomerID AND a.TransportID = #temp.TransportID;UPDATE #temp SET RefundMoneyProportion =(case when TotalRefundMoney=0 then '0' else RefundMoney /TotalRefundMoney end) *100, RefundMoneyAccounted =(case when ShipedTotalPrice=0 then '1' else RefundMoney /ShipedTotalPrice end)*100, ShipedRefundOrderProportion =(case when TotalRefundOrderNum=0 then '0' else CONVERT(MONEY,RefundOrderNum)/TotalRefundOrderNum end)*100, ShipedRefundOrderAccounted =(case when ShipedOrderNum=0 then '1' else CONVERT(MONEY,RefundOrderNum)/ShipedOrderNum end)*100, ComplaintsNumProportion =(case when a.TotalOrderNum=0 then '0' else CONVERT(MONEY,ComplaintsRefundOrderNum)/a.TotalOrderNum end)*100, ComplaintsNumAccounted =(case when TotalRefundOrderNum=0 then '1' else CONVERT(MONEY,ComplaintsNum)/TotalRefundOrderNum end)*100FROM #TempLogisticsStatistics AS #tempINNER JOIN ( SELECT CustomerID,SUM(RefundMoney)TotalRefundMoney ,SUM(RefundOrderNum) TotalRefundOrderNum ,SUM(ComplaintsNum) AS TotalComplaintsNum ,SUM(TotalOrderNum) AS TotalOrderNum FROM #TempLogisticsStatistics GROUP BY CustomerID) AS a ON a.CustomerID = #temp.CustomerID;UPDATE #temp SET RefundMoneyProportion =CASE WHEN RefundMoneyProportion='0'OR RefundMoneyProportion='0.00' THEN '-' ELSE RefundMoneyProportion + '%' END, RefundMoneyAccounted =CASE WHEN RefundMoneyAccounted='0'OR RefundMoneyAccounted='0.00' THEN '-' ELSE RefundMoneyAccounted + '%' END, ShipedRefundOrderProportion =CASE WHEN ShipedRefundOrderProportion='0'OR ShipedRefundOrderProportion='0.00' THEN '-' ELSE ShipedRefundOrderProportion + '%' END, ShipedRefundOrderAccounted =CASE WHEN ShipedRefundOrderAccounted='0'OR ShipedRefundOrderAccounted='0.00' THEN '-' ELSE ShipedRefundOrderAccounted + '%' END, ComplaintsNumProportion =CASE WHEN ComplaintsNumProportion='0'OR ComplaintsNumProportion='0.00' THEN '-' ELSE ComplaintsNumProportion + '%' END, ComplaintsNumAccounted =CASE WHEN ComplaintsNumAccounted='0'OR ComplaintsNumAccounted='0.00' THEN '-' ELSE ComplaintsNumAccounted + '%' ENDFROM #TempLogisticsStatistics AS #temp;----Sheet6 按客服人员统计CREATE TABLE #TempServiceStatistics( CustomerID int, OrderSourceID int, OrderSourceName varchar(50), ServiceAdminID int, ServiceAdminName varchar(50), DepartmentName varchar(500), Unfilled MONEY DEFAULT 0, Shipped MONEY DEFAULT 0, Section MONEY DEFAULT 0, UnfilledProportion varchar(10), ShippedProportion varchar(10), SectionProportion varchar(10), UnfilledOrderNum INT DEFAULT 0, ShippedOrderNum INT DEFAULT 0, SectionOrderNum INT DEFAULT 0, UnfilledOrderNumProportion varchar(10), ShippedOrderNumProportion varchar(10), SectionOrderNumProportion varchar(10), TotalRefundMoney MONEY DEFAULT 0, TotalSaleMoney MONEY DEFAULT 0, TotalOrderNum INT DEFAULT 0, RefundOrderNum INT DEFAULT 0, totalProportion varchar(10), totalOrderNumProportion varchar(10));INSERT INTO #TempServiceStatistics( CustomerID, OrderSourceID, OrderSourceName, ServiceAdminName, DepartmentName, TotalRefundMoney, Unfilled, Shipped, Section, RefundOrderNum, UnfilledOrderNum, ShippedOrderNum, SectionOrderNum) SELECT os.CustomerID, os.ID, os.OrderSourceName, os.ServiceAdminName, rd.DepartmentName, 0, 0, 0, 0, 0, 0, 0, 0 FROM RB_OrderSource os (NOLOCK) LEFT JOIN RB_Admin ra ( NOLOCK ) ON os.ServiceAdminID = ra.ID AND os.CustomerID = ra.CustomerID LEFT JOIN RB_Department rd ( NOLOCK ) ON ra.CustomerID = rd.CustomerID AND ra.DepartmentID = rd.ID WHERE os.IsActive = 1 AND os.OrderSourceType = @OrderSourceType AND os.CustomerID = @CustomerIDUPDATE #temp SET TotalRefundMoney=b.TotalRefundMoney,Unfilled=b.Unfilled,Shipped=b.Shipped,Section=b.Section,RefundOrderNum=b.RefundOrderNum,UnfilledOrderNum=b.UnfilledOrderNum,ShippedOrderNum=b.ShippedOrderNum,SectionOrderNum=b.SectionOrderNum FROM #TempServiceStatistics #temp INNER JOIN (SELECT fr.CustomerID,fr.OrderSourceID,fr.OrderSourceName,fr.ServiceAdminName ,SUM(FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) TotalRefundMoney ,SUM(CASE WHEN fr.EbayFundType=1 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Unfilled ,SUM(CASE WHEN fr.EbayFundType=2 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Shipped ,SUM(CASE WHEN fr.EbayFundType=3 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Section ,COUNT(fr.OrderCode) RefundOrderNum ,SUM(CASE WHEN fr.EbayFundType=1 THEN 1 ELSE 0 END) UnfilledOrderNum ,SUM(CASE WHEN fr.EbayFundType=2 THEN 1 ELSE 0 END) ShippedOrderNum ,SUM(CASE WHEN fr.EbayFundType=3 THEN 1 ELSE 0 END) SectionOrderNumFROM #OrderInfoFundReceipt AS frGROUP BY fr.CustomerID,fr.OrderSourceID,fr.OrderSourceName,fr.ServiceAdminName) b ON b.CustomerID = #temp.CustomerID AND b.OrderSourceID = #temp.OrderSourceIDUPDATE #temp SET #temp.totalSaleMoney=a.totalSaleMoney,TotalOrderNum=a.TotalOrderNumFROM #TempServiceStatistics AS #tempINNER JOIN ( SELECT oi.CustomerID,oi.OrderSourceID ,sum(TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) totalSaleMoney ,COUNT(oi.OrderCode) TotalOrderNum FROM dbo.RB_OrderInfo AS oi INNER JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = oi.CustomerID AND oi.OrderSourceID=os.ID where oi.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and oi.CustomerID=@CustomerID and os.OrderSourceType=@OrderSourceType and oi.PayTime between @startDate and @endDate+' 23:59:59' and oi.IsStockOrder=0 GROUP BY oi.CustomerID,oi.OrderSourceID) AS a ON a.CustomerID = #temp.CustomerID AND a.OrderSourceID = #temp.OrderSourceID;UPDATE #temp SET UnfilledProportion=case when totalRefundMoney=0 then 0 else Unfilled/totalRefundMoney end *100, ShippedProportion=case when totalRefundMoney=0 then 0 else Shipped/totalRefundMoney end *100, SectionProportion=case when totalRefundMoney=0 then 0 else Section/totalRefundMoney end *100, totalProportion=case when totalSaleMoney=0 then 0 else totalRefundMoney/totalSaleMoney end *100, UnfilledOrderNumProportion=case when RefundOrderNum=0 then 0 ELSE CONVERT(MONEY,UnfilledOrderNum) /RefundOrderNum end *100, ShippedOrderNumProportion=case when RefundOrderNum=0 then 0 else CONVERT(MONEY,ShippedOrderNum)/RefundOrderNum end *100, SectionOrderNumProportion=case when RefundOrderNum=0 then 0 else CONVERT(MONEY,SectionOrderNum)/RefundOrderNum end *100, totalOrderNumProportion=case when TotalOrderNum=0 then 0 else CONVERT(MONEY,RefundOrderNum)/TotalOrderNum end *100FROM #TempServiceStatistics AS #temp;UPDATE #temp SET UnfilledProportion=CASE WHEN UnfilledProportion='0'OR UnfilledProportion='0.00' THEN '-' ELSE UnfilledProportion + '%' END, ShippedProportion=CASE WHEN ShippedProportion='0'OR ShippedProportion='0.00' THEN '-' ELSE ShippedProportion + '%' END, SectionProportion=CASE WHEN SectionProportion='0'OR SectionProportion='0.00' THEN '-' ELSE SectionProportion + '%' END, totalProportion=CASE WHEN totalProportion='0'OR totalProportion='0.00' THEN '-' ELSE totalProportion + '%' END, UnfilledOrderNumProportion=CASE WHEN UnfilledOrderNumProportion='0'OR UnfilledOrderNumProportion='0.00' THEN '-' ELSE UnfilledOrderNumProportion + '%' END, ShippedOrderNumProportion=CASE WHEN ShippedOrderNumProportion='0'OR ShippedOrderNumProportion='0.00' THEN '-' ELSE ShippedOrderNumProportion + '%' END, SectionOrderNumProportion=CASE WHEN SectionOrderNumProportion='0'OR SectionOrderNumProportion='0.00' THEN '-' ELSE SectionOrderNumProportion + '%' END, totalOrderNumProportion=CASE WHEN totalOrderNumProportion='0'OR totalOrderNumProportion='0.00' THEN '-' ELSE totalOrderNumProportion + '%' ENDFROM #TempServiceStatistics AS #temp;---------------------------------------------页面显示--------------Sheet1退款订单明细表SELECT a.ID,STUFF((SELECT ',' +ISNULL(frl.AddAdminName,'')+','+ISNULL(frl.SKU,'')+','+ISNULL(frl.ProductNameCN,'')+';' FROM #OrderInfoFundReceiptList AS frl WHERE frl.CustomerID=a.CustomerID AND a.id=frl.ID FOR XML PATH('')),1,1,'' )AS SKUInfo,STUFF((SELECT ',' +ISNULL(frl.BusinessAdminName,'')+';' FROM #OrderInfoFundReceiptList AS frl WHERE frl.CustomerID=a.CustomerID AND a.id=frl.ID FOR XML PATH('')),1,1,'' )AS BusinessAdminInfoINTO #refundSkuFROM #OrderInfoFundReceipt AS a;SELECT Refund.OrderSourceName 订单来源渠道,Refund.OrderCode 系统订单号,Refund.ClientOrderCode 平台订单号,list.BusinessAdminInfo as '销售人员',list.SKUInfo as '上架人员/SKU/中文名',Refund.Currency 币种,Refund.ProductTotalPrice 售价,Refund.TransportPay 运费,Refund.ServiceAdminName 客服人员,Refund.FundMoney 退款金额,CASE Refund.EbayFundType WHEN 1 THEN '未发货退款' WHEN 2 THEN '已发货退款' WHEN 3 THEN '已发货部分退款' ELSE '未操作' END '退款类型',ISNULL(Refund.RefundCauseName1,Refund.RefundCauseName2) AS 一级退款原因,ISNULL(Refund.RefundCauseName2,'') AS 二级退款原因,CASE WHEN Refund.OrderState=2 THEN '是' ELSE '否' END 是否发货,CASE Refund.FundHandleType WHEN 1 THEN '未退货' WHEN 2 THEN '退货上架' WHEN 3 THEN '退货退回' ELSE '无' END AS 货物处理情况,ISNULL(wh.WareHouseName,'') 发货仓库,ec.Country_cn 国家,ISNULL(rt.TransportName,'') 物流,ISNULL(opa.DeliveredDays,0) 妥投天数,Refund.PayTime 下单时间,Refund.ShipTime 发货时间,Refund.RefundTime 退款时间 FROM #OrderInfoFundReceipt Refund (nolock)LEFT JOIN dbo.E_Country AS ec(NOLOCK) ON ec.Shorthand=Refund.CountryLEFT JOIN dbo.RB_Transport AS rt(NOLOCK) ON rt.CustomerID = Refund.CustomerID AND rt.ID = Refund.TransportIDLEFT JOIN dbo.RB_Order_Package_ApiShipTrack AS opa(NOLOCK) ON opa.CustomerID = Refund.CustomerID AND opa.OrderCode=Refund.OrderCodeLEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON wh.CustomerID = opa.CustomerID AND Refund.WareHouseID=wh.IDLEFT JOIN #refundSku AS list ON Refund.ID=list.id where Refund.CustomerID=@CustomerID;------------Sheet2按上架人员SKU统计 SELECT BusinessTeamName 销售小组,OrderSourceName 订单来源渠道,AddAdminName 上架人员,SKU 产品SKU,ProductNameCN 产品中文名 ,ReturnMoney 退款总金额,SaleMoney 销售总金额,ReturnMoneyProportion 退款金额占比 ,SaleProductNum 销售个数,ReturnProductNum 退款个数,ReturnNumProportion 退款个数占比 ,SaleOrderNum 总订单数,ReturnOrderNum 总退单数,ReturnOrderNumProportion 退款订单数占比 FROM #TempProductStatistics;-----------Sheet3: 按账号仓库角度统计SELECT OrderSourceName 来源渠道账号,WareHouseName 仓库 ,ShipedOrderCodeNum 已发货订单数,TotalRefundOrderNum 退款订单数,RefundOrderAccounted 退款订单数占比,RefundOrderProportion 退款订单数比例 ,ShipedTotalPrice 已发货金额,TotalRefundMoney 退款金额,RefundMoneyAccounted 退款金额占比,RefundMoneyProportion 退款金额比例FROM #TempWareHouseStatistics;---------------Sheet4:退款原因角度统计SELECT RefundCauseName1 一级退款原因,RefundCauseName2 二级退款原因,BenWareHouseFundMoney 本地仓发货退款金额,HaiWareHouseFundMoney 海外仓发货退款金额,RefundCauseProportion 退款原因所占比例,FundMoney 退款金额,FundMoneyProportion 退款金额比例,OrderCodeNum 退款订单数,ComplaintsNum 投诉次数FROM #TempRefundReasonStatistics;----------------Sheet5：物流渠道角度统计SELECT TransportName 物流渠道,RefundMoney 退款金额 ,ShipedTotalPrice 发货订单总金额,RefundMoneyAccounted 退款金额占比,RefundMoneyProportion 退款额比例 ,ShipedOrderNum 发货订单数,ShipedRefundOrderNum 已发货退款订单数,ShipedRefundOrderProportion 退款订单数比例,ShipedRefundOrderAccounted 退款订单数占比 ,ComplaintsNum 客户投诉次数,ComplaintsNumAccounted 订单投诉比例,ComplaintsNumProportion 订单投诉占比FROM #TempLogisticsStatistics;----------------Sheet6 按客服人员统计SELECT OrderSourceName 订单帐号,ServiceAdminName 客服人员,DepartmentName 部门 ,Unfilled 未发货退款,Shipped 已发货退全款,Section 已发货部分退款 ,UnfilledProportion 未发货退款比例,ShippedProportion 已发货退全款比例,SectionProportion 已发货部分退款比例 ,TotalRefundMoney 退款总金额,TotalSaleMoney 销售总额,totalProportion 退款总比例 ,UnfilledOrderNum 未发货退款订单数,ShippedOrderNum 已发货退全款订单数,SectionOrderNum 已发货部分退款退单数 ,UnfilledOrderNumProportion 未发货退款订单数比例,ShippedOrderNumProportion 已发货退全款订单数比例,SectionOrderNumProportion 已发货部分退款订单数比 ,RefundOrderNum 退款订单数量,TotalOrderNum 销售订单数量,totalOrderNumProportion 退款订单总比例FROM #TempServiceStatistics;------------报表说明SELECT '按上架人员SKU统计' AS 统计方式,'退款金额占比：退款总金额/销售总金额' 说明UNION ALLSELECT '' AS 统计方式,'退款个数占比：退款个数/销售个数' 说明UNION ALLSELECT '' AS 统计方式,'退款订单数占比：总退单数/总订单数' 说明UNION ALLSELECT '按账号仓库角度统计' AS 统计方式,'退款订单占比：此仓库退款订单/此仓库发货订单' 说明UNION ALLSELECT '' AS 统计方式,'退款订单比例：此仓库退款订单数/所有仓库退款订单数' 说明UNION ALLSELECT '' AS 统计方式,'退款金额占比：退款金额/已发货金额' 说明UNION ALLSELECT '' AS 统计方式,'退款金额比例：此仓库退款金额/所有仓库退款金额' 说明UNION ALLSELECT '退款原因角度统计' AS 统计方式,'退款金额比例 ：此退款原因退款金额/退款总金额' 说明UNION ALLSELECT '' AS 统计方式,'退款原因投诉比例：此原因投诉次数/总投诉次数' 说明UNION ALLSELECT '物理渠道角度统计' AS 统计方式,'退款额占比：此物流退款金额/此物流发货总金额' 说明UNION ALLSELECT '' AS 统计方式,'退款金额比例：此物流退款金额/总物流退款金额' 说明UNION ALLSELECT '' AS 统计方式,'退款订单占比:此物流退款订单数/此物流发货总订单数' 说明UNION ALLSELECT '' AS 统计方式,'退款订单数比例：此物流退款订单数/总物流退款订单数' 说明UNION ALLSELECT '' AS 统计方式,'订单投诉占比: ( 海关扣货 、包裹丢失、运输损坏)该物流的投诉次数/所有物流的投诉次数 * 100%' 说明UNION ALLSELECT '' AS 统计方式,'订单投诉比例: （海关扣货 、包裹丢失、运输损坏）该物流被投诉的订单数/该物流的所有订单数 * 100%' 说明;if Object_id('Tempdb..#orderinfo') IS NOT NULLDROP TABLE #orderinfo;IF OBJECT_ID('Tempdb..#orderinfo2') IS NOT NULLDROP TABLE #orderinfo2;DROP TABLE #refundSku;DROP TABLE #OrderInfoFundReceipt;DROP TABLE #OrderInfoFundReceiptList;DROP TABLE #TempProductStatistics;DROP TABLE #TempWareHouseStatistics;DROP TABLE #TempRefundReasonStatistics;DROP TABLE #TempLogisticsStatistics;DROP TABLE #TempServiceStatistics;
006-
007- 在自定义报告导出页面选择刚刚配置的【Cdiscount平台订单退款分析报告】，一直在加载中
008-
009-
010-
011- [结果]
012-
013-
014-
015- [期望]
016- 可以正常导出

001- <p>[步骤]</p>
001+
002- <p>1.在测试环境自定义报告配置中配置内容如下：</p>
003- <p><img src="{194499.png}" alt="" /></p>
004- <p><span style="color:#E53333;">配置的sql：</span></p>
005- <p>DECLARE @OrderSourceType int=35<br />CREATE TABLE #OrderInfoFundReceipt<br />(<br /> ID INT,<br /> AudiState INT,<br /> Currency VARCHAR(50),<br /> FundMoney MONEY,<br /> EbayFundType INT,<br /> CustomerID INT,<br /> ReasonTypes VARCHAR(100),<br /> FundHandleType INT,<br /> OrderCode VARCHAR(50),<br /> ClientOrderCode VARCHAR(50),<br /> OrderSourceID INT,<br /> Country VARCHAR(50),<br /> ProductTotalPrice MONEY,<br /> TransportPay MONEY,<br /> ServiceAdminName VARCHAR(50),<br /> OrderState INT,<br /> TransportID INT,<br /> ExchangeUSD MONEY,<br /> IsFBAOrder BIT,<br /> OrderSourceType INT,<br /> OrderSourceName VARCHAR(50),<br /> RefundTime DATETIME,<br /> ShipTime DATETIME,<br /> PayTime DATETIME,<br /> WareHouseID INT,<br /> TotalPrice MONEY,<br /> RefundCauseID1 INT,<br /> RefundCauseID2 INT,<br /> RefundCauseName1 VARCHAR(200),<br /> RefundCauseName2 VARCHAR(200)<br />);<br />----------------退款数据<br />IF @type=1<br /><br />INSERT INTO #OrderInfoFundReceipt<br /> (<br /> ID,<br /> AudiState,<br /> Currency,<br /> FundMoney,<br /> EbayFundType,<br /> CustomerID,<br /> ReasonTypes,<br /> RefundTime,<br /> FundHandleType,<br /> OrderCode,<br /> ClientOrderCode,<br /> OrderSourceID,<br /> Country,<br /> ProductTotalPrice,<br /> TransportPay,<br /> ServiceAdminName,<br /> OrderState,<br /> TransportID,<br /> ExchangeUSD,<br /> OrderSourceType,<br /> OrderSourceName,<br /> ShipTime,<br /> PayTime,<br /> IsFBAOrder,<br /> WareHouseID,<br /> TotalPrice<br /> )<br /> SELECT Refund.ID,Refund.AudiState,Refund.Currency,Refund.FundMoney,Refund.EbayFundType,Refund.CustomerID,Refund.ReasonTypes,Refund.AddTime,Refund.FundHandleType,<br /> info.OrderCode,info.ClientOrderCode,info.OrderSourceID,info.Country,info.ProductTotalPrice,info.TransportPay,os.ServiceAdminName,info.OrderState<br /> ,info.TransportID,info.ExchangeUSD,os.OrderSourceType,os.OrderSourceName,info.ShipTime,info.PayTime,info.IsFBAOrder,info.WareHouseID,info.TotalPrice<br /> FROM RB_OrderInfo_FundReceipt Refund (nolock)<br /> inner join RB_OrderInfo info (nolock) on Refund.OrderCode=info.OrderCode and Refund.CustomerID=info.CustomerID<br /> LEFT JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = info.CustomerID AND info.OrderSourceID=os.ID<br /> where Refund.AudiState!=3 AND Refund.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType<br /> AND info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' <br /> and Refund.AddTime between @startDate and @endDate+' 23:59:59'<br /> and info.IsStockOrder = 0; <br /><br />ELSE<br /><br />INSERT INTO #OrderInfoFundReceipt<br /> (<br /> ID,<br /> AudiState,<br /> Currency,<br /> FundMoney,<br /> EbayFundType,<br /> CustomerID,<br /> ReasonTypes,<br /> RefundTime,<br /> FundHandleType,<br /> OrderCode,<br /> ClientOrderCode,<br /> OrderSourceID,<br /> Country,<br /> ProductTotalPrice,<br /> TransportPay,<br /> ServiceAdminName,<br /> OrderState,<br /> TransportID,<br /> ExchangeUSD,<br /> OrderSourceType,<br /> OrderSourceName,<br /> ShipTime,<br /> PayTime,<br /> IsFBAOrder,<br /> WareHouseID,<br /> TotalPrice<br /> )<br /> SELECT Refund.ID,Refund.AudiState,Refund.Currency,Refund.FundMoney,Refund.EbayFundType,Refund.CustomerID,Refund.ReasonTypes,Refund.AddTime,Refund.FundHandleType,<br /> info.OrderCode,info.ClientOrderCode,info.OrderSourceID,info.Country,info.ProductTotalPrice,info.TransportPay,os.ServiceAdminName,info.OrderState<br /> ,info.TransportID,info.ExchangeUSD,os.OrderSourceType,OrderSourceName,info.ShipTime,info.PayTime,info.IsFBAOrder,info.WareHouseID,info.TotalPrice<br /> FROM RB_OrderInfo_FundReceipt Refund (nolock)<br /> inner join RB_OrderInfo info (nolock) on Refund.OrderCode=info.OrderCode and Refund.CustomerID=info.CustomerID<br /> LEFT JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = info.CustomerID AND info.OrderSourceID=os.ID<br /> where Refund.AudiState!=3 AND Refund.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType<br /> AND info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' <br /> and info.PayTime between @startDate and @endDate+' 23:59:59'<br /> and info.IsStockOrder=0; <br /><br /><br />UPDATE #temp set RefundCauseID1=rc1.CauseID,RefundCauseName1=rc1.CauseName, RefundCauseID2=ISNULL(rc2.CauseID,0),RefundCauseName2=ISNULL(rc2.CauseName,'其它原因')<br />FROM #OrderInfoFundReceipt #temp <br /> LEFT JOIN RB_OrderInfo_RefundCause AS rc2 ON rc2.CustomerID = #temp.CustomerID AND rc2.CauseID IN(select columnName from dbo.fn_StrToTable(#temp.ReasonTypes) where columnName &lt;&gt; '')<br /> LEFT JOIN RB_OrderInfo_RefundCause AS rc1 ON rc1.CustomerID = #temp.CustomerID AND rc2.ParentID=rc1.CauseID;<br /><br />--明细数据<br />SELECT Refund.ID,Refund.CustomerID,Refund.OrderCode,ISNULL(oss.AddAdminName,'') AddAdminName,fp.ProductSKU AS SKU,p.ProductNameCN,ol.SellerSKU,fp.ProductPrice,fp.ProductNum,(ol.ProductNum + ol.[ProductNumUpdate]) AS OrderProductNum,fp.OrderListID<br />,ol.BusinessAdminID,ISNULL(ol.BusinessAdminName,'') BusinessAdminName,ol.BusinessTeamID,ISNULL(ol.BusinessTeamName,'') BusinessTeamName<br />INTO #OrderInfoFundReceiptList<br />FROM #OrderInfoFundReceipt Refund (nolock)<br />inner join RB_Fund_Products fp (nolock) on Refund.ID=fp.ReceiptID and Refund.CustomerID=fp.CustomerID<br />INNER JOIN dbo.RB_OrderList AS ol (NOLOCK) ON ol.CustomerID = Refund.CustomerID AND ol.OrderCode = Refund.OrderCode AND fp.OrderListID=ol.ID<br />LEFT JOIN dbo.RB_Product AS p(NOLOCK) ON p.CustomerID = fp.CustomerID AND p.SKU = ol.SKU<br />LEFT JOIN RB_Product_OrderSourceSKU AS oss(NOLOCK) ON oss.CustomerID = fp.CustomerID AND oss.OrderSourceSKU=ol.SellerSKU<br />where Refund.CustomerID=@CustomerID;<br /><br /><br />---------Sheet2<br />CREATE TABLE #TempProductStatistics(<br />CustomerID INT,<br />BusinessTeamName varchar(50),<br />OrderSourceName varchar(50),<br />AddAdminName varchar(50),<br />SKU varchar(50),<br />ProductNameCN varchar(1000),<br />OrderSourceID INT,<br />ReturnProductNum INT DEFAULT 0,<br />SaleProductNum INT DEFAULT 0,<br />ReturnNumProportion varchar(10), <br />ReturnMoney MONEY DEFAULT 0,<br />SaleMoney MONEY DEFAULT 0, <br />ReturnMoneyProportion varchar(10), <br />ReturnOrderNum INT DEFAULT 0,<br />SaleOrderNum INT DEFAULT 0,<br />ReturnOrderNumProportion varchar(20),<br />FBAReturnMoney MONEY DEFAULT 0,<br />FBASaleMoney MONEY DEFAULT 0, <br />FBAReturnMoneyProportion varchar(10),<br />NoFBAReturnMoney MONEY DEFAULT 0,<br />NoFBASaleMoney MONEY DEFAULT 0, <br />NoFBAReturnMoneyProportion varchar(10)<br />);<br /><br />--Sheet2（ebay,速卖通,wish版）：按上架人员SKU统计（重新整合）<br />INSERT INTO #TempProductStatistics<br />(<br /> CustomerID,<br /> BusinessTeamName,<br /> OrderSourceName,<br /> AddAdminName,<br /> SKU,<br /> ProductNameCN,<br /> OrderSourceID,<br /> ReturnProductNum,<br /> ReturnMoney,<br /> ReturnOrderNum,<br /> FBAReturnMoney,<br /> NoFBAReturnMoney<br />)<br />SELECT f.CustomerID, fp.BusinessTeamName,f.OrderSourceName,fp.AddAdminName,fp.SKU,ProductNameCN,f.OrderSourceID<br /> ,SUM(fp.ProductNum) AS ReturnProductNum<br /> ,SUM(fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ReturnMoney<br /> ,COUNT(DISTINCT f.OrderCode) ReturnOrderNum<br /> ,SUM(CASE f.IsFBAOrder WHEN 1 THEN (fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ELSE 0 END) FBAReturnMoney<br /> ,SUM(CASE f.IsFBAOrder WHEN 0 THEN (fp.ProductPrice*fp.ProductNum/(case when f.ExchangeUSD =0 then 1 else f.ExchangeUSD end)) ELSE 0 END) NoFBAReturnMoney<br />FROM #OrderInfoFundReceipt AS f<br />INNER JOIN #OrderInfoFundReceiptList AS fp ON fp.CustomerID = f.CustomerID AND fp.ID = f.ID AND fp.OrderCode = f.OrderCode<br />GROUP BY f.CustomerID,fp.BusinessTeamName,f.OrderSourceName,fp.AddAdminName,fp.SKU,ProductNameCN,f.OrderSourceID;<br /><br /> <br /> select ol.CustomerID,ol.SKU,ISNULL(ol.BusinessTeamName,'') BusinessTeamName,ol.SellerSKU,oi.OrderSourceID,<br /> SUM(ol.ProductPrice*(ol.ProductNum + ol.[ProductNumUpdate])/(case when oi.ExchangeUSD =0 then 1 else oi.ExchangeUSD end)) SaleMoney<br /> ,SUM((ol.ProductNum + ol.[ProductNumUpdate])) SaleProductNum <br /> ,COUNT(oi.OrderCode) AS SaleOrderNum<br /> INTO #orderinfo2<br /> from RB_OrderList ol (nolock)<br /> inner join RB_OrderInfo oi (nolock) on ol.OrderCode = oi.OrderCode and ol.CustomerID=oi.CustomerID<br /> left join RB_OrderSource os (nolock) ON os.ID= oi.OrderSourceID and os.CustomerID=oi.CustomerID<br /> where oi.PayTime between @startDate and @endDate+' 23:59:59' and oi.IsStockOrder=0 AND <br /> [DownLoadUpdateTime]!='2000-01-01 00:00:00.000' and ol.CustomerID=@CustomerID and os.OrderSourceType=@OrderSourceType<br /> group by ol.CustomerID,ol.SKU,ISNULL(ol.BusinessTeamName,''),ol.SellerSKU,oi.OrderSourceID;<br /><br /> UPDATE #temp SET SaleProductNum=ISNULL(a.SaleProductNum,0)<br /> ,#temp.SaleMoney=ISNULL(a.SaleMoney,0)<br /> ,#temp.SaleOrderNum=ISNULL(a.SaleOrderNum,0)<br /> FROM #TempProductStatistics AS #temp <br /> INNER JOIN (<br /> SELECT a.CustomerID,a.SKU,a.BusinessTeamName,a.OrderSourceID,ISNULL(AddAdminName,'')AddAdminName<br /> ,SUM(SaleMoney)SaleMoney,SUM(SaleProductNum) SaleProductNum,SUM(SaleOrderNum)SaleOrderNum<br /> FROM #orderinfo2 AS a<br /> LEFT JOIN RB_Product_OrderSourceSKU AS oss(NOLOCK) ON oss.CustomerID = a.CustomerID AND oss.OrderSourceSKU=a.SellerSKU<br /> GROUP BY a.CustomerID,a.SKU,a.BusinessTeamName,a.OrderSourceID,ISNULL(AddAdminName,'')<br /> ) AS a ON a.CustomerID = #temp.CustomerID AND a.BusinessTeamName = #temp.BusinessTeamName AND a.SKU = #temp.SKU AND a.AddAdminName = #temp.AddAdminName AND a.OrderSourceID = #temp.OrderSourceID;<br /> <br /><br />update #TempProductStatistics set<br />ReturnMoneyProportion=(case when SaleMoney=0 then '0' else ReturnMoney * 100 /SaleMoney end),<br />ReturnNumProportion = (case when SaleProductNum=0 then '0' else CONVERT(MONEY,ReturnProductNum)*100 /SaleProductNum end),<br />ReturnOrderNumProportion = (case when SaleOrderNum=0 then '0' else CONVERT(MONEY,ReturnOrderNum)*100 /SaleOrderNum end),<br />FBAReturnMoneyProportion = (case when FBASaleMoney=0 then '0' else FBAReturnMoney *100 /FBASaleMoney end),<br />NoFBAReturnMoneyProportion = (case when NoFBASaleMoney=0 then '0' else NoFBAReturnMoney *100 /NoFBASaleMoney end);<br /><br />update #TempProductStatistics set<br />ReturnMoneyProportion=CASE WHEN ReturnMoneyProportion='0'OR ReturnMoneyProportion='0.00' THEN '-' ELSE ReturnMoneyProportion + '%' END,<br />ReturnNumProportion =CASE WHEN ReturnNumProportion='0'OR ReturnNumProportion='0.00' THEN '-' ELSE ReturnNumProportion + '%' END,<br />ReturnOrderNumProportion = CASE WHEN ReturnOrderNumProportion='0'OR ReturnOrderNumProportion='0.00' THEN '-' ELSE ReturnOrderNumProportion + '%' END,<br />FBAReturnMoneyProportion = CASE WHEN FBAReturnMoneyProportion='0'OR FBAReturnMoneyProportion='0.00' THEN '-' ELSE FBAReturnMoneyProportion + '%' END,<br />NoFBAReturnMoneyProportion = CASE WHEN NoFBAReturnMoneyProportion='0'OR NoFBAReturnMoneyProportion='0.00' THEN '-' ELSE NoFBAReturnMoneyProportion + '%' END;<br /><br /><br /><br />----------------Sheet3: 按账号仓库角度统计<br />CREATE TABLE #TempWareHouseStatistics<br />(<br /> CustomerID INT,<br /> OrderSourceID INT,<br /> OrderSourceName varchar(50),<br /> WareHouseID INT,<br /> WareHouseName varchar(50),<br /> ShipedTotalPrice MONEY DEFAULT 0,<br /> ShipedOrderCodeNum INT DEFAULT 0,<br /> TotalRefundMoney MONEY DEFAULT 0,<br /> TotalRefundOrderNum INT DEFAULT 0,<br /> RefundMoneyProportion varchar(10),<br /> RefundMoneyAccounted varchar(10),<br /> RefundOrderAccounted varchar(10),<br /> RefundOrderProportion varchar(10)<br />);<br /> INSERT INTO #TempWareHouseStatistics<br /> (CustomerID,OrderSourceID,OrderSourceName,WareHouseID,WareHouseName,TotalRefundMoney,TotalRefundOrderNum)<br /> SELECT rp.CustomerID,rp.OrderSourceID,rp.OrderSourceName,rp.WareHouseID,wh.WareHouseName<br /> ,SUM(rp.TotalPrice/case when rp.ExchangeUSD=0 then 1 else ExchangeUSD end) AS TotalRefundMoney<br /> ,COUNT(rp.OrderCode) AS TotalRefundOrderNum<br /> FROM #OrderInfoFundReceipt AS rp<br /> LEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON wh.CustomerID = rp.CustomerID AND rp.WareHouseID=wh.ID<br /> GROUP BY rp.CustomerID,rp.OrderSourceID,rp.OrderSourceName,rp.WareHouseID,wh.WareHouseName;<br /><br /><br /> UPDATE #temp SET ShipedTotalPrice=ISNULL(a.ShipedTotalPrice,0),ShipedOrderCodeNum=ISNULL(a.ShipedOrderCodeNum,0)<br /> FROM #TempWareHouseStatistics AS #temp<br /> INNER JOIN (<br /> SELECT oi.CustomerID, oi.OrderSourceID,oi.WareHouseID<br /> ,SUM(CASE WHEN oi.OrderState=2 THEN (oi.TotalPrice/case when oi.ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) AS ShipedTotalPrice<br /> ,SUM(CASE WHEN oi.OrderState=2 THEN 1 ELSE 0 end) AS ShipedOrderCodeNum<br /> FROM RB_OrderInfo AS oi(NOLOCK) <br /> INNER JOIN dbo.RB_OrderSource AS os ON os.CustomerID = oi.CustomerID AND oi.OrderSourceID=os.ID<br /> WHERE oi.CustomerID=@CustomerID AND os.OrderSourceType=@OrderSourceType<br /> AND oi.PayTime between @startDate and @endDate+' 23:59:59'<br /> and oi.IsStockOrder=0 <br /> GROUP BY oi.CustomerID, oi.OrderSourceID,oi.WareHouseID<br /> )AS a ON a.CustomerID = #temp.CustomerID AND a.WareHouseID = #temp.WareHouseID AND a.OrderSourceID = #temp.OrderSourceID;<br /><br /><br /> UPDATE #temp SET <br /> RefundOrderAccounted=(case when ShipedOrderCodeNum=0 then '1' ELSE convert(money,#temp.TotalRefundOrderNum) /ShipedOrderCodeNum end) * 100 ,<br /> RefundMoneyAccounted = (case when ShipedTotalPrice=0 then '1' else #temp.TotalRefundMoney /ShipedTotalPrice end) * 100 ,<br /> RefundOrderProportion = (case when a.TotalRefundOrderNum=0 then '0' else convert(money,#temp.TotalRefundOrderNum) *100 /a.TotalRefundOrderNum end),<br /> RefundMoneyProportion = (case when a.TotalRefundMoney=0 then '0' else #temp.TotalRefundMoney *100 /a.TotalRefundMoney end)<br /> FROM #TempWareHouseStatistics AS #temp <br /> INNER JOIN (<br /> SELECT CustomerID, SUM(TotalRefundOrderNum) TotalRefundOrderNum ,SUM(TotalRefundMoney) AS TotalRefundMoney <br /> FROM #TempWareHouseStatistics <br /> GROUP BY CustomerID<br /> ) AS a ON a.CustomerID = #temp.CustomerID;<br /><br /> UPDATE #temp SET <br /> RefundOrderAccounted=CASE WHEN RefundOrderAccounted='0'OR RefundOrderAccounted='0.00' THEN '-' ELSE RefundOrderAccounted + '%' END,<br /> RefundMoneyAccounted = CASE WHEN RefundMoneyAccounted='0'OR RefundMoneyAccounted='0.00' THEN '-' ELSE RefundMoneyAccounted + '%' END,<br /> RefundOrderProportion = CASE WHEN RefundOrderProportion='0'OR RefundOrderProportion='0.00' THEN '-' ELSE RefundOrderProportion + '%' END,<br /> RefundMoneyProportion =CASE WHEN RefundMoneyProportion='0'OR RefundMoneyProportion='0.00' THEN '-' ELSE RefundMoneyProportion + '%' END<br /> FROM #TempWareHouseStatistics AS #temp; <br /><br />---------------Sheet4:退款原因角度统计<br />SELECT ofp.CustomerID, ISNULL(ofp.RefundCauseName1,ofp.RefundCauseName2) RefundCauseName1, ofp.RefundCauseName2<br />,SUM(CASE WHEN wh.WareHouseType=1 THEN (ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) ELSE 0 END) BenWareHouseFundMoney<br />,SUM(CASE WHEN wh.WareHouseType&lt;&gt;1 THEN (ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) ELSE 0 END) HaiWareHouseFundMoney<br />,COUNT(1) ComplaintsNum<br />,CONVERT(varchar(10),'') RefundCauseProportion<br />,SUM(ofp.FundMoney/ case when ofp.ExchangeUSD=0 then 1 else ofp.ExchangeUSD end) FundMoney<br />,COUNT( ofp.OrderCode) OrderCodeNum<br />,CONVERT(varchar(10),'') AS FundMoneyProportion<br />INTO #TempRefundReasonStatistics<br />FROM #OrderInfoFundReceipt AS ofp<br />LEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON ofp.WareHouseID=wh.ID AND wh.CustomerID = ofp.CustomerID<br />GROUP BY ofp.CustomerID,ISNULL(ofp.RefundCauseName1,ofp.RefundCauseName2),ofp.RefundCauseName2;<br /><br />UPDATE #temp <br /> SET RefundCauseProportion = (case when TotalComplaintsNum=0 then '0' else convert(money,ComplaintsNum) * 100 /TotalComplaintsNum end),<br /> FundMoneyProportion = (case when TotalFundMoney=0 then '0' else FundMoney * 100 /TotalFundMoney end)<br />FROM #TempRefundReasonStatistics AS #temp<br />INNER JOIN (<br /> SELECT CustomerID, SUM(ComplaintsNum) TotalComplaintsNum,SUM(FundMoney) TotalFundMoney <br /> FROM #TempRefundReasonStatistics GROUP BY CustomerID<br />)AS a ON a.CustomerID = #temp.CustomerID;<br /><br />UPDATE #temp <br /> SET RefundCauseProportion =CASE WHEN RefundCauseProportion='0'OR RefundCauseProportion='0.00' THEN '-' ELSE RefundCauseProportion + '%' END,<br /> FundMoneyProportion =CASE WHEN FundMoneyProportion='0'OR FundMoneyProportion='0.00' THEN '-' ELSE FundMoneyProportion + '%' END<br />FROM #TempRefundReasonStatistics AS #temp;<br /><br /><br />----------------Sheet5：物流渠道角度统计<br />CREATE TABLE #TempLogisticsStatistics(<br /> id [int] PRIMARY KEY NOT NULL identity(1,1),<br /> CustomerID int,<br /> TransportID int,<br /> TotalOrderNum INT DEFAULT 0,<br /> TransportName varchar(300) ,<br /> RefundMoney money DEFAULT 0,<br /> ShipedTotalPrice money DEFAULT 0,<br /> RefundMoneyProportion varchar(100) ,<br /> RefundMoneyAccounted varchar(100) ,<br /> ComplaintsNum int DEFAULT 0,<br /> ComplaintsNumProportion varchar(100),<br /> ComplaintsNumAccounted varchar(100),<br /> ShipedOrderNum INT DEFAULT 0,<br /> ShipedRefundOrderNum INT DEFAULT 0,<br /> RefundOrderNum INT DEFAULT 0,<br /> ComplaintsRefundOrderNum INT DEFAULT 0,<br /> ShipedRefundOrderProportion varchar(100),<br /> ShipedRefundOrderAccounted varchar(100)<br /> );<br /><br />INSERT INTO #TempLogisticsStatistics(CustomerID,TransportID,TransportName,RefundMoney,ShipedRefundOrderNum,RefundOrderNum)<br />SELECT f.CustomerID,f.TransportID,ISNULL(t.TransportName,'')<br /> ,SUM(TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) as RefundMoney<br /> ,SUM(CASE WHEN f.OrderState=2 THEN 1 ELSE 0 end) AS ShipedRefundOrderNum<br /> ,COUNT(f.OrderCode) RefundOrderNum<br /> FROM #OrderInfoFundReceipt AS f<br /> LEFT JOIN RB_Transport t (nolock) on f.TransportID=t.ID and f.CustomerID=t.CustomerID <br /> GROUP BY f.CustomerID,f.TransportID,ISNULL(t.TransportName,'');<br /><br />UPDATE #temp SET ShipedTotalPrice=a.ShipedTotalPrice,ShipedOrderNum=a.ShipedOrderNum<br /> ,TotalOrderNum=a.TotalOrderNum<br />FROM #TempLogisticsStatistics AS #temp<br />INNER JOIN (<br /> select info.CustomerID,TransportID,<br /> SUM(CASE WHEN info.OrderState=2 THEN (TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 end) as ShipedTotalPrice,<br /> SUM(CASE WHEN info.OrderState=2 THEN 1 ELSE 0 end) ShipedOrderNum,<br /> COUNT(info.OrderCode) TotalOrderNum<br /> from RB_OrderInfo info (nolock)<br /> left join RB_OrderSource os (nolock) on os.ID= info.OrderSourceID and os.CustomerID=info.CustomerID<br /> where info.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000'<br /> and info.PayTime between @startDate and @endDate+' 23:59:59' <br /> AND info.CustomerID=@CustomerID<br /> and os.OrderSourceType=@OrderSourceType<br /> and info.IsStockOrder=0 <br /> group by info.TransportID,info.CustomerID<br />) AS a ON a.CustomerID = #temp.CustomerID AND a.TransportID = #temp.TransportID;<br /><br />UPDATE #temp SET ComplaintsRefundOrderNum=a.ComplaintsRefundOrderNum<br /> ,ComplaintsNum=a.ComplaintsNum<br />FROM #TempLogisticsStatistics AS #temp<br />INNER JOIN (<br /> SELECT f.CustomerID,f.TransportID<br /> ,COUNT(f.OrderCode) ComplaintsRefundOrderNum<br /> ,COUNT(1) ComplaintsNum<br /> FROM #OrderInfoFundReceipt AS f<br /> WHERE f.RefundCauseID2 IN(9,10,11)<br /> GROUP BY f.CustomerID,f.TransportID<br />) AS a ON a.CustomerID = #temp.CustomerID AND a.TransportID = #temp.TransportID;<br /><br /><br />UPDATE #temp SET <br /> RefundMoneyProportion =(case when TotalRefundMoney=0 then '0' else RefundMoney /TotalRefundMoney end) *100,<br /> RefundMoneyAccounted =(case when ShipedTotalPrice=0 then '1' else RefundMoney /ShipedTotalPrice end)*100,<br /> ShipedRefundOrderProportion =(case when TotalRefundOrderNum=0 then '0' else CONVERT(MONEY,RefundOrderNum)/TotalRefundOrderNum end)*100,<br /> ShipedRefundOrderAccounted =(case when ShipedOrderNum=0 then '1' else CONVERT(MONEY,RefundOrderNum)/ShipedOrderNum end)*100,<br /> ComplaintsNumProportion =(case when a.TotalOrderNum=0 then '0' else CONVERT(MONEY,ComplaintsRefundOrderNum)/a.TotalOrderNum end)*100,<br /> ComplaintsNumAccounted =(case when TotalRefundOrderNum=0 then '1' else CONVERT(MONEY,ComplaintsNum)/TotalRefundOrderNum end)*100<br />FROM #TempLogisticsStatistics AS #temp<br />INNER JOIN (<br /> SELECT CustomerID,SUM(RefundMoney)TotalRefundMoney<br /> ,SUM(RefundOrderNum) TotalRefundOrderNum<br /> ,SUM(ComplaintsNum) AS TotalComplaintsNum<br /> ,SUM(TotalOrderNum) AS TotalOrderNum<br /> FROM #TempLogisticsStatistics GROUP BY CustomerID<br />) AS a ON a.CustomerID = #temp.CustomerID;<br /><br />UPDATE #temp SET <br /> RefundMoneyProportion =CASE WHEN RefundMoneyProportion='0'OR RefundMoneyProportion='0.00' THEN '-' ELSE RefundMoneyProportion + '%' END,<br /> RefundMoneyAccounted =CASE WHEN RefundMoneyAccounted='0'OR RefundMoneyAccounted='0.00' THEN '-' ELSE RefundMoneyAccounted + '%' END,<br /> ShipedRefundOrderProportion =CASE WHEN ShipedRefundOrderProportion='0'OR ShipedRefundOrderProportion='0.00' THEN '-' ELSE ShipedRefundOrderProportion + '%' END,<br /> ShipedRefundOrderAccounted =CASE WHEN ShipedRefundOrderAccounted='0'OR ShipedRefundOrderAccounted='0.00' THEN '-' ELSE ShipedRefundOrderAccounted + '%' END,<br /> ComplaintsNumProportion =CASE WHEN ComplaintsNumProportion='0'OR ComplaintsNumProportion='0.00' THEN '-' ELSE ComplaintsNumProportion + '%' END,<br /> ComplaintsNumAccounted =CASE WHEN ComplaintsNumAccounted='0'OR ComplaintsNumAccounted='0.00' THEN '-' ELSE ComplaintsNumAccounted + '%' END<br />FROM #TempLogisticsStatistics AS #temp;<br /><br />----Sheet6 按客服人员统计<br />CREATE TABLE #TempServiceStatistics(<br /> CustomerID int,<br /> OrderSourceID int,<br /> OrderSourceName varchar(50),<br /> ServiceAdminID int,<br /> ServiceAdminName varchar(50),<br /> DepartmentName varchar(500),<br /> Unfilled MONEY DEFAULT 0,<br /> Shipped MONEY DEFAULT 0,<br /> Section MONEY DEFAULT 0,<br /> UnfilledProportion varchar(10),<br /> ShippedProportion varchar(10),<br /> SectionProportion varchar(10),<br /> UnfilledOrderNum INT DEFAULT 0,<br /> ShippedOrderNum INT DEFAULT 0,<br /> SectionOrderNum INT DEFAULT 0,<br /> UnfilledOrderNumProportion varchar(10),<br /> ShippedOrderNumProportion varchar(10),<br /> SectionOrderNumProportion varchar(10),<br /> TotalRefundMoney MONEY DEFAULT 0,<br /> TotalSaleMoney MONEY DEFAULT 0,<br /> TotalOrderNum INT DEFAULT 0,<br /> RefundOrderNum INT DEFAULT 0,<br /> totalProportion varchar(10),<br /> totalOrderNumProportion varchar(10)<br />);<br /><br />INSERT INTO #TempServiceStatistics<br />(<br /> CustomerID,<br /> OrderSourceID,<br /> OrderSourceName,<br /> ServiceAdminName,<br /> DepartmentName,<br /> TotalRefundMoney,<br /> Unfilled,<br /> Shipped,<br /> Section,<br /> RefundOrderNum,<br /> UnfilledOrderNum,<br /> ShippedOrderNum,<br /> SectionOrderNum<br />)<br /> SELECT os.CustomerID,<br /> os.ID,<br /> os.OrderSourceName,<br /> os.ServiceAdminName,<br /> rd.DepartmentName,<br /> 0,<br /> 0,<br /> 0,<br /> 0,<br /> 0,<br /> 0,<br /> 0,<br /> 0<br /> FROM RB_OrderSource os (NOLOCK)<br /> LEFT JOIN RB_Admin ra ( NOLOCK )<br /> ON os.ServiceAdminID = ra.ID<br /> AND os.CustomerID = ra.CustomerID<br /> LEFT JOIN RB_Department rd ( NOLOCK )<br /> ON ra.CustomerID = rd.CustomerID<br /> AND ra.DepartmentID = rd.ID<br /> WHERE os.IsActive = 1<br /> AND os.OrderSourceType = @OrderSourceType<br /> AND os.CustomerID = @CustomerID<br /><br />UPDATE #temp SET TotalRefundMoney=b.TotalRefundMoney,Unfilled=b.Unfilled,Shipped=b.Shipped,Section=b.Section,<br />RefundOrderNum=b.RefundOrderNum,UnfilledOrderNum=b.UnfilledOrderNum,ShippedOrderNum=b.ShippedOrderNum,<br />SectionOrderNum=b.SectionOrderNum FROM #TempServiceStatistics #temp <br />INNER JOIN <br />(SELECT fr.CustomerID,fr.OrderSourceID,fr.OrderSourceName,fr.ServiceAdminName<br /> ,SUM(FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) TotalRefundMoney<br /> ,SUM(CASE WHEN fr.EbayFundType=1 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Unfilled<br /> ,SUM(CASE WHEN fr.EbayFundType=2 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Shipped<br /> ,SUM(CASE WHEN fr.EbayFundType=3 THEN (FundMoney/case when ExchangeUSD=0 then 1 else ExchangeUSD end) ELSE 0 END) Section<br /> ,COUNT(fr.OrderCode) RefundOrderNum<br /> ,SUM(CASE WHEN fr.EbayFundType=1 THEN 1 ELSE 0 END) UnfilledOrderNum<br /> ,SUM(CASE WHEN fr.EbayFundType=2 THEN 1 ELSE 0 END) ShippedOrderNum<br /> ,SUM(CASE WHEN fr.EbayFundType=3 THEN 1 ELSE 0 END) SectionOrderNum<br />FROM #OrderInfoFundReceipt AS fr<br />GROUP BY fr.CustomerID,fr.OrderSourceID,fr.OrderSourceName,fr.ServiceAdminName) b ON b.CustomerID = #temp.CustomerID AND b.OrderSourceID = #temp.OrderSourceID<br /><br />UPDATE #temp SET #temp.totalSaleMoney=a.totalSaleMoney,TotalOrderNum=a.TotalOrderNum<br />FROM #TempServiceStatistics AS #temp<br />INNER JOIN (<br /> SELECT oi.CustomerID,oi.OrderSourceID<br /> ,sum(TotalPrice/case when ExchangeUSD=0 then 1 else ExchangeUSD end) totalSaleMoney<br /> ,COUNT(oi.OrderCode) TotalOrderNum<br /> FROM dbo.RB_OrderInfo AS oi<br /> INNER JOIN dbo.RB_OrderSource AS os (NOLOCK) ON os.CustomerID = oi.CustomerID AND oi.OrderSourceID=os.ID<br /> where oi.[DownLoadUpdateTime]!='2000-01-01 00:00:00.000' <br /> and oi.CustomerID=@CustomerID<br /> and os.OrderSourceType=@OrderSourceType<br /> and oi.PayTime between @startDate and @endDate+' 23:59:59'<br /> and oi.IsStockOrder=0 <br /> GROUP BY oi.CustomerID,oi.OrderSourceID<br />) AS a ON a.CustomerID = #temp.CustomerID AND a.OrderSourceID = #temp.OrderSourceID;<br /><br /><br />UPDATE #temp SET <br /> UnfilledProportion=case when totalRefundMoney=0 then 0 else Unfilled/totalRefundMoney end *100,<br /> ShippedProportion=case when totalRefundMoney=0 then 0 else Shipped/totalRefundMoney end *100,<br /> SectionProportion=case when totalRefundMoney=0 then 0 else Section/totalRefundMoney end *100,<br /> totalProportion=case when totalSaleMoney=0 then 0 else totalRefundMoney/totalSaleMoney end *100,<br /> UnfilledOrderNumProportion=case when RefundOrderNum=0 then 0 ELSE CONVERT(MONEY,UnfilledOrderNum) /RefundOrderNum end *100,<br /> ShippedOrderNumProportion=case when RefundOrderNum=0 then 0 else CONVERT(MONEY,ShippedOrderNum)/RefundOrderNum end *100,<br /> SectionOrderNumProportion=case when RefundOrderNum=0 then 0 else CONVERT(MONEY,SectionOrderNum)/RefundOrderNum end *100,<br /> totalOrderNumProportion=case when TotalOrderNum=0 then 0 else CONVERT(MONEY,RefundOrderNum)/TotalOrderNum end *100<br />FROM #TempServiceStatistics AS #temp;<br /><br />UPDATE #temp SET <br /> UnfilledProportion=CASE WHEN UnfilledProportion='0'OR UnfilledProportion='0.00' THEN '-' ELSE UnfilledProportion + '%' END,<br /> ShippedProportion=CASE WHEN ShippedProportion='0'OR ShippedProportion='0.00' THEN '-' ELSE ShippedProportion + '%' END,<br /> SectionProportion=CASE WHEN SectionProportion='0'OR SectionProportion='0.00' THEN '-' ELSE SectionProportion + '%' END,<br /> totalProportion=CASE WHEN totalProportion='0'OR totalProportion='0.00' THEN '-' ELSE totalProportion + '%' END,<br /> UnfilledOrderNumProportion=CASE WHEN UnfilledOrderNumProportion='0'OR UnfilledOrderNumProportion='0.00' THEN '-' ELSE UnfilledOrderNumProportion + '%' END,<br /> ShippedOrderNumProportion=CASE WHEN ShippedOrderNumProportion='0'OR ShippedOrderNumProportion='0.00' THEN '-' ELSE ShippedOrderNumProportion + '%' END,<br /> SectionOrderNumProportion=CASE WHEN SectionOrderNumProportion='0'OR SectionOrderNumProportion='0.00' THEN '-' ELSE SectionOrderNumProportion + '%' END,<br /> totalOrderNumProportion=CASE WHEN totalOrderNumProportion='0'OR totalOrderNumProportion='0.00' THEN '-' ELSE totalOrderNumProportion + '%' END<br />FROM #TempServiceStatistics AS #temp;<br /><br />---------------------------------------------页面显示<br />--------------Sheet1退款订单明细表<br />SELECT a.ID,STUFF((SELECT ',' +ISNULL(frl.AddAdminName,'')+','+ISNULL(frl.SKU,'')+','+ISNULL(frl.ProductNameCN,'')+';' FROM #OrderInfoFundReceiptList AS frl WHERE frl.CustomerID=a.CustomerID AND a.id=frl.ID FOR XML PATH('')),1,1,'' )AS SKUInfo<br />,STUFF((SELECT ',' +ISNULL(frl.BusinessAdminName,'')+';' FROM #OrderInfoFundReceiptList AS frl WHERE frl.CustomerID=a.CustomerID AND a.id=frl.ID FOR XML PATH('')),1,1,'' )AS BusinessAdminInfo<br />INTO #refundSku<br />FROM #OrderInfoFundReceipt AS a;<br /><br />SELECT Refund.OrderSourceName 订单来源渠道,Refund.OrderCode 系统订单号,Refund.ClientOrderCode 平台订单号<br />,list.BusinessAdminInfo as '销售人员'<br />,list.SKUInfo as '上架人员/SKU/中文名'<br />,Refund.Currency 币种,Refund.ProductTotalPrice 售价,Refund.TransportPay 运费,Refund.ServiceAdminName 客服人员<br />,Refund.FundMoney 退款金额<br />,CASE Refund.EbayFundType WHEN 1 THEN '未发货退款' WHEN 2 THEN '已发货退款' WHEN 3 THEN '已发货部分退款' ELSE '未操作' END '退款类型',<br />ISNULL(Refund.RefundCauseName1,Refund.RefundCauseName2) AS 一级退款原因,<br />ISNULL(Refund.RefundCauseName2,'') AS 二级退款原因,<br />CASE WHEN Refund.OrderState=2 THEN '是' ELSE '否' END 是否发货,<br />CASE Refund.FundHandleType WHEN 1 THEN '未退货' WHEN 2 THEN '退货上架' WHEN 3 THEN '退货退回' ELSE '无' END AS 货物处理情况,<br />ISNULL(wh.WareHouseName,'') 发货仓库,<br />ec.Country_cn 国家,<br />ISNULL(rt.TransportName,'') 物流,<br />ISNULL(opa.DeliveredDays,0) 妥投天数,<br />Refund.PayTime 下单时间,<br />Refund.ShipTime 发货时间,<br />Refund.RefundTime 退款时间 <br />FROM #OrderInfoFundReceipt Refund (nolock)<br />LEFT JOIN dbo.E_Country AS ec(NOLOCK) ON ec.Shorthand=Refund.Country<br />LEFT JOIN dbo.RB_Transport AS rt(NOLOCK) ON rt.CustomerID = Refund.CustomerID AND rt.ID = Refund.TransportID<br />LEFT JOIN dbo.RB_Order_Package_ApiShipTrack AS opa(NOLOCK) ON opa.CustomerID = Refund.CustomerID AND opa.OrderCode=Refund.OrderCode<br />LEFT JOIN dbo.RB_WareHouse AS wh(NOLOCK) ON wh.CustomerID = opa.CustomerID AND Refund.WareHouseID=wh.ID<br />LEFT JOIN #refundSku AS list ON Refund.ID=list.id <br />where Refund.CustomerID=@CustomerID;<br /><br />------------Sheet2按上架人员SKU统计<br /><br /> SELECT BusinessTeamName 销售小组,OrderSourceName 订单来源渠道,AddAdminName 上架人员,SKU 产品SKU,ProductNameCN 产品中文名 <br /> ,ReturnMoney 退款总金额,SaleMoney 销售总金额,ReturnMoneyProportion 退款金额占比<br /> ,SaleProductNum 销售个数,ReturnProductNum 退款个数,ReturnNumProportion 退款个数占比<br /> ,SaleOrderNum 总订单数,ReturnOrderNum 总退单数,ReturnOrderNumProportion 退款订单数占比<br /> FROM #TempProductStatistics;<br /><br /><br />-----------Sheet3: 按账号仓库角度统计<br />SELECT OrderSourceName 来源渠道账号,WareHouseName 仓库<br /> ,ShipedOrderCodeNum 已发货订单数,TotalRefundOrderNum 退款订单数,RefundOrderAccounted 退款订单数占比,RefundOrderProportion 退款订单数比例<br /> ,ShipedTotalPrice 已发货金额,TotalRefundMoney 退款金额,RefundMoneyAccounted 退款金额占比,RefundMoneyProportion 退款金额比例<br />FROM #TempWareHouseStatistics;<br /><br />---------------Sheet4:退款原因角度统计<br />SELECT RefundCauseName1 一级退款原因,RefundCauseName2 二级退款原因<br />,BenWareHouseFundMoney 本地仓发货退款金额,HaiWareHouseFundMoney 海外仓发货退款金额<br />,RefundCauseProportion 退款原因所占比例<br />,FundMoney 退款金额,FundMoneyProportion 退款金额比例,OrderCodeNum 退款订单数<br />,ComplaintsNum 投诉次数<br />FROM #TempRefundReasonStatistics;<br /><br />----------------Sheet5：物流渠道角度统计<br />SELECT TransportName 物流渠道,RefundMoney 退款金额<br /> ,ShipedTotalPrice 发货订单总金额,RefundMoneyAccounted 退款金额占比,RefundMoneyProportion 退款额比例<br /> ,ShipedOrderNum 发货订单数,ShipedRefundOrderNum 已发货退款订单数,ShipedRefundOrderProportion 退款订单数比例,ShipedRefundOrderAccounted 退款订单数占比<br /> ,ComplaintsNum 客户投诉次数,ComplaintsNumAccounted 订单投诉比例,ComplaintsNumProportion 订单投诉占比<br />FROM #TempLogisticsStatistics;<br /><br />----------------Sheet6 按客服人员统计<br />SELECT OrderSourceName 订单帐号,ServiceAdminName 客服人员,DepartmentName 部门<br /> ,Unfilled 未发货退款,Shipped 已发货退全款,Section 已发货部分退款<br /> ,UnfilledProportion 未发货退款比例,ShippedProportion 已发货退全款比例,SectionProportion 已发货部分退款比例<br /> ,TotalRefundMoney 退款总金额,TotalSaleMoney 销售总额,totalProportion 退款总比例<br /> ,UnfilledOrderNum 未发货退款订单数,ShippedOrderNum 已发货退全款订单数,SectionOrderNum 已发货部分退款退单数<br /> ,UnfilledOrderNumProportion 未发货退款订单数比例,ShippedOrderNumProportion 已发货退全款订单数比例,SectionOrderNumProportion 已发货部分退款订单数比<br /> ,RefundOrderNum 退款订单数量,TotalOrderNum 销售订单数量,totalOrderNumProportion 退款订单总比例<br />FROM #TempServiceStatistics;<br /><br />------------报表说明<br />SELECT '按上架人员SKU统计' AS 统计方式,'退款金额占比：退款总金额/销售总金额' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款个数占比：退款个数/销售个数' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款订单数占比：总退单数/总订单数' 说明<br />UNION ALL<br />SELECT '按账号仓库角度统计' AS 统计方式,'退款订单占比：此仓库退款订单/此仓库发货订单' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款订单比例：此仓库退款订单数/所有仓库退款订单数' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款金额占比：退款金额/已发货金额' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款金额比例：此仓库退款金额/所有仓库退款金额' 说明<br />UNION ALL<br />SELECT '退款原因角度统计' AS 统计方式,'退款金额比例 ：此退款原因退款金额/退款总金额' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款原因投诉比例：此原因投诉次数/总投诉次数' 说明<br />UNION ALL<br />SELECT '物理渠道角度统计' AS 统计方式,'退款额占比：此物流退款金额/此物流发货总金额' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款金额比例：此物流退款金额/总物流退款金额' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款订单占比:此物流退款订单数/此物流发货总订单数' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'退款订单数比例：此物流退款订单数/总物流退款订单数' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'订单投诉占比: ( 海关扣货 、包裹丢失、运输损坏)该物流的投诉次数/所有物流的投诉次数 * 100%' 说明<br />UNION ALL<br />SELECT '' AS 统计方式,'订单投诉比例: （海关扣货 、包裹丢失、运输损坏）该物流被投诉的订单数/该物流的所有订单数 * 100%' 说明;<br /><br /><br /><br />if Object_id('Tempdb..#orderinfo') IS NOT NULL<br />DROP TABLE #orderinfo;<br /><br />IF OBJECT_ID('Tempdb..#orderinfo2') IS NOT NULL<br />DROP TABLE #orderinfo2;<br /><br />DROP TABLE #refundSku;<br />DROP TABLE #OrderInfoFundReceipt;<br />DROP TABLE #OrderInfoFundReceiptList;<br />DROP TABLE #TempProductStatistics;<br />DROP TABLE #TempWareHouseStatistics;<br />DROP TABLE #TempRefundReasonStatistics;<br />DROP TABLE #TempLogisticsStatistics;<br />DROP TABLE #TempServiceStatistics;</p>
006- <p><img src="{194500.png}" alt="" /></p>
007- <p>在自定义报告导出页面选择刚刚配置的【Cdiscount平台订单退款分析报告】，一直在加载中</p>
008- <p><br /></p>
009- <p><img src="{194501.png}" alt="" /></p>
010- <p><br /></p>
011- <p>[结果]</p>
012- <p><br /></p>
013- <p><img src="{194502.png}" alt="" /></p>
014- <p><img src="{194503.png}" alt="" /></p>
015- <br /><p>[期望]</p>
016- 可以正常导出<br />

修改了 指派给 ，旧值为 ""，新值为 "huchuwen"。
修改了 Bug标题 ，区别为：

001- zanwu
001+ CDiscount平台订单退款分析报告增加FBC字段，部分字段未与需求一致，排序未按需求要求

001- zanwu
001+ CDiscount平台订单退款分析报告增加FBC字段，部分字段未与需求一致，排序未按需求要求

修改了 重现步骤，区别为：

001-
001+ 步骤：
002+
003+ 结果：
004+
005+
006+ 希望：
007+ 与需求保持一致

001-
001+ <p>步骤：</p>
002+ <p><img src="{194510.png}" alt="" /></p>
003+ <p>结果：</p>
004+ <p><img src="{194516.png}" alt="" /></p>
005+ <p><br /></p>
006+ <p>希望：</p>
007+ <p>与需求保持一致</p>

修改了 重现步骤，区别为：

005-
005+

005- <p><br /></p>
005+ <p><img src="{194518.png}" alt="" /></p>

修改了 抄送给，旧值为 ""，新值为 ",lianghuan,songyu"。

修改了 解决方案，旧值为 ""，新值为 "external"。
修改了 解决日期，旧值为 ""，新值为 "2022-04-12 17:22:01"。
修改了 指派给 ，旧值为 "huchuwen"，新值为 "xiayu"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "huchuwen"。

修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "xiayu"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "xiayu"。
修改了 关闭日期，旧值为 ""，新值为 "2022-04-16 11:33:07"。