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

测算账号：

SELECT
api.CustomerID ,
api.OrderSourceID ,
api.DevIDSellerId ,
api.AppIDMarketplaceId,
os.OrderSourceCountry
FROM
RB_OrderSource_EbayAmazonAPI api ( NOLOCK )
LEFT OUTER JOIN RB_OrderSource os WITH ( NOLOCK ) ON os.CustomerID = api.CustomerID 
AND os.ID = api.OrderSourceID 
WHERE
os.ID in(12325, 15524, 15523, 15513, 15514, 15516, 15517, 15519, 15520, 15521, 15522, 15525, 15512, 15515, 15518, 15526, 16478, 16574, 17466, 18121)

采集源数据：

SELECT top 200 * from PaymentReports_IN (nolock) WHERE SellerId = 'A3AVQ528T7B264' and  MarketplaceId = 'A21TJRUUN4KGV' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_GB (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A1F83G8C2ARO7P' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_DE (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A1PA6795UKMFR9' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_IT (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'APJ6JRA9NG5V4' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_FR (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A13V1IB3VIYZZH' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_ES (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A1RKKUPIHCS9HS' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_PL (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A1C3SOZRARQ6R3' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_SE (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A2NODRKZP88ZB9' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_NL (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A1805IZSGTT6HS' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_US (nolock) WHERE SellerId = 'A4ZM173VY1VFI' and  MarketplaceId = 'ATVPDKIKX0DER' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_CA (nolock) WHERE SellerId = 'A4ZM173VY1VFI' and  MarketplaceId = 'A2EUQ1WTGCTBG2' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_MX (nolock) WHERE SellerId = 'A4ZM173VY1VFI' and  MarketplaceId = 'A1AM78C64UM0Y8' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_AU (nolock) WHERE SellerId = 'A3PS6FNOWI5ENO' and  MarketplaceId = 'A39IBJ37TRP1C6' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_SA (nolock) WHERE SellerId = 'A16W7VZAROKXQD' and  MarketplaceId = 'A17E79C6D8DWNP' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_AE (nolock) WHERE SellerId = 'AZ7LC9QL7BXMO' and  MarketplaceId = 'A2VIGQ35RCS4UG' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_SG (nolock) WHERE SellerId = 'A20NNJ71L9IBC2' and  MarketplaceId = 'A19VAU5U5O7RUS' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_BR (nolock) WHERE SellerId = 'A4ZM173VY1VFI' and  MarketplaceId = 'A2Q3Y263D00KWC' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_TR (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'A33AVAJ2PDY3EV' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_BE (nolock) WHERE SellerId = 'A3TXUBTQ09KQLC' and  MarketplaceId = 'AMEN7PMS3EDWL' and RequestDate > '2023-08-01' ORDER BY id desc
SELECT top 200 * from PaymentReports_JP (nolock) WHERE SellerId = 'A2LX3H3VVDAYB3' and  MarketplaceId = 'A1VC38T7YXB528' and RequestDate > '2023-08-01' ORDER BY id desc

初始化6月期初。5月期末

UPDATE ifc_eventsverify_report_amazon ra
INNER JOIN ordersource os ON ra.OrderSourceName = os.ordersource_name 
SET ra.OrderSourceId = os.ordersource_id


UPDATE
ifc_eventsverify_report_amazon ra
INNER JOIN ordersource os ON ra.OrderSourceId = os.ordersource_id
INNER JOIN admin ad ON ad.AdminId = os.sale_admin_id
INNER JOIN department de on ad.DepartmentId = de.DepartmentId
INNER JOIN department dep on de.ParentId = dep.DepartmentId
set ra.DepartmentId = dep.DepartmentId,ra.DepartmentName = dep.DepartmentName

修改币种：

UPDATE ifc_eventsverify_report_amazon ra
INNER JOIN Db_Irobotbox_Data t1 ON t1.OtherData = ra.Currency 
AND t1.IsDelete =FALSE
INNER JOIN Db_Irobotbox_Account_Configdata t2 ON t1.ID = t2.IrobotBoxDataID 
AND t2.AccountBookID = 1 
AND t2.IsDelete =FALSE
INNER JOIN Db_Kingdee_Data t3 ON t3.ID = t2.KingdeeID 
AND t2.AccountBookID = t3.AccountBookID 
AND t1.BasicDataTypeID = 5
INNER JOIN Db_Kingdee_Data t4 ON t4.FCYFORID = t3.KingdeeCode 
AND t3.AccountBookID = t4.AccountBookID 
AND t4.DataYear = '2023' 
AND t4.DataMonth = '05' 
SET ra.ExchangeRate = t4.FExchangeRate

已更改逻辑在正式环境匹配了数据，待后续核查数据问题