From ab0fbf96697a57d293111135ef46d735e17895b9 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期五, 30 五月 2025 17:23:36 +0800
Subject: [PATCH] excel导出
---
src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java | 217 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 217 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java b/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java
index 5f265bc..6894c2a 100644
--- a/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java
+++ b/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,211 @@
return new Response<PageInfo>().set(1, pageInfo);
}
+ /**
+ * 鍏呯數缁撴潫鏃堕棿鐨勫畾浣嶉�昏緫涓�:
+ * _鐢垫睜缁刬d琛ㄤ腑,璁板綍鏃堕棿澶т簬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;
+ }
+ }
+
}
--
Gitblit v1.9.1