package com.szpg.db.dao.impl;

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

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

import com.szpg.db.dao.PgAlarmRuleDao;
import com.szpg.db.data.PgAlarmRule;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.NumberFormat;

public class PgAlarmRuleDaoImpl implements PgAlarmRuleDao {
	
	private final Logger logger = Logger.getLogger(this.getClass().getName());

	@Override
	public List<PgAlarmRule> findAllRule() {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ALARM_RULE ORDER BY ID";
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class));
			
			if (null != list && list.isEmpty() == false) {
				logger.debug("查询所有报警规则成功[" + list.size() + "]");
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("查询所有报警规则异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public PgAlarmRule findRuleById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = id;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据ID查询报警规则成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ID查询报警规则异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	@Override
	public List<PgAlarmRule> findRuleListByDevtype(String devtype) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE DEVTYPE = ?";
		Object[] param = new Object[1];
		param[0] = devtype;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据设备类型查询报警规则成功[" + list.size() + "]");
				
				return list;
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备类型查询报警规则异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	@Override
	public PgAlarmRule findRuleByDevice(Integer deviceId) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE PGDEVICEID = ?";
		Object[] param = new Object[1];
		param[0] = deviceId;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据设备ID查询报警规则成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备ID查询报警规则异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	@Override
	public PgAlarmRule findRuleByDevtype(String devtype) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE DEVTYPE = ?";
		Object[] param = new Object[1];
		param[0] = devtype;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据设备ID查询报警规则成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备ID查询报警规则异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}

	@Override
	public int updateWSHighThresholdByDevice(Integer deviceId, Double wd, Double sd) {
		Connection conn = null;
		
		String sql = "UPDATE PG_ALARM_RULE SET HIGHVALUE = ? WHERE PGDEVICEID = ?";
		Object[] params = new Object[2];
		params[0] = NumberFormat.format(wd, "0.00") + ";" + NumberFormat.format(sd, "0.00");
		params[1] = deviceId;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			
			int count = runner.update(conn, sql, params);
			
			if (count > 0)
				logger.debug("更新温湿度高限报警阈值成功");
			else {
				logger.error("更新温湿度高限报警阈值失败！");
			}
			return count;
		} catch (Exception ex) {
			logger.error("更新温湿度高限报警阈值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return 0;
	}
	
	@Override
	public int updateHighThresholdByDevice(Integer deviceId, Double high) {
		Connection conn = null;
		
		String sql = "UPDATE PG_ALARM_RULE SET HIGHVALUE = ? WHERE PGDEVICEID = ?";
		Object[] params = new Object[2];
		params[0] = NumberFormat.format(high, "0.00");
		params[1] = deviceId;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			
			int count = runner.update(conn, sql, params);
			
			if (count > 0)
				logger.debug("更新高限报警阈值成功");
			else {
				logger.error("更新高限报警阈值失败！");
			}
			return count;
		} catch (Exception ex) {
			logger.error("更新高限报警阈值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return 0;
	}
	
	@Override
	public int updateLowThresholdByDevice(Integer deviceId, Double low) {
		Connection conn = null;
		
		String sql = "UPDATE PG_ALARM_RULE SET LOWVALUE = ? WHERE PGDEVICEID = ?";
		Object[] params = new Object[2];
		params[0] = NumberFormat.format(low, "0.00");
		params[1] = deviceId;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();
			
			int count = runner.update(conn, sql, params);
			
			if (count > 0)
				logger.debug("更新低限报警阈值成功");
			else {
				logger.error("更新低限报警阈值失败！");
			}
			return count;
		} catch (Exception ex) {
			logger.error("更新低限报警阈值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return 0;
	}

}
