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);
}
public static Boolean LoadShortTermApplications(ref ApplicationTDS AMainDS, string AOutreachCode)
{
string QueryShortTermApplication = "";
ApplicationTDS MainDS = new ApplicationTDS();
String ShortTermAppTableName = AMainDS.PmShortTermApplication.TableName;
List <OdbcParameter> Parameters = new List <OdbcParameter>();
OdbcParameter Parameter = new OdbcParameter("eventcode", OdbcType.VarChar, PmShortTermApplicationTable.GetConfirmedOptionCodeLength());
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
QueryShortTermApplication =
"SELECT PUB_pm_short_term_application.*, PUB_pm_general_application.*, PUB_p_partner.p_partner_short_name_c " +
"FROM PUB_pm_short_term_application, PUB_pm_general_application, PUB_p_partner ";
if (AOutreachCode.Length == 0)
{
// load all appicants with no event
QueryShortTermApplication += "WHERE PUB_pm_short_term_application.pm_confirmed_option_code_c IS NULL ";
}
else if (AOutreachCode.Length > 0)
{
Parameter.Value = AOutreachCode.Substring(0, 5);
Parameters.Add(
Parameter);
QueryShortTermApplication += "WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? ";
}
QueryShortTermApplication += "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 "
+
"AND PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n";
DBAccess.GDBAccessObj.Select(MainDS,
QueryShortTermApplication,
ShortTermAppTableName, Transaction, Parameters.ToArray());
});
AMainDS.Merge(MainDS);
return(true);
}
public static Boolean LoadApplicationsForConverting(ref ApplicationTDS AMainDS, bool AConvertTo,
string AOutreachCode, bool AAllOutreaches, bool AAllEvents, string AYear)
{
string Query = string.Empty;
ApplicationTDS MainDS = new ApplicationTDS();
String ShortTermAppTableName = AMainDS.PmShortTermApplication.TableName;
List <OdbcParameter> Parameters = new List <OdbcParameter>();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
Query =
"SELECT DISTINCT PUB_pm_short_term_application.*, PUB_pm_general_application.*, PUB_p_partner.p_partner_short_name_c "
+
"FROM PUB_pm_short_term_application, PUB_pm_general_application, PUB_p_partner, p_unit " +
"WHERE ";
// if we are looking for a specific event
if (!AAllEvents)
{
if (AOutreachCode.Length == 0)
{
// load all appicants with no event
Query += "PUB_pm_short_term_application.pm_confirmed_option_code_c IS NULL " +
"AND ";
}
else if (AOutreachCode.Length > 0)
{
// get all outreaches relating to an event
if (AAllOutreaches)
{
OdbcParameter Parameter = new OdbcParameter("eventcode", OdbcType.VarChar, 5);
Parameter.Value = AOutreachCode.Substring(0, 5);
Parameters.Add(Parameter);
Query += "SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? " +
"AND ";
}
else
{
OdbcParameter Parameter =
new OdbcParameter("eventcode", OdbcType.VarChar, PmShortTermApplicationTable.GetConfirmedOptionCodeLength());
Parameter.Value = AOutreachCode;
Parameters.Add(Parameter);
Query += "PUB_pm_short_term_application.pm_confirmed_option_code_c = ? " +
"AND ";
}
}
}
// if year criteria has been supplied
if (!string.IsNullOrEmpty(AYear))
{
OdbcParameter Parameter = new OdbcParameter("year", OdbcType.VarChar, 2);
Parameter.Value = AYear.ToString().Substring(2, 2);
Parameters.Add(Parameter);
Query += "SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 3, 2) = ? " +
"AND ";
}
Query += "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 " +
"AND PUB_pm_general_application.pm_gen_application_status_c = 'A' " +
"AND PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n " +
"AND p_unit.p_outreach_code_c = PUB_pm_short_term_application.pm_confirmed_option_code_c " +
"AND ";
if (AConvertTo)
{
// converting to past experience so need to make sure past experience does not already exist
Query += "NOT ";
}
Query += "EXISTS (SELECT * FROM pm_past_experience " +
"WHERE pm_past_experience.p_partner_key_n = pm_short_term_application.p_partner_key_n " +
"AND pm_past_experience.pm_prev_location_c = PUB_pm_short_term_application.pm_confirmed_option_code_c)";
DBAccess.GDBAccessObj.Select(MainDS, Query, ShortTermAppTableName, Transaction, Parameters.ToArray());
});
AMainDS.Merge(MainDS);
return(true);
}
public static Boolean LoadShortTermApplications(ref ApplicationTDS AMainDS, string AOutreachCode)
{
Boolean NewTransaction;
string QueryShortTermApplication = "";
List <OdbcParameter> Parameters = new List <OdbcParameter>();
OdbcParameter Parameter = new OdbcParameter("eventcode", OdbcType.VarChar, PmShortTermApplicationTable.GetConfirmedOptionCodeLength());
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
QueryShortTermApplication =
"SELECT PUB_pm_short_term_application.*, PUB_pm_general_application.*, PUB_p_partner.p_partner_short_name_c " +
"FROM PUB_pm_short_term_application, PUB_pm_general_application, PUB_p_partner ";
if (AOutreachCode.Length == 0)
{
// load all appicants with no event
QueryShortTermApplication += "WHERE PUB_pm_short_term_application.pm_confirmed_option_code_c IS NULL ";
}
else if (AOutreachCode.Length > 0)
{
Parameter.Value = AOutreachCode.Substring(0, 5);
Parameters.Add(Parameter);
QueryShortTermApplication += "WHERE SUBSTRING(PUB_pm_short_term_application.pm_confirmed_option_code_c, 1, 5) = ? ";
}
QueryShortTermApplication += "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 "
+
"AND PUB_p_partner.p_partner_key_n = PUB_pm_short_term_application.p_partner_key_n";
DBAccess.GDBAccessObj.Select(AMainDS,
QueryShortTermApplication,
AMainDS.PmShortTermApplication.TableName, ReadTransaction, Parameters.ToArray());
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TConferenceDataReaderWebConnector.LoadShortTermApplications: commit own transaction.");
}
}
return(true);
}
