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

import com.szpg.db.dao.PgWsDao;
import com.szpg.db.data.PgTemphum;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.NumberFormat;

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

	@Override
	public List<PgTemphum> findAllWsdz() {
		Connection conn = null;
		
		String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM";
		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<PgTemphum> list = new ArrayList<PgTemphum>();
				for (int i = 0; i < tempList.size(); i++) {
					PgTemphum ws = new PgTemphum();
					Object[] item = tempList.get(i);
					
					ws.setId(((Number) item[0]).intValue());
					ws.setTemp(Float.parseFloat((String) item[1]));
					ws.setHum(Float.parseFloat((String) item[2]));
					ws.setPgdeviceid(((Number) item[4]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ws.setLogtime(cal.getTime());
					ws.setUptime(cal.getTime());
					
					list.add(ws);
				}
				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 PgTemphum findWsdzById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM 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) {
				PgTemphum ws = new PgTemphum();
				Object[] item = tempList.get(0);
				
				ws.setId(((Number) item[0]).intValue());
				ws.setTemp(Float.parseFloat((String) item[1]));
				ws.setHum(Float.parseFloat((String) item[2]));
				ws.setPgdeviceid(((Number) item[4]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				ws.setLogtime(cal.getTime());
				ws.setUptime(cal.getTime());
				
				logger.debug("根据ID查询温湿度监测值成功" + ws + "]");
				
				return ws;
			}
			else 
				return null;
		} catch (Exception ex) {
			logger.error("根据ID查询温湿度监测值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	@Override
	public PgTemphum findLatestWsdzByDevice(Integer deviceid) {
		Connection conn = null;

		String queryStr = "SELECT A.* FROM " +
				"(SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM WHERE PGDEVICEID = ? ORDER BY LOGTIME DESC) A " +
				"WHERE ROWNUM <= 1";
		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(), param);
			
			if (null != tempList && tempList.isEmpty() == false) {
				PgTemphum ws = new PgTemphum();
				Object[] item = tempList.get(0);
				
				ws.setId(((Number) item[0]).intValue());
				ws.setTemp(Float.parseFloat((String) item[1]));
				ws.setHum(Float.parseFloat((String) item[2]));
				ws.setPgdeviceid(((Number) item[4]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				ws.setLogtime(cal.getTime());
				ws.setUptime(cal.getTime());
				
				logger.debug("根据设备ID查询最新的温湿度监测值成功" + ws + "]");
				
				return ws;
			}
			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<PgTemphum> findWsdzByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM 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<PgTemphum> list = new ArrayList<PgTemphum>();
				for (int i = 0; i < tempList.size(); i++) {
					PgTemphum ws = new PgTemphum();
					Object[] item = tempList.get(i);
					
					ws.setId(((Number) item[0]).intValue());
					ws.setTemp(Float.parseFloat((String) item[1]));
					ws.setHum(Float.parseFloat((String) item[2]));
					ws.setPgdeviceid(((Number) item[4]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ws.setLogtime(cal.getTime());
					ws.setUptime(cal.getTime());
					
					list.add(ws);
				}
				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<PgTemphum> findWsdzByDeviceAndTm(Integer deviceid, String start, String end) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM 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<PgTemphum> list = new ArrayList<PgTemphum>();
				for (int i = 0; i < tempList.size(); i++) {
					PgTemphum ws = new PgTemphum();
					Object[] item = tempList.get(i);
					
					ws.setId(((Number) item[0]).intValue());
					ws.setTemp(Float.parseFloat((String) item[1]));
					ws.setHum(Float.parseFloat((String) item[2]));
					ws.setPgdeviceid(((Number) item[4]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					ws.setLogtime(cal.getTime());
					ws.setUptime(cal.getTime());
					
					list.add(ws);
				}
				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 double findLastTempByDevice(Integer deviceid) {
		Connection conn = null;
		
		String sql = "SELECT TEMP FROM (SELECT * FROM PG_TEMPHUM WHERE PGDEVICEID = ? ORDER BY ID DESC) WHERE ROWNUM = 1";
		Object[] param = new Object[1];
		param[0] = deviceid;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			double tempValue = NumberFormat.parseDouble((String) runner.query(conn, sql, new ScalarHandler<String>(1), param), "0.00");
			return tempValue;
		} catch (Exception ex) {
			logger.error("根据设备查询最近的温度值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0.0;
	}
	
	@Override
	public double findLastHumByDevice(Integer deviceid) {
		Connection conn = null;
		
		String sql = "SELECT HUM FROM (SELECT * FROM PG_TEMPHUM WHERE PGDEVICEID = ? ORDER BY ID DESC) WHERE ROWNUM = 1";
		Object[] param = new Object[1];
		param[0] = deviceid;
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			double humValue = NumberFormat.parseDouble((String) runner.query(conn, sql, new ScalarHandler<String>(1), param), "0.00");
			return humValue;
		} catch (Exception ex) {
			logger.error("根据设备查询最近的湿度值异常", ex);
		} finally {
			try {
				DbUtils.commitAndClose(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0.0;
	}

	@Override
	public int addWsdzRecord(PgTemphum ws) {
		return addWsdzRecord(ws.getTemp(), ws.getHum(), ws.getTmStr(), ws.getPgdeviceid());
	}

	@Override
	public int addWsdzRecord(float temp, float hum, String tm, int deviceid) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_TEMPHUM " + 
						   "(TEMP, HUM, LOGTIME, UPTIME, PGDEVICEID) " + 
						   "VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
		Object[] params = new Object[5];
		params[0] = NumberFormat.format(temp, "0.00");
		params[1] = NumberFormat.format(hum, "0.00");
		params[2] = tm;
		params[3] = tm;
		params[4] = 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 + ", temp=" + temp + ", hum=" + hum + ", 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<PgTemphumAlm> findAllWsdbj() {
//		Connection conn = null;
//		
//		String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_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<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
//				for (int i = 0; i < tempList.size(); i++) {
//					PgTemphumAlm wsalm = new PgTemphumAlm();
//					Object[] item = tempList.get(i);
//					
//					wsalm.setId(((Number) item[0]).intValue());
//					wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
//					wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
//					wsalm.setPgdeviceid(((Number) item[4]).intValue());
//					
//					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
//					Calendar cal = Calendar.getInstance();
//					cal.setTimeInMillis(tmValue);
//					wsalm.setLogtime(cal.getTime());
//					wsalm.setUptime(cal.getTime());
//					
//					list.add(wsalm);
//				}
//				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 PgTemphumAlm findWsdbjById(Integer id) {
//		Connection conn = null;
//		
//		String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_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) {
//				PgTemphumAlm wsalm = new PgTemphumAlm();
//				Object[] item = tempList.get(0);
//				
//				wsalm.setId(((Number) item[0]).intValue());
//				wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
//				wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
//				wsalm.setPgdeviceid(((Number) item[4]).intValue());
//				
//				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
//				Calendar cal = Calendar.getInstance();
//				cal.setTimeInMillis(tmValue);
//				wsalm.setLogtime(cal.getTime());
//				wsalm.setUptime(cal.getTime());
//				
//				logger.debug("根据ID查询温湿度报警状态成功" + wsalm + "]");
//				
//				return wsalm;
//			}
//			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<PgTemphumAlm> findWsdbjByDevice(Integer deviceid) {
//		Connection conn = null;
//		
//		String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_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<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
//				for (int i = 0; i < tempList.size(); i++) {
//					PgTemphumAlm wsalm = new PgTemphumAlm();
//					Object[] item = tempList.get(i);
//					
//					wsalm.setId(((Number) item[0]).intValue());
//					wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
//					wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
//					wsalm.setPgdeviceid(((Number) item[4]).intValue());
//					
//					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
//					Calendar cal = Calendar.getInstance();
//					cal.setTimeInMillis(tmValue);
//					wsalm.setLogtime(cal.getTime());
//					wsalm.setUptime(cal.getTime());
//					
//					list.add(wsalm);
//				}
//				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<PgTemphumAlm> findWsdbjByDeviceAndTm(Integer deviceid, String start, String end) {
//		Connection conn = null;
//		
//		String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_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<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
//				for (int i = 0; i < tempList.size(); i++) {
//					PgTemphumAlm wsalm = new PgTemphumAlm();
//					Object[] item = tempList.get(i);
//					
//					wsalm.setId(((Number) item[0]).intValue());
//					wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
//					wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
//					wsalm.setPgdeviceid(((Number) item[4]).intValue());
//					
//					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
//					Calendar cal = Calendar.getInstance();
//					cal.setTimeInMillis(tmValue);
//					wsalm.setLogtime(cal.getTime());
//					wsalm.setUptime(cal.getTime());
//					
//					list.add(wsalm);
//				}
//				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 addWsdbjRecord(PgTemphumAlm wsalm) {
//		return addWsdbjRecord(wsalm.getTempalm(), wsalm.getHumalm(), wsalm.getTmStr(), wsalm.getPgdeviceid());
//	}
//
//	@Override
//	public int addWsdbjRecord(boolean temp, boolean hum, String tm, int deviceid) {
//		Connection conn = null;
//		
//		String insertStr = "INSERT INTO PG_TEMPHUM_ALM " + 
//						   "(TEMPALM, HUMALM, LOGTIME, UPTIME, PGDEVICEID) " + 
//						   "VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
//		Object[] params = new Object[5];
//		params[0] = temp;
//		params[1] = hum;
//		params[2] = tm;
//		params[3] = tm;
//		params[4] = 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 + ", temp=" + temp + ", hum=" + hum + ", 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;
//	}

}