Project

General

Profile

Revision be2b30e1

IDbe2b30e1004eed0f38704f02a831816bfa78f2d7
Parent eb191b81
Child 7d431f85

Added by Andreas Müller over 9 years ago

move check master to test package

View differences:

.gitattributes
1343 1343
cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/out/PesiSourceExport.java -text
1344 1344
cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/out/PesiTaxonExport.java -text
1345 1345
cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/out/PesiTransformer.java -text
1346
cdm-pesi/src/main/resources/Check_Import_Master.sql -text
1347 1346
cdm-pesi/src/main/resources/log4j.properties -text
1348
cdm-pesi/src/main/resources/sql/Check_Import_Master.sql -text
1349 1347
cdm-pesi/src/test/java/eu/etaxonomy/cdm/io/common/TestConnection.java -text
1350 1348
cdm-pesi/src/test/java/eu/etaxonomy/cdm/io/erms/ermsErrors.txt -text
1349
cdm-pesi/src/test/resources/sql/Check_Import_Master.sql -text
cdm-pesi/src/main/resources/Check_Import_Master.sql
1
BEGIN
2

  
3
	DECLARE @n INT
4
	DECLARE @n_cdm INT
5
	DECLARE @n_bm INT
6
	DECLARE @str_n NVARCHAR (7)
7
	DECLARE @str_n_cdm NVARCHAR (7)
8
	DECLARE @str_n_bm NVARCHAR (7)
9

  
10

  
11
------------------------------------------- Taxon -------------------------------
12

  
13
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON
14
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON
15
	SET @n = @n_bm - @n_cdm
16

  
17
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
18
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
19
	SET @str_n = Cast(@n AS NVARCHAR)
20

  
21
	IF @n = 0 BEGIN
22
		PRINT ('Both databases have the same number of taxa = ' + @str_n_bm)
23
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
24
	SELECT @n = COUNT(*)
25
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
26
		[CDM_EM2PESI].[DBO].TAXON cdm_t
27
		ON (bm_t.IdInSource = cdm_t.IdInSource OR bm_t.IdInSource IS NULL AND cdm_t.IdInSource IS NULL) 
28
		    AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
29
	SET @str_n = Cast(@n AS NVARCHAR)
30
	IF @n = @n_bm BEGIN
31
		PRINT ('To each taxon from one database there is an identical taxon in the other one (IdInSource, GUID)')
32
	END ELSE PRINT ('WARNING: Only ' + @str_n + ' taxa are identical (IdInSource, GUID) in both databases')
33

  
34
		/*
35
	SELECT cdm_t.Fullname, cdm_t.IdInSource, cdm_t.GUID, 'in CDM_EM2PESI but not in EM2PESI'
36
	FROM [CDM_EM2PESI].[DBO].TAXON cdm_t
37
	WHERE NOT EXISTS
38
		(SELECT * FROM [EM2PESI].[DBO].TAXON WHERE (IdInSource = cdm_t.IdInSource OR IdInSource IS NULL AND cdm_t.IdInSource IS NULL)
39
		AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
40
	ORDER BY cdm_t.IdInSource
41

  
42
	SELECT bm_t.Fullname, bm_t.IdInSource, bm_t.GUID, 'in EM2PESI but not in CDM_EM2PESI'
43
	FROM [EM2PESI].[DBO].TAXON bm_t
44
	WHERE NOT EXISTS
45
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON WHERE (IdInSource = bm_t.IdInSource OR IdInSource IS NULL AND bm_t.IdInSource IS NULL)
46
		AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
47
	ORDER BY bm_t.IdInSource
48

  
49
	*/
50

  
51
-- Source
52
/* Does not make much sense since the source for taxa is almost always the database itself which must be created as source
53
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
54
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
55
	SET @n = @n_bm - @n_cdm
56

  
57
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
58
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
59
	SET @str_n = Cast(@n AS NVARCHAR)
60

  
61
	IF @n = 0 BEGIN
62
		PRINT ('Both databases have the same number of taxa which have a source = ' + @str_n_bm)
63
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a source, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
64

  
65
		SELECT @n = COUNT(*) -- in both databases sources exist but are different
66
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
67
			[CDM_EM2PESI].[DBO].TAXON cdm_t
68
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
69
			[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
70
			[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
71
			WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
72
		SET @str_n = Cast(@n AS NVARCHAR)
73
		IF @n = 0 BEGIN
74
			PRINT ('All identical taxa that have sources have the same source')
75
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different sources')
76
	/-*
77
		SELECT cdm_t.Fullname, cdm_s.RefIdInSource as Source_CDM_EM2PESI, bm_s.RefIdInSource as Source_EM2PESI
78
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
79
		[CDM_EM2PESI].[DBO].TAXON cdm_t
80
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
81
		[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
82
		[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
83
		WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
84
	*-/
85
		SELECT @n_bm = COUNT(*) -- taxa with source only in EM2PESI
86
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
87
			[CDM_EM2PESI].[DBO].TAXON cdm_t
88
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
89
			[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
90
			WHERE NOT EXISTS
91
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
92
				[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
93
				WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
94
				AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
95
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
96
		IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' taxa have sources in EM2PESI but not in CDM_EM2PESI')
97
	/-*
98
		SELECT cdm_t.Fullname, bm_s.Fullname as source_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
99
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
100
		[CDM_EM2PESI].[DBO].TAXON cdm_t
101
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
102
		[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
103
		WHERE NOT EXISTS
104
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
105
		[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
106
		WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
107
		AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
108
	*-/
109
		SELECT @n_cdm = COUNT(*) -- taxa with source only in CDM_EM2PESI
110
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
111
			[CDM_EM2PESI].[DBO].TAXON cdm_t
112
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
113
			[CDM_EM2PESI].[DBO].SOURCE cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
114
			WHERE NOT EXISTS
115
				(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
116
				[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
117
				WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
118
				AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
119
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
120
		IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' taxa have sources in CDM_EM2PESI but not in EM2PESI')
121
	/-*
122
		SELECT cdm_t.Fullname, cdm_s.Fullname as source_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
123
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
124
		[CDM_EM2PESI].[DBO].TAXON cdm_t
125
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
126
		[CDM_EM2PESI].[DBO].SOURCE cdm_s ON bm_t.SourceFk = cdm_s.SourceId
127
		(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
128
		[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
129
		WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
130
		AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
131
	*-/
132
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
133
			PRINT ('All identical taxa have the same source')
134
		END
135

  
136
*/
137

  
138
-- Authors
139
	SELECT @n = COUNT(*)
140
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
141
		CDM_EM2PESI.[DBO].TAXON cdm_t
142
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
143
		WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
144
	SET @str_n = Cast(@n AS NVARCHAR)
145
	IF @n = 0 BEGIN
146
		PRINT ('All identical taxa have the same authors')
147
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same authors')
148

  
149
	/*
150
		SELECT cdm_t.TaxonId as ID_CDM, bm_t.TaxonId as ID_SQL, cdm_t.FullName as FullName_CDM, bm_t.FullName as FullName_SQL,
151
			cdm_t.AuthorString as AuthorString_CDM, bm_t.AuthorString as AuthorString_SQL
152
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
153
		CDM_EM2PESI.[DBO].TAXON cdm_t
154
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
155
		WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
156
	*/
157

  
158
-- Epithets
159
	SELECT @n = COUNT(*)
160
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
161
		[CDM_EM2PESI].[DBO].TAXON cdm_t
162
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
163
		WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
164
		ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
165
		ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
166
		ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
167
	SET @str_n = Cast(@n AS NVARCHAR)
168
	IF @n = 0 BEGIN
169
		PRINT ('All identical taxa have the same epithets')
