V1_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.ServiceException;
import csip.annotations.Polling;
import csip.annotations.Resource;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
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 wqm.utils.DBResources;
import static wqm.utils.DBResources.WQM_ID;
/**
*
* @author od
* @author Shaun Case
*/
@Name("WQM-03: Pesticide Product List (PestProdList)")
@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/1.0")
@Resource(from = DBResources.class)
@Deprecated
public class V1_0 extends csip.ModelDataService {
private ArrayList<m.wqm.wqm03_pesticideattributes.V1_0.SearchFilter> filters;
private ArrayList<m.wqm.wqm03_pesticideattributes.V1_0.SearchResult> searchResults;
@Override
protected void preProcess() throws csip.api.server.ServiceException {
JSONArray filterArray;
filters = new ArrayList<>();
searchResults = new ArrayList<>();
filterArray = parameter().getJSONArray("filters");
try {
for (int i = 0; i < filterArray.length(); i++) {
m.wqm.wqm03_pesticideattributes.V1_0.SearchFilter tFilter
= new m.wqm.wqm03_pesticideattributes.V1_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 {
String query = "SELECT reg_no, prod_name, type_desc, pc_code, ai_name, pc_pct "
+ "FROM wqm.wqm_pesticide_products WHERE ";
int counter = 0;
for (m.wqm.wqm03_pesticideattributes.V1_0.SearchFilter aFilter : filters) {
if (counter > 0) {
query += " AND ";
}
query += aFilter.getWhereClause();
counter++;
}
try (Connection conn = resources().getJDBC(WQM_ID);
Statement statement = conn.createStatement()) {
try (ResultSet results = statement.executeQuery(query)) {
while (results.next()) {
Map<String, String> resultMap = new HashMap<>();
resultMap.put("reg_no", results.getString("reg_no"));
resultMap.put("prod_name", results.getString("prod_name"));
resultMap.put("type_desc", results.getString("type_desc"));
resultMap.put("pc_code", results.getString("pc_code"));
resultMap.put("ai_name", results.getString("ai_name"));
resultMap.put("pc_pct", results.getString("pc_pct"));
searchResults.add(new m.wqm.wqm03_pesticideattributes.V1_0.SearchResult(resultMap));
}
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, "SQLException for WQM-3!", ex);
throw new ServiceException("SQL problem.", ex);
}
}
@Override
protected void postProcess() throws ServiceException {
try {
JSONArray resultArr = new JSONArray();
for (m.wqm.wqm03_pesticideattributes.V1_0.SearchResult result : searchResults) {
resultArr.put(result.getResultArray());
}
results().put("pesticides", resultArr, "List of query results");
} catch (JSONException ex) {
LOG.log(Level.SEVERE, "Error in processing the response JSON for WQM-18!", ex);
throw new ServiceException("Error in processing the response JSON.", ex);
}
}
protected static class SearchResult {
private final Map<String, SearchResult.valueDescription> results = new HashMap<String, SearchResult.valueDescription>() {
{
put("reg_no", new SearchResult.valueDescription("", "EPA product registration number (EPA Reg. No.)"));
put("prod_name", new SearchResult.valueDescription("", "Pesticide product name"));
put("type_desc", new SearchResult.valueDescription("", "Pesticide product type)"));
put("pc_code", new SearchResult.valueDescription("", "Pesticide chemical code"));
put("ai_name", new SearchResult.valueDescription("", "Active ingredient name"));
put("pc_pct", new SearchResult.valueDescription("", "Active ingredient percentage"));
}
};
SearchResult(Map<String, String> dbResults) {
SearchResult.valueDescription resultValue;
for (Map.Entry<String, String> result : dbResults.entrySet()) {
resultValue = results.get(result.getKey());
if (null != resultValue) {
resultValue.value = result.getValue();
}
}
}
public JSONArray getResultArray() throws JSONException {
JSONArray ret_val = new JSONArray();
for (Map.Entry<String, SearchResult.valueDescription> result : results.entrySet()) {
ret_val.put(JSONUtils.dataDesc(result.getKey(), result.getValue().value, result.getValue().description));
}
return ret_val;
}
protected static class valueDescription {
String value;
String description;
valueDescription(String value, String description) {
this.value = value;
this.description = description;
}
}
}
protected static class SearchFilter {
private SearchFilter.columnName filterName;
private String searchValue;
private boolean beginsWith;
private boolean contains;
private boolean endsWith;
SearchFilter(JSONArray filterArray) throws ServiceException {
this.filterName = new SearchFilter.columnName();
this.searchValue = "";
this.beginsWith = false;
this.contains = false;
this.endsWith = false;
if (null != filterArray) {
try {
Map<String, JSONObject> filterMap = JSONUtils.preprocess(filterArray);
if (filterMap.containsKey("pp_filter_name")) {
filterName.setName(filterMap.get("pp_filter_name").getString("value"));
if (filterMap.containsKey("pp_filter_value")) {
searchValue = filterMap.get("pp_filter_value").getString("value");
if (filterMap.containsKey("begins_with")) {
beginsWith = filterMap.get("begins_with").getBoolean("value");
if (filterMap.containsKey("contains")) {
contains = filterMap.get("contains").getBoolean("value");
if (filterMap.containsKey("ends_with")) {
endsWith = filterMap.get("ends_with").getBoolean("value");
} else {
throw new ServiceException("Missing 'ends_with' parameter for this filter request. Cannot proceed.");
}
} else {
throw new ServiceException("Missing 'contains' parameter for this filter request. Cannot proceed.");
}
} else {
throw new ServiceException("Missing 'begins_with' parameter for this filter request. Cannot proceed.");
}
} 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);
}
} else {
throw new ServiceException("No input values for the filter. Cannot proceed.");
}
if (!filterName.isValid()) {
throw new ServiceException("Filter name specified is not a valid column name. Cannot proceed.");
}
}
String getWhereClause() {
String ret_val = "( ";
int counter = 0;
if (filterName.isValid() && (beginsWith || contains || endsWith)) {
ret_val += filterName.getName() + " LIKE ";
if (beginsWith) {
ret_val += " '" + searchValue + "%' ";
counter++;
}
if (contains) {
if (counter > 0) {
ret_val += " OR " + filterName.getName() + " LIKE ";
}
ret_val += " '%" + searchValue + "%' ";
counter++;
}
if (endsWith) {
if (counter > 0) {
ret_val += " OR " + filterName.getName() + " LIKE ";
}
ret_val += " '%" + searchValue + "' ";
counter++;
}
ret_val += " ) ";
} else {
ret_val = "";
}
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 {
private final String names[] = {"reg_no", "prod_name", "type_desc", "pc_code", "ai_name"};
private int nameIdx;
columnName() {
nameIdx = -1;
}
public boolean setName(String cName) {
nameIdx = -1;
for (int i = 0; i < 5; i++) {
if (names[i].equals(cName)) {
nameIdx = i;
break;
}
}
return isValid();
}
public String getName() {
String ret_val = "";
if ((nameIdx >= 0) && (nameIdx <= 5)) {
ret_val = names[nameIdx];
}
return ret_val;
}
public boolean isValid() {
return ((nameIdx >= 0) && (nameIdx <= 5));
}
}
}
}