mingde-bd/application/admin/controller/House.php

<?php
namespace app\admin\controller;

use app\common\model\HousePay;
use think\App;
use think\Db;
use think\Exception;

class House extends Auth
{

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

    }
    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('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $name = input('name','','trim');
            if($name){
                $map[] = ['name','like','%'.$name.'%'];
            }
            $xq_title = input('xq_title','','trim');
            if($xq_title){
                $map[] = ['xq_title','like','%'.$xq_title.'%'];
            }
            $card = input('card','','trim');
            if($card){
                $map[] = ['card','like','%'.$card.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $cate = input('cate','','trim');
            if($cate != ''){
                $map[] = ['cate','=',$cate];
            }
            $c_type = input('c_type','','trim');
            if($c_type != ''){
                $map[] = ['c_type','=',$c_type];
            }
            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]['cate_name'] =$this->model->cate[$v['cate']];
                $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{

            $cType = Db::name('house_c_type')
                ->where('del',0)
                ->where('enable',1)
                ->select();
            $this->assign('m_name','资源');
            $this->assign('cType',$cType);
            $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);
            return $this->fetch();
        }
    }

    public function showfee(){
        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('title','','trim');
            if($title){
                $map[] = ['title','like','%'.$title.'%'];
            }
            $name = input('name','','trim');
            if($name){
                $map[] = ['name','like','%'.$name.'%'];
            }
            $xq_title = input('xq_title','','trim');
            if($xq_title){
                $map[] = ['xq_title','like','%'.$xq_title.'%'];
            }
            $card = input('card','','trim');
            if($card){
                $map[] = ['card','like','%'.$card.'%'];
            }
            $enable = input('enable','','trim');
            if($enable != ''){
                $map[] = ['enable','=',$enable];
            }
            $cate = input('cate','','trim');
            if($cate != ''){
                $map[] = ['cate','=',$cate];
            }
            $c_type = input('c_type','','trim');
            if($c_type != ''){
                $map[] = ['c_type','=',$c_type];
            }

            $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]['cate_name'] =$this->model->cate[$v['cate']];
                $start =Db::name('house_pay_log')
                    ->alias('hpl')
                    ->join('house_pay hp','hp.id = hpl.pay_id')
                    ->where('hpl.bus_id',$v['id'])
                    ->where('hp.status',1)
                    ->where('hp.del',0)
                    ->field('hpl.start')
                    ->order('hpl.start','asc')
                    ->find();
                $start = $start?$start['start']:'';
                $end = Db::name('house_pay_log')
                    ->alias('hpl')
                    ->join('house_pay hp','hp.id = hpl.pay_id')
                    ->where('hpl.bus_id',$v['id'])
                    ->where('hp.status',1)
                    ->where('hp.del',0)
                    ->field('hpl.end')
                    ->order('hpl.end','asc')
                    ->find();
                $end = $end?$end['end']:'';
                $money = Db::name('house_pay_log')
                    ->alias('hpl')
                    ->join('house_pay hp','hp.id = hpl.pay_id')
                    ->where('hpl.bus_id',$v['id'])
                    ->where('hp.status',1)
                    ->where('hp.del',0)
                    ->sum('hpl.money');

                $lists[$k]['startend'] = '';
                if($start && $end){
                    $lists[$k]['startend'] = $start.' ~ '.$end;
                }
                $lists[$k]['money'] = 0;
                if($money){
                    $lists[$k]['money'] = $money;
                }
            }
            //数据返回
            $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{

            $cType = Db::name('house_c_type')
                ->where('del',0)
                ->where('enable',1)
                ->select();
            $this->assign('m_name','综合收费');
            $this->assign('cType',$cType);
            return $this->fetch();
        }
    }

    public function fee($id){
            $refer = isset($_SERVER['http_referer'])?$_SERVER['http_referer']:'';
            $lists = [];
            // 获取房产及房产下的储藏室和停车位
            $house = Db::name('house')->where(['id'=>$id,'enable'=>1,'del'=>0])->find();
            if(!$house){
                $this->error('资源不存在',$refer);
            }
            if($house['owner_id'] <= 0){
                $this->error('资源未绑定业主',$refer);
            }
            $fees  =  (new \app\common\model\HouseFee())->getTree(cur_org_id());;
            $fees = $fees?json_decode(json_encode($fees),true):[];

            $lists = $this->model->getlistbyowner($house['owner_id'],$house['area_id']);
            foreach ($lists as $k=>$v){
                $arrs = [];
                foreach ($fees as $kk=>$vv){
                    if($vv['type'] == $v['type']){
                        $children = $vv['child']?$vv['child']:[];
                        foreach ($children as $kkk=>$vvv){
                            $vvv['parent_title'] = $vv['title'];
                            $vvv['end'] = $vvv['start'] = '';
                            $info = Db::name('house_pay_log')
                                ->alias('pl')
                                ->join('house_pay p','p.id = pl.pay_id')
                                ->where('pl.bus_id',$v['id'])
                                ->where('pl.fee_id',$vvv['id'])
                                ->order('pl.end','desc')
                                ->field('pl.*')
                                ->find();
                            if($info){
                                $vvv['end'] = $info['end'];
                                $vvv['start'] = $info['start'];
                            }
                            $minfo =Db::name('house_pay_log')
                                ->alias('pl')
                                ->join('house_pay p','p.id = pl.pay_id')
                                ->where('pl.bus_id',$v['id'])
                                ->where('pl.fee_id',$vvv['id'])
                                ->sum('pl.money');
                            $vvv['tmoney'] = 0;
                            if($minfo){
                                $vvv['tmoney'] = $minfo;
                            }
                            $arrs[] = $vvv;
                        }
                    }
                }
                $lists[$k]['fee'] = $arrs;
            }

            $owner =Db::name('owner')
                ->where('id',$house['owner_id'])
                 ->find();

            $this->assign('lists',$lists);
            $this->assign('houseId',$id);
            $this->assign('owner',$owner);
            return $this->fetch();
    }

    //保存收费记录
    public function feeSave(){
        $houseId = input('houseId');
        $lastDate = input('lastDate');
        $order = input('order');
        if(!$houseId){
            $this->error('参数错误');
        }
        if(!$lastDate){
            $this->error('请选择最晚缴费日期');
        }
        if(!$order){
            $this->error('请选择收费科目');
        }
        $order = json_decode($order,true);
        if(empty($order)){
            $this->error('请选择收费科目');
        }
        foreach ($order as $k=>$v){
            if($v['total_price'] <= 0){
                $this->error('收费金额必须大于0');
            }
            $fee = Db::name('house_fee')->where('id',$v['fee_id'])->find();
            if(!$fee||$fee['del']==1||$fee['enable'] == 0){
                $this->error('收费科目不存在');
            }
            if($fee['cycle'] == 1){ // 按周期
                if(!$v['start']||!$v['end']){
                    $this->error('未选择收费周期');
                }
                $ret = Db::name('house_pay_log')
                    ->alias('hpl')
                    ->join('house_pay hp','hp.id = hpl.pay_id')
                    ->where('hp.del',0)
                    ->where('hp.status','<>',3)
                    ->where('hpl.bus_id',$v['id'])
                    ->where('hpl.fee_id',$v['fee_id'])
                    ->where('hpl.start','<=',$v['end'])
                    ->where('hpl.end','>=',$v['start'])
                    ->field('hpl.*')
                    ->find();
                if($ret){
                    $this->error('请勿重复收费');
                }
            }
        }

        $ret = (new HousePay())->createOrder($houseId,$lastDate,$order,$this->userId,cur_org_id());
        if(!$ret){
            $this->error((new HousePay())->getError());
        }else{
            $data['info']   =   '操作成功';
            $data['status'] =   1;
            $data['id']    =   $ret;
            $this->success('','',$data);
        }
    }
    /**
     * 新增/编辑
     */
    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();
                $info['c_type_list'] = $this->model->getCType($info['c_type']);
                $this->assign('info',$info);
            }
            $area = (new \app\common\model\HouseAreas())->getList();
            $owner = (new \app\common\model\Owner())->getList();
            $cate = $this->model->getCate();
            $this->assign('area',$area);
            $this->assign('owner',$owner);
            $this->assign('cate',$cate);
            return $this->fetch();
        }
    }

    /**
     * 删除记录
     * @param int $id
     */
    public function del($id=0){
        if(!$id){
            $this->error('参数错误');
        }
        $res = db('house')->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('house')->where('id',$id)->setField($fn,$fv);
        if($res){
            $this->success('操作成功');
        }else{
            $this->error('操作失败');
        }
    }
    public function getType(){
        $catId = input('id');
        $list = Db::name('house_c_type')
        ->where('cate',$catId)
        ->where('del',0)
        ->where('enable',1)
        ->select();
        $this->success('','',$list);
    }
    // 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','F','G','H','I','J');
        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,'类型');
        $sheet->setCellValueByColumnAndRow(5,1,'业主姓名');
        $sheet->setCellValueByColumnAndRow(6,1,'业主电话');
        $sheet->setCellValueByColumnAndRow(7,1,'业主身份证');
        $sheet->setCellValueByColumnAndRow(8,1,'业主预存款');
        $sheet->setCellValueByColumnAndRow(9,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';

        $userId = $this->userId;
        $orgId = cur_org_id();
        set_time_limit(0);
        ini_set("memory_limit", -1);
        ob_flush();//清空缓存
        flush();//刷新缓存
        $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'=>1,
            'create_time'=>getTime(),
        ];
        $importId =Db::name('import_log')->insertGetId($imData);
        $objPHPExcel = $objReader->load($filepath,$encode='utf-8');
        $table = array(0,1,2,3,4,5,6,7,8,9);
        $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[3]){
                        $msg = "第".$row."行，数据存在空值，未导入";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }
                    if(!in_array($oneData[3],$this->model->cate)){
                        $msg = "第".$row."行，资源属性错误，未导入";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }

                    $c = array_reverse($this->model->cate1);
                    if(in_array($c[$oneData[3]],[1,2,3,4])){
                        if(!is_numeric($oneData[2]) ||  !$oneData[2]){
                            $msg = "第".$row."行，面积字段数据格式错误(只能为纯数字)，未导入";
                            echo "<font color=\"red\">".$msg."</font><br />";
                            $error++;
                            $errorMsg[] = $msg;
                            continue;
                        }
                    }else{
                        $oneData[2] = '0.00';
                    }

                    if(in_array($c[$oneData[3]],[1,2,3])){
                        $type = 1;
                    }elseif ($c[$oneData[3]]==4){
                        $type = 2;
                    }elseif ($c[$oneData[3]]==5){
                        $type = 3;
                    }
                    $pid = $this->getPH($oneData[0]);
                    $ret = Db::name('house')
                        ->where('org_id',$orgId)
                        ->where('title',$oneData[1])
                        ->where('area_id',$pid)
                        ->where('del',0)
                        ->find();
                    if($ret){
                        $msg = "第".$row."行，数据已存在，未导入";
                        echo "<font color=\"red\">".$msg."</font><br />";
                        $error++;
                        $errorMsg[] = $msg;
                        continue;
                    }

                    if(!$oneData[5] || !$oneData[6] ||!$oneData[7]){
                        $owId = 0;
                    }else{
                        $oType = 1;
                        if($oneData[9]){
                            if(in_array($oneData[9],['住户','租户'])){
                                $oType = $oneData[9]=='住户'?1:2;

                            }
                        }
                        $oMoney  = '0.00';
                        if($oneData[8]){
                            if(is_numeric($oneData[8])){
                                $oMoney  = $oneData[8];
                            }
                        }
                        $owner = Db::name('owner')
                            ->where('card',$oneData[7])
                            ->where('del',0)
                            ->where('org_id',$orgId)
                            ->find();
                        if ($owner){
                            $owId = $owner['id'];
                        }else{
                            $oData = [
                                'org_id'=>$orgId,
                                'name'=>$oneData[5],
                                'phone'=>$oneData[6],
                                'card'=>$oneData[7],
                                'money'=>$oMoney,
                                'type'=>$oType,
                                'import_log_id'=>$importId,
                                'create_time'=>getTime(),
                            ];
                            $owId = Db::name('owner')->insertGetId($oData);
                            if(!$owId){
                                $msg = "第".$row."行，导入业主失败";
                                echo "<font color=\"red\">".$msg."</font><br />";
                                $error++;
                                $errorMsg[] = $msg;
                                continue;
                            }

                        }


                    }
                    $iii = 0;
                    if($oneData[4]){
                        $tId = Db::name('house_c_type')
                            ->where('name',$oneData[4])
                            ->where('org_id',$orgId)
                            ->where('cate',$c[$oneData[3]])
                            ->find();
                        if($tId){
                            $iii = $tId['id'];
                        }
                    }
                    $data = [
                        'org_id'=>$orgId,
                        'area_id'=>$pid,
                        'owner_id'=>$owId,
                        'type'=>$type,
                        'cate'=>$c[$oneData[3]],
                        'title'=>$oneData[1],
                        'area'=>$oneData[2],
                        'c_type'=>$iii,
                        'create_time'=>getTime(),
                        'enable'=>1,
                        'import_log_id'=>$importId,
                    ];
                    $Id = Db::name('house')->insertGetId($data);
                    if($owId>0){
                        $this->model->addLog($Id);
                    }
                    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 />";
        }
    }
    public function getPH($name){
        $orgId = cur_org_id();
        $res = Db::name('house_areas')
            ->where('org_id',$orgId)
            ->where('title',$name)
            ->where('pid',0)
            ->where('type',1)
            ->find();

        if(empty($res)){
            $data  = [
                'org_id'=>$orgId,
                'title'=>$name,
                'type'=>1,
            ];
            $id = Db::name('house_areas')->insertGetId($data);
        }else{
            $id = $res['id'];
        }
        return $id;
    }
}