Project

General

Profile

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, List pivotAttributes, boolean omitIdValues) throws SQLException {
59
		//map keyed by id column - data
60
		OrderedMap table = new OrderedMap();
61
		//track all the data columns
62
		OrderedMap widestRow = new OrderedMap();
63
		//map keyed by the pivot column - metadata
64
		OrderedMap headerRows = new OrderedMap();
65
		
66
		//vocab columns
67
		String vocabNameCol = "qmetadatavocabulary";
68
		String vocabValueCol = "qmetadatavalue";
69
		//maps for the vocab lists
70
		OrderedMap vocabNames = new OrderedMap();
71
		OrderedMap vocabValues = new OrderedMap();
72
		//all vocab names/values
73
		List allVocabNames = new ArrayList();
74
		List allVocabValues = new ArrayList();
75
		
76
		int colCount = rs.getMetaData().getColumnCount();
77
		String idColName = rs.getMetaData().getColumnName(idCol);
78
		
79
		while (rs.next()) {
80
			String id = rs.getString(idCol);
81
			String pivotValue = rs.getString(pivotCol);
82
			
83
			//look up the data row we are working on
84
			OrderedMap row = (OrderedMap) table.get(id);
85
			if (row == null) {
86
				row = new OrderedMap();
87
			}
88
			//look up the metadata row we are working on
89
			OrderedMap metadataRow = (OrderedMap) table.get(pivotValue);
90
			if (metadataRow == null) {
91
				metadataRow = new OrderedMap();
92
			}
93
			
94
			//get the values for this pivot
95
			for (int i = 1; i <= colCount; i++) {
96
				if (i != pivotCol) {
97
					String colName = rs.getMetaData().getColumnName(i);
98
					String value = rs.getString(i);
99
					//clean up the value
100
					if (value != null) {
101
						value = value.replaceAll("\n", " ");
102
						value = value.replaceAll("\\s+", " ");
103
						value = value.replaceAll("<html>", " ");
104
						value = value.replaceAll("</html>", " ");
105
						value = value.replaceAll("<head>", " ");
106
						value = value.replaceAll("</head>", " ");
107
						value = value.replaceAll("<body>", " ");
108
						value = value.replaceAll("</body>", " ");
109
					}
110
					//do we include this column in the pivot?
111
					if (pivotAttributes.contains(colName)) {
112
						//annotate the column name with the pivot column value if not the id column
113
						if (i != idCol) {
114
							colName = pivotValue + "_" + colName;
115
						}
116
						row.put(colName, value);
117
					}
118
					else {
119
						if (colName.startsWith(vocabNameCol) || colName.startsWith(vocabValueCol)) {
120
							//don't add it to the normal metadata
121
						}
122
						else {
123
							metadataRow.put(colName, value);
124
						}
125
					}
126
					//names
127
					if (colName.startsWith(vocabNameCol)) {
128
						List list = (List) vocabNames.get(pivotValue);
129
						if (list == null) {
130
							list = new ArrayList();
131
						}
132
						list.add(value);
133
						vocabNames.put(pivotValue, list);
134
						allVocabNames.add(value);
135
					}
136
					//values
137
					if (colName.startsWith(vocabValueCol)) {
138
						List list = (List) vocabValues.get(pivotValue);
139
						if (list == null) {
140
							list = new ArrayList();
141
						}
142
						list.add(value);
143
						vocabValues.put(pivotValue, list);
144
						allVocabValues.add(value);
145
					}
146
				}
147
			}
148
			//track the data columns - the values are junk
149
			widestRow.putAll(row);
150
			
151
			//put the row back (or maybe it's the first time)
152
			table.put(id, row);
153
			
154
			//put the metadata header back
155
			headerRows.put(pivotValue, metadataRow);
156
			
157
		}
158
		
159
		/** Construct the table structure for returning **/
160
		
161
		//now make it into a list
162
		List retTable = new ArrayList();
163
		
164
		//map keyed by metadata labels
165
		OrderedMap metadataHeaders = new OrderedMap();
166
		
167
		//do the data header - drives the other columns - based on widest entry
168
		List header = new ArrayList(widestRow.keySet());
169
		
170
		//do the metadata header rows (basically rotate them around)
171
		Iterator headerIter = header.iterator();
172
		while (headerIter.hasNext()) {
173
			String column = (String) headerIter.next();
174
			//get the pivotValue part of column name
175
			String pivotValue = column;
176
			try {
177
				pivotValue = column.substring(0, column.indexOf("_"));
178
			}
179
			catch (Exception e) {}
180
			//look up the row from the metadata - keyed by pivot value
181
			OrderedMap metadataRow = (OrderedMap) headerRows.get(pivotValue);
182
			if (metadataRow != null) {
183
				//go through the values
184
				Iterator metadataIter = metadataRow.keySet().iterator();
185
				while (metadataIter.hasNext()) {
186
					String key = (String) metadataIter.next();
187
					String value = (String) metadataRow.get(key);
188
					OrderedMap newMetadataRow = (OrderedMap) metadataHeaders.get(key);
189
					if (newMetadataRow == null) {
190
						newMetadataRow = new OrderedMap();
191
					}
192
					newMetadataRow.put(column, value);
193
					metadataHeaders.put(key, newMetadataRow);
194
				}
195
			}
196
		}
197
		
198
		//make metadata rows as list/arrays on the reteurn table
199
		Iterator metadataLabelIter = metadataHeaders.keySet().iterator();
200
		while (metadataLabelIter.hasNext()) {
201
			String label = (String) metadataLabelIter.next();
202
			OrderedMap row = (OrderedMap) metadataHeaders.get(label);
203
			List rowValues = new ArrayList(row.values());
204
			rowValues.add(0, label);
205
			retTable.add(rowValues.toArray(new String[0]));
206
		}
207
		
208
		//create the special vocab matrix rows
209
		List vocabTable = new ArrayList();
210
		List uniqueVocabs = new ArrayList();
211
		for (int i = 0; i < allVocabNames.size(); i++) {
212
			List vocabRow = new ArrayList();
213
			String vocabName = (String) allVocabNames.get(i);
214
			String vocabValue = (String) allVocabValues.get(i);
215
			String key = vocabName + "/" + vocabValue;
216
			//check if we've processed this already, skip if so
217
			if (uniqueVocabs.contains(key)) {
218
				continue;
219
			}
220
			uniqueVocabs.add(key);
221
			//TODO: expand the column count by one for _everything_
222
			vocabRow.add(key);
223
			//vocabRow.add(vocabName);
224
			//vocabRow.add(vocabValue);
225
			//go through the questions now, again
226
			headerIter = header.iterator();
227
			while (headerIter.hasNext()) {
228
				String column = (String) headerIter.next();
229
				//get the pivotValue part of column name if it exists
230
				String pivotValue = null;
231
				try {
232
					pivotValue = column.substring(0, column.indexOf("_"));
233
				}
234
				catch (Exception e) {}
235
				if (pivotValue == null) {
236
					continue;
237
				}
238
				//check to see if this question has that keyword
239
				List names = (List) vocabNames.get(pivotValue);
240
				List values = (List) vocabValues.get(pivotValue);
241
				if (names != null && names.indexOf(vocabName) > -1 && names.indexOf(vocabName) == values.indexOf(vocabValue) ) {
242
					vocabRow.add("true");
243
				}
244
				else {
245
					vocabRow.add("false");
246
				}
247
			}
248
			//put the row on
249
			vocabTable.add(vocabRow.toArray(new String[0]));
250
		}
251
		
252
		//put the vocab matrix on the table
253
		retTable.addAll(vocabTable);
254
		
255
		//put the data header row on the table
256
		retTable.add(header.toArray(new String[0]));
257
		
258
		//now the value rows in the table
259
		Iterator rowIter = table.values().iterator();
260
		int rowCount = 1;
261
		while (rowIter.hasNext()) {
262
			OrderedMap rowMap = (OrderedMap) rowIter.next();
263
			List row = new ArrayList();
264
			//iterate over the widest row's columns
265
			Iterator columnIter = widestRow.keySet().iterator();
266
			while (columnIter.hasNext()) {
267
				Object key = columnIter.next();
268
				Object value = rowMap.get(key);
269
				//hide the value used for Ids - just increment row
270
				if (key.equals(idColName) && omitIdValues) {
271
					value = String.valueOf(rowCount);
272
				}
273
				row.add(value);
274
			}
275
			rowCount++;
276
			retTable.add(row.toArray(new String[0]));
277
		}
278
		
279
		return retTable;
280
	}
