package com.fgkj.dao.impl;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.UUID;
|
|
import com.fgkj.actions.ActionUtil;
|
import com.fgkj.dao.AlarmDaoFactory;
|
import com.fgkj.dao.BaseDAO;
|
import com.fgkj.dao.CallBack;
|
import com.fgkj.dao.DAOHelper;
|
import com.fgkj.dao.LimitNumberFactory;
|
import com.fgkj.db.DBUtil;
|
import com.fgkj.db.IDatabaseName;
|
import com.fgkj.dto.BattInf;
|
import com.fgkj.dto.BattMap_information;
|
import com.fgkj.dto.Batt_Maint_Dealarm;
|
import com.fgkj.dto.Batt_maint_inf;
|
import com.fgkj.dto.Batt_maintenance_inf;
|
import com.fgkj.dto.Battalarm_data;
|
import com.fgkj.dto.Battresdata;
|
import com.fgkj.dto.Batttestdata_inf;
|
import com.fgkj.dto.Batttestdatastop;
|
import com.fgkj.dto.Page;
|
import com.fgkj.dto.User_inf;
|
|
public class Battalarm_dataDAOImpl implements BaseDAO, CallBack {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Battalarm_data ba = new Battalarm_data();
|
ba.setNum(rs.getInt("num"));
|
ba.setBattGroupId(rs.getInt("BattGroupId"));
|
ba.setMonNum(rs.getInt("MonNum"));
|
ba.setRecord_Id(rs.getLong("Record_Id"));
|
ba.setMonNum(rs.getInt("alm_id"));
|
ba.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
ba.setAlm_level(rs.getInt("alm_level"));
|
ba.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
ba.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
ba.setAlm_value(rs.getFloat("alm_value"));
|
ba.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
ba.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
ba.setAlm_cleared_type(rs.getInt("alm_cleared_type"));
|
ba.setUsr_Id(rs.getInt("usr_Id"));
|
list.add(ba);
|
// System.out.println(b);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
public boolean add(Object obj) {
|
Battalarm_data bd = (Battalarm_data) obj;
|
String sql = "insert into db_alarm.tb_battalarm_data(BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time"
|
+ ",alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
Boolean bl = DAOHelper.executeUpdate(
|
DBUtil.getConn(),
|
sql,
|
new Object[] { bd.getBattGroupId(), bd.getMonNum(),
|
bd.getRecord_Id(), bd.getAlm_id(),
|
bd.getAlm_signal_id(), bd.getAlm_level(),
|
bd.getAlm_start_time(), bd.getAlm_end_time(),
|
bd.getAlm_value(), bd.getAlm_is_confirmed(),
|
bd.getAlm_confirmed_time(), bd.getAlm_cleared_type(),
|
bd.getUsr_Id() });
|
return bl;
|
}
|
|
// 3.1电池告警实时查询(确认告警)
|
public boolean update(Object obj) {
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "update db_alarm.tb_battalarm_data set alm_is_confirmed=?,alm_confirmed_time=? where num=? ";
|
Boolean bl = DAOHelper.executeUpdate(
|
DBUtil.getConn(),
|
sql,
|
new Object[] { adata.getAlm_is_confirmed(),adata.getAlm_confirmed_time(),adata.getNum() });
|
return bl;
|
}
|
|
// 3.1电池告警实时查询(确认告警)(多条记录)
|
public String updatePro(Object obj) {
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "update db_alarm.tb_battalarm_data set alm_is_confirmed="+adata.getAlm_is_confirmed()+",alm_confirmed_time='"+DAOHelper.sdf.format(adata.getAlm_confirmed_time())+"' where num="+adata.getNum();
|
return sql;
|
}
|
//3.1/3.2电池告警查询(删除记录)
|
public boolean del(Object obj) {
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "delete from db_alarm.tb_battalarm_data where num=?";
|
Boolean bl = DAOHelper.executeUpdate(
|
DBUtil.getConn(), sql,
|
new Object[] { adata.getNum() });
|
return bl;
|
}
|
//3.1/3.2电池告警查询(删除记录)<多条记录删除>
|
public String delPro(Object obj){
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "delete from db_alarm.tb_battalarm_data where num="+adata.getNum();
|
return sql;
|
}
|
//当进程重启时,将实时数据修改为历史记录
|
public boolean NowToHistory(){
|
String sql="update db_alarm.tb_battalarm_data set alm_cleared_type=1 where alm_id in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+")";
|
Boolean bl=DAOHelper.executeUpdate(DBUtil.getConn(), sql, null);
|
return bl;
|
}
|
public List searchAll() {
|
String sql = "select * from db_alarm.tb_battalarm_data";
|
List list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(), null,
|
new Battalarm_dataDAOImpl());
|
return list;
|
}
|
|
// 3.1电池容量告警实时查询
|
/*
|
* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id
|
* 组端电压：fault_type_id
|
* 充电电流：fault_level
|
* 放电电流：record_uid
|
* 单体电压：maint_type_id
|
* 单体温度：maint_done
|
* 单体内阻：maint_close
|
* 连接条阻抗：master_id
|
* -----Battalarm_data
|
* MonNum 容量告警告警
|
* Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
* 下限告警/上限告警 tb_battalarm_data
|
* alm_signal_id：为奇数--上限告警
|
* alm_signal_id：为偶数--下限告警
|
*
|
* 当前告警 num: 选中为0 未选中100
|
* 历史告警 monnum：选中为1 未选中100
|
*/
|
public List serchCapAlarm(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
//System.out.println(bmd);
|
Battalarm_data adata = bmd.getAdata();
|
BattInf binf=bmd.getBinf();
|
Batt_maint_inf mainf = bmd.getMainf();
|
List<Batt_Maint_Dealarm> list = new ArrayList<Batt_Maint_Dealarm>();
|
String sql="";
|
String numberSql=" SELECT FOUND_ROWS() number";
|
Connection conn=DBUtil.getConn();
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time "
|
+ "from db_alarm.tb_battalarm_data "
|
+ "where alm_id in(?,?,?,?,?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
"and alm_level in(?,?,?,?) and alm_cleared_type=0 " +
|
"and db_alarm.tb_battalarm_data.BattGroupId in (select battgroupid from db_battinf.tb_battinf " +
|
"where ";
|
//用于维护区
|
String station1SqlT=" stationname1!=? ";//全部
|
String station1SqlF=" stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
//用于机房站点
|
String stationSqlT=" and stationname!=? ";//全部
|
String stationSqlF=" and stationname=? ";
|
if(binf.getStationName().equals("")){
|
baseSql+=stationSqlT;
|
}else{
|
baseSql+=stationSqlF;
|
}
|
|
//用于电池组条件
|
String idSqlT="and db_battinf.tb_battinf.BattGroupId!=?) ";//全部
|
String idSqlF="and db_battinf.tb_battinf.BattGroupId=?) ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
String endSql="order by db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
list = DAOHelper.executeQueryLimit(
|
sql,
|
conn,
|
new Object[] {
|
mainf.getUsr_id(),
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
mainf.getMaster_id(),
|
adata.getMonNum(),
|
adata.getRecord_Id(),
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattGroupId(),
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
Page page = new Page();
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
Batt_Maint_Dealarm blast = new Batt_Maint_Dealarm();
|
Page page=new Page();
|
page.setPageAll(number);
|
blast.setPage(page);
|
list.add(blast);
|
return list;
|
}
|
|
// 3.1电池告警实时查询
|
|
/* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id
|
* 组端电压：fault_type_id
|
* 充电电流：fault_level
|
* 放电电流：record_uid
|
* 单体电压：maint_type_id
|
* 单体温度：maint_done
|
* 单体内阻：maint_close
|
* 连接条阻抗：master_id
|
* -----Battalarm_data
|
* MonNum 容量告警告警
|
* Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
* 下限告警/上限告警 tb_battalarm_data
|
* alm_signal_id：为奇数--上限告警
|
* alm_signal_id：为偶数--下限告警
|
*
|
* 当前告警 num: 选中为0 未选中100
|
* 历史告警 monnum：选中为1 未选中100
|
*/
|
/*public List serchByCondition(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
//System.out.println(bmd);
|
Battalarm_data adata = bmd.getAdata();
|
BattInf binf=bmd.getBinf();
|
Batt_maint_inf mainf = bmd.getMainf();
|
List<Batt_Maint_Dealarm> list = new ArrayList<Batt_Maint_Dealarm>();
|
String sql="";
|
String numberSql=" SELECT FOUND_ROWS() number";
|
Connection conn=DBUtil.getConn();
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time "
|
+ "from db_alarm.tb_battalarm_data "
|
+ "where alm_id in(?,?,?,?,?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
"and alm_level in(?,?,?,?) and alm_cleared_type=0 " +
|
"and db_alarm.tb_battalarm_data.BattGroupId in (select battgroupid from db_battinf.tb_battinf " +
|
"where ";
|
//用于维护区
|
String station1SqlT=" stationname1!=? ";//全部
|
String station1SqlF=" stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
//用于机房站点
|
String stationSqlT=" and stationname!=? ";//全部
|
String stationSqlF=" and stationname=? ";
|
if(binf.getStationName().equals("")){
|
baseSql+=stationSqlT;
|
}else{
|
baseSql+=stationSqlF;
|
}
|
|
//用于电池组条件
|
String idSqlT="and db_battinf.tb_battinf.BattGroupId!=?) ";//全部
|
String idSqlF="and db_battinf.tb_battinf.BattGroupId=?) ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
String endSql="order by db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
list = DAOHelper.executeQueryLimit(
|
sql,
|
conn,
|
new Object[] {
|
mainf.getUsr_id(),
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
mainf.getMaster_id(),
|
adata.getMonNum(),
|
adata.getRecord_Id(),
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattGroupId(),
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
Page page = new Page();
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
Batt_Maint_Dealarm blast = new Batt_Maint_Dealarm();
|
Page page=new Page();
|
page.setPageAll(number);
|
blast.setPage(page);
|
list.add(blast);
|
|
return list;
|
}*/
|
// 3.1电池告警实时查询
|
/*
|
* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id
|
* 组端电压：fault_type_id
|
* 充电电流：fault_level
|
* 放电电流：record_uid
|
* 单体电压：maint_type_id
|
* 单体温度：maint_done
|
* 单体内阻：maint_close
|
* 连接条阻抗：master_id
|
* -----Battalarm_data
|
* MonNum 容量告警告警
|
* Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
* 下限告警/上限告警 tb_battalarm_data
|
* alm_signal_id：为奇数--上限告警
|
* alm_signal_id：为偶数--下限告警
|
*
|
* 当前告警 num: 选中为0 未选中100
|
* 历史告警 monnum：选中为1 未选中100
|
*/
|
public List serchByCondition(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
Battalarm_data adata = bmd.getAdata();
|
BattInf binf=bmd.getBinf();
|
Batt_maint_inf mainf = bmd.getMainf();
|
User_inf uinf=bmd.getUinf();
|
List<Batt_Maint_Dealarm> list = new ArrayList<Batt_Maint_Dealarm>();
|
String sql="";
|
String numberSql=" SELECT FOUND_ROWS() number";
|
Connection conn=DBUtil.getConn();
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time "
|
+ " ,stationname,battgroupname"
|
+ " from db_alarm.tb_battalarm_data,db_battinf.tb_battinf "
|
+ " where alm_id in(?,?,?,?,?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
" and alm_level in(?,?,?,?) and alm_cleared_type=0 " +
|
" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ? ";
|
//用于电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";//全部
|
String idSqlF=" and db_battinf.tb_battinf.battgroupid=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//用于用户管理的电池组的筛选
|
String userSql=" and db_alarm.tb_battalarm_data.BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )";
|
baseSql+=userSql;
|
String endSql="order by db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
list = DAOHelper.executeQueryLimit(
|
sql,
|
conn,
|
new Object[] {
|
mainf.getUsr_id(),
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
mainf.getMaster_id(),
|
adata.getMonNum(),
|
adata.getRecord_Id(),
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
|
"%"+binf.getStationName1()+"%",
|
"%"+binf.getStationName()+"%",
|
binf.getBattGroupId(),
|
uinf.getUId(),
|
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize() }, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
String name="未知"; //存放告警名称
|
String type="未知"; //存放告警等级
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
Page page = new Page();
|
BattInf binf=new BattInf();
|
binf.setBattGroupId(rs.getInt("battGroupId"));
|
binf.setStationName(rs.getString("stationName"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
//battinf中的StationName8，StationName9存放告警名称和等级
|
name=AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id());
|
type=AlarmDaoFactory.getAlarmType(adata.getAlm_level());
|
binf.setStationName8(name);
|
binf.setStationName9(type);
|
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
Batt_Maint_Dealarm blast = new Batt_Maint_Dealarm();
|
Page page=new Page();
|
page.setPageAll(number);
|
blast.setPage(page);
|
list.add(blast);
|
|
return list;
|
}
|
// 3.2电池告警
|
/*
|
* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id 119001
|
* 组端电压：fault_type_id 119002
|
* 充电电流：fault_level 119003
|
* 放电电流：record_uid 119004
|
* 单体电压：maint_type_id 119005
|
* 单体温度：maint_done 119006
|
* 单体内阻：maint_close 119007
|
* 连接条阻抗：master_id 119008
|
* -----alarm_data
|
* MonNum 容量告警告警
|
*Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
*
|
* 下限告警/上限告警 alarm_data
|
* alm_signal_id：为奇数--上限告警（alm_id） 选中1 未选中100
|
* alm_signal_id：为偶数--下限告警 (alm_signal_id) 选中0 未选中100
|
*
|
* 告警状态alm_cleared_type;
|
* 当前告警:选中0
|
* 历史告警:选中1
|
* 两者告警:选中100
|
* 全都不选:不选200
|
|
*/
|
/*public List serchByInfo(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Batttestdata_inf tdata=bmd.getTdata();
|
Battalarm_data adata = bmd.getAdata();
|
Batt_maint_inf mainf = bmd.getMainf();
|
String sql="";
|
String numberSql="SELECT FOUND_ROWS() number";//存放总数
|
Connection conn=DBUtil.getConn();//sql和numberSql共用一个connection
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct(db_alarm.tb_battalarm_data.BattGroupId),db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time " +
|
"from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
"where alm_id in(?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
"and alm_level in(?,?,?,?) " +
|
"and alm_start_time>=? and alm_start_time<=? ";
|
//用于实时告警
|
String almSqlT="and alm_cleared_type=? ";
|
//历史告警
|
String almSqlF="and alm_cleared_type in(?,1,2) ";
|
//两者均包含
|
String almSqlA="and alm_cleared_type in(?,0,1,2) ";
|
if(adata.getAlm_cleared_type()==0){
|
baseSql+=almSqlT;
|
}else if(adata.getAlm_cleared_type()==1){
|
baseSql+=almSqlF;
|
}else if(adata.getAlm_cleared_type()==100){
|
baseSql+=almSqlA;
|
}else if(adata.getAlm_cleared_type()==200){
|
baseSql+=almSqlT;
|
}
|
//用于battinf部分
|
String binfSql=" and db_alarm.tb_battalarm_data.BattGroupId=db_battinf.tb_battinf.BattGroupId ";
|
baseSql+=binfSql;
|
//用于维护区
|
String station1SqlT=" and stationname1!=? ";//全部
|
String station1SqlF=" and stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
//用于机房站点
|
String stationSqlT=" and stationname!=? ";//全部
|
String stationSqlF=" and stationname=? ";
|
if(binf.getStationName().equals("")){
|
baseSql+=stationSqlT;
|
}else{
|
baseSql+=stationSqlF;
|
}
|
//用于电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.BattGroupId!=? ";//全部
|
String idSqlF=" and db_battinf.tb_battinf.BattGroupId=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
String endSql="order by db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
//System.out.println(sql);
|
List<Batt_Maint_Dealarm> list= DAOHelper.executeQueryLimit(sql,conn,
|
new Object[] {
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
|
adata.getAlm_start_time(),
|
adata.getAlm_start_time1(),
|
adata.getAlm_cleared_type(),
|
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattGroupId(),
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize()},
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
BattInf binf = new BattInf();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
|
Page page = new Page();
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
//System.out.println("number: "+number);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).getPage().setPageAll(number);
|
}
|
return list;
|
}*/
|
|
// 3.2电池告警
|
/*
|
* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id 119001
|
* 组端电压：fault_type_id 119002
|
* 充电电流：fault_level 119003
|
* 放电电流：record_uid 119004
|
* 单体电压：maint_type_id 119005
|
* 单体温度：maint_done 119006
|
* 单体内阻：maint_close 119007
|
* 连接条阻抗：master_id 119008
|
* -----alarm_data
|
* MonNum 容量告警告警
|
*Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
*
|
* 下限告警/上限告警 alarm_data
|
* alm_signal_id：为奇数--上限告警（alm_id） 选中1 未选中100
|
* alm_signal_id：为偶数--下限告警 (alm_signal_id) 选中0 未选中100
|
*
|
* 告警状态alm_cleared_type;
|
* 当前告警:选中0
|
* 历史告警:选中1
|
* 两者告警:选中100
|
* 全都不选:不选200
|
|
*/
|
public List serchByInfo(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Batttestdata_inf tdata=bmd.getTdata();
|
Battalarm_data adata = bmd.getAdata();
|
Batt_maint_inf mainf = bmd.getMainf();
|
User_inf uinf=bmd.getUinf();
|
String sql="";
|
String numberSql="SELECT FOUND_ROWS() number";//存放总数
|
Connection conn=DBUtil.getConn();//sql和numberSql共用一个connection
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct(db_alarm.tb_battalarm_data.BattGroupId),db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time, " +
|
" stationname,battgroupname" +
|
" from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
" where alm_id in(?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
" and alm_level in(?,?,?,?) " +
|
" and alm_start_time>=? and alm_start_time<=? " +
|
" and db_battinf.tb_battinf.stationname1 like ? and db_battinf.tb_battinf.stationname like ? ";
|
//用于实时告警
|
String almSqlT="and alm_cleared_type=? ";
|
//历史告警
|
String almSqlF="and alm_cleared_type in(?,1,2) ";
|
//两者均包含
|
String almSqlA="and alm_cleared_type in(?,0,1,2) ";
|
if(adata.getAlm_cleared_type()==0){
|
baseSql+=almSqlT;
|
}else if(adata.getAlm_cleared_type()==1){
|
baseSql+=almSqlF;
|
}else if(adata.getAlm_cleared_type()==100){
|
baseSql+=almSqlA;
|
}else if(adata.getAlm_cleared_type()==200){
|
baseSql+=almSqlT;
|
}
|
|
//用于电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";//全部
|
String idSqlF=" and db_battinf.tb_battinf.battgroupid=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//用于用户管理的电池组的筛选
|
String userSql=" and db_alarm.tb_battalarm_data.BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )";
|
baseSql+=userSql;
|
String endSql="order by db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
List<Batt_Maint_Dealarm> list= DAOHelper.executeQueryLimit(sql,conn,
|
new Object[] {
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
|
adata.getAlm_start_time(),
|
adata.getAlm_start_time1(),
|
"%"+binf.getStationName1()+"%",
|
"%"+binf.getStationName()+"%",
|
adata.getAlm_cleared_type(),
|
|
|
binf.getBattGroupId(),
|
uinf.getUId(),
|
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize()},
|
new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
String name="未知"; //存放告警名称
|
String type="未知"; //存放告警等级
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
BattInf binf = new BattInf();
|
binf.setStationName(rs.getString("stationName"));
|
binf.setBattGroupName(rs.getString("battGroupName"));
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
//battinf中的StationName8，StationName9存放告警名称和等级
|
name=AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id());
|
type=AlarmDaoFactory.getAlarmType(adata.getAlm_level());
|
binf.setStationName8(name);
|
binf.setStationName9(type);
|
|
Page page = new Page();
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
//System.out.println("number: "+number);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).getPage().setPageAll(number);
|
}
|
return list;
|
}
|
|
// 3.2电池告警
|
/*
|
* 条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id 119001
|
* 组端电压：fault_type_id 119002
|
* 充电电流：fault_level 119003
|
* 放电电流：record_uid 119004
|
* 单体电压：maint_type_id 119005
|
* 单体温度：maint_done 119006
|
* 单体内阻：maint_close 119007
|
* 连接条阻抗：master_id 119008
|
* -----alarm_data
|
* MonNum 容量告警告警
|
*Record_Id 容量更换告警
|
* 告警等级
|
* 一级告警：num
|
* 二级告警：battgroupid
|
* 三级告警：master_audit
|
* 四级告警：appoint-uid
|
*
|
*
|
* 下限告警/上限告警 alarm_data
|
* alm_signal_id：为奇数--上限告警（alm_id） 选中1 未选中100
|
* alm_signal_id：为偶数--下限告警 (alm_signal_id) 选中0 未选中100
|
*
|
* 告警状态alm_cleared_type;
|
* 当前告警:选中0
|
* 历史告警:选中1
|
* 两者告警:选中100
|
* 全都不选:不选200
|
* 包机人
|
* usr_id: 全部100，单个名字对应的uid
|
*/
|
@SuppressWarnings("unchecked")
|
public List serchByInfo1(Object obj) {
|
Batt_Maint_Dealarm bmd = (Batt_Maint_Dealarm) obj;
|
BattInf binf=bmd.getBinf();
|
Batttestdata_inf tdata=bmd.getTdata();
|
Battalarm_data adata = bmd.getAdata();
|
Batt_maint_inf mainf = bmd.getMainf();
|
List<Batt_Maint_Dealarm> list = new ArrayList<Batt_Maint_Dealarm>();
|
String sql="";
|
String numberSql="SELECT FOUND_ROWS() number";//存放总数
|
Connection conn=DBUtil.getConn();//sql和numberSql共用一个connection
|
String baseSql = "select SQL_CALC_FOUND_ROWS distinct(db_alarm.tb_battalarm_data.BattGroupId),db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time " +
|
"from db_alarm.tb_battalarm_data " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_battgroup on db_user.tb_user_battgroup_baojigroup_battgroup.battgroupid=db_alarm.tb_battalarm_data.BattGroupId " +
|
"left outer join db_user.tb_user_battgroup_baojigroup_usr on db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id= db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id " +
|
"left outer join db_user.tb_user_inf on db_user.tb_user_inf.uId=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
"where alm_id in(?,?,?,?,?,?,?,?,?,?) and alm_signal_id%2 in(?,?) " +
|
"and alm_level in(?,?,?,?) " +
|
"and alm_start_time>=? and alm_start_time<=? ";
|
//用于实时告警
|
String almSqlT="and alm_cleared_type=? ";
|
//历史告警
|
String almSqlF="and alm_cleared_type in(?,1,2) ";
|
//两者均包含
|
String almSqlA="and alm_cleared_type in(?,0,1,2) ";
|
if(adata.getAlm_cleared_type()==0){
|
baseSql+=almSqlT;
|
}else if(adata.getAlm_cleared_type()==1){
|
baseSql+=almSqlF;
|
}else if(adata.getAlm_cleared_type()==100){
|
baseSql+=almSqlA;
|
}else if(adata.getAlm_cleared_type()==200){
|
baseSql+=almSqlT;
|
}
|
|
//用于包机人条件
|
String userSqlT="and db_user.tb_user_inf.uId!=? and db_user.tb_user_inf.ubaojiusr=1 ";//全部
|
String userSqlF="and db_user.tb_user_inf.uId=? and db_user.tb_user_inf.ubaojiusr=1 ";
|
if(adata.getUsr_Id()==100){
|
baseSql+=userSqlT;
|
}else{
|
baseSql+=userSqlF;
|
}
|
//用于电池测试记录条件:
|
//放电/充电单个/在线监测记录
|
String testSqlH="and db_alarm.tb_battalarm_data.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type=? and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time) ";
|
//内阻/电导
|
String testSqlF="and db_alarm.tb_battalarm_data.BattGroupId in(select distinct(db_batt_testdata.tb_battresdata_inf.BattGroupId ) from db_batt_testdata.tb_battresdata_inf where db_batt_testdata.tb_battresdata_inf.test_type!=? and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time)) ";
|
//充电或者放电都行
|
String testSqlT="and db_alarm.tb_battalarm_data.BattGroupId in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time) ";
|
//无记录
|
String testSqlN=" and db_alarm.tb_battalarm_data.BattGroupId not in(select distinct(db_batt_testdata.tb_battresdata_inf.BattGroupId ) from db_batt_testdata.tb_battresdata_inf where db_batt_testdata.tb_battresdata_inf.test_type in (?,5,6) and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time)) " +
|
" and db_alarm.tb_battalarm_data.BattGroupId not in(select distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId ) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time) " ;
|
//全部
|
String testSqlA="and db_alarm.tb_battalarm_data.BattGroupId!=(select count(distinct(db_batt_testdata.tb_batttestdata_inf.BattGroupId )) from db_batt_testdata.tb_batttestdata_inf where db_batt_testdata.tb_batttestdata_inf.test_type in(?,2,3) and (record_time)>=(?) and (record_time)<=(?) and (test_starttime)<=(record_time) " ;
|
|
if(tdata.getTest_type()==2){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==3){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==9){
|
baseSql+=testSqlH;
|
}else if(tdata.getTest_type()==5000){
|
baseSql+=testSqlF;
|
}else if(tdata.getTest_type()==3000){//充电或放电
|
baseSql+=testSqlT;
|
}else if(tdata.getTest_type()==4000){//无记录
|
baseSql+=testSqlN;
|
}else if(tdata.getTest_type()==0){
|
baseSql+=testSqlA;
|
}
|
//用于测试类型
|
//全部
|
String start_typeA=" )";
|
//拉闸放电
|
String start_typeF=" and test_starttype<5 and test_type not in(2,9) and test_starttype!=2) ";
|
//核对性放电
|
String start_typeT=" and test_starttype>=5 or (test_type not in(2,9) and test_starttype=2)) ";
|
if(tdata.getTest_type()!=5000){
|
if(tdata.getTest_starttype()==0){
|
baseSql+=start_typeA;
|
}else if(tdata.getTest_starttype()==1){
|
baseSql+=start_typeF;
|
}else if(tdata.getTest_starttype()==2){
|
baseSql+=start_typeT;
|
}
|
}
|
|
//用于电池维护记录条件
|
//有维护记录
|
String maintSqlT="and db_alarm.tb_battalarm_data.BattGroupId in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where (maint_done_time)>=(?) and (maint_done_time)<=(?)) " ;
|
//无维护记录
|
String maintSqlF="and db_alarm.tb_battalarm_data.BattGroupId not in(select db_battinf.tb_batt_maint_inf.battgroupid from db_battinf.tb_batt_maint_inf where (maint_done_time)>=(?) and (maint_done_time)<=(?)) " ;
|
//全部
|
String maintSqlA="and db_alarm.tb_battalarm_data.BattGroupId!=(select count(distinct(db_battinf.tb_batt_maint_inf.battgroupid )) from db_battinf.tb_batt_maint_inf where (maint_done_time)>=(?) and (maint_done_time)<=(?)) " ;
|
if(mainf.getRemark().equals("0")){
|
baseSql+=maintSqlF;
|
}else if(mainf.getRemark().equals("1")){
|
baseSql+=maintSqlT;
|
}else if(mainf.getRemark().equals("100")){
|
baseSql+=maintSqlA;
|
}
|
//用于battinf部分
|
String binfSql=" and db_alarm.tb_battalarm_data.BattGroupId in(select db_battinf.tb_battinf.BattGroupId from db_battinf.tb_battinf where (battProductDate)>=(?) and (battProductDate)<=(?) and (battinusedate)>=(?) and (battinusedate)<=(?) ";
|
baseSql+=binfSql;
|
//用于维护区
|
String station1SqlT=" and stationname1!=? ";//全部
|
String station1SqlF=" and stationname1=? ";
|
if(binf.getStationName1().equals("")){
|
baseSql+=station1SqlT;
|
}else{
|
baseSql+=station1SqlF;
|
}
|
//用于机房站点
|
String stationSqlT=" and stationname!=? ";//全部
|
String stationSqlF=" and stationname=? ";
|
if(binf.getStationName().equals("")){
|
baseSql+=stationSqlT;
|
}else{
|
baseSql+=stationSqlF;
|
}
|
//用于电池类型
|
String producerSqlT=" and battproducer!=? ";//全部
|
String producerSqlF=" and battproducer=? ";
|
if(binf.getBattProducer().equals("")){
|
baseSql+=producerSqlT;
|
}else{
|
baseSql+=producerSqlF;
|
}
|
|
//用于电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.BattGroupId!=? ";//全部
|
String idSqlF=" and db_battinf.tb_battinf.BattGroupId=? ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
//用于单体电压
|
String volSqlT="and monVolStd!=? ";//全部
|
String volSqlF="and monVolStd=? " ;
|
if(binf.getMonVolStd()==0){
|
baseSql+=volSqlT;
|
}else{
|
baseSql+=volSqlF;
|
}
|
//用于电池容量
|
String capSqlT="and monCapStd!=? ";//全部
|
String capSqlF="and monCapStd=?" ;
|
if(binf.getMonCapStd()==0){
|
baseSql+=capSqlT;
|
}else{
|
baseSql+=capSqlF;
|
}
|
//系统类型
|
String nameSqlT="and BattGroupName1 like ?) ";
|
String nameSqlF="and BattGroupName1 not like '%开关电源系统%' and BattGroupName1 not like '%UPS系统%' and BattGroupName1 not like ?) ";
|
if(binf.getBattGroupName1().equals("其他")){
|
baseSql+=nameSqlF;
|
}else{
|
baseSql+=nameSqlT;
|
}
|
|
|
String endSql="order by db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
String limitSql=" limit ?,? ";
|
sql=baseSql+endSql+limitSql;
|
|
System.out.println(sql);
|
if(tdata.getTest_type()==4000){
|
list = DAOHelper.executeQueryLimit(
|
sql,
|
conn,
|
new Object[] {
|
mainf.getUsr_id(),
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
mainf.getMaster_id(),
|
adata.getMonNum(),
|
adata.getRecord_Id(),
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
|
adata.getAlm_start_time(),
|
adata.getAlm_start_time1(),
|
adata.getAlm_cleared_type(),
|
adata.getUsr_Id(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1(),
|
|
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
binf.getBattGroupId(),
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
"%"+binf.getBattGroupName1()+"%",
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize()}, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
BattInf binf = new BattInf();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
|
Page page = new Page();
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}else{
|
list = DAOHelper.executeQueryLimit(
|
sql,
|
conn,
|
new Object[] {
|
mainf.getUsr_id(),
|
mainf.getFault_type_id(),
|
mainf.getFault_level(),
|
mainf.getRecord_uid(),
|
mainf.getMaint_type_id(),
|
mainf.getMaint_done(),
|
mainf.getMaint_close(),
|
mainf.getMaster_id(),
|
adata.getMonNum(),
|
adata.getRecord_Id(),
|
|
adata.getAlm_id(),
|
adata.getAlm_signal_id(),
|
|
mainf.getNum(),
|
mainf.getBattGroupId(),
|
mainf.getMaster_audit(),
|
mainf.getAppoint_uid(),
|
|
adata.getAlm_start_time(),
|
adata.getAlm_start_time1(),
|
adata.getAlm_cleared_type(),
|
adata.getUsr_Id(),
|
|
tdata.getTest_type(),
|
tdata.getRecord_time(),
|
tdata.getRecord_time1(),
|
|
mainf.getMaint_done_time(),
|
mainf.getMaint_done_time1(),
|
|
|
binf.getBattProductDate(),
|
binf.getBattProductDate1(),
|
binf.getBattInUseDate(),
|
binf.getBattInUseDate1(),
|
binf.getStationName1(),
|
binf.getStationName(),
|
binf.getBattProducer(),
|
binf.getBattGroupId(),
|
binf.getMonVolStd(),
|
binf.getMonCapStd(),
|
"%"+binf.getBattGroupName1()+"%",
|
(bmd.getPage().getPageCurr() - 1)* bmd.getPage().getPageSize(),
|
bmd.getPage().getPageSize()}, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
BattInf binf = new BattInf();
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
|
Page page = new Page();
|
bmd.setBinf(binf);
|
bmd.setAdata(adata);
|
|
bmd.setPage(page);
|
list.add(bmd);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
}
|
//去掉limit条件后的总数
|
int number=LimitNumberFactory.GetLimtitNumber(conn, numberSql);
|
//System.out.println("number: "+number);
|
if(list!=null&&list.size()>0){
|
list.get(list.size()-1).getPage().setPageAll(number);
|
}
|
return list;
|
}
|
//0.5查询实时告警数
|
/*public List serchAlm(Object obj){
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
Battalarm_data adata=bmd.getAdata();
|
BattInf binf=bmd.getBinf();
|
String sql="select count(num) as number,alm_id " +
|
"from db_alarm.tb_battalarm_data " +
|
"where " +
|
"alm_cleared_type=0 " ;
|
//根据城市选取
|
String citySql=" and tb_battalarm_data.BattGroupId in (select distinct(db_battinf.tb_battinf.BattGroupId) from db_battinf.tb_battinf where stationname like ? ) GROUP BY(alm_id) ";
|
sql+=citySql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{"%"+binf.getStationName()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
|
adata.setNum(rs.getInt("number"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}*/
|
public List serchAlm(Object obj){
|
Batt_Maint_Dealarm bmd=(Batt_Maint_Dealarm) obj;
|
Battalarm_data adata=bmd.getAdata();
|
BattInf binf=bmd.getBinf();
|
User_inf uinf=bmd.getUinf();
|
String sql="select count(num) as number,alm_id " +
|
"from db_alarm.tb_battalarm_data " +
|
"where " +
|
"alm_cleared_type=0 " ;
|
//根据城市选取
|
String citySql=" and tb_battalarm_data.BattGroupId in (select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? and db_battinf.tb_battinf.stationname like ? ) " +
|
" GROUP BY(alm_id) ";
|
sql+=citySql;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId(),"%"+binf.getStationName()+"%"}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
|
adata.setNum(rs.getInt("number"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//0.10实时告警记录总数查询
|
/*public List serchRealTime(){
|
String sql="select count(num) as number " +
|
"from db_alarm.tb_battalarm_data " +
|
"where " +
|
"alm_cleared_type=0 " +
|
"and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+
|
"and db_alarm.tb_battalarm_data.BattGroupId in (select battgroupid from db_battinf.tb_battinf )";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setNum(rs.getInt("number"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}*/
|
public List serchRealTime(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select count(num) as number " +
|
"from db_alarm.tb_battalarm_data " +
|
"where " +
|
"alm_cleared_type=0 " +
|
"and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+
|
"and db_alarm.tb_battalarm_data.BattGroupId in (select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setNum(rs.getInt("number"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
// 根据battgroupId和阈值查数据
|
public List serchByBatt(Object obj) {
|
Battalarm_data bad = (Battalarm_data) obj;
|
String sql = "select distinct(battgroupid),monnum from db_alarm.tb_battalarm_data where battgroupid=? order by battgroupid";
|
List list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(),
|
new Object[] { bad.getBattGroupId() }, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Battalarm_data adata = new Battalarm_data();
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setMonNum(rs.getInt("monNum"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
// 根据battgroupId和alm_cleared_type查新增告警记录
|
public List serchByAlmType(Object obj) {
|
Battalarm_data bad = (Battalarm_data) obj;
|
String sql = "select distinct(battgroupid),monnum from db_alarm.tb_battalarm_data where battgroupid=? and alm_cleared_type=0";
|
// String
|
// sql="select battgroupid,monnum from tb_battalarm_data where alm_cleared_type=1";
|
List list = DAOHelper.executeQuery(sql,
|
DBUtil.getConn(),
|
new Object[] { bad.getBattGroupId() }, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
Battalarm_data adata = new Battalarm_data();
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setMonNum(rs.getInt("monNum"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//根据num取消告警
|
public boolean cancelalarm(Object obj){
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "update db_alarm.tb_battalarm_data set record_id=?,alm_end_time=?,alm_cleared_type=? where num=? ";
|
Boolean bl = DAOHelper.executeUpdate(
|
DBUtil.getConn(),
|
sql,
|
new Object[] { adata.getRecord_Id(),adata.getAlm_end_time(),adata.getAlm_cleared_type(),adata.getNum() });
|
return bl;
|
}
|
|
//根据num取消告警(多条记录)
|
public String cancelalarmPro(Object obj){
|
Battalarm_data adata = (Battalarm_data) obj;
|
String sql = "update db_alarm.tb_battalarm_data set record_id="+adata.getRecord_Id()+",alm_end_time='"+DAOHelper.sdf.format(adata.getAlm_end_time())+"',alm_cleared_type="+adata.getAlm_cleared_type()+" where num="+adata.getNum();
|
return sql;
|
}
|
|
//7.2增加容量告警的数据
|
public boolean addPro(Object obj) {
|
List<Batt_Maint_Dealarm> list=(List<Batt_Maint_Dealarm>) obj;
|
String sql ="";//存放所有的插入语句
|
ArrayList<String> sql_str=new ArrayList();
|
if(list!=null&&list.size()>0){
|
for(int i=0;i<list.size();i++){
|
Batttestdatastop bstop=(Batttestdatastop) list.get(i).getSdata();
|
Battalarm_data bd = new Battalarm_data();
|
bd.setBattGroupId(bstop.getBattGroupId());
|
bd.setMonNum(bstop.getMon_num());
|
bd.setRecord_Id(UUID.randomUUID().getLeastSignificantBits());
|
bd.setAlm_id(bstop.getNum());
|
bd.setAlm_signal_id(bstop.getTest_type());//存放告警类型的高低告警
|
bd.setAlm_level(bstop.getData_available());//默认为表中当前告警类型的告警等级
|
bd.setAlm_start_time(new Date());//当前记录的时间
|
bd.setAlm_end_time(new Date());
|
bd.setAlm_value(bstop.getTest_cap());//默认为当前告警类型的实际值
|
bd.setAlm_is_confirmed(0);
|
bd.setAlm_confirmed_time(new Date());//默认为当前时间
|
bd.setAlm_cleared_type(0);//默认为实时告警0
|
bd.setUsr_Id(0);
|
int flag=judge(bd);//判断改电池组是否存在同类型的告警
|
|
if(flag==1){
|
sql = "update db_alarm.tb_battalarm_data set Record_Id="+bd.getRecord_Id()+",alm_signal_id="+bd.getAlm_signal_id()+",alm_level="+bd.getAlm_level()
|
+",alm_start_time='"+DAOHelper.sdf.format(bd.getAlm_start_time())+"',alm_end_time='"+DAOHelper.sdf.format(bd.getAlm_end_time())+"',alm_value="+bd.getAlm_value()+",alm_is_confirmed="+bd.getAlm_is_confirmed()
|
+",alm_confirmed_time='"+DAOHelper.sdf.format(bd.getAlm_confirmed_time())+"',usr_Id="+bd.getUsr_Id()
|
+" where BattGroupId="+bd.getBattGroupId()+" and MonNum="+bd.getMonNum()+" and alm_id="+bd.getAlm_id()+" and alm_cleared_type="+bd.getAlm_cleared_type();
|
}else{
|
sql = "insert into db_alarm.tb_battalarm_data(BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time"
|
+ ",alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id) " +
|
"values(+"+bd.getBattGroupId()+","
|
+ bd.getMonNum()+","
|
+bd.getRecord_Id()+","
|
+bd.getAlm_id()+","
|
+bd.getAlm_signal_id()+","
|
+bd.getAlm_level()+","
|
+"'"+DAOHelper.sdf.format(bd.getAlm_start_time())+"',"
|
+"'"+DAOHelper.sdf.format(bd.getAlm_end_time())+"',"
|
+bd.getAlm_value()+","
|
+bd.getAlm_is_confirmed()+","
|
+"'"+DAOHelper.sdf.format(bd.getAlm_confirmed_time())+"',"
|
+bd.getAlm_cleared_type()+","
|
+bd.getUsr_Id()+")";
|
}
|
sql_str.add(sql);
|
}
|
}
|
Boolean bl=DAOHelper.makeManualCommit(DBUtil.getConn(), sql_str);
|
return bl;
|
}
|
//9.1根据机房信息查询该机房实时告警总数
|
public int serchByStationId(Object obj){
|
BattMap_information binformation=(BattMap_information) obj;
|
String sql="select count(num) as nums from db_alarm.tb_battalarm_data " +
|
"where " +
|
"battgroupid in (select distinct battgroupid from db_battinf.tb_battinf where stationid=? ) " +
|
"and alm_cleared_type=0 and alm_id not in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+") limit 1 ";
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binformation.getStationId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
int nums=rs.getInt("nums");
|
list.add(nums);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int nums=0;//最后的结果
|
if(list!=null&&list.size()>0){
|
nums=(Integer) list.get(list.size()-1);
|
}
|
|
return nums;
|
}
|
|
//9.1根据机房stationid查询机房下电池组单体告警情况
|
public String serchMonAlarm(Object obj){
|
BattInf binf=(BattInf) obj;
|
String sql=" select battgroupid,GROUP_CONCAT(DISTINCT monnum) as monnums from db_alarm.tb_battalarm_data " +
|
" where " +
|
" battgroupid =? " +
|
" and alm_cleared_type=0 " +
|
" GROUP BY battgroupid limit 1";
|
//System.out.println(binf.getBattGroupId()+"********************");
|
List<Battalarm_data> list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{binf.getBattGroupId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setNote(rs.getString("monnums"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
String monAlarms="";//存放指定电池组告警单体信息
|
if(list!=null&&list.size()>0){
|
monAlarms=list.get(list.size()-1).getNote();
|
}
|
return monAlarms;
|
}
|
//7.2落后单体筛选出来后，往告警表添加前的判断
|
public int judge(Object obj){
|
Battalarm_data bd = (Battalarm_data) obj;
|
String sql=" SELECT COUNT(num) AS nums FROM db_alarm.tb_battalarm_data WHERE battgroupid=? and monnum=? and alm_id=? and alm_cleared_type=? ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(),new Object[]{bd.getBattGroupId(),bd.getMonNum(),bd.getAlm_id(),bd.getAlm_cleared_type()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
int nums=rs.getInt("nums");
|
list.add(nums);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
int flag=0;//返回标识符
|
if(list!=null&&list.size()>0){
|
int nums=(Integer) list.get(list.size()-1);
|
if(nums>0){
|
flag=1;
|
}else{
|
flag=0;
|
}
|
}
|
return flag;
|
}
|
|
//饼状图电池单体健康率
|
/*public List serchGood(){
|
String sql="SELECT battgroupid,count(DISTINCT MonNum) as nums,alm_id " +
|
"FROM db_alarm.tb_battalarm_data " +
|
"where BattGroupId in(select BattGroupId from db_battinf.tb_battinf) " +
|
"and alm_id in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+") " +
|
"and alm_cleared_type=0 " +
|
"GROUP BY battgroupid,alm_id " ;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int capAlarm=0;//告警告警总数
|
int capChange=0;//更换告警总数
|
try {
|
while(rs.next()){
|
int num=rs.getInt("nums");
|
int alm_id=rs.getInt("alm_id");
|
if(alm_id==AlarmDaoFactory.Alarm_CapAlarm){
|
capAlarm+=num;
|
}else if(alm_id==AlarmDaoFactory.Alarm_CapChange){
|
capChange+=num;
|
}
|
}
|
list.add(capAlarm);
|
list.add(capChange);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}*/
|
public List serchGood(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="SELECT battgroupid,count(DISTINCT MonNum) as nums,alm_id " +
|
" FROM db_alarm.tb_battalarm_data " +
|
" where BattGroupId in(select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? ) " +
|
" and alm_id in("+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+") " +
|
" and alm_cleared_type=0 " +
|
" GROUP BY battgroupid,alm_id " ;
|
//System.out.println(sql);
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
int capAlarm=0;//告警告警总数
|
int capChange=0;//更换告警总数
|
try {
|
while(rs.next()){
|
int num=rs.getInt("nums");
|
int alm_id=rs.getInt("alm_id");
|
if(alm_id==AlarmDaoFactory.Alarm_CapAlarm){
|
capAlarm+=num;
|
}else if(alm_id==AlarmDaoFactory.Alarm_CapChange){
|
capChange+=num;
|
}
|
}
|
list.add(capAlarm);
|
list.add(capChange);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//项目下方的滚动，查询最新电池告警(旧版本)
|
public List serchTopAlmInBatt(){
|
String sql="select db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id " +
|
" from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
" where alm_cleared_type=0 " +
|
" and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid " +
|
" order by alm_start_time desc " +
|
" limit 1";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setStationid(rs.getString("stationid"));
|
adata.setStationname(rs.getString("stationname"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
//项目下方的滚动，查询最新电池告警(新版本)
|
/*public List serchTopBattTen(){
|
String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id " +
|
" from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
" where alm_cleared_type=0 " +
|
" and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid " +
|
" and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+
|
" order by alm_start_time desc " +
|
" limit 0,5";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), null, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setNum(0);//0表示电池告警
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setStationid(rs.getString("stationid"));
|
adata.setStationname(rs.getString("stationname"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
String adataStr=adata.getStationname()
|
+"在"+ActionUtil.chageDateToString(adata.getAlm_start_time(), ActionUtil.time_yyyyMMddHHmmss)
|
+"出现了"+AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id())
|
+"告警";
|
adata.setNote(adataStr);
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}*/
|
public List serchTopBattTen(Object obj){
|
User_inf uinf=(User_inf) obj;
|
String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id " +
|
" from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
" where alm_cleared_type=0 " +
|
" and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid " +
|
" and db_alarm.tb_battalarm_data.BattGroupId in (select distinct db_battinf.tb_battinf.battgroupid " +
|
" from db_battinf.tb_battinf,db_user.tb_user_battgroup_baojigroup_battgroup,db_user.tb_user_battgroup_baojigroup_usr,db_user.tb_user_inf " +
|
" where db_user.tb_user_battgroup_baojigroup_battgroup.BattGroupId=db_battinf.tb_battinf.BattGroupId " +
|
" and db_user.tb_user_battgroup_baojigroup_usr.baoji_group_id=db_user.tb_user_battgroup_baojigroup_battgroup.baoji_group_id " +
|
" and db_user.tb_user_inf.uid=db_user.tb_user_battgroup_baojigroup_usr.uid " +
|
" and db_user.tb_user_inf.uid=? )" +
|
" and alm_id not in("+AlarmDaoFactory.Alarm_vol_Online+","+AlarmDaoFactory.Alarm_CapAlarm+","+AlarmDaoFactory.Alarm_CapChange+","+AlarmDaoFactory.Alarm_res_Conn+","+AlarmDaoFactory.ALM_TYPE_DisChargeMonVol_ID+")"+
|
" order by alm_start_time desc " +
|
" limit 0,5";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{uinf.getUId()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setNum(0);//0表示电池告警
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setStationid(rs.getString("stationid"));
|
adata.setStationname(rs.getString("stationname"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
String adataStr=adata.getStationname()
|
+"在"+ActionUtil.chageDateToString(adata.getAlm_start_time(), ActionUtil.time_yyyyMMddHHmmss)
|
+"出现了"+AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id())
|
+"告警";
|
adata.setNote(adataStr);
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//点击项目下方的滚动，查询该条告警的实时信息
|
public List serchBatt_alarm(Object obj){
|
Battalarm_data adata=(Battalarm_data) obj;
|
String sql="select distinct db_alarm.tb_battalarm_data.battgroupid,db_battinf.tb_battinf.stationname,db_battinf.tb_battinf.stationid,db_alarm.tb_battalarm_data.monnum,alm_id,alm_start_time,alm_signal_id,alm_cleared_type " +
|
" from db_alarm.tb_battalarm_data,db_battinf.tb_battinf " +
|
" where db_battinf.tb_battinf.battgroupid=db_alarm.tb_battalarm_data.battgroupid " +
|
" and db_alarm.tb_battalarm_data.battgroupid=? and db_alarm.tb_battalarm_data.monnum=? and alm_id=? and alm_start_time=? ";
|
List list=DAOHelper.executeQuery(sql, DBUtil.getConn(), new Object[]{adata.getBattGroupId(),adata.getMonNum(),adata.getAlm_id(),adata.getAlm_start_time()}, new CallBack() {
|
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
Battalarm_data adata=new Battalarm_data();
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setStationid(rs.getString("stationid"));
|
adata.setStationname(rs.getString("stationname"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_id(rs.getInt("alm_id"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_cleared_type(rs.getInt("alm_cleared_type"));
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
// 3.1电池告警实时查询<***********跨域专用**************>
|
public List serchByCondition_ky(Object obj) {
|
BattInf binf=(BattInf) obj;
|
String sql="";
|
String baseSql = "select distinct db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.num,alm_signal_id,db_alarm.tb_battalarm_data.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time "
|
+ " ,stationname,stationname5,battgroupname"
|
+ " from db_alarm.tb_battalarm_data,db_battinf.tb_battinf "
|
+ " where alm_cleared_type=0 ";
|
//用于电池组条件
|
String idSqlT=" and db_battinf.tb_battinf.battgroupid!=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";//全部
|
String idSqlF=" and db_battinf.tb_battinf.battgroupid=? and db_alarm.tb_battalarm_data.battgroupid=db_battinf.tb_battinf.battgroupid ";
|
if(binf.getBattGroupId()==0){
|
baseSql+=idSqlT;
|
}else{
|
baseSql+=idSqlF;
|
}
|
String endSql="order by db_alarm.tb_battalarm_data.BattGroupId,db_alarm.tb_battalarm_data.alm_start_time desc,db_alarm.tb_battalarm_data.monnum asc ";
|
sql=baseSql+endSql;
|
List list = DAOHelper.executeQuery(sql,DBUtil.getConn(),new Object[] {binf.getBattGroupId()}, new CallBack() {
|
public List getResults(ResultSet rs) {
|
List list = new ArrayList();
|
try {
|
while (rs.next()) {
|
String name="未知"; //存放告警名称
|
String type="未知"; //存放告警等级
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setNum(rs.getInt("num"));
|
adata.setBattGroupId(rs.getInt("battGroupId"));
|
adata.setAlm_signal_id(rs.getInt("alm_signal_id"));
|
adata.setMonNum(rs.getInt("monNum"));
|
adata.setAlm_value(rs.getFloat("alm_value"));
|
adata.setAlm_level(rs.getInt("alm_level"));
|
adata.setAlm_is_confirmed(rs.getInt("alm_is_confirmed"));
|
adata.setAlm_confirmed_time(rs.getTimestamp("alm_confirmed_time"));
|
adata.setAlm_start_time(rs.getTimestamp("alm_start_time"));
|
adata.setAlm_end_time(rs.getTimestamp("alm_end_time"));
|
|
adata.setStationname(rs.getString("stationName"));
|
adata.setBattGroupName(rs.getString("battGroupName"));
|
//battinf中的StationName8，StationName9存放告警名称和等级
|
name=AlarmDaoFactory.getAlarmName(adata.getAlm_signal_id());
|
adata.setAlarmname(name);
|
type=AlarmDaoFactory.getAlarmType(adata.getAlm_level());
|
adata.setAlarmtype(type);
|
list.add(adata);
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public static void main(String[] args) throws ParseException {
|
Battalarm_dataDAOImpl bimp = new Battalarm_dataDAOImpl();
|
|
Page p = new Page();
|
p.setPageCurr(1);
|
p.setPageSize(10);
|
/*条件所存放的位置前台：tb_batt_maint_inf
|
* 在线电压：usr_id
|
* 组端电压：fault_type_id
|
* 充电电流：fault_level
|
* 放电电流：record_uid
|
* 单体电压：maint_type_id
|
* 单体温度：maint_done
|
* 单体内阻：maint_close
|
* 连接条阻抗：master_id
|
* */
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Date date1 = sdf.parse("2000-01-01");
|
Date date2 = sdf.parse("2020-01-01");
|
Batt_maint_inf mainf=new Batt_maint_inf();
|
mainf.setUsr_id(0);
|
mainf.setFault_type_id(119002);
|
mainf.setFault_level(119003);
|
mainf.setRecord_uid(119004);
|
mainf.setMaint_type_id(119005);
|
mainf.setMaint_done(119006);
|
mainf.setMaint_close(119007);
|
mainf.setMaster_id(0);
|
|
|
mainf.setNum(1);
|
mainf.setBattGroupId(2);
|
mainf.setMaster_audit(3);
|
mainf.setAppoint_uid(4);
|
|
Battalarm_data adata = new Battalarm_data();
|
adata.setAlm_id(0); //下限告警未选中
|
adata.setAlm_signal_id(1); //上限告警选中
|
adata.setAlm_cleared_type(100);
|
adata.setAlm_start_time(ActionUtil.getSimpDate(date1));
|
adata.setAlm_start_time1(ActionUtil.getSimpDate(date2));
|
|
BattInf binf = new BattInf();
|
|
binf.setStationName("");
|
binf.setStationName1("");
|
binf.setBattGroupId(0);
|
|
|
User_inf uinf=new User_inf();
|
uinf.setUId(1002);
|
Batt_Maint_Dealarm bmd = new Batt_Maint_Dealarm();
|
bmd.setPage(p);
|
bmd.setAdata(adata);
|
bmd.setMainf(mainf);
|
bmd.setBinf(binf);
|
bmd.setUinf(uinf);
|
|
//List<Batt_Maint_Dealarm> list = bimp.serchByCondition(bmd);
|
/* List<Batt_Maint_Dealarm> list = bimp.serchByInfo(bmd);
|
for (Batt_Maint_Dealarm b : list) {
|
System.out.println(b);
|
}
|
System.out.println(list.size());*/
|
// List list=bimp.serchRealTime(uinf);
|
// System.out.println(list);
|
List<Integer> list = bimp.serchGood(uinf);
|
for (int num : list) {
|
System.out.println(num);
|
}
|
System.out.println(list.size());
|
}
|
}
|