170
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same epithets')
171

  
172
	/*
173
	SELECT  cdm_t.GenusOrUninomial as Genus_CDM, bm_t.GenusOrUninomial as Genus_SQL,
174
			cdm_t.InfraGenericEpithet as Infragenus_CDM, bm_t.InfraGenericEpithet as Infragenus_SQL,
175
			cdm_t.SpecificEpithet  as SpecificEpithet_CDM, bm_t.SpecificEpithet as SpecificEpithet_SQL,
176
			cdm_t.InfraSpecificEpithet as Infraspec_CDM, bm_t.InfraSpecificEpithet as Infraspec_SQL
177
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
178
		CDM_EM2PESI.[DBO].TAXON cdm_t
179
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
180
		WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
181
		ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
182
		ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
183
		ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
184
	*/
185

  
186
-- Names
187
	SELECT @n = COUNT(*)
188
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
189
		[CDM_EM2PESI].[DBO].TAXON cdm_t
190
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
191
		WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
192
		ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
193
		ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
194
	SET @str_n = Cast(@n AS NVARCHAR)
195
	IF @n = 0 BEGIN
196
		PRINT ('All identical taxa have the same scientific name')
197
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same scientific name')
198
	/*
199
	SELECT cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
200
		cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
201
		cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
202
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
203
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
204
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
205
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
206
	CDM_EM2PESI.[DBO].TAXON cdm_t
207
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
208
	WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
209
		ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
210
		ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
211
	*/
212

  
213
-- Full Name
214
	SELECT @n = COUNT(*)
215
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
216
		CDM_EM2PESI.[DBO].TAXON cdm_t
217
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
218
		WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
219
	SET @str_n = Cast(@n AS NVARCHAR)
220
	IF @n = 0 BEGIN
221
		PRINT ('All identical taxa have the same full name')
222
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same full name')
223

  
224
/*
225
	SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
226
		cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
227
		cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
228
		cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
229
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
230
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
231
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
232
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
233
		CDM_EM2PESI.[DBO].TAXON cdm_t
234
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
235
	WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
236
	ORDER BY cdm_t.FullName
237
	*/
238

  
239
-- Nom Ref
240

  
241
	SELECT @n = COUNT(*)
242
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
243
		CDM_EM2PESI.[DBO].TAXON cdm_t
244
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
245
		WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
246
	SET @str_n = Cast(@n AS NVARCHAR)
247
	IF @n = 0 BEGIN
248
		PRINT ('All identical taxa have the same nomenclatural reference')
249
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same nomenclatural reference')
250
	/*
251
	SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
252
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
253
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
254
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
255
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
256
		CDM_EM2PESI.[DBO].TAXON cdm_t
257
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
258
	WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
259
	ORDER BY cdm_t.NomRefString
260
	*/
261

  
262
-- Ranks
263
	SELECT @n = COUNT(*)
264
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
265
		[CDM_EM2PESI].[DBO].TAXON cdm_t
266
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
267
		WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
268
		ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
269
		ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
270
	SET @str_n = Cast(@n AS NVARCHAR)
271
	IF @n = 0 BEGIN
272
		PRINT ('All identical taxa have the same rank (KingdomFk, RankFk, RankCache)')
273
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same rank (KingdomFk, RankFk, RankCache)')
274
	/*
275
	SELECT cdm_t.Fullname, cdm_t.KingdomFk as Kingdom_CDM_EM2PESI, bm_t.KingdomFk as Kingdom_EM2PESI,
276
	cdm_t.RankFk as RankFk_CDM_EM2PESI, bm_t.RankFk as RankFk_EM2PESI,
277
	cdm_t.RankCache as Rank_CDM_EM2PESI, bm_t.RankCache as Rank_EM2PESI
278
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
279
	[CDM_EM2PESI].[DBO].TAXON cdm_t
280
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
281
	WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
282
	ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
283
	ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
284
	*/
285

  
286
-- Status
287
	SELECT @n = COUNT(*)
288
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
289
		[CDM_EM2PESI].[DBO].TAXON cdm_t
290
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
291
		WHERE ISNULL(bm_t.NameStatusCache, '') <> ISNULL(cdm_t.NameStatusCache, '') OR
292
		ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '')
293
	SET @str_n = Cast(@n AS NVARCHAR)
294
	IF @n = 0 BEGIN
295
		PRINT ('All identical taxa have the same status (NameStatusCache, TaxonStatusCache)')
296
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same status (NameStatusCache, TaxonStatusCache)')
297
	/*
298
	SELECT cdm_t.Fullname, cdm_t.NameStatusFk as NameStatusFk_CDM_EM2PESI, bm_t.NameStatusFk as NameStatusFk_EM2PESI,
299
	cdm_t.NameStatusCache as NameStatus_CDM_EM2PESI, bm_t.NameStatusCache as NameStatus_EM2PESI,
300
	cdm_t.TaxonStatusFk as TaxonStatusFk_CDM_EM2PESI, bm_t.TaxonStatusFk as TaxonStatusFk_EM2PESI,
301
	cdm_t.TaxonStatusCache as TaxonStatus_CDM_EM2PESI, bm_t.TaxonStatusCache as TaxonStatus_EM2PESI
302
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
303
	[CDM_EM2PESI].[DBO].TAXON cdm_t
304
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
305
	WHERE ISNULL(bm_t.NameStatusCache, '') <> ISNULL(cdm_t.NameStatusCache, '') OR
306
	ISNULL(bm_t.TaxonStatusCache, '') <> ISNULL(cdm_t.TaxonStatusCache, '')
307
	*/
308

  
309
-- Parents
310
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
311
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
312
	SET @n = @n_bm - @n_cdm
313

  
314
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
315
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
316
	SET @str_n = Cast(@n AS NVARCHAR)
317

  
318
	IF @n = 0 BEGIN
319
		PRINT ('Both databases have the same number of taxa which have a parent = ' + @str_n_bm)
320
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a parent, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
321

  
322
		SELECT @n = COUNT(*) -- in both databases parents exist but are different
323
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
324
			[CDM_EM2PESI].[DBO].TAXON cdm_t
325
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
326
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
327
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
328
			WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
329
		SET @str_n = Cast(@n AS NVARCHAR)
330
		IF @n = 0 BEGIN
331
			PRINT ('All identical taxa that have parents have the same parent')
332
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
333
	/*
334
		SELECT  cdm_t.FullName as Child_CDM, cdm_pt.Fullname as Parent_CDM_EM2PESI, bm_t.FullName as child_sql,  bm_pt.Fullname as Parent_EM2PESI
335
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
336
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
337
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
338
		[	CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
339
		WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
340
	*/
341
		SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI
342
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
343
			[CDM_EM2PESI].[DBO].TAXON cdm_t
344
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
345
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
346
			WHERE NOT EXISTS
347
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
348
				WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
349
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
350
		IF @n_bm > 0 BEGIN 
351
			PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
352
		END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
353

  
354
	/*
355
		SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
356
		FROM [EM2PESI].[DBO].TAXON bm_t 
357
			INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
358
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') 
359
		
360
		WHERE cdm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
361
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt
362
		WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL) 
363
				AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '')
364
				AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId)
365
		ORDER BY cdm_t.Fullname
366

  
367
	*/
368
		SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI
369
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
370
			[CDM_EM2PESI].[DBO].TAXON cdm_t
371
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
372
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
373
			WHERE NOT EXISTS
