最近分组,老大对dm平台组做了新的规划,对于平台的首页,想要展示设备的一系列统计。
总共包括6个部分,实现类似于下图中的首页,老大从网上的找的图,这是初始需求。6个部分包括:
1、总设备数
2、设备已连接数(缓存)
3、按类型分类
4、按版本分类(版本)
5、一周内,活跃设备数统计
6、一周内,异常设备数统计
1. 总设备数
单表查询,Mytabis自动生成表mapper的时候,xml里会有统计count的方法。
2. 设备已连接数
设备已连接数是从ppcp缓存中拿出来的数据,调用getConnectMapCache()方法,可得到Map,取其size,即为已连接数量。由于用的是平台封装项目,暂不详述。
3. 按类型分类
Mysql:
多表查询,sql语句如下:
select t.type device_type ,COUNT(d.model_id) count
from t_device_model t , t_device d
where 1=1
and t.id = d.model_id
group by t.type
查询结果如下:
Dao层:
定义了VO类,接收查询结果的一条记录:
public class ReportVO implements Serializable{
private String deviceType;//设备类型
private Integer count;//统计的数量
public Integer getCount() {
return count;
}
public void setCount(Integer count) {
this.count = count;
}
public String getDeviceType() {
return deviceType;
}
public void setDeviceType(String deviceType) {
this.deviceType = deviceType;
}
}
返回List列表,List内是实体类VO,接口方法如下:
public interface ReportDeviceClassMapper {
List<ReportVO> reportDeviceByType();
}
Mapper.xml如下:
<mapper namespace="com.payment.dm.model.mapper.ext.ReportDeviceClassMapper">
<resultMap id="BaseResultMap" type="com.payment.dm.model.vo.ReportVO">
<result column="device_type" jdbcType="VARCHAR" property="deviceType"/>
<result column="count" jdbcType="INTEGER" property="count"/>
</resultMap>
<select id="reportDeviceByType" resultMap="BaseResultMap">
select t.type device_type ,COUNT(d.model_id) count
from t_device_model t , t_device d
where 1=1
and t.id = d.model_id
group by t.type
</select>
</mapper>
对应的service
public List<ReportVO> reportDeviceByType() {
List<ReportVO> reportVOs = reportDeviceClassMapper.reportDeviceByType();
logger.info("[ReportDeviceService] [reportDeviceByType] query device by type, response = {}", reportVOs);
return reportVOs;
}
Service层:
调用dao层的service方法,返回Response类型的结果,方便前台解析
@Override
public Response<List<ReportVO>> reportDeviceByType() {
List<ReportVO> reportVOs = reportDeviceService.reportDeviceByType();
logger.info("[ReportServiceImpl] [reportDeviceByType] query device by type, reportVOs = {}", reportVOs);
return Response.OK(reportVOs);
}
Controller层:
Controller:
@RequestMapping("/device/report")
public class ReportController extends BaseController{
@Autowired
private DeviceReportComponent deviceReportComponent;
/**
* query device report by type
*
* @return
*/
@RequestMapping(value = "/type", method = RequestMethod.GET)
public String reportDeviceByType() {
logger.info("[ReportController] [reportDeviceByType] report divice by type!");
return deviceReportComponent.reportDeviceByType();
}
}
component:
public String reportDeviceByType() {
logger.info("[QueryController] [reportDeviceByType] report divice by type!");
Response<List<ReportVO>> response = reportService.reportDeviceByType();
return ResponseUtil.returnResponse(response.getEntity());
}
前端:
前端链接到后台接口:/device/report/type
{"code":200,
"data":
[{"count":16601,"deviceType":"1"},
{"count":6433,"deviceType":"2"},
{"count":2183,"deviceType":"3"},
{"count":29046,"deviceType":"4"}],
"message":"SUCCESS"}
再将type的1234解析成对应的类型名称,放到对应饼图,即可。页面如下:
4. 按版本分类
按软件版本分类的设备数,也是从ppcp服务器缓存中取数据。
Map<String, Map<String, Object>> versionCache = ppcpDeviceVersionCache.getAllDeviceVersionCache();
从缓存取出数据格式为Map:Map<设备号,Map<设备大类型,设备类型版本>>
{12345={system=1.7, ppbutler=2.14, uboot=1.0}, 98765={system=1.7, ppbutler=2.13, uboot=1.0}}
最后想要传给前端的格式为:Map<设备大类型,Map<设备类型版本,设备数量>>
{system={1.7=2}, ppbutler={2.14=1, 2.13=1}, uboot={1.0=2}}
在这里的格式转换,纠结了很长的时间,要感谢我的给力同事zhen,轻而易举解决了我的困扰,膜拜~~
问题解决过程及解析请移步博文:
【Map】Map类型和格式设计(工作需要)
http://blog.csdn.net/xiaoxiaolove613/article/details/73289513
代码如下:
Controller层:
component:
public String reportDeviceByVersion() {
logger.info("[QueryController] [reportDeviceByVersion] report divice by version!");
Map<String, Map<String, Object>> versionCache = ppcpDeviceVersionCache.getAllDeviceVersionCache();
logger.info("[QueryController] [getAllDeviceVersionCache] get version cache,versionCache={}",versionCache);
Map<String,Map<String,Integer>> result = new HashMap<>();
for (Map<String,Object> map : versionCache.values()) {
for (String key : map.keySet()) {
if (!result.containsKey(key)) {
result.put(key,new HashMap<>());
}
Map<String, Integer> map1 = result.get(key);
String k = String.valueOf(map.get(key));
if (!map1.containsKey(k)) {
map1.put(k,1);
} else {
map1.put(k,map1.get(k)+1);
}
}
}
return ResponseUtil.returnResponse(result);
}
前端:
前端链接到后台接口:/device/report/version
{"code":200,
"data":{"system":{"1.7":2},
"ppbutler":{"2.13\n":1,"2.14\n":1},
"uboot":{"1.0\n":2}},
"message":"SUCCESS"}
前端解析数据,按设备类型分类,并放到对应饼图。页面如下:
5. 一周内,活跃设备数统计
从数据库查询出数据,到后台代码,再到前端页面,中间的日期格式转换,以及7天数据中有缺失日期时应该怎么处理,着实为难了我好一阵子。中间也请教了好多人,还有网上的各种资料查询,好在有给力zhen的帮助,最后终于在一个乌七八黑的夜晚解决了这个“大”bug。具体问题解决过程及解析请移步博文:
【mysql】查询前7天的数据统计(解决日期不连续问题)
http://blog.csdn.net/xiaoxiaolove613/article/details/73251181
实现代码如下:
MySQL:
SELECT
COUNT(DISTINCT device_serial_num) device_count,
DATE_FORMAT(created_time,'%Y-%m-%d') date,
DAYOFWEEK(created_time)-1 week_day
FROM
sp_user_order
WHERE
pay_fee > 0
and created_time < CURDATE() and created_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
GROUP BY
date
Dao层:
VO:
public class ReportWeekVO implements Serializable{
private String date;//日期
private Integer week;//周几
private Integer count;//设备数
public Integer getCount() {
return count;
}
public void setCount(Integer count) {
this.count = count;
}
public Integer getWeek() {
return week;
}
public void setWeek(Integer week) {
this.week = week;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
}
Mapper:
public interface ReportDeviceWeekMapper {
List<ReportWeekVO> activeDeviceWeek();
}
Mapper.xml
<mapper namespace="com.payment.dm.model.mapper.ext.ReportDeviceWeekMapper">
<resultMap id="BaseResultMap" type="com.payment.dm.model.vo.ReportWeekVO">
<result column="date" jdbcType="VARCHAR" property="date"/>
<result column="device_count" jdbcType="INTEGER" property="count"/>
<result column="week_day" jdbcType="INTEGER" property="week"/>
</resultMap>
<select id="activeDeviceWeek" resultMap="BaseResultMap">
SELECT
COUNT(DISTINCT device_serial_num) device_count,
DATE_FORMAT(created_time,'%Y-%m-%d') date,
DAYOFWEEK(created_time)-1 week_day
FROM
sp_user_order
WHERE
pay_fee > 0
and created_time < CURDATE() and created_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
GROUP BY
date
</select>
</mapper>
service:
public List<ReportWeekVO> activeDeviceWeek() {
List<ReportWeekVO> reportWeekVOs = reportDeviceWeekMapper.activeDeviceWeek();
logger.info("[ReportDeviceService] [activeDeviceWeek] query active device in week, response = {}", JSON.toJSONString(reportWeekVOs));
List<ReportWeekVO> newReportWeekVOs = new ArrayList<>();
//获取当天日期
SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 1; i < 8; i++) {
ReportWeekVO newReportWeekVO = new ReportWeekVO();
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DATE, -i);
Date monday = calendar.getTime();
String preMonday = dateFormater.format(monday);
newReportWeekVO.setDate(preMonday);
int intWeek = calendar.get(Calendar.DAY_OF_WEEK) - 1;
newReportWeekVO.setWeek(intWeek);
newReportWeekVO.setCount(0);
newReportWeekVOs.add(newReportWeekVO);
}
logger.info("[ReportDeviceService] [activeDeviceWeek] newReportWeekVOs = {}", JSON.toJSONString(newReportWeekVOs));
for (int i = 0; i < newReportWeekVOs.size(); i++) {
for (int j = 0; j < reportWeekVOs.size(); j++) {
if (newReportWeekVOs.get(i).getDate().equals(reportWeekVOs.get(j).getDate())) {
newReportWeekVOs.get(i).setCount(reportWeekVOs.get(j).getCount());
}
}
}
logger.info("[ReportDeviceService] [activeDeviceWeek] newReportWeekVOs = {}", JSON.toJSONString(newReportWeekVOs));
return newReportWeekVOs;
}
Service层:
@Override
public Response<List<ReportWeekVO>> activeDeviceWeek() {
List<ReportWeekVO> reportWeekVOs = reportDeviceService.activeDeviceWeek();
logger.info("[ReportServiceImpl] [activeDeviceWeek] query active device in week, reportWeekVOs = {}", reportWeekVOs);
return Response.OK(reportWeekVOs);
}
Controller层:
Controller:
/**
* query device active num in a week
*
* @return
*/
@RequestMapping(value = "/active", method = RequestMethod.GET)
public String activeDeviceWeek() {
logger.info("[ReportController] [reportDeviceByVersion] query active device in week!");
return deviceReportComponent.activeDeviceWeek();
}
component:
public String activeDeviceWeek() {
logger.info("[QueryController] [activeDeviceWeek] report active divice in weeks!");
Response<List<ReportWeekVO>> response = reportService.activeDeviceWeek();
return ResponseUtil.returnResponse(response.getEntity());
}
前端:
{"code":200,
"data":[{"count":4,"date":"2017-06-14","week":3},
{"count":8,"date":"2017-06-13","week":2},
{"count":4,"date":"2017-06-12","week":1},
{"count":0,"date":"2017-06-11","week":0},
{"count":0,"date":"2017-06-10","week":6},
{"count":4,"date":"2017-06-09","week":5},
{"count":8,"date":"2017-06-08","week":4}],
"message":"SUCCESS"}
6. 一周内,异常设备数统计
类似于活跃设备数统计,除了sql有不同。
MySQL:
SELECT
a.dateconn date,
count(a.deviceid) device_count,
DAYOFWEEK(a.dateconn) - 1 week_day
FROM
(
SELECT
COUNT(1) concount,
DATE_FORMAT(connect_time, '%Y-%m-%d') dateconn,
device_id deviceid
FROM
t_connect_history
WHERE
connect_time < CURDATE()
AND connect_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
GROUP BY
dateconn,
device_id
) a
WHERE
a.concount >= 5
GROUP BY
date
Mapper:
List<ReportWeekVO> excepDeviceWeek();
Mapper.xml:
<select id="excepDeviceWeek" resultMap="BaseResultMap">
SELECT
a.dateconn date,
count(a.deviceid) device_count,
DAYOFWEEK(a.dateconn) - 1 week_day
FROM
(
SELECT
COUNT(1) concount,
DATE_FORMAT(connect_time, '%Y-%m-%d') dateconn,
device_id deviceid
FROM
t_connect_history
WHERE
connect_time < CURDATE()
AND connect_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)
GROUP BY
dateconn,
device_id
) a
WHERE
a.concount >= 5
GROUP BY
date
</select>
前端:
{"code":200,
"data":[{"count":26,"date":"2017-06-14","week":3},
{"count":20,"date":"2017-06-13","week":2},
{"count":19,"date":"2017-06-12","week":1},
{"count":0,"date":"2017-06-11","week":0},
{"count":0,"date":"2017-06-10","week":6},
{"count":22,"date":"2017-06-09","week":5},
{"count":37,"date":"2017-06-08","week":4}],
"message":"SUCCESS"}
7、首页展示
至此,设备首页大功告成,页面如下。(由于首页页面太大,只截取上半部分)后续的细节工作就是,从首页点击可以进到设备管理页面。
8、总结
总结来看,设备首页统计,有几个需要注意的小点:
格式转换
1、时间格式(Date型和String型)
2、List和Map类型转换
3、后台数据和前台数据转换