V1.111 edit by lijun 2025-03-13 1.新增锁具工作模式读取 2.新增根据平台锁具模式设置当前所有锁具的模式

DELL

2025-03-13 11a814e836495cb0de25eaa670c75338d85e5e42

 ElectLock_Monitor/src/com/dev/lock/comm/Lock_Task_SQL.java

@@ -10,6 +10,8 @@
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;

@@ -25,8 +27,12 @@
      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);

         }

@@ -77,10 +83,15 @@
    * @param lock_id

    * @param state

    */

   public static void updateLock_Inf_Table(MysqlConnPool pool,int lock_id,ElectLock_State 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() +

                  " WHERE lock_id = " + lock_id;

                  " ,lock_online =  " + state.getLock_online() +

                  " ,lock_mac =  '" + state.getLockMacAddr() + "'" +

                  " ,lock_open_count =  " + state.getLock_open_count() + 
                  " ,model =  " + state.getWorkmodel() + 
                  " ,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);

@@ -99,7 +110,7 @@
    * @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 "

      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());

@@ -110,6 +121,7 @@
            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) {

@@ -156,12 +168,13 @@
    */

   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("

                     + "   (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 {

@@ -176,7 +189,7 @@


   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 " + 
      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());

@@ -190,6 +203,8 @@
            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);

         }

@@ -236,4 +251,199 @@
         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();

      }

		
   }

	
	
   /**

    *    当平台锁具模式未配置时返回-1

    *    返回0-离线模式   1-在线模式

    * @param pool

    * @return

    */

   public static int querySystemLockMode(MysqlConnPool pool) {

      int sys_model = -1;

      String sql_str_sel = "SELECT plate_model FROM " + Sql_Mysql.Plate_Info_Table;

      Sql_Mysql sql = new Sql_Mysql(pool.getConn());

      ResultSet res = null;

      try {

         res = sql.sqlMysqlQuery(sql_str_sel);

         if(res.next()) {

            sys_model = res.getInt("plate_model");

         }

      } 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 sys_model;

   }

	
   /**

    * 插入远程开锁失败记录

    */

   public static void insertRemoteOpenFailAlarm(MysqlConnPool pool,int lock_id) {

      String sql_str = "INSERT INTO " + Sql_Mysql.Lock_Alm_Temp_Table + "(lock_id,alm_source,alm_start_time) VALUES(" + lock_id + "," + ElectLock_ComBase.ALM_Source_PlatForm + ",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();

      }

   }

}