getuserinfosql.java

实现统一的人员日志管理系统管理后台

package com.tb.log.util.tools;

public class GetUserInfoSql {
	String getSql = "";
	String sql = "";
	String getGrade = "";
	String getProName = "";
	String getSupper = "";
	String getDname = "";
	String getUserName = "";
	
	public String getPageSql(String supper_id,String dept_name,String pro_name,String grade,String user_name,String deptid){
		
		if(pro_name ==null || pro_name.equals("")){
			getProName = "";
		}else{
			getProName = " c.pro_name like '%"+pro_name+"%' and";
		}
		if(supper_id ==null || supper_id.equals("")){
			getSupper = "";
		}else{
			getSupper = " b.supper_id like '%"+supper_id+"%' and";
		}
		if(dept_name ==null || dept_name.equals("")){
			getDname = "";
		}else{
			getDname = " b.dept_name like '%"+dept_name+"%' and";
		}
		if(grade ==null || grade.equals("")){
			getGrade = "";
		}else{
			getGrade = " a.grade like '%"+grade+"%' and";
		}
		if(user_name ==null || user_name.equals("")){
			getUserName = "";
		}else{
			getUserName = " a.user_name like '%"+user_name+"%' ";
		}
		sql = getGrade+getProName+getSupper+getDname+getUserName;

		if(sql==null||sql.equals("")){
			getSql = "select * from (select row_.*, rownum rownum_ from ("+
				     " select  a.user_id,a.stu_id,a.user_name,a.deptid,a.grade ,a.user_state ,b.dept_name ,b.dept_state "+  
		              "from t_user a,t_department b where a.deptid=b.dept_id and a.deptid in("+deptid+") ) row_ where rownum <=?) where rownum_ >?";
		}else{
			//如果sql最后一个字符是","号将它去掉
			if(sql.substring(sql.length()-4,sql.length()).equals(" and")){
				sql = sql.substring(0,sql.length()-4);
			}
			//getSql = "select * from (select row_.*, rownum rownum_ from (select * from t_department where"+sql+") row_ where rownum <=?) where rownum_ >?";
			getSql = "select * from (select row_.*, rownum rownum_ from ("+
		     " select  a.user_id,a.stu_id,a.user_name,a.deptid,a.grade ,a.user_state ,b.dept_name ,b.dept_state "+  
             "from t_user a,t_department b "+
             "where a.deptid=b.dept_id and a.deptid in("+deptid+") and "+sql+
             ") row_ where rownum <=?) where rownum_ >?";
		}
		return getSql;
	}
	
	public String getCountSql(String supper_id,String dept_name,String pro_name,String grade,String user_name,String deptid){
		
		if(pro_name ==null || pro_name.equals("")){
			getProName = "";
		}else{
			getProName = " c.pro_name like '%"+pro_name+"%' and";
		}
		if(supper_id ==null || supper_id.equals("")){
			getSupper = "";
		}else{
			getSupper = " b.supper_id like '%"+supper_id+"%' and";
		}
		if(dept_name ==null || dept_name.equals("")){
			getDname = "";
		}else{
			getDname = " b.dept_name like '%"+dept_name+"%' and";
		}
		if(grade ==null || grade.equals("")){
			getGrade = "";
		}else{
			getGrade = " a.grade like '%"+grade+"%' and";
		}
		if(user_name ==null || user_name.equals("")){
			getUserName = "";
		}else{
			getUserName = " a.user_name like '%"+user_name+"%' ";
		}
		sql = getGrade+getProName+getSupper+getDname+getUserName;

		if(sql==null||sql.equals("")){
			getSql = "select count(*) from (select row_.*, rownum rownum_ from ("+
				     " select a.stu_id,a.user_name,a.deptid,a.grade,c.pro_name,c.pro_state,e.log_state "+  
		              "from t_user a,t_department b,t_project c,t_userproject d,t_worklog e "+
		              "where a.deptid=b.dept_id and a.user_id=d.user_id and d.pro_id=c.pro_id and a.user_id=e.user_id and e.pro_id=d.pro_id and a.deptid in("+deptid+") "+
		              ") row_ )";
		}else{
			//如果sql最后一个字符是","号将它去掉
			if(sql.substring(sql.length()-4,sql.length()).equals(" and")){
				sql = sql.substring(0,sql.length()-4);
			}
			//getSql = "select * from (select row_.*, rownum rownum_ from (select * from t_department where"+sql+") row_ where rownum <=?) where rownum_ >?";
			getSql = "select count(*) from (select row_.*, rownum rownum_ from ("+
		     " select a.stu_id,a.user_name,a.deptid,a.grade,c.pro_name,c.pro_state,e.log_state "+  
             "from t_user a,t_department b,t_project c,t_userproject d,t_worklog e "+
             "where a.deptid=b.dept_id and a.user_id=d.user_id and d.pro_id=c.pro_id and a.user_id=e.user_id and e.pro_id=d.pro_id and a.deptid in("+deptid+") and "+sql+
             ") row_ )";
		}
		return getSql;
	}
		public static void main(String[] args) {
			GetUserInfoSql getsql = new GetUserInfoSql();
			int dept_grade = 1;
			String grade="组长";
			String pro_name = "";
			String supper_id = "TB" ;
			String dept_name = null;
			String user_name="";
			//System.out.println(getsql.getCountSql(supper_id, dept_name, pro_name, grade, user_name));
		}
}