book_admin/application/admin/controller/Viporders.php

<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\main\constants\AdminConstants;
use app\main\service\AdminService;
use app\main\helper\ArrayHelper;
use think\db\Query;
use think\Log;

class Viporders extends Backend
{
    /**
     * @var \app\common\model\Orders
     */
    protected $model = null;

    /**
     * @var \app\common\model\VipAdminBind
     */
    protected $vipAdminBindModel = null;

    /**
     * @var \app\common\model\Admin
     */
    protected $adminModel = null;

    /**
     * @var \app\common\model\AdminConfig
     */
    protected $adminConfigModel = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('Orders');
        $this->vipAdminBindModel = model('VipAdminBind');
        $this->adminModel = model('Admin');
        $this->adminConfigModel = model('AdminConfig');

    }


    /**
     * 订单明细导出
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @author 李聪聪
     */
    public function export()
    {
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();

        $filter = $this->request->get("filter", '');
        $filter = json_decode($filter, true);
        $where = [];
        $channelId = null;
        $username = null;
        $nickname = null;
        if ($filter) {
            foreach ($filter as $k => $v) {
                switch ($k) {
                    case 'out_trade_no':
                        $where['out_trade_no'] = ['like', '%' . $v . '%'];
                        break;
                    case 'transaction_id':
                        $where['transaction_id'] = ['like', '%' . $v . '%'];
                        break;
                    case 'money':
                        $where['orders.money'] = $v;
                        break;
                    case 'state':
                        $where['orders.state'] = $v;
                        break;
                    case 'createtime':
                        $arr = array_slice(explode(' - ', $v), 0, 2);
                        $where['orders.createtime'] = ['BETWEEN time', $arr];
                        if (abs(strtotime($arr[1])-strtotime($arr[0]) > (86400*31))) {
                            $this->error("查询时间范围超过一个月", null, 13);
                        }
                        break;
                    case 'finishtime':
                        $arr = array_slice(explode(' - ', $v), 0, 2);
                        $where['orders.finishtime'] = ['BETWEEN time', $arr];
                        if (abs(strtotime($arr[1])-strtotime($arr[0]) > (86400*31))) {
                            $this->error("查询时间范围超过一个月", null, 13);
                        }
                        break;

                    case 'channel_nickname';
                        $nickname = $v;
                        break;
                    case 'channel_id':
                        $channelId = $v;
                        break;
                    case 'channel_username':
                        $username = $v;
                        break;

                    default:
                        break;
                }
            }
        }
        ini_set('memory_limit', '256M'); //内存限制

        $columns = [
            '商户单号',
            '交易单号',
            '用户',
            '用户关注时间',
            '用户注册时间',
            '类型',
            '充值金额',
            '支付状态',
            '下单时间',
            '完成时间',
            '推广链接id',
            '推广链接渠道',
            '书名',
            '代理商',
            '渠道商昵称',
            '渠道商Id',
            '渠道商账号',
            '是否分账'
        ];

        header('Content-Description: File Transfer');

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment; filename="vip导出订单明细-'.date('YmdHis', time()).'.csv"');

        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流中

        $condition_referral = []; //推广条件
        if (isset($_GET['ids'])) {
            $condition_referral['orders.referral_id'] = $_GET['ids'];//推广id
            $condition_referral['orders.resource_id'] = [['exp', 'is null'], ['=', 0], 'or'];//资源id
        }
        $channelIds = $this->vipAdminBindModel->getChannelIds($this->auth->id, $channelId, $username, $nickname);
        if (empty($channelIds) && empty($channelId)) {
            fclose($fp);
            exit();
        }
        $allAgentIds = $this->vipAdminBindModel->getAllAgentIds($channelIds);
        $channelIdAndAgentIds = array_merge($channelIds, $allAgentIds);

        $fetch = $this->_getFetchObj($where, $condition_referral, $channelIdAndAgentIds);
        $total = $fetch->count();

        $limit = 1000;

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

        $offset = 0;

        $channelIds = $this->vipAdminBindModel->getChannelIds($this->auth->id, $channelId, $username, $nickname);
        if (empty($channelIds) && empty($channelId)) {
            return json(["total" => 0, "rows" => []]);
        }
        $allAgentIds = $this->vipAdminBindModel->getAllAgentIds($channelIds);
        $channelIdAndAgentIds = array_merge($channelIds, $allAgentIds);

        for($i = 1; $i <= $pages; $i++) {
            $fetch = $this->_getFetchObj($where, $condition_referral, $channelIdAndAgentIds);
            if (!empty($where['orders.createtime']) || !empty($where['createtime'])) {
                $fetch->force('createtime');
            }
            /**
             * @var Query
             */
            $query = $fetch->order($sort, $order)
                ->limit(0, $limit);
            if ($offset) {
                $query->where('orders.id','<',$offset);
            }
            $list = $query->select();

            $orderOne = end($list);
            $offset = $orderOne['id'];

            $orderIds = array_column($list, 'out_trade_no');
            $splitRows = model("PalmpaySplitAccountLog")->field('order_id')->where('order_id', 'in', $orderIds)->where('channel_amount', '>', 0)->select();

            if ($splitRows) {
                $splitRows = ArrayHelper::index($splitRows, 'order_id');
            }

            $ids = array_column($list, 'referral_id');
            //wud 20191021 VIP后台订单明细查询
            $referrals = model('referral')->field(['id','admin_id', 'name'])->where('id', "in", $ids )->select();

            if ( !empty($referrals) ){
                $keyReferrals = [];
                foreach ( $referrals as $kk => $vv ){
                    $keyReferrals[$vv['id']] = [
                        'admin_id' => $vv['admin_id'],
                        'name'     => $vv['name'],
                    ];
                }
            }
            $book_ids = array_column($list, 'book_id');
            $book_data = model('Book')->whereIn('id', $book_ids)->column('id,name');

            $admin_ids = array_column($list, 'admin_id');
            $admin_channel_list = model('admin')
                ->join(['admin' => 'leadAdmin'], 'admin.id = leadAdmin.id')
                ->join(['admin_extend' => 'leadExt'], 'admin.id = leadExt.admin_id')
                ->join(['admin' => 'channelAdmin'], 'leadExt.create_by = channelAdmin.id')
                ->join(['auth_group_access' => 'leadAga'], 'leadAga.uid = leadAdmin.id')
                ->whereIn('admin.id', $admin_ids)
                ->field([
                    'admin.id'              => 'id',
                    'leadAdmin.id'          => 'lead_admin_id',
                    'leadAdmin.username'    => 'lead_admin_username',
                    'leadAdmin.nickname'    => 'lead_admin_nickname',
                    'channelAdmin.id'       => 'channel_admin_id',
                    'channelAdmin.username' => 'channel_admin_username',
                    'channelAdmin.nickname' => 'channel_admin_nickname',
                    'leadAga.group_id'      => 'lead_group_id',
                ])
                ->select();
            $admin_channel_data = [];
            foreach($admin_channel_list as $data){
                $admin_channel_data[$data['id']] = $data;
            }

            foreach ($list as $k => &$v) {
                if ($v['book_id'] && array_key_exists($v['book_id'], $book_data)) {
                    $v['bookname'] = $book_data[$v['book_id']];
                }
                $v['pdpd']= $keyReferrals[$v['referral_id']]['name'] ?? '';
                $v['radmin_id']= $keyReferrals[$v['referral_id']]['admin_id'] ?? '';
                if (array_key_exists($v['admin_id'], $admin_channel_data)) {
                    if(!isset($admin_channel_data[$v['admin_id']]['lead_group_id'])){
                        continue;
                    }
                    if ($admin_channel_data[$v['admin_id']]['lead_group_id'] == AdminConstants::ADMIN_GROUP_ID_CHANNEL) {
                        $v['channel_id'] = $admin_channel_data[$v['admin_id']]['lead_admin_id'];
                        $v['channel_nickname'] = $admin_channel_data[$v['admin_id']]['lead_admin_nickname'];
                        $v['channel_username'] = $admin_channel_data[$v['admin_id']]['lead_admin_username'];
                        $v['agent_nickname'] = '-';
                    } else if($admin_channel_data[$v['admin_id']]['lead_group_id'] == AdminConstants::ADMIN_GROUP_ID_AGENT){
                        $v['channel_id'] = $admin_channel_data[$v['admin_id']]['channel_admin_id'];
                        $v['channel_nickname'] = $admin_channel_data[$v['admin_id']]['channel_admin_nickname'];
                        $v['channel_username'] = $admin_channel_data[$v['admin_id']]['channel_admin_username'];
                        $v['agent_nickname'] = $admin_channel_data[$v['admin_id']]['lead_admin_nickname'];
                    }
                }

                if($v['user_id']){
                    try{
                        $res = model('user')
                            ->setConnect($v['user_id'])
                            ->where(['id' => $v['user_id']])
                            ->field('id,channel_id,nickname,createtime,avatar,subscribe_time')
                            ->select();
                        $res = $res[0];
                    }catch (\Exception $e){
                        Log::info("获取用户信息失败,用户ID：{$v['user_id']}");
                        Log::info($e->getMessage());
                    }

                    if($res){
                        $v['user'] = $res;
                    }else{
                        $v['user'] = ['nickname'=>'未知','subscribe_time'=>'','createtime'=>0];
                    }
                }else{
                    $v['user'] = ['nickname'=>'未知'];
                }

                //是否分账
                $split = '否';
                if ($splitRows && isset($splitRows[$v['out_trade_no']])) {
                    $split = '是';
                }
                // '\t' 可以解决科学技术法问题
                $rowData = [
                    empty($v['out_trade_no'])?"" : "\t".$v['out_trade_no']."\t" ,
                    empty($v['transaction_id'])? "":"\t".$v['transaction_id']."\t" ,
                    $v['user']['nickname']."(id:{$v['user_id']})",
                    $v['user']['subscribe_time'] ? date('Y-m-d H:i:s', $v['user']['subscribe_time']) : '-',
                    date('Y-m-d H:i:s',$v['user']['createtime']),
                    $v['type'] == 1 ? '书币充值':'VIP充值',
                    $v['money']?? '',
                    $v['state'] == 0 ?'未支付':'已支付',
                    date('Y-m-d H:i:s',$v['createtime']),
                    date('Y-m-d H:i:s',$v['finishtime']),
                    $v['referral_id']?? '',
                    $v['pdpd']?? '',
                    $v['bookname']?? '',
                    $v['agent_nickname']?? '',
                    $v['channel_nickname']?? '',
                    $v['channel_id']?? '',
                    $v['channel_username']?? '',
                    $split
                ];

                //需要格式转换，否则会乱码
                mb_convert_variables('GBK', 'UTF-8', $rowData);

                fputcsv($fp, $rowData);

                unset($rowData);

            }

            unset($list);

            //必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
            ob_flush();
            flush();

        }

        fclose($fp);

        exit();


    }




    public function index()
    {
        if (!in_array($this->group, [7, 8])) {
            $this->error('当前用户不是vip也不是vip运营');
        }
        $orderpath = '/admin/viporders/index?ref=addtabs';
        $exportpath = '/admin/viporders/export';
        if (isset($_GET['ids'])) {
            $orderpath .= '&ids=' . $_GET['ids'];
            $exportpath .= '?ids=' . $_GET['ids'];
        }
        $this->assignconfig('orderpath', $orderpath);
        $this->assignconfig('exportpath', $exportpath);
        if ($this->request->isAjax()) {
            $condition_referral = []; //推广条件
            if (isset($_GET['ids'])) {
                $condition_referral['orders.referral_id'] = $_GET['ids'];//推广id
                $condition_referral['orders.resource_id'] = [['exp', 'is null'], ['=', 0], 'or'];//资源id
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $orCondition = [];
            $filter = $this->request->get("filter", '');
            $filter = json_decode($filter, true);
            $where = [];
            $channelId = null;
            $username = null;
            $nickname = null;
            $whereChannel = [];
            if ($filter) {
                foreach ($filter as $k => $v) {
                    switch ($k) {
                        case 'out_trade_no':
                        case 'transaction_id':
                        case 'money':
                        case 'state':
                            $where[$k] = $v;
                            break;
                        case 'createtime':
                            $arr = array_slice(explode(' - ', $v), 0, 2);
                            $where['orders.createtime'] = ['BETWEEN time', $arr];
                            if (abs(strtotime($arr[1])-strtotime($arr[0]) > (86400*31))) {
                                $this->error("查询时间范围超过一个月", null, 13);
                            }
                            break;
                        case 'finishtime':
                            $arr = array_slice(explode(' - ', $v), 0, 2);
                            $where['orders.finishtime'] = ['BETWEEN time', $arr];
                            if (abs(strtotime($arr[1])-strtotime($arr[0]) > (86400*31))) {
                                $this->error("查询时间范围超过一个月", null, 13);
                            }
                            break;
                        case 'channel_nickname';
                            $whereChannel['nickname'] = $v;
                            break;
                        case 'channel_id':
                            $whereChannel['id'] = $v;
                            break;
                        case 'channel_username':
                            $whereChannel['username'] = $v;
                            break;
                        default:
                            break;
                    }
                }
            }
            if ($whereChannel) {
                $channelId = AdminService::instance()->getAdminModel()->where($whereChannel)->column('id');
                if (!$channelId) {
                    return json(["total" => 0, "rows" => []]);
                }
            }

            $channelIds = $this->vipAdminBindModel->getChannelIds($this->auth->id, $channelId, $username, $nickname);
            if (empty($channelIds) && empty($channelId)) {
                return json(["total" => 0, "rows" => []]);
            }
            $allAgentIds = $this->vipAdminBindModel->getAllAgentIds($channelIds);
            $channelIdAndAgentIds = array_merge($channelIds, $allAgentIds);


            $channelList = $this->adminConfigModel
                ->field([
                    'admin_id',
                    'qrcode_image',
                    'json'
                ])
                ->where(['admin_id' => ['in', $channelIds]])
                ->select();
            $aChannels = [];
            foreach ($channelList as $item) {
                $aChannels[$item->admin_id] = [
                    'channel_qrCodeImage' => $this->_getQrCodeImage($item->qrcode_image, $item->json)
                ];
            }

            $totleFetchObj = $this->_getFetchObj($where, $condition_referral, $channelIdAndAgentIds);
            if (array_key_exists('createtime', $filter)) {
                $total = $totleFetchObj->force('createtime')->count();
                $sort = 'orders.createtime';
            } else {
                $total = $totleFetchObj->count();
                $sort = 'orders.id';
            }

            $listFetchObj = $this->_getFetchObj($where, $condition_referral, $channelIdAndAgentIds);

            $list = $listFetchObj
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();
            $ids = $keyReferrals = [];
            foreach ($list as $k => $v) {
                $ids[] = $v['referral_id'];
                $v['user'] = model('user')->setConnect($v['user_id'])->where(['id' => $v['user_id']])->find();
            }
            //wud 20191021 VIP后台订单明细查询
            $referrals = model('referral')->field(['id','admin_id', 'name'])->where('id', "in", $ids )->select();

            if ( !empty($referrals) ){
                $keyReferrals = [];
                foreach ( $referrals as $kk => $vv ){
                    $keyReferrals[$vv['id']] = [
                        'admin_id' => $vv['admin_id'],
                        'name'     => $vv['name'],
                    ];
                }
            }
            $book_ids = array_column($list, 'book_id');
            $book_data = model('Book')->whereIn('id', $book_ids)->column('id,name');

            $admin_ids = array_column($list, 'admin_id');
            $admin_channel_list = model('admin')
                ->join(['admin' => 'leadAdmin'], 'admin.id = leadAdmin.id')
                ->join(['admin_extend' => 'leadExt'], 'admin.id = leadExt.admin_id')
                ->join(['admin' => 'channelAdmin'], 'leadExt.create_by = channelAdmin.id')
                ->join(['auth_group_access' => 'leadAga'], 'leadAga.uid = leadAdmin.id')
                ->whereIn('admin.id', $admin_ids)
                ->field([
                    'admin.id'              => 'id',
                    'leadAdmin.id'          => 'lead_admin_id',
                    'leadAdmin.username'    => 'lead_admin_username',
                    'leadAdmin.nickname'    => 'lead_admin_nickname',
                    'channelAdmin.id'       => 'channel_admin_id',
                    'channelAdmin.username' => 'channel_admin_username',
                    'channelAdmin.nickname' => 'channel_admin_nickname',
                    'leadAga.group_id'      => 'lead_group_id',
                ])
                ->select();
            $admin_channel_data = [];
            foreach($admin_channel_list as $data){
                $admin_channel_data[$data['id']] = $data;
            }
            foreach ($list as $k => &$v) {
                if ($v['book_id'] && array_key_exists($v['book_id'], $book_data)) {
                    $v['bookname'] = $book_data[$v['book_id']];
                }
                $v['pdpd']= $keyReferrals[$v['referral_id']]['name'] ?? '';
                $v['radmin_id']= $keyReferrals[$v['referral_id']]['admin_id'] ?? '';
                if (array_key_exists($v['admin_id'], $admin_channel_data)) {
                    if ($admin_channel_data[$v['admin_id']]['lead_group_id'] == AdminConstants::ADMIN_GROUP_ID_CHANNEL) {
                        $v['channel_id'] = $admin_channel_data[$v['admin_id']]['lead_admin_id'];
                        $v['channel_nickname'] = $admin_channel_data[$v['admin_id']]['lead_admin_nickname'];
                        $v['channel_username'] = $admin_channel_data[$v['admin_id']]['lead_admin_username'];
                        $v['agent_nickname'] = '-';
                        $v['channel_qrimage'] = $aChannels[$v['channel_id']]['channel_qrCodeImage'];
                    } else if($admin_channel_data[$v['admin_id']]['lead_group_id'] == AdminConstants::ADMIN_GROUP_ID_AGENT){
                        $v['channel_id'] = $admin_channel_data[$v['admin_id']]['channel_admin_id'];
                        $v['channel_nickname'] = $admin_channel_data[$v['admin_id']]['channel_admin_nickname'];
                        $v['channel_username'] = $admin_channel_data[$v['admin_id']]['channel_admin_username'];
                        $v['agent_nickname'] = $admin_channel_data[$v['admin_id']]['lead_admin_nickname'];
                        $v['channel_qrimage'] = $aChannels[$v['channel_id']]['channel_qrCodeImage'];
                    }
                }
            }
            $result = array("total" => $total, "rows" => $list);

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

    private function _getQrCodeImage($qrCodeImage, $adminJson)
    {
        if (empty($qrCodeImage)) {
            return 'https://open.weixin.qq.com/qr/code?username=' . $adminJson['authorizer_info']['user_name'];
        } else {
            return $qrCodeImage;
        }
    }

    /**
     * @param $where
     * @param $condition_referral
     * @param $channelIdAndAgentIds
     * @return \app\common\model\Orders
     */
    private function _getFetchObj($where, $condition_referral, $channelIdAndAgentIds)
    {
        $fetchObj = $this->model
            //wu 20191021 优化
//            ->join(['referral' => 'r'], 'r.id=orders.referral_id', 'left')
//            ->join(['book' => 'b'], 'orders.book_id = b.id', 'left')
//            ->join(['admin' => 'leadAdmin'], 'orders.admin_id = leadAdmin.id')
//            ->join(['admin_extend' => 'leadExt'], 'orders.admin_id = leadExt.admin_id')
//            ->join(['admin' => 'channelAdmin'], 'leadExt.create_by = channelAdmin.id')
//            ->join(['auth_group_access' => 'leadAga'], 'leadAga.uid = leadAdmin.id')
//            ->field([
//                'orders.*',
//                'r.name' => 'pdpd',
//                'b.name' => 'bookname',
//                'r.admin_id' => 'radmin_id',
//                'leadAdmin.id' => 'lead_admin_id',
//                'leadAdmin.username' => 'lead_admin_username',
//                'leadAdmin.nickname' => 'lead_admin_nickname',
//                'channelAdmin.id' => 'channel_admin_id',
//                'channelAdmin.username' => 'channel_admin_username',
//                'channelAdmin.nickname' => 'channel_admin_nickname',
//                'leadAga.group_id' => 'lead_group_id',
//            ])
            ->where($where)
            ->where('orders.deduct', 0)
            ->whereIn('admin_id', $channelIdAndAgentIds)
            ->where($condition_referral);

        return $fetchObj;
    }
}