/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package m.wqm.pestpractscores;
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.*;

/**
 *
 * @author dhawal
 */
@Name("Integrated Pest Mitigation Practice Scores (PestPractScores)")
@Description("Compile WQM pesticide-related concern mitigation scores")
//change
@Path("m/pesticide_practice_score/1.0")

public class V1_0 extends ModelDataService{
    
    int AoAId=0;
    int pleach_practice_score=0;
    int psolsurf_practice_score=0;
    int padsurf_practice_score=0;
    int pdrift_practice_score=0;
    
    
    //JSONArray getArray
    ArrayList<m.wqm.pestpractscores.Input> components=new ArrayList<>(); // store the set of all input soilcomponents as objects
    ArrayList<m.wqm.pestpractscores.Result1> result1=new ArrayList<>();  // store the result as objects
    @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");
        System.out.println("Entry into ModelDataService");
            for(int i=0;i<groups.length();i++)
            {
                Map<String, JSONObject> group = JSONUtils.preprocess(groups.getJSONArray(i));
             
                int AoAId = JSONUtils.getIntParam(group, "AoAId", 0);
                int plan_ipm_practice = JSONUtils.getIntParam(group, "plan_ipm_practice", 0);
                String plan_ipm_variant=JSONUtils.getStringParam(group,"plan_pract_variant","err");

                m.wqm.pestpractscores.Input input=new m.wqm.pestpractscores.Input(AoAId,plan_ipm_practice,plan_ipm_variant);
                //System.out.println(input.plan_ipm_level);
                components.add(input);
            }
            System.out.println(components);
        }
        
    @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 (m.wqm.pestpractscores.Input ip : components) {
             
             int pleach_practice_score=0;
             int psolsurf_practice_score=0;
             int padsurf_practice_score=0;
             int pdrift_practice_score=0;
             String query;
             query="SELECT ipm_practice_score FROM wqm_ipm_practice_scores WHERE practice_id='"+ip.plan_ipm_practice+"' AND wqm_concern='Pesticide Leaching'" ;  
             if (ip.plan_pract_variant!=null && !ip.plan_pract_variant.isEmpty()){ 
                query=query.concat(" AND practice_variant='"+ip.plan_pract_variant+"'");}
             //System.out.println(ip.plan_pract_variant);
             //System.out.println(ip.AoAId);
             ResultSet results = statement.executeQuery(query);

                while (results.next())
                {
                   int pract_score =results.getInt("ipm_practice_score");
                   pleach_practice_score=pleach_practice_score +pract_score ;
                }
             query="SELECT ipm_practice_score FROM wqm_ipm_practice_scores WHERE practice_id='"+ip.plan_ipm_practice+"' AND wqm_concern='Pesticide Solution Runoff'" ;
             if (ip.plan_pract_variant!=null && !ip.plan_pract_variant.isEmpty()){
                query=query.concat(" AND practice_variant='"+ip.plan_pract_variant+"'");}
             //System.out.println(query);
                results = statement.executeQuery(query);
             
                while (results.next())
                {
                   int pract_score =results.getInt("ipm_practice_score");
                   psolsurf_practice_score  =psolsurf_practice_score +pract_score ;
                }
             query="SELECT ipm_practice_score FROM wqm_ipm_practice_scores WHERE practice_id='"+ip.plan_ipm_practice+"' AND wqm_concern='Pesticide Adsorbed Runoff'" ;
             if (ip.plan_pract_variant!=null && !ip.plan_pract_variant.isEmpty()){
                query=query.concat(" AND practice_variant='"+ip.plan_pract_variant+"'");}
             //System.out.println(query);
                results = statement.executeQuery(query);

                while (results.next())
                {
                   int pract_score=results.getInt("ipm_practice_score");
                   padsurf_practice_score =padsurf_practice_score +pract_score;
                }
            query="SELECT ipm_practice_score FROM wqm_ipm_practice_scores WHERE practice_id='"+ip.plan_ipm_practice+"' AND wqm_concern='Pesticide Drift'" ;
            if (ip.plan_pract_variant!=null && !ip.plan_pract_variant.isEmpty()){
                query=query.concat(" AND practice_variant='"+ip.plan_pract_variant+"'");}
            //System.out.println(query);
                results = statement.executeQuery(query);

                while (results.next())
                {
                   int practice_score =results.getInt("ipm_practice_score");
                   pdrift_practice_score =pdrift_practice_score +practice_score ;
                }
            
            Result1 result=new Result1(ip.AoAId,pleach_practice_score,psolsurf_practice_score,padsurf_practice_score,pdrift_practice_score);
            result1.add(result);
        }

            return EXEC_OK;       
    }
        @Override
        //writing the results back to JSON
    protected void postProcess() throws Exception {
        for(int i=0;i<result1.size();i++)
        {
            
            Result1 temp=result1.get(i);
            putResult("AoAId",temp.AoAId,"Area of Analysis ID");
            putResult("pleach_practice_score",temp.pleach_practice_score,"pleach_practice_score");
            putResult("psolsurf_practice_score",temp.psolsurf_practice_score,"psolsurf_practice_score");
            putResult("padsurf_practice_score",temp.padsurf_practice_score,"padsurf_practice_score");
            putResult("pdrift_practice_score",temp.pdrift_practice_score,"pdrift_practice_score");
        }    
        //putResult("aoa_sarp",aoa_sarp,"Pesticide Soil Adsorbed Runoff Potential of AoA");
    }
}