Project

General

Profile

« Previous | Next » 

Revision 4872

Now doing the dataquery/download in the FIRST skin's jsp rather than embedded in the Metacat servlet.
TODO: implement the very-custom data return format agreed upon by the FIRST team

View differences:

lib/style/skins/first/download.jsp
1
<%@page 
2
import="java.util.Iterator"%><%@page 
3
import="java.util.ArrayList"%><%@page 
4
import="edu.ucsb.nceas.utilities.OrderedMap"%><%@page 
5
import="java.util.List"%><%@page 
6
import="java.util.Enumeration"%><%@page 
7
import="java.sql.SQLException"%><%@page 
8
import="org.ecoinformatics.datamanager.transpose.DataTranspose"%><%@page 
9
import="au.com.bytecode.opencsv.CSVWriter"%><%@page 
10
import="java.io.OutputStreamWriter"%><%@page 
11
import="java.io.Writer"%><%@page 
12
import="java.sql.ResultSet"%><%@page 
13
import="edu.ucsb.nceas.metacat.dataquery.DataQuery"%><%@page 
14
import="java.io.IOException"%><%@page 
15
import="edu.ucsb.nceas.utilities.PropertyNotFoundException"%><%@page 
16
import="java.util.Hashtable"%><%@ page 
17
language="java" %><%
18
/**
19
 * 
20
 * '$RCSfile$'
21
 * Copyright: 2008 Regents of the University of California and the
22
 *             National Center for Ecological Analysis and Synthesis
23
 *    '$Author: leinfelder $'
24
 *      '$Date: 2008-08-22 16:48:56 -0700 (Fri, 22 Aug 2008) $'
25
 * '$Revision: 4305 $'
26
 * 
27
 * This program is free software; you can redistribute it and/or modify
28
 * it under the terms of the GNU General Public License as published by
29
 * the Free Software Foundation; either version 2 of the License, or
30
 * (at your option) any later version.
31
 * 
32
 * This program is distributed in the hope that it will be useful,
33
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
34
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
35
 * GNU General Public License for more details.
36
     
37
 * You should have received a copy of the GNU General Public License
38
 * along with this program; if not, write to the Free Software
39
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
40
 */  
41
%><%
42
Hashtable params = getParams(request);
43
handleDataquery(params,response,request.getSession().getId());
44
%><%!
45
	private Hashtable getParams(HttpServletRequest request) {
46
		Hashtable params = new Hashtable();
47
		Enumeration<String> paramlist = (Enumeration<String>)request.getParameterNames();
48
		while (paramlist.hasMoreElements()) {
49
	
50
			String name = paramlist.nextElement();
51
			String[] value = request.getParameterValues(name);
52
			params.put(name, value);
53
		}
54
	
55
		return params;
56
	}
57
%><%!
58
	private List transpose(ResultSet rs, int idCol, int pivotCol, boolean omitIdValues) throws SQLException {
59
		OrderedMap table = new OrderedMap();
60
		OrderedMap widestRow = new OrderedMap();
61
		int colCount = rs.getMetaData().getColumnCount();
62
		String idColName = rs.getMetaData().getColumnName(idCol);
63
		
64
		while (rs.next()) {
65
			String id = rs.getString(idCol);
66
			String pivotValue = rs.getString(pivotCol);
67
			
68
			//look up the row we are working on
69
			OrderedMap row = (OrderedMap) table.get(id);
70
			if (row == null) {
71
				row = new OrderedMap();
72
			}
73
			
74
			//get the values for this pivot
75
			for (int i = 1; i <= colCount; i++) {
76
				if (i != pivotCol) {
77
					String colName = rs.getMetaData().getColumnName(i);
78
					//annotate the column name with the pivot column value if not the id column
79
					if (i != idCol) {
80
						colName = pivotValue + "_" + colName;
81
					}
82
					String value = rs.getString(i);
83
					row.put(colName, value);
84
				}
85
			}
86
			//track the headers - the values are junk
87
			widestRow.putAll(row);
88
			
89
			//put the row back (or maybe it's the first time)
90
			table.put(id, row);	
91
		}
92
		
93
		//now make it into a list
94
		List retTable = new ArrayList();
95
		
96
		//do the header, based on widest entry
97
		List header = new ArrayList(widestRow.keySet());
98
		retTable.add(header.toArray(new String[0]));
99
		
100
		//now the value rows
101
		Iterator rowIter = table.values().iterator();
102
		int rowCount = 1;
103
		while (rowIter.hasNext()) {
104
			OrderedMap rowMap = (OrderedMap) rowIter.next();
105
			List row = new ArrayList();
106
			//iterate over the widest row's columns
107
			Iterator columnIter = widestRow.keySet().iterator();
108
			while (columnIter.hasNext()) {
109
				Object key = columnIter.next();
110
				Object value = rowMap.get(key);
111
				//hide the value used for Ids - just increment row
112
				if (key.equals(idColName) && omitIdValues) {
113
					value = String.valueOf(rowCount);
114
				}
115
				row.add(value);
116
			}
117
			rowCount++;
118
			retTable.add(row.toArray(new String[0]));
119
		}
120
		
121
		return retTable;
122
	}
