Esempio n. 1
0
  /**
   * <code>getDistributorByCriteria</code> returns a vector of DistributorData's meeting the
   * criteria of the BusEntitySearchCriteria.
   *
   * @param pCrit a <code>BusEntitySearchCriteria</code> value the criteria
   * @return a <code>DistributorDataVector</code> value
   * @exception SQLException if an error occurs
   */
  public DistributorDataVector getDistributorByCriteria(BusEntitySearchCriteria pCrit)
      throws RemoteException {

    DistributorDataVector dV = new DistributorDataVector();
    Connection conn = null;
    // STORE FILTER===========================================================//
    Integer storeId = null;
    if (pCrit.getStoreBusEntityIds() != null && pCrit.getStoreBusEntityIds().size() > 0) {
      storeId = (Integer) pCrit.getStoreBusEntityIds().get(0);
    } else {
      throw new RemoteException("Store ID can't be null. ");
    }
    String storeFilter =
        "(select STORE_DIM_ID from DW_STORE_DIM \n" + "  where STORE_ID = " + storeId + " ) \n";

    //  NAMES FILTER=========================================================//
    String nameFilter =
        getSearchSqlByFilterName("JD_DIST_NAME", pCrit.getSearchName(), pCrit.getSearchNameType());
    ;
    // =======================================================================//
    try {
      //        conn = getConnection();
      conn = getReportConnection();
      String sql =
          "select  \n "
              + " DISTRIBUTOR_DIM_ID, \n"
              + " DIST_ID, DIST_NAME, \n"
              + " JD_DIST_ID, JD_DIST_NAME, \n"
              + " JD_DIST_CITY, JD_DIST_STATE, \n"
              + " JD_DIST_ADDRESS, JD_DIST_STATUS_CD \n"
              + "from DW_DISTRIBUTOR_DIM where \n"
              + " STORE_DIM_ID = "
              + storeFilter
              + " \n"
              + nameFilter
              + " order by JD_DIST_NAME";

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      if (pCrit.getResultLimit() != QueryRequest.UNLIMITED) {
        //          stmt.setMaxRows(2000);
      }
      while (rs.next()) {
        DistributorData dd = DistributorData.createValue();

        BusEntityData be = new BusEntityData();
        be.setBusEntityId(rs.getInt("DISTRIBUTOR_DIM_ID"));
        be.setShortDesc((rs.getString("JD_DIST_NAME") != null) ? rs.getString("JD_DIST_NAME") : "");
        be.setBusEntityStatusCd(
            (rs.getString("JD_DIST_STATUS_CD") != null) ? rs.getString("JD_DIST_STATUS_CD") : "");

        AddressData ad = new AddressData();
        ad.setAddress1(
            (rs.getString("JD_DIST_ADDRESS") != null) ? rs.getString("JD_DIST_ADDRESS") : "");
        ad.setCity((rs.getString("JD_DIST_CITY") != null) ? rs.getString("JD_DIST_CITY") : "");
        ad.setStateProvinceCd(
            (rs.getString("JD_DIST_STATE") != null) ? rs.getString("JD_DIST_STATE") : "");

        dd.setBusEntity(be);
        dd.setPrimaryAddress(ad);

        dV.add(dd);
      }
      stmt.close();
    } catch (Exception exc) {
      exc.printStackTrace();
      throw processException(exc);
    } finally {
      closeConnection(conn);
    }
    return dV;
  }
