Project

General

Profile

Actions

task #10174

open

Export data to BfN

Added by Andreas Müller 3 months ago. Updated 3 months ago.

Status:
In Progress
Priority:
Priority14
Category:
data
Target version:
Start date:
Due date:
% Done:

60%

Estimated time:
Severity:
normal

Description

for testing BfN import

Open Issues:

  • homonyms
  • MAN
  • pesi
Actions #1

Updated by Andreas Müller 3 months 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#%'

Actions #2

Updated by Andreas Müller 3 months ago

  • % Done changed from 0 to 60
Actions #3

Updated by Andreas Müller 3 months ago

  • Tags changed from export, euro+med to export, euro+med, pesi
  • Description updated (diff)
Actions #4

Updated by Andreas Müller 3 months ago

  • Status changed from New to In Progress
  • Priority changed from New to Priority14
Actions #5

Updated by Andreas Müller 3 months ago

  • Target version changed from Release 5.35 to Release 5.36
Actions

Also available in: Atom PDF