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(item[3] != null && item[3].equals("1") == true ? true: false);
stat.setRun(item[4] != null && item[4].equals("1") == true ? true: false);
stat.setFlt(item[5] != null && item[5].equals("1") == true ? true: false);
stat.setEn(item[6] != null && item[6].equals("1") == true ? true: false);
stat.setNo(item[7] != null && item[7].equals("1") == true ? true: false);
stat.setRout(item[8] != null && item[8].equals("1") == true ? true: false);
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(item[3] != null && item[3].equals("1") == true ? true: false);
stat.setRun(item[4] != null && item[4].equals("1") == true ? true: false);
stat.setFlt(item[5] != null && item[5].equals("1") == true ? true: false);
stat.setEn(item[6] != null && item[6].equals("1") == true ? true: false);
stat.setNo(item[7] != null && item[7].equals("1") == true ? true: false);
stat.setRout(item[8] != null && item[8].equals("1") == true ? true: false);
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(item[3] != null && item[3].equals("1") == true ? true: false);
stat.setRun(item[4] != null && item[4].equals("1") == true ? true: false);
stat.setFlt(item[5] != null && item[5].equals("1") == true ? true: false);
stat.setEn(item[6] != null && item[6].equals("1") == true ? true: false);
stat.setNo(item[7] != null && item[7].equals("1") == true ? true: false);
stat.setRout(item[8] != null && item[8].equals("1") == true ? true: false);
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(item[3] != null && item[3].equals("1") == true ? true: false);
stat.setRun(item[4] != null && item[4].equals("1") == true ? true: false);
stat.setFlt(item[5] != null && item[5].equals("1") == true ? true: false);
stat.setEn(item[6] != null && item[6].equals("1") == true ? true: false);
stat.setNo(item[7] != null && item[7].equals("1") == true ? true: false);
stat.setRout(item[8] != null && item[8].equals("1") == true ? true: false);
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(), param);
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(item[3] != null && item[3].equals("1") == true ? true: false);
stat.setRun(item[4] != null && item[4].equals("1") == true ? true: false);
stat.setFlt(item[5] != null && item[5].equals("1") == true ? true: false);
stat.setEn(item[6] != null && item[6].equals("1") == true ? true: false);
stat.setNo(item[7] != null && item[7].equals("1") == true ? true: false);
stat.setRout(item[8] != null && item[8].equals("1") == true ? true: false);
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;
}
@Override
public int addStatRecord(boolean run, boolean flt, boolean am, String tm, int deviceid) {
Connection conn = null;
String insertStr = "INSERT INTO PG_FJ_STAT " +
"(RUN, FLT, AM, LOGTIME, UPTIME, PGDEVICEID) " +
"VALUES (?, ?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
Object[] params = new Object[6];
params[0] = run;
params[1] = flt;
params[2] = am;
params[3] = tm;
params[4] = tm;
params[5] = 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;
}
@Override
public int updateStatRecord(boolean run, boolean flt, boolean am, String tm, int deviceid) {
Connection conn = null;
String updateStr = "UPDATE PG_FJ_STAT SET " +
"RUN = ?, " +
"FLT = ?, " +
"AM = ?, " +
"LOGTIME = TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), " +
"UPTIME = TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') " +
"WHERE PGDEVICEID = ?";
Object[] params = new Object[6];
params[0] = run;
params[1] = flt;
params[2] = am;
params[3] = tm;
params[4] = tm;
params[5] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, updateStr, 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;
}
}
