1 package org.paneris.jal.controller;
2
3 import java.io.FileWriter;
4 import java.io.PrintWriter;
5 import java.sql.Connection;
6 import java.sql.ResultSet;
7 import java.sql.Statement;
8 import java.util.Enumeration;
9 import java.util.Vector;
10
11 import org.paneris.jal.model.CSVUtil;
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.FieldMetaData;
16 import org.paneris.jal.model.SystemProperties;
17 import org.paneris.user.model.User;
18 import org.paneris.util.JoinVector;
19 import org.webmacro.Template;
20 import org.webmacro.servlet.HandlerException;
21 import org.webmacro.servlet.WMServlet;
22 import org.webmacro.servlet.WebContext;
23
24
25
26
27
28
29 public class Export extends WMServlet {
30
31 private static final long serialVersionUID = 1L;
32 private DBConnectionManager connMgr = DBConnectionManager.getInstance();
33 private Connection conn;
34
35
36
37
38
39
40
41
42 public Template handle(WebContext context) throws HandlerException {
43
44
45
46
47 String db = (String) context.getForm("db");
48 String lookups = (String) context.getForm("lookups");
49 if (db == null) {
50 throw new HandlerException("No datasource specified.");
51 } else {
52 conn = connMgr.getConnection("Upload",db);
53 if (conn == null) throw new HandlerException("Can't get connection " + db);
54 }
55 context.put("db",db);
56 String path = "";
57 try {
58 SystemProperties sp = new SystemProperties(db);
59 path = sp.getProperty("exportdir");
60 } catch (Exception e) {
61 path = connMgr.getUploadDir();
62 }
63 context.put("path",path);
64
65
66
67
68 String table = (String) context.getForm("table");
69 String filename = (String) context.getForm("file");
70
71
72
73 String templateName = null;
74 try {
75 templateName = User.checkLoggedIn("controller", this.getClass().getName(), context, " access the Database Administration pages.");
76 } catch (Exception e) {
77 throw new HandlerException("Could not get user:" + e.toString());
78 }
79 if (templateName == null) {
80 templateName = (String) context.getForm("wmtemplate");
81
82
83
84 if (table == null) {
85 if (templateName == null) templateName = "jal/view/Export.wm";
86 context.put("wmtemplate",templateName);
87 try {
88 context.put("tables", listDatadictionaryTables(db));
89 } catch (Exception e) {
90 throw new HandlerException(e.toString());
91 }
92 }
93
94
95
96 else {
97 if (templateName == null) templateName = "jal/view/ExportResults.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 PrintWriter pw;
115 try {
116 pw = new PrintWriter(new FileWriter(file));
117 JoinVector headerVector = new JoinVector();
118 for (Enumeration en = ddTable.getMetaData().getFields().elements(); en.hasMoreElements() ;) {
119 headerVector.addElement(CSVUtil.escapeCSV(((FieldMetaData) en.nextElement()).getFieldName()));
120 }
121 pw.println(headerVector.join(","));
122 lines++;
123 } catch (Exception e) {
124 throw new HandlerException(e.toString() + ", line number=" + lines);
125 }
126
127
128
129
130 String sqlString = "SELECT id FROM " + table;
131 try {
132 ResultSet rs = s.executeQuery(sqlString);
133 while (rs.next()) {
134 DDRecord rec = new DDRecord(db,table,new Integer(rs.getInt(1)));
135 JoinVector lineVector = new JoinVector();
136 for (Enumeration en = rec.elements(); en.hasMoreElements() ;) {
137 DDField field = (DDField) en.nextElement();
138 String value = "";
139 if (lookups == null || field.getMetaData().getRelationshipTable().equals("")) {
140 value = CSVUtil.escapeCSV(field.getStringValue().trim());
141 } else {
142 value = field.getCSVValue().trim();
143 }
144 lineVector.addElement(value);
145 }
146 pw.println(lineVector.join(","));
147 lines++;
148 records++;
149 }
150 pw.close();
151 } catch (Exception e) {
152 throw new HandlerException(e.toString() + ", line number=" + lines);
153 }
154 context.put("records",Integer.toString(records));
155 }
156 }
157
158
159
160
161
162 connMgr.freeConnection(db, conn);
163
164 try {
165 return (Template) context.getBroker().get("template",templateName);
166 } catch (Exception e) {
167 throw new HandlerException("Could not locate template: " + templateName);
168 }
169 }
170
171
172
173
174 private Vector listDatadictionaryTables(String db) throws Exception {
175 Statement s = conn.createStatement();
176 Vector rt = new Vector();
177 String sqlString = new String("SELECT * FROM datadictionarytables ORDER BY displayorder");
178 ResultSet rs = s.executeQuery(sqlString);
179 while (rs.next()) {
180 rt.addElement(new DDRecord(db, "datadictionarytables", rs));
181 }
182 return rt;
183 }
184
185 }
186