AHATcrdb.java [src/java/database/ahat] 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 database.ahat;
import csip.ServiceException;
import csip.SessionLogger;
import csip.annotations.Resource;
import csip.utils.Binaries;
import database.DBResources;
import gisobjects.GISObject;
import gisobjects.GISObjectException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import static database.DBResources.CRDB;
/**
*
* @author Brad
* @author <a href="mailto:shaun.case@colostate.edu">Shaun Case</a>
*/
@Resource(from = DBResources.class)
public class AHATcrdb implements AutoCloseable {
public final static int MAX_DISTANCE_METERS = 20000; // 20Km
private final static String SELECT_ALL_ANIMAL_TYPES
= "Select ahat_animal_type_id, ahat_animal_type_name, ahat_animal_type_display FROM energy.d_AHAT_animal_type where active_record_ind='Y' order by ahat_animal_type_name";
private final static String SELECT_ANIMAL_TYPE_DISPLAY
= "Select ahat_animal_type_display FROM energy.d_AHAT_animal_type where ahat_animal_type_id=?";
private final static String SELECT_LIGHTING_TYPE_BASE_FACTOR
= "select ahat_grouping_base_factor FROM energy.d_AHAT_light_grouping_base_uf WHERE ahat_lighting_type_id=? and ahat_regime_type_id=?";
private final static String SELECT_LIGHTING_TYPE_WORSE_BASE_FACTOR_FOR_GROUP
= "select MAX(ahat_grouping_base_factor) as max_ahat_grouping_base_factor FROM energy.d_AHAT_light_grouping_base_uf WHERE ahat_lighting_type_id=? and ahat_regime_type_id=?";
private final static String SELECT_LIGHTING_TYPE_BEST_BASE_FACTOR_FOR_GROUP
= "select MIN(ahat_grouping_base_factor) and min_ahat_grouping_base_factor FROM energy.d_AHAT_light_grouping_base_uf WHERE ahat_lighting_type_id=? and ahat_regime_type_id=?";
private final static String SELECT_LIGHTING_TYPE_USE_FACTOR
= "select ahat_grouping_use_factor FROM energy.d_AHAT_light_grouping_base_uf WHERE ahat_lighting_type_id=? ahat_regime_type_id=?";
private final static String SELECT_DAIRY_LIGHTING_EFFICENT_UF_FOR_GROUP
= "select TOP 1 ahat_animal_lighting_use_factor FROM energy.d_AHAT_animal_lighting_use_factor where ahat_animal_type_id=1 and ahat_lighting_type_id in (?)";
private final static String SELECT_LIGHTING_TYPE_EFFICIENT_USE_FACTOR_ONE
= "select TOP 1 ahat_grouping_use_factor, (SELECT ahat_grouping_type_id FROM energy.d_AHAT_animal_lighting_use_factor where ahat_grouping_type_id > 0 AND ahat_lighting_type_id=?";
private final static String SELECT_LIGHTING_TYPE_EFFICIENT_USE_FACTOR_TWO
= ") as group_id FROM energy.d_AHAT_light_grouping_base_uf ORDER BY ahat_grouping_use_factor";
private final static String SELECT_LIGHTING_TYPE_DETAIL_FOR_ID
= "Select ahat_lighting_type_id, ahat_lighting_type_name, ahat_lighting_type_display, ahat_lighting_type_use_factor FROM energy.d_AHAT_lighting_type where active_record_ind='Y' AND d_ahat_lighting_type_id=?";
private final static String SELECT_GROUP_ID_FOR_DAIRY_LIGHTING
= "SELECT ahat_grouping_type_id FROM energy.d_AHAT_animal_lighting_use_factor where ahat_grouping_type_id > 0 AND ahat_animal_type_id=1 AND ahat_lighting_type_id=?";
private final static String SELECT_ENERGY_COST_FOR_ENERGY_ID
= "Select ahat_energy_type_cost FROM energy.d_AHAT_energy_type where ahat_energy_type_id=?";
private final static String SELECT_FARROW_HEATING_USE_FACTOR_FOR_ID
= "Select ahat_farrow_heating_use_factor FROM energy.d_AHAT_farrow_heating_use_factor where ahat_farrow_heating_key=?";
private final static String SELECT_POULTRY_HEATING_USE_FACTOR_FOR_ID
= "Select ahat_poultry_heating_uf FROM energy.d_AHAT_poultry_heating_uf where ahat_poultry_heating_uf_key=?";
private final static String SELECT_HEATING_USE_FACTOR_FOR_ID
= "Select ahat_heating_use_factor FROM energy.d_AHAT_heating_use_factor where ahat_heating_use_factor_key=?";
private final static String SELECT_LIGHTING_TYPE_USE_FACTOR_FOR_ID
= "Select ahat_animal_lighting_use_factor FROM energy.d_AHAT_animal_lighting_use_factor where ahat_lighting_type_id=? AND ahat_animal_type_id=?";
private final static String SELECT_MILK_COOLING_USE_FACTOR_FOR_KEY
= "Select ahat_milk_cooling_use_factor FROM energy.d_AHAT_milk_cooling_use_factor where ahat_milk_cooling_key=?";
private final static String SELECT_VENTILATION_USE_FACTOR_FOR_ID
= "Select ahat_ventilation_use_factor FROM energy.d_AHAT_ventilation_use_factor where ahat_ventilation_use_factor_key=?";
private final static String SELECT_VENTILATION_BEST_OPTIONS_FOR_ID
= "select ahat_ventilation_use_factor_key, ahat_ventilation_use_factor, ahat_ventilation_use_factor_name FROM energy.d_ahat_ventilation_use_factor where ahat_ventilation_use_factor < (select uf.ahat_ventilation_use_factor from d_ahat_ventilation_use_factor uf where uf.ahat_ventilation_use_factor_key=? and ahat_ventilation_use_factor_key in (?) order by ahat_ventilation_use_factor asc;";
private final static String SELECT_MILK_COOLING_BEST_OPTIONS_FOR_ID
= "select ahat_milk_cooling_key, ahat_milk_cooling_use_factor, ahat_milk_cooling_key_name FROM energy.d_ahat_milk_cooling_use_factor where ahat_milk_cooling_use_factor < (select uf.ahat_milk_cooling_use_factor from d_ahat_milk_cooling_use_factor uf where uf.ahat_milk_cooling_key=?) order by ahat_milk_cooling_use_factor asc;";
private final static String SELECT_FARROW_HEATING_BEST_OPTIONS_FOR_ID
= "select ahat_farrow_heating_key, ahat_farrow_heating_use_factor, ahat_farrow_heating_name FROM energy.d_ahat_farrow_heating_use_factor where ahat_farrow_heating_use_factor < (select uf.ahat_farrow_heating_use_factor from d_ahat_farrow_heating_use_factor uf where uf.ahat_farrow_heating_key=? and ahat_farrow_heating_key in (?) order by ahat_farrow_heating_use_factor asc ;";
private final static String SELECT_HEATING_BEST_OPTIONS_FOR_ID
= "select ahat_poultry_heating_uf_key, ahat_poultry_heating_uf, ahat_poultry_heating_uf_name FROM energy.d_ahat_poultry_heating_uf where ahat_poultry_heating_uf < (select uf.ahat_poultry_heating_uf from d_ahat_poultry_heating_uf uf where uf.ahat_poultry_heating_uf_key=? ";
private final static String SELECT_LIGHTING_TYPES_FOR_ANIMAL
= "SELECT uf.ahat_animal_lighting_sort_order, l.ahat_lighting_type_id, l.ahat_lighting_type_name, l.ahat_lighting_type_display FROM energy.d_AHAT_lighting_type l "
+ " JOIN d_ahat_animal_lighting_use_factor uf on l.ahat_lighting_type_id = uf.ahat_lighting_type_id WHERE l.active_record_ind='Y' and uf.ahat_animal_type_id=? order by uf.ahat_animal_lighting_sort_order";
private final static String SELECT_HEATING_TYPES_FOR_ANIMAL_HOUSING
= "SELECT h.ahat_heating_type_id, h.ahat_heating_type_name, h.ahat_heating_type_display FROM energy.d_AHAT_heating_type h "
+ "WHERE h.active_record_ind='Y' ORDER BY h.ahat_heating_sort_order";
private final static String SELECT_HEATING_TYPE_NAME_FOR_ID
= "SELECT ahat_heating_type_name FROM energy.d_AHAT_heating_type WHERE ahat_heating_type_id=?";
// TODO: Consider making WHERE clauses to be >= 0 if 20Km is not a good measure and no known measure would exist.
// This would add a few milliseconds, but still around 1 second return time from SQL Server.
private final static String NEW_POULTRY_SEARCH
= "DECLARE @g2 geography = geography::STGeomFromText('?', 4326).EnvelopeCenter();"
+ "select id, pointid, location.STAsText(), grid_code from energy.HDD_poultry_index WITH(INDEX(idxPoultry)) WHERE location.STDistance(@g2) < " + MAX_DISTANCE_METERS + " order by location.STDistance(@g2) ASC;";
private final static String NEW_SWINE_SEARCH
= "DECLARE @g2 geography = geography::STGeomFromText('?', 4326).EnvelopeCenter();"
+ "select id, pointid, location.STAsText(), grid_code, location.STDistance(@g2) / 1000 as distanceKm from energy.HDD_swine_index WITH(INDEX(idxSwine)) WHERE location.STDistance(@g2) < " + MAX_DISTANCE_METERS + " order by location.STDistance(@g2) ASC;";
protected SessionLogger LOG;
protected Connection connection;
public AHATcrdb(Class<?> c, SessionLogger log) throws SQLException, ServiceException {
LOG = log;
connection = Binaries.getRessourceJDBC(c, CRDB, log);
validate();
}
public AHATcrdb(Connection conn, SessionLogger log) throws SQLException, ServiceException {
if (null != conn) {
LOG = log;
connection = conn;
validate();
} else {
throw new ServiceException("No database connection object provided.");
}
}
public void validate() throws ServiceException, SQLException {
if ((null != connection) && (!connection.getMetaData().getDatabaseProductName().contains("Microsoft SQL"))) {
throw new csip.ServiceException("Invalid connection type passed to mssql_crdb. Class requires a connection to a MsSQL server.");
}
}
public double getLightingTypeUseFactor(int house, int lightingType) {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
public void close() throws SQLException {
if (null != connection) {
Log(Level.INFO, "Closing Database Connection.");
connection.close();
}
}
private void Log(Level level, String msg, Throwable t) {
if ((null != LOG) && (LOG.isLoggable(level))) {
if (LOG.isLoggable(level)) {
LOG.log(level, msg, t);
}
}
}
private void Log(Level level, String msg) {
if ((null != LOG) && (LOG.isLoggable(level))) {
LOG.log(level, msg);
}
}
private void Log(Level level, String msg, Object... params) {
if ((null != LOG) && (LOG.isLoggable(level))) {
LOG.log(level, msg, params);
}
}
public double getLightingTypeBaseFactor(int house, int lightingType, boolean reimeId) {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
public String GetAnimalTypeDisplay(int animal_id) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_ANIMAL_TYPE_DISPLAY)) {
statement.setInt(1, animal_id);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getString("ahat_animal_type_display");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<AnimalType> GetAnimalTypes() throws SQLException, ServiceException {
ArrayList<AnimalType> animals = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_ALL_ANIMAL_TYPES)) {
ResultSet results = statement.executeQuery();
while (results.next()) {
AnimalType animal = new AnimalType();
animal.name = results.getString("ahat_animal_type_name");
animal.id = results.getInt("ahat_animal_type_id");
animal.displayName = results.getString("ahat_animal_type_display");
animals.add(animal);
}
if (animals.size() <= 0) {
throw new ServiceException("There were no animal type records in the database.");
}
}
return animals;
}
public double GetDairyLightingGroupEfficientUseFactor(String inList) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_DAIRY_LIGHTING_EFFICENT_UF_FOR_GROUP)) {
statement.setString(1, inList);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_animal_lighting_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public double GetEnergyCostForEnergyId(int energyId) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_ENERGY_COST_FOR_ENERGY_ID)) {
statement.setInt(1, energyId);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_energy_type_cost");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public double GetFarrowHeatingUseFactor(int key) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_FARROW_HEATING_USE_FACTOR_FOR_ID)) {
statement.setInt(1, key);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_farrow_heating_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<HeatingOption> GetFarrowHeatingBestOptionScenarios(int heatKey, String inList) throws SQLException, ServiceException {
ArrayList<HeatingOption> scenarioOptions = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_FARROW_HEATING_BEST_OPTIONS_FOR_ID)) {
statement.setInt(1, heatKey);
statement.setString(2, inList);
ResultSet results = statement.executeQuery();
while (results.next()) {
HeatingOption option = new HeatingOption();
option.key = results.getInt("ahat_farrow_heating_key");
option.useFactor = results.getDouble("ahat_farrow_heating_use_factor");
option.name = results.getString("ahat_farrow_heating_name");
}
if (scenarioOptions.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return scenarioOptions;
}
public int GetGroupIdForLightingId(int lightingID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_GROUP_ID_FOR_DAIRY_LIGHTING)) {
statement.setInt(1, lightingID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getInt("ahat_grouping_type_id");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
/**
* Poultry Heating Options from list and key
*
* @param heatKey
* @param inList
* @return
*/
public ArrayList<HeatingOption> GetHeatingBestOptionScenarios(int heatKey, String inList) throws SQLException, ServiceException {
ArrayList<HeatingOption> scenarioOptions = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_HEATING_BEST_OPTIONS_FOR_ID + " and ahat_poultry_heating_uf_key in (?) order by ahat_poultry_heating_uf asc;")) {
statement.setInt(1, heatKey);
statement.setString(2, inList);
ResultSet results = statement.executeQuery();
while (results.next()) {
HeatingOption option = new HeatingOption();
option.key = results.getInt("ahat_poultry_heating_uf_key");
option.useFactor = results.getDouble("ahat_poultry_heating_uf");
option.name = results.getString("ahat_poultry_heating_uf_name");
}
if (scenarioOptions.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return scenarioOptions;
}
/**
* Poultry Heating Options from key only
*
* @param heatKey
* @return
*/
public ArrayList<HeatingOption> GetPoultryHeatingBestOptionScenarios(int heatKey) throws SQLException, ServiceException {
ArrayList<HeatingOption> scenarioOptions = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_HEATING_BEST_OPTIONS_FOR_ID + " order by ahat_poultry_heating_uf asc;")) {
statement.setInt(1, heatKey);
ResultSet results = statement.executeQuery();
while (results.next()) {
HeatingOption option = new HeatingOption();
option.key = results.getInt("ahat_poultry_heating_uf_key");
option.useFactor = results.getDouble("ahat_poultry_heating_uf");
option.name = results.getString("ahat_poultry_heating_uf_name");
}
if (scenarioOptions.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return scenarioOptions;
}
public String GetHeatingTypeNameForId(int heatingId) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_HEATING_TYPE_NAME_FOR_ID)) {
statement.setInt(1, heatingId);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getString("ahat_heating_type_name");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<HeatingType> GetHeatingTypes() throws SQLException, ServiceException {
ArrayList<HeatingType> types = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_HEATING_TYPES_FOR_ANIMAL_HOUSING)) {
ResultSet results = statement.executeQuery();
while (results.next()) {
HeatingType option = new HeatingType();
option.id = results.getInt("ahat_heating_type_id");
option.name = results.getString("ahat_heating_type_name");
option.displayName = results.getString("ahat_heating_type_display");
}
if (types.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return types;
}
public double GetHeatingUseFactor(int key) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_HEATING_USE_FACTOR_FOR_ID)) {
statement.setInt(1, key);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_heating_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public int GetLightingTypeBaseFactor(int animalID, int lightingID, int regimeID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_BASE_FACTOR)) {
statement.setInt(1, lightingID);
statement.setInt(2, regimeID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getInt("ahat_grouping_base_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public int GetLightingTypeBestBaseFactorForGroup(int lightingID, int regimeID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_BEST_BASE_FACTOR_FOR_GROUP)) {
statement.setInt(1, lightingID);
statement.setInt(2, regimeID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getInt("min_ahat_grouping_base_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public double GetLightingTypeBaseUseFactor(int animalID, int lightingID, int regimeID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_USE_FACTOR)) {
statement.setInt(1, lightingID);
statement.setInt(2, regimeID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getInt("ahat_grouping_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<LightingType> GetLightingTypeDetail(int lightingID) throws SQLException, ServiceException {
ArrayList<LightingType> lights = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_DETAIL_FOR_ID)) {
statement.setInt(1, lightingID);
ResultSet results = statement.executeQuery();
while (results.next()) {
LightingType light = new LightingType();
light.id = results.getInt("ahat_lighting_type_id");
light.name = results.getString("ahat_lighting_type_name");
light.displayName = results.getString("ahat_lighting_type_display");
light.useFactor = results.getDouble("ahat_lighting_type_use_factor");
lights.add(light);
}
if (lights.size() <= 0) {
throw new ServiceException("There were no lighting types found for that lightingId.");
}
}
return lights;
}
public ArrayList<LightType> GetLightingTypes(int animalId) throws SQLException, ServiceException {
ArrayList<LightType> lights = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPES_FOR_ANIMAL)) {
statement.setInt(1, animalId);
ResultSet results = statement.executeQuery();
while (results.next()) {
LightType light = new LightType();
light.id = results.getInt("ahat_lighting_type_id");
light.name = results.getString("ahat_lighting_type_name");
light.displayName = results.getString("ahat_lighting_type_display");
light.sortOrder = results.getInt("ahat_animal_lighting_sort_order");
lights.add(light);
}
if (lights.size() <= 0) {
throw new ServiceException("There were no lighting types found for that lightingId.");
}
}
return lights;
}
public double GetLightingTypeUseFactor(int animalID, int lightingID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_USE_FACTOR_FOR_ID)) {
statement.setInt(1, lightingID);
statement.setInt(2, animalID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_animal_lighting_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public int GetLightingTypeWorseBaseFactorForGroup(int lightingID, int regimeID) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_LIGHTING_TYPE_USE_FACTOR_FOR_ID)) {
statement.setInt(1, lightingID);
statement.setInt(2, regimeID);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getInt("max_ahat_grouping_base_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<CoolingOption> GetMilkCoolingBestOptionScenarios(int coolingKey) throws SQLException, ServiceException {
ArrayList<CoolingOption> coolingOptions = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_MILK_COOLING_BEST_OPTIONS_FOR_ID)) {
statement.setInt(1, coolingKey);
ResultSet results = statement.executeQuery();
while (results.next()) {
CoolingOption coolingOption = new CoolingOption();
coolingOption.key = results.getInt("ahat_milk_cooling_key");
coolingOption.useFactor = results.getDouble("ahat_milk_cooling_use_factor");
coolingOption.name = results.getString("ahat_milk_cooling_key_name");
}
if (coolingOptions.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return coolingOptions;
}
public double GetMilkCoolingUseFactor(int coolingKey) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_MILK_COOLING_USE_FACTOR_FOR_KEY)) {
statement.setInt(1, coolingKey);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_milk_cooling_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public double GetPoultryHeatingUseFactor(int key) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_POULTRY_HEATING_USE_FACTOR_FOR_ID)) {
statement.setInt(1, key);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_poultry_heating_uf");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public double GetVentilationUseFactor(int ventKey) throws SQLException, ServiceException {
try (PreparedStatement statement = connection.prepareStatement(SELECT_VENTILATION_USE_FACTOR_FOR_ID)) {
statement.setInt(1, ventKey);
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("ahat_ventilation_use_factor");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
}
public ArrayList<VentilationOption> GetVentilationBestOptionScenarios(int ventKey, String inList) throws SQLException, ServiceException {
ArrayList<VentilationOption> ventilationOptions = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(SELECT_VENTILATION_BEST_OPTIONS_FOR_ID)) {
statement.setInt(1, ventKey);
statement.setString(2, inList);
ResultSet results = statement.executeQuery();
while (results.next()) {
VentilationOption ventilationOption = new VentilationOption();
ventilationOption.key = results.getInt("ahat_ventilation_use_factor_key");
ventilationOption.useFactor = results.getDouble("ahat_ventilation_use_factor");
ventilationOption.name = results.getString("ahat_ventilation_use_factor_name");
}
if (ventilationOptions.size() <= 0) {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
return ventilationOptions;
}
public double GetHeatingDegreeFactorForPoultryLatLon(GISObject location) throws GISObjectException, SQLException, ServiceException {
if (null != location) {
try (PreparedStatement statement = connection.prepareStatement(NEW_POULTRY_SEARCH)) {
statement.setString(1, location.toWKT());
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("grid_code");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
} else {
throw new ServiceException("No geometry was passed to the GetHeatingDegreeFactorForPoultryLatLon function.");
}
}
public double GetHeatingDegreeFactorForSwineLatLon(GISObject location) throws GISObjectException, SQLException, ServiceException {
if (null != location) {
try (PreparedStatement statement = connection.prepareStatement(NEW_SWINE_SEARCH)) {
statement.setString(1, location.toWKT());
ResultSet results = statement.executeQuery();
if (results.next()) {
return results.getDouble("grid_code");
} else {
throw new ServiceException("There were no animal type display values for the animal id specified.");
}
}
} else {
throw new ServiceException("No geometry was passed to the GetHeatingDegreeFactorForSwineLatLon function.");
}
}
public class AnimalType {
String name;
String displayName;
int id;
}
public class HeatingType {
String name;
String displayName;
int id;
}
public class LightingType {
String name;
String displayName;
int id;
double useFactor;
}
public class LightType {
String name;
String displayName;
int id;
int sortOrder;
}
public class HeatingOption {
int key;
double useFactor;
String name;
}
public class VentilationOption {
int key;
double useFactor;
String name;
}
public class CoolingOption {
int key;
double useFactor;
String name;
}
}