FinStatement


1   /******************************************************************************
2    * The contents of this file are subject to the   Compiere License  Version 1.1
3    * ("License"); You may not use this file except in compliance with the License
4    * You may obtain a copy of the License at http://www.compiere.org/license.html
5    * Software distributed under the License is distributed on an  "AS IS"  basis,
6    * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7    * the specific language governing rights and limitations under the License.
8    * The Original Code is             Compiere  ERP & CRM Smart Business Solution
9    * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
10   * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.report;
15  
16  import java.util.*;
17  import java.sql.*;
18  import java.math.*;
19  import java.io.Serializable  ;
20  
21  import org.compiere.process.*;
22  import org.compiere.model.*;
23  import org.compiere.print.*;
24  import org.compiere.util.*;
25  
26  /**
27   *  Statement of Account
28   *
29   *  @author Jorg Janke
30   *  @version $Id: FinStatement.java,v 1.6 2003/08/04 03:54:46 jjanke Exp $
31   */
32  public class FinStatement extends SvrProcess
33  {
34      /**
35       *
36       */
37      public FinStatement()
38      {
39          super();
40          log.info(" ");
41      }   //  FinStatement
42  
43      /** AcctSchame Parameter            */
44      private int                 m_C_AcctSchema_ID = 0;
45      /** Period Parameter                */
46      private int                 m_C_Period_ID = 0;
47      private Timestamp           m_DateAcct_From = null;
48      private Timestamp           m_DateAcct_To = null;
49      /** Org Parameter                   */
50      private int                 m_AD_Org_ID = 0;
51      /** Account Parameter               */
52      private int                 m_Account_ID = 0;
53      /** BPartner Parameter              */
54      private int                 m_C_BPartner_ID = 0;
55      /** Product Parameter               */
56      private int                 m_M_Product_ID = 0;
57      /** Project Parameter               */
58      private int                 m_C_Project_ID = 0;
59      /** Activity Parameter              */
60      private int                 m_C_Activity_ID = 0;
61      /** SalesRegion Parameter           */
62      private int                 m_C_SalesRegion_ID = 0;
63      /** Campaign Parameter              */
64      private int                 m_C_Campaign_ID = 0;
65  
66      /** Parameter Where Clause          */
67      private StringBuffer          m_parameterWhere = new StringBuffer  ();
68  
69  
70      /**
71       *  Prepare - e.g., get Parameters.
72       */
73      protected void prepare()
74      {
75          StringBuffer   sb = new StringBuffer   ("FinStatement Prepare - Record_ID=")
76              .append(getRecord_ID());
77          //  Parameter
78          ProcessInfoParameter[] para = getParameter();
79          for (int i = 0; i < para.length; i++)
80          {
81              String   name = para[i].getParameterName();
82              if (para[i].getParameter() == null)
83                  ;
84              else if (name.equals("C_AcctSchema_ID"))
85                  m_C_AcctSchema_ID = ((BigDecimal)para[i].getParameter()).intValue();
86              else if (name.equals("C_Period_ID"))
87                  m_C_Period_ID = ((BigDecimal)para[i].getParameter()).intValue();
88              else if (name.equals("DateAcct"))
89              {
90                  m_DateAcct_From = (Timestamp)para[i].getParameter();
91                  m_DateAcct_To = (Timestamp)para[i].getParameter_To();
92              }
93              else if (name.equals("AD_Org_ID"))
94                  m_AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
95              else if (name.equals("Account_ID"))
96                  m_Account_ID = ((BigDecimal)para[i].getParameter()).intValue();
97              else if (name.equals("C_BPartner_ID"))
98                  m_C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue();
99              else if (name.equals("M_Product_ID"))
100                 m_M_Product_ID = ((BigDecimal)para[i].getParameter()).intValue();
101             else if (name.equals("C_Project_ID"))
102                 m_C_Project_ID = ((BigDecimal)para[i].getParameter()).intValue();
103             else if (name.equals("C_Activity_ID"))
104                 m_C_Activity_ID = ((BigDecimal)para[i].getParameter()).intValue();
105             else if (name.equals("C_SalesRegion_ID"))
106                 m_C_SalesRegion_ID = ((BigDecimal)para[i].getParameter()).intValue();
107             else if (name.equals("C_Campaign_ID"))
108                 m_C_Campaign_ID = ((BigDecimal)para[i].getParameter()).intValue();
109             else
110                 log.error("prepare - Unknown Parameter: " + name);
111         }
112         //  Mandatory C_AcctSchema_ID
113         m_parameterWhere.append("C_AcctSchema_ID=").append(m_C_AcctSchema_ID);
114         //  Optional Org
115         if (m_AD_Org_ID != 0)
116             m_parameterWhere.append(" AND AD_Org_ID=").append(m_AD_Org_ID);
117         //  Optional Account
118         if (m_Account_ID != 0)
119             m_parameterWhere.append(" AND Account_ID=").append(m_Account_ID);
120         //  Optional BPartner
121         if (m_C_BPartner_ID != 0)
122             m_parameterWhere.append(" AND C_BPartner_ID=").append(m_C_BPartner_ID);
123         //  Optional Product
124         if (m_M_Product_ID != 0)
125             m_parameterWhere.append(" AND M_Product_ID=").append(m_M_Product_ID);
126         //  Optional Project
127         if (m_C_Project_ID != 0)
128             m_parameterWhere.append(" AND C_Project_ID=").append(m_C_Project_ID);
129         //  Optional Activity
130         if (m_C_Activity_ID != 0)
131             m_parameterWhere.append(" AND C_Activity_ID=").append(m_C_Activity_ID);
132         //  Optional Campaign
133         if (m_C_Campaign_ID != 0)
134             m_parameterWhere.append(" AND C_Campaign_ID=").append(m_C_Campaign_ID);
135         //  Optional Sales Region
136         if (m_C_SalesRegion_ID != 0)
137             m_parameterWhere.append(" AND C_SalesRegion_ID=").append(m_C_SalesRegion_ID);
138         //
139         setDateAcct();
140         sb.append(" - DateAcct ").append(m_DateAcct_From).append("-").append(m_DateAcct_To);
141         sb.append(" - ").append(m_parameterWhere);
142         log.debug(sb.toString());
143     }   //  prepare
144 
145     /**
146      *  Set Start/End Date of Report - if not defined current Month
147      */
148     private void setDateAcct()
149     {
150         //  Date defined
151         if (m_DateAcct_From != null)
152         {
153             if (m_DateAcct_To == null)
154                 m_DateAcct_To = new Timestamp (System.currentTimeMillis());
155             return;
156         }
157         //  Get Date from Period
158         if (m_C_Period_ID == 0)
159         {
160            GregorianCalendar cal = new GregorianCalendar(Language.getLanguage().getLocale());
161            cal.setTimeInMillis(System.currentTimeMillis());
162            cal.set(Calendar.HOUR_OF_DAY, 0);
163            cal.set(Calendar.MINUTE, 0);
164            cal.set(Calendar.SECOND, 0);
165            cal.set(Calendar.MILLISECOND, 0);
166            cal.set(Calendar.DAY_OF_MONTH, 1);       //  set to first of month
167            m_DateAcct_From = new Timestamp (cal.getTimeInMillis());
168            cal.add(Calendar.MONTH, 1);
169            cal.add(Calendar.DAY_OF_YEAR, -1);       //  last of month
170            m_DateAcct_To = new Timestamp (cal.getTimeInMillis());
171            return;
172         }
173 
174         String   sql = "SELECT StartDate, EndDate FROM C_Period WHERE C_Period_ID=?";
175         PreparedStatement pstmt = null;
176         try
177         {
178             pstmt = DB.prepareStatement(sql);
179             pstmt.setInt(1, m_C_Period_ID);
180             ResultSet rs = pstmt.executeQuery();
181             if (rs.next())
182             {
183                 m_DateAcct_From = rs.getTimestamp(1);
184                 m_DateAcct_To = rs.getTimestamp(2);
185             }
186             rs.close();
187             pstmt.close();
188             pstmt = null;
189         }
190         catch (Exception   e)
191         {
192             log.error("setDateAcct", e);
193         }
194         finally
195         {
196             try
197             {
198                 if (pstmt != null)
199                     pstmt.close ();
200             }
201             catch (Exception   e)
202             {}
203             pstmt = null;
204         }
205     }   //  setDateAcct
206 
207     /*************************************************************************/
208 
209     /**
210      *  Perform process.
211      *  @return Message to be translated
212      *  @throws Exception
213      */
214     protected String   doIt()
215     {
216         long start = System.currentTimeMillis();
217 
218         createBalanceLine();
219         createDetailLines();
220 
221         int AD_PrintFormat_ID = 134;
222         getProcessInfo().setTransientObject (MPrintFormat.get (AD_PrintFormat_ID, false));
223 
224         log.debug("doIt - " + (System.currentTimeMillis() - start) + " ms");
225         return "";
226     }   //  doIt
227 
228     /**
229      *  Create Beginning Balance Line
230      */
231     private void createBalanceLine()
232     {
233         StringBuffer   sb = new StringBuffer   ("INSERT INTO T_ReportStatement "
234             + "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
235             + "DateAcct, Name, Description,"
236             + "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
237         sb.append("SELECT ").append(getAD_PInstance_ID()).append(",0,0,")
238             .append(DB.TO_DATE(m_DateAcct_From, true)).append(",")
239             .append(DB.TO_STRING(Msg.getMsg(Env.getCtx(), "BeginningBalance"))).append(",NULL,"
240             + "COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(AmtAcctDr-AmtAcctCr),0), COALESCE(SUM(Qty),0) "
241             + "FROM Fact_Acct_Balance "
242             + "WHERE ").append(m_parameterWhere)
243             .append(" AND DateAcct <= ").append(DB.TO_DATE(m_DateAcct_From));
244         int no = DB.executeUpdate(sb.toString());
245         log.debug("createBalanceLine #" + no);
246     }   //  createBalanceLine
247 
248     /**
249      *  Create Beginning Balance Line
250      */
251     private void createDetailLines()
252     {
253         StringBuffer   sb = new StringBuffer   ("INSERT INTO T_ReportStatement "
254             + "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
255             + "DateAcct, Name, Description,"
256             + "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
257         sb.append("SELECT ").append(getAD_PInstance_ID()).append(",Fact_Acct_ID,1,")
258             .append("DateAcct,NULL,NULL,"
259             + "AmtAcctDr, AmtAcctCr, AmtAcctDr-AmtAcctCr, Qty "
260             + "FROM Fact_Acct "
261             + "WHERE ").append(m_parameterWhere)
262             .append(" AND DateAcct BETWEEN ").append(DB.TO_DATE(m_DateAcct_From))
263             .append(" AND ").append(DB.TO_DATE(m_DateAcct_To));
264         int no = DB.executeUpdate(sb.toString());
265         log.debug("createDetailLine #" + no);
266 
267         //  Set Name,Description
268         String   sql_select = "SELECT e.Name, fa.Description "
269             + "FROM Fact_Acct fa"
270             + " INNER JOIN AD_Table t ON (fa.AD_Table_ID=t.AD_Table_ID)"
271             + " INNER JOIN AD_Element e ON (t.TableName||'_ID'=e.ColumnName) "
272             + "WHERE r.Fact_Acct_ID=fa.Fact_Acct_ID";
273         //  Translated Version ...
274         sb = new StringBuffer   ("UPDATE T_ReportStatement r SET (Name,Description)=(")
275             .append(sql_select).append(") "
276             + "WHERE Fact_Acct_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID());
277        no = DB.executeUpdate(sb.toString());
278        log.debug("Trx Name #=" + no);//, sql.toString());
279     }   //  createBalanceLine
280 
281 }   //  FinStatement
282
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags