EDIT

    static final ICdmDataSource cdmDestination = CdmDestinations.localH2();

//    static final ICdmDataSource cdmDestination = CdmDestinations.cdm_greece_checklist_production();

//    private static final UUID sourceUuid = UUID.fromString("1964a2e5-9ec3-411e-a36a-44279230c4b9");

    private static final UUID sourceUuid = UUID.fromString("3430d54b-1a51-4a0d-9d9f-38178f1e4de3");

//    private static final String fileName = "20171110_Turland_433_others_59.xlsx";

//    private static final String fileName = "20180824_Metadata_Zarkos_images_July_2018_b.xlsx";

//    private static final String fileName = "2018_Aug_images.xlsx";

    private static final String fileName = "20180925_FOG_images.xlsx";

//    NOTE!!: Darauf achten, dass die Header case sensitiv sind und keine Leerzeichen am Ende sein sollten, trim funktioniert seltsamerweise nicht immer

/**

* Copyright (C) 2018 EDIT

* European Distributed Institute of Taxonomy

* http://www.e-taxonomy.eu

*

* The contents of this file are subject to the Mozilla Public License Version 1.1

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.app.greece;

import java.net.URI;

import java.util.UUID;

import org.apache.log4j.Logger;

import eu.etaxonomy.cdm.app.common.CdmDestinations;

import eu.etaxonomy.cdm.database.DatabaseTypeEnum;

import eu.etaxonomy.cdm.database.DbSchemaValidation;

import eu.etaxonomy.cdm.database.ICdmDataSource;

import eu.etaxonomy.cdm.io.common.CdmDefaultImport;

import eu.etaxonomy.cdm.io.common.IImportConfigurator.CHECK;

import eu.etaxonomy.cdm.io.common.ImportResult;

import eu.etaxonomy.cdm.io.greece.GreeceWillingImportConfigurator;

import eu.etaxonomy.cdm.model.name.NomenclaturalCode;

import eu.etaxonomy.cdm.model.reference.Reference;

import eu.etaxonomy.cdm.model.reference.ReferenceFactory;

/**

 * @author a.mueller

 * @since 21.08.2018

 *

 */

public class GreeceWillingActivator {

    @SuppressWarnings("unused")

    private static final Logger logger = Logger.getLogger(GreeceWillingActivator.class);

    static final ICdmDataSource cdmDestination = CdmDestinations.localH2();

//    static final ICdmDataSource cdmDestination = CdmDestinations.cdm_greece_checklist_production();

    private static final UUID sourceUuid = UUID.fromString("b96b8a10-e2a5-4a01-b2f2-435a59a7a269");

    private static final String fileName = "WillingImport.xslx";

    private void doImport(ICdmDataSource cdmDestination){

        DbSchemaValidation schemaVal = cdmDestination.getDatabaseType() == DatabaseTypeEnum.H2 ? DbSchemaValidation.CREATE : DbSchemaValidation.VALIDATE;

        URI source = greekChecklist();  //just any

        //make Source

        GreeceWillingImportConfigurator config = GreeceWillingImportConfigurator.NewInstance(source, cdmDestination);

        config.setCheck(CHECK.IMPORT_WITHOUT_CHECK);

        config.setDbSchemaValidation(schemaVal);

        config.setSourceReference(getSourceReference());

        config.setNomenclaturalCode(NomenclaturalCode.ICNAFP);

        CdmDefaultImport<GreeceWillingImportConfigurator> myImport = new CdmDefaultImport<>();

        ImportResult result = myImport.invoke(config);

        System.out.println(result.createReport());

    }

    private URI greekChecklist(){

        return URI.create("file:////BGBM-PESIHPC/Greece/" + fileName);

    }

    private Reference getSourceReference(){

        Reference result = ReferenceFactory.newDatabase();

//        xx;

        result.setTitle(fileName);

        result.setUuid(sourceUuid);

        return result;

    }

    /**

     * @param args

     */

    public static void main(String[] args) {

        GreeceWillingActivator me = new GreeceWillingActivator();

        me.doImport(cdmDestination);

        System.exit(0);

    }

}

		    result += " WHERE AreaID NOT IN (1, 21, 650, 653, 1718, 654, 646, 647) ";  //#3986

            		" occ.Cultivated, occ.StatusUnknown, occ.WorldDistCompl, occ.Notes occNotes, " +

import eu.etaxonomy.cdm.common.CdmUtils;

