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 findLatestCmdByDestAndCount(String dest, int count) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU_RDCMD WHERE RESPONSED='0' AND TIMEOUT='0' AND DEST=? AND COUNT_WORD=? ORDER BY TM DESC";
		Object[] params = new Object[2];
		params[0] = dest;
		params[1] = count;
		
		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 PgAcuRdcmd findLatestCmdByDestAndType(String dest, String type) {
		Connection conn = null;
		
		String queryStr = "SELECT * FROM PG_ACU_RDCMD WHERE RESPONSED='0' AND TIMEOUT='0' AND 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, MEM_AREA_CD, START_MEM_WORD, START_MEM_BIT, COUNT_WORD, COUNT_BIT, COUNT_SENSOR, OFFSET, RESPONSED, TIMEOUT) " + 
						   "VALUES (?, TO_DATE(?, 'YYYYMMDDHH24MISS'), ?, ?, ?, ?, ?, ?, ?, ?, ?, '0', '0')";
		Object[] params = new Object[11];
		params[0] = cmd.getId();
		params[1] = TimeFormat.format(cmd.getTm(), "yyyyMMddHHmmss");
		params[2] = cmd.getCmd_type();
		params[3] = cmd.getDest();
		params[4] = cmd.getMem_area_cd();
		params[5] = cmd.getStart_mem_word();
		params[6] = cmd.getStart_mem_bit();
		params[7] = cmd.getCount_word();
		params[8] = cmd.getCount_bit();
		params[9] = cmd.getCount_sensor();
		params[10] = null == cmd.getOffset() ? 0 : cmd.getOffset();
		
		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 updateCmdRecordResponsed(String cmdId) {
		Connection conn = null;
		
		String updateStr = "UPDATE PG_ACU_RDCMD SET RESPONSED = '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("更新读取命令已响应字段失败！");
			
			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;
	}

}