1 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 > getOrderBy(VariablesSecureApp vars, TableSQLData tableSQL) throws Exception { 36 Vector<String > vOrderBy = new Vector<String >(); 37 StringBuffer orderBy = new StringBuffer (); 38 if (tableSQL==null) return vOrderBy; 39 String sortCols = vars.getInStringParameter("sort_cols"); 40 String 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 token = datas.nextToken(); 52 String 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 > filter, Vector<String > filterParams) throws Exception { 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 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 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 formatFilter(String tablename, String columnname, String reference, boolean first) { 117 if (columnname==null || columnname.equals("") || tablename==null || tablename.equals("") || reference==null || reference.equals("")) return ""; 118 StringBuffer text = new StringBuffer (); 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 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 { 142 Vector<QueryFieldStructure> vOrderBy = tableSQL.getOrderByFields(); 143 StringBuffer txtAux = new StringBuffer (); 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 > positions = tableSQL.getOrderByPosition(); 154 txtAux = new StringBuffer (); 155 if (positions!=null) { 156 for (int i=0;i<positions.size();i++) { 157 String 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 > directions = tableSQL.getOrderByDirection(); 166 txtAux = new StringBuffer (); 167 if (directions!=null) { 168 for (int i=0;i<directions.size();i++) { 169 String 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 generateSQL(ConnectionProvider conn, VariablesSecureApp vars, TableSQLData tableSQL, String selectFields, Vector<String > filter, Vector<String > filterParams) throws Exception { 178 Vector<String > orderBy = new Vector<String >(); 179 String loadSessionOrder = vars.getSessionValue(tableSQL.getTabID() + "|newOrder"); 180 if (loadSessionOrder==null || loadSessionOrder.equals("")) orderBy = getOrderBy(vars, tableSQL); 181 else { 182 String auxOrder = vars.getSessionValue(tableSQL.getTabID() + "|orderby"); 183 if (!auxOrder.equals("")) orderBy.addElement(auxOrder); 184 } 185 if (filter==null) filter = new Vector<String >(); 186 if (filterParams==null) filterParams = new Vector<String >(); 187 SQLReturnObject parametersData = getFilter(vars, tableSQL, filter, filterParams); 188 String parentKey = tableSQL.getParentColumnName(); 189 if (parentKey!=null && !parentKey.equals("")) { 190 String 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 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 |