private String convertToString(IdVector pAccountIdv) {
String stringOfIds = "";
if ((pAccountIdv != null && pAccountIdv.size() > 0)) {
for (int i = 0; i < pAccountIdv.size(); i++) {
if (stringOfIds.length() > 0) {
stringOfIds = stringOfIds + "," + (String) pAccountIdv.get(i);
} else {
stringOfIds = (String) pAccountIdv.get(i);
}
}
}
return stringOfIds;
}
/**
* <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;
}