第11章：数据统计与分析-02-运营数据统计

目录2. 运营数据统计2.1 需求分析2.2 完善页面2.2.1 定义模型数据2.2.2 发送请求获取动态数据2.3 后台代码2.3.1 Controller2.3.2 服务接口2.3.3 服务实现类2.3.4 Dao接口2.3.5 Mapper映射文件3. 运营数据统计报表导出3.1 需求分析3.2 提供模板文件3.3 完善页面3.4 后台代码2. 运营数据统计2.1 需求分析通过运营数据统计可以展示出体检机构的运营情况包括会员数据、预约到诊数据、热门套餐等信息。本章节就是要通过一个表格的形式来展示这些运营数据。效果如下图2.2 完善页面运营数据统计对应的页面为/pages/report_business.html。2.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 :[] } } }) /scriptdiv classbox styleheight: 900px div classexcelTitle el-button clickexportExcel导出Excel/el-button运营数据统计 /div div classexcelTime日期{{reportData.reportDate}}/div table classexceTable cellspacing0 cellpadding0 tr td colspan4 classheadBody会员数据统计/td /tr tr td width20% classtabletrBg新增会员数/td td width30%{{reportData.todayNewMember}}/td td width20% classtabletrBg总会员数/td td width30%{{reportData.totalMember}}/td /tr tr td classtabletrBg本周新增会员数/td td{{reportData.thisWeekNewMember}}/td td classtabletrBg本月新增会员数/td td{{reportData.thisMonthNewMember}}/td /tr tr td colspan4 classheadBody预约到诊数据统计/td /tr tr td classtabletrBg今日预约数/td td{{reportData.todayOrderNumber}}/td td classtabletrBg今日到诊数/td td{{reportData.todayVisitsNumber}}/td /tr tr td classtabletrBg本周预约数/td td{{reportData.thisWeekOrderNumber}}/td td classtabletrBg本周到诊数/td td{{reportData.thisWeekVisitsNumber}}/td /tr tr td classtabletrBg本月预约数/td td{{reportData.thisMonthOrderNumber}}/td td classtabletrBg本月到诊数/td td{{reportData.thisMonthVisitsNumber}}/td /tr tr td colspan4 classheadBody热门套餐/td /tr tr classtabletrBg textCenter td套餐名称/td td预约数量/td td占比/td td备注/td /tr tr v-fors in reportData.hotSetmeal td{{s.name}}/td td{{s.setmeal_count}}/td td{{s.proportion}}/td td/td /tr /table /div2.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 :[] } }, created() { //发送ajax请求获取动态数据 axios.get(/report/getBusinessReportData.do).then((res){ this.reportData res.data.data; }); } }) /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:获取运营统计数据成功 }2.3 后台代码2.3.1 Controller在ReportController中提供getBusinessReportData方法Reference private ReportService reportService; /** * 获取运营统计数据 * return */ RequestMapping(/getBusinessReportData.do) public Result getBusinessReportData(){ try { MapString, Object result reportService.getBusinessReport(); return new Result(true,MessageConstant.GET_BUSINESS_REPORT_SUCCESS,result); } catch (Exception e) { e.printStackTrace(); return new Result(true,MessageConstant.GET_BUSINESS_REPORT_FAIL); } }2.3.2 服务接口在health_interface工程中创建ReportService服务接口并声明getBusinessReport方法package com.liusp.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 - ListSetmeal */ public MapString,Object getBusinessReport() throws Exception; }2.3.3 服务实现类在health_service_provider工程中创建服务实现类ReportServiceImpl并实现ReportService接口package com.liusp.service.impl; import com.alibaba.dubbo.config.annotation.Service; import com.liusp.dao.MemberDao; import com.liusp.dao.OrderDao; import com.liusp.service.ReportService; import com.liusp.utils.DateUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import java.util.Date; 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; /** * 获得运营统计数据 * Map数据格式 * todayNewMember - number * totalMember - number * thisWeekNewMember - number * thisMonthNewMember - number * todayOrderNumber - number * todayVisitsNumber - number * thisWeekOrderNumber - number * thisWeekVisitsNumber - number * thisMonthOrderNumber - number * thisMonthVisitsNumber - number * hotSetmeal - ListSetmeal */ public MapString, Object getBusinessReport() throws Exception{ //获得当前日期 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 ListMap hotSetmeal orderDao.findHotSetmeal(); MapString,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; } }2.3.4 Dao接口在OrderDao和MemberDao中声明相关统计查询方法package com.liusp.dao; import com.liusp.pojo.Order; import java.util.List; import java.util.Map; public interface OrderDao { public void add(Order order); public ListOrder findByCondition(Order order); public Map findById4Detail(Integer id); public Integer findOrderCountByDate(String date); public Integer findOrderCountAfterDate(String date); public Integer findVisitsCountByDate(String date); public Integer findVisitsCountAfterDate(String date); public ListMap findHotSetmeal(); }package com.liusp.dao; import com.github.pagehelper.Page; import com.liusp.pojo.Member; import java.util.List; public interface MemberDao { public ListMember findAll(); public PageMember selectByCondition(String queryString); public void add(Member member); public void deleteById(Integer id); public Member findById(Integer id); public Member findByTelephone(String telephone); public void edit(Member member); public Integer findMemberCountBeforeDate(String date); public Integer findMemberCountByDate(String date); public Integer findMemberCountAfterDate(String date); public Integer findMemberTotalCount(); }2.3.5 Mapper映射文件在OrderDao.xml和MemberDao.xml中定义SQL语句OrderDao.xml!--根据日期统计预约数-- select idfindOrderCountByDate parameterTypestring resultTypeint select count(id) from t_order where orderDate #{value} /select !--根据日期统计预约数统计指定日期之后的预约数-- select idfindOrderCountAfterDate parameterTypestring resultTypeint select count(id) from t_order where orderDate gt; #{value} /select !--根据日期统计到诊数-- select idfindVisitsCountByDate parameterTypestring resultTypeint select count(id) from t_order where orderDate #{value} and orderStatus 已到诊 /select !--根据日期统计到诊数统计指定日期之后的到诊数-- select idfindVisitsCountAfterDate parameterTypestring resultTypeint select count(id) from t_order where orderDate gt; #{value} and orderStatus 已到诊 /select !--热门套餐查询前4条-- select idfindHotSetmeal resultTypemap 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 /selectMemberDao.xml!--根据日期统计会员数统计指定日期之前的会员数-- select idfindMemberCountBeforeDate parameterTypestring resultTypeint select count(id) from t_member where regTime lt; #{value} /select !--根据日期统计会员数-- select idfindMemberCountByDate parameterTypestring resultTypeint select count(id) from t_member where regTime #{value} /select !--根据日期统计会员数统计指定日期之后的会员数-- select idfindMemberCountAfterDate parameterTypestring resultTypeint select count(id) from t_member where regTime gt; #{value} /select !--总会员数-- select idfindMemberTotalCount resultTypeint select count(id) from t_member /select3. 运营数据统计报表导出3.1 需求分析运营数据统计报表导出就是将统计数据写入到Excel并提供给客户端浏览器进行下载以便体检机构管理人员对运营数据的查看和存档。3.2 提供模板文件本章节我们需要将运营统计数据通过POI写入到Excel文件对应的Excel效果如下通过上面的Excel效果可以看到表格比较复杂涉及到合并单元格、字体、字号、字体加粗、对齐方式等的设置。如果我们通过POI编程的方式来设置这些效果代码会非常繁琐。在企业实际开发中对于这种比较复杂的表格导出一般我们会提前设计一个Excel模板文件在这个模板文件中提前将表格的结构和样式设置好我们的程序只需要读取这个文件并在文件中的相应位置写入具体的值就可以了。在本章节资料中已经提供了一个名为report_template.xlsx的模板文件需要将这个文件复制到health_backend工程的template目录中3.3 完善页面在report_business.html页面提供导出按钮并绑定事件div classexcelTitle el-button clickexportExcel导出Excel/el-button运营数据统计 /divmethods:{ //导出Excel报表 exportExcel(){ window.location.href /report/exportBusinessReport.do; } }3.4 后台代码在ReportController中提供exportBusinessReport方法基于POI将数据写入到Excel中并通过输出流下载到客户端/** * 导出Excel报表 * return */ RequestMapping(/exportBusinessReport.do) public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response){ try{ //远程调用报表服务获取报表数据 MapString, Object result reportService.getBusinessReport(); //取出返回结果数据准备将报表数据写入到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); ListMap hotSetmeal (ListMap) result.get(hotSetmeal); //获得Excel模板文件绝对路径 String temlateRealPath ResourceUtils.getURL(classpath:).getPath() static/template/report_template.xlsx; System.out.println(temlateRealPath); // 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; 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());//占比 } //通过输出流进行文件下载 ServletOutputStream out response.getOutputStream(); response.setContentType(application/vnd.ms-excel);//代表excel文件类型 response.setHeader(content-Disposition, attachment;filenamereport.xlsx);//指定以附件形式下载 workbook.write(out); out.flush(); out.close(); workbook.close(); return null; }catch (Exception e){ return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL,null); } }