1. 套餐预约占比饼形图
1.1 需求分析
会员可以通过移动端自助进行体检预约，在预约时需要选择预约的体检套餐。本章节我们需要通过饼形图直观的展示出会员预约的各个套餐占比情况。展示效果如下图：
1.2 完善页面
套餐预约占比饼形图对应的页面为/pages/report_setmeal.html。
1.2.1 导入ECharts库
<script src="../plugins/echarts/echarts.js"></script>
1.2.2 参照官方实例导入饼形图
<!DOCTYPE html>
<html>
<head>
<!-- 页面meta -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>XX健康</title>
<meta name="description" content="XX健康">
<meta name="keywords" content="XX健康">
<meta content="width=device-width,initial-scale=1,maximum-scale=1,user-scalable=no" name="viewport">
<!-- 引入样式 -->
<link rel="stylesheet" href="../css/style.css">
<script src="../plugins/echarts/echarts.js"></script>
</head>
<body class="hold-transition">
<div id="app">
<div class="content-header">
<h1>统计分析<small>套餐占比</small></h1>
<el-breadcrumb separator-class="el-icon-arrow-right" class="breadcrumb">
<el-breadcrumb-item :to="{ path: '/' }">首页</el-breadcrumb-item>
<el-breadcrumb-item>统计分析</el-breadcrumb-item>
<el-breadcrumb-item>套餐占比</el-breadcrumb-item>
</el-breadcrumb>
</div>
<div class="app-container">
<div class="box">
<!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
<div id="chart1" style="height:600px;"></div>
</div>
</div>
</div>
</body>
<!-- 引入组件库 -->
<script src="../js/vue.js"></script>
<script src="../js/axios-0.18.0.js"></script>
<script type="text/javascript">
// 基于准备好的dom，初始化echarts实例
var myChart1 = echarts.init(document.getElementById('chart1'));
// 使用刚指定的配置项和数据显示图表。
//myChart.setOption(option);
axios.get("/report/getSetmealReport.do").then((res)=>{
myChart1.setOption({
title : {
text: '套餐预约占比',
subtext: '',
x:'center'
},
tooltip : {//提示框组件
trigger: 'item',//触发类型，在饼形图中为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>
</html>
根据饼形图对数据格式的要求，我们发送ajax请求，服务端需要返回如下格式的数据：
{
"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方法
@Reference
private SetmealService setmealService;
/**
* 查询报表
* @return
*/
@RequestMapping("/getSetmealReport")
public Result getSetmealReport() {
try {
Map<String, Object> data = new HashMap<>();
List<String> setmealNames = new ArrayList<>();
List<Map<String, Object>> setmealCount = setmealService.findSetmealCount();
data.put("setmealCount", setmealCount);
if (!CollectionUtils.isEmpty(setmealCount)) {
for (Map<String, Object> map : setmealCount) {
String name = (String) map.get("name");
setmealNames.add(name);
}
}
data.put("setmealNames", setmealNames);
return new Result(true, MessageConstant.GET_SETMEAL_COUNT_REPORT_SUCCESS, data);
} catch (Exception e) {
e.printStackTrace();
return new Result(false, MessageConstant.GET_SETMEAL_COUNT_REPORT_FAIL);
}
}
建议:在不区分前后的情况下,优先查询数据较完整(包含其他所需要数据)的数据
1.3.2 服务接口
在SetmealService服务接口中扩展方法findSetmealCount
public List<Map<String,Object>> findSetmealCount();
1.3.3 服务实现类
在SetmealServiceImpl服务实现类中实现findSetmealCount方法
public List <Map<String, Object>> findSetmealCount() {
return setmealDao.findSetmealCount();
}
1.3.4 Dao接口
在SetmealDao接口中扩展方法findSetmealCount
public List<Map<String,Object>> findSetmealCount();
1.3.5 Mapper映射文件
在SetmealDao.xml映射文件中提供SQL语句
<!--查询套餐占比数据-->
<select id="findSetmealCount" resultType="hashMap">
select
s.name name,
count(o.id) value
from t_setmeal s, t_order o
where s.id = o.setmeal_id
group by s.name
</select>
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 :[]
}
}
})
</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的数据绑定将数据展示到页面
<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)=>{
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")
public Result getBusinessReportData() {
try {
Map<String, Object> data = reportService.getBusinessReportData();
return new Result(true, MessageConstant.GET_BUSINESS_REPORT_SUCCESS, data);
} catch (Exception e) {
e.printStackTrace();
return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL);
}
}
2.3.2 服务接口
在health_interface工程中创建ReportService服务接口并声明getBusinessReport方法
package com.heiheihei.service.impl;
import com.alibaba.dubbo.config.annotation.Service;
import com.heiheihei.dao.MemberDao;
import com.heiheihei.dao.OrderDao;
import com.heiheihei.service.MemberService;
import com.heiheihei.service.ReportService;
import com.heiheihei.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;
/**
* 运营数据统计服务
*
* @author 嘿嘿嘿1212
* @version 1.0
* @date 2019/10/26 21:04
*/
@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 {
//获取当前日期
String reportDate = DateUtils.parseDate2String(DateUtils.getToday());
//获取本周星期一的日期
String thisWeekMonday = DateUtils.parseDate2String(DateUtils.getThisWeekMonday());
//获取本月第一天的日期
String firstDay4ThisMonth = DateUtils.parseDate2String(DateUtils.getFirstDay4ThisMonth());
//今天新增会员数
Integer todayNewMember = memberDao.findMemberCountByDate(reportDate);
//总会员数
Integer totalMember = memberDao.findMemberTotalCount();
//本周新增会员数
Integer thisWeekNewMember = memberDao.findMemberCountAfterDate(thisWeekMonday);
//今日预约数
Integer todayOrderNumber = orderDao.findOrderCountByDate(reportDate);
//本周预约数
Integer thisWeekOrderNumber = orderDao.findOrderCountAfterDate(thisWeekMonday);
//本月预约数
Integer thisMonthOrderNumber = orderDao.findOrderCountAfterDate(firstDay4ThisMonth);
//今日到诊数
Integer todayVisitsNumber = orderDao.findVisitsCountByDate(reportDate);
//本周到诊数
Integer thisWeekVisitsNumber = orderDao.findVisitsCountAfterDate(thisWeekMonday);
//本月到诊数
Integer thisMonthVisitsNumber = orderDao.findVisitsCountAfterDate(firstDay4ThisMonth);
//热门套餐(取前4)
List<Map> hotSetmeal = orderDao.findHotSetmeal();
//拼装数据
Map<String, Object> map = new HashMap<>();
map.put("reportDate", reportDate);
map.put("todayNewMember", todayNewMember);
map.put("totalMember", totalMember);
map.put("thisWeekNewMember", thisWeekNewMember);
map.put("todayOrderNumber", todayOrderNumber);
map.put("thisWeekOrderNumber", thisWeekOrderNumber);
map.put("thisMonthOrderNumber", thisMonthOrderNumber);
map.put("todayVisitsNumber", todayVisitsNumber);
map.put("thisWeekVisitsNumber", thisWeekVisitsNumber);
map.put("thisMonthVisitsNumber", thisMonthVisitsNumber);
map.put("hotSetmeal", hotSetmeal);
return map;
}
}
2.3.4 Dao接口
在OrderDao和MemberDao中声明相关统计查询方法
package com.heiheihei.dao;
import com.heiheihei.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.heiheihei.dao;
import com.github.pagehelper.Page;
import com.heiheihei.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：
<!‐‐根据日期统计预约数‐‐>
<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>
<!‐‐热门套餐，查询前4条‐‐>
<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：
<!‐‐根据日期统计会员数，统计指定日期之前的会员数‐‐>
<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模板文件，在这个模板文件中提前将表格的结构和样式设置好，我们的程序只需要读取这个文件并在文件中的相应位置写入具体的值就可以了。
3.3 完善页面
在report_business.html页面提供导出按钮并绑定事件
<div class="excelTitle" >
<el‐button @click="exportExcel">导出Excel</el‐button>运营数据统计 </div>
<script>
methods:{
//导出Excel报表
exportExcel(){
window.location.href = '/report/exportBusinessReport.do';
}
}
</script>
3.4 后台代码
在ReportController中提供exportBusinessReport方法，基于POI将数据写入到Excel中并通过输出流下载到客户端
/**
* 生成运营excel报表
* @param request
* @param response
* @return
*/
@RequestMapping("/exportBusinessReport")
public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response) {
try {
Map<String, Object> data = reportService.getBusinessReportData();
//获取当前日期
String reportDate = (String) data.get("reportDate");
//今天新增会员数
Integer todayNewMember = (Integer) data.get("todayNewMember");
//总会员数
Integer totalMember = (Integer) data.get("totalMember");
//本周新增会员数
Integer thisWeekNewMember = (Integer) data.get("thisWeekNewMember");
//本月新增会员数
Integer thisMonthNewMember = (Integer) data.get("thisMonthNewMember");
//今日预约数
Integer todayOrderNumber = (Integer) data.get("todayOrderNumber");
//本周预约数
Integer thisWeekOrderNumber = (Integer) data.get("thisWeekOrderNumber");
//本月预约数
Integer thisMonthOrderNumber = (Integer) data.get("thisMonthOrderNumber");
//今日到诊数
Integer todayVisitsNumber = (Integer) data.get("todayVisitsNumber");
//本周到诊数
Integer thisWeekVisitsNumber = (Integer) data.get("thisWeekVisitsNumber");
//本月到诊数
Integer thisMonthVisitsNumber = (Integer) data.get("thisMonthVisitsNumber");
//热门套餐(取前4)
List<Map> hotSetmeal = (List<Map>) data.get("hotSetmeal");
String template = request.getSession().getServletContext().getRealPath("template") + File.separator + "report_template.xlsx";
XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File(template)));
XSSFSheet sheet = excel.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 setmealCount = (Long) map.get("setmeal_count");
BigDecimal proportion = (BigDecimal) map.get("proportion");
row = sheet.getRow(rowNum++);
row.getCell(4).setCellValue(name);
row.getCell(5).setCellValue(setmealCount);
row.getCell(6).setCellValue(proportion.doubleValue());
}
ServletOutputStream out = response.getOutputStream();
//设置文件类型
response.setContentType("application/vnd.ms-excel");
//设置以附件形式进行下载
response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
excel.write(out);
out.flush();
out.close();
excel.close();
return null;
} catch (Exception e) {
e.printStackTrace();
return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL, null);
}
}
注意:BigDecimal 是Java在java.math包中提供的API类，用来对超过16位有效位的数进行精确的运算。具体详细百科
注意
在实际开发中,数据量是巨大的,并不会进行实时统计
- 通过Quartz或者Spring schedule定时任务进行insert调度
- 通过mysql的事件触发存储过程进行统计
- 以上两种综合（定时器+mybatis+存储过程调度）
在进行空闲时统计存储到统计表内存储统计数据.
扫一扫
1008
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
