/// <summary> /// Helper method that we can call to initialise each of the filter/find comboBoxes /// </summary> private void InitFilterFindComboBox(TCmbAutoPopulated AClonedFromComboBox, TCmbAutoComplete AFFInstance) { AFFInstance.DisplayMember = AClonedFromComboBox.DisplayMember; AFFInstance.ValueMember = AClonedFromComboBox.ValueMember; if (AClonedFromComboBox.Name.Contains("Account")) { // This is quicker than getting the cached table again DataView dv = new DataView(FAccountTable.Copy()); dv.RowFilter = TFinanceControls.PrepareAccountFilter(true, false, false, false, ""); dv.Sort = String.Format("{0}", AAccountTable.GetAccountCodeDBName()); AFFInstance.DataSource = dv; } else if (AClonedFromComboBox.Name.Contains("CostCentre")) { // This is quicker than getting the cached table again DataView dv = new DataView(FCostCentreTable.Copy()); dv.RowFilter = TFinanceControls.PrepareCostCentreFilter(true, false, false, false); dv.Sort = String.Format("{0}", ACostCentreTable.GetCostCentreCodeDBName()); AFFInstance.DataSource = dv; } else { throw new Exception("Unexpected ComboBox name"); } AFFInstance.DrawMode = DrawMode.OwnerDrawFixed; AFFInstance.DrawItem += new DrawItemEventHandler(DrawComboBoxItem); }
private DataTable GetAccountListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName) { StringCollection FieldList = new StringCollection(); FieldList.Add(AAccountTable.GetLedgerNumberDBName()); FieldList.Add(AAccountTable.GetAccountCodeDBName()); FieldList.Add(AAccountTable.GetAccountTypeDBName()); FieldList.Add(AAccountTable.GetAccountCodeShortDescDBName()); FieldList.Add(AAccountTable.GetAccountActiveFlagDBName()); FieldList.Add(AAccountTable.GetPostingStatusDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyFlagDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyCodeDBName()); GLSetupTDS TempDS = new GLSetupTDS(); AAccountAccess.LoadViaALedger(TempDS, ALedgerNumber, FieldList, AReadTransaction); // load AAccountProperty and set the BankAccountFlag AAccountPropertyAccess.LoadViaALedger(TempDS, ALedgerNumber, AReadTransaction); foreach (AAccountPropertyRow accProp in TempDS.AAccountProperty.Rows) { if ((accProp.PropertyCode == MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT) && (accProp.PropertyValue == "true")) { TempDS.AAccount.DefaultView.RowFilter = String.Format("{0}='{1}'", AAccountTable.GetAccountCodeDBName(), accProp.AccountCode); GLSetupTDSAAccountRow acc = (GLSetupTDSAAccountRow)TempDS.AAccount.DefaultView[0].Row; acc.BankAccountFlag = true; TempDS.AAccount.DefaultView.RowFilter = ""; } } // not currently needed as an Account is only a Bank Account if it has a 'Bank Account' Account Property // load AAccountHierarchyDetails and check if this account reports to the CASH account /*AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(TempDS, * ALedgerNumber, * MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, * AReadTransaction); * * TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber); * TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo); * List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT); * * foreach (GLSetupTDSAAccountRow account in TempDS.AAccount.Rows) * { * if (children.Contains(account.AccountCode)) * { * account.CashAccountFlag = true; * } * }*/ return(TempDS.AAccount); }
/// <summary> /// Export the accounts /// </summary> public static void ExportAccounts(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, List <string> AAccounts) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "account.csv")); Console.WriteLine("Writing file: " + filename); // only export accounts that are actually used with these cost centres string sql = String.Format("SELECT {0}, {1}, {2} from PUB_{3} WHERE {4} = {5} AND " + " {6}=true ORDER BY {0}", AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeLongDescDBName(), AAccountTable.GetDebitCreditIndicatorDBName(), AAccountTable.GetTableDBName(), AAccountTable.GetLedgerNumberDBName(), ALedgerNumber, AAccountTable.GetPostingStatusDBName()); TDBTransaction Transaction = null; DataTable accounts = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { accounts = DBAccess.GDBAccessObj.SelectDT(sql, "accounts", Transaction); }); StringBuilder sb = new StringBuilder(); if (accounts != null) { foreach (DataRow row in accounts.Rows) { if (AAccounts.Contains(row[0].ToString())) { sb.Append(StringHelper.StrMerge(new string[] { row[0].ToString(), row[1].ToString(), Convert.ToBoolean(row[2]) ? "Soll" : "Haben" }, ACSVSeparator)); sb.Append(ANewLine); } } } StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
// select either the given account code or (if it is no longer in the grid) the first row in the grid private void SelectAccountInGrid(string AAccountCode) { FPreviouslySelectedDetailRow = null; foreach (DataRowView RowView in FForeignCurrencyAccountsDT.DefaultView) { if (RowView[AAccountTable.GetAccountCodeDBName()].ToString() == AAccountCode) { grdDetails.SelectRowInGrid(grdDetails.Rows.DataSourceRowToIndex(RowView) + 1); return; } } SelectRowInGrid(1); }
/// <summary> /// Populates the Cost Centre, Account Code and DR Account Code ComboBoxes. /// </summary> /// <param name="ACostCentreComboBox">Cost Centre ComboBox Control</param> /// <param name="AAccountCodeComboBox">Account Code ComboBox Control</param> /// <param name="ADrAccountCodeComboBox">DR Account Code ComboBox Control</param> /// <param name="ALedgerNumber">Ledger Number</param> /// <param name="ACalledByReceivableScreen">Set this to true if the 'Admin Grants Receivable' screen is calling this Method.</param> public static void PopulateComboBoxes(TCmbAutoPopulated ACostCentreComboBox, TCmbAutoPopulated AAccountCodeComboBox, TCmbAutoPopulated ADrAccountCodeComboBox, Int32 ALedgerNumber, bool ACalledByReceivableScreen) { string filter = String.Empty; DataTable CostCentreListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList, ALedgerNumber); CostCentreListTable.DefaultView.Sort = ACostCentreTable.GetCostCentreNameDBName() + " ASC"; if (ACalledByReceivableScreen) { filter = ACostCentreTable.GetPostingCostCentreFlagDBName() + " = true AND " + ACostCentreTable.GetCostCentreTypeDBName() + " = 'Local'"; } else { filter = ACostCentreTable.GetPostingCostCentreFlagDBName() + " = true"; } ACostCentreComboBox.InitialiseUserControl(CostCentreListTable, ACostCentreTable.GetCostCentreCodeDBName(), ACostCentreTable.GetCostCentreNameDBName(), null); ACostCentreComboBox.AppearanceSetup(new int[] { -1, 300 }, 20); ACostCentreComboBox.Filter = filter; DataTable AccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, ALedgerNumber); AccountListTable.DefaultView.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; filter = AAccountTable.GetPostingStatusDBName() + " = true AND " + AAccountTable.GetAccountTypeDBName().ToUpper() + " = 'INCOME'"; AAccountCodeComboBox.InitialiseUserControl(AccountListTable, AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeShortDescDBName(), null); AAccountCodeComboBox.AppearanceSetup(new int[] { -1, 300 }, 20); AAccountCodeComboBox.Filter = filter; DataTable DrAccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, ALedgerNumber); DrAccountListTable.DefaultView.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; filter = AAccountTable.GetPostingStatusDBName() + " = true AND " + AAccountTable.GetAccountTypeDBName().ToUpper() + " = 'EXPENSE'"; ADrAccountCodeComboBox.InitialiseUserControl(DrAccountListTable, AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeShortDescDBName(), null); ADrAccountCodeComboBox.AppearanceSetup(new int[] { -1, 300 }, 20); ADrAccountCodeComboBox.Filter = filter; }
public static void GetForeignCurrencyAccountActuals(ref DataTable AForeignCurrencyAccounts, Int32 ALedgerNumber, Int32 AYear) { DataTable ForeignCurrencyAccounts = AForeignCurrencyAccounts; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetForeignCurrencyAccountActuals"); db.ReadTransaction(ref Transaction, delegate { AGeneralLedgerMasterTable glmTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = glmTbl.NewRowTyped(false); foreach (DataRow ForeignCurrencyAccountRow in ForeignCurrencyAccounts.Rows) { GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.Year = AYear; GLMTemplateRow.AccountCode = ForeignCurrencyAccountRow[AAccountTable.GetAccountCodeDBName()].ToString(); glmTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, Transaction); Decimal YtdActual = 0; Decimal YtdActualForeign = 0; if (glmTbl != null) { // // I need to sum up all the GLM entries for this account: foreach (AGeneralLedgerMasterRow glmRow in glmTbl.Rows) { if (!glmRow.IsYtdActualForeignNull()) { YtdActualForeign += glmRow.YtdActualForeign; } YtdActual += glmRow.YtdActualBase; } } ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = YtdActual; ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = YtdActualForeign; } }); db.CloseDBConnection(); }
public static void GetForeignCurrencyAccountActuals(ref DataTable AForeignCurrencyAccounts, Int32 ALedgerNumber, Int32 AYear) { //string ReturnValue = ""; DataTable ForeignCurrencyAccounts = AForeignCurrencyAccounts.Clone(); ForeignCurrencyAccounts.Merge(AForeignCurrencyAccounts); string CostCentreCode = "[" + ALedgerNumber + "]"; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction, delegate { foreach (DataRow ForeignCurrencyAccountRow in ForeignCurrencyAccounts.Rows) { AGeneralLedgerMasterTable Table = AGeneralLedgerMasterAccess.LoadByUniqueKey( ALedgerNumber, AYear, ForeignCurrencyAccountRow[AAccountTable.GetAccountCodeDBName()].ToString(), CostCentreCode, Transaction); if ((Table != null) && (Table.Rows.Count > 0)) { AGeneralLedgerMasterRow Row = Table[0]; if (Row.IsYtdActualForeignNull()) { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = 0; } else { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = Row.YtdActualForeign; } ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = Row.YtdActualBase; } else { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = 0; ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = 0; } } }); AForeignCurrencyAccounts = ForeignCurrencyAccounts; }
private void GetData() { FMainDS.AAccount.DefaultView.RowFilter = "a_foreign_currency_flag_l = 'true'"; FForeignCurrencyAccountsDT.Clear(); FForeignCurrencyAccountsDT = FMainDS.AAccount.Clone(); FForeignCurrencyAccountsDT.Merge(FMainDS.AAccount.DefaultView.ToTable()); Int32 Year = (Int32)TDataCache.TMFinance.GetCacheableFinanceTable( TCacheableFinanceTablesEnum.LedgerDetails, FLedgerNumber).Rows[0][ALedgerTable.GetCurrentFinancialYearDBName()]; // get additional columns from database TRemote.MFinance.Common.ServerLookups.WebConnectors.GetForeignCurrencyAccountActuals(ref FForeignCurrencyAccountsDT, FLedgerNumber, Year); DataView myDataView = FForeignCurrencyAccountsDT.DefaultView; myDataView.AllowNew = false; myDataView.RowFilter = "a_foreign_currency_flag_l = 'true'"; myDataView.Sort = AAccountTable.GetForeignCurrencyCodeDBName() + " ASC, " + AAccountTable.GetAccountCodeDBName() + " ASC"; grdDetails.DataSource = new DevAge.ComponentModel.BoundDataView(myDataView); }
/// <summary> /// export all GL Transactions in the given year, towards the specified cost centres /// </summary> public static void ExportGLTransactions(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres, string AIgnoreAccounts, string AIgnoreReferences, ref List <string> ACostCentresInvolved, ref List <string> AAccountsInvolved) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv")); Console.WriteLine("Writing file: " + filename); TDBTransaction Transaction = new TDBTransaction(); ATransactionTable transactions = new ATransactionTable(); ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable(); ATransactionTable allTransactionsInJournal = new ATransactionTable(); AGiftBatchTable giftbatches = new AGiftBatchTable(); AAccountTable accounts = new AAccountTable(); DBAccess.ReadTransaction(ref Transaction, delegate { TDataBase db = Transaction.DataBaseObj; string sql = String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " + "FROM PUB_{8} AS B, PUB_{7} AS T " + "WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " + "AND T.{9} = B.{9} AND T.{0} = B.{0} " + "AND T.{13} IN ({14}) " + "AND NOT T.{17} IN ({19}) " + "AND NOT T.{20} IN ({21}) " + "ORDER BY {0}, {1}, {2}", ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTransactionAmountDBName(), ABatchTable.GetDateEffectiveDBName(), ATransactionTable.GetNarrativeDBName(), ATransactionTable.GetReferenceDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ABatchTable.GetBatchYearDBName(), AFinancialYear, ATransactionTable.GetAccountCodeDBName(), ATransactionTable.GetDebitCreditIndicatorDBName(), "'" + AIgnoreAccounts.Replace(",", "','") + "'", ATransactionTable.GetReferenceDBName(), "'" + AIgnoreReferences.Replace(",", "','") + "'"); transactions = (ATransactionTable)db.SelectDT(transactions, sql, Transaction, null, 0, 0); // get the analysis attributes sql = String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " + "WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " + "AND T.{2} = B.{2} AND T.{8} = B.{8} " + "AND T.{9} IN ({10}) " + "AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}", ATransAnalAttribTable.GetTableDBName(), ABatchTable.GetTableDBName(), ABatchTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchYearDBName(), AFinancialYear, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetBatchYearDBName()); db.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0); TransAnalAttrib.DefaultView.Sort = ATransAnalAttribTable.GetBatchNumberDBName() + "," + ATransAnalAttribTable.GetJournalNumberDBName() + "," + ATransAnalAttribTable.GetTransactionNumberDBName(); // get a list of all batches involved List <Int64> batches = new List <Int64>(); StringBuilder batchnumbers = new StringBuilder(); foreach (ATransactionRow r in transactions.Rows) { if (!batches.Contains(r.BatchNumber)) { batches.Add(r.BatchNumber); batchnumbers.Append(r.BatchNumber.ToString() + ","); } } // get the other transactions in the same journal for finding the opposite cc/acc involved // for performance reasons, get all transactions of the whole batch sql = String.Format("SELECT DISTINCT TJ.* " + "FROM PUB_{0} AS TJ " + "WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})", ATransactionTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ATransactionTable.GetBatchNumberDBName(), batchnumbers.ToString() + "-1"); allTransactionsInJournal = (ATransactionTable)db.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0); allTransactionsInJournal.DefaultView.Sort = ATransactionTable.GetBatchNumberDBName() + "," + ATransactionTable.GetJournalNumberDBName(); // get all names of gift batches sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2} " + "AND {3} = {4}", AGiftBatchTable.GetTableDBName(), AGiftBatchTable.GetLedgerNumberDBName(), ALedgerNumber, AGiftBatchTable.GetBatchYearDBName(), AFinancialYear); db.SelectDT(giftbatches, sql, Transaction, null, 0, 0); giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName(); sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2}", AAccountTable.GetTableDBName(), AAccountTable.GetLedgerNumberDBName(), ALedgerNumber); db.SelectDT(accounts, sql, Transaction, null, 0, 0); accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName(); }); StringBuilder sb = new StringBuilder(); int rowCounter = 0; foreach (ATransactionRow row in transactions.Rows) { if (row.DebitCreditIndicator) { row.TransactionAmount *= -1.0m; } StringBuilder attributes = new StringBuilder(); DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber }); ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions); string OtherCostCentres = string.Empty; string OtherAccountCodes = string.Empty; if (OtherTransactions.Length < 30) { foreach (ATransactionRow r in OtherTransactions) { OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode); OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode); } } if (!ACostCentresInvolved.Contains(row.CostCentreCode)) { ACostCentresInvolved.Add(row.CostCentreCode); } if (!AAccountsInvolved.Contains(row.AccountCode)) { AAccountsInvolved.Add(row.AccountCode); } // we are using gift batch for receiving payments string Narrative = row.Narrative; if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB")) { // find the account and set the account description into the narrative try { DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode); Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc; } catch (Exception) { } try { DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2))); Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription; } catch (Exception) { } } sb.Append(StringHelper.StrMerge( new string[] { "B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(), row.CostCentreCode, row.AccountCode, row.TransactionDate.ToString("yyyyMMdd"), OtherCostCentres, OtherAccountCodes, Narrative, row.Reference, String.Format("{0:N}", row.TransactionAmount), attributes.ToString() }, ACSVSeparator)); sb.Append(ANewLine); rowCounter++; if (rowCounter % 500 == 0) { TLogging.Log("Processing transactions " + rowCounter.ToString()); } } TLogging.Log("Processing transactions " + rowCounter.ToString()); StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
/// <summary> /// get the default bank account for this ledger /// </summary> public string GetDefaultBankAccount() { #region Validate Arguments if (FLedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), FLedgerNumber); } #endregion Validate Arguments TDataBase db = DBAccess.Connect("GetDefaultBankAccount", FDataBase); TDBTransaction readTransaction = new TDBTransaction(); string BankAccountCode = new TSystemDefaults(db).GetStringDefault( SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + FLedgerNumber.ToString()); if (BankAccountCode.Length == 0) { try { db.ReadTransaction( ref readTransaction, delegate { // use the first bank account AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(FLedgerNumber, readTransaction); accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" + MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " + AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'"; if (accountProperties.DefaultView.Count > 0) { BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode; } else { string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c" + " FROM a_gift_batch " + " WHERE a_gift_batch.a_ledger_number_i =" + FLedgerNumber + " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "'" + " ORDER BY a_gift_batch.a_batch_number_i DESC" + " LIMIT 1;"; DataTable latestAccountCode = db.SelectDT(SQLQuery, "LatestAccountCode", readTransaction); // use the Bank Account of the previous Gift Batch if ((latestAccountCode != null) && (latestAccountCode.Rows.Count > 0)) { BankAccountCode = latestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c" } // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database else { AAccountTable accountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, readTransaction); #region Validate Data if ((accountTable == null) || (accountTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), FLedgerNumber)); } #endregion Validate Data DataView dv = accountTable.DefaultView; dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c dv.RowFilter = String.Format("{0} = true AND {1} = true", AAccountTable.GetAccountActiveFlagDBName(), AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true"; DataTable sortedDT = dv.ToTable(); TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(FLedgerNumber); List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT); foreach (DataRow account in sortedDT.Rows) { // check if this account reports to the CASH account if (children.Contains(account["a_account_code_c"].ToString())) { BankAccountCode = account["a_account_code_c"].ToString(); break; } } } } }); if (FDataBase == null) { db.CloseDBConnection(); } } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } } return(BankAccountCode); }
/// <summary> /// get the default bank account for this ledger /// </summary> /// <param name="ALedgerNumber"></param> public static string GetDefaultBankAccount(int ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault( SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString()); if (BankAccountCode.Length == 0) { TDBTransaction readTransaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref readTransaction, delegate { // use the first bank account AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction); accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" + MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " + AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'"; if (accountProperties.DefaultView.Count > 0) { BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode; } else { string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " + "FROM a_gift_batch " + "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber + " AND a_gift_batch.a_batch_number_i = (" + "SELECT max(a_gift_batch.a_batch_number_i) " + "FROM a_gift_batch " + "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber + " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')"; DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction); // use the Bank Account of the previous Gift Batch if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0)) { BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c" } // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database else { AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction); #region Validate Data if ((AccountTable == null) || (AccountTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data DataView dv = AccountTable.DefaultView; dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c dv.RowFilter = String.Format("{0} = true AND {1} = true", AAccountTable.GetAccountActiveFlagDBName(), AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true"; DataTable sortedDT = dv.ToTable(); TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber); TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo); List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT); foreach (DataRow account in sortedDT.Rows) { // check if this account reports to the CASH account if (children.Contains(account["a_account_code_c"].ToString())) { BankAccountCode = account["a_account_code_c"].ToString(); break; } } } } }); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } } return(BankAccountCode); }