mcashbook.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-2001 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.model;

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

import org.compiere.util.*;

/**
 *  CashBook Model.
 *  <pre>
 *  Event chain:
 *  - C_CashLine is inserted
 *      C_Cash_Post
 *          create allocation line
 *              C_Allocation_Trg fires
 *                  Update C_BPartner Open Item Amount
 *          update invoice (IsPaid)
 *
 *  LifeLine:
 *  - When Invoice is posted
 *      - Default CashBook entry is generated and link invoice-cashline created
 *
 *  - When an invoice is reversed, a new (reversing) CashBook entry is created
 *
 *  - When a CashBook entry is changed (date, ..)
 *      - the old entry is reversed
 *      - the new entry is created
 *  </pre>
 *  @see org.compiere.apps.VPayment#save
 *  @see "C_Order_Post.sql, C_Cash_Post.sql"
 *
 *  @author 	Jorg Janke
 *  @version 	$Id: MCashBook.java,v 1.10 2002/10/08 04:28:40 jjanke Exp $
 */
public final class MCashBook implements Serializable
{
	/**
	 *  Load CashBook Line
	 *  @param CashLine_ID cash line
	 */
	public MCashBook (int CashLine_ID)
	{
		Log.trace(Log.l3_Util, "MCashBook - " + CashLine_ID);
		C_CashLine_ID = CashLine_ID;
		String sql = "SELECT c.C_CashBook_ID,c.C_Cash_ID,c.StatementDate,c.Processed, "
			+ "cl.Line,cl.Amount, cl.AD_Client_ID,cl.AD_Org_ID,cl.Description,cl.C_Invoice_ID "
			+ "FROM C_Cash c, C_CashLine cl "
			+ "WHERE c.C_Cash_ID=cl.C_Cash_ID AND cl.C_CashLine_ID=?";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, C_CashLine_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				C_CashBook_ID = rs.getInt(1);
				C_Cash_ID = rs.getInt(2);
				StatementDate = rs.getTimestamp(3);
				Processed = rs.getString(4).equals("Y");
				Line = rs.getInt(5);
				Amount = rs.getBigDecimal(6);
				AD_Client_ID = rs.getInt(7);
				AD_Org_ID = rs.getInt(8);
				Description = rs.getString(9);
				if (Description == null)
					Description = "";
				C_Invoice_ID = rs.getInt(10);
			}
			else
				C_CashLine_ID = 0;
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error ("MCashBook.Constructor", e);
			C_CashLine_ID = 0;
		}
	}   //  MCashBook

	private int         C_CashLine_ID = 0;
	private int         C_Cash_ID = 0;
	private int         Line = 0;
	private String      Description = "";
	private BigDecimal  Amount = new BigDecimal(0.0);
	private int         C_CashBook_ID = 0;
	private int         AD_Client_ID = 0;
	private int         AD_Org_ID = 0;
	private int         C_Invoice_ID = 0;
	private boolean     Processed = false;
	private Timestamp   StatementDate = null;

	/*************************************************************************/

	/**
	 *  Create CashBook Entry Line.
	 *
	 *  a) Find CashBook for Trx - based on Org_ID
	 *
	 *  @see "C_Post_Invoice.sql"
	 *  @param ctx context
	 *  @param WindowNo window no
	 *  @param AD_Client_ID client id
	 *  @param AD_Org_ID org id
	 *  @param StatementDate statement date
	 *  @param C_Invoice_ID invoice id
	 *  @param Amount amount
	 *  @param Description description
	 *  @return C_CashLine_ID or 0 if failure
	 */
	public static int createCashBookEntry(Properties ctx, int WindowNo,
		int AD_Client_ID, int AD_Org_ID, Timestamp StatementDate,
		int C_Invoice_ID, BigDecimal Amount, String Description)
	{
		//  a) Find first CashBook for Trx
		int C_CashBook_ID = 0;
		String sql = "SELECT C_CashBook_ID FROM C_CashBook "
			+ "WHERE AD_Org_ID=? ORDER BY Created";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, AD_Org_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				C_CashBook_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error ("MCashBook.createCashBookEntry-1", e);
		}
		if (C_CashBook_ID == 0)
		{
			Log.error("MCashBook.createCashBookEntry - No CashBook for AD_Org_ID=" + AD_Org_ID);
			return 0;
		}

		return createCashBookEntry(ctx, WindowNo, AD_Client_ID, AD_Org_ID,
			StatementDate, C_Invoice_ID, Amount, Description, C_CashBook_ID);
	}   //  createCashBookEntry

	/**
	 *  Create CashBook Entry Line.
	 *
	 *  b) Find/Create Cash Entry
	 *  c) Create Cash Line Entry
	 *
	 *  @param ctx context
	 *  @param WindowNo window no
	 *  @param AD_Client_ID client id
	 *  @param AD_Org_ID org id
	 *  @param StatementDate datament date
	 *  @param C_Invoice_ID invoice id
	 *  @param Amount amount
	 *  @param Description description
	 *  @param C_CashBook_ID cashbook id
	 *  @return C_CashLine_ID or 0 if failure
	 */
	public static int createCashBookEntry (Properties ctx, int WindowNo,
		int AD_Client_ID, int AD_Org_ID, Timestamp StatementDate,
		int C_Invoice_ID, BigDecimal Amount, String Description, int C_CashBook_ID)
	{
		Env.setContext(ctx, WindowNo, "AD_Client_ID", AD_Client_ID);    //  make sure
		int user = Env.getContextAsInt(ctx, "#AD_User_ID");
		StatementDate = TimeUtil.getDay(StatementDate);					//	truncate

		//  b) Find/Create Cash Entry
		int C_Cash_ID = 0;
		//	"WHERE C_CashBook_ID=? AND TRUNC(StatementDate)=TRUNC(?)" results in
		//	ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
		//	C_Cash_Trg now guarantees that StatementDate is truncated.
		String sql = "SELECT C_Cash_ID "
			+ "FROM C_Cash "
			+ "WHERE C_CashBook_ID=? AND StatementDate=?"		//	#1/2
			+ " AND Processed='N'";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, C_CashBook_ID);
			pstmt.setTimestamp (2, StatementDate);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				C_Cash_ID = rs.getInt(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			Log.error ("MCashBook.createCashBookEntry-2\nSQL=" + sql
				+ " - C_CashBook_ID=" + C_CashBook_ID + ", StatementDate=" + StatementDate, e);
		}
		//  Create Cash Entry
		if (C_Cash_ID == 0)
		{
			StringBuffer isql = new StringBuffer("INSERT INTO C_Cash "
				+ "(C_Cash_ID,AD_Client_ID,AD_Org_ID, "
				+ "IsActive,Created,CreatedBy,Updated,UpdatedBy, "
				+ "C_CashBook_ID, Name,StatementDate,DateAcct,"
				+ "BeginningBalance,EndingBalance,StatementDifference, "
				+ "Processing,Processed,Posted) VALUES (");
			//
			C_Cash_ID = DB.getKeyNextNo(ctx, WindowNo, "C_Cash");
			isql.append(C_Cash_ID).append(",");
			isql.append(AD_Client_ID).append(",").append(AD_Org_ID);
			//
			isql.append(",'Y',SysDate,").append(user).append(",SysDate,").append(user).append(",");
			isql.append(C_CashBook_ID).append(",");
			String date = StatementDate.toString().substring(0,10);
			isql.append("'").append(date).append("',");             //  Name
			date = DB.TO_DATE(StatementDate, true) + ",";
			isql.append(date).append(date);                         //  Statement/Acct Date
			isql.append("0,0,0,'N','N','N')");
			//
			int no = DB.executeUpdate(isql.toString());
			//
			if (no == 1)
				Log.trace(Log.l3_Util, "MCashBook.createCashBookEntry - C_Cash_ID=" + C_Cash_ID);
			else
			{
				Log.error("MCashBook.createCashBookEntry - Not Inserted - C_Cash_ID=" + C_Cash_ID);
				return 0;
			}
		}

		//  c) Create Cash Line Entry
		StringBuffer isql = new StringBuffer("INSERT INTO C_CashLine "
			+ "(C_CashLine_ID,AD_Client_ID,AD_Org_ID, "
			+ "IsActive,Created,CreatedBy,Updated,UpdatedBy, "
			+ "C_Cash_ID,C_Invoice_ID, "
			+ "Line, Description,Amount,CashType, "
			+ "DiscountAmt,WriteOffAmt,IsGenerated"
			+ ") VALUES (");
		//
		int C_CashLine_ID = DB.getKeyNextNo(ctx, WindowNo, "C_CashLine");
		isql.append(C_CashLine_ID).append(",")
			.append(AD_Client_ID).append(",").append(AD_Org_ID)
			.append(",'Y',SysDate,").append(user).append(",SysDate,").append(user).append(", ");
		//	C_Cash_ID,C_Invoice_ID,
		isql.append(C_Cash_ID);
		if (C_Invoice_ID == 0)
			isql.append(",NULL, ");
		else
			isql.append(",").append(C_Invoice_ID).append(", ");
		//	Line, Description,Amount,CashType,
		isql.append("(SELECT NVL(MAX(Line),0)+10 FROM C_CashLine WHERE C_Cash_ID=")
			.append(C_Cash_ID).append("),");
		if (Description == null || Description.length() == 0)
			isql.append("NULL,");
		else
			isql.append("'").append(Description).append("',");
		isql.append(Amount)
			.append(",'I',");
		//	DiscountAmt,WriteOffAmt,IsGenerated
		isql.append("0,0,'Y')");
		int no = DB.executeUpdate(isql.toString());
		//
		if (no == 1)
			Log.trace(Log.l3_Util, "MCashBook.createCashBookEntry - C_CashLine_ID=" + C_CashLine_ID
				+ ", C_Invoice_ID=" + C_Invoice_ID + ", Amount=" + Amount + " - " + Description);
		else
		{
			Log.error("MCashBook.createCashBookEntry - Not Inserted - C_CashLine_ID=" + C_CashLine_ID);
			C_CashLine_ID = 0;
		}
		return C_CashLine_ID;
	}   //  createCashBookEntry

	/*************************************************************************/

	/**
	 *  Update if valid
	 *  @return true if updated
	 */
	public boolean update()
	{
		if (C_CashLine_ID == 0 || Processed)
			return false;
		boolean retValue = true;
		//
		StringBuffer sql = new StringBuffer ("UPDATE C_CashLine SET Description=");
		if (Description == null || Description.length() == 0)
			sql.append("NULL,");
		else
			sql.append("'").append(Description).append("',");
		sql.append("Amount=").append(Amount.toString());
		sql.append(" WHERE C_CashLine_ID=").append(C_CashLine_ID);
		//
		int no = DB.executeUpdate(sql.toString());
		if (no == 1)
			Log.trace(Log.l3_Util, "MCashBook.update - success");
		else
		{
			Log.error("MCashBook.update - Not Updated - C_CashLine_ID=" + C_CashLine_ID);
			retValue = false;
		}
		return retValue;
	}   //  update

	/**
	 *  Reverse Entry
	 *  @param ctx context
	 *  @param WindowNo window no
	 *  @return true if OK
	 */
	public boolean reverse(Properties ctx, int WindowNo)
	{
		//  C_CashLine_ID of reversed entry or 0 if error
		int no = createCashBookEntry (ctx, WindowNo, AD_Client_ID, AD_Org_ID,
			StatementDate, C_Invoice_ID, Amount.negate(),
			Description + " (" + Line + ")", C_CashBook_ID);
		return (no != 0);
	}   //  reverse

	/**
	 *  Delete Entry
	 *  @return true if deleted
	 */
	public boolean delete()
	{
		String sql = "DELETE C_CashLine WHERE C_CashLine_ID=" + C_CashLine_ID;
		int no = DB.executeUpdate(sql);
		return (no == 1);
	}   //  delete

	/**************************************************************************/

	/**
	 *  CashLine
	 *  @return cash line id
	 */
	public int getC_CashLine_ID()
	{
		return C_CashLine_ID;
	}

	/**
	 *  Cash
	 *  @return cash id
	 */
	public int getC_Cash_ID()
	{
		return C_Cash_ID;
	}

	/**
	 *  Line No
	 *  @return line
	 */
	public int getLine()
	{
		return Line;
	}

	/**
	 *  Description
	 *  @param newDescription description
	 */
	public void setDescription(String newDescription)
	{
		Description = newDescription;
	}
	public String getDescription()
	{
		return Description;
	}

	/**
	 *  Amount
	 *  @param newAmount amount
	 */
	public void setAmount (BigDecimal newAmount)
	{
		Amount = newAmount;
	}
	public java.math.BigDecimal getAmount()
	{
		return Amount;
	}

	/**
	 *  CashBook
	 *  @return cash book id
	 */
	public int getC_CashBook_ID()
	{
		return C_CashBook_ID;
	}

	/**
	 *  Processed
	 *  @return true if processed
	 */
	public boolean isProcessed()
	{
		return Processed;
	}

	/**
	 *  StatementDate
	 *  @return datement date
	 */
	public Timestamp getStatementDate()
	{
		return StatementDate;
	}

	/**
	 *  Is Same Date
	 *  @param compare date to compare
	 *  @return true if same date as statement date
	 */
	public boolean isSameDate (Timestamp compare)
	{
		if (compare == null)
			return false;
		//  JDBC date format YYYY-MM-DD
		String stmt = StatementDate.toString().substring(0,10);
		String cmp = compare.toString().substring(0,10);
		//
		Log.trace(Log.l5_DData, "MCashBook.isSameDate - " + stmt + " " + cmp);
		return stmt.compareTo(cmp) == 0;
	}   //  isSameDate

}   //  MCashBook