matcher.java

Java写的ERP系统

package org.compiere.acct;

import java.util.*;
import java.sql.*;
import java.math.*;

import org.compiere.util.*;
import org.apache.log4j.Logger;

/**
 *	Automatic Matching
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: Matcher.java,v 1.3 2003/03/19 06:47:32 jjanke Exp $
 */
public class Matcher
{
	/**
	 * 	Constructor
	 *  @param AD_Client_ID Client
	 */
	public Matcher (int AD_Client_ID)
	{
		m_AD_Client_ID = AD_Client_ID;
	}	//	Matcher

	private int					m_AD_Client_ID;
	/**	Logger							*/
	protected Logger			log = Logger.getLogger (getClass());

	/**
	 * 	Matching
	 *  <pre>
	 *  Derive Invoice-Receipt Match from PO-Invoice and PO-Receipt
	 * 	Purchase Order (20)
	 *  - Invoice1 (10)
	 *  - Invoice2 (10)
	 *  - Receipt1 (5)
	 *  - Receipt2 (15)
	 *
	 * 	(a) Creates Directs
	 * 		- Invoice1 - Receipt1 (5)
	 * 		- Invoice2 - Receipt2 (10)
	 *
	 *  (b) Creates Indirects
	 * 		- Invoice1 - Receipt2 (5)
	 *  (Not imlemented)
	 *
	 *
	 *  </pre>
	 *  @return number of records created
	 */
	public int match()
	{
		int counter = 0;
		//	(a) Direct Matches
		String sql = "SELECT m1.AD_Client_ID,m2.AD_Org_ID, "			//	1..2
			+ "m1.C_InvoiceLine_ID,m2.M_InOutLine_ID,m1.M_Product_ID, "	// 	3..5
			+ "m1.DateTrx,m2.DateTrx, m1.Qty, m2.Qty "					//	6..9
			+ "FROM M_MatchPO m1, M_MatchPO m2 "
			+ "WHERE m1.C_OrderLine_ID=m2.C_OrderLine_ID"
			+ " AND m1.M_InOutLine_ID IS NULL"
			+ " AND m2.C_InvoiceLine_ID IS NULL"
			+ " AND m1.M_Product_ID=m2.M_Product_ID"
			+ " AND m1.AD_Client_ID=?"						//	#1
		//	Not existing Inv Matches
			+ "	AND NOT EXISTS (SELECT * FROM M_MatchInv mi "
			+ "WHERE mi.C_InvoiceLine_ID=m1.C_InvoiceLine_ID AND mi.M_InOutLine_ID=m2.M_InOutLine_ID)";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, m_AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				BigDecimal qty1 = rs.getBigDecimal(8);
				BigDecimal qty2 = rs.getBigDecimal(9);
				BigDecimal Qty = qty1.min(qty2);
				if  (Qty.equals(Env.ZERO))
					continue;
				Timestamp dateTrx1 = rs.getTimestamp(6);
				Timestamp dateTrx2 = rs.getTimestamp(7);
				Timestamp DateTrx = dateTrx1;
				if (dateTrx1.before(dateTrx2))
					DateTrx = dateTrx2;
				//
				int AD_Client_ID = rs.getInt(1);
				int AD_Org_ID = rs.getInt(2);
				int C_InvoiceLine_ID = rs.getInt(3);
				int M_InOutLine_ID = rs.getInt(4);
				int M_Product_ID = rs.getInt(5);
				//
				if (createMatchInv(AD_Client_ID, AD_Org_ID,
					M_InOutLine_ID, C_InvoiceLine_ID,
					M_Product_ID, DateTrx, Qty))
					counter++;
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error("match", e);
		}
		log.debug("Matcher.match - Client_ID=" + m_AD_Client_ID
			+ ", Records created=" + counter);
		return counter;
	}	//	match

	/**
	 * 	Create MatchInv record
	 *  @param AD_Client_ID Client
	 *  @param AD_Org_ID Org
	 *  @param M_InOutLine_ID Receipt
	 *  @param C_InvoiceLine_ID Invoice
	 *  @param M_Product_ID Product
	 *  @param DateTrx Date
	 *  @param Qty Qty
	 *  @return true if record created
	 */
	private boolean createMatchInv (int AD_Client_ID, int AD_Org_ID,
		int M_InOutLine_ID, int C_InvoiceLine_ID,
		int M_Product_ID, Timestamp DateTrx, BigDecimal Qty)
	{
		log.debug("createMatchInv - InvLine=" + C_InvoiceLine_ID + ",Rec=" + M_InOutLine_ID + ", Qty=" + Qty + ", " + DateTrx);
		String CompiereSys = Ini.getProperty(Ini.P_COMPIERESYS);
		int M_MatchInv_ID = DB.getKeyNextNo (AD_Client_ID, CompiereSys, "M_MatchInv");
		//
		StringBuffer sql = new StringBuffer("INSERT INTO M_MatchInv ("
			+ "M_MatchInv_ID, "
			+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
			+ "M_InOutLine_ID,C_InvoiceLine_ID, "
			+ "M_Product_ID,DateTrx,Qty, "
			+ "Processing,Processed,Posted) VALUES (")
			.append(M_MatchInv_ID).append(", ")
			.append(AD_Client_ID).append(",").append(AD_Org_ID).append(",'Y',SysDate,0,SysDate,0, ")
			.append(M_InOutLine_ID).append(",").append(C_InvoiceLine_ID).append(", ")
			.append(M_Product_ID).append(",").append(DB.TO_DATE(DateTrx,true)).append(",").append(Qty)
			.append(", 'N','Y','N')");
		int no = DB.executeUpdate(sql.toString());
		return no == 1;
	}	//	createMatchInv
}	//	Matcher