taffair.java

基于J2EE的办公自动化系统。实现流程定义流程办理等。运用了hibernate+struts+spring框架综合运用的系统。

		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean flag = false;

		sql = "select * from transtype where rtid  =" + rtid;
		session = this.sf.openSession();
		conn = session.connection();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				File file = new File(dir + "/" + rs.getString("txmlpath"));
				file.delete();
				flag = true;
			}
		} catch (SQLException e) {
			flag = false;
			e.printStackTrace();

		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}			
		}
		return flag;
	}

	public List init(String uno) {
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		session = this.sf.openSession();
		conn = session.connection();
		
		List list = new ArrayList();
		try {
			sql = "select a.* from transtype a ,tuserrole b ,ttyperole c where b.uno =  " + uno
					+ " and b.rid = c.rid and c.rtid = a.rtid";

			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map map = new HashMap();
				map.put("rtid", rs.getString("rtid"));
				map.put("tname", rs.getString("tname"));
				map.put("txmlpath", rs.getString("txmlpath"));
				map.put("tstep", rs.getString("tstep"));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
			list = null;
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}	
		}
		return list;
	}

	
	
	public void addtyperole(long rtid, String[] rid) {		

		String[] sqls = new String[rid.length];
		for(int i=0 ;i<rid.length;i++){
			sqls[i] = "insert into TTYPEROLE (TR_ID, RTID, RID)";
	        sqls[i] =sqls[i] +" values(seq_t_typerole.nextval,'"+String.valueOf(rtid)+"','"+rid[i]+"')";

		}	
		boolean flag = this.executeUpdates(sqls);	
		
		if(flag){
			
		}
	}
	
	
	public boolean executeUpdates(String[] sqls){
		Connection conn = null;		
		Statement st = null;
		
		session = sf.openSession();		
		conn =session.connection();
		try {
			conn.setAutoCommit(false);//设置不自动提交
			st = conn.createStatement();
			for(int i=0;i<sqls.length;i++){
				st.addBatch(sqls[i]);
			}			
			int[] re=st.executeBatch();
			conn.commit();//提交事务
			if(re.length>0){
				return true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();//事务回顾
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			try {
				st.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		
		return false;
	}

	public List getroles(String roleid) {
		List list = new ArrayList();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		String sql =null;
		int count1 = 0 ;
		int count2 = 0 ;
		String[] roleids = roleid.split(",");
		session = sf.openSession();
		conn= session.connection();
		try {
			
			st = conn.createStatement();
			for (int i = 0; i < roleids.length; i++) {
				sql = "select uno from tuserrole t where t.rid =" + roleids[i];
				rs = st.executeQuery(sql);
				String userids = "";
				String username = "";
				Map userMap = new HashMap();
				while (rs.next()) {
					if (userids.equals("")) {
						userids = rs.getString("uno");
					} else {
						userids = userids + "," + rs.getString("uno");
					}
					count1 += 1;
				}
				if (!userids.equals("")) {
					
					String[] users = userids.split(",");
					count2 = users.length-1;
					for (int j = 0; j < users.length; j++) {
						sql = "select t.uno,t.utruename from tuser t where t.uno = "+ users[j] + " and t.uislocked=0";
						rs = st.executeQuery(sql);
						while (rs.next()) {
							if (username.equals("")) {
								username = rs.getString("utruename");
							} else {
								username = username + ","+ rs.getString("utruename");
							}													
						}						
					}
					if(count1>count2){
						userids ="";
						for(int m = 0;m<count2;m++){
							if (userids.equals("")) {
								userids = users[m];
							} else {
								userids = userids + "," +users[m];
							}
						}
						userMap.put("userid", userids);
						userMap.put("username", username);
					}else{
						userMap.put("userid", userids);
						userMap.put("username", username);
					}
				}				
				list.add(userMap);
			}
		
		} catch (Exception e) {
			list = null;
			e.printStackTrace();
			// TODO: handle exception
		}finally{
			try {
				rs.close();
				st.close();
				conn.close();
				
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}		
		return list;		
	}

	public boolean AffairReq(AffairReqForm affairReqForm,HttpServletRequest request) {
		
		Connection conn = null;
		Statement st = null;
		Statement st2 = null;
		boolean flag  = true;
		Transreq affaireq = new Transreq();
		Transstep affairstep = new Transstep();			
		try {
			BeanUtils.copyProperties(affaireq, affairReqForm);
		} catch (IllegalAccessException e2) {
			// TODO 自动生成 catch 块
			e2.printStackTrace();
		} catch (InvocationTargetException e2) {
			// TODO 自动生成 catch 块
			e2.printStackTrace();
		}			
		String ReqSql = null;
		String StepSql = null;
		long ReqID = getReqID(); 
		ReqSql = "insert into TRANSREQ (RQID, RTITLE, UNO, TNO, RGRADE, RSTARTTIME, RENDTIME, RNEXTTIME, RLASTTIME, RDETAIL, RSTATUS, RMEMO)";
		ReqSql = ReqSql + "values ("+ReqID+","+"'"+affaireq.getRtitle()+"','";
		ReqSql = ReqSql +affaireq.getUno()+"','";
		ReqSql = ReqSql +affaireq.getTno()+"','";
		ReqSql = ReqSql +affaireq.getRgrade()+"','";
		ReqSql = ReqSql +affaireq.getRstarttime()+"','";
		ReqSql = ReqSql +affaireq.getRendtime()+"','";
		ReqSql = ReqSql +affaireq.getRnexttime()+"','";
		ReqSql = ReqSql +affaireq.getRlasttime()+"','";
		ReqSql = ReqSql +affaireq.getRdetail()+"','";
		ReqSql = ReqSql +affaireq.getRstatus()+"','";
		ReqSql = ReqSql +affaireq.getRmemo()+"')";

		affairstep.setRqid(ReqID);
		affairstep.setScurrentid(1);
		affairstep.setUno(request.getParameter("userid"));
		affairstep.setSstatus("0");//当前状态
		affairstep.setSresult("0");
		affairstep.setSprestep(0);
		StepSql ="insert into TRANSSTEP (SID, RQID, SCURRENTID, UNO, SSTATUS, SPRESTEP, SNEXTUNO, SCURRENTTIME, SNEXTTIME, SRESULT, SDETAIL, SNAME, SPATH)";
		StepSql = StepSql+ "values ( SEQ_T_RANSSTEP.nextval,";
		StepSql = StepSql +ReqID+",";
		StepSql = StepSql +affairstep.getScurrentid()+",'";
		StepSql = StepSql +affairstep.getUno()+"','";
		StepSql = StepSql +affairstep.getSstatus()+"',";
		StepSql = StepSql +affairstep.getSprestep()+",'";
		StepSql = StepSql +affairstep.getSnextuno()+"','";
		StepSql = StepSql +affairstep.getScurrenttime()+"','";
		StepSql = StepSql +affairstep.getSnexttime()+"','";
		StepSql = StepSql +affairstep.getSresult()+"','";		
		StepSql = StepSql +affairstep.getSdetail()+"','";
		StepSql = StepSql +affairstep.getSname()+"','";
		StepSql = StepSql +affairstep.getSpath()+"')";		

		session = sf.openSession();		
		conn =session.connection();
			try {
				conn.setAutoCommit(false);//设置不自动提交
				st = conn.createStatement();
				st2 = conn.createStatement();
				st.execute(ReqSql);
				st2.execute(StepSql);
				conn.commit();//提交事务	
			} catch (SQLException e) {
				try {					
					flag = false;
					conn.rollback();//事务回顾
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				e.printStackTrace();
			}finally{
				try {
					st.close();
					st2.close();
					conn.close();
				} catch (SQLException e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}
			}			
			return flag;	
	}
	public boolean addaffairstep(Transstep ransstep) {			
		try {
			session = sf.openSession();	
			tx = session.beginTransaction();
			session.save(ransstep);			
			tx.commit();
			session.flush();
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			tx.rollback();
		}finally{
			session.close();
			sf.close();
		}
		return false;
	}
	
	private long getReqID(){
		long ReqID = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;		
		session = sf.openSession();		
		conn =session.connection();
		String Sql = null;
		try {
			Sql = "select SEQ_T_RANSREQ.NEXTVAL as ReqID FROM  dual";
			ps = conn.prepareStatement(Sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				ReqID = rs.getInt("ReqID");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
			sf.close();
		}		
		return ReqID;
	}

	public List reqList(int currentPage, int count, Transreq reqbean,HttpServletRequest request) {
		StringBuffer sql = new StringBuffer();
		StringBuffer centent = new StringBuffer();
		String rgrade = request.getParameter("rgrade");
		
		if(reqbean.getReqUsername()!=null&&!reqbean.getReqUsername().trim().equals("")){
			centent.append(" and usr.utruename like '%").append(reqbean.getReqUsername().trim()).append("%'");
		}
		if(rgrade !=null && !rgrade.trim().equals("")){
			centent.append(" and req.rgrade =").append(rgrade.trim()).append("");
		}
		if(reqbean.getRstarttime()!=null &&!reqbean.getRstarttime().trim().equals("")){
			centent.append(" and req.rlasttime between'").append(reqbean.getRstarttime().trim()).append("'");
			centent.append(" and '").append(reqbean.getRendtime().trim()).append("'");
		}		
		sql.append("SELECT * FROM(")
		.append("select typ.tname,req.*,usr.utruename,ROWNUM AS num from transreq req ")
		.append("inner join tuser usr on req.uno =  usr.uno ")
		.append("inner join transtype typ on typ.rtid = req.tno where 1 = 1 and req.rstatus = '0' ")
		.append(centent)
		.append(")t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.rqid");
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		session = sf.openSession();
		conn = session.connection();
		List list = null;
		Transreq reqbeans = null;
		try {
			int i = 0 ;
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());			
			list = new ArrayList();
			while (rs.next()) {	
				i = i +1;
				reqbeans = new Transreq();
				reqbeans.setRqid(rs.getInt("RQID"));
				reqbeans.setRtitle(rs.getString("RTITLE"));
				reqbeans.setTname(rs.getString("TNAME"));
				reqbeans.setReqUsername(rs.getString("utruename"));
				reqbeans.setRgrade(rs.getString("rgrade"));
				reqbeans.setRlasttime(rs.getString("RLASTTIME"));				
				list.add(reqbeans);
			}
			if (i == 0 ){
				list = null;
			}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			session.close();		
			sf.close();
			
		}

		return list;
	}



	public List getaudit(HttpServletRequest request) {
		List list = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		session = sf.openSession();
		conn = session.connection();
		Map affair = null;
		Map req = null;
		try {
			stmt = conn.createStatement();
			sql = "select a.rgrade ,a.tno ,a.rdetail ,a.rtitle ,b.tname,b.tstep ,c.sprestep ,a.rlasttime,c.sid";
			sql = sql +" from transreq a inner join transtype b on a.tno = b.rtid";
			sql = sql +" inner join transstep c on a.rqid = c.rqid where c.sstatus = '0' and a.rqid = ";

			String rqid = "0";
			if (request.getParameter("rqids")!=null && !request.getParameter("rqids").equals("")){
				rqid = request.getParameter("rqids");
			}		
			sql = sql +rqid ;
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				affair = new HashMap();
				affair.put("sid",rs.getString("sid"));
				affair.put("rdetail", rs.getString("rdetail"));
				affair.put("rtitle", rs.getString("rtitle"));
				affair.put("tstep", rs.getString("tstep"));
				affair.put("rgrade", rs.getString("rgrade"));
				affair.put("tno", rs.getString("tno"));
				affair.put("rqid", rqid);
				affair.put("tname", rs.getString("tname"));
				affair.put("rlasttime", rs.getString("rlasttime"));
				affair.put("sprestep", rs.getString("sprestep"));				
			}