Project

General

Profile

« Previous | Next » 

Revision 8ef74e91

Added by Andreas Müller over 5 years ago

ref #7346 use E+M area vocabulary for common names

View differences:

app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelAreaImport.java
1
/**
2
* Copyright (C) 2007 EDIT
3
* European Distributed Institute of Taxonomy
4
* http://www.e-taxonomy.eu
5
*
6
* The contents of this file are subject to the Mozilla Public License Version 1.1
7
* See LICENSE.TXT at the top of this package for the full license terms.
8
*/
9

  
10
package eu.etaxonomy.cdm.io.berlinModel.in;
11

  
12
import java.net.URI;
13
import java.sql.ResultSet;
14
import java.sql.SQLException;
15
import java.util.HashMap;
16
import java.util.Map;
17
import java.util.UUID;
18

  
19
import org.apache.commons.lang.StringUtils;
20
import org.apache.log4j.Logger;
21
import org.springframework.stereotype.Component;
22
import org.springframework.transaction.TransactionStatus;
23

  
24
import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;
25
import eu.etaxonomy.cdm.io.berlinModel.in.validation.BerlinModelAreaImportValidator;
26
import eu.etaxonomy.cdm.io.common.CdmImportBase;
27
import eu.etaxonomy.cdm.io.common.IOValidator;
28
import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
29
import eu.etaxonomy.cdm.io.common.Source;
30
import eu.etaxonomy.cdm.model.common.Annotation;
31
import eu.etaxonomy.cdm.model.common.AnnotationType;
32
import eu.etaxonomy.cdm.model.common.CdmBase;
33
import eu.etaxonomy.cdm.model.common.ExtensionType;
34
import eu.etaxonomy.cdm.model.common.Language;
35
import eu.etaxonomy.cdm.model.common.Marker;
36
import eu.etaxonomy.cdm.model.common.MarkerType;
37
import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
38
import eu.etaxonomy.cdm.model.common.OriginalSourceType;
39
import eu.etaxonomy.cdm.model.common.TermType;
40
import eu.etaxonomy.cdm.model.common.TermVocabulary;
41
import eu.etaxonomy.cdm.model.location.NamedArea;
42
import eu.etaxonomy.cdm.model.location.NamedAreaLevel;
43
import eu.etaxonomy.cdm.model.location.NamedAreaType;
44
import eu.etaxonomy.cdm.model.reference.Reference;
45

  
46

  
47
/**
48
 * @author a.mueller
49
 * @since 20.03.2008
50
 */
51
@Component
52
public class BerlinModelAreaImport  extends BerlinModelImportBase {
53

  
54
    private static final long serialVersionUID = -2810052908318645219L;
55
    private static final Logger logger = Logger.getLogger(BerlinModelAreaImport.class);
56

  
57
	public static final String NAMESPACE = "emArea";
58

  
59
	private static final String pluralString = "areas";
60
	private static final String dbTableName = "emArea";  //??
61

  
62
	public BerlinModelAreaImport(){
63
		super(dbTableName, pluralString);
64
	}
65

  
66
	@Override
67
	protected String getIdQuery(BerlinModelImportState state) {
68
		String result = " SELECT AreaId FROM " + getTableName();
69
//		if (StringUtils.isNotBlank(state.getConfig().getOccurrenceFilter())){
70
//			result += " WHERE " +  state.getConfig().getOccurrenceFilter();
71
//		}
72
		return result;
73
	}
74

  
75
	@Override
76
	protected String getRecordQuery(BerlinModelImportConfigurator config) {
77
		String strQuery =
78
		          " SELECT * "
79
		        + " FROM emArea a "
80
                + " WHERE (a.AreaId IN (" + ID_LIST_TOKEN + ")  )"
81
//		        + " ORDER BY PTaxon.RIdentifier"
82
                ;
83
		return strQuery;
84
	}
85

  
86
	private Map<Integer, NamedArea> euroMedAreas = new HashMap<>();
87

  
88

  
89
	private TermVocabulary<NamedArea> createEuroMedAreas(BerlinModelImportState state) throws SQLException {
90

  
91

  
92
	    logger.warn("Start creating E+M areas");
93
		Source source = state.getConfig().getSource();
94
		Reference sourceReference = state.getConfig().getSourceReference();
95

  
96
		TransactionStatus txStatus = this.startTransaction();
97

  
98
		sourceReference = getSourceReference(sourceReference);
99

  
100
		TermVocabulary<NamedArea> euroMedAreasVoc = makeEmptyEuroMedVocabulary();
101

  
102
		MarkerType eurMarkerType = getMarkerType(state, BerlinModelTransformer.uuidEurArea, "eur", "eur Area", "eur", getEuroMedMarkerTypeVoc());
103
		MarkerType euroMedAreaMarkerType = getMarkerType(state, BerlinModelTransformer.uuidEurMedArea, "EuroMedArea", "EuroMedArea", "EuroMedArea", getEuroMedMarkerTypeVoc());
104
		ExtensionType isoCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidIsoCode, "IsoCode", "IsoCode", "iso");
105
		ExtensionType tdwgCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidTdwgAreaCode, "TDWG code", "TDWG Area code", "tdwg");
106
		ExtensionType mclCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidMclCode, "MCL code", "MedCheckList code", "mcl");
107
		NamedAreaLevel areaLevelTop = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelTop, "Euro+Med top area level", "Euro+Med top area level. This level is only to be used for the area representing the complete Euro+Med area", "e+m top", null);
108
		NamedAreaLevel areaLevelEm1 = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelFirst, "Euro+Med 1. area level", "Euro+Med 1. area level", "e+m 1.", null);
109
		NamedAreaLevel areaLevelEm2 = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelSecond, "Euro+Med 2. area level", "Euro+Med 2. area level", "Euro+Med 1. area level", null);
110

  
111

  
112
		String sql = "SELECT * , CASE WHEN EMCode = 'EM' THEN 'a' ELSE 'b' END as isEM " +
113
				" FROM emArea " +
114
				" ORDER BY isEM, EMCode";
115
		ResultSet rs = source.getResultSet(sql);
116

  
117
		NamedArea euroMedArea = null;
118
		NamedArea lastLevel1Area = null;
119

  
120
		//euroMedArea (EMCode = 'EM')
121
		rs.next();
122
		euroMedArea = makeSingleEuroMedArea(rs, eurMarkerType, euroMedAreaMarkerType, isoCodeExtType, tdwgCodeExtType, mclCodeExtType,
123
				areaLevelTop, areaLevelEm1 , areaLevelEm2, sourceReference, euroMedArea, lastLevel1Area);
124
		euroMedAreasVoc.addTerm(euroMedArea);
125

  
126
		//all other areas
127
		while (rs.next()){
128
			NamedArea newArea = makeSingleEuroMedArea(rs, eurMarkerType, euroMedAreaMarkerType,
129
					isoCodeExtType, tdwgCodeExtType, mclCodeExtType,
130
					areaLevelTop, areaLevelEm1 , areaLevelEm2, sourceReference, euroMedArea, lastLevel1Area);
131
			if (newArea != null){
132
    			euroMedAreasVoc.addTerm(newArea);
133
    			if (newArea.getPartOf().equals(euroMedArea)){
134
    				lastLevel1Area = newArea;
135
    			}
136
			}
137
		}
138
		emAreaFinetuning(euroMedAreasVoc, areaLevelEm2);
139

  
140
		markAreasAsHidden(state, euroMedAreasVoc);
141

  
142
	    getVocabularyService().saveOrUpdate(euroMedAreasVoc);
143

  
144
		try {
145
            commitTransaction(txStatus);
146
        } catch (Exception e) {
147
             e.printStackTrace();
148
             logger.error("An exception occurred when trying to commit E+M Areas");
149
        }
150
		logger.warn("Created E+M areas");
151

  
152
		return euroMedAreasVoc;
153
	}
154

  
155
	/**
156
     * @param areaLevelEm2
157
	 * @param euroMedAreas2
158
     */
