MPaySelectionCheck


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.sql.*;
17  import java.math.*;
18  import java.util.*;
19  import java.text.*;
20  import java.io.*;
21  
22  import org.compiere.model.*;
23  import org.compiere.util.*;
24  
25  /**
26   *  Payment Print/Export model.
27   *
28   *  @author     Jorg Janke
29   *  @version    $Id: MPaySelectionCheck.java,v 1.12 2003/10/04 03:51:50 jjanke Exp $
30   */
31  public final class MPaySelectionCheck extends X_C_PaySelectionCheck
32  {
33      /**
34       *  Constructor
35       *  @param ctx context
36       *  @param C_PaySelectionCheck_ID C_PaySelectionCheck_ID
37       */
38      public MPaySelectionCheck (Properties ctx, int C_PaySelectionCheck_ID)
39      {
40          super(ctx, C_PaySelectionCheck_ID);
41      }   //  MPaySelectionCheck
42  
43      /**
44       *  Constructor
45       *  @param ctx context
46       *  @param rs result set
47       */
48      public MPaySelectionCheck(Properties ctx, ResultSet rs)
49      {
50          super(ctx, rs);
51      }   //  MPaySelectionCheck
52  
53      static private Logger       s_log = Logger.getCLogger (MPaySelectionCheck.class);
54  
55      /*************************************************************************/
56  
57      /**
58       *  String Representation
59       *  @return info
60       */
61      public String   toString()
62      {
63          StringBuffer   sb = new StringBuffer  ("MPaymentCheck[");
64          sb.append(getID()).append("-").append(getDocumentNo())
65              .append("-").append(getPayAmt()).append("-").append(getPayDate())
66              .append(",PaymetRule=").append(getPaymentRule())
67              .append(",Qty=").append(getQty())
68              .append("]");
69          return sb.toString();
70      }   //  toString
71  
72  
73      private int         m_C_BankAccount_ID = -1;
74      private int         m_C_Currency_ID = -1;
75      private Timestamp   m_PayDate = null;
76  
77      /**
78       *  Payment Date
79       *  @return PayDate
80       */
81      public Timestamp getPayDate()
82      {
83          if (m_PayDate == null)
84              setPaySelectionInfo();
85          return m_PayDate;
86      }   //  getPayDate
87  
88      /**
89       *  Payment Currency
90       *  @return C_Currency_ID
91       */
92      public int getC_Currency_ID()
93      {
94          if (m_C_Currency_ID == -1)
95              setPaySelectionInfo();
96          return m_C_Currency_ID;
97      }   //  getC_Currency_ID
98  
99      /**
100      *  Bank Account
101      *  @return C_BankAccount_ID
102      */
103     public int getC_BankAccount_ID()
104     {
105         if (m_C_BankAccount_ID == -1)
106             setPaySelectionInfo();
107         return m_C_BankAccount_ID;
108     }   //  getC_BankAccount_ID
109 
110     /**
111      *  Get PaySelection Info
112      */
113     private void setPaySelectionInfo()
114     {
115         String   sql = "SELECT ps.C_BankAccount_ID, ps.PayDate, ba.C_Currency_ID "
116             + "FROM C_PaySelection ps"
117             + " INNER JOIN C_BankAccount ba ON (ps.C_BankAccount_ID=ba.C_BankAccount_ID) "
118             + "WHERE ps.C_PaySelection_ID=?";
119         try
120         {
121             PreparedStatement pstmt = DB.prepareStatement(sql);
122             pstmt.setInt(1, getC_PaySelection_ID());
123             ResultSet rs = pstmt.executeQuery();
124             if (rs.next())
125             {
126                 m_C_BankAccount_ID = rs.getInt(1);
127                 m_PayDate = rs.getTimestamp(2);
128                 m_C_Currency_ID = rs.getInt(3);
129             }
130             else
131                 log.error("setPaySelectionInfo - Not found for ID=" + getC_PaySelection_ID());
132             rs.close();
133             pstmt.close();
134         }
135         catch (SQLException e)
136         {
137             log.error("setPaySelectionInfo", e);
138         }
139     }   //  setPaySelectionInfo
140 
141     /*************************************************************************/
142 
143     /**
144      *  Get Invoice IDs
145      *  @return array of C_Invoice_ID
146      */
147     protected PaySelectionLine[] getPaySelectionLines ()
148     {
149         ArrayList list = new ArrayList();
150         String   sql = "SELECT psl.C_Invoice_ID, psl.PayAmt, psl.DifferenceAmt,"
151             + " i.DocumentNo, i.GrandTotal, i.C_Currency_ID, i.C_BPartner_ID "
152             + "FROM C_PaySelectionLine psl"
153             + " INNER JOIN C_Invoice i ON (psl.C_Invoice_ID=i.C_Invoice_ID) "
154             + "WHERE C_PaySelectionCheck_ID=?";
155         try
156         {
157             PreparedStatement pstmt = DB.prepareStatement(sql);
158             pstmt.setInt(1, getID());
159             ResultSet rs = pstmt.executeQuery();
160             while (rs.next())
161             {
162                 int C_Invoice_ID = rs.getInt(1);
163                 BigDecimal PayAmt = rs.getBigDecimal(2);
164                 BigDecimal DifferenceAmt = rs.getBigDecimal(3);
165                 String   DocumentNo = rs.getString(4);
166                 BigDecimal GrandTotal = rs.getBigDecimal(5);
167                 int C_Currency_ID = rs.getInt(6);
168                 int C_BPartner_ID = rs.getInt(7);
169                 //
170                 PaySelectionLine psl = new PaySelectionLine(C_Invoice_ID, DocumentNo,
171                     GrandTotal, PayAmt, DifferenceAmt, C_Currency_ID, C_BPartner_ID);
172                 list.add(psl);
173             }
174             rs.close();
175             pstmt.close();
176         }
177         catch (SQLException e)
178         {
179             log.error("getPaySelectionLines", e);
180         }
181         //
182         PaySelectionLine[] retValue = new PaySelectionLine[list.size()];
183         list.toArray(retValue);
184         return retValue;
185     }   //  getInvoiceInfo
186 
187     /**
188      *  Payment Selection Line Info (Value Object)
189      */
190     class PaySelectionLine
191     {
192         public PaySelectionLine (int C_Invoice_ID, String   DocumentNo,
193             BigDecimal GrandTotal, BigDecimal PayAmt, BigDecimal DifferenceAmt,
194             int C_Currency_ID, int C_BPartner_ID)
195         {
196             this.C_Invoice_ID = C_Invoice_ID;
197             this.DocumentNo = DocumentNo;
198             if (GrandTotal != null)
199                 this.GrandTotal = GrandTotal;
200             if (PayAmt != null)
201                 this.PayAmt = PayAmt;
202             if (DifferenceAmt != null)
203                 this.DifferenceAmt = DifferenceAmt;
204             this.C_Currency_ID = C_Currency_ID;
205             this.C_BPartner_ID = C_BPartner_ID;
206         }
207         public int          C_Invoice_ID;
208         public String         DocumentNo;
209         public BigDecimal   GrandTotal = Env.ZERO;
210         public BigDecimal   PayAmt = Env.ZERO;
211         public BigDecimal   DifferenceAmt = Env.ZERO;
212         public int          C_Currency_ID;
213         public int          C_BPartner_ID;
214 
215         /**
216          *  String Representation
217          *  @return info
218          */
219         public String   toString()
220         {
221             StringBuffer   sb = new StringBuffer  ("PaySelectionLine[");
222             sb.append(DocumentNo).append(",GrandTotal=").append(GrandTotal)
223                 .append(",Difference=").append(DifferenceAmt)
224                 .append("]");
225             return sb.toString();
226         }   //  toString
227 
228     }   //  PaySelectionLine
229 
230 
231     /*************************************************************************/
232 
233     /**
234      *  Get Checks of Payment Selection
235      *
236      *  @param C_PaySelection_ID Payment Selection
237      *  @param PaymentRule Payment Rule
238      *  @param startDocumentNo start document no
239      *  @return array of checks
240      */
241     static public MPaySelectionCheck[] get (int C_PaySelection_ID,
242         String   PaymentRule, int startDocumentNo)
243     {
244         s_log.debug("get - C_PaySelection_ID=" + C_PaySelection_ID
245             + ", PaymentRule=" +  PaymentRule + ", startDocumentNo=" + startDocumentNo);
246         ArrayList list = new ArrayList();
247 
248         int docNo = startDocumentNo;
249         String   sql = "SELECT * FROM C_PaySelectionCheck "
250             + "WHERE C_PaySelection_ID=? AND PaymentRule=?";
251         try
252         {
253             PreparedStatement pstmt = DB.prepareStatement(sql);
254             pstmt.setInt(1, C_PaySelection_ID);
255             pstmt.setString(2, PaymentRule);
256             ResultSet rs = pstmt.executeQuery();
257             while (rs.next())
258             {
259                 MPaySelectionCheck c = new MPaySelectionCheck (Env.getCtx(), rs);
260                 c.setDocumentNo(String.valueOf(docNo++));       //  set Check Document No
261                 list.add(c);
262             }
263             rs.close();
264             pstmt.close();
265         }
266         catch (SQLException e)
267         {
268             s_log.error("get", e);
269         }
270 
271         //  convert to Array
272         MPaySelectionCheck[] retValue = new MPaySelectionCheck[list.size()];
273         list.toArray(retValue);
274         return retValue;
275     }   //  createPayments
276 
277     /*************************************************************************/
278 
279     /**
280      *  Export to File
281      *  @param checks array of checks
282      *  @param file file to export checks
283      *  @return number of lines
284      */
285     public static int exportToFile (MPaySelectionCheck[] checks, File file)
286     {
287         if (checks == null || checks.length == 0)
288             return 0;
289         //  Must be a file
290         if (file.isDirectory())
291         {
292             s_log.error("exportToFile - file is directory - " + file.getAbsolutePath());
293             return 0;
294         }
295         //  delete if exists
296         try
297         {
298             if (file.exists())
299                 file.delete();
300         }
301         catch (Exception   e)
302         {
303             s_log.error("exportToFile - could not delete - " + file.getAbsolutePath(), e);
304         }
305 
306         char x = '"';      //  ease
307         int noLines = 0;
308         StringBuffer   line = null;
309         try
310         {
311             FileWriter fw = new FileWriter(file);
312 
313             //  write header
314             line = new StringBuffer  ();
315             line.append(x).append("Value").append(x).append(",")
316                 .append(x).append("Name").append(x).append(",")
317                 .append(x).append("Contact").append(x).append(",")
318                 .append(x).append("Addr1").append(x).append(",")
319                 .append(x).append("Addr2").append(x).append(",")
320                 .append(x).append("City").append(x).append(",")
321                 .append(x).append("State").append(x).append(",")
322                 .append(x).append("ZIP").append(x).append(",")
323                 .append(x).append("Country").append(x).append(",")
324                 .append(x).append("ReferenceNo").append(x).append(",")
325                 .append(x).append("DocumentNo").append(x).append(",")
326                 .append(x).append("PayDate").append(x).append(",")
327                 .append(x).append("Currency").append(x).append(",")
328                 .append(x).append("PayAmount").append(x).append(",")
329                 .append(x).append("Comment").append(x)
330                 .append(Env.NL);
331             fw.write(line.toString());
332             noLines++;
333 
334             //  write lines
335             for (int i = 0; i < checks.length; i++)
336             {
337                 MPaySelectionCheck mpp = checks[i];
338                 if (mpp == null)
339                     continue;
340                 //  BPartner Info
341                 String   bp[] = getBPartnerInfo(mpp.getC_BPartner_ID());
342 
343                 //  Comment - list of invoice document no
344                 StringBuffer   comment = new StringBuffer  ();
345                 PaySelectionLine[] psls = mpp.getPaySelectionLines();
346                 for (int l = 0; l < psls.length; l++)
347                 {
348                     if (l > 0)
349                         comment.append("_");
350                     comment.append(psls[l].DocumentNo);
351                 }
352                 line = new StringBuffer  ();
353                 line.append(x).append(bp[BP_VALUE]).append(x).append(",")   // Value
354                     .append(x).append(bp[BP_NAME]).append(x).append(",")    // Name
355                     .append(x).append(bp[BP_CONTACT]).append(x).append(",") // Contact
356                     .append(x).append(bp[BP_ADDR1]).append(x).append(",")   // Addr1
357                     .append(x).append(bp[BP_ADDR2]).append(x).append(",")   // Addr2
358                     .append(x).append(bp[BP_CITY]).append(x).append(",")    // City
359                     .append(x).append(bp[BP_REGION]).append(x).append(",")  // State
360                     .append(x).append(bp[BP_POSTAL]).append(x).append(",")  // ZIP
361                     .append(x).append(bp[BP_COUNTRY]).append(x).append(",") // Country
362                     .append(x).append(bp[BP_REFNO]).append(x).append(",")   // ReferenceNo
363                     //  Payment Info
364                     .append(x).append(mpp.getDocumentNo()).append(x).append(",")    // DocumentNo
365                     .append(mpp.getPayDate()).append(",")               // PayDate
366                     .append(x).append(getCurrencyInfo(mpp.getC_Currency_ID())).append(x).append(",")    // Currency
367                     .append(mpp.getPayAmt()).append(",")                // PayAmount
368                     .append(x).append(comment.toString()).append(x)     // Comment
369                     .append(Env.NL);
370                 fw.write(line.toString());
371                 noLines++;
372             }   //  write line
373 
374             fw.flush();
375             fw.close();
376         }
377         catch (Exception   e)
378         {
379             s_log.error("exportToFile", e);
380         }
381 
382         return noLines;
383 
384     }   //  exportToFile
385 
386     /** Currency Cache      */
387     private static CCache s_currencies = new CCache("payCurrenvies", 10);
388 
389     /**
390      *  Get Currency Description
391      *  @param C_Currency_ID currency
392      *  @return currency
393      */
394     public static String   getCurrencyInfo(int C_Currency_ID)
395     {
396         Integer   key = new Integer  (C_Currency_ID);
397         String   retValue = (String  )s_currencies.get(key);
398         if (retValue != null)
399             return retValue;
400 
401         //  Read it
402         String   sql = "SELECT ISO_Code FROM C_Currency WHERE C_Currency_ID=?";
403         try
404         {
405             PreparedStatement pstmt = DB.prepareStatement(sql);
406             pstmt.setInt(1, C_Currency_ID);
407             ResultSet rs = pstmt.executeQuery();
408             //
409             if (rs.next())
410                 retValue = rs.getString(1);
411             rs.close();
412             pstmt.close();
413         }
414         catch (SQLException e)
415         {
416             s_log.error("MPaySelectionCheck.getCurrency", e);
417         }
418 
419         //  Currency not found
420         if (retValue == null)
421         {
422             retValue = "<" + C_Currency_ID + ">";
423             s_log.error("getCurrency - NOT found ID=" + C_Currency_ID);
424         }
425 
426         s_currencies.put(key, retValue);
427         return retValue;
428     }   //  getCurrencyInfo
429 
430 
431     /** BPartner Info Index for Value       */
432     private static final int     BP_VALUE = 0;
433     /** BPartner Info Index for Name        */
434     private static final int     BP_NAME = 1;
435     /** BPartner Info Index for Contact Name    */
436     private static final int     BP_CONTACT = 2;
437     /** BPartner Info Index for Address 1   */
438     private static final int     BP_ADDR1 = 3;
439     /** BPartner Info Index for Address 2   */
440     private static final int     BP_ADDR2 = 4;
441     /** BPartner Info Index for City        */
442     private static final int     BP_CITY = 5;
443     /** BPartner Info Index for Region      */
444     private static final int     BP_REGION = 6;
445     /** BPartner Info Index for Postal Code */
446     private static final int     BP_POSTAL = 7;
447     /** BPartner Info Index for Country     */
448     private static final int     BP_COUNTRY = 8;
449     /** BPartner Info Index for Reference No    */
450     private static final int     BP_REFNO = 9;
451 
452     /**
453      *  Get Customer/Vendor Info.
454      *  Based on BP_ static variables
455      *  @param C_BPartner_ID BPartner
456      *  @return info array
457      */
458     private static String  [] getBPartnerInfo (int C_BPartner_ID)
459     {
460         String  [] bp = new String  [10];
461 
462         String   sql = "SELECT bp.Value, bp.Name, c.Name AS Contact, "
463             + "a.Address1, a.Address2, a.City, r.Name AS Region, a.Postal, "
464             + "cc.Name AS Country, bp.ReferenceNo "
465             + "FROM C_BPartner bp, AD_User c, C_BPartner_Location l, C_Location a, C_Region r, C_Country cc "
466             + "WHERE bp.C_BPartner_ID=?"        // #1
467             + " AND bp.C_BPartner_ID=c.C_BPartner_ID(+)"
468             + " AND bp.C_BPartner_ID=l.C_BPartner_ID"
469             + " AND l.C_Location_ID=a.C_Location_ID"
470             + " AND a.C_Region_ID=r.C_Region_ID(+)"
471             + " AND a.C_Country_ID=cc.C_Country_ID "
472             + "ORDER BY l.IsBillTo DESC";
473         try
474         {
475             PreparedStatement pstmt = DB.prepareStatement(sql);
476             pstmt.setInt(1, C_BPartner_ID);
477             ResultSet rs = pstmt.executeQuery();
478             //
479             if (rs.next())
480             {
481                 bp[BP_VALUE] = rs.getString(1);
482                 if (bp[BP_VALUE] == null)
483                     bp[BP_VALUE] = "";
484                 bp[BP_NAME] = rs.getString(2);
485                 if (bp[BP_NAME] == null)
486                     bp[BP_NAME] = "";
487                 bp[BP_CONTACT] = rs.getString(3);
488                 if (bp[BP_CONTACT] == null)
489                     bp[BP_CONTACT] = "";
490                 bp[BP_ADDR1] = rs.getString(4);
491                 if (bp[BP_ADDR1] == null)
492                     bp[BP_ADDR1] = "";
493                 bp[BP_ADDR2] = rs.getString(5);
494                 if (bp[BP_ADDR2] == null)
495                     bp[BP_ADDR2] = "";
496                 bp[BP_CITY] = rs.getString(6);
497                 if (bp[BP_CITY] == null)
498                     bp[BP_CITY] = "";
499                 bp[BP_REGION] = rs.getString(7);
500                 if (bp[BP_REGION] == null)
501                     bp[BP_REGION] = "";
502                 bp[BP_POSTAL] = rs.getString(8);
503                 if (bp[BP_POSTAL] == null)
504                     bp[BP_POSTAL] = "";
505                 bp[BP_COUNTRY] = rs.getString(9);
506                 if (bp[BP_COUNTRY] == null)
507                     bp[BP_COUNTRY] = "";
508                 bp[BP_REFNO] = rs.getString(10);
509                 if (bp[BP_REFNO] == null)
510                     bp[BP_REFNO] = "";
511             }
512             rs.close();
513             pstmt.close();
514         }
515         catch (SQLException e)
516         {
517             s_log.error("getBPartnerInfo", e);
518         }
519         return bp;
520     }   //  getBPartnerInfo
521 
522     /*************************************************************************/
523 
524     /**
525      *  Confirm Print
526      *  @param checks checks
527      *  @return last Document number or 0 if nothing printed
528      */
529     public static int confirmPrint (MPaySelectionCheck[] checks)
530     {
531         int lastDocumentNo = 0;
532         for (int i = 0; i < checks.length; i++)
533         {
534             MPaySelectionCheck check = checks[i];
535             MPayment payment = new MPayment(check.getCtx(), check.getC_Payment_ID());
536             if (check.getC_Payment_ID() != 0)
537             {
538                 //  save dcheck (new doc no)
539                 check.save();
540                 //  Update check number
541                 payment.setCheckNo(check.getDocumentNo());
542                 payment.save();
543             }
544             else    //  New Payment
545             {
546                 payment = new MPayment(check.getCtx(), 0);
547                 //
548                 if (check.getPaymentRule().equals(PAYMENTRULE_Check))
549                     payment.setBankCheck (check.getC_BankAccount_ID(), false, check.getDocumentNo());
550                 else if (check.getPaymentRule().equals(PAYMENTRULE_CreditCard))
551                     payment.setTenderType(MPayment.TENDERTYPE_CreditCard);
552                 else if (check.getPaymentRule().equals(PAYMENTRULE_TransferACH))
553                     payment.setBankACH(check.getC_BankAccount_ID(), false);
554                 else
555                 {
556                     s_log.error("confirmPrint - Unsupported Payment Rule=" + check.getPaymentRule());
557                     continue;
558                 }
559                 payment.setTrxType(MPayment.TRXTYPE_CreditPayment);
560                 payment.setAmount(check.getC_Currency_ID(), check.getPayAmt());
561                 payment.setDateTrx(check.getPayDate());
562                 payment.setDocumentNo();
563                 payment.setC_BPartner_ID(check.getC_BPartner_ID());
564                 //  Link to Invoice
565                 PaySelectionLine[] psls = check.getPaySelectionLines();
566                 s_log.debug("confirmPrint - " + check + " (#SelectionLines=" + psls.length + ")");
567                 if (check.getQty() == 1 && psls != null && psls.length == 1)
568                 {
569                     PaySelectionLine psl = psls[0];
570                     s_log.debug("confirmPrint - map to Invoice " + psl);
571                     //
572                     payment.setC_Invoice_ID (psl.C_Invoice_ID);
573                     payment.setDiscountAmt (psl.DifferenceAmt);
574                 }
575                 else
576                     payment.setDiscountAmt(Env.ZERO);
577                 payment.setWriteOffAmt(Env.ZERO);
578                 payment.save();
579                 //
580                 int C_Payment_ID = payment.getID();
581                 if (C_Payment_ID < 1)
582                     s_log.error("confirmPrint - Payment not created=" + check);
583                 else
584                 {
585                     check.setC_Payment_ID (C_Payment_ID);
586                     check.setIsPrinted(true);
587                     check.save ();
588                     payment.post();
589                 }
590             }   //  new Payment
591 
592             //  Get Check Document No
593             try
594             {
595                 int no = Integer.parseInt(check.getDocumentNo());
596                 if (lastDocumentNo < no)
597                     lastDocumentNo = no;
598             }
599             catch (NumberFormatException   ex)
600             {
601                 s_log.error("confirmPrint - DocumentNo=" + check.getDocumentNo(), ex);
602             }
603         }   //  all checks
604 
605         s_log.debug("confirmPrint - Last Document No = " + lastDocumentNo);
606         return lastDocumentNo;
607     }   //  confirmPrint
608 
609 }   //  MPaySelectionCheck
610
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags