org.generationcp.middleware.dao.LocationDAO.java Source code

Java tutorial

Introduction

Here is the source code for org.generationcp.middleware.dao.LocationDAO.java

Source

/*******************************************************************************
 * Copyright (c) 2012, All Rights Reserved.
 * <p/>
 * Generation Challenge Programme (GCP)
 * <p/>
 * <p/>
 * This software is licensed for use under the terms of the GNU General Public License (http://bit.ly/8Ztv8M) and the provisions of Part F
 * of the Generation Challenge Programme Amended Consortium Agreement (http://bit.ly/KQX1nL)
 *******************************************************************************/

package org.generationcp.middleware.dao;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.generationcp.middleware.domain.dms.LocationDto;
import org.generationcp.middleware.exceptions.MiddlewareQueryException;
import org.generationcp.middleware.manager.Operation;
import org.generationcp.middleware.pojos.Country;
import org.generationcp.middleware.pojos.Georef;
import org.generationcp.middleware.pojos.Location;
import org.generationcp.middleware.pojos.LocationDetails;
import org.generationcp.middleware.pojos.Locdes;
import org.generationcp.middleware.service.api.location.AdditionalInfoDto;
import org.generationcp.middleware.service.api.location.LocationDetailsDto;
import org.generationcp.middleware.service.api.location.LocationFilters;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * DAO class for {@link Location}.
 */
@SuppressWarnings("unchecked")
public class LocationDAO extends GenericDAO<Location, Integer> {

    private static final String UNIQUE_ID = "uniqueID";
    private static final String CLASS_NAME_LOCATION = "Location";
    private static final String COUNTRY_ID = "cntryid";
    private static final String COUNTRY = "country";
    private static final String GET_BY_TYPE = "getByType";
    private static final String GET_BY_COUNTRY = "getByCountry";
    private static final String LNAME = "lname";
    private static final String LOCID = "locid";
    private static final String LTYPE = "ltype";
    private static final String NAME_OR_OPERATION = "name|operation";

    private static final Logger LOG = LoggerFactory.getLogger(LocationDAO.class);

