germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_climate_data
Parameters
Name
Type
Mode
groupIds
text
IN
markedIds
text
IN
datasetIds
text
IN
climateIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`climate_id` = ", `climate_id`,",climate_value,NULL)) AS ", "`", CONCAT(climates.`name`, IF(ISNULL(climates.unit_id), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM climatedata LEFT JOIN climates ON climates.id = climatedata.climate_id LEFT JOIN locations ON locations.id = climatedata.location_id LEFT JOIN units ON units.id = climates.unit_id WHERE climatedata.dataset_id IN (', datasetIds, ') AND ', IF(climateIds IS NULL, '1=1', CONCAT('climate_id IN (', climateIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT locations.site_name AS `name`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, DATE_FORMAT(climatedata.recording_date, '%Y') AS year, DATE_FORMAT(climatedata.recording_date, '%Y-%m-%d') AS Date, locations.site_name AS `Site`, locations.id AS dbId, ', IF(groupIds IS NULL, '', CONCAT('(SELECT json_arrayagg(CONCAT(LEFT(groups.name, 10), IF(LENGTH(groups.name)>10, "...", ""))) FROM groupmembers LEFT JOIN groups ON groups.id = groupmembers.group_id WHERE groupmembers.group_id IN (', groupIds, ') AND groupmembers.foreign_id = locations.id) AS group_ids, ')), @SQL,' FROM climatedata LEFT JOIN locations ON locations.id = climatedata.location_id ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON locations.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = climatedata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id WHERE (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('locations.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('locations.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') AND datasets.id IN (', datasetIds, ') GROUP BY locations.id, dataset_id, recording_date, year'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END