Project

General

Profile

1 4872 leinfelder
<%@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 5063 leinfelder
import="edu.ucsb.nceas.utilities.PropertyNotFoundException"%><%@page
16
import="edu.ucsb.nceas.metacat.util.SystemUtil"%><%@page
17 4872 leinfelder
import="java.util.Hashtable"%><%@ page
18
language="java" %><%
19
/**
20
 *
21
 * '$RCSfile$'
22
 * Copyright: 2008 Regents of the University of California and the
23
 *             National Center for Ecological Analysis and Synthesis
24
 *    '$Author: leinfelder $'
25
 *      '$Date: 2008-08-22 16:48:56 -0700 (Fri, 22 Aug 2008) $'
26
 * '$Revision: 4305 $'
27
 *
28
 * This program is free software; you can redistribute it and/or modify
29
 * it under the terms of the GNU General Public License as published by
30
 * the Free Software Foundation; either version 2 of the License, or
31
 * (at your option) any later version.
32
 *
33
 * This program is distributed in the hope that it will be useful,
34
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
35
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
36
 * GNU General Public License for more details.
37
38
 * You should have received a copy of the GNU General Public License
39
 * along with this program; if not, write to the Free Software
40
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
41
 */
42
%><%
43
Hashtable params = getParams(request);
44
handleDataquery(params,response,request.getSession().getId());
45
%><%!
46
	private Hashtable getParams(HttpServletRequest request) {
47
		Hashtable params = new Hashtable();
48
		Enumeration<String> paramlist = (Enumeration<String>)request.getParameterNames();
49
		while (paramlist.hasMoreElements()) {
50
51
			String name = paramlist.nextElement();
52
			String[] value = request.getParameterValues(name);
53
			params.put(name, value);
54
		}
55
56
		return params;
57
	}
58
%><%!
59 4896 leinfelder
	private List processResultsSet(ResultSet rs, int omitColumn, String omitColumnLabel) throws SQLException {
60 4895 leinfelder
		List retTable = new ArrayList();
61
		int colCount = rs.getMetaData().getColumnCount();
62
		List columnHeaders = new ArrayList();
63
		int recordCount = 1;
64
		OrderedMap uniqueIds = new OrderedMap();
65
66
		while (rs.next()) {
67
			List row = new ArrayList();
68
			//get the values for this row
69
			for (int i = 1; i <= colCount; i++) {
70 4896 leinfelder
71
				String colName = rs.getMetaData().getColumnName(i);
72 4895 leinfelder
				String value = rs.getString(i);
73
74
				//clean up the value
75
				value = cleanUp(value);
76
77
				//hide the ids
78
				if (i == omitColumn) {
79
					String lookupValue = (String) uniqueIds.get(value);
80
					if (lookupValue == null) {
81
						lookupValue = recordCount + "";
82
					}
83
					uniqueIds.put(value, lookupValue);
84
					value = lookupValue;
85 4896 leinfelder
					colName = omitColumnLabel;
86 4895 leinfelder
				}
87 4896 leinfelder
				if (recordCount == 1) {
88
					columnHeaders.add(colName);
89
				}
90 4895 leinfelder
91
				row.add(value);
92
			}
93
			retTable.add(row.toArray(new String[0]));
94
			recordCount++;
95
		}
96
		retTable.add(0, columnHeaders.toArray(new String[0]));
97
		return retTable;
98
	}
