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.PgAcuRdcmdDao;
import com.szpg.db.data.PgAcuRdcmd;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.TimeFormat;

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

	@Override
	public List<PgAcuRdcmd> findAll() {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU_RDCMD";
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcuRdcmd> list = (List<PgAcuRdcmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuRdcmd>(PgAcuRdcmd.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 PgAcuRdcmd findById(String id) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU_RDCMD WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = id;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcuRdcmd> list = (List<PgAcuRdcmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuRdcmd>(PgAcuRdcmd.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 PgAcuRdcmd findLatestCmdByDestAndType(String dest, String type) {
		Connection conn = null;
		
		String queryStr = "SELECT C.* FROM PG_ACU_RDCMD C, PG_ACU A WHERE TIMEOUT='0' AND A.ACU_CODE=C.DEST_ACU_CODE AND A.ACU_DEST=? AND CMD_TYPE=? ORDER BY TM DESC";
		Object[] params = new Object[2];
		params[0] = dest;
		params[1] = type;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<PgAcuRdcmd> list = (List<PgAcuRdcmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuRdcmd>(PgAcuRdcmd.class), params);
			
			if (null != list && list.isEmpty() == false) {
				logger.debug("根据目的地址和命令类型查询内存读取命令成功[" + list.size() + "]");
				
				return list.get(0);
			} else 
				return null;
		} catch (Exception ex) {
			logger.error("根据目的地址和命令类型查询内存读取命令异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public int addCmdRecord(PgAcuRdcmd cmd) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_ACU_RDCMD " + 
						   "(ID, TM, CMD_TYPE, DEST_ACU_CODE, TIMEOUT) " + 
						   "VALUES (?, TO_DATE(?, 'YYYYMMDDHH24MISS'), ?, ?, '0')";
		Object[] params = new Object[4];
		params[0] = cmd.getId();
		params[1] = TimeFormat.format(cmd.getTm(), "yyyyMMddHHmmss");
		params[2] = cmd.getCmd_type();
		params[3] = cmd.getDest_acu_code();
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, insertStr, params);
			
			if (count > 0) 
				logger.debug("插入读取命令数据成功" + cmd);
			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 updateCmdRecordTimeout(String cmdId) {
		Connection conn = null;
		
		String updateStr = "UPDATE PG_ACU_RDCMD SET TIMEOUT = '1' WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = cmdId;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, updateStr, param);
			
			if (count > 0) {
				logger.debug("更新读取命令已超时字段成功" + cmdId);
			} else {
				logger.error("更新读取命令已超时字段成功" + cmdId);
			}
			
			return count;
		} catch (Exception ex) {
			logger.error("更新读取命令已超时字段异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteCmdRecord(String cmdId) {
		Connection conn = null;
		
		String updateStr = "DELETE FROM PG_ACU_RDCMD WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = cmdId;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, updateStr, param);
			
			if (count > 0) 
				logger.debug("删除读取命令成功" + cmdId);
			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 deleteTimeoutCmd() {
		Connection conn = null;
		
		String updateStr = "DELETE FROM PG_ACU_RDCMD WHERE TIMEOUT = '1'";
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, updateStr);
			
			if (count > 0) {
				logger.debug("删除超时的命令成功");
			}
			
			return count;
		} catch (Exception ex) {
			logger.error("删除超时的命令异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}