package com.dev.fbs9600;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
|
public class FBS9600_State_SQL {
|
public static void createFBS9600StateTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String str1 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_fbs9600_state`";
|
String str2 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_fbs9600_state` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', `dev_id` BIGINT NOT NULL DEFAULT 0, `op_cmd` INT NOT NULL DEFAULT 0, `dev_workstate` INT NOT NULL DEFAULT 0, `dev_alarmstate` INT NOT NULL DEFAULT 0, `dev_commcount` INT NOT NULL DEFAULT 0, `dev_errcommcount` INT NOT NULL DEFAULT 0, PRIMARY KEY (`num`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
String str3 = "";
|
if (al_stat.size() > 0) {
|
str3 = "INSERT INTO `db_ram_db`.`tb_fbs9600_state` (dev_ip, dev_id) VALUES";
|
}
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
str3 = str3 + ", ";
|
}
|
str3 = str3 + " ('" + ((FBS9600_State) al_stat.get(n)).ip_addr + "',"
|
+ ((FBS9600_State) al_stat.get(n)).dev_id + ")";
|
}
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
if (str3.length() > 0)
|
sql.sqlMysqlExecute(str3);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9600State(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "UPDATE `db_ram_db`.`tb_fbs9600_state` SET dev_workstate = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).work_state;
|
}
|
sql_str = sql_str + " END, dev_commcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_count;
|
}
|
sql_str = sql_str + " END, dev_errcommcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_err_count;
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
sql_str = sql_str + ",";
|
}
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
}
|
sql_str = sql_str + ")";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
String sql_str2 = "INSERT IGNORE INTO `db_ram_db`.`tb_fbs9600_state` ( dev_ip, dev_id, op_cmd, dev_workstate, dev_alarmstate, dev_commcount, dev_errcommcount) VALUES('127.0.0',"
|
+ ((FBS9600_State) al_stat.get(0)).dev_id + "," + '0' + "," + '0' + "," + '0' + "," + '0' + "," + '0'
|
+ ")";
|
try {
|
sql.sqlMysqlExecute(sql_str2);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9100State(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
|
|
|
String sql_str = "UPDATE "+Sql_Mysql.FBS9100State_Table+" SET dev_workstate = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).work_state;
|
}
|
sql_str = sql_str + " END, record_datetime = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN '"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"'";
|
}
|
sql_str = sql_str + " END, dev_commcount = CASE dev_id ";
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_count;
|
}
|
sql_str = sql_str + " END, dev_errcommcount = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN "
|
+ ((FBS9600_State) al_stat.get(n)).com_err_count;
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (n > 0) {
|
sql_str = sql_str + ",";
|
}
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
}
|
sql_str = sql_str + ")";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
String sql_str2 = "INSERT IGNORE INTO "+Sql_Mysql.FBS9100State_Table+" ( dev_ip, dev_id, op_cmd, dev_workstate, dev_alarmstate, dev_commcount, dev_errcommcount) VALUES('127.0.0',"
|
+ ((FBS9600_State) al_stat.get(0)).dev_id + "," + '0' + "," + '0' + "," + '0' + "," + '0' + "," + '0'
|
+ ")";
|
try {
|
sql.sqlMysqlExecute(sql_str2);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
//System.err.println(e);
|
} finally {
|
//System.out.println("############");
|
sql.close_con();
|
}
|
}
|
|
public static void queryFbs9600UsrCmd(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "SELECT dev_id,op_cmd FROM " + Sql_Mysql.FBS9100SetParam_Table;
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
while (res.next()) {
|
int id = res.getInt("dev_id");
|
int cmd = res.getInt("op_cmd");
|
for (int n = 0; n < al_stat.size(); n++)
|
if (id == ((FBS9600_State) al_stat.get(n)).dev_id) {
|
if ((((FBS9600_State) al_stat.get(n)).getFBS9600Cmd() != 0)
|
&& (82 != ((FBS9600_State) al_stat.get(n)).getFBS9600Cmd()))
|
break;
|
((FBS9600_State) al_stat.get(n)).setFBS9600CmdAckFromDev(false);
|
((FBS9600_State) al_stat.get(n)).setFBS9600Cmd(cmd);
|
|
break;
|
}
|
}
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void updateFbs9600UsrCmdAck(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat) {
|
String sql_str = "UPDATE "+Sql_Mysql.FBS9100SetParam_Table+" SET ";
|
|
sql_str = sql_str + " op_cmd = CASE dev_id ";
|
for (int n = 0; n < al_stat.size(); n++) {
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
sql_str = sql_str + " WHEN " + ((FBS9600_State) al_stat.get(n)).dev_id + " THEN " + 82;
|
}
|
}
|
sql_str = sql_str + " END WHERE dev_id IN (";
|
int count = 0;
|
for (int n = 0; n < al_stat.size(); n++) {
|
if (count > 0) {
|
sql_str = sql_str + ",";
|
}
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
sql_str = sql_str + ((FBS9600_State) al_stat.get(n)).dev_id;
|
count++;
|
}
|
}
|
sql_str = sql_str + ")";
|
|
if (count > 0) {
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException localSQLException) {
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
for (int n = 0; n < al_stat.size(); n++) {
|
FBS9600_State stat = (FBS9600_State) al_stat.get(n);
|
if ((stat.getFBS9600Cmd() != 0) && (stat.getFBS9600CmdAckFromDev())) {
|
stat.setFBS9600Cmd(0);
|
stat.setFBS9600CmdAckFromDev(false);
|
}
|
}
|
}
|
|
public static void insertFBS9100SetParam_Table(MysqlConnPool pool,int dev_id) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.FBS9100SetParam_Table + " Where dev_id = " + dev_id;
|
String sql_str_ins = " insert into " + Sql_Mysql.FBS9100SetParam_Table + "(dev_id) values("+dev_id+")";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(!res.next()) {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
}
|
