protected void FillNoteGrid()
{
if (!IsValidFormID())
{
if (!Utilities.IsDev() || Request.QueryString["id"] != null)
{
HideTableAndSetErrorMessage();
return;
}
// can still view all if dev and no id set .. but no insert/edit
GrdNote.Columns[5].Visible = false;
}
if (!IsValidFormScreen() && !Utilities.IsDev())
{
HideTableAndSetErrorMessage();
return;
}
DataTable dt = IsValidFormID() ? NoteDB.GetDataTable_ByEntityID(GetFormID(), null, true, true) : NoteDB.GetDataTable(true);
if (IsValidFormScreen())
{
Hashtable allowedNoteTypes = new Hashtable();
DataTable noteTypes = ScreenNoteTypesDB.GetDataTable_ByScreenID(GetFormScreen());
for (int i = 0; i < noteTypes.Rows.Count; i++)
{
allowedNoteTypes[Convert.ToInt32(noteTypes.Rows[i]["note_type_id"])] = 1;
}
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
if (allowedNoteTypes[Convert.ToInt32(dt.Rows[i]["note_type_id"])] == null)
{
dt.Rows.RemoveAt(i);
}
}
}
UserView userView = UserView.GetInstance();
bool canSeeModifiedBy = userView.IsStakeholder || userView.IsMasterAdmin;
dt.Columns.Add("last_modified_note_info_visible", typeof(Boolean));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["last_modified_note_info_visible"] = canSeeModifiedBy;
}
ViewState["noteinfo_data"] = dt;
// add note info to hidden field to use when emailing notes
string emailBodyText = string.Empty;
Booking booking = BookingDB.GetByEntityID(GetFormID());
if (booking != null)
{
emailBodyText += @"<br /><br />
<u>Treatment Information</u>
<br />
<table border=""0"" cellpadding=""0"" cellspacing=""0"">" +
(booking.Patient == null ? "" : @"<tr><td>Patient</td><td style=""width:10px;""></td><td>" + booking.Patient.Person.FullnameWithoutMiddlename + @"</td></tr>") +
(booking.Offering == null ? "" : @"<tr><td>Service</td><td></td><td>" + booking.Offering.Name + @"</td></tr>") + @"
<tr><td>Date</td><td></td><td>" + booking.DateStart.ToString("dd-MM-yyyy") + @"</td></tr>
<tr><td>Provider</td><td></td><td>" + booking.Provider.Person.FullnameWithoutMiddlename + @"</td></tr>
</table>";
}
for (int i = 0; i < dt.Rows.Count; i++)
{
Note n = NoteDB.Load(dt.Rows[i]);
emailBodyText += "<br /><br /><u>Note (" + n.DateAdded.ToString("dd-MM-yyyy") + ")</u><br />" + n.Text.Replace(Environment.NewLine, "<br />");
}
emailText.Value = emailBodyText + "<br /><br />" + SystemVariableDB.GetByDescr("LettersEmailSignature").Value;;
if (dt.Rows.Count > 0)
{
if (IsPostBack && ViewState["noteinfo_sortexpression"] != null && ViewState["noteinfo_sortexpression"].ToString().Length > 0)
{
DataView dataView = new DataView(dt);
dataView.Sort = ViewState["noteinfo_sortexpression"].ToString();
GrdNote.DataSource = dataView;
}
else
{
GrdNote.DataSource = dt;
}
try
{
GrdNote.DataBind();
}
catch (Exception ex)
{
this.lblErrorMessage.Visible = true;
this.lblErrorMessage.Text = ex.ToString();
}
}
else
{
dt.Rows.Add(dt.NewRow());
GrdNote.DataSource = dt;
GrdNote.DataBind();
int TotalColumns = GrdNote.Rows[0].Cells.Count;
GrdNote.Rows[0].Cells.Clear();
GrdNote.Rows[0].Cells.Add(new TableCell());
GrdNote.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GrdNote.Rows[0].Cells[0].Text = "No Record Found";
}
Tuple <string, string, string, string> refsEmailInfo = GetReferrersEmail();
ImageButton btnEmail = GrdNote.HeaderRow.FindControl("btnEmail") as ImageButton;
if (refsEmailInfo != null)
{
btnEmail.Visible = true;
((HiddenField)GrdNote.HeaderRow.FindControl("hiddenRefEmail")).Value = refsEmailInfo.Item1;
((HiddenField)GrdNote.HeaderRow.FindControl("hiddenRefName")).Value = refsEmailInfo.Item2;
((HiddenField)GrdNote.HeaderRow.FindControl("hiddenBookingOrg")).Value = refsEmailInfo.Item3;
((HiddenField)GrdNote.HeaderRow.FindControl("HiddenBookingPatientName")).Value = refsEmailInfo.Item4;
}
else
{
btnEmail.Visible = false;
}
DisallowAddEditIfNoPermissions(); // place this after databinding
}
protected void GrdNote_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label lblId = (Label)GrdNote.Rows[e.RowIndex].FindControl("lblId");
DropDownList ddlNoteType = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlNoteType");
DropDownList ddlBodyPart = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlBodyPart");
TextBox txtText = (TextBox)GrdNote.Rows[e.RowIndex].FindControl("txtText");
//DropDownList ddlSite = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlSite");
DropDownList ddlDate_Day = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlDate_Day");
DropDownList ddlDate_Month = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlDate_Month");
DropDownList ddlDate_Year = (DropDownList)GrdNote.Rows[e.RowIndex].FindControl("ddlDate_Year");
DataTable dt = ViewState["noteinfo_data"] as DataTable;
DataRow[] foundRows = dt.Select("note_id=" + lblId.Text);
Note note = NoteDB.Load(foundRows[0]);
DateTime date = GetDate(ddlDate_Day.SelectedValue, ddlDate_Month.SelectedValue, ddlDate_Year.SelectedValue);
NoteDB.Update(Convert.ToInt32(lblId.Text), date, Convert.ToInt32(Session["StaffID"]), Convert.ToInt32(ddlNoteType.SelectedValue), Convert.ToInt32(ddlBodyPart.SelectedValue), txtText.Text, note.Site.SiteID);
// if its a booking note
// email admin so they know if a provider is sabotaging the system (it has happened before)
int loggedInStaffID = Session["StaffID"] == null ? -1 : Convert.ToInt32(Session["StaffID"]);
Booking booking = BookingDB.GetByEntityID(GetFormID());
if (booking != null) // if note is for a booking
{
int thresholdCharacters = 50;
int totalCharactersBefore = note.Text.Trim().Length;
int totalCharactersAfter = txtText.Text.Trim().Length;
int difference = totalCharactersAfter - totalCharactersBefore;
if (totalCharactersBefore > thresholdCharacters && totalCharactersAfter < thresholdCharacters && difference < -20)
{
string mailText = @"This is an administrative email to notify you that notes for a booking may have been deleted.
<u>Logged-in user performing the udate</u>
" + StaffDB.GetByID(loggedInStaffID).Person.FullnameWithoutMiddlename + @"
<u>Original Text (Characters: " + totalCharactersBefore + @")</u>
<font color=""blue"">" + note.Text.Replace(Environment.NewLine, "<br />") + @"</font>
<u>Updated Text (Characters: " + totalCharactersAfter + @")</u>
<font color=""blue"">" + txtText.Text.Replace(Environment.NewLine, "<br />") + @"</font>
<u>Booking details</u>
<table border=""0"" cellpadding=""2"" cellspacing=""2""><tr><td>Booking ID:</td><td>" + booking.BookingID + @"</td></tr><tr><td>Booking Date:</td><td>" + booking.DateStart.ToString("d MMM, yyyy") + " " + booking.DateStart.ToString("h:mm") + (booking.DateStart.Hour < 12 ? "am" : "pm") + @"</td></tr><tr><td>Organisation:</td><td>" + booking.Organisation.Name + @"</td></tr><tr><td>Provider:</td><td>" + booking.Provider.Person.FullnameWithoutMiddlename + @"</td></tr><tr><td>Patient:</td><td>" + (booking.Patient == null ? "" : booking.Patient.Person.FullnameWithoutMiddlename + " [ID:" + booking.Patient.PatientID + "]") + @"</td></tr><tr><td>Status:</td><td>" + booking.BookingStatus.Descr + @"</td></tr></table>
Regards,
Mediclinic
";
bool EnableDeletedBookingsAlerts = Convert.ToInt32(SystemVariableDB.GetByDescr("EnableDeletedBookingsAlerts").Value) == 1;
if (EnableDeletedBookingsAlerts && !Utilities.IsDev())
{
Emailer.AsyncSimpleEmail(
((SystemVariables)Session["SystemVariables"])["Email_FromEmail"].Value,
((SystemVariables)Session["SystemVariables"])["Email_FromName"].Value,
((SystemVariables)Session["SystemVariables"])["AdminAlertEmail_To"].Value,
"Notification that booking notes may have been deleted",
mailText.Replace(Environment.NewLine, "<br />"),
true,
null);
}
}
}
GrdNote.Columns[7].Visible = true;
GrdNote.EditIndex = -1;
FillNoteGrid();
}
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);
}