MCashBook


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  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-2001 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.model;
15  
16  import java.math.*;
17  import java.sql.*;
18  import java.util.*;
19  import java.io.Serializable  ;
20  
21  import org.compiere.util.*;
22  
23  /**
24   *  CashBook Model.
25   *  <pre>
26   *  Event chain:
27   *  - C_CashLine is inserted
28   *      C_Cash_Post
29   *          create allocation line
30   *              C_Allocation_Trg fires
31   *                  Update C_BPartner Open Item Amount
32   *          update invoice (IsPaid)
33   *
34   *  LifeLine:
35   *  - When Invoice is posted
36   *      - Default CashBook entry is generated and link invoice-cashline created
37   *
38   *  - When an invoice is reversed, a new (reversing) CashBook entry is created
39   *
40   *  - When a CashBook entry is changed (date, ..)
41   *      - the old entry is reversed
42   *      - the new entry is created
43   *  </pre>
44   *  @see org.compiere.apps.VPayment#save
45   *  @see "C_Order_Post.sql, C_Cash_Post.sql"
46   *
47   *  @author     Jorg Janke
48   *  @version    $Id: MCashBook.java,v 1.10 2002/10/08 04:28:40 jjanke Exp $
49   */
50  public final class MCashBook implements Serializable  
51  {
52      /**
53       *  Load CashBook Line
54       *  @param CashLine_ID cash line
55       */
56      public MCashBook (int CashLine_ID)
57      {
58          Log.trace(Log.l3_Util, "MCashBook - " + CashLine_ID);
59          C_CashLine_ID = CashLine_ID;
60          String   sql = "SELECT c.C_CashBook_ID,c.C_Cash_ID,c.StatementDate,c.Processed, "
61              + "cl.Line,cl.Amount, cl.AD_Client_ID,cl.AD_Org_ID,cl.Description,cl.C_Invoice_ID "
62              + "FROM C_Cash c, C_CashLine cl "
63              + "WHERE c.C_Cash_ID=cl.C_Cash_ID AND cl.C_CashLine_ID=?";
64          try
65          {
66              PreparedStatement pstmt = DB.prepareStatement(sql);
67              pstmt.setInt(1, C_CashLine_ID);
68              ResultSet rs = pstmt.executeQuery();
69              if (rs.next())
70              {
71                  C_CashBook_ID = rs.getInt(1);
72                  C_Cash_ID = rs.getInt(2);
73                  StatementDate = rs.getTimestamp(3);
74                  Processed = rs.getString(4).equals("Y");
75                  Line = rs.getInt(5);
76                  Amount = rs.getBigDecimal(6);
77                  AD_Client_ID = rs.getInt(7);
78                  AD_Org_ID = rs.getInt(8);
79                  Description = rs.getString(9);
80                  if (Description == null)
81                      Description = "";
82                  C_Invoice_ID = rs.getInt(10);
83              }
84              else
85                  C_CashLine_ID = 0;
86              rs.close();
87              pstmt.close();
88          }
89          catch (SQLException e)
90          {
91              Log.error ("MCashBook.Constructor", e);
92              C_CashLine_ID = 0;
93          }
94      }   //  MCashBook
95  
96      private int         C_CashLine_ID = 0;
97      private int         C_Cash_ID = 0;
98      private int         Line = 0;
99      private String        Description = "";
100     private BigDecimal  Amount = new BigDecimal(0.0);
101     private int         C_CashBook_ID = 0;
102     private int         AD_Client_ID = 0;
103     private int         AD_Org_ID = 0;
104     private int         C_Invoice_ID = 0;
105     private boolean     Processed = false;
106     private Timestamp   StatementDate = null;
107 
108     /*************************************************************************/
109 
110     /**
111      *  Create CashBook Entry Line.
112      *
113      *  a) Find CashBook for Trx - based on Org_ID
114      *
115      *  @see "C_Post_Invoice.sql"
116      *  @param ctx context
117      *  @param WindowNo window no
118      *  @param AD_Client_ID client id
119      *  @param AD_Org_ID org id
120      *  @param StatementDate statement date
121      *  @param C_Invoice_ID invoice id
122      *  @param Amount amount
123      *  @param Description description
124      *  @return C_CashLine_ID or 0 if failure
125      */
126     public static int createCashBookEntry(Properties ctx, int WindowNo,
127         int AD_Client_ID, int AD_Org_ID, Timestamp StatementDate,
128         int C_Invoice_ID, BigDecimal Amount, String   Description)
129     {
130         //  a) Find first CashBook for Trx
131         int C_CashBook_ID = 0;
132         String   sql = "SELECT C_CashBook_ID FROM C_CashBook "
133             + "WHERE AD_Org_ID=? ORDER BY Created";
134         try
135         {
136             PreparedStatement pstmt = DB.prepareStatement(sql);
137             pstmt.setInt(1, AD_Org_ID);
138             ResultSet rs = pstmt.executeQuery();
139             if (rs.next())
140                 C_CashBook_ID = rs.getInt(1);
141             rs.close();
142             pstmt.close();
143         }
144         catch (SQLException e)
145         {
146             Log.error ("MCashBook.createCashBookEntry-1", e);
147         }
148         if (C_CashBook_ID == 0)
149         {
150             Log.error("MCashBook.createCashBookEntry - No CashBook for AD_Org_ID=" + AD_Org_ID);
151             return 0;
152         }
153 
154         return createCashBookEntry(ctx, WindowNo, AD_Client_ID, AD_Org_ID,
155             StatementDate, C_Invoice_ID, Amount, Description, C_CashBook_ID);
156     }   //  createCashBookEntry
157 
158     /**
159      *  Create CashBook Entry Line.
160      *
161      *  b) Find/Create Cash Entry
162      *  c) Create Cash Line Entry
163      *
164      *  @param ctx context
165      *  @param WindowNo window no
166      *  @param AD_Client_ID client id
167      *  @param AD_Org_ID org id
168      *  @param StatementDate datament date
169      *  @param C_Invoice_ID invoice id
170      *  @param Amount amount
171      *  @param Description description
172      *  @param C_CashBook_ID cashbook id
173      *  @return C_CashLine_ID or 0 if failure
174      */
175     public static int createCashBookEntry (Properties ctx, int WindowNo,
176         int AD_Client_ID, int AD_Org_ID, Timestamp StatementDate,
177         int C_Invoice_ID, BigDecimal Amount, String   Description, int C_CashBook_ID)
178     {
179         Env.setContext(ctx, WindowNo, "AD_Client_ID", AD_Client_ID);    //  make sure
180         int user = Env.getContextAsInt(ctx, "#AD_User_ID");
181         StatementDate = TimeUtil.getDay(StatementDate);                 //  truncate
182 
183         //  b) Find/Create Cash Entry
184         int C_Cash_ID = 0;
185         //  "WHERE C_CashBook_ID=? AND TRUNC(StatementDate)=TRUNC(?)" results in
186         //  ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
187         //  C_Cash_Trg now guarantees that StatementDate is truncated.
188         String   sql = "SELECT C_Cash_ID "
189             + "FROM C_Cash "
190             + "WHERE C_CashBook_ID=? AND StatementDate=?"       //  #1/2
191             + " AND Processed='N'";
192         try
193         {
194             PreparedStatement pstmt = DB.prepareStatement(sql);
195             pstmt.setInt(1, C_CashBook_ID);
196             pstmt.setTimestamp (2, StatementDate);
197             ResultSet rs = pstmt.executeQuery();
198             if (rs.next())
199                 C_Cash_ID = rs.getInt(1);
200             rs.close();
201             pstmt.close();
202         }
203         catch (SQLException e)
204         {
205             Log.error ("MCashBook.createCashBookEntry-2\nSQL=" + sql
206                 + " - C_CashBook_ID=" + C_CashBook_ID + ", StatementDate=" + StatementDate, e);
207         }
208         //  Create Cash Entry
209         if (C_Cash_ID == 0)
210         {
211             StringBuffer   isql = new StringBuffer  ("INSERT INTO C_Cash "
212                 + "(C_Cash_ID,AD_Client_ID,AD_Org_ID, "
213                 + "IsActive,Created,CreatedBy,Updated,UpdatedBy, "
214                 + "C_CashBook_ID, Name,StatementDate,DateAcct,"
215                 + "BeginningBalance,EndingBalance,StatementDifference, "
216                 + "Processing,Processed,Posted) VALUES (");
217             //
218             C_Cash_ID = DB.getKeyNextNo(ctx, WindowNo, "C_Cash");
219             isql.append(C_Cash_ID).append(",");
220             isql.append(AD_Client_ID).append(",").append(AD_Org_ID);
221             //
222             isql.append(",'Y',SysDate,").append(user).append(",SysDate,").append(user).append(",");
223             isql.append(C_CashBook_ID).append(",");
224             String   date = StatementDate.toString().substring(0,10);
225             isql.append("'").append(date).append("',");             //  Name
226             date = DB.TO_DATE(StatementDate, true) + ",";
227             isql.append(date).append(date);                         //  Statement/Acct Date
228             isql.append("0,0,0,'N','N','N')");
229             //
230             int no = DB.executeUpdate(isql.toString());
231             //
232             if (no == 1)
233                 Log.trace(Log.l3_Util, "MCashBook.createCashBookEntry - C_Cash_ID=" + C_Cash_ID);
234             else
235             {
236                 Log.error("MCashBook.createCashBookEntry - Not Inserted - C_Cash_ID=" + C_Cash_ID);
237                 return 0;
238             }
239         }
240 
241         //  c) Create Cash Line Entry
242         StringBuffer   isql = new StringBuffer  ("INSERT INTO C_CashLine "
243             + "(C_CashLine_ID,AD_Client_ID,AD_Org_ID, "
244             + "IsActive,Created,CreatedBy,Updated,UpdatedBy, "
245             + "C_Cash_ID,C_Invoice_ID, "
246             + "Line, Description,Amount,CashType, "
247             + "DiscountAmt,WriteOffAmt,IsGenerated"
248             + ") VALUES (");
249         //
250         int C_CashLine_ID = DB.getKeyNextNo(ctx, WindowNo, "C_CashLine");
251         isql.append(C_CashLine_ID).append(",")
252             .append(AD_Client_ID).append(",").append(AD_Org_ID)
253             .append(",'Y',SysDate,").append(user).append(",SysDate,").append(user).append(", ");
254         //  C_Cash_ID,C_Invoice_ID,
255         isql.append(C_Cash_ID);
256         if (C_Invoice_ID == 0)
257             isql.append(",NULL, ");
258         else
259             isql.append(",").append(C_Invoice_ID).append(", ");
260         //  Line, Description,Amount,CashType,
261         isql.append("(SELECT NVL(MAX(Line),0)+10 FROM C_CashLine WHERE C_Cash_ID=")
262             .append(C_Cash_ID).append("),");
263         if (Description == null || Description.length() == 0)
264             isql.append("NULL,");
265         else
266             isql.append("'").append(Description).append("',");
267         isql.append(Amount)
268             .append(",'I',");
269         //  DiscountAmt,WriteOffAmt,IsGenerated
270         isql.append("0,0,'Y')");
271         int no = DB.executeUpdate(isql.toString());
272         //
273         if (no == 1)
274             Log.trace(Log.l3_Util, "MCashBook.createCashBookEntry - C_CashLine_ID=" + C_CashLine_ID
275                 + ", C_Invoice_ID=" + C_Invoice_ID + ", Amount=" + Amount + " - " + Description);
276         else
277         {
278             Log.error("MCashBook.createCashBookEntry - Not Inserted - C_CashLine_ID=" + C_CashLine_ID);
279             C_CashLine_ID = 0;
280         }
281         return C_CashLine_ID;
282     }   //  createCashBookEntry
283 
284     /*************************************************************************/
285 
286     /**
287      *  Update if valid
288      *  @return true if updated
289      */
290     public boolean update()
291     {
292         if (C_CashLine_ID == 0 || Processed)
293             return false;
294         boolean retValue = true;
295         //
296         StringBuffer   sql = new StringBuffer   ("UPDATE C_CashLine SET Description=");
297         if (Description == null || Description.length() == 0)
298             sql.append("NULL,");
299         else
300             sql.append("'").append(Description).append("',");
301         sql.append("Amount=").append(Amount.toString());
302         sql.append(" WHERE C_CashLine_ID=").append(C_CashLine_ID);
303         //
304         int no = DB.executeUpdate(sql.toString());
305         if (no == 1)
306             Log.trace(Log.l3_Util, "MCashBook.update - success");
307         else
308         {
309             Log.error("MCashBook.update - Not Updated - C_CashLine_ID=" + C_CashLine_ID);
310             retValue = false;
311         }
312         return retValue;
313     }   //  update
314 
315     /**
316      *  Reverse Entry
317      *  @param ctx context
318      *  @param WindowNo window no
319      *  @return true if OK
320      */
321     public boolean reverse(Properties ctx, int WindowNo)
322     {
323         //  C_CashLine_ID of reversed entry or 0 if error
324         int no = createCashBookEntry (ctx, WindowNo, AD_Client_ID, AD_Org_ID,
325             StatementDate, C_Invoice_ID, Amount.negate(),
326             Description + " (" + Line + ")", C_CashBook_ID);
327         return (no != 0);
328     }   //  reverse
329 
330     /**
331      *  Delete Entry
332      *  @return true if deleted
333      */
334     public boolean delete()
335     {
336         String   sql = "DELETE C_CashLine WHERE C_CashLine_ID=" + C_CashLine_ID;
337         int no = DB.executeUpdate(sql);
338         return (no == 1);
339     }   //  delete
340 
341     /**************************************************************************/
342 
343     /**
344      *  CashLine
345      *  @return cash line id
346      */
347     public int getC_CashLine_ID()
348     {
349         return C_CashLine_ID;
350     }
351 
352     /**
353      *  Cash
354      *  @return cash id
355      */
356     public int getC_Cash_ID()
357     {
358         return C_Cash_ID;
359     }
360 
361     /**
362      *  Line No
363      *  @return line
364      */
365     public int getLine()
366     {
367         return Line;
368     }
369 
370     /**
371      *  Description
372      *  @param newDescription description
373      */
374     public void setDescription(String   newDescription)
375     {
376         Description = newDescription;
377     }
378     public String   getDescription()
379     {
380         return Description;
381     }
382 
383     /**
384      *  Amount
385      *  @param newAmount amount
386      */
387     public void setAmount (BigDecimal newAmount)
388     {
389         Amount = newAmount;
390     }
391     public java.math.BigDecimal   getAmount()
392     {
393         return Amount;
394     }
395 
396     /**
397      *  CashBook
398      *  @return cash book id
399      */
400     public int getC_CashBook_ID()
401     {
402         return C_CashBook_ID;
403     }
404 
405     /**
406      *  Processed
407      *  @return true if processed
408      */
409     public boolean isProcessed()
410     {
411         return Processed;
412     }
413 
414     /**
415      *  StatementDate
416      *  @return datement date
417      */
418     public Timestamp getStatementDate()
419     {
420         return StatementDate;
421     }
422 
423     /**
424      *  Is Same Date
425      *  @param compare date to compare
426      *  @return true if same date as statement date
427      */
428     public boolean isSameDate (Timestamp compare)
429     {
430         if (compare == null)
431             return false;
432         //  JDBC date format YYYY-MM-DD
433         String   stmt = StatementDate.toString().substring(0,10);
434         String   cmp = compare.toString().substring(0,10);
435         //
436         Log.trace(Log.l5_DData, "MCashBook.isSameDate - " + stmt + " " + cmp);
437         return stmt.compareTo(cmp) == 0;
438     }   //  isSameDate
439 
440 }   //  MCashBook
441
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags