TASK#33369 品牌销售业绩统计报表添加字段 / 技术研发中心 12月第1版本（2022年）

任务描述

暂无

需求描述

1.1. 背景

背景：现有品牌销售业绩统计报表，为提高绩效查验添加对应字段

1.2. 验收目标

1.品牌销售业绩统计报表（RMB）添加首单销售订单时间

2.品牌销售业绩统计报表（RMB）添加产品开发人员字段

2. 功能设计

2.1. 业务流程

无

2.2.1 操作路径

1. 报告中心---自定义报告下载

2.2.3 品牌销售业绩统计报表（RMB）添加首单销售订单时间

2.2.3.1 功能点描述：

原设计：

调整：该自定义报告，添加第一个销售订单时间字段；

2.2.3 品牌销售业绩统计报表（RMB）添加产品主管字段

2.2.3.1 功能点描述：

原设计：

调整：添加第一个品牌产品部的产品经理字段；数据来源于产品开发人员

验收标准

暂无

附件

历史记录

2022-12-02 10:39:17, 由 许瑞丰 创建。
2022-12-02 17:10:19, 由肖雄启动。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-02 17:10:16"。
修改了 任务状态，旧值为 "wait"，新值为 "doing"。

2022-12-02 19:12:30, 由肖雄添加备注。

一、只需要自定义报表调整报表：品牌销售业绩统计报表（RMB）

--DECLARE @CustomerID int	 =1
--DECLARE @StartTime DATETIME
--DECLARE @EndTime DATETIME
--DECLARE	@AdminID INT =1521
--SET @StartTime = '2022-01-01'
--SET @EndTime = '2022-06-01'
--SELECT * FROM RB_Report_OrderSettlement a(NOLOCK)
--WHERE
--	a.CustomerID = @CustomerID
--	AND SettlementShortDate >= @StartTime
--	AND SettlementShortDate <= @EndTime
--	AND a.OrderSourceType = 1
--	AND PromotionType = 1
--	AND a.OrderSourceID IN ( SELECT OrderSourceID FROM dbo.RB_OrderSource_SaleAdmin WHERE CustomerID = @CustomerID AND AdminID IN ( @AdminID ) )

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#Tb_Temp_SKU_Order_Min_AddTime') and type='U')  
BEGIN
DROP TABLE #Tb_Temp_SKU_Order_Min_AddTime
END

select * INTO #Tb_Temp_SKU_Order_Min_AddTime From (
select  SKU, (select top(1) o.AddTime from RB_OrderList(NOLOCK) o where CustomerID=@CustomerID AND  o.SKU= p.SKU and o.AddTime>=p.AddTime order by  o.AddTime asc ) AS AddTime
from RB_Product(NOLOCK) p 
)ss where ss.AddTime IS NOT NULL

