/// <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);
}
private void LoadListData()
{
string CheckedMember = "CHECKED";
string ValueMember = PUnitTable.GetPartnerKeyDBName();
string DisplayMember = PUnitTable.GetUnitNameDBName();
// retrieve data from server
PUnitTable UnitTable = TRemote.MPartner.Partner.WebConnectors.GetLedgerUnits();
DataView view = new DataView(UnitTable);
FFieldTable = view.ToTable(true, new string[] { ValueMember, DisplayMember });
FFieldTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbLedger.Columns.Clear();
clbLedger.AddCheckBoxColumn("", FFieldTable.Columns[CheckedMember], 17, false);
clbLedger.AddTextColumn(Catalog.GetString("Ledger Name"), FFieldTable.Columns[DisplayMember]);
clbLedger.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), FFieldTable.Columns[ValueMember]);
clbLedger.DataBindGrid(FFieldTable, DisplayMember, CheckedMember, ValueMember, false, true, false);
clbLedger.AutoStretchColumnsToFitWidth = true;
//TODO: only temporarily until settings file exists
clbLedger.SetCheckedStringList("");
}
private void InitializeManualCode()
{
string CurrencyCode;
string CurrencyName;
string ConferenceName;
TPartnerClass PartnerClass;
// display the conference name in the title bar and in a text box at the top of the screen
TRemote.MPartner.Partner.ServerLookups.WebConnectors.GetPartnerShortName(FPartnerKey, out ConferenceName, out PartnerClass);
this.Text = this.Text + " [" + ConferenceName + "]";
txtConferenceName.Text = ConferenceName;
// display the conference currency in a text box at the top of the screen and in pnlDetails
TRemote.MConference.Conference.WebConnectors.GetCurrency(FPartnerKey, out CurrencyCode, out CurrencyName);
txtConferenceCurrency.Text = CurrencyCode + ": " + CurrencyName;
txtDetailSupplement.CurrencyCode = CurrencyCode;
DataTable Table = TRemote.MConference.Conference.WebConnectors.GetOutreachTypes(FPartnerKey);
// add empty row
DataRow emptyRow = Table.NewRow();
emptyRow[PUnitTable.ColumnPartnerKeyId] = -1;
emptyRow[PUnitTable.ColumnOutreachCodeId] = string.Empty;
emptyRow[PUnitTable.ColumnUnitNameId] = Catalog.GetString("Select an outreach");
Table.Rows.Add(emptyRow);
// populate the combo box
cmbDetailOutreachType.InitialiseUserControl(Table,
PUnitTable.GetOutreachCodeDBName(),
PUnitTable.GetUnitNameDBName(),
null);
cmbDetailOutreachType.AppearanceSetup(new int[] { -1, 500 }, -1);
}
/// <summary>
/// load data for sites grid from server
/// </summary>
private void LoadSitesData()
{
string CheckedMember = SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER;
string SiteKey = PUnitTable.GetPartnerKeyDBName();
string SiteShortName = PUnitTable.GetUnitNameDBName();
AvailableSitesTable = TRemote.MSysMan.WebConnectors.GetAvailableSites();
clbSites.Columns.Clear();
clbSites.AddCheckBoxColumn("", AvailableSitesTable.Columns[CheckedMember], 17, false);
clbSites.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), AvailableSitesTable.Columns[SiteKey], 100);
clbSites.AddTextColumn(Catalog.GetString("Site Name"), AvailableSitesTable.Columns[SiteShortName], 400);
clbSites.DataBindGrid(AvailableSitesTable, "", CheckedMember, SiteKey, false, true, false);
// create list of site keys that have been checked when screen opens
OriginallyCheckedSites = new List <Int64>();
OriginallyCheckedSites.Clear();
foreach (DataRow SiteRow in AvailableSitesTable.Rows)
{
if (Convert.ToBoolean(SiteRow[SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER]))
{
OriginallyCheckedSites.Add(Convert.ToInt64(SiteRow[PUnitTable.GetPartnerKeyDBName()]));
}
}
}
private void LoadListData(string AFilter)
{
string CheckedMember = "CHECKED";
string ValueMember = PUnitTable.GetPartnerKeyDBName();
string DisplayMember = PUnitTable.GetUnitNameDBName();
PUnitTable Table;
Table = TRemote.MPartner.Partner.WebConnectors.GetActiveFieldUnits(AFilter);
DataView view = new DataView(Table);
DataTable NewTable = view.ToTable(true, new string[] { ValueMember, DisplayMember });
NewTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbField.Columns.Clear();
clbField.AddCheckBoxColumn("", NewTable.Columns[CheckedMember], 17, false);
clbField.AddTextColumn(Catalog.GetString("Field Name"), NewTable.Columns[DisplayMember], 240);
clbField.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[ValueMember], 100);
clbField.DataBindGrid(NewTable, DisplayMember, CheckedMember, ValueMember, false, true, false);
//TODO: only temporarily until settings file exists
clbField.SetCheckedStringList("");
}
private void LoadListData(string AFilter)
{
string CheckedMember = "CHECKED";
string ValueMember = PUnitTable.GetPartnerKeyDBName();
string DisplayMember = PUnitTable.GetUnitNameDBName();
PUnitTable Table;
// retrieve data from server
Table = TRemote.MPartner.Partner.WebConnectors.GetLedgerUnits(AFilter);
DataView view = new DataView(Table);
DataTable NewTable = view.ToTable(true, new string[] { ValueMember, DisplayMember });
NewTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbLedger.Columns.Clear();
clbLedger.AddCheckBoxColumn("", NewTable.Columns[CheckedMember], 17, false);
clbLedger.AddTextColumn(Catalog.GetString("Ledger Name"), NewTable.Columns[DisplayMember], 240);
clbLedger.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[ValueMember], 100);
clbLedger.DataBindGrid(NewTable, DisplayMember, CheckedMember, ValueMember, false, true, false);
// allow 'any' selection for receipt frequency
DataRow emptyRow = cmbReceiptLetterFrequency.Table.NewRow();
emptyRow[0] = string.Empty;
emptyRow[1] = Catalog.GetString("Any Frequency");
cmbReceiptLetterFrequency.Table.Rows.Add(emptyRow);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="e"></param>
/// <param name="AMainDS"></param>
/// <param name="AVerificationResult"></param>
/// <returns></returns>
public static Boolean VerifyUnitData(DataColumnChangeEventArgs e, PartnerEditTDS AMainDS, out TVerificationResult AVerificationResult)
{
Boolean ReturnValue;
AVerificationResult = null;
if (e.Column.ColumnName == PUnitTable.GetUnitNameDBName())
{
VerifyUnitNameChange(e, AMainDS, out AVerificationResult);
}
// any verification errors?
if (AVerificationResult == null)
{
ReturnValue = true;
}
else
{
ReturnValue = false;
// MessageBox.Show('VerifyUnitData: There was an error!');
}
return(ReturnValue);
}
private void FilterList(System.Object sender, EventArgs e)
{
DataView view = new DataView(FFieldTable);
view.RowFilter = PUnitTable.GetUnitNameDBName() + " LIKE '" + txtFilterFields.Text + "%'";
clbLedger.DataBindGrid(view.ToTable(), PUnitTable.GetUnitNameDBName(), "CHECKED", PUnitTable.GetPartnerKeyDBName(), false, true, false);
}
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);
}
private void UpdateDefaultCombobox(Boolean ADuringScreenActivation)
{
// update default combobox
String SelectedSites = clbSites.GetCheckedStringList();
Int64 SelectedDefaultSite = 0;
String SiteName;
// remember original selection for default site
if (ADuringScreenActivation)
{
SelectedDefaultSite = Convert.ToInt64(TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_SITEKEY));
}
else
{
if (cmbDefaultSite.SelectedValue != null)
{
SelectedDefaultSite = Convert.ToInt64(cmbDefaultSite.SelectedValue);
}
}
String[] SiteKeyArray = SelectedSites.Split(',');
Int32 Counter = 0;
// initialize data table for combobox
CmbDataTable.Clear();
for (Counter = 0; Counter < SiteKeyArray.Length; Counter++)
{
if (SiteKeyArray[Counter] != "")
{
// find site name for given key ("Find" does not work on DataTable as it does not have primary key)
SiteName = "";
foreach (DataRow SiteRow in AvailableSitesTable.Rows)
{
if (Convert.ToInt64(SiteRow[PUnitTable.GetPartnerKeyDBName()]) == Convert.ToInt64(SiteKeyArray[Counter]))
{
SiteName = SiteRow[PUnitTable.GetUnitNameDBName()].ToString();
break;
}
}
CmbDataTable.Rows.Add(string.Format("{0:0000000000}",
Convert.ToInt64(SiteKeyArray[Counter])) + " - " + SiteName, Convert.ToInt64(SiteKeyArray[Counter]));
}
}
// now reselected the original selected default site
if (SelectedDefaultSite > 0)
{
cmbDefaultSite.SelectedValue = SelectedDefaultSite;
}
}
private static bool AddFieldToTable(long AFieldKey, ref DataTable AFieldsTable, ref TDBTransaction ATransaction)
{
// First check if the unit is already in the table
foreach (DataRow Row in AFieldsTable.Rows)
{
if ((long)Row[UNIT_KEY] == AFieldKey)
{
return(true);
}
}
bool IsLedger = false;
PUnitTable UnitTable;
PPartnerTypeTable PartnerTypeTable;
StringCollection FieldList = new StringCollection();
FieldList.Add(PUnitTable.GetUnitNameDBName());
UnitTable = PUnitAccess.LoadByPrimaryKey(AFieldKey, FieldList, ATransaction);
PartnerTypeTable = PPartnerTypeAccess.LoadViaPPartner(AFieldKey, ATransaction);
foreach (PPartnerTypeRow PartnerTypeRow in PartnerTypeTable.Rows)
{
if (PartnerTypeRow.TypeCode == "LEDGER")
{
IsLedger = true;
break;
}
}
if (!IsLedger)
{
return(false);
}
if (UnitTable.Rows.Count > 0)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = AFieldKey;
NewRow[UNIT_NAME] = UnitTable.Rows[0][PUnitTable.GetUnitNameDBName()];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
return(true);
}
/// <summary>
/// Returns all or only the selected outreach options
/// </summary>
/// <param name="ASelection">List with Unit key and Unit name of the selected outreach options</param>
/// <param name="AAllOptionsInTable">True if all outreach options should be returned</param>
public static void GetSelectedOutreachOptions(ref List <KeyValuePair <long, String> > ASelection,
bool AAllOptionsInTable)
{
foreach (DataRow Row in FUnitTable.Rows)
{
if (AAllOptionsInTable || (bool)Row["Is Selected"])
{
long UnitKey = (long)Row[PUnitTable.GetPartnerKeyDBName()];
String UnitName = (String)Row[PUnitTable.GetUnitNameDBName()];
ASelection.Add(new KeyValuePair <long, String>(UnitKey, UnitName));
}
}
}
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 PUnitTable GetLedgerUnits()
{
PUnitTable UnitTable = new PUnitTable();
PUnitRow UnitRow;
TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetLedgerUnits 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() + ", " + PPartnerTypeTable.GetTableDBName() +
" WHERE pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTypeTable.GetTableDBName() + "." + PPartnerTypeTable.GetPartnerKeyDBName() +
" AND " + PPartnerTypeTable.GetTypeCodeDBName() + " = '" + MPartnerConstants.PARTNERTYPE_LEDGER + "'";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "ledgers", 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);
}
private static bool GetAllSendingFields(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
PUnitTable UnitTable;
PcAttendeeTable AttendeeTable = new PcAttendeeTable();
TLogging.LogAtLevel(9, "TConferenceOptions.GetAllSendingFields called!");
TDataBase db = DBAccess.Connect("GetAllSendingFields");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
StringCollection FieldList = new StringCollection();
FieldList.Add(PcAttendeeTable.GetHomeOfficeKeyDBName());
AttendeeTable = PcAttendeeAccess.LoadAll(FieldList, ReadTransaction);
long LastUnitKey = 0;
long NewUnitKey = 0;
String HomeOfficeColumnName = PcAttendeeTable.GetHomeOfficeKeyDBName();
foreach (DataRow AttendeeRow in AttendeeTable.Select("", HomeOfficeColumnName))
{
if (AttendeeRow[HomeOfficeColumnName] != null)
{
NewUnitKey = (long)AttendeeRow[HomeOfficeColumnName];
}
else
{
continue;
}
if (LastUnitKey == NewUnitKey)
{
continue;
}
UnitTable = PUnitAccess.LoadByPrimaryKey(NewUnitKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
DataRow ResultRow = AFieldsTable.NewRow();
ResultRow[SELECTION] = false;
ResultRow[UNIT_KEY] = NewUnitKey;
ResultRow[UNIT_NAME] = UnitTable[0][PUnitTable.GetUnitNameDBName()];
ResultRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(ResultRow);
LastUnitKey = NewUnitKey;
}
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetAllSendingFields: committed own transaction.");
}
}
return(true);
}
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>
/// save the changes
/// </summary>
/// <returns></returns>
public bool SaveChanges()
{
Boolean Result = false;
String CheckedStringList = clbSites.GetCheckedStringList();
String[] SiteKeyArray = CheckedStringList.Split(',');
Int32 Counter = 0;
List <Int64> AddedSiteKeyList = new List <Int64>();
List <Int64> RemovedSiteKeyList = new List <Int64>();
Int64 RemovedSiteKey;
String RemovedSiteName;
Boolean AnySiteRemoved = false;
String UserMessage = Catalog.GetString(
"Are you sure you want to remove access to following sites? You will not be able to create Partner Keys for them any longer! \r\n");
TVerificationResultCollection VerificationResultCollection = new TVerificationResultCollection();
TVerificationResult VerificationResult = TStringChecks.StringMustNotBeEmpty(cmbDefaultSite.Text,
lblDefaultSite.Text);
// Handle addition/removal to/from TVerificationResultCollection
VerificationResultCollection.Auto_Add_Or_AddOrRemove(null, VerificationResult, null);
if (!TDataValidation.ProcessAnyDataValidationErrors(false, VerificationResultCollection, this.GetType()))
{
return(false);
}
// save site keys selected for possible use
for (Counter = 0; Counter < SiteKeyArray.Length; Counter++)
{
AddedSiteKeyList.Add(Convert.ToInt64(SiteKeyArray[Counter]));
}
// create list of site keys that have been removed and double check with user
foreach (DataRow SiteRow in AvailableSitesTable.Rows)
{
if (!Convert.ToBoolean(SiteRow[SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER]))
{
// check if previously checked site is now no longer checked
RemovedSiteKey = Convert.ToInt64(SiteRow[PUnitTable.GetPartnerKeyDBName()]);
if (OriginallyCheckedSites.Contains(RemovedSiteKey))
{
AnySiteRemoved = true;
RemovedSiteKeyList.Add(RemovedSiteKey);
RemovedSiteName = SiteRow[PUnitTable.GetUnitNameDBName()].ToString();
UserMessage += "\r\n" + String.Format("{0:0000000000}", RemovedSiteKey) + " - " + RemovedSiteName;
}
}
}
if (AnySiteRemoved)
{
if (MessageBox.Show(UserMessage, Catalog.GetString("Remove access to Sites"), MessageBoxButtons.YesNo,
MessageBoxIcon.Warning) == DialogResult.No)
{
return(false);
}
}
// save default site key
if (cmbDefaultSite.SelectedValue != null)
{
TSystemDefaults.SetSystemDefault(SharedConstants.SYSDEFAULT_SITEKEY, cmbDefaultSite.SelectedValue.ToString());
}
Result = TRemote.MSysMan.WebConnectors.SaveSiteKeys(AddedSiteKeyList, RemovedSiteKeyList);
if (Result)
{
// reload data from server here so the list is sorted by check box value first and then site name (consider doing this
// on client in the future if performance issues)
LoadSitesData();
FPetraUtilsObject.DisableSaveButton();
// We don't have unsaved changes anymore
FPetraUtilsObject.HasChanges = false;
}
return(Result);
}
public static PUnitTable GetLedgerUnits(string ALedgerName)
{
PUnitTable UnitTable = new PUnitTable();
PUnitRow UnitRow;
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
if (ALedgerName == "*")
{
ALedgerName = "";
}
else if (ALedgerName.EndsWith("*"))
{
ALedgerName = ALedgerName.Substring(0, ALedgerName.Length - 1);
}
TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetLedgerUnits 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() + ", " + PPartnerTypeTable.GetTableDBName() +
" WHERE pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTypeTable.GetTableDBName() + "." + PPartnerTypeTable.GetPartnerKeyDBName() +
" AND " + PPartnerTypeTable.GetTypeCodeDBName() + " = '" + MPartnerConstants.PARTNERTYPE_LEDGER + "'";
if (ALedgerName.Length > 0)
{
// in case there is a filter set for the event name
ALedgerName = ALedgerName.Replace('*', '%') + "%";
SqlStmt = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() +
" LIKE '" + ALedgerName + "'";
}
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "ledgers", 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.GetLedgerUnits: committed own transaction.");
}
}
return(UnitTable);
}
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 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 bool GetFieldUnits(Int64 AConferenceKey, TUnitTypeEnum AFieldTypes, out DataTable AFieldsTable, out String AConferencePrefix)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
AFieldsTable = new DataTable("Field Units");
AConferencePrefix = "";
PUnitTable UnitTable;
DataTable TmpTable;
PmShortTermApplicationTable ShortTermerTable = new PmShortTermApplicationTable();
AFieldsTable.Columns.Add("Selection", System.Type.GetType("System.Boolean"));
AFieldsTable.Columns.Add("Unit_Key", System.Type.GetType("System.Int64"));
AFieldsTable.Columns.Add("Unit_Name", System.Type.GetType("System.String"));
AFieldsTable.Columns.Add("Outreach_Code", System.Type.GetType("System.String"));
AFieldsTable.Columns.Add("Used_in_Conference", System.Type.GetType("System.Boolean"));
AConferencePrefix = TConferenceOptions.GetConferencePrefix(AConferenceKey);
switch (AFieldTypes)
{
case TUnitTypeEnum.utSendingFields:
return(TConferenceOptions.GetSendingFields(AConferenceKey, ref AFieldsTable));
case TUnitTypeEnum.utReceivingFields:
return(TConferenceOptions.GetReceivingFields(AConferenceKey, ref AFieldsTable));
case TUnitTypeEnum.utOutreachOptions:
if (TConferenceOptions.GetOutreachOptions(AConferenceKey, out TmpTable))
{
foreach (DataRow Row in TmpTable.Rows)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = Row["Partner Key"];
NewRow[UNIT_NAME] = Row["Unit Name"];
NewRow[CAMPAIGN_CODE] = Row["Outreach Code"];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
return(true);
}
return(false);
default:
break;
}
TLogging.LogAtLevel(9, "TConferenceOptions.GetFieldUnits called!");
TDataBase db = DBAccess.Connect("GetFieldUnits");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
StringCollection FieldList = new StringCollection();
FieldList.Add(PmShortTermApplicationTable.GetStFieldChargedDBName());
FieldList.Add(PmShortTermApplicationTable.GetConfirmedOptionCodeDBName());
FieldList.Add(PmShortTermApplicationTable.GetRegistrationOfficeDBName());
ShortTermerTable = PmShortTermApplicationAccess.LoadAll(FieldList, ReadTransaction);
long LastUnitKey = 0;
long NewUnitKey = 0;
bool IsUsedInOneConference = false;
String ConfirmedOptionCode = "";
System.Type StringType = System.Type.GetType("System.String");
String SearchedColumnName = "";
switch (AFieldTypes)
{
case TUnitTypeEnum.utChargedFields:
SearchedColumnName = PmShortTermApplicationTable.GetStFieldChargedDBName();
break;
case TUnitTypeEnum.utRegisteringFields:
SearchedColumnName = PmShortTermApplicationTable.GetRegistrationOfficeDBName();
break;
default:
break;
}
foreach (DataRow ShortTermerRow in ShortTermerTable.Select("", SearchedColumnName))
{
if ((ShortTermerRow[SearchedColumnName] != null) &&
(ShortTermerRow[SearchedColumnName].ToString().Length > 0))
{
NewUnitKey = (long)ShortTermerRow[SearchedColumnName];
}
else
{
continue;
}
if (LastUnitKey != NewUnitKey)
{
if ((AFieldsTable.Rows.Count > 0) &&
(IsUsedInOneConference))
{
AFieldsTable.Rows[AFieldsTable.Rows.Count - 1][USED_IN_CONFERENCE] = true;
}
IsUsedInOneConference = false;
}
// We have to check from every shorttermer if the charged field is used
// in this conference
if (IsUsedInOneConference)
{
continue;
}
if (ShortTermerRow[PmShortTermApplicationTable.GetConfirmedOptionCodeDBName()].GetType() == StringType)
{
ConfirmedOptionCode = (string)ShortTermerRow[PmShortTermApplicationTable.GetConfirmedOptionCodeDBName()];
}
else
{
ConfirmedOptionCode = "";
}
if (ConfirmedOptionCode.StartsWith(AConferencePrefix))
{
IsUsedInOneConference = true;
}
if (LastUnitKey == NewUnitKey)
{
continue;
}
UnitTable = PUnitAccess.LoadByPrimaryKey(NewUnitKey, ReadTransaction);
if (UnitTable.Rows.Count > 0)
{
DataRow ResultRow = AFieldsTable.NewRow();
ResultRow[SELECTION] = false;
ResultRow[UNIT_KEY] = NewUnitKey;
ResultRow[UNIT_NAME] = UnitTable[0][PUnitTable.GetUnitNameDBName()];
ResultRow[CAMPAIGN_CODE] = ConfirmedOptionCode;
ResultRow[USED_IN_CONFERENCE] = IsUsedInOneConference;
AFieldsTable.Rows.Add(ResultRow);
LastUnitKey = NewUnitKey;
}
}
// Check for the previous entry the "IsUsedInConference" field
if ((AFieldsTable.Rows.Count > 0) &&
(IsUsedInOneConference))
{
AFieldsTable.Rows[AFieldsTable.Rows.Count - 1][USED_IN_CONFERENCE] = true;
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetFieldUnits: committed own transaction.");
}
}
return(true);
}
