isAjax()) { //分页参数 $length = input('rows', 10, 'intval'); //每页条数 $page = input('page', 1, 'intval'); //第几页 $start = ($page - 1) * $length; //分页开始位置 //排序 $sortRow = input('sidx', 'sort', 'trim'); //排序列 $sort = input('sord', 'asc', 'trim'); //排序方式 $order = $sortRow . ' ' . $sort . ' ,id desc'; $title = input('title', '', 'trim'); if ($title) { $map[] = ['ga.title', 'like', '%' . $title . '%']; } $plan_id = input('plan_id','','trim'); if($plan_id != ''){ $map[] = ['gt.plan_id','=',$plan_id]; } $map[] = ['gtr.org_id', '=', $this->orgId]; $map = empty($map) ? true : $map; //数据查询 $lists = Db::name('green_task_record') ->alias('gtr') ->field('gtr.*,ga.title,gt.title as task_title') ->join('green_task gt','gt.id = gtr.green_task_id') ->Leftjoin('green_addr ga', 'ga.id=gtr.green_addr_id') ->where($map) ->limit($start, $length) ->order($order) ->select(); foreach ($lists as $k => $v) { $lists[$k]['task_user'] = Db::name('user') ->where('id', $v['user_id'])->value('real_name'); } //数据返回 $totalCount = Db::name('green_task_record') ->alias('gtr') ->field('gtr.*,ga.title,gt.title as task_title') ->join('green_task gt','gt.id = gtr.green_task_id') ->Leftjoin('green_addr ga', 'ga.id=gtr.green_addr_id') ->where($map) ->count(); $totalPage = ceil($totalCount / $length); $result['page'] = $page; $result['total'] = $totalPage; $result['records'] = $totalCount; $result['rows'] = $lists; return json($result); } else { $plans = model('GreenPlan')->getList($this->orgId); $this->assign('plans',$plans); $this->assign('meta_title', '绿化养护任务记录列表'); return $this->fetch(); } } // 详情 public function details($id) { $meta_title = '记录详情'; if (!$id) { $this->error('参数错误'); } $info = Db::name('green_task_record')->where('id', $id)->find(); $info['addr'] = Db::name('green_addr') ->where('id', $info['green_addr_id'])->value('title'); $info['task_name'] = Db::name('green_task') ->where('id', $info['green_task_id'])->value('title'); $info['task_addr_form'] = Db::name('green_addr_form') ->alias('a') ->join('green_task_addr b','a.id = b.green_form_id') ->where('b.id', $info['green_task_addr_id']) ->value('a.title'); $info['task_user'] = Db::name('user')->where('id', $info['user_id'])->value('real_name'); $info['check_json'] =$info['check_json']? json_decode($info['check_json'], true):[]; $this->assign('meta_title', $meta_title); $this->assign('info', $info); return $this->fetch(); } //excel导出 public function export() { //获取excel表名 $meta_title = '绿化养护任务记录导出'; include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php'; if (request()->isPost()) { $start = input('start'); $end = input('end'); $where[] = ['create_yyyymmdd', '>=', $start]; $where[] = ['create_yyyymmdd', '<=', $end]; $where[] = ['org_id', '=', $this->orgId]; $ret = Db::name('green_task_record')->where($where)->select(); foreach ($ret as $k => $v) { $ret[$k]['addr'] = Db::name('green_addr')->where('id', $v['green_addr_id'])->value('title'); $ret[$k]['task_title'] = Db::name('green_task')->where('id', $v['green_task_id'])->value('title'); $ret[$k]['task_user'] = Db::name('user')->where('id', $v['user_id'])->value('real_name'); } //实例化PHPExcel类 $objPHPExcel =new \PHPExcel(); //激活当前的sheet表 $objPHPExcel->setActiveSheetIndex(0); //设置表格头(即excel表格的第一行) $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '编号') ->setCellValue('B1', '执行人') ->setCellValue('C1', '计划') ->setCellValue('D1', '地点') ->setCellValue('E1', '汇报内容') ->setCellValue('F1', '检查项'); // 设置表格头水平居中 $objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('H1')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置列水平居中 $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->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); $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置单元格宽度 $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(60); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30); $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(30); //循环刚取出来的数组,将数据逐一添加到excel表格。 for ($i = 0; $i < count($ret); $i++) { $objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $ret[$i]['id']);//ID $objPHPExcel->getActiveSheet()->setCellValue('B' . ($i + 2), $ret[$i]['task_user']);//执行人 $objPHPExcel->getActiveSheet()->setCellValue('C' . ($i + 2), $ret[$i]['task_title']);//名称 $objPHPExcel->getActiveSheet()->setCellValue('D' . ($i + 2), $ret[$i]['addr']);//地点 $objPHPExcel->getActiveSheet()->setCellValue('E' . ($i + 2), $ret[$i]['content']);//汇报内容 $result = json_decode($v['check_json'], true); $str = array(); foreach ($result as $kk => $vv) { $ss = $vv['title'] . ":"; if ($vv['type'] == 1) { $ss .= $vv['result']; } else { if ($vv['result'] == 1) { $ss .= '☑'; } else { $ss .= '□'; } } $str[] = $ss; } $objPHPExcel->getActiveSheet()->setCellValue('F' . ($i + 2), implode(' ; ', $str));//检查项 } //设置保存的Excel表格名称 $filename = '绿化养护任务记录_' . date('YmdHis', time()) . '.xls'; //设置当前激活的sheet表格名称 $objPHPExcel->getActiveSheet()->setTitle('绿化养护任务记录'); //设置浏览器窗口下载表格 ob_end_clean(); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="' . $filename); //生成excel文件 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //下载文件在浏览器窗口 return $objWriter->save('php://output'); } $this->assign('s',date('Ymd')); $this->assign('e',date('Ymd',time()+(3*86400))); $this->assign('meta_title', $meta_title); return $this->fetch(); } }