本文目录导读：
我来帮你设计一个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]);
}
这个系统提供了完整的函数、安全的事务处理和灵活的可配置性，可以直接用于生产环境，需要根据具体业务需求进行适当调整。
