PHP怎样实现用户积分兑换？虚拟货币变现设计

<?php
// 假设你有一个数据库连接 $pdo

class PointsService {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    /**
     * 增加用户积分
     * @param int $userId
     * @param int $amount 增加的积分数
     * @param string $type 交易类型，如 'earn_task', 'recharge'
     * @param int $relatedId 关联实体ID
     * @param string $description 描述
     * @return bool
     */
    public function addPoints(int $userId, int $amount, string $type, int $relatedId = 0, string $description = ''): bool {
        if ($amount <= 0) return false;

        try {
            $this->pdo->beginTransaction();

            // 获取当前积分
            $stmt = $this->pdo->prepare("SELECT current_points FROM user_points WHERE user_id = ? FOR UPDATE"); // 加锁，防止并发问题
            $stmt->execute([$userId]);
            $currentPoints = $stmt->fetchColumn();

            if ($currentPoints === false) {
                // 新用户或首次获得积分，插入记录
                $stmt = $this->pdo->prepare("INSERT INTO user_points (user_id, current_points) VALUES (?, ?)");
                $stmt->execute([$userId, $amount]);
                $balanceAfter = $amount;
            } else {
                // 更新积分
                $balanceAfter = $currentPoints + $amount;
                $stmt = $this->pdo->prepare("UPDATE user_points SET current_points = ?, last_updated_at = NOW() WHERE user_id = ?");
                $stmt->execute([$balanceAfter, $userId]);
            }

            // 记录交易
            $stmt = $this->pdo->prepare("INSERT INTO points_transactions (user_id, type, amount, balance_after_transaction, related_entity_type, related_entity_id, description, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())");
            $stmt->execute([$userId, $type, $amount, $balanceAfter, 'user', $relatedId, $description]);

            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            // 记录错误日志
            error_log("Add points failed for user {$userId}: " . $e->getMessage());
            return false;
        }
    }

    /**
     * 扣除用户积分
     * @param int $userId
     * @param int $amount 扣除的积分数
     * @param string $type 交易类型，如 'exchange', 'withdraw'
     * @param int $relatedId 关联实体ID
     * @param string $description 描述
     * @return bool
     */
    public function deductPoints(int $userId, int $amount, string $type, int $relatedId = 0, string $description = ''): bool {
        if ($amount <= 0) return false;

        try {
            $this->pdo->beginTransaction();

            // 获取当前积分并加锁
            $stmt = $this->pdo->prepare("SELECT current_points FROM user_points WHERE user_id = ? FOR UPDATE");
            $stmt->execute([$userId]);
            $currentPoints = $stmt->fetchColumn();

            if ($currentPoints === false || $currentPoints < $amount) {
                $this->pdo->rollBack();
                return false; // 积分不足或用户不存在
            }

            $balanceAfter = $currentPoints - $amount;

            // 更新积分
            $stmt = $this->pdo->prepare("UPDATE user_points SET current_points = ?, last_updated_at = NOW() WHERE user_id = ?");
            $stmt->execute([$balanceAfter, $userId]);

            // 记录交易
            $stmt = $this->pdo->prepare("INSERT INTO points_transactions (user_id, type, amount, balance_after_transaction, related_entity_type, related_entity_id, description, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())");
            $stmt->execute([$userId, $type, -$amount, $balanceAfter, 'user', $relatedId, $description]); // 注意这里是负数

            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            error_log("Deduct points failed for user {$userId}: " . $e->getMessage());
            return false;
        }
    }

    /**
     * 处理积分兑换商品
     * @param int $userId
     * @param int $itemId 兑换的商品ID
     * @return bool
     */
    public function exchangeItem(int $userId, int $itemId): bool {
        try {
            $this->pdo->beginTransaction();

            // 1. 获取商品信息并加锁
            $stmt = $this->pdo->prepare("SELECT item_name, points_cost, stock FROM exchange_items WHERE item_id = ? FOR UPDATE");
            $stmt->execute([$itemId]);
            $item = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$item || $item['stock'] <= 0) {
                $this->pdo->rollBack();
                return false; // 商品不存在或库存不足
            }

            $pointsCost = $item['points_cost'];

            // 2. 检查用户积分并加锁
            $stmt = $this->pdo->prepare("SELECT current_points FROM user_points WHERE user_id = ? FOR UPDATE");
            $stmt->execute([$userId]);
            $currentPoints = $stmt->fetchColumn();

            if ($currentPoints === false || $currentPoints < $pointsCost) {
                $this->pdo->rollBack();
                return false; // 积分不足
            }

            // 3. 扣除用户积分
            $balanceAfter = $currentPoints - $pointsCost;
            $stmt = $this->pdo->prepare("UPDATE user_points SET current_points = ?, last_updated_at = NOW() WHERE user_id = ?");
            $stmt->execute([$balanceAfter, $userId]);

            // 4. 记录积分交易
            $stmt = $this->pdo->prepare("INSERT INTO points_transactions (user_id, type, amount, balance_after_transaction, related_entity_type, related_entity_id, description, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())");
            $stmt->execute([$userId, 'exchange_out', -$pointsCost, $balanceAfter, 'item', $itemId, "兑换商品: {$item['item_name']}"]);

            // 5. 减少商品库存
            $stmt = $this->pdo->prepare("UPDATE exchange_items SET stock = stock - 1 WHERE item_id = ?");
            $stmt->execute([$itemId]);

            // 6. 创建兑换订单
            $stmt = $this->pdo->prepare("INSERT INTO exchange_orders (user_id, item_id, points_deducted, order_status, created_at) VALUES (?, ?, ?, ?, NOW())");
            $stmt->execute([$userId, $itemId, $pointsCost, 'pending']);

            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            error_log("Exchange item failed for user {$userId}, item {$itemId}: " . $e->getMessage());
            return false;
        }
    }
}

// 示例用法
// $dsn = 'mysql:host=localhost;dbname=your_db_name;charset=utf8mb4';
// $username = 'your_username';
// $password = 'your_password';
// try {
//     $pdo = new PDO($dsn, $username, $password, [
//         PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
//         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
//     ]);
// } catch (PDOException $e) {
//     die("数据库连接失败: " . $e->getMessage());
// }

// $pointsService = new PointsService($pdo);

// // 模拟用户获得积分
// if ($pointsService->addPoints(1, 100, 'daily_sign_in', 0, '每日签到奖励')) {
//     echo "用户1获得100积分成功。\n";
// } else {
//     echo "用户1获得积分失败。\n";
// }

// // 模拟用户兑换商品
// if ($pointsService->exchangeItem(1, 1)) { // 假设商品ID为1
//     echo "用户1兑换商品成功。\n";
// } else {
//     echo "用户1兑换商品失败（可能积分不足或库存不足）。\n";
// }
?>

beginTransaction()

commit()

rollBack()

// 伪代码示例：PDO事务
$pdo->beginTransaction();
try {
    // 1. 检查用户积分是否足够 (SELECT ... FOR UPDATE 加锁，防止并发问题)
    // 2. 扣除用户积分 (UPDATE)
    // 3. 检查商品库存是否足够 (SELECT ... FOR UPDATE)
    // 4. 减少商品库存 (UPDATE)
    // 5. 记录兑换订单 (INSERT)

    $pdo->commit(); // 所有操作都成功，提交事务
    return true;
} catch (Exception $e) {
    $pdo->rollBack(); // 任何一步失败，回滚所有操作
    // 记录详细错误日志
    return false;
}