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