IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 积分日志记录

wen PHP项目 6

本文目录导读：

PHP 积分日志记录

  1. 数据库设计
  2. 基础积分类
  3. 积分规则处理类
  4. 查询和展示
  5. 使用示例
  6. API接口示例

我来为你提供一个完整的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
        ]);
    }
}

这个积分日志系统包含了完整的功能：

  1. 记录：支持添加/扣减积分，记录变更原因
  2. 规则：灵活的积分规则配置和频率限制
  3. 查询：支持分页、筛选、统计
  4. 缓存：使用Redis优化性能
  5. 事务：确保数据一致性

使用时可以根据业务需求扩展具体的积分类型和规则。

上一篇PHP 积分商城兑换

下一篇PHP 成就勋章怎么设计

相关文章

抱歉，评论功能暂时关闭!