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