// Store the new appointment in the database
public static bool AddAppointment(Appointment newAppointment)
{
Database db = Database.Instance();
if (db.OpenConnection())
{
if (!AppointmentExists(newAppointment))
{
// Build the query string
String newAppointmentQuery;
String sqlFormattedStartDate = newAppointment.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String sqlFormattedEndDate = newAppointment.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Create the new appointment query
newAppointmentQuery = "INSERT INTO appointments VALUES(NULL,'" + newAppointment.PatientID + "'," +
"'" + newAppointment.StaffID + "'," +
"'" + sqlFormattedStartDate + "'," +
"'" + sqlFormattedEndDate + "', NULL);";
// Insert the appointment into the database
db.Insert(newAppointmentQuery);
// Close the connection
db.CloseConnection();
return true;
}
db.CloseConnection();
}
return false;
}
// Create a new appointment and pass it to the meta layer for insertion into the database
public bool AddAppointment(DateTime startDate, DateTime endDate, Staff staff, Patient patient)
{
Appointment newAppointment = new Appointment();
newAppointment.PatientID = patient.PatientID;
newAppointment.StaffID = staff.StaffID;
newAppointment.StartDate = startDate;
newAppointment.EndDate = endDate;
return BusinessMetaLayer.AddAppointment(newAppointment);
}
public void TestCancelAppointmentSuccess()
{
// Arrange
AdminController ac = new AdminController();
bool expected = true;
Appointment app = new Appointment();
app.AppointmentID = 1;
app.PatientID = 1;
app.StaffID = 1;
app.StartDate = new DateTime(2000, 1, 1, 9, 0, 0);
app.EndDate = new DateTime(2000, 1, 1, 9, 15, 0);
// Act
bool actual = ac.CancelAppointment(app);
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSaveAppointmentNotesFail()
{
// Arrange
MedicalController mc = new MedicalController();
bool expected = false;
Appointment app = new Appointment();
app.AppointmentID = 1;
app.PatientID = 10;
app.StaffID = 2;
app.StartDate = new DateTime(2000, 1, 1, 9, 0, 0);
app.EndDate = new DateTime(2000, 1, 1, 9, 15, 0);
// Act
bool actual = mc.SaveAppointmentNotes(app, "test");
// Assert
Assert.AreEqual(expected, actual);
}
public FormAppointmentDetails(Appointment appointment)
{
InitializeComponent();
_adminController = new AdminController();
_appointment = appointment;
// Start times of the day
TimeSpan startTime = new TimeSpan(9, 0, 0);
TimeSpan endTime = new TimeSpan(9, 15, 0);
// Populate form fields
PopulateFields();
PopulateComboBox(startTimeCmb, startTime, _appointment.StartDate);
PopulateComboBox(endTimeCmb, endTime, _appointment.EndDate);
// Allow editing if the appointment is in the future
if (_appointment.StartDate > DateTime.Now)
{
//modifyAppointmentBtn.Enabled = true;
cancelAppointmentBtn.Enabled = true;
}
}
public void AppointmentClicked(AppointmentLabel sender, EventArgs e)
{
AppointmentLabel ap = sender;
_appointment = ap.Appointment;
if (ap.Appointment != null)
{
_patient = ap.Appointment.Patient;
// Fill in form details
patientNameTxt.Text = _patient.ToString();
dateOfBirthTxt.Text = _patient.DateOfBirth.ToShortDateString();
startTxt.Text = _appointment.StartDate.ToShortTimeString();
endTxt.Text = _appointment.EndDate.ToShortTimeString();
appNotesTxt.Text = _appointment.AppointmentNotes;
appNotesTxt.ReadOnly = true;
// Change form fields
addTestBtn.Enabled = true;
if (_appointment.StartDate >= DateTime.Now)
{
addPrescriptionBtn.Enabled = true;
appNotesTxt.ReadOnly = false;
saveAppointmentBtn.Enabled = true;
}
FormRefresh();
}
else
{
patientNameTxt.Text = "";
startTxt.Text = "";
endTxt.Text = "";
}
}
public bool SaveAppointmentNotes(Appointment appointment, string notes)
{
return BusinessMetaLayer.SaveAppointmentNotes(appointment, EncodeMySql(notes));
}
// Cancel an already existing appointment by passing it to the business meta layer
public bool CancelAppointment(Appointment appointment)
{
return BusinessMetaLayer.CancelAppointment(appointment);
}
// Get a list of appointments for a member of staff on a set day
public static List<Appointment> GetStaffAvailability(Staff staff, DateTime date)
{
Database db = Database.Instance();
List<Appointment> _appointments = new List<Appointment>();
if (db.OpenConnection())
{
String query;
String sqlFormattedDate = date.Date.ToString("yyyy-MM-dd HH:mm:ss");
query = "SELECT apps.* FROM appointments apps " +
"INNER JOIN staff s " +
"ON s.staffID=apps.staffID " +
"WHERE s.staffID=" + staff.StaffID + " AND (s.role=" + (int)PermissionsFlag.Doctor + " OR s.role=" + (int) PermissionsFlag.Nurse + ") AND date(apps.date)='" + sqlFormattedDate + "';";
DbDataReader dr = db.Select(query);
// Create the staff data
// Read the data and store them in the list
while (dr.Read())
{
Appointment newAppointment = new Appointment();
newAppointment.AppointmentID = dr.GetInt32(0);
newAppointment.PatientID = dr.GetInt32(1);
newAppointment.StaffID = dr.GetInt32(2);
newAppointment.StartDate = dr.GetDateTime(3);
newAppointment.EndDate = dr.GetDateTime(4);
newAppointment.AppointmentNotes = dr.IsDBNull(5) ? "" : dr.GetString(5);
_appointments.Add(newAppointment);
}
dr.Close();
db.CloseConnection();
}
return _appointments;
}
// Returns all appointments for one member of staff
public static List<Appointment> GetStaffAppointments(int staffID)
{
Database db = Database.Instance();
List<Appointment> _appointments = new List<Appointment>();
if (db.OpenConnection())
{
String query;
query = "SELECT apps.* FROM appointments apps " +
"INNER JOIN staff s " +
"ON s.staffID=apps.staffID " +
"WHERE s.staffID=" + staffID +
";";
DbDataReader dr = db.Select(query);
// Create the staff data
// Read the data and store them in the list
while (dr.Read())
{
Appointment newAppointment = new Appointment();
newAppointment.AppointmentID = dr.GetInt32(0);
newAppointment.PatientID = dr.GetInt32(1);
newAppointment.StaffID = dr.GetInt32(2);
newAppointment.StartDate = dr.GetDateTime(3);
newAppointment.EndDate = dr.GetDateTime(4);
newAppointment.AppointmentNotes = dr.IsDBNull(5) ? "" : dr.GetString(5);
_appointments.Add(newAppointment);
}
dr.Close();
db.CloseConnection();
}
return _appointments;
}
// Cancel a stored appointment if it already exists
public static bool CancelAppointment(Appointment appointment)
{
Database db = Database.Instance();
if (db.OpenConnection())
{
if (AppointmentExists(appointment))
{
// Build the query string
String cancelAppointmentQuery;
String sqlFormattedStartDate = appointment.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String sqlFormattedEndDate = appointment.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Create the new appointment query
cancelAppointmentQuery = "DELETE FROM appointments WHERE patientID=" + appointment.PatientID +
" and date='" + sqlFormattedStartDate + "'" +
" and endDate='" + sqlFormattedEndDate + "'";
// Insert the appointment into the database
db.Delete(cancelAppointmentQuery);
// Close the connection
db.CloseConnection();
return true;
}
db.CloseConnection();
}
return false;
}
// Checks if an appointment already exists
public static bool AppointmentExists(Appointment appointment)
{
Database db = Database.Instance();
// Format the appointment start and end time for mysql
String sqlFormattedStartDate = appointment.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String sqlFormattedEndDate = appointment.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Check the number of rows that are returned
int numRows = db.Count("SELECT COUNT(*) FROM appointments WHERE patientID='" + appointment.PatientID +
"'and staffID='" + appointment.StaffID +
"'and date='" + sqlFormattedStartDate +
"'and endDate='" + sqlFormattedEndDate + "';");
// If an appointment exists and there's one row
if (numRows > 0)
{
// The appointment exists already
return true;
}
return false;
}
// Add the notes for the appointment to the database
public static bool SaveAppointmentNotes(Appointment appointment, String notes)
{
Database db = Database.Instance();
if (db.OpenConnection())
{
if (AppointmentExists(appointment))
{
// Build the query string
String updateAppointmentQuery;
// Create the new appointment query
updateAppointmentQuery = "UPDATE appointments SET appointmentNotes='" + notes + "' " +
"WHERE appointmentID=" + appointment.AppointmentID + ";";
// Insert the appointment into the database
db.Delete(updateAppointmentQuery);
// Close the connection
db.CloseConnection();
return true;
}
db.CloseConnection();
}
return false;
}
