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>
/// <returns></returns>
public TLocationPK DetermineCurrentLocationPK()
{
DataRow CurrentDR = this.CurrentDataRow;
TLocationPK LocationPK;
if (CurrentDR != null)
{
// get LocationKey of current DataRow
LocationPK =
new TLocationPK(Convert.ToInt64(CurrentDR[PPartnerLocationTable.GetSiteKeyDBName()]),
Convert.ToInt32(CurrentDR[PPartnerLocationTable.GetLocationKeyDBName()]));
}
else
{
LocationPK = new TLocationPK(-1, -1);
}
// MessageBox.Show('SiteKey: ' + LocationPK.SiteKey.ToString + "\r\n" +
// 'LocationKey: ' + LocationPK.LocationKey.ToString);
return(LocationPK);
}
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>
/// Determine best location for partner out of a list of possible locations. Or simply find best one
/// if no suggestion is made.
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ALocationKeyList"></param>
/// <param name="APartnerLocationKeysTable"></param>
/// <param name="ATransaction"></param>
/// <returns>True if the address was found and added, otherwise false.</returns>
private static Boolean DetermineAndAddBestLocationKey(
Int64 APartnerKey,
List <TLocationPK> ALocationKeyList,
ref PPartnerLocationTable APartnerLocationKeysTable,
TDBTransaction ATransaction)
{
PPartnerLocationTable AllPartnerLocationTable;
PPartnerLocationTable FilteredPartnerLocationTable = new PPartnerLocationTable();
TLocationPK LocationPK = new TLocationPK();
PPartnerLocationRow PartnerLocationKeyRow;
PPartnerLocationRow PartnerLocationRowCopy;
TLocationPK BestLocationPK;
if (ALocationKeyList.Count == 0)
{
// no list suggested: find best address in db for this partner
BestLocationPK = TMailing.GetPartnersBestLocation(APartnerKey);
}
else if (ALocationKeyList.Count == 1)
{
// only one location suggested: take this one
BestLocationPK = ALocationKeyList[0];
}
else
{
// Process location key list related to partner.
// In order to use Calculations.DetermineBestAddress we need to first retrieve full data
// for all suggested records from the db. Therefore load all locations for this partner
// and then create a table of the ones that are suggested.
AllPartnerLocationTable = PPartnerLocationAccess.LoadViaPPartner(APartnerKey, ATransaction);
foreach (PPartnerLocationRow PartnerLocationRow in AllPartnerLocationTable.Rows)
{
LocationPK.SiteKey = PartnerLocationRow.SiteKey;
LocationPK.LocationKey = PartnerLocationRow.LocationKey;
if (ALocationKeyList.Contains(LocationPK))
{
PartnerLocationRowCopy = (PPartnerLocationRow)FilteredPartnerLocationTable.NewRow();
DataUtilities.CopyAllColumnValues(PartnerLocationRow, PartnerLocationRowCopy);
FilteredPartnerLocationTable.Rows.Add(PartnerLocationRowCopy);
}
}
BestLocationPK = Calculations.DetermineBestAddress(FilteredPartnerLocationTable);
}
// create new row, initialize it and add it to the table
if (BestLocationPK.LocationKey != -1)
{
PartnerLocationKeyRow = (PPartnerLocationRow)APartnerLocationKeysTable.NewRow();
PartnerLocationKeyRow[PPartnerLocationTable.GetPartnerKeyDBName()] = APartnerKey;
PartnerLocationKeyRow[PPartnerLocationTable.GetSiteKeyDBName()] = BestLocationPK.SiteKey;
PartnerLocationKeyRow[PPartnerLocationTable.GetLocationKeyDBName()] = BestLocationPK.LocationKey;
// only add row if it does not already exist
if (!APartnerLocationKeysTable.Rows.Contains(
new object[] { PartnerLocationKeyRow.PartnerKey, PartnerLocationKeyRow.SiteKey, PartnerLocationKeyRow.LocationKey }))
{
APartnerLocationKeysTable.Rows.Add(PartnerLocationKeyRow);
}
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AKeyCount">The number of keys that were actually added to the extract (any duplicates are excluded)</param>
/// <param name="AIgnoredKeyList">A reference to a List of long. If not null the server will fill it with the partner keys that were ignored. Does not include duplicates.</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
/// <param name="AIgnoreInactive">true if inactive partners should be ignored</param>
/// <param name="AIgnoreNonMailingLocations">true to ignore if the partner's best address is a non-mailing location</param>
/// <param name="AIgnoreNoSolicitations">true to ignore partners where the No Solicitations flag is set</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
out Int32 AKeyCount,
out List <long> AIgnoredKeyList,
bool AIgnoreDuplicates,
bool AIgnoreInactive,
bool AIgnoreNonMailingLocations,
bool AIgnoreNoSolicitations)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
List <long> ignoredKeyList = new List <long>();
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
// Pre-process the table to remove partner rows that do not match the filter requirements
for (int i = APartnerKeysTable.Rows.Count - 1; i >= 0; i--)
{
DataRow dr = APartnerKeysTable.Rows[i];
Int64 partnerKey = Convert.ToInt64(dr[APartnerKeyColumn]);
// Get a partner record containing our fields of interest
StringCollection fields = new StringCollection();
fields.Add(PPartnerTable.GetStatusCodeDBName());
fields.Add(PPartnerTable.GetNoSolicitationsDBName());
DataTable dt = PPartnerAccess.LoadByPrimaryKey(partnerKey, fields, Transaction);
if (dt.Rows.Count > 0)
{
if (AIgnoreInactive || AIgnoreNoSolicitations)
{
bool isActive = false;
bool isNoSolicitation = false;
object o = dt.Rows[0][PPartnerTable.GetStatusCodeDBName()];
if (o != null)
{
TStdPartnerStatusCode statusCode = SharedTypes.StdPartnerStatusCodeStringToEnum(o.ToString());
isActive = (statusCode == TStdPartnerStatusCode.spscACTIVE);
}
o = dt.Rows[0][PPartnerTable.GetNoSolicitationsDBName()];
if (o != null)
{
isNoSolicitation = Convert.ToBoolean(o);
}
if ((AIgnoreInactive && !isActive) || (AIgnoreNoSolicitations && isNoSolicitation))
{
ignoredKeyList.Add(partnerKey);
APartnerKeysTable.Rows.Remove(dr);
}
}
}
else
{
ignoredKeyList.Add(partnerKey);
}
}
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
Transaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = PartnerLocationRow.PartnerKey;
if (PartnerKey > 0)
{
if (AIgnoreNonMailingLocations)
{
// The PartnerLocationRow only contains the PK fields so now we need to get the SendMail column
StringCollection fields = new StringCollection();
fields.Add(PPartnerLocationTable.GetSendMailDBName());
PPartnerLocationTable t =
PPartnerLocationAccess.LoadByPrimaryKey(PartnerKey, PartnerLocationRow.SiteKey,
PartnerLocationRow.LocationKey, fields, Transaction);
if ((t != null) && (t.Rows.Count > 0) && (((PPartnerLocationRow)t.Rows[0]).SendMail == false))
{
ignoredKeyList.Add(PartnerKey);
continue;
}
}
RecordCounter += 1;
TLogging.LogAtLevel(1, "Preparing Partner " + PartnerKey + " (Record Number " + RecordCounter + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, Transaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, Transaction);
}
});
AKeyCount = RecordCounter;
AIgnoredKeyList = ignoredKeyList;
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
/// <param name="ACommitTransaction">true if transaction should be committed at end of method</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates,
bool ACommitTransaction)
{
Boolean NewTransaction;
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, WriteTransaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
WriteTransaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.Log("Preparing Partner " + PartnerKey.ToString() + " (Record Number " + RecordCounter.ToString() + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, WriteTransaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, WriteTransaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, WriteTransaction);
}
if (ACommitTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured while extending an Extract from a list of Partner Keys:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
/// <summary>
/// extend an extract from a list of best addresses
/// </summary>
/// <param name="AExtractId">Extract Id of the Extract to extend</param>
/// <param name="APartnerKeysTable"></param>
/// <param name="APartnerKeyColumn">number of the column that contains the partner keys</param>
/// <param name="ASiteKeyColumn">number of the column that contains the site keys</param>
/// <param name="ALocationKeyColumn">number of the column that contains the location keys</param>
/// <param name="AIgnoreDuplicates">true if duplicates should be looked out for. Can be set to false if called only once and not several times per extract.</param>
public static void ExtendExtractFromListOfPartnerKeys(
Int32 AExtractId,
DataTable APartnerKeysTable,
Int32 APartnerKeyColumn,
Int32 ASiteKeyColumn,
Int32 ALocationKeyColumn,
bool AIgnoreDuplicates)
{
int RecordCounter = 0;
PPartnerLocationTable PartnerLocationKeysTable;
Int64 PartnerKey;
TDBTransaction Transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
MExtractTable ExtractTable = new MExtractTable();
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// Location Keys need to be determined as extracts do not only need partner keys but
// also Location Keys.
DetermineBestLocationKeys(APartnerKeysTable, APartnerKeyColumn, ASiteKeyColumn,
ALocationKeyColumn, out PartnerLocationKeysTable,
Transaction);
// use the returned table which contains partner and location keys to build the extract
foreach (PPartnerLocationRow PartnerLocationRow in PartnerLocationKeysTable.Rows)
{
PartnerKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetPartnerKeyDBName()]);
if (PartnerKey > 0)
{
RecordCounter += 1;
TLogging.LogAtLevel(1, "Preparing Partner " + PartnerKey + " (Record Number " + RecordCounter + ")");
// add row for partner to extract and fill with contents
MExtractRow NewRow = ExtractTable.NewRowTyped();
NewRow.ExtractId = AExtractId;
NewRow.PartnerKey = PartnerKey;
NewRow.SiteKey = Convert.ToInt64(PartnerLocationRow[PPartnerLocationTable.GetSiteKeyDBName()]);
NewRow.LocationKey = Convert.ToInt32(PartnerLocationRow[PPartnerLocationTable.GetLocationKeyDBName()]);
// only add row if it does not already exist for this partner
if (AIgnoreDuplicates || !ExtractTable.Rows.Contains(new object[] { NewRow.ExtractId, NewRow.PartnerKey, NewRow.SiteKey }))
{
ExtractTable.Rows.Add(NewRow);
}
}
}
if (ExtractTable.Rows.Count > 0)
{
// update field in extract master for quick access to number of partners in extract
MExtractMasterTable ExtractMaster = MExtractMasterAccess.LoadByPrimaryKey(AExtractId, Transaction);
ExtractMaster[0].KeyCount = ExtractTable.Rows.Count;
ExtractTable.ThrowAwayAfterSubmitChanges = true; // no need to keep data as this increases speed significantly
MExtractAccess.SubmitChanges(ExtractTable, Transaction);
MExtractMasterAccess.SubmitChanges(ExtractMaster, Transaction);
}
});
}
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);
}
