/// <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);
}
/// <summary>
/// Retrieves the Partner ShortName, the PartnerClass and PartnerStatus.
/// </summary>
/// <param name="APartnerKey">PartnerKey to identify the Partner.</param>
/// <param name="APartnerShortName">Returns the ShortName.</param>
/// <param name="APartnerClass">Returns the PartnerClass (FAMILY, ORGANISATION, etc).</param>
/// <param name="APartnerStatus">Returns the PartnerStatus (eg. ACTIVE, DIED).</param>
/// <param name="ATransaction">Open DB Transaction.</param>
/// <returns>True if partner was found, otherwise false.</returns>
public static Boolean RetrievePartnerShortName(Int64 APartnerKey,
out String APartnerShortName,
out TPartnerClass APartnerClass,
out TStdPartnerStatusCode APartnerStatus,
TDBTransaction ATransaction)
{
Boolean ReturnValue;
StringCollection RequiredColumns;
PPartnerTable PartnerTable;
// initialise out Arguments
APartnerShortName = "";
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerClass = TPartnerClass.FAMILY;
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerStatus = TStdPartnerStatusCode.spscINACTIVE;
if (APartnerKey != 0)
{
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ATransaction, null, 0, 0);
if (PartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
// since we loaded by primary key there must just be one partner row
APartnerShortName = PartnerTable[0].PartnerShortName;
APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
APartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
ReturnValue = true;
}
}
else
{
// Return result as valid if Partner Key is 0.
ReturnValue = true;
}
return(ReturnValue);
}
/// <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>
public static PPartnerRow CheckPartnerExists2(Int64 APartnerKey, bool AMustNotBeMergedPartner)
{
PPartnerRow ReturnValue = null;
TDBTransaction ReadTransaction;
Boolean NewTransaction;
PPartnerTable PartnerTable;
if (APartnerKey != 0)
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "CheckPartnerExists: committed own transaction.");
}
}
if (PartnerTable.Rows.Count != 0)
{
if (AMustNotBeMergedPartner)
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(
PartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
{
ReturnValue = PartnerTable[0];
}
}
else
{
ReturnValue = PartnerTable[0];
}
}
}
return(ReturnValue);
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AKeyCount">The number of keys that were actually added to the extract (any duplicates are excluded)</param>
/// <param name="AIgnoredKeyList">A reference to a List of long. If not null the server will fill it with the partner keys that were ignored. Does not include duplicates.</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
/// <param name="AIgnoreInactive">true if inactive partners should be ignored</param>
/// <param name="AIgnoreNonMailingLocations">true to ignore if the partner's best address is a non-mailing location</param>
/// <param name="AIgnoreNoSolicitations">true to ignore partners where the No Solicitations flag is set</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
out Int32 AKeyCount,
out List <long> AIgnoredKeyList,
bool AIgnoreDuplicates,
bool AIgnoreInactive,
bool AIgnoreNonMailingLocations,
bool AIgnoreNoSolicitations)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
List <long> ignoredKeyList = new List <long>();
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
// Pre-process the table to remove partner rows that do not match the filter requirements
for (int i = APartnerKeysTable.Rows.Count - 1; i >= 0; i--)
{
DataRow dr = APartnerKeysTable.Rows[i];
Int64 partnerKey = Convert.ToInt64(dr[APartnerKeyColumn]);
// Get a partner record containing our fields of interest
StringCollection fields = new StringCollection();
fields.Add(PPartnerTable.GetStatusCodeDBName());
fields.Add(PPartnerTable.GetNoSolicitationsDBName());
DataTable dt = PPartnerAccess.LoadByPrimaryKey(partnerKey, fields, Transaction);
if (dt.Rows.Count > 0)
{
if (AIgnoreInactive || AIgnoreNoSolicitations)
{
bool isActive = false;
bool isNoSolicitation = false;
object o = dt.Rows[0][PPartnerTable.GetStatusCodeDBName()];
if (o != null)
{
TStdPartnerStatusCode statusCode = SharedTypes.StdPartnerStatusCodeStringToEnum(o.ToString());
isActive = (statusCode == TStdPartnerStatusCode.spscACTIVE);
}
o = dt.Rows[0][PPartnerTable.GetNoSolicitationsDBName()];
if (o != null)
{
isNoSolicitation = Convert.ToBoolean(o);
}
if ((AIgnoreInactive && !isActive) || (AIgnoreNoSolicitations && isNoSolicitation))
{
ignoredKeyList.Add(partnerKey);
APartnerKeysTable.Rows.Remove(dr);
}
}
}
else
{
ignoredKeyList.Add(partnerKey);
}
}
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
Transaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = PartnerLocationRow.PartnerKey;
if (PartnerKey > 0)
{
if (AIgnoreNonMailingLocations)
{
// The PartnerLocationRow only contains the PK fields so now we need to get the SendMail column
StringCollection fields = new StringCollection();
fields.Add(PPartnerLocationTable.GetSendMailDBName());
PPartnerLocationTable t =
PPartnerLocationAccess.LoadByPrimaryKey(PartnerKey, PartnerLocationRow.SiteKey,
PartnerLocationRow.LocationKey, fields, Transaction);
if ((t != null) && (t.Rows.Count > 0) && (((PPartnerLocationRow)t.Rows[0]).SendMail == false))
{
ignoredKeyList.Add(PartnerKey);
continue;
}
}
RecordCounter += 1;
TLogging.LogAtLevel(1, "Preparing Partner " + PartnerKey + " (Record Number " + RecordCounter + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, Transaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, Transaction);
}
});
AKeyCount = RecordCounter;
AIgnoredKeyList = ignoredKeyList;
}
public static string CreateAnnualGiftReceipts(Int32 ALedgerNumber,
DateTime AStartDate,
DateTime AEndDate,
string AHTMLTemplate,
bool ADeceasedFirst = false,
string AExtract = null,
Int64 ADonorKey = 0)
{
TLanguageCulture.LoadLanguageAndCulture();
// get BaseCurrency
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
ALedgerTable LedgerTable = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable);
string BaseCurrency = LedgerTable[0].BaseCurrency;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
// get the local country code
string LocalCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(Transaction);
DataTable donorkeys = new DataTable();
string SqlStmt = "";
if (ADonorKey != 0)
{
TPartnerClass Class;
string ShortName;
TPartnerServerLookups.GetPartnerShortName(ADonorKey, out ShortName, out Class);
donorkeys.Columns.Add(new DataColumn("DonorKey"));
donorkeys.Columns.Add(new DataColumn("DonorName"));
DataRow SingleRow = donorkeys.NewRow();
SingleRow[0] = ADonorKey;
SingleRow[1] = ShortName;
donorkeys.Rows.Add(SingleRow);
}
else
{
SortedList <string, string> Defines = new SortedList <string, string>();
if (!string.IsNullOrEmpty(AExtract))
{
Defines.Add("BYEXTRACT", string.Empty);
}
// first get all donors in the given date range
SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonors.sql", Defines);
OdbcParameter[] parameters = new OdbcParameter[4];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("StartDate", OdbcType.Date);
parameters[1].Value = AStartDate;
parameters[2] = new OdbcParameter("EndDate", OdbcType.Date);
parameters[2].Value = AEndDate;
parameters[3] = new OdbcParameter("Extract", OdbcType.VarChar);
parameters[3].Value = AExtract;
donorkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "DonorKeys", Transaction, parameters);
// put deceased partner's at the front (still sorted alphabetically)
if (ADeceasedFirst)
{
// create a new datatable with same structure as donorkeys
DataTable temp = donorkeys.Clone();
temp.Clear();
// add deceased donors to the temp table and delete from donorkeys
for (int i = 0; i < donorkeys.Rows.Count; i++)
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(donorkeys.Rows[i][2].ToString()) == TStdPartnerStatusCode.spscDIED)
{
temp.Rows.Add((object[])donorkeys.Rows[i].ItemArray.Clone());
donorkeys.Rows[i].Delete();
}
}
// add remaining partners to temp table
donorkeys.AcceptChanges();
temp.Merge(donorkeys);
donorkeys = temp;
}
}
string ResultDocument = "";
SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonationsOfDonor.sql");
foreach (DataRow donorrow in donorkeys.Rows)
{
Int64 donorKey = Convert.ToInt64(donorrow[0]);
string donorName = donorrow[1].ToString();
OdbcParameter[] parameters = new OdbcParameter[4];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("StartDate", OdbcType.Date);
parameters[1].Value = AStartDate;
parameters[2] = new OdbcParameter("EndDate", OdbcType.Date);
parameters[2].Value = AEndDate;
parameters[3] = new OdbcParameter("DonorKey", OdbcType.BigInt);
parameters[3].Value = donorKey;
// TODO: should we print each gift detail, or just one row per gift?
DataTable donations = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "Donations", Transaction, parameters);
if (donations.Rows.Count > 0)
{
string letter = FormatLetter(donorKey, donorName, donations, BaseCurrency, AHTMLTemplate, LocalCountryCode, Transaction);
if (TFormLettersTools.AttachNextPage(ref ResultDocument, letter))
{
// TODO: store somewhere that the receipt has been printed?
// TODO also store each receipt with the donor in document management, and in contact management?
}
}
}
TFormLettersTools.CloseDocument(ref ResultDocument);
return(ResultDocument);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
public static Boolean MergedPartnerDetails(Int64 AMergedPartnerPartnerKey,
out String AMergedPartnerPartnerShortName,
out TPartnerClass AMergedPartnerPartnerClass,
out Int64 AMergedIntoPartnerKey,
out String AMergedIntoPartnerShortName,
out TPartnerClass AMergedIntoPartnerClass,
out String AMergedBy,
out DateTime AMergeDate)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
StringCollection RequiredColumns;
PPartnerTable MergedPartnerTable;
PPartnerTable PartnerMergedIntoTable;
PPartnerMergeTable PartnerMergeTable;
Boolean ReturnValue = false;
// Initialise out Arguments
AMergedPartnerPartnerShortName = "";
AMergedPartnerPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AMergedIntoPartnerKey = -1;
AMergedIntoPartnerShortName = "";
AMergedIntoPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AMergedBy = "";
AMergeDate = DateTime.MinValue;
if (AMergedPartnerPartnerKey != 0)
{
/*
* First we look up the Partner that was Merged to get some details of it.
*/
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
MergedPartnerTable = PPartnerAccess.LoadByPrimaryKey(
AMergedPartnerPartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);
if (MergedPartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(
MergedPartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
{
ReturnValue = false;
}
else
{
AMergedPartnerPartnerShortName = MergedPartnerTable[0].PartnerShortName;
AMergedPartnerPartnerClass = SharedTypes.PartnerClassStringToEnum(MergedPartnerTable[0].PartnerClass);
/*
* Now we look up the Partner that was Merged in the PartnerMerge DB Table
* to get the information about the Merged-Into Partner.
*/
PartnerMergeTable = PPartnerMergeAccess.LoadByPrimaryKey(AMergedPartnerPartnerKey, ReadTransaction);
if (PartnerMergeTable.Rows.Count == 0)
{
/*
* Although we didn't find the Merged Partner in the PartnerMerge
* DB Table it is still a valid, Merged Partner, so we return true
* in this case.
*/
ReturnValue = true;
}
else
{
/*
* Now we look up the Merged-Into Partner to get some details of it.
*/
PartnerMergedIntoTable = PPartnerAccess.LoadByPrimaryKey(
PartnerMergeTable[0].MergeTo, RequiredColumns,
ReadTransaction, null, 0, 0);
if (PartnerMergedIntoTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
AMergedIntoPartnerKey = PartnerMergeTable[0].MergeTo;
AMergedBy = PartnerMergeTable[0].MergedBy;
AMergeDate = PartnerMergeTable[0].MergeDate.Value;
AMergedIntoPartnerShortName = PartnerMergedIntoTable[0].PartnerShortName;
AMergedIntoPartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerMergedIntoTable[0].PartnerClass);
ReturnValue = true;
}
}
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.MergedPartnerDetails: committed own transaction.");
}
}
}
return(ReturnValue);
}
public static Boolean VerifyPartnerAndGetDetails(Int64 APartnerKey,
out String APartnerShortName,
out TPartnerClass APartnerClass,
out Boolean AIsMergedPartner,
out Boolean AUserCanAccessPartner)
{
APartnerShortName = "";
APartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AIsMergedPartner = false;
AUserCanAccessPartner = false;
TDBTransaction ReadTransaction;
Boolean NewTransaction;
StringCollection RequiredColumns;
PPartnerTable PartnerTable;
Boolean ReturnValue = true;
TStdPartnerStatusCode PartnerStatus = TStdPartnerStatusCode.spscACTIVE;
// initialise outout Arguments
if (APartnerKey != 0)
{
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
RequiredColumns.Add(PPartnerTable.GetRestrictedDBName());
RequiredColumns.Add(PPartnerTable.GetUserIdDBName());
RequiredColumns.Add(PPartnerTable.GetGroupIdDBName());
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.VerifyPartner: committed own transaction.");
}
}
if (PartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
// since we loaded by primary key there must just be one partner row
APartnerShortName = PartnerTable[0].PartnerShortName;
APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
PartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
// check if user can access partner
if (Ict.Petra.Server.MPartner.Common.TSecurity.CanAccessPartner(PartnerTable[0]) == TPartnerAccessLevelEnum.palGranted)
{
AUserCanAccessPartner = true;
}
// check if partner is merged
if (PartnerStatus == TStdPartnerStatusCode.spscMERGED)
{
AIsMergedPartner = true;
}
else
{
AIsMergedPartner = false;
}
ReturnValue = true;
}
}
else
{
// Return result as valid if Partner Key is 0.
ReturnValue = true;
}
return(ReturnValue);
}
