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.PgAcuDao;
import com.szpg.db.data.PgAcu;
import com.szpg.db.util.ConnectionManager;

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

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

	@Override
	public PgAcu findACUById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU WHERE ACTIVE = '1' AND ID = ?";
		Object[] param = new Object[1];
		param[0] = id;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcu> list = (List<PgAcu>) runner.query(conn, queryStr, new BeanListHandler<PgAcu>(PgAcu.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据ID查询ACU成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ID查询ACU异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	@Override
	public PgAcu findACUByDest(String dest) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU WHERE ACTIVE = '1' AND ACU_DEST = ?";
		Object[] param = new Object[1];
		param[0] = dest;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcu> list = (List<PgAcu>) runner.query(conn, queryStr, new BeanListHandler<PgAcu>(PgAcu.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据目的地址查询ACU成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据目的地址查询ACU异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	@Override
	public PgAcu findACUByHost(String host) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU WHERE ACTIVE = '1' AND ACU_HOST = ?";
		Object[] param = new Object[1];
		param[0] = host;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcu> list = (List<PgAcu>) runner.query(conn, queryStr, new BeanListHandler<PgAcu>(PgAcu.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据主机地址查询ACU成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据主机地址查询ACU异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}

	@Override
	public PgAcu findACUByCode(String acucode) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU WHERE ACTIVE = '1' AND ACU_CODE = ?";
		Object[] param = new Object[1];
		param[0] = acucode;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcu> list = (List<PgAcu>) runner.query(conn, queryStr, new BeanListHandler<PgAcu>(PgAcu.class), param);
			
			if (null != list && list.size() == 1) {
				logger.debug("根据代码查询ACU成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据代码查询ACU异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
}