crm/app/sys/controller/SmartScreenMore.php

<?php

namespace app\sys\controller;

use app\model\ConstructionStep;
use app\model\Employee;
use app\model\Org;
use app\model\SmartScreenConstruction;
use app\model\SmartScreenImportLog;
use app\model\SmartScreenProject;
use app\model\SmartScreenProjectCate;
use cutExcel\MyreadFilter;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;
use think\facade\Log;
use think\facade\Queue;
use think\facade\View;

class SmartScreenMore
{
    /**
     * 在施工地列表
     */
    public function construction() {
        $root_id = request()->employee['root_id'];
        if (!request()->isAjax()) {
            $step_list = ConstructionStep::where('root_id', '=', $root_id)->order('order asc')->select()->toArray();
            View::assign('step_list', $step_list);
            return View::fetch();
        }
        $param = request()->only(['page'=> 1, 'limit'=> 15, 'keyword'=> '', 'emp_name'=> '', 'status'=> '']);

        $model = SmartScreenConstruction::with(['designer', 'employee'])->where([['root_id', '=', $root_id]]);
        if (!empty($param['keyword'])){
            $where_k = [['owner_name|address', 'like', '%' . $param['keyword'] . '%']];
            $model = $model->where($where_k);
        }
        if (!empty($param['emp_name'])) {
            $where_e = [['project_manager|inspection', 'like', '%' . $param['emp_name'] . '%']];
            $emp_name = $param['emp_name'];

            $designer_org = Org::where([['path', 'like', $root_id . '%'], ['org_type', '=', 2], ['status', '=', 1]])->column('id');
            $designer_id = Employee::where([['root_id', '=', $root_id], ['name', 'like', '%' . $param['emp_name'] . '%'], ['state', '=', '在职'], ['org_id', 'in', $designer_org]])->column('id');
            $where_d = [['designer_id', 'in', $designer_id]];

            if (empty($designer_id)) {
                $model = $model->where($where_e);
            } else {
                $model = $model->where(function ($query) use ($emp_name, $where_d) {
                    $query->whereOr([[['project_manager', 'like', '%' . $emp_name . '%']], [['inspection', 'like', '%' . $emp_name . '%']], $where_d]);
                });
            }
        }

        if (!empty($param['status'])) {
            $where_s = [['status', 'like', '%' . $param['status'] . '%']];
            $model = $model->where($where_s);
        }
        $count = $model->count();

        $list = $model->order('addtime desc')->page($param['page'], $param['limit'])->select()->toArray();

        return json(['code'=> 0, 'data'=> $list, 'count'=> $count]);
    }

    /**
     * 在施工地添加
     */
    public function construction_add(){
        $root_id = request()->employee['root_id'];
        if (!request()->isPost()) {
            /* $step_list = ConstructionStep::where('root_id', '=', $root_id)->order('order asc')->select()->toArray();
            View::assign('step_list', $step_list); */

            //设计师获取
            $w[] = ['path', 'like', $root_id . '-%'];
            $w[] = ['org_type', '=', 2];
            $orgs = Org::where($w)->column('id');
            $designer = Employee::where([['org_id', 'in', $orgs], ['state', '=', '在职'], ['show', '=', 0]])->field('id,name,initials s')->order('s asc')->select()->toArray();
            View::assign('designer', $designer);
            return View::fetch();
        }

        $param = request()->only(['owner_name', 'address', 'designer_id', 'project_manager', 'inspection', 'status']);

        $param['employee_id'] = request()->employee['id'];
        $param['root_id'] = request()->employee['root_id'];

        $result = SmartScreenConstruction::create($param);

        if ($result !== false) {
            return json(['code'=> 0, 'data'=> [], 'msg'=> '保存成功']);
        } else {
            return json(['code'=> 1, 'data'=> [], 'msg'=> '保存失败']);
        }
    }

