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