<?php
/**
 * Created by PhpStorm.
 * User: ruidiudiu
 * Date: 2018/11/20
 * Time: 16:33
 */

namespace app\portal\controller;


use app\portal\model\EquipmentModel;
use cmf\controller\AdminBaseController;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Style_Alignment;
use PHPExcel_Worksheet_Drawing;
use PHPExcel_Writer_Excel2007;
use QRcode;
use think\Db;

/**
 * Class AdminEquipmentController
 * @package app\portal\controller
 * @adminMenuRoot(
 *     'name'   =>'设备管理',
 *     'action' =>'index',
 *     'parent' =>'',
 *     'display'=> true,
 *     'order'  => 30,
 *     'icon'   =>'th',
 *     'remark' =>'设备管理'
 * )
 */
class AdminEquipmentController extends AdminBaseController{

    public function index(){
        $param=$this->request->param();
        $where=array();
        if (!empty($param['name'])){
            $where['name']=['like','%'.$param['name'].'%'];
        }
        if (!empty($param['hospital'])){
            $where['hospital']=['like','%'.$param['hospital'].'%'];
        }
        if (!empty($param['note'])){
            $where['note']=['like','%'.$param['note'].'%'];
        }
        if (!empty($param['start_number'])&&!empty($param['end_number'])){
            $where['serial_number']=['between',[$param['start_number'],$param['end_number']]];
        }
        if (!empty($param['start_time'])&&!empty($param['end_time'])){
            $where['create_time']=['between',[strtotime($param['start_time']),strtotime($param['end_time'])]];
        }
        $where['is_delete']=0;
        $Equipment=new EquipmentModel();
        $data=$Equipment->where($where)->order('create_time','desc')->paginate(10,false,['query'=>$param]);
        $this->assign('data',$data);
        $this->assign('name', isset($param['name']) ? $param['name'] : '');
        $this->assign('hospital', isset($param['hospital']) ? $param['hospital'] : '');
        $this->assign('note', isset($param['note']) ? $param['note'] : '');
        $this->assign('start_number', isset($param['start_number']) ? $param['start_number'] : '');
        $this->assign('end_number', isset($param['end_number']) ? $param['end_number'] : '');
        $this->assign('start_time', isset($param['start_time']) ? $param['start_time'] : '');
        $this->assign('end_time', isset($param['end_time']) ? $param['end_time'] : '');
        return $this->fetch();
    }

    public function add(){

        return $this->fetch();
    }

    public function addPost(){
        $data=$this->request->post();
        $data['create_time']=strtotime($data['create_time']);
        $data['qr_code'] = $this->createQrCode($data['name']);
        $is_equipment=Db::name('equipment')->where(['name'=>$data['name'],'is_delete'=>0])->count();
        if (!empty($is_equipment)){
            $this->apiResponse(302,'设备已存在');
        }
        $Equipment=new EquipmentModel();
        $res=$Equipment->save($data);
        if ($res){
            $this->apiResponse(200,'保存成功');
        }else{
            $this->apiResponse(301,'error');
        }
    }

    public function edit(){
        $id=$this->request->param('id');
        $data=Db::name('equipment')->where('id',$id)->find();

        $this->assign('data',$data);
        return $this->fetch();
    }

    public function editPost(){
        $param=$this->request->post();
        $id=$param['id'];
        $data=[
            'serial_number'=>$param['serial_number'],
            'hospital'=>$param['hospital'],
            'note'=>$param['note']
        ];
        $Equipment=new EquipmentModel();
        $res=$Equipment->save($data,['id'=>$id]);
        if ($res){
            $this->apiResponse(200,'保存成功');
        }else{
            $this->apiResponse(301,'error');
        }
    }

    //删除
    public function delete(){
        $id=$this->request->param('id');
        try{
            Db::name('equipment')->where('id',$id)->update(['is_delete'=>1]);
        }catch (\Exception $exception){
            $this->error('删除失败');
        }
        $this->success('删除成功');
    }


