package cn.iocoder.yudao.module.mes.dal.mysql.energy;
|
|
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.energy.vo.EnergyRecordPageReqVO;
|
import cn.iocoder.yudao.module.mes.controller.admin.energy.vo.EnergyAnalysisReqVO;
|
import cn.iocoder.yudao.module.mes.dal.dataobject.energy.EnergyRecordDO;
|
import lombok.Data;
|
import org.apache.ibatis.annotations.*;
|
import java.math.BigDecimal;
|
import java.util.List;
|
|
@Mapper
|
public interface EnergyRecordMapper extends BaseMapperX<EnergyRecordDO> {
|
default PageResult<EnergyRecordDO> selectPage(EnergyRecordPageReqVO req) {
|
return selectPage(req, new LambdaQueryWrapperX<EnergyRecordDO>()
|
.geIfPresent(EnergyRecordDO::getRecordDate, req.getStartDate())
|
.leIfPresent(EnergyRecordDO::getRecordDate, req.getEndDate())
|
.eqIfPresent(EnergyRecordDO::getEnergyType, req.getEnergyType())
|
.likeIfPresent(EnergyRecordDO::getUsageArea, req.getUsageArea())
|
.eqIfPresent(EnergyRecordDO::getProductCategory, req.getProductCategory())
|
.orderByDesc(EnergyRecordDO::getRecordDate, EnergyRecordDO::getId));
|
}
|
|
// 按数据库分组，避免加载全部台账；租户拦截器自动添加 tenant_id 条件。
|
@Select("""
|
<script>
|
SELECT energy_type AS energyType,
|
<choose><when test="req.groupBy == 'MONTH'">DATE_FORMAT(record_date, '%Y-%m')</when>
|
<otherwise>DATE_FORMAT(record_date, '%Y-%m-%d')</otherwise></choose> AS period,
|
usage_area AS usageArea, SUM(quantity) AS quantity, SUM(amount) AS amount,
|
COUNT(*) AS recordCount
|
FROM mes_energy_record
|
WHERE deleted = 0 AND record_date BETWEEN #{req.startDate} AND #{req.endDate}
|
<if test="req.energyType != null and req.energyType != ''">AND energy_type = #{req.energyType}</if>
|
<if test="req.usageArea != null and req.usageArea != ''">AND usage_area LIKE CONCAT('%', #{req.usageArea}, '%')</if>
|
<if test="req.productCategory != null and req.productCategory != ''">AND product_category = #{req.productCategory}</if>
|
GROUP BY energy_type, period, usage_area
|
ORDER BY period, energy_type, usage_area
|
</script>
|
""")
|
List<Aggregate> selectAnalysis(@Param("req") EnergyAnalysisReqVO req);
|
|
@Data
|
class Aggregate {
|
private String energyType;
|
private String period;
|
private String usageArea;
|
private BigDecimal quantity;
|
private BigDecimal amount;
|
private long recordCount;
|
}
|
}
|
