private void InitUserControlsManually() { // set controls in filter to default values ucoFilter.InitialiseUserControl(); // Hook up EventFilterChanged Event to be able to react to changed filter ucoFilter.EventFilterChanged += new TEventHandlerEventFilterChanged(this.EventFilterChanged); //grdEvent.AutoSizeCells(); // now the filter is initialized we can load the initial data LoadEventListData(); grdEvent.Columns.Clear(); grdEvent.AddTextColumn("Event Name", FEventTable.Columns[PPartnerTable.GetPartnerShortNameDBName()]); grdEvent.AddTextColumn("Event Code", FEventTable.Columns[PUnitTable.GetOutreachCodeDBName()]); grdEvent.AddTextColumn("Country", FEventTable.Columns[PCountryTable.GetCountryNameDBName()]); grdEvent.AddDateColumn("Start Date", FEventTable.Columns[PPartnerLocationTable.GetDateEffectiveDBName()]); grdEvent.AddDateColumn("End Date", FEventTable.Columns[PPartnerLocationTable.GetDateGoodUntilDBName()]); grdEvent.AddPartnerKeyColumn("Event Key", FEventTable.Columns[PPartnerTable.GetPartnerKeyDBName()]); grdEvent.AddTextColumn("Event Type", FEventTable.Columns[PUnitTable.GetUnitTypeCodeDBName()]); FEventTable.DefaultView.AllowDelete = false; FEventTable.DefaultView.AllowEdit = false; FEventTable.DefaultView.AllowNew = false; grdEvent.Selection.EnableMultiSelection = false; grdEvent.AutoResizeGrid(); grdEvent.Selection.SelectRow(1, true); }
/// <summary> /// Gets the filter criteria. /// </summary> /// <returns></returns> public string GetFilterCriteria() { string ReturnValue = PUnitTable.GetUnitNameDBName() + " LIKE '" + NameFilter + "%'"; // add filter for current and future events if (CurrentAndFutureEventsOnly) { ReturnValue += " AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= '" + DateTime.Today.Date + "'"; } // add filter 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 (IncludeConferenceUnits && IncludeOutreachUnits) { ReturnValue += " AND ((" + ConferenceWhereClause + ") OR (" + OutreachWhereClause + "))"; } else if (IncludeConferenceUnits) { ReturnValue += " AND (" + ConferenceWhereClause + ")"; } else if (IncludeOutreachUnits) { ReturnValue += " AND (" + OutreachWhereClause + ")"; } return(ReturnValue); }
/// <summary> /// get the current address from a location table /// </summary> /// <param name="ATable">table with locations</param> /// <returns>data view containing the current address</returns> public static DataView DetermineCurrentAddresses(PPartnerLocationTable ATable) { return(new DataView(ATable, "((" + PPartnerLocationTable.GetDateEffectiveDBName() + " <= #" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "# OR " + PPartnerLocationTable.GetDateEffectiveDBName() + " IS NULL) AND (" + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= #" + DateTime.Now.Date.ToString("yyyy-MM-dd") + "# OR " + PPartnerLocationTable.GetDateGoodUntilDBName() + " IS NULL))", "", DataViewRowState.CurrentRows)); }
/// <summary> /// get the current address from a location table /// </summary> /// <param name="ATable">table with locations</param> /// <returns>data view containing the current address</returns> public static DataView DetermineCurrentAddresses(PPartnerLocationTable ATable) { // dd/MM/yyyy did not work on Mono on Mac // see also http://www.csharp-examples.net/dataview-rowfilter/ return(new DataView(ATable, "((" + PPartnerLocationTable.GetDateEffectiveDBName() + " <= #" + DateTime.Now.Date.ToString( "yyyy-MM-dd") + "# OR " + PPartnerLocationTable.GetDateEffectiveDBName() + " IS NULL) AND (" + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= #" + DateTime.Now.Date.ToString( "yyyy-MM-dd") + "# OR " + PPartnerLocationTable.GetDateGoodUntilDBName() + " IS NULL))", "", DataViewRowState.CurrentRows)); }
/// <summary> /// check the validity of each location and update the icon of each location (current address, old address, future address) /// </summary> /// <param name="APartnerLocationsDT">the datatable to check</param> /// <param name="ADateToCheck"></param> public static void DeterminePartnerLocationsDateStatus(DataTable APartnerLocationsDT, DateTime ADateToCheck) { System.DateTime pDateEffective; System.DateTime pDateGoodUntil; /* * Add custom DataColumn if its not part of the DataTable yet */ if (!APartnerLocationsDT.Columns.Contains(PARTNERLOCATION_ICON_COLUMN)) { APartnerLocationsDT.Columns.Add(new System.Data.DataColumn(PARTNERLOCATION_ICON_COLUMN, typeof(Int32))); } /* * Loop over all DataRows and determine their 'Date Status'. The result is then * stored in the 'Icon' DataColumn. */ foreach (DataRow pRow in APartnerLocationsDT.Rows) { if (pRow.RowState != DataRowState.Deleted) { bool Unchanged = pRow.RowState == DataRowState.Unchanged; pDateEffective = TSaveConvert.ObjectToDate(pRow[PPartnerLocationTable.GetDateEffectiveDBName()]); pDateGoodUntil = TSaveConvert.ObjectToDate( pRow[PPartnerLocationTable.GetDateGoodUntilDBName()], TNullHandlingEnum.nhReturnHighestDate); // Current Address: Icon = 1, // Future Address: Icon = 2, // Expired Address: Icon = 3. if ((pDateEffective <= ADateToCheck) && ((pDateGoodUntil >= ADateToCheck) || (pDateGoodUntil == new DateTime(9999, 12, 31)))) { pRow[PartnerEditTDSPPartnerLocationTable.GetIconDBName()] = ((object)1); } else if (pDateEffective > ADateToCheck) { pRow[PartnerEditTDSPPartnerLocationTable.GetIconDBName()] = ((object)2); } else { pRow[PartnerEditTDSPPartnerLocationTable.GetIconDBName()] = ((object)3); } if (Unchanged) { // We do not want changing the Icon column to enable save. So revert row status to original. pRow.AcceptChanges(); } } } }
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> /// todoComment /// </summary> /// <param name="e"></param> /// <param name="AVerificationResultCollection"></param> /// <param name="AVerificationResult"></param> public static void VerifyDates(DataColumnChangeEventArgs e, TVerificationResultCollection AVerificationResultCollection, out TVerificationResult AVerificationResult) { AVerificationResult = null; DateTime DateGoodUntil = new DateTime(); DateTime DateEffective = new DateTime(); try { if (e.Column.ColumnName == PPartnerLocationTable.GetDateEffectiveDBName()) { // MessageBox.Show('p_date_effective_d: ' + e.Row['p_date_effective_d', DataRowVersion.Current].ToString); DateEffective = TSaveConvert.ObjectToDate(e.ProposedValue); DateGoodUntil = TSaveConvert.ObjectToDate(e.Row[PPartnerLocationTable.GetDateGoodUntilDBName()]); } else if (e.Column.ColumnName == PPartnerLocationTable.GetDateGoodUntilDBName()) { DateEffective = TSaveConvert.ObjectToDate(e.Row[PPartnerLocationTable.GetDateEffectiveDBName()]); DateGoodUntil = TSaveConvert.ObjectToDate(e.ProposedValue); } // MessageBox.Show('p_date_effective_d: ' + DateEffective.ToString + Environment.NewLine + // 'p_date_good_until_d: ' + DateGoodUntil.ToString); if (e.Column.ColumnName == PPartnerLocationTable.GetDateEffectiveDBName()) { AVerificationResult = TDateChecks.FirstLesserOrEqualThanSecondDate(DateEffective, DateGoodUntil, "Valid From", "Valid To"); } else { AVerificationResult = TDateChecks.FirstGreaterOrEqualThanSecondDate(DateGoodUntil, DateEffective, "Valid To", "Valid From"); } if (e.Column.ColumnName == PPartnerLocationTable.GetDateEffectiveDBName()) { // delete any error that might have been there from a verification of the other column AVerificationResultCollection.Remove(PPartnerLocationTable.GetDateGoodUntilDBName()); } else { // delete any error that might have been there from a verification of the other column AVerificationResultCollection.Remove(PPartnerLocationTable.GetDateEffectiveDBName()); } } catch (Exception Exp) { MessageBox.Show("Exception occured in TPartnerAddressVerification.VerifyDates: " + Exp.ToString()); } }
/// <summary> /// todoComment /// </summary> /// <param name="e"></param> /// <param name="AVerificationResultCollection"></param> /// <param name="AVerificationResult"></param> /// <returns></returns> public static Boolean VerifyPartnerLocationData(DataColumnChangeEventArgs e, TVerificationResultCollection AVerificationResultCollection, out TVerificationResult AVerificationResult) { Boolean ReturnValue; AVerificationResult = null; // MessageBox.Show('Verifying DataRow...'); if ((e.Column.ColumnName == PPartnerLocationTable.GetDateEffectiveDBName()) || (e.Column.ColumnName == PPartnerLocationTable.GetDateGoodUntilDBName())) { VerifyDates(e, AVerificationResultCollection, out AVerificationResult); } if (e.Column.Ordinal == ((PPartnerLocationTable)e.Column.Table).ColumnEmailAddress.Ordinal) { VerifyEmailAddress(e, out AVerificationResult); } if (e.Column.Ordinal == ((PPartnerLocationTable)e.Column.Table).ColumnLocationType.Ordinal) { VerifyLocationType(e, out AVerificationResult); } // any verification errors? if (AVerificationResult == null) { ReturnValue = true; } else { ReturnValue = false; // MessageBox.Show('VerifyPartnerLocationData: There was an error!'); } return(ReturnValue); }
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> /// 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> /// Creates a list of locations for a given Partner. /// </summary> /// <remarks>Corresponds in parts with with Progress 4GL Method /// 'GetPartnerLocations' in partner/maillib.p.</remarks> /// <param name="APartnerKey">PartnerKey of the Partner being processed.</param> /// <param name="AMailingAddressesOnly">If true: only include addresses with mailing flag set.</param> /// <param name="AIncludeCurrentAddresses">If true: include current addresses.</param> /// <param name="AIncludeFutureAddresses">If true: include future addresses.</param> /// <param name="AIncludeExpiredAddresses">If true: include expired addresses.</param> /// <param name="APartnerLocations">The Locations of the Partner being processed.</param> /// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null /// gets passed then the Method executes DB commands with the 'globally available' /// <see cref="DBAccess.GDBAccessObj" /> instance, otherwise with the instance that gets passed in with this /// Argument!</param> /// <returns>False if an invalid PartnerKey was passed in or if Petra Security /// denied access to the Partner, otherwise true.</returns> public static bool GetPartnerLocations(Int64 APartnerKey, bool AMailingAddressesOnly, bool AIncludeCurrentAddresses, bool AIncludeFutureAddresses, bool AIncludeExpiredAddresses, out PPartnerLocationTable APartnerLocations, TDataBase ADataBase = null) { TDBTransaction ReadTransaction; Boolean NewTransaction; String SelectSQL; DataSet FillDataSet; OdbcParameter param; // Initialise out Argument APartnerLocations = null; if (APartnerKey > 0) { TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Checking access to Partner."); if (TSecurity.CanAccessPartnerByKey(APartnerKey, false, ADataBase) == TPartnerAccessLevelEnum.palGranted) { ReadTransaction = DBAccess.GetDBAccessObj(ADataBase).GetNewOrExistingTransaction( IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); // Load Partner Locations, taking passed in restrictions into account. try { SelectSQL = "SELECT *" + " FROM PUB_" + PPartnerLocationTable.GetTableDBName() + " WHERE " + PPartnerLocationTable.GetPartnerKeyDBName() + " = ?" + " AND (NOT ? = true OR (? = true AND " + PPartnerLocationTable.GetSendMailDBName() + " = true))" + " AND ((? = true AND ((" + PPartnerLocationTable.GetDateEffectiveDBName() + " <= ?" + " OR " + PPartnerLocationTable.GetDateEffectiveDBName() + " IS NULL)" + " AND (" + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?" + " OR " + PPartnerLocationTable.GetDateGoodUntilDBName() + " IS NULL)))" + " OR (? = true AND " + PPartnerLocationTable.GetDateEffectiveDBName() + " > ?)" + " OR (? = true AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " < ?))"; List <OdbcParameter> parameters = new List <OdbcParameter>(); param = new OdbcParameter("PartnerKey", OdbcType.Decimal, 10); param.Value = APartnerKey; parameters.Add(param); param = new OdbcParameter("MailingAddressOnly1", OdbcType.Bit); param.Value = AMailingAddressesOnly; parameters.Add(param); param = new OdbcParameter("MailingAddressOnly2", OdbcType.Bit); param.Value = AMailingAddressesOnly; parameters.Add(param); param = new OdbcParameter("IncludeCurrentAddresses", OdbcType.Bit); param.Value = AIncludeCurrentAddresses; parameters.Add(param); param = new OdbcParameter("TodaysDate1", OdbcType.Date); param.Value = DateTime.Now; parameters.Add(param); param = new OdbcParameter("TodaysDate2", OdbcType.Date); param.Value = DateTime.Now; parameters.Add(param); param = new OdbcParameter("IncludeFutureAddresses", OdbcType.Bit); param.Value = AIncludeFutureAddresses; parameters.Add(param); param = new OdbcParameter("TodaysDate3", OdbcType.Date); param.Value = DateTime.Now; parameters.Add(param); param = new OdbcParameter("IncludeExpiredAddresses", OdbcType.Bit); param.Value = AIncludeExpiredAddresses; parameters.Add(param); param = new OdbcParameter("TodaysDate4", OdbcType.Date); param.Value = DateTime.Now; parameters.Add(param); /* * Our out Argument 'APartnerLocations' is a Typed DataTable, but SelectDT * returns an untyped DataTable, therefore we need to create a Typed DataTable * that contains the data of the returned untyped DataTable! */ FillDataSet = new DataSet(); APartnerLocations = new PPartnerLocationTable(PPartnerLocationTable.GetTableDBName()); FillDataSet.Tables.Add(APartnerLocations); DBAccess.GetDBAccessObj(ADataBase).Select(FillDataSet, SelectSQL, PPartnerLocationTable.GetTableDBName(), ReadTransaction, parameters.ToArray()); // TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: FillDataSet.Tables.Count: " + FillDataSet.Tables.Count.ToString()); FillDataSet.Tables.Remove(APartnerLocations); if (APartnerLocations.Rows.Count > 0) { // TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: Found " + APartnerLocations.Rows.Count.ToString() + " PartnerLocations found for Partner " + APartnerKey.ToString() + "."); } else { /* * /* No Rows returned = no PartnerLocations for Partner. * That shouldn't happen with existing Partners, but if it does (eg. non-existing * PartnerKey passed in) we return an empty Typed DataTable. */ // TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: No PartnerLocations found for Partner " + APartnerKey.ToString() + "!"); APartnerLocations = new PPartnerLocationTable(); } } finally { if (NewTransaction) { DBAccess.GetDBAccessObj(ADataBase).CommitTransaction(); TLogging.LogAtLevel(7, "TMailing.GetPartnerLocations: committed own transaction."); } } return(true); } else { TLogging.LogAtLevel(8, "TMailing.GetPartnerLocations: Access to Partner DENIED!"); // Petra Security prevents us from accessing this Partner -> return false; return(false); } } else { // Invalid PartnerKey -> return false; return(false); } }
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); }