| @@ -10,17 +10,22 @@ |
| 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.sql.*; |
| 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.") |
| +@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) |
| +@Resource(from = DBResources.class) |
| /** |
| * |
| * @author SrinivasReddy kontham |
| @@ -28,47 +33,13 @@ |
| */ |
| public class V1_0 extends ModelDataService { |
| |
| - private ArrayList<Input> components = new ArrayList<>(); // store the set of all input soilcomponents as objects |
| - private ArrayList<Result1> result1 = new ArrayList<>(); // store the result as objects |
| - private int nleach_pract_score = 0; |
| - private int nleach_avoid_pract_score = 0; |
| - private int nleach_control_pract_score = 0; |
| - private int nleach_trap_pract_score = 0; |
| - private int nsurf_pract_score = 0; |
| - private int nsurf_avoid_pract_score = 0; |
| - private int nsurf_control_pract_score = 0; |
| - private int nsurf_trap_pract_score = 0; |
| - private int psurf_pract_score = 0; |
| - private int psurf_avoid_pract_score = 0; |
| - private int psurf_control_pract_score = 0; |
| - private int psurf_trap_pract_score = 0; |
| - private int ssurf_pract_score = 0; |
| - private int ssurf_avoid_pract_score = 0; |
| - private int ssurf_control_pract_score = 0; |
| - private int ssurf_trap_pract_score = 0; |
| + 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 { |
| - nleach_pract_score = 0; |
| - nleach_avoid_pract_score = 0; |
| - nleach_control_pract_score = 0; |
| - nleach_trap_pract_score = 0; |
| - nsurf_pract_score = 0; |
| - nsurf_avoid_pract_score = 0; |
| - nsurf_control_pract_score = 0; |
| - nsurf_trap_pract_score = 0; |
| - psurf_pract_score = 0; |
| - psurf_avoid_pract_score = 0; |
| - psurf_control_pract_score = 0; |
| - psurf_trap_pract_score = 0; |
| - ssurf_pract_score = 0; |
| - ssurf_avoid_pract_score = 0; |
| - ssurf_control_pract_score = 0; |
| - ssurf_trap_pract_score = 0; |
| - |
| components = new ArrayList<>(); |
| result1 = new ArrayList<>(); |
| |
| @@ -82,227 +53,469 @@ |
| 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 Input(AoAid, plan_pract_id, plan_pract_discrim_type, plan_pract_discrim_value)); |
| + components.add(new m.wqm.sednutpractscores.V1_0.Input( |
| + AoAid, plan_pract_id, plan_pract_discrim_type, plan_pract_discrim_value)); |
| } |
| - } catch (ServiceException | JSONException ex) { |
| - LOG.log(Level.SEVERE, "Cannot process the input JSON",ex); |
| - throw new ServiceException("JSONException",ex); |
| - } |
| + } 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 { |
| - |
| - try (Connection conn = getResourceJDBC(WQM_ID); |
| + |
| + 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 (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_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, '') = ''"; |
| + 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 { |
| |
| - } else { |
| - query = "SELECT nut_pract_score FROM 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); |
| - } |
| + 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) + ";"; |
| + } |
| |
| - ResultSet results = statement.executeQuery(query); |
| + 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, '') = '';"; |
| |
| - 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_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_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); |
| - } |
| + } 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) + ";"; |
| + } |
| |
| - results = statement.executeQuery(query); |
| + ResultSet 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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_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_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); |
| - } |
| + 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); |
| + 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; |
| + while (results.next()) { |
| + int ps_control_pract_score = results.getInt("nut_pract_score"); |
| + psurf_control_pract_score += ps_control_pract_score; |
| } |
| - result1.add(new 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 (NumberFormatException | SQLException ex) { |
| - LOG.log(Level.SEVERE, "Cannot process that request",ex); |
| - throw new ServiceException("SQLException",ex); |
| + 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 (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)")); |
| - } |
| + 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, "Could not create result JSON",ex); |
| - throw new ServiceException("JSONException",ex); |
| - } |
| - |
| + 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); |
| + } |
| + |
| } |
| |
| - public class Input { |
| + 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) { |
| + 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; |
| @@ -312,7 +525,7 @@ |
| |
| } |
| |
| - public class Result1 { |
| + static class Result1 { |
| |
| int nleach_pract_score = 0; |
| int nleach_avoid_pract_score = 0; |
| @@ -332,7 +545,15 @@ |
| 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) { |
| + 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; |
| @@ -353,3 +574,341 @@ |
| } |
| } |
| } |
| + |
| +//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); |
| +// } |
| +// |
| +// } |
| +// |