jude-user-recharge/app/admin/controller/order/Order.php

<?php
/**
 * 订单管理控制器
 */

namespace app\admin\controller\order;

use app\common\controller\Backend;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

class Order extends Backend
{
    protected array $noNeedLogin = ['*'];

    /**
     * 订单列表
     * @return void
     */
    public function index(): void
    {
        $where = [];

        // 订单编号搜索
        if (!empty($this->request->get('order_no'))) {
            $orderNo = $this->request->get('order_no');
            $where[] = ['o.order_no', 'like', '%' . $orderNo . '%'];
        }

        // 商品名称搜索
        if (!empty($this->request->get('product_name'))) {
            $productName = $this->request->get('product_name');
            $where[] = ['oi.item_name', 'like', '%' . $productName . '%'];
        }

        // 会员名称搜索
        if (!empty($this->request->get('user_name'))) {
            $userName = $this->request->get('user_name');
            $where[] = ['u.name', 'like', '%' . $userName . '%'];
        }

        // 分佣人搜索
        if (!empty($this->request->get('distributor_name'))) {
            $distributorName = $this->request->get('distributor_name');
            $where[] = ['du.name', 'like', '%' . $distributorName . '%'];
        }

        // 支付状态筛选
        if (!empty($this->request->get('pay_status'))) {
            $payStatus = $this->request->get('pay_status');
            $where['o.pay_status'] = $payStatus;
        }

        // 支付方式筛选
        if (!empty($this->request->get('pay_method'))) {
            $payMethod = $this->request->get('pay_method');
            $where['o.pay_method'] = $payMethod;
        }

        // 日期范围筛选
        if (!empty($this->request->get('date_range'))) {
            $dateRange = $this->request->get('date_range');
            $dateArr = explode(' - ', $dateRange);
            if (count($dateArr) == 2) {
                $startDate = $dateArr[0];
                $endDate = $dateArr[1];
                $where[] = ['o.order_time', '>=', $startDate . ' 00:00:00'];
                $where[] = ['o.order_time', '<=', $endDate . ' 23:59:59'];
            }
        }

        // 排序
        $order = [];
        if (!empty($this->request->get('order'))) {
            $order_a = $this->request->get('order');
            $order_a = explode(',', $order_a);
            $order[$order_a[0]] = $order_a[1];
        } else {
            $order['o.order_time'] = 'desc';
        }

        // 分页
        $page = $this->request->get('page', 1);
        $limit = $this->request->get('limit', 20);

        // 查询数据（使用订单商品表作为主表，一个订单可能对应多个商品）
        $orderList = Db::name('order_item')
            ->alias('oi')
            ->leftJoin('order o', 'oi.order_id = o.id')
            ->leftJoin('user_on u', 'o.user_id = u.id')
            ->leftJoin('user_on du', 'o.distributor_id = du.id')
            ->field('
                o.order_time,
                o.order_no,
                oi.item_name,
                oi.quantity,
                oi.unit_price,
                u.name,
                o.pay_amount,
                o.pay_method,
                o.pay_status,
                o.commission_amount,
                du.name,
                o.order_time,
                o.pay_time,
                oi.id,
                o.id as order_id
            ')
            ->where($where)
            ->order($order)
            ->paginate([
                'list_rows' => $limit,
                'page' => $page
            ]);

        // 转换字段格式
        $list = $orderList->items();
        foreach ($list as &$item) {
            // 支付方式转换
            $payMethodMap = [
                'wechat' => '微信',
                'alipay' => '支付宝',
                'bank' => '银行卡',
                'balance' => '余额'
            ];
            $item['pay_method'] = $payMethodMap[$item['pay_method']] ?? $item['pay_method'];

            // 支付状态转换
            $payStatusMap = [
                0 => '待支付',
                1 => '已支付',
                2 => '支付失败',
                3 => '已取消'
            ];
            $item['pay_status_text'] = $payStatusMap[$item['pay_status']] ?? $item['pay_status'];

            // 格式化日期
            if (!empty($item['order_time'])) {
                $item['order_time'] = date('Y-m-d', strtotime($item['order_time']));
            }
        }

        $this->success('查询成功', [
            'list' => $list,
            'total' => $orderList->total()
        ]);
    }


    /**
     * 构建查询条件
     * @return array
     */
    private function buildWhere(): array
    {
        $where = [];

        if (!empty($this->request->get('order_no'))) {
            $where[] = ['o.order_no', 'like', '%' . $this->request->get('order_no') . '%'];
        }

        if (!empty($this->request->get('item_name'))) {
            $where[] = ['oi.item_name', 'like', '%' . $this->request->get('item_name') . '%'];
        }

        if (!empty($this->request->get('user_name'))) {
            $where[] = ['u.name', 'like', '%' . $this->request->get('user_name') . '%'];
        }

        if (!empty($this->request->get('distributor_name'))) {
            $where[] = ['du.name', 'like', '%' . $this->request->get('distributor_name') . '%'];
        }

        if ($this->request->get('pay_status') !== '' && $this->request->get('pay_status') !== null) {
            $where['o.pay_status'] = $this->request->get('pay_status');
        }

        if (!empty($this->request->get('pay_method'))) {
            $where['o.pay_method'] = $this->request->get('pay_method');
        }

        if (!empty($this->request->get('date_range'))) {
            $dateRange = $this->request->get('date_range');
            $dateArr = explode(' - ', $dateRange);
            if (count($dateArr) == 2) {
                $startDate = $dateArr[0];
                $endDate = $dateArr[1];
                $where[] = ['o.order_time', '>=', $startDate . ' 00:00:00'];
                $where[] = ['o.order_time', '<=', $endDate . ' 23:59:59'];
            }
        }

        return $where;
    }

    /**
     * 构建排序
     * @return array
     */
    private function buildOrder(): array
    {
        $order = [];
        if (!empty($this->request->get('order'))) {
            $order_a = $this->request->get('order');
            $order_a = explode(',', $order_a);
            $order[$order_a[0]] = $order_a[1];
        } else {
            $order['o.order_time'] = 'desc';
        }
        return $order;
    }

    /**
     * 使用PhpSpreadsheet导出Excel文件
     * @return void
     */
    public function export(): void
    {
        // 获取查询条件
        $where = $this->buildWhere();
        $order = $this->buildOrder();

        // 查询所有数据
        $data = Db::name('order_item')
            ->alias('oi')
            ->leftJoin('order o', 'oi.order_id = o.id')
            ->leftJoin('user_on u', 'o.user_id = u.id')
            ->leftJoin('user_on du', 'o.distributor_id = du.id')
            ->field('
            o.order_no as 订单编号,
            DATE(o.order_time) as 日期,
            oi.item_name as 商品名称,
            oi.quantity as 购买数量,
            oi.unit_price as 商品单价,
            u.name as 会员名称,
            o.pay_amount as 支付金额,
            o.pay_method as 支付方式,
            o.pay_status as 支付状态,
            o.commission_amount as 最后佣金,
            du.name as 分佣人名称,
            o.order_time as 下单时间,
            o.pay_time as 支付时间
        ')
            ->where($where)
            ->order($order)
            ->select()
            ->toArray();

        if (empty($data)) {
            $this->error(__('没有找到符合条件的数据'));
        }

        // 转换字段格式
        foreach ($data as &$item) {
            // 支付方式转换
            $payMethodMap = [
                'wechat' => '微信支付',
                'alipay' => '支付宝',
                'bank' => '银行卡',
                'balance' => '余额支付'
            ];
            $item['支付方式'] = $payMethodMap[$item['支付方式']] ?? $item['支付方式'];

            // 支付状态转换
            $payStatusMap = [
                0 => '待支付',
                1 => '已支付',
                2 => '支付失败',
                3 => '已取消'
            ];
            $item['支付状态'] = $payStatusMap[$item['支付状态']] ?? $item['支付状态'];

            // 格式化金额（PhpSpreadsheet会自动处理数字格式）
            $item['商品单价'] = (float)$item['商品单价'];
            $item['支付金额'] = (float)$item['支付金额'];
            $item['最后佣金'] = (float)$item['最后佣金'];

            // 格式化时间
            if ($item['下单时间']) {
                $item['下单时间'] = date('Y-m-d H:i:s', strtotime($item['下单时间']));
            }
            if ($item['支付时间']) {
                $item['支付时间'] = date('Y-m-d H:i:s', strtotime($item['支付时间']));
            }
        }

        try {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // 定义表头
            $headers = [
                '订单编号', '日期', '商品名称', '购买数量', '商品单价',
                '会员名称', '支付金额', '支付方式', '支付状态',
                '最后佣金', '分佣人名称', '下单时间', '支付时间'
            ];

            // 设置表头
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);

                // 设置表头加粗和背景色
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setRGB('D9D9D9');

                $col++;
            }

            // 填充数据
            $row = 2;
            foreach ($data as $item) {
                $col = 'A';
                foreach ($item as $value) {
                    $cellAddress = $col . $row;

                    // 特殊处理订单编号（防止科学计数法）
                    if ($col === 'A') {
                        $sheet->setCellValueExplicit($cellAddress, $value, DataType::TYPE_STRING);
                        $sheet->getStyle($cellAddress)->getNumberFormat()->setFormatCode('@');
                    } else {
                        $sheet->setCellValue($cellAddress, $value);
                    }
                    $col++;
                }
                $row++;
            }

            // 设置列宽自适应
            $lastColumn = $sheet->getHighestColumn();
            for ($col = 'A'; $col <= $lastColumn; $col++) {
                $sheet->getColumnDimension($col)->setAutoSize(true);
            }

            // 生成文件名
            $fileName = '订单数据_' . date('YmdHis') . '.xlsx';

            // 清除输出缓冲区
            ob_clean();

            // 设置HTTP头
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');

            // 输出到浏览器
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');

            exit;

        } catch (\Exception $e) {
            $this->error('导出失败：' . $e->getMessage());
        }
    }

