mssql_sdm.java [src/soils/db] Revision: default  Date:
/*
 * 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 soils.db;

import csip.SessionLogger;
import csip.annotations.Resource;
import csip.api.server.ServiceException;
import csip.utils.Binaries;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.logging.Level;
import soils.Component;
import soils.Horizon;
import soils.MapUnit;
import static soils.db.DBResources.SDM;
import static soils.db.DBResources.SDM_REST;
import soils.db.tables.TableCocropyld;
import soils.db.tables.TableCoecoclass;
import soils.db.tables.TableComonth;
import soils.db.tables.TableComponent;
import soils.db.tables.TableComponentCalculations;
import soils.db.tables.TableFragments;
import soils.db.tables.TableHorizon;
import soils.db.tables.TableMapUnit;
import soils.db.tables.TableMuaggatt;
import soils.db.tables.TableMucropyld;
import soils.db.tables.TableTexture;
import soils.db.tables.TableTextureGroup;
import soils.exceptions.SDMException;
import soils.utils.EvalResult;

/**
 *
 * @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
 */
@Resource(from = soils.db.DBResources.class)
public class mssql_sdm extends MSSQL_SOILS {

  public mssql_sdm(Class<?> c, SessionLogger log) throws ServiceException, SQLException {
    DB_GEOM_SRID = "4326";
    logPrefix = "mssql_sdm";
    LOG = log;

    connection = Binaries.getResourceJDBC(c, SDM, log);
    if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
      throw new ServiceException("Invalid connection type passed to mssql_ssurgo.  Class requires a connection to a MsSQL server.");
    } else if (!isValidDb()) {
      throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
    }

  }

  public mssql_sdm(Class<?> c, SessionLogger log, boolean rest) throws ServiceException, SQLException {
    DB_GEOM_SRID = "4326";
    logPrefix = "mssql_sdm";
    LOG = log;

    if (rest) {
      connection = Binaries.getResourceJDBC(c, SDM_REST, log);
    } else {
      connection = Binaries.getResourceJDBC(c, SDM, log);
    }
    if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
      throw new ServiceException("Invalid connection type passed to mssql_sdm.  Class requires a connection to a MsSQL server.");
    } else if (!isValidDb()) {
      throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
    }

  }

  public mssql_sdm(Connection conn, SessionLogger Log) throws ServiceException, SQLException {
    DB_GEOM_SRID = "4326";
    logPrefix = "mssql_sdm";
    LOG = Log;
    if (null != conn) {
      if (conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
        connection = conn;
        if (!isValidDb()) {
          throw new ServiceException("Invalid connection database type passed to soils_data mssql_sdm implementation. Class requires a connection to an instance of the SDM database.");
        }
      } else {
        throw new ServiceException("Invalid connection type passed to mssql_sdm.  Class requires a connection to a MsSQL server.");
      }
    } else {
      throw new ServiceException("No connection object passed to mssql_sdm.  Class requires a connection to a MsSQL server.");
    }

  }

  @Override
  protected String buildBasicNoFilterMukeyByCokeyQuery(String cokey) {
    return "SELECT "
        + getMapUnitFieldList() + ", "
        + "legend.areasymbol, legend.areaname, mapunit.muacres,"
        + "    sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM component "
        + "    INNER JOIN mapunit on component.mukey=mapunit.mukey "
        + "    INNER JOIN legend on mapunit.lkey=legend.lkey "
        + "    INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + "    INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + "    INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " WHERE component.cokey=" + Integer.parseInt(cokey)
        + "       AND legend.areatypename like 'Non-MLRA%' ;";
  }

  @Override
  protected String buildBasicMukeyByCokeyQuery(String cokey) {
    return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres,"
        + "    sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM component "
        + "    INNER JOIN mapunit on component.mukey=mapunit.mukey "
        + "    INNER JOIN legend on mapunit.lkey=legend.lkey "
        + "    INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + "    INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + "    INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " WHERE component.cokey=" + Integer.parseInt(cokey)
        + "       AND legend.areatypename like 'Non-MLRA%' ;";
  }

  @Override
  protected String buildBasicMukeyQuery(String mukey) {
    return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres,"
        + "    sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM mapunit "
        + " INNER JOIN legend on mapunit.lkey=legend.lkey "
        + " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " WHERE mapunit.mukey=" + Integer.parseInt(mukey)
        + "       AND legend.areatypename like 'Non-MLRA%' "
        + " ORDER BY mukey;";
  }

  @Override
  protected String buildBasicMukeyQueryByArea(String areaSymbol) {
    return "SELECT mapunit.mukey, mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, mapunit.muacres, mupolygon.mupolygongeo.STAsText() as mupolygon, "
        + "    sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM legend "
        + " INNER JOIN mapunit on legend.lkey=mapunit.lkey "
        + " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " INNER JOIN mupolygon on mapunit.mukey=mupolygon.mukey "
        + " WHERE legend.areasymbol='" + areaSymbol + "' "
        + "       AND legend.areatypename like 'Non-MLRA%' "
        + " ORDER BY mapunit.mukey;";
  }

  @Override
  protected String buildBasicMukeyQueryByAreaWithShape(String areaSymbol) {
    return null;
  }

  @Override
  protected String buildCHFQuery(String mukey, String filter) {
    return "SELECT component.majcompflag, component.mukey, component.cokey, component.compname, "
        + " component.comppct_r, component.hydgrp, component.slope_r, "
        + " component.taxorder, chorizon.chkey, chorizon.om_r, "
        + " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
        + " chorizon.kwfact, chorizon.kffact, chorizon.sar_r, chorizon.ec_r, "
        + " component.wei, component.slopelenusle_r, component.tfact, component.comppct_r, component.hydricrating, "
        + " chorizon.cokey, chorizon.kffact, chorizon.kwfact, "
        + " chorizon.sandtotal_r, chorizon.om_r, chorizon.hzdepb_r, chorizon.hzthk_r, "
        + " chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_h, "
        + " chorizon.silttotal_r, chorizon.claytotal_r, "
        + " component.taxorder, "
        + " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
        + " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
        + " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
        + " FROM component "
        + " INNER JOIN chorizon on component.cokey=chorizon.cokey "
        + " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
        + " WHERE component.mukey=" + mukey + " "
        + " AND component.slope_r is not null"
        + " AND UPPER(component.majcompflag)='YES' "
        + ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY component.cokey, chorizon.hzdept_r "
        : "  AND (component.taxorder!='' AND component.taxorder is not null) "
        + " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 )  ))"
        + " ORDER BY component.cokey, chorizon.hzdept_r") + ";";
  }

  @Override
  protected String buildWeppWepsCHFQuery(String cokey, String filter) {
    return "SELECT  mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, "
        + " component.majcompflag, "
        + " component.mukey, "
        + " component.cokey, "
        + " component.compname, "
        + " component.comppct_r, "
        + " component.hydgrp, "
        + " component.slope_r, component.slope_l, component.slope_h, "
        + " component.taxorder, component.taxsubgrp, "
        + " component.wei, "
        + " component.slopelenusle_r, component.slopelenusle_l, component.slopelenusle_h, "
        + " component.tfact, "
        + " component." + TableComponent.ALBEDODRY_R + ", component." + TableComponent.ALBEDODRY_L + ", component." + TableComponent.ALBEDODRY_H + ", "
        + " chorizon.chkey, "
        + " chorizon.om_r, chorizon.om_l, chorizon.om_h, "
        + " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
        + " chorizon.kwfact, "
        + " chorizon.kffact, "
        + " chorizon.cokey,  "
        + " chorizon.sandtotal_r, chorizon.sandtotal_l, chorizon.sandtotal_h, "
        + " chorizon.sandvc_r, chorizon.sandvc_l, chorizon.sandvc_h, "
        + " chorizon.sandco_r, chorizon.sandco_l, chorizon.sandco_h, "
        + " chorizon.sandmed_r, chorizon.sandmed_l, chorizon.sandmed_h, "
        + " chorizon.sandfine_r, chorizon.sandfine_l, chorizon.sandfine_h, "
        + " chorizon.sandvf_r, chorizon.sandvf_l, chorizon.sandvf_h, "
        + " chorizon.caco3_r, chorizon.caco3_l, chorizon.caco3_h, "
        + " chorizon.cec7_l, chorizon.cec7_r, chorizon.cec7_h, "
        + " chorizon.wthirdbar_l, chorizon.wthirdbar_r, chorizon.wthirdbar_h, "
        + " chorizon.wfifteenbar_l, chorizon.wfifteenbar_r, chorizon.wfifteenbar_h, "
        + " chorizon.dbthirdbar_l, chorizon.dbthirdbar_r, chorizon.dbthirdbar_h, "
        + " chorizon.silttotal_r, chorizon.silttotal_l, chorizon.silttotal_h, "
        + " chorizon.claytotal_r, chorizon.claytotal_l, chorizon.claytotal_h, "
        + " chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_h, "
        + " chorizon." + TableHorizon.ECEC_R + ", chorizon." + TableHorizon.ECEC_L + ", chorizon." + TableHorizon.ECEC_H + ","
        + " chorizon." + TableHorizon.LEP_R + ", chorizon." + TableHorizon.LEP_L + ", chorizon." + TableHorizon.LEP_H + ", "
        + " chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_H + " "
        + " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
        + " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
        + " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
        + " FROM component "
        + " INNER JOIN mapunit ON component.mukey=mapunit.mukey "
        + " INNER JOIN legend ON legend.lkey=mapunit.lkey "
        + " INNER JOIN chorizon on component.cokey=chorizon.cokey "
        + " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
        + " WHERE component.cokey=" + cokey + " "
        + " AND legend.areatypename like 'Non-MLRA%' "
        + " AND component.slope_r is not null "
        + " AND UPPER(component.majcompflag)='YES' "
        + " AND (component.taxorder!='' AND component.taxorder is not null) "
        + ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY chorizon.hzdept_r, chorizon.chkey "
        : ((filter.equalsIgnoreCase("WIND")) ? "  AND (component.taxorder!='' AND component.taxorder is not null) "
        + " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 )  )) "
        : "")
        + " ORDER BY chorizon.hzdept_r, chorizon.chkey ") + ";";
  }

  @Override
  protected String buildCHQuery(String cokey, String filter) {
    return "SELECT  mapunit.musym, mapunit.muname, legend.areasymbol, legend.areaname, "
        + " component.majcompflag, "
        + " component.mukey, "
        + " component.cokey, "
        + " component.compname, "
        + " component.comppct_r, "
        + " component.hydgrp, "
        + " component.slope_r, component.slope_l, component.slope_h, "
        + " component.taxorder, component.taxsubgrp, "
        + " component.wei, "
        + " component.slopelenusle_r, component.slopelenusle_l, component.slopelenusle_h, component.hydricrating, "
        + " component.tfact, "
        + " component." + TableComponent.ALBEDODRY_R + ", component." + TableComponent.ALBEDODRY_L + ", component." + TableComponent.ALBEDODRY_H + ", "
        + " chorizon.chkey, "
        + " chorizon.om_r, chorizon.om_l, chorizon.om_h, "
        + " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
        + " chorizon.kwfact, "
        + " chorizon.kffact, "
        + " chorizon.cokey,  "
        + " chorizon.sandtotal_r, chorizon.sandtotal_l, chorizon.sandtotal_h, "
        + " chorizon.sandvc_r, chorizon.sandvc_l, chorizon.sandvc_h, "
        + " chorizon.sandco_r, chorizon.sandco_l, chorizon.sandco_h, "
        + " chorizon.sandmed_r, chorizon.sandmed_l, chorizon.sandmed_h, "
        + " chorizon.sandfine_r, chorizon.sandfine_l, chorizon.sandfine_h, "
        + " chorizon.sandvf_r, chorizon.sandvf_l, chorizon.sandvf_h, "
        + " chorizon.caco3_r, chorizon.caco3_l, chorizon.caco3_h, "
        + " chorizon.cec7_l, chorizon.cec7_r, chorizon.cec7_h, "
        + " chorizon.wthirdbar_l, chorizon.wthirdbar_r, chorizon.wthirdbar_h, "
        + " chorizon.wfifteenbar_l, chorizon.wfifteenbar_r, chorizon.wfifteenbar_h, "
        + " chorizon.dbthirdbar_l, chorizon.dbthirdbar_r, chorizon.dbthirdbar_h, "
        + " chorizon.silttotal_r, chorizon.silttotal_l, chorizon.silttotal_h, "
        + " chorizon.claytotal_r, chorizon.claytotal_l, chorizon.claytotal_h, "
        + " chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_h, "
        + " chorizon." + TableHorizon.ECEC_R + ", chorizon." + TableHorizon.ECEC_L + ", chorizon." + TableHorizon.ECEC_H + ","
        + " chorizon." + TableHorizon.LEP_R + ", chorizon." + TableHorizon.LEP_L + ", chorizon." + TableHorizon.LEP_H + ", "
        + " chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_R + ", chorizon." + TableHorizon.KSAT_H + ", "
        + " chorizon." + TableHorizon.EC_R + ", chorizon." + TableHorizon.SAR_R + ", "
        + " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
        + " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
        + " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
        + " FROM component "
        + " INNER JOIN mapunit ON component.mukey=mapunit.mukey "
        + " INNER JOIN legend ON legend.lkey=mapunit.lkey "
        + " INNER JOIN chorizon on component.cokey=chorizon.cokey "
        + " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
        + " WHERE component.cokey=" + cokey + " "
        + " AND legend.areatypename like 'Non-MLRA%' "
        + " AND component.slope_r is not null "
        + " AND UPPER(component.majcompflag)='YES' "
        + " AND (component.taxorder!='' AND component.taxorder is not null) "
        + ((filter.equalsIgnoreCase("WATER")) ? " AND (component.taxorder!='' AND component.taxorder is not null) ORDER BY chorizon.hzdept_r "
        : ((filter.equalsIgnoreCase("WIND")) ? "  AND (component.taxorder!='' AND component.taxorder is not null) "
        + " AND (chorizon.sandtotal_r is not null OR ( (UPPER(taxorder)='HISTOSOLS') OR ( (UPPER(taxorder)<>'HISTOSOLS') AND hzdepb_r>10 AND om_r>15 )  )) "
        : "")
        + " ORDER BY chorizon.hzdept_r ") + ";";
  }

  @Override
  protected String buildCSMQuery(MapUnit mapUnit) {
    String ret_val = "";
    String lastQueryWhere = "";
    String lastQueryWhere2 = "";

    if (mapUnit.components().size() > 0) {
      for (String cokey : mapUnit.components().keySet()) {
        if (lastQueryWhere.length() > 1) {
          lastQueryWhere += " OR component.cokey='" + cokey + "'";
          lastQueryWhere2 += " OR WT1.cokey='" + cokey + "'";
        } else {
          lastQueryWhere += " component.cokey='" + cokey + "'";
          lastQueryWhere2 += "WT1.cokey='" + cokey + "'";
        }
      }

      ret_val = "With WT1 As (Select component.cokey, component.compname, component.comppct_r, MIN(cosoilmoist.soimoistdept_r) As wtbl_top_min, MAX(cosoilmoist.soimoistdepb_r) As wtbl_bot_max From component Inner Join comonth On component.cokey=comonth.cokey Inner Join cosoilmoist On comonth.comonthkey=cosoilmoist.comonthkey "
          + "Where " + lastQueryWhere + "and cosoilmoist.soimoiststat='Wet' Group By component.cokey, component.compname, component.comppct_r), WT2 As (Select WT1.cokey, WT1.compname, WT1.comppct_r, WT1.wtbl_top_min, WT1.wtbl_bot_max, MAX(cosoilmoist.soimoistdept_r) As nonwet_top_max From WT1 Left Outer Join comonth On WT1.cokey=comonth.cokey Left Outer Join cosoilmoist On comonth.comonthkey=cosoilmoist.comonthkey "
          + "Where " + lastQueryWhere2 + " and (cosoilmoist.soimoiststat NOT IN ('Wet') OR cosoilmoist.soimoiststat IS NULL) Group By WT1.cokey, WT1.compname, WT1.comppct_r, WT1.wtbl_top_min, WT1.wtbl_bot_max) Select WT2.cokey, WT2.compname, WT2.comppct_r, WT2.wtbl_top_min, WT2.wtbl_bot_max, WT2.nonwet_top_max, case when (wtbl_bot_max < 165 or nonwet_top_max >= wtbl_bot_max) then 'Perched' else 'Apparent' end as wtkind from WT2";
    }

    return ret_val;
  }

  @Override
  protected String buildCoCropYldQuery(HashMap<String, MapUnit> mapUnits, ArrayList<String> cropNames, ArrayList<String> yldUnits) {
    String query = "";
    String cokeyList;
    String cropNameList = "";
    String unitList = "";

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      cokeyList = "(";
      int count = 0;
      for (MapUnit mapunit : mapUnits.values()) {
        LinkedHashMap<String, Component> components = mapunit.components();

        for (String cokey : components.keySet()) {
          if (count > 0) {
            cokeyList += ", ";
          }
          cokeyList += cokey;
          count++;
        }
      }
      cokeyList += ") ";

      if (null != cropNames) {
        count = 0;
        for (String cropName : cropNames) {
          if (count > 0) {
            cropNameList += ", ";
          }
          String cleanCropName = EvalResult.cleanStringForSQL(cropName);

          if (!cleanCropName.isEmpty()) {
            cropNameList += "'" + cleanCropName + "'";

            count++;
          }
        }
      }

      if (null != yldUnits) {
        count = 0;
        for (String yldUnit : yldUnits) {
          if (count > 0) {
            unitList += ", ";
          }
          String cleanYldUnit = EvalResult.cleanStringForSQL(yldUnit);

          if (!cleanYldUnit.isEmpty()) {
            unitList += "'" + cleanYldUnit + "'";

            count++;
          }

        }
      }

      cokeyList += ((cropNameList.isEmpty()) ? "" : (" AND " + "cocropyld." + TableCocropyld.CROPNAME + " in (" + cropNameList + ") "));
      cokeyList += ((unitList.isEmpty()) ? "" : (" AND " + "cocropyld." + TableCocropyld.YLDUNITS + " in (" + unitList + ") "));
      cokeyList += " ORDER BY mukey, cocropyld.cokey;";

      query = "SELECT mapunit.mukey, "
          + "cocropyld." + TableCocropyld.CROPNAME + ", "
          + "cocropyld." + TableCocropyld.YLDUNITS + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_L + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_R + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_H + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_L + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_R + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_H + ", "
          + "cocropyld." + TableCocropyld.CROPPRODINDEX + ", "
          + "cocropyld." + TableCocropyld.VASOIPRDGRP + ", "
          + "cocropyld." + TableCocropyld.COKEY + ", "
          + "cocropyld." + TableCocropyld.COCROPYLDKEY + " "
          + " FROM cocropyld "
          + " INNER JOIN component on cocropyld.cokey=component.cokey "
          + " INNER JOIN mapunit on component.mukey=mapunit.mukey "
          + " WHERE cocropyld.cokey in " + cokeyList;
    }

    return query;
  }

  @Override
  protected String buildCoCropYldQuery(LinkedHashMap<String, Component> component) {
    String query = "";
    String cokeyList;

    if ((null != component) && (component.size() > 0)) {
      cokeyList = "(";
      int count = 0;
      for (String cokey : component.keySet()) {
        if (count > 0) {
          cokeyList += ", ";
        }
        cokeyList += cokey;
        count++;
      }

      cokeyList += ") ORDER BY mapunit.mukey, cocropyld.cokey;";

      query = "SELECT mapunit.mukey, "
          + "cocropyld." + TableCocropyld.CROPNAME + ", "
          + "cocropyld." + TableCocropyld.YLDUNITS + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_L + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_R + ", "
          + "cocropyld." + TableCocropyld.NONIRRYIELD_H + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_L + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_R + ", "
          + "cocropyld." + TableCocropyld.IRRYIELD_H + ", "
          + "cocropyld." + TableCocropyld.CROPPRODINDEX + ", "
          + "cocropyld." + TableCocropyld.VASOIPRDGRP + ", "
          + "cocropyld." + TableCocropyld.COKEY + ", "
          + "cocropyld." + TableCocropyld.COCROPYLDKEY + " "
          + " FROM cocropyld "
          + " INNER JOIN component on cocropyld.cokey=component.cokey "
          + " INNER JOIN mapunit on component.mukey=mapunit.mukey "
          + " WHERE cocropyld.cokey in " + cokeyList;
    }

    return query;
  }

  @Override
  protected String buildCoEcoClassQuery(HashMap<String, MapUnit> mapUnits) {
    String query = "";
    String cokeyList;

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      cokeyList = "(";
      int count = 0;
      for (MapUnit mapunit : mapUnits.values()) {
        LinkedHashMap<String, Component> components = mapunit.components();

        for (String cokey : components.keySet()) {
          if (count > 0) {
            cokeyList += ", ";
          }
          cokeyList += cokey;
          count++;
        }
      }
      cokeyList += ") ORDER BY mapunit.mukey, component.cokey;";

      query = "SELECT mapunit.mukey, "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSTYPENAME + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSREF + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSID + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.ECOCLASSNAME + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.COKEY + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.COECOCLASSKEY + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.SOURCESDWPRIMARYKEY + ", "
          + TableCoecoclass.TABLE_NAME + "." + TableCoecoclass.SOURCESDWTABLEPHYSICALNAME + " "
          + " FROM coecoclass "
          + " INNER JOIN component on coecoclass.cokey=component.cokey "
          + " INNER JOIN mapunit on component.mukey=mapunit.mukey "
          + " WHERE coecoclass.cokey in " + cokeyList;
    }

    return query;
  }

  @Override
  protected String buildCoEcoClassQuery(LinkedHashMap<String, Component> component) {
    String query = "";
    String cokeyList;

    if ((null != component) && (component.size() > 0)) {
      cokeyList = "(";
      int count = 0;

      for (String cokey : component.keySet()) {
        if (count > 0) {
          cokeyList += ", ";
        }
        cokeyList += cokey;
        count++;
      }

      cokeyList += ") ORDER BY mukey, cokey;";

      query = "SELECT mapunit.mukey, "
          + TableCoecoclass.ECOCLASSTYPENAME + ", "
          + TableCoecoclass.ECOCLASSREF + ", "
          + TableCoecoclass.ECOCLASSID + ", "
          + TableCoecoclass.ECOCLASSNAME + ", "
          + TableCoecoclass.COKEY + ", "
          + TableCoecoclass.COECOCLASSKEY + ", "
          + TableCoecoclass.SOURCESDWPRIMARYKEY + ", "
          + TableCoecoclass.SOURCESDWTABLEPHYSICALNAME + " "
          + " FROM coecoclass "
          + " INNER JOIN component on coecoclass.cokey=component.cokey "
          + " INNER JOIN mapunit on component.mukey=mapunit.mukey "
          + " WHERE coecoclass.cokey in " + cokeyList;
    }

    return query;
  }

  @Override
  protected String buildCoMonthQuery(HashMap<String, MapUnit> mapUnits) {
    String query = "";
    String cokeyList;

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      cokeyList = "(";
      int count = 0;
      for (MapUnit mapunit : mapUnits.values()) {
        LinkedHashMap<String, Component> components = mapunit.components();

        for (String cokey : components.keySet()) {
          if (count > 0) {
            cokeyList += ", ";
          }
          cokeyList += cokey;
          count++;
        }
      }
      cokeyList += ") ORDER BY " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + ", " + TableComponent.TABLE_NAME + "." + TableComponent.COKEY + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.MONTH_SEQ + " ;";

      query = "SELECT " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + ","
          + TableComonth.TABLE_NAME + "." + TableComonth.COMONTH_KEY + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.MONTH + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.FLOOD_FREQ_CL + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.POND_FREQ_CL + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.MONTH_SEQ + ", "
          + TableComonth.TABLE_NAME + "." + TableComonth.COKEY + " "
          + " FROM " + TableComonth.TABLE_NAME + " "
          + " INNER JOIN "
          + TableComponent.TABLE_NAME + " ON "
          + TableComonth.TABLE_NAME + "." + TableComonth.COKEY + "="
          + TableComponent.TABLE_NAME + "." + TableComponent.COKEY + " "
          + " WHERE " + TableComonth.TABLE_NAME + "." + TableComonth.COKEY + " IN " + cokeyList;
    }

    return query;
  }

  @Override
  protected String buildFindMapUnitsByPolyKeyQuery(String mupolygonkey, boolean returnIntersectionShape) {
    return "SELECT mupolygon.areasymbol, mapunit.musym, mapunit.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, mupolygon.muareaacres as area, "
        + ((returnIntersectionShape) ? " mupolygon.mupolygongeo.STAsText() AS intersectionShape, " : " ")
        + " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM dbo.mupolygon "
        + " INNER JOIN dbo.mapunit ON mupolygon.mukey=dbo.mapunit.mukey "
        + " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
        + " INNER JOIN dbo.muaggatt ON mapunit.mukey=dbo.muaggatt.mukey "
        + " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " WHERE mupolygon.mupolygonkey=" + mupolygonkey + ";";
  }

  @Override
  protected String buildFindMapUnitsQuery(String WKTShape, boolean returnIntersectionShape) {
    return "SELECT areasymbol, areaname, musym, mukey, muname, muacres, brockdepmin, geography::STGeomFromText(intersectPoly.STAsText(), " + DEFAULT_GEOG_SRID + ").MakeValid().STArea() / 4046.86 AS area "
        + (returnIntersectionShape ? ", geography::STGeomFromText(intersectPoly.STAsText(), " + DEFAULT_GEOG_SRID + ").MakeValid().STAsText() AS intersectionShape " : " ")
        + "    , saversion, saverest, spatialversion, spatialverest, tabularversion, tabularverest "
        + " FROM "
        + " (SELECT m.areasymbol, legend.areaname, m.musym, m.mukey, mapunit.muacres, mapunit.muname, muaggatt.brockdepmin, "
        + " m.mupolygongeo.STIntersection(geometry::STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ")).MakeValid() as intersectPoly, "
        + " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + " FROM dbo.mupolygon as m "
        + " WITH (index(SI_mupolygon_24876)) "
        + " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey "
        + " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey "
        + " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey "
        + " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol "
        + " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol "
        + " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol "
        + " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ")) = 1 "
        + " AND m.mupolygongeo.STIsValid()=1 "
        + " AND (geometry:: STGeomFromText ('" + WKTShape + "', " + DB_GEOM_SRID + ").STIsValid())=1 "
        + " AND legend.areatypename like 'Non-MLRA%' ) as a"
        + " ORDER BY mukey; ";
  }

  @Override
  protected String buildFragmentsQuery(String chkey) {
    return "SELECT " + TableFragments.CHFRAGSKEY + ", " + TableFragments.FRAGVOL_R + ", " + TableFragments.FRAGVOL_L + ", " + TableFragments.FRAGVOL_H
        + ", " + TableFragments.FRAGSIZE_R + ", " + TableFragments.FRAGSIZE_L + ", " + TableFragments.FRAGSIZE_H
        + ", " + TableFragments.FRAGHARD + ", " + TableFragments.FRAGSHP + ", " + TableFragments.FRAGROUND + ", " + TableFragments.FRAGKIND
        + " FROM chfrags WHERE chkey=" + chkey + " Order By " + TableFragments.FRAGVOL_R + " ; ";
  }

  @Override
  protected String buildGenericMapUnitIntersectQuery() {
    throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  protected String buildIsConusQuery(String lat, String lon) {
    return "SELECT top 1 areasymbol, "
        + " CASE WHEN SUBSTRING(areasymbol, 1, 2) in ('AL','AZ','AR','CA','CO','CT','DC','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')THEN 1 ELSE 0 END  as CONUS "
        + " from dbo.mupolygon "
        + " WITH (index(SI_mupolygon_24876)) "
        + " WHERE mupolygongeo.STIntersects(geometry::STGeomFromText('POINT("
        + lon + " " + lat + ")', 4326)) = 1;";
  }

  @Override
  protected String buildMinResdept_rQuery(String cokey) {
    return "SELECT min(resdept_r) as " + TableComponentCalculations.RESDEPTMIN_R + " FROM corestrictions WHERE cokey=" + cokey + "; ";
  }

  @Override
  protected String buildMuCropYldQuery(HashMap<String, MapUnit> mapUnits, ArrayList<String> cropNames, ArrayList<String> yldUnits) {
    String query = "";
    String cropNameList = "";
    String unitList = "";
    int count = 0;

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      query = "SELECT " + TableMucropyld.CROPNAME + ", "
          + TableMucropyld.YLDUNITS + ", "
          + TableMucropyld.NONIRRYIELD_L + ", "
          + TableMucropyld.NONIRRYIELD_R + ", "
          + TableMucropyld.NONIRRYIELD_H + ", "
          + TableMucropyld.IRRYIELD_L + ", "
          + TableMucropyld.IRRYIELD_R + ", "
          + TableMucropyld.IRRYIELD_H + ", "
          + TableMucropyld.MUKEY + ", "
          + TableMucropyld.MUCRPYLDKEY
          + " FROM mucropyld WHERE mukey in ( ";

      for (String mukey : mapUnits.keySet()) {
        if (count > 0) {
          query += ", ";
        }
        query += mukey;
        count++;
      }

      query += ") ";

      if (null != cropNames) {
        count = 0;
        for (String cropName : cropNames) {
          if (count > 0) {
            cropNameList += ", ";
          }
          String cleanCropName = EvalResult.cleanStringForSQL(cropName);

          if (!cleanCropName.isEmpty()) {
            cropNameList += "'" + cleanCropName + "'";

            count++;
          }
        }
      }

      if (null != yldUnits) {
        count = 0;
        for (String yldUnit : yldUnits) {
          if (count > 0) {
            unitList += ", ";
          }
          String cleanYldUnit = EvalResult.cleanStringForSQL(yldUnit);

          if (!cleanYldUnit.isEmpty()) {
            unitList += "'" + cleanYldUnit + "'";

            count++;
          }
        }
      }

      query += ((cropNameList.isEmpty()) ? "" : (" AND " + TableMucropyld.CROPNAME + " in (" + cropNameList + ") "));
      query += ((unitList.isEmpty()) ? "" : (" AND " + TableMucropyld.YLDUNITS + " in (" + unitList + ") "));
      query += " ORDER BY mukey;";
    }
    return query;
  }

  @Override
  protected String buildPlainComponentQuery(String mukey) {
    return "SELECT " + getComponentFieldList() + " FROM " + TableComponent.TABLE_NAME
        + " WHERE " + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + "=" + mukey + " ;";
  }

  /**
   * Retrieves all component, horizon and fragments information for each mapunit
   * specified in mapUnits.
   *
   * @param mapUnits
   * @return
   * @throws ServiceException
   */
  @Override
  protected String buildPlainNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits) throws ServiceException {
    return buildPlainNoFilterCHFQuery(mapUnits, false);
  }

  @Override
  protected String buildBasicNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits) throws ServiceException {
    return buildPlainNoFilterCHFQuery(mapUnits, true);
  }

  @Override
  protected String buildPlainNoFilterCHFQuery(HashMap<String, MapUnit> mapUnits, boolean includeNonMajComp) throws ServiceException {
    String mukeyList = "";

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      for (MapUnit mapunit : mapUnits.values()) {
        if (mukeyList.length() > 1) {
          mukeyList += ", ";
        }
        mukeyList += mapunit.mukey();
      }
    } else {
      throw new ServiceException("No Mapunits specified to the buildPlainNoFilterCHFQuery() Function");
    }

    return "SELECT "
        + getComponentFieldList() + ", "
        + getHorizonFieldList() + ", "
        + getFragFieldList()
        + " FROM " + TableComponent.TABLE_NAME
        + " INNER JOIN " + TableHorizon.TABLE_NAME + " ON "
        + TableComponent.TABLE_NAME + "." + TableComponent.COKEY
        + "="
        + TableHorizon.TABLE_NAME + "." + TableHorizon.COKEY
        + " LEFT OUTER JOIN "
        + TableFragments.TABLE_NAME + " ON "
        + TableHorizon.TABLE_NAME + "." + TableHorizon.CHKEY_NAME
        + "="
        + TableFragments.TABLE_NAME + "." + TableFragments.CHKEY
        + " WHERE "
        + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY + " IN (" + mukeyList + ") "
        + (!includeNonMajComp
            ? " AND UPPER(" + TableComponent.TABLE_NAME + "." + TableComponent.MAJ_COMP_FLAG + ")='YES' "
            : "")
        + " ORDER BY "
        + TableComponent.TABLE_NAME + "." + TableComponent.MUKEY
        + ", "
        + TableComponent.TABLE_NAME + "." + TableComponent.COKEY
        + ", "
        + TableHorizon.TABLE_NAME + "." + TableHorizon.HZDEPT_R_NAME
        + ", "
        + TableHorizon.TABLE_NAME + "." + TableHorizon.CHKEY_NAME
        + ", "
        + TableFragments.TABLE_NAME + "." + TableFragments.CHFRAGSKEY
        + ";";
  }

  @Override
  protected String buildPlainCHFQuery(String mukey) {
    return "SELECT component.majcompflag, component.mukey, component.cokey, component.compname, "
        + " component.comppct_r, component.hydgrp, component.slope_r, "
        + " component." + TableComponent.RS_PROD_H + ", "
        + " component." + TableComponent.RS_PROD_L + ", "
        + " component." + TableComponent.RS_PROD_R + ", "
        + " component." + TableComponent.OTHERPH_NAME + ", "
        + " component." + TableComponent.LOCALPHASE_NAME + ", "
        + " component." + TableComponent.MAJ_COMP_FLAG + ", "
        + " component.taxorder, chorizon.chkey, chorizon.om_r, "
        + " chorizon.hzthk_r, chorizon.hzdept_r, chorizon.hzdepb_r, "
        + " chorizon.kwfact, chorizon.kffact, "
        + " component.wei, component.slopelenusle_r, component.tfact, "
        + " chorizon.cokey, chorizon.kffact, chorizon.kwfact, "
        + " chorizon.sandtotal_r, chorizon.om_r, chorizon.hzdepb_r, chorizon.hzthk_r, "
        + " chorizon.ph1to1h2o_r, chorizon.ph1to1h2o_l, chorizon.ph1to1h2o_h, "
        + " chorizon.silttotal_r, chorizon.claytotal_r, "
        + " chfrags.fragvol_r, chfrags.chfragskey, chfrags.fraghard, "
        + " chfrags.fragkind, chfrags.fraground, chfrags.fragshp, chfrags.fragsize_h, "
        + " chfrags.fragsize_l, chfrags.fragsize_r, chfrags.fragvol_h, chfrags.fragvol_l "
        + " FROM component "
        + " INNER JOIN chorizon on component.cokey=chorizon.cokey "
        + " LEFT OUTER JOIN chfrags on chorizon.chkey=chfrags.chkey "
        + " WHERE component.mukey=" + mukey + " "
        + " AND component.slope_r is not null"
        + " AND UPPER(component.majcompflag)='YES' ;";
  }

  @Override
  protected String buildStateAreaSymbolQuery(String stateAbbrev) {
    return "SELECT areasymbol FROM legend "
        + " WHERE areatypename like 'Non-MLRA%' "
        + "       AND areasymbol like '" + stateAbbrev + "%' "
        + " ORDER BY areasymbol;";
  }

  @Override
  protected String buildStateCountyByLocationQuery(String countyWKT) {
    String query = null;

    if (null != countyWKT) {
      query = "SELECT distinct(areasymbol) as areasymbol FROM ( "
          + " SELECT legend.areasymbol "
          + " FROM sapolygon "
          + " INNER JOIN dbo.legend ON sapolygon.lkey=dbo.legend.lkey "
          + " WHERE sapolygongeo.STIntersects( geometry::STGeomFromText( '" + countyWKT + "', 4326).MakeValid())=1 "
          + " AND sapolygongeo.STIsValid()=1 "
          + "	AND  legend.areatypename like 'Non-MLRA%' "
          + "	) as a order by areasymbol;";
    }
    return query;
  }

  @Override
  protected String buildTextureDataQuery(HashMap<String, MapUnit> mapUnits) {
    String query = "";
    String keyList;

    if ((null != mapUnits) && (mapUnits.size() > 0)) {
      keyList = "(";
      int count = 0;
      for (MapUnit mapunit : mapUnits.values()) {
        LinkedHashMap<String, Component> components = mapunit.components();

        for (Component component : components.values()) {
          LinkedHashMap<String, Horizon> horizons = component.horizons();

          for (Horizon horizon : horizons.values()) {
            if (count > 0) {
              keyList += ", ";
            }
            keyList += horizon.chkey();
            count++;
          }
        }
      }

      if (count > 0) {
        keyList += ") ORDER BY mapunit.mukey, component.cokey, chorizon.chkey, chtexturegrp.chtgkey, chtexture.chtkey;";

        query = "SELECT mapunit.mukey, component.cokey, chorizon.chkey "
            + ", chtexturegrp." + TableTextureGroup.CHTGKEY
            + ", chtexturegrp." + TableTextureGroup.DESCRIPTION
            + ", chtexturegrp." + TableTextureGroup.TEXTURE
            + ", chtexture." + TableTexture.CHTKEY
            + ", chtexture." + TableTexture.TEXTURE_CL
            + ", chtexture." + TableTexture.LIEUTEX
            + ", CASE WHEN UPPER(chtexturegrp." + TableTextureGroup.STRATEXTSFLAG + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.STRATEXTSFLAG
            + ", CASE WHEN UPPER(chtexturegrp." + TableTextureGroup.RVINDICATOR + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.RVINDICATOR
            + " FROM chtexturegrp "
            + "      INNER JOIN chtexture on chtexturegrp." + TableTextureGroup.CHTGKEY + "=chtexture." + TableTexture.CHTGKEY
            + "      INNER JOIN chorizon on chtexturegrp.chkey=chorizon.chkey "
            + "      INNER JOIN component on chorizon.cokey=component.cokey "
            + "      INNER JOIN mapunit on component.mukey=mapunit.mukey "
            + " WHERE chtexturegrp." + TableTextureGroup.CHKEY + " IN " + keyList
            + " ;";
      }
    }
    return query;
  }

  @Override
  protected String buildTextureGroupsQuery(String chkey) {
    return "SELECT " + TableTextureGroup.CHTGKEY + ", " + TableTextureGroup.TEXTURE
        + ", CASE WHEN UPPER(" + TableTextureGroup.STRATEXTSFLAG + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.STRATEXTSFLAG
        + ", CASE WHEN UPPER(" + TableTextureGroup.RVINDICATOR + ")='YES' THEN '1' ELSE '0' END AS " + TableTextureGroup.RVINDICATOR
        + " FROM chtexturegrp WHERE chkey=" + chkey + "; ";
  }

  ////////////////////////////////
