package com.power.datasave;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Calendar;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.power.data.PowerData_RT;
|
import com.power.data.PowerDev_acdcData;
|
import com.power.mysql.MysqlConnPool;
|
import com.power.mysql.Sql_Mysql;
|
|
public class PowerDataSave_SQL {
|
|
public static void Create_PowerHistoryDataTable(MysqlConnPool pool, int PowerDeviceId, Date time) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.PWRDEV_HISTORY_DATA_TABLE+PowerDeviceId+"_"+Com.getDateTimeFormat(time, Com.DTF_YM) + "`"
|
+ " ( `num` bigint NOT NULL AUTO_INCREMENT ,"
|
+ "`PowerDeviceId` bigint NOT NULL DEFAULT 0 ,"
|
+ "`record_time` datetime NOT NULL DEFAULT '2005-1-1 00:00:00' ,";
|
|
//**************** AC *********************
|
String ac_sql_str = "`AC_acIn1_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn1_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn1_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn1_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn1_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn1_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acIn2_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_temprature` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acVolH_Limit` float NOT NULL DEFAULT 0 ,"
|
+ "`AC_acVolL_Limit` float NOT NULL DEFAULT 0 ,";
|
|
//**************** ACDC ***********************
|
String acdc_sql_str = "`ACDC_acIn1_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acIn1_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acIn1_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acIn2_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acIn2_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acIn2_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acOut_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_dcOut_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_loader_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_battgroup1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_battgroup1_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_battgroup2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_battgroup2_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acdcm_temp` float NOT NULL DEFAULT 0 ,";
|
|
String tmpstr;
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "`ACDC_m"+(i+1)+"_outCurr` "+" float NOT NULL DEFAULT 0 ,";
|
acdc_sql_str += tmpstr;
|
}
|
|
acdc_sql_str += "`ACDC_chargLimitCurr` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_junChargeVol` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_floatChargeVol` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acVolH_Limit` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_acVolL_Limit` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_dcOutVolH_Limit` float NOT NULL DEFAULT 0 ,"
|
+ "`ACDC_dcOutVolL_Limit` float NOT NULL DEFAULT 0 ,";
|
|
//******************* DC *******************
|
String dc_sql_str = "`DC_dcIn1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcIn2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcOut1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcOut1_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcOut2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcOut2_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_temprature` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcVolH_Limit` float NOT NULL DEFAULT 0 ,"
|
+ "`DC_dcVolL_Limit` float NOT NULL DEFAULT 0 ,";
|
|
sql_str += ac_sql_str + acdc_sql_str + dc_sql_str
|
+ " PRIMARY KEY (`num`),"
|
+ " KEY `index_powerdeviceid` (`PowerDeviceId`) USING BTREE,"
|
+ " KEY `index_record_time` (`record_time`) USING BTREE"
|
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
//System.out.println(sql_str);
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error("Create_PowerHistoryDataTable():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static void deleteHistoryData(MysqlConnPool conn_pool, Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_pwrdev_data_history' " +
|
" AND TABLE_NAME like 'tb_pwrdev_historydata_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
int count = 0;
|
res = sql.sqlMysqlTotalQuery(sql_select_strs);
|
try {
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs += ",";
|
}
|
|
sql_delete_strs += "db_pwrdev_data_history." + res.getString("TABLE_NAME");
|
sql.logger.warn("ɾ³ý£º"+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count > 0) {
|
sql.sqlMysqlTotalExecute(sql_delete_strs);
|
}
|
|
res.close();
|
} catch (SQLException e) {
|
sql.logger.error("deleteHistoryData():" + e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
public static void RecordPowerRealData(MysqlConnPool pool,PowerData_RT rtdata) {
|
|
Date time = new Date();
|
|
Create_PowerHistoryDataTable(pool,rtdata.PowerDevDeviceId,time);
|
|
String sql_str = " INSERT INTO " + Sql_Mysql.PWRDEV_HISTORY_DATA_TABLE + rtdata.PowerDevDeviceId + "_" + Com.getDateTimeFormat(time, Com.DTF_YM) + "`";
|
|
String sql_str_target = " (PowerDeviceId,record_time,";
|
|
//**************** AC *********************
|
String ac_sql_str = "AC_acIn1_volA,"
|
+ "AC_acIn1_volB,"
|
+ "AC_acIn1_volC,"
|
+ "AC_acIn1_currA,"
|
+ "AC_acIn1_currB,"
|
+ "AC_acIn1_currC,"
|
+ "AC_acIn2_volA,"
|
+ "AC_acIn2_volB,"
|
+ "AC_acIn2_volC,"
|
+ "AC_acIn2_currA,"
|
+ "AC_acIn2_currB,"
|
+ "AC_acIn2_currC,"
|
+ "AC_temprature,"
|
+ "AC_acVolH_Limit,"
|
+ "AC_acVolL_Limit,";
|
|
//**************** ACDC ***********************
|
String acdc_sql_str = "ACDC_acIn1_volA,"
|
+ "ACDC_acIn1_volB,"
|
+ "ACDC_acIn1_volC,"
|
+ "ACDC_acIn2_volA,"
|
+ "ACDC_acIn2_volB,"
|
+ "ACDC_acIn2_volC,"
|
+ "ACDC_acOut_volA,"
|
+ "ACDC_acOut_volB,"
|
+ "ACDC_acOut_volC,"
|
+ "ACDC_acOut_currA,"
|
+ "ACDC_acOut_currB,"
|
+ "ACDC_acOut_currC,"
|
+ "ACDC_dcOut_vol,"
|
+ "ACDC_loader_curr,"
|
+ "ACDC_battgroup1_vol,"
|
+ "ACDC_battgroup1_curr,"
|
+ "ACDC_battgroup2_vol,"
|
+ "ACDC_battgroup2_curr,"
|
+ "ACDC_acdcm_temp,";
|
|
String tmpstr;
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "ACDC_m"+(i+1)+"_outCurr,";
|
acdc_sql_str += tmpstr;
|
}
|
|
acdc_sql_str += "ACDC_chargLimitCurr,"
|
+ "ACDC_junChargeVol,"
|
+ "ACDC_floatChargeVol,"
|
+ "ACDC_acVolH_Limit,"
|
+ "ACDC_acVolL_Limit,"
|
+ "ACDC_dcOutVolH_Limit,"
|
+ "ACDC_dcOutVolL_Limit,";
|
|
//******************* DC *******************
|
String dc_sql_str = "DC_dcIn1_vol,"
|
+ "DC_dcIn2_vol,"
|
+ "DC_dcOut1_vol,"
|
+ "DC_dcOut1_curr,"
|
+ "DC_dcOut2_vol,"
|
+ "DC_dcOut2_curr,"
|
+ "DC_temprature,"
|
+ "DC_dcVolH_Limit,"
|
+ "DC_dcVolL_Limit";
|
|
|
sql_str_target += ac_sql_str + acdc_sql_str + dc_sql_str + ") VALUES " ;
|
|
String sql_values = "(" + rtdata.PowerDevDeviceId +"," + "'" + Com.getDateTimeFormat(new Date(),Com.DTF_YMDhms ) + "',";
|
|
String sql_ac_value = rtdata.m_acData.acIn1_volA + ","
|
+ rtdata.m_acData.acIn1_volB + ","
|
+ rtdata.m_acData.acIn1_volC + ","
|
+ rtdata.m_acData.acIn1_currA + ","
|
+ rtdata.m_acData.acIn1_currB + ","
|
+ rtdata.m_acData.acIn1_currC + ","
|
+ rtdata.m_acData.acIn2_volA + ","
|
+ rtdata.m_acData.acIn2_volB + ","
|
+ rtdata.m_acData.acIn2_volC + ","
|
+ rtdata.m_acData.acIn2_currA + ","
|
+ rtdata.m_acData.acIn2_currB + ","
|
+ rtdata.m_acData.acIn2_currC + ","
|
+ rtdata.m_acData.temprature + ","
|
+ rtdata.m_acData.acVolH_Limit + ","
|
+ rtdata.m_acData.acVolL_Limit + ",";
|
|
String sql_acdc_value = rtdata.m_acdcData.acIn1_volA + ","
|
+ rtdata.m_acdcData.acIn1_volB + ","
|
+ rtdata.m_acdcData.acIn1_volC + ","
|
+ rtdata.m_acdcData.acIn2_volA + ","
|
+ rtdata.m_acdcData.acIn2_volB + ","
|
+ rtdata.m_acdcData.acIn2_volC + ","
|
+ rtdata.m_acdcData.acOut_volA + ","
|
+ rtdata.m_acdcData.acOut_volB + ","
|
+ rtdata.m_acdcData.acOut_volC + ","
|
+ rtdata.m_acdcData.acOut_currA + ","
|
+ rtdata.m_acdcData.acOut_currB + ","
|
+ rtdata.m_acdcData.acOut_currC + ","
|
+ rtdata.m_acdcData.dcOut_vol + ","
|
+ rtdata.m_acdcData.loader_curr + ","
|
+ rtdata.m_acdcData.battgroup1_vol + ","
|
+ rtdata.m_acdcData.battgroup1_curr + ","
|
+ rtdata.m_acdcData.battgroup2_vol + ","
|
+ rtdata.m_acdcData.battgroup2_curr + ","
|
+ rtdata.m_acdcData.acdcm_temp + ",";
|
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
sql_acdc_value += rtdata.m_acdcData.m_out_curr[i] + ",";
|
}
|
|
sql_acdc_value += rtdata.m_acdcData.chargLimitCurr + ","
|
+ rtdata.m_acdcData.junChargeVol + ","
|
+ rtdata.m_acdcData.floatChargeVol + ","
|
+ rtdata.m_acdcData.acVolH_Limit + ","
|
+ rtdata.m_acdcData.acVolL_Limit + ","
|
+ rtdata.m_acdcData.dcOutVolH_Limit + ","
|
+ rtdata.m_acdcData.dcOutVolL_Limit + ",";
|
|
|
String sql_dc_value = rtdata.m_dcData.dcIn1_vol + ","
|
+ rtdata.m_dcData.dcIn2_vol + ","
|
+ rtdata.m_dcData.dcOut1_vol + ","
|
+ rtdata.m_dcData.dcOut1_curr + ","
|
+ rtdata.m_dcData.dcOut2_vol + ","
|
+ rtdata.m_dcData.dcOut2_curr + ","
|
+ rtdata.m_dcData.temprature + ","
|
+ rtdata.m_dcData.dcVolH_Limit + ","
|
+ rtdata.m_dcData.dcVolL_Limit + ")";
|
|
|
sql_values += sql_ac_value + sql_acdc_value + sql_dc_value;
|
|
sql_str += sql_str_target + sql_values;
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
|
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
sql.logger.error(e.toString(), e);
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
public static Date getDateBefore(Date d,int day){
|
Calendar now =Calendar.getInstance();
|
now.setTime(d);
|
now.set(Calendar.DATE,now.get(Calendar.DATE)-day);
|
|
return now.getTime();
|
}
|
|
}
|
