/// <summary>
/// Returns the Family Members of a Family.
/// </summary>
/// <param name="AFamilyPartnerKey">PartnerKey of the FAMILY.</param>
/// <param name="AReadTransaction">Open DB Transaction.</param>
/// <returns>An Instance of <see cref="PartnerInfoTDSFamilyMembersTable" />.
/// If there were Family Members, there will be one DataRow for each Family Member.</returns>
private static PartnerInfoTDSFamilyMembersTable GetFamilyMembers(Int64 AFamilyPartnerKey,
TDBTransaction AReadTransaction)
{
OdbcParameter[] ParametersArray;
DataSet TmpDS;
PPersonTable FamilyPersonsDT;
PartnerInfoTDSFamilyMembersRow NewRow;
PartnerInfoTDSFamilyMembersTable FamilyMembersDT;
FamilyMembersDT = new PartnerInfoTDSFamilyMembersTable();
ParametersArray = new OdbcParameter[1];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;
TmpDS = new DataSet();
FamilyPersonsDT = new PPersonTable();
TmpDS.Tables.Add(FamilyPersonsDT);
AReadTransaction.DataBaseObj.Select(TmpDS,
"SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ", " +
PPersonTable.GetFamilyNameDBName() + ", " +
PPersonTable.GetTitleDBName() + ", " +
PPersonTable.GetFirstNameDBName() + ", " +
PPersonTable.GetMiddleName1DBName() + ", " +
PPersonTable.GetFamilyIdDBName() + ' ' +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
"PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "' " + // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
"ORDER BY " + PPersonTable.GetFamilyIdDBName() + " ASC",
PPersonTable.GetTableName(), AReadTransaction, ParametersArray, 0, 0);
// Add Persons to Table
for (Int32 Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
{
NewRow = FamilyMembersDT.NewRowTyped(false);
NewRow.PartnerKey = FamilyPersonsDT[Counter].PartnerKey;
NewRow.PartnerShortName =
Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
FamilyPersonsDT[Counter]),
TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;
FamilyMembersDT.Rows.Add(NewRow);
}
return(FamilyMembersDT);
}
private static bool GetAllReceivingFields(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetAllReceivingFields called!");
TDataBase db = DBAccess.Connect("GetAllReceivingFields");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[0];
DataSet TmpDS = ReadTransaction.DataBaseObj.Select(
"SELECT PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
", PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() +
" FROM PUB_" + PUnitTable.GetTableDBName() +
", PUB_" + PPartnerTable.GetTableDBName() + ", " +
"PUB_" + PPartnerTypeTable.GetTableDBName() +
" WHERE PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetStatusCodeDBName() + " = \"ACTIVE\"" +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetTypeCodeDBName() + " = \"LEDGER\"" +
" ORDER BY PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " ASC",
"TempTable", ReadTransaction, ParametersArray);
DataTable ResultTale = TmpDS.Tables[0];
for (int Counter = 0; Counter < ResultTale.Rows.Count; ++Counter)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = ResultTale.Rows[Counter][PUnitTable.GetPartnerKeyDBName()];
NewRow[UNIT_NAME] = ResultTale.Rows[Counter][PUnitTable.GetUnitNameDBName()];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFields: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// Retrieves the Partner ShortName, the PartnerClass and PartnerStatus.
/// </summary>
/// <param name="APartnerKey">PartnerKey to identify the Partner.</param>
/// <param name="APartnerShortName">Returns the ShortName.</param>
/// <param name="APartnerClass">Returns the PartnerClass (FAMILY, ORGANISATION, etc).</param>
/// <param name="APartnerStatus">Returns the PartnerStatus (eg. ACTIVE, DIED).</param>
/// <param name="ATransaction">Open DB Transaction.</param>
/// <returns>True if partner was found, otherwise false.</returns>
public static Boolean RetrievePartnerShortName(Int64 APartnerKey,
out String APartnerShortName,
out TPartnerClass APartnerClass,
out TStdPartnerStatusCode APartnerStatus,
TDBTransaction ATransaction)
{
Boolean ReturnValue;
StringCollection RequiredColumns;
PPartnerTable PartnerTable;
// initialise out Arguments
APartnerShortName = "";
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerClass = TPartnerClass.FAMILY;
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerStatus = TStdPartnerStatusCode.spscINACTIVE;
if (APartnerKey != 0)
{
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ATransaction, null, 0, 0);
if (PartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
// since we loaded by primary key there must just be one partner row
APartnerShortName = PartnerTable[0].PartnerShortName;
APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
APartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
ReturnValue = true;
}
}
else
{
// Return result as valid if Partner Key is 0.
ReturnValue = true;
}
return(ReturnValue);
}
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 String DetermineCurrentPartnerStatus()
{
DataRow CurrentDR = this.CurrentDataRow;
String PartnerStatus;
if (CurrentDR != null)
{
// get PartnerStatus of current DataRow
PartnerStatus = Convert.ToString(CurrentDR[PPartnerTable.GetStatusCodeDBName()]);
}
else
{
PartnerStatus = "";
}
// MessageBox.Show(PartnerStatus);
return(PartnerStatus);
}
public static PUnitTable GetActiveFieldUnits()
{
PUnitTable UnitTable = new PUnitTable();
PUnitRow UnitRow;
TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!");
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
// Load data
string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
" FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
" WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" +
" OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" +
" AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" +
" AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
" AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", Transaction);
foreach (DataRow eventRow in events.Rows)
{
UnitRow = (PUnitRow)UnitTable.NewRow();
UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
UnitRow.UnitName = Convert.ToString(eventRow[1]);
UnitTable.Rows.Add(UnitRow);
}
});
return(UnitTable);
}
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>
/// 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;
}
public static DataTable GetAvailableSites()
{
TDBTransaction ReadTransaction = new TDBTransaction();
DataTable SitesTable = new DataTable();
DataTable UnusedSitesTable = new DataTable();
DataTable UsedSitesTable = new DataTable();
DataRow SitesRow;
string IsPartnerLedger = SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER;
string SiteKey = PUnitTable.GetPartnerKeyDBName();
string SiteShortName = PUnitTable.GetUnitNameDBName();
Int64 PartnerKey;
SitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
SitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
SitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
UnusedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
UnusedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
UnusedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
UsedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
UsedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
UsedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
DBAccess.ReadTransaction(
ref ReadTransaction,
delegate
{
try
{
// Load data
string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
" FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
" WHERE ((" + PUnitTable.GetUnitTypeCodeDBName() + " = 'F')" +
" OR(" + PUnitTable.GetUnitTypeCodeDBName() + " = 'A'))" +
" AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
" AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable sites = ReadTransaction.DataBaseObj.SelectDT(SqlStmt, "fields", ReadTransaction);
foreach (DataRow tempSiteRow in sites.Rows)
{
PartnerKey = Convert.ToInt64(tempSiteRow[0]);
// check if a site is already used in table p_partner_ledger
if (PPartnerLedgerAccess.CountViaPUnit(PartnerKey, ReadTransaction) > 0)
{
SitesRow = UsedSitesTable.NewRow();
SitesRow[IsPartnerLedger] = true;
SitesRow[SiteKey] = PartnerKey;
SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]);
UsedSitesTable.Rows.Add(SitesRow);
}
else
{
SitesRow = UnusedSitesTable.NewRow();
SitesRow[IsPartnerLedger] = false;
SitesRow[SiteKey] = PartnerKey;
SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]);
UnusedSitesTable.Rows.Add(SitesRow);
}
}
// first add used sites to table
foreach (DataRow tempSiteRow in UsedSitesTable.Rows)
{
SitesRow = SitesTable.NewRow();
SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
SitesRow[SiteKey] = tempSiteRow[SiteKey];
SitesRow[SiteShortName] = tempSiteRow[SiteShortName];
SitesTable.Rows.Add(SitesRow);
}
// and now add unused sites to table
foreach (DataRow tempSiteRow in UnusedSitesTable.Rows)
{
SitesRow = SitesTable.NewRow();
SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
SitesRow[SiteKey] = tempSiteRow[SiteKey];
SitesRow[SiteShortName] = tempSiteRow[SiteShortName];
SitesTable.Rows.Add(SitesRow);
}
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
});
return(SitesTable);
}
public static Boolean MergedPartnerDetails(Int64 AMergedPartnerPartnerKey,
out String AMergedPartnerPartnerShortName,
out TPartnerClass AMergedPartnerPartnerClass,
out Int64 AMergedIntoPartnerKey,
out String AMergedIntoPartnerShortName,
out TPartnerClass AMergedIntoPartnerClass,
out String AMergedBy,
out DateTime AMergeDate)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
StringCollection RequiredColumns;
PPartnerTable MergedPartnerTable;
PPartnerTable PartnerMergedIntoTable;
PPartnerMergeTable PartnerMergeTable;
Boolean ReturnValue = false;
// Initialise out Arguments
AMergedPartnerPartnerShortName = "";
AMergedPartnerPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AMergedIntoPartnerKey = -1;
AMergedIntoPartnerShortName = "";
AMergedIntoPartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AMergedBy = "";
AMergeDate = DateTime.MinValue;
if (AMergedPartnerPartnerKey != 0)
{
/*
* First we look up the Partner that was Merged to get some details of it.
*/
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
MergedPartnerTable = PPartnerAccess.LoadByPrimaryKey(
AMergedPartnerPartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);
if (MergedPartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(
MergedPartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
{
ReturnValue = false;
}
else
{
AMergedPartnerPartnerShortName = MergedPartnerTable[0].PartnerShortName;
AMergedPartnerPartnerClass = SharedTypes.PartnerClassStringToEnum(MergedPartnerTable[0].PartnerClass);
/*
* Now we look up the Partner that was Merged in the PartnerMerge DB Table
* to get the information about the Merged-Into Partner.
*/
PartnerMergeTable = PPartnerMergeAccess.LoadByPrimaryKey(AMergedPartnerPartnerKey, ReadTransaction);
if (PartnerMergeTable.Rows.Count == 0)
{
/*
* Although we didn't find the Merged Partner in the PartnerMerge
* DB Table it is still a valid, Merged Partner, so we return true
* in this case.
*/
ReturnValue = true;
}
else
{
/*
* Now we look up the Merged-Into Partner to get some details of it.
*/
PartnerMergedIntoTable = PPartnerAccess.LoadByPrimaryKey(
PartnerMergeTable[0].MergeTo, RequiredColumns,
ReadTransaction, null, 0, 0);
if (PartnerMergedIntoTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
AMergedIntoPartnerKey = PartnerMergeTable[0].MergeTo;
AMergedBy = PartnerMergeTable[0].MergedBy;
AMergeDate = PartnerMergeTable[0].MergeDate.Value;
AMergedIntoPartnerShortName = PartnerMergedIntoTable[0].PartnerShortName;
AMergedIntoPartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerMergedIntoTable[0].PartnerClass);
ReturnValue = true;
}
}
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.MergedPartnerDetails: committed own transaction.");
}
}
}
return(ReturnValue);
}
public static Boolean VerifyPartnerAndGetDetails(Int64 APartnerKey,
out String APartnerShortName,
out TPartnerClass APartnerClass,
out Boolean AIsMergedPartner,
out Boolean AUserCanAccessPartner)
{
APartnerShortName = "";
APartnerClass = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
AIsMergedPartner = false;
AUserCanAccessPartner = false;
TDBTransaction ReadTransaction;
Boolean NewTransaction;
StringCollection RequiredColumns;
PPartnerTable PartnerTable;
Boolean ReturnValue = true;
TStdPartnerStatusCode PartnerStatus = TStdPartnerStatusCode.spscACTIVE;
// initialise outout Arguments
if (APartnerKey != 0)
{
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
RequiredColumns.Add(PPartnerTable.GetRestrictedDBName());
RequiredColumns.Add(PPartnerTable.GetUserIdDBName());
RequiredColumns.Add(PPartnerTable.GetGroupIdDBName());
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.VerifyPartner: committed own transaction.");
}
}
if (PartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
// since we loaded by primary key there must just be one partner row
APartnerShortName = PartnerTable[0].PartnerShortName;
APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
PartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
// check if user can access partner
if (Ict.Petra.Server.MPartner.Common.TSecurity.CanAccessPartner(PartnerTable[0]) == TPartnerAccessLevelEnum.palGranted)
{
AUserCanAccessPartner = true;
}
// check if partner is merged
if (PartnerStatus == TStdPartnerStatusCode.spscMERGED)
{
AIsMergedPartner = true;
}
else
{
AIsMergedPartner = false;
}
ReturnValue = true;
}
}
else
{
// Return result as valid if Partner Key is 0.
ReturnValue = true;
}
return(ReturnValue);
}
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);
}
public static PUnitTable GetActiveFieldUnits(string AFieldName)
{
PUnitTable UnitTable = new PUnitTable();
PUnitRow UnitRow;
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
if (AFieldName == "*")
{
AFieldName = "";
}
else if (AFieldName.EndsWith("*"))
{
AFieldName = AFieldName.Substring(0, AFieldName.Length - 1);
}
TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!");
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
// Load data
string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
" FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
" WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" +
" OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" +
" AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" +
" AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
" AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";
if (AFieldName.Length > 0)
{
// in case there is a filter set for the event name
AFieldName = AFieldName.Replace('*', '%') + "%";
SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() +
" LIKE '" + AFieldName + "'";
}
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", ReadTransaction);
foreach (DataRow eventRow in events.Rows)
{
UnitRow = (PUnitRow)UnitTable.NewRow();
UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
UnitRow.UnitName = Convert.ToString(eventRow[1]);
UnitTable.Rows.Add(UnitRow);
}
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerDataReaderWebConnector.GetActiveFieldUnits: committed own transaction.");
}
}
return(UnitTable);
}