99
%><%!
100 4874 leinfelder
	private List transpose(ResultSet rs, int idCol, int pivotCol, List pivotAttributes, boolean omitIdValues) throws SQLException {
101 4875 leinfelder
		//map keyed by id column - data
102 4872 leinfelder
		OrderedMap table = new OrderedMap();
103 4875 leinfelder
		//track all the data columns
104 4872 leinfelder
		OrderedMap widestRow = new OrderedMap();
105 4875 leinfelder
		//map keyed by the pivot column - metadata
106
		OrderedMap headerRows = new OrderedMap();
107
108 4883 leinfelder
		//vocab columns
109
		String vocabNameCol = "qmetadatavocabulary";
110
		String vocabValueCol = "qmetadatavalue";
111
		//maps for the vocab lists
112
		OrderedMap vocabNames = new OrderedMap();
113
		OrderedMap vocabValues = new OrderedMap();
114
		//all vocab names/values
115
		List allVocabNames = new ArrayList();
116
		List allVocabValues = new ArrayList();
117 5087 leinfelder
		boolean twoColumnMetadata = false;
118 4883 leinfelder
119 4872 leinfelder
		int colCount = rs.getMetaData().getColumnCount();
120
		String idColName = rs.getMetaData().getColumnName(idCol);
121
122
		while (rs.next()) {
123
			String id = rs.getString(idCol);
124
			String pivotValue = rs.getString(pivotCol);
125
126 4875 leinfelder
			//look up the data row we are working on
127 4872 leinfelder
			OrderedMap row = (OrderedMap) table.get(id);
128
			if (row == null) {
129
				row = new OrderedMap();
130
			}
131 4875 leinfelder
			//look up the metadata row we are working on
132
			OrderedMap metadataRow = (OrderedMap) table.get(pivotValue);
133
			if (metadataRow == null) {
134
				metadataRow = new OrderedMap();
135
			}
136 4872 leinfelder
137
			//get the values for this pivot
138
			for (int i = 1; i <= colCount; i++) {
139
				if (i != pivotCol) {
140
					String colName = rs.getMetaData().getColumnName(i);
141 4875 leinfelder
					String value = rs.getString(i);
142 4895 leinfelder
143 4876 leinfelder
					//clean up the value
144 4895 leinfelder
					value = cleanUp(value);
145
146 4874 leinfelder
					//do we include this column in the pivot?
147
					if (pivotAttributes.contains(colName)) {
148
						//annotate the column name with the pivot column value if not the id column
149
						if (i != idCol) {
150
							colName = pivotValue + "_" + colName;
151
						}
152
						row.put(colName, value);
153 4872 leinfelder
					}
154 4875 leinfelder
					else {
155 4885 leinfelder
						if (colName.startsWith(vocabNameCol) || colName.startsWith(vocabValueCol)) {
156
							//don't add it to the normal metadata
157
						}
158
						else {
159
							metadataRow.put(colName, value);
160
						}
161 4875 leinfelder
					}
162 4883 leinfelder
					//names
163
					if (colName.startsWith(vocabNameCol)) {
164
						List list = (List) vocabNames.get(pivotValue);
165
						if (list == null) {
166
							list = new ArrayList();
167
						}
168
						list.add(value);
169
						vocabNames.put(pivotValue, list);
170
						allVocabNames.add(value);
171
					}
172
					//values
173
					if (colName.startsWith(vocabValueCol)) {
174
						List list = (List) vocabValues.get(pivotValue);
175
						if (list == null) {
176
							list = new ArrayList();
177
						}
178
						list.add(value);
179
						vocabValues.put(pivotValue, list);
180
						allVocabValues.add(value);
181
					}
182 4872 leinfelder
				}
183
			}
184 4875 leinfelder
			//track the data columns - the values are junk
185 4872 leinfelder
			widestRow.putAll(row);
186
187
			//put the row back (or maybe it's the first time)
188 4875 leinfelder
			table.put(id, row);
189
190
			//put the metadata header back
191
			headerRows.put(pivotValue, metadataRow);
192
193 4872 leinfelder
		}
194
195 4883 leinfelder
		/** Construct the table structure for returning **/
196
197 4872 leinfelder
		//now make it into a list
198
		List retTable = new ArrayList();
199
200 4875 leinfelder
		//map keyed by metadata labels
201
		OrderedMap metadataHeaders = new OrderedMap();
202
203
		//do the data header - drives the other columns - based on widest entry
204 4872 leinfelder
		List header = new ArrayList(widestRow.keySet());
205 4875 leinfelder
206
		//do the metadata header rows (basically rotate them around)
207
		Iterator headerIter = header.iterator();
208 4886 leinfelder
		String lastValue = "";
209 4875 leinfelder
		while (headerIter.hasNext()) {
210
			String column = (String) headerIter.next();
211
			//get the pivotValue part of column name
212
			String pivotValue = column;
213
			try {
214
				pivotValue = column.substring(0, column.indexOf("_"));
215
			}
216
			catch (Exception e) {}
217
			//look up the row from the metadata - keyed by pivot value
218
			OrderedMap metadataRow = (OrderedMap) headerRows.get(pivotValue);
219
			if (metadataRow != null) {
220
				//go through the values
221
				Iterator metadataIter = metadataRow.keySet().iterator();
222
				while (metadataIter.hasNext()) {
223
					String key = (String) metadataIter.next();
224
					String value = (String) metadataRow.get(key);
225
					OrderedMap newMetadataRow = (OrderedMap) metadataHeaders.get(key);
226
					if (newMetadataRow == null) {
227
						newMetadataRow = new OrderedMap();
228
					}
229 4886 leinfelder
					//if it's the same as the last one, just use null value
230
					if (lastValue.equals(pivotValue)) {
231
						value = null;
232
					}
233 4875 leinfelder
					newMetadataRow.put(column, value);
234
					metadataHeaders.put(key, newMetadataRow);
235
				}
236
			}
237 4886 leinfelder
238
			lastValue = pivotValue;
239
240 4875 leinfelder
		}
241
242
		//make metadata rows as list/arrays on the reteurn table
243
		Iterator metadataLabelIter = metadataHeaders.keySet().iterator();
244
		while (metadataLabelIter.hasNext()) {
245
			String label = (String) metadataLabelIter.next();
246
			OrderedMap row = (OrderedMap) metadataHeaders.get(label);
247
			List rowValues = new ArrayList(row.values());
248
			rowValues.add(0, label);
249 4892 leinfelder
			if (twoColumnMetadata) {
250
				//add extra column
251
				rowValues.add(1, null);
252
			}
253 4875 leinfelder
			retTable.add(rowValues.toArray(new String[0]));
254
		}
255
256 4883 leinfelder
		//create the special vocab matrix rows
257
		List vocabTable = new ArrayList();
258
		List uniqueVocabs = new ArrayList();
259
		for (int i = 0; i < allVocabNames.size(); i++) {
260
			List vocabRow = new ArrayList();
261
			String vocabName = (String) allVocabNames.get(i);
262
			String vocabValue = (String) allVocabValues.get(i);
263
			String key = vocabName + "/" + vocabValue;
264
			//check if we've processed this already, skip if so
265
			if (uniqueVocabs.contains(key)) {
266
				continue;
267
			}
268 4892 leinfelder
			uniqueVocabs.add(key);
269
			if (twoColumnMetadata) {
270
				vocabRow.add(vocabName);
271
				vocabRow.add(vocabValue);
272
			}
273
			else {
274
				vocabRow.add(key);
275
			}
276 4883 leinfelder
			//go through the questions now, again
277 4898 leinfelder
			String lastPivotValue = "";
278 4883 leinfelder
			headerIter = header.iterator();
279
			while (headerIter.hasNext()) {
280
				String column = (String) headerIter.next();
281
				//get the pivotValue part of column name if it exists
282
				String pivotValue = null;
283
				try {
284
					pivotValue = column.substring(0, column.indexOf("_"));
285
				}
286
				catch (Exception e) {}
287
				if (pivotValue == null) {
288
					continue;
289
				}
290 4898 leinfelder
				//check to not duplicate values
291
				if (pivotValue.equals(lastPivotValue)) {
292
					vocabRow.add(null);
293 4883 leinfelder
				}
294
				else {
295 4898 leinfelder
					//check to see if this question has that keyword
296
					List names = (List) vocabNames.get(pivotValue);
297
					List values = (List) vocabValues.get(pivotValue);
298 5087 leinfelder
					String containsVocabItem = "false";
299
					if (names != null) {
300
						int vocabNameIndex = names.indexOf(vocabName);
301
						int vocabValueIndex = values.indexOf(vocabValue);
302
						// contains the vocab and the value _somewhere_
303
						if (vocabNameIndex > -1 && vocabValueIndex > -1) {
304
							containsVocabItem = "true";
305
						}
306 4898 leinfelder
					}
307 5087 leinfelder
					vocabRow.add(containsVocabItem);
308 4883 leinfelder
				}
309 4898 leinfelder
				lastPivotValue = pivotValue;
310 4883 leinfelder
			}
311
			//put the row on
312
			vocabTable.add(vocabRow.toArray(new String[0]));
313
		}
314
315
		//put the vocab matrix on the table
316
		retTable.addAll(vocabTable);
317
318 4892 leinfelder
		if (twoColumnMetadata) {
319
			//add column to data header row
320
			header.add(1, null);
321
		}
322 4897 leinfelder
323
		//replace the "studentId" label
324
		int temp = header.indexOf("studentid");
325
		if (header.remove(temp) != null) {
326
			header.add(temp, "recordNum");
327
		}
328
329 4875 leinfelder
		//put the data header row on the table
330 4872 leinfelder
		retTable.add(header.toArray(new String[0]));
331
332 4875 leinfelder
		//now the value rows in the table
333 4872 leinfelder
		Iterator rowIter = table.values().iterator();
334
		int rowCount = 1;
335
		while (rowIter.hasNext()) {
336
			OrderedMap rowMap = (OrderedMap) rowIter.next();
337
			List row = new ArrayList();
338
			//iterate over the widest row's columns
339
			Iterator columnIter = widestRow.keySet().iterator();
340
			while (columnIter.hasNext()) {
341
				Object key = columnIter.next();
342
				Object value = rowMap.get(key);
343
				//hide the value used for Ids - just increment row
344
				if (key.equals(idColName) && omitIdValues) {
345
					value = String.valueOf(rowCount);
346
				}
347
				row.add(value);
348
			}
349
			rowCount++;
350 4892 leinfelder
			if (twoColumnMetadata) {
351
				//add extra column
352
				row.add(1, null);
353
			}
354 4872 leinfelder
			retTable.add(row.toArray(new String[0]));
355
		}
356
357
		return retTable;
358
	}
