germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_compound_data
Parameters
Name
Type
Mode
groupIds
text
IN
markedIds
text
IN
datasetIds
text
IN
compoundIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`compound_id` = ", `compound_id`, ",compound_value, NULL)) AS ", "`", CONCAT(compounds.`name`, IF(ISNULL(compounds.unit_id), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM compounddata LEFT JOIN compounds ON compounds.id = compounddata.compound_id LEFT JOIN germinatebase ON germinatebase.id = compounddata.germinatebase_id LEFT JOIN units ON units.id = compounds.unit_id WHERE compounddata.dataset_id IN (', datasetIds, ') AND ', IF(compoundIds IS NULL, '1=1', CONCAT('compound_id IN (', compoundIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT germinatebase.name, germinatebase.puid, `g`.`name` AS `entity_parent_name`, `g`.`general_identifier` AS `entity_parent_general_identifier`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, DATE_FORMAT(compounddata.recording_date, '%Y') AS year, germinatebase.id AS dbId, germinatebase.general_identifier, ', 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 = germinatebase.id) AS group_ids, ')), @SQL,' FROM compounddata LEFT JOIN germinatebase ON germinatebase.id = compounddata.germinatebase_id LEFT JOIN germinatebase g ON g.id = germinatebase.entityparent_id ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = compounddata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id WHERE datasets.id IN (', datasetIds, ') AND (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('germinatebase.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') GROUP BY germinatebase.id, dataset_id, year'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END