/**
*
* @author Niadri Roy
* @version 1.0, 07/08/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.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import org.apache.log4j.Logger;
import com.eforce.baby.EEMSException;
import com.eforce.baby.auth.vo.PrivilegeVO;
import com.eforce.baby.auth.vo.SessionUserVO;
import com.eforce.baby.auth.vo.UserPrivilegeList;
import com.eforce.baby.auth.vo.UserVO;
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.EEMSCryptography;
import com.eforce.baby.utils.EEMSUtil;
import com.eforce.baby.utils.IConstants;
import com.eforce.baby.utils.IErrorMessageKeys;
/**
*
* @author Niadri Roy
* @version 1.0, 07/08/2004
* @since EEMS architecture refresh
*/
public class UserDAO extends BaseDAO
{
private Logger log = (Logger)Logger.getInstance(this.getClass().getName());
/**
* @param dsName
* @param dbType
* @param userVO
*/
public void createUser(String dsName, String dbType, UserVO userVO) throws EEMSException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getFetchNewUserIdQuery(dbType);
log.debug(sql);
userVO.setId(this.getCurrentSequenceValue(con,sql));
if(!userExists(con,dbType,userVO))
{
sql = qManager.getFetchNewUserQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, userVO.getId() );
pStmt.setString( 2, userVO.getUserName() );
log.debug("Before Encryption: " + userVO.getPassword());
String password = EEMSCryptography.getInstance().encrypt(userVO.getPassword().trim());
log.debug("After Encryption: " + password);
pStmt.setString( 3, password);
pStmt.executeUpdate();
this.createSearchContent(con,dbType,userVO,"TBL_USER","USER_ID");
/* Group list information */
String[] groupIds = EEMSUtil.convertToArray(userVO.getGroupNameList(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
sql = qManager.getFindCreateUserGroupQuery(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("user id "+userVO.getId());
pStmt.setString( 1, userVO.getId() );
pStmt.setString( 2, groupIds[i].trim() );
pStmt.executeUpdate();
log.debug("ROLE-GROUP added" + i);
}
}
}
log.debug("inserted...and returning");
}
else
{
log.debug("Duplicate User ID found ....aborting...") ;
DAOException daox = new DAOException ("Duplicate User");
daox.setMessageKey(IErrorMessageKeys.KEY_DUPLICATE_USER);
throw daox;
}
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
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
{
if( rs!= null )
{
rs.close();
}
pStmt.close();
}
return currentValue;
}
/**
* @param dsName
* @param dbType
* @return
*/
public ReportResultsVO findUserByName(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId) throws DAOException
{
String sql = QueryManager.getInstance().getFindUserByNameQuery(dbType);
return super.findReportResult(dsName, dbType,page,sort, sql);
// (dsName, dbType, page, sort, userId, sql ,profileRepId);
}
/**
* @param dsName
* @param dbType
* @return
*/
public ReportResultsVO findUserByGroup(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId ) throws DAOException
{
String sql = QueryManager.getInstance().getFindUserByGroupQuery(dbType);
return super.findReportResult(dsName, dbType,page,sort, sql);
}
/**
* @param dsName
* @param dbType
* @param reportID
* @return
*/
public UserVO findUser(String dsName, String dbType, String userID) throws DAOException, SQLException
{
// TODO Auto-generated method stub
Connection con = super.createConnection(dsName);
UserVO userVO = new UserVO();
userVO.setId(userID);
log.debug("start of DB Fetch");
ResultSet rset=null;
PreparedStatement pstmt=null;
try {
String sql = QueryManager.getInstance().getFindUserInfoQuery(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
pstmt.setString(1, userVO.getId());
rset = pstmt.executeQuery();
String groups = "";
String sep = ", ";
while(rset.next())
{
if(groups.equals(""))
{
userVO.setUserName(rset.getString(1));
log.debug("user="+rset.getString(1));
//userVO.setPassword(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()));
}
userVO.setGroupNameList(groups);
log.debug("groups="+groups);
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
if( rset!= null )
{
rset.close();
}
pstmt.close();
this.releaseConnection(con);
}
log.debug("End of DB Fetch");
return userVO;
}
/**
* <p> Authenticates the user's credentials passed as parameters
* with ones stored in the underlying database.
*
* @param dsName datasouce that stores the user credentials
* @param dbType database type of the underlying database that stores user credentials
* @param userLogin the user's login name
* @param password the user's password
*
* @exception DAOException if the database operation fails during authentication <p>
*
* @return true is the authentication succeeds, or false if the authentication fails
*/
public boolean authenticate(String dsName, String dbType, String userLogin, String password) throws DAOException
{
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try
{
con = super.createConnection(dsName);
String sql = QueryManager.getInstance().getAuthenticationQuery(dbType);
log.debug("Authentication SQL: " + sql);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, userLogin);
rset = pstmt.executeQuery();
if (rset.next())
{
String dbUserLogin = rset.getString(1);
String dbPassword = rset.getString(2);
log.debug("Password from db: " + dbPassword);
log.debug("Password from screen: " + password);
dbPassword = EEMSCryptography.getInstance().decrypt(dbPassword);
log.debug("Password from db decripted: " + dbPassword);
if(dbUserLogin == null || dbPassword == null)
{
return false;
}
else
{
if(userLogin.equals(dbUserLogin) && password.equals(dbPassword))
{
// put login timestamp
sql = QueryManager.getInstance().getSetLoginTimeStampQuery(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setTimestamp(1, new Timestamp((new Date()).getTime()));
pstmt.setString(2, userLogin);
pstmt.executeUpdate();
return true;
}
}
}
}
catch(SQLException e)
{
throw new DAOException(e.getMessage());
}
catch(Exception e)
{
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
super.releaseConnection(con);
}
return false;
}
public SessionUserVO findUserSessionInfo(String dsName, String dbType, String userLogin) throws DAOException
{
Connection con = null;
PreparedStatement stmt = null;
ResultSet rset = null;
SessionUserVO userSess = new SessionUserVO();
try
{
con = super.createConnection(dsName);
String sql = QueryManager.getInstance().getUserSessionInfoQuery(dbType);
stmt = con.prepareStatement(sql);
stmt.setString(1, userLogin);
rset = stmt.executeQuery();
if (rset.next())
{
userSess = new SessionUserVO();
userSess.setUserId(rset.getString(1));
userSess.setProfileId(rset.getString(2));
userSess.setFirstName(rset.getString(3));
userSess.setMiddleName(rset.getString(4));
userSess.setLastName(rset.getString(5));
userSess.setPosition(rset.getString(6));
userSess.setAgency(rset.getString(7));
userSess.setUserLoginId(rset.getString(8));
userSess.setProfileReportId(rset.getString(9));
userSess.setDefaultPage(rset.getString(10));
userSess.setDefaultMenu(rset.getString(11));
}
}
catch(SQLException se)
{
throw new DAOException(se.getMessage());
}
finally
{
super.cleanUp(rset, stmt);
super.releaseConnection(con);
}
return userSess;
}
public UserPrivilegeList findUserPrivileges(String dsName, String dbType, String userLogin) throws DAOException
{
UserPrivilegeList privList = new UserPrivilegeList();
Connection con = null;
PreparedStatement stmt = null;
ResultSet rset = null;
try
{
con = super.createConnection(dsName);
String sql = QueryManager.getInstance().getFindUserPrivilegesQuery(dbType);
stmt = con.prepareStatement(sql);
stmt.setString(1, userLogin);
rset = stmt.executeQuery();
PrivilegeVO privilegeVO = null;
while (rset.next())
{
privilegeVO = new PrivilegeVO();
privilegeVO.setPrivilegeId(rset.getString(1));
privilegeVO.setPrivilegeName(rset.getString(2));
privilegeVO.setPrivilegeType(rset.getString(3));
privilegeVO.setAccessLevel(rset.getString(4));
privilegeVO.setDescription(rset.getString(5));
privList.addPrivilege(privilegeVO.getPrivilegeName(), privilegeVO);
}
}
catch(SQLException se)
{
log.error("ERROR: ", se);
throw new DAOException();
}
finally
{
super.cleanUp(rset, stmt);
super.releaseConnection(con);
}
return privList;
}
/**
* @param dsName
* @param dbType
* @param userVO
*/
public void updateUser(String dsName, String dbType, UserVO userVO) throws EEMSException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getUpdateUserQuery(dbType);
log.debug(sql);
if(!userExists(con,dbType,userVO))
{
pStmt = con.prepareStatement( sql );
pStmt.setString( 3, userVO.getId() );
pStmt.setString( 1, userVO.getUserName() );
log.debug("Before Encryption: " + userVO.getPassword());
String password = EEMSCryptography.getInstance().encrypt(userVO.getPassword());
log.debug("After Encryption: " + password);
pStmt.setString( 2, password);
pStmt.executeUpdate();
this.createSearchContent(con,dbType,userVO,"TBL_USER","USER_ID");
/* Group list information */
String[] groupIds = EEMSUtil.convertToArray(userVO.getGroupNameList(),IConstants.EEMS_DELIMITER);
if(groupIds!=null)
{
/* delete the entries from tbl_user_group */
sql = qManager.getUserSelectedGroupDeleteQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
pStmt.setString( 1, userVO.getId() );
pStmt.executeUpdate();
/* insert selected group */
sql = qManager.getFindCreateUserGroupQuery(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("user id "+userVO.getId());
pStmt.setString( 1, userVO.getId() );
pStmt.setString( 2, groupIds[i].trim() );
pStmt.executeUpdate();
log.debug("ROLE-GROUP added" + i);
}
}
}
log.debug("inserted...and updated");
}
else
{
log.debug("Duplicate User ID found ....aborting...") ;
DAOException daox = new DAOException ("Duplicate User");
daox.setMessageKey(IErrorMessageKeys.KEY_DUPLICATE_USER);
throw daox;
}
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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
public ArrayList getAllUsers(String dsName, String dbType) throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
UserVO userVO = null;
ArrayList userList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getFindAllUserQuery(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
userVO = new UserVO();
userVO.setId(rset.getString(1));
userVO.setUserName(rset.getString(2));
userList.add(userVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
if( rset!= null )
{
rset.close();
}
pstmt.close();
this.releaseConnection(con);
}
return userList;
}
public boolean userExists(Connection con, String dbType, UserVO userVO) throws DAOException, SQLException
{
boolean userExists=true;
PreparedStatement pStmt= null;
ResultSet rset = null;
try
{
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getExisitingUserQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, userVO.getUserName() );
pStmt.setString( 2, userVO.getId());
rset = pStmt.executeQuery();
if (rset.next() && rset.getInt(1)>0)
{
userExists = true;
log.debug("user found"+ rset.getInt(1) );
}
else
{
userExists = false;
log.debug("user NOT found"+ rset.getInt(1) );
}
}
catch(SQLException ex)
{
}
finally
{
if( rset!= null )
{
rset.close();
}
pStmt.close();
}
return userExists;
}
/**
* @param dsName
* @param dbType
* @param distGroupId
* @return
*/
public ArrayList getDistGroupSelectedUsers(String dsName, String dbType, String distGroupId) throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
UserVO userVO = null;
ArrayList userList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getDistGroupSelectedUserQuery(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, distGroupId );
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
userVO = new UserVO();
userVO.setId(rset.getString(1));
userVO.setUserName(rset.getString(2));
userList.add(userVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
if( rset!= null )
{
rset.close();
}
pstmt.close();
this.releaseConnection(con);
}
return userList;
}
/**
* @param dsName
* @param dbType
* @param distGroupId
* @return
*/
public ArrayList getGroupSelectedUsers(String dsName, String dbType, String groupId) throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
UserVO userVO = null;
ArrayList userList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getSelectedGroupUsersInfo(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, groupId );
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
userVO = new UserVO();
userVO.setId(rset.getString(2));
userVO.setUserName(rset.getString(1));
userList.add(userVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
if( rset!= null )
{
rset.close();
}
pstmt.close();
this.releaseConnection(con);
}
return userList;
}
/**
* @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.getDeleteUserQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
log.debug("User 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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
public String findUserID(String dsName, String dbType, String id) throws DAOException
{
Connection con = this.createConnection( dsName );
String userId = "";
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getFindUserIDQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
ResultSet rset = pStmt.executeQuery();
if(rset.next()){
userId=rset.getString(1);
}
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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
return userId;
}
public String findLoginID(String dsName, String dbType, String id) throws DAOException
{
Connection con = this.createConnection( dsName );
String userId = "";
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getFindLoginIDQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
ResultSet rset = pStmt.executeQuery();
if(rset.next()){
userId=rset.getString(1);
}
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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
return userId;
}
public String findProfileID(String dsName, String dbType, String id) throws DAOException
{
Connection con = this.createConnection( dsName );
String userId = "";
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getFindProfileIDQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
ResultSet rset = pStmt.executeQuery();
if(rset.next()){
userId=rset.getString(1);
}
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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
return userId;
}
public String findForceProfileEnabled(String dsName, String dbType) throws DAOException
{
Connection con = this.createConnection( dsName );
String profileEnabled = "";
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getForceProfileEnabledQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
ResultSet rset = pStmt.executeQuery();
if(rset.next()){
profileEnabled=rset.getString(1);
}
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
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
return profileEnabled;
}
}