maintenance/application/admin/controller/department/Admin.php

<?php

namespace app\admin\controller\department;

use app\admin\model\department\Department;
use app\admin\model\department\Department as DepartmentModel;
use app\common\controller\Backend;
use \app\admin\model\department\Admin as DepartmentAdminModel;
use \app\admin\model\department\AuthAdmin as AdminModel;
use fast\Random;
use fast\Tree;
use think\Db;
use think\Model;
use think\Validate;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;


/**
 * 部门成员
 */
class Admin extends Backend
{

    protected $tree = null;
    /**
     * 部门人员对象
     * @var null
     */
    protected $dadminModel = null;
    protected $childrenGroupIds = [];//权限组
    /**
     * 部门所有
     * @var array
     */
    protected $allDepartment = [];//
    protected $noNeedRight=['selectpage','getDepartmentIds','departadmintotal','export','import'];


    public function _initialize()
    {
        parent::_initialize();
        $this->childrenGroupIds = $this->auth->getChildrenGroupIds($this->auth->isSuperAdmin());

        $this->dadminModel = new DepartmentAdminModel;
        $departmentList = [];
//        $this->allDepartment = \app\admin\model\department\Admin::getAllDepartmentsArray($this->auth->id,($this->auth->isSuperAdmin()||$this->auth->data_scope)?true:false);
        $this->allDepartment = \app\admin\model\department\Admin::getAllDepartmentsArray($this->auth->id,true);
        $this->allDepartment=collection($this->allDepartment)->toArray();

        $departmentdata = [];
        foreach ($this->allDepartment as $k => $v) {
            $state = ['opened' => true];
            $departmentList[] = [
                'id' => $v['id'],
                'parent' => $v['parent_id'] ? $v['parent_id'] : '#',
                'text' =>preg_replace('/&nbsp;|\s*|│|└|├\s*/','',__($v['name'])),//str_replace("&nbsp;",'', __($v['name'])) ,
                'state' => $state
            ];
        }


        $tree = Tree::instance()->init($this->allDepartment, 'parent_id');
        $departmentOptions = $tree->getTree(0, "<option model='@model_id' value=@id @selected @disabled>@spacer@name</option>");

        $this->view->assign('departmentOptions', $departmentOptions);
        $this->assignconfig('departmentList', $departmentList);

        $result = Tree::instance()->getTreeList(Tree::instance()->getTreeArray(0));
        foreach ($result as $k => $v) {
            $departmentdata[$v['id']] = $v['name'];
        }
        $this->view->assign('departmentdata', $departmentdata);

        //兼容旧版本没有手机号
        $database=config('database');
        $exits_mobile=\think\Db::query("SELECT 1 FROM  information_schema.COLUMNS WHERE  table_name='{$database['prefix']}admin' AND COLUMN_NAME='mobile' limit 1");
        $this->view->assign('exits_mobile', $exits_mobile?1:0);
        $this->assignconfig('exits_mobile', $exits_mobile?1:0);
    }

