package com.dev.lock.comm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import com.base.Com;
|
import com.dev.lock.data.ElectLock_ComBase;
|
import com.dev.lock.data.ElectLock_Ctl_Log;
|
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.setComm_count(res.getInt("comm_count")); //'通信计数',
|
state.setErr_tol_count(res.getInt("err_tol_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();
|
}
|
}
|
|
|
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,int lock_id,ElectLock_State state) {
|
String sql_str = "UPDATE " + Sql_Mysql.Lock_Inf_Table +
|
" Set lock_state = " + state.getLock_state() +
|
" 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 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 "
|
+ " 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"));
|
|
}
|
} 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) VALUES("
|
+ "" + log.lock_id
|
+ "," + log.ctl_type + ""
|
+ "," + log.ctl_result + ""
|
+ ",'" + Com.getDateTimeFormat(log.ctl_time, Com.DTF_YMDhms) + "'"
|
+ "," + log.ctl_id_card + ""
|
+ ");";
|
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();
|
}
|
}
|
}
|
