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

PHP项目交易记录与流水查询

wen PHP项目 1

本文目录导读：

PHP项目交易记录与流水查询

  1. 数据库设计
  2. 核心PHP代码
  3. 前端查询界面 (Vue.js示例)
  4. 优化建议

我来为您设计一个PHP项目中的交易记录与流水查询系统，这是一个完整的解决方案，包含数据库设计、查询逻辑和前端展示。

数据库设计

交易记录表 (transaction_log)

CREATE TABLE `transaction_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `transaction_no` varchar(32) NOT NULL COMMENT '交易单号',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `type` tinyint(4) NOT NULL COMMENT '交易类型：1-充值 2-消费 3-退款 4-提现 5-转账',
  `amount` decimal(12,2) NOT NULL COMMENT '交易金额',
  `balance_before` decimal(12,2) DEFAULT '0.00' COMMENT '交易前余额',
  `balance_after` decimal(12,2) DEFAULT '0.00' COMMENT '交易后余额',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-处理中 1-成功 2-失败 3-已取消',
  `payment_method` varchar(50) DEFAULT NULL COMMENT '支付方式：alipay/wechat/bank等',
  `remark` varchar(500) DEFAULT NULL COMMENT '交易备注',
  `order_id` varchar(32) DEFAULT NULL COMMENT '关联订单号',
  `ip_address` varchar(45) DEFAULT NULL COMMENT '操作IP',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_transaction_no` (`transaction_no`),
  KEY `idx_type` (`type`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_user_created` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易记录表';

资金流水表 (account_flow)

CREATE TABLE `account_flow` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `flow_no` varchar(32) NOT NULL COMMENT '流水号',
  `account_id` int(11) NOT NULL COMMENT '账户ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `direction` tinyint(4) NOT NULL COMMENT '方向：1-收入 2-支出',
  `amount` decimal(12,2) NOT NULL COMMENT '变动金额',
  `balance` decimal(12,2) NOT NULL COMMENT '变动后余额',
  `flow_type` varchar(30) NOT NULL COMMENT '流水类型：order_pay/recharge/withdraw/refund/etc',
  `business_key` varchar(64) DEFAULT NULL COMMENT '业务标识',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_flow_no` (`flow_no`),
  KEY `idx_flow_type` (`flow_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水表';

核心PHP代码

TransactionService.php - 交易服务类

<?php
namespace App\Services;
use App\Models\TransactionLog;
use App\Models\AccountFlow;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
class TransactionService
{
    /**
     * 查询交易记录
     */
    public function queryTransactions($params, $page = 1, $pageSize = 15)
    {
        $query = TransactionLog::query();
        // 基础过滤
        if (!empty($params['user_id'])) {
            $query->where('user_id', $params['user_id']);
        }
        // 交易类型
        if (!empty($params['type'])) {
            if (is_array($params['type'])) {
                $query->whereIn('type', $params['type']);
            } else {
                $query->where('type', $params['type']);
            }
        }
        // 交易状态
        if (isset($params['status']) && $params['status'] !== '') {
            $query->where('status', $params['status']);
        }
        // 时间范围
        if (!empty($params['start_date'])) {
            $query->where('created_at', '>=', $params['start_date']);
        }
        if (!empty($params['end_date'])) {
            $query->where('created_at', '<=', $params['end_date'] . ' 23:59:59');
        }
        // 金额范围
        if (!empty($params['min_amount'])) {
            $query->where('amount', '>=', $params['min_amount']);
        }
        if (!empty($params['max_amount'])) {
            $query->where('amount', '<=', $params['max_amount']);
        }
        // 交易单号
        if (!empty($params['transaction_no'])) {
            $query->where('transaction_no', 'like', "%{$params['transaction_no']}%");
        }
        // 关联订单
        if (!empty($params['order_id'])) {
            $query->where('order_id', $params['order_id']);
        }
        // 排序
        $sortField = $params['sort_field'] ?? 'created_at';
        $sortOrder = $params['sort_order'] ?? 'desc';
        $query->orderBy($sortField, $sortOrder);
        // 分页
        $total = $query->count();
        $items = $query->skip(($page - 1) * $pageSize)
                      ->take($pageSize)
                      ->get();
        // 格式化数据
        $items->transform(function ($item) {
            return $this->formatTransaction($item);
        });
        return [
            'total' => $total,
            'page' => $page,
            'page_size' => $pageSize,
            'items' => $items,
            'summary' => $this->getTransactionSummary($query)
        ];
    }
    /**
     * 查询资金流水
     */
    public function queryAccountFlows($params, $page = 1, $pageSize = 15)
    {
        $query = AccountFlow::query();
        // 过滤条件
        if (!empty($params['user_id'])) {
            $query->where('user_id', $params['user_id']);
        }
        if (!empty($params['account_id'])) {
            $query->where('account_id', $params['account_id']);
        }
        if (!empty($params['direction'])) {
            $query->where('direction', $params['direction']);
        }
        if (!empty($params['flow_type'])) {
            $query->where('flow_type', $params['flow_type']);
        }
        // 时间范围
        if (!empty($params['start_date'])) {
            $query->where('created_at', '>=', $params['start_date']);
        }
        if (!empty($params['end_date'])) {
            $query->where('created_at', '<=', $params['end_date'] . ' 23:59:59');
        }
        // 排序
        $query->orderBy('created_at', 'desc');
        $total = $query->count();
        $items = $query->skip(($page - 1) * $pageSize)
                      ->take($pageSize)
                      ->get();
        return [
            'total' => $total,
            'page' => $page,
            'page_size' => $pageSize,
            'items' => $items->toArray()
        ];
    }
    /**
     * 获取交易统计
     */
    public function getTransactionStats($userId, $startDate, $endDate)
    {
        return TransactionLog::where('user_id', $userId)
            ->where('status', 1) // 只统计成功的
            ->whereBetween('created_at', [$startDate, $endDate])
            ->selectRaw("
                SUM(CASE WHEN type = 1 THEN amount ELSE 0 END) as total_recharge,
                SUM(CASE WHEN type = 2 THEN amount ELSE 0 END) as total_consume,
                SUM(CASE WHEN type = 3 THEN amount ELSE 0 END) as total_refund,
                SUM(CASE WHEN type = 4 THEN amount ELSE 0 END) as total_withdraw,
                COUNT(*) as total_count
            ")
            ->first();
    }
    /**
     * 格式化交易记录
     */
    private function formatTransaction($item)
    {
        $typeMap = [
            1 => ['name' => '充值', 'icon' => 'pay-circle'],
            2 => ['name' => '消费', 'icon' => 'shopping-cart'],
            3 => ['name' => '退款', 'icon' => 'rollback'],
            4 => ['name' => '提现', 'icon' => 'bank'],
            5 => ['name' => '转账', 'icon' => 'swap']
        ];
        $statusMap = [
            0 => ['name' => '处理中', 'color' => 'orange'],
            1 => ['name' => '成功', 'color' => 'green'],
            2 => ['name' => '失败', 'color' => 'red'],
            3 => ['name' => '已取消', 'color' => 'grey']
        ];
        return [
            'id' => $item->id,
            'transaction_no' => $item->transaction_no,
            'type' => $item->type,
            'type_name' => $typeMap[$item->type]['name'] ?? '未知',
            'type_icon' => $typeMap[$item->type]['icon'] ?? '',
            'amount' => $item->amount,
            'amount_formatted' => number_format($item->amount, 2),
            'balance_before' => $item->balance_before,
            'balance_after' => $item->balance_after,
            'status' => $item->status,
            'status_name' => $statusMap[$item->status]['name'] ?? '未知',
            'status_color' => $statusMap[$item->status]['color'] ?? '',
            'payment_method' => $item->payment_method,
            'remark' => $item->remark,
            'order_id' => $item->order_id,
            'created_at' => $item->created_at->format('Y-m-d H:i:s'),
            'created_at_diff' => $item->created_at->diffForHumans()
        ];
    }
    /**
     * 获取交易汇总
     */
    private function getTransactionSummary($query)
    {
        return $query->selectRaw("
            COUNT(*) as total_count,
            SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) as total_success_amount,
            SUM(amount) as total_amount
        ")->first();
    }
}

TransactionController.php - 控制器

<?php
namespace App\Http\Controllers\Api;
use App\Services\TransactionService;
use Illuminate\Http\Request;
class TransactionController extends Controller
{
    protected $transactionService;
    public function __construct(TransactionService $transactionService)
    {
        $this->transactionService = $transactionService;
    }
    /**
     * 获取交易列表
     */
    public function index(Request $request)
    {
        $params = $request->validate([
            'user_id' => 'sometimes|integer',
            'type' => 'sometimes|integer|in:1,2,3,4,5',
            'status' => 'sometimes|integer|in:0,1,2,3',
            'start_date' => 'sometimes|date',
            'end_date' => 'sometimes|date|after_or_equal:start_date',
            'min_amount' => 'sometimes|numeric|min:0',
            'max_amount' => 'sometimes|numeric|min:0',
            'transaction_no' => 'sometimes|string',
            'order_id' => 'sometimes|string',
            'page' => 'sometimes|integer|min:1',
            'page_size' => 'sometimes|integer|min:1|max:100'
        ]);
        $page = $params['page'] ?? 1;
        $pageSize = $params['page_size'] ?? 15;
        $result = $this->transactionService->queryTransactions($params, $page, $pageSize);
        return response()->json([
            'code' => 200,
            'message' => 'success',
            'data' => $result
        ]);
    }
    /**
     * 获取流水列表
     */
    public function flows(Request $request)
    {
        $params = $request->validate([
            'user_id' => 'required|integer',
            'account_id' => 'sometimes|integer',
            'direction' => 'sometimes|integer|in:1,2',
            'flow_type' => 'sometimes|string',
            'start_date' => 'sometimes|date',
            'end_date' => 'sometimes|date',
            'page' => 'sometimes|integer|min:1',
            'page_size' => 'sometimes|integer|min:1|max:100'
        ]);
        $page = $params['page'] ?? 1;
        $pageSize = $params['page_size'] ?? 15;
        $result = $this->transactionService->queryAccountFlows($params, $page, $pageSize);
        return response()->json($result);
    }
    /**
     * 获取交易详情
     */
    public function show($id)
    {
        $transaction = TransactionLog::findOrFail($id);
        return response()->json([
            'code' => 200,
            'data' => $this->formatTransactionDetail($transaction)
        ]);
    }
    /**
     * 获取交易统计
     */
    public function stats(Request $request)
    {
        $params = $request->validate([
            'user_id' => 'required|integer',
            'start_date' => 'required|date',
            'end_date' => 'required|date|after_or_equal:start_date'
        ]);
        $stats = $this->transactionService->getTransactionStats(
            $params['user_id'],
            $params['start_date'],
            $params['end_date']
        );
        return response()->json([
            'code' => 200,
            'data' => $stats
        ]);
    }
}

导出功能

/**
 * 导出交易记录
 */
public function export(Request $request)
{
    $params = $request->validate([
        'user_id' => 'required|integer',
        'start_date' => 'required|date',
        'end_date' => 'required|date',
        'format' => 'sometimes|in:csv,xlsx'
    ]);
    $transactions = TransactionLog::where('user_id', $params['user_id'])
        ->whereBetween('created_at', [$params['start_date'], $params['end_date'] . ' 23:59:59'])
        ->orderBy('created_at', 'desc')
        ->get();
    $format = $params['format'] ?? 'csv';
    if ($format === 'csv') {
        return $this->exportCsv($transactions);
    } else {
        return $this->exportExcel($transactions);
    }
}
private function exportCsv($transactions)
{
    $headers = [
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename="transactions.csv"'
    ];
    $callback = function() use ($transactions) {
        $file = fopen('php://output', 'w');
        fputcsv($file, ['交易单号', '类型', '金额', '状态', '支付方式', '时间', '备注']);
        foreach ($transactions as $transaction) {
            fputcsv($file, [
                $transaction->transaction_no,
                $this->getTypeName($transaction->type),
                $transaction->amount,
                $this->getStatusName($transaction->status),
                $transaction->payment_method,
                $transaction->created_at,
                $transaction->remark
            ]);
        }
        fclose($file);
    };
    return response()->stream($callback, 200, $headers);
}

前端查询界面 (Vue.js示例)

<template>
  <div class="transaction-query">
    <!-- 搜索条件 -->
    <el-form :model="searchForm" inline>
      <el-form-item label="交易类型">
        <el-select v-model="searchForm.type" placeholder="全部" clearable>
          <el-option label="充值" :value="1" />
          <el-option label="消费" :value="2" />
          <el-option label="退款" :value="3" />
          <el-option label="提现" :value="4" />
          <el-option label="转账" :value="5" />
        </el-select>
      </el-form-item>
      <el-form-item label="状态">
        <el-select v-model="searchForm.status" placeholder="全部" clearable>
          <el-option label="处理中" :value="0" />
          <el-option label="成功" :value="1" />
          <el-option label="失败" :value="2" />
          <el-option label="已取消" :value="3" />
        </el-select>
      </el-form-item>
      <el-form-item label="时间范围">
        <el-date-picker
          v-model="dateRange"
          type="daterange"
          range-separator="至"
          start-placeholder="开始日期"
          end-placeholder="结束日期"
          value-format="yyyy-MM-dd"
        />
      </el-form-item>
      <el-form-item label="交易单号">
        <el-input v-model="searchForm.transaction_no" placeholder="输入交易单号" />
      </el-form-item>
      <el-form-item>
        <el-button type="primary" @click="handleSearch">查询</el-button>
        <el-button @click="handleReset">重置</el-button>
        <el-button @click="handleExport" type="success">导出</el-button>
      </el-form-item>
    </el-form>
    <!-- 统计信息 -->
    <el-row :gutter="20" class="statistics">
      <el-col :span="6">
        <el-card>
          <div class="stat-item">
            <span>总交易次数</span>
            <h3>{{ summary.total_count }}</h3>
          </div>
        </el-card>
      </el-col>
      <el-col :span="6">
        <el-card>
          <div class="stat-item">
            <span>成功交易额</span>
            <h3>¥{{ summary.total_success_amount }}</h3>
          </div>
        </el-card>
      </el-col>
      <el-col :span="6">
        <el-card>
          <div class="stat-item">
            <span>总金额</span>
            <h3>¥{{ summary.total_amount }}</h3>
          </div>
        </el-card>
      </el-col>
    </el-row>
    <!-- 交易列表 -->
    <el-table :data="tableData" stripe style="width: 100%">
      <el-table-column prop="transaction_no" label="交易单号" width="200" />
      <el-table-column prop="type_name" label="类型" width="100">
        <template slot-scope="scope">
          <el-tag :type="scope.row.type | typeTag">{{ scope.row.type_name }}</el-tag>
        </template>
      </el-table-column>
      <el-table-column prop="amount_formatted" label="金额" width="150" />
      <el-table-column prop="balance_before" label="交易前余额" width="150" />
      <el-table-column prop="balance_after" label="交易后余额" width="150" />
      <el-table-column prop="status_name" label="状态" width="100">
        <template slot-scope="scope">
          <el-tag :type="scope.row.status | statusTag">{{ scope.row.status_name }}</el-tag>
        </template>
      </el-table-column>
      <el-table-column prop="payment_method" label="支付方式" width="120" />
      <el-table-column prop="created_at" label="时间" width="180" />
      <el-table-column label="操作" width="150">
        <template slot-scope="scope">
          <el-button type="text" @click="viewDetail(scope.row)">查看详情</el-button>
        </template>
      </el-table-column>
    </el-table>
    <!-- 分页 -->
    <el-pagination
      @size-change="handleSizeChange"
      @current-change="handleCurrentChange"
      :current-page="currentPage"
      :page-sizes="[15, 30, 50, 100]"
      :page-size="pageSize"
      layout="total, sizes, prev, pager, next, jumper"
      :total="total">
    </el-pagination>
  </div>
</template>
<script>
export default {
  data() {
    return {
      searchForm: {
        type: '',
        status: '',
        transaction_no: '',
        min_amount: '',
        max_amount: ''
      },
      dateRange: [],
      tableData: [],
      currentPage: 1,
      pageSize: 15,
      total: 0,
      summary: {
        total_count: 0,
        total_success_amount: 0,
        total_amount: 0
      }
    }
  },
  methods: {
    async fetchData() {
      const params = {
        ...this.searchForm,
        page: this.currentPage,
        page_size: this.pageSize
      }
      if (this.dateRange && this.dateRange.length === 2) {
        params.start_date = this.dateRange[0]
        params.end_date = this.dateRange[1]
      }
      try {
        const response = await axios.get('/api/transactions', { params })
        this.tableData = response.data.data.items
        this.total = response.data.data.total
        this.summary = response.data.data.summary
      } catch (error) {
        this.$message.error('获取数据失败')
      }
    },
    handleSearch() {
      this.currentPage = 1
      this.fetchData()
    },
    handleReset() {
      this.searchForm = {
        type: '',
        status: '',
        transaction_no: '',
        min_amount: '',
        max_amount: ''
      }
      this.dateRange = []
      this.handleSearch()
    },
    handleSizeChange(val) {
      this.pageSize = val
      this.fetchData()
    },
    handleCurrentChange(val) {
      this.currentPage = val
      this.fetchData()
    },
    viewDetail(row) {
      // 查看交易详情
      this.$router.push(`/transactions/${row.id}`)
    },
    async handleExport() {
      const params = {
        ...this.searchForm,
        start_date: this.dateRange[0] || '',
        end_date: this.dateRange[1] || '',
        format: 'csv'
      }
      try {
        const response = await axios.get('/api/transactions/export', {
          params,
          responseType: 'blob'
        })
        const url = window.URL.createObjectURL(new Blob([response.data]))
        const link = document.createElement('a')
        link.href = url
        link.download = `transactions_${Date.now()}.csv`
        link.click()
        window.URL.revokeObjectURL(url)
      } catch (error) {
        this.$message.error('导出失败')
      }
    }
  },
  mounted() {
    this.fetchData()
  }
}
</script>

优化建议

性能优化

// 1. 使用索引优化查询
// 2. 大数据量使用分页查询
// 3. 缓存热点数据
public function getTransactionList($userId, $page, $pageSize)
{
    $cacheKey = "transactions:{$userId}:{$page}:{$pageSize}";
    return Cache::remember($cacheKey, 60, function() use ($userId, $page, $pageSize) {
        return $this->queryTransactions(['user_id' => $userId], $page, $pageSize);
    });
}
// 4. 使用数据库分区
ALTER TABLE transaction_log
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

安全考虑

// 1. 权限控制
public function index(Request $request)
{
    $this->authorize('view-transactions');
    // 只能查看自己的交易记录
    if (auth()->user()->role !== 'admin') {
        $params['user_id'] = auth()->id();
    }
}
// 2. 敏感信息脱敏
private function maskTransactionNo($no)
{
    return substr($no, 0, 4) . '****' . substr($no, -4);
}
// 3. 防止SQL注入 - 使用参数绑定
$query->whereRaw('amount BETWEEN ? AND ?', [$min, $max]);

这套完整的交易记录与流水查询方案包含了数据库设计、后端API、查询逻辑、前端界面、导出功能以及性能优化建议,可以根据实际项目需求进行调整和扩展。

上一篇PHP项目反欺诈与黑名单

下一篇PHP项目风控规则引擎设计

相关文章

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