germinate_template_4_21_11_08
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_trials_data
Parameters
Name
Type
Mode
groupIds
text
IN
markedIds
text
IN
datasetIds
text
IN
phenotypeIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`phenotype_id` = ", `phenotype_id`,",phenotype_value,NULL)) AS ", "`", CONCAT(phenotypes.`name`, IF(ISNULL(phenotypes.unit_id) OR ISNULL(units.unit_abbreviation), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM phenotypedata LEFT JOIN phenotypes ON phenotypes.id = phenotypedata.phenotype_id LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN units ON units.id = phenotypes.unit_id WHERE phenotypedata.dataset_id IN (', datasetIds, ') AND ', IF(phenotypeIds IS NULL, '1=1', CONCAT('phenotype_id IN (', phenotypeIds, ')'))); 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, `synonyms`.`synonyms` AS `germplasm_synonyms`, `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, ( SELECT GROUP_CONCAT(site_name SEPARATOR ", ") FROM locations LEFT JOIN datasetlocations ON datasetlocations.location_id = locations.id WHERE datasetlocations.dataset_id = phenotypedata.dataset_id ) AS location_name, ( SELECT site_name FROM locations WHERE locations.id = phenotypedata.location_id ) AS trial_site, MAX(DATE_FORMAT(phenotypedata.recording_date, '%Y-%m-%d')) AS Date, phenotypedata.rep AS rep, germinatebase.id AS dbId, germinatebase.general_identifier, treatments.description AS treatments_description, ', IF(groupIds IS NULL, '', CONCAT('(SELECT concat("[", group_concat(CONCAT("\"", LEFT ( groups.NAME, 10 ), IF ( LENGTH( groups.NAME )> 10, "...\"", "\"" )) order by groups.name) , "]") 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 phenotypedata LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN `synonyms` on (`synonyms`.`synonymtype_id` = 1 AND `synonyms`.`foreign_id` = `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 = phenotypedata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id LEFT JOIN treatments ON treatments.id = phenotypedata.treatment_id WHERE (', 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, ')')))), ') AND datasets.id IN (', datasetIds, ') GROUP BY germinatebase.id, synonyms.synonyms, treatment_id, dataset_id, phenotypedata.location_id, phenotypedata.rep'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END