GenesysServiceImpl.java

/*
 * Copyright 2017 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.genesys.server.service.impl;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.persistence.EntityManager;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.genesys.server.exception.NonUniqueAccessionException;
import org.genesys.server.exception.SearchException;
import org.genesys.server.model.genesys.Accession;
import org.genesys.server.model.genesys.AccessionAlias;
import org.genesys.server.model.genesys.AccessionCollect;
import org.genesys.server.model.genesys.AccessionData;
import org.genesys.server.model.genesys.AccessionHistoric;
import org.genesys.server.model.genesys.AccessionId;
import org.genesys.server.model.genesys.PDCI;
import org.genesys.server.model.genesys.PDCIStatistics;
import org.genesys.server.model.genesys.SelfCopy;
import org.genesys.server.model.genesys.SvalbardDeposit;
import org.genesys.server.model.genesys.Taxonomy2;
import org.genesys.server.model.impl.AccessionIdentifier3;
import org.genesys.server.model.impl.Country;
import org.genesys.server.model.impl.Crop;
import org.genesys.server.model.impl.FaoInstitute;
import org.genesys.server.model.impl.PGRFANetwork;
import org.genesys.server.persistence.AccessionHistoricRepository;
import org.genesys.server.persistence.AccessionIdRepository;
import org.genesys.server.persistence.AccessionRepository;
import org.genesys.server.persistence.FaoInstituteRepository;
import org.genesys.server.persistence.GenesysLowlevelRepository;
import org.genesys.server.persistence.PDCIRepository;
import org.genesys.server.persistence.SvalbardRepository;
import org.genesys.server.service.CropService;
import org.genesys.server.service.ElasticsearchService;
import org.genesys.server.service.GenesysService;
import org.genesys.server.service.PGRFANetworkService;
import org.genesys.server.service.TaxonomyService;
import org.genesys.server.service.filter.AccessionFilter;
import org.genesys.server.service.worker.AccessionProcessor;
import org.genesys.util.MCPDUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.base.Charsets;
import com.google.common.collect.Sets;
import com.opencsv.CSVWriter;

@Service
@Transactional(readOnly = true)
public class GenesysServiceImpl implements GenesysService {

	public static final Logger LOG = LoggerFactory.getLogger(GenesysServiceImpl.class);

	@Value("${frontend.url}")
	private String frontendUrl;

	@Autowired
	private EntityManager entityManager;

	// Services
	@Autowired
	private TaxonomyService taxonomyService;
	@Autowired
	private AccessionProcessor accessionProcessor;

	// Repositories
	@Autowired
	private AccessionRepository accessionRepository;
	@Autowired
	private AccessionHistoricRepository accessionHistoricRepository;
	@Autowired
	private AccessionIdRepository accessionIdRepository;
	@Autowired
	private PGRFANetworkService networkService;
	@Autowired
	private FaoInstituteRepository instituteRepository;
	@Autowired
	@Qualifier("genesysLowlevelRepositoryCustomImpl")
	private GenesysLowlevelRepository genesysLowlevelRepository;
	@Autowired
	private SvalbardRepository svalbardRepository;

	@Autowired
	private CropService cropService;

	@Autowired
	private PDCIRepository repoPdci;

	@Autowired(required = false)
	private ElasticsearchService elasticService;
	
	public GenesysServiceImpl() {
		
	}

	@Override
	public long countAll() {
		return accessionRepository.count();
	}

	@Override
	public long countAllActive() {
		return accessionRepository.countByHistoric(false);
	}

	@Override
	public long countAllHistoric() {
		return accessionRepository.countByHistoric(true);
	}

	@Override
	public long countByCrop(final Crop crop) {
		return accessionRepository.countByCrop(crop);
	}

	@Override
	public long countByInstitute(FaoInstitute institute) {
		return accessionRepository.countByInstitute(institute);
	}

    @Override
    public long countByInstituteWithDoi(final FaoInstitute institute) {
        return accessionRepository.countByInstituteWithDoi(institute);
    }

	@Override
	public long countByOrigin(Country country) {
		return accessionRepository.countByOrigin(country);
	}

	@Override
	public long countByLocation(Country country) {
		return accessionRepository.countByLocation(country);
	}

	@Override
	public List<FaoInstitute> findHoldingInstitutes(Set<Long> accessionIds) {
		return accessionRepository.findDistinctInstitutesFor(accessionIds);
	}

	@Override
	public Set<Long> listAccessions(FaoInstitute institute, Set<Long> accessionIds) {
		return accessionRepository.findByInstituteAndIds(institute, accessionIds);
	}

	@Override
	public Page<Accession> listAccessions(Collection<Long> accessionIds, Pageable pageable) {
		if (accessionIds == null || accessionIds.size() == 0) {
			return null;
		}
		return accessionRepository.findById(accessionIds, pageable);
	}

	@Override
	public Accession getAccession(AccessionIdentifier3 aid3) throws NonUniqueAccessionException {
		try {
			Accession accession = accessionRepository
					.findOne(instituteRepository.findByCode(aid3.getHoldingInstitute()), aid3.getDoi(), aid3.getAccessionNumber(), aid3.getGenus());
			return lazyLoadAccession(accession);
		} catch (IncorrectResultSizeDataAccessException e) {
			LOG.error("Duplicate accession name instCode={} acceNumb={} genus={}", aid3.getHoldingInstitute(), aid3.getAccessionNumber(), aid3.getGenus());
			throw new NonUniqueAccessionException(aid3.getHoldingInstitute(), aid3.getAccessionNumber(), aid3.getGenus());
		}
	}

	@Override
	public Accession getAccession(String instCode, String acceNumb) throws NonUniqueAccessionException {
		try {
			Accession accession = accessionRepository.findByInstituteCodeAndAccessionNumber(instCode, acceNumb);
			return lazyLoadAccession(accession);
		} catch (IncorrectResultSizeDataAccessException e) {
			LOG.error("Duplicate accession name instCode={} acceNumb={}", instCode, acceNumb);
			throw new NonUniqueAccessionException(instCode, acceNumb);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Accession> listAccessions(final String acceNumb) {
		List<Accession> accessions = new ArrayList<>();
		for (final Accession accession : accessionRepository.findByAccessionNumber(acceNumb)) {
			accessions.add(lazyLoadAccession(accession));
		}
		return accessions;
	}

	@Override
	public Accession getAccession(String instCode, String doi, String acceNumb, String genus) throws NonUniqueAccessionException {
		try {
			Accession accession = accessionRepository.findOne(instituteRepository.findByCode(instCode), doi, acceNumb, genus);
			return lazyLoadAccession(accession);
		} catch (IncorrectResultSizeDataAccessException e) {
			LOG.error("Duplicate accession name instCode={} acceNumb={} genus={}", instCode, acceNumb, genus);
			throw new NonUniqueAccessionException(instCode, acceNumb, genus);
		}
	}

	@Override
	public Accession getAccession(long accessionId) {
		Accession accession = accessionRepository.findById(accessionId).orElse(null);
		return lazyLoadAccession(accession);
	}

	/**
	 * {@inheritDoc}
	 */
	@Transactional
	@Override
	public void removeDOIs() {
		accessionRepository.removeDOIs();
	}

	@Override
	public Accession getAccessionByDOI(final String doi) {
		Accession accession = accessionRepository.findByDoi(doi);
		return lazyLoadAccession(accession);
	}

	private <T extends AccessionData> T lazyLoadAccession(T accession) {
		if (accession != null) {
			AccessionId accessionId = accession.getAccessionId();

			if (accessionId.getStorage() != null)
				accessionId.getStorage().size();
			if (accessionId.getAliases() != null)
				accessionId.getAliases().size();
			if (accessionId.getBreederCode() != null)
				accessionId.getBreederCode().size();
			if (accessionId.getBreederName() != null)
				accessionId.getBreederName().size();
			if (accessionId.getDuplSite() != null)
				accessionId.getDuplSite().size();
			if (accessionId.getRemarks() != null)
				accessionId.getRemarks().size();

			if (accession.getCountryOfOrigin() != null)
				accession.getCountryOfOrigin().getId();
			if (accession.getCrop() != null)
				accession.getCrop().getId();

			if (accessionId.getColl() != null) {
				if (accessionId.getColl().getCollCode() != null)
					accessionId.getColl().getCollCode().size();
				if (accessionId.getColl().getCollInstAddress() != null)
					accessionId.getColl().getCollInstAddress().size();
				if (accessionId.getColl().getCollName() != null)
					accessionId.getColl().getCollName().size();
			}

			if (accessionId.getPdci() != null)
				accessionId.getPdci().getId();
		}
		return accession;
	}

	@Override
	public List<Accession> listAccessions(FaoInstitute faoInstitute, String accessionName) {
		return accessionRepository.findByInstituteAndAccessionNumber(faoInstitute, accessionName);
	}

	@Override
	public List<SvalbardDeposit> getSvalbardData(AccessionId accession) {
		if (accession == null) {
			return null;
		}
		return svalbardRepository.findByAccession(accession);
	}


	public AllStuff loadAllStuff(AccessionData accession) {
		if (accession == null)
			return null;
		lazyLoadAccession(accession);
		AllStuff all = new AllStuff(accession);
		all.collect = accession.getAccessionId().getColl();
		all.names = accession.getAccessionId().getAliases();
		all.remarks = accession.getAccessionId().getRemarks();
		all.sgsvDeposits = getSvalbardData(accession.getAccessionId());
		return all;
	}

	@Override
	public List<AllStuff> loadAllStuff(Collection<Long> accessionIds) {
		if (accessionIds == null || accessionIds.size() == 0) {
			return Collections.emptyList();
		}

		final Map<Long, AllStuff> map = Collections.synchronizedMap(new HashMap<Long, AllStuff>());

		List<AllStuff> alls = accessionIds.stream().map(accessionId -> new AllStuff(accessionId)).peek(all -> map.put(all.id, all)).collect(Collectors.toList());

		accessionRepository.findAllById(accessionIds).stream().forEach(a -> {
			AllStuff all = map.get(a.getAccessionId().getId());
			lazyLoadAccession(a);
			all.accession = a;
			all.collect = a.getAccessionId().getColl();
			all.names = a.getAccessionId().getAliases();
			all.remarks = a.getAccessionId().getRemarks();
			all.sgsvDeposits = new ArrayList<>();
		});

		accessionHistoricRepository.findAllById(accessionIds).stream().forEach(a -> {
			AllStuff all = map.get(a.getAccessionId().getId());
			lazyLoadAccession(a);
			all.accession = a;
			all.collect = a.getAccessionId().getColl();
			all.names = a.getAccessionId().getAliases();
			all.remarks = a.getAccessionId().getRemarks();
			all.sgsvDeposits = new ArrayList<>();
		});

		svalbardRepository.findAllFor(accessionIds).stream().forEach(d -> {
			AllStuff all = map.get(d.getAccession().getId());
			all.sgsvDeposits.add(d);
		});

		alls.stream().forEach(all -> {
			detach(all.accession);
			detach(all.collect);
			detach(all.names);
			detach(all.remarks);
		});

		return alls;
	}

	private void detach(Object obj) {
		if (obj != null) {
			if (obj instanceof Collection) {
				for (Object o : (Collection<?>) obj)
					detach(o);
			} else {
				entityManager.detach(obj);
			}
		}
	}

	@Override
	public Page<Object[]> statisticsCropNameByInstitute(FaoInstitute institute, Pageable pageable) {
		return accessionRepository.statisticsCropNameInInstitute(institute, pageable);
	}

	@Override
	public Page<Object[]> statisticsGenusByInstitute(FaoInstitute institute, Pageable pageable) {
		return accessionRepository.statisticsGenusInInstitute(institute, pageable);
	}

	@Override
	public Page<Object[]> statisticsSpeciesByInstitute(FaoInstitute institute, Pageable pageable) {
		final Page<Object[]> page = accessionRepository.statisticsSpeciesInInstitute(institute, pageable);
		for (final Object[] r : page.getContent()) {
			if (r[0] != null) {
				r[0] = taxonomyService.get((Long) r[0]);
			}
		}
		return page;
	}

	@Override
	public List<Object[]> statisticsSpeciesByInstituteES(FaoInstitute institute, int size) {
		AccessionFilter af = new AccessionFilter();
		af.holder().code(Sets.newHashSet(institute.getCode()));
		af.historic(false);

		ElasticsearchService.TermResult result = null;
		try {
			result = elasticService.termStatisticsAuto(Accession.class, af, size, "taxonomy.taxSpecies");
		} catch (SearchException e) {
			LOG.warn(e.getMessage());
		}

		List<Object[]> statistics = new ArrayList<>(size);
		if (result != null) {
			for(ElasticsearchService.Term term: result.getTerms()) {
				Object[] statItem = new Object[2];
				statItem[0] = taxonomyService.get(Long.valueOf(term.getTerm()));
				statItem[1] = term.getCount();
				statistics.add(statItem);
			}
		}
		return statistics;
	}

	@Override
	public List<Long> listAccessionsIds(Pageable pageable) {
		return accessionRepository.listAccessionsIds(pageable);
	}

	@Override
	public Page<Accession> listAccessionsByOrganization(PGRFANetwork network, Pageable pageable) {
		final List<FaoInstitute> members = networkService.getInstitutes(network);
		if (members == null || members.size() == 0) {
			return new PageImpl<Accession>(Collections.emptyList());
		}
		return accessionRepository.findByInstitute(members, pageable);
	}

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(readOnly = false)
	// @CacheEvict(value = "statistics", allEntries = true)
	public void updateAccessionCountryRefs() {
		genesysLowlevelRepository.updateCountryRefs();
	}

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(readOnly = false)
	// @CacheEvict(value = "statistics", allEntries = true)
	public void updateAccessionInstitueRefs() {
		genesysLowlevelRepository.updateFaoInstituteRefs();
	}

	@Override
	@Transactional
	// @CacheEvict(value = "statistics", allEntries = true)
	public void updateAccessionCount(FaoInstitute institute) {
		if (institute != null) {
			long accessionCount = accessionRepository.countByInstitute(institute);
			instituteRepository.updateAccessionCount(institute, accessionCount);
		}
	}

	/**
	 * @deprecated Should be removed
	 */
	@Deprecated
	@Override
	@Transactional(readOnly = false)
	// @CacheEvict(value = "statistics", allEntries = true)
	public void addAccessions(List<Accession> accessions) {
		accessionRepository.saveAll(accessions);
	}

	@Override
	@Transactional(readOnly = false)
	// @CacheEvict(value = "statistics", allEntries = true)
	public List<Accession> saveAccessions(FaoInstitute institute, List<Accession> accessions) {
		if (LOG.isDebugEnabled()) {
			LOG.debug("Saving {} accessions", accessions.size());
		}
		accessionRepository.saveAll(accessions);
		// updateAccessionCount(institute);
		return accessions;
	}


	@Override
	@Transactional(readOnly = false)
	// @CacheEvict(value = "statistics", allEntries = true)
	public List<Accession> saveAccessions(Iterable<Accession> accessions) {
		Set<FaoInstitute> institutes = new HashSet<FaoInstitute>();
		for (Accession accession : accessions) {
			// System.out.println("Saving " + accession + " STO=" + accession.getStoRage() +
			// " ST=" +
			// accession.getStorage());
			institutes.add(accession.getInstitute());
		}
		List<Accession> res = accessionRepository.saveAll(accessions);
		for (FaoInstitute institute : institutes)
			updateAccessionCount(institute);
		return res;
	}

	@Override
	@Transactional(readOnly = false)
	@PreAuthorize("hasRole('ADMINISTRATOR') or hasPermission(#institute, 'DELETE') or hasPermission(#institute, 'ADMINISTRATION')")
	// @CacheEvict(value = "statistics", allEntries = true)
	public List<AccessionHistoric> removeAccessions(FaoInstitute institute, List<Accession> toDelete) {
		List<AccessionHistoric> deleted = new ArrayList<AccessionHistoric>();

		if (toDelete.size() > 0) {
			final Set<Long> accessionIds = new HashSet<Long>();

			for (final Accession accn : toDelete) {
				if (institute.getId().equals(accn.getInstitute().getId())) {
					accessionIds.add(accn.getAccessionId().getId());
				} else {
					throw new RuntimeException("Accession " + accn.getAccessionNumber() + " does not belong to " + institute.getCode());
				}
				if (accn.getDoi() != null) {
					throw new RuntimeException("Refusing to delete accession that has DOI assigned - accession " + accn.getAccessionNumber() + " has DOI " + accn.getDoi());
				}
			}

			accessionRepository.deleteAll(toDelete);

			for (Accession accession : toDelete) {
				AccessionHistoric hist = new AccessionHistoric();
				SelfCopy.copy(accession, hist);
				hist.setAccessionId(accessionIdRepository.findById(accession.getId()).orElse(null));
				// hist.setId(hist.getAccessionId().getId());
				deleted.add(hist);
			}

			LOG.info("Done deleting, now adding");
			accessionHistoricRepository.saveAll(deleted);
			// for (Accession a : toDelete) {
			// accessionRepository.deleteActive(a.getAccessionId().getId());
			// }
		}

		return deleted;
	}

	/**
	 * Update {@link SvalbardDeposit} data and link with Genesys accessions. The
	 * primary key for {@link SvalbardDeposit} is the <code>sgsv_id</code> as
	 * provided by NordGen.
	 *
	 * Any existing SGSV records are first deleted. Inserted and linked with
	 * accessions in this method.
	 *
	 * @param svalbardDeposits
	 * @return
	 */
	@Override
	// Worker threads don't carry this information
	// @PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_UNCOMMITTED)
	// @CacheEvict(value = "statistics", allEntries = true)
	public List<SvalbardDeposit> saveSvalbards(List<SvalbardDeposit> svalbardDeposits) {
		if (CollectionUtils.isEmpty(svalbardDeposits)) {
			return svalbardDeposits;
		}

		List<Long> sgsvIds = svalbardDeposits.stream().map(deposit -> deposit.getId()).collect(Collectors.toList());
		// // Delete existing
		// LOG.info("Deleting SvalbardDeposits with IDs=" + toDelete);
		// svalbardRepository.deleteById(toDelete);

		svalbardRepository.saveAll(svalbardDeposits);

		// This seems to be needed to make nativeQueries work later on.
		entityManager.flush();

		// link by aliases where INSTCODE matches alias use, GENUS and ALIAS match
		int updatedCount = svalbardRepository.linkByAlternativeName(sgsvIds);
		if (updatedCount > 0 && LOG.isDebugEnabled()) {
			LOG.debug("Linked {} accessions by alias in set {}", updatedCount, sgsvIds);
		}

		// link indirectly where INSTCODE matches alternative sgsvCode, GENUS and
		// ACCENUMB match
		updatedCount = svalbardRepository.linkInDirectly(sgsvIds);
		if (updatedCount > 0 && LOG.isDebugEnabled()) {
			LOG.debug("Linked {} accessions using alternate code in set {}", updatedCount, sgsvIds);
		}

		// link directly where INSTCODE, GENUS and ACCENUMB match
		updatedCount = svalbardRepository.linkDirectly(sgsvIds);
		if (updatedCount > 0 && LOG.isDebugEnabled()) {
			LOG.debug("Linked {} accessions in set {}", updatedCount, sgsvIds);
		}

		accessionRepository.setInSvalbardByDeposits(sgsvIds);

		// Do it again.
		entityManager.flush();
		entityManager.clear();

		// reload after updates
		svalbardDeposits = svalbardRepository.findAllById(sgsvIds);

		return svalbardDeposits;
	}

	@Override
	public long countAvailableForDistribution(Set<Long> accessionIds) {
		if (accessionIds == null || accessionIds.size() == 0) {
			return 0;
		}
		return accessionRepository.countAvailableForDistribution(accessionIds);
	}

	@Override
	public Set<Long> filterAvailableForDistribution(Set<Long> accessionIds) {
		if (accessionIds == null || accessionIds.size() == 0) {
			return Collections.emptySet();
		}
		return accessionRepository.filterAvailableForDistribution(accessionIds);
	}

	@Override
	// TODO FIXME Need proper term URLs
	public void writeAccessions(final AccessionFilter filter, final OutputStream outputStream, String shortFilter, String dataSource) throws Exception {

		// UTF8 is used for encoding entry names
		final ZipOutputStream zos = new ZipOutputStream(outputStream);
		final StringBuilder commentBuilder = new StringBuilder("Genesys Accessions");
		if (StringUtils.isNotBlank(shortFilter)) {
			commentBuilder.append(" ").append("filter=").append(shortFilter);
		}
		zos.setComment(commentBuilder.toString());
		zos.flush();

		// Filter information
		final ZipEntry readmeEntry = new ZipEntry("README.txt");
		readmeEntry.setComment("Extra information");
		readmeEntry.setTime(System.currentTimeMillis());
		zos.putNextEntry(readmeEntry);
		writeREADME(zos, shortFilter, dataSource);
		zos.closeEntry();
		zos.flush();

		// Accessions
		final ZipEntry coreEntry = new ZipEntry("core.csv");
		coreEntry.setComment("Accession information");
		coreEntry.setTime(System.currentTimeMillis());
		zos.putNextEntry(coreEntry);
		writeAccessionsCore(filter, zos);
		zos.closeEntry();
		zos.flush();

		// AccessionGeo
		ZipEntry entry = new ZipEntry("names.csv");
		entry.setComment("Accession Names");
		entry.setTime(System.currentTimeMillis());
		zos.putNextEntry(entry);
		writeAccessionsNames(filter, zos);
		zos.closeEntry();
		zos.flush();

		// AccessionGeo
		entry = new ZipEntry("geo.csv");
		entry.setComment("GIS information");
		entry.setTime(System.currentTimeMillis());
		zos.putNextEntry(entry);
		writeAccessionsGeo(filter, zos);
		zos.closeEntry();
		zos.flush();

		// AccessionCollect
		entry = new ZipEntry("coll.csv");
		entry.setComment("Collecting information");
		entry.setTime(System.currentTimeMillis());
		zos.putNextEntry(entry);
		writeAccessionsColl(filter, zos);
		zos.closeEntry();
		zos.flush();

		final ZipEntry metaEntry = new ZipEntry("meta.xml");
		metaEntry.setComment("Darwin Core Archive metadata");
		metaEntry.setTime(System.currentTimeMillis());
		zos.putNextEntry(metaEntry);
		try (final BufferedWriter osw = new BufferedWriter(new OutputStreamWriter(zos, Charsets.UTF_8))) {
			osw.write("<?xml version='1.0' encoding='utf-8'?>\n");
			osw.write(
					"<archive xmlns=\"http://rs.tdwg.org/dwc/text/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://rs.tdwg.org/dwc/text/ http://rs.tdwg.org/dwc/text/tdwg_dwc_text.xsd\">\n");
			osw.write("<core encoding=\"UTF-8\" fieldsTerminatedBy=\",\" linesTerminatedBy=\"\\n\" fieldsEnclosedBy=\"&quot;\" ignoreHeaderLines=\"0\">\n");
			osw.write("\t<files><location>core.csv</location></files>\n");
			osw.write("\t<id index=\"0\" />\n");
			osw.write("\t<field index=\"1\" term=\"UUID\"/>\n");
			osw.write("\t<field index=\"2\" term=\"http://purl.org/germplasm/germplasmType#wiewsInstituteID\"/>\n");
			osw.write("\t<field index=\"3\" term=\"http://purl.org/germplasm/germplasmTerm#germplasmIdentifier\"/>\n");
			osw.write("\t<field index=\"4\" term=\"http://rs.tdwg.org/dwc/terms/genus\"/>\n");
			osw.write("\t<field index=\"5\" term=\"http://rs.tdwg.org/dwc/terms/species\"/>\n");
			osw.write("\t<field index=\"6\" term=\"orgCty\"/>\n");
			osw.write("\t<field index=\"7\" term=\"acqSrc\"/>\n");
			osw.write("\t<field index=\"8\" term=\"acqDate\"/>\n");
			osw.write("\t<field index=\"9\" term=\"mlsStat\"/>\n");
			osw.write("\t<field index=\"10\" term=\"available\"/>\n");
			osw.write("\t<field index=\"11\" term=\"storage\"/>\n");
			osw.write("\t<field index=\"12\" term=\"sampStat\"/>\n");
			osw.write("\t<field index=\"13\" term=\"dublInst\"/>\n");
			osw.write("\t<field index=\"14\" term=\"createdBy\"/>\n");
			osw.write("\t<field index=\"15\" term=\"createdDate\"/>\n");
			osw.write("\t<field index=\"16\" term=\"lastModifiedBy\"/>\n");
			osw.write("\t<field index=\"17\" term=\"lastModifiedDate\"/>\n");
			osw.write("\t<field index=\"18\" term=\"doi\"/>\n");
			osw.write("</core>\n");

			osw.write("<extension encoding=\"UTF-8\" fieldsTerminatedBy=\",\" linesTerminatedBy=\"\\n\" fieldsEnclosedBy=\"&quot;\" ignoreHeaderLines=\"0\">\n");
			osw.write("\t<files><location>");
			osw.write("names.csv");
			osw.write("</location></files>\n");
			osw.write("\t<coreid index=\"0\" />\n");
			osw.write("\t<field index=\"1\" term=\"instCode\"/>\n");
			osw.write("\t<field index=\"2\" term=\"name\"/>\n");
			osw.write("\t<field index=\"3\" term=\"aliasType\"/>\n");
			osw.write("\t<field index=\"4\" term=\"lang\"/>\n");
			osw.write("\t<field index=\"5\" term=\"version\"/>\n");
			osw.write("</extension>\n");

			osw.write("<extension encoding=\"UTF-8\" fieldsTerminatedBy=\",\" linesTerminatedBy=\"\\n\" fieldsEnclosedBy=\"&quot;\" ignoreHeaderLines=\"0\">\n");
			osw.write("\t<files><location>");
			osw.write("geo.csv");
			osw.write("</location></files>\n");
			osw.write("\t<coreid index=\"0\" />\n");
			osw.write("\t<field index=\"1\" term=\"latitude\"/>\n");
			osw.write("\t<field index=\"2\" term=\"longitude\"/>\n");
			osw.write("\t<field index=\"3\" term=\"elevation\"/>\n");
			osw.write("\t<field index=\"4\" term=\"datum\"/>\n");
			osw.write("\t<field index=\"5\" term=\"uncertainty\"/>\n");
			osw.write("\t<field index=\"6\" term=\"method\"/>\n");
			osw.write("\t<field index=\"7\" term=\"version\"/>\n");
			osw.write("</extension>\n");

			osw.write("<extension encoding=\"UTF-8\" fieldsTerminatedBy=\",\" linesTerminatedBy=\"\\n\" fieldsEnclosedBy=\"&quot;\" ignoreHeaderLines=\"0\">\n");
			osw.write("\t<files><location>");
			osw.write("coll.csv");
			osw.write("</location></files>\n");
			osw.write("\t<coreid index=\"0\" />\n");
			osw.write("\t<field index=\"1\" term=\"collMissId\"/>\n");
			osw.write("\t<field index=\"2\" term=\"collNumb\"/>\n");
			osw.write("\t<field index=\"3\" term=\"collDate\"/>\n");
			osw.write("\t<field index=\"4\" term=\"collSrc\"/>\n");
			osw.write("\t<field index=\"5\" term=\"collSite\"/>\n");
			osw.write("\t<field index=\"6\" term=\"collCode\"/>\n");
			osw.write("\t<field index=\"7\" term=\"collName\"/>\n");
			osw.write("\t<field index=\"8\" term=\"collInstAddress\"/>\n");
			osw.write("\t<field index=\"9\" term=\"version\"/>\n");
			osw.write("</extension>\n");

			osw.write("</archive>\n");
			osw.flush();
			zos.closeEntry();
			zos.finish();
			zos.flush();
			outputStream.flush();
		}
	}

	private void writeREADME(ZipOutputStream zos, String shortFilter, String dataSource) throws IOException {
		final BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(zos));
		bw.write("Date:\t");
		bw.write(Instant.now().toString());
		bw.write("\n");

		if (StringUtils.isNotBlank(shortFilter)) {
			bw.write("Filter:\t");
			bw.write(shortFilter);
			bw.write("\n");
		}

		if (StringUtils.isNotBlank(dataSource)) {
			bw.write("URL:\t");
			bw.write(frontendUrl);
			bw.write(dataSource);
			bw.write("\n");
		}

		bw.write("Attribution:\t");
		bw.write("https://www.genesys-pgr.org/content/terms");
		bw.write("\n");
		bw.flush();
	}

	private void writeAccessionsCore(final AccessionFilter filter, ZipOutputStream zos) throws Exception {
		final CSVWriter csv = new CSVWriter(new BufferedWriter(new OutputStreamWriter(zos)), ',', '"', '\\', "\n");
		csv.writeNext(new String[] { "genesysId", "uuid", "instCode", "acceNumb", "doi", "genus", "species", "spAuthor", "subtaxa", "subtAuthor", "fullTaxa", "origCty", "acqSrc", "acqDate", "mlsStat", "available", "historic",
			"curationType", "storage", "sampStat", "duplSite", "createdBy", "createdDate", "lastModifiedBy", "lastModifiedDate" });

		final Writer writer = new Writer();

		// Write accession information
		accessionProcessor.process(filter, (accessions) -> {
			for (Accession accession: accessions) {
				writer.processCore(csv, accession);
			}
		});
		csv.flush();
	}

	private void writeAccessionsGeo(final AccessionFilter filter, ZipOutputStream zos) throws IOException {
		final CSVWriter csv = new CSVWriter(new BufferedWriter(new OutputStreamWriter(zos)), ',', '"', '\\', "\n");
		csv.writeNext(new String[] { "genesysId", "latitude", "longitude", "elevation", "datum", "uncertainty", "method" });

		final Writer writer = new Writer();

		// Write accession geo information
		try {
			accessionProcessor.process(filter, (accessions) -> {
				for (Accession accession: accessions) {
					writer.processGeo(csv, accession.getAccessionId());
				}
			});
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
		} finally {
			csv.flush();
		}
	}

	private void writeAccessionsNames(final AccessionFilter filter, ZipOutputStream zos) throws IOException {
		final CSVWriter csv = new CSVWriter(new BufferedWriter(new OutputStreamWriter(zos)), ',', '"', '\\', "\n");
		csv.writeNext(new String[] { "genesysId", "instCode", "name", "aliasType", "lang", "version" });

		final Writer writer = new Writer();

		// Write accession aliases information
		try {
			accessionProcessor.process(filter, (accessions) -> {
				for (Accession accession: accessions) {
					List<AccessionAlias> accessionAliases = accession.getAccessionId().getAliases();
					if (accessionAliases != null && accessionAliases.size() > 0) {
						writer.processAliases(csv, accessionAliases);
					}
				}
			});
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
		} finally {
			csv.flush();
		}
	}

	private void writeAccessionsColl(final AccessionFilter filter, final ZipOutputStream zos) throws IOException {
		final CSVWriter csv = new CSVWriter(new BufferedWriter(new OutputStreamWriter(zos)), ',', '"', '\\', "\n");
//		csv.writeNext(new String[] { "genesysId", "collMissId", "collNumb", "collDate", "collSrc", "collSite", "collCode", "collName", "collInstAddress", "version" });
		csv.writeNext(new String[] { "genesysId", "collMissId", "collNumb", "collDate", "collSrc", "collSite", "version" });

		final Writer writer = new Writer();

		// Write accession collections information
		try {
			accessionProcessor.process(filter, (accessions) -> {
				for (Accession accession: accessions) {
					AccessionCollect accessionCollect = accession.getAccessionId().getColl();
					if (accessionCollect != null) {
						writer.processCollect(csv, accessionCollect);
					}
				}
			});
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
		} finally {
			csv.flush();
		}
	}

	private static class Writer {
		int i = 0;

		private void processGeo(final CSVWriter csv, final AccessionId geo) throws IOException {
			final String[] row = new String[8];
			// genesysId
			row[0] = toStringValue(geo.getId());
			// latitude
			row[1] = toStringValue(geo.getLatitude());
			// longitude
			row[2] = toStringValue(geo.getLongitude());
			// elevation
			row[3] = toStringValue(geo.getElevation());
			// datum
			row[4] = geo.getCoordinateDatum();
			// uncertainty
			row[5] = toStringValue(geo.getCoordinateUncertainty());
			// method
			row[6] = geo.getGeoreferenceMethod();
			csv.writeNext(row);
			csv.flush();

			if (i++ % 5000 == 0) {
				LOG.info("Writing geo DWCA {}", i);
			}
		}

		private void processAliases(final CSVWriter csv, final List<AccessionAlias> aliases) throws IOException {
			for (AccessionAlias alias : aliases) {
				final String[] row = new String[6];
				// genesysId
				row[0] = toStringValue(alias.getAccession().getId());
				// instCode
				row[1] = alias.getUsedBy();
				// name
				row[2] = alias.getName();
				// aliasType
				row[3] = alias.getAliasType().name();
				// lang
				row[4] = alias.getLang();
				// version
				row[5] = toStringValue(alias.getVersion());
				csv.writeNext(row);
				csv.flush();

				if (i++ % 5000 == 0) {
					LOG.info("Writing alias DWCA {}", i);
				}
			}
		}

		private void processCollect(final CSVWriter csv, final AccessionCollect collect) throws IOException {
			final String[] row = new String[7];
			// genesysId
			row[0] = toStringValue(collect.getAccession().getId());
			// collMissId
			row[1] = collect.getCollMissId();
			// collNumb
			row[2] = collect.getCollNumb();
			// collDate
			row[3] = collect.getCollDate();
			// collSrc
			row[4] = toStringValue(collect.getCollSrc());
			// collSite
			row[5] = collect.getCollSite();
			// version
			row[6] = toStringValue(collect.getVersion());
			csv.writeNext(row);
			csv.flush();

			if (i++ % 5000 == 0) {
				LOG.info("Writing collecting DWCA {}", i);
			}
		}

		private void processCore(final CSVWriter csv, final Accession accession) throws IOException {
			final String[] row = new String[25];
			// genesysId
			row[0] = toStringValue(accession.getId());
			// uuid
			row[1] = toStringValue(accession.getUuid());
			// instCode
			row[2] = accession.getInstituteCode();
			// acceNumb
			row[3] = accession.getAccessionNumber();
			// doi
			row[4] = accession.getDoi();
			// genus
			row[5] = accession.getGenus();

			if (accession.getTaxonomy() != null) {
				// species
				row[6] = toStringValue(accession.getTaxonomy().getSpecies());
				// spAuthor
				row[7] = toStringValue(accession.getTaxonomy().getSpAuthor());
				// subtaxa
				row[8] = toStringValue(accession.getTaxonomy().getSubtaxa());
				// subtAuthor
				row[9] = toStringValue(accession.getTaxonomy().getSubtAuthor());
				// fullTaxa
				row[10] = toStringValue(accession.getTaxonomy().getTaxonName());
			}

			// origCty
			row[11] = accession.getOrigCty();
			// acqSrc
			row[12] = ""; // toStringValue(accession.getAcquisitionSource()); // Obsolete
			// acqDate
			row[13] = accession.getAcquisitionDate();
			// mlsStat
			row[14] = toStringValue(accession.getMlsStatus());
			// available
			row[15] = toStringValue(accession.getAvailable());
			// historic
			row[16] = toStringValue(accession.isHistoric());
			// curationType
			row[17] = toStringValue(accession.getCurationType());
			// storage
			row[18] = MCPDUtil.toMcpdArray(accession.getAccessionId().getStorage());
			// sampStat
			row[19] = toStringValue(accession.getSampStat());
			// duplSite
			row[20] = MCPDUtil.toMcpdArray(accession.getAccessionId().getDuplSite());
			// createdBy
			row[21] = toStringValue(accession.getCreatedBy());
			// createdDate
			row[22] = toDateValue(accession.getCreatedDate());
			// lastModifiedBy
			row[23] = toStringValue(accession.getLastModifiedBy());
			// lastModifiedDate
			row[24] = toDateValue(accession.getLastModifiedDate());
			csv.writeNext(row);
			csv.flush();

			if (i++ % 5000 == 0) {
				LOG.info("Writing core DWCA {}", i);
			}
		}

		private String toStringValue(final Object value) {
			return value != null ? value.toString() : "";
		}

		private String toDateValue(final Instant date) {
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").withZone(ZoneId.systemDefault());
			return date != null ? formatter.format(date) : "";
		}
	}

	@Override
	public List<Long> listAccessionsIds(Taxonomy2 taxonomy) {
		return accessionRepository.listAccessionsIds(taxonomy);
	}

	@Override
	@Transactional
	public int assignMissingUuid(int count) {
		List<AccessionId> accessionIds = accessionIdRepository.findMissingUuid(PageRequest.of(0, count));
		for (AccessionId accessionId : accessionIds) {
			if (accessionId.getUuid() == null) {
				accessionId.setUuid(UUID.randomUUID());
			}
		}
		accessionIdRepository.saveAll(accessionIds);
		LOG.info("Generated {} new UUIDs", accessionIds.size());
		return accessionIds.size();
	}

	@Override
	public AccessionHistoric getHistoricAccession(UUID uuid) {
		AccessionHistoric ah = accessionHistoricRepository.findOneByUuid(uuid);
		return lazyLoadAccession(ah);
	}

	@Override
	public List<PDCI> loadPDCI(List<Long> accessionIds) {
		return repoPdci.findByAccessionId(accessionIds);
	}

	@Override
	public PDCIStatistics statisticsPDCI(PGRFANetwork network) {
		PDCIStatistics stats = new PDCIStatistics();

		for (FaoInstitute faoInstitute : networkService.getInstitutes(network)) {
			stats.merge(faoInstitute.getStatisticsPDCI());
		}
		return stats;
	}


	@Transactional(readOnly = false)
	@Override
	public FaoInstitute updatePDCI(FaoInstitute faoInstitute) {
		// Reload
		faoInstitute = instituteRepository.findById(faoInstitute.getId()).orElse(null);
				
		Object[] overall = (Object[]) repoPdci.statistics(faoInstitute);

		if (overall == null || overall[3] == null || ((Number) overall[3]).longValue() == 0) {
			LOG.warn("Clearing PDCI statistics for {}", faoInstitute);
			faoInstitute.setPdciMin(null);
			faoInstitute.setPdciMax(null);
			faoInstitute.setPdciAvg(null);
			faoInstitute.setPdciHistogram(null);

		} else {
			faoInstitute.setPdciMin(((Number) overall[0]).doubleValue());
			faoInstitute.setPdciMax(((Number) overall[1]).doubleValue());
			faoInstitute.setPdciAvg(((Number) overall[2]).doubleValue());
			PDCIStatistics pdciStatistics = new PDCIStatistics();
			List<Object[]> histogram = repoPdci.histogram(faoInstitute);
			pdciStatistics.makeHistogram(histogram);

			faoInstitute.setPdciHistogram(Arrays.toString(pdciStatistics.getHistogram()));
		}

		return instituteRepository.save(faoInstitute);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
	// @PreAuthorize("hasRole('ADMINISTRATOR')")
	public List<Accession> updateAccessionCrops(Set<Long> accessionIds) {
		List<Accession> accessions = new ArrayList<>(accessionRepository.findAllById(accessionIds));
		for (Accession accession : accessions) {
			accessionRepository.updateCrop(accession, cropService.getCrop(accession.getCropName()));
		}
		return accessions;
	}

	@Override
	@Cacheable(value = "statistics", unless = "#result == null", key = "'stats.' + #root.methodName")
	public List<Object[]> getLastUpdatedStatistics() {
		List<Object[]> res = accessionRepository.lastUpdatedStatistics();
		for (Object[] row : res) {
			row[3] = instituteRepository.findById((Long) row[3]).orElse(null);
		}
		return res;
	}

	@Override
	@Cacheable(value = "statistics", unless = "#result == null", key = "'stats.' + #root.methodName + '-' + #faoInstitute.code")
	public List<Object[]> getLastUpdatedStatistics(FaoInstitute faoInstitute) {
		return accessionRepository.lastUpdatedStatistics(faoInstitute);
	}

	@Override
	@Cacheable(value = "statistics", unless = "#result == null", key = "'stats.' + #root.methodName + '-' + #faoInstitute.code + '-' + #historic")
	public List<Object[]> getLastUpdatedStatistics(FaoInstitute faoInstitute, boolean historic) {
		return accessionRepository.lastUpdatedStatistics(faoInstitute, historic);
	}

	@Override
	public List<AccessionData> listActiveAndHistoric(List<UUID> accessionUuids) {
		return accessionRepository.findActiveAndHistoric(accessionUuids);
	}

}