private void PopulateStaffComboBox()
{
staff.Clear();
staffcomboBox1.Items.Clear();
// Get day from the DateTime picker
Column dayColumn = null;
switch (dateTimePicker1.Value.DayOfWeek)
{
case DayOfWeek.Monday: dayColumn = Tables.ROTA_TABLE.Mon; break;
case DayOfWeek.Tuesday: dayColumn = Tables.ROTA_TABLE.Tue; break;
case DayOfWeek.Wednesday: dayColumn = Tables.ROTA_TABLE.Wed; break;
case DayOfWeek.Thursday: dayColumn = Tables.ROTA_TABLE.Thur; break;
case DayOfWeek.Friday: dayColumn = Tables.ROTA_TABLE.Fri; break;
case DayOfWeek.Saturday: dayColumn = Tables.ROTA_TABLE.Sat; break;
case DayOfWeek.Sunday: dayColumn = Tables.ROTA_TABLE.Sun; break;
}
QueryBuilder b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.ID, Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.STAFF_TABLE.JobRole)
.From(Tables.STAFF_TABLE, Tables.ROTA_TABLE)
.Where(
b.IsEqual(Tables.STAFF_TABLE.ID, Tables.ROTA_TABLE.StaffID),
b.And(),
b.IsEqual(dayColumn, 1));
CustomTableFactory ctf = new CustomTableFactory(dbCon);
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
Staff s = new Staff();
s.ID = int.Parse(row[Tables.STAFF_TABLE.ID].ToString());
s.FirstName = row[Tables.STAFF_TABLE.FirstName].ToString();
s.LastName = row[Tables.STAFF_TABLE.LastName].ToString();
s.JobRole = row[Tables.STAFF_TABLE.JobRole].ToString();
if (s.JobRole == "Doctor" || s.JobRole == "Nurse")
{
staff.Add(s);
}
}
foreach (Staff s in staff)
{
staffcomboBox1.Items.Add(string.Format("{0} {1}, {2}", s.FirstName, s.LastName, s.JobRole));
}
}
private void PopulateRotaRows()
{
CustomTableFactory ctf = new CustomTableFactory(dbCon);
QueryBuilder b = new QueryBuilder();
Column dayColumn = GetDayColumnForDay(date.DayOfWeek);
b.Select(Tables.STAFF_TABLE.ID, Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.STAFF_TABLE.JobRole).From(Tables.STAFF_TABLE, Tables.ROTA_TABLE).Where(
b.IsEqual(Tables.STAFF_TABLE.ID, Tables.ROTA_TABLE.StaffID), b.And(), b.IsEqual(dayColumn, 1)
);
CustomTable ct = ctf.GetCustomTable(b);
rotaRows = ct.GetRows();
}
private void PopulateDataGrid()
{
QueryBuilder b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.STAFF_TABLE.JobRole,
Tables.ROTA_TABLE.Mon, Tables.ROTA_TABLE.Tue, Tables.ROTA_TABLE.Wed, Tables.ROTA_TABLE.Thur, Tables.ROTA_TABLE.Fri, Tables.ROTA_TABLE.Sat, Tables.ROTA_TABLE.Sun)
.From(Tables.ROTA_TABLE, Tables.STAFF_TABLE)
.Where(b.IsEqual(Tables.STAFF_TABLE.ID, Tables.ROTA_TABLE.StaffID), b.And(),
b.IsEqual(Tables.STAFF_TABLE.FirstName, firstName), b.And(), b.IsEqual(Tables.STAFF_TABLE.LastName, lastName));
CustomTableFactory ctf = new CustomTableFactory(dbCon);
CustomTable ct = ctf.GetCustomTable(b);
if (ct.GetRows().Count == 0)
{
MessageBox.Show("No staff member found using those names.", "No Staff Member");
this.Close();
this.Dispose();
return;
}
Dictionary <Tables.Column, object> row = ct.GetRows()[0];
List <string> resultRow = new List <string>();
foreach (Column c in b.GetSelectedColumns())
{
string s = "";
try
{
int i = int.Parse(row[c].ToString());
s = (i == 1) ? "On" : "Off";
}catch (FormatException e)
{
s = row[c].ToString();
}
resultRow.Add(s);
}
dataGridView1.Rows.Add(resultRow.ToArray());
}
/// <summary>
/// Sets the next available staff ID.
/// </summary>
private void GetLatestStaffID()
{
QueryBuilder b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.ID).From(Tables.STAFF_TABLE).OrderBy(true, Tables.STAFF_TABLE.ID).Limit(1);
CustomTableFactory ctf = new CustomTableFactory(dbCon);
CustomTable ct = ctf.GetCustomTable(b);
int id = GetInt(ct.GetRows()[0][Tables.STAFF_TABLE.ID]);
if (id > nextAvailableStaffID)
{
nextAvailableStaffID = id;
}
}
private void LoadDataGrid()
{
//select M.scientificName, M.commercialName, M.manufacturer, MI.instructions FROM Medication M, MedicationInstance MI WHERE M.id = MI.medicationID AND MI.prescriptionID = 159;
CustomTableFactory ctf = new CustomTableFactory(dbCon);
QueryBuilder b = new QueryBuilder();
b.Select(Tables.MEDICATION_TABLE.ScientificName, Tables.MEDICATION_TABLE.CommercialName, Tables.MEDICATION_TABLE.Manufacturer, Tables.MEDICATIONINSTANCE_TABLE.Instructions)
.From(Tables.MEDICATION_TABLE, Tables.MEDICATIONINSTANCE_TABLE)
.Where(b.IsEqual(Tables.MEDICATION_TABLE.ID, Tables.MEDICATIONINSTANCE_TABLE.MedicationID), b.And(), b.IsEqual(Tables.MEDICATIONINSTANCE_TABLE.PrescriptionID, prescriptionID));
CustomTable table = ctf.GetCustomTable(b);
foreach (Dictionary <Column, object> row in table.GetRows())
{
dataGridView1.Rows.Add(row[MEDICATION_TABLE.ScientificName], row[MEDICATION_TABLE.CommercialName], row[MEDICATION_TABLE.Manufacturer], row[MEDICATIONINSTANCE_TABLE.Instructions]);
}
}
private void PopulateAdminFormRota()
{
dataGridView1.Rows.Clear();
CustomTableFactory ctf = new CustomTableFactory(dbCon);
b = new QueryBuilder();
b.Select(Tables.ROTA_TABLE.StaffID, Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.STAFF_TABLE.JobRole,
Tables.ROTA_TABLE.Mon, Tables.ROTA_TABLE.Tue, Tables.ROTA_TABLE.Wed,
Tables.ROTA_TABLE.Thur, Tables.ROTA_TABLE.Fri, Tables.ROTA_TABLE.Sat, Tables.ROTA_TABLE.Sun)
.From(Tables.ROTA_TABLE, Tables.STAFF_TABLE).Where(b.IsEqual(Tables.STAFF_TABLE.ID, Tables.ROTA_TABLE.StaffID));
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
dataGridView1.Rows.Add(row.Values.ToArray());
}
}
private void PopulateAdminMessage()
{
CustomTableFactory ctf = new CustomTableFactory(dbCon);
b = new QueryBuilder();
b.Select(Tables.PATIENT_TABLE.ID, Tables.PATIENT_TABLE.FirstName, Tables.PATIENT_TABLE.LastName, Tables.PATIENT_TABLE.Email).From(Tables.PATIENT_TABLE);
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
foreach (var value in row.Values)
{
Console.Write(value + " | ");
}
dataGridView1.Rows.Add(row.Values.ToArray());
Log.WriteLine();
}
}
private void populateUpdateStaff()
{
CustomTableFactory ctf = new CustomTableFactory(dbCon);
b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.ID, Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName).From(Tables.STAFF_TABLE);
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
foreach (var value in row.Values)
{
//Console.Write(value + " | ");
}
dataGridView1.Rows.Add(row.Values.ToArray());
Log.WriteLine();
}
}
/// <summary>
/// Populates the appointment <see cref="System.Windows.Forms.DataGridView"/> pulling appointments
/// that are between the two given dates.
/// </summary>
/// <param name="d1">The 'from' date.</param>
/// <param name="d2">The 'to' date.</param>
public void PopulateAppointments(DateTime d1, DateTime d2)
{
appointmentDataGridView.Rows.Clear();
selectedAppointments.Clear();
selectedAppointments = af.GetAppointmentsByDateRange(d1, d2);
//DateTime today = DateTime.Today;
//DateTime date = new DateTime(today.Year, today.Month, today.Day);
//DateTime date2 = date.AddDays(1);
DateTime date = new DateTime(d1.Year, d1.Month, d1.Day);
DateTime date2 = new DateTime(d2.Year, d2.Month, d2.Day);;
string date1String = date.ToString("yyyy-MM-dd");
string date2String = date2.ToString("yyyy-MM-dd");
CustomTableFactory ctf = new CustomTableFactory(dbCon);
//QueryBuilder b = new QueryBuilder();
//b.Select(Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.PATIENT_TABLE.FirstName, Tables.PATIENT_TABLE.LastName, Tables.APPOINTMENT_TABLE.AppointmentDate, Tables.APPOINTMENT_TABLE.AppointmentTime)
// .From(Tables.STAFF_TABLE, Tables.PATIENT_TABLE, Tables.APPOINTMENT_TABLE)
// .Where(b.IsEqual(Tables.APPOINTMENT_TABLE.StaffID, Tables.STAFF_TABLE.ID), b.And(),
// b.IsEqual(Tables.APPOINTMENT_TABLE.PatientID, Tables.PATIENT_TABLE.ID), b.And(),
// b.IsMoreThanEqual(Tables.APPOINTMENT_TABLE.AppointmentDate,date1String),b.And(),
// b.IsLessThan(Tables.APPOINTMENT_TABLE.AppointmentDate,date2String));
QueryBuilder b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.PATIENT_TABLE.FirstName, Tables.PATIENT_TABLE.LastName, Tables.APPOINTMENT_TABLE.AppointmentDate, Tables.APPOINTMENT_TABLE.AppointmentTime, Tables.APPOINTMENT_TABLE.Status, Tables.APPOINTMENT_TABLE.Cause)
.From(Tables.STAFF_TABLE, Tables.PATIENT_TABLE, Tables.APPOINTMENT_TABLE)
.Where(b.IsEqual(Tables.APPOINTMENT_TABLE.StaffID, Tables.STAFF_TABLE.ID), b.And(),
b.IsEqual(Tables.APPOINTMENT_TABLE.PatientID, Tables.PATIENT_TABLE.ID), b.And(),
b.IsBetweenDate(Tables.APPOINTMENT_TABLE.AppointmentDate, date, date2));
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
foreach (var value in row.Values)
{
Console.Write(value + " | ");
}
appointmentDataGridView.Rows.Add(row.Values.ToArray());
Log.WriteLine();
}
}
private void PopulateAdminFormStaff()
{
dataGridView2.Rows.Clear();
CustomTableFactory ctf = new CustomTableFactory(dbCon);
b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.ID, Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.STAFF_TABLE.JobRole,
Tables.STAFF_TABLE.Email, Tables.STAFF_TABLE.Address, Tables.STAFF_TABLE.Postcode).From(Tables.STAFF_TABLE);
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
foreach (var value in row.Values)
{
Console.Write(value + " | ");
}
dataGridView2.Rows.Add(row.Values.ToArray());
Log.WriteLine();
}
}
public void PopulateAppointments(string firstName, string lastName)
{
if (infoFac.GetPatientsByName(firstName, lastName).Count == 0)
{
MessageBox.Show("There are no patients matching that name. Please make sure your spelling is correct.", "No Patients Found");
return;
}
Patient p = infoFac.GetPatientsByName(firstName, lastName)[0];
int patientID = p.ID;
selectedAppointments.Clear();
appointmentDataGridView.Rows.Clear();
QueryBuilder b2 = new QueryBuilder();
b2.Select(Tables.ALL).From(Tables.APPOINTMENT_TABLE).Where(b2.IsEqual(Tables.APPOINTMENT_TABLE.PatientID, patientID));
selectedAppointments = af.GetAppointments(b2);
CustomTableFactory ctf = new CustomTableFactory(dbCon);
QueryBuilder b = new QueryBuilder();
b.Select(Tables.STAFF_TABLE.FirstName, Tables.STAFF_TABLE.LastName, Tables.PATIENT_TABLE.FirstName, Tables.PATIENT_TABLE.LastName, Tables.APPOINTMENT_TABLE.AppointmentDate, Tables.APPOINTMENT_TABLE.AppointmentTime, Tables.APPOINTMENT_TABLE.Status, Tables.APPOINTMENT_TABLE.Cause)
.From(Tables.STAFF_TABLE, Tables.PATIENT_TABLE, Tables.APPOINTMENT_TABLE)
.Where(b.IsEqual(Tables.APPOINTMENT_TABLE.StaffID, Tables.STAFF_TABLE.ID), b.And(),
b.IsEqual(Tables.APPOINTMENT_TABLE.PatientID, Tables.PATIENT_TABLE.ID), b.And(),
b.IsEqual(Tables.PATIENT_TABLE.FirstName, firstName), b.And(),
b.IsEqual(Tables.PATIENT_TABLE.LastName, lastName));
CustomTable ct = ctf.GetCustomTable(b);
foreach (var row in ct.GetRows())
{
foreach (var value in row.Values)
{
//Console.Write(value + " | ");
}
appointmentDataGridView.Rows.Add(row.Values.ToArray());
//Log.WriteLine();
}
}