Esempio n. 2
0
  /**
   * <code>getAccountsUIByCriteria</code> returns a vector of AccountUIView's meeting the criteria
   * of the BusEntitySearchCriteria.
   *
   * @param pCrit a <code>BusEntitySearchCriteria</code> value the criteria
   * @return a <code>AccountUIViewVector</code> value
   * @exception SQLException if an error occurs
   */
  public AccountUIViewVector getAccountsUIByCriteria(BusEntitySearchCriteria pCrit)
      throws RemoteException {
    Connection conn = null;
    AccountUIViewVector acctVec = new AccountUIViewVector();
    try {
      // USER FILTER ==========================================================//
      Integer userId = null;
      String userFilterForAccounts = "";
      if (pCrit.getUserIds() != null && pCrit.getUserIds().size() > 0) {
        userId = (Integer) pCrit.getUserIds().get(0);
        userFilterForAccounts = getUserFilterForAccounts(userId.intValue());
      }
      // STORE FILTER===========================================================//
      Integer storeId = null;
      if (pCrit.getStoreBusEntityIds() != null && pCrit.getStoreBusEntityIds().size() > 0) {
        storeId = (Integer) pCrit.getStoreBusEntityIds().get(0);
      } else {
        throw new RemoteException("Store ID can't be null. ");
      }
      String storeFilter =
          "(select STORE_DIM_ID from DW_STORE_DIM \n" + "  where STORE_ID = " + storeId + " ) \n";

      String storeFilterForAccounts = getStoreFilterForAccounts(storeId.intValue());

      // ACCOUNT NAMES FILTER==================================================//
      String accountNameFilter =
          getSearchSqlByFilterName(
              "JD_ACCOUNT_NAME", pCrit.getSearchName(), pCrit.getSearchNameType());
      String accountIdFilter =
          (Utility.isSet(pCrit.getSearchId())) ? " and JD_ACCOUNT_ID = " + pCrit.getSearchId() : "";
      // ========================================================================//
      conn = getReportConnection();
      String sql =
          "select  \n "
              + " wm_concat(ACCOUNT_DIM_ID) ACCOUNT_DIM_IDS, \n"
              + " ACCOUNT_ID,  \n"
              + " JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
              + " JD_ACCOUNT_CITY, JD_ACCOUNT_STATE, \n"
              + " JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
              + "from DW_ACCOUNT_DIM where \n"
              + "   STORE_DIM_ID = "
              + storeFilter
              + " \n"
              +
              /*            " and  account_ID in ( " + storeFilterForAccounts +") \n"
               */
              userFilterForAccounts
              + accountIdFilter
              + accountNameFilter
              + " group by  \n "
              + "    ACCOUNT_ID,  \n"
              + "    JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
              + "    JD_ACCOUNT_CITY, JD_ACCOUNT_STATE,  \n"
              + "    JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
              + " order by JD_ACCOUNT_NAME ";

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      if (pCrit.getResultLimit() != QueryRequest.UNLIMITED) {
        stmt.setMaxRows(2000);
      }
      int rowId = 0;
      while (rs.next()) {
        AccountUIView aui = AccountUIView.createValue();
        rowId--;
        String accountDimIds =
            (rs.getString("ACCOUNT_DIM_IDS") != null) ? rs.getString("ACCOUNT_DIM_IDS") : "";

        BusEntityData be = new BusEntityData();
        be.setBusEntityId(rowId);
        be.setShortDesc(
            (rs.getString("JD_ACCOUNT_NAME") != null) ? rs.getString("JD_ACCOUNT_NAME") : "");
        be.setBusEntityStatusCd(
            (rs.getString("JD_ACCOUNT_STATUS_CD") != null)
                ? rs.getString("JD_ACCOUNT_STATUS_CD")
                : "");

        PropertyData pd = new PropertyData();
        pd.setValue((rs.getString("JD_MARKET") != null) ? rs.getString("JD_MARKET") : "");

        AddressData ad = new AddressData();
        ad.setAddress1("");
        ad.setCity(
            (rs.getString("JD_ACCOUNT_CITY") != null) ? rs.getString("JD_ACCOUNT_CITY") : "");
        ad.setStateProvinceCd(
            (rs.getString("JD_ACCOUNT_STATE") != null) ? rs.getString("JD_ACCOUNT_STATE") : "");

        aui.setAccountDimIds(accountDimIds);
        aui.setBusEntity(be);
        aui.setAccountType(pd);
        aui.setPrimaryAddress(ad);
        acctVec.add(aui);
      }
      stmt.close();
    } catch (Exception exc) {
      exc.printStackTrace();
      throw processException(exc);
    } finally {
      closeConnection(conn);
    }
    return acctVec;
  }