KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > acct > Matcher


1 package org.compiere.acct;
2
3 import java.util.*;
4 import java.sql.*;
5 import java.math.*;
6
7 import org.compiere.util.*;
8 import org.apache.log4j.Logger;
9
10 /**
11  * Automatic Matching
12  *
13  * @author Jorg Janke
14  * @version $Id: Matcher.java,v 1.4 2003/11/06 07:10:40 jjanke Exp $
15  */
16 public class Matcher
17 {
18     /**
19      * Constructor
20      * @param AD_Client_ID Client
21      */
22     public Matcher (int AD_Client_ID)
23     {
24         m_AD_Client_ID = AD_Client_ID;
25     } // Matcher
26
27     private int m_AD_Client_ID;
28     /** Logger */
29     protected Logger log = Logger.getLogger (getClass());
30
31     /**
32      * Matching
33      * <pre>
34      * Derive Invoice-Receipt Match from PO-Invoice and PO-Receipt
35      * Purchase Order (20)
36      * - Invoice1 (10)
37      * - Invoice2 (10)
38      * - Receipt1 (5)
39      * - Receipt2 (15)
40      *
41      * (a) Creates Directs
42      * - Invoice1 - Receipt1 (5)
43      * - Invoice2 - Receipt2 (10)
44      *
45      * (b) Creates Indirects
46      * - Invoice1 - Receipt2 (5)
47      * (Not imlemented)
48      *
49      *
50      * </pre>
51      * @return number of records created
52      */
53     public int match()
54     {
55         int counter = 0;
56         // (a) Direct Matches
57 String JavaDoc sql = "SELECT m1.AD_Client_ID,m2.AD_Org_ID, " // 1..2
58 + "m1.C_InvoiceLine_ID,m2.M_InOutLine_ID,m1.M_Product_ID, " // 3..5
59 + "m1.DateTrx,m2.DateTrx, m1.Qty, m2.Qty " // 6..9
60 + "FROM M_MatchPO m1, M_MatchPO m2 "
61             + "WHERE m1.C_OrderLine_ID=m2.C_OrderLine_ID"
62             + " AND m1.M_InOutLine_ID IS NULL"
63             + " AND m2.C_InvoiceLine_ID IS NULL"
64             + " AND m1.M_Product_ID=m2.M_Product_ID"
65             + " AND m1.AD_Client_ID=?" // #1
66 // Not existing Inv Matches
67 + " AND NOT EXISTS (SELECT * FROM M_MatchInv mi "
68             + "WHERE mi.C_InvoiceLine_ID=m1.C_InvoiceLine_ID AND mi.M_InOutLine_ID=m2.M_InOutLine_ID)";
69         try
70         {
71             PreparedStatement pstmt = DB.prepareStatement(sql);
72             pstmt.setInt(1, m_AD_Client_ID);
73             ResultSet rs = pstmt.executeQuery();
74             while (rs.next())
75             {
76                 BigDecimal qty1 = rs.getBigDecimal(8);
77                 BigDecimal qty2 = rs.getBigDecimal(9);
78                 BigDecimal Qty = qty1.min(qty2);
79                 if (Qty.equals(Env.ZERO))
80                     continue;
81                 Timestamp dateTrx1 = rs.getTimestamp(6);
82                 Timestamp dateTrx2 = rs.getTimestamp(7);
83                 Timestamp DateTrx = dateTrx1;
84                 if (dateTrx1.before(dateTrx2))
85                     DateTrx = dateTrx2;
86                 //
87 int AD_Client_ID = rs.getInt(1);
88                 int AD_Org_ID = rs.getInt(2);
89                 int C_InvoiceLine_ID = rs.getInt(3);
90                 int M_InOutLine_ID = rs.getInt(4);
91                 int M_Product_ID = rs.getInt(5);
92                 //
93 if (createMatchInv(AD_Client_ID, AD_Org_ID,
94                     M_InOutLine_ID, C_InvoiceLine_ID,
95                     M_Product_ID, DateTrx, Qty))
96                     counter++;
97             }
98             rs.close();
99             pstmt.close();
100         }
101         catch (SQLException e)
102         {
103             log.error("match", e);
104         }
105         log.debug("Matcher.match - Client_ID=" + m_AD_Client_ID
106             + ", Records created=" + counter);
107         return counter;
108     } // match
109
110     /**
111      * Create MatchInv record
112      * @param AD_Client_ID Client
113      * @param AD_Org_ID Org
114      * @param M_InOutLine_ID Receipt
115      * @param C_InvoiceLine_ID Invoice
116      * @param M_Product_ID Product
117      * @param DateTrx Date
118      * @param Qty Qty
119      * @return true if record created
120      */
121     private boolean createMatchInv (int AD_Client_ID, int AD_Org_ID,
122         int M_InOutLine_ID, int C_InvoiceLine_ID,
123         int M_Product_ID, Timestamp DateTrx, BigDecimal Qty)
124     {
125         log.debug("createMatchInv - InvLine=" + C_InvoiceLine_ID + ",Rec=" + M_InOutLine_ID + ", Qty=" + Qty + ", " + DateTrx);
126         int M_MatchInv_ID = DB.getKeyNextNo (AD_Client_ID, "M_MatchInv");
127         //
128 StringBuffer JavaDoc sql = new StringBuffer JavaDoc("INSERT INTO M_MatchInv ("
129             + "M_MatchInv_ID, "
130             + "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
131             + "M_InOutLine_ID,C_InvoiceLine_ID, "
132             + "M_Product_ID,DateTrx,Qty, "
133             + "Processing,Processed,Posted) VALUES (")
134             .append(M_MatchInv_ID).append(", ")
135             .append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',SysDate,0,SysDate,0, ")
136             .append(M_InOutLine_ID).append(",").append(C_InvoiceLine_ID).append(", ")
137             .append(M_Product_ID).append(",").append(DB.TO_DATE(DateTrx,true)).append(",").append(Qty)
138             .append(", 'N','Y','N')");
139         int no = DB.executeUpdate(sql.toString());
140         return no == 1;
141     } // createMatchInv
142 } // Matcher
143
Popular Tags