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.IOValidator;
  27 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  28 import eu.etaxonomy.cdm.io.common.Source;
  29 import eu.etaxonomy.cdm.model.common.Annotation;
  30 import eu.etaxonomy.cdm.model.common.AnnotationType;
  31 import eu.etaxonomy.cdm.model.common.CdmBase;
  32 import eu.etaxonomy.cdm.model.common.ExtensionType;
  33 import eu.etaxonomy.cdm.model.common.Language;
  34 import eu.etaxonomy.cdm.model.common.Marker;
  35 import eu.etaxonomy.cdm.model.common.MarkerType;
  36 import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
  37 import eu.etaxonomy.cdm.model.common.OriginalSourceType;
  38 import eu.etaxonomy.cdm.model.common.TermType;
  39 import eu.etaxonomy.cdm.model.common.TermVocabulary;
  40 import eu.etaxonomy.cdm.model.location.NamedArea;
  41 import eu.etaxonomy.cdm.model.location.NamedAreaLevel;
  42 import eu.etaxonomy.cdm.model.location.NamedAreaType;
  43 import eu.etaxonomy.cdm.model.reference.Reference;
  44
  45
  46 /**
  47  * @author a.mueller
  48  * @since 20.03.2008
  49  */
  50 @Component
  51 public class BerlinModelAreaImport  extends BerlinModelImportBase {
  52
  53     private static final long serialVersionUID = -2810052908318645219L;
  54     private static final Logger logger = Logger.getLogger(BerlinModelAreaImport.class);
  55
  56         public static final String NAMESPACE = "emArea";
  57
  58         private static final String pluralString = "areas";
  59         private static final String dbTableName = "emArea";  //??
  60
  61         public BerlinModelAreaImport(){
  62                 super(dbTableName, pluralString);
  63         }
  64
  65         @Override
  66         protected String getIdQuery(BerlinModelImportState state) {
  67                 String result =
  68                           " SELECT AreaId "
  69                         + " FROM " + getTableName();
  70                 if (state.getConfig().isEuroMed()){
  71                     result += " WHERE AreaID NOT IN (1, 21, 650, 653, 1718, 654, 646, 647) ";  //#3986
  72                 }
  73                 return result;
  74         }
  75
  76         @Override
  77         protected String getRecordQuery(BerlinModelImportConfigurator config) {
  78                 String strQuery =
  79                           " SELECT * "
  80                         + " FROM emArea a "
  81                 + " WHERE (a.AreaId IN (" + ID_LIST_TOKEN + ")  )"
  82                         + " ORDER BY a.AreaId "
  83                 ;
  84                 return strQuery;
  85         }
  86
  87         private Map<Integer, NamedArea> euroMedAreas = new HashMap<>();
  88
  89
  90         private TermVocabulary<NamedArea> createEuroMedAreas(BerlinModelImportState state) throws SQLException {
  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             MarkerType hiddenAreaMarkerType = getMarkerType(state, BerlinModelTransformer.uuidHiddenArea,
 192                     "Hidden Area","Used to hide distributions for the named areas in publications", null, getEuroMedMarkerTypeVoc());
 193
 194             //Add hidden area marker to Rs(C) and Rs(N)
 195             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs);
 196             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_B);
 197             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_C);
 198             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_E);
 199             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_N);
 200             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_K);
 201             hideArea(euroMedAreasVoc, hiddenAreaMarkerType, BerlinModelTransformer.uuidRs_W);
 202         } catch (Exception e) {
 203             e.printStackTrace();
 204             logger.error("Exception in markAreasAsHidden: " + e.getMessage());
 205         }
 206
 207     }
 208
 209     private void hideArea(TermVocabulary<NamedArea> euroMedAreasVoc, MarkerType hiddenAreaMarkerType, UUID areaUuid) {
 210         for (NamedArea namedArea : euroMedAreasVoc){
 211             if (namedArea.getUuid().equals(areaUuid)){
 212                 namedArea.addMarker(Marker.NewInstance(hiddenAreaMarkerType, true));
 213                 return;
 214             }
 215         }
 216     }
 217
 218     /**
 219      * @param oldArea
 220      * @param namedArea
 221      * @param areaLevelEm2
 222      */
 223     private void makeSubterm(NamedArea oldArea, NamedArea namedArea, NamedAreaLevel areaLevelEm2) {
 224         namedArea.setLevel(areaLevelEm2);
 225         namedArea.setPartOf(oldArea);
 226     }
 227
 228
 229         /**
 230          * @param eurMarkerType
 231          * @param euroMedAreaMarkerType
 232          * @param isoCodeExtType
 233          * @param tdwgCodeExtType
 234          * @param mclCodeExtType
 235          * @param rs
 236          * @param areaLevelEm2
 237          * @param areaLevelEm1
 238          * @param areaLevelTop
 239          * @throws SQLException
 240          */
 241         private NamedArea makeSingleEuroMedArea(ResultSet rs, MarkerType eurMarkerType,
 242                         MarkerType euroMedAreaMarkerType, ExtensionType isoCodeExtType,
 243                         ExtensionType tdwgCodeExtType, ExtensionType mclCodeExtType,
 244                         NamedAreaLevel areaLevelTop, NamedAreaLevel areaLevelEm1, NamedAreaLevel areaLevelEm2,
 245                         Reference sourceReference, NamedArea euroMedArea, NamedArea level1Area) throws SQLException {
 246
 247             Integer areaId = rs.getInt("AreaId");
 248                 String emCode = nullSafeTrim(rs.getString("EMCode"));
 249                 String isoCode = nullSafeTrim(rs.getString("ISOCode"));
 250                 String tdwgCode = nullSafeTrim(rs.getString("TDWGCode"));
 251                 String unit = nullSafeTrim(rs.getString("Unit"));
 252 //                                    ,[Status]
 253 //                                    ,[OutputOrder]
 254                 boolean eurMarker = rs.getBoolean("eur");
 255                 boolean euroMedAreaMarker = rs.getBoolean("EuroMedArea");
 256                 String notes = nullSafeTrim(rs.getString("Notes"));
 257                 String mclCode = nullSafeTrim(rs.getString("MCLCode"));
 258                 String geoSearch = nullSafeTrim(rs.getString("NameForGeoSearch"));
 259
 260
 261                 if (isBlank(emCode)){
 262                         emCode = unit;
 263                 }
 264
 265                 //uuid
 266                 UUID uuid = BerlinModelTransformer.getEMAreaUuid(emCode);
 267                 NamedArea area = (NamedArea)getTermService().find(uuid);
 268                 if (area == null){
 269                         //label
 270                         area = NamedArea.NewInstance(geoSearch, unit, emCode);
 271                         if (uuid != null){
 272                                 area.setUuid(uuid);
 273                         }else{
 274                             if (areaId == 211 || areaId == 213){  //Additional Azores and Canary Is. area are merged into primary area, see also area.addSource part below
 275                                 return null;
 276                             }
 277                                 logger.warn("Uuid for emCode could not be defined: " + emCode);
 278                         }
 279                 }
 280
 281
 282                 //code
 283                 area.setIdInVocabulary(emCode);
 284                 //notes
 285                 if (StringUtils.isNotEmpty(notes)){
 286                         area.addAnnotation(Annotation.NewInstance(notes, AnnotationType.EDITORIAL(), Language.DEFAULT()));
 287                 }
 288                 //markers
 289                 area.addMarker(Marker.NewInstance(eurMarkerType, eurMarker));
 290                 area.addMarker(Marker.NewInstance(euroMedAreaMarkerType, euroMedAreaMarker));
 291
 292                 //extensions
 293                 if (isNotBlank(isoCode)){
 294                         area.addExtension(isoCode, isoCodeExtType);
 295                 }
 296                 if (isNotBlank(tdwgCode)){
 297                         area.addExtension(tdwgCode, tdwgCodeExtType);
 298                 }
 299                 if (isNotBlank(mclCode)){
 300                         area.addExtension(mclCode, mclCodeExtType);
 301                 }
 302
 303                 //type
 304                 area.setType(NamedAreaType.ADMINISTRATION_AREA());
 305
 306                 //source
 307                 area.addSource(OriginalSourceType.Import, String.valueOf(areaId), NAMESPACE, sourceReference, null);
 308                 //add duplicate area ids for canary
 309                 if (areaId == 624){ //Canary Is.
 310                     area.addSource(OriginalSourceType.Import, String.valueOf(213), NAMESPACE, sourceReference, null);
 311                 }
 312                 if (areaId == 210){//Azores
 313             area.addSource(OriginalSourceType.Import, String.valueOf(211), NAMESPACE, sourceReference, null);
 314         }
 315
 316                 //parent
 317                 if (euroMedArea != null){
 318                         if (emCode.contains("(")){
 319                                 area.setPartOf(level1Area);
 320                                 area.setLevel(areaLevelEm2);
 321                         }else{
 322                                 area.setPartOf(euroMedArea);
 323                                 area.setLevel(areaLevelEm1);
 324                         }
 325                 }else{
 326                         area.setLevel(areaLevelTop);
 327                 }
 328                 this.euroMedAreas.put(areaId, area);
 329
 330                 //save
 331                 getTermService().saveOrUpdate(area);
 332
 333                 return area;
 334         }
 335
 336         private String nullSafeTrim(String string) {
 337                 if (string == null){
 338                         return null;
 339                 }else{
 340                         return string.trim();
 341                 }
 342         }
 343
 344         /**
 345          *
 346          */
 347         private TermVocabulary<NamedArea> makeEmptyEuroMedVocabulary() {
 348                 TermType type = TermType.NamedArea;
 349                 String description = "Euro+Med area vocabulary";
 350                 String label = "E+M areas";
 351                 String abbrev = null;
 352                 URI termSourceUri = null;
 353                 OrderedTermVocabulary<NamedArea> result = OrderedTermVocabulary.NewInstance(type, description, label, abbrev, termSourceUri);
 354
 355                 result.setUuid(BerlinModelTransformer.uuidVocEuroMedAreas);
 356                 getVocabularyService().save(result);
 357                 return result;
 358         }
 359
 360         @Override
 361         public boolean doPartition(@SuppressWarnings("rawtypes") ResultSetPartitioner partitioner, BerlinModelImportState state)  {
 362             TermVocabulary<?> voc = getVocabularyService().find(BerlinModelTransformer.uuidVocEuroMedAreas);
 363             if (voc == null){
 364                 try {
 365                     createEuroMedAreas(state);
 366                 } catch (SQLException e) {
 367                     logger.warn("Exception when creating areas: " + e.getMessage());
 368                     e.printStackTrace();
 369                 }
 370         }
 371             return true;
 372         }
 373
 374
 375         @Override
 376         public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition(ResultSet rs, BerlinModelImportState state) {
 377
 378                 Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<>();
 379                 return result;
 380         }
 381
 382         @Override
 383         protected boolean doCheck(BerlinModelImportState state){
 384                 IOValidator<BerlinModelImportState> validator = new BerlinModelAreaImportValidator();
 385                 return validator.validate(state);
 386         }
 387
 388
 389         @Override
 390         protected boolean isIgnore(BerlinModelImportState state){
 391                 if (! (state.getConfig().isDoOccurrence() || state.getConfig().isDoCommonNames())){
 392                         return true;
 393                 }else{
 394                         if (!this.checkSqlServerColumnExists(state.getConfig().getSource(), "emArea", "AreaId")){
 395                                 logger.error("emArea table or AreaId column do not exist. Must ignore area import");
 396                                 return true;
 397                         }else{
 398                             TermVocabulary<?> voc = getVocabularyService().find(BerlinModelTransformer.uuidVocEuroMedAreas);
 399                         return voc != null;
 400                         }
 401                 }
 402         }
 403
 404 }