app-import/src/main/java/eu/etaxonomy/cdm/io/berlinModel/in/BerlinModelCommonNamesImport.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.sql.ResultSet;
  13 import java.sql.SQLException;
  14 import java.util.ArrayList;
  15 import java.util.HashMap;
  16 import java.util.HashSet;
  17 import java.util.List;
  18 import java.util.Map;
  19 import java.util.Set;
  20 import java.util.SortedSet;
  21 import java.util.TreeSet;
  22 import java.util.UUID;
  23
  24 import org.apache.commons.lang.StringUtils;
  25 import org.apache.log4j.Logger;
  26 import org.springframework.stereotype.Component;
  27
  28 import eu.etaxonomy.cdm.common.CdmUtils;
  29 import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;
  30 import eu.etaxonomy.cdm.io.berlinModel.in.validation.BerlinModelCommonNamesImportValidator;
  31 import eu.etaxonomy.cdm.io.common.IOValidator;
  32 import eu.etaxonomy.cdm.io.common.ResultSetPartitioner;
  33 import eu.etaxonomy.cdm.io.common.Source;
  34 import eu.etaxonomy.cdm.io.common.TdwgAreaProvider;
  35 import eu.etaxonomy.cdm.model.common.Annotation;
  36 import eu.etaxonomy.cdm.model.common.AnnotationType;
  37 import eu.etaxonomy.cdm.model.common.CdmBase;
  38 import eu.etaxonomy.cdm.model.common.Extension;
  39 import eu.etaxonomy.cdm.model.common.ExtensionType;
  40 import eu.etaxonomy.cdm.model.common.Language;
  41 import eu.etaxonomy.cdm.model.common.Marker;
  42 import eu.etaxonomy.cdm.model.common.MarkerType;
  43 import eu.etaxonomy.cdm.model.common.Representation;
  44 import eu.etaxonomy.cdm.model.description.CommonTaxonName;
  45 import eu.etaxonomy.cdm.model.description.DescriptionElementSource;
  46 import eu.etaxonomy.cdm.model.description.TaxonDescription;
  47 import eu.etaxonomy.cdm.model.location.Country;
  48 import eu.etaxonomy.cdm.model.location.NamedArea;
  49 import eu.etaxonomy.cdm.model.name.TaxonNameBase;
  50 import eu.etaxonomy.cdm.model.reference.Reference;
  51 import eu.etaxonomy.cdm.model.taxon.Taxon;
  52 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  53
  54 /**
  55  *
  56  * @author a.mueller
  57  * @created 20.03.2008
  58  */
  59 @Component
  60 public class BerlinModelCommonNamesImport  extends BerlinModelImportBase {
  61         private static final Logger logger = Logger.getLogger(BerlinModelCommonNamesImport.class);
  62
  63         public static final UUID REFERENCE_LANGUAGE_ISO639_2_UUID = UUID.fromString("40c4f8dd-3d9c-44a4-b77a-76e137a89a5f");
  64         public static final UUID REFERENCE_LANGUAGE_STRING_UUID = UUID.fromString("2a1b678f-c27d-48c1-b43e-98fd0d426305");
  65         public static final UUID STATUS_ANNOTATION_UUID = UUID.fromString("e3f7b80a-1286-458d-812c-5e818f731968");
  66
  67         public static final String NAMESPACE = "common name";
  68
  69
  70         private static final String pluralString = "common names";
  71         private static final String dbTableName = "emCommonName";
  72
  73
  74         //map that stores the regions (named areas) and makes them accessible via the regionFk
  75         private Map<String, NamedArea> regionMap = new HashMap<String, NamedArea>();
  76
  77         public BerlinModelCommonNamesImport(){
  78                 super(dbTableName, pluralString);
  79         }
  80
  81         @Override
  82         protected String getIdQuery(BerlinModelImportState state) {
  83                 String result = " SELECT CommonNameId FROM emCommonName WHERE (1=1) ";
  84                 if (StringUtils.isNotBlank(state.getConfig().getCommonNameFilter())){
  85                         result += " AND " + state.getConfig().getCommonNameFilter();
  86                 }
  87
  88                 return result;
  89         }
  90
  91         @Override
  92         protected String getRecordQuery(BerlinModelImportConfigurator config) {
  93                 String recordQuery = "";
  94                 recordQuery =
  95                                 " SELECT     cn.CommonNameId, cn.CommonName, PTaxon.RIdentifier AS taxonId, cn.PTNameFk, cn.RefFk AS refId, cn.Status, cn.RegionFks, cn.MisNameRefFk, " +
  96                                                "               cn.NameInSourceFk, cn.Created_When, cn.Updated_When, cn.Created_Who, cn.Updated_Who, cn.Note AS Notes, languageCommonName.Language, " +
  97                                                "               languageCommonName.LanguageOriginal, languageCommonName.ISO639_1, languageCommonName.ISO639_2,   " +
  98                                                "               emLanguageReference.RefFk AS languageRefRefFk, emLanguageReference.ReferenceShort, emLanguageReference.ReferenceLong,  " +
  99                                                "               emLanguageReference.LanguageFk, languageReferenceLanguage.Language AS refLanguage, languageReferenceLanguage.ISO639_2 AS refLanguageIso639_2,  "+
 100                                                "               misappliedTaxon.RIdentifier AS misappliedTaxonId " +
 101                                         " FROM         PTaxon AS misappliedTaxon RIGHT OUTER JOIN " +
 102                                             "                  emLanguage AS languageReferenceLanguage RIGHT OUTER JOIN " +
 103                                                        "       emLanguageReference ON languageReferenceLanguage.LanguageId = emLanguageReference.LanguageFk RIGHT OUTER JOIN " +
 104                                                        "       emCommonName AS cn INNER JOIN " +
 105                                                        "       PTaxon ON cn.PTNameFk = PTaxon.PTNameFk AND cn.PTRefFk = PTaxon.PTRefFk ON  " +
 106                                                        "       emLanguageReference.ReferenceId = cn.LanguageRefFk LEFT OUTER JOIN " +
 107                                                         "      emLanguage AS languageCommonName ON cn.LanguageFk = languageCommonName.LanguageId ON misappliedTaxon.PTNameFk = cn.NameInSourceFk AND  " +
 108                                                         "      misappliedTaxon.PTRefFk = cn.MisNameRefFk " +
 109                         " WHERE cn.CommonNameId IN (" + ID_LIST_TOKEN + ")";
 110                 return recordQuery;
 111         }
 112
 113         @Override
 114         protected void doInvoke(BerlinModelImportState state) {
 115                 try {
 116                         makeRegions(state);
 117                 } catch (Exception e) {
 118                         logger.error("Error when creating common name regions:" + e.getMessage());
 119                         e.printStackTrace();
 120                         state.setUnsuccessfull();
 121                 }
 122                 super.doInvoke(state);
 123                 return;
 124         }
 125
 126         /**
 127          * @param state
 128          *
 129          */
 130         private void makeRegions(BerlinModelImportState state) {
 131                 try {
 132                         SortedSet<Integer> regionFks = new TreeSet<Integer>();
 133                         Source source = state.getConfig().getSource();
 134
 135                         //fill set with all regionFk from emCommonName.regionFks
 136                         getRegionFks(state, regionFks, source);
 137                         //concat filter string
 138                         String sqlWhere = getSqlWhere(regionFks);
 139
 140                         //get E+M - TDWG Mapping
 141                         Map<String, String> emTdwgMap = getEmTdwgMap(source);
 142                         //fill regionMap
 143                         fillRegionMap(state, sqlWhere, emTdwgMap);
 144
 145                         return;
 146                 } catch (NumberFormatException e) {
 147                         e.printStackTrace();
 148                         state.setUnsuccessfull();
 149                         return;
 150                 } catch (SQLException e) {
 151                         e.printStackTrace();
 152                         state.setUnsuccessfull();
 153                         return;
 154                 }
 155         }
 156
 157
 158         @Override
 159         public boolean doPartition(ResultSetPartitioner partitioner, BerlinModelImportState state)  {
 160                 boolean success = true ;
 161
 162                 Set<TaxonBase> taxaToSave = new HashSet<TaxonBase>();
 163                 Map<String, Taxon> taxonMap = (Map<String, Taxon>) partitioner.getObjectMap(BerlinModelTaxonImport.NAMESPACE);
 164                 Map<String, TaxonNameBase> taxonNameMap = (Map<String, TaxonNameBase>) partitioner.getObjectMap(BerlinModelTaxonNameImport.NAMESPACE);
 165
 166                 Map<String, Reference> refMap = (Map<String, Reference>) partitioner.getObjectMap(BerlinModelReferenceImport.REFERENCE_NAMESPACE);
 167
 168                 Map<String, Language> iso6392Map = new HashMap<String, Language>();
 169
 170         //      logger.warn("MisappliedNameRefFk  not yet implemented for Common Names");
 171
 172                 ResultSet rs = partitioner.getResultSet();
 173                 try{
 174                         while (rs.next()){
 175
 176                                 //create TaxonName element
 177                                 Object commonNameId = rs.getObject("CommonNameId");
 178                                 int taxonId = rs.getInt("taxonId");
 179                                 Object refId = rs.getObject("refId");
 180                                 Object ptNameFk = rs.getObject("PTNameFk");
 181                                 String commonNameString = rs.getString("CommonName");
 182                                 String iso639_2 = rs.getString("ISO639_2");
 183                                 String iso639_1 = rs.getString("ISO639_1");
 184                                 String languageString = rs.getString("Language");
 185                                 String originalLanguageString = rs.getString("LanguageOriginal");
 186                                 Object misNameRefFk = rs.getObject("MisNameRefFk");
 187                                 Object languageRefRefFk = rs.getObject("languageRefRefFk");
 188                                 String refLanguage = rs.getString("refLanguage");
 189                                 String refLanguageIso639_2 = rs.getString("refLanguageIso639_2");
 190                                 String status = rs.getString("Status");
 191                                 Object nameInSourceFk = rs.getObject("NameInSourceFk");
 192                                 Object misappliedTaxonId = rs.getObject("misappliedTaxonId");
 193
 194                                 //regions
 195                                 String regionFks  = rs.getString("RegionFks");
 196                                 String[] regionFkSplit = regionFks.split(",");
 197
 198                                 //commonNameString
 199                                 if (isBlank(commonNameString)){
 200                                         String message = "CommonName is empty or null. Do not import record for taxon " + taxonId;
 201                                         logger.warn(message);
 202                                         continue;
 203                                 }
 204
 205                                 //taxon
 206                                 Taxon taxon = null;
 207                                 TaxonBase<?> taxonBase  = taxonMap.get(String.valueOf(taxonId));
 208                                 if (taxonBase == null){
 209                                         logger.warn("Taxon (" + taxonId + ") could not be found. Common name " + commonNameString + "(" + commonNameId + ") not imported");
 210                                         continue;
 211                                 }else if (! taxonBase.isInstanceOf(Taxon.class)){
 212                                         logger.warn("taxon (" + taxonId + ") is not accepted. Can't import common name " +  commonNameId);
 213                                         continue;
 214                                 }else{
 215                                         taxon = CdmBase.deproxy(taxonBase, Taxon.class);
 216                                 }
 217
 218                                 //Language
 219                                 Language language = getAndHandleLanguage(iso6392Map, iso639_2, iso639_1, languageString, originalLanguageString, state);
 220
 221                                 //CommonTaxonName
 222                                 List<CommonTaxonName> commonTaxonNames = new ArrayList<CommonTaxonName>();
 223                                 for (String regionFk : regionFkSplit){ //
 224                                         CommonTaxonName commonTaxonName;
 225                                         if (commonTaxonNames.size() == 0){
 226                                                 commonTaxonName = CommonTaxonName.NewInstance(commonNameString, language);
 227                                         }else{
 228                                                 commonTaxonName = (CommonTaxonName)commonTaxonNames.get(0).clone();
 229                                         }
 230                                         commonTaxonNames.add(commonTaxonName);
 231                                         regionFk = regionFk.trim();
 232                                         NamedArea area = regionMap.get(regionFk);
 233                                         if (area == null){
 234                                                 if (regionFkSplit.length > 1 && StringUtils.isNotBlank(regionFk)){
 235                                                         logger.warn("Area for " + regionFk + " not defined in regionMap.");
 236                                                 }else{
 237                                                         //no region is defined
 238                                                 }
 239                                         }else{
 240                                                 commonTaxonName.setArea(area);
 241                                                 TaxonDescription description = getDescription(taxon);
 242                                                 description.addElement(commonTaxonName);
 243                                         }
 244                                 }
 245
 246                                 //Reference/Source
 247                                 String strRefId = String.valueOf(refId);
 248                                 String languageRefFk = String.valueOf(languageRefRefFk);
 249                                 if (! CdmUtils.nullSafeEqual(strRefId, languageRefFk)){
 250                                         //use strRefId if languageRefFk is null
 251                                         if (languageRefRefFk == null){
 252                                                 languageRefFk = strRefId;
 253                                         }else{
 254                                                 logger.warn("CommonName.RefFk (" + CdmUtils.Nz(strRefId) + ") and LanguageReference.RefFk " + CdmUtils.Nz(languageRefFk) + " are not equal. I will import only languageReference.RefFk");
 255                                         }
 256                                 }
 257
 258                                 Reference<?> reference = refMap.get(String.valueOf(languageRefRefFk));
 259                                 String microCitation = null;
 260                                 String originalNameString = null;
 261
 262                                 TaxonNameBase<?,?> nameUsedInSource = taxonNameMap.get(String.valueOf(nameInSourceFk));
 263                                 if (nameInSourceFk != null && nameUsedInSource == null){
 264                                         logger.warn("Name used in source (" + nameInSourceFk + ") was not found for common name " + commonNameId);
 265                                 }
 266                                 DescriptionElementSource source = DescriptionElementSource.NewPrimarySourceInstance(reference, microCitation, nameUsedInSource, originalNameString);
 267                                 for (CommonTaxonName commonTaxonName : commonTaxonNames){
 268                                         commonTaxonName.addSource(source);
 269                                 }
 270
 271
 272                                 //MisNameRef
 273                                 if (misNameRefFk != null){
 274                                         //Taxon misappliedName = getMisappliedName(biblioRefMap, nomRefMap, misNameRefFk, taxon);
 275                                         Taxon misappliedNameTaxon = null;
 276                                         if (misappliedTaxonId != null){
 277                                                 TaxonBase<?> misTaxonBase =  taxonMap.get(String.valueOf(misappliedTaxonId));
 278                                                 if (misTaxonBase == null){
 279                                                         logger.warn("MisappliedName not found for misappliedTaxonId " + misappliedTaxonId + "; commonNameId: " + commonNameId);
 280                                                 }else if (misTaxonBase.isInstanceOf(Taxon.class)){
 281                                                         misappliedNameTaxon = CdmBase.deproxy(misTaxonBase, Taxon.class);
 282                                                 }else{
 283                                                         logger.warn("Misapplied name taxon is not of type Taxon but " + misTaxonBase.getClass().getSimpleName());
 284                                                 }
 285                                         }else{
 286
 287                                                 Reference<?> sec = refMap.get(String.valueOf(misNameRefFk));
 288                                                 if (nameUsedInSource == null || sec == null){
 289                                                         logger.warn("Taxon name or misapplied name reference is null for common name " + commonNameId);
 290                                                 }else{
 291                                                         misappliedNameTaxon = Taxon.NewInstance(nameUsedInSource, sec);
 292                                                         MarkerType misCommonNameMarker = getMarkerType(state, BerlinModelTransformer.uuidMisappliedCommonName,"Misapplied Common Name in Berlin Model", "Misapplied taxon was automatically created by Berlin Model import for a common name with a misapplied name reference", "MCN");
 293                                                         Marker marker = Marker.NewInstance(misCommonNameMarker, true);
 294                                                         misappliedNameTaxon.addMarker(marker);
 295                                                         taxaToSave.add(misappliedNameTaxon);
 296                                                         logger.warn("Misapplied name taxon could not be found in database but misapplied name reference exists for common name. " +
 297                                                                         "New misapplied name for misapplied reference common name was added. CommonNameId: " + commonNameId);
 298                                                 }
 299                                         }
 300                                         if (misappliedNameTaxon != null){
 301
 302                                                 if (! taxon.getMisappliedNames().contains(misappliedNameTaxon)){
 303                                                         taxon.addMisappliedName(misappliedNameTaxon,state.getTransactionalSourceReference(), null);
 304                                                         logger.warn("Misapplied name for common name was not found related to the accepted taxon. Created new relationship. CommonNameId: " + commonNameId);
 305                                                 }
 306
 307                                                 TaxonDescription misappliedNameDescription = getDescription(misappliedNameTaxon);
 308                                                 for (CommonTaxonName commonTaxonName : commonTaxonNames){
 309                                                         CommonTaxonName commonNameClone = (CommonTaxonName)commonTaxonName.clone();
 310                                                         misappliedNameDescription.addElement(commonNameClone);
 311                                                 }
 312                                         }else{
 313                                                 logger.warn("Misapplied name is null for common name " + commonNameId);
 314                                         }
 315
 316                                 }
 317
 318
 319                                 //reference extensions
 320                                 if (reference != null){
 321                                         if (StringUtils.isNotBlank(refLanguage)){
 322                                                 ExtensionType refLanguageExtensionType = getExtensionType( state, REFERENCE_LANGUAGE_STRING_UUID, "reference language","The language of the reference","ref. lang.");
 323                                                 Extension.NewInstance(reference, refLanguage, refLanguageExtensionType);
 324                                         }
 325
 326                                         if (StringUtils.isNotBlank(refLanguageIso639_2)){
 327                                                 ExtensionType refLanguageIsoExtensionType = getExtensionType( state, REFERENCE_LANGUAGE_ISO639_2_UUID, "reference language iso 639-2","The iso 639-2 code of the references language","ref. lang. 639-2");
 328                                                 Extension.NewInstance(reference, refLanguageIso639_2, refLanguageIsoExtensionType);
 329                                         }
 330                                 }else if (isNotBlank(refLanguage) || isNotBlank(refLanguageIso639_2)){
 331                                         logger.warn("Reference is null (" + languageRefRefFk + ") but refLanguage (" + CdmUtils.Nz(refLanguage) + ") or iso639_2 (" + CdmUtils.Nz(refLanguageIso639_2) + ") was not null for common name ("+ commonNameId +")");
 332                                 }
 333
 334                                 //status
 335                                 if (isNotBlank(status)){
 336                                         AnnotationType statusAnnotationType = getAnnotationType( state, STATUS_ANNOTATION_UUID, "status","The status of this object","status", null);
 337                                         for (CommonTaxonName commonTaxonName : commonTaxonNames){
 338                                                 Annotation annotation = Annotation.NewInstance(status, statusAnnotationType, Language.DEFAULT());
 339                                                 commonTaxonName.addAnnotation(annotation);
 340                                         }
 341                                 }
 342
 343                                 //Notes
 344                                 for (CommonTaxonName commonTaxonName : commonTaxonNames){
 345                                         doIdCreatedUpdatedNotes(state, commonTaxonName, rs, String.valueOf(commonNameId), NAMESPACE);
 346                                 }
 347                                 partitioner.startDoSave();
 348                                 taxaToSave.add(taxon);
 349
 350                         }
 351                 } catch (SQLException e) {
 352                         logger.error("SQLException:" +  e);
 353                         return false;
 354                 } catch (ClassCastException e) {
 355                         e.printStackTrace();
 356                 }
 357
 358                 //      logger.info( i + " names handled");
 359                 getTaxonService().save(taxaToSave);
 360                 return success;
 361
 362         }
 363
 364         /**
 365          * @param iso6392Map
 366          * @param iso639_2
 367          * @param languageString
 368          * @param originalLanguageString
 369          * @param state
 370          * @return
 371          */
 372         private Language getAndHandleLanguage(Map<String, Language> iso639Map,  String iso639_2, String iso639_1, String languageString, String originalLanguageString, BerlinModelImportState state) {
 373                 Language language;
 374                 if (isNotBlank(iso639_2)|| isNotBlank(iso639_1)  ){
 375                         //TODO test performance, implement in state
 376                         language = getLanguageFromIsoMap(iso639Map, iso639_2, iso639_1);
 377
 378                         if (language == null){
 379                                 language = getTermService().getLanguageByIso(iso639_2);
 380                                 iso639Map.put(iso639_2, language);
 381                                 if (language == null){
 382                                         try {
 383                                                 language = getTermService().getLanguageByIso(iso639_1);
 384                                         } catch (Exception e) {
 385                                                 // TODO Auto-generated catch block
 386                                                 // TODO remove if problem with duplicate DescElement_Annot id is solved
 387                                                 e.printStackTrace();
 388                                         }
 389                                         iso639Map.put(iso639_1, language);
 390                                 }
 391                                 if (language == null){
 392                                         logger.warn("Language for code ISO693-2 '" + iso639_2 + "' and ISO693-1 '" + iso639_1 + "' was not found");
 393                                 }
 394                         }
 395                 } else if ("unknown".equals(languageString)){
 396                         language = Language.UNKNOWN_LANGUAGE();
 397                 } else if ("Majorcan".equalsIgnoreCase(languageString)){
 398                         language = getLanguage(state, BerlinModelTransformer.uuidLangMajorcan, "Majorcan", "Majorcan (original 'mallorqu\u00EDn')", null);
 399                 }else{
 400                         logger.warn("language ISO 639_1 and ISO 639_2 were empty for " + languageString);
 401                         language = null;
 402                 }
 403                 addOriginalLanguage(language, originalLanguageString);
 404                 return language;
 405         }
 406
 407
 408         /**
 409          * @param iso639Map
 410          * @param iso639_2
 411          * @param iso639_1
 412          * @return
 413          */
 414         private Language getLanguageFromIsoMap(Map<String, Language> iso639Map, String iso639_2, String iso639_1) {
 415                 Language language;
 416                 language = iso639Map.get(iso639_2);
 417                 if (language == null){
 418                         language = iso639Map.get(iso639_1);
 419                 }
 420                 return language;
 421         }
 422
 423         /**
 424          * @param language
 425          * @param originalLanguageString
 426          */
 427         private void addOriginalLanguage(Language language,     String originalLanguageString) {
 428                 if (isBlank(originalLanguageString)){
 429                         return;
 430                 }else if (language == null){
 431                         logger.warn("Language could not be defined, but originalLanguageString exists: " + originalLanguageString);
 432                 }else {
 433                         Representation representation = language.getRepresentation(language);
 434                         if (representation == null){
 435                                 language.addRepresentation(Representation.NewInstance(originalLanguageString, originalLanguageString, originalLanguageString, language));
 436                                 getTermService().saveOrUpdate(language);
 437                         }
 438                 }
 439
 440         }
 441
 442
 443
 444         /**
 445          * Fills the regionFks with all regionFks from emCommonName. Comma separated regionFks will be split.
 446          * @param state
 447          * @param regionFks
 448          * @param source
 449          * @return
 450          * @throws SQLException
 451          *
 452          */
 453         private void getRegionFks(BerlinModelImportState state, SortedSet<Integer> regionFks, Source source) throws SQLException {
 454                 String sql = " SELECT DISTINCT RegionFks FROM emCommonName";
 455                 if (state.getConfig().getCommonNameFilter() != null){
 456                         sql += " WHERE " + state.getConfig().getCommonNameFilter();
 457                 }
 458
 459                 ResultSet rs = source.getResultSet(sql);
 460                 while (rs.next()){
 461                         String strRegionFks = rs.getString("RegionFks");
 462                         if (isBlank(strRegionFks)){
 463                                 continue;
 464                         }
 465
 466                         String[] regionFkArray = strRegionFks.split(",");
 467                         for (String regionFk: regionFkArray){
 468                                 regionFk = regionFk.trim();
 469                                 if (! StringUtils.isNumeric(regionFk) || "".equals(regionFk)  ){
 470                                         state.setUnsuccessfull();
 471                                         logger.warn("RegionFk is not numeric: " + regionFk +  " ( part of " + strRegionFks + ")");
 472                                 }else{
 473                                         regionFks.add(Integer.valueOf(regionFk));
 474                                 }
 475                         }
 476                 }
 477                 return;
 478         }
 479
 480
 481
 482         /**
 483          * Fills the {@link #regionMap} by all emLanguageRegion regions defined in the sql filter.
 484          * {@link #regionMap} maps emLanguageRegion.RegionId to named areas.
 485          * @param state
 486          * @param sqlWhere
 487          * @param emTdwgMap
 488          * @throws SQLException
 489          */
 490         private void fillRegionMap(BerlinModelImportState state, String sqlWhere,
 491                         Map<String, String> emTdwgMap) throws SQLException {
 492                 Source source = state.getConfig().getSource();
 493                 String sql;
 494                 ResultSet rs;
 495                 sql = " SELECT RegionId, Region FROM emLanguageRegion WHERE RegionId IN ("+ sqlWhere+ ") ";
 496                 rs = source.getResultSet(sql);
 497                 while (rs.next()){
 498                         Object regionId = rs.getObject("RegionId");
 499                         String region = rs.getString("Region");
 500                         String[] splitRegion = region.split("-");
 501                         if (splitRegion.length <= 1){
 502                                 NamedArea newArea = getNamedArea(state, null, region, "Language region '" + region + "'", null, null, null);
 503 //                              getTermService().save(newArea);
 504                                 regionMap.put(String.valueOf(regionId), newArea);
 505                                 logger.warn("Found new area: " +  region);
 506                         }else if (splitRegion.length == 2){
 507                                 String emCode = splitRegion[1].trim();
 508                                 String tdwgCode = emTdwgMap.get(emCode);
 509                                 if (StringUtils.isNotBlank(tdwgCode) ){
 510                                         NamedArea tdwgArea = getNamedArea(state, tdwgCode);
 511                                         regionMap.put(String.valueOf(regionId), tdwgArea);
 512                                 }else {
 513                                         NamedArea area = getOtherAreas(state, emCode, tdwgCode);
 514                                         if (area != null){
 515                                                 regionMap.put(String.valueOf(regionId), area);
 516                                         }else{
 517                                                 logger.warn("emCode did not map to valid tdwgCode: " +  CdmUtils.Nz(emCode) + "->" + CdmUtils.Nz(tdwgCode));
 518                                         }
 519                                 }
 520                         }
 521                 }
 522         }
 523
 524
 525         /**
 526          * Returns the are for a given TDWG code. See {@link #getEmTdwgMap(Source)} for exceptions from
 527          * the TDWG code
 528          * @param state
 529          * @param tdwgCode
 530          */
 531         private NamedArea getNamedArea(BerlinModelImportState state, String tdwgCode) {
 532                 NamedArea area;
 533                 if (tdwgCode.equalsIgnoreCase("Ab")){
 534                         area = getNamedArea(state, BerlinModelTransformer.uuidAzerbaijanNakhichevan, "Azerbaijan & Nakhichevan", "Azerbaijan (including Nakhichevan)",  "Ab", null, null);
 535                         getTermService().saveOrUpdate(area);
 536                 }else if (tdwgCode.equalsIgnoreCase("Uk")){
 537                         area = getNamedArea(state, BerlinModelTransformer.uuidUkraineAndCrimea , "Ukraine & Crimea", "Ukraine (including Crimea)", "Uk", null, null);
 538                         getTermService().saveOrUpdate(area);
 539                 }else if (tdwgCode.equalsIgnoreCase("Rf")){
 540                         area = Country.RUSSIANFEDERATION();
 541                 }else if (tdwgCode.equalsIgnoreCase("Gg")){
 542                         area = Country.GEORGIA();
 543                 }else{
 544                         area = TdwgAreaProvider.getAreaByTdwgAbbreviation(tdwgCode);
 545                 }
 546                 if (area == null){
 547                         logger.warn("Area is null for " + tdwgCode);
 548                 }
 549                 return area;
 550         }
 551
 552         /**
 553          * @param regionFks
 554          * @return
 555          */
 556         private String getSqlWhere(SortedSet<Integer> regionFks) {
 557                 String sqlWhere = "";
 558                 for (Integer regionFk : regionFks){
 559                         sqlWhere += regionFk + ",";
 560                 }
 561                 sqlWhere = sqlWhere.substring(0, sqlWhere.length()-1);
 562                 return sqlWhere;
 563         }
 564
 565         /**
 566          * Returns a map which is filled by the emCode->TdwgCode mapping defined in emArea.
 567          * Some exceptions are defined for emCode 'Ab','Rf','Uk' and some additional mapping is added
 568          * for 'Ab / Ab(A)', 'Ga / Ga(F)', 'It / It(I)', 'Ar / Ar(A)','Hs / Hs(S)'
 569          * @param source
 570          * @throws SQLException
 571          */
 572         private Map<String, String> getEmTdwgMap(Source source) throws SQLException {
 573                 String sql;
 574                 ResultSet rs;
 575                 Map<String, String> emTdwgMap = new HashMap<String, String>();
 576                 sql = " SELECT EmCode, TDWGCode FROM emArea ";
 577                 rs = source.getResultSet(sql);
 578                 while (rs.next()){
 579                         String emCode = rs.getString("EMCode");
 580                         String TDWGCode = rs.getString("TDWGCode");
 581                         if (StringUtils.isNotBlank(emCode) ){
 582                                 emCode = emCode.trim();
 583                                 if (emCode.equalsIgnoreCase("Ab") || emCode.equalsIgnoreCase("Rf")||
 584                                                 emCode.equalsIgnoreCase("Uk") || emCode.equalsIgnoreCase("Gg")){
 585                                         emTdwgMap.put(emCode, emCode);
 586                                 }else if (StringUtils.isNotBlank(TDWGCode)){
 587                                         emTdwgMap.put(emCode, TDWGCode.trim());
 588                                 }
 589                         }
 590                 }
 591                 emTdwgMap.put("Ab / Ab(A)", "Ab");
 592                 emTdwgMap.put("Ga / Ga(F)", "FRA-FR");
 593                 emTdwgMap.put("It / It(I)", "ITA");
 594                 emTdwgMap.put("Uk / Uk(U)", "Uk");
 595                 emTdwgMap.put("Ar / Ar(A)", "TCS-AR");
 596                 emTdwgMap.put("Hs / Hs(S)", "SPA-SP");
 597
 598                 return emTdwgMap;
 599         }
 600
 601
 602         /**
 603          * Returns the first non-image gallery description. Creates a new one if no description exists.
 604          * @param taxon
 605          * @return
 606          */
 607         private TaxonDescription getDescription(Taxon taxon) {
 608                 TaxonDescription result = null;
 609                 for (TaxonDescription taxonDescription : taxon.getDescriptions()){
 610                         if (! taxonDescription.isImageGallery()){
 611                                 result = taxonDescription;
 612                         }
 613                 }
 614                 if (result == null){
 615                         result = TaxonDescription.NewInstance(taxon);
 616                 }
 617                 return result;
 618         }
 619
 620         @Override
 621         public Map<Object, Map<String, ? extends CdmBase>> getRelatedObjectsForPartition(ResultSet rs) {
 622                 String nameSpace;
 623                 Class<?> cdmClass;
 624                 Set<String> idSet;
 625                 Map<Object, Map<String, ? extends CdmBase>> result = new HashMap<Object, Map<String, ? extends CdmBase>>();
 626
 627                 String pos = "0";
 628                 try{
 629                         Set<String> taxonIdSet = new HashSet<String>();
 630                         Set<String> nameIdSet = new HashSet<String>();
 631                         Set<String> referenceIdSet = new HashSet<String>();
 632                         while (rs.next()){
 633                                 handleForeignKey(rs, taxonIdSet, "taxonId");
 634                                 handleForeignKey(rs, taxonIdSet, "misappliedTaxonId");
 635                                 handleForeignKey(rs, referenceIdSet, "refId");
 636                                 handleForeignKey(rs, referenceIdSet, "languageRefRefFk");
 637                                 handleForeignKey(rs, nameIdSet, "NameInSourceFk");
 638                                 handleForeignKey(rs, nameIdSet, "PTNameFk");
 639                                 handleForeignKey(rs, referenceIdSet, "MisNameRefFk");
 640                         }
 641
 642                         //name map
 643                         nameSpace = BerlinModelTaxonNameImport.NAMESPACE;
 644                         cdmClass = TaxonNameBase.class;
 645                         idSet = nameIdSet;
 646                         Map<String, TaxonNameBase<?,?>> nameMap = (Map<String, TaxonNameBase<?,?>>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 647                         result.put(nameSpace, nameMap);
 648
 649                         //taxon map
 650                         nameSpace = BerlinModelTaxonImport.NAMESPACE;
 651                         cdmClass = TaxonBase.class;
 652                         idSet = taxonIdSet;
 653                         Map<String, TaxonBase<?>> taxonMap = (Map<String, TaxonBase<?>>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 654                         result.put(nameSpace, taxonMap);
 655
 656                         //reference map
 657                         nameSpace = BerlinModelReferenceImport.REFERENCE_NAMESPACE;
 658                         cdmClass = Reference.class;
 659                         idSet = referenceIdSet;
 660                         Map<String, Reference> referenceMap = (Map<String, Reference>)getCommonService().getSourcedObjectsByIdInSource(cdmClass, idSet, nameSpace);
 661                         result.put(nameSpace, referenceMap);
 662                         // TODO remove if problem with duplicate DescElement_Annot id is solved
 663                 } catch (SQLException e) {
 664                         throw new RuntimeException("pos: " +pos, e);
 665                 } catch (NullPointerException nep){
 666                         logger.error("NullPointerException in getRelatedObjectsForPartition()");
 667                 }
 668                 return result;
 669         }
 670
 671
 672
 673         /* (non-Javadoc)
 674          * @see eu.etaxonomy.cdm.io.common.CdmIoBase#doCheck(eu.etaxonomy.cdm.io.common.IoStateBase)
 675          */
 676         @Override
 677         protected boolean doCheck(BerlinModelImportState state){
 678                 IOValidator<BerlinModelImportState> validator = new BerlinModelCommonNamesImportValidator();
 679                 return validator.validate(state);
 680         }
 681
 682         /* (non-Javadoc)
 683          * @see eu.etaxonomy.cdm.io.common.CdmIoBase#isIgnore(eu.etaxonomy.cdm.io.common.IImportConfigurator)
 684          */
 685         protected boolean isIgnore(BerlinModelImportState state){
 686                 return ! state.getConfig().isDoCommonNames();
 687         }
 688
 689 }