package c;

import csip.ModelDataService;
import csip.ServiceException;
import csip.annotations.*;
import static csip.annotations.ResourceType.JDBC;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ws.rs.Path;


/*
 * 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.
 */
//TODO:  This needs to be moved to the conf file!
@Resource(file = "${conservation_resources.db}",
        env = {"maxWait=10000", "jmxEnabled=false"}, id = "csip.erosion.sqlsvr", type = JDBC)

/**
 *
 * @author Shaun Case
 * @author Wes Lloyd
 */
//These aren't needed since this isn't a service.
@Name("MsSQLGIS")
@Description("SQL SVR db imp")
@VersionInfo("1.1")
@Path("d/sqlsvr/1.0")
public class MsSQLGIS extends ModelDataService implements GIS_DB {

    static final Logger logger = Logger.getLogger(MsSQLGIS.class.getName());
    private Connection connection;

    MsSQLGIS() throws csip.ServiceException, SQLException {
        connection = getResourceJDBC("csip.erosion.sqlsvr");
        if (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
            throw new csip.ServiceException("Invalid connection type passed to MsSQLGIS.  Class requires a connection to a MsSQL server.");
        }
    }

//    MsSQLGIS(Connection conn) throws csip.ServiceException, SQLException {
//        if (null != conn) {
//            if (conn.getMetaData().getDatabaseProductName().contains("Microsoft SQL")) {
//                connection = conn;
//            } else {
//                throw new csip.ServiceException("Invalid connection type passed to MsSQLGIS.  Class requires a connection to a MsSQL server.");
//            }
//        } else {
//            throw new csip.ServiceException("No connection object passed to MsSQLGIS.  Class requires a connection to a MsSQL server.");
//        }
//    }
    private GIS_DB.FileQryResult findFile(String select) throws SQLException {
        GIS_DB.FileQryResult res = null;
        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 3) {
                logger.severe("invalid columns in soil query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from soil query!");
            } else {
                res = new GIS_DB.FileQryResult();
                res.id = r.getString(1);
                res.file_path = r.getString(2);
                res.file_name = r.getString(3);
                logger.info("id=" + res.id);
                logger.info("r2_path=" + res.file_path);
                logger.info("r2_name=" + res.file_name);
            }
        }
        return res;
    }

    private String findCmzName(String select, int colcnt) throws SQLException {
        String ret_val = "0";
        try (Statement s = connection.createStatement()) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != colcnt) {
                logger.severe("invalid number of columns returned in find dbl query:" + select);
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from find dbl query!");
            } else {
                ret_val = r.getString(1);
                logger.info("value=" + ret_val);
            }
            return ret_val;
        }
    }

    private Collection<GIS_DB.Soil> findPolygons(String select) throws SQLException {
        LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();

        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 13) {
                logger.severe("invalid columns in soil query");
                throw new IllegalArgumentException("invalid Columns");
            }

            while (r.next()) {
                GIS_DB.Soil soil = new GIS_DB.Soil();
                soil.coFips = r.getString(1);
                soil.muSym = r.getString(2);
                soil.muKey = r.getString(3);
                soil.r2Path = r.getString(4);
                soil.r2Name = r.getString(5);
                soil.muName = r.getString(6);
                soil.compName = r.getString(7);
                soil.kffact = r.getString(8);
                soil.tfact = r.getDouble(9);
                soil.slopeR = r.getDouble(10);
                soil.coKey = r.getString(11);
                soil.percentAoi = r.getDouble(12);
                soil.sizeAoi = r.getDouble(13);
                soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
                String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
                sInfo += " ,mukey=" + soil.muKey;
                sInfo += " ,r2_path=" + soil.r2Path;
                sInfo += " ,r2_name=" + soil.r2Name;
                logger.info(sInfo);
                soilLst.add(soil);
            }
        }
        return soilLst;
    }

    private Collection<GIS_DB.Soil> findPolygonsWithSand(String select) throws SQLException {
        LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();
        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            SQLWarning warning = s.getWarnings();
            if (warning != null) {
                String sqlWarning = warning.getMessage();
                logger.info("**************************************************SQL WARNINGS! executing polygon with sand query");
                logger.info("**************************************************message=" + sqlWarning);
                logger.info("**************************************************sql-state=" + warning.getSQLState());
                logger.info("**************************************************error-code=" + warning.getErrorCode());
                if ((sqlWarning != null) && (sqlWarning.length() > 0)) {
                    throw new SQLException(sqlWarning);
                }
            } else {
                logger.info("**************************************************NO SQL WARNINGS! executing polygon with sand query");
            }
            int cols = r.getMetaData().getColumnCount();
            if (cols != 14) {
                logger.severe("invalid columns in soil query");
                throw new IllegalArgumentException("invalid Columns");
            }

            while (r.next()) {
                GIS_DB.Soil soil = new GIS_DB.Soil();
                soil.coFips = r.getString(1);
                soil.muSym = r.getString(2);
                soil.muKey = r.getString(3);
                soil.r2Path = r.getString(4);
                soil.r2Name = r.getString(5);
                soil.muName = r.getString(6);
                soil.compName = r.getString(7);
                soil.kffact = r.getString(8);
                soil.tfact = r.getDouble(9);
                soil.slopeR = r.getDouble(10);
                soil.coKey = r.getString(11);
                soil.sandtotalR = Double.parseDouble(r.getString(12));  // issues here with precision from ssurgo are avoided by converting the string value to double
                soil.percentAoi = r.getDouble(13);
                soil.sizeAoi = r.getDouble(14);
                soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
                String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
                sInfo += " ,mukey=" + soil.muKey;
                sInfo += " ,r2_path=" + soil.r2Path;
                sInfo += " ,r2_name=" + soil.r2Name;
                logger.info(sInfo);
                soilLst.add(soil);
            }
        }

        return soilLst;
    }

    private Collection<GIS_DB.Soil> findPolygonsWeps(String select) throws SQLException {
        LinkedList<GIS_DB.Soil> soilLst = new LinkedList<GIS_DB.Soil>();
        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 13) {
                logger.severe("invalid columns in soil query");
                throw new IllegalArgumentException("invalid Columns");
            }

            while (r.next()) {
                GIS_DB.Soil soil = new GIS_DB.Soil();
                soil.coFips = r.getString(1);
                soil.muSym = r.getString(2);
                soil.muKey = r.getString(3);
                soil.wepsPath = r.getString(4);
                soil.wepsName = r.getString(5);
                soil.muName = r.getString(6);
                soil.compName = r.getString(7);
                soil.kffact = r.getString(8);
                soil.tfact = r.getDouble(9);
                soil.slopeR = r.getDouble(10);
                soil.coKey = r.getString(11);
                //soil.sandtotalR = r.getDouble(12);
                soil.percentAoi = r.getDouble(12);
                soil.sizeAoi = r.getDouble(13);
                soil.lengthR = calculateSlopeR(soil.slopeR, soil.coFips);
                String sInfo = "ADDING SOIL TO LIST cokey=" + soil.coKey;
                sInfo += " ,mukey=" + soil.muKey;
                sInfo += " ,weps_path=" + soil.wepsPath;
                sInfo += " ,weps_name=" + soil.wepsName;
                logger.info(sInfo);
                soilLst.add(soil);
            }
        }

        return soilLst;
    }

    private double calculateSlopeR(double slope_r, String areaSymbol) {
        double lengthR = 0.0;

        // Determines if the soil survey area is in the Palouse region
        if (PalouseAreas.contains(areaSymbol)) // Use Palouse region slope length lookup table.
        {
            for (int i = 0; i < PalouseSlopeLength.length; i++) {
                if (PalouseSlopeLength[i][1] != -1) {
                    if ((slope_r >= PalouseSlopeLength[i][0]) && (slope_r < PalouseSlopeLength[i][1])) {
                        lengthR = PalouseSlopeLength[i][2];
                        break;
                    } else if (slope_r >= PalouseSlopeLength[i][0]) {
                        lengthR = PalouseSlopeLength[i][2];
                        break;
                    }
                }
            }
        } else // Uses Lightle and Weesies 10/1/1996 slope length lookup table (see documentation).
        // Not for Palouse region
        {
            for (int i = 0; i < LightleWeesiesSlopeLength.length; i++) {
//                logger.info("LightleWeesiesSlopeLength[" + i + "][0]=" + LightleWeesiesSlopeLength[i][0]);
//                logger.info("LightleWeesiesSlopeLength[" + i + "][1]=" + LightleWeesiesSlopeLength[i][1]);
//                logger.info("LightleWeesiesSlopeLength[" + i + "][2]=" + LightleWeesiesSlopeLength[i][2]);
//                logger.info("slope_r=" + slope_r);
//                logger.info("expr val=" + ((slope_r >= LightleWeesiesSlopeLength[i][0]) && (slope_r < LightleWeesiesSlopeLength[i][1])));
                if (LightleWeesiesSlopeLength[i][1] != -1) {
                    if ((slope_r >= LightleWeesiesSlopeLength[i][0]) && (slope_r < LightleWeesiesSlopeLength[i][1])) {
                        lengthR = LightleWeesiesSlopeLength[i][2];
//                        logger.info("grabbing value now from double check");
                        break;
                    }
                } else if (slope_r >= LightleWeesiesSlopeLength[i][0]) {
                    lengthR = LightleWeesiesSlopeLength[i][2];
//                        logger.info("grabbing value now from SINGLE check");
                    break;
                }
            }
        }
        return lengthR;
    }

    private boolean isValidCoord(String select) throws SQLException {
        boolean ret_val = false;
        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 1) {
                throw new IllegalArgumentException("invalid Columns");
            }
            if (r.next()) {
                String sValidCoord = r.getString(1).trim();
                ret_val = (sValidCoord.contains("1"));
            }

        }
        return ret_val;
    }

    @Override
    public boolean IsValidCliCoord(double lat, double lon) throws SQLException {
        return isValidCoord("select count(1) where exists (select 1 where exists (select top 1 cli_key "
                + "from r2gis.cli_geom with (Index(cli_geom_idx)) "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1));");
    }

    @Override
    public boolean IsValidManCoord(double lat, double lon) throws SQLException {
        return isValidCoord("select count(1) where exists (select top 1 co_cmz "
                + "from r2gis.cmz_geom "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1);");
    }

    @Override
    public boolean IsValidSoiCoord(double lat, double lon) throws SQLException {
        return isValidCoord("select count(1) where exists (select top 1 areasymbol "
                + "from ssurgo.soilmu_a with (Index(geom_sidx)) "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(the_geom) = 1);");
    }

    @Override
    public boolean IsValidGisCoord(double lat, double lon) throws SQLException {
        return isValidCoord("select count(1) where exists (select top 1 co_cmz "
                + "from r2gis.cmz_geom "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1) and exists "
                + "(select 1 where exists (select top 1 cli_key "
                + "from r2gis.cli_geom with (Index(cli_geom_idx)) "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(geometry) = 1)) "
                + "and exists "
                + "(select 1 where exists (select top 1 areasymbol "
                + "from ssurgo.soilmu_a with (Index(geom_sidx)) "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(the_geom) = 1));");
    }

    @Override
    public boolean IsValidRusle2Soil(double lat, double lon) throws SQLException {
        GIS_DB.FileQryResult fqr = findSoils(lat, lon);
        return fqr != null;
    }

    @Override
    public boolean IsValidWepsSoil(double lat, double lon) throws SQLException {
        GIS_DB.FileQryResult fqr = findSoilsWeps(lat, lon);
        return fqr != null;
    }

    @Override
    // Determine if a point is within the wind station interpolation boundary for WEPS
    public boolean IsInInterpolateBoundary(double lat, double lon) throws SQLException {
        return isValidCoord("SELECT geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin(the_geom)  FROM r2gis.weps_int_boundary;");
    }

    @Override
    public GIS_DB.FileQryResult findSoils(double lat, double lon) throws SQLException {
        return findFile("SELECT top 1 m.musym co_fips, r2_path, r2_name FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils AS m "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")',0).STWithin(g.the_geom) = 1 "
                + "AND g.mukey = m.mukey;");
    }

    @Override
    //
    // TODO:  fix me
    //
    // added string matching to fix a problem which requires us to regenerate r2 soils
    public GIS_DB.FileQryResult findSoilsByCokey(String cokey, double lon) throws SQLException {
        return findFile("SELECT distinct m.musym co_fips, cast(r2_path as varchar(1200)) r2_path, cast(r2_name as varchar(1200)) r2_name FROM ssurgo.soilmu_a AS g, r2gis.map_soils AS m "
                + "WHERE m.cokey='" + cokey + "' "
                + "AND g.mukey = m.mukey and m.r2_name like '%' + m.compname + '%';");
    }

    @Override
    public Collection<GIS_DB.Soil> findSoilsForPolygon(String _polygon, double poly_longitude) throws SQLException {
        logger.info("The input polygon is=" + _polygon);
        logger.info("The input polygon len len =" + _polygon.length());
        if ((_polygon == null) || (_polygon.length() < 1)) {
            throw new SQLException("Empty polygon specified to findSoilsForPolygon, SQL Error.");
        }

//TODO:  Validate this query in MsSQL...
        String select = "SELECT m.co_fips, m.musym, m.mukey, r2_path, r2_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, "
                + "((geography::STGeomFromText(geometry.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
                + "geography::STGeomFromText(geometry.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
                + "FROM soil_geom AS g with (Index(geom_sidx)), map_soils AS m "
                + "WHERE g.geometry.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
                + "AND g.mukey = m.mukey "
                + "AND geometry.STIsValid() = 1;";

        return findPolygons(select);
    }

    private String getOpenGISPolygon(String _polygon) throws SQLException {
        String ret_val = "";
        try (Statement s = connection.createStatement();) {
            String select = "SELECT CASE WHEN geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().EnvelopeAngle() > 90 THEN geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().ReorientObject().STAsText() ELSE geography::STGeomFromText('" + _polygon + "', 4326).MakeValid().STAsText() END as openGISRotation;";

            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);

            if (r.next()) {
                ret_val = r.getString("openGISRotation");
            }
        }

        return ret_val;
    }

