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.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.log4j.Logger;

import com.szpg.db.dao.PgDeviceDao;
import com.szpg.db.data.PgAlarm;
import com.szpg.db.data.PgDevice;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.TimeFormat;

public class PgDeviceDaoImpl implements PgDeviceDao {
	
	private final Logger logger = Logger.getLogger(this.getClass().getName());
	
	@Override
	public String findAcuCodeByCode(String code) {
		Connection conn = null;
		
		String queryStr = "SELECT PARTITION FROM PG_DEVICE WHERE ASSETCODE = ?";
		Object[] param = new Object[1];
		param[0] = code;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<String> tempList = (List<String>) runner.query(conn, queryStr, new ColumnListHandler<String>(), param);
			
			if (null != tempList && tempList.size() == 1) {
				String partition = tempList.get(0);
				
				logger.debug("根据设备代码查询所在ACU分区代码成功[" + partition + "]");
				return partition;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备代码查询所在ACU分区代码异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}


	@Override
	public Integer findDeviceIdByCode(String code) {
		Connection conn = null;
		
		String queryStr = "SELECT ID FROM PG_DEVICE WHERE ASSETCODE = ?";
		Object[] param = new Object[1];
		param[0] = code;
		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.size() == 1) {
				int id = ((Number) tempList.get(0)).intValue();
				
				logger.debug("根据代码查询设备ID成功[ID=" + id + ", CODE=" + code + "]");
				return id;
			}
			else {
				logger.debug("未找到设备[" + code + "]");
				return -1;
			}
		} catch (Exception ex) {
			logger.error("根据代码查询设备ID异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	@Override
	public PgDevice findDeviceById(Integer deviceId) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_DEVICE WHERE ID = ?";
		Object[] params = new Object[1];
		params[0] = deviceId;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			PgDevice device = (PgDevice) runner.query(conn, queryStr, new BeanHandler<PgDevice>(PgDevice.class), params);
			
			if (null != device) {
				logger.debug("根据ID查询设备资产成功");
				
				return device;
			} 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<PgDevice> findDeviceByAcu(String acucode) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_DEVICE WHERE ASSETCODE LIKE ?";
		Object[] params = new Object[1];
		params[0] = acucode + "%";
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgDevice> list = (List<PgDevice>) runner.query(conn, queryStr, new BeanListHandler<PgDevice>(PgDevice.class), params);
			
			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 List<PgDevice> findDeviceByPositionAndType(String acucode, String devtype, String cabin) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_DEVICE WHERE ASSETCODE LIKE ?";
		Object[] params = new Object[1];
		params[0] = acucode + "." + devtype + "." + cabin + "%";
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgDevice> list = (List<PgDevice>) runner.query(conn, queryStr, new BeanListHandler<PgDevice>(PgDevice.class), params);
			
			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 int addGenerlAlarmRecord(PgAlarm alarmRec) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_ALARM " + 
						   "(ALARM_DATE, ALARM_VALUE, ALARMDEVID, ALARMTYPEID) " + 
						   "VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?)";
		Object[] params = new Object[4];
		params[0] = TimeFormat.formatTimestamp(alarmRec.getAlarm_date());
		params[1] = alarmRec.getAlarm_value();
		params[2] = alarmRec.getAlarmdevid();
		params[3] = alarmRec.getAlarmtypeid();
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, insertStr, params);
			
			if (count > 0) 
				logger.debug("插入通用报警记录成功" + alarmRec);
			else 
				logger.error("插入通用报警记录失败！");
			
			return count;
		} catch (Exception ex) {
			logger.error("插入通用报警记录异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
