Project

General

Profile

Statistics
| Branch: | Revision:

cdmlib-apps / cdm-pesi / src / test / resources / sql / Check_Import_Master.sql @ be2b30e1

History | View | Annotate | Download (63 KB)

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
-- Types
310
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TypeNameFk IS NOT NULL
311
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TypeNameFk 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 type = ' + @str_n_bm)
320
        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)
321

    
322
                SELECT @n = COUNT(*) -- in both databases types 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.TypeNameFk = bm_pt.TaxonId INNER JOIN
327
                        [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = 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 types have the same type')
332
                END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different types')
333
        /*
334
                SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, bm_pt.Fullname as Type_EM2PESI
335
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
336
                [CDM_EM2PESI].[DBO].TAXON cdm_t
337
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
338
                [EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId INNER JOIN
339
                [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
340
                WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
341
                ORDER BY cdm_t.Fullname
342
        */
343
                SELECT @n_bm = COUNT(*) -- taxa with type only in EM2PESI
344
                        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
345
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
346
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
347
                        [EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
348
                        WHERE NOT EXISTS
349
                                (SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
350
                                WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
351
                SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
352
                IF @n_bm > 0 PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have types in EM2PESI but not in CDM_EM2PESI')
353
        /*
354
                SELECT cdm_t.Fullname, bm_pt.Fullname as Type_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
355
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
356
                [CDM_EM2PESI].[DBO].TAXON cdm_t
357
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
358
                [EM2PESI].[DBO].TAXON bm_pt ON bm_t.TypeNameFk = bm_pt.TaxonId
359
                WHERE NOT EXISTS
360
                (SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
361
                WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
362
                ORDER BY cdm_t.Fullname
363
        */
364
                SELECT @n_cdm = COUNT(*) -- taxa with type only in CDM_EM2PESI
365
                        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
366
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
367
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
368
                        [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
369
                        WHERE NOT EXISTS
370
                                (SELECT * FROM [EM2PESI].[DBO].TAXON
371
                                WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
372
                SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
373
                IF @n_cdm > 0 PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have types in CDM_EM2PESI but not in EM2PESI')
374
        /*
375
                SELECT cdm_t.Fullname, cdm_pt.Fullname as Type_CDM_EM2PESI, 'in CDM_EM2PESI but not in EM2PESI'
376
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
377
                [CDM_EM2PESI].[DBO].TAXON cdm_t
378
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
379
                [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.TypeNameFk = cdm_pt.TaxonId
380
                WHERE NOT EXISTS
381
                (SELECT * FROM [EM2PESI].[DBO].TAXON
382
                WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
383
        */
384
                IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
385
                        PRINT ('All identical taxa have the same type')
386
                END
387

    
388
-- QualityStatus
389
        SELECT @n = COUNT(*)
390
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
391
                [CDM_EM2PESI].[DBO].TAXON cdm_t
392
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
393
                WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
394
        SET @str_n = Cast(@n AS NVARCHAR)
395
        IF @n = 0 BEGIN
396
                PRINT ('All identical taxa have the same quality status (QualityStatusCache)')
397
        END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same quality status (QualityStatusCache)')
398
        /*
399
        SELECT cdm_t.Fullname, cdm_t.QualityStatusCache as QualityStatus_CDM_EM2PESI, bm_t.QualityStatusCache as QualityStatus_EM2PESI
400
        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
401
        [CDM_EM2PESI].[DBO].TAXON cdm_t
402
        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
403
        WHERE ISNULL(bm_t.QualityStatusCache, '') <> ISNULL(cdm_t.QualityStatusCache, '')
404
        */
405

    
406
-- Experts
407
        SELECT @n = COUNT(*)
408
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
409
                [CDM_EM2PESI].[DBO].TAXON cdm_t
410
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
411
                WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
412
                ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
413
        SET @str_n = Cast(@n AS NVARCHAR)
414
        IF @n = 0 BEGIN
415
                PRINT ('All identical taxa have the same experts (ExpertName, SpeciesExpertName)')
416
        END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same experts (ExpertName, SpeciesExpertName)')
417
        /*
418
        SELECT cdm_t.Fullname, cdm_t.ExpertName as ExpertName_CDM_EM2PESI, bm_t.ExpertName as ExpertName_EM2PESI,
419
        cdm_t.SpeciesExpertName as SpeciesExpertName_CDM_EM2PESI, bm_t.SpeciesExpertName as SpeciesExpertName_EM2PESI
420
        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
421
        [CDM_EM2PESI].[DBO].TAXON cdm_t
422
        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
423
        WHERE ISNULL(bm_t.ExpertName, '') <> ISNULL(cdm_t.ExpertName, '') OR
424
        ISNULL(bm_t.SpeciesExpertName, '') <> ISNULL(cdm_t.SpeciesExpertName, '')
425
        */
426

    
427
-- Citation
428
        SELECT @n = COUNT(*)
429
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
430
                [CDM_EM2PESI].[DBO].TAXON cdm_t
431
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
432
                WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
433
        SET @str_n = Cast(@n AS NVARCHAR)
434
        IF @n = 0 BEGIN
435
                PRINT ('All identical taxa have the same CacheCitation')
436
        END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same CacheCitation')
437
        /*
438
        SELECT cdm_t.Fullname, cdm_t.CacheCitation as CacheCitation_CDM_EM2PESI, bm_t.CacheCitation as CacheCitation_EM2PESI
439
        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
440
        [CDM_EM2PESI].[DBO].TAXON cdm_t
441
        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
442
        WHERE ISNULL(bm_t.CacheCitation, '') <> ISNULL(cdm_t.CacheCitation, '')
443
        */
444

    
445
-- Original database
446
        SELECT @n = COUNT(*)
447
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
448
                [CDM_EM2PESI].[DBO].TAXON cdm_t
449
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
450
                WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
451
        SET @str_n = Cast(@n AS NVARCHAR)
452
        IF @n = 0 BEGIN
453
                PRINT ('All identical taxa have the same OriginalDB')
454
        END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the same OriginalDB')
455
        /*
456
        SELECT cdm_t.Fullname, cdm_t.OriginalDB as OriginalDB_CDM_EM2PESI, bm_t.OriginalDB as OriginalDB_EM2PESI
457
        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
458
        [CDM_EM2PESI].[DBO].TAXON cdm_t
459
        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
460
        WHERE ISNULL(bm_t.OriginalDB, '') <> ISNULL(cdm_t.OriginalDB, '')
461
        */
462

    
463
-- Action
464
        SELECT @n = COUNT(*)
465
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
466
                [CDM_EM2PESI].[DBO].TAXON cdm_t
467
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
468
                WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
469
                convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00'))  <>
470
                convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
471
        SET @str_n = Cast(@n AS NVARCHAR)
472
        IF @n = 0 BEGIN
473
                PRINT ('All identical taxa have the same last actions (LastAction, LastActionDate)')
474
        END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa DO NOT have the last actions (LastAction, LastActionDate)')
475
        /*
476
        SELECT cdm_t.Fullname, cdm_t.LastAction as LastAction_CDM_EM2PESI, bm_t.LastAction as LastAction_EM2PESI,
477
        cdm_t.LastActionDate as LastActionDate_CDM_EM2PESI, bm_t.LastActionDate as LastActionDate_EM2PESI
478
        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
479
        [CDM_EM2PESI].[DBO].TAXON cdm_t
480
        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
481
        WHERE ISNULL(bm_t.LastAction, '') <> ISNULL(cdm_t.LastAction, '') OR
482
        convert(smalldatetime, ISNULL(bm_t.LastActionDate, '00:00:00')) <>
483
        convert(smalldatetime, ISNULL(cdm_t.LastActionDate, '00:00:00'))
484
        */
485

    
486

    
487
        
488
-- Parents
489

    
490
        PRINT ' '
491
        PRINT 'PARENTS'
492
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
493
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE ParentTaxonFk IS NOT NULL
494
        SET @n = @n_bm - @n_cdm
495

    
496
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
497
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
498
        SET @str_n = Cast(@n AS NVARCHAR)
499

    
500
        IF @n = 0 BEGIN
501
                PRINT ('Both databases have the same number of taxa which have a parent = ' + @str_n_bm)
502
        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)
503

    
504
                SELECT @n = COUNT(*) -- in both databases parents exist but are different
505
                        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
506
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
507
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
508
                        [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
509
                        [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
510
                        WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
511
                SET @str_n = Cast(@n AS NVARCHAR)
512
                IF @n = 0 BEGIN
513
                        PRINT ('All identical taxa that have parents have the same parent')
514
                END ELSE PRINT ('WARNING: ' + @str_n + ' identical taxa have different parents')
515
        /*
516
                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
517
                FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
518
                        [CDM_EM2PESI].[DBO].TAXON cdm_t ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
519
                        [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId INNER JOIN
520
                [        CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
521
                WHERE bm_pt.IdInSource <> cdm_pt.IdInSource OR ISNULL(bm_pt.GUID, '') <> ISNULL(cdm_pt.GUID, '')
522
        */
523
                SELECT @n_bm = COUNT(*) -- taxa with parent only in EM2PESI
524
                        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
525
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
526
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
527
                        [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
528
                        WHERE NOT EXISTS
529
                                (SELECT * FROM [CDM_EM2PESI].[DBO].TAXON
530
                                WHERE IdInSource = bm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(bm_pt.GUID, ''))
531
                SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
532
                IF @n_bm > 0 BEGIN 
533
                        PRINT ('WARNING: ' + @str_n_bm + ' identical taxa have parents in EM2PESI but not in CDM_EM2PESI')
534
                END ELSE PRINT ('All identical taxa that have parents in EM2PESI do have parents in CDM_EM2PESI')
535

    
536
        /*
537
                SELECT cdm_t.Fullname ChildName, bm_pt.Fullname as Parent_EM2PESI, 'in EM2PESI but not in CDM_EM2PESI'
538
                FROM [EM2PESI].[DBO].TAXON bm_t 
539
                        INNER JOIN [EM2PESI].[DBO].TAXON bm_pt ON bm_t.ParentTaxonFk = bm_pt.TaxonId
540
                        INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_t ON ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') 
541
                
542
                WHERE cdm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
543
                (SELECT * FROM [CDM_EM2PESI].[DBO].TAXON cdm_pt
544
                WHERE (cdm_pt.IdInSource = bm_pt.IdInSource OR cdm_pt.IdInSource IS NULL AND bm_pt.IdInSource IS NULL) 
545
                                AND ISNULL(cdm_pt.GUID, '') = ISNULL(bm_pt.GUID, '')
546
                                AND cdm_t.ParentTaxonFk = cdm_pt.TaxonId)
547
                ORDER BY cdm_t.Fullname
548

549
        */
550
                SELECT @n_cdm = COUNT(*) -- taxa with parent only in CDM_EM2PESI
551
                        FROM [EM2PESI].[DBO].TAXON bm_t INNER JOIN
552
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
553
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '') INNER JOIN
554
                        [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
555
                        WHERE NOT EXISTS
556
                                (SELECT * FROM [EM2PESI].[DBO].TAXON
557
                                WHERE IdInSource = cdm_pt.IdInSource AND ISNULL(GUID, '') = ISNULL(cdm_pt.GUID, ''))
558
                SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
559
                IF @n_cdm > 0 BEGIN 
560
                        PRINT ('WARNING: ' + @str_n_cdm + ' identical taxa have parents in CDM_EM2PESI but not in EM2PESI')
561
                END ELSE PRINT('All identical taxa that have parents in CDM_EM2PESI do have parents in EM2PESI')
562
                
563
        /*
564
                SELECT bm_t.Fullname ChildName, cdm_pt.Fullname as Parent_CDM, 'in CDM_EM2PESI but not in EM2PESI'
565
                FROM [CDM_EM2PESI].[DBO].TAXON cdm_t 
566
                        INNER JOIN [CDM_EM2PESI].[DBO].TAXON cdm_pt ON cdm_t.ParentTaxonFk = cdm_pt.TaxonId
567
                        INNER JOIN [EM2PESI].[DBO].TAXON bm_t ON ISNULL(cdm_t.GUID, '') = ISNULL(bm_t.GUID, '') 
568
                
569
                WHERE bm_t.ParentTaxonFk  IS NULL AND NOT EXISTS 
570
                (SELECT * FROM [EM2PESI].[DBO].TAXON bm_pt
571
                WHERE (bm_pt.IdInSource = cdm_pt.IdInSource OR bm_pt.IdInSource IS NULL AND cdm_pt.IdInSource IS NULL) 
572
                                AND ISNULL(bm_pt.GUID, '') = ISNULL(cdm_pt.GUID, '')
573
                                AND bm_t.ParentTaxonFk = bm_pt.TaxonId)
574
                ORDER BY bm_t.Fullname
575
        */
576
                IF @n = 0 AND @n_cdm = 0 AND @n_bm = 0 BEGIN
577
                        PRINT ('All identical taxa have the same parent')
578
                END
579

    
580
-- TreeIndex
581
/* This is not checked. This field should be created by the PESI-Procedure recalculateallstoredpaths.
582
        Actually checking the parents of the same taxa is enough to ensure the compatibility of the taxonomical tree as a whole.
583
        See the Parent section
584
*/
585
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
586
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].TAXON WHERE TreeIndex IS NOT NULL
587
        SET @n = @n_bm - @n_cdm
588

    
589
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
590
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
591
        SET @str_n = Cast(@n AS NVARCHAR)
592

    
593
        IF @n = 0 BEGIN
594
                PRINT ('Both databases have the same number of taxa which have a tree index = ' + @str_n_bm)
595
        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)
596
        
597
------------------------------------------- RelTaxon -------------------------------
598
        PRINT ' '
599
        PRINT 'RELATIONSHIP'
600
        
601
-- taxonomical relationships between taxa and nomenclatural relationships between names
602
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].RELTAXON
603
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].RELTAXON
604
        SET @n = @n_bm - @n_cdm
605

    
606
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
607
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
608
        SET @str_n = Cast(@n AS NVARCHAR)
609

    
610
        IF @n = 0 BEGIN
611
                PRINT ('Both databases have the same number of relationship records = ' + @str_n_bm)
612
        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)
613

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

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

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

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

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

    
707

    
708
------------------------------------------- CommonName -------------------------------
709
        PRINT ' '
710
        PRINT 'COMMON NAMES'
711

    
712
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].CommonName
713
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].CommonName
714
        SET @n = @n_bm - @n_cdm
