public void UpdateRecord()
{
TDBTransaction ReadTransaction = new TDBTransaction();
GiftBatchTDS MainDS = new GiftBatchTDS();
TDataBase db = DBAccess.Connect("test");
db.ReadTransaction(ref ReadTransaction,
delegate
{
ALedgerAccess.LoadAll(MainDS, ReadTransaction);
});
ReadTransaction.Rollback();
MainDS.ALedger[0].LastGiftBatchNumber++;
AGiftBatchRow batch = MainDS.AGiftBatch.NewRowTyped();
batch.LedgerNumber = MainDS.ALedger[0].LedgerNumber;
batch.BatchNumber = MainDS.ALedger[0].LastGiftBatchNumber;
batch.BankAccountCode = "6000";
batch.BatchYear = 0;
batch.BatchPeriod = 1;
batch.CurrencyCode = "EUR";
batch.BatchDescription = "test";
batch.BankCostCentre = (MainDS.ALedger[0].LedgerNumber * 100).ToString("0000");
batch.LastGiftNumber = 0;
batch.HashTotal = 83;
MainDS.AGiftBatch.Rows.Add(batch);
GiftBatchTDSAccess.SubmitChanges(MainDS);
MainDS.AcceptChanges();
MainDS.AGiftBatch[0].BatchDescription = "test2";
GiftBatchTDSAccess.SubmitChanges(MainDS);
TDBTransaction transaction = new TDBTransaction();
AGiftBatchTable batches = null;
db.ReadTransaction(
ref transaction,
delegate
{
batches = AGiftBatchAccess.LoadByPrimaryKey(batch.LedgerNumber, batch.BatchNumber, transaction);
});
// some problems with sqlite and datagrid
Assert.AreEqual(typeof(decimal), batches[0][AGiftBatchTable.ColumnHashTotalId].GetType(), "type decimal");
Assert.AreEqual(83.0m, batches[0].HashTotal, "gift batch hashtotal does not equal");
}
/// <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();
}
public static int GetGLMSequenceForBudget(int ALedgerNumber, string AAccountCode, string ACostCentreCode, int AYear)
{
int retVal;
AGeneralLedgerMasterTable GeneralLedgerMasterTable = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetGLMSequenceForBudget");
db.ReadTransaction(
ref Transaction,
delegate
{
GeneralLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByUniqueKey(ALedgerNumber,
AYear,
AAccountCode,
ACostCentreCode,
Transaction);
});
if (GeneralLedgerMasterTable.Count > 0)
{
retVal = (int)GeneralLedgerMasterTable.Rows[0].ItemArray[0];
}
else
{
retVal = -1;
}
return(retVal);
}
public static List <string> GetUniqueTypes(
Int64 APartnerKey
)
{
TDBTransaction T = new TDBTransaction();
TDataBase DB = DBAccess.Connect("Get get unique data types");
List <OdbcParameter> SQLParameter = new List <OdbcParameter>();
List <string> Types = new List <string>();
DB.ReadTransaction(ref T, delegate {
string sql = "SELECT DISTINCT `p_type_c` FROM `p_data_history` " +
"WHERE `p_partner_key_n` = ?";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.VarChar)
{
Value = APartnerKey.ToString()
});
DataTable AllTypes = DB.SelectDT(sql, "UniqueTypes", T, SQLParameter.ToArray());
foreach (DataRow TypeRow in AllTypes.Rows)
{
Types.Add(TypeRow.Field <string>("p_type_c"));
}
});
return(Types);
}
} // Select Gift Recipients
/// <summary>
/// Find all the gifts for a worker, presenting the results in four year columns.
/// NOTE - the user can select the field of the donor.
///
/// All the DB work was previously done by the Select Gift Recipients function above.
/// I only need to filter the table by recipientKey.
/// </summary>
/// <returns></returns>
public static DataTable SelectGiftDonors(TParameterList AParameters, TResultList AResults)
{
Int64 recipientKey = AParameters.Get("RecipientKey").ToInt64();
// TotalGiftsPerRecipient must not be static.
DataTable TotalGiftsPerRecipient = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("SelectGiftDonors");
string SqlQuery = TSession.GetVariable("QueryFinanceReport_SelectGiftRecipients").ToString();
SqlQuery = SqlQuery.Replace("GROUP by ", "AND recipient.p_partner_key_n = " + recipientKey + " GROUP by ");
db.ReadTransaction(
ref Transaction,
delegate
{
TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction);
});
TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString();
DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true,
new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" });
return(resultTable);
}
/// <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);
}
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);
}
} // ExportAllGiftBatchData
private String PartnerShortName(Int64 partnerKey)
{
if (partnerKey > 0)
{
// Get Partner ShortName
PPartnerTable pt = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("PartnerShortName");
db.ReadTransaction(
ref Transaction,
delegate
{
pt =
PPartnerAccess.LoadByPrimaryKey(partnerKey,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }),
Transaction, null, 0, 0);
});
if (pt.Rows.Count == 1)
{
return(pt[0].PartnerShortName);
}
}
return("");
}
/// <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();
}
/// <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>
/// 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);
}
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>
/// ...
/// </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>
/// 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();
}
}
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>
/// 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();
}
}
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);
}
/// <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);
}
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>
/// Application and Database should have the same version, otherwise all sorts of things can go wrong.
/// this is specific to the OpenPetra database, for all other databases it will just ignore the database version check
/// </summary>
private static void CheckDatabaseVersion(TDataBase ADataBase)
{
TDBTransaction ReadTransaction = new TDBTransaction();
DataTable Tbl = null;
if (TAppSettingsManager.GetValue("action", string.Empty, false) == "patchDatabase")
{
// we want to upgrade the database, so don't check for the database version
return;
}
TDataBase db = DBAccess.Connect("CheckDatabaseVersion", ADataBase);
db.ReadTransaction(ref ReadTransaction,
delegate
{
// now check if the database is 'up to date'; otherwise run db patch against it
Tbl = ReadTransaction.DataBaseObj.SelectDT(
"SELECT s_default_value_c FROM PUB_s_system_defaults WHERE s_default_code_c = 'CurrentDatabaseVersion'",
"Temp", ReadTransaction, new OdbcParameter[0]);
});
if (Tbl.Rows.Count == 0)
{
return;
}
}
private void GetLedgerInfo(out DateTime APeriodStartDate, out DateTime APeriodEndDate,
out string AForexGainsLossesAccount, TDataBase ADataBase)
{
ALedgerTable LedgerTable = null;
TDataBase db = DBAccess.Connect("GetLedgerInfo", ADataBase);
TDBTransaction Transaction = new TDBTransaction();
DateTime PeriodStartDate = DateTime.Today;
DateTime PeriodEndDate = DateTime.Today;
string ForexGainsLossesAccount = String.Empty;
db.ReadTransaction(ref Transaction,
delegate
{
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction);
ForexGainsLossesAccount = LedgerTable[0].ForexGainsLossesAccount;
TFinancialYear.GetStartAndEndDateOfPeriod(FLedgerNumber, LedgerTable[0].CurrentPeriod, out PeriodStartDate, out PeriodEndDate, Transaction);
});
AForexGainsLossesAccount = ForexGainsLossesAccount;
APeriodStartDate = PeriodStartDate;
APeriodEndDate = PeriodEndDate;
}
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);
}
public void Init()
{
CommonNUnitFunctions.ResetDatabase();
TPetraServerConnector.Connect("../../etc/TestServer.config");
TDataBase db = DBAccess.Connect("AddressToolsTest");
PPartnerRow Partner1 = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS, db);
TestPartnerKey = Partner1.PartnerKey;
//Guard assert: ensure database has been properly reset
TDBTransaction ATransaction = new TDBTransaction();
DataTable ResultTable = null;
string Query = string.Format("SELECT * FROM p_location WHERE p_site_key_n = {0} AND p_location_key_i = {1}",
DomainManager.GSiteKey,
LocationKey);
db.ReadTransaction(
ref ATransaction,
delegate
{
ResultTable = ATransaction.DataBaseObj.SelectDT(Query, "CheckLocations", ATransaction);
});
Assert.AreEqual(0, ResultTable.Rows.Count);
}
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);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ALastContactDate"></param>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with a new Database connection</param>
public static void GetLastContactDate(Int64 APartnerKey, out DateTime ALastContactDate,
TDataBase ADataBase = null)
{
TDBTransaction ReadTransaction = new TDBTransaction();
DataSet LastContactDS;
PContactLogRow ContactDR;
DateTime LastContactDate = new DateTime();
LastContactDS = new DataSet("LastContactDate");
LastContactDS.Tables.Add(new PContactLogTable());
TDataBase db = DBAccess.Connect("GetLastContactDate", ADataBase);
db.ReadTransaction(
ref ReadTransaction,
delegate
{
PContactLogAccess.LoadViaPPartner(LastContactDS, APartnerKey,
StringHelper.InitStrArr(new String[] { PContactLogTable.GetContactDateDBName() }), ReadTransaction,
StringHelper.InitStrArr(new String[] { "ORDER BY " + PContactLogTable.GetContactDateDBName() + " DESC" }), 0, 1);
if (LastContactDS.Tables[PContactLogTable.GetTableName()].Rows.Count > 0)
{
ContactDR = ((PContactLogTable)LastContactDS.Tables[PContactLogTable.GetTableName()])[0];
LastContactDate = ContactDR.ContactDate;
}
else
{
LastContactDate = DateTime.MinValue;
}
});
ALastContactDate = LastContactDate;
}
public void TestDBAccess_SelectUsingDataAdapterMulti2()
{
TDBTransaction ReadTransaction = new TDBTransaction();
const string TestReadQuery1 =
"SELECT * from p_partner where p_partner_key_n = :APartnerKey and p_partner_short_name_c LIKE :APartnerShortName;";
DataTable TmpDT = new DataTable();
TDataAdapterCanceller TmpDac;
TDataBase db = DBAccess.Connect("TestDBAccess");
OdbcParameter[] ParametersArray;
List <object[]> ParameterValuesList = new List <object[]>();
ParametersArray = new OdbcParameter[2];
ParametersArray[0] = new OdbcParameter("APartnerKey", OdbcType.BigInt);
ParametersArray[1] = new OdbcParameter("APartnerShortName", OdbcType.Text);
ParameterValuesList.Add(new object[] { 43005001, "z%" });
ParameterValuesList.Add(new object[] { 43005002, "T%" });
db.ReadTransaction(ref ReadTransaction,
delegate
{
// Act AND Assert #1 - Prepared Parametrised Query
Assert.AreEqual(1, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions: ParametersArray, AParameterValues: ParameterValuesList, APrepareSelectCommand: true),
"SelectUsingDataAdapterMulti using a Prepared Command did not yield 1 records, but ought to.");
// Act AND Assert #2 - Non-Prepared Parametrised Query
Assert.AreEqual(1, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions: ParametersArray, AParameterValues: ParameterValuesList),
"SelectUsingDataAdapterMulti using NO Prepared Command did not yield 1 records, but ought to.");
});
}
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);
}
}
/// <summary>
/// Checks for the existance of a Partner.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Partner to check for.</param>
/// <param name="AMustNotBeMergedPartner">Set to true to check whether the Partner
/// must not be a Merged Partner.</param>
/// <returns>An instance of PPartnerRow if the Partner exists (taking AMustNotBeMergedPartner into consideration),
/// otherwise null.</returns>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with a new Database connection</param>
public static PPartnerRow CheckPartnerExists2(Int64 APartnerKey, bool AMustNotBeMergedPartner,
TDataBase ADataBase = null)
{
PPartnerRow ReturnValue = null;
TDBTransaction ReadTransaction = new TDBTransaction();
PPartnerTable PartnerTable = null;
if (APartnerKey != 0)
{
TDataBase db = DBAccess.Connect("CheckPartnerExists2", ADataBase);
db.ReadTransaction(ref ReadTransaction,
delegate
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
});
if (PartnerTable.Rows.Count != 0)
{
if (AMustNotBeMergedPartner)
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(
PartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
{
ReturnValue = PartnerTable[0];
}
}
else
{
ReturnValue = PartnerTable[0];
}
}
}
return(ReturnValue);
}
public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber, string AMotivationGroupCode)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadMotivationDetails");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
if (AMotivationGroupCode.Length > 0)
{
AMotivationGroupAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
AMotivationDetailAccess.LoadViaAMotivationGroup(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
}
else
{
AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
}
AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
});
// 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);
}
