/**
*
*
*
*
*
* @author Niladri Roy
* @version 1.0, Jul 21, 2004
* @since EEMS architecture refresh
*/
package com.eforce.baby.auth.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import com.eforce.baby.auth.vo.RolePrivilegeVO;
import com.eforce.baby.auth.vo.RoleVO;
import com.eforce.baby.common.dao.BaseDAO;
import com.eforce.baby.common.dao.DAOException;
import com.eforce.baby.common.dao.QueryManager;
import com.eforce.baby.common.vo.PageInfoVO;
import com.eforce.baby.common.vo.SortInfoVO;
import com.eforce.baby.reports.vo.ReportResultsVO;
import com.eforce.baby.utils.EEMSUtil;
import com.eforce.baby.utils.IConstants;
import com.eforce.baby.utils.IErrorMessageKeys;
/**
* @author nroy
*
* Created on Jul 21, 2004
*
*/
public class RoleDAO extends BaseDAO
{
private Logger log = (Logger)Logger.getInstance(this.getClass().getName());
/**
* @param dsName
* @param dbType
* @param roleVO
*/
public void createRole(String dsName, String dbType, RoleVO roleVO) throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt = null;
ResultSet rset = null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Checking for duplicate role */
String sql;
sql = qManager.getDuplicateRoleQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getRoleName() );
rset = null;
rset = pStmt.executeQuery();
if( rset.next() ){
if(rset.getInt(1)>0){
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_ERROR_DUPLICATE_ROLE);
throw daox;
}
}
sql = qManager.getFetchNewRoleIdQuery(dbType);
log.debug(sql);
roleVO.setId(this.getCurrentSequenceValue(con,sql));
sql = qManager.getFetchNewRoleQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getId() );
pStmt.setString( 2, roleVO.getRoleName());
pStmt.setString( 3, roleVO.getRoleDesc());
log.debug("ID="+roleVO.getId());
pStmt.executeUpdate();
this.createSearchContent(con, dbType, roleVO, "TBL_ROLE", "ROLE_ID" ) ;
log.debug("Role Info incerted ");
String[] groupIds = EEMSUtil.convertToArray(roleVO.getGroupNames(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
sql = qManager.getFetchRoleGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<groupIds.length;i++)
{
if(groupIds[i]!= null)
{
log.debug("GROUPSID " + groupIds[i]);
log.debug("role id "+roleVO.getId());
pStmt.setString( 1, groupIds[i].trim() );
pStmt.setString( 2, roleVO.getId() );
pStmt.executeUpdate();
log.debug("ROLE-GROUP added" + i);
}
}
}
/* Privilege */
String[] privilegeIds = EEMSUtil.convertToArray(roleVO.getPrivilegeNames(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
sql = qManager.getRolePrivilegeQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<privilegeIds.length;i++)
{
if(privilegeIds[i]!= null)
{
log.debug("privilegeID " + privilegeIds[i]);
log.debug("role id "+roleVO.getId());
pStmt.setString( 1, roleVO.getId() );
pStmt.setString( 2, privilegeIds[i].trim() );
pStmt.executeUpdate();
log.debug("ROLE-privilege added" + i);
}
}
}
/* Privilege */
log.debug("inserted...and returning");
con.commit();
}
catch(SQLException ex)
{ ex.printStackTrace();
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_DATABASE_ERROR);
throw daox;
}
finally
{
try
{
super.cleanUp(rset, pStmt);
this.releaseConnection(con);
}
catch (DAOException e)
{
// TODO Auto-generated catch block
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* @param con
* @param query
* @return
* @throws SQLException
*/
public String getCurrentSequenceValue( Connection con, String query )
throws SQLException
{
String currentValue = "-1";
PreparedStatement pStmt = con.prepareStatement( query );
ResultSet rs = null;
try {
rs = pStmt.executeQuery();
if( rs.next() )
{
currentValue = rs.getString(1);
}
}
finally
{
super.cleanUp(rs, pStmt);
}
return currentValue;
}
/**
* @param dsName
* @param dbType
* @param page
* @param sort
* @param profileId
* @return
*/
public ReportResultsVO findRoleByName(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId ) throws DAOException
{
String sql = QueryManager.getInstance().getFindRoleByName(dbType);
log.debug("findRoleByName-sql="+sql);
return super.findReportResult(dsName, dbType,page,sort, sql);
}
/**
* @param dsName
* @param dbType
* @param roleID
* @return
*/
public RoleVO findRole(String dsName, String dbType, String roleID) throws DAOException
{
Connection con = super.createConnection(dsName);
RoleVO roleVO = new RoleVO();
roleVO.setId(roleID);
PreparedStatement pstmt=null;
ResultSet rset = null;
log.debug("start of DB Fetch");
try {
String sql = QueryManager.getInstance().getFindRole(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
pstmt.setString(1, roleVO.getId());
rset = pstmt.executeQuery();
String groups = "";
String sep = ", ";
while(rset.next())
{
if(groups.equals(""))
{
roleVO.setRoleName(rset.getString(1));
roleVO.setRoleDesc(rset.getString(2));
}
if(!rset.getString(3).equals("NA"))
{
groups += rset.getString(3)+sep;
}
}
if(groups.endsWith(sep))
{
groups=groups.substring(0,(groups.length()-sep.length()));
}
roleVO.setGroupNames(groups);
log.debug("groups="+groups);
/* Privilege */
sql = QueryManager.getInstance().getFindRoleSelectedPrivilegeQuery(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
pstmt.setString(1, roleVO.getId());
rset = pstmt.executeQuery();
String privileges = "";
sep = ", ";
while(rset.next())
{
if(!rset.getString(2).equals("NA"))
{
privileges += rset.getString(2)+sep;
}
}
if(privileges.endsWith(sep))
{
privileges=privileges.substring(0,(privileges.length()-sep.length()));
}
roleVO.setPrivilegeNames(privileges);
/* Privilege */
log.debug("Privilege="+privileges);
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset,pstmt);
this.releaseConnection(con);
}
log.debug("End of DB Fetch" + roleVO);
return roleVO;
}
/**
* @param dsName
* @param dbType
* @param roleVO
*/
public void updateRole(String dsName, String dbType, RoleVO roleVO) throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt = null;
ResultSet rset = null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Check if there is any ROLE with same name */
String sql;
sql = qManager.getDuplicateRoleUpdateQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getId() );
pStmt.setString( 2, roleVO.getRoleName() );
log.debug("SQL="+sql);
log.debug("ID="+roleVO.getId()+" || "+roleVO.getRoleName());
rset = pStmt.executeQuery();
if( rset.next() ){
if(rset.getInt(1)>0){
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_ERROR_DUPLICATE_ROLE);
throw daox;
}
}
sql = qManager.getUpdateRole(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getRoleName() );
pStmt.setString( 2, roleVO.getRoleDesc() );
pStmt.setString( 3, roleVO.getId());
pStmt.executeUpdate();
this.createSearchContent(con, dbType, roleVO, "TBL_ROLE", "ROLE_ID" ) ;
//first delete old groups
sql = qManager.getDeleteRoleGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getId());
pStmt.executeUpdate();
String[] groupIds = EEMSUtil.convertToArray(roleVO.getGroupNames(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
sql = qManager.getFetchRoleGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<groupIds.length;i++)
{
if(groupIds[i]!= null)
{
log.debug("GROUPSID " + groupIds[i]);
log.debug("role id "+roleVO.getId());
pStmt.setString( 1, groupIds[i].trim() );
pStmt.setString( 2, roleVO.getId() );
pStmt.executeUpdate();
log.debug("ROLE-GROUP added" + i);
}
}
}
//first delete old privilege
sql = qManager.getDelRolePrivilegeQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, roleVO.getId());
pStmt.executeUpdate();
/* Privilege */
String[] privilegeIds = EEMSUtil.convertToArray(roleVO.getPrivilegeNames(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
sql = qManager.getRolePrivilegeQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<privilegeIds.length;i++)
{
if(privilegeIds[i]!= null)
{
log.debug("privilegeID " + privilegeIds[i]);
log.debug("role id "+roleVO.getId());
pStmt.setString( 1, roleVO.getId() );
pStmt.setString( 2, privilegeIds[i].trim() );
pStmt.executeUpdate();
log.debug("ROLE-privilege added" + i);
}
}
}
/* Privilege */
log.debug("inserted...and updated");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_DATABASE_ERROR);
throw daox;
}
finally
{
try
{
super.cleanUp(rset, pStmt);
this.releaseConnection(con);
}
catch (DAOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* @param dsName
* @param dbType
* @param id
*/
public void delete(String dsName, String dbType, String id) throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getDelGroupRoleQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
sql = qManager.getDelRolePrivilegeQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
sql = qManager.getDeleteRoleQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
log.debug("Role Deleted ");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_DATABASE_ERROR);
throw daox;
}
finally
{
try
{
super.cleanUp(null, pStmt);
this.releaseConnection(con);
}
catch (DAOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called to get role selected privilege
* @param dsName
* @param dbType
* @param roleId
* @return ArrayList
* @throws DAOException
* @throws SQLException
*/
public ArrayList getRoleSelectedPrivileges(String dsName, String dbType, String roleId)
throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
RolePrivilegeVO privilege = null;
ArrayList privilegeList = new ArrayList();
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
String sql = QueryManager.getInstance().getFindRoleSelectedPrivilegeQuery(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, roleId );
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
privilege = new RolePrivilegeVO();
privilege.setId(rset.getString(1));
privilege.setPrivilegeName(rset.getString(2));
privilegeList.add(privilege);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
this.releaseConnection(con);
}
return privilegeList;
}
/**
* Called to get all privileges
* @param dsName
* @param dbType
* @return ArrayList
* @throws DAOException
* @throws SQLException
*/
public ArrayList getAllPrivileges(String dsName, String dbType)
throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
RolePrivilegeVO privilege = null;
ArrayList privilegeList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getFindAllPrivilegeQuery(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
privilege = new RolePrivilegeVO();
privilege.setId(rset.getString(1));
privilege.setPrivilegeName(rset.getString(2));
privilegeList.add(privilege);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
this.releaseConnection(con);
}
return privilegeList;
}
}