杂项更新

whycxzp

2025-06-09 7fe1ea37e4044eb61374a51c5a2fe1ecf9e834be

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

@@ -2,19 +2,29 @@

import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

@Service
public class BattRealTimeDataHistoryService {

    @Autowired
    private SubTablePageInfoUtil subTablePageInfoUtil;

    @Autowired
    private MybatisSqlExecuteService sqlExecuteService;

    public Response<PageInfo> getPage(int pageNum, int pageSize, int battGroupId,
                                                               String startTime, String endTime) throws ParseException, InterruptedException {
@@ -28,4 +38,276 @@
        return new Response<PageInfo>().set(1, pageInfo);
    }

    /**
     * 充电结束时间的定位逻辑为:
     * _电池组id表中,记录时间大于dischargeEndTime且电流为0的记录中,顺序取出第一个记录.
     * 考虑到充电结束时间可能跨表,所以需要查询下一个月的表.
     * @return
     */
    public BattRealTimeDataHistory getChargeEnd(int battGroupId, Calendar dischargeEndTime) {
        //查询的年月为dischargeEndTime的年月
        int year = dischargeEndTime.get(Calendar.YEAR);
        int month = dischargeEndTime.get(Calendar.MONTH);
        //String tableName = "tb_batt_realdata_" + battGroupId + "_" + year + "_" + (month+1);
        //查询dischargeEndTime的年月加上一个月
        int yearOfNextMonth = year;
        int monthOfNextMonth = month;
        if (month == 11) {
            yearOfNextMonth++;
            monthOfNextMonth = 0;
        }else{
            monthOfNextMonth++;
        }
        String battGroupIdTable = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
        List<BattRealTimeDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setBattGroupId(rs.getInt("binf_id"));
                    his.setRecordTime(rs.getTimestamp("record_time"));
                    his.setOnlineVol(rs.getFloat("online_vol"));
                    his.setGroupVol(rs.getFloat("group_vol"));
                    his.setGroupTmp(rs.getFloat("group_tmp"));
                    his.setGroupCurr(rs.getFloat("group_curr"));

                    list.add(his);
                }
                return list;

            }
        });
        //如果不存在记录,则查询下一个月的表
        if (list.size() == 0) {
            //String tableNameNextMonth = "tb_batt_realdata_" + battGroupId + "_" + yearOfNextMonth + "_" + (monthOfNextMonth+1);
            dischargeEndTime.add(Calendar.MONTH, 1);
            String battGroupITableNameNextMonth = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
            //还原dischargeEndTime
            dischargeEndTime.add(Calendar.MONTH, -1);
            String sqlNextMonth = "select * from db_power_history." + battGroupITableNameNextMonth + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
            List<BattRealTimeDataHistory> listNextMonth = sqlExecuteService.executeQuery_call(sqlNextMonth, new CallBack() {

                @Override
                public List getResults(ResultSet rs) throws SQLException {
                    List<BattRealTimeDataHistory> list = new LinkedList<>();
                    //如果存在记录
                    while (rs.next()) {
                        BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                        his.setBattGroupId(rs.getInt("binf_id"));
                        his.setRecordTime(rs.getTimestamp("record_time"));
                        his.setOnlineVol(rs.getFloat("online_vol"));
                        his.setGroupVol(rs.getFloat("group_vol"));
                        his.setGroupTmp(rs.getFloat("group_tmp"));
                        his.setGroupCurr(rs.getFloat("group_curr"));

                        list.add(his);
                    }
                    return list;

                }
            });
            return listNextMonth.size() == 0 ? null : listNextMonth.get(0);
        }else{
            return list.get(0);
        }
    }

    public List<BattRealTimeDataHistory> getFcVolList(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time < '" + testStartTime + "' and  batt_test_type = 1 order by num desc limit 104";
        List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public List<BattRealTimeDataHistory> getPreVolList(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time = '" + testStartTime + "' order by mon_num asc limit 104";
        List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public List<BattRealTimeDataHistory> getRecordList(int battGroupId, Date recordTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time >= '" + recordTime + "' order by num asc limit 104";
        List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    public Date getChargeStartTime(int battGroupId, Date recordTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
        String sql = "select record_time from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + recordTime + "' and batt_test_type = 2 limit 1";
        List<Date> timeList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<Date> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    Date recordTime = rs.getTimestamp("record_time");

                    list.add(recordTime);
                }
                return list;

            }
        });
        return timeList.get(0);
    }

    public List<BattRealTimeDataHistory> getFcVolListAfter(int battGroupId, Date testStartTime) {
        String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
        String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + testStartTime + "' and  batt_test_type = 1 order by num desc limit 104";
        List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return fcVolList;
    }

    private String getBattGroupIdTable(int battGroupId, Date time) {
        Calendar timeCalendar = Calendar.getInstance();
        timeCalendar.setTime(time);
        int year = timeCalendar.get(Calendar.YEAR);
        int month = timeCalendar.get(Calendar.MONTH)+1;
        if (month < 10){
            return battGroupId + "_" + year + "_0" + month;
        }else{
            return battGroupId + "_" + month + "_" + month;
        }
    }

    public List<BattRealTimeDataHistory> getFirstRecordList(String tableName) {
        String sql = "select * from db_power_history." + tableName + " limit 104";
        List<BattRealTimeDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setTestStartTime(rs.getTimestamp("test_start_time"));
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonRes(rs.getFloat("mon_res"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return list;
    }

    /**
     * select * from tb_batt_realdata_100001_2025_04 where record_time in
     * (SELECT MIN(record_time) AS min_time FROM tb_batt_realdata_100001_2025_04
     * GROUP BY DATE(record_time))
     *
     * @param tableName
     * @param timeAgo
     * @return
     */
    public List<BattRealTimeDataHistory> getFirstRecordListOfDay(String tableName, Date timeAgo) {
        String sqlExtra;
        if(timeAgo!=null){
            sqlExtra = " where record_time > '" + timeAgo + "'";
        }else{
            sqlExtra = "";
        }
        String sql = "select * from db_power_history." + tableName + " where record_time in (SELECT MIN(record_time) FROM db_power_history." + tableName + sqlExtra +" GROUP BY DATE(record_time))";
        List<BattRealTimeDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {

            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattRealTimeDataHistory> list = new LinkedList<>();
                //如果存在记录
                while (rs.next()) {
                    BattRealTimeDataHistory his = new BattRealTimeDataHistory();
                    his.setTestStartTime(rs.getTimestamp("test_start_time"));
                    his.setRecordTime(rs.getTimestamp("record_time"));
                    his.setMonNum(rs.getInt("mon_num"));
                    his.setMonVol(rs.getFloat("mon_vol"));
                    his.setMonRes(rs.getFloat("mon_res"));
                    his.setMonTmp(rs.getFloat("mon_tmp"));

                    list.add(his);
                }
                return list;

            }
        });
        return list;
    }
}