<%@page import="java.util.Iterator"%><%@page import="java.util.ArrayList"%><%@page import="edu.ucsb.nceas.utilities.OrderedMap"%><%@page import="java.util.List"%><%@page import="java.util.Enumeration"%><%@page import="java.sql.SQLException"%><%@page import="org.ecoinformatics.datamanager.transpose.DataTranspose"%><%@page import="au.com.bytecode.opencsv.CSVWriter"%><%@page import="java.io.OutputStreamWriter"%><%@page import="java.io.Writer"%><%@page import="java.sql.ResultSet"%><%@page import="edu.ucsb.nceas.metacat.dataquery.DataQuery"%><%@page import="java.io.IOException"%><%@page import="edu.ucsb.nceas.utilities.PropertyNotFoundException"%><%@page import="edu.ucsb.nceas.metacat.util.SystemUtil"%><%@page import="java.util.Hashtable"%><%@ page language="java" %><% /** * * '$RCSfile$' * Copyright: 2008 Regents of the University of California and the * National Center for Ecological Analysis and Synthesis * '$Author: leinfelder $' * '$Date: 2008-08-22 16:48:56 -0700 (Fri, 22 Aug 2008) $' * '$Revision: 4305 $' * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ %><% Hashtable params = getParams(request); handleDataquery(params,response,request.getSession().getId()); %><%! private Hashtable getParams(HttpServletRequest request) { Hashtable params = new Hashtable(); Enumeration paramlist = (Enumeration)request.getParameterNames(); while (paramlist.hasMoreElements()) { String name = paramlist.nextElement(); String[] value = request.getParameterValues(name); params.put(name, value); } return params; } %><%! private List processResultsSet(ResultSet rs, int omitColumn, String omitColumnLabel) throws SQLException { List retTable = new ArrayList(); int colCount = rs.getMetaData().getColumnCount(); List columnHeaders = new ArrayList(); int recordCount = 1; OrderedMap uniqueIds = new OrderedMap(); while (rs.next()) { List row = new ArrayList(); //get the values for this row for (int i = 1; i <= colCount; i++) { String colName = rs.getMetaData().getColumnName(i); String value = rs.getString(i); //clean up the value value = cleanUp(value); //hide the ids if (i == omitColumn) { String lookupValue = (String) uniqueIds.get(value); if (lookupValue == null) { lookupValue = recordCount + ""; } uniqueIds.put(value, lookupValue); value = lookupValue; colName = omitColumnLabel; } if (recordCount == 1) { columnHeaders.add(colName); } row.add(value); } retTable.add(row.toArray(new String[0])); recordCount++; } //make sure there are no "?column?" headers for (int i = 0; i < columnHeaders.size(); i++) { String columnName = (String) columnHeaders.get(i); if (columnName.equals("?column?")) { String previousColumn = (String) columnHeaders.get(i-1); String[] previousParts = previousColumn.split("_"); int previousCount = Integer.parseInt(previousParts[1]); columnName = previousParts[0] + "_"+ (previousCount + 1); columnHeaders.set(i, columnName); } } retTable.add(0, columnHeaders.toArray(new String[0])); return retTable; } %><%! private List transpose(ResultSet rs, int idCol, int pivotCol, List pivotAttributes, boolean omitIdValues) throws SQLException { //map keyed by id column - data OrderedMap table = new OrderedMap(); //track all the data columns OrderedMap widestRow = new OrderedMap(); //map keyed by the pivot column - metadata OrderedMap headerRows = new OrderedMap(); //vocab columns String vocabNameCol = "qmetadatavocabulary"; String vocabValueCol = "qmetadatavalue"; //maps for the vocab lists OrderedMap vocabNames = new OrderedMap(); OrderedMap vocabValues = new OrderedMap(); //all vocab names/values List allVocabNames = new ArrayList(); List allVocabValues = new ArrayList(); boolean twoColumnMetadata = false; int colCount = rs.getMetaData().getColumnCount(); String idColName = rs.getMetaData().getColumnName(idCol); while (rs.next()) { String id = rs.getString(idCol); String pivotValue = rs.getString(pivotCol); //look up the data row we are working on OrderedMap row = (OrderedMap) table.get(id); if (row == null) { row = new OrderedMap(); } //look up the metadata row we are working on OrderedMap metadataRow = (OrderedMap) table.get(pivotValue); if (metadataRow == null) { metadataRow = new OrderedMap(); } //get the values for this pivot for (int i = 1; i <= colCount; i++) { if (i != pivotCol) { String colName = rs.getMetaData().getColumnName(i); //check for "missing" headers in the qchoice /* if (colName.equals("?column?")) { String previousColumn = rs.getMetaData().getColumnName(i-1); if (previousColumn.startsWith("qchoice")) { String[] previousParts = previousColumn.split("_"); int previousCount = 0; if (previousParts.length == 2) { previousCount = Integer.parseInt(previousParts[1]); } colName = previousParts[0] + "_"+ (previousCount + 1); } } */ String value = rs.getString(i); //clean up the value value = cleanUp(value); //do we include this column in the pivot? if (pivotAttributes.contains(colName)) { //annotate the column name with the pivot column value if not the id column if (i != idCol) { colName = pivotValue + "_" + colName; } row.put(colName, value); } else { if (colName.startsWith(vocabNameCol) || colName.startsWith(vocabValueCol)) { //don't add it to the normal metadata } else { metadataRow.put(colName, value); } } //names if (colName.startsWith(vocabNameCol)) { List list = (List) vocabNames.get(pivotValue); if (list == null) { list = new ArrayList(); } list.add(value); vocabNames.put(pivotValue, list); allVocabNames.add(value); } //values if (colName.startsWith(vocabValueCol)) { List list = (List) vocabValues.get(pivotValue); if (list == null) { list = new ArrayList(); } list.add(value); vocabValues.put(pivotValue, list); allVocabValues.add(value); } } } //track the data columns - the values are junk widestRow.putAll(row); //put the row back (or maybe it's the first time) table.put(id, row); //put the metadata header back headerRows.put(pivotValue, metadataRow); } /** Construct the table structure for returning **/ //now make it into a list List retTable = new ArrayList(); //map keyed by metadata labels OrderedMap metadataHeaders = new OrderedMap(); //do the data header - drives the other columns - based on widest entry List header = new ArrayList(widestRow.keySet()); //do the metadata header rows (basically rotate them around) Iterator headerIter = header.iterator(); String lastValue = ""; while (headerIter.hasNext()) { String column = (String) headerIter.next(); //get the pivotValue part of column name String pivotValue = column; try { pivotValue = column.substring(0, column.indexOf("_")); } catch (Exception e) {} //look up the row from the metadata - keyed by pivot value OrderedMap metadataRow = (OrderedMap) headerRows.get(pivotValue); if (metadataRow != null) { //go through the values Iterator metadataIter = metadataRow.keySet().iterator(); while (metadataIter.hasNext()) { String key = (String) metadataIter.next(); String value = (String) metadataRow.get(key); OrderedMap newMetadataRow = (OrderedMap) metadataHeaders.get(key); if (newMetadataRow == null) { newMetadataRow = new OrderedMap(); } //if it's the same as the last one, just use null value if (lastValue.equals(pivotValue)) { value = null; } newMetadataRow.put(column, value); metadataHeaders.put(key, newMetadataRow); } } lastValue = pivotValue; } //make metadata rows as list/arrays on the reteurn table Iterator metadataLabelIter = metadataHeaders.keySet().iterator(); while (metadataLabelIter.hasNext()) { String label = (String) metadataLabelIter.next(); OrderedMap row = (OrderedMap) metadataHeaders.get(label); List rowValues = new ArrayList(row.values()); rowValues.add(0, label); if (twoColumnMetadata) { //add extra column rowValues.add(1, null); } retTable.add(rowValues.toArray(new String[0])); } //create the special vocab matrix rows List vocabTable = new ArrayList(); List uniqueVocabs = new ArrayList(); for (int i = 0; i < allVocabNames.size(); i++) { List vocabRow = new ArrayList(); String vocabName = (String) allVocabNames.get(i); String vocabValue = (String) allVocabValues.get(i); String key = vocabName + "/" + vocabValue; //check if we've processed this already, skip if so if (uniqueVocabs.contains(key)) { continue; } uniqueVocabs.add(key); if (twoColumnMetadata) { vocabRow.add(vocabName); vocabRow.add(vocabValue); } else { vocabRow.add(key); } //go through the questions now, again String lastPivotValue = ""; headerIter = header.iterator(); while (headerIter.hasNext()) { String column = (String) headerIter.next(); //get the pivotValue part of column name if it exists String pivotValue = null; try { pivotValue = column.substring(0, column.indexOf("_")); } catch (Exception e) {} if (pivotValue == null) { continue; } //check to not duplicate values if (pivotValue.equals(lastPivotValue)) { vocabRow.add(null); } else { //check to see if this question has that keyword List names = (List) vocabNames.get(pivotValue); List values = (List) vocabValues.get(pivotValue); String containsVocabItem = "false"; if (names != null) { int vocabNameIndex = names.indexOf(vocabName); int vocabValueIndex = values.indexOf(vocabValue); // contains the vocab and the value _somewhere_ if (vocabNameIndex > -1 && vocabValueIndex > -1) { containsVocabItem = "true"; } } vocabRow.add(containsVocabItem); } lastPivotValue = pivotValue; } //put the row on vocabTable.add(vocabRow.toArray(new String[0])); } //put the vocab matrix on the table retTable.addAll(vocabTable); if (twoColumnMetadata) { //add column to data header row header.add(1, null); } //replace the "studentId" label int temp = header.indexOf("studentid"); if (header.remove(temp) != null) { header.add(temp, "recordNum"); } //put the data header row on the table retTable.add(header.toArray(new String[0])); //now the value rows in the table Iterator rowIter = table.values().iterator(); int rowCount = 1; while (rowIter.hasNext()) { OrderedMap rowMap = (OrderedMap) rowIter.next(); List row = new ArrayList(); //iterate over the widest row's columns Iterator columnIter = widestRow.keySet().iterator(); while (columnIter.hasNext()) { Object key = columnIter.next(); Object value = rowMap.get(key); //hide the value used for Ids - just increment row if (key.equals(idColName) && omitIdValues) { value = String.valueOf(rowCount); } row.add(value); } rowCount++; if (twoColumnMetadata) { //add extra column row.add(1, null); } retTable.add(row.toArray(new String[0])); } return retTable; } %><%! private String cleanUp(String value) { if (value != null) { value = value.replaceAll("\n", " "); value = value.replaceAll("\\s+", " "); value = value.replaceAll("", " "); value = value.replaceAll("", " "); value = value.replaceAll("", " "); value = value.replaceAll("", " "); value = value.replaceAll("", " "); value = value.replaceAll("", " "); //translate any ecogrid urls value = convertEcogridURL(value); } return value; } %><%! private String convertEcogridURL(String value) { if (value != null) { String prefix = "ecogrid://knb/"; if (value.startsWith(prefix)) { //String docid = value.substring(prefix.length(), value.length()); // TODO make URL String contextURL = ""; try { contextURL = SystemUtil.getContextURL(); } catch (PropertyNotFoundException pnfe) { //do nothing } value = value.replaceFirst(prefix, contextURL + "/metacat?action=read&docid="); } } return value; } %><%! private void handleDataquery( Hashtable params, HttpServletResponse response, String sessionId) throws PropertyNotFoundException, IOException { DataQuery dq = null; if (sessionId != null) { dq = new DataQuery(sessionId); } else { dq = new DataQuery(); } String dataqueryXML = (params.get("dataquery"))[0]; ResultSet rs = null; try { rs = dq.executeQuery(dataqueryXML); } catch (Exception e) { //probably need to do something here e.printStackTrace(); return; } //process the result set String qformat = "csv"; String[] temp = params.get("qformat"); if (temp != null && temp.length > 0) { qformat = temp[0]; } String fileName = "query-results." + qformat; boolean transpose = false; temp = params.get("transpose"); if (temp != null && temp.length > 0) { transpose = Boolean.parseBoolean(temp[0]); } int observation = 0; temp = params.get("observation"); if (temp != null && temp.length > 0) { observation = Integer.parseInt(temp[0]); } int pivot = 0; temp = params.get("pivot"); if (temp != null && temp.length > 0) { pivot = Integer.parseInt(temp[0]); } String[] pivotColumns = null; temp = params.get("pivotColumns"); if (temp != null && temp.length > 0) { pivotColumns = temp; } //get the results as csv file if (qformat != null && qformat.equalsIgnoreCase("csv")) { response.setContentType("text/csv"); //response.setContentType("application/csv"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); Writer writer = new OutputStreamWriter(response.getOutputStream()); //CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER); CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER); try { if (transpose) { List pivotCols = new ArrayList(); if (pivotColumns != null) { for (int i = 0; i < pivotColumns.length; i++) { pivotCols.add(pivotColumns[i]); } } else { pivotCols.add("studentid"); pivotCols.add("score"); pivotCols.add("response"); pivotCols.add("responsefile"); } List transposedTable = transpose(rs, observation, pivot, pivotCols, true); csv.writeAll(transposedTable); } else { List processedTable = processResultsSet(rs, 3, "recordNum"); csv.writeAll(processedTable); } csv.flush(); response.flushBuffer(); rs.close(); } catch (SQLException e) { e.printStackTrace(); } return; } } %>