/// find the current best address for the partner
public static bool GetBestAddress(Int64 APartnerKey,
out PLocationTable AAddress,
out string ACountryNameLocal,
TDBTransaction ATransaction,
bool AOnlySendMail = false)
{
AAddress = null;
ACountryNameLocal = "";
DataSet PartnerLocationsDS = new DataSet();
PartnerLocationsDS.Tables.Add(new PPartnerLocationTable());
PartnerLocationsDS.Tables.Add(new PCountryTable());
DataTable PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()];
PCountryTable CountryTable = (PCountryTable)PartnerLocationsDS.Tables[PCountryTable.GetTableName()];
CountryTable.DefaultView.Sort = PCountryTable.GetCountryCodeDBName();
// add special column BestAddress and Icon
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean)));
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32)));
// find all locations of the partner, put it into a dataset
PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, ATransaction);
Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS);
Calculations.DetermineBestAddress(PartnerLocationsDS);
foreach (PPartnerLocationRow row in PartnerLocationTable.Rows)
{
if (AOnlySendMail && !Convert.ToBoolean(row[PPartnerLocationTable.GetSendMailDBName()]))
{
// ignore addresses that are not set for receiving mail.
continue;
}
// find the row with BestAddress = 1
if (Convert.ToInt32(row["BestAddress"]) == 1)
{
// we also want the post address, need to load the p_location table:
AAddress = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, ATransaction);
// watch out for empty country codes
if (AAddress[0].CountryCode.Trim().Length > 0)
{
if (CountryTable.DefaultView.Find(AAddress[0].CountryCode) == -1)
{
CountryTable.Merge(PCountryAccess.LoadByPrimaryKey(AAddress[0].CountryCode, ATransaction));
}
ACountryNameLocal = CountryTable[CountryTable.DefaultView.Find(AAddress[0].CountryCode)].CountryNameLocal;
}
break;
}
}
return(AAddress != null);
}
/// <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>
/// Determines which address is the 'Best Address' of a Partner, and marks it in the DataColumn 'BestAddress'.
/// </summary>
/// <remarks>There are convenient overloaded server-side Methods, Ict.Petra.Server.MPartner.ServerCalculations.DetermineBestAddress,
/// which work by specifying the PartnerKey of a Partner in an Argument.</remarks>
/// <param name="APartnerLocationsDT">DataTable containing the addresses of a Partner.</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(DataTable APartnerLocationsDT)
{
TLocationPK ReturnValue;
DataRow[] OrderedRows;
System.Int32 CurrentRow;
System.Int32 BestRow;
System.Int16 FirstRowAddrOrder;
bool FirstRowMailingAddress;
System.DateTime BestRowDate;
System.DateTime TempDate;
CurrentRow = 0;
BestRow = 0;
bool Unchanged = false;
TLogging.LogAtLevel(8, "Calculations.DetermineBestAddress: processing " + APartnerLocationsDT.Rows.Count.ToString() + " rows...");
if (APartnerLocationsDT == null)
{
throw new ArgumentException("Argument APartnerLocationsDT must not be null");
}
if (!APartnerLocationsDT.Columns.Contains(PARTNERLOCATION_BESTADDR_COLUMN))
{
DeterminePartnerLocationsDateStatus(APartnerLocationsDT, DateTime.Today);
}
/*
* Add custom DataColumn if its not part of the DataTable yet
*/
if (!APartnerLocationsDT.Columns.Contains(PARTNERLOCATION_BESTADDR_COLUMN))
{
APartnerLocationsDT.Columns.Add(new System.Data.DataColumn(PARTNERLOCATION_BESTADDR_COLUMN, typeof(Boolean)));
}
/*
* Order tables' rows: first all records with p_send_mail_l = true, these are ordered
* ascending by Icon, then all records with p_send_mail_l = false, these are ordered
* ascending by Icon.
*/
OrderedRows = APartnerLocationsDT.Select(APartnerLocationsDT.DefaultView.RowFilter,
PPartnerLocationTable.GetSendMailDBName() + " DESC, " + PartnerEditTDSPPartnerLocationTable.GetIconDBName() + " ASC",
DataViewRowState.CurrentRows);
if (OrderedRows.Length > 1)
{
FirstRowAddrOrder = Convert.ToInt16(OrderedRows[0][PartnerEditTDSPPartnerLocationTable.GetIconDBName()]);
FirstRowMailingAddress = Convert.ToBoolean(OrderedRows[0][PPartnerLocationTable.GetSendMailDBName()]);
// determine pBestRowDate
if (FirstRowAddrOrder != 3)
{
BestRowDate = TSaveConvert.ObjectToDate(OrderedRows[CurrentRow][PPartnerLocationTable.GetDateEffectiveDBName()]);
}
else
{
BestRowDate = TSaveConvert.ObjectToDate(OrderedRows[CurrentRow][PPartnerLocationTable.GetDateGoodUntilDBName()]);
}
// iterate through the sorted rows
for (CurrentRow = 0; CurrentRow <= OrderedRows.Length - 1; CurrentRow += 1)
{
Unchanged = OrderedRows[CurrentRow].RowState == DataRowState.Unchanged;
// reset any row that might have been marked as 'best' before
OrderedRows[CurrentRow][PartnerEditTDSPPartnerLocationTable.GetBestAddressDBName()] = ((object)0);
// We do not want changing the BestAddress column to enable save. So revert row status to original.
if (Unchanged)
{
OrderedRows[CurrentRow].AcceptChanges();
}
// determine pTempDate
if (FirstRowAddrOrder != 3)
{
TempDate = TSaveConvert.ObjectToDate(OrderedRows[CurrentRow][PPartnerLocationTable.GetDateEffectiveDBName()]);
}
else
{
TempDate = TSaveConvert.ObjectToDate(OrderedRows[CurrentRow][PPartnerLocationTable.GetDateGoodUntilDBName()]);
}
// still the same ADDR_ORDER than the ADDR_ORDER of the first row and
// still the same Mailing Address than the Mailing Address flag of the first row > proceed
if ((Convert.ToInt16(OrderedRows[CurrentRow][PartnerEditTDSPPartnerLocationTable.GetIconDBName()]) == FirstRowAddrOrder) &&
(Convert.ToBoolean(OrderedRows[CurrentRow][PPartnerLocationTable.GetSendMailDBName()]) == FirstRowMailingAddress))
{
switch (FirstRowAddrOrder)
{
case 1:
case 3:
// find the Row with the highest p_date_effective_d (or p_date_good_until_d) date
if (TempDate > BestRowDate)
{
BestRowDate = TempDate;
BestRow = CurrentRow;
}
break;
case 2:
// find the Row with the lowest p_date_effective_d date
if (TempDate < BestRowDate)
{
BestRowDate = TempDate;
BestRow = CurrentRow;
}
break;
}
}
}
Unchanged = OrderedRows[0].RowState == DataRowState.Unchanged;
// mark the location that was determined to be the 'best'
OrderedRows[BestRow][PartnerEditTDSPPartnerLocationTable.GetBestAddressDBName()] = ((object)1);
// We do not want changing the BestAddress column to enable save. So revert row status to original.
if (Unchanged)
{
OrderedRows[0].AcceptChanges();
}
ReturnValue =
new TLocationPK(Convert.ToInt64(OrderedRows[BestRow][PLocationTable.GetSiteKeyDBName()]),
Convert.ToInt32(OrderedRows[BestRow][PLocationTable.GetLocationKeyDBName()]));
}
else
{
if (OrderedRows.Length == 1)
{
Unchanged = OrderedRows[0].RowState == DataRowState.Unchanged;
// mark the only location to be the 'best'
OrderedRows[0][PartnerEditTDSPPartnerLocationTable.GetBestAddressDBName()] = ((object)1);
// We do not want changing the BestAddress column to enable save. So revert row status to original.
if (Unchanged)
{
OrderedRows[0].AcceptChanges();
}
ReturnValue = new TLocationPK(Convert.ToInt64(OrderedRows[0][PLocationTable.GetSiteKeyDBName()]),
Convert.ToInt32(OrderedRows[0][PLocationTable.GetLocationKeyDBName()]));
}
else
{
ReturnValue = new TLocationPK();
}
}
return(ReturnValue);
}
/// <summary>
/// Creates a list of locations for a given Partner.
/// </summary>
/// <remarks>Corresponds in parts with with Progress 4GL Method
/// 'GetPartnerLocations' in partner/maillib.p.</remarks>
/// <param name="APartnerKey">PartnerKey of the Partner being processed.</param>
/// <param name="AMailingAddressesOnly">If true: only include addresses with mailing flag set.</param>
/// <param name="AIncludeCurrentAddresses">If true: include current addresses.</param>
/// <param name="AIncludeFutureAddresses">If true: include future addresses.</param>
/// <param name="AIncludeExpiredAddresses">If true: include expired addresses.</param>
/// <param name="APartnerLocations">The Locations of the Partner being processed.</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>False if an invalid PartnerKey was passed in or if Petra Security
/// denied access to the Partner, otherwise true.</returns>
public static bool GetPartnerLocations(Int64 APartnerKey,
bool AMailingAddressesOnly,
bool AIncludeCurrentAddresses,
bool AIncludeFutureAddresses,
bool AIncludeExpiredAddresses,
out PPartnerLocationTable APartnerLocations,
TDataBase ADataBase = null)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
String SelectSQL;
DataSet FillDataSet;
OdbcParameter param;
// Initialise out Argument
APartnerLocations = null;
if (APartnerKey > 0)
{
TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Checking access to Partner.");
if (TSecurity.CanAccessPartnerByKey(APartnerKey, false, ADataBase) ==
TPartnerAccessLevelEnum.palGranted)
{
ReadTransaction = DBAccess.GetDBAccessObj(ADataBase).GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Load Partner Locations, taking passed in restrictions into account.
try
{
SelectSQL =
"SELECT *" +
" FROM PUB_" + PPartnerLocationTable.GetTableDBName() +
" WHERE " + PPartnerLocationTable.GetPartnerKeyDBName() + " = ?" +
" AND (NOT ? = true OR (? = true AND " + PPartnerLocationTable.GetSendMailDBName() + " = true))" +
" AND ((? = true AND ((" + PPartnerLocationTable.GetDateEffectiveDBName() + " <= ?" +
" OR " + PPartnerLocationTable.GetDateEffectiveDBName() + " IS NULL)" +
" AND (" + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?" +
" OR " + PPartnerLocationTable.GetDateGoodUntilDBName() + " IS NULL)))" +
" OR (? = true AND " + PPartnerLocationTable.GetDateEffectiveDBName() + " > ?)" +
" OR (? = true AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " < ?))";
List <OdbcParameter> parameters = new List <OdbcParameter>();
param = new OdbcParameter("PartnerKey", OdbcType.Decimal, 10);
param.Value = APartnerKey;
parameters.Add(param);
param = new OdbcParameter("MailingAddressOnly1", OdbcType.Bit);
param.Value = AMailingAddressesOnly;
parameters.Add(param);
param = new OdbcParameter("MailingAddressOnly2", OdbcType.Bit);
param.Value = AMailingAddressesOnly;
parameters.Add(param);
param = new OdbcParameter("IncludeCurrentAddresses", OdbcType.Bit);
param.Value = AIncludeCurrentAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate1", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("TodaysDate2", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("IncludeFutureAddresses", OdbcType.Bit);
param.Value = AIncludeFutureAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate3", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
param = new OdbcParameter("IncludeExpiredAddresses", OdbcType.Bit);
param.Value = AIncludeExpiredAddresses;
parameters.Add(param);
param = new OdbcParameter("TodaysDate4", OdbcType.Date);
param.Value = DateTime.Now;
parameters.Add(param);
/*
* Our out Argument 'APartnerLocations' is a Typed DataTable, but SelectDT
* returns an untyped DataTable, therefore we need to create a Typed DataTable
* that contains the data of the returned untyped DataTable!
*/
FillDataSet = new DataSet();
APartnerLocations = new PPartnerLocationTable(PPartnerLocationTable.GetTableDBName());
FillDataSet.Tables.Add(APartnerLocations);
DBAccess.GetDBAccessObj(ADataBase).Select(FillDataSet, SelectSQL,
PPartnerLocationTable.GetTableDBName(),
ReadTransaction,
parameters.ToArray());
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: FillDataSet.Tables.Count: " + FillDataSet.Tables.Count.ToString());
FillDataSet.Tables.Remove(APartnerLocations);
if (APartnerLocations.Rows.Count > 0)
{
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: Found " + APartnerLocations.Rows.Count.ToString() + " PartnerLocations found for Partner " + APartnerKey.ToString() + ".");
}
else
{
/*
* /* No Rows returned = no PartnerLocations for Partner.
* That shouldn't happen with existing Partners, but if it does (eg. non-existing
* PartnerKey passed in) we return an empty Typed DataTable.
*/
// TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: No PartnerLocations found for Partner " + APartnerKey.ToString() + "!");
APartnerLocations = new PPartnerLocationTable();
}
}
finally
{
if (NewTransaction)
{
DBAccess.GetDBAccessObj(ADataBase).CommitTransaction();
TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: committed own transaction.");
}
}
return(true);
}
else
{
TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Access to Partner DENIED!");
// Petra Security prevents us from accessing this Partner -> return false;
return(false);
}
}
else
{
// Invalid PartnerKey -> return false;
return(false);
}
}
