能耗管理的统计

zss

3 天以前 8546dfc55df24663d18137c83bf490c15b8d46da

能耗管理的统计

 src/main/java/com/ruoyi/energy/controller/EnergyConsumptionDetailController.java

@@ -3,11 +3,9 @@
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.energy.dto.EnergyConsumptionDetailDto;
import com.ruoyi.energy.dto.EnergyStatisticsDto;
import com.ruoyi.energy.pojo.Energy;
import com.ruoyi.energy.pojo.EnergyConsumptionDetail;
import com.ruoyi.energy.service.EnergyConsumptionDetailService;
import com.ruoyi.energy.service.EnergyService;
import com.ruoyi.energy.vo.EnergyStatisticsVo;
import com.ruoyi.framework.aspectj.lang.annotation.Log;
import com.ruoyi.framework.aspectj.lang.enums.BusinessType;
import com.ruoyi.framework.web.domain.R;
@@ -56,11 +54,13 @@

    @Log(title = "能耗抄表明细-导入", businessType = BusinessType.IMPORT)
    @PostMapping("/importData")
    @ApiOperation("能耗抄表明细-导入")
    public R importData(MultipartFile file) throws Exception {
        return energyConsumptionDetailService.importData(file);
    }

    @PostMapping("/downloadTemplate")
    @ApiOperation("能耗抄表-下载模板")
    @Log(title = "能耗抄表-下载模板", businessType = BusinessType.EXPORT)
    public void downloadTemplate(HttpServletResponse response) {
        ExcelUtil<EnergyConsumptionDetailDto> util = new ExcelUtil<EnergyConsumptionDetailDto>(EnergyConsumptionDetailDto.class);
@@ -78,8 +78,8 @@

    @GetMapping("/statistics")
    @ApiOperation("按日月年汇总统计")
    public R statistics(EnergyStatisticsDto energyStatisticsDto) {
        return R.ok(energyConsumptionDetailService.statistics(energyStatisticsDto));
    public R statistics(EnergyStatisticsVo energyStatisticsVo) {
        return R.ok(energyConsumptionDetailService.statistics(energyStatisticsVo));
    }

}

 src/main/java/com/ruoyi/energy/controller/EnergyController.java

@@ -57,11 +57,13 @@

    @Log(title = "能源类型-导入", businessType = BusinessType.IMPORT)
    @PostMapping("/importData")
    @ApiOperation("能源类型-导入")
    public R importData(MultipartFile file) throws Exception {
        return energyService.importData(file);
    }

    @PostMapping("/downloadTemplate")
    @ApiOperation("能源类型-下载模板")
    @Log(title = "能源类型-下载模板", businessType = BusinessType.EXPORT)
    public void downloadTemplate(HttpServletResponse response) {
        ExcelUtil<Energy> util = new ExcelUtil<Energy>(Energy.class);

 src/main/java/com/ruoyi/energy/dto/EnergyConsumptionDetailDto.java

@@ -7,6 +7,8 @@
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;

//能耗抄表明细
@Data
@ExcelIgnoreUnannotated
@@ -26,4 +28,8 @@

    //创建人
    private String createUserName;

    //费用
    private BigDecimal cost;

}

 src/main/java/com/ruoyi/energy/dto/EnergyConsumptionTypeDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,22 @@
package com.ruoyi.energy.dto;

import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel("能耗类型占比")
public class EnergyConsumptionTypeDto {

    @ApiModelProperty("能源类型")
    private String energyTyep;

    @ApiModelProperty("能耗用量")
    private BigDecimal energyConsumption;

    @ApiModelProperty("能耗费用")
    private BigDecimal energyCost;
}

 src/main/java/com/ruoyi/energy/dto/EnergyCostDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,43 @@
package com.ruoyi.energy.dto;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.checkerframework.checker.units.qual.A;

import java.math.BigDecimal;
import java.time.LocalDate;

//能耗费用明细
@Data
@ApiModel("能耗费用明细")
public class EnergyCostDto {

    @ApiModelProperty("日期")
    private LocalDate meterReadingDate;

    @ApiModelProperty("用水量")
    private BigDecimal waterConsumption;

    @ApiModelProperty("æ°´è´¹")
    private BigDecimal waterCost;

    @ApiModelProperty("用电量")
    private BigDecimal electricityConsumption;

    @ApiModelProperty("电费")
    private BigDecimal electricityCost;

    @ApiModelProperty("用气量")
    private BigDecimal gasConsumption;

    @ApiModelProperty("气费")
    private BigDecimal gasCost;

    @ApiModelProperty("合计用量")
    private BigDecimal totalConsumption;

    @ApiModelProperty("合计费用")
    private BigDecimal totalCost;

}

 src/main/java/com/ruoyi/energy/dto/EnergyStatisticsDto.java

@@ -1,20 +1,36 @@
package com.ruoyi.energy.dto;


import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.time.LocalDate;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

//按日月年汇总统计能耗的传参
@Data
@ApiModel("按日月年汇总统计的能耗数据")
public class EnergyStatisticsDto {

    //开始日期
    private LocalDate startDate;
    @ApiModelProperty("总耗用量")
    private BigDecimal totalEnergyConsumption;

    //结束日期
    private LocalDate endDate;
    @ApiModelProperty("总能耗费用")
    private BigDecimal totalEnergyCost;

    //能耗场景(办公/生产)
    private String type;
    @ApiModelProperty("平均用量")
    private BigDecimal averageConsumption;

    @ApiModelProperty("环比变化")
    private BigDecimal changeVite;

    @ApiModelProperty("能耗类型占比")
    private List<EnergyConsumptionTypeDto> energyConsumptionTypeProportion;

    @ApiModelProperty("能耗明细")
    private List<EnergyCostDto> energyCostDtos;




}

 src/main/java/com/ruoyi/energy/mapper/EnergyConsumptionDetailMapper.java

@@ -3,10 +3,17 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.energy.dto.EnergyConsumptionDetailDto;
import com.ruoyi.energy.dto.EnergyConsumptionTypeDto;
import com.ruoyi.energy.dto.EnergyCostDto;
import com.ruoyi.energy.pojo.EnergyConsumptionDetail;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.energy.vo.EnergyStatisticsVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
@@ -20,4 +27,10 @@
public interface EnergyConsumptionDetailMapper extends BaseMapper<EnergyConsumptionDetail> {

    IPage<EnergyConsumptionDetailDto> pageEnergyConsumptionDetail(Page<EnergyConsumptionDetailDto> page, @Param("c") EnergyConsumptionDetailDto energyConsumptionDetailDto);

    Map<String, BigDecimal> calculateEnergy(@Param("c") EnergyStatisticsVo energyStatisticsVo);

    List<EnergyConsumptionTypeDto> energyConsumptionTypeProportion(@Param("c") EnergyStatisticsVo energyStatisticsVo);

    List<EnergyCostDto> energyCostDtos(@Param("c") EnergyStatisticsVo energyStatisticsVo);
}

 src/main/java/com/ruoyi/energy/service/EnergyConsumptionDetailService.java

@@ -6,6 +6,7 @@
import com.ruoyi.energy.dto.EnergyStatisticsDto;
import com.ruoyi.energy.pojo.EnergyConsumptionDetail;
import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.energy.vo.EnergyStatisticsVo;
import com.ruoyi.framework.web.domain.R;
import org.springframework.web.multipart.MultipartFile;

@@ -27,6 +28,6 @@

    void export(HttpServletResponse response);

    Object statistics(EnergyStatisticsDto energyStatisticsDto);
    EnergyStatisticsDto statistics(EnergyStatisticsVo energyStatisticsVo);

}

 src/main/java/com/ruoyi/energy/service/impl/EnergyConsumptionDetailServiceImpl.java

