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>
/// 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);
}
