package com.data.upload;

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

import com.base.Com;
import com.data.csv.CsvData;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;

public class ResDataUpload_SQL {
	
	public static int checkDataOk(MysqlConnPool conn_pool, int bg_id, CsvData res_data) {
		int data_reason_type = 0;
		
		Calendar mcld = Calendar.getInstance();
		mcld.setTime(res_data.m_TestTime);
		
		if(false == res_data.m_DataOk) {
			data_reason_type = 7;
		}
		
		int batt_cap = 0;
		int mon_count = 0;
		float mon_vol = 0;
		if(0 == data_reason_type) {
			//******************************************************************//
			if(CsvData.DevType_CSV == res_data.m_DevType) {
				res_data.m_STDAH = batt_cap;
			}
			
			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.BattResDataInf_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(bg_id < 1) {
				data_reason_type = 6;
			} else if(mon_count != res_data.m_BattSum) {
				data_reason_type = 3;
			} else if((int)(mon_vol*10) != (int)(res_data.m_MonomerVol*10)) {
				data_reason_type = 4;
			} else {
				if((res_data.m_STDAH > 0) && (res_data.m_STDAH != batt_cap)) {
					data_reason_type = 2;
				}
			}
		}
		
		return data_reason_type;
	}
	
	public static boolean insertResData(MysqlConnPool conn_pool, int bg_id, int uid, CsvData csv_data)
	{
		boolean upload_res = false;
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		int testRecordCount = sql.getBattTestRecordCountNew(bg_id, Sql_Mysql.BattResDataInf_Table);
		Calendar ald = Calendar.getInstance();
		ald.setTime(csv_data.m_TestTime);
		String tb_name = "`db_batt_testdata`.`tb_BattResData_" + bg_id + "` ";
		try {
			//if(true == progress_event.ProgressIsRunning)
			{
				String sql_str0 = "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, "
						+ "`test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
						+ "`group_vol` FLOAT NOT NULL DEFAULT 0, "
						+ "`test_curr` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_num` INT NOT NULL DEFAULT 0, "
						+ "`mon_vol` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_tmp` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_res` FLOAT NOT NULL DEFAULT 0, "
						+ "`mon_ser` FLOAT NOT NULL DEFAULT 0, "
						+ "`conn_res` FLOAT NOT NULL DEFAULT 0, "
						+ "INDEX index_test_record_count (`test_record_count`), "
						+ "PRIMARY KEY (`num`));";
				sql.sqlMysqlExecute(sql_str0);
				
				ArrayList<String> al_sql_str = new ArrayList<String>();
				String sql_str1 = "INSERT INTO " + tb_name + " "
									+ " (BattGroupId, "
									+ "test_record_count, "
									+ "test_type, "
									+ "data_new, "
									+ "data_available, "
									+ "test_starttime, "
									+ "group_vol, "
									+ "test_curr, "
									+ "mon_num, "
									+ "mon_vol,"
									+ "mon_tmp,"
									+ "mon_res,"
									+ "mon_ser,"
									+ "conn_res)"
									+ " VALUES ";
					for(int n=0; n<csv_data.m_BattSum; n++)
					{
						if(n > 0)
							sql_str1 += ", ";
						
						sql_str1 = sql_str1
									+ "("
									+ bg_id + ", "
									+ testRecordCount + ", "
									+ csv_data.m_TestType + ", "
									+ true + ", "
									+ true + ", "
									+ "'" + Com.getDateTimeFormat(csv_data.m_TestTime, Com.DTF_YMDhms) + "', "
									+ csv_data.m_GroupVol + ", "
									+ csv_data.m_TestCurr + ", "
									+ ((n+1)*10 + 1) + ", "
									+ csv_data.MonomerVol[n] + ","
									+ csv_data.MonomerTmp[n] + ","
									+ csv_data.MonomerRes[n] + ","
									+ csv_data.MonomerSer[n] + ","
									+ csv_data.MonomerConn[n]
									+ ")";
					}
				al_sql_str.add(sql_str1);
					
				String sql_str2 = "INSERT INTO " + Sql_Mysql.BattResDataInf_Table
								+ " (BattGroupId, "
								+ "test_record_count, "
								+ "test_type, "
								+ "data_new, "
								+ "data_available, "
								+ "test_starttime, "
								+ "test_devtype, "
								+ "record_time, "
								+ "group_vol, "
								+ "test_curr, "
								+ "upload_usr_id, "
								+ "upload_client_type) "
								+ " VALUES "
								+ "("+ bg_id + ", "
								+ testRecordCount + ", "
								+ csv_data.m_TestType + ", "
								+ true + ", "
								+ true + ", "
								+ "'" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
								+ csv_data.m_DevType + ", "
								+ "'" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
								+ csv_data.m_GroupVol + ", "
								+ csv_data.m_TestCurr + ", "
								+ uid + ", "
								+ Com.UploadData_ClientType_CS_SVR + ") ";
				if(1 == testRecordCount) {
					sql_str2 = "UPDATE " + Sql_Mysql.BattResDataInf_Table + " SET "
							+ "test_type=" + csv_data.m_TestType + ", "
							+ "data_new=" + true + ", "
							+ "data_available=" + true + ", "
							+ "test_starttime='" + Com.getDateTimeFormat(ald.getTime(), Com.DTF_YMDhms) + "', "
							+ "test_devtype=" + csv_data.m_DevType + ", "
							+ "record_time='" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "', "
							+ "group_vol=" + csv_data.m_GroupVol + ", "
							+ "test_curr=" + csv_data.m_TestCurr + ", "
							+ "upload_usr_id=" + uid + ", "
							+ "upload_client_type=" + Com.UploadData_ClientType_CS_SVR + " "
							+ " WHERE BattGroupId=" + bg_id
							+ " AND test_record_count=" + testRecordCount;
				}
				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;
	}
}