/**
* 创建更新激活码
*
* @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;
}
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;
}
/**
* 获取游戏接口参数列表
*
* @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;
}
/*
*@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);
}
}
/**
* <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;
}
/**
* <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;
}