package com.whyc.service;
|
|
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 {
|
|
PageInfo<Object> pageInfo = subTablePageInfoUtil.getPageInfoByMonthTable(pageNum, pageSize,
|
ThreadLocalUtil.parse(startTime, 1), ThreadLocalUtil.parse(endTime, 1),
|
"db_power_history",
|
"tb_batt_realdata_" + battGroupId,
|
new BattRealTimeDataHistory()
|
);
|
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 order 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;
|
}
|
}
|
