paginateAsMapByNativeSql

jayce洪

浏览: 23591 次
性别:
来自: 南京

最近访客更多访客>>

justplayoop1

akacd

eliming

Lstoryc

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

sql

@SuppressWarnings("unchecked")
public String listRiskList(){
String whereCondition = "where t.f_inspection_result='不正常' ";
/*查询条件*/
/*网元名称*/
String netElementName = (String) this.getParameter("q_netElementName", String.class);
if(netElementName != null && !"".equals(netElementName)){
String netElementSql = "select f_id from t_net_element t where t.f_net_element_name like '%"+netElementName.trim()+"%'";
List<Map> netElementIdList = manager.findByNativeSqlAsMap(netElementSql, null);
if(netElementIdList.size()==1) whereCondition+=" and r.f_net_element_id = "+netElementIdList.get(0).get("f_id");
if(netElementIdList.size() > 1){
whereCondition+=" and(r.f_net_element_id = "+netElementIdList.get(0).get("f_id");
for (int i = 1; i < netElementIdList.size(); i++) {
whereCondition+=" or r.f_net_element_id = "+netElementIdList.get(i).get("f_id");
}
whereCondition+=")";
}
}
/*巡检人*/
String inspectionPerson = (String) this.getParameter("q_inspectionPerson", String.class);
if(inspectionPerson != null && !"".equals(inspectionPerson)){
String userSql = "select f_id from app_user u where u.f_username like '%"+inspectionPerson.trim()+"%'";
List<Map> userIdList = manager.findByNativeSqlAsMap(userSql, null);
if(userIdList.size()==1) whereCondition+=" and r.f_user_id = "+userIdList.get(0).get("f_id");
if(userIdList.size() > 1){
whereCondition+=" and(r.f_user_id = "+userIdList.get(0).get("f_id");
for (int i = 1; i < userIdList.size(); i++) {
whereCondition+=" or r.f_user_id = "+userIdList.get(i).get("f_id");
}
whereCondition+=")";
}
}
/*巡检时间*/
String inspectionTime = (String) this.getParameter("q_inspectionTime", String.class);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(inspectionTime != null && !"".equals(inspectionTime)){
inspectionTime += " 00:00:00";
Date date1 = null;
try {
date1 = sdf.parse(inspectionTime);
whereCondition+=" and r.f_inspection_time = '"+new Timestamp(date1.getTime())+"'";
} catch (ParseException e) {
e.printStackTrace();
}
}
String sql = "SELECT t.f_inspection_record_id,r.f_inspection_time,r.f_user_id,f_net_element_id,count(*) as cnt "
+ "FROM njupt.t_inspec_record_detail t "
+ "left join t_inspection_record r on r.f_id=t.f_inspection_record_id "
+ whereCondition
+ " group by t.f_inspection_record_id";
int startIndex = pageNo * pageSize - pageSize;
riskListPagination = manager.paginateAsMapByNativeSql(sql, null, startIndex, pageSize);
return "riskList";
}

<%@page pageEncoding="UTF-8" import="java.io.*"%>
<%@include file="/common/taglibs.jsp" %>
<c:set var="riskList" value="${ riskListPagination.items}"/>

<!DOCTYPE html>
<html>
<head>
<title>风险列表</title>
<%@ include file="/common/meta_ace.jsp"%>
<link rel="stylesheet" href="${ctx}/assets_ace/css/jquery-ui.min.css"/>
</head>

<body class="no-skin">

<form action="${ ctx }/njupt/inspectionRecordDetail.action" method="post" class="form-horizontal" role="form">

<div class="main-container" id="main-container">


<div class="main-content">
<div class="page-content">
<div class="row">
<div class="col-xs-12">


<font color="red">${ msg}</font>
<div class="widget-box widget-color-blue">
<div class="widget-header">
<h5 class="widget-title">风险列表</h5>
<div class="widget-toolbar">
</div>
</div>
<div class="form-group">

<table >
<tr>
<td>网元名称 :</td>
<td>
        <input type="text" name="q_netElementName" id="q_netElementName" value="${ param.q_netElementName}" class="input-small-z"/>