374
				(SELECT * FROM [EM2PESI].[DBO].TAXON
375
				WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
376
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
377
		IF @n_cdm > 0 BEGIN 
378
			PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
379
		END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
380
		
381
	/*
382
		SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI'
383
		FROM [CDM_EM2PESI].[DBO].TAXON cdm_t 
384
			INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
385
			INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '') 
386
		
387
		WHERE bm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
388
		(SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt
389
		WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL) 
390
				AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '')
391
				AND bm_t.ParentTaxonFk = bm_pt.TaxonId)
392
		ORDER BY bm_t.Fullname
393
	*/
394
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
395
			PRINT ('All identical taxa have the same parent')
396
		END
397

  
398
-- TreeIndex
399
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths.
400
	Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole.
401
	See the Parent section
402
*/
403
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
404
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
405
	SET @n = @n_bm - @n_cdm
406

  
407
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
408
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
409
	SET @str_n = Cast(@n AS NVARCHAR)
410

  
411
	IF @n = 0 BEGIN
412
		PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
413
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a tree index, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
414

  
415

  
416
-- Types
417
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
418
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
419
	SET @n = @n_bm - @n_cdm
420

  
421
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
422
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
423
	SET @str_n = Cast(@n AS NVARCHAR)
424

  
425
	IF @n = 0 BEGIN
426
		PRINT ('Both databases have the same number of taxa which have a type = ' + @str_n_bm)
427
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a type, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
428

  
429
		SELECT @n = COUNT(*) -- in both databases types exist but are different
430
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
431
			[CDM_EM2PESI].[DBO].TAXON cdm_t
432
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
433
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId INNER JOIN
434
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
435
			WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
436
		SET @str_n = Cast(@n AS NVARCHAR)
437
		IF @n = 0 BEGIN
438
			PRINT ('All identical taxa that have types have the same type')
439
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different types')
440
	/*
441
		SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, bm_pt.Fullname as Type_EM2PESI
442
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
443
		[CDM_EM2PESI].[DBO].TAXON cdm_t
444
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
445
		[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId INNER JOIN
446
		[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
447
		WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
448
		ORDER BY cdm_t.Fullname
449
	*/
450
		SELECT @n_bm = COUNT(*) -- taxa with type only in EM2PESI
451
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
452
			[CDM_EM2PESI].[DBO].TAXON cdm_t
453
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
454
			[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
455
			WHERE NOT EXISTS
456
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
457
				WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
458
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
459
		IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have types in EM2PESI but not in CDM_EM2PESI')
460
	/*
461
		SELECT cdm_t.Fullname, bm_pt.Fullname as Type_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
462
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
463
		[CDM_EM2PESI].[DBO].TAXON cdm_t
464
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
465
		[EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
466
		WHERE NOT EXISTS
467
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
468
		WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
469
		ORDER BY cdm_t.Fullname
470
	*/
471
		SELECT @n_cdm = COUNT(*) -- taxa with type only in CDM_EM2PESI
472
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
473
			[CDM_EM2PESI].[DBO].TAXON cdm_t
474
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
475
			[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
476
			WHERE NOT EXISTS
477
				(SELECT * FROM [EM2PESI].[DBO].TAXON
478
				WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
479
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
480
		IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have types in CDM_EM2PESI but not in EM2PESI')
481
	/*
482
		SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
483
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
484
		[CDM_EM2PESI].[DBO].TAXON cdm_t
485
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
486
		[CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
487
		WHERE NOT EXISTS
488
		(SELECT * FROM [EM2PESI].[DBO].TAXON
489
		WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
490
	*/
491
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
492
			PRINT ('All identical taxa have the same type')
493
		END
494

  
495
-- QualityStatus
496
	SELECT @n = COUNT(*)
497
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
498
		[CDM_EM2PESI].[DBO].TAXON cdm_t
499
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
500
		WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
501
	SET @str_n = Cast(@n AS NVARCHAR)
502
	IF @n = 0 BEGIN
503
		PRINT ('All identical taxa have the same quality status (QualityStatusCache)')
504
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same quality status (QualityStatusCache)')
505
	/*
506
	SELECT cdm_t.Fullname, cdm_t.QualityStatusCache as QualityStatus_CDM_EM2PESI, bm_t.QualityStatusCache as QualityStatus_EM2PESI
507
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
508
	[CDM_EM2PESI].[DBO].TAXON cdm_t
509
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
510
	WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
511
	*/
512

  
513
-- Experts
514
	SELECT @n = COUNT(*)
515
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
516
		[CDM_EM2PESI].[DBO].TAXON cdm_t
517
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
518
		WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
519
		ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
520
	SET @str_n = Cast(@n AS NVARCHAR)
521
	IF @n = 0 BEGIN
522
		PRINT ('All identical taxa have the same experts (ExpertName, SpeciesExpertName)')
523
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same experts (ExpertName, SpeciesExpertName)')
524
	/*
525
	SELECT cdm_t.Fullname, cdm_t.ExpertName as ExpertName_CDM_EM2PESI, bm_t.ExpertName as ExpertName_EM2PESI,
526
	cdm_t.SpeciesExpertName as SpeciesExpertName_CDM_EM2PESI, bm_t.SpeciesExpertName as SpeciesExpertName_EM2PESI
527
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
528
	[CDM_EM2PESI].[DBO].TAXON cdm_t
529
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
530
	WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
531
	ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
532
	*/
533

  
534
-- Citation
535
	SELECT @n = COUNT(*)
536
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
537
		[CDM_EM2PESI].[DBO].TAXON cdm_t
538
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
539
		WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
540
	SET @str_n = Cast(@n AS NVARCHAR)
541
	IF @n = 0 BEGIN
542
		PRINT ('All identical taxa have the same CacheCitation')
543
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same CacheCitation')
544
	/*
545
	SELECT cdm_t.Fullname, cdm_t.CacheCitation as CacheCitation_CDM_EM2PESI, bm_t.CacheCitation as CacheCitation_EM2PESI
546
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
547
	[CDM_EM2PESI].[DBO].TAXON cdm_t
548
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
549
	WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
550
	*/
551

  
552
-- Original database
553
	SELECT @n = COUNT(*)
554
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
555
		[CDM_EM2PESI].[DBO].TAXON cdm_t
556
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
557
		WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
558
	SET @str_n = Cast(@n AS NVARCHAR)
559
	IF @n = 0 BEGIN
560
		PRINT ('All identical taxa have the same OriginalDB')
561
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same OriginalDB')
562
	/*
563
	SELECT cdm_t.Fullname, cdm_t.OriginalDB as OriginalDB_CDM_EM2PESI, bm_t.OriginalDB as OriginalDB_EM2PESI
564
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
565
	[CDM_EM2PESI].[DBO].TAXON cdm_t
566
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
567
	WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
568
	*/
569

  
570
-- Action
571
	SELECT @n = COUNT(*)
572
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
573
		[CDM_EM2PESI].[DBO].TAXON cdm_t
574
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
575
		WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
576
		convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00'))  <>
577
		convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
578
	SET @str_n = Cast(@n AS NVARCHAR)
579
	IF @n = 0 BEGIN
580
		PRINT ('All identical taxa have the same last actions (LastAction, LastActionDate)')
581
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the last actions (LastAction, LastActionDate)')
582
	/*
583
	SELECT cdm_t.Fullname, cdm_t.LastAction as LastAction_CDM_EM2PESI, bm_t.LastAction as LastAction_EM2PESI,
584
	cdm_t.LastActionDate as LastActionDate_CDM_EM2PESI, bm_t.LastActionDate as LastActionDate_EM2PESI
585
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
586
	[CDM_EM2PESI].[DBO].TAXON cdm_t
587
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
588
	WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
589
	convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00')) <>
590
	convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
591
	*/
592

  
593
------------------------------------------- RelTaxon -------------------------------
594
	PRINT ' '
595
	PRINT 'RELATIONSHIP'
596
	
597
-- taxonomical relationships between taxa and nomenclatural relationships between names
598
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].RELTAXON
599
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].RELTAXON
600
	SET @n = @n_bm - @n_cdm
601

  
602
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
603
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
604
	SET @str_n = Cast(@n AS NVARCHAR)
605

  
606
	IF @n = 0 BEGIN
607
		PRINT ('Both databases have the same number of relationship records = ' + @str_n_bm)
608
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of relationship records, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
609

  
610
		SELECT @n_bm = COUNT(*) -- relationships only in EM2PESI
611
			FROM [EM2PESI].[DBO].RELTAXON bm_rt INNER JOIN
612
			[EM2PESI].[DBO].TAXON bm_t1 ON bm_rt.TaxonFk1 = bm_t1.TaxonId INNER JOIN
613
			[EM2PESI].[DBO].TAXON bm_t2 ON bm_rt.TaxonFk2 = bm_t2.TaxonId INNER JOIN
614
			[CDM_EM2PESI].[DBO].TAXON cdm_t1
615
			ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
616
			[CDM_EM2PESI].[DBO].TAXON cdm_t2
617
			ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
618
			WHERE NOT EXISTS
619
			(SELECT * FROM [CDM_EM2PESI].[DBO].RELTAXON
620
			WHERE TaxonFk1 = cdm_t1.TaxonId AND TaxonFk2 = cdm_t2.TaxonId AND RelQualifierCache = bm_rt.RelQualifierCache)
621
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
622
		IF @n_bm = 0 BEGIN
623
			PRINT ('All relationships in EM2PESI exist also in CDM_EM2PESI')
624
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing relationships for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
625
	/*
626
		SELECT bm_t1.Fullname, bm_rt.RelQualifierCache, bm_t2.Fullname, 'in EM2PESI but not in CDM_EM2PESI'
627
		FROM [EM2PESI].[DBO].RELTAXON bm_rt INNER JOIN
628
		[EM2PESI].[DBO].TAXON bm_t1 ON bm_rt.TaxonFk1 = bm_t1.TaxonId INNER JOIN
629
		[EM2PESI].[DBO].TAXON bm_t2 ON bm_rt.TaxonFk2 = bm_t2.TaxonId INNER JOIN
630
		[CDM_EM2PESI].[DBO].TAXON cdm_t1
631
		ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
632
		[CDM_EM2PESI].[DBO].TAXON cdm_t2
633
		ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
634
		WHERE NOT EXISTS
635
		(SELECT * FROM [CDM_EM2PESI].[DBO].RELTAXON
636
		WHERE TaxonFk1 = cdm_t1.TaxonId AND TaxonFk2 = cdm_t2.TaxonId AND RelQualifierCache = bm_rt.RelQualifierCache)
637

  
638
		SELECT [RelTaxonQualifierFk]
639
		,[RelQualifierCache]
640
		, COUNT(*) as n
641
		FROM [CDM_EM2PESI].[dbo].[RelTaxon] rel INNER JOIN [CDM_EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1
642
		INNER JOIN [CDM_EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
643
		GROUP BY RelTaxonQualifierFk, RelQualifierCache
644
		ORDER BY RelTaxonQualifierFk, COUNT(*) DESC
645

  
646
		SELECT [RelTaxonQualifierFk]
647
		,[RelQualifierCache]
648
		, COUNT(*) as n
649
		FROM [EM2PESI].[dbo].[RelTaxon]
650
		GROUP BY RelTaxonQualifierFk, RelQualifierCache
651
		ORDER BY RelTaxonQualifierFk, COUNT(*) DESC
652
		
653
		SELECT t1.QualifierId, t1.Qualifier, t1.n as n_cdm, t2.n as n_bm,  t1.n - t2.n as diff
654
		FROM (
655

  
656
			SELECT rtq.QualifierId , rtq.Qualifier,  COUNT(rel.RelTaxonId) as n
657
			FROM RelTaxonQualifier rtq LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[RelTaxon] rel ON rel.RelTaxonQualifierFk = rtq.QualifierId 
658
				LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1 
659
				LEFT OUTER JOIN [CDM_EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
660
			GROUP BY QualifierId, Qualifier
661
		) t1 INNER JOIN  (
662
			SELECT rtq2.QualifierId , rtq2.Qualifier, COUNT(rel.RelTaxonId) as n
663
			FROM RelTaxonQualifier rtq2 LEFT OUTER JOIN [EM2PESI].[dbo].[RelTaxon] rel ON rel.RelTaxonQualifierFk = rtq2.QualifierId 
664
				LEFT OUTER JOIN [EM2PESI].[dbo].[Taxon] t1 ON t1.TaxonId = rel.TaxonFk1 
665
				LEFT OUTER JOIN [EM2PESI].[dbo].[Taxon] t2 ON t2.TaxonId = rel.TaxonFk2
666
				GROUP BY QualifierId, Qualifier
667
		) t2  ON t1.QualifierId  = t2.QualifierId AND t1.n <> t2.n
668
		ORDER BY t1.QualifierId DESC
669

  
670
	*/
671
		SELECT @n_cdm = COUNT(*) -- relationships only in CDM_EM2PESI
672
			FROM [CDM_EM2PESI].[DBO].RELTAXON cdm_rt INNER JOIN
673
			[CDM_EM2PESI].[DBO].TAXON cdm_t1 ON cdm_rt.TaxonFk1 = cdm_t1.TaxonId INNER JOIN
674
			[CDM_EM2PESI].[DBO].TAXON cdm_t2 ON cdm_rt.TaxonFk2 = cdm_t2.TaxonId INNER JOIN
675
			[EM2PESI].[DBO].TAXON bm_t1
676
			ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
677
			[EM2PESI].[DBO].TAXON bm_t2
678
			ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
679
			WHERE NOT EXISTS
680
			(SELECT * FROM [EM2PESI].[DBO].RELTAXON
681
			WHERE TaxonFk1 = bm_t1.TaxonId AND TaxonFk2 = bm_t2.TaxonId AND RelQualifierCache = cdm_rt.RelQualifierCache)
682
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
683
		IF @n_cdm = 0 BEGIN
684
			PRINT ('All relationships in CDM_EM2PESI exist also in EM2PESI')
685
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing relationships for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
686
	/*
687
		SELECT cdm_t1.Fullname, cdm_rt.RelQualifierCache, cdm_t2.Fullname, 'in CDM_EM2PESI but not in CDM_EM2PESI'
688
		FROM [CDM_EM2PESI].[DBO].RELTAXON cdm_rt INNER JOIN
689
		[CDM_EM2PESI].[DBO].TAXON cdm_t1 ON cdm_rt.TaxonFk1 = cdm_t1.TaxonId INNER JOIN
690
		[CDM_EM2PESI].[DBO].TAXON cdm_t2 ON cdm_rt.TaxonFk2 = cdm_t2.TaxonId INNER JOIN
691
		[EM2PESI].[DBO].TAXON bm_t1
692
		ON bm_t1.IdInSource = cdm_t1.IdInSource AND ISNULL(bm_t1.GUID, '') = ISNULL(cdm_t1.GUID, '') INNER JOIN
693
		[EM2PESI].[DBO].TAXON bm_t2
694
		ON bm_t2.IdInSource = cdm_t2.IdInSource AND ISNULL(bm_t2.GUID, '') = ISNULL(cdm_t2.GUID, '')
695
		WHERE NOT EXISTS
696
		(SELECT * FROM [EM2PESI].[DBO].RELTAXON
697
		WHERE TaxonFk1 = bm_t1.TaxonId AND TaxonFk2 = bm_t2.TaxonId AND RelQualifierCache = cdm_rt.RelQualifierCache)
698
	*/
699
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
700
			PRINT ('All relationships are identical in both databases')
701
		END
702

  
703

  
704
------------------------------------------- CommonName -------------------------------
705
	PRINT ' '
706
	PRINT 'COMMON NAMES'
707

  
708
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].CommonName
709
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].CommonName
710
	SET @n = @n_bm - @n_cdm
