/**
* This method is used to check a sub category is in active
*
* @param subCategoryVOobj - details about sub category
* @throws DeleteSubCategoryDeclinedException - throws if the sub category is in used.
* @throws ApplicationException - wrap exception in application object of exception framework
*/
private void checkDeleteAllowed(SubCategoryVO subCategoryVOobj)
throws DeleteSubCategoryDeclinedException, ApplicationException {
Connection conn = null;
PreparedStatement preStat1 = null;
ResultSet rs1 = null;
try {
conn = DBConnection.getDBConnection();
/* select all teh active sub-sub-category for given sub-category */
String sqlQuery1 = "select * from CRM_SUB_SUB_CATEGORY where PARENT_ID= ? AND STATUS=?";
if (conn != null) {
preStat1 = conn.prepareStatement(sqlQuery1);
preStat1.setInt(1, subCategoryVOobj.getSubCategoryId());
preStat1.setString(2, "1");
}
rs1 = preStat1.executeQuery();
if (rs1.next()) {
throw new DeleteSubCategoryDeclinedException(
"Sub-Category can not be deleted", "subcategory.delete.exception");
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
} catch (DeleteSubCategoryDeclinedException e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new DeleteSubCategoryDeclinedException(e.getBusinessReason(), e.getBusinessCode());
} catch (Exception e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e);
} finally {
try {
if (conn != null) {
conn.close();
}
if (rs1 != null) {
rs1.close();
}
if (preStat1 != null) {
preStat1.close();
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
}
}
}
/**
* This method is used to check the duplicate sub category.
*
* @param object1 - details about sub category to check.
* @throws SubCategoryNameDuplicatedException - throws if the sub category name is duplicated.
* @throws ApplicationException - wrap exception in application object of exception framework
*/
private void checkDuplicateSubCategory(SubCategoryVO object1)
throws SubCategoryNameDuplicatedException, ApplicationException {
Connection conn1 = null;
PreparedStatement preStat1 = null;
ResultSet rs1 = null;
try {
conn1 = DBConnection.getDBConnection();
/* check if a sub-category with the same name already exists */
String sqlQuery1 = "select * from CRM_SUB_CATEGORY where SUB_CATEGORY_NAME = ?";
if (conn1 != null) {
preStat1 = conn1.prepareStatement(sqlQuery1);
preStat1.setString(1, object1.getSubCategoryName());
}
rs1 = preStat1.executeQuery();
if (rs1.next()) {
throw new SubCategoryNameDuplicatedException(
"Sub-Type name cannot be duplicate", "subcategory.name.duplicate.exception");
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
} catch (SubCategoryNameDuplicatedException e) {
throw new SubCategoryNameDuplicatedException(e.getBusinessReason(), e.getBusinessCode());
} catch (Exception e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e);
} finally {
try {
if (conn1 != null) {
conn1.close();
}
if (rs1 != null) {
rs1.close();
}
if (preStat1 != null) {
preStat1.close();
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
}
}
}
/**
* This method is used to create sub-categories
*
* @param SubCategoryVO - details of subCategory to be created.
* @return boolean - true if sub category is create successfully else false
* @throws BusinessException - wrap exception in business object of exception framework
* @throws ApplicationException - wrap exception in application object of exception framework
*/
public boolean createSubCategory(SubCategoryVO object1)
throws BusinessException, ApplicationException {
boolean result = false;
Date todaysDate = (new Date());
java.sql.Date sqlDate = new java.sql.Date(todaysDate.getTime());
String status = object1.getStatus();
int catStatus = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_INACTIVE;
if (status.equals("Active")) {
catStatus = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_ACTIVE;
}
checkDuplicateSubCategory(object1);
Connection conn = null;
PreparedStatement preStat = null;
Statement stat = null;
ResultSet resultSet = null;
try {
/* create a sub-category */
String sqlQuery =
"insert into CRM_SUB_CATEGORY(SUB_CATEGORY_NAME, STATUS, CREATED_BY , CREATED_DATE, SUB_CATEGORY_DESCRIPTION,"
+ "SUB_CATEGORY_ID,PARENT_ID) values (?,?,?,?,?,?,?)";
conn = DBConnection.getDBConnection();
if (conn != null) {
preStat = conn.prepareStatement(sqlQuery);
stat = conn.createStatement();
/* sequence used for generating the sub-cagegory id */
resultSet = stat.executeQuery("SELECT NEXT VALUE FOR SUB_CAT_ID_SEQ FROM SYSIBM.sysdummy1");
resultSet.next();
int subCatId = resultSet.getInt(1);
preStat.setString(1, object1.getSubCategoryName());
preStat.setInt(2, catStatus);
preStat.setString(3, object1.getUserName());
preStat.setDate(4, sqlDate);
preStat.setString(5, object1.getDescription());
preStat.setInt(6, subCatId);
preStat.setInt(7, object1.getCategoryId());
int rowInserted = preStat.executeUpdate();
if (rowInserted == 1) {
logger.info("Sub-Category Created");
result = true;
} else {
logger.info("Sub-Category not Created");
}
} else {
logger.info("Connection is null ");
}
conn.close();
UtilityCache.populateSubCategoryCache();
UtilityCache.populateRetailerSubCategoryCache();
} catch (SQLException sql) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sql);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sql);
} catch (Exception e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e);
} finally {
try {
if (conn != null) {
conn.close();
}
if (resultSet != null) {
resultSet.close();
}
if (stat != null) {
stat.close();
}
if (preStat != null) {
preStat.close();
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
}
}
return result;
}
/**
* This method is used to edit a sub problem type (sub-category)
*
* @param SubCategoryVO - information about sub category to be edited.
* @return boolean - true if the edit is success else false
* @throws BusinessException - wrap exception in business object of exception framework
* @throws ApplicationException - wrap exception in application object of exception framework
*/
public boolean editSubCategory(SubCategoryVO subCategoryVOobj)
throws BusinessException, ApplicationException {
boolean result = false;
Date todaysDate = (new Date());
java.sql.Date sqlDate = new java.sql.Date(todaysDate.getTime());
String status = subCategoryVOobj.getStatus();
int catStatus = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_INACTIVE;
if (status.equals("Active")) {
catStatus = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_ACTIVE;
}
/* if the user tries to inactivate a sub-category, check some condition */
if (catStatus == AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_INACTIVE) {
checkDeleteAllowed(subCategoryVOobj);
}
Connection conn1 = null;
PreparedStatement preStat = null;
ResultSet resultSet = null;
try {
conn1 = DBConnection.getDBConnection();
String sqlQuery =
"update CRM_SUB_CATEGORY SET(STATUS, MODIFIED_BY, MODIFIED_DATE, SUB_CATEGORY_DESCRIPTION) = (?,?,?,?) where SUB_CATEGORY_ID = ?";
if (conn1 != null) {
preStat = conn1.prepareStatement(sqlQuery);
preStat.setInt(1, catStatus);
preStat.setString(2, subCategoryVOobj.getUserName());
preStat.setDate(3, sqlDate);
preStat.setString(4, subCategoryVOobj.getDescription());
preStat.setInt(5, subCategoryVOobj.getSubCategoryId());
int rowUpdated = preStat.executeUpdate();
if (rowUpdated == 1) {
result = true;
} else {
logger.info("Sub-Category not modified");
}
} else {
logger.info("Connection is null ");
}
conn1.close();
UtilityCache.populateSubCategoryCache();
UtilityCache.populateRetailerSubCategoryCache();
} catch (SQLException sql) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sql);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sql);
} catch (Exception e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e);
} finally {
try {
if (conn1 != null) {
conn1.close();
}
if (resultSet != null) {
resultSet.close();
}
if (preStat != null) {
preStat.close();
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
}
}
return result;
}
/**
* This method is used to search sub-category
*
* @param SubCategoryVO - search criteria
* @throws BusinessException - wrap exception in business object of exception framework
* @throws ApplicationException - wrap exception in application object of exception framework
*/
public List searchSubCategory(SubCategoryVO object1, int lowerBound, int upperBound)
throws BusinessException, ApplicationException {
Connection conn = null;
Connection conn1 = null;
PreparedStatement preStat = null;
PreparedStatement preStat1 = null;
PreparedStatement preStat2 = null;
ResultSet resultSet = null;
ResultSet resultSet1 = null;
ResultSet rs1 = null;
ArrayList<SubCategoryVO> arrayList = new ArrayList<SubCategoryVO>();
String stausStr = null;
String catName = null;
lowerBound = lowerBound + 1;
try {
// String sqlQuery2="select count(*) from CRM_SUB_CATEGORY where
// PARENT_ID = ? AND SUB_CATEGORY_NAME LIKE ? AND
// SUB_CATEGORY_DESCRIPTION LIKE ?"; //select the records matching
// the search criteria
//
// String sqlQuery="select * from (select a.*, row_number() over()
// rnum from (select * from CRM_SUB_CATEGORY where PARENT_ID = ? AND
// SUB_CATEGORY_NAME LIKE ?" +
// " AND SUB_CATEGORY_DESCRIPTION LIKE ?)a)b where rnum
// >="+lowerBound+" and rnum <="+upperBound+""; //select the records
// matching the search criteria
StringBuffer sqlQuery =
new StringBuffer("select * from (select a.*, row_number() over() rnum from (");
sqlQuery.append("select * from CRM_SUB_CATEGORY"); // fetch all
// userID
StringBuffer sqlQueryinner =
new StringBuffer("select count(*) from (select * from CRM_SUB_CATEGORY");
if (!(object1.getCategoryId() == -1)) {
sqlQuery.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE PARENT_ID= ");
sqlQuery.append(object1.getCategoryId());
sqlQueryinner.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE PARENT_ID= ");
sqlQueryinner.append(object1.getCategoryId());
}
if (!(object1.getSubCategoryName().equals("")) || object1.getSubCategoryName() == null) {
sqlQuery.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_NAME LIKE ");
sqlQuery.append("'%" + object1.getSubCategoryName() + "%'");
sqlQueryinner.append(
" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_NAME LIKE ");
sqlQueryinner.append("'%" + object1.getSubCategoryName() + "%'");
}
if (!(object1.getDescription().equals("") || object1.getDescription() == null)) {
sqlQuery.append(
" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_DESCRIPTION LIKE ");
sqlQuery.append("'%" + object1.getDescription() + "%'");
sqlQueryinner.append(
" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_DESCRIPTION LIKE ");
sqlQueryinner.append("'%" + object1.getDescription() + "%'");
}
sqlQuery.append(")a) b where rnum >=" + lowerBound + " and rnum <=" + upperBound);
sqlQueryinner.append(")h");
conn = DBConnection.getDBConnection();
conn1 = DBConnection.getDBConnection();
if (conn != null) {
String sql = sqlQuery.toString();
String sql1 = sqlQueryinner.toString();
preStat =
conn.prepareStatement(
sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
resultSet = preStat.executeQuery();
if (!resultSet.next()) {
throw new NoRecordExistsException(
"No Record Found", "subcategory.search.norecord.exception");
} else {
resultSet.previous(); // move the cursor to the first row
while (resultSet.next()) {
String stat = resultSet.getString("STATUS");
if (stat.equals("1")) {
stausStr = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_1;
} else {
stausStr = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_0;
}
int catId = resultSet.getInt("PARENT_ID");
if (conn1 != null) {
String sqlQuery1 = "select CATEGORY_NAME from CRM_CATEGORY where CATEGORY_ID = ? ";
preStat1 = conn1.prepareStatement(sqlQuery1);
preStat1.setInt(1, catId);
rs1 = preStat1.executeQuery();
if (rs1.next()) {
catName = rs1.getString("CATEGORY_NAME");
}
}
SubCategoryVO voObj = new SubCategoryVO();
voObj.setCategoryName(catName);
voObj.setSubCategoryId(resultSet.getInt("SUB_CATEGORY_ID"));
voObj.setSubCategoryName(resultSet.getString("SUB_CATEGORY_NAME"));
voObj.setDescription(resultSet.getString("SUB_CATEGORY_DESCRIPTION"));
voObj.setStatus(stausStr);
arrayList.add(voObj);
}
}
preStat2 =
conn.prepareStatement(
sql1, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
resultSet1 = preStat2.executeQuery();
if (resultSet1.next()) {
noOfRecords = resultSet1.getInt(1);
getRecordCount(); // get the number of records in the
// result set
}
} else {
logger.info("Connection is null ");
}
conn.close();
} catch (SQLException sql) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sql);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sql);
} catch (NoRecordExistsException e) {
throw new NoRecordExistsException(e.getBusinessReason(), e.getBusinessCode());
} catch (Exception e) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e);
} finally {
try {
if (conn != null) {
conn.close();
}
if (conn1 != null) {
conn1.close();
}
if (resultSet != null) {
resultSet.close();
}
if (rs1 != null) {
rs1.close();
}
if (preStat != null) {
preStat.close();
}
if (preStat1 != null) {
preStat1.close();
}
} catch (SQLException sqlExcep) {
logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep);
throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep);
}
}
return arrayList;
}