    /**
     * 获取订单状态选项
     * @return void
     */
    public function statusOptions(): void
    {
        $options = [
            ['value' => 0, 'label' => '待支付'],
            ['value' => 1, 'label' => '已支付'],
            ['value' => 2, 'label' => '支付失败'],
            ['value' => 3, 'label' => '已取消']
        ];

        $this->success('获取成功', $options);
    }

    /**
     * 获取支付方式选项
     * @return void
     */
    public function payMethodOptions(): void
    {
        $options = [
            ['value' => 'wechat', 'label' => '微信'],
            ['value' => 'alipay', 'label' => '支付宝'],
            ['value' => 'bank', 'label' => '银行卡'],
            ['value' => 'balance', 'label' => '余额']
        ];

        $this->success('获取成功', $options);
    }


    /**
     * 获取支付方式文本
     * @param string $method
     * @return string
     */
    private function getPayMethodText(string $method): string
    {
        $map = [
            'wechat' => '微信支付',
            'alipay' => '支付宝',
            'bank' => '银行卡',
            'balance' => '余额支付'
        ];
        return $map[$method] ?? $method;
    }

    /**
     * 获取支付状态文本
     * @param int $status
     * @return string
     */
    private function getPayStatusText(int $status): string
    {
        $map = [
            0 => '待支付',
            1 => '已支付',
            2 => '支付失败',
            3 => '已取消'
        ];
        return $map[$status] ?? '未知';
    }

}