app-import/src/main/java/eu/etaxonomy/cdm/io/algaterra/AlgaTerraSpecimenImport.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.HashMap;
  17 import java.util.HashSet;
  18 import java.util.Map;
  19 import java.util.Set;
  20 import java.util.UUID;
  21
  22 import org.apache.commons.lang.StringUtils;
  23 import org.apache.log4j.Logger;
  24 import org.springframework.stereotype.Component;
  25 import org.springframework.transaction.TransactionStatus;
  26
  27 import eu.etaxonomy.cdm.api.facade.DerivedUnitFacade;
  28 import eu.etaxonomy.cdm.api.facade.DerivedUnitFacade.DerivedUnitType;
  29 import eu.etaxonomy.cdm.api.facade.DerivedUnitFacadeNotSupportedException;
  30 import eu.etaxonomy.cdm.io.algaterra.validation.AlgaTerraSpecimenImportValidator;
  31 import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase;
  32 import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportConfigurator;
  33 import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportState;
  34 import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelTaxonImport;
  35 import eu.etaxonomy.cdm.io.common.IOValidator;
  36 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  37 import eu.etaxonomy.cdm.io.common.Source;
  38 import eu.etaxonomy.cdm.model.agent.Team;
  39 import eu.etaxonomy.cdm.model.common.CdmBase;
  40 import eu.etaxonomy.cdm.model.common.DefinedTermBase;
  41 import eu.etaxonomy.cdm.model.common.Language;
  42 import eu.etaxonomy.cdm.model.common.Marker;
  43 import eu.etaxonomy.cdm.model.common.MarkerType;
  44 import eu.etaxonomy.cdm.model.common.OrderedTermVocabulary;
  45 import eu.etaxonomy.cdm.model.common.TermVocabulary;
  46 import eu.etaxonomy.cdm.model.common.TimePeriod;
  47 import eu.etaxonomy.cdm.model.description.CategoricalData;
  48 import eu.etaxonomy.cdm.model.description.DescriptionBase;
  49 import eu.etaxonomy.cdm.model.description.Feature;
  50 import eu.etaxonomy.cdm.model.description.IndividualsAssociation;
  51 import eu.etaxonomy.cdm.model.description.MeasurementUnit;
  52 import eu.etaxonomy.cdm.model.description.Modifier;
  53 import eu.etaxonomy.cdm.model.description.QuantitativeData;
  54 import eu.etaxonomy.cdm.model.description.SpecimenDescription;
  55 import eu.etaxonomy.cdm.model.description.State;
  56 import eu.etaxonomy.cdm.model.description.StatisticalMeasure;
  57 import eu.etaxonomy.cdm.model.description.StatisticalMeasurementValue;
  58 import eu.etaxonomy.cdm.model.description.TaxonDescription;
  59 import eu.etaxonomy.cdm.model.description.TextData;
  60 import eu.etaxonomy.cdm.model.location.NamedArea;
  61 import eu.etaxonomy.cdm.model.location.Point;
  62 import eu.etaxonomy.cdm.model.location.ReferenceSystem;
  63 import eu.etaxonomy.cdm.model.location.TdwgArea;
  64 import eu.etaxonomy.cdm.model.location.WaterbodyOrCountry;
  65 import eu.etaxonomy.cdm.model.name.BotanicalName;
  66 import eu.etaxonomy.cdm.model.name.Rank;
  67 import eu.etaxonomy.cdm.model.occurrence.DerivedUnit;
  68 import eu.etaxonomy.cdm.model.occurrence.FieldObservation;
  69 import eu.etaxonomy.cdm.model.reference.Reference;
  70 import eu.etaxonomy.cdm.model.taxon.Taxon;
  71 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  72
  73
  74 /**
  75  * @author a.mueller
  76  * @created 20.03.2008
  77  * @version 1.0
  78  */
  79 @Component
  80 public class AlgaTerraSpecimenImport  extends BerlinModelImportBase {
  81         private static final Logger logger = Logger.getLogger(AlgaTerraSpecimenImport.class);
  82
  83         public static final String ECO_FACT_NAMESPACE = "EcoFact";
  84         public static final String TERMS_NAMESPACE = "ALGA_TERRA_TERMS";
  85
  86         //TODO move to transformrer
  87         final static UUID uuidMarkerAlkalinity = UUID.fromString("e52d0ea2-0c1f-4d95-ae6d-e21ab317c594");
  88         final static UUID uuidRefSystemGps = UUID.fromString("c23e4928-c137-4e4a-b6ab-b430da3d0b94");
  89         final static UUID uuidFeatureSpecimenCommunity = UUID.fromString("3ff5b1ab-3999-4b5a-b8f7-01fd2f6c12c7");
  90         final static UUID uuidFeatureAdditionalData = UUID.fromString("0ac82ab8-2c2b-4953-98eb-a9f718eb9c57");
  91         final static UUID uuidFeatureHabitatExplanation = UUID.fromString("6fe32295-61a3-44fc-9fcf-a85790ea888f");
  92
  93         final static UUID uuidVocAlgaTerraClimate = UUID.fromString("b0a677c6-8bb6-43f4-b1b8-fc377a10feb5");
  94         final static UUID uuidVocAlgaTerraHabitat = UUID.fromString("06f30114-e19c-4e7d-a8e5-5488c41fcbc5");
  95         final static UUID uuidVocAlgaTerraLifeForm = UUID.fromString("3c0b194e-809c-4b42-9498-6ff034066ed7");
  96
  97         final static UUID uuidFeatureAlgaTerraClimate = UUID.fromString("8754674c-9ab9-4f28-95f1-91eeee2314ee");
  98         final static UUID uuidFeatureAlgaTerraHabitat = UUID.fromString("7def3fc2-cdc5-4739-8e13-62edbd053415");
  99         final static UUID uuidFeatureAlgaTerraLifeForm = UUID.fromString("9b657901-1b0d-4a2a-8d21-dd8c1413e2e6");
 100
 101         final static UUID uuidVocParameter = UUID.fromString("45888b40-5bbb-4293-aa1e-02479796cd7c");
 102         final static UUID uuidStatMeasureSingleValue = UUID.fromString("eb4c3d98-4d4b-4c37-8eb4-17315ce79920");
 103         final static UUID uuidMeasurementValueModifier = UUID.fromString("0218a7a3-f6c0-4d06-a4f8-6b50b73aef5e");
 104
 105         final static UUID uuidModifierLowerThan = UUID.fromString("2b500085-6bef-4003-b6ea-e0ad0237d79d");
 106         final static UUID uuidModifierGreaterThan = UUID.fromString("828df49d-c745-48f7-b083-0ada43356c34");
 107
 108         private static int modCount = 5000;
 109         private static final String pluralString = "specimen and observation";
 110         private static final String dbTableName = "Fact";  //??
 111
 112
 113         public AlgaTerraSpecimenImport(){
 114                 super();
 115         }
 116
 117
 118
 119         /* (non-Javadoc)
 120          * @see eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase#getIdQuery()
 121          */
 122         @Override
 123         protected String getIdQuery(BerlinModelImportState state) {
 124                 String result = " SELECT factId " +
 125                                 " FROM Fact " +
 126                                         " INNER JOIN EcoFact ON Fact.ExtensionFk = EcoFact.EcoFactId " +
 127                                         "INNER JOIN PTaxon ON Fact.PTNameFk = PTaxon.PTNameFk AND Fact.PTRefFk = PTaxon.PTRefFk "
 128                                 + " WHERE FactCategoryFk = 202 "
 129                                 + " ORDER BY EcoFact.EcoFactId, PTaxon.RIdentifier, Fact.FactId ";
 130                 return result;
 131         }
 132
 133         /* (non-Javadoc)
 134          * @see eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase#getRecordQuery(eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportConfigurator)
 135          */
 136         @Override
 137         protected String getRecordQuery(BerlinModelImportConfigurator config) {
 138                         String strQuery =   //DISTINCT because otherwise emOccurrenceSource creates multiple records for a single distribution
 139             " SELECT PTaxon.RIdentifier as taxonId, Fact.FactId, Fact.RecordBasis, EcoFact.*, " +
 140                " tg.ID AS GazetteerId, tg.L2Code, tg.L3Code, tg.L4Code, tg.Country, tg.ISOCountry, " +
 141                " ec.UUID as climateUuid, eh.UUID as habitatUuid, elf.UUID as lifeFormUuid" +
 142             " FROM Fact " +
 143                  " INNER JOIN EcoFact ON Fact.ExtensionFk = EcoFact.EcoFactId " +
 144                  " INNER JOIN PTaxon ON dbo.Fact.PTNameFk = dbo.PTaxon.PTNameFk AND dbo.Fact.PTRefFk = dbo.PTaxon.PTRefFk " +
 145                  " LEFT OUTER JOIN TDWGGazetteer tg ON EcoFact.TDWGGazetteerFk = tg.ID " +
 146                  " LEFT OUTER JOIN EcoClimate  ec  ON EcoFact.ClimateFk  = ec.ClimateId " +
 147                  " LEFT OUTER JOIN EcoHabitat  eh  ON EcoFact.HabitatFk  = eh.HabitatId " +
 148                  " LEFT OUTER JOIN EcoLifeForm elf ON EcoFact.LifeFormFk = elf.LifeFormId " +
 149               " WHERE Fact.FactCategoryFk = 202 AND (Fact.FactId IN (" + ID_LIST_TOKEN + ")  )"
 150             + " ORDER BY EcoFact.EcoFactId, PTaxon.RIdentifier, Fact.FactId "
 151             ;
 152                 return strQuery;
 153         }
 154
 155         /* (non-Javadoc)
 156          * @see eu.etaxonomy.cdm.io.berlinModel.in.IPartitionedIO#doPartition(eu.etaxonomy.cdm.io.berlinModel.in.ResultSetPartitioner, eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportState)
 157          */
 158         public boolean doPartition(ResultSetPartitioner partitioner, BerlinModelImportState bmState) {
 159                 boolean success = true;
 160
 161                 AlgaTerraImportState state = (AlgaTerraImportState)bmState;
 162                 try {
 163                         makeVocabulariesAndFeatures(state);
 164                 } catch (SQLException e1) {
 165                         logger.warn("Exception occurred when trying to create Ecofact vocabularies: " + e1.getMessage());
 166                         e1.printStackTrace();
 167                 }
 168                 Set<TaxonBase> taxaToSave = new HashSet<TaxonBase>();
 169
 170                 Map<String, TaxonBase> taxonMap = (Map<String, TaxonBase>) partitioner.getObjectMap(BerlinModelTaxonImport.NAMESPACE);
 171                 Map<String, DerivedUnit> ecoFactMap = (Map<String, DerivedUnit>) partitioner.getObjectMap(ECO_FACT_NAMESPACE);
 172
 173                 ResultSet rs = partitioner.getResultSet();
 174
 175                 try {
 176
 177                         int i = 0;
 178
 179                         //for each reference
 180             while (rs.next()){
 181
 182                         if ((i++ % modCount) == 0 && i!= 1 ){ logger.info("Specimen facts handled: " + (i-1));}
 183
 184                                 int newTaxonId = rs.getInt("taxonId");
 185                                 int factId = rs.getInt("FactId");
 186                                 int ecoFactId = rs.getInt("EcoFactId");
 187                                 String recordBasis = rs.getString("RecordBasis");
 188
 189                                 try {
 190
 191                                         //source ref
 192                                         Reference<?> sourceRef = state.getTransactionalSourceReference();
 193
 194                                         //facade
 195                                         DerivedUnitType type = makeDerivedUnitType(recordBasis);
 196                                         DerivedUnitFacade facade = getDerivedUnit(state, ecoFactId, ecoFactMap, type);
 197
 198                                         //field observation
 199                                         handleSingleSpecimen(rs, facade, state);
 200
 201                                         state.setCurrentFieldObservationNotNew(false);
 202
 203                                         //description element
 204                                         TaxonDescription taxonDescription = getTaxonDescription(state, newTaxonId, taxonMap, factId, sourceRef);
 205                                         IndividualsAssociation indAssociation = IndividualsAssociation.NewInstance();
 206                                         Feature feature = makeFeature(type);
 207                                         indAssociation.setAssociatedSpecimenOrObservation(facade.innerDerivedUnit());
 208                                         indAssociation.setFeature(feature);
 209                                         taxonDescription.addElement(indAssociation);
 210
 211                                         taxaToSave.add(taxonDescription.getTaxon());
 212
 213
 214                                 } catch (Exception e) {
 215                                         logger.warn("Exception in ecoFact: FactId " + factId + ". " + e.getMessage());
 216 //                                      e.printStackTrace();
 217                                 }
 218
 219             }
 220
 221 //            logger.warn("Specimen: " + countSpecimen + ", Descriptions: " + countDescriptions );
 222
 223                         logger.warn("Taxa to save: " + taxaToSave.size());
 224                         getTaxonService().save(taxaToSave);
 225
 226                         return success;
 227                 } catch (SQLException e) {
 228                         logger.error("SQLException:" +  e);
 229                         return false;
 230                 }
 231         }
 232
 233
 234
 235
 236         /**
 237          * Creates the vocabularies and the features for Climate, Habitat and Lifeform
 238          * @param state
 239          * @throws SQLException
 240          */
 241         private void makeVocabulariesAndFeatures(AlgaTerraImportState state) throws SQLException {
 242                 String abbrevLabel = null;
 243                 URI uri = null;
 244
 245                 if (! state.isSpecimenVocabulariesCreated()){
 246
 247                         TransactionStatus txStatus = this.startTransaction();
 248
 249                         boolean isOrdered = true;
 250                         OrderedTermVocabulary<State> climateVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraClimate, "Climate", "Climate", abbrevLabel, uri, isOrdered, null);
 251                         OrderedTermVocabulary<State> habitatVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraHabitat, "Habitat", "Habitat", abbrevLabel, uri, isOrdered, null);
 252                         OrderedTermVocabulary<State> lifeformVoc = (OrderedTermVocabulary)getVocabulary(uuidVocAlgaTerraLifeForm, "Lifeform", "Lifeform", abbrevLabel, uri, isOrdered, null);
 253
 254
 255                         Feature feature = getFeature(state, uuidFeatureAlgaTerraClimate, "Climate","Climate", null, null);
 256                         feature.setSupportsCategoricalData(true);
 257
 258                         feature = getFeature(state, uuidFeatureAlgaTerraLifeForm, "LifeForm","LifeForm", null, null);
 259                         feature.setSupportsCategoricalData(true);
 260
 261                         feature = Feature.HABITAT();
 262                         feature.setSupportsCategoricalData(true);
 263                         getTermService().saveOrUpdate(feature);
 264
 265                         Source source = state.getAlgaTerraConfigurator().getSource();
 266
 267                         String climateSql = "SELECT * FROM EcoClimate";
 268                         ResultSet rs = source.getResultSet(climateSql);
 269                         while (rs.next()){
 270                                 String climate = rs.getString("Climate");
 271                                 String description = rs.getString("Description");
 272                                 Integer id = rs.getInt("ClimateId");
 273                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 274                                 State stateTerm = getStateTerm(state, uuid, climate, description, null, climateVoc);
 275                                 addOriginalSource(stateTerm, id.toString(), "EcoClimate", state.getTransactionalSourceReference());
 276                                 getTermService().saveOrUpdate(stateTerm);
 277                         }
 278
 279                         String habitatSql = "SELECT * FROM EcoHabitat";
 280                         rs = source.getResultSet(habitatSql);
 281                         while (rs.next()){
 282                                 String habitat = rs.getString("Habitat");
 283                                 String description = rs.getString("Description");
 284                                 Integer id = rs.getInt("HabitatId");
 285                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 286                                 State stateTerm = getStateTerm(state, uuid, habitat, description, null, habitatVoc);
 287                                 addOriginalSource(stateTerm, id.toString(), "EcoHabitat", state.getTransactionalSourceReference());
 288                                 getTermService().saveOrUpdate(stateTerm);
 289                         }
 290
 291                         String lifeformSql = "SELECT * FROM EcoLifeForm";
 292                         rs = source.getResultSet(lifeformSql);
 293                         while (rs.next()){
 294                                 String lifeform = rs.getString("LifeForm");
 295                                 String description = rs.getString("Description");
 296                                 Integer id = rs.getInt("LifeFormId");
 297                                 UUID uuid = UUID.fromString(rs.getString("UUID"));
 298                                 State stateTerm = getStateTerm(state, uuid, lifeform, description, null, lifeformVoc);
 299                                 addOriginalSource(stateTerm, id.toString(), "EcoLifeForm", state.getTransactionalSourceReference());
 300                                 getTermService().saveOrUpdate(stateTerm);
 301                         }
 302
 303                         this.commitTransaction(txStatus);
 304
 305                         state.setSpecimenVocabulariesCreated(true);
 306                 }
 307
 308         }
 309
 310
 311
 312         private void handleSingleSpecimen(ResultSet rs, DerivedUnitFacade facade, AlgaTerraImportState state) throws SQLException {
 313                 //FIXME missing fields #3084, #3085, #3080
 314                 try {
 315                         Object alkalinityFlag = rs.getBoolean("AlkalinityFlag");
 316
 317                         String locality = rs.getString("Locality");
 318                         Double latitude = nullSafeDouble(rs, "Latitude");
 319                         Double longitude = nullSafeDouble(rs, "Longitude");
 320                         Integer errorRadius = nullSafeInt(rs,"Prec");
 321                         String geoCodeMethod = rs.getString("GeoCodeMethod");
 322
 323                         Integer altitude = nullSafeInt(rs, "Altitude");
 324                         Integer lowerAltitude = nullSafeInt(rs,"AltitudeLowerValue");
 325                         String altitudeUnit = rs.getString("AltitudeUnit");
 326                         Double depth = nullSafeDouble(rs, "Depth");
 327                         Double depthLow = nullSafeDouble(rs, "DepthLow");
 328
 329                         String collectorsNumber = rs.getString("CollectorsNumber");
 330                         Date collectionDateStart = rs.getDate("CollectionDate");
 331                         Date collectionDateEnd = rs.getDate("CollectionDateEnd");
 332
 333                         String climateUuid = rs.getString("climateUuid");
 334                         String habitatUuid = rs.getString("habitatUuid");
 335                         String lifeFormUuid = rs.getString("lifeFormUuid");
 336
 337                         String habitat = rs.getString("HabitatExplanation");
 338                         String community = rs.getString("Comunity");
 339                         String additionalData = rs.getString("AdditionalData");
 340
 341
 342
 343                         FieldObservation fieldObservation = facade.getFieldObservation(true);
 344
 345                         //alkalinity marker
 346                         if (alkalinityFlag != null){
 347                                 MarkerType alkalinityMarkerType = getMarkerType(state, uuidMarkerAlkalinity, "Alkalinity", "Alkalinity", null);
 348                                 boolean alkFlag = Boolean.valueOf(alkalinityFlag.toString());
 349                                 Marker alkalinityMarker = Marker.NewInstance(alkalinityMarkerType, alkFlag);
 350                                 fieldObservation.addMarker(alkalinityMarker);
 351                         }
 352
 353                         //location
 354                         facade.setLocality(locality);
 355
 356                         //exact location
 357                         ReferenceSystem referenceSystem = makeRefrenceSystem(geoCodeMethod, state);
 358                         Point exactLocation = Point.NewInstance(longitude, latitude, referenceSystem, errorRadius);
 359                         facade.setExactLocation(exactLocation);
 360
 361                         //altitude, depth
 362                         if (StringUtils.isNotBlank(altitudeUnit) && ! altitudeUnit.trim().equalsIgnoreCase("m")){
 363                                 logger.warn("Altitude unit is not [m] but: " +  altitudeUnit);
 364                         }
 365                         if ( altitude != null){
 366                                 if (lowerAltitude == null){
 367                                         facade.setAbsoluteElevation(altitude);
 368                                 }else{
 369                                         if (! facade.isEvenDistance(lowerAltitude, altitude)){
 370                                                 //FIXME there is a ticket for this
 371                                                 altitude = altitude + 1;
 372                                                 logger.warn("Current implementation of altitude does not allow uneven distances");
 373                                         }
 374                                         facade.setAbsoluteElevationRange(lowerAltitude,altitude);
 375                                 }
 376                         }
 377                         if ( depth != null){
 378                                 //FIXME needs model change to accept double #3072
 379                                 Integer intDepth = depth.intValue();
 380                                 if (depthLow == null){
 381                                         facade.setDistanceToWaterSurface(intDepth);
 382                                 }else{
 383                                         //FIXME range not yet in model #3074
 384                                         facade.setDistanceToWaterSurface(intDepth);
 385                                 }
 386                         }
 387
 388                         //habitat, ecology, community, etc.
 389                         DescriptionBase<?> fieldDescription = getFieldObservationDescription(facade);
 390
 391                         addCategoricalValue(state, fieldDescription, climateUuid, uuidFeatureAlgaTerraClimate);
 392                         addCategoricalValue(state, fieldDescription, habitatUuid, Feature.HABITAT().getUuid());
 393                         addCategoricalValue(state, fieldDescription, lifeFormUuid, uuidFeatureAlgaTerraLifeForm);
 394
 395                         if (isNotBlank(habitat)){
 396                                 Feature habitatExplanation = getFeature(state, uuidFeatureHabitatExplanation, "Habitat Explanation", "HabitatExplanation", null, null);
 397                                 TextData textData = TextData.NewInstance(habitatExplanation);
 398                                 textData.putText(Language.DEFAULT(), habitat);
 399                                 getFieldObservationDescription(facade).addElement(textData);
 400                         }
 401                         if (isNotBlank(community)){
 402                                 Feature communityFeature = getFeature(state, uuidFeatureSpecimenCommunity, "Community", "The community of a specimen (e.g. other algae in the same sample)", null, null);
 403                                 TextData textData = TextData.NewInstance(communityFeature);
 404                                 textData.putText(Language.DEFAULT(), community);
 405                                 getFieldObservationDescription(facade).addElement(textData);
 406                         }
 407                         if (isNotBlank(additionalData)){  //or handle it as Annotation ??
 408                                 Feature additionalDataFeature = getFeature(state, uuidFeatureAdditionalData, "Additional Data", "Additional Data", null, null);
 409                                 TextData textData = TextData.NewInstance(additionalDataFeature);
 410                                 textData.putText(Language.DEFAULT(), additionalData);
 411                                 getFieldObservationDescription(facade).addElement(textData);
 412                         }
 413
 414                         //field
 415                         facade.setFieldNumber(collectorsNumber);
 416                         TimePeriod gatheringPeriod = TimePeriod.NewInstance(collectionDateStart, collectionDateEnd);
 417                         facade.setGatheringPeriod(gatheringPeriod);
 418                         handleCollectorTeam(state, facade, rs);
 419
 420                         //areas
 421                         makeAreas(state, rs, facade);
 422
 423                         //parameters
 424                         makeParameter(state, rs, getFieldObservationDescription(facade));
 425
 426                         //collection
 427                         String voucher = rs.getString("Voucher");
 428                         if (StringUtils.isNotBlank(voucher)){
 429                                 facade.setAccessionNumber(voucher);
 430                         }
 431
 432
 433                         //notes
 434                         //TODO is this an annotation on field observation or on the derived unit?
 435
 436                         //TODO id, created for fact +  ecoFact
 437                         //      this.doIdCreatedUpdatedNotes(state, descriptionElement, rs, id, namespace);
 438
 439                 } catch (Exception e) {
 440                         throw new RuntimeException(e);
 441                 }
 442
 443         }
 444
 445
 446         private void makeParameter(AlgaTerraImportState state, ResultSet rs, DescriptionBase<?> descriptionBase) throws SQLException {
 447                 for (int i = 1; i <= 10; i++){
 448                         String valueStr = rs.getString(String.format("P%dValue", i));
 449                         String unitStr = rs.getString(String.format("P%dUnit", i));
 450                         String parameter = rs.getString(String.format("P%dParameter", i));
 451                         String method = rs.getString(String.format("P%dMethod", i));
 452
 453                         //method
 454                         if (StringUtils.isNotBlank(method)){
 455                                 logger.warn("Methods not yet handled: " + method);
 456                         }
 457                         //parameter
 458                         TermVocabulary<Feature> vocParameter = getVocabulary(uuidVocParameter, "Feature vocabulary for AlgaTerra measurement parameters", "Parameters", null, null, false, Feature.COMMON_NAME());
 459                         if (StringUtils.isNotBlank(parameter)){
 460                                 UUID featureUuid = getParameterFeatureUuid(state, parameter);
 461                                 Feature feature = getFeature(state, featureUuid, parameter, parameter, null, vocParameter);
 462                                 QuantitativeData quantData = QuantitativeData.NewInstance(feature);
 463
 464                                 //unit
 465                                 MeasurementUnit unit = getMeasurementUnit(state, unitStr);
 466                                 quantData.setUnit(unit);
 467                                 try {
 468
 469                                         Set<Modifier> valueModifier = new HashSet<Modifier>();
 470                                         valueStr = normalizeAndModifyValue(state, valueStr, valueModifier);
 471                                         //value
 472                                         Float valueFlt = Float.valueOf(valueStr);  //TODO maybe change model to Double ??
 473
 474                                         StatisticalMeasure measureSingleValue = getStatisticalMeasure(state, uuidStatMeasureSingleValue, "Value", "Single measurement value", null, null);
 475                                         StatisticalMeasurementValue value = StatisticalMeasurementValue.NewInstance(measureSingleValue, valueFlt);
 476                                         quantData.addStatisticalValue(value);
 477                                         descriptionBase.addElement(quantData);
 478
 479                                 } catch (NumberFormatException e) {
 480                                         logger.warn(String.format("Value '%s' can't be converted to double. Parameter %s not imported.", valueStr, parameter));
 481                                 }
 482                         }else if (isNotBlank(valueStr) || isNotBlank(unitStr) ){
 483                                 logger.warn("There is value or unit without parameter: " + i);
 484                         }
 485
 486
 487                 }
 488
 489         }
 490
 491         private String normalizeAndModifyValue(AlgaTerraImportState state, String valueStr, Set<Modifier> valueModifier) {
 492                 valueStr = valueStr.replace(",", ".");
 493                 if (valueStr.startsWith("<")){
 494                         TermVocabulary<Modifier> measurementValueModifierVocabulary = getVocabulary(uuidMeasurementValueModifier, "Measurement value modifier", "Measurement value modifier", null, null, false, Modifier.NewInstance());
 495                         Modifier modifier = getModifier(state, uuidModifierLowerThan, "Lower", "Lower than the given measurement value", "<", measurementValueModifierVocabulary);
 496                         valueModifier.add(modifier);
 497                         valueStr = valueStr.replace("<", "");
 498                 }
 499                 if (valueStr.startsWith(">")){
 500                         TermVocabulary<Modifier> measurementValueModifierVocabulary = getVocabulary(uuidMeasurementValueModifier, "Measurement value modifier", "Measurement value modifier", null, null, false, Modifier.NewInstance());
 501                         Modifier modifier = getModifier(state, uuidModifierGreaterThan, "Lower", "Lower than the given measurement value", "<", measurementValueModifierVocabulary);
 502                         valueModifier.add(modifier);
 503                         valueStr = valueStr.replace(">", "");
 504                 }
 505                 return valueStr;
 506         }
 507
 508
 509
 510         private UUID getParameterFeatureUuid(AlgaTerraImportState state, String key) {
 511                 //TODO define some UUIDs in Transformer
 512                 UUID uuid = state.getParameterFeatureUuid(key);
 513                 if (uuid == null){
 514                         uuid = UUID.randomUUID();
 515                         state.putParameterFeatureUuid(key, uuid);
 516                 }
 517                 return uuid;
 518         }
 519
 520
 521
 522         /**
 523          * TODO move to InputTransformerBase
 524          * @param state
 525          * @param unitStr
 526          * @return
 527          */
 528         private MeasurementUnit getMeasurementUnit(AlgaTerraImportState state, String unitStr) {
 529                 MeasurementUnit result = null;
 530                 if (StringUtils.isNotBlank(unitStr)){
 531                         UUID uuidMeasurementUnitMgL = UUID.fromString("7ac302c5-3cbd-4334-964a-bf5d11eb9ead");
 532                         UUID uuidMeasurementUnitMolMol = UUID.fromString("96b78d78-3e49-448f-8100-e7779b71dd53");
 533                         UUID uuidMeasurementUnitMicroMolSiL = UUID.fromString("2cb8bc85-a4af-42f1-b80b-34c36c9f75d4");
 534                         UUID uuidMeasurementUnitMicroMolL = UUID.fromString("a631f62e-377e-405c-bd1a-76885b13a72b");
 535                         UUID uuidMeasurementUnitDegreeC = UUID.fromString("55222aec-d5be-413e-8db7-d9a48c316c6c");
 536                         UUID uuidMeasurementUnitPercent = UUID.fromString("3ea3110e-f048-4bed-8bfe-33c60f63626f");
 537                         UUID uuidMeasurementUnitCm = UUID.fromString("3ea3110e-f048-4bed-8bfe-33c60f63626f");
 538                         UUID uuidMeasurementUnitMicroSiCm = UUID.fromString("3ea3110e-f048-4bed-8bfe-33c60f63626f");
 539
 540
 541                         if (unitStr.equalsIgnoreCase("mg/L")){
 542                                 return  getMeasurementUnit(state, uuidMeasurementUnitMgL, unitStr, unitStr, unitStr, null);
 543                         }else if (unitStr.equalsIgnoreCase("mol/mol")){
 544                                 return result = getMeasurementUnit(state, uuidMeasurementUnitMolMol, unitStr, unitStr, unitStr, null);
 545                         }else if (unitStr.equalsIgnoreCase("\u00B5mol Si/L")){   //µmol Si/L
 546                                 return getMeasurementUnit(state, uuidMeasurementUnitMicroMolSiL, unitStr, unitStr, unitStr, null);
 547                         }else if (unitStr.equalsIgnoreCase("\u00B5mol/L")){             //µmol/L
 548                                 return getMeasurementUnit(state, uuidMeasurementUnitMicroMolL, unitStr, unitStr, unitStr, null);
 549                         }else if (unitStr.equalsIgnoreCase("\u00B0C")){               //°C
 550                                 return getMeasurementUnit(state, uuidMeasurementUnitDegreeC, unitStr, unitStr, unitStr, null);
 551                         }else if (unitStr.equalsIgnoreCase("%")){
 552                                 return getMeasurementUnit(state, uuidMeasurementUnitPercent, unitStr, unitStr, unitStr, null);
 553                         }else if (unitStr.equalsIgnoreCase("cm")){
 554                                 return getMeasurementUnit(state, uuidMeasurementUnitCm, unitStr, unitStr, unitStr, null);
 555                         }else if (unitStr.equalsIgnoreCase("\u00B5S/cm")){   //µS/cm
 556                                 return getMeasurementUnit(state, uuidMeasurementUnitMicroSiCm, unitStr, unitStr, unitStr, null);
 557                         }else{
 558                                 logger.warn("MeasurementUnit was not recognized");
 559                                 return null;
 560                         }
 561                 }else{
 562                         return null;
 563                 }
 564         }
 565
 566
 567
 568         private void addCategoricalValue(AlgaTerraImportState importState, DescriptionBase description, String uuidTerm, UUID featureUuid) {
 569                 if (uuidTerm != null){
 570                         State state = this.getStateTerm(importState, UUID.fromString(uuidTerm));
 571                         Feature feature = getFeature(importState, featureUuid);
 572                         CategoricalData categoricalData = CategoricalData.NewInstance(state, feature);
 573                         description.addElement(categoricalData);
 574                 }
 575         }
 576
 577
 578         private void handleCollectorTeam(AlgaTerraImportState state, DerivedUnitFacade facade, ResultSet rs) throws SQLException {
 579                 // FIXME parsen
 580                 String collector = rs.getString("Collector");
 581                 Team team = Team.NewTitledInstance(collector, collector);
 582                 facade.setCollector(team);
 583
 584
 585
 586         }
 587
 588         private void makeAreas(AlgaTerraImportState state, ResultSet rs, DerivedUnitFacade facade) throws SQLException {
 589                 Object gazetteerId = rs.getObject("GazetteerId");
 590                 if (gazetteerId != null){
 591                         //TDWG
 592                         NamedArea tdwgArea;
 593                         String tdwg4 = rs.getString("L4Code");
 594                         if (isNotBlank(tdwg4)){
 595                                 tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(tdwg4);
 596                         }else{
 597                                 String tdwg3 = rs.getString("L3Code");
 598                                 if (isNotBlank(tdwg3)){
 599                                         tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(tdwg3);
 600                                 }else{
 601                                         Integer tdwg2 = rs.getInt("L2Code");
 602                                         tdwgArea = TdwgArea.getAreaByTdwgAbbreviation(String.valueOf(tdwg2));
 603                                 }
 604                         }
 605                         if (tdwgArea == null){
 606                                 logger.warn("TDWG area could not be defined for gazetterId: " + gazetteerId);
 607                         }else{
 608                                 facade.addCollectingArea(tdwgArea);
 609                         }
 610
 611                         //Countries
 612                         WaterbodyOrCountry country = null;
 613                         String isoCountry = rs.getString("ISOCountry");
 614                         String countryStr = rs.getString("Country");
 615                         if (isNotBlank(isoCountry)){
 616                                 country = WaterbodyOrCountry.getWaterbodyOrCountryByIso3166A2(isoCountry);
 617                         }else if (isNotBlank(countryStr)){
 618                                 logger.warn("Country exists but no ISO code");
 619                         }
 620                         if (country == null){
 621                                 logger.warn("Country does not exist for GazetteerID " + gazetteerId);
 622                         }else{
 623                                 facade.setCountry(country);
 624                         }
 625
 626                 }
 627
 628                 //Waterbody
 629                 WaterbodyOrCountry waterbody = null;
 630                 String waterbodyStr = rs.getString("WaterBody");
 631                 if (isNotBlank(waterbodyStr)){
 632                         if (waterbodyStr.equals("Atlantic Ocean")){
 633                                 waterbody = WaterbodyOrCountry.ATLANTICOCEAN();
 634                         }else{
 635                                 logger.warn("Waterbody not recognized: " + waterbody);
 636                         }
 637                         if (waterbody != null){
 638                                 facade.addCollectingArea(waterbody);
 639                         }
 640                 }
 641
 642
 643                 //countries sub
 644                 //TODO
 645         }
 646
 647         private DescriptionBase getFieldObservationDescription(DerivedUnitFacade facade) {
 648                 Set<DescriptionBase> descriptions = facade.innerFieldObservation().getDescriptions();
 649                 for (DescriptionBase desc : descriptions){
 650                         if (desc.isImageGallery() == false){
 651                                 return desc;
 652                         }
 653                 }
 654                 SpecimenDescription specDesc = SpecimenDescription.NewInstance(facade.innerFieldObservation());
 655                 descriptions.add(specDesc);
 656                 return specDesc;
 657         }
 658
 659         private ReferenceSystem makeRefrenceSystem(String geoCodeMethod, AlgaTerraImportState state) {
 660                 if (StringUtils.isBlank(geoCodeMethod)){
 661                         return null;
 662                 }else if(geoCodeMethod.startsWith("GPS")){
 663                         getReferenceSystem(state, uuidRefSystemGps, "GPS", "GPS", "GPS", ReferenceSystem.GOOGLE_EARTH().getVocabulary());
 664                         return ReferenceSystem.WGS84();
 665                 }else if(geoCodeMethod.startsWith("Google")){
 666                         return ReferenceSystem.GOOGLE_EARTH();
 667                 }else if(geoCodeMethod.startsWith("Map")){
 668                         logger.warn("Reference system " +  geoCodeMethod +  " not yet supported.");
 669                         return null;
 670                 }else if(geoCodeMethod.startsWith("WikiProjekt Georeferenzierung") || geoCodeMethod.startsWith("http://toolserver.org/~geohack/geohack.php") ){
 671                         return ReferenceSystem.WGS84();
 672                 }else {
 673                         logger.warn("Reference system " +  geoCodeMethod +  " not yet supported.");
 674                         return null;
 675                 }
 676         }
 677
 678         /**
 679          * @param state
 680          * @param ecoFactId
 681          * @param derivedUnitMap
 682          * @param type
 683          * @return
 684          */
 685         private DerivedUnitFacade getDerivedUnit(AlgaTerraImportState state, int ecoFactId, Map<String, DerivedUnit> derivedUnitMap, DerivedUnitType type) {
 686                 String key = String.valueOf(ecoFactId);
 687                 DerivedUnit derivedUnit = derivedUnitMap.get(key);
 688                 DerivedUnitFacade facade;
 689                 if (derivedUnit == null){
 690                         facade = DerivedUnitFacade.NewInstance(type);
 691                         derivedUnitMap.put(key, derivedUnit);
 692                 }else{
 693                         try {
 694                                 facade = DerivedUnitFacade.NewInstance(derivedUnit);
 695                         } catch (DerivedUnitFacadeNotSupportedException e) {
 696                                 logger.error(e.getMessage());
 697                                 facade = DerivedUnitFacade.NewInstance(type);
 698                         }
 699                 }
 700
 701                 return facade;
 702         }
 703
 704         private Feature makeFeature(DerivedUnitType type) {
 705                 if (type.equals(DerivedUnitType.DerivedUnit)){
 706                         return Feature.INDIVIDUALS_ASSOCIATION();
 707                 }else if (type.equals(DerivedUnitType.FieldObservation) || type.equals(DerivedUnitType.Observation) ){
 708                         return Feature.OBSERVATION();
 709                 }else if (type.equals(DerivedUnitType.Fossil) || type.equals(DerivedUnitType.LivingBeing) || type.equals(DerivedUnitType.Specimen )){
 710                         return Feature.SPECIMEN();
 711                 }
 712                 logger.warn("No feature defined for derived unit type: " + type);
 713                 return null;
 714         }
 715
 716
 717         private DerivedUnitType makeDerivedUnitType(String recordBasis) {
 718                 DerivedUnitType result = null;
 719                 if (StringUtils.isBlank(recordBasis)){
 720                         result = DerivedUnitType.DerivedUnit;
 721                 } else if (recordBasis.equalsIgnoreCase("FossileSpecimen")){
 722                         result = DerivedUnitType.Fossil;
 723                 }else if (recordBasis.equalsIgnoreCase("HumanObservation")){
 724                         result = DerivedUnitType.Observation;
 725                 }else if (recordBasis.equalsIgnoreCase("Literature")){
 726                         logger.warn("Literature record basis not yet supported");
 727                         result = DerivedUnitType.DerivedUnit;
 728                 }else if (recordBasis.equalsIgnoreCase("LivingSpecimen")){
 729                         result = DerivedUnitType.LivingBeing;
 730                 }else if (recordBasis.equalsIgnoreCase("MachineObservation")){
 731                         logger.warn("MachineObservation record basis not yet supported");
 732                         result = DerivedUnitType.Observation;
 733                 }else if (recordBasis.equalsIgnoreCase("PreservedSpecimen")){
 734                         result = DerivedUnitType.Specimen;
 735                 }
 736                 return result;
 737         }
 738
 739         /* (non-Javadoc)
 740          * @see eu.etaxonomy.cdm.io.berlinModel.in.IPartitionedIO#getRelatedObjectsForPartition(java.sql.ResultSet)
 741          */
 742         public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition(ResultSet rs) {
 743                 String nameSpace;
 744                 Class cdmClass;
 745                 Set<String> idSet;
 746                 Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<Object, Map<String, ? extends CdmBase>>();
 747
 748                 try{
 749                         Set<String> taxonIdSet = new HashSet<String>();
 750                         Set<String> fieldObservationIdSet = new HashSet<String>();
 751                         Set<String> termsIdSet = new HashSet<String>();
 752
 753                         while (rs.next()){
 754                                 handleForeignKey(rs, taxonIdSet, "taxonId");
 755                                 handleForeignKey(rs, fieldObservationIdSet, "ecoFactId");
 756                                 handleForeignKey(rs, termsIdSet, "ClimateFk");
 757                                 handleForeignKey(rs, termsIdSet, "HabitatFk");
 758                                 handleForeignKey(rs, termsIdSet, "LifeFormFk");
 759                         }
 760
 761                         //taxon map
 762                         nameSpace = BerlinModelTaxonImport.NAMESPACE;
 763                         cdmClass = TaxonBase.class;
 764                         idSet = taxonIdSet;
 765                         Map<String, TaxonBase> objectMap = (Map<String, TaxonBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 766                         result.put(nameSpace, objectMap);
 767
 768                         //field observation map map
 769                         nameSpace = AlgaTerraSpecimenImport.ECO_FACT_NAMESPACE;
 770                         cdmClass = FieldObservation.class;
 771                         idSet = taxonIdSet;
 772                         Map<String, FieldObservation> fieldObservationMap = (Map<String, FieldObservation>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 773                         result.put(nameSpace, fieldObservationMap);
 774
 775                         //terms
 776                         nameSpace = AlgaTerraSpecimenImport.TERMS_NAMESPACE;
 777                         cdmClass = FieldObservation.class;
 778                         idSet = taxonIdSet;
 779                         Map<String, DefinedTermBase> termMap = (Map<String, DefinedTermBase>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 780                         result.put(nameSpace, termMap);
 781
 782
 783
 784
 785                 } catch (SQLException e) {
 786                         throw new RuntimeException(e);
 787                 }
 788                 return result;
 789         }
 790
 791
 792         /**
 793          * Use same TaxonDescription if two records belong to the same taxon
 794          * @param state
 795          * @param newTaxonId
 796          * @param oldTaxonId
 797          * @param oldDescription
 798          * @param taxonMap
 799          * @return
 800          */
 801         private TaxonDescription getTaxonDescription(AlgaTerraImportState state, int newTaxonId, Map<String, TaxonBase> taxonMap, int factId, Reference<?> sourceSec){
 802                 TaxonDescription result = null;
 803                 TaxonBase<?> taxonBase = taxonMap.get(String.valueOf(newTaxonId));
 804
 805                 //TODO for testing
 806                 if (taxonBase == null && ! state.getConfig().isDoTaxa()){
 807                         taxonBase = Taxon.NewInstance(BotanicalName.NewInstance(Rank.SPECIES()), null);
 808                 }
 809
 810                 Taxon taxon;
 811                 if ( taxonBase instanceof Taxon ) {
 812                         taxon = (Taxon) taxonBase;
 813                 } else if (taxonBase != null) {
 814                         logger.warn("TaxonBase for Fact(Specimen) with factId" + factId + " was not of type Taxon but: " + taxonBase.getClass().getSimpleName());
 815                         return null;
 816                 } else {
 817                         logger.warn("TaxonBase for Fact(Specimen) " + factId + " is null.");
 818                         return null;
 819                 }
 820                 Set<TaxonDescription> descriptionSet= taxon.getDescriptions();
 821                 if (descriptionSet.size() > 0) {
 822                         result = descriptionSet.iterator().next();
 823                 }else{
 824                         result = TaxonDescription.NewInstance();
 825                         result.setTitleCache(sourceSec.getTitleCache(), true);
 826                         taxon.addDescription(result);
 827                 }
 828                 return result;
 829         }
 830
 831
 832         /* (non-Javadoc)
 833          * @see eu.etaxonomy.cdm.io.common.CdmIoBase#doCheck(eu.etaxonomy.cdm.io.common.IoStateBase)
 834          */
 835         @Override
 836         protected boolean doCheck(BerlinModelImportState state){
 837                 IOValidator<BerlinModelImportState> validator = new AlgaTerraSpecimenImportValidator();
 838                 return validator.validate(state);
 839         }
 840
 841         /* (non-Javadoc)
 842          * @see eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase#getTableName()
 843          */
 844         @Override
 845         protected String getTableName() {
 846                 return dbTableName;
 847         }
 848
 849         /* (non-Javadoc)
 850          * @see eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportBase#getPluralString()
 851          */
 852         @Override
 853         public String getPluralString() {
 854                 return pluralString;
 855         }
 856
 857         /* (non-Javadoc)
 858          * @see eu.etaxonomy.cdm.io.common.CdmIoBase#isIgnore(eu.etaxonomy.cdm.io.common.IImportConfigurator)
 859          */
 860         protected boolean isIgnore(BerlinModelImportState state){
 861                 return ! ((AlgaTerraImportState)state).getAlgaTerraConfigurator().isDoSpecimen();
 862         }
 863
 864 }