admin1/Home/Controller/DeviceRegistrationController.class.php

<?php

namespace Home\Controller;

use Think\Controller;

class DeviceRegistrationController extends ComController
{

    public function index()
    {
        $this->display('index');
    }

    public function upfile()
    {
        $data = I();
        if (isset($data['upfile'])) {
            if ($_FILES['dianfile']['name'] != '') {
                if ($_FILES["dianfile"]["error"] > 0) {
                    echo json_encode(array('statusCode' => 300, 'message' => $_FILES["dianfile"]["error"]));
                } else {
                    $files = $_FILES["dianfile"]["name"];
                    $ftypes = strtolower(substr(strrchr($files, '.'), 1));
                    if ($ftypes != "xls") {
                        echo json_encode(array('statusCode' => 300, 'message' => '格式不对,请上传.xls结尾的文件'));
                    } else {
                        $filetrue = move_uploaded_file($_FILES["dianfile"]["tmp_name"], "/var/www/html/jdxf/admin1/knowfile/devicedata/DeviceRegistration.xls");
                        if ($filetrue) {
                            $this->xlsin("/var/www/html/jdxf/admin1/knowfile/devicedata/DeviceRegistration.xls");
                        } else {
                            echo json_encode(array('statusCode' => 300, 'message' => '上传失败'));
                        }
                    }
                }
            } else {
                echo json_encode(array('statusCode' => 300, 'message' => '没有文件！'));
            }
        } else {
            $this->display('index');
        }
    }

