package com.szpg.db.dao.impl;

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

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

import com.szpg.db.dao.PgDeviceDao;
import com.szpg.db.util.ConnectionManager;

public class PgDeviceDaoImpl implements PgDeviceDao {
	
	private final Logger logger = Logger.getLogger(this.getClass().getName());
	
	@Override
	public List<Integer> findDeviceIdByDestAndType(String dest, String type) {
		Connection conn = null;
		
		String key = "";
		
		switch (type.toUpperCase()) {
			case "CH": 
				key = "%甲烷%";
				break;
			case "WS":
				key = "%温湿度%";
				break;
			case "CO":
				key = "%一氧化碳%";
				break;
			case "O2":
				key = "%氧气%";
				break;
			case "HS":
				key = "%硫化氢%";
				break;
		}
		
		String queryStr = "SELECT D.ID FROM PG_DEVICE D, PG_ACU A WHERE D.PARTITION = A.ACU_CODE AND A.ACU_DEST = ? AND D.DEVCODE LIKE '" + key + "' ORDER BY D.ID";
		Object[] param = new Object[1];
		param[0] = dest;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据ACU的目的地址和类型查询设备ID成功[" + tempList.size() + "]");
				
				List<Integer> list = new ArrayList<Integer>();
				for (int i = 0; i < tempList.size(); i++) {
					list.add(((Number) tempList.get(i)).intValue());
				}
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ACU的目的地址和类型查询设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public List<Integer> findCH4DeviceIdByDest(String dest) {
		Connection conn = null;
		
		String queryStr = "SELECT D.ID FROM PG_DEVICE D, PG_ACU A WHERE D.PARTITION = A.ACU_CODE AND A.ACU_DEST = ? AND D.DEVCODE LIKE '%甲烷%' ORDER BY D.ID";
		Object[] param = new Object[1];
		param[0] = dest;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据ACU的目的地址查询甲烷设备ID成功[" + tempList.size() + "]");
				
				List<Integer> list = new ArrayList<Integer>();
				for (int i = 0; i < tempList.size(); i++) {
					list.add(((Number) tempList.get(i)).intValue());
				}
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ACU的目的地址查询甲烷设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public List<Integer> findWSDeviceIdByDest(String dest) {
		Connection conn = null;
		
		String queryStr = "SELECT D.ID FROM PG_DEVICE D, PG_ACU A WHERE D.PARTITION = A.ACU_CODE AND A.ACU_DEST = ? AND D.DEVCODE LIKE '%温湿度%' ORDER BY D.ID";
		Object[] param = new Object[1];
		param[0] = dest;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据ACU的目的地址查询温湿度设备ID成功[" + tempList.size() + "]");
				
				List<Integer> list = new ArrayList<Integer>();
				for (int i = 0; i < tempList.size(); i++) {
					list.add(((Number) tempList.get(i)).intValue());
				}
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ACU的目的地址查询温湿度设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public List<Integer> findCODeviceIdByDest(String dest) {
		Connection conn = null;
		
		String queryStr = "SELECT D.ID FROM PG_DEVICE D, PG_ACU A WHERE D.PARTITION = A.ACU_CODE AND A.ACU_DEST = ? AND D.DEVCODE LIKE '%一氧化碳%' ORDER BY D.ID";
		Object[] param = new Object[1];
		param[0] = dest;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据ACU的目的地址查询一氧化碳设备ID成功[" + tempList.size() + "]");
				
				List<Integer> list = new ArrayList<Integer>();
				for (int i = 0; i < tempList.size(); i++) {
					list.add(((Number) tempList.get(i)).intValue());
				}
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ACU的目的地址查询一氧化碳设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public List<Integer> findO2DeviceIdByDest(String dest) {
		Connection conn = null;
		
		String queryStr = "SELECT D.ID FROM PG_DEVICE D, PG_ACU A WHERE D.PARTITION = A.ACU_CODE AND A.ACU_DEST = ? AND D.DEVCODE LIKE '%氧气%' ORDER BY D.ID";
		Object[] param = new Object[1];
		param[0] = dest;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据ACU的目的地址查询氧气设备ID成功[" + tempList.size() + "]");
				
				List<Integer> list = new ArrayList<Integer>();
				for (int i = 0; i < tempList.size(); i++) {
					list.add(((Number) tempList.get(i)).intValue());
				}
				
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ACU的目的地址查询氧气设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

}