@@ -7,11 +7,11 @@ |
* |
*/ |
import csip.ModelDataService; |
+import csip.ServiceException; |
import csip.utils.JSONUtils; |
import csip.utils.Dates; |
import csip.annotations.Polling; |
import java.sql.Connection; |
-import java.sql.DriverManager; |
import java.sql.ResultSet; |
import java.sql.SQLException; |
import java.sql.Statement; |
@@ -26,8 +26,9 @@ |
import org.codehaus.jettison.json.JSONArray; |
import org.codehaus.jettison.json.JSONObject; |
import java.util.concurrent.TimeUnit; |
+import java.util.logging.Level; |
+import java.util.logging.Logger; |
import org.codehaus.jettison.json.JSONException; |
-import static wqm.utils.WQMTools.getConnection; |
|
@Name("WQM-16: Nutrient Application Management Scores (NutAppMgtScores)") |
@Description("This service computes scores for adjusting the rate, timing, and method of applying nutrients to mitigate nitrogen leaching, and nitrogen and phosphorus runoff loss potential.") |
@@ -44,20 +45,9 @@ |
private String p_soil_test_result = "err"; |
private ArrayList<V1_0.Crop> cropList; |
private String error_msg = ""; |
- private Connection conn = null; |
- private Statement statement = null; |
|
@Override |
protected void preProcess() throws Exception { |
- try { |
- conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
- statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); |
- } catch (SQLException ex) { |
- |
- LOG.info("Did not open database for WQM-16."); |
- LOG.info(ex.getMessage()); |
- this.error_msg = "Could not open database for WQM-16. " + ex.getMessage(); |
- } |
|
if (error_msg.isEmpty()) { |
AoAId = getIntParam("aoa_id", 0); |
@@ -75,7 +65,7 @@ |
|
cropList.add(new V1_0.Crop(JSONUtils.getIntParam(mgtCropId, "mgt_crop_id", 0), JSONUtils.getStringParam(mgtCropId, "crop_plant_date", "err"), |
JSONUtils.getDoubleParam(mgtCropId, "crop_yield", 0), JSONUtils.getStringParam(mgtCropId, "crop_yield_units", "err"), |
- applicationList, p_soil_test_result, statement)); |
+ applicationList, p_soil_test_result)); |
} |
|
ValidateInput(); |
@@ -99,7 +89,9 @@ |
} else { |
result = new ArrayList<>(); |
|
- try { |
+ try ( |
+ Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
+ Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);) { |
for (V1_0.Crop crop : cropList) { |
|
String crop_type = crop.getCropType(); |
@@ -157,8 +149,7 @@ |
int nleach_app_mgt_score = n_app_rate_score + n_app_timing_score + app_method_score; |
int nsurf_app_mgt_score = n_app_rate_score + n_app_timing_score + app_method_score; |
int psurf_app_mgt_score = p_app_rate_score + p_app_timing_score + app_method_score; |
- V1_0.Result result1 = new V1_0.Result(AoAId, nleach_app_mgt_score, nsurf_app_mgt_score, psurf_app_mgt_score, n_app_rate_score, n_app_timing_score, p_app_rate_score, p_app_timing_score, app_method_score); |
- result.add(result1); |
+ result.add(new V1_0.Result(AoAId, nleach_app_mgt_score, nsurf_app_mgt_score, psurf_app_mgt_score, n_app_rate_score, n_app_timing_score, p_app_rate_score, p_app_timing_score, app_method_score)); |
|
conn.close(); |
} catch (SQLException se) { |
@@ -167,13 +158,6 @@ |
ret_val += se.getMessage(); |
} catch (Exception ex) { |
ret_val += ex.getMessage(); |
- } finally { |
- if (statement != null) { |
- statement.close(); |
- } |
- if (conn != null) { |
- conn.close(); |
- } |
} |
} |
return (error_msg.isEmpty() ? EXEC_OK : error_msg); |
@@ -224,7 +208,6 @@ |
private String cropYieldUnits; |
private ArrayList<V1_0.Crop.NutrientApplication> nutrientApplicationList; |
private V1_0.Result calc_result; |
- private Statement statement; |
private String cropType = ""; |
private String error_msg = ""; |
private Boolean multipleNutrientApplication = false; |
@@ -236,12 +219,11 @@ |
private String pSoilTestResult = "None"; |
|
public Crop(int mgtCropId, String cropPlantDate, double cropYield, |
- String cropYieldUnits, JSONArray applicationList, String pSoilTestResult, Statement statement) { |
+ String cropYieldUnits, JSONArray applicationList, String pSoilTestResult) { |
this.mgtCropId = mgtCropId; |
this.cropPlantDate = cropPlantDate; |
this.cropYield = cropYield; |
this.cropYieldUnits = cropYieldUnits; |
- this.statement = statement; |
this.pSoilTestResult = pSoilTestResult; |
|
nutrientApplicationList = new ArrayList<V1_0.Crop.NutrientApplication>(); |
@@ -279,7 +261,9 @@ |
if ((cropType.isEmpty()) && (error_msg.isEmpty())) { |
ResultSet resultSet; |
String query = "SELECT wqm_crop_type FROM wqm_crops WHERE wqm_crop_id=" + mgtCropId + " AND wqm_crop_units='" + cropYieldUnits + "';"; |
- try { |
+ try ( |
+ Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
+ Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);) { |
resultSet = statement.executeQuery(query); |
if (!resultSet.first()) { |
error_msg += "That crop, " + mgtCropId + ", and crop yield units, " + cropYieldUnits + ", was not found in the database"; |
@@ -288,6 +272,8 @@ |
} |
} catch (SQLException ex) { |
error_msg += ex.getMessage(); |
+ } catch (ServiceException ex) { |
+ Logger.getLogger(V1_0.class.getName()).log(Level.SEVERE, null, ex); |
} |
} |
return cropType; |
@@ -314,7 +300,9 @@ |
|
public int[] getNutrientApplicationRateScores() { |
if ((appRateScore[N_APP_RATE_SCORE] < 0) || (appRateScore[P_APP_RATE_SCORE] < 0)) { |
- try { |
+ try ( |
+ Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
+ Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);) { |
if (nutrientApplicationList.isEmpty()) { |
ResultSet resultSet; |
|
@@ -436,6 +424,8 @@ |
} |
} catch (SQLException ex) { |
error_msg += ex.getMessage(); |
+ } catch (ServiceException ex) { |
+ Logger.getLogger(V1_0.class.getName()).log(Level.SEVERE, null, ex); |
} |
} |
|
@@ -596,7 +586,9 @@ |
ResultSet resultSet; |
for (V1_0.Crop.NutrientApplication.Nutrient nutrient : nutrientList) { |
int app_timing_score; |
- try { |
+ try ( |
+ Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
+ Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);) { |
long app_day_diff = Dates.diffInMillis(getCropPlantDate(), getApplicationDate()); |
app_day_diff = TimeUnit.MILLISECONDS.toDays(app_day_diff); |
String query = "SELECT app_mgt_score FROM wqm_nutrient_application_mgt_scores WHERE nutrient='"; |
@@ -642,66 +634,66 @@ |
|
public class Result { |
|
- private int AoAId; |
- private int nleach_app_mgt_score; |
- private int nsurf_app_mgt_score; |
- private int psurf_app_mgt_score; |
- private int n_app_rate_score; |
- private int n_app_timing_score; |
- private int p_app_rate_score; |
- private int p_app_timing_score; |
- private int app_method_score; |
+ private int AoAId; |
+ private int nleach_app_mgt_score; |
+ private int nsurf_app_mgt_score; |
+ private int psurf_app_mgt_score; |
+ private int n_app_rate_score; |
+ private int n_app_timing_score; |
+ private int p_app_rate_score; |
+ private int p_app_timing_score; |
+ private int app_method_score; |
|
- public Result(int AoAId, int nleach_app_mgt_score, int nsurf_app_mgt_score, |
- int psurf_app_mgt_score, int n_app_rate_score, int n_app_timing_score, |
- int p_app_rate_score, int p_app_timing_score, int app_method_score) { |
- this.AoAId = AoAId; |
- this.nleach_app_mgt_score = nleach_app_mgt_score; |
- this.nsurf_app_mgt_score = nsurf_app_mgt_score; |
- this.psurf_app_mgt_score = psurf_app_mgt_score; |
- this.n_app_rate_score = n_app_rate_score; |
- this.n_app_timing_score = n_app_timing_score; |
- this.p_app_rate_score = p_app_rate_score; |
- this.p_app_timing_score = p_app_timing_score; |
- this.app_method_score = app_method_score; |
+ public Result(int AoAId, int nleach_app_mgt_score, int nsurf_app_mgt_score, |
+ int psurf_app_mgt_score, int n_app_rate_score, int n_app_timing_score, |
+ int p_app_rate_score, int p_app_timing_score, int app_method_score) { |
+ this.AoAId = AoAId; |
+ this.nleach_app_mgt_score = nleach_app_mgt_score; |
+ this.nsurf_app_mgt_score = nsurf_app_mgt_score; |
+ this.psurf_app_mgt_score = psurf_app_mgt_score; |
+ this.n_app_rate_score = n_app_rate_score; |
+ this.n_app_timing_score = n_app_timing_score; |
+ this.p_app_rate_score = p_app_rate_score; |
+ this.p_app_timing_score = p_app_timing_score; |
+ this.app_method_score = app_method_score; |
+ } |
+ |
+ //Getter Methods |
+ public int getAoAId() { |
+ return this.AoAId; |
+ } |
+ |
+ public int getNleachAppMgtScore() { |
+ return this.nleach_app_mgt_score; |
+ } |
+ |
+ public int getNsurfAppMgtScore() { |
+ return this.nsurf_app_mgt_score; |
+ } |
+ |
+ public int getPsurfAppMgtScore() { |
+ return this.psurf_app_mgt_score; |
+ } |
+ |
+ public int getnAppRateScore() { |
+ return this.n_app_rate_score; |
+ } |
+ |
+ public int getnAppTimingScore() { |
+ return this.n_app_timing_score; |
+ } |
+ |
+ public int getpAppRateScore() { |
+ return this.p_app_rate_score; |
+ } |
+ |
+ public int getpAppTimingScore() { |
+ return this.p_app_timing_score; |
+ } |
+ |
+ public int getAppMethodScore() { |
+ return this.app_method_score; |
+ } |
} |
|
- //Getter Methods |
- public int getAoAId() { |
- return this.AoAId; |
- } |
- |
- public int getNleachAppMgtScore() { |
- return this.nleach_app_mgt_score; |
- } |
- |
- public int getNsurfAppMgtScore() { |
- return this.nsurf_app_mgt_score; |
- } |
- |
- public int getPsurfAppMgtScore() { |
- return this.psurf_app_mgt_score; |
- } |
- |
- public int getnAppRateScore() { |
- return this.n_app_rate_score; |
- } |
- |
- public int getnAppTimingScore() { |
- return this.n_app_timing_score; |
- } |
- |
- public int getpAppRateScore() { |
- return this.p_app_rate_score; |
- } |
- |
- public int getpAppTimingScore() { |
- return this.p_app_timing_score; |
- } |
- |
- public int getAppMethodScore() { |
- return this.app_method_score; |
- } |
} |
- |
-} |
@@ -1,13 +1,8 @@ |
package m.wqm.nuttechscores; |
|
-/** |
- * |
- * @author SrinivasReddy kontham |
- * @ update Rumpal Sidhu |
- * @ update Sandeep Kasavaraju |
- */ |
import csip.ModelDataService; |
import static csip.ModelDataService.EXEC_OK; |
+import csip.ServiceException; |
import java.util.ArrayList; |
import javax.ws.rs.Path; |
import oms3.annotations.Description; |
@@ -17,42 +12,53 @@ |
import csip.utils.JSONUtils; |
import java.util.Map; |
import java.sql.*; |
-import java.util.concurrent.*; |
+import java.util.logging.Level; |
+import org.codehaus.jettison.json.JSONException; |
|
+/** |
+ * @version 1.0 |
+ * @author SrinivasReddy kontham |
+ * @author Rumpal Sidhu |
+ * @ update Sandeep Kasavaraju |
+ */ |
@Name("WQM-14: Nutrient Technique Scores (NutTechScores)") |
@Description("This service computes scores for techniques applied to mitigate nitrogen leaching, nitrogen runoff, and phosphorus runoff loss potential") |
@Path("m/nut_tech_scores/1.0") |
|
public class V1_0 extends ModelDataService { |
+ |
private ArrayList<V1_0.Input> components; // store the set of all input soilcomponents as objects |
private ArrayList<V1_0.Result1> result1; // store the result as objects |
private int aoaId; |
+ private String error_msg; |
|
@Override |
- // reading the inputs from the json file into input object and placing it in the arraylist |
- protected void preProcess() throws Exception { |
- components = new ArrayList<>(); |
- 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_techn_id = JSONUtils.getIntParam(group, "plan_techn_id", 0); |
- String plan_techn_discrim_type = JSONUtils.getStringParam(group, "plan_techn_discrim_type", ""); |
- String plan_techn_discrim = JSONUtils.getStringParam(group, "plan_techn_discrim", ""); |
- components.add(new V1_0.Input(aoaId, plan_techn_id, plan_techn_discrim_type, plan_techn_discrim)); |
+ protected void preProcess() { |
+ this.error_msg = ""; |
+ this.components = new ArrayList<V1_0.Input>(); |
+ try { |
+ JSONArray groups = getJSONArrayParam("pestcomponents"); |
+ for (int i = 0; i < groups.length(); i++) { |
+ Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i)); |
+ this.aoaId = JSONUtils.getIntParam(group, "AoAid", 0); |
+ int plan_techn_id = JSONUtils.getIntParam(group, "plan_techn_id", 0); |
+ String plan_techn_discrim_type = JSONUtils.getStringParam(group, "plan_techn_discrim_type", null); |
+ String plan_techn_discrim = JSONUtils.getStringParam(group, "plan_techn_discrim", null); |
+ this.components.add(new V1_0.Input(this.aoaId, plan_techn_id, |
+ plan_techn_discrim_type, plan_techn_discrim)); |
+ } |
+ } catch (ServiceException | JSONException e) { |
+ this.error_msg = "Cannot process the request JSON: " + e.getMessage(); |
+ LOG.log(Level.SEVERE, this.error_msg); |
} |
} |
|
@Override |
- protected String process() throws Exception { |
- result1 = new ArrayList<>(); |
- Connection conn = null; |
- Statement statement = null; |
- try { |
- conn = wqm.utils.WQMTools.getConnection("wqm", LOG ); |
+ protected String process() { |
+ result1 = new ArrayList<V1_0.Result1>(); |
+ try (Connection conn = wqm.utils.WQMTools.getConnection("wqm", LOG); |
+ Statement statement = conn.createStatement();) { |
conn.setAutoCommit(false); |
- statement = conn.createStatement(); |
- |
int nleach_techn_score = 0; |
int nleach_avoid_techn_score = 0; |
int nleach_control_techn_score = 0; |
@@ -67,154 +73,192 @@ |
int psurf_trap_techn_score = 0; |
|
for (V1_0.Input ip : components) { |
- String query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ip.plan_techn_id+" AND wqm_concern='Nitrogen in Ground Water';"; |
+ String q1; |
+ String q2; |
+ q1 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type |
+ + "' AND tech_discrim ='" + ip.plan_techn_discrim + "' ;"; |
+ q2 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND mode_of_action = %s" |
+ + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type |
+ + "' AND tech_discrim ='" + ip.plan_techn_discrim + "' ;"; |
+ |
+ if (ip.plan_techn_discrim_type != null && ip.plan_techn_discrim != null) { |
+ } else if (ip.plan_techn_discrim_type == null && ip.plan_techn_discrim == null) { |
+ q1 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND tech_discrim_type IS NULL" |
+ + " AND tech_discrim IS NULL;"; |
+ q2 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND mode_of_action = %s" |
+ + " AND tech_discrim_type IS NULL" |
+ + " AND tech_discrim IS NULL;"; |
+ } else if (ip.plan_techn_discrim_type == null) { |
+ q1 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND tech_discrim_type IS NULL" |
+ + " AND tech_discrim ='" + ip.plan_techn_discrim + "' ;"; |
+ q2 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND mode_of_action = %s" |
+ + " AND tech_discrim_type IS NULL" |
+ + " AND tech_discrim ='" + ip.plan_techn_discrim + "' ;"; |
+ } else if (ip.plan_techn_discrim == null) { |
+ q1 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type |
+ + "' AND tech_discrim IS NULL;"; |
+ q2 = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores " |
+ + "WHERE nutrient_technique_id =" + ip.plan_techn_id |
+ + " AND wqm_concern = %s" |
+ + " AND mode_of_action = %s" |
+ + " AND tech_discrim_type ='" + ip.plan_techn_discrim_type |
+ + "' AND tech_discrim IS NULL;"; |
+ |
+ } |
+ |
+ String query = String.format(q1, "'Nitrogen in Ground Water'"); |
ResultSet resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int techn_score = resultset.getInt("nut_tech_score"); |
nleach_techn_score += techn_score; |
} |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id="+ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action ='Avoid';"; |
+ |
+ query = String.format(q2, "'Nitrogen in Ground Water'", "'Avoid'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int avoid_techn_score = resultset.getInt("nut_tech_score"); |
nleach_avoid_techn_score += avoid_techn_score; |
} |
|
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id="+ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action = 'Control';"; |
+ query = String.format(q2, "'Nitrogen in Ground Water'", "'Control'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int control_techn_score = resultset.getInt("nut_tech_score"); |
nleach_control_techn_score += control_techn_score; |
} |
|
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Ground Water' AND mode_of_action = 'Trap';"; |
+ query = String.format(q2, "'Nitrogen in Ground Water'", "'Trap'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int trap_techn_score = resultset.getInt("nut_tech_score"); |
nleach_trap_techn_score += trap_techn_score; |
- |
+ |
} |
|
// #Compute nutrient technique mitigation scores for Nitrogen in Surface Water concern and increment total scores |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Surface Water';"; |
+ query = String.format(q1, "'Nitrogen in Surface Water'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int techn_score = resultset.getInt("nut_tech_score"); |
nsurf_techn_score += techn_score; |
} |
|
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Surface Water' AND mode_of_action = 'Avoid';"; |
+ query = String.format(q2, "'Nitrogen in Surface Water'", "'Avoid'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int avoid_techn_score = resultset.getInt("nut_tech_score"); |
nsurf_avoid_techn_score += avoid_techn_score; |
} |
|
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Nitrogen in Surface Water' AND mode_of_action= 'Control';"; |
+ query = String.format(q2, "'Nitrogen in Surface Water'", "'Control'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int control_techn_score = resultset.getInt("nut_tech_score"); |
nsurf_control_techn_score += control_techn_score; |
} |
|
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id = " + ip.plan_techn_id+ " AND wqm_concern= 'Nitrogen in Surface Water' AND mode_of_action= 'Trap';"; |
+ query = String.format(q2, "'Nitrogen in Surface Water'", "'Trap'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int trap_techn_score = resultset.getInt("nut_tech_score"); |
nsurf_trap_techn_score += trap_techn_score; |
} |
|
- //#Compute nutrient technique mitigation scores for Phosphorus in Surface Water concern and increment total scores |
- if(ip.plan_techn_discrim.isEmpty()){ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water';"; |
- } |
- else{ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND tech_discrim_type ='"+ ip.plan_techn_discrim_type+"' AND tech_discrim ='"+ip.plan_techn_discrim+"' ;"; |
- } |
+ //#Compute nutrient technique mitigation scores for Phosphorus in Surface Water concern and increment total scores |
+ query = String.format(q1, "'Phosphorus in Surface Water'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int techn_score = resultset.getInt("nut_tech_score"); |
psurf_techn_score += techn_score; |
} |
|
- if(ip.plan_techn_discrim.isEmpty()){ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = 'Avoid';"; |
- } |
- else{ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND tech_discrim_type ='"+ ip.plan_techn_discrim_type+"' AND tech_discrim ='"+ip.plan_techn_discrim+"' AND mode_of_action = 'Avoid';"; |
- } |
- // query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id = " + ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = 'Avoid';"; |
+ query = String.format(q2, "'Phosphorus in Surface Water'", "'Avoid'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int avoid_techn_score = resultset.getInt("nut_tech_score"); |
psurf_avoid_techn_score += avoid_techn_score; |
} |
|
- if(ip.plan_techn_discrim.isEmpty()){ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = 'Control';"; |
- } |
- else{ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND tech_discrim_type ='"+ ip.plan_techn_discrim_type+"' AND tech_discrim ='"+ip.plan_techn_discrim+"' AND mode_of_action = 'Control';"; |
- } |
- //query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern ='Phosphorus in Surface Water' AND mode_of_action ='Control';"; |
+ query = String.format(q2, "'Phosphorus in Surface Water'", "'Control'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int control_techn_score = resultset.getInt("nut_tech_score"); |
psurf_control_techn_score += control_techn_score; |
} |
|
- if(ip.plan_techn_discrim.isEmpty()){ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND mode_of_action = 'Trap';"; |
- } |
- else{ |
- query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id ="+ ip.plan_techn_id+" AND wqm_concern = 'Phosphorus in Surface Water' AND tech_discrim_type ='"+ ip.plan_techn_discrim_type+"' AND tech_discrim ='"+ip.plan_techn_discrim+"' AND mode_of_action = 'Trap';"; |
- } |
- // query = "SELECT nut_tech_score FROM wqm_nutrient_technique_scores WHERE nutrient_technique_id =" +ip.plan_techn_id+" AND wqm_concern ='Phosphorus in Surface Water' AND mode_of_action ='Trap';"; |
+ query = String.format(q2, "'Phosphorus in Surface Water'", "'Trap'"); |
resultset = statement.executeQuery(query); |
while (resultset.next()) { |
int trap_techn_score = resultset.getInt("nut_tech_score"); |
psurf_trap_techn_score += trap_techn_score; |
} |
} |
- result1.add(new V1_0.Result1(aoaId, nleach_techn_score, nsurf_techn_score, psurf_techn_score, nleach_avoid_techn_score, nleach_control_techn_score, nleach_trap_techn_score, nsurf_avoid_techn_score, nsurf_control_techn_score, nsurf_trap_techn_score, psurf_avoid_techn_score, psurf_control_techn_score, psurf_trap_techn_score)); |
+ result1.add(new V1_0.Result1(aoaId, nleach_techn_score, |
+ nsurf_techn_score, psurf_techn_score, nleach_avoid_techn_score, |
+ nleach_control_techn_score, nleach_trap_techn_score, |
+ nsurf_avoid_techn_score, nsurf_control_techn_score, |
+ nsurf_trap_techn_score, psurf_avoid_techn_score, |
+ psurf_control_techn_score, psurf_trap_techn_score)); |
|
- } catch (SQLException se) { |
- LOG.info("Did not open database for WQM-14"); |
+ } catch (ServiceException | SQLException se) { |
+ this.error_msg += "Database connection error: " + se.getMessage(); |
+ LOG.info("Did not open database for WQM-14!"); |
LOG.info(se.getMessage()); |
- } finally { |
- if (statement != null) { |
- statement.close(); |
- } |
- if (conn != null) { |
- conn.close(); |
- } |
} |
- return EXEC_OK; |
+ |
+ return (this.error_msg.isEmpty() ? EXEC_OK : this.error_msg); |
} |
|
@Override |
//writing the results back to JSON |
- protected void postProcess() throws Exception { |
- JSONArray result1Arr = new JSONArray(); |
- for (V1_0.Result1 rs1 : result1) { |
- JSONArray tmpArr = new JSONArray(); |
- tmpArr.put(JSONUtils.dataDesc("AoAId", rs1.AoAid, "Area of Analysis Identifier")); |
- tmpArr.put(JSONUtils.dataDesc("nleach_techn_score", rs1.nleach_techn_score, "nutrient management technique mitigation score for nitrogen in ground water concern")); |
- tmpArr.put(JSONUtils.dataDesc("nsurf_techn_score", rs1.nsurf_techn_score, "nutrient management technique mitigation score for nitrogen in surface water concern")); |
- tmpArr.put(JSONUtils.dataDesc("psurf_techn_score", rs1.psurf_techn_score, "nutrient management technique mitigation score for phosphorus in surface water concern")); |
- tmpArr.put(JSONUtils.dataDesc("nleach_avoid_techn_score", rs1.nleach_avoid_techn_score, "integer, nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to groundwater")); |
- tmpArr.put(JSONUtils.dataDesc("nleach_control_techn_score", rs1.nleach_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to groundwater")); |
- tmpArr.put(JSONUtils.dataDesc("nleach_trap_techn_score", rs1.nleach_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from groundwater")); |
- tmpArr.put(JSONUtils.dataDesc("nsurf_avoid_techn_score", rs1.nsurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to surface water")); |
- tmpArr.put(JSONUtils.dataDesc("nsurf_control_techn_score", rs1.nsurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to surface water runoff")); |
- tmpArr.put(JSONUtils.dataDesc("nsurf_trap_techn_score", rs1.nsurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from surface water")); |
- tmpArr.put(JSONUtils.dataDesc("psurf_avoid_techn_score", rs1.psurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess phosphorus use or application and loss to surface water")); |
- tmpArr.put(JSONUtils.dataDesc("psurf_control_techn_score", rs1.psurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field phosphorus losses to surface water runoff")); |
- tmpArr.put(JSONUtils.dataDesc("psurf_trap_techn_score", rs1.psurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess phophorus and keeping it from surface water")); |
- result1Arr.put(JSONUtils.dataDesc("Nutrient Technique Scores", tmpArr, "AoANutScores")); |
+ protected void postProcess() { |
+ try { |
+ JSONArray result1Arr = new JSONArray(); |
+ for (V1_0.Result1 rs1 : result1) { |
+ JSONArray tmpArr = new JSONArray(); |
+ tmpArr.put(JSONUtils.dataDesc("AoAId", rs1.AoAid, "Area of Analysis Identifier")); |
+ tmpArr.put(JSONUtils.dataDesc("nleach_techn_score", rs1.nleach_techn_score, "nutrient management technique mitigation score for nitrogen in ground water concern")); |
+ tmpArr.put(JSONUtils.dataDesc("nsurf_techn_score", rs1.nsurf_techn_score, "nutrient management technique mitigation score for nitrogen in surface water concern")); |
+ tmpArr.put(JSONUtils.dataDesc("psurf_techn_score", rs1.psurf_techn_score, "nutrient management technique mitigation score for phosphorus in surface water concern")); |
+ tmpArr.put(JSONUtils.dataDesc("nleach_avoid_techn_score", rs1.nleach_avoid_techn_score, "integer, nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to groundwater")); |
+ tmpArr.put(JSONUtils.dataDesc("nleach_control_techn_score", rs1.nleach_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to groundwater")); |
+ tmpArr.put(JSONUtils.dataDesc("nleach_trap_techn_score", rs1.nleach_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from groundwater")); |
+ tmpArr.put(JSONUtils.dataDesc("nsurf_avoid_techn_score", rs1.nsurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess nitrogen use or application and loss to surface water")); |
+ tmpArr.put(JSONUtils.dataDesc("nsurf_control_techn_score", rs1.nsurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field nitrogen losses to surface water runoff")); |
+ tmpArr.put(JSONUtils.dataDesc("nsurf_trap_techn_score", rs1.nsurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess nitrogen and keeping it from surface water")); |
+ tmpArr.put(JSONUtils.dataDesc("psurf_avoid_techn_score", rs1.psurf_avoid_techn_score, "nutrient management technique mitigation score for avoiding excess phosphorus use or application and loss to surface water")); |
+ tmpArr.put(JSONUtils.dataDesc("psurf_control_techn_score", rs1.psurf_control_techn_score, "nutrient management technique mitigation score for controlling in-field phosphorus losses to surface water runoff")); |
+ tmpArr.put(JSONUtils.dataDesc("psurf_trap_techn_score", rs1.psurf_trap_techn_score, "nutrient management technique mitigation score for trapping excess phophorus and keeping it from surface water")); |
+ result1Arr.put(JSONUtils.dataDesc("Nutrient Technique Scores", tmpArr, "AoANutScores")); |
+ } |
+ putResult("operation", result1Arr); |
+ } catch (JSONException e) { |
+ this.error_msg = "Cannot process the response JSON: " + e.getMessage(); |
+ LOG.info(e.getMessage()); |
} |
- putResult("operation", result1Arr); |
} |
+ |
public class Input { |
|
int AoAid; |
@@ -223,7 +267,7 @@ |
String plan_techn_discrim; |
|
public Input(int AoAid, int plan_techn_id, String plan_techn_discrim_type, |
- String plan_techn_discrim) { |
+ String plan_techn_discrim) { |
this.AoAid = AoAid; |
this.plan_techn_id = plan_techn_id; |
this.plan_techn_discrim_type = plan_techn_discrim_type; |
@@ -231,6 +275,7 @@ |
} |
|
} |
+ |
public class Result1 { |
|
int nleach_techn_score = 0; |
@@ -247,12 +292,12 @@ |
int psurf_trap_techn_score = 0; |
int AoAid; |
|
- public Result1(int AoAid, int nleach_techn_score, int nsurf_techn_score, |
- int psurf_techn_score, int nleach_avoid_techn_score, |
- int nleach_control_techn_score, int nleach_trap_techn_score, |
- int nsurf_avoid_techn_score, int nsurf_control_techn_score, |
- int nsurf_trap_techn_score, int psurf_avoid_techn_score, |
- int psurf_control_techn_score, int psurf_trap_techn_score) { |
+ public Result1(int AoAid, int nleach_techn_score, int nsurf_techn_score, |
+ int psurf_techn_score, int nleach_avoid_techn_score, |
+ int nleach_control_techn_score, int nleach_trap_techn_score, |
+ int nsurf_avoid_techn_score, int nsurf_control_techn_score, |
+ int nsurf_trap_techn_score, int psurf_avoid_techn_score, |
+ int psurf_control_techn_score, int psurf_trap_techn_score) { |
this.nleach_techn_score = nleach_techn_score; |
this.nleach_avoid_techn_score = nleach_avoid_techn_score; |
this.nleach_control_techn_score = nleach_control_techn_score; |