1 package org.paneris.jal.controller;
2
3 import java.io.BufferedReader;
4 import java.io.File;
5 import java.io.FileReader;
6 import java.sql.Connection;
7 import java.sql.ResultSet;
8 import java.sql.Statement;
9 import java.util.Enumeration;
10 import java.util.Vector;
11
12 import org.paneris.jal.model.DBConnectionManager;
13 import org.paneris.jal.model.DDField;
14 import org.paneris.jal.model.DDRecord;
15 import org.paneris.jal.model.SystemProperties;
16 import org.paneris.user.model.User;
17 import org.paneris.util.CSVStringTokenizer;
18 import org.paneris.util.ExtendedHash;
19 import org.paneris.util.ExtensionFilter;
20 import org.webmacro.Template;
21 import org.webmacro.servlet.HandlerException;
22 import org.webmacro.servlet.WMServlet;
23 import org.webmacro.servlet.WebContext;
24
25
26
27
28 public class Upload extends WMServlet {
29
30 private static final long serialVersionUID = 1L;
31 private DBConnectionManager connMgr = DBConnectionManager.getInstance();
32 private Connection conn;
33 private String db;
34 private String table;
35
36
37
38
39
40
41 public Template handle(WebContext context) throws HandlerException {
42
43
44
45
46 db = (String) context.getForm("db");
47 if (db == null) {
48 throw new HandlerException("No datasource specified.");
49 } else {
50 conn = connMgr.getConnection("Upload",db);
51 if (conn == null) throw new HandlerException("Can't get connection " + db);
52 }
53 context.put("db",db);
54 String path = "";
55 try {
56 SystemProperties sp = new SystemProperties(db);
57 path = sp.getProperty("uploaddir");
58 } catch (Exception e) {
59 path = connMgr.getUploadDir();
60 }
61 context.put("path",path);
62
63
64
65
66 table = (String) context.getForm("table");
67 String filename = (String) context.getForm("file");
68 boolean append = (context.getForm("append") != null);
69
70
71
72 String templateName = null;
73 try {
74 templateName = User.checkLoggedIn("controller", this.getClass().getName(), context, " access the Database Administration pages.");
75 } catch (Exception e) {
76 throw new HandlerException("Could not get user:" + e.toString());
77 }
78 if (templateName == null) {
79 templateName = (String) context.getForm("wmtemplate");
80
81
82
83 if (table == null) {
84 if (templateName == null) templateName = "jal/view/Upload.wm";
85 context.put("wmtemplate",templateName);
86 try {
87 context.put("tables", listDatadictionaryTables());
88 context.put("files", listCSVFiles(path));
89 } catch (Exception e) {
90 throw new HandlerException(e.toString());
91 }
92 }
93
94
95
96 else {
97 if (templateName == null) templateName = "jal/view/UploadResults.wm";
98 context.put("wmtemplate",templateName);
99 Statement s;
100 DDRecord ddTable;
101 try {
102 s = conn.createStatement();
103 ddTable = new DDRecord(db, table);
104 } catch (Exception e) {
105 throw new HandlerException(e.toString());
106 }
107
108
109
110
111 String file = new String (path + filename);
112 int lines = 0;
113 int records = 0;
114 BufferedReader br;
115 Vector headerVector;
116 try {
117 br = new BufferedReader(new FileReader(file));
118 headerVector = parseCSVLine(br.readLine());
119 lines++;
120 } catch (Exception e) {
121 throw new HandlerException(e.toString() + ", line number=" + lines);
122 }
123
124
125
126
127 boolean validFile = true;
128 if (headerVector.size() == ddTable.size()) {
129 String errorFields = new String();
130 for (Enumeration en = headerVector.elements() ; en.hasMoreElements() ;) {
131 String fieldname = (String) en.nextElement();
132 if (!ddTable.containsKey(fieldname)) {
133 errorFields = errorFields.concat(fieldname + ", ");
134 }
135 }
136 if (errorFields.length() > 0) {
137 context.put("error", "Some fields in the import file do not match the fields in the " + table + " (" + errorFields + ")");
138 validFile = false;
139 }
140 }
141 else {
142 context.put("error", "The number of fields in the import file header does not match the number of fields in the datadictionary. (import: " + Integer.toString(headerVector.size()) + ", datadictionary: " + Integer.toString(ddTable.size())+ ").");
143 validFile = false;
144 }
145
146
147
148
149 if (validFile) {
150 if (!append) {
151 try {
152 s.executeUpdate("DELETE FROM " + table);
153 } catch (Exception e) {
154 throw new RuntimeException(e.toString());
155 }
156 }
157
158
159 try {
160 s.executeUpdate("DROP SEQUENCE " + table + "_id_seq");
161 } catch (Exception e) {
162 throw new RuntimeException(e.toString());
163 }
164
165
166
167 String line = new String();
168 try {
169 while ((line = br.readLine()) != null) {
170 lines++;
171 Vector lineVector = parseCSVLine(line);
172
173 if (!lineVector.isEmpty()) {
174 ExtendedHash nameValues = new ExtendedHash(headerVector,lineVector);
175 DDRecord record = new DDRecord(db,table);
176 for (Enumeration en = record.elements() ; en.hasMoreElements() ;) {
177 DDField field = (DDField) en.nextElement();
178 field.setValue((String) nameValues.get(field.getMetaData().getFieldName()));
179
180 }
181 DDField field = (DDField) record.get("id");
182 ResultSet rs = s.executeQuery("SELECT id FROM " + table + " WHERE id = " + field.getDisplayValue());
183 if (rs.next()) {
184 record.write();
185 } else {
186 record.write(true);
187 }
188 records++;
189
190
191
192
193
194
195 }
196 }
197 } catch (Exception e) {
198
199 createSequence();
200 throw new HandlerException(e.toString() + ", line number=" + lines);
201 }
202 context.put("records",Integer.toString(records));
203
204
205
206
207 createSequence();
208 }
209 }
210 }
211
212
213
214
215
216 connMgr.freeConnection(db, conn);
217
218 try {
219
220 return (Template) context.getBroker().get("template",templateName);
221 } catch (Exception e) {
222 throw new HandlerException("Could not locate template: " + templateName);
223 }
224 }
225
226 private Vector parseCSVLine (String line) {
227 String delim = ",";
228 CSVStringTokenizer st = new CSVStringTokenizer(line,delim);
229 Vector lineVector = new Vector();
230 while (st.hasMoreTokens()) {
231 String token = new String(st.nextToken());
232
233
234 if (token.length() > 0) {
235
236 if (token.charAt(0) == '"') {
237 while ((token.charAt(token.length()-1) != '"') && (st.hasMoreTokens())) {
238 token = token.concat(delim + st.nextToken());
239 }
240 }
241
242 if ((token.charAt(0) == '"') && (token.charAt(token.length()-1) == '"')){
243 token = token.substring(1,token.length()-1);
244 }
245 }
246 lineVector.addElement(token);
247 }
248 return lineVector;
249 }
250
251
252
253
254 private Vector listDatadictionaryTables() throws Exception {
255 Statement s = conn.createStatement();
256 Vector rt = new Vector();
257 String sqlString = new String("SELECT * FROM datadictionarytables ORDER BY displayorder");
258 ResultSet rs = s.executeQuery(sqlString);
259 while (rs.next()) {
260 rt.addElement(new DDRecord(db, "datadictionarytables", rs));
261 }
262 return rt;
263 }
264
265 private String[] listCSVFiles(String path) {
266 File csvdir = new File(path);
267 ExtensionFilter filter = new ExtensionFilter("csv");
268 return csvdir.list(filter);
269 }
270
271
272
273
274
275
276 private void createSequence() throws HandlerException {
277 int max = 1;
278
279 try {
280 Statement s = conn.createStatement();
281 ResultSet rs = s.executeQuery("SELECT MAX(id) FROM " + table);
282 if (rs.next()) {
283 max = rs.getInt(1);
284 }
285 } catch (Exception e) {
286 throw new HandlerException(e.toString());
287 }
288 if (max < 1) {
289 max = 1;
290 }
291 try {
292 Statement s = conn.createStatement();
293 s.executeUpdate("CREATE SEQUENCE " + table + "_id_seq START " + Integer.toString(max));
294 } catch (Exception e) {
295 throw new RuntimeException(e.toString());
296 }
297 }
298
299 }
300