/**
* Parent Cosntructor
* @param cashbook parent
*/
public MCashbookLine(MCashBook cashbook)
: this(cashbook.GetCtx(), 0, cashbook.Get_TrxName())
{
SetClientOrg(cashbook);
SetC_CashBook_ID(cashbook.GetC_CashBook_ID());
_parent = cashbook;
}
/**
* Get Cash Journal for currency, org and date
* @param ctx context
* @param C_Currency_ID currency
* @param AD_Org_ID org
* @param dateAcct date
* @param trxName transaction
* @return cash
*/
public static MCash Get(Ctx ctx, int AD_Org_ID, DateTime?dateAcct, int C_Currency_ID, Trx trxName)
{
MCash retValue = null;
// Existing Journal
String sql = "SELECT * FROM C_Cash c "
+ "WHERE c.AD_Org_ID=" + AD_Org_ID // #1
+ " AND TRUNC(c.StatementDate, 'DD')=@sdate" // #2
+ " AND c.Processed='N'"
+ " AND EXISTS (SELECT * FROM C_CashBook cb "
+ "WHERE c.C_CashBook_ID=cb.C_CashBook_ID AND cb.AD_Org_ID=c.AD_Org_ID"
+ " AND cb.C_Currency_ID=" + C_Currency_ID + ")"; // #3
DataTable dt = null;
SqlParameter[] param = null;
IDataReader idr = null;
try
{
param = new SqlParameter[1];
param[0] = new SqlParameter("@sdate", TimeUtil.GetDay(dateAcct));
idr = DB.ExecuteReader(sql, param, trxName);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MCash(ctx, dr, trxName);
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally { dt = null; }
if (retValue != null)
{
return(retValue);
}
// Get CashBook
MCashBook cb = MCashBook.Get(ctx, AD_Org_ID, C_Currency_ID);
if (cb == null)
{
_log.Warning("No CashBook for AD_Org_ID=" + AD_Org_ID + ", C_Currency_ID=" + C_Currency_ID);
return(null);
}
// Create New Journal
retValue = new MCash(cb, dateAcct);
retValue.Save(trxName);
return(retValue);
}
/**
* Get Cash Book
* @return cash book
*/
public MCashBook GetCashBook()
{
if (_book == null)
{
_book = MCashBook.Get(GetCtx(), GetC_CashBook_ID());
}
return(_book);
}
/**
* Parent Cosntructor
* @param cash parent
*/
public MCashLine(MCash cash)
: this(cash.GetCtx(), 0, cash.Get_TrxName())
{
SetClientOrg(cash);
SetC_Cash_ID(cash.GetC_Cash_ID());
_parent = cash;
_cashBook = _parent.GetCashBook();
}
/**
* Get CashBook
* @return cash book
*/
public MCashBook GetCashBook()
{
if (_cashBook == null)
{
_cashBook = MCashBook.Get(GetCtx(), GetParent().GetC_CashBook_ID());
}
return(_cashBook);
}
private bool UpdateCompletedBalance()
{
MCashBook cashbook = new MCashBook(GetCtx(), GetC_CashBook_ID(), Get_TrxName());
cashbook.SetCompletedBalance(Decimal.Add(cashbook.GetCompletedBalance(), GetStatementDifference()));
cashbook.SetRunningBalance(Decimal.Subtract(cashbook.GetRunningBalance(), GetStatementDifference()));
if (!cashbook.Save())
{
return(false);
}
return(true);
}
/**
* Get CashBook for Org and Currency
* @param ctx context
* @param AD_Org_ID org
* @param C_Currency_ID currency
* @return cash book or null
*/
public static MCashBook Get(Ctx ctx, int AD_Org_ID, int C_Currency_ID)
{
// Try from cache
//Iterator it = _cache.values().iterator();
IEnumerator it = _cache.Values.GetEnumerator();
while (it.MoveNext())
{
MCashBook cb = (MCashBook)it.Current;
if (cb.GetAD_Org_ID() == AD_Org_ID && cb.GetC_Currency_ID() == C_Currency_ID)
{
return(cb);
}
}
// Get from DB
//SI_0648_1 : if there are multiple "defaults / no default" with in same org and currency, system will create object of highest ID.
MCashBook retValue = null;
String sql = "SELECT * FROM C_CashBook "
+ " WHERE AD_Org_ID=" + AD_Org_ID + " AND C_Currency_ID=" + C_Currency_ID
+ " ORDER BY IsDefault DESC, C_CashBook_id DESC";
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, null);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MCashBook(ctx, dr, null);
int key = retValue.GetC_CashBook_ID();
_cache.Add(key, retValue);
break;
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, "get", e);
}
finally { dt = null; }
return(retValue);
}
/**
* Parent Constructor
* @param cb cash book
* @param today date - if null today
*/
public MCash(MCashBook cb, DateTime?today)
: this(cb.GetCtx(), 0, cb.Get_TrxName())
{
SetClientOrg(cb);
SetC_CashBook_ID(cb.GetC_CashBook_ID());
if (today != null)
{
SetStatementDate(today);
SetDateAcct(today);
//String name = DisplayType.getDateFormat(DisplayType.Date).format(today) + " " + cb.GetName();
String name = String.Format("{0:d}", today) + " " + cb.GetName();
SetName(name);
}
_book = cb;
}
/**
* Get MCashBook from Cache
* @param ctx context
* @param C_CashBook_ID id
* @return MCashBook
*/
public static MCashBook Get(Ctx ctx, int C_CashBook_ID)
{
int key = C_CashBook_ID;
MCashBook retValue = (MCashBook)_cache[key];
if (retValue != null)
{
return(retValue);
}
retValue = new MCashBook(ctx, C_CashBook_ID, null);
if (retValue.Get_ID() != 0)
{
_cache.Add(key, retValue);
}
return(retValue);
}
//Amit 10-9-2014 - Correspity Work
public static MCash Get(Ctx ctx, int AD_Org_ID, DateTime?dateAcct, int C_Currency_ID, int C_CashBook_ID, int C_DocType_ID, Trx trxName)
{
MCash retValue = null;
// Existing Journal
String sql = "SELECT * FROM C_Cash c "
+ "WHERE c.AD_Org_ID=" + AD_Org_ID // #1
+ " AND TRUNC(c.StatementDate, 'DD')=@sdate" // #2
+ " AND c.Processed='N'"
+ " AND EXISTS (SELECT * FROM C_CashBook cb "
+ "WHERE c.C_CashBook_ID=" + C_CashBook_ID + " AND cb.AD_Org_ID=c.AD_Org_ID)"; // #3
DataTable dt = null;
SqlParameter[] param = null;
IDataReader idr = null;
try
{
param = new SqlParameter[1];
param[0] = new SqlParameter("@sdate", TimeUtil.GetDay(dateAcct));
idr = DB.ExecuteReader(sql, param, trxName);
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MCash(ctx, dr, trxName);
}
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
finally { dt = null; }
if (retValue != null)
{
return(retValue);
}
// Get CashBook
//MCashBook cb = MCashBook.Get(ctx, AD_Org_ID, C_Currency_ID);
//if (cb == null)
//{
// _log.Warning("No CashBook for AD_Org_ID=" + AD_Org_ID + ", C_Currency_ID=" + C_Currency_ID);
// return null;
//}
MCashBook cb = new MCashBook(ctx, C_CashBook_ID, trxName);
if (cb == null)
{
_log.Warning("No CashBook");
return(null);
}
//Get Closing Balance of Last Record of that cashbook
//sql = @"SELECT EndingBalance FROM C_Cash WHERE IsActive = 'Y' AND C_CashBook_ID = "+ C_CashBook_ID
// + " AND c_cash_id = (SELECT MAX(C_Cash_ID) FROM C_Cash WHERE IsActive = 'Y' AND C_CashBook_ID = " + C_CashBook_ID+ ")";
sql = @"SELECT SUM(completedbalance + runningbalance)AS BegningBalance FROM c_cashbook WHERE IsActive = 'Y' AND c_cashbook_id =" + C_CashBook_ID;
decimal beginingBalance = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, trxName));
// Create New Journal
retValue = new MCash(cb, dateAcct);
retValue.SetC_DocType_ID(C_DocType_ID);
retValue.SetBeginningBalance(beginingBalance);
retValue.Save(trxName);
return(retValue);
}
private bool UpdateCbAndLine()
{
// Update Cash Journal
if (!UpdateHeader())
{
log.Warning("Cannot update cash journal.");
return(false);
}
// Update Cashbook and CashbookLine
MCash parent = GetParent();
MCashBook cashbook = new MCashBook(GetCtx(), parent.GetC_CashBook_ID(), Get_TrxName());
if (cashbook.GetCompletedBalance() == 0)
{
cashbook.SetCompletedBalance(parent.GetBeginningBalance());
}
cashbook.SetRunningBalance(Decimal.Add(Decimal.Subtract(cashbook.GetRunningBalance(), old_ebAmt), new_ebAmt));
//if (cashbook.GetRunningBalance() == 0)
//{
// cashbook.SetRunningBalance
// (Decimal.Add(Decimal.Add(Decimal.Subtract(cashbook.GetRunningBalance(), old_ebAmt), new_ebAmt),cashbook.GetCompletedBalance()));
//}
//else
//{
// cashbook.SetRunningBalance(Decimal.Add(Decimal.Subtract(cashbook.GetRunningBalance(), old_ebAmt), new_ebAmt));
//}
if (!cashbook.Save())
{
log.Warning("Cannot update running balance.");
return(false);
}
DataTable dtCashbookLine;
int C_CASHBOOKLINE_ID = 0;
string sql = "SELECT C_CASHBOOKLINE_ID FROM C_CASHBOOKLINE WHERE C_CASHBOOK_ID="
+ cashbook.GetC_CashBook_ID() + " AND DATEACCT="
+ DB.TO_DATE(parent.GetDateAcct()) + " AND AD_ORG_ID=" + GetAD_Org_ID();
dtCashbookLine = DB.ExecuteDataset(sql, null, null).Tables[0];
if (dtCashbookLine.Rows.Count > 0)
{
C_CASHBOOKLINE_ID = Util.GetValueOfInt(dtCashbookLine.Rows[0]
.ItemArray[0]);
}
MCashbookLine cashbookLine = new MCashbookLine(GetCtx(), C_CASHBOOKLINE_ID, Get_TrxName());
if (C_CASHBOOKLINE_ID == 0)
{
cashbookLine.SetC_CashBook_ID(cashbook.GetC_CashBook_ID());
// SI_0419 : Update org/client as on cash line
cashbookLine.SetAD_Org_ID(GetAD_Org_ID());
cashbookLine.SetAD_Client_ID(GetAD_Client_ID());
cashbookLine.SetEndingBalance
(Decimal.Add(Decimal.Add(Decimal.Subtract(cashbookLine.GetEndingBalance(), old_ebAmt), new_ebAmt), cashbook.GetCompletedBalance()));
}
else
{
cashbookLine.SetEndingBalance(Decimal.Add(Decimal.Subtract(cashbookLine.GetEndingBalance(), old_ebAmt), new_ebAmt));
}
cashbookLine.SetDateAcct(parent.GetDateAcct());
cashbookLine.SetStatementDifference(Decimal.Add(Decimal.Subtract(cashbookLine.GetStatementDifference(), old_sdAmt), new_sdAmt));
if (!cashbookLine.Save())
{
log.Warning("Cannot create/update cashbook line.");
return(false);
}
return(true);
}
