KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > openbravo > erpCommon > utility > TableSQLData


1 /*
2  *************************************************************************
3  * The contents of this file are subject to the Openbravo Public License
4  * Version 1.0 (the "License"), being the Mozilla Public License
5  * Version 1.1 with a permitted attribution clause; you may not use this
6  * file except in compliance with the License. You may obtain a copy of
7  * the License at http://www.openbravo.com/legal/license.html
8  * Software distributed under the License is distributed on an "AS IS"
9  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
10  * License for the specific language governing rights and limitations
11  * under the License.
12  * The Original Code is Openbravo ERP.
13  * The Initial Developer of the Original Code is Openbravo SL
14  * All portions are Copyright (C) 2001-2006 Openbravo SL
15  * All Rights Reserved.
16  * Contributor(s): ______________________________________.
17  ************************************************************************
18 */
19 package org.openbravo.erpCommon.utility;
20
21 import org.openbravo.database.ConnectionProvider;
22 import org.openbravo.data.FieldProvider;
23 import org.openbravo.base.secureApp.VariablesSecureApp;
24 import java.util.Hashtable JavaDoc;
25 import java.util.Vector JavaDoc;
26 import java.util.Properties JavaDoc;
27 import java.util.Enumeration JavaDoc;
28 import org.apache.log4j.Logger;
29
30
31 public class TableSQLData {
32   static Logger log4j = Logger.getLogger(TableSQLData.class);
33   private final String JavaDoc internalPrefix = "@@";
34   private static final String JavaDoc FIELD_CONCAT = " || ' - ' || ";
35   private static final String JavaDoc INACTIVE_DATA = "**";
36   private VariablesSecureApp vars;
37   private ConnectionProvider pool;
38   private Hashtable JavaDoc<String JavaDoc, String JavaDoc> parameters = new Hashtable JavaDoc<String JavaDoc, String JavaDoc>();
39   private Vector JavaDoc<Properties JavaDoc> structure = new Vector JavaDoc<Properties JavaDoc>();
40   private Vector JavaDoc<QueryParameterStructure> paramSelect = new Vector JavaDoc<QueryParameterStructure>();
41   private Vector JavaDoc<QueryParameterStructure> paramFrom = new Vector JavaDoc<QueryParameterStructure>();
42   private Vector JavaDoc<QueryParameterStructure> paramWhere = new Vector JavaDoc<QueryParameterStructure>();
43   private Vector JavaDoc<QueryParameterStructure> paramOrderBy = new Vector JavaDoc<QueryParameterStructure>();
44   private Vector JavaDoc<QueryParameterStructure> paramFilter = new Vector JavaDoc<QueryParameterStructure>();
45   private Vector JavaDoc<QueryParameterStructure> paramInternalFilter = new Vector JavaDoc<QueryParameterStructure>();
46   private Vector JavaDoc<QueryParameterStructure> paramInternalOrderBy = new Vector JavaDoc<QueryParameterStructure>();
47   private Vector JavaDoc<QueryFieldStructure> select = new Vector JavaDoc<QueryFieldStructure>();
48   private Vector JavaDoc<QueryFieldStructure> from = new Vector JavaDoc<QueryFieldStructure>();
49   private Vector JavaDoc<QueryFieldStructure> where = new Vector JavaDoc<QueryFieldStructure>();
50   private Vector JavaDoc<QueryFieldStructure> orderBy = new Vector JavaDoc<QueryFieldStructure>();
51   private Vector JavaDoc<QueryFieldStructure> filter = new Vector JavaDoc<QueryFieldStructure>();
52   private Vector JavaDoc<QueryFieldStructure> internalFilter = new Vector JavaDoc<QueryFieldStructure>();
53   private Vector JavaDoc<QueryFieldStructure> internalOrderBy = new Vector JavaDoc<QueryFieldStructure>();
54   private Vector JavaDoc<String JavaDoc> orderByPosition = new Vector JavaDoc<String JavaDoc>();
55   private Vector JavaDoc<String JavaDoc> orderByDirection = new Vector JavaDoc<String JavaDoc>();
56   private int index = 0;
57   private boolean isSameTable = false;
58
59   public TableSQLData() {
60   }
61
62   public TableSQLData(VariablesSecureApp _vars, ConnectionProvider _conn, String JavaDoc _adTabId, String JavaDoc _orgList, String JavaDoc _clientList) throws Exception JavaDoc {
63     if (_vars!=null) setVars(_vars);
64     setPool(_conn);
65     setTabID(_adTabId);
66     setOrgList(_orgList);
67     setClientList(_clientList);
68     setWindowDefinition();
69     generateStructure();
70     generateSQL();
71   }
72
73   public void setParameter(String JavaDoc name, String JavaDoc value) throws Exception JavaDoc {
74     if (name==null || name.equals("")) throw new Exception JavaDoc("Invalid parameter name");
75     if (this.parameters==null) this.parameters = new Hashtable JavaDoc<String JavaDoc, String JavaDoc>();
76     if (value==null || value.equals("")) this.parameters.remove(name.toUpperCase());
77     else this.parameters.put(name.toUpperCase(), value);
78   }
79
80   public String JavaDoc getParameter(String JavaDoc name) {
81     if (name==null || name.equals("")) return "";
82     else if (this.parameters==null) return "";
83     else return this.parameters.get(name.toUpperCase());
84   }
85
86   public Vector JavaDoc<String JavaDoc> getParameters() {
87     Vector JavaDoc<String JavaDoc> result = new Vector JavaDoc<String JavaDoc>();
88     if (log4j.isDebugEnabled()) log4j.debug("Obtaining parameters");
89     Vector JavaDoc<QueryParameterStructure> vAux = getSelectParameters();
90     if (vAux!=null) {
91       for (int i=0;i<vAux.size();i++) {
92         QueryParameterStructure aux = vAux.elementAt(i);
93         String JavaDoc strAux = getParameter(aux.getName());
94         if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
95       }
96     }
97     if (log4j.isDebugEnabled()) log4j.debug("Select parameters obtained");
98     vAux = getFromParameters();
99     if (vAux!=null) {
100       for (int i=0;i<vAux.size();i++) {
101         QueryParameterStructure aux = vAux.elementAt(i);
102         String JavaDoc strAux = getParameter(aux.getName());
103         if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
104       }
105     }
106     if (log4j.isDebugEnabled()) log4j.debug("From parameters obtained");
107     vAux = getWhereParameters();
108     if (vAux!=null) {
109       for (int i=0;i<vAux.size();i++) {
110         QueryParameterStructure aux = vAux.elementAt(i);
111         String JavaDoc strAux = getParameter(aux.getName());
112         if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
113       }
114     }
115     if (log4j.isDebugEnabled()) log4j.debug("Where parameters obtained");
116     vAux = getFilterParameters();
117     if (vAux!=null) {
118       for (int i=0;i<vAux.size();i++) {
119         QueryParameterStructure aux = vAux.elementAt(i);
120         String JavaDoc strAux = getParameter(aux.getName());
121         if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
122       }
123     }
124     if (log4j.isDebugEnabled()) log4j.debug("Filter parameters obtained");
125     vAux = getOrderByParameters();
126     if (vAux!=null) {
127       for (int i=0;i<vAux.size();i++) {
128         QueryParameterStructure aux = vAux.elementAt(i);
129         String JavaDoc strAux = getParameter(aux.getName());
130         if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
131       }
132     }
133     if (log4j.isDebugEnabled()) log4j.debug("Order by parameters obtained");
134     result.addElement("#AD_LANGUAGE");
135     return result;
136   }
137
138   public Vector JavaDoc<String JavaDoc> getParameterValues() {
139     Vector JavaDoc<String JavaDoc> result = new Vector JavaDoc<String JavaDoc>();
140     if (log4j.isDebugEnabled()) log4j.debug("Obtaining parameters values");
141     Vector JavaDoc<QueryParameterStructure> vAux = getSelectParameters();
142     if (vAux!=null) {
143       for (int i=0;i<vAux.size();i++) {
144         QueryParameterStructure aux = vAux.elementAt(i);
145         result.addElement(getParameter(aux.getName()));
146       }
147     }
148     if (log4j.isDebugEnabled()) log4j.debug("Select parameters obtained");
149     vAux = getFromParameters();
150     if (vAux!=null) {
151       for (int i=0;i<vAux.size();i++) {
152         QueryParameterStructure aux = vAux.elementAt(i);
153         result.addElement(getParameter(aux.getName()));
154       }
155     }
156     if (log4j.isDebugEnabled()) log4j.debug("From parameters obtained");
157     vAux = getWhereParameters();
158     if (vAux!=null) {
159       for (int i=0;i<vAux.size();i++) {
160         QueryParameterStructure aux = vAux.elementAt(i);
161         result.addElement(getParameter(aux.getName()));
162       }
163     }
164     if (log4j.isDebugEnabled()) log4j.debug("Where parameters obtained");
165     vAux = getFilterParameters();
166     if (vAux!=null) {
167       for (int i=0;i<vAux.size();i++) {
168         QueryParameterStructure aux = vAux.elementAt(i);
169         result.addElement(getParameter(aux.getName()));
170       }
171     }
172     if (log4j.isDebugEnabled()) log4j.debug("Filter parameters obtained");
173     vAux = getOrderByParameters();
174     if (vAux!=null) {
175       for (int i=0;i<vAux.size();i++) {
176         QueryParameterStructure aux = vAux.elementAt(i);
177         result.addElement(getParameter(aux.getName()));
178       }
179     }
180     if (log4j.isDebugEnabled()) log4j.debug("Order by parameters obtained");
181     return result;
182   }
183
184   public Vector JavaDoc<String JavaDoc> getParameterValuesTotalSQL() {
185     Vector JavaDoc<String JavaDoc> result = new Vector JavaDoc<String JavaDoc>();
186     if (log4j.isDebugEnabled()) log4j.debug("Obtaining parameters values");
187     Vector JavaDoc<QueryParameterStructure> vAux = getFromParameters();
188     if (vAux!=null) {
189       for (int i=0;i<vAux.size();i++) {
190         QueryParameterStructure aux = vAux.elementAt(i);
191         result.addElement(getParameter(aux.getName()));
192       }
193     }
194     if (log4j.isDebugEnabled()) log4j.debug("From parameters obtained");
195     vAux = getWhereParameters();
196     if (vAux!=null) {
197       for (int i=0;i<vAux.size();i++) {
198         QueryParameterStructure aux = vAux.elementAt(i);
199         result.addElement(getParameter(aux.getName()));
200       }
201     }
202     if (log4j.isDebugEnabled()) log4j.debug("Where parameters obtained");
203     vAux = getFilterParameters();
204     if (vAux!=null) {
205       for (int i=0;i<vAux.size();i++) {
206         QueryParameterStructure aux = vAux.elementAt(i);
207         result.addElement(getParameter(aux.getName()));
208       }
209     }
210     if (log4j.isDebugEnabled()) log4j.debug("Filter parameters obtained");
211     return result;
212   }
213
214   public void setVars(VariablesSecureApp _vars) throws Exception JavaDoc {
215     if (_vars==null) throw new Exception JavaDoc("The session vars is null");
216     this.vars = _vars;
217   }
218   
219   public VariablesSecureApp getVars() {
220     return this.vars;
221   }
222
223   public void setPool(ConnectionProvider _conn) throws Exception JavaDoc {
224     if (_conn==null) throw new Exception JavaDoc("The pool is null");
225     this.pool = _conn;
226   }
227   
228   public ConnectionProvider getPool() {
229     return this.pool;
230   }
231
232   public void setTabID(String JavaDoc _data) throws Exception JavaDoc {
233     if (_data==null || _data.equals("")) throw new Exception JavaDoc("The Tab ID must be specified");
234     setParameter(internalPrefix + "AD_Tab_ID", _data);
235   }
236
237   public String JavaDoc getTabID() {
238     return getParameter(internalPrefix + "AD_Tab_ID");
239   }
240
241   public boolean getIsSameTable() {
242     return this.isSameTable;
243   }
244
245   public void setWindowDefinition() throws Exception JavaDoc {
246     TableSQLQueryData[] data = TableSQLQueryData.selectWindowDefinition(getPool(), getVars().getLanguage(), getTabID());
247     if (data==null || data.length==0) throw new Exception JavaDoc("Couldn't extract window information");
248     setParameter(internalPrefix + "AD_Window_ID", data[0].adWindowId);
249     setParameter(internalPrefix + "AD_Table_ID", data[0].adTableId);
250     setParameter(internalPrefix + "TabLevel", data[0].tablevel);
251     setParameter(internalPrefix + "IsReadOnly", data[0].isreadonly);
252     setParameter(internalPrefix + "HasTree", data[0].hastree);
253     setParameter(internalPrefix + "WhereClause", data[0].whereclause);
254     setParameter(internalPrefix + "OrderByClause", data[0].orderbyclause);
255     setParameter(internalPrefix + "AD_Process_ID", data[0].adProcessId);
256     setParameter(internalPrefix + "AD_ColumnSortOrder_ID", data[0].adColumnsortorderId);
257     setParameter(internalPrefix + "AD_ColumnSortYesNo_ID", data[0].adColumnsortyesnoId);
258     setParameter(internalPrefix + "IsSortTab", data[0].issorttab);
259     setParameter(internalPrefix + "FilterClause", data[0].filterclause);
260     setParameter(internalPrefix + "EditReference", data[0].editreference);
261     setParameter(internalPrefix + "WindowType", data[0].windowtype);
262     setParameter(internalPrefix + "IsSOTrx", data[0].issotrx);
263     setParameter(internalPrefix + "WindowName", data[0].windowName);
264     setParameter(internalPrefix + "WindowNameTrl", data[0].windowNameTrl);
265     setParameter(internalPrefix + "TabName", data[0].tabName);
266     setParameter(internalPrefix + "TabNameTrl", data[0].tabNameTrl);
267     setParameter(internalPrefix + "TableName", data[0].tablename);
268     if (!getTabLevel().equals("0")) {
269       TableSQLQueryData[] parentFields = TableSQLQueryData.parentsColumnName(getPool(), getTabID());
270       if (parentFields != null && parentFields.length>0) {
271         if (log4j.isDebugEnabled()) log4j.debug("Parent key: " + parentFields[0].columnname);
272         setParameter(internalPrefix + "ParentColumnName", parentFields[0].columnname);
273       } else {
274         parentFields = TableSQLQueryData.parentsColumnNameKey(getPool(), getTabID());
275         if (parentFields != null && parentFields.length>0) {
276           if (log4j.isDebugEnabled()) log4j.debug("Parent key: " + parentFields[0].columnname);
277           setParameter(internalPrefix + "ParentColumnName", parentFields[0].columnname);
278           this.isSameTable = true;
279         }
280       }
281     }
282   }
283
284   public String JavaDoc getWindowID() {
285     return getParameter(internalPrefix + "AD_Window_ID");
286   }
287
288   public String JavaDoc getKeyColumn() {
289     return getParameter(internalPrefix + "KeyColumn");
290   }
291
292   public String JavaDoc getTableID() {
293     return getParameter(internalPrefix + "AD_Table_ID");
294   }
295
296   public String JavaDoc getTableName() {
297     return getParameter(internalPrefix + "TableName");
298   }
299
300   public String JavaDoc getWhereClause() {
301     return getParameter(internalPrefix + "WhereClause");
302   }
303
304   public String JavaDoc getOrderByClause() {
305     return getParameter(internalPrefix + "OrderByClause");
306   }
307
308   public String JavaDoc getFilterClause() {
309     return getParameter(internalPrefix + "FilterClause");
310   }
311
312   public String JavaDoc getTabLevel() {
313     return getParameter(internalPrefix + "TabLevel");
314   }
315
316   public String JavaDoc getWindowType() {
317     return getParameter(internalPrefix + "WindowType");
318   }
319
320   public String JavaDoc getParentColumnName() {
321     return getParameter(internalPrefix + "ParentColumnName");
322   }
323
324   public void setOrgList(String JavaDoc _orgList) throws Exception JavaDoc {
325     setParameter(internalPrefix + "orgList", _orgList);
326   }
327
328   public String JavaDoc getOrgList() {
329     return getParameter(internalPrefix + "orgList");
330   }
331
332   public void setClientList(String JavaDoc _clientList) throws Exception JavaDoc {
333     setParameter(internalPrefix + "clientList", _clientList);
334   }
335
336   public String JavaDoc getClientList() {
337     return getParameter(internalPrefix + "clientList");
338   }
339
340   public void addStructure(Properties JavaDoc _prop) {
341     if (_prop==null) return;
342     if (this.structure == null) this.structure = new Vector JavaDoc<Properties JavaDoc>();
343     this.structure.addElement(_prop);
344   }
345
346   public Vector JavaDoc<Properties JavaDoc> getStructure() {
347     return this.structure;
348   }
349
350   public void addOrderByPosition(String JavaDoc _data) {
351     if (_data==null) return;
352     if (this.orderByPosition == null) this.orderByPosition = new Vector JavaDoc<String JavaDoc>();
353     this.orderByPosition.addElement(_data);
354   }
355
356   public Vector JavaDoc<String JavaDoc> getOrderByPosition() {
357     return this.orderByPosition;
358   }
359
360   public void addOrderByDirection(String JavaDoc _data) {
361     if (_data==null) return;
362     if (this.orderByDirection == null) this.orderByDirection = new Vector JavaDoc<String JavaDoc>();
363     this.orderByDirection.addElement(_data);
364   }
365
366   public Vector JavaDoc<String JavaDoc> getOrderByDirection() {
367     return this.orderByDirection;
368   }
369
370   public Vector JavaDoc<Properties JavaDoc> getFilteredStructure(String JavaDoc propertyName, String JavaDoc propertyValue) {
371     Vector JavaDoc<Properties JavaDoc> vAux = new Vector JavaDoc<Properties JavaDoc>();
372     if (this.structure==null) return vAux;
373     for (Enumeration JavaDoc<Properties JavaDoc> e = this.structure.elements();e.hasMoreElements();) {
374       Properties JavaDoc prop = e.nextElement();
375       if (prop.getProperty(propertyName).equals(propertyValue)) vAux.addElement(prop);
376     }
377     return vAux;
378   }
379
380   public void setIndex(int _index) {
381     this.index = _index;
382   }
383
384   public int getIndex() {
385     return this.index;
386   }
387
388   public void addSelectField(String JavaDoc _field, String JavaDoc _alias) {
389     addSelectField(_field, _alias, false);
390   }
391
392   public void addSelectField(String JavaDoc _field, String JavaDoc _alias, boolean _new) {
393     QueryFieldStructure p = new QueryFieldStructure(_field, " AS ", _alias, "SELECT");
394     if (this.select == null) this.select = new Vector JavaDoc<QueryFieldStructure>();
395     else {
396       String JavaDoc oldField = getSelectField(_alias);
397       if (oldField!=null && !oldField.equals("")) p = new QueryFieldStructure(oldField + FIELD_CONCAT + _field, " AS ", _alias, "SELECT");
398     }
399     this.select.addElement(p);
400   }
401
402   public String JavaDoc getSelectField(String JavaDoc _alias) {
403     if (this.select == null) return "";
404     for (int i=0;i<this.select.size();i++) {
405       QueryFieldStructure p = this.select.elementAt(i);
406       if (p.getAlias().equalsIgnoreCase(_alias)) return p.getField();
407     }
408     return "";
409   }
410
411   public Vector JavaDoc<QueryFieldStructure> getSelectFields() {
412     return this.select;
413   }
414
415   public void addFromField(String JavaDoc _field, String JavaDoc _alias) {
416     QueryFieldStructure p = new QueryFieldStructure(_field, " ", _alias, "FROM");
417     if (this.from == null) this.from = new Vector JavaDoc<QueryFieldStructure>();
418     from.addElement(p);
419   }
420
421   public Vector JavaDoc<QueryFieldStructure> getFromFields() {
422     return this.from;
423   }
424
425   public void addWhereField(String JavaDoc _field, String JavaDoc _type) {
426     QueryFieldStructure p = new QueryFieldStructure(_field, "", "", _type);
427     if (this.where == null) this.where = new Vector JavaDoc<QueryFieldStructure>();
428     where.addElement(p);
429   }
430
431   public Vector JavaDoc<QueryFieldStructure> getWhereFields() {
432     return this.where;
433   }
434
435   public void addFilterField(String JavaDoc _field, String JavaDoc _type) {
436     QueryFieldStructure p = new QueryFieldStructure(_field, "", "", _type);
437     if (this.filter == null) this.filter = new Vector JavaDoc<QueryFieldStructure>();
438     filter.addElement(p);
439   }
440
441   public Vector JavaDoc<QueryFieldStructure> getFilterFields() {
442     return this.filter;
443   }
444
445   public void addInternalFilterField(String JavaDoc _field, String JavaDoc _type) {
446     QueryFieldStructure p = new QueryFieldStructure(_field, "", "", _type);
447     if (this.internalFilter == null) this.internalFilter = new Vector JavaDoc<QueryFieldStructure>();
448     internalFilter.addElement(p);
449   }
450
451   public Vector JavaDoc<QueryFieldStructure> getInternalFilterFields() {
452     return this.internalFilter;
453   }
454
455   public void addOrderByField(String JavaDoc _field) {
456     QueryFieldStructure p = new QueryFieldStructure(_field, "", "", "ORDERBY");
457     if (this.orderBy == null) this.orderBy = new Vector JavaDoc<QueryFieldStructure>();
458     this.orderBy.addElement(p);
459   }
460
461   public Vector JavaDoc<QueryFieldStructure> getOrderByFields() {
462     return this.orderBy;
463   }
464
465   public void addInternalOrderByField(String JavaDoc _field) {
466     QueryFieldStructure p = new QueryFieldStructure(_field, "", "", "ORDERBY");
467     if (this.internalOrderBy == null) this.internalOrderBy = new Vector JavaDoc<QueryFieldStructure>();
468     this.internalOrderBy.addElement(p);
469   }
470
471   public Vector JavaDoc<QueryFieldStructure> getInternalOrderByFields() {
472     return this.internalOrderBy;
473   }
474
475   public void addSelectParameter(String JavaDoc _parameter, String JavaDoc _fieldName) {
476     if (this.paramSelect == null) this.paramSelect = new Vector JavaDoc<QueryParameterStructure>();
477     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "SELECT");
478     this.paramSelect.addElement(aux);
479   }
480
481   public Vector JavaDoc<QueryParameterStructure> getSelectParameters() {
482     return this.paramSelect;
483   }
484
485   public void addFromParameter(String JavaDoc _parameter, String JavaDoc _fieldName) {
486     if (this.paramFrom == null) this.paramFrom = new Vector JavaDoc<QueryParameterStructure>();
487     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "FROM");
488     this.paramFrom.addElement(aux);
489   }
490
491   public Vector JavaDoc<QueryParameterStructure> getFromParameters() {
492     return this.paramFrom;
493   }
494
495   public void addWhereParameter(String JavaDoc _parameter, String JavaDoc _fieldName, String JavaDoc _type) {
496     if (this.paramWhere == null) this.paramWhere = new Vector JavaDoc<QueryParameterStructure>();
497     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, _type);
498     this.paramWhere.addElement(aux);
499   }
500
501   public Vector JavaDoc<QueryParameterStructure> getWhereParameters() {
502     return this.paramWhere;
503   }
504
505   public void addFilterParameter(String JavaDoc _parameter, String JavaDoc _fieldName, String JavaDoc _type) {
506     if (this.paramFilter == null) this.paramFilter = new Vector JavaDoc<QueryParameterStructure>();
507     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, _type);
508     this.paramFilter.addElement(aux);
509   }
510
511   public Vector JavaDoc<QueryParameterStructure> getFilterParameters() {
512     return this.paramFilter;
513   }
514
515   public void addInternalFilterParameter(String JavaDoc _parameter, String JavaDoc _fieldName, String JavaDoc _type) {
516     if (this.paramInternalFilter == null) this.paramInternalFilter = new Vector JavaDoc<QueryParameterStructure>();
517     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, _type);
518     this.paramInternalFilter.addElement(aux);
519   }
520
521   public Vector JavaDoc<QueryParameterStructure> getInternalFilterParameters() {
522     return this.paramInternalFilter;
523   }
524
525   public void addOrderByParameter(String JavaDoc _parameter, String JavaDoc _fieldName) {
526     if (this.paramOrderBy == null) this.paramOrderBy = new Vector JavaDoc<QueryParameterStructure>();
527     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "ORDERBY");
528     this.paramOrderBy.addElement(aux);
529   }
530
531   public Vector JavaDoc<QueryParameterStructure> getOrderByParameters() {
532     return this.paramOrderBy;
533   }
534
535   public void addInternalOrderByParameter(String JavaDoc _parameter, String JavaDoc _fieldName) {
536     if (this.paramInternalOrderBy == null) this.paramInternalOrderBy = new Vector JavaDoc<QueryParameterStructure>();
537     QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "ORDERBY");
538     this.paramInternalOrderBy.addElement(aux);
539   }
540
541   public Vector JavaDoc<QueryParameterStructure> getInternalOrderByParameters() {
542     return this.paramInternalOrderBy;
543   }
544
545   public String JavaDoc parseContext(String JavaDoc context, String JavaDoc type) {
546     if (context==null || context.equals("")) return "";
547     StringBuffer JavaDoc strOut = new StringBuffer JavaDoc();
548     String JavaDoc value = new String JavaDoc(context);
549     String JavaDoc token, defStr;
550     int i = value.indexOf("@");
551     while (i!=-1) {
552       strOut.append(value.substring(0,i));
553       value = value.substring(i+1);
554       int j=value.indexOf("@");
555       if (j==-1) {
556         strOut.append(value);
557         return strOut.toString();
558       }
559       token = value.substring(0, j);
560       if (token.equalsIgnoreCase("#User_Client")) defStr=getClientList();
561       else if (token.equalsIgnoreCase("#User_Org")) defStr=getOrgList();
562       else defStr="?";
563       
564       if (defStr.equals("?")) {
565         if (type.equalsIgnoreCase("WHERE")) addWhereParameter(token, type.toUpperCase(), type.toUpperCase());
566         else if (type.equalsIgnoreCase("FILTER")) addInternalFilterParameter(token, type.toUpperCase(), type.toUpperCase());
567         else if (type.equalsIgnoreCase("INTERNAL_ORDERBY")) addInternalOrderByParameter(token, "ORDERBY");
568       }
569       strOut.append(defStr);
570       value=value.substring(j+1);
571       i=value.indexOf("@");
572     }
573     strOut.append(value);
574     return strOut.toString().replace("'?'","?");
575   }
576
577   private void generateStructure() throws Exception JavaDoc {
578     if (getPool()==null) throw new Exception JavaDoc("No pool defined for database connection");
579     else if (getTabID().equals("")) throw new Exception JavaDoc("No Tab defined");
580     TableSQLQueryData[] data = TableSQLQueryData.selectStructure(getPool(), getTabID(), getVars().getLanguage());
581     if (data==null || data.length==0) throw new Exception JavaDoc("Couldn't get structure for tab " + getTabID());
582     String JavaDoc primaryKey = "";
583     String JavaDoc secondaryKey = "";
584     for (int i=0;i<data.length;i++) {
585       Properties JavaDoc prop = new Properties JavaDoc();
586       prop.setProperty("ColumnName", data[i].columnname);
587       prop.setProperty("AD_Reference_ID", data[i].adReferenceId);
588       prop.setProperty("AD_Reference_Value_ID", data[i].adReferenceValueId);
589       prop.setProperty("AD_Val_Rule_ID", data[i].adValRuleId);
590       prop.setProperty("FieldLength", data[i].fieldlength);
591       prop.setProperty("DefaultValue", data[i].defaultvalue);
592       prop.setProperty("IsKey", data[i].iskey);
593       prop.setProperty("IsParent", data[i].isparent);
594       prop.setProperty("IsMandatory", data[i].ismandatory);
595       prop.setProperty("IsUpdateable", data[i].isupdateable);
596       prop.setProperty("ReadOnlyLogic", data[i].readonlylogic);
597       prop.setProperty("IsIdentifier", data[i].isidentifier);
598       prop.setProperty("SeqNo", data[i].seqno);
599       prop.setProperty("IsTranslated", data[i].istranslated);
600       prop.setProperty("IsEncrypted", data[i].isencrypted);
601       prop.setProperty("VFormat", data[i].vformat);
602       prop.setProperty("ValueMin", data[i].valuemin);
603       prop.setProperty("ValueMax", data[i].valuemax);
604       prop.setProperty("IsSelectionColumn", data[i].isselectioncolumn);
605       prop.setProperty("AD_Process_ID", data[i].adProcessId);
606       prop.setProperty("IsSessionAttr", data[i].issessionattr);
607       prop.setProperty("IsSecondaryKey", data[i].issecondarykey);
608       prop.setProperty("IsDesencryptable", data[i].isdesencryptable);
609       prop.setProperty("AD_CallOut_ID", data[i].adCalloutId);
610       prop.setProperty("Name", data[i].name);
611       prop.setProperty("AD_FieldGroup_ID", data[i].adFieldgroupId);
612       prop.setProperty("IsDisplayed", data[i].isdisplayed);
613       prop.setProperty("DisplayLogic", data[i].displaylogic);
614       prop.setProperty("DisplayLength", data[i].displaylength);
615       prop.setProperty("IsReadOnly", data[i].isreadonly);
616       prop.setProperty("SortNo", data[i].sortno);
617       prop.setProperty("IsSameLine", data[i].issameline);
618       prop.setProperty("IsHeading", data[i].isheading);
619       prop.setProperty("IsFieldOnly", data[i].isfieldonly);
620       prop.setProperty("ShowInRelation", data[i].showinrelation);
621       prop.setProperty("ColumnNameSearch", data[i].columnnameSearch);
622       addStructure(prop);
623       String JavaDoc parentKey = getParentColumnName();
624       if (parentKey==null) parentKey = "";
625       if (primaryKey.equals("") && data[i].iskey.equals("Y") && (getIsSameTable() || !data[i].columnname.equalsIgnoreCase(parentKey))) {
626         primaryKey = data[i].columnname;
627       } else if (secondaryKey.equals("") && data[i].issecondarykey.equals("Y") && (getIsSameTable() || !data[i].columnname.equalsIgnoreCase(parentKey))) {
628         secondaryKey = data[i].columnname;
629       }
630     }
631     if (!primaryKey.equals("")) setParameter(internalPrefix + "KeyColumn", primaryKey);
632     else if (!secondaryKey.equals("")) setParameter(internalPrefix + "KeyColumn", secondaryKey);
633     else {
634       primaryKey = TableSQLQueryData.columnNameKey(getPool(), getTabID());
635       if (!primaryKey.equals("")) setParameter(internalPrefix + "KeyColumn", primaryKey);
636       else throw new Exception JavaDoc("No column key defined for this tab");
637     }
638   }
639
640   public void generateSQL() throws Exception JavaDoc {
641     if (getPool()==null) throw new Exception JavaDoc("No pool defined for database connection");
642     Vector JavaDoc<Properties JavaDoc> headers = getStructure();
643     if (headers==null || headers.size()==0) throw new Exception JavaDoc("No structure defined");
644     int myIndex = this.index++;
645     addFromField(getTableName(), getTableName());
646     for (Enumeration JavaDoc<Properties JavaDoc> e = headers.elements();e.hasMoreElements();) {
647       Properties JavaDoc prop = e.nextElement();
648       switch (Integer.valueOf(prop.getProperty("AD_Reference_ID")).intValue()) {
649         case 17: //List
650 case 18: //Table
651 case 19: //TableDir
652 case 30: //Search
653 case 31: //Locator
654 case 35:
655         case 25: //Account
656 case 800011: //Product Search
657 addSelectField(getTableName() + "." + prop.getProperty("ColumnName"), prop.getProperty("ColumnName"));
658           identifier(getTableName(), prop, prop.getProperty("ColumnName") + "_R");
659           break;
660         default:
661           identifier(getTableName(), prop, prop.getProperty("ColumnName"));
662           break;
663       }
664     }
665     setWindowFilters();
666   }
667
668   public void identifier(String JavaDoc parentTableName, Properties JavaDoc field, String JavaDoc identifierName) throws Exception JavaDoc {
669     String JavaDoc reference;
670     if (field==null) return;
671     else reference = field.getProperty("AD_Reference_ID");
672     switch (Integer.valueOf(reference).intValue()) {
673       case 17: //List
674 setListQuery(parentTableName, field.getProperty("ColumnName"), field.getProperty("AD_Reference_Value_ID"), identifierName);
675         break;
676       case 18: //Table
677 setTableQuery(parentTableName, field.getProperty("ColumnName"), field.getProperty("AD_Reference_Value_ID"), identifierName);
678         break;
679       case 19: //TableDir
680 setTableDirQuery(parentTableName, field.getProperty("ColumnNameSearch"), field.getProperty("ColumnName"), field.getProperty("AD_Reference_Value_ID"), identifierName);
681         break;
682       case 35: //PAttribute
683 case 30: //Search
684 setTableDirQuery(parentTableName, field.getProperty("ColumnNameSearch"), field.getProperty("ColumnName"), field.getProperty("AD_Reference_Value_ID"), identifierName);
685         break;
686       case 32: //Image
687 setImageQuery(parentTableName, field.getProperty("ColumnNameSearch"), identifierName);
688         break;
689       case 28: //Button
690 if (field.getProperty("AD_Reference_Value_ID")!=null && !field.getProperty("AD_Reference_Value_ID").equals("")) {
691           setListQuery(parentTableName, field.getProperty("ColumnName"), field.getProperty("AD_Reference_Value_ID"), identifierName);
692         } else addSelectField(formatField((parentTableName + "." + field.getProperty("ColumnName")), reference), identifierName);
693         break;
694       default:
695         if (!checkTableTranslation(parentTableName, field, reference, identifierName)) {
696           addSelectField(formatField((parentTableName + "." + field.getProperty("ColumnName")), reference), identifierName);
697         }
698         break;
699     }
700   }
701
702   private boolean checkTableTranslation(String JavaDoc tableName, Properties JavaDoc field, String JavaDoc reference, String JavaDoc identifierName) throws Exception JavaDoc {
703     if (tableName==null || tableName.equals("") || field==null) return false;
704     ComboTableQueryData[] data = ComboTableQueryData.selectTranslatedColumn(getPool(), field.getProperty("TableName"), field.getProperty("ColumnName"));
705     if (data==null || data.length==0) return false;
706     int myIndex = this.index++;
707     addSelectField("(CASE WHEN td_trl" + myIndex + "." + data[0].columnname + " IS NULL THEN " + formatField((tableName + "." + field.getProperty("ColumnName")), reference) + " ELSE " + formatField(("td_trl" + myIndex + "." + data[0].columnname), reference) + " END)", identifierName);
708     addFromField("(SELECT AD_Language, " + data[0].reference + ", " + data[0].columnname + " FROM " + data[0].tablename + ") td_trl" + myIndex + " on " + tableName + "." + data[0].reference + " = td_trl" + myIndex + "." + data[0].reference + " AND td_trl" + myIndex + ".AD_Language = ?", "td_trl" + myIndex);
709     addFromParameter("#AD_LANGUAGE", "LANGUAGE");
710     return true;
711   }
712   
713   private String JavaDoc formatField(String JavaDoc field, String JavaDoc reference) {
714     String JavaDoc result = "";
715     if (field==null) return "";
716     else if (reference==null || reference.length()==0) return field;
717     switch (Integer.valueOf(reference).intValue()) {
718     case 11: //INTEGER
719 result = "CAST(" + field + " AS INTEGER)";
720       break;
721     case 12: //AMOUNT
722 case 22: //NUMBER
723 case 26: //ROWID
724 case 29: //QUANTITY
725 case 800008: //PRICE
726 case 800019: //GENERAL QUANTITY
727 result = "TO_NUMBER(" + field + ")";
728       break;
729     case 15: //DATE
730 result = "TO_CHAR(" + field + (getVars()==null?"":(", '" + getVars().getSessionValue("#AD_SqlDateFormat") + "'")) + ")";
731       break;
732     case 16: //DATETIME
733 result = "(" + field + ")";
734       break;
735     case 24: //TIME
736 result = "TO_CHAR(" + field + ", 'HH24:MI:SS')";
737       break;
738     case 20: //YESNO
739 result = "COALESCE(" + field + ", 'N')";
740       break;
741     default:
742       result = "TO_CHAR(" + field + ")";
743       break;
744     }
745     return result;
746   }
747
748   private Properties JavaDoc fieldToProperties(FieldProvider field) throws Exception JavaDoc {
749     Properties JavaDoc aux = new Properties JavaDoc();
750     if (field!=null) {
751       aux.setProperty("ColumnName", field.getField("name"));
752       aux.setProperty("TableName", field.getField("tablename"));
753       aux.setProperty("AD_Reference_ID", field.getField("reference"));
754       aux.setProperty("AD_Reference_Value_ID", field.getField("referencevalue"));
755       aux.setProperty("IsMandatory", field.getField("required"));
756       aux.setProperty("ColumnNameSearch", field.getField("columnname"));
757     }
758     return aux;
759   }
760
761   private void setImageQuery(String JavaDoc tableName, String JavaDoc fieldName, String JavaDoc identifierName) throws Exception JavaDoc {
762     int myIndex = this.index++;
763     addSelectField("((CASE td" + myIndex + ".isActive WHEN 'N' THEN '" + INACTIVE_DATA + "' ELSE '' END) || td" + myIndex + ".imageURL)", identifierName);
764     String JavaDoc tables = "(select IsActive, AD_Image_ID, ImageURL from AD_Image) td" + myIndex;
765     tables += " on " + tableName + "." + fieldName;
766     tables += " = td" + myIndex + ".AD_Image_ID";
767     addFromField(tables, "td" + myIndex);
768   }
769
770   private void setListQuery(String JavaDoc tableName, String JavaDoc fieldName, String JavaDoc referenceValue, String JavaDoc identifierName) throws Exception JavaDoc {
771     int myIndex = this.index++;
772     addSelectField("((CASE td" + myIndex + ".isActive WHEN 'N' THEN '" + INACTIVE_DATA + "' ELSE '' END) || (CASE WHEN td_trl" + myIndex + ".name IS NULL THEN td" + myIndex + ".name ELSE td_trl" + myIndex + ".name END))", identifierName);
773     String JavaDoc tables = "(select IsActive, ad_ref_list_id, ad_reference_id, value, name from ad_ref_list) td" + myIndex;
774     tables += " on ";
775     if (fieldName.equalsIgnoreCase("DocAction")) tables += "(CASE " + tableName + "." + fieldName + " WHEN '--' THEN 'CL' ELSE TO_CHAR(" + tableName + "." + fieldName + ") END)";
776     else tables += tableName + "." + fieldName;
777     tables += " = td" + myIndex + ".value AND td" + myIndex + ".ad_reference_id = ?";
778     addFromField(tables, "td" + myIndex);
779     addFromParameter("TD" + myIndex + ".AD_REFERENCE_ID", "KEY");
780     setParameter("TD" + myIndex + ".AD_REFERENCE_ID", referenceValue);
781     addFromField("(SELECT ad_language, name, ad_ref_list_id from ad_ref_list_trl) td_trl" + myIndex + " on td" + myIndex + ".ad_ref_list_id = td_trl" + myIndex + ".ad_ref_list_id AND td_trl" + myIndex + ".ad_language = ?", "td_trl" + myIndex);
782     addFromParameter("#AD_LANGUAGE", "LANGUAGE");
783   }
784
785   private void setTableQuery(String JavaDoc tableName, String JavaDoc fieldName, String JavaDoc referenceValue, String JavaDoc identifierName) throws Exception JavaDoc {
786     int myIndex = this.index++;
787     ComboTableQueryData trd[] = ComboTableQueryData.selectRefTable(getPool(), referenceValue);
788     if (trd==null || trd.length==0) return;
789     String JavaDoc tables = "(SELECT ";
790     if (trd[0].isvaluedisplayed.equals("Y")) {
791       addSelectField("td" + myIndex + ".VALUE", identifierName);
792       tables += "value, ";
793     }
794     tables += trd[0].keyname + ", " + trd[0].name + " FROM ";
795     Properties JavaDoc fieldsAux = fieldToProperties(trd[0]);
796     tables += trd[0].tablename + ") td" + myIndex;
797     tables += " on " + tableName + "." + fieldName + " = td" + myIndex + "." + trd[0].keyname;
798     addFromField(tables, "td" + myIndex);
799     identifier("td" + myIndex, fieldsAux, identifierName);
800   }
801
802   private void setTableDirQuery(String JavaDoc tableName, String JavaDoc fieldName, String JavaDoc parentFieldName, String JavaDoc referenceValue, String JavaDoc identifierName) throws Exception JavaDoc {
803     int myIndex = this.index++;
804     String JavaDoc name = fieldName;
805     String JavaDoc tableDirName = name.substring(0,name.length()-3);
806     if (referenceValue!=null && !referenceValue.equals("")) {
807       TableSQLQueryData[] search = TableSQLQueryData.searchInfo(getPool(), referenceValue);
808       if (search!=null && search.length!=0) {
809         name = search[0].columnname;
810         tableDirName = search[0].tablename;
811       }
812     }
813     ComboTableQueryData trd[] = ComboTableQueryData.identifierColumns(getPool(), tableDirName);
814     String JavaDoc tables = "(SELECT " + name;
815     for (int i=0;i<trd.length;i++) tables += ", " + trd[i].name;
816     tables += " FROM ";
817     tables += tableDirName + ") td" + myIndex;
818     tables += " on " + tableName + "." + parentFieldName + " = td" + myIndex + "." + name + "\n";
819     addFromField(tables, "td" + myIndex);
820     for (int i=0;i<trd.length;i++) identifier("td" + myIndex, fieldToProperties(trd[i]), identifierName);
821   }
822
823   private int findCloseTarget(String JavaDoc text, int pos, String JavaDoc openChar, String JavaDoc closeChar) {
824     if (text==null || text.equals("")) return -1;
825     int nextOpen = text.indexOf(openChar, pos);
826     int nextClose = text.indexOf(closeChar, pos);
827     if (nextClose!=-1) {
828       while (pos!=-1 && nextClose!=-1 && (nextOpen!=-1 && nextClose>nextOpen)) {
829         pos = findCloseTarget(text, nextOpen+1, openChar, closeChar);
830         nextOpen = text.indexOf(openChar, pos);
831         nextClose = text.indexOf(closeChar, pos);
832       }
833       if (pos==-1) nextClose = -1;
834     }
835     return nextClose;
836   }
837
838   private Vector JavaDoc<String JavaDoc> getOrdeByIntoFields(String JavaDoc text) {
839     Vector JavaDoc<String JavaDoc> result = new Vector JavaDoc<String JavaDoc>();
840     if (text!=null && !text.equals("")) {
841       int lastPos = 0;
842       int openPos = text.indexOf("(");
843       int actualPos = text.indexOf(",");
844       while (actualPos!=-1) {
845         if (actualPos>openPos) openPos = findCloseTarget(text, openPos+1, "(", ")");
846         if (openPos == -1) {
847           log4j.error("Parsing failed on orderby clause: " + text + " - pos: " + Integer.toString(actualPos));
848           result.addElement(text.substring(lastPos));
849           lastPos=-1;
850           actualPos=-1;
851         } else {
852           result.addElement(text.substring(lastPos, actualPos));
853           lastPos = actualPos+1;
854           openPos = text.indexOf("(", lastPos);
855           actualPos = text.indexOf(",", lastPos);
856         }
857       }
858       if (lastPos!=-1 && lastPos<text.length()) result.addElement(text.substring(lastPos));
859     }
860     return result;
861   }
862
863   private void setWindowFilters() throws Exception JavaDoc {
864     String JavaDoc strWhereClause = getWhereClause();
865     if (strWhereClause!=null && !strWhereClause.equals("")) {
866       if (strWhereClause.indexOf("@")!=-1) strWhereClause = parseContext(strWhereClause, "WHERE");
867       addWhereField(strWhereClause, "WHERE");
868     }
869     if (!getTableName().toUpperCase().endsWith("_ACCESS")) {
870       addWhereField(getTableName() + ".AD_Client_ID IN (" + getClientList() + ")", "WHERE");
871       addWhereField(getTableName() + ".AD_Org_ID IN (" + getOrgList() + ")", "WHERE");
872     }
873     String JavaDoc parentKey = getParentColumnName();
874     if (parentKey!=null && !parentKey.equals("")) {
875       addWhereField(getTableName() + "." + parentKey + " = ?", "PARENT");
876       addWhereParameter("PARENT", "PARENT", "PARENT");
877     }
878     String JavaDoc strOrderByClause = getOrderByClause();
879     if (strOrderByClause==null || strOrderByClause.equals("")) getFieldsOrderByClause();
880     else {
881       if (strOrderByClause.indexOf("@")!=-1) strOrderByClause = parseContext(strOrderByClause, "INTERNAL_ORDERBY");
882       Vector JavaDoc<String JavaDoc> vecOrdersAux = getOrdeByIntoFields(strOrderByClause);
883       if (vecOrdersAux!=null && vecOrdersAux.size()>0) {
884         for (int i=0;i<vecOrdersAux.size();i++) addInternalOrderByField(getRealOrderByColumn(vecOrdersAux.elementAt(i)));
885       } else addInternalOrderByField(getRealOrderByColumn(strOrderByClause));
886     }
887     String JavaDoc strFilterClause = getFilterClause();
888     if (strFilterClause != null && !strFilterClause.equals("")) {
889       if (strFilterClause.indexOf("@")!=-1) strFilterClause = parseContext(strFilterClause, "FILTER");
890       addInternalFilterField(strFilterClause, "FILTER");
891     }
892     if (getWindowType().equalsIgnoreCase("T") && getTabLevel().equals("0")) {
893       addInternalFilterField("(COALESCE(" + getTableName() + ".Processed, 'N')='N' OR " + getTableName() + ".Updated > now()-TO_NUMBER(?))", "FILTER");
894       addInternalFilterParameter("#Transactional$Range", "FILTER", "FILTER");
895     }
896   }
897
898   private void getFieldsOrderByClause() throws Exception JavaDoc {
899     TableSQLQueryData[] orderByData = TableSQLQueryData.selectOrderByFields(getPool(), getTabID());
900     if (orderByData==null) return;
901     for (int i=0;i<orderByData.length;i++) {
902       addInternalOrderByField(getTableName() + "." + orderByData[i].columnname + " " + (orderByData[i].columnname.equalsIgnoreCase("DocumentNo")?"DESC":"ASC"));
903     }
904   }
905
906   private String JavaDoc getRealOrderByColumn(String JavaDoc data) {
907     if (data==null || data.equals("")) return "";
908     data = data.trim();
909     String JavaDoc orderDirection = "ASC";
910     String JavaDoc orderField = data;
911     int pos = data.toUpperCase().lastIndexOf(" DESC");
912     if (pos==-1 || pos!= (data.length()-5)) {
913       pos = data.toUpperCase().lastIndexOf(" ASC");
914       if (pos!=-1 && pos== (data.length()-4)) {
915         orderField = data.substring(0,pos);
916       } else return data;
917     } else {
918       orderField = data.substring(0,pos);
919       orderDirection = "DESC";
920     }
921     String JavaDoc _alias = getSelectFieldAlias(orderField);
922     if (!_alias.equals("") && this.structure!=null) {
923       boolean isTranslated = false;
924       if (_alias.endsWith("_R")) {
925         isTranslated = true;
926         _alias = _alias.substring(0, _alias.length()-2);
927       }
928       int position = -1;
929       String JavaDoc reference = "";
930       Properties JavaDoc myProps = getColumnPosition(_alias);
931       if (myProps != null) {
932         position = Integer.valueOf(myProps.getProperty("Position")).intValue();
933         reference = myProps.getProperty("AD_Reference_ID");
934       }
935       if (position!=-1) {
936         addOrderByPosition(Integer.toString(position));
937         addOrderByDirection(orderDirection);
938       }
939       if (!isTranslated) {
940         String JavaDoc aux = getSelectField(_alias + "_R");
941         if (aux!=null && !aux.equals("")) orderField = aux;
942       }
943       if (reference.equals("15") || reference.equals("16") || reference.equals("24")) {
944         orderField = "TO_DATE(" + orderField + ")";
945       }
946     }
947     return orderField + " " + orderDirection;
948   }
949
950   public SQLReturnObject[] getHeaders() {
951     return getHeaders(false);
952   }
953
954   public SQLReturnObject[] getHeaders(boolean withoutIdentifiers) {
955     SQLReturnObject[] data = null;
956     Vector JavaDoc<SQLReturnObject> vAux = new Vector JavaDoc<SQLReturnObject>();
957     Vector JavaDoc<Properties JavaDoc> structure = getStructure();
958     for (Enumeration JavaDoc<Properties JavaDoc> e = structure.elements();e.hasMoreElements();) {
959       Properties JavaDoc prop = e.nextElement();
960       if (prop.getProperty("IsKey").equals("Y") || prop.getProperty("IsSecondaryKey").equals("Y") || (prop.getProperty("IsDisplayed").equals("Y") && prop.getProperty("ShowInRelation").equals("Y") && prop.getProperty("IsEncrypted").equals("N") && !prop.getProperty("ColumnName").equals(getParentColumnName()))) {
961         SQLReturnObject dataAux = new SQLReturnObject();
962         dataAux.setData("columnname", prop.getProperty("ColumnName"));
963         dataAux.setData("gridcolumnname", prop.getProperty("ColumnName"));
964         dataAux.setData("adReferenceId", prop.getProperty("AD_Reference_ID"));
965         dataAux.setData("adReferenceValueId", prop.getProperty("AD_Reference_Value_ID"));
966         dataAux.setData("isidentifier", (prop.getProperty("IsIdentifier").equals("Y")?"true":"false"));
967         dataAux.setData("iskey", (prop.getProperty("ColumnName").equals(getKeyColumn())?"true":"false"));
968         dataAux.setData("isvisible", ((prop.getProperty("IsDisplayed").equals("Y") && prop.getProperty("ShowInRelation").equals("Y"))?"true":"false"));
969         dataAux.setData("name", prop.getProperty("Name"));
970         String JavaDoc type = "string";
971         if (prop.getProperty("AD_Reference_ID").equals("17") || prop.getProperty("AD_Reference_ID").equals("18") || prop.getProperty("AD_Reference_ID").equals("19")) {
972           type="dynamicEnum";
973         } else if (prop.getProperty("AD_Reference_ID").equals("800101")) {
974           type="url";
975         } else if (prop.getProperty("AD_Reference_ID").equals("32")) {
976           type="img";
977         }
978         dataAux.setData("type", type);
979         String JavaDoc strWidth = prop.getProperty("DisplayLength");
980         if (strWidth==null || strWidth.equals("")) strWidth = "0";
981         int width = Integer.valueOf(strWidth).intValue();
982         width = width * 6;
983         if (width<23) width=23;
984         else if (width>300) width=300;
985         dataAux.setData("width", Integer.toString(width));
986         boolean cloneRecord = (!withoutIdentifiers && (prop.getProperty("IsDisplayed").equals("Y") && prop.getProperty("ShowInRelation").equals("Y")) && prop.getProperty("ColumnName").equals(getKeyColumn()) && !getSelectField(prop.getProperty("ColumnName") + "_R").equals(""));
987         if (cloneRecord) {
988           dataAux.setData("isvisible", "false");
989           dataAux.setData("gridcolumnname", "keyname");
990         }
991         vAux.addElement(dataAux);
992         if (cloneRecord) vAux.addElement(getClone(dataAux));
993       }
994     }
995     data = new SQLReturnObject[vAux.size()];
996     vAux.copyInto(data);
997     return data;
998   }
999   
1000  public SQLReturnObject getClone(SQLReturnObject data) {
1001    SQLReturnObject dataAux = new SQLReturnObject();
1002    dataAux.setData("columnname", data.getData("columnname"));
1003    dataAux.setData("gridcolumnname", data.getData("columnname"));
1004    dataAux.setData("adReferenceId", data.getData("adReferenceId"));
1005    dataAux.setData("adReferenceValueId", data.getData("adReferenceValueId"));
1006    dataAux.setData("isidentifier", "true");
1007    dataAux.setData("iskey", "false");
1008    dataAux.setData("isvisible", "true");
1009    dataAux.setData("name", data.getData("name"));
1010    dataAux.setData("type", data.getData("type"));
1011    dataAux.setData("width", data.getData("width"));
1012    return dataAux;
1013  }
1014
1015  public Properties JavaDoc getColumnPosition(String JavaDoc _name) {
1016    SQLReturnObject[] vAux = getHeaders();
1017    Properties JavaDoc _prop = new Properties JavaDoc();
1018    for (int i = 0;i<vAux.length;i++) {
1019      if (vAux[i].getData("columnname").equalsIgnoreCase(_name) || (getTableName() + "." + vAux[i].getData("columnname")).equalsIgnoreCase(_name)) {
1020        _prop.setProperty("AD_Reference_ID", vAux[i].getData("adReferenceId"));
1021        _prop.setProperty("Position", Integer.toString(i));
1022        return _prop;
1023      }
1024    }
1025    return null;
1026  }
1027
1028  public String JavaDoc getSelectFieldAlias(String JavaDoc _data) {
1029    if (this.select == null) return "";
1030    for (int i=0;i<this.select.size();i++) {
1031      QueryFieldStructure p = this.select.elementAt(i);
1032      if (p.getField().equalsIgnoreCase(_data)) return p.getAlias();
1033      else if (p.getAlias().equalsIgnoreCase(_data)) return p.getAlias();
1034      else if ((getTableName() + "." + p.getAlias()).equalsIgnoreCase(_data.trim())) return p.getAlias();
1035    }
1036    return "";
1037  }
1038
1039  public void setOrderBy(Vector JavaDoc<String JavaDoc> _fields, Vector JavaDoc<String JavaDoc> _params) {
1040    this.orderBy = new Vector JavaDoc<QueryFieldStructure>();
1041    this.paramOrderBy = new Vector JavaDoc<QueryParameterStructure>();
1042    if (_fields==null || _fields.size()==0) {
1043      if (this.internalOrderBy!=null) {
1044        for (int i=0;i<this.internalOrderBy.size();i++) {
1045          QueryFieldStructure aux = this.internalOrderBy.elementAt(i);
1046          addOrderByField(getRealOrderByColumn(aux.getField()));
1047        }
1048        if (this.paramInternalOrderBy!=null) {
1049          for (int i=0;i<this.paramInternalOrderBy.size();i++) {
1050            QueryParameterStructure aux = this.paramInternalOrderBy.elementAt(i);
1051            addOrderByParameter(aux.getName(), aux.getField());
1052          }
1053        }
1054      }
1055    } else {
1056      for (int i=0;i<_fields.size();i++) {
1057        String JavaDoc strOrderByClause = _fields.elementAt(i);
1058        Vector JavaDoc<String JavaDoc> vecOrdersAux = getOrdeByIntoFields(strOrderByClause);
1059        if (vecOrdersAux!=null && vecOrdersAux.size()>0) {
1060          for (int j=0;j<vecOrdersAux.size();j++) addOrderByField(getRealOrderByColumn(vecOrdersAux.elementAt(j)));
1061        } else addOrderByField(getRealOrderByColumn(strOrderByClause));
1062      }
1063      if (_params!=null)
1064        for (int i=0;i<_params.size();i++) addOrderByParameter(_params.elementAt(i), _params.elementAt(i));
1065    }
1066  }
1067
1068  public void setFilter(Vector JavaDoc<String JavaDoc> _fields, Vector JavaDoc<String JavaDoc> _params) {
1069    this.filter = new Vector JavaDoc<QueryFieldStructure>();
1070    this.paramFilter = new Vector JavaDoc<QueryParameterStructure>();
1071    if (_fields==null || _fields.size()==0) {
1072      if (this.internalFilter!=null) {
1073        for (int i=0;i<this.internalFilter.size();i++) {
1074          QueryFieldStructure aux = this.internalFilter.elementAt(i);
1075          addFilterField(aux.getField(), aux.getType());
1076        }
1077        if (this.paramInternalFilter!=null) {
1078          for (int i=0;i<this.paramInternalFilter.size();i++) {
1079            QueryParameterStructure aux = this.paramInternalFilter.elementAt(i);
1080            addFilterParameter(aux.getName(), aux.getField(), aux.getType());
1081          }
1082        }
1083      }
1084    } else {
1085      for (int i=0;i<_fields.size();i++) addFilterField(_fields.elementAt(i), "FILTER");
1086      if (_params!=null)
1087        for (int i=0;i<_params.size();i++) addFilterParameter(_params.elementAt(i), _params.elementAt(i), "FILTER");
1088    }
1089  }
1090
1091  public String JavaDoc getSQL() {
1092    return getSQL(null, null, null, null, null);
1093  }
1094
1095  public String JavaDoc getSQL(Vector JavaDoc<String JavaDoc> _FilterFields, Vector JavaDoc<String JavaDoc> _FilterParams, Vector JavaDoc<String JavaDoc> _OrderFields, Vector JavaDoc<String JavaDoc> _OrderParams, String JavaDoc selectFields) {
1096    StringBuffer JavaDoc text = new StringBuffer JavaDoc();
1097    boolean hasWhere = false;
1098    Vector JavaDoc<QueryFieldStructure> aux = null;
1099    if (selectFields==null || selectFields.equals("")) {
1100      aux = getSelectFields();
1101      if (aux!=null) {
1102        text.append("SELECT ");
1103        for (int i=0;i<aux.size();i++) {
1104          QueryFieldStructure auxStructure = aux.elementAt(i);
1105          if (i>0) text.append(", ");
1106          text.append(auxStructure.toString(true)).append(" ");
1107        }
1108        text.append(" \n");
1109      }
1110    } else {
1111      text.append("SELECT ").append(selectFields).append("\n");
1112    }
1113    
1114    aux = getFromFields();
1115    if (aux!=null) {
1116      StringBuffer JavaDoc txtAux = new StringBuffer JavaDoc();
1117      text.append("FROM ");
1118      for (int i=0;i<aux.size();i++) {
1119        QueryFieldStructure auxStructure = aux.elementAt(i);
1120        if (!txtAux.toString().equals("")) txtAux.append("left join ");
1121        txtAux.append(auxStructure.toString()).append(" \n");
1122      }
1123      text.append(txtAux.toString());
1124    }
1125    
1126    aux = getWhereFields();
1127    StringBuffer JavaDoc txtAuxWhere = new StringBuffer JavaDoc();
1128    if (aux!=null) {
1129      for (int i=0;i<aux.size();i++) {
1130        QueryFieldStructure auxStructure = aux.elementAt(i);
1131        hasWhere=true;
1132        if (!txtAuxWhere.toString().equals("")) txtAuxWhere.append("AND ");
1133        txtAuxWhere.append(auxStructure.toString()).append(" \n");
1134      }
1135    }
1136    setFilter(_FilterFields, _FilterParams);
1137    aux = getFilterFields();
1138    if (aux!=null) {
1139      for (int i=0;i<aux.size();i++) {
1140        QueryFieldStructure auxStructure = aux.elementAt(i);
1141        hasWhere=true;
1142        if (!txtAuxWhere.toString().equals("")) txtAuxWhere.append("AND ");
1143        txtAuxWhere.append(auxStructure.toString()).append(" \n");
1144      }
1145    }
1146    if (hasWhere) text.append("WHERE ").append(txtAuxWhere.toString());
1147    setOrderBy(_OrderFields, _OrderParams);
1148    aux = getOrderByFields();
1149    if (aux!=null) {
1150      StringBuffer JavaDoc txtAux = new StringBuffer JavaDoc();
1151      text.append("ORDER BY ");
1152      for (int i=0;i<aux.size();i++) {
1153        QueryFieldStructure auxStructure = aux.elementAt(i);
1154        if (!txtAux.toString().equals("")) txtAux.append(", ");
1155        txtAux.append(auxStructure.toString());
1156      }
1157      if (!txtAux.toString().equals("")) txtAux.append(", ");
1158      txtAux.append(getTableName()).append(".").append(getKeyColumn());
1159      text.append(txtAux.toString());
1160    }
1161    return text.toString();
1162  }
1163
1164  public String JavaDoc getTotalSQL() {
1165    StringBuffer JavaDoc text = new StringBuffer JavaDoc();
1166    Vector JavaDoc<QueryFieldStructure> aux = null;
1167    boolean hasWhere = false;
1168    text.append("SELECT COUNT(*) AS TOTAL ");
1169    
1170    aux = getFromFields();
1171    if (aux!=null) {
1172      StringBuffer JavaDoc txtAux = new StringBuffer JavaDoc();
1173      text.append("FROM ");
1174      for (int i=0;i<aux.size();i++) {
1175        QueryFieldStructure auxStructure = aux.elementAt(i);
1176        if (!txtAux.toString().equals("")) txtAux.append("left join ");
1177        txtAux.append(auxStructure.toString()).append(" \n");
1178      }
1179      text.append(txtAux.toString());
1180    }
1181    
1182    aux = getWhereFields();
1183    StringBuffer JavaDoc txtAuxWhere = new StringBuffer JavaDoc();
1184    if (aux!=null) {
1185      for (int i=0;i<aux.size();i++) {
1186        QueryFieldStructure auxStructure = aux.elementAt(i);
1187        hasWhere=true;
1188        if (!txtAuxWhere.toString().equals("")) txtAuxWhere.append("AND ");
1189        txtAuxWhere.append(auxStructure.toString()).append(" \n");
1190      }
1191    }
1192    aux = getFilterFields();
1193    if (aux!=null) {
1194      for (int i=0;i<aux.size();i++) {
1195        QueryFieldStructure auxStructure = aux.elementAt(i);
1196        hasWhere=true;
1197        if (!txtAuxWhere.toString().equals("")) txtAuxWhere.append("AND ");
1198        txtAuxWhere.append(auxStructure.toString()).append(" \n");
1199      }
1200    }
1201    if (hasWhere) text.append("WHERE ").append(txtAuxWhere.toString());
1202    
1203    return text.toString();
1204  }
1205}
1206
Popular Tags