Beispiel #1
0
	/**
	 * 创建更新激活码
	 * 
	 * @param model 
	 *        激活码
	 * @return 激活码编号
	 */
	public int createUpdate(wh.game.model.ActiveCode model) throws Exception{
		Map<String, Object> inParameters = new HashMap<String, Object>();
		inParameters.put("ActiveCodeId", model.getActiveCodeId());
		inParameters.put("GameId", model.getGameId());
		inParameters.put("ServerId", model.getServerId());
		inParameters.put("Name", model.getName());
		inParameters.put("Code", model.getCode());
		inParameters.put("ActiveCodeType", model.getActiveCodeType());
		inParameters.put("CreateDate", Utility.getSqlDate(model.getCreateDate()));
		inParameters.put("ModifyDate", Utility.getSqlDate(model.getModifyDate()));
		inParameters.put("Status", model.getStatus());
		inParameters.put("UsedType", model.getUsedType());
		
		OutParameters outParameters = new OutParameters();
		outParameters.putOutParmType("ErrNo", java.sql.Types.INTEGER);
		outParameters.putOutParmType("ErrMsg", java.sql.Types.NVARCHAR);
		int parasCount = inParameters.size() + outParameters.getOutParmTypes().size();
		StringBuilder sbBuilder = new StringBuilder();
		for (int i = 0; i < parasCount; i++) {
			if (0 == i) {
				sbBuilder.append("?");
			} else {
				sbBuilder.append(",?");
			}
		}
		
		int retValue = SQLHelper.runProcedureNonQuery(Config.CONNECTION_STRING_ULD,
			"{? = call WH_Game_ActiveCode_CreateUpdate(" + sbBuilder.toString() + ")}", inParameters, outParameters);		
		
		int errNo = Integer.valueOf(outParameters.getOutParmValue("ErrNo").toString());
		String errMsg = outParameters.getOutParmValue("ErrMsg").toString();
		MyErr.checkErr(errNo, errMsg);
		
		return retValue;
	}
Beispiel #2
0
  private String getSearchSqlByFilterName(String filterName, String filterVal, int filterType) {
    String nameFilter = "";
    if (Utility.isSet(filterVal)) {

      switch (filterType) {
        case QueryRequest.BEGINS:
          nameFilter =
              " and UPPER("
                  + filterName
                  + ") LIKE UPPER('"
                  + filterVal.replaceAll("'", "''")
                  + "%') \n";
          break;
        case QueryRequest.CONTAINS:
          nameFilter =
              " and UPPER("
                  + filterName
                  + ") LIKE UPPER('%"
                  + filterVal.replaceAll("'", "''")
                  + "%') \n";
          break;
      }
    }
    return nameFilter;
  }
Beispiel #3
0
	/**
	 * 获取游戏接口参数列表
	 * 
	 * @param queryModel 
	 *        查询用户实体
	 * @param totalCount 
	 *        返回总数量
	 * @param isAll 
	 *        是否获取所有数据,false、否;true、是;
	 * @param pageIndex 
	 *        页码,从1开始(>=1)
	 * @param pageSize 
	 *        每页显示数量
	 * @param fldSort 
	 *        排序字段,默认""则按照主键编号排序
	 * @param sortType 
	 *        排序方法,0为升序,1为降序
	 * @return 游戏接口参数列表
     */
	public List<wh.game.model.GameInterfaceParams> getList(wh.game.model.GameInterfaceParams queryModel, RefObject<Integer> totalCount, 
		boolean isAll, int pageIndex, int pageSize, String fldSort, int sortType) throws Exception {
		List<wh.game.model.GameInterfaceParams> list = new ArrayList<wh.game.model.GameInterfaceParams>();
		if (Utility.isEmpty(fldSort)) {
			fldSort = "";
		}
		
		Map<String, Object> inParameters = new HashMap<String, Object>();
		inParameters.put("IsAll", isAll);
		inParameters.put("PageIndex", pageIndex);
		inParameters.put("PageSize", pageSize);
		inParameters.put("FldSort", fldSort);
		inParameters.put("SortType", sortType);
		
		inParameters.put("GameInterfaceParamsId", queryModel.getGameInterfaceParamsId());
		inParameters.put("GameInterfaceId", queryModel.getGameInterfaceId());
		inParameters.put("GameId", queryModel.getGameId());
		inParameters.put("ParamName", queryModel.getParamName());
		inParameters.put("ParamValue", queryModel.getParamValue());
		inParameters.put("ParamExplain", queryModel.getParamExplain());
		inParameters.put("SignType", queryModel.getSignType());
		inParameters.put("SignIndex", queryModel.getSignIndex());
		inParameters.put("ParamType", queryModel.getParamType());
		inParameters.put("ParamInType", queryModel.getParamInType());
		inParameters.put("ParamOutType", queryModel.getParamOutType());
		inParameters.put("SignFormatValue", queryModel.getSignFormatValue());
		
		Map<String, Integer> outParameters = new HashMap<String, Integer>();
		outParameters.put("ErrNo", java.sql.Types.INTEGER);
		outParameters.put("ErrMsg", java.sql.Types.NVARCHAR);
		outParameters.put("TotalCount", java.sql.Types.INTEGER);
		
        int parasCount = inParameters.size() + outParameters.size();
		StringBuilder sbBuilder = new StringBuilder();
		for (int i = 0; i < parasCount; i++) {
			if (0 == i) {
				sbBuilder.append("?");
			}
			else {
				sbBuilder.append(",?");
			}
		}
		
		ResultSet rs = null;
		CallableStatement cstmt = null;
		int errNo = 0;
		String errMsg = "";
		try {
			rs = SQLHelper.runProcedure(Config.CONNECTION_STRING_ULD, "{call WH_Game_GameInterfaceParams_GetList(" + sbBuilder.toString() + ")}",
					inParameters, outParameters);
			if (rs != null) {
				while (rs.next()) {
					list.add(getModel(rs));
				}
				
				cstmt = (CallableStatement)rs.getStatement();
				errNo = cstmt.getInt("ErrNo");
				errMsg = cstmt.getString("ErrMsg");
				totalCount.argvalue = cstmt.getInt("TotalCount");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage(), e);
		} finally {
			SQLHelper.closeAll(cstmt);
		}
		
		MyErr.checkErr(errNo, errMsg);
		return list;
	}
