金蝶与采集作对比

余额页面展示统一

以亚马逊平台渠道+月份的维度(结算时间)

统计订单和非订单

orderid == 17位带扛的纯数字为订单，其他为非订单

金蝶数据sql:

SELECT
`t1`.`ID` AS `ID`,
`t1`.`AmazonOrderId` AS `AmazonOrderId`,
`t1`.`PostedDate` AS `PostedDate`,
t1.ProductSales + t1.ShippingCredits + t1.SalesTaxCollected + t1.CostOfPints + t1.PromotionalRebates + t1.GiftWrapCredits + t1.OtherInCome + t1.CouponFee + t1.AdsPayment + t1.SellingFees + t1.FbaFees + t1.FBAStorageFee + t1.FBAInboundFee + t1.FBARemovalFee + t1.OtherTransactionFees + t1.LongTermStorageFee KingdeeTotalAmount,
t1.OrderSourceID,
t1. AmazonOrderId REGEXP '^[0-9]{3}-[0-9]{7}-[0-9]{7}$' IsOrderFee

FROM
`ifc_payment_amazon` t1
INNER JOIN `db_account_book_strategy_match_payment` p ON ( `t1`.`BillNumber` = `p`.`BussniessBillNumber` ) 
WHERE
`p`.`AccountBookID` = 1 
AND `t1`.`PostedDate` >= '2023-06-01' 
AND `t1`.`PostedDate` < '2023-08-01' 
AND `t1`.`OrderSourceType` = 1 
AND t1.OrderSourceID IN (
'12325',
'15524',
'15523',
'15513',
'15514',
'15516',
'15517',
'15519',
'15520',
'15521',
'15522',
'15525',
'15512',
'15515',
'15518',
'15526',
'16478',
'16574',
'17466',
'18121')

SELECT
ID,
OrderId AmazonOrderId,
[Date] PostedDate,
Total PaymentTotalAmount,
12325 AS  OrderSourceId,
CASE WHEN OrderId LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END IsOrderFee
FROM
PaymentReports_IN ( nolock ) 
WHERE
SellerId = 'A3AVQ528T7B264' 
AND MarketplaceId = 'A21TJRUUN4KGV' 
AND [Date] >= '2023-06-01' 
AND [Date] < '2023-08-01' 
--AND [__CrawlConfigId] = 2568
union all
SELECT
ID,
OrderId AmazonOrderId,
[Date] PostedDate,
Total PaymentTotalAmount,
12325 AS  OrderSourceId,
CASE WHEN OrderId LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END IsOrderFee
FROM
PaymentReports_GB ( nolock ) 
WHERE
SellerId = 'A3TXUBTQ09KQLC' 
AND MarketplaceId = 'A1F83G8C2ARO7P' 
AND [Date] >= '2023-06-01' 
AND [Date] < '2023-08-01' 
--AND [__CrawlConfigId] = 2125

已完成