private void SetupGrid()
{
// create a new table
FCriteriaData.Columns.Add(PBankTable.GetPartnerKeyDBName(), Type.GetType("System.Int64"));
FCriteriaData.Columns.Add(BankTDSPBankTable.GetSiteKeyDBName(), Type.GetType("System.Int64"));
FCriteriaData.Columns.Add(BankTDSPBankTable.GetLocationKeyDBName(), Type.GetType("System.Int32"));
FCriteriaData.Columns.Add(PBankTable.GetBranchNameDBName(), Type.GetType("System.String"));
FCriteriaData.Columns.Add(PBankTable.GetBranchCodeDBName(), Type.GetType("System.String"));
FCriteriaData.Columns.Add(PBankTable.GetBicDBName(), Type.GetType("System.String"));
FCriteriaData.Columns.Add(BankTDSPBankTable.GetCityDBName(), Type.GetType("System.String"));
FCriteriaData.Columns.Add(BankTDSPBankTable.GetCountryCodeDBName(), Type.GetType("System.String"));
FCriteriaData.Columns.Add(BankTDSPBankTable.GetStatusCodeDBName(), Type.GetType("System.String"));
FCriteriaData.PrimaryKey = new DataColumn[] {
FCriteriaData.Columns[PBankTable.GetPartnerKeyDBName()],
FCriteriaData.Columns[BankTDSPBankTable.GetSiteKeyDBName()], FCriteriaData.Columns[BankTDSPBankTable.GetLocationKeyDBName()]
};
FCriteriaData.DefaultView.AllowNew = false;
// add columns to the grid
grdDetails.Columns.Clear();
grdDetails.AddTextColumn("Partner Key", FCriteriaData.Columns[PBankTable.GetPartnerKeyDBName()], 80);
grdDetails.AddTextColumn("Bank Name", FCriteriaData.Columns[PBankTable.GetBranchNameDBName()]);
grdDetails.AddTextColumn("Bank/Branch Code", FCriteriaData.Columns[PBankTable.GetBranchCodeDBName()], 120);
grdDetails.AddTextColumn("BIC/SWIFT Code", FCriteriaData.Columns[PBankTable.GetBicDBName()], 110);
grdDetails.AddTextColumn("Status", FCriteriaData.Columns[BankTDSPBankTable.GetStatusCodeDBName()], 70);
grdDetails.AddTextColumn("City", FCriteriaData.Columns[PLocationTable.GetCityDBName()], 80);
grdDetails.AddTextColumn("Country", FCriteriaData.Columns[PLocationTable.GetCountryCodeDBName()], 60);
grdDetails.DoubleClickCell += new TDoubleClickCellEventHandler(grdDetails_DoubleClickCell);
grdDetails.EnterKeyPressed += new TKeyPressedEventHandler(grdDetails_EnterKey);
grdDetails.Selection.FocusRowEntered += new SourceGrid.RowEventHandler(this.FocusedRowChanged);
}
/// <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 void Filter(System.Object sender, EventArgs e)
{
// Apply filter to the grid
DataView MyDataView = FCriteriaData.DefaultView;
String Filter = "";
if (!string.IsNullOrEmpty(txtBranchName.Text))
{
Filter += FCriteriaData.Columns[PBankTable.GetBranchNameDBName()] + " LIKE '" + txtBranchName.Text + "%'";
}
if (!string.IsNullOrEmpty(txtBranchCode.Text))
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FCriteriaData.Columns[PBankTable.GetBranchCodeDBName()] + " LIKE '" + txtBranchCode.Text + "%'";
}
if (!string.IsNullOrEmpty(txtBicCode.Text))
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FCriteriaData.Columns[PBankTable.GetBicDBName()] + " LIKE '" + txtBicCode.Text + "%'";
}
if (!string.IsNullOrEmpty(txtCity.Text))
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FCriteriaData.Columns[PLocationTable.GetCityDBName()] + " LIKE '" + txtCity.Text + "%'";
}
if (!string.IsNullOrEmpty(txtCountry.Text))
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FCriteriaData.Columns[PLocationTable.GetCountryCodeDBName()] + " LIKE '" + txtCountry.Text + "%'";
}
if (!chkShowInactive.Checked)
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FCriteriaData.Columns[BankTDSPBankTable.GetStatusCodeDBName()] + " = '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscACTIVE) + "'";
}
MyDataView.RowFilter = Filter;
grdDetails.DataSource = new DevAge.ComponentModel.BoundDataView(MyDataView);
UpdateRecordNumberDisplay();
SelectRowInGrid();
FocusedRowChanged(this, null);
}