159
    private void emAreaFinetuning(TermVocabulary<NamedArea> euroMedAreas, NamedAreaLevel areaLevelEm2) {
160
        //CZ
161
        NamedArea oldArea = euroMedAreas.getTermByIdInvocabulary("Cz");
162
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Cs"), areaLevelEm2);
163
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sk"), areaLevelEm2);
164

  
165
        //Ju
166
        oldArea = euroMedAreas.getTermByIdInvocabulary("Ju");
167
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("BH"), areaLevelEm2);
168
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Cg"), areaLevelEm2);
169
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Ct"), areaLevelEm2);
170
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Mk"), areaLevelEm2);
171
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sl"), areaLevelEm2);
172
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sr"), areaLevelEm2);
173

  
174
        //IJ
175
        oldArea = euroMedAreas.getTermByIdInvocabulary("IJ");
176
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Ir"), areaLevelEm2);
177
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Jo"), areaLevelEm2);
178

  
179
        //LS
180
        oldArea = euroMedAreas.getTermByIdInvocabulary("LS");
181
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Le"), areaLevelEm2);
182
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sy"), areaLevelEm2);
183

  
184
    }
185

  
186
    //5.Mark areas to be hidden #3979 .5
187
    private void markAreasAsHidden(BerlinModelImportState state, TermVocabulary<NamedArea> euroMedAreasVoc) {
188

  
189
        try {
190

  
191
            @SuppressWarnings("unchecked")
192
            TermVocabulary<MarkerType> vocUserDefinedMarkerTypes = getVocabularyService().find(CdmImportBase.uuidUserDefinedMarkerTypeVocabulary);
193
            if (vocUserDefinedMarkerTypes == null){
194
                String message = "Marker type vocabulary could not be found. Hidden areas not added.";
195
                logger.error(message);
196
                System.out.println(message);
197
            }
198
            MarkerType hiddenAreaMarkerType = getMarkerType(state, BerlinModelTransformer.uuidHiddenArea,
199
                    "Hidden Area","Used to hide distributions for the named areas in publications", null, getEuroMedMarkerTypeVoc());
200

  
201
            //Add hidden area marker to Rs(C) and Rs(N)
202
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs);
203
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_B);
204
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_C);
205
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_E);
206
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_N);
207
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_K);
208
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_W);
209
        } catch (Exception e) {
210
            e.printStackTrace();
211
            logger.error("Exception in markAreasAsHidden: " + e.getMessage());
212
        }
213

  
214
    }
215

  
216
    private void hideArea(TermVocabulary<NamedArea> euroMedAreasVoc, MarkerType hiddenAreaMarkerType, UUID areaUuid) {
217
        for (NamedArea namedArea : euroMedAreasVoc){
218
            if (namedArea.getUuid().equals(areaUuid)){
219
                namedArea.addMarker(Marker.NewInstance(hiddenAreaMarkerType, true));
220
                return;
221
            }
222
        }
223
    }
224

  
225
    /**
226
     * @param oldArea
227
     * @param namedArea
228
     * @param areaLevelEm2
229
     */
230
    private void makeSubterm(NamedArea oldArea, NamedArea namedArea, NamedAreaLevel areaLevelEm2) {
231
        namedArea.setLevel(areaLevelEm2);
232
        namedArea.setPartOf(oldArea);
233
    }
234

  
235

  
236
	/**
237
	 * @param eurMarkerType
238
	 * @param euroMedAreaMarkerType
239
	 * @param isoCodeExtType
240
	 * @param tdwgCodeExtType
241
	 * @param mclCodeExtType
242
	 * @param rs
243
	 * @param areaLevelEm2
244
	 * @param areaLevelEm1
245
	 * @param areaLevelTop
246
	 * @throws SQLException
247
	 */
248
	private NamedArea makeSingleEuroMedArea(ResultSet rs, MarkerType eurMarkerType,
249
			MarkerType euroMedAreaMarkerType, ExtensionType isoCodeExtType,
250
			ExtensionType tdwgCodeExtType, ExtensionType mclCodeExtType,
251
			NamedAreaLevel areaLevelTop, NamedAreaLevel areaLevelEm1, NamedAreaLevel areaLevelEm2,
252
			Reference sourceReference, NamedArea euroMedArea, NamedArea level1Area) throws SQLException {
253

  
254
	    Integer areaId = rs.getInt("AreaId");
255
		String emCode = nullSafeTrim(rs.getString("EMCode"));
256
		String isoCode = nullSafeTrim(rs.getString("ISOCode"));
257
		String tdwgCode = nullSafeTrim(rs.getString("TDWGCode"));
258
		String unit = nullSafeTrim(rs.getString("Unit"));
259
//				      ,[Status]
260
//				      ,[OutputOrder]
261
		boolean eurMarker = rs.getBoolean("eur");
262
		boolean euroMedAreaMarker = rs.getBoolean("EuroMedArea");
263
		String notes = nullSafeTrim(rs.getString("Notes"));
264
		String mclCode = nullSafeTrim(rs.getString("MCLCode"));
265
		String geoSearch = nullSafeTrim(rs.getString("NameForGeoSearch"));
266

  
267

  
268
		if (isBlank(emCode)){
269
			emCode = unit;
270
		}
271

  
272
		//uuid
273
		UUID uuid = BerlinModelTransformer.getEMAreaUuid(emCode);
274
		NamedArea area = (NamedArea)getTermService().find(uuid);
275
		if (area == null){
276
			//label
277
			area = NamedArea.NewInstance(geoSearch, unit, emCode);
278
			if (uuid != null){
279
				area.setUuid(uuid);
280
			}else{
281
			    if (areaId == 211 || areaId == 213){  //Additional Azores and Canary Is. area are merged into primary area, see also area.addSource part below
282
			        return null;
283
			    }
284
				logger.warn("Uuid for emCode could not be defined: " + emCode);
285
			}
286
		}
287

  
288

  
289
		//code
290
		area.setIdInVocabulary(emCode);
291
		//notes
292
		if (StringUtils.isNotEmpty(notes)){
293
			area.addAnnotation(Annotation.NewInstance(notes, AnnotationType.EDITORIAL(), Language.DEFAULT()));
294
		}
295
		//markers
296
		area.addMarker(Marker.NewInstance(eurMarkerType, eurMarker));
297
		area.addMarker(Marker.NewInstance(euroMedAreaMarkerType, euroMedAreaMarker));
298

  
299
		//extensions
300
		if (isNotBlank(isoCode)){
301
			area.addExtension(isoCode, isoCodeExtType);
302
		}
303
		if (isNotBlank(tdwgCode)){
304
			area.addExtension(tdwgCode, tdwgCodeExtType);
305
		}
306
		if (isNotBlank(mclCode)){
307
			area.addExtension(mclCode, mclCodeExtType);
308
		}
309

  
310
		//type
311
		area.setType(NamedAreaType.ADMINISTRATION_AREA());
312

  
313
		//source
314
		area.addSource(OriginalSourceType.Import, String.valueOf(areaId), NAMESPACE, sourceReference, null);
315
		//add duplicate area ids for canary
316
		if (areaId == 624){ //Canary Is.
317
		    area.addSource(OriginalSourceType.Import, String.valueOf(213), NAMESPACE, sourceReference, null);
318
		}
319
		if (areaId == 210){//Azores
320
            area.addSource(OriginalSourceType.Import, String.valueOf(211), NAMESPACE, sourceReference, null);
321
        }
322

  
323
		//parent
324
		if (euroMedArea != null){
325
			if (emCode.contains("(")){
326
				area.setPartOf(level1Area);
327
				area.setLevel(areaLevelEm2);
328
			}else{
329
				area.setPartOf(euroMedArea);
330
				area.setLevel(areaLevelEm1);
331
			}
332
		}else{
333
			area.setLevel(areaLevelTop);
334
		}
335
		this.euroMedAreas.put(areaId, area);
336

  
337
		//save
338
		getTermService().saveOrUpdate(area);
339

  
340
		return area;
341
	}
342

  
343
	private String nullSafeTrim(String string) {
344
		if (string == null){
345
			return null;
346
		}else{
347
			return string.trim();
348
		}
349
	}
