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
		int colCount = rs.getMetaData().getColumnCount();
67
		String idColName = rs.getMetaData().getColumnName(idCol);
68
		
69
		while (rs.next()) {
70
			String id = rs.getString(idCol);
71
			String pivotValue = rs.getString(pivotCol);
72
			
73
			//look up the data row we are working on
74
			OrderedMap row = (OrderedMap) table.get(id);
75
			if (row == null) {
76
				row = new OrderedMap();
77
			}
78
			//look up the metadata row we are working on
79
			OrderedMap metadataRow = (OrderedMap) table.get(pivotValue);
80
			if (metadataRow == null) {
81
				metadataRow = new OrderedMap();
82
			}
83
			
84
			//get the values for this pivot
85
			for (int i = 1; i <= colCount; i++) {
86
				if (i != pivotCol) {
87
					String colName = rs.getMetaData().getColumnName(i);
88
					String value = rs.getString(i);
89
					//clean up the value
90
					if (value != null) {
91
						value = value.replaceAll("\n", " ");
92
						value = value.replaceAll("\\s+", " ");
93
						value = value.replaceAll("<html>", " ");
94
						value = value.replaceAll("</html>", " ");
95
						value = value.replaceAll("<head>", " ");
96
						value = value.replaceAll("</head>", " ");
97
						value = value.replaceAll("<body>", " ");
98
						value = value.replaceAll("</body>", " ");
99
					}
100
					//do we include this column in the pivot?
101
					if (pivotAttributes.contains(colName)) {
102
						//annotate the column name with the pivot column value if not the id column
103
						if (i != idCol) {
104
							colName = pivotValue + "_" + colName;
105
						}
106
						row.put(colName, value);
107
					}
108
					else {
109
						metadataRow.put(colName, value);
110
					}
111
				}
112
			}
113
			//track the data columns - the values are junk
114
			widestRow.putAll(row);
115
			
116
			//put the row back (or maybe it's the first time)
117
			table.put(id, row);
118
			
119
			//put the metadata header back
120
			headerRows.put(pivotValue, metadataRow);
121
			
122
		}
123
		
124
		//now make it into a list
125
		List retTable = new ArrayList();
126
		
127
		//map keyed by metadata labels
128
		OrderedMap metadataHeaders = new OrderedMap();
129
		
130
		//do the data header - drives the other columns - based on widest entry
131
		List header = new ArrayList(widestRow.keySet());
132
		
133
		//do the metadata header rows (basically rotate them around)
134
		Iterator headerIter = header.iterator();
135
		while (headerIter.hasNext()) {
136
			String column = (String) headerIter.next();
137
			//get the pivotValue part of column name
138
			String pivotValue = column;
139
			try {
140
				pivotValue = column.substring(0, column.indexOf("_"));
141
			}
142
			catch (Exception e) {}
143
			//look up the row from the metadata - keyed by pivot value
144
			OrderedMap metadataRow = (OrderedMap) headerRows.get(pivotValue);
145
			if (metadataRow != null) {
146
				//go through the values
147
				Iterator metadataIter = metadataRow.keySet().iterator();
148
				while (metadataIter.hasNext()) {
149
					String key = (String) metadataIter.next();
150
					String value = (String) metadataRow.get(key);
151
					OrderedMap newMetadataRow = (OrderedMap) metadataHeaders.get(key);
152
					if (newMetadataRow == null) {
153
						newMetadataRow = new OrderedMap();
154
					}
155
					newMetadataRow.put(column, value);
156
					metadataHeaders.put(key, newMetadataRow);
157
				}
158
			}
159
		}
160
		
161
		//make metadata rows as list/arrays on the reteurn table
162
		Iterator metadataLabelIter = metadataHeaders.keySet().iterator();
163
		while (metadataLabelIter.hasNext()) {
164
			String label = (String) metadataLabelIter.next();
165
			OrderedMap row = (OrderedMap) metadataHeaders.get(label);
166
			List rowValues = new ArrayList(row.values());
167
			rowValues.add(0, label);
168
			retTable.add(rowValues.toArray(new String[0]));
169
		}
170
		
171
		//put the data header row on the table
172
		retTable.add(header.toArray(new String[0]));
173
		
174
		//now the value rows in the table
175
		Iterator rowIter = table.values().iterator();
176
		int rowCount = 1;
177
		while (rowIter.hasNext()) {
178
			OrderedMap rowMap = (OrderedMap) rowIter.next();
179
			List row = new ArrayList();
180
			//iterate over the widest row's columns
181
			Iterator columnIter = widestRow.keySet().iterator();
182
			while (columnIter.hasNext()) {
183
				Object key = columnIter.next();
184
				Object value = rowMap.get(key);
185
				//hide the value used for Ids - just increment row
186
				if (key.equals(idColName) && omitIdValues) {
187
					value = String.valueOf(rowCount);
188
				}
189
				row.add(value);
190
			}
191
			rowCount++;
192
			retTable.add(row.toArray(new String[0]));
193
		}
194
		
195
		return retTable;
196
	}
197
%><%!
198
	private void handleDataquery(
199
			Hashtable<String, String[]> params,
200
	        HttpServletResponse response,
201
	        String sessionId) throws PropertyNotFoundException, IOException {
202
		
203
		DataQuery dq = null;
204
		if (sessionId != null) {
205
			dq = new DataQuery(sessionId);
206
		}
207
		else {
208
			dq = new DataQuery();
209
		}
210
		
211
		String dataqueryXML = (params.get("dataquery"))[0];
212
	
213
		ResultSet rs = null;
214
		try {
215
			rs = dq.executeQuery(dataqueryXML);
216
		} catch (Exception e) {
217
			//probably need to do something here
218
			e.printStackTrace();
219
			return;
220
		}
221
		
222
		//process the result set
223
		String qformat = "csv";
224
		String[] temp = params.get("qformat");
225
		if (temp != null && temp.length > 0) {
226
			qformat = temp[0];
227
		}
228
		String fileName = "query-results." + qformat;
229
		
230
		boolean transpose = false;
231
		temp = params.get("transpose");
232
		if (temp != null && temp.length > 0) {
233
			transpose = Boolean.parseBoolean(temp[0]);
234
		}
235
		int observation = 0;
236
		temp = params.get("observation");
237
		if (temp != null && temp.length > 0) {
238
			observation = Integer.parseInt(temp[0]);
239
		}
240
		int pivot = 0;
241
		temp = params.get("pivot");
242
		if (temp != null && temp.length > 0) {
243
			pivot = Integer.parseInt(temp[0]);
244
		}
245
		
246
		//get the results as csv file
247
		if (qformat != null && qformat.equalsIgnoreCase("csv")) {
248
			response.setContentType("text/csv");
249
			//response.setContentType("application/csv");
250
	        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
251
	        
252
			Writer writer = new OutputStreamWriter(response.getOutputStream());
253
			//CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
254
			CSVWriter csv = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER);
255
			try {
256
				if (transpose) {
257
					List pivotCols = new ArrayList();
258
					pivotCols.add("studentid");
259
					pivotCols.add("score");
260
					pivotCols.add("response");
261
					List transposedTable = transpose(rs, observation, pivot, pivotCols, true);
262
					csv.writeAll(transposedTable);
263
				} else {
264
					csv.writeAll(rs, true);
265
				}
266
				
267
				csv.flush();
268
				response.flushBuffer();
269
				
270
				rs.close();
271
				
272
			} catch (SQLException e) {
273
				e.printStackTrace();
274
			}
275
			
276
			return;
277
		}
278
		
279
	}
280
%>
(4-4/22)