package cn.iocoder.yudao.module.mes.dal.mysql.em.record;
|
|
import cn.iocoder.yudao.framework.common.pojo.PageResult;
|
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
|
import cn.iocoder.yudao.module.mes.controller.admin.em.record.vo.MesEmRecordPageReqVO;
|
import cn.iocoder.yudao.module.mes.dal.dataobject.em.record.MesEmRecordDO;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.math.BigDecimal;
|
import java.time.LocalDate;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* MES 能耗抄表记录 Mapper
|
*
|
* @author 超级管理员
|
*/
|
@Mapper
|
public interface MesEmRecordMapper extends BaseMapperX<MesEmRecordDO> {
|
|
default PageResult<MesEmRecordDO> selectPage(MesEmRecordPageReqVO reqVO) {
|
return selectPage(reqVO, new LambdaQueryWrapperX<MesEmRecordDO>()
|
.eqIfPresent(MesEmRecordDO::getMeterId, reqVO.getMeterId())
|
.likeIfPresent(MesEmRecordDO::getMeterName, reqVO.getMeterName())
|
.eqIfPresent(MesEmRecordDO::getEnergyType, reqVO.getEnergyType())
|
.eqIfPresent(MesEmRecordDO::getWorkshopId, reqVO.getWorkshopId())
|
.eqIfPresent(MesEmRecordDO::getSource, reqVO.getSource())
|
.betweenIfPresent(MesEmRecordDO::getRecordDate, reqVO.getRecordDate())
|
.orderByDesc(MesEmRecordDO::getRecordDate)
|
.orderByDesc(MesEmRecordDO::getId));
|
}
|
|
default MesEmRecordDO selectByMeterIdAndDate(Long meterId, LocalDate recordDate) {
|
return selectOne(new LambdaQueryWrapperX<MesEmRecordDO>()
|
.eq(MesEmRecordDO::getMeterId, meterId)
|
.eq(MesEmRecordDO::getRecordDate, recordDate));
|
}
|
|
default MesEmRecordDO selectLastByMeterId(Long meterId, LocalDate beforeDate, Long excludeId) {
|
return selectOne(new LambdaQueryWrapperX<MesEmRecordDO>()
|
.eq(MesEmRecordDO::getMeterId, meterId)
|
.lt(MesEmRecordDO::getRecordDate, beforeDate)
|
.ne(excludeId != null, MesEmRecordDO::getId, excludeId)
|
.orderByDesc(MesEmRecordDO::getRecordDate)
|
.orderByDesc(MesEmRecordDO::getId)
|
.last("LIMIT 1"));
|
}
|
|
default List<MesEmRecordDO> selectListByMeterIdAndDateRange(Long meterId, LocalDate begin, LocalDate end) {
|
return selectList(new LambdaQueryWrapperX<MesEmRecordDO>()
|
.eq(MesEmRecordDO::getMeterId, meterId)
|
.between(MesEmRecordDO::getRecordDate, begin, end));
|
}
|
|
/**
|
* 统计某个考核对象在指定区间内的用量
|
*
|
* @param objectType 0 全厂 / 1 车间 / 2 产线 / 3 设备
|
*/
|
@Select("""
|
<script>
|
SELECT COALESCE(SUM(usage_amount), 0)
|
FROM mes_em_record
|
WHERE deleted = 0
|
AND energy_type = #{energyType}
|
AND record_date BETWEEN #{begin} AND #{end}
|
<if test="objectType == 1">AND workshop_id = #{objectId}</if>
|
<if test="objectType == 2">AND line_id = #{objectId}</if>
|
<if test="objectType == 3">AND machinery_id = #{objectId}</if>
|
</script>
|
""")
|
BigDecimal sumUsage(@Param("energyType") Integer energyType,
|
@Param("objectType") Integer objectType,
|
@Param("objectId") Long objectId,
|
@Param("begin") LocalDate begin,
|
@Param("end") LocalDate end);
|
|
@Select("""
|
SELECT COALESCE(SUM(usage_amount), 0)
|
FROM mes_em_record
|
WHERE deleted = 0 AND meter_id = #{meterId} AND record_date BETWEEN #{begin} AND #{end}
|
""")
|
BigDecimal sumMeterUsage(@Param("meterId") Long meterId,
|
@Param("begin") LocalDate begin,
|
@Param("end") LocalDate end);
|
|
@Select("""
|
SELECT COALESCE(SUM(usage_amount), 0)
|
FROM mes_em_record
|
WHERE deleted = 0 AND energy_type = #{energyType} AND record_date BETWEEN #{begin} AND #{end}
|
""")
|
BigDecimal sumTypeUsage(@Param("energyType") Integer energyType,
|
@Param("begin") LocalDate begin,
|
@Param("end") LocalDate end);
|
|
default List<MesEmRecordDO> selectListByDateRange(LocalDate begin, LocalDate end) {
|
return selectList(new LambdaQueryWrapperX<MesEmRecordDO>()
|
.between(MesEmRecordDO::getRecordDate, begin, end)
|
.orderByAsc(MesEmRecordDO::getRecordDate)
|
.orderByAsc(MesEmRecordDO::getId));
|
}
|
|
// ==================== 统计查询 ====================
|
|
default List<Map<String, Object>> selectTypeStats(LocalDate begin, LocalDate end) {
|
return selectMaps(new QueryWrapper<MesEmRecordDO>()
|
.select("energy_type AS energyType",
|
"COALESCE(SUM(usage_amount), 0) AS usageAmount",
|
"COALESCE(SUM(cost), 0) AS costAmount",
|
"COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
|
.between("record_date", begin, end)
|
.groupBy("energy_type")
|
.orderByAsc("energy_type"));
|
}
|
|
default List<Map<String, Object>> selectWorkshopStats(LocalDate begin, LocalDate end) {
|
return selectMaps(new QueryWrapper<MesEmRecordDO>()
|
.select("workshop_id AS workshopId",
|
"workshop_name AS workshopName",
|
"COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce",
|
"COALESCE(SUM(cost), 0) AS costAmount")
|
.between("record_date", begin, end)
|
.isNotNull("workshop_id")
|
.groupBy("workshop_id", "workshop_name")
|
.orderByDesc("tce"));
|
}
|
|
default List<Map<String, Object>> selectTrendRows(LocalDate begin, LocalDate end) {
|
return selectMaps(new QueryWrapper<MesEmRecordDO>()
|
.select("record_date AS recordDate",
|
"energy_type AS energyType",
|
"COALESCE(SUM(usage_amount), 0) AS usageAmount",
|
"COALESCE(SUM(cost), 0) AS costAmount",
|
"COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
|
.between("record_date", begin, end)
|
.groupBy("record_date", "energy_type")
|
.orderByAsc("record_date"));
|
}
|
|
default List<Map<String, Object>> selectTopMeterStats(LocalDate begin, LocalDate end, int limit) {
|
return selectMaps(new QueryWrapper<MesEmRecordDO>()
|
.select("meter_id AS meterId",
|
"meter_name AS meterName",
|
"energy_type AS energyType",
|
"unit AS unit",
|
"COALESCE(SUM(usage_amount), 0) AS usageAmount",
|
"COALESCE(SUM(cost), 0) AS costAmount",
|
"COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
|
.between("record_date", begin, end)
|
.groupBy("meter_id", "meter_name", "energy_type", "unit")
|
.orderByDesc("tce")
|
.last("LIMIT " + limit));
|
}
|
|
default Long selectCountByMeterId(Long meterId) {
|
return selectCount(new LambdaQueryWrapperX<MesEmRecordDO>()
|
.eq(MesEmRecordDO::getMeterId, meterId));
|
}
|
|
}
|
