V1_0.java [src/java/m/wqm/sednutpractscores] Revision: e9779fc73de8f34e014e3bde2503140ff58f7cd7 Date: Thu Aug 06 12:26:55 MDT 2015
package m.wqm.sednutpractscores;
/**
*
* @author SrinivasReddy kontham
*/
import csip.ModelDataService;
import static csip.ModelDataService.EXEC_OK;
import java.util.ArrayList;
import javax.ws.rs.Path;
import oms3.annotations.Description;
import oms3.annotations.Name;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
import csip.utils.JSONUtils;
import java.util.Map;
import java.sql.*;
import java.util.concurrent.*;
@Name("SedNutPractScores")
@Description("Sediment Nutrient Practice Scores")
@Path("m/nut_pract_scores/1.0")
public class V1_0 extends ModelDataService
{
ArrayList<Input> components=new ArrayList<>(); // store the set of all input soilcomponents as objects
ArrayList<Result1> result1=new ArrayList<>(); // store the result as objects
int nleach_pract_score=0;
int nleach_avoid_pract_score=0;
int nleach_control_pract_score=0;
int nleach_trap_pract_score=0;
int nsurf_pract_score=0;
int nsurf_avoid_pract_score=0;
int nsurf_control_pract_score=0;
int nsurf_trap_pract_score=0;
int psurf_pract_score=0;
int psurf_avoid_pract_score=0;
int psurf_control_pract_score=0;
int psurf_trap_pract_score=0;
int ssurf_pract_score=0;
int ssurf_avoid_pract_score=0;
int ssurf_control_pract_score=0;
int ssurf_trap_pract_score=0;
int AoAid;
@Override
// reading the inputs from the json file into input object and placing it in the arraylist
protected void preProcess() throws Exception {
JSONArray groups = getJSONArrayParam("pestcomponents");
for(int i=0;i<groups.length();i++)
{
Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i));
AoAid = JSONUtils.getIntParam(group, "AoAid", 0);
int plan_pract_id=JSONUtils.getIntParam(group,"plan_practice_id",0);
String plan_pract_discrim_type=JSONUtils.getStringParam(group,"plan_practice_discrim_type","err");
String plan_pract_discrim_value=JSONUtils.getStringParam(group,"plan_practice_discrim_value","err");
Input input=new Input(AoAid,plan_pract_id,plan_pract_discrim_type,plan_pract_discrim_value);
components.add(input);
}
}
@Override
protected String process() throws Exception
{
Connection conn = null;
Statement statement = null;
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "admin");
conn.setAutoCommit(false);
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.equals(""))
{
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, '') = ''";
}
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);
}
ResultSet results = statement.executeQuery(query);
while (results.next())
{
int nl_avoid_pract_score=results.getInt("nut_pract_score");
nleach_avoid_pract_score=nleach_avoid_pract_score+nl_avoid_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int nl_control_pract_score=results.getInt("nut_pract_score");
nleach_control_pract_score=nleach_control_pract_score+nl_control_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int nl_trap_pract_score=results.getInt("nut_pract_score");
nleach_trap_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.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int sd_avoid_pract_score=results.getInt("nut_pract_score");
ssurf_avoid_pract_score=ssurf_avoid_pract_score+sd_avoid_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int sd_control_pract_score=results.getInt("nut_pract_score");
ssurf_control_pract_score=ssurf_control_pract_score+sd_control_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int sd_trap_pract_score=results.getInt("nut_pract_score");
ssurf_trap_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.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ns_avoid_pract_score=results.getInt("nut_pract_score");
nsurf_avoid_pract_score=nsurf_avoid_pract_score+ns_avoid_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ns_control_pract_score=results.getInt("nut_pract_score");
nsurf_control_pract_score=nsurf_control_pract_score+ns_control_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ns_trap_pract_score=results.getInt("nut_pract_score");
nsurf_trap_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.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ps_avoid_pract_score=results.getInt("nut_pract_score");
psurf_avoid_pract_score=psurf_avoid_pract_score+ps_avoid_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ps_control_pract_score=results.getInt("nut_pract_score");
psurf_control_pract_score=psurf_control_pract_score+ps_control_pract_score;
}
if(ip.plan_practice_discrim_type.equals(""))
{
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);
}
results = statement.executeQuery(query);
while (results.next())
{
int ps_trap_pract_score=results.getInt("nut_pract_score");
psurf_trap_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 result=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);
result1.add(result);
return EXEC_OK;
}
@Override
//writing the results back to JSON
protected void postProcess() throws Exception
{
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)"));
}
putResult("operation", result1Arr);
}
}