expenseapinvoice.java

Java写的ERP系统

/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is                  Compiere  ERP & CRM  Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2003 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.process;

import java.sql.*;
import java.math.*;
import java.net.*;

import org.compiere.model.*;
import org.compiere.util.*;

/**
 *	Create AP Invoices from Expense Reports
 *
 * 	@author 	Jorg Janke
 * 	@version 	$Id: ExpenseAPInvoice.java,v 1.5 2003/02/22 04:25:17 jjanke Exp $
 */
public class ExpenseAPInvoice extends SvrProcess
{
	/**
	 *	Constructor
	 */
	public ExpenseAPInvoice()
	{
		super();
		Log.trace(Log.l1_User, "ExpenseAPInvoice");
	}	//	ExpenseAPInvoice

	private int			m_C_BPartner_ID = 0;
	private Timestamp	m_DateFrom = null;
	private Timestamp	m_DateTo = null;
	private int			m_noInvoices = 0;

	/**
	 *  Prepare - e.g., get Parameters.
	 */
	protected void prepare()
	{
		Parameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].ParameterName;
			if (para[i].Parameter == null)
				;
			else if (name.equals("C_BPartner_ID"))
				m_C_BPartner_ID = ((BigDecimal)para[i].Parameter).intValue();
			else if (name.equals("DateReport"))
			{
				m_DateFrom = (Timestamp)para[i].Parameter;
				m_DateTo = (Timestamp)para[i].Parameter_To;
			}
			else
				Log.error("ExpenseAPInvoice.prepare - Unknown Parameter: " + name);
		}
	}	//	prepare


	/**
	 *  Perform process.
	 *  @return Message to be translated
	 *  @throws Exception
	 */
	protected String doIt() throws java.lang.Exception
	{
		StringBuffer sql = new StringBuffer ("SELECT e.AD_Client_ID, e.AD_Org_ID,"
			+ "e.C_BPartner_ID, e.M_Warehouse_ID,"
			+ "el.C_Project_ID, el.C_Activity_ID, el.C_Campaign_ID,"
			+ "el.M_Product_ID, el.S_ResourceAssignment_ID, el.C_UOM_ID,"
			+ "el.Qty, el.Description, el.Note,"
			+ "el.ExpenseAmt, el.C_Currency_ID, el.ConvertedAmt, "
			+ "e.DocumentNo, el.S_TimeExpenseLine_ID "
			+ "FROM S_TimeExpense e"
			+ " INNER JOIN S_TimeExpenseLine el ON (e.S_TimeExpense_ID=el.S_TimeExpense_ID) "
			+ "WHERE el.C_InvoiceLine_ID IS NULL"
			+ " AND el.ConvertedAmt<>0 AND e.Processed='Y'");
		if (m_C_BPartner_ID != 0)
			sql.append(" AND e.C_BPartner_ID=?");				//	#1
		if (m_DateFrom != null)
			sql.append(" AND e.DateReport >= ?");				//	#2
		if (m_DateTo != null)
			sql.append(" AND e.DateReport <= ?");				//	#3
		sql.append(" ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID, el.Line");
		//
		int old_BPartner_ID = -1;
		MInvoice invoice = null;
		//
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql.toString ());
			int par = 1;
			if (m_C_BPartner_ID != 0)
				pstmt.setInt (par++, m_C_BPartner_ID);
			if (m_DateFrom != null)
				pstmt.setTimestamp (par++, m_DateFrom);
			if (m_DateTo != null)
				pstmt.setTimestamp (par++, m_DateTo);
			ResultSet rs = pstmt.executeQuery ();
			while (rs.next())				//	********* Expense Line Loop
			{
				//	e.AD_Client_ID, e.AD_Org_ID,							//	1..2
				int AD_Client_ID = rs.getInt(1);
				int AS_org_ID = rs.getInt(2);
				//	el.C_BPartner_ID, e.M_Warehouse_ID,						//	3..4
				int C_BPartner_ID = rs.getInt(3);
				int M_Warehouse_ID = rs.getInt(4);
				//	el.C_Project_ID, el.C_Activity_ID, el.C_Campaign_ID,	//	5..7
				int C_Project_ID = rs.getInt(5);
				int C_Activity_ID = rs.getInt(6);
				int C_Campaign_ID = rs.getInt(7);
				//	el.M_Product_ID, el.S_ResourceAssignment_ID, el.C_UOM_ID,	// 	8..10
				int M_Product_ID = rs.getInt(8);
				int S_ResourceAssignment_ID = rs.getInt(9);
				int C_UOM_ID = rs.getInt(10);
				//	el.Qty, el.Description, el.Note,						//	11..13
				BigDecimal Qty = rs.getBigDecimal(11);
				String Description = rs.getString(12);
				String Note = rs.getString(13);
				//	el.ExpenseAmt, el.C_Currency_ID, el.ConvertedAmt 		//	14..16
				BigDecimal Cost = rs.getBigDecimal(16);
				if (Cost == null || Cost.compareTo(Env.ZERO) == 0)
					continue;
				//	e.DocumentNo, el.S_TimeExpenseLine_ID					//	17..18
				String DocumentNo = rs.getString(17);
				int S_TimeExpenseLine_ID = rs.getInt(18);


				//	New BPartner - New Order
				if (C_BPartner_ID != old_BPartner_ID)
				{
					completeInvoice (invoice);
					MBPartner bp = new MBPartner (getCtx(), C_BPartner_ID);
					//
					Log.trace(Log.l4_Data, "ExpenseAPInvoice.doIt - New Invoice for", bp);
					invoice = new MInvoice (getCtx(), 0);
					invoice.setC_DocTypeTarget_ID(MInvoice.DocBaseType_API);
					invoice.setDocumentNo (DocumentNo);
					//
					invoice.setBPartner(bp);
					if (C_Activity_ID != 0)
						invoice.setC_Activity_ID(C_Activity_ID);
					if (C_Campaign_ID != 0)
						invoice.setC_Campaign_ID(C_Campaign_ID);
					if (C_Project_ID != 0)
						invoice.setC_Project_ID(C_Project_ID);
					//
					if (!invoice.save())
					{
						Log.error("ExpenseAPInvoice.doIt - cannot save Invoice");
						invoice = null;
						break;
					}
					old_BPartner_ID = C_BPartner_ID;
				}
				//	OrderLine
				MInvoiceLine il = new MInvoiceLine (getCtx(), 0, invoice.getC_Invoice_ID());
				il.setInvoice (invoice);
				//
				if (M_Product_ID != 0)
					il.setM_Product_ID(M_Product_ID);
				il.setQtyInvoiced(Qty);
				il.setDescription(Description);
				//
				if (Cost != null && Cost.compareTo(Env.ZERO) != 0)
					il.setPriceActual(Cost);
				else
					il.setPrice();
				if (C_UOM_ID != 0 && il.getC_UOM_ID() == 0)
					il.setC_UOM_ID(C_UOM_ID);
				il.setTax();
				if (!il.save())
				{
					Log.error ("ExpenseAPInvoice.doIt - cannot save InvoiceLine");
					invoice = null;
					break;
				}
				//	Update TimeExpense Line
				sql = new StringBuffer ("UPDATE S_TimeExpenseLine SET C_InvoiceLine_ID=")
					.append(il.getID())
					.append(" WHERE S_TimeExpenseLine_ID=").append(S_TimeExpenseLine_ID);
				int no = DB.executeUpdate(sql.toString());
				if (no == 1)
					Log.trace(Log.l3_Util, "ExpenseSOrder.doIt Updated",
						"S_TimeExpenseLine_ID=" + S_TimeExpenseLine_ID
						+ " with C_InvoiceLine_ID=" + il.getID());
				else
					Log.error("ExpenseSOrder.doIt Not Updated #" + no
						+ " - S_TimeExpenseLine_ID=" + S_TimeExpenseLine_ID
						+ " with C_InvoiceLine_ID=" + il.getID());
			}								//	********* Expense Line Loop
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			Log.error ("ExpenseAPInvoice.doIt", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		completeInvoice (invoice);
		return "@Created@=" + m_noInvoices;
	}	//	doIt

	/**
	 * 	Complete Invoice
	 *	@param invoice invoice
	 */
	private void completeInvoice (MInvoice invoice)
	{
		if (invoice == null)
			return;
		m_noInvoices++;
		addLog(invoice.getDateInvoiced(), invoice.getID(), invoice.getGrandTotal(), invoice.getDocumentNo());
	}	//	completeInvoice

}	//	ExpenseAPInvoice