<?php
namespace app\common\controller;

use app\BaseController;
use think\facade\Db;
use think\facade\Request;


class Common extends BaseController
{

    //获取省市区
    public  function getArea() {
        $where['area_pid'] = Request::param('area_id',0);
        $data = Db::name('area')->where($where)->field("area_id,area_name")->select()->toArray();
        return $this->returnJson($data,'success');
    }

    //通过id获取地区名称
    public  function getName($id) {
        $where['area_id'] = $id;
        return Db::name('area')->where($where)->value('area_name');
    }

    //查看所有的银行列表
    public function bankAccountList() {

        $data = Db::name("bank_account")->select()->toArray();

        return $this->returnJson($data,'success');

    }

    //对数组中的时间字段进行日期转换
    public static function changeField($data,$field="create_time",$format="Y-m-d H:i:s") {
        $prompt = "暂无";
        if(empty($data)) {
            return $data;
        }
        foreach($data as $k =>$v) {
            if(is_array($v)) {
                if(is_array($field)) {
                    foreach ($field as $value) {
                        if($v[$value] != 0 || !is_null($v[$value])){
                            $data[$k][$value] = date($format,$v[$value]);
                        } else {
                            $data[$k][$value] = $prompt;
                        }
                    }
                } else {
                    if($v[$field] != 0 || !is_null($v[$field])){
                        $data[$k][$field] = date($format,$v[$field]);
                    } else {
                        $data[$k][$field] = $prompt;
                    }
                }
            } else {
                if(is_array($field)) {
                    foreach ($field as $value) {
                        if(isset($data[$value]) || $data[$value] !=0){
                            $data[$value] = date($format,$data[$value]);
                        } else{
                            $data[$value] = $prompt;
                        }
                    }
                    break;
                } else {
                    if(isset($data[$field]) || $data[$field] !=0) {
                        $data[$field] = date($format, $data[$field]);
                    } else {
                        $data[$field] = $prompt;
                    }
                    break;
                }
            }
        }

        return $data;
    }

    /**
     * @param $url_ids string 1,2,3
     * @param $navAl array
     * @param $type int 1 所有都赋值,0 给特定的赋值
     */
    public  static function changeNav($url_ids,$navAl,$type=1) {

        $urlsArr = explode(",",$url_ids);
        if($type==1 ) {
            return $navAl;
        } else {
            foreach($navAl as $k =>$v ) {
                $navAl[$k]['is_power'] =0 ;
                if(isset($v['property_nav_id']) && !in_array($v['property_nav_id'],$urlsArr)) {
                    unset($navAl[$k]) ;
                }
                if(isset($v['community_nav_id']) && !in_array($v['community_nav_id'],$urlsArr)) {
                    unset($navAl[$k]);
                }
                if(isset($v['admin_nav_id']) && !in_array($v['admin_nav_id'],$urlsArr)) {
                    unset($navAl[$k]);
                }
            }
        }
        return $navAl;
    }

    //获取流水详情,village_money_list表
    public static function getWithDrawDetail($where) {
        $data = Db::name('village_money_list')->alias('ml')
            ->leftJoin('house_village hv','ml.village_id = hv.village_id')
            ->leftJoin('house_property hp','hp.property_id = ml.property_id')
            ->where($where)
            ->field("ml.*,hv.village_name,hp.property_name")
            ->find();

        if($data) {
            $where_one['order_id'] = $data['order_id'];
            switch ($data['table_name']) {
                case "pay_order" :

                    $orderInfo = Db::name('pay_order')->alias('po')
                        ->leftJoin('house_vacancy hv', 'hv.vacancy_id = po.vacancy_id')
                        ->leftJoin('user u', 'po.uid = u.uid')
                        ->field('po.*,hv.vacancy_address,u.phone,u.nickname')
                        ->where($where_one)
                        ->find();
                    break;
                case "withdraw_order":
                    $orderInfo = Db::name('withdraw_order')
                        ->where($where_one)->find();
                    break;
                default:
                    return false;
            }

            return ['data' => $data, 'orderInfo' => $orderInfo];
        } else {
            return false;
        }
    }


