/// <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 LocationKeys for all the rows selected in the grid. /// </summary> public int[] GetSelectedLocationKeys() { int[] LocationKeys = new int[clbRecords.CheckedItemsCount]; int i = 0; foreach (DataRow Row in FDataTable.Rows) { if (Convert.ToBoolean(Row["Checked"]) == true) { LocationKeys[i] = Convert.ToInt32(Row[PLocationTable.GetLocationKeyDBName()]); i++; } } return(LocationKeys); }
/// <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> /// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable. /// /// </summary> /// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param> /// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters /// (including parameter Value)</param> /// <returns>SQL WHERE criteria /// </returns> private String BuildCustomWhereCriteria(DataTable ACriteriaData, out OdbcParameter[] AParametersArray) { String CustomWhereCriteria = ""; DataTable CriteriaDataTable; DataRow CriteriaRow; ArrayList InternalParameters; OdbcParameter miParam; CriteriaDataTable = ACriteriaData; CriteriaRow = CriteriaDataTable.Rows[0]; InternalParameters = new ArrayList(); if (CriteriaRow["Addr1"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnLocalityId, CriteriaRow, "Addr1", "Addr1Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Street2"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnStreetNameId, CriteriaRow, "Street2", "Street2Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Addr3"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnAddress3Id, CriteriaRow, "Addr3", "Addr3Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["City"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCityId, CriteriaRow, "City", "CityMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["PostCode"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnPostalCodeId, CriteriaRow, "PostCode", "PostCodeMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["County"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCountyId, CriteriaRow, "County", "CountyMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Country"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCountryCodeId, CriteriaRow, "Country", "CountryMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["LocationKey"].ToString().Length > 0) { // DISREGARD ALL OTHER SEARCH CRITERIA!!! CustomWhereCriteria = ""; InternalParameters = new ArrayList(); CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria, PLocationTable.GetTableDBName(), PLocationTable.GetLocationKeyDBName()); miParam = new OdbcParameter("", OdbcType.Decimal, 10); miParam.Value = (object)CriteriaRow["LocationKey"]; InternalParameters = new ArrayList(); InternalParameters.Add(miParam); } // TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria); /* Convert ArrayList to a array of ODBCParameters * seem to need to declare a type first */ AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter)))); InternalParameters = null; // ensure this is GC'd return(CustomWhereCriteria); }
/// <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); }
/// <summary> /// contructor /// </summary> /// <param name="ACriteriaData"></param> public TPartnerLocationFindUIConnector(DataTable ACriteriaData) : base() { Hashtable ColumnNameMapping; String CustomWhereCriteria; ArrayList InternalParameters; OdbcParameter miParam; DataRow CriteriaRow; FPagedDataSetObject = new TPagedDataSet(new PartnerFindTDSSearchResultTable()); ColumnNameMapping = null; // get the first and only row CriteriaRow = ACriteriaData.Rows[0]; // used to help with strong typing of columns InternalParameters = new ArrayList(); CustomWhereCriteria = ""; if (CriteriaRow["Addr1"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnLocalityId, CriteriaRow, "Addr1", "Addr1Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Street2"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnStreetNameId, CriteriaRow, "Street2", "Street2Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Addr3"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnAddress3Id, CriteriaRow, "Addr3", "Addr3Match", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["City"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCityId, CriteriaRow, "City", "CityMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["PostCode"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnPostalCodeId, CriteriaRow, "PostCode", "PostCodeMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["County"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCountyId, CriteriaRow, "County", "CountyMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["Country"].ToString().Length > 0) { new TDynamicSearchHelper(PLocationTable.TableId, PLocationTable.ColumnCountryCodeId, CriteriaRow, "Country", "CountryMatch", ref CustomWhereCriteria, ref InternalParameters); } if (CriteriaRow["LocationKey"].ToString().Length > 0) { // DISREGARD ALL OTHER SEARCH CRITERIA!!! CustomWhereCriteria = ""; InternalParameters = new ArrayList(); CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria, PLocationTable.GetTableDBName(), PLocationTable.GetLocationKeyDBName()); miParam = new OdbcParameter("", OdbcType.Decimal, 10); miParam.Value = (object)CriteriaRow["LocationKey"]; InternalParameters = new ArrayList(); InternalParameters.Add(miParam); } Console.WriteLine("WHERE CLAUSE: " + CustomWhereCriteria); FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters( " p_city_c, p_postal_code_c, p_locality_c, p_street_name_c, p_address_3_c, p_county_c, p_country_code_c, p_location_key_i, p_site_key_n ", "PUB_p_location ", " p_location_key_i<>-1 " + CustomWhereCriteria + ' ', "p_city_c ", ColumnNameMapping, ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))))); // fields // table // where // order by // both empty for now string session = TSession.GetSessionID(); // // Start the Find Thread // try { ThreadStart myThreadStart = delegate { FPagedDataSetObject.ExecuteQuery(session); }; FFindThread = new Thread(myThreadStart); FFindThread.Name = "PartnerLocationFind" + Guid.NewGuid().ToString(); FFindThread.Start(); } catch (Exception) { throw; } }
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)); }