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