影响范围

速卖通平台订单联盟佣金和交易佣金计算是否准确

提交记录

自测报告

1、建速卖通客户订单，并关联好平台订单和后台订单关系。

2、计算订单费用

3、对照需求计算逻辑看费用是否准确

附上计算逻辑sql如下：

DECLARE @ProductTotalPrice DECIMAL(18,2)=0
DECLARE @Tex DECIMAL(18,2)=0
DECLARE @TransportPay DECIMAL(18,2)=0
DECLARE @SellerOrderAmountAmount DECIMAL(18,2)=0
DECLARE @DiscountPrice DECIMAL(18,2)=0
DECLARE @ShipTex DECIMAL(18,2)=0
DECLARE @ProductTexPrice DECIMAL(18,2)=0
DECLARE @AfflicateFeeRate DECIMAL(18,2)=0
DECLARE @EscrowFeeRate DECIMAL(18,2)=0
SELECT @ProductTotalPrice=ISNULL(oc.ProductPriceAmount,0)*oc.ProductCount,
@Tex=ISNULL(ao.OrderAmountAmount,0) - ISNULL(ao.SellerOrderAmountAmount,0),
@TransportPay=oi.TransportPay,
@SellerOrderAmountAmount =  ao.SellerOrderAmountAmount,
@AfflicateFeeRate = oc.AfflicateFeeRate,
@EscrowFeeRate = oc.EscrowFeeRate
FROM RB_AliExpress_OrderChildOrderList oc(NOLOCK) INNER JOIn RB_AliExpress_OrderInfo(NOLOCK) ao
ON oc.CustomerID=ao.CustomerID AND oc.AliExpressOrderID=ao.AliExpressOrderID
INNER JOIN RB_OrderInfo oi ON ao.CustomerID=oi.CustomerID AND ao.AliExpressOrderID=oi.ClientOrderCode
WHERE oc.AliExpressOrderID='8151235343874884' 

SELECT @DiscountPrice = @ProductTotalPrice - @Tex + @TransportPay - @SellerOrderAmountAmount
SELECT @ShipTex = (@TransportPay /(@ProductTotalPrice+@TransportPay-@DiscountPrice))*@Tex
SELECT @ProductTexPrice = @Tex - @ShipTex;

SELECT (@ProductTotalPrice - @DiscountPrice - @ProductTexPrice) * @AfflicateFeeRate AS '联盟拥金',
(@ProductTotalPrice - @DiscountPrice - @ProductTexPrice) * @EscrowFeeRate + ((@TransportPay- @ShipTex) * 0.05) AS '交易佣金'

//产品总价
decimal productTotalPrice = aliExpressOrderList!=null && aliExpressOrderList.Any()? aliExpressOrderList.Sum(a => (a.ProductPriceAmount * (a.ProductCount ?? 0))) : 0;
//税费收入
decimal tex = aliExpressOrderInfo.OrderAmountAmount - sellerOrderAmountAmount;
//运费收入
decimal transportPay = _orderInfo.TransportPay;
//总收入（不含税）
decimal totalPrice = aliExpressOrderInfo.OrderAmountAmount;
//优惠金额=产品总价-税费收入+运费收入-总收入（不含税）
decimal discountPrice = productTotalPrice - tex + transportPay - totalPrice;
//运费税费=（运费收入/（产品总价+运费收入-优惠金额））*税费收入
decimal shipTex = (transportPay / (productTotalPrice + transportPay - discountPrice)) * tex;
//产品总价税费=税费收入-运费税费
decimal productTexPrice = tex - shipTex;
//联盟佣金比例
var afflicateFee = aliExpressOrderList.Where(a=>a.AfflicateFeeRate.HasValue)?.FirstOrDefault().AfflicateFeeRate ?? 0;
//联盟佣金=(产品总价-优惠金额-产品总价税费)*联盟佣金比例
totalAfflicateFee = (productTotalPrice - discountPrice - productTexPrice) * afflicateFee;
//交易佣金比例
var escrowFee = aliExpressOrderList.Where(a=> a.EscrowFeeRate.HasValue).FirstOrDefault().EscrowFeeRate ?? 0;
//交易佣金=(产品总价-优惠金额-产品总价税费)*交易佣金比例+(运费-运费税费)*运费佣金
totalEscrowFee = (productTotalPrice - discountPrice - productTexPrice) * escrowFee + ((transportPay- shipTex) * 0.05M) ;