@@ -7,6 +7,8 @@
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.energy.dto.EnergyConsumptionDetailDto;
import com.ruoyi.energy.dto.EnergyConsumptionTypeDto;
import com.ruoyi.energy.dto.EnergyCostDto;
import com.ruoyi.energy.dto.EnergyStatisticsDto;
import com.ruoyi.energy.mapper.EnergyMapper;
import com.ruoyi.energy.pojo.Energy;
@@ -14,6 +16,7 @@
import com.ruoyi.energy.mapper.EnergyConsumptionDetailMapper;
import com.ruoyi.energy.service.EnergyConsumptionDetailService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.energy.vo.EnergyStatisticsVo;
import com.ruoyi.framework.web.domain.R;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
@@ -21,7 +24,11 @@
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
@@ -91,7 +98,32 @@
    }

    @Override
    public Object statistics(EnergyStatisticsDto energyStatisticsDto) {
        return null;
    public EnergyStatisticsDto statistics(EnergyStatisticsVo energyStatisticsVo) {
        EnergyStatisticsDto energyStatisticsDto = new EnergyStatisticsDto();
        //计算总耗用量+总能耗费用
        Map<String, BigDecimal> map=energyConsumptionDetailMapper.calculateEnergy(energyStatisticsVo);
        energyStatisticsDto.setTotalEnergyConsumption(map.get("totalEnergyConsumption"));
        energyStatisticsDto.setTotalEnergyCost(map.get("totalEnergyCost"));
        //平均用量
        energyStatisticsDto.setAverageConsumption(energyStatisticsDto.getTotalEnergyConsumption().divide(new BigDecimal(energyStatisticsVo.getDays()),2, RoundingMode.HALF_UP));
        //环比变化=(本期值 - ä¸ŠæœŸå€¼) / ä¸ŠæœŸå€¼ * 100
        LocalDate minDays = energyStatisticsVo.getStartDate().minusDays(energyStatisticsVo.getDays());
        LocalDate masDays = energyStatisticsVo.getEndDate().minusDays(energyStatisticsVo.getDays());
        EnergyStatisticsVo oldenergyStatisticsVo = new EnergyStatisticsVo();
        BeanUtils.copyProperties(energyStatisticsVo,oldenergyStatisticsVo);
        oldenergyStatisticsVo.setStartDate(minDays);
        oldenergyStatisticsVo.setEndDate(masDays);
        Map<String, BigDecimal> oldmap=energyConsumptionDetailMapper.calculateEnergy(oldenergyStatisticsVo);
        if (ObjectUtils.isNotEmpty(oldmap)) {
            BigDecimal changeVite = (map.get("totalEnergyConsumption").subtract(oldmap.get("totalEnergyConsumption"))).divide(oldmap.get("totalEnergyConsumption"), 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100));
            energyStatisticsDto.setChangeVite(changeVite);
        }
        //能耗类型占比
        List<EnergyConsumptionTypeDto> energyConsumptionTypeDtos=energyConsumptionDetailMapper.energyConsumptionTypeProportion(energyStatisticsVo);
        energyStatisticsDto.setEnergyConsumptionTypeProportion(energyConsumptionTypeDtos);
        //能耗明细
        List<EnergyCostDto> energyCostDtos=energyConsumptionDetailMapper.energyCostDtos(energyStatisticsVo);
        energyStatisticsDto.setEnergyCostDtos(energyCostDtos);
        return energyStatisticsDto;
    }
}

 src/main/java/com/ruoyi/energy/vo/EnergyStatisticsVo.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,23 @@
