public static double GetAccountAmount(AccountType type, Guid iAccountID, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
GLAccountsInfo accInfo = new GLAccountsController().GetObjectByID(iAccountID) as GLAccountsInfo;
if (accInfo == null)
{
return(0);
}
return(GetAccountAmount(type, accInfo, startDate, endDate, strConditionQuery, isIncludeChildren));
}
public static List <GLJournalEntrysInfo> GetCreditEntrys(Guid iAccountID, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
GLAccountsInfo accInfo = new GLAccountsController().GetObjectByID(iAccountID) as GLAccountsInfo;
if (accInfo == null)
{
return(new List <GLJournalEntrysInfo>());
}
return(GetCreditEntrys(accInfo, startDate, endDate, strConditionQuery, isIncludeChildren));
}
public static double GetJournalAmount(Guid debitAccountID, Guid creditAccountID, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
GLAccountsInfo debitAccount = new GLAccountsController().GetObjectByID(debitAccountID) as GLAccountsInfo;
GLAccountsInfo creditAccount = new GLAccountsController().GetObjectByID(creditAccountID) as GLAccountsInfo;
if (debitAccount == null || creditAccount == null)
{
return(0);
}
return(GetJournalAmount(debitAccount, creditAccount, startDate, endDate, strConditionQuery, isIncludeChildren));
}
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 List <GLJournalEntrysInfo> GenerateCloseEntrys(Guid fromAccID, Guid toAccID, bool isIncludeChildren, DateTime?startDate, DateTime?endDate, String strConditionQuery, String strRemark)
{
List <GLJournalEntrysInfo> lstResults = new List <GLJournalEntrysInfo>();
GLAccountsController accCtrl = new GLAccountsController();
GLAccountsInfo fromAccount = accCtrl.GetObjectByID(fromAccID) as GLAccountsInfo;
GLAccountsInfo toAccount = accCtrl.GetObjectByID(toAccID) as GLAccountsInfo;
if (fromAccount == null || toAccount == null)
{
return(lstResults);
}
#region Current
double dbDebitAmt = AccountingProvider.GetDebitAmount(fromAccID, startDate, endDate, strConditionQuery, false);
double dbCreditAmt = AccountingProvider.GetCreditAmount(fromAccID, startDate, endDate, strConditionQuery, false);
double dbDiff = dbDebitAmt - dbCreditAmt;
if (dbDiff != 0)
{
GLJournalEntrysInfo entry = new GLJournalEntrysInfo();
entry.Remark = strRemark + String.Format(@" : TK {0} sang TK {1}", fromAccount.No, toAccount.No);
entry.EntryType = ABCCommon.ABCConstString.EntryTypePeriodEnding;
if (dbDiff > 0)
{
entry.AmtTot = dbDiff;
entry.FK_GLAccountID_Debit = toAccID;
entry.FK_GLAccountID_Credit = fromAccID;
}
else
{
entry.AmtTot = -dbDiff;
entry.FK_GLAccountID_Debit = fromAccID;
entry.FK_GLAccountID_Credit = toAccID;
}
entry.RaiseAmtTot = entry.AmtTot;
entry.FK_GLAccountID_RaiseDebit = entry.FK_GLAccountID_Debit;
entry.FK_GLAccountID_RaiseCredit = entry.FK_GLAccountID_Credit;
lstResults.Add(entry);
}
#endregion
if (isIncludeChildren)
{
List <BusinessObject> lstChildren = accCtrl.GetListByForeignKey("FK_GLParentAccountID", fromAccount.GLAccountID);
foreach (GLAccountsInfo child in lstChildren)
{
lstResults.AddRange(GenerateCloseEntrys(child.GLAccountID, toAccID, true, startDate, endDate, strConditionQuery, strRemark));
}
}
return(lstResults);
}
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 GLJournalEntrysInfo GenerateJournalEntry(BusinessObject objItem, String strDebitNo, String strCreditNo, double dbAmt, String strDesc)
{
GLAccountsController accCtrl = new GLAccountsController();
GLJournalEntrysInfo entry = new GLJournalEntrysInfo();
BusinessObjectHelper.CopyObject(objItem, entry, false);
InvalidateJournalEntry(entry, null);
entry.Remark = strDesc;
entry.FK_GLAccountID_Debit = ABCHelper.DataConverter.ConvertToGuid(accCtrl.GetIDByNo(strDebitNo));
entry.FK_GLAccountID_RaiseDebit = entry.FK_GLAccountID_Debit;
entry.FK_GLAccountID_Credit = ABCHelper.DataConverter.ConvertToGuid(accCtrl.GetIDByNo(strCreditNo));
entry.FK_GLAccountID_RaiseCredit = entry.FK_GLAccountID_Credit;
entry.AmtTot = dbAmt;
return(entry);
}
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);
}
}
}
