public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateCurrentPeriod)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrentPeriodDates");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
ledgerTable[0].CurrentPeriod,
Transaction);
startDate = accountingPeriodTable[0].PeriodStartDate;
endDate = accountingPeriodTable[0].PeriodEndDate;
});
db.CloseDBConnection();
AStartDateCurrentPeriod = startDate;
AEndDateCurrentPeriod = endDate;
return(true);
}
/// <summary>
/// set a session variable.
/// store to database immediately
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public static void SetVariable(string name, object value)
{
TDataBase db = ConnectDB("SessionSetVariable");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(ref t, ref SubmissionOK,
delegate
{
if (FSessionValues.Keys.Contains(name))
{
FSessionValues[name] = (new TVariant(value)).EncodeToString();
}
else
{
FSessionValues.Add(name, (new TVariant(value)).EncodeToString());
}
SaveSession(t);
SubmissionOK = true;
});
db.CloseDBConnection();
}
public static bool TypeAheadMotivationDetail(Int32 ALedgerNumber, string ASearch,
Int32 ALimit,
out DataTable AResult)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("TypeAheadMotivationDetail");
DataTable result = new DataTable();
db.ReadTransaction(
ref Transaction,
delegate
{
string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadMotivationDetail.sql");
OdbcParameter[] parameters = new OdbcParameter[3];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("MotivationDetailCode", OdbcType.VarChar);
parameters[1].Value = "%" + ASearch + "%";
parameters[2] = new OdbcParameter("DescDetail", OdbcType.VarChar);
parameters[2].Value = "%" + ASearch + "%";
SqlStmt += " LIMIT " + ALimit.ToString();
result = db.SelectDT(SqlStmt, "Search", Transaction, parameters);
});
db.CloseDBConnection();
AResult = result;
return(result.Rows.Count > 0);
}
private ALedgerInitFlagRow FindRecord(String AFlag)
{
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("FindRecord", FDataBase);
ALedgerInitFlagTable LedgerInitFlagTable = null;
ALedgerInitFlagRow Ret = null;
db.ReadTransaction(
ref ReadTransaction,
delegate
{
LedgerInitFlagTable = ALedgerInitFlagAccess.LoadByPrimaryKey(FLedgerNumber, AFlag, ReadTransaction);
if ((LedgerInitFlagTable != null) && (LedgerInitFlagTable.Rows.Count == 1))
{
Ret = LedgerInitFlagTable[0];
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
return(Ret);
}
public static String GetUserDefault(String AKey, String ADefaultValue, TDataBase ADataBase = null)
{
String ReturnValue = "";
TDataBase db = DBAccess.Connect("GetUserDefault", ADataBase);
TDBTransaction ReadTransaction = new TDBTransaction();
db.ReadTransaction(ref ReadTransaction,
delegate
{
if (!SUserDefaultsAccess.Exists(UserInfo.GetUserInfo().UserID, AKey, ReadTransaction))
{
ReturnValue = ADefaultValue;
}
else
{
SUserDefaultsTable DT =
SUserDefaultsAccess.LoadByPrimaryKey(UserInfo.GetUserInfo().UserID, AKey, ReadTransaction);
ReturnValue = DT[0].DefaultValue;
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
/// <summary>
/// THIS METHOD DOES NOT RETURN USEFUL VALUES because the Year is not specified.
/// It is only called from tests, and those tests pass,
/// because there's no previous financial year in the database,
/// because the data returned by this method is from the earliest year in the ledger.
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AAccountCode"></param>
/// <param name="ACostCentreCode"></param>
/// <param name="ADataBase"></param>
public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, string ACostCentreCode, TDataBase ADataBase = null)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("TGet_GLM_Info", ADataBase);
db.ReadTransaction(ref transaction,
delegate
{
FGLMTbl = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = ALedgerNumber;
GLMTemplateRow.AccountCode = AAccountCode;
GLMTemplateRow.CostCentreCode = ACostCentreCode;
FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction);
if (FGLMTbl.Rows.Count == 0)
{
// String msg = TLogging.StackTraceToText(new StackTrace(true));
String msg = "";
TLogging.Log(String.Format("ERROR: No TGet_GLM_Info row found for ({0}, {1}).",
ACostCentreCode, AAccountCode, msg));
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
}
/// <summary>
/// Load a single row by sequence and period
/// </summary>
/// <returns>True if it seemed to work</returns>
public Boolean LoadBySequence(Int32 ASequence, Int32 APeriod)
{
Boolean LoadedOk = false;
if (ASequence != -1)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadBySequence", FDataBase);
db.ReadTransaction(ref transaction,
delegate
{
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(ASequence, APeriod, transaction);
LoadedOk = FGLMpTable.Rows.Count > 0;
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
return(LoadedOk);
}
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_RequestedConnection()
{
TDataBase RequestedConnection = EstablishDBConnectionAndReturnIt("New DB Connection");
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.SimpleAutoDBConnAndReadTransactionSelector(ATransaction: out ReadTransaction, AName: "NewTransaction",
ADatabase: RequestedConnection,
AEncapsulatedDBAccessCode: delegate
{
Result =
Convert.ToInt32(ReadTransaction.DataBaseObj.ExecuteScalar("SELECT COUNT(*) FROM p_partner WHERE p_partner_key_n = 43005001",
ReadTransaction));
// Is this the expected connection?
Assert.AreEqual("New DB Connection", ReadTransaction.DataBaseObj.ConnectionName);
});
// Did we get the expected transaction?
Assert.AreEqual("NewTransaction", ReadTransaction.TransactionName);
// Check we get a result
Assert.AreEqual(1, Result);
// Check the new transaction is rolled back
Assert.False(ReadTransaction.Valid);
RequestedConnection.CloseDBConnection();
}
private static string GetUserIDFromEmail(string AUserEmail)
{
TDataBase db = DBAccess.Connect("GetUserIDFromEmail");
TDBTransaction Transaction = new TDBTransaction();
string UserID = AUserEmail;
db.ReadTransaction(ref Transaction,
delegate
{
string sql = "SELECT s_user_id_c FROM PUB_s_user WHERE UPPER(s_email_address_c) = ?";
OdbcParameter[] parameters = new OdbcParameter[1];
parameters[0] = new OdbcParameter("EmailAddress", OdbcType.VarChar);
parameters[0].Value = AUserEmail.ToUpper();
DataTable result = db.SelectDT(sql, "user", Transaction, parameters);
if (result.Rows.Count == 1)
{
UserID = result.Rows[0][0].ToString();
}
else
{
TLogging.Log("Login with E-Mail address failed for " + AUserEmail + ". " +
"We found " + result.Rows.Count.ToString() + " matching rows for this address.");
throw new Exception("multiple users are matching this email address");
}
});
db.CloseDBConnection();
return(UserID);
}
public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod, TDataBase ADataBase = null)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetFirstDayOfAccountingPeriod", ADataBase);
DateTime Result = DateTime.MinValue;
db.ReadTransaction(ref Transaction,
delegate
{
// Get the accounting periods for this ledger. The table will contain more than 12 rows.
// The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created.
// We are really only interested in the Day anyway
AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction);
DataView periodsView = new DataView(periods, "",
String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows);
AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row;
Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day);
if (ADateInAPeriod.Day < row.PeriodStartDate.Day)
{
Result = Result.AddMonths(-1);
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
AFirstDayOfPeriod = Result;
return(Result != DateTime.MinValue);
}
/// <summary>
/// this returns the default next available (highest) partner key of the given field
/// </summary>
/// <param name="AFieldPartnerKey">if this is -1, then the sitekey defined in System Parameters is used</param>
/// <param name="ADataBase"></param>
/// <returns>void</returns>
public static System.Int64 GetNewPartnerKey(System.Int64 AFieldPartnerKey, TDataBase ADataBase = null)
{
PPartnerLedgerTable PartnerLedgerTable = null;
Int64 ReturnValue = -1;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetNewPartnerKey", ADataBase);
db.ReadTransaction(ref ReadTransaction,
delegate
{
PartnerLedgerTable = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
ReturnValue = PartnerLedgerTable[0].PartnerKey + PartnerLedgerTable[0].LastPartnerId + 1;
// Now check that this does not exist, and increment until we
// find one which does not
while (PPartnerAccess.Exists(ReturnValue, ReadTransaction))
{
ReturnValue = ReturnValue + 1;
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
/// <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
}
/// <summary>
/// The constructor needs a ledgerinfo (for the ledger number)
/// </summary>
/// <param name="ALedgerNumber"></param>
public THandleAccountPropertyInfo(Int32 ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("THandleAccountPropertyInfo");
try
{
db.ReadTransaction(
ref Transaction,
delegate
{
FPropertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction);
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
db.CloseDBConnection();
}
private void LoadDatabase()
{
intBaseCurrencyDigits = DIGIT_INIT_VALUE;
intForeignCurrencyDigits = DIGIT_INIT_VALUE;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadDatabase");
db.ReadTransaction(
ref transaction,
delegate
{
currencyTable = ACurrencyAccess.LoadAll(transaction);
});
db.CloseDBConnection();
if (currencyTable.Rows.Count == 0)
{
EVerificationException terminate = new EVerificationException(
Catalog.GetString("The table a_currency is empty!"));
terminate.Context = "Common Accounting";
terminate.ErrorCode = "TCurrencyInfo01";
throw terminate;
}
}
/// <summary>
/// Gets the next available key for PPartnerGiftDestination
/// </summary>
/// <param name="ADataBase"></param>
/// <returns>The next available key</returns>
internal static int GetNewKeyForPartnerGiftDestination(TDataBase ADataBase = null)
{
int ReturnValue = 0;
TDataBase db = DBAccess.Connect("GetNewKeyForPartnerGiftDestination", ADataBase);
TDBTransaction Transaction = new TDBTransaction();
db.ReadTransaction(
ref Transaction,
delegate
{
PPartnerGiftDestinationTable Table = PPartnerGiftDestinationAccess.LoadAll(Transaction);
foreach (PPartnerGiftDestinationRow Row in Table.Rows)
{
if (Row.Key >= ReturnValue)
{
ReturnValue = Row.Key + 1;
}
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
private void PrepareTestCaseData()
{
TDBTransaction Transaction = new TDBTransaction();
bool AccountTestCasesAvailable = false;
bool CostCentreTestCasesAvailable = false;
TDataBase db = DBAccess.Connect("PrepareTestCaseData");
db.ReadTransaction(ref Transaction,
delegate
{
// Check if some special test data are available - otherwise load ...
AccountTestCasesAvailable = AAccountAccess.Exists(LedgerNumber, "6001", Transaction);
CostCentreTestCasesAvailable = ACostCentreAccess.Exists(LedgerNumber, "4301", Transaction);
});
db.CloseDBConnection();
if (!AccountTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" +
"test-sql\\gl-test-account-data.sql", LedgerNumber);
}
if (!CostCentreTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" +
"test-sql\\gl-test-costcentre-data.sql", LedgerNumber);
}
}
/// remove all variables that start with a name, eg. PROGRESSTRACKER
public static void ClearVariables(string ANameStartsWith)
{
TDataBase db = ConnectDB("SessionClearVariables");
TDBTransaction t = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(ref t, ref SubmissionOK,
delegate
{
bool finished = false;
while (!finished)
{
finished = true;
foreach (string name in FSessionValues.Keys)
{
if (name.StartsWith(ANameStartsWith))
{
FSessionValues.Remove(name);
finished = false;
break;
}
}
}
SaveSession(t);
SubmissionOK = true;
});
db.CloseDBConnection();
}
/// <summary>
/// Loads only GLM Data selected by Ledger Number, Year and Account Code ...
/// </summary>
public TGlmInfo(int ALedgerNumber, int ACurrentFinancialYear, string AAccountCode, TDataBase ADataBase = null)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("TGlmInfo", ADataBase);
db.ReadTransaction(
ref transaction,
delegate
{
FGLMTbl = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = ALedgerNumber;
GLMTemplateRow.AccountCode = AAccountCode;
GLMTemplateRow.Year = ACurrentFinancialYear;
FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction);
TLogging.LogAtLevel(1,
"TGlmInfo(" + ALedgerNumber + ", " + ACurrentFinancialYear + ", " + AAccountCode + ") has loaded " + FGLMTbl.Rows.Count +
" Rows.");
iPtr = -1;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
}
private void CloseTestDBConnection(TDataBase ADBAccessObject, string AConnectionName = null, bool ALogNumberOfConnections = false)
{
if (ALogNumberOfConnections)
{
if (FDBType == TDBType.PostgreSQL)
{
TLogging.Log(" CloseTestDBConnection: Number of open DB Connections on PostgreSQL BEFORE closing Database connection: " +
TDataBase.GetNumberOfDBConnections(FDBType) + ADBAccessObject.GetDBConnectionIdentifier());
}
}
TLogging.Log(" CloseTestDBConnection: Closing connection to Database..." + ADBAccessObject.GetDBConnectionIdentifier());
ADBAccessObject.CloseDBConnection();
TLogging.Log(" CloseTestDBConnection: Database connection closed." + TDataBase.GetDBConnectionName(AConnectionName));
if (ALogNumberOfConnections)
{
if (FDBType == TDBType.PostgreSQL)
{
TLogging.Log(" CloseTestDBConnection: Number of open DB Connections on PostgreSQL AFTER closing Database connection: " +
TDataBase.GetNumberOfDBConnections(FDBType) + TDataBase.GetDBConnectionName(AConnectionName));
}
}
}
/// <summary>
/// Load all GLMP rows for this Cost Centre in this period
/// </summary>
/// <returns></returns>
public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod)
{
Boolean LoadedOk = false;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadByCostCentreAccountPeriod", FDataBase);
FGLMpTable = new AGeneralLedgerMasterPeriodTable();
db.ReadTransaction(ref transaction,
delegate
{
db.SelectDT(
FGLMpTable,
"SELECT a_general_ledger_master_period.* FROM" +
" a_general_ledger_master_period, a_general_ledger_master" +
" WHERE" +
" a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" +
" AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber +
" AND a_general_ledger_master.a_year_i = " + AYear +
" AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" +
" AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" +
" AND a_general_ledger_master_period.a_period_number_i=" + APeriod,
transaction);
LoadedOk = (FGLMpTable.Rows.Count > 0);
}); // AutoReadTransaction
if (FDataBase == null)
{
db.CloseDBConnection();
}
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
return(LoadedOk);
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo");
db.ReadTransaction(
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = db.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
db.CloseDBConnection();
}
private void DeleteFlag(String AFlag)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("DeleteFlag", FDataBase);
Boolean SubmissionOK = true;
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
ALedgerInitFlagTable LedgerInitFlagTable = ALedgerInitFlagAccess.LoadByPrimaryKey(
FLedgerNumber, AFlag, Transaction);
if (LedgerInitFlagTable.Rows.Count == 1)
{
LedgerInitFlagTable[0].Delete();
ALedgerInitFlagAccess.SubmitChanges(LedgerInitFlagTable, Transaction);
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
public static ACurrencyLanguageRow GetCurrencyLanguage(string ACurrencyCode)
{
ACurrencyLanguageRow ReturnValue = null;
string Language = TUserDefaults.GetStringDefault(MSysManConstants.USERDEFAULT_UILANGUAGE);
if (Language.Length > 2)
{
// need to get the two digit language code of p_language: de-DE => DE, en-EN => EN
Language = Language.Substring(Language.Length - 2).ToUpper();
}
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrencyLanguage");
db.ReadTransaction(
ref Transaction,
delegate
{
ACurrencyLanguageTable CurrencyLanguageTable = ACurrencyLanguageAccess.LoadByPrimaryKey(ACurrencyCode, Language, Transaction);
if ((CurrencyLanguageTable != null) && (CurrencyLanguageTable.Rows.Count > 0))
{
ReturnValue = CurrencyLanguageTable[0];
}
});
db.CloseDBConnection();
return(ReturnValue);
}
/// <summary>
/// processes the delegate
/// </summary>
private static void GenericProcessor(string ADelegateName, bool ARunManually = false)
{
if (!FProcessDelegates.ContainsKey(ADelegateName))
{
return;
}
TProcessDelegate TypedDelegate = FProcessDelegates[ADelegateName];
TDataBase db = null;
try
{
db = DBAccess.Connect("Servers's DB Connection for TimedProcessing");
TypedDelegate(db, ARunManually);
if (TLogging.DebugLevel >= 9)
{
TLogging.Log("Timed Processing: Delegate " + ADelegateName + " has run.");
}
}
finally
{
if (db != null)
{
db.CloseDBConnection();
}
}
}
public void TestDBAccess_SimpleAutoDBConnAndReadTransactionSelector_RequestedConnectionJoin()
{
if (FDBType == TDBType.SQLite)
{
// do not run this test with SQLite
return;
}
TDataBase RequestedConnection = DBAccess.Connect("New DB Connection");
TDBTransaction FirstTransaction = RequestedConnection.BeginTransaction(IsolationLevel.ReadCommitted, -1, "FirstTransaction");
bool newTransaction;
TDBTransaction ReadTransaction = RequestedConnection.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
out newTransaction,
"NewTransaction");
Assert.AreEqual("New DB Connection", ReadTransaction.DataBaseObj.ConnectionName);
// Did we get not a new transaction
Assert.AreEqual(false, newTransaction);
// Did we get the expected transaction?
Assert.AreEqual("FirstTransaction", ReadTransaction.TransactionName);
// Check the transaction we joined is left open
Assert.True(ReadTransaction.Valid);
ReadTransaction.Rollback();
RequestedConnection.CloseDBConnection();
}
/// <summary>
/// Gets daily exchange rate for the given currencies and date. The APriorDaysAllwed parameter limits how 'old' the rate can be.
/// The unique rate parameter can ensure that a rate is only returned if there is only one to choose from.
/// TODO: might even collect the latest exchange rate from the web
/// </summary>
/// <param name="ACurrencyFrom"></param>
/// <param name="ACurrencyTo"></param>
/// <param name="ADateEffective"></param>
/// <param name="APriorDaysAllowed">Sets a limit on how many days prior to ADateEffective to search. Use -1 for no limit,
/// 0 to imply that the rate must match for the specified date, 1 for the date and the day before and so on.</param>
/// <param name="AEnforceUniqueRate">If true the method will only return a value if there is one unique rate in the date range.
/// Otherwise it returns the latest rate.</param>
/// <returns>Zero if no exchange rate found</returns>
public static decimal GetDailyExchangeRate(string ACurrencyFrom,
string ACurrencyTo,
DateTime ADateEffective,
int APriorDaysAllowed,
Boolean AEnforceUniqueRate)
{
// TODO: collect exchange rate from the web; save to db
// see Mantis tracker case #87
// The rule is that we don't enforce finding a unique rate over the whole date range as that doesn't really make sense
if (AEnforceUniqueRate && (APriorDaysAllowed == -1))
{
throw new ArgumentException(
"The GetDailyExchangeRate method does not allow 'AEnforceUniqueRate' to be true when 'APriorDaysAllowed' is -1. Unique rates should only be requested over a limited date range.");
}
if (ACurrencyFrom == ACurrencyTo)
{
return(1.0M);
}
// Define our earliest date, if set
DateTime earliestDate = DateTime.MinValue;
if (APriorDaysAllowed >= 0)
{
earliestDate = ADateEffective.AddDays(-APriorDaysAllowed);
}
// Query the database using the specific period ...
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetDailyExchangeRate");
ExchangeRateTDS allRates = null;
db.ReadTransaction(
ref transaction,
delegate
{
allRates = TCrossLedger.LoadDailyExchangeRateData(false, earliestDate, ADateEffective);
});
db.CloseDBConnection();
// Now work out the correct rate from the returned rows
if (allRates != null)
{
decimal rateOfExchange;
DateTime effectiveDate;
if (CommonRoutines.GetBestExchangeRate(allRates.ADailyExchangeRate, ACurrencyFrom, ACurrencyTo, AEnforceUniqueRate,
out rateOfExchange, out effectiveDate))
{
return(rateOfExchange);
}
}
//Returning 0 causes a validation error to force the user to select an exchange rate:
return(0.0m);
}
public static bool GetGiftsForFieldChangeAdjustment(ref GiftBatchTDS AGiftDS, Int32 ALedgerNumber,
Int64 ARecipientKey,
DateTime AStartDate,
DateTime AEndDate,
Int64 AOldField,
out TVerificationResultCollection AMessages)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetGiftsForFieldChangeAdjustment");
GiftBatchTDS MainDS = new GiftBatchTDS();
AMessages = new TVerificationResultCollection();
db.ReadTransaction(
ref Transaction,
delegate
{
string SqlStmt = TDataBase.ReadSqlFile("Gift.GetGiftsToAdjustField.sql");
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int);
param.Value = ALedgerNumber;
parameters.Add(param);
param = new OdbcParameter("StartDate", OdbcType.Date);
param.Value = AStartDate;
parameters.Add(param);
param = new OdbcParameter("EndDate", OdbcType.Date);
param.Value = AEndDate;
parameters.Add(param);
param = new OdbcParameter("RecipientKey", OdbcType.BigInt);
param.Value = ARecipientKey;
parameters.Add(param);
param = new OdbcParameter("OldField", OdbcType.BigInt);
param.Value = AOldField;
parameters.Add(param);
db.Select(MainDS, SqlStmt, MainDS.AGiftDetail.TableName, Transaction, parameters.ToArray());
// get additional data
foreach (GiftBatchTDSAGiftDetailRow Row in MainDS.AGiftDetail.Rows)
{
AGiftBatchAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Transaction);
AGiftRow GiftRow =
AGiftAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Row.GiftTransactionNumber, Transaction);
Row.DateEntered = GiftRow.DateEntered;
Row.DonorKey = GiftRow.DonorKey;
Row.IchNumber = 0;
Row.DonorName = PPartnerAccess.LoadByPrimaryKey(Row.DonorKey, Transaction)[0].PartnerShortName;
}
});
AGiftDS = MainDS;
db.CloseDBConnection();
return(CheckGiftsNotPreviouslyReversed(AGiftDS, out AMessages));
}
private void CloseTestConnections(TDataBase ATestDBInstance1, TDataBase ATestDBInstance2,
int AInitialNumberOfDBConnections)
{
// Close the two independent DB Connections one after the other.
// Note: If you look at the Log output you will see that closing these two DB Connections HAS NOT GOT AN
// IMPACT on the number of open DB Connections that PostgreSQL reports - this is because of the Connection
// Pooling that is going on and these DB Connections not having been 'released' by the Connection Pool...!
ATestDBInstance1.CloseDBConnection();
ATestDBInstance2.CloseDBConnection();
}
/// <summary>Use a separate DB Connection to get the Ledger name</summary>
/// <param name="ALedgerNumber"></param>
/// <returns></returns>
public static string GetLedgerNameUsingSeparateDb(Int32 ALedgerNumber)
{
TDataBase dataBase = DBAccess.SimpleEstablishDBConnection("GetLedgerName");
String ledgerName = GetLedgerName(ALedgerNumber, dataBase);
dataBase.CloseDBConnection();
return(ledgerName);
}
/// <summary>
/// Closes the Database connection to the Database
/// for TTimedProcessing.
/// </summary>
/// <returns>void</returns>
private static void CloseDBConnection(TDataBase DBAccessObj)
{
DBAccessObj.CloseDBConnection();
}
public static void Main(string[] args)
{
try
{
// establish connection to database
TCmdOpts settings = new TCmdOpts();
if (!settings.IsFlagSet("Server.ODBC_DSN"))
{
Console.WriteLine(
"sample call: " +
"ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
Environment.Exit(-1);
}
new TLogging("debug.log");
TDataBase db = new TDataBase();
TDBType dbtype = TDBType.ProgressODBC;
if (settings.IsFlagSet("Server.RDBMSType"))
{
dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
}
if (dbtype != TDBType.ProgressODBC)
{
throw new Exception("at the moment only Progress ODBC db is supported");
}
db.EstablishDBConnection(dbtype,
settings.GetOptValue("Server.ODBC_DSN"),
"",
"",
settings.GetOptValue("username"),
settings.GetOptValue("password"),
"");
DBAccess.GDBAccessObj = db;
TLogging.DebugLevel = 10;
string sqlText = "";
if (!settings.IsFlagSet("sql"))
{
Console.WriteLine("Please enter sql and finish with semicolon: ");
while (!sqlText.Trim().EndsWith(";"))
{
sqlText += " " + Console.ReadLine();
}
sqlText = sqlText.Substring(0, sqlText.Length - 1);
}
else
{
sqlText = settings.GetOptValue("sql");
Console.WriteLine(sqlText);
}
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
DataTable table = db.SelectDT(sqlText, "temp", transaction);
XmlDocument doc = TDataBase.DataTableToXml(table);
if (settings.IsFlagSet("output"))
{
if (settings.GetOptValue("output").EndsWith("yml"))
{
TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("csv"))
{
TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("xml"))
{
StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
sw.Write(TXMLParser.XmlToString2(doc));
sw.Close();
}
}
else
{
TYml2Xml.Xml2Yml(doc, "temp.yml");
StreamReader sr = new StreamReader("temp.yml");
Console.WriteLine(sr.ReadToEnd());
sr.Close();
}
db.RollbackTransaction();
db.CloseDBConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
