private DataTable GetAccountListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
StringCollection FieldList = new StringCollection();
FieldList.Add(AAccountTable.GetLedgerNumberDBName());
FieldList.Add(AAccountTable.GetAccountCodeDBName());
FieldList.Add(AAccountTable.GetAccountTypeDBName());
FieldList.Add(AAccountTable.GetAccountCodeShortDescDBName());
FieldList.Add(AAccountTable.GetAccountActiveFlagDBName());
FieldList.Add(AAccountTable.GetPostingStatusDBName());
FieldList.Add(AAccountTable.GetForeignCurrencyFlagDBName());
FieldList.Add(AAccountTable.GetForeignCurrencyCodeDBName());
GLSetupTDS TempDS = new GLSetupTDS();
AAccountAccess.LoadViaALedger(TempDS, ALedgerNumber, FieldList, AReadTransaction);
// load AAccountProperty and set the BankAccountFlag
AAccountPropertyAccess.LoadViaALedger(TempDS, ALedgerNumber, AReadTransaction);
foreach (AAccountPropertyRow accProp in TempDS.AAccountProperty.Rows)
{
if ((accProp.PropertyCode == MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT) && (accProp.PropertyValue == "true"))
{
TempDS.AAccount.DefaultView.RowFilter = String.Format("{0}='{1}'",
AAccountTable.GetAccountCodeDBName(),
accProp.AccountCode);
GLSetupTDSAAccountRow acc = (GLSetupTDSAAccountRow)TempDS.AAccount.DefaultView[0].Row;
acc.BankAccountFlag = true;
TempDS.AAccount.DefaultView.RowFilter = "";
}
}
// not currently needed as an Account is only a Bank Account if it has a 'Bank Account' Account Property
// load AAccountHierarchyDetails and check if this account reports to the CASH account
/*AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(TempDS,
* ALedgerNumber,
* MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD,
* AReadTransaction);
*
* TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
* TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
* List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
*
* foreach (GLSetupTDSAAccountRow account in TempDS.AAccount.Rows)
* {
* if (children.Contains(account.AccountCode))
* {
* account.CashAccountFlag = true;
* }
* }*/
return(TempDS.AAccount);
}
/// <summary>
/// Export the accounts
/// </summary>
public static void ExportAccounts(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
List <string> AAccounts)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "account.csv"));
Console.WriteLine("Writing file: " + filename);
// only export accounts that are actually used with these cost centres
string sql =
String.Format("SELECT {0}, {1}, {2} from PUB_{3} WHERE {4} = {5} AND " +
" {6}=true ORDER BY {0}",
AAccountTable.GetAccountCodeDBName(),
AAccountTable.GetAccountCodeLongDescDBName(),
AAccountTable.GetDebitCreditIndicatorDBName(),
AAccountTable.GetTableDBName(),
AAccountTable.GetLedgerNumberDBName(),
ALedgerNumber,
AAccountTable.GetPostingStatusDBName());
TDBTransaction Transaction = null;
DataTable accounts = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
accounts = DBAccess.GDBAccessObj.SelectDT(sql, "accounts", Transaction);
});
StringBuilder sb = new StringBuilder();
if (accounts != null)
{
foreach (DataRow row in accounts.Rows)
{
if (AAccounts.Contains(row[0].ToString()))
{
sb.Append(StringHelper.StrMerge(new string[] { row[0].ToString(), row[1].ToString(),
Convert.ToBoolean(row[2]) ? "Soll" : "Haben" }, ACSVSeparator));
sb.Append(ANewLine);
}
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <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;
}
/// <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>
/// get the default bank account for this ledger
/// </summary>
public string GetDefaultBankAccount()
{
#region Validate Arguments
if (FLedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), FLedgerNumber);
}
#endregion Validate Arguments
TDataBase db = DBAccess.Connect("GetDefaultBankAccount", FDataBase);
TDBTransaction readTransaction = new TDBTransaction();
string BankAccountCode = new TSystemDefaults(db).GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + FLedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
try
{
db.ReadTransaction(
ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(FLedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c" +
" FROM a_gift_batch " +
" WHERE a_gift_batch.a_ledger_number_i =" + FLedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "'" +
" ORDER BY a_gift_batch.a_batch_number_i DESC" +
" LIMIT 1;";
DataTable latestAccountCode =
db.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((latestAccountCode != null) && (latestAccountCode.Rows.Count > 0))
{
BankAccountCode = latestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable accountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, readTransaction);
#region Validate Data
if ((accountTable == null) || (accountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
FLedgerNumber));
}
#endregion Validate Data
DataView dv = accountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(FLedgerNumber);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
}
return(BankAccountCode);
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction readTransaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);
#region Validate Data
if ((AccountTable == null) || (AccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
DataView dv = AccountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
return(BankAccountCode);
}
