本文档详细介绍了如何利用SpringBoot和Mybatis框架构建监控平台的日、周、月和年报表功能。通过数据库设计、逻辑处理和代码实现，阐述了日报、周报、月报的查询策略，特别强调了在数据不完整情况下的处理方法，并给出了部分关键代码示例。
springboot+mybatis开发日、周、月、年报功能。因为公司平台是监控平台产品，所以就已监控平台来进行说明。
前提：因为我处理的是如果设备运行或者不正常，则没有数据产生，因此没法正常用顺序一一对应去处理，只能通过规则去匹配正确地查找数据。如果你们数据中一定会产生数据，则可以通过其他快速的方法更好更快地去做好报表。由于涉及到公司，所有优化的代码没有放出来，这个是v1版本。优化版本包括了用stream流处理数据和ConcurrentHashMap。
1、数据库设计(数据库中包含很多设备，每个设备都用自己唯一的ID)，设置日表，日表包含了每个设备的id、今天日期时间（例如2022-03-10 00:00:00）、平均值、每个小时的数据。月表设计2022_03：每天过去的日表数据，都会统计一天的平均值加到月表，月表包含了id、时间、过去日期的平均值。同理，年表设计包含设备id、每个月的时间、平均值。
2、基于上面数据库的设计，就可以进行开发日、周、月、年报了。
3、逻辑：日报的开发：判断选择的是否是今天，如果是今天，则去数据库中查找日表，如果查询的数据如果不是今天、则需要判断查询的年月，找到对应的月表，在月表中查询该天的信息。
周报的开发：可以结合前端My97DatePicker开发，之前文章有，就不多说了。如果查询的周数包含今天，则需要去数据库中查询日表，如果不包含今天，则需要判断该查询周的第一天和最后一天的月份，如果是同一个月，则只需要查询一个月表数据就行，否则需要查询第一天和最后一天的月表。
月表开发：如果查询的是当天月份，则需要查询当天的数据和当月的数据。如果查询的是其他月份，则只需要去查询其他月份的数据即可。
年表设计：直接去查询年表的数据即可
4、代码的逻辑:日报：直接查询数据即可，但是需要定义日报包含的属性的java类。
周报（实际上查询的是每天00:00:00的平均值，在月表中或者在日表中）：定义日报包含的属性的java类，查询完所有的日报数据(list集合)，创建Hashtable，用Hashtable的健作为设备id号的匹配值，如果健不存在，则和查询周数的具体日期时间比较，相对应的日期的值赋给周报属性w1、w2、w3、w4、w5、w6、w7。如果健值存在，也需要和时间对比赋值。
月报：月报的设计和周报的原理相同，只不过月报的属性是每个月天
年报：年报的设计跟日报的差不多
5、日报开发代码:
//报表的类型判断
if(reporttype==0){
map.put("currentDateValue",currentDateValue);
currentDateValue = currentDateValue.substring(0,4)+"-"+currentDateValue.substring(5,7)+"-"+currentDateValue.substring(8,10);
if(isTodayFlag){
map.put("tableName","mstattoday");
map.put("siteIds", siteIdsList);
map.put("baseMeterIds", baseMeterIdsList);
map.put("sDateTime", currentDateValue+" 00:00:00");
//查询报表数据总数
Integer total = reportService.getMeterDayForReportCount(map);
int realTotal = gridPage.getRealTotal(total, gridPage.getRows());
int thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
int page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
map.put("page",page);
map.put("pageSize", gridPage.getRows());
//查询具体数据，DayMeterValue是日报的java实体
List<DayMeterValue> dmv = reportService.getMeterDayForReport(map);
if(baseMeterIdsList.size()>0&&dmv.size()==0){
total = reportService.getMeterNotDataCountForReport(map);
//gird分页处理，因为前台是要分页同时要选择每页选择的页数的
realTotal = gridPage.getRealTotal(total, gridPage.getRows());
thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
dmv = reportService.getMeterNotDataForReport(map);
}
//返回gird组件需要显示的信息
grid.put("rows", dmv);
grid.put("page", thispage);
grid.put("records", total);
grid.put("total", realTotal);
}else{
String dataBaseName = "slb_met";
String tableName = "";
if(!currentDateValue.split("-")[1].equals("")){
tableName = "mstat_"+ currentDateValue.split("-")[0]+currentDateValue.split("-")[1];
}
currentDateValue.split("-");
//当查询的不是为今天时，则去去找月表存不存在
Integer tablesCount = reportService.getTablesCountByTableName(dataBaseName,tableName);
//月表存在时才查询
if(tablesCount>0){
//查询月表中的日数据
tableName = dataBaseName+"."+tableName;
map.put("tableName",tableName);
map.put("siteIds", siteIdsList);
map.put("baseMeterIds", baseMeterIdsList);
map.put("sDateTime", currentDateValue+" 00:00:00");
map.put("pageSize", gridPage.getRows());
Integer total = reportService.getMeterDayForReportCount(map);
int realTotal = gridPage.getRealTotal(total, gridPage.getRows());
int thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
int page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
map.put("page", page);
List<DayMeterValue> dmv = reportService.getMeterDayForReport(map);
grid.put("rows", dmv);
grid.put("page", thispage);
grid.put("records", total);
grid.put("total", realTotal);
}else {
··//对没有数据进行处理，当完全不存在数据时，查询的类型一样能出来，只是没有数据
map.put("siteIds", siteIdsList);
map.put("baseMeterIds", baseMeterIdsList);
Integer total = reportService.getMeterNotDataCountForReport(map);
int realTotal = gridPage.getRealTotal(total, gridPage.getRows());
int thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
int page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
map.put("page", page);
map.put("pageSize", gridPage.getRows());
map.put("currentDateValue",currentDateValue);
List<DayMeterValue> dmv = reportService.getMeterNotDataForReport(map);
grid.put("rows", dmv);
grid.put("page", thispage);
grid.put("records", total);
grid.put("total", realTotal);
}
}
mybatis日报xml的代码(我查询的是根据设备配置表的设备类型量去找数据的，这边跟具体业务有关).这里需要注意的点是$是不带数据类型，#是带上了数据类型
SELECT sm.name siteName, dm.name devName, cm.name metName,cm.unit unit,#{param.currentDateValue} date,
concat(mm.h0,':',cm.basemeterid) h0,concat(mm.h1,':',cm.basemeterid) h1,concat(mm.h2,':',cm.basemeterid) h2,concat(mm.h3,':',cm.basemeterid) h3,concat(mm.h4,':',cm.basemeterid) h4,
concat(mm.h5,':',cm.basemeterid) h5,concat(mm.h6,':',cm.basemeterid) h6,concat(mm.h7,':',cm.basemeterid) h7,concat(mm.h8,':',cm.basemeterid) h8,concat(mm.h9,':',cm.basemeterid) h9,
concat(mm.h10,':',cm.basemeterid) h10,concat(mm.h11,':',cm.basemeterid) h11,concat(mm.h12,':',cm.basemeterid) h12,concat(mm.h13,':',cm.basemeterid) h13,concat(mm.h14,':',cm.basemeterid) h14,
concat(mm.h15,':',cm.basemeterid) h15,concat(mm.h16,':',cm.basemeterid) h16,concat(mm.h17,':',cm.basemeterid) h17,concat(mm.h18,':',cm.basemeterid) h18,concat(mm.h19,':',cm.basemeterid) h19,
concat(mm.h20,':',cm.basemeterid) h20,concat(mm.h21,':',cm.basemeterid) h21,concat(mm.h22,':',cm.basemeterid) h22,concat(mm.h23,':',cm.basemeterid) h23
from slb_cfg.meter cm
inner join slb_cfg.site sm on cm.siteid=sm.siteid
inner join slb_cfg.device dm on cm.devid=dm.devid
LEFT JOIN slb_cfg.regright rr on rr.groupid = #{param.groupid} and rr.regid = sm.regid
LEFT JOIN slb_cfg.siteright sr on sr.groupid = #{param.groupid} and sr.access = 1 and sr.siteid = cm.siteid
LEFT JOIN ${param.tableName} mm on mm.m=cm.meterid and mm.t=#{param.sDateTime}
where (sr.siteid is not null or rr.regid is not null)
<if test="param.siteIds != null and param.siteIds.size > 0">
and cm.siteid in
<foreach collection="param.siteIds" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="param.baseMeterIds != null and param.baseMeterIds.size > 0">
and cm.basemeterid in
<foreach collection="param.baseMeterIds" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
order by siteName,devName,metName limit #{param.page},#{param.pageSize};
周报的代码：
//月表类型判断
if(reporttype==1){
map.put("siteIds", siteIdsList);
map.put("baseMeterIds", baseMeterIdsList);
//处理数据，获取年月
String [] s = startDate.split("年第");
if(s.length>1){
s[1]= s[1].substring(0,s[1].length()-1);
if(s[1].length()==1){
s[1] = "0"+s[1];
}
}
//jdk8获取周的日期函数，我这里的设计是从周一到周日的
WeekFields weekFields= WeekFields.ISO;
LocalDate now = LocalDate.now();
//输入你想要的年份和周数
LocalDate localDate = now.withYear(Integer.parseInt(s[0])).with(weekFields.weekOfYear(),Integer.parseInt(s[1]));
LocalDate localDate1 = localDate.with(weekFields.dayOfWeek(), 1L);
LocalDate localDate2 = localDate.with(weekFields.dayOfWeek(), 2L);
LocalDate localDate3 = localDate.with(weekFields.dayOfWeek(), 3L);
LocalDate localDate4 = localDate.with(weekFields.dayOfWeek(), 4L);
LocalDate localDate5 = localDate.with(weekFields.dayOfWeek(), 5L);
LocalDate localDate6 = localDate.with(weekFields.dayOfWeek(), 6L);
LocalDate localDate7 = localDate.with(weekFields.dayOfWeek(), 7L);
String week1= localDate1+" 00:00:00";
String week2= localDate2+" 00:00:00";
String week3= localDate3+" 00:00:00";
String week4= localDate4+" 00:00:00";
String week5= localDate5+" 00:00:00";
String week6= localDate6+" 00:00:00";
String week7= localDate7+" 00:00:00";
String monTableName1 = "";
String monTableName2 = "";
List<String> listDate1 = new ArrayList();
List<String> listDate2 = new ArrayList();
List<WeekMeterValue> wmv = null;
List<WeekMeterValue> wmv1 = null;
List<WeekMeterValue> wmv2 = null;
List<WeekMeterValue> allWeekList = new ArrayList<>();
String startDate1 = startDate +"("+ (localDate1+"").split("-")[1]+"."+(localDate1+"").split("-")[2]+"-"+ (localDate7+"").split("-")[1]+"."+(localDate7+"").split("-")[2]+")";
//判断是否是不同月的数据
if(localDate1.getMonth()==localDate7.getMonth()){
monTableName1 = "slb_met.mstat_"+(localDate1+"").split("-")[0]+(localDate1+"").split("-")[1];
listDate1.add(localDate1+" 00:00:00");
listDate1.add(localDate2+" 00:00:00");
listDate1.add(localDate3+" 00:00:00");
listDate1.add(localDate4+" 00:00:00");
listDate1.add(localDate5+" 00:00:00");
listDate1.add(localDate6+" 00:00:00");
listDate1.add(localDate7+" 00:00:00");
map.put("monTableName1", monTableName1);
map.put("listDate1", listDate1);
map.put("startDate", startDate1);
String dataBaseName = "slb_met";
String tableName = "mstat_"+(localDate1+"").split("-")[0]+(localDate1+"").split("-")[1];
Integer tablesCount = reportService.getTablesCountByTableName(dataBaseName,tableName);
if(tablesCount==1){
wmv = reportService.getMeterWeekForReport(map);
}
if(wmv!=null&&wmv.size()>0){
allWeekList.addAll(wmv);
wmv = null;
}
//查询完移除查询过的数据，这个
map.remove("monTableName1");
map.remove("listDate1");
}else {
monTableName1 = "";
monTableName2 = "slb_met.mstat_"+(localDate7+"").split("-")[0]+(localDate7+"").split("-")[1];
listDate1.add(localDate1+" 00:00:00");
if(localDate1.getMonth()==localDate2.getMonth()){
listDate1.add(localDate2+" 00:00:00");
}else {
listDate2.add(localDate2+" 00:00:00");
}
if(localDate1.getMonth()==localDate3.getMonth()){
listDate1.add(localDate3+" 00:00:00");
}else {
listDate2.add(localDate3+" 00:00:00");
}
if(localDate1.getMonth()==localDate4.getMonth()){
listDate1.add(localDate4+" 00:00:00");
}else {
listDate2.add(localDate4+" 00:00:00");
}
if(localDate1.getMonth()==localDate5.getMonth()){
listDate1.add(localDate5+" 00:00:00");
}else {
listDate2.add(localDate5+" 00:00:00");
}
if(localDate1.getMonth()==localDate6.getMonth()){
listDate1.add(localDate6+" 00:00:00");
}else {
listDate2.add(localDate6+" 00:00:00");
}
if(localDate1.getMonth()==localDate7.getMonth()){
listDate1.add(localDate7+" 00:00:00");
}else {
listDate2.add(localDate7+" 00:00:00");
}
//先对最早的月份数据进行处理，这样在排序中更好地查找数据
tableName = "mstat_"+ (localDate1+"").split("-")[0]+(localDate1+"").split("-")[1];
tablesCount = reportService.getTablesCountByTableName(dataBaseName,tableName);
if(tablesCount==1) {
wmv1 = reportService.getMeterWeekForReport(map);
}
if(wmv1!=null&&wmv1.size()>0){
allWeekList.addAll(wmv1);
wmv1 = null;
}
map.remove("monTableName1");
map.remove("listDate1");
}
map.put("monTableName2", monTableName2);
map.put("listDate2", listDate2);
map.put("startDate", startDate1);
String dataBaseName = "slb_met";
String tableName = "mstat_"+ (localDate7+"").split("-")[0]+(localDate7+"").split("-")[1];
Integer tablesCount = reportService.getTablesCountByTableName(dataBaseName,tableName);
if(tablesCount==1){
wmv2 = reportService.getMeterWeekForReport(map);
}
if(wmv2!=null&&wmv2.size()>0){
allWeekList.addAll(wmv2);
wmv2 = null;
}
map.put("listDate1", listDate1);
map.remove("monTableName2");
map.remove("listDate2");
monTableName1 = "slb_met.mstat_"+(localDate1+"").split("-")[0]+(localDate1+"").split("-")[1];
map.put("monTableName1", monTableName1);
map.put("startDate", startDate1);
monTableName1 = "";
monTableName2 = "";
String todayTableName = "";
for(String l:listDate1){
if(l.equals(now+" 00:00:00")){
todayTableName = "mstattoday";
}
}
if(listDate2.size()>0){
for(String l:listDate2){
if(l.equals(now+" 00:00:00")){
todayTableName = "mstattoday";
}
}
}
if(!(todayTableName.equals(""))){
map.put("todaTableName", todayTableName);
map.put("todayTime", now+" 00:00:00");
map.put("startDate", startDate1);
String dataBaseName = "slb_met";
String tableName = "mstattoday";
Integer tablesCount = reportService.getTablesCountByTableName(dataBaseName,tableName);
if(tablesCount==1){
wmv = reportService.getMeterWeekForReport(map);
if(wmv!=null&&wmv.size()>0){
allWeekList.addAll(wmv);
}
}
}
if(allWeekList.size()>0&&allWeekList!=null){
Hashtable<String,WeekMeterValue> ht = new Hashtable<>();
for(WeekMeterValue weekVal:allWeekList){
if(ht.containsKey(weekVal.getM())){
if(week1.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW1(weekVal.getV()+':'+weekVal.getBm());
}else if(week2.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW2(weekVal.getV()+':'+weekVal.getBm());
}else if(week3.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW3(weekVal.getV()+':'+weekVal.getBm());
}else if(week4.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW4(weekVal.getV()+':'+weekVal.getBm());
}else if(week5.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW5(weekVal.getV()+':'+weekVal.getBm());
}else if(week6.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW6(weekVal.getV()+':'+weekVal.getBm());
}else if(week7.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW7(weekVal.getV()+':'+weekVal.getBm());
}
}else {
ht.put(weekVal.getM(),weekVal);
if(week1.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW1(weekVal.getV()+':'+weekVal.getBm());
}else if(week2.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW2(weekVal.getV()+':'+weekVal.getBm());
}else if(week3.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW3(weekVal.getV()+':'+weekVal.getBm());
}else if(week4.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW4(weekVal.getV()+':'+weekVal.getBm());
}else if(week5.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW5(weekVal.getV()+':'+weekVal.getBm());
}else if(week6.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW6(weekVal.getV()+':'+weekVal.getBm());
}else if(week7.equals(weekVal.getT())){
ht.get(weekVal.getM()).setW7(weekVal.getV()+':'+weekVal.getBm());
}
}
}
List<WeekMeterValue> reportList = new LinkedList();
reportList.addAll(ht.values());
//获取所有的数据还需要对数据进行排序，这里忽略了
Integer total = reportList.size();
int realTotal = gridPage.getRealTotal(total, gridPage.getRows());
int thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
int page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
if(thispage<realTotal){
reportList = reportList.subList(page,page+gridPage.getRows());
}else if(thispage==realTotal){
reportList = reportList.subList(page,total);
}
grid.put("rows", reportList);
grid.put("page", thispage);
grid.put("records", total);
grid.put("total", realTotal);
}else{
//对查询不存在的数据进行过滤
group = getUserInfo(request).getGroupid();
map.put("groupid",group);
map.put("siteIds", siteIdsList);
map.put("baseMeterIds", baseMeterIdsList);
Integer total = reportService.getMeterNotDataCountForReport(map);
int realTotal = gridPage.getRealTotal(total, gridPage.getRows());
int thispage=gridPage.getPage();
if(thispage>realTotal){
thispage=realTotal;
}
int page= gridPage.getRows() * ((thispage==0?1:thispage)-1);
map.put("page", page);
map.put("pageSize", gridPage.getRows());
List<WeekMeterValue> dmv = reportService.getMeterNotDataForWeekReport(map);
grid.put("rows", dmv);
grid.put("page", thispage);
grid.put("records", total);
grid.put("total", realTotal);
}
}
mybatis周报的xml，只是供参考：
SELECT sm.name siteName, dm.name devName, cm.name metName,cm.unit unit,cm.meterid m,cm.basemeterid bm,mm.t t,mm.v v, #{param.startDate} date
from slb_cfg.meter cm
inner join slb_cfg.site sm on cm.siteid=sm.siteid
inner join slb_cfg.device dm on cm.devid=dm.devid
LEFT JOIN slb_cfg.regright rr on rr.groupid = #{param.groupid} and rr.regid = sm.regid
LEFT JOIN slb_cfg.siteright sr on sr.groupid = #{param.groupid} and sr.access = 1 and sr.siteid = cm.siteid
<if test="param.todaTableName != null and param.todaTableName!='' ">
LEFT JOIN ${param.todaTableName} mm on mm.m=cm.meterid and mm.t=#{param.todayTime}
</if>
<if test="param.monTableName1 != null and param.monTableName1!='' ">
LEFT JOIN ${param.monTableName1} mm on mm.m=cm.meterid and mm.t in
<foreach collection="param.listDate1" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="param.monTableName2 != null and param.monTableName2!='' ">
LEFT JOIN ${param.monTableName2} mm on mm.m=cm.meterid and mm.t in
<foreach collection="param.listDate2" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
where (sr.siteid is not null or rr.regid is not null)
<if test="param.siteIds != null and param.siteIds.size > 0">
and cm.siteid in
<foreach collection="param.siteIds" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="param.baseMeterIds != null and param.baseMeterIds.size > 0">
and cm.basemeterid in
<foreach collection="param.baseMeterIds" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
order by siteName,devName,metName;
月报和年报原理和日报和周报差不多，就不重复写代码了。主要理解Hashtable对周报的处理数据的原理就好了，这里代码没有用快速查找的方法进行周报和月报和年报进行数据处理，另外就是还可以用stream流处理查找和排序，有兴趣的可以自己处理一下。
扫一扫
1507
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
