private void WriteLocation(PLocationRow ALocationRow, PPartnerLocationRow APartnerLocationRow,
TLocationPK ABestAddressPK)
{
string PhoneNumber;
string EmailAddress;
string FaxNumber;
Write(ALocationRow.IsSiteKeyNull() ? 0 : ALocationRow.SiteKey);
Write(ALocationRow.IsLocalityNull() ? "" : ALocationRow.Locality);
Write(ALocationRow.IsStreetNameNull() ? "" : ALocationRow.StreetName);
Write(ALocationRow.IsAddress3Null() ? "" : ALocationRow.Address3);
WriteLine();
Write(ALocationRow.IsCityNull() ? "" : ALocationRow.City);
Write(ALocationRow.IsCountyNull() ? "" : ALocationRow.County);
Write(ALocationRow.IsPostalCodeNull() ? "" : ALocationRow.PostalCode);
Write(ALocationRow.IsCountryCodeNull() ? "" : ALocationRow.CountryCode);
WriteLine();
Write(APartnerLocationRow.IsDateEffectiveNull() ? "?" : APartnerLocationRow.DateEffective.Value.ToString(DATEFORMAT));
Write(APartnerLocationRow.IsDateGoodUntilNull() ? "?" : APartnerLocationRow.DateGoodUntil.Value.ToString(DATEFORMAT));
Write(APartnerLocationRow.IsLocationTypeNull() ? "" : APartnerLocationRow.LocationType);
Write(APartnerLocationRow.IsSendMailNull() ? false : APartnerLocationRow.SendMail);
if ((APartnerLocationRow.LocationKey == ABestAddressPK.LocationKey)
&& (APartnerLocationRow.SiteKey == ABestAddressPK.SiteKey))
{
// For the Location that is the 'Best Address' of the Partner we export 'Primary Phone Number',
// 'Primary E-mail Address' and the 'Fax Number'.
// They are exported for backwards compatibility as part of the 'Location' information as that is the only
// place where the data was/is stored (and was/is seen and was/is maintained by the user) in Petra 2.x!
TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhoneAndFax(APartnerLocationRow.PartnerKey,
out PhoneNumber, out EmailAddress, out FaxNumber);
Write(EmailAddress ?? String.Empty);
Write(PhoneNumber ?? String.Empty);
Write(0); // Phone Extensions are no longer kept in the Contact Details scheme so we can't export them...
Write(FaxNumber ?? String.Empty);
Write(0); // Fax Extensions are no longer kept in the Contact Details scheme so we can't export them...
}
else
{
// For any Location that isn't the 'Best Address' of the Partner: Export empty data for EmailAddress,
// PhoneNumber, (Phone) Extension, Fax and Fax Extension.
Write(String.Empty);
Write(String.Empty);
Write(0);
Write(String.Empty);
Write(0);
}
WriteLine();
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="ALocationRow"></param>
/// <param name="APartnerKey"></param>
/// <param name="AExistingLocationParametersDT"></param>
/// <param name="AReadTransaction"></param>
/// <param name="AExistingSiteKey"></param>
/// <param name="AExistingLocationKey"></param>
/// <returns></returns>
private static Boolean CheckReUseExistingLocation(PLocationRow ALocationRow,
Int64 APartnerKey,
ref PartnerAddressAggregateTDSSimilarLocationParametersTable AExistingLocationParametersDT,
TDBTransaction AReadTransaction,
out Int64 AExistingSiteKey,
out Int32 AExistingLocationKey)
{
Boolean ReturnValue;
PLocationTable MatchingLocationsDT;
Boolean FoundSimilarLocation;
PartnerAddressAggregateTDSSimilarLocationParametersRow SimilarLocationRow;
PartnerAddressAggregateTDSSimilarLocationParametersRow SimilarLocationParameterRow;
DataView ExistingLocationParametersDV;
int LocationUsedByNPartners;
int Counter;
PLocationRow MatchingLocationRow;
AExistingSiteKey = 0;
AExistingLocationKey = 0;
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation for Location " + ALocationRow.LocationKey.ToString() +
// ": AExistingLocationParametersDT.Rows.Count: " + AExistingLocationParametersDT.Rows.Count.ToString());
// Check if there is a Parameter Row for the LocationKey we are looking at
ExistingLocationParametersDV = new DataView(AExistingLocationParametersDT,
PartnerAddressAggregateTDSSimilarLocationParametersTable.GetSiteKeyDBName() + " = " + ALocationRow.SiteKey.ToString() + " AND " +
PartnerAddressAggregateTDSSimilarLocationParametersTable.GetLocationKeyDBName() + " = " + ALocationRow.LocationKey.ToString(),
"",
DataViewRowState.CurrentRows);
// No, there isn't one: perform DB check
if (ExistingLocationParametersDV.Count == 0)
{
FoundSimilarLocation = false;
#region Look in the DB for *similar* Locations
// first check how many odbc parameters need to be created
int CountParameters = 0;
int CurrentParameter = 0;
if (!ALocationRow.IsLocalityNull() && (ALocationRow.Locality != ""))
{
CountParameters++;
}
if (!ALocationRow.IsStreetNameNull() && (ALocationRow.StreetName != ""))
{
CountParameters++;
}
if (!ALocationRow.IsCityNull() && (ALocationRow.City != ""))
{
CountParameters++;
}
if (!ALocationRow.IsPostalCodeNull() && (ALocationRow.PostalCode != ""))
{
CountParameters++;
}
if (!ALocationRow.IsCountryCodeNull() && (ALocationRow.CountryCode != ""))
{
CountParameters++;
}
// initialize parameters and prepare SQL statement
OdbcParameter[] parameters = new OdbcParameter[CountParameters];
string sqlLoadSimilarAddresses = "SELECT * FROM PUB_" + PLocationTable.GetTableDBName() + " WHERE";
// add Locality to query
sqlLoadSimilarAddresses += " " + PLocationTable.GetLocalityDBName();
if (ALocationRow.IsLocalityNull() || (ALocationRow.Locality == ""))
{
sqlLoadSimilarAddresses += " is null";
}
else
{
sqlLoadSimilarAddresses += " = ?";
parameters[CurrentParameter] = new OdbcParameter("Locality", OdbcType.VarChar);
parameters[CurrentParameter].Value = ALocationRow.Locality;
CurrentParameter++;
}
// add Street Name to query
sqlLoadSimilarAddresses += " AND " + PLocationTable.GetStreetNameDBName();
if (ALocationRow.IsStreetNameNull() || (ALocationRow.StreetName == ""))
{
sqlLoadSimilarAddresses += " is null";
}
else
{
sqlLoadSimilarAddresses += " = ?";
parameters[CurrentParameter] = new OdbcParameter("StreetName", OdbcType.VarChar);
parameters[CurrentParameter].Value = ALocationRow.StreetName;
CurrentParameter++;
}
// add City to query
sqlLoadSimilarAddresses += " AND " + PLocationTable.GetCityDBName();
if (ALocationRow.IsCityNull() || (ALocationRow.City == ""))
{
sqlLoadSimilarAddresses += " is null";
}
else
{
sqlLoadSimilarAddresses += " = ?";
parameters[CurrentParameter] = new OdbcParameter("City", OdbcType.VarChar);
parameters[CurrentParameter].Value = ALocationRow.City;
CurrentParameter++;
}
// add Post Code to query
sqlLoadSimilarAddresses += " AND " + PLocationTable.GetPostalCodeDBName();
if (ALocationRow.IsPostalCodeNull() || (ALocationRow.PostalCode == ""))
{
sqlLoadSimilarAddresses += " is null";
}
else
{
sqlLoadSimilarAddresses += " = ?";
parameters[CurrentParameter] = new OdbcParameter("PostalCode", OdbcType.VarChar);
parameters[CurrentParameter].Value = ALocationRow.PostalCode;
CurrentParameter++;
}
// add Country Code to query
sqlLoadSimilarAddresses += " AND " + PLocationTable.GetCountryCodeDBName();
if (ALocationRow.IsCountryCodeNull() || (ALocationRow.CountryCode == ""))
{
sqlLoadSimilarAddresses += " is null";
}
else
{
sqlLoadSimilarAddresses += " = ?";
parameters[CurrentParameter] = new OdbcParameter("CountryCode", OdbcType.VarChar);
parameters[CurrentParameter].Value = ALocationRow.CountryCode;
CurrentParameter++;
}
MatchingLocationsDT = new PLocationTable();
// run query to find similar locations
DBAccess.GDBAccessObj.SelectDT(MatchingLocationsDT, sqlLoadSimilarAddresses, AReadTransaction, parameters, 0, 0);
/*
* Note: County and Address3 are not searched for - we are looking for a
* Location that is *similar*!
*/
MatchingLocationRow = null; // to avoid compiler warning
if (MatchingLocationsDT.Rows.Count != 0)
{
// check if any of the returned Rows is not the current Row
for (Counter = 0; Counter <= MatchingLocationsDT.Rows.Count - 1; Counter += 1)
{
if (MatchingLocationsDT[Counter].LocationKey != ALocationRow.LocationKey)
{
FoundSimilarLocation = true;
AExistingSiteKey = MatchingLocationsDT[Counter].SiteKey;
AExistingLocationKey = (int)MatchingLocationsDT[Counter].LocationKey;
MatchingLocationRow = (PLocationRow)MatchingLocationsDT[Counter];
break;
}
}
}
else
{
FoundSimilarLocation = false;
}
#endregion
if (FoundSimilarLocation)
{
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation: Location " + ALocationRow.LocationKey.ToString() + ": found a similar Location (" + AExistingLocationKey.ToString() + ")!");
AExistingLocationParametersDT = new PartnerAddressAggregateTDSSimilarLocationParametersTable(
MPartnerConstants.EXISTINGLOCATIONPARAMETERS_TABLENAME);
LocationUsedByNPartners =
(Int16)(PPartnerLocationAccess.CountViaPLocation(AExistingSiteKey, AExistingLocationKey, AReadTransaction));
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation: LocationUsedByNPartners: " + LocationUsedByNPartners.ToString());
SimilarLocationRow = AExistingLocationParametersDT.NewRowTyped(false);
SimilarLocationRow.SiteKey = ALocationRow.SiteKey;
SimilarLocationRow.LocationKey = ALocationRow.LocationKey;
SimilarLocationRow.Locality = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnLocality, MatchingLocationRow);
SimilarLocationRow.StreetName = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnStreetName, MatchingLocationRow);
SimilarLocationRow.Address3 = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnAddress3, MatchingLocationRow);
SimilarLocationRow.City = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnCity, MatchingLocationRow);
SimilarLocationRow.PostalCode = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnPostalCode, MatchingLocationRow);
SimilarLocationRow.County = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnCounty, MatchingLocationRow);
SimilarLocationRow.CountryCode = TSaveConvert.StringColumnToString(MatchingLocationsDT.ColumnCountryCode, MatchingLocationRow);
if (LocationUsedByNPartners > 0)
{
SimilarLocationRow.UsedByNOtherPartners = LocationUsedByNPartners;
}
else
{
SimilarLocationRow.UsedByNOtherPartners = 0;
}
SimilarLocationRow.SiteKeyOfSimilarLocation = AExistingSiteKey;
SimilarLocationRow.LocationKeyOfSimilarLocation = AExistingLocationKey;
SimilarLocationRow.AnswerProcessedClientSide = false;
SimilarLocationRow.AnswerProcessedServerSide = false;
AExistingLocationParametersDT.Rows.Add(SimilarLocationRow);
SimilarLocationRow.AcceptChanges();
ReturnValue = true;
}
else
{
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation: Location " + ALocationRow.LocationKey.ToString() + ": found no similar Location.");
ReturnValue = false;
}
}
else
{
// AExistingLocationParametersDT was passed in, holding parameters for the LocationKey we are looking at
SimilarLocationParameterRow = (PartnerAddressAggregateTDSSimilarLocationParametersRow)ExistingLocationParametersDV[0].Row;
if (SimilarLocationParameterRow.AnswerReuse)
{
AExistingSiteKey = SimilarLocationParameterRow.SiteKeyOfSimilarLocation;
AExistingLocationKey = SimilarLocationParameterRow.LocationKeyOfSimilarLocation;
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation: AExistingLocationParametersDT tells me to re-use existing Location " +
// AExistingLocationParametersDT[0].LocationKeyOfSimilarLocation.ToString() + '.');
SimilarLocationParameterRow.AnswerProcessedClientSide = true;
// SimilarLocationParameterRow.AcceptChanges;
ReturnValue = true;
}
else
{
// TLogging.LogAtLevel(9, "CheckReUseExistingLocation: AExistingLocationParametersDT tells me NOT to re-use existing Location.");
SimilarLocationParameterRow.AnswerProcessedClientSide = true;
// SimilarLocationParameterRow.AcceptChanges;
ReturnValue = false;
}
}
return ReturnValue;
}
private void WriteLocation(PLocationRow ALocationRow, PPartnerLocationRow APartnerLocationRow)
{
Write(ALocationRow.IsSiteKeyNull() ? 0 : ALocationRow.SiteKey);
Write(ALocationRow.IsLocalityNull() ? "" : ALocationRow.Locality);
Write(ALocationRow.IsStreetNameNull() ? "" : ALocationRow.StreetName);
Write(ALocationRow.IsAddress3Null() ? "" : ALocationRow.Address3);
WriteLine();
Write(ALocationRow.IsCityNull() ? "" : ALocationRow.City);
Write(ALocationRow.IsCountyNull() ? "" : ALocationRow.County);
Write(ALocationRow.IsPostalCodeNull() ? "" : ALocationRow.PostalCode);
Write(ALocationRow.IsCountryCodeNull() ? "" : ALocationRow.CountryCode);
WriteLine();
Write(APartnerLocationRow.IsDateEffectiveNull() ? "?" : APartnerLocationRow.DateEffective.Value.ToString(DATEFORMAT));
Write(APartnerLocationRow.IsDateGoodUntilNull() ? "?" : APartnerLocationRow.DateGoodUntil.Value.ToString(DATEFORMAT));
Write(APartnerLocationRow.IsLocationTypeNull() ? "" : APartnerLocationRow.LocationType);
Write(APartnerLocationRow.IsSendMailNull() ? false : APartnerLocationRow.SendMail);
Write(APartnerLocationRow.IsEmailAddressNull() ? "" : APartnerLocationRow.EmailAddress);
Write(APartnerLocationRow.IsTelephoneNumberNull() ? "" : APartnerLocationRow.TelephoneNumber);
Write(APartnerLocationRow.IsExtensionNull() ? 0 : APartnerLocationRow.Extension);
Write(APartnerLocationRow.IsFaxNumberNull() ? "" : APartnerLocationRow.FaxNumber);
Write(APartnerLocationRow.IsFaxExtensionNull() ? 0 : APartnerLocationRow.FaxExtension);
WriteLine();
}