/// <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 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 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(); }
private static bool PossibleMatch(DataRow AAddressARow, ref string AAddressA, ref string[] AAddressAArray, DataRow AAddressBRow, bool AExactMatchNumber) { bool ReturnValue = true; bool MatchingPostCodes = false; string AddressB = null; // if addresses have two different postcodes then discount immediately if (!string.IsNullOrEmpty(AAddressARow[PLocationTable.GetPostalCodeDBName()].ToString()) && !string.IsNullOrEmpty(AAddressBRow[PLocationTable.GetPostalCodeDBName()].ToString())) { if (AAddressARow[PLocationTable.GetPostalCodeDBName()].ToString().ToUpper() != AAddressBRow[PLocationTable.GetPostalCodeDBName()].ToString().ToUpper()) { return(false); } MatchingPostCodes = true; } // if this is the first time this address has got this far... if (string.IsNullOrEmpty(AAddressA)) { AAddressA = string.Join("", AAddressARow[PLocationTable.GetLocalityDBName()].ToString(), AAddressARow[PLocationTable.GetStreetNameDBName()].ToString(), AAddressARow[PLocationTable.GetAddress3DBName()].ToString()); // remove punctuation characters and replace with a space; lower case AAddressA = Regex.Replace(AAddressA, @"[^\p{L}\p{N}]+", " ", RegexOptions.Compiled).ToLower(); // make sure there is a space between all letters and numbers (i.e. caused by a typo) AAddressA = Regex.Replace(AAddressA, "(?<=[0-9])(?=[A-Za-z])|(?<=[A-Za-z])(?=[0-9])", " "); AAddressAArray = AAddressA.Split(' '); } AddressB = string.Join("", AAddressBRow[PLocationTable.GetLocalityDBName()].ToString(), AAddressBRow[PLocationTable.GetStreetNameDBName()].ToString(), AAddressBRow[PLocationTable.GetAddress3DBName()].ToString()); // remove punctuation characters and replace with a space; lower case AddressB = Regex.Replace(AddressB, @"[^\p{L}\p{N}]+", " ", RegexOptions.Compiled).ToLower(); // make sure there is a space between all letters and numbers (i.e. caused by a typo) AddressB = Regex.Replace(AddressB, "(?<=[0-9])(?=[A-Za-z])|(?<=[A-Za-z])(?=[0-9])", " "); if (string.IsNullOrWhiteSpace(AAddressA)) // if addressA is blank { // match if addressB is also blank or postcodes match if (string.IsNullOrWhiteSpace(AddressB) || MatchingPostCodes) { return(true); } else { return(false); } } String[] AddressBArray = AddressB.Split(' '); bool PossibleMatch = false; bool NumbersMatch = false; // compare AddressA to AddressB foreach (string Item1 in AAddressAArray) { if (string.IsNullOrEmpty(Item1)) { continue; } if (IsDigitsOnly(Item1)) // if word is a number { // if this word is a number but the 2nd address contains no numbers if (!AddressB.Any(char.IsDigit)) { if (MatchingPostCodes) // if matching postcodes continue { PossibleMatch = true; NumbersMatch = true; } } else { foreach (string Item2 in AddressBArray) { // Only compare two items if second item is also a number. if (IsDigitsOnly(Item2) && ((AExactMatchNumber && (Item1 == Item2)) || (!AExactMatchNumber && (ComputeDamerauLevenshteinDistance(Item1, Item2) <= 1)))) { PossibleMatch = true; NumbersMatch = true; break; } } } } else if (AddressBArray.Contains(Item1)) { // if 2nd address contains this word then success PossibleMatch = true; } else { foreach (string Item2 in AddressBArray) { // calculate max allowed distance to get from first item to the second decimal Distance = 0; if (AAddressAArray.Count() > 1) { Distance = Math.Min(Math.Floor((decimal)Math.Min(Item1.Length, Item2.Length) / 2), 2); } // if two words have a similar length then use the Levenshtein Distance algorithm to determine how alike they are. // If they have a distance less than or equal to 'Distance' then success. if (!IsDigitsOnly(Item2) && (Math.Abs(Item1.Length - Item2.Length) <= Math.Min(Math.Min(Item1.Length, Item2.Length), 2)) && (ComputeDamerauLevenshteinDistance(Item1, Item2) <= Distance)) { PossibleMatch = true; break; } } } ReturnValue = PossibleMatch; if (!ReturnValue) { break; } PossibleMatch = false; } // If a match has not been made but the numbers in the two addresses do agree then try again but the other way around. // I.e. compare AddressB to AddressA // This means that abbreviations and names that can be spelt as one or two words are still matched // and optional address elaments are ignored if only in one address. if (!ReturnValue && NumbersMatch) { foreach (string Item1 in AddressBArray) { // do not need to compare numbers again if (string.IsNullOrEmpty(Item1) || IsDigitsOnly(Item1)) { continue; } if (AAddressAArray.Contains(Item1)) { // if 2nd address contains this word then success PossibleMatch = true; } else { foreach (string Item2 in AAddressAArray) { // calculate max allowed distance to get from first item to the second decimal Distance = 0; if (AAddressAArray.Count() > 1) { Distance = Math.Min(Math.Floor((decimal)Math.Min(Item1.Length, Item2.Length) / 2), 2); } // if two words have a similar length then use the Levenshtein Distance algorithm to determine how alike they are. // If they have a distance less than or equal to 'Distance' then success. if (!IsDigitsOnly(Item2) && (Math.Abs(Item1.Length - Item2.Length) <= Math.Min(Math.Min(Item1.Length, Item2.Length), 2)) && (ComputeDamerauLevenshteinDistance(Item1, Item2) <= Distance)) { PossibleMatch = true; break; } } } ReturnValue = PossibleMatch; if (!ReturnValue) { break; } PossibleMatch = false; } } return(ReturnValue); }