1 package org.paneris.jal.controller;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6 import java.util.Enumeration;
7 import java.util.Vector;
8
9 import org.paneris.jal.model.DBConnectionManager;
10 import org.paneris.jal.model.DDField;
11 import org.paneris.jal.model.DDRecord;
12 import org.paneris.jal.model.DataCache;
13 import org.paneris.jal.model.FieldMetaData;
14 import org.paneris.jal.model.LookupItem;
15 import org.paneris.jal.model.TableMetaData;
16 import org.webmacro.Template;
17 import org.webmacro.servlet.HandlerException;
18 import org.webmacro.servlet.PanerisPage;
19 import org.webmacro.servlet.WebContext;
20
21
22
23
24 public class LookupList extends PanerisPage {
25
26 private static final long serialVersionUID = 1L;
27 private DBConnectionManager connMgr = DBConnectionManager.getInstance();
28 private Connection conn;
29 private static final boolean debug = false;
30 private DataCache dataCache = DataCache.getInstance();
31
32
33
34
35
36
37
38
39
40 public Template handle(WebContext context) throws HandlerException {
41
42 String db = null;
43 String add = null;
44 String drill = null;
45 String drillup = null;
46 String table = null;
47 String field = null;
48 String returnfield = null;
49 String templateName = null;
50 String parentid = null;
51 String user = null;
52
53
54
55
56
57 if (db == null) db = (String) context.getForm("db");
58 if (db == null) throw new HandlerException("No datasource specified.");
59 else {
60 conn = connMgr.getConnection("Admin",db);
61 if (conn == null) throw new HandlerException("Can't get connection: "+db);
62 }
63 if (table==null) table = (String) context.getForm("table");
64 if (user==null) user = (String) context.getForm("user");
65 if (field==null) field = (String) context.getForm("field");
66 if (returnfield==null) returnfield = (String) context.getForm("returnfield");
67 if (drill==null) drill = (String) context.getForm("drill");
68 if (drillup==null) drillup = (String) context.getForm("drillup");
69 if (add==null) add = (String) context.getForm("add");
70 if (parentid==null) parentid = (String) context.getForm("parentid");
71 if (parentid==null) parentid = "0";
72 if (parentid.equals("")) parentid = "0";
73 if (templateName==null) templateName = (String) context.getForm("wmtemplate");
74 if (templateName==null) templateName = "jal/view/LookupList.wm";
75
76
77
78
79
80 try {
81 Statement s = conn.createStatement();
82
83 FieldMetaData typefield = null;
84 TableMetaData metaData = dataCache.getTableMetaData(db, table);
85
86 out:
87 for (Enumeration en = metaData.getFields().elements() ; en.hasMoreElements() ;) {
88 FieldMetaData fieldMetaData = (FieldMetaData) en.nextElement();
89 if (!fieldMetaData.getRelationshipTable().equals("") && !fieldMetaData.getRelationshipTable().equals(table)) {
90
91 typefield = fieldMetaData;
92 break out;
93 }
94 }
95 String sqlString = "";
96
97 int typeid = 0;
98 int typeorder = 0;
99 int parent = 0;
100
101 if (drill == null) {
102 if ((parentid == null) || (parentid.equals("0"))) {
103 parent = 0;
104 } else {
105 DDRecord rec = new DDRecord(db,table,new Integer(parentid));
106 parent = new Integer(rec.getFieldValue("parent")).intValue();
107 }
108 } else {
109 parent = new Integer(parentid).intValue();
110 }
111
112 DDRecord parentRec = new DDRecord(db,table,new Integer(parent));
113
114 int parentTypeOrder = 0;
115 if (parent != 0) {
116
117 DDRecord parentTypeRec = parentRec.getField(typefield.getFieldName()).getLookup();
118 context.put("previouslevel",parentTypeRec.getFieldValue("type"));
119 parentTypeOrder = new Integer(parentTypeRec.getFieldValue("typeorder")).intValue();
120 }
121
122 sqlString = "SELECT id, type, typeorder FROM " + typefield.getRelationshipTable();
123 sqlString += " WHERE typeorder > " + parentTypeOrder + " ORDER BY typeorder";
124 if (debug) System.err.println(sqlString);
125 ResultSet rs = s.executeQuery(sqlString);
126 if (rs.next()) {
127 typeid = rs.getInt(1);
128 context.put("thislevel",rs.getString(2));
129 typeorder = rs.getInt(3);
130 }
131
132 sqlString = "SELECT type FROM " + typefield.getRelationshipTable();
133 sqlString += " WHERE typeorder > " + typeorder + " ORDER BY typeorder";
134 rs = s.executeQuery(sqlString);
135 if (debug) System.err.println(sqlString);
136 if (rs.next()) {
137 context.put("nextlevel",rs.getString(1));
138 }
139
140
141 if (add != null) {
142 DDRecord lookup = new DDRecord(db,table,new Integer(0));
143 lookup.setFieldValue(field,(String) context.getForm("new"));
144 lookup.setFieldValue("type",typeid+"");
145 lookup.setFieldValue("parent",parent+"");
146 if (user != null) {
147 try {
148 DDField userField = (DDField) lookup.get("lastamendeduser");
149 if (userField != null) {
150 userField.setValue(user);
151 }
152 } catch (Exception e) {
153 ;
154 }
155 }
156 lookup.write();
157 }
158
159 sqlString = "SELECT id, " + field + " FROM " + table + " WHERE parent = " + parent;
160 sqlString += " ORDER BY " + field;
161 DDRecord tableRS = new DDRecord(db,table);
162 if (debug) System.err.println(sqlString);
163 rs = s.executeQuery(sqlString);
164 Vector results = new Vector();
165 while(rs.next()) {
166 LookupItem i = new LookupItem(rs.getString(1), rs.getString(2));
167 results.addElement(i);
168 }
169
170 context.put("ddtable",tableRS);
171 context.put("results", results);
172 context.put("parentid",parent+"");
173 } catch (Exception e) {
174 throw new HandlerException("Could not build page: " + e.toString());
175 }
176 context.put("field", field);
177 context.put("returnfield", returnfield);
178 context.put("db",db);
179 context.put("table",table);
180 context.put("wmtemplate",templateName);
181 connMgr.freeConnection(db, conn);
182
183
184 try {
185 return (Template) context.getBroker().get("template",templateName);
186 } catch (Exception e) {
187 throw new HandlerException("Could not locate template: " + templateName);
188 }
189 }
190
191
192
193 }