app-import/src/main/java/eu/etaxonomy/cdm/app/wp6/palmae/UseImport.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.app.wp6.palmae;
  11
  12 import java.io.FileInputStream;
  13 import java.io.FileNotFoundException;
  14 import java.io.IOException;
  15 import java.io.InputStream;
  16 import java.net.URI;
  17 import java.net.URISyntaxException;
  18 import java.util.ArrayList;
  19 import java.util.HashSet;
  20 import java.util.Iterator;
  21 import java.util.List;
  22 import java.util.Set;
  23 import java.util.UUID;
  24
  25 import org.apache.log4j.Logger;
  26 import org.apache.poi.hssf.usermodel.HSSFCell;
  27 import org.apache.poi.hssf.usermodel.HSSFRow;
  28 import org.apache.poi.hssf.usermodel.HSSFSheet;
  29 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  30 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  31
  32 import eu.etaxonomy.cdm.api.application.CdmApplicationController;
  33 import eu.etaxonomy.cdm.api.conversation.ConversationHolder;
  34 import eu.etaxonomy.cdm.api.service.IDescriptionService;
  35 import eu.etaxonomy.cdm.api.service.IFeatureTreeService;
  36 import eu.etaxonomy.cdm.api.service.IIdentifiableEntityService;
  37 import eu.etaxonomy.cdm.api.service.IReferenceService;
  38 import eu.etaxonomy.cdm.api.service.ITaxonService;
  39 import eu.etaxonomy.cdm.api.service.ITermService;
  40 import eu.etaxonomy.cdm.api.service.IVocabularyService;
  41 import eu.etaxonomy.cdm.api.service.pager.Pager;
  42 import eu.etaxonomy.cdm.common.AccountStore;
  43 import eu.etaxonomy.cdm.database.CdmDataSource;
  44 import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
  45 import eu.etaxonomy.cdm.database.ICdmDataSource;
  46 import eu.etaxonomy.cdm.model.agent.Team;
  47 import eu.etaxonomy.cdm.model.common.CdmBase;
  48 import eu.etaxonomy.cdm.model.common.DefinedTermBase;
  49 import eu.etaxonomy.cdm.model.common.IdentifiableSource;
  50 import eu.etaxonomy.cdm.model.common.Language;
  51 import eu.etaxonomy.cdm.model.common.LanguageString;
  52 import eu.etaxonomy.cdm.model.common.Marker;
  53 import eu.etaxonomy.cdm.model.common.MarkerType;
  54 import eu.etaxonomy.cdm.model.common.TermVocabulary;
  55 import eu.etaxonomy.cdm.model.common.TimePeriod;
  56 import eu.etaxonomy.cdm.model.description.CategoricalData;
  57 import eu.etaxonomy.cdm.model.description.Feature;
  58 import eu.etaxonomy.cdm.model.description.FeatureNode;
  59 import eu.etaxonomy.cdm.model.description.FeatureTree;
  60 import eu.etaxonomy.cdm.model.description.Modifier;
  61 import eu.etaxonomy.cdm.model.description.State;
  62 import eu.etaxonomy.cdm.model.description.StateData;
  63 import eu.etaxonomy.cdm.model.description.TaxonDescription;
  64 import eu.etaxonomy.cdm.model.description.TextData;
  65 import eu.etaxonomy.cdm.model.reference.IReference;
  66 import eu.etaxonomy.cdm.model.reference.Reference;
  67 import eu.etaxonomy.cdm.model.reference.ReferenceFactory;
  68 import eu.etaxonomy.cdm.model.taxon.Synonym;
  69 import eu.etaxonomy.cdm.model.taxon.Taxon;
  70 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  71 import eu.etaxonomy.cdm.persistence.dao.common.IDefinedTermDao;
  72
  73 public class UseImport {
  74         public static final Logger logger = Logger.getLogger(UseImport.class);
  75
  76         public void UseImport() {
  77
  78         }
  79
  80         public static ICdmDataSource dataSource() {
  81                 DatabaseTypeEnum dbType = DatabaseTypeEnum.MySQL;
  82                 String cdmServer = "localhost";
  83                 String cdmDB = "palm_use_cdm_db";
  84                 String cdmUserName = "root";
  85                 String cdmPWD = "root";
  86                 return makeDestination(dbType, cdmServer, cdmDB, -1, cdmUserName, cdmPWD);
  87         }
  88
  89         private static ICdmDataSource makeDestination(DatabaseTypeEnum dbType,
  90                         String cdmServer, String cdmDB, int port, String cdmUserName,
  91                         String pwd) {
  92                 // establish connection
  93                 pwd = AccountStore.readOrStorePassword(cdmServer, cdmDB, cdmUserName,
  94                                 pwd);
  95                 ICdmDataSource destination;
  96                 if (dbType.equals(DatabaseTypeEnum.MySQL)) {
  97                         destination = CdmDataSource.NewMySqlInstance(cdmServer, cdmDB,
  98                                         port, cdmUserName, pwd, null);
  99                 } else if (dbType.equals(DatabaseTypeEnum.PostgreSQL)) {
 100                         destination = CdmDataSource.NewPostgreSQLInstance(cdmServer, cdmDB,
 101                                         port, cdmUserName, pwd, null);
 102                 } else {
 103                         // TODO others
 104                         throw new RuntimeException("Unsupported DatabaseType");
 105                 }
 106                 return destination;
 107         }
 108
 109         public boolean importFromExcelSS(String xlsPath) {
 110                 boolean success = true;
 111
 112                 CdmApplicationController applicationController = CdmApplicationController.NewInstance(dataSource());
 113                 ConversationHolder conversation = applicationController.NewConversation();
 114                 conversation.startTransaction();
 115
 116                 ITaxonService service = applicationController.getTaxonService();
 117                 ITermService termService = applicationController.getTermService();
 118                 IDescriptionService descService = applicationController.getDescriptionService();
 119                 IReferenceService referenceService = applicationController.getReferenceService();
 120                 InputStream inputStream = null;
 121
 122                 try {
 123                         inputStream = new FileInputStream(xlsPath);
 124
 125                 } catch (FileNotFoundException e) {
 126                         success = false;
 127                         System.out.println("File not found in the specified path.");
 128                         e.printStackTrace();
 129                 }
 130
 131                 POIFSFileSystem fileSystem = null;
 132
 133                 try {
 134                         fileSystem = new POIFSFileSystem(inputStream);
 135
 136                         HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
 137                         HSSFSheet sheet = workBook.getSheetAt(0);
 138                         Iterator rows = sheet.rowIterator();
 139                         // Iterator rows = sheet.rowIterator();
 140                         ArrayList<ArrayList<String>> lstUpdates = new ArrayList<ArrayList<String>>();
 141                         Set<Integer> lstTaxonIDs = new HashSet<Integer>();
 142                         //Set<Integer> lstTaxonIDs;
 143
 144
 145                         while (rows.hasNext()) {
 146
 147                                 HSSFRow row = (HSSFRow) rows.next();
 148                                 System.out.println("Row No.: " + row.getRowNum());
 149                                 Iterator cells = row.cellIterator();
 150                                 ArrayList<String> lstTaxon = new ArrayList<String>();
 151                                 while (cells.hasNext()) {
 152                                         HSSFCell cell = (HSSFCell) cells.next();
 153
 154                                         int intCellType = cell.getCellType();
 155                                         switch (intCellType) {
 156                                                 case 0:
 157                                                         int cellValue = (int) cell.getNumericCellValue();
 158                                                         lstTaxon.add(Integer.toString(cellValue));
 159                                                         break;
 160                                                 case 1:
 161                                                         lstTaxon.add(cell.getStringCellValue());
 162                                                         break;
 163                                         }
 164                                 }
 165                                 lstUpdates.add(lstTaxon);
 166                                 lstTaxonIDs.add(Integer.parseInt(lstTaxon.get(0)));
 167                         }
 168
 169                         List<TaxonBase> taxa = service.findTaxaByID(lstTaxonIDs);
 170                         for(TaxonBase idTaxa : taxa) {
 171                                 //System.out.println(idTaxa.getUuid().toString());
 172                                 System.out.println(idTaxa.getName());
 173                         }
 174
 175
 176                         MarkerType useMarkerType = (MarkerType) termService.find(UUID.fromString("2e6e42d9-e92a-41f4-899b-03c0ac64f039"));
 177                         Marker useMarker = Marker.NewInstance(useMarkerType, true);
 178                         for (ArrayList<String> lstUpdate : lstUpdates) {
 179                                 System.out.println("-----------------------------------------------: " + lstUpdate.get(1));
 180                                 String idTaxonToUpdate = lstUpdate.get(1);
 181                                 TaxonDescription newUseDescription = TaxonDescription.NewInstance();
 182                                 newUseDescription.addMarker(useMarker);
 183                                 newUseDescription.setTitleCache(lstUpdate.get(2));
 184                                 Reference citation = ReferenceFactory.newGeneric();
 185                                 Team authorTeam = Team.NewInstance();
 186                                 authorTeam.setTitleCache(lstUpdate.get(3));
 187                                 citation.setAuthorTeam(authorTeam);
 188                                 citation.setTitle(lstUpdate.get(4));
 189
 190                                 //citation.
 191                                 TimePeriod year = TimePeriod.NewInstance(Integer.parseInt(lstUpdate.get(5)));
 192                                 citation.setDatePublished(year);
 193                                 citation.setTitleCache(lstUpdate.get(6));
 194                                 //citation.
 195                                 for(TaxonBase taxon : taxa) {
 196                                         String taxonUUID = taxon.getUuid().toString();
 197                                         //System.out.println(idTaxonToUpdate + "|" + taxonUUID);
 198                                         if(idTaxonToUpdate.equals(taxonUUID)) {
 199                                                 logger.info("Processing Taxn " + taxon.getTitleCache() + " with UUID: " + taxon.getUuid());
 200                                                 if(taxon.isInstanceOf(Synonym.class)) {
 201                                                         Taxon bestCandidate = null;
 202                                                         Synonym synonym = CdmBase.deproxy(taxon, Synonym.class);
 203                                                         Set<Taxon> acceptetdCandidates = synonym.getAcceptedTaxa();
 204                                                         if(!acceptetdCandidates.isEmpty()){
 205                                                                 bestCandidate = acceptetdCandidates.iterator().next();
 206                                                                 if(acceptetdCandidates.size() == 1){
 207                                                                         logger.info(acceptetdCandidates.size() + " Accepted taxa found for synonym " + taxon.getTitleCache() + ", using first one: " + bestCandidate.getTitleCache());
 208                                                                         Set<TaxonDescription> taxonDescriptions = bestCandidate.getDescriptions();
 209                                                                         if(!taxonDescriptions.isEmpty()) {
 210                                                                                 TaxonDescription firstDescription = taxonDescriptions.iterator().next();
 211                                                                                 //newUseSummary.addSource(null, null, citation, null);
 212                                                                                 //firstDescription.addElement(newUseSummary);
 213                                                                         }
 214                                                                         else {
 215                                                                                 logger.warn("No description container for: " + bestCandidate.getName());
 216
 217                                                                         }
 218                                                                 } else {
 219                                                                         logger.info("using accepted Taxon " +  bestCandidate.getTitleCache() + "for synonym " + taxon.getTitleCache());
 220                                                                         //List<DescriptionElementBase> descriptionElements = descService.getDescriptionElementsForTaxon((Taxon) bestCandidate, null, null, null, null, null);
 221                                                                         Set<TaxonDescription> taxonDescriptions = bestCandidate.getDescriptions();
 222                                                                         if(!taxonDescriptions.isEmpty()) {
 223                                                                                 TaxonDescription firstDescription = taxonDescriptions.iterator().next();
 224                                                                                 //newUseSummary.addSource(null, null, citation, null);
 225                                                                                 //firstDescription.addElement(newUseSummary);
 226                                                                         }
 227                                                                         else {
 228                                                                                 logger.warn("No description container for: " + bestCandidate.getName());
 229
 230                                                                         }
 231                                                                 }
 232                                                         }
 233                                                 }
 234                                                 else {
 235                                                         Taxon taxonAccepted = (Taxon) taxon;
 236                                                         /*Set<TaxonDescription> taxonDescriptions = taxonAccepted.getDescriptions();
 237                                                         if(!taxonDescriptions.isEmpty()) {
 238                                                                 TaxonDescription firstDescription = taxonDescriptions.iterator().next();
 239                                                                 //newUseSummary.addSource(null, null, citation, null);
 240                                                                 //firstDescription.addElement(newUseSummary);
 241                                                         }
 242                                                         else {
 243                                                                 logger.warn("No description container for: " + taxonAccepted.getName());
 244                                                         }*/
 245                                                         taxonAccepted.addDescription(newUseDescription);
 246                                                         service.saveOrUpdate(taxonAccepted);
 247
 248                                                 }
 249                                         }
 250                                 }
 251
 252                         }
 253                         conversation.commit(false);
 254
 255                 } catch (IOException e) {
 256                         success = false;
 257                         e.printStackTrace();
 258                 }
 259
 260                 return success;
 261
 262         }
 263
 264         private boolean loadUses() {
 265                 boolean success = true;
 266                 //String xslUseSummaryPathString = "C://workspace//Matched_UseSummary_referenceIdTaxEd_TaxonName.xls";
 267                 //String xslUseSummaryPathString = "C://workspace//testUseSummaries.xls";
 268
 269
 270                 String xslUseSummaryPathString = "//Users//alextheys//Projects//CDM_Trunk//Palm_Use_Data_Extension//CDMLib-apps//cdmlib-apps//UseImport//src//main//resources//Matched_UseSummary_referenceIdTaxEd_TaxonName.xls";
 271
 272                 //String xslUseRecordPathString = "C://workspace//UseRecordTerms_UseSummaryId.xls";
 273                 //String xslUseRecordPathString = "C://workspace//testUseRecords.xls";
 274                 //String xslUseRecordPathString = "C://workspace//test_useRecord.xls";
 275                 String xslUseRecordPathString = "//Users//alextheys//Projects//CDM_Trunk//Palm_Use_Data_Extension//CDMLib-apps//cdmlib-apps//UseImport//src//main//resources//UseRecordTerms_UseSummaryId.xls";
 276
 277                 InputStream inputStream = null;
 278
 279
 280                 CdmApplicationController applicationController = CdmApplicationController.NewInstance(dataSource());
 281                 ConversationHolder conversation = applicationController.NewConversation();
 282                 conversation.startTransaction();
 283
 284                 ITaxonService taxonService = applicationController.getTaxonService();
 285                 ITermService termService = applicationController.getTermService();
 286                 IDescriptionService descService = applicationController.getDescriptionService();
 287                 IReferenceService referenceService = applicationController.getReferenceService();
 288
 289
 290                 ArrayList<ArrayList<String>> lstUseSummaries = loadSpreadsheet(xslUseSummaryPathString);
 291                 ArrayList<ArrayList<String>> lstUseRecords = loadSpreadsheet(xslUseRecordPathString);
 292
 293                 MarkerType useMarkerType = (MarkerType) termService.find(UUID.fromString("2e6e42d9-e92a-41f4-899b-03c0ac64f039"));
 294                 Feature featureUseRecord = (Feature) termService.find(UUID.fromString("8125a59d-b4d5-4485-89ea-67306297b599"));
 295                 Feature featureUseSummary = (Feature) termService.find(UUID.fromString("6acb0348-c070-4512-a37c-67bcac016279"));
 296                 Pager<DefinedTermBase>  notAvailModPager = (Pager<DefinedTermBase> ) termService.findByTitle(Modifier.class, "N/A", null, null, null, null, null, null);
 297                 Pager<DefinedTermBase>  notAvailStatePager = (Pager<DefinedTermBase> ) termService.findByTitle(State.class, "N/A", null, null, null, null, null, null);
 298                 Modifier notAvailMod = (Modifier) notAvailModPager.getRecords().get(0);
 299                 State notAvailState = (State) notAvailStatePager.getRecords().get(0);
 300
 301                 int i = 0;
 302                 int j = 0;
 303                 try {
 304                         for (ArrayList<String> lstUseSummary : lstUseSummaries) {
 305                                 i++;
 306                                 String idTaxonToUpdate = lstUseSummary.get(3);
 307                                 TaxonBase taxon = taxonService.find(UUID.fromString(idTaxonToUpdate));
 308                                 if (taxon != null) {
 309                                         TaxonDescription newUseDescription = TaxonDescription.NewInstance();
 310                                         Marker useMarker = Marker.NewInstance(useMarkerType, true);
 311                                         newUseDescription.addMarker(useMarker);
 312                                         Reference useReference = null;
 313                                         Pager<Reference> reference = referenceService.findByTitle(Reference.class, lstUseSummary.get(5), null, null, null, null, null, null);
 314                                         if(reference.getCount() == 0) {
 315                                                 System.out.println("Reference title: " + lstUseSummary.get(5) + " not found.");
 316                                         } else if(reference.getCount() > 0 ) {
 317                                                 useReference = reference.getRecords().get(0);
 318                                         }
 319                                         IdentifiableSource source =IdentifiableSource.NewInstance(useReference, null);
 320                                         source.setOriginalNameString(taxon.getName().toString());
 321                                         newUseDescription.addSource(source);
 322                                         TextData useSummary = TextData.NewInstance(featureUseSummary);
 323                                         LanguageString languageString = LanguageString.NewInstance(lstUseSummary.get(1), Language.ENGLISH());
 324                                         useSummary.putText(languageString);
 325                                         descService.saveDescriptionElement(useSummary);
 326                                         newUseDescription.addElement(useSummary);
 327                                         for (ArrayList<String> lstUseRecord : lstUseRecords) {
 328                                                 j++;
 329                                                 //System.out.println("Processing UseSummary#: " + i + " ID:" + lstUseSummary.get(0) + "UseRecord: " + lstUseRecord.get(1));
 330                                                 if(lstUseSummary.get(0).equals(lstUseRecord.get(0))) {
 331                                                         CategoricalData useRecord = CategoricalData.NewInstance();
 332                                                         useRecord.setFeature(featureUseRecord);
 333                                                         String modifyingText = "";
 334                                                         if(lstUseRecord.get(3) != null && lstUseRecord.get(3).length() > 0) {
 335                                                                 Pager<DefinedTermBase> useCategoryPager = termService.findByTitle(State.class, lstUseRecord.get(3), null, null, null, null, null, null);
 336                                                                 State useCategory = null;
 337                                                                 if(useCategoryPager.getCount() > 0) {
 338                                                                         useCategory = (State) useCategoryPager.getRecords().get(0);
 339                                                                 } else {
 340                                                                         useCategory = notAvailState;
 341                                                                 }
 342                                                                 StateData stateCatData = StateData.NewInstance(useCategory);
 343                                                                 stateCatData.setState(useCategory);
 344                                                                 stateCatData.putModifyingText(Language.ENGLISH(), "Use Category");
 345                                                                 modifyingText += useCategory.toString() + ";";
 346                                                                 useRecord.addState(stateCatData);
 347
 348
 349                                                                 //useRecord.addState(stateData);
 350                                                         } else {
 351                                                                 State useCategory = notAvailState;
 352                                                                 StateData stateCatData = StateData.NewInstance(useCategory);
 353                                                                 stateCatData.setState(useCategory);
 354                                                                 stateCatData.putModifyingText(Language.ENGLISH(), "Use Category");
 355                                                                 modifyingText += useCategory.toString() + ";";
 356                                                                 useRecord.addState(stateCatData);
 357
 358                                                         }
 359
 360                                                         if(lstUseRecord.get(4) != null && lstUseRecord.get(4).length() > 0) {
 361                                                                 Pager<DefinedTermBase> useSubCategoryPager = termService.findByTitle(State.class, lstUseRecord.get(4), null, null, null, null, null, null);
 362                                                                 State useSubCategory = null;
 363                                                                 if(useSubCategoryPager.getCount() > 0) {
 364                                                                         useSubCategory = (State) useSubCategoryPager.getRecords().get(0);
 365
 366                                                                 } else {
 367                                                                         useSubCategory = notAvailState;
 368                                                                 }
 369                                                                 StateData stateSubCatData = StateData.NewInstance(useSubCategory);
 370                                                                 stateSubCatData.setState(useSubCategory);
 371                                                                 stateSubCatData.putModifyingText(Language.ENGLISH(), "Use SubCategory");
 372                                                                 modifyingText += useSubCategory.toString() + ";";
 373                                                                 useRecord.addState(stateSubCatData);
 374
 375                                                         }
 376                                                         else {
 377                                                                 State useSubCategory = notAvailState;
 378                                                                 StateData stateSubCatData = StateData.NewInstance(useSubCategory);
 379                                                                 stateSubCatData.setState(useSubCategory);
 380                                                                 stateSubCatData.putModifyingText(Language.ENGLISH(), "Use SubCategory");
 381                                                                 modifyingText += useSubCategory.toString() + ";";
 382                                                                 useRecord.addState(stateSubCatData);
 383
 384                                                         }
 385                                                         if(lstUseRecord.get(5) != null && lstUseRecord.get(5).length() > 0) {
 386                                                                 Pager<DefinedTermBase> countryPager = termService.findByTitle(Modifier.class, lstUseRecord.get(5), null, null, null, null, null, null);
 387                                                                 Modifier country = null;
 388                                                                 if(countryPager.getCount() > 0) {
 389                                                                         country = (Modifier) countryPager.getRecords().get(0);
 390                                                                 } else {
 391                                                                         country = notAvailMod;
 392                                                                 }
 393                                                                 modifyingText += country.toString() + ";";
 394                                                                 useRecord.addModifier(country);
 395                                                         } else {
 396                                                                 Modifier country = notAvailMod;
 397                                                                 modifyingText += country.toString() + ";";
 398                                                                 useRecord.addModifier(country);
 399                                                         }
 400
 401                                                         if(lstUseRecord.get(6) != null && lstUseRecord.get(6).length() > 0) {
 402                                                                 Pager<DefinedTermBase> plantPartPager = termService.findByTitle(Modifier.class, lstUseRecord.get(6), null, null, null, null, null, null);
 403                                                                 Modifier plantPart = null;
 404                                                                 if(plantPartPager.getCount() > 0) {
 405                                                                         plantPart = (Modifier) plantPartPager.getRecords().get(0);
 406                                                                 } else {
 407                                                                         plantPart = notAvailMod;
 408                                                                 }
 409                                                                 modifyingText += plantPart.toString() + ";";
 410                                                                 useRecord.addModifier(plantPart);
 411                                                         }else {
 412                                                                 Modifier plantPart = notAvailMod;
 413                                                                 modifyingText += plantPart.toString() + ";";
 414                                                                 useRecord.addModifier(plantPart);
 415                                                         }
 416                                                         if(lstUseRecord.get(7) != null && lstUseRecord.get(7).length() > 0) {
 417                                                                 Pager<DefinedTermBase> humanGroupPager = termService.findByTitle(Modifier.class, lstUseRecord.get(7), null, null, null, null, null, null);
 418                                                                 Modifier humanGroup = null;
 419                                                                 if(humanGroupPager.getCount() > 0) {
 420                                                                         humanGroup = (Modifier) humanGroupPager.getRecords().get(0);
 421                                                                 } else {
 422                                                                         humanGroup = notAvailMod;
 423                                                                 }
 424                                                                 modifyingText += humanGroup.toString() + ";";
 425                                                                 useRecord.addModifier(humanGroup);
 426                                                         } else {
 427                                                                 Modifier humanGroup = notAvailMod;
 428                                                                 modifyingText += humanGroup.toString() + ";";
 429                                                                 useRecord.addModifier(humanGroup);
 430                                                         }
 431                                                         if(lstUseRecord.get(8) != null && lstUseRecord.get(8).length() > 0) {
 432                                                                 Pager<DefinedTermBase> ethnicGroupPager = termService.findByTitle(Modifier.class, lstUseRecord.get(8), null, null, null, null, null, null);
 433                                                                 Modifier ethnicGroup = null;
 434                                                                 if(ethnicGroupPager.getCount() > 0) {
 435                                                                         ethnicGroup = (Modifier) ethnicGroupPager.getRecords().get(0);
 436                                                                         modifyingText += ethnicGroup.toString() + ";";
 437                                                                 } else {
 438                                                                         ethnicGroup = notAvailMod;
 439                                                                 }
 440                                                                 useRecord.addModifier(ethnicGroup);
 441                                                         }
 442                                                         else {
 443                                                                 Modifier ethnicGroup = notAvailMod;
 444                                                                 modifyingText += ethnicGroup.toString() + ";";
 445                                                                 useRecord.addModifier(ethnicGroup);
 446                                                         }
 447                                                         useRecord.putModifyingText(Language.ENGLISH(), modifyingText);
 448                                                         descService.saveDescriptionElement(useRecord);
 449                                                         newUseDescription.addElement(useRecord);
 450                                                 }
 451                                         }
 452
 453
 454
 455                                         if (taxon.isInstanceOf(Synonym.class)){
 456                                                 Taxon bestCandidate = null;
 457                                                 Synonym synonym = CdmBase.deproxy(taxon, Synonym.class);
 458                                                 Set<Taxon> acceptetdCandidates = synonym.getAcceptedTaxa();
 459                                                 if(!acceptetdCandidates.isEmpty()){
 460                                                         bestCandidate = acceptetdCandidates.iterator().next();
 461                                                         if(acceptetdCandidates.size() == 1){
 462                                                                 logger.info(acceptetdCandidates.size() + " Accepted taxa found for synonym " + taxon.getTitleCache() + ", using first one: " + bestCandidate.getTitleCache());
 463                                                                 bestCandidate.addDescription(newUseDescription);
 464                                                                 taxonService.saveOrUpdate(bestCandidate);
 465                                                                 conversation.commit();
 466                                                         }
 467                                                         else {
 468                                                                 logger.info("using accepted Taxon " +  bestCandidate.getTitleCache() + "for synonym " + taxon.getTitleCache());
 469                                                                 bestCandidate.addDescription(newUseDescription);
 470                                                                 taxonService.saveOrUpdate(bestCandidate);
 471                                                                 conversation.commit();
 472                                                         }
 473                                                 }
 474                                         } else {
 475                                                 Taxon taxonAccepted = (Taxon) taxon;
 476                                                 taxonAccepted.addDescription(newUseDescription);
 477                                                 taxonService.saveOrUpdate(taxonAccepted);
 478                                                 conversation.commit();
 479                                         }
 480                                 }
 481                                 else {
 482                                         System.out.println("Processing UseSummary#: " + i + " ID:" + lstUseSummary.get(0));
 483                                 }
 484                         }
 485
 486                         conversation.close();
 487                         applicationController.close();
 488
 489                 } catch (Exception e) {
 490                         success = false;
 491                         e.printStackTrace();
 492                 }
 493                 return success;
 494
 495         }
 496
 497         //Completed and tested!
 498         private boolean loadTerms() {
 499                 boolean success = true;
 500
 501                 //String xslPathString = "C://workspace//terms.xls";
 502                 String xslPathString = "//Users//alextheys//Projects//CDM_Trunk//Palm_Use_Data_Extension//CDMLib-apps//cdmlib-apps//UseImport//src//main//resources//terms.xls";
 503
 504                 CdmApplicationController applicationController = CdmApplicationController.NewInstance(dataSource());
 505                 ConversationHolder conversation = applicationController.NewConversation();
 506                 conversation.startTransaction();
 507
 508                 ITaxonService service = applicationController.getTaxonService();
 509                 ITermService termService = applicationController.getTermService();
 510                 IVocabularyService vocabularyService = applicationController.getVocabularyService();
 511                 IReferenceService referenceService = applicationController.getReferenceService();
 512
 513                 TermVocabulary<State> stateVocabulary =  (TermVocabulary<State>) vocabularyService.find(UUID.fromString("67430d7c-fd43-4e9d-af5e-d0dca3f74931"));
 514                 TermVocabulary<Modifier> countryVocabulary = (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("116c51f1-e63a-46f7-a258-e1149a42868b"));
 515                 TermVocabulary<Modifier> plantPartVocabulary = (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("369914fe-d54b-4063-99ce-abc81d30ad35"));
 516                 TermVocabulary<Modifier> humanGroupVocabulary =  (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("ca46cea5-bdf7-438d-9cd8-e2793d2178dc"));
 517
 518                 IDescriptionService descService = applicationController.getDescriptionService();
 519                 InputStream inputStream = null;
 520
 521                 try {
 522                         inputStream = new FileInputStream(xslPathString);
 523
 524                 } catch (FileNotFoundException e) {
 525                         success = false;
 526                         System.out.println("File not found in the specified path.");
 527                         e.printStackTrace();
 528                 }
 529
 530                 POIFSFileSystem fileSystem = null;
 531
 532                 try {
 533                         fileSystem = new POIFSFileSystem(inputStream);
 534
 535                         HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
 536                         HSSFSheet sheet = workBook.getSheetAt(0);
 537                         Iterator rows = sheet.rowIterator();
 538
 539                         ArrayList<ArrayList<String>> lstUpdates = new ArrayList<ArrayList<String>>();
 540
 541                         while (rows.hasNext()) {
 542
 543                                 HSSFRow row = (HSSFRow) rows.next();
 544                                 System.out.println("Row No.: " + row.getRowNum());
 545                                 Iterator cells = row.cellIterator();
 546                                 ArrayList<String> lstTerms = new ArrayList<String>();
 547                                 while (cells.hasNext()) {
 548                                         HSSFCell cell = (HSSFCell) cells.next();
 549
 550                                         int intCellType = cell.getCellType();
 551                                         switch (intCellType) {
 552                                                 case 0:
 553                                                         int cellValue = (int) cell.getNumericCellValue();
 554                                                         lstTerms.add(Integer.toString(cellValue));
 555                                                         break;
 556                                                 case 1:
 557                                                         lstTerms.add(cell.getStringCellValue());
 558                                                         break;
 559                                         }
 560                                 }
 561                                 lstUpdates.add(lstTerms);
 562                                 //lstTaxonIDs.add(Integer.parseInt(lstTaxon.get(0)));
 563                         }
 564                         for (ArrayList<String> lstUpdate : lstUpdates) {
 565                                 int termType = Integer.parseInt(lstUpdate.get(0));
 566                                 switch (termType) {
 567                                 //Case 0 = UseCategory
 568                                 case 0:
 569                                         Pager<State> useCategoryPager = termService.findByRepresentationText(lstUpdate.get(1), State.class, null, null);
 570                                         State useCategory = null;
 571                                         State useSubCat = null;
 572                                         if (useCategoryPager.getCount()>0) {
 573                                                 useCategory = useCategoryPager.getRecords().get(0);
 574                                         }
 575                                         if(useCategory == null) {
 576                                                 useCategory = State.NewInstance(lstUpdate.get(1), lstUpdate.get(1), null);
 577                                         }
 578                                         //State useCategory = (State) termService.
 579                                         if(lstUpdate.size() > 2) {
 580                                                 useSubCat = State.NewInstance(lstUpdate.get(2), lstUpdate.get(2), null);
 581                                                 useCategory.addIncludes(useSubCat);
 582                                         }
 583                                         stateVocabulary.addTerm(useCategory);
 584                                         vocabularyService.saveOrUpdate(stateVocabulary);
 585                                         conversation.commit(true);
 586                                         break;
 587
 588                                 //case 1: = HumanGroup
 589                                 case 1:
 590                                         Pager<Modifier> humanGroupPager = termService.findByRepresentationText(lstUpdate.get(1), Modifier.class, null, null);
 591
 592                                         Modifier humanGroup = null;
 593                                         Modifier ethnicGroup = null;
 594                                         if(humanGroupPager.getCount()>0) {
 595                                                 humanGroup = humanGroupPager.getRecords().get(0);
 596                                         }
 597
 598                                         if(humanGroup == null) {
 599                                                 humanGroup = Modifier.NewInstance(lstUpdate.get(1), lstUpdate.get(1), null);
 600                                         }
 601
 602                                         if(lstUpdate.size() >2) {
 603                                                 ethnicGroup = Modifier.NewInstance(lstUpdate.get(2), lstUpdate.get(2), null);
 604                                                 humanGroup.addIncludes(ethnicGroup);
 605                                         }
 606                                         humanGroupVocabulary.addTerm(humanGroup);
 607                                         vocabularyService.saveOrUpdate(humanGroupVocabulary);
 608                                         conversation.commit(true);
 609                                         break;
 610
 611                                 //case 2: = Country
 612                                 case 2:
 613                                         Pager<Modifier> countryPager = termService.findByRepresentationText(lstUpdate.get(1), Modifier.class, null, null);
 614                                         Modifier country = null;
 615
 616                                         if(countryPager.getCount()>0) {
 617                                                 country = countryPager.getRecords().get(0);
 618                                         }
 619
 620                                         if(country == null) {
 621                                                 country = Modifier.NewInstance(lstUpdate.get(1), lstUpdate.get(1), null);
 622                                                 countryVocabulary.addTerm(country);
 623                                                 vocabularyService.saveOrUpdate(countryVocabulary);
 624                                         }
 625                                         conversation.commit(true);
 626                                         break;
 627
 628                                 //case 3: //plantPart
 629                                 case 3:
 630                                         Pager<Modifier> plantPartPager = termService.findByRepresentationText(lstUpdate.get(1), Modifier.class, null, null);
 631                                         Modifier plantPart = null;
 632
 633                                         if(plantPartPager.getCount()>0) {
 634                                                 plantPart = plantPartPager.getRecords().get(0);
 635                                         }
 636
 637                                         if(plantPart == null) {
 638                                                 plantPart = Modifier.NewInstance(lstUpdate.get(1), lstUpdate.get(1), null);
 639                                                 plantPartVocabulary.addTerm(plantPart);
 640                                                 vocabularyService.saveOrUpdate(plantPartVocabulary);
 641                                         }
 642                                         conversation.commit(true);
 643                                         break;
 644
 645                                 }
 646                         }
 647                         conversation.close();
 648                         applicationController.close();
 649
 650                 } catch (IOException e) {
 651                         success = false;
 652                         e.printStackTrace();
 653                 }
 654                 return success;
 655
 656         }
 657
 658         private ArrayList<ArrayList<String>> loadSpreadsheet(String xslPathString) {
 659                 ArrayList<ArrayList<String>> lstUpdates = new ArrayList<ArrayList<String>>();
 660                 InputStream inputStream = null;
 661
 662                 try {
 663                         inputStream = new FileInputStream(xslPathString);
 664
 665                 } catch (FileNotFoundException e) {
 666                         System.out.println("File not found in the specified path.");
 667                         e.printStackTrace();
 668                 }
 669
 670                 POIFSFileSystem fileSystem = null;
 671
 672                 try {
 673                         fileSystem = new POIFSFileSystem(inputStream);
 674
 675                         HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
 676                         HSSFSheet sheet = workBook.getSheetAt(0);
 677                         Iterator rows = sheet.rowIterator();
 678                         // Iterator rows = sheet.rowIterator();
 679                         //Set<Integer> lstTaxonIDs;
 680
 681
 682                         while (rows.hasNext()) {
 683
 684                                 HSSFRow row = (HSSFRow) rows.next();
 685                                 System.out.println("Row No.: " + row.getRowNum());
 686                                 Iterator cells = row.cellIterator();
 687                                 ArrayList<String> lstTerms = new ArrayList<String>();
 688                                 while (cells.hasNext()) {
 689                                         HSSFCell cell = (HSSFCell) cells.next();
 690
 691                                         int intCellType = cell.getCellType();
 692                                         switch (intCellType) {
 693                                                 case 0:
 694                                                         int cellValue = (int) cell.getNumericCellValue();
 695                                                         lstTerms.add(Integer.toString(cellValue));
 696                                                         break;
 697                                                 case 1:
 698                                                         lstTerms.add(cell.getStringCellValue());
 699                                                         break;
 700                                         }
 701                                 }
 702                                 lstUpdates.add(lstTerms);
 703                                 //lstTaxonIDs.add(Integer.parseInt(lstTaxon.get(0)));
 704                         }
 705                 } catch (IOException e) {
 706                         e.printStackTrace();
 707                 }
 708                 return lstUpdates;
 709         }
 710
 711
 712         private boolean setupNecessaryItems() {
 713                 boolean success = false;
 714                 CdmApplicationController applicationController = CdmApplicationController.NewInstance(dataSource());
 715                 ConversationHolder conversation = applicationController.NewConversation();
 716
 717
 718                 ITaxonService service = applicationController.getTaxonService();
 719                 ITermService termService = applicationController.getTermService();
 720                 IVocabularyService vocabularyService = applicationController.getVocabularyService();
 721                 IFeatureTreeService featureTreeService = applicationController.getFeatureTreeService();
 722
 723                 MarkerType existingMarkertype = (MarkerType)termService.find(UUID.fromString("2e6e42d9-e92a-41f4-899b-03c0ac64f039"));
 724                 Feature featureUseRecord = (Feature) termService.find(UUID.fromString("8125a59d-b4d5-4485-89ea-67306297b599"));
 725                 Feature featureUseSummary = (Feature) termService.find(UUID.fromString("6acb0348-c070-4512-a37c-67bcac016279"));
 726                 TermVocabulary<State> stateVocabulary =  (TermVocabulary<State>) vocabularyService.find(UUID.fromString("67430d7c-fd43-4e9d-af5e-d0dca3f74931"));
 727                 TermVocabulary<Modifier> countryVocabulary = (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("116c51f1-e63a-46f7-a258-e1149a42868b"));
 728                 TermVocabulary<Modifier> plantPartVocabulary = (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("369914fe-d54b-4063-99ce-abc81d30ad35"));
 729                 TermVocabulary<Modifier> humanGroupVocabulary =  (TermVocabulary<Modifier>) vocabularyService.find(UUID.fromString("ca46cea5-bdf7-438d-9cd8-e2793d2178dc"));
 730                 Pager<DefinedTermBase>  notAvailModPager = (Pager<DefinedTermBase> ) termService.findByTitle(Modifier.class, "N/A", null, null, null, null, null, null);
 731                 Pager<DefinedTermBase>  notAvailStatePager = (Pager<DefinedTermBase> ) termService.findByTitle(State.class, "N/A", null, null, null, null, null, null);
 732
 733                 conversation.startTransaction();
 734                 if (existingMarkertype == null) {
 735                         existingMarkertype = MarkerType.NewInstance("use", "use", null);
 736                         existingMarkertype.setUuid( UUID.fromString("2e6e42d9-e92a-41f4-899b-03c0ac64f039"));
 737                         TermVocabulary<MarkerType> markerTypeVocabulary = (TermVocabulary<MarkerType>)vocabularyService.find((UUID.fromString("19dffff7-e142-429c-a420-5d28e4ebe305")));
 738                         markerTypeVocabulary.addTerm(existingMarkertype);
 739                         vocabularyService.saveOrUpdate(markerTypeVocabulary);
 740                         conversation.commit(true);
 741                 }
 742                 if (stateVocabulary == null) {
 743
 744                         URI termSourceUri = null;
 745                         try {
 746                                 termSourceUri = new URI("eu.etaxonomy.cdm.model.description.State");
 747                         } catch (URISyntaxException e) {
 748                                 e.printStackTrace();
 749                         }
 750                         stateVocabulary = TermVocabulary.NewInstance("Use Category", "Use Category", null, termSourceUri);
 751                         stateVocabulary.setUuid(UUID.fromString("67430d7c-fd43-4e9d-af5e-d0dca3f74931"));
 752                         vocabularyService.saveOrUpdate(stateVocabulary);
 753                         conversation.commit(true);
 754                 }
 755                 if (countryVocabulary == null) {
 756                         URI termSourceUri = null;
 757                         try {
 758                                 termSourceUri = new URI("eu.etaxonomy.cdm.model.description.Modifier");
 759                         } catch (URISyntaxException e) {
 760                                 e.printStackTrace();
 761                         }
 762                         countryVocabulary = TermVocabulary.NewInstance("Country", "Country", null, termSourceUri);
 763                         countryVocabulary.setUuid(UUID.fromString("116c51f1-e63a-46f7-a258-e1149a42868b"));
 764
 765                         vocabularyService.saveOrUpdate(countryVocabulary);
 766                         conversation.commit(true);
 767                 }
 768                 if (plantPartVocabulary == null) {
 769                         URI termSourceUri = null;
 770                         try {
 771                                 termSourceUri = new URI("eu.etaxonomy.cdm.model.description.Modifier");
 772                         } catch (URISyntaxException e) {
 773                                 e.printStackTrace();
 774                         }
 775                         plantPartVocabulary = TermVocabulary.NewInstance("Plant Part", "Plant Part", null, termSourceUri);
 776                         plantPartVocabulary.setUuid(UUID.fromString("369914fe-d54b-4063-99ce-abc81d30ad35"));
 777                         vocabularyService.saveOrUpdate(plantPartVocabulary);
 778                         conversation.commit(true);
 779                 }
 780                 if (humanGroupVocabulary == null) {
 781                         URI termSourceUri = null;
 782                         try {
 783                                 termSourceUri = new URI("eu.etaxonomy.cdm.model.description.Modifier");
 784                         } catch (URISyntaxException e) {
 785                                 e.printStackTrace();
 786                         }
 787                         humanGroupVocabulary = TermVocabulary.NewInstance("Human Group", "Human Group", null, termSourceUri);
 788                         humanGroupVocabulary.setUuid(UUID.fromString("ca46cea5-bdf7-438d-9cd8-e2793d2178dc"));
 789                         vocabularyService.saveOrUpdate(humanGroupVocabulary);
 790                         conversation.commit(true);
 791                 }
 792                 if(featureUseRecord == null|| featureUseSummary == null) {
 793                         TermVocabulary<Feature> featureVocabulary = (TermVocabulary<Feature>)vocabularyService.find((UUID.fromString("b187d555-f06f-4d65-9e53-da7c93f8eaa8")));
 794                         FeatureTree palmWebFeatureTree = featureTreeService.find(UUID.fromString("72ccce05-7cc8-4dab-8e47-bf3f5fd848a0"));
 795                         //List<FeatureTree> featureTrees = CdmStore.getService(IFeatureTreeService.class).list(FeatureTree.class, null, null, null, null);
 796
 797                         if (featureUseRecord == null ) {
 798                                 featureUseRecord = Feature.NewInstance("Use Record", "Use Record", null);
 799                                 featureUseRecord.setUuid(UUID.fromString("8125a59d-b4d5-4485-89ea-67306297b599"));
 800                                 featureUseRecord.isSupportsCategoricalData();
 801                                 featureUseRecord.setSupportsCategoricalData(true);
 802                                 featureVocabulary.addTerm(featureUseRecord);
 803                                 FeatureNode useRecFeatureNode = FeatureNode.NewInstance(featureUseRecord);
 804                                 palmWebFeatureTree.getRoot().addChild(useRecFeatureNode);
 805                         }
 806                         if (featureUseSummary == null) {
 807                                 featureUseSummary = Feature.NewInstance("Use", "Use", null);
 808                                 featureUseSummary.setUuid(UUID.fromString("6acb0348-c070-4512-a37c-67bcac016279"));
 809                                 featureUseSummary.isSupportsTextData();
 810                                 featureUseSummary.setSupportsTextData(true);
 811                                 //TermVocabulary<Feature> featureVocabulary = (TermVocabulary<Feature>)CdmStore.getService(IVocabularyService.class).find((UUID.fromString("b187d555-f06f-4d65-9e53-da7c93f8eaa8")));
 812                                 featureVocabulary.addTerm(featureUseSummary);
 813                                 FeatureNode useSumFeatureNode = FeatureNode.NewInstance(featureUseSummary);
 814                                 palmWebFeatureTree.getRoot().addChild(useSumFeatureNode);
 815                         }
 816
 817                         vocabularyService.saveOrUpdate(featureVocabulary);
 818                         featureTreeService.saveOrUpdate(palmWebFeatureTree);
 819                         conversation.commit(true);
 820
 821                 }
 822                 if(notAvailModPager.getCount() == 0) {
 823                         Modifier notAvailMod = Modifier.NewInstance("N/A", "N/A", null);
 824                         termService.saveOrUpdate(notAvailMod);
 825                         conversation.commit(true);
 826                 }
 827
 828                 if(notAvailStatePager.getCount() == 0) {
 829                         State notAvailState = State.NewInstance("N/A", "N/A", null);
 830                         termService.saveOrUpdate(notAvailState);
 831                         conversation.commit(true);
 832                 }
 833                 /*if(featureUseRecord == null) {
 834                         featureUseRecord = Feature.NewInstance("Use Record", "Use Record", null);
 835                         featureUseRecord.setUuid(UUID.fromString("8125a59d-b4d5-4485-89ea-67306297b599"));
 836                         featureUseRecord.isSupportsCategoricalData();
 837                         featureUseRecord.setSupportsCategoricalData(true);
 838                         //TermVocabulary<Feature> featureVocabulary = (TermVocabulary<Feature>)vocabularyService.find((UUID.fromString("b187d555-f06f-4d65-9e53-da7c93f8eaa8")));
 839                         featureVocabulary.addTerm(featureUseRecord);
 840                         FeatureTree palmWebFeatureTree = featureTreeService.find(UUID.fromString("72ccce05-7cc8-4dab-8e47-bf3f5fd848a0"));
 841                         FeatureNode useRecFeatureNode = FeatureNode.NewInstance(featureUseRecord);
 842                         palmWebFeatureTree.getRoot().addChild(useRecFeatureNode);
 843                         vocabularyService.saveOrUpdate(featureVocabulary);
 844                         featureTreeService.saveOrUpdate(palmWebFeatureTree);
 845                         conversation.commit(true);
 846
 847                 }
 848                 if(featureUseSummary == null) {
 849                         featureUseSummary = Feature.NewInstance("Use Summary", "Use Summary", null);
 850                         featureUseSummary.setUuid(UUID.fromString("6acb0348-c070-4512-a37c-67bcac016279"));
 851                         featureUseSummary.isSupportsTextData();
 852                         featureUseSummary.setSupportsTextData(true);
 853                         //TermVocabulary<Feature> featureVocabulary = (TermVocabulary<Feature>)vocabularyService.find((UUID.fromString("b187d555-f06f-4d65-9e53-da7c93f8eaa8")));
 854                         featureVocabulary.addTerm(featureUseSummary);
 855                         FeatureTree palmWebFeatureTree = featureTreeService.find(UUID.fromString("72ccce05-7cc8-4dab-8e47-bf3f5fd848a0"));
 856                         FeatureNode useRecFeatureNode = FeatureNode.NewInstance(featureUseSummary);
 857                         palmWebFeatureTree.getRoot().addChild(useRecFeatureNode);
 858                         vocabularyService.saveOrUpdate(featureVocabulary);
 859                         featureTreeService.saveOrUpdate(palmWebFeatureTree);
 860                         conversation.commit(true);
 861                 }*/
 862
 863                 conversation.close();
 864                 applicationController.close();
 865
 866                 return success;
 867         }
 868
 869         public static void main(String[] args) {
 870                 UseImport uiImport = new UseImport();
 871                 // String xlsPath = ".//toload.xlsx";
 872                 //String xlsPath = "C://workspace//CDM Trunk//UseImport//src//main//java//eu//etaxonomy//cdm//toLoad2.xls";
 873
 874                 uiImport.setupNecessaryItems();
 875                 uiImport.loadTerms();
 876                 uiImport.loadUses();
 877                 //String xlsPath = "C://workspace//toLoad3.xls";
 878                 //uiImport.importFromExcelSS(xlsPath);
 879
 880         }
 881 }