app-import/src/main/java/eu/etaxonomy/cdm/io/algaterra/AlgaTerraSpecimenImportBase.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.algaterra;
  11
  12 import java.net.URI;
  13 import java.sql.Date;
  14 import java.sql.ResultSet;
  15 import java.sql.SQLException;
  16 import java.util.Set;
  17 import java.util.UUID;
  18
  19 import org.apache.commons.lang.StringUtils;
  20 import org.apache.log4j.Logger;
  21 import org.springframework.transaction.TransactionStatus;
  22
  23 import eu.etaxonomy.cdm.api.facade.DerivedUnitFacade;
  24 import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase;
  25 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  26 import eu.etaxonomy.cdm.io.common.Source;
  27 import eu.etaxonomy.cdm.model.agent.Team;
  28 import eu.etaxonomy.cdm.model.agent.TeamOrPersonBase;
  29 import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
  30 import eu.etaxonomy.cdm.model.common.TimePeriod;
  31 import eu.etaxonomy.cdm.model.description.DescriptionBase;
  32 import eu.etaxonomy.cdm.model.description.Feature;
  33 import eu.etaxonomy.cdm.model.description.SpecimenDescription;
  34 import eu.etaxonomy.cdm.model.description.State;
  35 import eu.etaxonomy.cdm.model.description.TaxonDescription;
  36 import eu.etaxonomy.cdm.model.location.NamedArea;
  37 import eu.etaxonomy.cdm.model.location.Point;
  38 import eu.etaxonomy.cdm.model.location.ReferenceSystem;
  39 import eu.etaxonomy.cdm.model.location.TdwgArea;
  40 import eu.etaxonomy.cdm.model.location.WaterbodyOrCountry;
  41 import eu.etaxonomy.cdm.model.occurrence.Collection;
  42 import eu.etaxonomy.cdm.model.reference.Reference;
  43 import eu.etaxonomy.cdm.model.taxon.Taxon;
  44
  45 /**
  46  * @author a.mueller
  47  * @created 12.09.2012
  48  */
  49 public abstract class AlgaTerraSpecimenImportBase extends BerlinModelImportBase{
  50         private static final Logger logger = Logger.getLogger(AlgaTerraSpecimenImportBase.class);
  51
  52         public static final String ECO_FACT_FIELD_OBSERVATION_NAMESPACE = "EcoFact_FieldObservation";
  53         public static final String ECO_FACT_DERIVED_UNIT_NAMESPACE = "EcoFact_DerivedUnit";
  54         public static final String TYPE_SPECIMEN_FIELD_OBSERVATION_NAMESPACE = "TypeSpecimen_FieldObservation";
  55         public static final String TYPE_SPECIMEN_DERIVED_UNIT_NAMESPACE = "TypeSpecimen_DerivedUnit";
  56         public static final String FACT_ECOLOGY_NAMESPACE = "Fact(Ecology)";
  57
  58
  59         public static final String TERMS_NAMESPACE = "ALGA_TERRA_TERMS";
  60
  61         //TODO move to transformrer
  62         final static UUID uuidMarkerAlkalinity = UUID.fromString("e52d0ea2-0c1f-4d95-ae6d-e21ab317c594");
  63         final static UUID uuidRefSystemGps = UUID.fromString("c23e4928-c137-4e4a-b6ab-b430da3d0b94");
  64         final static UUID uuidFeatureSpecimenCommunity = UUID.fromString("3ff5b1ab-3999-4b5a-b8f7-01fd2f6c12c7");
  65         final static UUID uuidFeatureAdditionalData = UUID.fromString("0ac82ab8-2c2b-4953-98eb-a9f718eb9c57");
  66         final static UUID uuidFeatureHabitatExplanation = UUID.fromString("6fe32295-61a3-44fc-9fcf-a85790ea888f");
  67
  68         final static UUID uuidVocAlgaTerraClimate = UUID.fromString("b0a677c6-8bb6-43f4-b1b8-fc377a10feb5");
  69         final static UUID uuidVocAlgaTerraHabitat = UUID.fromString("06f30114-e19c-4e7d-a8e5-5488c41fcbc5");
  70         final static UUID uuidVocAlgaTerraLifeForm = UUID.fromString("3c0b194e-809c-4b42-9498-6ff034066ed7");
  71
  72         final static UUID uuidFeatureAlgaTerraClimate = UUID.fromString("8754674c-9ab9-4f28-95f1-91eeee2314ee");
  73         final static UUID uuidFeatureAlgaTerraHabitat = UUID.fromString("7def3fc2-cdc5-4739-8e13-62edbd053415");
  74         final static UUID uuidFeatureAlgaTerraLifeForm = UUID.fromString("9b657901-1b0d-4a2a-8d21-dd8c1413e2e6");
  75
  76         final static UUID uuidVocParameter = UUID.fromString("45888b40-5bbb-4293-aa1e-02479796cd7c");
  77         final static UUID uuidStatMeasureSingleValue = UUID.fromString("eb4c3d98-4d4b-4c37-8eb4-17315ce79920");
  78         final static UUID uuidMeasurementValueModifier = UUID.fromString("0218a7a3-f6c0-4d06-a4f8-6b50b73aef5e");
  79
  80         final static UUID uuidModifierLowerThan = UUID.fromString("2b500085-6bef-4003-b6ea-e0ad0237d79d");
  81         final static UUID uuidModifierGreaterThan = UUID.fromString("828df49d-c745-48f7-b083-0ada43356c34");
  82
  83         public AlgaTerraSpecimenImportBase(String tableName, String pluralString) {
  84                 super(tableName, pluralString);
  85         }
  86
  87         /**
  88          * Creates the vocabularies and the features for Climate, Habitat and Lifeform
  89          * @param state
  90          * @throws SQLException
  91          */
  92         protected void makeVocabulariesAndFeatures(AlgaTerraImportState state) throws SQLException {
  93                 String abbrevLabel = null;
  94                 URI uri = null;
  95
  96                 if (! state.isSpecimenVocabulariesCreated()){
  97
  98                         TransactionStatus txStatus = this.startTransaction();
  99
 100                         boolean isOrdered = true;
 101                         OrderedTermVocabulary<State> climateVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraClimate, "Climate", "Climate", abbrevLabel, uri, isOrdered, null);
 102                         OrderedTermVocabulary<State> habitatVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraHabitat, "Habitat", "Habitat", abbrevLabel, uri, isOrdered, null);
 103                         OrderedTermVocabulary<State> lifeformVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraLifeForm, "Lifeform", "Lifeform", abbrevLabel, uri, isOrdered, null);
 104
 105
 106                         Feature feature = getFeature(state, uuidFeatureAlgaTerraClimate, "Climate","Climate", null, null);
 107                         feature.setSupportsCategoricalData(true);
 108
 109                         feature = getFeature(state, uuidFeatureAlgaTerraLifeForm, "LifeForm","LifeForm", null, null);
 110                         feature.setSupportsCategoricalData(true);
 111
 112                         feature = Feature.HABITAT();
 113                         feature.setSupportsCategoricalData(true);
 114                         getTermService().saveOrUpdate(feature);
 115
 116                         Source source = state.getAlgaTerraConfigurator().getSource();
 117
 118                         String climateSql = "SELECT * FROM EcoClimate";
 119                         ResultSet rs = source.getResultSet(climateSql);
 120                         while (rs.next()){
 121                                 String climate = rs.getString("Climate");
 122                                 String description = rs.getString("Description");
 123                                 Integer id = rs.getInt("ClimateId");
 124                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 125                                 State stateTerm = getStateTerm(state, uuid, climate, description, null, climateVoc);
 126                                 addOriginalSource(stateTerm, id.toString(), "EcoClimate", state.getTransactionalSourceReference());
 127                                 getTermService().saveOrUpdate(stateTerm);
 128                         }
 129
 130                         String habitatSql = "SELECT * FROM EcoHabitat";
 131                         rs = source.getResultSet(habitatSql);
 132                         while (rs.next()){
 133                                 String habitat = rs.getString("Habitat");
 134                                 String description = rs.getString("Description");
 135                                 Integer id = rs.getInt("HabitatId");
 136                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 137                                 State stateTerm = getStateTerm(state, uuid, habitat, description, null, habitatVoc);
 138                                 addOriginalSource(stateTerm, id.toString(), "EcoHabitat", state.getTransactionalSourceReference());
 139                                 getTermService().saveOrUpdate(stateTerm);
 140                         }
 141
 142                         String lifeformSql = "SELECT * FROM EcoLifeForm";
 143                         rs = source.getResultSet(lifeformSql);
 144                         while (rs.next()){
 145                                 String lifeform = rs.getString("LifeForm");
 146                                 String description = rs.getString("Description");
 147                                 Integer id = rs.getInt("LifeFormId");
 148                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 149                                 State stateTerm = getStateTerm(state, uuid, lifeform, description, null, lifeformVoc);
 150                                 addOriginalSource(stateTerm, id.toString(), "EcoLifeForm", state.getTransactionalSourceReference());
 151                                 getTermService().saveOrUpdate(stateTerm);
 152                         }
 153
 154                         this.commitTransaction(txStatus);
 155
 156                         state.setSpecimenVocabulariesCreated(true);
 157                 }
 158
 159         }
 160
 161         protected String getLocalityString(){
 162                 return "Locality";
 163         }
 164
 165         protected void handleFieldObservationSpecimen(ResultSet rs, DerivedUnitFacade facade, AlgaTerraImportState state, ResultSetPartitioner partitioner) throws SQLException {
 166                 //FIXME missing fields #3084, #3085, #3080
 167                 try {
 168
 169                         Integer unitId = nullSafeInt(rs, "unitId");
 170                         String locality = rs.getString(getLocalityString());
 171                         Double latitude = nullSafeDouble(rs, "Latitude");
 172                         Double longitude = nullSafeDouble(rs, "Longitude");
 173                         Integer errorRadius = nullSafeInt(rs,"Prec");
 174                         String geoCodeMethod = rs.getString("GeoCodeMethod");
 175
 176                         Integer altitude = nullSafeInt(rs, "Altitude");
 177                         Integer lowerAltitude = nullSafeInt(rs,"AltitudeLowerValue");
 178                         String altitudeUnit = rs.getString("AltitudeUnit");
 179                         Double depth = nullSafeDouble(rs, "Depth");
 180                         Double depthLow = nullSafeDouble(rs, "DepthLow");
 181
 182                         String collectorsNumber = rs.getString("CollectorsNumber");
 183                         Date collectionDateStart = rs.getDate("CollectionDate");
 184                         Date collectionDateEnd = rs.getDate("CollectionDateEnd");
 185
 186                         //location
 187                         facade.setLocality(locality);
 188
 189                         //exact location
 190                         ReferenceSystem referenceSystem = makeRefrenceSystem(geoCodeMethod, state);
 191                         if (longitude != null || latitude != null || referenceSystem != null || errorRadius != null){
 192                                 Point exactLocation = Point.NewInstance(longitude, latitude, referenceSystem, errorRadius);
 193                                 facade.setExactLocation(exactLocation);
 194                         }
 195
 196                         //altitude, depth
 197                         if (StringUtils.isNotBlank(altitudeUnit) && ! altitudeUnit.trim().equalsIgnoreCase("m")){
 198                                 logger.warn("Altitude unit is not [m] but: " +  altitudeUnit);
 199                         }
 200                         if ( altitude != null){
 201                                 if (lowerAltitude == null){
 202                                         facade.setAbsoluteElevation(altitude);
 203                                 }else{
 204                                         if (! facade.isEvenDistance(lowerAltitude, altitude)){
 205                                                 //FIXME there is a ticket for this
 206                                                 altitude = altitude + 1;
 207                                                 logger.info("Current implementation of altitude does not allow uneven distances");
 208                                         }
 209                                         facade.setAbsoluteElevationRange(lowerAltitude,altitude);
 210                                 }
 211                         }
 212                         if ( depth != null){
 213                                 //FIXME needs model change to accept double #3072
 214                                 Integer intDepth = depth.intValue();
 215                                 if (depthLow == null){
 216                                         facade.setDistanceToWaterSurface(intDepth);
 217                                 }else{
 218                                         //FIXME range not yet in model #3074
 219                                         facade.setDistanceToWaterSurface(intDepth);
 220                                 }
 221                         }
 222
 223                         //field
 224                         facade.setFieldNumber(collectorsNumber);
 225                         TimePeriod gatheringPeriod = TimePeriod.NewInstance(collectionDateStart, collectionDateEnd);
 226                         facade.setGatheringPeriod(gatheringPeriod);
 227                         handleCollectorTeam(state, facade, rs);
 228
 229                         //areas
 230                         makeAreas(state, rs, facade);
 231
 232                         //notes
 233                         //TODO is this an annotation on field observation or on the derived unit?
 234
 235                         //id, created, updated, notes
 236                         if (unitId != null){
 237                                 this.doIdCreatedUpdatedNotes(state, facade.innerFieldObservation(), rs, unitId, getFieldObservationNameSpace());
 238                         }else{
 239                                 logger.warn("FieldObservation has no unitId: " +  facade.innerFieldObservation() + ": " + getFieldObservationNameSpace());
 240                         }
 241                 } catch (Exception e) {
 242                         throw new RuntimeException(e);
 243                 }
 244
 245         }
 246
 247         protected void handleFirstDerivedSpecimen(ResultSet rs, DerivedUnitFacade facade, AlgaTerraImportState state, ResultSetPartitioner partitioner) throws SQLException {
 248                 Integer unitId = nullSafeInt(rs, "unitId");
 249                 Integer collectionFk = nullSafeInt(rs,"CollectionFk");
 250
 251                 //collection
 252                 if (collectionFk != null){
 253                         Collection subCollection = state.getRelatedObject(AlgaTerraCollectionImport.NAMESPACE_SUBCOLLECTION, String.valueOf(collectionFk), Collection.class);
 254                         if (subCollection != null){
 255                                 facade.setCollection(subCollection);
 256                         }else{
 257                                 Collection collection = state.getRelatedObject(AlgaTerraCollectionImport.NAMESPACE_COLLECTION, String.valueOf(collectionFk), Collection.class);
 258                                 facade.setCollection(collection);
 259                         }
 260                 }
 261
 262                 //TODO id, created for fact +  ecoFact
 263                 //      this.doIdCreatedUpdatedNotes(state, descriptionElement, rs, id, namespace);
 264                 if (unitId != null){
 265                         this.doIdCreatedUpdatedNotes(state, facade.innerDerivedUnit(), rs, unitId, getDerivedUnitNameSpace());
 266                 }else{
 267                         logger.warn("Specimen has no unitId: " +  facade.innerDerivedUnit() + ": " + getDerivedUnitNameSpace());
 268                 }
 269         }
 270
 271
 272
 273         protected abstract String getDerivedUnitNameSpace();
 274
 275         protected abstract String getFieldObservationNameSpace();
 276
 277
 278         protected DescriptionBase getFieldObservationDescription(DerivedUnitFacade facade) {
 279                 Set<DescriptionBase> descriptions = facade.innerFieldObservation().getDescriptions();
 280                 for (DescriptionBase desc : descriptions){
 281                         if (desc.isImageGallery() == false){
 282                                 return desc;
 283                         }
 284                 }
 285                 SpecimenDescription specDesc = SpecimenDescription.NewInstance(facade.innerFieldObservation());
 286                 descriptions.add(specDesc);
 287                 return specDesc;
 288         }
 289
 290
 291         private void makeAreas(AlgaTerraImportState state, ResultSet rs, DerivedUnitFacade facade) throws SQLException {
 292                 Object gazetteerId = rs.getObject("GazetteerId");
 293                 if (gazetteerId != null){
 294                         //TDWG
 295                         NamedArea tdwgArea;
 296                         String tdwg4 = rs.getString("L4Code");
 297                         if (isNotBlank(tdwg4)){
 298                                 tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(tdwg4);
 299                         }else{
 300                                 String tdwg3 = rs.getString("L3Code");
 301                                 if (isNotBlank(tdwg3)){
 302                                         tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(tdwg3);
 303                                 }else{
 304                                         Integer tdwg2 = rs.getInt("L2Code");
 305                                         tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(String.valueOf(tdwg2));
 306                                 }
 307                         }
 308                         if (tdwgArea == null){
 309                                 logger.warn("TDWG area could not be defined for gazetterId: " + gazetteerId);
 310                         }else{
 311                                 facade.addCollectingArea(tdwgArea);
 312                         }
 313
 314                         //Countries
 315                         WaterbodyOrCountry country = null;
 316                         String isoCountry = rs.getString("ISOCountry");
 317                         String countryStr = rs.getString("Country");
 318                         if (isNotBlank(isoCountry)){
 319                                 country = WaterbodyOrCountry.getWaterbodyOrCountryByIso3166A2(isoCountry);
 320                         }else if (isNotBlank(countryStr)){
 321                                 logger.warn("Country exists but no ISO code");
 322                         }
 323                         if (country == null){
 324                                 logger.warn("Country does not exist for GazetteerID " + gazetteerId);
 325                         }else{
 326                                 facade.setCountry(country);
 327                         }
 328
 329                 }
 330
 331                 //Waterbody
 332                 WaterbodyOrCountry waterbody = null;
 333                 String waterbodyStr = rs.getString("WaterBody");
 334                 if (isNotBlank(waterbodyStr)){
 335                         if (waterbodyStr.equals("Atlantic Ocean")){
 336                                 waterbody = WaterbodyOrCountry.ATLANTICOCEAN();
 337                         }else{
 338                                 logger.warn("Waterbody not recognized: " + waterbody);
 339                         }
 340                         if (waterbody != null){
 341                                 facade.addCollectingArea(waterbody);
 342                         }
 343                 }
 344
 345
 346                 //countries sub
 347                 //TODO -> SpecimenImport (not existing in TypeSpecimen)
 348         }
 349
 350
 351
 352
 353         private ReferenceSystem makeRefrenceSystem(String geoCodeMethod, AlgaTerraImportState state) {
 354                 if (StringUtils.isBlank(geoCodeMethod)){
 355                         return null;
 356                 }else if(geoCodeMethod.startsWith("GPS")){
 357                         getReferenceSystem(state, uuidRefSystemGps, "GPS", "GPS", "GPS", ReferenceSystem.GOOGLE_EARTH().getVocabulary());
 358                         return ReferenceSystem.WGS84();
 359                 }else if(geoCodeMethod.startsWith("Google")){
 360                         return ReferenceSystem.GOOGLE_EARTH();
 361                 }else if(geoCodeMethod.startsWith("Map")){
 362                         logger.warn("Reference system " +  geoCodeMethod +  " not yet supported.");
 363                         return null;
 364                 }else if(geoCodeMethod.startsWith("WikiProjekt Georeferenzierung") || geoCodeMethod.startsWith("http://toolserver.org/~geohack/geohack.php") ){
 365                         return ReferenceSystem.WGS84();
 366                 }else {
 367                         logger.warn("Reference system " +  geoCodeMethod +  " not yet supported.");
 368                         return null;
 369                 }
 370         }
 371
 372
 373
 374
 375         private void handleCollectorTeam(AlgaTerraImportState state, DerivedUnitFacade facade, ResultSet rs) throws SQLException {
 376                 String collector = rs.getString("Collector");
 377                 TeamOrPersonBase<?> author = getAuthor(collector);
 378                 facade.setCollector(author);
 379         }
 380
 381         /**
 382          * @param facade
 383          * @param collector
 384          */
 385         protected TeamOrPersonBase<?> getAuthor(String author) {
 386                 // FIXME TODO parsen und deduplizieren
 387                 Team team = Team.NewTitledInstance(author, author);
 388                 return team;
 389         }
 390
 391
 392         /**
 393          * Use same TaxonDescription if two records belong to the same taxon
 394          * @param state
 395          * @param newTaxonId
 396          * @param oldTaxonId
 397          * @param oldDescription
 398          * @param taxonMap
 399          * @return
 400          */
 401         protected TaxonDescription getTaxonDescription(AlgaTerraImportState state, Taxon taxon, Reference<?> sourceSec){
 402                 TaxonDescription result = null;
 403                 Set<TaxonDescription> descriptionSet= taxon.getDescriptions();
 404                 if (descriptionSet.size() > 0) {
 405                         result = descriptionSet.iterator().next();
 406                 }else{
 407                         result = TaxonDescription.NewInstance();
 408                         result.setTitleCache(sourceSec.getTitleCache(), true);
 409                         taxon.addDescription(result);
 410                 }
 411                 return result;
 412         }
 413
 414
 415
 416
 417 }