select tt.*,tmp.AddTime '首单销售订单时间' from(
 SELECT
  YEAR(ShipDate) '年',
  MONTH(ShipDate) '月',
  a.OrderSourceName AS 'OrderBy',
  a.OrderSourceName AS '来源渠道',
  RIGHT(a.OrderSourceName, 2) AS '站点',
  CASE
    WHEN a.SKU != '' THEN a.SKU
    ELSE c.SKU
  END AS '系统SKU',
  f.sx AS '产品属性',
  '' AS '季节属性',
  SellerSKU AS '渠道SKU',
  a.BusinessAdminName '销售人员',
  e.EmpoyeeNumber '销售人员工号',
  CASE
    WHEN ISNULL(BusinessTeamName, '') = '' THEN '未分组'
    ELSE BusinessTeamName
  END '销售小组',
  dt.DepartmentName '来源渠道分组',
  CASE
    WHEN a.SKU != '' THEN CONVERT (VARCHAR (10), b.OnlineTime, 120)
    ELSE CONVERT (VARCHAR (10), d.OnlineTime, 120)
  END AS '上架时间',
  ROUND(
    1.0 * DATEDIFF(
      DAY,
      CASE
        WHEN a.SKU != '' THEN b.OnlineTime
        ELSE d.OnlineTime
      END,
      @EndTime
    ) / 365,
    2
  ) AS '上线周期',
  'RMB' '币种',
  ROUND(SUM (Profits), 2) '总毛利润',
  ROUND(
    SUM (
      (
        SalesPrice + TransportPay + SalesRefunds + PromotionRebates + OtherCredits + ISNULL(Tax, 0) + ISNULL(MarketplaceFacilitatorTax, 0)
      )
    ),
    2
  ) '总销售额',
  ROUND(SUM (SalesPrice), 2) '销售收入',
  ROUND(SUM (TransportPay), 2) '运费收入',
  ROUND(SUM (SalesRefunds), 2) '退款金额',
  ROUND(SUM (PromotionRebates), 2) '促销折扣',
  ROUND(SUM (OtherCredits), 2) '其他收入',
  ROUND(
    SUM (
      (
        ISNULL(Tax, 0) + ISNULL(MarketplaceFacilitatorTax, 0)
      )
    ),
    2
  ) '税费收入',
  ROUND(SUM (CommisionFee), 2) '交易佣金',
  ROUND(SUM (FBATransactionFees), 2) 'FBA操作费',
  ROUND(SUM (OrderSourceOtherDebits), 2) '平台其他支出',
  ROUND(SUM (CostOfAdvertising), 2) AS '广告费用',
  ROUND(
    SUM (BuyPrice + ISNULL(DistributionPrice, 0)),
    2
  ) '采购成本(RMB)',
  ROUND(SUM (TaxRefundFee), 2) '退税收益(RMB)',
  ROUND(SUM (ISNULL(SalesVatFee, 0)), 2) '销售VAT',
  ROUND(SUM (ISNULL(SubsidyPrice, 0)), 2) '补贴调整',
  ROUND(SUM (PackageFees), 2) '本地仓运费(RMB)',
  ROUND(SUM (OverSeasShipFees), 2) '海外仓运费',
  ROUND(SUM (FirstLegFees), 2) '头程运费',
  ROUND(SUM (TariffFees), 2) '进口关税',
  ROUND(SUM (ExchangeLost), 2) '汇率折损',
  ROUND(SUM (PackagedCost), 2) '包装成本',
  ROUND(
    SUM (ISNULL(WareHouseManagementPrice, 0)),
    2
  ) '仓库管理成本(自定义费用项)',
  ROUND(
    SUM (ISNULL(CustomFeeOtherCostPrice, 0)),
    2
  ) '其它费用成本(自定义费用项)',
  ROUND(SUM(a.ImportFee), 2) AS '人工导入费用',
  -----仓租只根据备注统计，可能有误差。当前已发现场景都考虑了，之后出现异常可以再优化
  ROUND(
    SUM(
      CASE
        WHEN a.OrderDescription LIKE '%amazon仓储费分摊%' THEN - a.OrderSourceOtherDebits
        ELSE 0
      END
    ),
    2
  ) AS 'FBA仓租',
  ROUND(
    SUM(
      CASE
        WHEN a.OrderDescription LIKE '%盛宝隆%'
        AND a.OrderDescription LIKE '%仓租%' THEN - a.ImportFee
        ELSE 0
      END
    ),
    2
  ) AS '盛宝隆仓租',
  MAX(d.AssignDevelopAdminName) '产品主管'
FROM
  RB_Report_OrderSettlement (NOLOCK) a
  LEFT JOIN dbo.RB_Product_Admin (NOLOCK) b ON b.CustomerID = a.CustomerID
  AND b.SKU = a.SKU
  LEFT JOIN dbo.RB_Product_OrderSourceSKU (NOLOCK) c ON c.CustomerID = a.CustomerID
  AND c.OrderSourceID = a.OrderSourceID
  AND c.OrderSourceSKU = a.SellerSKU
  AND c.[isDelete] = 0
  LEFT JOIN dbo.RB_Product_Admin (NOLOCK) d ON d.CustomerID = c.CustomerID
  AND d.SKU = c.SKU
  LEFT JOIN dbo.RB_Admin e on e.ID = a.BusinessAdminID
  LEFT JOIN dbo.RB_Department d1 ON d1.CustomerID = a.CustomerID
  AND e.DepartmentID = d1.ID
  LEFT JOIN (
    SELECT
      DISTINCT *
    FROM
      dbo.ypsku_group (NOLOCK)
  ) f ON f.SKU = a.SKU
  LEFT JOIN dbo.RB_OrderSource (NOLOCK) os ON a.CustomerID = os.CustomerID
  AND a.OrderSourceID = os.ID
  LEFT JOIN dbo.RB_Admin e1 on e1.ID = os.SaleAdminID
  LEFT JOIN dbo.RB_Department (NOLOCK) dt ON dt.CustomerID = a.CustomerID
  AND dt.ID = e1.DepartmentID
WHERE
  a.CustomerID = @CustomerID
  AND ShipDate >= @StartTime
  AND ShipDate <= @EndTime
  AND a.OrderSourceType = 1
  AND ISNULL(PromotionType, 0) = 0
  AND a.OrderSourceID IN (
    SELECT
      OrderSourceID
    FROM
      dbo.RB_OrderSource_SaleAdmin
    WHERE
      CustomerID = @CustomerID
      AND AdminID IN (@AdminID)
  )
GROUP BY
  YEAR(ShipDate),
  MONTH(ShipDate),
  RIGHT(a.OrderSourceName, 2),
  CASE
    WHEN a.SKU != '' THEN a.SKU
    ELSE c.SKU
  END,
  CASE
    WHEN ISNULL(BusinessTeamName, '') = '' THEN '未分组'
    ELSE BusinessTeamName
  END,
  CASE
    WHEN a.SKU != '' THEN CONVERT(VARCHAR(10), b.OnlineTime, 120)
    ELSE CONVERT(VARCHAR(10), d.OnlineTime, 120)
  END,
  ROUND(
    1.0 * DATEDIFF(
      DAY,
      CASE
        WHEN a.SKU != '' THEN b.OnlineTime
        ELSE d.OnlineTime
      END,
      @EndTime
    ) / 365,
    2
  ),
  a.OrderSourceName,
  f.sx,
  a.SellerSKU,
  a.BusinessAdminName,
  e.EmpoyeeNumber,
  dt.DepartmentName
UNION ALL
SELECT
  YEAR(ShipDate) '年',
  MONTH(ShipDate) '月',
  a.OrderSourceName AS 'OrderBy',
  a.OrderSourceName '来源渠道',
  RIGHT(a.OrderSourceName, 2) AS '站点',
  CASE
    WHEN a.SKU != '' THEN a.SKU
    ELSE c.SKU
  END AS '系统SKU',
  f.sx AS '产品属性',
  '' AS '季节属性',
  SellerSKU AS '渠道SKU',
  a.BusinessAdminName '销售人员',
  e.EmpoyeeNumber '销售人员工号',
  CASE
    WHEN ISNULL(BusinessTeamName, '') = '' THEN '未分组'
    ELSE BusinessTeamName
  END '销售小组',
  dt.DepartmentName '来源渠道分组',
  CASE
    WHEN a.SKU != '' THEN CONVERT (VARCHAR (10), b.OnlineTime, 120)
    ELSE CONVERT (VARCHAR (10), d.OnlineTime, 120)
  END AS '上架时间',
  ROUND(
    1.0 * DATEDIFF(
      DAY,
      CASE
        WHEN a.SKU != '' THEN b.OnlineTime
        ELSE d.OnlineTime
      END,
      @EndTime
    ) / 365,
    2
  ) AS '上线周期',
  'RMB' '币种',
  ROUND(SUM (Profits), 2) '总毛利润',
  ROUND(
    SUM (
      (
        SalesPrice + TransportPay + SalesRefunds + PromotionRebates + OtherCredits + ISNULL(Tax, 0) + ISNULL(MarketplaceFacilitatorTax, 0)
      )
    ),
    2
  ) '总销售额',
  ROUND(SUM (SalesPrice), 2) '销售收入',
  ROUND(SUM (TransportPay), 2) '运费收入',
  ROUND(SUM (SalesRefunds), 2) '退款金额',
  ROUND(SUM (PromotionRebates), 2) '促销折扣',
  ROUND(SUM (OtherCredits), 2) '其他收入',
  ROUND(
    SUM (
      (
        ISNULL(Tax, 0) + ISNULL(MarketplaceFacilitatorTax, 0)
      )
    ),
    2
  ) '税费收入',
  ROUND(SUM (CommisionFee), 2) '交易佣金',
  ROUND(SUM (FBATransactionFees), 2) 'FBA操作费',
  ROUND(SUM (OrderSourceOtherDebits), 2) '平台其他支出',
  ROUND(SUM (CostOfAdvertising), 2) AS '广告费用',
  ROUND(
    SUM (BuyPrice + ISNULL(DistributionPrice, 0)),
    2
  ) '采购成本(RMB)',
  ROUND(SUM (TaxRefundFee), 2) '退税收益(RMB)',
  ROUND(SUM (ISNULL(SalesVatFee, 0)), 2) '销售VAT',
  ROUND(SUM (ISNULL(SubsidyPrice, 0)), 2) '补贴调整',
  ROUND(SUM (PackageFees), 2) '本地仓运费(RMB)',
  ROUND(SUM (OverSeasShipFees), 2) '海外仓运费',
  ROUND(SUM (FirstLegFees), 2) '头程运费',
  ROUND(SUM (TariffFees), 2) '进口关税',
  ROUND(SUM (ExchangeLost), 2) '汇率折损',
  ROUND(SUM (PackagedCost), 2) '包装成本',
  ROUND(
    SUM (ISNULL(WareHouseManagementPrice, 0)),
    2
  ) '仓库管理成本(自定义费用项)',
  ROUND(
    SUM (ISNULL(CustomFeeOtherCostPrice, 0)),
    2
  ) '其它费用成本(自定义费用项)',
  ROUND(SUM(a.ImportFee), 2) AS '人工导入费用',
  -----仓租只根据备注统计，可能有误差。当前已发现场景都考虑了，之后出现异常可以再优化
  ROUND(
    SUM(
      CASE
        WHEN a.OrderDescription LIKE '%amazon仓储费分摊%' THEN - a.OrderSourceOtherDebits
        ELSE 0
      END
    ),
    2
  ) AS 'FBA仓租',
  ROUND(
    SUM(
      CASE
        WHEN a.OrderDescription LIKE '%盛宝隆%'
        AND a.OrderDescription LIKE '%仓租%' THEN - a.ImportFee
        ELSE 0
      END
    ),
    2
  ) AS '盛宝隆仓租',
  MAX(d.AssignDevelopAdminName) '产品主管'
FROM
  RB_Report_OrderSettlement (NOLOCK) a
  LEFT JOIN dbo.RB_Product_Admin (NOLOCK) b ON b.CustomerID = a.CustomerID
  AND b.SKU = a.SKU
  LEFT JOIN dbo.RB_Product_OrderSourceSKU (NOLOCK) c ON c.CustomerID = a.CustomerID
  AND c.OrderSourceID = a.OrderSourceID
  AND c.OrderSourceSKU = a.SellerSKU
  AND c.[isDelete] = 0
  LEFT JOIN dbo.RB_Product_Admin (NOLOCK) d ON d.CustomerID = c.CustomerID
  AND d.SKU = c.SKU
  LEFT JOIN dbo.RB_Admin e on e.ID = a.BusinessAdminID
  LEFT JOIN (
    SELECT
      DISTINCT *
    FROM
      dbo.ypsku_group (NOLOCK)
  ) f ON f.SKU = a.SKU
  LEFT JOIN dbo.RB_OrderSource (NOLOCK) os ON a.CustomerID = os.CustomerID
  AND a.OrderSourceID = os.ID
  LEFT JOIN dbo.RB_Admin e1 on e1.ID = os.SaleAdminID
  LEFT JOIN dbo.RB_Department (NOLOCK) dt ON dt.CustomerID = a.CustomerID
  AND dt.ID = e1.DepartmentID
WHERE
  a.CustomerID = @CustomerID
  AND SettlementShortDate >= @StartTime
  AND SettlementShortDate <= @EndTime
  AND a.OrderSourceType = 1
  AND PromotionType = 1
  AND a.OrderSourceID IN (
    SELECT
      OrderSourceID
    FROM
      dbo.RB_OrderSource_SaleAdmin
    WHERE
      CustomerID = @CustomerID
      AND AdminID IN (@AdminID)
  )
GROUP BY
  YEAR(ShipDate),
  MONTH(ShipDate),
  CASE
    WHEN a.SKU != '' THEN a.SKU
    ELSE c.SKU
  END,
  RIGHT(a.OrderSourceName, 2),
  CASE
    WHEN ISNULL(BusinessTeamName, '') = '' THEN '未分组'
    ELSE BusinessTeamName
  END,
  CASE
    WHEN a.SKU != '' THEN CONVERT(VARCHAR(10), b.OnlineTime, 120)
    ELSE CONVERT(VARCHAR(10), d.OnlineTime, 120)
  END,
  ROUND(
    1.0 * DATEDIFF(
      DAY,
      CASE
        WHEN a.SKU != '' THEN b.OnlineTime
        ELSE d.OnlineTime
      END,
      @EndTime
    ) / 365,
    2
  ),
  a.OrderSourceName,
  f.sx,
  a.SellerSKU,
  a.BusinessAdminName,
  e.EmpoyeeNumber,
  dt.DepartmentName
  )tt LEFT JOIN #Tb_Temp_SKU_Order_Min_AddTime tmp on tt.系统SKU=tmp.SKU

