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.common.dao.DAOException;
import com.eforce.baby.common.dao.QueryManager;
import com.eforce.baby.auth.vo.GroupVO;
import com.eforce.baby.reports.vo.ReportResultsVO;
import com.eforce.baby.common.vo.PageInfoVO;
import com.eforce.baby.common.vo.SortInfoVO;
import com.eforce.baby.utils.IErrorMessageKeys;
import com.eforce.baby.utils.EEMSUtil;
import com.eforce.baby.utils.IConstants;
import com.eforce.baby.common.dao.BaseDAO;
/**
* This class implements data storage and access logic
*/
public class GroupDAO extends BaseDAO {
private Logger log = (Logger) Logger.getInstance(this.getClass().getName());
/**
* Called when listing sorted by name
* @param dsName
* @param dbType
* @param page
* @param sort
* @param userId
* @param profileRepId
* @return ReportResultsVO
* @throws DAOException
*/
public ReportResultsVO findGroupByName(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId) throws DAOException
{
String sql = QueryManager.getInstance().getFindUserGroupQuery(dbType);
log.debug("SQL: " + sql);
return super.findReportResult(dsName, dbType,page,sort, sql);
}
/**
* Called when listing sorted by role
* @param dsName
* @param dbType
* @param page
* @param sort
* @param userId
* @param profileRepId
* @return ReportResultsVO
* @throws DAOException
*/
public ReportResultsVO findGroupByRole(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId) throws DAOException
{
String sql = QueryManager.getInstance().getFindUserGroupRoleQuery(dbType);
log.debug("SQL: " + sql);
return super.findReportResult(dsName, dbType,page,sort, sql);
}
/**
* Called when it is opened for update and 'Submit' button is pressed
* @param dsName
* @param dbType
* @param groupVO
* @throws DAOException
*/
public void updateGroup( String dsName, String dbType, GroupVO groupVO )
throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Check if there is any group with same name */
String sql;
sql = qManager.getDuplicateGroupUpdateQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, groupVO.getId() );
pStmt.setString( 2, groupVO.getGroupName() );
log.debug("SQL="+sql);
log.debug("ID="+groupVO.getId()+" || "+groupVO.getGroupName());
ResultSet 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_GROUP);
throw daox;
}
}
sql = qManager.getUpdateGroupQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, groupVO.getGroupName() );
pStmt.setString( 2, groupVO.getGroupDesc() );
pStmt.setString( 3, groupVO.getId());
pStmt.executeUpdate();
//first delete old users
sql = qManager.getDeleteGroupUserQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, groupVO.getId());
pStmt.executeUpdate();
this.createSearchContent(con,dbType, groupVO, "TBL_GROUP", "GROUP_ID");
String[] userIds = EEMSUtil.convertToArray(groupVO.getUserNames(),IConstants.EEMS_DELIMITER);
if(userIds!=null)
{
sql = qManager.getFindCreateUserGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<userIds.length;i++)
{
if(userIds[i]!= null)
{
log.debug("userID " + userIds[i]);
log.debug("groupid "+groupVO.getId());
pStmt.setString( 1, userIds[i].trim() );
pStmt.setString( 2, groupVO.getId() );
pStmt.executeUpdate();
log.debug("user-group added" + i);
}
}
}
log.debug("inserted...and updated");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error GroupDAO SQLException:" + e);
}
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
log.error("Error GroupDAO SQLException:" + e);
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called when report is viewed
* @param dsName
* @param dbType
* @param groupId
* @return GroupVO
* @throws DAOException
*/
public GroupVO findGroup(String dsName, String dbType, String groupId) throws DAOException
{
GroupVO groupVO = new GroupVO();
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
ResultSet rset = null;
QueryManager qManager = QueryManager.getInstance();
String sql = qManager.getGroupViewQuery(dbType);
log.debug("SQL:"+sql);
String userLoginId = null;
String userId = null;
try{
pStmt = con.prepareStatement(sql);
pStmt.setString(1, groupId);
rset = pStmt.executeQuery();
while(rset.next())
{
if(userLoginId==null)
userLoginId += rset.getString(1);
else
userLoginId += (IConstants.EEMS_DELIMITER + rset.getString(1));
if(userId==null)
userId += rset.getString(4);
else
userId += (IConstants.EEMS_DELIMITER + rset.getString(4));
groupVO.setGroupName(rset.getString(2));
groupVO.setGroupDesc(rset.getString(3));
groupVO.setId(groupId);
}
//Storing Role information separately
sql = qManager.getGroupRoleQuery(dbType);
pStmt = con.prepareStatement(sql);
pStmt.setString(1,groupId);
rset = pStmt.executeQuery();
String roleName="NA";
while(rset.next()){
roleName=rset.getString(1);
}
groupVO.setRoleName(roleName);
groupVO.setUserNames(userLoginId);
groupVO.setUserIDs(userId);
}
catch(SQLException e){
log.error("Error GroupDAO SQLException:" + e);
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_DATABASE_ERROR);
throw daox;
}
finally{
try
{
if(rset != null)
{
rset.close();
}
if(pStmt != null)
{
pStmt.close();
}
this.releaseConnection(con);
log.debug("Connection released");
}
catch (SQLException e)
{
// TODO Auto-generated catch block
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
return groupVO;
}
/**
* Called when new report is created
* @param dsName
* @param dbType
* @param groupVO
* @throws DAOException
*/
public void createGroup(String dsName, String dbType, GroupVO groupVO)throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Checking for duplicate group */
String sql;
sql = qManager.getDuplicateGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, groupVO.getGroupName() );
ResultSet 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_GROUP);
throw daox;
}
}
sql = qManager.getFetchNewGroupIdQuery(dbType);
log.debug(sql);
groupVO.setId(this.getCurrentSequenceValue(con,sql));
//sql = qManager.getFetchNewGroupQuery(dbType);
sql = qManager.getFindCreateGroupQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, groupVO.getId() );
pStmt.setString( 2, groupVO.getGroupName() );
pStmt.setString( 3, groupVO.getGroupDesc());
pStmt.executeUpdate();
this.createSearchContent(con,dbType, groupVO, "TBL_GROUP", "GROUP_ID");
String[] userIds = EEMSUtil.convertToArray(groupVO.getUserNames(),IConstants.EEMS_DELIMITER);
if(userIds!=null)
{
sql = qManager.getFindCreateUserGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
log.debug(sql);
for (int i=0; i<userIds.length;i++)
{
if(userIds[i]!= null)
{
pStmt.setString( 1, userIds[i].trim() );
pStmt.setString( 2, groupVO.getId() );
pStmt.executeUpdate();
log.debug("user-group added" + i);
}
}
}
log.debug("inserted in tbl_group...and returning");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
log.debug("Rollback...");
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error GroupDAO SQLException:" + e);
}
DAOException daox = new DAOException ("Database Error");
daox.setMessageKey(IErrorMessageKeys.KEY_DATABASE_ERROR);
throw daox;
}
finally
{
try
{
if(pStmt!=null) pStmt.close();
this.releaseConnection(con);
log.debug("Connection released");
}
catch (SQLException e)
{
// TODO Auto-generated catch block
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called to get unique sequence number
* @param con
* @param query
* @return String
* @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
* @return ArrayList
* @throws DAOException
* @throws SQLException
*/
public ArrayList getAllGroups(String dsName, String dbType) throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
GroupVO groupVO = null;
ArrayList groupList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getFindAllGroupsQuery(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
groupVO = new GroupVO();
groupVO.setId(rset.getString(1));
groupVO.setGroupName(rset.getString(2));
groupList.add(groupVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
this.releaseConnection(con);
}
return groupList;
}
/**
* @param dsName
* @param dbType
* @param roleId
* @return ArrayList
* @throws DAOException
* @throws SQLException
*/
public ArrayList getDistGroupSelectedGroups(String dsName, String dbType, String roleId)
throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
GroupVO groupVO = null;
ArrayList groupList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getDistGroupSelectedGroupsQuery(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, roleId );
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
groupVO = new GroupVO();
groupVO.setId(rset.getString(1));
groupVO.setGroupName(rset.getString(2));
groupList.add(groupVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
this.releaseConnection(con);
}
return groupList;
}
/**
* Called to delete a report
* @param dsName
* @param dbType
* @param id
* @throws DAOException
*/
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();
/* Delete from tbl_User_Group table */
String sql = qManager.getDeleteGroupUserQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
pStmt.close();
/* Select role_id from tbl_group_role table */
sql = qManager.getFindRoleIDQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
ResultSet rset = pStmt.executeQuery();
String roleID = "";
if(rset.next())
roleID= rset.getString(1);
pStmt.close();
if(!roleID.equals("")){
/* Delete from tbl_Group_role table */
sql = qManager.getDeleteGroupRoleQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
int numRwoDel = 0;
numRwoDel = pStmt.executeUpdate();
pStmt.close();
if(numRwoDel == 1){
/* Delete from tbl_role table */
sql = qManager.getDelRoleQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
pStmt.close();
}
}
/* Delete from tbl_Group table */
sql = qManager.getDeleteGroupQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
pStmt.close();
log.debug("Group Deleted ");
con.commit();
}
catch(SQLException ex)
{
log.error("Error GroupDAO SQLException:" + ex);
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error GroupDAO SQLException:" + e);
}
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
log.error("Error GroupDAO SQLException:" + e);
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called when user selected group info to be displayed
* @param dsName
* @param dbType
* @param roleId
* @return ArrayList
* @throws DAOException
* @throws SQLException
*/
public ArrayList getUserSelectedGroups(String dsName, String dbType, String userId)
throws DAOException, SQLException
{
Connection con = super.createConnection(dsName);
GroupVO groupVO = null;
ArrayList groupList=new ArrayList();
PreparedStatement pstmt=null;
ResultSet rset=null;
try {
String sql = QueryManager.getInstance().getUserSelectedGroupQuery(dbType);
pstmt = con.prepareStatement(sql);
pstmt.setString( 1, userId );
log.debug(sql);
rset = pstmt.executeQuery();
while(rset.next())
{
groupVO = new GroupVO();
groupVO.setId(rset.getString(1));
groupVO.setGroupName(rset.getString(2));
groupList.add(groupVO);
}
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset, pstmt);
this.releaseConnection(con);
}
return groupList;
}
}