    /**
     * 在施工地编辑
     */
    public function construction_edit(){
        $root_id = request()->employee['root_id'];
        $id = input('id', '', 'intval');
        $info = SmartScreenConstruction::where([['root_id', '=', $root_id], ['id', '=', $id]])->findOrEmpty();
        if (!request()->isPost()) {
            if (!$info->isEmpty()) {
                View::assign('data', $info);
            }

            View::assign('id', $id);

            /* $step_list = ConstructionStep::where('root_id', '=', $root_id)->order('order asc')->select()->toArray();
            View::assign('step_list', $step_list); */

            //设计师获取
            $w[] = ['path', 'like', $root_id . '-%'];
            $w[] = ['org_type', '=', 2];
            $orgs = Org::where($w)->column('id');
            $designer = Employee::where([['org_id', 'in', $orgs], ['state', '=', '在职'], ['show', '=', 0]])->field('id,name,initials s')->order('s asc')->select()->toArray();
            View::assign('designer', $designer);

            return View::fetch();
        }

        $param = request()->only(['id', 'owner_name', 'address', 'designer_id', 'project_manager', 'inspection', 'status']);
        if ($info->isEmpty()) {
            return json(['code'=> 1, 'msg'=> '操作失败']);
        }

        $result = $info->save($param);

        if ($result !== false) {
            return json(['code'=> 0, 'data'=> [], 'msg'=> '保存成功']);
        } else {
            return json(['code'=> 1, 'data'=> [], 'msg'=> '保存失败']);
        }  
    }

    /**
     * 在施工地删除
     */
    public function construction_delete() {
        $id = input('id', '', 'intval');
        $root_id = request()->employee['root_id'];

        $result = SmartScreenConstruction::where([['root_id', '=', $root_id], ['id', '=', $id]])->delete();

        if ($result !== false) {
            return json(['code'=> 0, 'msg'=> '删除成功']);
        } else {
            return json(['code'=> 1, 'msg'=> '删除失败']);
        }
    }

    /**
     * 项目分类添加
     */
    public function project_cate_add(){
        if (!request()->isPost()) {
            return View::fetch();
        }
        $name = input('name', '', 'trim');

        $root_id = request()->employee['root_id'];
        $employee_id = request()->employee['id'];
        $data = [
            'name'=> $name,
            'root_id'=> $root_id,
            'employee_id'=> $employee_id
        ];

        $result = SmartScreenProjectCate::create($data);
        if ($result !== false) {
            return json(['code'=> 0, 'msg'=> '添加成功']);
        } else {
            return json(['code'=> 1, 'msg'=> '添加失败']);
        }
    }

    /**
     * 项目分类编辑
     */
    public function project_cate_edit(){
        $id = input('id', '', 'intval');
        $root_id = request()->employee['root_id'];
        $info = SmartScreenProjectCate::where([['id', '=', $id], ['root_id', '=', $root_id]])->findOrEmpty();
        if (!request()->isPost()) {
            View::assign('data', $info);
            return View::fetch();
        }
        if($info->isEmpty()){
            return json(['code'=> 1, 'msg'=> '编辑失败']);
        }
        $name = input('name', '', 'trim');

        $root_id = request()->employee['root_id'];
        $info->name = $name;

        $result = $info->save();
        if ($result !== false) {
            return json(['code'=> 0, 'msg'=> '编辑成功']);
        } else {
            return json(['code'=> 1, 'msg'=> '编辑失败']);
        }
    }

    /**
     * 项目分类删除
     */
    public function project_cate_delete(){
        $id = input('id', '', 'intval');
        $root_id = request()->employee['root_id'];
        $info = SmartScreenProjectCate::where([['id', '=', $id], ['root_id', '=', $root_id]])->findOrEmpty();
        
        if($info->isEmpty()){
            return json(['code'=> 1, 'msg'=> '删除失败']);
        }

        Db::startTrans();
        $result = $info->delete();
        $result1 = SmartScreenProject::where([['cate_id', '=', $id]])->delete();
        
        if ($result !== false && $result1 !== false) {
            Db::commit();
            return json(['code'=> 0, 'msg'=> '删除成功']);
        } else {
            Db::rollback();
            return json(['code'=> 1, 'msg'=> '删除失败']);
        }
    }

    /**
     * 项目清单
     */
    public function project() {
        $root_id = request()->employee['root_id'];
        if (!request()->isAjax()) {
            return View::fetch();
        }
        $param = request()->only(['page'=> 1, 'limit'=> 15, 'keyword'=> '']);

        $where[] = ['root_id', '=', $root_id];

        $keyword = $param['keyword'];
        $list = SmartScreenProjectCate::with(['project'=> function($query) use ($keyword){
            if ($keyword) {
                $query->where('name', 'like', '%' . $keyword . '%')->with('employee');
            } else {
                $query->with('employee');
            }
        }])->withCount(['project'=> function($query) use ($keyword){
                $query->where('name', 'like', '%' . $keyword . '%')->with('employee');
        }])->where('root_id', '=', $root_id)->page($param['page'], $param['limit'])->select()->toArray();

        $count = SmartScreenProjectCate::where($where)->count();

        return json(['code'=> 0, 'data'=> $list, 'count'=> $count]);
    }

