首先,看看最终效果图:
1、datagrid.jsp页面源码
<meta charset="UTF-8" />
//导入官方的js和css,href/scr路径根据文件所在位置不同,而不一样
<link href="/mobilemode/apps/solex/js/easyui/themes/default/easyui.css" rel="stylesheet" type="text/css" />
<link href="/mobilemode/apps/solex/js/easyui/themes/icon.css" rel="stylesheet" type="text/css" />
<link href="/mobilemode/apps/solex/js/easyui/demo.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="/mobilemode/apps/solex/js/easyui/jquery-1.8.0.min.js"></script>
<script type="text/javascript" src="/mobilemode/apps/solex/js/easyui/jquery.easyui.min.js"></script>
<table id="dg" class="easyui-propertygrid" title="待办事项 [ <span id='count' style='font-family:微软雅黑; color:#EE2C2C;font-size:14px'></span> ]"
data-options="rownumbers:true,
singleSelect:false,
url:'/mobilemode/apps/solex/test/dbsy/queryToDo.jsp',
fit:true,
selectOnCheck: false,
onDblClickRow: function(){ openOldOa(); },
method: 'get',
showGroup: true,
scrollbarSize: 0,
groupFormatter: groupFormatter,
onLoadSuccess:function(data){
$('#dg').datagrid('collapseGroup');
},
columns: mycolumns">
</table>
<script type="text/javascript">
var mycolumns = [[
{field:'code',title:'单号',width:100,sortable:true},
{field:'description_',title:'项目',width:100,sortable:true},
{field:'name_',title:'当前状态',width:100,sortable:true},
{field:'requester',title:'申请人',width:100,sortable:true},
{field:'create_',title:'到达时间',width:100,resizable:false}
]];
//加载页面queryToJson.jsp
$(function(){
var pager = $('#dg').datagrid().datagrid('getPager');
$.ajax({
type:'get',//请求方式
url:'/mobilemode/apps/solex/test/dbsy/queryToDo.jsp', // AJAX HTTP请求接口
data:'',
dataType:'json',//请求类型为json
timeout:7000,//请求超时后停止请求
success: function(json){
var d=json.total;//直接.total获取total属性
$('#count').html(d);
}
});
})
//双击表格行,在新窗口中打开页面,并登陆到旧oa
function openOldOa(){
window.open('/interface/Entrance.jsp?id=osapdev&gopage=/login!forward.action');
}
//行分组之后,在页面上标题显示的格式
function groupFormatter(fvalue, rows){
return fvalue + ' ( <span style="color:#EE2C2C;font-size:13px">' + rows.length + ' </span>'+')';
}
</script>
2、数据查询的jsp页面
<%@page import="java.util.HashMap"%>
<%@page import="java.util.Map"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%>
<%@page import="java.util.ArrayList"%>
<%@page import="weaver.interfaces.datasource.DataSource"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="org.apache.commons.lang.time.DateUtils"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONArray"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.Date"%>
<%@page import="java.util.Calendar"%>
<%@page import="weaver.hrm.*"%>
<%@page import="weaver.general.StaticObj"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
//除了需要导入的包和编码,其他不要导入
//获取当前登录用户的userCode
User user = HrmUserVarify.getUser(request, response);/*泛微系统当前登录用户*/
//String loginId=request.getParameter("loginId");
String loginId="";
if(user!=null){
loginId=user.getLoginid();
};
/* sql语句中日期的来源
1、将待办结束日期设为当前日期,待办开始日期设为当前日期推前一年,且为当月第一天
2、判断user里面的待办开始日期和结束日期,如果不为空,则将待办开始日期和结束日期赋值给startDate和endDate
3、将endDate延后一天
4、转为规定格式
*/
Date endDate = new Date();//获取当前日期
Calendar cal = Calendar.getInstance();
cal.setTime(endDate);//日期设为当前日期
cal.set(Calendar.DAY_OF_MONTH, 1);//当前日期对象cal的天数,设为当月的第一天
cal.add(Calendar.YEAR, -1);//当前日期对象cal的年数减少一年
Date startDate=cal.getTime();//该日期为:当前日期推前一年,并将当月天数设为第一天
Calendar cal1 = Calendar.getInstance();
cal1.setTime(endDate);
cal1.add(Calendar.DAY_OF_YEAR, 1);
endDate = cal1.getTime();//设置为当前日期加1天
SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//将日期转为String类型
String endDateStr = dateFormat1.format(endDate);
String startDateStr = dateFormat1.format(startDate);
//sql语句
String sql = "select t.code,t.item,t.description_,t.name_,t.url,t.requester,t.create_,t.prestart_,t.actorid_,t.modulecode "
+"from jbpm_taskinstance t "
+"where t.create_ between to_date('"
+ startDateStr
+ "','YYYY-MM-DD')"
+ " and to_date('"
+ endDateStr
+ "','YYYY-MM-DD')"
+"and t.issuspended_=0 "
+"and t.isopen_=1 "
+"and t.actorid_='"+loginId+"' "
+"and not exists (select 1 from jbpm_token t1 where t1.id_ =t.token_ and t1.issuspended_=1)";
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
DataSource ds = (DataSource) StaticObj.getServiceByFullname(("datasource.196test"), DataSource.class);
conn = ds.getConnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
//将结果集用Map接收
List<Map<String,String>> list = new ArrayList();
while (rs.next()) {
Map<String,String> map=new HashMap();
map.put("code", rs.getString("code"));
map.put("item", rs.getString("item"));
map.put("description_", rs.getString("description_"));
map.put("name_", rs.getString("name_"));
map.put("url", rs.getString("url"));
map.put("requester", rs.getString("requester"));
map.put("create_", rs.getString("create_"));
map.put("prestart_", rs.getString("prestart_"));
map.put("actorid_", rs.getString("actorid_"));
map.put("modulecode", rs.getString("modulecode"));
map.put("group", rs.getString("description_"));//前端分组
list.add(map);
}
//拼接每条记录的code、description_、name_、url、requester字段,然后去重
List<Map<String,String>> noRepeatList = new ArrayList();
Date date = new Date();
String key="";
String strSum="";
//遍历Map的list集合
for(Map<String,String> m:list){
key = m.get("code")+m.get("description_")+m.get("name_")+m.get("url")+m.get("requester");
//如果strSum里面包含了key,表示当前对象重复,不在添加进去
if (strSum.indexOf(key) > -1) {
} else {
strSum = strSum + ";@^" + key;
noRepeatList.add(m);//不重复,则添加进来
}
}
list = noRepeatList;
JSONObject jsonObject = new JSONObject();
JSONArray jsonArray =JSONArray.fromObject(list);
int totalSize = jsonArray.size();
//多条
jsonObject.put("total", totalSize);
jsonObject.put("rows", jsonArray);
out.print(jsonObject);//最终的到得数据
} catch (Exception e) {
out.print(e.getMessage());
} finally {
try {
rs.close();
} catch (Exception e) {
}
try {
st.close();
} catch (Exception e) {
}
try {
conn.close();
} catch (Exception e) {
}
}
%>