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 { default PageResult selectPage(MesDcRecordPageReqVO reqVO) { return selectPage(reqVO, new LambdaQueryWrapperX() .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() .eq(MesDcRecordDO::getDeviceId, deviceId) .eq(MesDcRecordDO::getBizId, bizId)); } default MesDcRecordDO selectLastByDeviceId(Long deviceId) { return selectOne(new LambdaQueryWrapperX() .eq(MesDcRecordDO::getDeviceId, deviceId) .orderByDesc(MesDcRecordDO::getId) .last("LIMIT 1")); } default Long selectCountByDeviceId(Long deviceId) { return selectCount(MesDcRecordDO::getDeviceId, deviceId); } /** * 按设备分组统计采集数量、重量、记录条数(不含已作废记录) */ default List> selectDeviceSummary(MesDcStatisticsReqVO reqVO) { QueryWrapper 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> selectTotals(MesDcStatisticsReqVO reqVO) { QueryWrapper 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> 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 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 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()); } } }