package com.dev.lock.comm;
|
|
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.dev.lock.data.ElectLock_AuthIdcard;
|
import com.dev.lock.data.ElectLock_ComBase;
|
import com.dev.lock.data.ElectLock_Ctl_Log;
|
import com.dev.lock.data.ElectLock_Inf;
|
import com.dev.lock.data.ElectLock_Report;
|
import com.dev.lock.data.ElectLock_State;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Lock_Task_SQL {
|
|
|
public static void insertLock_Rt_Table(MysqlConnPool pool,int lock_id,ElectLock_State state) {
|
String sql_str_sel = "SELECT * FROM " + Sql_Mysql.Lock_Rt_Table + " WHERE lock_id = " + lock_id;
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Lock_Rt_Table + "(lock_id,client_ip) VALUES("+lock_id+",'" + state.getClient_ip() + "');";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
|
state.setLock_state(res.getInt("lock_state"));
|
|
state.setComm_count(res.getInt("comm_count")); //'通信计数',
|
state.setErr_tol_count(res.getInt("err_tol_count")); //'总错误计数',
|
System.out.println("lock_state:" +state.getLock_state());
|
}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();
|
}
|
}
|
|
|
public static void updateLock_Rt_Table(MysqlConnPool pool,int lock_id,ElectLock_State state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Lock_Rt_Table +
|
" Set record_time = NOW() " +
|
",gprs_sn = '' " +
|
",client_ip = '" + state.getClient_ip() + "'" +
|
",already_id_count = " + state.getAlready_id_count() +
|
",max_id_count = " + state.getMax_id_count() +
|
",lock_state = " + state.getLock_state() +
|
",lock_version = '" + state.getLock_version() + "'" +
|
",unlock_type = " + state.getUnlock_type() +
|
",unlock_id = " + state.getUnlock_id() +
|
",lock_addr = " + state.getLock_addr() +
|
",comm_count = " + state.getComm_count() +
|
",err_tol_count = " + state.getErr_tol_count() +
|
",err_count = " + state.getErr_count() +
|
" WHERE lock_id = " + lock_id;
|
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();
|
}
|
}
|
|
/**
|
* 更新 tb_lock_inf 表中锁的状态
|
* @param pool
|
* @param lock_id
|
* @param state
|
*/
|
public static void updateLock_Inf_Table(MysqlConnPool pool,ElectLock_Inf inf,ElectLock_State state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Lock_Inf_Table +
|
" Set lock_state = " + state.getLock_state() +
|
" ,lock_online = " + state.getLock_online() +
|
" ,lock_mac = '" + state.getLockMacAddr() + "'" +
|
" ,lock_open_count = " + state.getLock_open_count() +
|
" ,last_update_time = '" + Com.getDateTimeFormat(state.getLast_update_time(), Com.DTF_YMDhms) + "'" +
|
" WHERE lock_id = " + inf.getLock_id();
|
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 lock_id
|
* @param param
|
*/
|
public static void queryElectLockControlCmd(MysqlConnPool pool,int lock_id,ElectLock_State param) {
|
String sql_str = " SELECT op_cmd,id_card_set,lock_addr_set,ctl_uname "
|
+ " FROM " + Sql_Mysql.Lock_Rt_Table + ""
|
+ " WHERE lock_id = " + lock_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
param.setOp_cmd(res.getInt("op_cmd"));
|
param.setId_card_set(res.getInt("id_card_set"));
|
param.setLock_addr_set(res.getInt("lock_addr_set"));
|
param.setCtl_uname(res.getString("ctl_uname"));
|
|
}
|
} 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 state
|
*/
|
public static void updateElectLockControlCmd(MysqlConnPool pool, int lock_id, ElectLock_State state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Lock_Rt_Table +
|
" Set op_cmd = " + state.getOp_cmd() +
|
" WHERE lock_id = " + lock_id;
|
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 lock_id
|
* @param state
|
*/
|
public static void insertLock_Ctl_Log_Table(MysqlConnPool pool,ElectLock_Ctl_Log log) {
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Lock_Ctl_Log_Table + ""
|
+ " (lock_id,ctl_type,ctl_result,ctl_time,ctl_id_card,ctl_uname) VALUES("
|
+ "" + log.lock_id
|
+ "," + log.ctl_type + ""
|
+ "," + log.ctl_result + ""
|
+ ",'" + Com.getDateTimeFormat(log.ctl_time, Com.DTF_YMDhms) + "'"
|
+ "," + log.ctl_id_card + ""
|
+ ",'" + log.ctl_uname + "'"
|
+ ");";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str_ins);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
public static List<ElectLock_AuthIdcard> queryAuth_Idcard_Table(MysqlConnPool pool, int client_id) {
|
List<ElectLock_AuthIdcard> batch_auth = new ArrayList<>();
|
String sql_str = "SELECT DISTINCT(tb_key_inf.key_id),lock_id,state,tb_auth_idcard.create_time,key_number,ctl_uname " +
|
" FROM " + Sql_Mysql.Key_Inf_Table + "," + Sql_Mysql.Auth_Idcard_Table +
|
" WHERE tb_key_inf.key_id = tb_auth_idcard.key_id AND lock_id = " + client_id + " AND key_type = 1 ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
ElectLock_AuthIdcard auth = new ElectLock_AuthIdcard();
|
auth.key_id = res.getInt("key_id"); //'钥匙id',
|
auth.lock_id = res.getInt("lock_id"); //'锁具id',
|
auth.state = res.getInt("state"); //'1:移除id卡,2添加id卡,3已处理',
|
auth.create_time = res.getTimestamp("create_time"); //'创建时间'
|
auth.key_number = res.getString("key_number"); //ID卡号
|
|
auth.ctl_uname = res.getString("ctl_uname"); //操作人姓名
|
|
batch_auth.add(auth);
|
}
|
} 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 batch_auth;
|
}
|
|
|
/**
|
* 将当前批量操作记录移动到历史表
|
* @param pool
|
* @param tmp_auth
|
*/
|
public static void moveAuth_Idcard_Table(MysqlConnPool pool, ElectLock_AuthIdcard tmp_auth) {
|
ArrayList<String> sql_strs = new ArrayList<>();
|
String sql_str_ins = "INSERT INTO " + Sql_Mysql.Auth_Idcard_His_Table + "(key_id,lock_id,state,create_time,end_time,auth_result)" +
|
"(" +
|
" SELECT key_id,lock_id,state,create_time,NOW()," + tmp_auth.auth_result +
|
" FROM " + Sql_Mysql.Auth_Idcard_Table +
|
" WHERE lock_id = " + tmp_auth.lock_id + " AND key_id = " + tmp_auth.key_id +
|
" AND create_time = '" + Com.getDateTimeFormat(tmp_auth.create_time, Com.DTF_YMDhms) + "' " +
|
");";
|
String sql_str_del = "DELETE FROM " + Sql_Mysql.Auth_Idcard_Table + " WHERE lock_id = " + tmp_auth.lock_id + " AND key_id = " + tmp_auth.key_id + " AND create_time = '" + Com.getDateTimeFormat(tmp_auth.create_time, Com.DTF_YMDhms) + "'";
|
System.out.println(sql_str_ins);
|
sql_strs.add(sql_str_ins);
|
sql_strs.add(sql_str_del);
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.makeManualCommit(sql_strs);
|
} catch (Exception e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* 查询ID钥匙的归属人姓名
|
* @param pool
|
* @param client_id
|
* @param last_unlock_id
|
* @return
|
*/
|
public static String queryKeyInfByKeyNumberTable(MysqlConnPool pool, int keyNumber) {
|
String uname = "";
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Key_Inf_Table
|
+ " WHERE key_number = " + keyNumber;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
//System.out.println("====="+sql_str);
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
uname = res.getString("uname");
|
//System.out.println("uname:" + uname);
|
}
|
} 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 uname;
|
}
|
|
|
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 = 11001";
|
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 = 11001";
|
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 queryLock_Rt_TableByLockId(MysqlConnPool pool,int lock_id,ElectLock_State state) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Lock_Rt_Table + " WHERE lock_id = " + lock_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
state.setGprs_sn(res.getString("gprs_sn")); //'gprs模块SN码',
|
state.setClient_ip(res.getString("client_ip")); //'设备IP地址',
|
state.setAlready_id_count(res.getInt("already_id_count")); //'当前锁已授权卡数量',
|
state.setMax_id_count(res.getInt("max_id_count")); //'授权卡数量存储上限',
|
state.setLock_state(res.getInt("lock_state")); //'电子锁状态[0-关 1-开]',
|
state.setLock_version(res.getString("lock_version")); //'软件版本号',
|
state.setUnlock_type(res.getInt("unlock_type")); //'上一次开锁方式[0-485开锁 1-刷卡开锁 2-蓝牙开锁 3-DI开锁]',
|
state.setUnlock_id(res.getInt("unlock_id")); //'上一次刷开开锁卡号[仅刷开开锁时有效]',
|
state.setLock_addr(res.getInt("lock_addr")); //'设备地址',
|
state.setComm_count(res.getInt("comm_count")); //'通信计数',
|
state.setErr_tol_count(res.getInt("err_tol_count")); //'总错误计数',
|
// state.setErr_count(res.getInt("err_count")); //'连续错误计数',
|
state.setOp_cmd(res.getInt("op_cmd")); //'控制命令',
|
state.setId_card_set(res.getInt("id_card_set")); //'授权ID卡号',
|
state.setLock_addr_set(res.getInt("lock_addr_set"));; //'设置设备地址[慎用]',
|
|
}
|
} 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 queryLock_Report_TableByLockId(MysqlConnPool pool,int lock_id,ElectLock_Report state,int now_year) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Lock_Report_Table + " WHERE lock_id = " + lock_id + " AND record_year = " + now_year;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
|
state.record_year = res.getInt("record_year"); //统计年份',
|
state.month1 = res.getInt("month1"); //1月开锁次数',
|
state.month2 = res.getInt("month2"); //2月开锁次数',
|
state.month3 = res.getInt("month3"); //3月开锁次数',
|
state.month4 = res.getInt("month4"); //4月开锁次数',
|
state.month5 = res.getInt("month5"); //5月开锁次数',
|
state.month6 = res.getInt("month6"); //6月开锁次数',
|
state.month7 = res.getInt("month7"); //7月开锁次数',
|
state.month8 = res.getInt("month8"); //8月开锁次数',
|
state.month9 = res.getInt("month9"); //9月开锁次数',
|
state.month10 = res.getInt("month10"); //10月开锁次数',
|
state.month11 = res.getInt("month11"); //11月开锁次数',
|
state.month12 = res.getInt("month12"); //12月开锁次数',
|
state.quarter1 = res.getInt("quarter1"); //第一季度开锁次数',
|
state.quarter2 = res.getInt("quarter2"); //第二季度开锁次数',
|
state.quarter3 = res.getInt("quarter3"); //第三季度开锁次数',
|
state.quarter4 = res.getInt("quarter4"); //第四季度开锁次数',
|
state.year_count = res.getInt("year_count"); //本年开锁次数',
|
}
|
} 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();
|
}
|
|
}
|
}
|
