|
1
19 package org.openbravo.erpCommon.utility;
20
21 import org.openbravo.database.ConnectionProvider;
22 import org.openbravo.data.FieldProvider;
23 import org.openbravo.data.UtilSql;
24 import org.openbravo.base.secureApp.VariablesSecureApp;
25 import java.util.Hashtable  ;
26 import java.util.Vector ;
27 import java.sql.*;
28 import org.apache.log4j.Logger;
29
30
31 public class ComboTableData {
32 static Logger log4j = Logger.getLogger(ComboTableData.class);
33 private final String internalPrefix = "@@";
34 private static final String FIELD_CONCAT = " || ' - ' || ";
35 private static final String INACTIVE_DATA = "**";
36 private VariablesSecureApp vars;
37 private ConnectionProvider pool;
38 private Hashtable <String , String > parameters = new Hashtable <String , String >();
39 private Vector <QueryParameterStructure> paramSelect = new Vector <QueryParameterStructure>();
40 private Vector <QueryParameterStructure> paramFrom = new Vector <QueryParameterStructure>();
41 private Vector <QueryParameterStructure> paramWhere = new Vector <QueryParameterStructure>();
42 private Vector <QueryParameterStructure> paramOrderBy = new Vector <QueryParameterStructure>();
43 private Vector <QueryFieldStructure> select = new Vector <QueryFieldStructure>();
44 private Vector <QueryFieldStructure> from = new Vector <QueryFieldStructure>();
45 private Vector <QueryFieldStructure> where = new Vector <QueryFieldStructure>();
46 private Vector <QueryFieldStructure> orderBy = new Vector <QueryFieldStructure>();
47 private int index = 0;
48
49 public ComboTableData() {
50 }
51
52 public ComboTableData(ConnectionProvider _conn, String _referenceType, String _name, String _objectReference, String _validation, String _orgList, String _clientList, int _index) throws Exception {
53 this(null, _conn, _referenceType, _name, _objectReference, _validation, _orgList, _clientList, _index);
54 }
55
56 public ComboTableData(VariablesSecureApp _vars, ConnectionProvider _conn, String _referenceType, String _name, String _objectReference, String _validation, String _orgList, String _clientList, int _index) throws Exception {
57 if (_vars!=null) setVars(_vars);
58 setPool(_conn);
59 setReferenceType(_referenceType);
60 setObjectName(_name);
61 setObjectReference(_objectReference);
62 setValidation(_validation);
63 setOrgList(_orgList);
64 setClientList(_clientList);
65 setIndex(_index);
66 generateSQL();
67 parseNames();
68 }
69
70 public void setVars(VariablesSecureApp _vars) throws Exception {
71 if (_vars==null) throw new Exception ("The session vars is null");
72 this.vars = _vars;
73 }
74
75 public VariablesSecureApp getVars() {
76 return this.vars;
77 }
78
79 public void setPool(ConnectionProvider _conn) throws Exception {
80 if (_conn==null) throw new Exception ("The pool is null");
81 this.pool = _conn;
82 }
83
84 public ConnectionProvider getPool() {
85 return this.pool;
86 }
87
88 public void setReferenceType(String _reference) throws Exception {
89 if (_reference!=null && !_reference.equals("")) {
90 try {
91 Integer.valueOf(_reference).intValue();
92 } catch (Exception ignore) {
93 _reference = ComboTableQueryData.getReferenceID(getPool(), _reference, "D");
94 }
95 }
96 setParameter(internalPrefix + "reference", _reference);
97 }
98
99 public String getReferenceType() {
100 return getParameter(internalPrefix + "reference");
101 }
102
103 public void setObjectName(String _name) throws Exception {
104 setParameter(internalPrefix + "name", _name);
105 }
106
107 public String getObjectName() {
108 return getParameter(internalPrefix + "name");
109 }
110
111 public void setObjectReference(String _reference) throws Exception {
112 if (_reference!=null && !_reference.equals("")) {
113 try {
114 Integer.valueOf(_reference).intValue();
115 } catch (Exception ignore) {
116 _reference = ComboTableQueryData.getReferenceID(getPool(), _reference, (getReferenceType().equals("17")?"L":"T"));
117 }
118 }
119 setParameter(internalPrefix + "objectReference", _reference);
120 }
121
122 public String getObjectReference() {
123 return getParameter(internalPrefix + "objectReference");
124 }
125
126 public void setValidation(String _reference) throws Exception {
127 if (_reference!=null && !_reference.equals("")) {
128 try {
129 Integer.valueOf(_reference).intValue();
130 } catch (Exception ignore) {
131 _reference = ComboTableQueryData.getValidationID(getPool(), _reference);
132 }
133 }
134 setParameter(internalPrefix + "validation", _reference);
135 }
136
137 public String getValidation() {
138 return getParameter(internalPrefix + "validation");
139 }
140
141 public void setOrgList(String _orgList) throws Exception {
142 setParameter(internalPrefix + "orgList", _orgList);
143 }
144
145 public String getOrgList() {
146 return getParameter(internalPrefix + "orgList");
147 }
148
149 public void setClientList(String _clientList) throws Exception {
150 setParameter(internalPrefix + "clientList", _clientList);
151 }
152
153 public String getClientList() {
154 return getParameter(internalPrefix + "clientList");
155 }
156
157 public void addSelectField(String _field, String _alias) {
158 QueryFieldStructure p = new QueryFieldStructure(_field, " AS ", _alias, "SELECT");
159 if (this.select == null) this.select = new Vector <QueryFieldStructure>();
160 select.addElement(p);
161 }
162
163 public Vector <QueryFieldStructure> getSelectFields() {
164 return this.select;
165 }
166
167 public void addFromField(String _field, String _alias) {
168 QueryFieldStructure p = new QueryFieldStructure(_field, " ", _alias, "FROM");
169 if (this.from == null) this.from = new Vector <QueryFieldStructure>();
170 from.addElement(p);
171 }
172
173 public Vector <QueryFieldStructure> getFromFields() {
174 return this.from;
175 }
176
177 public void addWhereField(String _field, String _type) {
178 QueryFieldStructure p = new QueryFieldStructure(_field, "", "", _type);
179 if (this.where == null) this.where = new Vector <QueryFieldStructure>();
180 where.addElement(p);
181 }
182
183 public Vector <QueryFieldStructure> getWhereFields() {
184 return this.where;
185 }
186
187 public void addOrderByField(String _field) {
188 QueryFieldStructure p = new QueryFieldStructure(_field, "", "", "ORDERBY");
189 if (this.orderBy == null) this.orderBy = new Vector <QueryFieldStructure>();
190 orderBy.addElement(p);
191 }
192
193 public Vector <QueryFieldStructure> getOrderByFields() {
194 return this.orderBy;
195 }
196
197 public void addSelectParameter(String _parameter, String _fieldName) {
198 if (this.paramSelect == null) this.paramSelect = new Vector <QueryParameterStructure>();
199 QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "SELECT");
200 paramSelect.addElement(aux);
201 }
202
203 public Vector <QueryParameterStructure> getSelectParameters() {
204 return this.paramSelect;
205 }
206
207 public void addFromParameter(String _parameter, String _fieldName) {
208 if (this.paramFrom == null) this.paramFrom = new Vector <QueryParameterStructure>();
209 QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "FROM");
210 paramFrom.addElement(aux);
211 }
212
213 public Vector <QueryParameterStructure> getFromParameters() {
214 return this.paramFrom;
215 }
216
217 public void addWhereParameter(String _parameter, String _fieldName, String _type) {
218 if (this.paramWhere == null) this.paramWhere = new Vector <QueryParameterStructure>();
219 QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, _type);
220 paramWhere.addElement(aux);
221 }
222
223 public Vector <QueryParameterStructure> getWhereParameters() {
224 return this.paramWhere;
225 }
226
227 public void addOrderByParameter(String _parameter, String _fieldName) {
228 if (this.paramOrderBy == null) this.paramOrderBy = new Vector <QueryParameterStructure>();
229 QueryParameterStructure aux = new QueryParameterStructure(_parameter, _fieldName, "ORDERBY");
230 paramOrderBy.addElement(aux);
231 }
232
233 public Vector <QueryParameterStructure> getOrderByParameters() {
234 return this.paramOrderBy;
235 }
236
237 public void setParameter(String name, String value) throws Exception {
238 if (name==null || name.equals("")) throw new Exception ("Invalid parameter name");
239 if (this.parameters==null) this.parameters = new Hashtable <String , String >();
240 if (value==null || value.equals("")) this.parameters.remove(name.toUpperCase());
241 else this.parameters.put(name.toUpperCase(), value);
242 }
243
244 public String getParameter(String name) {
245 if (name==null || name.equals("")) return "";
246 else if (this.parameters==null) return "";
247 else return this.parameters.get(name.toUpperCase());
248 }
249
250 public Vector <String > getParameters() {
251 Vector <String > result = new Vector <String >();
252 if (log4j.isDebugEnabled()) log4j.debug("Obtaining parameters");
253 Vector <QueryParameterStructure> vAux = getSelectParameters();
254 if (vAux!=null) {
255 for (int i=0;i<vAux.size();i++) {
256 QueryParameterStructure aux = vAux.elementAt(i);
257 String strAux = getParameter(aux.getName());
258 if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
259 }
260 }
261 if (log4j.isDebugEnabled()) log4j.debug("Select parameters obtained");
262 vAux = getFromParameters();
263 if (vAux!=null) {
264 for (int i=0;i<vAux.size();i++) {
265 QueryParameterStructure aux = vAux.elementAt(i);
266 String strAux = getParameter(aux.getName());
267 if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
268 }
269 }
270 if (log4j.isDebugEnabled()) log4j.debug("From parameters obtained");
271 vAux = getWhereParameters();
272 if (vAux!=null) {
273 for (int i=0;i<vAux.size();i++) {
274 QueryParameterStructure aux = vAux.elementAt(i);
275 String strAux = getParameter(aux.getName());
276 if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
277 }
278 }
279 if (log4j.isDebugEnabled()) log4j.debug("Where parameters obtained");
280 vAux = getOrderByParameters();
281 if (vAux!=null) {
282 for (int i=0;i<vAux.size();i++) {
283 QueryParameterStructure aux = vAux.elementAt(i);
284 String strAux = getParameter(aux.getName());
285 if (strAux==null || strAux.equals("")) result.addElement(aux.getName());
286 }
287 }
288 if (log4j.isDebugEnabled()) log4j.debug("Order by parameters obtained");
289 result.addElement("#AD_LANGUAGE");
290 return result;
291 }
292
293 public void setIndex(int _index) {
294 this.index = _index;
295 }
296
297 public int getIndex() {
298 return this.index;
299 }
300
301 public void generateSQL() throws Exception {
302 if (getPool()==null) throw new Exception ("No pool defined for database connection");
303 else if (getReferenceType().equals("")) throw new Exception ("No reference type defined");
304
305 identifier("", null);
306 }
307
308 private void setListQuery(String tableName, String fieldName, String referenceValue) throws Exception {
309 int myIndex = this.index++;
310 addSelectField("td" + myIndex + ".value", "id");
311 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))", "NAME");
312 addSelectField("(CASE WHEN td_trl" + myIndex + ".description IS NULL THEN td" + myIndex + ".description ELSE td_trl" + myIndex + ".description END)", "DESCRIPTION");
313 String tables = "ad_ref_list td" + myIndex;
314 if (tableName!=null && tableName.length()!=0 && fieldName!=null && fieldName.length()!=0) tables += " on " + tableName + "." + fieldName + " = td" + myIndex + ".value ";
315 addFromField(tables, "td" + myIndex);
316 addFromField("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);
317 addFromParameter("#AD_LANGUAGE", "LANGUAGE");
318 addWhereField("td" + myIndex + ".ad_reference_id = ?", "KEY");
319 if (referenceValue==null || referenceValue.equals("")) {
320 addWhereParameter("AD_REFERENCE_ID", "KEY", "KEY");
321 setParameter("AD_REFERENCE_ID", getObjectReference());
322 } else {
323 addWhereParameter("TD" + myIndex + ".AD_REFERENCE_ID", "KEY", "KEY");
324 setParameter("TD" + myIndex + ".AD_REFERENCE_ID", referenceValue);
325 }
326 if (tableName==null || tableName.length()==0) {
327 addWhereField("(td" + myIndex + ".isActive = 'Y' OR td" + myIndex + ".Value = ? )", "ISACTIVE");
328 addWhereParameter("@ACTUAL_VALUE@", "ACTUAL_VALUE", "ISACTIVE");
329 }
330 addOrderByField("(CASE WHEN td_trl" + myIndex + ".name IS NULL THEN td" + myIndex + ".name ELSE td_trl" + myIndex + ".name END)");
331 }
332
333 private void setTableQuery(String tableName, String fieldName, String referenceValue) throws Exception {
334 int myIndex = this.index++;
335 ComboTableQueryData trd[] = ComboTableQueryData.selectRefTable(getPool(), ((referenceValue!=null && !referenceValue.equals(""))?referenceValue:getObjectReference()));
336 if (trd==null || trd.length==0) return;
337 addSelectField("td" + myIndex + "." + trd[0].keyname, "ID");
338 if (trd[0].isvaluedisplayed.equals("Y")) addSelectField("td" + myIndex + ".VALUE", "NAME");
339 ComboTableQueryData fieldsAux = new ComboTableQueryData();
340 fieldsAux.name = trd[0].name;
341 fieldsAux.tablename = trd[0].tablename;
342 fieldsAux.reference = trd[0].reference;
343 fieldsAux.referencevalue = trd[0].referencevalue;
344 fieldsAux.required = trd[0].required;
345 String tables = trd[0].tablename + " td" + myIndex;
346 if (tableName!=null && !tableName.equals("") && fieldName!=null && !fieldName.equals("")) {
347 tables += " on " + tableName + "." + fieldName + " = td" + myIndex + "." + trd[0].keyname + " \n";
348 tables += "AND td" + myIndex + ".AD_Client_ID IN (" + getClientList() + ") \n";
349 tables += "AND td" + myIndex + ".AD_Org_ID IN (" + getOrgList() + ")";
350 } else {
351 addWhereField("td" + myIndex + ".AD_Client_ID IN (" + getClientList() + ")", "CLIENT_LIST");
352 addWhereField("td" + myIndex + ".AD_Org_ID IN (" + getOrgList() + ")", "ORG_LIST");
353 }
354 addFromField(tables, "td" + myIndex);
355 String strSQL = trd[0].whereclause;
356 if (strSQL==null) strSQL = "";
357
358 if (!strSQL.equals("")) {
359 if (strSQL.indexOf("@")!=-1) strSQL = parseContext(strSQL, "WHERE");
360 addWhereField(strSQL, "FILTER");
361 }
362 if (tableName==null || tableName.equals("")) {
363 parseValidation();
364 addWhereField("(td" + myIndex + ".isActive = 'Y' OR td" + myIndex + "." + trd[0].keyname + " = ? )", "ISACTIVE");
365 addWhereParameter("@ACTUAL_VALUE@", "ACTUAL_VALUE", "ISACTIVE");
366 }
367 String orderByAux = (trd[0].orderbyclause.equals("")?"2":trd[0].orderbyclause);
368 if (orderByAux.indexOf("@")!=-1) orderByAux = parseContext(orderByAux, "ORDERBY");
369 identifier("td" + myIndex, fieldsAux);
370 addOrderByField(orderByAux);
371 }
372
373 private void setTableDirQuery(String tableName, String fieldName, String parentFieldName) throws Exception {
374 int myIndex = this.index++;
375 String name = ((fieldName!=null && !fieldName.equals(""))?fieldName:getObjectName());
376 String tableDirName = name.substring(0,name.length()-3);
377 ComboTableQueryData trd[] = ComboTableQueryData.identifierColumns(getPool(), tableDirName);
378 addSelectField("td" + myIndex + "." + name, "ID");
379
380 String tables = tableDirName + " td" + myIndex;
381 if (tableName!=null && !tableName.equals("") && parentFieldName!=null && !parentFieldName.equals("")) {
382 tables += " on " + tableName + "." + parentFieldName + " = td" + myIndex + "." + name + "\n";
383 tables += "AND td" + myIndex + ".AD_Client_ID IN (" + getClientList() + ") \n";
384 tables += "AND td" + myIndex + ".AD_Org_ID IN (" + getOrgList() + ")";
385 } else {
386 addWhereField("td" + myIndex + ".AD_Client_ID IN (" + getClientList() + ")", "CLIENT_LIST");
387 addWhereField("td" + myIndex + ".AD_Org_ID IN (" + getOrgList() + ")", "ORG_LIST");
388 }
389 addFromField(tables, "td" + myIndex);
390 if (tableName==null || tableName.equals("")) {
391 parseValidation();
392 addWhereField("(td" + myIndex + ".isActive = 'Y' OR td" + myIndex + "." + name + " = ? )", "ISACTIVE");
393 addWhereParameter("@ACTUAL_VALUE@", "ACTUAL_VALUE", "ISACTIVE");
394 }
395 for (int i=0;i<trd.length;i++) identifier("td" + myIndex, trd[i]);
396 addOrderByField("2");
397 }
398
399 public void parseNames() {
400 Vector <QueryFieldStructure> tables = getFromFields();
401 if (tables==null || tables.size()==0) return;
402 if (where!=null && where.size()>0) {
403 for (int i=0;i<where.size();i++) {
404 QueryFieldStructure auxStructure = where.elementAt(i);
405 if (auxStructure.getType().equalsIgnoreCase("FILTER")) {
406 String strAux = auxStructure.getField();
407 for (int j=0; j<tables.size(); j++) {
408 QueryFieldStructure auxTable = tables.elementAt(j);
409 String strTable = auxTable.getField();
410 int p = strTable.indexOf(" ");
411 if (p!=-1) strTable = strTable.substring(0, p).trim();
412 strAux = replaceIgnoreCase(strAux, strTable + ".", auxTable.getAlias() + ".");
413 }
414 if (!strAux.equalsIgnoreCase(auxStructure.getField())) {
415 auxStructure.setField(strAux);
416 if (log4j.isDebugEnabled()) log4j.debug("Field replaced: " + strAux);
417 where.set(i, auxStructure);
418 }
419 }
420 }
421 }
422 if (orderBy!=null && orderBy.size()>0) {
423 for (int i=0;i<orderBy.size();i++) {
424 QueryFieldStructure auxStructure = orderBy.elementAt(i);
425 String strAux = auxStructure.getField();
426 for (int j=0; j<tables.size(); j++) {
427 QueryFieldStructure auxTable = tables.elementAt(j);
428 String strTable = auxTable.getField();
429 int p = strTable.indexOf(" ");
430 if (p!=-1) strTable = strTable.substring(0, p).trim();
431 strAux = replaceIgnoreCase(strAux, strTable + ".", auxTable.getAlias() + ".");
432 }
433 if (!strAux.equalsIgnoreCase(auxStructure.getField())) {
434 auxStructure.setField(strAux);
435 if (log4j.isDebugEnabled()) log4j.debug("Field replaced: " + strAux);
436 orderBy.set(i, auxStructure);
437 }
438 }
439 }
440 }
441
442 private String replaceIgnoreCase(String data, String replaceWhat, String replaceWith) {
443 if (data==null || data.equals("")) return "";
444 if (log4j.isDebugEnabled()) log4j.debug("parsing data: " + data + " - replace: " + replaceWhat + " - with: " + replaceWith);
445 StringBuffer text = new StringBuffer ();
446 int i = data.toUpperCase().indexOf(replaceWhat.toUpperCase());
447 while (i!=-1) {
448 text.append(data.substring(0, i)).append(replaceWith);
449 data = data.substring(i+replaceWhat.length());
450 i = data.toUpperCase().indexOf(replaceWhat.toUpperCase());
451 }
452 text.append(data);
453 return text.toString();
454 }
455
456 private void parseValidation() throws Exception {
457 if (getValidation()==null || getValidation().equals("")) return;
458 if (log4j.isDebugEnabled()) log4j.debug("Validation id: " + getValidation());
459 String val = ComboTableQueryData.getValidation(getPool(), getValidation());
460 if (log4j.isDebugEnabled()) log4j.debug("Validation text: " + val);
461 if (val.indexOf("@")!=-1) val = parseContext(val, "WHERE");
462 if (!val.equals("")) addWhereField(val, "FILTER");
463 if (log4j.isDebugEnabled()) log4j.debug("Validation parsed: " + val);
464 }
465
466 public String parseContext(String context, String type) {
467 if (context==null || context.equals("")) return "";
468 StringBuffer strOut = new StringBuffer ();
469 String value = new String (context);
470 String token, defStr;
471 int i = value.indexOf("@");
472 while (i!=-1) {
473 strOut.append(value.substring(0,i));
474 value = value.substring(i+1);
475 int j=value.indexOf("@");
476 if (j==-1) {
477 strOut.append(value);
478 return strOut.toString();
479 }
480 token = value.substring(0, j);
481 if (token.equalsIgnoreCase("#User_Client")) defStr=getClientList();
482 else if (token.equalsIgnoreCase("#User_Org")) defStr=getOrgList();
483 else defStr="?";
484
485 if (defStr.equals("?")) {
486 if (type.equalsIgnoreCase("WHERE")) addWhereParameter(token, "FILTER", "FILTER");
487 else if (type.equalsIgnoreCase("ORDERBY")) addOrderByParameter(token, "FILTER");
488 }
489 strOut.append(defStr);
490 value=value.substring(j+1);
491 i=value.indexOf("@");
492 }
493 strOut.append(value);
494 return strOut.toString().replace("'?'","?");
495 }
496
497 public void identifier(String tableName, FieldProvider field) throws Exception {
498 String reference;
499 if (field==null) reference = getReferenceType();
500 else reference = field.getField("reference");
501 switch (Integer.valueOf(reference).intValue()) {
502 case 17: setListQuery(tableName, ((field==null)?"":field.getField("name")), ((field==null)?"":field.getField("referencevalue")));
504 break;
505 case 18: setTableQuery(tableName, ((field==null)?"":field.getField("name")), ((field==null)?"":field.getField("referencevalue")));
507 break;
508 case 19: setTableDirQuery(tableName, ((field==null)?"":field.getField("name")), ((field==null)?"":field.getField("name")));
510 break;
511 case 30: setTableDirQuery(tableName, ((field==null)?"":field.getField("name")), ((field==null)?"":field.getField("name")));
513 break;
514 case 31: setTableDirQuery(tableName, "M_Locator_ID", ((field==null)?getObjectName():field.getField("name")));
516 break;
517 case 35:
518 setTableDirQuery(tableName, ((field==null)?"":field.getField("name")), ((field==null)?"":field.getField("name")));
519 break;
520 case 25: setTableDirQuery(tableName, "C_ValidCombination_ID", ((field==null)?getObjectName():field.getField("name")));
522 break;
523 case 800011: setTableDirQuery(tableName, "M_Product_ID", ((field==null)?getObjectName():field.getField("name")));
525 break;
526 default:
527 if (!checkTableTranslation(tableName, field, reference)) {
528 addSelectField(formatField((((tableName!=null && tableName.length()!=0)?(tableName + "."):"") + field.getField("name")), reference), "NAME");
529 }
530 break;
531 }
532 }
533
534 private boolean checkTableTranslation(String tableName, FieldProvider field, String reference) throws Exception {
535 if (tableName==null || tableName.equals("") || field==null) return false;
536 ComboTableQueryData[] data = ComboTableQueryData.selectTranslatedColumn(getPool(), field.getField("tablename"), field.getField("name"));
537 if (data==null || data.length==0) return false;
538 int myIndex = this.index++;
539 addSelectField("(CASE WHEN td_trl" + myIndex + "." + data[0].columnname + " IS NULL THEN " + formatField((tableName + "." + field.getField("name")), reference) + " ELSE " + formatField(("td_trl" + myIndex + "." + data[0].columnname), reference) + " END)", "NAME");
540 addFromField(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);
541 addFromParameter("#AD_LANGUAGE", "LANGUAGE");
542 return true;
543 }
544
545 private String formatField(String field, String reference) {
546 String result = "";
547 if (field==null || field.length()==0) return "";
548 else if (reference==null || reference.length()==0) return field;
549 switch (Integer.valueOf(reference).intValue()) {
550 case 11: result = "CAST(" + field + " AS INTEGER)";
552 break;
553 case 12: case 22: case 26: case 29: case 800008: case 800019: result = "TO_NUMBER(" + field + ")";
560 break;
561 case 15: result = "TO_CHAR(" + field + (getVars()==null?"":(", '" + getVars().getSessionValue("#AD_SqlDateFormat") + "'")) + ")";
563 break;
564 case 16: result = "TO_CHAR(" + field + ")";
566 break;
567 case 24: result = "TO_CHAR(" + field + ", 'HH24:MI:SS')";
569 break;
570 default:
571 result = "TO_CHAR(" + field + ")";
572 break;
573 }
574 return result;
575 }
576
577 public String getQuery(boolean onlyId, String [] discard) {
578 StringBuffer text = new StringBuffer ();
579 Vector <QueryFieldStructure> aux = getSelectFields();
580 String idName = "";
581 boolean hasWhere = false;
582 if (aux!=null) {
583 StringBuffer name = new StringBuffer ();
584 String description = "";
585 String id = "";
586 text.append("SELECT ");
587 for (int i=0;i<aux.size();i++) {
588 QueryFieldStructure auxStructure = aux.elementAt(i);
589 if (!isInArray(discard, auxStructure.getType())) {
590 if (auxStructure.getData("alias").equalsIgnoreCase("ID")) {
591 if (id.equals("")) {
592 id = auxStructure.toString(true);
593 idName = auxStructure.toString();
594 }
595 } else if (auxStructure.getData("alias").equalsIgnoreCase("DESCRIPTION")) {
596 if (description.equals("")) description = auxStructure.toString(true);
597 } else {
598 if (name.toString().equals("")) name.append("(");
599 else name.append(FIELD_CONCAT);
600 name.append(auxStructure.toString());
601 }
602 }
603 }
604 if (!name.toString().equals("")) name.append(") AS NAME");
605 text.append(id).append(", ").append(name.toString());
606 if (description!=null && !description.equals("")) text.append(", ").append(description);
607 else text.append(", '' AS DESCRIPTION");
608 text.append(" \n");
609 }
610
611 aux = getFromFields();
612 if (aux!=null) {
613 StringBuffer txtAux = new StringBuffer ();
614 text.append("FROM ");
615 for (int i=0;i<aux.size();i++) {
616 QueryFieldStructure auxStructure = aux.elementAt(i);
617 if (!isInArray(discard, auxStructure.getType())) {
618 if (!txtAux.toString().equals("")) txtAux.append("left join ");
619 txtAux.append(auxStructure.toString()).append(" \n");
620 }
621 }
622 text.append(txtAux.toString());
623 }
624
625 aux = getWhereFields();
626 if (aux!=null) {
627 StringBuffer txtAux = new StringBuffer ();
628 for (int i=0;i<aux.size();i++) {
629 QueryFieldStructure auxStructure = aux.elementAt(i);
630 if (!isInArray(discard, auxStructure.getType())) {
631 hasWhere=true;
632 if (!txtAux.toString().equals("")) txtAux.append("AND ");
633 txtAux.append(auxStructure.toString()).append(" \n");
634 }
635 }
636 if (hasWhere) text.append("WHERE ").append(txtAux.toString());
637 }
638
639 if (!onlyId) {
640 aux = getOrderByFields();
641 if (aux!=null) {
642 StringBuffer txtAux = new StringBuffer ();
643 text.append("ORDER BY ");
644 for (int i=0;i<aux.size();i++) {
645 QueryFieldStructure auxStructure = aux.elementAt(i);
646 if (!isInArray(discard, auxStructure.getType())) {
647 if (!txtAux.toString().equals("")) txtAux.append(", ");
648 txtAux.append(auxStructure.toString());
649 }
650 }
651 text.append(txtAux.toString());
652 }
653 } else {
654 if (!hasWhere) text.append("WHERE ");
655 else text.append("AND ");
656 text.append(idName).append(" = ? ");
657 }
658 return text.toString();
659 }
660
661 private boolean isInArray(String [] data, String element) {
662 if (data==null || data.length==0 || element==null || element.equals("")) return false;
663 for (int i=0; i<data.length;i++) {
664 if (data[i].equalsIgnoreCase(element)) return true;
665 }
666 return false;
667 }
668
669 private int setSQLParameters(PreparedStatement st, int iParameter, String [] discard) {
670 Vector <QueryParameterStructure> vAux = getSelectParameters();
671 if (vAux!=null) {
672 for (int i=0;i<vAux.size();i++) {
673 QueryParameterStructure aux = vAux.elementAt(i);
674 if (!isInArray(discard, aux.getType())) {
675 String strAux = getParameter(aux.getName());
676 if (log4j.isDebugEnabled()) log4j.debug("Parameter - " + iParameter + " - " + aux.getName() + ": " + strAux);
677 UtilSql.setValue(st, ++iParameter, 12, null, strAux);
678 }
679 }
680 }
681 vAux = getFromParameters();
682 if (vAux!=null) {
683 for (int i=0;i<vAux.size();i++) {
684 QueryParameterStructure aux = vAux.elementAt(i);
685 if (!isInArray(discard, aux.getType())) {
686 String strAux = getParameter(aux.getName());
687 if (log4j.isDebugEnabled()) log4j.debug("Parameter - " + iParameter + " - " + aux.getName() + ": " + strAux);
688 UtilSql.setValue(st, ++iParameter, 12, null, strAux);
689 }
690 }
691 }
692 vAux = getWhereParameters();
693 if (vAux!=null) {
694 for (int i=0;i<vAux.size();i++) {
695 QueryParameterStructure aux = vAux.elementAt(i);
696 if (!isInArray(discard, aux.getType())) {
697 String strAux = getParameter(aux.getName());
698 if (log4j.isDebugEnabled()) log4j.debug("Parameter - " + iParameter + " - " + aux.getName() + ": " + strAux);
699 UtilSql.setValue(st, ++iParameter, 12, null, strAux);
700 }
701 }
702 }
703 vAux = getOrderByParameters();
704 if (vAux!=null) {
705 for (int i=0;i<vAux.size();i++) {
706 QueryParameterStructure aux = vAux.elementAt(i);
707 if (!isInArray(discard, aux.getType())) {
708 String strAux = getParameter(aux.getName());
709 if (log4j.isDebugEnabled()) log4j.debug("Parameter - " + iParameter + " - " + aux.getName() + ": " + strAux);
710 UtilSql.setValue(st, ++iParameter, 12, null, strAux);
711 }
712 }
713 }
714 return iParameter;
715 }
716
717 public FieldProvider[] select(boolean includeActual) throws Exception {
718 String strSql = getQuery(false, null);
719 if (log4j.isDebugEnabled()) log4j.debug("SQL: " + strSql);
720 PreparedStatement st = getPool().getPreparedStatement(strSql);
721 ResultSet result;
722 Vector <Object > vector = new Vector <Object >(0);
723
724 try {
725 int iParameter = 0;
726 iParameter = setSQLParameters(st, iParameter, null);
727 boolean idFound = false;
728 String actual = getParameter("@ACTUAL_VALUE@");
729 result = st.executeQuery();
730 while(result.next()) {
731 SQLReturnObject sqlReturnObject = new SQLReturnObject();
732 sqlReturnObject.setData("ID", UtilSql.getValue(result, "ID"));
733 sqlReturnObject.setData("NAME", UtilSql.getValue(result, "NAME"));
734 sqlReturnObject.setData("DESCRIPTION", UtilSql.getValue(result, "DESCRIPTION"));
735 if (includeActual && actual!=null && !actual.equals("")) {
736 if (actual.equals(sqlReturnObject.getData("ID"))) {
737 if (!idFound) {
738 vector.addElement(sqlReturnObject);
739 idFound=true;
740 }
741 } else vector.addElement(sqlReturnObject);
742 } else vector.addElement(sqlReturnObject);
743 }
744 result.close();
745
746 if (includeActual && actual!=null && !actual.equals("") && !idFound) {
747 getPool().releasePreparedStatement(st);
748 String [] discard = {"filter", "orderBy", "CLIENT_LIST", "ORG_LIST"};
749 strSql = getQuery(true, discard);
750 if (log4j.isDebugEnabled()) log4j.debug("SQL Actual ID: " + strSql);
751 st = getPool().getPreparedStatement(strSql);
752 iParameter = setSQLParameters(st, 0, discard);
753 UtilSql.setValue(st, ++iParameter, 12, null, actual);
754 result = st.executeQuery();
755 while(result.next()) {
756 SQLReturnObject sqlReturnObject = new SQLReturnObject();
757 sqlReturnObject.setData("ID", UtilSql.getValue(result, "ID"));
758 String strName = UtilSql.getValue(result, "NAME");
759 if (!strName.startsWith(INACTIVE_DATA)) strName = INACTIVE_DATA + strName;
760 sqlReturnObject.setData("NAME", strName);
761 vector.addElement(sqlReturnObject);
762 idFound = true;
763 }
764 result.close();
765 if (!idFound) {
766 SQLReturnObject sqlReturnObject = new SQLReturnObject();
767 sqlReturnObject.setData("ID", actual);
768 sqlReturnObject.setData("NAME", INACTIVE_DATA + Utility.messageBD(getPool(), "NotFound", getParameter("#AD_LANGUAGE")));
769 vector.addElement(sqlReturnObject);
770 }
771 }
772 } catch(SQLException e){
773 log4j.error("Error of SQL in query: " + strSql + "Exception:"+ e);
774 throw new Exception (Integer.toString(e.getErrorCode()));
775 } finally {
776 getPool().releasePreparedStatement(st);
777 }
778 FieldProvider objectListData[] = new FieldProvider[vector.size()];
779 vector.copyInto(objectListData);
780 return(objectListData);
781 }
782 }
783 |
Popular Tags |
Java API By Example, From Geeks To Geeks.