package com.szpg.db.dao.impl;

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

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

import com.szpg.db.dao.PgCh4Dao;
import com.szpg.db.data.PgCh4;
import com.szpg.db.data.PgCh4Alm;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.NumberFormat;

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

	@Override
	public List<PgCh4> findAllPblz() {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4, LOGTIME, PGDEVICEID FROM PG_CH4";
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("查询所有甲烷监测值成功[" + tempList.size() + "]");
				
				List<PgCh4> list = new ArrayList<PgCh4>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4 ch4 = new PgCh4();
					Object[] item = tempList.get(i);
					
					ch4.setId(((Number) item[0]).intValue());
					ch4.setCh4(Float.parseFloat((String) item[1]));
					ch4.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4.setLogtime(cal.getTime());
					ch4.setUptime(cal.getTime());
					
					list.add(ch4);
				}
				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 PgCh4 findPblzById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4, LOGTIME, PGDEVICEID FROM PG_CH4 WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = id;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler(), param);
			
			if (null != tempList && tempList.size() == 1) {
				PgCh4 ch4 = new PgCh4();
				Object[] item = tempList.get(0);
				
				ch4.setId(((Number) item[0]).intValue());
				ch4.setCh4(Float.parseFloat((String) item[1]));
				ch4.setPgdeviceid(((Number) item[3]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				ch4.setLogtime(cal.getTime());
				ch4.setUptime(cal.getTime());
				
				logger.debug("根据ID查询甲烷监测值成功" + ch4 + "]");
				
				return ch4;
			}
			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<PgCh4> findPblzByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4, LOGTIME, PGDEVICEID FROM PG_CH4 WHERE PGDEVICEID = ?";
		Object[] param = new Object[1];
		param[0] = deviceid;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据设备ID查询甲烷监测值成功[" + tempList.size() + "]");
				
				List<PgCh4> list = new ArrayList<PgCh4>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4 ch4 = new PgCh4();
					Object[] item = tempList.get(i);
					
					ch4.setId(((Number) item[0]).intValue());
					ch4.setCh4(Float.parseFloat((String) item[1]));
					ch4.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4.setLogtime(cal.getTime());
					ch4.setUptime(cal.getTime());
					
					list.add(ch4);
				}
				return list;
			}
			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<PgCh4> findPblzByDeviceAndTm(Integer deviceid, String start, String end) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4, LOGTIME, PGDEVICEID FROM PG_CH4 WHERE PGDEVICEID = ? AND LOGTIME >= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND LOGTIME <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')";
		Object[] params = new Object[3];
		params[0] = deviceid;
		params[1] = start;
		params[2] = end;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据设备ID和时间查询甲烷监测值成功[" + tempList.size() + "]");
				
				List<PgCh4> list = new ArrayList<PgCh4>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4 ch4 = new PgCh4();
					Object[] item = tempList.get(i);
					
					ch4.setId(((Number) item[0]).intValue());
					ch4.setCh4(Float.parseFloat((String) item[1]));
					ch4.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4.setLogtime(cal.getTime());
					ch4.setUptime(cal.getTime());
					
					list.add(ch4);
				}
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备ID和时间查询甲烷监测值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public int addPblzRecord(PgCh4 ch4) {
		return addPblzRecord(ch4.getCh4(), ch4.getTmStr(), ch4.getPgdeviceid());
	}

	@Override
	public int addPblzRecord(float value, String tm, int deviceid) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_CH4 " + 
						   "(CH4, LOGTIME, UPTIME, PGDEVICEID) " + 
						   "VALUES (?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
		Object[] params = new Object[4];
		params[0] = NumberFormat.format(value, "0.00");
		params[1] = tm;
		params[2] = tm;
		params[3] = deviceid;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, insertStr, params);
			
			if (count > 0) 
				logger.debug("插入甲烷监测值成功[tm=" + tm + ", value=" + value + ", deviceid=" + deviceid + "]");
			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 List<PgCh4Alm> findAllSbjz() {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4ALM, LOGTIME, PGDEVICEID FROM PG_CH4_ALM";
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("查询所有甲烷报警状态成功[" + tempList.size() + "]");
				
				List<PgCh4Alm> list = new ArrayList<PgCh4Alm>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4Alm ch4alm = new PgCh4Alm();
					Object[] item = tempList.get(i);
					
					ch4alm.setId(((Number) item[0]).intValue());
					ch4alm.setCh4alm(Boolean.parseBoolean((String) item[1]));
					ch4alm.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4alm.setLogtime(cal.getTime());
					ch4alm.setUptime(cal.getTime());
					
					list.add(ch4alm);
				}
				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 PgCh4Alm findSbjzById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4ALM, LOGTIME, PGDEVICEID FROM PG_CH4_ALM WHERE ID = ?";
		Object[] param = new Object[1];
		param[0] = id;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler(), param);
			
			if (null != tempList && tempList.size() == 1) {
				PgCh4Alm ch4 = new PgCh4Alm();
				Object[] item = tempList.get(0);
				
				ch4.setId(((Number) item[0]).intValue());
				ch4.setCh4alm(Boolean.parseBoolean((String) item[1]));
				ch4.setPgdeviceid(((Number) item[3]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				ch4.setLogtime(cal.getTime());
				ch4.setUptime(cal.getTime());
				
				logger.debug("根据ID查询甲烷报警状态成功" + ch4 + "]");
				
				return ch4;
			}
			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<PgCh4Alm> findSbjzByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4ALM, LOGTIME, PGDEVICEID FROM PG_CH4_ALM WHERE PGDEVICEID = ?";
		Object[] param = new Object[1];
		param[0] = deviceid;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据设备ID查询甲烷报警状态成功[" + tempList.size() + "]");
				
				List<PgCh4Alm> list = new ArrayList<PgCh4Alm>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4Alm ch4 = new PgCh4Alm();
					Object[] item = tempList.get(i);
					
					ch4.setId(((Number) item[0]).intValue());
					ch4.setCh4alm(Boolean.parseBoolean((String) item[1]));
					ch4.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4.setLogtime(cal.getTime());
					ch4.setUptime(cal.getTime());
					
					list.add(ch4);
				}
				return list;
			}
			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<PgCh4Alm> findSbjzByDeviceAndTm(Integer deviceid, String start, String end) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, CH4ALM, LOGTIME, PGDEVICEID FROM PG_CH4_ALM WHERE PGDEVICEID = ? AND LOGTIME >= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND LOGTIME <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')";
		Object[] params = new Object[3];
		params[0] = deviceid;
		params[1] = start;
		params[2] = end;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
			
			if (null != tempList && tempList.isEmpty() == false) {
				logger.debug("根据设备ID和时间查询甲烷报警状态成功[" + tempList.size() + "]");
				
				List<PgCh4Alm> list = new ArrayList<PgCh4Alm>();
				for (int i = 0; i < tempList.size(); i++) {
					PgCh4Alm ch4 = new PgCh4Alm();
					Object[] item = tempList.get(i);
					
					ch4.setId(((Number) item[0]).intValue());
					ch4.setCh4alm(Boolean.parseBoolean((String) item[1]));
					ch4.setPgdeviceid(((Number) item[3]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[2]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ch4.setLogtime(cal.getTime());
					ch4.setUptime(cal.getTime());
					
					list.add(ch4);
				}
				return list;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据设备ID和时间查询甲烷报警状态异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public int addSbjzRecord(PgCh4Alm ch4alm) {
		return addSbjzRecord(ch4alm.getCh4alm(), ch4alm.getTmStr(), ch4alm.getPgdeviceid());
	}

	@Override
	public int addSbjzRecord(boolean status, String tm, int deviceid) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_CH4_ALM " + 
						   "(CH4ALM, LOGTIME, UPTIME, PGDEVICEID) " + 
						   "VALUES (?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
		Object[] params = new Object[4];
		params[0] = status;
		params[1] = tm;
		params[2] = tm;
		params[3] = deviceid;
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, insertStr, params);
			
			if (count > 0) 
				logger.debug("插入甲烷报警状态成功[tm=" + tm + ", status=" + status + ", deviceid=" + deviceid + "]");
			else 
				logger.error("插入甲烷报警状态失败！");
			
			return count;
		} catch (Exception ex) {
			logger.error("插入甲烷报警状态异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
