private DataTable GetCountyListTable(TDBTransaction AReadTransaction, string ATableName)
{
return(DBAccess.GetDBAccessObj(AReadTransaction).SelectDT("SELECT DISTINCT " +
PLocationTable.GetCountryCodeDBName() + ", " +
PLocationTable.GetCountyDBName() + " FROM PUB." +
PLocationTable.GetTableDBName(), ATableName, AReadTransaction));
}
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);
}
private DataTable GetCountryListFromExistingLocationsTable(TDBTransaction AReadTransaction, string ATableName)
{
// Used eg. in Report Gift Data Export for finding donors.
return(DBAccess.GDBAccessObj.SelectDT("SELECT DISTINCT " + "PUB." + PCountryTable.GetTableDBName() + '.' +
PCountryTable.GetCountryCodeDBName() + ", " +
PCountryTable.GetCountryNameDBName() + " FROM PUB." +
PCountryTable.GetTableDBName() + " c, PUB." +
PLocationTable.GetTableDBName() + " l " +
" WHERE " + PLocationTable.GetCountyDBName() + " IS NOT NULL AND NOT " +
PLocationTable.GetCountyDBName() + " = ''" +
" AND c." + PCountryTable.GetCountryCodeDBName() + " = l." +
PLocationTable.GetCountryCodeDBName(), ATableName, AReadTransaction));
}
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);
}
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);
}
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);
}
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>
/// Determines the 'Best Address' of the PERSON and its Phone Number and Email Address.
/// </summary>
/// <param name="APhoneNumberOfPerson">Phone Number of the PERSON in international format.</param>
/// <param name="AEmailAddressOfPerson">Email Address of the PERSON.</param>
/// <returns><see cref="Ict.Petra.Shared.MPartner.TLocationPK" /> pointing to the 'Best Address' of the PERSON.</returns>
private TLocationPK DetermineAddressComponents(out string APhoneNumberOfPerson, out string AEmailAddressOfPerson)
{
TLocationPK ReturnValue = Ict.Petra.Shared.MPartner.Calculations.DetermineBestAddress(
FMainDS.Tables[PartnerEditTDSPPartnerLocationTable.GetTableName()]);
DataRow BestPartnerLocationDR;
DataRow BestLocationDR;
string TelephoneNumber;
string Extension;
string CountryCode;
// Initialise out Arguments
APhoneNumberOfPerson = null;
AEmailAddressOfPerson = null;
BestPartnerLocationDR = FMainDS.Tables[PartnerEditTDSPPartnerLocationTable.GetTableName()].Rows.Find(new object[]
{ FMainDS.PPerson[0].PartnerKey, ReturnValue.SiteKey, ReturnValue.LocationKey });
if (BestPartnerLocationDR != null)
{
BestLocationDR = FMainDS.Tables[PLocationTable.GetTableName()].Rows.Find(new object[]
{ ReturnValue.SiteKey, ReturnValue.LocationKey });
if (!BestPartnerLocationDR.IsNull(PPartnerLocationTable.GetTelephoneNumberDBName()))
{
TelephoneNumber = (string)BestPartnerLocationDR[PPartnerLocationTable.GetTelephoneNumberDBName()];
}
else
{
TelephoneNumber = String.Empty;
}
if (!BestPartnerLocationDR.IsNull(PPartnerLocationTable.GetExtensionDBName()))
{
Extension = ((int)BestPartnerLocationDR[PPartnerLocationTable.GetExtensionDBName()]).ToString();
}
else
{
Extension = String.Empty;
}
if (!BestLocationDR.IsNull(PLocationTable.GetCountryCodeDBName()))
{
CountryCode = (string)BestLocationDR[PLocationTable.GetCountryCodeDBName()];
}
else
{
CountryCode = String.Empty;
}
APhoneNumberOfPerson = Ict.Petra.Shared.MPartner.Calculations.FormatIntlPhoneNumber(
TelephoneNumber,
Extension,
CountryCode,
@TDataCache.GetCacheableDataTableFromCache);
if (!BestPartnerLocationDR.IsNull(PPartnerLocationTable.GetEmailAddressDBName()))
{
AEmailAddressOfPerson = (string)BestPartnerLocationDR[PPartnerLocationTable.GetEmailAddressDBName()];
}
else
{
AEmailAddressOfPerson = String.Empty;
}
}
else
{
MessageBox.Show("Unexpected condition: 'Best Address of PERSON is null'", DEV_FIX);
}
return(ReturnValue);
}
