<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nb.easy.easyframe.module.trace.dao.StatisticMapper">
<!--获取element组件总数-->
<select id="selectElementSum" resultType="Long">
SELECT COUNT(1) FROM trace_element
</select>
<!--获取battery组件总数-->
<select id="selectBatterySum" resultType="Long">
SELECT COUNT(1) FROM trace_battery_qty
</select>
<!--获取element组件数量-->
<select id="selectElementTotal" resultType="Long">
SELECT COUNT(1) FROM trace_element WHERE source = #{source}
</select>
<!--获取battery组件数量-->
<select id="selectBatteryTotal" resultType="Long">
SELECT COUNT(1) FROM trace_battery_qty WHERE cjbh = #{cjbh}
</select>
<!--公共结果映射集-->
<resultMap id="monthTotal" type="com.nb.easy.easyframe.module.trace.vo.StatisticMonthTotal">
<result property="month" column="month_ele"/>
<result property="monthTotal" column="month_total"/>
</resultMap>
<!--组件每月-->
<!--获取指定年份的所有组件数量按月分组并按月份顺序排序-->
<!--DATE_FORMAT(date,'%m')——日期格式 '%m'月 '%Y'年-->
<select id="selectElementMonthTotal" resultMap="monthTotal">
SELECT DATE_FORMAT(date,'%m') AS month_ele,
COUNT(1) AS month_total
FROM trace_element
WHERE DATE_FORMAT(date,'%Y') = #{year} AND source = #{source}
GROUP BY month_ele
ORDER BY month_ele ASC;
</select>
<!--电池片每月-->
<select id="selectBatteryMonthTotal" resultMap="monthTotal">
SELECT DATE_FORMAT(date,'%m') month_ele,
COUNT(1) AS month_total
FROM trace_battery_qty
WHERE DATE_FORMAT(date,'%Y') = #{year} AND cjbh = #{cjbh}
GROUP BY month_ele
ORDER BY month_ele ASC;
</select>
</mapper>
package com.nb.easy.easyframe.module.trace.service.serviceImpl;
import com.nb.easy.easyframe.framework.common.pojo.CommonResult;
import com.nb.easy.easyframe.framework.common.pojo.PageResult;
import com.nb.easy.easyframe.module.system.controller.admin.dict.vo.data.DictDataPageReqVO;
import com.nb.easy.easyframe.module.system.dal.dataobject.dict.DictDataDO;
import com.nb.easy.easyframe.module.system.service.dict.DictDataService;
import com.nb.easy.easyframe.module.trace.dao.StatisticMapper;
import com.nb.easy.easyframe.module.trace.service.StatisticService;
import com.nb.easy.easyframe.module.trace.vo.MonthResult;
import com.nb.easy.easyframe.module.trace.vo.StatisticMonthTotal;
import com.nb.easy.easyframe.module.trace.vo.StatisticResult;
import com.nb.easy.easyframe.module.trace.vo.StatisticTotal;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.*;
@Service
public class StatisticServiceImpl implements StatisticService {
@Resource
private StatisticMapper statisticMapper;
@Resource
private DictDataService dictDataService;
private CommonResult commonResult = new CommonResult();
//组件数量统计
@Override
public CommonResult<StatisticResult> getElementTotal() {
return commonResult.setData(commonTotal("trace_lot_factory_type")).setMsg("组件数量查询成功");
}
//电池片数量统计
@Override
public CommonResult<StatisticResult> getBatteryTotal() {
return commonResult.setData(commonTotal("trace_battery_factory_type")).setMsg("电池片数量查询成功");
}
/**
* ***已测通
* 扇形统计公共方法
* @auther durunwu
*/
public StatisticResult commonTotal(String dictType){
List<StatisticTotal> list = new ArrayList<>();
//获取标签label和字典键值value
PageResult<DictDataDO> pageResul = dictDataService.getDictDataPage(new DictDataPageReqVO().setDictType(dictType));
//根据组件类型查询 组件表或电池表的组件总数
Long sum = 0L;
if (dictType.equals("trace_lot_factory_type")){
sum = statisticMapper.selectElementSum();
}else if (dictType.equals("trace_battery_factory_type")){
sum = statisticMapper.selectBatterySum();
}
//将标签label和字典键值value存入Map
Long elementSum = sum;
pageResul.getList().forEach(battery -> {
//字典键值:2231,2232,2233
String source = battery.getValue();
//total返回查询的组件数量
Long total = 0L;
//根据字典类型去查询
if (dictType.equals("trace_lot_factory_type")){
total = statisticMapper.selectElementTotal(source);
}else if (dictType.equals("trace_battery_factory_type")){
total = statisticMapper.selectBatteryTotal(Integer.parseInt(source));
}
//计算百分比
java.text.NumberFormat numberFormat = java.text.NumberFormat.getInstance();
//StatisticTotal实体类存储:字典标签,字典键值,字典数量,百分比
StatisticTotal elementTotal = new StatisticTotal();
//设置精确到小数点后2位
numberFormat.setMaximumFractionDigits(2);
if (total != 0L){
String totalFirstPercentage = numberFormat.format((double) total / (double) elementSum * 100);
elementTotal
.setFactoryName(battery.getLabel())
.setFactoryType(source)
.setNum(total.toString())
.setPercent(totalFirstPercentage + "%");
list.add(elementTotal);
}else {
commonResult.setMsg("参数不能为0");
}
});
return new StatisticResult().setList(list).setTotal(elementSum.toString());
}
//组件每月
@Override
public CommonResult getElementMonthTotal() {
List<List<MonthResult>> result = commonMonthTotal("trace_lot_factory_type", 2021);
return commonResult.setData(result).setMsg("组件每月查询成功");
}
//电池片每月
@Override
public CommonResult getBatteryMonthTotal() {
List<List<MonthResult>> result = commonMonthTotal("trace_battery_factory_type", 2021);
return commonResult.setData(result).setMsg("电池片每月查询成功");
}
/**
* 折线图统计公共方法
* @auther durunwu
*/
public List<List<MonthResult>> commonMonthTotal(String dictType,Integer year){
//校验
if (year == 0 || "".equals(dictType) || dictType == null || dictType.length() == 0) commonResult.setMsg("内容不能为空");
//1.厂集合,用于存储所有工厂的数据
List<List<MonthResult>> factoryList = new ArrayList<>();
//切忌List<List<MonthResult>> factoryList = null;不用这样写,不然报空指针异常
//用于封住数据库结果集
List<StatisticMonthTotal> elementMonthList = new ArrayList<>();
//获取字典信息
PageResult<DictDataDO> pageResul = dictDataService.getDictDataPage(new DictDataPageReqVO().setDictType(dictType));
/**
* 两层循环 : 外层根据工厂类型遍历,内层根据月份遍历
*/
//根据工厂类型遍历,一个工厂匹配一条折线
for (DictDataDO dataDO : pageResul.getList()) {
//DictDataDO代表每一个单独的工厂
if (dictType.equals("trace_lot_factory_type")){
//sql查询结果字段:month月份,total组件数量,【每月的月份和组件数】作为一组对象封装进List<StatisticMonthTotal>
//elementMonthList 该工厂12个月的月份与组件数量
//dataDO.getValue() 该工厂的字典值 2231、2232、2233
elementMonthList = statisticMapper.selectElementMonthTotal(year.toString(), dataDO.getValue());
}else if (dictType.equals("trace_battery_factory_type")){
elementMonthList = statisticMapper.selectBatteryMonthTotal(year.toString(), dataDO.getValue());
}
//2.年集合,存储该工厂12个月的数据
List<MonthResult> yearResult = new ArrayList<>();
//遍历12个月份
for (int i = 1; i <= 12 ; i++) {
//3.月集合,用于存储该工厂该月的组件数量,只存储一个月的,
MonthResult monthResult = new MonthResult();
monthResult.setMonth(i + "月").setFactoryName(dataDO.getLabel());
//遍历当月的月份与组件数
for (StatisticMonthTotal monthTotal : elementMonthList){
if (i == Integer.parseInt(monthTotal.getMonth())){
monthResult.setMonthTotal(monthTotal.getMonthTotal());
break;
}
}
//如果该厂该月没有数据,则返回0
if (monthResult.getMonthTotal() == null){
monthResult.setMonthTotal("0");
}
//把12个月的数据放入monthResultList(存放年数据)
yearResult.add(monthResult);
}
//把该厂该年度的数据存入厂集合
factoryList.add(yearResult);
}
return factoryList;
}
}