package com.szpg.db.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
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 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();
			}
		}
	}

}