    /**
     * 项目清单添加
     */
    public function project_add(){
        $root_id = request()->employee['root_id'];
        if (!request()->isPost()) {
            $cate_list = SmartScreenProjectCate::where('root_id', '=', $root_id)->select()->toArray();
            View::assign('cate_list', $cate_list);
            return View::fetch();
        }

        $param = request()->only(['name', 'category', 'unit', 'price', 'content', 'cate_id']);

        $param['employee_id'] = request()->employee['id'];
        $param['root_id'] = $root_id;

        $result = SmartScreenProject::create($param);

        if ($result !== false) {
            return json(['code'=> 0, 'data'=> [], 'msg'=> '保存成功']);
        } else {
            return json(['code'=> 1, 'data'=> [], 'msg'=> '保存失败']);
        }
    }

    /**
     * 项目清单编辑
     */
    public function project_edit(){
        $root_id = request()->employee['root_id'];
        $id = input('id', '', 'intval');
        $info = SmartScreenProject::where([['root_id', '=', $root_id], ['id', '=', $id]])->findOrEmpty();
        if (!request()->isPost()) {
            if (!$info->isEmpty()) {
                View::assign('data', $info);
            }

            View::assign('id', $id);

            $cate_list = SmartScreenProjectCate::where('root_id', '=', $root_id)->select()->toArray();
            View::assign('cate_list', $cate_list);

            return View::fetch();
        }

        $param = request()->only(['id', 'name', 'category', 'unit', 'price', 'content', 'cate_id']);
        if ($info->isEmpty()) {
            return json(['code'=> 1, 'msg'=> '操作失败']);
        }

        $result = $info->save($param);

        if ($result !== false) {
            return json(['code'=> 0, 'data'=> [], 'msg'=> '保存成功']);
        } else {
            return json(['code'=> 1, 'data'=> [], 'msg'=> '保存失败']);
        }  
    }

    /**
     * 项目清单删除
     */
    public function project_delete() {
        $id = input('id', '', 'intval');
        $root_id = request()->employee['root_id'];

        $result = SmartScreenProject::where([['root_id', '=', $root_id], ['id', '=', $id]])->delete();

        if ($result !== false) {
            return json(['code'=> 0, 'msg'=> '删除成功']);
        } else {
            return json(['code'=> 1, 'msg'=> '删除失败']);
        }
    }

