/// <summary>
/// Populate the empty table p_partner_attribute using selected data from p_partner_location.
/// </summary>
/// <returns>Number of created PPartnerAttribute Rows.</returns>
public static int PopulatePPartnerAttribute()
{
int RowCounter = 0;
Int64 BestPartnerLocSiteKey;
int BestPartnerLocLocationKey;
List <PPartnerAttributeRecord> PPARecords;
if (FEmptyStringIndicator == null)
{
throw new EOPException("'FEmptyStringIndicator' must be set before Method 'PopulatePPartnerAttribute' gets called!");
}
// FPartnerLocationRecords got created when the p_partner_location table got processed; it holds selected Columns of all
// p_partner_location records of each Partner that gets loaded.
// TLogging.Log(String.Format("We have entries for {0} Partners.", FPartnerClassInformation.Count));
// Process each Partner and its p_partner_location records
foreach (Int64 PartnerKey in FPartnerClassInformation.Keys)
{
FInsertionOrderPerPartner = -1;
// Get that Partner's p_partner_location records from PPartnerLocationRecords
DataRow[] CurrentRows = FPartnerLocationRecords[Math.Abs(PartnerKey) % NumberOfTables].Select(
"p_partner_key_n = " + PartnerKey.ToString());
if (CurrentRows.Length == 0)
{
continue;
}
DataTable PPartnersLocationsDT = TPartnerContactDetails.BestAddressHelper.GetNewPPartnerLocationTableInstance();
foreach (DataRow r in CurrentRows)
{
PPartnersLocationsDT.Rows.Add(r.ItemArray);
}
// We hazard a guess that we will possibly create 2 times as many p_partner_attribute records as there are
// p_partner_location records - but in some cases there will be more (if we need to create many 'primary'
// p_partner_attribute records for Contact Details that come out of the 'Best Address' p_partner_location records,
// or less (if there are many p_partner_location records that don't have details that will become a Contact Detail).
// The guess is done so that PPARecords doesn't need to continually get expanded by .NET as we are adding to it.
// (The 2.0 factor got determined by checking the average number of records that got created by running this
// over actual data from the SA DB (1.3) and then rounding the number up, as often there will only be one
// p_partner_location record, and so we create at least 2 entries in that case.)
PPARecords = new List <PPartnerAttributeRecord>(PPartnersLocationsDT.Rows.Count * 2);
// Determine the p_partner_location record that constitutes the 'Best Address' of that Partner
if (!BestAddressHelper.DetermineBestAddress(PPartnersLocationsDT,
out BestPartnerLocSiteKey, out BestPartnerLocLocationKey))
{
throw new Exception(String.Format(
"Problem determining the 'Best Address' for the p_partner_location records of Partner with PartnerKey {0}! Number of p_partner_location records: {1}",
PartnerKey, PPartnersLocationsDT.Rows.Count));
}
else
{
if (PPartnersLocationsDT.Rows.Count > 1)
{
// TLogging.Log(
// String.Format(
// "'Best Address' for the p_parter_location records of Partner with PartnerKey {0} which has {1} p_partner_locations: p_parter_location SiteKey: {2}; p_parter_location LocationKey: {3}",
// PartnerKey, PPartnersLocationsDT.Rows.Count, BestPartnerLocSiteKey, BestPartnerLocLocationKey));
}
//
// Create p_partner_attribute records for each p_partner_location records'
// columns that hold 'Contact Detail' data and have values, and are unique,
//
// We want to create the p_partner_attribute records in the same order that the user
// would see the p_location records (that we lift them from) on the Address Tab.
DataView SortedRecordsDV = PPartnersLocationsDT.DefaultView;
SortedRecordsDV.Sort = "Icon_For_Sorting ASC, p_date_effective_d DESC";
for (int Counter = 0; Counter < SortedRecordsDV.Count; Counter++)
{
List <PPartnerAttributeRecord> PPARecordsSingleLocation =
ConstructPPartnerAttributeRecords(PartnerKey, SortedRecordsDV[Counter].Row);
for (int SingleLocCounter = 0; SingleLocCounter < PPARecordsSingleLocation.Count; SingleLocCounter++)
{
// LINQ Query for de-duplication: Check if there are already PPARecords of a Partner that have
// the same Attribute Type and Value as a record from PPARecordsSingleLocation. If so, don't add
// the record from PPARecordsSingleLocation to PPARecords as it would create a duplicate Contact Detail!
// (it is often the case that several p_partner_location records of a Partner hold the same data!)
var PPARecordsContainsQuery =
from PPARecord in PPARecords
where PPARecord.AttributeType == PPARecordsSingleLocation[SingleLocCounter].AttributeType &&
PPARecord.Value == PPARecordsSingleLocation[SingleLocCounter].Value
select PPARecord;
if (!PPARecordsContainsQuery.Any())
{
PPARecords.Add(PPARecordsSingleLocation[SingleLocCounter]);
}
}
}
// LINQ Query to establish Index values: Group all PPARecords of a Partner by
// Attribute Type and have the Primary record come first in each Group, followed by
// the other PPARecords in their insertion order.
var PPARecordsGroupedQuery =
from PPARecord in PPARecords
orderby PPARecord.Primary descending, PPARecord.InsertionOrderPerPartner
group PPARecord by PPARecord.AttributeType into grouping
orderby grouping.Key
select grouping;
// Iterate over each Group and determine the Index values of their individual members.
// The first member always starts with Index 0.
// Reason for this: The Contact Details Tab displays Contact Details grouped by
// Contact Category, and the ones of the same Contact Category are sorted by Index
// within each Contact Category. With this approach, 'Primary' PPARecords will be
// listed first, followed by other records in the order that a user would have seen
// them on the 'Addresses' Tab in Petra 2.x!
foreach (var PPARecordGroup in PPARecordsGroupedQuery)
{
int IndexInGroup = 0;
foreach (var PPARecGroupMember in PPARecordGroup)
{
PPARecGroupMember.Index = IndexInGroup++;
}
}
// Create new Rows and write them out
foreach (var PPARec in PPARecords)
{
CreateContactDetailsRow(PPARec, PartnerAttributeHoldingDataSet);
}
RowCounter += PPARecords.Count;
}
}
// Get rid of the Data Structure that we don't need anymore!
FPartnerLocationRecords = null;
// ... and kindly ask .NET's Garbage Collector to really get it out of memory, if it's convenient.
GC.Collect(0, GCCollectionMode.Optimized);
return(RowCounter);
}
private static List <PPartnerAttributeRecord> ConstructPPartnerAttributeRecords(Int64 APartnerKey, DataRow APartnerLocationDR)
{
var ReturnValue = new List <TPartnerContactDetails.PPartnerAttributeRecord>();
var PPARecordList = new List <PPartnerAttributeRecord>();
PPartnerAttributeRecord PPARecord;
bool IsBestAddr = BestAddressHelper.IsBestAddressPartnerLocationRecord(APartnerLocationDR);
bool AnyTelephoneNumberSetAsPrimary = false;
// Variables that hold record information
string TelephoneNumber = (string)APartnerLocationDR["p_telephone_number_c"];
string FaxNumber = (string)APartnerLocationDR["p_fax_number_c"];
string MobileNumber = (string)APartnerLocationDR["p_mobile_number_c"];
string AlternatePhoneNumber = (string)APartnerLocationDR["p_alternate_telephone_c"];
string Url = (string)APartnerLocationDR["p_url_c"];
string EmailAddress = (string)APartnerLocationDR["p_email_address_c"];
string PartnerClass;
FInsertionOrderPerPartner++;
//
// Work on the various p_partner_location DataColumns that hold data and that will each be migrated to
// a Contact Detail record - if they hold data.
//
if ((TelephoneNumber != FEmptyStringIndicator) ||
(EmailAddress != FEmptyStringIndicator))
{
// Set data parts that depend on certain conditions
if (TelephoneNumber != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
PPARecord.Value = TelephoneNumber;
PPARecord.AttributeType = ATTR_TYPE_PHONE;
if ((IsBestAddr) &&
(PPARecord.Current))
{
// Mark this Contact Detail as being 'Primary' - but only if the Contact Detail is current!
PPARecord.Primary = true;
AnyTelephoneNumberSetAsPrimary = true;
// TLogging.Log(String.Format(
// "Made Telephone Number '{0}' the 'Primary Phone' (PartnerKey: {1}, LocationKey: {2})",
// TelephoneNumber, APartnerKey, APartnerLocationDR["p_location_key_i"]));
}
PPARecordList.Add(PPARecord);
}
if (EmailAddress != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
PPARecord.Value = EmailAddress;
PPARecord.AttributeType = ATTR_TYPE_EMAIL;
if ((IsBestAddr) &&
(PPARecord.Current))
{
// Mark this Contact Detail as being 'Primary' - but only if the Contact Detail is current!
PPARecord.Primary = true;
// Mark this Contact Detail as being 'WithinOrganisation' as it has an 'organisation-internal' e-mail-address!
// - but only if the Partner is a PERSON!
if (FPartnerClassInformation.TryGetValue(APartnerKey, out PartnerClass))
{
if (PartnerClass == "PERSON")
{
if (EmailAddress.EndsWith("@om.org", StringComparison.InvariantCulture))
{
PPARecord.WithinOrganisation = true;
// TLogging.Log(String.Format(
// "Made email address '{0}' a 'WithinOrganisation' e-mail address (PartnerKey: {1}, LocationKey: {2})",
// EmailAddress, APartnerKey, APartnerLocationDR["p_location_key_i"]));
}
}
}
}
PPARecordList.Add(PPARecord);
}
}
if (FaxNumber != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
// TODO_LOW - PERHAPS: check if the Value is an email address and in case it is, record it as an e-mail address instead of this Attribute Type! [would need to use TStringChecks.ValidateEmail(xxxx, true)]
PPARecord.Value = FaxNumber;
PPARecord.AttributeType = ATTR_TYPE_FAX;
PPARecordList.Add(PPARecord);
}
if (MobileNumber != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
// TODO_LOW - PERHAPS: check if the Value is an email address and in case it is, record it as an e-mail address instead of this Attribute Type! [would need to use TStringChecks.ValidateEmail(xxxx, true)]
PPARecord.Value = MobileNumber;
PPARecord.AttributeType = ATTR_TYPE_MOBILE_PHONE;
if ((!AnyTelephoneNumberSetAsPrimary) &&
(IsBestAddr) &&
(PPARecord.Current))
{
// Mark this Contact Detail as being 'Primary' - but only if no other telephone number has been set as primary already and
// when the Contact Detail is current!
PPARecord.Primary = true;
AnyTelephoneNumberSetAsPrimary = true;
// TLogging.Log(String.Format(
// "Made MOBILE Number '{0}' the 'Primary Phone' (PartnerKey: {1}, LocationKey: {2})",
// MobileNumber, APartnerKey, APartnerLocationDR["p_location_key_i"]));
}
PPARecordList.Add(PPARecord);
}
if (AlternatePhoneNumber != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
// TODO_LOW - PERHAPS: check if the Value is an email address and in case it is, record it as an e-mail address instead of this Attribute Type! [would need to use TStringChecks.ValidateEmail(xxxx, true)]
PPARecord.Value = AlternatePhoneNumber;
PPARecord.AttributeType = ATTR_TYPE_PHONE;
if ((!AnyTelephoneNumberSetAsPrimary) &&
(IsBestAddr) &&
(PPARecord.Current))
{
// Mark this Contact Detail as being 'Primary' - but only if no other telephone number has been set as primary already and
// when the Contact Detail is current!
PPARecord.Primary = true;
AnyTelephoneNumberSetAsPrimary = true;
// TLogging.Log(String.Format(
// "Made ALTERNATE Phone Number '{0}' the 'Primary Phone' (PartnerKey: {1}, LocationKey: {2})",
// AlternatePhoneNumber, APartnerKey, APartnerLocationDR["p_location_key_i"]));
}
PPARecordList.Add(PPARecord);
}
if (Url != FEmptyStringIndicator)
{
PPARecord = GetNewPPartnerAttributeRecord(APartnerKey, APartnerLocationDR);
// TODO_LOW - PERHAPS: check if the Value is an email address and in case it is, record it as an e-mail address instead of this Attribute Type! [would need to use TStringChecks.ValidateEmail(xxxx, true)]
PPARecord.Value = Url;
PPARecord.AttributeType = ATTR_TYPE_WEBSITE;
PPARecordList.Add(PPARecord);
}
// Now add all created records to the ReturnValue
foreach (var PPARec in PPARecordList)
{
ReturnValue.Add(PPARec);
}
return(ReturnValue);
}
/// <summary>
/// Populate the empty table p_partner_attribute using selected data from p_partner_location.
/// </summary>
public static int PopulatePPartnerAttribute(StringCollection AColumnNames,
ref string[] ANewRow,
StreamWriter AWriter,
StreamWriter AWriterTest)
{
int RowCounter = 0;
DataTable PPartnersLocationsDT;
Int64 PartnerKey;
Int64 BestPartnerLocSiteKey;
int BestPartnerLocLocationKey;
List <PPartnerAttributeRecord> PPARecords;
DataView SortedRecordsDV;
// default for all new records
SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N");
// TLogging.Log(String.Format("PPartnerLocationRecords holds entries for {0} Partners.", FPartnerLocationRecords.Count));
// FPartnerLocationRecords got created when the p_partner_location table got processed; it holds selected Columns of all
// p_partner_location records of each Partner that gets loaded.
// Process each Partner and its p_partner_location records
foreach (var PPartnerLoctationRec in FPartnerLocationRecords)
{
FInsertionOrderPerPartner = -1;
// Get PartnerKey of Partner that we are working on at present from PPartnerLocationRecords
PartnerKey = PPartnerLoctationRec.Key;
// Get that Partner's p_partner_location records from PPartnerLocationRecords
PPartnersLocationsDT = PPartnerLoctationRec.Value;
// We hazard a guess that we will possibly create 2 times as many p_partner_attribute records as there are
// p_partner_location records - but in some cases there will be more (if we need to create many 'primary'
// p_partner_attribute records for Contact Details that come out of the 'Best Address' p_partner_location records,
// or less (if there are many p_partner_location records that don't have details that will become a Contact Detail).
// The guess is done so that PPARecords doesn't need to continually get expanded by .NET as we are adding to it.
// (The 2.0 factor got determined by checking the average number of records that got created by running this
// over actual data from the SA DB (1.3) and then rounding the number up, as often there will only be one
// p_partner_location record, and so we create at least 2 entries in that case.)
PPARecords = new List <PPartnerAttributeRecord>(PPartnersLocationsDT.Rows.Count * 2);
// Determine the p_partner_location record that constitutes the 'Best Address' of that Partner
if (!BestAddressHelper.DetermineBestAddress(PPartnersLocationsDT,
out BestPartnerLocSiteKey, out BestPartnerLocLocationKey))
{
throw new Exception(String.Format(
"Problem determining the 'Best Address' for the p_parter_location records of Partner with PartnerKey {0}! Number of p_parter_location records: {1}",
PartnerKey, PPartnersLocationsDT.Rows.Count));
}
else
{
if (PPartnersLocationsDT.Rows.Count > 1)
{
// TLogging.Log(
// String.Format(
// "'Best Address' for the p_parter_location records of Partner with PartnerKey {0} which has {1} p_partner_locations: p_parter_location SiteKey: {2}; p_parter_location LocationKey: {3}",
// PartnerKey, PPartnersLocationsDT.Rows.Count, BestPartnerLocSiteKey, BestPartnerLocLocationKey));
}
//
// Create p_partner_attribute records for each p_partner_location records'
// columns that hold 'Contact Detail' data and have values, and are unique,
//
// We want to create the p_partner_attribute records in the same order that the user
// would see the p_location records (that we lift them from) on the Address Tab.
SortedRecordsDV = PPartnersLocationsDT.DefaultView;
SortedRecordsDV.Sort = "Icon_For_Sorting ASC, p_date_effective_d DESC";
for (int Counter = 0; Counter < SortedRecordsDV.Count; Counter++)
{
List <PPartnerAttributeRecord> PPARecordsSingleLocation =
ConstructPPartnerAttributeRecords(PartnerKey, SortedRecordsDV[Counter].Row);
for (int SingleLocCounter = 0; SingleLocCounter < PPARecordsSingleLocation.Count; SingleLocCounter++)
{
// LINQ Query for de-duplication: Check if there are already PPARecords of a Partner that have
// the same Attribute Type and Value as a record from PPARecordsSingleLocation. If so, don't add
// the record from PPARecordsSingleLocation to PPARecords as it would create a duplicate Contact Detail!
// (it is often the case that several p_partner_location records of a Partner hold the same data!)
var PPARecordsContainsQuery =
from PPARecord in PPARecords
where PPARecord.AttributeType == PPARecordsSingleLocation[SingleLocCounter].AttributeType &&
PPARecord.Value == PPARecordsSingleLocation[SingleLocCounter].Value
select PPARecord;
if (!PPARecordsContainsQuery.Any())
{
PPARecords.Add(PPARecordsSingleLocation[SingleLocCounter]);
}
}
}
// LINQ Query to establish Index values: Group all PPARecords of a Partner by
// Attribute Type and have the Primary record come first in each Group, followed by
// the other PPARecords in their insertion order.
var PPARecordsGroupedQuery =
from PPARecord in PPARecords
orderby PPARecord.Primary descending, PPARecord.InsertionOrderPerPartner
group PPARecord by PPARecord.AttributeType into grouping
orderby grouping.Key
select grouping;
// Iterate over each Group and determine the Index values of their individual members.
// The first member always starts with Index 0.
// Reason for this: The Contact Details Tab displays Contact Details grouped by
// Contact Category, and the ones of the same Contact Category are sorted by Index
// within each Contact Category. With this approach, 'Primary' PPARecords will be
// listed first, followed by other records in the order that a user would have seen
// them on the 'Addresses' Tab in Petra 2.x!
foreach (var PPARecordGroup in PPARecordsGroupedQuery)
{
int IndexInGroup = 0;
foreach (var PPARecGroupMember in PPARecordGroup)
{
PPARecGroupMember.Index = IndexInGroup++;
}
}
// Create new Rows and write them out
foreach (var PPARec in PPARecords)
{
SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", PPARec.PartnerKey.ToString());
SetValue(AColumnNames, ref ANewRow, "p_sequence_i", PPARec.Sequence.ToString());
SetValue(AColumnNames, ref ANewRow, "p_attribute_type_c", PPARec.AttributeType);
SetValue(AColumnNames, ref ANewRow, "p_index_i", PPARec.Index.ToString());
SetValue(AColumnNames, ref ANewRow, "p_value_c", PPARec.Value.Replace(";", @"\;"));
SetValue(AColumnNames, ref ANewRow, "p_comment_c", PPARec.Comment != String.Empty ? PPARec.Comment : "\\N");
SetValue(AColumnNames, ref ANewRow, "p_primary_l", PPARec.Primary ? "1" : "0");
SetValue(AColumnNames, ref ANewRow, "p_within_organsiation_l", PPARec.WithinOrgansiation ? "1" : "0");
SetValue(AColumnNames, ref ANewRow, "p_specialised_l", PPARec.Specialised ? "1" : "0");
SetValue(AColumnNames, ref ANewRow, "p_confidential_l", PPARec.Confidential ? "1" : "0");
SetValue(AColumnNames, ref ANewRow, "p_current_l", PPARec.Current ? "1" : "0");
SetValue(AColumnNames, ref ANewRow, "p_no_longer_current_from_d",
PPARec.NoLongerCurrentFrom.HasValue ? PPARec.NoLongerCurrentFrom.Value.ToString("yyyy-dd-mm") : "\\N");
AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
if (AWriterTest != null)
{
AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_attribute FROM stdin;");
AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
AWriterTest.WriteLine("\\.");
AWriterTest.WriteLine("ROLLBACK;");
}
}
RowCounter += PPARecords.Count;
}
}
// Get rid of the Data Structure that we don't need anymore!
FPartnerLocationRecords = null;
// ... and kindly ask .NET's Garbage Collector to really get it out of memory, if it's convenient.
GC.Collect(0, GCCollectionMode.Optimized);
return(RowCounter);
}