    //获取流水列表
    public static function getWithDrawList($where,$page) {
        $count = Db::name('village_money_list')->alias('ml')
            ->leftJoin('house_village hv','ml.village_id = hv.village_id')
            ->leftJoin('house_property hp','hp.property_id = ml.property_id')
            ->where($where)->count();

        $data = Db::name('village_money_list')->alias('ml')
            ->leftJoin('house_village hv','ml.village_id = hv.village_id')
            ->leftJoin('house_property hp','hp.property_id = ml.property_id')
            ->where($where)
            ->field("ml.id,ml.type,ml.total_money,ml.income,ml.create_time,ml.now_village_money,ml.now_property_money,hv.village_name,hp.property_name")
            ->order('ml.create_time','desc')
            ->page($page,config('app.limit'))->select()->toArray();

        return ['total'=>$count,'data'=>$data];
    }

    //房间导入
    public static function importVacancy($filePath,$village_id) {

        $where['village_id'] = $village_id;
        //设置获取excel对象
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因为office版本可以向下兼容
        $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//$file 为解读的excel文件
        $sheet = $objPHPExcel->getSheet(0);
        $dataAll = $objPHPExcel->getSheet(0)->toArray(); //获取导入的数据;
        $highestRow = $sheet->getHighestRow(); // 取得总行数

        //获取社区布局
        $layout_list = Db::name('layout_list')->where($where)->column('layout_id','code');

        $park_block_list = Db::name('park_block')->where($where)->column('park_block_id','name');
        //获取社区不同布局的所有的建筑id
        $errorData =[]; //存放导入失败的数据;
        for($j=1;$j<$highestRow;$j++)
        {
            if(!empty($dataAll[$j][0]) && !empty($dataAll[$j][4]) &&!empty($dataAll[$j][7]) &&!empty($dataAll[$j][8])){
                $msg = "";
                $vacancyCode =   $dataAll[$j][0]; //房间编号
                $layout_list_code =   $dataAll[$j][7];//布局
                if($layout_list_code) {
                    $layout_id = $layout_list[$layout_list_code];
                    $data['layout_id'] = $layout_id;
                    $res_vacancy_code = self::getVacancyCode($vacancyCode,$layout_id);
                    if($res_vacancy_code['code'] !=200) {
                        $msg .= "房间编号不正确!";
                    }
                    $vacancy_code = $res_vacancy_code['msg'];
                    $data['vacancy_code'] = $vacancy_code;
                    $parent_id = $res_vacancy_code['parent_id'];
                    $data['parent_id'] = $parent_id;
                } else {
                    $msg .="社区布局未选择!";

                }
                $name = $dataAll[$j][1];//业主名字
                $id_card = $dataAll[$j][2];//布局;//身份证id

                if($id_card && !isCreditNo($id_card)) {
                    $msg .= "身份证id不符合!";
                }
                $phone = $dataAll[$j][3];//布局;//手机号
                if($phone && !isPhoneNo($phone)) {
                    $msg .="手机号不符合!";
                }
                $area = $dataAll[$j][4];//面积
                $car_block_name = $dataAll[$j][5];//车库位置
                $park_code = $dataAll[$j][6];//车位编号
                if($car_block_name && $park_code) {
                    $park_block_id = $park_block_list[$car_block_name];
                    $park_car_id = Db::name('park_car')->where(['village_id'=>$village_id,'park_block_id'=>$park_block_id,'park_code'=>$park_code])->value('park_car_id');
                    $data['park_car_id'] = $park_car_id;
                    $data['park_block_id'] = $park_block_id;
                }

                $house_type = $dataAll[$j][8];//住宅类型
                switch ($house_type) {
                    case "住宅" :
                        $type =1;
                        break;
                    case "商铺" :
                        $type = 2;
                        break;
                    case "办公" :
                        $type = 3;
                        break;
                    default :
                        $msg.= "住宅类型未选择!";
                }


                $data['name'] = $bind['name'] = $name;
                $data['id_card'] = $bind['id_card']= $id_card;
                $data['phone'] = $bind['phone'] = $phone;
                $data['area'] = $area;
                $data['house_type'] = $type;
                $data['create_time'] = $bind['create_time'] = $bind['pass_time']= time();
                $data['village_id'] = $bind['village_id'] = $village_id;

                if(empty($msg)) { //表明没有错误
                    $bind['vacancy_id'] = Db::name('house_vacancy')->insertGetId($data);
                    //导入到userbind表中
                    $bind['type'] = 0;
                    $bind['status']  = 1;
                    if($bind['name'] && $bind['id_card'] && $bind['phone']){
                        Db::name('house_user_bind')->save($bind);
                    }
                } else {
                    $dataAll[$j][] = $msg;
                    $errorData[] =  $dataAll[$j];
                }
            }else {
                $dataAll[$j][] = "数据不能为空!";
                $errorData[] =  $dataAll[$j];
            }
        }

        if(empty($errorData)) {
            return ['code'=>200,'data'=>[]];
        }else {
            //导出错误的数据
            $file = "./formwork/importVacancy.xls";
            $phpExcel = \PHPExcel_IOFactory::load($file);
            $title = ['A','B','C','D','E','F','G','H','I','J'];
            $i = 2;
            $optionsArr = Db::name('layout_list')->where(['village_id'=>$village_id])->column('code');
            $optionsString = implode(',',$optionsArr);//这个是选择布局
            $car_blockArr = Db::name('park_block')->where(['village_id'=>$village_id])->column('name');
            $optionsCarBlockString = implode(',',$car_blockArr);//这个是车库布局
            foreach ($errorData as $k =>$v) {
                foreach ($title as $key=>$value) {
                    $phpExcel->getActiveSheet()->setCellValue($value.$i,$v[$key]);
                }
                //设置单元楼下拉
                $phpExcel->getActiveSheet()->getCell('H'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    -> setAllowBlank(false)
                    -> setShowInputMessage(true)
                    -> setShowErrorMessage(true)
                    -> setShowDropDown(true)
                    -> setErrorTitle('输入的值有误')
                    -> setError('您输入的值不在下拉框列表内.')
                    -> setPromptTitle('社区布局')
                    -> setFormula1('"'.$optionsString.'"');; //这一句为要设置数据有效性的单元格
                $phpExcel->getActiveSheet()->getCell('I'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    -> setAllowBlank(false)
                    -> setShowInputMessage(true)
                    -> setShowErrorMessage(true)
                    -> setShowDropDown(true)
                    -> setErrorTitle('输入的值有误')
                    -> setError('您输入的值不在下拉框列表内.')
                    -> setPromptTitle('房屋类型')
                    -> setFormula1('"住宅,商铺,办公"');; //这一句为要设置数据有效性的单元格
                if($optionsCarBlockString) {
                    $phpExcel->getActiveSheet()->getCell('F'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                        -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        -> setAllowBlank(false)
                        -> setShowInputMessage(true)
                        -> setShowErrorMessage(true)
                        -> setShowDropDown(true)
                        -> setErrorTitle('输入的值有误')
                        -> setError('您输入的值不在下拉框列表内.')
                        -> setPromptTitle('车库位置')
                        -> setFormula1('"'.$optionsCarBlockString.'"');; //这一句为要设置数据有效性的单元格
                }
                $i++;
            }
            $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="房间导入失败数据.xls"');
            header('Cache-Control: max-age=0');
            $objWriter->save("php://output");

        }
    }


    //通过房间编号获取房间的父id
    public static function getParentId($layout_id,$vacancyCode) {
        $arr = explode('-',$vacancyCode);
        $level= 0;
        $parent_id = 0;
        foreach($arr as $k => $v) {
            $level++;
            $layout_build_id = Db::name('layout_build')->where(['layout_id'=>$layout_id,'code'=>$v,'level'=>$level,'parent_id'=>$parent_id])->value('layout_build_id');
            if($layout_build_id) {
                $parent_id = $layout_build_id;
            }
        }
        return $parent_id;
    }

    //通过填写的用户的房间号来转化成真正的房间编号
    public static function getVacancyCode($vacancyCode,$layout_id) {
        $vacancyCodeArr = explode('-',$vacancyCode);
        $roomCode = array_pop($vacancyCodeArr);//去掉最后一个是房间的
        $vacancy_code = '';
        $parent_id = 0;
        $level = 1;
        foreach ($vacancyCodeArr as $k => $v) {
            $parent_id = Db::name('layout_build')->where(['layout_id'=>$layout_id,'parent_id'=>$parent_id,'code'=>$v,'level'=>$level])->value('layout_build_id');
            if(!$parent_id){
                return ['code'=>400,'msg'=>'房间编号不存在!'];
            }
            $vacancy_code = $vacancy_code.$parent_id."-";
            $level++;
        }

        $arrCode = explode('-',rtrim($vacancy_code,'-'));
        return ['code'=>200,'msg'=>$vacancy_code.$roomCode,'parent_id'=>array_pop($arrCode)];

    }


    //用户导入
    public static function importTenant($filePath,$village_id) {
        $where['village_id'] = $village_id;
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因为office版本可以向下兼容
        $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//$file 为解读的excel文件
        $sheet = $objPHPExcel->getSheet(0);
        $dataAll = $objPHPExcel->getSheet(0)->toArray(); //获取导入的数据;
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $errorData =[]; //存放导入失败的数据;
        $insertData = [];
        $layout_list = Db::name('layout_list')->where($where)->column('layout_id','code');
        for($i=1;$i<$highestRow;$i++) {
            $msg="";
            $name = $dataAll[$i][0];
            if(is_null($name)){
                continue;
            }
            $phone = $dataAll[$i][1];
            if(!isPhoneNo($phone)){
                $msg.="手机号不正确!";
            }
            $id_card = $dataAll[$i][2];
            if(!isCreditNo($id_card)){
                $msg.="身份证号码不正确!";
            }
            $layout_list_code = $dataAll[$i][5];
            if($layout_list_code) {
                $layout_id = $layout_list[$layout_list_code];
            } else {
                $msg.="社区布局未选择!";
            }
            $vacancyCode = $dataAll[$i][3];
            //验证房间编号是否存在,是否合理
            $res_vacancy_code = self::getVacancyCode($vacancyCode,$layout_id);
            if($res_vacancy_code['code'] !=200) {
                $msg.= "房间编号不正确!";
            }
            $where['vacancy_code'] = $res_vacancy_code['msg'];
            $vacancy_id = Db::name('house_vacancy')->where($where)->value('vacancy_id');
            $userType = $dataAll[$i][4];
            switch($userType){
                case "租客":
                    $type=2;
                    break;
                case "业主":
                    $type=0;
                    break;
                case "家属":
                    $type =1;
                    break;
                default :
                    $msg.="未选择用户身份!";
            }
            //如果type=0,需要检测是否房间绑定了业主
            if($type==0){
                $is_exit = Db::name('house_user_bind')->where(['village_id'=>$village_id,'vacancy_id'=>$vacancy_id,'type'=>0,'status'=>1])->value('house_user_bind_id');
                if($is_exit){
                    $msg.='该房间已绑定业主,请在解绑!';
                }
            }
            $data['vacancy_id'] = $vacancy_id;
            $data['village_id'] = $village_id;
            $data['id_card'] = $id_card;
            $data['name'] = $name;
            $data['phone'] = $phone;
            $data['type'] = $type;
            $data['relatives_type'] =1; //默认
            $data['create_time'] = $data['pass_time'] = time();
            $data['status'] =1;
            if(empty($msg)){
                $insertData[] = $data;
            } else {
                $dataAll[$i][] = $msg;
                $errorData[] =  $dataAll[$i];
            }
        }
        //导入对的数据
        if(!empty($insertData)){
            Db::name('house_user_bind')->insertAll($insertData);
        }
        //如果有错误的数据,导出
        if($errorData){
            //导出错误的数据
            $file = "./formwork/importTenant.xls";
            $phpExcel = \PHPExcel_IOFactory::load($file);
            $title = ['A','B','C','D','E','F','G'];
            $i = 2;
            $optionsArr = Db::name('layout_list')->where(['village_id'=>$village_id])->column('code');
            $optionsString = implode(',',$optionsArr);//这个是选择布局
            foreach ($errorData as $k =>$v) {
                foreach ($title as $key=>$value) {
                    $phpExcel->getActiveSheet()->setCellValue($value.$i,$v[$key]);
                }
                //设置单元楼下拉
                $phpExcel->getActiveSheet()->getCell('F'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    -> setAllowBlank(false)
                    -> setShowInputMessage(true)
                    -> setShowErrorMessage(true)
                    -> setShowDropDown(true)
                    -> setErrorTitle('输入的值有误')
                    -> setError('您输入的值不在下拉框列表内.')
                    -> setPromptTitle('社区布局')
                    -> setFormula1('"'.$optionsString.'"');; //这一句为要设置数据有效性的单元格
                $phpExcel->getActiveSheet()->getCell('E'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    -> setAllowBlank(false)
                    -> setShowInputMessage(true)
                    -> setShowErrorMessage(true)
                    -> setShowDropDown(true)
                    -> setErrorTitle('输入的值有误')
                    -> setError('您输入的值不在下拉框列表内.')
                    -> setPromptTitle('房屋类型')
                    -> setFormula1('"租客,业主,家属"'); //这一句为要设置数据有效性的单元格

                $i++;
            }
            $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="用户导入失败数据.xls"');
            header('Cache-Control: max-age=0');
            $objWriter->save("php://output");
        } else {
            return ['code'=>200,'data'=>[]];
        }
    }

    //车辆导入
    public static function importCar($filePath,$village_id) {
        $where['village_id'] = $village_id;
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因为office版本可以向下兼容
        $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//$file 为解读的excel文件
        $sheet = $objPHPExcel->getSheet(0);
        $dataAll = $objPHPExcel->getSheet(0)->toArray(); //获取导入的数据;
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $optionsArr = Db::name('park_block')->where(['village_id'=>$village_id])->column('park_block_id','name');
        $errorData =[]; //存放导入失败的数据;
        $insertData = [];

        for($i=1;$i<$highestRow;$i++) {
            $msg="";
            $name = $dataAll[$i][0];
            $phone = $dataAll[$i][1];
            if(!isPhoneNo($phone)){
                $msg.="用户手机号不正确!";
            }
            $license_plate = $dataAll[$i][2];
            $car_color = $dataAll[$i][3];
            $brand = $dataAll[$i][4];
            $car_type = $dataAll[$i][5];
            $data['name'] = $name;
            $data['phone'] = $phone;
            $data['license_plate'] = $license_plate;
            $data['car_color'] = $car_color;
            $data['create_time'] = time();
            $data['village_id'] = $village_id;
            $data['brand'] = $brand;
            $data['car_type'] = $car_type;
            if(empty($msg)){
                $insertData[] = $data;
            } else {
                $dataAll[$i][] = $msg;
                $errorData[] =  $dataAll[$i];
            }
        }

        if(!empty($insertData)){
            Db::name('park_car')->insertAll($insertData);
            if($errorData) {
                //导出错误的数据
                $file = "./formwork/importCar.xls";
                $phpExcel = \PHPExcel_IOFactory::load($file);
                $title = ['A','B','C','D','E','F','G'];
                $i = 2;
                $optionsArr = Db::name('park_block')->where(['village_id'=>$village_id])->column('name');
                $optionsString = implode(',',$optionsArr);//这个是的车库
                foreach ($errorData as $k =>$v) {
                    foreach ($title as $key=>$value) {
                        $phpExcel->getActiveSheet()->setCellValue($value.$i,$v[$key]);
                    }
                    //设置单元楼下拉
                    $phpExcel->getActiveSheet()->getCell('F'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                        -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        -> setAllowBlank(false)
                        -> setShowInputMessage(true)
                        -> setShowErrorMessage(true)
                        -> setShowDropDown(true)
                        -> setErrorTitle('输入的值有误')
                        -> setError('您输入的值不在下拉框列表内.')
                        -> setPromptTitle('车库')
                        -> setFormula1('"'.$optionsString.'"');; //这一句为要设置数据有效性的单元格
                    $i++;
                }
                $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="用户导入失败数据.xls"');
                header('Cache-Control: max-age=0');
                $objWriter->save("php://output");
            } else {
                return ['code'=>200,'data'=>[]];
            }
        } else {
            return ['code'=>200,'data'=>[]];
        }

    }

    //车位导入
    public static function importCarPark($filePath,$village_id) {
        $where['village_id'] = $village_id;
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因为office版本可以向下兼容
        $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//$file 为解读的excel文件
        $sheet = $objPHPExcel->getSheet(0);
        $dataAll = $objPHPExcel->getSheet(0)->toArray(); //获取导入的数据;
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $optionsArr = Db::name('park_block')->where(['village_id'=>$village_id])->column('park_block_id','name');
        $errorData =[]; //存放导入失败的数据;
        $insertData = [];//存放最后需要插入到数据库的数据

        for($i=1;$i<$highestRow;$i++) {
            $msg="";
            $park_code = $dataAll[$i][0];
            $park_block_name = $dataAll[$i][1];
            $type_name = $dataAll[$i][2];
            if($type_name == "非机动车位"){
                $data['type'] =0;
            }
            if($park_block_name) {
                $park_block_id = $optionsArr[$park_block_name];
                $data['park_block_id'] = $park_block_id;
            } else{
                $msg.="车库必选选!";
            }

            $data['park_code'] = $park_code;

            //查询当前的车位是否已存在
            $is_exit = Db::name('park_car')->where(['village_id'=>$village_id,'park_block_id'=>$park_block_id,'park_code'=>$park_code])->value('park_car_id');
            if($is_exit) {
                $msg.="该车位已存在!";
            }
            $data['create_time'] = time();
            $data['village_id'] = $village_id;

            if(empty($msg)){
                $insertData[] = $data;
            } else {
                $dataAll[$i][] = $msg;
                $errorData[] =  $dataAll[$i];
            }
        }

        if(!empty($insertData)) {

            Db::name('park_car')->insertAll($insertData);
            if($errorData){

                //导出错误的数据
                $file = "./formwork/importCarPark.xls";
                $phpExcel = \PHPExcel_IOFactory::load($file);
                $title = ['A','B','C','D'];
                $i = 2;
                $optionsArr = Db::name('park_block')->where(['village_id'=>$village_id])->column('name');
                $optionsString = implode(',',$optionsArr);//这个是的车库
                foreach ($errorData as $k =>$v) {
                    foreach ($title as $key=>$value) {
                        $phpExcel->getActiveSheet()->setCellValue($value.$i,$v[$key]);
                    }
                    //设置单元楼下拉
                    $phpExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation()-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                        -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        -> setAllowBlank(false)
                        -> setShowInputMessage(true)
                        -> setShowErrorMessage(true)
                        -> setShowDropDown(true)
                        -> setErrorTitle('输入的值有误')
                        -> setError('您输入的值不在下拉框列表内.')
                        -> setPromptTitle('车库')
                        -> setFormula1('"'.$optionsString.'"');; //这一句为要设置数据有效性的单元格
                    $i++;
                }
                $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="用户导入失败数据.xls"');
                header('Cache-Control: max-age=0');
                $objWriter->save("php://output");
            } else{
                return ['code'=>200,'data'=>[]];
            }

        } else {
            return ['code'=>400,'msg'=>'文件内容为空!'];
        }
    }

    //每月水费导入
    public static function importCost($filePath,$village_id,$type){

        $where['village_id'] = $village_id;
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');//配置成2003版本，因为office版本可以向下兼容
        $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//$file 为解读的excel文件
        $sheet = $objPHPExcel->getSheet(0);
        $dataAll = $objPHPExcel->getSheet(0)->toArray(); //获取导入的数据;
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $errorData =[]; //存放导入失败的数据;
        $insertData = [];//存放最后需要插入到数据库的数据

        for($i=1;$i<$highestRow;$i++) {
            $msg="";
            $vacancy_code = $dataAll[$i][0];
            //判断房间号是否存在
            $vacancyInfo = Db::name('house_vacancy')->alias('hv')
                    ->leftJoin('layout_build lb','lb.layout_build_id = hv.parent_id')
                    ->where(['hv.village_id'=>$village_id,'hv.vacancy_code'=>$vacancy_code])
                    ->field('hv.vacancy_id,lb.water_price,lb.electric_price,lb.gas_price,hv.water_fee,hv.electric_fee,hv.gas_fee,hv.is_inherit')->find();
            if($vacancyInfo) {
                $data['vacancy_id'] = $vacancyInfo['vacancy_id'];
            } else {
                $msg.="物业编号不存在!";
            }

            switch ($type){
                case "water":
                    if($vacancyInfo['is_inherit'] ==1) {
                        $price = $vacancyInfo['water_price'];
                    } else {
                        $price = $vacancyInfo['water_fee'];
                    }
                    $outFileName = "水费导入失败.xls";
                    break;
                case "electric":
                    if($vacancyInfo['is_inherit'] ==1) {
                        $price = $vacancyInfo['electric_price'];
                    } else {
                        $price = $vacancyInfo['electric_fee'];
                    }
                    $outFileName = "电费导入失败.xls";
                    break;
                case "gas":
                    if($vacancyInfo['is_inherit'] ==1) {
                        $price = $vacancyInfo['gas_price'];
                    } else {
                        $price = $vacancyInfo['gas_fee'];
                    }
                    $outFileName = "燃气费导入失败.xls";
                    break;
                default :
                    $outFileName = "未知错误.xls";
                    return ['code'=>400,'msg'=>'小区费用未设置,请先设置费用!'];
            }

            //查看月份的费用是否存在
            $data['cost_month']  = $dataAll[$i][1];
            //判断月份是否超过了当前时间,不能导入超前月份
            if(strtotime(date('Y-m',time())) <= strtotime($data['cost_month'])) {
                $msg.="不允许导入超过当前月份的数据!";
            }
            $data['village_id'] = $village_id;
            $data['type']  = $type;
            $is_exit = Db::name('cost')->where($data)->find();
            if($is_exit) {
                $msg.="该条记录已存在!";
            }
            $data['price'] = $price;
            $data['area']  = $dataAll[$i][2];

            $data['total_money'] = $data['pay_money'] = number_format($price*$data['area'],2);
            $data['is_pay'] = 0;
            $data['create_time'] = time();

            if(empty($msg)){
                $insertData[] = $data;
            } else {
                $dataAll[$i][3] = $msg;
                $errorData[] =  $dataAll[$i];
            }
        }
        if(!empty($insertData) || !empty($errorData)) {
            Db::name('cost')->insertAll($insertData);
            if($errorData){
                //导出错误的数据
                if($type == "water") {
                    $file = "./formwork/importWater.xls";
                } else if($type == "electric") {
                    $file = "./formwork/importElectric.xls";
                } else {
                    $file = "./formwork/importGas.xls";
                }
                $phpExcel = \PHPExcel_IOFactory::load($file);
                $title = ['A','B','C','D'];
                $i = 2;
                foreach ($errorData as $k =>$v) {
                    foreach ($title as $key=>$value) {
                        $phpExcel->getActiveSheet()->setCellValue($value.$i,$v[$key]);
                    }
                    $i++;
                }
                $objWriter = new \PHPExcel_Writer_Excel5($phpExcel);
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename='.$outFileName);
                header('Cache-Control: max-age=0');
                $objWriter->save("php://output");
            } else{
                return ['code'=>200,'data'=>[]];
            }
        } else {
            return ['code'=>400,'msg'=>'文件内容为空!'];
        }

    }

    //验证短信验证码是否正确
    public static function rightCode($phone,$code) {
        $is_exit = Db::name('send_code')->where(['phone'=>$phone,'code'=>$code])->where('expires_time',">",time())->find();
        if($is_exit){
            return true;
        }
        return false;
    }

    //app用户登入之后同步数据
    public static function synUserData($uid,$phone) {
        //房间列表用户修改
        Db::name('house_vacancy')->where(['phone'=>$phone])->save(['uid'=>$uid]);
        //汽车关联
        Db::name('car')->where(['phone'=>$phone])->save(['uid'=>$uid]);

    }

    //通过房间编号和布局id获取房间地址
    public static function getVacancyAddress($vacancy_code,$layout_id) {
        $layoutString = Db::name('layout_list')->where(['layout_id'=>$layout_id])->value('code');
        $layoutArr = explode('-',$layoutString);
        $vacancyCodeArr = explode('-',$vacancy_code);
        $address="";
        foreach ($vacancyCodeArr as $k =>$v){

            $address.=$v.$layoutArr[$k];
        }
        return $address;
    }

    //通过房间id获取业主信息
    public static function getOwnerInfo($vacancy_id) {
        $data = Db::name('house_user_bind')->where(['vacancy_id'=>$vacancy_id,'type'=>0,'status'=>1])->field('type,status,phone,name')->find();
        return $data??[];
    }

    //获取需要绑定人员设备
    public static function getUserBindType() {
        return ['0'=>'业主','1'=>'家人','2'=>'租客','3'=>'替换业主'];
    }
    //获取需要绑定人员设备
    public static function getFamilyBindType() {
        return ['1'=>'配偶','2'=>'父母','3'=>'子女','4'=>'亲朋好友'];
    }

    //根据查询到的小区id,获取当前用户所在小区绑定的房间
    public static function getVacancy($village_id,$uid) {
        $data = Db::name('house_user_bind')->alias('hub')->leftJoin('house_vacancy hv','hv.vacancy_id = hub.vacancy_id')->where(['hub.village_id'=>$village_id,'hub.uid'=>$uid])->whereIn('hub.status',[1,2,4])
            ->field('hub.house_user_bind_id,hub.vacancy_id,hub.type,hv.layout_id,hv.vacancy_code')->select()->toArray();
        foreach ($data as $key =>$val) {
            $data[$key]['vacancy_address'] = self::getVacancyAddress($val['vacancy_code'],$val['layout_id']);
        }

        return $data;
    }

}