/// <summary>
/// Adds a Partner to an Extract, if they are not already present
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner</param>
/// <param name="ALocationPK">Location PK of a Partner's PartnerLocation
/// (usually the LocationPK of the 'Best Address' of the Partner).</param>
/// <param name="AExtractId">ExtractId of the Extract that the Partner should
/// get added to.</param>
/// <returns>True if the Partner was added to the Extract, otherwise false.</returns>
public static bool AddPartnerToExtract(Int64 APartnerKey,
TLocationPK ALocationPK, int AExtractId)
{
TDBTransaction Transaction = null;
bool SubmissionOK = false;
MExtractTable TemplateTable;
MExtractRow TemplateRow;
MExtractRow NewRow;
if (APartnerKey > 0)
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
/*
* First check whether the Partner isn't already in that Extract
*/
TemplateTable = new MExtractTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.ExtractId = AExtractId;
TemplateRow.PartnerKey = APartnerKey;
if (MExtractAccess.CountUsingTemplate(TemplateRow, null, Transaction) == 0)
{
/*
* Add Partner to Extract.
*/
NewRow = TemplateTable.NewRowTyped(false);
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = APartnerKey;
NewRow.SiteKey = ALocationPK.SiteKey;
NewRow.LocationKey = ALocationPK.LocationKey;
TemplateTable.Rows.Add(NewRow);
MExtractAccess.SubmitChanges(TemplateTable, Transaction);
SubmissionOK = true;
}
else
{
// Partner is already in that Extract -> Partner does not get added.
SubmissionOK = false;
}
});
}
else
{
// Invalid PartnerKey -> return false;
SubmissionOK = false;
}
return(SubmissionOK);
}
/// <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="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>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
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 = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
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);
}
});
}
/// <summary>
/// Adds a Partner to an Extract, if they are not already present
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner</param>
/// <param name="ALocationPK">Location PK of a Partner's PartnerLocation
/// (usually the LocationPK of the 'Best Address' of the Partner).</param>
/// <param name="AExtractId">ExtractId of the Extract that the Partner should
/// get added to.</param>
/// <returns>True if the Partner was added to the Extract, otherwise false.</returns>
public static bool AddPartnerToExtract(Int64 APartnerKey,
TLocationPK ALocationPK, int AExtractId)
{
TDBTransaction Transaction = null;
bool SubmissionOK = false;
MExtractTable TemplateTable;
MExtractRow TemplateRow;
MExtractRow NewRow;
if (APartnerKey > 0)
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
/*
* First check whether the Partner isn't already in that Extract
*/
TemplateTable = new MExtractTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.ExtractId = AExtractId;
TemplateRow.PartnerKey = APartnerKey;
if (MExtractAccess.CountUsingTemplate(TemplateRow, null, Transaction) == 0)
{
/*
* Add Partner to Extract.
*/
NewRow = TemplateTable.NewRowTyped(false);
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = APartnerKey;
NewRow.SiteKey = ALocationPK.SiteKey;
NewRow.LocationKey = ALocationPK.LocationKey;
TemplateTable.Rows.Add(NewRow);
MExtractAccess.SubmitChanges(TemplateTable, Transaction);
SubmissionOK = true;
}
else
{
// Partner is already in that Extract -> Partner does not get added.
SubmissionOK = false;
}
});
}
else
{
// Invalid PartnerKey -> return false;
SubmissionOK = false;
}
return SubmissionOK;
}
/// <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;
}
/// <summary>
/// Adds a Partner to an Extract, if they are not already present
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner</param>
/// <param name="ALocationPK">Location PK of a Partner's PartnerLocation
/// (usually the LocationPK of the 'Best Address' of the Partner).</param>
/// <param name="AExtractId">ExtractId of the Extract that the Partner should
/// get added to.</param>
/// <returns>True if the Partner was added to the Extract, otherwise false.</returns>
public static bool AddPartnerToExtract(Int64 APartnerKey,
TLocationPK ALocationPK, int AExtractId)
{
bool ReturnValue;
TDBTransaction WriteTransaction;
MExtractTable TemplateTable;
MExtractRow TemplateRow;
MExtractRow NewRow;
Boolean NewTransaction;
if (APartnerKey > 0)
{
WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
/*
* First check whether the Partner isn't already in that Extract
*/
TemplateTable = new MExtractTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.ExtractId = AExtractId;
TemplateRow.PartnerKey = APartnerKey;
if (MExtractAccess.CountUsingTemplate(TemplateRow, null, WriteTransaction) == 0)
{
/*
* Add Partner to Extract.
*/
NewRow = TemplateTable.NewRowTyped(false);
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = APartnerKey;
NewRow.SiteKey = ALocationPK.SiteKey;
NewRow.LocationKey = ALocationPK.LocationKey;
TemplateTable.Rows.Add(NewRow);
MExtractAccess.SubmitChanges(TemplateTable, WriteTransaction);
ReturnValue = true;
}
else
{
// Partner is already in that Extract -> Partner does not get added.
ReturnValue = false;
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(8, "TExtractsHandling.AddPartnerToExtract: committed own transaction!");
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while adding a Partner to an Extract:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
else
{
// Invalid PartnerKey -> return false;
ReturnValue = false;
}
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="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="ACommitTransaction">true if transaction should be committed at end of method</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates,
bool ACommitTransaction)
{
Boolean NewTransaction;
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, WriteTransaction);
// 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,
WriteTransaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.Log("Preparing Partner " + PartnerKey.ToString() + " (Record Number " + RecordCounter.ToString() + ")");
// 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, WriteTransaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, WriteTransaction);
}
if (ACommitTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while extending an Extract from a list of Partner Keys:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
public static Boolean IntersectExtracts(String ANewExtractName,
String ANewExtractDescription,
List <Int32>AIntersectExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
Int32 ExtractIndex;
MExtractTable FirstExtractTable;
MExtractTable IntersectedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Boolean PartnerExistsInAllExtracts;
Int32 NewExtractId;
NewExtractId = -1;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out NewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
if (AIntersectExtractIdList.Count > 0)
{
FirstExtractTable = MExtractAccess.LoadViaMExtractMaster(AIntersectExtractIdList[0], Transaction);
// iterate through all partners in first extract and check if this record also exists in all other extracts
foreach (DataRow ExtractRow in FirstExtractTable.Rows)
{
PartnerExistsInAllExtracts = true;
// now check if this partner record exists in all other extracts as well
for (ExtractIndex = 1;
ExtractIndex < AIntersectExtractIdList.Count && PartnerExistsInAllExtracts;
ExtractIndex++)
{
if (!MExtractAccess.Exists(AIntersectExtractIdList[ExtractIndex], ((MExtractRow)ExtractRow).PartnerKey,
((MExtractRow)ExtractRow).SiteKey, Transaction))
{
// can stop here as there is at least one extract where partner does not exist
PartnerExistsInAllExtracts = false;
}
}
// create and add row to intersected extract as it exists in all extracts
if (PartnerExistsInAllExtracts)
{
TemplateRow = (MExtractRow)IntersectedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = NewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
IntersectedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable IntersectedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(NewExtractId, Transaction);
IntersectedExtractMaster[0].KeyCount = IntersectedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(IntersectedExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(IntersectedExtractMaster, Transaction);
}
SubmissionOK = true;
});
ResultValue = SubmissionOK;
ANewExtractId = NewExtractId;
return ResultValue;
}
public static Boolean SubtractExtracts(String ANewExtractName,
String ANewExtractDescription,
String ABaseExtractName,
List <Int32>ASubtractExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
MExtractTable ExtractTable;
MExtractMasterTable BaseExtractMasterTable;
MExtractTable BaseExtractTable;
MExtractTable SubtractedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Int32 NewExtractId;
List <Int64>SubtractPartnerKeyList = new List <Int64>();
NewExtractId = -1;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out NewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
// first create a table that contains all partners to be subtracted
foreach (Int32 ExtractId in ASubtractExtractIdList)
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(ExtractId, Transaction);
foreach (DataRow ExtractRow in ExtractTable.Rows)
{
if (!SubtractPartnerKeyList.Exists(item => item == ((MExtractRow)ExtractRow).PartnerKey))
{
SubtractPartnerKeyList.Add(((MExtractRow)ExtractRow).PartnerKey);
}
}
}
if (ASubtractExtractIdList.Count > 0)
{
BaseExtractMasterTable = MExtractMasterAccess.LoadByUniqueKey(ABaseExtractName, Transaction);
BaseExtractTable = MExtractAccess.LoadViaMExtractMaster(((MExtractMasterRow)BaseExtractMasterTable.Rows[0]).ExtractId,
Transaction);
// iterate through all partners in base extract and check if this record also exists in extracts to be subtracted
foreach (DataRow ExtractRow in BaseExtractTable.Rows)
{
if (!SubtractPartnerKeyList.Exists(item => item == ((MExtractRow)ExtractRow).PartnerKey))
{
// if partner key does not exist in list to subtract then add it to result extract
TemplateRow = (MExtractRow)SubtractedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = NewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
SubtractedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable IntersectedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(NewExtractId, Transaction);
IntersectedExtractMaster[0].KeyCount = SubtractedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(SubtractedExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(IntersectedExtractMaster, Transaction);
}
SubmissionOK = true;
});
ResultValue = SubmissionOK;
ANewExtractId = NewExtractId;
return ResultValue;
}
/// <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="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="ACommitTransaction">true if transaction should be committed at end of method</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates,
bool ACommitTransaction)
{
Boolean NewTransaction;
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, WriteTransaction);
// 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,
WriteTransaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.Log("Preparing Partner " + PartnerKey.ToString() + " (Record Number " + RecordCounter.ToString() + ")");
// 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, WriteTransaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, WriteTransaction);
}
if (ACommitTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while extending an Extract from a list of Partner Keys:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
public static Boolean CombineExtracts(String ANewExtractName,
String ANewExtractDescription,
List <Int32>ACombineExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
MExtractTable ExtractTable;
MExtractTable CombinedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Int32 NewExtractId;
NewExtractId = -1;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out NewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
// loop through each extract and combine them
foreach (Int32 ExtractId in ACombineExtractIdList)
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(ExtractId, Transaction);
foreach (DataRow ExtractRow in ExtractTable.Rows)
{
if (CombinedExtractTable.Rows.Find(new object[] { NewExtractId,
((MExtractRow)ExtractRow).PartnerKey,
((MExtractRow)ExtractRow).SiteKey }) == null)
{
// create and add row to combined extract as it does not exist yet
TemplateRow = (MExtractRow)CombinedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = NewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
CombinedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable CombinedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(NewExtractId, Transaction);
CombinedExtractMaster[0].KeyCount = CombinedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(CombinedExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(CombinedExtractMaster, Transaction);
}
SubmissionOK = true;
});
ResultValue = SubmissionOK;
ANewExtractId = NewExtractId;
return ResultValue;
}
/// <summary>
/// Adds a Partner to an Extract, if they are not already present
/// </summary>
/// <param name="APartnerKey">PartnerKey of Partner</param>
/// <param name="ALocationPK">Location PK of a Partner's PartnerLocation
/// (usually the LocationPK of the 'Best Address' of the Partner).</param>
/// <param name="AExtractId">ExtractId of the Extract that the Partner should
/// get added to.</param>
/// <returns>True if the Partner was added to the Extract, otherwise false.</returns>
public static bool AddPartnerToExtract(Int64 APartnerKey,
TLocationPK ALocationPK, int AExtractId)
{
bool ReturnValue;
TDBTransaction WriteTransaction;
MExtractTable TemplateTable;
MExtractRow TemplateRow;
MExtractRow NewRow;
Boolean NewTransaction;
if (APartnerKey > 0)
{
WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
/*
* First check whether the Partner isn't already in that Extract
*/
TemplateTable = new MExtractTable();
TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.ExtractId = AExtractId;
TemplateRow.PartnerKey = APartnerKey;
if (MExtractAccess.CountUsingTemplate(TemplateRow, null, WriteTransaction) == 0)
{
/*
* Add Partner to Extract.
*/
NewRow = TemplateTable.NewRowTyped(false);
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = APartnerKey;
NewRow.SiteKey = ALocationPK.SiteKey;
NewRow.LocationKey = ALocationPK.LocationKey;
TemplateTable.Rows.Add(NewRow);
MExtractAccess.SubmitChanges(TemplateTable, WriteTransaction);
ReturnValue = true;
}
else
{
// Partner is already in that Extract -> Partner does not get added.
ReturnValue = false;
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(8, "TExtractsHandling.AddPartnerToExtract: committed own transaction!");
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while adding a Partner to an Extract:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
else
{
// Invalid PartnerKey -> return false;
ReturnValue = false;
}
return ReturnValue;
}
public static Boolean SubtractExtracts(String ANewExtractName,
String ANewExtractDescription,
String ABaseExtractName,
List <Int32>ASubtractExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
MExtractTable ExtractTable;
MExtractMasterTable BaseExtractMasterTable;
MExtractTable BaseExtractTable;
MExtractTable SubtractedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Boolean NewTransaction;
List <Int64>SubtractPartnerKeyList = new List <Int64>();
ANewExtractId = -1;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out ANewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
// first create a table that contains all partners to be subtracted
foreach (Int32 ExtractId in ASubtractExtractIdList)
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(ExtractId, WriteTransaction);
foreach (DataRow ExtractRow in ExtractTable.Rows)
{
if (!SubtractPartnerKeyList.Exists(item => item == ((MExtractRow)ExtractRow).PartnerKey))
{
SubtractPartnerKeyList.Add(((MExtractRow)ExtractRow).PartnerKey);
}
}
}
if (ASubtractExtractIdList.Count > 0)
{
BaseExtractMasterTable = MExtractMasterAccess.LoadByUniqueKey(ABaseExtractName, WriteTransaction);
BaseExtractTable = MExtractAccess.LoadViaMExtractMaster(((MExtractMasterRow)BaseExtractMasterTable.Rows[0]).ExtractId,
WriteTransaction);
// iterate through all partners in base extract and check if this record also exists in extracts to be subtracted
foreach (DataRow ExtractRow in BaseExtractTable.Rows)
{
if (!SubtractPartnerKeyList.Exists(item => item == ((MExtractRow)ExtractRow).PartnerKey))
{
// if partner key does not exist in list to subtract then add it to result extract
TemplateRow = (MExtractRow)SubtractedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = ANewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
SubtractedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable IntersectedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(ANewExtractId, WriteTransaction);
IntersectedExtractMaster[0].KeyCount = SubtractedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(SubtractedExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(IntersectedExtractMaster, WriteTransaction);
ResultValue = true;
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the subtraction of Extracts:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
return ResultValue;
}
public static Boolean IntersectExtracts(String ANewExtractName,
String ANewExtractDescription,
List <Int32>AIntersectExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
Int32 ExtractIndex;
MExtractTable FirstExtractTable;
MExtractTable IntersectedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Boolean NewTransaction;
Boolean PartnerExistsInAllExtracts;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
ANewExtractId = -1;
try
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out ANewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
if (AIntersectExtractIdList.Count > 0)
{
FirstExtractTable = MExtractAccess.LoadViaMExtractMaster(AIntersectExtractIdList[0], WriteTransaction);
// iterate through all partners in first extract and check if this record also exists in all other extracts
foreach (DataRow ExtractRow in FirstExtractTable.Rows)
{
PartnerExistsInAllExtracts = true;
// now check if this partner record exists in all other extracts as well
for (ExtractIndex = 1;
ExtractIndex < AIntersectExtractIdList.Count && PartnerExistsInAllExtracts;
ExtractIndex++)
{
if (!MExtractAccess.Exists(AIntersectExtractIdList[ExtractIndex], ((MExtractRow)ExtractRow).PartnerKey,
((MExtractRow)ExtractRow).SiteKey, WriteTransaction))
{
// can stop here as there is at least one extract where partner does not exist
PartnerExistsInAllExtracts = false;
}
}
// create and add row to intersected extract as it exists in all extracts
if (PartnerExistsInAllExtracts)
{
TemplateRow = (MExtractRow)IntersectedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = ANewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
IntersectedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable IntersectedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(ANewExtractId, WriteTransaction);
IntersectedExtractMaster[0].KeyCount = IntersectedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(IntersectedExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(IntersectedExtractMaster, WriteTransaction);
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the intersecting of Extracts:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
return ResultValue;
}
public static Boolean CombineExtracts(String ANewExtractName,
String ANewExtractDescription,
List <Int32>ACombineExtractIdList,
out Int32 ANewExtractId)
{
Boolean ResultValue = true;
Boolean ExtractAlreadyExists = false;
MExtractTable ExtractTable;
MExtractTable CombinedExtractTable = new MExtractTable();
MExtractRow TemplateRow;
Boolean NewTransaction;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
ANewExtractId = -1;
try
{
ResultValue = MPartner.Extracts.TExtractsHandling.CreateNewExtract(ANewExtractName,
ANewExtractDescription, out ANewExtractId, out ExtractAlreadyExists);
if (ResultValue && !ExtractAlreadyExists)
{
// loop through each extract and combine them
foreach (Int32 ExtractId in ACombineExtractIdList)
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(ExtractId, WriteTransaction);
foreach (DataRow ExtractRow in ExtractTable.Rows)
{
if (CombinedExtractTable.Rows.Find(new object[] { ANewExtractId,
((MExtractRow)ExtractRow).PartnerKey,
((MExtractRow)ExtractRow).SiteKey }) == null)
{
// create and add row to combined extract as it does not exist yet
TemplateRow = (MExtractRow)CombinedExtractTable.NewRowTyped(true);
TemplateRow.ExtractId = ANewExtractId;
TemplateRow.PartnerKey = ((MExtractRow)ExtractRow).PartnerKey;
TemplateRow.SiteKey = ((MExtractRow)ExtractRow).SiteKey;
TemplateRow.LocationKey = ((MExtractRow)ExtractRow).LocationKey;
CombinedExtractTable.Rows.Add(TemplateRow);
}
}
}
// update key count in master table
MExtractMasterTable CombinedExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(ANewExtractId, WriteTransaction);
CombinedExtractMaster[0].KeyCount = CombinedExtractTable.Rows.Count;
// submit changes in master and then in extract content table which refers to it
MExtractAccess.SubmitChanges(CombinedExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(CombinedExtractMaster, WriteTransaction);
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the combining of Extracts:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
return ResultValue;
}
/// <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="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>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
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 = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
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);
}
});
}