711

  
712
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
713
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
714
	SET @str_n = Cast(@n AS NVARCHAR)
715

  
716
	IF @n = 0 BEGIN
717
		PRINT ('Both databases have the same number of common names = ' + @str_n_bm)
718
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of common names, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
719

  
720
		SELECT @n_bm = COUNT(*) -- common names only in EM2PESI
721
			FROM [EM2PESI].[DBO].CommonName bm_cn INNER JOIN
722
			[EM2PESI].[DBO].TAXON bm_t ON bm_cn.TaxonFk = bm_t.TaxonId INNER JOIN
723
			[CDM_EM2PESI].[DBO].TAXON cdm_t
724
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
725
			WHERE NOT EXISTS
726
			(SELECT * FROM [CDM_EM2PESI].[DBO].CommonName
727
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(bm_cn.CommonName,'')
728
			AND ISNULL(LanguageCache,'') = ISNULL(bm_cn.LanguageCache,'')
729
			AND ISNULL(SourceNameCache,'') = ISNULL(bm_cn.SourceNameCache,'')
730
			AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_cn.SpeciesExpertName,'')
731
			AND ISNULL(LastAction,'') = ISNULL(bm_cn.LastAction,'')
732
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_cn.LastActionDate,'00:00:00')
733
			)
734
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
735
		IF @n_bm = 0 BEGIN
