添加查询电源信息历史数据接口

whyczh

2022-01-19 f83c4ad4117737869c5c189e48c9f99a7b16a091

添加查询电源信息历史数据接口

 src/main/java/com/whyc/controller/PwrdevDataHistoryController.java

New file
@@ -0,0 +1,26 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.dto.paramter.PwrdevReportParam;
import com.whyc.service.PwrdevDataHistoryService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
@RestController
@Api(tags = "报表统计-查询电源信息历史数据")
@RequestMapping("pwrdevDataHistory")
public class PwrdevDataHistoryController {
    @Resource
    private PwrdevDataHistoryService service;
    @PostMapping("/getDataHistory")
    @ApiOperation(value = "查询电源信息历史数据" , notes = "PwrdevDataHistoryAction_power_getPwrdevDataHistory")
    public Response getDataHistory(@RequestBody PwrdevReportParam pwrDevReportParam){
        return service.searchByCondition(pwrDevReportParam);
    }

}

 src/main/java/com/whyc/dto/paramter/PwrdevReportParam.java

New file
@@ -0,0 +1,14 @@
package com.whyc.dto.paramter;

import lombok.Data;

import java.util.Date;

@Data
public class PwrdevReportParam {
    private Date startTime;
    private Date endTime;
    private String type;
    private String powerDeviceId;
    private String val;
}

 src/main/java/com/whyc/mapper/PwrdevDataHistoryMapper.java

New file
@@ -0,0 +1,20 @@
package com.whyc.mapper;

import com.whyc.pojo.PwrdevDataHistory;
import org.apache.ibatis.annotations.Param;
import org.springframework.web.bind.annotation.PostMapping;

import java.util.Date;
import java.util.List;

public interface PwrdevDataHistoryMapper extends CustomMapper<PwrdevDataHistory> {
    List<String> getTableNameList(@Param("deviceIdEnd") String deviceIdEnd);

    List<PwrdevDataHistory> getGtStartTime(@Param("tableName") String tableName, Date startTime);

    List<PwrdevDataHistory> getLtEndTime(@Param("tableName") String tableName, Date endTime);

    List<PwrdevDataHistory> getData(@Param("tableName") String tableName);

    List<PwrdevDataHistory> getWeekOrDayData(@Param("tableName") String tableName,Date startTime,Date endTime);
}

 src/main/java/com/whyc/pojo/PwrdevDataHistory.java

New file
@@ -0,0 +1,149 @@
package com.whyc.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;

import java.util.Date;
@Data
@TableName(schema = "db_pwrdev_data_history",value = "tb_pwrdev_historydata")
@ApiModel(value="PwrdevDataHistory对象", description="")
public class PwrdevDataHistory {
    private Integer num;
    @TableField("PowerDeviceId")
    private Integer powerDeviceId;
    private Date recordTime;
    @TableField("AC_acIn1_volA")
    private Float AC_acIn1_volA;
    @TableField("AC_acIn1_volB")
    private Float AC_acIn1_volB;
    @TableField("AC_acIn1_volC")
    private Float AC_acIn1_volC;
    @TableField("AC_acIn1_currA")
    private Float AC_acIn1_currA;
    @TableField("AC_acIn1_currB")
    private Float AC_acIn1_currB;
    @TableField("AC_acIn1_currC")
    private Float AC_acIn1_currC;
    @TableField("AC_acIn2_volA")
    private Float AC_acIn2_volA;
    @TableField("AC_acIn2_volB")
    private Float AC_acIn2_volB;
    @TableField("AC_acIn2_volC")
    private Float AC_acIn2_volC;
    @TableField("AC_acIn2_currA")
    private Float AC_acIn2_currA;
    @TableField("AC_acIn2_currB")
    private Float AC_acIn2_currB;
    @TableField("AC_acIn2_currC")
    private Float AC_acIn2_currC;
    @TableField("AC_temprature")
    private Float ACTemprature;
    @TableField("AC_acVolH_Limit")
    private Float AC_acVolH_Limit;
    @TableField("AC_acVolL_Limit")
    private Float AC_acVolL_Limit;
    @TableField("ACDC_acIn1_volA")
    private Float ACDC_acIn1_volA;
    @TableField("ACDC_acIn1_volB")
    private Float ACDC_acIn1_volB;
    @TableField("ACDC_acIn1_volC")
    private Float ACDC_acIn1_volC;
    @TableField("ACDC_acIn2_volA")
    private Float ACDC_acIn2_volA;
    @TableField("ACDC_acIn2_volB")
    private Float ACDC_acIn2_volB;
    @TableField("ACDC_acIn2_volC")
    private Float ACDC_acIn2_volC;
    @TableField("ACDC_acOut_volA")
    private Float ACDC_acOut_volA;
    @TableField("ACDC_acOut_volB")
    private Float ACDC_acOut_volB;
    @TableField("ACDC_acOut_volC")
    private Float ACDC_acOut_volC;
    @TableField("ACDC_acOut_currA")
    private Float ACDC_acOut_currA;
    @TableField("ACDC_acOut_currB")
    private Float ACDC_acOut_currB;
    @TableField("ACDC_acOut_currC")
    private Float ACDC_acOut_currC;
    @TableField("ACDC_dcOut_vol")
    private Float ACDC_dcOut_vol;
    @TableField("ACDC_loader_curr")
    private Float ACDC_loader_curr;
    @TableField("ACDC_battgroup1_vol")
    private Float ACDC_battgroup1_vol;
    @TableField("ACDC_battgroup1_curr")
    private Float ACDC_battgroup1_curr;
    @TableField("ACDC_battgroup2_vol")
    private Float ACDC_battgroup2_vol;
    @TableField("ACDC_battgroup2_curr")
    private Float ACDC_battgroup2_curr;
    @TableField("ACDC_acdcm_temp")
    private Float ACDC_acdcm_temp;
    @TableField("ACDC_m1_outCurr")
    private Float ACDC_m1_outCurr;
    @TableField("ACDC_m2_outCurr")
    private Float ACDC_m2_outCurr;
    @TableField("ACDC_m3_outCurr")
    private Float ACDC_m3_outCurr;
    @TableField("ACDC_m4_outCurr")
    private Float ACDC_m4_outCurr;
    @TableField("ACDC_m5_outCurr")
    private Float ACDC_m5_outCurr;
    @TableField("ACDC_m6_outCurr")
    private Float ACDC_m6_outCurr;
    @TableField("ACDC_m7_outCurr")
    private Float ACDC_m7_outCurr;
    @TableField("ACDC_m8_outCurr")
    private Float ACDC_m8_outCurr;
    @TableField("ACDC_m9_outCurr")
    private Float ACDC_m9_outCurr;
    @TableField("ACDC_m10_outCurr")
    private Float ACDC_m10_outCurr;
    @TableField("ACDC_m11_outCurr")
    private Float ACDC_m11_outCurr;
    @TableField("ACDC_m12_outCurr")
    private Float ACDC_m12_outCurr;
    @TableField("ACDC_m13_outCurr")
    private Float ACDC_m13_outCurr;
    @TableField("ACDC_m14_outCurr")
    private Float ACDC_m14_outCurr;
    @TableField("ACDC_m15_outCurr")
    private Float ACDC_m15_outCurr;
    @TableField("ACDC_m16_outCurr")
    private Float ACDC_m16_outCurr;
    @TableField("ACDC_chargLimitCurr")
    private Float ACDC_chargLimitCurr;
    @TableField("ACDC_junChargeVol")
    private Float ACDC_junChargeVol;
    @TableField("ACDC_floatChargeVol")
    private Float ACDC_floatChargeVol;
    @TableField("ACDC_acVolH_Limit")
    private Float ACDC_acVolH_Limit;
    @TableField("ACDC_acVolL_Limit")
    private Float ACDC_acVolL_Limit;
    @TableField("ACDC_dcOutVolH_Limit")
    private Float ACDC_dcOutVolH_Limit;
    @TableField("ACDC_dcOutVolL_Limit")
    private Float ACDC_dcOutVolL_Limit;
    @TableField("DC_dcIn1_vol")
    private Float DC_dcIn1_vol;
    @TableField("DC_dcIn2_vol")
    private Float DC_dcIn2_vol;
    @TableField("DC_dcOut1_vol")
    private Float DC_dcOut1_vol;
    @TableField("DC_dcOut1_curr")
    private Float DC_dcOut1_curr;
    @TableField("DC_dcOut2_vol")
    private Float DC_dcOut2_vol;
    @TableField("DC_dcOut2_curr")
    private Float DC_dcOut2_curr;
    @TableField("DC_temprature")
    private Float DC_temprature;
    @TableField("DC_dcVolH_Limit")
    private Float DC_dcVolH_Limit;
    @TableField("DC_dcVolL_Limit")
    private Float DC_dcVolL_Limit;
}

 src/main/java/com/whyc/service/PwrdevDataHistoryService.java

New file
@@ -0,0 +1,94 @@
package com.whyc.service;

import com.whyc.dto.Response;
import com.whyc.dto.paramter.PwrdevReportParam;
import com.whyc.mapper.PwrdevDataHistoryMapper;
import com.whyc.pojo.PwrdevDataHistory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Service
public class PwrdevDataHistoryService {
    @Resource
    private PwrdevDataHistoryMapper mapper;

    public Response searchByCondition(PwrdevReportParam pwrdevReportParam){
        //获取设备id
        String deviceId = pwrdevReportParam.getPowerDeviceId();
        String sqlend = "%";
        //类型  天day 周week 月month 年year
        String type = pwrdevReportParam.getType();
        //年:2021  月:2021_06
        String val = pwrdevReportParam.getVal();
        //开始时间 week和day判断
        Date startTime = pwrdevReportParam.getStartTime()!=null?pwrdevReportParam.getStartTime():new Date();
        //结束时间
        Date endTime = pwrdevReportParam.getEndTime()!=null?pwrdevReportParam.getEndTime():new Date();
        //需判断月份是否跨月
        Calendar calendar_start = Calendar.getInstance();
        calendar_start.setTime(startTime);
        Calendar calendar_end = Calendar.getInstance();
        calendar_end.setTime(endTime);
        if ("year".equals(type)){
            sqlend = val+"%";
        }else if ("month".equals(type)){
            sqlend = val+"%";
        }else if("week".equals(type)){
            int year = calendar_start.get(Calendar.YEAR);
            sqlend = year+"%";
        }else{
            sqlend = calendar_start.get(Calendar.YEAR)+"%";
        }
        List<String> tableNameList = mapper.getTableNameList(deviceId+"_"+sqlend);
        //如果未查询到表名，则没有数据
        if (tableNameList.size()==0){
            return null;
        }
        //数据结果
        List<PwrdevDataHistory> result = new ArrayList<>();
        //时间格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //当周跨月份时
        if ("week".equals(type) && calendar_start.get(Calendar.MONTH)!=calendar_end.get(Calendar.MONTH)){
            for (String tableName:tableNameList) {
                //开始时间的月份获取，需+1
                int startMonth = calendar_start.get(Calendar.MONTH)+1;
                int endMonth = calendar_end.get(Calendar.MONTH)+1;
                //当表名和开始时间的月份相同时，获取开始时间后的数据
                if (Integer.valueOf(tableName.substring(tableName.length()-2))==startMonth){
                    List<PwrdevDataHistory> listMonth = mapper.getGtStartTime(tableName,startTime);
                    result.addAll(listMonth);
                }
                //当结束时间与表名的月份相同时，获取结束时间前的数据
                if (Integer.valueOf(tableName.substring(tableName.length()-2))==endMonth){
                    List<PwrdevDataHistory> listMonth = mapper.getLtEndTime(tableName,endTime);
                    result.addAll(listMonth);
                }
            }
            //当是年或者月的时候，直接获取整表数据
        }else if("year".equals(type) || "month".equals(type)){
            for (String tableName: tableNameList ) {
                List<PwrdevDataHistory> listMonth = mapper.getData(tableName);
                result.addAll(listMonth);
            }
            //当是天或者周为同月时，直接在当月表中获取开始时间到结束时间的数据
        }else{
            for (String tableName: tableNameList ) {
                int month = calendar_start.get(Calendar.MONTH)+1;
                if (Integer.valueOf(tableName.substring(tableName.length()-2))==month){
                    List<PwrdevDataHistory> listMonth = mapper.getWeekOrDayData(tableName,startTime,endTime);
                    result.addAll(listMonth);
                }
            }
        }

        return new Response().set(1,result,"查询成功");
    }

}

 src/main/resources/mapper/PwrdevDataHistoryMapper.xml

New file
@@ -0,0 +1,22 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.whyc.mapper.PwrdevDataHistoryMapper" >


    <select id="getTableNameList" resultType="java.lang.String">
        select TABLE_NAME from information_schema.TABLES t where t.TABLE_SCHEMA ='db_pwrdev_data_history' and t.TABLE_NAME like 'tb_pwrdev_historydata_${deviceIdEnd}'
    </select>
    <select id="getGtStartTime" resultType="com.whyc.pojo.PwrdevDataHistory">
        select * from db_pwrdev_data_history.${tableName} where record_time>=#{startTime}
    </select>
    <select id="getLtEndTime" resultType="com.whyc.pojo.PwrdevDataHistory">
        select * from db_pwrdev_data_history.${tableName} where record_time&lt;=#{endTime}
    </select>
    <select id="getData" resultType="com.whyc.pojo.PwrdevDataHistory">
        select * from db_pwrdev_data_history.${tableName}
    </select>
    <select id="getWeekOrDayData" resultType="com.whyc.pojo.PwrdevDataHistory">
        select * from db_pwrdev_data_history.${tableName} where record_time>=#{startTime} and record_time&lt;=#{endTime}
    </select>

</mapper>