Example #1
0
  /**
   * 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;
  }
Example #2
0
  /**
   * 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;
  }
Example #3
0
  /**
   * 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;
  }