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 5101 leinfelder
		//make sure there are no "?column?" headers
97
		for (int i = 0; i < columnHeaders.size(); i++) {
98
			String columnName = (String) columnHeaders.get(i);
99
			if (columnName.equals("?column?")) {
100
				String previousColumn = (String) columnHeaders.get(i-1);
101
				String[] previousParts = previousColumn.split("_");
102
				int previousCount = Integer.parseInt(previousParts[1]);
103
				columnName = previousParts[0]  + "_"+ (previousCount + 1);
104
				columnHeaders.set(i, columnName);
105
			}
106
		}
107 4895 leinfelder
		retTable.add(0, columnHeaders.toArray(new String[0]));
108
		return retTable;
109
	}
110
%><%!
111 4874 leinfelder
	private List transpose(ResultSet rs, int idCol, int pivotCol, List pivotAttributes, boolean omitIdValues) throws SQLException {
112 4875 leinfelder
		//map keyed by id column - data
113 4872 leinfelder
		OrderedMap table = new OrderedMap();
114 4875 leinfelder
		//track all the data columns
115 4872 leinfelder
		OrderedMap widestRow = new OrderedMap();
116 4875 leinfelder
		//map keyed by the pivot column - metadata
117
		OrderedMap headerRows = new OrderedMap();
118
119 4883 leinfelder
		//vocab columns
120
		String vocabNameCol = "qmetadatavocabulary";
121
		String vocabValueCol = "qmetadatavalue";
122
		//maps for the vocab lists
123
		OrderedMap vocabNames = new OrderedMap();
124
		OrderedMap vocabValues = new OrderedMap();
125
		//all vocab names/values
126
		List allVocabNames = new ArrayList();
127
		List allVocabValues = new ArrayList();
128 5087 leinfelder
		boolean twoColumnMetadata = false;
129 4883 leinfelder
130 4872 leinfelder
		int colCount = rs.getMetaData().getColumnCount();
131
		String idColName = rs.getMetaData().getColumnName(idCol);
132
133
		while (rs.next()) {
134
			String id = rs.getString(idCol);
135
			String pivotValue = rs.getString(pivotCol);
136
137 4875 leinfelder
			//look up the data row we are working on
138 4872 leinfelder
			OrderedMap row = (OrderedMap) table.get(id);
139
			if (row == null) {
140
				row = new OrderedMap();
141
			}
142 4875 leinfelder
			//look up the metadata row we are working on
143
			OrderedMap metadataRow = (OrderedMap) table.get(pivotValue);
144
			if (metadataRow == null) {
145
				metadataRow = new OrderedMap();
146
			}
147 4872 leinfelder
148
			//get the values for this pivot
149
			for (int i = 1; i <= colCount; i++) {
150
				if (i != pivotCol) {
151
					String colName = rs.getMetaData().getColumnName(i);
152 5102 leinfelder
153
					//check for "missing" headers in the qchoice
154
					/*
155
					if (colName.equals("?column?")) {
156
						String previousColumn = rs.getMetaData().getColumnName(i-1);
157
						if (previousColumn.startsWith("qchoice")) {
158
							String[] previousParts = previousColumn.split("_");
159
							int previousCount = 0;
160
							if (previousParts.length == 2) {
161
								previousCount = Integer.parseInt(previousParts[1]);
162
							}
163
							colName = previousParts[0]  + "_"+ (previousCount + 1);
164
						}
165
					}
166
					*/
167
168 4875 leinfelder
					String value = rs.getString(i);
169 4895 leinfelder
170 4876 leinfelder
					//clean up the value
171 4895 leinfelder
					value = cleanUp(value);
172
173 4874 leinfelder
					//do we include this column in the pivot?
174
					if (pivotAttributes.contains(colName)) {
175
						//annotate the column name with the pivot column value if not the id column
176
						if (i != idCol) {
177
							colName = pivotValue + "_" + colName;
178
						}
179
						row.put(colName, value);
180 4872 leinfelder
					}
181 4875 leinfelder
					else {
182 4885 leinfelder
						if (colName.startsWith(vocabNameCol) || colName.startsWith(vocabValueCol)) {
183
							//don't add it to the normal metadata
184
						}
185
						else {
186
							metadataRow.put(colName, value);
187
						}
188 4875 leinfelder
					}
189 4883 leinfelder
					//names
190
					if (colName.startsWith(vocabNameCol)) {
191
						List list = (List) vocabNames.get(pivotValue);
192
						if (list == null) {
193
							list = new ArrayList();
194
						}
195
						list.add(value);
196
						vocabNames.put(pivotValue, list);
197
						allVocabNames.add(value);
198
					}
199
					//values
200
					if (colName.startsWith(vocabValueCol)) {
201
						List list = (List) vocabValues.get(pivotValue);
202
						if (list == null) {
203
							list = new ArrayList();
204
						}
205
						list.add(value);
206
						vocabValues.put(pivotValue, list);
207
						allVocabValues.add(value);
208
					}
209 4872 leinfelder
				}
210
			}
211 4875 leinfelder
			//track the data columns - the values are junk
212 4872 leinfelder
			widestRow.putAll(row);
213
214
			//put the row back (or maybe it's the first time)
215 4875 leinfelder
			table.put(id, row);
216
217
			//put the metadata header back
218
			headerRows.put(pivotValue, metadataRow);
219
220 4872 leinfelder
		}
221
222 4883 leinfelder
		/** Construct the table structure for returning **/
223
224 4872 leinfelder
		//now make it into a list
225
		List retTable = new ArrayList();
226
227 4875 leinfelder
		//map keyed by metadata labels
228
		OrderedMap metadataHeaders = new OrderedMap();
229
230
		//do the data header - drives the other columns - based on widest entry
231 4872 leinfelder
		List header = new ArrayList(widestRow.keySet());
232 4875 leinfelder
233
		//do the metadata header rows (basically rotate them around)
234
		Iterator headerIter = header.iterator();
235 4886 leinfelder
		String lastValue = "";
236 4875 leinfelder
		while (headerIter.hasNext()) {
237
			String column = (String) headerIter.next();
238
			//get the pivotValue part of column name
239
			String pivotValue = column;
240
			try {
241
				pivotValue = column.substring(0, column.indexOf("_"));
242
			}
243
			catch (Exception e) {}
244
			//look up the row from the metadata - keyed by pivot value
245
			OrderedMap metadataRow = (OrderedMap) headerRows.get(pivotValue);
246
			if (metadataRow != null) {
247
				//go through the values
248
				Iterator metadataIter = metadataRow.keySet().iterator();
249
				while (metadataIter.hasNext()) {
250
					String key = (String) metadataIter.next();
251
					String value = (String) metadataRow.get(key);
252
					OrderedMap newMetadataRow = (OrderedMap) metadataHeaders.get(key);
253
					if (newMetadataRow == null) {
254
						newMetadataRow = new OrderedMap();
255
					}
256 4886 leinfelder
					//if it's the same as the last one, just use null value
257
					if (lastValue.equals(pivotValue)) {
258
						value = null;
259
					}
260 4875 leinfelder
					newMetadataRow.put(column, value);
261
					metadataHeaders.put(key, newMetadataRow);
262
				}
263
			}
264 4886 leinfelder
265
			lastValue = pivotValue;
266
267 4875 leinfelder
		}
268
269
		//make metadata rows as list/arrays on the reteurn table
270
		Iterator metadataLabelIter = metadataHeaders.keySet().iterator();
271
		while (metadataLabelIter.hasNext()) {
272
			String label = (String) metadataLabelIter.next();
273
			OrderedMap row = (OrderedMap) metadataHeaders.get(label);
274
			List rowValues = new ArrayList(row.values());
275
			rowValues.add(0, label);
276 4892 leinfelder
			if (twoColumnMetadata) {
277
				//add extra column
278
				rowValues.add(1, null);
279
			}
280 4875 leinfelder
			retTable.add(rowValues.toArray(new String[0]));
281
		}
282
283 4883 leinfelder
		//create the special vocab matrix rows
284
		List vocabTable = new ArrayList();
285
		List uniqueVocabs = new ArrayList();
286
		for (int i = 0; i < allVocabNames.size(); i++) {
287
			List vocabRow = new ArrayList();
288
			String vocabName = (String) allVocabNames.get(i);
289
			String vocabValue = (String) allVocabValues.get(i);
290
			String key = vocabName + "/" + vocabValue;
291
			//check if we've processed this already, skip if so
292
			if (uniqueVocabs.contains(key)) {
293
				continue;
294
			}
295 4892 leinfelder
			uniqueVocabs.add(key);
296
			if (twoColumnMetadata) {
297
				vocabRow.add(vocabName);
298
				vocabRow.add(vocabValue);
299
			}
300
			else {
301
				vocabRow.add(key);
302
			}
303 4883 leinfelder
			//go through the questions now, again
304 4898 leinfelder
			String lastPivotValue = "";
305 4883 leinfelder
			headerIter = header.iterator();
306
			while (headerIter.hasNext()) {
307
				String column = (String) headerIter.next();
308
				//get the pivotValue part of column name if it exists
309
				String pivotValue = null;
310
				try {
311
					pivotValue = column.substring(0, column.indexOf("_"));
312
				}
313
				catch (Exception e) {}
314
				if (pivotValue == null) {
315
					continue;
316
				}
317 4898 leinfelder
				//check to not duplicate values
318
				if (pivotValue.equals(lastPivotValue)) {
319
					vocabRow.add(null);
320 4883 leinfelder
				}
321
				else {
322 4898 leinfelder
					//check to see if this question has that keyword
323
					List names = (List) vocabNames.get(pivotValue);
324
					List values = (List) vocabValues.get(pivotValue);
325 5087 leinfelder
					String containsVocabItem = "false";
326
					if (names != null) {
327
						int vocabNameIndex = names.indexOf(vocabName);
328
						int vocabValueIndex = values.indexOf(vocabValue);
329
						// contains the vocab and the value _somewhere_
330
						if (vocabNameIndex > -1 && vocabValueIndex > -1) {
331
							containsVocabItem = "true";
332
						}
333 4898 leinfelder
					}
334 5087 leinfelder
					vocabRow.add(containsVocabItem);
335 4883 leinfelder
				}
336 4898 leinfelder
				lastPivotValue = pivotValue;
337 4883 leinfelder
			}
338
			//put the row on
339
			vocabTable.add(vocabRow.toArray(new String[0]));
340
		}
341
342
		//put the vocab matrix on the table
343
		retTable.addAll(vocabTable);
344
345 4892 leinfelder
		if (twoColumnMetadata) {
346
			//add column to data header row
347
			header.add(1, null);
348
		}
349 4897 leinfelder
350
		//replace the "studentId" label
351
		int temp = header.indexOf("studentid");
352
		if (header.remove(temp) != null) {
353
			header.add(temp, "recordNum");
354
		}
355
356 4875 leinfelder
		//put the data header row on the table
357 4872 leinfelder
		retTable.add(header.toArray(new String[0]));
358
359 4875 leinfelder
		//now the value rows in the table
360 4872 leinfelder
		Iterator rowIter = table.values().iterator();
361
		int rowCount = 1;
362
		while (rowIter.hasNext()) {
363
			OrderedMap rowMap = (OrderedMap) rowIter.next();
364
			List row = new ArrayList();
365
			//iterate over the widest row's columns
366
			Iterator columnIter = widestRow.keySet().iterator();
367
			while (columnIter.hasNext()) {
368
				Object key = columnIter.next();
369
				Object value = rowMap.get(key);
370
				//hide the value used for Ids - just increment row
371
				if (key.equals(idColName) && omitIdValues) {
372
					value = String.valueOf(rowCount);
373
				}
374
				row.add(value);
375
			}
376
			rowCount++;
377 4892 leinfelder
			if (twoColumnMetadata) {
378
				//add extra column
379
				row.add(1, null);
380
			}
381 4872 leinfelder
			retTable.add(row.toArray(new String[0]));
382
		}
383
384
		return retTable;
385
	}
386
%><%!
387 4895 leinfelder
	private String cleanUp(String value) {
388
		if (value != null) {
389
			value = value.replaceAll("\n", " ");
390
			value = value.replaceAll("\\s+", " ");
391
			value = value.replaceAll("<html>", " ");
392
			value = value.replaceAll("</html>", " ");
393
			value = value.replaceAll("<head>", " ");
394
			value = value.replaceAll("</head>", " ");
395
			value = value.replaceAll("<body>", " ");
396
			value = value.replaceAll("</body>", " ");
397 5063 leinfelder
			//translate any ecogrid urls
398
			value = convertEcogridURL(value);
399 4895 leinfelder
		}
400
		return value;
401
	}
402
%><%!
403 5063 leinfelder
private String convertEcogridURL(String value) {
404
	if (value != null) {
405
		String prefix = "ecogrid://knb/";
406
		if (value.startsWith(prefix)) {
407
			//String docid = value.substring(prefix.length(), value.length());
408
			// TODO make URL
409
			String contextURL = "";
410
			try {
411
				contextURL = SystemUtil.getContextURL();
412
			}
413
			catch (PropertyNotFoundException pnfe) {
414
				//do nothing
415
			}
416
			value = value.replaceFirst(prefix, contextURL + "/metacat?action=read&docid=");
417
		}
418
	}
419
	return value;
420
}
421
%><%!
422 4872 leinfelder
	private void handleDataquery(
423
			Hashtable<String, String[]> params,
424
	        HttpServletResponse response,
425
	        String sessionId) throws PropertyNotFoundException, IOException {
426
427
		DataQuery dq = null;
428
		if (sessionId != null) {
429
			dq = new DataQuery(sessionId);
430
		}
431
		else {
432
			dq = new DataQuery();
433
		}
434
435
		String dataqueryXML = (params.get("dataquery"))[0];
436
437
		ResultSet rs = null;
438
		try {
439
			rs = dq.executeQuery(dataqueryXML);
440
		} catch (Exception e) {
441
			//probably need to do something here
442
			e.printStackTrace();
443
			return;
444
		}
445
446
		//process the result set
447
		String qformat = "csv";
448
		String[] temp = params.get("qformat");
449
		if (temp != null && temp.length > 0) {
450
			qformat = temp[0];
451
		}
452
		String fileName = "query-results." + qformat;
453
454
		boolean transpose = false;
455
		temp = params.get("transpose");
456
		if (temp != null && temp.length > 0) {
457
			transpose = Boolean.parseBoolean(temp[0]);
458
		}
459
		int observation = 0;
460
		temp = params.get("observation");
461
		if (temp != null && temp.length > 0) {
462
			observation = Integer.parseInt(temp[0]);
463
		}
464
		int pivot = 0;
465
		temp = params.get("pivot");
466
		if (temp != null && temp.length > 0) {
467
			pivot = Integer.parseInt(temp[0]);
468
		}
469 5081 leinfelder
		String[] pivotColumns = null;
470
		temp = params.get("pivotColumns");
471
		if (temp != null && temp.length > 0) {
472
			pivotColumns = temp;
473
		}
474 4872 leinfelder
475
		//get the results as csv file
476
		if (qformat != null && qformat.equalsIgnoreCase("csv")) {
477
			response.setContentType("text/csv");
478
			//response.setContentType("application/csv");
479
	        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
480
481
			Writer writer = new OutputStreamWriter(response.getOutputStream());
482 4876 leinfelder
			//CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
483
			CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER);
484 4872 leinfelder
			try {
485
				if (transpose) {
486 4874 leinfelder
					List pivotCols = new ArrayList();
487 5081 leinfelder
					if (pivotColumns != null) {
488
						for (int i = 0; i < pivotColumns.length; i++) {
489
							pivotCols.add(pivotColumns[i]);
490
						}
491
					} else {
492
						pivotCols.add("studentid");
493
						pivotCols.add("score");
494
						pivotCols.add("response");
495
						pivotCols.add("responsefile");
496
					}
497 4874 leinfelder
					List transposedTable = transpose(rs, observation, pivot, pivotCols, true);
498 4872 leinfelder
					csv.writeAll(transposedTable);
499
				} else {
500 4896 leinfelder
					List processedTable = processResultsSet(rs, 3, "recordNum");
501 4895 leinfelder
					csv.writeAll(processedTable);
502 4872 leinfelder
				}
503
504
				csv.flush();
505
				response.flushBuffer();
506
507
				rs.close();
508
509
			} catch (SQLException e) {
510
				e.printStackTrace();
511
			}
512
513
			return;
514
		}
515
516
	}
517
%>