    /**
     * 成员列表
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        $department_id = $this->request->request("department_id");
        if ($this->request->isAjax()) {
            $this->model = new \app\admin\model\department\AuthAdmin();
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);
            $filter_w = [];
            $authGroupList = \app\admin\model\AuthGroupAccess::field('uid,group_id')
                ->where('group_id', 'in', 8)
                ->select();
            foreach ($authGroupList as $k => $v) {
                $childrenAdminIds[] = $v['uid'];
            }
            $filter_w['id']=['in',$childrenAdminIds];
            if (isset($filter['department_id'])) {
                $department_id = $filter['department_id'];
                unset($filter['department_id']);
                $this->request->get(['filter' => json_encode($filter)]);
            }
            if ($department_id) {
                if (!in_array($department_id,array_column($this->allDepartment,'id'))){
                    $this->error("您所选的部门没有权限");
                }
                $admin_ids = $this->dadminModel->where('department_id', 'in', $department_id)->column('admin_id');
                $filter_w['id'] = ['in', $admin_ids];
            }
//            else if ($this->auth->data_scope!=1&&!$this->auth->isSuperAdmin()){
//                $this->childrenAdminIds = \app\admin\model\department\Admin::getChildrenAdminIds($this->auth->id, true);
//                $filter_w['admin_id']=['in',$this->childrenAdminIds];
//            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->where($where)
                ->where($filter_w)
                ->order($sort, $order)->fetchSql(false)
                ->count();
            $list = $this->model
                ->where($where)
                ->with(['dadmin.department'])
                ->with(['groups.getGroup'])
                ->where($filter_w)
                ->order($sort, $order)
                ->limit($offset, $limit)->fetchSql(false)
                ->select();


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

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



    /**
     * 添加
     * @return string|\think\response\Json
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function add()
    {
        $groupdata = DepartmentModel::getGroupdata(
            $this->childrenGroupIds,
            $this->auth->isSuperAdmin() ? null : $this->auth->getGroups()
        );

        if ($this->request->isPost()) {

            $adminModel = new AdminModel();
            $departmentModel = new DepartmentModel();

            $params = $this->request->post("row/a");
            if ($params) {
                Db::startTrans();
                try {
                    $department_id = $this->request->post("department_id/a");
                    $is_principal = $this->request->post("is_principal",'0','intval');

                    //获取部门信息
                    if (!$department_id) {
                        exception(__("组织不能为空"));
                    }
                    $d_list = $departmentModel->where('id', 'in', $department_id)->select();
                    if (!$d_list) {
                        exception(__("组织不能为空"));
                    }
                    foreach ($d_list as $d_row){
                        if ($d_row['level']!=5){
                            exception(__("学员请选择到区队"));
                        }
                    }

                    if (!Validate::is($params['password'], '\S{6,16}')) {
                        exception(__("Please input correct password"));
                    }
                    $params['depart_id'] = $department_id[0]??0;
                    $params['salt'] = Random::alnum();
                    $params['password'] = md5(md5($params['password']) . $params['salt']);
                    $params['avatar'] = '/assets/img/avatar.png'; //设置新管理员默认头像。
                    $result = $adminModel->validate('Admin.add')->save($params);
                    if ($result === false) {
                        exception($adminModel->getError());
                    }
                    if (!$this->auth->isSuperAdmin()&&isset($params['data_scope'])){
                        unset($params['data_scope']);//超级管理人才能赋予全部数据权限
                    }

                    $admin_id = $adminModel->id;
                    $dadmin = array();
                    //添加部门信息
                    foreach ($d_list as $d_row) {
                        $dadmin[] = ['is_principal'=>$is_principal==1?1:0,'department_id' => $d_row->id, 'organise_id' => $d_row->organise_id ? $d_row->organise_id : $d_row->id, 'admin_id' => $admin_id];
                    }
                    $this->dadminModel->saveAll($dadmin);

                    $group = $this->request->post("group/a");
                    //过滤不允许的组别,避免越权
                    $group = [8];//array_intersect($this->childrenGroupIds, $group);

                    //添加权限默认
                    if (!$group) {
                        exception(__('The parent group exceeds permission limit'));
                    }

                    $dataset = [];
                    foreach ($group as $value) {
                        $dataset[] = ['uid' => $admin_id, 'group_id' => $value];
                    }
                    model('AuthGroupAccess')->saveAll($dataset);


                    Db::commit();
                } catch (\Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign('groupdata', $groupdata);
        return $this->view->fetch();
    }

    /**
     * 修改
     * @param null $ids
     * @return string|\think\response\Json
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function edit($ids = null)
    {

        $this->model = new AdminModel();
        $departmentModel = new DepartmentModel();
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
//        if (!\app\admin\model\department\Admin::checkDataAuth($this->auth,$row,'id')){
//            $this->error(__('You have no permission'));
//        }
        if ($this->request->isPost()) {

            //判断是不是超级管理员编辑
//            if (!$this->auth->isSuperAdmin() && DepartmentAdminModel::isSuperAdmin($row->id)) {
//                $this->error("您无权操作超级管理员");
//            }

            $params = $this->request->post("row/a");

            if ($params) {
                Db::startTrans();
                try {
                    $department_id = $this->request->post("department_id/a");
                    //获取部门信息
                    if (!$department_id) {
                        exception(__("Department can't null"));
                    }
                    $d_list = $departmentModel->where('id', 'in', $department_id)->select();

                    if (!$d_list) {
                        exception(__("Department can't null"));
                    }
                    foreach ($d_list as $d_row){
                        if ($d_row['level']!=5){
                            exception(__("学员请选择到区队"));
                        }
                    }
                    if ($params['password']) {
                        if (!Validate::is($params['password'], '\S{6,16}')) {
                            exception(__("Please input correct password"));
                        }
                        $params['salt'] = Random::alnum();
                        $params['password'] = md5(md5($params['password']) . $params['salt']);
                    } else {
                        unset($params['password'], $params['salt']);
                    }
                    if (!$this->auth->isSuperAdmin()&&isset($params['data_scope'])){
                        unset($params['data_scope']);//超级管理人才能赋予全部数据权限
                    }
                    $params['depart_id'] = $department_id[0]??0;
                    //这里需要针对username和email做唯一验证
                    $adminValidate = \think\Loader::validate('Admin');
                    $adminValidate->rule([
                        'username' => 'require|regex:\w{3,20}|unique:admin,username,' . $row->id,
//                        'email' => 'require|email|unique:admin,email,' . $row->id,
//                        'mobile'    => 'regex:1[3-9]\d{9}|unique:admin,mobile,' . $row->id,
                        'password' => 'regex:\S{32}',
                    ]);
                    $result = $row->validate('Admin.edit')->save($params);
                    if ($result === false) {
                        exception($row->getError());
                    }
                    $exist_departmentids = $this->dadminModel->where('admin_id', $row->id)->column('department_id');
                    $dadmin = array();
                    $deleteids = array_diff($exist_departmentids, $department_id);
                    //添加部门信息
                    foreach ($d_list as $d_row) {
                        if (!in_array($d_row->id, $exist_departmentids)) {
                            $dadmin[] = ['department_id' => $d_row->id, 'organise_id' => $d_row->organise_id ? $d_row->organise_id : $d_row->id, 'admin_id' => $row->id];
                        }
                    }
                    if ($deleteids) {
                        $this->dadminModel->where('admin_id', $row->id)->where('department_id', 'in', $deleteids)->delete();
                    }
                    if (count($dadmin) > 0) {
                        $this->dadminModel->saveAll($dadmin);
                    }


                    // 先移除所有权限
                    model('AuthGroupAccess')->where('uid', $row->id)->delete();

                    $group = $this->request->post("group/a");
                    // 过滤不允许的组别,避免越权
                    $group = [8];//array_intersect($this->childrenGroupIds, $group);
                    if (!$group) {
                        exception(__('The parent group exceeds permission limit'));
                    }

                    $dataset = [];
                    foreach ($group as $value) {
                        $dataset[] = ['uid' => $row->id, 'group_id' => $value];
                    }
                    model('AuthGroupAccess')->saveAll($dataset);

                    //更改姓名，同步更改考试表中的姓名
                    Db::name('real_exam')->where('user_id', $row->id)->update(['user_nickname'=>$params['nickname']]);

                    Db::commit();
                } catch (\Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $groupdata = DepartmentModel::getGroupdata(
            $this->childrenGroupIds,
            $this->auth->isSuperAdmin() ? null : $this->auth->getGroups()
        );
        $grouplist = $this->auth->getGroups($row['id']);
        $groupids = [];
        foreach ($grouplist as $k => $v) {
            $groupids[] = $v['id'];
        }
        $this->view->assign("groupids", $groupids);

        $this->view->assign('row', $row);
        $this->view->assign('department_ids', $this->dadminModel->getDepartmentIds($ids));
        $this->view->assign('groupdata', $groupdata);
        return $this->view->fetch();
    }


    /**
     * 删除
     */
    public function del($ids = "")
    {
        if (!$this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $this->childrenAdminIds = $this->auth->getChildrenAdminIds($this->auth->isSuperAdmin());
        $this->model = new AdminModel();

        $ids = $ids ? $ids : $this->request->post("ids");
        if ($ids) {
            $ids = array_intersect($this->childrenAdminIds, array_filter(explode(',', $ids)));
            // 避免越权删除管理员
            $childrenGroupIds = $this->childrenGroupIds;
            $adminList = $this->model->where('id', 'in', $ids)->where('id', 'in', function ($query) use ($childrenGroupIds) {
                $query->name('auth_group_access')->where('group_id', 'in', $childrenGroupIds)->field('uid');
            })->select();
            if ($adminList) {
                $deleteIds = [];
                foreach ($adminList as $k => $v) {
                    $deleteIds[] = $v->id;
                }
                $deleteIds = array_values(array_diff($deleteIds, [$this->auth->id]));
                if ($deleteIds) {
                    Db::startTrans();
                    try {
                        $this->model->destroy($deleteIds);
                        model('AuthGroupAccess')->where('uid', 'in', $deleteIds)->delete();
                        //删除部门员工信息
                        $this->dadminModel->where('admin_id', 'in', $deleteIds)->delete();;

                        Db::commit();
                    } catch (\Exception $e) {
                        Db::rollback();
                        $this->error($e->getMessage());
                    }
                    $this->success();
                }
                $this->error(__('No rows were deleted'));
            }
        }
        $this->error(__('You have no permission'));
    }

    /**
     * 批量更新
     */
    public function multi($ids = "")
    {
        $this->model = new AdminModel();
        return parent::multi($ids);
    }

    /**
     * 设置部门负责人
     */
    public function principal($ids = "")
    {

        $this->model = new AdminModel();
        $departmentModel = new DepartmentModel();
        $row = $this->model->get($ids);
        if ($this->request->isPost()) {

            //判断是不是超级管理员编辑
            if (!DepartmentAdminModel::isSuperAdmin($this->auth->id) && DepartmentAdminModel::isSuperAdmin($row->id)) {
                $this->error("您无权操作超级管理员");
            }

            $department_id = $this->request->post("department_id/a");
            if ($department_id && $department_id[0]) {
                Db::startTrans();
                try {
                    $d_list = $departmentModel->where('id', 'in', $department_id)->select();

                    if (!$d_list) {
                        exception(__("Department can't null"));
                    }
                    //先移除他所有负责的部门
                    $this->dadminModel->where('admin_id', $row->id)->update(['is_principal' => 0]);

                    //判断选择的部门是否存在，不存在就先把他加入
                    $p_departmentids = array_column($d_list, 'id');
                    $exist_departmentids = $this->dadminModel->where('admin_id', $row->id)->column('department_id');

                    $dadmin = array();
                    //添加部门信息
                    foreach ($d_list as $d_row) {
                        if (!in_array($d_row->id, $exist_departmentids)) {
                            $dadmin[] = ['department_id' => $d_row->id, 'organise_id' => $d_row->organise_id ? $d_row->organise_id : $d_row->id, 'admin_id' => $row->id];
                        }
                    }
                    if (count($dadmin) > 0) {
                        $this->dadminModel->saveAll($dadmin);
                    }
                    //更改为负责人
                    $this->dadminModel->where('admin_id', $row->id)->where('department_id', 'in', $p_departmentids)->update(['is_principal' => 1]);


                    Db::commit();
                } catch (\Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }

            } else {
                //移除他所有负责的部门
                $this->dadminModel->where('admin_id', $row->id)->update(['is_principal' => 0]);
            }
            $this->success();
        }


        $this->view->assign('row', $row);
        $this->view->assign('department_ids', $this->dadminModel::getPrincipalIds($ids));
        return $this->view->fetch();
    }

    /**
     * 获取员工的部门IDs
     */
    public function getDepartmentIds($id = ""){

        $departmentIds=DepartmentAdminModel::getDepartmentIds($id);
        //这里一定要返回有list这个字段,total是可选的,如果total<=list的数量,则会隐藏分页按钮
        $this->success("",'',['departmentIds' => $departmentIds]);
    }

    /**
     * 下拉选择
     * @Internal
     */
    public function selectpage()
    {
        $type=$this->request->request('type');//all代表选择所有
        $custom = ['status' => 'normal'];
        if ($type != "all") {
            $childrenAdminIds = \app\admin\model\department\Admin::getChildrenAdminIds($this->auth->id, true);
            $custom['id'] = ['in', $childrenAdminIds];
        }
        $this->selectpageFields = ['id', 'concat(nickname,"(",id,")") as nickname'];
        $this->request->request(['custom' => $custom]);
        //搜索字段
        $searchfield = (array)$this->request->request("searchField/a");
        $searchfield[]='id';
        $this->request->request(['searchField'=>$searchfield]);
        $this->model = model('admin');

        //设置过滤方法
        $this->request->filter(['trim', 'strip_tags', 'htmlspecialchars']);

        //搜索关键词,客户端输入以空格分开,这里接收为数组
        $word = (array)$this->request->request("q_word/a");
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");
        //搜索条件
        $andor = $this->request->request("andOr", "and", "strtoupper");
        //排序方式
        $orderby = (array)$this->request->request("orderBy/a");
        //显示的字段
        $field = $this->request->request("showField");
        //主键
        $primarykey = $this->request->request("keyField");
        //主键值
        $primaryvalue = $this->request->request("keyValue");
        //搜索字段
        $searchfield = (array)$this->request->request("searchField/a");
        //自定义搜索条件
        $custom = (array)$this->request->request("custom/a");
        //是否返回树形结构
        $istree = $this->request->request("isTree", 0);
        $ishtml = $this->request->request("isHtml", 0);
        if ($istree) {
            $word = [];
            $pagesize = 999999;
        }
        $order = [];
        foreach ($orderby as $k => $v) {
            $order[$v[0]] = $v[1];
        }
        $field = $field ? $field : 'name';

        //如果有primaryvalue,说明当前是初始化传值
        if ($primaryvalue !== null) {
            $where = [$primarykey => ['in', $primaryvalue]];
            $pagesize = 999999;
        } else {
            $where = function ($query) use ($word, $andor, $field, $searchfield, $custom) {
                $logic = $andor == 'AND' ? '&' : '|';
                $searchfield = is_array($searchfield) ? implode($logic, $searchfield) : $searchfield;
                $searchfield = str_replace(',', $logic, $searchfield);
                $word = array_filter(array_unique($word));
                if (count($word) == 1) {
                    $query->where($searchfield, "like", "%" . reset($word) . "%");
                } else {
                    $query->where(function ($query) use ($word, $searchfield) {
                        foreach ($word as $index => $item) {
                            $query->whereOr(function ($query) use ($item, $searchfield) {
                                $query->where($searchfield, "like", "%{$item}%");
                            });
                        }
                    });
                }
                if ($custom && is_array($custom)) {
                    foreach ($custom as $k => $v) {
                        if (is_array($v) && 2 == count($v)) {
                            $query->where($k, trim($v[0]), $v[1]);
                        } else {
                            $query->where($k, '=', $v);
                        }
                    }
                }
            };
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        $list = [];
        $total = $this->model->where($where)->count();
        if ($total > 0) {
            if (is_array($adminIds)) {
                $this->model->where($this->dataLimitField, 'in', $adminIds);
            }

            $fields = is_array($this->selectpageFields) ? $this->selectpageFields : ($this->selectpageFields && $this->selectpageFields != '*' ? explode(',', $this->selectpageFields) : []);

            //如果有primaryvalue,说明当前是初始化传值,按照选择顺序排序
            if ($primaryvalue !== null && preg_match("/^[a-z0-9_\-]+$/i", $primarykey)) {
                $primaryvalue = array_unique(is_array($primaryvalue) ? $primaryvalue : explode(',', $primaryvalue));
                //修复自定义data-primary-key为字符串内容时，给排序字段添加上引号
                $primaryvalue = array_map(function ($value) {
                    return '\'' . $value . '\'';
                }, $primaryvalue);

                $primaryvalue = implode(',', $primaryvalue);

                $this->model->orderRaw("FIELD(`{$primarykey}`, {$primaryvalue})");
            } else {
                $this->model->order($order);
            }

            $datalist = $this->model->where($where)
                ->field($this->selectpageFields)
                ->page($page, $pagesize)
                ->select();

            foreach ($datalist as $index => $item) {
                unset($item['password'], $item['salt']);
                if ($this->selectpageFields == '*') {
                    $result = [
                        $primarykey => isset($item[$primarykey]) ? $item[$primarykey] : '',
                        $field => isset($item[$field]) ? $item[$field] : '',
                    ];
                } else {
                    $result = (($item instanceof Model ? $item->toArray() : (array)$item));
                }
                $result['pid'] = isset($item['pid']) ? $item['pid'] : (isset($item['parent_id']) ? $item['parent_id'] : 0);
                $list[] = $result;
            }

            if ($istree && !$primaryvalue) {
                $tree = Tree::instance();
                $tree->init(collection($list)->toArray(), 'pid');
                $list = $tree->getTreeList($tree->getTreeArray(0), $field);
                if (!$ishtml) {
                    foreach ($list as &$item) {
                        $item = str_replace('&nbsp;', ' ', $item);
                    }
                    unset($item);
                }
            }
        }
        //这里一定要返回有list这个字段,total是可选的,如果total<=list的数量,则会隐藏分页按钮
        return json(['list' => $list, 'total' => $total]);
    }


    public function departadmintotal()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $departmentModel = new DepartmentModel();
        $list = $departmentModel
            ->where($where)->where(['level'=>5])
            ->order($sort, $order)
            ->paginate($limit);
        foreach ($list as $k => $v){
            $v->department_name = '';
            if(!empty($v->parent_id)){
                $row = $departmentModel->where(['id'=>$v->parent_id])->find();
                if(!empty($row->parent_id)){
                    $row2 = $departmentModel->where(['id'=>$row->parent_id])->find();
                    if(!empty($row2->parent_id)){
                        $row3 = $departmentModel->where(['id'=>$row2->parent_id])->find();
                        if(!empty($row3->parent_id)) {
                            $row4 = $departmentModel->where(['id' => $row3->parent_id])->find();
                            $v->department_name = $row4['name'].">>".$row3['name'].">>".$row2['name'].">>".$row['name'].">>".$v['name'];
                        }
                    }
                }
            }
            $v->department_admin_count = Db::name('admin')->where(['depart_id'=>$v['id']])->count();
            $v->alluser = Db::name('admin')->where(['depart_id'=>$v['id']])->select();
            $v->user_ids = Db::name('admin')->where(['depart_id'=>$v['id']])->column('id');
        }
        unset($v);
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    //导入
    public function import(){
        error_reporting(E_ALL);
        ini_set("memory_limit","8000M");
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';
        $this->model = new \app\admin\model\department\AuthAdmin();
        $table = $this->model->getQuery()->getTable();

        $fieldArr['学员账号'] = 'username';
        $fieldArr['学员姓名'] = 'nickname';
        $fieldArr['层次'] = 'cengci';
        $fieldArr['专业'] = 'zhuanye';
        $fieldArr['年级'] = 'nianji';
        $fieldArr['队'] = 'dui';
        $fieldArr['区队'] = 'depart_id';

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 2; $currentRow <= 2; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }
            for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = trim($v);
                    }
                }

                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }
        $idcard_str = '';
        $insert_data = [];

        try {
            foreach ($insert as $key => $v)
            {
                if(!empty($v['username'])){
                    if(empty($v['cengci'])){
                        $idcard_str.= $v['nickname']."：层次不能为空<br/>";
                        continue;
                    }
                    if(empty($v['zhuanye'])){
                        $idcard_str.= $v['nickname']."：专业不能为空<br/>";
                        continue;
                    }
                    if(empty($v['nianji'])){
                        $idcard_str.= $v['nickname']."：年级不能为空<br/>";
                        continue;
                    }
                    if(empty($v['dui'])){
                        $idcard_str.= $v['nickname']."：队不能为空<br/>";
                        continue;
                    }
                    if(empty($v['depart_id'])){
                        $idcard_str.= $v['nickname']."：区队不能为空<br/>";
                        continue;
                    }
                    //层次
                    $cengci = Db::name('department')->where('name',$v['cengci'])->where('parent_id',0)->value('id');
                    if(empty($cengci)){
                        $idcard_str.= $v['nickname']."：层次有误,请核实是否存在<br/>";
                        continue;
                    }else{
                        //专业
                        $zhuanye = Db::name('department')->where('name',$v['zhuanye'])->where('parent_id',$cengci)->value('id');
                        if(empty($zhuanye)){
                            $idcard_str.= $v['nickname']."：专业有误,请核实是否存在或是否为层次下级<br/>";
                            continue;
                        }else{
                            //年级
                            $nianji = Db::name('department')->where('name',$v['nianji'])->where('parent_id',$zhuanye)->value('id');
                            if(empty($nianji)){
                                $idcard_str.= $v['nickname']."：年级有误,请核实是否存在或是否为专业下级<br/>";
                                continue;
                            }else{
                                //队
                                $dui = Db::name('department')->where('name',$v['dui'])->where('parent_id',$nianji)->value('id');
                                if(empty($dui)){
                                    $idcard_str.= $v['nickname']."：队有误,请核实是否存在或是否为年级下级<br/>";
                                    continue;
                                }else{
                                    //区队
                                    $depart_id = Db::name('department')->where('name',$v['depart_id'])->where('parent_id',$dui)->value('id');
                                    if(empty($depart_id)){
                                        $idcard_str.= $v['nickname']."：区队有误,请核实是否存在或是否为队下级<br/>";
                                        continue;
                                    }
                                }
                            }
                        }
                    }
                    $admin_info = Db::name('admin')->where('username',$v['username'])->find();
                    if(empty($admin_info)){
                        $salt = Random::alnum();
                        $insert = [
                            'username'=>$v['username'],
                            'nickname'=>$v['nickname'],
                            'salt'=>$salt,
                            'password'=>$this->auth->getEncryptPassword('123456', $salt),
                            'avatar'=>'/assets/img/avatar.png',
                            'loginfailure'=>0,
                            'email'=>$v['username'].'@163.com',
                            'status'=>'normal',
                            'createtime'=>time(),
                            'updatetime'=>time(),
                            'depart_id'=>$depart_id??0,
                        ];
                        $adminId = Db::name('admin')->insertGetId($insert);
                        $add = [
                            'department_id'=>$depart_id??0,
                            'admin_id'=>$adminId,
                            'create_time'=>time(),
                            'update_time'=>time(),
                        ];
                        $departmentId = Db::name('department_admin')->insertGetId($add);
                        $group_info = Db::name('auth_group_access')->where('uid',$adminId)->find();
                        if(empty($group_info)){
                            $arr = [
                                'uid'=>$adminId,
                                'group_id'=>8
                            ];
                            Db::name('auth_group_access')->insertGetId($arr);
                        }
                    }else{
                        //已存在更新
                        Db::name('admin')->where('id',$admin_info['id'])->update(['depart_id'=>$depart_id]);
                        Db::name('department_admin')->where('admin_id',$admin_info['id'])->update(['department_id'=>$depart_id]);
                    }
                }
            }
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }
        if(!empty($idcard_str) ){
            $msg = '导入成功,';
            if (!empty($idcard_str)) {
                $msg .= '以下数据不合理：<br />' . $idcard_str;
            }
            $this->success($msg,'',[],15);
        }else{
            $this->success('导入成功');
        }
    }



    //导出
    public function export(){

        $this->model = new \app\admin\model\department\AuthAdmin();
        $department_id = $this->request->request("department_id");
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);
        $filter_w = [];
        if (isset($filter['department_id'])) {
            $department_id = $filter['department_id'];
            unset($filter['department_id']);
            $this->request->get(['filter' => json_encode($filter)]);
        }
        if ($department_id) {
            $filter_w['depart_id'] = ['in', $department_id];
        }
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        $list = $this->model->where($where)->where(['depart_id'=>['>',0]])->where($filter_w)->order($sort, $order)->select();

        $xlsName = '学员信息';
        $this->exportExcel($xlsName,'Excel2007', $list);
    }


    /**
     * 输出到浏览器(需要设置header头)
     * @param string $fileName 文件名
     * @param string $fileType 文件类型
     */
    function exportExcel($fileName, $fileType,$data)
    {
        //文件名称校验
        if (!$fileName) {
            trigger_error('文件名不能为空', E_USER_ERROR);
        }
        //Excel文件类型校验
        $type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
        if (!in_array($fileType, $type)) {
            trigger_error('未知文件类型', E_USER_ERROR);
        }

        $ext = '';

        if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
            header('Cache-Control: max-age=0');
            $ext = 'Xlsx';
        } else { //Excel5
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $fileName  . '.xls"');
            header('Cache-Control: max-age=0');
            $ext = 'Xls';
        }
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->getColumnDimension('A')->setWidth(15);
        $worksheet->getColumnDimension('B')->setWidth(15);
        $worksheet->getColumnDimension('C')->setWidth(15);
        $worksheet->getColumnDimension('D')->setWidth(15);

        $worksheet->getRowDimension('1')->setRowHeight(25);

        //设置工作表标题名称
        $worksheet->setTitle('学员信息');

        $worksheet->setCellValue('A1',"序号");
        $worksheet->setCellValue('B1',"学员账号");
        $worksheet->setCellValue('C1',"学员姓名");
        $worksheet->setCellValue('D1',"区队");
        
        
        foreach ($data as $ky => $value)
        {
            $qudui = Db::name('department')->where('id',$value['depart_id'])->value('name');
            $lie = $ky+2; 
            $worksheet->setCellValue('A'.$lie,++$ky);
            $worksheet->setCellValue('B'.$lie,$value['username']);
            $worksheet->setCellValue('C'.$lie,$value['nickname']);
            $worksheet->setCellValue('D'.$lie,$qudui);
        }
        

        $titlestyleArray = [
            'font' => [
                'name' => '黑体',
                'size' => 14
            ],
        ];
        $headerStyleArray = [
            'font' => [
                'name' => '方正小标宋简体',
                'size' => 18
            ],
        ];

        $commonStyleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ]
        ];
        $borderStyleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
            'font' => [
                'name' => '黑体',
            ],
        ];
        $worksheet->getStyle('A1:D1')->applyFromArray($titlestyleArray);
        $worksheet->getStyle('A1:D1')->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A2:R2')->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A3:R3')->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A'.$footer_total.':R'.$footer_total)->applyFromArray($commonStyleArray);
        // $worksheet->getStyle('A1:C1')->applyFromArray($borderStyleArray);
        // $worksheet->mergeCells('A1:R1');
        $writer = IOFactory::createWriter($spreadsheet,$ext);
        $writer->save('php://output');
        die();
    }



}