public static double GetAccountAmount(AccountType type, GLAccountsInfo accInfo, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
double debit = GetDebitAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
double credit = GetCreditAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
if (type == AccountType.Debit)
{
return(debit - credit);
}
else if (type == AccountType.Credit)
{
return(credit - debit);
}
else
{
if (debit > credit)
{
debit -= credit;
return(debit - credit);
}
else
{
credit -= debit;
return(credit - debit);
}
}
return(0);
}
public static GLAccountsInfo GetAccount(String strAccountNo)
{
GLAccountsInfo account = null;
if (AccountList.TryGetValue(strAccountNo, out account) == false)
{
account = new GLAccountsController().GetObjectByNo(strAccountNo) as GLAccountsInfo;
AccountList.Add(strAccountNo, account);
}
return(account);
}
public static double GetDebitAmount(GLAccountsInfo accInfo, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
double dbResult = 0;
String strQuery = String.Format(@"SELECT SUM(AmtTot) FROM GLJournalEntrys WHERE ApprovalStatus='{0}' AND FK_GLAccountID_Debit={1} ", ABCCommon.ABCConstString.ApprovalTypeApproved, accInfo.GLAccountID);
if (startDate.HasValue)
{
strQuery += String.Format(@" AND {0}", TimeProvider.GenCompareDateTime("JournalDate", ">=", startDate.Value));
}
if (endDate.HasValue)
{
strQuery += String.Format(@" AND {0} ", TimeProvider.GenCompareDateTime("JournalDate", "<=", endDate.Value));
}
if (String.IsNullOrWhiteSpace(strConditionQuery) == false)
{
strQuery += String.Format(@" AND {0} ", strConditionQuery);
}
object objAmt = BusinessObjectController.GetData(strQuery);
if (objAmt != null && objAmt != DBNull.Value)
{
dbResult += Convert.ToDouble(objAmt);
}
if (isIncludeChildren)
{
List <BusinessObject> lstChildren = new GLAccountsController().GetListByForeignKey("FK_GLAccountID", accInfo.GLAccountID);
foreach (GLAccountsInfo accChildInfo in lstChildren)
{
dbResult += GetDebitAmount(accChildInfo, startDate, endDate, strConditionQuery, true);
}
if (lstChildren.Count <= 0)
{
if (startDate.HasValue == false || (startDate.HasValue && startDate.Value <= SystemProvider.AppConfig.StartDate.Value))
{
dbResult += accInfo.DebitBeginBalance;
}
}
}
else
{
if (startDate.HasValue == false || (startDate.HasValue && startDate.Value <= SystemProvider.AppConfig.StartDate.Value))
{
dbResult += accInfo.DebitBeginBalance;
}
}
return(dbResult);
}
public static GLAccountsInfo CalculateAccount(GLAccountsInfo accInfo, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
GLAccountsController accCtrl = new GLAccountsController();
accInfo = accCtrl.GetObjectByID(accInfo.GLAccountID) as GLAccountsInfo;
if (accInfo == null)
{
return(null);
}
#region BeginBalance
if (startDate.HasValue && startDate.Value > SystemProvider.AppConfig.StartDate.Value)
{
accInfo.DebitBeginBalance = GetDebitAmount(accInfo.GLAccountID, null, startDate.Value.AddSeconds(-10), strConditionQuery, isIncludeChildren);
accInfo.CreditBeginBalance = GetCreditAmount(accInfo.GLAccountID, null, startDate.Value.AddSeconds(-10), strConditionQuery, isIncludeChildren);
}
else
{
accInfo.DebitBeginBalance = GetDebitBeginBalance(accInfo);
accInfo.CreditBeginBalance = GetCreditBeginBalance(accInfo);
}
if (accInfo.DebitBeginBalance > accInfo.CreditBeginBalance)
{
accInfo.DebitBeginBalance -= accInfo.CreditBeginBalance;
accInfo.CreditBeginBalance = 0;
}
else
{
accInfo.CreditBeginBalance -= accInfo.DebitBeginBalance;
accInfo.DebitBeginBalance = 0;
}
#endregion
accInfo.CurrentDebit = GetDebitAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
accInfo.CurrentCredit = GetCreditAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
if (accInfo.CurrentDebit > accInfo.CurrentCredit)
{
accInfo.CurrentDebit -= accInfo.CurrentCredit;
accInfo.CurrentCredit = 0;
}
else
{
accInfo.CurrentCredit -= accInfo.CurrentDebit;
accInfo.CurrentDebit = 0;
}
return(accInfo);
}
public static List <GLJournalEntrysInfo> GetCreditEntrys(List <String> lstAccounts, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
List <GLJournalEntrysInfo> lstResults = new List <GLJournalEntrysInfo>();
GLAccountsController accountCtrl = new GLAccountsController();
foreach (String strAccNo in lstAccounts)
{
GLAccountsInfo accInfo = accountCtrl.GetObjectByNo(strAccNo) as GLAccountsInfo;
if (accInfo != null)
{
lstResults.AddRange(GetCreditEntrys(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren));
}
}
return(lstResults);
}
public static Guid GetAccountID(String strAccountNo)
{
GLAccountsInfo account = null;
if (AccountList.TryGetValue(strAccountNo, out account) == false)
{
account = new GLAccountsController().GetObjectByNo(strAccountNo) as GLAccountsInfo;
AccountList.Add(strAccountNo, account);
}
if (account != null)
{
return(account.GLAccountID);
}
return(Guid.Empty);
}
public static double GetJournalAmount(GLAccountsInfo debitAccount, GLAccountsInfo creditAccount, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
double dbResult = 0;
String strQuery = String.Format(@"SELECT SUM(AmtTot) FROM GLJournalEntrys WHERE ApprovalStatus='{0}' AND FK_GLAccountID_Debit='{1}' AND FK_GLAccountID_Credit='{2}' ", ABCCommon.ABCConstString.ApprovalTypeApproved, debitAccount.GLAccountID, creditAccount.GLAccountID);
if (startDate.HasValue)
{
strQuery += String.Format(@" AND {0}", TimeProvider.GenCompareDateTime("JournalDate", ">=", startDate.Value));
}
if (endDate.HasValue)
{
strQuery += String.Format(@" AND {0} ", TimeProvider.GenCompareDateTime("JournalDate", "<=", endDate.Value));
}
if (String.IsNullOrWhiteSpace(strConditionQuery) == false)
{
strQuery += String.Format(@" AND {0} ", strConditionQuery);
}
object objAmt = BusinessObjectController.GetData(strQuery);
if (objAmt != null && objAmt != DBNull.Value)
{
dbResult += Convert.ToDouble(objAmt);
}
if (isIncludeChildren)
{
List <BusinessObject> lstChildren = new GLAccountsController().GetListByForeignKey("FK_GLAccountID", creditAccount.GLAccountID);
foreach (GLAccountsInfo creditChild in lstChildren)
{
dbResult += GetJournalAmount(debitAccount, creditChild, startDate, endDate, strConditionQuery, true);
}
lstChildren = new GLAccountsController().GetListByForeignKey("FK_GLAccountID", debitAccount.GLAccountID);
foreach (GLAccountsInfo debitChild in lstChildren)
{
dbResult += GetJournalAmount(debitChild, creditAccount, startDate, endDate, strConditionQuery, true);
}
}
return(dbResult);
}
public static double GetCreditBeginBalance(GLAccountsInfo accInfo)
{
double dbResult = 0;
List <BusinessObject> lstAccounts = new GLAccountsController().GetListByForeignKey("FK_GLAccountID", accInfo.GLAccountID);
foreach (GLAccountsInfo accChildInfo in lstAccounts)
{
dbResult += GetCreditBeginBalance(accChildInfo);
}
if (dbResult <= 0)
{
return(accInfo.CreditBeginBalance);
}
return(dbResult);
}
public static List <GLAccountsInfo> GetAccounts(String strAccountNo, bool isIncludeChildren)
{
List <GLAccountsInfo> lstResults = new List <GLAccountsInfo>();
GLAccountsInfo account = GetAccount(strAccountNo);
if (account != null)
{
lstResults.Add(account);
if (isIncludeChildren)
{
List <BusinessObject> lstChildren = new GLAccountsController().GetListByForeignKey("FK_GLAccountID", account.GLAccountID);
foreach (GLAccountsInfo child in lstChildren)
{
lstResults.AddRange(GetAccounts(child.No, false));
}
}
}
return(lstResults);
}
public static void CalculateAccount(Guid iAccountID)
{
GLAccountsController accCtrl = new GLAccountsController();
GLAccountsInfo accInfo = accCtrl.GetObjectByID(iAccountID) as GLAccountsInfo;
if (accInfo == null)
{
return;
}
accInfo = CalculateAccount(accInfo, null, null, "", true);
accCtrl.UpdateObject(accInfo);
if (accInfo.FK_GLAccountID.HasValue)
{
CalculateAccount(accInfo.FK_GLAccountID.Value);
}
}
public static List <GLJournalEntrysInfo> GetCreditEntrys(GLAccountsInfo accInfo, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
String strQuery = String.Format(@"SELECT * FROM GLJournalEntrys WHERE ApprovalStatus='{0}' AND FK_GLAccountID_Credit='{1}' ", ABCCommon.ABCConstString.ApprovalTypeApproved, accInfo.GLAccountID);
if (startDate.HasValue)
{
strQuery += String.Format(@" AND {0}", TimeProvider.GenCompareDateTime("JournalDate", ">=", startDate.Value));
}
if (endDate.HasValue)
{
strQuery += String.Format(@" AND {0} ", TimeProvider.GenCompareDateTime("JournalDate", "<=", endDate.Value));
}
if (String.IsNullOrWhiteSpace(strConditionQuery) == false)
{
strQuery += String.Format(@" AND {0} ", strConditionQuery);
}
strQuery += String.Format(@" ORDER BY JournalDate ");
List <BusinessObject> lstResults = new GLJournalEntrysController().GetList(strQuery);
if (isIncludeChildren)
{
GLAccountsController accCtrl = new GLAccountsController();
DataSet ds = accCtrl.GetDataSetByForeignKey("FK_GLAccountID", accInfo.GLAccountID);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
GLAccountsInfo accChildInfo = accCtrl.GetObjectFromDataRow(dr) as GLAccountsInfo;
if (accChildInfo != null)
{
lstResults.AddRange(GetCreditEntrys(accChildInfo, startDate, endDate, strConditionQuery, true));
}
}
}
}
return(lstResults.ConvertAll <GLJournalEntrysInfo>(delegate(BusinessObject item) { return (GLJournalEntrysInfo)item; }));
}
public static double GetAccountAmount(AccountType type, List <String> lstAccounts, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
double debitTot = 0;
double creditTot = 0;
GLAccountsController accountCtrl = new GLAccountsController();
foreach (String strAccNo in lstAccounts)
{
GLAccountsInfo accInfo = accountCtrl.GetObjectByNo(strAccNo) as GLAccountsInfo;
if (accInfo != null)
{
debitTot += GetDebitAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
creditTot += GetCreditAmount(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren);
}
}
if (type == AccountType.Debit)
{
return(debitTot - creditTot);
}
else if (type == AccountType.Credit)
{
return(creditTot - debitTot);
}
else
{
if (debitTot > creditTot)
{
debitTot -= creditTot;
return(debitTot - creditTot);
}
else
{
creditTot -= debitTot;
return(creditTot - debitTot);
}
}
}