package com.dev.lock.alarm;

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

import com.base.Com;
import com.dev.lock.alarm.data.ElectLock_AlarmData;
import com.dev.lock.data.ElectLock_Inf;
import com.dev.lock.data.ElectLock_State;
import com.sql.MysqlConnPool;
import com.sql.Sql_Mysql;

public class ElectLock_Alarm_Thread_SQL {

	public static void updateProcess_StartTime(MysqlConnPool pool, double mVersionnum) {
		String sql_str = "UPDATE " + Sql_Mysql.Process_Survey_Table + 
				" Set Process_starttime = NOW()  " +
				" ,ProcessTime = NOW() " + 
				" ,ProcessVersion = 'V" + mVersionnum + "'"+
				" WHERE ProcessId = 11002";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			sql.logger.error(e.toString(),e);
		} finally {
			sql.close_con();
		}
	}
	
	public static void updateProcess_ProcessTime(MysqlConnPool pool) {
		String sql_str = "UPDATE " + Sql_Mysql.Process_Survey_Table + 
				" Set ProcessTime = NOW() " + 
				" WHERE ProcessId = 11002";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			sql.logger.error(e.toString(),e);
		} finally {
			sql.close_con();
		}
	}

	/**
	 * 查询当前锁具的实时状态
	 * @param pool
	 * @param inf
	 */
	public static void queryElectLockState(MysqlConnPool pool, ElectLock_Inf inf) {
		String sql_str_sql = "SELECT record_time,lock_state,now() as now_time FROM " + Sql_Mysql.Lock_Rt_Table + " WHERE lock_id = " + inf.getLock_id();
		ResultSet res = null;
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		res = sql.sqlMysqlQuery(sql_str_sql);
		try {
			ElectLock_State state = inf.lockState;
			if(res.next()) {
				
				
				state.setRecord_time(res.getTimestamp("record_time"));		//更新时间lock_state
				state.setLock_state(res.getInt("lock_state"));				//'电子锁状态[0-关 1-开]',
				state.setNow_time(res.getTimestamp("now_time"));            //当前时间	
			}else {
				state.setNow_time(new Date());            //当前时间	
			}
		} 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 lock_id
	 * @param almId
	 * @return
	 */
	public static ElectLock_AlarmData queryElectLockAlarmByAlmId(MysqlConnPool pool,int lock_id, int almId) {
		ElectLock_AlarmData almData = null;
		String sql_str = " SELECT lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,alm_end_time"
						+ " FROM " + Sql_Mysql.Lock_Alarm_Table 
						+ " WHERE lock_id = " + lock_id + " AND alm_id = " + almId;
		ResultSet res = null;
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			//System.out.println(sql_str);
			res = sql.sqlMysqlQuery(sql_str);
			if(res.next()) {
				almData = new ElectLock_AlarmData();
				
				almData.setLock_id(res.getInt("lock_id"));
				almData.setAlm_id(res.getInt("alm_id"));
				almData.setAlm_source(res.getInt("alm_source"));						//'告警来源[1-平台触发  2-手机APP触发  3-锁具触发]',
				almData.setAlm_start_time(res.getTimestamp("alm_start_time"));			//'告警开始时间',
				almData.setAlm_is_confirmed(res.getInt("alm_is_confirmed")); 			//'告警是否确认',
				almData.setAlm_confirmed_time(res.getTimestamp("alm_confirmed_time"));	//'告警确认时间',
				almData.setAlm_end_time(res.getTimestamp("alm_end_time"));  			//'告警结束时间',
				
			}
		} 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();
		}
		return almData;
	}
	
	/**
	 * 插入实时告警
	 * @param pool
	 * @param lock_id
	 * @param almId
	 * @param alm_source
	 */
	public static void insertElectLockAlarm(MysqlConnPool pool,int lock_id, int almId,int almSource) {
		String sql_str = "INSERT INTO " + Sql_Mysql.Lock_Alarm_Table 
					   + "(lock_id,alm_id,alm_source,alm_start_time,alm_end_time) "
					   + " VALUES(" + lock_id + "," + almId + "," + almSource + ",Now(),Now());";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			sql.logger.error(e.toString(),e);
		} finally {
			sql.close_con();
		}
	}

	/**
	 * 
	 * @param pool
	 * @param almData
	 */
	public static void changeRealAlmToHisAlm(MysqlConnPool pool, ElectLock_AlarmData almData) {
		//创建当前告警开始年份的历史告警表
		String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Lock_Alarm_His_Table + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y) + " (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键'," + 
				"  `lock_id` int(11) NOT NULL DEFAULT '1' COMMENT '锁具ID'," + 
				"  `alm_id` int(11) NOT NULL DEFAULT '119001' COMMENT '告警类型'," + 
				"  `alm_source` int(11) NOT NULL DEFAULT '1' COMMENT '告警来源[1-平台触发  2-手机APP触发  3-锁具触发]'," + 
				"  `alm_start_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警开始时间'," + 
				"  `alm_is_confirmed` tinyint(4) NOT NULL DEFAULT '0' COMMENT '告警是否确认'," + 
				"  `alm_confirmed_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警确认时间'," + 
				"  `alm_end_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '告警结束时间'," + 
				"  PRIMARY KEY (`num`)" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y) + "年电子锁具历史告警表';";
		//2将当前的实时告警记录插入到历史告警表中  
		String sql_str2 = " INSERT INTO " + Sql_Mysql.Lock_Alarm_His_Table + Com.getDateTimeFormat(almData.getAlm_start_time(), Com.DTF_Y)
						+ "(lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,alm_end_time) "
						+ "(SELECT lock_id,alm_id,alm_source,alm_start_time,alm_is_confirmed,alm_confirmed_time,Now() from " + Sql_Mysql.Lock_Alarm_Table 
							+ " WHERE lock_id = " + almData.getLock_id() + " AND alm_id = " + almData.getAlm_id() + " "
						+ ")";	
		//3将实时告警表中的告警记录清空
		String sql_str3 = " DELETE FROM "  + Sql_Mysql.Lock_Alarm_Table 
						+ " WHERE "
						+ " lock_id =" + almData.getLock_id()
						+ " AND alm_id =" + almData.getAlm_id();
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			ArrayList<String> sql_strs = new ArrayList<String>();
			
			sql_strs.add(sql_str1);
			sql_strs.add(sql_str2);
			sql_strs.add(sql_str3);
			sql.makeManualCommit(sql_strs);
		} catch (Exception e) {
			sql.logger.error(e.toString(), e);
		} finally {
			sql.close_con();
		}
	}

	/**
	 * 将临时表中的告警记录插入实时告警表中
	 * @param pool
	 */
	public static void changeTempAlmToRealAlm(MysqlConnPool pool) {
		//1.查询是否存在临时告警记录
		String sql_str_sel = " SELECT COUNT(*) as alm_count FROM " + Sql_Mysql.Lock_Alm_Temp_Table;
		//2将当前的实时告警记录插入到历史告警表中  
		String sql_str2 = " INSERT INTO " + Sql_Mysql.Lock_Alarm_Table 
						+ " (lock_id,alm_id,alm_source,alm_start_time,alm_end_time) "
						+ " (SELECT lock_id,alm_id,alm_source,alm_start_time,alm_end_time from " + Sql_Mysql.Lock_Alm_Temp_Table + ")";	
		//3将实时告警表中的告警记录清空
		String sql_str3 = " DELETE FROM "  + Sql_Mysql.Lock_Alm_Temp_Table;
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		ResultSet res = null;
		try {
			res = sql.sqlMysqlQuery(sql_str_sel);	
			if(res.next()) {
				int alm_count = res.getInt("alm_count");
				//临时表中存在实时告警时，移动到实时告警表中
				if(alm_count > 0) {
					ArrayList<String> sql_strs = new ArrayList<String>();
					
					sql_strs.add(sql_str2);
					sql_strs.add(sql_str3);
					
					sql.makeManualCommit(sql_strs);
				}
			}
		} 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();
		}
	}

	public static void delLockAlarmData(MysqlConnPool pool, Date deldate) {
		String sql_str_sel = " select TABLE_NAME,CREATE_TIME " + 
				 " FROM information_schema.tables " + 
				 " where table_schema='db_lock_alarm' " + 
				 " AND TABLE_NAME like 'tb_lock_alarm_%' " + 
				 " AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
		
		ResultSet res = null;
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		res = sql.sqlMysqlQuery(sql_str_sel);
		int count = 0;
		StringBuffer sql_delete_strs = new StringBuffer(" DROP TABLE IF EXISTS ");
		try {
			while(res.next()) {
				if(count > 0) {
					sql_delete_strs.append(",");
				}
				
				sql_delete_strs.append("db_lock_alarm." + res.getString("TABLE_NAME"));
				sql.logger.info("自动删除超过5年的告警表 : db_lock_alarm."+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
				count++;
			}
			if(count >0) {
				//System.out.println(sql_delete_strs.toString());
				sql.sqlMysqlExecute(sql_delete_strs.toString());
			}
		} 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();
		}
	}
}