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); }