DownloadServiceImpl.java

/*
 * Copyright 2021 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.gringlobal.service.impl;

import static org.gringlobal.util.ExcelUtils.updateCell;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;
import java.util.Date;
import java.util.Optional;

import lombok.extern.slf4j.Slf4j;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.gringlobal.model.community.AccessionMCPD;
import org.gringlobal.service.AccessionService;
import org.gringlobal.service.DownloadService;
import org.gringlobal.service.filter.AccessionFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

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

	private static final int COL_INSTCODE;
	private static final int COL_DOI;
	private static final int COL_ACCENUMB;
	private static final int COL_HISTORICAL;
	private static final int COL_CURATIONTYPE;
	// taxonomy
	private static final int COL_GENUS;
	private static final int COL_SPECIES;
	private static final int COL_SPAUTHOR;
	private static final int COL_SUBTAXA;
	private static final int COL_SUBTAUTHOR;
	private static final int COL_CROPNAME;
	private static final int COL_SAMPSTAT;
	//
	private static final int COL_ACQDATE;
	private static final int COL_ACCENAME;
	// provenance
	private static final int COL_ORIGCTY;
	private static final int COL_COLLSITE;
	private static final int COL_DECLATITUDE;
	private static final int COL_DECLONGITUDE;
	private static final int COL_COORDUNCERT;
	private static final int COL_COORDDATUM;
	private static final int COL_GEOREFMETH;
	private static final int COL_ELEVATION;
	// collecting
	private static final int COL_COLLDATE;
	private static final int COL_COLLSRC;
	private static final int COL_COLLNUMB;
	private static final int COL_COLLCODE;
	private static final int COL_COLLNAME;
	private static final int COL_COLLINSTADDR;
	private static final int COL_COLLMISSID;
	// donor
	private static final int COL_DONORCODE;
	private static final int COL_DONORNAME;
	private static final int COL_DONORNUMB;
	private static final int COL_OTHERNUMB;
	// breeder
	private static final int COL_BREDCODE;
	private static final int COL_BREDNAME;
	private static final int COL_ANCEST;
	// dupl
	private static final int COL_DUPLSITE;
	private static final int COL_DUPLINSTNAME;
	// status
	private static final int COL_STORAGE;
	private static final int COL_MLSSTAT;
	private static final int COL_ACCEURL;
	private static final int COL_REMARKS;
	private static final int COL_DATAPROVIDERID;
	private static final int COL_LASTMODIFIED;

	static {
		int colNum = 0;
		COL_INSTCODE = colNum++;
		COL_DOI = colNum++;
		COL_ACCENUMB = colNum++;
		COL_HISTORICAL = colNum++;
		COL_CURATIONTYPE = colNum++;
		
		// taxonomy
		COL_GENUS = colNum++;
		COL_SPECIES = colNum++;
		COL_SPAUTHOR = colNum++;
		COL_SUBTAXA = colNum++;
		COL_SUBTAUTHOR = colNum++;
		COL_CROPNAME = colNum++;
		COL_SAMPSTAT = colNum++;
		//
		COL_ACQDATE = colNum++;
		COL_ACCENAME = colNum++;
		// provenance
		COL_ORIGCTY = colNum++;
		COL_COLLSITE = colNum++;
		COL_DECLATITUDE = colNum++;
		COL_DECLONGITUDE = colNum++;
		COL_COORDUNCERT = colNum++;
		COL_COORDDATUM = colNum++;
		COL_GEOREFMETH = colNum++;
		COL_ELEVATION = colNum++;
		// collecting
		COL_COLLDATE = colNum++;
		COL_COLLSRC = colNum++;
		COL_COLLNUMB = colNum++;
		COL_COLLCODE = colNum++;
		COL_COLLNAME = colNum++;
		COL_COLLINSTADDR = colNum++;
		COL_COLLMISSID = colNum++;
		// donor
		COL_DONORCODE = colNum++;
		COL_DONORNAME = colNum++;
		COL_DONORNUMB = colNum++;
		COL_OTHERNUMB = colNum++;
		// breeder
		COL_BREDCODE = colNum++;
		COL_BREDNAME = colNum++;
		COL_ANCEST = colNum++;
		// dupl
		COL_DUPLSITE = colNum++;
		COL_DUPLINSTNAME= colNum++;
		// status
		COL_STORAGE = colNum++;
		COL_MLSSTAT = colNum++;
		COL_ACCEURL = colNum++;
		COL_REMARKS = colNum++;
		COL_DATAPROVIDERID = colNum++;
		COL_LASTMODIFIED = colNum++;
	}

	@Autowired
	private AccessionService accessionService;

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

	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;
		}
	}

	private static interface IBatchAction<T> {
		Collection<T> apply(Collection<T> batch) throws Exception;
	}
	private static interface IDownloadAction<T> {
		void apply(IBatchAction<T> action) throws Exception;
	}

	@Override
	public void writeXlsxMCPD(AccessionFilter filter, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		writeXlsxMCPD((action) -> {
			try {
				var pageNumber = 0;
				var batchSize = 100;
				do {
					// Make sure to increment the requested page number!
					var page = PageRequest.of(pageNumber++, batchSize, Sort.by("id"));
					var accessionsMCPD = accessionService.listMCPD(filter, page);
					action.apply(accessionsMCPD.getContent());
					if (accessionsMCPD.isLast() || accessionsMCPD.isEmpty()) {
						break;
					}
				} while (pageNumber < 1000); // at most 1000 loops
				log.warn("Done streaming MCPD rows");
			} catch (Exception e) {
				log.warn("Error generating: {}", e.getMessage());
			}
		}, outputStream, shortFilter, dataSource);

	}

	/**
	 * @param generator row data generator function
	 * @param outputStream
	 * @param shortFilter
	 * @param dataSource
	 * @throws IOException
	 */
	private void writeXlsxMCPD(IDownloadAction<AccessionMCPD> generator, OutputStream outputStream, String shortFilter, String dataSource) throws IOException {
		XSSFWorkbook template = new XSSFWorkbook(this.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());

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

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

			generator.apply((accessions) -> {
				for (AccessionMCPD accessionMCPD : accessions) {
					writer.processMCPD(sheet, wbStyles, accessionMCPD);
				}
				return accessions;
			});
		});

		// ((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 AccessionMCPD accessionMCPD) {
			Row row = sheet.createRow(++i);
			writeMCPDRow(row, wbStyles, accessionMCPD);

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

	}

	private void writeMCPDRow(final Row row, WorkbookStyles wbStyles, final AccessionMCPD accessionMCPD) {

		// Process and write result
		row.createCell(COL_INSTCODE).setCellValue(accessionMCPD.instCode);
		row.createCell(COL_ACCENUMB).setCellValue(accessionMCPD.acceNumb);

		updateCell(row, COL_CURATIONTYPE, accessionMCPD.curationType);
		updateCell(row, COL_COLLNUMB, accessionMCPD.collNumb);
		updateCell(row, COL_COLLCODE, accessionMCPD.collCode);
		updateCell(row, COL_COLLNAME, accessionMCPD.collName);
		updateCell(row, COL_COLLINSTADDR, accessionMCPD.collInstAddress);
		updateCell(row, COL_COLLMISSID, accessionMCPD.collMissid);
		updateCell(row, COL_COLLSITE, accessionMCPD.collSite);
		updateCell(row, COL_COLLDATE, accessionMCPD.collDate);

		updateCell(row, COL_BREDCODE, accessionMCPD.bredCode);
		updateCell(row, COL_BREDNAME, accessionMCPD.bredName);
		updateCell(row, COL_ANCEST, accessionMCPD.ancest);

		updateCell(row, COL_GENUS, accessionMCPD.genus);
		updateCell(row, COL_SPECIES, accessionMCPD.species);
		updateCell(row, COL_SPAUTHOR, accessionMCPD.spAuthor);
		updateCell(row, COL_SUBTAXA, accessionMCPD.subtaxa);
		updateCell(row, COL_SUBTAUTHOR, accessionMCPD.subtAuthor);

		updateCell(row, COL_CROPNAME, accessionMCPD.cropName);
		updateCell(row, COL_ACQDATE, accessionMCPD.acqDate);

		updateCell(row, COL_ORIGCTY, accessionMCPD.origCty);

		updateCell(row, COL_DECLATITUDE, accessionMCPD.decLatitude);
		updateCell(row, COL_DECLONGITUDE, accessionMCPD.decLongitude);
		updateCell(row, COL_COORDUNCERT, accessionMCPD.coordUncert);
		updateCell(row, COL_COORDDATUM, accessionMCPD.coordDatum);
		updateCell(row, COL_GEOREFMETH, accessionMCPD.geoRefMeth);
		updateCell(row, COL_ELEVATION, accessionMCPD.elevation);

		updateCell(row, COL_SAMPSTAT, accessionMCPD.sampStat);

		if (accessionMCPD.collSrc != null) {
			Cell c = row.createCell(COL_COLLSRC);
			c.setCellValue(accessionMCPD.collSrc);
		}

		updateCell(row, COL_DUPLSITE, accessionMCPD.duplSite);
		updateCell(row, COL_DUPLINSTNAME, accessionMCPD.duplInstName);

		updateCell(row, COL_STORAGE, accessionMCPD.storage);
		updateCell(row, COL_MLSSTAT, accessionMCPD.mlsStat);

		updateCell(row, COL_ACCENAME, accessionMCPD.acceName);
		updateCell(row, COL_OTHERNUMB, accessionMCPD.otherNumb);

		updateCell(row, COL_DONORCODE, accessionMCPD.donorCode);
		updateCell(row, COL_DONORNAME, accessionMCPD.donorName);
		updateCell(row, COL_DONORNUMB, accessionMCPD.donorNumb);

//		updateCell(row, COL_UUID, accessionMCPD.id);

		updateCell(row, COL_HISTORICAL, accessionMCPD.historical);

		updateCell(row, COL_ACCEURL, accessionMCPD.acceUrl);

		updateCell(row, COL_REMARKS, accessionMCPD.remarks);

		updateCell(row, COL_DOI, accessionMCPD.puid);

		updateCell(row, COL_LASTMODIFIED, accessionMCPD.lastModified, wbStyles.dateStyle);
		updateCell(row, COL_DATAPROVIDERID, accessionMCPD.id);
	}

}