WooCommerce回购自定义插件：如何更新用户钱包余额？

我正在开发一款WooCommerce回购计算器插件，核心逻辑如下：

• 用户正常购买商品且订单完成后，可通过SKU申请回购
• 回购完成后扣除部分金额，剩余金额存入用户钱包
• 用户购买新品时，需从钱包余额中抵扣订单金额

目前已实现前端订单总额扣除钱包金额的功能，但数据库中的total_wallet_amount字段未同步更新。

现有代码 #

创建数据表的函数 #

function create_buy_back_calculator_table()
{
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'buy_back_calculator';
    $total_wallet_amount = $wpdb->prefix . 'total_wallet_amount';

    // 创建buy_back_calculator表的SQL语句
    $sql = "CREATE TABLE $table_name (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sku VARCHAR(255) NOT NULL,
        user_name VARCHAR(255) NOT NULL,
        user_email VARCHAR(255) NOT NULL,
        user_billing_address VARCHAR(255) NOT NULL,
        user_phone_number VARCHAR(20) NOT NULL,
        date_of_product_purchase DATE NOT NULL,
        date_of_buyback DATE NOT NULL,
        product_price DECIMAL(10, 2) NOT NULL,
        number_of_days INT NOT NULL,
        buy_back_price DECIMAL(10, 2) NOT NULL,
        amount_in_wallet_remaining DECIMAL(10, 2) NOT NULL,
        transit_status VARCHAR(255) NOT NULL,
        image_path VARCHAR(255) NOT NULL
    ) $charset_collate;";

    // 执行创建buy_back_calculator表的SQL查询
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    // 创建total_wallet_amount表的SQL语句
    $sql = "CREATE TABLE $total_wallet_amount (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_email VARCHAR(255) NOT NULL,
        total_wallet_amount DECIMAL(19, 4) NOT NULL
    ) $charset_collate;";

    // 执行创建total_wallet_amount表的SQL查询
    dbDelta($sql);
}

尝试的扣款函数（仅前端生效） #

function adjust_order_total_with_deduction()
{
    if (is_user_logged_in()) {
        global $wpdb;
        $current_user = wp_get_current_user();
        $user_email = $current_user->user_email;
        $total_wallet_amount_table = $wpdb->prefix . 'total_wallet_amount';
        $user_remaining_sum = $wpdb->get_var($wpdb->prepare("SELECT total_wallet_amount FROM $total_wallet_amount_table WHERE user_email = %s", $user_email));

        $deduction_amount = $user_remaining_sum; // 固定扣除金额
        if ($deduction_amount > 0) {
            WC()->cart->add_fee(__('Deduction Amount', 'your-text-domain'), -$deduction_amount);
        }
    }
}
add_action('woocommerce_cart_calculate_fees', 'adjust_order_total_with_deduction');

解决方案 #

问题出在woocommerce_cart_calculate_fees钩子仅负责修改前端显示的订单金额，不会触发数据库更新。需要在订单完成时同步更新钱包余额，同时优化抵扣逻辑避免异常情况。

修改后的完整代码：

// 前端订单金额扣除钱包余额，存储抵扣金额到会话
function adjust_order_total_with_deduction()
{
    if (is_user_logged_in()) {
        global $wpdb;
        $current_user = wp_get_current_user();
        $user_email = $current_user->user_email;
        $total_wallet_amount_table = $wpdb->prefix . 'total_wallet_amount';
        $user_remaining_sum = $wpdb->get_var($wpdb->prepare("SELECT total_wallet_amount FROM $total_wallet_amount_table WHERE user_email = %s", $user_email));

        // 确保抵扣金额不超过购物车总额，避免出现负数订单
        $cart_total = WC()->cart->get_subtotal();
        $deduction_amount = min(floatval($user_remaining_sum), floatval($cart_total));
        
        if ($deduction_amount > 0) {
            WC()->cart->add_fee(__('钱包余额抵扣', 'your-text-domain'), -$deduction_amount);
            // 将实际抵扣金额存入会话，用于订单完成时更新数据库
            WC()->session->set('wallet_deduction_amount', $deduction_amount);
        } else {
            WC()->session->set('wallet_deduction_amount', 0);
        }
    }
}
add_action('woocommerce_cart_calculate_fees', 'adjust_order_total_with_deduction');

// 订单完成时，更新数据库中的用户钱包余额
function update_wallet_balance_after_order_completion($order_id)
{
    $order = wc_get_order($order_id);
    $user_id = $order->get_user_id();
    
    if ($user_id === 0) return; // 跳过未登录用户的订单
    
    $current_user = get_user_by('id', $user_id);
    $user_email = $current_user->user_email;
    $deduction_amount = WC()->session->get('wallet_deduction_amount');
    
    if ($deduction_amount <= 0) return;
    
    global $wpdb;
    $total_wallet_amount_table = $wpdb->prefix . 'total_wallet_amount';
    
    // 执行数据库更新，扣除对应金额
    $wpdb->query($wpdb->prepare(
        "UPDATE $total_wallet_amount_table SET total_wallet_amount = total_wallet_amount - %f WHERE user_email = %s",
        $deduction_amount,
        $user_email
    ));
    
    // 清除会话中的抵扣金额，避免重复扣除
    WC()->session->__unset('wallet_deduction_amount');
}
add_action('woocommerce_order_status_completed', 'update_wallet_balance_after_order_completion');

关键优化点 #

• 通过WC()->session存储抵扣金额，避免用户放弃下单时误更新数据库
• 添加min()限制抵扣金额不超过购物车总额，防止订单金额为负
• 仅在订单状态变为completed时更新数据库，确保交易真实完成
• 处理未登录用户的情况，避免报错

内容的提问来源于stack exchange，提问作者Nilima Bose