359
%><%!
360 4895 leinfelder
	private String cleanUp(String value) {
361
		if (value != null) {
362
			value = value.replaceAll("\n", " ");
363
			value = value.replaceAll("\\s+", " ");
364
			value = value.replaceAll("<html>", " ");
365
			value = value.replaceAll("</html>", " ");
366
			value = value.replaceAll("<head>", " ");
367
			value = value.replaceAll("</head>", " ");
368
			value = value.replaceAll("<body>", " ");
369
			value = value.replaceAll("</body>", " ");
370 5063 leinfelder
			//translate any ecogrid urls
371
			value = convertEcogridURL(value);
372 4895 leinfelder
		}
373
		return value;
374
	}
375
%><%!
376 5063 leinfelder
private String convertEcogridURL(String value) {
377
	if (value != null) {
378
		String prefix = "ecogrid://knb/";
379
		if (value.startsWith(prefix)) {
380
			//String docid = value.substring(prefix.length(), value.length());
381
			// TODO make URL
382
			String contextURL = "";
383
			try {
384
				contextURL = SystemUtil.getContextURL();
385
			}
386
			catch (PropertyNotFoundException pnfe) {
387
				//do nothing
388
			}
389
			value = value.replaceFirst(prefix, contextURL + "/metacat?action=read&docid=");
390
		}
391
	}
392
	return value;
393
}
394
%><%!
395 4872 leinfelder
	private void handleDataquery(
396
			Hashtable<String, String[]> params,
397
	        HttpServletResponse response,
398
	        String sessionId) throws PropertyNotFoundException, IOException {
399
400
		DataQuery dq = null;
401
		if (sessionId != null) {
402
			dq = new DataQuery(sessionId);
403
		}
404
		else {
405
			dq = new DataQuery();
406
		}
407
408
		String dataqueryXML = (params.get("dataquery"))[0];
409
410
		ResultSet rs = null;
411
		try {
412
			rs = dq.executeQuery(dataqueryXML);
413
		} catch (Exception e) {
414
			//probably need to do something here
415
			e.printStackTrace();
416
			return;
417
		}
418
419
		//process the result set
420
		String qformat = "csv";
421
		String[] temp = params.get("qformat");
422
		if (temp != null && temp.length > 0) {
423
			qformat = temp[0];
424
		}
425
		String fileName = "query-results." + qformat;
426
427
		boolean transpose = false;
428
		temp = params.get("transpose");
429
		if (temp != null && temp.length > 0) {
430
			transpose = Boolean.parseBoolean(temp[0]);
431
		}
432
		int observation = 0;
433
		temp = params.get("observation");
434
		if (temp != null && temp.length > 0) {
435
			observation = Integer.parseInt(temp[0]);
436
		}
437
		int pivot = 0;
438
		temp = params.get("pivot");
439
		if (temp != null && temp.length > 0) {
440
			pivot = Integer.parseInt(temp[0]);
441
		}
442 5081 leinfelder
		String[] pivotColumns = null;
443
		temp = params.get("pivotColumns");
444
		if (temp != null && temp.length > 0) {
445
			pivotColumns = temp;
446
		}
447 4872 leinfelder
448
		//get the results as csv file
449
		if (qformat != null && qformat.equalsIgnoreCase("csv")) {
450
			response.setContentType("text/csv");
451
			//response.setContentType("application/csv");
452
	        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
453
454
			Writer writer = new OutputStreamWriter(response.getOutputStream());
455 4876 leinfelder
			//CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
456
			CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER);
457 4872 leinfelder
			try {
458
				if (transpose) {
459 4874 leinfelder
					List pivotCols = new ArrayList();
460 5081 leinfelder
					if (pivotColumns != null) {
461
						for (int i = 0; i < pivotColumns.length; i++) {
462
							pivotCols.add(pivotColumns[i]);
463
						}
464
					} else {
465
						pivotCols.add("studentid");
466
						pivotCols.add("score");
467
						pivotCols.add("response");
468
						pivotCols.add("responsefile");
469
					}
470 4874 leinfelder
					List transposedTable = transpose(rs, observation, pivot, pivotCols, true);
471 4872 leinfelder
					csv.writeAll(transposedTable);
472
				} else {
473 4896 leinfelder
					List processedTable = processResultsSet(rs, 3, "recordNum");
474 4895 leinfelder
					csv.writeAll(processedTable);
475 4872 leinfelder
				}
476
477
				csv.flush();
478
				response.flushBuffer();
479
480
				rs.close();
481
482
			} catch (SQLException e) {
483
				e.printStackTrace();
484
			}
485
486
			return;
487
		}
488
489
	}
490
%>