深圳智汇创想科技有限公司

1.1. 背景

   应财务核验平台来源渠道需求，关联来源渠道表，新增一份自定义报告；

1.1. 1价值

    提升数据核验时间效率，2小时/月/财务数据核验

1.2. 验收目标

            1. 新增来源渠道自定义报告；

2.   功能设计

2.1. 页面路径

自定义报告导出--财务来源渠道自定义

2.2.3 新增来源渠道自定义报告

2.2.3.1  功能点描述：

原设计： 

无

新增 ：

表列

订单来源渠道名称

真实店铺名称

所在国家

 VAT税号VAT税率

 清关人

 业务员负责人

业务员

客服小组客服

责任库存核算方式

订单授权状态

渠道系统状态

修改时间

当前渠道系统状态

异常原因

暂停原因

备注

参考这个添加修改：

DROP TABLE IF EXISTS #OrderSourceTypeName
SELECT
a.OrderSourceType,
    a.OrderSourceTypeName 
INTO #OrderSourceTypeName
FROM(

SELECT 1 OrderSourceType , '亚马逊平台' OrderSourceTypeName UNION

SELECT 2 OrderSourceType , 'Ebay平台' OrderSourceTypeName UNION

SELECT 3 OrderSourceType , '团购平台' OrderSourceTypeName UNION

SELECT 4 OrderSourceType , 'B2C网站平台' OrderSourceTypeName UNION

SELECT 5 OrderSourceType , '赠送订单' OrderSourceTypeName UNION

SELECT 6 OrderSourceType , '枪手订单' OrderSourceTypeName UNION

SELECT 7 OrderSourceType , '问题订单重发' OrderSourceTypeName UNION

SELECT 8 OrderSourceType , '问题订单补发' OrderSourceTypeName UNION

SELECT 9 OrderSourceType , '代发货渠道' OrderSourceTypeName UNION

SELECT 10 OrderSourceType , '亚马逊FBA' OrderSourceTypeName UNION

SELECT 19 OrderSourceType , '其他第三方渠道' OrderSourceTypeName UNION

SELECT 20 OrderSourceType , '本地仓库备库订单' OrderSourceTypeName UNION

SELECT 21 OrderSourceType , '海外仓库备库订单' OrderSourceTypeName UNION

SELECT 22 OrderSourceType , '亚马逊仓库备库订单' OrderSourceTypeName UNION

SELECT 23 OrderSourceType , 'Wish平台' OrderSourceTypeName UNION

SELECT 24 OrderSourceType , '共享平台' OrderSourceTypeName UNION

SELECT 25 OrderSourceType , '速卖通平台' OrderSourceTypeName UNION

SELECT 26 OrderSourceType , '麦进斗平台' OrderSourceTypeName UNION

SELECT 27 OrderSourceType , '敦煌平台' OrderSourceTypeName UNION

SELECT 28 OrderSourceType , 'PayPal交易平台' OrderSourceTypeName UNION

SELECT 29 OrderSourceType , '新蛋平台' OrderSourceTypeName UNION

SELECT 30 OrderSourceType , 'LetsEcig平台' OrderSourceTypeName UNION

SELECT 31 OrderSourceType , 'Meetgift平台' OrderSourceTypeName UNION

SELECT 32 OrderSourceType , 'Qoo10平台' OrderSourceTypeName UNION

SELECT 33 OrderSourceType , 'Lazada平台' OrderSourceTypeName UNION

SELECT 34 OrderSourceType , 'Alibaba平台' OrderSourceTypeName UNION

SELECT 35 OrderSourceType , 'Cdiscount平台' OrderSourceTypeName UNION

SELECT 36 OrderSourceType , 'Linio平台' OrderSourceTypeName UNION

SELECT 37 OrderSourceType , 'Bellabuy平台' OrderSourceTypeName UNION

SELECT 38 OrderSourceType , '京东平台' OrderSourceTypeName UNION

SELECT 39 OrderSourceType , 'Tophatter平台' OrderSourceTypeName UNION

SELECT 40 OrderSourceType , 'PriceMinister平台' OrderSourceTypeName UNION

SELECT 41 OrderSourceType , 'Mercadolibre平台' OrderSourceTypeName UNION

SELECT 42 OrderSourceType , 'Shopee平台' OrderSourceTypeName UNION

SELECT 43 OrderSourceType , 'Kamvio平台' OrderSourceTypeName UNION

SELECT 44 OrderSourceType , 'Amazonvendorexpress平台' OrderSourceTypeName UNION

SELECT 45 OrderSourceType , 'Walmart平台' OrderSourceTypeName UNION

SELECT 46 OrderSourceType , 'Deal平台' OrderSourceTypeName UNION

SELECT 47 OrderSourceType , 'Jumia平台' OrderSourceTypeName UNION

SELECT 48 OrderSourceType , 'Jet平台' OrderSourceTypeName UNION

SELECT 49 OrderSourceType , 'Sears平台' OrderSourceTypeName UNION

SELECT 50 OrderSourceType , 'Shopify平台' OrderSourceTypeName UNION

SELECT 51 OrderSourceType , 'Joom平台' OrderSourceTypeName UNION

SELECT 52 OrderSourceType , 'MyMall平台' OrderSourceTypeName UNION

SELECT 53 OrderSourceType , 'FactoryMarket平台' OrderSourceTypeName UNION

SELECT 54 OrderSourceType , 'Fyndiq平台' OrderSourceTypeName UNION

SELECT 55 OrderSourceType , '京东国内平台' OrderSourceTypeName UNION

SELECT 56 OrderSourceType , 'Oberlo平台' OrderSourceTypeName UNION

SELECT 57 OrderSourceType , '速卖通V2平台' OrderSourceTypeName UNION

SELECT 58 OrderSourceType , 'LazadaV2平台' OrderSourceTypeName UNION

SELECT 59 OrderSourceType , 'Yandex平台' OrderSourceTypeName UNION

SELECT 60 OrderSourceType , 'Paytm平台' OrderSourceTypeName UNION

SELECT 61 OrderSourceType , '京东国际平台' OrderSourceTypeName UNION

SELECT 62 OrderSourceType , 'Vova平台' OrderSourceTypeName UNION

SELECT 63 OrderSourceType , '京东印尼平台' OrderSourceTypeName UNION

SELECT 64 OrderSourceType , 'UMKA平台' OrderSourceTypeName UNION

SELECT 65 OrderSourceType , 'Rakuten平台' OrderSourceTypeName UNION

SELECT 66 OrderSourceType , '值点平台' OrderSourceTypeName UNION

SELECT 67 OrderSourceType , 'Allegro平台' OrderSourceTypeName UNION

SELECT 68 OrderSourceType , '亲库平台' OrderSourceTypeName UNION

SELECT 69 OrderSourceType , 'ZoodMall' OrderSourceTypeName UNION

SELECT 70 OrderSourceType , 'Mercadolibre国际平台' OrderSourceTypeName UNION

SELECT 71 OrderSourceType , 'Gmarket平台' OrderSourceTypeName UNION

SELECT 72 OrderSourceType , 'Auction平台' OrderSourceTypeName UNION

SELECT 73 OrderSourceType , 'Coupang平台' OrderSourceTypeName UNION

SELECT 74 OrderSourceType , 'FacebookMarketplace平台' OrderSourceTypeName UNION

SELECT 75 OrderSourceType , 'KauflandDe平台' OrderSourceTypeName UNION

SELECT 76 OrderSourceType , '阿里巴巴国际站平台' OrderSourceTypeName UNION

SELECT 77 OrderSourceType , 'RatukenDE平台' OrderSourceTypeName UNION

SELECT 78 OrderSourceType , 'Wayfair平台' OrderSourceTypeName UNION

SELECT 79 OrderSourceType , '店匠平台' OrderSourceTypeName UNION

SELECT 80 OrderSourceType , 'B2W平台' OrderSourceTypeName UNION

SELECT 81 OrderSourceType , 'Kogan平台' OrderSourceTypeName UNION

SELECT 82 OrderSourceType , 'Fruugo平台' OrderSourceTypeName UNION

SELECT 83 OrderSourceType , 'Komplett平台' OrderSourceTypeName UNION

SELECT 84 OrderSourceType , 'Digitect平台' OrderSourceTypeName UNION

SELECT 85 OrderSourceType , 'Yahoo平台' OrderSourceTypeName UNION

SELECT 86 OrderSourceType , 'Bukalapak平台' OrderSourceTypeName UNION

SELECT 87 OrderSourceType , '沃尔玛DSV平台' OrderSourceTypeName UNION

SELECT 88 OrderSourceType , 'Shopyy平台' OrderSourceTypeName UNION

SELECT 89 OrderSourceType , 'L192平台' OrderSourceTypeName UNION

SELECT 90 OrderSourceType , 'OTTO平台' OrderSourceTypeName UNION

SELECT 91 OrderSourceType , 'CrossMall平台' OrderSourceTypeName UNION

SELECT 92 OrderSourceType , 'Fnac平台' OrderSourceTypeName UNION

SELECT 93 OrderSourceType , 'Darty平台' OrderSourceTypeName UNION

SELECT 94 OrderSourceType , 'EPrice平台' OrderSourceTypeName UNION

SELECT 95 OrderSourceType , 'Daraz平台' OrderSourceTypeName UNION

SELECT 96 OrderSourceType , 'Americanas平台' OrderSourceTypeName UNION

SELECT 97 OrderSourceType , 'ShopeeV2平台' OrderSourceTypeName UNION

SELECT 98 OrderSourceType , 'Snapdeal平台' OrderSourceTypeName UNION

SELECT 99 OrderSourceType , 'Submarino平台' OrderSourceTypeName UNION

SELECT 100 OrderSourceType , 'Overstock平台' OrderSourceTypeName UNION

SELECT 101 OrderSourceType , 'ManoMano平台' OrderSourceTypeName UNION

SELECT 102 OrderSourceType , 'RueduCommerce平台' OrderSourceTypeName UNION

SELECT 103 OrderSourceType , 'TradeMe平台' OrderSourceTypeName UNION

SELECT 104 OrderSourceType , 'TikTok平台' OrderSourceTypeName UNION

SELECT 105 OrderSourceType , 'Worten平台' OrderSourceTypeName UNION

SELECT 106 OrderSourceType , 'PayPayMall平台' OrderSourceTypeName UNION

SELECT 107 OrderSourceType , 'Target平台' OrderSourceTypeName UNION

SELECT 108 OrderSourceType , 'Flipkart平台' OrderSourceTypeName UNION

SELECT 109 OrderSourceType , 'BestBuy平台' OrderSourceTypeName UNION

SELECT 110 OrderSourceType , 'BolCom平台' OrderSourceTypeName UNION

SELECT 111 OrderSourceType , 'Tokopedia平台' OrderSourceTypeName UNION

SELECT 112 OrderSourceType , 'SHOPLINE平台' OrderSourceTypeName UNION

SELECT 114 OrderSourceType , 'Akulaku平台' OrderSourceTypeName UNION

SELECT 115 OrderSourceType , 'Funpinpin平台' OrderSourceTypeName UNION

SELECT 116 OrderSourceType , 'Fannoshop平台' OrderSourceTypeName UNION

SELECT 117 OrderSourceType , 'OnBuy' OrderSourceTypeName UNION

SELECT 118 OrderSourceType , 'HomeDepot' OrderSourceTypeName UNION

SELECT 119 OrderSourceType , 'WooCommerce' OrderSourceTypeName UNION

SELECT 120 OrderSourceType , 'Thisshop' OrderSourceTypeName UNION

SELECT 121 OrderSourceType , 'XShoppy' OrderSourceTypeName UNION

SELECT 124 OrderSourceType , 'Catch平台' OrderSourceTypeName UNION

SELECT 125 OrderSourceType , '拼多多跨境' OrderSourceTypeName 

)a



