package com.dev.lock.alarm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.dev.lock.alarm.data.ElectLock_AlarmData;
|
import com.dev.lock.data.ElectLock_Inf;
|
import com.dev.lock.data.ElectLock_State;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class ElectLock_Alarm_Thread_SQL {
|
|
public static void updateProcess_StartTime(MysqlConnPool pool, double mVersionnum) {
|
String sql_str = "UPDATE " + Sql_Mysql.Process_Survey_Table +
|
" Set Process_starttime = NOW() " +
|
" ,ProcessTime = NOW() " +
|
" ,ProcessVersion = 'V" + mVersionnum + "'"+
|
" WHERE ProcessId = 11002";
|
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();
|
}
|
}
|
|
public static void updateProcess_ProcessTime(MysqlConnPool pool) {
|
String sql_str = "UPDATE " + Sql_Mysql.Process_Survey_Table +
|
" Set ProcessTime = NOW() " +
|
" WHERE ProcessId = 11002";
|
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 inf
|
*/
|
public static void queryElectLockState(MysqlConnPool pool, ElectLock_Inf inf) {
|
String sql_str_sql = "SELECT record_time,lock_state,now() as now_time FROM " + Sql_Mysql.Lock_Rt_Table + " WHERE lock_id = " + inf.getLock_id();
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlQuery(sql_str_sql);
|
try {
|
ElectLock_State state = inf.lockState;
|
if(res.next()) {
|
|
|
state.setRecord_time(res.getTimestamp("record_time")); //更新时间lock_state
|
state.setLock_state(res.getInt("lock_state")); //'电子锁状态[0-关 1-开]',
|
state.setNow_time(res.getTimestamp("now_time")); //当前时间
|
}else {
|
state.setNow_time(new Date()); //当前时间
|
}
|
} 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 lock_id
|
* @param almId
|
* @return
|
*/
|
public static ElectLock_AlarmData queryElectLockAlarmByAlmId(MysqlConnPool pool,int lock_id, int almId) {
|
ElectLock_AlarmData almData = null;
|
String sql_str = " SELECT lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,alm_end_time"
|
+ " FROM " + Sql_Mysql.Lock_Alarm_Table
|
+ " WHERE lock_id = " + lock_id + " AND alm_id = " + almId;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
almData = new ElectLock_AlarmData();
|
|
almData.setLock_id(res.getInt("lock_id"));
|
almData.setAlm_id(res.getInt("alm_id"));
|
almData.setAlm_source(res.getInt("alm_source")); //'告警来源[1-平台触发 2-手机APP触发 3-锁具触发]',
|
almData.setAlm_start_time(res.getTimestamp("alm_start_time")); //'告警开始时间',
|
almData.setAlm_is_confirmed(res.getInt("alm_is_confirmed")); //'告警是否确认',
|
almData.setAlm_confirmed_time(res.getTimestamp("alm_confirmed_time")); //'告警确认时间',
|
almData.setAlm_end_time(res.getTimestamp("alm_end_time")); //'告警结束时间',
|
|
}
|
} 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();
|
}
|
return almData;
|
}
|
|
/**
|
* 插入实时告警
|
* @param pool
|
* @param lock_id
|
* @param almId
|
* @param alm_source
|
*/
|
public static void insertElectLockAlarm(MysqlConnPool pool,int lock_id, int almId,int almSource) {
|
String sql_str = "INSERT INTO " + Sql_Mysql.Lock_Alarm_Table
|
+ "(lock_id,alm_id,alm_source,alm_start_time,alm_end_time) "
|
+ " VALUES(" + lock_id + "," + almId + "," + almSource + ",Now(),Now());";
|
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 almData
|
*/
|
public static void changeRealAlmToHisAlm(MysqlConnPool pool, ElectLock_AlarmData almData) {
|
//创建当前告警开始年份的历史告警表
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Lock_Alarm_His_Table + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y) + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键'," +
|
" `lock_id` int(11) NOT NULL DEFAULT '1' COMMENT '锁具ID'," +
|
" `alm_id` int(11) NOT NULL DEFAULT '119001' COMMENT '告警类型'," +
|
" `alm_source` int(11) NOT NULL DEFAULT '1' COMMENT '告警来源[1-平台触发 2-手机APP触发 3-锁具触发]'," +
|
" `alm_start_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警开始时间'," +
|
" `alm_is_confirmed` tinyint(4) NOT NULL DEFAULT '0' COMMENT '告警是否确认'," +
|
" `alm_confirmed_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警确认时间'," +
|
" `alm_end_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警结束时间'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y) + "年电子锁具历史告警表';";
|
//2将当前的实时告警记录插入到历史告警表中
|
String sql_str2 = " INSERT INTO " + Sql_Mysql.Lock_Alarm_His_Table + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y)
|
+ "(lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,alm_end_time) "
|
+ "(SELECT lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,Now() from " + Sql_Mysql.Lock_Alarm_Table
|
+ " WHERE lock_id = " + almData.getLock_id() + " AND alm_id = " + almData.getAlm_id() + " "
|
+ ")";
|
//3将实时告警表中的告警记录清空
|
String sql_str3 = " DELETE FROM " + Sql_Mysql.Lock_Alarm_Table
|
+ " WHERE "
|
+ " lock_id =" + almData.getLock_id()
|
+ " AND alm_id =" + almData.getAlm_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
ArrayList<String> sql_strs = new ArrayList<String>();
|
|
sql_strs.add(sql_str1);
|
sql_strs.add(sql_str2);
|
sql_strs.add(sql_str3);
|
sql.makeManualCommit(sql_strs);
|
} catch (Exception e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* 将临时表中的告警记录插入实时告警表中
|
* @param pool
|
*/
|
public static void changeTempAlmToRealAlm(MysqlConnPool pool) {
|
//1.查询是否存在临时告警记录
|
String sql_str_sel = " SELECT COUNT(*) as alm_count FROM " + Sql_Mysql.Lock_Alm_Temp_Table;
|
//2将当前的实时告警记录插入到历史告警表中
|
String sql_str2 = " INSERT INTO " + Sql_Mysql.Lock_Alarm_Table
|
+ " (lock_id,alm_id,alm_source,alm_start_time,alm_end_time) "
|
+ " (SELECT lock_id,alm_id,alm_source,alm_start_time,alm_end_time from " + Sql_Mysql.Lock_Alm_Temp_Table + ")";
|
//3将实时告警表中的告警记录清空
|
String sql_str3 = " DELETE FROM " + Sql_Mysql.Lock_Alm_Temp_Table;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
int alm_count = res.getInt("alm_count");
|
//临时表中存在实时告警时，移动到实时告警表中
|
if(alm_count > 0) {
|
ArrayList<String> sql_strs = new ArrayList<String>();
|
|
sql_strs.add(sql_str2);
|
sql_strs.add(sql_str3);
|
|
sql.makeManualCommit(sql_strs);
|
}
|
}
|
} catch (Exception 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();
|
}
|
}
|
|
public static void delLockAlarmData(MysqlConnPool pool, Date deldate) {
|
String sql_str_sel = " select TABLE_NAME,CREATE_TIME " +
|
" FROM information_schema.tables " +
|
" where table_schema='db_lock_alarm' " +
|
" AND TABLE_NAME like 'tb_lock_alarm_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
res = sql.sqlMysqlQuery(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_lock_alarm." + res.getString("TABLE_NAME"));
|
sql.logger.info("自动删除超过5年的告警表 : db_lock_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.sqlMysqlExecute(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();
|
}
|
}
|
}
|
