package com.dev.lock.history;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.dev.lock.data.ElectLock_Inf;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class ElectLock_HistoryData_Task_SQL {
|
|
/**
|
* 插入或者查询今年的统计信息
|
* @param pool
|
* @param rt_data
|
*/
|
public static void insertNowElectReport(MysqlConnPool pool, ElectLock_Inf rt_data,Date now) {
|
String sql_str_sel = "SELECT record_year,month1,month2,month3,month4,month5,month6, " +
|
" month7,month8,month9,month10,month11,month12,quarter1,quarter2,quarter3,quarter4,year_count " +
|
" FROM " + Sql_Mysql.Lock_Report_Table +
|
" WHERE lock_id = " + rt_data.getLock_id() + " AND record_year = " + Com.getDateTimeFormat(now, Com.DTF_Y);
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Lock_Report_Table + "(lock_id,record_year) VALUES(" + rt_data.getLock_id() + "," + Com.getDateTimeFormat(now, Com.DTF_Y) + ");";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//读取今年的统计数据
|
rt_data.lockReport.month1 = res.getInt("month1"); //1月开锁次数',
|
rt_data.lockReport.month2 = res.getInt("month2"); //2月开锁次数',
|
rt_data.lockReport.month3 = res.getInt("month3"); //3月开锁次数',
|
rt_data.lockReport.month4 = res.getInt("month4"); //4月开锁次数',
|
rt_data.lockReport.month5 = res.getInt("month5"); //5月开锁次数',
|
rt_data.lockReport.month6 = res.getInt("month6"); //6月开锁次数',
|
rt_data.lockReport.month7 = res.getInt("month7"); //7月开锁次数',
|
rt_data.lockReport.month8 = res.getInt("month8"); //8月开锁次数',
|
rt_data.lockReport.month9 = res.getInt("month9"); //9月开锁次数',
|
rt_data.lockReport.month10 = res.getInt("month10"); //10月开锁次数',
|
rt_data.lockReport.month11 = res.getInt("month11"); //11月开锁次数',
|
rt_data.lockReport.month12 = res.getInt("month12"); //12月开锁次数',
|
rt_data.lockReport.quarter1 = res.getInt("quarter1"); //第一季度开锁次数',
|
rt_data.lockReport.quarter2 = res.getInt("quarter2"); //第二季度开锁次数',
|
rt_data.lockReport.quarter3 = res.getInt("quarter3"); //第三季度开锁次数',
|
rt_data.lockReport.quarter4 = res.getInt("quarter4"); //第四季度开锁次数',
|
rt_data.lockReport.year_count = res.getInt("year_count"); //本年开锁次数',
|
|
}else {
|
//插入默认数据
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} 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 nowYear
|
*/
|
public static void createLockHistTable(MysqlConnPool pool, int lock_id, int nowYear) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Lock_His_Table + lock_id + "_" + nowYear + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键'," +
|
" `lock_id` int(11) NOT NULL DEFAULT '1' COMMENT '锁具ID号'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '记录时间'," +
|
" `lock_state` int(11) NOT NULL DEFAULT '0' COMMENT '锁具状态[0-闭锁 1-开锁]'," +
|
" `unlock_type` int(11) NOT NULL DEFAULT '0' COMMENT '上一次开锁方式[0-485开锁 1-刷卡开锁 2-蓝牙开锁 3-DI开锁]'," +
|
" `unlock_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '上一次刷开开锁卡号[仅刷ID卡开锁时有效]'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='锁具" + nowYear + "年状态更新表';";
|
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 deleteHistoryData(MysqlConnPool pool, Date delTime) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_lock_his' " +
|
" AND TABLE_NAME like 'tb_lock_his_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(delTime, Com.DTF_YMDhms)+"';" ;
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
int count = 0;
|
try {
|
res = sql.sqlMysqlTotalQuery(sql_select_strs);
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs += ",";
|
}
|
|
sql_delete_strs += "db_lock_his." + res.getString("TABLE_NAME");
|
sql.logger.warn("删除："+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count >0) {
|
sql.sqlMysqlTotalExecute(sql_delete_strs);
|
}
|
|
} catch (SQLException e) {
|
sql.logger.error("deleteHistoryData():" + e.toString(), e);
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("deleteHistoryData():" + e.toString(), e);
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* 插入指定锁具当年的默认数据
|
* @param pool
|
* @param lock_id
|
* @param Year
|
* @return
|
*/
|
public static void insertDefaultLock_Report_Table(MysqlConnPool pool,int lock_id,int year) {
|
String sql_str_sel = " SELECT * FROM " + Sql_Mysql.Lock_Report_Table + " WHERE lock_id = " + lock_id + " AND record_year = " + year;
|
String sql_str_ins = "INSERT INTO db_lock_ram.tb_lock_report(lock_id,record_year) VALUES(" + lock_id + "," + year + ");";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} 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();
|
}
|
}
|
|
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("192.168.10.82",3382,10);
|
insertDefaultLock_Report_Table(pool, 2 , 2025);
|
|
}
|
}
|
