/**
* easyui AJAX请求数据
* @param clockin
* @param request
* @param response
* @param dataGrid
*/
@RequestMapping(params = "datagrid")
public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
TSUser user = ResourceUtil.getSessionUser();
List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
String workNum = "";
if(StringUtils.isNotBlank(user.getEmp())) {//如果登录者存在员工id 就只查询自己的员工信息
JformResumeInfoEntity jformResumeInfo = jformResumeInfoService.getEntity(JformResumeInfoEntity.class, user.getEmp());
workNum = jformResumeInfo.getWorkNum();
}
/**
* 早上
gapTime = (
case when datediff(MINUTE,CONVERT(VARCHAR(100),sj,23) + ' 08:00:00',sj) < -200 then '无'
when datediff(MINUTE,CONVERT(VARCHAR(100),sj,23) + ' 08:00:00',sj) > -200 then '正常'
when datediff(MINUTE,CONVERT(VARCHAR(100),sj,23) + ' 08:00:00',sj) <= 0 then '正常'
when datediff(MINUTE,CONVERT(VARCHAR(100),sj,23) + ' 08:00:00',sj) > 0 then '迟到'
else '其他' end
)
*/
StringBuffer sql = new StringBuffer("select a.*,b.user_no as userNo,gapTime = (datediff(MINUTE,CONVERT(VARCHAR(100),sj,23) + ' 08:00:00',sj)) from kt_jl as a,dt_user as b WHERE a.user_serial = b.user_serial ");
if(StringUtils.isNotBlank(workNum)) {
sql.append(" and b.user_no = '" + workNum + "'");
}else {
String userNo = request.getParameter("userNo");
if(StringUtils.isNotBlank(userNo)) {
sql.append(" and b.user_no = '" + userNo + "'");
}
}
String sj_begin = request.getParameter("sj_begin");
if(StringUtils.isNotBlank(sj_begin)) {
sql.append(" and sj >= '" + sj_begin + " 00:00:00'");
}
String sj_end = request.getParameter("sj_end");
if(StringUtils.isNotBlank(sj_end)) {
sql.append(" and sj <= '" + sj_end + " 23:59:59'");
}
if(StringUtils.isBlank(sj_end) && StringUtils.isBlank(sj_begin) ) {
//默认当月
String year = DateUtilsEx.getCurrYear();
String month = DateUtilsEx.getCurrMothe();
/*String lostDay = String.valueOf(DateUtilsEx.getMonthLastDay());//当月最后一天
sql.append(" and sj BETWEEN '"+year+"-"+month+"-01 00:00:00' and '"+year+"-"+month+"-"+lostDay+" 23:59:59'");*/
//默认当天
String nowDay = String.valueOf(DateUtilsEx.getMonthNowDay());
sql.append(" and sj BETWEEN '"+year+"-"+month+"-"+nowDay+" 00:00:00' and '"+year+"-"+month+"-"+nowDay+" 23:59:59'");
}
if(StringUtils.isNotBlank(dataGrid.getSort())) {
sql.append(" order by "+ dataGrid.getSort() +" "+dataGrid.getOrder());
}else {
sql.append(" order by sj DESC");
}
//测试jdbctemplate
data = DynamicDBUtil.findList("sql-server2008",sql.toString());
List<Map<String, Object>> showList = null;//要显示的数据
int showLen = dataGrid.getRows();//动态得到前端需要显示多少条
int totlePage = data.size()%showLen == 0 ? data.size()/showLen : data.size()/showLen + 1;
if(dataGrid.getPage() == 1 ) {
if(showLen > data.size()) {
showLen = data.size();
}
showList = data.subList(0, showLen);
}else {
if(dataGrid.getPage() == totlePage) {
showList = data.subList(((dataGrid.getPage()-1)*showLen),data.size());
}else {
showList = data.subList(((dataGrid.getPage()-1)*showLen),((dataGrid.getPage()-1)*showLen)+showLen);
}
}
dataGrid.setResults(showList);
dataGrid.setTotal(data.size());//总条数
TagUtil.datagrid(response, dataGrid);
}
<t:datagrid name="clockinList" checkbox="true" pagination="true" fitColumns="true" title="员工考勤(默认当天考勤情况)" sortName="sj" actionUrl="clockinController.do?datagrid" idField="bh" fit="true" queryMode="group">
<t:dgCol title="bh" field="bh" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="员工" field="userNo" query="true" dictionary="jform_resume_info,work_num,name" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="用户主见" field="user_serial" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="考勤日期" field="sj" formatter="yyyy-MM-dd hh:mm:ss" query="true" queryMode="group" extendParams="styler:fmtype" width="120"></t:dgCol>
<t:dgCol title="考勤方向" field="fx" queryMode="single" dictionary="clockin" width="120"></t:dgCol>
<t:dgCol title="iden" field="iden" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="正常打开记录id" field="dev_serial" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="打开状态" field="dev_state" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="jlzp_serial" field="jlzp_serial" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="gly_no" field="gly_no" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="lx" field="lx" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="审核" field="shenhe" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="yich" field="yich" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="打卡距上班差分钟数" field="gapTime" hidden="true" queryMode="single" width="120"></t:dgCol>
<t:dgCol title="操作" field="opt" width="100"></t:dgCol>
