请假单流程开发
在开发中，有时，会遇到，求两个日期，相减求天数的需求，这个小例子是项目中遇到的，项目需要要为某部开发一个请假流程（不要jbpm,activity）
选择日期起止日期后，求请假天数
<script type="text/javascript"> var startDate; var endDate; loadCallBack = function() { $("#addLeaveAloneForm").validate({ meta : 'validate' }); startDate = 0; endDate = 0; $('#proxyPerson').on('change',function(id, text){ $('#proxyPosition').val(''); $('#proxyPhone').val(''); if(text){ $.ajax({ url : basePath + '/soft617/leaveAlone/getUserInfo.json', type : 'POST', data : { userId : text }, success : function(msg) { if(msg){ var result = msg.split("#@"); if("null"!=result[0]){ $('#proxyPosition').val(result[0]); } if("null"!=result[1]){ $('#proxyPhone').val(result[1]); } //$("#leaveSubject").focus(); } }, error : function() { $.sywDialog.warn("提示", "操作过程中出现错误。"); } }); } }); $("#leaveStart").on("focus", function(event, value, text) { startDate = 0; $('#totalDay').val(0); startDate =$('#leaveStart').val(); if (endDate != "" && endDate != null && endDate != 0) { var countDay = getMct(startDate, endDate); if(isNaN(countDay) ){ $('#totalDay').val(''); }else{ $('#totalDay').val(countDay); } } }); //focus $("#leaveEnd").on("focus", function(event, value, text) { endDate = 0; $('#totalDay').val(0); endDate =$('#leaveEnd').val(); if (startDate != "" && startDate != null && startDate != 0) { var countDay = getMct(startDate, endDate); //$('#totalDays').html(countDay); if(isNaN(countDay) ){ $('#totalDay').val(''); }else{ $('#totalDay').val(countDay); } } }); } function getMct(strDateStart, strDateEnd) { var strSeparator="-" ; var oDate1; var oDate2; var iDays; oDate1=strDateStart.split(strSeparator); oDate2=strDateEnd.split(strSeparator); var strDateS=new Date(oDate1[0],oDate1[1]-1,oDate1[2]); var strDateE=new Date(oDate2[0],oDate2[1]-1,oDate2[2]); iDays=parseInt(Math.abs(strDateS-strDateE)/1000/60/60/24); if (iDays <0) { alert( "结束时间必须大于开始时间!"); } return iDays+1; } </script>
开发这个请假单流程，得到用户的认可，我和伙伴都感到很有成就感，因为这个里面用到了好多算法，其中有一个就是数据库算法：
比如：审批状态的一个查询，可以多选（审批中，已审批，未销假，已销假，驳回），这个时候，sql 如何拼写，这里就用到了一个算法
// 拼接查询参数 if (param.get("appleStatusSerch") != null && !"".equals(param.get("appleStatusSerch"))) { String statusSerch = param.get("appleStatusSerch").toString(); String[] strs = statusSerch.split(","); if (strs.length == 1) { if (strs[0].equals("1")) { sbSql.append(" and t.APPROVAL_STATUS =1 "); } if (strs[0].equals("2")) { //sbSql.append(" and t.APPROVAL_STATUS =2 and t.SICK_TIME is null "); sbSql.append(" and t.APPROVAL_STATUS =2 "); } if (strs[0].equals("3")) { //sbSql.append(" and t.APPROVAL_STATUS =2 and t.SICK_TIME is not null "); sbSql.append(" and t.APPROVAL_STATUS =4"); } if (strs[0].equals("4")) { sbSql.append(" and t.APPROVAL_STATUS =3 "); } } else if (strs.length == 4) { sbSql.append(" and t.APPROVAL_STATUS in (1,2,3,4) "); } else if (strs.length > 1) { sbSql.append(" and ( "); for (int i = 0; i < strs.length; i++) { if (i > 0) { sbSql.append(" or "); } if (strs[i].equals("1")) { sbSql.append(" t.APPROVAL_STATUS =1 "); } if (strs[i].equals("2")) { //sbSql.append(" (t.APPROVAL_STATUS =2 and t.SICK_TIME is null)"); sbSql.append(" (t.APPROVAL_STATUS =2 )"); } if (strs[i].equals("3")) { //sbSql.append(" (t.APPROVAL_STATUS =2 and t.SICK_TIME is not null)"); sbSql.append(" (t.APPROVAL_STATUS =4 )"); } if (strs[i].equals("4")) { sbSql.append(" (t.APPROVAL_STATUS =3 )"); } } sbSql.append(" )"); } } else { sbSql.append(" and t.APPROVAL_STATUS not in(1,2,3,4)"); }
上面的代码是version_1其实优化之后应该这样写，
if (param.get("appleStatusSerch") != null && !"".equals(param.get("appleStatusSerch"))) { String statusSerch = param.get("appleStatusSerch").toString(); String[] strs = statusSerch.split(","); if (strs.length > ＝1) { sbSql.append(" and ( "); for (int i = 0; i < strs.length; i++) { if (i > 0) { sbSql.append(" or "); } if (strs[i].equals("1")) { sbSql.append(" t.APPROVAL_STATUS =1 "); } if (strs[i].equals("2")) { //sbSql.append(" (t.APPROVAL_STATUS =2 and t.SICK_TIME is null)"); sbSql.append(" (t.APPROVAL_STATUS =2 )"); } if (strs[i].equals("3")) { //sbSql.append(" (t.APPROVAL_STATUS =2 and t.SICK_TIME is not null)"); sbSql.append(" (t.APPROVAL_STATUS =4 )"); } if (strs[i].equals("4")) { sbSql.append(" (t.APPROVAL_STATUS =3 )"); } } sbSql.append(" )"); } } else { sbSql.append(" and t.APPROVAL_STATUS not in(1,2,3,4)"); }
其实，至少这个流程是如何实现的，算法也不是很简单：
审批列表是根据当前登录用户的角色，然后跟流程定义的角色比较，查找到对应的审批角色，在请假单的增加页面，自动会把审批列表算出：
流程定义表：是审批角色和使用角色
流程定义这里：你看关系是一对多的（其实是多对多的），表结构设置如下：
SOFT617_LEAVE_APPROVAL_PROCESS 流程定义表1,2,3,而下面的子表存的是分割后的id
储存时，审批角色对应的是一个，使用角色存的是
SOFT617_LEAVE_USE_ROLE 流程定义子表
关键代码：
前面的准备工作都做好了，现在开始我们的请假单的设计：
审批表 SOFT617_LEAVE_APPROVAL：
请假单从添加时，就启动了一个流程：
关键代码：
// /////////////////////////// 构建审批列表start////////////////////////// String sqlString = "select s.role_id_approval,r.role_name, u.user_name,u.user_id\n" + " from SOFT617_LEAVE_USE_ROLE t, soft617_leave_approval_process s,sys_role r,sys_user u ,sys_role_user sr\n" + "where t.USE_ROLE_ID in ( select t.role_id\n" + " from sys_role t\n" + " where t.role_id in\n" + " (select t.role_id\n" + " from sys_role_user t\n" + " where t.USER_ID =\n" + " '" + userId + "'))\n" + "and t.LEAVE_APPPROCESS_ID=s.id\n" + "and s.role_id_approval=r.role_id\n + "and sr.ROLE_ID=s.role_id_approval\n" + " and sr.user_id=u.user_id\n" + "order by s.SORT_ORDER";
执行请假单的保存操作：
String jsonString = request.getParameter("jsonString");
String userId = identity.getUserId();
JSONObject jsonObject = JSONObject.fromObject(jsonString);
String createTime = jsonObject.get("createTime").toString();
JSONUtils.getMorpherRegistry().registerMorpher(
new DateMorpher(new String[] { "yyyy-MM-dd HH:mm:ss",
"yyyy-MM-dd", "yyyy-MM-dd't'HH:mm:ss" }, new Date()));
LeaveAlone bean = (LeaveAlone) JSONObject.toBean(jsonObject,
LeaveAlone.class);
SimpleDateFormat sdfs = new SimpleDateFormat("yyyy-MM-dd HH:mm");
bean.setCreateTime(sdfs.parse(createTime));
// /////////////////发送消息start////////////////
Message message = new Message();
message.setTitle(bean.getUserName() + "请假，" + "委托您代理假期工作");
// message.setContent(bean.getUserName() + "请假" + bean.getTotalDay() +
// "天"
// + bean.getLeaveSubject());
SimpleDateFormat sdff = new SimpleDateFormat("yyyy-MM-dd");
message.setContent("请假时间：" + sdff.format(bean.getLeaveStart()) + "至"
+ sdff.format(bean.getLeaveEnd()));
message.setMessageImportance(MessageImportance.Unimportant);
message.setType(MessageType.Human);
message.setReceviers(bean.getProxyPerson().split(","));
Map<String, Object> map = new HashMap<String, Object>();
map.put("messageIsBack", MessageConstants.MESSAGE_IS_BACK_NOT);
message.setOtherInfo(map);
message.setAttachments("".split(","));
message.setReceiveRule("");
message.setSender(bean.getUserId());
// 消息服务器发送
message.send();
// /////////////////发送消息end ////////////////
// ///////////////////////////保存审批信息start//////////////////////////
String sqlString = "select s.role_id_approval,r.role_name, u.user_name,u.user_id\n"
+ " from SOFT617_LEAVE_USE_ROLE t, soft617_leave_approval_process s,sys_role r,sys_user u ,sys_role_user sr\n"
+ "where t.USE_ROLE_ID in ( select t.role_id\n"
+ " from sys_role t\n"
+ " where t.role_id in\n"
+ " (select t.role_id\n"
+ " from sys_role_user t\n"
+ " where t.USER_ID =\n"
+ " '"
+ userId
+ "'))\n"
+ "and t.LEAVE_APPPROCESS_ID=s.id\n"
+ "and s.role_id_approval=r.role_id\n"
+ "and sr.ROLE_ID=s.role_id_approval\n"
+ " and sr.user_id=u.user_id\n" + "order by s.SORT_ORDER";
List<Map<String, Object>> list = JDBCBaseDaoImpl.getJDBCTemplate()
.queryForList(sqlString);
try {
String leaveAloneId = leaveAloneService.save(bean);
//添加日志
logger.log(""+bean.getLeaveSubject()+"", "请假单", AuditLogLevel.level3, AuditLogResult.success, "新增", false);
int j = 0;
for (int i = 0; i < list.size(); i++) {
j = j + 1;
Map<String, Object> tempMap = list.get(i);
// 审批跟踪表
LeaveApproval leaveApproval = new LeaveApproval();
leaveApproval.setId(StringUtil.getGuid());
leaveApproval.setSortOrder(j);
leaveApproval.setApprovalUserId(tempMap.get("USER_ID")
.toString());
leaveApproval.setApprovalUserName(tempMap.get("USER_NAME")
.toString());
leaveApproval.setApprovalRoleId(tempMap.get("ROLE_ID_APPROVAL")
.toString());
leaveApproval.setApprovalRoleName(tempMap.get("ROLE_NAME")
.toString());
leaveApproval.setApprovalStatus("5");// 未审批；
leaveApproval.setApprovalOpinin("");
leaveApproval.setCreateTime(new Date());
leaveApproval.setCreator(userId);
leaveApproval.setLeaveAloneId(leaveAloneId);
if (j == 1) {
leaveApproval.setEffectyive("0");
//给审批人发送信息
// /////////////////发送消息start////////////////
Message messageApp = new Message();
messageApp.setTitle(bean.getUserName()+"的请假申请，请您审批！");
messageApp.setContent(bean.getUserName() + "请假，" + "【<a href='javascript:void(0)' onclick=\"viewApproval('"+ leaveAloneId+ "','"+ leaveApproval.getSortOrder()+ "'); return false\"><font style='color: #5374B1;'>"+ "</font>请您审批</a>】。");
messageApp.setMessageImportance(MessageImportance.Unimportant);
messageApp.setType(MessageType.Human);
messageApp.setReceviers(tempMap.get("USER_ID").toString().split(","));
Map<String, Object> mapApp = new HashMap<String, Object>();
mapApp.put("messageIsBack", MessageConstants.MESSAGE_IS_BACK_NOT);
messageApp.setOtherInfo(mapApp);
messageApp.setAttachments("".split(","));
messageApp.setReceiveRule("");
messageApp.setSender(bean.getUserId());
// 消息服务器发送
messageApp.send();
// /////////////////发送消息end ////////////////
} else {
leaveApproval.setEffectyive("1");
}
leaveAloneService.saveLeaveApproval(leaveApproval);
}
SyswarePageWrite.writeTOPage(response, "请假单添加成功！");
} catch (Exception e) {
SyswarePageWrite.writeTOPage(response, "请假单添加失败！");
}
各自审批人登录系统进行审批：
关键代码：
public void upddateLeaveApproval(Map<String, Object> map) { /** * map.put("radioVal", radioVal); map.put("approvalOpinin", * approvalOpinin); map.put("userId", userId); map.put("leaveId", * leaveId); */ String passVal = map.get("radioVal").toString(); String leaveId = map.get("leaveId").toString(); String userId = map.get("userId").toString(); String approvalOpinin = map.get("approvalOpinin").toString(); Integer maxNumber; Integer curentNumber; String leaveApprovalId = ""; if ("PASS".equals(passVal)) { // 查出最大的号 String sqlMaxNumber = " select max(sort_order) sort_order from SOFT617_LEAVE_APPROVAL s where s.leave_alone_id='" + leaveId + "'"; maxNumber = Integer.parseInt(JDBCBaseDaoImpl.getJDBCTemplate() .queryForList(sqlMaxNumber).get(0).get("SORT_ORDER") .toString()); // 查出当前审批人的号码 String sqlCurrentNumber = " select id, sort_order from SOFT617_LEAVE_APPROVAL s where s.leave_alone_id='" + leaveId + "' and s.approval_user_id='" + userId + "'"; curentNumber = Integer.parseInt(JDBCBaseDaoImpl.getJDBCTemplate() .queryForList(sqlCurrentNumber).get(0).get("SORT_ORDER") .toString()); leaveApprovalId = JDBCBaseDaoImpl.getJDBCTemplate() .queryForList(sqlCurrentNumber).get(0).get("ID").toString(); if (maxNumber == curentNumber) { // 如果当前审批人的号码序最大的号码一样说明是最后一个节点 // 更新请假表的状态 LeaveAlone leaveAlone = baseDao.getEntityById(LeaveAlone.class, leaveId); leaveAlone.setApprovalStatus("2");// 已审批 // 更新审批表信息 LeaveApproval leaveApproval = this .loadByLeaveApproval(leaveApprovalId); leaveApproval.setApprovalOpinin(approvalOpinin); leaveApproval.setApprovalStatus("1"); leaveApproval.setEffectyive("1"); this.updateLeaveApproval(leaveApproval); } else { // 不是最大号的情况下(更新当前审批信息，更新下一条审批审批信息为有效) String sqlString = "update SOFT617_LEAVE_APPROVAL s set s.approval_status = '1',\n" + "s.approval_opinin = '" + approvalOpinin + "',s.effectyive='1'\n" + "where s.id = (select id\n" + " from SOFT617_LEAVE_APPROVAL t\n" + " where t.leave_alone_id = '" + leaveId + "'\n" + " and t.APPROVAL_USER_ID = '" + userId + "')"; JDBCBaseDaoImpl.getJDBCTemplate().execute(sqlString); Integer nextNumberInteger = curentNumber + 1; String sqlString2 = "update SOFT617_LEAVE_APPROVAL s set s.effectyive= '0' where s.leave_alone_id='" + leaveId + "' and s.sort_order='" + nextNumberInteger + "'"; //向下一个人发送信息1、先查询下一个审批人是谁 String sqlSerchString="select t.APPROVAL_USER_ID, s.CREATOR from SOFT617_LEAVE_APPROVAL t ,SOFT617_LEAVE_ALONE s where t.leave_alone_id='"+leaveId+"' and t.sort_order='"+nextNumberInteger+"' and s.id=t.leave_alone_id " ; String nextuserId=JDBCBaseDaoImpl.getJDBCTemplate().queryForList(sqlSerchString).get(0).get("APPROVAL_USER_ID").toString(); String createUserId=JDBCBaseDaoImpl.getJDBCTemplate().queryForList(sqlSerchString).get(0).get("CREATOR").toString(); //根据用户id查询名字 String userNameSql="select t.USER_NAME from sys_user t where t.user_id='"+createUserId+"'" ; String user_name=JDBCBaseDaoImpl.getJDBCTemplate().queryForList(userNameSql).get(0).get("USER_NAME").toString(); //给审批人发送信息 // /////////////////发送消息start//////////////// Message messageApp = new Message(); messageApp.setTitle(user_name+"的请假申请，请您审批！"); // messageApp.setContent(user_name + "请假，" + "【'<a href='javascript:void(0)' onclick=\"viewApproval('" // + leaveId // + "'); return false\"><font style='color: #5374B1;'>" // + "</font>请您审批</a>】。"); messageApp.setContent(user_name+ "请假，" + "【<a href='javascript:void(0)' onclick=\"viewApproval('"+ leaveId+ "','"+ nextNumberInteger+ "'); return false\"><font style='color: #5374B1;'>"+ "</font>请您审批</a>】。"); messageApp.setMessageImportance(MessageImportance.Unimportant); messageApp.setType(MessageType.Human); messageApp.setReceviers(nextuserId.split(",")); Map<String, Object> mapApp = new HashMap<String, Object>(); mapApp.put("messageIsBack", MessageConstants.MESSAGE_IS_BACK_NOT); messageApp.setOtherInfo(mapApp); messageApp.setAttachments("".split(",")); messageApp.setReceiveRule(""); messageApp.setSender(nextuserId); // 消息服务器发送 messageApp.send(); // /////////////////发送消息end //////////////// JDBCBaseDaoImpl.getJDBCTemplate().execute(sqlString2); } } else { // 不同意的情况处理1、更新请假表的状态为驳回，并且更新审批表为无效标示 LeaveAlone leaveAlone= this.loadById(leaveId); leaveAlone.setApprovalStatus("3"); baseDao.update(leaveAlone); //更新审批表信息 String sqlString="update SOFT617_LEAVE_APPROVAL s set s.effectyive= '1', s.approval_status = '2',s.approval_opinin='"+approvalOpinin+"' where s.leave_alone_id='"+leaveId+"'and s.APPROVAL_USER_ID='"+userId+"'" ; JDBCBaseDaoImpl.getJDBCTemplate().execute(sqlString); } }
接着说，普通表格的上移，下移，这其实也是个算法：
以上，就是整个请假单流程的开发，说简单，也不简单，说难，也不算，其实，我们还开发了，其它流程的开发，比如；任务的审批流程等，其实，现在回过头来，再看，感觉这个还挺不错，现在忙里偷闲，记录下自己的成长点滴。
