private void RefreshBankAccountAndCostCentreData()
{
//Populate CostCentreList variable
DataTable costCentreList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref costCentreList, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)costCentreList;
//Populate AccountList variable
DataTable accountList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref accountList, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)accountList;
}
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
}
/// <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;
}
}
/// <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 void SetAccountCostCentreTableVariables()
{
//Populate CostCentreList variable
DataTable CostCentreListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref CostCentreListTable, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)CostCentreListTable;
//Populate AccountList variable
DataTable AccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref AccountListTable, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)AccountListTable;
}
/// <summary>
/// Call this to initialise the 'Lists' (tables) for the ComboBoxes
/// </summary>
/// <param name="ALoadAndFilterLogicObject">Supply a reference to the Filter Logic object because it needs a reference to the same Lists</param>
/// <param name="ACostCentreTab"></param>
/// <param name="AAccountTab"></param>
public void RefreshBankAccountAndCostCentreData(TUC_RecurringGiftBatches_LoadAndFilter ALoadAndFilterLogicObject,
out ACostCentreTable ACostCentreTab,
out AAccountTable AAccountTab)
{
//Populate CostCentreList variable
DataTable CostCentreList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable TmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(TmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref CostCentreList, FMainDS.Tables[TmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(TmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)CostCentreList;
ALoadAndFilterLogicObject.CostCentreTable = FCostCentreTable;
ACostCentreTab = FCostCentreTable;
//Populate AccountList variable
DataTable AccountList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable TmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(TmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref AccountList, FMainDS.Tables[TmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(TmpAccountTable.TableName);
FAccountTable = (AAccountTable)AccountList;
ALoadAndFilterLogicObject.AccountTable = FAccountTable;
AAccountTab = FAccountTable;
}
/// <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 = 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();
}
/// <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();
}
/// <summary>If this is a foreign currency supplier, it must be linked to accounts in that currency.
/// (And if it's not, it mustn't be!)</summary>
/// <param name="AccountRef"></param>
/// <param name="AccountType"></param>
/// <returns>true if the default back account is OK.</returns>
private bool ValidateAccountCurrency(string AccountRef, string AccountType)
{
bool CurrencyIsOk = true;
if (AccountRef == "") // I've not been given a default bank account. Perhaps this is OK?
{
return(CurrencyIsOk);
}
DataTable TempTbl = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable AccountList = (AAccountTable)TempTbl;
AccountList.DefaultView.RowFilter = String.Format("a_ledger_number_i={0} AND a_account_code_c='{1}'",
FLedgerNumber, AccountRef);
if (AccountList.DefaultView.Count == 1)
{
AAccountRow AccountDetail = (AAccountRow)AccountList.DefaultView[0].Row;
if (FMainDS.AApSupplier[0].CurrencyCode == FLedgerRow.BaseCurrency)
{
CurrencyIsOk = (AccountDetail.ForeignCurrencyFlag == false);
}
else
{
CurrencyIsOk =
((AccountDetail.ForeignCurrencyFlag == true) && (AccountDetail.ForeignCurrencyCode == FMainDS.AApSupplier[0].CurrencyCode));
}
if (!CurrencyIsOk)
{
MessageBox.Show(String.Format(Catalog.GetString("The {0} must be a {1} currency account."),
AccountType, FMainDS.AApSupplier[0].CurrencyCode), "Validation");
FMainDS.AApSupplier.Rows[0].EndEdit();
// This call isn't really useful, because although the user may go and create the required account,
// she won't have done so yet...
TDataCache.TMFinance.RefreshCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber); // scrub the cache so that I'll notice if the user makes a change
}
}
else
{
MessageBox.Show(String.Format(Catalog.GetString("Unable to access {0} account {1}"),
AccountType, AccountRef), "Error");
FMainDS.AApSupplier.Rows[0].EndEdit();
TDataCache.TMFinance.RefreshCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList); // scrub the cache - perhaps I'll get a different answer next time!
CurrencyIsOk = false;
}
return(CurrencyIsOk);
}
// 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>
/// Add columns that were created and are not part of the normal ASuspenseAccount
/// </summary>
private void AddSpecialColumns()
{
if (FDescriptionColumn == null)
{
FDescriptionColumn = new DataColumn();
FDescriptionColumn.DataType = System.Type.GetType("System.String");
FDescriptionColumn.ColumnName = "Parent_" + AAccountTable.GetAccountCodeShortDescDBName();
FDescriptionColumn.Expression = "Parent." + AAccountTable.GetAccountCodeShortDescDBName();
}
if (!FMainDS.ASuspenseAccount.Columns.Contains(FDescriptionColumn.ColumnName))
{
FMainDS.ASuspenseAccount.Columns.Add(FDescriptionColumn);
}
}
private void InitializeManualCode()
{
// Hook up DataSavingStarted Event to be able to run code before SaveChanges is doing anything
FPetraUtilsObject.DataSavingStarted += new TDataSavingStartHandler(this.DataSavingStarted);
// Hook up DataSaved Event to be able to run code after SaveChanges is finished
FPetraUtilsObject.DataSaved += new TDataSavedHandler(this.OnDataSaved);
// add column for account description
AddSpecialColumns();
grdDetails.Columns.Clear();
grdDetails.AddTextColumn("Account Code", FMainDS.ASuspenseAccount.ColumnSuspenseAccountCode);
grdDetails.AddTextColumn("Description",
FMainDS.ASuspenseAccount.Columns["Parent_" + AAccountTable.GetAccountCodeShortDescDBName()]);
}
/// <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;
}
private void CheckIfRevaluationIsDone()
{
if (!FInfoMode)
{
return;
}
/*
* I'm no longer looking at this flag,
* since it can be set even though some accounts are left requiring revaluation.
* See Mantis 0004059
*
* if ((new TLedgerInitFlagHandler(FledgerInfo.LedgerNumber,
* TLedgerInitFlagEnum.Revaluation).Flag))
* {
* return; // Revaluation has been performed for the current period.
* }
*/
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
// TODO: could also check for the balance in this month of the foreign currency account. if all balances are zero, no revaluation is needed.
string testForForeignKeyAccount =
String.Format("SELECT COUNT(*) FROM PUB_a_account WHERE {0} = {1} and {2} = true",
AAccountTable.GetLedgerNumberDBName(),
FledgerInfo.LedgerNumber,
AAccountTable.GetForeignCurrencyFlagDBName());
Int32 ForeignAccountCount = Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar(testForForeignKeyAccount, Transaction));
if (ForeignAccountCount > 0)
{
TVerificationResult tvr = new TVerificationResult(
Catalog.GetString("Currency revaluation"),
Catalog.GetString(
"Before proceeding you may want to revalue the foreign currency accounts."), "",
TPeriodEndErrorAndStatusCodes.PEEC_05.ToString(), TResultSeverity.Resv_Noncritical);
// Error is non-critical - the user can choose to continue.
FverificationResults.Add(tvr);
}
}); // Get NewOrExisting AutoReadTransaction
}
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();
}
private void CheckIfRevaluationIsDone()
{
if ((new TLedgerInitFlagHandler(FledgerInfo.LedgerNumber,
TLedgerInitFlagEnum.Revaluation).Flag))
{
return; // Revaluation has been performed for the current period.
}
bool NewTransaction = false;
TDBTransaction transaction;
try
{
transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// TODO: could also check for the balance in this month of the foreign currency account. if all balances are zero, no revaluation is needed.
string testForForeignKeyAccount =
String.Format("SELECT COUNT(*) FROM PUB_a_account WHERE {0} = {1} and {2} = true",
AAccountTable.GetLedgerNumberDBName(),
FledgerInfo.LedgerNumber,
AAccountTable.GetForeignCurrencyFlagDBName());
if (Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar(testForForeignKeyAccount, transaction)) != 0)
{
TVerificationResult tvr = new TVerificationResult(
Catalog.GetString("Ledger revaluation"),
Catalog.GetString("Please run a foreign currency revaluation first."), "",
TPeriodEndErrorAndStatusCodes.PEEC_05.ToString(), TResultSeverity.Resv_Critical);
// Error is critical but additional checks can still be done
FverificationResults.Add(tvr);
FHasCriticalErrors = true;
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
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);
}
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>
/// Check if a given account is active
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AAccountCode"></param>
/// <param name="AAccountList"></param>
/// <param name="AAccountExists"></param>
/// <returns></returns>
public static bool AccountIsActive(Int32 ALedgerNumber,
string AAccountCode,
AAccountTable AAccountList,
out bool AAccountExists)
{
AAccountExists = false;
bool RetVal = false;
AAccountRow CurrentAccountRow = null;
if (AAccountList != null)
{
CurrentAccountRow = (AAccountRow)AAccountList.Rows.Find(new object[] { ALedgerNumber, AAccountCode });
if (CurrentAccountRow != null)
{
AAccountExists = true;
RetVal = CurrentAccountRow.AccountActiveFlag;
}
}
return RetVal;
}
/// <summary>
/// Method to check and, if possible fix, codes that have had leading zeros removed by Excel for example.
/// </summary>
/// <param name="ALedgerNumber">The ledger number</param>
/// <param name="ARowNumber">The current row number</param>
/// <param name="AAccountCode">The account code that may get changed</param>
/// <param name="AAccountTableRef">The account table that will be checked for valid codes</param>
/// <param name="ACostCentreCode">The cost centre code that may get changed</param>
/// <param name="ACostCentreTableRef">The cost centre table that will be checked for valid codes</param>
/// <param name="AMessages">A message collection. If a change is made a 'Information' (non-critical) message will be added to the collection</param>
public static void FixAccountCodes(int ALedgerNumber, int ARowNumber, ref string AAccountCode, AAccountTable AAccountTableRef,
ref string ACostCentreCode, ACostCentreTable ACostCentreTableRef, TVerificationResultCollection AMessages)
{
// Start with the Account code
string code = ConvertTo4DigitCode(AAccountCode);
if (code != AAccountCode)
{
// Maybe it is wrong?
if (AAccountTableRef.Rows.Find(new object[] { ALedgerNumber, AAccountCode }) == null)
{
// That one does not exist, so try our new 4 digit one
if (AAccountTableRef.Rows.Find(new object[] { ALedgerNumber, code }) != null)
{
// Swap the short code for the longer one
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString(
"The account code '{0}' does not exist. The code has been converted to '{1}' (which does exist). Please check that this is what you intended."),
AAccountCode, code),
TResultSeverity.Resv_Noncritical));
AAccountCode = code;
}
}
}
// Do the same for the cost centre code
code = ConvertTo4DigitCode(ACostCentreCode);
if (code != ACostCentreCode)
{
// Maybe it is wrong?
if (ACostCentreTableRef.Rows.Find(new object[] { ALedgerNumber, ACostCentreCode }) == null)
{
// That one does not exist, so try our new 4 digit one
if (ACostCentreTableRef.Rows.Find(new object[] { ALedgerNumber, code }) != null)
{
// Swap the short code for the longer one
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString(
"The cost centre code '{0}' does not exist. The code has been converted to '{1}' (which does exist). Please check that this is what you intended."),
ACostCentreCode, code),
TResultSeverity.Resv_Noncritical));
ACostCentreCode = code;
}
}
}
}
/// <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>
/// 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);
}
private void SetupExtraGridFunctionality()
{
//Populate CostCentreList variable
DataTable costCentreList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref costCentreList, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)costCentreList;
//Populate AccountList variable
DataTable accountList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref accountList, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)accountList;
//Prepare grid to highlight inactive accounts/cost centres
// Create a cell view for special conditions
SourceGrid.Cells.Views.Cell strikeoutCell = new SourceGrid.Cells.Views.Cell();
strikeoutCell.Font = new System.Drawing.Font(grdDetails.Font, FontStyle.Strikeout);
//strikeoutCell.ForeColor = Color.Crimson;
// Create a condition, apply the view when true, and assign a delegate to handle it
SourceGrid.Conditions.ConditionView conditionAccountCodeActive = new SourceGrid.Conditions.ConditionView(strikeoutCell);
conditionAccountCodeActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column, int gridRow, object itemRow)
{
DataRowView row = (DataRowView)itemRow;
string accountCode = row[ABudgetTable.ColumnAccountCodeId].ToString();
return !AccountIsActive(accountCode);
};
SourceGrid.Conditions.ConditionView conditionCostCentreCodeActive = new SourceGrid.Conditions.ConditionView(strikeoutCell);
conditionCostCentreCodeActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column, int gridRow, object itemRow)
{
DataRowView row = (DataRowView)itemRow;
string costCentreCode = row[ABudgetTable.ColumnCostCentreCodeId].ToString();
return !CostCentreIsActive(costCentreCode);
};
//Add conditions to columns
int indexOfCostCentreCodeDataColumn = 0;
int indexOfAccountCodeDataColumn = 1;
grdDetails.Columns[indexOfCostCentreCodeDataColumn].Conditions.Add(conditionCostCentreCodeActive);
grdDetails.Columns[indexOfAccountCodeDataColumn].Conditions.Add(conditionAccountCodeActive);
}
/// <summary>
/// Validates the GL Detail data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ABatchRow">Manually added to bring over some GL Batch fields</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="AValidationCostCentreTable">REQUIRED for importing. A reference to a cost centre table so that inputs can be validated.</param>
/// <param name="AvalidationAccountTable">REQUIRED for importing. A reference to an account table so that inputs can be validated.</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGLDetailManual(object AContext, ABatchRow ABatchRow, ATransactionRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
ACostCentreTable AValidationCostCentreTable = null, AAccountTable AvalidationAccountTable = null)
{
DataColumn ValidationColumn;
TVerificationResult VerificationResult = null;
object ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted DataRows or non-unposted batches
if ((ABatchRow.BatchStatus != MFinanceConstants.BATCH_UNPOSTED) || (ARow.RowState == DataRowState.Deleted))
{
return(true);
}
bool isImporting = AContext.ToString().Contains("Importing");
// When used by the GUI TransactionAmount is not in the dictionary so had to pass the control directly
// But when importing we do have a dictionary entry
if (isImporting)
{
// 'GL amount must be non-zero and positive
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionAmountId];
if (true)
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.TransactionAmount,
String.Empty,
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
}
}
else
{
if (true)
{
// 'GL amount must be non-zero and positive
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionAmountId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
VerificationResult = TNumericalChecks.IsPositiveOrZeroDecimal(ARow.TransactionAmount,
"Amount of " + ValidationContext,
AContext, ValidationColumn);
if (VerificationResult != null)
{
VerificationResult.SuppressValidationToolTip = true;
}
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
return(VerifResultCollAddedCount == 0);
}
}
// 'Narrative must not be empty
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnNarrativeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (true)
{
VerificationResult = TStringChecks.StringMustNotBeEmpty(ARow.Narrative,
(isImporting) ? String.Empty : "Narrative of " + ValidationContext,
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
}
// 'Entered From Date' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionDateId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (true)
{
DateTime StartDatePeriod;
DateTime EndDatePeriod;
TSharedFinanceValidationHelper.GetValidPeriodDates(ARow.LedgerNumber, ABatchRow.BatchYear, 0, ABatchRow.BatchPeriod,
out StartDatePeriod,
out EndDatePeriod);
VerificationResult = (TScreenVerificationResult)TDateChecks.IsDateBetweenDates(ARow.TransactionDate,
StartDatePeriod,
EndDatePeriod,
(isImporting) ? String.Empty : "Transaction Date for " + ValidationContext.ToString(),
TDateBetweenDatesCheckType.dbdctUnspecific,
TDateBetweenDatesCheckType.dbdctUnspecific,
AContext,
ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
}
if (true)
{
// "Reference" is mandatory
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnReferenceId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (true)
{
VerificationResult = TStringChecks.StringMustNotBeEmpty(ARow.Reference,
(isImporting) ? String.Empty : "Reference of " + ValidationContext,
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
}
}
// 'CostCentre' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnCostCentreCodeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (true)
{
if ((AValidationCostCentreTable != null) && !ARow.IsCostCentreCodeNull())
{
// Code must exist in the cost centre table
ACostCentreRow foundRow = (ACostCentreRow)AValidationCostCentreTable.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.CostCentreCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Cost centre code '{0}' does not exist."), ARow.CostCentreCode),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
// cost centre must be a posting cost centre
if ((foundRow != null) && !foundRow.PostingCostCentreFlag)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Cost centre code '{0}' is not a posting cost centre."), ARow.CostCentreCode),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
//TODO: Maybe add a user preference to determine what to do with inactive values on importing
// cost centre must not be inactive
//if ((foundRow != null) && !foundRow.CostCentreActiveFlag)
//{
// if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
// new TVerificationResult(ValidationContext,
// String.Format(Catalog.GetString("Cost centre code '{0}' is an inactive cost centre."), ARow.CostCentreCode),
// TResultSeverity.Resv_Critical),
// ValidationColumn))
// {
// VerifResultCollAddedCount++;
// }
//}
}
}
// 'Account code' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnAccountCodeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (true)
{
if ((AvalidationAccountTable != null) && !ARow.IsAccountCodeNull())
{
// Code must exist in the account table
AAccountRow foundRow = (AAccountRow)AvalidationAccountTable.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.AccountCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Account code '{0}' does not exist."), ARow.AccountCode),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
// Account code must be a posting Account code
if ((foundRow != null) && !foundRow.PostingStatus)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Account code '{0}' is not a posting account."), ARow.AccountCode),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
//TODO: Maybe add a user preference to determine what to do with inactive values on importing
// Account code must not be inactive
//if ((foundRow != null) && !foundRow.AccountActiveFlag)
//{
// if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
// new TVerificationResult(ValidationContext,
// String.Format(Catalog.GetString("Account code '{0}' is an inactive account."), ARow.AccountCode),
// TResultSeverity.Resv_Critical),
// ValidationColumn))
// {
// VerifResultCollAddedCount++;
// }
//}
}
}
return(VerifResultCollAddedCount == 0);
}
public static DataTable AFOTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapte)
{
TDBTransaction Transaction = null;
AAccountTable AccountTable = new AAccountTable();
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string StandardSummaryCostCentre = LedgerNumber.ToString("00") + "00S";
string AccountHierarchyCode = AParameters["param_account_hierarchy_c"].ToString();
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_short_desc_c", typeof(string)));
Results.Columns.Add(new DataColumn("DebitCreditIndicator", typeof(bool)));
Results.Columns.Add(new DataColumn("ActualDebitBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualDebitIntl", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditIntl", typeof(Decimal)));
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
ALedgerRow LedgerRow = (ALedgerRow)ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction).Rows[0];
AAccountHierarchyRow HierarchyRow = (AAccountHierarchyRow)AAccountHierarchyAccess.LoadByPrimaryKey(
LedgerNumber, AccountHierarchyCode, Transaction).Rows[0];
List <String>list = new List <string>();
ScanHierarchy(ref list, LedgerNumber, HierarchyRow.RootAccountCode, AccountHierarchyCode, Transaction);
// get AAccountRows for each account number in list
foreach (string AccountCode in list)
{
AAccountRow AddRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(LedgerNumber, AccountCode, Transaction).Rows[0];
if (AddRow != null)
{
AccountTable.Rows.Add((object[])AddRow.ItemArray.Clone());
}
}
// Populate the Results Dataset
foreach (AAccountRow Account in AccountTable.Rows)
{
DataRow NewRow = Results.NewRow();
NewRow["a_account_code_c"] = Account.AccountCode;
NewRow["a_account_code_short_desc_c"] = Account.AccountCodeShortDesc;
int GLMSequence = TCommonBudgetMaintain.GetGLMSequenceForBudget(
LedgerNumber, Account.AccountCode, StandardSummaryCostCentre, AParameters["param_year_i"].ToInt32());
// get balances
if (Account.DebitCreditIndicator)
{
NewRow["DebitCreditIndicator"] = true;
NewRow["ActualDebitBase"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_BASE);
NewRow["ActualDebitIntl"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_INTERNATIONAL);
}
else
{
NewRow["DebitCreditIndicator"] = false;
NewRow["ActualCreditBase"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_BASE);
NewRow["ActualCreditIntl"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_INTERNATIONAL);
}
Results.Rows.Add(NewRow);
}
});
return Results;
}
private void SetAccountCostCentreTableVariables()
{
//Populate CostCentreList variable
DataTable CostCentreListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref CostCentreListTable, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)CostCentreListTable;
//Populate AccountList variable
DataTable AccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref AccountListTable, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)AccountListTable;
}
public static DataTable AFOTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
AAccountTable AccountTable = new AAccountTable();
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string StandardSummaryCostCentre = LedgerNumber.ToString("00") + "00S";
Int32 Year = AParameters["param_year_i"].ToInt32();
Int32 Period = AParameters["param_end_period_i"].ToInt32();
string AccountHierarchyCode = AParameters["param_account_hierarchy_c"].ToString();
String CostCentreFilter = GetReportingCostCentres(LedgerNumber, StandardSummaryCostCentre, "");
CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreFilter.Replace(",", "','") + "') ";
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_short_desc_c", typeof(string)));
Results.Columns.Add(new DataColumn("DebitCreditIndicator", typeof(bool)));
Results.Columns.Add(new DataColumn("ActualDebitBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualDebitIntl", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditIntl", typeof(Decimal)));
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerRow LedgerRow = (ALedgerRow)ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction).Rows[0];
AAccountHierarchyRow HierarchyRow = (AAccountHierarchyRow)AAccountHierarchyAccess.LoadByPrimaryKey(
LedgerNumber, AccountHierarchyCode, Transaction).Rows[0];
List <String>list = new List <string>();
ScanHierarchy(ref list, LedgerNumber, HierarchyRow.RootAccountCode, AccountHierarchyCode, Transaction);
// get AAccountRows for each account number in list
foreach (string AccountCode in list)
{
AAccountRow AddRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(LedgerNumber, AccountCode, Transaction).Rows[0];
if (AddRow != null)
{
AccountTable.Rows.Add((object[])AddRow.ItemArray.Clone());
}
}
// Populate the Results Dataset
foreach (AAccountRow Account in AccountTable.Rows)
{
String ActualField = Account.DebitCreditIndicator ? "ActualDebitBase" : "ActualCreditBase";
String IntlField = Account.DebitCreditIndicator ? "ActualDebitIntl" : "ActualCreditIntl";
String AccountFilter = GetReportingAccounts(LedgerNumber, Account.AccountCode, "");
AccountFilter = " AND glm.a_account_code_c IN ('" + AccountFilter.Replace(",",
"','") + "')";
String subtractOrAddBase = (Account.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Base ELSE 0-Base END"
:
"CASE WHEN debit=TRUE THEN 0-Base ELSE Base END";
String subtractOrAddIntl = (Account.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Intl ELSE 0-Intl END"
:
"CASE WHEN debit=TRUE THEN 0-Intl ELSE Intl END";
String Query =
"SELECT sum(" + subtractOrAddBase + ") AS Actual," +
" sum(" + subtractOrAddIntl + ") AS ActualIntl" +
" FROM" +
" (SELECT DISTINCT a_account.a_account_code_c AS AccountCode, glm.a_cost_centre_code_c AS CostCentreCode, a_account.a_debit_credit_indicator_l AS debit,"
+
" glmp.a_actual_base_n AS Base, glmp.a_actual_intl_n AS Intl" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account" +
" WHERE glm.a_glm_sequence_i=glmp.a_glm_sequence_i" +
" AND glm.a_account_code_c=a_account.a_account_code_c" +
" AND a_account.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_year_i=" + Year +
" AND glmp.a_period_number_i=" + Period +
AccountFilter +
CostCentreFilter +
") AS AllGlm";
DataTable tempTable = DbAdapter.RunQuery(Query, "AFO", Transaction);
DataRow NewRow = Results.NewRow();
NewRow["a_account_code_c"] = Account.AccountCode;
NewRow["a_account_code_short_desc_c"] = Account.AccountCodeShortDesc;
NewRow["DebitCreditIndicator"] = Account.DebitCreditIndicator;
NewRow[ActualField] = tempTable.Rows[0]["Actual"];
NewRow[IntlField] = tempTable.Rows[0]["ActualIntl"];
Results.Rows.Add(NewRow);
}
}); // Get NewOrExisting AutoReadTransaction
return Results;
} // AFO Table
/// <summary>
/// Validates the GL Detail data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ABatchRow">Manually added to bring over some GL Batch fields</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AControl"></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="AValidationCostCentreTable">REQUIRED for importing. A reference to a cost centre table so that inputs can be validated.</param>
/// <param name="AvalidationAccountTable">REQUIRED for importing. A reference to an account table so that inputs can be validated.</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGLDetailManual(object AContext, ABatchRow ABatchRow, ATransactionRow ARow, Control AControl,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict,
ACostCentreTable AValidationCostCentreTable = null, AAccountTable AvalidationAccountTable = null)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult = null;
object ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted DataRows or non-unposted batches
if ((ABatchRow.BatchStatus != MFinanceConstants.BATCH_UNPOSTED) || (ARow.RowState == DataRowState.Deleted))
{
return true;
}
bool isImporting = AContext.ToString().Contains("Importing");
// When used by the GUI TransactionAmount is not in the dictionary so had to pass the control directly
// But when importing we do have a dictionary entry
if (isImporting)
{
// 'GL amount must be non-zero and positive
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionAmountId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.TransactionAmount,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, AControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
}
else
{
if ((AControl != null) && AControl.Name.EndsWith("Amount"))
{
// 'GL amount must be non-zero and positive
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionAmountId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
VerificationResult = TNumericalChecks.IsPositiveOrZeroDecimal(ARow.TransactionAmount,
"Amount of " + ValidationContext,
AContext, ValidationColumn, AControl);
if (VerificationResult != null)
{
VerificationResult.SuppressValidationToolTip = true;
}
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
return VerifResultCollAddedCount == 0;
}
}
// 'Narrative must not be empty
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnNarrativeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TStringChecks.StringMustNotBeEmpty(ARow.Narrative,
(isImporting) ? ValidationControlsData.ValidationControlLabel : "Narrative of " + ValidationContext,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// 'Entered From Date' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnTransactionDateId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
DateTime StartDatePeriod;
DateTime EndDatePeriod;
TSharedFinanceValidationHelper.GetValidPeriodDates(ARow.LedgerNumber, ABatchRow.BatchYear, 0, ABatchRow.BatchPeriod,
out StartDatePeriod,
out EndDatePeriod);
VerificationResult = (TScreenVerificationResult)TDateChecks.IsDateBetweenDates(ARow.TransactionDate,
StartDatePeriod,
EndDatePeriod,
(isImporting) ? ValidationControlsData.ValidationControlLabel : "Transaction Date for " + 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 ((AControl != null) && AControl.Name.EndsWith("Reference"))
{
//TODO: Check if "Reference" is mandatory then make sure it is set
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnReferenceId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TStringChecks.StringMustNotBeEmpty(ARow.Reference,
(isImporting) ? ValidationControlsData.ValidationControlLabel : "Reference of " + ValidationContext,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
}
// 'CostCentre' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnCostCentreCodeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if ((AValidationCostCentreTable != null) && !ARow.IsCostCentreCodeNull())
{
// Code must exist in the cost centre table
ACostCentreRow foundRow = (ACostCentreRow)AValidationCostCentreTable.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.CostCentreCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Cost centre code '{0}' does not exist."), ARow.CostCentreCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// cost centre must be a posting cost centre
if ((foundRow != null) && !foundRow.PostingCostCentreFlag)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Cost centre code '{0}' is not a posting cost centre."), ARow.CostCentreCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// cost centre must not be inactive
if ((foundRow != null) && !foundRow.CostCentreActiveFlag)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Cost centre code '{0}' is an inactive cost centre."), ARow.CostCentreCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// 'Account code' must be valid
ValidationColumn = ARow.Table.Columns[ATransactionTable.ColumnAccountCodeId];
ValidationContext = String.Format("Transaction number {0} (batch:{1} journal:{2})",
ARow.TransactionNumber,
ARow.BatchNumber,
ARow.JournalNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if ((AvalidationAccountTable != null) && !ARow.IsAccountCodeNull())
{
// Code must exist in the account table
AAccountRow foundRow = (AAccountRow)AvalidationAccountTable.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.AccountCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Account code '{0}' does not exist."), ARow.AccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// Account code must be a posting Account code
if ((foundRow != null) && !foundRow.PostingStatus)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Account code '{0}' is not a posting account."), ARow.AccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// Account code must not be inactive
if ((foundRow != null) && !foundRow.AccountActiveFlag)
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(ValidationContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Account code '{0}' is an inactive account."), ARow.AccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
return VerifResultCollAddedCount == 0;
}
public static GLSetupTDS LoadLedgerSettings(Int32 ALedgerNumber, out DateTime ACalendarStartDate,
out bool ACurrencyChangeAllowed, out bool ACalendarChangeAllowed)
{
ACalendarStartDate = DateTime.MinValue;
ACurrencyChangeAllowed = false;
ACalendarChangeAllowed = false;
DateTime CalendarStartDate = ACalendarStartDate;
bool CurrencyChangeAllowed = ACurrencyChangeAllowed;
GLSetupTDS MainDS = new GLSetupTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
AAccountingSystemParameterAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
ALedgerInitFlagAccess.LoadViaALedger(MainDS, ALedgerNumber, null, Transaction);
// retrieve calendar start date (start date of financial year)
AAccountingPeriodTable CalendarTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction);
if (CalendarTable.Count > 0)
{
CalendarStartDate = ((AAccountingPeriodRow)CalendarTable.Rows[0]).PeriodStartDate;
}
// now check if currency change would be allowed
CurrencyChangeAllowed = true;
if ((AJournalAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
|| (AGiftBatchAccess.CountViaALedger(ALedgerNumber, Transaction) > 0))
{
// don't allow currency change if journals or gift batches exist
CurrencyChangeAllowed = false;
}
if (AGiftBatchAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
{
// don't allow currency change if journals exist
CurrencyChangeAllowed = false;
}
if (CurrencyChangeAllowed)
{
// don't allow currency change if there are foreign currency accounts for this ledger
AAccountTable TemplateTable;
AAccountRow TemplateRow;
StringCollection TemplateOperators;
TemplateTable = new AAccountTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.ForeignCurrencyFlag = true;
TemplateOperators = new StringCollection();
TemplateOperators.Add("=");
if (AAccountAccess.CountUsingTemplate(TemplateRow, TemplateOperators, Transaction) > 0)
{
CurrencyChangeAllowed = false;
}
}
});
ACalendarStartDate = CalendarStartDate;
ACurrencyChangeAllowed = CurrencyChangeAllowed;
// now check if calendar change would be allowed
ACalendarChangeAllowed = IsCalendarChangeAllowed(ALedgerNumber);
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return MainDS;
}
/// <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);
}
public static GLSetupTDS LoadLedgerSettings(Int32 ALedgerNumber, out DateTime ACalendarStartDate,
out bool ACurrencyChangeAllowed, out bool ACalendarChangeAllowed)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format("Function:{0} - Ledger number must be greater than 0",
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
ACalendarStartDate = DateTime.MinValue;
ACurrencyChangeAllowed = false;
ACalendarChangeAllowed = false;
DateTime CalendarStartDate = ACalendarStartDate;
bool CurrencyChangeAllowed = ACurrencyChangeAllowed;
GLSetupTDS MainDS = new GLSetupTDS();
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
AAccountingSystemParameterAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
ALedgerInitFlagAccess.LoadViaALedger(MainDS, ALedgerNumber, null, Transaction);
#region Validate Data
//ALedgerInitFlag is optional so no need to check
//TODO confirm this
if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Ledger Data for Ledger number {0} does not exist!"), ALedgerNumber));
}
else if ((MainDS.AAccountingSystemParameter == null) || (MainDS.AAccountingSystemParameter.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"AccountingSystemParameter Data for Ledger number {0} does not exist!"), ALedgerNumber));
}
#endregion Validate Data
// retrieve calendar start date (start date of financial year)
AAccountingPeriodTable CalendarTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction);
if (CalendarTable.Count > 0)
{
CalendarStartDate = ((AAccountingPeriodRow)CalendarTable.Rows[0]).PeriodStartDate;
}
// now check if currency change would be allowed
CurrencyChangeAllowed = true;
if ((AJournalAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
|| (AGiftBatchAccess.CountViaALedger(ALedgerNumber, Transaction) > 0))
{
// don't allow currency change if journals or gift batches exist
CurrencyChangeAllowed = false;
}
if (AGiftBatchAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
{
// don't allow currency change if journals exist
CurrencyChangeAllowed = false;
}
if (CurrencyChangeAllowed)
{
// don't allow currency change if there are foreign currency accounts for this ledger
AAccountTable TemplateTable;
AAccountRow TemplateRow;
StringCollection TemplateOperators;
TemplateTable = new AAccountTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.ForeignCurrencyFlag = true;
TemplateOperators = new StringCollection();
TemplateOperators.Add("=");
if (AAccountAccess.CountUsingTemplate(TemplateRow, TemplateOperators, Transaction) > 0)
{
CurrencyChangeAllowed = false;
}
}
});
ACalendarStartDate = CalendarStartDate;
ACurrencyChangeAllowed = CurrencyChangeAllowed;
// now check if calendar change would be allowed
ACalendarChangeAllowed = IsCalendarChangeAllowed(ALedgerNumber);
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
}
catch (EFinanceSystemDataTableReturnedNoDataException ex)
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format("Function:{0} - {1}",
Utilities.GetMethodName(true),
ex.Message));
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return MainDS;
}
/// <summary>
/// Call this to initialise the 'Lists' (tables) for the ComboBoxes
/// </summary>
/// <param name="ALoadAndFilterLogicObject">Supply a reference to the Filter Logic object because it needs a reference to the same Lists</param>
public void RefreshBankAccountAndCostCentreData(TUC_GiftBatches_LoadAndFilter ALoadAndFilterLogicObject)
{
//Populate CostCentreList variable
DataTable costCentreList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref costCentreList, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)costCentreList;
ALoadAndFilterLogicObject.CostCentreTable = FCostCentreTable;
//Populate AccountList variable
DataTable accountList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref accountList, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)accountList;
ALoadAndFilterLogicObject.AccountTable = FAccountTable;
}
/// <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();
}
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;
}
private void RefreshBankAccountAndCostCentreData()
{
//Populate CostCentreList variable
DataTable costCentreList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList,
FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref costCentreList, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
FCostCentreTable = (ACostCentreTable)costCentreList;
//Populate AccountList variable
DataTable accountList = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref accountList, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
FAccountTable = (AAccountTable)accountList;
}
/// <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,
SortedList <string, string>ATaxAnalysisAttributes,
ref List <string>ACostCentresInvolved,
ref List <string>AAccountsInvolved)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
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(",", "','") + "'");
ATransactionTable transactions = new ATransactionTable();
transactions = (ATransactionTable)DBAccess.GDBAccessObj.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());
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
DBAccess.GDBAccessObj.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");
ATransactionTable allTransactionsInJournal = new ATransactionTable();
allTransactionsInJournal = (ATransactionTable)DBAccess.GDBAccessObj.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);
AGiftBatchTable giftbatches = new AGiftBatchTable();
DBAccess.GDBAccessObj.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);
AAccountTable accounts = new AAccountTable();
DBAccess.GDBAccessObj.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
DBAccess.GDBAccessObj.RollbackTransaction();
int rowCounter = 0;
StringCollection costcentreCollection = StringHelper.StrSplit(ACostCentres, ",");
foreach (ATransactionRow row in transactions.Rows)
{
if (row.DebitCreditIndicator)
{
row.TransactionAmount *= -1.0m;
}
StringBuilder attributes = new StringBuilder();
DataRowView[] attribs = TransAnalAttrib.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber, row.TransactionNumber });
decimal TaxOnIncome = 0.0m;
decimal TaxOnExpense = 0.0m;
// only mention tax codes, if this transaction was against a costcentre that has to pay taxes
if (costcentreCollection.Contains(row.CostCentreCode))
{
foreach (DataRowView rv in attribs)
{
ATransAnalAttribRow attribRow = (ATransAnalAttribRow)rv.Row;
// also export attribRow.AnalysisTypeCode?
attributes.Append(attribRow.AnalysisAttributeValue);
if (attribRow.AnalysisAttributeValue == "v19")
{
TaxOnExpense = row.TransactionAmount * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "v7")
{
TaxOnExpense = row.TransactionAmount * 0.07m;
}
else if (attribRow.AnalysisAttributeValue == "70v7")
{
TaxOnExpense = row.TransactionAmount * 0.7m * 0.07m;
}
else if (attribRow.AnalysisAttributeValue == "70v19")
{
TaxOnExpense = row.TransactionAmount * 0.7m * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "m19")
{
TaxOnIncome = row.TransactionAmount * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "m7")
{
TaxOnIncome = row.TransactionAmount * 0.07m;
}
}
}
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(),
TaxOnIncome.ToString(),
TaxOnExpense.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();
}
private void SetupExtraGridFunctionality()
{
DataTable CostCentreListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList, FLedgerNumber);
ACostCentreTable tmpCostCentreTable = new ACostCentreTable();
FMainDS.Tables.Add(tmpCostCentreTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref CostCentreListTable, FMainDS.Tables[tmpCostCentreTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpCostCentreTable.TableName);
if ((CostCentreListTable == null) || (CostCentreListTable.Rows.Count == 0))
{
FCostCentreList = null;
}
else
{
FCostCentreList = (ACostCentreTable)CostCentreListTable;
}
DataTable AccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, FLedgerNumber);
AAccountTable tmpAccountTable = new AAccountTable();
FMainDS.Tables.Add(tmpAccountTable);
DataUtilities.ChangeDataTableToTypedDataTable(ref AccountListTable, FMainDS.Tables[tmpAccountTable.TableName].GetType(), "");
FMainDS.RemoveTable(tmpAccountTable.TableName);
if ((AccountListTable == null) || (AccountListTable.Rows.Count == 0))
{
FAccountList = null;
}
else
{
FAccountList = (AAccountTable)AccountListTable;
}
//Prepare grid to highlight inactive accounts/cost centres
// Create a cell view for special conditions
SourceGrid.Cells.Views.Cell strikeoutCell = new SourceGrid.Cells.Views.Cell();
strikeoutCell.Font = new System.Drawing.Font(grdDetails.Font, FontStyle.Strikeout);
//strikeoutCell.ForeColor = Color.Crimson;
// Create a condition, apply the view when true, and assign a delegate to handle it
SourceGrid.Conditions.ConditionView conditionAccountCodeActive = new SourceGrid.Conditions.ConditionView(strikeoutCell);
conditionAccountCodeActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column, int gridRow, object itemRow)
{
DataRowView row = (DataRowView)itemRow;
string accountCode = row[ATransactionTable.ColumnAccountCodeId].ToString();
return !AccountIsActive(accountCode);
};
SourceGrid.Conditions.ConditionView conditionCostCentreCodeActive = new SourceGrid.Conditions.ConditionView(strikeoutCell);
conditionCostCentreCodeActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column, int gridRow, object itemRow)
{
DataRowView row = (DataRowView)itemRow;
string costCentreCode = row[ATransactionTable.ColumnCostCentreCodeId].ToString();
return !CostCentreIsActive(costCentreCode);
};
//Add conditions to columns
int indexOfCostCentreCodeDataColumn = 2;
int indexOfAccountCodeDataColumn = 3;
grdDetails.Columns[indexOfCostCentreCodeDataColumn].Conditions.Add(conditionCostCentreCodeActive);
grdDetails.Columns[indexOfAccountCodeDataColumn].Conditions.Add(conditionAccountCodeActive);
//Prepare Analysis attributes grid to highlight inactive analysis codes
// Create a cell view for special conditions
SourceGrid.Cells.Views.Cell strikeoutCell2 = new SourceGrid.Cells.Views.Cell();
strikeoutCell2.Font = new System.Drawing.Font(grdAnalAttributes.Font, FontStyle.Strikeout);
//strikeoutCell.ForeColor = Color.Crimson;
// Create a condition, apply the view when true, and assign a delegate to handle it
SourceGrid.Conditions.ConditionView conditionAnalysisCodeActive = new SourceGrid.Conditions.ConditionView(strikeoutCell2);
conditionAnalysisCodeActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column2, int gridRow2, object itemRow2)
{
DataRowView row2 = (DataRowView)itemRow2;
string analysisCode = row2[ATransAnalAttribTable.ColumnAnalysisTypeCodeId].ToString();
return !FAnalysisAttributesLogic.AnalysisCodeIsActive(
cmbDetailAccountCode.GetSelectedString(), FCacheDS.AAnalysisAttribute, analysisCode);
};
// Create a condition, apply the view when true, and assign a delegate to handle it
SourceGrid.Conditions.ConditionView conditionAnalysisAttributeValueActive = new SourceGrid.Conditions.ConditionView(strikeoutCell2);
conditionAnalysisAttributeValueActive.EvaluateFunction = delegate(SourceGrid.DataGridColumn column2, int gridRow2, object itemRow2)
{
if (itemRow2 != null)
{
DataRowView row2 = (DataRowView)itemRow2;
string analysisCode = row2[ATransAnalAttribTable.ColumnAnalysisTypeCodeId].ToString();
string analysisAttributeValue = row2[ATransAnalAttribTable.ColumnAnalysisAttributeValueId].ToString();
return !TAnalysisAttributes.AnalysisAttributeValueIsActive(ref FcmbAnalAttribValues,
FCacheDS.AFreeformAnalysis,
analysisCode,
analysisAttributeValue);
}
else
{
return false;
}
};
//Add conditions to columns
int indexOfAnalysisCodeColumn = 0;
int indexOfAnalysisAttributeValueColumn = 1;
grdAnalAttributes.Columns[indexOfAnalysisCodeColumn].Conditions.Add(conditionAnalysisCodeActive);
grdAnalAttributes.Columns[indexOfAnalysisAttributeValueColumn].Conditions.Add(conditionAnalysisAttributeValueActive);
}
/// <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>
/// Validates the Gift Detail 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="ARecipientPartnerClass">Recipient's Partner Class (used for Motivation Detail validation).</param>
/// <param name="ASetupForILT">Optional - Is the recipient set up for inter-ledger transfers.</param>
/// <param name="ACostCentres">Optional - a CostCentres table. Is required for import validation. </param>
/// <param name="AAccounts">Optional - a Accounts table. Is required for import validation. </param>
/// <param name="AMotivationGroups">Optional - a MotivationGroups table. Is required for import validation. </param>
/// <param name="AMotivationDetails">Optional - a MotivationDetails table. Is required for import validation. </param>
/// <param name="AMailingTable">Optional - a Mailing table. Is required for import validation. </param>
/// <param name="ARecipientField">Optional The recipient field for the gift. Is required for import validation. </param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGiftDetailManual(object AContext,
GiftBatchTDSAGiftDetailRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
TValidationControlsDict AValidationControlsDict,
TPartnerClass? ARecipientPartnerClass,
bool? ASetupForILT = null,
ACostCentreTable ACostCentres = null,
AAccountTable AAccounts = null,
AMotivationGroupTable AMotivationGroups = null,
AMotivationDetailTable AMotivationDetails = null,
PMailingTable AMailingTable = null,
Int64 ARecipientField = -1)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult = null;
object ValidationContext;
int VerifResultCollAddedCount = 0;
bool ValidPartner = true;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return true;
}
bool isImporting = AContext.ToString().Contains("Importing");
// Check if valid recipient
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnRecipientKeyId];
ValidationContext = String.Format("Batch no. {0}, gift no. {1}, detail no. {2}",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
VerificationResult = TSharedPartnerValidation_Partner.IsValidPartner(
ARow.RecipientKey, new TPartnerClass[] { TPartnerClass.FAMILY, TPartnerClass.UNIT }, true,
isImporting ? Catalog.GetString("Recipient key") :
"Recipient of " + THelper.NiceValueDescription(ValidationContext.ToString()),
AContext, ValidationColumn, null);
if (VerificationResult != null)
{
AVerificationResultCollection.Remove(ValidationColumn);
AVerificationResultCollection.AddAndIgnoreNullValue(VerificationResult);
ValidPartner = false;
}
// 'Gift amount must be non-zero
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnGiftTransactionAmountId];
ValidationContext = String.Format("Batch Number {0} (transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsNonZeroDecimal(ARow.GiftTransactionAmount,
ValidationControlsData.ValidationControlLabel + (isImporting ? String.Empty : " of " + ValidationContext),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// If recipient is non-zero, field must also be non-zero. Only check for valid recipient keys.
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnRecipientLedgerNumberId];
ValidationContext = String.Format("batch:{0} transaction:{1} detail:{2}",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if ((ARow.RecipientKey > 0) && ValidPartner && (ARow.RecipientLedgerNumber == 0))
{
VerificationResult = TNumericalChecks.IsGreaterThanZero(ARow.RecipientLedgerNumber,
"Recipient field of " + ValidationContext + " is 0",
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// Motivation Group code must exist
if (!ARow.IsMotivationGroupCodeNull() && (AMotivationGroups != null))
{
AMotivationGroupRow foundRow = (AMotivationGroupRow)AMotivationGroups.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.MotivationGroupCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Unknown motivation group code '{0}'."),
ARow.MotivationGroupCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
if (!isImporting)
{
// NOTE AlanP Oct 2014. This gets checked by standard validation so may no longer be necessary?
// (There was a bug in standard validation where NULL and empty string checks did not quite work as they should ...
// so maybe this was necessary before. Anyway I am leaving it in for now. I know that importing works fine,
// but maybe it is necessary in other circumstances?)
// Motivation Detail must not be null
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnMotivationDetailCodeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (ARow.IsMotivationDetailCodeNull() || (ARow.MotivationDetailCode == String.Empty))
{
VerificationResult = TGeneralChecks.ValueMustNotBeNullOrEmptyString(ARow.MotivationDetailCode,
(isImporting ? ValidationControlsData.ValidationControlLabel : "Motivation Detail code " + ValidationContext),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
}
}
// Motivation Detail must be valid
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnMotivationDetailCodeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (!ARow.IsMotivationDetailCodeNull() && (AMotivationDetails != null))
{
AMotivationDetailRow foundRow = (AMotivationDetailRow)AMotivationDetails.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.MotivationGroupCode, ARow.MotivationDetailCode });
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Unknown motivation detail code '{0}' for group '{1}'."),
ARow.MotivationDetailCode, ARow.MotivationGroupCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
if ((foundRow != null) && (foundRow.MotivationStatus == false) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Motivation detail code '{0}' is no longer in use."),
ARow.MotivationDetailCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
if ((foundRow != null) && (foundRow.RecipientKey != 0) && (ARow.RecipientKey != 0) && (foundRow.RecipientKey != ARow.RecipientKey)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The recipient partner key for motivation detail code '{0}' does not match the recipient partner key in the import line."),
ARow.MotivationDetailCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// Motivation Detail must not be 'Field' or 'Keymin' if the recipient is a Family partner
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnMotivationDetailCodeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (!ARow.IsMotivationDetailCodeNull()
&& (ARow.MotivationGroupCode == MFinanceConstants.MOTIVATION_GROUP_GIFT) && (ARecipientPartnerClass != null)
&& ((ARow.MotivationDetailCode == MFinanceConstants.GROUP_DETAIL_FIELD)
|| (ARow.MotivationDetailCode == MFinanceConstants.GROUP_DETAIL_KEY_MIN))
&& (ARecipientPartnerClass == TPartnerClass.FAMILY))
{
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = new TVerificationResult(AContext,
String.Format(Catalog.GetString("Motivation Detail code '{0}' is not allowed for Family recipients."),
ARow.MotivationDetailCode),
TResultSeverity.Resv_Critical);
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext,
new TScreenVerificationResult(VerificationResult, ValidationColumn, ValidationControlsData.ValidationControl),
ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
}
// Cost Centre Code must exist and be active. Only required for importing because the GUI does this for us otherwise.
if (isImporting && (ACostCentres != null) && !ARow.IsCostCentreCodeNull())
{
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnCostCentreCodeId];
ValidationContext = ARow.CostCentreCode;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
// We even need to check that the code exists!
DataRow foundRow = ACostCentres.Rows.Find(new object[] { ARow.LedgerNumber, ARow.CostCentreCode });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown cost centre code '{0}'."), ARow.CostCentreCode),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
/*
* new TVerificationResult(ValidationContext,
* String.Format(Catalog.GetString("Unknown cost centre code '{0}'."), ARow.CostCentreCode),
* TResultSeverity.Resv_Critical),
*/
ValidationColumn))
{
VerifResultCollAddedCount++;
}
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
ACostCentres,
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++;
}
}
}
// Account Code must exist and be active. Only required for importing because the GUI does this for us otherwise.
if (isImporting && (AAccounts != null) && !ARow.IsAccountCodeNull())
{
DataColumn[] ValidationColumns = new DataColumn[] {
ARow.Table.Columns[AGiftDetailTable.ColumnAccountCodeId], ARow.Table.Columns[AGiftDetailTable.ColumnTaxDeductibleAccountCodeId]
};
string[] AccountCodes = new string[] {
ARow.AccountCode, ARow.TaxDeductibleAccountCode
};
for (int i = 0; i < 2; i++)
{
if (AValidationControlsDict.TryGetValue(ValidationColumns[i], out ValidationControlsData))
{
// We even need to check that the code exists!
DataRow foundRow = AAccounts.Rows.Find(new object[] { ARow.LedgerNumber, AccountCodes[i] });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(AccountCodes[i],
String.Format(Catalog.GetString("Unknown account code '{0}'."), AccountCodes[i]),
TResultSeverity.Resv_Critical),
ValidationColumns[i]))
{
VerifResultCollAddedCount++;
}
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
AAccounts,
AccountCodes[i],
AAccountTable.GetAccountActiveFlagDBName(),
AContext,
ValidationColumns[i],
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if ((VerificationResult != null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumns[i], true))
{
VerifResultCollAddedCount++;
}
}
}
}
// Mailing code must exist
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnMailingCodeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (!ARow.IsMailingCodeNull() && (AMailingTable != null))
{
PMailingRow foundRow = (PMailingRow)AMailingTable.Rows.Find(ARow.MailingCode);
if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Unknown mailing code '{0}'."),
ARow.MailingCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// Detail comments type 1 must not be null if associated comment is not null
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnCommentOneTypeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsGiftCommentOneNull() && (ARow.GiftCommentOne != String.Empty))
{
VerificationResult = TGeneralChecks.ValueMustNotBeNullOrEmptyString(ARow.CommentOneType,
(isImporting ? ValidationControlsData.ValidationControlLabel : "Comment 1 type " + ValidationContext),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
if (VerificationResult == null)
{
// There is a comment type for the comment - but it needs to be one of the valid types
if ((ARow.CommentOneType != MFinanceConstants.GIFT_COMMENT_TYPE_DONOR)
&& (ARow.CommentOneType != MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT)
&& (ARow.CommentOneType != MFinanceConstants.GIFT_COMMENT_TYPE_BOTH)
&& (ARow.CommentOneType != MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Comment type must be one of '{0}', '{1}', '{2}' or '{3}'."),
MFinanceConstants.GIFT_COMMENT_TYPE_DONOR,
MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT,
MFinanceConstants.GIFT_COMMENT_TYPE_BOTH,
MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// Detail comments type 2 must not be null if associated comment is not null
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnCommentTwoTypeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsGiftCommentTwoNull() && (ARow.GiftCommentTwo != String.Empty))
{
VerificationResult = TGeneralChecks.ValueMustNotBeNullOrEmptyString(ARow.CommentTwoType,
(isImporting ? ValidationControlsData.ValidationControlLabel : "Comment 2 type " + ValidationContext),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
if (VerificationResult == null)
{
// There is a comment type for the comment - but it needs to be one of the valid types
if ((ARow.CommentTwoType != MFinanceConstants.GIFT_COMMENT_TYPE_DONOR)
&& (ARow.CommentTwoType != MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT)
&& (ARow.CommentTwoType != MFinanceConstants.GIFT_COMMENT_TYPE_BOTH)
&& (ARow.CommentTwoType != MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Comment type must be one of '{0}', '{1}', '{2}' or '{3}'."),
MFinanceConstants.GIFT_COMMENT_TYPE_DONOR,
MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT,
MFinanceConstants.GIFT_COMMENT_TYPE_BOTH,
MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// Detail comments type 3 must not be null if associated comment is not null
ValidationColumn = ARow.Table.Columns[AGiftDetailTable.ColumnCommentThreeTypeId];
ValidationContext = String.Format("(batch:{0} transaction:{1} detail:{2})",
ARow.BatchNumber,
ARow.GiftTransactionNumber,
ARow.DetailNumber);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsGiftCommentThreeNull() && (ARow.GiftCommentThree != String.Empty))
{
VerificationResult = TGeneralChecks.ValueMustNotBeNullOrEmptyString(ARow.CommentThreeType,
(isImporting ? ValidationControlsData.ValidationControlLabel : "Comment 3 type " + ValidationContext),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
if (VerificationResult == null)
{
// There is a comment type for the comment - but it needs to be one of the valid types
if ((ARow.CommentThreeType != MFinanceConstants.GIFT_COMMENT_TYPE_DONOR)
&& (ARow.CommentThreeType != MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT)
&& (ARow.CommentThreeType != MFinanceConstants.GIFT_COMMENT_TYPE_BOTH)
&& (ARow.CommentThreeType != MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Comment type must be one of '{0}', '{1}', '{2}' or '{3}'."),
MFinanceConstants.GIFT_COMMENT_TYPE_DONOR,
MFinanceConstants.GIFT_COMMENT_TYPE_RECIPIENT,
MFinanceConstants.GIFT_COMMENT_TYPE_BOTH,
MFinanceConstants.GIFT_COMMENT_TYPE_OFFICE),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
return VerifResultCollAddedCount == 0;
}
/// <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;
}
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));
}
}
private static bool ValidateKeyBudgetFields(int ALedgerNumber,
int ARowNumber,
ref ACostCentreTable ACostCentreTbl,
ref AAccountTable AAccountTbl,
string ACostCentre,
string AAccount,
string ABudgetType,
string ABudgetYearStringUpper,
ref TVerificationResultCollection AVerificationResult)
{
string VerificationMessage = string.Empty;
ACostCentreTable CCTable = ACostCentreTbl;
AAccountTable AccTable = AAccountTbl;
ACostCentreRow CostCentreRow = null;
AAccountRow AccountRow = null;
if (CCTable == null)
{
try
{
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
CCTable = ACostCentreAccess.LoadViaALedger(ALedgerNumber, transaction);
AccTable = AAccountAccess.LoadViaALedger(ALedgerNumber, transaction);
#region Validate Data
if ((CCTable == null) || (CCTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Cost Centre data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
else if ((AccTable == null) || (AccTable.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
});
ACostCentreTbl = CCTable;
AAccountTbl = AccTable;
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
//Check for missing or inactive cost centre
CostCentreRow = (ACostCentreRow)ACostCentreTbl.Rows.Find(new object[] { ALedgerNumber, ACostCentre });
if (CostCentreRow == null)
{
VerificationMessage += String.Format(Catalog.GetString(" Cost Centre: '{0}' does not exist."), ACostCentre);
}
else if (!CostCentreRow.CostCentreActiveFlag)
{
VerificationMessage += String.Format(Catalog.GetString(" Cost Centre: '{0}' is currently inactive."), ACostCentre);
}
//Check for missing or inactive account code
AccountRow = (AAccountRow)AAccountTbl.Rows.Find(new object[] { ALedgerNumber, AAccount });
if (AccountRow == null)
{
VerificationMessage += String.Format(Catalog.GetString(" Account: '{0}' does not exist."), AAccount);
}
else if (!AccountRow.AccountActiveFlag)
{
VerificationMessage += String.Format(Catalog.GetString(" Account: '{0}' is currently inactive."), AAccount);
}
//Check Budget Type
if ((ABudgetType != MFinanceConstants.BUDGET_ADHOC)
&& (ABudgetType != MFinanceConstants.BUDGET_SAME)
&& (ABudgetType != MFinanceConstants.BUDGET_INFLATE_N)
&& (ABudgetType != MFinanceConstants.BUDGET_SPLIT)
&& (ABudgetType != MFinanceConstants.BUDGET_INFLATE_BASE))
{
VerificationMessage += String.Format(Catalog.GetString(" Budget Type: '{0}' does not exist."), ABudgetType);
}
//Check Budget Year
if ((ABudgetYearStringUpper != MFinanceConstants.BUDGET_YEAR_THIS.ToUpper())
&& (ABudgetYearStringUpper != MFinanceConstants.BUDGET_YEAR_NEXT.ToUpper()))
{
VerificationMessage += String.Format(Catalog.GetString(" Budget Year: '{0}' should be '{1}' or '{2}'."),
ABudgetYearStringUpper,
MFinanceConstants.BUDGET_YEAR_THIS,
MFinanceConstants.BUDGET_YEAR_NEXT);
}
//Check if errors have occurred
if (VerificationMessage.Length > 0)
{
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Row: " + ARowNumber.ToString("0000")),
VerificationMessage,
TResultSeverity.Resv_Noncritical));
return false;
}
else
{
return true;
}
}
/// <summary>
/// export all GL Balances in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLBalances(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv"));
Console.WriteLine("Writing file: " + filename);
string sql =
String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " +
"FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " +
"WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " +
"AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " +
"AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " +
"A.{11}=true AND NOT GLM.{0} IN ({12})" +
"ORDER BY GLM.{1}, GLM.{0}",
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName(),
AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(),
AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGeneralLedgerMasterTable.GetYearDBName(),
AFinancialYear,
"'" + ACostCentres.Replace(",", "','") + "'",
AAccountTable.GetTableDBName(),
AAccountTable.GetPostingStatusDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(),
AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName());
TDBTransaction Transaction = new TDBTransaction();
DataTable balances = null;
DBAccess.ReadTransaction(ref Transaction,
delegate
{
balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction);
});
StringBuilder sb = new StringBuilder();
if (balances != null)
{
foreach (DataRow row in balances.Rows)
{
sb.Append(StringHelper.StrMerge(
new string[] {
row["CostCentre"].ToString(),
row["Account"].ToString(),
String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])),
String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"]))
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// Method to check and, if possible fix, codes that have had leading zeros removed by Excel for example.
/// </summary>
/// <param name="ALedgerNumber">The ledger number</param>
/// <param name="ARowNumber">The current row number</param>
/// <param name="AAccountCode">The account code that may get changed</param>
/// <param name="AAccountTableRef">The account table that will be checked for valid codes</param>
/// <param name="ACostCentreCode">The cost centre code that may get changed</param>
/// <param name="ACostCentreTableRef">The cost centre table that will be checked for valid codes</param>
/// <param name="AMessages">A message collection. If a change is made a 'Information' (non-critical) message will be added to the collection</param>
public static void FixAccountCodes(int ALedgerNumber, int ARowNumber, ref string AAccountCode, AAccountTable AAccountTableRef,
ref string ACostCentreCode, ACostCentreTable ACostCentreTableRef, TVerificationResultCollection AMessages)
{
// Start with the Account code
string code = ConvertTo4DigitCode(AAccountCode);
if (code != AAccountCode)
{
// Maybe it is wrong?
if (AAccountTableRef.Rows.Find(new object[] { ALedgerNumber, AAccountCode }) == null)
{
// That one does not exist, so try our new 4 digit one
if (AAccountTableRef.Rows.Find(new object[] { ALedgerNumber, code }) != null)
{
// Swap the short code for the longer one
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString(
"The account code '{0}' does not exist. The code has been converted to '{1}' (which does exist). Please check that this is what you intended."),
AAccountCode, code),
TResultSeverity.Resv_Noncritical));
AAccountCode = code;
}
}
}
// Do the same for the cost centre code
code = ConvertTo4DigitCode(ACostCentreCode);
if (code != ACostCentreCode)
{
// Maybe it is wrong?
if (ACostCentreTableRef.Rows.Find(new object[] { ALedgerNumber, ACostCentreCode }) == null)
{
// That one does not exist, so try our new 4 digit one
if (ACostCentreTableRef.Rows.Find(new object[] { ALedgerNumber, code }) != null)
{
// Swap the short code for the longer one
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString(
"The cost centre code '{0}' does not exist. The code has been converted to '{1}' (which does exist). Please check that this is what you intended."),
ACostCentreCode, code),
TResultSeverity.Resv_Noncritical));
ACostCentreCode = code;
}
}
}
}
