admin管理员组文章数量:1576358
jsp页面
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun/jsp/jstl/functions"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3/TR/html4/loose.dtd">
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<html>
<!--<![endif]-->
<!-- BEGIN HEAD -->
<head>
<meta charset="utf-8" />
<title>超标报警</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta content="width=device-width, initial-scale=1" name="viewport" />
<meta content="" name="description" />
<meta content="" name="author" />
<link rel="stylesheet" type="text/css"
href="<%=basePath%>easyui/themes/icon.css">
<!-- BEGIN GLOBAL MANDATORY STYLES -->
<link rel="stylesheet" type="text/css"
href="<%=basePath%>easyui/themes/default/easyui.css">
<link
href="<%=basePath%>assets/global/plugins/font-awesome/css/font-awesome.min.css"
rel="stylesheet" type="text/css" />
<link
href="<%=basePath%>assets/global/plugins/simple-line-icons/simple-line-icons.min.css"
rel="stylesheet" type="text/css" />
<link
href="<%=basePath%>assets/global/plugins/bootstrap/css/bootstrap.min.css"
rel="stylesheet" type="text/css" />
<link
href="<%=basePath%>assets/global/plugins/uniform/css/uniform.default.css"
rel="stylesheet" type="text/css" />
<link
href="<%=basePath%>assets/global/plugins/bootstrap-switch/css/bootstrap-switch.min.css"
rel="stylesheet" type="text/css" />
<!-- END GLOBAL MANDATORY STYLES -->
<!-- BEGIN THEME GLOBAL STYLES -->
<link href="<%=basePath%>assets/global/css/components.min.css"
rel="stylesheet" id="style_components" type="text/css" />
<link href="<%=basePath%>assets/global/css/plugins.min.css"
rel="stylesheet" type="text/css" />
<!-- END THEME GLOBAL STYLES -->
<!-- BEGIN PAGE LEVEL STYLES -->
<link href="<%=basePath%>assets/apps/css/inbox.min.css" rel="stylesheet"
type="text/css" />
<!-- END PAGE LEVEL STYLES -->
<!-- BEGIN THEME LAYOUT STYLES -->
<link href="<%=basePath%>assets/layouts/layout4/css/layout.min.css"
rel="stylesheet" type="text/css" />
<link
href="<%=basePath%>assets/layouts/layout4/css/themes/light.min.css"
rel="stylesheet" type="text/css" id="style_color" />
<link href="<%=basePath%>assets/layouts/layout4/css/custom.min.css"
rel="stylesheet" type="text/css" />
<!-- END THEME LAYOUT STYLES -->
<link rel="shortcut icon" href="favicon.ico" />
<!-- END HEAD -->
<!-- BEGIN PAGE LEVEL PLUGINS -->
<!-- 表格的样式信息lxy -->
<link rel="stylesheet" href="<%=basePath%>css/bootstrap/bootstrap-table.min.css" />
<!--图标和默认样式-->
<!-- <link rel="stylesheet" type="text/css" href="http://apps.bdimg/libs/bootstrap/3.3.4/css/bootstrap.css"> -->
<!-- <link rel="stylesheet" type="text/css" href="http://cdn.bootcss/font-awesome/4.6.0/css/font-awesome.min.css"> -->
<!--核心样式-->
<link href="<%=basePath%>css/ySelect.css" rel="stylesheet" type="text/css">
<script src="<%=basePath%>js/jquery-1.11.3.min.js"></script>
<script src="<%=basePath%>js/bootstrap.min.js"></script>
<script src="<%=basePath%>js/ySelect.js"></script>
<!-- 导出表格 -->
<script type="text/javascript" src="<%=basePath%>js/FileSaver.js"></script>
<script type="text/javascript" src="<%=basePath%>js/jszip.js"></script>
<script type="text/javascript" src="<%=basePath%>js/excel-gen.js"></script>
<!-- 开始时间和结束时间 -->
<link type="text/css" rel="stylesheet" href="<%=basePath%>jeDate/skin/jedate.css">
<script type="text/javascript" src="<%=basePath%>jeDate/src/jedate.js"></script>
<style>
/* * {
font-family: "微软雅黑";
font-size: 14px;
letter-spacing: 2px;
} */
*{font-family:"微软雅黑";font-size: 14px;letter-spacing:2px;}
.mb{
margin-bottom:20px
}
</style>
</head>
<body
class="page-container-bg-solid page-header-fixed page-sidebar-closed-hide-logo">
<div class="clearfix"></div>
<div class="page-content">
<ul class="page-breadcrumb breadcrumb">
<li class="active"><span
style="color: #5e738b; font-size: 16px;">超标报警</span>
<!-- <i class="fa fa-circle"> -->
</i></li>
</ul>
<!-- END PAGE BREADCRUMB -->
<div class="row">
<div class="col-md-12">
<div class="portlet light bordered" style="height: 866px;">
<div class="portlet-body">
<ul class="nav nav-tabs">
<c:forEach items="${allPollutantDeliveryBtn}"
var="allPollutantDeliveryBtn" varStatus="status_index">
<li>
<li <c:if test="${status_index.index==0}">class="active"</c:if>>
<a href="#${allPollutantDeliveryBtn.FBI_URL}" data-toggle="tab"
style="font-size: 16px;">${allPollutantDeliveryBtn.FBI_NAME}</a>
</li>
</c:forEach>
</ul>
<div class="tab-content">
<div class="tab-pane fade active in" id="tab_1_1">
<div>
<!-- <div class="container" style="margin-top: 25px"> -->
<div class="" style="margin-top: 25px">
<div class="row clearfix" style="margin-bottom:15px">
<div class="col-md-4 col-sm-4 col-lg-4 column mb ">
<div class="form-group">
<label class="col-md-5 col-sm-6 col-lg-4 control-label">开始时间</label>
<div class="col-md-7 col-sm-6 col-lg-6">
<input type="text" id="STARTIME" name="STARTIME" class="form-control" value="${startTime}" style="min-width: 118px"/>
</div>
</div>
</div>
<div class="col-md-4 col-sm-4 col-lg-4 column mb ">
<label class="col-md-5 col-sm-6 col-lg-4 column mb">监测点</label>
<!-- <div class="col-md-7 col-sm-6 col-lg-6">
<input type="text" id="MO_FID" name="MO_FID" class="easyui-combotree form-control input-inline" style="width:100%;height: 34px" />
</div> -->
<div class="col-md-7 col-sm-6 col-lg-6">
<input type="text" id="EA_EFID" name="EA_EFID" class="easyui-combotree form-control input-inline" style="width:100%;height: 34px" >
<%-- <option value="" ></option>
<c:forEach var="monitor" items="${monitor}">
<option value="${monitor.MO_FID}" >${monitor.MO_NAME}</option>
<!-- <option value="MO20190910135454AA4AA293">1号泊位监测点</option> -->
<!-- <option value="MO2019090613055712C690D3">2号泊位监测点</option> -->
</c:forEach> --%>
<!-- </select> -->
</div>
</div>
<div class="col-md-4 col-sm-4 col-lg-4 column mb">
<div class="row clearfix">
<c:forEach var="pdBtnSecond0" items="${pollutantDeliverySecondtab_1_1}">
<c:if test="${pdBtnSecond0.FBI_TYPE==1}">
<a class="btn green" id="searchFunc" style="font-size: 16px; font-family: '微软雅黑';">${pdBtnSecond0.FBI_NAME}
<i class="fa fa-search"></i>
</a>
</c:if>
</c:forEach>
<c:forEach var="pdBtnSecond0" items="${pollutantDeliverySecondtab_1_1}">
<c:if test="${pdBtnSecond0.FBI_TYPE==8}">
<a href="javascript:;" id="exportExcel" class="btn red" style="font-size: 16px; font-family: '微软雅黑';">${pdBtnSecond0.FBI_NAME}
<i class="fa fa-edit"></i>
</a>
</c:if>
</c:forEach>
</div>
</div>
</div>
<div class="row clearfix">
<div class="col-md-4 col-sm-4 col-lg-4 column mb">
<label class="col-md-5 col-sm-6 col-lg-4 control-label">结束时间</label>
<div class="col-md-7 col-sm-6 col-lg-6">
<input type="text" id="ENDTIME" name="ENDTIME" class="form-control" value="${endtime}" style="min-width: 118px"/>
</div>
</div>
<div class="col-md-4 col-sm-4 col-lg-4 column mb">
<label class="col-md-5 col-sm-6 col-lg-4 column mb">监测指标</label>
<div class="col-md-7 col-sm-6 col-lg-6">
<select type="text" id="EA_INDICATOR" name="EA_INDICATOR" class="form-control">
<c:forEach var="sys" items="${sys}">
<option value="${sys.SDD_DICTIONARY_CODE}" >${sys.SDD_DICTIONARY_CNAME}</option>
<!-- <option value="PM10">PM10</option> -->
<!-- <option value="TSP" >TSP</option> -->
<!-- <option value="噪音" >PM2.5</option> -->
</c:forEach>
</select>
</div>
</div>
<div class="col-md-4 col-sm-4 col-lg-4 column mb">
</div>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="portlet-body" style="height: 500px;">
<div class="table-scrollable table-responsive">
<table id="initMonitorTableInfo" class="table table-striped table-bordered table-hover table-checkable order-column table text-nowrap">
</table>
</div>
</div>
<div></div>
</div>
</div>
</div>
</div>
<div class="clearfix margin-bottom-20"></div>
</div>
</div>
</div>
</div>
<!-- BEGIN CONTENT BODY -->
<!-- BEGIN CORE PLUGINS -->
<script src="<%=basePath%>assets/global/plugins/bootstrap/js/bootstrap.min.js" type="text/javascript"></script>
<script src="<%=basePath%>assets/global/plugins/bootstrap-hover-dropdown/bootstrap-hover-dropdown.min.js" type="text/javascript"></script>
<%-- <script src="<%=basePath%>assets/global/plugins/uniform/jquery.uniform.min.js" type="text/javascript"></script> --%>
<script src="<%=basePath%>assets/global/plugins/bootstrap-switch/js/bootstrap-switch.min.js" type="text/javascript"></script>
<!-- BEGIN THEME LAYOUT SCRIPTS -->
<!-- bootstrap表格信息lxy -->
<script src="<%=basePath%>js/bootstrap/bootstrap-table.min.js"></script>
<script src="<%=basePath%>js/bootstrap/bootstrap-table-zh-CN.min.js"></script>
<!-- bootstrap表格信息lxy -->
<script src="<%=basePath%>easyui/jquery.easyui.min.js"></script>
<script src="<%=basePath%>js/bootstrap/center-loader.js"></script>
<script type="text/javascript">
//开始时间和结束时间
function timeFormat(date) {
if (!date || typeof(date) === "string") {
this.error("参数异常,请检查...");
}
var y = date.getFullYear(); //年
var m = date.getMonth() + 1; //月
var d = date.getDate(); //日
return y + "-" + (m<10? ('0'+m) :m) + "-" + (d<10? ('0'+d) :d);
}
// 获取当月第一天
function getFirstDayOfMonth (date) {
date.setDate(1);
return timeFormat(date);
}
$(function(){
//蓝色主题色
jeDate("#STARTIME", {
theme : {
bgcolor : "#00A1CB",
color : "#ffffff",
pnColor : "#00CCFF"
},
format : "YYYY-MM-DD "
});
if($("#STARTIME").val()=="")
$("#STARTIME").val(getFirstDayOfMonth(new Date()))
//蓝色主题色
jeDate("#ENDTIME", {
theme : {
bgcolor : "#00A1CB",
color : "#ffffff",
pnColor : "#00CCFF"
},
format : "YYYY-MM-DD "
});
if($("#ENDTIME").val()=="")
$("#ENDTIME").val(timeFormat(new Date()))
});
var search_type;//查询条件
var search_company;//查询条件
var search_starttime;//开始时间
var search_endtime;//结束时间
//导出条件
$("#exportExcel").click(function(){
<%-- $('body').loader('show','<img src="<%=basePath %>images/loading1.gif">'); //防止表单重复提交 --%>
/* search_company=$("#EA_INDICATOR").val();
search_starttime=$("#STARTIME").val();
search_endtime=$("#ENDTIME").val();
var node=$tree.tree("getSelected");
if(node !=null){
search_type=node.id;//查询条件
var children = $tree.tree('getChildren',node.target);
for(var i=0;i<children.length;i++ ){
var node1=children[i];
search_type+=","+node1.id;
}
} */
location.href='<%=basePath%>ExcessiveAlarm/exportExcel.te?EA_EFID='+search_type+'&EA_INDICATOR='+search_company+'&STARTIME='+search_starttime+'&ENDTIME='+search_endtime;
});
$(function() {
initMonitorTable();
});
$("#searchFunc").on("click",initMonitorTable)
$('#EA_EFID').combotree({
url:'<%=basePath%>userInfo/getCompanyMonitorUse.te',
method:'get',
checkbox:true,
lines:true,
multiple:false, //当为true时,为多选,false为单选
clickNodeForSpan:true,
editable:true,//定义用户是否可以直接往文本域中输入文字。
KeyHandler:{
query:function(data){
var t=$(this).combotree('tree');
var nodes =t.tree('getChildren');
for(var i=0;i<nodes.length;i++ ){
var node=nodes[i];
if(node.text.indexOf(data)>=0){
$(node.target).show();
}else{
$(node.target).hide();
}
}
}
},
onSelect:function(node){
$("#EA_EFID").combotree("setText",node.text);
$("#EA_EFID").combotree("setValue",node.id);
},
onHidePanel: function() {
if($(this).combotree("options")){
var valueField = $(this).combotree("getText");
var val = $(this).combotree("getValue"); //当前combotree的值
var t = $(this).combotree("tree");
var nodes =t.tree('getChildren');
var result = true; //为true说明输入的值在下拉框数据中不存在
for(var i=0;i<nodes.length;i++ ){
var node=nodes[i];
if(node.text==valueField){
$(this).combotree('setValue',node.id);
// $("#temp_"+id).val(node.id)
result=false;
break;
}
}
if (result) {
$(this).combotree("clear");
// $("#temp_"+id).val("");
}
}
}
});
$tree=$('#EA_EFID').combotree("tree");
var node=$tree.tree("getSelected");
if(node !=null){
var EA_EFID=node.id
;
var children = $tree.tree('getChildren',node.target);
for(var i=0;i<children.length;i++ ){
var node1=children[i];
EA_EFID+=","+node1.id ;
}
}
function initMonitorTable(){
//防止表单重复提交
$('body').loader('show','<img src="<%=basePath %>images/loading1.gif">');
$('#initMonitorTableInfo').bootstrapTable('destroy');
$('#initMonitorTableInfo').bootstrapTable({
method: 'post',
contentType: "application/x-www-form-urlencoded",
url : '<%=basePath%>ExcessiveAlarm/initExcessive.te',
cache: false,
pagination: true,
paginationHAlign:'right',
queryParamsType:'', //默认值为 'limit' ,在默认情况下 传给服务端的参数为:offset,limit,sort
// 设置为 '' 在这种情况下传给服务器的参数为:pageSize,pageNumber
queryParams:queryParams,//带参数查询
singleSelect: false,
pageSize: 10,
pageList: [10, 20,30],
search: false, //不显示 搜索框
showColumns: false, //不显示下拉框(选择显示的列)
sidePagination: "server", //服务端请求
striped:true, //隔行变色
clickToSelect:true, //整行点击
columns: [
{
field: 'rownum_',
title: '序号',
valign:'middle',
align: 'center',
// formatter:function(value,row,index){
// return (index+1);
// }
},
{
field: 'ea_HCFID',
title: '所属单位',
valign:'middle',
align: 'center',
/* formatter:function(value,row,index){
var temp=value;
if(value==null || value.length<=0){
value="";
}
if( value != null && value.length>5){
value=value.substring(0,5)+"..";
}
return '<div title="'+temp+'">'+value+'</div>' ;
} */
},
{
field: 'ea_ENAME',
title: '名称',
valign:'middle',
align: 'center',
/* formatter:function(value,row,index){
var temp=value;
if(value==null || value.length<=0){
value="";
}
if( value != null && value.length>8){
value=value.substring(0,8)+"..";
}
return '<div title="'+temp+'">'+value+'</div>' ;
} */
},
{
field: 'ea_INDICATOR',
title: '监测指标',
valign:'middle',
align: 'center',
formatter:function(value,row,index){
var temp=value;
if( value != null && value.length>5){
value=value.substring(0,5)+"..";
}
return '<div title="'+temp+'">'+value+'</div>' ;
}
},
{
field: 'ea_VALUE',
title: '数据采集值',
valign:'middle',
align: 'center',
},
{
field: 'ea_THRESHOLD_LOWER',
title: '阈值下限',
valign:'middle',
align: 'center',
},
{
field: 'ea_THRESHOLD_UP',
title: '阈值上限',
valign:'middle',
align: 'center',
},
{
field: 'ea_THRESHOLD_NAME',
title: '阈值名称',
valign:'middle',
align: 'center',
},
{
field:'ea_COLLECT_TIME',
title:'采集时间',
valign: 'middle',
align: 'center',
}],
onLoadSuccess:function(){
//防止表单重复提交(查询出数据之后把遮罩进行隐藏)
$('body').loader('hide');
search_company=$("#EA_INDICATOR").val();
search_starttime=$("#STARTIME").val();//开始时间
search_endtime=$("#ENDTIME").val();//结束时间
var node=$tree.tree("getSelected");
if(node !=null){
search_type=node.id;//查询条件
var children = $tree.tree('getChildren',node.target);
for(var i=0;i<children.length;i++ ){
var node1=children[i];
search_type+=","+node1.id;
}
}
},
onLoadError: function () {
// bootbox.alert("数据加载失败!");
window.parent.modalOutError("数据加载失败!");
}
});
};
function queryParams(params){
var STARTIME=$("#STARTIME").val();
var ENDTIME=$("#ENDTIME").val();
var EA_INDICATOR=$("#EA_INDICATOR").val();
$tree=$('#EA_EFID').combotree("tree");
var node=$tree.tree("getSelected");
if(node !=null){
var EA_EFID=node.id;
var children = $tree.tree('getChildren',node.target);
for(var i=0;i<children.length;i++ ){
var node1=children[i];
EA_EFID+=","+node1.id;
}
}
var temp = {
pageSize:params.pageSize,
pageNumber:params.pageNumber,
//查询条件
STARTIME:STARTIME,//开始时间
ENDTIME:ENDTIME,//结束时间
EA_INDICATOR:EA_INDICATOR,
//后台的值 :前台的值
EA_EFID:EA_EFID,
};
return temp;
}
</script>
</body>
</html>
contorller层
package enytek.com.cn.controller.ExcessiveAlarm;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import enytek.com.cn.model.excessiveAlarm.ExcessiveAlarm;
import enytek.com.cn.model.functionbuttoninfo.FunctionButtonInfoModel;
import enytek.com.cn.model.holdingcopany.HoldingCompanyModel;
import enytek.com.cn.model.monitoring.Monitoring;
import enytek.com.cn.model.realtimedata.RealTimeData;
import enytek.com.cn.model.sysDataDictionary.SysDataDictionayModel;
import enytek.com.cn.model.userInfo.UserInfoEntity;
import enytek.com.cn.service.Monitoring.MonitoringService;
import enytek.com.cn.service.excessiveAlarm.ExcessiveAlarmService;
import enytek.com.cn.service.holdingcompany.HoldingCompanyService;
import enytek.com.cn.service.sysDataDictionary.SysDataDictionaryService;
import enytek.com.cn.util.CommonUtil;
import enytek.com.cn.util.echecomap.EhcacheMapUtil;
import enytek.com.cn.util.userlimit.AllLimitUtil;
@Controller
@RequestMapping("ExcessiveAlarm")
public class ExcessiveAlarmController {
@Resource(name="excessiveAlarmService")
private ExcessiveAlarmService excessiveAlarmService;
@Resource(name="sysDataDictionaryService")
private SysDataDictionaryService sysDataDictionaryService;
@Resource(name="holdingCompanyService")
private HoldingCompanyService holdingCompanyService;
@Resource(name="monitoringService")
private MonitoringService monitoringService;
/**
// * 字典表内容 research_report
* @param dic
* @return
*/
public List<SysDataDictionayModel> getDataDic(String dic){
SysDataDictionayModel sysData = new SysDataDictionayModel();
sysData.setSDD_DICTIONARY(dic);
List<SysDataDictionayModel> sysDataList = sysDataDictionaryService.findDicInfo(sysData);
return sysDataList;
}
//初始化监测点
@RequestMapping("initSiteManger")
public String showShippingInfo(HttpSession session,String noChangeFid,Model model) throws Exception{
UserInfoEntity userInfo = (UserInfoEntity)session.getAttribute("LoginUserInfo");
Object obj = EhcacheMapUtil.getObject("btn"+userInfo.getUI_FID());
if(obj!=null){
List<FunctionButtonInfoModel> tempList = (List<FunctionButtonInfoModel>)obj;
//获取一级按钮的信息start
List<FunctionButtonInfoModel> resultList = new ArrayList<FunctionButtonInfoModel>();
AllLimitUtil.getButtonListInfoFromFid(noChangeFid, tempList, resultList);
model.addAttribute("allPollutantDeliveryBtn",resultList);
//获取一级按钮的信息end
if(resultList!=null && resultList.size()>0){
for (int j = 0; j < resultList.size(); j++) {
List<FunctionButtonInfoModel> resultListErJi = new ArrayList<FunctionButtonInfoModel>();
FunctionButtonInfoModel tempFunctionButtonInfoModel=resultList.get(j);
String erJiFId = tempFunctionButtonInfoModel.getFBI_FID();
AllLimitUtil.getButtonListInfoFromFid(erJiFId, tempList, resultListErJi);
model.addAttribute("pollutantDeliverySecond"+resultList.get(j).getFBI_URL(),resultListErJi);
}
}
}else{
return "permissionDenied";
}
/**
* C标签表达式 要在初始中
*/
SysDataDictionayModel sys=new SysDataDictionayModel();
sys.setSDD_DICTIONARY("0060");
sys.setSDD_DELFALG("0");
List<SysDataDictionayModel> findDicInfo = sysDataDictionaryService.findDicInfo(sys);
model.addAttribute("sys",findDicInfo);
return "ExcessiveAlarm/showExcessiveAlarm";
}
// public List<ExcessiveAlarm> findHcmCityInfo(HttpSession session) throws Exception{
// UserInfoEntity userInfo = (UserInfoEntity)session.getAttribute("LoginUserInfo");
// Object obj = EhcacheMapUtil.getObject("btn"+userInfo.getUI_FID());
// List<ExcessiveAlarm> ExcessiveAlarmlist = new ArrayList<ExcessiveAlarm>();
// return ExcessiveAlarmlist;
// }
/**
* 查询及分页
* @param yh
* @param pageSize
* @param pageNumber
* @return
* @throws Exception
*/
@RequestMapping("/initExcessive")
@ResponseBody
public Map<String, Object> initExcessive(ExcessiveAlarm excessive,HttpSession session,@RequestParam int pageSize,@RequestParam int pageNumber,Model model) throws Exception {
UserInfoEntity userInfo = (UserInfoEntity)session.getAttribute("LoginUserInfo");
Object obj = EhcacheMapUtil.getObject("btn"+userInfo.getUI_FID());
if(excessive!=null){//获取值不能为空
if(excessive.getEA_INDICATOR().equals("0")){//如果获取数据中EA_INDICATOR的内容为0时
excessive.setEA_INDICATOR(""); //那么输出的数据中EA_INDICATOR的内容就为字符为空
}
}
//时间查询
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String systim= sdf.format(new Date());
if(excessive.getEA_EFID()!=null && excessive.getEA_EFID()!=""){//如果获取数据中EA_EFID值不为空 且获取数据中EA_EFID内容字符不为空
String ea_efid = excessive.getEA_EFID();//获取EA_EFID的值
excessive.setEA_EFID(null);//防止重值 输出值为null
String[] efid=ea_efid.split(",");//split为分隔符 定义String类型的数值
// String mo="";
// String hc="";
List<String> company=new ArrayList<String>();//创建ArrayList字符串的list集合
List<String> molist=new ArrayList<String>();
for(int i=0;i<efid.length;i++){//startsWith字符串前缀
if(efid[i].startsWith("MO")){
// mo+="'"+efid[i]+"'"+",";
molist.add(efid[i]);
}
if(efid[i].startsWith("HC")){
// hc+="'"+efid[i]+"'";
company.add(efid[i]);
}
}
if(molist.size()>0){//如果molist长度大于0
excessive.setEA_EFID_IN(CommonUtil.getString("EA_EFID",molist));//EA_EFID_IN用 CommonUtil获取返回一个随机的32位长的字符串EA_EFID的值
}
if(company.size()>0){
excessive.setEA_HCFID_IN(CommonUtil.getString("EA_HCFID",company));//EA_HCFID_IN用 CommonUtil获取返回一个随机的32位长的字符串EA_HCFID的值
}
}
/**
* 设置权限用
*/
if(excessive.getEA_EFID()=="" || excessive.getEA_EFID()==null){ //如果数据EA_EFID的值等于字符0 或数据EA_EFID值为null时
excessive.setEA_HCFID_IN(CommonUtil.getString("EA_HCFID", userInfo.getCompanyList()));//输出EA_HCFID_IN
}
// excessive.setEA_EFID(userInfo.getLoginHoldIngCompany());
excessive.setENDTIME(excessive.getENDTIME()+" 23:59:59");
List<ExcessiveAlarm> selectPaging3 = excessiveAlarmService.selectExcessiveAlarmPaging(excessive, pageNumber, pageSize); //获取数据
Integer selectexcessiveAlarmCount = excessiveAlarmService.selectExcessiveAlarmCount(excessive);
Map<String, Object> result = new HashMap<String, Object>();
result.put("total", selectexcessiveAlarmCount);
result.put("rows", selectPaging3);
return result;
}
/**
* 导出EXCEL
* @param response
* @return
* @return
* @throws Exception
*/
/*
/**
* 使用HSSFWorkbook导出
* cy 2019年9月6日17:36:46
* @param response
* @param entity
* @param session
* @return
* @throws Exception
*/
@SuppressWarnings("deprecation")//主要用在取消一些编译器产生的警告对代码左侧行列的遮挡
@RequestMapping(value="exportExcel")
@ResponseBody
public void exportExcelFile(HttpServletResponse response,ExcessiveAlarm excessive,HttpSession session) throws Exception{
UserInfoEntity userInfo = (UserInfoEntity)session.getAttribute("LoginUserInfo");
Object obj = EhcacheMapUtil.getObject("btn"+userInfo.getUI_FID());
if(excessive!=null){
if(excessive.getEA_INDICATOR().equals("0")){
excessive.setEA_INDICATOR("");
}
}
//当设备id不等于空且不等于字符串空是
if(excessive.getEA_EFID()!=null && excessive.getEA_EFID()!=""){
String ea_efid = excessive.getEA_EFID();//获取EA_EFID的值
excessive.setEA_EFID(null);//防止重值 输出值为null
String[] efid=ea_efid.split(",");//split为分隔符 定义String类型的数值
List<String> company=new ArrayList<String>();//创建ArrayList字符串的list集合
List<String> molist=new ArrayList<String>();
for(int i=0;i<efid.length;i++){
if(efid[i].startsWith("MO")){ //startsWith字符串前缀
molist.add(efid[i]);
}
if(efid[i].startsWith("HC")){
company.add(efid[i]);
}
}
if(molist.size()>0){
excessive.setEA_EFID_IN(CommonUtil.getString("EA_EFID",molist));//EA_EFID的值等于EA_EFID_IN
}
if(company.size()>0){
excessive.setEA_HCFID_IN(CommonUtil.getString("EA_HCFID",company));//EA_HCFID的值等于EA_HCFID_IN
}
}
if(excessive.getEA_EFID()=="" || excessive.getEA_EFID()==null){
excessive.setEA_HCFID_IN(CommonUtil.getString("EA_HCFID", userInfo.getCompanyList()));//设置权限
}
//创建一个Excel文件
HSSFWorkbook wb=new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("超标预警");
sheet.setColumnWidth(0, 20 * 256);
sheet.setColumnWidth(1, 20 * 256);
sheet.setColumnWidth(2, 20 * 256);
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(4, 20 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 20 * 256);
sheet.setColumnWidth(7, 20 * 256);
sheet.setColumnWidth(8, 20 * 256);
sheet.setColumnWidth(9, 20 * 256);
sheet.setColumnWidth(10, 20 * 256);
sheet.setColumnWidth(11, 20 * 256);
sheet.setColumnWidth(12, 20 * 256);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("所属单位");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("名称");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("监测指标");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("数据采集值");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("阈值下限");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("阈值上限");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("阈值名称");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue("采集时间");
cell.setCellStyle(style);
excessive.setENDTIME(excessive.getENDTIME()+" 23:59:59");
List<ExcessiveAlarm> excessiveAlarm =excessiveAlarmService.selectExcessiveAlarmExcel(excessive);
for(int i=0;i<excessiveAlarm.size();i++){
row = sheet.createRow((int) i + 1);
ExcessiveAlarm tcharge = (ExcessiveAlarm)excessiveAlarm.get(i);
//放入相应的数据
row.createCell((short) 0).setCellValue(i+1);
row.createCell((short) 1).setCellValue(tcharge.getEA_HCFID());
row.createCell((short) 2).setCellValue(tcharge.getEA_ENAME());
row.createCell((short) 3).setCellValue(tcharge.getEA_INDICATOR());
row.createCell((short) 4).setCellValue(tcharge.getEA_VALUE());
row.createCell((short) 5).setCellValue(tcharge.getEA_THRESHOLD_LOWER());
row.createCell((short) 6).setCellValue(tcharge.getEA_THRESHOLD_UP());
row.createCell((short) 7).setCellValue(tcharge.getEA_THRESHOLD_NAME());
row.createCell((short) 8).setCellValue(tcharge.getEA_COLLECT_TIME());
}
//存放位置
try
{
response.setContentType("application/x-download");//客户端浏览器
String fileName = "超标预警.xlsx";
fileName = URLEncoder.encode(fileName, "UTF-8");//客户端在进行网页请求的时
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);//下载后的文件名
OutputStream out = response.getOutputStream();//输出的对象
wb.write(out);
out.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
service层
package enytek.com.cn.service.excessiveAlarm;
import enytek.com.cn.model.excessiveAlarm.ExcessiveAlarm;
import enytek.com.cn.model.realtimedata.RealTimeData;
import java.util.List;
/**
* Dao interface
*/
public interface ExcessiveAlarmService {
/**
*新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public int insertExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*修改方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public int updateExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public Integer deleteExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*批量删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public Integer deleteExcessiveAlarmList(ExcessiveAlarm entity) throws Exception;
/**
* 查询方法
* 2019-09-04 15:04:36
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public List<ExcessiveAlarm> selectExcessiveAlarmList(ExcessiveAlarm entity) throws Exception;
/**
* 分页查询
* 2019-09-04 15:04:36
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public List<ExcessiveAlarm> selectExcessiveAlarmPaging(ExcessiveAlarm entity,int pager_offset,int pageSize) throws Exception;
/**
* 总数量
*2019-09-04 15:04:36
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public Integer selectExcessiveAlarmCount(ExcessiveAlarm entity) throws Exception;
/**
*批量新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:36
*/
public int batchInsertExcessiveAlarm(List<ExcessiveAlarm> entity) throws Exception;
/**
* 导出EXCEL
* @param entity
* @return
* @throws Exception
*/
public List<ExcessiveAlarm> selectExcessiveAlarmExcel(ExcessiveAlarm entity) throws Exception;
/**
* 总揽 超标天数排名
* @param entity
* @return
* @throws Exception
*/
public List<ExcessiveAlarm> findExInfo(ExcessiveAlarm entity,int pager_offset,int pageSize) throws Exception;
/**
* 总揽 超标天数排名 总记录数
* @param entity
* @return
* @throws Exception
*/
public Integer findExInfoCount(ExcessiveAlarm entity) throws Exception;
}
## serviceImpl
```java
package enytek.service.Impl.excessiveAlarm;
import java.util.List;
import enytek.model.excessiveAlarm.ExcessiveAlarm;
import enytek.service.excessiveAlarm.ExcessiveAlarmService;
import enytek.dao.excessiveAlarm.ExcessiveAlarmDao;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import enytek.util.PageTools;
/**
* Service implementation class
*
*/
@Service("excessiveAlarmService")
public class ExcessiveAlarmServiceImpl implements ExcessiveAlarmService{
@Resource(name="excessiveAlarmDao")
private ExcessiveAlarmDao excessiveAlarmDao;
/**
*新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public int insertExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.insertExcessiveAlarm(entity);
}
/**
*修改方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public int updateExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.updateExcessiveAlarm(entity);
}
/**
*删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public Integer deleteExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.deleteExcessiveAlarm(entity);
}
/**
*批量删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public Integer deleteExcessiveAlarmList(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.deleteExcessiveAlarmList(entity);
}
/**
* 查询方法
* 2019-09-04 15:04:37
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmList(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.selectExcessiveAlarmList(entity);
}
/**
* 分页查询
* 2019-09-04 15:04:37
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmPaging(ExcessiveAlarm entity,int pager_offset,int pageSize) throws Exception {
PageTools pt = new PageTools();
int startPage = pt.getBeginNumbers(pager_offset, pageSize);
int endPage = pt.getEndNumbers(pager_offset, pageSize);
entity.setStartPage(startPage);
entity.setEndPage(endPage);
return excessiveAlarmDao.selectExcessiveAlarmPaging(entity);
}
/**
* 总数量
*2019-09-04 15:04:37
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public Integer selectExcessiveAlarmCount(ExcessiveAlarm entity) throws Exception {
return excessiveAlarmDao.selectExcessiveAlarmCount(entity);
}
/**
*批量新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
public int batchInsertExcessiveAlarm(List<ExcessiveAlarm> entity) throws Exception{
return excessiveAlarmDao.batchInsertExcessiveAlarm(entity);
}
/**
*导出方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:37
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmExcel(
ExcessiveAlarm entity) throws Exception {
// TODO Auto-generated method stub
return excessiveAlarmDao.selectExcessiveAlarmExcel(entity);
}
@Override
public List<ExcessiveAlarm> findExInfo(ExcessiveAlarm entity,
int pager_offset, int pageSize) throws Exception {
// TODO Auto-generated method stub
PageTools pt = new PageTools();
int startPage = pt.getBeginNumbers(pager_offset, pageSize);
int endPage = pt.getEndNumbers(pager_offset, pageSize);
entity.setStartPage(startPage);
entity.setEndPage(endPage);
return excessiveAlarmDao.findExInfo(entity);
}
@Override
public Integer findExInfoCount(ExcessiveAlarm entity) throws Exception {
// TODO Auto-generated method stub
return excessiveAlarmDao.findExInfoCount(entity);
}
}
mybatis-config.xml
<!-- 超标报警 -->
<typeAlias alias="excessiveAlarm" type="enytek.model.excessiveAlarm.ExcessiveAlarm" />
<!-- 超标报警 -->
<mapper resource="enytek/com/cn/mappers/excessiveAlarm/excessiveAlarmMapper.xml"/>
dao层
package enytek.com.cn.dao.excessiveAlarm;
import enytek.com.cn.model.excessiveAlarm.ExcessiveAlarm;
import enytek.com.cn.model.realtimedata.RealTimeData;
import java.util.List;
/**
* Dao interface
*/
public interface ExcessiveAlarmDao {
/**
*新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public int insertExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*修改方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public int updateExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public Integer deleteExcessiveAlarm(ExcessiveAlarm entity) throws Exception;
/**
*批量删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public Integer deleteExcessiveAlarmList(ExcessiveAlarm entity) throws Exception;
/**
* 查询方法
* 2019-09-04 15:04:34
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public List<ExcessiveAlarm> selectExcessiveAlarmList(ExcessiveAlarm entity) throws Exception;
/**
* 分页查询
* 2019-09-04 15:04:34
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public List<ExcessiveAlarm> selectExcessiveAlarmPaging(ExcessiveAlarm entity) throws Exception;
/**
* 总数量
*2019-09-04 15:04:34
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public Integer selectExcessiveAlarmCount(ExcessiveAlarm entity) throws Exception;
/**
*批量新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:34
*/
public int batchInsertExcessiveAlarm(List<ExcessiveAlarm> entity) throws Exception;
/**
* 导出EXCEL
* @param entity
* @return
* @throws Exception
*/
public List<ExcessiveAlarm> selectExcessiveAlarmExcel(ExcessiveAlarm entity) throws Exception;
/**
* 总揽 超标天数排名
* @param entity
* @return
* @throws Exception
*/
public List<ExcessiveAlarm> findExInfo(ExcessiveAlarm entity) throws Exception;
/**
* 总揽 超标天数排名 总记录数
* @param entity
* @return
* @throws Exception
*/
public Integer findExInfoCount(ExcessiveAlarm entity) throws Exception;
}
daoImpl
package enytek.com.cn.dao.Impl.excessiveAlarm;
import java.util.List;
import org.springframework.stereotype.Repository;
import enytek.com.cn.base.PublicSqlSessionDao;
import enytek.com.cn.model.excessiveAlarm.ExcessiveAlarm;
import enytek.com.cn.dao.excessiveAlarm.ExcessiveAlarmDao;
/**
* Dao implementation
*
*/
@Repository("excessiveAlarmDao")
public class ExcessiveAlarmDaoImpl extends PublicSqlSessionDao implements ExcessiveAlarmDao {
/**
*新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public int insertExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().insert("insertExcessiveAlarm",entity );
}
/**
*修改方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public int updateExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().update("updateExcessiveAlarm",entity );
}
/**
*删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public Integer deleteExcessiveAlarm(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().update("deleteExcessiveAlarm",entity );
}
/**
*删除方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public Integer deleteExcessiveAlarmList(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().update("deleteExcessiveAlarmList",entity );
}
/**
* 查询方法
* 2019-09-04 15:04:35
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmList(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().selectList("selectExcessiveAlarmList",entity);
}
/**
* 分页查询
* 2019-09-04 15:04:35
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmPaging(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().selectList("selectExcessiveAlarmPaging",entity);
}
/**
* 总数量
*2019-09-04 15:04:35
* @param proent
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public Integer selectExcessiveAlarmCount(ExcessiveAlarm entity) throws Exception {
return this.getSqlSession().selectOne("selectExcessiveAlarmCount",entity);
}
/**
*批量新增方法
* @param entity
* @return
* @throws Exception
* @author YH
* @date 2019-09-04 15:04:35
*/
@Override
public int batchInsertExcessiveAlarm(List<ExcessiveAlarm> entity) throws Exception{
return this.getSqlSession().insert("batchInsertExcessiveAlarm",entity );
}
/**
* 导出EXCEL
* @param entity
* @return
* @throws Exception
*/
@Override
public List<ExcessiveAlarm> selectExcessiveAlarmExcel(ExcessiveAlarm entity)
throws Exception {
// TODO Auto-generated method stub
return this.getSqlSession().selectList("selectExcessiveAlarmExcel",entity);
}
/**
* 总揽 超标天数排名
* @param entity
* @return
* @throws Exception
*/
@Override
public List<ExcessiveAlarm> findExInfo(ExcessiveAlarm entity)
throws Exception {
// TODO Auto-generated method stub
return this.getSqlSession().selectList("findExInfo",entity);
}
@Override
public Integer findExInfoCount(ExcessiveAlarm entity) throws Exception {
// TODO Auto-generated method stub
return this.getSqlSession().selectOne("findExInfoCount",entity);
}
}
mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" "http://mybatis/dtd/mybatis-3-mapper.dtd">
<mapper namespace="enytek.dao.excessiveAlarm.ExcessiveAlarmDao">
<!-- 返回列 2019-09-04 15:04:38 YH-->
<sql id="Base_Column_List">
EA_ID
,EA_FID
,EA_HCFID
,EA_EFID
,EA_ENAME
,EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,EA_THRESHOLD_NAME
,EA_COLLECT_TIME
,EA_CREATE_TIME
,EA_DELFLAG
</sql>
<!-- 查询条件 2019-09-04 15:04:38 YH-->
<sql id="where_conditions">
<if test="EA_ID != null and EA_ID != ''">
and EA_ID = #{EA_ID,jdbcType=DECIMAL}
</if>
<if test="EA_FID != null and EA_FID != ''">
and EA_FID = #{EA_FID,jdbcType=VARCHAR}
</if>
<if test="EA_HCFID != null and EA_HCFID != ''">
and EA_HCFID = #{EA_HCFID,jdbcType=VARCHAR}
</if>
<if test="EA_HCFID_IN != null and EA_HCFID_IN != ''">
and (${EA_HCFID_IN})
</if>
<if test="EA_EFID_IN != null and EA_EFID_IN != ''">
and (${EA_EFID_IN})
</if>
<if test="EA_EFID != null and EA_EFID != ''">
and EA_EFID = #{EA_EFID,jdbcType=VARCHAR}
</if>
<if test="EA_ENAME != null and EA_ENAME != ''">
and EA_ENAME = #{EA_ENAME,jdbcType=VARCHAR}
</if>
<if test="EA_INDICATOR != null and EA_INDICATOR != ''">
and EA_INDICATOR = #{EA_INDICATOR,jdbcType=VARCHAR}
</if>
<if test="EA_VALUE != null and EA_VALUE != ''">
and EA_VALUE = #{EA_VALUE,jdbcType=VARCHAR}
</if>
<if test="EA_THRESHOLD_UP != null and EA_THRESHOLD_UP != ''">
and EA_THRESHOLD_UP = #{EA_THRESHOLD_UP,jdbcType=VARCHAR}
</if>
<if test="EA_THRESHOLD_LOWER != null and EA_THRESHOLD_LOWER != ''">
and EA_THRESHOLD_LOWER = #{EA_THRESHOLD_LOWER,jdbcType=VARCHAR}
</if>
<if test="EA_THRESHOLD_NAME != null and EA_THRESHOLD_NAME != ''">
and EA_THRESHOLD_NAME = #{EA_THRESHOLD_NAME,jdbcType=VARCHAR}
</if>
<if test="EA_COLLECT_TIME != null and EA_COLLECT_TIME != ''">
and EA_COLLECT_TIME = #{EA_COLLECT_TIME,jdbcType=DATE}
</if>
<if test="EA_CREATE_TIME != null and EA_CREATE_TIME != ''">
and EA_CREATE_TIME = #{EA_CREATE_TIME,jdbcType=DATE}
</if>
<if test="EA_DELFLAG != null and EA_DELFLAG != ''">
and EA_DELFLAG = #{EA_DELFLAG,jdbcType=VARCHAR}
</if>
<if test="STARTIME != null and STARTIME !=''">
and to_date(to_char(EA_COLLECT_TIME, 'yyyy-MM-dd HH24:MI'),'yyyy-MM-dd HH24:MI') >= to_date(#{STARTIME},'yyyy-MM-dd HH24:MI')
</if>
<if test="ENDTIME != null and ENDTIME !=''">
and to_date(to_char(EA_COLLECT_TIME, 'yyyy-MM-dd HH24:MI'),'yyyy-MM-dd HH24:MI') <= to_date(#{ENDTIME},'yyyy-mm-dd hh24:mi:ss')
</if>
</sql>
<!-- 新增方法 2019-09-04 15:04:38 YH-->
<insert id="insertExcessiveAlarm" parameterType="excessiveAlarm">
<selectKey keyProperty="EA_ID" order="BEFORE" resultType="Integer">
select SEQ_EQ_ID.nextval from dual
</selectKey>
insert into EXCESSIVE_ALARM (
EA_ID
,EA_FID
,EA_HCFID
,EA_EFID
,EA_ENAME
,EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,EA_THRESHOLD_NAME
,EA_COLLECT_TIME
,EA_CREATE_TIME
,EA_DELFLAG
)
values(
#{EA_ID,jdbcType=DECIMAL}
,#{EA_FID,jdbcType=VARCHAR}
,#{EA_HCFID,jdbcType=VARCHAR}
,#{EA_EFID,jdbcType=VARCHAR}
,#{EA_ENAME,jdbcType=VARCHAR}
,#{EA_INDICATOR,jdbcType=VARCHAR}
,#{EA_VALUE,jdbcType=VARCHAR}
,#{EA_THRESHOLD_UP,jdbcType=VARCHAR}
,#{EA_THRESHOLD_LOWER,jdbcType=VARCHAR}
,#{EA_THRESHOLD_NAME,jdbcType=VARCHAR}
,#{EA_COLLECT_TIME,jdbcType=VARCHAR}
,#{EA_CREATE_TIME,jdbcType=VARCHAR}
,#{EA_DELFLAG,jdbcType=VARCHAR}
)
</insert>
<!-- 批量新增方法 2019-09-04 15:04:38 YH-->
<insert id="batchInsertExcessiveAlarm" parameterType="excessiveAlarm">
insert into EXCESSIVE_ALARM (
EA_ID
,EA_FID
,EA_HCFID
,EA_EFID
,EA_ENAME
,EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,EA_THRESHOLD_NAME
,EA_COLLECT_TIME
,EA_CREATE_TIME
,EA_DELFLAG
)
select SEQ_EQ_ID.nextval, cd.* from (
<foreach collection="list" item="item" index="index" separator="union all">
select
EA_FID,#{EA_FID,jdbcType=VARCHAR}
,EA_HCFID,#{EA_HCFID,jdbcType=VARCHAR}
,EA_EFID,#{EA_EFID,jdbcType=VARCHAR}
,EA_ENAME,#{EA_ENAME,jdbcType=VARCHAR}
,EA_INDICATOR,#{EA_INDICATOR,jdbcType=VARCHAR}
,EA_VALUE,#{EA_VALUE,jdbcType=VARCHAR}
,EA_THRESHOLD_UP,#{EA_THRESHOLD_UP,jdbcType=VARCHAR}
,EA_THRESHOLD_LOWER,#{EA_THRESHOLD_LOWER,jdbcType=VARCHAR}
,EA_THRESHOLD_NAME,#{EA_THRESHOLD_NAME,jdbcType=VARCHAR}
,EA_COLLECT_TIME,#{EA_COLLECT_TIME,jdbcType=DATE}
,EA_CREATE_TIME,#{EA_CREATE_TIME,jdbcType=DATE}
,EA_DELFLAG,#{EA_DELFLAG,jdbcType=VARCHAR}
from dual
</foreach>
) cd
</insert>
<!-- 查询方法 2019-09-04 15:04:38 YH-->
<select id="selectExcessiveAlarmList" parameterType="excessiveAlarm" resultType="excessiveAlarm">
select
EA_ID
,EA_FID
,EA_HCFID
,EA_EFID
,EA_ENAME
,EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,EA_THRESHOLD_NAME
,to_char(EA_COLLECT_TIME,'yyyy-MM-dd hh24:mi:ss') EA_COLLECT_TIME
,to_char(EA_CREATE_TIME,'yyyy-MM-dd hh24:mi:ss') EA_CREATE_TIME
,EA_DELFLAG
<include refid="Base_Column_List" />
from EXCESSIVE_ALARM
<where>
1=1
<include refid="where_conditions" />
</where>
</select>
<!-- 删除方法 2019-09-04 15:04:38 YH-->
<update id="deleteExcessiveAlarm" parameterType="excessiveAlarm">
update EXCESSIVE_ALARM set EA_DELFLAG='1'
where EA_FID=#{EA_FID,jdbcType=VARCHAR}
</update>
<!-- 批量删除方法 2019-09-04 15:04:38 YH-->
<update id="deleteExcessiveAlarmList" parameterType="excessiveAlarm">
update EXCESSIVE_ALARM set EA_DELFLAG='1'
where EA_FID in (${EA_FID})
</update>
<!-- 修改方法 2019-09-04 15:04:38 YH-->
<update id="updateExcessiveAlarm" parameterType="excessiveAlarm">
update EXCESSIVE_ALARM
<set>
<if test="EA_ID != null and EA_ID!=''">
EA_ID=#{EA_ID,jdbcType=DECIMAL},
</if>
<if test="EA_FID != null and EA_FID!=''">
EA_FID=#{EA_FID,jdbcType=VARCHAR},
</if>
<if test="EA_HCFID != null and EA_HCFID!=''">
EA_HCFID=#{EA_HCFID,jdbcType=VARCHAR},
</if>
<if test="EA_EFID != null and EA_EFID!=''">
EA_EFID=#{EA_EFID,jdbcType=VARCHAR},
</if>
<if test="EA_ENAME != null and EA_ENAME!=''">
EA_ENAME=#{EA_ENAME,jdbcType=VARCHAR},
</if>
<if test="EA_INDICATOR != null and EA_INDICATOR!=''">
EA_INDICATOR=#{EA_INDICATOR,jdbcType=VARCHAR},
</if>
<if test="EA_VALUE != null and EA_VALUE!=''">
EA_VALUE=#{EA_VALUE,jdbcType=VARCHAR},
</if>
<if test="EA_THRESHOLD_UP != null and EA_THRESHOLD_UP!=''">
EA_THRESHOLD_UP=#{EA_THRESHOLD_UP,jdbcType=VARCHAR},
</if>
<if test="EA_THRESHOLD_LOWER != null and EA_THRESHOLD_LOWER!=''">
EA_THRESHOLD_LOWER=#{EA_THRESHOLD_LOWER,jdbcType=VARCHAR},
</if>
<if test="EA_THRESHOLD_NAME != null and EA_THRESHOLD_NAME!=''">
EA_THRESHOLD_NAME=#{EA_THRESHOLD_NAME,jdbcType=VARCHAR},
</if>
<if test="EA_COLLECT_TIME != null and EA_COLLECT_TIME!=''">
EA_COLLECT_TIME=#{EA_COLLECT_TIME,jdbcType=DATE},
</if>
<if test="EA_CREATE_TIME != null and EA_CREATE_TIME!=''">
EA_CREATE_TIME=#{EA_CREATE_TIME,jdbcType=DATE},
</if>
<if test="EA_DELFLAG != null and EA_DELFLAG!=''">
EA_DELFLAG=#{EA_DELFLAG,jdbcType=VARCHAR},
</if>
</set>
where EA_FID=#{EA_FID,jdbcType=VARCHAR}
</update>
<!-- 查询总条数方法2019-09-04 15:04:38 YH-->
<select id="selectExcessiveAlarmCount" parameterType="excessiveAlarm" resultType="Integer">
select count(1) from Excessive_Alarm,MONITORING
<where>
1=1
AND EXCESSIVE_ALARM.EA_EFID=MONITORING.MO_FID and MONITORING.MO_STAUTS='0'
<include refid="where_conditions" />
</where>
</select>
<!-- 分页查询方法2019-09-04 15:04:38 YH-->
<select id="selectExcessiveAlarmPaging" parameterType="excessiveAlarm" resultType="excessiveAlarm">
select
<include refid="Base_Column_List" />,
rownum_ from(select
EA_ID
,EA_FID
,(select HC_NAME from HOLDINGCOMPANY where HC_DELFLAG='0' and HC_FID=EA_HCFID) as EA_HCFID
,(select MO_NAME from MONITORING where MO_DELFLAG='0' and MO_FID=EA_EFID) as EA_EFID
,EA_ENAME
,(select SDD_DICTIONARY_CNAME from SYS_DATA_DICTIONARY where SDD_DICTIONARY='0060' and SDD_DELFALG='0' and SDD_DICTIONARY_CODE=EA_INDICATOR) as EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,(select SDD_DICTIONARY_CNAME from SYS_DATA_DICTIONARY where SDD_DICTIONARY='0051' and SDD_DELFALG='0' and SDD_DICTIONARY_CODE=EA_THRESHOLD_NAME) as EA_THRESHOLD_NAME
,to_char(EA_COLLECT_TIME,'yyyy-MM-dd hh24:mi:ss') EA_COLLECT_TIME
,EA_CREATE_TIME
,EA_DELFLAG,row_number()
over(order by EA_ID desc) as rownum_ from EXCESSIVE_ALARM,MONITORING
where
1=1
AND EXCESSIVE_ALARM.EA_EFID=MONITORING.MO_FID and MONITORING.MO_STAUTS='0'
<include refid="where_conditions" />
) row_ WHERE row_.rownum_ between #{startPage} and #{endPage}
</select>
<!--excel导出-->
<select id="selectExcessiveAlarmExcel" parameterType="excessiveAlarm" resultType="excessiveAlarm">
select
EA_ID
,EA_FID
,(SELECT hc_name from HOLDINGCOMPANY where hc_fid = EA_HCFID) as EA_HCFID
,EA_EFID
,EA_ENAME
,(select SDD_DICTIONARY_CNAME from SYS_DATA_DICTIONARY where SDD_DICTIONARY='0060' and SDD_DELFALG='0' and SDD_DICTIONARY_CODE=EA_INDICATOR) as EA_INDICATOR
,EA_VALUE
,EA_THRESHOLD_UP
,EA_THRESHOLD_LOWER
,(select SDD_DICTIONARY_CNAME from SYS_DATA_DICTIONARY where SDD_DICTIONARY='0051' and SDD_DELFALG='0' and SDD_DICTIONARY_CODE=EA_THRESHOLD_NAME) as EA_THRESHOLD_NAME
,to_char(EA_COLLECT_TIME,'yyyy-MM-dd hh24:mi:ss') EA_COLLECT_TIME
,EA_CREATE_TIME
,EA_DELFLAG
from EXCESSIVE_ALARM,MONITORING
<where>
EA_DELFLAG= '0' AND EXCESSIVE_ALARM.EA_EFID=MONITORING.MO_FID and MONITORING.MO_STAUTS='0'
<if test="EA_ENAME != null and EA_ENAME !=''">
and EA_ENAME =#{EA_ENAME,jdbcType=VARCHAR}
</if>
<if test="EA_INDICATOR != null and EA_INDICATOR !=''">
and EA_INDICATOR =#{EA_INDICATOR,jdbcType=VARCHAR}
</if>
<if test="EA_INDICATOR != null and EA_INDICATOR !=''">
and EA_INDICATOR =#{EA_INDICATOR,jdbcType=VARCHAR}
</if>
<if test="EA_EFID_IN != null and EA_EFID_IN !=''">
and (${EA_EFID_IN})
</if>
<if test="EA_HCFID_IN != null and EA_HCFID_IN !=''">
and (${EA_HCFID_IN})
</if>
<if test="STARTIME != null and STARTIME !=''">
and to_date(to_char(EA_COLLECT_TIME, 'yyyy-MM-dd HH24:MI'),'yyyy-MM-dd HH24:MI') >= to_date(#{STARTIME}, 'yyyy-MM-dd HH24:MI')
</if>
<if test="ENDTIME != null and ENDTIME !=''">
and to_date(to_char(EA_COLLECT_TIME, 'yyyy-MM-dd HH24:MI'),'yyyy-MM-dd HH24:MI') <= to_date(#{ENDTIME}, 'yyyy-mm-dd hh24:mi:ss')
</if>
</where>
</select>
<select id="findExInfo" parameterType="excessiveAlarm" resultType="excessiveAlarm">
select EA_DAYCOUNT,EA_EFID,rownum_ from (
select Count(1) as EA_DAYCOUNT,EA_EFID,row_number() over(order by count(1) DESC) as rownum_ from (
SELECT EA_EFID,TO_CHAR(EA_COLLECT_TIME, 'yyyy-MM-dd') AS EA_COLLECT_TIME
FROM EXCESSIVE_ALARM
<where>
EA_DELFLAG= '0'
<if test="EA_HCFID != null and EA_HCFID!=''">
and (${EA_HCFID})
</if>
</where>
GROUP BY EA_EFID,TO_CHAR(EA_COLLECT_TIME, 'yyyy-MM-dd')
)
GROUP BY EA_EFID
order by EA_DAYCOUNT DESC
)row_
WHERE row_.rownum_ between #{startPage} and #{endPage}
</select>
<select id="findExInfoCount" parameterType="excessiveAlarm" resultType="Integer">
select count(1)
from (select Count(1) as EA_DAYCOUNT, EA_EFID
from (SELECT EA_EFID,TO_CHAR(EA_COLLECT_TIME, 'yyyy-MM-dd') AS EA_COLLECT_TIME
FROM EXCESSIVE_ALARM
<where>
EA_DELFLAG= '0'
<if test="EA_HCFID != null and EA_HCFID!=''">
and (${EA_HCFID})
</if>
</where>
GROUP BY EA_EFID, TO_CHAR(EA_COLLECT_TIME, 'yyyy-MM-dd'))
GROUP BY EA_EFID
order by EA_DAYCOUNT DESC)
</select>
</mapper>
Model层(实体类)
package enytek.com.cn.model.excessiveAlarm;
import java.lang.Integer;
import java.lang.String;
import java.util.Date;
/**
*<p>Title:设备报警表 </p>
*<p>Description: </p>
*<p>Company: </p>
* @author YH
* @date 2019-09-04 15:04:33
*/
public class ExcessiveAlarm {
/**
* 主键
* creater:YH
* time:2019-09-04 15:04:33
*/
private Integer EA_ID;
/**
* 伪列
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_FID;
/**
* 单位fid
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_HCFID;
/**
* 设备fid
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_EFID;
/**
* 设备名称
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_ENAME;
private String EA_ETYPE;//设备类型
/**
* 监测指标
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_INDICATOR;
/**
* 数据采集值
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_VALUE;
/**
* 阈值上限
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_THRESHOLD_UP;
/**
* 阈值下限
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_THRESHOLD_LOWER;
/**
* 阈值名称
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_THRESHOLD_NAME;
/**
* 数据采集时间
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_COLLECT_TIME;
/**
* 创建时间
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_CREATE_TIME;
/**
* 删除标记
* creater:YH
* time:2019-09-04 15:04:33
*/
private String EA_DELFLAG;
//开始
private Integer StartPage;
private Integer EndPage;
private String rownum_;//序号
//开始时间
private String STARTIME;
//结束时间
private String ENDTIME;
private String EA_HCFID_IN;//单位fid in 查询 2019年9月16日13:55:073
private String EA_EFID_IN;//监测点 fid in 查询 2019年9月16日18:41:01
private String EA_HC_NAME; //单位名称
private String EA_DAYCOUNT; //超标天数
public String getRownum_() {
return rownum_;
}
public void setRownum_(String rownum_) {
this.rownum_ = rownum_;
}
public String getEA_EFID_IN() {
return EA_EFID_IN;
}
public void setEA_EFID_IN(String eA_EFID_IN) {
EA_EFID_IN = eA_EFID_IN;
}
public String getEA_HCFID_IN() {
return EA_HCFID_IN;
}
public void setEA_HCFID_IN(String eA_HCFID_IN) {
EA_HCFID_IN = eA_HCFID_IN;
}
public String getSTARTIME() {
return STARTIME;
}
public void setSTARTIME(String sTARTIME) {
STARTIME = sTARTIME;
}
public String getENDTIME() {
return ENDTIME;
}
public void setENDTIME(String eNDTIME) {
ENDTIME = eNDTIME;
}
public Integer getStartPage() {
return StartPage;
}
public void setStartPage(Integer startPage) {
StartPage = startPage;
}
public Integer getEndPage() {
return EndPage;
}
public void setEndPage(Integer endPage) {
EndPage = endPage;
}
public Integer getEA_ID() {
return EA_ID;
}
public void setEA_ID(Integer EA_ID){
this.EA_ID = EA_ID;
}
public String getEA_FID() {
return EA_FID;
}
public void setEA_FID(String EA_FID){
this.EA_FID = EA_FID;
}
public String getEA_HCFID() {
return EA_HCFID;
}
public void setEA_HCFID(String EA_HCFID){
this.EA_HCFID = EA_HCFID;
}
public String getEA_EFID() {
return EA_EFID;
}
public void setEA_EFID(String EA_EFID){
this.EA_EFID = EA_EFID;
}
public String getEA_ENAME() {
return EA_ENAME;
}
public void setEA_ENAME(String EA_ENAME){
this.EA_ENAME = EA_ENAME;
}
public String getEA_INDICATOR() {
return EA_INDICATOR;
}
public void setEA_INDICATOR(String EA_INDICATOR){
this.EA_INDICATOR = EA_INDICATOR;
}
public String getEA_VALUE() {
return EA_VALUE;
}
public void setEA_VALUE(String EA_VALUE){
this.EA_VALUE = EA_VALUE;
}
public String getEA_THRESHOLD_UP() {
return EA_THRESHOLD_UP;
}
public void setEA_THRESHOLD_UP(String EA_THRESHOLD_UP){
this.EA_THRESHOLD_UP = EA_THRESHOLD_UP;
}
public String getEA_THRESHOLD_LOWER() {
return EA_THRESHOLD_LOWER;
}
public void setEA_THRESHOLD_LOWER(String EA_THRESHOLD_LOWER){
this.EA_THRESHOLD_LOWER = EA_THRESHOLD_LOWER;
}
public String getEA_THRESHOLD_NAME() {
return EA_THRESHOLD_NAME;
}
public void setEA_THRESHOLD_NAME(String EA_THRESHOLD_NAME){
this.EA_THRESHOLD_NAME = EA_THRESHOLD_NAME;
}
public String getEA_COLLECT_TIME() {
return EA_COLLECT_TIME;
}
public void setEA_COLLECT_TIME(String eA_COLLECT_TIME) {
EA_COLLECT_TIME = eA_COLLECT_TIME;
}
public String getEA_CREATE_TIME() {
return EA_CREATE_TIME;
}
public void setEA_CREATE_TIME(String eA_CREATE_TIME) {
EA_CREATE_TIME = eA_CREATE_TIME;
}
public String getEA_DELFLAG() {
return EA_DELFLAG;
}
public void setEA_DELFLAG(String EA_DELFLAG){
this.EA_DELFLAG = EA_DELFLAG;
}
public String getEA_HC_NAME() {
return EA_HC_NAME;
}
public void setEA_HC_NAME(String eA_HC_NAME) {
EA_HC_NAME = eA_HC_NAME;
}
public String getEA_DAYCOUNT() {
return EA_DAYCOUNT;
}
public void setEA_DAYCOUNT(String eA_DAYCOUNT) {
EA_DAYCOUNT = eA_DAYCOUNT;
}
public String getEA_ETYPE() {
return EA_ETYPE;
}
public void setEA_ETYPE(String eA_ETYPE) {
EA_ETYPE = eA_ETYPE;
}
}
本文标签:
版权声明:本文标题:查询和导出 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dianzi/1727799414a1130649.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论