    private function xlsin($filename)
    {//导入
        import("Org.Util_it.PHPExcel");
        $PHPExcel = new \PHPExcel();
        //如果excel文件后缀名为.xls，导入这个类
        import("Org.Util_it.PHPExcel.Reader.Excel5");
        //如果excel文件后缀名为.xlsx，导入这下类
        //import("Org.Util.PHPExcel.Reader.Excel2007");
        //$PHPReader=new \PHPExcel_Reader_Excel2007();

        $PHPReader = new \PHPExcel_Reader_Excel5();
        //载入文件
        $PHPExcel = $PHPReader->load($filename);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet = $PHPExcel->getSheet(0);
        //获取总列数
        $highestColumn = $currentSheet->getHighestColumn();
        //获取总行数
        $highestRow = $currentSheet->getHighestRow();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        $arr = array(1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z');
        //echo $highestRow.$highestColumn;
        // 一次读取一列
        //$type=M('know_type')->field('id')->where('name="'.$types.'"')->select();
//        $customer = M('userinfo_point');
//        $obtasksql = "insert into sp_userinfo_point(owner_code,owner_name,unitinfo,louyu,sim,maintainTime,dest_address,addr1,addr2,
//                              addr3,address,posistion,install_time,install_man,dwtype,owner_xh,s_interval,community,company,rtmp,
//                              hls,html5,danwei,owner_bl,push_param1,push_param2,push_param3,push_param4,login_param,transfer_type) values ";

        $expCellName = array('设备编号', '状态', '描述');
        $expTitle = '导入信息';
        $expTableData =[];
        $addall=0;
        $downnum = 0;
        for ($row = 2; $row <= $highestRow; $row++) {//行
            $val0 = $currentSheet->getCellByColumnAndRow(0, $row)->getValue();
            $val1 = $currentSheet->getCellByColumnAndRow(1, $row)->getValue();
            $val2 = $currentSheet->getCellByColumnAndRow(2, $row)->getValue();
            $val3 = $currentSheet->getCellByColumnAndRow(3, $row)->getValue();
            $val4 = $currentSheet->getCellByColumnAndRow(4, $row)->getValue();
            $val5 = $currentSheet->getCellByColumnAndRow(5, $row)->getValue();
            $val6 = $currentSheet->getCellByColumnAndRow(6, $row)->getValue();
            $val7 = $currentSheet->getCellByColumnAndRow(7, $row)->getValue();
            $val8 = $currentSheet->getCellByColumnAndRow(8, $row)->getValue();
            $val9 = $currentSheet->getCellByColumnAndRow(9, $row)->getValue();
            $val10 = $currentSheet->getCellByColumnAndRow(10, $row)->getValue();
            if (empty($val4)){
                $val4='';
            }
            if (empty($val8)){
                $val8='z';
            }
            if (empty($val9)){
                $val9='z';
            }
            if (empty($val10)){
                $val10='z';
            }
            if (empty($val6)){
                $val6='';
            }
            $devicedata['owner_code']=$val0;
            $devicedata['owner_name']=$val1;
            $devicedata['dwtype']=$val2;
            $devicedata['company']=$val3;
            $devicedata['sim']=$val4;
            $devicedata['louyu']=$val5;
            $devicedata['owner_bl']=$val6;
            $devicedata['unitinfo']=$val7;
            $devicedata['owner_xh']='0,'.$val8.$val9.$val10;
            $devicedata['install_time']=date('Y-m-d H:i:s', time());
            $res_one=M('owner')->add($devicedata);
            if ($res_one){
                $devicestatusdata['deviceid']=$val0;
                $devicestatusdata['deviceno']='0';
                $devicestatusdata['statustime']='0000-00-00 00:00:00';
                $res_two=M('devices_status')->add($devicestatusdata);
                if ($res_two){
                    if($val2 == '128'){
                        $tab=M('owner')->execute("CALL device_table('".$val0."','".$val2."')");
                    }else{
                        $tab=M('owner')->execute("CALL device_table1('".$val0."','".$val2."')");
                    }
                    $addall++;
                    if ($tab){
//                        $expTableData[$row-2]['device_code']=$val0;
//                        $expTableData[$row-2]['status']='导入成功';
//                        $expTableData[$row-2]['describe']='成功';
                    }else{
//                        $expTableData[$row-2]['device_code']=$val0;
//                        $expTableData[$row-2]['status']='导入失败';
//                        $expTableData[$row-2]['describe']='单独表创建失败';
                    }
                }else{
//                    $expTableData[$row-2]['device_code']=$val0;
//                    $expTableData[$row-2]['status']='导入失败';
//                    $expTableData[$row-2]['describe']='设备状态表插入失败';
                }
            }else{
//                $expTableData[$row-2]['device_code']=$val0;
//                $expTableData[$row-2]['status']='导入失败';
//                $expTableData[$row-2]['describe']='设备表插入失败';
            }
            $downnum++;
        }
        unlink('/var/www/html/jdxf/admin1/knowfile/devicedata/DeviceRegistration.xls');
//        $this->exportExcel($expTitle, $expCellName, $expTableData);

        $res = '共' . $downnum . '条,成功导入' . $addall . '条。';
        echo json_encode(array('statusCode' => 200, 'message' => '导入成功：' . $res));    //.'时间：'.$stra.','.$strb
    }








//    public function deldata()
//    {
//        $Model = M('userinfo_point');
//        $Data = I();
//        if (session('power') == 2) {
//            $this->mtReturn(300, '本账号没有该权限，如需开通请联系管理员', 1);
//        } else {
//            $used_data = M()->query("select * from sp_userinfo_point where id=" . $Data['id']);
//            $res = $Model->where('id=' . $Data['id'])->delete();
//            if ($res) {
//
//                //日志开始
//                $filename_log = "/var/www/html/jdxf/log/config_log" . date("Y-m-d") . ".log";
//                $data_log = "\r\n" . '[' . date("Y-m-d H:i:s") . ']操作人员手机号：' . session('phone_login') . '---删除sp_userinfo_point表---ID：' . $Data['id'] . "\r\n";
//                $data_log1 = '删除前数据：' . json_encode($used_data) . "\r\n";
//                file_put_contents($filename_log, $data_log, FILE_APPEND | LOCK_EX);
//                file_put_contents($filename_log, $data_log1, FILE_APPEND | LOCK_EX);
//                //日志开始结束
//
//                $this->mtReturn(200, '删除成功' . $Data['id']);
//            } else {
//                $this->mtReturn(300, '删除失败');
//            }
//
//        }
//
//    }
//
    public function downdata()
    {//导出Excel
        $Data = I();
        $UserVIew = M("userinfo_point");
        $Map = array();
        if (!empty($Data['deviceid'])) {
            $Map['deviceid'] = $Data['deviceid'];
        }
        if (!empty($Data['phone'])) {
            $Map['phone'] = $Data['phone'];
        }
        $permdata = $this->permdata();
        if (!empty($permdata)) {
            $Map['addman'] = $permdata;
        }
        //$Count= $UserVIew->where($Map)->count();// 查询满足要求的总记录数 	 ,contactman,contactphone
        $expTableData = $UserVIew->field("deviceid,`host`,circuit,point,desc")->where($Map)->order('addtime desc')->select();
        $expCellName = array('设备id', '主机号', '回路号', '设备号', '描述');
        $expTitle = '点位信息';
        $this->exportExcel($expTitle, $expCellName, $expTableData);
        // echo json_encode($expTableData);
    }
}