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.DistributionGroupVO;
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;
/**
* This class implements data storage and access logic
*/
public class DistributionGroupDAO extends BaseDAO
{
private Logger log = (Logger)Logger.getInstance(this.getClass().getName());
/**
* @param dsName
* @param dbType
* @return ArrayList
* @throws DAOException
*/
public ArrayList findGroupsForDistribution(String dsName,String dbType)
throws DAOException
{
ArrayList list = new ArrayList();
Connection con = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
try
{
con = this.createConnection(dsName);
String sql = QueryManager.getInstance().getFindDIstributionGroupsQuery(dbType);
pStmt = con.prepareStatement(sql);
rs = pStmt.executeQuery();
DistributionGroupVO vo = null;
while( rs.next() )
{
vo = new DistributionGroupVO();
vo.setId(rs.getString(1));
vo.setDistGroupName(rs.getString(2));
vo.setDistGroupDesc(rs.getString(3));
list.add( vo );
}
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error: " + e);
throw new DAOException( e.getMessage());
}
finally
{
super.cleanUp(rs,pStmt);
super.releaseConnection(con);
}
return list;
}
/**
* Called to create a new DistributionGroup Report
* @param dsName
* @param dbType
* @param distGroupVO
* @throws DAOException
*/
public void createDistGroup(String dsName, String dbType, DistributionGroupVO distGroupVO) throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Check if there is any Dist group with same name */
String sql;
sql = qManager.getDuplicateDistGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, distGroupVO.getDistGroupName() );
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_DIST_GROUP);
throw daox;
}
}
sql = qManager.getFetchNewDistGroupIdQuery(dbType);
log.debug(sql);
distGroupVO.setId(this.getCurrentSequenceValue(con,sql));
sql = qManager.getFetchNewDistGroupQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, distGroupVO.getId() );
pStmt.setString( 2, distGroupVO.getDistGroupName() );
pStmt.setString( 3, distGroupVO.getDistGroupDesc());
pStmt.setString( 4, distGroupVO.getIsEnabled());
pStmt.executeUpdate();
this.createSearchContent(con,dbType,distGroupVO,"TBL_DISTRIBUTION_GROUP","GROUP_ID");
String[] userIds = EEMSUtil.convertToArray(distGroupVO.getUserNames(),IConstants.EEMS_DELIMITER);
if(userIds!=null)
{
sql = qManager.getFetchDistGroupUserQuery(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 "+distGroupVO.getId());
pStmt.setString( 1, userIds[i].trim() );
pStmt.setString( 2, distGroupVO.getId() );
pStmt.executeUpdate();
log.debug("user-group added" + i);
}
}
}
log.debug("inserted...and returning");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error: " + 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
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called when new unique sequence value is necessary
* @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;
}
/**
* Called when listing sorted by name
* @param dsName
* @param dbType
* @param page
* @param sort
* @param userId
* @param profileRepId
* @return ReportResultsVO
* @throws DAOException
*/
public ReportResultsVO findDistributionGroupByName(String dsName, String dbType, PageInfoVO page, SortInfoVO sort, String userId, String profileRepId )
throws DAOException
{
String sql = QueryManager.getInstance().getFindDistributionGroupByName(dbType);
return super.findReportResult(dsName, dbType,page,sort, sql);
}
/**
* Called to get information of a particular Distribution Group Report
* @param dsName
* @param dbType
* @param reportID
* @return DistributionGroupVO
* @throws DAOException
*/
public DistributionGroupVO findDistGroup(String dsName, String dbType, String reportID) throws DAOException
{
Connection con = super.createConnection(dsName);
DistributionGroupVO distGroupVO = new DistributionGroupVO();
distGroupVO.setId(reportID);
PreparedStatement pstmt=null;
ResultSet rset=null;
log.debug("start of DB Fetch");
try {
String sql = QueryManager.getInstance().getFindDistGroup(dbType);
pstmt = con.prepareStatement(sql);
log.debug(sql);
pstmt.setString(1, distGroupVO.getId());
rset = pstmt.executeQuery();
String users = "";
String sep = ", ";
while(rset.next())
{
if(users.equals(""))
{
distGroupVO.setDistGroupName(rset.getString(1));
distGroupVO.setDistGroupDesc(rset.getString(2));
distGroupVO.setIsEnabled(rset.getString(3));
//log.debug("user="+ rset.getString(1));
}
if(!rset.getString(4).equals("NA"))
{
users += rset.getString(4)+sep;
}
}
if(users.endsWith(sep))
{
users=users.substring(0,(users.length()-sep.length()));
}
distGroupVO.setUserNames(users);
log.debug("users="+users);
}
catch (Exception e)
{
// TODO Auto-generated catch block
throw new DAOException(e.getMessage());
}
finally
{
super.cleanUp(rset,pstmt);
super.releaseConnection(con);
}
log.debug("End of DB Fetch" + distGroupVO);
return distGroupVO;
}
/**
* Called to update Distribution Group Report
* @param dsName
* @param dbType
* @param distGroupVO
* @throws DAOException
*/
public void updateDistGroup(String dsName, String dbType, DistributionGroupVO distGroupVO)throws DAOException
{
Connection con = this.createConnection( dsName );
PreparedStatement pStmt=null;
try
{
con.setAutoCommit(false);
QueryManager qManager = QueryManager.getInstance();
/* Check if there is any Dist group with same name */
String sql;
sql = qManager.getDuplicateDistGroupUpdateQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, distGroupVO.getId() );
pStmt.setString( 2, distGroupVO.getDistGroupName() );
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_DIST_GROUP);
throw daox;
}
}
sql = qManager.getUpdateDistGroupQuery(dbType);
log.debug(sql);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, distGroupVO.getDistGroupName() );
pStmt.setString( 2, distGroupVO.getDistGroupDesc() );
pStmt.setString( 3, distGroupVO.getIsEnabled());
pStmt.setString( 4, distGroupVO.getId());
pStmt.executeUpdate();
this.createSearchContent(con,dbType,distGroupVO,"TBL_DISTRIBUTION_GROUP","GROUP_ID");
//first delete old users
sql = qManager.getDeleteDistGroupUserQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, distGroupVO.getId());
pStmt.executeUpdate();
String[] userIds = EEMSUtil.convertToArray(distGroupVO.getUserNames(),IConstants.EEMS_DELIMITER);
if(userIds!=null)
{
sql = qManager.getFetchDistGroupUserQuery(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 "+distGroupVO.getId());
pStmt.setString( 1, userIds[i].trim() );
pStmt.setString( 2, distGroupVO.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: " + 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: " + e);
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
/**
* Called to delete the 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_DISTRIBUTION_GROUP */
String sql = qManager.getDeleteDistGroupQuery(dbType);
pStmt = con.prepareStatement( sql );
pStmt.setString( 1, id );
pStmt.executeUpdate();
log.debug("Distribution Group Deleted ");
con.commit();
}
catch(SQLException ex)
{
try
{
con.rollback();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
log.error("Error: " + 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: " + e);
DAOException daox = new DAOException ("FATAL Error");
daox.setMessageKey(IErrorMessageKeys.KEY_FATAL_ERROR);
throw daox;
}
}
}
}