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[] {}); } } }