`
jayce洪
  • 浏览: 24070 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

paginateAsMapByNativeSql

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="sidebar responsive" id="sidebar"> -->
<!-- </div> -->
<div class="main-content">
<div class="page-content">
<div class="row">
<div class="col-xs-12">
<!-- PAGE CONTENT BEGINS -->

<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>
                               
<!----项目创建/查询的表单 ENDS--->
</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>
<!----项目列表的表格 ENDS--->
</div>
</div>


<!-- PAGE CONTENT ENDS -->
</div>
</div>
<!-- page-content ENDS -->
</div>
<!-- main-content ENDS -->
</div>

<div class="footer">
<%@ include file="/common/footer_ace.jsp"%>
</div>
<!-- footer -->
</div>
<!-- /.main-container -->
</form>
<!-- ace scripts -->
<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>
分享到：
| radio取消选中
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics