/**
* 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);
}
//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);
}