715

    
716
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
717
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
718
        SET @str_n = Cast(@n AS NVARCHAR)
719

    
720
        IF @n = 0 BEGIN
721
                PRINT ('Both databases have the same number of common names = ' + @str_n_bm)
722
        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)
723

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

    
800
------------------------------------------- AdditionalTaxonSource -------------------------------
801
        PRINT ' '
802
        PRINT 'ADDITIONAL TAXON SOURCE'
803
        
804
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].AdditionalTaxonSource
805
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].AdditionalTaxonSource
806
        SET @n = @n_bm - @n_cdm
807

    
808
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
809
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
810
        SET @str_n = Cast(@n AS NVARCHAR)
811

    
812
        IF @n = 0 BEGIN
813
                PRINT ('Both databases have the same number of additional sources = ' + @str_n_bm)
814
        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)
815

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

    
885
------------------------------ Image (currently exist only in ERMS) -------------------------------
886
        PRINT ' '
887
        PRINT 'IMAGES'
888
        
889
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Image
890
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Image
891
        SET @n = @n_bm - @n_cdm
892

    
893
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
894
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
895
        SET @str_n = Cast(@n AS NVARCHAR)
896

    
897
        IF @n = 0 BEGIN
898
                PRINT ('Both databases have the same number of images = ' + @str_n_bm)
