package com.data.upload;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import com.base.BattTestData;
import com.base.Com;
import com.data.fbo.FboData;
import com.data.fbo.FboDataInf;
import com.data.idc.IdcData;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;

public class FboDataUpload_SQL {
	
	public static int checkAndInsertFboData(MysqlConnPool conn_pool, int bg_id, int uid, String file_name)
	{
		int data_reason_type = 0;
		
		File f = new File(file_name);
		if(false == f.exists()) {
			data_reason_type = 16;
			return data_reason_type;
		}
		
		FboDataInf data_inf = new FboDataInf();
		ArrayList<FboData> al_fbo_data = new ArrayList<FboData>();
		
		if(f.getName().toUpperCase().endsWith(".FBO")) {
			FboData.checkFboFile(f, data_inf, al_fbo_data);
		} else if (f.getName().toUpperCase().endsWith(".IDC")) {
			IdcData.checkIdcFile(f, data_inf, al_fbo_data);
		}
		f.delete();
		
		Calendar mcld = Calendar.getInstance();
		mcld.set(Calendar.YEAR, 2000+data_inf.TestStartTime.year);
		mcld.set(Calendar.MONTH, data_inf.TestStartTime.month);
		mcld.set(Calendar.DATE, data_inf.TestStartTime.day);
		mcld.set(Calendar.HOUR_OF_DAY, data_inf.TestStartTime.hour);
		mcld.set(Calendar.MINUTE, data_inf.TestStartTime.minute);
		mcld.set(Calendar.SECOND, data_inf.TestStartTime.second);
		
		int batt_cap = 0;
		int mon_count = 0;
		float mon_vol = 0;
		
		String sql_bginf = "SELECT DISTINCT MonCapStd,MonCount,MonVolStd FROM " 
							+ Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
		String sql_str = "SELECT DISTINCT test_record_count FROM " + Sql_Mysql.BattTestDataInf_Table
							+ " WHERE BattGroupId=" + bg_id 
							+ " AND test_starttime='" + Com.getDateTimeFormat(mcld.getTime(), Com.DTF_YMDhms) + "'";
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			ResultSet res = sql.sqlMysqlQuery(sql_bginf);
			if(res.next()) {
				batt_cap = (int) (res.getFloat("MonCapStd"));
				mon_count = res.getInt("MonCount");
				mon_vol = res.getFloat("MonVolStd");
			}
			
			res = sql.sqlMysqlQuery(sql_str);
			if(res.next()) {
				data_reason_type = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		
		if(0 == data_reason_type) {
			if(batt_cap != data_inf.STDCap) {
				data_reason_type = 2;
			} else if(mon_count != data_inf.BattSum) {
				data_reason_type = 3;
			} else if(mon_vol != data_inf.MonomerVol) {
				data_reason_type = 4;
			}
		}
		
		if(0 == data_reason_type ) {
			if(false == insertFboData(conn_pool, bg_id, uid, data_inf, al_fbo_data)) {
				data_reason_type = 15;
			}
		}
		
		return data_reason_type;
	}
	
	private static boolean insertFboData(MysqlConnPool conn_pool, int bg_id, int uid,
										FboDataInf data_inf, ArrayList<FboData> al_fbo_data)
	{
		boolean upload_res = false;
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		int testRecordCount = sql.getBattTestRecordCountNew(bg_id, Sql_Mysql.BattTestDataInf_Table);
		Calendar ald = Calendar.getInstance();
		ald.set(Calendar.YEAR, 2000+data_inf.TestStartTime.year);
		ald.set(Calendar.MONTH, data_inf.TestStartTime.month);
		ald.set(Calendar.DATE, data_inf.TestStartTime.day);
		ald.set(Calendar.HOUR_OF_DAY, data_inf.TestStartTime.hour);
		ald.set(Calendar.MINUTE, data_inf.TestStartTime.minute);
		ald.set(Calendar.SECOND, data_inf.TestStartTime.second);
		int data_type = BattTestData.BATTSTATE_DISCHARGE;
		if(0xFD != data_inf.DataType) {
			data_type = BattTestData.BATTSTATE_CHARGE;
		}
		String tb_name = "`db_batt_testdata`.`tb_BattTestData_" + bg_id + "` ";
		String tb_stop_name = "`db_batt_testdata`.`tb_BattTestDataStop_" + bg_id + "` ";
		String tmp_tb_name = "`db_batt_testdata`.`tb_BattTestData_FBO` ";
		
		try {
			String sql_str = "CREATE TABLE IF NOT EXISTS " + tb_name
					+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
					+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
					+ "`test_record_count` INT NOT NULL DEFAULT 0, "
					+ "`test_type` INT NOT NULL DEFAULT 0, "
					+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
					+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
					+ "`record_num` INT NOT NULL DEFAULT 0, "
					+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`test_timelong` INT NOT NULL DEFAULT 0, "
					+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
					+ "`mon_num` INT NOT NULL DEFAULT 0, "
					+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "INDEX index_test_record_count (`test_record_count`), "
					+ "PRIMARY KEY (`num`));";
			sql.sqlMysqlExecute(sql_str);
			
			sql_str = "CREATE TABLE IF NOT EXISTS " + tb_stop_name
					+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
					+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
					+ "`test_record_count` INT NOT NULL DEFAULT 0, "
					+ "`test_type` INT NOT NULL DEFAULT 0, "
					+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
					+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
					+ "`record_num` INT NOT NULL DEFAULT 0, "
					+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`test_timelong` INT NOT NULL DEFAULT 0, "
					+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
					+ "`mon_num` INT NOT NULL DEFAULT 0, "
					+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "INDEX index_test_record_count (`test_record_count`), "
					+ "PRIMARY KEY (`num`));";
			sql.sqlMysqlExecute(sql_str);
			
			sql_str = "DROP TABLE IF EXISTS " + tmp_tb_name;
			sql.sqlMysqlExecute(sql_str);
			
			sql_str = "CREATE TEMPORARY TABLE IF NOT EXISTS " + tmp_tb_name 
					+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
					+ "`BattGroupId` INT NOT NULL DEFAULT 0, "
					+ "`test_record_count` INT NOT NULL DEFAULT 0, "
					+ "`test_type` INT NOT NULL DEFAULT 0, "
					+ "`data_new` BOOLEAN NOT NULL DEFAULT false, "
					+ "`data_available` BOOLEAN NOT NULL DEFAULT false, "
					+ "`record_num` INT NOT NULL DEFAULT 0, "
					+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
					+ "`test_timelong` INT NOT NULL DEFAULT 0, "
					+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
					+ "`test_cap` FLOAT NOT NULL DEFAULT 0, "
					+ "`mon_num` INT NOT NULL DEFAULT 0, "
					+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
					+ "INDEX index_test_record_count (`test_record_count`), "
					+ "PRIMARY KEY (`num`));";
			sql.sqlMysqlExecute(sql_str);
			
			int test_time_long = 0;
			FboData tmp_data = null;
			for(int cnt=0; cnt<al_fbo_data.size(); cnt++)
			{
				tmp_data = al_fbo_data.get(cnt);
				test_time_long = tmp_data.m_TestTime.hour;
				test_time_long = test_time_long*60 + tmp_data.m_TestTime.minute;
				test_time_long = test_time_long*60 + tmp_data.m_TestTime.second;
				
				if(cnt > 0)
				{
					for(int sn=0; sn<data_inf.BattSum; sn++)
					{
						if(tmp_data.SingleVol[sn] < 0.1)
						{
							tmp_data.SingleVol[sn] = al_fbo_data.get(cnt-1).SingleVol[sn];
						}
					}
				}
				
				sql_str = "INSERT INTO " + tmp_tb_name + " "
						+ "(BattGroupId, "
						+ "test_record_count, "
						+ "test_type, "
						+ "data_new, "
						+ "data_available, "
						+ "record_num, "
						+ "test_starttime, "
						+ "record_time, "
						+ "test_timelong, "
						+ "group_vol, "
						+ "test_curr, "
						+ "test_cap, "
						+ "mon_num, "
						+ "mon_vol)"
						+ " VALUES ";
				for(int n=0; n<data_inf.BattSum; n++)
				{
					if(n > 0)
						sql_str += ", ";
					
					sql_str += "("+ bg_id + ", "
								+ testRecordCount + ", "
								+ data_type + ", "
								+ true + ", "
								+ true + ", "
								+ (cnt + 1) + ", "
								+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
								+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
								+ test_time_long + ", "
								+ tmp_data.SumVoltage + ", "
								+ tmp_data.SubCurrent[0] + ", "
								+ tmp_data.SubCap[0] + ", "
								+ (n+1) + ", "
								+ tmp_data.SingleVol[n] + ")";
				}
				//System.out.println(sql_str);
				sql.sqlMysqlExecute(sql_str);
			}
			
			//-----------------------------------------------------------------//
			double test_ah = tmp_data.SubCap[0];
			test_ah = (test_ah*3600) / test_time_long; //ƽ¾ù²âÊÔµçÁ÷
			tmp_data.SubCurrent[0] = (float) test_ah;
			//-----------------------------------------------------------------//
			if(al_fbo_data.size() > 0)
			{
				ArrayList<String> al_sql_str = new ArrayList<String>();
				sql_str = "INSERT INTO " + tb_stop_name
							+ " (BattGroupId, "
							+ "test_record_count, "
							+ "test_type, "
							+ "data_new, "
							+ "data_available, "
							+ "record_num, "
							+ "test_starttime, "
							+ "record_time, "
							+ "test_timelong, "
							+ "group_vol, "
							+ "test_curr, "
							+ "test_cap, "
							+ "mon_num, "
							+ "mon_vol)"
							+ " VALUES ";
				for(int n=0; n<data_inf.BattSum; n++)
				{
					if(n > 0)
						sql_str += ", ";
					
					sql_str = sql_str
					+ "("+ bg_id + ", "
					+ testRecordCount + ", "
					+ data_type + ", "
					+ true + ", "
					+ true + ", "
					+ al_fbo_data.size() + ", "
					+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
					+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
					+ test_time_long + ", "
					+ tmp_data.SumVoltage + ", "
					+ tmp_data.SubCurrent[0] + ", "
					+ tmp_data.SubCap[0] + ", "
					+ (n+1) + ", "
					+ tmp_data.SingleVol[n] + ")";
				}
				al_sql_str.add(sql_str);
				
				String sql_str1 = "INSERT INTO " + Sql_Mysql.BattTestDataInf_Table
						+ " (BattGroupId, "
						+ "test_record_count, "
						+ "test_type, "
						+ "record_time_interval, "
						+ "data_new, "
						+ "data_available, "
						+ "record_num, "
						+ "test_starttime, "
						+ "test_starttype, "
						+ "record_time, "
						+ "test_timelong, "
						+ "test_stoptype,"
						+ "group_vol, "
						+ "test_curr, "
						+ "test_cap, "
						+ "max_monnum, "
						+ "max_monvol, "
						+ "min_monnum, "
						+ "min_monvol, "
						+ "upload_usr_id, "
						+ "upload_data_ext, "
						+ "upload_client_type ) "
						+ " VALUES "
						+ "("+ bg_id + ", "
						+ testRecordCount + ", "
						+ data_type + ", "
						+ data_inf.SaveInterval * 60 + ", "
						+ true + ", "
						+ true + ", "
						+ al_fbo_data.size() + ", "
						+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
						+ data_inf.Device + ", "
						+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
						+ test_time_long + ", "
						+ data_inf.StopType + ", "
						+ tmp_data.SumVoltage + ", "
						+ tmp_data.SubCurrent[0] + ", "
						+ tmp_data.SubCap[0] + ", "
						+ (data_inf.SMaxIndex[0]+1) + ", "
						+ data_inf.SMaxVol[0] + ", "
						+ (data_inf.SMinIndex[0]+1) + ", "
						+ data_inf.SMinVol[0] + ", "
						+ uid + ", "
						+ data_inf.SourceDataExt + ", "
						+ Com.UploadData_ClientType_CS_SVR + ") ";
				if(1 == testRecordCount) {
					sql_str1 = "UPDATE " + Sql_Mysql.BattTestDataInf_Table + " SET "
							+ "test_type=" + data_type + ", "
							+ "record_time_interval=" + data_inf.SaveInterval * 60 + ", "
							+ "data_new=" + true + ", "
							+ "data_available=" + true + ", "
							+ "record_num=" + al_fbo_data.size() + ", "
							+ "test_starttime='" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
							+ "test_starttype=" + data_inf.Device + ", "
							+ "record_time='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
							+ "test_timelong=" + test_time_long + ", "
							+ "test_stoptype=" + data_inf.StopType + ", "
							+ "group_vol=" + tmp_data.SumVoltage + ", "
							+ "test_curr=" + tmp_data.SubCurrent[0] + ", "
							+ "test_cap=" + tmp_data.SubCap[0] + ", "
							+ "max_monnum=" + (data_inf.SMaxIndex[0]+1) + ", "
							+ "max_monvol=" + data_inf.SMaxVol[0] + ", "
							+ "min_monnum=" + (data_inf.SMinIndex[0]+1) + ", "
							+ "min_monvol=" + data_inf.SMinVol[0] + ", "
							+ "upload_usr_id=" + uid + ", "
							+ "upload_data_ext=" + data_inf.SourceDataExt + ", "
							+ "upload_client_type=" + Com.UploadData_ClientType_CS_SVR + " "
							+ " WHERE BattGroupId=" + bg_id
							+ " AND test_record_count=" + testRecordCount;
				}
				al_sql_str.add(sql_str1);
				
				String sql_str2 = "INSERT INTO " + tb_name + " "
									+ " (BattGroupId, "
									+ "test_record_count, "
									+ "test_type, "
									+ "data_new, "
									+ "data_available, "
									+ "record_num, "
									+ "test_starttime, "
									+ "record_time, "
									+ "test_timelong, "
									+ "group_vol, "
									+ "test_curr, "
									+ "test_cap, "
									+ "mon_num, "
									+ "mon_vol) "
									+ " SELECT "
									+ " BattGroupId, "
									+ "test_record_count, "
									+ "test_type, "
									+ "data_new, "
									+ "data_available, "
									+ "record_num, "
									+ "test_starttime, "
									+ "record_time, "
									+ "test_timelong, "
									+ "group_vol, "
									+ "test_curr, "
									+ "test_cap, "
									+ "mon_num, "
									+ "mon_vol "
									+ " FROM " + tmp_tb_name;
				al_sql_str.add(sql_str2);
				
				upload_res = sql.makeManualCommit(al_sql_str);
			}
		} catch (Exception e) {
			e.printStackTrace();
			upload_res = false;
		} finally {
			sql.close_con();
		}
		
		return upload_res;
	}
}