<commit_before>/*******************************************************************************
 *  Copyright © 2013 - 2015 EMBL - European Bioinformatics Institute
 *
 *  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 uk.ac.ebi.phenotype.web.controller;

import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocument;
import org.apache.solr.common.SolrDocumentList;
import org.hibernate.HibernateException;
import org.mousephenotype.cda.db.dao.*;
import org.mousephenotype.cda.db.pojo.*;
import org.mousephenotype.cda.db.pojo.ReferenceDTO;
import org.mousephenotype.cda.enumerations.SexType;
import org.mousephenotype.cda.solr.generic.util.PhenotypeCallSummarySolr;
import org.mousephenotype.cda.solr.generic.util.PhenotypeFacetResult;
import org.mousephenotype.cda.solr.generic.util.Tools;
import org.mousephenotype.cda.solr.service.ExperimentService;
import org.mousephenotype.cda.solr.service.GeneService;
import org.mousephenotype.cda.solr.service.MpService;
import org.mousephenotype.cda.solr.service.SolrIndex;
import org.mousephenotype.cda.solr.service.SolrIndex.AnnotNameValCount;
import org.mousephenotype.cda.solr.service.dto.*;
import org.mousephenotype.cda.solr.web.dto.DataTableRow;
import org.mousephenotype.cda.solr.web.dto.GenePageTableRow;
import org.mousephenotype.cda.solr.web.dto.PhenotypePageTableRow;
import org.mousephenotype.cda.solr.web.dto.SimpleOntoTerm;
import org.mousephenotype.cda.utilities.CommonUtils;
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.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import uk.ac.ebi.generic.util.ExcelWorkBook;
import uk.ac.sanger.phenodigm2.dao.PhenoDigmWebDao;
import uk.ac.sanger.phenodigm2.model.GeneIdentifier;
import uk.ac.sanger.phenodigm2.web.AssociationSummary;
import uk.ac.sanger.phenodigm2.web.DiseaseAssociationSummary;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Controller
public class FileExportController {

	protected CommonUtils commonUtils = new CommonUtils();

	private final Logger log = LoggerFactory.getLogger(this.getClass().getCanonicalName());

	@Autowired
	public PhenotypeCallSummaryDAO phenotypeCallSummaryDAO;

	@Autowired
	private SolrIndex solrIndex;

	@Autowired
	private GeneService geneService;

	@Autowired
    @Qualifier("phenotypePipelineDAOImpl")
	private PhenotypePipelineDAO ppDAO;

	@Resource(name = "globalConfiguration")
	private Map<String, String> config;

	@Autowired
	private ExperimentService experimentService;

	@Autowired
	OrganisationDAO organisationDao;

	@Autowired
	StrainDAO strainDAO;

	@Autowired
	AlleleDAO alleleDAO;

	@Autowired
	private MpService mpService;

	@Autowired
	private PhenotypeCallSummarySolr phenoDAO;

	private String NO_INFO_MSG = "No information available";

	private String hostName;

	@Autowired
	@Qualifier("admintoolsDataSource")
	private DataSource admintoolsDataSource;

	@Autowired
	private ReferenceDAO referenceDAO;

	@Autowired
	private GwasDAO gwasDao;

	@Autowired
	private GenomicFeatureDAO genesDao;

	@Autowired
	private PhenoDigmWebDao phenoDigmDao;
	private final double rawScoreCutoff = 1.97;

	/**
	 * Return a TSV formatted response which contains all datapoints
	 *
	 * @param phenotypingCenterParameter
	 * @param pipelineStableId
	 * @param procedureStableId
	 * @param parameterStableId
	 * @param alleleAccession
	 * @param sexesParameter
	 * @param zygositiesParameter
	 * @param strainParameter
	 * @return
	 * @throws SolrServerException
	 * @throws IOException
	 * @throws URISyntaxException
	 */
	@ResponseBody
	@RequestMapping(value = "/exportraw", method = RequestMethod.GET)
	public String getExperimentalData(
			@RequestParam(value = "phenotyping_center", required = true) String phenotypingCenterParameter,
			@RequestParam(value = "pipeline_stable_id", required = true) String pipelineStableId,
			@RequestParam(value = "procedure_stable_id", required = true) String procedureStableId,
			@RequestParam(value = "parameter_stable_id", required = true) String parameterStableId,
			@RequestParam(value = "allele_accession", required = true) String alleleAccession,
			@RequestParam(value = "sex", required = false) String[] sexesParameter,
			@RequestParam(value = "zygosity", required = false) String[] zygositiesParameter,
			@RequestParam(value = "strain", required = false) String strainParameter)
					throws SolrServerException, IOException, URISyntaxException {

		Organisation phenotypingCenter = organisationDao.getOrganisationByName(phenotypingCenterParameter);
		Pipeline pipeline = ppDAO.getPhenotypePipelineByStableId(pipelineStableId);
		Parameter parameter = ppDAO.getParameterByStableId(parameterStableId);
		Allele allele = alleleDAO.getAlleleByAccession(alleleAccession);
		SexType sex = (sexesParameter != null && sexesParameter.length > 1) ? SexType.valueOf(sexesParameter[0]) : null;
		List<String> zygosities = (zygositiesParameter == null) ? null : Arrays.asList(zygositiesParameter);
		String center = phenotypingCenter.getName();
		Integer centerId = phenotypingCenter.getId();
		String geneAcc = allele.getGene().getId().getAccession();
		String alleleAcc = allele.getId().getAccession();

		String strainAccession = null;
		if (strainParameter != null) {
			Strain s = strainDAO.getStrainByName(strainParameter);
			if (s != null) {
				strainAccession = s.getId().getAccession();
			}
		}

		List<ExperimentDTO> experiments = experimentService.getExperimentDTO(parameter.getId(), pipeline.getId(),
				geneAcc, sex, centerId, zygosities, strainAccession, null, Boolean.FALSE, alleleAcc);

		List<String> rows = new ArrayList<>();
		rows.add(StringUtils.join(new String[] { "Experiment", "Center", "Pipeline", "Procedure", "Parameter", "Strain",
				"Colony", "Gene", "Allele", "MetadataGroup", "Zygosity", "Sex", "AssayDate", "Value", "Metadata" },
				", "));

		Integer i = 1;

		for (ExperimentDTO experiment : experiments) {

			// Adding all data points to the export
			Set<ObservationDTO> observations = experiment.getControls();
			observations.addAll(experiment.getMutants());

			for (ObservationDTO observation : observations) {
				List<String> row = new ArrayList<>();
				row.add("Exp" + i.toString());
				row.add(center);
				row.add(pipelineStableId);
				row.add(procedureStableId);
				row.add(parameterStableId);
				row.add(observation.getStrain());
				row.add((observation.getGroup().equals("control")) ? "+/+" : observation.getColonyId());
				row.add((observation.getGroup().equals("control")) ? "\"\"" : geneAcc);
				row.add((observation.getGroup().equals("control")) ? "\"\"" : alleleAcc);
				row.add((observation.getMetadataGroup() != null && !observation.getMetadataGroup().isEmpty())
						? observation.getMetadataGroup() : "\"\"");
				row.add((observation.getZygosity() != null && !observation.getZygosity().isEmpty())
						? observation.getZygosity() : "\"\"");
				row.add(observation.getSex());
				row.add(observation.getDateOfExperimentString());

				String dataValue = observation.getCategory();
				if (dataValue == null) {
					dataValue = observation.getDataPoint().toString();
				}

				row.add(dataValue);
				row.add("\"" + StringUtils.join(observation.getMetadata(), "::") + "\"");

				rows.add(StringUtils.join(row, ", "));
			}

			// Next experiment
			i++;
		}

		return StringUtils.join(rows, "\n");
	}

	/**
	 * <p>
	 * Export table as TSV or Excel file.
	 * </p>
	 *
	 * @param model
	 * @return
	 */
	@RequestMapping(value = "/export", method = RequestMethod.GET)
	public void exportTableAsExcelTsv(
			/*
			 * *****************************************************************
			 * *** Please keep in mind that /export is used for ALL exports on
			 * the website so be cautious about required parameters
			 *******************************************************************/
			@RequestParam(value = "externalDbId", required = true) Integer extDbId,
			@RequestParam(value = "fileType", required = true) String fileType,
			@RequestParam(value = "fileName", required = true) String fileName,
			@RequestParam(value = "legacyOnly", required = false, defaultValue = "false") Boolean legacyOnly,
			@RequestParam(value = "allele_accession", required = false) String[] allele,
			@RequestParam(value = "rowStart", required = false) Integer rowStart,
			@RequestParam(value = "length", required = false) Integer length,
			@RequestParam(value = "panel", required = false) String panelName,
			@RequestParam(value = "mpId", required = false) String mpId,
			@RequestParam(value = "mpTerm", required = false) String mpTerm,
			@RequestParam(value = "mgiGeneId", required = false) String[] mgiGeneId,
			@RequestParam(value = "parameterStableId", required = false) String[] parameterStableId, // should
																										// be
																										// filled
																										// for
																										// graph
																										// data
																										// export
			@RequestParam(value = "zygosity", required = false) String[] zygosities, // should
																						// be
																						// filled
																						// for
																						// graph
																						// data
																						// export
			@RequestParam(value = "strains", required = false) String[] strains, // should
																					// be
																					// filled
																					// for
																					// graph
																					// data
																					// export
			@RequestParam(value = "geneSymbol", required = false) String geneSymbol,
			@RequestParam(value = "solrCoreName", required = false) String solrCoreName,
			@RequestParam(value = "params", required = false) String solrFilters,
			@RequestParam(value = "gridFields", required = false) String gridFields,
			@RequestParam(value = "showImgView", required = false, defaultValue = "false") Boolean showImgView,
			@RequestParam(value = "dumpMode", required = false) String dumpMode,
			@RequestParam(value = "baseUrl", required = false) String baseUrl,
			@RequestParam(value = "sex", required = false) String sex,
			@RequestParam(value = "phenotypingCenter", required = false) String[] phenotypingCenter,
			@RequestParam(value = "pipelineStableId", required = false) String[] pipelineStableId,
			@RequestParam(value = "dogoterm", required = false, defaultValue = "false") Boolean dogoterm,
			@RequestParam(value = "gocollapse", required = false, defaultValue = "false") Boolean gocollapse,
			@RequestParam(value = "gene2pfam", required = false, defaultValue = "false") Boolean gene2pfam,
			@RequestParam(value = "doAlleleRef", required = false, defaultValue = "false") Boolean doAlleleRef,
			@RequestParam(value = "filterStr", required = false) String filterStr, HttpSession session,
			HttpServletRequest request, HttpServletResponse response, Model model) throws Exception {

		hostName = request.getAttribute("mappedHostname").toString().replace("https:", "http:");
		System.out.println("------------\nEXPORT \n---------");

		String query = "*:*"; // default
		String fqStr = null;

		log.debug("solr params: " + solrFilters);

		String[] pairs = solrFilters.split("&");
		for (String pair : pairs) {
			try {
				String[] parts = pair.split("=");
				if (parts[0].equals("q")) {
					query = parts[1];
				} else if (parts[0].equals("fq")) {
					fqStr = parts[1];
				}
			} catch (Exception e) {
				log.error("Error getting value of q");
				e.printStackTrace();
			}
		}

		Workbook wb = null;
		List<String> dataRows = new ArrayList();
		// Default to exporting 10 rows
		length = length != null ? length : 10;

		panelName = panelName == null ? "" : panelName;

		if (!solrCoreName.isEmpty()) {

			if (dumpMode.equals("all")) {
				rowStart = 0;
				// length = parseMaxRow(solrParams); // this is the facetCount
				length = 10000000;
			}

			if (solrCoreName.equalsIgnoreCase("experiment")) {
				ArrayList<Integer> phenotypingCenterIds = new ArrayList<Integer>();
				try {
					for (int i = 0; i < phenotypingCenter.length; i++) {
						phenotypingCenterIds.add(organisationDao
								.getOrganisationByName(phenotypingCenter[i].replaceAll("%20", " ")).getId());
					}
				} catch (NullPointerException e) {
					log.error("Cannot find organisation ID for org with name " + phenotypingCenter);
				}
				List<String> zygList = null;
				if (zygosities != null) {
					zygList = Arrays.asList(zygosities);
				}
				String s = (sex.equalsIgnoreCase("null")) ? null : sex;
				dataRows = composeExperimentDataExportRows(parameterStableId, mgiGeneId, allele, s,
						phenotypingCenterIds, zygList, strains, pipelineStableId);
			} else if (solrCoreName.equalsIgnoreCase("genotype-phenotype")) {
				if (mgiGeneId != null) {
					dataRows = composeDataRowGeneOrPhenPage(mgiGeneId[0], request.getParameter("page"), solrFilters,
							request);
				} else if (mpId != null) {
					dataRows = composeDataRowGeneOrPhenPage(mpId, request.getParameter("page"), solrFilters, request);
				}
			} else if (dogoterm) {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeGene2GoAnnotationDataRows(json, request, dogoterm, gocollapse);
			} else if (gene2pfam) {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeGene2PfamClansDataRows(json, request);
			} else if (doAlleleRef) {
				dataRows = composeAlleleRefExportRows(length, rowStart, filterStr, dumpMode);
			} else {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeDataTableExportRows(query, solrCoreName, json, rowStart, length, showImgView,
						solrFilters, request, legacyOnly, fqStr);
			}
		}

		writeOutputFile(response, dataRows, fileType, fileName, wb);

	}

	private int parseMaxRow(String solrParams) {
		String[] paramsList = solrParams.split("&");
		int facetCount = 0;
		for (String str : paramsList) {
			if (str.startsWith("facetCount=")) {
				String[] vals = str.split("=");
				facetCount = Integer.parseInt(vals[1]);
			}
		}
		return facetCount;
	}

	public List<String> composeExperimentDataExportRows(String[] parameterStableId, String[] geneAccession,
			String allele[], String gender, ArrayList<Integer> phenotypingCenterIds, List<String> zygosity,
			String[] strain, String[] pipelines)
					throws SolrServerException, IOException, URISyntaxException, SQLException {

		List<String> rows = new ArrayList();
		SexType sex = null;
		if (gender != null) {
			sex = SexType.valueOf(gender);
		}
		if (phenotypingCenterIds == null) {
			throw new RuntimeException("ERROR: phenotypingCenterIds is null. Expected non-null value.");
		}
		if (phenotypingCenterIds.isEmpty()) {
			phenotypingCenterIds.add(null);
		}

		if (strain == null || strain.length == 0) {
			strain = new String[1];
			strain[0] = null;
		}
		if (allele == null || allele.length == 0) {
			allele = new String[1];
			allele[0] = null;
		}
		ArrayList<Integer> pipelineIds = new ArrayList<>();
		if (pipelines != null) {
			for (String pipe : pipelines) {
				pipelineIds.add(ppDAO.getPhenotypePipelineByStableId(pipe).getId());
			}
		}
		if (pipelineIds.isEmpty()) {
			pipelineIds.add(null);
		}

		List<ExperimentDTO> experimentList;
		for (int k = 0; k < parameterStableId.length; k++) {
			for (int mgiI = 0; mgiI < geneAccession.length; mgiI++) {
				for (Integer pCenter : phenotypingCenterIds) {
					for (Integer pipelineId : pipelineIds) {
						for (int strainI = 0; strainI < strain.length; strainI++) {
							for (int alleleI = 0; alleleI < allele.length; alleleI++) {
								experimentList = experimentService.getExperimentDTO(parameterStableId[k], pipelineId,
										geneAccession[mgiI], sex, pCenter, zygosity, strain[strainI]);
								if (experimentList.size() > 0) {
									for (ExperimentDTO experiment : experimentList) {
										rows.addAll(experiment.getTabbedToString(ppDAO));
									}
								}
							}
						}
					}
				}
			}
		}
		return rows;
	}

	public List<String> composeDataTableExportRows(String query, String solrCoreName, JSONObject json,
			Integer iDisplayStart, Integer iDisplayLength, boolean showImgView, String solrParams,
			HttpServletRequest request, boolean legacyOnly, String fqStr) throws IOException, URISyntaxException {
		List<String> rows = null;

		if (solrCoreName.equals("gene")) {
			rows = composeGeneDataTableRows(json, request, legacyOnly);
		} else if (solrCoreName.equals("mp")) {
			rows = composeMpDataTableRows(json, request);
		} else if (solrCoreName.equals("ma")) {
			rows = composeMaDataTableRows(json, request);
		} else if (solrCoreName.equals("pipeline")) {
			rows = composeProtocolDataTableRows(json, request);
		} else if (solrCoreName.equals("images")) {
			rows = composeImageDataTableRows(query, json, iDisplayStart, iDisplayLength, showImgView, solrParams,
					request);
		} else if (solrCoreName.equals("impc_images")) {
			rows = composeImpcImageDataTableRows(query, json, iDisplayStart, iDisplayLength, showImgView, fqStr,
					request);
		} else if (solrCoreName.equals("disease")) {
			rows = composeDiseaseDataTableRows(json, request);
		}

		return rows;
	}

	private List<String> composeProtocolDataTableRows(JSONObject json, HttpServletRequest request) {
		JSONArray docs = json.getJSONObject("response").getJSONArray("docs");

		String impressBaseUrl = request.getAttribute("drupalBaseUrl").toString().replace("https", "http")
				+ "/impress/impress/displaySOP/";
		// String impressBaseUrl = request.getAttribute("drupalBaseUrl") +
		// "/impress/impress/displaySOP/";

		List<String> rowData = new ArrayList();
		rowData.add("Parameter\tProcedure\tProcedure Impress link\tPipeline"); // column
																				// names

		for (int i = 0; i < docs.size(); i++) {
			List<String> data = new ArrayList();
			JSONObject doc = docs.getJSONObject(i);
			data.add(doc.getString("parameter_name"));

			JSONArray procedures = doc.getJSONArray("procedure_name");
			JSONArray procedure_stable_keys = doc.getJSONArray("procedure_stable_key");

			List<String> procedureLinks = new ArrayList<String>();
			for (int p = 0; p < procedures.size(); p++) {
				// String procedure = procedures.get(p).toString();
				String procedure_stable_key = procedure_stable_keys.get(p).toString();
				procedureLinks.add(impressBaseUrl + procedure_stable_key);
			}

			// String procedure = doc.getString("procedure_name");
			// data.add(procedure);
			data.add(StringUtils.join(procedures, "|"));

			// String procedure_stable_key =
			// doc.getString("procedure_stable_key");
			// String procedureLink = impressBaseUrl + procedure_stable_key;
			// data.add(procedureLink);
			data.add(StringUtils.join(procedureLinks, "|"));

			data.add(doc.getString("pipeline_name"));
			rowData.add(StringUtils.join(data, "\t"));
		}
		return rowData;
	}

	private List<String> composeImageDataTableRows(String query, JSONObject json, Integer iDisplayStart,
			Integer iDisplayLength, boolean showImgView, String solrParams, HttpServletRequest request) {
		// System.out.println("query: "+ query + " -- "+ solrParams);

		String mediaBaseUrl = config.get("mediaBaseUrl").replace("https:", "http:");

		List<String> rowData = new ArrayList();

		String mpBaseUrl = request.getAttribute("baseUrl") + "/phenotypes/";
		String maBaseUrl = request.getAttribute("baseUrl") + "/anatomy/";
		String geneBaseUrl = request.getAttribute("baseUrl") + "/genes/";

		if (showImgView) {

			JSONArray docs = json.getJSONObject("response").getJSONArray("docs");
			rowData.add("Annotation term\tAnnotation id\tAnnotation id link\tImage link"); // column
																							// names

			for (int i = 0; i < docs.size(); i++) {

				JSONObject doc = docs.getJSONObject(i);

				List<String> data = new ArrayList();
				List<String> lists = new ArrayList();
				List<String> termLists = new ArrayList();
				List<String> link_lists = new ArrayList();

				String[] fields = { "annotationTermId", "expName", "symbol_gene" };
				for (String fld : fields) {
					if (doc.has(fld)) {

						JSONArray list = doc.getJSONArray(fld);

						if (fld.equals("annotationTermId")) {

							JSONArray termList = doc.containsKey("annotationTermName")
									? doc.getJSONArray("annotationTermName") : new JSONArray();

							for (int l = 0; l < list.size(); l++) {
								String value = list.getString(l);
								String termVal = termList.size() == 0 ? NO_INFO_MSG : termList.getString(l);

								if (value.startsWith("MP:")) {
									link_lists.add(hostName + mpBaseUrl + value);
									termLists.add("MP:" + termVal);
								}
								if (value.startsWith("MA:")) {
									link_lists.add(hostName + maBaseUrl + value);
									termLists.add("MA:" + termVal);
								}

								lists.add(value); // id
							}
						} else if (fld.equals("symbol_gene")) {
							// gene symbol and its link
							for (int l = 0; l < list.size(); l++) {
								String[] parts = list.getString(l).split("_");
								String symbol = parts[0];
								String mgiId = parts[1];
								termLists.add("Gene:" + symbol);
								lists.add(mgiId);
								link_lists.add(hostName + geneBaseUrl + mgiId);
							}
						} else if (fld.equals("expName")) {

							for (int l = 0; l < list.size(); l++) {
								String value = list.getString(l);

								termLists.add("Procedure:" + value);
								lists.add(NO_INFO_MSG);
								link_lists.add(NO_INFO_MSG);
							}
						}
					}
				}

				data.add(termLists.size() == 0 ? NO_INFO_MSG : StringUtils.join(termLists, "|")); // term
																									// names
				data.add(StringUtils.join(lists, "|"));
				data.add(StringUtils.join(link_lists, "|"));

				data.add(mediaBaseUrl + "/" + doc.getString("largeThumbnailFilePath"));
				rowData.add(StringUtils.join(data, "\t"));
			}
		} else {
			// System.out.println("MODE: annotview " + showImgView);
			// annotation view
			// annotation view: images group by annotationTerm per row
			rowData.add(
					"Annotation type\tAnnotation term\tAnnotation id\tAnnotation id link\tRelated image count\tImages link"); // column
																																// names
			JSONObject facetFields = json.getJSONObject("facet_counts").getJSONObject("facet_fields");

			JSONArray sumFacets = solrIndex.mergeFacets(facetFields);

			int numFacets = sumFacets.size();
			int quotient = (numFacets / 2) / iDisplayLength - ((numFacets / 2) % iDisplayLength) / iDisplayLength;
			int remainder = (numFacets / 2) % iDisplayLength;
			int start = iDisplayStart * 2; // 2 elements(name, count), hence
											// multiply by 2
			int end = iDisplayStart == quotient * iDisplayLength ? (iDisplayStart + remainder) * 2
					: (iDisplayStart + iDisplayLength) * 2;

			for (int i = start; i < end; i = i + 2) {
				List<String> data = new ArrayList();
				// array element is an alternate of facetField and facetCount

				String[] names = sumFacets.get(i).toString().split("_");
				if (names.length == 2) { // only want facet value of xxx_yyy
					String annotName = names[0];

					Map<String, String> hm = solrIndex.renderFacetField(names, request.getAttribute("mappedHostname").toString(), request.getAttribute("baseUrl").toString());

					data.add(hm.get("label"));
					data.add(annotName);
					data.add(hm.get("id"));
					// System.out.println("annotname: "+ annotName);
					if (hm.get("fullLink") != null) {
						data.add(hm.get("fullLink").toString());
					} else {
						data.add(NO_INFO_MSG);
					}

					String imgCount = sumFacets.get(i + 1).toString();
					data.add(imgCount);

					String facetField = hm.get("field");

					solrParams = solrParams.replaceAll("&q=.+&",
							"&q=" + query + " AND " + facetField + ":\"" + names[0] + "\"&");
					String imgSubSetLink = hostName + request.getAttribute("baseUrl") + "/imagesb?" + solrParams;
					
					data.add(imgSubSetLink);
					rowData.add(StringUtils.join(data, "\t"));
				}
			}
		}

		return rowData;
	}

	private List<String> composeImpcImageDataTableRows(String query, JSONObject json, Integer iDisplayStart,
			Integer iDisplayLength, boolean showImgView, String fqStrOri, HttpServletRequest request)
					throws IOException, URISyntaxException {

		// currently just use the solr field value
		// String mediaBaseUrl =
		// config.get("impcMediaBaseUrl").replace("https:", "http:");
		List<String> rowData = new ArrayList();

		String baseUrl = request.getAttribute("baseUrl").toString();
		String mpBaseUrl = baseUrl + "/phenotypes/";
		String maBaseUrl = baseUrl + "/anatomy/";
		String geneBaseUrl = baseUrl + "/genes/";

		if (showImgView) {

			JSONArray docs = json.getJSONObject("response").getJSONArray("docs");
			// rowData.add("Annotation term\tAnnotation id\tAnnotation id
			// link\tProcedure\tGene symbol\tGene symbol link\tImage link"); //
			// column names
			rowData.add("Procedure\tGene symbol\tGene symbol link\tMA term\tMA term link\tImage link"); // column
																										// names

			for (int i = 0; i < docs.size(); i++) {
				List<String> data = new ArrayList();
				JSONObject doc = docs.getJSONObject(i);

				// String[] fields = {"annotationTermName", "annotationTermId",
				// "expName", "symbol_gene"};
				String[] fields = { "procedure_name", "gene_symbol", "ma_term" };
				for (String fld : fields) {
					if (doc.has(fld)) {
						List<String> lists = new ArrayList();

						if (fld.equals("gene_symbol")) {

							data.add(doc.getString("gene_symbol"));
							data.add(hostName + geneBaseUrl + doc.getString("gene_accession_id"));

						} else if (fld.equals("procedure_name")) {
							data.add(doc.getString("procedure_name"));
						} else if (fld.equals("ma_term")) {
							JSONArray maTerms = doc.getJSONArray("ma_term");
							JSONArray maIds = doc.getJSONArray("ma_id");
							List<String> ma_Terms = new ArrayList<>();
							List<String> ma_links = new ArrayList<>();
							for (int m = 0; m < maTerms.size(); m++) {
								ma_Terms.add(maTerms.get(m).toString());
								ma_links.add(hostName + maBaseUrl + maIds.get(m).toString());
							}

							data.add(StringUtils.join(ma_Terms, "|"));
							data.add(StringUtils.join(ma_links, "|"));

						}
					} else {
						/*
						 * if ( fld.equals("annotationTermId") ){
						 * data.add(NO_INFO_MSG); data.add(NO_INFO_MSG);
						 * data.add(NO_INFO_MSG); } else if (
						 * fld.equals("symbol_gene") ){
						 */
						if (fld.equals("gene_symbol")) {
							data.add(NO_INFO_MSG);
							data.add(NO_INFO_MSG);
						} else if (fld.equals("procedure_name")) {
							data.add(NO_INFO_MSG);
						} else if (fld.equals("ma_term")) {
							data.add(NO_INFO_MSG);
							data.add(NO_INFO_MSG);
						}
					}
				}

				data.add(doc.containsKey("jpeg_url") ? doc.getString("jpeg_url") : NO_INFO_MSG);
				rowData.add(StringUtils.join(data, "\t"));
			}
		} else {

			// annotation view: images group by annotationTerm per row
			baseUrl = baseUrl.replace("https:", "http:");
			String mediaBaseUrl = "http:" + config.get("impcMediaBaseUrl");

			rowData.add(
					"Annotation type\tAnnotation term\tAnnotation id\tAnnotation id link\tRelated image count\tImages link"); // column
																																// names

			String fqStr = query;

			String defaultQStr = "observation_type:image_record&qf=auto_suggest&defType=edismax";

			if (query != "") {
				defaultQStr = "q=" + query + " AND " + defaultQStr;
			} else {
				defaultQStr = "q=" + defaultQStr;
			}

			String defaultFqStr = "fq=(biological_sample_group:experimental)";

			JSONObject facetFields = json.getJSONObject("facet_counts").getJSONObject("facet_fields");

			List<AnnotNameValCount> annots = solrIndex.mergeImpcFacets(json, baseUrl);

			int numFacets = annots.size();
			int start = iDisplayStart; // 2 elements(name, count), hence
										// multiply by 2
			int end = iDisplayStart + iDisplayLength;
			end = end > numFacets ? numFacets : end;

			for (int i = start; i < end; i++) {

				List<String> data = new ArrayList();

				AnnotNameValCount annot = annots.get(i);

				String displayAnnotName = annot.name;
				data.add(displayAnnotName);

				String annotVal = annot.val;
				data.add(annotVal);

				if (annot.id != null) {
					data.add(annot.id);
					data.add(annot.link);
				} else {
					data.add(NO_INFO_MSG);
					data.add(NO_INFO_MSG);
				}

				String thisFqStr = defaultFqStr + " AND " + annot.facet + ":\"" + annotVal + "\"";

				List pathAndImgCount = solrIndex.fetchImpcImagePathByAnnotName(query, thisFqStr);

				int imgCount = (int) pathAndImgCount.get(1);

				StringBuilder sb = new StringBuilder();
				sb.append("");
				sb.append(imgCount);
				data.add(sb.toString());

				String imgSubSetLink = mediaBaseUrl + "?" + thisFqStr;

				data.add(imgSubSetLink);

				rowData.add(StringUtils.join(data, "\t"));
			}
		}

		return rowData;
	}

	private List<String> composeMpDataTableRows(JSONObject json, HttpServletRequest request) {
		JSONArray docs = json.getJSONObject("response").getJSONArray("docs");

		String baseUrl = request.getAttribute("baseUrl") + "/phenotypes/";

		List<String> rowData = new ArrayList();
		rowData.add(
				"Mammalian phenotype term\tMammalian phenotype id\tMammalian phenotype id link\tMammalian phenotype definition\tMammalian phenotype synonym\tMammalian phenotype top level term\tComputationally mapped human phenotype terms\tComputationally mapped human phenotype term Ids\tPostqc call(s)"); // column
																																																																													// names

		for (int i = 0; i < docs.size(); i++) {
			List<String> data = new ArrayList();
			JSONObject doc = docs.getJSONObject(i);

			data.add(doc.getString("mp_term"));
			String mpId = doc.getString("mp_id");
			data.add(mpId);
			data.add(hostName + baseUrl + mpId);
<commit_msg>fixed path to multiple impg images<commit_after>/*******************************************************************************
 *  Copyright © 2013 - 2015 EMBL - European Bioinformatics Institute
 *
 *  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 uk.ac.ebi.phenotype.web.controller;

import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocument;
import org.apache.solr.common.SolrDocumentList;
import org.hibernate.HibernateException;
import org.mousephenotype.cda.db.dao.*;
import org.mousephenotype.cda.db.pojo.*;
import org.mousephenotype.cda.db.pojo.ReferenceDTO;
import org.mousephenotype.cda.enumerations.SexType;
import org.mousephenotype.cda.solr.generic.util.PhenotypeCallSummarySolr;
import org.mousephenotype.cda.solr.generic.util.PhenotypeFacetResult;
import org.mousephenotype.cda.solr.generic.util.Tools;
import org.mousephenotype.cda.solr.service.ExperimentService;
import org.mousephenotype.cda.solr.service.GeneService;
import org.mousephenotype.cda.solr.service.MpService;
import org.mousephenotype.cda.solr.service.SolrIndex;
import org.mousephenotype.cda.solr.service.SolrIndex.AnnotNameValCount;
import org.mousephenotype.cda.solr.service.dto.*;
import org.mousephenotype.cda.solr.web.dto.DataTableRow;
import org.mousephenotype.cda.solr.web.dto.GenePageTableRow;
import org.mousephenotype.cda.solr.web.dto.PhenotypePageTableRow;
import org.mousephenotype.cda.solr.web.dto.SimpleOntoTerm;
import org.mousephenotype.cda.utilities.CommonUtils;
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.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import uk.ac.ebi.generic.util.ExcelWorkBook;
import uk.ac.sanger.phenodigm2.dao.PhenoDigmWebDao;
import uk.ac.sanger.phenodigm2.model.GeneIdentifier;
import uk.ac.sanger.phenodigm2.web.AssociationSummary;
import uk.ac.sanger.phenodigm2.web.DiseaseAssociationSummary;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Controller
public class FileExportController {

	protected CommonUtils commonUtils = new CommonUtils();

	private final Logger log = LoggerFactory.getLogger(this.getClass().getCanonicalName());

	@Autowired
	public PhenotypeCallSummaryDAO phenotypeCallSummaryDAO;

	@Autowired
	private SolrIndex solrIndex;

	@Autowired
	private GeneService geneService;

	@Autowired
    @Qualifier("phenotypePipelineDAOImpl")
	private PhenotypePipelineDAO ppDAO;

	@Resource(name = "globalConfiguration")
	private Map<String, String> config;

	@Autowired
	private ExperimentService experimentService;

	@Autowired
	OrganisationDAO organisationDao;

	@Autowired
	StrainDAO strainDAO;

	@Autowired
	AlleleDAO alleleDAO;

	@Autowired
	private MpService mpService;

	@Autowired
	private PhenotypeCallSummarySolr phenoDAO;

	private String NO_INFO_MSG = "No information available";

	private String hostName;

	@Autowired
	@Qualifier("admintoolsDataSource")
	private DataSource admintoolsDataSource;

	@Autowired
	private ReferenceDAO referenceDAO;

	@Autowired
	private GwasDAO gwasDao;

	@Autowired
	private GenomicFeatureDAO genesDao;

	@Autowired
	private PhenoDigmWebDao phenoDigmDao;
	private final double rawScoreCutoff = 1.97;

	/**
	 * Return a TSV formatted response which contains all datapoints
	 *
	 * @param phenotypingCenterParameter
	 * @param pipelineStableId
	 * @param procedureStableId
	 * @param parameterStableId
	 * @param alleleAccession
	 * @param sexesParameter
	 * @param zygositiesParameter
	 * @param strainParameter
	 * @return
	 * @throws SolrServerException
	 * @throws IOException
	 * @throws URISyntaxException
	 */
	@ResponseBody
	@RequestMapping(value = "/exportraw", method = RequestMethod.GET)
	public String getExperimentalData(
			@RequestParam(value = "phenotyping_center", required = true) String phenotypingCenterParameter,
			@RequestParam(value = "pipeline_stable_id", required = true) String pipelineStableId,
			@RequestParam(value = "procedure_stable_id", required = true) String procedureStableId,
			@RequestParam(value = "parameter_stable_id", required = true) String parameterStableId,
			@RequestParam(value = "allele_accession", required = true) String alleleAccession,
			@RequestParam(value = "sex", required = false) String[] sexesParameter,
			@RequestParam(value = "zygosity", required = false) String[] zygositiesParameter,
			@RequestParam(value = "strain", required = false) String strainParameter)
					throws SolrServerException, IOException, URISyntaxException {

		Organisation phenotypingCenter = organisationDao.getOrganisationByName(phenotypingCenterParameter);
		Pipeline pipeline = ppDAO.getPhenotypePipelineByStableId(pipelineStableId);
		Parameter parameter = ppDAO.getParameterByStableId(parameterStableId);
		Allele allele = alleleDAO.getAlleleByAccession(alleleAccession);
		SexType sex = (sexesParameter != null && sexesParameter.length > 1) ? SexType.valueOf(sexesParameter[0]) : null;
		List<String> zygosities = (zygositiesParameter == null) ? null : Arrays.asList(zygositiesParameter);
		String center = phenotypingCenter.getName();
		Integer centerId = phenotypingCenter.getId();
		String geneAcc = allele.getGene().getId().getAccession();
		String alleleAcc = allele.getId().getAccession();

		String strainAccession = null;
		if (strainParameter != null) {
			Strain s = strainDAO.getStrainByName(strainParameter);
			if (s != null) {
				strainAccession = s.getId().getAccession();
			}
		}

		List<ExperimentDTO> experiments = experimentService.getExperimentDTO(parameter.getId(), pipeline.getId(),
				geneAcc, sex, centerId, zygosities, strainAccession, null, Boolean.FALSE, alleleAcc);

		List<String> rows = new ArrayList<>();
		rows.add(StringUtils.join(new String[] { "Experiment", "Center", "Pipeline", "Procedure", "Parameter", "Strain",
				"Colony", "Gene", "Allele", "MetadataGroup", "Zygosity", "Sex", "AssayDate", "Value", "Metadata" },
				", "));

		Integer i = 1;

		for (ExperimentDTO experiment : experiments) {

			// Adding all data points to the export
			Set<ObservationDTO> observations = experiment.getControls();
			observations.addAll(experiment.getMutants());

			for (ObservationDTO observation : observations) {
				List<String> row = new ArrayList<>();
				row.add("Exp" + i.toString());
				row.add(center);
				row.add(pipelineStableId);
				row.add(procedureStableId);
				row.add(parameterStableId);
				row.add(observation.getStrain());
				row.add((observation.getGroup().equals("control")) ? "+/+" : observation.getColonyId());
				row.add((observation.getGroup().equals("control")) ? "\"\"" : geneAcc);
				row.add((observation.getGroup().equals("control")) ? "\"\"" : alleleAcc);
				row.add((observation.getMetadataGroup() != null && !observation.getMetadataGroup().isEmpty())
						? observation.getMetadataGroup() : "\"\"");
				row.add((observation.getZygosity() != null && !observation.getZygosity().isEmpty())
						? observation.getZygosity() : "\"\"");
				row.add(observation.getSex());
				row.add(observation.getDateOfExperimentString());

				String dataValue = observation.getCategory();
				if (dataValue == null) {
					dataValue = observation.getDataPoint().toString();
				}

				row.add(dataValue);
				row.add("\"" + StringUtils.join(observation.getMetadata(), "::") + "\"");

				rows.add(StringUtils.join(row, ", "));
			}

			// Next experiment
			i++;
		}

		return StringUtils.join(rows, "\n");
	}

	/**
	 * <p>
	 * Export table as TSV or Excel file.
	 * </p>
	 *
	 * @param model
	 * @return
	 */
	@RequestMapping(value = "/export", method = RequestMethod.GET)
	public void exportTableAsExcelTsv(
			/*
			 * *****************************************************************
			 * *** Please keep in mind that /export is used for ALL exports on
			 * the website so be cautious about required parameters
			 *******************************************************************/
			@RequestParam(value = "externalDbId", required = true) Integer extDbId,
			@RequestParam(value = "fileType", required = true) String fileType,
			@RequestParam(value = "fileName", required = true) String fileName,
			@RequestParam(value = "legacyOnly", required = false, defaultValue = "false") Boolean legacyOnly,
			@RequestParam(value = "allele_accession", required = false) String[] allele,
			@RequestParam(value = "rowStart", required = false) Integer rowStart,
			@RequestParam(value = "length", required = false) Integer length,
			@RequestParam(value = "panel", required = false) String panelName,
			@RequestParam(value = "mpId", required = false) String mpId,
			@RequestParam(value = "mpTerm", required = false) String mpTerm,
			@RequestParam(value = "mgiGeneId", required = false) String[] mgiGeneId,
			@RequestParam(value = "parameterStableId", required = false) String[] parameterStableId, // should
																										// be
																										// filled
																										// for
																										// graph
																										// data
																										// export
			@RequestParam(value = "zygosity", required = false) String[] zygosities, // should
																						// be
																						// filled
																						// for
																						// graph
																						// data
																						// export
			@RequestParam(value = "strains", required = false) String[] strains, // should
																					// be
																					// filled
																					// for
																					// graph
																					// data
																					// export
			@RequestParam(value = "geneSymbol", required = false) String geneSymbol,
			@RequestParam(value = "solrCoreName", required = false) String solrCoreName,
			@RequestParam(value = "params", required = false) String solrFilters,
			@RequestParam(value = "gridFields", required = false) String gridFields,
			@RequestParam(value = "showImgView", required = false, defaultValue = "false") Boolean showImgView,
			@RequestParam(value = "dumpMode", required = false) String dumpMode,
			@RequestParam(value = "baseUrl", required = false) String baseUrl,
			@RequestParam(value = "sex", required = false) String sex,
			@RequestParam(value = "phenotypingCenter", required = false) String[] phenotypingCenter,
			@RequestParam(value = "pipelineStableId", required = false) String[] pipelineStableId,
			@RequestParam(value = "dogoterm", required = false, defaultValue = "false") Boolean dogoterm,
			@RequestParam(value = "gocollapse", required = false, defaultValue = "false") Boolean gocollapse,
			@RequestParam(value = "gene2pfam", required = false, defaultValue = "false") Boolean gene2pfam,
			@RequestParam(value = "doAlleleRef", required = false, defaultValue = "false") Boolean doAlleleRef,
			@RequestParam(value = "filterStr", required = false) String filterStr, HttpSession session,
			HttpServletRequest request, HttpServletResponse response, Model model) throws Exception {

		hostName = request.getAttribute("mappedHostname").toString().replace("https:", "http:");
		System.out.println("------------\nEXPORT \n---------");

		String query = "*:*"; // default
		String fqStr = null;

		log.debug("solr params: " + solrFilters);

		String[] pairs = solrFilters.split("&");
		for (String pair : pairs) {
			try {
				String[] parts = pair.split("=");
				if (parts[0].equals("q")) {
					query = parts[1];
				} else if (parts[0].equals("fq")) {
					fqStr = parts[1];
				}
			} catch (Exception e) {
				log.error("Error getting value of q");
				e.printStackTrace();
			}
		}

		Workbook wb = null;
		List<String> dataRows = new ArrayList();
		// Default to exporting 10 rows
		length = length != null ? length : 10;

		panelName = panelName == null ? "" : panelName;

		if (!solrCoreName.isEmpty()) {

			if (dumpMode.equals("all")) {
				rowStart = 0;
				// length = parseMaxRow(solrParams); // this is the facetCount
				length = 10000000;
			}

			if (solrCoreName.equalsIgnoreCase("experiment")) {
				ArrayList<Integer> phenotypingCenterIds = new ArrayList<Integer>();
				try {
					for (int i = 0; i < phenotypingCenter.length; i++) {
						phenotypingCenterIds.add(organisationDao
								.getOrganisationByName(phenotypingCenter[i].replaceAll("%20", " ")).getId());
					}
				} catch (NullPointerException e) {
					log.error("Cannot find organisation ID for org with name " + phenotypingCenter);
				}
				List<String> zygList = null;
				if (zygosities != null) {
					zygList = Arrays.asList(zygosities);
				}
				String s = (sex.equalsIgnoreCase("null")) ? null : sex;
				dataRows = composeExperimentDataExportRows(parameterStableId, mgiGeneId, allele, s,
						phenotypingCenterIds, zygList, strains, pipelineStableId);
			} else if (solrCoreName.equalsIgnoreCase("genotype-phenotype")) {
				if (mgiGeneId != null) {
					dataRows = composeDataRowGeneOrPhenPage(mgiGeneId[0], request.getParameter("page"), solrFilters,
							request);
				} else if (mpId != null) {
					dataRows = composeDataRowGeneOrPhenPage(mpId, request.getParameter("page"), solrFilters, request);
				}
			} else if (dogoterm) {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeGene2GoAnnotationDataRows(json, request, dogoterm, gocollapse);
			} else if (gene2pfam) {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeGene2PfamClansDataRows(json, request);
			} else if (doAlleleRef) {
				dataRows = composeAlleleRefExportRows(length, rowStart, filterStr, dumpMode);
			} else {
				JSONObject json = solrIndex.getDataTableExportRows(solrCoreName, solrFilters, gridFields, rowStart,
						length, showImgView);
				dataRows = composeDataTableExportRows(query, solrCoreName, json, rowStart, length, showImgView,
						solrFilters, request, legacyOnly, fqStr);
			}
		}

		writeOutputFile(response, dataRows, fileType, fileName, wb);

	}

	private int parseMaxRow(String solrParams) {
		String[] paramsList = solrParams.split("&");
		int facetCount = 0;
		for (String str : paramsList) {
			if (str.startsWith("facetCount=")) {
				String[] vals = str.split("=");
				facetCount = Integer.parseInt(vals[1]);
			}
		}
		return facetCount;
	}

	public List<String> composeExperimentDataExportRows(String[] parameterStableId, String[] geneAccession,
			String allele[], String gender, ArrayList<Integer> phenotypingCenterIds, List<String> zygosity,
			String[] strain, String[] pipelines)
					throws SolrServerException, IOException, URISyntaxException, SQLException {

		List<String> rows = new ArrayList();
		SexType sex = null;
		if (gender != null) {
			sex = SexType.valueOf(gender);
		}
		if (phenotypingCenterIds == null) {
			throw new RuntimeException("ERROR: phenotypingCenterIds is null. Expected non-null value.");
		}
		if (phenotypingCenterIds.isEmpty()) {
			phenotypingCenterIds.add(null);
		}

		if (strain == null || strain.length == 0) {
			strain = new String[1];
			strain[0] = null;
		}
		if (allele == null || allele.length == 0) {
			allele = new String[1];
			allele[0] = null;
		}
		ArrayList<Integer> pipelineIds = new ArrayList<>();
		if (pipelines != null) {
			for (String pipe : pipelines) {
				pipelineIds.add(ppDAO.getPhenotypePipelineByStableId(pipe).getId());
			}
		}
		if (pipelineIds.isEmpty()) {
			pipelineIds.add(null);
		}

		List<ExperimentDTO> experimentList;
		for (int k = 0; k < parameterStableId.length; k++) {
			for (int mgiI = 0; mgiI < geneAccession.length; mgiI++) {
				for (Integer pCenter : phenotypingCenterIds) {
					for (Integer pipelineId : pipelineIds) {
						for (int strainI = 0; strainI < strain.length; strainI++) {
							for (int alleleI = 0; alleleI < allele.length; alleleI++) {
								experimentList = experimentService.getExperimentDTO(parameterStableId[k], pipelineId,
										geneAccession[mgiI], sex, pCenter, zygosity, strain[strainI]);
								if (experimentList.size() > 0) {
									for (ExperimentDTO experiment : experimentList) {
										rows.addAll(experiment.getTabbedToString(ppDAO));
									}
								}
							}
						}
					}
				}
			}
		}
		return rows;
	}

	public List<String> composeDataTableExportRows(String query, String solrCoreName, JSONObject json,
			Integer iDisplayStart, Integer iDisplayLength, boolean showImgView, String solrParams,
			HttpServletRequest request, boolean legacyOnly, String fqStr) throws IOException, URISyntaxException {
		List<String> rows = null;

		if (solrCoreName.equals("gene")) {
			rows = composeGeneDataTableRows(json, request, legacyOnly);
		} else if (solrCoreName.equals("mp")) {
			rows = composeMpDataTableRows(json, request);
		} else if (solrCoreName.equals("ma")) {
			rows = composeMaDataTableRows(json, request);
		} else if (solrCoreName.equals("pipeline")) {
			rows = composeProtocolDataTableRows(json, request);
		} else if (solrCoreName.equals("images")) {
			rows = composeImageDataTableRows(query, json, iDisplayStart, iDisplayLength, showImgView, solrParams,
					request);
		} else if (solrCoreName.equals("impc_images")) {
			rows = composeImpcImageDataTableRows(query, json, iDisplayStart, iDisplayLength, showImgView, fqStr,
					request);
		} else if (solrCoreName.equals("disease")) {
			rows = composeDiseaseDataTableRows(json, request);
		}

		return rows;
	}

	private List<String> composeProtocolDataTableRows(JSONObject json, HttpServletRequest request) {
		JSONArray docs = json.getJSONObject("response").getJSONArray("docs");

		String impressBaseUrl = request.getAttribute("drupalBaseUrl").toString().replace("https", "http")
				+ "/impress/impress/displaySOP/";
		// String impressBaseUrl = request.getAttribute("drupalBaseUrl") +
		// "/impress/impress/displaySOP/";

		List<String> rowData = new ArrayList();
		rowData.add("Parameter\tProcedure\tProcedure Impress link\tPipeline"); // column
																				// names

		for (int i = 0; i < docs.size(); i++) {
			List<String> data = new ArrayList();
			JSONObject doc = docs.getJSONObject(i);
			data.add(doc.getString("parameter_name"));

			JSONArray procedures = doc.getJSONArray("procedure_name");
			JSONArray procedure_stable_keys = doc.getJSONArray("procedure_stable_key");

			List<String> procedureLinks = new ArrayList<String>();
			for (int p = 0; p < procedures.size(); p++) {
				// String procedure = procedures.get(p).toString();
				String procedure_stable_key = procedure_stable_keys.get(p).toString();
				procedureLinks.add(impressBaseUrl + procedure_stable_key);
			}

			// String procedure = doc.getString("procedure_name");
			// data.add(procedure);
			data.add(StringUtils.join(procedures, "|"));

			// String procedure_stable_key =
			// doc.getString("procedure_stable_key");
			// String procedureLink = impressBaseUrl + procedure_stable_key;
			// data.add(procedureLink);
			data.add(StringUtils.join(procedureLinks, "|"));

			data.add(doc.getString("pipeline_name"));
			rowData.add(StringUtils.join(data, "\t"));
		}
		return rowData;
	}

	private List<String> composeImageDataTableRows(String query, JSONObject json, Integer iDisplayStart,
			Integer iDisplayLength, boolean showImgView, String solrParams, HttpServletRequest request) {
		// System.out.println("query: "+ query + " -- "+ solrParams);

		String mediaBaseUrl = config.get("mediaBaseUrl").replace("https:", "http:");

		List<String> rowData = new ArrayList();

		String mpBaseUrl = request.getAttribute("baseUrl") + "/phenotypes/";
		String maBaseUrl = request.getAttribute("baseUrl") + "/anatomy/";
		String geneBaseUrl = request.getAttribute("baseUrl") + "/genes/";

		if (showImgView) {

			JSONArray docs = json.getJSONObject("response").getJSONArray("docs");
			rowData.add("Annotation term\tAnnotation id\tAnnotation id link\tImage link"); // column
																							// names

			for (int i = 0; i < docs.size(); i++) {

				JSONObject doc = docs.getJSONObject(i);

				List<String> data = new ArrayList();
				List<String> lists = new ArrayList();
				List<String> termLists = new ArrayList();
				List<String> link_lists = new ArrayList();

				String[] fields = { "annotationTermId", "expName", "symbol_gene" };
				for (String fld : fields) {
					if (doc.has(fld)) {

						JSONArray list = doc.getJSONArray(fld);

						if (fld.equals("annotationTermId")) {

							JSONArray termList = doc.containsKey("annotationTermName")
									? doc.getJSONArray("annotationTermName") : new JSONArray();

							for (int l = 0; l < list.size(); l++) {
								String value = list.getString(l);
								String termVal = termList.size() == 0 ? NO_INFO_MSG : termList.getString(l);

								if (value.startsWith("MP:")) {
									link_lists.add(hostName + mpBaseUrl + value);
									termLists.add("MP:" + termVal);
								}
								if (value.startsWith("MA:")) {
									link_lists.add(hostName + maBaseUrl + value);
									termLists.add("MA:" + termVal);
								}

								lists.add(value); // id
							}
						} else if (fld.equals("symbol_gene")) {
							// gene symbol and its link
							for (int l = 0; l < list.size(); l++) {
								String[] parts = list.getString(l).split("_");
								String symbol = parts[0];
								String mgiId = parts[1];
								termLists.add("Gene:" + symbol);
								lists.add(mgiId);
								link_lists.add(hostName + geneBaseUrl + mgiId);
							}
						} else if (fld.equals("expName")) {

							for (int l = 0; l < list.size(); l++) {
								String value = list.getString(l);

								termLists.add("Procedure:" + value);
								lists.add(NO_INFO_MSG);
								link_lists.add(NO_INFO_MSG);
							}
						}
					}
				}

				data.add(termLists.size() == 0 ? NO_INFO_MSG : StringUtils.join(termLists, "|")); // term
																									// names
				data.add(StringUtils.join(lists, "|"));
				data.add(StringUtils.join(link_lists, "|"));

				data.add(mediaBaseUrl + "/" + doc.getString("largeThumbnailFilePath"));
				rowData.add(StringUtils.join(data, "\t"));
			}
		} else {
			// System.out.println("MODE: annotview " + showImgView);
			// annotation view
			// annotation view: images group by annotationTerm per row
			rowData.add(
					"Annotation type\tAnnotation term\tAnnotation id\tAnnotation id link\tRelated image count\tImages link"); // column
																																// names
			JSONObject facetFields = json.getJSONObject("facet_counts").getJSONObject("facet_fields");

			JSONArray sumFacets = solrIndex.mergeFacets(facetFields);

			int numFacets = sumFacets.size();
			int quotient = (numFacets / 2) / iDisplayLength - ((numFacets / 2) % iDisplayLength) / iDisplayLength;
			int remainder = (numFacets / 2) % iDisplayLength;
			int start = iDisplayStart * 2; // 2 elements(name, count), hence
											// multiply by 2
			int end = iDisplayStart == quotient * iDisplayLength ? (iDisplayStart + remainder) * 2
					: (iDisplayStart + iDisplayLength) * 2;

			for (int i = start; i < end; i = i + 2) {
				List<String> data = new ArrayList();
				// array element is an alternate of facetField and facetCount

				String[] names = sumFacets.get(i).toString().split("_");
				if (names.length == 2) { // only want facet value of xxx_yyy
					String annotName = names[0];

					Map<String, String> hm = solrIndex.renderFacetField(names, request.getAttribute("mappedHostname").toString(), request.getAttribute("baseUrl").toString());

					data.add(hm.get("label"));
					data.add(annotName);
					data.add(hm.get("id"));
					// System.out.println("annotname: "+ annotName);
					if (hm.get("fullLink") != null) {
						data.add(hm.get("fullLink").toString());
					} else {
						data.add(NO_INFO_MSG);
					}

					String imgCount = sumFacets.get(i + 1).toString();
					data.add(imgCount);

					String facetField = hm.get("field");

					solrParams = solrParams.replaceAll("&q=.+&",
							"&q=" + query + " AND " + facetField + ":\"" + names[0] + "\"&");
					String imgSubSetLink = hostName + request.getAttribute("baseUrl") + "/imagesb?" + solrParams;
					
					data.add(imgSubSetLink);
					rowData.add(StringUtils.join(data, "\t"));
				}
			}
		}

		return rowData;
	}

	private List<String> composeImpcImageDataTableRows(String query, JSONObject json, Integer iDisplayStart,
			Integer iDisplayLength, boolean showImgView, String fqStrOri, HttpServletRequest request)
					throws IOException, URISyntaxException {

		// currently just use the solr field value
		// String mediaBaseUrl = config.get("impcMediaBaseUrl").replace("https:", "http:");
		List<String> rowData = new ArrayList();
		
        //String mediaBaseUrl = config.get("mediaBaseUrl");

		String baseUrl = request.getAttribute("baseUrl").toString();
		String mpBaseUrl = baseUrl + "/phenotypes/";
		String maBaseUrl = baseUrl + "/anatomy/";
		String geneBaseUrl = baseUrl + "/genes/";

		if (showImgView) {

			JSONArray docs = json.getJSONObject("response").getJSONArray("docs");
			// rowData.add("Annotation term\tAnnotation id\tAnnotation id
			// link\tProcedure\tGene symbol\tGene symbol link\tImage link"); //
			// column names
			rowData.add("Procedure\tGene symbol\tGene symbol link\tMA term\tMA term link\tImage link"); // column
																										// names

			for (int i = 0; i < docs.size(); i++) {
				List<String> data = new ArrayList();
				JSONObject doc = docs.getJSONObject(i);

				// String[] fields = {"annotationTermName", "annotationTermId",
				// "expName", "symbol_gene"};
				String[] fields = { "procedure_name", "gene_symbol", "ma_term" };
				for (String fld : fields) {
					if (doc.has(fld)) {
						List<String> lists = new ArrayList();

						if (fld.equals("gene_symbol")) {

							data.add(doc.getString("gene_symbol"));
							data.add(hostName + geneBaseUrl + doc.getString("gene_accession_id"));

						} else if (fld.equals("procedure_name")) {
							data.add(doc.getString("procedure_name"));
						} else if (fld.equals("ma_term")) {
							JSONArray maTerms = doc.getJSONArray("ma_term");
							JSONArray maIds = doc.getJSONArray("ma_id");
							List<String> ma_Terms = new ArrayList<>();
							List<String> ma_links = new ArrayList<>();
							for (int m = 0; m < maTerms.size(); m++) {
								ma_Terms.add(maTerms.get(m).toString());
								ma_links.add(hostName + maBaseUrl + maIds.get(m).toString());
							}

							data.add(StringUtils.join(ma_Terms, "|"));
							data.add(StringUtils.join(ma_links, "|"));

						}
					} else {
						/*
						 * if ( fld.equals("annotationTermId") ){
						 * data.add(NO_INFO_MSG); data.add(NO_INFO_MSG);
						 * data.add(NO_INFO_MSG); } else if (
						 * fld.equals("symbol_gene") ){
						 */
						if (fld.equals("gene_symbol")) {
							data.add(NO_INFO_MSG);
							data.add(NO_INFO_MSG);
						} else if (fld.equals("procedure_name")) {
							data.add(NO_INFO_MSG);
						} else if (fld.equals("ma_term")) {
							data.add(NO_INFO_MSG);
							data.add(NO_INFO_MSG);
						}
					}
				}

				data.add(doc.containsKey("jpeg_url") ? doc.getString("jpeg_url") : NO_INFO_MSG);
				rowData.add(StringUtils.join(data, "\t"));
			}
		} else {

			// annotation view: images group by annotationTerm per row
			//String mediaBaseUrl = config.get("mediaBaseUrl");
			// need to add hostname as this is for excel and not browser
			String mediaBaseUrl = request.getAttribute("mappedHostname").toString() + baseUrl + "/impcImages/images?";
			//System.out.println("MEDIABASEURL: "+ mediaBaseUrl);
			
			rowData.add(
					"Annotation type\tAnnotation term\tAnnotation id\tAnnotation id link\tRelated image count\tImages link"); // column
																																// names

			String fqStr = query;

			String defaultQStr = "observation_type:image_record&qf=auto_suggest&defType=edismax";

			if (query != "") {
				defaultQStr = "q=" + query + " AND " + defaultQStr;
			} else {
				defaultQStr = "q=" + defaultQStr;
			}

			String defaultFqStr = "fq=(biological_sample_group:experimental)";

			JSONObject facetFields = json.getJSONObject("facet_counts").getJSONObject("facet_fields");

			List<AnnotNameValCount> annots = solrIndex.mergeImpcFacets(json, baseUrl);

			int numFacets = annots.size();
			int start = iDisplayStart; // 2 elements(name, count), hence
										// multiply by 2
			int end = iDisplayStart + iDisplayLength;
			end = end > numFacets ? numFacets : end;

			for (int i = start; i < end; i++) {

				List<String> data = new ArrayList();

				AnnotNameValCount annot = annots.get(i);

				String displayAnnotName = annot.name;
				data.add(displayAnnotName);

				String annotVal = annot.val;
				data.add(annotVal);

				if (annot.id != null) {
					data.add(annot.id);
					data.add(annot.link);
				} else {
					data.add(NO_INFO_MSG);
					data.add(NO_INFO_MSG);
				}

				String thisFqStr = defaultFqStr + " AND " + annot.facet + ":\"" + annotVal + "\"";

				List pathAndImgCount = solrIndex.fetchImpcImagePathByAnnotName(query, thisFqStr);

				int imgCount = (int) pathAndImgCount.get(1);

				StringBuilder sb = new StringBuilder();
				sb.append("");
				sb.append(imgCount);
				data.add(sb.toString());

				String imgSubSetLink = mediaBaseUrl + defaultQStr + "&" + thisFqStr;
				//System.out.println("IMG LINK: " + imgSubSetLink );
				data.add(imgSubSetLink);

				rowData.add(StringUtils.join(data, "\t"));
			}
		}

		return rowData;
	}

	private List<String> composeMpDataTableRows(JSONObject json, HttpServletRequest request) {
		JSONArray docs = json.getJSONObject("response").getJSONArray("docs");

		String baseUrl = request.getAttribute("baseUrl") + "/phenotypes/";

		List<String> rowData = new ArrayList();
		rowData.add(
				"Mammalian phenotype term\tMammalian phenotype id\tMammalian phenotype id link\tMammalian phenotype definition\tMammalian phenotype synonym\tMammalian phenotype top level term\tComputationally mapped human phenotype terms\tComputationally mapped human phenotype term Ids\tPostqc call(s)"); // column
																																																																													// names

		for (int i = 0; i < docs.size(); i++) {
			List<String> data = new ArrayList();
			JSONObject doc = docs.getJSONObject(i);

			data.add(doc.getString("mp_term"));
			String mpId = doc.getString("mp_id");
			data.add(mpId);
			data.add(hostName + baseUrl + mpId);

{'fraction_non_alphanumeric': 0.13973422759895948, 'fraction_numerical': 0.0028833798226094586, 'mean_word_length': 3.7249370650081444, 'pattern_counts': {'":': 6, '<': 133, '<?xml version=': 0, '>': 107, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '261927', 'n_tokens_mistral': 23134, 'n_tokens_neox': 19229, 'n_words': 4167}