    /**
     * 导入前日志存储
     */
    public function importLog()
    {
        $type = input('type', 1, 'intval');
        if (!request()->isPost()) {
            View::assign('type', $type);
            return View::fetch();
        }
        $request = request();
        $param = $request->only(['file_name', 'file', 'type']);

        $logData = [
            'name' => $param['file_name'],
            'type' => $param['type'],
            'count' => 0,
            'valid_count' =>  0,
            'invalid_count' => 0,
            'path'=> $param['file'],
            'employee_id' => $request->employee->id,
            'root_id'=> $request->employee->root_id
        ];

        $data = SmartScreenImportLog::create($logData);

        if ($data == false) {
            return json(['code' => 1, 'msg' => '添加失败']);
        }
        $data->status = 1;
        $data->save();

        /* $jobHandlerClassName = 'app\jobs\SmartScreenImport'; 
        $jobQueueName = 'smart_screen_import';
        $orderData = ['id'=> $data->id, 'type'=> $type];  //这个是需要传到消费者的数据
        Queue::later(0, $jobHandlerClassName, $orderData, $jobQueueName); */

        json(['code' => 0, 'msg' => '正在导入中，请刷新列表查看'])->send();
        
        // 开始导入
        try {
            $tmp_file = $_FILES['file']['tmp_name'];
            $content = file_get_contents($tmp_file);
            if ($content) {
                $data->tmp_path = $tmp_file;
                $data->save();

                //实例化PHPExcel类
                $reader = IOFactory::createReader('Xlsx');
                if (!$reader->canRead($tmp_file)) {
                    Log::record('-------screen read error------')->save();
                    Log::record('file canRead false')->save();
                    Log::record('-------screen read error------')->save();
                } else {
                    $rows = 100000;
                    $start_rows = 1;
            
                    $end_rows = $start_rows + $rows;
                    $MyReadFilter = new MyreadFilter($start_rows, $end_rows);
                    $reader->setReadFilter($MyReadFilter);
            
                    $reader->setReadDataOnly(true);
            
                    $objPHPExcel = $reader->load($tmp_file);
                    $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                    $highestRow = $sheet->getHighestRow();       // 取得总行数
                    $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            
                    \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
                    $lines = $highestRow - 1;
            
                    $root_id = $request->employee->root_id;
                    if ($lines > 0) {
                        $datas = [];
                        $j = $start_rows + 1;
            
                        for ($j; $j <= $highestRow; $j++) {
                            if ($type == 1) {
                                $datas[] = [
                                    'owner_name' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                                    'address' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
                                    'designer' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
                                    'project_manager' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                                    'inspection' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                                    'status' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
                                    'row'=> $j
                                ];
                            } elseif ($type == 2) {
                                $datas[] = [
                                    'cate' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                                    'category' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
                                    'name' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
                                    'unit' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                                    'price' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                                    'content' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
                                    'row'=> $j
                                ];
                            }
                        }

                        $cate_list = SmartScreenProjectCate::where('root_id', '=', $root_id)->column('id', 'name');
            
                        $save_data = [];
            
                        foreach ($datas as $k => $v) {
                            $one_cus = [];
                            if ($type == 1) {
                                $designer_id = 0;
                                if (!empty($v['designer'])) {
                                    $designer_id = Employee::where([['name', '=', $v['designer']], ['root_id', '=', $root_id]])->value('id');
                                }
                                /* $status = 0;
                                if (!empty($v['status'])) {
                                    $status = ConstructionStep::where([['root_id', '=', $root_id], ['name', '=', $v['status']]])->value('id');
                                } */
            
                                $one_cus['owner_name'] = $v['owner_name'];
                                $one_cus['address'] = $v['address'];
                                $one_cus['designer_id'] = $designer_id ? $designer_id : 0;
                                $one_cus['project_manager'] = $v['project_manager'];
                                $one_cus['inspection'] = $v['inspection'];
                                $one_cus['status'] = $v['status'];
                                $one_cus['employee_id'] = $request->employee->id;
                                $one_cus['root_id'] = $root_id;
                            } elseif ($type == 2) {
                                if (isset($cate_list[$v['cate']])) {
                                    $one_cus['cate_id'] = $cate_list[$v['cate']];
                                } else {
                                    $new_id = SmartScreenProjectCate::insertGetId(['name'=> $v['cate'], 'root_id'=> $root_id, 'employee_id'=> $request->employee->id]);
                                    $cate_list[$v['cate']] = $new_id;
                                    $one_cus['cate_id'] = $new_id;
                                }
                                $one_cus['category'] = $v['category'];
                                $one_cus['name'] = $v['name'];
                                $one_cus['unit'] = $v['unit'];
                                $one_cus['price'] = $v['price'];
                                $one_cus['content'] = $v['content'];
                                $one_cus['employee_id'] = $request->employee->id;
                                $one_cus['root_id'] = $root_id;
                            }
                            $save_data[] = $one_cus;
                        }
            
                        Db::startTrans();
                        try {
                            foreach ($save_data as $k => $v) {
                                if ($type == 1) {
                                    $result = SmartScreenConstruction::create($v);
                                } elseif ($type == 2) {
                                    SmartScreenProject::create($v);
                                }
                            }
                            Db::commit();
                        } catch (\Exception $e) {
                            trace($e->getMessage(), 'error');
                            // 回滚事务
                            Db::rollback();
                        }
            
                        $data->status = 2;
                        $data->count = count($datas);
                        $data->valid_count = count($datas);
                        $data->execute_rows = $highestRow;
                        $data->tmp_path = '';
                        $data->save();
                    } else {
                        $data->tmp_path = '';
                        $data->status = 2;
                        $data->save();
                    }
                }
            } else {
                Log::record('-------screen content empty error------')->save();
                Log::record('content empty')->save();
                Log::record('-------screen content empty error------')->save();
            }
        } catch (\Exception $e) {
            Log::record('-------screen import error------')->save();
            Log::record('content error')->save();
            Log::record('-------screen import error------')->save();
        }
        die;
    }
}