本文目录导读：
我来为你提供一个完整的PHP积分日志记录系统实现方案。
数据库设计
-- 积分日志表 CREATE TABLE `points_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL COMMENT '用户ID', `points_change` int(11) NOT NULL COMMENT '积分变动值(正数为增加，负数为扣减)', `points_balance` int(11) NOT NULL COMMENT '变动后积分余额', `type` varchar(50) NOT NULL COMMENT '积分类型：sign_in/buy/refund/exchange/admin_adjust等', varchar(100) NOT NULL COMMENT '积分标题', `description` text COMMENT '详细描述', `order_id` varchar(50) DEFAULT NULL COMMENT '关联订单号', `operator` varchar(50) DEFAULT 'system' COMMENT '操作者', `extra` json DEFAULT NULL COMMENT '扩展信息', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), KEY `idx_type` (`type`), KEY `idx_user_time` (`user_id`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分日志表'; -- 积分规则表 CREATE TABLE `points_rule` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rule_code` varchar(50) NOT NULL COMMENT '规则编码', `rule_name` varchar(100) NOT NULL COMMENT '规则名称', `points` int(11) NOT NULL COMMENT '积分值', `status` tinyint(1) DEFAULT 1 COMMENT '状态：1启用，0禁用', `frequency` varchar(50) DEFAULT NULL COMMENT '频率限制：once/day/week/month', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_rule_code` (`rule_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分规则表';
基础积分类
<?php
/**
* 积分日志服务类
*/
class PointsLogger {
private $db;
private $redis;
public function __construct($db, $redis = null) {
$this->db = $db;
$this->redis = $redis;
}
/**
* 记录积分日志
* @param int $userId 用户ID
* @param int $points 积分变动值
* @param string $type 积分类型
* @param string $title 标题
* @param array $options 选项
* @return bool
*/
public function log($userId, $points, $type, $title, $options = []) {
try {
// 开启事务
$this->db->beginTransaction();
// 1. 检查积分余额
$balance = $this->getUserBalance($userId);
// 如果是扣减积分，检查余额是否足够
if ($points < 0 && $balance + $points < 0) {
throw new Exception("积分余额不足");
}
// 2. 更新用户积分余额
$newBalance = $this->updateUserBalance($userId, $points);
// 3. 插入积分日志
$logData = [
'user_id' => $userId,
'points_change' => $points,
'points_balance' => $newBalance,
'type' => $type,
'title' => $title,
'description' => $options['description'] ?? '',
'order_id' => $options['order_id'] ?? null,
'operator' => $options['operator'] ?? 'system',
'extra' => isset($options['extra']) ? json_encode($options['extra']) : null
];
$this->insertLog($logData);
// 4. 添加Redis缓存
if ($this->redis) {
$this->addRedisCache($userId, $logData);
}
$this->db->commit();
return true;
} catch (Exception $e) {
$this->db->rollBack();
error_log("积分记录失败: " . $e->getMessage());
return false;
}
}
/**
* 获取用户积分余额
*/
private function getUserBalance($userId) {
$sql = "SELECT points_balance FROM users WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId]);
return $stmt->fetchColumn() ?: 0;
}
/**
* 更新用户积分余额
*/
private function updateUserBalance($userId, $points) {
$sql = "UPDATE users SET points_balance = points_balance + ? WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$points, $userId]);
return $this->getUserBalance($userId);
}
/**
* 插入日志记录
*/
private function insertLog($data) {
$sql = "INSERT INTO points_log (user_id, points_change, points_balance, type, title, description, order_id, operator, extra)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $this->db->prepare($sql);
$stmt->execute([
$data['user_id'],
$data['points_change'],
$data['points_balance'],
$data['type'],
$data['title'],
$data['description'],
$data['order_id'],
$data['operator'],
$data['extra']
]);
}
/**
* Redis缓存处理
*/
private function addRedisCache($userId, $logData) {
$cacheKey = "user_points_log:{$userId}";
// 存储最近100条记录
$this->redis->lPush($cacheKey, json_encode($logData));
$this->redis->lTrim($cacheKey, 0, 99);
// 设置过期时间（可选）
$this->redis->expire($cacheKey, 86400); // 24小时
}
}
积分规则处理类
<?php
/**
* 积分规则处理类
*/
class PointsManager {
private $db;
private $logger;
private $redis;
public function __construct($db, $logger, $redis = null) {
$this->db = $db;
$this->logger = $logger;
$this->redis = $redis;
}
/**
* 处理积分奖励
* @param int $userId 用户ID
* @param string $ruleCode 规则编码
* @param array $context 上下文信息
* @return array [status, message]
*/
public function reward($userId, $ruleCode, $context = []) {
// 获取规则配置
$rule = $this->getRule($ruleCode);
if (!$rule || $rule['status'] != 1) {
return ['status' => false, 'message' => '规则不存在或已禁用'];
}
// 检查频率限制
if (!$this->checkFrequency($userId, $ruleCode, $rule['frequency'])) {
return ['status' => false, 'message' => '超出获取频率限制'];
}
// 记录积分
$result = $this->logger->log(
$userId,
$rule['points'],
$ruleCode,
$rule['rule_name'],
[
'description' => $context['description'] ?? '',
'order_id' => $context['order_id'] ?? null,
'operator' => $context['operator'] ?? 'system',
'extra' => $context['extra'] ?? []
]
);
if ($result) {
// 标记已获取（用于频率限制）
$this->markReward($userId, $ruleCode);
return ['status' => true, 'message' => '积分奖励成功'];
}
return ['status' => false, 'message' => '积分奖励失败'];
}
/**
* 获取规则信息
*/
private function getRule($ruleCode) {
// 优先从缓存获取
if ($this->redis) {
$cached = $this->redis->get("points_rule:{$ruleCode}");
if ($cached) {
return json_decode($cached, true);
}
}
$sql = "SELECT * FROM points_rule WHERE rule_code = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$ruleCode]);
$rule = $stmt->fetch(PDO::FETCH_ASSOC);
if ($rule && $this->redis) {
$this->redis->set("points_rule:{$ruleCode}", json_encode($rule), 3600);
}
return $rule;
}
/**
* 检查频率限制
*/
private function checkFrequency($userId, $ruleCode, $frequency) {
if (!$frequency) return true;
$key = "points_freq:{$userId}:{$ruleCode}:{$frequency}";
if ($this->redis) {
$count = $this->redis->get($key);
if ($count && $count >= $this->getFrequencyLimit($frequency)) {
return false;
}
// 设置过期时间
$expire = $this->getFrequencyExpire($frequency);
$this->redis->expire($key, $expire);
return true;
}
// 数据库查询方式
$sql = "SELECT COUNT(*) FROM points_log
WHERE user_id = ? AND type = ? AND
created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)";
$interval = $this->getFrequencyInterval($frequency);
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $ruleCode, $interval]);
$count = $stmt->fetchColumn();
return $count < $this->getFrequencyLimit($frequency);
}
/**
* 获取频率限制
*/
private function getFrequencyLimit($frequency) {
$limits = [
'once' => 1,
'day' => 1,
'week' => 1,
'month' => 1
];
return $limits[$frequency] ?? 99;
}
/**
* 获取频率过期时间
*/
private function getFrequencyExpire($frequency) {
$expires = [
'once' => 86400, // 1天
'day' => 86400, // 1天
'week' => 604800, // 7天
'month' => 2592000 // 30天
];
return $expires[$frequency] ?? 86400;
}
/**
* 获取频率间隔（小时）
*/
private function getFrequencyInterval($frequency) {
$intervals = [
'once' => 24,
'day' => 24,
'week' => 168,
'month' => 720
];
return $intervals[$frequency] ?? 24;
}
/**
* 标记已获取
*/
private function markReward($userId, $ruleCode) {
if (!$this->redis) return;
$key = "points_freq:{$userId}:{$ruleCode}:";
// 标记当前日期
$this->redis->incr($key . 'day');
$this->redis->expire($key . 'day', 86400);
}
}
查询和展示
<?php
/**
* 积分日志查询类
*/
class PointsLogQuery {
private $db;
private $redis;
public function __construct($db, $redis = null) {
$this->db = $db;
$this->redis = $redis;
}
/**
* 分页查询用户积分日志
* @param int $userId 用户ID
* @param int $page 页码
* @param int $limit 每页条数
* @param array $filters 过滤条件
* @return array
*/
public function getLogs($userId, $page = 1, $limit = 10, $filters = []) {
$offset = ($page - 1) * $limit;
$where = "WHERE user_id = ?";
$params = [$userId];
// 按类型过滤
if (!empty($filters['type'])) {
$where .= " AND type = ?";
$params[] = $filters['type'];
}
// 按日期过滤
if (!empty($filters['start_date'])) {
$where .= " AND created_at >= ?";
$params[] = $filters['start_date'];
}
if (!empty($filters['end_date'])) {
$where .= " AND created_at <= ?";
$params[] = $filters['end_date'];
}
// 统计总数
$countSql = "SELECT COUNT(*) FROM points_log {$where}";
$stmt = $this->db->prepare($countSql);
$stmt->execute($params);
$total = $stmt->fetchColumn();
// 获取数据
$sql = "SELECT * FROM points_log {$where} ORDER BY created_at DESC LIMIT ? OFFSET ?";
$stmt = $this->db->prepare($sql);
$params[] = $limit;
$params[] = $offset;
$stmt->execute($params);
$logs = $stmt->fetchAll(PDO::FETCH_ASSOC);
return [
'total' => $total,
'page' => $page,
'limit' => $limit,
'pages' => ceil($total / $limit),
'data' => $logs
];
}
/**
* 汇总统计
*/
public function getStats($userId) {
$stats = [
'total_earned' => 0,
'total_spent' => 0,
'points_count' => 0,
'today_earned' => 0,
'today_spent' => 0
];
// 总计收入
$sql = "SELECT SUM(points_change) as total FROM points_log
WHERE user_id = ? AND points_change > 0";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId]);
$stats['total_earned'] = $stmt->fetchColumn() ?: 0;
// 总计支出
$sql = "SELECT SUM(points_change) as total FROM points_log
WHERE user_id = ? AND points_change < 0";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId]);
$stats['total_spent'] = $stmt->fetchColumn() ?: 0;
// 符合条件记录数
$sql = "SELECT COUNT(*) FROM points_log WHERE user_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId]);
$stats['points_count'] = $stmt->fetchColumn();
return $stats;
}
/**
* 导出日志
*/
public function export($userId, $filters = []) {
$where = "WHERE user_id = ?";
$params = [$userId];
if (!empty($filters['type'])) {
$where .= " AND type = ?";
$params[] = $filters['type'];
}
$sql = "SELECT * FROM points_log {$where} ORDER BY created_at DESC";
$stmt = $this->db->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
使用示例
<?php
// 初始化
$db = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "user", "password");
$redis = new Redis();
$redis->connect('localhost', 6379);
$logger = new PointsLogger($db, $redis);
$manager = new PointsManager($db, $logger, $redis);
$query = new PointsLogQuery($db, $redis);
// 1. 用户签到获取积分
$userId = 123;
$result = $manager->reward($userId, 'sign_in', [
'description' => '每日签到奖励',
'extra' => ['ip' => '127.0.0.1']
]);
if ($result['status']) {
echo "签到成功，获得积分！";
} else {
echo $result['message'];
}
// 2. 购买商品获得积分
$result = $manager->reward($userId, 'buy', [
'description' => '购买商品积分奖励',
'order_id' => 'ORDER2024001',
'extra' => ['amount' => 100]
]);
// 3. 查询积分日志
$logs = $query->getLogs($userId, 1, 10, [
'type' => 'sign_in',
'start_date' => '2024-01-01',
'end_date' => '2024-01-31'
]);
// 4. 获取统计信息
$stats = $query->getStats($userId);
// 5. 导出Excel
$data = $query->export($userId, ['type' => 'buy']);
API接口示例
<?php
// points_api.php
class PointsApi {
private $logger;
private $query;
public function __construct() {
// 初始化连接
$this->init();
}
/**
* 添加积分 (POST /api/points/add)
*/
public function addPoints() {
$userId = $_POST['user_id'];
$points = $_POST['points'];
$type = $_POST['type'];
$title = $_POST['title'];
$result = $this->logger->log(
$userId,
$points,
$type,
$title,
['description' => $_POST['description'] ?? '']
);
$this->jsonResponse($result, '积分添加成功');
}
/**
* 查询积分日志 (GET /api/points/logs)
*/
public function getLogs() {
$userId = $_GET['user_id'];
$page = $_GET['page'] ?? 1;
$limit = $_GET['limit'] ?? 10;
$logs = $this->query->getLogs($userId, $page, $limit);
$this->jsonResponse(true, '查询成功', $logs);
}
/**
* 获取积分余额
*/
public function getBalance() {
$userId = $_GET['user_id'];
$sql = "SELECT points_balance FROM users WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId]);
$balance = $stmt->fetchColumn();
$this->jsonResponse(true, '查询成功', ['balance' => $balance]);
}
private function jsonResponse($success, $message, $data = null) {
header('Content-Type: application/json');
echo json_encode([
'success' => $success,
'message' => $message,
'data' => $data
]);
}
}
这个积分日志系统包含了完整的功能：
- 记录：支持添加/扣减积分，记录变更原因
- 规则：灵活的积分规则配置和频率限制
- 查询：支持分页、筛选、统计
- 缓存：使用Redis优化性能
- 事务：确保数据一致性
使用时可以根据业务需求扩展具体的积分类型和规则。
版权声明：NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
