germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_passport_data
Parameters
Name
Type
Mode
germplasmIds
longtext
IN
groupIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`attribute_id` = ", `attribute_id`,",value,NULL)) AS ", "`", attributes.`name`, "`")) INTO @SQL FROM attributedata LEFT JOIN attributes ON attributes.id = attributedata.attribute_id WHERE `attributes`.`target_table` = "germinatebase"'); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; SET @SQL = CONCAT(' SELECT `germinatebase`.`name` AS `germplasm_name`,`germinatebase`.`id` AS `germplasm_id`,`germinatebase`.`general_identifier` AS `germplasm_gid`,`germinatebase`.`number` AS `germplasm_number`,`germinatebase`.`puid` AS `germplasm_puid`,`entitytypes`.`id` AS `entity_type_id`,`entitytypes`.`name` AS `entity_type_name`,`germinatebase`.`entityparent_id` AS `entity_parent_id`,`g`.`name` AS `entity_parent_name`,`g`.`general_identifier` AS `entity_parent_general_identifier`,`biologicalstatus`.`id` AS `biological_status_id`,`biologicalstatus`.`sampstat` AS `biological_status_name`,`synonyms`.`synonyms` AS `synonyms`,`germinatebase`.`collnumb` AS `collector_number`,`taxonomies`.`genus` AS `genus`,`taxonomies`.`species` AS `species`,`taxonomies`.`subtaxa` AS `subtaxa`,`institutions`.`id` AS `institution_id`,`institutions`.`name` AS `institution_name`,`locations`.`site_name` AS `location`,`locations`.`latitude` AS `latitude`,`locations`.`longitude` AS `longitude`,`locations`.`elevation` AS `elevation`,`countries`.`country_name` AS `country_name`,`countries`.`country_code2` AS `country_code`,`germinatebase`.`colldate` AS `coll_date`,`germinatebase`.`pdci` AS `pdci`,(select count(1) from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = "germinatebase") and (`images`.`foreign_id` = `germinatebase`.`id`))) AS `image_count`,(select `images`.`path` from (`images` left join `imagetypes` on((`imagetypes`.`id` = `images`.`imagetype_id`))) where ((`imagetypes`.`reference_table` = "germinatebase") and (`images`.`foreign_id` = `germinatebase`.`id`)) limit 1) AS `first_image_path`,(select 1 from `phenotypedata` where (`phenotypedata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_trials_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 1)) limit 1) AS `has_genotypic_data`,(select 1 from (`datasetmembers` left join `datasets` on((`datasets`.`id` = `datasetmembers`.`dataset_id`))) where ((`datasetmembers`.`foreign_id` = `germinatebase`.`id`) and (`datasetmembers`.`datasetmembertype_id` = 2) and (`datasets`.`datasettype_id` = 4)) limit 1) AS `has_allelefreq_data`,(select 1 from `compounddata` where (`compounddata`.`germinatebase_id` = `germinatebase`.`id`) limit 1) AS `has_compound_data`', IF(@SQL IS NULL, '', CONCAT(', ', @SQL)),' FROM `germinatebase` LEFT JOIN `germinatebase` `g` ON `g`.`id` = `germinatebase`.`entityparent_id` LEFT JOIN `institutions` ON `institutions`.`id` = `germinatebase`.`institution_id` LEFT JOIN `entitytypes` ON `germinatebase`.`entitytype_id` = `entitytypes`.`id` LEFT JOIN `taxonomies` ON `germinatebase`.`taxonomy_id` = `taxonomies`.`id` LEFT JOIN `locations` ON `germinatebase`.`location_id` = `locations`.`id` LEFT JOIN `countries` ON `locations`.`country_id` = `countries`.`id` LEFT JOIN `biologicalstatus` ON `biologicalstatus`.`id` = `germinatebase`.`biologicalstatus_id` LEFT JOIN `synonyms` ON (`synonyms`.`foreign_id` = `germinatebase`.`id` AND `synonyms`.`synonymtype_id` = 1) LEFT JOIN attributedata ON attributedata.foreign_id = germinatebase.id LEFT JOIN attributes ON (attributes.id = attributedata.attribute_id AND `attributes`.`target_table` = "germinatebase") ', IF (groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id'), ' WHERE (', IF(groupIds IS NULL AND germplasmIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(germplasmIds IS NULL), CONCAT('germinatebase.id IN (', germplasmIds, ')'), IF(germplasmIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', germplasmIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') GROUP BY germinatebase.id, synonyms.synonyms'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END