METADATA_ITEM=SELECT DISTINCT institution_name, town, country, iso_country_id, logo_url, description, description_url, body_type_fk,includes_types, access_restriction, usage_restriction, purpose, number_of_subunits, collection_code, strength, notes, doc_state, region, address_line_1, address_line_2, address_line_3, address_line_4, address_line_5, keyword, telephone, fax, email, body_id, bodyname, number_of_species FROM ui_cache_body WHERE source_id=? AND nn_fk =?
METADATA_ITEM2=SELECT DISTINCT institution_name, town, country, iso_country_id, logo_url, description, description_url, body_type_fk,includes_types, access_restriction, usage_restriction, purpose, number_of_subunits, collection_code, strength, notes, doc_state, region, address_line_1, address_line_2, address_line_3, address_line_4, address_line_5, keyword, telephone, fax, email, body_id, bodyname, number_of_species FROM ui_cache_body WHERE body_id=? 

METADATA_ITEM_RELATED_PERSONS_LIST=SELECT family_name FROM ui_cache_person WHERE body_id =?
METADATA_ITEM_COLLECTION_COVERAGE_KEYWORDS_LIST=SELECT keyword, qualifier, keyword_type FROM ui_cache_keyword_collection_coverage WHERE body_id =?
METADATA_ITEM_RELATED_ITEMS_LIST=SELECT child_name, child_type, child_id FROM ui_cache_relation where parent_id =?
METADATA_ITEM_HOLDERS_LIST=SELECT parent_name, parent_type, parent_id FROM ui_cache_relation WHERE child_id =?

METADATA_ITEM_DETAIL=SELECT DISTINCT institution_name, b.town, b.country, iso_country_id, logo_url, description, description_url, b.body_type_fk, doc_state, notes,includes_types, number_of_subunits, access_restriction, usage_restriction, collection_code, purpose, strength, region, address_line_1, address_line_2, address_line_3, address_line_4, address_line_5, telephone, fax, email,b.body_id, b.bodyname as bodyname, number_of_species,family_name FROM ui_cache_body b LEFT JOIN ui_cache_person p ON b.body_id=p.body_id WHERE b.bodyname IS NOT NULL AND b.source_id=? AND b.nn_fk =?



NETWORKS=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=1 ORDER BY bodyname

COLLECTIONS_A_D=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'A' and 'D') ORDER BY bodyname
COLLECTIONS_E_H=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'E' and 'H') ORDER BY bodyname
COLLECTIONS_I_L=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'I' and 'L') ORDER BY bodyname
COLLECTIONS_M_P=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'M' and 'P') ORDER BY bodyname
COLLECTIONS_Q_T=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'Q' and 'T') ORDER BY bodyname
COLLECTIONS_U_Z=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=3 AND (substr(bodyname,1,1) between 'U' and 'Z') ORDER BY bodyname

COLLECTION_CLASS_COUNT=SELECT keyword, COUNT (keyword) AS total FROM ui_cache_collection_class GROUP BY keyword ORDER BY keyword
OBJECT_CLASS_COUNT=SELECT keyword, COUNT (keyword) AS total FROM ui_cache_object_class GROUP BY keyword ORDER BY keyword 
ORGANISATION_TYPE_COUNT=SELECT keyword, COUNT (keyword)AS total FROM ui_cache_organisation_class GROUP BY keyword ORDER BY keyword 

ORGANISATIONS_A_D=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'A' and 'D') ORDER BY bodyname
ORGANISATIONS_E_H=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'E' and 'H') ORDER BY bodyname
ORGANISATIONS_I_L=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'I' and 'L') ORDER BY bodyname
ORGANISATIONS_M_P=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'M' and 'P') ORDER BY bodyname
ORGANISATIONS_Q_T=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'Q' and 'T') ORDER BY bodyname
ORGANISATIONS_U_Z=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND body_type_fk=2 AND (substr(bodyname,1,1) between 'U' and 'Z') ORDER BY bodyname

TIMEPERIOD_COVERAGE=SELECT DISTINCT upper (keyword) as keyword FROM ui_cache_keyword_timeperiod ORDER BY keyword
COUNTRY=SELECT DISTINCT country, iso_country_id FROM  ui_cache_body where country is not null

