/* * Similar to makeSureValInRange function except it is meant to return * more than one values of valid user input via LinkedList and it will * not return unless all of the input is valid * @param1 scanner object * @param2 lowerbound int * @param3 upperbound int * @param4 String that represents the entity we are entering the values for */ public static LinkedList<Integer> makeSureValInRange2( Scanner scan, int lowerbound, int upperbound, String inputFor) { assert (lowerbound <= upperbound); LinkedList<Integer> list = new LinkedList<Integer>(); System.out.println( SearchCriteria.prioritySetUpString( SearchCriteria.StringEnum.SELECT_CRITERIA, inputFor)); // prints the options to the user int value = scan.nextInt(); if (valInRange(lowerbound, upperbound, value)) { list.add(value); // If the first inputted is in range then we add more values boolean done = false; int i = lowerbound; while (!done && i < upperbound) { // keep looking for input until user enters a duplicate value // or the LinkedList of input is full value = scan.nextInt(); if (!list.contains(value) && valInRange(lowerbound, upperbound, value)) list.add(value); else done = true; i++; } return list; } else { // If the first value intered is not valid, then we return a null, which means that // we use default values (all values within range) System.out.println( SearchCriteria.prioritySetUpString(SearchCriteria.StringEnum.DEFAULT, inputFor)); return null; } }
/* *@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); } }