www.pudn.com > code_source_compiere_erp_crm_logiciel_java.zip > MCashBook.java
/******************************************************************************
* 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.
*
* 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
*
* @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