DownloadServiceImpl.java

/*
 * Copyright 2018 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 static org.genesys.util.ExcelUtils.updateCell;
import static org.genesys.util.ExcelUtils.updateCellEmail;
import static org.genesys.util.ExcelUtils.updateCellUrl;

import java.io.EOFException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.nio.file.Paths;
import java.nio.file.attribute.FileTime;
import java.time.LocalDate;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.UUID;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ooxml.POIXMLProperties;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.DeferredSXSSFSheet;
import org.apache.poi.xssf.streaming.DeferredSXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
// NOTE Excel 2016 has a limit of 66,530 hyperlinks
// import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.genesys.amphibian.client.api.DatasetsApi;
import org.genesys.amphibian.client.invoker.ApiClient;
import org.genesys.amphibian.client.model.TraitDataFilter;
import org.genesys.filerepository.model.ImageGallery;
import org.genesys.filerepository.model.RepositoryFolder;
import org.genesys.filerepository.service.BytesStorageService;
import org.genesys.filerepository.service.ImageGalleryService;
import org.genesys.server.model.dataset.Dataset;
import org.genesys.server.model.dataset.DatasetCreator;
import org.genesys.server.model.dataset.DatasetLocation;
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.AccessionId;
import org.genesys.server.model.genesys.AccessionRef;
import org.genesys.server.model.genesys.AccessionRemark;
import org.genesys.server.model.genesys.PDCI;
import org.genesys.server.model.genesys.QAccession;
import org.genesys.server.model.genesys.Taxonomy2;
import org.genesys.server.model.grin.TaxonomySpecies;
import org.genesys.server.model.impl.Country;
import org.genesys.server.model.impl.FaoInstitute;
import org.genesys.server.model.traits.Descriptor;
import org.genesys.server.model.traits.DescriptorList;
import org.genesys.server.model.vocab.VocabularyTerm;
import org.genesys.server.mvc.JspHelper;
import org.genesys.server.service.DownloadService;
import org.genesys.server.service.filter.AccessionFilter;
import org.genesys.server.service.worker.AccessionProcessor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.data.jpa.repository.support.Querydsl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.opencsv.CSVWriter;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.core.types.dsl.PathBuilderFactory;
import com.querydsl.jpa.JPQLQuery;

@Service
@Transactional(readOnly = true)
public class DownloadServiceImpl implements DownloadService, InitializingBean {

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

	private final String NEW_LINE = "\n";

	public static final int COL_INSTCODE;
	public static final int COL_DOI;
	public static final int COL_ACCENUMB;
	public static final int COL_HISTORIC;
	public static final int COL_GENUS;
	public static final int COL_SPECIES;
	public static final int COL_SPAUTHOR;
	public static final int COL_SUBTAXA;
	public static final int COL_SUBTAUTHOR;
	public static final int COL_GRIN_TAXON_ID;
	public static final int COL_GRIN_NAME;
	public static final int COL_GRIN_AUTHOR;
	public static final int COL_CROPNAME;
	public static final int COL_CROPCODE; // Genesys
	public static final int COL_SAMPSTAT;
	public static final int COL_ACQDATE;
	public static final int COL_ACCENAME;
	public static final int COL_ORIGCTY;
	public static final int COL_COLLSITE;
	public static final int COL_DECLATITUDE;
	public static final int COL_DECLONGITUDE;
	public static final int COL_COORDUNCERT;
	public static final int COL_COORDDATUM;
	public static final int COL_GEOREFMETH;
	public static final int COL_ELEVATION;
	public static final int COL_COLLDATE;
	public static final int COL_COLLSRC;
	public static final int COL_COLLNUMB;
	public static final int COL_COLLCODE;
	public static final int COL_COLLNAME;
	public static final int COL_COLLINSTADDR;
	public static final int COL_COLLMISSID;
	public static final int COL_DONORCODE;
	public static final int COL_DONORNAME;
	public static final int COL_DONORNUMB;
	public static final int COL_OTHERNUMB;
	public static final int COL_BREDCODE;
	public static final int COL_BREDNAME;
	public static final int COL_ANCEST;
	public static final int COL_DUPLSITE;
	public static final int COL_DUPLINSTNAME; // Not populated
	public static final int COL_STORAGE;
	public static final int COL_MLSSTAT;
	public static final int COL_ACCEURL;
	public static final int COL_REMARKS;
	public static final int COL_DATAPROVIDERID;
	public static final int COL_UUID;
	public static final int COL_LASTMODIFIED;
	public static final int COL_CURATIONTYPE;

	static {
		int col = 0;
		COL_INSTCODE = col++;
		COL_DOI = col++;
		COL_ACCENUMB = col++;
		COL_HISTORIC = col++;
		COL_CURATIONTYPE = col++;
		COL_GENUS = col++;
		COL_SPECIES = col++;
		COL_SPAUTHOR = col++;
		COL_SUBTAXA = col++;
		COL_SUBTAUTHOR = col++;
		COL_GRIN_TAXON_ID = col++;
		COL_GRIN_NAME = col++;
		COL_GRIN_AUTHOR = col++;
		COL_CROPNAME = col++;
		COL_CROPCODE = col++;
		COL_SAMPSTAT = col++;
		COL_ACQDATE = col++;
		COL_ACCENAME = col++;
		COL_ORIGCTY = col++;
		COL_COLLSITE = col++;
		COL_DECLATITUDE = col++;
		COL_DECLONGITUDE = col++;
		COL_COORDUNCERT = col++;
		COL_COORDDATUM = col++;
		COL_GEOREFMETH = col++;
		COL_ELEVATION = col++;
		COL_COLLDATE = col++;
		COL_COLLSRC = col++;
		COL_COLLNUMB = col++;
		COL_COLLCODE = col++;
		COL_COLLNAME = col++;
		COL_COLLINSTADDR = col++;
		COL_COLLMISSID = col++;
		COL_DONORCODE = col++;
		COL_DONORNAME = col++;
		COL_DONORNUMB = col++;
		COL_OTHERNUMB = col++;
		COL_BREDCODE = col++;
		COL_BREDNAME = col++;
		COL_ANCEST = col++;
		COL_DUPLSITE = col++;
		COL_DUPLINSTNAME= col++;
		COL_STORAGE = col++;
		COL_MLSSTAT = col++;
		COL_ACCEURL = col++;
		COL_REMARKS = col++;
		COL_DATAPROVIDERID = col++;
		COL_UUID = col++;
		COL_LASTMODIFIED = col++;
	}

	@Autowired
	private AccessionProcessor accessionProcessor;

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

	@Value("${genesys.catalog.url}")
	private String genesysCatalogUrl;

	private HashMap<String, Method> pdciMethods;

	@PersistenceContext
	private EntityManager entityManager;

	@Autowired
	private ImageGalleryService imageGalleryService;

	@Autowired
	private BytesStorageService byteStorageService;

	@Autowired(required = false)
	private ApiClient amphibianClient;

	private DatasetsApi datasetsApi;

	@Override
	public void afterPropertiesSet() throws Exception {
		if (amphibianClient != null) {
			datasetsApi = new DatasetsApi(amphibianClient);
		}
	}

	private static class WorkbookStyles {
		CellStyle dateStyle;

		public static WorkbookStyles create(Workbook workbook) {
			WorkbookStyles wbStyles = new WorkbookStyles();
			
			wbStyles.dateStyle = workbook.createCellStyle();
			wbStyles.dateStyle.setDataFormat(workbook.createDataFormat().getFormat("dd-mmm-yyyy"));
			wbStyles.dateStyle.setAlignment(HorizontalAlignment.RIGHT);

			return wbStyles;
		}
	}

	@Override
	public void writeXlsxMCPD(AccessionFilter filter, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		if (filter.isFulltextQuery()) {
			writeXlsxMCPD((action) -> {
				try {
					accessionProcessor.process(filter, action);
					LOG.warn("Done streaming MCPD rows");
				} catch (Exception e) {
					LOG.warn("Error generating: {}", e.getMessage());
				}
			}, outputStream, shortFilter, dataSource);
		} else {
			writeXlsxMCPD(filter.buildPredicate(), outputStream, shortFilter, dataSource);
		}
	}

	@Override
	public void writeXlsxMCPD(Predicate predicate, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		PathBuilder<Accession> builder = new PathBuilderFactory().create(Accession.class);
		Querydsl querydsl = new Querydsl(entityManager, builder);
		JPQLQuery<Long> query = querydsl.createQuery(QAccession.accession)
			// select id only
			.select(QAccession.accession.id)
			// order by id
			.orderBy(QAccession.accession.id.asc());

		// apply filter
		query.where(predicate);

		writeXlsxMCPD(query, outputStream, shortFilter, dataSource);
	}

	@Override
	public void writeXlsxMCPD(JPQLQuery<Long> queryAccessionId, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		writeXlsxMCPD((action) -> {
			try {
				accessionProcessor.processMCPD(queryAccessionId, action, null);
				LOG.warn("Done streaming MCPD rows");
			} catch (Exception e) {
				LOG.warn("Error generating: {}", e.getMessage());
			}
		}, outputStream, shortFilter, dataSource);
	}

	@Override
	public void writeXlsxMCPD(IDownloadAction action, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/MCPD.xlsx"));
		POIXMLProperties props = template.getProperties();

		POIXMLProperties.CoreProperties coreProp = props.getCoreProperties();
		coreProp.setCreated(Optional.of(new Date()));

		POIXMLProperties.CustomProperties custProp = props.getCustomProperties();
		if (StringUtils.isNotBlank(shortFilter)) {
			custProp.addProperty("Filter", shortFilter);
		}
		custProp.addProperty("Genesys URL", frontendUrl);

		// keep 50 rows in memory, exceeding rows will be flushed to disk
		DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook(template, 50);
		WorkbookStyles wbStyles = WorkbookStyles.create(wb);
		CellStyle dateStyle = wbStyles.dateStyle;

		Sheet legal = wb.getXSSFSheet("Legal information");
		Row r;
		Cell c;
		int row = 0;
		r = legal.createRow(row++);
		r.createCell(0).setCellValue("Server URL");
		r.createCell(1).setCellValue(frontendUrl);

		if (StringUtils.isNotBlank(shortFilter)) {
			r = legal.createRow(row++);
			r.createCell(0).setCellValue("Filters");
			r.createCell(1).setCellValue(shortFilter);
		}

		if (StringUtils.isNotBlank(dataSource)) {
			r = legal.createRow(row++);
			r.createCell(0).setCellValue("Data source");
			c = r.createCell(1);
			c.setCellValue(frontendUrl + dataSource);
		}

		r = legal.createRow(row++);
		r.createCell(0).setCellValue("Date");
		c = r.createCell(1);
		c.setCellStyle(dateStyle);
		c.setCellValue(new Date());
		r = legal.createRow(row);
		r.createCell(0).setCellValue("Attribution");
		r.createCell(1).setCellValue(frontendUrl + "/content/terms");

		// Register a streaming sheet
		final DeferredSXSSFSheet sheet = wb.getStreamingSheet("MCPD");
		sheet.setRowGenerator((sheet2) -> {

			// Write accession information
			final Writer writer = new Writer();

			action.apply((accessions) -> {
				for (Accession accession : accessions) {
					writer.processMCPD(sheet, wbStyles, accession);
				}
			});
		});

		// ((SXSSFSheet) sheet).flushRows();

		LOG.warn("Writing Excel to output stream");
		wb.write(outputStream);

		wb.close();
		wb.dispose();
		outputStream.flush();
		LOG.info("Done");
	}

	private class Writer {
		int i = 0;

		private void processMCPD(final Sheet sheet, WorkbookStyles wbStyles, final Accession accession) {
			Row row = sheet.createRow(++i);
			writeMCPDRow(row, wbStyles, accession);

			if (i % 1000 == 0) {
				LOG.info("Writing MCPD row={}", i);
				try {
					((SXSSFSheet) sheet).flushRows();
				} catch (IOException e) {
					LOG.error(e.getMessage(), e);
				}
			}
		}

		private void processPDCI(final Sheet sheet, final Accession accession) {
			final PDCI pdci = accession.getAccessionId().getPdci();
			if (pdci != null) {
				Row row = sheet.createRow(++i);
				writePDCIRow(row, accession, pdci);

				if (i % 1000 == 0) {
					LOG.info("Writing PDCI row={}", i);
					try {
						((SXSSFSheet) sheet).flushRows();
					} catch (IOException e) {
						LOG.error(e.getMessage(), e);
					}
				}
			}
		}
	}

	private void writeMCPDRow(final Row row, WorkbookStyles wbStyles, final Accession accession) {

		// Process and write result
		row.createCell(COL_INSTCODE).setCellValue(accession.getInstitute().getCode());
		row.createCell(COL_ACCENUMB).setCellValue(accession.getAccessionNumber());

		AccessionId accessionId = accession.getAccessionId();

		AccessionCollect collect = accessionId.getColl();
		if (collect != null) {
			updateCell(row, COL_COLLNUMB, collect.getCollNumb());
			updateCell(row, COL_COLLCODE, collect.getCollCode());
			updateCell(row, COL_COLLNAME, collect.getCollName());
			updateCell(row, COL_COLLINSTADDR, collect.getCollInstAddress());
			updateCell(row, COL_COLLMISSID, collect.getCollMissId());
			updateCell(row, COL_COLLSITE, collect.getCollSite());
			updateCell(row, COL_COLLDATE, collect.getCollDate());
			updateCell(row, COL_COLLSRC, collect.getCollSrc());
		}

		updateCell(row, COL_BREDCODE, accessionId.getBreederCode());
		updateCell(row, COL_BREDNAME, accessionId.getBreederName());
		updateCell(row, COL_ANCEST, accession.getAncest());

		Taxonomy2 taxonomy = accession.getTaxonomy();
		if (taxonomy != null) {
			updateCell(row, COL_GENUS, taxonomy.getGenus());
			updateCell(row, COL_SPECIES, taxonomy.getSpecies());
			updateCell(row, COL_SPAUTHOR, taxonomy.getSpAuthor());
			updateCell(row, COL_SUBTAXA, taxonomy.getSubtaxa());
			updateCell(row, COL_SUBTAUTHOR, taxonomy.getSubtAuthor());

			TaxonomySpecies currentGrinTaxonomy = taxonomy.getCurrentTaxonomySpecies();
			if (currentGrinTaxonomy != null) {
				updateCell(row, COL_GRIN_TAXON_ID, currentGrinTaxonomy.getId());
				updateCell(row, COL_GRIN_NAME, currentGrinTaxonomy.getName());
				updateCell(row, COL_GRIN_AUTHOR, currentGrinTaxonomy.getNameAuthority());
			}
		}

		updateCell(row, COL_CROPNAME, accession.getCropName());
		if (accession.getCrop() != null) {
			updateCell(row, COL_CROPCODE, accession.getCrop().getShortName());
		}
		updateCell(row, COL_ACQDATE, accession.getAcquisitionDate());

		Country origin = accession.getCountryOfOrigin();
		if (origin != null) {
			updateCell(row, COL_ORIGCTY, origin.getCode3());
		}

		AccessionId aid = accessionId;
		if (aid != null) {
			updateCell(row, COL_DECLATITUDE, aid.getLatitude());
			updateCell(row, COL_DECLONGITUDE, aid.getLongitude());
			updateCell(row, COL_COORDUNCERT, aid.getCoordinateUncertainty());
			updateCell(row, COL_COORDDATUM, aid.getCoordinateDatum());
			updateCell(row, COL_GEOREFMETH, aid.getGeoreferenceMethod());
			updateCell(row, COL_ELEVATION, aid.getElevation());

		}

		updateCell(row, COL_SAMPSTAT, accession.getSampStat());

//		// This is the wrong source for COLLSRC
//		if (StringUtils.isNotBlank(accession.getAcquisitionSource())) {
//			Cell c = row.createCell(COL_COLLSRC);
//			try {
//				c.setCellValue(Integer.parseInt(accession.getAcquisitionSource()));
//			} catch (NumberFormatException e) {
//				c.setCellValue(accession.getAcquisitionSource());
//			}
//		}

		updateCell(row, COL_DUPLSITE, accessionId.getDuplSite());
		// updateCell(row, COL_DUPLSITENAME, accession.getAccessionId().getDuplSiteName()));
		updateCell(row, COL_STORAGE, accessionId.getStorage());
		updateCell(row, COL_MLSSTAT, accession.getMlsStatus());

		List<AccessionAlias> names = accessionId.getAliases();
		if (names != null && names.size() > 0) {
			String acceName = null;
			String otherNumb = null;
//			String donorNumb = null;
			for (AccessionAlias a : names) {
				switch (a.getAliasType()) {
				case ACCENAME:
					acceName = addName(acceName, a.getName());
					break;
//				case DONORNUMB:
//					donorNumb = a.getName();
//					break;
				case OTHERNUMB:
					otherNumb = addName(otherNumb, a.getName(), a.getUsedBy());
					break;
				default:
					break;
				}
			}

			updateCell(row, COL_ACCENAME, acceName);
			updateCell(row, COL_OTHERNUMB, otherNumb);
		}

		updateCell(row, COL_DONORCODE, accession.getDonorCode());
		updateCell(row, COL_DONORNAME, accession.getDonorName());
		updateCell(row, COL_DONORNUMB, accession.getDonorNumb());

		if (accession.getUuid() != null) {
			updateCell(row, COL_UUID, accession.getUuid().toString());
		}

		updateCell(row, COL_HISTORIC, accession.isHistoric());

		if (StringUtils.isNotBlank(accession.getAcceUrl())) {
			updateCell(row, COL_ACCEURL, accession.getAcceUrl());
		}

		List<AccessionRemark> remarks = accessionId.getRemarks();
		if (remarks != null && remarks.size() > 0) {
			String r = "";
			for (AccessionRemark remark : remarks) {
				if (r.length() > 0)
					r += ";";
				if (StringUtils.isNotBlank(remark.getFieldName())) {
					r += remark.getFieldName() + ":" + remark.getRemark();
				} else {
					r += remark.getRemark();
				}
			}

			updateCell(row, COL_REMARKS, r);
		}

		if (StringUtils.isNotBlank(accession.getDoi())) {
			final String doi = accession.getDoi();
			updateCell(row, COL_DOI, doi);
		}

		if (accession.getCurationType() != null) {
			updateCell(row, COL_CURATIONTYPE, accession.getCurationType().name());
		}
		
		{
			updateCell(row, COL_LASTMODIFIED, accession.getLastModifiedDate(), wbStyles.dateStyle);
			updateCell(row, COL_DATAPROVIDERID, accession.getDataProviderId());
		}
	}

	@Override
	public void writeXlsxDescriptorList(final DescriptorList descriptorList, final OutputStream outputStream) throws IOException {
		final XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/CATALOG-DESCRIPTORLIST.xlsx"));

		final DeferredSXSSFWorkbook workbook = new DeferredSXSSFWorkbook(template, 50);
		WorkbookStyles wbStyles = WorkbookStyles.create(workbook);

		final Sheet metadataSheet = workbook.getXSSFSheet("metadata");
		descriptorListWriteMetadata(descriptorList, metadataSheet, wbStyles);

		final DeferredSXSSFSheet descriptorsSheet = workbook.getStreamingSheet("descriptors");
		descriptorsSheet.setRowGenerator((sheet) -> {
			writeDescriptorsSheet(descriptorList.getDescriptors(), sheet);
		});

		workbook.write(outputStream);
		workbook.close();
		workbook.dispose();
		outputStream.flush();
	}

	private void descriptorListWriteMetadata(DescriptorList descriptorList, Sheet sheet, WorkbookStyles wbStyles) {
		int row = 1, col = 1;
		// 1 uuid
		updateCell(sheet.getRow(row++), col, descriptorList.getUuid().toString());
		// 2 version
		updateCell(sheet.getRow(row++), col, descriptorList.getVersion());
		// 3 Descriptors list provider
		updateCell(sheet.getRow(row++), col, descriptorList.getOwner().getName());
		// 4 Title
		updateCell(sheet.getRow(row++), col, descriptorList.getTitle());
		// 5 Descriptors list version
		updateCell(sheet.getRow(row++), col, descriptorList.getVersionTag());
		// 6 Descriptors list description
		updateCell(sheet.getRow(row++), col, descriptorList.getDescription());
		// 7 Descriptors list URL
		updateCellUrl(sheet.getRow(row++), col, genesysCatalogUrl + "/descriptorlists/" + descriptorList.getUuid());
		// 8 URL to definition
		updateCellUrl(sheet.getRow(row++), col, descriptorList.getUrl());
		// 9 Original publisher
		updateCell(sheet.getRow(row++), col, descriptorList.getPublisher());
		// 10 Bibliographic citation
		updateCell(sheet.getRow(row++), col, descriptorList.getBibliographicCitation());
		// 11 Date of registration of the descriptors list
		updateCell(sheet.getRow(row++), col, descriptorList.getCreatedDate(), wbStyles.dateStyle);
		// 12 Crop
		updateCell(sheet.getRow(row++), col, descriptorList.getCrop());
		// 13 Descriptors list email
		updateCellEmail(sheet.getRow(row++), col, descriptorList.getOwner().getEmail());
		// 14 Descriptors list address
		updateCell(sheet.getRow(row++), col, descriptorList.getOwner().getAddress());
		// 15 Recommended citation
		{
			StringBuilder citation = new StringBuilder();
			// Genetic Resources Research Institute of the Kenya Agricultural and Livestock
			// Research Organization
			citation.append(descriptorList.getOwner().getName());
			// (2018)
			citation.append(" (").append(LocalDate.ofInstant(descriptorList.getCreatedDate(), ZoneOffset.UTC).getYear()).append(")");
			citation.append(". ");
			// Key descriptors for forage legumes.
			citation.append(descriptorList.getTitle()).append(". ");
			// Version 1.0.
			citation.append("Version ").append(descriptorList.getVersionTag()).append(". ");
			citation.append(genesysCatalogUrl).append("/descriptorlists/").append(descriptorList.getUuid());
			// accessed on
			citation.append(" accessed on ");
			citation.append(LocalDate.now().format(DateTimeFormatter.ISO_DATE));
			updateCell(sheet.getRow(row++), col, citation.toString());
		}
		updateCell(sheet.getRow(row++), col, descriptorList.getOriginalLanguageTag());
	}

	@Override
	public void writeXlsxDataset(final Dataset dataset, final OutputStream outputStream) throws IOException {
		final XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/CATALOG-DATASET.xlsx"));

		final DeferredSXSSFWorkbook workbook = new DeferredSXSSFWorkbook(template, 50);
		WorkbookStyles wbStyles = WorkbookStyles.create(workbook);

		final Sheet datasetSheet = workbook.getXSSFSheet("metadata");
		datasetWriteMetadata(dataset, datasetSheet, wbStyles);

		final DeferredSXSSFSheet accessionsSheet = workbook.getStreamingSheet("accessions");
		accessionsSheet.setRowGenerator((sheet) -> {
			datasetWriteAccessionsSheet(dataset.getAccessionRefs(), sheet);
		});

		final DeferredSXSSFSheet descriptorsSheet = workbook.getStreamingSheet("descriptors");
		descriptorsSheet.setRowGenerator((sheet) -> {
			writeDescriptorsSheet(dataset.getDescriptors(), sheet);
		});

		final DeferredSXSSFSheet locationsSheet = workbook.getStreamingSheet("location_timing");
		locationsSheet.setRowGenerator((sheet) -> {
			datasetWriteLocationsSheet(dataset.getLocations(), sheet);
		});

		final DeferredSXSSFSheet creatorsSheet = workbook.getStreamingSheet("creators");
		creatorsSheet.setRowGenerator((sheet) -> {
			datasetWriteCreatorsSheet(dataset.getCreators(), sheet);
		});

		workbook.write(outputStream);
		workbook.close();
		workbook.dispose();
		outputStream.flush();
	}
	
	private void datasetWriteMetadata(Dataset dataset, Sheet sheet, WorkbookStyles wbStyles) {
		int row = 1, col = 1;
		// 1 uuid
		updateCell(sheet.getRow(row++), col, dataset.getUuid().toString());
		// 2 version
		updateCell(sheet.getRow(row++), col, dataset.getVersion());
		// 3 Dataset provider
		updateCell(sheet.getRow(row++), col, dataset.getOwner().getName());
		// 4 Title
		updateCell(sheet.getRow(row++), col, dataset.getTitle()); // TODO Does this need translation?
		// 5 Dataset version
		updateCell(sheet.getRow(row++), col, dataset.getVersionTag());
		// 6 Dataset description
		updateCell(sheet.getRow(row++), col, dataset.getDescription()); // TODO Does this need translation?
		// 7 Dataset URL
		updateCellUrl(sheet.getRow(row++), col, genesysCatalogUrl + "/datasets/" + dataset.getUuid());
		// 8 Date of registration of the dataset
		updateCell(sheet.getRow(row++), col, dataset.getCreatedDate(), wbStyles.dateStyle);
		// 9 License
		updateCell(sheet.getRow(row++), col, dataset.getRights());
		// 10 Language of the dataset
		updateCell(sheet.getRow(row++), col, dataset.getOriginalLanguageTag()); // TODO Does this need translation lang?
		// 11 Related resources
		updateCell(sheet.getRow(row++), col, dataset.getSource());

		// 12 Crops
		if (dataset.getCrops() != null) {
			updateCell(sheet.getRow(row++), col, dataset.getCrops().stream().collect(Collectors.joining(", ")));
		}
		// 13 Dataset provider email
		updateCellEmail(sheet.getRow(row++), col, dataset.getOwner().getEmail());
		// 14 Dataset provider addresss
		updateCell(sheet.getRow(row++), col, dataset.getOwner().getAddress());

		// 15 Recommended citation
		{
			StringBuilder citation = new StringBuilder();
			// Genetic Resources Research Institute of the Kenya Agricultural and Livestock
			// Research Organization
			citation.append(dataset.getOwner().getName());
			// (2018)
			if (StringUtils.isNotBlank(dataset.getCreated())) {
				citation.append(" (").append(dataset.getCreated()).append(")");
			}
			// .
			citation.append(". ");
			// Characterization of groundnut accessions in Kakamega (Kenya).
			citation.append(dataset.getTitle()).append(". ");
			// Version 1.0.
			citation.append("Version ").append(dataset.getVersionTag()).append(". ");
			// https://catalog.demo.genesys-pgr.org/datasets/65c12a21-7fa4-481e-ae79-758426c4cae3
			citation.append(genesysCatalogUrl).append("/datasets/").append(dataset.getUuid());
			// accessed on
			citation.append(" accessed on ");
			citation.append(LocalDate.now().format(DateTimeFormatter.ISO_DATE));
			updateCell(sheet.getRow(row++), col, citation.toString());
		}

	}

	private void writeDescriptorsSheet(final List<Descriptor> descriptors, final Sheet sheet) {
		int item = 2;
		final var columnOffset = -1;

		for (final Descriptor descriptor : descriptors) {
			item++;

			final Row nextRow = sheet.createRow(item);

			updateCell(nextRow, columnOffset + 1, descriptor.getUuid().toString());
			updateCell(nextRow, columnOffset + 2, descriptor.getVersion());
			updateCell(nextRow, columnOffset + 3, descriptor.getCrop());
			updateCell(nextRow, columnOffset + 4, descriptor.getVersionTag());
			updateCell(nextRow, columnOffset + 5, descriptor.getTitle());
			updateCell(nextRow, columnOffset + 6, descriptor.getCategory().name());
			updateCell(nextRow, columnOffset + 7, descriptor.getDescription());
			updateCell(nextRow, columnOffset + 8, descriptor.getDataType().name());
			updateCell(nextRow, columnOffset + 9, descriptor.getIntegerOnly());
			updateCell(nextRow, columnOffset + 10, descriptor.getMinValue());
			updateCell(nextRow, columnOffset + 11, descriptor.getMaxValue());

			if (descriptor.getVocabulary() != null) {
				updateCell(nextRow, 12, descriptor.getVocabulary().getUuid().toString());
			}

			final StringBuilder codes = new StringBuilder();
			final StringBuilder codeTitles = new StringBuilder();
			final StringBuilder codeDescriptions = new StringBuilder();
			for (final VocabularyTerm term : descriptor.getTerms()) {
				codes.append(NEW_LINE).append(term.getCode());
				codeTitles.append(NEW_LINE).append(term.getTitle());
				String description = term.getDescription();
				codeDescriptions.append(NEW_LINE).append(description != null ? description.replace(NEW_LINE, "; ") : "");
			}
			updateCell(nextRow, columnOffset + 13, codes.toString().replaceFirst(NEW_LINE, ""));
			updateCell(nextRow, columnOffset + 14, codeTitles.toString().replaceFirst(NEW_LINE, ""));
			updateCell(nextRow, columnOffset + 15, codeDescriptions.toString().replaceFirst(NEW_LINE, ""));

			updateCell(nextRow, columnOffset + 16, descriptor.getColumnName());
			updateCell(nextRow, columnOffset + 17, descriptor.getUom());

			// updateCell(nextRow, columnOffset + 18, descriptor.); // Heading number
			updateCell(nextRow, columnOffset + 19, descriptor.getOriginalLanguageTag());
		}
	}

	private void datasetWriteLocationsSheet(final List<DatasetLocation> locations, final Sheet sheet) {
		int item = 2;
		for (final DatasetLocation location : locations) {
			item++;

			final Row nextRow = sheet.createRow(item);

			int col = 0;
			updateCell(nextRow, col++, location.getUuid().toString());
			updateCell(nextRow, col++, location.getVersion());
			updateCell(nextRow, col++, location.getCountryCode());
			updateCell(nextRow, col++, location.getStateProvince());
			updateCell(nextRow, col++, location.getVerbatimLocality());
			updateCell(nextRow, col++, location.getDescription());
			updateCell(nextRow, col++, location.getDecimalLongitude());
			updateCell(nextRow, col++, location.getDecimalLatitude());
			updateCell(nextRow, col++, location.getStartDate());
			updateCell(nextRow, col++, location.getEndDate());
		}
	}

	private void datasetWriteCreatorsSheet(final List<DatasetCreator> creators, final Sheet sheet) {
		int item = 2;
		final var columnOffset = -1;

		for (final DatasetCreator creator : creators) {
			item++;

			final Row nextRow = sheet.createRow(item);

			updateCell(nextRow, columnOffset + 1, creator.getUuid().toString());
			updateCell(nextRow, columnOffset + 2, creator.getVersion());
			updateCell(nextRow, columnOffset + 3, creator.getFullName());
			updateCell(nextRow, columnOffset + 4, creator.getRole().name());
			updateCell(nextRow, columnOffset + 5, creator.getInstitutionalAffiliation());
			updateCell(nextRow, columnOffset + 9, creator.getInstituteAddress());
		}
	}

	private void datasetWriteAccessionsSheet(final Collection<? extends AccessionRef<Dataset>> identifiers, final Sheet sheet) {
		int item = 2;
		final var columnOffset = -1;

		for (final AccessionRef<Dataset> accessionRef : identifiers) {
			item++;

			final Row nextRow = sheet.createRow(item);

			Accession accession = accessionRef.getAccession();
			if (accession != null) {
				updateCell(nextRow, columnOffset + 1, accession.getInstCode());
				updateCell(nextRow, columnOffset + 2, accession.getAccessionNumber());
//				addCellUrl(cellAccession, getAccessionUrl(accession));
				updateCell(nextRow, columnOffset + 3, accession.getTaxonomy().getGenus());
				updateCell(nextRow, columnOffset + 4, accession.getTaxonomy().getSpecies());
				updateCell(nextRow, columnOffset + 5, accession.getDoi());
			} else {
				updateCell(nextRow, columnOffset + 1, accessionRef.getInstCode());
				updateCell(nextRow, columnOffset + 2, accessionRef.getAcceNumb());
//				addCellUrl(cellAccession, getAccessionUrl(null, accessionRef.getDoi()));
				updateCell(nextRow, columnOffset + 3, accessionRef.getGenus());
				updateCell(nextRow, columnOffset + 4, accessionRef.getSpecies());
				updateCell(nextRow, columnOffset + 5, accessionRef.getDoi());
			}
		}
	}


//	private String getAccessionUrl(Accession accession) {
//		if (accession == null) return null;
//		return getAccessionUrl(accession.getUuid().toString(), accession.getDoi());
//	}
//
//	private String getAccessionUrl(String uuid, String doi) {
//		if (uuid == null && doi == null) return null;
//		return frontendUrl + "/" + (doi == null ? "a/" + uuid : doi);
//	}

	@Override
	public void writeXlsxDatasetObservations(final Dataset dataset, TraitDataFilter traitDataFilter, List<Descriptor> descriptors, final OutputStream outputStream) throws IOException {
		final XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/amphibian-dataset.xlsx"));

		final DeferredSXSSFWorkbook workbook = new DeferredSXSSFWorkbook(template, 50);

		final XSSFSheet dataSheet = workbook.getXSSFSheet("Data");
		final Row headerRow = dataSheet.getRow(0);
		int headerCellItem = 0;
		var accessionCell = updateCell(headerRow, headerCellItem++, "Accession");
		for (Descriptor descriptor : descriptors) {
			var c = updateCell(headerRow, headerCellItem++, descriptor.getTitle());
			c.setCellStyle(accessionCell.getCellStyle());
		}

		final XSSFSheet codedSheet = workbook.getXSSFSheet("Coded");
		final Row codedHeaderRow = codedSheet.getRow(0);
		int codedHeaderCellItem = 0;
		accessionCell = updateCell(codedHeaderRow, codedHeaderCellItem++, "Accession");
		for (Descriptor descriptor : descriptors) {
			var c= updateCell(codedHeaderRow, codedHeaderCellItem++, descriptor.getTitle());
			c.setCellStyle(accessionCell.getCellStyle());
		}

		List<UUID> accessionUuids = new ArrayList<>();

		streamAndWriteData(dataset, data -> {
			datasetFetchAcceUuids(data, accessionUuids);
			datasetWriteAmphibianData(data, descriptors, dataSheet, true);
			datasetWriteAmphibianData(data, descriptors, codedSheet, false);
		}, traitDataFilter, 1000, null);

//		var codedTable = codedSheet.createTable(new AreaReference(new CellReference(0, 0, true, true), new CellReference(codedSheet.getLastRowNum(), descriptors.size(), true, true), workbook.getSpreadsheetVersion()));
//		codedTable.setDisplayName("Encoded");
//		codedTable.updateReferences();
//		codedTable.updateHeaders();
//
//		var dataTable = dataSheet.createTable(new AreaReference(new CellReference(0, 0, true, true), new CellReference(dataSheet.getLastRowNum(), descriptors.size(), true, true), workbook.getSpreadsheetVersion()));
//		dataTable.setDisplayName("Observations");
//		dataTable.updateReferences();
//		dataTable.updateHeaders();

		var accessionRefs = dataset.getAccessionRefs().stream().filter(acceRef -> acceRef.getAccession() != null && accessionUuids.contains(acceRef.getAccession().getUuid())).collect(Collectors.toList());

		final DeferredSXSSFSheet descriptorsSheet = workbook.getStreamingSheet("descriptors");
		descriptorsSheet.setRowGenerator((sheet) -> {
			writeDescriptorsSheet(dataset.getDescriptors(), sheet);
		});
		
		final DeferredSXSSFSheet accessionsSheet = workbook.getStreamingSheet("accessions");
		accessionsSheet.setRowGenerator((sheet) -> {
			datasetWriteAccessionsSheet(accessionRefs, sheet);
		});
		
		WorkbookStyles wbStyles = WorkbookStyles.create(workbook);
		final Sheet datasetSheet = workbook.getXSSFSheet("metadata");
		datasetWriteMetadata(dataset, datasetSheet, wbStyles);

		final DeferredSXSSFSheet locationsSheet = workbook.getStreamingSheet("location_timing");
		locationsSheet.setRowGenerator((sheet) -> {
			datasetWriteLocationsSheet(dataset.getLocations(), sheet);
		});

		final DeferredSXSSFSheet creatorsSheet = workbook.getStreamingSheet("creators");
		creatorsSheet.setRowGenerator((sheet) -> {
			datasetWriteCreatorsSheet(dataset.getCreators(), sheet);
		});

		int sheetPosition = 0;
		workbook.setSheetOrder("data", sheetPosition++);
		workbook.setSheetOrder("coded", sheetPosition++);
		workbook.setSheetOrder("descriptors", sheetPosition++);
		workbook.setSheetOrder("accessions", sheetPosition++);
		workbook.setSheetOrder("metadata", sheetPosition++);
		workbook.setSheetOrder("location_timing", sheetPosition++);
		workbook.setSheetOrder("creators", sheetPosition++);

		workbook.write(outputStream);
		workbook.close();
		workbook.dispose();
		outputStream.flush();
	}

	private void datasetFetchAcceUuids(List<Object> dataList, List<UUID> accessionUuids) {
		for (Object rowData : dataList) {
			Map<String, Object> rowDataMap = (Map<String, Object>) rowData;
			var uuid = String.valueOf(rowDataMap.get("accession"));
			accessionUuids.add(UUID.fromString(uuid));
		}
	}

	private void streamAndWriteData(Dataset dataset, DatasetWriteAction action, TraitDataFilter traitDataFilter, int batchSize, UUID streamUuid) {
		var observationResponse = datasetsApi.streamObservations(dataset.getUuid().toString(), null, batchSize, streamUuid, traitDataFilter);
		List<Object> data;
		if (observationResponse == null || observationResponse.getData() == null) {
			return;
		} else {
			data = observationResponse.getData();
		}
		action.write(data);
		streamUuid = observationResponse.getStreamUuid();
		if (streamUuid != null) {
			streamAndWriteData(dataset, action, traitDataFilter, batchSize, streamUuid);
		}
	}
	
	private interface DatasetWriteAction {
		void write(List<Object> data);
	}
	
	private void datasetWriteAmphibianData(List<Object> dataList, List<Descriptor> descriptors, XSSFSheet sheet, boolean useTextLabels) {
		int rowItem = sheet.getLastRowNum() + 1;

		for (Object rowData : dataList) {
			Map<String, Object> rowDataMap = (Map<String, Object>) rowData;
			final Row row = sheet.createRow(rowItem++);

			int cellItem = 0;
			updateCell(row, cellItem++, String.valueOf(rowDataMap.get("accessionNumber")));
//			addCellUrl(cellAccession, getAccessionUrl(String.valueOf(rowDataMap.get("accession")), String.valueOf(rowDataMap.get("doi"))));

			var descriptorMap = descriptors.stream().collect(Collectors.toMap(
				descriptor -> descriptor,
				descriptor -> descriptor.getTerms().stream().collect(Collectors.toMap(VocabularyTerm::getCode, VocabularyTerm::getTitle))
			));
			
			for (Descriptor descriptor : descriptors) {
				List<Object> dataObjects = (List<Object>) rowDataMap.get(descriptor.getUuid().toString());
				if (dataObjects == null || dataObjects.isEmpty()) {
					updateCell(row, cellItem++, StringUtils.EMPTY);
				} else {
					if (useTextLabels) {
						dataObjects = replaceWithTitles(dataObjects, descriptorMap.get(descriptor));
					}
					var updated = dataObjects.size() > 1 ? 
						updateCell(row, cellItem++, new HashSet<>(dataObjects)) :
						updateCell(row, cellItem++, dataObjects.get(0));
				}
			}
		}
	}

	private List<Object> replaceWithTitles(List<Object> dataList, Map<String, String> termsMap) {
		if (termsMap.isEmpty()) {
			return dataList;
		}
		return dataList.stream().map(data -> {
			var title = termsMap.get(String.valueOf(data));
			return title != null ? title : data;
		}).collect(Collectors.toList());
	}

	@Override
	public void writeXlsxDescriptor(final List<Descriptor> descriptors, final OutputStream outputStream) throws IOException {
		XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/DESCRIPTORS.xlsx"));
		DeferredSXSSFWorkbook workbook = new DeferredSXSSFWorkbook(template, 50);

		DeferredSXSSFSheet descriptorSheet = workbook.getStreamingSheet("Descriptors");
		descriptorSheet.setRowGenerator((sheet) -> {
			writeDescriptorInformation(descriptors, sheet);
		});

		workbook.write(outputStream);
		workbook.close();
		workbook.dispose();
		outputStream.flush();
	}

	private void writeDescriptorInformation(List<Descriptor> descriptors, Sheet sheet) {
		int item = 0;

		for (Descriptor descriptor : descriptors) {
			item++;

			Row nextRow = sheet.createRow(item);

			updateCell(nextRow, 0, descriptor.getUuid().toString());
			updateCell(nextRow, 1, descriptor.getVersion());
			updateCell(nextRow, 2, descriptor.getCrop());
			updateCell(nextRow, 3, descriptor.getVersionTag());
			updateCell(nextRow, 4, descriptor.getTitle());
			updateCell(nextRow, 5, descriptor.getCategory().name());
			updateCell(nextRow, 6, descriptor.isKey());
			updateCell(nextRow, 7, descriptor.getDescription());
			updateCell(nextRow, 8, descriptor.getDataType().name());
			updateCell(nextRow, 9, descriptor.getIntegerOnly());
			updateCell(nextRow, 10, descriptor.getMinValue());
			updateCell(nextRow, 11, descriptor.getMaxValue());

			String vocabulary = descriptor.getVocabulary() == null ? "" : descriptor.getVocabulary().getTitle();
			updateCell(nextRow, 12, vocabulary);

			updateCell(nextRow, 13, descriptor.isPublished());
			updateCell(nextRow, 14, descriptor.getColumnName());
			updateCell(nextRow, 15, descriptor.getUom());
		}
	}

	@Override
	public void writeXlsxPDCI(final AccessionFilter filter, final OutputStream outputStream, String shortFilter, String dataSource) throws IOException {

		XSSFWorkbook template = new XSSFWorkbook(getClass().getResourceAsStream("/template/download/PDCI.xlsx"));

		// keep 1000 rows in memory, exceeding rows will be flushed to disk
		DeferredSXSSFWorkbook wb = new DeferredSXSSFWorkbook(template, 50);

		CellStyle dateStyle = wb.createCellStyle();
		dateStyle.setDataFormat(wb.createDataFormat().getFormat("dd/mm/yyyy"));
		dateStyle.setAlignment(HorizontalAlignment.LEFT);

		Sheet legal = wb.getXSSFSheet("Legal information");
		Row r;
		Cell c;
		int row = 0;
		r = legal.createRow(row++);
		r.createCell(0).setCellValue("Server URL");
		updateCellUrl(r, 1, frontendUrl);

		if (StringUtils.isNotBlank(shortFilter)) {
			r = legal.createRow(row++);
			r.createCell(0).setCellValue("Filters");
			r.createCell(1).setCellValue(shortFilter);
		}

		if (StringUtils.isNotBlank(dataSource)) {
			r = legal.createRow(row++);
			r.createCell(0).setCellValue("Data source");
			updateCellUrl(r, 1, frontendUrl + dataSource);
		}

		r = legal.createRow(row++);
		r.createCell(0).setCellValue("Date");
		c = r.createCell(1);
		c.setCellStyle(dateStyle);
		c.setCellValue(new Date());
		r = legal.createRow(row);
		r.createCell(0).setCellValue("Attribution");
		updateCellUrl(r, 1, frontendUrl + "/content/terms");

		final DeferredSXSSFSheet pdciSheet = wb.getStreamingSheet("PDCI");
		pdciSheet.setRowGenerator((sheet) -> {
			final Writer writer = new Writer();

			// Write accession information
			try {
				accessionProcessor.process(filter, (accessions) -> {
					for (Accession accession : accessions) {
						writer.processPDCI(sheet, accession);
					}
				});
			} catch (Exception e) {
				LOG.warn("Error generating: {}", e.getMessage());
			}
		});

		LOG.info("Writing to output stream");
		wb.write(outputStream);
		wb.close();
		wb.dispose();
		outputStream.flush();
		LOG.info("Done");
	}

	@Override
	public void writeCsvLastUpdated(final List<Object[]> lastUpdatedStatistics, final OutputStreamWriter outputStream) {

		try (CSVWriter writer = new CSVWriter(outputStream, '\t', '"', '\\', "\n")) {
			final JspHelper helper = new JspHelper();
			final String[] months = helper.monthNames(LocaleContextHolder.getLocale());
			final String[] entries = "Update date#Institute code#Institute name#Count".split("#");
			writer.writeNext(entries);

			for (Object[] row : lastUpdatedStatistics) {
				FaoInstitute institute = (FaoInstitute) row[3];

				String day = (row[2] != null) ? String.valueOf(row[2]) : "";
				String month = (row[1] != null) ? months[((Integer) row[1]) - 1] : "";
				String year = (row[0] != null) ? String.valueOf(row[0]) : "";

				StringBuilder builder = new StringBuilder();
				builder.append(day).append("-").append(month).append("-").append(year).append("#").append(institute.getCode()).append("#").append(institute.getFullName()).append(
					"#").append(row[4]);

				writer.writeNext(builder.toString().split("#"));
			}
			writer.flush();
			LOG.info("Done");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void writePDCIRow(Row row, Accession accession, PDCI pdci) {
		// Process and write result
		int cell = 0;
		updateCell(row, cell++, accession.getInstCode());
		updateCell(row, cell++, accession.getAccessionNumber());
		updateCell(row, cell++, accession.getGenus());

		if (accession.getUuid() != null) {
			updateCell(row, cell++, accession.getUuid().toString());
		}

		updateCell(row, cell++, pdci.getScore());
		for (String indicator : pdci.getIndependentItems()) {
			updateCell(row, cell++, getPDCIScore(pdci, indicator));
		}
		for (String indicator : pdci.getDependentItems()) {
			updateCell(row, cell++, getPDCIScore(pdci, indicator));
		}
	}

	private Number getPDCIScore(PDCI pdci, String indicator) {
		synchronized (this) {
			if (pdciMethods == null) {
				pdciMethods = new HashMap<String, Method>();
				try {
					for (String name : PDCI.independentItems) {
						Method m = PDCI.class.getMethod("get" + StringUtils.capitalize(name));
						if (m.getReturnType() == int.class) {
							pdciMethods.put(name, m);
						}
					}
					for (String name : PDCI.dependentItems) {
						Method m = PDCI.class.getMethod("get" + StringUtils.capitalize(name));
						if (m.getReturnType() == int.class) {
							pdciMethods.put(name, m);
						}
					}
				} catch (Throwable e) {
					LOG.warn(e.getMessage(), e);
				}
			}
		}
		try {
			Method m = pdciMethods.get(indicator);
			if (m != null) {
				return (int) m.invoke(pdci);
			} else {
				return -1;
			}
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			LOG.warn(e.getMessage());
			return -1;
		}
	}


	public static String catalogDescriptorCategory(String name) {
		switch (name) {
		case "Morphology":
			return "CHARACTERIZATION";
		case "Chemical":
			return "EVALUATION";
		case "Disease":
			return "BIOTICSTRESS";
		case "Insect":
			return "BIOTICSTRESS";
		case "Stress":
			return "ABIOTICSTRESS";
		case "Growth":
			return "EVALUATION";
		case "Phenology":
			return "CHARACTERIZATION";
		case "Cytological":
			return "CHARACTERIZATION";
		case "Nematode":
			return "BIOTICSTRESS";
		case "Production":
			return "EVALUATION";
		case "Physiology":
			return "CHARACTERIZATION";
		case "Flower Fruit":
			return "CHARACTERIZATION";
		case "Quality":
			return "EVALUATION";
		case "Molecular":
			return "MOLECULAR";
		default:
			return name;
		}
	}

	public static String catalogDataType(int fieldType) {
		switch (fieldType) {
		case 0:
			// Coded
			return "CODED";
		case 1:
			// Double
			return "NUMERIC";
		case 2:
			// Long
			return "NUMERIC";
		default:
			return null;
		}
	}

	private String addName(String otherNames, String name, String usedBy) {
		if (StringUtils.isBlank(name)) {
			return otherNames;
		} else if (StringUtils.isBlank(usedBy)) {
			return addName(otherNames, name);
		} else {
			return addName(otherNames, usedBy + ":" + name);
		}
	}

	private String addName(String otherNames, String name) {
		if (StringUtils.isBlank(name))
			return otherNames;
		if (otherNames == null)
			return name;
		return otherNames + ";" + name;
	}

	@Override
	public void writeAccessionImageArchive(AccessionFilter filter, OutputStream outputStream) throws Exception {
		var filterWithImages = filter.copy(AccessionFilter.class);
		filterWithImages.images = true;

		try (final ZipOutputStream zos = new ZipOutputStream(outputStream)) {
			final StringBuilder commentBuilder = new StringBuilder("Genesys Accession Images");
			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();

			var th = new ThrowableHolder<IOException>();
			accessionProcessor.process(filterWithImages, (accessions) -> {
				for (Accession accession : accessions) {
					RepositoryFolder folder = accession.getAccessionId().getRepositoryFolder();
					ImageGallery imageGallery = imageGalleryService.getImageGallery(folder);
					if (imageGallery != null) {
						for (var repositoryImage : imageGallery.getImages()) {
							var zipPath = Paths.get(repositoryImage.getFolder().getFolderPath().toString(), repositoryImage.getOriginalFilename()).toString();
							LOG.warn("Adding {}", zipPath);
							final ZipEntry imageEntry = new ZipEntry(zipPath);
							imageEntry.setTime(repositoryImage.getLastModifiedDate().toEpochMilli());
							imageEntry.setCreationTime(FileTime.fromMillis(repositoryImage.getCreatedDate().toEpochMilli()));
							imageEntry.setComment(repositoryImage.getTitle());
							zos.putNextEntry(imageEntry);
							// write here
							byteStorageService.get(repositoryImage.storagePath(), (inputStream) -> {
								try {
									inputStream.transferTo(zos);
								} catch (IOException e) {
									th.throwable = e;
								}
							});
							if (th.throwable != null) {
								LOG.warn("Streaming bytes from storage threw {}", th.throwable, th.throwable);
								throw th.throwable;
							}
							zos.closeEntry();
							zos.flush();
						}
					}
				}
			});
			if (th.throwable != null) {
				LOG.warn("Streaming bytes from storage threw {}", th.throwable, th.throwable);
				throw th.throwable;
			}

		} catch (EOFException e) {
			LOG.warn("Download was aborted: {}", e.getMessage());
			throw e;
		} catch (Throwable e) {
			LOG.warn("Error generating image zip archive: {}", e.getMessage());
			throw e;
		}
	}

	/**
	 * Internal holder class for a Throwable in a callback transaction model.
	 */
	private static class ThrowableHolder<T extends Throwable> {
		public T throwable;
	}

}