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.BeanListHandler;
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;

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 = ((Number) 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 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 = (List<Object[]>) 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());

				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 = (Number) runner.query(conn, querySql, new ScalarHandler<Number>(1), queryParam);
			
			if (null != id) {
				// 找到设备已存在的记录
				// 1先将正在报警的设备自动消警
				String updateSql = "UPDATE PG_ALARM SET ACTIVE=0 WHERE ID=?";
				
				Object[] updateParam = new Object[1];
				updateParam[0] = id.longValue();
				
				runner.update(conn, updateSql, updateParam); // 更新报警记录
			} 
			
			// 插入新的报警记录
			String insertSql = "INSERT INTO PG_ALARM " + 
				 	   		   "(ID, ACTIVE, ALARM_DATE, ALARM_VALUE, DESCRIPTION, ALARMDEVID, ALARMTYPEID, USERID) " + 
				 	   		   "VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?, ?, ?)";
			
			String sqlId = "SELECT SEQ_PG_ALARM_ID.NEXTVAL FROM DUAL";
			long newId = ((Number) runner.query(conn, sqlId, new ScalarHandler<Number>(1))).longValue(); // 获取新的id
				
			Object[] params = new Object[8];
			
			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();
			
			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();
			}
		}
	}

}
