需求背景：因为现在仓租费用没有自动导入利润表，从而导致估算的毛利虚增，影响数据100万左右。为了解决这个问题现在每月手动导入盛宝隆仓租，需要花费技术、财务大量时间处理数据和核对数据，并且导入的数据也不一定准。大约每月耗费时间2人天/月

需求内容：下面的内容做成服务，每个月自动跑，不再需要人工介入

一、定时从盛宝隆仓租费-费用明细列表API获取仓租费用数据，同步至Fee_WarehouseChargeCountdetails

每天获取结算时间为前一天的

二、Fee_WarehouseChargeCountdetails表数据按照仓租费用均摊逻辑把箱产品的仓租费用均摊至条码产品，并把均摊后的数据同步至Fee_WarehouseChargeCountResult

箱总仓租费用  *（箱内X条码产品体积  /  箱内所有产品体积之和）=  X条码产品的均摊费用

三、费用均摊至Fee_WarehouseChargeCountResult 表之后，对定时跑服务对数据进行匹配

匹配逻辑1：

使用Fee_WarehouseChargeCountResult  的FNSKU 与 RB_Amazon_Product_Base、RB_OrderSource的FNSKU匹配数据

从RB_Amazon_Product_Base表关联的字段：

CustomerID

OrderSourceID

OrderSourceName

ClientSKU AS ClientSKU
OrderSourceSKU AS SellerSKU

BusinessAdminID AS BusinessAdminID
BusinessUserName AS BusinessAdminName

BusinessTeam AS BusinessTeamID
BusinessTeamName AS BusinessTeamName

BusinessTeam AS SalesTeamID
BusinessTeamName AS SalesTeamName

ASINorItemID AS ASINorItemID

从RB_OrderSource表关联的字段：

OrderSourceCountry AS OrderSourceContry

SaleAdminID AS SalesAdminID

SaleAdminName AS SalesAdminName

匹配逻辑2：

Fee_WarehouseChargeCountResult 表中使用匹配逻辑1匹配不上的数据且ErpChannelSourceId不为空值时，继续使用表中的"ProductSku" " ChildFnsku"字段和赛盒wms库存库龄报告的"商品编码" "FNSKU"字段匹配，两个字段都能同时匹配上之时，则取赛盒wms库存库龄报告表中的渠道编码填充到Fee_WarehouseChargeCountResult 表的"ErpChannelSourceId"字段

四、从RB_Customer_Currency获取汇率信息把原币金额转换为人民币

-ABS(ff.ChargingAmount * x.ExchangeRMB) = ImportFee

五、第3步匹配处理的数据自动同步至Tmp_Report_OrderSettlement利润临时表之中

六、同步至Tmp_Report_OrderSettlement的数据自动同步到 RB_Report_OrderSettlement 利润表

同步利润表逻辑：

   pb.CustomerID,
       ff.ChargingDate AS SettlementDate,
       pb.OrderSourceID,
       pb.OrderSourceName,
       '' AS ClientOrderCode,
       '' AS OrderCode,
       '' AS TransactionId,
       ff.ChargingDate AS PayTime,
       ff.ChargingDate AS ShipTime,
       os.OrderSourceCountry AS OrderSourceContry,
       Y.Id AS WareHouseID,
       Y.WareHouseName AS WareHouseName,
       0 AS TransportID,
       '' AS TransportName,
       '' AS TrackNumbers,
       -- -ABS(ff.ChargingAmount * x.ExchangeRMB) AS TotalPrice,
     0 AS TotalPrice,
       x.ExchangeRMB AS ExchangeRMB,
       --人民币兑美元汇率
       @USD_Exchange AS USD_ExchangeRMB,
       x.Currency AS Currency,
       '' AS OperatorName,
       '盛宝隆仓租导入2022010122020210-36092.536117675' AS OrderDescription,
       ff.ProductSku AS SKU,
       pb.ClientSKU AS ClientSKU,
       pb.OrderSourceSKU AS SellerSKU,
       ff.ChildFnsku AS OrderItemId,
       0 AS ProductSalesPrice,
       0 AS ProductShipPrice,
       pb.ProductNameCN AS ProductNameCN,
       0 AS ProductGrossWeight,
       0 AS LastSupplierPrice,
       1 AS ProductNum,
       0 AS ProdutNumUpdate,
       '' AS GroupSKU,
       0 AS GroupProductNum,
       '' AS AssignDevelopAdminName,
       '' AS DevelopAdminName,
       '' AS EditAdminName,
       '' AS ImageAdminName,
       pb.BusinessAdminID AS BusinessAdminID,
       pb.BusinessUserName AS BusinessAdminName,
       '' AS BuyerName,
       '' AS CheckAdminName,
       pb.BusinessTeam AS BusinessTeamID,
       pb.BusinessTeamName AS BusinessTeamName,
     -ABS(ff.ChargingAmount * x.ExchangeRMB) AS Profits,
     --0 AS  Profits,
       0 AS Incomes,
       -ABS(ff.ChargingAmount * x.ExchangeRMB) AS Expenses,
       0 AS SalesPrice,
       0 AS TransportPay,
       0 AS SalesRefunds,
       0 AS PromotionRebates,
       0 AS OtherCredits,
       0 AS CommisionFee,
       0 AS FBATransactionFees,
       -- -ABS(ff.ChargingAmount * x.ExchangeRMB) AS OrderSourceOtherDebits,
     0 AS OrderSourceOtherDebits,
       0 AS BuyPrice,
       0 AS PackageFees,
       0 AS OverSeasShipFees,
       0 AS FirstLegFees,
       0 AS TariffFees,
       0 AS ExchangeLost,
       0 AS ProductReturnsFee,
       ff.ChargingDate AS AddTime,
       1 AS OrderSourceType,
       0 AS PackagedCost,
       ff.ChargingDate AS OrderAddedTime,
       0 AS OrderStatus,
       3 AS OrderState,
       os.SaleAdminID AS SalesAdminID,
       os.SaleAdminName AS SalesAdminName,
       --销售小组
       pb.BusinessTeam AS SalesTeamID,
       pb.BusinessTeamName AS SalesTeamName,
       ff.ChargingDate AS ConfirmTime,
       ff.ChargingDate AS PayDate,
       ff.ChargingDate AS SettlementShortDate,
       '' AS DevIDSellerId,
       '' AS SellerUserID,
       '' AS PaypalAccount,
       0 AS CostOfAdvertising,
       0 AS IsBiSycnComplete,
       GETDATE() AS BiSycnCompleteTime,
       ff.ChargingDate AS ShipDate,
       GETDATE() AS UpdateTime,
       CONVERT(INT,ff.CustomerCode) AS OrderListID,
       0 AS SalesVatFee,
       0 AS SubsidyPrice,
       0 AS DistributionPrice,
       pb.ASINorItemID AS ASINorItemID,
       '' AS ProductGroupSKU,
       0 AS AddOrderReasonType,
       0 AS WareHouseManagementPrice,
       0 AS CustomFeeOtherCostPrice,
       0 AS TaxRefundFee,
       0 AS Tax,
       0 AS MarketplaceFacilitatorTax,
       -ABS(ff.ChargingAmount * x.ExchangeRMB)  AS ImportFee,
       0 AS PromotionType,
       0 AS CostOfStorageFee,
       0 AS RelationID,
       0 AS IsBuildFee,
       ff.ID AS FeeWarehouseChargeID