package com.data.Comm;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
import com.base.Com;
|
import com.data.Central_Control;
|
import com.data.Central_RT_Data;
|
import com.data.Central_ST_Data;
|
import com.data.Motor_inf;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Central_DataParsing_Thread_SQL {
|
|
/**
|
* ²åÈë»ò¸üÐÂÉ豸ͨѶ״̬
|
* @param pool
|
* @param motor
|
*/
|
public static void insertOrUpdateTb_Device_StateTable(MysqlConnPool pool,Motor_inf motor) {
|
String sql_str_sel = " Select dev_id from " + Sql_Mysql.Tb_Device_State + " Where dev_id = " + motor.getDev_id();
|
String sql_str_upd = " UPDATE " + Sql_Mysql.Tb_Device_State + " SET comm_status = " + motor.conn_state + " WHERE dev_id = " + motor.getDev_id();
|
String sql_str_ins = " INSERT INTO "+Sql_Mysql.Tb_Device_State+"(dev_id,comm_status) VALUES("+motor.getDev_id()+","+motor.conn_state+");";
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üÐÂÉ豸µÄͨѶ״̬
|
* @param pool
|
* @param motor
|
*/
|
public static void updateTb_Device_StateTable(MysqlConnPool pool,Motor_inf motor) {
|
String sql_str = " UPDATE "+Sql_Mysql.Tb_Device_State+" SET comm_status = "+motor.conn_state+",record_time='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = "+motor.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ¼ÓÔؼ¯Öмà¿Øƽ̨É豸É豸ÐÅÏ¢
|
* @param pool
|
* @param motors
|
*/
|
public static void loadDeviceData(MysqlConnPool pool,List<Motor_inf> motors) {
|
String sql_str = " SELECT * FROM " + Sql_Mysql.Tb_MW_Motor_inf + " WHERE sys_id = 100001";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
Motor_inf motor = new Motor_inf(res.getInt("dev_id"));
|
motor.setDev_ip(res.getString("dev_ip"));
|
motor.setDev_name(res.getString("dev_name"));
|
motor.setSys_id(res.getInt("sys_id"));
|
motor.setSys_name(res.getString("sys_name"));
|
motor.setNote(res.getString("note"));
|
motors.add(motor);
|
}
|
} 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 stdata
|
*/
|
public static void insertOrUpdateTb_Central_Monitor_Sys_St(MysqlConnPool pool,Central_ST_Data stdata) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.Tb_Central_Monitor_Sys_St + " where dev_id = " + stdata.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.Tb_Central_Monitor_Sys_St + "(dev_id) values("+stdata.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.Tb_Central_Monitor_Sys_St + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',switch_close_1st_2500A = " + stdata.getSwitch_close_1st_2500A()
|
+ ",switch_open_1st_2500A = " + stdata.getSwitch_open_1st_2500A()
|
+ ",switch_fault_1st_2500A = " + stdata.getSwitch_fault_1st_2500A()
|
+ ",switch_close_2st_2500A = " + stdata.getSwitch_close_2st_2500A()
|
+ ",switch_open_2st_2500A = " + stdata.getSwitch_open_2st_2500A()
|
+ ",switch_fault_2st_2500A = " + stdata.getSwitch_fault_2st_2500A()
|
+ ",switch_close_bus_screen = " + stdata.getSwitch_close_bus_screen()
|
+ ",switch_open_bus_screen = " + stdata.getSwitch_open_bus_screen()
|
+ ",switch_fault_bus_screen = " + stdata.getSwitch_fault_bus_screen()
|
+ ",switch_close_1st_2500A_load = " + stdata.getSwitch_close_1st_2500A_load()
|
+ ",switch_open_1st_2500A_load = " + stdata.getSwitch_open_1st_2500A_load()
|
+ ",switch_fault_1st_2500A_load = " + stdata.getSwitch_fault_1st_2500A_load()
|
+ ",switch_close_2st_2500A_load = " + stdata.getSwitch_close_2st_2500A_load()
|
+ ",switch_open_2st_2500A_load = " + stdata.getSwitch_open_2st_2500A_load()
|
+ ",switch_fault_2st_2500A_load = " + stdata.getSwitch_fault_2st_2500A_load()
|
+ ",curr_a = " + stdata.getCurr_a()
|
+ ",vol_a = " + stdata.getVol_a()
|
+ ",curr_b = " + stdata.getCurr_b()
|
+ ",vol_b = " + stdata.getVol_b()
|
+ " Where dev_id = " + stdata.getDev_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_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception 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 rtdata
|
*/
|
public static void insertOrUpdateTb_Central_Monitor_Sys_Rt(MysqlConnPool pool,Central_RT_Data rtdata) {
|
String sql_str_sel = " select dev_id from " + Sql_Mysql.Tb_Central_Monitor_Sys_Rt + " where dev_id = " + rtdata.getDev_id();
|
String sql_str_ins = " insert into " + Sql_Mysql.Tb_Central_Monitor_Sys_Rt + "(dev_id) values("+rtdata.getDev_id()+")";
|
String sql_str_upd = " update " + Sql_Mysql.Tb_Central_Monitor_Sys_Rt + ""
|
+ " SET record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)
|
+ "',switch_close = " + rtdata.getSwitch_close()
|
+ ",switch_open = " + rtdata.getSwitch_open()
|
+ ",switch_fault = " + rtdata.getSwitch_fault()
|
+ ",panel_vol = " + rtdata.getPanel_vol()
|
+ ",panel_curr = " + rtdata.getPanel_curr()
|
+ " Where dev_id = " + rtdata.getDev_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_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception 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 control
|
*/
|
public static void queryTb_Central_Monitor_Sys_Control(MysqlConnPool pool,Central_Control control) {
|
String sql_str = " SELECT * FROM "+Sql_Mysql.Tb_Central_Monitor_Sys_Control+" WHERE dev_id = " + control.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
control.setOp_cmd(res.getInt("op_cmd"));
|
control.setSwitch_close_1st_2500A(res.getInt("switch_close_1st_2500A"));
|
control.setSwitch_close_1st_2500A_load(res.getInt("switch_close_1st_2500A_load"));
|
control.setSwitch_close_2st_2500A(res.getInt("switch_close_2st_2500A"));
|
control.setSwitch_close_2st_2500A_load(res.getInt("switch_close_2st_2500A_load"));
|
control.setSwitch_close_bus_screen(res.getInt("switch_close_bus_screen"));
|
control.setSwitch_open_bus_screen(res.getInt("switch_open_bus_screen"));
|
control.setSwitch_open_1st_2500A(res.getInt("switch_open_1st_2500A"));
|
control.setSwitch_open_1st_2500A_load(res.getInt("switch_open_1st_2500A_load"));
|
control.setSwitch_open_2st_2500A(res.getInt("switch_open_2st_2500A"));
|
control.setSwitch_open_2st_2500A_load(res.getInt("switch_open_2st_2500A_load"));
|
}
|
} catch (Exception 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 control
|
*/
|
public static void updateTb_Central_Monitor_Sys_Control(MysqlConnPool pool,Central_Control control) {
|
if(control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchClose_1st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchOpen_1st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchClose_2st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchOpen_2st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchClose_3st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchOpen_3st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchClose_4st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchOpen_4st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchClose_5st_ACK ||
|
control.getOp_cmd() == Central_Data_ComBase.CMD_SwitchOpen_5st_ACK ) {
|
String sql_str = " UPDATE "+Sql_Mysql.Tb_Central_Monitor_Sys_Control+" SET op_cmd = "+control.getOp_cmd()+" WHERE dev_id = " + control.getDev_id();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (Exception e) {
|
sql.logger.error(e.toString(),e);
|
} finally {
|
sql.close_con();
|
}
|
}else {
|
control.setOp_cmd(Central_Data_ComBase.CMD_Null);
|
}
|
}
|
}
|
