View Javadoc

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   * Generate a popup window for a lookup list.
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       * Handle this request to generate a popup window for a lookup list
34       *
35       * @param context   the WebContext
36       * @return          a Template to be expanded
37       * @exception       org.webmacro.servlet.HandlerException Cannot Connect to DB
38       * @exception       org.webmacro.servlet.HandlerException Cannot find templates
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          // Read in parameters from the form
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          // Choose what  function to perform
78          // --------------------------------
79  
80          try {
81              Statement s = conn.createStatement();
82              // work out what field gives us the type
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                      // we have our field
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             // sort out out types
114             int parentTypeOrder = 0;
115             if (parent != 0) {
116                 // previous level
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             // this level
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             // next level
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             // add one in?
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                       ; //ignore this exception
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         // return the appropriate template
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 }