package com.dev.fbs9600;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class FBS9600_State_SQL {
|
|
/**
|
* ´´½¨tb_fbs9600_stateÊý¾Ý¿â±í£¬²¢ÇÒ¸ù¾Ýal_stat¼¯ºÏ½«É豸idºÍip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_stat
|
*/
|
public static void createFBS9600StateTableOnRam(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9600State_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9600State_Table
|
+ " ( `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 " + Sql_Mysql.FBS9600State_Table
|
+ " (dev_ip, dev_id) VALUES";
|
}
|
for(int n=0; n<al_stat.size(); n++)
|
{
|
if(n > 0) {
|
str3 += ", ";
|
}
|
str3 += " ('" + al_stat.get(n).ip_addr + "'," + 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) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/***********************************************************************************/
|
/***********************************************************************************/
|
public static void updateFbs9600State(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat)
|
{
|
String sql_str = "UPDATE " + Sql_Mysql.FBS9600State_Table
|
+ " SET "
|
+ " dev_workstate = CASE dev_id ";
|
for(int n=0; n<al_stat.size(); n++) {
|
sql_str += " WHEN " + al_stat.get(n).dev_id
|
+ " THEN " + al_stat.get(n).work_state;
|
}
|
sql_str += " END, " + " dev_commcount = CASE dev_id ";
|
for(int n=0; n<al_stat.size(); n++) {
|
sql_str += " WHEN " + al_stat.get(n).dev_id
|
+ " THEN " + al_stat.get(n).com_count;
|
}
|
sql_str += " END, " + " dev_errcommcount = CASE dev_id ";
|
for(int n=0; n<al_stat.size(); n++) {
|
sql_str += " WHEN " + al_stat.get(n).dev_id
|
+ " THEN " + al_stat.get(n).com_err_count;
|
}
|
sql_str += " END WHERE dev_id IN (";
|
for(int n=0; n<al_stat.size(); n++) {
|
if(n > 0) {
|
sql_str += ",";
|
}
|
sql_str += al_stat.get(n).dev_id;
|
}
|
sql_str += ")";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
//e.printStackTrace();
|
} finally {
|
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.FBS9600State_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 == al_stat.get(n).dev_id) {
|
if(FBS9600_State.UsrCmdNull == al_stat.get(n).getFBS9600Cmd()) {
|
al_stat.get(n).setFBS9600CmdAckFromDev(false);
|
al_stat.get(n).setFBS9600Cmd(cmd);
|
}
|
|
break;
|
}
|
}
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
//e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/***********************************************************************************/
|
public static void updateFbs9600UsrCmdAck(MysqlConnPool con_pool, ArrayList<FBS9600_State> al_stat)
|
{
|
String sql_str = "UPDATE " + Sql_Mysql.FBS9600State_Table
|
+ " SET ";
|
sql_str += " op_cmd = CASE dev_id ";
|
for(int n=0; n<al_stat.size(); n++) {
|
FBS9600_State stat = al_stat.get(n);
|
if((FBS9600_State.UsrCmdNull != stat.getFBS9600Cmd()) && (true == stat.getFBS9600CmdAckFromDev()))
|
{
|
sql_str += " WHEN " + al_stat.get(n).dev_id
|
+ " THEN " + FBS9600_State.UsrCmdNull;
|
}
|
}
|
sql_str += " END WHERE dev_id IN (";
|
int count = 0;
|
for(int n=0; n<al_stat.size(); n++) {
|
if(count > 0) {
|
sql_str += ",";
|
}
|
FBS9600_State stat = al_stat.get(n);
|
if((FBS9600_State.UsrCmdNull != stat.getFBS9600Cmd()) && (true == stat.getFBS9600CmdAckFromDev()))
|
{
|
sql_str += al_stat.get(n).dev_id;
|
count += 1;
|
}
|
}
|
sql_str += ")";
|
|
if(count > 0) {
|
//System.out.println(sql_str);
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
//e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
for(int n=0; n<al_stat.size(); n++) {
|
FBS9600_State stat = al_stat.get(n);
|
if((FBS9600_State.UsrCmdNull != stat.getFBS9600Cmd()) && (true == stat.getFBS9600CmdAckFromDev()))
|
{
|
stat.setFBS9600Cmd(FBS9600_State.UsrCmdNull);
|
stat.setFBS9600CmdAckFromDev(false);
|
}
|
}
|
}
|
}
|