// To calculate area in acres, we use the st_transform function to transform the intersection polygon into
    // a NAD83 Kansas projection which has units in feet (not degrees), which produces square feet.
    // We then divide by 43,560 sq ft to get acres.
    @Override
    public Collection<GIS_DB.Soil> findSoilsForPolygonWithSand(String _polygon, double poly_longitude) throws SQLException {
        String polygon = "ST_PolygonFromText('POLYGON((-90.111 38.19,-90.110 38.19, -90.110 38.18,-90.111 38.18, -90.111 38.19))')";
        logger.info("The input polygon is=" + _polygon);
        logger.info("The input polygon len len =" + _polygon.length());
        if ((_polygon == null) || (_polygon.length() < 1)) {
            _polygon = polygon;
            poly_longitude = -90.111;
        }
        // added a fix "and m.r2_name like '%' || m.compname || '%'" to the end of this query because
        // soils have duplicate records presently, and need to be regenerated...
//TODO:  Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL.        

        String select = "SELECT m.co_fips, m.musym, m.mukey, r2_path, r2_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, m.sandtotal_r, "
                + "((geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
                + "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
                + "FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils AS m "
                + "WHERE g.the_geom.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
                + "AND g.mukey = m.mukey "
                + "AND the_geom.STIsValid() = 1 "
                + "and m.r2_name like '%' + m.compname + '%';";
        return findPolygonsWithSand(select);
//        return findPolygonsWithSand("SELECT m.co_fips, m.musym, m.mukey, r2_path, r2_name, m.muname, "
//                + "m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, m.sandtotal_r, "
//                + "st_area(st_intersection(" + _polygon
//                + ", geometry)) / st_area(" + _polygon
//                + ")*100 as percentAoi,"
//                + "st_area(st_transform(st_intersection(" + _polygon
//                + ", geometry),3541))/43560 as sizeAoi "
//                + "FROM soil_geom AS g, map_soils AS m "
//                + "WHERE ST_Intersects(" + _polygon
//                + ", g.geometry) "
//                + "AND g.mukey = m.mukey "
//                + "and st_isvalid(geometry)='t' "
//                + "and st_isvalid(" + _polygon
//                + ")='t' and m.r2_name like '%' || m.compname || '%';");
    }

    @Override
