PHP三级分销佣金计算

wen PHP项目 2026-08-11 131

本文目录导读：

PHP三级分销佣金计算

数据库表结构
核心PHP佣金计算类
使用示例
关键功能说明

我来帮你设计一个PHP三级分销佣金计算系统,这个系统将包含完整的数据库结构和计算逻辑。

数据库表结构

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT NULL,  -- 上级推荐人ID
    level INT DEFAULT 0,         -- 用户等级（可选）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES users(id)
);
-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    commission_rate_1 DECIMAL(5,2) DEFAULT 0.10,  -- 一级佣金比例
    commission_rate_2 DECIMAL(5,2) DEFAULT 0.05,  -- 二级佣金比例
    commission_rate_3 DECIMAL(5,2) DEFAULT 0.02   -- 三级佣金比例
);
-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,           -- 购买用户ID
    product_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,   -- 购买价格
    status TINYINT DEFAULT 0,       -- 订单状态：0待支付，1已支付，2已取消
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 佣金记录表
CREATE TABLE commission_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    user_id INT NOT NULL,           -- 获得佣金的用户ID
    from_user_id INT NOT NULL,      -- 产生订单的用户ID
    level TINYINT NOT NULL,         -- 佣金级别（1,2,3）
    amount DECIMAL(10,2) NOT NULL,  -- 佣金金额
    status TINYINT DEFAULT 0,       -- 0待结算，1已结算，2已取消
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (from_user_id) REFERENCES users(id)
);

核心PHP佣金计算类