Beispiel #4
0
  /*
   *@param  pCriteria            List of SearchCriteria objects
   *@return  a set of ItemView objects
   *@exception  RemoteException
   */
  public ItemViewVector searchStoreItems(List pCriteria, boolean pDistInfoFl)
      throws RemoteException {
    Connection con = null;
    try {
      con = getReportConnection();

      Integer storeIdI = new Integer(0);
      String categCond = "";
      String shortDescCond = "";
      String manufCond = "";
      String manufSkuCond = "";
      String distSkuCond = "";

      String storeFilter = "";
      /////////////////////
      // Create a set of filters
      for (Iterator iter = pCriteria.iterator(); iter.hasNext(); ) {
        SearchCriteria sc = (SearchCriteria) iter.next();
        String name = sc.getName();
        Object objValue = sc.getObjectValue();
        String strValue = (objValue instanceof String) ? ((String) objValue).trim() : "";
        if (SearchCriteria.STORE_ID.equals(name)) {
          storeIdI = (Integer) objValue;
          storeFilter =
              "select STORE_DIM_ID from DW_STORE_DIM \n"
                  + "  where STORE_ID = "
                  + storeIdI
                  + "  \n";

        } else if (SearchCriteria.CATALOG_CATEGORY.equals(name)) {
          String subStr = " like '%" + strValue.toUpperCase().replaceAll("'", "''") + "%' \n";
          categCond =
              "    and (UPPER(JD_CATEGORY1) "
                  + subStr
                  + " or  \n"
                  + "         UPPER(JD_CATEGORY2) "
                  + subStr
                  + " or  \n"
                  + "         UPPER(JD_CATEGORY3) "
                  + subStr
                  + ") \n";
        } else if (SearchCriteria.PRODUCT_SHORT_DESC.equals(name)) {
          shortDescCond =
              " and UPPER(JD_ITEM_DESC) like '%"
                  + strValue.toUpperCase().replaceAll("'", "''")
                  + "%' \n";
        } else if (SearchCriteria.MANUFACTURER_SHORT_DESC.equals(name)) {
          manufCond =
              " and UPPER(JD_MANUF_NAME) like '%"
                  + strValue.toUpperCase().replaceAll("'", "''")
                  + "%' \n";
        } else if (SearchCriteria.MANUFACTURER_SKU_NUMBER.equals(name)) {
          manufSkuCond =
              " and UPPER(JD_MANUF_SKU) like '%"
                  + strValue.toUpperCase().replaceAll("'", "''")
                  + "%' \n";
        } else if (SearchCriteria.DISTRIBUTOR_SKU_NUMBER.equals(name)) {
          distSkuCond =
              " and UPPER(JD_DIST_SKU) like '%"
                  + strValue.toUpperCase().replaceAll("'", "''")
                  + "%' \n";
        }
      }

      // Get catalog id --------------------------------------------------------//
      /*       int catalogId = 0;
             String sql = "select distinct STORE_CATALOG_ID from DW_CATEGORY_DIM \n" +
                          " where STORE_DIM_ID IN (" + storeFilter  + ")";
             log(
             "DWOperationBean ---------------------------> sql1: " + sql);

             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(sql);
             int count = 0;
             while (rs.next()) {
               count++;
               catalogId = rs.getInt(1);
             }
             rs.close();
             stmt.close();
             if (count == 0) {
               String errorMess = "No catalog for store. Store id: " + storeIdI;
               throw new Exception(errorMess);
             }
             if (count > 1) {
               String errorMess = "Multiple active catalogs for store. Store id: " +
                                  storeIdI;
               throw new Exception(errorMess);
             }
      */
      // ---------------------------------------------------------------------//
      String sql =
          "select                \n"
              + " id.ITEM_DIM_ID,      \n"
              + " JD_ITEM_DESC  ,      \n"
              + " JD_ITEM_PACK ,       \n"
              + " JD_ITEM_UOM ,        \n"
              + " JD_MANUF_SKU,        \n"
              + " JD_MANUF_NAME,        \n"
              + " JD_CATEGORY1         \n";

      String fromSql =
          " from DW_ITEM_DIM id,                               \n "
              + "      DW_CATEGORY_DIM cd,                           \n"
              + "      DW_MANUFACTURER_DIM md                        \n";
      String whereSql =
          " where id.CATEGORY_DIM_ID = cd.CATEGORY_DIM_ID     \n"
              +
              //          "    and cd.STORE_CATALOG_ID = " + catalogId + "    \n" +
              "    and cd.STORE_DIM_ID IN ("
              + storeFilter
              + ")  \n"
              + "    and md.MANUFACTURER_DIM_ID  = id.MANUFACTURER_DIM_ID   \n";

      // ---------------------------------------------------------------------//

      if (pDistInfoFl) {
        sql +=
            " , JD_DIST_SKU,                                       \n"
                + " (select JD_DIST_NAME from DW_DISTRIBUTOR_DIM dd      \n"
                + "   where DISTRIBUTOR_DIM_ID  = did.DISTRIBUTOR_DIM_ID \n"
                + " ) JD_DIST_NAME                                       \n";

        fromSql += "    ,DW_ITEM_DISTRIBUTOR did  \n";
        whereSql += "    and id.ITEM_DIM_ID   = did.ITEM_DIM_ID (+)         \n" + distSkuCond;
      }
      sql += fromSql + whereSql;
      sql += categCond + shortDescCond + manufCond + manufSkuCond;
      sql += " order by JD_MANUF_SKU ";

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      ItemViewVector itemVwV = new ItemViewVector();
      while (rs.next()) {
        ItemView itemVw = ItemView.createValue();
        itemVwV.add(itemVw);
        itemVw.setStoreId(storeIdI.intValue());
        //         itemVw.setCatalogId(catalogId);

        itemVw.setItemId(rs.getInt("ITEM_DIM_ID"));
        itemVw.setName(Utility.strNN(rs.getString("JD_ITEM_DESC")));
        itemVw.setSku(Utility.strNN(rs.getString("JD_MANUF_SKU")));
        itemVw.setUom(Utility.strNN(rs.getString("JD_ITEM_UOM")));
        itemVw.setPack(Utility.strNN(rs.getString("JD_ITEM_PACK")));
        itemVw.setCategory(Utility.strNN(rs.getString("JD_CATEGORY1")));
        itemVw.setManufName(Utility.strNN(rs.getString("JD_MANUF_NAME")));
        itemVw.setManufSku(Utility.strNN(rs.getString("JD_MANUF_SKU")));
        if (pDistInfoFl) {
          itemVw.setDistId(0);
          itemVw.setDistName(Utility.strNN(rs.getString("JD_DIST_NAME")));
          itemVw.setDistSku(Utility.strNN(rs.getString("JD_DIST_SKU")));
        } else {
          itemVw.setDistId(0);
          itemVw.setDistName("");
          itemVw.setDistSku("");
        }
        itemVw.setSelected(false);
      }
      rs.close();
      stmt.close();
      return itemVwV;
    } catch (Exception e) {
      e.printStackTrace();
      throw new RemoteException(e.getMessage());
    } finally {
      closeConnection(con);
    }
  }