899
        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)
900

    
901
                SELECT @n_bm = COUNT(*) -- images only in EM2PESI
902
                        FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
903
                        [EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
904
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
905
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
906
                        WHERE NOT EXISTS
907
                        (SELECT * FROM [CDM_EM2PESI].[DBO].Image
908
                        WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
909
                        AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
910
                        )
911
                SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
912
                IF @n_bm = 0 BEGIN
913
                        PRINT ('All images in EM2PESI exist also in CDM_EM2PESI')
914
                END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing images for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
915
        /*
916
                SELECT bm_t.Fullname, bm_i.img_thumb, bm_i.img_url, 'in EM2PESI but not in CDM_EM2PESI'
917
                FROM [EM2PESI].[DBO].Image bm_i INNER JOIN
918
                [EM2PESI].[DBO].TAXON bm_t ON bm_i.TaxonFk = bm_t.TaxonId INNER JOIN
919
                [CDM_EM2PESI].[DBO].TAXON cdm_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 [CDM_EM2PESI].[DBO].Image
923
                WHERE TaxonFk = cdm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(bm_i.img_thumb,'')
924
                AND ISNULL(img_url,'') = ISNULL(bm_i.img_url,'')
925
                )
926
        */
927
                SELECT @n_cdm = COUNT(*) -- images only in CDM_EM2PESI
928
                        FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
929
                        [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
930
                        [EM2PESI].[DBO].TAXON bm_t
931
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
932
                        WHERE NOT EXISTS
933
                        (SELECT * FROM [EM2PESI].[DBO].Image
934
                        WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
935
                        AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
936
                        )
937
                SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
938
                IF @n_cdm = 0 BEGIN
939
                        PRINT ('All images in CDM_EM2PESI exist also in EM2PESI')
940
                END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing images for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
941
        /*
942
                SELECT bm_t.Fullname, cdm_i.img_thumb, cdm_i.img_url, 'in CDM_EM2PESI but not in EM2PESI'
943
                FROM [CDM_EM2PESI].[DBO].Image cdm_i INNER JOIN
944
                [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_i.TaxonFk = cdm_t.TaxonId INNER JOIN
945
                [EM2PESI].[DBO].TAXON bm_t
946
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
947
                WHERE NOT EXISTS
948
                (SELECT * FROM [EM2PESI].[DBO].Image
949
                WHERE TaxonFk = bm_t.TaxonId AND ISNULL(img_thumb,'') = ISNULL(cdm_i.img_thumb,'')
950
                AND ISNULL(img_url,'') = ISNULL(cdm_i.img_url,'')
951
                )
952
        */
953
                IF @n_cdm = 0 AND @n_bm = 0 BEGIN
954
                        PRINT ('All images are identical in both databases')
955
                END
956

    
957
------------------------------------------- Note -------------------------------
958
        PRINT ' '
959
        PRINT 'NOTES'
960

    
961
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN
962
                [EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
963
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN
964
                [CDM_EM2PESI].[DBO].NoteSource ON NoteFk = NoteId
965
        SET @n = @n_bm - @n_cdm
966

    
967
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
968
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
969
        SET @str_n = Cast(@n AS NVARCHAR)
970

    
971
        IF @n = 0 BEGIN
972
                PRINT ('Both databases have the same number of note * notesource results = ' + @str_n_bm)
973
        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)
974

    
975
                SELECT @n_bm = COUNT(*) -- note * notesource results only in EM2PESI
976
                        FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
977
                        [EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
978
                        [EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
979
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
980
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
981
                        WHERE NOT EXISTS
982
                        (SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
983
                        ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
984
                        WHERE TaxonFk = cdm_t.TaxonId 
985
                        AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
986
                        AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
987
                        AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
988
                        AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
989
                        AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
990
                        AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
991
                        AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
992
                        AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
993
                        AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
994
                        )
995
                SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
996
                IF @n_bm = 0 BEGIN
997
                        PRINT ('All note * notesource results in EM2PESI exist also in CDM_EM2PESI')
998
                END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing note * notesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
999
        /*
1000
                Typical Problems: 
1001
                Duplicate entries during E+M SQL import for taxa using the same name object.
1002
                Missing source info by E+M SQL import
1003
                Last Action date automatically created (but wrong) by CDM import
1004
                
1005
                SELECT bm_t.Fullname, bm_n.Note_1, bm_n.Note_2, bm_n.NoteCategoryCache, bm_n.LanguageCache,
1006
                bm_n.SpeciesExpertName, bm_n.LastAction, bm_n.LastActionDate, bm_ns.SourceNameCache, bm_ns.SourceDetail,
1007
                'in EM2PESI but not in CDM_EM2PESI'
1008
                FROM [EM2PESI].[DBO].Note bm_n INNER JOIN
1009
                [EM2PESI].[DBO].TAXON bm_t ON bm_n.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
1010
                [EM2PESI].[DBO].NoteSource bm_ns ON bm_ns.NoteFk = bm_n.NoteId INNER JOIN
1011
                [CDM_EM2PESI].[DBO].TAXON cdm_t
1012
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1013
                WHERE NOT EXISTS
1014
                (SELECT * FROM [CDM_EM2PESI].[DBO].Note LEFT OUTER JOIN [CDM_EM2PESI].[DBO].NoteSource
1015
                ON [CDM_EM2PESI].[DBO].Note.NoteId = [CDM_EM2PESI].[DBO].NoteSource.NoteFk
1016
                WHERE TaxonFk = cdm_t.TaxonId 
1017
                AND ISNULL(Note_1,'') = ISNULL(bm_n.Note_1,'')
1018
                AND ISNULL(Note_2,'') = ISNULL(bm_n.Note_2,'')
1019
                AND ISNULL(NoteCategoryCache,'') = ISNULL(bm_n.NoteCategoryCache,'')
1020
                AND ISNULL(LanguageCache,'') = ISNULL(bm_n.LanguageCache,'')
1021
                AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_n.SpeciesExpertName,'')
1022
                AND ISNULL(LastAction,'') = ISNULL(bm_n.LastAction,'')
1023
                AND Left(ISNULL(LastActionDate,'00:00:00'),18) = Left(ISNULL(bm_n.LastActionDate,'00:00:00'),18)
1024
                AND ISNULL(SourceNameCache,'') = ISNULL(bm_ns.SourceNameCache,'')
1025
                AND ISNULL(SourceDetail,'') = ISNULL(bm_ns.SourceDetail,'')
1026
                )
1027
                ORDER BY bm_n.NoteCategoryFk, bm_t.Fullname, bm_n.Note_1
1028
        */
1029
                SELECT @n_cdm = COUNT(*) -- note * notesource results only in CDM_EM2PESI
1030
                        FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
1031
                        [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1032
                        [CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
1033
                        [EM2PESI].[DBO].TAXON bm_t
1034
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1035
                        WHERE NOT EXISTS
1036
                        (SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1037
                        ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1038
                        WHERE TaxonFk = bm_t.TaxonId 
1039
                        AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1040
                        AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1041
                        AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1042
                        AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1043
                        AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1044
                        AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1045
                        AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
1046
                        AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1047
                        AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1048
                        )
1049
                SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1050
                IF @n_cdm = 0 BEGIN
1051
                        PRINT ('All note * notesource results in CDM_EM2PESI exist also in EM2PESI')
1052
                END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing note * notesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
1053
        /*
1054
                SELECT bm_t.Fullname, cdm_n.Note_1, cdm_n.Note_2, cdm_n.NoteCategoryCache, cdm_n.LanguageCache,
1055
                cdm_n.SpeciesExpertName, cdm_n.LastAction, cdm_n.LastActionDate, cdm_ns.SourceNameCache, cdm_ns.SourceDetail,
1056
                'in CDM_EM2PESI but not in EM2PESI'
1057
                FROM [CDM_EM2PESI].[DBO].Note cdm_n INNER JOIN
1058
                [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_n.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1059
                [CDM_EM2PESI].[DBO].NoteSource cdm_ns ON cdm_ns.NoteFk = cdm_n.NoteId INNER JOIN
1060
                [EM2PESI].[DBO].TAXON bm_t
1061
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1062
                WHERE NOT EXISTS
1063
                (SELECT * FROM [EM2PESI].[DBO].Note LEFT OUTER JOIN [EM2PESI].[DBO].NoteSource
1064
                ON [EM2PESI].[DBO].Note.NoteId = [EM2PESI].[DBO].NoteSource.NoteFk
1065
                WHERE TaxonFk = bm_t.TaxonId 
1066
                AND ISNULL(Note_1,'') = ISNULL(cdm_n.Note_1,'')
1067
                AND ISNULL(Note_2,'') = ISNULL(cdm_n.Note_2,'')
1068
                AND ISNULL(NoteCategoryCache,'') = ISNULL(cdm_n.NoteCategoryCache,'')
1069
                AND ISNULL(LanguageCache,'') = ISNULL(cdm_n.LanguageCache,'')
1070
                AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_n.SpeciesExpertName,'')
1071
                AND ISNULL(LastAction,'') = ISNULL(cdm_n.LastAction,'')
1072
                AND LEFT(ISNULL(LastActionDate,'00:00:00'),18) = LEFT(ISNULL(cdm_n.LastActionDate,'00:00:00'),18)
1073
                AND ISNULL(SourceNameCache,'') = ISNULL(cdm_ns.SourceNameCache,'')
1074
                AND ISNULL(SourceDetail,'') = ISNULL(cdm_ns.SourceDetail,'')
1075
                )
1076
                ORDER BY cdm_n.NoteCategoryFk, cdm_t.Fullname,cdm_n.Note_1
1077
                
1078
        */
1079
                IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1080
                        PRINT ('All note * notesource results are identical in both databases')
1081
                END
1082

    
1083
------------------------------------------- Occurrence -------------------------------
1084
        PRINT ' '
1085
        PRINT 'OCCURRENCES'
1086
        
1087
        SELECT @n_bm = COUNT(*) FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
1088
                [EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
1089
        SELECT @n_cdm = COUNT(*) FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN
1090
                [CDM_EM2PESI].[DBO].OccurrenceSource ON OccurrenceFk = OccurrenceId
1091
        SET @n = @n_bm - @n_cdm
1092

    
1093
        SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
1094
        SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1095
        SET @str_n = Cast(@n AS NVARCHAR)
1096

    
1097
        IF @n = 0 BEGIN
1098
                PRINT ('Both databases have the same number of occurrence * occurrencesource results = ' + @str_n_bm)
1099
        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)
1100

    
1101
                SELECT @n_bm = COUNT(*) -- occurrence * occurrencesource results only in EM2PESI
1102
                        FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
1103
                        [EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
1104
                        [EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
1105
                        [CDM_EM2PESI].[DBO].TAXON cdm_t
1106
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1107
                        WHERE NOT EXISTS
1108
                        (SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
1109
                        ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1110
                        WHERE TaxonFk = cdm_t.TaxonId 
1111
                                AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1112
                                AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
1113
                                AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
1114
                                AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
1115
                                AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
1116
                                AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
1117
                                AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
1118
                                AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
1119
                                AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
1120
                        )
1121
                SET @str_n_bm = Cast(@n_bm AS NVARCHAR)
1122
                IF @n_bm = 0 BEGIN
1123
                        PRINT ('All occurrence * occurrencesource results in EM2PESI exist also in CDM_EM2PESI')
1124
                END ELSE PRINT ('WARNING: ' + @str_n_bm + ' existing occurrence * occurrencesource results for identical taxa in EM2PESI DO NOT exist in CDM_EM2PESI')
1125
        /*
1126
                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,
1127
                bm_o.SpeciesExpertName, bm_o.LastAction, bm_o.LastActionDate, bm_os.SourceNameCache, bm_os.OldTaxonName,
1128
                'in EM2PESI but not in CDM_EM2PESI'
1129
                FROM [EM2PESI].[DBO].Occurrence bm_o INNER JOIN
1130
                [EM2PESI].[DBO].TAXON bm_t ON bm_o.TaxonFk = bm_t.TaxonId LEFT OUTER JOIN
1131
                [EM2PESI].[DBO].OccurrenceSource bm_os ON bm_os.OccurrenceFk = bm_o.OccurrenceId INNER JOIN
1132
                [CDM_EM2PESI].[DBO].TAXON cdm_t
1133
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1134
                WHERE NOT EXISTS
1135
                (SELECT * FROM [CDM_EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [CDM_EM2PESI].[DBO].OccurrenceSource
1136
                ON [CDM_EM2PESI].[DBO].Occurrence.OccurrenceId = [CDM_EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1137
                WHERE TaxonFk = cdm_t.TaxonId 
1138
                AND ISNULL(TaxonFullNameCache,'') = ISNULL(bm_o.TaxonFullNameCache,'')
1139
                AND ISNULL(AreaNameCache,'') = ISNULL(bm_o.AreaNameCache,'')
1140
                AND ISNULL(OccurrenceStatusCache,'') = ISNULL(bm_o.OccurrenceStatusCache,'')
1141
                AND ISNULL(Notes,'') = ISNULL(bm_o.Notes,'')
1142
                AND ISNULL(SpeciesExpertName,'') = ISNULL(bm_o.SpeciesExpertName,'')
1143
                AND ISNULL(LastAction,'') = ISNULL(bm_o.LastAction,'')
1144
                AND ISNULL(LastActionDate,'00:00:00') = ISNULL(bm_o.LastActionDate,'00:00:00')
1145
                AND ISNULL(SourceNameCache,'') = ISNULL(bm_os.SourceNameCache,'')
1146
                AND ISNULL(OldTaxonName,'') = ISNULL(bm_os.OldTaxonName,'')
1147
                )
1148
                ORDER BY bm_t.Fullname, AreaNameCache 
1149
        */
1150
                SELECT @n_cdm = COUNT(*) -- occurrence * occurrencesource results only in CDM_EM2PESI
1151
                        FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
1152
                        [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1153
                        [CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
1154
                        [EM2PESI].[DBO].TAXON bm_t
1155
                        ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1156
                        WHERE NOT EXISTS
1157
                        (SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
1158
                        ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1159
                        WHERE TaxonFk = bm_t.TaxonId 
1160
                                AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1161
                                AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
1162
                                AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
1163
                                AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
1164
                                AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
1165
                                AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
1166
                                AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
1167
                                AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
1168
                                AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
1169
                        )
1170
                SET @str_n_cdm = Cast(@n_cdm AS NVARCHAR)
1171
                IF @n_cdm = 0 BEGIN
1172
                        PRINT ('All occurrence * occurrencesource results in CDM_EM2PESI exist also in EM2PESI')
1173
                END ELSE PRINT ('WARNING: ' + @str_n_cdm + ' existing occurrence * occurrencesource results for identical taxa in CDM_EM2PESI DO NOT exist in EM2PESI')
1174
        /*
1175
                SELECT cdm_t.TaxonId as cdmTID, bm_t.TaxonId bmTID, cdm_t.Fullname, cdm_o.TaxonFullNameCache, cdm_o.AreaNameCache, cdm_o.OccurrenceStatusCache, cdm_o.Notes,
1176
                cdm_o.SpeciesExpertName, cdm_o.LastAction, cdm_o.LastActionDate, cdm_os.SourceNameCache, cdm_os.OldTaxonName,
1177
                'in CDM_EM2PESI but not in EM2PESI'
1178
                FROM [CDM_EM2PESI].[DBO].Occurrence cdm_o INNER JOIN
1179
                [CDM_EM2PESI].[DBO].TAXON cdm_t ON cdm_o.TaxonFk = cdm_t.TaxonId LEFT OUTER JOIN
1180
                [CDM_EM2PESI].[DBO].OccurrenceSource cdm_os ON cdm_os.OccurrenceFk = cdm_o.OccurrenceId INNER JOIN
1181
                [EM2PESI].[DBO].TAXON bm_t
1182
                ON bm_t.IdInSource = cdm_t.IdInSource AND ISNULL(bm_t.GUID, '') = ISNULL(cdm_t.GUID, '')
1183
                WHERE NOT EXISTS
1184
                (SELECT * FROM [EM2PESI].[DBO].Occurrence LEFT OUTER JOIN [EM2PESI].[DBO].OccurrenceSource
1185
                ON [EM2PESI].[DBO].Occurrence.OccurrenceId = [EM2PESI].[DBO].OccurrenceSource.OccurrenceFk
1186
                WHERE TaxonFk = bm_t.TaxonId 
1187
                AND ISNULL(TaxonFullNameCache,'') = ISNULL(cdm_o.TaxonFullNameCache,'')
1188
                AND ISNULL(AreaNameCache,'') = ISNULL(cdm_o.AreaNameCache,'')
1189
                AND ISNULL(OccurrenceStatusCache,'') = ISNULL(cdm_o.OccurrenceStatusCache,'')
1190
                AND ISNULL(Notes,'') = ISNULL(cdm_o.Notes,'')
1191
                AND ISNULL(SpeciesExpertName,'') = ISNULL(cdm_o.SpeciesExpertName,'')
1192
                AND ISNULL(LastAction,'') = ISNULL(cdm_o.LastAction,'')
1193
                AND ISNULL(LastActionDate,'00:00:00') = ISNULL(cdm_o.LastActionDate,'00:00:00')
1194
                AND ISNULL(SourceNameCache,'') = ISNULL(cdm_os.SourceNameCache,'')
1195
                AND ISNULL(OldTaxonName,'') = ISNULL(cdm_os.OldTaxonName,'')
1196
                )
1197
                ORDER BY bm_t.Fullname, AreaNameCache 
1198
        */
1199
                IF @n_cdm = 0 AND @n_bm = 0 BEGIN
1200
                        PRINT ('All occurrence * occurrencesource results are identical in both databases')
1201
                END
1202

    
1203

    
1204
        PRINT ('End of check')
1205

    
1206

    
1207
END
Add picture from clipboard (Maximum size: 40 MB)