二、自测，由于用了临时表，因些自定义报表导出无法预览

三、SVN提交

无

2022-12-02 19:12:47, 由肖雄完成。
修改了 总计消耗，旧值为 "0"，新值为 "1"。
修改了 指派给 ，旧值为 "xiaoxiong"，新值为 "xuruifeng"。
修改了 实际完成，旧值为 ""，新值为 "2022-12-02 19:12:37"。
修改了 任务状态，旧值为 "doing"，新值为 "done"。
修改了 预计剩余，旧值为 "1"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "xiaoxiong"。
2022-12-05 10:19:40, 由肖雄编辑。

上传了附件自定义报表：品牌销售业绩统计报表（RMB）.sql
2022-12-28 09:14:39, 由 zhengqiao 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "xuruifeng"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhengqiao"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-28 09:14:39"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	技术研发中心 12月第1版本（2022年）
所属模块	/
相关需求	品牌销售业绩统计报表添加字段
指派给	Closed 于 2022-12-28 09:14:39
任务类型	后端
任务标签	初始
开发组	多平台开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	许瑞丰于 2022-12-02 10:39:17
由谁完成	肖雄于 2022-12-02 19:12:37
由谁取消	暂无
由谁关闭	zhengqiao 于 2022-12-28 09:14:39
关闭原因	已完成
最后编辑	zhengqiao 于 2022-12-28 09:14:39

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	1工时
总计消耗	1工时
预计剩余	0工时
预计开始	2022-12-02
实际开始	2022-12-02
截止日期	2022-12-02