public void TestDBAccess_GNoETransaction_throws_proper_ExceptionOnWrongMinimumIsolationLevel()
{
bool NewTrans;
TDataBase db = DBAccess.Connect("TestDBAccess", FDataBase);
//
// Arrange
//
TDBTransaction transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTrans);
// Guard Assert: Check that the new DB Transaction has been taken out
Assert.That(NewTrans, Is.True);
//
// Act and Assert
//
Assert.Throws <EDBTransactionIsolationLevelTooLowException>(() =>
db.GetNewOrExistingTransaction(
IsolationLevel.Serializable,
out NewTrans), "GetNewOrExistingTransaction didn't throw expected " +
"Exception (EDBTransactionIsolationLevelTooLowException) on asking for a minimum IsolationLevel which "
+
"cannot be met");
// Roll back the one DB Transaction that has been requested (this would happen automatically on DB closing, but
// it's better to do this explicitly here so it is clear it isn't forgotten about.
transaction.Rollback();
}
public void TestDBAccess_CheckRunningDBTransactionIsolationLevelIsCompatible_WithMinimumIsolationLevel_ExpectTrue()
{
bool NewTrans;
bool Result;
TDataBase db = DBAccess.Connect("TestDBAccess", FDataBase);
//
// Arrange
//
TDBTransaction transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTrans);
// Guard Assert: Check that the new DB Transaction has been taken out
Assert.That(NewTrans, Is.True);
// Act
Result = db.CheckRunningDBTransactionIsolationLevelIsCompatible(IsolationLevel.ReadCommitted);
// Primary Assert
Assert.IsTrue(Result, "Calling CheckRunningDBTransactionIsolationLevelIsCompatible and asking for a minimum " +
"IsolationLevel that is met by the running DB Transactions' IsolationLevel did not return true");
// Roll back the one DB Transaction that has been requested (this would happen automatically on DB closing, but
// it's better to do this explicitly here so it is clear it isn't forgotten about.
transaction.Rollback();
}
public static bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out string ACountryNameLocal)
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetBestAddress");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
bool ResultValue = false;
try
{
ResultValue = TAddressTools.GetBestAddress(APartnerKey,
out AAddress,
out ACountryNameLocal,
Transaction);
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(ResultValue);
}
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>
/// Tests whether the current user has access to a particular Partner.
/// </summary>
/// <remarks>Corresponds to Progress 4GL Method 'CanAccessPartner' in
/// common/sp_partn.p</remarks>
/// <param name="APartnerKey">PartnerKey of Partner for which access should
/// be checked for.</param>
/// <param name="AThrowExceptionIfDenied"></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>
/// <returns><see cref="TPartnerAccessLevelEnum.palGranted" /> if access
/// to the Partner is granted (or Partner doesn't exist), otherwise a different
/// <see cref="TPartnerAccessLevelEnum" /> value.</returns>
public static TPartnerAccessLevelEnum CanAccessPartnerByKey(Int64 APartnerKey,
bool AThrowExceptionIfDenied, TDataBase ADataBase = null)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
PPartnerTable PartnerTable;
if (APartnerKey != 0)
{
TDataBase db = DBAccess.Connect("DetermineBestAddress", ADataBase);
ReadTransaction = db.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
out NewTransaction);
// Load Partner
try
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
if (PartnerTable.Rows.Count > 0)
{
// Partner exists, now check Access Level
if (!AThrowExceptionIfDenied)
{
return(CanAccessPartner(PartnerTable[0], ADataBase));
}
else
{
CanAccessPartnerExc(PartnerTable[0], ADataBase);
/*
* The previous Method call would throw an Exception
* in case access would not be granted and program execution
* would leave this Method at that point; if it doesn't
* then this implies that access is granted.
*/
return(TPartnerAccessLevelEnum.palGranted);
}
}
else
{
// Partner not found. Access Level is Granted in this case.
return(TPartnerAccessLevelEnum.palGranted);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(8, "TSecurity.CanAccessPartnerByKey: committed own transaction.");
}
}
}
else
{
// Invalid Partner. Access Level is Granted in this case.
return(TPartnerAccessLevelEnum.palGranted);
}
}
/// <summary>
/// Determines which address is the 'Best Address' of a Partner, and returns the PPartnerLocation record which is the
/// 'Best Address'.
/// </summary>
/// <remarks>There are two similar shared Methods in Namespace Ict.Petra.Server.MPartner.Common.Calculations,
/// both called 'DetermineBestAddress' which work by passing in the PartnerLocations of a Partner in an Argument
/// and which return a <see cref="TLocationPK" />. As those Methods don't access the database, these Methods
/// can be used client-side as well!</remarks>
/// <param name="APartnerKey">PartnerKey of the Partner whose addresses should be checked.</param>
/// <param name="APartnerLocationDR">PPartnerLocation Record that is the record that is the Location of the 'Best Address'.</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>
/// <returns>A <see cref="TLocationPK" /> which points to the 'Best Address'. If no 'Best Address' was found,
/// SiteKey and LocationKey of this instance will be both -1.</returns>
public static TLocationPK DetermineBestAddress(Int64 APartnerKey, out PPartnerLocationRow APartnerLocationDR,
TDataBase ADataBase = null)
{
TLocationPK ReturnValue = new TLocationPK();
PPartnerLocationTable PartnerLocationDT;
Boolean NewTransaction;
TDataBase db = DBAccess.Connect("DetermineBestAddress", ADataBase);
TDBTransaction ReadTransaction = db.GetNewOrExistingTransaction(
MCommonConstants.CACHEABLEDT_ISOLATIONLEVEL,
out NewTransaction);
try
{
PartnerLocationDT = PPartnerLocationAccess.LoadViaPPartner(APartnerKey, ReadTransaction);
ReturnValue = Ict.Petra.Server.MPartner.Common.Calculations.DetermineBestAddress(PartnerLocationDT);
APartnerLocationDR = (PPartnerLocationRow)PartnerLocationDT.Rows.Find(new object[]
{ APartnerKey, ReturnValue.SiteKey, ReturnValue.LocationKey });
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "ServerCalculations.DetermineBestAddress: commited own transaction.");
}
}
return(ReturnValue);
}
private Int64 GetLedgerPartnerKey(Int32 ALedgerNumber)
{
Int64 retVal = 0;
bool NewTransaction = false;
TDataBase db = DBAccess.Connect("GetLedgerPartnerKey");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
if (MainDS.ALedger.Count > 0)
{
retVal = MainDS.ALedger[0].PartnerKey;
}
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(retVal);
}
/// <summary>
/// Tests whether the current user has access to a particular Partner.
/// </summary>
/// <remarks>
/// <para>Corresponds to Progress 4GL Method 'CanAccessPartner' in
/// common/sp_partn.p</para>
/// <para>A shared implementation of this Method exists that has two additional
/// Arguments. It needs the Foundation Row to be passed in, but has the
/// advantage of not needing a Server roundtrip for a DB lookup!</para>
/// </remarks>
/// <param name="APartnerRow">Partner for which access should be checked for.</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>
/// <returns><see cref="T:TPartnerAccessLevelEnum.palGranted" /> if access
/// to the Partner is granted, otherwise a different
/// <see cref="T:TPartnerAccessLevelEnum" /> value.</returns>
public static TPartnerAccessLevelEnum CanAccessPartner(
PPartnerRow APartnerRow, TDataBase ADataBase = null)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
PFoundationTable FoundationTable;
if (APartnerRow.PartnerKey != 0)
{
// If PartnerClass is ORGANISATION, we need to check if it is a Foundation
if (APartnerRow.PartnerClass == SharedTypes.PartnerClassEnumToString(
TPartnerClass.ORGANISATION))
{
TDataBase db = DBAccess.Connect("DetermineBestAddress", ADataBase);
ReadTransaction = db.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
out NewTransaction);
// Load Foundation
try
{
FoundationTable = PFoundationAccess.LoadByPrimaryKey(APartnerRow.PartnerKey,
ReadTransaction);
if (FoundationTable.Rows.Count > 0)
{
// The ORGANISATION is a Foundation, we need to check Foundation Security
return(Ict.Petra.Shared.MPartner.TSecurity.CanAccessPartner(APartnerRow,
true, FoundationTable[0]));
}
else
{
// The ORGANISATION isn't a Foundation, we don't need to check Foundation Security
return(Ict.Petra.Shared.MPartner.TSecurity.CanAccessPartner(APartnerRow,
false, null));
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(8, "TSecurity.CanAccessPartnerByKey: committed own transaction.");
}
}
}
else
{
// PartnerClass isn't ORGANISATION, we don't need to check Foundation Security
return(Ict.Petra.Shared.MPartner.TSecurity.CanAccessPartner(APartnerRow,
false, null));
}
}
else
{
// Invalid Partner. Access Level is Granted in this case.
return(TPartnerAccessLevelEnum.palGranted);
}
}
private static bool GetAllReceivingFields(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetAllReceivingFields called!");
TDataBase db = DBAccess.Connect("GetAllReceivingFields");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[0];
DataSet TmpDS = ReadTransaction.DataBaseObj.Select(
"SELECT PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
", PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() +
" FROM PUB_" + PUnitTable.GetTableDBName() +
", PUB_" + PPartnerTable.GetTableDBName() + ", " +
"PUB_" + PPartnerTypeTable.GetTableDBName() +
" WHERE PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetStatusCodeDBName() + " = \"ACTIVE\"" +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetTypeCodeDBName() + " = \"LEDGER\"" +
" ORDER BY PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " ASC",
"TempTable", ReadTransaction, ParametersArray);
DataTable ResultTale = TmpDS.Tables[0];
for (int Counter = 0; Counter < ResultTale.Rows.Count; ++Counter)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = ResultTale.Rows[Counter][PUnitTable.GetPartnerKeyDBName()];
NewRow[UNIT_NAME] = ResultTale.Rows[Counter][PUnitTable.GetUnitNameDBName()];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFields: committed own transaction.");
}
}
return(true);
}
private bool CallGetNewOrExistingTransaction(out TDBTransaction ATransaction, IsolationLevel AIsolationLevel = IsolationLevel.ReadCommitted,
string ATransactionName = "")
{
bool NewTrans;
TDataBase db = DBAccess.Connect("CallGetNewOrExistingTransaction");
ATransaction = db.GetNewOrExistingTransaction(AIsolationLevel, out NewTrans, ATransactionName);
return(NewTrans);
}
/// create new gift info
public static AGiftBatchRow CreateNewGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewGiftInfo", ADataBase);
bool NewTransaction;
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
ALedgerAccess.LoadAll(AGiftDS, Transaction);
AGiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(AGiftDS.ALedger[0].LedgerNumber, DateTime.Today, "Test batch", db);
// Create a new GiftBatch
AGiftBatchRow Batch = AGiftDS.AGiftBatch[0];
Batch.BankAccountCode = "6000";
Batch.BatchYear = 1;
Batch.BatchPeriod = 1;
Batch.CurrencyCode = "EUR";
Batch.BankCostCentre = Batch.LedgerNumber.ToString() + "00";
Batch.LastGiftNumber = 1;
Batch.ExchangeRateToBase = 0.5M;
// Create a new Gift record
AGiftRow Gift = AGiftDS.AGift.NewRowTyped();
Gift.LedgerNumber = Batch.LedgerNumber;
Gift.BatchNumber = Batch.BatchNumber;
Gift.GiftTransactionNumber = 1;
Gift.DonorKey = APartnerKey;
AGiftDS.AGift.Rows.Add(Gift);
// Create a new GiftDetail record
AGiftDetailRow GiftDetail = AGiftDS.AGiftDetail.NewRowTyped();
GiftDetail.LedgerNumber = Gift.LedgerNumber;
GiftDetail.BatchNumber = Gift.BatchNumber;
GiftDetail.GiftTransactionNumber = Gift.GiftTransactionNumber;
GiftDetail.DetailNumber = 1;
GiftDetail.MotivationGroupCode = "GIFT";
GiftDetail.MotivationDetailCode = "SUPPORT";
// this won't work with RecipientKey 0 anymore. see https://github.com/openpetra/openpetra/issues/183
GiftDetail.RecipientKey = 43000000;
GiftDetail.RecipientLedgerNumber = APartnerKey;
GiftDetail.GiftTransactionAmount = 10;
AGiftDS.AGiftDetail.Rows.Add(GiftDetail);
if (NewTransaction)
{
Transaction.Rollback();
}
return(Batch);
}
/// <summary>
/// get the number and name of the registration offices that the current user has access for
/// </summary>
/// <returns></returns>
public static PPartnerTable GetRegistrationOffices()
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetRegistrationOffices");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
PPartnerTable result = new PPartnerTable();
try
{
List <Int64> offices = GetRegistrationOfficeKeysOfUser(Transaction);
StringCollection FieldList = new StringCollection();
FieldList.Add(PPartnerTable.GetPartnerKeyDBName());
FieldList.Add(PPartnerTable.GetPartnerShortNameDBName());
// get the short names of the registration offices
foreach (Int64 OfficeKey in offices)
{
PPartnerTable partnerTable = PPartnerAccess.LoadByPrimaryKey(OfficeKey, FieldList, Transaction);
result.Merge(partnerTable);
}
// remove unwanted columns
List <string> ColumnNames = new List <string>();
foreach (DataColumn column in result.Columns)
{
ColumnNames.Add(column.ColumnName);
}
foreach (string columnName in ColumnNames)
{
if (!FieldList.Contains(columnName))
{
result.Columns.Remove(columnName.ToString());
}
}
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
result.DefaultView.Sort = PPartnerTable.GetPartnerKeyDBName();
return(result);
}
/// <summary>
/// Adds the OMer field from the person and family record of the partner
/// to the data table if it is not already there.
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="AFieldsTable"></param>
/// <returns></returns>
private static bool GetReceivingFieldFromGiftDestination(long APartnerKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetReceivingFieldFromGiftDestination called!");
TDataBase db = DBAccess.Connect("GetReceivingFieldFromGiftDestination");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
PPersonTable PersonTable;
PPartnerGiftDestinationTable GiftDestinationTable;
long FamilyKey = APartnerKey;
PersonTable = PPersonAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
if (PersonTable.Rows.Count > 0)
{
PPersonRow PersonRow = (PPersonRow)PersonTable[0];
FamilyKey = PersonRow.FamilyKey;
}
GiftDestinationTable = PPartnerGiftDestinationAccess.LoadViaPPartner(FamilyKey, ReadTransaction);
if (GiftDestinationTable.Rows.Count > 0)
{
foreach (PPartnerGiftDestinationRow Row in GiftDestinationTable.Rows)
{
// check if the gift destination is currently active
if ((Row.DateEffective <= DateTime.Today) &&
(Row.IsDateExpiresNull() || ((Row.DateExpires >= DateTime.Today) && (Row.DateExpires != Row.DateEffective))))
{
AddFieldToTable(Row.FieldKey, ref AFieldsTable, ref ReadTransaction);
}
}
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFieldFromPartnerTable: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// checks if the result for this query is already cached.
/// If not, the result is retrieved from the database.
/// The result is added to the cache, and the result is returned
/// as a DataSet from the cache.
/// </summary>
/// <param name="sql"></param>
/// <param name="ATable">can already have some prepared columns; optional parameter, can be null
/// </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 the 'globally available'
/// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this
/// Argument!</param>
/// <returns>void</returns>
public DataSet GetDataSet(String sql, DataTable ATable, TDataBase ADataBase = null)
{
TDataBase DBAccessObj = DBAccess.GetDBAccessObj(ADataBase);
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
DataSet newDataSet;
int counter = 0;
foreach (string sqlstr in storedSQLQuery)
{
if (sqlstr == sql)
{
// create a clone of the result, so that the returned datasets
// can be treated separately
return(((DataSet)storedDataSet[counter]).Copy());
}
counter++;
}
try
{
ReadTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
if (ATable == null)
{
newDataSet = DBAccessObj.Select(sql, "Cache", ReadTransaction);
}
else
{
newDataSet = new DataSet();
newDataSet.Tables.Add(ATable);
ATable.TableName = "Cache";
DBAccessObj.Select(newDataSet, sql, "Cache", ReadTransaction);
}
}
finally
{
if (NewTransaction)
{
DBAccessObj.CommitTransaction();
}
}
storedDataSet.Add(newDataSet);
storedSQLQuery.Add(sql);
// return a copy, so that changes to the dataset don't affect the stored copy.
return(newDataSet.Copy());
}
/// <summary>
/// check if there is already a supplier record for the given partner
/// </summary>
/// <param name="APartnerKey"></param>
/// <returns></returns>
public bool CanFindSupplier(Int64 APartnerKey)
{
TDBTransaction ReadTransaction;
bool NewTransaction = false;
bool ReturnValue = false;
ReadTransaction = FDataBase.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
try
{
ReturnValue = AApSupplierAccess.Exists(APartnerKey, ReadTransaction);
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
}
}
return(ReturnValue);
}
/// <summary>
/// get the family keys of the specified persons
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
public static DataTable GetFamilyKeys(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string> Defines = new SortedList <string, string>();
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return(null);
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.GetFamilyKeyOfPerson.sql", Defines);
Boolean NewTransaction;
TDataBase db = DBAccess.Connect("GetFamilyKeys");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(null);
}
return(resultTable);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(null);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}
/// <summary>
/// check if the partner has a link to a cost centre (eg. a worker)
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ACostCentreCode"></param>
/// <returns></returns>
public static Boolean HasPartnerCostCentreLink(Int64 APartnerKey, out String ACostCentreCode)
{
Boolean ReturnValue;
TDBTransaction ReadTransaction;
Boolean NewTransaction;
StringCollection RequiredColumns;
AValidLedgerNumberTable ValidLedgerNumberTable;
ACostCentreCode = "";
RequiredColumns = new StringCollection();
RequiredColumns.Add(AValidLedgerNumberTable.GetCostCentreCodeDBName());
TDataBase db = DBAccess.Connect("HasPartnerCostCentreLink");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
try
{
ValidLedgerNumberTable = AValidLedgerNumberAccess.LoadViaPPartnerPartnerKey(
APartnerKey,
RequiredColumns,
ReadTransaction,
null,
0,
0);
if (ValidLedgerNumberTable.Rows.Count != 0)
{
ACostCentreCode = ValidLedgerNumberTable[0].CostCentreCode;
ReturnValue = true;
}
else
{
ReturnValue = false;
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "HasPartnerCostCentreLink: committed own transaction.");
}
}
return(ReturnValue);
}
public static PUnitTable GetOutreachOptions(Int64 AUnitKey)
{
String ConferenceCodePrefix = "";
PUnitTable UnitTable = new PUnitTable();
PUnitRow TemplateRow = UnitTable.NewRowTyped(false);
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetOutreachOptions called!");
TDataBase db = DBAccess.Connect("GetOutreachOptions");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
/* Load data */
UnitTable = PUnitAccess.LoadByPrimaryKey(AUnitKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
String ConferenceCode = ((PUnitRow)UnitTable.Rows[0]).OutreachCode;
if (ConferenceCode.Length >= 5)
{
ConferenceCodePrefix = ConferenceCode.Substring(0, 5) + "%";
}
StringCollection operators = new StringCollection();
operators.Add("LIKE");
TemplateRow.OutreachCode = ConferenceCodePrefix;
UnitTable = PUnitAccess.LoadUsingTemplate(TemplateRow, operators, null, ReadTransaction);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetOutreachOptions: committed own transaction.");
}
}
return(UnitTable);
}
/// create new recurring gift info
public static ARecurringGiftBatchRow CreateNewRecurringGiftInfo(Int64 APartnerKey, ref GiftBatchTDS AGiftDS, TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("CreateNewRecurringGiftInfo", ADataBase);
bool NewTransaction;
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerAccess.LoadAll(AGiftDS, Transaction);
AGiftDS = TGiftTransactionWebConnector.CreateARecurringGiftBatch(AGiftDS.ALedger[0].LedgerNumber, db);
if (NewTransaction)
{
Transaction.Rollback();
}
// Create a new RecurringGiftBatch
ARecurringGiftBatchRow Batch = AGiftDS.ARecurringGiftBatch[0];
Batch.BankAccountCode = "6000";
Batch.CurrencyCode = "EUR";
// Create a new RecurringGift record
ARecurringGiftRow RecurringGift = AGiftDS.ARecurringGift.NewRowTyped();
RecurringGift.LedgerNumber = Batch.LedgerNumber;
RecurringGift.BatchNumber = Batch.BatchNumber;
RecurringGift.GiftTransactionNumber = 1;
RecurringGift.DonorKey = APartnerKey;
AGiftDS.ARecurringGift.Rows.Add(RecurringGift);
// Create a new RecurringGiftDetail record
ARecurringGiftDetailRow RecurringGiftDetail = AGiftDS.ARecurringGiftDetail.NewRowTyped();
RecurringGiftDetail.LedgerNumber = Batch.LedgerNumber;
RecurringGiftDetail.BatchNumber = Batch.BatchNumber;
RecurringGiftDetail.GiftTransactionNumber = 1;
RecurringGiftDetail.MotivationGroupCode = "GIFT";
RecurringGiftDetail.MotivationDetailCode = "SUPPORT";
RecurringGiftDetail.RecipientKey = 43000000;
RecurringGiftDetail.RecipientLedgerNumber = APartnerKey;
RecurringGiftDetail.GiftAmount = 10;
AGiftDS.ARecurringGiftDetail.Rows.Add(RecurringGiftDetail);
return(Batch);
}
public static Boolean LoadUserDefaultsTable(String AUserName,
out SUserDefaultsTable AUserDefaultsDataTable, TDataBase ADataBase = null)
{
Boolean ReturnValue = false;
TDataBase db = DBAccess.Connect("LoadUserDefaultsTable", ADataBase);
TDBTransaction ReadTransaction = null;
bool NewTransaction = false;
try
{
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
if (SUserDefaultsAccess.CountViaSUser(AUserName, ReadTransaction) != 0)
{
AUserDefaultsDataTable = SUserDefaultsAccess.LoadViaSUser(AUserName, null, ReadTransaction,
StringHelper.InitStrArr(new string[] { "ORDER BY", SUserDefaultsTable.GetDefaultCodeDBName() }), 0, 0);
AUserDefaultsDataTable.AcceptChanges();
}
else
{
AUserDefaultsDataTable = new SUserDefaultsTable();
}
ReturnValue = true;
}
finally
{
if (NewTransaction && (ReadTransaction != null))
{
ReadTransaction.Rollback();
TLogging.LogAtLevel(9, "TUserDefaults.LoadUserDefaultsTable: rolled back own transaction.");
}
}
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
private static String GetConferencePrefix(long AConferenceKey)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
String ConferencePrefix = "-----";
PUnitTable UnitTable;
TLogging.LogAtLevel(9, "TConferenceOptions.GetOutreachPrefix: called.");
TDataBase db = DBAccess.Connect("GetConferencePrefix");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
UnitTable = PUnitAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
if (UnitTable.Rows[0][PUnitTable.GetOutreachCodeDBName()] != System.DBNull.Value)
{
ConferencePrefix = (string)UnitTable.Rows[0][PUnitTable.GetOutreachCodeDBName()];
if (ConferencePrefix.Length > 5)
{
ConferencePrefix = ConferencePrefix.Substring(0, 5);
}
}
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetOutreachPrefix: committed own transaction.");
}
}
return(ConferencePrefix);
}
private static bool GetReceivingFieldsForOneConference(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetReceivingFieldsForOneConference called!");
TDataBase db = DBAccess.Connect("GetReceivingFieldsForOneConference");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
String PartnerKeyDBName = PcAttendeeTable.GetPartnerKeyDBName();
PcAttendeeTable AttendeeTable;
StringCollection FieldList = new StringCollection();
FieldList.Add(PartnerKeyDBName);
AttendeeTable = PcAttendeeAccess.LoadViaPcConference(AConferenceKey, FieldList, ReadTransaction);
foreach (DataRow Row in AttendeeTable.Rows)
{
long PartnerKey = (long)Row[PartnerKeyDBName];
GetReceivingFieldFromGiftDestination(PartnerKey, ref AFieldsTable);
GetReceivingFieldFromShortTermTable(PartnerKey, ref AFieldsTable);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFieldsForOneConference: committed own transaction.");
}
}
return(true);
}
public void TestSimpleDatabaseAccess()
{
bool NewTransaction = false;
TDataBase db = DBAccess.Connect("TestSimpleDatabaseAccess");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
Assert.AreEqual(1, Ict.Petra.Server.MFinance.Account.Data.Access.ALedgerAccess.CountAll(Transaction), "Testing the number of ledgers");
}
catch (Exception)
{
throw;
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}
/// is the current user representing the office that is organising the conference?
/// This user gets to see all registrations.
public static bool IsConferenceOrganisingOffice()
{
// TODO: check for permissions for just one specific office, linked from the config file?
bool NewTransaction;
TDataBase db = DBAccess.Connect("IsConferenceOrganisingOffice");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
List <Int64> AllowedRegistrationOffices = new List <long>();
try
{
AllowedRegistrationOffices = GetRegistrationOfficeKeysOfUser(Transaction);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
return(AllowedRegistrationOffices.Count > MINIMUM_OFFICES_TO_BECOME_ORGANIZER);
}
/// <summary>
/// Determines which address is the 'Best Address' of a Partner, and returns the PLocation record which the
/// 'Best Address' is pointing to.
/// </summary>
/// <remarks>There are two similar shared Methods in Namespace Ict.Petra.Server.MPartner.Common.Calculations,
/// both called 'DetermineBestAddress' which work by passing in the PartnerLocations of a Partner in an Argument
/// and which return a <see cref="TLocationPK" />. As those Methods don't access the database, these Methods
/// can be used client-side as well!</remarks>
/// <param name="APartnerKey">PartnerKey of the Partner whose addresses should be checked.</param>
/// <param name="APartnerLocationDR">PPartnerLocation Record that is the record that is the Location of the 'Best Address'.</param>
/// <param name="ALocationDR">PLocation Record that the 'Best Address' is pointing to.</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>
/// <returns>A <see cref="TLocationPK" /> which points to the 'Best Address'. If no 'Best Address' was found,
/// SiteKey and LocationKey of this instance will be both -1.</returns>
public static TLocationPK DetermineBestAddress(Int64 APartnerKey, out PPartnerLocationRow APartnerLocationDR,
out PLocationRow ALocationDR, TDataBase ADataBase = null)
{
PLocationTable LocationDT;
TLocationPK BestLocation = new TLocationPK();
Boolean NewTransaction;
APartnerLocationDR = null;
ALocationDR = null;
BestLocation = DetermineBestAddress(APartnerKey, out APartnerLocationDR);
TDataBase db = DBAccess.Connect("DetermineBestAddress", ADataBase);
TDBTransaction ReadTransaction = db.GetNewOrExistingTransaction(
MCommonConstants.CACHEABLEDT_ISOLATIONLEVEL,
out NewTransaction);
try
{
LocationDT = PLocationAccess.LoadByPrimaryKey(BestLocation.SiteKey, BestLocation.LocationKey, ReadTransaction);
if (LocationDT.Rows.Count > 0)
{
ALocationDR = LocationDT[0];
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "ServerCalculations.DetermineBestAddress: commited own transaction.");
}
}
return(BestLocation);
}
/// <summary>
/// Adds the confirmed option code to the data table, using the values
/// from the shorttermtable of the current partner
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="AFieldsTable"></param>
/// <returns></returns>
private static bool GetReceivingFieldFromShortTermTable(long APartnerKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetReceivingFieldFromShortTermTable called!");
TDataBase db = DBAccess.Connect("GetReceivingFieldFromShortTermTable");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
PmShortTermApplicationTable ShortTermTable;
ShortTermTable = PmShortTermApplicationAccess.LoadViaPPerson(APartnerKey, ReadTransaction);
foreach (PmShortTermApplicationRow Row in ShortTermTable.Rows)
{
if (!Row.IsStConfirmedOptionNull())
{
AddFieldToTable(Row.StConfirmedOption, ref AFieldsTable, ref ReadTransaction);
}
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFieldFromShortTermTable: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// Get the actual amount
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AGLMSeqThisYear"></param>
/// <param name="AGLMSeqNextYear"></param>
/// <param name="APeriodNumber"></param>
/// <param name="ANumberAccountingPeriods"></param>
/// <param name="ACurrentFinancialYear"></param>
/// <param name="AThisYear"></param>
/// <param name="AYTD"></param>
/// <param name="ABalSheetForwardPeriods"></param>
/// <param name="ACurrencySelect"></param>
/// <returns></returns>
private static decimal GetActualInternal(int ALedgerNumber,
int AGLMSeqThisYear,
int AGLMSeqNextYear,
int APeriodNumber,
int ANumberAccountingPeriods,
int ACurrentFinancialYear,
int AThisYear,
bool AYTD,
bool ABalSheetForwardPeriods,
string ACurrencySelect)
{
decimal retVal = 0;
decimal currencyAmount = 0;
bool incExpAccountFwdPeriod = false;
//DEFINE BUFFER a_glm_period FOR a_general_ledger_master_period.
//DEFINE BUFFER a_glm FOR a_general_ledger_master.
//DEFINE BUFFER buf_account FOR a_account.
if (AGLMSeqThisYear == -1)
{
return(retVal);
}
bool newTransaction = false;
TDataBase db = DBAccess.Connect("GetActualInternal");
TDBTransaction dBTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction);
AGeneralLedgerMasterTable generalLedgerMasterTable = null;
AGeneralLedgerMasterRow generalLedgerMasterRow = null;
AGeneralLedgerMasterPeriodTable generalLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow generalLedgerMasterPeriodRow = null;
AAccountTable AccountTable = null;
AAccountRow AccountRow = null;
try
{
if (APeriodNumber == 0) /* start balance */
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterRow.StartBalanceBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterRow.StartBalanceIntl;
break;
default:
currencyAmount = generalLedgerMasterRow.StartBalanceForeign;
break;
}
}
else if (APeriodNumber > ANumberAccountingPeriods) /* forwarding periods only exist for the current financial year */
{
if (ACurrentFinancialYear == AThisYear)
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear,
APeriodNumber,
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
else
{
generalLedgerMasterPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqNextYear,
(APeriodNumber - ANumberAccountingPeriods),
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
}
else /* normal period */
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
if (generalLedgerMasterPeriodRow != null)
{
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterPeriodRow.ActualBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterPeriodRow.ActualIntl;
break;
default:
currencyAmount = generalLedgerMasterPeriodRow.ActualForeign;
break;
}
}
if ((APeriodNumber > ANumberAccountingPeriods) && (ACurrentFinancialYear == AThisYear))
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, generalLedgerMasterRow.AccountCode, dBTransaction);
AccountRow = (AAccountRow)AccountTable.Rows[0];
if ((AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
(AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_EXPENSE.ToUpper()) &&
!ABalSheetForwardPeriods)
{
incExpAccountFwdPeriod = true;
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
ANumberAccountingPeriods,
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
if (!AYTD)
{
if (!((APeriodNumber == (ANumberAccountingPeriods + 1)) && incExpAccountFwdPeriod) &&
!((APeriodNumber == (ANumberAccountingPeriods + 1)) && (ACurrentFinancialYear > AThisYear)))
{
/* if it is an income expense acount, and we are in period 13, nothing needs to be subtracted,
* because that was done in correcting the amount in the block above;
* if we are in a previous year, in period 13, don't worry about subtracting.
*
* THIS IS CLEARLY INCORRECT - THE CONDITION ABOVE APPLIES *ONLY* IN THE FIRST FORWARDING PERIOD, NOT IN EVERY FORWARDING PERIOD.
* IF THE METHOD IS ONLY CALLED FROM AUTOGENERATE BUDGETS, THIS IS PROBABLY INCONSEQUENTIAL.
*/
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
(APeriodNumber - 1),
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
retVal = currencyAmount;
}
finally
{
if (newTransaction)
{
dBTransaction.Rollback();
}
}
return(retVal);
}
/// <summary>
/// get all partners that we want to display on the current birthday report
/// </summary>
public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string> Defines = new SortedList <string, string>();
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
if (AParameters.Exists("FamilyKey"))
{
SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal)
{
Value = AParameters.Get("FamilyKey").ToDecimal()
});
Defines.Add("BYFAMILYKEY", string.Empty);
}
else
{
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
if (AParameters.Get("param_chkSelectTypes").ToBool() == true)
{
string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' });
string FilterForTypes = string.Empty;
foreach (string type in types)
{
if (FilterForTypes.Length > 0)
{
FilterForTypes += " OR ";
}
FilterForTypes += "pptype.p_type_code_c = ?";
SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar)
{
Value = type
});
}
Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")");
}
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate();
DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate();
if (FromDate.DayOfYear < ToDate.DayOfYear)
{
Defines.Add("WITHDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
}
else
{
Defines.Add("WITHOUTDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
}
}
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return(null);
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines);
Boolean NewTransaction;
TDataBase db = DBAccess.Connect("CalculateBirthdays");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(null);
}
// if end date is not set, use the end of this year
DateTime AgeDay = DateTime.Now;
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
AgeDay = AParameters.Get("param_dtpToDate").ToDate();
}
else
{
AgeDay = new DateTime(AgeDay.Year, 12, 31);
}
// Calculate the age, in new column
resultTable.Columns.Add(new DataColumn("age", typeof(Int32)));
foreach (DataRow r in resultTable.Rows)
{
int age = 0;
if (r["DOB"] != DBNull.Value)
{
DateTime BDay = Convert.ToDateTime(r["DOB"]);
age = AgeDay.Year - BDay.Year;
}
r["Age"] = age;
}
// filter by anniversaries?
if ((AParameters.Get("param_chkAnniversaries").ToBool() == true) &&
!AParameters.Get("param_txtAnniversaries").IsZeroOrNull())
{
List <string> anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' }));
List <DataRow> RowsToDelete = new List <DataRow>();
foreach (DataRow r in resultTable.Rows)
{
if (!anniversaries.Contains(r["Age"].ToString()))
{
RowsToDelete.Add(r);
}
}
foreach (DataRow r in RowsToDelete)
{
resultTable.Rows.Remove(r);
}
}
return(resultTable);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(null);
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
}
public static bool CreateUser(string AUsername, string APassword, string AFirstName, string AFamilyName,
string AModulePermissions, string AClientComputerName, string AClientIPAddress, TDBTransaction ATransaction = null)
{
TDataBase DBConnectionObj = DBAccess.GetDBAccessObj(ATransaction);
TDBTransaction ReadWriteTransaction = null;
bool SeparateDBConnectionEstablished = false;
bool NewTransaction;
bool SubmissionOK = false;
// TODO: check permissions. is the current user allowed to create other users?
SUserTable userTable = new SUserTable();
SUserRow newUser = userTable.NewRowTyped();
newUser.UserId = AUsername;
newUser.FirstName = AFirstName;
newUser.LastName = AFamilyName;
if (AUsername.Contains("@"))
{
newUser.EmailAddress = AUsername;
newUser.UserId = AUsername.Substring(0, AUsername.IndexOf("@")).
Replace(".", string.Empty).
Replace("_", string.Empty).ToUpper();
}
if (DBConnectionObj == null)
{
// ATransaction was null and GDBAccess is also null: we need to establish a DB Connection manually here!
DBConnectionObj = DBAccess.SimpleEstablishDBConnection("CreateUser");
SeparateDBConnectionEstablished = true;
}
ReadWriteTransaction = DBConnectionObj.GetNewOrExistingTransaction(
IsolationLevel.Serializable, out NewTransaction, "CreateUser");
try
{
// Check whether the user that we are asked to create already exists
if (SUserAccess.Exists(newUser.UserId, ReadWriteTransaction))
{
TLogging.Log("Cannot create new user because a user with User Name '" + newUser.UserId + "' already exists!");
return(false);
}
newUser.PwdSchemeVersion = TPasswordHelper.CurrentPasswordSchemeNumber;
userTable.Rows.Add(newUser);
string UserAuthenticationMethod = TAppSettingsManager.GetValue("UserAuthenticationMethod", "OpenPetraDBSUser", false);
if (UserAuthenticationMethod == "OpenPetraDBSUser")
{
if (APassword.Length > 0)
{
SetNewPasswordHashAndSaltForUser(newUser, APassword, AClientComputerName, AClientIPAddress, ReadWriteTransaction);
if (AModulePermissions != TMaintenanceWebConnector.DEMOMODULEPERMISSIONS)
{
newUser.PasswordNeedsChange = true;
}
}
}
else
{
try
{
IUserAuthentication auth = TUserManagerWebConnector.LoadAuthAssembly(UserAuthenticationMethod);
if (!auth.CreateUser(AUsername, APassword, AFirstName, AFamilyName))
{
newUser = null;
}
}
catch (Exception e)
{
TLogging.Log("Problem loading user authentication method " + UserAuthenticationMethod + ": " + e.ToString());
return(false);
}
}
if (newUser != null)
{
SUserAccess.SubmitChanges(userTable, ReadWriteTransaction);
List <string> modules = new List <string>();
if (AModulePermissions == DEMOMODULEPERMISSIONS)
{
modules.Add("PTNRUSER");
modules.Add("FINANCE-1");
ALedgerTable theLedgers = ALedgerAccess.LoadAll(ReadWriteTransaction);
foreach (ALedgerRow ledger in theLedgers.Rows)
{
modules.Add("LEDGER" + ledger.LedgerNumber.ToString("0000"));
}
}
else
{
string[] modulePermissions = AModulePermissions.Split(new char[] { ',' });
foreach (string s in modulePermissions)
{
if (s.Trim().Length > 0)
{
modules.Add(s.Trim());
}
}
}
SUserModuleAccessPermissionTable moduleAccessPermissionTable = new SUserModuleAccessPermissionTable();
foreach (string module in modules)
{
SUserModuleAccessPermissionRow moduleAccessPermissionRow = moduleAccessPermissionTable.NewRowTyped();
moduleAccessPermissionRow.UserId = newUser.UserId;
moduleAccessPermissionRow.ModuleId = module;
moduleAccessPermissionRow.CanAccess = true;
moduleAccessPermissionTable.Rows.Add(moduleAccessPermissionRow);
}
SUserModuleAccessPermissionAccess.SubmitChanges(moduleAccessPermissionTable, ReadWriteTransaction);
// TODO: table permissions should be set by the module list
// TODO: add p_data_label... tables here so user can generally have access
string[] tables = new string[] {
"p_bank", "p_church", "p_family", "p_location",
"p_organisation", "p_partner", "p_partner_location",
"p_partner_type", "p_person", "p_unit", "p_venue",
"p_data_label", "p_data_label_lookup", "p_data_label_lookup_category", "p_data_label_use", "p_data_label_value_partner",
};
SUserTableAccessPermissionTable tableAccessPermissionTable = new SUserTableAccessPermissionTable();
foreach (string table in tables)
{
SUserTableAccessPermissionRow tableAccessPermissionRow = tableAccessPermissionTable.NewRowTyped();
tableAccessPermissionRow.UserId = newUser.UserId;
tableAccessPermissionRow.TableName = table;
tableAccessPermissionTable.Rows.Add(tableAccessPermissionRow);
}
SUserTableAccessPermissionAccess.SubmitChanges(tableAccessPermissionTable, ReadWriteTransaction);
TUserAccountActivityLog.AddUserAccountActivityLogEntry(newUser.UserId,
TUserAccountActivityLog.USER_ACTIVITY_USER_RECORD_CREATED,
String.Format(Catalog.GetString("The user record for the new user {0} got created by user {1}. "),
newUser.UserId, UserInfo.GUserInfo.UserID) +
String.Format(ResourceTexts.StrRequestCallerInfo, AClientComputerName, AClientIPAddress),
ReadWriteTransaction);
SubmissionOK = true;
return(true);
}
}
finally
{
if (NewTransaction)
{
if (SubmissionOK)
{
ReadWriteTransaction.DataBaseObj.CommitTransaction();
}
else
{
ReadWriteTransaction.DataBaseObj.RollbackTransaction();
}
if (SeparateDBConnectionEstablished)
{
DBConnectionObj.CloseDBConnection();
}
}
}
return(false);
}
public static DataTable FindContacts(string AContactor,
DateTime?AContactDate,
string ACommentContains,
string AMethodOfContact,
string AModuleID,
string AMailingCode,
PPartnerContactAttributeTable AContactAttributes)
{
Boolean NewTransaction;
DataTable Contacts = new DataTable();
TDataBase db = DBAccess.Connect("FindContacts");
TDBTransaction WriteTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
try
{
string Query = "SELECT p_contact_log.*, p_partner_contact.p_partner_key_n, p_partner.p_partner_short_name_c" +
" FROM p_contact_log, p_partner_contact, p_partner" +
" WHERE" +
" p_partner_contact.p_contact_log_id_i = p_contact_log.p_contact_log_id_i" +
" AND p_partner.p_partner_key_n = p_partner_contact.p_partner_key_n";
if (AContactor.Length > 0)
{
Query += " AND p_contact_log.p_contactor_c = '" + AContactor + "'";
}
if (AContactDate.HasValue)
{
Query += " AND p_contact_log.s_contact_date_d = '" + AContactDate + "'";
}
if (AMethodOfContact.Length > 0)
{
Query += " AND p_contact_log.p_contact_code_c = '" + AMethodOfContact + "'";
}
if (AModuleID.Length > 0)
{
Query += " AND p_contact_log.s_module_id_c = '" + AModuleID + "'";
}
if (AMailingCode.Length > 0)
{
Query += " AND p_contact_log.p_mailing_code_c = '" + AMailingCode + "'";
}
if (ACommentContains.Length > 0)
{
Query += " AND p_contact_log.p_contact_comment_c LIKE '%" + ACommentContains + "%'";
}
if ((AContactAttributes != null) && (AContactAttributes.Rows.Count > 0))
{
Query += " AND EXISTS (SELECT * " +
" FROM p_partner_contact_attribute" +
" WHERE" +
" p_partner_contact_attribute.p_contact_id_i = p_contact_log.p_contact_log_id_i" +
" AND (";
foreach (PPartnerContactAttributeRow Row in AContactAttributes.Rows)
{
Query += " (p_partner_contact_attribute.p_contact_attribute_code_c = '" + Row.ContactAttributeCode + "'" +
" AND p_partner_contact_attribute.p_contact_attr_detail_code_c = '" + Row.ContactAttrDetailCode + "') OR";
}
// remove the final " OR"
Query = Query.Substring(0, Query.Length - 3) + "))";
}
WriteTransaction.DataBaseObj.SelectDT(Contacts, Query, WriteTransaction);
Contacts.PrimaryKey = new DataColumn[] {
Contacts.Columns["p_partner_key_n"], Contacts.Columns["p_contact_log_id_i"]
};
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
if (NewTransaction)
{
WriteTransaction.Rollback();
}
return(Contacts);
}
