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.PgFjDao;
import com.szpg.db.data.PgFjRt;
import com.szpg.db.data.PgFjStat;
import com.szpg.db.util.ConnectionManager;

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

	@Override
	public PgFjRt findLatestRtByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, RUNSECOND, RUNHOUR FROM PG_FJ_RT WHERE PGDEVICEID = ? ORDER BY LOGTIME DESC";
		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) {
				PgFjRt rt = new PgFjRt();
				Object[] item = tempList.get(0);
				
				rt.setId(((Number) item[0]).intValue());
				rt.setPgdeviceid(((Number) item[2]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				rt.setLogtime(cal.getTime());
				rt.setUptime(cal.getTime());
				
				rt.setRunsecond(Integer.parseInt((String) item[3]));
				rt.setRunhour(Integer.parseInt((String) item[4]));
				
				logger.debug("根据ID查询风机最新运行时长成功" + rt + "]");
				
				return rt;
			}
			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 addRtRecord(PgFjRt fjrt) {
		return addRtRecord(fjrt.getRunsecond(), fjrt.getRunhour(), fjrt.getTmStr(), fjrt.getPgdeviceid());
	}

	@Override
	public int addRtRecord(int second, int hour, String tm, int deviceid) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_FJ_RT " + 
						   "(RUNSECOND, RUNHOUR, 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] = second;
		params[1] = hour;
		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 + ", second=" + second + ", hour=" + hour + ", 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<PgFjStat> findAllStat() {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, AM, RUN, FLT, EN, NO, ROUT FROM PG_FJ_STAT";
		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<PgFjStat> list = new ArrayList<PgFjStat>();
				for (int i = 0; i < tempList.size(); i++) {
					PgFjStat stat = new PgFjStat();
					Object[] item = tempList.get(i);
					
					stat.setId(((Number) item[0]).intValue());
					stat.setPgdeviceid(((Number) item[2]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					stat.setLogtime(cal.getTime());
					stat.setUptime(cal.getTime());
					
					stat.setAm(Boolean.parseBoolean((String) item[3]));
					stat.setRun(Boolean.parseBoolean((String) item[4]));
					stat.setFlt(Boolean.parseBoolean((String) item[5]));
					stat.setEn(Boolean.parseBoolean((String) item[6]));
					stat.setNo(Boolean.parseBoolean((String) item[7]));
					stat.setRout(Boolean.parseBoolean((String) item[8]));
					
					list.add(stat);
				}
				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 PgFjStat findStatById(Integer id) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, AM, RUN, FLT, EN, NO, ROUT FROM PG_FJ_STAT 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) {
				PgFjStat stat = new PgFjStat();
				Object[] item = tempList.get(0);
				
				stat.setId(((Number) item[0]).intValue());
				stat.setPgdeviceid(((Number) item[2]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				stat.setLogtime(cal.getTime());
				stat.setUptime(cal.getTime());
				
				stat.setAm(Boolean.parseBoolean((String) item[3]));
				stat.setRun(Boolean.parseBoolean((String) item[4]));
				stat.setFlt(Boolean.parseBoolean((String) item[5]));
				stat.setEn(Boolean.parseBoolean((String) item[6]));
				stat.setNo(Boolean.parseBoolean((String) item[7]));
				stat.setRout(Boolean.parseBoolean((String) item[8]));
				
				logger.debug("根据ID查询风机运行状态成功" + stat + "]");
				
				return stat;
			}
			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<PgFjStat> findStatByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, AM, RUN, FLT, EN, NO, ROUT FROM PG_FJ_STAT 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<PgFjStat> list = new ArrayList<PgFjStat>();
				for (int i = 0; i < tempList.size(); i++) {
					PgFjStat stat = new PgFjStat();
					Object[] item = tempList.get(i);
					
					stat.setId(((Number) item[0]).intValue());
					stat.setPgdeviceid(((Number) item[2]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					stat.setLogtime(cal.getTime());
					stat.setUptime(cal.getTime());
					
					stat.setAm(Boolean.parseBoolean((String) item[3]));
					stat.setRun(Boolean.parseBoolean((String) item[4]));
					stat.setFlt(Boolean.parseBoolean((String) item[5]));
					stat.setEn(Boolean.parseBoolean((String) item[6]));
					stat.setNo(Boolean.parseBoolean((String) item[7]));
					stat.setRout(Boolean.parseBoolean((String) item[8]));
					
					list.add(stat);
				}
				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<PgFjStat> findStatByDeviceAndTm(Integer deviceid, String start, String end) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, AM, RUN, FLT, EN, NO, ROUT FROM PG_FJ_STAT 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<PgFjStat> list = new ArrayList<PgFjStat>();
				for (int i = 0; i < tempList.size(); i++) {
					PgFjStat stat = new PgFjStat();
					Object[] item = tempList.get(i);
					
					stat.setId(((Number) item[0]).intValue());
					stat.setPgdeviceid(((Number) item[2]).intValue());
					
					long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(tmValue);
					stat.setLogtime(cal.getTime());
					stat.setUptime(cal.getTime());
					
					stat.setAm(Boolean.parseBoolean((String) item[3]));
					stat.setRun(Boolean.parseBoolean((String) item[4]));
					stat.setFlt(Boolean.parseBoolean((String) item[5]));
					stat.setEn(Boolean.parseBoolean((String) item[6]));
					stat.setNo(Boolean.parseBoolean((String) item[7]));
					stat.setRout(Boolean.parseBoolean((String) item[8]));
					
					list.add(stat);
				}
				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 PgFjStat findLatestStatByDevice(Integer deviceid) {
		Connection conn = null;
		
		String queryStr = "SELECT ID, LOGTIME, PGDEVICEID, AM, RUN, FLT, EN, NO, ROUT FROM PG_FJ_STAT WHERE PGDEVICEID = ? ORDER BY LOGTIME DESC";
		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) {
				PgFjStat stat = new PgFjStat();
				Object[] item = tempList.get(0);
				
				stat.setId(((Number) item[0]).intValue());
				stat.setPgdeviceid(((Number) item[2]).intValue());
				
				long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[1]).toJdbc()).getTime();
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(tmValue);
				stat.setLogtime(cal.getTime());
				stat.setUptime(cal.getTime());
				
				stat.setAm(Boolean.parseBoolean((String) item[3]));
				stat.setRun(Boolean.parseBoolean((String) item[4]));
				stat.setFlt(Boolean.parseBoolean((String) item[5]));
				stat.setEn(Boolean.parseBoolean((String) item[6]));
				stat.setNo(Boolean.parseBoolean((String) item[7]));
				stat.setRout(Boolean.parseBoolean((String) item[8]));
				
				logger.debug("根据ID查询风机最新运行状态成功" + stat + "]");
				
				return stat;
			}
			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 addStatRecord(PgFjStat fjstat) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_FJ_STAT " + 
						   "(AM, RUN, FLT, EN, NO, ROUT, LOGTIME, UPTIME, PGDEVICEID) " + 
						   "VALUES (?, ?, ?, ?, ?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
		Object[] params = new Object[9];
		params[0] = fjstat.getAm();
		params[1] = fjstat.getRun();
		params[2] = fjstat.getFlt();
		params[3] = fjstat.getEn();
		params[4] = fjstat.getNo();
		params[5] = fjstat.getRout();
		params[6] = fjstat.getTmStr();
		params[7] = fjstat.getTmStr();
		params[8] = fjstat.getPgdeviceid();
		
		try {
			conn = ConnectionManager.getConnectionFromC3P0();
			conn.setAutoCommit(false);
			
			QueryRunner runner = new QueryRunner();
			int count = runner.update(conn, insertStr, params);
			
			if (count > 0) 
				logger.debug("插入风机运行状态成功" + fjstat);
			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 addStatRecord(boolean run, boolean flt, String tm, int deviceid) {
		Connection conn = null;
		
		String insertStr = "INSERT INTO PG_FJ_STAT " + 
						   "(RUN, FLT, 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] = run;
		params[1] = flt;
		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 + ", run=" + run + ", flt=" + flt + ", 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;
	}

}