123
%><%!
124
	private void handleDataquery(
125
			Hashtable<String, String[]> params,
126
	        HttpServletResponse response,
127
	        String sessionId) throws PropertyNotFoundException, IOException {
128
		
129
		DataQuery dq = null;
130
		if (sessionId != null) {
131
			dq = new DataQuery(sessionId);
132
		}
133
		else {
134
			dq = new DataQuery();
135
		}
136
		
137
		String dataqueryXML = (params.get("dataquery"))[0];
138
	
139
		ResultSet rs = null;
140
		try {
141
			rs = dq.executeQuery(dataqueryXML);
142
		} catch (Exception e) {
143
			//probably need to do something here
144
			e.printStackTrace();
145
			return;
146
		}
147
		
148
		//process the result set
149
		String qformat = "csv";
150
		String[] temp = params.get("qformat");
151
		if (temp != null && temp.length > 0) {
152
			qformat = temp[0];
153
		}
154
		String fileName = "query-results." + qformat;
155
		
156
		boolean transpose = false;
157
		temp = params.get("transpose");
158
		if (temp != null && temp.length > 0) {
159
			transpose = Boolean.parseBoolean(temp[0]);
160
		}
161
		int observation = 0;
162
		temp = params.get("observation");
163
		if (temp != null && temp.length > 0) {
164
			observation = Integer.parseInt(temp[0]);
165
		}
166
		int pivot = 0;
167
		temp = params.get("pivot");
168
		if (temp != null && temp.length > 0) {
169
			pivot = Integer.parseInt(temp[0]);
170
		}
171
		
172
		//get the results as csv file
173
		if (qformat != null && qformat.equalsIgnoreCase("csv")) {
174
			response.setContentType("text/csv");
175
			//response.setContentType("application/csv");
176
	        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
177
	        
178
			Writer writer = new OutputStreamWriter(response.getOutputStream());
179
			CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
180
			try {
181
				if (transpose) {
182
					List transposedTable = transpose(rs, observation, pivot, true);
183
					csv.writeAll(transposedTable);
184
				} else {
185
					csv.writeAll(rs, true);
186
				}
187
				
188
				csv.flush();
189
				response.flushBuffer();
190
				
191
				rs.close();
192
				
193
			} catch (SQLException e) {
194
				e.printStackTrace();
195
			}
196
			
197
			return;
198
		}
199
		
200
	}
201
%>
lib/style/skins/first/first-assessment-resultset.xsl
256 256
			</form>	
257 257
			
258 258
			<!-- for downloading data -->				
259
			<form action="{$contextURL}/metacat" method="POST" id="assessmentForm" name="assessmentForm" >
259
			<form action="{$contextURL}/style/skins/first/download.jsp" method="POST" id="assessmentForm" name="assessmentForm" >
260 260
				<input type="hidden" name="qformat" value="first" />
261 261
				<input type="hidden" name="sessionid">
262 262
					<xsl:attribute name="value">

Also available in: Unified diff