//
//  Private member functions
//
////////////////////////////////
  @Override
  protected final boolean isValidDb() {
    boolean ret_val = false;
    String query = "select top 1 mukey from dbo.mupolygon;";
    try (Statement statement = connection.createStatement();) {
      ResultSet results = statement.executeQuery(query);

      if (results.next()) {
        ret_val = true;
      }
    } catch (SQLException ex) {
      //Not a valid SDM database...
      Log(Level.SEVERE, "Database provided to soils_data.mssql_sdm is not a valid SDM instance.  Cannot continue: " + ex.getMessage(), ex);
    }

    return ret_val;
  }

  @Override
  public boolean filterComponentHorizonFragData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, String filter, boolean computeLightleWeesieSlope) throws SQLException, ServiceException {
    return super.filterComponentHorizonFragData(dbResults, filterResults, mapUnit, filter, computeLightleWeesieSlope); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  public boolean filterRusle2ComponentHorizonData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, Component tComponent) throws SQLException, ServiceException {
    return super.filterRusle2ComponentHorizonData(dbResults, filterResults, mapUnit, tComponent); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  public boolean filterWEPSComponentHorizonFragData(ResultSet dbResults, FilterResults filterResults, MapUnit mapUnit, Component tComponent, boolean computeLightleWeesieSlope) throws SQLException, ServiceException {
    return super.filterWEPSComponentHorizonFragData(dbResults, filterResults, mapUnit, tComponent, computeLightleWeesieSlope); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  public boolean findComponentsForMapUnit(MapUnit mapUnit) throws ServiceException {
    throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  public MapUnit findComponentsForMukey(String mukey) throws ServiceException {
    throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
  }

  @Override
  protected String buildValidateCokeyQuery(int cokey) {
    return "SELECT cokey from component WHERE cokey=" + cokey + ";";
  }

  @Override
  protected String buildFindAllByShapeQuery(String WKT, boolean returnIntersectionShape) throws SDMException {
    String query = "";

    TableMapUnit mapUnit = new TableMapUnit();
    TableMuaggatt muaggatt = new TableMuaggatt();
    TableComponent component = new TableComponent();
    TableHorizon horizon = new TableHorizon();
    TableFragments fragments = new TableFragments();
    TableTexture texture = new TableTexture();
    TableTextureGroup textureGroup = new TableTextureGroup();

    ArrayList<String> tableColumns = mapUnit.getColumnList();
    ArrayList<String> asColumnNames = new ArrayList<>();

    query = "SELECT ";

    for (String key : muaggatt.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }

    for (String key : component.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }
    for (String key : horizon.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }
    for (String key : fragments.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }
    for (String key : texture.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }
    for (String key : textureGroup.getColumnList()) {
      if (!tableColumns.contains(key)) {
        tableColumns.add(key);
      }
    }

    for (String key : tableColumns) {
      query += "a." + key + ", ";
    }

    query += " a.saversion, a.saverest, a.spatialversion, a.spatialverest, a.tabularversion, a.tabularverest "
        + ((returnIntersectionShape) ? ", a.intersectPoly" : "");

    query += " FROM "
        + "( SELECT ";

    tableColumns.clear();
    for (String key : mapUnit.getColumnList()) {
      if (key.equalsIgnoreCase("areaname")) {
        tableColumns.add("legend." + key);
      } else {
        if (key.equalsIgnoreCase("areasymbol")) {
          tableColumns.add("legend." + key);
        } else {
          tableColumns.add(mapUnit.getTableName() + "." + key);
        }
      }
      asColumnNames.add(key);
    }
    for (String key : muaggatt.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(muaggatt.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }
    for (String key : component.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(component.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }
    for (String key : horizon.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(horizon.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }
    for (String key : fragments.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(fragments.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }
    for (String key : texture.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(texture.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }
    for (String key : textureGroup.getColumnList()) {
      if (!asColumnNames.contains(key)) {
        tableColumns.add(textureGroup.getTableName() + "." + key);
        asColumnNames.add(key);
      }
    }

    if (tableColumns.size() != asColumnNames.size()) {
      throw new SDMException("Cannot build list of column names.  As list size does not match column list size.");
    }

    for (int i = 0; i < tableColumns.size(); i++) {
      String cName = tableColumns.get(i);
      String asName = asColumnNames.get(i);
      query += cName + " as " + asName;
      query += ", ";
    }

    query += " sac.saversion, sac.saverest, sp.spatialversion, sp.spatialverest, tab.tabularversion, tab.tabularverest "
        + ((returnIntersectionShape) ? ", m.mupolygongeo.STIntersection(geometry::STGeomFromText (' " + WKT + "', " + DB_GEOM_SRID + ")).MakeValid() as intersectPoly " : "")
        + " FROM dbo.mupolygon as m  WITH (index(SI_mupolygon_24876)) "
        + " INNER JOIN dbo.mapunit ON m.mukey=dbo.mapunit.mukey  "
        + " INNER JOIN dbo.legend ON mapunit.lkey=dbo.legend.lkey  "
        + " INNER JOIN dbo.muaggatt ON m.mukey=dbo.muaggatt.mukey  "
        + " INNER JOIN sacatalog sac ON legend.areasymbol = sac.areasymbol  "
        + " INNER JOIN saspatialver sp ON legend.areasymbol = sp.areasymbol  "
        + " INNER JOIN satabularver tab ON legend.areasymbol = tab.areasymbol  "
        + " INNER JOIN dbo.component ON m.mukey=dbo.component.mukey "
        + " INNER JOIN dbo.chorizon on dbo.component.cokey=dbo.chorizon.cokey  "
        + " LEFT OUTER JOIN dbo.chfrags on dbo.chorizon.chkey=dbo.chfrags.chkey  "
        + " INNER JOIN dbo.chtexturegrp on dbo.chorizon.chkey=dbo.chtexturegrp.chkey  "
        + " INNER JOIN dbo.chtexture on dbo.chtexturegrp.chtgkey=dbo.chtexture.chtgkey "
        + " WHERE m.mupolygongeo.STIntersects(geometry::STGeomFromText ('" + WKT + "', " + DB_GEOM_SRID + ")) = 1  "
        + " AND m.mupolygongeo.STIsValid()=1  "
        + " AND (geometry:: STGeomFromText ('" + WKT + "', " + DB_GEOM_SRID + ").STIsValid())=1  "
        + " AND legend.areatypename like 'Non-MLRA%' "
        + " AND UPPER(component.majcompflag)='YES' ";

    query += " ) as a "
        + " ORDER BY mukey, cokey, hzdept_r, chkey, chtgkey;";

    return query;
  }
}