KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > openbravo > erpCommon > ad_forms > SQLExecutor


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.ad_forms;
20
21 import org.openbravo.erpCommon.utility.*;
22 import org.openbravo.erpCommon.businessUtility.WindowTabs;
23 import org.openbravo.base.secureApp.*;
24 import org.openbravo.xmlEngine.XmlDocument;
25 import java.io.*;
26 import javax.servlet.*;
27 import javax.servlet.http.*;
28
29
30
31 public class SQLExecutor extends HttpSecureAppServlet {
32   
33
34   public void doPost (HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
35     VariablesSecureApp vars = new VariablesSecureApp(request);
36
37     if (!Utility.hasFormAccess(this, vars, "", "org.openbravo.erpCommon.ad_forms.SQLExecutor")) {
38       bdError(response, "AccessTableNoView", vars.getLanguage());
39       return;
40     }
41
42     if (vars.commandIn("DEFAULT")) {
43       String JavaDoc strSQL = vars.getGlobalVariable("inpSQL", "SQLExecutor|sql", "");
44       SQLExecutor_Query sql = null;
45       SQLExecutor_Query[] data = null;
46       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
47       int intRecordRange = (strRecordRange.equals("")?0:Integer.parseInt(strRecordRange));
48       String JavaDoc strInitRecord = vars.getSessionValue("SQLExecutor|initRecordNumber");
49       int initRecordNumber = (strInitRecord.equals("")?0:Integer.parseInt(strInitRecord));
50       try {
51         if (!strSQL.toUpperCase().trim().startsWith("SELECT ")) {
52           vars.removeSessionValue("SQLExecutor|sql");
53           throw new ServletException("Invalid SQL statement");
54         }
55         data = SQLExecutor_Query.select(this, strSQL, initRecordNumber, intRecordRange);
56       } catch (Exception JavaDoc ignored) {}
57       printPage(response, vars, strSQL, data, strInitRecord, initRecordNumber, intRecordRange);
58     } else if (vars.commandIn("FIND")) {
59       String JavaDoc strSQL = vars.getRequestGlobalVariable("inpSQL", "SQLExecutor|sql");
60       SQLExecutor_Query[] data = null;
61       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
62       int intRecordRange = (strRecordRange.equals("")?0:Integer.parseInt(strRecordRange));
63       vars.setSessionValue("SQLExecutor|initRecordNumber", "0");
64       int initRecordNumber = 0;
65       try {
66         if (!strSQL.toUpperCase().trim().startsWith("SELECT ")) {
67           vars.removeSessionValue("SQLExecutor|sql");
68           throw new ServletException("Invalid SQL statement");
69         }
70         data = SQLExecutor_Query.select(this, strSQL, initRecordNumber, intRecordRange);
71       } catch (Exception JavaDoc ex) {
72         ex.printStackTrace();
73         vars.setSessionValue("SQLExecutor|message", Utility.messageBD(this, "Error", vars.getLanguage()));
74       }
75       log4j.debug("sql: "+strSQL);
76       printPage(response, vars, strSQL, data, "0", initRecordNumber, intRecordRange);
77     } else if (vars.commandIn("RELATION_XLS")){
78       String JavaDoc strSQL = vars.getGlobalVariable("inpSQL", "SQLExecutor|sql", "");
79       vars.setSessionValue("SQLExecutor|sql", strSQL);
80       SQLExecutor_Query[] data = null;
81       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
82       int intRecordRange = (strRecordRange.equals("")?0:Integer.parseInt(strRecordRange));
83       vars.setSessionValue("SQLExecutor|initRecordNumber", "0");
84       int initRecordNumber = 0;
85       try {
86     if (!strSQL.toUpperCase().trim().startsWith("SELECT ")) {
87         vars.removeSessionValue("SQLExecutor|sql");
88       throw new ServletException("Invalid SQL statement");
89       }
90       data = SQLExecutor_Query.select(this, strSQL, initRecordNumber, intRecordRange);
91       } catch (Exception JavaDoc ex) {
92       ex.printStackTrace();
93       vars.setSessionValue("SQLExecutor|message", Utility.messageBD(this, "Error", vars.getLanguage()));
94       }
95       log4j.debug("sql: "+strSQL);
96       printExcel(response, vars, strSQL, data);
97     } else if (vars.commandIn("FIRST_RELATION")) {
98       vars.setSessionValue("SQLExecutor|initRecordNumber", "0");
99       response.sendRedirect(strDireccion + request.getServletPath() + "?Command=DEFAULT");
100     } else if (vars.commandIn("PREVIOUS_RELATION")) {
101       String JavaDoc strInitRecord = vars.getSessionValue("SQLExecutor|initRecordNumber");
102       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
103       int intRecordRange = strRecordRange.equals("")?0:Integer.parseInt(strRecordRange);
104       if (strInitRecord.equals("") || strInitRecord.equals("0")) {
105         vars.setSessionValue("SQLExecutor|initRecordNumber", "0");
106       } else {
107         int initRecord = (strInitRecord.equals("")?0:Integer.parseInt(strInitRecord));
108         initRecord -= intRecordRange;
109         strInitRecord = ((initRecord<0)?"0":Integer.toString(initRecord));
110         vars.setSessionValue("SQLExecutor|initRecordNumber", strInitRecord);
111       }
112       response.sendRedirect(strDireccion + request.getServletPath() + "?Command=DEFAULT");
113     } else if (vars.commandIn("NEXT_RELATION")) {
114       String JavaDoc strInitRecord = vars.getSessionValue("SQLExecutor|initRecordNumber");
115       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
116       int intRecordRange = strRecordRange.equals("")?0:Integer.parseInt(strRecordRange);
117       int initRecord = (strInitRecord.equals("")?0:Integer.parseInt(strInitRecord));
118       if (initRecord==0) initRecord=1;
119       initRecord += intRecordRange;
120       strInitRecord = ((initRecord<0)?"0":Integer.toString(initRecord));
121       vars.setSessionValue("SQLExecutor|initRecordNumber", strInitRecord);
122       response.sendRedirect(strDireccion + request.getServletPath() + "?Command=DEFAULT");
123     } else if (vars.commandIn("LAST_RELATION")) {
124       String JavaDoc strRecordRange = Utility.getContext(this, vars, "#RecordRange", "SQLExecutor");
125       String JavaDoc strSQL = vars.getGlobalVariable("inpSQL", "SQLExecutor|sql", "");
126       String JavaDoc strInitRecord = lastRange(vars, strSQL, strRecordRange);
127       vars.setSessionValue("SQLExecutor|initRecordNumber", strInitRecord);
128       response.sendRedirect(strDireccion + request.getServletPath() + "?Command=DEFAULT");
129     } else pageError(response);
130   }
131
132   private String JavaDoc lastRange(VariablesSecureApp vars, String JavaDoc strSQL, String JavaDoc strRecordRange) {
133     SQLExecutor_Query[] data = null;
134     int initRecord = 0;
135     try {
136       data = SQLExecutor_Query.select(this, strSQL);
137     } catch (Exception JavaDoc ex) {
138       ex.printStackTrace();
139       return "0";
140     }
141     while (initRecord<data.length) {
142       initRecord += Integer.parseInt(strRecordRange);
143     }
144     initRecord -= Integer.parseInt(strRecordRange);
145     if (initRecord<0) initRecord = 0;
146     return Integer.toString(initRecord);
147
148   }
149   private void printExcel(HttpServletResponse response, VariablesSecureApp vars, String JavaDoc strSQL, SQLExecutor_Query[] data) throws IOException, ServletException{
150     log4j.info("print page");
151     if (log4j.isDebugEnabled()) log4j.debug("printPage - Reading xml\n");
152     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/ad_forms/SQLExecutor_Excel").createXmlDocument();
153
154     SQLExecutorData[] dataHeader = null;
155     StringBuffer JavaDoc dataBuffer = new StringBuffer JavaDoc();
156     if (data!=null && data.length != 0) {
157         log4j.debug("data != null || data.length != 0");
158     dataHeader = new SQLExecutorData[data[0].name.size()];
159         for (int i = 0; i<data[0].name.size(); i++){
160         log4j.debug("data[0].name: " + data[0].name);
161         log4j.debug(data[0].name.elementAt(i));
162         dataHeader[i] = new SQLExecutorData();
163         dataHeader[i].header = data[0].name.elementAt(i);
164     }
165     dataBuffer.append("<tr>\n");
166     for (int j=0; j<data.length; j++) {
167         if (j!=0) {
168             dataBuffer.append("<tr>\n");
169             for (int k=0; k<data[0].name.size(); k++) {
170                 dataBuffer.append("<td>");
171                 dataBuffer.append(data[j].getField(Integer.toString(k)));
172                 dataBuffer.append("</td>\n");
173             }
174             dataBuffer.append("</tr>\n");
175         }
176     }
177     }
178     log4j.debug("dataBuffer: "+dataBuffer.toString());
179     xmlDocument.setParameter("data", dataBuffer.toString());
180     xmlDocument.setData("structureHeader", dataHeader);
181
182     response.setContentType("text/xls; charset=UTF-8");
183     PrintWriter out = response.getWriter();
184     if (log4j.isDebugEnabled()) log4j.debug("printPage - Printing document\n");
185     out.println(xmlDocument.print());
186     out.close();
187     if (log4j.isDebugEnabled()) log4j.debug("printPage - End printing document\n");
188   }
189
190   
191   private void printPage(HttpServletResponse response, VariablesSecureApp vars, String JavaDoc strSQL, SQLExecutor_Query[] data, String JavaDoc strInitRecord, int initRecordNumber, int intRecordRange) throws IOException, ServletException{
192     log4j.info("print page");
193     if (log4j.isDebugEnabled()) log4j.debug("printPage - Reading xml\n");
194     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/ad_forms/SQLExecutor").createXmlDocument();
195     log4j.debug("prueba");
196     xmlDocument.setParameter("direction", "var baseDirection = \"" + strReplaceWith + "/\";\n");
197     xmlDocument.setParameter("language", "LNG_POR_DEFECTO=\"" + vars.getLanguage() + "\";");
198     String JavaDoc strMessage = vars.getSessionValue("SQLExecutor|message");;
199     if (!strMessage.equals("")) strMessage = "alert('" + strMessage + "');";
200     vars.removeSessionValue("SQLExecutor|message");
201     xmlDocument.setParameter("buscador", strMessage);
202     log4j.debug("prueba");
203     xmlDocument.setParameter("sql", strSQL);
204     log4j.debug("sql");
205     SQLExecutorData[] dataHeader = null;
206     StringBuffer JavaDoc dataBuffer = new StringBuffer JavaDoc();
207     if (data!=null && data.length != 0) {
208         log4j.debug("data != null || data.length != 0");
209     dataHeader = new SQLExecutorData[data[0].name.size()];
210         for (int i = 0; i<data[0].name.size(); i++){
211         log4j.debug("data[0].name: " + data[0].name);
212         log4j.debug(data[0].name.elementAt(i));
213         dataHeader[i] = new SQLExecutorData();
214         dataHeader[i].header = data[0].name.elementAt(i);
215     }
216     dataBuffer.append("<tr>\n");
217     for (int j=0; j<data.length; j++) {
218         if (j!=0) {
219             int evenOdd = j%2;
220             dataBuffer.append("<tr class=\"TableDetailRow" + String.valueOf(evenOdd) + "\">\n");
221             for (int k=0; k<data[0].name.size(); k++) {
222                 dataBuffer.append("<td>");
223                 dataBuffer.append(data[j].getField(Integer.toString(k)));
224                 dataBuffer.append("</td>\n");
225             }
226             dataBuffer.append("</tr>\n");
227         }
228     }
229     }
230     /*StringBuffer strData = new StringBuffer("var myData = new Array(\n");
231     StringBuffer strHeader = new StringBuffer();
232     if (log4j.isDebugEnabled()) log4j.debug("printPage - Making grid data\n");
233     int selectedIndex = 0;
234     if (data!=null && data.length!=0) {
235       for (int countHeads=0;countHeads<data[0].type.size();countHeads++) {
236         strHeader.append("obj.addHeader(new Header(\"").append(data[0].name.elementAt(countHeads)).append("\", \"").append(data[0].type.elementAt(countHeads)).append("\", 100, 20, true));\n");
237       }
238       for (int contadorData=0;contadorData<data.length;contadorData++) {
239         strData.append("new Array(");
240         for (int countHeads=0;countHeads<data[0].data.size();countHeads++) {
241           if (countHeads>0) strData.append(",");
242           strData.append("\"").append(Replace.replace(Replace.replace(Replace.replace(data[contadorData].getField(Integer.toString(countHeads)), "\r", ""), "\n", "<br>"), "\"", "\\\"")).append("\"");
243         }
244         strData.append(")");
245         if (contadorData < (data.length-1)) strData.append(",");
246         strData.append("\n");
247       }
248     } else {
249       strInitRecord = "0";
250     }
251     strData.append(");");
252     String strGrid = "obj.setSelectedRow(" + Integer.toString(selectedIndex) + ");\n";
253     strGrid += "obj.setInitRowNum(" + (strInitRecord.equals("0")?"1":strInitRecord) + ");\n";
254     xmlDocument.setParameter("grid", strGrid);
255     xmlDocument.setParameter("header", strHeader.toString());
256     xmlDocument.setParameter("data", strData.toString());*/
257     xmlDocument.setData("structureHeader", dataHeader);
258     xmlDocument.setParameter("data", dataBuffer.toString());
259     //xmlDocument.setData("reportLinea", "structure1", dataLinea);
260 if (log4j.isDebugEnabled()) log4j.debug("printPage - Making toolbar\n");
261     ToolBar toolbar = new ToolBar(this, vars.getLanguage(), "SQLExecutor", false, "document.frmMain.inpKey", "myGrid", null, false, "ad_forms", strReplaceWith, false, true);
262     toolbar.prepareQueryTemplate((initRecordNumber>1), (data!=null && data.length!=0 && data.length>=intRecordRange), vars.getSessionValue("#ShowTest", "N").equals("Y"));
263     xmlDocument.setParameter("toolbar", toolbar.toString());
264     
265     log4j.debug("toolbar");
266     try {
267       KeyMap key = new KeyMap(this, vars, "SQLExecutor.html");
268       xmlDocument.setParameter("keyMap", key.getRelationKeyMaps());
269     } catch (Exception JavaDoc ex) {
270       throw new ServletException(ex);
271     }
272     log4j.debug("keymap");
273     try {
274       WindowTabs tabs = new WindowTabs(this, vars, "org.openbravo.erpCommon.ad_forms.SQLExecutor");
275       log4j.debug("tabs");
276       xmlDocument.setParameter("parentTabContainer", tabs.parentTabs());
277       log4j.debug("parentTabContainer");
278       xmlDocument.setParameter("mainTabContainer", tabs.mainTabs());
279       log4j.debug("mainTabContainer");
280       xmlDocument.setParameter("childTabContainer", tabs.childTabs());
281       log4j.debug("childTabContainer");
282       xmlDocument.setParameter("theme", vars.getTheme());
283       log4j.debug("theme");
284       NavigationBar nav = new NavigationBar(this, vars.getLanguage(), "SQLExecutor.html", classInfo.id, classInfo.type, strReplaceWith, tabs.breadcrumb());
285       xmlDocument.setParameter("navigationBar", nav.toString());
286       log4j.debug("navigationBar");
287       LeftTabsBar lBar = new LeftTabsBar(this, vars.getLanguage(), "SQLExecutor.html", strReplaceWith);
288       xmlDocument.setParameter("leftTabs", lBar.manualTemplate());
289       log4j.debug("leftTabs");
290     } catch (Exception JavaDoc ex) {
291       throw new ServletException(ex);
292     }
293     {
294       OBError myMessage = vars.getMessage("SQLExecutor");
295       vars.removeMessage("SQLExecutor");
296       if (myMessage!=null) {
297         xmlDocument.setParameter("messageType", myMessage.getType());
298         xmlDocument.setParameter("messageTitle", myMessage.getTitle());
299         xmlDocument.setParameter("messageMessage", myMessage.getMessage());
300       }
301     }
302     xmlDocument.setParameter("calendar", vars.getLanguage().substring(0,2));
303     log4j.debug("calendar");
304     response.setContentType("text/html; charset=UTF-8");
305     PrintWriter out = response.getWriter();
306     if (log4j.isDebugEnabled()) log4j.debug("printPage - Printing document\n");
307     out.println(xmlDocument.print());
308     out.close();
309     if (log4j.isDebugEnabled()) log4j.debug("printPage - End printing document\n");
310   }
311
312
313   public String JavaDoc getServletInfo() {
314     return "Servlet for the standard SQL execution";
315   } // end of getServletInfo() method
316 }
317
Popular Tags