/// find the current best address for the partner public static bool GetBestAddress(Int64 APartnerKey, out PLocationTable AAddress, out string ACountryNameLocal, TDBTransaction ATransaction, bool AOnlySendMail = false) { AAddress = null; ACountryNameLocal = ""; DataSet PartnerLocationsDS = new DataSet(); PartnerLocationsDS.Tables.Add(new PPartnerLocationTable()); PartnerLocationsDS.Tables.Add(new PCountryTable()); DataTable PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()]; PCountryTable CountryTable = (PCountryTable)PartnerLocationsDS.Tables[PCountryTable.GetTableName()]; CountryTable.DefaultView.Sort = PCountryTable.GetCountryCodeDBName(); // add special column BestAddress and Icon PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean))); PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32))); // find all locations of the partner, put it into a dataset PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, ATransaction); Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS); Calculations.DetermineBestAddress(PartnerLocationsDS); foreach (PPartnerLocationRow row in PartnerLocationTable.Rows) { if (AOnlySendMail && !Convert.ToBoolean(row[PPartnerLocationTable.GetSendMailDBName()])) { // ignore addresses that are not set for receiving mail. continue; } // find the row with BestAddress = 1 if (Convert.ToInt32(row["BestAddress"]) == 1) { // we also want the post address, need to load the p_location table: AAddress = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, ATransaction); // watch out for empty country codes if (AAddress[0].CountryCode.Trim().Length > 0) { if (CountryTable.DefaultView.Find(AAddress[0].CountryCode) == -1) { CountryTable.Merge(PCountryAccess.LoadByPrimaryKey(AAddress[0].CountryCode, ATransaction)); } ACountryNameLocal = CountryTable[CountryTable.DefaultView.Find(AAddress[0].CountryCode)].CountryNameLocal; } break; } } return(AAddress != null); }
/// <summary> /// get the best email address that is valid today, with some location details /// </summary> public static string GetBestEmailAddressWithDetails(Int64 APartnerKey, out PLocationTable AAddress, out string ACountryNameLocal) { string EmailAddress = ""; PLocationTable Address = null; string CountryNameLocal = ""; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { DataSet PartnerLocationsDS = new DataSet(); PartnerLocationsDS.Tables.Add(new PPartnerLocationTable()); PartnerLocationsDS.Tables.Add(new PCountryTable()); DataTable PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()]; PCountryTable CountryTable = (PCountryTable)PartnerLocationsDS.Tables[PCountryTable.GetTableName()]; CountryTable.DefaultView.Sort = PCountryTable.GetCountryCodeDBName(); // add special column BestAddress and Icon PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean))); PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32))); // find all locations of the partner, put it into a dataset PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, Transaction); Ict.Petra.Shared.MPartner.Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS); Ict.Petra.Shared.MPartner.Calculations.DetermineBestAddress(PartnerLocationsDS); foreach (PPartnerLocationRow row in PartnerLocationTable.Rows) { // find the row with BestAddress = 1 if (Convert.ToInt32(row["BestAddress"]) == 1) { if (!row.IsEmailAddressNull()) { EmailAddress = row.EmailAddress; } // we also want the post address, need to load the p_location table: Address = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, Transaction); if (CountryTable.DefaultView.Find(Address[0].CountryCode) == -1) { CountryTable.Merge(PCountryAccess.LoadByPrimaryKey(Address[0].CountryCode, Transaction)); } CountryNameLocal = CountryTable[CountryTable.DefaultView.Find(Address[0].CountryCode)].CountryNameLocal; } } }); AAddress = Address; ACountryNameLocal = CountryNameLocal; return(EmailAddress); }
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); }
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 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); }