rolemgtdao.java

j2ee源码

/*
 * Created on 2005/11/2
 */
package com.leeman.wkexs.master.rolemgt.dao;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.leeman.common.data.Dao;
import com.leeman.common.data.ResultSetReader;

import com.leeman.common.util.Formatter;
import com.leeman.wkexs.master.rolemgt.entity.*;
/**
 * @author Dennis
 */
public class RoleMgtDao extends Dao{

	public int getListCount(RoleHdrSO roleHdrSO) throws Exception
	{
		int total = 0;
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(roleHdrSO, true));
		if(rs.next()){				
			total = rs.getInt("total");
		}
		rs.close();
		dbConn.closeQuery();
		return total;		
	}
	
	public String getListQuery(RoleHdrSO roleHdrSO, boolean isCountSQL)
	{
		StringBuffer sql = new StringBuffer();
		
		sql.append("select ");
		if (isCountSQL){
			sql.append(" count(*) as total");
		}
		else{
			sql.append(" *");
		}
		sql.append(" from role_hdr");
		sql.append(" where company_id = " + Formatter.toSqlString(roleHdrSO.getCompany_id()));
		if (!Formatter.replaceNullString(roleHdrSO.getRole_id()).equals("")){
			sql.append(" and role_id like " + Formatter.toSqlString(roleHdrSO.getRole_id() + "%"));
		}
		if (!Formatter.replaceNullString(roleHdrSO.getRole_name()).equals("")){
			sql.append(" and upper(role_name) like " + Formatter.toSqlString(roleHdrSO.getRole_name().toUpperCase() + "%"));
		}
		sql.append(" order by role_id");
		return sql.toString();
	}
	
	public ArrayList getList(RoleHdrSO roleHdrSO, int startIndex, int maxRecords) throws Exception
	{
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(roleHdrSO, false));
		ArrayList list = new ArrayList();
			
		ResultSetReader reader = new ResultSetReader(rs, startIndex, maxRecords); 
		while (reader.next())
		{
			RoleHdrVO vo = new RoleHdrVO();
			vo.setCompany_id(rs.getString("company_id"));
			vo.setRole_id(rs.getString("role_id"));
			vo.setRole_name(rs.getString("role_name"));
			vo.setCreate_dttm(rs.getDate("create_dttm"));
			vo.setCreate_by(rs.getString("create_by"));
			vo.setLast_modify_dttm(rs.getDate("last_modify_dttm"));
			vo.setLast_modify_by(rs.getString("last_modify_by"));
			
			list.add(vo);
		}
		rs.close();
		rs = null;
		reader = null;		
		return list;
	}
	
	/**
	 * Get a all privileges for a given role in the system, including privilaged and avaliable for select 
	 * @param hdrVO
	 * @param langId
	 * @return ArrayList of RoleDtlVO 
	 * @throws Exception
	 */
	public ArrayList getRolePrivList(RoleHdrVO hdrVO, String langId) throws Exception
	{
		initDbConn();
		StringBuffer sql = new StringBuffer();
		
		sql.append("select p.*, r.role_id, r.active from privilege p");
		sql.append(" left join role_dtl r");
		sql.append(" on p.company_id = r.company_id");
		sql.append(" and p.program_id = r.program_id");
		sql.append(" and p.priv_id = r.priv_id");
		sql.append(" and r.role_id = " + Formatter.toSqlString(hdrVO.getRole_id()));
		sql.append(" where p.company_id = " + Formatter.toSqlString(hdrVO.getCompany_id()));
		sql.append(" and p.lang_id = " + Formatter.toSqlString(langId));
		sql.append(" order by p.module, p.program_id, p.priv_id");
		
		ArrayList list = new ArrayList();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		while(rs.next())
		{
			RoleDtlVO dtlVO = new RoleDtlVO();
			dtlVO.setCompany_id(rs.getString("company_id"));
			dtlVO.setModule(rs.getString("module"));
			dtlVO.setRole_id(rs.getString("role_id"));
			dtlVO.setProgram_id(rs.getString("program_id"));
			dtlVO.setProgram_name(rs.getString("program_name"));
			dtlVO.setPriv_id(rs.getString("priv_id"));
			dtlVO.setPriv_desc(rs.getString("priv_desc"));
			dtlVO.setActive(rs.getString("active"));
			list.add(dtlVO);
		}
		rs.close();
		rs = null;
		return list;
	}
	
	/**
	 * Get privilege list for a program for a given Role ID
	 * @param hdrVO
	 * @param programId
	 * @return ArrayList of authorized priv ID
	 * @throws Exception
	 */
	public ArrayList getRoleProgramPrivList(RoleHdrVO hdrVO, String programId) throws Exception
	{
		initDbConn();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct priv_id from role_dtl");
		sql.append(" where company_id = " + Formatter.toSqlString(hdrVO.getCompany_id()));
		sql.append(" and role_id = " + Formatter.toSqlString(hdrVO.getRole_id()));
		sql.append(" and program_id = " + Formatter.toSqlString(programId));
		sql.append(" and active = " + Formatter.toSqlString(Formatter.boolToChar(true)));
		
		ArrayList list = new ArrayList();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		while(rs.next())
		{
			list.add(rs.getString("priv_id"));
		}
		rs.close();
		rs = null;
		return list;
	}
	
	// check if role id exists in user master
	public long getRoleIdCount(RoleHdrVO hdrVO, String table) throws Exception
	{
		initDbConn();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) as total from " + table);
		sql.append(" where company_id = " + Formatter.toSqlString(hdrVO.getCompany_id()));
		sql.append(" and upper(role_id) = " + Formatter.toSqlString(hdrVO.getRole_id().toUpperCase()));
				
		ResultSet rs = dbConn.executeQuery(sql.toString());
		long total = 0;
		if (rs.next()) {				
			total = rs.getLong("total");
		}
		rs.close();
		rs = null;
		dbConn.closeQuery();
		return total;
	}
	
	/**
	 * Get a all view privileges for a given role in the system 
	 * @param hdrVO
	 * @param langId
	 * @return ArrayList of RoleDtlVO 
	 * @throws Exception
	 */
	public ArrayList getRoleViewPrivList(RoleHdrVO hdrVO, String langId) throws Exception
	{
		initDbConn();
		StringBuffer sql = new StringBuffer();
		
		sql.append(" select p.* , r.active ");
		sql.append(" from ");
		sql.append(" ( select unique company_id, module, program_id ");
		sql.append("   from privilege ");
		sql.append("   where company_id = " + Formatter.toSqlString(hdrVO.getCompany_id()) );
		sql.append("   and lang_id = " + Formatter.toSqlString(langId) );
		sql.append(" ) p " ) ;
		sql.append(" left join " );
		sql.append(" ( select unique company_id, active, program_id ");	
		sql.append("   from role_dtl " );
		sql.append("   where company_id= " + Formatter.toSqlString(hdrVO.getCompany_id()) );
		sql.append("   and role_id = " + Formatter.toSqlString(hdrVO.getRole_id()) );
		sql.append("   and active = 'Y' " );
		sql.append(" ) r " );
		sql.append(" on r.program_id = p.program_id " );			
		sql.append(" order by p.module, p.program_id " );
	
		ArrayList list = new ArrayList();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		while(rs.next())
		{
			RoleDtlVO dtlVO = new RoleDtlVO();
			dtlVO.setCompany_id(rs.getString("company_id"));
			dtlVO.setModule(rs.getString("module"));
			dtlVO.setProgram_id(rs.getString("program_id"));
			dtlVO.setActive(rs.getString("active"));
			list.add(dtlVO);
		}
		rs.close();
		rs = null;
		return list;
	}
		
}