-- 查询
SELECT
ost.OrderSourceTypeName '平台',
os.OrderSourceName [来源渠道名称],
    osl.AddTime [导入时间],
CASE
WHEN osl.AddTime >= DATEADD(MONTH,-3,GETDATE()) THEN '新账号-3个月内'
WHEN osl.AddTime < DATEADD(MONTH,-3,GETDATE()) and osl.AddTime >= DATEADD(MONTH,-12,GETDATE()) THEN '普通账号-1年内'
ELSE '老账号-大1年'
END [账号类型],
de.DepartmentName [负责部门],
CASE os.SourceStatus
WHEN 0 THEN '正常'
WHEN 1 THEN '申诉中'
WHEN 2 THEN '暂停'
WHEN 3 THEN '注销'
ELSE NULL
END [渠道系统状态],

(SELECT
MAX(ros.PayTime)
FROM dbo.RB_Report_OrderSettlement ros(NOLOCK)
INNER JOIN dbo.RB_OrderInfo oi(NOLOCK) ON oi.CustomerID = ros.CustomerID AND oi.OrderCode = ros.OrderCode
WHERE  ros.CustomerID=1 AND oi.IsStockOrder=0 AND oi.IsSystem = 0 AND ros.PayTime >= DATEADD(DAY, -360, GETDATE()) AND oi.OrderSourceID=os.ID
) [最近一笔订单支付时间],
os.UpdateTime [来源渠道最近更新时间],
os.RealShopName '真实店铺名称',
os.OrderSourceCountry '所在国家',
os.VATRatio '税率',
os.SaleVATRatio '销售税率',
os.SaleAdminName '首选销售人员',
    os.ServiceAdminName '客服人员',
