checklogsql.java

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

package com.tb.log.util.tools;

public class CheckLogSql {
	String getSql = "";
	String sql = "";
	
	String getEndDate = "";
	String getStartDate = "";
	String getProId = "";
	String getLogState = "";
	String getUserName = "";
	
	
	public String getPageSql(String start_date,String end_date,String pro_id,String log_state,String user_name,String deptid){
		
		if(start_date ==null || start_date.equals("")){
			getStartDate = "";
		}else{
			getStartDate = " a.log_date >='"+start_date+"' and";
		}
		if(end_date ==null || end_date.equals("")){
			getEndDate = "";
		}else{
			getEndDate = " a.log_date<='"+end_date+"' and";
		}
		if(pro_id ==null || pro_id.equals("")){
			getProId = "";
		}else{
			getProId = " a.pro_id="+pro_id +" and";
		}
		
		if(log_state ==null || log_state.equals("")){
			getLogState = "";
		}else{
			getLogState = " a.log_state='"+log_state+"' and";
		}
		if(user_name ==null || user_name.equals("")){
			getUserName = "";
		}else{
			getUserName = " c.user_name like '%"+user_name+"%'";
		}
		sql = getStartDate+getEndDate+getProId+getLogState+getUserName;

		if(sql==null||sql.equals("")){
			getSql = "select * from (select row_.*, rownum rownum_ from (select a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,a.log_state,b.pro_name ,b.pro_state,c.user_name from t_worklog a,t_project b ,t_user c where a.pro_id=b.pro_id and a.user_id=c.user_id and c.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 a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,a.log_state,b.pro_name ,b.pro_state,c.user_name from t_worklog a,t_project b ,t_user c where a.pro_id=b.pro_id and a.user_id=c.user_id and c.deptid in("+deptid+") and "+sql+") row_ where rownum <=?) where rownum_ >?";
		}
		return getSql;
	}
	
	public String getCountSql(String start_date,String end_date,String pro_id,String log_state,String user_name,String deptid){
		
		if(start_date ==null || start_date.equals("")){
			getStartDate = "";
		}else{
			getStartDate = " a.log_date >='"+start_date+"' and";
		}
		if(end_date ==null || end_date.equals("")){
			getEndDate = "";
		}else{
			getEndDate = " a.log_date<='"+end_date+"' and";
		}
		if(pro_id ==null || pro_id.equals("")){
			getProId = "";
		}else{
			getProId = " a.pro_id="+pro_id +" and";
		}
		
		if(log_state ==null || log_state.equals("")){
			getLogState = "";
		}else{
			getLogState = " a.log_state like '%"+log_state+"%' and";
		}
		if(user_name ==null || user_name.equals("")){
			getUserName = "";
		}else{
			getUserName = " c.user_name='"+user_name+"'";
		}
		sql = getStartDate+getEndDate+getProId+getLogState+getUserName;

		if(sql==null||sql.equals("")){
			sql = "select count(*) from (select row_.*, rownum rownum_ from " +
			"(select a.log_id,a.log_date,a.log_desc,a.log_plan,a.log_state,b.pro_name ,b.pro_state " +
			"from t_worklog a,t_project b,t_user c  where a.pro_id=b.pro_id and a.user_id=c.user_id and c.deptid in("+deptid+")) row_ )"; 
		}else{
			sql = "select count(*) from (select a.log_id,a.log_date,a.log_desc,a.log_plan,a.user_id,a.log_state,b.pro_name ,b.pro_state from t_worklog a,t_project b ,t_user c where a.pro_id=b.pro_id and a.user_id=c.user_id and c.deptid in("+deptid+") and"+sql;
			//如果sql最后一个字符是","号将它去掉
			if(sql.substring(sql.length()-4,sql.length()).equals(" and")){
				sql = sql.substring(0,sql.length()-4);
				
			}
			sql = sql +")";
		}
		return sql;
	}
		public static void main(String[] args) {
			
			CheckLogSql getsql = new CheckLogSql();
			//String  pro_category = "";
			String start_date = "";
			String end_date = "";
			String pro_state = "" ;
			String pro_id = "";
			String log_state ="";
			String user_name = "朱德金";
		//System.out.println(getsql.getPageSql(start_date, end_date, pro_id, log_state, user_name));
		//System.out.println(getsql.getCountSql(start_date, end_date, pro_id, log_state, user_name));
		}
}