<?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 app\portal\Controller\OrderController; 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=[ '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 unUse(){ /*解除设备占用*/ $id=$this->request->param('id'); $eq=Db::name('equipment')->where('id',$id)->find(); dump($eq); /*查找对应订单*/ $map['eq_name']=$eq['name']; $order=\db('order')->where($map)->find(); //Db::name('equipment')->where('id',$id)->update(['use'=>0]); /*结束订单*/ $data['end_time']=time(); // $data['price']= $Order->getPrice($order['users_id'],$order['start_time'],time()); dump($data); // \db('order')->where($map)->update(); } //批量导入设备信息 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; } }