350

  
351
	/**
352
	 *
353
	 */
354
	private TermVocabulary<NamedArea> makeEmptyEuroMedVocabulary() {
355
		TermType type = TermType.NamedArea;
356
		String description = "Euro+Med area vocabulary";
357
		String label = "E+M areas";
358
		String abbrev = null;
359
		URI termSourceUri = null;
360
		OrderedTermVocabulary<NamedArea> result = OrderedTermVocabulary.NewInstance(type, description, label, abbrev, termSourceUri);
361

  
362
		result.setUuid(BerlinModelTransformer.uuidVocEuroMedAreas);
363
		getVocabularyService().save(result);
364
		return result;
365
	}
366

  
367
	@Override
368
	public boolean doPartition(@SuppressWarnings("rawtypes") ResultSetPartitioner partitioner, BerlinModelImportState state)  {
369
	    TermVocabulary<?> voc = getVocabularyService().find(BerlinModelTransformer.uuidVocEuroMedAreas);
370
	    if (voc == null){
371
	        try {
372
	            createEuroMedAreas(state);
373
	        } catch (SQLException e) {
374
	            logger.warn("Exception when creating areas: " + e.getMessage());
375
	            e.printStackTrace();
376
	        }
377
        }
378
	    return true;
379
	}
380

  
381

  
382
	@Override
383
	public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition(ResultSet rs, BerlinModelImportState state) {
384

  
385
		Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<>();
386
		return result;
387
	}
388

  
389
	@Override
390
	protected boolean doCheck(BerlinModelImportState state){
391
		IOValidator<BerlinModelImportState> validator = new BerlinModelAreaImportValidator();
392
		return validator.validate(state);
393
	}
394

  
395

  
396
	@Override
397
	protected boolean isIgnore(BerlinModelImportState state){
398
		if (! (state.getConfig().isDoOccurrence() || state.getConfig().isDoCommonNames())){
399
			return true;
400
		}else{
401
			if (!this.checkSqlServerColumnExists(state.getConfig().getSource(), "emArea", "AreaId")){
402
				logger.error("emArea table or AreaId column do not exist. Must ignore area import");
403
				return true;
404
			}else{
405
				return false;
406
			}
407
		}
408
	}
409

  
410
}
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelCommonNamesImport.java
24 24
import org.apache.commons.lang.StringUtils;
25 25
import org.apache.log4j.Logger;
26 26
import org.springframework.stereotype.Component;
27
import org.springframework.transaction.TransactionStatus;
27 28

  
28 29
import eu.etaxonomy.cdm.common.CdmUtils;
29 30
import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;
......
31 32
import eu.etaxonomy.cdm.io.common.IOValidator;
32 33
import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
33 34
import eu.etaxonomy.cdm.io.common.Source;
34
import eu.etaxonomy.cdm.io.common.TdwgAreaProvider;
35 35
import eu.etaxonomy.cdm.model.common.CdmBase;
36 36
import eu.etaxonomy.cdm.model.common.Extension;
37 37
import eu.etaxonomy.cdm.model.common.ExtensionType;
38
import eu.etaxonomy.cdm.model.common.IdentifiableSource;
38 39
import eu.etaxonomy.cdm.model.common.Language;
39 40
import eu.etaxonomy.cdm.model.common.Marker;
40 41
import eu.etaxonomy.cdm.model.common.MarkerType;
42
import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
41 43
import eu.etaxonomy.cdm.model.common.Representation;
42 44
import eu.etaxonomy.cdm.model.common.TermVocabulary;
43 45
import eu.etaxonomy.cdm.model.description.CommonTaxonName;
44 46
import eu.etaxonomy.cdm.model.description.DescriptionElementSource;
45 47
import eu.etaxonomy.cdm.model.description.TaxonDescription;
46
import eu.etaxonomy.cdm.model.location.Country;
47 48
import eu.etaxonomy.cdm.model.location.NamedArea;
48 49
import eu.etaxonomy.cdm.model.name.TaxonName;
49 50
import eu.etaxonomy.cdm.model.reference.Reference;
......
74 75

  
75 76

  
76 77
	//map that stores the regions (named areas) and makes them accessible via the regionFk
77
	private Map<String, NamedArea> regionMap = new HashMap<>();
78
	private Map<String, NamedArea> regionFkToAreaMap = new HashMap<>();
