V2_0.java [src/java/m/wqm/wqm03_pesticideattributes] Revision: Date:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package m.wqm.wqm03_pesticideattributes;
import csip.api.server.PayloadResults;
import csip.api.server.ServiceException;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.logging.Level;
import javax.ws.rs.Path;
import csip.annotations.Description;
import csip.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.json.simple.parser.ParseException;
import soils.db.tables.Table;
import soils.db.tables.TableColumnDouble;
import soils.db.tables.TableColumnInteger;
import soils.db.tables.TableColumnString;
import wqm.utils.DBResources;
import static wqm.utils.DBResources.WQM_ID;
import static wqm.utils.Pesticides.AI_LIST;
import static wqm.utils.Pesticides.PRODUCTS_LIST;
/**
*
* @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
*/
@Name("WQM-03: Pesticide Product List (PestProdList) Version 2.0")
@Description("This service returns a list of pesticide products and "
+ "their active ingredients from the wqm_pesticide_products table "
+ "of the WQM Data Mart. The list is filtered by criteria provided "
+ "in the request payload.")
@Path("m/wqm/pestprodlist/2.0")
@Resource(from = DBResources.class)
@Deprecated
public class V2_0 extends csip.ModelDataService {
private static final int MAX_PRODUCTS = 40;
private ArrayList<m.wqm.wqm03_pesticideattributes.V2_0.SearchFilter> filters;
private Products products;
private int max_products;
@Override
protected void preProcess() throws csip.api.server.ServiceException {
Map<String, JSONObject> inputMap = getParamMap();
JSONArray filterArray;
filters = new ArrayList<>();
max_products = JSONUtils.getIntParam(inputMap, "max_products", MAX_PRODUCTS);
filterArray = parameter().getJSONArray("filters");
try {
for (int i = 0; i < filterArray.length(); i++) {
m.wqm.wqm03_pesticideattributes.V2_0.SearchFilter tFilter
= new m.wqm.wqm03_pesticideattributes.V2_0.SearchFilter(filterArray.getJSONArray(i));
filters.add(tFilter);
}
} catch (JSONException ex) {
LOG.log(Level.SEVERE, "Error in processing the request JSON for WQM-3!", ex);
throw new ServiceException("Error in processing the request JSON.", ex);
}
}
@Override
protected void doProcess() throws csip.api.server.ServiceException, SQLException {
products = new Products(filters);
}
@Override
protected void postProcess() throws ServiceException, ParseException, JSONException {
products.toJSON(max_products, results());
}
protected class Products {
ProductsData productsData = new ProductsData();
LinkedHashMap<String, Product> productsFound = new LinkedHashMap<>();
String query = "SELECT wqm.d_pesticides_products.reg_no as " + ProductData.REG_NO + ", wqm.d_pesticides_products.Id as " + ProductNameData.PRODUCT_ID + ", "
+ " wqm.d_pesticides_products.epa_code as " + ProductData.EPA_NO + ", "
+ " wqm.d_pesticides_products.prod_name as " + ProductNameData.PRODUCT_NAME + ", wqm.d_pesticides_ais.Id as " + ActiveIngredientsData.AI_ID + ", "
+ " wqm.d_pesticides_products.app_area as " + ProductData.APP_AREA + ", wqm.d_pesticides_products.app_meth as " + ProductData.APP_METHOD + ", "
+ " wqm.d_pesticides_ais.ai_name as " + ActiveIngredientsData.AI_NAME + ", wqm.d_pesticides_formula.PC_PCT as " + ActiveIngredientsData.AI_PCT + ", "
+ " wqm.d_pesticides_ais.app_rate as " + ActiveIngredientsData.DEFAULT_APP_RATE_ID + " "
+ " FROM wqm.d_pesticides_products "
+ " INNER JOIN wqm.d_pesticides_formula ON wqm.d_pesticides_products.REG_NO=wqm.d_pesticides_formula.REG_NO "
+ " INNER JOIN wqm.d_pesticides_ais ON wqm.d_pesticides_formula.PC_CODE=wqm.d_pesticides_ais.pc_code "
+ " WHERE ";
Products(ArrayList<V2_0.SearchFilter> filters) throws SQLException, ServiceException {
if (null != filters) {
int counter = 0;
String newQuery = query;
ArrayList<String> whereList = new ArrayList<>();
for (V2_0.SearchFilter aFilter : filters) {
if (counter > 0) {
newQuery += " AND ";
}
String tWhere = aFilter.getWhereClause();
if (!aFilter.isIntegerType()) {
whereList.add(aFilter.getWhereCondition());
}
newQuery += tWhere;
counter++;
}
newQuery += " AND wqm.d_pesticides_ais.HAS_DATA=1 "
+ " ORDER BY wqm.d_pesticides_products.reg_no, product_name;";
try (Connection conn = resources().getJDBC(WQM_ID);
PreparedStatement statement = conn.prepareStatement(newQuery)) {
for (int i = 0; i < whereList.size(); i++) {
statement.setString(i + 1, whereList.get(i));
}
ResultSet results = statement.executeQuery();
counter = 0;
if (results.next()) {
do {
String reg_no = results.getString(ProductData.REG_NO);
if (productsFound.containsKey(reg_no)) {
productsFound.get(reg_no).addProduct(results);
} else {
Product product = new Product(results);
productsFound.put(reg_no, product);
counter++;
}
} while (results.next());
}
// Removed: Just report a finding result count of "0", since this isn't necessarily an error condition.
//else {
// throw new ServiceException("Database query returned no results for this search criteria.");
// }
productsData.ResultCount(counter);
}
}
}
public JSONArray toJSON(int max_products, PayloadResults results) throws JSONException {
JSONArray ret_val = new JSONArray();
int counter = 0;
productsData.toJSON(ret_val);
results.put("return_count", (productsFound.size() > max_products) ? max_products : productsFound.size(), "Actual count of products returned for this search. May be smaller than the actual result size found, if max_products input value was set to a value less than the actual result size.");
JSONArray listArray = new JSONArray();
for (Product product : productsFound.values()) {
product.toJSON(listArray);
counter++;
if (counter >= max_products) {
break;
}
}
results.put(PRODUCTS_LIST, listArray, "List of products found in the database matching the search criteria");
return ret_val;
}
protected class Product {
ProductData data = new ProductData();
LinkedHashMap<String, ProductNameData> productNames = new LinkedHashMap<>();
LinkedHashMap<String, ActiveIngredientsData> activeIngredients = new LinkedHashMap<>();
Product(ResultSet results) throws ServiceException, SQLException {
data.readValuesFromSQL(results);
addProduct(results);
}
private void addProduct(ResultSet results) throws ServiceException, SQLException {
String reg_no = results.getString(ProductData.REG_NO);
String nameId = results.getString(ProductNameData.PRODUCT_ID);
String aiId = results.getString(ActiveIngredientsData.AI_ID);
if (data.RegNo().equals(reg_no)) {
if (!productNames.containsKey(nameId)) {
ProductNameData newData = new ProductNameData();
newData.readValuesFromSQL(results);
productNames.put(nameId, newData);
}
if (!activeIngredients.containsKey(aiId)) {
ActiveIngredientsData newData = new ActiveIngredientsData();
newData.readValuesFromSQL(results);
activeIngredients.put(aiId, newData);
}
} else {
throw new ServiceException("Invalid " + ProductData.REG_NO + " passed to Product::addProduct");
}
}
public void toJSON(JSONArray tArray) throws JSONException {
JSONArray ret_val = new JSONArray();
data.toJSON(ret_val);
JSONArray names = new JSONArray();
for (ProductNameData nameData : productNames.values()) {
JSONArray tempArray = new JSONArray();
nameData.toJSON(tempArray);
names.put(tempArray);
}
ret_val.put(JSONUtils.data(PRODUCTS_LIST, names));
JSONArray ingredients = new JSONArray();
for (ActiveIngredientsData aiData : activeIngredients.values()) {
JSONArray tempArray = new JSONArray();
aiData.toJSON(tempArray);
ingredients.put(tempArray);
}
ret_val.put(JSONUtils.data(AI_LIST, ingredients));
tArray.put(ret_val);
}
}
}
protected static class SearchFilter {
private static final String[] compareTypes = {"begins_with", "ends_with", "contains", "is_exactly"};
private SearchFilter.columnName filterName;
private String searchValue = "";
private int compareTypeIdx = -1; //Error condition value. If still set to -1 after the below for-loop then there was an error.
SearchFilter(JSONArray filterArray) throws ServiceException {
try {
Map<String, JSONObject> filterMap = JSONUtils.preprocess(filterArray);
if (filterMap.containsKey("pp_filter_name")) {
filterName = new SearchFilter.columnName(filterMap.get("pp_filter_name").getString("value"));
if (filterMap.containsKey("pp_filter_value")) {
searchValue = filterMap.get("pp_filter_value").getString("value");
for (int i = 0; i < compareTypes.length; i++) {
if (filterMap.containsKey(compareTypes[i])) {
if (filterMap.get(compareTypes[i]).getBoolean("value")) {
if (-1 == compareTypeIdx) {
compareTypeIdx = i;
} else {
throw new ServiceException("Only one form of compare type is allowed per filter.");
}
}
}
}
if (-1 == compareTypeIdx) {
String availableCompares = "";
for (int i = 0; i < compareTypes.length; i++) {
availableCompares += ((i > 0) ? ", " : "") + compareTypes[i];
}
throw new ServiceException("Missing a compare method type parameter for this filter name, [" + filterName.getName() + "], request. Available compare types are: " + availableCompares);
} else {
if (filterName.isInteger() && (!compareTypes[compareTypeIdx].equals("is_exactly"))) {
throw new ServiceException("The filter name specified, [" + filterName.getName() + "], requires the use of the 'is_exactly' compare type.");
}
}
} else {
throw new ServiceException("Missing 'pp_filter_value' parameter for this filter request. Cannot proceed.");
}
} else {
throw new ServiceException("Missing 'pp_filter_name' parameter for this filter request. Cannot proceed.");
}
} catch (JSONException ex) {
throw new ServiceException("JSONException", ex);
}
if (!filterName.isValid()) {
throw new ServiceException("Filter name specified is not a valid column name. Cannot proceed.");
}
}
boolean isIntegerType() {
return filterName.isInteger();
}
String getWhereCondition() {
String ret_val = "";
if (filterName.isValid() && !filterName.isInteger() && compareTypeIdx >= 0 && compareTypeIdx < compareTypes.length) {
ret_val = searchValue;
}
return ret_val;
}
String getWhereClause() {
String ret_val = "";
if (filterName.isValid() && compareTypeIdx >= 0 && compareTypeIdx < compareTypes.length) {
if (!filterName.isInteger()) {
if (compareTypes[compareTypeIdx].equals("is_exactly")) {
ret_val = "( " + filterName.getFullName() + "=? )";
} else {
ret_val = "( " + filterName.getFullName() + " LIKE ";
if (compareTypes[compareTypeIdx].equals("begins_with") && !filterName.isInteger()) {
ret_val += "CONCAT(?,'%')";
} else if (compareTypes[compareTypeIdx].equals("contains") && !filterName.isInteger()) {
ret_val += "CONCAT('%',?,'%')";
} else if (compareTypes[compareTypeIdx].equals("ends_with") && !filterName.isInteger()) {
ret_val += "CONCAT('%',?)";
}
ret_val += " ) ";
}
} else if (compareTypes[compareTypeIdx].equals("is_exactly")) {
ret_val = "( " + filterName.getFullName() + "=" + Integer.parseInt(searchValue) + " )";
}
}
return ret_val;
}
// Since the column names used in the filter are constrained, this class will help
// ensure that the contraints are adhered to.
protected static class columnName {
protected static final String names[] = {"reg_no", "prod_name", "type_desc", "pc_code", "ai_name", "epa_code", "company_code", "product_code"};
protected static final String fullNames[] = {"wqm.d_pesticides_products.reg_no", "wqm.d_pesticides_products.prod_name", "type_desc",
"wqm.d_pesticides_formula.pc_code", "wqm.d_pesticides_ais.ai_name", "wqm.d_pesticides_products.epa_code",
"wqm.d_pesticides_products.company_code", "wqm.d_pesticides_products.product_code"};
protected int nameIdx;
protected static int NAMES_LEN = names.length;
protected String Type = "string";
columnName(String cName) {
nameIdx = -1;
for (int i = 0; i < NAMES_LEN; i++) {
if (names[i].equals(cName)) {
nameIdx = i;
break;
}
}
switch (nameIdx) {
case 6:
case 7:
Type = "integer";
}
}
public boolean isInteger() {
return Type.equals("integer");
}
public String getType() {
return Type;
}
public String getFullName() {
String ret_val = "";
if ((nameIdx >= 0) && (nameIdx <= NAMES_LEN)) {
ret_val = fullNames[nameIdx];
}
return ret_val;
}
public String getName() {
String ret_val = "";
if ((nameIdx >= 0) && (nameIdx <= NAMES_LEN)) {
ret_val = names[nameIdx];
}
return ret_val;
}
public boolean isValid() {
return ((nameIdx >= 0) && (nameIdx <= NAMES_LEN));
}
}
}
protected static class ProductsData extends Table {
static final String RESULT_COUNT = "result_count";
static final String[] RESULT_COUNT_DATA = {RESULT_COUNT, "Quantifies how many results were found for this search. May be different than return_count if result_count exceeded the input variable for max_products"};
ProductsData() {
columns.put(RESULT_COUNT, new TableColumnInteger(RESULT_COUNT_DATA));
columns.get(RESULT_COUNT).setIncludeInOutput(true);
}
void ResultCount(int value) {
columns.get(RESULT_COUNT).setValue(value);
}
}
protected static class ProductData extends Table {
static final String REG_NO = "reg_no";
static final String EPA_NO = "epa_code";
static final String APP_AREA = "app_area_id";
static final String APP_METHOD = "app_method_id";
static final String[] REG_NO_DATA = {REG_NO, "EPA product registration number (EPA Reg. No.) in padded form"};
static final String[] EPA_NO_DATA = {EPA_NO, "EPA product registration number (EPA Reg. No.)"};
static final String[] APP_AREA_DATA = {APP_AREA, "Application Area String database Id"};
static final String[] APP_METHOD_DATA = {APP_METHOD, "Application Method String database Id"};
ProductData() {
columns.put(EPA_NO, new TableColumnString(EPA_NO_DATA));
columns.put(REG_NO, new TableColumnString(REG_NO_DATA));
columns.put(APP_AREA, new TableColumnInteger(APP_AREA_DATA));
columns.put(APP_METHOD, new TableColumnInteger(APP_METHOD_DATA));
setNonOutputColumns(null);
setUnusedColumns(null);
}
public void RegNo(String value) {
columns.get(REG_NO).setValue(value);
}
public String RegNo() {
return (String) columns.get(REG_NO).getValue();
}
}
protected static class ProductNameData extends Table {
static final String PRODUCT_NAME = "product_name";
static final String PRODUCT_ID = "product_id";
static final String[] PRODUCT_NAME_DATA = {PRODUCT_NAME, "EPA product name"};
static final String[] PRODUCT_ID_DATA = {PRODUCT_ID, "Database Id of the product name"};
ProductNameData() {
columns.put(PRODUCT_NAME, new TableColumnString(PRODUCT_NAME_DATA));
columns.put(PRODUCT_ID, new TableColumnString(PRODUCT_ID_DATA));
setNonOutputColumns(null);
setUnusedColumns(null);
}
}
protected static class ActiveIngredientsData extends Table {
static final String AI_ID = "ai_id";
static final String AI_NAME = "ai_name";
static final String AI_PCT = "ai_pct";
static final String DEFAULT_APP_RATE_ID = "default_app_rate_id";
static final String[] AI_ID_DATA = {AI_ID, "Active ingredient database Id"};
static final String[] AI_NAME_DATA = {AI_NAME, "Active ingredient name"};
static final String[] AI_PCT_DATA = {AI_PCT, "Active ingredient percent", "Percent", "%.3f"};
static final String[] DEFAULT_APP_RATE_ID_DATA = {DEFAULT_APP_RATE_ID, "Default application rate database id"};
ActiveIngredientsData() {
columns.put(AI_ID, new TableColumnString(AI_ID_DATA));
columns.put(AI_NAME, new TableColumnString(AI_NAME_DATA));
columns.put(AI_PCT, new TableColumnDouble(AI_PCT_DATA));
columns.put(DEFAULT_APP_RATE_ID, new TableColumnString(DEFAULT_APP_RATE_ID_DATA));
setNonOutputColumns(null);
setUnusedColumns(null);
}
}
}