736
			PRINT ('All common names in EM2PESI exist also in CDM_EM2PESI')
737
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing common names for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
738
	/*
739
		SELECT bm_t.Fullname, bm_cn.CommonName, bm_cn.LanguageCache,
740
		bm_cn.SourceNameCache, bm_cn.SpeciesExpertName, bm_cn.LastAction,
741
		bm_cn.LastActionDate, 'in EM2PESI but not in CDM_EM2PESI'
742
		FROM [EM2PESI].[DBO].CommonName bm_cn INNER JOIN
743
		[EM2PESI].[DBO].TAXON bm_t ON bm_cn.TaxonFk = bm_t.TaxonId INNER JOIN
744
		[CDM_EM2PESI].[DBO].TAXON cdm_t
745
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
746
		WHERE NOT EXISTS
747
		(SELECT * FROM [CDM_EM2PESI].[DBO].CommonName
748
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(bm_cn.CommonName,'')
749
		AND ISNULL(LanguageCache,'') = ISNULL(bm_cn.LanguageCache,'')
750
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_cn.SourceNameCache,'')
751
		AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_cn.SpeciesExpertName,'')
752
		AND ISNULL(LastAction,'') = ISNULL(bm_cn.LastAction,'')
753
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_cn.LastActionDate,'00:00:00')
754
		)
755
	*/
756
		SELECT @n_cdm = COUNT(*) -- common names only in CDM_EM2PESI
757
			FROM [CDM_EM2PESI].[DBO].CommonName cdm_cn INNER JOIN
758
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_cn.TaxonFk = cdm_t.TaxonId INNER JOIN
759
			[EM2PESI].[DBO].TAXON bm_t
760
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
761
			WHERE NOT EXISTS
762
			(SELECT * FROM [EM2PESI].[DBO].CommonName
763
			WHERE TaxonFk = bm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(cdm_cn.CommonName,'')
764
			AND ISNULL(LanguageCache,'') = ISNULL(cdm_cn.LanguageCache,'')
765
			AND ISNULL(SourceNameCache,'') = ISNULL(cdm_cn.SourceNameCache,'')
766
			AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_cn.SpeciesExpertName,'')
767
			AND ISNULL(LastAction,'') = ISNULL(cdm_cn.LastAction,'')
768
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_cn.LastActionDate,'00:00:00')
769
			)
770
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
771
		IF @n_cdm = 0 BEGIN
772
			PRINT ('All common names in CDM_EM2PESI exist also in EM2PESI')
773
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing common names for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
774
	/*
775
		SELECT bm_t.Fullname, cdm_cn.CommonName, cdm_cn.LanguageCache,
776
		cdm_cn.SourceNameCache, cdm_cn.SpeciesExpertName, cdm_cn.LastAction,
777
		cdm_cn.LastActionDate, 'in CDM_EM2PESI but not in EM2PESI'
778
		FROM [CDM_EM2PESI].[DBO].CommonName cdm_cn INNER JOIN
779
		[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_cn.TaxonFk = cdm_t.TaxonId INNER JOIN
780
		[EM2PESI].[DBO].TAXON bm_t
781
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
782
		WHERE NOT EXISTS
783
		(SELECT * FROM [EM2PESI].[DBO].CommonName
784
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(CommonName,'') = ISNULL(cdm_cn.CommonName,'')
785
		AND ISNULL(LanguageCache,'') = ISNULL(cdm_cn.LanguageCache,'')
786
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_cn.SourceNameCache,'')
787
		AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_cn.SpeciesExpertName,'')
788
		AND ISNULL(LastAction,'') = ISNULL(cdm_cn.LastAction,'')
789
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_cn.LastActionDate,'00:00:00')
790
		)
791
	*/
792
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
793
			PRINT ('All common names are identical in both databases')
794
		END
795

  
796
------------------------------------------- AdditionalTaxonSource -------------------------------
797
	PRINT ' '
798
	PRINT 'AdditionalTaxonSource'
799
	
800
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].AdditionalTaxonSource
801
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
802
	SET @n = @n_bm - @n_cdm
803

  
804
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
805
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
806
	SET @str_n = Cast(@n AS NVARCHAR)
807

  
808
	IF @n = 0 BEGIN
809
		PRINT ('Both databases have the same number of additional sources = ' + @str_n_bm)
810
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of additional sources, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
811

  
812
		SELECT @n_bm = COUNT(*) -- additional sources only in EM2PESI
813
			FROM [EM2PESI].[DBO].AdditionalTaxonSource bm_ats INNER JOIN
814
			[EM2PESI].[DBO].TAXON bm_t ON bm_ats.TaxonFk = bm_t.TaxonId INNER JOIN
815
			[CDM_EM2PESI].[DBO].TAXON cdm_t
816
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
817
			WHERE NOT EXISTS
818
			(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
819
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
820
			AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
821
			AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
822
			)
823
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
824
		IF @n_bm = 0 BEGIN
825
			PRINT ('All additional sources in EM2PESI exist also in CDM_EM2PESI')
826
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing additional sources for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
827
	/*
828
		SELECT bm_t.Fullname, bm_ats.SourceUseCache, bm_ats.SourceNameCache,
829
		bm_ats.SourceDetail, 'in EM2PESI but not in CDM_EM2PESI'
830
		FROM [EM2PESI].[DBO].AdditionalTaxonSource bm_ats INNER JOIN
831
		[EM2PESI].[DBO].TAXON bm_t ON bm_ats.TaxonFk = bm_t.TaxonId INNER JOIN
832
		[CDM_EM2PESI].[DBO].TAXON cdm_t
833
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
834
		WHERE NOT EXISTS
835
		(SELECT * FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
836
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(bm_ats.SourceUseCache,'')
837
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_ats.SourceNameCache,'')
838
		AND ISNULL(SourceDetail,'') = ISNULL(bm_ats.SourceDetail,'')
839
		)
840
	*/
841
		SELECT @n_cdm = COUNT(*) -- additional sources only in CDM_EM2PESI
842
			FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN
843
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_ats.TaxonFk = cdm_t.TaxonId INNER JOIN
844
			[EM2PESI].[DBO].TAXON bm_t
845
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
846
			WHERE NOT EXISTS
847
			(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
848
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
849
			AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
850
			AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
851
			)
852
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
853
		IF @n_cdm = 0 BEGIN
854
			PRINT ('All additional sources in CDM_EM2PESI exist also in EM2PESI')
855
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing additional sources for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
856
	/*
857
		SELECT bm_t.Fullname, cdm_ats.SourceUseCache, cdm_ats.SourceNameCache,
858
		cdm_ats.SourceDetail, 'in EM2PESI but not in CDM_EM2PESI'
859
		FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource cdm_ats INNER JOIN
860
		[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_ats.TaxonFk = cdm_t.TaxonId INNER JOIN
861
		[EM2PESI].[DBO].TAXON bm_t
862
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
863
		WHERE NOT EXISTS
864
		(SELECT * FROM [EM2PESI].[DBO].AdditionalTaxonSource
865
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(SourceUseCache,'') = ISNULL(cdm_ats.SourceUseCache,'')
866
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ats.SourceNameCache,'')
867
		AND ISNULL(SourceDetail,'') = ISNULL(cdm_ats.SourceDetail,'')
868
		)
869
	*/
870
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
871
			PRINT ('All additional sources are identical in both databases')
872
		END
873

  
874
------------------------------ Image (currently exist only in ERMS) -------------------------------
875
	PRINT ' '
876
	PRINT 'IMAGES'
877
	
878
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Image
879
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Image
880
	SET @n = @n_bm - @n_cdm