281
%><%!
282
	private void handleDataquery(
283
			Hashtable<String, String[]> params,
284
	        HttpServletResponse response,
285
	        String sessionId) throws PropertyNotFoundException, IOException {
286
		
287
		DataQuery dq = null;
288
		if (sessionId != null) {
289
			dq = new DataQuery(sessionId);
290
		}
291
		else {
292
			dq = new DataQuery();
293
		}
294
		
295
		String dataqueryXML = (params.get("dataquery"))[0];
296
	
297
		ResultSet rs = null;
298
		try {
299
			rs = dq.executeQuery(dataqueryXML);
300
		} catch (Exception e) {
301
			//probably need to do something here
302
			e.printStackTrace();
303
			return;
304
		}
305
		
306
		//process the result set
307
		String qformat = "csv";
308
		String[] temp = params.get("qformat");
309
		if (temp != null && temp.length > 0) {
310
			qformat = temp[0];
311
		}
312
		String fileName = "query-results." + qformat;
313
		
314
		boolean transpose = false;
315
		temp = params.get("transpose");
316
		if (temp != null && temp.length > 0) {
317
			transpose = Boolean.parseBoolean(temp[0]);
318
		}
319
		int observation = 0;
320
		temp = params.get("observation");
321
		if (temp != null && temp.length > 0) {
322
			observation = Integer.parseInt(temp[0]);
323
		}
324
		int pivot = 0;
325
		temp = params.get("pivot");
326
		if (temp != null && temp.length > 0) {
327
			pivot = Integer.parseInt(temp[0]);
328
		}
329
		
330
		//get the results as csv file
331
		if (qformat != null && qformat.equalsIgnoreCase("csv")) {
332
			response.setContentType("text/csv");
333
			//response.setContentType("application/csv");
334
	        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
335
	        
336
			Writer writer = new OutputStreamWriter(response.getOutputStream());
337
			//CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
338
			CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER);
339
			try {
340
				if (transpose) {
341
					List pivotCols = new ArrayList();
342
					pivotCols.add("studentid");
343
					pivotCols.add("score");
344
					pivotCols.add("response");
345
					List transposedTable = transpose(rs, observation, pivot, pivotCols, true);
346
					csv.writeAll(transposedTable);
347
				} else {
348
					csv.writeAll(rs, true);
349
				}
350
				
351
				csv.flush();
352
				response.flushBuffer();
353
				
354
				rs.close();
355
				
356
			} catch (SQLException e) {
357
				e.printStackTrace();
358
			}
359
			
360
			return;
361
		}
362
		
363
	}
364
%>
(4-4/22)