78 79

  
79 80
	public BerlinModelCommonNamesImport(){
80 81
		super(dbTableName, pluralString);
......
141 142
	 */
142 143
	private void makeRegions(BerlinModelImportState state) {
143 144
		try {
144
			SortedSet<Integer> regionFks = new TreeSet<>();
145
			TransactionStatus tx = startTransaction();
146
		    SortedSet<Integer> regionFks = new TreeSet<>();
145 147
			Source source = state.getConfig().getSource();
146 148

  
147 149
			//fill set with all regionFk from emCommonName.regionFks
148
			getRegionFks(state, regionFks, source);
150
			fillRegionFks(state, regionFks, source);
149 151
			//concat filter string
150 152
			String sqlWhere = getSqlWhere(regionFks);
151 153

  
152 154
			//get E+M - TDWG Mapping
153
			Map<String, String> emTdwgMap = getEmTdwgMap(source);
155
//			Map<String, String> emTdwgMap = getEmTdwgMap(source);
156
			Map<String, NamedArea> emCodeToAreaMap = getEmCodeToAreaMap(source);
154 157
			//fill regionMap
155
			fillRegionMap(state, sqlWhere, emTdwgMap);
158
			fillRegionMap(state, sqlWhere, emCodeToAreaMap);
159

  
160
			commitTransaction(tx);
156 161

  
157 162
			return;
158 163
		} catch (NumberFormatException e) {
......
167 172
	}
168 173

  
169 174

  
170
	@Override
175
    @Override
171 176
	public boolean doPartition(ResultSetPartitioner partitioner, BerlinModelImportState state)  {
172 177
		boolean success = true ;
173 178

  
......
269 274
					}
270 275
					commonTaxonNames.add(commonTaxonName);
271 276
					regionFk = regionFk.trim();
272
					NamedArea area = regionMap.get(regionFk);
277
					NamedArea area = regionFkToAreaMap.get(regionFk);
273 278
					if (area == null){
274 279
						if (isNotBlank(regionFk) && regionFk != NO_REGION){
275 280
							logger.warn("Area for " + regionFk + " not defined in regionMap.");
......
311 316
	                commonTaxonName.addSource(source);
312 317
				}
313 318

  
314

  
315 319
				//MisNameRef
316 320
				if (misNameRefFk != null){
317 321
					//Taxon misappliedName = getMisappliedName(biblioRefMap, nomRefMap, misNameRefFk, taxon);
......
515 519
	 * @throws SQLException
516 520
	 *
517 521
	 */
518
	private void getRegionFks(BerlinModelImportState state, SortedSet<Integer> regionFks, Source source) throws SQLException {
519
		String sql = " SELECT DISTINCT RegionFks FROM emCommonName";
522
	private void fillRegionFks(BerlinModelImportState state, SortedSet<Integer> regionFks,
523
	        Source source) throws SQLException {
524
		String sql =
525
		          " SELECT DISTINCT RegionFks "
526
		        + " FROM emCommonName";
520 527
		if (state.getConfig().getCommonNameFilter() != null){
521 528
			sql += " WHERE " + state.getConfig().getCommonNameFilter();
522 529
		}
......
553 560
	 * @throws SQLException
554 561
	 */
555 562
	private void fillRegionMap(BerlinModelImportState state, String sqlWhere,
556
			Map<String, String> emTdwgMap) throws SQLException {
557
		Source source = state.getConfig().getSource();
558
		String sql;
559
		ResultSet rs;
560
		sql = " SELECT RegionId, Region FROM emLanguageRegion WHERE RegionId IN ("+ sqlWhere+ ") ";
561
		rs = source.getResultSet(sql);
563
			Map<String, NamedArea> emCodeToAreaMap) throws SQLException {
564

  
565
	    Source source = state.getConfig().getSource();
566
		String sql =
567
		      " SELECT RegionId, Region "
568
		    + " FROM  emLanguageRegion "
569
		    + " WHERE RegionId IN ("+ sqlWhere+ ") ";
570
		ResultSet rs = source.getResultSet(sql);
562 571
		while (rs.next()){
563 572
			Object regionId = rs.getObject("RegionId");
564 573
			String region = rs.getString("Region");
......
566 575
			if (splitRegion.length <= 1){
567 576
				NamedArea newArea = getNamedArea(state, null, region, "Language region '" + region + "'", null, null, null);
568 577
//				getTermService().save(newArea);
569
				regionMap.put(String.valueOf(regionId), newArea);
570
				logger.info("Found new area: " +  region);
578
				regionFkToAreaMap.put(String.valueOf(regionId), newArea);
579
				logger.warn("Found new area: " +  region);
571 580
			}else if (splitRegion.length == 2){
572
				String emCode = splitRegion[1].trim();
573
				String tdwgCode = emTdwgMap.get(emCode);
574
				if (isNotBlank(tdwgCode) ){
575
					NamedArea tdwgArea = getNamedArea(state, tdwgCode);
576
					regionMap.put(String.valueOf(regionId), tdwgArea);
577
				}else {
578
					NamedArea area = getOtherAreas(state, emCode, tdwgCode);
579
					if (area != null){
580
						regionMap.put(String.valueOf(regionId), area);
581
					}else{
582
						logger.warn("emCode did not map to valid tdwgCode: " +  CdmUtils.Nz(emCode) + "->" + CdmUtils.Nz(tdwgCode));
583
					}
581
				String emCode = splitRegion[1].trim().replace(" ", "");
582

  
583
				NamedArea area = emCodeToAreaMap.get(emCode);
584
				if (area == null){
585
				    String[] splits = emCode.split("/");
586
				    if (splits.length == 2){
587
				        area = emCodeToAreaMap.get(splits[0]);
588
		            }
589
				    if (area != null){
590
				        logger.warn("emCode ambigous. Use larger area: " +  CdmUtils.Nz(emCode) + "->" + regionId);
591
				    }else{
592
				        logger.warn("emCode not recognized. Region not defined: " +  CdmUtils.Nz(emCode) + "->" + regionId);
593
				    }
594
				}
595
				if (area != null){
596
				    regionFkToAreaMap.put(String.valueOf(regionId), area);
584 597
				}
585 598
			}
586 599
		}
587 600
	}
588 601

  
589

  
590
	/**
591
	 * Returns the are for a given TDWG code. See {@link #getEmTdwgMap(Source)} for exceptions from
592
	 * the TDWG code
593
	 * @param state
594
	 * @param tdwgCode
595
	 */
596
	private NamedArea getNamedArea(BerlinModelImportState state, String tdwgCode) {
597
		NamedArea area;
598
		if (tdwgCode.equalsIgnoreCase("Ab")){
599
			area = getNamedArea(state, BerlinModelTransformer.uuidAb, "Azerbaijan & Nakhichevan", "Azerbaijan (including Nakhichevan)",  "Ab", null, null);
600
			getTermService().saveOrUpdate(area);
601
		}else if (tdwgCode.equalsIgnoreCase("Uk")){
602
			area = getNamedArea(state, BerlinModelTransformer.uuidUk , "Ukraine & Crimea", "Ukraine (including Crimea)", "Uk", null, null);
603
			getTermService().saveOrUpdate(area);
604
		}else if (tdwgCode.equalsIgnoreCase("Rf")){
605
//			area = getNamedArea(state, BerlinModelTransformer.uuidRf , "Ukraine & Crimea", "Ukraine (including Crimea)", "Uk", null, null);
606
//			getTermService().saveOrUpdate(area);
607
			area = Country.RUSSIANFEDERATION();
608
		}else if (tdwgCode.equalsIgnoreCase("Gg")){
609
			area = Country.GEORGIA();
610
		}else if (tdwgCode.equalsIgnoreCase("SM")){
611
            area = getNamedArea(state, BerlinModelTransformer.uuidSM , "Serbia & Montenegro", "Serbia & Montenegro", "SM", null, null);
612
            getTermService().saveOrUpdate(area);
613
        }else if (tdwgCode.equalsIgnoreCase("Tu")){
614
            area = Country.TURKEYREPUBLICOF();
615
        }else{
616
			area = TdwgAreaProvider.getAreaByTdwgAbbreviation(tdwgCode);
617
		}
618
		if (area == null){
619
			logger.warn("Area is null for " + tdwgCode);
620
		}
621
		return area;
622
	}
623

  
624 602
	/**
625 603
	 * @param regionFks
626 604
	 * @return
......
634 612
		return sqlWhere;
635 613
	}
636 614

  
637
	/**
638
	 * Returns a map which is filled by the emCode->TdwgCode mapping defined in emArea.
639
	 * Some exceptions are defined for emCode 'Ab','Rf','Uk' and some additional mapping is added
640
	 * for 'Ab / Ab(A)', 'Ga / Ga(F)', 'It / It(I)', 'Ar / Ar(A)','Hs / Hs(S)'
641
	 * @param source
642
	 * @throws SQLException
643
	 */
644
	private Map<String, String> getEmTdwgMap(Source source) throws SQLException {
645
		String sql;
646
		ResultSet rs;
647
		Map<String, String> emTdwgMap = new HashMap<>();
648
		sql = " SELECT EmCode, TDWGCode FROM emArea ";
649
		rs = source.getResultSet(sql);
650
		while (rs.next()){
651
			String emCode = rs.getString("EMCode");
652
			String TDWGCode = rs.getString("TDWGCode");
653
			if (isNotBlank(emCode) ){
654
				emCode = emCode.trim();
655
				if (emCode.equalsIgnoreCase("Ab") || emCode.equalsIgnoreCase("Rf")||
656
						emCode.equalsIgnoreCase("Uk") || emCode.equalsIgnoreCase("Gg")
657
						|| emCode.equalsIgnoreCase("SM") || emCode.equalsIgnoreCase("Tu")){
658
					emTdwgMap.put(emCode, emCode);
659
				}else if (isNotBlank(TDWGCode)){
660
					emTdwgMap.put(emCode, TDWGCode.trim());
661
				}
662
			}
663
		}
664
		emTdwgMap.put("Ab / Ab(A)", "Ab");
665
		emTdwgMap.put("Ga / Ga(F)", "FRA-FR");
666
		emTdwgMap.put("It / It(I)", "ITA");
667
		emTdwgMap.put("Uk / Uk(U)", "Uk");
668
		emTdwgMap.put("Ar / Ar(A)", "TCS-AR");
669
		emTdwgMap.put("Hs / Hs(S)", "SPA-SP");
670
		emTdwgMap.put("Hb / Hb(E)", "IRE-IR");
671

  
672
		return emTdwgMap;
673
	}
615
//	/**
616
//	 * Returns a map which is filled by the emCode->TdwgCode mapping defined in emArea.
617
//	 * Some exceptions are defined for emCode 'Ab','Rf','Uk' and some additional mapping is added
618
//	 * for 'Ab / Ab(A)', 'Ga / Ga(F)', 'It / It(I)', 'Ar / Ar(A)','Hs / Hs(S)'
619
//	 * @param source
620
//	 * @throws SQLException
621
//	 */
622
//	private Map<String, String> getEmTdwgMap(Source source) throws SQLException {
623
//
624
//		Map<String, String> emTdwgMap = new HashMap<>();
625
//		String sql = " SELECT EmCode, TDWGCode "
626
//		    + " FROM emArea ";
627
//		ResultSet rs = source.getResultSet(sql);
628
//		while (rs.next()){
629
//			String emCode = rs.getString("EMCode");
630
//			String TDWGCode = rs.getString("TDWGCode");
631
//			if (isNotBlank(emCode) ){
632
//				emCode = emCode.trim();
633
//				if (emCode.equalsIgnoreCase("Ab") || emCode.equalsIgnoreCase("Rf")||
634
//						emCode.equalsIgnoreCase("Uk") || emCode.equalsIgnoreCase("Gg")
635
//						|| emCode.equalsIgnoreCase("SM") || emCode.equalsIgnoreCase("Tu")){
636
//					emTdwgMap.put(emCode, emCode);
637
//				}else if (isNotBlank(TDWGCode)){
638
//					emTdwgMap.put(emCode, TDWGCode.trim());
639
//				}
640
//			}
641
//		}
642
//		emTdwgMap.put("Ab / Ab(A)", "Ab");
643
//		emTdwgMap.put("Ga / Ga(F)", "FRA-FR");
644
//		emTdwgMap.put("It / It(I)", "ITA");
645
//		emTdwgMap.put("Uk / Uk(U)", "Uk");
646
//		emTdwgMap.put("Ar / Ar(A)", "TCS-AR");
647
//		emTdwgMap.put("Hs / Hs(S)", "SPA-SP");
648
//		emTdwgMap.put("Hb / Hb(E)", "IRE-IR");
649
//
650
//		return emTdwgMap;
651
//	}
652

  
653

  
654

  
655
    /**
656
     * @param source
657
     * @return
658
     * @throws SQLException
659
     */
660
    private Map<String, NamedArea> getEmCodeToAreaMap(Source source) throws SQLException {
661
        Map<String, NamedArea> emCodeToAreaMap = new HashMap<>();
662
        String sql =
663
              " SELECT EmCode, AreaId "
664
            + " FROM emArea ";
665
        ResultSet rs = source.getResultSet(sql);
666
        while (rs.next()){
667

  
668
            String emCode = rs.getString("EMCode");
669
            if (isNotBlank(emCode)){
670
                Integer areaId = rs.getInt("AreaId");
671
                NamedArea area = getAreaByAreaId(areaId);
672
                if (area != null){
673
                    emCodeToAreaMap.put(emCode.trim(), area);
674
                }else{
675
                    logger.warn("Area not found for areaId " + areaId);
676
                }
677
            }
678

  
679
        }
680

  
681
//        emTdwgMap.put("Ab / Ab(A)", "Ab");
682

  
683
        return emCodeToAreaMap;
684
    }
685

  
686
    /**
687
     * @param emCode
688
     * @return
689
     */
690
    private NamedArea getAreaByAreaId(int areaId) {
691
        NamedArea result = null;
692
        String areaIdStr = String.valueOf(areaId);
693
        OrderedTermVocabulary<NamedArea> voc = getAreaVoc();
694
        getVocabularyService().update(voc);
695
        for (NamedArea area : voc.getTerms()){
696
            for (IdentifiableSource source : area.getSources()){
697
                if (areaIdStr.equals(source.getIdInSource()) && BerlinModelAreaImport.NAMESPACE.equals(source.getIdNamespace())){
698
                    if (result != null){
699
                        logger.warn("Result for areaId already exists. areaId: " + areaId);
700
                    }
701
                    result = area;
702
                }
703
            }
704
        }
705
        return result;
706
    }
707

  
708
    private OrderedTermVocabulary<NamedArea> areaVoc;
709
    @SuppressWarnings("unchecked")
710
    private OrderedTermVocabulary<NamedArea> getAreaVoc(){
711
        if (areaVoc == null){
712
            areaVoc = (OrderedTermVocabulary<NamedArea>)getVocabularyService().find(BerlinModelTransformer.uuidVocEuroMedAreas);
713
        }
714
        return areaVoc;
715
    }
674 716

  
675 717

  
676 718
	/**
......
739 781
			result.put(nameSpace, referenceMap);
740 782
			// TODO remove if problem with duplicate DescElement_Annot id is solved
741 783
		} catch (SQLException e) {
742
			throw new RuntimeException("pos: " +pos, e);
784
			throw new RuntimeException("pos: " + pos, e);
743 785
		} catch (NullPointerException nep){
744 786
			logger.error("NullPointerException in getRelatedObjectsForPartition()");
745 787
		}
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelImportBase.java
287 287
		}else if("TCS-AB;TCS-AD;TCS-GR".equals(tdwg)){
288 288
			return Country.GEORGIA();
289 289

  
290

  
291 290
		}else if("Cc".equals(em)){
292 291
			return getNamedArea(state, BerlinModelTransformer.uuidCaucasia, "Caucasia (Ab + Ar + Gg + Rf(CS))", "Euro+Med area 'Caucasia (Ab + Ar + Gg + Rf(CS))'", "Cc", null, null);
293 292
		}
......
394 393
        return markerTypeVoc;
395 394
    }
396 395

  
396

  
397
    /**
398
     * @param sourceReference
399
     * @return
400
     */
401
    protected Reference getSourceReference(Reference sourceReference) {
402
        Reference persistentSourceReference = getReferenceService().find(sourceReference.getUuid());  //just to be sure
403
        if (persistentSourceReference != null){
404
            sourceReference = persistentSourceReference;
405
        }
406
        return sourceReference;
407
    }
408

  
397 409
}
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelImportConfigurator.java
139 139
				, BerlinModelTypesImport.class
140 140
				, BerlinModelTaxonImport.class
141 141
				, BerlinModelFactsImport.class
142
				, BerlinModelCommonNamesImport.class
142
				, BerlinModelAreaImport.class
143 143
				, BerlinModelOccurrenceImport.class
144 144
				, BerlinModelOccurrenceSourceImport.class
145 145
				, BerlinModelWebMarkerCategoryImport.class
146 146
				, BerlinModelWebMarkerImport.class
147 147
				, BerlinModelTaxonRelationImport.class
148
                , BerlinModelCommonNamesImport.class
149

  
148 150
		};
149 151
	}
150 152

  
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelOccurrenceImport.java
9 9

  
10 10
package eu.etaxonomy.cdm.io.berlinModel.in;
11 11

  
12
import java.net.URI;
13 12
import java.sql.ResultSet;
14 13
import java.sql.SQLException;
15 14
import java.util.ArrayList;
......
18 17
import java.util.List;
19 18
import java.util.Map;
20 19
import java.util.Set;
21
import java.util.UUID;
22 20

  
23 21
import org.apache.commons.lang.StringUtils;
24 22
import org.apache.log4j.Logger;
25 23
import org.springframework.stereotype.Component;
26
import org.springframework.transaction.TransactionStatus;
27 24

  
28 25
import eu.etaxonomy.cdm.common.CdmUtils;
29 26
import eu.etaxonomy.cdm.hibernate.HibernateProxyHelper;
30 27
import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;
31 28
import eu.etaxonomy.cdm.io.berlinModel.in.validation.BerlinModelOccurrenceImportValidator;
32
import eu.etaxonomy.cdm.io.common.CdmImportBase;
33 29
import eu.etaxonomy.cdm.io.common.IOValidator;
34 30
import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
35
import eu.etaxonomy.cdm.io.common.Source;
36 31
import eu.etaxonomy.cdm.io.common.TdwgAreaProvider;
37 32
import eu.etaxonomy.cdm.model.common.Annotation;
38 33
import eu.etaxonomy.cdm.model.common.AnnotationType;
39 34
import eu.etaxonomy.cdm.model.common.CdmBase;
40
import eu.etaxonomy.cdm.model.common.ExtensionType;
35
import eu.etaxonomy.cdm.model.common.IdentifiableSource;
41 36
import eu.etaxonomy.cdm.model.common.Language;
42 37
import eu.etaxonomy.cdm.model.common.Marker;
43 38
import eu.etaxonomy.cdm.model.common.MarkerType;
44 39
import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
45
import eu.etaxonomy.cdm.model.common.OriginalSourceType;
46
import eu.etaxonomy.cdm.model.common.TermType;
47
import eu.etaxonomy.cdm.model.common.TermVocabulary;
48 40
import eu.etaxonomy.cdm.model.description.DescriptionElementBase;
49 41
import eu.etaxonomy.cdm.model.description.Distribution;
50 42
import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
51 43
import eu.etaxonomy.cdm.model.description.TaxonDescription;
52 44
import eu.etaxonomy.cdm.model.location.NamedArea;
53
import eu.etaxonomy.cdm.model.location.NamedAreaLevel;
54
import eu.etaxonomy.cdm.model.location.NamedAreaType;
55 45
import eu.etaxonomy.cdm.model.reference.Reference;
56 46
import eu.etaxonomy.cdm.model.taxon.Taxon;
57 47
import eu.etaxonomy.cdm.model.taxon.TaxonBase;
......
64 54
 */
65 55
@Component
66 56
public class BerlinModelOccurrenceImport  extends BerlinModelImportBase {
67
	private static final Logger logger = Logger.getLogger(BerlinModelOccurrenceImport.class);
57

  
58
    private static final long serialVersionUID = -7918122767284077183L;
59

  
60
    private static final Logger logger = Logger.getLogger(BerlinModelOccurrenceImport.class);
68 61

  
69 62
	public static final String NAMESPACE = "Occurrence";
70 63
	private static final String EM_AREA_NAMESPACE = "emArea";
......
104 97
		return strQuery;
105 98
	}
106 99

  
107
	private Map<Integer, NamedArea> euroMedAreas = new HashMap<Integer, NamedArea>();
100
//	private Map<Integer, NamedArea> euroMedAreas = new HashMap<>();
108 101

  
109 102

  
110 103
	@Override
111 104
	public void doInvoke(BerlinModelImportState state) {
112
		if (state.getConfig().isUseEmAreaVocabulary()){
113
			try {
114
				createEuroMedAreas(state);
115
			} catch (Exception e) {
116
				logger.error("Exception occurred when trying to create euroMed Areas");
117
				e.printStackTrace();
118
				state.setSuccess(false);
119
			}
120
		}
105
//		if (state.getConfig().isUseEmAreaVocabulary()){
106
//			try {
107
//				createEuroMedAreas(state);
108
//			} catch (Exception e) {
109
//				logger.error("Exception occurred when trying to create euroMed Areas");
110
//				e.printStackTrace();
111
//				state.setSuccess(false);
112
//			}
113
//		}
121 114
		super.doInvoke(state);
122 115
		//reset
123
		euroMedAreas = new HashMap<Integer, NamedArea>();
116
//		euroMedAreas = new HashMap<>();
124 117
	}
125 118

  
126
	private TermVocabulary<NamedArea> createEuroMedAreas(BerlinModelImportState state) throws SQLException {
127
		logger.warn("Start creating E+M areas");
128
		Source source = state.getConfig().getSource();
129
		Reference sourceReference = state.getConfig().getSourceReference();
130

  
131
		TransactionStatus txStatus = this.startTransaction();
132

  
133
		sourceReference = getSourceReference(sourceReference);
134

  
135
		TermVocabulary<NamedArea> euroMedAreas = makeEmptyEuroMedVocabulary();
136

  
137
		MarkerType eurMarkerType = getMarkerType(state, BerlinModelTransformer.uuidEurArea, "eur", "eur Area", "eur", getEuroMedMarkerTypeVoc());
138
		MarkerType euroMedAreaMarkerType = getMarkerType(state, BerlinModelTransformer.uuidEurMedArea, "EuroMedArea", "EuroMedArea", "EuroMedArea", getEuroMedMarkerTypeVoc());
139
		ExtensionType isoCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidIsoCode, "IsoCode", "IsoCode", "iso");
140
		ExtensionType tdwgCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidTdwgAreaCode, "TDWG code", "TDWG Area code", "tdwg");
141
		ExtensionType mclCodeExtType = getExtensionType(state, BerlinModelTransformer.uuidMclCode, "MCL code", "MedCheckList code", "mcl");
142
		NamedAreaLevel areaLevelTop = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelTop, "Euro+Med top area level", "Euro+Med top area level. This level is only to be used for the area representing the complete Euro+Med area", "e+m top", null);
143
		NamedAreaLevel areaLevelEm1 = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelFirst, "Euro+Med 1. area level", "Euro+Med 1. area level", "e+m 1.", null);
144
		NamedAreaLevel areaLevelEm2 = getNamedAreaLevel(state, BerlinModelTransformer.uuidEuroMedAreaLevelSecond, "Euro+Med 2. area level", "Euro+Med 2. area level", "Euro+Med 1. area level", null);
145

  
146

  
147
		String sql = "SELECT * , CASE WHEN EMCode = 'EM' THEN 'a' ELSE 'b' END as isEM " +
148
				" FROM emArea " +
149
				" ORDER BY isEM, EMCode";
150
		ResultSet rs = source.getResultSet(sql);
151

  
152
		NamedArea euroMedArea = null;
153
		NamedArea lastLevel1Area = null;
154

  
155
		//euroMedArea (EMCode = 'EM')
156
		rs.next();
157
		euroMedArea = makeSingleEuroMedArea(rs, eurMarkerType, euroMedAreaMarkerType, isoCodeExtType, tdwgCodeExtType, mclCodeExtType,
158
				areaLevelTop, areaLevelEm1 , areaLevelEm2, sourceReference, euroMedArea, lastLevel1Area);
159
		euroMedAreas.addTerm(euroMedArea);
160

  
161
		//all other areas
162
		while (rs.next()){
163
			NamedArea newArea = makeSingleEuroMedArea(rs, eurMarkerType, euroMedAreaMarkerType,
164
					isoCodeExtType, tdwgCodeExtType, mclCodeExtType,
165
					areaLevelTop, areaLevelEm1 , areaLevelEm2, sourceReference, euroMedArea, lastLevel1Area);
166
			if (newArea != null){
167
    			euroMedAreas.addTerm(newArea);
168
    			if (newArea.getPartOf().equals(euroMedArea)){
169
    				lastLevel1Area = newArea;
170
    			}
171
			}
172
		}
173
		emAreaFinetuning(euroMedAreas, areaLevelEm2);
174

  
175
		markAreasAsHidden(state, euroMedAreas);
176

  
177
	    getVocabularyService().saveOrUpdate(euroMedAreas);
178

  
179
		try {
180
            commitTransaction(txStatus);
181
        } catch (Exception e) {
182
             e.printStackTrace();
183
             logger.error("An exception occurred when trying to commit E+M Areas");
184
        }
185
		logger.warn("Created E+M areas");
186

  
187
		return euroMedAreas;
188
	}
