Java > Open Source Codes > org > openbravo > erpCommon > ad_reports > ReportBudgetGenerateExcel


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.ad_reports;
21
22 import org.openbravo.erpCommon.utility.*;
23 import org.openbravo.erpCommon.businessUtility.WindowTabs;
24 import org.openbravo.base.secureApp.HttpSecureAppServlet;
25 import org.openbravo.base.secureApp.VariablesSecureApp;
26 import org.openbravo.xmlEngine.XmlDocument;
27 import java.io.*;
28 import javax.servlet.*;
29 import javax.servlet.http.*;
30
31
32
33 public class ReportBudgetGenerateExcel extends HttpSecureAppServlet {
34
35
36   public void doPost (HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
37     VariablesSecureApp vars = new VariablesSecureApp(request);
38
39     if (!Utility.hasProcessAccess(this, vars, "", "ReportBudgetGenerateExcel")) {
40       bdError(response, "AccessTableNoView", vars.getLanguage());
41       return;
42     }
43
44     if (vars.commandIn("DEFAULT")){
45       printPageDataSheet(response, vars);
46     }else if(vars.commandIn("EXCEL")){
47
48       vars.removeSessionValue("ReportBudgetGenerateExcel|inpTabId");
49       String strBPartner = vars.getRequestInGlobalVariable("inpcBPartnerId_IN", "ReportBudgetGenerateExcel|inpcBPartnerId_IN");
50       String strBPGroup = vars.getRequestInGlobalVariable("inpcBPGroupID", "ReportBudgetGenerateExcel|inpcBPGroupID");
51       String strProduct = vars.getRequestInGlobalVariable("inpmProductId_IN", "ReportBudgetGenerateExcel|inpmProductId_IN");
52       String strProdCategory = vars.getRequestInGlobalVariable("inpmProductCategoryId", "ReportBudgetGenerateExcel|inpmProductCategoryId");
53       //String strUser1 = vars.getRequestInGlobalVariable("inpUser1", "ReportBudgetGenerateExcel|inpUser1");
54 //String strUser2 = vars.getRequestInGlobalVariable("inpUser2", "ReportBudgetGenerateExcel|inpUser2");
55 String strSalesRegion = vars.getRequestInGlobalVariable("inpcSalesRegionId", "ReportBudgetGenerateExcel|inpcSalesRegionId");
56       String strCampaign = vars.getRequestInGlobalVariable("inpcCampaingId", "ReportBudgetGenerateExcel|inpcCampaingId");
57       String strActivity = vars.getRequestInGlobalVariable("inpcActivityId", "ReportBudgetGenerateExcel|inpcActivityId");
58       String strProject = vars.getRequestInGlobalVariable("inpcProjectId", "ReportBudgetGenerateExcel|inpcProjectId");
59       String strTrxOrg = vars.getRequestInGlobalVariable("inpTrxOrg", "ReportBudgetGenerateExcel|inpTrxOrg");
60       String strMonth = vars.getRequestInGlobalVariable("inpMonth", "ReportBudgetGenerateExcel|inpMonthId");
61       String strValidcombination = vars.getRequestGlobalVariable("inpcValidcombinationId", "ReportBudgetGenerateExcel|inpcValidcombinationId");
62       printPageDataExcel(response, vars, strBPartner, strBPGroup, strProduct, strProdCategory, /*strUser1, strUser2,*/ strSalesRegion, strCampaign, strActivity, strProject, strTrxOrg, strMonth, strValidcombination);
63     } else pageErrorPopUp(response);
64   }
65
66   void printPageDataSheet(HttpServletResponse response, VariablesSecureApp vars/*, String strdateFrom, String strdateTo, String strcBpartnerId, String strcProjectId, String strmCategoryId, String strProjectkind, String strProjectphase, String strProjectstatus, String strProjectpublic,String strcRegionId, String strSalesRep, String strProduct*/) throws IOException, ServletException {
67     if (log4j.isDebugEnabled()) log4j.debug("Output: dataSheet");
68     String discard[]={"sectionPartner"};
69     String strTitle = "";
70     XmlDocument xmlDocument=null;
71     xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/ad_reports/ReportBudgetGenerateExcel").createXmlDocument();
72
73     ToolBar toolbar = new ToolBar(this, vars.getLanguage(), "ReportBudgetGenerateExcel", false, "", "", "",false, "ad_reports", strReplaceWith, false, true);
74     toolbar.prepareSimpleToolBarTemplate();
75     xmlDocument.setParameter("toolbar", toolbar.toString());
76
77     try {
78       KeyMap key = new KeyMap(this, vars, "ReportBudgetGenerateExcel.html");
79       xmlDocument.setParameter("keyMap", key.getReportKeyMaps());
80     } catch (Exception ex) {
81       throw new ServletException(ex);
82     }
83     try {
84       WindowTabs tabs = new WindowTabs(this, vars, "org.openbravo.erpCommon.ad_reports.ReportBudgetGenerateExcel");
85       xmlDocument.setParameter("parentTabContainer", tabs.parentTabs());
86       xmlDocument.setParameter("mainTabContainer", tabs.mainTabs());
87       xmlDocument.setParameter("childTabContainer", tabs.childTabs());
88       xmlDocument.setParameter("theme", vars.getTheme());
89       NavigationBar nav = new NavigationBar(this, vars.getLanguage(), "ReportBudgetGenerateExcel.html", classInfo.id, classInfo.type, strReplaceWith, tabs.breadcrumb());
90       xmlDocument.setParameter("navigationBar", nav.toString());
91       LeftTabsBar lBar = new LeftTabsBar(this, vars.getLanguage(), "ReportBudgetGenerateExcel.html", strReplaceWith);
92       xmlDocument.setParameter("leftTabs", lBar.manualTemplate());
93     } catch (Exception ex) {
94       throw new ServletException(ex);
95     }
96     {
97       OBError myMessage = vars.getMessage("ReportBudgetGenerateExcel");
98       vars.removeMessage("ReportBudgetGenerateExcel");
99       if (myMessage!=null) {
100         xmlDocument.setParameter("messageType", myMessage.getType());
101         xmlDocument.setParameter("messageTitle", myMessage.getTitle());
102         xmlDocument.setParameter("messageMessage", myMessage.getMessage());
103       }
104     }
105     xmlDocument.setParameter("calendar", vars.getLanguage().substring(0,2));
106
107     // xmlDocument.setData("reportCBPartnerId_IN", "liststructure", ReportBudgetGenerateExcelData.selectBpartner(this));
108 try {
109       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_BP_Group_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
110       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
111       xmlDocument.setData("reportCBPGroupId","liststructure", comboTableData.select(false));
112       comboTableData = null;
113     } catch (Exception ex) {
114       throw new ServletException(ex);
115     }
116
117     // xmlDocument.setData("reportMProductId_IN", "liststructure", ReportBudgetGenerateExcelData.selectMproduct(this));
118 try {
119       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "M_Product_Category_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
120       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
121       xmlDocument.setData("reportM_PRODUCTCATEGORY","liststructure", comboTableData.select(false));
122       comboTableData = null;
123     } catch (Exception ex) {
124       throw new ServletException(ex);
125     }
126
127
128     /* try {
129         ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_ElementValue_ID(this, vars.getLanguage", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
130         Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
131         xmlDocument.setData("reportUser1","liststructure", comboTableData.select(false));
132         comboTableData = null;
133         } catch (Exception ex) {
134         throw new ServletException(ex);
135         }*/
136
137
138     /* try {
139         ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_ElementValue_ID(this, vars.getLanguage", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
140         Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
141         xmlDocument.setData("reportUser2","liststructure", comboTableData.select(false));
142         comboTableData = null;
143         } catch (Exception ex) {
144         throw new ServletException(ex);
145         }*/
146
147
148     try {
149       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_SalesRegion_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
150       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
151       xmlDocument.setData("reportCSalesRegionId","liststructure", comboTableData.select(false));
152       comboTableData = null;
153     } catch (Exception ex) {
154       throw new ServletException(ex);
155     }
156
157
158     try {
159       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Campaign_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
160       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
161       xmlDocument.setData("reportCCampaignId","liststructure", comboTableData.select(false));
162       comboTableData = null;
163     } catch (Exception ex) {
164       throw new ServletException(ex);
165     }
166
167
168     try {
169       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Activity_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
170       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
171       xmlDocument.setData("reportCActivityId","liststructure", comboTableData.select(false));
172       comboTableData = null;
173     } catch (Exception ex) {
174       throw new ServletException(ex);
175     }
176
177
178     try {
179       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Project_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
180       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
181       xmlDocument.setData("reportCProjectId","liststructure", comboTableData.select(false));
182       comboTableData = null;
183     } catch (Exception ex) {
184       throw new ServletException(ex);
185     }
186
187
188     try {
189       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "AD_Org_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
190       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
191       xmlDocument.setData("reportTrxOrg","liststructure", comboTableData.select(false));
192       comboTableData = null;
193     } catch (Exception ex) {
194       throw new ServletException(ex);
195     }
196
197     xmlDocument.setData("reportMonth","liststructure",ReportBudgetGenerateExcelData.selectMonth(this));
198     /*try {
199       ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_ValidCombination_ID", "", "", Utility.getContext(this, vars, "#User_Org", "ReportBudgetGenerateExcel"), Utility.getContext(this, vars, "#User_Client", "ReportBudgetGenerateExcel"), 0);
200       Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportBudgetGenerateExcel", "");
201       xmlDocument.setData("reportCValidCombinationId","liststructure", comboTableData.select(false));
202       comboTableData = null;
203       } catch (Exception ex) {
204       throw new ServletException(ex);
205       }*/
206      //added by gro 03/06/2007
207 OBError myMessage = vars.getMessage("ReportBudgetGenerateExcel");
208         vars.removeMessage("ReportBudgetGenerateExcel");
209         if (myMessage!=null) {
210           xmlDocument.setParameter("messageType", myMessage.getType());
211           xmlDocument.setParameter("messageTitle", myMessage.getTitle());
212           xmlDocument.setParameter("messageMessage", myMessage.getMessage());
213         }
214
215     xmlDocument.setParameter("language", "LNG_POR_DEFECTO=\"" + vars.getLanguage() + "\";");
216     xmlDocument.setParameter("direction", "var baseDirection = \"" + strReplaceWith + "/\";\n");
217     response.setContentType("text/html; charset=UTF-8");
218     PrintWriter out = response.getWriter();
219     out.println(xmlDocument.print());
220     out.close();
221   }
222
223   void printPageDataExcel(HttpServletResponse response, VariablesSecureApp vars, String strBPartner, String strBPGroup, String strProduct, String strProdCategory, /*String strUser1, String strUser2,*/ String strSalesRegion, String strCampaign, String strActivity,String strProject, String strTrxOrg, String strMonth, String strValidcombination) throws IOException, ServletException {
224
225     if (log4j.isDebugEnabled()) log4j.debug("Output: EXCEL");
226     StringBuffer columns= new StringBuffer ();
227     StringBuffer tables= new StringBuffer ();
228
229     if (strBPartner != null && strBPartner != "") {
230       columns.append("PARTNER, ");
231       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_BPARTNER', TO_CHAR(C_BPARTNER_ID), '" ).append( vars.getLanguage()).append("') AS PARTNER, C_BPARTNER_ID FROM C_BPARTNER WHERE C_BPARTNER_ID IN" ).append( strBPartner ).append(")");
232     } else columns.append("' ' AS PARTNER, ");
233     if (strBPGroup != null && strBPGroup != "") {
234       columns.append("PARTNERGROUP, ");
235       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_BP_GROUP', TO_CHAR(C_BP_GROUP_ID), '" ).append( vars.getLanguage() ).append( "') AS PARTNERGROUP FROM C_BP_GROUP WHERE C_BP_GROUP_ID IN" ).append( strBPGroup ).append( ")");
236     } else columns.append("' ' AS PARTNERGROUP, ");
237     if (strProduct != null && strProduct != "") {
238       columns.append("PRODUCT, ");
239       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('M_PRODUCT', TO_CHAR(M_PRODUCT_ID), '" ).append( vars.getLanguage() ).append( "') AS PRODUCT, M_PRODUCT_ID FROM M_PRODUCT WHERE M_PRODUCT_ID IN" ).append( strProduct ).append( ")");
240     } else columns.append("' ' AS PRODUCT, ");
241     if (strProdCategory != null && strProdCategory != "") {
242       columns.append("PRODCATEGORY, ");
243       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('M_PRODUCT_CATEGORY', TO_CHAR(M_PRODUCT_CATEGORY_ID), '" ).append( vars.getLanguage() ).append( "') AS PRODCATEGORY FROM M_PRODUCT_CATEGORY WHERE M_PRODUCT_CATEGORY_ID IN" ).append( strProdCategory ).append( ")");
244     } else columns.append("' ' AS PRODCATEGORY, ");
245     /*if (strUser1 != null && strUser1 != "") {
246       columns.append("USER1, ");
247       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_ELEMENTVALUE', TO_CHAR(C_ELEMENTVALUE_ID), '" ).append( vars.getLanguage() ).append( "') AS USER1 FROM C_ELEMENTVALUE WHERE C_ELEMENTVALUE_ID IN" ).append( strUser1 ).append( ")");
248       } else columns.append("' ' AS USER1, ");
249       if (strUser2 != null && strUser2 != "") {
250       columns.append("USER2, ");
251       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_ELEMENTVALUE', TO_CHAR(C_ELEMENTVALUE_ID), '" ).append( vars.getLanguage() ).append( "') AS USER2 FROM C_ELEMENTVALUE WHERE C_ELEMENTVALUE_ID IN" ).append( strUser2 ).append( ")");
252       } else columns.append("' ' AS USER2, ");*/
253     if (strSalesRegion != null && strSalesRegion != "") {
254       columns.append("SALESREGION, ");
255       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_SALESREGION', TO_CHAR(C_SALESREGION_ID), '" ).append( vars.getLanguage() ).append( "') AS SALESREGION FROM C_SALESREGION WHERE C_SALESREGION_ID IN" ).append( strSalesRegion ).append( ")");
256     } else columns.append("' ' AS SALESREGION, ");
257     if (strCampaign != null && strCampaign != "") {
258       columns.append("CAMPAIGN, ");
259       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_CAMPAIGN', TO_CHAR(C_CAMPAIGN_ID), '" ).append( vars.getLanguage() ).append( "') AS CAMPAIGN FROM C_CAMPAIGN WHERE C_CAMPAIGN_ID IN" ).append( strCampaign ).append( ")");
260     } else columns.append("' ' AS CAMPAIGN, ");
261     if (strActivity != null && strActivity != "") {
262       columns.append("ACTIVITY, ");
263       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_ACTIVITY', TO_CHAR(C_ACTIVITY_ID), '" ).append( vars.getLanguage() ).append( "') AS ACTIVITY FROM C_ACTIVITY WHERE C_ACTIVITY_ID IN" ).append( strActivity ).append( ")");
264     } else columns.append("' ' AS ACTIVITY, ");
265     if (strProject != null && strProject != "") {
266       columns.append("PROJECT, ");
267       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_PROJECT', TO_CHAR(C_PROJECT_ID), '" ).append( vars.getLanguage() ).append( "') AS PROJECT FROM C_PROJECT WHERE C_PROJECT_ID IN" ).append( strProject ).append( ")");
268     } else columns.append("' ' AS PROJECT, ");
269     if (strTrxOrg != null && strTrxOrg != "") {
270       columns.append("TRXORG, ");
271       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('AD_ORG', TO_CHAR(AD_ORG_ID), '" ).append( vars.getLanguage() ).append( "') AS TRXORG FROM AD_ORG WHERE AD_ORG_ID IN").append( strTrxOrg ).append( ")");
272     } else columns.append("' ' AS TRXORG, ");
273     if (strMonth != null && strMonth != "") {
274       columns.append("MONTH, ");
275       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('AD_MONTH', TO_CHAR(AD_MONTH_ID), '" ).append( vars.getLanguage() ).append( "') AS MONTH FROM AD_MONTH WHERE AD_MONTH_ID IN").append( strMonth ).append( ")");
276     } else columns.append("' ' AS MONTH, ");
277     if (strValidcombination != null && strValidcombination != "") {
278       columns.append("VALIDCOMBINATION, ");
279       tables.append(", (SELECT AD_COLUMN_IDENTIFIER('C_VALIDCOMBINATION', TO_CHAR(C_VALIDCOMBINATION_ID), '" ).append( vars.getLanguage() ).append( "' ) AS VALIDCOMBINATION FROM C_VALIDCOMBINATION WHERE C_VALIDCOMBINATION_ID = ").append( strValidcombination ).append( ")");
280     } else columns.append("' ' AS VALIDCOMBINATION, ");
281
282     //Adds currency to the excel sheet, Euro is default currency
283 columns.append(" 'EUR' AS CURRENCY ");
284
285     response.setContentType("application/xls");
286     PrintWriter out = response.getWriter();
287
288     XmlDocument xmlDocument=null;
289     ReportBudgetGenerateExcelData[] data=null;
290     data = ReportBudgetGenerateExcelData.select(this, columns.toString(), tables.toString());
291
292     xmlDocument = xmlEngine.readXmlTemplate("org/openbravo/erpCommon/ad_reports/ReportBudgetGenerateExcelXLS").createXmlDocument();
293
294
295     xmlDocument.setData("structure1", data);
296     out.println(xmlDocument.print());
297     out.close();
298   }
299
300   public String getServletInfo() {
301     return "Servlet ReportBudgetGenerateExcel.";
302   } // end of getServletInfo() method
303 }
304

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags