protected String getField(
Segment pSegment, int pFieldNum, boolean pMandatoryFl, String pErrorMessage) {
DataElement de = null;
String val = null;
String err = null;
try {
de = pSegment.getDataElement(pFieldNum);
} catch (Exception exc) {
if (pMandatoryFl) {
err = pErrorMessage + " (1)";
}
}
if (err == null && de == null && pMandatoryFl) {
err = pErrorMessage + " (2)";
}
if (err == null && de != null) {
try {
val = de.get();
} catch (Exception exc) {
}
if (!Utility.isSet(val) && pMandatoryFl) {
err = pErrorMessage + " (3)";
}
}
if (err != null) {
errorMsgs.add(err);
}
return val;
}
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;
}
public void extractShipByN1(Loop inLoop) throws OBOEException {
Loop loop = null;
int numberInVector = inLoop.getCount("N1");
for (int i = 0; i < numberInVector; i++) {
loop = inLoop.getLoop("N1", i);
if (loop == null) return;
Segment segment = loop.getSegment("N1");
String qualif = getField(segment, 1, false, null);
if ("ST".equals(qualif)) {
String shipToName = getField(segment, 2, false, null);
ediInp856Vw.setShipToName(shipToName);
String identQualifier = getField(segment, 3, false, null);
if ("01".equals(identQualifier)) {
String shipToCode = getField(segment, 4, false, null);
ediInp856Vw.setShipToCode(shipToCode);
}
extractShipFromN4(loop);
} else {
String distName = qualif;
ediInp856Vw.setDistName(distName);
String distCodeQualifier = getField(segment, 2, false, null);
ediInp856Vw.setDistIdentCodeQualif(distCodeQualifier);
if (!Utility.isSet(distName) && !Utility.isSet(distCodeQualifier)) {
errorMsgs.add("No distributor info in segment N1");
setValid(false);
return;
}
if (Utility.isSet(distCodeQualifier)) {
String distCode = getField(segment, 3, false, null);
ediInp856Vw.setDistIdentCode(distCode);
if (!Utility.isSet(distCode) && !Utility.isSet(distName)) {
errorMsgs.add("No distributor info in segment N1");
setValid(false);
return;
}
}
}
}
}
public void extractPurchaseOrderReferencePRF(Loop inLoop) throws OBOEException {
Segment segment = null;
try {
segment = inLoop.getSegment("PRF");
} catch (Exception exc) {
}
if (segment == null) {
return;
}
String purchaseOrderNumber = getField(segment, 1, false, null);
if (Utility.isSet(ediInp856Vw.getDistOrderNum())
&& !ediInp856Vw.getDistOrderNum().equals(purchaseOrderNumber)) {
ediHandler.appendIntegrationRequest(ediInp856Vw);
ediInp856Vw = ediInp856Vw.copy();
ediInp856Vw.setItems(new EdiInp856ItemViewVector());
}
ediInp856Vw.setDistOrderNum(purchaseOrderNumber);
return;
}
/**
* <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;
}
