/// <summary>
/// ...
/// </summary>
/// <param name="ALedgerNumber"></param>
public TGetAccountHierarchyDetailInfo(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
{
FHierarchyDetailTable = AAccountHierarchyDetailAccess.LoadViaALedger(ALedgerNumber, Transaction);
FAccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);
FAccountTable.DefaultView.Sort = "a_account_code_c";
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
}
private void PrepareTestCaseData()
{
TDBTransaction Transaction = null;
bool AccountTestCasesAvailable = false;
bool CostCentreTestCasesAvailable = false;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
// Check if some special test data are available - otherwise load ...
AccountTestCasesAvailable = AAccountAccess.Exists(LedgerNumber, "6001", Transaction);
CostCentreTestCasesAvailable = ACostCentreAccess.Exists(LedgerNumber, "4301", Transaction);
});
if (!AccountTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\" +
"test-sql\\gl-test-account-data.sql", LedgerNumber);
}
if (!CostCentreTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\" +
"test-sql\\gl-test-costcentre-data.sql", LedgerNumber);
}
}
/// <summary>
/// ...
/// </summary>
/// <param name="ALedgerNumber"></param>
public TGetAccountHierarchyDetailInfo(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("TGetAccountHierarchyDetailInfo");
try
{
db.ReadTransaction(
ref Transaction,
delegate
{
FHierarchyDetailTable = AAccountHierarchyDetailAccess.LoadViaALedger(ALedgerNumber, Transaction);
FAccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);
FAccountTable.DefaultView.Sort = "a_account_code_c";
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
db.CloseDBConnection();
}
private void LoadData()
{
FAccountRow = null;
if (FTransaction != null)
{
FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, FTransaction);
}
else
{
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction);
});
}
#region Validate Data
if ((FAccountTable == null) || (FAccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
FLedgerNumber));
}
#endregion Validate Data
}
private void LoadData()
{
FAccountRow = null;
if (FTransaction != null)
{
FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, FTransaction);
}
else
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("TAccountInfo.LoadData");
db.ReadTransaction(
ref Transaction,
delegate
{
FAccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction);
});
db.CloseDBConnection();
}
#region Validate Data
if ((FAccountTable == null) || (FAccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
FLedgerNumber));
}
#endregion Validate Data
}
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);
}
private void RunRevaluationIntern()
{
Boolean NewTransaction;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
AAccountTable accountTable = new AAccountTable();
AAccountRow accountTemplate = (AAccountRow)accountTable.NewRowTyped(false);
accountTemplate.LedgerNumber = F_LedgerNum;
accountTemplate.AccountActiveFlag = true;
accountTemplate.ForeignCurrencyFlag = true;
accountTable = AAccountAccess.LoadUsingTemplate(accountTemplate, DBTransaction);
AGeneralLedgerMasterTable glmTable = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)glmTable.NewRowTyped(false);
glmTemplate.LedgerNumber = F_LedgerNum;
glmTemplate.Year = F_FinancialYear;
glmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, DBTransaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
if (accountTable.Rows.Count == 0) // not using any foreign accounts?
{
return;
}
for (int iCnt = 0; iCnt < accountTable.Rows.Count; ++iCnt)
{
AAccountRow accountRow = (AAccountRow)accountTable[iCnt];
for (int kCnt = 0; kCnt < F_CurrencyCode.Length; ++kCnt)
{
// AForeignCurrency[] and ANewExchangeRate[] shall support a value
// for this account resp. for the currency of the account
if (accountRow.ForeignCurrencyCode.Equals(F_CurrencyCode[kCnt]))
{
glmTable.DefaultView.RowFilter = "a_account_code_c = '" + accountRow.AccountCode + "'";
if (glmTable.DefaultView.Count > 0)
{
RevaluateAccount(glmTable.DefaultView, F_ExchangeRate[kCnt]);
}
}
}
}
CloseRevaluationAccountingBatch();
}
private void LoadData()
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
FAccountTable = AAccountAccess.LoadViaALedger(
FLedgerInfo.LedgerNumber, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
FAccountRow = null;
}
/// <summary>
/// Get the actual amount
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AGLMSeqThisYear"></param>
/// <param name="AGLMSeqNextYear"></param>
/// <param name="APeriodNumber"></param>
/// <param name="ANumberAccountingPeriods"></param>
/// <param name="ACurrentFinancialYear"></param>
/// <param name="AThisYear"></param>
/// <param name="AYTD"></param>
/// <param name="ABalSheetForwardPeriods"></param>
/// <param name="ACurrencySelect"></param>
/// <returns></returns>
private static decimal GetActualInternal(int ALedgerNumber,
int AGLMSeqThisYear,
int AGLMSeqNextYear,
int APeriodNumber,
int ANumberAccountingPeriods,
int ACurrentFinancialYear,
int AThisYear,
bool AYTD,
bool ABalSheetForwardPeriods,
string ACurrencySelect)
{
decimal retVal = 0;
decimal currencyAmount = 0;
bool incExpAccountFwdPeriod = false;
//DEFINE BUFFER a_glm_period FOR a_general_ledger_master_period.
//DEFINE BUFFER a_glm FOR a_general_ledger_master.
//DEFINE BUFFER buf_account FOR a_account.
if (AGLMSeqThisYear == -1)
{
return(retVal);
}
bool newTransaction = false;
TDataBase db = DBAccess.Connect("GetActualInternal");
TDBTransaction dBTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction);
AGeneralLedgerMasterTable generalLedgerMasterTable = null;
AGeneralLedgerMasterRow generalLedgerMasterRow = null;
AGeneralLedgerMasterPeriodTable generalLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow generalLedgerMasterPeriodRow = null;
AAccountTable AccountTable = null;
AAccountRow AccountRow = null;
try
{
if (APeriodNumber == 0) /* start balance */
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterRow.StartBalanceBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterRow.StartBalanceIntl;
break;
default:
currencyAmount = generalLedgerMasterRow.StartBalanceForeign;
break;
}
}
else if (APeriodNumber > ANumberAccountingPeriods) /* forwarding periods only exist for the current financial year */
{
if (ACurrentFinancialYear == AThisYear)
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear,
APeriodNumber,
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
else
{
generalLedgerMasterPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqNextYear,
(APeriodNumber - ANumberAccountingPeriods),
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
}
else /* normal period */
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
if (generalLedgerMasterPeriodRow != null)
{
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterPeriodRow.ActualBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterPeriodRow.ActualIntl;
break;
default:
currencyAmount = generalLedgerMasterPeriodRow.ActualForeign;
break;
}
}
if ((APeriodNumber > ANumberAccountingPeriods) && (ACurrentFinancialYear == AThisYear))
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, generalLedgerMasterRow.AccountCode, dBTransaction);
AccountRow = (AAccountRow)AccountTable.Rows[0];
if ((AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
(AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_EXPENSE.ToUpper()) &&
!ABalSheetForwardPeriods)
{
incExpAccountFwdPeriod = true;
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
ANumberAccountingPeriods,
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
if (!AYTD)
{
if (!((APeriodNumber == (ANumberAccountingPeriods + 1)) && incExpAccountFwdPeriod) &&
!((APeriodNumber == (ANumberAccountingPeriods + 1)) && (ACurrentFinancialYear > AThisYear)))
{
/* if it is an income expense acount, and we are in period 13, nothing needs to be subtracted,
* because that was done in correcting the amount in the block above;
* if we are in a previous year, in period 13, don't worry about subtracting.
*
* THIS IS CLEARLY INCORRECT - THE CONDITION ABOVE APPLIES *ONLY* IN THE FIRST FORWARDING PERIOD, NOT IN EVERY FORWARDING PERIOD.
* IF THE METHOD IS ONLY CALLED FROM AUTOGENERATE BUDGETS, THIS IS PROBABLY INCONSEQUENTIAL.
*/
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
(APeriodNumber - 1),
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
retVal = currencyAmount;
}
finally
{
if (newTransaction)
{
dBTransaction.Rollback();
}
}
return(retVal);
}
/// <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);
}
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber)
{
FBudgetTDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction);
string sqlLoadBudgetForThisAndNextYear =
string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)",
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int);
param.Value = ALedgerNumber;
parameters.Add(param);
param = new OdbcParameter("thisyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear;
parameters.Add(param);
param = new OdbcParameter("nextyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1;
parameters.Add(param);
DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction,
parameters.ToArray());
string sqlLoadBudgetPeriodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
ABudgetPeriodTable.GetTableDBName(),
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(FBudgetTDS,
sqlLoadBudgetPeriodForThisAndNextYear,
FBudgetTDS.ABudgetPeriod.TableName,
Transaction,
parameters.ToArray());
// Accept row changes here so that the Client gets 'unmodified' rows
FBudgetTDS.AcceptChanges();
GLPostingDS = new GLPostingTDS();
AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction);
// get the glm sequences for this year and next year
for (int i = 0; i <= 1; i++)
{
int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i;
AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Year = Year;
GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction));
}
string sqlLoadGlmperiodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(GLPostingDS,
sqlLoadGlmperiodForThisAndNextYear,
GLPostingDS.AGeneralLedgerMasterPeriod.TableName,
Transaction,
parameters.ToArray());
});
GLPostingDS.AcceptChanges();
return(true);
}
/// <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);
}
/// <summary>
/// this can be used from the unit tests
/// </summary>
public static BankImportTDS ImportBankStatementHelper(Int32 ALedgerNumber,
string ABankAccountCode,
string ASeparator,
string ADateFormat,
string ANumberFormat,
string ACurrencyCode,
string AColumnsUsage,
string AStartAfterLine,
string ABankStatementFilename,
string AStatementData)
{
Int32 FirstTransactionRow = 0;
string DateFormat = (ADateFormat == "MDY" ? "M/d/yyyy" : "d.M.yyyy");
string ThousandsSeparator = (ANumberFormat == "American" ? "," : ".");
string DecimalSeparator = (ANumberFormat == "American" ? "." : ",");
List <String> StatementData = new List <string>();
string [] stmtarray = AStatementData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string line in stmtarray)
{
StatementData.Add(line);
}
// skip headers
Int32 lineCounter = FirstTransactionRow;
// TODO: support splitting a file by month?
// at the moment this only works for files that are already split by month
// TODO: check if this statement has already been imported, by the stmt.Filename; delete old statement
BankImportTDS MainDS = new BankImportTDS();
AEpStatementRow stmt = MainDS.AEpStatement.NewRowTyped();
stmt.StatementKey = -1;
// TODO: BankAccountKey should be NOT NULL. for the moment not time to implement
// stmt.BankAccountKey = Convert.ToInt64(TXMLParser.GetAttribute(RootNode, "BankAccountKey"));
stmt.Filename = Path.GetFileName(ABankStatementFilename.Replace('\\', Path.DirectorySeparatorChar));
// depending on the path of BankStatementFilename you could determine between several bank accounts
// search all config parameters starting with "BankNameFor",
// and see if the rest of the parameter name is part of the filename or path
StringCollection BankNameForParameters = TAppSettingsManager.GetKeys("BankNameFor");
foreach (string BankNameForParameter in BankNameForParameters)
{
if (stmt.Filename.ToLower().Contains(BankNameForParameter.Substring("BankNameFor".Length).ToLower()))
{
stmt.Filename = TAppSettingsManager.GetValue(BankNameForParameter);
}
}
if (stmt.Filename.Length > AEpStatementTable.GetFilenameLength())
{
// use the last number of characters of the path and filename
stmt.Filename = ABankStatementFilename.Substring(ABankStatementFilename.Length - AEpStatementTable.GetFilenameLength());
}
if (ACurrencyCode == String.Empty)
{
// get the currency code from the ledger, or from the bank account
TDataBase db = DBAccess.Connect("ImportBankStatementHelper_Currency");
TDBTransaction Transaction = new TDBTransaction();
db.ReadTransaction(ref Transaction,
delegate
{
AAccountTable AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, ABankAccountCode, Transaction);
if (AccountTable[0].ForeignCurrencyFlag)
{
ACurrencyCode = AccountTable[0].ForeignCurrencyCode;
}
else
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
ACurrencyCode = LedgerTable[0].BaseCurrency;
}
});
db.CloseDBConnection();
}
stmt.LedgerNumber = ALedgerNumber;
stmt.CurrencyCode = ACurrencyCode;
stmt.BankAccountCode = ABankAccountCode;
MainDS.AEpStatement.Rows.Add(stmt);
// allow the user to change the order&meaning of columns
string[] ColumnsUsage = AColumnsUsage.Split(new char[] { ',' });
Dictionary <DateTime, List <AEpTransactionRow> > TransactionsPerMonth = new Dictionary <DateTime, List <AEpTransactionRow> >();
bool startParsing = (AStartAfterLine == String.Empty);
AStartAfterLine = AStartAfterLine.Replace('"', '\'');
for (; lineCounter < StatementData.Count; lineCounter++)
{
string line = StatementData[lineCounter];
if (AStartAfterLine == line.Replace('"', '\''))
{
startParsing = true;
continue;
}
if (!startParsing)
{
continue;
}
AEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped();
row.StatementKey = stmt.StatementKey;
foreach (string UseAs in ColumnsUsage)
{
if (line == String.Empty)
{
// this line is too short, does not have enough columns.
// ignore this row.
row = null;
continue;
}
string Value = StringHelper.GetNextCSV(ref line, StatementData, ref lineCounter, ASeparator);
if (UseAs.ToLower() == "dateeffective")
{
if (Value.Length == "dd.mm.yy".Length)
{
DateFormat = DateFormat.Replace("yyyy", "yy");
}
try
{
row.DateEffective = XmlConvert.ToDateTime(Value, DateFormat);
}
catch (Exception)
{
TLogging.Log("Problem with date effective: " + Value + " (Format: " + DateFormat + ")");
}
}
else if (UseAs.ToLower() == "accountname")
{
if (row.AccountName.Length > 0)
{
row.AccountName += " ";
}
row.AccountName += Value;
}
else if (UseAs.ToLower() == "description")
{
// remove everything after DTA; it is not relevant and confused matching
if (Value.IndexOf(" DTA ") > 0)
{
Value = Value.Substring(0, Value.IndexOf(" DTA "));
}
if (row.Description.Length > 0)
{
row.Description += " ";
}
row.Description += Value;
}
else if (UseAs.ToLower() == "amount")
{
if (Value.Contains(" "))
{
// cut off currency code; should have been defined in the data description file, for the whole batch
Value = Value.Substring(0, Value.IndexOf(" ") - 1);
}
Value = Value.Replace(ThousandsSeparator, "");
Value = Value.Replace(DecimalSeparator, ".");
row.TransactionAmount = Convert.ToDecimal(Value, System.Globalization.CultureInfo.InvariantCulture);
}
else if (UseAs.ToLower() == "directdebiths")
{
if (Value == "S")
{
row.TransactionAmount *= -1;
}
}
else if (UseAs.ToLower() == "currency")
{
if (stmt.CurrencyCode == string.Empty)
{
stmt.CurrencyCode = Value.ToUpper();
}
else if (stmt.CurrencyCode != Value.ToUpper())
{
throw new Exception("cannot mix several currencies in the same bank statement file");
}
}
}
if (row == null)
{
// ignore this line
continue;
}
// all transactions with positive amount can be donations
if (row.TransactionAmount > 0)
{
row.TransactionTypeCode = MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
DateTime month = new DateTime(row.DateEffective.Year, row.DateEffective.Month, 1);
if (!TransactionsPerMonth.ContainsKey(month))
{
TransactionsPerMonth.Add(month, new List <AEpTransactionRow>());
}
TransactionsPerMonth[month].Add(row);
}
if (TransactionsPerMonth.Keys.Count == 0)
{
// cannot find any transactions
return(MainDS);
}
// now find the month that should be imported
DateTime MonthToBeImported = DateTime.MinValue;
foreach (DateTime month in TransactionsPerMonth.Keys)
{
if (MonthToBeImported == DateTime.MinValue)
{
MonthToBeImported = month;
}
else
{
if (TransactionsPerMonth[month].Count > TransactionsPerMonth[MonthToBeImported].Count)
{
MonthToBeImported = month;
}
}
}
DateTime latestDate = DateTime.MinValue;
Int32 rowCount = 0;
foreach (AEpTransactionRow row in TransactionsPerMonth[MonthToBeImported])
{
rowCount++;
row.Order = rowCount;
row.NumberOnPaperStatement = row.Order;
MainDS.AEpTransaction.Rows.Add(row);
if (row.DateEffective > latestDate)
{
latestDate = row.DateEffective;
}
}
stmt.Date = latestDate;
return(MainDS);
}