</td>
</tr>
<tr>
<td>巡检人 :</td>
<td>
        <input type="text" name="q_inspectionPerson" id="q_inspectionPerson" value="${ param.q_inspectionPerson}" class="input-small-z"/>
</td>
</tr>
<tr>
<td>巡检时间 :</td>
<td>
        <input type="text" name="q_inspectionTime" id="q_inspectionTime" value="${ param.q_inspectionTime}" class="input-small-z"/>
</td>
</tr>
    </table>
<button name="method:listRiskList" class="btn btn-primary btn-sm" id="btn_search">查询</button>


</div>
<div id="main">
<div class='blog_main_title' style="font-size: 15px;">



<c:set var="pagination" value="${projectPagination}" />
<table id="sample-table-1" class="table table-striped table-bordered table-hover">

<thead>
<tr>

<th>巡检人</th>

<th>巡检时间</th>

<th>网元名称</th>

<th>不正常巡检项数量</th>

</tr>
</thead>

<tbody>
<c:set var="pagination" value="${ riskListPagination}"/>
<c:set var="searchMethod" value="listRiskList"/>

   <c:forEach var="risk" items="${ riskList}" varStatus="status">
<td>
<nativeSql:sqlQueryForUnique sql="select f_username from app_user where f_id = ${ risk.f_user_id}" id="user"/>
${ user.f_username}
</td>

<td>
<fmt:formatDate value="${ risk.f_inspection_time}" pattern="yyyy-MM-dd"/>
</td>

<td>
<nativeSql:sqlQueryForUnique sql="select f_net_element_name from t_net_element where f_id = ${ risk.f_net_element_id}" id="netElementName"/>
${ netElementName.f_net_element_name}
</td>
<td>
<a href="${ ctx}/njupt/inspectionRecord.action?method:editFormForRisk&inspectionRecord.id=${ risk.f_inspection_record_id}">${ risk.cnt}</a>
</td>

           </tr>
       </c:forEach>

</tbody>
<tr class="group_row">
<td colspan="100" style="border-top: 1px solid #CCCCCC;">
<%@ include file="/common/pagination_ace.jsp"%>
</td>
</tr>
</table>

</div>
</div>


</div>
</div>

</div>

</div>

<div class="footer">
<%@ include file="/common/footer_ace.jsp"%>
</div>

</div>

</form>

<script src="${ ctx }/ace/js/ace-elements.min.js"></script>
<script src="${ ctx }/ace/js/ace.min.js"></script>
<script type="text/javascript">
$((function($){
$.datepicker.regional['zh-CN'] = {
    clearText: '清除',
    clearStatus: '清除已选日期',
    closeText: '关闭',
    closeStatus: '不改变当前选择',
    prevText: '上月',
    prevStatus: '显示上月',
    prevBigText: '<<',
    prevBigStatus: '显示上一年',
    nextText: '下月',
    nextStatus: '显示下月',
    nextBigText: '>>',
    nextBigStatus: '显示下一年',
    currentText: '今天',
    currentStatus: '显示本月',
    monthNames: ['一月','二月','三月','四月','五月','六月', '七月','八月','九月','十月','十一月','十二月'],
    monthNamesShort: ['一','二','三','四','五','六', '七','八','九','十','十一','十二'],
    monthStatus: '选择月份',
    yearStatus: '选择年份',
    weekHeader: '周',
    weekStatus: '年内周次',
    dayNames: ['星期日','星期一','星期二','星期三','星期四','星期五','星期六'],
    dayNamesShort: ['周日','周一','周二','周三','周四','周五','周六'],
    dayNamesMin: ['日','一','二','三','四','五','六'],
    dayStatus: '设置 DD 为一周起始',
    dateStatus: '选择 m月 d日, DD',
    dateFormat: 'yy-mm-dd',
    firstDay: 1,
    initStatus: '请选择日期',
    isRTL: false};
$.datepicker.setDefaults($.datepicker.regional['zh-CN']);
})(jQuery));
jQuery(function($) {
$( "#q_inspectionTime" ).datepicker({
dateFormat:'yy-mm-dd',
showOtherMonths: true,
selectOtherMonths: true,
changeMonth: true,
changeYear: true
});
})
</script>
</body>
</html>

分享到：