/// <summary> /// The constructor needs a ledgerinfo (for the ledger number) /// </summary> /// <param name="ALedgerNumber"></param> public THandleAccountPropertyInfo(Int32 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 TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { FPropertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction); }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } }
/// <summary> /// The constructor needs a ledgerinfo (for the ledger number) /// </summary> /// <param name="ALedgerNumber"></param> public THandleAccountPropertyInfo(Int32 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 TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("THandleAccountPropertyInfo"); try { db.ReadTransaction( ref Transaction, delegate { FPropertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction); }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } db.CloseDBConnection(); }
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> /// The constructor needs a ledgerinfo (for the ledger number) /// </summary> /// <param name="ledgerInfo"></param> public THandleAccountPropertyInfo(TLedgerInfo ledgerInfo) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); propertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ledgerInfo.LedgerNumber, transaction); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); } } }
/// <summary> /// get the default bank account for this ledger /// </summary> /// <param name="ALedgerNumber"></param> public static string GetDefaultBankAccount(int ALedgerNumber) { string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault( SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString()); if (BankAccountCode.Length == 0) { bool NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { // use the first bank account AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction); 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 { // needed for old Petra 2.x database BankAccountCode = "6000"; } } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } } return(BankAccountCode); }
/// <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) { string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault( SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString()); if (BankAccountCode.Length == 0) { TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { // use the first bank account AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction); 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", Transaction); // use the Bank Account of the previous Gift Batch if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0)) { BankAccountCode = LatestAccountCode.Rows[0]["a_bank_account_code_c"].ToString(); } // 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, Transaction); DataView dv = AccountTable.DefaultView; dv.Sort = "a_account_code_c asc"; dv.RowFilter = "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; } } } } }); } 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); }