V1_0.java [src/java/m/wqm/sednutpractscores] Revision: 6594230a15bc3941ab7782ea36938c5dc056d0d4 Date: Thu May 26 14:41:06 MDT 2016
package m.wqm.sednutpractscores;
import csip.ModelDataService;
import csip.ServiceException;
import csip.annotations.Resource;
import java.util.ArrayList;
import javax.ws.rs.Path;
import oms3.annotations.Description;
import oms3.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import csip.utils.JSONUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.logging.Level;
import org.codehaus.jettison.json.JSONException;
import wqm.utils.DBResources;
import static wqm.utils.DBResources.WQM_ID;
@Name("WQM-15: Sediment and Nutrient Practice Scores (SedNutPractScores)")
@Description("This service computes scores for conservation practices applied "
+ "to mitigate nitrogen leaching, sediment runoff, nitrogen runoff, "
+ "and phosphorus runoff loss potential.")
@Path("m/nut_pract_scores/1.0")
@Resource(from = DBResources.class)
/**
*
* @author SrinivasReddy kontham
* @author Shaun Case
*/
public class V1_0 extends ModelDataService {
private ArrayList<m.wqm.sednutpractscores.V1_0.Input> components;
private ArrayList<m.wqm.sednutpractscores.V1_0.Result1> result1;
private int AoAid;
@Override
protected void preProcess() throws ServiceException {
components = new ArrayList<>();
result1 = new ArrayList<>();
try {
JSONArray groups = getJSONArrayParam("pestcomponents");
for (int i = 0; i < groups.length(); i++) {
Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i));
AoAid = JSONUtils.getIntParam(group, "AoAid", 0);
int plan_pract_id = JSONUtils.getIntParam(group, "plan_practice_id", 0);
String plan_pract_discrim_type = JSONUtils.getStringParam(group, "plan_practice_discrim_type", "err");
String plan_pract_discrim_value = JSONUtils.getStringParam(group, "plan_practice_discrim_value", "err");
components.add(new m.wqm.sednutpractscores.V1_0.Input(
AoAid, plan_pract_id, plan_pract_discrim_type, plan_pract_discrim_value));
}
} catch (JSONException ex) {
LOG.log(Level.SEVERE, "Error in processing the request JSON for WQM-15!", ex);
throw new ServiceException("Error in processing the request JSON.", ex);
}
}
@Override
protected void doProcess() throws Exception {
int nleach_pract_score = 0;
int nleach_avoid_pract_score = 0;
int nleach_control_pract_score = 0;
int nleach_trap_pract_score = 0;
int nsurf_pract_score = 0;
int nsurf_avoid_pract_score = 0;
int nsurf_control_pract_score = 0;
int nsurf_trap_pract_score = 0;
int psurf_pract_score = 0;
int psurf_avoid_pract_score = 0;
int psurf_control_pract_score = 0;
int psurf_trap_pract_score = 0;
int ssurf_pract_score = 0;
int ssurf_avoid_pract_score = 0;
int ssurf_control_pract_score = 0;
int ssurf_trap_pract_score = 0;
try (Connection conn = getResourceJDBC(WQM_ID);
Statement statement = conn.createStatement()) {
for (m.wqm.sednutpractscores.V1_0.Input ip : components) {
String q1 = null;
String q2 = null;
if (ip.plan_practice_discrim_type.isEmpty()) {
q1 = "SELECT nut_pract_score "
+ "FROM wqm.wqm_sediment_nutrient_practice_scores "
+ "WHERE practice_id = " + ip.plan_practice_id
+ " AND wqm_concern = '-wqm_concern' "
+ "AND mode_of_action = '-mode_of_action' AND COALESCE(pract_discrim_type, '') = '';";
} else {
q2 = "SELECT nut_pract_score "
+ "FROM wqm.wqm_sediment_nutrient_practice_scores "
+ "WHERE practice_id = " + ip.plan_practice_id
+ " AND wqm_concern = '-wqm_concern' "
+ "AND mode_of_action = '-mode_of_action' "
+ "AND CAST(min_pract_discrim AS int) <= "
+ Integer.parseInt(ip.plan_practice_discrim_value)
+ " AND CAST(max_pract_discrim AS int) > "
+ Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
String query;
/*Compute practice mitigation scores for Nitrogen in Ground Water and
increment total scores */
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
ResultSet results = statement.executeQuery(query);
while (results.next()) {
int nl_avoid_pract_score = results.getInt("nut_pract_score");
nleach_avoid_pract_score += nl_avoid_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int nl_control_pract_score = results.getInt("nut_pract_score");
nleach_control_pract_score += nl_control_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Ground Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int nl_trap_pract_score = results.getInt("nut_pract_score");
nleach_trap_pract_score += nl_trap_pract_score;
}
nleach_pract_score = nleach_trap_pract_score + nleach_control_pract_score + nleach_avoid_pract_score;
/* Compute practice mitigation scores for Sediment in Surface Water
and increment total scores */
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int sd_avoid_pract_score = results.getInt("nut_pract_score");
ssurf_avoid_pract_score += sd_avoid_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + "AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int sd_control_pract_score = results.getInt("nut_pract_score");
ssurf_control_pract_score += sd_control_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Sediment in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int sd_trap_pract_score = results.getInt("nut_pract_score");
ssurf_trap_pract_score += sd_trap_pract_score;
}
ssurf_pract_score = ssurf_trap_pract_score + ssurf_control_pract_score + ssurf_avoid_pract_score;
// #Increment practice mitigation scores for Nitrogen in Surface Water and increment total scores
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ns_avoid_pract_score = results.getInt("nut_pract_score");
nsurf_avoid_pract_score += ns_avoid_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ns_control_pract_score = results.getInt("nut_pract_score");
nsurf_control_pract_score += ns_control_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Nitrogen in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + "AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Trap' AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ns_trap_pract_score = results.getInt("nut_pract_score");
nsurf_trap_pract_score += ns_trap_pract_score;
}
nsurf_pract_score = nsurf_trap_pract_score + nsurf_control_pract_score + nsurf_avoid_pract_score;
// #Increment practice mitigation scores for Phosphorus in Surface Water and increment total scores
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Avoid");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + "AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ps_avoid_pract_score = results.getInt("nut_pract_score");
psurf_avoid_pract_score += ps_avoid_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Control");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id + " "
// + "AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ps_control_pract_score = results.getInt("nut_pract_score");
psurf_control_pract_score += ps_control_pract_score;
}
if (ip.plan_practice_discrim_type.isEmpty()) {
query = q1.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
} else {
query = q2.replace("-wqm_concern", "Phosphorus in Surface Water")
.replace("-mode_of_action", "Trap");
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + "AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
}
results = statement.executeQuery(query);
while (results.next()) {
int ps_trap_pract_score = results.getInt("nut_pract_score");
psurf_trap_pract_score += ps_trap_pract_score;
}
psurf_pract_score = psurf_trap_pract_score + psurf_control_pract_score + psurf_avoid_pract_score;
}
result1.add(new m.wqm.sednutpractscores.V1_0.Result1(AoAid, nleach_pract_score, ssurf_pract_score,
nsurf_pract_score, psurf_pract_score, nleach_avoid_pract_score,
nleach_control_pract_score, nleach_trap_pract_score,
ssurf_avoid_pract_score, ssurf_control_pract_score,
ssurf_trap_pract_score, nsurf_avoid_pract_score,
nsurf_control_pract_score, nsurf_trap_pract_score,
psurf_avoid_pract_score, psurf_control_pract_score,
psurf_trap_pract_score));
} catch (SQLException ex) {
LOG.log(Level.SEVERE, "SQLException for WQM-15!", ex);
throw new ServiceException("SQL problem.", ex);
} catch (NumberFormatException ex) {
LOG.log(Level.SEVERE, "NumberFormatException for WQM-15!", ex);
throw new ServiceException("NumberFormatException: "
+ "Please check the value of plan_practice_discrim_value.", ex);
}
}
@Override
protected void postProcess() throws Exception {
try {
JSONArray result1Arr = new JSONArray();
for (m.wqm.sednutpractscores.V1_0.Result1 rs1 : result1) {
JSONArray tmpArr = new JSONArray();
tmpArr.put(JSONUtils.dataDesc("AoAId", AoAid, "Area of Analysis Identifier"));
tmpArr.put(JSONUtils.dataDesc("nleach_pract_score", rs1.nleach_pract_score, " Nitrogen Leaching Practice Mitigation Score"));
tmpArr.put(JSONUtils.dataDesc("nsurf_pract_score", rs1.nsurf_pract_score, "Sediment Runoff Practice Mitigation Score"));
tmpArr.put(JSONUtils.dataDesc("ssurf_pract_score", rs1.ssurf_pract_score, "Nitrogen Runoff Practice Mitigation Score"));
tmpArr.put(JSONUtils.dataDesc("psurf_pract_score", rs1.psurf_pract_score, "Phosphorus Runoff Practice Mitigation Score"));
tmpArr.put(JSONUtils.dataDesc("nleach_avoid_pract_score", rs1.nleach_avoid_pract_score, "Nitrogen Leaching Practice Score (Avoid)"));
tmpArr.put(JSONUtils.dataDesc("nleach_control_pract_score", rs1.nleach_control_pract_score, "Nitrogen Leaching Practice Score (Control)"));
tmpArr.put(JSONUtils.dataDesc("nleach_trap_pract_score", rs1.nleach_trap_pract_score, "Nitrogen Leaching Practice Score (Trap)"));
tmpArr.put(JSONUtils.dataDesc("ssurf_avoid_pract_score", rs1.ssurf_avoid_pract_score, "Sediment Runoff Practice Score (Avoid)"));
tmpArr.put(JSONUtils.dataDesc("ssurf_control_pract_score", rs1.ssurf_control_pract_score, "Sediment Runoff Practice Score (Control)"));
tmpArr.put(JSONUtils.dataDesc("ssurf_trap_pract_score", rs1.ssurf_trap_pract_score, "Sediment Runoff Practice Score (Trap)"));
tmpArr.put(JSONUtils.dataDesc("nsurf_avoid_pract_score", rs1.nsurf_avoid_pract_score, "Nitrogen Runoff Practice Score (Avoid)"));
tmpArr.put(JSONUtils.dataDesc("nsurf_control_pract_score", rs1.nsurf_control_pract_score, "Nitrogen Runoff Practice Score (Control)"));
tmpArr.put(JSONUtils.dataDesc("nsurf_trap_pract_score", rs1.nsurf_trap_pract_score, "Nitrogen Runoff Practice Score (Trap)"));
tmpArr.put(JSONUtils.dataDesc("psurf_avoid_pract_score", rs1.psurf_avoid_pract_score, "Phosphorus Runoff Practice Score (Avoid)"));
tmpArr.put(JSONUtils.dataDesc("psurf_control_pract_score", rs1.psurf_control_pract_score, "Phosphorus Runoff Practice Score (Control)"));
tmpArr.put(JSONUtils.dataDesc("psurf_trap_pract_score", rs1.psurf_trap_pract_score, "Phosphorus Runoff Practice Score (Trap)"));
result1Arr.put(JSONUtils.dataDesc("(AoASedNutPractScore)", tmpArr, "(AoASedNutPractScore)"));
}
putResult("operation", result1Arr);
} catch (JSONException ex) {
LOG.log(Level.SEVERE, "Error in processing the response JSON for WQM-15!", ex);
throw new ServiceException("Error in processing the response JSON.", ex);
}
}
static class Input {
int AoAid;
int plan_practice_id;
String plan_practice_discrim_type;
String plan_practice_discrim_value;
public Input(int AoAid, int plan_practice_id,
String plan_practice_discrim_type, String plan_practice_discrim_value) {
this.AoAid = AoAid;
this.plan_practice_id = plan_practice_id;
this.plan_practice_discrim_type = plan_practice_discrim_type;
this.plan_practice_discrim_value = plan_practice_discrim_value;
}
}
static class Result1 {
int nleach_pract_score = 0;
int nleach_avoid_pract_score = 0;
int nleach_control_pract_score = 0;
int nleach_trap_pract_score = 0;
int nsurf_pract_score = 0;
int nsurf_avoid_pract_score = 0;
int nsurf_control_pract_score = 0;
int nsurf_trap_pract_score = 0;
int psurf_pract_score = 0;
int psurf_avoid_pract_score = 0;
int psurf_control_pract_score = 0;
int psurf_trap_pract_score = 0;
int ssurf_pract_score = 0;
int ssurf_avoid_pract_score = 0;
int ssurf_control_pract_score = 0;
int ssurf_trap_pract_score = 0;
int AoAid;
public Result1(int AoAid, int nleach_pract_score,
int ssurf_pract_score, int nsurf_pract_score,
int psurf_pract_score, int nleach_avoid_pract_score,
int nleach_control_pract_score, int nleach_trap_pract_score,
int ssurf_avoid_pract_score, int ssurf_control_pract_score,
int ssurf_trap_pract_score, int nsurf_avoid_pract_score,
int nsurf_control_pract_score, int nsurf_trap_pract_score,
int psurf_avoid_pract_score, int psurf_control_pract_score,
int psurf_trap_pract_score) {
this.nleach_pract_score = nleach_pract_score;
this.nleach_avoid_pract_score = nleach_avoid_pract_score;
this.nleach_control_pract_score = nleach_control_pract_score;
this.nleach_trap_pract_score = nleach_trap_pract_score;
this.nsurf_pract_score = nsurf_pract_score;
this.nsurf_avoid_pract_score = nsurf_avoid_pract_score;
this.nsurf_control_pract_score = nsurf_control_pract_score;
this.nsurf_trap_pract_score = nsurf_trap_pract_score;
this.psurf_pract_score = psurf_pract_score;
this.psurf_avoid_pract_score = psurf_avoid_pract_score;
this.psurf_control_pract_score = psurf_control_pract_score;
this.psurf_trap_pract_score = psurf_trap_pract_score;
this.ssurf_pract_score = ssurf_pract_score;
this.ssurf_avoid_pract_score = ssurf_avoid_pract_score;
this.ssurf_control_pract_score = ssurf_control_pract_score;
this.ssurf_trap_pract_score = ssurf_trap_pract_score;
this.AoAid = AoAid;
}
}
}
//protected void doProcess() throws Exception {
//
// int nleach_pract_score = 0;
// int nleach_avoid_pract_score = 0;
// int nleach_control_pract_score = 0;
// int nleach_trap_pract_score = 0;
// int nsurf_pract_score = 0;
// int nsurf_avoid_pract_score = 0;
// int nsurf_control_pract_score = 0;
// int nsurf_trap_pract_score = 0;
// int psurf_pract_score = 0;
// int psurf_avoid_pract_score = 0;
// int psurf_control_pract_score = 0;
// int psurf_trap_pract_score = 0;
// int ssurf_pract_score = 0;
// int ssurf_avoid_pract_score = 0;
// int ssurf_control_pract_score = 0;
// int ssurf_trap_pract_score = 0;
//
// try (Connection conn = getResourceJDBC(WQM_ID);
// Statement statement = conn.createStatement()) {
// for (m.wqm.sednutpractscores.V1_0.Input ip : components) {
// String query;
// /*Compute practice mitigation scores for Nitrogen in Ground Water and
// increment total scores */
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
//
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// ResultSet results = statement.executeQuery(query);
//
// while (results.next()) {
// int nl_avoid_pract_score = results.getInt("nut_pract_score");
// nleach_avoid_pract_score += nl_avoid_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int nl_control_pract_score = results.getInt("nut_pract_score");
// nleach_control_pract_score += nl_control_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Ground Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int nl_trap_pract_score = results.getInt("nut_pract_score");
// nleach_trap_pract_score += nl_trap_pract_score;
// }
// nleach_pract_score = nleach_trap_pract_score + nleach_control_pract_score + nleach_avoid_pract_score;
// /* Compute practice mitigation scores for Sediment in Surface Water
// and increment total scores */
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int sd_avoid_pract_score = results.getInt("nut_pract_score");
// ssurf_avoid_pract_score += sd_avoid_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + "AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int sd_control_pract_score = results.getInt("nut_pract_score");
// ssurf_control_pract_score += sd_control_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Sediment in Surface Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int sd_trap_pract_score = results.getInt("nut_pract_score");
// ssurf_trap_pract_score += sd_trap_pract_score;
// }
// ssurf_pract_score = ssurf_trap_pract_score + ssurf_control_pract_score + ssurf_avoid_pract_score;
// // #Increment practice mitigation scores for Nitrogen in Surface Water and increment total scores
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ns_avoid_pract_score = results.getInt("nut_pract_score");
// nsurf_avoid_pract_score += ns_avoid_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ns_control_pract_score = results.getInt("nut_pract_score");
// nsurf_control_pract_score += ns_control_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + "AND wqm_concern = 'Nitrogen in Surface Water' "
// + "AND mode_of_action = 'Trap' AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ns_trap_pract_score = results.getInt("nut_pract_score");
// nsurf_trap_pract_score += ns_trap_pract_score;
// }
// nsurf_pract_score = nsurf_trap_pract_score + nsurf_control_pract_score + nsurf_avoid_pract_score;
// // #Increment practice mitigation scores for Phosphorus in Surface Water and increment total scores
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Avoid' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Avoid' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + "AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ps_avoid_pract_score = results.getInt("nut_pract_score");
// psurf_avoid_pract_score += ps_avoid_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Control' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id + " "
// + "AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Control' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + " AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ps_control_pract_score = results.getInt("nut_pract_score");
// psurf_control_pract_score += ps_control_pract_score;
// }
// if (ip.plan_practice_discrim_type.isEmpty()) {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Trap' AND COALESCE(pract_discrim_type, '') = '';";
// } else {
// query = "SELECT nut_pract_score "
// + "FROM wqm.wqm_sediment_nutrient_practice_scores "
// + "WHERE practice_id = " + ip.plan_practice_id
// + " AND wqm_concern = 'Phosphorus in Surface Water' "
// + "AND mode_of_action = 'Trap' "
// + "AND CAST(min_pract_discrim AS int) <= "
// + Integer.parseInt(ip.plan_practice_discrim_value)
// + "AND CAST(max_pract_discrim AS int) > "
// + Integer.parseInt(ip.plan_practice_discrim_value) + ";";
// }
//
// results = statement.executeQuery(query);
//
// while (results.next()) {
// int ps_trap_pract_score = results.getInt("nut_pract_score");
// psurf_trap_pract_score += ps_trap_pract_score;
// }
// psurf_pract_score = psurf_trap_pract_score + psurf_control_pract_score + psurf_avoid_pract_score;
// }
// result1.add(new m.wqm.sednutpractscores.V1_0.Result1(AoAid, nleach_pract_score, ssurf_pract_score,
// nsurf_pract_score, psurf_pract_score, nleach_avoid_pract_score,
// nleach_control_pract_score, nleach_trap_pract_score,
// ssurf_avoid_pract_score, ssurf_control_pract_score,
// ssurf_trap_pract_score, nsurf_avoid_pract_score,
// nsurf_control_pract_score, nsurf_trap_pract_score,
// psurf_avoid_pract_score, psurf_control_pract_score,
// psurf_trap_pract_score));
// } catch (SQLException ex) {
// LOG.log(Level.SEVERE, "SQLException for WQM-15!", ex);
// throw new ServiceException("SQL problem.", ex);
// }
//
// }
//