public static DataTable GetDataTable_ReferrersOf(int organistion_id, bool showDeletedRefs = false, bool showDeletedRegRefs = false, string matchSurname = "", bool searchSurnameOnlyStartsWith = false) { matchSurname = matchSurname.Replace("'", "''"); string sql = @"SELECT rr.register_referrer_id, rr.organisation_id, rr.referrer_id, rr.provider_number, rr.report_every_visit_to_referrer,rr.batch_send_all_patients_treatment_notes,rr.date_last_batch_send_all_patients_treatment_notes,rr.register_referrer_date_added, r.person_id, r.referrer_date_added, r.is_deleted as referrer_is_deleted, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr, r.is_deleted as referrer_is_deleted, rr.is_deleted as register_referrer_is_deleted FROM RegisterReferrer AS rr LEFT OUTER JOIN Referrer AS r ON rr.referrer_id = r.referrer_id LEFT OUTER JOIN Person AS p ON r.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id WHERE " + (showDeletedRegRefs ? "" : "rr.is_deleted = 0 AND ") + (showDeletedRefs ? "" : "r.is_deleted = 0 AND ") + "rr.organisation_id = " + organistion_id.ToString() + ((matchSurname.Length > 0 && !searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '%" + matchSurname + "%'" : "") + ((matchSurname.Length > 0 && searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '" + matchSurname + "%'" : "") + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_AllNotInc(Referrer[] excList) { string notInList = string.Empty; foreach (Referrer r in excList) { notInList += r.ReferrerID.ToString() + ","; } if (notInList.Length > 0) { notInList = notInList.Substring(0, notInList.Length - 1); } string sql = @"SELECT r.referrer_id, r.person_id, r.referrer_date_added, " + PersonDB.GetFields("", "p") + @", t.title_id, t.descr FROM Referrer AS r LEFT OUTER JOIN Person AS p ON r.person_id = p.person_id INNER JOIN Title t ON t.title_id = p.title_id WHERE r.is_deleted = 0 " + ((notInList.Length > 0) ? " AND r.referrer_id NOT IN (" + notInList + @") " : "") + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_StaffOf(int organistion_id, bool inc_deleted = false, bool only_providers = false, bool excl_external = true) { string sql = @"SELECT r.register_staff_id,r.register_staff_date_added,r.organisation_id,r.provider_number AS registration_provider_number,r.main_provider_for_clinic, r.excl_sun, r.excl_mon, r.excl_tue, r.excl_wed, r.excl_thu, r.excl_fri, r.excl_sat, r.is_deleted as registration_is_deleted, s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number, s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external, s.staff_date_added, s.start_date, s.end_date, s.comment, s.num_days_to_display_on_booking_screen, s.show_header_on_booking_screen, s.bk_screen_field_id, s.bk_screen_show_key, s.enable_daily_reminder_sms, s.enable_daily_reminder_email, s.hide_booking_notes, sr.field_id as field_field_id,sr.descr as field_descr, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr FROM RegisterStaff AS r LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id LEFT OUTER JOIN Person p ON s.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id LEFT OUTER JOIN Field sr ON s.field_id = sr.field_id WHERE s.staff_id > 0 AND s.is_fired = 0 " + (inc_deleted ? "" : " AND r.is_deleted = 0 ") + (!only_providers ? "" : " AND s.is_provider = 1 ") + (!excl_external ? "" : " AND s.is_external = 0 ") + " AND r.organisation_id = " + organistion_id.ToString() + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_AllNotInc(Staff[] excList) { string notInList = string.Empty; foreach (Staff s in excList) { notInList += s.StaffID.ToString() + ","; } if (notInList.Length > 0) { notInList = notInList.Substring(0, notInList.Length - 1); } string sql = @"SELECT s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number, s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external, s.staff_date_added, s.start_date, s.end_date, s.comment, " + PersonDB.GetFields("", "p") + @", t.title_id, t.descr FROM Staff AS s LEFT OUTER JOIN Person p ON s.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id WHERE s.staff_id > 0 AND s.is_fired = 0 AND s.is_external = 0 " + ((notInList.Length > 0) ? " AND staff_id NOT IN (" + notInList + @") " : "") + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_WorkingStaffOf(int organistion_id, DateTime date) { string sql = @"SELECT Distinct --r.register_staff_id,r.register_staff_date_added,r.organisation_id,r.provider_number AS registration_provider_number, --r.excl_sun, r.excl_mon, r.excl_tue, r.excl_wed, r.excl_thu, r.excl_fri, r.excl_sat, r.main_provider_for_clinic, s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number, s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external, s.staff_date_added, s.start_date, s.end_date, s.comment, s.num_days_to_display_on_booking_screen, s.show_header_on_booking_screen, s.bk_screen_field_id, s.bk_screen_show_key, s.enable_daily_reminder_sms, s.enable_daily_reminder_email, s.hide_booking_notes, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr FROM RegisterStaff AS r LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id LEFT OUTER JOIN Person p ON s.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id WHERE s.staff_id > 0 AND s.is_fired = 0 AND r.is_deleted = 0 AND r.organisation_id = " + organistion_id + @" -- get unavail bookings for whole day .... and make sure there are none AND (SELECT COUNT(*) FROM Booking WHERE ( (booking_type_id = 341 AND (organisation_id IS NULL OR organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') )) OR (booking_type_id = 342 AND (organisation_id = r.organisation_id) AND ( (is_recurring = 0 AND CONVERT(TIME,Booking.date_start) = '00:00' AND CONVERT(TIME,Booking.date_end) >= '23:59') OR (is_recurring = 1 AND recurring_start_time = '00:00' AND recurring_end_time >= '23:59') )) ) AND (date_deleted IS NULL) AND ( (is_recurring = 0 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) = '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"') OR (is_recurring = 1 AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_start))) <= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"' AND (SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, Booking.date_end))) >= '" + date.ToString("yyyy-MM-dd") + " 00:00:00" + @"' AND (Booking.recurring_weekday_id = " + WeekDayDB.GetWeekDayID(date.DayOfWeek) + @")) ) AND (provider = r.staff_id) ) = 0 AND (SELECT COUNT(*) FROM Staff WHERE (staff_id = r.staff_id) AND (excl_" + date.DayOfWeek.ToString().Substring(0, 3).ToLower() + @" = 0)) > 0 ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
private static string JoinedSQL() { string sql = @" SELECT pr.patient_referrer_id as pr_patient_referrer_id, pr.patient_id as pr_patient_id, pr.register_referrer_id as pr_register_referrer_id, pr.organisation_id as pr_organisation_id, pr.patient_referrer_date_added as pr_patient_referrer_date_added, pr.is_debtor as pr_is_debtor, pr.is_active as pr_is_active, pat.patient_id as patient_patient_id,pat.person_id as patient_person_id, pat.patient_date_added as patient_patient_date_added, pat.is_clinic_patient as patient_is_clinic_patient, pat.is_gp_patient as patient_is_gp_patient, pat.is_deleted as patient_is_deleted, pat.is_deceased as patient_is_deceased, pat.flashing_text as patient_flashing_text, pat.flashing_text_added_by as patient_flashing_text_added_by, pat.flashing_text_last_modified_date as patient_flashing_text_last_modified_date, pat.private_health_fund as patient_private_health_fund, pat.concession_card_number as patient_concession_card_number, pat.concession_card_expiry_date as patient_concession_card_expiry_date, pat.is_diabetic as patient_is_diabetic, pat.is_member_diabetes_australia as patient_is_member_diabetes_australia, pat.diabetic_assessment_review_date as patient_diabetic_assessment_review_date, pat.ac_inv_offering_id as patient_ac_inv_offering_id, pat.ac_pat_offering_id as patient_ac_pat_offering_id, pat.login as patient_login, pat.pwd as patient_pwd, pat.is_company as patient_is_company, pat.abn as patient_abn, pat.next_of_kin_name as patient_next_of_kin_name, pat.next_of_kin_relation as patient_next_of_kin_relation, pat.next_of_kin_contact_info as patient_next_of_kin_contact_info, regref.register_referrer_id as regref_register_referrer_id,regref.organisation_id as regref_organisation_id, regref.referrer_id as regref_referrer_id, regref.provider_number as regref_provider_number, regref.report_every_visit_to_referrer as regref_report_every_visit_to_referrer, regref.batch_send_all_patients_treatment_notes as regref_batch_send_all_patients_treatment_notes, regref.date_last_batch_send_all_patients_treatment_notes as regref_date_last_batch_send_all_patients_treatment_notes, regref.register_referrer_date_added as regref_register_referrer_date_added, ref.referrer_id as referrer_referrer_id,ref.person_id as referrer_person_id, ref.referrer_date_added as referrer_referrer_date_added, org.entity_id as organisation_entity_id, org.parent_organisation_id as organisation_parent_organisation_id, org.use_parent_offernig_prices as organisation_use_parent_offernig_prices, org.organisation_type_id as organisation_organisation_type_id, org.organisation_customer_type_id as organisation_organisation_customer_type_id,org.name as organisation_name, org.acn as organisation_acn, org.abn as organisation_abn, org.organisation_date_added as organisation_organisation_date_added, org.organisation_id as organisation_organisation_id, org.organisation_date_modified as organisation_organisation_date_modified, org.is_debtor as organisation_is_debtor, org.is_creditor as organisation_is_creditor, org.bpay_account as organisation_bpay_account, org.weeks_per_service_cycle as organisation_weeks_per_service_cycle, org.start_date as organisation_start_date, org.end_date as organisation_end_date, org.comment as organisation_comment, org.free_services as organisation_free_services, org.excl_sun as organisation_excl_sun, org.excl_mon as organisation_excl_mon, org.excl_tue as organisation_excl_tue, org.excl_wed as organisation_excl_wed, org.excl_thu as organisation_excl_thu, org.excl_fri as organisation_excl_fri, org.excl_sat as organisation_excl_sat, org.sun_start_time as organisation_sun_start_time, org.sun_end_time as organisation_sun_end_time, org.mon_start_time as organisation_mon_start_time, org.mon_end_time as organisation_mon_end_time, org.tue_start_time as organisation_tue_start_time, org.tue_end_time as organisation_tue_end_time, org.wed_start_time as organisation_wed_start_time, org.wed_end_time as organisation_wed_end_time, org.thu_start_time as organisation_thu_start_time, org.thu_end_time as organisation_thu_end_time, org.fri_start_time as organisation_fri_start_time, org.fri_end_time as organisation_fri_end_time, org.sat_start_time as organisation_sat_start_time, org.sat_end_time as organisation_sat_end_time, org.sun_lunch_start_time as organisation_sun_lunch_start_time, org.sun_lunch_end_time as organisation_sun_lunch_end_time, org.mon_lunch_start_time as organisation_mon_lunch_start_time, org.mon_lunch_end_time as organisation_mon_lunch_end_time, org.tue_lunch_start_time as organisation_tue_lunch_start_time, org.tue_lunch_end_time as organisation_tue_lunch_end_time, org.wed_lunch_start_time as organisation_wed_lunch_start_time, org.wed_lunch_end_time as organisation_wed_lunch_end_time, org.thu_lunch_start_time as organisation_thu_lunch_start_time, org.thu_lunch_end_time as organisation_thu_lunch_end_time, org.fri_lunch_start_time as organisation_fri_lunch_start_time, org.fri_lunch_end_time as organisation_fri_lunch_end_time, org.sat_lunch_start_time as organisation_sat_lunch_start_time, org.sat_lunch_end_time as organisation_sat_lunch_end_time, org.last_batch_run as organisation_last_batch_run, org.is_deleted as organisation_is_deleted, " + PersonDB.GetFields("patient_person_", "patient_person") + @", patient_person_title.title_id as patient_person_title_title_id, patient_person_title.descr as patient_person_title_descr, " + PersonDB.GetFields("referrer_person_", "referrer_person") + @", referrer_person_title.title_id as referrer_person_title_title_id, referrer_person_title.descr as referrer_person_title_descr, nonepcorg.entity_id as nonepcorg_entity_id, nonepcorg.parent_organisation_id as nonepcorg_parent_organisation_id, nonepcorg.use_parent_offernig_prices as nonepcorg_use_parent_offernig_prices, nonepcorg.organisation_type_id as nonepcorg_organisation_type_id, nonepcorg.organisation_customer_type_id as nonepcorg_organisation_customer_type_id,nonepcorg.name as nonepcorg_name, nonepcorg.acn as nonepcorg_acn, nonepcorg.abn as nonepcorg_abn, nonepcorg.organisation_date_added as nonepcorg_organisation_date_added, nonepcorg.organisation_id as nonepcorg_organisation_id, nonepcorg.organisation_date_modified as nonepcorg_organisation_date_modified, nonepcorg.is_debtor as nonepcorg_is_debtor, nonepcorg.is_creditor as nonepcorg_is_creditor, nonepcorg.bpay_account as nonepcorg_bpay_account, nonepcorg.weeks_per_service_cycle as nonepcorg_weeks_per_service_cycle, nonepcorg.start_date as nonepcorg_start_date, nonepcorg.end_date as nonepcorg_end_date, nonepcorg.comment as nonepcorg_comment, nonepcorg.free_services as nonepcorg_free_services, nonepcorg.excl_sun as nonepcorg_excl_sun, nonepcorg.excl_mon as nonepcorg_excl_mon, nonepcorg.excl_tue as nonepcorg_excl_tue, nonepcorg.excl_wed as nonepcorg_excl_wed, nonepcorg.excl_thu as nonepcorg_excl_thu, nonepcorg.excl_fri as nonepcorg_excl_fri, nonepcorg.excl_sat as nonepcorg_excl_sat, nonepcorg.sun_start_time as nonepcorg_sun_start_time, nonepcorg.sun_end_time as nonepcorg_sun_end_time, nonepcorg.mon_start_time as nonepcorg_mon_start_time, nonepcorg.mon_end_time as nonepcorg_mon_end_time, nonepcorg.tue_start_time as nonepcorg_tue_start_time, nonepcorg.tue_end_time as nonepcorg_tue_end_time, nonepcorg.wed_start_time as nonepcorg_wed_start_time, nonepcorg.wed_end_time as nonepcorg_wed_end_time, nonepcorg.thu_start_time as nonepcorg_thu_start_time, nonepcorg.thu_end_time as nonepcorg_thu_end_time, nonepcorg.fri_start_time as nonepcorg_fri_start_time, nonepcorg.fri_end_time as nonepcorg_fri_end_time, nonepcorg.sat_start_time as nonepcorg_sat_start_time, nonepcorg.sat_end_time as nonepcorg_sat_end_time, nonepcorg.sun_lunch_start_time as nonepcorg_sun_lunch_start_time, nonepcorg.sun_lunch_end_time as nonepcorg_sun_lunch_end_time, nonepcorg.mon_lunch_start_time as nonepcorg_mon_lunch_start_time, nonepcorg.mon_lunch_end_time as nonepcorg_mon_lunch_end_time, nonepcorg.tue_lunch_start_time as nonepcorg_tue_lunch_start_time, nonepcorg.tue_lunch_end_time as nonepcorg_tue_lunch_end_time, nonepcorg.wed_lunch_start_time as nonepcorg_wed_lunch_start_time, nonepcorg.wed_lunch_end_time as nonepcorg_wed_lunch_end_time, nonepcorg.thu_lunch_start_time as nonepcorg_thu_lunch_start_time, nonepcorg.thu_lunch_end_time as nonepcorg_thu_lunch_end_time, nonepcorg.fri_lunch_start_time as nonepcorg_fri_lunch_start_time, nonepcorg.fri_lunch_end_time as nonepcorg_fri_lunch_end_time, nonepcorg.sat_lunch_start_time as nonepcorg_sat_lunch_start_time, nonepcorg.sat_lunch_end_time as nonepcorg_sat_lunch_end_time, nonepcorg.last_batch_run as nonepcorg_last_batch_run, nonepcorg.is_deleted as nonepcorg_is_deleted FROM PatientReferrer AS pr INNER JOIN Patient AS pat ON pat.patient_id = pr.patient_id LEFT OUTER JOIN RegisterReferrer AS regref ON regref.register_referrer_id = pr.register_referrer_id LEFT OUTER JOIN Referrer AS ref ON ref.referrer_id = regref.referrer_id LEFT OUTER JOIN Organisation AS org ON org.organisation_id = regref.organisation_id INNER JOIN Person AS patient_person ON patient_person.person_id = pat.person_id INNER JOIN Title AS patient_person_title ON patient_person_title.title_id = patient_person.title_id INNER JOIN Person AS referrer_person ON referrer_person.person_id = ref.person_id INNER JOIN Title AS referrer_person_title ON referrer_person_title.title_id = referrer_person.title_id LEFT OUTER JOIN Organisation AS nonepcorg ON nonepcorg.organisation_id = pr.organisation_id WHERE 1 = 1 "; // is_active = 1"; return(sql); }
public static HealthCardEPCRemainingChangeHistory[] GetByHealthCardID(int health_card_id) { string sql = @" SELECT health_card_epc_remaining_change_history_id,HealthCardEPCRemainingChangeHistory.health_card_epc_remaining_id,HealthCardEPCRemainingChangeHistory.staff_id,date, pre_num_services_remaining,post_num_services_remaining, health_card_id,HealthCardEPCRemaining.field_id,num_services_remaining,deleted_by,date_deleted, Field.descr, staff.staff_id as staff_staff_id, staff.person_id as staff_person_id, staff.login as staff_login, staff.pwd as staff_pwd, staff.staff_position_id as staff_staff_position_id, staff.field_id as staff_field_id, staff.costcentre_id as staff_costcentre_id, staff.is_contractor as staff_is_contractor, staff.tfn as staff_tfn, staff.provider_number as staff_provider_number, staff.is_fired as staff_is_fired, staff.is_commission as staff_is_commission, staff.commission_percent as staff_commission_percent, staff.is_stakeholder as staff_is_stakeholder,staff.is_master_admin as staff_is_master_admin,staff.is_admin as staff_is_admin,staff.is_principal as staff_is_principal,staff.is_provider as staff_is_provider, staff.is_external as staff_is_external, staff.staff_date_added as staff_staff_date_added, staff.start_date as staff_start_date, staff.end_date as staff_end_date, staff.comment as staff_comment, staff.num_days_to_display_on_booking_screen as staff_num_days_to_display_on_booking_screen, staff.show_header_on_booking_screen as staff_show_header_on_booking_screen, staff.bk_screen_field_id as staff_bk_screen_field_id, staff.bk_screen_show_key as staff_bk_screen_show_key, staff.enable_daily_reminder_sms as staff_enable_daily_reminder_sms, staff.enable_daily_reminder_email as staff_enable_daily_reminder_email, staff.hide_booking_notes as staff_hide_booking_notes, " + PersonDB.GetFields("person_", "person") + @", title.title_id as title_title_id, title.descr as title_descr FROM HealthCardEPCRemainingChangeHistory LEFT OUTER JOIN HealthCardEPCRemaining ON HealthCardEPCRemainingChangeHistory.health_card_epc_remaining_id = HealthCardEPCRemaining.health_card_epc_remaining_id LEFT OUTER JOIN Field ON HealthCardEPCRemaining.field_id = Field.field_id LEFT OUTER JOIN Staff staff ON staff.staff_id = HealthCardEPCRemainingChangeHistory.staff_id LEFT OUTER JOIN Person person ON person.person_id = staff.person_id LEFT OUTER JOIN Title title ON title.title_id = person.title_id WHERE HealthCardEPCRemaining.health_card_id = " + health_card_id; DataTable tbl = DBBase.ExecuteQuery(sql).Tables[0]; HealthCardEPCRemainingChangeHistory[] histories = new HealthCardEPCRemainingChangeHistory[tbl.Rows.Count]; for (int i = 0; i < tbl.Rows.Count; i++) { histories[i] = Load(tbl.Rows[i]); histories[i].HealthCardEpcRemaining = HealthCardEPCRemainingDB.Load(tbl.Rows[i]); histories[i].HealthCardEpcRemaining.Field = IDandDescrDB.Load(tbl.Rows[i], "field_id", "descr"); histories[i].Staff = StaffDB.Load(tbl.Rows[i], "staff_"); histories[i].Staff.Person = PersonDB.Load(tbl.Rows[i], "person_"); histories[i].Staff.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "title_title_id", "title_descr"); } return(histories); }
public static DataTable GetDataTable_StaffInfo(bool incSupportStaff, bool showIsFired = false, bool showIsExternal = false, bool showOnlyExternal = false, string matchSurname = "", bool searchSurnameOnlyStartsWith = false) { matchSurname = matchSurname.Replace("'", "''"); string sql = @"SELECT s.staff_id as staff_id,login,pwd,pos.staff_position_id as staff_position_id,pos.descr as staff_position_descr,s.field_id as field_id,r.descr AS field_descr,c.costcentre_id as costcentre_id,c.descr AS costcentre_descr,is_contractor,tfn,provider_number,is_fired,is_commission,commission_percent, is_stakeholder,is_master_admin,is_admin,is_principal,is_provider,is_external, s.staff_date_added,start_date,end_date,comment,num_days_to_display_on_booking_screen, show_header_on_booking_screen, bk_screen_field_id, bk_screen_show_key, enable_daily_reminder_sms, enable_daily_reminder_email, hide_booking_notes, " + PersonDB.GetFields("", "p") + @", p2.firstname AS added_by_firstname, t.title_id, t.descr, c.costcentre_id as cost_centre_costcentre_id,c.descr as cost_centre_descr,c.parent_id as cost_centre_parent_id, r.field_id as field_field_id,r.descr as field_descr, pos.staff_position_id as staff_position_staff_position_id,pos.descr as staff_position_descr FROM Staff s LEFT OUTER JOIN Person p ON s.person_id = p.person_id LEFT OUTER JOIN Person p2 ON p2.person_id = p.added_by LEFT OUTER JOIN Title t ON t.title_id = p.title_id INNER JOIN Field r ON s.field_id = r.field_id INNER JOIN StaffPosition pos ON pos.staff_position_id = s.staff_position_id INNER JOIN CostCentre AS c ON c.costcentre_id = s.costcentre_id "; string whereClause = string.Empty; if (!incSupportStaff) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " s.staff_id > 0 "; } if (!showIsFired) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " s.is_fired = 0 "; } if (matchSurname.Length > 0 && !searchSurnameOnlyStartsWith) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " p.surname LIKE '%" + matchSurname + "%'"; } if (matchSurname.Length > 0 && searchSurnameOnlyStartsWith) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " p.surname LIKE '" + matchSurname + "%'"; } if (!showIsExternal) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " s.is_external = 0"; } if (showOnlyExternal) { whereClause += (whereClause.Length == 0 ? " WHERE " : " AND ") + " s.is_external = 1"; } sql += whereClause + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static Referrer GetByID(int referrer_id) { string sql = @"SELECT r.referrer_id,r.person_id,r.referrer_date_added, " + PersonDB.GetFields("", "p") + @", t.title_id, t.descr FROM Referrer AS r INNER JOIN Person p ON r.person_id = p.person_id INNER JOIN Title t ON t.title_id = p.title_id WHERE r.is_deleted = 0 AND referrer_id = " + referrer_id.ToString() + @" ORDER BY p.surname, p.firstname, p.middlename"; DataTable tbl = DBBase.ExecuteQuery(sql).Tables[0]; return((tbl.Rows.Count == 0) ? null : LoadFull(tbl)[0]); }
private static DataTable _GetDataTable_PatientsOf(int register_referrer_id, bool onlyActive, bool show_deleted = false, bool show_deceased = false, bool show_only_is_clinic_patient = false, bool show_only_is_gp_patient = false, string matchSurname = "", bool searchSurnameOnlyStartsWith = false, string matchSuburbs = "", string matchStreets = "", string searchPhoneNbr = "", string matchMedicareCardNo = "", bool searchMedicareCardNoOnlyStartsWith = false, int matchDOBDay = -1, int matchDOBMonth = -1, int matchDOBYear = -1, string matchReferrers = "") { matchSurname = matchSurname.Replace("'", "''"); matchSuburbs = matchSuburbs.Replace("'", "''"); matchStreets = matchStreets.Replace("'", "''"); searchPhoneNbr = searchPhoneNbr.Replace("'", "''"); matchMedicareCardNo = matchMedicareCardNo.Replace("'", "''"); matchReferrers = matchReferrers.Replace("'", "''"); string sql = @"SELECT DISTINCT pa.patient_id,pa.person_id, pa.patient_date_added, pa.is_clinic_patient, pa.is_gp_patient, pa.is_deleted, pa.is_deceased,pa.is_deleted, pa.flashing_text, pa.flashing_text_added_by, pa.flashing_text_last_modified_date, pa.private_health_fund, pa.concession_card_number, pa.concession_card_expiry_date, pa.is_diabetic, pa.is_member_diabetes_australia, pa.diabetic_assessment_review_date, pa.ac_inv_offering_id, pa.ac_pat_offering_id, pa.login, pa.pwd, pa.is_company, pa.abn, pa.next_of_kin_name, pa.next_of_kin_relation, pa.next_of_kin_contact_info, " + PersonDB.GetFields("", "p") + @", t.title_id, t.descr FROM PatientReferrer AS pr LEFT OUTER JOIN Patient pa ON pr.patient_id = pa.patient_id LEFT OUTER JOIN Person p ON pa.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id WHERE pr.register_referrer_id = " + register_referrer_id.ToString() + (onlyActive ? " AND pr.is_active = 1 " : "") + @" " + (matchSurname.Length > 0 && !searchSurnameOnlyStartsWith ? " AND p.surname LIKE '%" + matchSurname + "%'" : "") + @" " + (matchSurname.Length > 0 && searchSurnameOnlyStartsWith ? " AND p.surname LIKE '" + matchSurname + "%'" : "") + @" " + (Utilities.GetAddressType().ToString() != "Contact" || matchSuburbs == "" ? "" : " AND (SELECT COUNT(*) FROM Contact AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND ad.suburb_id IN (" + matchSuburbs + ")) > 0 ") + @" " + (Utilities.GetAddressType().ToString() != "Contact" || matchStreets == "" ? "" : " AND (SELECT COUNT(*) FROM Contact AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND ad.address_channel_id IN (" + matchStreets + ")) > 0 ") + @" " + (Utilities.GetAddressType().ToString() != "Contact" || searchPhoneNbr == "" ? "" : " AND (SELECT COUNT(*) FROM Contact AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND dbo.ufnFilterNonDigit(addr_line1) LIKE '" + searchPhoneNbr + "%'" + ") > 0 ") + @" " + (Utilities.GetAddressType().ToString() != "ContactAus" || matchSuburbs == "" ? "" : " AND (SELECT COUNT(*) FROM ContactAus AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND ad.suburb_id IN (" + matchSuburbs + ")) > 0 ") + @" " + (Utilities.GetAddressType().ToString() != "ContactAus" || matchStreets == "" ? "" : " AND (SELECT COUNT(*) FROM ContactAus AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND (ad.street_name = '" + matchStreets + "' OR SOUNDEX(ad.street_name) = SOUNDEX('" + matchStreets + "'))) > 0 ") + @" " + (Utilities.GetAddressType().ToString() != "ContactAus" || searchPhoneNbr == "" ? "" : " AND (SELECT COUNT(*) FROM ContactAus AS ad WHERE entity_id=p.entity_id AND ad.contact_date_deleted IS NULL AND dbo.ufnFilterNonDigit(addr_line1) LIKE '" + searchPhoneNbr + "%'" + ") > 0 ") + @" " + (matchMedicareCardNo.Length > 0 && !searchMedicareCardNoOnlyStartsWith ? " AND (SELECT COUNT(*) FROM HealthCard hc WHERE hc.patient_id=pa.patient_id AND is_active = 1 AND [dbo].[ufnFilterNonAlphaNumeric]( hc.card_nbr) LIKE '%" + matchMedicareCardNo + "%') > 0 " : "") + @" " + (matchMedicareCardNo.Length > 0 && searchMedicareCardNoOnlyStartsWith ? " AND (SELECT COUNT(*) FROM HealthCard hc WHERE hc.patient_id=pa.patient_id AND is_active = 1 AND [dbo].[ufnFilterNonAlphaNumeric]( hc.card_nbr) LIKE '" + matchMedicareCardNo + "%') > 0 " : "") + @" " + (matchDOBDay == -1 ? "" : " AND datepart(day,p.dob) = " + matchDOBDay) + @" " + (matchDOBMonth == -1 ? "" : " AND datepart(month,p.dob) = " + matchDOBMonth) + @" " + (matchDOBYear == -1 ? "" : " AND datepart(year,p.dob) = " + matchDOBYear) + @" " + (show_deleted ? "" : " AND pa.is_deleted = 0 ") + @" " + (show_deceased ? "" : " AND pa.is_deceased = 0 ") + @" " + (!show_only_is_clinic_patient ? "" : " AND pa.is_clinic_patient = 1 ") + @" " + (!show_only_is_gp_patient ? "" : " AND pa.is_gp_patient = 1 ") + @" " + (matchReferrers == "" ? "" : " AND (SELECT COUNT(*) FROM PatientReferrer WHERE PatientReferrer.is_active = 1 AND patient_id=pa.patient_id AND register_referrer_id IN (" + matchReferrers + ")) > 0 ") + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_AllActiveRegRefByPatientsOfInternalOrg(int organisation_id) { string sql = @" SELECT DISTINCT rr.register_referrer_id, rr.organisation_id, rr.referrer_id, rr.provider_number, rr.report_every_visit_to_referrer, rr.batch_send_all_patients_treatment_notes, rr.date_last_batch_send_all_patients_treatment_notes, rr.register_referrer_date_added, rr.is_deleted, o.entity_id as organisation_entity_id, o.parent_organisation_id, o.use_parent_offernig_prices, o.organisation_type_id, o.organisation_customer_type_id, o.name, o.acn, o.abn, o.organisation_date_added, o.organisation_date_modified, o.is_debtor, o.is_creditor, o.bpay_account, o.weeks_per_service_cycle, o.start_date, o.end_date, o.comment, o.free_services, o.excl_sun, o.excl_mon, o.excl_tue, o.excl_wed, o.excl_thu, o.excl_fri, o.excl_sat, o.sun_start_time, o.sun_end_time, o.mon_start_time, o.mon_end_time, o.tue_start_time, o.tue_end_time, o.wed_start_time, o.wed_end_time, o.thu_start_time, o.thu_end_time, o.fri_start_time, o.fri_end_time, o.sat_start_time, o.sat_end_time, o.sun_lunch_start_time, o.sun_lunch_end_time, o.mon_lunch_start_time, o.mon_lunch_end_time, o.tue_lunch_start_time, o.tue_lunch_end_time, o.wed_lunch_start_time, o.wed_lunch_end_time, o.thu_lunch_start_time, o.thu_lunch_end_time, o.fri_lunch_start_time, o.fri_lunch_end_time, o.sat_lunch_start_time, o.sat_lunch_end_time, o.last_batch_run, o.is_deleted as organisation_is_deleted, ref.person_id, ref.referrer_date_added, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr FROM PatientReferrer pr LEFT JOIN RegisterReferrer rr ON pr.register_referrer_id = rr.register_referrer_id LEFT OUTER JOIN Organisation o ON o.organisation_id = rr.organisation_id LEFT JOIN Referrer ref ON ref.referrer_id = rr.referrer_id LEFT JOIN Person p ON p.person_id = ref.person_id LEFT JOIN Title t ON t.title_id = p.title_id LEFT JOIN Patient patient ON pr.patient_id = patient.patient_id WHERE pr.is_active = 1 AND rr.is_deleted = 0 AND patient.is_deleted = 0 -- make sure its a referring 'doctor' and not a referring organisation (ie a mailout that referred a new customer AND pr.register_referrer_id IS NOT NULL -- get by org_id AND pr.patient_id IN (SELECT patient_id FROM RegisterPatient WHERE is_deleted = 0 AND organisation_id = " + organisation_id + @") -- where patiet has multiple referring doctors, get only the current one AND pr.patient_referrer_date_added = ( SELECT MAX(pr2.patient_referrer_date_added) FROM PatientReferrer pr2 WHERE pr2.register_referrer_id IS NOT NULL AND pr2.patient_id = pr.patient_id ) "; return(DBBase.ExecuteQuery(sql).Tables[0]); }
private static string JoinedSql(bool incRegRefDeleted = false, bool incRefDeleted = false, bool incOrgDeleted = false) { string whereClause = " 1=1 "; if (!incRegRefDeleted) { whereClause += (whereClause.Length == 0 ? "" : " AND ") + " r.is_deleted = 0 "; } if (!incRefDeleted) { whereClause += (whereClause.Length == 0 ? "" : " AND ") + " ref.is_deleted = 0 "; } if (!incOrgDeleted) { whereClause += (whereClause.Length == 0 ? "" : " AND ") + " o.is_deleted = 0 "; } string sql = @" SELECT r.register_referrer_id, r.organisation_id, r.referrer_id, r.provider_number, r.report_every_visit_to_referrer, r.batch_send_all_patients_treatment_notes, r.date_last_batch_send_all_patients_treatment_notes, r.register_referrer_date_added, r.is_deleted, o.entity_id as organisation_entity_id, o.parent_organisation_id, o.use_parent_offernig_prices, o.organisation_type_id, o.organisation_customer_type_id, o.name, o.acn, o.abn, o.organisation_date_added, o.organisation_date_modified, o.is_debtor, o.is_creditor, o.bpay_account, o.weeks_per_service_cycle, o.start_date, o.end_date, o.comment, o.free_services, o.excl_sun, o.excl_mon, o.excl_tue, o.excl_wed, o.excl_thu, o.excl_fri, o.excl_sat, o.sun_start_time, o.sun_end_time, o.mon_start_time, o.mon_end_time, o.tue_start_time, o.tue_end_time, o.wed_start_time, o.wed_end_time, o.thu_start_time, o.thu_end_time, o.fri_start_time, o.fri_end_time, o.sat_start_time, o.sat_end_time, o.sun_lunch_start_time, o.sun_lunch_end_time, o.mon_lunch_start_time, o.mon_lunch_end_time, o.tue_lunch_start_time, o.tue_lunch_end_time, o.wed_lunch_start_time, o.wed_lunch_end_time, o.thu_lunch_start_time, o.thu_lunch_end_time, o.fri_lunch_start_time, o.fri_lunch_end_time, o.sat_lunch_start_time, o.sat_lunch_end_time, o.last_batch_run, o.is_deleted as organisation_is_deleted, ref.person_id, ref.referrer_date_added, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr FROM RegisterReferrer r LEFT OUTER JOIN Organisation o ON o.organisation_id = r.organisation_id INNER JOIN Referrer ref ON ref.referrer_id = r.referrer_id INNER JOIN Person p ON p.person_id = ref.person_id INNER JOIN Title t ON t.title_id = p.title_id WHERE " + whereClause; return(sql); }
public static DataTable GetDataTable(int referrer_id = -1, string matchSurname = "", bool searchSurnameOnlyStartsWith = false, bool incDeleted = false) { matchSurname = matchSurname.Replace("'", "''"); string sql = @"SELECT r.referrer_id,r.person_id,r.referrer_date_added, r.is_deleted, " + PersonDB.GetFields("", "p") + @", p2.firstname AS added_by_firstname, t.title_id, t.descr -- (select count(*) from registerreferrer where r.referrer_id = registerreferrer.referrer_id) as FROM Referrer AS r INNER JOIN Person p ON r.person_id = p.person_id LEFT OUTER JOIN Person p2 ON p2.person_id = p.added_by INNER JOIN Title t ON t.title_id = p.title_id WHERE " + (incDeleted ? "1=1 " : "r.is_deleted = 0 ") + (referrer_id != -1 ? " AND r.referrer_id = " + referrer_id : "") + ((matchSurname.Length > 0 && !searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '%" + matchSurname + "%'" : "") + ((matchSurname.Length > 0 && searchSurnameOnlyStartsWith) ? " AND p.surname LIKE '" + matchSurname + "%'" : "") + @" ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
public static DataTable GetDataTable_StaffOf(int[] organistion_ids, bool inc_deleted = false, bool only_providers = false, bool excl_external = true) { if (organistion_ids == null || organistion_ids.Length == 0) { organistion_ids = new int[] { 0 } } ; string sql = @"SELECT DISTINCT s.staff_id, s.person_id, s.login, s.pwd, s.staff_position_id, s.field_id, s.costcentre_id, s.is_contractor, s.tfn, s.provider_number, s.is_fired, s.is_commission, s.commission_percent, s.is_stakeholder, s.is_master_admin, s.is_admin, s.is_principal, s.is_provider, s.is_external, s.staff_date_added, s.start_date, s.end_date, s.comment, s.num_days_to_display_on_booking_screen, s.show_header_on_booking_screen, s.bk_screen_field_id, s.bk_screen_show_key, s.enable_daily_reminder_sms, s.enable_daily_reminder_email, s.hide_booking_notes, sr.field_id as field_field_id,sr.descr as field_descr, " + PersonDB.GetFields("", "p").Replace("p.entity_id", "p.entity_id AS person_entity_id") + @", t.title_id, t.descr FROM RegisterStaff AS r LEFT OUTER JOIN Staff s ON r.staff_id = s.staff_id LEFT OUTER JOIN Person p ON s.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id LEFT OUTER JOIN Field sr ON s.field_id = sr.field_id WHERE s.staff_id > 0 AND s.is_fired = 0 " + (inc_deleted ? "" : " AND r.is_deleted = 0 ") + (!only_providers ? "" : " AND s.is_provider = 1 ") + (!excl_external ? "" : " AND s.is_external = 0 ") + " AND r.organisation_id IN (" + string.Join(",", organistion_ids) + @") ORDER BY p.surname, p.firstname, p.middlename"; return(DBBase.ExecuteQuery(sql).Tables[0]); }
protected static string JoinedSql(bool onlyTopRow = false) { return(@"SELECT " + (onlyTopRow ? " top 1 " : "") + @" userlogin.userlogin_id as userlogin_userlogin_id,userlogin.staff_id as userlogin_staff_id,userlogin.patient_id as userlogin_patient_id,userlogin.username as userlogin_username, userlogin.site_id as userlogin_site_id,userlogin.is_successful as userlogin_is_successful,userlogin.is_logged_off as userlogin_is_logged_off, userlogin.session_id as userlogin_session_id,userlogin.login_time as userlogin_login_time,userlogin.last_access_time as userlogin_last_access_time,userlogin.last_access_page as userlogin_last_access_page, userlogin.ipaddress as userlogin_ipaddress, staff.staff_id as staff_staff_id,staff.person_id as staff_person_id,staff.login as staff_login,staff.pwd as staff_pwd,staff.staff_position_id as staff_staff_position_id, staff.field_id as staff_field_id,staff.is_contractor as staff_is_contractor,staff.tfn as staff_tfn, staff.is_fired as staff_is_fired,staff.costcentre_id as staff_costcentre_id, staff.is_admin as staff_is_admin,staff.provider_number as staff_provider_number,staff.is_commission as staff_is_commission,staff.commission_percent as staff_commission_percent, staff.is_stakeholder as staff_is_stakeholder,staff.is_master_admin as staff_is_master_admin,staff.is_admin as staff_is_admin,staff.is_principal as staff_is_principal,staff.is_provider as staff_is_provider, staff.is_external as staff_is_external, staff.staff_date_added as staff_staff_date_added,staff.start_date as staff_start_date,staff.end_date as staff_end_date,staff.comment as staff_comment, staff.num_days_to_display_on_booking_screen as staff_num_days_to_display_on_booking_screen, staff.show_header_on_booking_screen as staff_show_header_on_booking_screen, staff.bk_screen_field_id as staff_bk_screen_field_id, staff.bk_screen_show_key as staff_bk_screen_show_key, staff.enable_daily_reminder_sms as staff_enable_daily_reminder_sms, staff.enable_daily_reminder_email as staff_enable_daily_reminder_email, staff.hide_booking_notes as staff_hide_booking_notes, " + PersonDB.GetFields("person_", "p") + @", t.title_id as title_title_id, t.descr as title_descr, c.costcentre_id as cost_centre_costcentre_id,c.descr as cost_centre_descr,c.parent_id as cost_centre_parent_id, sr.field_id as field_field_id,sr.descr as field_descr, sp.staff_position_id as staff_position_staff_position_id,sp.descr as staff_position_descr, patient.patient_id as patient_patient_id, patient.person_id as patient_person_id, patient.patient_date_added as patient_patient_date_added, patient.is_clinic_patient as patient_is_clinic_patient, patient.is_gp_patient as patient_is_gp_patient, patient.is_deleted as patient_is_deleted, patient.is_deceased as patient_is_deceased, patient.flashing_text as patient_flashing_text, patient.flashing_text_added_by as patient_flashing_text_added_by, patient.flashing_text_last_modified_date as patient_flashing_text_last_modified_date, patient.private_health_fund as patient_private_health_fund, patient.concession_card_number as patient_concession_card_number, patient.concession_card_expiry_date as patient_concession_card_expiry_date, patient.is_diabetic as patient_is_diabetic, patient.is_member_diabetes_australia as patient_is_member_diabetes_australia, patient.diabetic_assessment_review_date as patient_diabetic_assessment_review_date, patient.ac_inv_offering_id as patient_ac_inv_offering_id, patient.ac_pat_offering_id as patient_ac_pat_offering_id, patient.login as patient_login, patient.pwd as patient_pwd, patient.is_company as patient_is_company, patient.abn as patient_abn, patient.next_of_kin_name as patient_next_of_kin_name, patient.next_of_kin_relation as patient_next_of_kin_relation, patient.next_of_kin_contact_info as patient_next_of_kin_contact_info, " + PersonDB.GetFields("pperson_", "pp") + @", tt.title_id as ttitle_title_id, tt.descr as ttitle_descr, site.site_id as site_site_id,site.entity_id as site_entity_id,site.name as site_name,site.site_type_id as site_site_type_id,site.abn as site_abn,site.acn as site_acn,site.tfn as site_tfn, site.asic as site_asic,site.is_provider as site_is_provider,site.bank_bpay as site_bank_bpay,site.bank_bsb as site_bank_bsb,site.bank_account as site_bank_account, site.bank_direct_debit_userid as site_bank_direct_debit_userid,site.bank_username as site_bank_username,site.oustanding_balance_warning as site_oustanding_balance_warning, site.print_epc as site_print_epc,site.excl_sun as site_excl_sun,site.excl_mon as site_excl_mon,site.excl_tue as site_excl_tue,site.excl_wed as site_excl_wed,site.excl_thu as site_excl_thu, site.excl_fri as site_excl_fri,site.excl_sat as site_excl_sat,site.day_start_time as site_day_start_time,site.lunch_start_time as site_lunch_start_time, site.lunch_end_time as site_lunch_end_time,site.day_end_time as site_day_end_time,site.fiscal_yr_end as site_fiscal_yr_end,site.num_booking_months_to_get as site_num_booking_months_to_get, sitetype.descr as site_type_descr FROM UserLogin userlogin LEFT OUTER JOIN Site site ON site.site_id = userlogin.site_id LEFT OUTER JOIN SiteType sitetype ON site.site_type_id = sitetype.site_type_id LEFT OUTER JOIN Staff staff ON staff.staff_id = userlogin.staff_id LEFT OUTER JOIN Person p ON staff.person_id = p.person_id LEFT OUTER JOIN Title t ON t.title_id = p.title_id LEFT OUTER JOIN Patient patient ON patient.patient_id = userlogin.patient_id LEFT OUTER JOIN Person pp ON patient.person_id = pp.person_id LEFT OUTER JOIN Title tt ON tt.title_id = pp.title_id LEFT OUTER JOIN Field sr ON staff.field_id = sr.field_id LEFT OUTER JOIN StaffPosition sp ON sp.staff_position_id = staff.staff_position_id LEFT OUTER JOIN CostCentre AS c ON c.costcentre_id = staff.costcentre_id "); }
public static string[] BulkGetAllTreatmentNotes(DateTime date_start, DateTime date_end, string newline = "\n", bool incNoteIDForDebug = false) { string recurring_condition = string.Empty; string non_recurring_condition = string.Empty; if (date_start != DateTime.MinValue && date_end != DateTime.MinValue) { recurring_condition = "AND ( booking.date_start >= '" + date_start.ToString("yyyy-MM-dd HH:mm:ss") + "' AND booking.date_end <= '" + date_end.ToString("yyyy-MM-dd HH:mm:ss") + @"' )"; non_recurring_condition = "AND ( booking.date_end IS NULL OR booking.date_end > '" + date_start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "') " + (date_end == DateTime.MinValue ? "" : " AND booking.date_start < '" + date_end.Date.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss") + "'") + @""; } else if (date_start != DateTime.MinValue) { recurring_condition = "AND ( booking.date_start >= '" + date_start.ToString("yyyy-MM-dd HH:mm:ss") + "')"; non_recurring_condition = "AND ( booking.date_end IS NULL OR booking.date_end > '" + date_start.Date.ToString("yyyy-MM-dd HH:mm:ss") + "') " + @""; } else if (date_end != DateTime.MinValue) { recurring_condition = "AND ( booking.date_end <= '" + date_end.ToString("yyyy-MM-dd HH:mm:ss") + @"' )"; non_recurring_condition = "AND ( booking.date_start < '" + date_end.Date.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss") + @"' )"; } else { recurring_condition = ""; non_recurring_condition = ""; } string sql = @" SELECT note.note_id as note_note_id, note.entity_id as note_entity_id, note.note_type_id as note_note_type_id, note.body_part_id as note_body_part_id, note.text as note_text, note.date_added as note_date_added, note.date_modified as note_date_modified, note.added_by as note_added_by, note.modified_by as note_modified_by, note.site_id as note_site_id, booking.booking_id as booking_booking_id,booking.entity_id as booking_entity_id, booking.date_start as booking_date_start,booking.date_end as booking_date_end,booking.organisation_id as booking_organisation_id, booking.provider as booking_provider,booking.patient_id as booking_patient_id,booking.offering_id as booking_offering_id,booking.booking_type_id as booking_booking_type_id, booking.booking_status_id as booking_booking_status_id,booking.booking_unavailability_reason_id as booking_booking_unavailability_reason_id,booking.added_by as booking_added_by,booking.date_created as booking_date_created, booking.booking_confirmed_by_type_id as booking_booking_confirmed_by_type_id,booking.confirmed_by as booking_confirmed_by,booking.date_confirmed as booking_date_confirmed, booking.deleted_by as booking_deleted_by, booking.date_deleted as booking_date_deleted, booking.cancelled_by as booking_cancelled_by, booking.date_cancelled as booking_date_cancelled, booking.is_patient_missed_appt as booking_is_patient_missed_appt,booking.is_provider_missed_appt as booking_is_provider_missed_appt, booking.is_emergency as booking_is_emergency, booking.need_to_generate_first_letter as booking_need_to_generate_first_letter,booking.need_to_generate_last_letter as booking_need_to_generate_last_letter,booking.has_generated_system_letters as booking_has_generated_system_letters, booking.arrival_time as booking_arrival_time, booking.sterilisation_code as booking_sterilisation_code, booking.informed_consent_added_by as booking_informed_consent_added_by, booking.informed_consent_date as booking_informed_consent_date, booking.is_recurring as booking_is_recurring,booking.recurring_weekday_id as booking_recurring_weekday_id, booking.recurring_start_time as booking_recurring_start_time,booking.recurring_end_time as booking_recurring_end_time, (SELECT 0) AS booking_note_count, (SELECT 0) AS booking_inv_count, patient.patient_id as patient_patient_id, patient.person_id as patient_person_id, patient.patient_date_added as patient_patient_date_added, patient.is_clinic_patient as patient_is_clinic_patient,patient.is_gp_patient as patient_is_gp_patient,patient.is_deleted as patient_is_deleted,patient.is_deceased as patient_is_deceased, patient.flashing_text as patient_flashing_text, patient.flashing_text_added_by as patient_flashing_text_added_by, patient.flashing_text_last_modified_date as patient_flashing_text_last_modified_date, patient.private_health_fund as patient_private_health_fund, patient.concession_card_number as patient_concession_card_number, patient.concession_card_expiry_date as patient_concession_card_expiry_date, patient.is_diabetic as patient_is_diabetic, patient.is_member_diabetes_australia as patient_is_member_diabetes_australia, patient.diabetic_assessment_review_date as patient_diabetic_assessment_review_date, patient.ac_inv_offering_id as patient_ac_inv_offering_id, patient.ac_pat_offering_id as patient_ac_pat_offering_id, patient.login as patient_login, patient.pwd as patient_pwd, patient.is_company as patient_is_company, patient.abn as patient_abn, patient.next_of_kin_name as patient_next_of_kin_name, patient.next_of_kin_relation as patient_next_of_kin_relation, patient.next_of_kin_contact_info as patient_next_of_kin_contact_info, " + PersonDB.GetFields("person_patient_", "person_patient") + @", title_patient.title_id as title_patient_title_id, title_patient.descr as title_patient_descr, rr.register_referrer_id as rr_register_referrer_id, rr.organisation_id as rr_organisation_id, rr.referrer_id as rr_referrer_id, rr.provider_number as rr_provider_number, rr.report_every_visit_to_referrer as rr_report_every_visit_to_referrer, rr.batch_send_all_patients_treatment_notes as rr_batch_send_all_patients_treatment_notes, rr.date_last_batch_send_all_patients_treatment_notes as rr_date_last_batch_send_all_patients_treatment_notes, rr.register_referrer_date_added as rr_register_referrer_date_added, rr.is_deleted as rr_is_deleted, ref.referrer_id as ref_referrer_id, ref.person_id as ref_person_id, ref.referrer_date_added as ref_referrer_date_added, " + PersonDB.GetFields("p_", "p") + @", t.title_id as t_title_id, t.descr as t_descr FROM Note note INNER JOIN Booking booking ON booking.entity_id = note.entity_id INNER JOIN Patient patient ON patient.patient_id = booking.patient_id INNER JOIN Person person_patient ON person_patient.person_id = patient.person_id INNER JOIN Title title_patient ON title_patient.title_id = person_patient.title_id INNER Join PatientReferrer patient_referrer ON patient_referrer.patient_id = patient.patient_id INNER Join RegisterReferrer rr ON rr.register_referrer_id = patient_referrer.register_referrer_id INNER JOIN Referrer ref ON ref.referrer_id = rr.referrer_id INNER JOIN Person p ON p.person_id = ref.person_id INNER JOIN Title t ON t.title_id = p.title_id WHERE note.note_type_id = 252 -- only provider treatment notes AND booking.booking_status_id = 187 -- only get completed bookings AND booking.booking_type_id = 34 -- only get bookings for patients (not blockout-prov/org-timeslot bookings) AND ( (booking.is_recurring = 0 " + recurring_condition + @") OR (booking.is_recurring = 1 " + non_recurring_condition + @") ) AND rr.is_deleted = 0 AND rr.batch_send_all_patients_treatment_notes = 1 ORDER BY rr.register_referrer_id, person_patient.surname, person_patient.firstname, booking.date_start, note.note_id "; DataTable tbl = DBBase.ExecuteQuery(sql).Tables[0]; string[] notes = new string[tbl.Rows.Count]; for (int i = 0; i < tbl.Rows.Count; i++) { Booking booking = BookingDB.Load(tbl.Rows[i], "booking_"); booking.Patient = PatientDB.Load(tbl.Rows[i], "patient_"); booking.Patient.Person = PersonDB.Load(tbl.Rows[i], "person_patient_"); booking.Patient.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "title_patient_title_id", "title_patient_descr"); Note note = NoteDB.Load(tbl.Rows[i], "note_"); RegisterReferrer rr = RegisterReferrerDB.Load(tbl.Rows[i], "rr_"); rr.Referrer = ReferrerDB.Load(tbl.Rows[i], "ref_"); rr.Referrer.Person = PersonDB.Load(tbl.Rows[i], "p_"); rr.Referrer.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "t_title_id", "t_descr"); notes[i] = booking.GetNoteTextForTreatmentLetter(rr.Referrer, note, newline, incNoteIDForDebug); } return(notes); }
protected static string JoinedSql() { string sql = @" SELECT bpo.booking_patient_offering_id as bpo_booking_patient_offering_id, bpo.booking_patient_id as bpo_booking_patient_id, bpo.offering_id as bpo_offering_id, bpo.quantity as bpo_quantity, bpo.added_by as bpo_added_by, bpo.added_date as bpo_added_date, bpo.is_deleted as bpo_is_deleted, bpo.deleted_by as bpo_deleted_by, bpo.deleted_date as bpo_deleted_date, bpo.area_treated as bpo_area_treated, offering.offering_id as offering_offering_id, offering.offering_type_id as offering_offering_type_id, offering.field_id as offering_field_id, offering.aged_care_patient_type_id as offering_aged_care_patient_type_id, offering.num_clinic_visits_allowed_per_year as offering_num_clinic_visits_allowed_per_year, offering.offering_invoice_type_id as offering_offering_invoice_type_id, offering.name as offering_name, offering.short_name as offering_short_name, offering.descr as offering_descr, offering.is_gst_exempt as offering_is_gst_exempt, offering.default_price as offering_default_price, offering.service_time_minutes as offering_service_time_minutes, offering.max_nbr_claimable as offering_max_nbr_claimable, offering.max_nbr_claimable_months as offering_max_nbr_claimable_months, offering.medicare_company_code as offering_medicare_company_code, offering.dva_company_code as offering_dva_company_code, offering.tac_company_code as offering_tac_company_code, offering.medicare_charge as offering_medicare_charge, offering.dva_charge as offering_dva_charge, offering.tac_charge as offering_tac_charge, offering.popup_message as offering_popup_message, offering.reminder_letter_months_later_to_send as offering_reminder_letter_months_later_to_send, offering.reminder_letter_id as offering_reminder_letter_id, offering.use_custom_color as offering_use_custom_color, offering.custom_color as offering_custom_color, offeringfield.field_id as offeringfield_field_id, offeringfield.descr as offeringfield_descr, added_by.staff_id as added_by_staff_id,added_by.person_id as added_by_person_id,added_by.login as added_by_login,added_by.pwd as added_by_pwd,added_by.staff_position_id as added_by_staff_position_id, added_by.field_id as added_by_field_id,added_by.is_contractor as added_by_is_contractor,added_by.tfn as added_by_tfn, added_by.is_fired as added_by_is_fired,added_by.costcentre_id as added_by_costcentre_id, added_by.is_admin as added_by_is_admin,added_by.provider_number as added_by_provider_number,added_by.is_commission as added_by_is_commission,added_by.commission_percent as added_by_commission_percent, added_by.is_stakeholder as added_by_is_stakeholder, added_by.is_master_admin as added_by_is_master_admin, added_by.is_admin as added_by_is_admin, added_by.is_principal as added_by_is_principal, added_by.is_provider as added_by_is_provider, added_by.is_external as added_by_is_external, added_by.staff_date_added as added_by_staff_date_added,added_by.start_date as added_by_start_date,added_by.end_date as added_by_end_date,added_by.comment as added_by_comment, added_by.num_days_to_display_on_booking_screen as added_by_num_days_to_display_on_booking_screen, added_by.show_header_on_booking_screen as added_by_show_header_on_booking_screen, added_by.bk_screen_field_id as added_by_bk_screen_field_id, added_by.bk_screen_show_key as added_by_bk_screen_show_key, added_by.enable_daily_reminder_sms as added_by_enable_daily_reminder_sms, added_by.enable_daily_reminder_email as added_by_enable_daily_reminder_email, " + PersonDB.GetFields("added_by_person_", "added_by_person") + @", title_added_by.title_id as title_added_by_title_id, title_added_by.descr as title_added_by_descr, deleted_by.staff_id as deleted_by_staff_id,deleted_by.person_id as deleted_by_person_id,deleted_by.login as deleted_by_login,deleted_by.pwd as deleted_by_pwd,deleted_by.staff_position_id as deleted_by_staff_position_id, deleted_by.field_id as deleted_by_field_id,deleted_by.is_contractor as deleted_by_is_contractor,deleted_by.tfn as deleted_by_tfn, deleted_by.is_fired as deleted_by_is_fired,deleted_by.costcentre_id as deleted_by_costcentre_id, deleted_by.is_admin as deleted_by_is_admin,deleted_by.provider_number as deleted_by_provider_number,deleted_by.is_commission as deleted_by_is_commission,deleted_by.commission_percent as deleted_by_commission_percent, deleted_by.is_stakeholder as deleted_by_is_stakeholder, deleted_by.is_master_admin as deleted_by_is_master_admin, deleted_by.is_admin as deleted_by_is_admin, deleted_by.is_principal as deleted_by_is_principal, deleted_by.is_provider as deleted_by_is_provider, deleted_by.is_external as deleted_by_is_external, deleted_by.staff_date_added as deleted_by_staff_date_added,deleted_by.start_date as deleted_by_start_date,deleted_by.end_date as deleted_by_end_date,deleted_by.comment as deleted_by_comment, deleted_by.num_days_to_display_on_booking_screen as deleted_by_num_days_to_display_on_booking_screen, deleted_by.show_header_on_booking_screen as deleted_by_show_header_on_booking_screen, deleted_by.bk_screen_field_id as deleted_by_bk_screen_field_id, deleted_by.bk_screen_show_key as deleted_by_bk_screen_show_key, deleted_by.enable_daily_reminder_sms as deleted_by_enable_daily_reminder_sms, deleted_by.enable_daily_reminder_email as deleted_by_enable_daily_reminder_email, " + PersonDB.GetFields("deleted_by_person_", "deleted_by_person") + @", title_deleted_by.title_id as title_deleted_by_title_id, title_deleted_by.descr as title_deleted_by_descr FROM BookingPatientOffering bpo LEFT OUTER JOIN BookingPatient bookingpatient ON bpo.booking_patient_id = bookingpatient.booking_patient_id LEFT OUTER JOIN Offering offering ON bpo.offering_id = offering.offering_id LEFT OUTER JOIN Field offeringfield ON offeringfield.field_id = offering.field_id LEFT OUTER JOIN Staff added_by ON bpo.added_by = added_by.staff_id LEFT OUTER JOIN Person added_by_person ON added_by_person.person_id = added_by.person_id LEFT OUTER JOIN Title title_added_by ON title_added_by.title_id = added_by_person.title_id LEFT OUTER JOIN Staff deleted_by ON bpo.deleted_by = deleted_by.staff_id LEFT OUTER JOIN Person deleted_by_person ON deleted_by_person.person_id = deleted_by.person_id LEFT OUTER JOIN Title title_deleted_by ON title_deleted_by.title_id = deleted_by_person.title_id "; return(sql); }