public static void main(String[] args) throws Exception {
Object result = null;
try {
BrowseGetOptionsHandler test = new BrowseGetOptionsHandler();
Map incomingRequest = new HashMap();
incomingRequest.put("organizationId", "PURIDIOM");
incomingRequest.put("userId", "KELLI");
incomingRequest.put("browseName", "requisitionheader");
incomingRequest.put("successPage", "SUCCESS");
incomingRequest.put("failurePage", "FAILURE");
test.handleRequest(incomingRequest);
System.out.println(incomingRequest);
System.out.println("BrowseRetrieve - " + (String) incomingRequest.get("viewPage"));
BrowseObject browseObject = (BrowseObject) incomingRequest.get("browseObject");
BrowseColumn[] labels = browseObject.getLabels();
for (int i = 0; i < labels.length; i++) {
BrowseColumn column = (BrowseColumn) labels[i];
if (!column.isHidden()) {
System.out.println(labels[i].getLabel() + " - " + column.getColumnName());
}
}
System.out.println("COMPLETE");
} catch (Exception exception) {
System.out.println(exception.toString());
throw exception;
}
}
public Object executeTask(Object object) throws Exception {
StringBuffer query = new StringBuffer();
Object result = null;
Map incomingRequest = (Map) object;
try {
DBSession dbs = (DBSession) incomingRequest.get("dbsession");
BrowseObject b = (BrowseObject) incomingRequest.get("browseObject");
String organizationId = (String) incomingRequest.get("organizationId");
String userTimeZone = (String) incomingRequest.get("userTimeZone");
String userDateFormat = (String) incomingRequest.get("userDateFormat");
String sqlWhere = (String) incomingRequest.get("sqlWhere");
List dateArguments = new ArrayList();
if (Utility.isEmpty(userDateFormat)) {
userDateFormat =
PropertiesManager.getInstance(organizationId)
.getProperty("MISC", "DATEFORMAT", "MM-dd-yyyy");
}
sqlWhere =
ReportDates.dateWhereClauseDecoder(
sqlWhere, dateArguments, organizationId, userTimeZone, userDateFormat);
if (Utility.isEmpty(sqlWhere)) {
sqlWhere = "";
}
String sqlSelect = b.getSqlSelect();
query.append(sqlSelect);
query.append(" from " + b.getSqlFrom());
query.append(" " + sqlWhere);
Log.debug(this, incomingRequest.get("userId") + " - query: " + query.toString());
long start = System.currentTimeMillis();
System.out.println("Report Query: " + query.toString());
List list = dbs.query(query.toString(), dateArguments.toArray(), b.getMaxRows());
long end = System.currentTimeMillis();
Log.error(
this,
"It took "
+ ((end - start) / 1000)
+ " seconds to execute the query: "
+ query.toString());
Log.debug(this, incomingRequest.get("userId") + " - dbs.query COMPLETE.");
result = list;
this.setStatus(dbs.getStatus());
} catch (Exception e) {
incomingRequest.put("failed", Boolean.TRUE);
Log.error(
this,
"Error executing Report Query: " + query.toString() + " -Exception: " + e.getMessage());
e.printStackTrace();
this.setStatus(Status.FAILED);
}
return result;
}
public Object executeTask(Object object) throws Exception {
List result;
try {
Map incomingRequest = (Map) object;
DBSession dbs = (DBSession) incomingRequest.get("dbsession");
String o = (String) incomingRequest.get("organizationId");
String userId = (String) incomingRequest.get("userId");
String userDateFormat = (String) incomingRequest.get("userDateFormat");
BrowseObject b = (BrowseObject) incomingRequest.get("browseObject");
List groupFilters = b.getGroupFilters();
List groupFilterOptions = new ArrayList();
List commodities = new ArrayList();
// boolean unspscCommodities = PropertiesManager.getInstance(o).getProperty("MISC",
// "CommodityType", "").equalsIgnoreCase("UNSPSC");
boolean unspscCommodities = false;
boolean commoditySet = false;
PropertiesManager propertiesManager = PropertiesManager.getInstance(o);
if (Utility.isEmpty(userDateFormat)) {
userDateFormat = propertiesManager.getProperty("MISC", "DateFormat", "MM-dd-yyyy");
}
for (int ix = 0; ix < groupFilters.size(); ix++) {
BrowseGroupFilter gf = (BrowseGroupFilter) groupFilters.get(ix);
StringBuffer query = new StringBuffer();
StringBuffer queryFilter = new StringBuffer();
if (!Utility.isEmpty(gf.getType()) && gf.getType().equals("CostRange")) {
incomingRequest.put("CostRange_itemType", incomingRequest.get("as_item_type"));
}
List dateArguments = new ArrayList();
if (Utility.isEmpty(gf.getType()) || !gf.getType().equals("Keywords")) {
query.append(gf.getSqlSelect() + " from " + gf.getSqlFrom() + " where 1 = 1");
if (Utility.ckNull(gf.getSqlWhere()).length() > 0) {
query.append(" and ( " + gf.getSqlWhere() + ")");
}
if (Utility.ckNull(b.getSqlWhere()).length() > 0) {
String sqlWhere = b.getSqlWhere();
sqlWhere = sqlWhere.replaceAll(":as_userid", "'" + userId + "'");
if (sqlWhere.indexOf(":as_today") > 0) {
dateArguments.add(
Dates.getDate(
Dates.today(userDateFormat, (String) incomingRequest.get("userTimeZone"))));
}
sqlWhere = sqlWhere.replaceAll(":as_today", "?");
query.append(" and ( " + sqlWhere + ")");
}
List filters = b.getBrowseFilters();
if (filters != null) {
for (int i = 0; i < filters.size(); i++) {
BrowseFilter filter = (BrowseFilter) filters.get(i);
String key = filter.getColumnName();
if (!b.validateColumn(key)) {
// skip this filter
continue;
}
String value = encoder.encodeForSQL(new OracleCodec(), filter.getValue());
String operator = filter.getOperator();
String logicalOperator = filter.getLogicalOperator();
if (!BrowseValidationUtility.permissibleOperators.contains(operator)) {
operator = "=";
}
if (!BrowseValidationUtility.permissibleLogicalOperators.contains(logicalOperator)) {
logicalOperator = "AND";
}
String type = filter.getType();
if (!Utility.isEmpty(value)) {
if (Utility.isEmpty(operator)) {
operator = "=";
}
if (Utility.isEmpty(logicalOperator)) {
logicalOperator = "AND";
}
if (Utility.isEmpty(type)) {
type = "STRING";
}
if (queryFilter.length() > 0) {
queryFilter.append(" " + logicalOperator + " ");
}
if (type.equalsIgnoreCase("DATE")) {
dateArguments.add(Dates.getDate(userDateFormat, value));
queryFilter.append(" " + key + " " + operator + " ?");
} else if (operator.equalsIgnoreCase("LIKE")) {
queryFilter.append(" UPPER(" + key + ") " + operator + " '" + value + "'");
} else {
queryFilter.append(" " + key + " " + operator + " '" + value + "'");
}
if (key.indexOf(".commodity") > 0) {
if (!Utility.isEmpty(value)) {
commoditySet = true;
if (value.indexOf("%") > 0) {
String commodity = "00000000";
for (int iv = 0; iv < value.length(); iv++) {
char temp = value.charAt(iv);
if (temp != '%') {
if (iv == 0) {
commodity = temp + commodity.substring(iv + 1);
} else if (commodity.length() >= (iv + 1)) {
commodity =
commodity.substring(0, iv) + temp + commodity.substring(iv + 1);
} else {
commodity = commodity.substring(0, iv) + temp;
}
}
}
if (!commodities.contains(commodity)) {
commodities.add(commodity);
}
}
}
}
}
}
}
if (queryFilter.length() > 0) {
query.append("AND (" + queryFilter + " )");
}
if (gf.getType().equalsIgnoreCase("Commodity") && unspscCommodities) {
String originalQueryString = query.toString();
List commodityList = null;
CommodityGetUNSPSCWhereClause commodityWhere = new CommodityGetUNSPSCWhereClause();
int attempts = 0;
while ((commodityList == null || commodityList.size() == 0) && attempts <= 3) {
query = new StringBuffer(originalQueryString);
String commodityArray[] = new String[commodities.size()];
commodities.toArray(commodityArray);
incomingRequest.put("Commodity_commodity", commodityArray);
incomingRequest.put("retrieveAllCodes", "N");
if (attempts > 0) {
incomingRequest.put("retrieveAllFamilies", "Y");
if (attempts > 1) {
incomingRequest.put("retrieveAllGroups", "Y");
if (attempts > 2) {
incomingRequest.put("retrieveAllCommodities", "Y");
}
}
}
String unspscWhere = (String) commodityWhere.executeTask(incomingRequest);
if (!Utility.isEmpty(unspscWhere)) {
query.append(" AND (" + unspscWhere + ")");
}
if (!Utility.isEmpty(gf.getSqlGroupBy())) {
query.append(" group by " + gf.getSqlGroupBy());
}
if (!Utility.isEmpty(gf.getSqlOrderBy())) {
query.append(" order by " + gf.getSqlOrderBy());
}
Log.debug(this, "group filter query: " + query.toString());
Object arguments[] = new Object[dateArguments.size()];
for (int i = 0; i < dateArguments.size(); i++) {
arguments[i] = dateArguments.get(i);
}
commodityList = dbs.query(query.toString(), arguments);
attempts++;
}
gf.setSelectionValues(commodityList);
} else {
if (!Utility.isEmpty(gf.getSqlGroupBy())) {
query.append(" group by " + gf.getSqlGroupBy());
}
if (!Utility.isEmpty(gf.getSqlOrderBy())) {
query.append(" order by " + gf.getSqlOrderBy());
}
Log.debug(this, "group filter query: " + query.toString());
Object arguments[] = new Object[dateArguments.size()];
for (int i = 0; i < dateArguments.size(); i++) {
arguments[i] = dateArguments.get(i);
}
List list = dbs.query(query.toString(), arguments);
gf.setSelectionValues(list);
}
}
}
result = groupFilters;
this.setStatus(dbs.getStatus());
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return result;
}