public static Boolean GetConferenceApplications(ref ConferenceApplicationTDS AMainDS, Int64 AConferenceKey)
{
// make sure outreach codes are up to date in case it has changed in Unit record
TAttendeeManagement.RefreshOutreachCode(AConferenceKey);
TDataBase db = DBAccess.Connect("GetConferenceApplications");
TDBTransaction ReadTransaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
PcConferenceTable ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
if (ConferenceTable.Count == 0)
{
ReadTransaction.Rollback();
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
string OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref AMainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// obtain PPartner records for all the home offices
foreach (PcAttendeeRow AttendeeRow in AMainDS.PcAttendee.Rows)
{
if (AMainDS.PPartner.Rows.Find(new object[] { AttendeeRow.HomeOfficeKey }) == null)
{
PPartnerAccess.LoadByPrimaryKey(AMainDS, AttendeeRow.HomeOfficeKey, ReadTransaction);
}
}
ReadTransaction.Rollback();
return(true);
}
/// <summary>
/// return a list of all applicants for a given event
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventCode"></param>
/// <param name="ARegisteringOffice"></param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <param name="ATransaction"></param>
/// <returns></returns>
private static ConferenceApplicationTDS GetApplications(
ref ConferenceApplicationTDS AMainDS,
string AEventCode,
Int64 ARegisteringOffice,
string AApplicationStatus,
string ARole,
bool AClearJSONData,
TDBTransaction ATransaction)
{
LoadApplicationsFromDB(ref AMainDS,
AEventCode, ARegisteringOffice,
ARole, new Nullable <Int64>(),
ATransaction);
DataView PersonView = AMainDS.PPerson.DefaultView;
PersonView.Sort = PPersonTable.GetPartnerKeyDBName();
DataView GenAppView = AMainDS.PmGeneralApplication.DefaultView;
GenAppView.Sort = PmGeneralApplicationTable.GetPartnerKeyDBName() + "," +
PmGeneralApplicationTable.GetApplicationKeyDBName() + "," + PmGeneralApplicationTable.GetRegistrationOfficeDBName();
if (AMainDS.HasChanges())
{
AMainDS.EnforceConstraints = false;
AMainDS.AcceptChanges();
}
return(AMainDS);
}
/// <summary>
/// Refresh Outreach Code for applications and conference
/// </summary>
public static void RefreshOutreachCode(Int64 AConferenceKey)
{
TDBTransaction Transaction = new TDBTransaction();
bool SubmissionOK = true;
PcConferenceTable ConferenceTable;
PUnitTable UnitTable;
PmShortTermApplicationTable ShortTermAppTable;
ConferenceApplicationTDS MainDS;
DBAccess.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
ConferenceTable = new PcConferenceTable();
UnitTable = new PUnitTable();
ShortTermAppTable = new PmShortTermApplicationTable();
MainDS = new ConferenceApplicationTDS();
// get the conference in order to update the OutreachPrefix
ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
// update OutreachPrefix in conference record in case it was changed in Unit record for event
UnitTable = PUnitAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (UnitTable[0].OutreachCode.Length >= 5)
{
ConferenceTable[0].OutreachPrefix = UnitTable[0].OutreachCode.Substring(0, 5);
}
else
{
ConferenceTable[0].OutreachPrefix = UnitTable[0].OutreachCode;
}
MainDS.Merge(ConferenceTable);
// update event code
ShortTermAppTable = PmShortTermApplicationAccess.LoadViaPUnitStConfirmedOption(AConferenceKey, Transaction);
foreach (PmShortTermApplicationRow ShortTermAppRow in ShortTermAppTable.Rows)
{
ShortTermAppRow.ConfirmedOptionCode = UnitTable[0].OutreachCode;
}
MainDS.Merge(ShortTermAppTable);
MainDS.ThrowAwayAfterSubmitChanges = true;
ConferenceApplicationTDSAccess.SubmitChanges(MainDS);
});
}
/// <summary>
/// Refresh Outreach Code for applications and conference
/// </summary>
public static void RefreshOutreachCode(Int64 AConferenceKey)
{
TDBTransaction Transaction = null;
bool SubmissionOK = true;
PcConferenceTable ConferenceTable;
PUnitTable UnitTable;
PmShortTermApplicationTable ShortTermAppTable;
ConferenceApplicationTDS MainDS;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ConferenceTable = new PcConferenceTable();
UnitTable = new PUnitTable();
ShortTermAppTable = new PmShortTermApplicationTable();
MainDS = new ConferenceApplicationTDS();
// get the conference in order to update the OutreachPrefix
ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
// update OutreachPrefix in conference record in case it was changed in Unit record for event
UnitTable = PUnitAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (UnitTable[0].OutreachCode.Length >= 5)
{
ConferenceTable[0].OutreachPrefix = UnitTable[0].OutreachCode.Substring(0, 5);
}
else
{
ConferenceTable[0].OutreachPrefix = UnitTable[0].OutreachCode;
}
MainDS.Merge(ConferenceTable);
// update event code
ShortTermAppTable = PmShortTermApplicationAccess.LoadViaPUnitStConfirmedOption(AConferenceKey, Transaction);
foreach (PmShortTermApplicationRow ShortTermAppRow in ShortTermAppTable.Rows)
{
ShortTermAppRow.ConfirmedOptionCode = UnitTable[0].OutreachCode;
}
MainDS.Merge(ShortTermAppTable);
MainDS.ThrowAwayAfterSubmitChanges = true;
ConferenceApplicationTDSAccess.SubmitChanges(MainDS);
});
}
/// <summary>
/// if attendee is not valid anymore, the attendee should be removed from pc_attendee table
/// </summary>
/// <returns></returns>
private static bool IsAttendeeValid(ConferenceApplicationTDS AMainDS,
string AOutreachPrefix,
Int64 AAttendeeKey)
{
PmShortTermApplicationRow ShortTermRow = null;
foreach (PmShortTermApplicationRow Row in AMainDS.PmShortTermApplication.Rows)
{
if ((Row.PartnerKey == AAttendeeKey) && (AOutreachPrefix.Length >= 5) &&
(Row.ConfirmedOptionCode.Substring(0, 5) == AOutreachPrefix.Substring(0, 5)))
{
ShortTermRow = Row;
break;
}
}
if (ShortTermRow == null)
{
return(false);
}
if (ShortTermRow.StBasicDeleteFlag)
{
return(false);
}
PmGeneralApplicationRow GeneralAppRow = (PmGeneralApplicationRow)AMainDS.PmGeneralApplication.Rows.Find(
new object[] { ShortTermRow.PartnerKey, ShortTermRow.ApplicationKey, ShortTermRow.RegistrationOffice });
if (GeneralAppRow == null)
{
return(false);
}
if (GeneralAppRow.GenAppDeleteFlag)
{
return(false);
}
if (!(GeneralAppRow.GenApplicationStatus.StartsWith("H") || GeneralAppRow.GenApplicationStatus.StartsWith("A")))
{
return(false);
}
return(true);
}
public static Boolean GetConferenceApplications(ref ConferenceApplicationTDS AMainDS, Int64 AConferenceKey)
{
Boolean NewTransaction;
// make sure outreach codes are up to date in case it has changed in Unit record
TAttendeeManagement.RefreshOutreachCode(AConferenceKey);
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
PcConferenceTable ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString());
}
string OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref AMainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// obtain PPartner records for all the home offices
foreach (PcAttendeeRow AttendeeRow in AMainDS.PcAttendee.Rows)
{
if (AMainDS.PPartner.Rows.Find(new object[] { AttendeeRow.HomeOfficeKey }) == null)
{
PPartnerAccess.LoadByPrimaryKey(AMainDS, AttendeeRow.HomeOfficeKey, ReadTransaction);
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceDataReaderWebConnector.GetConferenceApplications: commit own transaction.");
}
}
return(true);
}
/// <summary>
/// return a list of all applicants for a given event, but only the registration office that the user has permissions for, ie. Module REG-00xx0000000
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventPartnerKey"></param>
/// <param name="AEventCode"></param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARegistrationOffice">if -1, then show all offices that the user has permission for</param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <returns></returns>
public static bool GetApplications(
ref ConferenceApplicationTDS AMainDS,
Int64 AEventPartnerKey,
string AEventCode,
string AApplicationStatus,
Int64 ARegistrationOffice,
string ARole,
bool AClearJSONData)
{
return(GetApplications(ref AMainDS,
AEventPartnerKey,
AEventCode,
AApplicationStatus,
ARegistrationOffice,
IsConferenceOrganisingOffice(),
ARole,
AClearJSONData));
}
private static bool LoadApplicationsFromDB(
ref ConferenceApplicationTDS AMainDS,
string AEventCode,
Int64?ARegisteringOffice,
string ARole,
Int64?APartnerKey,
TDBTransaction ATransaction)
{
ConferenceApplicationTDS MainDS = new ConferenceApplicationTDS();
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter parameter = new OdbcParameter("eventcode", OdbcType.VarChar, PmShortTermApplicationTable.GetConfirmedOptionCodeLength());
parameter.Value = AEventCode.Substring(0, 5);
parameters.Add(parameter);
string DataLabels = "(PUB_p_data_label.p_text_c = 'MedicalNotes' OR PUB_p_data_label.p_text_c = 'Rebukes')";
string queryShortTermApplication = "SELECT PUB_pm_short_term_application.* " +
"FROM PUB_pm_short_term_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? ";
string queryGeneralApplication = "SELECT PUB_pm_general_application.* " +
"FROM PUB_pm_short_term_application, PUB_pm_general_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_pm_general_application.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n " +
" AND PUB_pm_general_application.pm_application_key_i = PUB_pm_short_term_application.pm_application_key_i "
+
" AND PUB_pm_general_application.pm_registration_office_n = PUB_pm_short_term_application.pm_registration_office_n";
string queryPerson = "SELECT DISTINCT PUB_p_person.* " +
"FROM PUB_pm_short_term_application, PUB_p_person " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_p_person.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n";
string queryPartner = "SELECT DISTINCT PUB_p_partner.* " +
"FROM PUB_p_partner, PUB_pm_short_term_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND (PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n" +
" OR PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.pm_st_field_charged_n)";
string queryDataLabel = "SELECT DISTINCT PUB_p_data_label_value_partner.* " +
"FROM PUB_pm_short_term_application, PUB_p_data_label_value_partner, PUB_p_data_label " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_p_data_label_value_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n" +
" AND PUB_p_data_label_value_partner.p_data_label_key_i = PUB_p_data_label.p_key_i" +
" AND " + DataLabels;
if ((ARole != null) && (ARole.Length > 0))
{
queryGeneralApplication += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryShortTermApplication += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryPerson += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryDataLabel += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
}
if (ARegisteringOffice.HasValue && (ARegisteringOffice.Value > 0))
{
string queryRegistrationOffice =
" AND (PUB_pm_short_term_application.pm_st_field_charged_n = ? OR PUB_pm_short_term_application.pm_registration_office_n = ?)";
queryGeneralApplication += queryRegistrationOffice;
queryShortTermApplication += queryRegistrationOffice;
queryPerson += queryRegistrationOffice;
queryPartner += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryDataLabel += queryRegistrationOffice;
parameter = new OdbcParameter("fieldCharged", OdbcType.Decimal, 10);
parameter.Value = ARegisteringOffice.Value;
parameters.Add(parameter);
parameter = new OdbcParameter("registrationOffice", OdbcType.Decimal, 10);
parameter.Value = ARegisteringOffice.Value;
parameters.Add(parameter);
}
if (APartnerKey.HasValue && (APartnerKey.Value > 0))
{
queryGeneralApplication += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryShortTermApplication += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryPerson += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryPartner += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryDataLabel += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
parameter = new OdbcParameter("partnerkey", OdbcType.Decimal, 10);
parameter.Value = APartnerKey.Value;
parameters.Add(parameter);
}
DBAccess.GDBAccessObj.Select(MainDS,
queryShortTermApplication,
MainDS.PmShortTermApplication.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryPerson,
MainDS.PPerson.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryPartner,
MainDS.PPartner.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryGeneralApplication,
MainDS.PmGeneralApplication.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryDataLabel,
MainDS.PDataLabelValuePartner.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
"SELECT * FROM PUB_p_data_label WHERE " + DataLabels,
MainDS.PDataLabel.TableName, ATransaction);
AMainDS.Merge(MainDS);
AMainDS.PDataLabelValuePartner.DefaultView.Sort = PDataLabelValuePartnerTable.GetDataLabelKeyDBName() + "," +
PDataLabelValuePartnerTable.GetPartnerKeyDBName();
AMainDS.PDataLabel.DefaultView.Sort = PDataLabelTable.GetTextDBName();
return(true);
}
/// <summary>
/// return a list of all applicants for a given event,
/// but if AConferenceOrganisingOffice is false,
/// consider only the registration office that the user has permissions for, ie. Module REG-00xx0000000
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventPartnerKey">The ConferenceKey</param>
/// <param name="AEventCode">The OutreachPrefix</param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARegistrationOffice">if -1, then show all offices that the user has permission for</param>
/// <param name="AConferenceOrganisingOffice">if true, all offices are considered</param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <returns></returns>
public static bool GetApplications(
ref ConferenceApplicationTDS AMainDS,
Int64 AEventPartnerKey,
string AEventCode,
string AApplicationStatus,
Int64 ARegistrationOffice,
bool AConferenceOrganisingOffice,
string ARole,
bool AClearJSONData)
{
TDBTransaction Transaction = null;
ConferenceApplicationTDS MainDS = new ConferenceApplicationTDS();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
// load all attendees of this conference.
// only load once: GetApplications might be called for several application stati
if (MainDS.PcAttendee.Rows.Count == 0)
{
PcAttendeeRow templateAttendeeRow = MainDS.PcAttendee.NewRowTyped(false);
templateAttendeeRow.ConferenceKey = AEventPartnerKey;
PcAttendeeAccess.LoadUsingTemplate(MainDS, templateAttendeeRow, Transaction);
MainDS.PcAttendee.DefaultView.Sort = PcAttendeeTable.GetPartnerKeyDBName();
}
if (AConferenceOrganisingOffice && (ARegistrationOffice == -1))
{
// avoid duplicates, who are registered by one office, but charged to another office
GetApplications(ref MainDS, AEventCode, -1, AApplicationStatus, ARole, AClearJSONData, Transaction);
}
else
{
List <Int64> AllowedRegistrationOffices = GetRegistrationOfficeKeysOfUser(Transaction);
foreach (Int64 RegistrationOffice in AllowedRegistrationOffices)
{
if ((ARegistrationOffice == RegistrationOffice) || (ARegistrationOffice == -1))
{
GetApplications(ref MainDS, AEventCode, RegistrationOffice, AApplicationStatus, ARole, AClearJSONData, Transaction);
}
}
}
// required for DefaultView.Find
MainDS.PmShortTermApplication.DefaultView.Sort =
PmShortTermApplicationTable.GetStConfirmedOptionDBName() + "," +
PmShortTermApplicationTable.GetPartnerKeyDBName();
MainDS.PmGeneralApplication.DefaultView.Sort =
PmGeneralApplicationTable.GetPartnerKeyDBName() + "," +
PmGeneralApplicationTable.GetApplicationKeyDBName() + "," +
PmGeneralApplicationTable.GetRegistrationOfficeDBName();
MainDS.PDataLabelValuePartner.DefaultView.Sort = PDataLabelValuePartnerTable.GetDataLabelKeyDBName() + "," +
PDataLabelValuePartnerTable.GetPartnerKeyDBName();
MainDS.PDataLabel.DefaultView.Sort = PDataLabelTable.GetTextDBName();
});
AMainDS = MainDS;
if (AMainDS.HasChanges())
{
AMainDS.EnforceConstraints = false;
AMainDS.AcceptChanges();
}
return(true);
}
/// <summary>
/// return a list of all applicants for a given event
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventCode"></param>
/// <param name="ARegisteringOffice"></param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <param name="ATransaction"></param>
/// <returns></returns>
private static ConferenceApplicationTDS GetApplications(
ref ConferenceApplicationTDS AMainDS,
string AEventCode,
Int64 ARegisteringOffice,
string AApplicationStatus,
string ARole,
bool AClearJSONData,
TDBTransaction ATransaction)
{
LoadApplicationsFromDB(ref AMainDS,
AEventCode, ARegisteringOffice,
ARole, new Nullable <Int64>(),
ATransaction);
DataView PersonView = AMainDS.PPerson.DefaultView;
PersonView.Sort = PPersonTable.GetPartnerKeyDBName();
DataView GenAppView = AMainDS.PmGeneralApplication.DefaultView;
GenAppView.Sort = PmGeneralApplicationTable.GetPartnerKeyDBName() + "," +
PmGeneralApplicationTable.GetApplicationKeyDBName() + "," + PmGeneralApplicationTable.GetRegistrationOfficeDBName();
if (AMainDS.HasChanges())
{
AMainDS.EnforceConstraints = false;
AMainDS.AcceptChanges();
}
return AMainDS;
}
private static bool LoadApplicationsFromDB(
ref ConferenceApplicationTDS AMainDS,
string AEventCode,
Int64? ARegisteringOffice,
string ARole,
Int64? APartnerKey,
TDBTransaction ATransaction)
{
ConferenceApplicationTDS MainDS = new ConferenceApplicationTDS();
List <OdbcParameter>parameters = new List <OdbcParameter>();
OdbcParameter parameter = new OdbcParameter("eventcode", OdbcType.VarChar, PmShortTermApplicationTable.GetConfirmedOptionCodeLength());
parameter.Value = AEventCode.Substring(0, 5);
parameters.Add(parameter);
string DataLabels = "(PUB_p_data_label.p_text_c = 'MedicalNotes' OR PUB_p_data_label.p_text_c = 'Rebukes')";
string queryShortTermApplication = "SELECT PUB_pm_short_term_application.* " +
"FROM PUB_pm_short_term_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? ";
string queryGeneralApplication = "SELECT PUB_pm_general_application.* " +
"FROM PUB_pm_short_term_application, PUB_pm_general_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_pm_general_application.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n " +
" AND PUB_pm_general_application.pm_application_key_i = PUB_pm_short_term_application.pm_application_key_i "
+
" AND PUB_pm_general_application.pm_registration_office_n = PUB_pm_short_term_application.pm_registration_office_n";
string queryPerson = "SELECT DISTINCT PUB_p_person.* " +
"FROM PUB_pm_short_term_application, PUB_p_person " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_p_person.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n";
string queryPartner = "SELECT DISTINCT PUB_p_partner.* " +
"FROM PUB_p_partner, PUB_pm_short_term_application " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND (PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n" +
" OR PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.pm_st_field_charged_n)";
string queryDataLabel = "SELECT DISTINCT PUB_p_data_label_value_partner.* " +
"FROM PUB_pm_short_term_application, PUB_p_data_label_value_partner, PUB_p_data_label " +
"WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
" AND PUB_p_data_label_value_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n" +
" AND PUB_p_data_label_value_partner.p_data_label_key_i = PUB_p_data_label.p_key_i" +
" AND " + DataLabels;
if ((ARole != null) && (ARole.Length > 0))
{
queryGeneralApplication += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryShortTermApplication += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryPerson += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryDataLabel += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
}
if (ARegisteringOffice.HasValue && (ARegisteringOffice.Value > 0))
{
string queryRegistrationOffice =
" AND (PUB_pm_short_term_application.pm_st_field_charged_n = ? OR PUB_pm_short_term_application.pm_registration_office_n = ?)";
queryGeneralApplication += queryRegistrationOffice;
queryShortTermApplication += queryRegistrationOffice;
queryPerson += queryRegistrationOffice;
queryPartner += " AND PUB_pm_short_term_application.pm_st_congress_code_c LIKE '" + ARole + "%'";
queryDataLabel += queryRegistrationOffice;
parameter = new OdbcParameter("fieldCharged", OdbcType.Decimal, 10);
parameter.Value = ARegisteringOffice.Value;
parameters.Add(parameter);
parameter = new OdbcParameter("registrationOffice", OdbcType.Decimal, 10);
parameter.Value = ARegisteringOffice.Value;
parameters.Add(parameter);
}
if (APartnerKey.HasValue && (APartnerKey.Value > 0))
{
queryGeneralApplication += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryShortTermApplication += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryPerson += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryPartner += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
queryDataLabel += " AND PUB_pm_short_term_application.p_partner_key_n = ?";
parameter = new OdbcParameter("partnerkey", OdbcType.Decimal, 10);
parameter.Value = APartnerKey.Value;
parameters.Add(parameter);
}
DBAccess.GDBAccessObj.Select(MainDS,
queryShortTermApplication,
MainDS.PmShortTermApplication.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryPerson,
MainDS.PPerson.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryPartner,
MainDS.PPartner.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryGeneralApplication,
MainDS.PmGeneralApplication.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
queryDataLabel,
MainDS.PDataLabelValuePartner.TableName, ATransaction, parameters.ToArray());
DBAccess.GDBAccessObj.Select(MainDS,
"SELECT * FROM PUB_p_data_label WHERE " + DataLabels,
MainDS.PDataLabel.TableName, ATransaction);
AMainDS.Merge(MainDS);
AMainDS.PDataLabelValuePartner.DefaultView.Sort = PDataLabelValuePartnerTable.GetDataLabelKeyDBName() + "," +
PDataLabelValuePartnerTable.GetPartnerKeyDBName();
AMainDS.PDataLabel.DefaultView.Sort = PDataLabelTable.GetTextDBName();
return true;
}
/// <summary>
/// return a list of all applicants for a given event, but only the registration office that the user has permissions for, ie. Module REG-00xx0000000
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventPartnerKey"></param>
/// <param name="AEventCode"></param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARegistrationOffice">if -1, then show all offices that the user has permission for</param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <returns></returns>
public static bool GetApplications(
ref ConferenceApplicationTDS AMainDS,
Int64 AEventPartnerKey,
string AEventCode,
string AApplicationStatus,
Int64 ARegistrationOffice,
string ARole,
bool AClearJSONData)
{
return GetApplications(ref AMainDS,
AEventPartnerKey,
AEventCode,
AApplicationStatus,
ARegistrationOffice,
IsConferenceOrganisingOffice(),
ARole,
AClearJSONData);
}
/// <summary>
/// if attendee is not valid anymore, the attendee should be removed from pc_attendee table
/// </summary>
/// <returns></returns>
private static bool IsAttendeeValid(ConferenceApplicationTDS AMainDS,
string AOutreachPrefix,
Int64 AAttendeeKey)
{
PmShortTermApplicationRow ShortTermRow = null;
foreach (PmShortTermApplicationRow Row in AMainDS.PmShortTermApplication.Rows)
{
if ((Row.PartnerKey == AAttendeeKey) && (AOutreachPrefix.Length >= 5)
&& (Row.ConfirmedOptionCode.Substring(0, 5) == AOutreachPrefix.Substring(0, 5)))
{
ShortTermRow = Row;
break;
}
}
if (ShortTermRow == null)
{
return false;
}
if (ShortTermRow.StBasicDeleteFlag)
{
return false;
}
PmGeneralApplicationRow GeneralAppRow = (PmGeneralApplicationRow)AMainDS.PmGeneralApplication.Rows.Find(
new object[] { ShortTermRow.PartnerKey, ShortTermRow.ApplicationKey, ShortTermRow.RegistrationOffice });
if (GeneralAppRow == null)
{
return false;
}
if (GeneralAppRow.GenAppDeleteFlag)
{
return false;
}
if (!(GeneralAppRow.GenApplicationStatus.StartsWith("H") || GeneralAppRow.GenApplicationStatus.StartsWith("A")))
{
return false;
}
return true;
}
/// <summary>
/// Load/Refresh all Attendees for a conference
/// </summary>
public static void RefreshAttendees(Int64 AConferenceKey)
{
TDBTransaction Transaction = new TDBTransaction();
bool SubmissionOK = true;
PcConferenceTable ConferenceTable;
PUnitTable UnitTable;
string OutreachPrefix = String.Empty;
ConferenceApplicationTDS MainDS;
// make sure outreach codes are up to date in case it has changed in Unit record
RefreshOutreachCode(AConferenceKey);
DBAccess.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
ConferenceTable = new PcConferenceTable();
UnitTable = new PUnitTable();
MainDS = new ConferenceApplicationTDS();
// get the conference prefix which links all outreaches associated with a conference
ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref MainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// check a valid pcattendee record exists for each short term application
foreach (PmShortTermApplicationRow ShortTermAppRow in MainDS.PmShortTermApplication.Rows)
{
if (!IsAttendeeValid(MainDS, OutreachPrefix, ShortTermAppRow.PartnerKey))
{
// ignore deleted applications, or cancelled applications
continue;
}
// update outreach code in application (it may have changed)
UnitTable = PUnitAccess.LoadByPrimaryKey(ShortTermAppRow.StConfirmedOption, Transaction);
ShortTermAppRow.ConfirmedOptionCode = ((PUnitRow)UnitTable.Rows[0]).OutreachCode;
// Do we have a record for this attendee yet?
bool AttendeeRecordExists = false;
if (MainDS.PcAttendee.Rows.Contains(new object[] { AConferenceKey, ShortTermAppRow.PartnerKey }))
{
AttendeeRecordExists = true;
}
// create a new PcAttendee record if one does not already exist for this attendee
if (!AttendeeRecordExists)
{
PcAttendeeRow AttendeeRow = MainDS.PcAttendee.NewRowTyped();
AttendeeRow.ConferenceKey = AConferenceKey;
AttendeeRow.PartnerKey = ShortTermAppRow.PartnerKey;
if (ShortTermAppRow.ConfirmedOptionCode.Length >= 11)
{
AttendeeRow.OutreachType = ShortTermAppRow.ConfirmedOptionCode.Substring(5, 6);
}
PmGeneralApplicationRow GeneralAppRow = (PmGeneralApplicationRow)MainDS.PmGeneralApplication.Rows.Find(
new object[] { ShortTermAppRow.PartnerKey, ShortTermAppRow.ApplicationKey, ShortTermAppRow.RegistrationOffice });
DateTime DateAccepted = GeneralAppRow.GenAppDate;
if (!GeneralAppRow.IsGenAppSendFldAcceptDateNull())
{
DateAccepted = GeneralAppRow.GenAppSendFldAcceptDate.Value;
}
else if (!GeneralAppRow.IsGenAppRecvgFldAcceptNull())
{
DateAccepted = GeneralAppRow.GenAppRecvgFldAccept.Value;
}
AttendeeRow.Registered = DateAccepted;
// TODO: in Petra 2.x, this was calculated from pm_staff_data
AttendeeRow.HomeOfficeKey = ShortTermAppRow.RegistrationOffice;
if (AttendeeRow.HomeOfficeKey == 0)
{
AttendeeRow.HomeOfficeKey = ((int)AttendeeRow.PartnerKey / 1000000) * 1000000;
}
MainDS.PcAttendee.Rows.Add(AttendeeRow);
}
}
PcRoomAllocTable RoomAllocTable = null;
PcExtraCostTable ExtraCostTable = null;
// now check the other way: all attendees of this conference, are they still valid?
foreach (PcAttendeeRow AttendeeRow in MainDS.PcAttendee.Rows)
{
if ((AttendeeRow.RowState != DataRowState.Added) &&
!IsAttendeeValid(MainDS, OutreachPrefix, AttendeeRow.PartnerKey))
{
// remove their accommodation
RoomAllocTable = PcRoomAllocAccess.LoadViaPcAttendee(AttendeeRow.ConferenceKey, AttendeeRow.PartnerKey, Transaction);
foreach (DataRow Row in RoomAllocTable.Rows)
{
Row.Delete();
}
if (RoomAllocTable != null)
{
PcRoomAllocAccess.SubmitChanges(RoomAllocTable, Transaction);
}
// remove any extra costs
ExtraCostTable = PcExtraCostAccess.LoadViaPcAttendee(AttendeeRow.ConferenceKey, AttendeeRow.PartnerKey, Transaction);
foreach (DataRow Row in ExtraCostTable.Rows)
{
Row.Delete();
}
if (ExtraCostTable != null)
{
PcExtraCostAccess.SubmitChanges(ExtraCostTable, Transaction);
}
// remove attendee
AttendeeRow.Delete();
}
}
int shorttermApplicationsCount = MainDS.PmShortTermApplication.Count;
int attendeeCount = MainDS.PcAttendee.Count;
MainDS.ThrowAwayAfterSubmitChanges = true;
ConferenceApplicationTDSAccess.SubmitChanges(MainDS);
});
}
public static Boolean GetConferenceApplications(ref ConferenceApplicationTDS AMainDS, Int64 AConferenceKey)
{
Boolean NewTransaction;
// make sure outreach codes are up to date in case it has changed in Unit record
TAttendeeManagement.RefreshOutreachCode(AConferenceKey);
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
PcConferenceTable ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, ReadTransaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
string OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref AMainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// obtain PPartner records for all the home offices
foreach (PcAttendeeRow AttendeeRow in AMainDS.PcAttendee.Rows)
{
if (AMainDS.PPartner.Rows.Find(new object[] { AttendeeRow.HomeOfficeKey }) == null)
{
PPartnerAccess.LoadByPrimaryKey(AMainDS, AttendeeRow.HomeOfficeKey, ReadTransaction);
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceDataReaderWebConnector.GetConferenceApplications: commit own transaction.");
}
}
return true;
}
/// <summary>
/// method for importing data entered on the web form
/// </summary>
/// <returns></returns>
public static string DataImportFromForm(string AFormID, string AJSONFormData, bool ASendApplicationReceivedEmail)
{
if (AFormID == "TestPrintingEmail")
{
// This is a test for printing to PDF and sending an email, no partner is created in the database.
// make sure you have a photo with name data\photos\815.jpg for the photo to appear in the pdf
TApplicationFormData data = (TApplicationFormData)TJsonTools.ImportIntoTypedStructure(typeof(TApplicationFormData),
AJSONFormData);
data.RawData = AJSONFormData;
string pdfIdentifier;
string pdfFilename = GeneratePDF(0815, data.registrationcountrycode, data, out pdfIdentifier);
try
{
if (SendEmail(0815, data.registrationcountrycode, data, pdfFilename))
{
// return id of the PDF pdfIdentifier
string result = "{\"success\":true,\"data\":{\"pdfPath\":\"downloadPDF.aspx?pdf-id=" + pdfIdentifier + "\"}}";
return(result);
}
else
{
string message = String.Format(Catalog.GetString("We were not able to send the email to {0}"), data.email);
TLogging.Log("returning: " + "{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
return("{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
}
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
}
if (AFormID == "RegisterPerson")
{
TApplicationFormData data = (TApplicationFormData)TJsonTools.ImportIntoTypedStructure(typeof(TApplicationFormData),
AJSONFormData);
data.RawData = AJSONFormData;
Int64 NewPersonPartnerKey = -1;
string imageTmpPath = String.Empty;
try
{
PartnerEditTDS MainDS = new PartnerEditTDS();
// TODO: check that email is unique. do not allow email to be associated with 2 records. this would cause trouble with authentication
// TODO: create a user for this partner
Int64 NewFamilyPartnerKey = CreateFamily(ref MainDS, data);
NewPersonPartnerKey = CreatePerson(ref MainDS, NewFamilyPartnerKey, data);
CreateAddress(ref MainDS, data, NewFamilyPartnerKey);
try
{
PartnerEditTDSAccess.SubmitChanges(MainDS);
}
catch (Exception Exc)
{
string message = "There is some critical error when saving to the database: " + Exc.ToString();
return("{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
}
// add a record for the application
ConferenceApplicationTDS ConfDS = new ConferenceApplicationTDS();
PmGeneralApplicationRow GeneralApplicationRow = ConfDS.PmGeneralApplication.NewRowTyped();
GeneralApplicationRow.RawApplicationData = AJSONFormData;
GeneralApplicationRow.PartnerKey = NewPersonPartnerKey;
GeneralApplicationRow.ApplicationKey = -1;
GeneralApplicationRow.RegistrationOffice = data.registrationoffice;
GeneralApplicationRow.GenAppDate = DateTime.Today;
GeneralApplicationRow.AppTypeName = MConferenceConstants.APPTYPE_CONFERENCE;
// TODO pm_st_basic_camp_identifier_c is quite strange. will there be an overflow soon?
// see ticket https://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=161
GeneralApplicationRow.OldLink = "";
GeneralApplicationRow.GenApplicantType = "";
GeneralApplicationRow.GenApplicationStatus = MConferenceConstants.APPSTATUS_ONHOLD;
ConfDS.PmGeneralApplication.Rows.Add(GeneralApplicationRow);
PmShortTermApplicationRow ShortTermApplicationRow = ConfDS.PmShortTermApplication.NewRowTyped();
ShortTermApplicationRow.PartnerKey = NewPersonPartnerKey;
ShortTermApplicationRow.ApplicationKey = -1;
ShortTermApplicationRow.RegistrationOffice = data.registrationoffice;
ShortTermApplicationRow.StAppDate = DateTime.Today;
ShortTermApplicationRow.StApplicationType = MConferenceConstants.APPTYPE_CONFERENCE;
ShortTermApplicationRow.StBasicOutreachId = GeneralApplicationRow.OldLink;
ShortTermApplicationRow.StCongressCode = data.role;
ShortTermApplicationRow.ConfirmedOptionCode = data.eventidentifier;
ShortTermApplicationRow.StConfirmedOption = Convert.ToInt64(data.eventpartnerkey);
ShortTermApplicationRow.StFieldCharged = data.registrationoffice;
ShortTermApplicationRow.Arrival = data.dateofarrival;
ShortTermApplicationRow.Departure = data.dateofdeparture;
ConfDS.PmShortTermApplication.Rows.Add(ShortTermApplicationRow);
// TODO ApplicationForms
try
{
ConferenceApplicationTDSAccess.SubmitChanges(ConfDS);
}
catch (Exception Exc)
{
string message = "There is some critical error when saving to the database: " + Exc.ToString();
return("{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
}
// process Photo
imageTmpPath = TAppSettingsManager.GetValue("Server.PathTemp") +
Path.DirectorySeparatorChar +
Path.GetFileName(data.imageid);
if (File.Exists(imageTmpPath))
{
string photosPath = TAppSettingsManager.GetValue("Server.PathData") + Path.DirectorySeparatorChar +
"photos";
if (!Directory.Exists(photosPath))
{
Directory.CreateDirectory(photosPath);
}
File.Copy(imageTmpPath,
photosPath +
Path.DirectorySeparatorChar +
NewPersonPartnerKey +
Path.GetExtension(imageTmpPath).ToLower(), true);
}
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
string message = "There is some critical error when saving to the database";
return("{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
}
if (ASendApplicationReceivedEmail)
{
string pdfIdentifier;
string pdfFilename = GeneratePDF(NewPersonPartnerKey, data.registrationcountrycode, data, out pdfIdentifier);
try
{
if (SendEmail(NewPersonPartnerKey, data.registrationcountrycode, data, pdfFilename))
{
if (File.Exists(imageTmpPath))
{
// only delete the temp image after successful application. otherwise we have a problem with resending the application, because the tmp image is gone
File.Delete(imageTmpPath);
}
// return id of the PDF pdfIdentifier
string result = "{\"success\":true,\"data\":{\"pdfPath\":\"downloadPDF.aspx?pdf-id=" + pdfIdentifier + "\"}}";
return(result);
}
}
catch (Exception e)
{
TLogging.Log(e.Message);
TLogging.Log(e.StackTrace);
}
}
string message2 = String.Format(Catalog.GetString("We were not able to send the email to {0}"), data.email);
string result2 = "{\"failure\":true, \"data\":{\"result\":\"" + message2 + "\"}}";
if (ASendApplicationReceivedEmail)
{
TLogging.Log(result2);
}
return(result2);
}
else
{
string message = "The server does not know about a form called " + AFormID;
TLogging.Log(message);
return("{\"failure\":true, \"data\":{\"result\":\"" + message + "\"}}");
}
}
/// <summary>
/// return a list of all applicants for a given event,
/// but if AConferenceOrganisingOffice is false,
/// consider only the registration office that the user has permissions for, ie. Module REG-00xx0000000
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="AEventPartnerKey">The ConferenceKey</param>
/// <param name="AEventCode">The OutreachPrefix</param>
/// <param name="AApplicationStatus"></param>
/// <param name="ARegistrationOffice">if -1, then show all offices that the user has permission for</param>
/// <param name="AConferenceOrganisingOffice">if true, all offices are considered</param>
/// <param name="ARole"></param>
/// <param name="AClearJSONData"></param>
/// <returns></returns>
public static bool GetApplications(
ref ConferenceApplicationTDS AMainDS,
Int64 AEventPartnerKey,
string AEventCode,
string AApplicationStatus,
Int64 ARegistrationOffice,
bool AConferenceOrganisingOffice,
string ARole,
bool AClearJSONData)
{
Boolean NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
// load all attendees of this conference.
// only load once: GetApplications might be called for several application stati
if (AMainDS.PcAttendee.Rows.Count == 0)
{
PcAttendeeRow templateAttendeeRow = AMainDS.PcAttendee.NewRowTyped(false);
templateAttendeeRow.ConferenceKey = AEventPartnerKey;
PcAttendeeAccess.LoadUsingTemplate(AMainDS, templateAttendeeRow, Transaction);
AMainDS.PcAttendee.DefaultView.Sort = PcAttendeeTable.GetPartnerKeyDBName();
}
if (AConferenceOrganisingOffice && (ARegistrationOffice == -1))
{
// avoid duplicates, who are registered by one office, but charged to another office
GetApplications(ref AMainDS, AEventCode, -1, AApplicationStatus, ARole, AClearJSONData, Transaction);
}
else
{
List <Int64>AllowedRegistrationOffices = GetRegistrationOfficeKeysOfUser(Transaction);
foreach (Int64 RegistrationOffice in AllowedRegistrationOffices)
{
if ((ARegistrationOffice == RegistrationOffice) || (ARegistrationOffice == -1))
{
GetApplications(ref AMainDS, AEventCode, RegistrationOffice, AApplicationStatus, ARole, AClearJSONData, Transaction);
}
}
}
// required for DefaultView.Find
AMainDS.PmShortTermApplication.DefaultView.Sort =
PmShortTermApplicationTable.GetStConfirmedOptionDBName() + "," +
PmShortTermApplicationTable.GetPartnerKeyDBName();
AMainDS.PmGeneralApplication.DefaultView.Sort =
PmGeneralApplicationTable.GetPartnerKeyDBName() + "," +
PmGeneralApplicationTable.GetApplicationKeyDBName() + "," +
PmGeneralApplicationTable.GetRegistrationOfficeDBName();
AMainDS.PDataLabelValuePartner.DefaultView.Sort = PDataLabelValuePartnerTable.GetDataLabelKeyDBName() + "," +
PDataLabelValuePartnerTable.GetPartnerKeyDBName();
AMainDS.PDataLabel.DefaultView.Sort = PDataLabelTable.GetTextDBName();
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
if (AMainDS.HasChanges())
{
AMainDS.EnforceConstraints = false;
AMainDS.AcceptChanges();
}
return true;
}
/// <summary>
/// Load/Refresh all Attendees for a conference
/// </summary>
public static void RefreshAttendees(Int64 AConferenceKey)
{
TDBTransaction Transaction = null;
bool SubmissionOK = true;
PcConferenceTable ConferenceTable;
PUnitTable UnitTable;
string OutreachPrefix = String.Empty;
ConferenceApplicationTDS MainDS;
// make sure outreach codes are up to date in case it has changed in Unit record
RefreshOutreachCode(AConferenceKey);
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ConferenceTable = new PcConferenceTable();
UnitTable = new PUnitTable();
MainDS = new ConferenceApplicationTDS();
// get the conference prefix which links all outreaches associated with a conference
ConferenceTable = PcConferenceAccess.LoadByPrimaryKey(AConferenceKey, Transaction);
if (ConferenceTable.Count == 0)
{
throw new Exception("Cannot find conference " + AConferenceKey.ToString("0000000000"));
}
OutreachPrefix = ConferenceTable[0].OutreachPrefix;
// load application data for all conference attendees from db
TApplicationManagement.GetApplications(ref MainDS, AConferenceKey, OutreachPrefix, "all", -1, true, null, false);
// check a valid pcattendee record exists for each short term application
foreach (PmShortTermApplicationRow ShortTermAppRow in MainDS.PmShortTermApplication.Rows)
{
if (!IsAttendeeValid(MainDS, OutreachPrefix, ShortTermAppRow.PartnerKey))
{
// ignore deleted applications, or cancelled applications
continue;
}
// update outreach code in application (it may have changed)
UnitTable = PUnitAccess.LoadByPrimaryKey(ShortTermAppRow.StConfirmedOption, Transaction);
ShortTermAppRow.ConfirmedOptionCode = ((PUnitRow)UnitTable.Rows[0]).OutreachCode;
// Do we have a record for this attendee yet?
bool AttendeeRecordExists = false;
if (MainDS.PcAttendee.Rows.Contains(new object[] { AConferenceKey, ShortTermAppRow.PartnerKey }))
{
AttendeeRecordExists = true;
}
// create a new PcAttendee record if one does not already exist for this attendee
if (!AttendeeRecordExists)
{
PcAttendeeRow AttendeeRow = MainDS.PcAttendee.NewRowTyped();
AttendeeRow.ConferenceKey = AConferenceKey;
AttendeeRow.PartnerKey = ShortTermAppRow.PartnerKey;
if (ShortTermAppRow.ConfirmedOptionCode.Length >= 11)
{
AttendeeRow.OutreachType = ShortTermAppRow.ConfirmedOptionCode.Substring(5, 6);
}
PmGeneralApplicationRow GeneralAppRow = (PmGeneralApplicationRow)MainDS.PmGeneralApplication.Rows.Find(
new object[] { ShortTermAppRow.PartnerKey, ShortTermAppRow.ApplicationKey, ShortTermAppRow.RegistrationOffice });
DateTime DateAccepted = GeneralAppRow.GenAppDate;
if (!GeneralAppRow.IsGenAppSendFldAcceptDateNull())
{
DateAccepted = GeneralAppRow.GenAppSendFldAcceptDate.Value;
}
else if (!GeneralAppRow.IsGenAppRecvgFldAcceptNull())
{
DateAccepted = GeneralAppRow.GenAppRecvgFldAccept.Value;
}
AttendeeRow.Registered = DateAccepted;
// TODO: in Petra 2.x, this was calculated from pm_staff_data
AttendeeRow.HomeOfficeKey = ShortTermAppRow.RegistrationOffice;
if (AttendeeRow.HomeOfficeKey == 0)
{
AttendeeRow.HomeOfficeKey = ((int)AttendeeRow.PartnerKey / 1000000) * 1000000;
}
MainDS.PcAttendee.Rows.Add(AttendeeRow);
}
}
PcRoomAllocTable RoomAllocTable = null;
PcExtraCostTable ExtraCostTable = null;
// now check the other way: all attendees of this conference, are they still valid?
foreach (PcAttendeeRow AttendeeRow in MainDS.PcAttendee.Rows)
{
if ((AttendeeRow.RowState != DataRowState.Added)
&& !IsAttendeeValid(MainDS, OutreachPrefix, AttendeeRow.PartnerKey))
{
// remove their accommodation
RoomAllocTable = PcRoomAllocAccess.LoadViaPcAttendee(AttendeeRow.ConferenceKey, AttendeeRow.PartnerKey, Transaction);
foreach (DataRow Row in RoomAllocTable.Rows)
{
Row.Delete();
}
if (RoomAllocTable != null)
{
PcRoomAllocAccess.SubmitChanges(RoomAllocTable, Transaction);
}
// remove any extra costs
ExtraCostTable = PcExtraCostAccess.LoadViaPcAttendee(AttendeeRow.ConferenceKey, AttendeeRow.PartnerKey, Transaction);
foreach (DataRow Row in ExtraCostTable.Rows)
{
Row.Delete();
}
if (ExtraCostTable != null)
{
PcExtraCostAccess.SubmitChanges(ExtraCostTable, Transaction);
}
// remove attendee
AttendeeRow.Delete();
}
}
int shorttermApplicationsCount = MainDS.PmShortTermApplication.Count;
int attendeeCount = MainDS.PcAttendee.Count;
MainDS.ThrowAwayAfterSubmitChanges = true;
ConferenceApplicationTDSAccess.SubmitChanges(MainDS);
});
}
