package com.szpg.db.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;

import com.szpg.db.dao.PgAlarmDao;
import com.szpg.db.data.PgAlarm;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.TimeFormat;

/**
 * @author TAN YUE
 */
public class PgAlarmDaoImpl implements PgAlarmDao {
	
	private final Logger logger = Logger.getLogger(this.getClass().getName());
	

	@Override
	public int findAlarmCountByDate(String date) {
		Connection conn = null;
		String sql = "SELECT COUNT(ID) FROM PG_ALARM WHERE TO_CHAR(ALARM_DATE, 'YYYY-MM-DD') = ?";
		Object[] param = new Object[1];
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			param[0] = date;
			
			int count = (runner.query(conn, sql, new ScalarHandler<Number>(1), param)).intValue();
			return count;
		} catch (Exception ex) {
			logger.error("根据日期查询报警记录数异常", ex);
			return 0;
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public PgAlarm findLatestAlarmByDevice(int deviceId) {
		Connection conn = null;
		String queryStr = "SELECT A.* FROM " +
				"(SELECT ID, ACTIVE, ALARM_DATE, ALARM_VALUE, DESCRIPTION, ALARMDEVID, ALARMTYPEID, ALARM_GRADE, LOG_TIME, LOG_DESC FROM PG_ALARM WHERE ALARMDEVID = ? ORDER BY ALARM_DATE DESC) A " +
				"WHERE ROWNUM <= 1";
		Object[] param = new Object[1];
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			param[0] = deviceId;

			List<Object[]> tempList = runner.query(conn, queryStr, new ArrayListHandler(), param);
			if (null != tempList && tempList.isEmpty() == false) {
				PgAlarm alarm = new PgAlarm();
				Object[] item = tempList.get(0);

				alarm.setId(((Number) item[0]).intValue());
				alarm.setActive(((Number) item[1]).intValue());

				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				alarm.setAlarm_date(cal.getTime());

				alarm.setAlarm_value((String) item[3]);
				alarm.setDescription((String) item[4]);
				alarm.setAlarmdevid(((Number) item[5]).intValue());
				alarm.setAlarmtypeid(((Number) item[6]).intValue());
				alarm.setAlarm_grade((String) item[7]);

				if (null != item[8]) {
					long logTmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[8]).toJdbc()).getTime();
					Calendar logCal = Calendar.getInstance();
					logCal.setTimeInMillis(logTmValue);
					alarm.setLog_time(logCal.getTime());
				} else {
					alarm.setLog_time(null);
				}

				alarm.setLog_desc((String) item[9]);

				logger.debug("根据设备查询最新报警记录成功");

				return alarm;
			} else {
				return null;
			}
		} catch (Exception ex) {
			logger.error("根据设备查询最新报警记录成功", ex);
			return null;
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public PgAlarm findLatestAlarmByDeviceAndType(int deviceId, String type) {
		Connection conn = null;
		String queryStr = "SELECT A.* FROM " +
				"(SELECT ID, ACTIVE, ALARM_DATE, ALARM_VALUE, DESCRIPTION, ALARMDEVID, ALARMTYPEID, ALARM_GRADE, LOG_TIME, LOG_DESC FROM PG_ALARM WHERE ALARMDEVID = ? AND DESCRIPTION LIKE ? ORDER BY ALARM_DATE DESC) A " +
				"WHERE ROWNUM <= 1";
		Object[] param = new Object[2];
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			param[0] = deviceId;
			param[1] = "%" + type + "%";

			List<Object[]> tempList = runner.query(conn, queryStr, new ArrayListHandler(), param);
			if (null != tempList && tempList.isEmpty() == false) {
				PgAlarm alarm = new PgAlarm();
				Object[] item = tempList.get(0);

				alarm.setId(((Number) item[0]).intValue());
				alarm.setActive(((Number) item[1]).intValue());

				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				alarm.setAlarm_date(cal.getTime());

				alarm.setAlarm_value((String) item[3]);
				alarm.setDescription((String) item[4]);
				alarm.setAlarmdevid(((Number) item[5]).intValue());
				alarm.setAlarmtypeid(((Number) item[6]).intValue());
				alarm.setAlarm_grade((String) item[7]);

				long logTmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[8]).toJdbc()).getTime();
				Calendar logCal = Calendar.getInstance();
				logCal.setTimeInMillis(logTmValue);
				alarm.setLog_time(logCal.getTime());
				alarm.setLog_desc((String) item[9]);

				logger.debug("根据设备查询最新报警记录成功");

				return alarm;
			} else {
				return null;
			}
		} catch (Exception ex) {
			logger.error("根据设备查询最新报警记录失败", ex);
			return null;
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public long insertAlarmRecord(PgAlarm alarmRecord) {
		Connection conn = null;
		
		String querySql = "SELECT ID FROM PG_ALARM WHERE ALARMDEVID = ? AND ACTIVE=1";
		Object[] queryParam = new Object[1];
		queryParam[0] = alarmRecord.getAlarmdevid();
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			
			// 查询同一设备是否有正在报警的消息
			Number id = runner.query(conn, querySql, new ScalarHandler<Number>(1), queryParam);
			
			if (null != id) {
				// 找到设备已存在的记录
				// 1先将正在报警的设备自动消警
				String updateSql = "UPDATE PG_ALARM SET ACTIVE=0, LOG_TIME=SYSDATE, LOG_DESC=? WHERE ID=?";
				
				Object[] updateParam = new Object[2];
				updateParam[0] = "重复报警自动消警";
				updateParam[1] = id.longValue();

				// 更新报警记录
				runner.update(conn, updateSql, updateParam);
			}
			
			// 插入新的报警记录
			String insertSql = "INSERT INTO PG_ALARM " + 
				 	   		   "(ID, ACTIVE, ALARM_DATE, ALARM_VALUE, DESCRIPTION, ALARMDEVID, ALARMTYPEID, USERID, ALARM_GRADE, LOG_TIME, LOG_DESC) " +
				 	   		   "VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?, ?, ?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
			
			String sqlId = "SELECT SEQ_PG_ALARM_ID.NEXTVAL FROM DUAL";
			// 获取新的id
			long newId = (runner.query(conn, sqlId, new ScalarHandler<Number>(1))).longValue();

			Object[] params = new Object[11];
			
			params[0] = newId;
			params[1] = alarmRecord.getActive();
			params[2] = TimeFormat.formatTimestamp(alarmRecord.getAlarm_date());
			params[3] = alarmRecord.getAlarm_value();
			params[4] = alarmRecord.getDescription();
			params[5] = alarmRecord.getAlarmdevid();
			params[6] = alarmRecord.getAlarmtypeid();
			params[7] = alarmRecord.getUserid();
			params[8] = alarmRecord.getAlarm_grade();
			params[9] = TimeFormat.formatTimestamp(alarmRecord.getLog_time());
			params[10] = alarmRecord.getLog_desc();

			// 新增报警记录
			int count = runner.update(conn, insertSql, params);
			
			if (count > 0) {
				logger.debug("插入报警数据成功");
			} else {
				logger.error("插入报警数据失败！");
			}
			
			return newId;
		} catch (Exception ex) {
			logger.error("插入报警数据异常", ex);
			return 0;
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}