/// <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();
}
/// <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>
/// This array of CostCentres may contain some funds that should not be processed
/// through ICH.
/// I need to process those here, and remove them from the list.
///
/// </summary>
private static StringDictionary GetDestinationAccountCodes(int ALedgerNumber, ACostCentreTable CostCentreTbl, TDBTransaction ATransaction)
{
AAccountPropertyTable AccountPropertyTbl = new AAccountPropertyTable();
AAccountPropertyRow TemplateRow = AccountPropertyTbl.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PropertyCode = "CLEARING-ACCOUNT-FOR-CC";
AccountPropertyTbl = AAccountPropertyAccess.LoadUsingTemplate(TemplateRow, ATransaction);
StringDictionary ReportingAccountForCC = new StringDictionary();
foreach (AAccountPropertyRow Row in AccountPropertyTbl.Rows)
{
String[] CcList = Row.PropertyValue.Split(',');
foreach (String CC in CcList)
{
ReportingAccountForCC.Add(CC, Row.AccountCode);
}
}
foreach (DataRow UntypedCCRow in CostCentreTbl.Rows)
{
ACostCentreRow CostCentreRow = (ACostCentreRow)UntypedCCRow;
if (!ReportingAccountForCC.ContainsKey(CostCentreRow.CostCentreCode))
{
ReportingAccountForCC.Add(CostCentreRow.CostCentreCode, MFinanceConstants.ICH_ACCT_ICH);
}
}
return ReportingAccountForCC;
}
/// <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>
/// Validates the Gift Batch data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
/// <param name="AAccountTableRef">Account Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACostCentreTableRef">Cost centre table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="AAccountPropertyTableRef">Account Property Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="AAccountingPeriodTableRef">Accounting Period Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACurrencyTableRef">Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ABaseCurrency">Ledger base currency. Required when importing</param>
/// <param name="AInternationalCurrency">Ledger international currency. Required when importing</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGiftBatchManual(object AContext,
AGiftBatchRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
TValidationControlsDict AValidationControlsDict,
AAccountTable AAccountTableRef = null,
ACostCentreTable ACostCentreTableRef = null,
AAccountPropertyTable AAccountPropertyTableRef = null,
AAccountingPeriodTable AAccountingPeriodTableRef = null,
ACorporateExchangeRateTable ACorporateExchangeTableRef = null,
ACurrencyTable ACurrencyTableRef = null,
string ABaseCurrency = null,
string AInternationalCurrency = null)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TScreenVerificationResult VerificationResult;
object ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted or posted DataRows
if ((ARow.RowState == DataRowState.Deleted) || (ARow.BatchStatus == MFinanceConstants.BATCH_POSTED))
{
return true;
}
bool IsImporting = AContext.ToString().Contains("Importing");
// Bank Account Code must be active
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankAccountCodeId];
ValidationContext = ARow.BankAccountCode;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsBankAccountCodeNull() && (AAccountTableRef != null))
{
// We even need to check that the code exists!
AAccountRow foundRow = (AAccountRow)AAccountTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankAccountCode });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Unknown bank account code '{0}'."), ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// If it does exist and the account is a foreign currency account then the batch currency must match
if ((foundRow != null) && foundRow.ForeignCurrencyFlag)
{
if ((foundRow.ForeignCurrencyCode != ARow.CurrencyCode) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' is a foreign currency account so the currency code for the batch must be '{1}'."),
ARow.BankAccountCode, foundRow.ForeignCurrencyCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// If it does exist it must be a posting account
if (foundRow != null)
{
if (!foundRow.PostingStatus && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' is not a posting account."),
ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
if ((foundRow != null) && (ARow.GiftType == MFinanceConstants.GIFT_TYPE_GIFT))
{
// The account must be a bank account as defined in the AccountProperty table
if (AAccountPropertyTableRef != null)
{
AAccountPropertyRow foundRow2 = (AAccountPropertyRow)AAccountPropertyTableRef.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.BankAccountCode, "BANK ACCOUNT", "true" });
if ((foundRow2 == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' must be associated with a real 'Bank Account' when the gift type is a 'Gift'."),
ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
AAccountTableRef,
ValidationContext.ToString(),
AAccountTable.GetAccountActiveFlagDBName(),
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if ((VerificationResult != null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// Bank Cost Centre Code validation
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankCostCentreId];
ValidationContext = ARow.BankCostCentre;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsBankCostCentreNull() && (ACostCentreTableRef != null))
{
// We even need to check that the code exists!
ACostCentreRow foundRow = (ACostCentreRow)ACostCentreTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankCostCentre });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown Bank Cost Centre: '{0}'."), ARow.BankCostCentre),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// Even if the cost centre exists it must be a 'posting' cost centre
if (foundRow != null)
{
if (!foundRow.PostingCostCentreFlag && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("The cost centre '{0}' is not a Posting Cost Centre."), ARow.BankCostCentre),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
// Bank Cost Centre Code must be active
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
ACostCentreTableRef,
ValidationContext.ToString(),
ACostCentreTable.GetCostCentreActiveFlagDBName(),
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if ((VerificationResult != null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// Currency Code validation
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnCurrencyCodeId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsCurrencyCodeNull() && (ACurrencyTableRef != null))
{
// Currency code must exist in the currency table
ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.CurrencyCode);
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown currency code '{0}'."), ARow.CurrencyCode),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
// 'Exchange Rate' must be greater than 0
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnExchangeRateToBaseId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsExchangeRateToBaseNull())
{
VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase,
ValidationControlsData.ValidationControlLabel +
(IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
// Exchange rate must be 1.00 if the currency is the the base ledger currency
if ((ABaseCurrency != null)
&& (!ARow.IsCurrencyCodeNull())
&& (ARow.CurrencyCode == ABaseCurrency)
&& (ARow.ExchangeRateToBase != 1.00m))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
Catalog.GetString("A batch in the ledger base currency must have exchange rate of 1.00."),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// 'Effective From Date' must be valid
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGlEffectiveDateId];
ValidationContext = ARow.BatchNumber;
DateTime StartDateCurrentPeriod;
DateTime EndDateLastForwardingPeriod;
TSharedFinanceValidationHelper.GetValidPostingDateRange(ARow.LedgerNumber,
out StartDateCurrentPeriod,
out EndDateLastForwardingPeriod);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = (TScreenVerificationResult)TDateChecks.IsDateBetweenDates(ARow.GlEffectiveDate,
StartDateCurrentPeriod,
EndDateLastForwardingPeriod,
ValidationControlsData.ValidationControlLabel + (IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()),
TDateBetweenDatesCheckType.dbdctUnspecific,
TDateBetweenDatesCheckType.dbdctUnspecific,
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
// If the GL date was good we need to have a corporate exchange rate for base currency to Intl for the first day of the period
if ((VerificationResult == null) && (ACorporateExchangeTableRef != null) && !ARow.IsGlEffectiveDateNull()
&& (ABaseCurrency != null) && (AInternationalCurrency != null) && (ABaseCurrency != AInternationalCurrency))
{
DateTime firstOfMonth;
if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber, ARow.GlEffectiveDate, out firstOfMonth))
{
ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find(
new object[] { ABaseCurrency, AInternationalCurrency, firstOfMonth });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"International currency: there is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."),
ABaseCurrency, AInternationalCurrency,
StringHelper.DateToLocalizedString(firstOfMonth)),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// Gift Type must be one of our predefined constants
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGiftTypeId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsGiftTypeNull())
{
// Ensure the gift type is correct and that it matches one of the allowable options (applies when importing)
if ((ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT)
&& (ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
&& (ARow.GiftType != MFinanceConstants.GIFT_TYPE_OTHER))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown gift type '{0}'. Expected one of '{1}', '{2}' or '{3}'"),
ARow.GiftType,
MFinanceConstants.GIFT_TYPE_GIFT, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, MFinanceConstants.GIFT_TYPE_OTHER),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
return VerifResultCollAddedCount == 0;
}
/// <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);
}
private void ParseBatchLine(ref AGiftBatchRow AGiftBatch,
ref TDBTransaction ATransaction,
ref ALedgerTable ALedgerTable,
ref string AImportMessage,
int ARowNumber,
TVerificationResultCollection AMessages,
TValidationControlsDict AValidationControlsDictBatch,
AAccountTable AValidationAccountTable,
AAccountPropertyTable AValidationAccountPropertyTable,
AAccountingPeriodTable AValidationAccountingPeriodTable,
ACostCentreTable AValidationCostCentreTable,
ACorporateExchangeRateTable AValidationCorporateExchTable,
ACurrencyTable AValidationCurrencyTable)
{
// There are 8 elements to import (the last of which can be blank)
string BatchDescription = ImportString(Catalog.GetString("Batch description"),
FMainDS.AGiftBatch.ColumnBatchDescription, AValidationControlsDictBatch);
string BankAccountCode = ImportString(Catalog.GetString("Bank account code"),
FMainDS.AGiftBatch.ColumnBankAccountCode, AValidationControlsDictBatch).ToUpper();
decimal HashTotal = ImportDecimal(Catalog.GetString("Hash total"),
FMainDS.AGiftBatch.ColumnHashTotal, ARowNumber, AMessages, AValidationControlsDictBatch);
DateTime GlEffectiveDate = ImportDate(Catalog.GetString("Effective Date"),
FMainDS.AGiftBatch.ColumnGlEffectiveDate, ARowNumber, AMessages, AValidationControlsDictBatch);
AImportMessage = "Creating new batch";
// This call sets: BatchNumber, BatchYear, BatchPeriod, GlEffectiveDate, ExchangeRateToBase, BatchDescription, BankAccountCode
// BankCostCentre and CurrencyCode. The effective date will NOT be modified.
// The first three are not validated because they should be ok by default
AGiftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref FMainDS,
ref ATransaction,
ref ALedgerTable,
FLedgerNumber,
GlEffectiveDate,
false);
// Now we modify some of these in the light of the imported data
AGiftBatch.BatchDescription = BatchDescription;
AGiftBatch.BankAccountCode = BankAccountCode;
AGiftBatch.HashTotal = HashTotal;
AGiftBatch.CurrencyCode = ImportString(Catalog.GetString("Currency code"),
FMainDS.AGiftBatch.ColumnCurrencyCode, AValidationControlsDictBatch);
AGiftBatch.ExchangeRateToBase = ImportDecimal(Catalog.GetString("Exchange rate to base"),
FMainDS.AGiftBatch.ColumnExchangeRateToBase, ARowNumber, AMessages, AValidationControlsDictBatch);
AGiftBatch.BankCostCentre = ImportString(Catalog.GetString("Bank cost centre"),
FMainDS.AGiftBatch.ColumnBankCostCentre, AValidationControlsDictBatch).ToUpper();
AGiftBatch.GiftType = ImportString(Catalog.GetString("Gift type"),
FMainDS.AGiftBatch.ColumnGiftType, AValidationControlsDictBatch);
// If GiftType was empty, will default to GIFT
// In all cases we ensure that the case entered by the user is converted to the case of our constants
if ((AGiftBatch.GiftType == String.Empty) || (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT, true) == 0))
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT;
}
else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, true) == 0)
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND;
}
else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_OTHER, true) == 0)
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_OTHER;
}
int messageCountBeforeValidate = AMessages.Count;
// Do our standard gift batch validation checks on this row
AImportMessage = Catalog.GetString("Validating the gift batch data");
AGiftBatchValidation.Validate(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch);
// And do the additional manual ones
AImportMessage = Catalog.GetString("Additional validation of the gift batch data");
TSharedFinanceValidation_Gift.ValidateGiftBatchManual(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch,
AValidationAccountTable, AValidationCostCentreTable, AValidationAccountPropertyTable, AValidationAccountingPeriodTable,
AValidationCorporateExchTable, AValidationCurrencyTable,
FLedgerBaseCurrency, FLedgerIntlCurrency);
for (int i = messageCountBeforeValidate; i < AMessages.Count; i++)
{
((TVerificationResult)AMessages[i]).OverrideResultContext(String.Format(MCommonConstants.StrValidationErrorInLine, ARowNumber));
if (AMessages[i] is TScreenVerificationResult)
{
TVerificationResult downgrade = new TVerificationResult((TScreenVerificationResult)AMessages[i]);
AMessages.RemoveAt(i);
AMessages.Insert(i, downgrade);
}
}
if (AGiftBatch.ExchangeRateToBase > 10000000) // Huge numbers here indicate that the decimal comma/point is incorrect.
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber),
String.Format(Catalog.GetString("A huge exchange rate of {0} suggests a decimal point format problem."),
AGiftBatch.ExchangeRateToBase),
TResultSeverity.Resv_Noncritical));
}
}
/// <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)
{
#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);
}