881

  
882
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
883
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
884
	SET @str_n = Cast(@n AS NVARCHAR)
885

  
886
	IF @n = 0 BEGIN
887
		PRINT ('Both databases have the same number of images = ' + @str_n_bm)
888
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of images, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
889

  
890
		SELECT @n_bm = COUNT(*) -- images only in EM2PESI
891
			FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
892
			[EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
893
			[CDM_EM2PESI].[DBO].TAXON cdm_t
894
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
895
			WHERE NOT EXISTS
896
			(SELECT * FROM [CDM_EM2PESI].[DBO].Image
897
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
898
			AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
899
			)
900
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
901
		IF @n_bm = 0 BEGIN
902
			PRINT ('All images in EM2PESI exist also in CDM_EM2PESI')
903
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing images for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
904
	/*
905
		SELECT bm_t.Fullname, bm_i.img_thumb, bm_i.img_url, 'in EM2PESI but not in CDM_EM2PESI'
906
		FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
907
		[EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
908
		[CDM_EM2PESI].[DBO].TAXON cdm_t
909
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
910
		WHERE NOT EXISTS
911
		(SELECT * FROM [CDM_EM2PESI].[DBO].Image
912
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
913
		AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
914
		)
915
	*/
916
		SELECT @n_cdm = COUNT(*) -- images only in CDM_EM2PESI
917
			FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
918
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
919
			[EM2PESI].[DBO].TAXON bm_t
920
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
921
			WHERE NOT EXISTS
922
			(SELECT * FROM [EM2PESI].[DBO].Image
923
			WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
924
			AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
925
			)
926
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
927
		IF @n_cdm = 0 BEGIN
928
			PRINT ('All images in CDM_EM2PESI exist also in EM2PESI')
929
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing images for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
930
	/*
931
		SELECT bm_t.Fullname, cdm_i.img_thumb, cdm_i.img_url, 'in CDM_EM2PESI but not in EM2PESI'
932
		FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
933
		[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
934
		[EM2PESI].[DBO].TAXON bm_t
935
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
936
		WHERE NOT EXISTS
937
		(SELECT * FROM [EM2PESI].[DBO].Image
938
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
939
		AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
940
		)
941
	*/
942
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
943
			PRINT ('All images are identical in both databases')
944
		END
945

  
946
------------------------------------------- Note -------------------------------
947
	PRINT ' '
948
	PRINT 'NOTES'
949

  
950
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN
951
		[EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
952
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN
953
		[CDM_EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
954
	SET @n = @n_bm - @n_cdm
955

  
956
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
957
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
958
	SET @str_n = Cast(@n AS NVARCHAR)
959

  
960
	IF @n = 0 BEGIN
961
		PRINT ('Both databases have the same number of note * notesource results = ' + @str_n_bm)
962
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of note * notesource results, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
963

  
964
		SELECT @n_bm = COUNT(*) -- note * notesource results only in EM2PESI
965
			FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
966
			[EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
967
			[EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
968
			[CDM_EM2PESI].[DBO].TAXON cdm_t
969
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
970
			WHERE NOT EXISTS
971
			(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
972
			ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
973
			WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
974
			AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
975
			AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
976
			AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
977
			AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
978
			AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
979
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
980
			AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
981
			AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
982
			)
983
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
984
		IF @n_bm = 0 BEGIN
985
			PRINT ('All note * notesource results in EM2PESI exist also in CDM_EM2PESI')
986
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing note * notesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
987
	/*
988
		SELECT bm_t.Fullname, bm_n.Note_1, bm_n.Note_2, bm_n.NoteCategoryCache, bm_n.LanguageCache,
989
		bm_n.SpeciesExpertName, bm_n.LastAction, bm_n.LastActionDate, bm_ns.SourceNameCache, bm_ns.SourceDetail,
990
		'in EM2PESI but not in CDM_EM2PESI'
991
		FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
992
		[EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
993
		[EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
994
		[CDM_EM2PESI].[DBO].TAXON cdm_t
995
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
996
		WHERE NOT EXISTS
997
		(SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
998
		ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
999
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
1000
		AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
1001
		AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
1002
		AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
1003
		AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
1004
		AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
1005
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_n.LastActionDate,'00:00:00')
1006
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
1007
		AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
1008
		)
1009
	*/
1010
		SELECT @n_cdm = COUNT(*) -- note * notesource results only in CDM_EM2PESI
1011
			FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
1012
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1013
			[CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
1014
			[EM2PESI].[DBO].TAXON bm_t
1015
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1016
			WHERE NOT EXISTS
1017
			(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1018
			ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1019
			WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1020
			AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1021
			AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1022
			AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1023
			AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1024
			AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1025
			AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
1026
			AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1027
			AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1028
			)
1029
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1030
		IF @n_cdm = 0 BEGIN
1031
			PRINT ('All note * notesource results in CDM_EM2PESI exist also in EM2PESI')
1032
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing note * notesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
1033
	/*
1034
		SELECT bm_t.Fullname, cdm_n.Note_1, cdm_n.Note_2, cdm_n.NoteCategoryCache, cdm_n.LanguageCache,
1035
		cdm_n.SpeciesExpertName, cdm_n.LastAction, cdm_n.LastActionDate, cdm_ns.SourceNameCache, cdm_ns.SourceDetail,
1036
		'in CDM_EM2PESI but not in EM2PESI'
1037
		FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
1038
		[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1039
		[CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
1040
		[EM2PESI].[DBO].TAXON bm_t
1041
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1042
		WHERE NOT EXISTS
1043
		(SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1044
		ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1045
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1046
		AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1047
		AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1048
		AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1049
		AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1050
		AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1051
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_n.LastActionDate,'00:00:00')
1052
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1053
		AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1054
		)
1055
	*/
1056
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1057
			PRINT ('All note * notesource results are identical in both databases')
1058
		END
1059

  
1060
------------------------------------------- Occurrence -------------------------------
1061
	PRINT ' '
1062
	PRINT 'OCCURRENCES'
1063
	
1064
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
1065
		[EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
1066
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
1067
		[CDM_EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
1068
	SET @n = @n_bm - @n_cdm
1069

  
1070
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
1071
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1072
	SET @str_n = Cast(@n AS NVARCHAR)
1073

  
1074
	IF @n = 0 BEGIN
1075
		PRINT ('Both databases have the same number of occurrence * occurrencesource results = ' + @str_n_bm)
1076
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of occurrence * occurrencesource results, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
1077

  
1078
		SELECT @n_bm = COUNT(*) -- occurrence * occurrencesource results only in EM2PESI
1079
			FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
1080
			[EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
1081
			[EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
1082
			[CDM_EM2PESI].[DBO].TAXON cdm_t
1083
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1084
			WHERE NOT EXISTS
1085
			(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
1086
			ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1087
			WHERE TaxonFk = cdm_t.TaxonId 
1088
				AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1089
				AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
1090
				AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
1091
				AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
1092
				AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
1093
				AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
1094
				AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
1095
				AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
1096
				AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
1097
			)
1098
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
1099
		IF @n_bm = 0 BEGIN
1100
			PRINT ('All occurrence * occurrencesource results in EM2PESI exist also in CDM_EM2PESI')
1101
		END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing occurrence * occurrencesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
1102
	/*
1103
		SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, bm_t.Fullname, bm_o.TaxonFullNameCache, bm_o.AreaNameCache, bm_o.OccurrenceStatusCache, bm_o.Notes,
1104
		bm_o.SpeciesExpertName, bm_o.LastAction, bm_o.LastActionDate, bm_os.SourceNameCache, bm_os.OldTaxonName,
1105
		'in EM2PESI but not in CDM_EM2PESI'
1106
		FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
1107
		[EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
1108
		[EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
1109
		[CDM_EM2PESI].[DBO].TAXON cdm_t
1110
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1111
		WHERE NOT EXISTS
1112
		(SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
1113
		ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1114
		WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1115
		AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
1116
		AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
1117
		AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
1118
		AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
1119
		AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
1120
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
1121
		AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
1122
		AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
1123
		)
1124
	*/
1125
		SELECT @n_cdm = COUNT(*) -- occurrence * occurrencesource results only in CDM_EM2PESI
1126
			FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
1127
			[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1128
			[CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
1129
			[EM2PESI].[DBO].TAXON bm_t
1130
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1131
			WHERE NOT EXISTS
1132
			(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
1133
			ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1134
			WHERE TaxonFk = bm_t.TaxonId 
1135
				AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1136
				AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
1137
				AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
1138
				AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
1139
				AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
1140
				AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
1141
				AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
1142
				AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
1143
				AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
1144
			)
1145
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1146
		IF @n_cdm = 0 BEGIN
1147
			PRINT ('All occurrence * occurrencesource results in CDM_EM2PESI exist also in EM2PESI')
1148
		END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing occurrence * occurrencesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
1149
	/*
1150
		SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, bm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
1151
		cdm_o.SpeciesExpertName, cdm_o.LastAction, cdm_o.LastActionDate, cdm_os.SourceNameCache, cdm_os.OldTaxonName,
1152
		'in CDM_EM2PESI but not in EM2PESI'
1153
		FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
1154
		[CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1155
		[CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
1156
		[EM2PESI].[DBO].TAXON bm_t
1157
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1158
		WHERE NOT EXISTS
1159
		(SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
1160
		ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1161
		WHERE TaxonFk = bm_t.TaxonId AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1162
		AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
1163
		AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
1164
		AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
1165
		AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
1166
		AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
1167
		AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
1168
		AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
1169
		AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
1170
		)
1171
	*/
1172
		IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1173
			PRINT ('All occurrence * occurrencesource results are identical in both databases')
1174
		END
1175

  
1176

  
1177
	PRINT ('End of check')
1178

  
1179

  
1180
END
cdm-pesi/src/main/resources/sql/Check_Import_Master.sql
1
BEGIN
2

  
3
	DECLARE @n INT
4
	DECLARE @n_cdm INT
5
	DECLARE @n_bm INT
6
	DECLARE @str_n NVARCHAR (7)
7
	DECLARE @str_n_cdm NVARCHAR (7)
8
	DECLARE @str_n_bm NVARCHAR (7)
9

  
10

  
11
------------------------------------------- Taxon -------------------------------
12

  
13
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON
14
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON
15
	SET @n = @n_bm - @n_cdm
16

  
17
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
18
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
19
	SET @str_n = Cast(@n AS NVARCHAR)
20

  
21
	IF @n = 0 BEGIN
22
		PRINT ('Both databases have the same number of taxa = ' + @str_n_bm)
23
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
24
	SELECT @n = COUNT(*)
25
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
26
		[CDM_EM2PESI].[DBO].TAXON cdm_t
27
		ON (bm_t.IdInSource = cdm_t.IdInSource OR bm_t.IdInSource IS NULL AND cdm_t.IdInSource IS NULL) 
28
		    AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
29
	SET @str_n = Cast(@n AS NVARCHAR)
30
	IF @n = @n_bm BEGIN
31
		PRINT ('To each taxon from one database there is an identical taxon in the other one (IdInSource, GUID)')
32
	END ELSE PRINT ('WARNING: Only ' + @str_n + ' taxa are identical (IdInSource, GUID) in both databases')
33

  
34
		/*
35
	SELECT cdm_t.Fullname, cdm_t.IdInSource, cdm_t.GUID, 'in CDM_EM2PESI but not in EM2PESI'
36
	FROM [CDM_EM2PESI].[DBO].TAXON cdm_t
37
	WHERE NOT EXISTS
38
		(SELECT * FROM [EM2PESI].[DBO].TAXON WHERE (IdInSource = cdm_t.IdInSource OR IdInSource IS NULL AND cdm_t.IdInSource IS NULL)
39
		AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
40
	ORDER BY cdm_t.IdInSource
41

  
42
	SELECT bm_t.Fullname, bm_t.IdInSource, bm_t.GUID, 'in EM2PESI but not in CDM_EM2PESI'
43
	FROM [EM2PESI].[DBO].TAXON bm_t
44
	WHERE NOT EXISTS
45
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON WHERE (IdInSource = bm_t.IdInSource OR IdInSource IS NULL AND bm_t.IdInSource IS NULL)
46
		AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
47
	ORDER BY bm_t.IdInSource
48

  
49
	*/
50

  
51
-- Source
52
/* Does not make much sense since the source for taxa is almost always the database itself which must be created as source
53
	SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
54
	SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE SourceFk IS NOT NULL
55
	SET @n = @n_bm - @n_cdm
56

  
57
	SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
58
	SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
59
	SET @str_n = Cast(@n AS NVARCHAR)
60

  
61
	IF @n = 0 BEGIN
62
		PRINT ('Both databases have the same number of taxa which have a source = ' + @str_n_bm)
63
	END ELSE PRINT ('WARNING: Both databases DO NOT have the same number of taxa that have a source, n_bm = ' + @str_n_bm + ' and n_cdm = '+ @str_n_cdm)
64

  
65
		SELECT @n = COUNT(*) -- in both databases sources exist but are different
66
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
67
			[CDM_EM2PESI].[DBO].TAXON cdm_t
68
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
69
			[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
70
			[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
71
			WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
72
		SET @str_n = Cast(@n AS NVARCHAR)
73
		IF @n = 0 BEGIN
74
			PRINT ('All identical taxa that have sources have the same source')
75
		END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different sources')
76
	/-*
77
		SELECT cdm_t.Fullname, cdm_s.RefIdInSource as Source_CDM_EM2PESI, bm_s.RefIdInSource as Source_EM2PESI
78
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
79
		[CDM_EM2PESI].[DBO].TAXON cdm_t
80
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
81
		[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId INNER JOIN
82
		[CDM_EM2PESI].[DBO].TAXON cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
83
		WHERE bm_s.RefIdInSource <> cdm_s.RefIdInSource OR ISNULL(bm_s.OriginalDB, '') <> ISNULL(cdm_s.OriginalDB, '')
84
	*-/
85
		SELECT @n_bm = COUNT(*) -- taxa with source only in EM2PESI
86
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
87
			[CDM_EM2PESI].[DBO].TAXON cdm_t
88
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
89
			[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
90
			WHERE NOT EXISTS
91
				(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
92
				[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
93
				WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
94
				AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
95
		SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
96
		IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' taxa have sources in EM2PESI but not in CDM_EM2PESI')
97
	/-*
98
		SELECT cdm_t.Fullname, bm_s.Fullname as source_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
99
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
100
		[CDM_EM2PESI].[DBO].TAXON cdm_t
101
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
102
		[EM2PESI].[DBO].SOURCE bm_s ON bm_t.SourceFk = bm_s.SourceId
103
		WHERE NOT EXISTS
104
		(SELECT * FROM [CDM_EM2PESI].[DBO].TAXON INNER JOIN
105
		[CDM_EM2PESI].[DBO].TAXON cdm_s ON SourceFk = SourceId
106
		WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
107
		AND IdInSource = bm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_t.GUID, ''))
108
	*-/
109
		SELECT @n_cdm = COUNT(*) -- taxa with source only in CDM_EM2PESI
110
			FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
111
			[CDM_EM2PESI].[DBO].TAXON cdm_t
112
			ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
113
			[CDM_EM2PESI].[DBO].SOURCE cdm_s ON cdm_t.SourceFk = cdm_s.SourceId
114
			WHERE NOT EXISTS
115
				(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
116
				[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
117
				WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
118
				AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
119
		SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
120
		IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' taxa have sources in CDM_EM2PESI but not in EM2PESI')
121
	/-*
122
		SELECT cdm_t.Fullname, cdm_s.Fullname as source_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
123
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
124
		[CDM_EM2PESI].[DBO].TAXON cdm_t
125
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
126
		[CDM_EM2PESI].[DBO].SOURCE cdm_s ON bm_t.SourceFk = cdm_s.SourceId
127
		(SELECT * FROM [EM2PESI].[DBO].TAXON INNER JOIN
128
		[EM2PESI].[DBO].TAXON bm_s ON SourceFk = SourceId
129
		WHERE cdm_s.RefIdInSource = bm_s.RefIdInSource AND cdm_s.OriginalDB = bm_s.OriginalDB
130
		AND IdInSource = cdm_t.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_t.GUID, ''))
131
	*-/
132
		IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
133
			PRINT ('All identical taxa have the same source')
134
		END
135

  
136
*/
137

  
138
-- Authors
139
	SELECT @n = COUNT(*)
140
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
141
		CDM_EM2PESI.[DBO].TAXON cdm_t
142
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
143
		WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
144
	SET @str_n = Cast(@n AS NVARCHAR)
145
	IF @n = 0 BEGIN
146
		PRINT ('All identical taxa have the same authors')
147
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same authors')
148

  
149
	/*
150
		SELECT cdm_t.TaxonId as ID_CDM, bm_t.TaxonId as ID_SQL, cdm_t.FullName as FullName_CDM, bm_t.FullName as FullName_SQL,
151
			cdm_t.AuthorString as AuthorString_CDM, bm_t.AuthorString as AuthorString_SQL
152
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
153
		CDM_EM2PESI.[DBO].TAXON cdm_t
154
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
155
		WHERE ISNULL(bm_t.AuthorString, '') <> ISNULL(cdm_t.AuthorString, '')
156
	*/
157

  
158
-- Epithets
159
	SELECT @n = COUNT(*)
160
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
161
		[CDM_EM2PESI].[DBO].TAXON cdm_t
162
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
163
		WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
164
		ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
165
		ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
166
		ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
167
	SET @str_n = Cast(@n AS NVARCHAR)
168
	IF @n = 0 BEGIN
169
		PRINT ('All identical taxa have the same epithets')
170
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same epithets')
171

  
172
	/*
173
	SELECT  cdm_t.GenusOrUninomial as Genus_CDM, bm_t.GenusOrUninomial as Genus_SQL,
174
			cdm_t.InfraGenericEpithet as Infragenus_CDM, bm_t.InfraGenericEpithet as Infragenus_SQL,
175
			cdm_t.SpecificEpithet  as SpecificEpithet_CDM, bm_t.SpecificEpithet as SpecificEpithet_SQL,
176
			cdm_t.InfraSpecificEpithet as Infraspec_CDM, bm_t.InfraSpecificEpithet as Infraspec_SQL
177
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
178
		CDM_EM2PESI.[DBO].TAXON cdm_t
179
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
180
		WHERE ISNULL(bm_t.GenusOrUninomial, '') <> ISNULL(cdm_t.GenusOrUninomial, '') OR
181
		ISNULL(bm_t.InfraGenericEpithet, '') <> ISNULL(cdm_t.InfraGenericEpithet, '') OR
182
		ISNULL(bm_t.SpecificEpithet, '') <> ISNULL(cdm_t.SpecificEpithet, '') OR
183
		ISNULL(bm_t.InfraSpecificEpithet, '') <> ISNULL(cdm_t.InfraSpecificEpithet, '')
184
	*/
185

  
186
-- Names
187
	SELECT @n = COUNT(*)
188
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
189
		[CDM_EM2PESI].[DBO].TAXON cdm_t
190
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
191
		WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
192
		ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
193
		ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
194
	SET @str_n = Cast(@n AS NVARCHAR)
195
	IF @n = 0 BEGIN
196
		PRINT ('All identical taxa have the same scientific name')
197
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same scientific name')
198
	/*
199
	SELECT cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
200
		cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
201
		cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
202
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
203
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
204
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
205
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
206
	CDM_EM2PESI.[DBO].TAXON cdm_t
207
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
208
	WHERE ISNULL(bm_t.WebSearchName, '') <> ISNULL(cdm_t.WebSearchName, '') OR
209
		ISNULL(bm_t.WebShowName, '') <> ISNULL(cdm_t.WebShowName, '') OR
210
		ISNULL(bm_t.DisplayName, '') <> ISNULL(cdm_t.DisplayName, '')
211
	*/
212

  
213
-- Full Name
214
	SELECT @n = COUNT(*)
215
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
216
		CDM_EM2PESI.[DBO].TAXON cdm_t
217
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
218
		WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
219
	SET @str_n = Cast(@n AS NVARCHAR)
220
	IF @n = 0 BEGIN
221
		PRINT ('All identical taxa have the same full name')
222
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same full name')
223

  
224
/*
225
	SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
226
		cdm_t.WebSearchName as WebSearchName_CDM_EM2PESI, bm_t.WebSearchName as WebSearchName_EM2PESI,
227
		cdm_t.WebShowName as WebShowName_CDM_EM2PESI, bm_t.WebShowName as WebShowName_EM2PESI,
228
		cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
229
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
230
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
231
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
232
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
233
		CDM_EM2PESI.[DBO].TAXON cdm_t
234
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
235
	WHERE ISNULL(bm_t.FullName, '') <> ISNULL(cdm_t.FullName, '')
236
	ORDER BY cdm_t.FullName
237
	*/
238

  
239
-- Nom Ref
240

  
241
	SELECT @n = COUNT(*)
242
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
243
		CDM_EM2PESI.[DBO].TAXON cdm_t
244
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
245
		WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
246
	SET @str_n = Cast(@n AS NVARCHAR)
247
	IF @n = 0 BEGIN
248
		PRINT ('All identical taxa have the same nomenclatural reference')
249
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same nomenclatural reference')
250
	/*
251
	SELECT cdm_t.FullName as FullName_CDM_EM2PESI, bm_t.FullName as FullName_EM2PESI,
252
		cdm_t.NomRefString as NomRefString_CDM_EM2PESI, bm_t.NomRefString as NomRefString_EM2PESI,
253
		cdm_t.DisplayName as DisplayName_CDM_EM2PESI, bm_t.DisplayName as DisplayName_EM2PESI,
254
		cdm_t.GUID as GUID_CDM, bm_t.GUID as GUID_SQL
255
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
256
		CDM_EM2PESI.[DBO].TAXON cdm_t
257
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
258
	WHERE ISNULL(bm_t.NomRefString, '') <> ISNULL(cdm_t.NomRefString, '')
259
	ORDER BY cdm_t.NomRefString
260
	*/
261

  
262
-- Ranks
263
	SELECT @n = COUNT(*)
264
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
265
		[CDM_EM2PESI].[DBO].TAXON cdm_t
266
		ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
267
		WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
268
		ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
269
		ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
270
	SET @str_n = Cast(@n AS NVARCHAR)
271
	IF @n = 0 BEGIN
272
		PRINT ('All identical taxa have the same rank (KingdomFk, RankFk, RankCache)')
273
	END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same rank (KingdomFk, RankFk, RankCache)')
274
	/*
275
	SELECT cdm_t.Fullname, cdm_t.KingdomFk as Kingdom_CDM_EM2PESI, bm_t.KingdomFk as Kingdom_EM2PESI,
276
	cdm_t.RankFk as RankFk_CDM_EM2PESI, bm_t.RankFk as RankFk_EM2PESI,
277
	cdm_t.RankCache as Rank_CDM_EM2PESI, bm_t.RankCache as Rank_EM2PESI
278
	FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
279
	[CDM_EM2PESI].[DBO].TAXON cdm_t
280
	ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
281
	WHERE ISNULL(bm_t.KingdomFk, 0) <> ISNULL(cdm_t.KingdomFk, 0) OR
282
	ISNULL(bm_t.RankFk, 0) <> ISNULL(cdm_t.RankFk, 0) OR
283
	ISNULL(bm_t.RankCache, '') <> ISNULL(cdm_t.RankCache, '')
284
	*/
285

  
286
-- Status
287
	SELECT @n = COUNT(*)
288
		FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
... This diff was truncated because it exceeds the maximum size that can be displayed.

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)