package com.whyc.service;
|
|
import com.whyc.dto.*;
|
import com.whyc.dto.paramter.AlarmPar;
|
import com.whyc.dto.paramter.AnalysisDto;
|
import com.whyc.dto.paramter.DevAlarmPar;
|
import com.whyc.dto.result.AlarmRes;
|
import com.whyc.dto.result.RealDateDTO;
|
import com.whyc.dto.result.ReportBattResultDTO;
|
import com.whyc.mapper.CallBack;
|
import com.whyc.pojo.*;
|
import com.whyc.util.ActionUtil;
|
import com.whyc.util.ThreadLocalUtil;
|
import org.apache.ibatis.annotations.Param;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import springfox.documentation.service.ApiListing;
|
|
import javax.swing.*;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.*;
|
|
@Service
|
public class SubTablePageInfoService {
|
@Autowired
|
private MybatisSqlExecuteService sqlExecuteService;
|
//2,7,33,55，72，78，109-SubTablePageInfoUtils.java;201行,435行
|
public List<PwrdevAlarmHistory> getList2(PwrdevAlarmHistory data){
|
String sql="select alarm.*,inf.StationName,inf.StationName1,inf.StationName2,inf.StationName3,inf.StationName5,inf.PowerDeviceName " +
|
" from db_pwrdev_alarm."+data.getRecordYear()+" alarm,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where alarm.PowerDeviceId = inf.PowerDeviceId ";
|
if(data.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+data.getStationName1()+"%'";
|
}
|
if(data.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+data.getStationName2()+"%'";
|
}
|
if(data.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+data.getStationName5()+"%'";
|
}
|
if(data.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+data.getStationName3()+"%'";
|
}
|
if(data.getAlmStartTime()!=null){
|
sql+=" and alarm.alm_start_time >='"+ ThreadLocalUtil.format(data.getAlmStartTime(),1)+"' ";
|
}
|
if(data.getAlmStartTime1()!=null){
|
sql+=" and alarm.alm_start_time <='"+ThreadLocalUtil.format(data.getAlmStartTime1(),1)+"' ";
|
}
|
if(data.getAlmSource()==0){
|
sql+=" and alm_source!=100 ";
|
}
|
if(data.getAlmSource()!=0){
|
sql+=" and alm_source="+data.getAlmSource()+" ";
|
}
|
if(data.getAlmTypes()!=null&&data.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<data.getAlmTypes().size();i++) {
|
sql+=data.getAlmTypes().get(i);
|
if(i!=(data.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and StationId in ( select distinct inf.StationId from " +
|
" (select StationId from db_battinf.tb_battinf union select StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+data.getUsrId()+" ) order by alarm.alm_start_time desc limit "+data.getLimitStart()+","+data.getLimitEnd()+" ";
|
List<PwrdevAlarmHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevAlarmHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevAlarmHistory ph=new PwrdevAlarmHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordId(rs.getLong("record_id"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setPowerDeviceName(rs.getString("PowerDeviceName"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmSource(rs.getInt("alm_source"));
|
ph.setAlmIndex(rs.getInt("alm_index"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmValue(rs.getFloat("alm_value"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setUsrId(rs.getInt("usr_Id"));
|
ph.setAlmTrigger(rs.getInt("alm_trigger"));
|
ph.setAlmSeverity(rs.getInt("alm_severity"));
|
ph.setStationName(rs.getString("StationName"));
|
ph.setStationName1(rs.getString("StationName1"));
|
ph.setStationName2(rs.getString("StationName2"));
|
ph.setStationName3(rs.getString("StationName3"));
|
ph.setStationName5(rs.getString("StationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//3,53,54,77,102,127,136-SubTablePageInfoUtils.java;72行 211行,445行
|
public List<PwrdevAlarmHistory> getListForTX(PwrdevAlarmHistory data){
|
String sql="select alarm.*,inf.StationName,inf.StationName1,inf.StationName2,inf.StationName3,inf.StationName5,inf.PowerDeviceName " +
|
" from db_pwrdev_alarm."+data.getRecordYear()+" alarm,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where alarm.PowerDeviceId = inf.PowerDeviceId ";
|
if(data.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+data.getStationName1()+"%'";
|
}
|
if(data.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+data.getStationName2()+"%'";
|
}
|
if(data.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+data.getStationName5()+"%'";
|
}
|
if(data.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+data.getStationName3()+"%'";
|
}
|
if(data.getAlmStartTime()!=null){
|
sql+=" and alarm.alm_start_time >='"+ThreadLocalUtil.format(data.getAlmStartTime(),1)+"' ";
|
}
|
if(data.getAlmStartTime1()!=null){
|
sql+=" and alarm.alm_start_time <='"+ThreadLocalUtil.format(data.getAlmStartTime1(),1)+"' ";
|
}
|
if(data.getAlmSource()==0){
|
sql+=" and alm_source!=100 ";
|
}
|
if(data.getAlmSource()!=0){
|
sql+=" and alm_source="+data.getAlmSource()+" ";
|
}
|
if(data.getAlmTypes()!=null&&data.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<data.getAlmTypes().size();i++) {
|
sql+=data.getAlmTypes().get(i);
|
if(i!=(data.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(data.getAlmLevels()!=null&&data.getAlmLevels().size()>0){
|
sql+=" and alm_level in ( ";
|
for (int i=0;i<data.getAlmLevels().size();i++) {
|
sql+=data.getAlmLevels().get(i);
|
if(i!=(data.getAlmLevels().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and StationId in ( select distinct inf.StationId from " +
|
" (select StationId from db_battinf.tb_battinf union select StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+data.getUsrId()+" ) order by alarm.alm_start_time desc limit "+data.getLimitStart()+","+data.getLimitEnd()+" ";
|
List<PwrdevAlarmHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevAlarmHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevAlarmHistory ph=new PwrdevAlarmHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordId(rs.getLong("record_id"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setPowerDeviceName(rs.getString("PowerDeviceName"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmSource(rs.getInt("alm_source"));
|
ph.setAlmIndex(rs.getInt("alm_index"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmValue(rs.getFloat("alm_value"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setUsrId(rs.getInt("usr_Id"));
|
ph.setAlmTrigger(rs.getInt("alm_trigger"));
|
ph.setAlmSeverity(rs.getInt("alm_severity"));
|
ph.setStationName(rs.getString("StationName"));
|
ph.setStationName1(rs.getString("StationName1"));
|
ph.setStationName2(rs.getString("StationName2"));
|
ph.setStationName3(rs.getString("StationName3"));
|
ph.setStationName5(rs.getString("StationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//4,11,18,98,103-SubTablePageInfoUtils.java;445行,72行
|
public int getCount(UserLog userLog){
|
String sql=" select count(*) num from db_user."+userLog.getRecordYear()+" l,db_user.tb_user_inf u" +
|
" where l.uId = u.uId" +
|
" and uOprateDay between '"+ThreadLocalUtil.format(userLog.getOperationTime(),1)+"' and '"+ThreadLocalUtil.format(userLog.getOperationTime2(),1)+"' ";
|
if(userLog.getUId()!=null){
|
sql+=" and l.uId ="+userLog.getUId()+" ";
|
}
|
//条件查询-非全部
|
if(userLog.getEventType()!=null){
|
if(userLog.getEventType()==1){
|
if(userLog.getOperationType()==1||userLog.getOperationType()==2
|
||userLog.getOperationType()==20||userLog.getOperationType()==21){
|
if(userLog.getOperationType()==1){
|
sql+=" and uoprateType in ("+userLog.getOperationType()+",35) ";
|
}else{
|
sql+=" and uoprateType ="+userLog.getOperationType()+" ";
|
}
|
}
|
if(userLog.getOperationType()==31||userLog.getOperationType()==32
|
||userLog.getOperationType()==33||userLog.getOperationType()==34){
|
sql+=" and uoprateType ="+userLog.getOperationType()+" ";
|
if(userLog.getOperationType()==31){
|
sql+=" or uOprateMsg regexp '登录PC系统失败' ";
|
}else if(userLog.getOperationType()==32){
|
sql+=" or uOprateMsg regexp '配置参数修改|导航配置' ";
|
}else if(userLog.getOperationType()==33){
|
sql+=" r uOprateMsg regexp '密码' and uOprateMsg not regexp '更新密码复杂度规则' ";
|
}else{
|
sql+=" or uOprateMsg regexp 'LoginAction!login|imeout' ";
|
}
|
}
|
if(userLog.getOperationType()==3||userLog.getOperationType()==4||userLog.getOperationType()==5){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp " +
|
" '添加新用户|的用户信息|权限组|审计数据记录容量 " +
|
" |执行了User模块|执行了PermitGroup' ";
|
}
|
}
|
if(userLog.getEventType()==2){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp '单体|电池组|组端|设备|Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D' " +
|
" and uOprateMsg not regexp '电源|Pwrdev' ";
|
}
|
if(userLog.getEventType()==3){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp 'PowerInf|Pwr' ";
|
}
|
if(userLog.getEventType()==4){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg not regexp '添加新用户|的用户信息|权限组|审计数据记录容量|配置参数修改|导航配置|密码|单体|电池组|组端|设备|电源| " +
|
" |执行了User模块|执行了PermitGroup| " +
|
" |Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D| " +
|
" |PowerInf|Pwr' ";
|
}
|
}
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("num"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//5-PwrdevDataHistoryService.java;81行
|
public List<PwrdevDataHistory> getData(String tableName){
|
String sql=" select * from db_pwrdev_data_history."+tableName+" ";
|
List<PwrdevDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevDataHistory ph=new PwrdevDataHistory();
|
ph.setNum(rs.getInt("num"));
|
ph.setPowerDeviceId(rs.getInt("PowerDeviceId"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setAcAcIn1VolA(rs.getFloat("AC_acIn1_volA"));
|
ph.setAcAcIn1VolB(rs.getFloat("AC_acIn1_volB"));
|
ph.setAcAcIn1VolC(rs.getFloat("AC_acIn1_volC"));
|
ph.setAcAcIn1CurrA(rs.getFloat("AC_acIn1_currA"));
|
ph.setAcAcIn1CurrB(rs.getFloat("AC_acIn1_currB"));
|
ph.setAcAcIn1CurrC(rs.getFloat("AC_acIn1_currC"));
|
ph.setAcAcIn2VolA(rs.getFloat("AC_acIn2_volA"));
|
ph.setAcAcIn2VolB(rs.getFloat("AC_acIn2_volB"));
|
ph.setAcAcIn2VolC(rs.getFloat("AC_acIn2_volC"));
|
ph.setAcAcIn2CurrA(rs.getFloat("AC_acIn2_currA"));
|
ph.setAcAcIn2CurrB(rs.getFloat("AC_acIn2_currB"));
|
ph.setAcAcIn2CurrC(rs.getFloat("AC_acIn2_currC"));
|
ph.setAcTemprature(rs.getFloat("AC_temprature"));
|
ph.setAcAcVolHLimit(rs.getFloat("AC_acVolH_Limit"));
|
ph.setAcAcVolLLimit(rs.getFloat("AC_acVolL_Limit"));
|
ph.setAcdcAcIn1VolA(rs.getFloat("ACDC_acIn1_volA"));
|
ph.setAcdcAcIn1VolB(rs.getFloat("ACDC_acIn1_volB"));
|
ph.setAcdcAcIn1VolC(rs.getFloat("ACDC_acIn1_volC"));
|
ph.setAcdcAcIn2VolA(rs.getFloat("ACDC_acIn2_volA"));
|
ph.setAcdcAcIn2VolB(rs.getFloat("ACDC_acIn2_volB"));
|
ph.setAcdcAcIn2VolC(rs.getFloat("ACDC_acIn2_volC"));
|
ph.setAcdcAcOutVolA(rs.getFloat("ACDC_acOut_volA"));
|
ph.setAcdcAcOutVolB(rs.getFloat("ACDC_acOut_volB"));
|
ph.setAcdcAcOutVolC(rs.getFloat("ACDC_acOut_volC"));
|
ph.setAcdcAcOutCurrA(rs.getFloat("ACDC_acOut_currA"));
|
ph.setAcdcAcOutCurrB(rs.getFloat("ACDC_acOut_currB"));
|
ph.setAcdcAcOutCurrC(rs.getFloat("ACDC_acOut_currC"));
|
ph.setAcdcDcOutVol(rs.getFloat("ACDC_dcOut_vol"));
|
ph.setAcdcLoaderCurr(rs.getFloat("ACDC_loader_curr"));
|
ph.setAcdcBattgroup1Vol(rs.getFloat("ACDC_battgroup1_vol"));
|
ph.setAcdcBattgroup1Curr(rs.getFloat("ACDC_battgroup1_curr"));
|
ph.setAcdcBattgroup2Vol(rs.getFloat("ACDC_battgroup2_vol"));
|
ph.setAcdcBattgroup2Curr(rs.getFloat("ACDC_battgroup2_curr"));
|
ph.setAcdcAcdcmTemp(rs.getFloat("ACDC_acdcm_temp"));
|
ph.setAcdcM1OutCurr(rs.getFloat("ACDC_m1_outCurr"));
|
ph.setAcdcM2OutCurr(rs.getFloat("ACDC_m2_outCurr"));
|
ph.setAcdcM3OutCurr(rs.getFloat("ACDC_m3_outCurr"));
|
ph.setAcdcM4OutCurr(rs.getFloat("ACDC_m4_outCurr"));
|
ph.setAcdcM5OutCurr(rs.getFloat("ACDC_m5_outCurr"));
|
ph.setAcdcM6OutCurr(rs.getFloat("ACDC_m6_outCurr"));
|
ph.setAcdcM7OutCurr(rs.getFloat("ACDC_m7_outCurr"));
|
ph.setAcdcM8OutCurr(rs.getFloat("ACDC_m8_outCurr"));
|
ph.setAcdcM9OutCurr(rs.getFloat("ACDC_m9_outCurr"));
|
ph.setAcdcM10OutCurr(rs.getFloat("ACDC_m10_outCurr"));
|
ph.setAcdcM11OutCurr(rs.getFloat("ACDC_m11_outCurr"));
|
ph.setAcdcM12OutCurr(rs.getFloat("ACDC_m12_outCurr"));
|
ph.setAcdcM13OutCurr(rs.getFloat("ACDC_m13_outCurr"));
|
ph.setAcdcM14OutCurr(rs.getFloat("ACDC_m14_outCurr"));
|
ph.setAcdcM15OutCurr(rs.getFloat("ACDC_m15_outCurr"));
|
ph.setAcdcM16OutCurr(rs.getFloat("ACDC_m16_outCurr"));
|
ph.setAcdcChargLimitCurr(rs.getFloat("ACDC_chargLimitCurr"));
|
ph.setAcdcJunChargeVol(rs.getFloat("ACDC_junChargeVol"));
|
ph.setAcdcFloatChargeVol(rs.getFloat("ACDC_floatChargeVol"));
|
ph.setAcdcAcVolHLimit(rs.getFloat("ACDC_acVolH_Limit"));
|
ph.setAcdcAcVolLLimit(rs.getFloat("ACDC_acVolL_Limit"));
|
ph.setAcdcDcOutVolHLimit(rs.getFloat("ACDC_dcOutVolH_Limit"));
|
ph.setAcdcDcOutVolLLimit(rs.getFloat("ACDC_dcOutVolL_Limit"));
|
ph.setDcDcIn1Vol(rs.getFloat("DC_dcIn1_vol"));
|
ph.setDcDcIn2Vol(rs.getFloat("DC_dcIn2_vol"));
|
ph.setDcDcOut1Vol(rs.getFloat("DC_dcOut1_vol"));
|
ph.setDcDcOut1Curr(rs.getFloat("DC_dcOut1_curr"));
|
ph.setDcDcOut2Vol(rs.getFloat("DC_dcOut2_vol"));
|
ph.setDcDcOut2Curr(rs.getFloat("DC_dcOut2_curr"));
|
ph.setDcTemprature(rs.getFloat("DC_temprature"));
|
ph.setDcDcVolHLimit(rs.getFloat("DC_dcVolH_Limit"));
|
ph.setDcDcVolLLimit(rs.getFloat("DC_dcVolL_Limit"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//6-PwrDevDataHistoryGWService.java;45行
|
public List<PwrDevDataHistoryGW> getListByParam(Long powerDeviceId, String tableMonth, Date subStartTime, Date subEndTime){
|
String sql=" select * from db_pwrdev_data_history_gw.tb_pwrdev_historydata_gw_"+powerDeviceId+"_"+tableMonth+" d " +
|
" where d.record_time >='"+ThreadLocalUtil.format(subStartTime,1)+"' and d.record_time <='"+ThreadLocalUtil.format(subEndTime,1)+"' ";
|
List<PwrDevDataHistoryGW> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrDevDataHistoryGW> list=new ArrayList<>();
|
while (rs.next()){
|
PwrDevDataHistoryGW ph=new PwrDevDataHistoryGW();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setAcin1_vola(rs.getFloat("acin1_vola"));
|
ph.setAcin1_volb(rs.getFloat("acin1_volb"));
|
ph.setAcin1_volc(rs.getFloat("acin1_volc"));
|
ph.setAcin2_vola(rs.getFloat("acin2_vola"));
|
ph.setAcin2_volb(rs.getFloat("acin2_volb"));
|
ph.setAcin2_volc(rs.getFloat("acin2_volc"));
|
ph.setAcin1_curra(rs.getFloat("acin1_curra"));
|
ph.setAcin1_currb(rs.getFloat("acin1_currb"));
|
ph.setAcin1_currc(rs.getFloat("acin1_currc"));
|
ph.setAcin2_curra(rs.getFloat("acin2_curra"));
|
ph.setAcin2_currb(rs.getFloat("acin2_currb"));
|
ph.setAcin2_currc(rs.getFloat("acin2_currc"));
|
ph.setDcout_vol(rs.getFloat("dcout_vol"));
|
ph.setDcout_curr(rs.getFloat("dcout_curr"));
|
ph.setBattgroup1_vol(rs.getFloat("battgroup1_vol"));
|
ph.setBattgroup1_curr(rs.getFloat("battgroup1_curr"));
|
ph.setBattgroup2_vol(rs.getFloat("battgroup2_vol"));
|
ph.setBattgroup2_curr(rs.getFloat("battgroup2_curr"));
|
ph.setM1_out_vol(rs.getFloat("m1_out_vol"));
|
ph.setM2_out_vol(rs.getFloat("m2_out_vol"));
|
ph.setM3_out_vol(rs.getFloat("m3_out_vol"));
|
ph.setM4_out_vol(rs.getFloat("m4_out_vol"));
|
ph.setM5_out_vol(rs.getFloat("m5_out_vol"));
|
ph.setM6_out_vol(rs.getFloat("m6_out_vol"));
|
ph.setM7_out_vol(rs.getFloat("m7_out_vol"));
|
ph.setM8_out_vol(rs.getFloat("m8_out_vol"));
|
ph.setM9_out_vol(rs.getFloat("m9_out_vol"));
|
ph.setM10_out_vol(rs.getFloat("m10_out_vol"));
|
ph.setM11_out_vol(rs.getFloat("m11_out_vol"));
|
ph.setM12_out_vol(rs.getFloat("m12_out_vol"));
|
ph.setM13_out_vol(rs.getFloat("m13_out_vol"));
|
ph.setM14_out_vol(rs.getFloat("m14_out_vol"));
|
ph.setM15_out_vol(rs.getFloat("m15_out_vol"));
|
ph.setM16_out_vol(rs.getFloat("m16_out_vol"));
|
ph.setM1_outcurr(rs.getFloat("m1_outcurr"));
|
ph.setM2_outcurr(rs.getFloat("m2_outcurr"));
|
ph.setM3_outcurr(rs.getFloat("m3_outcurr"));
|
ph.setM4_outcurr(rs.getFloat("m4_outcurr"));
|
ph.setM5_outcurr(rs.getFloat("m5_outcurr"));
|
ph.setM6_outcurr(rs.getFloat("m6_outcurr"));
|
ph.setM7_outcurr(rs.getFloat("m7_outcurr"));
|
ph.setM8_outcurr(rs.getFloat("m8_outcurr"));
|
ph.setM9_outcurr(rs.getFloat("m9_outcurr"));
|
ph.setM10_outcurr(rs.getFloat("m10_outcurr"));
|
ph.setM11_outcurr(rs.getFloat("m11_outcurr"));
|
ph.setM12_outcurr(rs.getFloat("m12_outcurr"));
|
ph.setM13_outcurr(rs.getFloat("m13_outcurr"));
|
ph.setM14_outcurr(rs.getFloat("m14_outcurr"));
|
ph.setM15_outcurr(rs.getFloat("m15_outcurr"));
|
ph.setM16_outcurr(rs.getFloat("m16_outcurr"));
|
ph.setM_temper1(rs.getFloat("m_temper1"));
|
ph.setM_temper2(rs.getFloat("m_temper2"));
|
ph.setM_humidity1(rs.getFloat("m_humidity1"));
|
ph.setM_humidity2(rs.getFloat("m_humidity2"));
|
ph.setUseracin1_vola(rs.getFloat("useracin1_vola"));
|
ph.setUseracin1_volb(rs.getFloat("useracin1_volb"));
|
ph.setUseracin1_volc(rs.getFloat("useracin1_volc"));
|
ph.setUseracin1_curra(rs.getFloat("useracin1_curra"));
|
ph.setUseracin1_currb(rs.getFloat("useracin1_currb"));
|
ph.setUseracin1_currc(rs.getFloat("useracin1_currc"));
|
ph.setUseracin2_vola(rs.getFloat("useracin2_vola"));
|
ph.setUseracin2_volb(rs.getFloat("useracin2_volb"));
|
ph.setUseracin2_volc(rs.getFloat("useracin2_volc"));
|
ph.setUseracin2_curra(rs.getFloat("useracin2_curra"));
|
ph.setUseracin2_currb(rs.getFloat("useracin2_currb"));
|
ph.setUseracin2_currc(rs.getFloat("useracin2_currc"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//8,133-StandardFeatureCurveService.java;86行
|
public List<StandardFeatureCurve> getList(String va, String c){
|
String sql=" select * from `web_site`.`tb_standard_feature_curve_"+va+"_"+c+"`";
|
List<StandardFeatureCurve> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<StandardFeatureCurve> list=new ArrayList<>();
|
while (rs.next()){
|
StandardFeatureCurve ph=new StandardFeatureCurve();
|
ph.setTitleNameUnion(rs.getString("id"));
|
ph.setTime(rs.getString("time"));
|
ph.setValue(rs.getDouble("value"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//9,12,20,24,49，110,118-SubTablePageInfoUtils.java;225行,459行 225行
|
public List<AlarmRes> getListForInterface(InterfaceParam param){
|
String sql="select history.BattGroupId,history.num,alm_id,alm_signal_id,history.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time,alm_severity" +
|
",stationId,stationname,stationname1,stationname2,stationname3,stationname5,battgroupname " +
|
" from db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.battgroupid=db_battinf.tb_battinf.battgroupid ";
|
if(param.getBattgroupIds()!=null){
|
sql+=" and history.BattGroupId in ( ";
|
for (int i=0;i<param.getBattgroupIds().size();i++) {
|
sql+=param.getBattgroupIds().get(i);
|
if(i!=(param.getBattgroupIds().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(param.getStartTime()!=null){
|
sql+=" and alm_start_time>='"+ ThreadLocalUtil.format(param.getStartTime(),1)+"' ";
|
}
|
if(param.getEndTime()!=null){
|
sql+=" and alm_start_time<='"+ ThreadLocalUtil.format(param.getEndTime(),1)+"' ";
|
}
|
|
sql+=" order by history.battgroupid asc,history.alm_start_time desc,history.monnum asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
|
List<AlarmRes> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<AlarmRes> list=new ArrayList<>();
|
int id=0;
|
int i=0;
|
List dataList=new ArrayList();
|
AlarmRes ph=new AlarmRes();
|
while (rs.next()){
|
int battGroupId=rs.getInt("BattGroupId");
|
if(battGroupId!=id){
|
ph.setAdatas(dataList);
|
dataList=new ArrayList();
|
}
|
BattalarmData data=new BattalarmData();
|
data.setNum(rs.getLong("num"));
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setAlmId(rs.getInt("alm_id"));
|
data.setAlmSignalId(rs.getInt("alm_signal_id"));
|
data.setMonNum(rs.getInt("monnum"));
|
data.setAlmValue(rs.getFloat("alm_value"));
|
data.setAlmLevel(rs.getInt("alm_level"));
|
data.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
data.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setAlmSeverity(rs.getInt("alm_severity"));
|
dataList.add(data);
|
if(battGroupId!=id){
|
if(i!=0){
|
list.add(ph);
|
}
|
id=battGroupId;
|
ph=new AlarmRes();
|
ph.setBattGroupId(battGroupId);
|
ph.setStationId(rs.getString("stationId"));
|
ph.setStationName(rs.getString("stationname"));
|
ph.setStationName1(rs.getString("stationname1"));
|
ph.setStationName2(rs.getString("stationname2"));
|
ph.setStationName3(rs.getString("stationname3"));
|
ph.setStationName5(rs.getString("stationname5"));
|
ph.setBattGroupName(rs.getString("battgroupname"));
|
}
|
if(rs.isLast()){
|
ph.setAdatas(dataList);
|
list.add(ph);
|
}
|
i++;
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
//10,25,31，81，90-SubTablePageInfoUtils.java;98行
|
public int getCount(PwrdevAlarmHistory param){
|
String sql="select count(distinct alarm.num) as number from db_pwrdev_alarm."+param.getRecordYear()+" alarm,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where alarm.PowerDeviceId = inf.PowerDeviceId ";
|
if(param.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+param.getStationName1()+"%'";
|
}
|
if(param.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+param.getStationName2()+"%'";
|
}
|
if(param.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+param.getStationName5()+"%'";
|
}
|
if(param.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+param.getStationName3()+"%'";
|
}
|
if(param.getAlmStartTime()!=null){
|
sql+=" and alarm.alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' ";
|
}
|
if(param.getAlmStartTime1()!=null){
|
sql+=" and alarm.alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
}
|
if(param.getAlmSource()==0){
|
sql+=" and alm_source!=100 ";
|
}
|
if(param.getAlmSource()!=0){
|
sql+=" and alm_source="+param.getAlmSource()+" ";
|
}
|
if(param.getAlmTypes()!=null&¶m.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<param.getAlmTypes().size();i++) {
|
sql+=param.getAlmTypes().get(i);
|
if(i!=(param.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and StationId in ( select distinct inf.StationId from" +
|
" (select StationId from db_battinf.tb_battinf union select StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+param.getUsrId()+" )";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//13,14,17,36，94-SubTablePageInfoUtils.java;88行
|
public int getCount(DevAlarmPar param){
|
String sql="SELECT count(distinct history.num) as number FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id=db_battinf.tb_battinf.FbsDeviceId ";
|
if(param.getStationName1()!=null){
|
if(param.getStationName1().equals("")){
|
sql+=" and stationName1!='"+param.getStationName1()+"' ";
|
}else{
|
sql+=" and stationName1='"+param.getStationName1()+"' ";
|
}
|
}
|
if(param.getStationName()!=null){
|
if(param.getStationName().equals("")){
|
sql+=" and stationName!='"+param.getStationName()+"' ";
|
}else{
|
sql+=" and stationName='"+param.getStationName()+"' ";
|
}
|
}
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
sql+=" and history.dev_id in(select distinct db_battinf.tb_battinf.FbsDeviceId " +
|
" 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="+param.getUId()+")";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
//13,14,17,36，94-SubTablePageInfoUtils.java;88行
|
public int getCount_stationId(DevAlarmPar param){
|
String sql="SELECT count(distinct history.num) as number FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id=db_battinf.tb_battinf.FbsDeviceId ";
|
/*if(param.getStationName1()!=null){
|
if(param.getStationName1().equals("")){
|
sql+=" and stationName1!='"+param.getStationName1()+"' ";
|
}else{
|
sql+=" and stationName1='"+param.getStationName1()+"' ";
|
}
|
}
|
if(param.getStationName()!=null){
|
if(param.getStationName().equals("")){
|
sql+=" and stationName!='"+param.getStationName()+"' ";
|
}else{
|
sql+=" and stationName='"+param.getStationName()+"' ";
|
}
|
}*/
|
if(param.getStationId()!=null){
|
if(param.getStationId().equals("")){
|
sql+=" and stationId!='"+param.getStationId()+"' ";
|
}else{
|
sql+=" and stationId='"+param.getStationId()+"' ";
|
}
|
}
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
sql+=" and history.dev_id in(select distinct db_battinf.tb_battinf.FbsDeviceId " +
|
" 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="+param.getUId()+")";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//15,41-PwrdevTimeOutAlarmService.java;28行 47行
|
public List getList(int uId,String alarmParamTableName){
|
String sql="SELECT t.num num, t.PowerDeviceId PowerDeviceId,alarm_typeId,alarm_index,timeout_tag,read_datetime,system_datetime,resume_datetime,alarm_name,StationName " +
|
" FROM db_pwrdev_alarm.tb_pwrdev_time_out_alarm t ,db_pwrdev_alarm."+alarmParamTableName+" p2,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" WHERE t.alarm_typeId = p2.alarm_id and t.PowerDeviceId= inf.PowerDeviceId " +
|
" AND t.PowerDeviceId in ( SELECT DISTINCT devInf.PowerDeviceId " +
|
" FROM db_pwrdev_inf.tb_pwrdev_inf devInf, " +
|
" db_user.tb_user_battgroup_baojigroup_battgroup group1, " +
|
" db_user.tb_user_battgroup_baojigroup_usr groupUser " +
|
" WHERE " +
|
" devInf.StationId = group1.StationId " +
|
" AND group1.baoji_group_id = groupUser.baoji_group_id " +
|
" AND groupUser.uId ="+uId+")";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevTimeOutAlarm> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevTimeOutAlarm ph=new PwrdevTimeOutAlarm();
|
ph.setNum(rs.getLong("num"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setAlarmTypeid(rs.getInt("alarm_typeId"));
|
ph.setAlarmIndex(rs.getInt("alarm_index"));
|
ph.setTimeoutTag(rs.getInt("timeout_tag"));
|
ph.setReadDatetime(rs.getTimestamp("read_datetime"));
|
ph.setSystemDatetime(rs.getTimestamp("system_datetime"));
|
ph.setResumeDatetime(rs.getTimestamp("resume_datetime"));
|
ph.setAlarmName(rs.getString("alarm_name"));
|
ph.setStationName(rs.getString("StationName"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
public List getList2_stationId(DevAlarmPar param){
|
String sql=" SELECT history.num,dev_id,dev_ip,alm_type,alm_level,alm_start_time,alm_end_time,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_severity, " +
|
" db_battinf.tb_battinf.stationName,stationName1,stationName2,stationName3,stationName5,db_battinf.tb_battinf.stationName4 as dev_name " +
|
" FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id=db_battinf.tb_battinf.FbsDeviceId ";
|
/*if(param.getStationName1()!=null){
|
if(param.getStationName1().equals("")){
|
sql+=" and stationName1!='"+param.getStationName1()+"' ";
|
}else{
|
sql+=" and stationName1='"+param.getStationName1()+"' ";
|
}
|
}
|
if(param.getStationName()!=null){
|
if(param.getStationName().equals("")){
|
sql+=" and stationName!='"+param.getStationName()+"' ";
|
}else{
|
sql+=" and stationName='"+param.getStationName()+"' ";
|
}
|
}*/
|
if(param.getStationId()!=null){
|
if(param.getStationId().equals("")){
|
sql+=" and stationId!='"+param.getStationId()+"' ";
|
}else{
|
sql+=" and stationId='"+param.getStationId()+"' ";
|
}
|
}
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
sql+=" and history.dev_id in(select distinct db_battinf.tb_battinf.FbsDeviceId " +
|
" 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="+param.getUId()+") " +
|
" ORDER BY alm_start_time desc ,dev_name asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<DevalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
DevalarmDataHistory ph=new DevalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setDevId(rs.getInt("dev_id"));
|
ph.setDevIp(rs.getString("dev_ip"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setDevName(rs.getString("dev_name"));
|
ph.setStationName(rs.getString("stationName"));
|
ph.setStationName1(rs.getString("stationName1"));
|
ph.setStationName2(rs.getString("stationName2"));
|
ph.setStationName3(rs.getString("stationName3"));
|
ph.setStationName5(rs.getString("stationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//16,19,30，61,105，111,123-SubTablePageInfoUtils.java;188行 422行
|
public List getList2(DevAlarmPar param){
|
String sql=" SELECT history.num,dev_id,dev_ip,alm_type,alm_level,alm_start_time,alm_end_time,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,alm_severity, " +
|
" db_battinf.tb_battinf.stationName,stationName1,stationName2,stationName3,stationName5,db_battinf.tb_battinf.stationName4 as dev_name " +
|
" FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id=db_battinf.tb_battinf.FbsDeviceId ";
|
if(param.getStationName1()!=null){
|
if(param.getStationName1().equals("")){
|
sql+=" and stationName1!='"+param.getStationName1()+"' ";
|
}else{
|
sql+=" and stationName1='"+param.getStationName1()+"' ";
|
}
|
}
|
if(param.getStationName()!=null){
|
if(param.getStationName().equals("")){
|
sql+=" and stationName!='"+param.getStationName()+"' ";
|
}else{
|
sql+=" and stationName='"+param.getStationName()+"' ";
|
}
|
}
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
sql+=" and history.dev_id in(select distinct db_battinf.tb_battinf.FbsDeviceId " +
|
" 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="+param.getUId()+") " +
|
" ORDER BY alm_start_time desc ,dev_name asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<DevalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
DevalarmDataHistory ph=new DevalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setDevId(rs.getInt("dev_id"));
|
ph.setDevIp(rs.getString("dev_ip"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setDevName(rs.getString("dev_name"));
|
ph.setStationName(rs.getString("stationName"));
|
ph.setStationName1(rs.getString("stationName1"));
|
ph.setStationName2(rs.getString("stationName2"));
|
ph.setStationName3(rs.getString("stationName3"));
|
ph.setStationName5(rs.getString("stationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//21,74，84，86，93,116,135-SubTablePageInfoUtils.java;164行，398行
|
public List<UserLog> getList2(UserLog userLog){
|
String sql=" select l.*,u.uName from db_user."+userLog.getRecordYear()+" l,db_user.tb_user_inf u " +
|
" where l.uId = u.uId " +
|
" and uOprateDay between '"+ThreadLocalUtil.format(userLog.getOperationTime(),1)+"' and '"+ThreadLocalUtil.format(userLog.getOperationTime2(),1)+"' ";
|
if(userLog.getUId()!=null){
|
sql+=" and l.uId ="+userLog.getUId()+" ";
|
}
|
//条件查询-非全部
|
if(userLog.getEventType()!=null){
|
if(userLog.getEventType()==1){
|
if(userLog.getOperationType()==1||userLog.getOperationType()==2
|
||userLog.getOperationType()==20||userLog.getOperationType()==21){
|
if(userLog.getOperationType()==1){
|
sql+=" and uoprateType in ("+userLog.getOperationType()+",35) ";
|
}else{
|
sql+=" and uoprateType ="+userLog.getOperationType()+" ";
|
}
|
}
|
if(userLog.getOperationType()==31||userLog.getOperationType()==32
|
||userLog.getOperationType()==33||userLog.getOperationType()==34){
|
sql+=" and uoprateType ="+userLog.getOperationType()+" ";
|
if(userLog.getOperationType()==31){
|
sql+=" or uOprateMsg regexp '登录PC系统失败' ";
|
}else if(userLog.getOperationType()==32){
|
sql+=" or uOprateMsg regexp '配置参数修改|导航配置' ";
|
}else if(userLog.getOperationType()==33){
|
sql+=" r uOprateMsg regexp '密码' and uOprateMsg not regexp '更新密码复杂度规则' ";
|
}else{
|
sql+=" or uOprateMsg regexp 'LoginAction!login|imeout' ";
|
}
|
}
|
if(userLog.getOperationType()==3||userLog.getOperationType()==4||userLog.getOperationType()==5){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp " +
|
" '添加新用户|的用户信息|权限组|审计数据记录容量 " +
|
" |执行了User模块|执行了PermitGroup' ";
|
}
|
}
|
if(userLog.getEventType()==2){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp '单体|电池组|组端|设备|Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D' " +
|
" and uOprateMsg not regexp '电源|Pwrdev' ";
|
}
|
if(userLog.getEventType()==3){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg regexp 'PowerInf|Pwr' ";
|
}
|
if(userLog.getEventType()==4){
|
sql+=" and uoprateType= #{operationType} " +
|
" and uOprateMsg not regexp '添加新用户|的用户信息|权限组|审计数据记录容量|配置参数修改|导航配置|密码|单体|电池组|组端|设备|电源| " +
|
" |执行了User模块|执行了PermitGroup| " +
|
" |Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D| " +
|
" |PowerInf|Pwr' ";
|
}
|
}
|
sql+=" ORDER BY num DESC limit "+userLog.getLimitStart()+","+userLog.getLimitEnd()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList();
|
try {
|
while (rs.next()){
|
UserLog us=new UserLog();
|
us.setNum(rs.getInt("num"));
|
us.setUId(rs.getInt("uId"));
|
us.setOperationType(rs.getInt("uOprateType"));
|
us.setOperationTime(rs.getTimestamp("uOprateDay"));
|
us.setTerminalIp(rs.getString("uTerminalIp"));
|
us.setOperationMsg(rs.getString("uOprateMsg"));
|
//us.setOperationMsgEnUs(rs.getString("uOprateMsgEnUs"));
|
us.setOperationDetail(rs.getString("operation_detail"));
|
//us.setOperationDetailEnUs(rs.getString("operation_detail_enUs"));
|
us.setUName(rs.getString("uName"));
|
//us.setRecordYear(rs.getString("recordYear"));
|
us.setReadFlag(rs.getInt("read_flag"));
|
list.add(us);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//23,60,75，87，88,100,235-SubTablePageInfoUtils.java;469行，235行
|
public List<DevalarmDataHistory> getListForInterface_dev(InterfaceParam param){
|
String sql=" SELECT history.num,dev_id,dev_ip,alm_type,alm_level,alm_start_time,alm_end_time,alm_is_confirmed,alm_confirmed_time,alm_cleared_type, " +
|
" db_battinf.tb_battinf.stationName,db_battinf.tb_battinf.stationName1,db_battinf.tb_battinf.stationName2,db_battinf.tb_battinf.stationName3," +
|
" db_battinf.tb_battinf.stationName5,db_battinf.tb_battinf.FbsDeviceName as dev_name " +
|
" FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id = db_battinf.tb_battinf.FbsDeviceId ";
|
if(param.getDeviceIds()!=null&¶m.getDeviceIds().size()>0){
|
sql+=" and history.dev_id in( ";
|
for (int i=0;i<param.getDeviceIds().size();i++) {
|
sql+=param.getDeviceIds().get(i);
|
if(i!=(param.getDeviceIds().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(param.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getStartTime(),1)+"' ";
|
}
|
if(param.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(param.getEndTime(),1)+"' ";
|
}
|
sql+=" ORDER BY alm_start_time desc ,dev_name asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<DevalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
DevalarmDataHistory ph=new DevalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setDevId(rs.getInt("dev_id"));
|
ph.setDevIp(rs.getString("dev_ip"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setDevName(rs.getString("dev_name"));
|
ph.setStationName(rs.getString("stationName"));
|
ph.setStationName1(rs.getString("stationName1"));
|
ph.setStationName2(rs.getString("stationName2"));
|
ph.setStationName3(rs.getString("stationName3"));
|
ph.setStationName5(rs.getString("stationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//26，82-HistoryDataArchivingService.java;107行 103行
|
public void insertBatch4BattAlarmDataHistoryByYear(String year, List<BattalarmDataHistory> temp){
|
String sql="insert into db_alarm.`tb_battalarm_data_history_"+year+"` " +
|
" (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,alm_severity) " +
|
" values";
|
if(temp!=null&&temp.size()>0){
|
for (int i=0;i<temp.size();i++) {
|
BattalarmDataHistory bh=temp.get(i);
|
sql+="("+bh.getBattGroupId()+","+bh.getMonNum()+","+bh.getRecordId()+","+bh.getAlmId()+","+bh.getAlmSignalId()+","+bh.getAlmLevel()+
|
",'"+ThreadLocalUtil.format(bh.getAlmStartTime(),1)+"','"+ThreadLocalUtil.format(bh.getAlmEndTime(),1)+"',"+bh.getAlmValue()+","+bh.getAlmIsConfirmed()+"," +
|
"'"+ThreadLocalUtil.format(bh.getAlmConfirmedTime(),1)+"',"+bh.getAlmClearedType()+","+bh.getUsrId()+","+bh.getAlmSeverity()+")";
|
if(i!=(temp.size()-1)){
|
sql+=",";
|
}
|
}
|
}
|
sqlExecuteService.execute(sql);
|
}
|
|
//28-BattmonTestcapService.java;39行
|
public void create_standard_curve(String tableName){
|
String sql="CREATE TABLE IF NOT EXISTS db_battinf."+tableName+" ( " +
|
" num int(11) NOT NULL AUTO_INCREMENT, " +
|
" monvolstd float NOT NULL DEFAULT '0', " +
|
" moncapstd float NOT NULL DEFAULT '0', " +
|
" battproducer varchar(64) NOT NULL DEFAULT '', " +
|
" battmodel varchar(64) NOT NULL DEFAULT '', " +
|
" test_cap double NOT NULL DEFAULT '0', " +
|
" mon_vol double NOT NULL DEFAULT '0', " +
|
" note varchar(64) NOT NULL DEFAULT '', " +
|
" PRIMARY KEY (num), " +
|
" KEY test_cap_index (test_cap) " +
|
" )";
|
sqlExecuteService.execute(sql);
|
}
|
|
//29,65，83，112,128-SubTablePageInfoUtils.java;104行
|
public int getCountForTX(PwrdevAlarmHistory param){
|
String sql="select count(distinct alarm.num) as number from db_pwrdev_alarm."+param.getRecordYear()+" alarm,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where alarm.PowerDeviceId = inf.PowerDeviceId ";
|
if(param.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+param.getStationName1()+"%'";
|
}
|
if(param.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+param.getStationName2()+"%'";
|
}
|
if(param.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+param.getStationName5()+"%'";
|
}
|
if(param.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+param.getStationName3()+"%'";
|
}
|
if(param.getAlmStartTime()!=null){
|
sql+=" and alarm.alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' ";
|
}
|
if(param.getAlmStartTime1()!=null){
|
sql+=" and alarm.alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' ";
|
}
|
if(param.getAlmSource()==0){
|
sql+=" and alm_source!=100 ";
|
}
|
if(param.getAlmSource()!=0){
|
sql+=" and alm_source="+param.getAlmSource()+" ";
|
}
|
if(param.getAlmTypes()!=null&¶m.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<param.getAlmTypes().size();i++) {
|
sql+=param.getAlmTypes().get(i);
|
if(i!=(param.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(param.getAlmLevels()!=null&¶m.getAlmLevels().size()>0){
|
sql+=" and alm_level in ( ";
|
for (int i=0;i<param.getAlmLevels().size();i++) {
|
sql+=param.getAlmLevels().get(i);
|
if(i!=(param.getAlmLevels().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and StationId in ( select distinct inf.StationId from" +
|
" (select StationId from db_battinf.tb_battinf union select StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+param.getUsrId()+" )";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//32,47,50,124-PwrdevAlarmService.java;189行 158行,85行
|
public List<PwrdevAlarmParam> searchAll(String tableName){
|
String sql="select * from db_pwrdev_alarm."+tableName;
|
List<PwrdevAlarmParam> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevAlarmParam> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevAlarmParam ph=new PwrdevAlarmParam();
|
ph.setAlarmId(rs.getLong("alarm_id"));
|
ph.setAlarmLimith(rs.getFloat("alarm_limitH"));
|
ph.setAlarmLimitl(rs.getFloat("alarm_limitL"));
|
ph.setAlarmLimiten(rs.getInt("alarm_limitEn"));
|
ph.setUseralarmEn(rs.getInt("UserAlarm_EN"));
|
ph.setAlarmLevel(rs.getInt("alarm_level"));
|
ph.setAlarmDevtype(rs.getInt("alarm_devtype"));
|
ph.setAlarmName(rs.getString("alarm_name"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//34，51,64,96-SubTablePageInfoUtils.java;121行
|
public int getCountForInterface(InterfaceParam param){
|
String sql="SELECT count(*) as number FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.dev_id=db_battinf.tb_battinf.FbsDeviceId ";
|
if(param.getDeviceIds()!=null&¶m.getDeviceIds().size()>0){
|
sql+=" and history.dev_id in( ";
|
for (int i=0;i<param.getDeviceIds().size();i++) {
|
sql+=param.getDeviceIds().get(i);
|
if(i!=(param.getDeviceIds().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(param.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getStartTime(),1)+"' ";
|
}
|
if(param.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(param.getEndTime(),1)+"' ";
|
}
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//35,38,57,67,101，71,117 -SubTablePageInfoUtils.java;176行 410行
|
public List<AlarmRes> getList2(AlarmPar param){
|
String sql="select history.BattGroupId,history.num,alm_signal_id,history.monnum,alm_value,alm_level,alm_is_confirmed,alm_confirmed_time,alm_start_time,alm_end_time " +
|
" ,alm_severity,stationname,StationName1,StationName2,StationName3,StationName5,battgroupname " +
|
" from db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.battgroupid=db_battinf.tb_battinf.battgroupid " +
|
" and alm_id in("+param.getAlmIdOne()+","+param.getAlmIdTwo()+", "+param.getAlmIdThree()+ ", "+param.getAlmIdFour()+ ", "+param.getAlmIdFive()+ ", "+param.getAlmIdSix()+ ", "+param.getAlmIdSeven()+ ", "+param.getAlmIdEight()+ ") " +
|
" and alm_signal_id%2 in("+param.getAlmSignalIdOne()+","+param.getAlmSignalIdTwo()+") " +
|
" and alm_level in( "+param.getAlmLevelOne()+ ", "+param.getAlmLevelTwo()+ ", "+param.getAlmLevelThree()+ ", "+param.getAlmLevelFour()+ ") " +
|
" and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' " +
|
" and db_battinf.tb_battinf.stationname1 like '%"+param.getStationname1()+"%' " +
|
" and db_battinf.tb_battinf.stationname like '%"+param.getStationname()+"%' ";
|
if(param.getBattGroupId()==0){
|
sql+=" and db_battinf.tb_battinf.battgroupid!="+param.getBattGroupId()+" ";
|
}else{
|
sql+=" and db_battinf.tb_battinf.battgroupid="+param.getBattGroupId()+" ";
|
}
|
sql+=" and history.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="+param.getUId()+" " +
|
" )";
|
sql+=" order by history.alm_start_time desc,history.BattGroupId asc,history.monnum asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
|
List<AlarmRes> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<AlarmRes> list=new ArrayList<>();
|
int id=0;
|
int i=0;
|
List dataList=new ArrayList();
|
AlarmRes ph=new AlarmRes();
|
while (rs.next()){
|
int battGroupId=rs.getInt("BattGroupId");
|
if(battGroupId!=id){
|
ph.setAdataHs(dataList);
|
dataList=new ArrayList();
|
}
|
BattalarmDataHistory data=new BattalarmDataHistory();
|
data.setNum(rs.getLong("num"));
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setAlmSignalId(rs.getInt("alm_signal_id"));
|
data.setMonNum(rs.getInt("monnum"));
|
data.setAlmValue(rs.getFloat("alm_value"));
|
data.setAlmLevel(rs.getInt("alm_level"));
|
data.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
data.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setAlmSeverity(rs.getInt("alm_severity"));
|
dataList.add(data);
|
if(battGroupId!=id){
|
if(i!=0){
|
list.add(ph);
|
}
|
id=battGroupId;
|
ph=new AlarmRes();
|
ph.setBattGroupId(battGroupId);
|
ph.setStationName(rs.getString("stationname"));
|
ph.setStationName1(rs.getString("stationname1"));
|
ph.setStationName2(rs.getString("stationname2"));
|
ph.setStationName3(rs.getString("stationname3"));
|
ph.setStationName5(rs.getString("stationname5"));
|
ph.setBattGroupName(rs.getString("battgroupname"));
|
}
|
if(rs.isLast()){
|
ph.setAdataHs(dataList);
|
list.add(ph);
|
}
|
i++;
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//40-BattmonTestcapService.java;50行
|
public List<BattmonTestcap> getTestCapList(BattmonStandardcurve curve){
|
String sql="select monvolstd,moncapstd,battproducer,battmodel,test_cap,ROUND(avg(mon_vol),3) as mon_vol from " +
|
" `db_battinf`.`tb_battmon_testcap_"+curve.getMonVolStd()+"_"+curve.getMonCapStd()+"_"+curve.getNote()+"` " +
|
" where monvolstd= "+curve.getMonVolStd()+" " +
|
" and moncapstd="+curve.getMonCapStd()+" " +
|
" and battproducer='"+curve.getBattproducer()+"' " +
|
" and battmodel='"+curve.getBattmodel()+"' " +
|
" group by test_cap " +
|
" order by num";
|
List<BattmonTestcap> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattmonTestcap> list=new ArrayList<>();
|
while (rs.next()){
|
BattmonTestcap ph=new BattmonTestcap();
|
ph.setMonvolstd(rs.getFloat("monvolstd"));
|
ph.setMoncapstd(rs.getFloat("moncapstd"));
|
ph.setBattproducer(rs.getString("battproducer"));
|
ph.setBattmodel(rs.getString("battmodel"));
|
ph.setTestCap(rs.getDouble("test_cap"));
|
ph.setMonVol(rs.getDouble("mon_vol"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//42,129-HistoryDataArchivingService.java;72行 76行
|
public void insertBatch4PowerAlarmHistoryByYear(String year, List<PwrdevAlarmHistory> temp){
|
String sql="insert into db_pwrdev_alarm.`tb_pwrdev_alarm_history_"+year+"` " +
|
" (record_id,PowerDeviceId,alm_type,alm_level,alm_source,alm_index,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id,alm_severity,alm_trigger) " +
|
" values";
|
if(temp!=null&&temp.size()>0){
|
for (int i=0;i<temp.size();i++) {
|
PwrdevAlarmHistory bh=temp.get(i);
|
sql+="("+bh.getRecordId()+","+bh.getPowerDeviceId()+","+bh.getAlmType()+","+bh.getAlmLevel()+","+bh.getAlmSource()+","+bh.getAlmIndex()+
|
",'"+ThreadLocalUtil.format(bh.getAlmStartTime(),1)+"','"+ThreadLocalUtil.format(bh.getAlmEndTime(),1)+"',"+bh.getAlmValue()+","+bh.getAlmIsConfirmed()+"," +
|
"'"+ThreadLocalUtil.format(bh.getAlmConfirmedTime(),1)+"',"+bh.getAlmClearedType()+","+bh.getUsrId()+","+bh.getAlmSeverity()+","+bh.getAlmTrigger()+")";
|
if(i!=(temp.size()-1)){
|
sql+=",";
|
}
|
}
|
}
|
sqlExecuteService.execute(sql);
|
}
|
|
//43-PwrdevDataHistoryService.java;69行
|
public List<PwrdevDataHistory> getGtStartTime(String tableName, Date startTime){
|
String sql="select * from db_pwrdev_data_history."+tableName+" where record_time>='"+ ThreadLocalUtil.format(startTime,1)+"' ";
|
List<PwrdevDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevDataHistory ph=new PwrdevDataHistory();
|
ph.setNum(rs.getInt("num"));
|
ph.setPowerDeviceId(rs.getInt("PowerDeviceId"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setAcAcIn1VolA(rs.getFloat("AC_acIn1_volA"));
|
ph.setAcAcIn1VolB(rs.getFloat("AC_acIn1_volB"));
|
ph.setAcAcIn1VolC(rs.getFloat("AC_acIn1_volC"));
|
ph.setAcAcIn1CurrA(rs.getFloat("AC_acIn1_currA"));
|
ph.setAcAcIn1CurrB(rs.getFloat("AC_acIn1_currB"));
|
ph.setAcAcIn1CurrC(rs.getFloat("AC_acIn1_currC"));
|
ph.setAcAcIn2VolA(rs.getFloat("AC_acIn2_volA"));
|
ph.setAcAcIn2VolB(rs.getFloat("AC_acIn2_volB"));
|
ph.setAcAcIn2VolC(rs.getFloat("AC_acIn2_volC"));
|
ph.setAcAcIn2CurrA(rs.getFloat("AC_acIn2_currA"));
|
ph.setAcAcIn2CurrB(rs.getFloat("AC_acIn2_currB"));
|
ph.setAcAcIn2CurrC(rs.getFloat("AC_acIn2_currC"));
|
ph.setAcTemprature(rs.getFloat("AC_temprature"));
|
ph.setAcAcVolHLimit(rs.getFloat("AC_acVolH_Limit"));
|
ph.setAcAcVolLLimit(rs.getFloat("AC_acVolL_Limit"));
|
ph.setAcdcAcIn1VolA(rs.getFloat("ACDC_acIn1_volA"));
|
ph.setAcdcAcIn1VolB(rs.getFloat("ACDC_acIn1_volB"));
|
ph.setAcdcAcIn1VolC(rs.getFloat("ACDC_acIn1_volC"));
|
ph.setAcdcAcIn2VolA(rs.getFloat("ACDC_acIn2_volA"));
|
ph.setAcdcAcIn2VolB(rs.getFloat("ACDC_acIn2_volB"));
|
ph.setAcdcAcIn2VolC(rs.getFloat("ACDC_acIn2_volC"));
|
ph.setAcdcAcOutVolA(rs.getFloat("ACDC_acOut_volA"));
|
ph.setAcdcAcOutVolB(rs.getFloat("ACDC_acOut_volB"));
|
ph.setAcdcAcOutVolC(rs.getFloat("ACDC_acOut_volC"));
|
ph.setAcdcAcOutCurrA(rs.getFloat("ACDC_acOut_currA"));
|
ph.setAcdcAcOutCurrB(rs.getFloat("ACDC_acOut_currB"));
|
ph.setAcdcAcOutCurrC(rs.getFloat("ACDC_acOut_currC"));
|
ph.setAcdcDcOutVol(rs.getFloat("ACDC_dcOut_vol"));
|
ph.setAcdcLoaderCurr(rs.getFloat("ACDC_loader_curr"));
|
ph.setAcdcBattgroup1Vol(rs.getFloat("ACDC_battgroup1_vol"));
|
ph.setAcdcBattgroup1Curr(rs.getFloat("ACDC_battgroup1_curr"));
|
ph.setAcdcBattgroup2Vol(rs.getFloat("ACDC_battgroup2_vol"));
|
ph.setAcdcBattgroup2Curr(rs.getFloat("ACDC_battgroup2_curr"));
|
ph.setAcdcAcdcmTemp(rs.getFloat("ACDC_acdcm_temp"));
|
ph.setAcdcM1OutCurr(rs.getFloat("ACDC_m1_outCurr"));
|
ph.setAcdcM2OutCurr(rs.getFloat("ACDC_m2_outCurr"));
|
ph.setAcdcM3OutCurr(rs.getFloat("ACDC_m3_outCurr"));
|
ph.setAcdcM4OutCurr(rs.getFloat("ACDC_m4_outCurr"));
|
ph.setAcdcM5OutCurr(rs.getFloat("ACDC_m5_outCurr"));
|
ph.setAcdcM6OutCurr(rs.getFloat("ACDC_m6_outCurr"));
|
ph.setAcdcM7OutCurr(rs.getFloat("ACDC_m7_outCurr"));
|
ph.setAcdcM8OutCurr(rs.getFloat("ACDC_m8_outCurr"));
|
ph.setAcdcM9OutCurr(rs.getFloat("ACDC_m9_outCurr"));
|
ph.setAcdcM10OutCurr(rs.getFloat("ACDC_m10_outCurr"));
|
ph.setAcdcM11OutCurr(rs.getFloat("ACDC_m11_outCurr"));
|
ph.setAcdcM12OutCurr(rs.getFloat("ACDC_m12_outCurr"));
|
ph.setAcdcM13OutCurr(rs.getFloat("ACDC_m13_outCurr"));
|
ph.setAcdcM14OutCurr(rs.getFloat("ACDC_m14_outCurr"));
|
ph.setAcdcM15OutCurr(rs.getFloat("ACDC_m15_outCurr"));
|
ph.setAcdcM16OutCurr(rs.getFloat("ACDC_m16_outCurr"));
|
ph.setAcdcChargLimitCurr(rs.getFloat("ACDC_chargLimitCurr"));
|
ph.setAcdcJunChargeVol(rs.getFloat("ACDC_junChargeVol"));
|
ph.setAcdcFloatChargeVol(rs.getFloat("ACDC_floatChargeVol"));
|
ph.setAcdcAcVolHLimit(rs.getFloat("ACDC_acVolH_Limit"));
|
ph.setAcdcAcVolLLimit(rs.getFloat("ACDC_acVolL_Limit"));
|
ph.setAcdcDcOutVolHLimit(rs.getFloat("ACDC_dcOutVolH_Limit"));
|
ph.setAcdcDcOutVolLLimit(rs.getFloat("ACDC_dcOutVolL_Limit"));
|
ph.setDcDcIn1Vol(rs.getFloat("DC_dcIn1_vol"));
|
ph.setDcDcIn2Vol(rs.getFloat("DC_dcIn2_vol"));
|
ph.setDcDcOut1Vol(rs.getFloat("DC_dcOut1_vol"));
|
ph.setDcDcOut1Curr(rs.getFloat("DC_dcOut1_curr"));
|
ph.setDcDcOut2Vol(rs.getFloat("DC_dcOut2_vol"));
|
ph.setDcDcOut2Curr(rs.getFloat("DC_dcOut2_curr"));
|
ph.setDcTemprature(rs.getFloat("DC_temprature"));
|
ph.setDcDcVolHLimit(rs.getFloat("DC_dcVolH_Limit"));
|
ph.setDcDcVolLLimit(rs.getFloat("DC_dcVolL_Limit"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//44,70,104，108,122,134-SubTablePageInfoUtils.java;121行,115行
|
public int getCountForInterface_batt(InterfaceParam param){
|
String sql="SELECT count(*) as number FROM db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.battgroupid=db_battinf.tb_battinf.battgroupid ";
|
if(param.getDeviceIds()!=null&¶m.getDeviceIds().size()>0){
|
sql+=" and history.BattGroupId in( ";
|
for (int i=0;i<param.getBattgroupIds().size();i++) {
|
sql+=param.getBattgroupIds().get(i);
|
if(i!=(param.getBattgroupIds().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
if(param.getStartTime()!=null){
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getStartTime(),1)+"' ";
|
}
|
if(param.getEndTime()!=null){
|
sql+=" and alm_start_time <='"+ThreadLocalUtil.format(param.getEndTime(),1)+"' ";
|
}
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//48-BattRealdataService.java;59行
|
public List<RealDateDTO> serchByCondition(BattRealdata realdata){
|
String sql="select * from db_batt_history.tb_batt_realdata_"+realdata.getTableName()+" " +
|
" where recrod_time >= '"+ThreadLocalUtil.format(realdata.getRecrodTime(),1)+"' " +
|
" and recrod_time <= '"+ThreadLocalUtil.format(realdata.getRecrodTime1(),1)+"' " +
|
" and mon_num ="+realdata.getMonNum()+" " +
|
" order by recrod_time asc";
|
List<RealDateDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<RealDateDTO> list=new ArrayList<>();
|
while (rs.next()){
|
RealDateDTO ph=new RealDateDTO();
|
ph.setRecrodTime(rs.getTimestamp("recrod_time"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupCurr(rs.getFloat("group_curr"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
ph.setMonRes(rs.getFloat("mon_res"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//52，107-HistoryDataArchivingService.java;165行，169行
|
public void insertBatch4UserLogByYear(String year, List<UserLog> userLogs){
|
String sql=" insert into `tb_user_log_"+year+"`(uId,uOprateType,uOprateDay,uTerminalIp,uOprateMsg,operation_detail,read_flag)" +
|
" values" ;
|
if(userLogs!=null&&userLogs.size()>0){
|
for (int i=0;i<userLogs.size();i++) {
|
UserLog bh=userLogs.get(i);
|
sql+="("+bh.getUId()+","+bh.getOperationType()+",'"+ThreadLocalUtil.format(bh.getOperationTime(),1)+"','"+bh.getTerminalIp()+"','"+bh.getOperationMsg()+"','"+bh.getOperationDetail()+
|
"',"+bh.getReadFlag()+")";
|
if(i!=(userLogs.size()-1)){
|
sql+=",";
|
}
|
}
|
}
|
sqlExecuteService.execute(sql);
|
}
|
|
//56-BattmonTestcapService.java;44行
|
public int add(BattmonTestcap btcp){
|
String sql="insert into db_battinf."+btcp.getTableName()+" (monvolstd,moncapstd,battproducer,battmodel,test_cap,mon_vol,note) " +
|
" SELECT DISTINCT " +
|
" "+btcp.getMonvolstd()+","+btcp.getMoncapstd()+",'"+btcp.getBattproducer()+"','"+btcp.getBattmodel()+"',ROUND(test_cap,4),ROUND(mon_vol,3),'"+btcp.getNote()+"' from " +
|
" db_batt_testdata.tb_batttestdata_"+btcp.getBattgroupId()+" " +
|
" where test_record_count="+btcp.getTestRecordCount()+" " +
|
" and mon_num="+btcp.getMonNum()+" ";
|
int flag=sqlExecuteService.executeUpdate(sql,new Object[]{});
|
return flag;
|
}
|
|
//58-HistoryDataArchivingService.java;65行
|
public void createTable4PowerAlarmHistoryByYear(String year){
|
String sql="CREATE TABLE db_pwrdev_alarm.`tb_pwrdev_alarm_history_"+year+"` ( " +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT, " +
|
" `record_id` bigint(20) NOT NULL DEFAULT '0', " +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0', " +
|
" `alm_type` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_level` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_source` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_index` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_start_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_end_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_value` float NOT NULL DEFAULT '0', " +
|
" `alm_is_confirmed` tinyint(1) NOT NULL DEFAULT '0', " +
|
" `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_cleared_type` int(11) NOT NULL DEFAULT '0', " +
|
" `usr_Id` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_trigger` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0', " +
|
" PRIMARY KEY (`num`), " +
|
" KEY `index_record_id` (`record_id`), " +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`), " +
|
" KEY `index_alm_type` (`alm_type`), " +
|
" KEY `index_alm_start_time` (`alm_start_time`), " +
|
" KEY `index_alm_cleared_type` (`alm_cleared_type`) " +
|
" ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
|
sqlExecuteService.execute(sql);
|
}
|
|
//63，106-PwrdevTimeAdjAlarmService.java;41行，32行
|
public List<PwrdevTimeAdjAlarm> getList_adj(int uId, String alarmParamTableName){
|
String sql=" SELECT adj.num num ,adj.PowerDeviceId PowerDeviceId,alarm_typeId,detect_datetime,record_datetime,alarm_name,StationName " +
|
" FROM db_pwrdev_alarm.tb_pwrdev_time_adj_alarm adj ,db_pwrdev_alarm."+alarmParamTableName+" p2,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" WHERE adj.alarm_typeId = p2.alarm_id and adj.PowerDeviceId= inf.PowerDeviceId " +
|
" AND adj.PowerDeviceId in ( SELECT DISTINCT devInf.PowerDeviceId " +
|
" FROM db_pwrdev_inf.tb_pwrdev_inf devInf, " +
|
" db_user.tb_user_battgroup_baojigroup_battgroup group1, " +
|
" db_user.tb_user_battgroup_baojigroup_usr groupUser " +
|
" WHERE " +
|
" devInf.StationId = group1.StationId " +
|
" AND group1.baoji_group_id = groupUser.baoji_group_id " +
|
" AND groupUser.uId ="+uId+")";
|
List<PwrdevTimeAdjAlarm> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevTimeAdjAlarm> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevTimeAdjAlarm ph=new PwrdevTimeAdjAlarm();
|
ph.setNum(rs.getLong("num"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setAlarmTypeid(rs.getInt("alarm_typeId"));
|
ph.setDetectDatetime(rs.getTimestamp("detect_datetime"));
|
ph.setRecordDatetime(rs.getTimestamp("record_datetime"));
|
ph.setAlarmName(rs.getString("alarm_name"));
|
ph.setStationName(rs.getString("StationName"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//66，68，80,99,132-SubTablePageInfoUtils.java;80行
|
public int getCount(AlarmPar param){
|
String sql="select count(distinct history.num) as number " +
|
" from db_alarm."+param.getRecordYear()+" history,db_battinf.tb_battinf " +
|
" where history.battgroupid=db_battinf.tb_battinf.battgroupid " +
|
" and alm_id in("+param.getAlmIdOne()+","+param.getAlmIdTwo()+", "+param.getAlmIdThree()+ ", "+param.getAlmIdFour()+ ", "+param.getAlmIdFive()+ ", "+param.getAlmIdSix()+ ", "+param.getAlmIdSeven()+ ", "+param.getAlmIdEight()+ ") " +
|
" and alm_signal_id%2 in("+param.getAlmSignalIdOne()+","+param.getAlmSignalIdTwo()+") " +
|
" and alm_level in( "+param.getAlmLevelOne()+ ", "+param.getAlmLevelTwo()+ ", "+param.getAlmLevelThree()+ ", "+param.getAlmLevelFour()+ ") " +
|
" and alm_start_time>='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmStartTime1(),1)+"' " +
|
" and db_battinf.tb_battinf.stationname1 like '%"+param.getStationname1()+"%' " +
|
" and db_battinf.tb_battinf.stationname like '%"+param.getStationname()+"%' ";
|
if(param.getBattGroupId()==0){
|
sql+=" and db_battinf.tb_battinf.battgroupid!="+param.getBattGroupId()+" ";
|
}else{
|
sql+=" and db_battinf.tb_battinf.battgroupid="+param.getBattGroupId()+" ";
|
}
|
sql+=" and history.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="+param.getUId()+" " +
|
" )";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
|
//69-SubTablePageInfoUtils.java;500行
|
public List<BattalarmDataHistory> getLevelSubList_batt(int uId, String tableName){
|
String sql=" SELECT distinct history.num,history.alm_level " +
|
" from db_alarm."+tableName+" history, " +
|
" db_battinf.tb_battinf " +
|
" where history.battgroupid = db_battinf.tb_battinf.battgroupid " +
|
" and alm_level in(1,2,3,4) " +
|
" and alm_id in (119001, 119002, 119003, 119004, 119005, 119006, 119007, 119012) " +
|
" and history.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 = "+uId+" " +
|
" )";
|
List<BattalarmDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
BattalarmDataHistory ph=new BattalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
|
//91-HistoryDataArchivingService.java;158行
|
public void createTable4UserLogByYear( String year){
|
String sql=" CREATE TABLE `tb_user_log_"+year+"` ( " +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT, " +
|
" `uId` int(11) NOT NULL DEFAULT '0', " +
|
" `uOprateType` int(11) NOT NULL DEFAULT '0', " +
|
" `uOprateDay` datetime NOT NULL DEFAULT '1980-01-01 00:00:00', " +
|
" `uTerminalIp` varchar(20) NOT NULL DEFAULT ' ', " +
|
" `uOprateMsg` text, " +
|
" `operation_detail` text COMMENT '操作的具体数据', " +
|
" `read_flag` tinyint(1) DEFAULT '1' COMMENT '是否已阅读,此功能针对异常信息', " +
|
" PRIMARY KEY (`num`), " +
|
" KEY `user_id` (`uId`) " +
|
" ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
|
sqlExecuteService.execute(sql);
|
}
|
|
//0-BattRealdataController.java;50行
|
public List<BattRealdata> serchDate100( String table){
|
String sql="select distinct num, recrod_time, group_vol, online_vol, group_curr" +
|
" from db_batt_history.tb_batt_realdata_"+table+" " +
|
" where mon_num=1 " +
|
"order by recrod_time desc limit 0, 100";
|
List<BattRealdata> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattRealdata> list=new ArrayList<>();
|
while (rs.next()){
|
BattRealdata ph=new BattRealdata();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecrodTime(rs.getTimestamp("recrod_time"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupCurr(rs.getFloat("group_curr"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//92-PwrdevDataHistoryService.java;74行
|
public List<PwrdevDataHistory> getLtEndTime( String tableName, Date endTime){
|
String sql=" select * from db_pwrdev_data_history."+tableName+" where record_time<='"+ ThreadLocalUtil.format(endTime,1)+"' ";
|
List<PwrdevDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevDataHistory ph=new PwrdevDataHistory();
|
ph.setNum(rs.getInt("num"));
|
ph.setPowerDeviceId(rs.getInt("PowerDeviceId"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setAcAcIn1VolA(rs.getFloat("AC_acIn1_volA"));
|
ph.setAcAcIn1VolB(rs.getFloat("AC_acIn1_volB"));
|
ph.setAcAcIn1VolC(rs.getFloat("AC_acIn1_volC"));
|
ph.setAcAcIn1CurrA(rs.getFloat("AC_acIn1_currA"));
|
ph.setAcAcIn1CurrB(rs.getFloat("AC_acIn1_currB"));
|
ph.setAcAcIn1CurrC(rs.getFloat("AC_acIn1_currC"));
|
ph.setAcAcIn2VolA(rs.getFloat("AC_acIn2_volA"));
|
ph.setAcAcIn2VolB(rs.getFloat("AC_acIn2_volB"));
|
ph.setAcAcIn2VolC(rs.getFloat("AC_acIn2_volC"));
|
ph.setAcAcIn2CurrA(rs.getFloat("AC_acIn2_currA"));
|
ph.setAcAcIn2CurrB(rs.getFloat("AC_acIn2_currB"));
|
ph.setAcAcIn2CurrC(rs.getFloat("AC_acIn2_currC"));
|
ph.setAcTemprature(rs.getFloat("AC_temprature"));
|
ph.setAcAcVolHLimit(rs.getFloat("AC_acVolH_Limit"));
|
ph.setAcAcVolLLimit(rs.getFloat("AC_acVolL_Limit"));
|
ph.setAcdcAcIn1VolA(rs.getFloat("ACDC_acIn1_volA"));
|
ph.setAcdcAcIn1VolB(rs.getFloat("ACDC_acIn1_volB"));
|
ph.setAcdcAcIn1VolC(rs.getFloat("ACDC_acIn1_volC"));
|
ph.setAcdcAcIn2VolA(rs.getFloat("ACDC_acIn2_volA"));
|
ph.setAcdcAcIn2VolB(rs.getFloat("ACDC_acIn2_volB"));
|
ph.setAcdcAcIn2VolC(rs.getFloat("ACDC_acIn2_volC"));
|
ph.setAcdcAcOutVolA(rs.getFloat("ACDC_acOut_volA"));
|
ph.setAcdcAcOutVolB(rs.getFloat("ACDC_acOut_volB"));
|
ph.setAcdcAcOutVolC(rs.getFloat("ACDC_acOut_volC"));
|
ph.setAcdcAcOutCurrA(rs.getFloat("ACDC_acOut_currA"));
|
ph.setAcdcAcOutCurrB(rs.getFloat("ACDC_acOut_currB"));
|
ph.setAcdcAcOutCurrC(rs.getFloat("ACDC_acOut_currC"));
|
ph.setAcdcDcOutVol(rs.getFloat("ACDC_dcOut_vol"));
|
ph.setAcdcLoaderCurr(rs.getFloat("ACDC_loader_curr"));
|
ph.setAcdcBattgroup1Vol(rs.getFloat("ACDC_battgroup1_vol"));
|
ph.setAcdcBattgroup1Curr(rs.getFloat("ACDC_battgroup1_curr"));
|
ph.setAcdcBattgroup2Vol(rs.getFloat("ACDC_battgroup2_vol"));
|
ph.setAcdcBattgroup2Curr(rs.getFloat("ACDC_battgroup2_curr"));
|
ph.setAcdcAcdcmTemp(rs.getFloat("ACDC_acdcm_temp"));
|
ph.setAcdcM1OutCurr(rs.getFloat("ACDC_m1_outCurr"));
|
ph.setAcdcM2OutCurr(rs.getFloat("ACDC_m2_outCurr"));
|
ph.setAcdcM3OutCurr(rs.getFloat("ACDC_m3_outCurr"));
|
ph.setAcdcM4OutCurr(rs.getFloat("ACDC_m4_outCurr"));
|
ph.setAcdcM5OutCurr(rs.getFloat("ACDC_m5_outCurr"));
|
ph.setAcdcM6OutCurr(rs.getFloat("ACDC_m6_outCurr"));
|
ph.setAcdcM7OutCurr(rs.getFloat("ACDC_m7_outCurr"));
|
ph.setAcdcM8OutCurr(rs.getFloat("ACDC_m8_outCurr"));
|
ph.setAcdcM9OutCurr(rs.getFloat("ACDC_m9_outCurr"));
|
ph.setAcdcM10OutCurr(rs.getFloat("ACDC_m10_outCurr"));
|
ph.setAcdcM11OutCurr(rs.getFloat("ACDC_m11_outCurr"));
|
ph.setAcdcM12OutCurr(rs.getFloat("ACDC_m12_outCurr"));
|
ph.setAcdcM13OutCurr(rs.getFloat("ACDC_m13_outCurr"));
|
ph.setAcdcM14OutCurr(rs.getFloat("ACDC_m14_outCurr"));
|
ph.setAcdcM15OutCurr(rs.getFloat("ACDC_m15_outCurr"));
|
ph.setAcdcM16OutCurr(rs.getFloat("ACDC_m16_outCurr"));
|
ph.setAcdcChargLimitCurr(rs.getFloat("ACDC_chargLimitCurr"));
|
ph.setAcdcJunChargeVol(rs.getFloat("ACDC_junChargeVol"));
|
ph.setAcdcFloatChargeVol(rs.getFloat("ACDC_floatChargeVol"));
|
ph.setAcdcAcVolHLimit(rs.getFloat("ACDC_acVolH_Limit"));
|
ph.setAcdcAcVolLLimit(rs.getFloat("ACDC_acVolL_Limit"));
|
ph.setAcdcDcOutVolHLimit(rs.getFloat("ACDC_dcOutVolH_Limit"));
|
ph.setAcdcDcOutVolLLimit(rs.getFloat("ACDC_dcOutVolL_Limit"));
|
ph.setDcDcIn1Vol(rs.getFloat("DC_dcIn1_vol"));
|
ph.setDcDcIn2Vol(rs.getFloat("DC_dcIn2_vol"));
|
ph.setDcDcOut1Vol(rs.getFloat("DC_dcOut1_vol"));
|
ph.setDcDcOut1Curr(rs.getFloat("DC_dcOut1_curr"));
|
ph.setDcDcOut2Vol(rs.getFloat("DC_dcOut2_vol"));
|
ph.setDcDcOut2Curr(rs.getFloat("DC_dcOut2_curr"));
|
ph.setDcTemprature(rs.getFloat("DC_temprature"));
|
ph.setDcDcVolHLimit(rs.getFloat("DC_dcVolH_Limit"));
|
ph.setDcDcVolLLimit(rs.getFloat("DC_dcVolL_Limit"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
|
}
|
|
//95-HistoryDataArchivingService.java;96行
|
public void createTable4BattAlarmDataHistoryByYear(String year){
|
String sql=" CREATE TABLE db_alarm.`tb_battalarm_data_history_"+year+"` ( " +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT, " +
|
" `BattGroupId` int(11) NOT NULL DEFAULT '0', " +
|
" `MonNum` int(11) NOT NULL DEFAULT '0', " +
|
" `Record_Id` bigint(20) NOT NULL DEFAULT '0', " +
|
" `alm_id` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_signal_id` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_level` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_start_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_end_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_value` float NOT NULL DEFAULT '0', " +
|
" `alm_is_confirmed` tinyint(1) NOT NULL DEFAULT '0', " +
|
" `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_cleared_type` int(11) NOT NULL DEFAULT '0', " +
|
" `usr_Id` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0', " +
|
" PRIMARY KEY (`num`), " +
|
" KEY `index_battgroup_id` (`BattGroupId`), " +
|
" KEY `index_Record_Id` (`Record_Id`), " +
|
" KEY `index_alm_id` (`alm_id`), " +
|
" KEY `index_alm_start_time` (`alm_start_time`), " +
|
" KEY `index_alm_cleared_type` (`alm_cleared_type`), " +
|
" KEY `index_alm_signal_id` (`alm_signal_id`), " +
|
" KEY `index_alm_level` (`alm_level`) " +
|
" ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
|
sqlExecuteService.execute(sql);
|
}
|
|
//114-HistoryDataArchivingService.java;138行
|
public void insertBatch4DevAlarmDataHistoryByYear(String year, List<DevalarmDataHistory> temp){
|
String sql=" insert into db_alarm.`tb_devalarm_data_history_"+year+"` " +
|
" (record_id,dev_id,dev_ip,alm_type,alm_level" +
|
" ,alm_start_time,alm_end_time,alm_value,alm_is_confirmed" +
|
" ,alm_confirmed_time,alm_cleared_type,usr_Id,alm_severity) " +
|
" values ";
|
if(temp!=null&&temp.size()>0){
|
for (int i=0;i<temp.size();i++) {
|
DevalarmDataHistory bh=temp.get(i);
|
sql+="("+bh.getRecordId()+","+bh.getDevId()+",'"+bh.getDevIp()+"',"+bh.getAlmType()+","+bh.getAlmLevel()+
|
",'"+ThreadLocalUtil.format(bh.getAlmStartTime(),1)+"','"+ThreadLocalUtil.format(bh.getAlmEndTime(),1)+"',"+bh.getAlmValue()+","+bh.getAlmIsConfirmed()+"," +
|
"'"+ThreadLocalUtil.format(bh.getAlmConfirmedTime(),1)+"',"+bh.getAlmClearedType()+","+bh.getUsrId()+","+bh.getAlmSeverity()+")";
|
if(i!=(temp.size()-1)){
|
sql+=",";
|
}
|
}
|
}
|
sqlExecuteService.execute(sql);
|
}
|
|
//115-PwrdevDataHistoryService.java;89行
|
public List<PwrdevDataHistory> getWeekOrDayData( String tableName,Date startTime,Date endTime){
|
String sql=" select * from db_pwrdev_data_history."+tableName+" where record_time>='"+ThreadLocalUtil.format(startTime,1)+"' and record_time<='"+ThreadLocalUtil.format(endTime,1)+"' ";
|
List<PwrdevDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevDataHistory ph=new PwrdevDataHistory();
|
ph.setNum(rs.getInt("num"));
|
ph.setPowerDeviceId(rs.getInt("PowerDeviceId"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setAcAcIn1VolA(rs.getFloat("AC_acIn1_volA"));
|
ph.setAcAcIn1VolB(rs.getFloat("AC_acIn1_volB"));
|
ph.setAcAcIn1VolC(rs.getFloat("AC_acIn1_volC"));
|
ph.setAcAcIn1CurrA(rs.getFloat("AC_acIn1_currA"));
|
ph.setAcAcIn1CurrB(rs.getFloat("AC_acIn1_currB"));
|
ph.setAcAcIn1CurrC(rs.getFloat("AC_acIn1_currC"));
|
ph.setAcAcIn2VolA(rs.getFloat("AC_acIn2_volA"));
|
ph.setAcAcIn2VolB(rs.getFloat("AC_acIn2_volB"));
|
ph.setAcAcIn2VolC(rs.getFloat("AC_acIn2_volC"));
|
ph.setAcAcIn2CurrA(rs.getFloat("AC_acIn2_currA"));
|
ph.setAcAcIn2CurrB(rs.getFloat("AC_acIn2_currB"));
|
ph.setAcAcIn2CurrC(rs.getFloat("AC_acIn2_currC"));
|
ph.setAcTemprature(rs.getFloat("AC_temprature"));
|
ph.setAcAcVolHLimit(rs.getFloat("AC_acVolH_Limit"));
|
ph.setAcAcVolLLimit(rs.getFloat("AC_acVolL_Limit"));
|
ph.setAcdcAcIn1VolA(rs.getFloat("ACDC_acIn1_volA"));
|
ph.setAcdcAcIn1VolB(rs.getFloat("ACDC_acIn1_volB"));
|
ph.setAcdcAcIn1VolC(rs.getFloat("ACDC_acIn1_volC"));
|
ph.setAcdcAcIn2VolA(rs.getFloat("ACDC_acIn2_volA"));
|
ph.setAcdcAcIn2VolB(rs.getFloat("ACDC_acIn2_volB"));
|
ph.setAcdcAcIn2VolC(rs.getFloat("ACDC_acIn2_volC"));
|
ph.setAcdcAcOutVolA(rs.getFloat("ACDC_acOut_volA"));
|
ph.setAcdcAcOutVolB(rs.getFloat("ACDC_acOut_volB"));
|
ph.setAcdcAcOutVolC(rs.getFloat("ACDC_acOut_volC"));
|
ph.setAcdcAcOutCurrA(rs.getFloat("ACDC_acOut_currA"));
|
ph.setAcdcAcOutCurrB(rs.getFloat("ACDC_acOut_currB"));
|
ph.setAcdcAcOutCurrC(rs.getFloat("ACDC_acOut_currC"));
|
ph.setAcdcDcOutVol(rs.getFloat("ACDC_dcOut_vol"));
|
ph.setAcdcLoaderCurr(rs.getFloat("ACDC_loader_curr"));
|
ph.setAcdcBattgroup1Vol(rs.getFloat("ACDC_battgroup1_vol"));
|
ph.setAcdcBattgroup1Curr(rs.getFloat("ACDC_battgroup1_curr"));
|
ph.setAcdcBattgroup2Vol(rs.getFloat("ACDC_battgroup2_vol"));
|
ph.setAcdcBattgroup2Curr(rs.getFloat("ACDC_battgroup2_curr"));
|
ph.setAcdcAcdcmTemp(rs.getFloat("ACDC_acdcm_temp"));
|
ph.setAcdcM1OutCurr(rs.getFloat("ACDC_m1_outCurr"));
|
ph.setAcdcM2OutCurr(rs.getFloat("ACDC_m2_outCurr"));
|
ph.setAcdcM3OutCurr(rs.getFloat("ACDC_m3_outCurr"));
|
ph.setAcdcM4OutCurr(rs.getFloat("ACDC_m4_outCurr"));
|
ph.setAcdcM5OutCurr(rs.getFloat("ACDC_m5_outCurr"));
|
ph.setAcdcM6OutCurr(rs.getFloat("ACDC_m6_outCurr"));
|
ph.setAcdcM7OutCurr(rs.getFloat("ACDC_m7_outCurr"));
|
ph.setAcdcM8OutCurr(rs.getFloat("ACDC_m8_outCurr"));
|
ph.setAcdcM9OutCurr(rs.getFloat("ACDC_m9_outCurr"));
|
ph.setAcdcM10OutCurr(rs.getFloat("ACDC_m10_outCurr"));
|
ph.setAcdcM11OutCurr(rs.getFloat("ACDC_m11_outCurr"));
|
ph.setAcdcM12OutCurr(rs.getFloat("ACDC_m12_outCurr"));
|
ph.setAcdcM13OutCurr(rs.getFloat("ACDC_m13_outCurr"));
|
ph.setAcdcM14OutCurr(rs.getFloat("ACDC_m14_outCurr"));
|
ph.setAcdcM15OutCurr(rs.getFloat("ACDC_m15_outCurr"));
|
ph.setAcdcM16OutCurr(rs.getFloat("ACDC_m16_outCurr"));
|
ph.setAcdcChargLimitCurr(rs.getFloat("ACDC_chargLimitCurr"));
|
ph.setAcdcJunChargeVol(rs.getFloat("ACDC_junChargeVol"));
|
ph.setAcdcFloatChargeVol(rs.getFloat("ACDC_floatChargeVol"));
|
ph.setAcdcAcVolHLimit(rs.getFloat("ACDC_acVolH_Limit"));
|
ph.setAcdcAcVolLLimit(rs.getFloat("ACDC_acVolL_Limit"));
|
ph.setAcdcDcOutVolHLimit(rs.getFloat("ACDC_dcOutVolH_Limit"));
|
ph.setAcdcDcOutVolLLimit(rs.getFloat("ACDC_dcOutVolL_Limit"));
|
ph.setDcDcIn1Vol(rs.getFloat("DC_dcIn1_vol"));
|
ph.setDcDcIn2Vol(rs.getFloat("DC_dcIn2_vol"));
|
ph.setDcDcOut1Vol(rs.getFloat("DC_dcOut1_vol"));
|
ph.setDcDcOut1Curr(rs.getFloat("DC_dcOut1_curr"));
|
ph.setDcDcOut2Vol(rs.getFloat("DC_dcOut2_vol"));
|
ph.setDcDcOut2Curr(rs.getFloat("DC_dcOut2_curr"));
|
ph.setDcTemprature(rs.getFloat("DC_temprature"));
|
ph.setDcDcVolHLimit(rs.getFloat("DC_dcVolH_Limit"));
|
ph.setDcDcVolLLimit(rs.getFloat("DC_dcVolL_Limit"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//119-HistoryDataArchivingService.java;127行
|
public void createTable4DevAlarmDataHistoryByYear( String year){
|
String sql=" CREATE TABLE db_alarm.`tb_devalarm_data_history_"+year+"` ( " +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT, " +
|
" `record_id` bigint(20) NOT NULL DEFAULT '0', " +
|
" `dev_id` int(11) NOT NULL DEFAULT '0', " +
|
" `dev_ip` varchar(24) NOT NULL DEFAULT '127.0.0.1', " +
|
" `alm_type` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_level` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_start_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_end_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_value` float NOT NULL DEFAULT '0', " +
|
" `alm_is_confirmed` tinyint(1) NOT NULL DEFAULT '0', " +
|
" `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00', " +
|
" `alm_cleared_type` int(11) NOT NULL DEFAULT '0', " +
|
" `usr_Id` int(11) NOT NULL DEFAULT '0', " +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0', " +
|
" PRIMARY KEY (`num`), " +
|
" KEY `index_record_id` (`record_id`), " +
|
" KEY `index_dev_id` (`dev_id`), " +
|
" KEY `index_alm_type` (`alm_type`), " +
|
" KEY `index_alm_start_time` (`alm_start_time`), " +
|
" KEY `index_alm_cleared_type` (`alm_cleared_type`) " +
|
" ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
|
sqlExecuteService.execute(sql);
|
}
|
|
//121-SubTablePageInfoUtils.java;503行
|
public List<BattalarmDataHistory> getLevelSubList_dev(int uId, String tableName){
|
String sql=" SELECT distinct history.num,history.alm_level " +
|
" FROM db_alarm."+tableName+" history, " +
|
" db_battinf.tb_battinf " +
|
" where history.dev_id = db_battinf.tb_battinf.FbsDeviceId " +
|
" and alm_level in(1,2,3,4) " +
|
" and history.dev_id in (select distinct db_battinf.tb_battinf.FbsDeviceId " +
|
" 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 = "+uId+")";
|
List<BattalarmDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
BattalarmDataHistory ph=new BattalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//125-BattmonTestcapService.java;42行
|
public void del(BattmonTestcap btcp){
|
String sql="delete from db_battinf."+btcp.getTableName()+" " +
|
" where monvolstd="+btcp.getMonvolstd()+" and moncapstd="+btcp.getMoncapstd()+" and battproducer='"+btcp.getBattproducer()+"' and " +
|
" battmodel='"+btcp.getBattmodel()+"' and note='"+btcp.getNote()+"' ";
|
sqlExecuteService.execute(sql);
|
}
|
|
//130-SubTablePageInfoUtils.java;506行
|
public List<BattalarmDataHistory> getLevelSubList_pwr(int uId, String tableName, List<Integer> almTypes){
|
String sql="SELECT distinct alarm.num,alarm.alm_level " +
|
" from db_pwrdev_alarm."+tableName+" alarm,db_pwrdev_inf.tb_pwrdev_inf " +
|
" inf where alarm.PowerDeviceId = inf.PowerDeviceId " +
|
" and alm_level in(1,2,3,4) " ;
|
if(almTypes!=null&&almTypes.size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<almTypes.size();i++) {
|
sql+=almTypes.get(i);
|
if(i!=(almTypes.size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and StationId in ( select distinct inf.StationId from " +
|
" (select StationId from db_battinf.tb_battinf union select StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf, " +
|
" 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.StationId=inf.StationId " +
|
" 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="+uId+" )";
|
List<BattalarmDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
BattalarmDataHistory ph=new BattalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//1-PwrdevHistorydataGwMapper.xml;11行
|
public List<Integer> searchMaxNum2(Date startTime, Date stopTime,String table){
|
String sql="(SELECT num " +
|
" FROM db_pwrdev_data_history_gw.tb_pwrdev_historydata_gw_"+table+" " +
|
" WHERE record_time BETWEEN '"+ThreadLocalUtil.format(startTime,1)+"' " +
|
" AND '"+ThreadLocalUtil.format(stopTime,1)+"' " +
|
" ORDER BY record_time asc limit 1) " +
|
" union all " +
|
" (SELECT num " +
|
" FROM db_pwrdev_data_history_gw.tb_pwrdev_historydata_gw_"+table+" " +
|
" WHERE record_time BETWEEN '"+ThreadLocalUtil.format(startTime,1)+"' " +
|
" AND '"+ThreadLocalUtil.format(stopTime,1)+"' " +
|
" ORDER BY record_time desc limit 1)";
|
List<Integer> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Integer> list=new ArrayList<>();
|
while (rs.next()){
|
list.add(rs.getInt("num"));
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//4-StandardFeatureCurveMapper.xml;6行
|
public void insertBatch(String titleNameUnion, List<StandardFeatureCurve> curveList2){
|
String sql="insert into `web_site`.`tb_standard_feature_curve_"+titleNameUnion+"` (`time`,`value`) values ";
|
if(curveList2!=null&&curveList2.size()>0){
|
for (int i=0;i<curveList2.size();i++) {
|
StandardFeatureCurve bh=curveList2.get(i);
|
sql+="('"+bh.getTime()+"',"+bh.getValue()+")";
|
if(i!=(curveList2.size()-1)){
|
sql+=",";
|
}
|
}
|
}
|
sqlExecuteService.execute(sql);
|
}
|
//100-StandardFeatureCurveMapper.xml;12行
|
public void createTable(String titleNameUnion){
|
String sql="CREATE TABLE `web_site`.`tb_standard_feature_curve_"+titleNameUnion+"` ( " +
|
" `id` INT NOT NULL AUTO_INCREMENT, " +
|
" `time` VARCHAR(45) NULL, " +
|
" `value` DOUBLE NULL, " +
|
" PRIMARY KEY (`id`));";
|
sqlExecuteService.execute(sql);
|
}
|
|
//6-FBOTestDataMapper.xml;21行
|
public void delete(Integer battGroupId, Integer testRecordCount){
|
String sql="delete from db_fbo_testdata.tb_fbotestdata_"+battGroupId+" where test_record_count = "+testRecordCount+" ";
|
sqlExecuteService.execute(sql);
|
}
|
|
//7-ElePriceMapper.xml;22行
|
public void deleteByNum(String tableName, int num){
|
String sql=" delete from web_site."+tableName+" where num = "+num+" ";
|
sqlExecuteService.execute(sql);
|
}
|
|
//10-BattalarmDataHistoryMapper.xml;224行
|
public List<BattalarmDataHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName){
|
String sql="select h.*,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_alarm."+tableName+" h,db_battinf.tb_battinf b " +
|
" where h.BattGroupId = b.BattGroupId " ;
|
if(stationId!=null&&!stationId.isEmpty()){
|
sql+=" and b.stationId = '"+stationId+"' ";
|
}
|
sql+=" and h.alm_start_time >='"+ThreadLocalUtil.format(startTime,1)+"' and h.alm_start_time <= '"+ThreadLocalUtil.format(endTime,1)+"' ";
|
List<BattalarmDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
BattalarmDataHistory data=new BattalarmDataHistory();
|
data.setNum(rs.getLong("num"));
|
data.setMonNum(rs.getInt("MonNum"));
|
data.setRecordId(rs.getLong("Record_Id"));
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setAlmSignalId(rs.getInt("alm_signal_id"));
|
data.setAlmValue(rs.getFloat("alm_value"));
|
data.setAlmLevel(rs.getInt("alm_level"));
|
data.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
data.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
data.setUsrId(rs.getInt("usr_Id"));
|
data.setAlmSeverity(rs.getInt("alm_severity"));
|
data.setStationName1(rs.getString("StationName1"));
|
data.setStationName2(rs.getString("StationName2"));
|
data.setStationName3(rs.getString("StationName3"));
|
data.setStationName5(rs.getString("StationName5"));
|
list.add(data);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//12-ElePriceMapper.xml;4行
|
public void addElePrice(ElePrice elePrice){
|
String sql=" insert into web_site.tb_eleprice_"+elePrice.getTmpId()+" (Tmp_id,Tmp_starttime,Tmp_stoptime,Ele_price) " +
|
" values( "+elePrice.getTmpId()+",'"+ThreadLocalUtil.format(elePrice.getTmpStarttime(),4)+"','"+ThreadLocalUtil.format(elePrice.getTmpStoptime(),4)+"',"+elePrice.getElePrice()+")";
|
sqlExecuteService.execute(sql);
|
}
|
//15-BattResDataMapper.xml;15行
|
public BattResData getMinInfo(Integer battGroupId, Integer testRecordCount){
|
String sql=" select mon_num ,mon_vol from db_batt_testdata.tb_battresdata_"+battGroupId+" " +
|
" where mon_vol=(select min(mon_vol) from db_batt_testdata.tb_battresdata_"+battGroupId+" where test_record_count="+testRecordCount+" ) limit 1";
|
List<BattResData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattResData> list=new ArrayList<>();
|
while (rs.next()){
|
BattResData bh=new BattResData();
|
bh.setMonNum(rs.getInt("mon_num"));
|
bh.setMonVol(rs.getFloat("mon_vol"));
|
list.add(bh);
|
}
|
return list;
|
}
|
});
|
BattResData bres=null;
|
if(list!=null&&list.size()>0){
|
bres=list.get(0);
|
}
|
return bres;
|
|
}
|
//16-Ld9testdatastopMapper.xml;16行
|
public List<Ld9testdatastop> searchlinftestdata(Ld9testdataInf linf){
|
String sql="select * from db_ld9_testdata.tb_ld9testdatastop_"+linf.getBattGroupId()+" " +
|
" where test_record_count="+linf.getTestRecordCount()+" order by mon_num asc";
|
List<Ld9testdatastop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdatastop> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdatastop ph=new Ld9testdatastop();
|
ph.setNum(rs.getLong("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setMaxMonvol(rs.getFloat("max_monvol"));
|
ph.setMinMonvol(rs.getFloat("min_monvol"));
|
ph.setTestStopreason(rs.getInt("test_stopreason"));
|
ph.setMonCap(rs.getFloat("mon_cap"));
|
ph.setMonRestCap(rs.getFloat("mon_rest_cap"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//17-BattTestparamMapper.xml;5行
|
public int judgeTable( String battGroupId){
|
String sql="select count(*) as tableNum " +
|
" from INFORMATION_SCHEMA.TABLES " +
|
" where TABLE_SCHEMA = 'db_dev_testparam' " +
|
" and TABLE_NAME = 'tb_batt_testparam_"+battGroupId+"'";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("tableNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int tableNum =0;
|
if(list!=null){
|
tableNum= (int) list.get(0);
|
}
|
return tableNum;
|
}
|
|
//18-PwrdevHistorydataGwMapper.xml;24行
|
public List<PwrdevHistorydataGw> serchByCondition(Date startTime, Date stopTime, String table, int roteN){
|
String sql=" select * from (select a.*, (@i:= @i+1) as number " +
|
" from (select * " +
|
" from db_pwrdev_data_history_gw.tb_pwrdev_historydata_gw_"+table+" " +
|
" where record_time >= '"+ThreadLocalUtil.format(startTime,1)+"' " +
|
" and record_time <= '"+ThreadLocalUtil.format(stopTime,1)+"' " +
|
" ) a, " +
|
" (select @i:=0) b) c " +
|
" where c.number%"+roteN+"=0" ;
|
List<PwrdevHistorydataGw> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevHistorydataGw> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevHistorydataGw ph=new PwrdevHistorydataGw();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setPowerdeviceid(rs.getLong("powerdeviceid"));
|
ph.setAcin1Vola(rs.getFloat("acin1_vola"));
|
ph.setAcin1Volb(rs.getFloat("acin1_volb"));
|
ph.setAcin1Volc(rs.getFloat("acin1_volc"));
|
ph.setAcin2Vola(rs.getFloat("acin2_vola"));
|
ph.setAcin2Volb(rs.getFloat("acin2_volb"));
|
ph.setAcin2Volc(rs.getFloat("acin2_volc"));
|
ph.setAcin1Curra(rs.getFloat("acin1_curra"));
|
ph.setAcin1Currb(rs.getFloat("acin1_currb"));
|
ph.setAcin1Currc(rs.getFloat("acin1_currc"));
|
ph.setAcin2Curra(rs.getFloat("acin2_curra"));
|
ph.setAcin2Currb(rs.getFloat("acin2_currb"));
|
ph.setAcin2Currc(rs.getFloat("acin2_currc"));
|
ph.setDcoutVol(rs.getFloat("dcout_vol"));
|
ph.setDcoutCurr(rs.getFloat("dcout_curr"));
|
ph.setBattgroup1Vol(rs.getFloat("battgroup1_vol"));
|
ph.setBattgroup1Curr(rs.getFloat("battgroup1_curr"));
|
ph.setBattgroup2Vol(rs.getFloat("battgroup2_vol"));
|
ph.setBattgroup2Curr(rs.getFloat("battgroup2_curr"));
|
ph.setM1OutVol(rs.getFloat("m1_out_vol"));
|
ph.setM2OutVol(rs.getFloat("m2_out_vol"));
|
ph.setM3OutVol(rs.getFloat("m3_out_vol"));
|
ph.setM4OutVol(rs.getFloat("m4_out_vol"));
|
ph.setM5OutVol(rs.getFloat("m5_out_vol"));
|
ph.setM6OutVol(rs.getFloat("m6_out_vol"));
|
ph.setM7OutVol(rs.getFloat("m7_out_vol"));
|
ph.setM8OutVol(rs.getFloat("m8_out_vol"));
|
ph.setM9OutVol(rs.getFloat("m9_out_vol"));
|
ph.setM10OutVol(rs.getFloat("m10_out_vol"));
|
ph.setM11OutVol(rs.getFloat("m11_out_vol"));
|
ph.setM12OutVol(rs.getFloat("m12_out_vol"));
|
ph.setM13OutVol(rs.getFloat("m13_out_vol"));
|
ph.setM14OutVol(rs.getFloat("m14_out_vol"));
|
ph.setM15OutVol(rs.getFloat("m15_out_vol"));
|
ph.setM16OutVol(rs.getFloat("m16_out_vol"));
|
ph.setM1Outcurr(rs.getFloat("m1_outcurr"));
|
ph.setM2Outcurr(rs.getFloat("m2_outcurr"));
|
ph.setM3Outcurr(rs.getFloat("m3_outcurr"));
|
ph.setM4Outcurr(rs.getFloat("m4_outcurr"));
|
ph.setM5Outcurr(rs.getFloat("m5_outcurr"));
|
ph.setM6Outcurr(rs.getFloat("m6_outcurr"));
|
ph.setM7Outcurr(rs.getFloat("m7_outcurr"));
|
ph.setM8Outcurr(rs.getFloat("m8_outcurr"));
|
ph.setM9Outcurr(rs.getFloat("m9_outcurr"));
|
ph.setM10Outcurr(rs.getFloat("m10_outcurr"));
|
ph.setM11Outcurr(rs.getFloat("m11_outcurr"));
|
ph.setM12Outcurr(rs.getFloat("m12_outcurr"));
|
ph.setM13Outcurr(rs.getFloat("m13_outcurr"));
|
ph.setM14Outcurr(rs.getFloat("m14_outcurr"));
|
ph.setM15Outcurr(rs.getFloat("m15_outcurr"));
|
ph.setM16Outcurr(rs.getFloat("m16_outcurr"));
|
ph.setMTemper1(rs.getFloat("m_temper1"));
|
ph.setMTemper2(rs.getFloat("m_temper2"));
|
ph.setMHumidity1(rs.getFloat("m_humidity1"));
|
ph.setMHumidity2(rs.getFloat("m_humidity2"));
|
ph.setUseracin1Vola(rs.getFloat("useracin1_vola"));
|
ph.setUseracin1Volb(rs.getFloat("useracin1_volb"));
|
ph.setUseracin1Volc(rs.getFloat("useracin1_volc"));
|
ph.setUseracin1Curra(rs.getFloat("useracin1_curra"));
|
ph.setUseracin1Currb(rs.getFloat("useracin1_currb"));
|
ph.setUseracin1Currc(rs.getFloat("useracin1_currc"));
|
ph.setUseracin2Vola(rs.getFloat("useracin2_vola"));
|
ph.setUseracin2Volb(rs.getFloat("useracin2_volb"));
|
ph.setUseracin2Volc(rs.getFloat("useracin2_volc"));
|
ph.setUseracin2Curra(rs.getFloat("useracin2_curra"));
|
ph.setUseracin2Currb(rs.getFloat("useracin2_currb"));
|
ph.setUseracin2Currc(rs.getFloat("useracin2_currc"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//19-Ld9testdatastopMapper.xml;6行
|
public List<Ld9testdatastop> serchByInfo(int testRecordCount, int battGroupId, int testMonNum){
|
String sql=" select num,BattGroupId,test_record_count,test_type,test_starttime,record_time,test_timelong,group_vol,test_curr,test_cap,mon_cap,mon_rest_cap,mon_num,mon_vol,test_monnum,max_monvol,min_monvol,test_stopreason " +
|
" from db_ld9_testdata.tb_ld9testdatastop_"+battGroupId+" " +
|
" where test_record_count="+testRecordCount+" and test_monnum="+testMonNum+" ";
|
List<Ld9testdatastop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdatastop> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdatastop ph=new Ld9testdatastop();
|
ph.setNum(rs.getLong("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setMaxMonvol(rs.getFloat("max_monvol"));
|
ph.setMinMonvol(rs.getFloat("min_monvol"));
|
ph.setTestStopreason(rs.getInt("test_stopreason"));
|
ph.setMonCap(rs.getFloat("mon_cap"));
|
ph.setMonRestCap(rs.getFloat("mon_rest_cap"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//97-Ld9testdatastopMapper.xml;11行
|
public List<Ld9testdatastop> serchByCondition(int testRecordCount, int battGroupId){
|
String sql=" select num,BattGroupId,test_record_count,test_type,test_starttime,record_time,test_timelong,group_vol,test_curr,test_cap,mon_cap,mon_rest_cap,mon_num,mon_vol,test_monnum,max_monvol,min_monvol,test_stopreason " +
|
" from db_ld9_testdata.tb_ld9testdatastop_"+battGroupId+" " +
|
" where test_record_count="+testRecordCount+" ";
|
List<Ld9testdatastop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdatastop> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdatastop ph=new Ld9testdatastop();
|
ph.setNum(rs.getLong("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setMaxMonvol(rs.getFloat("max_monvol"));
|
ph.setMinMonvol(rs.getFloat("min_monvol"));
|
ph.setTestStopreason(rs.getInt("test_stopreason"));
|
ph.setMonCap(rs.getFloat("mon_cap"));
|
ph.setMonRestCap(rs.getFloat("mon_rest_cap"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//20-BattResDataMapper.xml;12行
|
public Float calcAvgVol(Integer battGroupId, Integer testRecordCount){
|
String sql="select avg(binary mon_vol) as monVol from db_batt_testdata.tb_battresdata_"+battGroupId+" where test_record_count= "+testRecordCount+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getFloat("monVol"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
Float monVol =0f;
|
if(list!=null){
|
monVol= (Float) list.get(0);
|
}
|
return monVol;
|
}
|
|
//64-ElePriceMapper.xml;18行
|
public void deleteEleprice(String tableName){
|
String sql=" delete from web_site."+tableName+" ";
|
sqlExecuteService.execute(sql);
|
}
|
//23-ElePriceMapper.xml;8行
|
public void createEleprice(String tableName){
|
String sql="CREATE TABLE IF NOT EXISTS web_site."+tableName+" ( " +
|
" `num` int(11) NOT NULL AUTO_INCREMENT, " +
|
" `tmp_id` int(11) NOT NULL DEFAULT '1001', " +
|
" `tmp_starttime` TIME NOT NULL DEFAULT '00:00:00' COMMENT '开始时间', " +
|
" `tmp_stoptime` TIME NOT NULL DEFAULT '00:00:00' COMMENT '结束时间', " +
|
" `ele_price` float NOT NULL DEFAULT '0' COMMENT '电费价格', " +
|
" PRIMARY KEY (`num`) " +
|
" )";
|
sqlExecuteService.execute(sql);
|
}
|
//75-ElePriceMapper.xml;25行
|
public List<ElePrice> getList(String tableName){
|
String sql="select * from web_site."+tableName+" order by tmp_starttime asc";
|
List<ElePrice> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<ElePrice> list=new ArrayList<>();
|
while (rs.next()){
|
ElePrice ph=new ElePrice();
|
ph.setNum(rs.getInt("num"));
|
ph.setTmpId(rs.getInt("tmp_id"));
|
ph.setTmpStarttime(rs.getTimestamp("tmp_starttime"));
|
ph.setTmpStoptime(rs.getTimestamp("tmp_stoptime"));
|
ph.setElePrice(rs.getFloat("ele_price"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//24-BattRealdataMapper.xml;23行
|
public int judgeTable_realdata( String table){
|
String sql="select count(*) as tableNum " +
|
" from INFORMATION_SCHEMA.TABLES " +
|
" where TABLE_SCHEMA = 'db_batt_history' " +
|
" and TABLE_NAME = 'tb_batt_realdata_"+table+"'";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("tableNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int tableNum =0;
|
if(list!=null){
|
tableNum= (int) list.get(0);
|
}
|
return tableNum;
|
}
|
|
//29-BattRealdataMapper.xml;42行
|
public List<Integer> searchMaxNum2(BattRealdata realdata){
|
String sql="(SELECT " +
|
" num " +
|
" FROM " +
|
" db_batt_history.`tb_batt_realdata_"+realdata.getTableName()+"` " +
|
" WHERE " +
|
" recrod_time BETWEEN '"+ThreadLocalUtil.format(realdata.getRecrodTime(),1)+"' " +
|
" AND '"+ThreadLocalUtil.format(realdata.getRecrodTime1(),1)+"' " +
|
" ORDER BY " +
|
" recrod_time asc limit 1) " +
|
" union all " +
|
" (SELECT " +
|
" num " +
|
" FROM " +
|
" db_batt_history.`tb_batt_realdata_"+realdata.getTableName()+"` " +
|
" WHERE " +
|
" recrod_time BETWEEN '"+ThreadLocalUtil.format(realdata.getRecrodTime(),1)+"' " +
|
" AND '"+ThreadLocalUtil.format(realdata.getRecrodTime1(),1)+"' " +
|
" ORDER BY " +
|
" recrod_time desc limit 1)";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("num"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
return list;
|
}
|
|
//92-BattRealdataMapper.xml;29行
|
public int searchMaxNum(BattRealdata realdata){
|
String sql="select count(*) as maxNum " +
|
" from db_batt_history.tb_batt_realdata_"+realdata.getTableName()+" " +
|
" where recrod_time >= '"+ThreadLocalUtil.format(realdata.getRecrodTime(),1)+"' " +
|
" and recrod_time <= '"+ThreadLocalUtil.format(realdata.getRecrodTime1(),1)+"' " +
|
" and mon_num = "+realdata.getMonNum()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("maxNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int maxNum =0;
|
if(list!=null){
|
maxNum= (int) list.get(0);
|
}
|
return maxNum;
|
}
|
//105-BattRealdataMapper.xml;36行
|
public int searchMinNum(BattRealdata realdata){
|
String sql="select min(num) as minNum " +
|
" from db_batt_history.tb_batt_realdata_"+realdata.getTableName()+" " +
|
" where mon_num = "+realdata.getMonNum()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("minNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int minNum =0;
|
if(list!=null){
|
minNum= (int) list.get(0);
|
}
|
return minNum;
|
}
|
|
//BattRealdataMapper.xml;63行
|
public List<RealDateDTO> serchByCondition2(BattRealdata realdata){
|
String sql=" select recrod_time, " +
|
" group_vol, " +
|
" online_vol, " +
|
" group_curr, " +
|
" mon_vol, " +
|
" mon_tmp, " +
|
" mon_res, " +
|
" mon_num " +
|
" from (select a.*, (@i:= @i+1) as number " +
|
" from (select * " +
|
" from db_batt_history.tb_batt_realdata_"+realdata.getTableName()+" " +
|
" where recrod_time >= '"+ThreadLocalUtil.format(realdata.getRecrodTime(),1)+"' " +
|
" and recrod_time <= '"+ThreadLocalUtil.format(realdata.getRecrodTime1(),1)+"' " +
|
" and mon_num = "+realdata.getMonNum()+") a, " +
|
" (select @i:=0) b) c " +
|
" where c.number%"+realdata.getRoteN()+"=0";
|
List<RealDateDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<RealDateDTO> list=new ArrayList<>();
|
while (rs.next()){
|
RealDateDTO ph=new RealDateDTO();
|
ph.setRecrodTime(rs.getTimestamp("recrod_time"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupCurr(rs.getFloat("group_curr"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
ph.setMonRes(rs.getFloat("mon_res"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//32-BattTestDataStopMapper.xml;26行
|
public List<BattTestDataStop> getList(int battGroupId,int testRecordCount){
|
String sql="SELECT DISTINCT battGroupId, test_curr, test_cap, mon_num, mon_vol " +
|
" FROM " +
|
" db_batt_testdata.tb_batttestdatastop_"+battGroupId+" " +
|
" WHERE " +
|
" test_record_count = "+testRecordCount+" " +
|
" AND data_available = 1";
|
List<BattTestDataStop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattTestDataStop> list=new ArrayList<>();
|
while (rs.next()){
|
BattTestDataStop ph=new BattTestDataStop();
|
ph.setBattGroupId(rs.getInt("battGroupId"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//33-PwrdevAlarmHistoryMapper.xml;362行
|
public List<PwrdevAlarmHistory> getListByStationId_pwr(Date startTime, Date endTime, String stationId,String tableName){
|
String sql=" select h.*,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_pwrdev_alarm."+tableName+" h,db_pwrdev_inf.tb_pwrdev_inf b " +
|
" where h.PowerDeviceId = b.PowerDeviceId " ;
|
if(stationId !=null&&!stationId.isEmpty()){
|
sql+=" and b.stationId = '"+stationId+"' ";
|
}
|
sql+=" and h.alm_start_time >= '"+ThreadLocalUtil.format(startTime,1)+"' and h.alm_start_time <='"+ThreadLocalUtil.format(endTime,1)+"' ";
|
List<PwrdevAlarmHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<PwrdevAlarmHistory> list=new ArrayList<>();
|
while (rs.next()){
|
PwrdevAlarmHistory ph=new PwrdevAlarmHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordId(rs.getLong("record_id"));
|
ph.setPowerDeviceId(rs.getLong("PowerDeviceId"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmSource(rs.getInt("alm_source"));
|
ph.setAlmIndex(rs.getInt("alm_index"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmValue(rs.getFloat("alm_value"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setUsrId(rs.getInt("usr_Id"));
|
ph.setAlmTrigger(rs.getInt("alm_trigger"));
|
ph.setAlmSeverity(rs.getInt("alm_severity"));
|
ph.setStationName1(rs.getString("StationName1"));
|
ph.setStationName2(rs.getString("StationName2"));
|
ph.setStationName3(rs.getString("StationName3"));
|
ph.setStationName5(rs.getString("StationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//35-Ld9testdataMapper.xml;12行
|
public List<Ld9testdata> serchByCondition(int roteN, int testRecordCount, int battGroupId, int testMonNum){
|
String sql="select num,BattGroupId,test_record_count,test_type,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol" +
|
",test_curr,test_cap,mon_cap,mon_rest_cap,mon_num,mon_vol,mon_tmp,test_monnum " +
|
" from db_ld9_testdata.tb_ld9testdata_"+battGroupId+" " +
|
" where test_record_count="+testRecordCount+" and (record_num%"+roteN+"=0 ) and test_monnum="+testMonNum+" ";
|
List<Ld9testdata> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdata> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdata ph=new Ld9testdata();
|
ph.setNum(rs.getLong("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setMonCap(rs.getFloat("mon_cap"));
|
ph.setMonRestCap(rs.getFloat("mon_rest_cap"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//59-Ld9testdataMapper.xml;6行
|
public List<Ld9testdata> serchByInfo(Ld9testdata ld9testdata){
|
String sql=" select test_monnum,max(record_num) as record_nums,BattGroupId,test_record_count " +
|
" from db_ld9_testdata.tb_ld9testdata_"+ld9testdata.getBattGroupId()+" " +
|
" where test_record_count="+ld9testdata.getTestRecordCount()+" " +
|
" GROUP BY test_monnum order by test_monnum asc";
|
List<Ld9testdata> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdata> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdata ph=new Ld9testdata();
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setRecordNums(rs.getInt("record_nums"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//73-Ld9testdataMapper.xml;17行
|
public List<Ld9testdata> getLD9AllGroupByTestRecordCount(Ld9testdataInf ld9inf){
|
String sql="select * from db_ld9_testdata.tb_ld9testdata_"+ld9inf.getBattGroupId()+" " +
|
" where test_record_count="+ld9inf.getTestRecordCount()+" order by mon_num asc";
|
List<Ld9testdata> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdata> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdata ph=new Ld9testdata();
|
ph.setNum(rs.getLong("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setTestMonnum(rs.getInt("test_monnum"));
|
ph.setMonCap(rs.getFloat("mon_cap"));
|
ph.setMonRestCap(rs.getFloat("mon_rest_cap"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//62-Ld9testdataMapper.xml;22行
|
public Map getLD9AllGroupTimeLong(Ld9testdataInf ld9inf){
|
String sql="select mon_num,max(test_timelong) as max_testTimelong " +
|
" from db_ld9_testdata.tb_ld9testdata_"+ld9inf.getBattGroupId()+" " +
|
" where test_record_count="+ld9inf.getTestRecordCount()+" group by mon_num";
|
List<Ld9testdata> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<Ld9testdata> list=new ArrayList<>();
|
while (rs.next()){
|
Ld9testdata ph=new Ld9testdata();
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setTestTimelong(rs.getInt("max_testTimelong"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
Map map=new HashMap();
|
if(list!=null&&list.size()>0){
|
for (Ld9testdata ld9:list) {
|
map.put(ld9.getMonNum(),ld9.getTestTimelong());
|
}
|
}
|
return map;
|
}
|
|
//37-BattTestDataStopMapper.xml;35行
|
public Float calcAvgVol_stop(Integer battGroupId, Integer testRecordCount){
|
String sql=" SELECT " +
|
" AVG(BINARY mon_vol) AS mon_avg " +
|
" FROM " +
|
" db_batt_testdata.tb_batttestdatastop_"+battGroupId+" " +
|
" WHERE " +
|
" test_record_count = "+testRecordCount+" " +
|
" AND data_available = 1 " +
|
" ORDER BY BattgroupId";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getFloat("mon_avg"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
Float mon_avg =0f;
|
if(list!=null){
|
mon_avg= (Float) list.get(0);
|
}
|
return mon_avg;
|
|
}
|
|
//65-BattTestDataStopMapper.xml;6行
|
public BattTestDataStop findByMonNum(Integer battGroupId,Integer monNum){
|
String sql="select stop.battgroupid,stop.test_curr,stop.test_cap,stop.mon_vol,stop.test_record_count,stop.mon_num,stop.group_vol,inf.max_monvol " +
|
" from db_batt_testdata.tb_batttestdatastop_"+battGroupId+" stop,db_batt_testdata.tb_batttestdata_inf inf " +
|
" where stop.battGroupId = inf.battGroupId and stop.test_starttime = inf.test_starttime and stop.mon_num ="+monNum+" " +
|
" and stop.data_available=1 and inf.test_type=3 " +
|
" order by stop.test_starttime desc limit 1";
|
List<BattTestDataStop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattTestDataStop> list=new ArrayList<>();
|
while (rs.next()){
|
BattTestDataStop ph=new BattTestDataStop();
|
ph.setBattGroupId(rs.getInt("battGroupId"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setMaxMonVol(rs.getFloat("max_monvol"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
BattTestDataStop btsp=null;
|
if(list!=null&&list.size()>0){
|
btsp=list.get(0);
|
}
|
return btsp;
|
}
|
|
//77-BattTestDataStopMapper.xml;13行
|
public Float getRealCap(BattTestDataStop stop){
|
String sql=" select test_cap from db_batt_testdata.tb_batttestdata_"+stop.getBattGroupId()+" " +
|
" where test_record_count="+stop.getTestRecordCount()+" and mon_vol>"+stop.getMonVol()+" and mon_num="+stop.getMonNum()+" and data_available=1 " +
|
" order by record_time desc limit 1";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
while (rs.next()){
|
list.add(rs.getFloat("test_cap"));
|
}
|
return list;
|
}
|
});
|
float test_cap=0f;
|
if(list!=null&&list.size()>0){
|
test_cap= (float) list.get(0);
|
}
|
return test_cap;
|
}
|
|
//89-BattTestDataStopMapper.xml;20行
|
public List<BattTestDataStop> getTestDataStop(Integer battGroupId, Date testStarttime){
|
String sql=" select num,BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol" +
|
",group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp " +
|
" from db_batt_testdata.tb_batttestdatastop_"+battGroupId+" " +
|
" where test_starttime='"+ThreadLocalUtil.format(testStarttime,1)+"' " +
|
" and data_available=1";
|
List<BattTestDataStop> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattTestDataStop> list=new ArrayList<>();
|
while (rs.next()){
|
BattTestDataStop ph=new BattTestDataStop();
|
ph.setBattGroupId(rs.getInt("battGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestType(rs.getInt("test_type"));
|
ph.setDataNew(rs.getInt("data_new"));
|
ph.setDataAvailable(rs.getInt("data_available"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
ph.setTestStarttime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimelong(rs.getInt("test_timelong"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//38,67--BattMaintDealarmMapper.xml;226行 210
|
public List<ReportBattResultDTO> searchByBattGroupId5(ReportBattDTO dto){
|
String sql=" select distinct(db_battinf.tb_battinf.BattgroupId),moncount,moncapstd,monvolstd,monresstd,monserstd,FloatVolLevel,OfflineVolLevel " +
|
" ,db_batt_testdata.tb_battresdata_inf.battgroupid,db_batt_testdata.tb_battresdata_inf.test_record_count " +
|
" ,db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".group_vol,db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".test_curr" +
|
" ,db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".test_starttime,mon_num,mon_vol,mon_tmp,mon_res,mon_ser,conn_res " +
|
" from " +
|
" db_battinf.tb_battinf,db_batt_testdata.tb_battresdata_inf,db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+" " +
|
" where " +
|
" db_battinf.tb_battinf.BattgroupId=db_batt_testdata.tb_battresdata_inf.BattGroupId " +
|
" and " +
|
" db_batt_testdata.tb_battresdata_inf.BattGroupId=db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".battgroupid " +
|
" and " +
|
" db_batt_testdata.tb_battresdata_inf.test_record_count=db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".test_record_count " +
|
" and db_battinf.tb_battinf.battgroupid="+dto.getBattGroupId()+" and " +
|
" db_batt_testdata.tb_battresdata_inf.data_available=1 and " +
|
" db_batt_testdata.tb_battresdata_inf.test_type="+dto.getTestType()+" " ;
|
if(dto.getMonNum()!=0){
|
sql+=" and db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".mon_num="+dto.getMonNum()+" ";
|
}
|
if(dto.getStationName1()!=null&&!dto.getStationName1().isEmpty()){
|
sql+=" and stationname1='"+dto.getStationName1()+"' ";
|
}
|
if(dto.getStationName()!=null&&!dto.getStationName().isEmpty()){
|
sql+=" and stationname='"+dto.getStationName()+"' ";
|
}
|
sql+=" order by db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".test_record_count asc,db_batt_testdata.tb_battresdata_"+dto.getBattGroupId()+".mon_num asc ";
|
List<ReportBattResultDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<ReportBattResultDTO> list=new ArrayList<>();
|
while (rs.next()){
|
ReportBattResultDTO ph=new ReportBattResultDTO();
|
ph.setBattGroupId(rs.getInt("battGroupId"));
|
ph.setMonCount(rs.getInt("moncount"));
|
ph.setMonCapStd(rs.getFloat("moncapstd"));
|
ph.setMonVolStd(rs.getFloat("monvolstd"));
|
ph.setMonResStd(rs.getFloat("monresstd"));
|
ph.setMonSerStd(rs.getFloat("monserstd"));
|
ph.setFloatVolLevel(rs.getFloat("FloatVolLevel"));
|
ph.setOfflineVolLevel(rs.getFloat("OfflineVolLevel"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestStartTime(rs.getTimestamp("test_starttime"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
ph.setMonTmp(rs.getFloat("mon_tmp"));
|
ph.setMonSer(rs.getFloat("mon_ser"));
|
ph.setMonRes(rs.getFloat("mon_res"));
|
ph.setConnRes(rs.getFloat("conn_res"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//45-FBOTestDataMapper.xml;24行
|
public List<FBOTestData> getList_fbo(int battGroupId, int testRecordCount){
|
String sql=" select * from db_fbo_testdata.tb_fbotestdata_"+battGroupId+" where test_record_count = "+testRecordCount+" ";
|
List<FBOTestData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<FBOTestData> list=new ArrayList<>();
|
while (rs.next()){
|
FBOTestData ph=new FBOTestData();
|
ph.setNum(rs.getInt("num"));
|
ph.setBattGroupId(rs.getInt("BattGroupId"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestStartTime(rs.getTimestamp("test_starttime"));
|
ph.setTestTimeLong(rs.getInt("test_timelong"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setRecordNum(rs.getInt("record_num"));
|
ph.setOnlineVol(rs.getFloat("online_vol"));
|
ph.setSumVol(rs.getFloat("sum_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//76-FBOTestDataMapper.xml;27行
|
public int getMonCuntBytestRecordCount(int battGroupId, int testRecordCount){
|
String sql="select if(max(mon_num) is null,0,max(mon_num)) as monCount " +
|
"from db_fbo_testdata.tb_fbotestdata_"+battGroupId+" where test_record_count = "+testRecordCount+" limit 1";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("monCount"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int monCount =0;
|
if(list!=null){
|
monCount= (int) list.get(0);
|
}
|
return monCount;
|
}
|
|
//46-BattMaintDealarmMapper.xml;199行
|
public List<ReportBattResultDTO> searchByBattGroupId(ReportBattDTO dto){
|
String sql="select distinct(db_battinf.tb_battinf.BattgroupId),moncount,moncapstd,monvolstd,monresstd,monserstd,FloatVolLevel,OfflineVolLevel" +
|
" ,db_batt_testdata.tb_batttestdata_inf.battgroupid,max_monvol,min_monvol,db_batt_testdata.tb_batttestdata_inf.test_record_count" +
|
" ,db_batt_testdata.tb_batttestdata_inf.test_type,db_batt_testdata.tb_batttestdata_inf.test_record_count" +
|
" ,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_starttime,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".record_time" +
|
" ,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_timelong,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".group_vol" +
|
" ,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_curr,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_cap" +
|
" ,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".mon_num,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".mon_vol" +
|
" from" +
|
" db_battinf.tb_battinf,db_batt_testdata.tb_batttestdata_inf,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+" " +
|
" where" +
|
" db_battinf.tb_battinf.BattgroupId=db_batt_testdata.tb_batttestdata_inf.BattGroupId " +
|
" and" +
|
" db_batt_testdata.tb_batttestdata_inf.BattGroupId=db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".battgroupid " +
|
" and" +
|
" db_batt_testdata.tb_batttestdata_inf.test_record_count=db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_record_count " +
|
" and" +
|
" db_batt_testdata.tb_batttestdata_inf.record_num=db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".record_num " +
|
" and db_battinf.tb_battinf.battgroupid="+dto.getBattGroupId()+" and " +
|
" db_batt_testdata.tb_batttestdata_inf.data_available=1" +
|
" and db_batt_testdata.tb_batttestdata_inf.test_type="+dto.getTestType()+" and " +
|
" db_batt_testdata.tb_batttestdata_inf.test_starttype=3 " ;
|
if(dto.getMonNum()!=0){
|
sql+=" and db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".mon_num="+dto.getMonNum()+" ";
|
}
|
if(dto.getStationName1()!=null&&!dto.getStationName1().isEmpty()){
|
sql+=" and stationname1='"+dto.getStationName1()+"' ";
|
}
|
if(dto.getStationName()!=null&&!dto.getStationName().isEmpty()){
|
sql+=" and stationname='"+dto.getStationName()+"' ";
|
}
|
sql+=" order by db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".test_record_count asc,db_batt_testdata.tb_batttestdatastop_"+dto.getBattGroupId()+".mon_num asc ";
|
List<ReportBattResultDTO> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<ReportBattResultDTO> list=new ArrayList<>();
|
while (rs.next()){
|
ReportBattResultDTO ph=new ReportBattResultDTO();
|
ph.setBattGroupId(rs.getInt("battGroupId"));
|
ph.setMonCount(rs.getInt("moncount"));
|
ph.setMonCapStd(rs.getFloat("moncapstd"));
|
ph.setMonVolStd(rs.getFloat("monvolstd"));
|
ph.setMonResStd(rs.getFloat("monresstd"));
|
ph.setMonSerStd(rs.getFloat("monserstd"));
|
ph.setFloatVolLevel(rs.getFloat("FloatVolLevel"));
|
ph.setOfflineVolLevel(rs.getFloat("OfflineVolLevel"));
|
ph.setMaxMonVol(rs.getFloat("max_monvol"));
|
ph.setMinMonVol(rs.getFloat("min_monvol"));
|
ph.setTestRecordCount(rs.getInt("test_record_count"));
|
ph.setTestStartTime(rs.getTimestamp("test_starttime"));
|
ph.setRecordTime(rs.getTimestamp("record_time"));
|
ph.setTestTimeLong(rs.getInt("test_timelong"));
|
ph.setGroupVol(rs.getFloat("group_vol"));
|
ph.setTestCurr(rs.getFloat("test_curr"));
|
ph.setTestCap(rs.getFloat("test_cap"));
|
ph.setMonNum(rs.getInt("mon_num"));
|
ph.setMonVol(rs.getFloat("mon_vol"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//49-PowerAppSysMapper.xml;12行
|
public int updateFlag(String columnName){
|
String sql=" update db_pwrdev_inf.tb_pwrapp_sys set "+columnName+"=1 limit 1 ";
|
int flag=sqlExecuteService.executeUpdate(sql,null);
|
return flag;
|
}
|
|
//52-BatttestdataInfMapper.xml;34行
|
public int judge( int battGroupId){
|
String sql="select count(table_name) as judgeNum from INFORMATION_SCHEMA.TABLES where table_name ='tb_batttestdatastop_"+battGroupId+"' and TABLE_SCHEMA='db_batt_testdata'";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("judgeNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int judgeNum =0;
|
if(list!=null){
|
judgeNum= (int) list.get(0);
|
}
|
return judgeNum;
|
}
|
|
//55-DevalarmDataHistoryMapper.xml;215行
|
public List<DevalarmDataHistory> getListByStationId_dev(Date startTime, Date endTime, String stationId,String tableName){
|
String sql="select h.*,b.StationName,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_alarm."+tableName+" h,db_battinf.tb_battinf b " +
|
" where h.dev_id = b.FBSDeviceId " ;
|
if(stationId!=null&&!stationId.isEmpty()){
|
sql+=" and b.stationId ="+stationId+" ";
|
}
|
sql+=" and h.alm_start_time >= '"+ThreadLocalUtil.format(startTime,1)+"' and h.alm_start_time <= '"+ThreadLocalUtil.format(endTime,1)+"' ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<DevalarmDataHistory> list=new ArrayList<>();
|
while (rs.next()){
|
DevalarmDataHistory ph=new DevalarmDataHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setDevId(rs.getInt("dev_id"));
|
ph.setDevIp(rs.getString("dev_ip"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setStationName(rs.getString("stationName"));
|
ph.setStationName1(rs.getString("stationName1"));
|
ph.setStationName2(rs.getString("stationName2"));
|
ph.setStationName3(rs.getString("stationName3"));
|
ph.setStationName5(rs.getString("stationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//60-PwrdevAlarmParamMapper.xml;27行
|
public int updateParam(PwrdevAlarmParam param){
|
String sql=" update db_pwrdev_alarm.tb_pwrdev_alarm_param_"+param.getPowerDeviceId()+" set alarm_limitH ="+param.getAlarmLimith()+"," +
|
" alarm_limitL ="+param.getAlarmLimitl()+",alarm_level="+param.getAlarmLevel()+
|
",UserAlarm_EN="+param.getUseralarmEn()+" where alarm_id ="+param.getAlarmId()+" ";
|
int flag=sqlExecuteService.executeUpdate(sql,null);
|
return flag;
|
}
|
|
//61-BattResDataMapper.xml;21行
|
public List serchisStandard(int battGroupId){
|
String sql=" select * from db_batt_testdata.tb_battresdata_"+battGroupId+" where isStandard=1 ";
|
List<BattResData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattResData> list=new ArrayList<>();
|
while (rs.next()){
|
BattResData bh=new BattResData();
|
bh.setNum(rs.getLong("num"));
|
bh.setBattGroupId(rs.getInt("BattGroupId"));
|
bh.setTestRecordCount(rs.getInt("test_record_count"));
|
bh.setTestType(rs.getInt("test_type"));
|
bh.setDataNew(rs.getInt("data_new"));
|
bh.setDataAvailable(rs.getInt("data_available"));
|
bh.setTestStartTime(rs.getTimestamp("test_starttime"));
|
bh.setGroupVol(rs.getFloat("group_vol"));
|
bh.setTestCurr(rs.getFloat("test_curr"));
|
bh.setMonNum(rs.getInt("mon_num"));
|
bh.setMonVol(rs.getFloat("mon_vol"));
|
bh.setMonTemp(rs.getFloat("mon_tmp"));
|
bh.setMonSer(rs.getFloat("mon_ser"));
|
bh.setMonRes(rs.getFloat("mon_res"));
|
bh.setConnRes(rs.getFloat("conn_res"));
|
list.add(bh);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//78-BattResDataMapper.xml;24行
|
public List serchCurrent( int battGroupId){
|
String sql=" select * from db_batt_testdata.tb_battresdata_"+battGroupId+" " +
|
" where test_starttime=( " +
|
" SELECT DISTINCT test_starttime from db_batt_testdata.tb_battresdata_inf where BattGroupId="+battGroupId+" order by test_starttime desc limit 1 " +
|
" )";
|
List<BattResData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattResData> list=new ArrayList<>();
|
while (rs.next()){
|
BattResData bh=new BattResData();
|
bh.setNum(rs.getLong("num"));
|
bh.setBattGroupId(rs.getInt("BattGroupId"));
|
bh.setTestRecordCount(rs.getInt("test_record_count"));
|
bh.setTestType(rs.getInt("test_type"));
|
bh.setDataNew(rs.getInt("data_new"));
|
bh.setDataAvailable(rs.getInt("data_available"));
|
bh.setTestStartTime(rs.getTimestamp("test_starttime"));
|
bh.setGroupVol(rs.getFloat("group_vol"));
|
bh.setTestCurr(rs.getFloat("test_curr"));
|
bh.setMonNum(rs.getInt("mon_num"));
|
bh.setMonVol(rs.getFloat("mon_vol"));
|
bh.setMonTemp(rs.getFloat("mon_tmp"));
|
bh.setMonSer(rs.getFloat("mon_ser"));
|
bh.setMonRes(rs.getFloat("mon_res"));
|
bh.setConnRes(rs.getFloat("conn_res"));
|
list.add(bh);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//96-BattResDataMapper.xml;18行
|
public List<BattResData> getResInfoByBattGroupId( int battGroupId){
|
String sql="select * from db_batt_testdata.tb_battresdata_"+battGroupId+" order by test_starttime desc";
|
List<BattResData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<BattResData> list=new ArrayList<>();
|
while (rs.next()){
|
BattResData bh=new BattResData();
|
bh.setNum(rs.getLong("num"));
|
bh.setBattGroupId(rs.getInt("BattGroupId"));
|
bh.setTestRecordCount(rs.getInt("test_record_count"));
|
bh.setTestType(rs.getInt("test_type"));
|
bh.setDataNew(rs.getInt("data_new"));
|
bh.setDataAvailable(rs.getInt("data_available"));
|
bh.setTestStartTime(rs.getTimestamp("test_starttime"));
|
bh.setGroupVol(rs.getFloat("group_vol"));
|
bh.setTestCurr(rs.getFloat("test_curr"));
|
bh.setMonNum(rs.getInt("mon_num"));
|
bh.setMonVol(rs.getFloat("mon_vol"));
|
bh.setMonTemp(rs.getFloat("mon_tmp"));
|
bh.setMonSer(rs.getFloat("mon_ser"));
|
bh.setMonRes(rs.getFloat("mon_res"));
|
bh.setConnRes(rs.getFloat("conn_res"));
|
list.add(bh);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
//108-BattResDataMapper.xml;4行
|
public void deleteByTestRecordCount(Integer battGroupId, Integer testRecordCount){
|
String sql="delete from db_batt_testdata.tb_battresdata_"+battGroupId+" where test_record_count = "+testRecordCount+"";
|
sqlExecuteService.execute(sql);
|
}
|
|
//93-CommonMapper.xml;160行
|
public void truncate(String dbName, String tableName){
|
String sql="TRUNCATE "+dbName+".`"+tableName+"`";
|
sqlExecuteService.execute(sql);
|
}
|
|
//107-PwrdevHistorydataGwMapper.xml;5行
|
public int judgeTable_pwr(String table){
|
String sql=" select count(*) as tableNum " +
|
" from INFORMATION_SCHEMA.TABLES " +
|
" where TABLE_SCHEMA = 'db_pwrdev_data_history_gw' " +
|
" and TABLE_NAME = 'tb_pwrdev_historydata_gw_"+table+"';";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("tableNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int tableNum =0;
|
if(list!=null){
|
tableNum= (int) list.get(0);
|
}
|
return tableNum;
|
}
|
//查询ups历史告警数量
|
public int getCountForUps(UpsAlarmDTO param) {
|
String sql="select count(distinct alarm.num) as number from db_upspwrdev_alarm."+param.getRecordYear()+" alarm,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where alarm.power_device_id = inf.PowerDeviceId ";
|
if(param.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+param.getStationName1()+"%'";
|
}
|
if(param.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+param.getStationName2()+"%'";
|
}
|
if(param.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+param.getStationName5()+"%'";
|
}
|
if(param.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+param.getStationName3()+"%'";
|
}
|
/*if(param.getPwrdevId()!=0){
|
sql+=" and alarm.power_device_id="+param.getPwrdevId();
|
}*/
|
if(param.getAlmStartTime()!=null){
|
sql+=" and alarm.alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' ";
|
}
|
if(param.getAlmEndTime()!=null){
|
sql+=" and alarm.alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' ";
|
}
|
if(param.getAlmTypes()!=null&¶m.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<param.getAlmTypes().size();i++) {
|
sql+=param.getAlmTypes().get(i);
|
if(i!=(param.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and alarm.power_device_id in ( select distinct inf.PowerDeviceId from" +
|
" (select PowerDeviceId,StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+param.getUsrId()+" )";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("number"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int num =0;
|
if(list!=null){
|
num= (int) list.get(0);
|
}
|
return num;
|
}
|
//查询ups历史告警
|
public List getList2(UpsAlarmDTO param){
|
String sql=" SELECT history.num,record_id,power_device_id,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type, " +
|
" inf.stationName,stationName1,stationName2,stationName3,stationName5,inf.PowerDeviceName as dev_name " +
|
" FROM db_upspwrdev_alarm."+param.getRecordYear()+" history,db_pwrdev_inf.tb_pwrdev_inf inf " +
|
" where history.power_device_id = inf.PowerDeviceId ";
|
if(param.getStationName1()!=null){
|
sql+=" and stationName1 like '%"+param.getStationName1()+"%'";
|
}
|
if(param.getStationName2()!=null){
|
sql+=" and stationName2 like '%"+param.getStationName2()+"%'";
|
}
|
if(param.getStationName5()!=null){
|
sql+=" and stationName5 like '%"+param.getStationName5()+"%'";
|
}
|
if(param.getStationName3()!=null){
|
sql+=" and stationName3 like '%"+param.getStationName3()+"%'";
|
}
|
if(param.getAlmTypes()!=null&¶m.getAlmTypes().size()>0){
|
sql+=" and alm_type in ( ";
|
for (int i=0;i<param.getAlmTypes().size();i++) {
|
sql+=param.getAlmTypes().get(i);
|
if(i!=(param.getAlmTypes().size()-1)){
|
sql+=",";
|
}
|
}
|
sql+=")";
|
}
|
sql+=" and alm_start_time >='"+ThreadLocalUtil.format(param.getAlmStartTime(),1)+"' and alm_start_time <='"+ThreadLocalUtil.format(param.getAlmEndTime(),1)+"' ";
|
sql+=" and history.power_device_id in ( select distinct inf.PowerDeviceId from" +
|
" (select PowerDeviceId,StationId from db_pwrdev_inf.tb_pwrdev_inf ) inf," +
|
" 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.StationId=inf.StationId" +
|
" 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="+param.getUsrId()+" ) " +
|
" ORDER BY alm_start_time desc ,dev_name asc limit "+param.getLimitStart()+","+param.getLimitEnd()+" ";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List<UpspwrdevAlarmHistory> list=new ArrayList<>();
|
while (rs.next()){
|
UpspwrdevAlarmHistory ph=new UpspwrdevAlarmHistory();
|
ph.setNum(rs.getLong("num"));
|
ph.setRecordId(rs.getLong("record_id"));
|
ph.setPowerDeviceId(rs.getLong("power_device_id"));
|
ph.setAlmType(rs.getInt("alm_type"));
|
ph.setAlmLevel(rs.getInt("alm_level"));
|
ph.setAlmStartTime(rs.getTimestamp("alm_start_time"));
|
ph.setAlmEndTime(rs.getTimestamp("alm_end_time"));
|
ph.setAlmValue(rs.getFloat("alm_value"));
|
ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
|
ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
|
ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
|
ph.setDevName(rs.getString("dev_name"));
|
ph.setStationName(rs.getString("stationName"));
|
ph.setStationName1(rs.getString("stationName1"));
|
ph.setStationName2(rs.getString("stationName2"));
|
ph.setStationName3(rs.getString("stationName3"));
|
ph.setStationName5(rs.getString("stationName5"));
|
list.add(ph);
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//ups历史告警确认
|
public int setHisAlmIsConfirmed(int num, String year) {
|
String sql=" update db_upspwrdev_alarm.tb_upspwrdev_alarm_history_"+year+" set alm_is_confirmed =1," +
|
" where num = "+num+" ";
|
int flag=sqlExecuteService.executeUpdate(sql,null);
|
return flag;
|
}
|
//ups历史告警取消
|
public int cancleHisAlmIsConfirmed(int num, String year) {
|
String sql=" update db_upspwrdev_alarm.tb_upspwrdev_alarm_history_"+year+" set alm_is_confirmed =0," +
|
" where num = "+num+" ";
|
int flag=sqlExecuteService.executeUpdate(sql,null);
|
return flag;
|
}
|
//ups历史告警删除
|
public void delHisAlm(int num, String year) {
|
String sql="delete from db_upspwrdev_alarm.tb_upspwrdev_alarm_history_"+year+" where num = "+num+"";
|
sqlExecuteService.execute(sql);
|
}
|
|
//获取总的放电电流的总值
|
public Float getSumCurr(int battGroupId, int testRecordCount) {
|
String sql=" SELECT sum(ABS(test_curr)) as sumCurr " +
|
" FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" and mon_num =1" ;
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getFloat("sumCurr"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
Float sumCurr =0f;
|
if(list!=null){
|
sumCurr= (Float) list.get(0);
|
}
|
return sumCurr;
|
}
|
//获取总的放电记录
|
public List<BatttestdataId> getAllTdata(int battGroupId, int testRecordCount) {
|
String sql=" SELECT * FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" order by record_num desc";
|
List<BatttestdataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
BatttestdataId tdata=new BatttestdataId();
|
tdata.setBattGroupId(rs.getInt("BattGroupId"));
|
tdata.setTestRecordCount(rs.getInt("test_record_count"));
|
tdata.setTestType(rs.getInt("test_type"));
|
tdata.setDataNew(rs.getInt("data_new"));
|
tdata.setDataAvailable(rs.getInt("data_available"));
|
tdata.setRecordNum(rs.getInt("record_num"));
|
tdata.setTestStarttime(rs.getTimestamp("test_starttime"));
|
tdata.setRecordTime(rs.getTimestamp("record_time"));
|
tdata.setTestTimelong(rs.getInt("test_timelong"));
|
tdata.setOnlineVol(rs.getFloat("online_vol"));
|
tdata.setGroupVol(rs.getFloat("group_vol"));
|
tdata.setTestCurr(rs.getFloat("test_curr"));
|
tdata.setTestCap(rs.getFloat("test_cap"));
|
tdata.setMonNum(rs.getInt("mon_num"));
|
tdata.setMonVol(rs.getFloat("mon_vol"));
|
tdata.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(tdata);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//删除记录
|
public void deleteData(int battGroupId, int testRecordCount, int num) {
|
String sql=" delete FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" and record_num>"+num;
|
sqlExecuteService.executeUpdate(sql,null);
|
}
|
//创建clear
|
public void creatClear(int battGroupId) {
|
String sql=" CREATE TABLE IF NOT EXISTS db_batt_testdata.tb_batttestdata_clear_"+battGroupId+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `BattGroupId` int(11) NOT NULL DEFAULT '0'," +
|
" `test_record_count` int(11) NOT NULL DEFAULT '0'," +
|
" `test_type` int(11) NOT NULL DEFAULT '0'," +
|
" `data_new` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `data_available` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `record_num` int(11) NOT NULL DEFAULT '0'," +
|
" `test_starttime` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" `record_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" `test_timelong` int(11) NOT NULL DEFAULT '0'," +
|
" `online_vol` float NOT NULL DEFAULT '0'," +
|
" `group_vol` float NOT NULL DEFAULT '0'," +
|
" `test_curr` float NOT NULL DEFAULT '0'," +
|
" `test_cap` float NOT NULL DEFAULT '0'," +
|
" `mon_num` int(11) NOT NULL DEFAULT '0'," +
|
" `mon_vol` float NOT NULL DEFAULT '0'," +
|
" `mon_tmp` float NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_test_record_count` (`test_record_count`)" +
|
")";
|
sqlExecuteService.execute(sql);
|
}
|
//先存入clear表
|
public void insertClear(int battGroupId, int testRecordCount,int recordNum) {
|
//判断表是否存在
|
|
String sql = "INSERT INTO db_batt_testdata.tb_batttestdata_clear_" + battGroupId
|
+ "(BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp) "
|
+ "(SELECT BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp from db_batt_testdata.tb_batttestdata_" + battGroupId
|
+ " WHERE test_record_count = " +testRecordCount
|
+ " and record_num > " + recordNum
|
+ ")";
|
sqlExecuteService.executeUpdate(sql,null);
|
}
|
//获取需要恢复的放电记录
|
public List<BatttestdataClear> getClearTdata(int battGroupId, int testRecordCount) {
|
String sql=" SELECT * " +
|
" FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" ";
|
List<BatttestdataClear> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
BatttestdataClear clear=new BatttestdataClear();
|
clear.setBattGroupId(rs.getInt("BattGroupId"));
|
clear.setTestRecordCount(rs.getInt("test_record_count"));
|
clear.setTestType(rs.getInt("test_type"));
|
clear.setDataNew(rs.getInt("data_new"));
|
clear.setDataAvailable(rs.getInt(""));
|
clear.setRecordNum(rs.getInt("record_num"));
|
clear.setTestStarttime(rs.getTimestamp("test_starttime"));
|
clear.setRecordTime(rs.getTimestamp("record_time"));
|
clear.setTestTimelong(rs.getInt("test_timelong"));
|
clear.setOnlineVol(rs.getFloat("online_vol"));
|
clear.setGroupVol(rs.getFloat("group_vol"));
|
clear.setTestCurr(rs.getFloat("test_curr"));
|
clear.setTestCap(rs.getFloat("test_cap"));
|
clear.setMonNum(rs.getInt("mon_num"));
|
clear.setMonVol(rs.getFloat("mon_vol"));
|
clear.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(clear);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
|
public void insertRecover(int battGroupId, int testRecordCount) {
|
String sql = "INSERT INTO db_batt_testdata.tb_batttestdata_" + battGroupId
|
+ "(BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp) "
|
+ "(SELECT BattGroupId,test_record_count,test_type,data_new,data_available,record_num,test_starttime,record_time,test_timelong,online_vol,group_vol,test_curr,test_cap,mon_num,mon_vol,mon_tmp from db_batt_testdata.tb_batttestdata_clear_" + battGroupId
|
+ " WHERE test_record_count = " +testRecordCount
|
+ ")";
|
sqlExecuteService.executeUpdate(sql,null);
|
}
|
|
public void deleteRecover(int battGroupId, int testRecordCount) {
|
String sql=" delete FROM db_batt_testdata.tb_batttestdata_clear_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount;
|
sqlExecuteService.executeUpdate(sql,null);
|
}
|
//先查询出所有的记录
|
public List<BatttestdataClear> getRecoveInf(int battGroupId, int testRecordCount) {
|
String sql=" SELECT distinct battGroupId,test_record_count,record_num FROM db_batt_testdata.tb_batttestdata_clear_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" order by record_num asc";
|
List<BatttestdataClear> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
BatttestdataClear tclear=new BatttestdataClear();
|
tclear.setBattGroupId(rs.getInt("BattGroupId"));
|
tclear.setTestRecordCount(rs.getInt("test_record_count"));
|
tclear.setRecordNum(rs.getInt("record_num"));
|
list.add(tclear);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//查出需要预估的数据
|
public List<AnaysisData> getAnaysisData(int battGroupId, int testRecordCount) {
|
String sql=" SELECT distinct record_num,test_timelong,group_vol,test_cap,mon_num,mon_vol,test_curr FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" order by record_num asc,mon_num asc ";
|
List<AnaysisData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
List<Float> monVols=null;
|
List<Integer> monNums=null;
|
AnaysisData data=null;
|
int num=0;
|
try {
|
while (rs.next()){
|
int monNum=rs.getInt("mon_num");
|
float monvol=rs.getFloat("mon_vol");
|
int recordNum=rs.getInt("record_num");
|
if(num!=recordNum){
|
if(num!=0){
|
data.setMonVols(monVols);
|
data.setMonNums(monNums);
|
list.add(data);
|
}
|
data=new AnaysisData();
|
monVols=new ArrayList<>();
|
monNums=new ArrayList<>();
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
data.setRecordTime(ActionUtil.secToTime(data.getTestTimelong()));
|
data.setGroupVol(rs.getFloat("group_vol"));
|
data.setTestCap(rs.getFloat("test_cap"));
|
data.setGroupCurr(rs.getFloat("test_curr"));
|
data.setRecordNum(recordNum);
|
num=recordNum;
|
}
|
monVols.add(monvol);
|
monNums.add(monNum);
|
}
|
data.setMonVols(monVols);
|
data.setMonNums(monNums);
|
list.add(data);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//将解析出的数据存入数据库表
|
public void storeAnaysisToSqlWithNum(int battGroupId,int testRecordCount, List<AnaylsisId> idList) {
|
ArrayList sql_str=new ArrayList();
|
String tableName="db_analysis."+"tb_analysis_"+battGroupId;
|
//检测表是否存在
|
if(idList!=null){
|
String insertSql="insert into "+tableName+" "
|
+ "("
|
+ "test_record_count,"
|
+ "record_num,"
|
+ "record_time,"
|
+ "mon_num,"
|
+ "mon_vol,"
|
+ "flag"
|
+ ") "
|
+ " values ";
|
for (int i=0;i<idList.size();i++) {
|
AnaylsisId ays=idList.get(i);
|
if(i>0){
|
insertSql+=" , ";
|
}
|
insertSql=insertSql
|
+ "("
|
+ ays.getTestRecordCount()+","
|
+ ays.getRecordNum()+","
|
+ "'"+ays.getRecordTime()+"',"
|
+ ays.getMonNum()+","
|
+ ays.getMonVol()+","
|
+ 1+""
|
+ ")";//如果对应是字符几个带上单引号
|
}
|
sql_str.add(insertSql);
|
}
|
sqlExecuteService.makeManualCommit(sql_str);
|
}
|
//将解析的组端电压修改到数据库
|
public void updateAnaysisToSqlWithNum(int battGroupId, int testRecordCount, List<AnaylsisId> groupVolList) {
|
ArrayList sql_str=new ArrayList();
|
String tableName="db_analysis."+"tb_analysis_"+battGroupId;
|
if(groupVolList!=null){
|
for ( AnaylsisId ays:groupVolList) {
|
String updateSql="update "+tableName+" set group_vol="+ays.getGroupVol()+" where test_record_count="+testRecordCount+" and record_num="+ays.getRecordNum();
|
sql_str.add(updateSql);
|
}
|
}
|
sqlExecuteService.makeManualCommit(sql_str);
|
}
|
|
//验证分析表是否存在
|
public int judgeTable_anaysis(int battGroupId, int testRecordCount) {
|
String sql="select count(*) as tableNum " +
|
" from INFORMATION_SCHEMA.TABLES " +
|
" where TABLE_SCHEMA = 'db_analysis' " +
|
" and TABLE_NAME = 'tb_analysis_"+battGroupId+"'";
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("tableNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int tableNum =0;
|
if(list!=null){
|
tableNum= (int) list.get(0);
|
}
|
return tableNum;
|
}
|
//检测表是存在后testRecordCount数据是否存在
|
public int getAnaysisIdByTestRecordCount(int battGroupId, int testRecordCount) {
|
String sql=" SELECT count(*) as sum FROM db_analysis.tb_analysis_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount;
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("sum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int sum =0;
|
if(list!=null){
|
sum= (int) list.get(0);
|
}
|
return sum;
|
}
|
//检测书否存在补充的数据
|
public int getAnaysisSupplement(int battGroupId, int testRecordCount) {
|
String sql=" SELECT count(*) as supple FROM db_analysis.tb_analysis_"+battGroupId+
|
" WHERE flag=0 and test_record_count = " +testRecordCount;
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("supple"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int supple =0;
|
if(list!=null){
|
supple= (int) list.get(0);
|
}
|
return supple;
|
}
|
|
//检测书否存在补充的数据
|
public int getAnaysisNum(int battGroupId, int testRecordCount) {
|
String sql=" SELECT count(DISTINCT mon_num) as anaNum FROM db_analysis.tb_analysis_"+battGroupId+
|
" WHERE flag=1 and test_record_count = " +testRecordCount;
|
List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
LinkedList<Object> temp = new LinkedList<>();
|
try {
|
while (rs.next())
|
temp.add(rs.getInt("anaNum"));
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return temp;
|
}
|
});
|
int anaNum =0;
|
if(list!=null){
|
anaNum= (int) list.get(0);
|
}
|
return anaNum;
|
}
|
|
|
//创建clear
|
public void creatAnasys(int battGroupId) {
|
String sql=" CREATE TABLE IF NOT EXISTS db_analysis.tb_analysis_"+battGroupId+" (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `test_record_count` int(11) NOT NULL DEFAULT '0'," +
|
" `record_num` int not NULL DEFAULT 0," +
|
" `record_time` varchar(64) not NULL DEFAULT '00:00:00'," +
|
" `group_vol` float not NULL DEFAULT 0," +
|
" `mon_num` int not NULL DEFAULT 0," +
|
" `mon_vol` float not NULL DEFAULT 0," +
|
" `flag` int not NULL DEFAULT 0," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 ";
|
sqlExecuteService.execute(sql);
|
}
|
//查询预估数据
|
public List<AnaylsisId> getAnaysisXls(int battGroupId, int testRecordCount) {
|
String sql=" SELECT distinct num,test_record_count,record_num,record_time,group_vol,mon_vol,mon_num,flag FROM db_analysis.tb_analysis_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" order by test_record_count asc ,record_num asc ,mon_num ASC ";
|
List<AnaylsisId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
AnaylsisId idData=new AnaylsisId();
|
idData.setNum(rs.getInt("num"));
|
idData.setTestRecordCount(rs.getInt("test_record_count"));
|
idData.setRecordNum(rs.getInt("record_num"));
|
idData.setRecordTime(rs.getString("record_time"));
|
idData.setGroupVol(rs.getFloat("group_vol"));
|
idData.setMonNum(rs.getInt("mon_num"));
|
idData.setMonVol(rs.getFloat("mon_vol"));
|
idData.setFlag(rs.getInt("flag"));
|
list.add(idData);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//补前面的数据
|
public void insertBeforeData(int battGroupId, int testRecordCount, List<AnaysisData> list) {
|
ArrayList sql_str=new ArrayList();
|
String tableName="db_analysis."+"tb_analysis_"+battGroupId;
|
//检测表是否存在
|
if(list!=null){
|
String insertSql="insert into "+tableName+" "
|
+ "("
|
+ "test_record_count,"
|
+ "record_num,"
|
+ "record_time,"
|
+ "group_vol,"
|
+ "mon_num,"
|
+ "mon_vol,"
|
+ "flag"
|
+ ") "
|
+ " values ";
|
for (int i=0;i<list.size();i++) {
|
AnaysisData ays=list.get(i);
|
List monVols=ays.getMonVols();
|
for (int j=0;j<monVols.size();j++) {
|
if(!(i==0&&j==0)){
|
insertSql+=" , ";
|
}
|
String volSql="("
|
+ testRecordCount+","
|
+ ays.getRecordNum()+","
|
+ "'"+ays.getRecordTime()+"',"
|
+ ays.getGroupVol()+","
|
+ (j+1)+","
|
+ monVols.get(j)+","
|
+ 0+""
|
+ ")";//如果对应是字符几个带上单引号
|
insertSql+=volSql;
|
}
|
}
|
sql_str.add(insertSql);
|
}
|
sqlExecuteService.makeManualCommit(sql_str);
|
}
|
//2.将数据插入电池组数据
|
public void insertAnaysisXlsToTdata(int battGroupId, int testRecordCount, List<XlsToTdata> list) {
|
ArrayList sql_str=new ArrayList();
|
String tableName="db_batt_testdata."+"tb_batttestdata_"+battGroupId;
|
//检测表是否存在
|
if(list!=null){
|
String insertSql="insert into "+tableName+" "
|
+ "("
|
+ "BattGroupId,"
|
+ "test_record_count,"
|
+ "record_num,"
|
+ "record_time,"
|
+ "test_starttime,"
|
+ "test_timelong,"
|
+ "group_vol,"
|
+ "test_curr,"
|
+ "test_cap,"
|
+ "mon_num,"
|
+ "mon_vol,"
|
+ "test_type,"
|
+ "data_new,"
|
+ "data_available"
|
+ ") "
|
+ " values ";
|
for (int i=0;i<list.size();i++) {
|
XlsToTdata tdata=list.get(i);
|
List monVols=tdata.getMonVols();
|
for (int j=0;j<monVols.size();j++) {
|
if(!(i==0&&j==0)){
|
insertSql+=" , ";
|
}
|
String volSql="("
|
+ battGroupId+","
|
+ testRecordCount+","
|
+ (i+1)+","
|
+ "'"+tdata.getRecordTime()+"',"
|
+ "'"+ActionUtil.sdf.format(new Date())+"',"
|
+ tdata.getTestTimelong()+","
|
+ tdata.getGroupVol()+","
|
+ tdata.getGroupCurr()+","
|
+ tdata.getTestCap()+","
|
+ (j+1)+","
|
+ monVols.get(j)+","
|
+ 3+","
|
+ 1+","
|
+ 1+""
|
+ ")";//如果对应是字符几个带上单引号
|
insertSql+=volSql;
|
}
|
}
|
sql_str.add(insertSql);
|
}
|
sqlExecuteService.makeManualCommit(sql_str);
|
}
|
|
//预估
|
public List<PredictDataId> getPredictDataId(int battGroupId, int testRecordCount) {
|
String sql=" SELECT distinct * FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+
|
" order by record_num asc,mon_num asc ";
|
List<PredictDataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
PredictDataId data=new PredictDataId();
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestCap(rs.getFloat("test_type"));
|
data.setDataNew(rs.getInt("data_new"));
|
data.setDataAvailable(rs.getInt("data_available"));
|
data.setRecordNum(rs.getInt("record_num"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
data.setOnlineVol(rs.getFloat("online_vol"));
|
data.setGroupVol(rs.getFloat("group_vol"));
|
data.setTestCurr(rs.getFloat("test_curr"));
|
data.setTestCap(rs.getFloat("test_cap"));
|
data.setMonNum(rs.getInt("mon_num"));
|
data.setMonVol(rs.getFloat("mon_vol"));
|
data.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//2.取出最后一笔数据
|
public List<PredictDataId> getPredictDataIdLast(int battGroupId, int testRecordCount, int maxRecordNum) {
|
String sql=" SELECT distinct * FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+" and record_num="+maxRecordNum+
|
" order by record_num asc,mon_num asc ";
|
List<PredictDataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
PredictDataId data=new PredictDataId();
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestType(rs.getInt("test_type"));
|
data.setDataNew(rs.getInt("data_new"));
|
data.setDataAvailable(rs.getInt("data_available"));
|
data.setRecordNum(rs.getInt("record_num"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
data.setOnlineVol(rs.getFloat("online_vol"));
|
data.setGroupVol(rs.getFloat("group_vol"));
|
data.setTestCurr(rs.getFloat("test_curr"));
|
data.setTestCap(rs.getFloat("test_cap"));
|
data.setMonNum(rs.getInt("mon_num"));
|
data.setMonVol(rs.getFloat("mon_vol"));
|
data.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//3.取出预估对象的maxRecordNum之后的数据
|
public List<PredictDataId> getPredictDataIdAgain(int battGroupId, int testRecordCount, int maxRecordNum) {
|
String sql=" SELECT distinct * FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
|
" WHERE test_record_count = " +testRecordCount+" and record_num>="+maxRecordNum+
|
" order by record_num asc,mon_num asc ";
|
List<PredictDataId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
PredictDataId data=new PredictDataId();
|
data.setBattGroupId(rs.getInt("BattGroupId"));
|
data.setTestRecordCount(rs.getInt("test_record_count"));
|
data.setTestType(rs.getInt("test_type"));
|
data.setDataNew(rs.getInt("data_new"));
|
data.setDataAvailable(rs.getInt("data_available"));
|
data.setRecordNum(rs.getInt("record_num"));
|
data.setTestStarttime(rs.getTimestamp("test_starttime"));
|
data.setRecordTime(rs.getTimestamp("record_time"));
|
data.setTestTimelong(rs.getInt("test_timelong"));
|
data.setOnlineVol(rs.getFloat("online_vol"));
|
data.setGroupVol(rs.getFloat("group_vol"));
|
data.setTestCurr(rs.getFloat("test_curr"));
|
data.setTestCap(rs.getFloat("test_cap"));
|
data.setMonNum(rs.getInt("mon_num"));
|
data.setMonVol(rs.getFloat("mon_vol"));
|
data.setMonTmp(rs.getFloat("mon_tmp"));
|
list.add(data);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
return list;
|
}
|
//将数据插入id表
|
public void addPredictDataId(int battGroupId, int testRecordCount2, List<PredictDataId> list2, List<PredictDataId> list1, Map<Integer,PredictDataId> mapDifference) {
|
ArrayList sql_str=new ArrayList();
|
String tableName="db_batt_testdata."+"tb_batttestdata_"+battGroupId;
|
//先插入2
|
if(list2!=null){
|
String insertSql2="insert into "+tableName+" "
|
+ "("
|
+ "BattGroupId,"
|
+ "test_record_count,"
|
+ "test_type,"
|
+ "data_new,"
|
+ "data_available,"
|
+ "record_num,"
|
+ "test_starttime,"
|
+ "record_time,"
|
+ "test_timelong,"
|
+ "online_vol,"
|
+ "group_vol,"
|
+ "test_curr,"
|
+ "test_cap,"
|
+ "mon_num,"
|
+ "mon_vol,"
|
+ "mon_tmp"
|
+ ") "
|
+ " values ";
|
for (int i=0;i<list2.size();i++) {
|
PredictDataId data2=list2.get(i);
|
String sql2="("
|
+battGroupId+","
|
+ data2.getTestRecordCount()+","
|
+ data2.getTestType()+","
|
+ data2.getDataNew()+","
|
+ data2.getDataAvailable()+","
|
+ data2.getRecordNum()+1+","
|
+ "'"+ActionUtil.sdf.format(data2.getTestStarttime())+"',"
|
+ "'"+ActionUtil.sdf.format(data2.getRecordTime())+"',"
|
+ data2.getTestTimelong()+","
|
+ data2.getOnlineVol()+","
|
+ data2.getGroupVol()+","
|
+ data2.getTestCurr()+","
|
+ data2.getTestCap()+","
|
+ data2.getMonNum()+","
|
+ data2.getMonVol()+","
|
+ data2.getMonTmp()+""
|
+ ")";//如果对应是字符几个带上单引号
|
insertSql2+=sql2;
|
}
|
sql_str.add(insertSql2);
|
}
|
//再插入1
|
if(list1!=null){
|
String insertSql1="insert into "+tableName+" "
|
+ "("
|
+ "BattGroupId,"
|
+ "test_record_count,"
|
+ "test_type,"
|
+ "data_new,"
|
+ "data_available,"
|
+ "record_num,"
|
+ "test_starttime,"
|
+ "record_time,"
|
+ "test_timelong,"
|
+ "online_vol,"
|
+ "group_vol,"
|
+ "test_curr,"
|
+ "test_cap,"
|
+ "mon_num,"
|
+ "mon_vol,"
|
+ "mon_tmp"
|
+ ") "
|
+ " values ";
|
for (int i=0;i<list1.size();i++) {
|
PredictDataId data1=list1.get(i);
|
String sql1="("
|
+battGroupId+","
|
+ data1.getTestRecordCount()+","
|
+ data1.getTestType()+","
|
+ data1.getDataNew()+","
|
+ data1.getDataAvailable()+","
|
+ data1.getRecordNum()+","
|
+ "'"+ActionUtil.sdf.format(data1.getTestStarttime())+"',"
|
+ "'"+ActionUtil.sdf.format(data1.getRecordTime())+"',"
|
+ data1.getTestTimelong()+mapDifference.get(data1.getMonNum()).getTestTimelong()+","
|
+ data1.getOnlineVol()+","
|
+ data1.getGroupVol()+","
|
+ data1.getTestCurr()+mapDifference.get(data1.getMonNum()).getTestCurr()+","
|
+ data1.getTestCap()+mapDifference.get(data1.getMonNum()).getTestCap()+","
|
+ data1.getMonNum()+","
|
+ data1.getMonVol()+mapDifference.get(data1.getMonNum()).getMonVol()+","
|
+ data1.getMonTmp()+mapDifference.get(data1.getMonNum()).getMonTmp()+""
|
+ ")";//如果对应是字符几个带上单引号
|
insertSql1+=sql1;
|
}
|
sql_str.add(insertSql1);
|
}
|
sqlExecuteService.makeManualCommit(sql_str);
|
}
|
//获取当前天平均负载电流
|
public Float getAvgLoadCurr(Integer powerId, String tableName) {
|
String sql=" select avg(load_curr) as avgCurr from "+tableName +" where DATE(record_time) = CURDATE() ";
|
List<Float> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) throws SQLException {
|
List list=new ArrayList<>();
|
try {
|
while (rs.next()){
|
list.add(rs.getFloat("avgCurr"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
float avgCurr=0f;
|
if (list!=null){
|
avgCurr=list.get(0);
|
}
|
return avgCurr;
|
}
|
}
|