189

  
190
	/**
191
     * @param areaLevelEm2
192
	 * @param euroMedAreas2
119
    /**
120
     * @param emCode
121
     * @return
193 122
     */
194
    private void emAreaFinetuning(TermVocabulary<NamedArea> euroMedAreas, NamedAreaLevel areaLevelEm2) {
195
        //CZ
196
        NamedArea oldArea = euroMedAreas.getTermByIdInvocabulary("Cz");
197
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Cs"), areaLevelEm2);
198
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sk"), areaLevelEm2);
199

  
200
        //Ju
201
        oldArea = euroMedAreas.getTermByIdInvocabulary("Ju");
202
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("BH"), areaLevelEm2);
203
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Cg"), areaLevelEm2);
204
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Ct"), areaLevelEm2);
205
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Mk"), areaLevelEm2);
206
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sl"), areaLevelEm2);
207
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sr"), areaLevelEm2);
208

  
209
        //IJ
210
        oldArea = euroMedAreas.getTermByIdInvocabulary("IJ");
211
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Ir"), areaLevelEm2);
212
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Jo"), areaLevelEm2);
213

  
214
        //LS
215
        oldArea = euroMedAreas.getTermByIdInvocabulary("LS");
216
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Le"), areaLevelEm2);
217
        makeSubterm(oldArea, euroMedAreas.getTermByIdInvocabulary("Sy"), areaLevelEm2);
