package cn.iocoder.yudao.module.mes.dal.mysql.dc.record;
|
|
import cn.iocoder.yudao.framework.common.pojo.PageResult;
|
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
|
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
|
import cn.iocoder.yudao.module.mes.controller.admin.dc.record.vo.MesDcRecordPageReqVO;
|
import cn.iocoder.yudao.module.mes.controller.admin.dc.statistics.vo.MesDcStatisticsReqVO;
|
import cn.iocoder.yudao.module.mes.dal.dataobject.dc.record.MesDcRecordDO;
|
import cn.iocoder.yudao.module.mes.enums.dc.MesDcRecordStatusEnum;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import org.apache.ibatis.annotations.Mapper;
|
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* MES 数据采集记录 Mapper
|
*
|
* @author 超级管理员
|
*/
|
@Mapper
|
public interface MesDcRecordMapper extends BaseMapperX<MesDcRecordDO> {
|
|
default PageResult<MesDcRecordDO> selectPage(MesDcRecordPageReqVO reqVO) {
|
return selectPage(reqVO, new LambdaQueryWrapperX<MesDcRecordDO>()
|
.eqIfPresent(MesDcRecordDO::getDeviceId, reqVO.getDeviceId())
|
.likeIfPresent(MesDcRecordDO::getDeviceCode, reqVO.getDeviceCode())
|
.eqIfPresent(MesDcRecordDO::getWorkOrderId, reqVO.getWorkOrderId())
|
.likeIfPresent(MesDcRecordDO::getWorkOrderCode, reqVO.getWorkOrderCode())
|
.eqIfPresent(MesDcRecordDO::getProductId, reqVO.getProductId())
|
.eqIfPresent(MesDcRecordDO::getBatchCode, reqVO.getBatchCode())
|
.eqIfPresent(MesDcRecordDO::getStatus, reqVO.getStatus())
|
.betweenIfPresent(MesDcRecordDO::getRecordTime, reqVO.getRecordTime())
|
.betweenIfPresent(MesDcRecordDO::getCreateTime, reqVO.getCreateTime())
|
.orderByDesc(MesDcRecordDO::getRecordTime)
|
.orderByDesc(MesDcRecordDO::getId));
|
}
|
|
default MesDcRecordDO selectByDeviceIdAndBizId(Long deviceId, String bizId) {
|
return selectOne(new LambdaQueryWrapperX<MesDcRecordDO>()
|
.eq(MesDcRecordDO::getDeviceId, deviceId)
|
.eq(MesDcRecordDO::getBizId, bizId));
|
}
|
|
default MesDcRecordDO selectLastByDeviceId(Long deviceId) {
|
return selectOne(new LambdaQueryWrapperX<MesDcRecordDO>()
|
.eq(MesDcRecordDO::getDeviceId, deviceId)
|
.orderByDesc(MesDcRecordDO::getId)
|
.last("LIMIT 1"));
|
}
|
|
default Long selectCountByDeviceId(Long deviceId) {
|
return selectCount(MesDcRecordDO::getDeviceId, deviceId);
|
}
|
|
/**
|
* 按设备分组统计采集数量、重量、记录条数(不含已作废记录)
|
*/
|
default List<Map<String, Object>> selectDeviceSummary(MesDcStatisticsReqVO reqVO) {
|
QueryWrapper<MesDcRecordDO> wrapper = new QueryWrapper<>();
|
wrapper.select("device_id AS deviceId", "device_code AS deviceCode",
|
"MAX(device_name) AS deviceName",
|
"COALESCE(SUM(quantity), 0) AS totalQuantity",
|
"COALESCE(SUM(weight), 0) AS totalWeight",
|
"COUNT(*) AS recordCount");
|
appendStatisticsWhere(wrapper, reqVO);
|
wrapper.groupBy("device_id", "device_code");
|
wrapper.orderByDesc("totalQuantity");
|
return selectMaps(wrapper);
|
}
|
|
/**
|
* 统计汇总数量、重量、记录条数(不含已作废记录)
|
*/
|
default List<Map<String, Object>> selectTotals(MesDcStatisticsReqVO reqVO) {
|
QueryWrapper<MesDcRecordDO> wrapper = new QueryWrapper<>();
|
wrapper.select("COALESCE(SUM(quantity), 0) AS totalQuantity",
|
"COALESCE(SUM(weight), 0) AS totalWeight",
|
"COUNT(*) AS recordCount");
|
appendStatisticsWhere(wrapper, reqVO);
|
return selectMaps(wrapper);
|
}
|
|
/**
|
* 按天/小时统计采集趋势(不含已作废记录)
|
*
|
* @param reqVO 查询条件
|
* @param byHour 是否按小时分组
|
*/
|
default List<Map<String, Object>> selectTrend(MesDcStatisticsReqVO reqVO, boolean byHour) {
|
String periodExpression = byHour
|
? "DATE_FORMAT(record_time, '%Y-%m-%d %H:00')"
|
: "DATE_FORMAT(record_time, '%Y-%m-%d')";
|
QueryWrapper<MesDcRecordDO> wrapper = new QueryWrapper<>();
|
wrapper.select(periodExpression + " AS period",
|
"COALESCE(SUM(quantity), 0) AS totalQuantity",
|
"COALESCE(SUM(weight), 0) AS totalWeight",
|
"COUNT(*) AS recordCount");
|
appendStatisticsWhere(wrapper, reqVO);
|
wrapper.groupBy(periodExpression);
|
wrapper.orderByAsc(periodExpression);
|
return selectMaps(wrapper);
|
}
|
|
/**
|
* 拼接统计查询条件:排除已作废记录,可选设备、工单、时间范围
|
*/
|
private void appendStatisticsWhere(QueryWrapper<MesDcRecordDO> wrapper,
|
MesDcStatisticsReqVO reqVO) {
|
if (reqVO == null) {
|
wrapper.ne("status", MesDcRecordStatusEnum.CANCELED.getStatus());
|
return;
|
}
|
wrapper.ne("status", MesDcRecordStatusEnum.CANCELED.getStatus());
|
if (reqVO.getDeviceId() != null) {
|
wrapper.eq("device_id", reqVO.getDeviceId());
|
}
|
if (reqVO.getWorkOrderId() != null) {
|
wrapper.eq("work_order_id", reqVO.getWorkOrderId());
|
}
|
if (reqVO.getBeginTime() != null) {
|
wrapper.ge("record_time", reqVO.getBeginTime());
|
}
|
if (reqVO.getEndTime() != null) {
|
wrapper.le("record_time", reqVO.getEndTime());
|
}
|
}
|
|
}
|