ALPHABET_COLL=SELECT DISTINCT bodyname FROM ui_cache_body WHERE bodyname ILIKE ? AND body_type_fk=3 ORDER BY bodyname ASC
ALPHABET_ORG=SELECT DISTINCT bodyname FROM ui_cache_body  WHERE bodyname ILIKE ? AND (body_type_fk=2 OR body_type_fk=1 OR body_type_fk = 3) ORDER BY bodyname ASC
ALPHABET_PERSON=SELECT DISTINCT family_name FROM ui_cache_person  WHERE family_name ILIKE ? ORDER BY family_name  ASC
ALPHABET_TOWN=SELECT DISTINCT town FROM ui_cache_body WHERE town ILIKE ? ORDER BY town ASC

COUNTRYXNETWORK=SELECT  DISTINCT bodyname, body_id FROM ui_cache_body WHERE body_type_fk = 1 AND iso_country_id = ?
COUNTRYXORGANISATION=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE body_type_fk = 2 AND iso_country_id = ?
COUNTRYXCOLLECTION=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE body_type_fk = 3 AND iso_country_id = ?
TOWNS=SELECT DISTINCT town FROM ui_cache_body WHERE iso_country_id=?
REGIONS=SELECT DISTINCT region FROM ui_cache_body WHERE region IS NOT NULL AND iso_country_id=?

TOWNSXBODY=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE bodyname IS NOT NULL AND town ILIKE ?    
REGIONSXBODY=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE region LIKE ?   
PERSONSXBODY=SELECT DISTINCT bodyname, body_id FROM ui_cache_person WHERE family_name LIKE?   


ALPHABET_TAXONOMIC_COVERAGE=SELECT DISTINCT upper (keyword) as keyword FROM ui_cache_keyword_taxon_name WHERE keyword ILIKE ? ORDER BY keyword
ALPHABET_COMMON_NAME_COVERAGE=SELECT DISTINCT upper (keyword) as keyword FROM ui_cache_keyword_common_name WHERE keyword ILIKE ?   ORDER BY keyword
ALPHABET_GEO_COVERAGE=SELECT DISTINCT upper (keyword) as keyword FROM ui_cache_keyword_geospatial WHERE keyword ILIKE ? ORDER BY keyword

PROVIDERS=SELECT body.body_type_fk, body.bodyname, body.body_id FROM body WHERE biocase_url IS NOT NULL AND body_type_fk=3 AND bodyname IS NOT NULL AND bodyname ILIKE ?
PROVIDERS_LIST=select distinct name,body_id, access_point_url from datasource inner join body on datasource_id=source_id

ORGANISATION_TYPE=SELECT DISTINCT bodyname, body_id FROM ui_cache_organisation_class WHERE keyword=? ORDER BY bodyname
COLLECTION_CLASS=SELECT DISTINCT bodyname, body_id FROM ui_cache_collection_class WHERE keyword=? ORDER BY bodyname
OBJECT_CLASS=SELECT DISTINCT bodyname, body_id FROM ui_cache_object_class WHERE keyword=? ORDER BY bodyname

KEYWORDSXBODY=SELECT DISTINCT bodyname, body_id FROM ui_cache_body WHERE keyword ILIKE ?

INSTITUTIONS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_body where (body_type_fk =2 OR body_type_fk = 1 OR body_type_fk = 3)
INSTITUTIONS_TYPE_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_organisation_class
COLLECTIONS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_body where (body_type_fk =3)
COLLECTIONS_CLASS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk FROM ui_cache_collection_class
COLLECTIONS_OBJECTS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_object_class
TOWNS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_body 
COUNTRIES_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_body
INCLUDE_TYPES=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_body
PERSONS_FULL_LIST=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk from ui_cache_person where family_name IS NOT NULL

BASIC_SEARCH=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk FROM ui_cache_body WHERE bodyname ILIKE ?
TOP_KEYWORDS=SELECT keyword, count(keyword) AS rownb FROM ui_cache_keyword_collection_coverage WHERE keyword_type_fk = ? GROUP BY KEYWORD ORDER BY rownb DESC LIMIT 20
COUNTRIES_STAT=SELECT DISTINCT   country, COUNT (country) AS country_count FROM ui_cache_body WHERE country IS NOT NULL AND bodyname IS NOT NULL AND body_type_fk= ? GROUP BY country ORDER BY country_count DESC 
MAX_NB_SPECIES_STAT=SELECT DISTINCT bodyname, number_of_species from ui_cache_body WHERE number_of_species IS NOT NULL ORDER BY number_of_species DESC LIMIT 15


FULL_TAXONOMIC_COVERAGE=SELECT DISTINCT  bodyname, body_id, town, country,body_type_fk, source_id, nn_fk FROM ui_cache_keyword_taxon_name 
FULL_COMMON_NAME_COVERAGE=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk  FROM ui_cache_keyword_common_name 
FULL_GEO_COVERAGE=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk  FROM ui_cache_keyword_geospatial 
FULL_TIMEPERIOD_COVERAGE=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk  FROM ui_cache_keyword_timeperiod 
FULL_COLLECTION_COVERAGE=SELECT DISTINCT bodyname, body_id, town, country,body_type_fk, source_id, nn_fk FROM ui_cache_keyword_collection_coverage
ISO_COUNTRY_NAME=select full_country_name from isocountry WHERE iso_country_id = ?
PROVIDERS=select bodyname,biocase_url from body INNER JOIN bodyname ON body_id = body_fk where biocase_url is not null and body_type_fk = '3'


CORM_FUZZY_SEARCH_STARTING_WITH=select body_id, bodyname, town, country, source_id, nn_fk, body_type_fk from body join bodyname on body_fk = body_id join address on address_fk = address_id where freetext_search ~* ('[[:<:]]' || ?)

CORM_FUZZY_SEARCH_ENDING_WITH=select body_id, bodyname, town, country, source_id, nn_fk,  body_type_fk from body join bodyname on body_fk = body_id join address  on address_fk = address_id where freetext_search ~* (? || '[[:>:]]')

CORM_FUZZY_SEARCH_CONTAINING=select body_id, bodyname, town, country, source_id, nn_fk,  body_type_fk from body join bodyname on body_fk = body_id join address  on address_fk = address_id where freetext_search ~* ?

CORM_FUZZY_SEARCH_EXACT=select body_id, bodyname, town, country, source_id, nn_fk,  body_type_fk from body join bodyname on body_fk = body_id join address  on address_fk = address_id where ts_freetext_search @@ to_tsquery('default', ?)

FIELD_KEYWORD=keyword

PROVIDERS_BY_INDEXED_TERM2=select DISTINCT name, access_point_url, body_id from unitcacheterm INNER JOIN  (datasource inner join body on datasource_id= source_id) ON datasource_fk = datasource_id where term ILIKE ?
PROVIDERS_BY_INDEXED_TERM=select DISTINCT name, access_point_url, datasource_id from unitcacheterm INNER JOIN  datasource ON datasource_fk = datasource_id where gbif_de_bot_provider is true and  term ILIKE ?
PROVIDER_DETAIL=select name, number_of_records, number_of_species,supplier_admin_contact, supplier_description from datasource where datasource_id=?

CONCEPTS=select concept_path, documentation from concept where documentation IS NOT NULL


UL_COUNTRIES=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =476) AS countryList
UL_LOCALITY=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =470) AS locality
UL_COLLECTOR=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =434) AS collector 
UL_HIGHER_TAXON=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join unitcacheterm on datasource_id = datasource_fk  where gbif_de_bot_provider is true and concept_fk =144) AS higherTaxon
UL_SCIENTIFIC_NAME=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =146) AS scientificName
UL_COMMON_NAME=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join  unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =189) AS commonName
UL_TIMEPERIOD=select * from (select unit_cache_term_id,datasource_fk, concept_fk, term from datasource inner join  unitcacheterm on datasource_id = datasource_fk where gbif_de_bot_provider is true and concept_fk =583) AS timePeriod
UL_PROVIDERS_PART1=select DISTINCT name, access_point_url, datasource_id  from