218

  
219
    }
220

  
221
    //5.Mark areas to be hidden #3979 .5
222
    private void markAreasAsHidden(BerlinModelImportState state, TermVocabulary<NamedArea> euroMedAreasVoc) {
223

  
224
        try {
225

  
226
            @SuppressWarnings("unchecked")
227
            TermVocabulary<MarkerType> vocUserDefinedMarkerTypes = getVocabularyService().find(CdmImportBase.uuidUserDefinedMarkerTypeVocabulary);
228
            if (vocUserDefinedMarkerTypes == null){
229
                String message = "Marker type vocabulary could not be found. Hidden areas not added.";
230
                logger.error(message);
231
                System.out.println(message);
123
    private NamedArea getAreaByAreaId(int areaId) {
124
        NamedArea result = null;
125
        String areaIdStr = String.valueOf(areaId);
126
        OrderedTermVocabulary<NamedArea> voc = getAreaVoc();
127
        for (NamedArea area : voc.getTerms()){
128
            for (IdentifiableSource source : area.getSources()){
129
                if (areaIdStr.equals(source.getIdInSource()) && BerlinModelAreaImport.NAMESPACE.equals(source.getIdNamespace())){
130
                    if (result != null){
131
                        logger.warn("Result for areaId already exists. areaId: " + areaId);
132
                    }
133
                    result = area;
134
                }
232 135
            }
233
            MarkerType hiddenAreaMarkerType = getMarkerType(state, BerlinModelTransformer.uuidHiddenArea,
234
                    "Hidden Area","Used to hide distributions for the named areas in publications", null, getEuroMedMarkerTypeVoc());
235

  
236
            //Add hidden area marker to Rs(C) and Rs(N)
237
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs);
238
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_B);
239
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_C);
240
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_E);
241
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_N);
242
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_K);
243
            hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_W);
