Displaying differences for changeset
 
display as  

src/java/m/wqm/nutappmgtscores/V1_0.java

@@ -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;
-    }
 }
-
-}

src/java/m/wqm/nuttechscores/V1_0.java

@@ -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;

src/java/m/wqm/nuttechscores/V1_0.json

@@ -14,17 +14,117 @@
                     {
                         "name": "plan_techn_id",
                         "value": 1,
-                        "Description": "nutrient management technique identifier corresponding to nutrient_technique_score_id value in WQM data mart"
+                        "Description": "Nutrient management technique identifier"
                     },
                     {
                         "name": "plan_techn_discrim_type",
-                        "value": "",
-                        "Description": "type of discriminator for varying nutrient management technique scores; value is 'soil test result' otherwise NULL"
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
                     },
                     {
                         "name": "plan_techn_discrim",
-                        "value": "",
-                        "Description": "value of the discriminator:  HIGH,MEDIUM, LOW, NO SOIL TEST"
+                        "Description": "Value of the discriminator"
+                    }
+                ],
+                [
+                    {
+                        "name": "AoAid",
+                        "value": 1,
+                        "Description": "Area of Analysis Identifier"
+                    },
+                    {
+                        "name": "plan_techn_id",
+                        "value": 5,
+                        "Description": "Nutrient management technique identifier"
+                    },
+                    {
+                        "name": "plan_techn_discrim_type",
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
+                    },
+                    {
+                        "name": "plan_techn_discrim",
+                        "Description": "Value of the discriminator"
+                    }
+                ],
+                [
+                    {
+                        "name": "AoAid",
+                        "value": 1,
+                        "Description": "Area of Analysis Identifier"
+                    },
+                    {
+                        "name": "plan_techn_id",
+                        "value": 8,
+                        "Description": "Nutrient management technique identifier"
+                    },
+                    {
+                        "name": "plan_techn_discrim_type",
+                        "value": "Soil test result",
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
+                    },
+                    {
+                        "name": "plan_techn_discrim",
+                        "value": "Medium",
+                        "Description": "Value of the discriminator"
+                    }
+                ],
+                [
+                    {
+                        "name": "AoAid",
+                        "value": 1,
+                        "Description": "Area of Analysis Identifier"
+                    },
+                    {
+                        "name": "plan_techn_id",
+                        "value": 14,
+                        "Description": "Nutrient management technique identifier"
+                    },
+                    {
+                        "name": "plan_techn_discrim_type",
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
+                    },
+                    {
+                        "name": "plan_techn_discrim",
+                        "Description": "Value of the discriminator"
+                    }
+                ],
+                [
+                    {
+                        "name": "AoAid",
+                        "value": 1,
+                        "Description": "Area of Analysis Identifier"
+                    },
+                    {
+                        "name": "plan_techn_id",
+                        "value": 2,
+                        "Description": "Nutrient management technique identifier"
+                    },
+                    {
+                        "name": "plan_techn_discrim_type",
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
+                    },
+                    {
+                        "name": "plan_techn_discrim",
+                        "Description": "Value of the discriminator"
+                    }
+                ],
+                [
+                    {
+                        "name": "AoAid",
+                        "value": 1,
+                        "Description": "Area of Analysis Identifier"
+                    },
+                    {
+                        "name": "plan_techn_id",
+                        "value": 9,
+                        "Description": "Nutrient management technique identifier"
+                    },
+                    {
+                        "name": "plan_techn_discrim_type",
+                        "Description": "Type of discriminator for varying nutrient management technique scores"
+                    },
+                    {
+                        "name": "plan_techn_discrim",
+                        "Description": "Value of the discriminator"
                     }
                 ]
             ]