<?php
class CommissionCalculator {
    private $db;  // PDO实例
    public function __construct(PDO $db) {
        $this->db = $db;
    }
    /**
     * 获取用户的上级推荐链（最多三级）
     */
    public function getAncestors($userId, $limit = 3) {
        $ancestors = [];
        $currentId = $userId;
        for ($i = 0; $i < $limit; $i++) {
            $sql = "SELECT id, parent_id FROM users WHERE id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$currentId]);
            $user = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$user || !$user['parent_id']) {
                break;
            }
            $currentId = $user['parent_id'];
            $ancestors[] = [
                'user_id' => $currentId,
                'level' => $i + 1
            ];
        }
        return $ancestors;
    }
    /**
     * 计算订单佣金
     */
    public function calculateCommission($orderId) {
        // 获取订单信息
        $orderSql = "SELECT * FROM orders WHERE id = ?";
        $orderStmt = $this->db->prepare($orderSql);
        $orderStmt->execute([$orderId]);
        $order = $orderStmt->fetch(PDO::FETCH_ASSOC);
        if (!$order) {
            throw new Exception("订单不存在");
        }
        // 获取商品佣金比例
        $productSql = "SELECT * FROM products WHERE id = ?";
        $productStmt = $this->db->prepare($productSql);
        $productStmt->execute([$order['product_id']]);
        $product = $productStmt->fetch(PDO::FETCH_ASSOC);
        if (!$product) {
            throw new Exception("商品不存在");
        }
        // 获取购买用户的三级上级
        $ancestors = $this->getAncestors($order['user_id'], 3);
        // 计算佣金
        $commissions = [];
        $price = $order['price'];
        foreach ($ancestors as $ancestor) {
            $level = $ancestor['level'];
            // 根据级别获取佣金比例
            switch ($level) {
                case 1:
                    $rate = $product['commission_rate_1'];
                    break;
                case 2:
                    $rate = $product['commission_rate_2'];
                    break;
                case 3:
                    $rate = $product['commission_rate_3'];
                    break;
                default:
                    $rate = 0;
            }
            $amount = round($price * $rate, 2);
            if ($amount > 0) {
                $commissions[] = [
                    'order_id' => $orderId,
                    'user_id' => $ancestor['user_id'],
                    'from_user_id' => $order['user_id'],
                    'level' => $level,
                    'amount' => $amount
                ];
            }
        }
        return $commissions;
    }
    /**
     * 保存佣金记录（订单支付成功后调用）
     */
    public function saveCommissions($orderId) {
        // 检查佣金是否已存在
        $checkSql = "SELECT COUNT(*) FROM commission_records WHERE order_id = ?";
        $checkStmt = $this->db->prepare($checkSql);
        $checkStmt->execute([$orderId]);
        if ($checkStmt->fetchColumn() > 0) {
            throw new Exception("该订单佣金已计算");
        }
        // 计算佣金
        $commissions = $this->calculateCommission($orderId);
        // 开启事务
        $this->db->beginTransaction();
        try {
            $insertSql = "INSERT INTO commission_records 
                         (order_id, user_id, from_user_id, level, amount) 
                         VALUES (?, ?, ?, ?, ?)";
            $insertStmt = $this->db->prepare($insertSql);
            $totalCommission = 0;
            foreach ($commissions as $commission) {
                $insertStmt->execute([
                    $commission['order_id'],
                    $commission['user_id'],
                    $commission['from_user_id'],
                    $commission['level'],
                    $commission['amount']
                ]);
                $totalCommission += $commission['amount'];
            }
            // 更新订单状态为已支付
            $updateOrderSql = "UPDATE orders SET status = 1 WHERE id = ?";
            $updateOrderStmt = $this->db->prepare($updateOrderSql);
            $updateOrderStmt->execute([$orderId]);
            $this->db->commit();
            return [
                'success' => true,
                'total_commission' => $totalCommission,
                'details' => $commissions
            ];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
    /**
     * 取消订单时撤回佣金
     */
    public function cancelCommissions($orderId) {
        $sql = "UPDATE commission_records SET status = 2 WHERE order_id = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$orderId]);
        // 更新订单状态为已取消
        $updateOrderSql = "UPDATE orders SET status = 2 WHERE id = ?";
        $updateOrderStmt = $this->db->prepare($updateOrderSql);
        $updateOrderStmt->execute([$orderId]);
        return $stmt->rowCount();
    }
    /**
     * 获取用户佣金汇总
     */
    public function getUserCommissionSummary($userId) {
        $sql = "SELECT 
                    level,
                    COUNT(*) as order_count,
                    SUM(amount) as total_amount,
                    SUM(CASE WHEN status = 0 THEN amount ELSE 0 END) as pending_amount,
                    SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) as settled_amount
                FROM commission_records 
                WHERE user_id = ? 
                GROUP BY level";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $summary = [
            'total' => array_sum(array_column($results, 'total_amount')),
            'levels' => []
        ];
        foreach ($results as $row) {
            $summary['levels'][] = [
                'level' => $row['level'],
                'order_count' => $row['order_count'],
                'total' => $row['total_amount'],
                'pending' => $row['pending_amount'],
                'settled' => $row['settled_amount']
            ];
        }
        return $summary;
    }
    /**
     * 获取佣金明细列表
     */
    public function getCommissionDetail($userId, $page = 1, $pageSize = 20) {
        $offset = ($page - 1) * $pageSize;
        $sql = "SELECT 
                    cr.*, 
                    u.username as from_user_name,
                    p.name as product_name
                FROM commission_records cr
                LEFT JOIN users u ON cr.from_user_id = u.id
                LEFT JOIN orders o ON cr.order_id = o.id
                LEFT JOIN products p ON o.product_id = p.id
                WHERE cr.user_id = ?
                ORDER BY cr.created_at DESC
                LIMIT $offset, $pageSize";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

使用示例

<?php
// 初始化数据库连接
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';
$username = 'root';
$password = '';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
$db = new PDO($dsn, $username, $password, $options);
$calculator = new CommissionCalculator($db);
// 假设用户注册，建立推荐关系
function registerUser($db, $username, $parentId = null) {
    $stmt = $db->prepare("INSERT INTO users (username, parent_id) VALUES (?, ?)");
    $stmt->execute([$username, $parentId]);
    return $db->lastInsertId();
}
// 创建测试用户
$u1 = registerUser($db, '张总');      // 顶级用户
$u2 = registerUser($db, '李经理', $u1); // 二级用户
$u3 = registerUser($db, '王主管', $u2); // 三级用户
$u4 = registerUser($db, '赵员工', $u3); // 购买者
// 商品佣金设置
$productSql = "INSERT INTO products (name, price, commission_rate_1, commission_rate_2, commission_rate_3) 
               VALUES ('商品A', 1000.00, 0.10, 0.05, 0.02)";
$db->exec($productSql);
$productId = $db->lastInsertId();
// 创建订单
$orderSql = "INSERT INTO orders (user_id, product_id, price) VALUES (?, ?, ?)";
$stmt = $db->prepare($orderSql);
$stmt->execute([$u4, $productId, 1000.00]);
$orderId = $db->lastInsertId();
// 计算并保存佣金
try {
    $result = $calculator->saveCommissions($orderId);
    echo "佣金计算成功！\n";
    echo "总佣金：{$result['total_commission']} 元\n\n";
    foreach ($result['details'] as $detail) {
        echo "第{$detail['level']}级佣金：{$detail['amount']} 元（用户ID: {$detail['user_id']}）\n";
    }
    // 查询佣金汇总
    echo "\n--- 李经理的佣金汇总 ---\n";
    $summary = $calculator->getUserCommissionSummary($u2);
    print_r($summary);
    // 获取佣金明细
    echo "\n--- 佣金明细 ---\n";
    $details = $calculator->getCommissionDetail($u2);
    foreach ($details as $detail) {
        echo "订单：{$detail['product_name']}，级别：{$detail['level']}，佣金：{$detail['amount']} 元\n";
    }
} catch (Exception $e) {
    echo "错误：{$e->getMessage()}\n";
}

关键功能说明

佣金计算规则

一级佣金：直接推荐人获得10%
二级佣金：上级的上级获得5%
三级佣金：第三级推荐人获得2%

核心特点

自动计算：根据用户关系自动找到三级上级
防重复计算：检查佣金记录防止重复计算
事务处理：确保数据一致性
状态管理：支持待结算、已结算、已取消状态
灵活配置：每个商品可独立设置佣金比例

实际应用建议

// 订单支付成功后调用
$calculator->saveCommissions($orderId);
// 订单退货时调用
$calculator->cancelCommissions($orderId);
// 佣金结算
function settleCommissions($userId, $adminId) {
    $stmt = $db->prepare("UPDATE commission_records 
                         SET status = 1, settled_at = NOW() 
                         WHERE user_id = ? AND status = 0");
    $stmt->execute([$userId]);
    // 记录结算操作日志（需要添加settled_at字段）
    $logStmt = $db->prepare("INSERT INTO commission_settlements 
                            (user_id, admin_id, amount) 
                            SELECT ?, ?, SUM(amount) 
                            FROM commission_records 
                            WHERE user_id = ? AND status = 0");
    $logStmt->execute([$userId, $adminId, $userId]);
}

这个系统提供了完整的函数、安全的事务处理和灵活的可配置性，可以直接用于生产环境，需要根据具体业务需求进行适当调整。

本文地址： https://www.bmjup.com/post/43664.html