NN2022081301/application/admin/controller/store/System.php

<?php

namespace app\admin\controller\store;

use app\common\controller\AdminController;
use app\http\IResponse;
use Lettered\Support\Upload;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;

class System extends AdminController
{

    /**
     * 数据统计
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/17 11:34
     *
     * @return mixed
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getAgentStat()
    {
        // 接收数据
        $where = [];
        //组合搜索
        !empty(input('areaId')) && $where[]
            = ['area_id', 'eq', input('areaId')];
        // 时间处理
        if (!empty(input('time'))){
            list($start, $end) = str2arr(trim(input('time')),'-');
            $where[] = ['created_at', 'between', [strtotime($start), strtotime($end)]];
        }

        // 消费总额包含 商品、接单、技能、摩的
        // 直接读取支付记录表吧
        $coun  = [
          'total' => model('common/OrderPaylog')->sum('pay_price'),
          'days' => model('common/OrderPaylog')->whereTime('created_at','d')->sum('pay_price'),
          'month' => model('common/OrderPaylog')->whereTime('created_at','m')->sum('pay_price'),
          'year' => model('common/OrderPaylog')->whereTime('created_at','y')->sum('pay_price')
        ];
        // 商品订单统计
        // 摩的订单统计
        // 配送订单统计
        // 技能订单统计
        $sdata = [
            'goods' => [
                'total' => db('goods_order')->where($where)->count(),
                'days' => db('goods_order')->whereTime('created_at','d')->where($where)->count(),
                'month' => db('goods_order')->whereTime('created_at','m')->where($where)->count(),
                'year' => db('goods_order')->whereTime('created_at','y')->where($where)->count(),
            ],
            'motor' => [
                'total' => db('taxi_order')->where($where)->count(),
                'days' => db('taxi_order')->whereTime('created_at','d')->where($where)->count(),
                'month' => db('taxi_order')->whereTime('created_at','m')->where($where)->count(),
                'year' => db('taxi_order')->whereTime('created_at','y')->where($where)->count(),
            ],
            'skill' => [
                'total' => db('skill_order')->where($where)->count(),
                'days' => db('skill_order')->whereTime('created_at','d')->where($where)->count(),
                'month' => db('skill_order')->whereTime('created_at','m')->where($where)->count(),
                'year' => db('skill_order')->whereTime('created_at','y')->where($where)->count(),
            ],
            'mission' => [
                'total' => db('mission_order')->where($where)->count(),
                'days' => db('mission_order')->whereTime('created_at','d')->where($where)->count(),
                'month' => db('mission_order')->whereTime('created_at','m')->where($where)->count(),
                'year' => db('mission_order')->whereTime('created_at','y')->where($where)->count(),
            ]
        ];
        // 统计
        $order = [  'total' => 0, 'days' => 0, 'month' => 0, 'year' => 0, ];

        foreach ($sdata as $item){
            $order['total'] += $item['total'];
            $order['days'] += $item['days'];
            $order['month'] += $item['month'];
            $order['year'] += $item['year'];
        }
        $data['order'] = $order;

        // 用户 ->where('deleted_at','=','0') 不包含删除
        $user  = [
            'total' => db('users')->where('deleted_at','=','0')->count(),
            'days' => db('users')->whereTime('created_at','d')->where('deleted_at','=','0')->count(),
            'month' => db('users')->whereTime('created_at','m')->where('deleted_at','=','0')->count(),
            'year' => db('users')->whereTime('created_at','y')->where('deleted_at','=','0')->count()
        ];
        $data['user'] = $user;

        // 统计图
        $res = [];
        
        for ($month = 1; $month <=12 ; $month ++){

                $res[] = model('common/GoodsOrder')
                ->where($where)
                ->whereTime('created_at', 'between', $this->getMonthTime($month))
                ->count();;
        }
        $data['coun'] = $coun;
        $data['chart'] = $res;
        return IResponse::success($data,"加载数据成功");
    }

    private function getMonthTime($month)
    {
        // 
        $firstday = date('Y-m-01', strtotime(date('Y') . '-' . $month . '-1'));
        $lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
        return array($firstday,$lastday);
    }


    /**
     *导出
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/17 12:51
     *
     * @return mixed
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function exportDataSheet()
    {
        $param = $this->request->param();
        
        $valid = $this->validate($param,[
            'model| 导出数据' => 'require'
        ]);
        // 错误返回
        (true !== $valid) && IResponse::failure($valid);
        // 一些条件
        $where = [];
        // 时间处理
        if (!empty(input('created_at'))){
            list($start, $end) = str2arr(input('created_at'),'-');
            $where[] = ['created_at', 'between', [strtotime($start), strtotime($end)]];
        }
        switch ($param['model']){
            case "users":
                $this->exportUserSheet($where);
                break;
            case "seller":
                $this->exportSellerSheet($where);
                break;
            case "order":
                $this->exportOrderSheet($where);
                break;
        }
        return IResponse::failure("数据异常");
    }

    /**
     * 导出会员数据
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/17 12:04
     *
     * @param $where
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    private function exportUserSheet($where)
    {
        $list = db('users')->where($where)->select();
        // 是否空数据
        if (!$list){
            IResponse::failure("查找不到数据");
        }
        foreach ($list as $key => &$item) {
            // 类型处理
            $item['gender'] = $item['gender'] > 0 ? "男" : "女";
            $item['is_seller'] = $item['is_seller'] > 0 ? "是" : "否";
            $item['is_agent'] = $item['is_agent'] == 2 ? "是" : "否";
            $item['is_verify'] = $item['is_verify'] == 2 ? "已实名" : "未实名";

            $item['created_at'] = date('Y-m-d H:i:s', $item['created_at']);
            $item['status'] =  $item['status'] >= 1 ? '正常' : '锁定';
        }

        $head = ['会员号', 'OpenID', '会员昵称', '联系方式', '余额', '资产', '是否卖家', '是否代理', '是否实名', '注册时间'];// 表头信息
        $keys = ['card_id', 'open_id', 'nickname', 'mobile', 'balance', 'property', 'is_seller', 'is_agent', 'is_verify', 'created_at'];

        $this->downloadExcel("会员记录" . date("YmdHis", time()), $list, $head, $keys);
    }

    /**
     * 导出商户数据
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/17 12:49
     *
     * @param $where
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    private function exportSellerSheet($where)
    {
        $list = db('seller')->where($where)->select();

        // 是否空数据
        if (!$list){
            IResponse::failure("查找不到数据");
        }
        foreach ($list as $key => &$item) {
            // 类型处理
            $item['is_allow'] = $item['is_allow'] == 2 ? "通过" : $item['is_allow'] == 2 ? "待审核" : "未提交";
            // 地址拼接
            $item['address'] = $item['province'] . $item['city'] . $item['country'] .$item['address'];

            $item['fd_img'] =  dejson($item['fd_img']);

            $item['created_at'] = date('Y-m-d H:i:s', $item['created_at']);
            $item['status'] =  $item['status'] >= 1 ? '正常' : '锁定';
        }
//        halt($list);
        $head = ['店铺名称', '店铺地址','主营产品','店铺照片','经度', '维度', '联系人', '联系方式', '身份证', '店铺审核', '备注信息', '注册时间'];// 表头信息
        $keys = ['seller_name', 'address','products','fd_img', 'lng','lat', 'contact', 'mobile', 'id_card', 'is_allow','remark', 'created_at'];

        $this->downloadExcel("店铺记录" . date("YmdHis", time()), $list, $head, $keys);
    }

    /**
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/17 10:52
     *
     * @param array $where
     *
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    private function exportOrderSheet($where)
    {
        //TODO 可以加一些时间查询，地区查询等
        $list = db('goods_order')->alias('g')
            ->join('ins_users u','u.id = g.user_id')
            ->join('ins_seller s','s.id = g.seller_id')
            ->join('ins_users_addr d','d.id = g.addr_id')
            ->join('ins_china c','c.id = g.area_id')
            ->field('g.*,u.nickname,s.seller_name,c.name as area_name,d.name,d.mobile,d.province,d.city,d.country,d.detail')
            ->select();
        foreach ($list as $key => &$item){
            // 类型处理
            $item['is_pin'] = $item['is_pin'] > 0 ? "是" : "否";
            $item['pin_rebate_rec'] = $item['pin_rebate_rec'] > 0 ? "已领取" : "未领取";
            // 地址拼接
            $item['address'] = $item['province'] . $item['city'] . $item['country'] .$item['detail'] . "(" . $item['name'] . $item['mobile'] . ")" ;
            // 时间处理
            $item['pin_rebate_expired'] = $item['pin_rebate_expired'] > 0 ? date('Y-m-d H:i:s',$item['pin_rebate_expired']) : 'NULL';
            $item['paid_at'] = $item['paid_at'] > 0 ? date('Y-m-d H:i:s',$item['paid_at']) : 'NULL';
            $item['refund_at'] = $item['refund_at'] > 0 ? date('Y-m-d H:i:s',$item['refund_at']) : 'NULL';
            $item['shipping_at'] = $item['paid_at']  > 0 ? date('Y-m-d H:i:s',$item['shipping_at']) : 'NULL';
            $item['received_at'] = $item['paid_at']  > 0 ? date('Y-m-d H:i:s',$item['received_at']) : 'NULL';
            $item['created_at'] = date('Y-m-d H:i:s',$item['created_at']);
            // 订单状态
            // 0： 已关闭 1：待支付， 2：待发货，3：待收货 ， 4： 已完成 ， 5： 待开团 ， 6： 未中团
            $status = "已关闭";
            switch ($item['status']) {
                case 1 :
                    $status = "待支付";
                    break;
                case 2 :
                    $status = "待发货";
                    break;
                case 3 :
                    $status = "待收货";
                    break;
                case 4 :
                    $status = "已完成";
                    break;
                case 5 :
                    $status = "待开团";
                    break;
                case 6 :
                    $status = "未中团";
                    break;
            }
            $item['status'] =  $status;
        }

        $head = ['订单号','区域号', '区域名称','经营店铺', '下单用户', '收获地址', '订单金额', '付款金额', '是否拼团', '是否拼团','返现金额领取', '拼团返现过期时间', '付款时间','退款时间', '发货时间', '收货时间', '下单时间', '订单状态'];// 表头信息
        $keys = ['order_no','area_id','area_name', 'seller_name', 'nickname','address', 'total_price', 'pay_price', 'is_pin', 'pin_rebate_rec', 'pin_rebate', 'pin_rebate_expired', 'paid_at', 'refund_at', 'shipping_at', 'received_at', 'created_at', 'status'];

        $this->downloadExcel("订单记录" . date("YmdHis", time()), $list, $head, $keys);
    }
    //报表导出核心方法
    //* 导出excel表
    //* $data：要导出excel表的数据，接受一个二维数组
    //* $name：excel表的表名
    //* $head：excel表的表头，接受一个一维数组
    //* $key：$data中对应表头的键的数组，接受一个一维数组
    //* 备注：此函数缺点是，表头（对应列数）不能超过26；
    //*循环不够灵活，一个单元格中不方便存放两个数据库字段的值
    public function downloadExcel($name = '表名称', $data = [], $head = [], $keys = [])
    {
        $count = count($head);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }

        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                if ($i == 65) {
                    $sheet->setCellValueExplicit(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]], DataType::TYPE_STRING);
                }elseif (is_array($item[$keys[$i - 65]])){
                    $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), arr2str($item[$keys[$i - 65]]));
//                    foreach ($item[$keys[$i - 65]] as $ki){
//                        $drawing[$key + 2] = new Drawing();
//                        $drawing[$key + 2]->setName('Logo');
//                        $drawing[$key + 2]->setDescription('Logo');
//                        $drawing[$key + 2]->setPath($this->app->getRootPath() . 'public' . filter_annex_url($ki));
//                        $drawing[$key + 2]->setWidth(80);
//                        $drawing[$key + 2]->setHeight(80);
//                        $drawing[$key + 2]->setCoordinates(strtoupper(chr($i)) . ($key + 2));
//                        $drawing[$key + 2]->setWorksheet($spreadsheet->getActiveSheet());
//                    }
                }
                else
                    $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(30); //固定列宽

            }

        }
    
        // 全局行高
       // $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(100);
        $writer = new Xlsx($spreadsheet);
        
        $path = $this->app->getRootPath(). '/public/uploads/export/';
        // 
        if(!is_dir($path)){
            mkdir($path, 0755);
        }
    
        // 保存到文件
        $writer->save( $path . $name .'.xlsx');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        
        return IResponse::success([
            'url' =>  get_annex_url('/export/' . $name .'.xlsx')
        ],"导出数据成功");
    }
    /**
     * 关于我们
     *
     * @author 许祖兴 < zuxing.xu@lettered.cn>
     * @date 2020/7/7 11:43
     *
     * @return mixed
     */
    public function about()
    {
        if ($this->request->isPost()){
            // 接收数据
            $params = $this->request->param();
            // 校验
            $valid = $this->validate($params, [
                'content|内容数据' => 'require'
            ]);
            (true !== $valid) && IResponse::failure($valid);
            // base64encode
            // $params['content'] = base64_encode($params['content']);
            // 更新数据
            model('common/SystemConfig')->update([
                'value' => $params['content']
            ],['name' => 'about','group' => 'store']);
            return IResponse::success("更新成功");
        }

        return IResponse::success([
            'content' => sys_config('about','store')
        ]);
    }
}