    public List<Location> getByName(final String name, final Operation operation) {
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            this.addNameSearchCriteria(name, operation, criteria);
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("getByName", LocationDAO.NAME_OR_OPERATION, name + "|" + operation,
                            e.getMessage(), LocationDAO.CLASS_NAME_LOCATION),
                    e);
        }
    }

    private void addNameSearchCriteria(final String name, final Operation operation, final Criteria criteria) {
        if (operation == null || operation == Operation.EQUAL) {
            criteria.add(Restrictions.eq(LocationDAO.LNAME, name));
        } else if (operation == Operation.LIKE) {
            criteria.add(Restrictions.like(LocationDAO.LNAME, name, MatchMode.START));
        }
    }

    public List<Location> getByNameAndUniqueID(final String name, final Operation operation,
            final String programUUID) {
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);

            this.addNameSearchCriteria(name, operation, criteria);
            criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                    Restrictions.isNull(LocationDAO.UNIQUE_ID)));
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByName", "name|operation",
                    name + "|" + operation, e.getMessage(), "Location"), e);
        }
    }

    public List<Location> getByName(final String name, final Operation operation, final int start,
            final int numOfRows) {
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);

            this.addNameSearchCriteria(name, operation, criteria);

            criteria.setFirstResult(start);
            criteria.setMaxResults(numOfRows);
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("getByName", LocationDAO.NAME_OR_OPERATION, name + "|" + operation,
                            e.getMessage(), LocationDAO.CLASS_NAME_LOCATION),
                    e);
        }
    }

    public List<Location> getByNameAndUniqueID(final String name, final Operation operation,
            final String programUUID, final int start, final int numOfRows) {
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);

            this.addNameSearchCriteria(name, operation, criteria);
            criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                    Restrictions.isNull(LocationDAO.UNIQUE_ID)));
            criteria.setFirstResult(start);
            criteria.setMaxResults(numOfRows);
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByName", "name|operation",
                    name + "|" + operation, e.getMessage(), "Location"), e);
        }
    }

    public long countByName(final String name, final Operation operation) {
        try {
            if (name != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.setProjection(Projections.rowCount());

                this.addNameSearchCriteria(name, operation, criteria);

                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("countByName", LocationDAO.NAME_OR_OPERATION,
                            name + "|" + operation, e.getMessage(), LocationDAO.CLASS_NAME_LOCATION),
                    e);
        }
        return 0;
    }

    public long countByNameAndUniqueID(final String name, final Operation operation, final String programUUID) {
        try {
            if (name != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.setProjection(Projections.rowCount());

                this.addNameSearchCriteria(name, operation, criteria);
                criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                        Restrictions.isNull(LocationDAO.UNIQUE_ID)));
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("countByName", "name|operation|programUUID",
                            name + "|" + operation + "|" + programUUID, e.getMessage(), "Location"),
                    e);
        }
        return 0;
    }

    public List<Location> getByCountry(final Country country) {
        try {
            if (country != null) {
                final Integer countryId = country.getCntryid();
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.COUNTRY_ID, countryId));
                criteria.addOrder(Order.asc(LocationDAO.LNAME));
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_COUNTRY,
                    LocationDAO.COUNTRY, country.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public List<Location> getByCountryAndType(final Country country, final Integer type) {
        try {
            if (country != null && type != null) {
                final Integer countryId = country.getCntryid();
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.COUNTRY_ID, countryId));
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.addOrder(Order.asc(LocationDAO.LNAME));
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_COUNTRY,
                    LocationDAO.COUNTRY, country.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public List<Location> getByNameCountryAndType(final String name, final Country country, final Integer type) {
        try {

            Integer countryId = null;
            if (country != null) {
                countryId = country.getCntryid();
            }

            final Criteria criteria = this.getSession().createCriteria(Location.class);

            if (countryId != null) {
                criteria.add(Restrictions.eq(LocationDAO.COUNTRY_ID, countryId));
            }

            if (type != null && 0 != type.intValue()) {
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
            }

            if (name != null && !name.isEmpty()) {
                criteria.add(Restrictions.like(LocationDAO.LNAME, "%" + name.trim() + "%"));
            }

            criteria.addOrder(Order.asc(LocationDAO.LNAME));

            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_COUNTRY,
                    LocationDAO.COUNTRY, country.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public List<Location> getByCountry(final Country country, final int start, final int numOfRows) {
        try {
            if (country != null) {
                final Integer countryId = country.getCntryid();
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.COUNTRY_ID, countryId));
                criteria.setFirstResult(start);
                criteria.setMaxResults(numOfRows);
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_COUNTRY,
                    LocationDAO.COUNTRY, country.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public long countByCountry(final Country country) {
        try {
            if (country != null) {
                final Integer countryId = country.getCntryid();
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.COUNTRY_ID, countryId));
                criteria.setProjection(Projections.rowCount());
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_COUNTRY,
                    LocationDAO.COUNTRY, country.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return 0;
    }

    public List<Location> getByType(final Integer type) {
        try {
            if (type != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.addOrder(Order.asc(LocationDAO.LNAME));
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_TYPE, "type",
                    String.valueOf(type), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public List<Location> getByType(final Integer type, final String programUUID) {
        try {
            if (type != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                        Restrictions.isNull(LocationDAO.UNIQUE_ID)));
                criteria.addOrder(Order.asc(LocationDAO.LNAME));
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_TYPE, "type",
                    String.valueOf(type), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public List<Location> getByType(final Integer type, final int start, final int numOfRows) {
        try {
            if (type != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.setFirstResult(start);
                criteria.setMaxResults(numOfRows);
                return criteria.list();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage(LocationDAO.GET_BY_TYPE, "type",
                    String.valueOf(type), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    public long countByType(final Integer type) {
        try {
            if (type != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.setProjection(Projections.rowCount());
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countByType", "type",
                    String.valueOf(type), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return 0;
    }

    public long countByType(final Integer type, final String programUUID) {
        try {
            if (type != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq(LocationDAO.LTYPE, type));
                criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                        Restrictions.isNull(LocationDAO.UNIQUE_ID)));
                criteria.setProjection(Projections.rowCount());
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countByType", "type",
                    String.valueOf(type), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return 0;
    }

    @SuppressWarnings("rawtypes")
    public List<Location> getAllBreedingLocations() {
        final List<Location> locationList = new ArrayList<>();
        try {
            final Session session = this.getSession();
            final SQLQuery query = session.createSQLQuery(Location.GET_ALL_BREEDING_LOCATIONS);
            final List results = query.list();

            for (final Object o : results) {
                final Object[] result = (Object[]) o;
                if (result != null) {
                    final Integer locid = (Integer) result[0];
                    final Integer ltype = (Integer) result[1];
                    final Integer nllp = (Integer) result[2];
                    final String lname = (String) result[3];
                    final String labbr = (String) result[4];
                    final Integer snl3id = (Integer) result[5];
                    final Integer snl2id = (Integer) result[6];
                    final Integer snl1id = (Integer) result[7];
                    final Integer cntryid = (Integer) result[8];
                    final Integer lrplce = (Integer) result[9];
                    final Double latitude = (Double) result[11];
                    final Double longitude = (Double) result[12];
                    final Double altitude = (Double) result[13];
                    final String programUUID = (String) result[14];

                    final Location location = new Location(locid, ltype, nllp, lname, labbr, snl3id, snl2id, snl1id,
                            cntryid, lrplce);
                    location.setUniqueID(programUUID);

                    final Georef georef = new Georef();
                    georef.setLocid(locid);
                    georef.setLat(latitude);
                    georef.setLon(longitude);
                    georef.setAlt(altitude);
                    location.setGeoref(georef);
                    locationList.add(location);
                }
            }
            return locationList;
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllBreedingLocations", "", null,
                    e.getMessage(), "GermplasmDataManager"), e);
        }
    }

    @SuppressWarnings("deprecation")
    public Long countAllBreedingLocations() {
        try {
            final Session session = this.getSession();
            final SQLQuery query = session.createSQLQuery(Location.COUNT_ALL_BREEDING_LOCATIONS);
            return (Long) query.addScalar("count", Hibernate.LONG).uniqueResult();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countAllBreedingLocations", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public List<LocationDetails> getLocationDetails(final Integer locationId, final Integer start,
            final Integer numOfRows) {
        try {

            final StringBuilder query = new StringBuilder()
                    .append("select l.lname as location_name,l.locid,l.ltype as ltype,")
                    .append(" g.lat as latitude, g.lon as longitude, g.alt as altitude,")
                    .append(" c.cntryid as cntryid, c.isofull as country_full_name, l.labbr as location_abbreviation,")
                    .append(" ud.fname as location_type,")
                    .append(" ud.fdesc as location_description, l.program_uuid,")
                    .append(" c.isoabbr as cntry_name, province.lname AS province_name, province.locid as province_id")
                    .append(" from location l").append(" left join georef g on l.locid = g.locid")
                    .append(" left join cntry c on l.cntryid = c.cntryid")
                    .append(" left join udflds ud on ud.fldno = l.ltype").append(" ,location province");

            if (locationId != null) {
                query.append(" where l.locid = :id");
                query.append(" AND province.locid = l.snl1id");

                final SQLQuery sqlQuery = this.getSession().createSQLQuery(query.toString());
                sqlQuery.setParameter("id", locationId);
                sqlQuery.setFirstResult(start);
                sqlQuery.setMaxResults(numOfRows);
                sqlQuery.addEntity(LocationDetails.class);

                return sqlQuery.list();
            }

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationDetails", "id",
                    String.valueOf(locationId), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return new ArrayList<>();
    }

    @Override
    public Location saveOrUpdate(final Location location) {
        try {
            final Location savedLocation = super.saveOrUpdate(location);
            if (location.getGeoref() != null) {
                location.getGeoref().setLocid(location.getLocid());
                this.getSession().saveOrUpdate(location.getGeoref());
            }
            return savedLocation;
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException("Error in saveOrUpdate(location): " + e.getMessage(), e);
        }
    }

    public List<Location> getAllProvincesByCountry(final Integer countryId) {
        if (countryId == null || countryId == 0) {
            return new ArrayList<>();
        }

        try {
            final SQLQuery query = this.getSession().createSQLQuery(Location.GET_PROVINCE_BY_COUNTRY);
            query.addEntity(Location.class);
            query.setParameter("countryId", countryId);
            return query.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllProvinces", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public List<Location> getAllProvinces() {

        try {
            final SQLQuery query = this.getSession().createSQLQuery(Location.GET_ALL_PROVINCES);
            query.addEntity(Location.class);
            return query.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getAllProvinces", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public List<LocationDto> getLocationDtoByIds(final Collection<Integer> ids) {
        final List<LocationDto> returnList = new ArrayList<>();
        if (ids == null || ids.isEmpty()) {
            return returnList;
        }
        try {
            final String sql = "SELECT l.lname, prov.lname, c.isoabbr, l.locid" + " FROM location l"
                    + " LEFT JOIN location prov ON prov.locid = l.snl1id"
                    + " LEFT JOIN cntry c ON c.cntryid = l.cntryid" + " WHERE l.locid in (:ids)";
            final SQLQuery query = this.getSession().createSQLQuery(sql);
            query.setParameterList("ids", ids);
            final List<Object[]> results = query.list();

            if (results != null) {
                for (final Object[] result : results) {
                    returnList.add(new LocationDto((Integer) result[3], (String) result[0], (String) result[1],
                            (String) result[2]));
                }
            }

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationDtoById", "id",
                    ids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return returnList;
    }

    public List<Location> getLocationByIds(final Collection<Integer> ids) {

        if (ids == null || ids.isEmpty()) {
            return new ArrayList<>();
        }

        try {
            return this.getSession().createCriteria(Location.class).add(Restrictions.in(LocationDAO.LOCID, ids))
                    .addOrder(Order.asc(LocationDAO.LNAME)).list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    String.format("Error with getLocationByIds(id=[%s])", StringUtils.join(ids, ",")), e);
        }
    }

    public Map<Integer, String> getLocationNamesByLocationIDs(final List<Integer> locIds) {
        final Map<Integer, String> toreturn = new HashMap<>();

        final List<Location> locations = this.getLocationByIds(locIds);
        for (final Location location : locations) {
            toreturn.put(location.getLocid(), location.getLname());
        }

        return toreturn;
    }

    public Map<Integer, String> getLocationNamesMapByGIDs(final List<Integer> gids) {
        final Map<Integer, String> toreturn = new HashMap<>();
        for (final Integer gid : gids) {
            toreturn.put(gid, null);
        }

        try {
            final SQLQuery query = this.getSession().createSQLQuery(Location.GET_LOCATION_NAMES_BY_GIDS);
            query.setParameterList("gids", gids);

            final List<Object> results = query.list();
            for (final Object result : results) {
                final Object[] resultArray = (Object[]) result;
                final Integer gid = (Integer) resultArray[0];
                final String location = (String) resultArray[2];
                toreturn.put(gid, location);
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationNamesMapByGIDs", "gids",
                    gids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }

        return toreturn;
    }

    public Map<Integer, LocationDto> getLocationNamesByGIDs(final List<Integer> gids) {
        final Map<Integer, LocationDto> toreturn = new HashMap<>();
        for (final Integer gid : gids) {
            toreturn.put(gid, null);
        }

        try {
            final SQLQuery query = this.getSession().createSQLQuery(Location.GET_LOCATION_NAMES_BY_GIDS);
            query.setParameterList("gids", gids);

            final List<Object> results = query.list();
            for (final Object result : results) {
                final Object[] resultArray = (Object[]) result;
                final Integer gid = (Integer) resultArray[0];
                final Integer locid = (Integer) resultArray[1];
                final String locationName = (String) resultArray[2];
                toreturn.put(gid, new LocationDto(locid, locationName));
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationNamesByGIDs", "gids",
                    gids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }

        return toreturn;
    }

    public List<Location> getLocationsByDTypeAndLType(final String dval, final Integer dType, final Integer lType) {
        final List<Location> locations = new ArrayList<>();
        try {
            final StringBuilder sqlString = new StringBuilder()
                    .append("SELECT  l.locid, l.ltype, l.nllp, l.lname, l.labbr")
                    .append(", l.snl3id, l.snl2id, l.snl1id, l.cntryid, l.lrplce ")
                    .append("FROM locdes ld INNER JOIN location l ").append(" ON l.locid = ld.locid ")
                    .append("WHERE dtype = :dtype  AND ltype = :ltype AND dval = :dval ");

            final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
            query.setParameter("dtype", dType);
            query.setParameter(LocationDAO.LTYPE, lType);
            query.setParameter("dval", dval);

            final List<Object[]> results = query.list();

            if (!results.isEmpty()) {
                for (final Object[] row : results) {
                    final Integer locid = (Integer) row[0];
                    final Integer ltype = (Integer) row[1];
                    final Integer nllp = (Integer) row[2];
                    final String lname = (String) row[3];
                    final String labbr = (String) row[4];
                    final Integer snl3id = (Integer) row[5];
                    final Integer snl2id = (Integer) row[6];
                    final Integer snl1id = (Integer) row[7];
                    final Integer cntryid = (Integer) row[8];
                    final Integer lrplce = (Integer) row[9];

                    locations.add(new Location(locid, ltype, nllp, lname, labbr, snl3id, snl2id, snl1id, cntryid,
                            lrplce));

                }
            }

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationsByDTypeAndLType",
                    "dType|lType", dType + "|" + lType, e.getMessage(), "Locdes"), e);
        }
        return locations;
    }

    public List<Location> getByTypeWithParent(final Integer type, final Integer relationshipType) {
        final List<Location> locationList = new ArrayList<>();
        try {
            final Session session = this.getSession();
            final String sql = "SELECT f.locid, f.lname, fd.dval " + " FROM location f "
                    + " INNER JOIN locdes fd ON fd.locid = f.locid AND fd.dtype = " + relationshipType
                    + " WHERE f.ltype = " + type;
            final SQLQuery query = session.createSQLQuery(sql);
            final List<Object[]> results = query.list();

            for (final Object o : results) {
                final Object[] result = (Object[]) o;
                if (result != null) {
                    final Integer fieldId = (Integer) result[0];
                    final String fieldName = (String) result[1];
                    final String parentId = (String) result[2];

                    final Location location = new Location();
                    location.setLocid(fieldId);
                    location.setLname(fieldName);
                    location.setParentLocationId(
                            parentId != null && NumberUtils.isNumber(parentId) ? Integer.valueOf(parentId) : null);
                    locationList.add(location);
                }
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByTypeWithParent", "type",
                    type.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return locationList;
    }

    public Map<Integer, Location> getNamesByIdsIntoMap(final Collection<Integer> ids) {
        final Map<Integer, Location> map = new HashMap<>();
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            criteria.add(Restrictions.in(LocationDAO.LOCID, ids));
            final List<Location> locations = criteria.list();

            if (locations != null && !locations.isEmpty()) {
                for (final Location location : locations) {
                    map.put(location.getLocid(), location);
                }
            }

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getNamesByIdsIntoMap", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return map;
    }

    public List<Location> getByIds(final List<Integer> ids) {
        List<Location> locations = new ArrayList<>();

        if (ids == null || ids.isEmpty()) {
            return locations;
        }

        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            criteria.add(Restrictions.in(LocationDAO.LOCID, ids));
            locations = criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByIds", "", null, e.getMessage(),
                    LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return locations;
    }

    public List<Location> getByUniqueID(final String programUUID) {
        List<Location> locations = new ArrayList<>();

        if (programUUID == null || programUUID.isEmpty()) {
            return locations;
        }

        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                    Restrictions.isNull(LocationDAO.UNIQUE_ID)));
            locations = criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByIds", "", null, e.getMessage(),
                    LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return locations;
    }

    public List<Location> getByUniqueIDAndExcludeLocationTypes(final String programUUID,
            final List<Integer> locationTypesToExclude) {

        List<Location> locations = new ArrayList<>();

        if (programUUID == null || programUUID.isEmpty()) {
            return locations;
        }

        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                    Restrictions.isNull(LocationDAO.UNIQUE_ID)));
            if (locationTypesToExclude != null && !locationTypesToExclude.isEmpty()) {
                criteria.add(Restrictions.not(Restrictions.in(LocationDAO.LTYPE, locationTypesToExclude)));
            }
            locations = criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getByUniqueIDAndExcludeLocationTypes",
                    "", null, e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }

        return locations;
    }

    public long countByLocationAbbreviation(final String locationAbbreviation) {
        try {
            if (locationAbbreviation != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.eq("labbr", locationAbbreviation));
                criteria.setProjection(Projections.rowCount());
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countByLocationAbbreviation",
                    "locationAbbreviation", locationAbbreviation, e.getMessage(), LocationDAO.CLASS_NAME_LOCATION),
                    e);
        }
        return 0;
    }

    public long countByUniqueID(final String programUUID) {
        try {
            if (programUUID != null) {
                final Criteria criteria = this.getSession().createCriteria(Location.class);
                criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                        Restrictions.isNull(LocationDAO.UNIQUE_ID)));
                criteria.setProjection(Projections.rowCount());
                return ((Long) criteria.uniqueResult()).longValue();
            }
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countByUniqueID", "uniqueID",
                    programUUID, e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
        return 0;
    }

    public List<Location> getProgramLocations(final String programUUID) {
        List<Location> locations = new ArrayList<>();
        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            criteria.add(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID));
            locations = criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error in getProgramLocations(" + programUUID + ") in LocationDao: " + e.getMessage(), e);
        }
        return locations;
    }

    public List<Locdes> getLocdesByLocId(final Integer locationId) {
        try {
            final Criteria criteria = this.getSession().createCriteria(Locdes.class);
            criteria.add(Restrictions.eq("locationId", locationId));
            return criteria.list();
        } catch (final HibernateException e) {
            throw new MiddlewareQueryException("Error with getByIds() query from Location: " + e.getMessage(), e);
        }
    }

    public List<Location> getBreedingLocations(final List<Integer> ids) {
        try {
            final List<Integer> validCodes = new ArrayList<>();
            // 410, 411, 412
            validCodes.add(410);
            validCodes.add(411);
            validCodes.add(412);

            final Criteria criteria = this.getSession().createCriteria(Location.class);
            if (!ids.isEmpty()) {
                criteria.add(Restrictions.in("locid", ids));
            }
            criteria.add(Restrictions.in("ltype", validCodes));
            criteria.addOrder(Order.asc("lname"));

            return criteria.list();
        } catch (final HibernateException e) {
            LocationDAO.LOG.error(e.getMessage(), e);
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("getBreedingLocations", "", null, e.getMessage(), "Location"), e);
        }
    }

    public List<Location> getSeedingLocations(final List<Integer> ids, final Integer seedLType) {
        try {

            final Criteria criteria = this.getSession().createCriteria(Location.class);
            if (!ids.isEmpty()) {
                criteria.add(Restrictions.in("locid", ids));
            }
            criteria.add(Restrictions.eq("ltype", seedLType));
            criteria.addOrder(Order.asc("lname"));

            return criteria.list();
        } catch (final HibernateException e) {
            LocationDAO.LOG.error(e.getMessage(), e);
            throw new MiddlewareQueryException(
                    this.getLogExceptionMessage("getSeedingLocations", "", null, e.getMessage(), "Location"), e);
        }
    }

    public List<Location> getBreedingLocationsByUniqueID(final String programUUID) {
        List<Location> locations = new ArrayList<>();

        if (programUUID == null || programUUID.isEmpty()) {
            return locations;
        }

        try {
            final Criteria criteria = this.getSession().createCriteria(Location.class);
            // filter by programUUID plus return also records with null
            // programUUID (common historical data)
            criteria.add(Restrictions.or(Restrictions.eq(LocationDAO.UNIQUE_ID, programUUID),
                    Restrictions.isNull(LocationDAO.UNIQUE_ID)));
            // set location types for Breeding Location
            criteria.add(Restrictions.in(LocationDAO.LTYPE, Arrays.asList(Location.BREEDING_LOCATION_TYPE_IDS)));
            criteria.addOrder(Order.asc("lname"));

            locations = criteria.list();
        } catch (final HibernateException e) {
            LocationDAO.LOG.error(e.getMessage(), e);
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getBreedingLocationsByUniqueID", "",
                    null, e.getMessage(), "Location"), e);

        }
        return locations;

    }

    public List<LocationDetails> getFilteredLocations(final Integer countryId, final Integer locationType,
            final String locationName, final String programUUID) {

        try {

            final StringBuilder queryString = new StringBuilder()
                    .append("SELECT l.lname as location_name,l.locid,l.ltype as ltype,")
                    .append(" g.lat as latitude, g.lon as longitude, g.alt as altitude,")
                    .append(" c.cntryid as cntryid, c.isofull as country_full_name, l.labbr as location_abbreviation,")
                    .append(" ud.fname as location_type,")
                    .append(" ud.fdesc as location_description, l.program_uuid")
                    .append(" ,c.isoabbr as cntry_name, province.lname AS province_name, province.locid as province_id")
                    .append(" FROM location l").append(" LEFT JOIN georef g on l.locid = g.locid")
                    .append(" LEFT JOIN cntry c on l.cntryid = c.cntryid")
                    .append(" LEFT JOIN udflds ud on ud.fldno = l.ltype").append(" ,location province ")
                    .append(" WHERE (l.program_uuid = '").append(programUUID).append("'")
                    .append(" or l.program_uuid is null) ").append(" and province.locid = l.snl1id ");

            if (countryId != null) {
                queryString.append(" AND c.cntryid = ");
                queryString.append(countryId);
            }

            if (locationType != null) {
                queryString.append(" AND l.ltype = ");
                queryString.append(locationType);
            }

            if (locationName != null && !locationName.isEmpty()) {
                queryString.append(" AND l.lname REGEXP '");
                queryString.append(locationName);
                queryString.append("' ");
            }

            queryString.append(" ORDER BY UPPER(l.lname) ");

            final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
            query.addEntity(LocationDetails.class);

            return query.list();

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getFilteredLocations", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public long countLocationsByFilter(final Map<LocationFilters, Object> filters) {

        try {
            final StringBuilder sqlString = new StringBuilder();

            sqlString.append("SELECT l.locid ").append(" FROM location l ")
                    .append(" LEFT JOIN georef g on g.locid = l.locid ")
                    .append(" LEFT JOIN cntry c on c.cntryid = l.cntryid ")
                    .append(" LEFT JOIN udflds ud on ud.fldno = l.ltype ")
                    .append(createConditionWhereByFilter(filters));

            final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
            this.setQueryParameters(query, filters);

            return query.list().size();
        } catch (final HibernateException e) {
            LocationDAO.LOG.error(e.getMessage(), e);
            throw new MiddlewareQueryException(this.getLogExceptionMessage("countLocationsByFilter", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    public List<LocationDetailsDto> getLocationsByFilter(final int pageNumber, final int pageSize,
            final Map<LocationFilters, Object> filters) {
        final List<LocationDetailsDto> locationList = new ArrayList<>();
        final StringBuilder sqlString = new StringBuilder();
        try {

            sqlString.append(
                    "SELECT l.locid ,ud.fname ,l.lname ,l.labbr ,c.isothree ,c.isoabbr ,g.lat ,g.lon ,g.alt ,province.lname as province")
                    .append(" FROM location l ").append(" LEFT JOIN georef g on l.locid = g.locid ")
                    .append(" LEFT JOIN cntry c on l.cntryid = c.cntryid ")
                    .append(" LEFT JOIN udflds ud on ud.fldno = l.ltype, ").append(" location province")
                    .append(createConditionWhereByFilter(filters));

            sqlString.append(" and province.locid = l.snl1id ");
            sqlString.append(" ORDER BY l.locid ");

            final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString()).addScalar("l.locid")
                    .addScalar("ud.fname").addScalar("l.lname").addScalar("l.labbr").addScalar("c.isothree")
                    .addScalar("c.isoabbr").addScalar("g.lat").addScalar("g.lon").addScalar("g.alt")
                    .addScalar("province");
            final int start = pageSize * (pageNumber - 1);
            final int numOfRows = pageSize;
            query.setFirstResult(start);
            query.setMaxResults(numOfRows);
            this.setQueryParameters(query, filters);

            final List<Object[]> results = query.list();

            if (!results.isEmpty()) {
                for (final Object[] row : results) {
                    final Integer locationDbId = (Integer) row[0];
                    final String locationType = (String) row[1];
                    final String name = (String) row[2];
                    final String abbreviation = (String) row[3];
                    final String countryCode = (String) row[4];
                    final String countryName = (String) row[5];
                    final Double latitude = (Double) row[6];
                    final Double longitude = (Double) row[7];
                    final Double altitude = (Double) row[8];

                    final LocationDetailsDto locationDetailsDto = new LocationDetailsDto(locationDbId, locationType,
                            name, abbreviation, countryCode, countryName, latitude, longitude, altitude);
                    if (!locationType.equalsIgnoreCase(LocationDAO.COUNTRY)) {
                        final AdditionalInfoDto additionalInfoDto = new AdditionalInfoDto(
                                locationDetailsDto.getLocationDbId());
                        additionalInfoDto.addInfo("province", (String) row[9]);
                        locationDetailsDto.setMapAdditionalInfo(additionalInfoDto);
                    }
                    locationList.add(locationDetailsDto);
                }
            }

            return locationList;

        } catch (final HibernateException e) {
            LocationDAO.LOG.error(e.getMessage(), e);
            throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocalLocationsByFilter", "", null,
                    e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
        }
    }

    private void setQueryParameters(final SQLQuery query, final Map<LocationFilters, Object> filters) {
        for (final Map.Entry<LocationFilters, Object> entry : filters.entrySet()) {
            final LocationFilters filter = entry.getKey();
            final Object value = entry.getValue();
            if (value.getClass().isArray()) {
                query.setParameterList(filter.getParameter(), (Object[]) value);
            } else {
                query.setParameter(filter.getParameter(), value);
            }
        }
    }

    private String createConditionWhereByFilter(final Map<LocationFilters, Object> filters) {
        final StringBuilder sqlString = new StringBuilder();
        sqlString.append(" WHERE 1 = 1");

        for (final Map.Entry<LocationFilters, Object> entry : filters.entrySet()) {
            final LocationFilters filter = entry.getKey();
            final Object value = entry.getValue();

            sqlString.append(" AND ");

            if (value.getClass().isArray()) {
                sqlString.append(filter.getStatement()).append("in (:").append(filter.getParameter()).append(") ");

            } else {
                sqlString.append(filter.getStatement()).append("= :").append(filter.getParameter()).append(" ");
            }
        }
        return sqlString.toString();

    }
}