TASK#52218 【测试】采集数据源与后台下载报告一致，572店铺合计（50-100个店铺） / 财务余额核对与金蝶对接V1

任务描述

暂无

需求描述

需求路径：M-15688 FMS：Ebay金蝶对接项目（责任人：林很） · 钉钉文档 (dingtalk.com)

验收标准

暂无

历史记录

2023-08-03 14:38:32, 由周杰创建。
2023-08-07 09:43:52, 由周杰编辑。
修改了 开发组 ，旧值为 ""，新值为 "1"。
修改了 截止日期，旧值为 "2023-08-08"，新值为 "2023-08-10"。
修改了 最初预计，旧值为 "16"，新值为 "32"。
修改了 预计剩余，旧值为 "16"，新值为 "32"。
2023-08-07 19:46:56, 由 林鸿贵 启动。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-08-07 19:46:49"。
修改了 预计剩余，旧值为 "32"，新值为 "8"。
修改了 任务状态，旧值为 "wait"，新值为 "doing"。
2023-08-07 19:48:27, 由 林鸿贵 记录工时，消耗 8 小时。
修改了 总计消耗，旧值为 "0"，新值为 "8"。
修改了 预计剩余，旧值为 "8"，新值为 "24"。

已梳理采集与后台下载报告的对应关系，编写脚本中，预计8-8、8-9批量核对店铺数据
2023-08-08 17:45:50, 由 林鸿贵 添加备注。

目前已核对完业务提供的所有店铺后台交易明细--后台下载报告

验证场景：

通过脚本

1、根据店铺和月份对比采集库对应的月份

不区分订单和非订单

Type、类型
調整 ADJUSTMENT
訂單 SALE
凍結冻结
發款發款
付款糾紛纠纷
其他費用 NON SALE CHARGE
收費 TRANSFER
索償 REFUND
退款 REFUND
郵寄標籤 SHIPPING LABEL

---对应表格
SELECT SUM( CAST ( NetAmount AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%mymalldealshop%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND NetAmount != '--'

---对应表格
SELECT
SUM ( CAST ( GrossTransactionAmount AS FLOAT ) ) AS '交易总额'
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND GrossTransactionAmount != '--'

SELECT
SUM ( CAST ( ItemSubtotal AS FLOAT ) ) AS '物品小计'
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND ItemSubtotal != '--'

SELECT
SUM ( CAST ( EbayCollecteTax AS FLOAT ) ) AS 'eBay 收取的稅款'
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND EbayCollecteTax != '--'

SELECT SUM(ColumnName1) AS '佣金(成交費 — 固定\成交費 — 因類別而異\非常高「物品與說明不符」率的費用\表現低於標準的費用\跨國交易費
)' from (
SELECT SUM
( CAST ( FinalValueFeeFixed AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND FinalValueFeeFixed != '--'
UNION ALL
SELECT SUM
( CAST ( FinalValueFeeVariable AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND FinalValueFeeVariable != '--'
UNION ALL
SELECT
SUM ( CAST ( VeryHighItemNotAsDescribedFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND VeryHighItemNotAsDescribedFee != '--'
UNION ALL
SELECT
SUM ( CAST ( InternationalFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND InternationalFee != '--'
UNION ALL
SELECT
SUM ( CAST ( BelowStandardPerformanceFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601]
WHERE
__LoginUserName LIKE '%vocalnamall%'
AND TransactioncreationDate >= '2023-05-01'
AND TransactioncreationDate <= '2023-05-31'
AND BelowStandardPerformanceFee != '--'
)AS UnionTable;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---对应表格
SELECT SUM( CAST ( NetAmount AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND NetAmount != '--'

SELECT SUM( CAST (replace(NetAmount,',','') AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%mymalldealshop%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND NetAmount != '--'

---对应表格
SELECT
SUM ( CAST ( GrossTransactionAmount AS FLOAT ) ) AS '交易总额'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND GrossTransactionAmount != '--'

SELECT
SUM ( CAST ( ItemSubtotal AS FLOAT ) ) AS '物品小计'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND ItemSubtotal != '--'

SELECT
SUM ( CAST ( EbayCollecteTax AS FLOAT ) ) AS 'eBay 收取的稅款'
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND EbayCollecteTax != '--'

SELECT SUM(ColumnName1) AS '佣金(成交費 — 固定\成交費 — 因類別而異\非常高「物品與說明不符」率的費用\表現低於標準的費用\跨國交易費
)' from (
SELECT SUM
( CAST ( FinalValueFeeFixed AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND FinalValueFeeFixed != '--'
UNION ALL
SELECT SUM
( CAST ( FinalValueFeeVariable AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND FinalValueFeeVariable != '--'
UNION ALL
SELECT
SUM ( CAST ( VeryHighItemNotAsDescribedFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND VeryHighItemNotAsDescribedFee != '--'
UNION ALL
SELECT
SUM ( CAST ( InternationalFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND InternationalFee != '--'
UNION ALL
SELECT
SUM ( CAST ( BelowStandardPerformanceFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801]
WHERE
__LoginUserName LIKE '%wonderoneyoustore%'
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31'
AND BelowStandardPerformanceFee != '--'
)AS UnionTable;
2023-08-08 17:47:58, 由 林鸿贵 添加备注。

存在业务下载的地区时间与采集时间不一致，导致数据校验不通过

只要是业务下载的地区时间是香港的，因为采集库也是通过香港时间去下载数据，经校验，各个项的总额统计是一致的
2023-08-09 11:33:48, 由 林鸿贵 完成。
修改了 总计消耗，旧值为 "8"，新值为 "8.1"。
修改了 指派给 ，旧值为 "linhonggui"，新值为 "zhoujie"。
修改了 实际完成，旧值为 ""，新值为 "2023-08-09 11:33:44"。
修改了 任务状态，旧值为 "doing"，新值为 "done"。
修改了 预计剩余，旧值为 "24"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "linhonggui"。
2023-08-09 11:50:24, 由周杰关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "zhoujie"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-08-09 11:50:24"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

已完成

基本信息
任务的一生

所属迭代	财务余额核对与金蝶对接V1
所属模块	/
相关需求	FMS：Ebay金蝶对接项目
指派给	Closed 于 2023-08-09 11:50:24
任务类型	测试
任务标签	初始
开发组	百货开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	周杰于 2023-08-03 14:38:32
由谁完成	林鸿贵于 2023-08-09 11:33:44
由谁取消	暂无
由谁关闭	周杰于 2023-08-09 11:50:24
关闭原因	已完成
最后编辑	周杰于 2023-08-09 11:50:24

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

工时信息

最初预计	32工时
总计消耗	8.1工时
预计剩余	0工时
预计开始	2023-08-07
实际开始	2023-08-07
截止日期	2023-08-10