import eu.etaxonomy.cdm.model.name.NameRelationshipType;

import eu.etaxonomy.cdm.model.name.NomenclaturalStatus;

import eu.etaxonomy.cdm.model.name.NomenclaturalStatusType;

import eu.etaxonomy.cdm.model.name.TaxonName;

import eu.etaxonomy.cdm.model.reference.ReferenceFactory;

import eu.etaxonomy.cdm.strategy.parser.NonViralNameParserImpl;

import eu.etaxonomy.cdm.strategy.parser.TimePeriodParser;

	private static final Integer AUCT_REF_ID = 5959;

				Integer ptRefFk1 = nullSafeInt(rs, "PTRefFk1");

								boolean isProParte = "p.p.".equals(notes);

							    if (isProParte){

								    notes = null;

								}

							    boolean isDoubtful = "?".equals(notes);

                                if (isDoubtful){

                                    notes = null;

                                }

                                if (notes!= null && notes.startsWith("{non ") && notes.endsWith("}")){

                                    notes = notes.substring(1, notes.length() - 1);

                                }

							    //handle auct. author

						        if (fromTaxon.getSec() == null || fromTaxon.getSec().getTitleCache().startsWith("auct.")){

							        String existingSecTitle = fromTaxon.getSec() == null ? null : fromTaxon.getSec().getTitleCache().trim();

						            String existingAppendedPhrase = fromTaxon.getAppendedPhrase();

							        if (fromTaxon.getSec() == null && isBlank(existingAppendedPhrase)){

							            existingAppendedPhrase = "auct.";

							        }

						            fromTaxon.setSec(null);

							        if (isNotBlank(existingAppendedPhrase) && isNotBlank(notes)){

							            logger.warn("Misapplied name has >1 MA relation with a note, RelId: " + relPTaxonId);

							        }

							        String newAppendedPhrase = CdmUtils.concat(", ", existingSecTitle, notes);

							        fromTaxon.setAppendedPhrase(CdmUtils.concat("; ", existingAppendedPhrase, newAppendedPhrase));

							        if (isBlank(fromTaxon.getAppendedPhrase())){

							            logger.warn("Appended phrase is empty. This is probably not correct. RelID: " + relPTaxonId);

							        }else if ("auct.".equals(fromTaxon.getAppendedPhrase())){

							            fromTaxon.setAppendedPhrase(null);

							        }

							        notes = null;

							    }else if (notes != null && notes.startsWith("non ")){

							        fromTaxon.setAppendedPhrase(CdmUtils.concat(", ", fromTaxon.getAppendedPhrase(), notes));

							        notes = null;

							    }

							    if (isProParte){

							        taxonRelationship = toTaxon.addProParteMisappliedName(fromTaxon, citation, microcitation);

							    }else{

							        taxonRelationship = toTaxon.addMisappliedName(fromTaxon, citation, microcitation);

	                            }

							    if (isDoubtful){

							        ((TaxonRelationship)taxonRelationship).setDoubtful(true);

							    }

							//

							notes = handleSynonymNotes(state, toTaxon, synonym, notes, relPTaxonId);

						if (taxonRelationship != null && isNotBlank(notes)){

						}

						if (isNotBlank(notes)){

						    logger.warn("Notes in RelPTaxon should all be handled explicitly and should not exist as notes anymore. RelID: " + relPTaxonId + ". Note: " + notes);

	/**

     * @param toTaxon

     * @param synonym

     * @param notes

     * @return

     */

    private String handleSynonymNotes(BerlinModelImportState state, Taxon toTaxon, Synonym synonym, String notes, int relId) {

        if (state.getConfig().isEuroMed() && isNotBlank(notes)){

            notes = notes.trim();

            if (notes.startsWith("[non ") && notes.endsWith("]")){

                notes = notes.substring(5, notes.length()-1).trim();

                String[] splits = notes.split(", nec ");

                for (String split : splits){

                    String nameStr = split.replace("<i>", "").replace("</i>", "");

                    NonViralNameParserImpl parser = NonViralNameParserImpl.NewInstance();

                    TaxonName name;

                    NomenclaturalStatusType status = null;

                    if (nameStr.endsWith(", nom. rej.") || nameStr.endsWith(", nom. cons.")||nameStr.endsWith(", nom. illeg.")){

                        String statusStr = nameStr.endsWith(", nom. rej.")? ", nom. rej.":

                            nameStr.endsWith(", nom. cons.")? ", nom. cons.":

                            ", nom. illeg.";

                        nameStr = nameStr.replace(statusStr, "");

                        statusStr = statusStr.replace(", ", "");

                        try {

                            status = NomenclaturalStatusType.getNomenclaturalStatusTypeByAbbreviation(statusStr, null);

                        } catch (UnknownCdmTypeException e) {

                            logger.warn("NomStatusType not recognized: "+  statusStr + ", RelId: " +  relId);

                        }

                    }

                    if (nameStr.contains(",") || nameStr.contains(" in ") ){

                        name = parser.parseReferencedName(nameStr, state.getConfig().getNomenclaturalCode(), null);

                    }else if (nameStr.matches(".*\\s\\d{4}")){

                        String nameStr2 = nameStr.substring(0, nameStr.length() - 5).trim();

                        String yearStr = nameStr.substring(nameStr.length()-4);

                        name = (TaxonName)parser.parseFullName(nameStr2, state.getConfig().getNomenclaturalCode(), null);

                        Reference nomRef = name.getNomenclaturalReference();

                        if (nomRef == null){

                            nomRef = ReferenceFactory.newGeneric();

                            name.setNomenclaturalReference(nomRef);

                        }

                        nomRef.setDatePublished(TimePeriodParser.parseStringVerbatim(yearStr));

                    }else if (nameStr.endsWith(" 1831-1832")){

                        String nameStr2 = nameStr.substring(0, nameStr.length() - 10).trim();

                        name = (TaxonName)parser.parseFullName(nameStr2, state.getConfig().getNomenclaturalCode(), null);

                        Reference nomRef = name.getNomenclaturalReference();

                        if (nomRef == null){

                            nomRef = ReferenceFactory.newGeneric();

                            name.setNomenclaturalReference(nomRef);

                        }

                        nomRef.setDatePublished(TimePeriodParser.parseStringVerbatim("1831-1832"));

                    }else{

                        name = parser.parseReferencedName(nameStr, state.getConfig().getNomenclaturalCode(), null);

                    }

                    if (name.isProtectedTitleCache() || name.isProtectedNameCache()

                            || name.getNomenclaturalReference() != null && (name.getNomenclaturalReference().isProtectedAbbrevTitleCache()|| name.getNomenclaturalReference().isProtectedTitleCache() )){

                        logger.warn("Blocking name for synonym relation could not be parsed: " + nameStr + ", RelId: "+ relId);

                    }

                    if (status != null){

                        name.addStatus(NomenclaturalStatus.NewInstance(status));

                    }

                    synonym.getName().addRelationshipFromName(name, NameRelationshipType.BLOCKING_NAME_FOR(), null, null, null);

                    getNameService().saveOrUpdate(name);

                }

                return null;

            }else{

                return notes;

            }

        }else{

            return notes;

        }

    }

    private void handleAllRelatedTaxa(BerlinModelImportState state, Taxon taxon,

            Map<Integer, Classification> classificationMap, Integer secRefFk) {

					" FROM emCommonName cn INNER JOIN PTaxon pt ON pt.PTNameFk = cn.PTNameFk AND pt.PTRefFk = cn.PTRefFk " +

					" WHERE (cn.LanguageRefFk NOT IN " +

						"(cn.LanguageRefFk is NOT NULL) AND "

						+ " cn.LanguageRefFk <> cn.RefFk "

						+ " AND pt.statusFk NOT IN (6) ";

				System.out.println("============================================================================");

				System.out.println("There are " + count + " common names that have a languageRefFk which can not be found in the emLanguageReference table AND are not equal to RefFk.");

				System.out.println("============================================================================");

/**

* Copyright (C) 2018 EDIT

* European Distributed Institute of Taxonomy

* http://www.e-taxonomy.eu

*

* The contents of this file are subject to the Mozilla Public License Version 1.1

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.io.greece;

import java.net.MalformedURLException;

import java.net.URI;

import java.text.ParseException;

import java.util.List;

import java.util.Map;

import java.util.UUID;

import org.apache.log4j.Logger;

import eu.etaxonomy.cdm.api.facade.DerivedUnitFacade;

import eu.etaxonomy.cdm.api.service.config.MatchingTaxonConfigurator;

import eu.etaxonomy.cdm.io.common.utils.ImportDeduplicationHelper;

import eu.etaxonomy.cdm.io.mexico.SimpleExcelTaxonImport;

import eu.etaxonomy.cdm.io.mexico.SimpleExcelTaxonImportState;

import eu.etaxonomy.cdm.model.agent.Team;

import eu.etaxonomy.cdm.model.common.Language;

import eu.etaxonomy.cdm.model.common.TimePeriod;

import eu.etaxonomy.cdm.model.description.IndividualsAssociation;

import eu.etaxonomy.cdm.model.description.TaxonDescription;

import eu.etaxonomy.cdm.model.location.Country;

import eu.etaxonomy.cdm.model.media.Media;

import eu.etaxonomy.cdm.model.occurrence.Collection;

import eu.etaxonomy.cdm.model.reference.Reference;

import eu.etaxonomy.cdm.model.taxon.Taxon;

import eu.etaxonomy.cdm.strategy.parser.TimePeriodParser;

/**

 * @author a.mueller

 * @since 21.08.2018

 *

 */

public class GreeceWillingImport

        extends SimpleExcelTaxonImport<GreeceWillingImportConfigurator>{

    private static final long serialVersionUID = 8258914747643501550L;

    private static final Logger logger = Logger.getLogger(GreeceWillingImport.class);

    private ImportDeduplicationHelper<SimpleExcelTaxonImportState> dedupHelper;

    /**

     * {@inheritDoc}

     */

    @Override

    protected void firstPass(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state) {

        try {

            Map<String, String> record = state.getOriginalRecord();

            String scientificName = record.get("ScientificName");

            String stableIdentifier = record.get("ObjectURI");

            String title = record.get("Title");

            String titleDescription = record.get("TitleDescription");

            String collector = record.get("Collector");

            String collectorNumber = record.get("CollectorNumber");

            String collectionDate = record.get("CollectionDate");

//            String CollDateISO = record.get("CollDateISO");

            String catalogNumber = record.get("CatalogNumber");

            String locality = record.get("Locality");

            String image = record.get("Image");

            String latitude = record.get("Latitude");

            String longitude = record.get("Longitude");

            String rdfId = record.get("rdfID");

            TimePeriod date = TimePeriodParser.parseEnglishDate(collectionDate, null);

            validate(state, "BaseOfRecords", "Specimen");

            validate(state, "InstitutionCode", "BGBM");

            validate(state, "CollectionCode", "B");

            validate(state, "HigherGeography", "Greece");

            validate(state, "Country", "Greece");

            validate(state, "CountryCode", "GR");

//        HerbariumID

//        CollDateISO

            Taxon taxon = getTaxonByName(state, scientificName);

            DerivedUnitFacade facade = DerivedUnitFacade.NewPreservedSpecimenInstance();

            facade.setPreferredStableUri(URI.create(stableIdentifier));

            facade.setFieldNumber(collectorNumber);

            facade.setBarcode(catalogNumber);

            facade.setCountry(Country.GREECEHELLENICREPUBLIC());

            facade.setLocality(locality);

            try {

                facade.setExactLocationByParsing(longitude, latitude, null, null);

            } catch (ParseException e) {

                e.printStackTrace();

            }

            facade.setCollector(getCollector(state, collector));

            facade.getGatheringEvent(true).setTimeperiod(date);

            facade.setCollection(getCollection(state));

            Media media = getMedia(state,title, titleDescription, image);

            facade.addFieldObjectMedia(media);

            Reference sourceReference = state.getSourceReference();

            String sourceId = rdfId;

            String namespace = "rdfID";

            this.addOriginalSource(facade.innerFieldUnit(), sourceId, namespace, sourceReference);

            this.addOriginalSource(facade.innerDerivedUnit(), sourceId, namespace, sourceReference);

            this.addOriginalSource(media, sourceId, namespace, sourceReference);

            TaxonDescription description = getTaxonDescription(taxon, sourceReference, false, CREATE);

            IndividualsAssociation specimen = IndividualsAssociation.NewInstance(facade.innerDerivedUnit());

            description.addElement(specimen);

            //        getDedupHelper(state).replaceAuthorNamesAndNomRef(state, name);

        } catch (MalformedURLException e) {

            logger.warn("An error occurred during import");

        }

    }

    private Collection bgbm;

    /**

     * @param state

     * @return

     */

    private Collection getCollection(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state) {

        if (bgbm == null){

            List<Collection> results = getCollectionService().searchByCode("B");

            if (results.size()> 1){

                throw new RuntimeException("More then 1 collection found for 'B'");

            }else if (results.isEmpty()){

                throw new RuntimeException("No collection found for 'B'");

            }

            bgbm = results.get(0);

        }

        return bgbm;

    }

    /**

     * @param state

     * @param string

     * @param string2

     */

    private void validate(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state, String attr,

            String expectedValue) {

        Map<String, String> record = state.getOriginalRecord();

        String attrValue = record.get(attr);

        if (!expectedValue.equalsIgnoreCase(attrValue)){

            throw new RuntimeException("Attribute " + attr + " has not expected value " + expectedValue + " but "+ attrValue);

        }

    }

    /**

     * @param state

     * @param title

     * @param titleDescription

     * @param image

     * @return

     * @throws MalformedURLException

     */

    private Media getMedia(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state, String title,

            String titleDescription, String image) throws MalformedURLException {

        //TODO

        String thumbnail = image;

        Media media = getImageMedia(image, thumbnail, true);

        if (isNotBlank(title)){

            media.putTitle(Language.ENGLISH(), title);

        }

        if (isNotBlank(titleDescription)){

            media.putDescription(Language.ENGLISH(), titleDescription);

        }

        //TODO thumbnails etc.

//        ImageInfo info = ImageInfo.NewInstanceWithMetaData(URI.create(image), 60);

//        Media media = Media.NewInstance(uri, info.getLength(), info.getMimeType(), info.getSuffix());

        return media;

    }

    Team willingCollector;

    /**

     * @param state

     * @param collector

     * @return

     */

    private Team getCollector(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state,

            String collector) {

        if (!"Willing,R. & Willing,E.".equals(collector)){

            throw new RuntimeException("Unexpected collector: " + collector);

        }

        if (willingCollector == null){

            UUID willingTeamUuid = UUID.fromString("ab3594a5-304f-4f19-bc8b-4a38c8abfad7");

            willingCollector = (Team)getAgentService().find(willingTeamUuid);

        }

        return willingCollector;

    }

    /**

     * @param state

     * @param scientificName

     * @return

     */

    private Taxon getTaxonByName(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state,

            String scientificName) {

        MatchingTaxonConfigurator config = MatchingTaxonConfigurator.NewInstance();

        config.setTaxonNameTitle(scientificName);

        config.setIncludeSynonyms(false);

        Taxon result = getTaxonService().findBestMatchingTaxon(config);

        return result;

    }

    /**

     * @param state

     * @return

     */

    private ImportDeduplicationHelper<SimpleExcelTaxonImportState> getDedupHelper(SimpleExcelTaxonImportState<GreeceWillingImportConfigurator> state) {

        if (this.dedupHelper == null){

            dedupHelper = ImportDeduplicationHelper.NewInstance(this, state);

        }

        return this.dedupHelper;

    }

}

/**

* Copyright (C) 2018 EDIT

* European Distributed Institute of Taxonomy

* http://www.e-taxonomy.eu

*

* The contents of this file are subject to the Mozilla Public License Version 1.1

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.io.greece;

import java.net.URI;

import eu.etaxonomy.cdm.database.ICdmDataSource;

import eu.etaxonomy.cdm.io.common.ImportStateBase;

import eu.etaxonomy.cdm.io.excel.common.ExcelImportConfiguratorBase;

import eu.etaxonomy.cdm.io.mexico.SimpleExcelTaxonImportState;

/**

 * @author a.mueller

 * @since 21.08.2018

 *

 */

public class GreeceWillingImportConfigurator

        extends ExcelImportConfiguratorBase{

    private static final long serialVersionUID = 5599561699561750055L;

    public static GreeceWillingImportConfigurator NewInstance(URI source, ICdmDataSource destination) {

        return new GreeceWillingImportConfigurator(source, destination);

    }

    /**

     * @param uri

     * @param destination

     */

    protected GreeceWillingImportConfigurator(URI uri, ICdmDataSource destination) {

        super(uri, destination);

    }

    @SuppressWarnings({ "unchecked", "rawtypes" })

    @Override

    public ImportStateBase getNewState() {

        return new SimpleExcelTaxonImportState<>(this);

    }

    /**

     * {@inheritDoc}

     */

    @SuppressWarnings("unchecked")

    @Override

    protected void makeIoClassList() {

        ioClassList = new Class[]{

                GreeceWillingImport.class,

        };

    }

}