Actions
task #10174
closedExport data to BfN
Start date:
Due date:
% Done:
100%
Estimated time:
Severity:
normal
Description
for testing BfN import
Open Issues:
- homonyms
- MAN
- pesi
Updated by Andreas Müller over 1 year ago
- Tags changed from export to export, euro+med
SELECT CASE WHEN orderIndex < 34 THEN CONCAT(RANK, ' ', VOLLNAME) ELSE VOLLNAME END VOLLNAME, TAXON_ID, -- RANK, orderIndex, statusId, statusLabel, TAXON_CHILD_OF, SYN_FLAG, TAXONSTATUS, TAXON_UUID FROM ( SELECT CASE WHEN repSt.id IS NULL THEN n.titleCache ELSE CONCAT(n.titleCache, ', ', repSt.abbreviatedLabel ) END VOLLNAME, tb.id TAXON_ID, rep.abbreviatedLabel RANK, r.orderIndex, pTb.id TAXON_CHILD_OF, '1' SYN_FLAG, CASE WHEN tb.doubtful <> 0 OR tn.status = 'DOU' THEN 'fraglich' WHEN tn.status like 'EXC%' THEN 'ausgeschlossen' ELSE 'akzeptiert' END TAXONSTATUS, tb.uuid TAXON_UUID, repSt.id statusId, repSt.abbreviatedLabel statusLabel FROM TaxonNode tn INNER JOIN TaxonBase tb ON tn.taxon_id = tb.id INNER JOIN TaxonName n ON n.id = tb.name_id LEFT JOIN DefinedTermBase r ON n.rank_id = r.id LEFT JOIN DefinedTermBase_Representation MN ON r.id = MN.DefinedTermBase_id LEFT JOIN Representation rep ON MN.representations_id = rep.id LEFT JOIN NomenclaturalStatus st ON st.name_id = n.id LEFT JOIN DefinedTermBase stType ON st.type_id = stType.id LEFT JOIN DefinedTermBase_Representation MN2 ON stType.id = MN2.DefinedTermBase_id LEFT JOIN Representation repSt ON MN2.representations_id = repSt.id LEFT JOIN TaxonNode pTn ON tn.parent_id = pTn.id LEFT JOIN TaxonBase pTb ON pTb.id = pTn.taxon_id WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%' UNION ALL SELECT CASE WHEN repSt.id IS NULL THEN CONCAT(CASE WHEN n.homotypicalGroup_id = accN.homotypicalGroup_id THEN '≡ ' ELSE '= ' END, n.titleCache) ELSE CONCAT(CASE WHEN n.homotypicalGroup_id = accN.homotypicalGroup_id THEN '≡ ' ELSE '= ' END, n.titleCache, ', ', repSt.abbreviatedLabel ) END VOLLNAME, acc.id TAXON_ID, rep.abbreviatedLabel RANK, r.orderIndex, NULL, CASE WHEN nr.id IS NOT NULL THEN 'b' ELSE 'x' END SYN_FLAG, NULL, NULL, repSt.id statusId, repSt.abbreviatedLabel statusLabel FROM TaxonNode tn INNER JOIN TaxonBase acc ON tn.taxon_id = acc.id INNER JOIN TaxonBase syn ON syn.acceptedTaxon_id = acc.id INNER JOIN TaxonName n ON n.id = syn.name_id INNER JOIN TaxonName accN ON accN.id = acc.name_id LEFT JOIN DefinedTermBase r ON n.rank_id = r.id LEFT JOIN DefinedTermBase_Representation MN ON r.id = MN.DefinedTermBase_id LEFT JOIN Representation rep ON MN.representations_id = rep.id LEFT JOIN NomenclaturalStatus st ON st.name_id = n.id -- erzeugt noch 2 Duplikate LEFT JOIN DefinedTermBase stType ON st.type_id = stType.id LEFT JOIN DefinedTermBase_Representation MN2 ON stType.id = MN2.DefinedTermBase_id LEFT JOIN Representation repSt ON MN2.representations_id = repSt.id LEFT JOIN NameRelationship nr ON nr.relatedFrom_id = n.id AND nr.relatedTo_id = accN.id AND nr.type_id = 920 WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%' UNION ALL SELECT CONCAT (n.titleCache, CASE WHEN rel.type_id = 956 THEN ' p. p.' WHEN rel.type_id = 955 THEN ' pro partim' END ) AS VOLLNAME , acc.id TAXON_ID, rep.abbreviatedLabel RANK, r.orderIndex, NULL, 'x', NULL, NULL, NULL, NULL FROM TaxonNode tn INNER JOIN TaxonBase acc ON tn.taxon_id = acc.id INNER JOIN TaxonRelationship rel ON acc.id = rel.relatedTo_id INNER JOIN DefinedTermBase relType ON relType.id = rel.type_id AND rel.type_id IN (956, 955) INNER JOIN TaxonBase man ON man.id = rel.relatedFrom_id INNER JOIN TaxonName n ON n.id = man.name_id INNER JOIN TaxonName accN ON accN.id = acc.name_id LEFT JOIN DefinedTermBase r ON n.rank_id = r.id LEFT JOIN DefinedTermBase_Representation MN ON r.id = MN.DefinedTermBase_id LEFT JOIN Representation rep ON MN.representations_id = rep.id WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%' UNION ALL SELECT CONCAT ('–', n.titleCache, CASE WHEN rel.type_id = 960 THEN ' auct.' WHEN rel.type_id = 959 THEN ' excl. typo' END ) AS VOLLNAME , acc.id TAXON_ID, rep.abbreviatedLabel RANK, r.orderIndex, NULL, 'x', NULL, NULL, NULL, NULL FROM TaxonNode tn INNER JOIN TaxonBase acc ON tn.taxon_id = acc.id INNER JOIN TaxonRelationship rel ON acc.id = rel.relatedTo_id INNER JOIN DefinedTermBase relType ON relType.id = rel.type_id AND rel.type_id IN (959, 960) INNER JOIN TaxonBase man ON man.id = rel.relatedFrom_id INNER JOIN TaxonName n ON n.id = man.name_id INNER JOIN TaxonName accN ON accN.id = acc.name_id LEFT JOIN DefinedTermBase r ON n.rank_id = r.id LEFT JOIN DefinedTermBase_Representation MN ON r.id = MN.DefinedTermBase_id LEFT JOIN Representation rep ON MN.representations_id = rep.id WHERE tn.treeIndex LIKE '#t10#10#56284#54730#%' ) AS uni -- WHERE SYN_FLAG = '1' AND TAXONSTATUS like 'aus%' -- WHERE orderIndex < 34 -- WHERE statusID IS NOT NULL -- WHERE VOLLNAME LIKE '≡%' -- GROUP BY VOLLNAME HAVING COUNT(*) > 1 ORDER BY VOLLNAME, TAXON_ID
For Lilianae: '#t10#10#56284#54730#51914#54954#50717#%'
Updated by Andreas Müller over 1 year ago
- Tags changed from export, euro+med to export, euro+med, pesi
- Description updated (diff)
Updated by Andreas Müller over 1 year ago
- Status changed from New to In Progress
- Priority changed from New to Priority14
Updated by Andreas Müller over 1 year ago
- Target version changed from Release 5.35 to Release 5.44
Updated by Andreas Müller 2 months ago
- Target version changed from Release 5.44 to Release 5.43
Updated by Andreas Müller about 1 month ago
- Status changed from In Progress to Closed
- Target version deleted (
Release 5.43) - % Done changed from 60 to 100
For now this should be enough. Improve whenever a new export is needed again.
Actions