244
        } catch (Exception e) {
245
            e.printStackTrace();
246
            logger.error("Exception in markAreasAsHidden: " + e.getMessage());
247 136
        }
248

  
137
        return result;
249 138
    }
250 139

  
251
    private void hideArea(TermVocabulary<NamedArea> euroMedAreasVoc, MarkerType hiddenAreaMarkerType, UUID areaUuid) {
252
        for (NamedArea namedArea : euroMedAreasVoc){
253
            if (namedArea.getUuid().equals(areaUuid)){
254
                namedArea.addMarker(Marker.NewInstance(hiddenAreaMarkerType, true));
255
                return;
256
            }
140
    private OrderedTermVocabulary<NamedArea> areaVoc;
141
    @SuppressWarnings("unchecked")
142
    private OrderedTermVocabulary<NamedArea> getAreaVoc(){
143
        if (areaVoc == null){
144
            areaVoc = (OrderedTermVocabulary<NamedArea>)getVocabularyService().find(BerlinModelTransformer.uuidVocEuroMedAreas);
257 145
        }
146
        return areaVoc;
258 147
    }
259 148

  
260
    /**
261
     * @param oldArea
262
     * @param namedArea
263
     * @param areaLevelEm2
264
     */
265
    private void makeSubterm(NamedArea oldArea, NamedArea namedArea, NamedAreaLevel areaLevelEm2) {
266
        namedArea.setLevel(areaLevelEm2);
267
        namedArea.setPartOf(oldArea);
268
    }
269

  
270
    /**
271
	 * @param sourceReference
272
	 * @return
273
	 */
274
	private Reference getSourceReference(Reference sourceReference) {
275
		Reference persistentSourceReference = getReferenceService().find(sourceReference.getUuid());  //just to be sure
276
		if (persistentSourceReference != null){
277
			sourceReference = persistentSourceReference;
278
		}
279
		return sourceReference;
280
	}
281

  
282
	/**
283
	 * @param eurMarkerType
284
	 * @param euroMedAreaMarkerType
285
	 * @param isoCodeExtType
286
	 * @param tdwgCodeExtType
287
	 * @param mclCodeExtType
288
	 * @param rs
289
	 * @param areaLevelEm2
290
	 * @param areaLevelEm1
291
	 * @param areaLevelTop
292
	 * @throws SQLException
293
	 */
294
	private NamedArea makeSingleEuroMedArea(ResultSet rs, MarkerType eurMarkerType,
295
			MarkerType euroMedAreaMarkerType, ExtensionType isoCodeExtType,
296
			ExtensionType tdwgCodeExtType, ExtensionType mclCodeExtType,
297
			NamedAreaLevel areaLevelTop, NamedAreaLevel areaLevelEm1, NamedAreaLevel areaLevelEm2,
298
			Reference sourceReference, NamedArea euroMedArea, NamedArea level1Area) throws SQLException {
299
		Integer areaId = rs.getInt("AreaId");
300
		String emCode = nullSafeTrim(rs.getString("EMCode"));
301
		String isoCode = nullSafeTrim(rs.getString("ISOCode"));
302
		String tdwgCode = nullSafeTrim(rs.getString("TDWGCode"));
303
		String unit = nullSafeTrim(rs.getString("Unit"));
304
//				      ,[Status]
305
//				      ,[OutputOrder]
306
		boolean eurMarker = rs.getBoolean("eur");
307
		boolean euroMedAreaMarker = rs.getBoolean("EuroMedArea");
308
		String notes = nullSafeTrim(rs.getString("Notes"));
309
		String mclCode = nullSafeTrim(rs.getString("MCLCode"));
310
		String geoSearch = nullSafeTrim(rs.getString("NameForGeoSearch"));
311

  
312

  
313

  
314
		if (isBlank(emCode)){
315
			emCode = unit;
316
		}
317

  
318
		//uuid
319
		UUID uuid = BerlinModelTransformer.getEMAreaUuid(emCode);
320
		NamedArea area = (NamedArea)getTermService().find(uuid);
321
		if (area == null){
322
			//label
323
			area = NamedArea.NewInstance(geoSearch, unit, emCode);
324
			if (uuid != null){
325
				area.setUuid(uuid);
326
			}else{
327
			    if (areaId == 211 || areaId == 213){  //Additional Azores and Canary Is. area are merged into primary area, see also area.addSource part below
328
			        return null;
329
			    }
330
				logger.warn("Uuid for emCode could not be defined: " + emCode);
331
			}
332
		}
333

  
334

  
335
		//code
336
		area.setIdInVocabulary(emCode);
337
		//notes
338
		if (StringUtils.isNotEmpty(notes)){
339
			area.addAnnotation(Annotation.NewInstance(notes, AnnotationType.EDITORIAL(), Language.DEFAULT()));
340
		}
341
		//markers
342
		area.addMarker(Marker.NewInstance(eurMarkerType, eurMarker));
343
		area.addMarker(Marker.NewInstance(euroMedAreaMarkerType, euroMedAreaMarker));
344

  
345
		//extensions
346
		if (isNotBlank(isoCode)){
347
			area.addExtension(isoCode, isoCodeExtType);
348
		}
349
		if (isNotBlank(tdwgCode)){
350
			area.addExtension(tdwgCode, tdwgCodeExtType);
351
		}
352
		if (isNotBlank(mclCode)){
353
			area.addExtension(mclCode, mclCodeExtType);
354
		}
355

  
356
		//type
357
		area.setType(NamedAreaType.ADMINISTRATION_AREA());
358

  
359
		//source
360
		area.addSource(OriginalSourceType.Import, String.valueOf(areaId), EM_AREA_NAMESPACE, sourceReference, null);
361
		//add duplicate area ids for canary
362
		if (areaId == 624){ //Canary Is.
363
		    area.addSource(OriginalSourceType.Import, String.valueOf(213), EM_AREA_NAMESPACE, sourceReference, null);
364
		}
365
		if (areaId == 210){//Azores
366
            area.addSource(OriginalSourceType.Import, String.valueOf(211), EM_AREA_NAMESPACE, sourceReference, null);
367
        }
368

  
369
		//parent
370
		if (euroMedArea != null){
371
			if (emCode.contains("(")){
372
				area.setPartOf(level1Area);
373
				area.setLevel(areaLevelEm2);
374
			}else{
375
				area.setPartOf(euroMedArea);
376
				area.setLevel(areaLevelEm1);
377
			}
378
		}else{
379
			area.setLevel(areaLevelTop);
380
		}
381
		this.euroMedAreas.put(areaId, area);
382

  
383
		//save
384
		getTermService().saveOrUpdate(area);
385

  
386
		return area;
387
	}
388 149

  
389 150
	private String nullSafeTrim(String string) {
390 151
		if (string == null){
......
394 155
		}
395 156
	}
396 157

  
397
	/**
398
	 *
399
	 */
400
	private TermVocabulary<NamedArea> makeEmptyEuroMedVocabulary() {
401
		TermType type = TermType.NamedArea;
402
		String description = "Euro+Med area vocabulary";
403
		String label = "E+M areas";
404
		String abbrev = null;
405
		URI termSourceUri = null;
406
		OrderedTermVocabulary<NamedArea> result = OrderedTermVocabulary.NewInstance(type, description, label, abbrev, termSourceUri);
407

  
408
		result.setUuid(BerlinModelTransformer.uuidVocEuroMedAreas);
409
		getVocabularyService().save(result);
410
		return result;
411
	}
412

  
413 158
	@Override
414 159
	public boolean doPartition(ResultSetPartitioner partitioner, BerlinModelImportState state) {
415 160
		boolean success = true;
416
		Set<TaxonBase> taxaToSave = new HashSet<TaxonBase>();
161
		Set<TaxonBase> taxaToSave = new HashSet<>();
417 162

  
418 163
		Map<String, TaxonBase<?>> taxonMap = partitioner.getObjectMap(BerlinModelTaxonImport.NAMESPACE);
419 164

  
......
422 167
		try {
423 168
			//map to store the mapping of duplicate berlin model occurrences to their real distributions
424 169
			//duplicated may occur due to area mappings from BM areas to TDWG areas
425
			Map<Integer, String> duplicateMap = new HashMap<Integer, String>();
170
			Map<Integer, String> duplicateMap = new HashMap<>();
426 171
			int oldTaxonId = -1;
427 172
			TaxonDescription oldDescription = null;
428 173
			int i = 0;
......
453 198

  
454 199
					Reference sourceRef = state.getTransactionalSourceReference();
455 200

  
456
					List<NamedArea> areas = makeAreaList(state, rs,	occurrenceId);
201
					List<NamedArea> areas = makeAreaList(state, partitioner, rs, occurrenceId);
457 202

  
458 203
                    //create description(elements)
459 204
                    TaxonDescription taxonDescription = getTaxonDescription(newTaxonId, oldTaxonId, oldDescription, taxonMap, occurrenceId, sourceRef);
......
513 258

  
514 259
	/**
515 260
	 * @param state
261
	 * @param partitioner
516 262
	 * @param rs
517 263
	 * @param occurrenceId
518 264
	 * @param tdwgCodeString
......
521 267
	 * @throws SQLException
522 268
	 */
523 269
	//Create area list
524
	private List<NamedArea> makeAreaList(BerlinModelImportState state, ResultSet rs, int occurrenceId) throws SQLException {
525
		List<NamedArea> areas = new ArrayList<NamedArea>();
270
	private List<NamedArea> makeAreaList(BerlinModelImportState state, ResultSetPartitioner partitioner, ResultSet rs, int occurrenceId) throws SQLException {
271

  
272
	    List<NamedArea> areas = new ArrayList<>();
526 273

  
527 274
		if (state.getConfig().isUseEmAreaVocabulary()){
528
			Integer areaId = rs.getInt("AreaId");
529
	        NamedArea area = this.euroMedAreas.get(areaId);
275
		    Integer areaId = rs.getInt("AreaId");
276
			NamedArea area = getAreaByAreaId(areaId);
277
			if (area == null){
278
			    logger.warn("Area for areaId " + areaId + " not found.");
279
			}
530 280
			areas.add(area);
531 281
		}else{
532 282
	        String tdwgCodeString = rs.getString("TDWGCode");
......
566 316

  
567 317
	@Override
568 318
	public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition(ResultSet rs, BerlinModelImportState state) {
569
		String nameSpace;
570
		Class<?> cdmClass;
571
		Set<String> idSet;
572
		Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<Object, Map<String, ? extends CdmBase>>();
573 319

  
574 320
		try{
321

  
322
		    Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<>();
575 323
			Set<String> taxonIdSet = new HashSet<String>();
576 324
			while (rs.next()){
577 325
				handleForeignKey(rs, taxonIdSet, "taxonId");
578 326
			}
579 327

  
580 328
			//taxon map
581
			nameSpace = BerlinModelTaxonImport.NAMESPACE;
582
			cdmClass = TaxonBase.class;
583
			idSet = taxonIdSet;
584
			Map<String, TaxonBase> objectMap = (Map<String, TaxonBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
329
			String nameSpace = BerlinModelTaxonImport.NAMESPACE;
330
			Class<?> cdmClass = TaxonBase.class;
331
			Set<String> idSet = taxonIdSet;
332
			Map<String, ? extends CdmBase> objectMap = (Map<String, TaxonBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
585 333
			result.put(nameSpace, objectMap);
586 334

  
335
			return result;
587 336
		} catch (SQLException e) {
588 337
			throw new RuntimeException(e);
589 338
		}
590
		return result;
591 339
	}
592 340

  
593 341

  
594

  
595 342
	/**
596 343
     * Tests if a distribution with the same tdwgArea and the same status already exists in the description.
597 344
     * If so the old distribution is returned
app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/validation/BerlinModelAreaImportValidator.java
1
/**
2
* Copyright (C) 2007 EDIT
3
* European Distributed Institute of Taxonomy
4
* http://www.e-taxonomy.eu
5
*
6
* The contents of this file are subject to the Mozilla Public License Version 1.1
7
* See LICENSE.TXT at the top of this package for the full license terms.
8
*/
9

  
10
package eu.etaxonomy.cdm.io.berlinModel.in.validation;
11

  
12
import org.apache.log4j.Logger;
13

  
14
import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportState;
15
import eu.etaxonomy.cdm.io.common.IOValidator;
16

  
17
/**
18
 * @author a.mueller
19
 * @since 17.02.2010
20
 */
21
public class BerlinModelAreaImportValidator implements IOValidator<BerlinModelImportState> {
22
	@SuppressWarnings("unused")
23
    private static final Logger logger = Logger.getLogger(BerlinModelAreaImportValidator.class);
24

  
25
	@Override
26
	public boolean validate(BerlinModelImportState state) {
27
		boolean result = true;
28
//		BerlinModelImportConfigurator config = state.getConfig();
29
//		result &= checkSourcesWithWhitespace(config);
30
		return result;
31
	}
32

  
33

  
34
	//******************************** CHECK *************************************************
35

  
36

  
37
//    private static boolean checkSourcesWithWhitespace(BerlinModelImportConfigurator config){
38
//        try {
39
//            boolean result = true;
40
//            Source source = config.getSource();
41
//            String strSelect = "SELECT OccurrenceId, PTNameFk, PTRefFk, AreaFk, Sources, Created_When, Created_Who, Updated_When, Updated_Who, Notes, Occurrence ";
42
//            String strCount = " SELECT count(*) as n";
43
//            String strQueryBase =
44
//                    " FROM emOccurrence " +
45
//                    " WHERE (Sources LIKE '%  %') OR (Sources LIKE '% ') OR (Sources LIKE ' %') ";
46
//
47
//            ResultSet rs = source.getResultSet(strCount + strQueryBase);
48
//            rs.next();
49
//            int n = rs.getInt("n");
50
//            if (n > 0){
51
//                System.out.println("=======================================================================");
52
//                System.out.println("There are "+n+" occurrences with source attribute has unexpected whitespace!");
53
//                System.out.println("---------------------------------------------------------------");
54
//                System.out.println(strSelect + strQueryBase);
55
//                System.out.println("=======================================================================");
56
//            }
57
//
58
//            rs = source.getResultSet(strSelect + strQueryBase);
59
//            while (rs.next()){
60
//                int occurrenceId = rs.getInt("OccurrenceId");
61
//                String sources = rs.getString("Sources");
62
//
63
//                System.out.println("OccurrenceSourceId:" + occurrenceId +
64
//                        "\n  Sources: " + sources)
65
//                        ;
66
//            }
67
//            return result;
68
//        } catch (SQLException e) {
69
//            e.printStackTrace();
70
//            return false;
71
//        }
72
//    }
73
}

Also available in: Unified diff