/*
 * $Id$
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2017, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package m.comet.utils;

import gisobjects.GISObject;

/**
 *
 * @author rumpal
 */
public class DBQueries {

    public static String COMET01Query01(GISObject geom) {
        String query = "SELECT res.mukey, res.musym, res.muname, geography::STGeomFromText(intersectPoly.STAsText(), 4326).MakeValid().STArea()/4046.86 AS sizeIntersectionAcres "
                + "FROM (SELECT mupoly.musym, mupoly.mukey, mupoly.mupolygongeo.STIntersection(geometry::STGeomFromText('" + geom.getGeometry()
                + "',4326)).MakeValid() AS intersectPoly, muint.muname "
                + "FROM mupolygon AS mupoly WITH (index(SI_mupolygon_24876)) "
                + "INNER JOIN mapunit AS muint "
                + "ON mupoly.mukey = muint.mukey "
                + "WHERE mupoly.mupolygongeo.STIntersects(geometry::STGeomFromText('" + geom.getGeometry() + "', 4326))=1 "
                + "AND mupoly.mupolygongeo.STIsValid()=1 "
                + "AND geometry::STGeomFromText('" + geom.getGeometry() + "', 4326).STIsValid()=1) AS res;";
        return query;
    }

    public static String COMET01Query02(int mukey) {
        String query = "SELECT cokey, compname, comppct_r, taxorder "
                + "FROM component "
                + "WHERE mukey = " + mukey + " "
                + "AND comppct_r >= 10 AND taxorder IS NOT NULL AND majcompflag = 'Yes';";
        return query;
    }
    
    public static String COMET02Query03(int mukey) {
        String query = "SELECT muname "
                + "FROM mapunit "
                + "WHERE mukey = " + mukey + ";";
        return query;
    }

    public static String COMET02Query01(int mukey, int cokey) {
        String query = "SELECT comp.cokey, comp.compname, comp.taxorder, hor.chkey, "
                + "hor.hzdept_r, hor.hzdepb_r,  hor.wthirdbar_r, "
                + "hor.wfifteenbar_r, hor.sandtotal_r,  hor.claytotal_r, hor.om_r, "
                + "hor.ksat_r, hor.ph1to1h2o_r "
                + "FROM component AS comp "
                + "JOIN chorizon AS hor ON comp.cokey = hor.cokey "
                + "WHERE comp.mukey = " + mukey + " AND comp.cokey = " + cokey + ";";
        return query;
    }

    public static String COMET02Query02(int chkey) {
        String query = "SELECT DISTINCT text.texcl FROM chtexture AS text "
                + "JOIN chtexturegrp AS grp ON grp.chtgkey = text.chtgkey "
                + "WHERE grp.chkey = " + chkey + ";";
        return query;
    }

    public static String COMET05Query01(Integer id, Integer score) {
        String query = "SELECT id, pract_code, pract_name, pract_benefit, score "
                + "FROM comet.d_pract_score"
                + (score == null && id == null ? ";" : " WHERE " + (score == null ? "" : " score = " + score) + (score != null && id != null ? " AND " : "") + (id == null ? "" : "id = " + id)) + ";";
        return query;
    }

    public static String COMET06Query01(int practiceId) {
        String query = "SELECT moist_humid_co2, moist_humid_n20, moist_humid_methane "
                + "FROM comet.d_pract_red_coeff WHERE practice_score_id = " + practiceId + ";";
        return query;
    }

    public static String COMET06Query02(int practiceId) {
        String query = "SELECT dry_semiarid_co2, dry_semiarid_n20, dry_semiarid_methane "
                + "FROM comet.d_pract_red_coeff WHERE practice_score_id = " + practiceId + ";";
        return query;
    }
}