1. 套餐预约占比饼形图 1.1 需求分析 会员可以通过移动端自助进行体检预约,在预约时需要选择预约的体检套餐。本章节我们需要通过饼形图直观的展示出会员预约的各个套餐占比情况。展示效果如下图:
1.2 完善页面 套餐预约占比饼形图对应的页面为/pages/report_setmeal.html。
1.2.1 导入ECharts库 1 <script src ="../plugins/echarts/echarts.js" > </script >
1.2.2 参照官方实例导入饼形图 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 <div class ="box" > <div id ="chart1" style ="height:600px;" > </div > </div > <script type ="text/javascript" > var myChart1 = echarts.init(document .getElementById('chart1' )); axios.get("/report/getSetmealReport.do" ).then((res )=> { myChart1.setOption({ title : { text: '套餐预约占比' , subtext: '' , x:'center' }, tooltip : { trigger: 'item' , formatter: "{a} <br/>{b} : {c} ({d}%)" }, legend: { orient: 'vertical' , left: 'left' , data: res.data.data.setmealNames }, series : [ { name: '套餐预约占比' , type: 'pie' , radius : '55%' , center: ['50%' , '60%' ], data:res.data.data.setmealCount, itemStyle: { emphasis: { shadowBlur: 10, shadowOffsetX: 0, shadowColor: 'rgba(0, 0, 0, 0.5)' } } } ] }); });</script >
根据饼形图对数据格式的要求,我们发送ajax请求,服务端需要返回如下格式的数据:
1 2 3 4 5 6 7 8 9 10 11 12 { "data" :{ "setmealNames" :["套餐1" ,"套餐2" ,"套餐3" ], "setmealCount" :[ {"name" :"套餐1" ,"value" :10 }, {"name" :"套餐2" ,"value" :30 }, {"name" :"套餐3" ,"value" :25 } ] }, "flag" :true , "message" :"获取套餐统计数据成功" }
1.3 后台代码 1.3.1 Controller 在health_backend工程的ReportController中提供getSetmealReport方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 @Referenceprivate SetmealService setmealService; @RequestMapping("/getSetmealReport" )public Result getSetmealReport(){ List <Map <String , Object>> list = setmealService.findSetmealCount(); Map <String ,Object> map = new HashMap<>(); map .put("setmealCount" ,list ); List <String > setmealNames = new ArrayList<>(); for(Map <String ,Object> m : list ){ String name = (String ) m.get("name" ); setmealNames.add(name); } map .put("setmealNames" ,setmealNames); return new Result(true , MessageConstant.GET_SETMEAL_COUNT_REPORT_SUCCESS,map ); }
1.3.2 服务接口 在SetmealService服务接口中扩展方法findSetmealCount
1 public List <Map <String ,Object >> findSetmealCount();
1.3.3 服务实现类 在SetmealServiceImpl服务实现类中实现findSetmealCount方法
1 2 3 public List <Map <String , Object >> findSetmealCount() { return setmealDao.findSetmealCount(); }
1.3.4 Dao接口 在SetmealDao接口中扩展方法findSetmealCount
1 public List <Map <String ,Object >> findSetmealCount();
1.3.5 Mapper映射文件 在SetmealDao.xml映射文件中提供SQL语句
1 2 3 4 5 6 <select id="findSetmealCount" resultType="map"> select s.name,count(o.id) as value from t_order o ,t_setmeal s where o.setmeal_id = s.id group by s.name </select >
2. 运营数据统计 2.1 需求分析 通过运营数据统计可以展示出体检机构的运营情况,包括会员数据、预约到诊数据、热门套餐等信息。本章节就是要通过一个表格的形式来展示这些运营数据。效果如下图:
2.2 完善页面 运营数据统计对应的页面为/pages/report_business.html。
2.2.1 定义模型数据 定义数据模型,通过VUE的数据绑定展示数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 <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 :[] } } }) </script > <div class ="box" style ="height: 900px" > <div class ="excelTitle" > <el-button @click ="exportExcel" > 导出Excel</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.2 发送请求获取动态数据 在VUE的钩子函数中发送ajax请求获取动态数据,通过VUE的数据绑定将数据展示到页面
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 <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请求,服务端需要返回如下格式的数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 { "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方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @Reference private ReportService reportService;@RequestMapping ("/getBusinessReportData" ) public Result getBusinessReportData(){ try { Map <String , 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方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 package com.itheima.service;import java.util.Map ; public interface ReportService { public Map <String ,Object > getBusinessReport() throws Exception; }
2.3.3 服务实现类 在health_service_provider工程中创建服务实现类ReportServiceImpl并实现ReportService接口
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 package com.itheima.service;import com.alibaba.dubbo.config.annotation.Service;import com.itheima.dao.MemberDao;import com.itheima.dao.OrderDao;import com.itheima.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; public Map <String , 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); 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; } }
2.3.4 Dao接口 在OrderDao和MemberDao中声明相关统计查询方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 package com.itheima.dao;import com.itheima.pojo.Order ;import java.util.List ;import java.util.Map ;public interface OrderDao { public void add(Order order ); public List <Order > 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 List <Map > findHotSetmeal(); } package com.itheima.dao;import com.github.pagehelper.Page;import com.itheima.pojo.Member;import java.util.List ;public interface MemberDao { public List <Member> findAll(); public Page<Member> 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 <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 > <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 >
MemberDao.xml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <select id ="findMemberCountBeforeDate" parameterType ="string" resultType ="int" > select count(id) from t_member where regTime < = # {value} </select > <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 >
3. 运营数据统计报表导出 3.1 需求分析 运营数据统计报表导出就是将统计数据写入到Excel并提供给客户端浏览器进行下载,以便体检机构管理人员对运营数据的查看和存档。
3.2 提供模板文件 本章节我们需要将运营统计数据通过POI写入到Excel文件,对应的Excel效果如下:
通过上面的Excel效果可以看到,表格比较复杂,涉及到合并单元格、字体、字号、字体加粗、对齐方式等的设置。如果我们通过POI编程的方式来设置这些效果代码会非常繁琐。
在企业实际开发中,对于这种比较复杂的表格导出一般我们会提前设计一个Excel模板文件,在这个模板文件中提前将表格的结构和样式设置好,我们的程序只需要读取这个文件并在文件中的相应位置写入具体的值就可以了。
在本章节资料中已经提供了一个名为report_template.xlsx的模板文件,需要将这个文件复制到health_backend工程的template目录中
3.3 完善页面 在report_business.html页面提供导出按钮并绑定事件
1 2 3 4 5 6 7 8 9 <div class ="excelTitle" > <el-button @click="exportExcel" >导出Excel</el-button>运营数据统计 </div> methods:{ exportExcel(){ window .location.href = '/report /exportBusinessReport.do '; } }
3.4 后台代码 在ReportController中提供exportBusinessReport方法,基于POI将数据写入到Excel中并通过输出流下载到客户端
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 @RequestMapping("/exportBusinessReport" ) public Result exportBusinessReport(HttpServletRequest request , HttpServletResponse response ) { try { Map<String, Object> result = reportService.getBusinessReport() ; 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" ); String temlateRealPath = request.getSession() .getServletContext() .getRealPath("template" ) + File . separator + "report_template.xlsx" ; 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" ) ; response.setHeader("content-Disposition" , "attachment;filename=report.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 ) ; } }