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 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); }
/// <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); }
private void BtnOK_Click(System.Object sender, EventArgs e) { if (clbAddress.CheckedItemsCount == 0) { FUserAnswer = "CHANGE-NONE"; } else if (clbAddress.CheckedItemsCount == FPartnerSharingLocationDV.Count) { FUserAnswer = "CHANGE-ALL"; } else { FUserAnswer = "CHANGE-SOME" + ':'; FUserAnswer = FUserAnswer + clbAddress.GetCheckedStringList(); // return list separated by ';' FUserAnswer.Replace(',', ';'); } //MessageBox.Show("FUserAnswer: " + FUserAnswer); if ((FUserAnswer == "CHANGE-NONE") || (FUserAnswer.StartsWith("CHANGE-SOME"))) { /* Check whether user has CREATE right on p_location table */ if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapCREATE, PLocationTable.GetTableDBName())) { TMessages.MsgSecurityException(new ESecurityDBTableAccessDeniedException("", "create", PLocationTable.GetTableDBName()), this.GetType()); MessageBox.Show(Catalog.GetString("Due to the selection that you have made" + " a new Address would need" + "\r\n" + "to be created. However, you do not have permission to do this." + "\r\n" + "\r\n" + "Either select 'Change all' to change all addresses (if this is appropriate)," + "\r\n" + "or choose 'Cancel' to abort the Save operation."), Catalog.GetString("Security Violation - Explanation"), MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } else { DialogResult = System.Windows.Forms.DialogResult.OK; } } else { DialogResult = System.Windows.Forms.DialogResult.OK; } Close(); }
/// <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 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); }