    //批量导入设备信息
    public function batch_add(){

        return $this->fetch();
    }
    //批量导入设备信息
    public function batch_addPost(){
        //上传excel文件
        $file = $this->request->file('myfile');
        //移到/public/uploads/excel/下
        $info = $file->move(ROOT_PATH.'public'.DS.'upload'.DS.'excel');
        //上传文件成功
        if($info) {
            //引入PHPExcel类
            vendor('VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel.php');
            vendor('VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php');
            //获取上传后的文件名
            $fileName = $info->getSaveName();
            //文件路径
            $filePath = ROOT_PATH.'public/upload/excel/'.$fileName;
            //实例化PHPExcel类
            //使用 PHPExcel_IOFactory 来鉴别文件应该使用哪一个读取类
            $inputFileType = PHPExcel_IOFactory::identify($filePath);
            //实例化类
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            //$PHPReader = new \PHPExcel_Reader_Excel2007();
            //读取excel文件
            $objPHPExcel = $objReader->load($filePath);
            //读取excel文件中的第一个工作表
            $sheet = $objPHPExcel->getSheet(0);
            $allRow = $sheet->getHighestRow();  //取得总行数
//            $testInfo=new TestinfoModel();
            //从第二行开始插入，第一行是列名
            $Equipment=new EquipmentModel();
            for ($j = 2; $j <= $allRow; $j++) {
                $data=[
                    'serial_number'=> $objPHPExcel->getActiveSheet()->getCell("a" . $j)->getValue(),
                    'mac_address' => $objPHPExcel->getActiveSheet()->getCell("b" . $j)->getValue(),
                    'name' => $objPHPExcel->getActiveSheet()->getCell("c" . $j)->getValue(),
                    'hospital' => $objPHPExcel->getActiveSheet()->getCell("d" . $j)->getValue(),
                    'note' => $objPHPExcel->getActiveSheet()->getCell("e" . $j)->getValue(),
                    'create_time' => time()
                ];
                $data['qr_code'] = $this->createQrCode($data['name']);
                //查询设备是否已存在
                $is_equipment=Db::name('equipment')->where(['name'=>$data['name'],'is_delete'=>0])->count();
                if (!empty($is_equipment)){
                    echo "第" . $j . "行".$data['name']."的设备已存在！<br/>";
                    continue;
                }
                try{
                    dump($data);
                    Db::name('equipment')->insert($data);
                    echo "第" . $j . "行数据导入成功！<br/>";
                }catch (\Exception $exception){
                    echo "第" . $j . "行数据导入失败！错误信息:".$exception->getMessage()."<br/>";
                }
            }
        }else{
            echo "上传文件失败！";
        }
    }

    //批量导出
    public function batch_export(){
        $ids=$this->request->param('ids');
        $ids=explode(',',$ids);

        $data= Db::name('equipment')->where('id','in',$ids)->select();

        // 导出Exl
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Worksheet/Drawing");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel2007");
        $objPHPExcel = new PHPExcel();

        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

        $objActSheet = $objPHPExcel->getActiveSheet();

        // 水平居中（位置很重要，建议在最初始位置）
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objActSheet->setCellValue('A1', '设备锁编号');
        $objActSheet->setCellValue('B1', '设备锁名称');
        $objActSheet->setCellValue('C1', '二维码');
        $objActSheet->setCellValue('D1', '初始医院');
        $objActSheet->setCellValue('E1', 'MAC地址');
        $objActSheet->setCellValue('F1', '备注');
        // 设置个表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);

        // 垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        foreach($data as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k, $v['serial_number']);
            $objActSheet->setCellValue('B'.$k, $v['name']);

            $img = Db::name('equipment')->where('id',$v['id'])->find();
            // 图片生成
            $objDrawing[$k] = new PHPExcel_Worksheet_Drawing();
            $objDrawing[$k]->setPath('./upload/'.$img['qr_code']);
            // 设置宽度高度
            $objDrawing[$k]->setHeight(80);//照片高度
            $objDrawing[$k]->setWidth(80); //照片宽度
            /*设置图片要插入的单元格*/
            $objDrawing[$k]->setCoordinates('C'.$k);
            // 图片偏移距离
            $objDrawing[$k]->setOffsetX(12);
            $objDrawing[$k]->setOffsetY(12);
            $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());

            // 表格内容
            $objActSheet->setCellValue('D'.$k, $v['hospital']);
            $objActSheet->setCellValue('E'.$k, $v['mac_address']);
            $objActSheet->setCellValue('F'.$k, $v['note']);

            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(80);

        }

        $fileName = '设备信息表';
        $date = date("Y-m-d",time());
        $fileName .= "_{$date}.xls";
        $fileName = iconv("utf-8", "gb2312", $fileName);
        //重命名表
        // $objPHPExcel->getActiveSheet()->setTitle('test');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        // END
    }

    //导出模板
    public function template_export(){

        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Writer/IWriter");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Writer/Abstract");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel2007");
        vendor("VENDOR_PATH/phpoffice/phpexcel/Classes/IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '设备编号')
            ->setCellValue('B1', 'MAC地址')
            ->setCellValue('C1', '设备名称')
            ->setCellValue('D1', '初始医院')
            ->setCellValue('F1', '备注');

        /*--------------开始从数据库提取信息插入Excel表中------------------*/

//        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
//        $count = count($sql);  //计算有多少条数据
//        for ($i = 2; $i <= $count+1; $i++) {
//            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['pname']);
//            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['access']);
//            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['jointime']);
//        }

        /*--------------下面是设置其他信息------------------*/
        $objPHPExcel->getActiveSheet()->setTitle('sheet1');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置

        header('Content-Disposition: attachment;filename="睡宝设备信息模板.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来

        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件

    }


    //生成二维码
    public function createQrCode($value){
        import('phpqrcode.qrlib',VENDOR_PATH,'.php');
        $name=date('YmdHis').rand(1000,9999);
        $filename='qrCode/'.$name.'.png';
        $filePath = 'upload/'.$filename; // 生成的文件名
        $errorCorrectionLevel = 'L';//容错级别
        $matrixPointSize = 6;//生成图片大小
        QRcode::png($value,$filePath, $errorCorrectionLevel, $matrixPointSize, 2);
        return $filename;
    }

}