Internals
For users that need more detailed information about the software including database design etc.
Artcode
PROCEDURE GUC_AnpassungArtcodeTemp
-- setting the timestamp
delete from [dbo].[Artcode_LastChange] where Tabelle = 'ArtcodeTemp'
TRUNCATE TABLE [dbo].[Artcode_ArtID];
INSERT INTO [dbo].[Artcode_ArtID](NameID)
select T.ID
FROM ArtcodeTemp AS T
where T.Art_ID IS NULL
-- Setting the Art_ID for missing names
UPDATE T SET T.Art_ID = '9P0#' + REPLICATE('0', 6 - LEN(CAST(A.ID AS varchar))) + CAST(A.ID AS varchar)
-- select T.Art_ID, '9P0#' + REPLICATE('0', 6 - LEN(CAST(A.ID AS varchar))) + CAST(A.ID AS varchar)
FROM ArtcodeTemp AS T INNER JOIN [Artcode_ArtID] A ON T.ID = A.NameId
WHERE T.Art_ID IS NULL
-- Setting the family
TRUNCATE TABLE [dbo].[Artcode_Family]
INSERT INTO [dbo].[Artcode_Family]
([NameID]
,[FamilyNameID]
,[Family])
SELECT
T_0.NameID,
CASE WHEN T_1.TaxonomicRank = 'fam.' THEN T_1.NameID
ELSE
CASE WHEN T_2.TaxonomicRank = 'fam.' AND T_2.IgnoreButKeepForReference = 0 THEN T_2.NameID
ELSE
CASE WHEN T_3.TaxonomicRank = 'fam.' AND T_3.IgnoreButKeepForReference = 0 AND H_2.IgnoreButKeepForReference = 0
THEN T_3.NameID ELSE
CASE WHEN T_4.TaxonomicRank = 'fam.' AND T_4.IgnoreButKeepForReference = 0 AND H_3.IgnoreButKeepForReference = 0
THEN T_4.NameID
ELSE NULL
END END END END AS FamilyNameID,
CASE WHEN T_1.TaxonomicRank = 'fam.' THEN T_1.GenusOrSupragenericName ELSE CASE WHEN T_2.TaxonomicRank = 'fam.' AND
T_2.IgnoreButKeepForReference = 0 THEN T_2.GenusOrSupragenericName ELSE CASE WHEN T_3.TaxonomicRank = 'fam.' AND
T_3.IgnoreButKeepForReference = 0 AND
H_2.IgnoreButKeepForReference = 0 THEN T_3.GenusOrSupragenericName ELSE CASE WHEN T_4.TaxonomicRank = 'fam.' AND
T_4.IgnoreButKeepForReference = 0 AND H_3.IgnoreButKeepForReference = 0 THEN T_4.GenusOrSupragenericName ELSE NULL
END END END END AS Family
FROM dbo.TaxonHierarchy AS H_3 INNER JOIN
dbo.TaxonName AS T_4 ON H_3.NameParentID = T_4.NameID AND H_3.ProjectID = 1128 RIGHT OUTER JOIN
dbo.TaxonName AS T_3 INNER JOIN
dbo.TaxonHierarchy AS H_2 ON T_3.NameID = H_2.NameParentID AND H_2.ProjectID = 1128 ON
H_3.NameID = T_3.NameID RIGHT OUTER JOIN
dbo.TaxonHierarchy AS H_1 INNER JOIN
dbo.TaxonName AS T_2 ON H_1.NameParentID = T_2.NameID AND H_1.ProjectID = 1128 RIGHT OUTER JOIN
dbo.TaxonHierarchy AS H_0 INNER JOIN
dbo.TaxonName AS T_0 ON H_0.NameID = T_0.NameID AND H_0.ProjectID = 1128 INNER JOIN
dbo.TaxonName AS T_1 ON H_0.NameParentID = T_1.NameID AND H_0.ProjectID = 1128 ON H_1.NameID = T_1.NameID AND H_1.ProjectID = 1128 ON
H_2.NameID = T_2.NameID AND H_2.ProjectID = 1128
-- family for synonyms
INSERT INTO [dbo].[Artcode_Family]
([NameID]
,[FamilyNameID]
,[Family])
SELECT S.NameID, F.FamilyNameID, F.Family
FROM [Artcode_Family] F
INNER JOIN dbo.TaxonSynonymy S ON S.SynNameID = F.NameID AND S.ProjectID = 1128
AND NOT EXISTS(SELECT * FROM [Artcode_Family] A WHERE A.NameID = S.NameID)
UPDATE T SET T.fam = UPPER(SUBSTRING(F.Family, 1, 3)), T.id_fam = AF.Art_ID
-- select T.fam, UPPER(SUBSTRING(F.Family, 1, 3)), T.id_fam, AF.Art_ID
FROM ArtcodeTemp AS T INNER JOIN Artcode_Family F ON T.ID = F.NameID
INNER JOIN ArtcodeTemp AS AF ON AF.ID = F.FamilyNameID
--Ausgabesperre, Statistiksperre, Eingabesperre, SAP
UPDATE T SET T.Ausgabesperre = NULL, T.Statistiksperre = NULL, T.Eingabesperre = NULL, T.saP = NULL
-- select T.Ausgabesperre, Statistiksperre, Eingabesperre, SAP
FROM ArtcodeTemp AS T WHERE T.Ausgabesperre = 0
-- setting the rank
UPDATE T SET T.rang = R.rang_id
-- select T.rang, R.rang_id
FROM ArtcodeTemp AS T INNER JOIN [dbo].[Artcode_Rang] R ON R.Code = T.TaxonomicRank
-- setting the rank for synonyms
UPDATE T SET T.rang = 'syn'
-- select T.rang, 'syn'
FROM ArtcodeTemp AS T INNER JOIN TaxonSynonymy S ON T.ID = S.NameID and S.ProjectID = 1128 --and (T.rang is null or T.rang = '')
-- setting the Art_ID_Gueltig for synonyms
UPDATE T SET T.Art_ID_Gueltig = A.Art_ID
-- select T.Art_ID, T.Art_ID_Gueltig, A.Art_ID
FROM ArtcodeTemp AS T INNER JOIN TaxonSynonymy S ON T.ID = S.NameID and S.ProjectID = 1128 AND T.Art_ID_Gueltig IS NULL
INNER JOIN ArtcodeTemp A ON S.SynNameID = A.ID
-- Eintrag Art_ID_Gueltig wo er fehlt
UPDATE A SET A.Art_ID_Gueltig = CASE WHEN E.ExternalNameURI IS NULL THEN NULL ELSE E.ExternalNameURI END
-- SELECT A.Art_ID_Gueltig, CASE WHEN E.ExternalNameURI IS NULL THEN NULL ELSE E.ExternalNameURI END AS Art_ID_Gueltig_E
FROM dbo.TaxonNameExternalID AS E INNER JOIN
dbo.TaxonName AS N ON E.NameID = N.NameID AND E.ExternalDatabaseID = 1003 AND (N.IgnoreButKeepForReference = 0)
INNER JOIN ArtcodeTemp AS A ON A.ID = N.NameID AND A.Art_ID_Gueltig IS NULL
-- setting the Autor_Pos
UPDATE T SET T.Autor_Pos = 1
-- select T.Autor_Pos, 1, T.rang, N.TaxonNameCache, N.TaxonomicRank, ar.DisplayOrder
FROM ArtcodeTemp AS T
inner join TaxonName N on T.id = N.NameID and N.SpeciesEpithet = N.InfraspecificEpithet and N.BasionymAuthors <> ''
inner join Artcode_Rang as AR on N.TaxonomicRank = ar.Code and ar.DisplayOrder < 170
-- setting the Autor_Pos
UPDATE T SET T.Autor_Pos = 2
-- select T.Autor_Pos, 2, T.rang, N.TaxonNameCache, N.TaxonomicRank, ar.DisplayOrder, T.Autor
FROM ArtcodeTemp AS T
inner join TaxonName N on T.id = N.NameID and N.SpeciesEpithet like 'x %' and N.BasionymAuthors <> ''
inner join Artcode_Rang as AR on N.TaxonomicRank = ar.Code and ar.DisplayOrder < 170
-- setting Verantwortung Bayerns
UPDATE T SET T.VeraB = A.AnalysisValue
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 5
-- setting Verantwortung Deutschlands
UPDATE T SET T.VeraD = A.AnalysisValue
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 4
-- setting Gefährdung nach Roter Liste Bayern 2003
UPDATE T SET T.RLB = A.AnalysisValue
-- select T.RLB, A.AnalysisValue
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 1
-- setting Gefährung nach Roter Liste Deutschland 2018
UPDATE T SET T.RLD = A.AnalysisValue
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 62
-- setting Schutzstatus in Bayern
UPDATE T SET T.Schutz_BNatSchG = case when A.AnalysisValue = '§§' then 's' else 'b' end
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 7
UPDATE T SET T.sensu = N.NonNomenclaturalNameSuffix
-- select T.sensu, N.NonNomenclaturalNameSuffix
FROM ArtcodeTemp AS T INNER JOIN [TaxonName] N ON T.ID = N.NameID and N.NonNomenclaturalNameSuffix <> ''
-- setting Schutzstatus in Bayern
UPDATE T SET T.Schutz_BNatSchG = case when A.AnalysisValue = '§§' then 's' else 'b' end
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 7
-- setting id_agg
UPDATE T SET T.id_agg = case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
-- select T.id_agg, case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonHierarchy] H ON T.ID = H.NameID AND H.ProjectID = 1128
inner join ArtcodeTemp P on H.NameParentID = P.ID
inner join dbo.TaxonAcceptedName A on T.id = A.NameID and A.ProjectID = 1128
-- setting id_agg for not accepted names
UPDATE T SET T.id_agg = case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
-- select T.id_agg, case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonHierarchy] H ON T.ID = H.NameID AND H.ProjectID = 1128
inner join ArtcodeTemp P on H.NameParentID = P.ID
WHERE T.id_agg IS NULL OR T.id_agg = ''
-- setting id_agg for not synynyms missing a hierarchy
UPDATE T SET T.id_agg = case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
-- select T.id_agg, case when P.Art_ID_Gueltig is null then P.Art_ID else P.Art_ID_Gueltig end
FROM ArtcodeTemp AS T
INNER JOIN [dbo].[TaxonSynonymy] S ON S.NameID = T.ID AND S.ProjectID = 1128
INNER JOIN [dbo].[TaxonHierarchy] H ON S.SynNameID = H.NameID AND H.ProjectID = 1128
inner join ArtcodeTemp P on H.NameParentID = P.ID
WHERE T.id_agg IS NULL OR T.id_agg = ''
-- setting BOLD_ID
UPDATE T SET T.BOLD_ID = E.ExternalNameURI
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameExternalID] E ON T.ID = E.NameID and E.ExternalDatabaseID = 1010
-- setting Bayernstatus
UPDATE T SET T.staby = B.staby
FROM ArtcodeTemp AS T INNER JOIN [dbo].[TaxonNameListAnalysis] A ON T.ID = A.NameID AND A.AnalysisID = 2
INNER JOIN [dbo].[Artcode_Bayernstatus] B ON B.[AnalysisValue] = A.AnalysisValue
INSERT INTO [dbo].[Artcode_LastChange] ([Tabelle])
VALUES ('ArtcodeTemp')
-- setting the timestamp
delete from [dbo].[Artcode_LastChange] where Tabelle = 'Artcode'
TRUNCATE TABLE [dbo].[Artcode]
INSERT INTO Artcode
(Ordnung, Art_ID, Synonym, Art_ID_Gueltig, Gattung, Art, Autor, Autor_Pos, Name_Deutsch, RLB, RLD, FFH_Anh2, FFH_Anh4, FFH_Anh5, VSR_Anh1, Status_IUCN, Schutz_BNatSchG, Ausgabesperre, Statistiksperre,
Eingabesperre, saP, saP_ABC, saP_BC, saP_ZR, rang, sensu, id_agg, staby, VeraB, VeraD, WnASA, EUBrd, BASVO, BOLD_ID, NAME_DG, fam, id_fam, pruefung, ID)
SELECT Ordnung, Art_ID, Synonym, Art_ID_Gueltig, Gattung, Art, Autor, Autor_Pos, Name_Deutsch, RLB, RLD, FFH_Anh2, FFH_Anh4, FFH_Anh5, VSR_Anh1, Status_IUCN, Schutz_BNatSchG, Ausgabesperre, Statistiksperre,
Eingabesperre, saP, saP_ABC, saP_BC, saP_ZR, rang, sensu, id_agg, staby, VeraB, VeraD, WnASA, EUBrd, BASVO, BOLD_ID, NAME_DG, fam, id_fam, pruefung, ID
FROM ArtcodeTemp
INSERT INTO [dbo].[Artcode_LastChange] ([Tabelle])
VALUES ('Artcode')