jili2/application/admin/controller/Owner.php

<?php
namespace app\admin\controller;

use think\App;
use think\Db;
use think\Exception;

class Owner extends Auth
{

    public function __construct(App $app = null) {
        parent::__construct($app);
        $this->model= new \app\common\model\Owner();
        $this->table= $this->model->table;

    }
    public function index($import_id=0){
        if(request()->isAjax()){
            //分页参数
            $length = input('rows',10,'intval');   //每页条数
            $page = input('page',1,'intval');      //第几页
            $start = ($page - 1) * $length;     //分页开始位置
            //排序
            $sortRow = input('sidx','id','trim');      //排序列
            $sort = input('sord','desc','trim');        //排序方式
            $order = $sortRow.' '.$sort;

            $title = input('name','','trim');
            if($title){
                $map[] = ['name','like','%'.$title.'%'];
            }
            $title1 = input('phone','','trim');
            if($title1){
                $map[] = ['phone','like','%'.$title1.'%'];
            }
            $title2 = input('card','','trim');
            if($title2){
                $map[] = ['card','like','%'.$title2.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            if($import_id >0){
                $map[] = ['import_log_id','=',$import_id];
            }else{
                $map[] = ['del','=',0];
            }
            $map[] = ['org_id','=',$this->orgId];
            $map= empty($map) ? true: $map;
            //数据查询
            $lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
            foreach ($lists as $k=>$v){
                $lists[$k]['is_show_option'] =$import_id>0?false:true;
            }
            //数据返回
            $totalCount = db($this->table)->where($map)->count();
            $totalPage = ceil($totalCount/$length);
            $result['page'] = $page;
            $result['total'] = $totalPage;
            $result['records'] = $totalCount;
            $result['rows'] = $lists;
            return json($result);
        }else{
            $logInfo = [];
            if($import_id>0){
                $logInfo = Db::name('import_log')
                    ->where('id',$import_id)
                    ->find();
            }
            $this->assign('import_id',$import_id);
            $this->assign('importInfo',$logInfo);
            $this->assign('m_name','业主');
            return $this->fetch();
        }
    }

    /**
     * 新增/编辑
     */
    public function add($id=0){
        if(request()->isPost()){
            $res = $this->model->updates();
            if($res){
                $this->success('操作成功',url('index'));
            }else{
                $this->error($this->model->getError());
            }
        }else{
            if($id){
                $info =db($this->table)->where('id',$id)->find();
                $this->assign('info',$info);
            }
            return $this->fetch();
        }
    }

    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField('del',1);
        if($res){
            $this->success('删除成功');
        }else{
            $this->error('删除失败');
        }
    }

    /**
     * 改变字段值
     * @param int $fv
     * @param string $fn
     * @param int $fv
     */
    public function changeField($id=0,$fn='',$fv=0){
        if(!$fn||!$id){
            $this->error('参数错误');
        }
        $res = db($this->table)->where('id',$id)->setField($fn,$fv);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }

    // excel
    public function import(){

        return $this->fetch();
    }

    /**
     * 下载模板
     */
    public function downloadtem(){
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit","512M");
        $fileName = '业主列表.xlsx';

        $excel = new \PHPExcel();
        $sheet = $excel->setActiveSheetIndex(0);
        $arr = array('A','B','C','D','E');
        foreach($arr as $k=>$v){
            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(true);//换行
            $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
        }
        $sheet->setCellValueByColumnAndRow(0,1,'姓名');
        $sheet->setCellValueByColumnAndRow(1,1,'联系电话');
        $sheet->setCellValueByColumnAndRow(2,1,'身份证号');
        $sheet->setCellValueByColumnAndRow(3,1,'预付款');
        $sheet->setCellValueByColumnAndRow(4,1,'类型(业主/租户)');

        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output'); //文件通过浏览器下载

    }

    /**
     * 导入
     */
    public function importexcel(){
        include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
        set_time_limit(0);
        ini_set("memory_limit", -1);
        ob_flush();//清空缓存
        flush();//刷新缓存

        $orgid = cur_org_id();
        $file = request()->file('file');

        if(!$file){
            exit('请上传文件');
        }
        // 移动到框架应用根目录/uploads/ 目录下
        $info = $file->validate([ 'size'=>config('app.max_upload_file_size') ])
            ->move(env('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads'. DIRECTORY_SEPARATOR . 'files');
        if(!$info){
            exit('上传文件失败');

        }
        $img = env('root_path').'/public/uploads/files/' . $info->getSaveName();
        $finfo = $info->getInfo();
        $fileExt = explode('.',$finfo['name']);
        $finfo['file_name'] = $finfo['name'];
        $filepath = str_replace('\\', '/', $img);
        $file_type = $fileExt[count($fileExt)-1];

        if($file_type == 'xls'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }else{
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }

        $imData = [
            'org_id'=>$orgid,
            'title'=>$finfo['file_name'],
            'type'=>2,
            'create_time'=>getTime(),
        ];
        $importId =Db::name('import_log')->insertGetId($imData);
        $objPHPExcel = $objReader->load($filepath,$encode='utf-8');
        $table = array(0,1,2,3,4);
        $allobjWorksheets = $objPHPExcel->getAllSheets();
        try{
            @unlink($filepath);
            foreach($allobjWorksheets as $key=>$objWorksheet){
                $highestRow = $objWorksheet->getHighestRow();
                $success = 0;
                $error = 0;
                $errorMsg = [];
                for ($row = 2; $row <= $highestRow; ++$row) {
                    $oneData = array();
                    for ($col = 0; $col < count($table); ++$col) {
                        $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
                        $value = (string)$cell->getValue();
                        $oneData[$table[$col]]= $value;
                    }
                    if(!$oneData[0]||!$oneData[1] ||!$oneData[2]){
                        $msg = "第".$row."行，数据存在空值，未导入";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }
                    if($oneData[4]){
                        if(!in_array($oneData[4],['业主','租户'])){
                            $msg = "第".$row."行，类型错误，未导入";
                            echo "<font color=\"red\">".$msg."</font><br />";
                            $error++;
                            $errorMsg[] = $msg;
                            continue;
                        }
                        $type = $oneData[4]=='业主'?1:2;
                    }else{
                        $type = 1;
                    }
                    $check =  Db::name('owner')
                        ->where('card',$oneData[2])
                        ->where('del',0)
                        ->where('org_id',$orgid)
                        ->find();
                    if($check){
                        $msg = "第".$row."行，姓名<".$oneData[0].">已存在，未导入";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }
                    if($oneData[3]){
                        if(!is_numeric($oneData[3])){
                            $msg = "第".$row."行，预存款字段数据格式错误(只能为纯数字)，未导入";
                            echo "<font color=\"red\">".$msg."</font><br />";
                            $error++;
                            $errorMsg[] = $msg;
                            continue;
                        }
                        $money  = $oneData[3];
                    }else{
                        $money  = '0.00';
                    }

                    $data = [
                        'org_id'=>$orgid,
                        'name'=>$oneData[0],
                        'phone'=>$oneData[1],
                        'card'=>$oneData[2],
                        'money'=>$money,
                        'type'=>$type,
                        'create_time'=>getTime(),
                        'import_log_id'=>$importId
                    ];
                    $Id = Db::name('owner')->insertGetId($data);
                    if(!$Id){
                        $msg = "第".$row."行，导入失败";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }
                    $success++;
                }
                Db::name('import_log')->where('id',$importId)
                    ->update(
                        [
                            'success'=>$success,
                            'error'=>$error,
                            'error_msg'=>json_encode($errorMsg),
                        ]
                    );
            }
            echo "<font color=\"green\">导入完成</font><br />";
        }catch (Exception $e){
            echo $e->getMessage();
            echo "<font color=\"red\">数据异常，已停止导入</font><br />";
        }
    }
}