船志健康项目-运营数据统计、POI报表11
一、运营数据统计
1. 需求分析
2. 完善页面
运营数据统计对应的页面为/pages/report_business.html。
2.1 定义模型数据
定义数据模型，通过VUE的数据绑定展示数据
<script> var vue = new Vue({ el: '#app', data:{ reportData:{ reportDate:null, todayNewMember :0, totalMember :0, thisWeekNewMember :0, thisMonthNewMember :0, todayOrderNumber :0, todayVisitsNumber :0, thisWeekOrderNumber :0, thisWeekVisitsNumber :0, thisMonthOrderNumber :0, thisMonthVisitsNumber :0, hotSetmeal :[ {name:'阳光爸妈升级肿瘤12项筛查（男女单人）体检套餐',setmeal_count:200,proportion:0.222}, {name:'阳光爸妈升级肿瘤12项筛查体检套餐',setmeal_count:200,proportion:0.222} ] } } }) </script>
<div class="box" style="height: 900px"> <div class="excelTitle" > <el-button @click="exportExcel">导出Excel</el-button> <el-button @click="exportPDF">导出PDF</el-button>运营数据统计 </div> <div class="excelTime">日期：{{reportData.reportDate}}</div> <table class="exceTable" cellspacing="0" cellpadding="0"> <tr> <td colspan="4" class="headBody">会员数据统计</td> </tr> <tr> <td width='20%' class="tabletrBg">新增会员数</td> <td width='30%'>{{reportData.todayNewMember}}</td> <td width='20%' class="tabletrBg">总会员数</td> <td width='30%'>{{reportData.totalMember}}</td> </tr> <tr> <td class="tabletrBg">本周新增会员数</td> <td>{{reportData.thisWeekNewMember}}</td> <td class="tabletrBg">本月新增会员数</td> <td>{{reportData.thisMonthNewMember}}</td> </tr> <tr> <td colspan="4" class="headBody">预约到诊数据统计</td> </tr> <tr> <td class="tabletrBg">今日预约数</td> <td>{{reportData.todayOrderNumber}}</td> <td class="tabletrBg">今日到诊数</td> <td>{{reportData.todayVisitsNumber}}</td> </tr> <tr> <td class="tabletrBg">本周预约数</td> <td>{{reportData.thisWeekOrderNumber}}</td> <td class="tabletrBg">本周到诊数</td> <td>{{reportData.thisWeekVisitsNumber}}</td> </tr> <tr> <td class="tabletrBg">本月预约数</td> <td>{{reportData.thisMonthOrderNumber}}</td> <td class="tabletrBg">本月到诊数</td> <td>{{reportData.thisMonthVisitsNumber}}</td> </tr> <tr> <td colspan="4" class="headBody">热门套餐</td> </tr> <tr class="tabletrBg textCenter"> <td>套餐名称</td> <td>预约数量</td> <td>占比</td> <td>备注</td> </tr> <tr v-for="s in reportData.hotSetmeal"> <td>{{s.name}}</td> <td>{{s.setmeal_count}}</td> <td>{{s.proportion}}</td> <td></td> </tr> </table> </div>
2.2 发送请求获取动态数据
在VUE的钩子函数中发送ajax请求获取动态数据，通过VUE的数据绑定将数据展示到页面
<script> var vue = new Vue({ el: '#app', data:{ reportData:{ reportDate:null, todayNewMember :0, totalMember :0, thisWeekNewMember :0, thisMonthNewMember :0, todayOrderNumber :0, todayVisitsNumber :0, thisWeekOrderNumber :0, thisWeekVisitsNumber :0, thisMonthOrderNumber :0, thisMonthVisitsNumber :0, hotSetmeal :[ {name:'阳光爸妈升级肿瘤12项筛查（男女单人）体检套餐',setmeal_count:200,proportion:0.222}, {name:'阳光爸妈升级肿瘤12项筛查体检套餐',setmeal_count:200,proportion:0.222} ] } }, created() { axios.get("/report/getBusinessReportData.do").then((res)=>{ if(res.data.flag){ this.reportData = res.data.data; }else { this.$message.error(res.data.message); } }); } }) </script>
根据页面对数据格式的要求，我们发送ajax请求，服务端需要返回如下格式的数据：
{
"data":{
"todayVisitsNumber":0,
"reportDate":"2019-04-25",
"todayNewMember":0,
"thisWeekVisitsNumber":0,
"thisMonthNewMember":2,
"thisWeekNewMember":0,
"totalMember":10,
"thisMonthOrderNumber":2,
"thisMonthVisitsNumber":0,
"todayOrderNumber":0,
"thisWeekOrderNumber":0,
"hotSetmeal":[
{"proportion":0.4545,"name":"粉红珍爱(女)升级TM12项筛查体检套
餐","setmeal_count":5},
{"proportion":0.1818,"name":"阳光爸妈升级肿瘤12项筛查体检套
餐","setmeal_count":2},
{"proportion":0.1818,"name":"珍爱高端升级肿瘤12项筛查","setmeal_count":2},
{"proportion":0.0909,"name":"孕前检查套餐","setmeal_count":1}
],
},
"flag":true,
"message":"获取运营统计数据成功"
}
3. 后台代码
3.1 Controller
在ReportController中提供getBusinessReportData方法
@Reference private ReportService reportService; //获取运营统计数据 @RequestMapping("/getBusinessReportData") public Result getBusinessReportData(){ try{ Map<String,Object> map = reportService.getBusinessReportData(); return new Result(true,MessageConstant.GET_BUSINESS_REPORT_SUCCESS,map); }catch (Exception e){ e.printStackTrace(); return new Result(true,MessageConstant.GET_BUSINESS_REPORT_FAIL); } }
3.2 服务接口
在health_interface工程中创建ReportService服务接口并声明getBusinessReportData方法
package com.itheima.service; import java.util.Map; public interface ReportService { /** * 获得运营统计数据 * Map数据格式： * todayNewMember -> number * totalMember -> number * thisWeekNewMember -> number * thisMonthNewMember -> number * todayOrderNumber -> number * todayVisitsNumber -> number * thisWeekOrderNumber -> number * thisWeekVisitsNumber -> number * thisMonthOrderNumber -> number * thisMonthVisitsNumber -> number * hotSetmeals -> List<Setmeal> */ public Map<String,Object> getBusinessReportData() throws Exception; }
3.3 服务实现类
在health_service_provider工程中创建服务实现类ReportServiceImpl并实现ReportService接口
package com.itheima.service.impl; import com.alibaba.dubbo.config.annotation.Service; import com.itheima.dao.MemberDao; import com.itheima.dao.OrderDao; import com.itheima.service.ReportService; import com.itheima.utils.DateUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 统计报表服务 */ @Service(interfaceClass = ReportService.class) @Transactional public class ReportServiceImpl implements ReportService { @Autowired private MemberDao memberDao; @Autowired private OrderDao orderDao; @Override public Map<String, Object> getBusinessReportData() throws Exception { /** * 获得运营统计数据 * Map数据格式： * todayNewMember -> number * totalMember -> number * thisWeekNewMember -> number * thisMonthNewMember -> number * todayOrderNumber -> number * todayVisitsNumber -> number * thisWeekOrderNumber -> number * thisWeekVisitsNumber -> number * thisMonthOrderNumber -> number * thisMonthVisitsNumber -> number * hotSetmeal -> List<Setmeal> */ //获得当前日期 String today = DateUtils.parseDate2String(DateUtils.getToday()); //获得本周一的日期 String thisWeekMonday = DateUtils.parseDate2String(DateUtils.getThisWeekMonday()); //获得本月第一天的日期 String firstDay4ThisMonth = DateUtils.parseDate2String(DateUtils.getFirstDay4ThisMonth()); //今日新增会员数 Integer todayNewMember = memberDao.findMemberCountByDate(today); //总会员数 Integer totalMember = memberDao.findMemberTotalCount(); //本周新增会员数 Integer thisWeekNewMember = memberDao.findMemberCountAfterDate(thisWeekMonday); //本月新增会员数 Integer thisMonthNewMember = memberDao.findMemberCountAfterDate(firstDay4ThisMonth); //今日预约数 Integer todayOrderNumber = orderDao.findOrderCountByDate(today); //本周预约数 Integer thisWeekOrderNumber = orderDao.findOrderCountAfterDate(thisWeekMonday); //本月预约数 Integer thisMonthOrderNumber = orderDao.findOrderCountAfterDate(firstDay4ThisMonth); //今日到诊数 Integer todayVisitsNumber = orderDao.findVisitsCountByDate(today); //本周到诊数 Integer thisWeekVisitsNumber = orderDao.findVisitsCountAfterDate(thisWeekMonday); //本月到诊数 Integer thisMonthVisitsNumber = orderDao.findVisitsCountAfterDate(firstDay4ThisMonth); //热门套餐（取前4） List<Map> hotSetmeal = orderDao.findHotSetmeal(); Map<String,Object> result = new HashMap<>(); result.put("reportDate",today); result.put("todayNewMember",todayNewMember); result.put("totalMember",totalMember); result.put("thisWeekNewMember",thisWeekNewMember); result.put("thisMonthNewMember",thisMonthNewMember); result.put("todayOrderNumber",todayOrderNumber); result.put("thisWeekOrderNumber",thisWeekOrderNumber); result.put("thisMonthOrderNumber",thisMonthOrderNumber); result.put("todayVisitsNumber",todayVisitsNumber); result.put("thisWeekVisitsNumber",thisWeekVisitsNumber); result.put("thisMonthVisitsNumber",thisMonthVisitsNumber); result.put("hotSetmeal",hotSetmeal); return result; } }
3.4 Dao接口
在OrderDao和MemberDao中声明相关统计查询方法
package com.itheima.dao;
import com.itheima.pojo.Member;
public interface MemberDao {
public Integer findMemberCountByDate(String today);
public Integer findMemberTotalCount();
public Integer findMemberCountAfterDate(String thisWeekMonday);
}
package com.itheima.dao; import com.itheima.pojo.Order; import java.util.List; import java.util.Map; public interface OrderDao { public Integer findOrderCountByDate(String today); public Integer findOrderCountAfterDate(String firstDay4ThisMonth); public Integer findVisitsCountByDate(String today); public Integer findVisitsCountAfterDate(String thisWeekMonday); public List<Map> findHotSetmeal(); }
3.5 Mapper映射文件
在OrderDao.xml和MemberDao.xml中定义SQL语句
MemberDao.xml：
<!--根据日期统计会员数--> <select id="findMemberCountByDate" parameterType="string" resultType="int"> select count(id) from t_member where regTime = #{value} </select> <!--根据日期统计会员数，统计指定日期之后的会员数--> <select id="findMemberCountAfterDate" parameterType="string" resultType="int"> select count(id) from t_member where regTime >= #{value} </select> <!--总会员数--> <select id="findMemberTotalCount" resultType="int"> select count(id) from t_member </select>
OrderDao.xml：
<!--根据日期统计预约数--> <select id="findOrderCountByDate" parameterType="string" resultType="int"> select count(id) from t_order where orderDate = #{value} </select> <!--根据日期统计预约数，统计指定日期之后的预约数--> <select id="findOrderCountAfterDate" parameterType="string" resultType="int"> select count(id) from t_order where orderDate >= #{value} </select> <!--根据日期统计到诊数--> <select id="findVisitsCountByDate" parameterType="string" resultType="int"> select count(id) from t_order where orderDate = #{value} and orderStatus = '已到诊' </select> <!--根据日期统计到诊数，统计指定日期之后的到诊数--> <select id="findVisitsCountAfterDate" parameterType="string" resultType="int"> select count(id) from t_order where orderDate >= #{value} and orderStatus = '已到诊' </select> <!--热门套餐，查询前5条--> <select id="findHotSetmeal" resultType="map"> select s.name, count(o.id) setmeal_count ,count(o.id)/(select count(id) from t_order) proportion from t_order o inner join t_setmeal s on s.id = o.setmeal_id group by o.setmeal_id order by setmeal_count desc limit 0,4 </select>
二、运营数据统计报表导出
1. 需求分析
运营数据统计报表导出就是将统计数据写入到Excel并提供给客户端浏览器进行下载，以便体检机构管理人员对运营数据的查看和存档。
2. 提供模板文件
本章节我们需要将运营统计数据通过POI写入到Excel文件，对应的Excel效果如下：
通过上面的Excel效果可以看到，表格比较复杂，涉及到合并单元格、字体、字号、字体加粗、对齐方式等的设置。如果我们通过POI编程的方式来设置这些效果代码会非常繁琐。
在企业实际开发中，对于这种比较复杂的表格导出一般我们会提前设计一个Excel模板文件，在这个模板文件中提前将表格的结构和样式设置好，我们的程序只需要读取这个文件并在文件中的相应位置写入 具体的值就可以了。
在本章节资料中已经提供了一个名为report_template.xlsx的模板文件，需要将这个文件复制到health_backend工程的template目录中
3. 完善页面
在report_business.html页面提供导出按钮并绑定事件
<div class="excelTitle" > <el-button @click="exportExcel">导出Excel</el-button>运营数据统计 </div>
methods:{
//导出Excel报表
exportExcel(){
//此处不用ajax请求，因为响应数据不是json数据而是输出流
window.location.href = '/report/exportBusinessReport.do';
}
}
4. 后台代码
在ReportController中提供exportBusinessReport方法，基于POI将数据写入到Excel中并通过输出流下 载到客户端
//导出excel报表 @RequestMapping("/exportBusinessReport") public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response){ try{ //远程调用报表服务获取报表数据 Map<String, Object> result = reportService.getBusinessReportData(); //取出返回结果数据，准备将报表数据写入到Excel文件中 String reportDate = (String) result.get("reportDate"); Integer todayNewMember = (Integer) result.get("todayNewMember"); Integer totalMember = (Integer) result.get("totalMember"); Integer thisWeekNewMember = (Integer) result.get("thisWeekNewMember"); Integer thisMonthNewMember = (Integer) result.get("thisMonthNewMember"); Integer todayOrderNumber = (Integer) result.get("todayOrderNumber"); Integer thisWeekOrderNumber = (Integer) result.get("thisWeekOrderNumber"); Integer thisMonthOrderNumber = (Integer) result.get("thisMonthOrderNumber"); Integer todayVisitsNumber = (Integer) result.get("todayVisitsNumber"); Integer thisWeekVisitsNumber = (Integer) result.get("thisWeekVisitsNumber"); Integer thisMonthVisitsNumber = (Integer) result.get("thisMonthVisitsNumber"); List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal"); //获得Excel模板文件绝对路径 String temlateRealPath = request.getSession().getServletContext().getRealPath("template") + File.separator +"report_template.xlsx"; //读取模板文件创建Excel表格对象 XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath))); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = sheet.getRow(2); row.getCell(5).setCellValue(reportDate);//日期 row = sheet.getRow(4); row.getCell(5).setCellValue(todayNewMember);//新增会员数（本日） row.getCell(7).setCellValue(totalMember);//总会员数 row = sheet.getRow(5); row.getCell(5).setCellValue(thisWeekNewMember);//本周新增会员数 row.getCell(7).setCellValue(thisMonthNewMember);//本月新增会员数 row = sheet.getRow(7); row.getCell(5).setCellValue(todayOrderNumber);//今日预约数 row.getCell(7).setCellValue(todayVisitsNumber);//今日到诊数 row = sheet.getRow(8); row.getCell(5).setCellValue(thisWeekOrderNumber);//本周预约数 row.getCell(7).setCellValue(thisWeekVisitsNumber);//本周到诊数 row = sheet.getRow(9); row.getCell(5).setCellValue(thisMonthOrderNumber);//本月预约数 row.getCell(7).setCellValue(thisMonthVisitsNumber);//本月到诊数 int rowNum = 12;//第13行 for(Map map : hotSetmeal){//热门套餐 String name = (String) map.get("name"); Long setmeal_count = (Long) map.get("setmeal_count"); BigDecimal proportion = (BigDecimal) map.get("proportion"); row = sheet.getRow(rowNum ++); row.getCell(4).setCellValue(name);//套餐名称 row.getCell(5).setCellValue(setmeal_count);//预约数量 row.getCell(6).setCellValue(proportion.doubleValue());//占比 } //使用输出流进行表格下载,基于浏览器作为客户端下载 OutputStream out = response.getOutputStream(); response.setContentType("application/vnd.ms-excel");//代表的是Excel文件类型 response.setHeader("content-Disposition", "attachment;filename=report.xlsx");//指定以附件形式进行下载 workbook.write(out); out.flush(); out.close(); workbook.close(); return null; }catch (Exception e){ e.printStackTrace(); return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL,null); } }
浙公网安备 33010602011771号