/**
* <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;
}
/**
* <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;
}