package com.ruoyi.energy.vo;


import lombok.Data;

import java.time.LocalDate;

//按日月年汇总统计能耗的传参
@Data
public class EnergyStatisticsVo {

    //开始日期
    private LocalDate startDate;

    //结束日期
    private LocalDate endDate;

    //查询间隔天数
    private Long days;

    //能耗场景(办公/生产)
    private String type;
}

 src/main/resources/mapper/energy/EnergyConsumptionDetailMapper.xml

@@ -26,16 +26,92 @@
                       left join energy e on ecd.energy_id = e.id
                       left join sys_user su on ecd.create_user = su.user_id) A
         <where>
            <if test="c.energyTyep != null and c.energyTyep != ''">
             <if test="c.type != null and c.type != ''">
                and type =#{c.type}
             </if>
             <if test="c.energyTyep != null and c.energyTyep != ''">
                and energy_tyep like concat('%',#{c.energyTyep},'%')
            </if>
             </if>
             <if test="c.energyName != null and c.energyName != ''">
                and energy_name like concat('%',#{c.energyName},'%')
            </if>
             </if>
             <if test="c.meterReadingDate != null and c.meterReadingDate != ''">
                 and meter_reading_date =#{c.meterReadingDate}
             </if>
         </where>
    </select>
    <select id="calculateEnergy" resultType="java.util.Map">
        select SUM(ecd.dosage) totalEnergyConsumption,
               SUM(ecd.dosage * e.unit_price) totalEnergyCost
            from energy_consumption_detail ecd
            left join  energy e on ecd.energy_id = e.id
            where ecd.meter_reading_date between #{c.startDate} and #{c.endDate}
            <if test="c.type != null and c.type != ''">
                and ecd.type =#{c.type}
            </if>
        </where>
    </select>
    <select id="energyConsumptionTypeProportion"
            resultType="com.ruoyi.energy.dto.EnergyConsumptionTypeDto">
         select e.energy_tyep,
                SUM(ecd.dosage) energyConsumption,
               SUM(ecd.dosage * e.unit_price) energyCost
            from energy_consumption_detail ecd
            left join  energy e on ecd.energy_id = e.id
            where ecd.meter_reading_date between #{c.startDate} and #{c.endDate}
            <if test="c.type != null and c.type != ''">
                and ecd.type =#{c.type}
            </if>
            group by e.energy_tyep
    </select>
    <select id="energyCostDtos" resultType="com.ruoyi.energy.dto.EnergyCostDto">
    select A.meter_reading_date,
           A.waterConsumption,
           A.waterCost,
           B.electricityConsumption,
           B.electricityCost,
           C.gasConsumption,
           C.gasCost,
           sum(A.waterConsumption+B.electricityConsumption+C.gasConsumption) totalConsumption,
           sum(A.waterCost+B.electricityCost+C.gasCost) totalCost
    from
    (select ecd.meter_reading_date,
               sum(ecd.dosage) waterConsumption,
               sum(ecd.dosage * e1.unit_price) waterCost
            from energy_consumption_detail ecd
            left join  energy e1 on ecd.energy_id = e1.id
            where ecd.meter_reading_date between #{c.startDate} and #{c.endDate}
            and e1.energy_tyep='æ°´'
            <if test="c.type != null and c.type != ''">
                and ecd.type =#{c.type}
            </if>
            group by ecd.meter_reading_date)A
    join
    (select ecd.meter_reading_date,
               sum(ecd.dosage) electricityConsumption,
               sum(ecd.dosage * e2.unit_price) electricityCost
            from energy_consumption_detail ecd
            left join  energy e2 on ecd.energy_id = e2.id
            where ecd.meter_reading_date between #{c.startDate} and #{c.endDate}
            and e2.energy_tyep='电'
            <if test="c.type != null and c.type != ''">
                and ecd.type =#{c.type}
            </if>
            group by ecd.meter_reading_date)B
        on A.meter_reading_date=B.meter_reading_date
    join
    (select ecd.meter_reading_date,
               sum(ecd.dosage) gasConsumption,
               sum(ecd.dosage * e3.unit_price) gasCost
            from energy_consumption_detail ecd
            left join  energy e3 on ecd.energy_id = e3.id
            where ecd.meter_reading_date between #{c.startDate} and #{c.endDate}
            and e3.energy_tyep='气'
            <if test="c.type != null and c.type != ''">
                and ecd.type =#{c.type}
            </if>
            group by ecd.meter_reading_date)C
        on A.meter_reading_date=C.meter_reading_date
        
    </select>

</mapper>