Beispiel #5
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;
  }
Beispiel #6
0
  /**
   * <code>getUserSites</code> returns a vector of SiteView's meeting the criteria of the
   * parameters.
   *
   * @param pStoreId a <code>int</code> ID value (from Main DB) of Store selected
   * @param pUserId a <code>int</code> ID value (from Main DB) of Reporting User selected (appUser
   *     if user is not selected or 0 - if user assigned to All Accounts( has property RepOA^))
   * @param pNameTempl <code>String</code> NANE filter value
   * @param nameBeginsFl <code>int</code> NANE filter type (QueryRequest.BEGINS or
   *     QueryRequest.CONTAINS)
   * @param pCity <code>String</code> filter value for CITY
   * @param pState <code>String</code> filter value for STATE
   * @param pAccountIdv <code>IdVector</code> filter of AccountDimIds (null if no filter)
   * @return a <code>SiteViewVector</code> value
   *     <p>/**
   *     ========================================================================================
   */
  public SiteViewVector getUserSites(
      int pStoreId,
      int pUserId,
      Integer pSiteDimId,
      String pNameTempl,
      int nameBeginsFl,
      String pCity,
      String pState,
      IdVector pAccountIdv,
      boolean showInactiveFl,
      int pResultLimit)
      throws RemoteException {

    Connection conn = null;
    SiteViewVector siteV = new SiteViewVector();

    try {
      String storeDimFilter =
          "(select STORE_DIM_ID from DW_STORE_DIM \n" + "  where STORE_ID = " + pStoreId + " ) \n";

      String userFilterForAccounts = ""; // if user is not RepOA^ and accounts where not selected
      if (pUserId > 0 && (pAccountIdv == null || pAccountIdv.size() == 0)) {
        userFilterForAccounts = getUserFilterForAccounts(pUserId);
      }

      String siteNameFilter = getSearchSqlByFilterName("SITE_NAME", pNameTempl, nameBeginsFl);
      String accountIdsFilter =
          (pAccountIdv != null && pAccountIdv.size() > 0)
              ? "and ACCOUNT_DIM_ID in (" + convertToString(pAccountIdv) + ")"
              : "";
      String cityFilter =
          (Utility.isSet(pCity)) ? " and UPPER(SITE_CITY) LIKE UPPER('" + pCity + "%')" : "";
      String stateFilter =
          (Utility.isSet(pState)) ? " and UPPER(SITE_STATE) LIKE UPPER('" + pState + "%')" : "";

      //     conn = getConnection();
      conn = getReportConnection();

      String sql =
          "select  SITE_DIM_ID, \n"
              + " ACCOUNT_DIM_ID, ACCOUNT_ID, \n"
              + " (Select JD_ACCOUNT_NAME from DW_ACCOUNT_DIM a \n"
              + "  where a.ACCOUNT_DIM_ID = s.ACCOUNT_DIM_ID) JD_ACCOUNT_NAME, \n"
              + " SITE_ID,  \n"
              + " SITE_NAME, SITE_STREET_ADDRESS, SITE_CITY, SITE_STATE, SITE_STATUS_CD \n"
              + "from DW_SITE_DIM  s\n"
              + "where \n"
              + "   STORE_DIM_ID = "
              + storeDimFilter
              + " \n"
              + userFilterForAccounts
              + accountIdsFilter
              + siteNameFilter
              + cityFilter
              + stateFilter
              + "order by SITE_NAME ";

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      if (pResultLimit != QueryRequest.UNLIMITED) {
        stmt.setMaxRows(Constants.MAX_SITES_TO_RETURN);
      }
      while (rs.next()) {
        SiteView sv = SiteView.createValue();
        sv.setId(rs.getInt("SITE_DIM_ID"));
        sv.setName(rs.getString("SITE_NAME"));
        sv.setAccountId(rs.getInt("ACCOUNT_ID"));
        sv.setAccountName(rs.getString("JD_ACCOUNT_NAME"));
        sv.setAddress(rs.getString("SITE_STREET_ADDRESS"));
        sv.setCity(rs.getString("SITE_CITY"));
        sv.setState(rs.getString("SITE_STATE"));
        sv.setStatus(rs.getString("SITE_STATUS_CD"));
        String targetS = null; // rs.getString("SITE_RANK");
        int target = 0;
        if (targetS != null) {
          try {
            target = Integer.parseInt(targetS);
          } catch (NumberFormatException e) {
          }
        }
        sv.setTargetFacilityRank(target);

        siteV.add(sv);
      }
      stmt.close();

    } catch (Exception exc) {
      exc.printStackTrace();
      throw processException(exc);
    } finally {
      closeConnection(conn);
    }
    return siteV;
  }