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