book_admin/application/admin/controller/Ordersstatement.php

<?php
/**
 * 对账角色用于对账的订单详情
 */

namespace app\admin\controller;

use app\common\controller\Backend;
use app\main\constants\AdminConstants;

/**
 * 订单管理
 *
 * @icon fa fa-circle-o
 */
class Ordersstatement extends Backend
{

    /**
     * @var \app\common\model\Orders Orders模型对象
     */
    protected $model;

    protected $noNeedRight = ['ordersexport'];

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('Orders');
    }


    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            ini_set('memory_limit', '1024M'); //内存限制

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('pkey_name')) {
                return $this->selectpage();
            }

            $whereArr = [];
            $whereArr['deduct'] = 0;

            $filter = $this->request->get("filter", '');
            $filter = json_decode($filter, true);
            $filter = $filter ? $filter : [];
            if (array_key_exists('createtime', $filter)) {
                //判断时间区间
                $timeRange = explode(' - ', $filter['createtime']);
                $stime = strtotime($timeRange[0]);
                $etime = strtotime($timeRange[1]);
                if (abs($etime - $stime) > (86400 * 31)) {
                    $this->error("查询时间范围超过 31 天", null, 13);
                }

                if ($stime < strtotime('2020-01-01 00:00:00') && $etime >= strtotime('2020-01-01 00:00:00')) {
                    $this->error("查询时间范围禁止跨年", null, 13);
                }

                // 查询 2020 年明细时，带扣量
                if ($stime >= strtotime('2020-01-01 00:00:00') || $etime >= strtotime('2020-01-01 00:00:00')) {
                    unset($whereArr['deduct']);
                }
            }

            if (!array_key_exists('createtime', $filter)) {
                $whereArr['createtime'] = [">=", time() - 3600];
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->where($whereArr)
                ->order($sort, $order)
                ->count();

            $list = $this->model
                ->where($where)
                ->where($whereArr)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }


    /**
     * 导出csv
     */
    public function ordersexport()
    {
        ini_set('memory_limit', '1024M'); //内存限制

        // ID = 1 or 13 超管 & 对账角色才有导出权限
        if (!in_array($this->group,
            [AdminConstants::ADMIN_GROUP_ID_SUPER_ADMIN, AdminConstants::ADMIN_GROUP_ID_STATEMENT])) {
            $this->error("权限错误", '/admin');
        }

        $whereArr = [];
        $whereArr['deduct'] = 0;

        $filter = $this->request->get("filter", '');
        $filter = json_decode($filter, true);
        $filter = $filter ? $filter : [];
        if (array_key_exists('createtime', $filter)) {
            //判断时间区间
            $timeRange = explode(' - ', $filter['createtime']);
            $stime = strtotime($timeRange[0]);
            $etime = strtotime($timeRange[1]);
            if (abs($etime - $stime) > (86400 * 31)) {
                $this->error("查询时间范围超过 31 天", null, 13);
            }

            if ($stime < strtotime('2020-01-01 00:00:00') && $etime >= strtotime('2020-01-01 00:00:00')) {
                $this->error("查询时间范围禁止跨年", null, 13);
            }

            // 查询 2020 年明细时，带扣量
            if ($stime >= strtotime('2020-01-01 00:00:00') || $etime >= strtotime('2020-01-01 00:00:00')) {
                unset($whereArr['deduct']);
            }
        }

        if (!array_key_exists('createtime', $filter)) {
            $whereArr['createtime'] = [">=", time() - 3600];
        }

        $columns = ['商户单号', '交易单号', '类型', '用户 ID', '充值金额', '支付状态', '下单时间', '完成时间'];

        list($where, $sort, $order, $offset, $limit) = $this->buildparams();

        $limit = 2000;

        $fileName = "导出订单数据-" . date('YmdHis', time()) . ".csv";

        header('Content-Description: File Transfer');
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment; filename=" . $fileName);
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        $fp = fopen('php://output', 'a');//打开output流
        mb_convert_variables('GBK', 'UTF-8', $columns);
        fputcsv($fp, $columns);//将数据格式化为CSV格式并写入到output流中

        $total = $this->model
            ->where($where)
            ->where($whereArr)
            ->count();

        $pages = ceil($total / $limit);

        $orderId = 0;
        for ($i = 1; $i <= $pages; $i++) {

            $offset = 0;
            if ($i > 1) {
                $whereArr['id'] = ['<', $orderId];
            }

            $list = $this->model
                ->field('id,out_trade_no,transaction_id,type,user_id,money,state,createtime,finishtime')
                ->where($where)
                ->where($whereArr)
                ->order(['createtime' => 'desc', 'id' => 'desc'])
                ->limit($offset, $limit)
                ->select();

            if (!empty($list)) {
                $endArr = end($list);
                $orderId = $endArr['id'];
                foreach ($list as $k => $v) {
                    // '\t' 可以解决科学技术法问题
                    $rowData = [
                        '"' . $v['out_trade_no'] . '"',
                        empty($v['transaction_id']) ? "无" : "\t" . $v['transaction_id'] . "\t",
                        $v['type'] == 1 ? '书币充值' : 'VIP充值',
                        $v['user_id'],
                        $v['money'],
                        $v['state'] == 1 ? '已支付' : '未支付',
                        date('Y-m-d H:i:s', $v['createtime']),
                        $v['state'] == 1 ? date('Y-m-d H:i:s', $v['finishtime']) : '',
                    ];
                    mb_convert_variables('GBK', 'UTF-8', $rowData); //需要格式转换，否则会乱码
                    fputcsv($fp, $rowData);
                }
            }
        }

        fclose($fp);
        exit;
    }
}