// To calculate area in acres, we use the st_transform function to transform the intersection polygon into
    // a NAD83 Kansas projection which has units in feet (not degrees), which produces square feet.
    // We then divide by 43,560 sq ft to get acres.
    public Collection<GIS_DB.Soil> findSoilsForPolygonWeps(String _polygon, double poly_longitude) throws SQLException {
//        String polygon = "ST_PolygonFromText('POLYGON((-90.111 38.19,-90.110 38.19, -90.110 38.18,-90.111 38.18, -90.111 38.19))')";
        logger.info("find weps soils - The input polygon is=" + _polygon);
        logger.info("The input polygon len len =" + _polygon.length());
        if ((_polygon == null) || (_polygon.length() < 1)) {
            throw new SQLException("no polygon provided for WEPS soil query!");
//            _polygon = polygon;
//            poly_longitude = -90.111;
        }
        //TODO:  Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL.

        String select = "SELECT m.co_fips, m.musym, m.mukey, weps_path, weps_name, m.muname, m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, "
                + "((geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea()) / (geography::STGeomFromText('" + getOpenGISPolygon(_polygon) + "', 4326 ).MakeValid().STArea() )) * 100.0 as percentAoi, "
                + "geography::STGeomFromText(the_geom.STIntersection( geometry::STGeomFromText('" + _polygon + "', 0)).MakeValid().STAsText(), 4326).MakeValid().STArea() / 4046.86 as sizeAoi "
                + "FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils_weps AS m "
                + "WHERE g.the_geom.STIntersects(geometry::STGeomFromText('" + _polygon + "',0)) = 1 "
                + "AND g.mukey = m.mukey "
                + "AND the_geom.STIsValid() = 1;";
        return findPolygonsWeps(select);
//        return findPolygonsWeps("SELECT m.co_fips, m.musym, m.mukey, weps_path, weps_name, m.muname, "
//                + "m.compname, m.kffact, m.tfact, m.slope_r, m.cokey, "
//                + "st_area(st_intersection(" + _polygon
//                + ", geometry)) / st_area(" + _polygon
//                + ")*100 as percentAoi,"
//                + "st_area(st_transform(st_intersection(" + _polygon
//                + ", geometry),3541))/43560 as sizeAoi "
//                + "FROM soil_geom AS g, map_soils_weps AS m "
//                + "WHERE ST_Intersects(" + _polygon
//                + ", g.geometry) "
//                + "AND g.mukey = m.mukey "
//                + "and st_isvalid(geometry)='t' "
//                + "and st_isvalid(" + _polygon
//                + ")='t'");
    }

    @Override
    public GIS_DB.FileQryResult findSoilsWeps(double lat, double lon) throws SQLException {
        return findFile("SELECT TOP 1 m.musym co_fips, weps_path, weps_name FROM ssurgo.soilmu_a AS g with (Index(geom_sidx)), r2gis.map_soils_weps AS m "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.the_geom ) = 1 "
                + "AND g.mukey = m.mukey;");
    }

    @Override
    public GIS_DB.FileQryResult findSoilsWepsByCokey(String cokey, double lon) throws SQLException {
        return findFile("SELECT TOP 1 m.musym co_fips, weps_path, weps_name FROM ssurgo.soilmu_a AS g, r2gis.map_soils_weps AS m "
                + "WHERE m.cokey='" + cokey + "' "
                + "AND g.mukey = m.mukey;");
    }

    @Override
    //
    // 09-03-2013 WJL 
    // Added the "DESC" to sort values in order to favor REQ for IDAHO and RMOD for COLORADO
    //
    // Basically these climate files (rmod, req, etc.), where they exist, should always be returned in favor of the just "R" ones...
    public GIS_DB.FileQryResult findClimate(double lat, double lon) throws SQLException {
        return findFile("SELECT top 1 m.co_fips, r2_path, r2_name  FROM r2gis.cli_geom as g WITH (Index(cli_geom_idx)), r2gis.map_climates AS m "
                + "WHERE geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.geometry ) = 1 "
                + "AND g.co_fips = m.co_fips "
                + "AND ((g.ei_rang=m.ei_rang) or (g.ei_rang = ''))"
                + "ORDER BY cast(m.r2_name as varchar(1200)) desc;");
    }

    @Override
    /**
     * New and improved version of findCounty. Looks in weps_county_geom if not
     * found in cnty_geom. This works for the city/county locations that have no
     * real county.
     */
    public GIS_DB.County findWEPSCounty(double lat, double lon) throws SQLException, ServiceException {
        GIS_DB.County cnty = null;

        try (Statement s = connection.createStatement();) {
            String select = "SELECT m.st_abbr, m.co_numb, m.co_name FROM r2gis.cnty_geom as g, r2gis.cnty_data AS m "
                    + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.geometry ) = 1 "
                    + "AND g.co_fips = m.co_fips;";

            ResultSet r = s.executeQuery(select);
            if (r.next()) {
                cnty = new GIS_DB.County();
                cnty.st_abbr = r.getString("st_abbr");
                cnty.county_code = r.getString("co_numb");
                cnty.name = r.getString("co_name");
                logger.info("state abbr=" + cnty.st_abbr + " county code=" + cnty.county_code + " county name=" + cnty.name);
            } else {
                r.close();
                select = "select top 1 st_abbr, county, name from r2gis.weps_county_geom "
                        + " INNER JOIN r2gis.cnty_data on weps_county_geom.state=cnty_data.st_numb "
                        + " WHERE geometry.STContains(geometry::STPointFromText('POINT( " + lon + " " + lat + " )', 4326))=1;";
                logger.info("query sql=" + select);
                r = s.executeQuery(select);
                if (r.next()) {
                    cnty = new GIS_DB.County();
                    cnty.st_abbr = r.getString("st_abbr");
                    cnty.county_code = r.getString("county");
                    cnty.name = r.getString("name");
                    logger.info("state abbr=" + cnty.st_abbr + " county code=" + cnty.county_code + " county name=" + cnty.name);
                } else {
                    logger.severe("No county located in WEPS County query for that point: POINT( " + lon + " " + lat + " )");
                    throw new ServiceException("No county located in WEPS County query for that point: POINT( " + lon + " " + lat + " )");
                }
            }
        }
        return cnty;
    }

    @Override
    public GIS_DB.County findCounty(double lat, double lon) throws SQLException, ServiceException {
        GIS_DB.County cnty = null;
        String select = "SELECT m.st_abbr, m.co_numb, m.co_name FROM r2gis.cnty_geom as g, r2gis.cnty_data AS m "
                + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( g.geometry ) = 1 "
                + "AND g.co_fips = m.co_fips;";
        try (Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 3) {
                logger.severe("invalid columns in cligen station query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from cligen station query!");
                throw new ServiceException("no result from cligen station query!");
            } else {
                cnty = new GIS_DB.County();
                cnty.st_abbr = r.getString(1);
                cnty.county_code = r.getString(2);
                cnty.name = r.getString(3);
                //s.close();
                logger.info("state abbr=" + cnty.st_abbr + " county code=" + cnty.county_code + " county name=" + cnty.name);
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
            throw ex;
        }
        return cnty;
    }

    @Override
    public GIS_DB.StationResult findCligenStation(double lat, double lon) throws SQLException {
        GIS_DB.StationResult cligenst = null;
        String select = "SELECT top 1 s.st_numb, s.station_id, point.STX, point.STY, s.elevation, point.STDistance(geometry::STPointFromText('POINT(" + lon + " " + lat + ")',4326)) as distance, s.name "
                + "FROM r2gis.cligen_stations as s "
                + "order by distance;";
        try (Connection connection = getResourceJDBC("csip.erosion.sqlsvr"); Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 7) {
                logger.severe("invalid columns in cligen station query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from cligen station query!");
            } else {
                cligenst = new GIS_DB.StationResult();
                cligenst.state = Integer.toString(r.getInt(1));
                cligenst.stationId = Integer.toString(r.getInt(2));
                cligenst.stationX = r.getString(3);
                cligenst.stationY = r.getString(4);
                cligenst.elevation = r.getString(5);
                cligenst.distance = r.getDouble(6);
                cligenst.name = (r.getString(7) != null ? r.getString(7) : "NAME_UNAVAILABLE-LOOKUP-ID#" + r.getString(2));
                logger.info("state number=" + cligenst.state + " state-result-set=" + r.getString(1) + " station id=" + cligenst.stationId + " stationid-resultset=" + r.getString(2) + " station_X=" + cligenst.stationX + " stationY=" + cligenst.stationY + " name=" + cligenst.name);
                logger.info("distance in deg=" + cligenst.distance);
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
        }
        return cligenst;
    }

    @Override
    public GIS_DB.StationResult findWindgenStation(double lat, double lon) throws SQLException {
        GIS_DB.StationResult windgenst = null;
        String select = "SELECT top 1 st_numb, station_id, geometry.STDistance(geometry::STPointFromText('POINT(" + lon + " " + lat + ")',4326)) as distance "
                + "FROM r2gis.windgen_stations "
                + "order by distance; ";
        try (Connection connection = getResourceJDBC("csip.erosion.sqlsvr"); Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 3) {
                logger.severe("invalid columns in windgen station query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from windgen station query!");
            } else {
                windgenst = new GIS_DB.StationResult();
                windgenst.state = r.getString(1);
                windgenst.stationId = Integer.toString(r.getInt(2));
                windgenst.distance = r.getDouble(3);
                //s.close(); 
                logger.info("state=" + windgenst.state);
                logger.info("station id=" + windgenst.stationId + "  rs.getstr=" + r.getString(2));
                logger.info("distance in deg=" + windgenst.distance);
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
        }
        return windgenst;
    }

    @Override
    public GIS_DB.StationResult cliGeomIntersect(double lat, double lon) throws SQLException {
        GIS_DB.StationResult clist = null;
        String select = "select st, name, st_fips, c1, lat_dd, long_dd, elev_m "
                + "from r2gis.weps_cli_geom "
                + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_cli_geom.geometry ) = 1;";

        try (Connection connection = getResourceJDBC("csip.erosion.sqlsvr"); Statement s = connection.createStatement()) {
            logger.info("query sql=" + select);
            try (ResultSet r = s.executeQuery(select)) {
                int cols = r.getMetaData().getColumnCount();
                if (cols != 7) {
                    logger.severe("invalid columns in cliGeomIntersect query");
                    throw new IllegalArgumentException("invalid Columns");
                }
                if (!r.next()) {
                    logger.severe("no result from cliGeomIntersect query!");
                } else {
                    clist = new GIS_DB.StationResult();
                    // Result set is getting a float for clist.stationId and clist.state
                    clist.state = Integer.toString(r.getInt(3));
                    clist.name = r.getString(2);
                    clist.stationId = Integer.toString(r.getInt(4));
                    clist.stationY = r.getString(5);
                    clist.stationX = r.getString(6);
                    clist.elevation = r.getString(7);

                    logger.info("state=" + clist.state + " resultset str-value=" + r.getString(3));
                    logger.info("cli station id=" + clist.stationId + " resultset str-value=" + r.getString(4));
                    logger.info("cli station name=" + clist.name);
                    logger.info("cli station elev=" + clist.elevation);
                }
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
        }
        return clist;
    }

    @Override
    public GIS_DB.StationResult windGeomIntersect(double lat, double lon) throws SQLException {
        GIS_DB.StationResult windgenst = null;
        String select = "select lat_dd, long_dd, wban, cntry, st, name "
                + "from r2gis.weps_wind_geom "
                + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_wind_geom.geometry ) = 1;";

        try (Connection connection = getResourceJDBC("csip.erosion.sqlsvr"); Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 6) {
                //s.close();
                logger.severe("invalid columns in windGeomIntersect query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                //s.close();
                logger.severe("no result from windGeomIntersect query!");
            } else {
                windgenst = new GIS_DB.StationResult();
                windgenst.stationX = r.getString(2);
                windgenst.stationY = r.getString(1);
                windgenst.stationId = Integer.toString(r.getInt(3));
                windgenst.country = r.getString(4);
                windgenst.state = r.getString(5);
                windgenst.name = r.getString(6);

                logger.info("state=" + windgenst.state);
                logger.info("station id=" + windgenst.stationId + "  rs.getStr=" + r.getString(3));
                logger.info("station name=" + windgenst.name);
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
        }
        return windgenst;
    }

    @Override
    // Uses a field lat long, to determine the county, then returns the county's centroid lat/long
    public GIS_DB.County countyCentroid(double lat, double lon) throws SQLException {
        GIS_DB.County county = null;
        String select = "select state, county, name, geometry.STCentroid().STX centroid_long, geometry.STCentroid().STY centroid_lat "
                + "from r2gis.weps_county_geom "
                + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 4326).STWithin( r2gis.weps_county_geom.geometry ) = 1;";

        try (Connection connection = getResourceJDBC("csip.erosion.sqlsvr"); Statement s = connection.createStatement();) {
            logger.info("query sql=" + select);
            ResultSet r = s.executeQuery(select);
            int cols = r.getMetaData().getColumnCount();
            if (cols != 5) {
                logger.severe("invalid columns in countyCentroid query");
                throw new IllegalArgumentException("invalid Columns");
            }
            if (!r.next()) {
                logger.severe("no result from countyCentroid query!");
            } else {
                county = new GIS_DB.County();
                county.st_abbr = r.getString("state");
                county.county_code = r.getString("county");
                county.name = r.getString("name");
                county.county_centroid_X = r.getString("centroid_long");
                county.county_centroid_Y = r.getString("centroid_lat");

                logger.info("county_fips_code=" + county.st_abbr + county.county_code);
                logger.info("name=" + county.name);
                logger.info("centroidX=" + county.county_centroid_X);
                logger.info("centroidY=" + county.county_centroid_Y);
            }
        } catch (SQLException | ServiceException ex) {
            LOG.log(Level.SEVERE, null, ex);
        }
        return county;
    }

    @Override
    public String findCmz(double lat, double lon) throws SQLException {
        return findCmzName("select distinct TOP 1 co_cmz from r2gis.cmz_geom "
                + "where geometry::STPointFromText('POINT(" + lon + " " + lat + ")', 0).STWithin( r2gis.cmz_geom.geometry ) = 1;", 1);
    }

    @Override
    @Deprecated
    public List<GIS_DB.FileQryResult> findManagements(double lat, double lon) throws SQLException {
        List<GIS_DB.FileQryResult> l = new ArrayList<GIS_DB.FileQryResult>();
//
//        try (Statement s = connection.createStatement();) {
//            //TODO:  Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL. 
//            ResultSet r = s.executeQuery("SELECT co_cmz, r2_path, r2_name FROM man_names WHERE co_cmz= (SELECT distinct co_cmz FROM cmz_geom "
//                    + "WHERE Within(GeomFromText( 'POINT(" + lon + " "
//                    + lat + ")' ), geometry) )  ORDER BY usage DESC  LIMIT 10;");
//
//            int cols = r.getMetaData().getColumnCount();
//            if (cols != 3) {
//                throw new IllegalArgumentException("invalid Columns");
//            }
//
//            while (r.next()) {
//                GIS_DB.FileQryResult res = new GIS_DB.FileQryResult();
//                res.id = r.getString(1);
//                res.file_path = r.getString(2);
//                res.file_name = r.getString(3);
//                l.add(res);
//            }
//        }
//
        return l;
    }

    @Override
    @Deprecated
    public List<GIS_DB.FileQryResult> findManagementsFilter(double lat, double lon, String filter1, String filter2) throws SQLException {
        List<GIS_DB.FileQryResult> l = new ArrayList<GIS_DB.FileQryResult>();
//        try (Statement s = connection.createStatement();) {
//            //TODO:  Check this query in MsSQL...Fix the ST_ functions to match those found in MsSQL.         
//            String sSql = "SELECT co_cmz, r2_path, r2_name FROM man_names WHERE co_cmz= (SELECT distinct co_cmz FROM cmz_geom "
//                    + "WHERE Within(GeomFromText( 'POINT(" + lon + " "
//                    + lat + ")' ), geometry) ) ";
//
//            if (filter1.length() > 0) {
//                sSql += " and (lower(r2_path) like '%" + filter1 + "%' or lower(r2_name) like '%" + filter1 + "%') ";
//            }
//            if (filter2.length() > 0) {
//                sSql += " and (lower(r2_path) like '%" + filter2 + "%' or lower(r2_name) like '%" + filter2 + "%') ";
//            }
//
//            sSql += " ORDER BY usage DESC  LIMIT 100;";
//
//            ResultSet r = s.executeQuery(sSql);
//            int cols = r.getMetaData().getColumnCount();
//            if (cols != 3) {
//                //s.close();
//                throw new IllegalArgumentException("invalid Columns");
//            }
//
//            while (r.next()) {
//                GIS_DB.FileQryResult res = new GIS_DB.FileQryResult();
//                res.id = r.getString(1);
//                res.file_path = r.getString(2);
//                res.file_name = r.getString(3);
//                l.add(res);
//            }
//        }
        return l;
    }

    @Override
    public void close() throws SQLException {
        if (null != connection) {
            connection.close();
        }
    }

}