os.ReasonWithRemark '备注',
CASE os.IsActive
WHEN 0 THEN '激活'
WHEN 1 THEN '不激活'
ELSE NULL
END '是否激活'


FROM dbo.RB_OrderSource os (NOLOCK)
LEFT JOIN (
SELECT CustomerID,LogTableID,MIN(AddTime) AddTime
FROM dbo.RB_OrderSource_Log osl(NOLOCK) 
WHERE osl.LogTableName='RB_OrderSource'
GROUP BY osl.CustomerID,osl.LogTableID
) osl ON osl.CustomerID = os.CustomerID AND osl.LogTableID = os.ID
LEFT JOIN irobotbox.dbo.RB_Admin ad(NOLOCK) ON ad.CustomerID = os.CustomerID AND ad.ID = os.SaleAdminID
LEFT JOIN irobotbox.dbo.RB_Department de(NOLOCK) ON de.CustomerID = os.CustomerID AND de.ID = ad.DepartmentID

LEFT JOIN(
SELECT
ros.CustomerID,
ros.OrderCode,
ros.OrderSourceID,
ros.Incomes,
ros.Profits,
ros.USD_ExchangeRMB,
ros.PayTime,
ROW_NUMBER() OVER(PARTITION BY oi.OrderSourceID ORDER BY oi.PayTime DESC) [rank]
FROM dbo.RB_Report_OrderSettlement ros(NOLOCK)
INNER JOIN dbo.RB_OrderInfo oi(NOLOCK) ON oi.CustomerID = ros.CustomerID AND oi.OrderCode = ros.OrderCode
WHERE  ros.CustomerID=1 AND oi.IsStockOrder=0 AND oi.IsSystem = 0 AND ros.PayTime >= DATEADD(DAY, -360, GETDATE())

)ros ON ros.OrderSourceID=os.ID AND ros.CustomerID = os.CustomerID
LEFT JOIN #OrderSourceTypeName ost ON ost.OrderSourceType = os.OrderSourceType

WHERE
os.CustomerID=1
--os.CustomerID=1
--AND os.ID=17225
    AND os.OrderSourceType =2


GROUP BY CASE
         WHEN osl.AddTime >= DATEADD(MONTH, -3, GETDATE()) THEN
         '新账号'
         WHEN osl.AddTime < DATEADD(MONTH, -3, GETDATE())
         AND osl.AddTime >= DATEADD(MONTH, -12, GETDATE()) THEN
         '普通账号'
         ELSE
         '老账号'
         END,
         CASE os.SourceStatus
         WHEN 0 THEN
         '正常'
         WHEN 1 THEN
         '申诉中'
         WHEN 2 THEN
         '暂停'
         WHEN 3 THEN
         '注销'
         ELSE
         NULL
         END,
         ost.OrderSourceTypeName,
         os.OrderSourceName,
         de.DepartmentName,
     osl.AddTime,
os.UpdateTime,
os.RealShopName,
os.OrderSourceCountry,
os.ID,
os.VATRatio,
os.SaleVATRatio,
os.SaleAdminName,
os.ServiceAdminName,
os.ReasonWithRemark,
    CASE os.IsActive
WHEN 0 THEN '激活'
WHEN 1 THEN '不激活'
ELSE NULL
    END