| IEC61850_ForPowerSupply/src/com/base/Com.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/main/main_PowerDev_IEC61850.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/power/alarm/PwrDevHistoryAlm_SubtableThread.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/power/alarm/PwrDevHistoryAlm_SubtableThread_SQL.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/power/datasave/PowerDataSave_SQL.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/power/mysql/Sql_Mysql.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| IEC61850_ForPowerSupply/src/com/version_inf/version_inf.txt | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 |
IEC61850_ForPowerSupply/src/com/base/Com.java
@@ -4,6 +4,7 @@ import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; public class Com { @@ -15,8 +16,9 @@ final public static String DTF_YMDhm = "yyyy-MM-dd HH:mm"; final public static String DTF_YMDh = "yyyy-MM-dd HH"; final public static String DTF_YMD = "yyyy-MM-dd"; final public static String DTFYMD = "yyyyMMdd"; final public static String DTF_YM = "yyyy_MM"; final public static String DTF_Y = "yyyy"; final public static String DTFYMD = "yyyyMMdd"; final public static String DTF_hms = "HH:mm:ss"; final public static String DTF_YMD_h_m_s = "yyyy-MM-dd+HH_mm_ss"; //final public static DateFormat DateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); @@ -48,10 +50,6 @@ } return date; } public static String getNowTimeWithAt() { return " @ " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms); } /** @@ -157,4 +155,53 @@ System.out.println(e.getMessage()); } } /** * 获取本月第一天0点时间 * @return */ public static long getMonthFirstDay(Date time) { //System.out.println(Com.getDateTimeFormat(time, Com.DTF_YMD)); Calendar calendar = Calendar.getInstance();// 获取当前日期 calendar.setTime(time); calendar.add(Calendar.MONTH, 0); calendar.set(Calendar.DAY_OF_MONTH, 1);// 设置为1号,当前日期既为本月第一天 calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); //System.out.println(calendar.getTimeInMillis()); return calendar.getTimeInMillis(); } /** * 获取今年第一天0点时间 * @return */ public static long getYearFirstDay(Date time) { //System.out.println(Com.getDateTimeFormat(time, Com.DTF_YMD)); Calendar currCal = Calendar.getInstance();// 获取当前日期 currCal.setTime(time); Calendar calendar = Calendar.getInstance(); calendar.clear(); calendar.set(Calendar.YEAR,currCal.get(Calendar.YEAR)); //System.out.println(calendar.getTimeInMillis()); return calendar.getTimeInMillis(); } public static void main(String[] args) { Date time = new Date(10,7,9); System.out.println(Com.getDateTimeFormat(new Date(getYearFirstDay(time)), Com.DTF_YMDhms)); } } IEC61850_ForPowerSupply/src/com/main/main_PowerDev_IEC61850.java
@@ -10,6 +10,7 @@ import com.base.AppConfig; import com.power.mysql.Sql_Mysql; import com.power.alarm.AppAlm_Param; import com.power.alarm.PwrDevHistoryAlm_SubtableThread; import com.power.alarm.PwrDeviceAlarm_Thread; import com.power.alarm.PwrDeviceAlarm_Thread_New; import com.power.alarm.PwrDeviceAlarm_Thread_SQL; @@ -22,7 +23,7 @@ public class main_PowerDev_IEC61850 { public final static boolean app_debug = false; public final static double m_VersionNum = 1.009; public final static double m_VersionNum = 1.101; public final static String m_Version = "Welcome To Use Monitor_PowerDev_IEC61850 V" + m_VersionNum + " RC_20201216"; @@ -114,6 +115,13 @@ real_thread.start(); } { //电源历史告警记录分表检测线程 PwrDevHistoryAlm_SubtableThread sub_thread = new PwrDevHistoryAlm_SubtableThread(GB_MysqlConnPool); new Thread(sub_thread).start(); } /*********************************************************************************/ while(true) { IEC61850_ForPowerSupply/src/com/power/alarm/PwrDevHistoryAlm_SubtableThread.java
New file @@ -0,0 +1,107 @@ package com.power.alarm; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import com.base.Com; import com.power.mysql.MysqlConnPool; /** * 对历史告警记录进行分表处理，创建的分表时间超过3年的告警记录删除 * @author LiJun * */ public class PwrDevHistoryAlm_SubtableThread implements Runnable{ public static int MaxRecordAlmTimeLong = 366*3; //最大记录历史告警时间 private List<PwrDeviceAlarm_Data> pwrAlmDatas; //需要分表的电源历史告警记录 private MysqlConnPool pool; private Logger logger = null; public PwrDevHistoryAlm_SubtableThread(MysqlConnPool pool) { this.pwrAlmDatas = new ArrayList<>(); //需要分表的电源历史告警记录 this.pool = pool; this.logger = LogManager.getLogger(this); } @Override public void run() { logger.info(" PwrDevHistoryAlm_SubtableThread Start at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) ); Date time = new Date(0); Date lastCheck = time; //上一次检测历史告警时间 Date lastDelete = time; //上一次删除历史告警时间 while(true) { try { pwrAlmDatas.clear(); time = new Date(); long checkTimeLong = (time.getTime()-lastCheck.getTime())/(24*60*60*1000); //System.out.println("checkTimeLong:"+checkTimeLong); if(checkTimeLong >= 1) { //每天检测一次历史告警数据 PwrDevHistoryAlm_SubtableThread_SQL.queryBattHistoryAlarm(pool, pwrAlmDatas); //System.err.println(pwrAlmDatas); if(pwrAlmDatas.size() > 0) { //开始进行电池历史告警记录分表 for(int i=0;i<pwrAlmDatas.size();i++) { /* if(battAlmDatas.get(i).battgroup_id != 1000022) { continue; } */ if(i%10==0) { Thread.sleep(100); } //将超过1个月的电源告警记录记入历史告警分表 PwrDevHistoryAlm_SubtableThread_SQL.changePwrAlarmToHistoryTable(pool, pwrAlmDatas.get(i)); } } lastCheck = time; } long deleteTimeLong = (time.getTime()-lastDelete.getTime())/(24*60*60*1000); //System.out.println("deleteTimeLong:"+deleteTimeLong); if(deleteTimeLong >= 1) { //10天检测一次删除历史告警数据 Date deldate = getDateBefore(time, MaxRecordAlmTimeLong); //检测历史告警记录,删除3年前的告警记录分表 PwrDevHistoryAlm_SubtableThread_SQL.delBattAlarmData(pool, deldate); lastDelete = time; } return; } catch (Exception e) { logger.error(e.toString(), e); } finally { try { Thread.sleep(10000); } catch (InterruptedException e) { logger.error(e.toString(), e); } } } } /** * 获取指定时间之前指定天数的时间 * @param d 指定时间 * @param day 指定天数 * @return */ public static Date getDateBefore(Date d,int day){ Calendar now =Calendar.getInstance(); now.setTime(d); now.set(Calendar.DATE,now.get(Calendar.DATE)-day); return now.getTime(); } } IEC61850_ForPowerSupply/src/com/power/alarm/PwrDevHistoryAlm_SubtableThread_SQL.java
New file @@ -0,0 +1,180 @@ package com.power.alarm; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.base.Com; import com.power.mysql.MysqlConnPool; import com.power.mysql.Sql_Mysql; public class PwrDevHistoryAlm_SubtableThread_SQL { /** * 查询今年之前的的电源历史告警记录 * @param pool * @param battAlarms */ public static void queryBattHistoryAlarm(MysqlConnPool pool,List<PwrDeviceAlarm_Data> pwrAlarms) { Date firstTime = new Date(Com.getYearFirstDay(new Date())); //本年1号0点时间 String sql_str = " SELECT * FROM " + Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE + " WHERE alm_start_time < '" + Com.getDateTimeFormat(firstTime, Com.DTF_YMDhms) + "'"; Sql_Mysql sql = new Sql_Mysql(pool.getConn()); ResultSet res = null; try { System.err.println(sql_str); res = sql.sqlMysqlQuery(sql_str); while(res.next()) { PwrDeviceAlarm_Data alm = new PwrDeviceAlarm_Data(res.getInt("alm_type")); alm.pwrdev_id = res.getInt("PowerDeviceId"); alm.RecId = res.getLong("record_id"); alm.alm_type = res.getInt("alm_type"); //告警id alm.alm_cleared_type = res.getInt("alm_cleared_type"); //告警类型 0:实时告警 1:历史告警 2:取消告警 alm.alm_vlaue = res.getFloat("alm_value"); //告警值 alm.alm_level = res.getInt("alm_level"); //告警等级 alm.alm_start_time = res.getTimestamp("alm_start_time"); //告警开始时间 alm.alm_end_time = res.getTimestamp("alm_end_time"); //告警结束时间 alm.alm_trigger = res.getInt("alm_trigger"); //告警来源[0-来源于点表；1-来源于电源告警参数] alm.alm_severity = res.getInt("alm_severity"); //告警重要等级[0:重要；1:紧急] pwrAlarms.add(alm); } } catch (SQLException e) { sql.logger.error(e.toString(), e); } finally { if(null != res) { try { res.close(); } catch (SQLException e) { sql.logger.error(e.toString(), e); } } sql.close_con(); } } /** * 将今年之前的电池告警记录存入告警分表 * @param pool * @param alm */ public static void changePwrAlarmToHistoryTable(MysqlConnPool pool,PwrDeviceAlarm_Data alm) { createPwrHistoryTable(pool, alm); //创建分表记录表 String sql_str_ins = " INSERT INTO " + Sql_Mysql.PWRDEV_ALARM_HISTORYSUB_TABLE + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_Y) + "(record_id,PowerDeviceId,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_trigger,alm_severity) (SELECT record_id,PowerDeviceId,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_trigger,alm_severity from "+Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE+" WHERE PowerDeviceId = " + alm.pwrdev_id + " and alm_type = " + alm.alm_type + " and alm_start_time = '" + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_YMDhms) + "')"; String sql_str_del = " DELETE FROM " + Sql_Mysql.PWRDEV_ALARM_HISTORY_TABLE +" WHERE PowerDeviceId = "+ alm.pwrdev_id + " and alm_type = " + alm.alm_type + " and alm_start_time = '" + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_YMDhms) + "'"; Sql_Mysql sql = new Sql_Mysql(pool.getConn()); try { ArrayList<String> sql_strs = new ArrayList<String>(); //System.err.println(sql_str_ins); //System.err.println(sql_str_del); sql_strs.add(sql_str_ins); sql_strs.add(sql_str_del); sql.makeManualCommit(sql_strs); } catch (Exception e) { sql.logger.error(e.toString(), e); } finally { sql.close_con(); } } /** * 创建电源历史告警分表 * @param pool * @param alm */ public static void createPwrHistoryTable(MysqlConnPool pool,PwrDeviceAlarm_Data alm) { String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.PWRDEV_ALARM_HISTORYSUB_TABLE + Com.getDateTimeFormat(alm.alm_start_time, Com.DTF_Y) + "(" + " `num` bigint(20) NOT NULL AUTO_INCREMENT," + " `record_id` bigint(20) NOT NULL DEFAULT '0'," + " `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," + " `alm_type` int(11) NOT NULL DEFAULT '0'," + " `alm_level` int(11) NOT NULL DEFAULT '0'," + " `alm_source` int(11) NOT NULL DEFAULT '0'," + " `alm_index` int(11) NOT NULL DEFAULT '0'," + " `alm_start_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," + " `alm_end_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," + " `alm_value` float NOT NULL DEFAULT '0'," + " `alm_is_confirmed` tinyint(1) NOT NULL DEFAULT '0'," + " `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," + " `alm_cleared_type` int(11) NOT NULL DEFAULT '0'," + " `usr_Id` int(11) NOT NULL DEFAULT '0'," + " `alm_trigger` int(11) NOT NULL DEFAULT '0' COMMENT '告警触发来源[0-来源于点表；1-来源于电源告警参数]'," + " `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '告警重要等级[0:重要；1:紧急]'," + " PRIMARY KEY (`num`)," + " KEY `index_record_id` (`record_id`)," + " KEY `index_pwrdev_id` (`PowerDeviceId`)," + " KEY `index_alm_type` (`alm_type`)," + " KEY `index_alm_start_time` (`alm_start_time`)," + " KEY `index_alm_cleared_type` (`alm_cleared_type`)" + ") ENGINE=InnoDB AUTO_INCREMENT=4796 DEFAULT CHARSET=utf8;" ; Sql_Mysql sql = new Sql_Mysql(pool.getConn()); try { sql.sqlMysqlExecute(sql_str); } catch (SQLException e) { sql.logger.error(e.toString(), e); } finally{ sql.close_con(); } } /** * 删除创建时间超过三年的电源历史告警分表 * @param pool * @param deldate */ public static void delBattAlarmData(MysqlConnPool pool,Date deldate) { String sql_str_sel = " select TABLE_NAME,CREATE_TIME " + " FROM information_schema.tables " + " where table_schema='db_pwrdev_alarm' " + " AND TABLE_NAME like 'tb_pwrdev_alarm_history_%' " + " AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ; ResultSet res = null; Sql_Mysql sql = new Sql_Mysql(pool.getConn()); res = sql.sqlMysqlTotalQuery(sql_str_sel); int count = 0; StringBuffer sql_delete_strs = new StringBuffer(" DROP TABLE IF EXISTS "); try { while(res.next()) { if(count > 0) { sql_delete_strs.append(","); } sql_delete_strs.append("db_pwrdev_alarm." + res.getString("TABLE_NAME")); sql.logger.info("自动删除超过3年的告警表 : db_pwrdev_alarm."+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)); count++; } if(count >0) { //System.out.println(sql_delete_strs.toString()); sql.sqlTotalMysqlExecute(sql_delete_strs.toString()); } } catch (SQLException e) { sql.logger.error(e.toString(), e); } finally { if(null != res) { try { res.close(); } catch (SQLException e) { sql.logger.error(e.toString(), e); } } sql.close_con(); } } } IEC61850_ForPowerSupply/src/com/power/datasave/PowerDataSave_SQL.java
@@ -114,7 +114,7 @@ Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn()); ResultSet res = null; int count = 0; res = sql.sqlMysqlQuery(sql_select_strs); res = sql.sqlMysqlTotalQuery(sql_select_strs); try { while(res.next()) { if(count > 0) { @@ -126,7 +126,7 @@ count++; } if(count > 0) { sql.sqlMysqlExecute(sql_delete_strs); sql.sqlTotalMysqlExecute(sql_delete_strs); } res.close(); IEC61850_ForPowerSupply/src/com/power/mysql/Sql_Mysql.java
@@ -44,6 +44,7 @@ public final static String PWRDEV_ALARM_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm`"; public final static String PWRDEV_ALARM_VERIFY_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm_verify`"; public final static String PWRDEV_ALARM_HISTORY_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm_history`"; public final static String PWRDEV_ALARM_HISTORYSUB_TABLE = DB_PWRDEV_ALARM + ".tb_pwrdev_alarm_history_"; //电源历史告警分库表 public final static String PWRDEV_ALARM_PARAM_FORSX_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm_param_forsx`"; //山西告警参数表 public final static String PWRDEV_ACDATA_TABLE = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_acdata`"; @@ -576,6 +577,14 @@ sql.setQueryTimeout(30); sql.execute(sql_str); } //执行sql语句 public void sqlTotalMysqlExecute(String sql_str) throws SQLException { Statement sql = mysql_con.createStatement(); sql.setQueryTimeout(5*60); sql.execute(sql_str); } //在事物中执行多条sql语句 @@ -631,6 +640,26 @@ return res; } //根据sql语句执行sql查询语句 public ResultSet sqlMysqlTotalQuery(String sql_str) { ResultSet res = null; try { Statement sql = mysql_con.createStatement(); sql.setQueryTimeout(60*5); String query = sql_str; res = sql.executeQuery(query); } catch(SQLException ex) { //System.err.println("SQLException:" + ex.getMessage()); logger.error(ex.toString(), ex); } return res; } //根据sql语句执行sql更新语句 public void sqlMysqlUpdate(String sql_str) { IEC61850_ForPowerSupply/src/com/version_inf/version_inf.txt
@@ -1,5 +1,9 @@ 山西太原 电源通信程序 V1.101 2023-09-08 1.电源历史告警新增按照年来分表记录，便于电源历史告警查询 2.分表中创建时间超过3年的分表会被删除 V1.009 2023-08-21 1.电源告警新增告警配置读取，用以判别电源告警是否需要手动确认以及能否自动消失
