@ApiOperation(value = "直属线路变电数量", notes = "")
@RequestMapping(value = {"/getEchartsZSXLBDSLData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "直属线路变电数量")
public ResultMsg getEchartsZSXLBDSLData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsZSXLBDSLData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "电网运行事件统计", notes = "")
@RequestMapping(value = {"/getEchartsDWYXSJTJData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "电网运行事件统计")
public ResultMsg getEchartsDWYXSJTJData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsDWYXSJTJData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "线路长度", notes = "")
@RequestMapping(value = {"/getEchartsXLCDData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "线路长度")
public ResultMsg getEchartsXLCDData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsXLCDData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "电网运行事件分布情况", notes = "")
@RequestMapping(value = {"/getEchartsDWYXSJFBQKData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "电网运行事件分布情况")
public ResultMsg getEchartsDWYXSJFBQKData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsDWYXSJFBQKData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "变压器,高抗,隔离开关数量", notes = "")
@RequestMapping(value = {"/getEchartsBYQGKGLKGSLData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "变压器,高抗,隔离开关数量")
public ResultMsg getEchartsBYQGKGLKGSLData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsBYQGKGLKGSLData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "设备服役年限", notes = "")
@RequestMapping(value = {"/getEchartsSBFYNXData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "设备服役年限")
public ResultMsg getEchartsSBFYNXData(HttpServletRequest request ) {
JSONObject json = substationDao.getEchartsSBFYNXData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "三跨隐患检修情况", notes = "")
@RequestMapping(value = {"/getEchartsSKYHJXData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "三跨隐患检修情况")
public ResultMsg getEchartsSKYHJXData(HttpServletRequest request) {
JSONObject json = substationDao.getEchartsSKYHJXData(request);
return new ResultMsgFactory().produce(json);
}
@ApiOperation(value = "获取地图统计数据", notes = "")
@RequestMapping(value = {"/getDTData"}, method = RequestMethod.POST)
@Log(operationType = "查询", operationName = "获取地图统计数据")
public ResultMsg getDTData(HttpServletRequest request) {
JSONObject json = substationDao.getDTData(request);
return new ResultMsgFactory().produce(json);
}
package com.hdkj.zszc.repository.dao;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import com.couchbase.client.deps.io.netty.util.internal.StringUtil;
import com.hdkj.zszc.dao.SubstationtypeDao;
import com.hdkj.zszc.entity.FileUpload;
import com.hdkj.zszc.entity.Substation;
import com.hdkj.zszc.entity.SubstationDevice;
import com.hdkj.zszc.entity.Substationtype;
import com.hdkj.zszc.repository.rowMapper.SubstationRowMapper;
import com.hdkj.zszc.repository.rowMapper.SubstationdeviceRowMapper;
import freemarker.template.Configuration;
import freemarker.template.ObjectWrapper;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import safe_com.Utils.MD5;
import sun.misc.BASE64Encoder;
/**
* 变电站Dao
* @author zyf
*
*/
@Transactional
@Service("substationDao")
public class SubstationDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private LineinfoDao lineinfoDao;
@Autowired
SubstationtypeDao substationtypeDao;
private Configuration configuration = null;
/**
* 查询变电站概况
*/
public JSONObject getBasic(int pageNumber,int pageSize,Substation substation,String deliveryDates,String ageLimit,String eventSum){
JSONObject json = new JSONObject();
String sql="select a.*,(a.safesum+a.qualitysum+a.qualityproblemsum+a.problemsum+a.problemqualitysum+a.electricexception+a.mountionsum+a.thundersum+a.icingsum+a.dancingsum) sjsl from ( "
+ "SELECT sub.*,(SELECT COUNT(*) FROM CCB_SAFE_EVENT safe WHERE safe.substation_line=sub.substation_name )safesum,"
+ "(SELECT COUNT(*) FROM CCB_QUALITY_EVENT quality WHERE quality.substation_line=sub.substation_name ) qualitysum,"
+ "(SELECT count(*) FROM CCB_QUALITY_EVENT quality,CCB_PROBLEM_DEVICE b WHERE quality.substation_line=sub.substation_name and quality.supplies_name=b.device_name and quality.supplier=b.supplier)qualityproblemsum,"
+ "(SELECT COUNT(*) FROM CCB_PROBLEM_DEVICE prm WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier ) problemsum,"
+ "(SELECT count(*) FROM CCB_PROBLEM_DEVICE prm,CCB_QUALITY_EVENT d WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier and prm.device_model=d.supplies_type and prm.supplier=d.supplier)problemqualitysum,"
+ "(SELECT COUNT(*) FROM CCB_ELECTRIC_EXCEPTION electric WHERE electric.line_name=sub.substation_name )electricexception, "
+ "(select count(*) from CCB_MOUNTAIN_FIRE mountain where mountain.Line_Name=sub.substation_name)mountionsum,"
+ "(select count(*) from CCB_THUNDER thunder where thunder.line_name=sub.substation_name)thundersum,"
+ "(select count(*) from CCB_ICING icing where icing.Line_Name=sub.substation_name)icingsum, "
+ "(select count(*) from CCB_DANCING_EVENT dinicing where dinicing.Line_Name=sub.substation_name)dancingsum "
+ " FROM CCB_SUBSTATION sub where 1=1";
if(substation.getSubstationName()!=null&&!substation.getSubstationName().equals("")){
sql+=" and SUBSTATION_NAME like '%" + substation.getSubstationName() + "%'";
}
if(substation.getNationalassets()!=null&&!substation.getNationalassets().equals("")){
sql+=" and nationalassets ='"+substation.getNationalassets()+ "'";
}
if(substation.getArea()!=null&&!substation.getArea().equals("")){
sql+=" and AREA ='" + substation.getArea() + "'";
}
if(deliveryDates!=null&&!deliveryDates.equals("")){
sql+="and substr(DELIVERY_DATE,0,4)='"+ deliveryDates+"'";
}
if(substation.getSubstationId()!=null&&!substation.getSubstationId().equals("")){
sql+="and SUBSTATION_ID='"+ substation.getSubstationId()+"'";
}
if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("10")){
lowage = "0";
}else if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}else if(ageLimit.equals("40")){
lowage = "30";
}else if(ageLimit.equals("50")){
lowage = "40";
}else if(ageLimit.equals("60")){
lowage = "50";
}
sql+=" and months_between(sysdate,to_date(SUBSTR(trim(DELIVERY_DATE),1,10),'yyyy-MM-dd'))/12 <='"+ ageLimit+"' ";
sql+="and months_between(sysdate,to_date(SUBSTR(trim(DELIVERY_DATE),1,10),'yyyy-MM-dd'))/12 >'"+ lowage+"' ";
/* sql+="and (to_char(sysdate,'yyyy') - to_char(DELIVERY_DATE,'yyyy')) <='"+ ageLimit+"'";
sql+="and (to_char(sysdate,'yyyy')-to_char(DELIVERY_DATE,'yyyy')) >'"+ lowage+"'";*/
}
sql += " ) a ";
if(eventSum != null && !eventSum.equals("")){
if(eventSum.equals("1")){
sql+=" order by sjsl asc";
}else if(eventSum.equals("2")){
sql+=" order by sjsl desc";
}
}
String pageSql = "SELECT C.* FROM (SELECT A.*, ROWNUM RN FROM ("
+ sql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
int firstPage=pageNumber*pageSize;
int lastPage=(pageNumber - 1) * pageSize + 1;
List<Substation> sub = jdbcTemplate.query(pageSql, new Object[]{firstPage,lastPage}, new SubstationRowMapper());
String sqlsum="select count(*) from (" + sql + ") ";
//获取总条数
int total = jdbcTemplate.queryForObject(sqlsum, Integer.class);
json.put("total", total);
json.put("rows", sub);
return json;
}
/**
* 查询有绑定坐标的变电站
*/
public List<Substation> haveXy(){
String sql="select * from ccb_substation ";
return jdbcTemplate.query(sql,new SubstationRowMapper());
}
/**
* 根据ID查询变电站
*/
public List<Substation> idgetBasic(String id){
String sql="select SUBSTATION_NAME,COORDINATEX,COORDINATEY from ccb_substation where SUBSTATION_ID=?";
return jdbcTemplate.query(sql,new Object[]{id},new SubstationRowMapper());
}
/**
* 根据名称查询变电站
*/
public List<Substation> findStationByName(String name){
String sql="select COORDINATEX,COORDINATEY from ccb_substation where SUBSTATION_NAME like '"+name+"'";
return jdbcTemplate.query(sql,new SubstationRowMapper());
}
/**
* 查询变电站各种设备
*/
public JSONObject getDevice(SubstationDevice device,int pageNumber,int pageSize,String deliveryDates,String ageLimit,String flag,String registerTime,String month,String area){
JSONObject json = new JSONObject();
String sql="SELECT dev.*,(SELECT sub.SUBSTATION_NAME FROM CCB_SUBSTATION sub WHERE "
+ " sub.SUBSTATION_ID=dev.SUBSTATION_ID) SUBSTATION_NAME "+
" FROM CCB_SUBSTATION_DEVICE dev WHERE 1=1 ";
if(device.getDeviceName()!=null&&!device.getDeviceName().equals("")){
sql+=" and dev.DEVICE_NAME like '%" +device.getDeviceName()+ "%'";
}
if(device.getSubstationId()!=null&&!device.getSubstationId().equals("")){
sql+=" and dev.SUBSTATION_ID='"+ device.getSubstationId() +"'";
}
if(device.getDeviceType()!=null&&!device.getDeviceType().equals("")){
sql+=" and dev.DEVICE_TYPE='"+ device.getDeviceType() +"'";
}
if(deliveryDates!=null&&!"".equals(deliveryDates)){
sql+=" and substr(dev.DELIVERY_DATE,0,4) ='"+ deliveryDates+"' ";
}
if(registerTime!=null&&!"".equals(registerTime)){
sql+=" and substr(dev.REGISTER_TIME,0,4) ='"+ registerTime+"' ";
}
if(month != null && !"".equals(month)){
if(month.length() == 0){
month = "0"+month;
}
sql += " and substr(dev.REGISTER_TIME, 6,2) = '"+month+"' ";
}
if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("10")){
lowage = "0";
}else if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}else if(ageLimit.equals("40")){
lowage = "30";
}else if(ageLimit.equals("50")){
lowage = "40";
}else if(ageLimit.equals("60")){
lowage = "50";
}
sql+="and months_between(sysdate,to_date(SUBSTR(trim(DELIVERY_DATE),1,10),'yyyy-MM-dd'))/12 <='"+ ageLimit+"' ";
sql+="and months_between(sysdate,to_date(SUBSTR(trim(DELIVERY_DATE),1,10),'yyyy-MM-dd'))/12 >'"+ lowage+"' ";
}
String pageSql = "SELECT C.* FROM (SELECT A.*, ROWNUM RN FROM ("
+ sql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
int firstPage=pageNumber*pageSize;
int lastPage=(pageNumber - 1) * pageSize + 1;
List<SubstationDevice> dev = jdbcTemplate.query(pageSql, new Object[]{firstPage,lastPage}, new SubstationdeviceRowMapper());
json.put("rows", dev);
String sqlsum="select count(*) from ("+ sql +") ";
//获取总条数
int total = jdbcTemplate.queryForObject(sqlsum,new Object[]{}, Integer.class);
json.put("total", total);
return json;
}
/**
* 查询变电站各种设备
*/
public JSONObject getDevice3(int pageNumber, int pageSize,String stationtype,String voltagelevel,String time,String flag){
if("主变压器".equals(stationtype)){
stationtype = "byq";
}else if("母线".equals(stationtype)){
stationtype = "mx";
}else if("电抗器".equals(stationtype)){
stationtype = "dkq";
}else if("电流互感器".equals(stationtype)){
stationtype = "dlhgq";
}else if("电压互感器".equals(stationtype)){
stationtype = "dyhgq";
}else if("断路器".equals(stationtype)){
stationtype = "dlq";
}else if("隔离开关".equals(stationtype)){
stationtype = "glkg";
}else if("避雷器".equals(stationtype)){
stationtype = "blq";
}else if("继电保护及安全自动装置".equals(stationtype)){
stationtype = "jdbh";
}else if("电容器".equals(stationtype)){
stationtype = "drq";
}else if("开关柜".equals(stationtype)){
stationtype = "kgg";
}else if("阻波器".equals(stationtype)){
stationtype = "zbq";
}else if("组合电器(GIS)".equals(stationtype)){
stationtype = "gis";
}else if("耦合电容器".equals(stationtype)){
stationtype = "ohdrq";
}else if("远动通信".equals(stationtype)){
stationtype = "ydtx";
}else{
stationtype = "";
}
if("220kV".equals(voltagelevel)){
voltagelevel = "16";
}else if("500kV".equals(voltagelevel)){
voltagelevel = "08";
}
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
Date oneYearOld=null;//1年
Date twoYearOld=null;//2年
Date fiveYearOld=null;//5年
Date sixYearOld=null;//6年
Date tenYearOld=null;//10年
Date elevenYearOld=null;//11年
Date elevenFiveYearOld=null;//15年
Date tenSixYearOld=null;//16年
Date twentyYearOld=null;//20年
Date twentyFiveYearOld=null;//25年
Date thirtyYearOld=null;//30年
Date fortyYearOld=null;//40年
Date date7 = null;
Date date12 = null;
Date date13 = null;
Date date14 = null;
Date date16 = null;
if(!StringUtil.isNullOrEmpty(time)) {
try {
Calendar b = Calendar.getInstance();
b.setTime(sdf.parse(time));
b.add(Calendar.YEAR, -1);
//c.add(Calendar.DAY_OF_MONTH, 1);
oneYearOld=b.getTime();
Calendar c = Calendar.getInstance();
c.setTime(sdf.parse(time));
c.add(Calendar.YEAR, -2);
twoYearOld=c.getTime();
Calendar d = Calendar.getInstance();
d.setTime(sdf.parse(time));
d.add(Calendar.YEAR, -5);
fiveYearOld=d.getTime();
Calendar g = Calendar.getInstance();
g.setTime(sdf.parse(time));
g.add(Calendar.YEAR, -6);
//g.add(Calendar.DAY_OF_MONTH, 1);
sixYearOld=g.getTime();
Calendar e = Calendar.getInstance();
e.setTime(sdf.parse(time));
e.add(Calendar.YEAR, -10);
tenYearOld=e.getTime();
Calendar f = Calendar.getInstance();
f.setTime(sdf.parse(time));
f.add(Calendar.YEAR, -11);
//e.add(Calendar.DAY_OF_MONTH, 1);
elevenYearOld=f.getTime();
Calendar h = Calendar.getInstance();
h.setTime(sdf.parse(time));
h.add(Calendar.YEAR, -15);
elevenFiveYearOld=h.getTime();
Calendar i = Calendar.getInstance();
i.setTime(sdf.parse(time));
i.add(Calendar.YEAR, -16);
//e.add(Calendar.DAY_OF_MONTH, 1);
tenSixYearOld=i.getTime();
Calendar i2 = Calendar.getInstance();
i2.setTime(sdf.parse(time));
i2.add(Calendar.YEAR, -20);
//e.add(Calendar.DAY_OF_MONTH, 1);
twentyYearOld=i2.getTime();
Calendar i3 = Calendar.getInstance();
i3.setTime(sdf.parse(time));
i3.add(Calendar.YEAR, -25);
//e.add(Calendar.DAY_OF_MONTH, 1);
twentyFiveYearOld=i3.getTime();
Calendar i4 = Calendar.getInstance();
i4.setTime(sdf.parse(time));
i4.add(Calendar.YEAR, -30);
//e.add(Calendar.DAY_OF_MONTH, 1);
thirtyYearOld=i4.getTime();
Calendar j = Calendar.getInstance();
j.setTime(sdf.parse(time));
j.add(Calendar.YEAR, -40);
//e.add(Calendar.DAY_OF_MONTH, 1);
fortyYearOld=j.getTime();
Calendar k = Calendar.getInstance();
k.setTime(sdf.parse(time));
k.add(Calendar.YEAR, -7);
//e.add(Calendar.DAY_OF_MONTH, 1);
date7=k.getTime();
Calendar l = Calendar.getInstance();
l.setTime(sdf.parse(time));
l.add(Calendar.YEAR, -12);
//e.add(Calendar.DAY_OF_MONTH, 1);
date12=l.getTime();
Calendar m = Calendar.getInstance();
m.setTime(sdf.parse(time));
m.add(Calendar.YEAR, -13);
date13=m.getTime();
Calendar n = Calendar.getInstance();
n.setTime(sdf.parse(time));
n.add(Calendar.YEAR, -14);
//e.add(Calendar.DAY_OF_MONTH, 1);
date14=n.getTime();
Calendar n1 = Calendar.getInstance();
n1.setTime(sdf.parse(time));
n1.add(Calendar.YEAR, -16);
date16=n1.getTime();
} catch (ParseException e) {
e.printStackTrace();
}
}
JSONObject json = new JSONObject();
String sql="SELECT * from (select d.*,s.substation_name from CCB_SUBSTATION_DEVICE d left join CCB_SUBSTATION s on d.substation_id=s.substation_id) WHERE 1=1 ";
if(stationtype != null && !"".equals(stationtype)){
sql += "and device_type='"+stationtype+"' ";
}else{
//sql += "and device_type in ('byq','dkq','dlq','drq','dlhgq','dyhgq','glkg','blq','blz','ohdrq','zbq','gis','mx','kgg','jdw','fdxq','syb','rdq','zndl','jdbh','ydtx') ";
sql += "and device_type in ('byq','dlq','dlhgq','dyhgq','glkg','blq','gis') ";
}
if(voltagelevel != null && !"".equals(voltagelevel)&&!"null".equals(voltagelevel)&&voltagelevel!="null"){
sql += "and voltage_level='"+voltagelevel+"' ";
}else {
sql += "and voltage_level in ('08','16')";
}
//1年以内
if(flag != null && "0".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"' ";
}
//1～5年
if(flag != null && "1".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"' ";
}
//5～10年
if(flag != null && "2".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"' ";
}
//10～15年
if(flag != null && "3".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"' ";
}
//15～20年
if(flag != null && "4".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"' ";
}
//20～25年
if(flag != null && "5".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"' ";
}
//25～30年
if(flag != null && "6".equals(flag)){
sql += "and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"' ";
}
//30年以上
if(flag != null && "7".equals(flag)){
sql += "and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"' ";
}
if(flag != null && "yj".equals(flag)){
//变压器 12
if("byq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date12)+"' ";
}
//断路器 14
if("dlq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date14)+"' ";
}
//电流互感器12
if("dlhgq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date12)+"' ";
}
//电压互感器 15
if("dyhgq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(elevenFiveYearOld)+"' ";
}
//隔离开关 14
if("glkg".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date14)+"' ";
}
//避雷器 16
if("blq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date16)+"' ";
}
//组合电器 13
if("gis".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date13)+"' ";
}
if("mx".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date13)+"' ";
}
if("dkq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date12)+"' ";
}
if("jdbh".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date7)+"' ";
}
if("drq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date12)+"' ";
}
if("kgg".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(date12)+"' ";
}
if("zbq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(fortyYearOld)+"' ";
}
if("ohdrq".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(fortyYearOld)+"' ";
}
if("ydtx".equals(stationtype)){
sql += "and RECENT_RUNDATE<='"+sdf.format(fortyYearOld)+"' ";
}
}
// if(flag != null && "hj".equals(flag)){
// sql += "and RECENT_RUNDATE<'"+sdf.format(twoYearOld)+"' ";
// }
String pageSql = "SELECT C.* FROM (SELECT A.*, ROWNUM RN FROM ("
+ sql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
int firstPage=pageNumber*pageSize;
int lastPage=(pageNumber - 1) * pageSize + 1;
List<SubstationDevice> dev = jdbcTemplate.query(pageSql, new Object[]{firstPage,lastPage}, new SubstationdeviceRowMapper());
json.put("rows", dev);
String sqlsum="select count(*) from ("+ sql +") ";
//获取总条数
int total = jdbcTemplate.queryForObject(sqlsum,new Object[]{}, Integer.class);
json.put("total", total);
return json;
}
/**
* 查询变电站各种设备
*/
public JSONObject getDevice4(int pageNumber, int pageSize,String stationtype,String voltagelevel,String time){
if("主变压器".equals(stationtype)){
stationtype = "byq";
}else if("电抗器".equals(stationtype)){
stationtype = "dkq";
}else if("断路器".equals(stationtype)){
stationtype = "dlq";
}else if("电力电容器".equals(stationtype)){
stationtype = "drq";
}else if("电流互感器".equals(stationtype)){
stationtype = "dlhgq";
}else if("电压互感器".equals(stationtype)){
stationtype = "dyhgq";
}else if("隔离开关".equals(stationtype)){
stationtype = "glkg";
}else if("避雷器".equals(stationtype)){
stationtype = "blq";
}else if("避雷针".equals(stationtype)){
stationtype = "blz";
}else if("耦合电容器".equals(stationtype)){
stationtype = "ohdrq";
}else if("阻波器".equals(stationtype)){
stationtype = "zbq";
}else if("组合电器(GIS)".equals(stationtype)){
stationtype = "gis";
}else if("母线".equals(stationtype)){
stationtype = "mx";
}else if("开关柜".equals(stationtype)){
stationtype = "kgg";
}else if("接地网".equals(stationtype)){
stationtype = "jdw";
}else if("放电线圈".equals(stationtype)){
stationtype = "fdxq";
}else if("所用变".equals(stationtype)){
stationtype = "syb";
}else if("熔断器".equals(stationtype)){
stationtype = "rdq";
}else if("站内电缆".equals(stationtype)){
stationtype = "zndl";
}else if("继电保护及安全自动装置".equals(stationtype)){
stationtype = "jdbh";
}else if("远动通信".equals(stationtype)){
stationtype = "ydtx";
}else{
stationtype = "";
}
if("1000kV".equals(voltagelevel)){
voltagelevel = "03";
}else if("750kV".equals(voltagelevel)){
voltagelevel = "04";
}else if("500kV".equals(voltagelevel)){
voltagelevel = "+-500kV";
}else if("330kV".equals(voltagelevel)){
voltagelevel = "17";
}else if("220kV".equals(voltagelevel)){
voltagelevel = "16";
}else if("110kV".equals(voltagelevel)){
voltagelevel = "15";
}else if("66kV".equals(voltagelevel)){
voltagelevel = "14";
}else if("35kV".equals(voltagelevel)){
voltagelevel = "13";
}else if("20kV".equals(voltagelevel)){
voltagelevel = "12";
}else if("10kV".equals(voltagelevel)){
voltagelevel = "11";
}else if("6kV".equals(voltagelevel)){
voltagelevel = "07";
}else if("380kV".equals(voltagelevel)){
voltagelevel = "10";
}else {
voltagelevel="";
}
JSONObject json = new JSONObject();
String sql="SELECT * from (select d.*,s.substation_name from CCB_SUBSTATION_DEVICE d left join CCB_SUBSTATION s on d.substation_id=s.substation_id) WHERE 1=1 ";
if(stationtype != null && !"".equals(stationtype)){
sql += "and device_type='"+stationtype+"' ";
}else{
sql += "and device_type in ('byq','dkq','dlq','drq','dlhgq','dyhgq','glkg','blq','blz','ohdrq','zbq','gis','mx','kgg','jdw','fdxq','syb','rdq','zndl','jdbh','ydtx') ";
}
if(voltagelevel != null && !"".equals(voltagelevel)&&!"null".equals(voltagelevel)&&voltagelevel!="null"){
if("+-500kV".equals(voltagelevel)) {
sql += "and voltage_level in ('01','08') ";
}else {
sql += "and voltage_level='"+voltagelevel+"' ";
}
}else {
sql += "and voltage_level in ('03','04','01','17','16','15','14','13','12','11','07','10','08')";
}
if(StringUtils.isNotEmpty(time)) {
sql += " and recent_rundate <='"+time+"'";
}
String pageSql = "SELECT C.* FROM (SELECT A.*, ROWNUM RN FROM ("
+ sql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
int firstPage=pageNumber*pageSize;
int lastPage=(pageNumber - 1) * pageSize + 1;
List<SubstationDevice> dev = jdbcTemplate.query(pageSql, new Object[]{firstPage,lastPage}, new SubstationdeviceRowMapper());
json.put("rows", dev);
String sqlsum="select count(*) from ("+ sql +") ";
//获取总条数
int total = jdbcTemplate.queryForObject(sqlsum,new Object[]{}, Integer.class);
json.put("total", total);
return json;
}
public JSONObject getDevice6(int pageNumber, int pageSize,String area,String date) {
JSONObject json = new JSONObject();
try{
String sql="select a.*,(a.safesum+a.qualitysum+a.qualityproblemsum+a.problemsum+a.problemqualitysum+a.electricexception+a.mountionsum+a.thundersum+a.icingsum+a.dancingsum) sjsl from ( "
+ "SELECT sub.*,(SELECT COUNT(*) FROM CCB_SAFE_EVENT safe WHERE safe.substation_line=sub.substation_name )safesum,"
+ "(SELECT COUNT(*) FROM CCB_QUALITY_EVENT quality WHERE quality.substation_line=sub.substation_name ) qualitysum,"
+ "(SELECT count(*) FROM CCB_QUALITY_EVENT quality,CCB_PROBLEM_DEVICE b WHERE quality.substation_line=sub.substation_name and quality.supplies_name=b.device_name and quality.supplier=b.supplier)qualityproblemsum,"
+ "(SELECT COUNT(*) FROM CCB_PROBLEM_DEVICE prm WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier ) problemsum,"
+ "(SELECT count(*) FROM CCB_PROBLEM_DEVICE prm,CCB_QUALITY_EVENT d WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier and prm.device_model=d.supplies_type and prm.supplier=d.supplier)problemqualitysum,"
+ "(SELECT COUNT(*) FROM CCB_ELECTRIC_EXCEPTION electric WHERE electric.line_name=sub.substation_name )electricexception, "
+ "(select count(*) from CCB_MOUNTAIN_FIRE mountain where mountain.Line_Name=sub.substation_name)mountionsum,"
+ "(select count(*) from CCB_THUNDER thunder where thunder.line_name=sub.substation_name)thundersum,"
+ "(select count(*) from CCB_ICING icing where icing.Line_Name=sub.substation_name)icingsum, "
+ "(select count(*) from CCB_DANCING_EVENT dinicing where dinicing.Line_Name=sub.substation_name)dancingsum "
+ " FROM CCB_SUBSTATION sub where 1=1";
if(StringUtils.isNotEmpty(area)) {
if("合计".equals(area)) {
sql += " and sub.area in ('江西','河南','湖北','湖南') ";
}else {
sql += " and sub.area = '" + area + "' ";
}
}
if(StringUtils.isNotEmpty(date)) {
sql += " and delivery_date <= '" + date + "' ";
}
sql += " and nationalassets = '01' ) a ";
String pageSql = "SELECT C.* FROM (SELECT A.*, ROWNUM RN FROM ("
+ sql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
int firstPage=pageNumber*pageSize;
int lastPage=(pageNumber - 1) * pageSize + 1;
List<Substation> sub = jdbcTemplate.query(pageSql, new Object[]{firstPage,lastPage}, new SubstationRowMapper());
String sqlsum="select count(*) from (" + sql + ") ";
//获取总条数
int total = jdbcTemplate.queryForObject(sqlsum, Integer.class);
json.put("total", total);
json.put("rows", sub);
}catch(Exception e){
// TODO Auto-generated catch block
e.printStackTrace();
}
return json;
}
/**
* 添加变电站概况
*/
public Boolean addBasic(Substation sub,List<FileUpload> fileImagesUploads){
try{
String sql="INSERT INTO ccb_substation VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";//,?,?
int i=jdbcTemplate.update(sql,new Object[]{sub.getSubstationId(),null,sub.getSubstationName(),sub.getArea(),sub.getMaintainUnit()
,sub.getVoltageLevel(),sub.getRunStatus(),sub.getDeliveryDate(),sub.getSubstationType(),sub.getImportantLevel(),
sub.getMaintransCapacity(),sub.getMaintransNum(),sub.getHighresistNum(),sub.getFivezzSwitch(),sub.getTwotzSwitch(),
sub.getCurrentTransformer(),sub.getVoltageTransformer(),sub.getIsGis(),sub.getEquipmentCode(),sub.getRemark(),sub.getCoordinatex(),sub.getCoordinatey()
,null,null,null,null,sub.getDifferentDynamic(),sub.getDifferentBasis(),sub.getIntroduction(),sub.getImage1(),sub.getImage2(),sub.getImage3(),sub.getRegisterTime(),sub.getNationalassets()});//,sub.getAssetsNum(),sub.getMaterialId()
int j=0;
if(fileImagesUploads.size()>0) {
for (FileUpload fileUpload : fileImagesUploads) {
String sql2="INSERT INTO CCB_FILE_UPLOAD VALUES(?,?,?,?)";
j=jdbcTemplate.update(sql2,new Object[]{fileUpload.getFileId(),fileUpload.getFileName(),fileUpload.getFilePath(),fileUpload.getType()});
}
}
return true;
}catch(Exception e){
return false;
}
}
/**
* 添加变电站设备概况
*/
public Boolean addDevice(SubstationDevice device,List<FileUpload> fileImagesUploads){
try{
String sql="INSERT INTO CCB_SUBSTATION_DEVICE VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
int i=jdbcTemplate.update(sql,new Object[]{device.getDeviceId(),device.getSubstationId(),null,device.getVoltageLevel(),device.getDeviceType()
,device.getGapUnit(),device.getModel(),device.getProduceFactory(),device.getDeliveryDate(),device.getRecentRundate(),device.getDeviceStatus(),
device.getDeviceCode(),device.getAssetsNature(),device.getAssetsUnit(),device.getDeviceName(),device.getDeviceMark(),
device.getDifferentDynamic(),device.getDifferentBasis(),device.getRemark(),device.getRegisterTime(),device.getAssetsNum(),device.getMaterialId(),
device.getSjZcbm(),device.getSjXmbm(),device.getSjPmbm(),device.getWlbm(),device.getFbXmbm(),device.getFbPmbm(),device.getZczt(),device.getScrapDate()});
int j=0;
if(fileImagesUploads.size()>0) {
for (FileUpload fileUpload : fileImagesUploads) {
String sql2="INSERT INTO CCB_FILE_UPLOAD VALUES(?,?,?,?)";
j=jdbcTemplate.update(sql2,new Object[]{fileUpload.getFileId(),fileUpload.getFileName(),fileUpload.getFilePath(),fileUpload.getType()});
}
}
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
/**
* 修改变电站概况
*/
public Boolean updateBasic(Substation sub,List<FileUpload> fileImagesUploads){
StringBuffer buff = new StringBuffer();
String differentBasis = sub.getDifferentBasis();
String image1 = sub.getImage1();
String image2 = sub.getImage2();
String image3 = sub.getImage3();
if("".equals(differentBasis) || differentBasis == null || "null".equals(differentBasis)){
String text = sub.getText();
if(!"".equals(text) && text != null && !"null".equals(text)){
differentBasis = text;
}
}
if(!"".equals(image1) && image1 != null && !"null".equals(image1)){
}else if(!"".equals(image2) && image2 != null && !"null".equals(image2)){
}else if(!"".equals(image3) && image3 != null && !"null".equals(image3)){
}else{
if("".equals(image1) || image1 == null || "null".equals(image1)){
String text1 = sub.getText1();
if(!"".equals(text1) && text1 != null && !"null".equals(text1)){
image1 = text1;
}
}
if("".equals(image2) || image2 == null || "null".equals(image2)){
String text2 = sub.getText2();
if(!"".equals(text2) && text2 != null && !"null".equals(text2)){
image2 = text2;
}
}
if("".equals(image3) || image3 == null || "null".equals(image3)){
String text3 = sub.getText3();
if(!"".equals(text3) && text3 != null && !"null".equals(text3)){
image3 = text3;
}
}
}
try{
buff.append("UPDATE ccb_substation SET SUBSTATION_NAME='").append(sub.getSubstationName()).append("',AREA='").append(sub.getArea()).append("',MAINTAIN_UNIT='").append(sub.getMaintainUnit()).append("',VOLTAGE_LEVEL='").append(sub.getVoltageLevel()).append("',RUN_STATUS='").append(sub.getRunStatus()).append("',DELIVERY_DATE='").append(sub.getDeliveryDate()).append("'")
.append(",SUBSTATION_TYPE='").append(sub.getSubstationType()).append("',IMPORTANT_LEVEL='").append(sub.getImportantLevel()).append("',MAINTRANS_CAPACITY='").append(sub.getMaintransCapacity()).append("',MAINTRANS_NUM='").append(sub.getMaintransNum()).append("',HIGHRESIST_NUM='").append(sub.getHighresistNum()).append("',FIVEZZ_SWITCH='").append(sub.getFivezzSwitch()).append("'")
.append(",TWOTZ_SWITCH='").append(sub.getTwotzSwitch()).append("',CURRENT_TRANSFORMER='").append(sub.getCurrentTransformer()).append("',VOLTAGE_TRANSFORMER='").append(sub.getVoltageTransformer()).append("',IS_GIS='").append(sub.getIsGis()).append("',EQUIPMENT_CODE='").append(sub.getEquipmentCode()).append("',REMARK='").append(sub.getRemark()).append("',COORDINATEX='").append(sub.getCoordinatex()).append("',COORDINATEY='").append(sub.getCoordinatey()).append("'")
.append(",DIFFERENT_DYNAMIC='").append(sub.getDifferentDynamic()).append("',");
if(!"".equals(differentBasis) && differentBasis != null){
buff.append("DIFFERENT_BASIS='").append(differentBasis).append("',");
}
buff.append("introduction='").append(sub.getIntroduction()).append("',");
if(!"".equals(image1) && image1 != null){
buff.append("image1='").append(image1).append("',");
}else{
buff.append("image1='',");
}
if(!"".equals(image2) && image2 != null){
buff.append("image2='").append(image2).append("',");
}else{
buff.append("image2='',");
}
if(!"".equals(image3) && image3 != null){
buff.append("image3='").append(image3).append("',");
}else{
buff.append("image3='',");
}
buff.append("REGISTER_TIME='").append(sub.getRegisterTime()).append("',nationalassets='").append(sub.getNationalassets()).append("'")//.append("',ASSETS_NUM='").append(sub.getAssetsNum()).append("',MATERIAL_ID='").append(sub.getMaterialId())
.append(" WHERE SUBSTATION_ID='").append(sub.getSubstationId()).append("'");
int i = jdbcTemplate.update(buff.toString());
/*String sql="UPDATE ccb_substation SET SUBSTATION_NAME=?,AREA=?,MAINTAIN_UNIT=?,VOLTAGE_LEVEL=?,RUN_STATUS=?,DELIVERY_DATE=?"
+ ",SUBSTATION_TYPE=?,IMPORTANT_LEVEL=?,MAINTRANS_CAPACITY=?,MAINTRANS_NUM=?,HIGHRESIST_NUM=?,FIVEZZ_SWITCH=?"
+ ",TWOTZ_SWITCH=?,CURRENT_TRANSFORMER=?,VOLTAGE_TRANSFORMER=?,IS_GIS=?,EQUIPMENT_CODE=?,REMARK=?,COORDINATEX=?,COORDINATEY=?"
+ ",DIFFERENT_DYNAMIC=?,DIFFERENT_BASIS=?,introduction=?,image1=?,image2=?,image3=?,REGISTER_TIME=?,nationalassets=?"
+ " WHERE SUBSTATION_ID=?";
int i=jdbcTemplate.update(sql,new Object[]{
sub.getSubstationName(),sub.getArea(),sub.getMaintainUnit(),sub.getVoltageLevel(),sub.getRunStatus(),sub.getDeliveryDate(),
sub.getSubstationType(),sub.getImportantLevel(),sub.getMaintransCapacity(),sub.getMaintransNum(),sub.getHighresistNum(),sub.getFivezzSwitch(),sub.getTwotzSwitch(),
sub.getCurrentTransformer(),sub.getVoltageTransformer(),sub.getIsGis(),
sub.getEquipmentCode(),sub.getRemark(),sub.getCoordinatex(),sub.getCoordinatey(),sub.getDifferentDynamic(),sub.getDifferentBasis(),sub.getIntroduction(),sub.getImage1(),sub.getImage2(),sub.getImage3(),sub.getRegisterTime(),sub.getNationalassets(),sub.getSubstationId()
});*/
int j=0;
if(fileImagesUploads.size()>0) {
//判断是否存在这条数据
String checkFileUpload="select count(1) from CCB_FILE_UPLOAD where FILE_ID=?";
int isChecked = jdbcTemplate.queryForObject(checkFileUpload, new Object[]{sub.getSubstationId()}, Integer.class);
if(isChecked>0) {
for(int i1=0; i1<fileImagesUploads.size(); i1++){
FileUpload fileUpload = fileImagesUploads.get(i1);
String sql2="delete from ccb_file_upload where file_id=? and type=?";
j=jdbcTemplate.update(sql2,new Object[]{sub.getSubstationId(),fileUpload.getType()});
}
}
for (FileUpload fileUpload : fileImagesUploads) {
String sql2="INSERT INTO CCB_FILE_UPLOAD VALUES(?,?,?,?)";
j=jdbcTemplate.update(sql2,new Object[]{sub.getSubstationId(),fileUpload.getFileName(),fileUpload.getFilePath(),fileUpload.getType()});
}
}
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
/**
* 修改变电站设备信息
*/
public Boolean updateDevice(SubstationDevice device,List<FileUpload> fileImagesUploads){
//String differentBasis = device.getDifferentBasis();
try{
String sql = "";
int i = 0;
//if(!"".equals(differentBasis) && differentBasis!= null){
sql="UPDATE CCB_SUBSTATION_DEVICE SET VOLTAGE_LEVEL=?,DEVICE_TYPE=?,GAP_UNIT=?,MODEL=?"
+ ",PRODUCE_FACTORY=?,DELIVERY_DATE=?,RECENT_RUNDATE=?,DEVICE_STATUS=?,DEVICE_CODE=?,"
+ "ASSETS_NATURE=?,ASSETS_UNIT=?,DEVICE_NAME=?,DIFFERENT_DYNAMIC=?,DIFFERENT_BASIS=?,remark=?,"
+ "register_time=?,assets_num=?,material_id=?,SJ_XMBM=?,FB_XMBM=?,WLBM=?,FB_PMBM=?,SJ_PMBM=?,SJ_ZCBM=?,ZCZT=?,SCRAP_DATE=?"
+ "WHERE DEVICE_ID=? AND SUBSTATION_ID=?";
i=jdbcTemplate.update(sql,new Object[]{device.getVoltageLevel(),device.getDeviceType(),
device.getGapUnit(),device.getModel(),device.getProduceFactory(),device.getDeliveryDate(),device.getRecentRundate(),device.getDeviceStatus(),device.getDeviceCode(),
device.getAssetsNature(),device.getAssetsUnit(),device.getDeviceName(),device.getDifferentDynamic(),device.getDifferentBasis(),
device.getRemark(),device.getRegisterTime(),device.getAssetsNum(),device.getMaterialId(),device.getSjXmbm(),device.getFbXmbm(),device.getWlbm(),
device.getFbPmbm(),device.getSjPmbm(),device.getSjZcbm(),device.getZczt(),device.getScrapDate(),device.getDeviceId(),device.getSubstationId()});
/*}else{
sql="UPDATE CCB_SUBSTATION_DEVICE SET VOLTAGE_LEVEL=?,DEVICE_TYPE=?,GAP_UNIT=?,MODEL=?"
+ ",PRODUCE_FACTORY=?,DELIVERY_DATE=?,RECENT_RUNDATE=?,DEVICE_STATUS=?,DEVICE_CODE=?,ASSETS_NATURE=?,ASSETS_UNIT=?,DEVICE_NAME=?,DIFFERENT_DYNAMIC=?,remark=?,register_time=?"
+ "WHERE DEVICE_ID=?";
i=jdbcTemplate.update(sql,new Object[]{device.getVoltageLevel(),device.getDeviceType(),
device.getGapUnit(),device.getModel(),device.getProduceFactory(),device.getDeliveryDate(),device.getRecentRundate(),device.getDeviceStatus(),device.getDeviceCode(),
device.getAssetsNature(),device.getAssetsUnit(),device.getDeviceName(),device.getDifferentDynamic(),device.getRemark(),device.getRegisterTime(),device.getDeviceId()});
}*/
int j=0;
if(fileImagesUploads.size()>0) {
//判断是否存在这条数据
String checkFileUpload="select count(1) from CCB_FILE_UPLOAD where FILE_ID=?";
int isChecked = jdbcTemplate.queryForObject(checkFileUpload, new Object[]{device.getDeviceId()+""+device.getSubstationId()}, Integer.class);
if(isChecked>0) {
String sql2="delete from ccb_file_upload where file_id=?";
j=jdbcTemplate.update(sql2,new Object[]{device.getDeviceId()+""+device.getSubstationId()});
}
for (FileUpload fileUpload : fileImagesUploads) {
//if(isChecked == 0) {
String sql2="INSERT INTO CCB_FILE_UPLOAD VALUES(?,?,?,?)";
j=jdbcTemplate.update(sql2,new Object[]{device.getDeviceId()+""+device.getSubstationId(),fileUpload.getFileName(),fileUpload.getFilePath(),fileUpload.getType()});
//}
}
}
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}
}
/**
* 删除变电站概况
*/
public Boolean deleteBasic(String id){
String sql="DELETE FROM ccb_substation WHERE SUBSTATION_ID in ("+ id +")";
String sql2="DELETE FROM CCB_SUBSTATION_DEVICE WHERE SUBSTATION_ID in ("+ id +")";
int i = jdbcTemplate.update(sql);
int j = jdbcTemplate.update(sql2);
if(i>0){
return true;
}
return false;
}
/**
* 根据变电站id去线路表里面查找
*/
public Boolean finLineBySubId(String ssid){
String sql = "select * from ccb_line_info where START_STATION in ("+ssid+") or END_STATION in ("+ssid+")";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(list != null && list.size() > 0){
return false;
}
return true;
}
/**
* 删除变电站设备
*/
public Boolean deleteDevice(String id){
String sql="DELETE FROM CCB_SUBSTATION_DEVICE WHERE DEVICE_ID in ("+ id +")";
int i=jdbcTemplate.update(sql);
if(i>0){
return true;
}
return false;
}
/**
* excel导入变电站数据
*/
public boolean importsub(List<Substation> subList){
int size = subList.size();
Substation sub = null;
boolean flag = true;
List<Substation> temp = new ArrayList<Substation>();
for(int i=0; i<size; i++){
sub = subList.get(i);
sub.setSubstationId(MD5.MD5(sub.getSubstationName()));
List<FileUpload> fileUploads=new ArrayList<>();
flag = addBasic(sub,fileUploads);
if(flag == false){
break;
}
temp.add(sub);
}
if(!flag && temp.size()>0){
String ids = "";
for(int j=0; j<temp.size(); j++){
sub = temp.get(j);
String id = sub.getSubstationId();
if(j == temp.size()-1){
ids += "'"+id+"'";
}else{
ids += "'"+id+"',";
}
}
deleteBasic(ids);
}
return flag;
}
/**
* excel导入变电站设备数据
*/
public boolean importDev(List<SubstationDevice> devList){
int size = devList.size();
SubstationDevice dev = null;
boolean flag = true;
List<SubstationDevice> temp = new ArrayList<SubstationDevice>();
for(int i=0; i<size; i++){
dev = devList.get(i);
dev.setDeviceMark(dev.getDeviceType());
dev.setDeviceId(MD5.MD5(dev.getDeviceName()));
String Id=lineinfoDao.getSubId(dev.getSubstationName());
dev.setSubstationId(Id);
List<FileUpload> fileUploads=new ArrayList<>();
flag = addDevice(dev,fileUploads);
if(flag == false){
break;
}
temp.add(dev);
}
if(!flag && temp.size()>0){
String ids = "";
for(int j=0; j<temp.size(); j++){
dev = temp.get(j);
String id = dev.getDeviceId();
if(j == temp.size()-1){
ids += "'"+id+"'";
}else{
ids += "'"+id+"',";
}
}
deleteBasic(ids);
}
return flag;
}
public String findDataBook(String code, String text){
String sql = "SELECT TYPECODE FROM PLAT_TYPEGROUP TG INNER JOIN PLAT_TYPE TP ON TG.ID=TP.TYPEGROUPID WHERE TG.TYPEGROUPCODE IN ('"+code+"') AND TP.TYPENAME LIKE '%"+text+"%'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
String result = "";
if(list != null && list.size() > 0){
for(int i=0; i<list.size(); i++){
Map<String, Object> map = list.get(i);
String typeCode = (String)map.get("TYPECODE");
if(i != list.size()-1){
result += "'"+typeCode+"',";
}else{
result += "'"+typeCode+"'";
}
}
}
return result;
}
//自定义查询电站信息
public JSONObject CustomSeachSub(int pageNumber, int pageSize, String names, String conditions,
String values, String joins){
JSONObject json = new JSONObject();
String baseSql="select * from (select a.*,(a.safesum+a.qualitysum+a.qualityproblemsum+a.problemsum+a.problemqualitysum+a.electricexception+a.mountionsum+a.thundersum+a.icingsum+a.dancingsum) sjsl from ( "
+ "SELECT sub.*,(SELECT COUNT(*) FROM CCB_SAFE_EVENT safe WHERE safe.substation_line=sub.substation_name )safesum,"
+ "(SELECT COUNT(*) FROM CCB_QUALITY_EVENT quality WHERE quality.substation_line=sub.substation_name ) qualitysum,"
+ "(SELECT count(*) FROM CCB_QUALITY_EVENT quality,CCB_PROBLEM_DEVICE b WHERE quality.substation_line=sub.substation_name and quality.supplies_name=b.device_name and quality.supplier=b.supplier)qualityproblemsum,"
+ "(SELECT COUNT(*) FROM CCB_PROBLEM_DEVICE prm WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier ) problemsum,"
+ "(SELECT count(*) FROM CCB_PROBLEM_DEVICE prm,CCB_QUALITY_EVENT d WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier and prm.device_model=d.supplies_type and prm.supplier=d.supplier)problemqualitysum,"
+ "(SELECT COUNT(*) FROM CCB_ELECTRIC_EXCEPTION electric WHERE electric.line_name=sub.substation_name )electricexception, "
+ "(select count(*) from CCB_MOUNTAIN_FIRE mountain where mountain.Line_Name=sub.substation_name)mountionsum,"
+ "(select count(*) from CCB_THUNDER thunder where thunder.line_name=sub.substation_name)thundersum,"
+ "(select count(*) from CCB_ICING icing where icing.Line_Name=sub.substation_name)icingsum, "
+ "(select count(*) from CCB_DANCING_EVENT dinicing where dinicing.Line_Name=sub.substation_name)dancingsum "
+ " FROM CCB_SUBSTATION sub) a) WHERE 1=1 ";
String[] rowNames = names.split(",");
String[] queryConditions = conditions.split(",");
String[] rowValues = values.split(",");
String[] rowJoins = joins.split(",");
for(int i=0; i<rowNames.length; i++){
String rowName = rowNames[i];
String queryCondition = queryConditions[i];
String rowValue = rowValues[i].trim();
if("null".equals(rowValue)){
rowValue = "";
}
String rowJoin = rowJoins[i];
if("NATIONALASSETS".equals(rowName)){
if("like".equals(queryCondition)){
if("是".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%01%' ";
}else if("否".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%02%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%"+rowValue+"%' ";
}
}else{
if("是".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '01' ";
}else if("否".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '02' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '"+rowValue+"' ";
}
}
}else if("VOLTAGE_LEVEL".equals(rowName)){
if(StringUtils.isNotEmpty(rowValue)&&rowValue.toLowerCase().contains("kv")) {
rowValue = rowValue.toLowerCase().replaceAll("kv", "kV");
}else if(StringUtils.isNotEmpty(rowValue)&&rowValue.toLowerCase().contains("v")){
rowValue = rowValue.toLowerCase().replaceAll("v", "V");
}
String dydjCode = findDataBook("voltageLevel", rowValue);
if(!"".equals(dydjCode) && dydjCode != null){
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " not in ("+dydjCode+") ";
}else{
baseSql += rowJoin + " " + rowName + " in ("+dydjCode+") ";
}
}else{
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '%666%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '666' ";
}
}
}else if("RUN_STATUS".equals(rowName)){
String runStateCode = findDataBook("runStart", rowValue);
if(!"".equals(runStateCode) && runStateCode != null){
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " not in ("+runStateCode+") ";
}else{
baseSql += rowJoin + " " + rowName + " in ("+runStateCode+") ";
}
}else{
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '%666%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '666' ";
}
}
}else if("SUBSTATION_TYPE".equals(rowName)){
String subTypeCode = findDataBook("subType", rowValue);
if(!"".equals(subTypeCode) && subTypeCode != null){
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " not in ("+subTypeCode+") ";
}else{
baseSql += rowJoin + " " + rowName + " in ("+subTypeCode+") ";
}
}else{
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '%666%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '666' ";
}
}
}else if("IMPORTANT_LEVEL".equals(rowName)){
String subImportantCode = findDataBook("subImportant", rowValue);
if(!"".equals(subImportantCode) && subImportantCode != null){
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " not in ("+subImportantCode+") ";
}else{
baseSql += rowJoin + " " + rowName + " in ("+subImportantCode+") ";
}
}else{
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '%666%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '666' ";
}
}
}else if("DELIVERY_DATE".equals(rowName)){
if("like".equals(queryCondition)){
baseSql += rowJoin + " DELIVERY_DATE" + " like '%" + rowValue + "%' ";
}else{
baseSql += rowJoin + " TO_DATE(DELIVERY_DATE, 'YYYY-MM-DD') " + queryCondition + " TO_DATE('" + rowValue + "','YYYY-MM-DD') ";
}
}else if("IS_GIS".equals(rowName)){
if("like".equals(queryCondition)){
if("是".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%1%' ";
}else if("否".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%2%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%"+rowValue+"%' ";
}
}else{
if("是".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'1' ";
}else if("否".equals(rowValue)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'2' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '"+rowValue+"' ";
}
}
}else if("REGISTER_TIME".equals(rowName)){
if("like".equals(queryCondition)){
baseSql += rowJoin + " REGISTER_TIME" + " like '%" + rowValue + "%' ";
}else{
baseSql += rowJoin + " TO_DATE(REGISTER_TIME, 'YYYY-MM-DD') " + queryCondition + " TO_DATE('" + rowValue + "','YYYY-MM-DD') ";
}
}else if("DIFFERENT_DYNAMIC".equals(rowName)){
String ydqkCode = findDataBook("differentDynamic", rowValue);
if(!"".equals(ydqkCode) && ydqkCode != null){
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " not in ("+ydqkCode+") ";
}else{
baseSql += rowJoin + " " + rowName + " in ("+ydqkCode+") ";
}
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%666%' ";
}
}else if("SAFESUM".equals(rowName)||"QUALITYSUM".equals(rowName)||"QUALITYPROBLEMSUM".equals(rowName)||"MOUNTIONSUM".equals(rowName)||"THUNDERSUM".equals(rowName)||"ICINGSUM".equals(rowName)||"ELECTRICEXCEPTION".equals(rowName)||"DANCINGSUM".equals(rowName)||"MAINTRANS_CAPACITY".equals(rowName)||"MAINTRANS_NUM".equals(rowName)||"HIGHRESIST_NUM".equals(rowName)||"FIVEZZ_SWITCH".equals(rowName)||"TWOTZ_SWITCH".equals(rowName)||"CURRENT_TRANSFORMER".equals(rowName)||"VOLTAGE_TRANSFORMER".equals(rowName)){
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%"+rowValue+"%' ";
}else{
baseSql += rowJoin + " to_number(" + rowName + ") " + queryCondition + "to_number("+rowValue+") ";
}
}else{
if("like".equals(queryCondition)){
baseSql += rowJoin + " " + rowName + " " + queryCondition + "'%"+rowValue+"%' ";
}else{
baseSql += rowJoin + " " + rowName + " " + queryCondition + " '"+rowValue+"' ";
}
}
}
String countSql = "SELECT COUNT(*) FROM (" + baseSql + ")";
int total = 0;
try{
total = jdbcTemplate.queryForObject(countSql, new Object[]{}, Integer.class);
}catch(Exception e){
List<Substation> resultList = new ArrayList<Substation>();
json.put("total", 0);
json.put("rows", resultList);
return json;
}
json.put("total", total);
String pageSql = "SELECT C.* "
+ "FROM (SELECT A.*, ROWNUM RN FROM ("
+ baseSql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
Object[] baseObjs = new Object[]{};
int length = baseObjs.length;
Object[] pageObjs = Arrays.copyOf(baseObjs, length + 2);
pageObjs[length] = pageNumber * pageSize;
pageObjs[length + 1] = (pageNumber - 1) * pageSize + 1;
List<Substation> subList = jdbcTemplate.query(pageSql, pageObjs, new SubstationRowMapper());
json.put("rows", subList);
return json;
}
//自定义查询电站设备信息
public JSONObject CustomSeachDev(String substationId,int pageNumber, int pageSize, String names, String conditions,
String values, String joins,String tableType){
JSONObject json = new JSONObject();
String baseSql="SELECT dev.*,(SELECT sub.SUBSTATION_NAME FROM CCB_SUBSTATION sub WHERE "
+ " sub.SUBSTATION_ID=dev.SUBSTATION_ID) SUBSTATION_NAME "+
" FROM CCB_SUBSTATION_DEVICE dev WHERE dev.substation_id='"+substationId+"' ";
String[] rowNames = names.split(",");
String[] queryConditions = conditions.split(",");
String[] rowValues = values.split(",");
String[] rowJoins = joins.split(",");
String val="";
String queryDicValue = "";
for(int i=0; i<rowNames.length; i++){
String rowName = rowNames[i];
String queryCondition = queryConditions[i];
String rowValue = rowValues[i];
switch(rowName) {
case "VOLTAGE_LEVEL":
queryDicValue = "设备电压等级";
break;
case "DEVICE_TYPE":
queryDicValue = "变电站设备类型";
break;
case "DEVICE_STATUS":
queryDicValue = "变电站设备状态";
break;
case "DIFFERENT_DYNAMIC":
queryDicValue = "异动情况";
break;
}
if("null".equals(rowValue)){
rowValue = "";
}
if("".equals(queryDicValue)){
String QueryDicSql = " SELECT tp.typecode FROM PLAT_TYPEGROUP tg INNER JOIN PLAT_TYPE tp ON tg.ID=tp.TYPEGROUPID WHERE tg.typegroupname = '"+queryDicValue+"' and tp.typename like '%"+rowValue+"%' ";
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(QueryDicSql);
if(resultList.size()>0) {
for(int j=0; j<resultList.size(); j++){
Map<String, Object> map = resultList.get(j);
String rowValued = (String) map.get("TYPECODE");
if(j == resultList.size()-1){
val +="'"+rowValued+"'";
}else{
val +="'"+rowValued+"',";
}
}
}
}
String rowJoin = rowJoins[i];
if("like".equals(queryCondition)){
if("VOLTAGE_LEVEL".equals(rowName)||"DEVICE_TYPE".equals(rowName)||"DEVICE_STATUS".equals(rowName)||"DIFFERENT_DYNAMIC".equals(rowName)){
if(val.equals("")){
baseSql += rowJoin + " " + "dev."+rowName + " IN ('')";
}else{
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + "dev."+rowName + " NOT IN ("+ val +")";
}else{
baseSql += rowJoin + " " + "dev."+rowName + " IN ("+ val +")";
}
}
}else{
baseSql += rowJoin + " " + "dev."+rowName + " like '%"+rowValue+"%'";
}
}else{
if("VOLTAGE_LEVEL".equals(rowName)||"DEVICE_TYPE".equals(rowName)||"DEVICE_STATUS".equals(rowName)||"DIFFERENT_DYNAMIC".equals(rowName)){
if(val.equals("")){
baseSql += rowJoin + " " + "dev."+rowName + " IN ('')";
}else{
if("!=".equals(queryCondition)){
baseSql += rowJoin + " " + "dev."+rowName + " NOT IN ("+ val +")";
}else{
baseSql += rowJoin + " " + "dev."+rowName + " IN ("+ val +")";
}
}
}else if("DELIVERY_DATE".equals(rowName)||"RECENT_RUNDATE".equals(rowName)||"REGISTER_TIME".equals(rowName)){
baseSql += rowJoin + " " + "dev."+rowName + " " + queryCondition + " '" + rowValue + "' ";
}else{
baseSql += rowJoin + " " + "dev."+rowName + " " + queryCondition +" '"+rowValue+"'";
}
}
}
String countSql = "SELECT COUNT(*) FROM (" + baseSql + ")";
int total = jdbcTemplate.queryForObject(countSql, new Object[]{}, Integer.class);
json.put("total", total);
String pageSql = "SELECT C.* "
+ "FROM (SELECT A.*, ROWNUM RN FROM ("
+ baseSql + ") A WHERE ROWNUM <= ?) C WHERE RN >=? ";
Object[] baseObjs = new Object[]{};
int length = baseObjs.length;
Object[] pageObjs = Arrays.copyOf(baseObjs, length + 2);
/* if(((pageNumber-1) * pageSize)>total ||total>(pageNumber * pageSize)){
pageNumber=1;
}*/
pageObjs[length] = pageNumber * pageSize;
pageObjs[length + 1] = (pageNumber - 1) * pageSize + 1;
List<SubstationDevice> devList = jdbcTemplate.query(pageSql, pageObjs, new SubstationdeviceRowMapper() );
json.put("rows", devList);
return json;
}
//查询站点是否重复
public List<String> getSubstationName(){
List<String> dataMap = new ArrayList<String>();
String sql="SELECT SUBSTATION_NAME FROM ccb_substation";
dataMap=jdbcTemplate.queryForList(sql, String.class);
return dataMap;
}
public Boolean updateSubCoordinate(Substation sub){
String sql="UPDATE ccb_substation SET COORDINATEX=?,COORDINATEY=?"
+ " WHERE SUBSTATION_ID=?";
int i=jdbcTemplate.update(sql,new Object[]{sub.getCoordinatex(),
sub.getCoordinatey(),sub.getSubstationId()});
//修改线路下的坐标
String sql2="UPDATE CCB_LINE_INFO SET SSX=?,SSY=? WHERE START_STATION=?";
int j=jdbcTemplate.update(sql2,new Object[]{sub.getCoordinatex(),
sub.getCoordinatey(),sub.getSubstationId()});
String sql3="UPDATE CCB_LINE_INFO SET ESX=?,ESY=? WHERE END_STATION=?";
int k=jdbcTemplate.update(sql3,new Object[]{sub.getCoordinatex(),
sub.getCoordinatey(),sub.getSubstationId()});
if(i>0 && j>0 && k>0){
return true;
}
return false;
}
public JSONObject selectCoor(){
JSONObject json = new JSONObject();
String baseSql = "SELECT SUBSTATION_NAME,COORDINATEX,COORDINATEY FROM CCB_SUBSTATION";
List<Substation> list = jdbcTemplate.query(baseSql, new SubstationRowMapper());
json.put("rows", list);
return json;
}
public List<Substation> findAllData(String substation,String nationalassets,String sarea, String deliveryDates, String ageLimit, String ids) {
String sql="SELECT sub.*,(SELECT COUNT(*) FROM CCB_SAFE_EVENT safe WHERE safe.substation_line=sub.substation_name )safesum,"
+ "(SELECT COUNT(*) FROM CCB_QUALITY_EVENT quality WHERE quality.substation_line=sub.substation_name ) qualitysum,"
+ "(SELECT count(*) FROM CCB_QUALITY_EVENT quality,CCB_PROBLEM_DEVICE b WHERE quality.substation_line=sub.substation_name and quality.supplies_name=b.device_name and quality.supplier=b.supplier)qualityproblemsum,"
+ "(SELECT COUNT(*) FROM CCB_PROBLEM_DEVICE prm WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier ) problemsum,"
+ "(SELECT count(*) FROM CCB_PROBLEM_DEVICE prm,CCB_QUALITY_EVENT d WHERE sub.supplie_type=prm.device_model and sub.supplie=prm.supplier and prm.device_model=d.supplies_type and prm.supplier=d.supplier)problemqualitysum,"
+ "(SELECT COUNT(*) FROM CCB_ELECTRIC_EXCEPTION electric WHERE electric.line_name=sub.substation_name )electricexception, "
+ "(select count(*) from CCB_MOUNTAIN_FIRE mountain where mountain.Line_Name=sub.substation_name)mountionsum,"
+ "(select count(*) from CCB_THUNDER thunder where thunder.line_name=sub.substation_name)thundersum,"
+ "(select count(*) from CCB_ICING icing where icing.Line_Name=sub.substation_name)icingsum, "
+ "(select count(*) from CCB_DANCING_EVENT dinicing where dinicing.Line_Name=sub.substation_name)dancingsum "
+ " FROM CCB_SUBSTATION sub where 1=1";
if(substation!=null&&!substation.equals("")){
sql+=" and SUBSTATION_NAME like '%" + substation + "%'";
}
if(nationalassets!=null&&!nationalassets.equals("")){
sql+=" and nationalassets ='"+nationalassets+ "'";
}
if(sarea!=null&&!sarea.equals("")){
sql+=" and AREA ='" + sarea + "'";
}
if(deliveryDates!=null&&!deliveryDates.equals("")){
sql+=" and substr(DELIVERY_DATE,0,4) = '"+ deliveryDates+"'";
}
if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("10")){
lowage = "0";
}else if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}else if(ageLimit.equals("40")){
lowage = "30";
}else if(ageLimit.equals("50")){
lowage = "40";
}else if(ageLimit.equals("60")){
lowage = "50";
}
sql+="and months_between(sysdate,to_date(SUBSTR(DELIVERY_DATE,1,10),'yyyy-MM-dd'))/12 <='"+ ageLimit+"' ";
sql+="and months_between(sysdate,to_date(SUBSTR(DELIVERY_DATE,1,10),'yyyy-MM-dd'))/12 >'"+ lowage+"' ";
}
if(ids != null && !"".equals(ids)){
String[] idList = ids.split(",");
String standardIds = "";
for(int i=0; i < idList.length; i++){
if(i != idList.length-1){
standardIds += "'" + idList[i] + "'," ;
}else{
standardIds += "'" + idList[i] + "'" ;
}
}
sql += "AND SUBSTATION_ID in (" + standardIds + ") ";
}
/*if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}
sql+="and (to_char(sysdate,'yyyy') - to_char(DELIVERY_DATE,'yyyy')) <='"+ ageLimit+"'";
sql+="and (to_char(sysdate,'yyyy')-to_char(DELIVERY_DATE,'yyyy')) >'"+ lowage+"'";
}*/
return jdbcTemplate.query(sql, new SubstationRowMapper());
}
//数据导出
public String Customimexport(HttpServletRequest equest, HttpServletResponse response, List<Substation>list) {
//JSONObject object=((Object) new JSONObject()).fromObject(data);
Workbook wb = null;
try {
//创建一个名称为"变电站概况"的sheet
wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("变电站概况");
sheet.setDefaultColumnWidth(18);// 设置默认列宽
sheet.setColumnWidth(0, 54*50);//设置第一列的宽度
Font font = wb.createFont();// 字体
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
CellStyle cellStylehead = wb.createCellStyle();// 表头样式
cellStylehead.setFont(font);// 设置字体样式
cellStylehead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
cellStylehead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStylehead.setWrapText(true);// 自动换行
//设置表头颜色
cellStylehead.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStylehead.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置边框
cellStylehead.setBorderTop(CellStyle.BORDER_THIN);
cellStylehead.setBorderRight(CellStyle.BORDER_THIN);
cellStylehead.setBorderBottom(CellStyle.BORDER_THIN);
cellStylehead.setBorderLeft(CellStyle.BORDER_THIN);
// 表体样式
CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
cellStyleBody.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStyleBody.setWrapText(true);// 自动换行
// 设置边框
cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
// 右对齐
CellStyle cellStyleRight = wb.createCellStyle();// 右对齐样式
cellStyleRight.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
cellStyleRight.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStyleRight.setWrapText(true);// 自动换行
cellStyleRight.setBorderTop(CellStyle.BORDER_THIN);
cellStyleRight.setBorderRight(CellStyle.BORDER_THIN);
cellStyleRight.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleRight.setBorderLeft(CellStyle.BORDER_THIN);
//居中对齐
CellStyle cellStyleCENTER = wb.createCellStyle();//右对齐样式
cellStyleCENTER.setAlignment(CellStyle.ALIGN_CENTER);//水平对齐
cellStyleCENTER.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对齐
cellStyleCENTER.setWrapText(true);//自动换行
cellStyleCENTER.setBorderTop(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderRight(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderLeft(CellStyle.BORDER_THIN);
Row rowHeader1 = sheet.createRow(0);
Cell cell99 = rowHeader1.createCell(0);
cell99.setCellValue("序号");
cell99.setCellStyle(cellStylehead);
Cell cell00 = rowHeader1.createCell(1);
cell00.setCellValue("变电站名称");
cell00.setCellStyle(cellStylehead);
Cell cell101 = rowHeader1.createCell(2);
cell101.setCellValue("是否直属资产 ");
cell101.setCellStyle(cellStylehead);
Cell cell01 = rowHeader1.createCell(3);
cell01.setCellValue("区域");
cell01.setCellStyle(cellStylehead);
Cell cell02 = rowHeader1.createCell(4);
cell02.setCellValue("运维单位");
cell02.setCellStyle(cellStylehead);
Cell cell03 = rowHeader1.createCell(5);
cell03.setCellValue("电压等级" );
cell03.setCellStyle(cellStylehead);
Cell cell04 = rowHeader1.createCell(6);
cell04.setCellValue("运行状态");
cell04.setCellStyle(cellStylehead);
Cell cell05 = rowHeader1.createCell(7);
cell05.setCellValue("投运日期" );
cell05.setCellStyle(cellStylehead);
Cell cell06 = rowHeader1.createCell(8);
cell06.setCellValue("电站类型" );
cell06.setCellStyle(cellStylehead);
Cell cell07 = rowHeader1.createCell(9);
cell07.setCellValue("变电站重要级别" );
cell07.setCellStyle(cellStylehead);
Cell cell08 = rowHeader1.createCell(10);
cell08.setCellValue("安全事件" );
cell08.setCellStyle(cellStylehead);
Cell cell09 = rowHeader1.createCell(11);
cell09.setCellValue("质量事件" );
cell09.setCellStyle(cellStylehead);
Cell cell10 = rowHeader1.createCell(12);
cell10.setCellValue("质量事件对应问题设备" );
cell10.setCellStyle(cellStylehead);
Cell cell11 = rowHeader1.createCell(13);
cell11.setCellValue("山火" );
cell11.setCellStyle(cellStylehead);
Cell cell12 = rowHeader1.createCell(14);
cell12.setCellValue("雷击" );
cell12.setCellStyle(cellStylehead);
Cell cell13 = rowHeader1.createCell(15);
cell13.setCellValue("覆冰 " );
cell13.setCellStyle(cellStylehead);
Cell cell14 = rowHeader1.createCell(16);
cell14.setCellValue("异常 " );
cell14.setCellStyle(cellStylehead);
Cell cell15 = rowHeader1.createCell(17);
cell15.setCellValue("舞动 " );
cell15.setCellStyle(cellStylehead);
Cell cell16 = rowHeader1.createCell(18);
cell16.setCellValue("主变容量(MVA)" );
cell16.setCellStyle(cellStylehead);
Cell cell17 = rowHeader1.createCell(19);
cell17.setCellValue("主变压器(台)" );
cell17.setCellStyle(cellStylehead);
Cell cell18 = rowHeader1.createCell(20);
cell18.setCellValue("高抗(台)" );
cell18.setCellStyle(cellStylehead);
Cell cell19= rowHeader1.createCell(21);
cell19.setCellValue("500kV开关(台)" );
cell19.setCellStyle(cellStylehead);
Cell cell20= rowHeader1.createCell(22);
cell20.setCellValue("220kV开关(台)" );
cell20.setCellStyle(cellStylehead);
Cell cell21= rowHeader1.createCell(23);
cell21.setCellValue("电流互感器(台)" );
cell21.setCellStyle(cellStylehead);
Cell cell22= rowHeader1.createCell(24);
cell22.setCellValue("电压互感器(台)" );
cell22.setCellStyle(cellStylehead);
Cell cell23= rowHeader1.createCell(25);
cell23.setCellValue("是否GIS变电站");
cell23.setCellStyle(cellStylehead);
Cell cell24= rowHeader1.createCell(26);
cell24.setCellValue("设备编码");
cell24.setCellStyle(cellStylehead);
/*Cell cell25= rowHeader1.createCell(27);
cell25.setCellValue("资产编码");
cell25.setCellStyle(cellStylehead);
Cell cell26= rowHeader1.createCell(28);
cell26.setCellValue("实物ID");
cell26.setCellStyle(cellStylehead);*/
Cell cell25 = rowHeader1.createCell(27);
cell25.setCellValue("异动日期" );
cell25.setCellStyle(cellStylehead);
Cell cell26= rowHeader1.createCell(28);
cell26.setCellValue("异动情况");
cell26.setCellStyle(cellStylehead);
Cell cell27= rowHeader1.createCell(29);
cell27.setCellValue("异动依据");
cell27.setCellStyle(cellStylehead);
Cell cell28= rowHeader1.createCell(30);
cell28.setCellValue("备注");
cell28.setCellStyle(cellStylehead);
Cell cell29= rowHeader1.createCell(31);
cell29.setCellValue("简介");
cell29.setCellStyle(cellStylehead);
for (int i = 0; i <list.size(); i++) {
Substation substation = list.get(i);
Row row111 = sheet.createRow(1+i);
row111.setHeightInPoints(14);// 设置行高
Cell c99 = row111.createCell(0);
c99.setCellValue((i+1)+"");
c99.setCellStyle(cellStyleBody);
Cell c0 = row111.createCell(1);
c0.setCellValue(substation.getSubstationName());
c0.setCellStyle(cellStyleBody);
Cell c100 = row111.createCell(2);
c100.setCellValue(substation.getNationalassets());
c100.setCellStyle(cellStyleBody);
Cell c1 = row111.createCell(3);
c1.setCellValue(substation.getArea());
c1.setCellStyle(cellStyleBody);
Cell c2 = row111.createCell(4);
c2.setCellValue(substation.getMaintainUnit());
c2.setCellStyle(cellStyleBody);
Cell c3 = row111.createCell(5);
c3.setCellValue(substation.getVoltageLevel());
c3.setCellStyle(cellStyleBody);
Cell c4 = row111.createCell(6);
c4.setCellValue(substation.getRunStatus());
c4.setCellStyle(cellStyleBody);
Cell c5 = row111.createCell(7);
c5.setCellValue(substation.getDeliveryDate());
c5.setCellStyle(cellStyleBody);
Cell c6 = row111.createCell(8);
c6.setCellValue(substation.getSubstationType());
c6.setCellStyle(cellStyleBody);
Cell c7 = row111.createCell(9);
c7.setCellValue(substation.getImportantLevel());
c7.setCellStyle(cellStyleBody);
Cell c8 = row111.createCell(10);
c8.setCellValue(substation.getSafesum());
c8.setCellStyle(cellStyleBody);
Cell c9 = row111.createCell(11);
c9.setCellValue(substation.getQualitysum());
c9.setCellStyle(cellStyleBody);
Cell c10 = row111.createCell(12);
c10.setCellValue(substation.getQualityproblemsum());
c10.setCellStyle(cellStyleBody);
Cell c11 = row111.createCell(13);
c11.setCellValue(substation.getMountionsum());
c11.setCellStyle(cellStyleBody);
Cell c12 = row111.createCell(14);
c12.setCellValue(substation.getThundersum());
c12.setCellStyle(cellStyleBody);
Cell c13 = row111.createCell(15);
c13.setCellValue(substation.getIcingsum());
c13.setCellStyle(cellStyleBody);
Cell c14 = row111.createCell(16);
c14.setCellValue(substation.getElectricexception());
c14.setCellStyle(cellStyleBody);
Cell c15 = row111.createCell(17);
c15.setCellValue(substation.getDancingsum());
c15.setCellStyle(cellStyleBody);
Cell c16 = row111.createCell(18);
c16.setCellValue(substation.getMaintransCapacity());
c16.setCellStyle(cellStyleBody);
Cell c17 = row111.createCell(19);
c17.setCellValue(substation.getMaintransNum());
c17.setCellStyle(cellStyleBody);
Cell c18 = row111.createCell(20);
c18.setCellValue(substation.getHighresistNum());
c18.setCellStyle(cellStyleBody);
Cell c19 = row111.createCell(21);
c19.setCellValue(substation.getFivezzSwitch());
c19.setCellStyle(cellStyleBody);
Cell c20 = row111.createCell(22);
c20.setCellValue(substation.getTwotzSwitch());
c20.setCellStyle(cellStyleBody);
Cell c21 = row111.createCell(23);
c21.setCellValue(substation.getCurrentTransformer());
c21.setCellStyle(cellStyleBody);
Cell c22 = row111.createCell(24);
c22.setCellValue(substation.getVoltageTransformer());
c22.setCellStyle(cellStyleBody);
Cell c23 = row111.createCell(25);
c23.setCellValue(substation.getIsGis());
c23.setCellStyle(cellStyleBody);
Cell c24 = row111.createCell(26);
c24.setCellValue(substation.getEquipmentCode());
c24.setCellStyle(cellStyleBody);
/*Cell c25 = row111.createCell(27);
c25.setCellValue(substation.getAssetsNum());
c25.setCellStyle(cellStyleBody);
Cell c26 = row111.createCell(28);
c26.setCellValue(substation.getMaterialId());
c26.setCellStyle(cellStyleBody);*/
Cell c25 = row111.createCell(27);
c25.setCellValue(substation.getRegisterTime());
c25.setCellStyle(cellStyleBody);
Cell c26 = row111.createCell(28);
c26.setCellValue(substation.getDifferentDynamic());
c26.setCellStyle(cellStyleBody);
Cell c27 = row111.createCell(29);
c27.setCellValue(substation.getDifferentBasis());
c27.setCellStyle(cellStyleBody);
Cell c28 = row111.createCell(30);
c28.setCellValue(substation.getRemark());
c28.setCellStyle(cellStyleBody);
Cell c29 = row111.createCell(31);
c29.setCellValue(substation.getIntroduction());
c29.setCellStyle(cellStyleBody);
}
String fileName = "变电站概况"
+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
+ ".xls";
String fileNamed=URLEncoder.encode(fileName,"UTF-8");
response.reset();
response.setHeader("Content-disposition","attachment;fileName="+fileNamed);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
OutputStream output=response.getOutputStream();
wb.write(output);
output.flush();
output.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public List<SubstationDevice> findDatas(String tableType, String dvName, String year, String ageLimit,
String substationId, String ids) {
String sql="SELECT dev.*,(SELECT sub.SUBSTATION_NAME FROM CCB_SUBSTATION sub WHERE "
+ " sub.SUBSTATION_ID=dev.SUBSTATION_ID) SUBSTATION_NAME "+
" FROM CCB_SUBSTATION_DEVICE dev WHERE 1=1 ";
if(dvName!=null&&!dvName.equals("")){
sql+=" and dev.DEVICE_NAME like '%"+ dvName +"%' ";
}
if(substationId!=null&&!substationId.equals("")){
sql+=" and dev.SUBSTATION_ID='"+ substationId +"'";
}
if(tableType!=null&&!tableType.equals("")){
sql+=" and dev.DEVICE_MARK='"+tableType +"'";
}
if(year!=null&&!year.equals("")){
sql+=" and TO_CHAR(dev.DELIVERY_DATE,'yyyy')='"+ year+"'";
}
if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("10")){
lowage = "0";
}else if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}else if(ageLimit.equals("40")){
lowage = "30";
}else if(ageLimit.equals("50")){
lowage = "40";
}else if(ageLimit.equals("60")){
lowage = "50";
}
sql+="and months_between(sysdate,to_date(SUBSTR(DELIVERY_DATE,1,10),'yyyy-MM-dd'))/12 <='"+ ageLimit+"' ";
sql+="and months_between(sysdate,to_date(SUBSTR(DELIVERY_DATE,1,10),'yyyy-MM-dd'))/12 >'"+ lowage+"' ";
}
if(ids != null && !"".equals(ids)){
String[] idList = ids.split(",");
String standardIds = "";
for(int i=0; i < idList.length; i++){
if(i != idList.length-1){
standardIds += "'" + idList[i] + "'," ;
}else{
standardIds += "'" + idList[i] + "'" ;
}
}
sql += "AND DEVICE_ID in (" + standardIds + ") ";
}
/*if(ageLimit!=null&&!ageLimit.equals("")){
String lowage = "0";
if(ageLimit.equals("20")){
lowage = "10";
}else if(ageLimit.equals("30")){
lowage = "20";
}
sql+=" and (to_char(sysdate,'yyyy') - to_char(DELIVERY_DATE,'yyyy')) <='"+ ageLimit+"'";
sql+=" and (to_char(sysdate,'yyyy')-to_char(DELIVERY_DATE,'yyyy')) >'"+ lowage+"'";
}*/
return jdbcTemplate.query(sql, new SubstationdeviceRowMapper());
}
//数据导出
public String subDeviceexport(HttpServletRequest equest, HttpServletResponse response, List<SubstationDevice>list) {
Workbook wb = null;
try {
//创建一个名称为"变电站概况"的sheet
wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("变电站设备");
sheet.setDefaultColumnWidth(18);// 设置默认列宽
sheet.setColumnWidth(0, 54*50);//设置第一列的宽度
Font font = wb.createFont();// 字体
font.setBoldweight(Font.BOLDWEIGHT_BOLD);// 加粗
CellStyle cellStylehead = wb.createCellStyle();// 表头样式
cellStylehead.setFont(font);// 设置字体样式
cellStylehead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
cellStylehead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStylehead.setWrapText(true);// 自动换行
// 设置边框
cellStylehead.setBorderTop(CellStyle.BORDER_THIN);
cellStylehead.setBorderRight(CellStyle.BORDER_THIN);
cellStylehead.setBorderBottom(CellStyle.BORDER_THIN);
cellStylehead.setBorderLeft(CellStyle.BORDER_THIN);
//设置表头颜色
cellStylehead.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStylehead.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 表体样式
CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
cellStyleBody.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStyleBody.setWrapText(true);// 自动换行
// 设置边框
cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
// 右对齐
CellStyle cellStyleRight = wb.createCellStyle();// 右对齐样式
cellStyleRight.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
cellStyleRight.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
cellStyleRight.setWrapText(true);// 自动换行
cellStyleRight.setBorderTop(CellStyle.BORDER_THIN);
cellStyleRight.setBorderRight(CellStyle.BORDER_THIN);
cellStyleRight.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleRight.setBorderLeft(CellStyle.BORDER_THIN);
//居中对齐
CellStyle cellStyleCENTER = wb.createCellStyle();//右对齐样式
cellStyleCENTER.setAlignment(CellStyle.ALIGN_CENTER);//水平对齐
cellStyleCENTER.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对齐
cellStyleCENTER.setWrapText(true);//自动换行
cellStyleCENTER.setBorderTop(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderRight(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleCENTER.setBorderLeft(CellStyle.BORDER_THIN);
Row rowHeader1 = sheet.createRow(0);
Cell cell99 = rowHeader1.createCell(0);
cell99.setCellValue("序号");
cell99.setCellStyle(cellStylehead);
Cell cell00 = rowHeader1.createCell(1);
cell00.setCellValue("变电站名称");
cell00.setCellStyle(cellStylehead);
Cell cell01 = rowHeader1.createCell(2);
cell01.setCellValue("设备名称");
cell01.setCellStyle(cellStylehead);
Cell cell02 = rowHeader1.createCell(3);
cell02.setCellValue("电压等级");
cell02.setCellStyle(cellStylehead);
Cell cell03 = rowHeader1.createCell(4);
cell03.setCellValue("设备类型名称" );
cell03.setCellStyle(cellStylehead);
Cell cell04 = rowHeader1.createCell(5);
cell04.setCellValue("间隔单元");
cell04.setCellStyle(cellStylehead);
Cell cell05 = rowHeader1.createCell(6);
cell05.setCellValue("型号" );
cell05.setCellStyle(cellStylehead);
Cell cell06 = rowHeader1.createCell(7);
cell06.setCellValue("生产厂家" );
cell06.setCellStyle(cellStylehead);
Cell cell07 = rowHeader1.createCell(8);
cell07.setCellValue("投运日期" );
cell07.setCellStyle(cellStylehead);
Cell cell08 = rowHeader1.createCell(9);
cell08.setCellValue("最近投运日期" );
cell08.setCellStyle(cellStylehead);
Cell cell09 = rowHeader1.createCell(10);
cell09.setCellValue("设备状态" );
cell09.setCellStyle(cellStylehead);
Cell cell10 = rowHeader1.createCell(11);
cell10.setCellValue("PMS设备编码" );
cell10.setCellStyle(cellStylehead);
Cell cell11 = rowHeader1.createCell(12);
cell11.setCellValue("分部资产编码" );
cell11.setCellStyle(cellStylehead);
Cell cell12 = rowHeader1.createCell(13);
cell12.setCellValue("实物ID" );
cell12.setCellStyle(cellStylehead);
Cell cell13 = rowHeader1.createCell(14);
cell13.setCellValue("省公司项目编码" );
cell13.setCellStyle(cellStylehead);
Cell cell14 = rowHeader1.createCell(15);
cell14.setCellValue("分部项目编码" );
cell14.setCellStyle(cellStylehead);
Cell cell15 = rowHeader1.createCell(16);
cell15.setCellValue("物料编码" );
cell15.setCellStyle(cellStylehead);
Cell cell16 = rowHeader1.createCell(17);
cell16.setCellValue("省公司PM编码" );
cell16.setCellStyle(cellStylehead);
Cell cell17 = rowHeader1.createCell(18);
cell17.setCellValue("分部PM编码" );
cell17.setCellStyle(cellStylehead);
Cell cell18 = rowHeader1.createCell(19);
cell18.setCellValue("省公司资产编码" );
cell18.setCellStyle(cellStylehead);
Cell cell19 = rowHeader1.createCell(20);
cell19.setCellValue("资产状态" );
cell19.setCellStyle(cellStylehead);
Cell cell20 = rowHeader1.createCell(21);
cell20.setCellValue("报废时间" );
cell20.setCellStyle(cellStylehead);
Cell cell21 = rowHeader1.createCell(22);
cell21.setCellValue("资产性质" );
cell21.setCellStyle(cellStylehead);
Cell cell22 = rowHeader1.createCell(23);
cell22.setCellValue("资产单位" );
cell22.setCellStyle(cellStylehead);
Cell cell23 = rowHeader1.createCell(24);
cell23.setCellValue("异动日期" );
cell23.setCellStyle(cellStylehead);
Cell cell24 = rowHeader1.createCell(25);
cell24.setCellValue("异动情况" );
cell24.setCellStyle(cellStylehead);
Cell cell25 = rowHeader1.createCell(26);
cell25.setCellValue("异动依据" );
cell25.setCellStyle(cellStylehead);
Cell cell26 = rowHeader1.createCell(27);
cell26.setCellValue("备注" );
cell26.setCellStyle(cellStylehead);
for (int i = 0; i < list.size(); i++) {
SubstationDevice SubstationDevice = list.get(i);
Row row111 = sheet.createRow(1+i);
row111.setHeightInPoints(14);// 设置行高
Cell c99 = row111.createCell(0);
c99.setCellValue((i+1)+"");
c99.setCellStyle(cellStyleBody);
Cell c0 = row111.createCell(1);
c0.setCellValue(SubstationDevice.getSubstationName());
c0.setCellStyle(cellStyleBody);
Cell c1 = row111.createCell(2);
c1.setCellValue(SubstationDevice.getDeviceName());
c1.setCellStyle(cellStyleBody);
Cell c2 = row111.createCell(3);
c2.setCellValue(SubstationDevice.getVoltageLevel());
c2.setCellStyle(cellStyleBody);
Cell c3 = row111.createCell(4);
c3.setCellValue(SubstationDevice.getDeviceType());
c3.setCellStyle(cellStyleBody);
Cell c4 = row111.createCell(5);
c4.setCellValue(SubstationDevice.getGapUnit());
c4.setCellStyle(cellStyleBody);
Cell c5 = row111.createCell(6);
c5.setCellValue(SubstationDevice.getModel());
c5.setCellStyle(cellStyleBody);
Cell c6 = row111.createCell(7);
c6.setCellValue(SubstationDevice.getProduceFactory());
c6.setCellStyle(cellStyleBody);
Cell c7 = row111.createCell(8);
c7.setCellValue(SubstationDevice.getDeliveryDate());
c7.setCellStyle(cellStyleBody);
Cell c8 = row111.createCell(9);
c8.setCellValue(SubstationDevice.getRecentRundate());
c8.setCellStyle(cellStyleBody);
Cell c9 = row111.createCell(10);
c9.setCellValue(SubstationDevice.getDeviceStatus());
c9.setCellStyle(cellStyleBody);
Cell c10 = row111.createCell(11);
c10.setCellValue(SubstationDevice.getDeviceCode());
c10.setCellStyle(cellStyleBody);
Cell c11 = row111.createCell(12);
c11.setCellValue(SubstationDevice.getAssetsNum());
c11.setCellStyle(cellStyleBody);
Cell c12 = row111.createCell(13);
c12.setCellValue(SubstationDevice.getMaterialId());
c12.setCellStyle(cellStyleBody);
Cell c13 = row111.createCell(14);
c13.setCellValue(SubstationDevice.getSjXmbm());
c13.setCellStyle(cellStyleBody);
Cell c14 = row111.createCell(15);
c14.setCellValue(SubstationDevice.getFbXmbm());
c14.setCellStyle(cellStyleBody);
Cell c15 = row111.createCell(16);
c15.setCellValue(SubstationDevice.getWlbm());
c15.setCellStyle(cellStyleBody);
Cell c16 = row111.createCell(17);
c16.setCellValue(SubstationDevice.getSjPmbm());
c16.setCellStyle(cellStyleBody);
Cell c17 = row111.createCell(18);
c17.setCellValue(SubstationDevice.getFbPmbm());
c17.setCellStyle(cellStyleBody);
Cell c18 = row111.createCell(19);
c18.setCellValue(SubstationDevice.getSjZcbm());
c18.setCellStyle(cellStyleBody);
Cell c19 = row111.createCell(20);
c19.setCellValue(SubstationDevice.getZczt());
c19.setCellStyle(cellStyleBody);
Cell c20 = row111.createCell(21);
c20.setCellValue(SubstationDevice.getScrapDate());
c20.setCellStyle(cellStyleBody);
Cell c21 = row111.createCell(22);
c21.setCellValue(SubstationDevice.getAssetsNature());
c21.setCellStyle(cellStyleBody);
Cell c22 = row111.createCell(23);
c22.setCellValue(SubstationDevice.getAssetsUnit());
c22.setCellStyle(cellStyleBody);
Cell c23 = row111.createCell(24);
c23.setCellValue(SubstationDevice.getRegisterTime());
c23.setCellStyle(cellStyleBody);
Cell c24 = row111.createCell(25);
c24.setCellValue(SubstationDevice.getDifferentDynamic());
c24.setCellStyle(cellStyleBody);
Cell c25 = row111.createCell(26);
c25.setCellValue(SubstationDevice.getDifferentBasis());
c25.setCellStyle(cellStyleBody);
Cell c26 = row111.createCell(27);
c26.setCellValue(SubstationDevice.getRemark());
c26.setCellStyle(cellStyleBody);
}
String fileName = "变电站设备明细"
+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
+ ".xls";
String fileNamed=URLEncoder.encode(fileName,"UTF-8");
response.reset();
response.setHeader("Content-disposition","attachment;fileName="+fileNamed);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
OutputStream output=response.getOutputStream();
wb.write(output);
output.flush();
output.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
*
* @param fileNname
* @return
* @throws UnsupportedEncodingException
*/
public File downLoadWord(String fileNname) throws UnsupportedEncodingException {
Map<String,Object> dataMap=new HashMap<String,Object>();
Date dates = new Date();
Calendar cal = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String currentDate = dateFormat.format(dates);
String year = String.format("%tY", dates);
int month = cal.get(Calendar.MONTH)+1;
String day = String.format("%td", dates);
dataMap.put("a1",year+"年"+month+"月"+day+"日");
//1、获取【河南、湖北、湖南、江西四省线路条数合计值】
int a2 = lineinfoDao.findLineCount0();
dataMap.put("a2", a2);
//2、【河南、湖北、湖南、江西四省线路长度合计值】
int a3 = lineinfoDao.findLineLength0();
dataMap.put("a3", a3);
//3、【河南、湖北、湖南、江西四省线路直属变电站数量】
int a31 = findStationCount0();
dataMap.put("a31", a31);
//4、【河南、湖北、湖南、江西四省主变压器数量合计值】
int a4 = findStationCount1();
dataMap.put("a4", a4);
//5、【河南、湖北、湖南、江西四省主变容量合计值】
int a5 = findStationCount2();
dataMap.put("a5", a5);
//6、【华中直属资产地理位置图】
String imagePath = "D:\\ditu0.png";
dataMap.put("image", getImageStr(imagePath));
//7、【取输电线路基本情况中湖北线路条数】
int a6 = lineinfoDao.findLineCount1("湖北");
dataMap.put("a6", a6);
//8、【取输电线路基本情况中湖北线路长度】
int a8 = lineinfoDao.findLineLength1("湖北");
dataMap.put("a7", a8);
//9、【取变电设备基本情况中湖北变电站数量】
int a8_1 = findStationCount3("湖北");
dataMap.put("a8", a8_1);
//10、【取变电设备基本情况中湖北主变压器台数】
int a9 = findStationCount4("湖北");
dataMap.put("a9", a9);
//11、【取变电设备基本情况中湖北主变容量】
int a10 = findStationCount5("湖北");
dataMap.put("a10", a10);
//12、【取输电线路基本情况中河南线路条数】
int a11 = lineinfoDao.findLineCount1("河南");
dataMap.put("a11", a11);
//13、【取输电线路基本情况中河南线路长度】
int a12 = lineinfoDao.findLineLength1("河南");
dataMap.put("a12", a12);
//14、【取变电设备基本情况中河南变电站数量】
int a13 = findStationCount3("河南");
dataMap.put("a13", a13);
//15、【取变电设备基本情况中河南主变压器台数】
int a14 = findStationCount4("河南");
dataMap.put("a14", a14);
//16、【取变电设备基本情况中河南主变容量】
int a15 = findStationCount5("河南");
dataMap.put("a15", a15);
//17、【取输电线路基本情况中湖南线路条数】
int a16 = lineinfoDao.findLineCount1("湖南");
dataMap.put("a16", a16);
//18、【取输电线路基本情况中湖南线路长度】
int a17 = lineinfoDao.findLineLength1("湖南");
dataMap.put("a17", a17);
//19、【取变电设备基本情况中湖南变电站数量】
int a18 = findStationCount3("湖南");
dataMap.put("a18", a18);
//20、【取变电设备基本情况中湖南主变压器台数】
int a181 = findStationCount4("湖南");
dataMap.put("a181", a181);
//21、【取变电设备基本情况中湖南主变容量】
int a19 = findStationCount5("湖南");
dataMap.put("a19", a19);
//22、【取输电线路基本情况中江西线路条数】
int a20 = lineinfoDao.findLineCount1("江西");
dataMap.put("a20", a20);
//23、【取输电线路基本情况中江西线路长度】
int a21 = lineinfoDao.findLineLength1("江西");
dataMap.put("a21", a21);
//24、【取变电设备基本情况中江西变电站数量】
int a22 = findStationCount3("江西");
dataMap.put("a22", a22);
//25、【取变电设备基本情况中江西主变压器台数】
int a23 = findStationCount4("江西");
dataMap.put("a23", a23);
//26、【取变电设备基本情况中江西主变容量】
int a24 = findStationCount5("江西");
dataMap.put("a24", a24);
//27、【当前年度】
dataMap.put("Y1", year+"年");
//28、【取资产统计分析中的变电设备基本情况表】
List<Substationtype> getprovicetype = substationtypeDao.getprovicetype0(currentDate);
if(getprovicetype.size() == 0){
dataMap.put("a01", "0");
dataMap.put("b01", "0");
dataMap.put("c01", "0");
dataMap.put("d01", "0");
dataMap.put("e01", "0");
dataMap.put("f01", "0");
dataMap.put("g01", "0");
dataMap.put("h01", "0");
dataMap.put("a02", "0");
dataMap.put("b02", "0");
dataMap.put("c02", "0");
dataMap.put("d02", "0");
dataMap.put("e02", "0");
dataMap.put("f02", "0");
dataMap.put("g02", "0");
dataMap.put("h02", "0");
}
for (int i = 0; i < getprovicetype.size(); i++) {
Substationtype substationtype = getprovicetype.get(i);
if(i==1){
dataMap.put("a02", substationtype.getStationcount());
dataMap.put("b02", substationtype.getMaintransCapacity());
dataMap.put("c02", substationtype.getMaintransNum());
dataMap.put("d02", substationtype.getHighresistNum());
dataMap.put("e02", substationtype.getFivezzSwitch());
dataMap.put("f02", substationtype.getTwotzSwitch());
dataMap.put("g02", substationtype.getCurrentTransformer());
dataMap.put("h02", substationtype.getVoltageTransformer());
}else if(i==2){
dataMap.put("a03", substationtype.getStationcount());
dataMap.put("b03", substationtype.getMaintransCapacity());
dataMap.put("c03", substationtype.getMaintransNum());
dataMap.put("d03", substationtype.getHighresistNum());
dataMap.put("e03", substationtype.getFivezzSwitch());
dataMap.put("f03", substationtype.getTwotzSwitch());
dataMap.put("g03", substationtype.getCurrentTransformer());
dataMap.put("h03", substationtype.getVoltageTransformer());
}else if(i==3){
dataMap.put("a04", substationtype.getStationcount());
dataMap.put("b04", substationtype.getMaintransCapacity());
dataMap.put("c04", substationtype.getMaintransNum());
dataMap.put("d04", substationtype.getHighresistNum());
dataMap.put("e04", substationtype.getFivezzSwitch());
dataMap.put("f04", substationtype.getTwotzSwitch());
dataMap.put("g04", substationtype.getCurrentTransformer());
dataMap.put("h04", substationtype.getVoltageTransformer());
}else if(i==4){
dataMap.put("a05", substationtype.getStationcount());
dataMap.put("b05", substationtype.getMaintransCapacity());
dataMap.put("c05", substationtype.getMaintransNum());
dataMap.put("d05", substationtype.getHighresistNum());
dataMap.put("e05", substationtype.getFivezzSwitch());
dataMap.put("f05", substationtype.getTwotzSwitch());
dataMap.put("g05", substationtype.getCurrentTransformer());
dataMap.put("h05", substationtype.getVoltageTransformer());
}else if(i==0){
dataMap.put("a01", substationtype.getStationcount());
dataMap.put("b01", substationtype.getMaintransCapacity());
dataMap.put("c01", substationtype.getMaintransNum());
dataMap.put("d01", substationtype.getHighresistNum());
dataMap.put("e01", substationtype.getFivezzSwitch());
dataMap.put("f01", substationtype.getTwotzSwitch());
dataMap.put("g01", substationtype.getCurrentTransformer());
dataMap.put("h01", substationtype.getVoltageTransformer());
}
}
//29、【取变电站概况表，但要选择直属】
List<Substation> stationOption = getStationOption();
List<Map<String,Object>> list1 = new ArrayList<Map<String,Object>>();
if(stationOption.size() == 0){
Map<String,Object> map = new HashMap<String,Object>();
map.put("Num", 1);
map.put("station", "-");
map.put("area", "-");
map.put("tyrq", "-");
map.put("zbrl", "-");
map.put("zbyq", "-");
map.put("gk", "-");
map.put("five", "-");
map.put("two", "-");
map.put("dy", "-");
map.put("dl", "-");
}
for (int i = 0; i < stationOption.size(); i++) {
Map<String,Object> map = new HashMap<String,Object>();
Substation substation = stationOption.get(i);
map.put("Num", i+1);
map.put("station", substation.getSubstationName()==null?"":substation.getSubstationName());
map.put("area", substation.getArea()==null?"":substation.getArea());
map.put("tyrq", substation.getDeliveryDate()==null?"":substation.getDeliveryDate());
map.put("zbrl", substation.getMaintransCapacity());
map.put("zbyq", substation.getMaintransNum());
map.put("gk", substation.getHighresistNum());
map.put("five", substation.getFivezzSwitch());
map.put("two", substation.getTwotzSwitch());
map.put("dy", substation.getCurrentTransformer());
map.put("dl", substation.getVoltageTransformer());
list1.add(map);
}
dataMap.put("list1", list1);
//30、【当前年度】
dataMap.put("Y2", year+"年");
//31、【取资产统计分析中的变电设备运行年限表】
List<Substationtype> getyeartype = substationtypeDao.getyeartype(currentDate);
for (int i = 0; i < getyeartype.size(); i++) {
Substationtype substationtype = getyeartype.get(i);
if(i == 0){
dataMap.put("a001", substationtype.getYear1());
dataMap.put("b001", substationtype.getYear2());
dataMap.put("c001", substationtype.getYear3());
dataMap.put("d001", substationtype.getYear4());
dataMap.put("e001", substationtype.getYear5());
}if(i == 1){
dataMap.put("a002", substationtype.getYear1());
dataMap.put("b002", substationtype.getYear2());
dataMap.put("c002", substationtype.getYear3());
dataMap.put("d002", substationtype.getYear4());
dataMap.put("e002", substationtype.getYear5());
}if(i == 2){
dataMap.put("a003", substationtype.getYear1());
dataMap.put("b003", substationtype.getYear2());
dataMap.put("c003", substationtype.getYear3());
dataMap.put("d003", substationtype.getYear4());
dataMap.put("e003", substationtype.getYear5());
}if(i == 3){
dataMap.put("a004", substationtype.getYear1());
dataMap.put("b004", substationtype.getYear2());
dataMap.put("c004", substationtype.getYear3());
dataMap.put("d004", substationtype.getYear4());
dataMap.put("e004", substationtype.getYear5());
}if(i == 4){
dataMap.put("a005", substationtype.getYear1());
dataMap.put("b005", substationtype.getYear2());
dataMap.put("c005", substationtype.getYear3());
dataMap.put("d005", substationtype.getYear4());
dataMap.put("e005", substationtype.getYear5());
}if(i == 5){
dataMap.put("a006", substationtype.getYear1());
dataMap.put("b006", substationtype.getYear2());
dataMap.put("c006", substationtype.getYear3());
dataMap.put("d006", substationtype.getYear4());
dataMap.put("e006", substationtype.getYear5());
}if(i == 6){
dataMap.put("a007", substationtype.getYear1());
dataMap.put("b007", substationtype.getYear2());
dataMap.put("c007", substationtype.getYear3());
dataMap.put("d007", substationtype.getYear4());
dataMap.put("e007", substationtype.getYear5());
}if(i == 7){
dataMap.put("a008", substationtype.getYear1());
dataMap.put("b008", substationtype.getYear2());
dataMap.put("c008", substationtype.getYear3());
dataMap.put("d008", substationtype.getYear4());
dataMap.put("e008", substationtype.getYear5());
}if(i == 8){
dataMap.put("a009", substationtype.getYear1());
dataMap.put("b009", substationtype.getYear2());
dataMap.put("c009", substationtype.getYear3());
dataMap.put("d009", substationtype.getYear4());
dataMap.put("e009", substationtype.getYear5());
}if(i == 9){
dataMap.put("a010", substationtype.getYear1());
dataMap.put("b010", substationtype.getYear2());
dataMap.put("c010", substationtype.getYear3());
dataMap.put("d010", substationtype.getYear4());
dataMap.put("e010", substationtype.getYear5());
}if(i == 10){
dataMap.put("a011", substationtype.getYear1());
dataMap.put("b011", substationtype.getYear2());
dataMap.put("c011", substationtype.getYear3());
dataMap.put("d011", substationtype.getYear4());
dataMap.put("e011", substationtype.getYear5());
}if(i == 11){
dataMap.put("a012", substationtype.getYear1());
dataMap.put("b012", substationtype.getYear2());
dataMap.put("c012", substationtype.getYear3());
dataMap.put("d012", substationtype.getYear4());
dataMap.put("e012", substationtype.getYear5());
}if(i == 12){
dataMap.put("a013", substationtype.getYear1());
dataMap.put("b013", substationtype.getYear2());
dataMap.put("c013", substationtype.getYear3());
dataMap.put("d013", substationtype.getYear4());
dataMap.put("e013", substationtype.getYear5());
}
}
//32、【取属于河南直属变电站的相关信息】
List<Substation> stationOption0 = getStationOption0("河南");
List<Map<String,Object>> list2 = new ArrayList<Map<String,Object>>();
if(stationOption0.size() == 0){
Map<String,Object> map = new HashMap<String,Object>();
map.put("Hl01", "未查询到相关数据");
map.put("Hl02", "未查询到相关数据");
list2.add(map);
}
for (int i = 0; i < stationOption0.size(); i++) {
Map<String,Object> map = new HashMap<String,Object>();
Substation substation = stationOption0.get(i);
map.put("Hl01", i+1+"、"+substation.getSubstationName());
map.put("Hl02", substation.getIntroduction()==null?"":substation.getIntroduction());
list2.add(map);
}
dataMap.put("list2", list2);
//33、【取属于湖北直属变电站的相关信息】
List<Substation> stationOption1 = getStationOption0("湖北");
List<Map<String,Object>> list3 = new ArrayList<Map<String,Object>>();
if(stationOption1.size() == 0){
Map<String,Object> map = new HashMap<String,Object>();
map.put("Hb01", "未查询到相关数据");
map.put("Hb02", "未查询到相关数据");
list3.add(map);
}
for (int i = 0; i < stationOption1.size(); i++) {
Map<String,Object> map = new HashMap<String,Object>();
Substation substation = stationOption1.get(i);
map.put("Hb01", i+1+"、"+substation.getSubstationName());
map.put("Hb02", substation.getIntroduction()==null?"":substation.getIntroduction());
list3.add(map);
}
dataMap.put("list3", list3);
//34、【取属于湖南直属变电站的相关信息】
List<Substation> stationOption2 = getStationOption0("湖南");
List<Map<String,Object>> list4 = new ArrayList<Map<String,Object>>();
if(stationOption2.size() == 0){
Map<String,Object> map = new HashMap<String,Object>();
map.put("Hn01", "未查询到相关数据");
map.put("Hn02", "未查询到相关数据");
list4.add(map);
}
for (int i = 0; i < stationOption2.size(); i++) {
Map<String,Object> map = new HashMap<String,Object>();
Substation substation = stationOption2.get(i);
map.put("Hn01", i+1+"、"+substation.getSubstationName());
map.put("Hn02", substation.getIntroduction()==null?"":substation.getIntroduction());
list4.add(map);
}
dataMap.put("list4", list4);
//34、【取属于江西直属变电站的相关信息】
List<Substation> stationOption3 = getStationOption0("江西");
List<Map<String,Object>> list5 = new ArrayList<Map<String,Object>>();
if(stationOption3.size() == 0){
Map<String,Object> map = new HashMap<String,Object>();
map.put("jx01", "未查询到相关数据");
map.put("jx02", "未查询到相关数据");
list5.add(map);
}
for (int i = 0; i < stationOption3.size(); i++) {
Map<String,Object> map = new HashMap<String,Object>();
Substation substation = stationOption3.get(i);
map.put("jx01", i+1+"、"+substation.getSubstationName());
map.put("jx02", substation.getIntroduction()==null?"":substation.getIntroduction());
list5.add(map);
}
dataMap.put("list5", list5);
/* Map<String,List<SubStationVo>> maps=new HashMap<String,List<SubStationVo>>();
List<SubStationVo> subStationList = new ArrayList<SubStationVo>();
//查询线路名称和简介和图片路径
List<Map<String,Object>> subStationWords = getSubStationWord();
int i=0;
//装载变电站
if (subStationWords.size() > 0) {
for (Map<String, Object> map : subStationWords) {
SubStationVo subStationVo = new SubStationVo();
subStationVo.setSubstationName(
(String) map.get("substation_name") == null ? "" : (String) map.get("substation_name"));
subStationVo.setIntroduction((String) map.get("introduction"));
List<String> filePaths = getFilePaths(map.get("filePath"));
if (filePaths.size() > 0) {
for (int j = 0; j < filePaths.size(); j++) {
if (j == 0) {
subStationVo.setImage1(getImageStr(filePaths.get(j) == null ? "" : filePaths.get(j)));
subStationVo.setIndex1(String.valueOf(i++));
} else if (j == 1) {
subStationVo.setIndex2(String.valueOf(i++));
subStationVo.setImage2(getImageStr(filePaths.get(j) == null ? "" : filePaths.get(j)));
} else if (j == 2) {
subStationVo.setIndex3(String.valueOf(i++));
subStationVo.setImage3(getImageStr(filePaths.get(j) == null ? "" : filePaths.get(j)));
}
}
} else {
subStationVo.setIndex1(String.valueOf(i++));
subStationVo.setIndex2(String.valueOf(i++));
subStationVo.setIndex3(String.valueOf(i++));
}
subStationList.add(subStationVo);
}
}
//线路
List<Map<String,Object>> subStationLineWords = getSubStationLineWord();
//装载线路
if(subStationLineWords.size()>0) {
for (Map<String, Object> map : subStationLineWords) {
SubStationVo subStationVo=new SubStationVo();
subStationVo.setSubstationName((String)map.get("line_name")==null?"":(String)map.get("line_name"));
subStationVo.setIntroduction((String)map.get("introduction"));
List<String> filePaths=getFilePaths(map.get("filePath"));
if(filePaths.size()>0) {
for(int j=0;j<filePaths.size();j++) {
if(j==0) {
subStationVo.setIndex1(String.valueOf(i++));
subStationVo.setImage1(getImageStr(filePaths.get(j)==null?"":filePaths.get(j)));
}else if(j==1) {
subStationVo.setIndex2(String.valueOf(i++));
subStationVo.setImage2(getImageStr(filePaths.get(j)==null?"":filePaths.get(j)));
}else if(j==2) {
subStationVo.setIndex3(String.valueOf(i++));
subStationVo.setImage3(getImageStr(filePaths.get(j)==null?"":filePaths.get(j)));
}
}
}else {
subStationVo.setIndex1(String.valueOf(i++));
subStationVo.setIndex2(String.valueOf(i++));
subStationVo.setIndex3(String.valueOf(i++));
}
subStationList.add(subStationVo);
}
}
maps.put("subStationList", subStationList);*/
configuration = new Configuration();
configuration.setDefaultEncoding("UTF-8");
configuration.setClassForTemplateLoading(this.getClass(), "/templates");// 模板文件所在路径
Template t = null;
try {
t = configuration.getTemplate("变电站信息导出版面设计_20190422.ftl","UTF-8"); // 获取模板文件
} catch (IOException e) {
e.printStackTrace();
}
File f = new File(fileNname);
Writer out = null;
try {
//out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f)));
out = new PrintWriter(f,"UTF-8");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
t.process(dataMap, out,ObjectWrapper.BEANS_WRAPPER); // 将填充数据填入模板文件并输出到目标文件
} catch (TemplateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return f;
}
//查询线路信息
private List<Map<String, Object>> getSubStationLineWord() {
String baseSql="select s.line_name,s.introduction, wm_concat(c.file_path) as filePath from ccb_line_info s left join ccb_file_upload c on s.LINE_ID=c.file_id\r\n" +
"where (c.type=1 or c.type is null) and nationalassets = '01' group by s.area,s.line_name,s.introduction";
return jdbcTemplate.queryForList(baseSql.toString());
}
/**
* 循环图片
* @param object
* @return
*/
private List<String> getFilePaths(Object object) {
List<String> filePaths=new ArrayList<>();
String path=(String)object;
//切割
if(!StringUtil.isNullOrEmpty(path)) {
String[] pathStr = path.split(",");
for(int i=0;i<pathStr.length;i++) {
filePaths.add(pathStr[i]);
}
}
return filePaths;
}
/**
*
* @param year
* @return
*/
private List<Map<String, Object>> getSubStationWord() {
String baseSql="select s.substation_name,s.introduction, wm_concat(c.file_path) as filePath from ccb_substation s left join ccb_file_upload c on s.substation_id=c.file_id\r\n" +
"where (c.type=1 or c.type is null) and nationalassets = '01' group by s.area,s.substation_name,s.introduction";
return jdbcTemplate.queryForList(baseSql.toString());
}
/**
* 将图片编译成二进制码的一个方法
* @param path
* @return
*/
private String getImageStr(String path){//图片的存放路径
if(!StringUtil.isNullOrEmpty(path)) {
//路径转换 \ 转 \\
// path=path.replace("\\", "\\\\");
BASE64Encoder encoder = new sun.misc.BASE64Encoder(); //这个类需要手动导出 ，之前sun公司留下来的，暂时先用着吧，之后可能会被删除
File file = new File(path);
if(!file.exists()){
return null;
}
BufferedImage bi = null;
try {
bi = ImageIO.read(file);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(bi, "png", baos);
byte[] bytes = baos.toByteArray();
return encoder.encodeBuffer(bytes).trim();
} catch (IOException e) {
e.printStackTrace();
}
}
return "";
}
public List<Substation> queryTemplated( String subStationname) {
String sql="SELECT * FROM CCB_SUBSTATION WHERE SUBSTATION_NAME='"+subStationname+"'";
return jdbcTemplate.query(sql,new SubstationRowMapper());
}
public Boolean findByName(String substationName){
String queryNum = "SELECT * FROM CCB_SUBSTATION WHERE SUBSTATION_NAME='"+substationName+"'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(queryNum);
if(list != null && list.size() > 0){
return false;
}
return true;
}
public Boolean findBySubstationName(String substationId, String substationName) {
String queryNum = "SELECT SUBSTATION_ID FROM CCB_SUBSTATION WHERE SUBSTATION_NAME='"+substationName+"'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(queryNum);
if(list != null && list.size() > 0){
Map<String, Object> map = list.get(0);
String subId = (String)map.get("SUBSTATION_ID");
if(substationId != null && !substationId.equals(subId)){
return false;
}
}
return true;
}
public List<SubstationDevice> queryTemplates(String subStationId, String deviceName) {
String sql="select * from CCB_SUBSTATION_DEVICE where substation_id = '"+subStationId+"' AND device_name ='"+deviceName+"'";
return jdbcTemplate.query(sql,new SubstationdeviceRowMapper());
}
public Boolean findDeviceByNameAndId(String deviceId, String subStationId, String deviceName) {
String queryNum="select DEVICE_ID from CCB_SUBSTATION_DEVICE where substation_id = '"+subStationId+"' AND device_name ='"+deviceName+"'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(queryNum);
if(list != null && list.size() > 0){
Map<String, Object> map = list.get(0);
String id = (String)map.get("DEVICE_ID");
if(deviceId != null && !deviceId.equals(id)){
return false;
}
}
return true;
}
public String findPath(String fileId, String fileName){
String sql = "SELECT FILE_PATH FROM CCB_FILE_UPLOAD WHERE FILE_ID='"+fileId+"' AND FILE_NAME='"+fileName+"'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
String path = "";
if(list != null && list.size() > 0){
Map<String, Object> map = list.get(0);
path = (String)map.get("FILE_PATH");
}
return path;
}
//1、获取【河南、湖北、湖南、江西四省路直属变电站数量】
public Integer findStationCount0() {
String sql="SELECT COUNT(0) FROM CCB_SUBSTATION WHERE nationalassets = '01' and area in ('河南','湖北','湖南','江西')";
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//2、【河南、湖北、湖南、江西四省直属主变压器数量合计值】
public Integer findStationCount1() {
String sql=" SELECT NVL(SUM(MAINTRANS_NUM),0) FROM CCB_SUBSTATION WHERE nationalassets = '01' and area in ('河南','湖北','湖南','江西')";
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//3、【河南、湖北、湖南、江西四省直属主变压器容量合计值】
public Integer findStationCount2() {
String sql=" SELECT NVL(SUM(MAINTRANS_CAPACITY),0) FROM CCB_SUBSTATION WHERE nationalassets = '01' and area in ('河南','湖北','湖南','江西')";
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//1、获取【各省路直属变电站数量】
public Integer findStationCount3(String province) {
String sql="SELECT COUNT(0) FROM CCB_SUBSTATION WHERE nationalassets = '01' ";
if(!"".equals(province) && !"null".equals(province) && province!= null){
sql += " AND AREA = '"+province+"' ";
}
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//2、【各省直属主变压器数量合计值】
public Integer findStationCount4(String province) {
String sql=" SELECT NVL(SUM(MAINTRANS_NUM),0) FROM CCB_SUBSTATION WHERE nationalassets = '01' ";
if(!"".equals(province) && !"null".equals(province) && province!= null){
sql += " AND AREA = '"+province+"' ";
}
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//3、【各省直属主变压器容量合计值】
public Integer findStationCount5(String province) {
String sql=" SELECT NVL(SUM(MAINTRANS_CAPACITY),0) FROM CCB_SUBSTATION WHERE nationalassets = '01' ";
if(!"".equals(province) && !"null".equals(province) && province!= null){
sql += " AND AREA = '"+province+"' ";
}
int total = jdbcTemplate.queryForObject(sql, Integer.class);
return total;
}
//3、【各省直属主变电站概况】
public List<Substation> getStationOption() {
String sql=" SELECT C.SUBSTATION_NAME,C.AREA,C.DELIVERY_DATE,C.MAINTRANS_CAPACITY,C.MAINTRANS_NUM,C.HIGHRESIST_NUM,C.FIVEZZ_SWITCH,C.TWOTZ_SWITCH,C.CURRENT_TRANSFORMER,C.VOLTAGE_TRANSFORMER FROM CCB_SUBSTATION C WHERE nationalassets = '01' AND area in ('河南','湖北','湖南','江西')";
List<Substation> sub = jdbcTemplate.query(sql, new SubstationRowMapper());
return sub;
}
//3、【各省直属主变电站概况】
public List<Substation> getStationOption0(String province ) {
String sql=" SELECT C.SUBSTATION_NAME,C.AREA,C.INTRODUCTION FROM CCB_SUBSTATION C WHERE nationalassets = '01' ";
if(!"".equals(province) && !"null".equals(province) && province!= null){
sql += " AND C.AREA = '"+province+"' ";
}
List<Substation> sub = jdbcTemplate.query(sql, new SubstationRowMapper());
return sub;
}
//直属线路变电数量
public JSONObject getEchartsZSXLBDSLData(HttpServletRequest request) {
// TODO Auto-generated method stub
JSONObject jsonObject = new JSONObject();
String sql =
"select '河南' as xlname,\n" +
"(select count(1) from CCB_LINE_INFO where NATIONALASSETS='01' and area='河南') as xlcount,\n" +
"'河南' as bdzname,\n" +
"(select count(1) from CCB_SUBSTATION where NATIONALASSETS='01' and area='河南') as bdzcount\n" +
"from dual\n" +
"union all\n" +
"select '湖北' as xlname,\n" +
"(select count(1) from CCB_LINE_INFO where NATIONALASSETS='01' and area='湖北') as xlcount,\n" +
"'湖北' as bdzname,\n" +
"(select count(1) from CCB_SUBSTATION where NATIONALASSETS='01' and area='湖北') as bdzcount\n" +
"from dual\n" +
"union all\n" +
"select '湖南' as xlname,\n" +
"(select count(1) from CCB_LINE_INFO where NATIONALASSETS='01' and area='湖南') as xlcount,\n" +
"'湖南' as bdzname,\n" +
"(select count(1) from CCB_SUBSTATION where NATIONALASSETS='01' and area='湖南') as bdzcount\n" +
"from dual\n" +
"union all\n" +
"select '江西' as xlname,\n" +
"(select count(1) from CCB_LINE_INFO where NATIONALASSETS='01' and area='江西') as xlcount,\n" +
"'江西' as bdzname,\n" +
"(select count(1) from CCB_SUBSTATION where NATIONALASSETS='01' and area='江西') as bdzcount\n" +
"from dual";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsDWYXSJTJData(HttpServletRequest request) {
// TODO Auto-generated method stub
JSONObject jsonObject = new JSONObject();
String sql =
"select '山火事件' as sjname,(select count(1) from CCB_MOUNTAIN_FIRE) as sjcount from dual\n" +
"union\n" +
"select '雷击事件' as sjname,(select count(1) from CCB_THUNDER) as sjcount from dual\n" +
"union\n" +
"select '覆冰事件' as sjname,(select count(1) from CCB_ICING) as sjcount from dual\n" +
"union\n" +
"select '舞动事件' as sjname,(select count(1) from CCB_DANCING_EVENT) as sjcount from dual\n" +
"union\n" +
"select '异常事件' as sjname,(select count(1) from CCB_ELECTRIC_EXCEPTION) as sjcount from dual";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsXLCDData(HttpServletRequest request) {
// TODO Auto-generated method stub
JSONObject jsonObject = new JSONObject();
String sql ="select '河南' as xlname,(select nvl(sum(TOTAL_LENGTH),0) from CCB_LINE_INFO where nationalassets='01' and area='河南' ) as xlcd from dual\n" +
"union\n" +
"select '湖北' as xlname,(select nvl(sum(TOTAL_LENGTH),0) from CCB_LINE_INFO where nationalassets='01' and area='湖北') as xlcd from dual\n" +
"union\n" +
"select '湖南' as xlname,(select nvl(sum(TOTAL_LENGTH),0) from CCB_LINE_INFO where nationalassets='01' and area='湖南') as xlcd from dual\n" +
"union\n" +
"select '江西' as xlname,(select nvl(sum(TOTAL_LENGTH),0) from CCB_LINE_INFO where nationalassets='01' and area='江西') as xlcd from dual";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsDWYXSJFBQKData(HttpServletRequest request) {
// TODO Auto-generated method stub
JSONObject jsonObject = new JSONObject();
String sql =
"select * from(select '1月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-01') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-01') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-01') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-01') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-01') as yccount\n" +
" from dual\n" +
" union\n" +
" select '2月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-02') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-02') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-02') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-02') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-02') as yccount\n" +
" from dual\n" +
" union\n" +
" select '3月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-03') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-03') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-03') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-03') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-03') as yccount\n" +
" from dual\n" +
"union\n" +
" select '4月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-04') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-04') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-04') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-04') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-04') as yccount\n" +
" from dual\n" +
" union\n" +
" select '5月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-05') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-05') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-05') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-05') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-05') as yccount\n" +
" from dual\n" +
" union\n" +
" select '6月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-06') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-06') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-06') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-06') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-06') as yccount\n" +
" from dual\n" +
" union\n" +
" select '7月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-07') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-07') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-07') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-07') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-07') as yccount\n" +
" from dual\n" +
" union\n" +
" select '8月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-08') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-08') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-08') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-08') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-08') as yccount\n" +
" from dual\n" +
" union\n" +
" select '9月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-09') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-09') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-09') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-09') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-09') as yccount\n" +
" from dual\n" +
" union\n" +
" select '10月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-10') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-10') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-10') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-10') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-10') as yccount\n" +
" from dual\n" +
" union\n" +
" select '11月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-11') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-11') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-11') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-11') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-11') as yccount\n" +
" from dual\n" +
" union\n" +
" select '12月' as sjmonth,\n" +
" (select count(1)\n" +
" from CCB_MOUNTAIN_FIRE\n" +
" where substr(happen_date, 0, 7) = to_char(sysdate, 'yyyy') || '-12') as hscount,\n" +
" (select count(1)\n" +
" from CCB_THUNDER\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-12') as ljcount,\n" +
" (select count(1)\n" +
" from CCB_ICING\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-12') as fbcount,\n" +
" (select count(1)\n" +
" from CCB_DANCING_EVENT\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-12') as wdcount,\n" +
" (select count(1)\n" +
" from CCB_ELECTRIC_EXCEPTION\n" +
" where substr(HAPPEN_DATE, 0, 7) = to_char(sysdate, 'yyyy') || '-12') as yccount\n" +
" from dual) order by to_number(replace(sjmonth,'月',''))";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsBYQGKGLKGSLData(HttpServletRequest request) {
JSONObject jsonObject = new JSONObject();
String sql =
"select '河南' as xlname,\n" +
" (select nvl(sum(MAINTRANS_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '河南') as byqcount,\n" +
" (select nvl(sum(HIGHRESIST_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '河南') as gkcount,\n" +
" (select nvl(sum(FIVEZZ_SWITCH),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '河南') as glkgcount\n" +
" from dual\n" +
"union\n" +
"select '湖北' as xlname,\n" +
" (select nvl(sum(MAINTRANS_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖北') as byqcount,\n" +
" (select nvl(sum(HIGHRESIST_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖北') as gkcount,\n" +
" (select nvl(sum(FIVEZZ_SWITCH),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖北') as glkgcount\n" +
" from dual\n" +
" union\n" +
"select '湖南' as xlname,\n" +
" (select nvl(sum(MAINTRANS_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖南') as byqcount,\n" +
" (select nvl(sum(HIGHRESIST_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖南') as gkcount,\n" +
" (select nvl(sum(FIVEZZ_SWITCH),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '湖南') as glkgcount\n" +
" from dual\n" +
" union\n" +
"select '江西' as xlname,\n" +
" (select nvl(sum(MAINTRANS_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '江西') as byqcount,\n" +
" (select nvl(sum(HIGHRESIST_NUM),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '江西') as gkcount,\n" +
" (select nvl(sum(FIVEZZ_SWITCH),0)\n" +
" from CCB_SUBSTATION s\n" +
" where s.NATIONALASSETS='01' and s.area = '江西') as glkgcount\n" +
" from dual";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsSBFYNXData(HttpServletRequest request) {
JSONObject jsonObject = new JSONObject();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
Date oneYearOld=null;//1年
Date twoYearOld=null;//2年
Date fiveYearOld=null;//5年
Date sixYearOld=null;//6年
Date tenYearOld=null;//10年
Date elevenYearOld=null;//11年
Date elevenFiveYearOld=null;//15年
Date tenSixYearOld=null;//16年
Date twentyYearOld=null;//20年
Date twentyFiveYearOld=null;//25年
Date thirtyYearOld=null;//30年
Date fortyYearOld=null;//40年
Date date7 = null;
Date date12 = null;
Date date13 = null;
Date date14 = null;
String sql =null;
Date date = new Date();
String year = sdf.format(date);
if(!StringUtil.isNullOrEmpty(year)) {
try {
Calendar b = Calendar.getInstance();
b.setTime(sdf.parse(year));
b.add(Calendar.YEAR, -1);
//c.add(Calendar.DAY_OF_MONTH, 1);
oneYearOld=b.getTime();
Calendar c = Calendar.getInstance();
c.setTime(sdf.parse(year));
c.add(Calendar.YEAR, -2);
//c.add(Calendar.DAY_OF_MONTH, 1);
twoYearOld=c.getTime();
Calendar d = Calendar.getInstance();
d.setTime(sdf.parse(year));
d.add(Calendar.YEAR, -5);
//d.add(Calendar.DAY_OF_MONTH, 1);
fiveYearOld=d.getTime();
Calendar g = Calendar.getInstance();
g.setTime(sdf.parse(year));
g.add(Calendar.YEAR, -6);
//g.add(Calendar.DAY_OF_MONTH, 1);
sixYearOld=g.getTime();
Calendar e = Calendar.getInstance();
e.setTime(sdf.parse(year));
e.add(Calendar.YEAR, -10);
//e.add(Calendar.DAY_OF_MONTH, 1);
tenYearOld=e.getTime();
Calendar f = Calendar.getInstance();
f.setTime(sdf.parse(year));
f.add(Calendar.YEAR, -11);
//e.add(Calendar.DAY_OF_MONTH, 1);
elevenYearOld=f.getTime();
Calendar h = Calendar.getInstance();
h.setTime(sdf.parse(year));
h.add(Calendar.YEAR, -15);
//e.add(Calendar.DAY_OF_MONTH, 1);
elevenFiveYearOld=h.getTime();
Calendar i = Calendar.getInstance();
i.setTime(sdf.parse(year));
i.add(Calendar.YEAR, -16);
//e.add(Calendar.DAY_OF_MONTH, 1);
tenSixYearOld=i.getTime();
Calendar i2 = Calendar.getInstance();
i2.setTime(sdf.parse(year));
i2.add(Calendar.YEAR, -20);
//e.add(Calendar.DAY_OF_MONTH, 1);
twentyYearOld=i2.getTime();
Calendar i3 = Calendar.getInstance();
i3.setTime(sdf.parse(year));
i3.add(Calendar.YEAR, -25);
//e.add(Calendar.DAY_OF_MONTH, 1);
twentyFiveYearOld=i3.getTime();
Calendar i4 = Calendar.getInstance();
i4.setTime(sdf.parse(year));
i4.add(Calendar.YEAR, -30);
//e.add(Calendar.DAY_OF_MONTH, 1);
thirtyYearOld=i4.getTime();
Calendar j = Calendar.getInstance();
j.setTime(sdf.parse(year));
j.add(Calendar.YEAR, -40);
//e.add(Calendar.DAY_OF_MONTH, 1);
fortyYearOld=j.getTime();
Calendar k = Calendar.getInstance();
k.setTime(sdf.parse(year));
k.add(Calendar.YEAR, -7);
//e.add(Calendar.DAY_OF_MONTH, 1);
date7=k.getTime();
Calendar l = Calendar.getInstance();
l.setTime(sdf.parse(year));
l.add(Calendar.YEAR, -12);
//e.add(Calendar.DAY_OF_MONTH, 1);
date12=l.getTime();
Calendar m = Calendar.getInstance();
m.setTime(sdf.parse(year));
m.add(Calendar.YEAR, -13);
date13=m.getTime();
Calendar n = Calendar.getInstance();
n.setTime(sdf.parse(year));
n.add(Calendar.YEAR, -14);
//e.add(Calendar.DAY_OF_MONTH, 1);
date14=n.getTime();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
sql= "SELECT * from ( \n" +
" select 1 xuhao,'主变压器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date12)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'byq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 2 xuhao,'断路器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date14)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 3 xuhao,'电流互感器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date12)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dlhgq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 4 xuhao,'电压互感器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(elevenFiveYearOld)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dyhgq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 5 xuhao,'隔离开关' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date14)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'glkg' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 6 xuhao,'避雷器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(tenSixYearOld)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'blq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 7 xuhao,'组合电器(GIS)' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date13)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'gis' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" union\r\n" +
" select 8 xuhao,'电抗器' stationtype,'500kV' as voltagelevel,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(oneYearOld)+"') year1,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(fiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(oneYearOld)+"') year2,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(tenYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(fiveYearOld)+"') year3,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(elevenFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(tenYearOld)+"') year4,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(elevenFiveYearOld)+"') year5,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(twentyFiveYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyYearOld)+"') year6,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE>='"+sdf.format(thirtyYearOld)+"' and RECENT_RUNDATE<'"+sdf.format(twentyFiveYearOld)+"') year7,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<'"+sdf.format(thirtyYearOld)+"') year8,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" and RECENT_RUNDATE<='"+sdf.format(date13)+"') bfyj,\r\n" +
" (select count(1) from CCB_SUBSTATION_DEVICE\r\n" +
" where device_TYPE = 'dkq' and voltage_level='08'\r\n" +
" ) yearhj\r\n" +
" from dual \n"+
" )a order by a.XUHAO ASC\r\n";
System.out.println(sql);
}
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getEchartsSKYHJXData(HttpServletRequest request) {
JSONObject jsonObject = new JSONObject();
String sql =
"select 1 as xh,'''三跨''改造' as jxname,\n" +
" (select count(1) from ccb_skzl_skzlqktj where skzl_sfywc = '是' and to_char(to_date(skzl_jhzlsj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy') and skzl_wczlsj is not null) as ywccount,\n" +
" (select count(1) from ccb_skzl_skzlqktj where skzl_sfywc = '否' and to_char(to_date(skzl_jhzlsj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy') and skzl_wczlsj is null) as wwccount\n" +
"from dual\n" +
"union\n" +
"select 2 as xh,'缺陷治理' as jxname,\n" +
" (select count(1) from ccb_major_defects where to_char(to_date(zdqx_qxfssj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy') and zdqx_qxxcsj is not null) as ywccount,\n" +
" (select count(1) from ccb_major_defects where to_char(to_date(zdqx_qxfssj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy') and zdqx_qxxcsj is null) as wwccount\n" +
"from dual\n" +
"union\n" +
"select 3 as xh,'检修工作' as jxname,\n" +
" (select count(1) from CCB_ZSZCJX_INFO where ZSZCJX_WGSJ is not null and to_char(to_date(zszcjx_kgsj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy')) as ywccount,\n" +
" (select count(1) from CCB_ZSZCJX_INFO where ZSZCJX_WGSJ is null and to_char(to_date(zszcjx_kgsj,'YYYY-MM-DD'),'yyyy') = to_char(sysdate, 'yyyy')) as wwccount\n" +
"from dual";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
jsonObject.put("data", list);
return jsonObject;
}
public JSONObject getDTData(HttpServletRequest request) {
JSONObject jsonObject = new JSONObject();
String sql =
"select (select count(1) from CCB_SUBSTATION where NATIONALASSETS='01') as bdzsl,\n" +
" (select sum(MAINTRANS_NUM) from CCB_SUBSTATION where NATIONALASSETS='01') as zbyq,\n" +
" (select sum(MAINTRANS_CAPACITY) from CCB_SUBSTATION where NATIONALASSETS='01') as bdzrl,\n" +
" (select count(1) from CCB_LINE_INFO where NATIONALASSETS='01') as sdxlts,\n" +
" (select sum(TOTAL_LENGTH) from CCB_LINE_INFO where NATIONALASSETS='01') as xlzcd,\n" +
" (select sum(TOWER_NUM) from CCB_LINE_INFO where NATIONALASSETS='01') as gtjs\n" +
" from dual";
Map<String, Object> map = this.jdbcTemplate.queryForMap(sql);
jsonObject.put("data", map);
return jsonObject;
}
}
该博客详尽地展示了电力系统的多种统计分析接口，包括直属线路变电数量、电网运行事件统计、线路长度分布、电网事件分布情况及变压器、高抗、隔离开关等关键设备的数量统计，同时涉及设备服役年限分析，为电力运维提供数据支持。
扫一扫
5073
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
