protected Tuple <Booking, PatientReferrer, bool, string, HealthCard> LoadRow(DataRow row)
{
Booking booking = BookingDB.Load(row, "booking_", true, false);
booking.Offering = OfferingDB.Load(row, "offering_");
PatientReferrer pr = PatientReferrerDB.Load(row, "pr_");
pr.RegisterReferrer = RegisterReferrerDB.Load(row, "regref_");
pr.RegisterReferrer.Referrer = ReferrerDB.Load(row, "referrer_");
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(row, "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(row, "referrer_person_title_title_id", "referrer_person_title_descr");
if (row["organisation_organisation_id"] != DBNull.Value)
{
pr.RegisterReferrer.Organisation = OrganisationDB.Load(row, "organisation_");
}
pr.Patient = PatientDB.Load(row, "patient_");
pr.Patient.Person = PersonDB.Load(row, "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(row, "patient_person_title_title_id", "patient_person_title_descr");
bool refHasEmail = Convert.ToInt32(row["ref_has_email"]) == 1;
string refEmail = row["ref_email"] == DBNull.Value ? null : Convert.ToString(row["ref_email"]);
HealthCard hc = HealthCardDB.Load(row, "hc_");
return(new Tuple <Booking, PatientReferrer, bool, string, HealthCard>(booking, pr, refHasEmail, refEmail, hc));
}
public static PatientReferrer GetByID(int patient_referrer_id)
{
//string sql = "SELECT patient_referrer_id,patient_id,register_referrer_id,patient_referrer_date_added,is_debtor,is_active FROM PatientReferrer WHERE patient_referrer_id = " + patient_referrer_id.ToString();
string sql = JoinedSQL() + " AND patient_referrer_id = " + patient_referrer_id.ToString();
DataTable tbl = DBBase.ExecuteQuery(sql).Tables[0];
if (tbl.Rows.Count == 0)
{
return(null);
}
else
{
PatientReferrer pr = Load(tbl.Rows[0]);
pr.Patient = PatientDB.Load(tbl.Rows[0]);
pr.Patient.Person = PersonDB.Load(tbl.Rows[0], "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(tbl.Rows[0], "patient_person_title_title_id", "patient_person_title_descr");
if (tbl.Rows[0]["pr_register_referrer_id"] != DBNull.Value)
{
pr.RegisterReferrer = RegisterReferrerDB.Load(tbl.Rows[0]);
pr.RegisterReferrer.Referrer = ReferrerDB.Load(tbl.Rows[0]);
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(tbl.Rows[0], "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(tbl.Rows[0], "referrer_person_title_title_id", "referrer_person_title_descr");
pr.RegisterReferrer.Organisation = OrganisationDB.Load(tbl.Rows[0], "organisation_");
}
if (tbl.Rows[0]["pr_organisation_id"] != DBNull.Value)
{
pr.Organisation = OrganisationDB.Load(tbl.Rows[0], "nonepcorg_");
}
return(pr);
}
}
protected void GrdSummaryReport_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataTable dt = Session["data_bookingswithoutsmsoremail"] as DataTable;
bool tblEmpty = (dt.Rows.Count == 1 && dt.Rows[0][0] == DBNull.Value);
if (!tblEmpty && e.Row.RowType == DataControlRowType.DataRow)
{
Label lblId = (Label)e.Row.FindControl("lblId");
DataRow[] foundRows = dt.Select("patient_id=" + lblId.Text);
DataRow thisRow = foundRows[0];
Patient patient = PatientDB.Load(thisRow);
patient.Person = PersonDB.Load(thisRow);
patient.Person.Title = IDandDescrDB.Load(thisRow, "t_title_id", "t_descr");
Utilities.AddConfirmationBox(e);
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
Utilities.SetEditRowBackColour(e, System.Drawing.Color.LightGoldenrodYellow);
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
}
}
public static PatientCondition LoadAll(DataRow row)
{
PatientCondition patientCondition = Load(row, "patient_condition_");
patientCondition.Condition = ConditionDB.Load(row, "condition_");
patientCondition.Patient = PatientDB.Load(row, "patient_");
patientCondition.Patient.Person = PersonDB.Load(row, "person_", "entity_id");
patientCondition.Patient.Person.Title = IDandDescrDB.Load(row, "title_title_id", "title_descr");
return(patientCondition);
}
public static Patient[] GetAllNotInc(RegisterReferrer[] excList)
{
DataTable tbl = GetDataTable_AllNotInc(excList);
Patient[] list = new Patient[tbl.Rows.Count];
for (int i = 0; i < tbl.Rows.Count; i++)
{
list[i] = PatientDB.Load(tbl.Rows[i]);
list[i].Person = PersonDB.Load(tbl.Rows[i]);
list[i].Person.Title = IDandDescrDB.Load(tbl.Rows[i], "type_id", "descr");
}
return(list);
}
private static Patient[] _GetPatientsOf(int register_referrer_id, bool onlyActive)
{
DataTable tbl = onlyActive ? GetDataTable_ActivePatientsOf(register_referrer_id) : GetDataTable_PatientsOf(register_referrer_id);
Patient[] list = new Patient[tbl.Rows.Count];
for (int i = 0; i < tbl.Rows.Count; i++)
{
Patient patient = PatientDB.Load(tbl.Rows[i]);
patient.Person = PersonDB.Load(tbl.Rows[i]);
patient.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "title_id", "descr");
list[i] = patient;
}
return(list);
}
public static InvoiceLine LoadAll(DataRow row)
{
InvoiceLine line = Load(row);
if (row["offering_id"] != DBNull.Value)
{
line.Offering = OfferingDB.Load(row);
}
if (row["patient_id"] != DBNull.Value)
{
line.Patient = PatientDB.Load(row, "patient_");
line.Patient.Person = PersonDB.Load(row, "patient_person_");
line.Patient.Person.Title = IDandDescrDB.Load(row, "title_patient_title_id", "title_patient_descr");
}
return(line);
}
public static SMSHistoryData LoadAll(DataRow row)
{
SMSHistoryData s = Load(row);
if (row["booking_booking_id"] != DBNull.Value)
{
s.Booking = BookingDB.Load(row, "booking_", false, false);
}
if (row["patient_patient_id"] != DBNull.Value)
{
s.Patient = PatientDB.Load(row, "patient_");
s.Patient.Person = PersonDB.Load(row, "person_patient_");
s.Patient.Person.Title = IDandDescrDB.Load(row, "title_patient_title_id", "title_patient_descr");
}
return(s);
}
public static LetterBestPrintHistory LoadAll(DataRow row)
{
LetterBestPrintHistory lph = Load(row, "lph_");
lph.Letter = LetterBestDB.Load(row, "letter_");
lph.Letter.LetterType = IDandDescrDB.Load(row, "lettertype_letter_type_id", "lettertype_descr");
if (row["patient_patient_id"] != DBNull.Value)
{
lph.Patient = PatientDB.Load(row, "patient_");
}
if (row["patient_patient_id"] != DBNull.Value)
{
lph.Patient.Person = PersonDB.Load(row, "person_patient_");
lph.Patient.Person.Title = IDandDescrDB.Load(row, "title_patient_title_id", "title_patient_descr");
}
return(lph);
}
public static OfferingOrder LoadAll(DataRow row)
{
OfferingOrder o = Load(row, "offeringorder_");
if (row["organisation_organisation_id"] != DBNull.Value)
{
o.Organisation = OrganisationDB.Load(row, "organisation_");
o.Organisation.OrganisationType = OrganisationTypeDB.Load(row, "org_type_");
o.Organisation.OrganisationType.OrganisationTypeGroup = IDandDescrDB.Load(row, "typegroup_organisation_type_group_id", "typegroup_descr");
}
if (row["staff_staff_id"] != DBNull.Value)
{
o.Staff = StaffDB.Load(row, "staff_");
}
if (row["staff_staff_id"] != DBNull.Value)
{
o.Staff.Person = PersonDB.Load(row, "person_staff_");
o.Staff.Person.Title = IDandDescrDB.Load(row, "title_staff_title_id", "title_staff_descr");
}
if (row["patient_patient_id"] != DBNull.Value)
{
o.Patient = PatientDB.Load(row, "patient_");
}
if (row["patient_patient_id"] != DBNull.Value)
{
o.Patient.Person = PersonDB.Load(row, "person_patient_");
o.Patient.Person.Title = IDandDescrDB.Load(row, "title_patient_title_id", "title_patient_descr");
}
if (row["offering_offering_id"] != DBNull.Value)
{
o.Offering = OfferingDB.Load(row, "offering_");
}
if (row["offeringfield_field_id"] != DBNull.Value)
{
o.Offering.Field = IDandDescrDB.Load(row, "offeringfield_field_id", "offeringfield_descr");
}
return(o);
}
public static UserLogin LoadAll(DataRow row)
{
UserLogin userlogin = Load(row, "userlogin_");
if (row["userlogin_site_id"] != DBNull.Value)
{
userlogin.Site = SiteDB.Load(row, "site_");
}
if (row["userlogin_staff_id"] != DBNull.Value)
{
userlogin.Staff = StaffDB.LoadAll(row);
}
if (row["userlogin_patient_id"] != DBNull.Value)
{
userlogin.Patient = PatientDB.Load(row, "patient_");
userlogin.Patient.Person = PersonDB.Load(row, "pperson_");
userlogin.Patient.Person.Title = IDandDescrDB.Load(row, "ttitle_title_id", "ttitle_descr");
}
return(userlogin);
}
public static PatientReferrer LoadAll(DataRow row)
{
PatientReferrer pr = PatientReferrerDB.Load(row, "pr_");
pr.RegisterReferrer = RegisterReferrerDB.Load(row, "regref_");
pr.RegisterReferrer.Referrer = ReferrerDB.Load(row, "referrer_");
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(row, "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(row, "referrer_person_title_title_id", "referrer_person_title_descr");
if (row["organisation_organisation_id"] != DBNull.Value)
{
pr.RegisterReferrer.Organisation = OrganisationDB.Load(row, "organisation_");
}
pr.Patient = PatientDB.Load(row, "patient_");
pr.Patient.Person = PersonDB.Load(row, "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(row, "patient_person_title_title_id", "patient_person_title_descr");
if (row["nonepcorg_entity_id"] != DBNull.Value)
{
pr.Organisation = OrganisationDB.Load(row, "nonepcorg_");
}
return(pr);
}
private static PatientReferrer[] _GetEPCPatientReferrersOf(int patient_id, bool onlyActive)
{
DataTable tbl = onlyActive ? GetDataTable_ActiveEPCReferrersOf(patient_id) : GetDataTable_EPCReferrersOf(patient_id);
PatientReferrer[] list = new PatientReferrer[tbl.Rows.Count];
for (int i = 0; i < tbl.Rows.Count; i++)
{
PatientReferrer pr = Load(tbl.Rows[i], "pr_");
pr.RegisterReferrer = RegisterReferrerDB.Load(tbl.Rows[i], "regref_");
pr.RegisterReferrer.Referrer = ReferrerDB.Load(tbl.Rows[i], "referrer_");
pr.RegisterReferrer.Referrer.Person = PersonDB.Load(tbl.Rows[i], "referrer_person_");
pr.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "referrer_person_title_title_id", "referrer_person_title_descr");
if (tbl.Rows[i]["organisation_organisation_id"] != DBNull.Value)
{
pr.RegisterReferrer.Organisation = OrganisationDB.Load(tbl.Rows[i], "organisation_");
}
pr.Patient = PatientDB.Load(tbl.Rows[i], "patient_");
pr.Patient.Person = PersonDB.Load(tbl.Rows[i], "patient_person_");
pr.Patient.Person.Title = IDandDescrDB.Load(tbl.Rows[i], "patient_person_title_title_id", "patient_person_title_descr");
list[i] = pr;
}
return(list);
}
public static LetterPrintHistory LoadAll(DataRow row)
{
LetterPrintHistory lph = Load(row, "lph_");
lph.Letter = LetterDB.Load(row, "letter_");
lph.SendMethod = new IDandDescr(Convert.ToInt32(row["lph_send_method_letter_print_history_send_method_id"]), Convert.ToString(row["lph_send_method_descr"]));
lph.Letter.LetterType = IDandDescrDB.Load(row, "lettertype_letter_type_id", "lettertype_descr");
if (row["letterorg_organisation_id"] != DBNull.Value)
{
lph.Letter.Organisation = OrganisationDB.Load(row, "letterorg_");
}
if (row["organisation_organisation_id"] != DBNull.Value)
{
lph.Organisation = OrganisationDB.Load(row, "organisation_");
}
if (row["patient_patient_id"] != DBNull.Value)
{
lph.Patient = PatientDB.Load(row, "patient_");
}
if (row["patient_patient_id"] != DBNull.Value)
{
lph.Patient.Person = PersonDB.Load(row, "person_patient_");
lph.Patient.Person.Title = IDandDescrDB.Load(row, "title_patient_title_id", "title_patient_descr");
}
if (row["staff_staff_id"] != DBNull.Value)
{
lph.Staff = StaffDB.Load(row, "staff_");
}
if (row["staff_staff_id"] != DBNull.Value)
{
lph.Staff.Person = PersonDB.Load(row, "person_staff_");
lph.Staff.Person.Title = IDandDescrDB.Load(row, "title_staff_title_id", "title_staff_descr");
}
if (row["regref_register_referrer_id"] != DBNull.Value)
{
lph.RegisterReferrer = RegisterReferrerDB.Load(row, "regref_");
}
if (row["regreforg_organisation_id"] != DBNull.Value)
{
lph.RegisterReferrer.Organisation = OrganisationDB.Load(row, "regreforg_");
}
if (row["referrer_referrer_id"] != DBNull.Value)
{
lph.RegisterReferrer.Referrer = ReferrerDB.Load(row, "referrer_");
}
if (row["referrer_referrer_id"] != DBNull.Value)
{
lph.RegisterReferrer.Referrer.Person = PersonDB.Load(row, "person_referrer_");
lph.RegisterReferrer.Referrer.Person.Title = IDandDescrDB.Load(row, "title_referrer_title_id", "title_referrer_descr");
}
if (row["lph_health_card_action_id"] != DBNull.Value)
{
lph.HealthCardAction = HealthCardActionDB.Load(row, "hca_");
lph.HealthCardAction.healthCardActionType = IDandDescrDB.Load(row, "hcat_health_card_action_type_id", "hcat_descr");
lph.HealthCardAction.HealthCard = HealthCardDB.Load(row, "hc_");
}
if (row["lph_booking_id"] != DBNull.Value)
{
lph.Booking = BookingDB.Load(row, "booking_");
}
return(lph);
}
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 void FillGrid()
{
DataTable dt;
try
{
dt = PatientDB.GetBirthdays_DataTable(Convert.ToInt32(ddlStartDate_Month.SelectedValue), Convert.ToInt32(ddlStartDate_Day.SelectedValue),
Convert.ToInt32(ddlEndDate_Month.SelectedValue), Convert.ToInt32(ddlEndDate_Day.SelectedValue));
}
catch (CustomMessageException ex)
{
SetErrorMessage(ex.Message);
return;
}
// get their mobile and emails
Patient[] patients = new Patient[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
patients[i] = PatientDB.Load(dt.Rows[i]);
patients[i].Person = PersonDB.Load(dt.Rows[i]);
patients[i].Person.Title = IDandDescrDB.Load(dt.Rows[i], "t_title_id", "t_descr");
}
Hashtable patientContactPhoneNbrHash = GetPatientPhoneNbrCache(patients);
Hashtable patientContactEmailHash = GetPatientEmailCache(patients);
ArrayList patientIDs = new ArrayList();
dt.Columns.Add("mobile", typeof(string));
dt.Columns.Add("email", typeof(string));
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
string phoneNumPatient = GetPhoneNbr(patientContactPhoneNbrHash, patients[i].Person.EntityID, true);
string emailPatient = GetEmail(patientContactEmailHash, patients[i].Person.EntityID);
if ((!chkIncWithMobile.Checked && (phoneNumPatient != null && phoneNumPatient.Length > 0)) ||
(!chkIncWithEmail.Checked && (emailPatient != null && emailPatient.Length > 0)))
{
dt.Rows.RemoveAt(i);
continue;
}
dt.Rows[i]["mobile"] = phoneNumPatient == null ? "" : phoneNumPatient;
dt.Rows[i]["email"] = emailPatient == null ? "" : emailPatient;
patientIDs.Add(patients[i].PatientID);
}
hiddenPatientIDs.Value = string.Join(",", (int[])patientIDs.ToArray(typeof(int)));
Session["data_bookingswithoutsmsoremail"] = dt;
if (!IsPostBack)
{
chkUsePaging.Checked = dt.Rows.Count > 50;
}
this.GrdSummaryReport.AllowPaging = chkUsePaging.Checked;
if (dt.Rows.Count > 0)
{
if (IsPostBack && Session["sortExpression_bookingswithoutsmsoremail"] != null && Session["sortExpression_bookingswithoutsmsoremail"].ToString().Length > 0)
{
DataView dataView = new DataView(dt);
dataView.Sort = Session["sortExpression_bookingswithoutsmsoremail"].ToString();
GrdSummaryReport.DataSource = dataView;
}
else
{
GrdSummaryReport.DataSource = dt;
}
try
{
GrdSummaryReport.DataBind();
GrdSummaryReport.PagerSettings.FirstPageText = "1";
GrdSummaryReport.PagerSettings.LastPageText = GrdSummaryReport.PageCount.ToString();
GrdSummaryReport.DataBind();
}
catch (Exception ex)
{
SetErrorMessage(ex.ToString());
}
}
else
{
dt.Rows.Add(dt.NewRow());
GrdSummaryReport.DataSource = dt;
GrdSummaryReport.DataBind();
int TotalColumns = GrdSummaryReport.Rows[0].Cells.Count;
GrdSummaryReport.Rows[0].Cells.Clear();
GrdSummaryReport.Rows[0].Cells.Add(new TableCell());
GrdSummaryReport.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GrdSummaryReport.Rows[0].Cells[0].Text = "No Record Found";
}
}
public static void RunBirthdaysWithoutSMSorEmail(bool includeValidation = true)
{
bool enableEmails = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendEmail").Value) == 1;
string emailAddress = SystemVariableDB.GetByDescr("BirthdayNotificationEmail_EmailAddress").Value;
bool incPatientsWithMobile = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_IncPatientsWithMobile").Value) == 1;
bool incPatientsWithEmail = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_IncPatientsWithEmail").Value) == 1;
bool sendMondays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendMondays").Value) == 1;
bool sendTuesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendTuesdays").Value) == 1;
bool sendWednesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendWednesdays").Value) == 1;
bool sendThursdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendThursdays").Value) == 1;
bool sendFridays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFridays").Value) == 1;
bool sendSaturdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendSaturdays").Value) == 1;
bool sendSundays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendSundays").Value) == 1;
int fromDaysAheadMondays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Mondays").Value);
int untilDaysAheadMondays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Mondays").Value);
int fromDaysAheadTuesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Tuesdays").Value);
int untilDaysAheadTuesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Tuesdays").Value);
int fromDaysAheadWednesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Wednesdays").Value);
int untilDaysAheadWednesdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Wednesdays").Value);
int fromDaysAheadThursdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Thursdays").Value);
int untilDaysAheadThursdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Thursdays").Value);
int fromDaysAheadFridays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Fridays").Value);
int untilDaysAheadFridays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Fridays").Value);
int fromDaysAheadSaturdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Saturdays").Value);
int untilDaysAheadSaturdays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Saturdays").Value);
int fromDaysAheadSundays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendFromDaysAhead_Sundays").Value);
int untilDaysAheadSundays = Convert.ToInt32(SystemVariableDB.GetByDescr("BirthdayNotificationEmail_SendUntilDaysAhead_Sundays").Value);
if (includeValidation && !enableEmails)
{
return;
}
if (!Utilities.IsValidEmailAddresses(emailAddress, false))
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Monday && !sendMondays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Tuesday && !sendTuesdays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Wednesday && !sendWednesdays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Thursday && !sendThursdays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Friday && !sendFridays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Saturday && !sendSaturdays)
{
return;
}
if (includeValidation && DateTime.Today.DayOfWeek == DayOfWeek.Sunday && !sendSundays)
{
return;
}
if (fromDaysAheadMondays > untilDaysAheadMondays)
{
return;
}
if (fromDaysAheadTuesdays > untilDaysAheadTuesdays)
{
return;
}
if (fromDaysAheadWednesdays > untilDaysAheadWednesdays)
{
return;
}
if (fromDaysAheadThursdays > untilDaysAheadThursdays)
{
return;
}
if (fromDaysAheadFridays > untilDaysAheadFridays)
{
return;
}
if (fromDaysAheadSaturdays > untilDaysAheadSaturdays)
{
return;
}
if (fromDaysAheadSundays > untilDaysAheadSundays)
{
return;
}
int fromDaysAhead = 0, untilDaysAhead = 0;
if (DateTime.Today.DayOfWeek == DayOfWeek.Monday)
{
fromDaysAhead = fromDaysAheadMondays; untilDaysAhead = untilDaysAheadMondays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Tuesday)
{
fromDaysAhead = fromDaysAheadTuesdays; untilDaysAhead = untilDaysAheadTuesdays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Wednesday)
{
fromDaysAhead = fromDaysAheadWednesdays; untilDaysAhead = untilDaysAheadWednesdays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Thursday)
{
fromDaysAhead = fromDaysAheadThursdays; untilDaysAhead = untilDaysAheadThursdays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Friday)
{
fromDaysAhead = fromDaysAheadFridays; untilDaysAhead = untilDaysAheadFridays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Saturday)
{
fromDaysAhead = fromDaysAheadSaturdays; untilDaysAhead = untilDaysAheadSaturdays;
}
if (DateTime.Today.DayOfWeek == DayOfWeek.Sunday)
{
fromDaysAhead = fromDaysAheadSundays; untilDaysAhead = untilDaysAheadSundays;
}
DateTime start = DateTime.Now.AddDays(fromDaysAhead);
DateTime end = DateTime.Now.AddDays(untilDaysAhead);
System.Data.DataTable dt;
try
{
dt = PatientDB.GetBirthdays_DataTable(start.Month, start.Day, end.Month, end.Day);
}
catch (CustomMessageException ex)
{
Logger.LogException(ex);
return;
}
// get their mobile and emails to filter
Patient[] patients = new Patient[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
patients[i] = PatientDB.Load(dt.Rows[i]);
patients[i].Person = PersonDB.Load(dt.Rows[i]);
patients[i].Person.Title = IDandDescrDB.Load(dt.Rows[i], "t_title_id", "t_descr");
}
Hashtable patientContactPhoneNbrHash = GetPatientPhoneNbrCache(patients);
Hashtable patientContactEmailHash = GetPatientEmailCache(patients);
dt.Columns.Add("mobile", typeof(string));
dt.Columns.Add("email", typeof(string));
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
string phoneNumPatient = GetPhoneNbr(patientContactPhoneNbrHash, patients[i].Person.EntityID, true);
string emailPatient = GetEmail(patientContactEmailHash, patients[i].Person.EntityID);
if ((!incPatientsWithMobile && (phoneNumPatient != null && phoneNumPatient.Length > 0)) ||
(!incPatientsWithEmail && (emailPatient != null && emailPatient.Length > 0)))
{
dt.Rows.RemoveAt(i);
continue;
}
dt.Rows[i]["mobile"] = phoneNumPatient == null ? "" : phoneNumPatient;
dt.Rows[i]["email"] = emailPatient == null ? "" : emailPatient;
}
// put in file to email
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("\"" + "D.O.B." + "\"").Append(",");
sb.Append("\"" + "Name" + "\"").Append(",");
sb.Append("\"" + "Clinic Patient" + "\"").Append(",");
sb.Append("\"" + "Mobile" + "\"").Append(",");
sb.Append("\"" + "Email" + "\"").Append(",");
sb.AppendLine();
bool tblEmpty = (dt.Rows.Count == 1 && dt.Rows[0][0] == DBNull.Value);
if (!tblEmpty)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("\"" + Convert.ToDateTime(dt.Rows[i]["dob"]).ToString("d MMMMM, yyyy") + "\"").Append(",");
sb.Append("\"" + dt.Rows[i]["firstname"].ToString() + " " + dt.Rows[i]["surname"].ToString() + (dt.Rows[i]["t_title_id"] == DBNull.Value || Convert.ToInt32(dt.Rows[i]["t_title_id"]) == 0 ? "" : " (" + dt.Rows[i]["t_descr"] + ")") + "\"").Append(",");
sb.Append("\"" + (Convert.ToBoolean(dt.Rows[i]["is_clinic_patient"]) ? "Yes" : "No") + "\"").Append(",");
sb.Append("\"" + dt.Rows[i]["mobile"].ToString() + "\"").Append(",");
sb.Append("\"" + dt.Rows[i]["email"].ToString() + "\"").Append(",");
sb.AppendLine();
}
}
// put in file, then email it
string tmpLettersDirectory = Letter.GetTempLettersDirectory();
if (!System.IO.Directory.Exists(tmpLettersDirectory))
{
throw new CustomMessageException("Temp letters directory doesn't exist");
}
string tmpDir = FileHelper.GetTempDirectoryName(tmpLettersDirectory);
System.IO.Directory.CreateDirectory(tmpDir);
string tmpFileName = tmpDir + "Birthdays.csv";
System.IO.File.WriteAllText(tmpFileName, sb.ToString());
Emailer.SimpleEmail(emailAddress, "Upcoming Birthdays", "Please find attached all birthdays from " + fromDaysAhead + " days ahead until " + untilDaysAhead + " days ahead.<br /><br />Regards,<br />Mediclinic", true, new string[] { tmpFileName }, null);
System.IO.File.Delete(tmpFileName);
System.IO.Directory.Delete(tmpDir);
}
