KickJava   Java API By Example, From Geeks To Geeks.

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


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
20 package org.openbravo.erpCommon.utility;
21
22 import java.util.*;
23
24 import org.openbravo.database.ConnectionProvider;
25 import org.openbravo.base.secureApp.VariablesSecureApp;
26 import org.apache.log4j.Logger;
27
28
29 public class ModelSQLGeneration {
30   static Logger log4j = Logger.getLogger(ModelSQLGeneration.class);
31
32   public ModelSQLGeneration() {
33   }
34
35   private static Vector<String JavaDoc> getOrderBy(VariablesSecureApp vars, TableSQLData tableSQL) throws Exception JavaDoc {
36     Vector<String JavaDoc> vOrderBy = new Vector<String JavaDoc>();
37     StringBuffer JavaDoc orderBy = new StringBuffer JavaDoc();
38     if (tableSQL==null) return vOrderBy;
39     String JavaDoc sortCols = vars.getInStringParameter("sort_cols");
40     String JavaDoc sortDirs = vars.getInStringParameter("sort_dirs");
41     
42     if (log4j.isDebugEnabled()) log4j.debug("sort_cols: " + sortCols);
43     if (log4j.isDebugEnabled()) log4j.debug("sort_dirs: " + sortDirs);
44
45     if (sortCols != null && sortCols.length()>0){
46       if (sortCols.startsWith("(")) sortCols = sortCols.substring(1,sortCols.length()-1);
47       if (sortDirs.startsWith("(")) sortDirs = sortDirs.substring(1,sortDirs.length()-1);
48       StringTokenizer datas = new StringTokenizer(sortCols, " ,", false);
49       StringTokenizer dirs = new StringTokenizer(sortDirs, " ,", false);
50       while (datas.hasMoreTokens()) {
51         String JavaDoc token = datas.nextToken();
52         String JavaDoc tokenDir = dirs.nextToken();
53         if (token.startsWith("'")) token = token.substring(1, token.length()-1);
54         if (tokenDir.startsWith("'")) tokenDir = tokenDir.substring(1, tokenDir.length()-1);
55         token = token.trim();
56         tokenDir = tokenDir.trim();
57         if (!token.equals("")) {
58           vOrderBy.addElement(tableSQL.getTableName() + "." + token + " " + tokenDir);
59           if (!orderBy.toString().equals("")) orderBy.append(", ");
60           orderBy.append(tableSQL.getTableName()).append(".").append(token).append(" ").append(tokenDir);
61         }
62       }
63     }
64     vars.setSessionValue(tableSQL.getTabID() + "|orderby", orderBy.toString());
65     return vOrderBy;
66   }
67
68   private static SQLReturnObject getFilter(VariablesSecureApp vars, TableSQLData tableSQL, Vector<String JavaDoc> filter, Vector<String JavaDoc> filterParams) throws Exception JavaDoc {
69     SQLReturnObject result = new SQLReturnObject();
70     if (tableSQL==null) return result;
71     boolean isNewFilter = !vars.getStringParameter("newFilter").equals("");
72     Vector<Properties> filters = tableSQL.getFilteredStructure("IsSelectionColumn", "Y");
73     if (filters==null || filters.size()==0) filters = tableSQL.getFilteredStructure("IsIdentifier", "Y");
74     if (filters==null || filters.size()==0) return result;
75     if (isNewFilter) {
76       for (Enumeration<Properties> e = filters.elements();e.hasMoreElements();) {
77         Properties prop = e.nextElement();
78         String JavaDoc aux = vars.getRequestGlobalVariable("inpParam" + prop.getProperty("ColumnName"), tableSQL.getTabID() + "|param" + prop.getProperty("ColumnName"));
79         if (!aux.equals("")) {
80           filter.addElement(formatFilter(tableSQL.getTableName(), prop.getProperty("ColumnName"), prop.getProperty("AD_Reference_ID"), true));
81           filterParams.addElement("Param" + prop.getProperty("ColumnName"));
82           result.setData("Param" + prop.getProperty("ColumnName"), aux);
83         }
84         if (prop.getProperty("AD_Reference_ID").equals("15")) {
85           aux = vars.getRequestGlobalVariable("inpParam" + prop.getProperty("ColumnName") + "_f", tableSQL.getTabID() + "|param" + prop.getProperty("ColumnName") + "_f");
86           if (!aux.equals("")) {
87             filter.addElement(formatFilter(tableSQL.getTableName(), prop.getProperty("ColumnName"), prop.getProperty("AD_Reference_ID"), false));
88             filterParams.addElement("Param" + prop.getProperty("ColumnName") + "_f");
89             result.setData("Param" + prop.getProperty("ColumnName") + "_f", aux);
90           }
91         }
92       }
93     }
94     else {
95       for (Enumeration<Properties> e = filters.elements();e.hasMoreElements();) {
96         Properties prop = e.nextElement();
97         String JavaDoc aux = vars.getSessionValue(tableSQL.getTabID() + "|param" + prop.getProperty("ColumnName"));
98         if (!aux.equals("")) {
99           filter.addElement(formatFilter(tableSQL.getTableName(), prop.getProperty("ColumnName"), prop.getProperty("AD_Reference_ID"), true));
100           filterParams.addElement("Param" + prop.getProperty("ColumnName"));
101           result.setData("Param" + prop.getProperty("ColumnName"), aux);
102         }
103         if (prop.getProperty("AD_Reference_ID").equals("15")) {
104           aux = vars.getSessionValue(tableSQL.getTabID() + "|param" + prop.getProperty("ColumnName") + "_f");
105           if (!aux.equals("")) {
106             filter.addElement(formatFilter(tableSQL.getTableName(), prop.getProperty("ColumnName"), prop.getProperty("AD_Reference_ID"), false));
107             filterParams.addElement("Param" + prop.getProperty("ColumnName") + "_f");
108             result.setData("Param" + prop.getProperty("ColumnName") + "_f", aux);
109           }
110         }
111       }
112     }
113     return result;
114   }
115
116   private static String JavaDoc formatFilter(String JavaDoc tablename, String JavaDoc columnname, String JavaDoc reference, boolean first) {
117     if (columnname==null || columnname.equals("") || tablename==null || tablename.equals("") || reference==null || reference.equals("")) return "";
118     StringBuffer JavaDoc text = new StringBuffer JavaDoc();
119     if (reference.equals("15") || reference.equals("16") || reference.equals("24")) {
120       text.append("TO_DATE(").append(tablename).append(".").append(columnname).append((reference.equals("24")?", 'HH24:MI:SS'":"")).append(") ");
121       if (first) text.append(">= ");
122       else text.append("< ");
123       text.append("TO_DATE(?").append((reference.equals("24")?", 'HH24:MI:SS'":"")).append(")");
124     } else if (reference.equals("11") || reference.equals("12") || reference.equals("13") || reference.equals("22") || reference.equals("29") || reference.equals("800008") || reference.equals("800019")) {
125       text.append(tablename).append(".").append(columnname).append(" ");
126       if (first) text.append(">= ");
127       else text.append("< ");
128       text.append("?");
129     } else if (reference.equals("10") || reference.equals("14") || reference.equals("34")) {
130       String JavaDoc aux = "";
131       if (!columnname.equalsIgnoreCase("Value") && !columnname.equalsIgnoreCase("DocumentNo")) aux="C_IGNORE_ACCENT";
132       text.append(aux).append("(");
133       text.append(tablename).append(".").append(columnname).append(") LIKE ");
134       text.append(aux).append("(?)");
135     } else {
136       text.append(tablename).append(".").append(columnname).append(" = ?");
137     }
138     return text.toString();
139   }
140
141   private static void setSessionOrderBy(VariablesSecureApp vars, TableSQLData tableSQL) throws Exception JavaDoc {
142     Vector<QueryFieldStructure> vOrderBy = tableSQL.getOrderByFields();
143     StringBuffer JavaDoc txtAux = new StringBuffer JavaDoc();
144     if (vOrderBy!=null) {
145       for (int i=0;i<vOrderBy.size();i++) {
146         QueryFieldStructure auxStructure = vOrderBy.elementAt(i);
147         if (!txtAux.toString().equals("")) txtAux.append(", ");
148         txtAux.append(auxStructure.toString());
149       }
150     }
151     vars.setSessionValue(tableSQL.getTabID() + "|orderby", txtAux.toString());
152
153     Vector<String JavaDoc> positions = tableSQL.getOrderByPosition();
154     txtAux = new StringBuffer JavaDoc();
155     if (positions!=null) {
156       for (int i=0;i<positions.size();i++) {
157         String JavaDoc auxStructure = positions.elementAt(i);
158         auxStructure = Integer.toString((Integer.valueOf(auxStructure).intValue()+1));
159         if (!txtAux.toString().equals("")) txtAux.append(",");
160         txtAux.append(auxStructure);
161       }
162     }
163     vars.setSessionValue(tableSQL.getTabID() + "|orderbyPositions", txtAux.toString());
164
165     Vector<String JavaDoc> directions = tableSQL.getOrderByDirection();
166     txtAux = new StringBuffer JavaDoc();
167     if (directions!=null) {
168       for (int i=0;i<directions.size();i++) {
169         String JavaDoc auxStructure = directions.elementAt(i);
170         if (!txtAux.toString().equals("")) txtAux.append(",");
171         txtAux.append(auxStructure);
172       }
173     }
174     vars.setSessionValue(tableSQL.getTabID() + "|orderbyDirections", txtAux.toString());
175   }
176
177   public static String JavaDoc generateSQL(ConnectionProvider conn, VariablesSecureApp vars, TableSQLData tableSQL, String JavaDoc selectFields, Vector<String JavaDoc> filter, Vector<String JavaDoc> filterParams) throws Exception JavaDoc {
178     Vector<String JavaDoc> orderBy = new Vector<String JavaDoc>();
179     String JavaDoc loadSessionOrder = vars.getSessionValue(tableSQL.getTabID() + "|newOrder");
180     if (loadSessionOrder==null || loadSessionOrder.equals("")) orderBy = getOrderBy(vars, tableSQL);
181     else {
182       String JavaDoc auxOrder = vars.getSessionValue(tableSQL.getTabID() + "|orderby");
183       if (!auxOrder.equals("")) orderBy.addElement(auxOrder);
184     }
185     if (filter==null) filter = new Vector<String JavaDoc>();
186     if (filterParams==null) filterParams = new Vector<String JavaDoc>();
187     SQLReturnObject parametersData = getFilter(vars, tableSQL, filter, filterParams);
188     String JavaDoc parentKey = tableSQL.getParentColumnName();
189     if (parentKey!=null && !parentKey.equals("")) {
190       String JavaDoc aux = vars.getGlobalVariable("inpParentKey", tableSQL.getWindowID() + "|" + parentKey);
191       if (!aux.equals("")) {
192         if (parametersData==null) parametersData = new SQLReturnObject();
193         parametersData.setData("PARENT", aux);
194       }
195     }
196     String JavaDoc strSQL = tableSQL.getSQL(filter, filterParams, orderBy, null, selectFields);
197     setSessionOrderBy(vars, tableSQL);
198     Utility.fillTableSQLParameters(conn, vars, parametersData, tableSQL, tableSQL.getWindowID());
199    return strSQL;
200   }
201 }
202
Popular Tags