/// <summary>
/// Populates the grid for the dialog for selecting Addresses to be merged.
/// </summary>
public bool InitializeAddressGrid(long APartnerKey)
{
// set text for label
lblInfo.Text = Catalog.GetString("The following addresses exist for the Partner being merged. Select the addresses to be transferred.") +
"\n\n" + Catalog.GetString("Any addresses which are not selected will be deleted!");
string CheckedMember = "CHECKED";
string Address1 = PLocationTable.GetLocalityDBName();
string Street2 = PLocationTable.GetStreetNameDBName();
string Address3 = PLocationTable.GetAddress3DBName();
string City = PLocationTable.GetCityDBName();
string LocationKey = PLocationTable.GetLocationKeyDBName();
string SiteKey = PLocationTable.GetSiteKeyDBName();
string LocationType = PPartnerLocationTable.GetLocationTypeDBName();
FMainDS = TRemote.MPartner.Partner.WebConnectors.GetPartnerDetails(APartnerKey, true, false, false);
if ((FMainDS != null) && (FMainDS.PLocation != null) && (FMainDS.PLocation.Rows.Count > 0))
{
DataView MyDataView = FMainDS.PLocation.DefaultView;
FDataTable = MyDataView.ToTable(true, new string[] { Address1, Street2, Address3, City, LocationKey, SiteKey });
FDataTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
FDataTable.Columns.Add(LocationType, typeof(string));
for (int Counter = 0; Counter < FMainDS.PLocation.Rows.Count; ++Counter)
{
FDataTable.Rows[Counter][LocationType] = FMainDS.PPartnerLocation.Rows[Counter][LocationType];
}
clbRecords.Columns.Clear();
clbRecords.AddCheckBoxColumn("", FDataTable.Columns[CheckedMember], 17, false);
clbRecords.AddTextColumn("Address-1", FDataTable.Columns[Address1]);
clbRecords.AddTextColumn("Street-2", FDataTable.Columns[Street2]);
clbRecords.AddTextColumn("Address-3", FDataTable.Columns[Address3]);
clbRecords.AddTextColumn("City", FDataTable.Columns[City]);
clbRecords.AddTextColumn("Location Type", FDataTable.Columns[LocationType]);
clbRecords.ValueChanged += new EventHandler(OnCheckboxChange);
clbRecords.DataBindGrid(FDataTable, Address1, CheckedMember, Address1, false, true, false);
clbRecords.SetCheckedStringList("");
clbRecords.AutoResizeGrid();
return(true);
}
else
{
return(false);
}
}
private DataTable GetConferenceListTable(TDBTransaction AReadTransaction, string ATableName)
{
DataTable Table;
DataColumn[] Key = new DataColumn[1];
// Used eg. Select Event Dialog
Table = DBAccess.GetDBAccessObj(AReadTransaction).SelectDT(
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName() +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
" FROM PUB." + PPartnerTable.GetTableDBName() +
", PUB." + PUnitTable.GetTableDBName() +
", PUB." + PLocationTable.GetTableDBName() +
", PUB." + PPartnerLocationTable.GetTableDBName() +
", PUB." + PCountryTable.GetTableDBName() +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')"
,
ATableName, AReadTransaction);
Key[0] = Table.Columns[PPartnerTable.GetPartnerKeyDBName()];
Table.PrimaryKey = Key;
return(Table);
}
/// <summary>
/// Returns the SiteKeys for all the rows selected in the grid.
/// </summary>
public long[] GetSelectedSiteKeys()
{
long[] SiteKeys = new long[clbRecords.CheckedItemsCount];
int i = 0;
foreach (DataRow Row in FDataTable.Rows)
{
if (Convert.ToBoolean(Row["Checked"]) == true)
{
SiteKeys[i] = Convert.ToInt64(Row[PLocationTable.GetSiteKeyDBName()]);
i++;
}
}
return(SiteKeys);
}
/// <summary>
/// Private method used by ExportPartners().
/// For the given partnerKey, updates given references countryCode and siteKey.
/// If there is no location for the given partnerKey, leaves them as they are.
/// </summary>
/// <param name="MainDS">Reference to the already filled Datastructure</param>
/// <param name="partnerKey"></param>
/// <param name="countryCode"></param>
/// <param name="siteKey"></param>
/// <returns>
/// True: if a location was found for given key and the countryCode
/// and siteKey were updated. False otherwise.
/// </returns>
private static bool UpdateCountryAndSiteForGivenPK(
PartnerEditTDS MainDS,
long partnerKey,
ref string countryCode /* default could be "" */,
ref Int64 siteKey /* default could be -1 */
)
{
bool retval = false;
// Find partnerLocation for given partner_key
DataView partnerLocationView = MainDS.PPartnerLocation.DefaultView;
partnerLocationView.RowFilter = PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerKey.ToString();
if (partnerLocationView.Count > 0)
{
// partnerLocation: links one partner to possibly several Locations
// Just get the first one for now (and disregard the others).
// TODO: could determine the best address and use that
PPartnerLocationRow partnerLocationRow = (PPartnerLocationRow)partnerLocationView[0].Row;
DataView locationView = MainDS.PLocation.DefaultView;
locationView.RowFilter =
PLocationTable.GetSiteKeyDBName() + "=" + partnerLocationRow.SiteKey.ToString() + " AND " +
PLocationTable.GetLocationKeyDBName() + "=" + partnerLocationRow.LocationKey.ToString();
if (locationView.Count > 0)
{
PLocationRow locationRow = (PLocationRow)locationView[0].Row;
countryCode = locationRow.CountryCode;
siteKey = locationRow.SiteKey;
retval = true;
}
else // if there is a partner_location, there has _got_ to be the corresponding location
{
throw new Exception("Error in application: I can't find the Location with LocationKey " +
partnerLocationRow.LocationKey.ToString() +
" (Sitekey " + partnerLocationRow.SiteKey.ToString() + ")"
);
}
}
return(retval);
}
private void CreateColumns()
{
string Tmp;
string LocalisedCountyLabel;
LocalisedStrings.GetLocStrCounty(out LocalisedCountyLabel, out Tmp);
// Done this way in case it changes
LocalisedCountyLabel = LocalisedCountyLabel.Replace(":", "").Replace("&", "");
grdResult.Columns.Clear();
grdResult.AddTextColumn("City", FPagedDataTable.Columns[PLocationTable.GetCityDBName()]);
grdResult.AddTextColumn("Post Code", FPagedDataTable.Columns[PLocationTable.GetPostalCodeDBName()]);
grdResult.AddTextColumn("Addr1", FPagedDataTable.Columns[PLocationTable.GetLocalityDBName()]);
grdResult.AddTextColumn("Street-2", FPagedDataTable.Columns[PLocationTable.GetStreetNameDBName()]);
grdResult.AddTextColumn("Addr3", FPagedDataTable.Columns[PLocationTable.GetAddress3DBName()]);
grdResult.AddTextColumn(LocalisedCountyLabel, FPagedDataTable.Columns[PLocationTable.GetCountyDBName()]);
grdResult.AddTextColumn("Country", FPagedDataTable.Columns[PLocationTable.GetCountryCodeDBName()]);
grdResult.AddTextColumn("Location Key", FPagedDataTable.Columns[PLocationTable.GetLocationKeyDBName()]);
grdResult.AddTextColumn("SiteKey", FPagedDataTable.Columns[PLocationTable.GetSiteKeyDBName()]);
grdResult.AutoResizeGrid();
}
public static DataTable GetEventUnits()
{
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
DataColumn[] Key = new DataColumn[3];
DataTable Events = new DataTable();
if (TLogging.DL >= 9)
{
Console.WriteLine("GetEventUnits called!");
}
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
string SqlStmt =
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName() +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
", " + PUnitTable.GetUnitNameDBName() +
" FROM pub_" + PPartnerTable.GetTableDBName() +
", pub_" + PUnitTable.GetTableDBName() +
", pub_" + PLocationTable.GetTableDBName() +
", pub_" + PPartnerLocationTable.GetTableDBName() +
", pub_" + PCountryTable.GetTableDBName() +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();
Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events",
Transaction, SqlParameterList.ToArray());
Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
Key[1] = Events.Columns[PPartnerLocationTable.GetSiteKeyDBName()];
Key[2] = Events.Columns[PPartnerLocationTable.GetLocationKeyDBName()];
Events.PrimaryKey = Key;
});
return(Events);
}
/// <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);
}
public static void FindAddressDuplicates(ref DataTable ADuplicateAddresses, bool AExactMatchNumber)
{
TDBTransaction Transaction = null;
DataTable ReturnTable = ADuplicateAddresses.Clone();
PLocationTable Locations = new PLocationTable();
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Checking for duplicate addresses"));
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// get all locations from database,
// except for those without a Locality, Street Name and Address as these are too vague to make a match
string Query = "SELECT p_location.* FROM p_location" +
" WHERE (p_location.p_locality_c is NOT NULL AND p_location.p_locality_c <> '')" +
" OR (p_location.p_street_name_c is NOT NULL AND p_location.p_street_name_c <> '')" +
" OR (p_location.p_address_3_c is NOT NULL AND p_location.p_address_3_c <> '')";
DBAccess.GDBAccessObj.SelectDT(Locations, Query, Transaction);
// create a list of tables grouped by country codes
List <DataTable> LocationDataTables = Locations.AsEnumerable()
.GroupBy(row => row[PLocationTable.GetCountryCodeDBName()])
.Select(g => g.CopyToDataTable())
.ToList();
DataTable BlankCountryLocations = Locations.Clone();
// create another table that contains all locations without a valid country code
for (int i = 0; i < LocationDataTables.Count; i++)
{
// this helps the time left feature to be more accurate from the start
LocationDataTables[i].DefaultView.Sort = PLocationTable.GetPostalCodeDBName() + " DESC";
LocationDataTables[i] = LocationDataTables[i].DefaultView.ToTable();
if (string.IsNullOrEmpty(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString()) ||
(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString() == "99"))
{
foreach (DataRow Row in LocationDataTables[i].Rows)
{
BlankCountryLocations.Rows.Add((object[])Row.ItemArray.Clone());
}
}
}
Int64 TotalCalculations = 0;
Int64 CompletedCalculations = 0;
decimal PercentageCompleted = 0;
// calculate number of calculations required for this check
for (int i = 0; i < LocationDataTables.Count; i++)
{
if (LocationDataTables[i].Rows.Count > 0)
{
TotalCalculations += ((Int64)LocationDataTables[i].Rows.Count) * ((Int64)LocationDataTables[i].Rows.Count - 1) / 2;
// if not table containing invalid country codes
if (!string.IsNullOrEmpty(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString()) &&
(LocationDataTables[i].Rows[0]["p_country_code_c"].ToString() != "99"))
{
TotalCalculations += BlankCountryLocations.Rows.Count;
}
}
}
Int64 TimeLeft;
int MinutesLeft;
int SecondsLeft;
Stopwatch time = Stopwatch.StartNew();
// begin search for possible duplicates
foreach (DataTable LocationCountry in LocationDataTables)
{
if (LocationCountry.Rows.Count <= 0)
{
continue;
}
for (int i = 0; i < LocationCountry.Rows.Count && ReturnTable.Rows.Count < 500; i++)
{
string AAddress = null;
string[] AAddressArray = null;
for (int j = i + 1; j < LocationCountry.Rows.Count; j++)
{
// check if two rows are a possible duplicate
if (PossibleMatch(LocationCountry.Rows[i], ref AAddress, ref AAddressArray, LocationCountry.Rows[j],
AExactMatchNumber))
{
ReturnTable.Rows.Add(new object[] {
LocationCountry.Rows[i][PLocationTable.GetSiteKeyDBName()],
LocationCountry.Rows[i][PLocationTable.GetLocationKeyDBName()],
LocationCountry.Rows[i][PLocationTable.GetLocalityDBName()],
LocationCountry.Rows[i][PLocationTable.GetStreetNameDBName()],
LocationCountry.Rows[i][PLocationTable.GetAddress3DBName()],
LocationCountry.Rows[i][PLocationTable.GetCityDBName()],
LocationCountry.Rows[i][PLocationTable.GetCountyDBName()],
LocationCountry.Rows[i][PLocationTable.GetPostalCodeDBName()],
LocationCountry.Rows[i][PLocationTable.GetCountryCodeDBName()],
LocationCountry.Rows[j][PLocationTable.GetSiteKeyDBName()],
LocationCountry.Rows[j][PLocationTable.GetLocationKeyDBName()],
LocationCountry.Rows[j][PLocationTable.GetLocalityDBName()],
LocationCountry.Rows[j][PLocationTable.GetStreetNameDBName()],
LocationCountry.Rows[j][PLocationTable.GetAddress3DBName()],
LocationCountry.Rows[j][PLocationTable.GetCityDBName()],
LocationCountry.Rows[j][PLocationTable.GetCountyDBName()],
LocationCountry.Rows[j][PLocationTable.GetPostalCodeDBName()],
LocationCountry.Rows[j][PLocationTable.GetCountryCodeDBName()]
});
}
CompletedCalculations++;
}
// if not table containing invalid country codes
if (!string.IsNullOrEmpty(LocationCountry.Rows[0]["p_country_code_c"].ToString()) &&
(LocationCountry.Rows[0]["p_country_code_c"].ToString() != "99"))
{
// compare with locations with invalid country codes
for (int j = 0; j < BlankCountryLocations.Rows.Count; j++)
{
if (PossibleMatch(LocationCountry.Rows[i], ref AAddress, ref AAddressArray, BlankCountryLocations.Rows[j],
AExactMatchNumber))
{
ReturnTable.Rows.Add(new object[] {
LocationCountry.Rows[i][PLocationTable.GetSiteKeyDBName()],
LocationCountry.Rows[i][PLocationTable.GetLocationKeyDBName()],
LocationCountry.Rows[i][PLocationTable.GetLocalityDBName()],
LocationCountry.Rows[i][PLocationTable.GetStreetNameDBName()],
LocationCountry.Rows[i][PLocationTable.GetAddress3DBName()],
LocationCountry.Rows[i][PLocationTable.GetCityDBName()],
LocationCountry.Rows[i][PLocationTable.GetCountyDBName()],
LocationCountry.Rows[i][PLocationTable.GetPostalCodeDBName()],
LocationCountry.Rows[i][PLocationTable.GetCountryCodeDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetSiteKeyDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetLocationKeyDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetLocalityDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetStreetNameDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetAddress3DBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetCityDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetCountyDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetPostalCodeDBName()],
BlankCountryLocations.Rows[j][PLocationTable.GetCountryCodeDBName()]
});
}
CompletedCalculations++;
}
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob)
{
break;
}
// estimate the remaining time
PercentageCompleted = decimal.Divide(CompletedCalculations * 100, TotalCalculations);
TimeLeft = (Int64)(time.ElapsedMilliseconds * ((100 / PercentageCompleted) - 1));
MinutesLeft = (int)TimeLeft / 60000;
string OutputMessage = string.Format(Catalog.GetString("Completed: {0}%"), Math.Round(PercentageCompleted, 1));
// only show estimated time left if at least 0.1% complete
if (PercentageCompleted >= (decimal)0.1)
{
// only show seconds if less than 10 minutes remaining
if (MinutesLeft < 10)
{
SecondsLeft = (int)(TimeLeft % 60000) / 1000;
OutputMessage += string.Format(Catalog.GetPluralString(" (approx. {0} minute and {1} seconds remaining)",
" (approx. {0} minutes and {1} seconds remaining)", MinutesLeft, true),
MinutesLeft, SecondsLeft);
}
else
{
OutputMessage += string.Format(Catalog.GetString(" (approx. {0} minutes remaining)"),
MinutesLeft);
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
OutputMessage,
PercentageCompleted);
}
}
});
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
ADuplicateAddresses = ReturnTable.Copy();
}
public static DataTable GetEventUnits(bool AIncludeConferenceUnits, bool AIncludeOutreachUnits,
string AEventName, bool AIncludeLocationData, bool ACurrentAndFutureEventsOnly)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
DataColumn[] Key = new DataColumn[1];
DataTable Events = new DataTable();
if (AEventName == "*")
{
AEventName = "";
}
else if (AEventName.EndsWith("*"))
{
AEventName = AEventName.Substring(0, AEventName.Length - 1);
}
if (TLogging.DL >= 9)
{
Console.WriteLine("GetEventUnits called!");
}
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
string SqlStmt =
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName();
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName();
}
SqlStmt = SqlStmt +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
" FROM pub_" + PPartnerTable.GetTableDBName() +
", pub_" + PUnitTable.GetTableDBName();
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
", pub_" + PLocationTable.GetTableDBName() +
", pub_" + PPartnerLocationTable.GetTableDBName() +
", pub_" + PCountryTable.GetTableDBName();
}
SqlStmt = SqlStmt +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND ";
if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND ";
}
SqlStmt = SqlStmt +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";
// add criteria for conference and/or outreach
String ConferenceWhereClause = "(" +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')";
String OutreachWhereClause = PUnitTable.GetOutreachCodeDBName() + " IS NOT NULL AND " +
PUnitTable.GetOutreachCodeDBName() + " <> '' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONF%' AND " +
PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONG%')";
if (AIncludeConferenceUnits &&
AIncludeOutreachUnits)
{
SqlStmt = SqlStmt + " AND ((" + ConferenceWhereClause + ") OR (" + OutreachWhereClause + "))";
}
else if (AIncludeConferenceUnits)
{
SqlStmt = SqlStmt + " AND (" + ConferenceWhereClause + ")";
}
else if (AIncludeOutreachUnits)
{
SqlStmt = SqlStmt + " AND (" + OutreachWhereClause + ")";
}
// add criteria for event name filter
if (AEventName.Length > 0)
{
// in case there is a filter set for the event name
AEventName = AEventName.Replace('*', '%') + "%";
SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() +
" LIKE '" + AEventName + "'";
}
if (ACurrentAndFutureEventsOnly)
{
SqlStmt = SqlStmt + " AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?";
SqlParameterList.Add(new OdbcParameter("param_date", OdbcType.Date)
{
Value = DateTime.Today.Date
});
}
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();
Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events",
ReadTransaction, SqlParameterList.ToArray());
Key[0] = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
Events.PrimaryKey = Key;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
if (TLogging.DL >= 7)
{
Console.WriteLine("GetEventUnits: committed own transaction.");
}
}
}
return(Events);
}
/// <summary>
/// return an XmlDocument with all partner info;
/// the partners are grouped by class, country, status, and sitekey
/// </summary>
/// <returns></returns>
public static string ExportPartners()
{
PartnerEditTDS MainDS = new PartnerEditTDS();
LoadDataFromDB(ref MainDS);
// Group partners into categories.
//
// A partner's category is defined by his: class, country, status, and sitekey
// It is stored as a string e.g. "FAMILY,DE,ACTIVE,0".
//
SortedList <string, List <long> > PartnerCategories = GroupPartnersIntoCategories(MainDS);
// create XML structure for each category
XmlDocument PartnerData = TYml2Xml.CreateXmlDocument();
XmlNode rootNode = PartnerData.FirstChild.NextSibling;
Int32 groupCounter = 0;
foreach (string category in PartnerCategories.Keys)
{
// get category data
groupCounter++;
XmlElement groupNode = PartnerData.CreateElement("PartnerGroup" + groupCounter.ToString());
rootNode.AppendChild(groupNode);
Int32 partnerCounter = 0;
string[] categoryDetails = category.Split(new char[] { ',' });
// may want to skip the categories with sitekey = -1
// right now, we still export them and ignore the partners 0 and 1000000 later
groupNode.SetAttribute("class", categoryDetails[0]);
groupNode.SetAttribute("Country", categoryDetails[1]);
groupNode.SetAttribute("status", categoryDetails[2]);
groupNode.SetAttribute("SiteKey", categoryDetails[3]);
List <long> partnerKeys = PartnerCategories[category];
foreach (long partnerKey in partnerKeys)
{
if ((partnerKey != 0) && (partnerKey != 1000000)) // skip organization root and the 0 when exporting
{
MainDS.PPartner.DefaultView.RowFilter = PPartnerTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
PPartnerRow partnerRow = (PPartnerRow)MainDS.PPartner.DefaultView[0].Row;
PFamilyRow familyRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_FAMILY)
{
MainDS.PFamily.DefaultView.RowFilter = PFamilyTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
familyRow = (PFamilyRow)MainDS.PFamily.DefaultView[0].Row;
}
PPersonRow personRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_PERSON)
{
MainDS.PPerson.DefaultView.RowFilter = PPersonTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
personRow = (PPersonRow)MainDS.PPerson.DefaultView[0].Row;
}
POrganisationRow organisationRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_ORGANISATION)
{
MainDS.POrganisation.DefaultView.RowFilter = POrganisationTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
organisationRow = (POrganisationRow)MainDS.POrganisation.DefaultView[0].Row;
}
PUnitRow unitRow = null;
UmUnitStructureRow unitStructureRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_UNIT)
{
MainDS.PUnit.DefaultView.RowFilter = PUnitTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
unitRow = (PUnitRow)MainDS.PUnit.DefaultView[0].Row;
MainDS.UmUnitStructure.DefaultView.RowFilter = UmUnitStructureTable.GetChildUnitKeyDBName() + " = " + partnerKey.ToString();
long numParents = MainDS.UmUnitStructure.DefaultView.Count;
if (numParents == 1)
{
unitStructureRow = (UmUnitStructureRow)MainDS.UmUnitStructure.DefaultView[0].Row;
}
else
{
throw new Exception(
"Units must have exactly one ParentUnit. " +
"The unit with partnerKey " + partnerKey.ToString() + " has " +
numParents.ToString() + ".");
}
}
PBankRow BankRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_BANK)
{
MainDS.PBank.DefaultView.RowFilter = PBankTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
BankRow = (PBankRow)MainDS.PBank.DefaultView[0].Row;
}
partnerCounter++;
XmlElement partnerNode = PartnerData.CreateElement("Partner" + partnerCounter.ToString());
groupNode.AppendChild(partnerNode);
partnerNode.SetAttribute("PartnerKey", partnerRow.PartnerKey.ToString());
//groupNode.SetAttribute("ShortName", partnerRow.PartnerShortName.ToString());
if (personRow != null)
{
partnerNode.SetAttribute("FirstName", personRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", personRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", personRow.Title.ToString());
}
else if (familyRow != null)
{
partnerNode.SetAttribute("FirstName", familyRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", familyRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", familyRow.Title.ToString());
}
else if (organisationRow != null)
{
partnerNode.SetAttribute("Name", organisationRow.OrganisationName.ToString());
}
else if (unitRow != null)
{
partnerNode.SetAttribute("Name", unitRow.UnitName.ToString());
partnerNode.SetAttribute("UnitTypeCode", unitRow.UnitTypeCode.ToString());
if (unitStructureRow != null)
{
partnerNode.SetAttribute("ParentUnitKey", unitStructureRow.ParentUnitKey.ToString());
}
}
if (BankRow != null)
{
partnerNode.SetAttribute("BranchName", BankRow.BranchName);
partnerNode.SetAttribute("BranchCode", BankRow.BranchCode);
partnerNode.SetAttribute("BranchBic", BankRow.Bic);
partnerNode.SetAttribute("EpFormatFile", BankRow.EpFormatFile);
}
partnerNode.SetAttribute("CreatedAt", partnerRow.DateCreated.Value.ToString("yyyy-MM-dd HH:mm:ss"));
// special types
string specialTypes = "";
MainDS.PPartnerType.DefaultView.RowFilter = PPartnerTypeTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
foreach (DataRowView rv in MainDS.PPartnerType.DefaultView)
{
if (specialTypes.Length > 0)
{
specialTypes += ", ";
}
specialTypes += ((PPartnerTypeRow)rv.Row).TypeCode;
}
if (specialTypes.Length > 0)
{
partnerNode.SetAttribute("SpecialTypes", specialTypes);
}
// addresses
DataView partnerLocationView = MainDS.PPartnerLocation.DefaultView;
partnerLocationView.RowFilter =
PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerRow.PartnerKey.ToString() +
"AND " + PPartnerLocationTable.GetLocationKeyDBName() + " <> 0 "; // ignore invalid addresses
Int32 addressCounter = 0;
foreach (DataRowView rv in partnerLocationView)
{
XmlElement addressNode = PartnerData.CreateElement("Address" + (addressCounter > 0 ? addressCounter.ToString() : ""));
addressCounter++;
partnerNode.AppendChild(addressNode);
PPartnerLocationRow partnerLocationRow = (PPartnerLocationRow)rv.Row;
DataView locationView = MainDS.PLocation.DefaultView;
locationView.RowFilter =
PLocationTable.GetSiteKeyDBName() + "=" + partnerLocationRow.SiteKey.ToString() + " AND " +
PLocationTable.GetLocationKeyDBName() + "=" + partnerLocationRow.LocationKey.ToString();
if (locationView.Count > 0)
{
PLocationRow locationRow = (PLocationRow)locationView[0].Row;
addressNode.SetAttribute("Street", locationRow.StreetName);
addressNode.SetAttribute("City", locationRow.City);
addressNode.SetAttribute("PostCode", locationRow.PostalCode);
}
addressNode.SetAttribute("Email", partnerLocationRow.EmailAddress);
addressNode.SetAttribute("Phone", partnerLocationRow.TelephoneNumber);
addressNode.SetAttribute("MobilePhone", partnerLocationRow.MobileNumber);
}
// TODO: notes
// TODO: This doesn't export as much data as it should?
}
}
}
return(TXMLParser.XmlToString(PartnerData));
}
