public static double GetJournalAmount(List <String> lstDebitAccounts, List <String> lstCreditAccounts, DateTime?startDate, DateTime?endDate, String strConditionQuery, bool isIncludeChildren)
{
List <GLAccountsInfo> lstDebits = GetAccounts(lstDebitAccounts, isIncludeChildren);
List <GLAccountsInfo> lstCredits = GetAccounts(lstCreditAccounts, isIncludeChildren);
String strDebit = String.Empty;
foreach (GLAccountsInfo acc in lstDebits)
{
if (String.IsNullOrEmpty(strDebit) == false)
{
strDebit += " OR ";
}
strDebit += String.Format(@" FK_GLAccountID_Debit='{0}' ", acc.GLAccountID);
}
strDebit = "(" + strDebit + ")";
String stCredit = String.Empty;
foreach (GLAccountsInfo acc in lstCredits)
{
if (String.IsNullOrEmpty(stCredit) == false)
{
stCredit += " OR ";
}
stCredit += String.Format(@" FK_GLAccountID_Credit='{0}' ", acc.GLAccountID);
}
stCredit = "(" + stCredit + ")";
double dbResult = 0;
String strQuery = String.Format(@"SELECT SUM(AmtTot) FROM GLJournalEntrys WHERE ApprovalStatus='{0}' AND {1} AND {2} ", ABCCommon.ABCConstString.ApprovalTypeApproved, strDebit, stCredit);
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);
}
return(dbResult);
}
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 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 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 void RefreshLookupTable(String strTableName)
{
bool isUpdate = false;
lock ( LookupTables )
{
LoadLookupTable(strTableName);
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colUpdateTime) &&
LastUpdateTimes.ContainsKey(strTableName))
{
#region Has 'UpdateTime'
DateTime lastTimeInDB = TimeProvider.GetTableLastUpdateTime(strTableName);
DataTable lookupTable = LookupTables[strTableName];
DateTime lastTimeInApp = LastUpdateTimes[strTableName];
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colABCStatus))
{
#region Has 'ABCStatus'
if (lastTimeInDB > lastTimeInApp)
{
#region Refresh Modified Items
String strQuery = String.Format(@"SELECT * FROM {0} WHERE ABCStatus ='Alive' AND {1}", strTableName, TimeProvider.GenCompareDateTime(ABCCommon.ABCConstString.colUpdateTime, ">", lastTimeInApp));
DataSet ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
BusinessObjectController controller = BusinessControllerFactory.GetBusinessController(strTableName);
String strPK = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
foreach (DataRow row in ds.Tables[0].Rows)
{
#region Row
BusinessObject obj = (BusinessObject)controller.GetObjectFromDataRow(row);
Guid iID = ABCHelper.DataConverter.ConvertToGuid(ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, strPK));
DataRow[] rows = lookupTable.Select(String.Format("{0}='{1}'", strPK, iID));
if (rows.Length == 0) // new
{
lookupTable.ImportRow(row);
}
else
{
DataRow dr = rows[0];
int index = lookupTable.Rows.IndexOf(dr);
if (DataStructureProvider.IsExistABCStatus(obj.AATableName) && ABCBusinessEntities.ABCDynamicInvoker.GetValue(obj, ABCCommon.ABCConstString.colABCStatus).ToString().Equals(ABCCommon.ABCConstString.ABCStatusDeleted)) //delete
{
lookupTable.Rows.RemoveAt(index);
}
else //update
{
object[] lstArr = new object[row.ItemArray.Length];
row.ItemArray.CopyTo(lstArr, 0);
lookupTable.Rows[index].ItemArray = lstArr;
}
}
#endregion
}
isUpdate = true;
}
#endregion
}
#endregion
}
else
{
#region Without 'ABCStatus'
if (lastTimeInDB > lastTimeInApp || TimeProvider.GetRecordCountOfTable(strTableName) != lookupTable.Rows.Count)
{
DataSet ds = BusinessControllerFactory.GetBusinessController(strTableName).GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
lookupTable.Rows.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lookupTable.ImportRow(dr);
}
isUpdate = true;
}
}
#endregion
}
LastUpdateTimes[strTableName] = lastTimeInDB;
#endregion
}
else
{
#region Without 'UpdateTime'
DataTable lookupTable = LookupTables[strTableName];
if (TimeProvider.GetRecordCountOfTable(strTableName) != lookupTable.Rows.Count)
{
DataSet ds = BusinessControllerFactory.GetBusinessController(strTableName).GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
lookupTable.Rows.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
lookupTable.ImportRow(dr);
}
isUpdate = true;
}
}
#endregion
}
}
if (isUpdate && RefreshTableMethods.ContainsKey(strTableName))
{
foreach (MethodInfo method in RefreshTableMethods[strTableName])
{
method.Invoke(null, new object[] {});
}
}
}
