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