DataGrid


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.io.*;
23  import java.util.*;
24  
25  import javax.servlet.*;
26  import javax.servlet.http.*;
27  
28  import org.openbravo.base.secureApp.HttpSecureAppServlet;
29  import org.openbravo.base.secureApp.VariablesSecureApp;
30  import org.openbravo.data.FieldProvider;
31  import org.openbravo.xmlEngine.XmlDocument;
32  
33  
34  public class DataGrid extends HttpSecureAppServlet {
35  
36    public void init (ServletConfig config) {
37      super.init(config);
38      boolHist = false;
39    }
40  
41    public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
42      VariablesSecureApp vars = new VariablesSecureApp(request);
43  
44      String   action = vars.getStringParameter("action");
45      String   TabId = vars.getStringParameter("inpadTabId");
46      String   WindowId = vars.getStringParameter("inpadWindowId");
47      if (log4j.isDebugEnabled()) log4j.debug("action: " + action);
48      if (log4j.isDebugEnabled()) log4j.debug("TabId: " + TabId);
49      if (log4j.isDebugEnabled()) log4j.debug("WindowId: " + WindowId);
50      TableSQLData tableSQL = null;
51      try {
52        tableSQL = new TableSQLData(vars, this, TabId, Utility.getContext(this, vars, "#User_Org", WindowId), Utility.getContext(this, vars, "#User_Client", WindowId));
53      } catch (Exception   ex) {
54        ex.printStackTrace();
55      }
56  
57      if (vars.commandIn("STRUCTURE")) {
58        printPageStructure(response, vars, tableSQL);
59      } else if (vars.commandIn("DATA")) {
60        if (log4j.isDebugEnabled()) log4j.debug(">>DATA");
61        if(action.equalsIgnoreCase("getRows")){
62          printPageData(response, vars, tableSQL);
63        } else if (action.equalsIgnoreCase("getIdsInRange")){
64          if (log4j.isDebugEnabled()) log4j.debug(">>>>getIdsInRange");
65          printPageDataId(response, vars, tableSQL);
66        } else if (action.equalsIgnoreCase("getColumnTotals")){
67          if (log4j.isDebugEnabled()) log4j.debug(">>>>getColumnTotals");
68          getColumnTotals(response, vars, tableSQL);
69        } else if (action.equalsIgnoreCase("getComboContent")) {
70          if (log4j.isDebugEnabled()) log4j.debug(">>>>getComboContent");
71          getComboContent(response, vars, TabId);
72        } else if (action.equalsIgnoreCase("getDefaultValues")) {
73          if (log4j.isDebugEnabled()) log4j.debug(">>>>getDefaultValues");
74          this.getDefaultValues(response, vars);
75        }
76      } else if (vars.commandIn("UPDATE")) {
77        if (log4j.isDebugEnabled()) log4j.debug(">>UPDATE");
78        try {
79          if (action.equalsIgnoreCase("deleteRow")) {
80            if (log4j.isDebugEnabled()) log4j.debug(">>>>deleteRow");
81            delete(response, vars, tableSQL);
82          } else {
83            save(response, vars);
84          }
85        } catch (Exception   e) {
86          e.printStackTrace();
87        }
88      } else {
89        if (log4j.isDebugEnabled()) log4j.debug("Command " + action + " not defined");
90      }
91    }
92  
93    private SQLReturnObject[] getHeaders(TableSQLData tableSQL) throws ServletException {
94      return tableSQL.getHeaders();
95    }
96  
97    private String   getTotalRows(TableSQLData tableSQL) {
98      if (tableSQL==null) return "0";
99      FieldProvider[] data = null;
100     try {
101       ExecuteQuery execquery = new ExecuteQuery(this, tableSQL.getTotalSQL(), tableSQL.getParameterValuesTotalSQL());
102       data = execquery.select(0,0);
103     } catch (Exception   ex) {
104       ex.printStackTrace();
105     }
106     if (data==null || data.length==0) return "0";
107     else return data[0].getField("TOTAL");
108   }
109 
110   private void printPageStructure(HttpServletResponse response, VariablesSecureApp vars, TableSQLData tableSQL) throws IOException, ServletException {
111     if (log4j.isDebugEnabled()) log4j.debug("Output: print page structure");
112     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/utility/DataGridStructure").createXmlDocument();
113     SQLReturnObject[] data = null;
114     String   type = "Hidden";
115     String   title = "";
116     String   description = "";
117     try {
118       data = getHeaders(tableSQL);
119     } catch (Exception   ex) {
120       type = "Error";
121       title = "Error";
122       description = ex.getMessage();
123       ex.printStackTrace();
124     }
125     xmlDocument.setParameter("type", type);
126     xmlDocument.setParameter("title", title);
127     xmlDocument.setParameter("description", description);
128     xmlDocument.setData("structure1", data);
129     response.setContentType("text/xml; charset=UTF-8");
130     response.setHeader("Cache-Control", "no-cache");
131     PrintWriter out = response.getWriter();
132     if (log4j.isDebugEnabled()) log4j.debug(xmlDocument.print());
133     out.println(xmlDocument.print());
134     out.close();
135   }
136 
137   private void printPageData(HttpServletResponse response, VariablesSecureApp vars, TableSQLData tableSQL) throws IOException, ServletException {
138     if (log4j.isDebugEnabled()) log4j.debug("Output: print page rows");
139     int pageSize = new Integer  (vars.getStringParameter("page_size")).intValue();
140     int offset = new Integer  (vars.getRequestGlobalVariable("offset", tableSQL.getTabID() + "|offset")).intValue();
141     SQLReturnObject[] headers = getHeaders(tableSQL);
142     FieldProvider[] data = null;
143     String   type = "Hidden";
144     String   title = "";
145     String   description = "";
146     if (tableSQL!=null && headers!=null) {
147       try{
148         String   strSQL = ModelSQLGeneration.generateSQL(this, vars, tableSQL, "", new Vector<String  >(), new Vector<String  >());
149         if (log4j.isDebugEnabled()) log4j.debug("SQL: " + strSQL);
150         vars.removeSessionValue(tableSQL.getTabID() + "|newOrder");
151         ExecuteQuery execquery = new ExecuteQuery(this, strSQL, tableSQL.getParameterValues());
152         data = execquery.select(offset,pageSize);
153       } catch (ServletException e) {
154         log4j.error("Error in print page data: " + e);
155         e.printStackTrace();
156         OBError myError = Utility.translateError(this, vars, vars.getLanguage(), e.getMessage());
157         if (!myError.isConnectionAvailable()) {
158           bdErrorAjax(response, "Error", "Connection Error", "No database connection");
159           return;
160         } else {
161           type = myError.getType();
162           title = myError.getTitle();
163           if (!myError.getMessage().startsWith("<![CDATA[")) description = "<![CDATA[" + myError.getMessage() + "]]>";
164           else description = myError.getMessage();
165         }
166       } catch (Exception   e) { 
167         if (log4j.isDebugEnabled()) log4j.debug("Error obtaining rows data");
168         type = "Error";
169         title = "Error";
170         if (e.getMessage().startsWith("<![CDATA[")) description = "<![CDATA[" + e.getMessage() + "]]>";
171         else description = e.getMessage();
172         e.printStackTrace();
173       }
174     }
175     if (!type.startsWith("<![CDATA[")) type = "<![CDATA[" + type + "]]>";
176     if (!title.startsWith("<![CDATA[")) title = "<![CDATA[" + title + "]]>";
177     if (!description.startsWith("<![CDATA[")) description = "<![CDATA[" + description + "]]>";
178     StringBuffer   strRowsData = new StringBuffer  ();
179     strRowsData.append("<xml-data>\n");
180     strRowsData.append("  <status>\n");
181     strRowsData.append("    <type>").append(type).append("</type>\n");
182     strRowsData.append("    <title>").append(title).append("</title>\n");
183     strRowsData.append("    <description>").append(description).append("</description>\n");
184     strRowsData.append("  </status>\n");
185     strRowsData.append("  <rows numRows=\"").append(getTotalRows(tableSQL)).append("\">\n");
186     if (data!=null && data.length>0) {
187       for (int j=0;j<data.length;j++) {
188         strRowsData.append("    <tr>\n");
189         for (int k=0;k<headers.length;k++) {
190           strRowsData.append("      <td><![CDATA[");
191           String   columnname = headers[k].getField("columnname");
192           if (headers[k].getField("iskey").equals("false") && !tableSQL.getSelectField(columnname + "_R").equals("")) columnname += "_R";
193           if ((data[j].getField(columnname)) != null) {
194             if (headers[k].getField("adReferenceId").equals("32")) strRowsData.append(strReplaceWith).append("/images/");
195             strRowsData.append(data[j].getField(columnname));
196           } else {
197             if (headers[k].getField("adReferenceId").equals("32")) {
198               strRowsData.append(strReplaceWith).append("/images/blank.gif");
199             } else strRowsData.append("&nbsp;");
200           }
201           strRowsData.append("]]></td>\n");
202         }
203         strRowsData.append("    </tr>\n");
204       }
205     }
206     strRowsData.append("  </rows>\n");
207     strRowsData.append("</xml-data>\n");
208     response.setContentType("text/xml; charset=UTF-8");
209     response.setHeader("Cache-Control", "no-cache");
210     PrintWriter out = response.getWriter();
211     if (log4j.isDebugEnabled()) log4j.debug(strRowsData.toString());
212     out.print(strRowsData.toString());
213     out.close();
214   }
215 
216   private void printPageDataId(HttpServletResponse response, VariablesSecureApp vars, TableSQLData tableSQL) throws IOException, ServletException {
217     if (log4j.isDebugEnabled()) log4j.debug("Output: print page ids");
218     int minOffset  = new Integer  (vars.getStringParameter("minOffset")).intValue();
219     int maxOffset  = new Integer  (vars.getStringParameter("maxOffset")).intValue();
220     String   type = "Hidden";
221     String   title = "";
222     String   description = "";
223     FieldProvider[] data = null;
224     if (tableSQL!=null) {
225       try{
226         String   strSQL = ModelSQLGeneration.generateSQL(this, vars, tableSQL, tableSQL.getKeyColumn() + " AS ID", new Vector<String  >(), new Vector<String  >());
227         ExecuteQuery execquery = new ExecuteQuery(this, strSQL, tableSQL.getParameterValues());
228         data = execquery.select(minOffset,maxOffset-minOffset);
229       } catch (Exception   e) { 
230         if (log4j.isDebugEnabled()) log4j.debug("Error obtaining rows data");
231         e.printStackTrace();
232         type = "Error";
233         title = "Error";
234         if (!e.getMessage().startsWith("<![CDATA[")) description = "<![CDATA[" + e.getMessage() + "]]>";
235       }
236     }
237 
238     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/utility/DataGridID").createXmlDocument();
239     xmlDocument.setParameter("type", type);
240     xmlDocument.setParameter("title", title);
241     xmlDocument.setParameter("description", description);
242     xmlDocument.setData("structure1", data);
243     response.setContentType("text/xml; charset=UTF-8");
244     response.setHeader("Cache-Control", "no-cache");
245     PrintWriter out = response.getWriter();
246     if (log4j.isDebugEnabled()) log4j.debug(xmlDocument.print());
247     out.println(xmlDocument.print());
248     out.close();
249   }
250 
251   private void getColumnTotals(HttpServletResponse response, VariablesSecureApp vars, TableSQLData tableSQL) throws ServletException, IOException {
252     if (log4j.isDebugEnabled()) log4j.debug("Output: print page column total");
253     String   rows = vars.getInStringParameter("rows");
254     String   columnname = vars.getStringParameter("columnName");
255     FieldProvider[] data = null;
256     if (tableSQL!=null) {
257       try{
258         Vector<String  > filter = new Vector<String  >();
259         filter.addElement(tableSQL.getTableName() + "." + tableSQL.getKeyColumn() + " IN " + rows);
260         String   strSQL = ModelSQLGeneration.generateSQL(this, vars, tableSQL, "SUM(" + columnname + ") AS TOTAL", filter, new Vector<String  >());
261         ExecuteQuery execquery = new ExecuteQuery(this, strSQL, tableSQL.getParameterValues());
262         data = execquery.select(0,0);
263       } catch (Exception   e) { 
264         if (log4j.isDebugEnabled()) log4j.debug("Error obtaining rows data");
265         e.printStackTrace();
266       }
267     }
268     String   total = "0";
269     if (data!=null && data.length>0) total = data[0].getField("TOTAL");
270     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/utility/DataGridTotal").createXmlDocument();
271     xmlDocument.setParameter("total", total);
272     response.setContentType("text/xml; charset=UTF-8");
273     response.setHeader("Cache-Control", "no-cache");
274     PrintWriter out = response.getWriter();
275     if (log4j.isDebugEnabled()) log4j.debug(xmlDocument.print());
276     out.println(xmlDocument.print());
277     out.close();
278   }
279 
280   private void delete(HttpServletResponse response, VariablesSecureApp vars, TableSQLData tableSQL) throws IOException, ServletException {
281     if (log4j.isDebugEnabled()) log4j.debug("Delete record");
282     Vector<String  > parametersData = null;
283     String   rows = vars.getInStringParameter("rows");
284     StringBuffer   SqlDataBuffer = new StringBuffer  ();
285     SqlDataBuffer.append("DELETE FROM ").append(tableSQL.getTableName()).append(" \n");
286     SqlDataBuffer.append("WHERE ").append(tableSQL.getKeyColumn()).append(" \n");
287     SqlDataBuffer.append("IN ").append(rows);
288     String   parentKey = tableSQL.getParentColumnName();
289     if (parentKey!=null && !parentKey.equals("")) {
290       SqlDataBuffer.append(" AND ").append(tableSQL.getTableName()).append(".").append(parentKey).append(" = ?");
291       if (parametersData==null) parametersData = new Vector<String  >();
292       parametersData.addElement(vars.getGlobalVariable("inpParentKey", tableSQL.getWindowID() + "|" + parentKey));
293     }
294     if (log4j.isDebugEnabled()) log4j.debug(SqlDataBuffer.toString());
295     int result = 1;
296     int total = 0;
297     String   type, title, description="";
298     try{
299       ExecuteQuery execquery = new ExecuteQuery(this, SqlDataBuffer.toString(), parametersData);
300       total = execquery.executeStatement();
301       if (total == 0) {
302         result=0;
303         type="Error";
304         title="Error";
305         description="0 " + Utility.messageBD(this, "RowsDeleted", vars.getLanguage());
306       } else {
307         result=1;
308         type="Success";
309         title="Success";
310         description=total + " " + Utility.messageBD(this, "RowsDeleted", vars.getLanguage());
311       }
312     } catch (ServletException e) { 
313       log4j.error("Error in delete: " + e);
314       e.printStackTrace();
315       OBError myError = Utility.translateError(this, vars, vars.getLanguage(), e.getMessage());
316       if (!myError.isConnectionAvailable()) {
317         bdErrorAjax(response, "Error", "Connection Error", "No database connection");
318         return;
319       } else {
320         result = 0;
321         type = myError.getType();
322         title = myError.getTitle();
323         if (!myError.getMessage().startsWith("<![CDATA[")) description = "<![CDATA[" + myError.getMessage() + "]]>";
324         else description = myError.getMessage();
325       }
326     } catch (Exception   e) {
327       log4j.error("Error in delete: " + e);
328       e.printStackTrace();
329       result = 0;
330       type = "ERROR";
331       title = "Error";
332       if (!e.getMessage().startsWith("<![CDATA[")) description = "<![CDATA[" + e.getMessage() + "]]>";
333       else description = e.getMessage();
334     }
335 
336     XmlDocument xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/utility/DataGridDelete").createXmlDocument();
337     xmlDocument.setParameter("result", Integer.toString(result));
338     xmlDocument.setParameter("type", type);
339     xmlDocument.setParameter("title", title);
340     xmlDocument.setParameter("description", description);
341     xmlDocument.setParameter("total", Integer.toString(total));
342     response.setContentType("text/xml; charset=UTF-8");
343     response.setHeader("Cache-Control", "no-cache");
344     PrintWriter out = response.getWriter();
345     if (log4j.isDebugEnabled()) log4j.debug(xmlDocument.print());
346     out.println(xmlDocument.print());
347     out.close();
348   }
349 
350   private void getComboContent(HttpServletResponse response, VariablesSecureApp vars, String   TabId) throws ServletException, IOException {
351       /*
352       String columnname = vars.getStringParameter("subordinatedColumn");
353       if (log4j.isDebugEnabled()) log4j.debug("--------");
354       if (log4j.isDebugEnabled()) log4j.debug("");
355       if (log4j.isDebugEnabled()) log4j.debug(columnname.toString());
356       if (log4j.isDebugEnabled()) log4j.debug("");
357       if (log4j.isDebugEnabled()) log4j.debug("--------");
358       
359       //Get columname to get reference id (17, 18 or 19)
360       DataGridReferenceIdData[] refId = DataGridReferenceIdData.select(this, vars.getLanguage(), TabId, columnname.toString());
361       
362       if (log4j.isDebugEnabled()) log4j.debug(refId[0].adReferenceId);
363       
364     if (refId[0].adReferenceId.equals("17")) { 
365       if (log4j.isDebugEnabled()) log4j.debug("true");
366       
367       StringBuffer SelectClause = new StringBuffer();
368       StringBuffer FromClause = new StringBuffer();
369       StringBuffer WhereClause = new StringBuffer();
370       SelectClause.append("SELECT ");
371       FromClause.append("FROM ");
372       WhereClause.append("WHERE ");
373       SelectClause.append("Name ");
374       FromClause.append("AD_Ref_List_V ");
375       WhereClause.append("Ad_Reference_Id = ").append(refId[0].adReferenceValueId);
376       WhereClause.append(" AND ");
377       WhereClause.append("ad_language = '").append(vars.getLanguage()).append("' \n");
378     
379       //String SqlData;
380       StringBuffer SqlData = new StringBuffer();
381       SqlData.append(SelectClause).append(FromClause).append(WhereClause);
382       if (log4j.isDebugEnabled()) log4j.debug("SQL del Combo: " + SqlData.toString());
383       
384       try{
385         ExecuteQuery execquery = new ExecuteQuery(this, SqlData.toString(), null);
386         FieldProvider[] data = execquery.select(0,0);
387         
388         StringBuffer strRowsData = new StringBuffer();
389         if (log4j.isDebugEnabled()) log4j.debug("\n Ha tragado la query del SQL Combo\n");
390           strRowsData.append("    <row>\n");
391 
392         for (int k=0;k<data.length;k++) {
393           if ((data[1].getField("Name")) != null) {
394             strRowsData.append("          <option>").append(data[k].getField("Name")).append("</option>\n");
395           }
396         }               
397           
398           strRowsData.append("    </row>\n");
399         response.setContentType("text/xml; charset=UTF-8");
400         PrintWriter out = response.getWriter();
401         //out.print("<response type=\"object\" id=\"structureParser\">");
402         out.print(strRowsData.toString());
403         //out.print("</response>");
404         out.close();
405           
406       } catch (Exception e) { 
407         if (log4j.isDebugEnabled()) log4j.debug("Error in printPageData");
408         e.printStackTrace();
409       }
410     } else if (refId[0].adReferenceId.equals("19")) { 
411       if (log4j.isDebugEnabled()) log4j.debug("true");
412       
413       StringBuffer SelectClause = new StringBuffer();
414       StringBuffer FromClause = new StringBuffer();
415       SelectClause.append("SELECT ");
416       FromClause.append("FROM ");
417       
418       SelectClause.append(columnname).append(", Name ");
419       FromClause.append(columnname.substring(0, columnname.length()-3));
420     
421       //String SqlData;
422       StringBuffer SqlData = new StringBuffer();
423       SqlData.append(SelectClause).append(FromClause);
424       if (log4j.isDebugEnabled()) log4j.debug("SQL del Combo: " + SqlData.toString());
425       
426       try{
427         ExecuteQuery execquery = new ExecuteQuery(this, SqlData.toString(), null);
428         FieldProvider[] data = execquery.select(0,0);
429         
430         StringBuffer strRowsData = new StringBuffer();
431         if (log4j.isDebugEnabled()) log4j.debug("\n Ha tragado la query del SQL Combo\n");
432           strRowsData.append("    <row>\n");
433 
434         for (int k=0;k<data.length;k++) {
435           if ((data[1].getField("Name")) != null) {
436             strRowsData.append("          <option>").append(data[k].getField("Name")).append("</option>\n");
437           }
438         }               
439           
440           strRowsData.append("    </row>\n");
441         response.setContentType("text/xml; charset=UTF-8");
442         PrintWriter out = response.getWriter();
443         //out.print("<response type=\"object\" id=\"structureParser\">");
444         out.print(strRowsData.toString());
445         //out.print("</response>");
446         out.close();
447           
448       } catch (Exception e) { 
449         if (log4j.isDebugEnabled()) log4j.debug("Error in printPageData");
450         e.printStackTrace();
451       }
452     }*/
453   }
454     
455   private void getDefaultValues(HttpServletResponse response, VariablesSecureApp vars) throws ServletException, IOException {
456   }
457 
458   private void save(HttpServletResponse response, VariablesSecureApp vars) throws ServletException, IOException {
459   }
460 }
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags