public IEnumerable <SingleOperationViewModel> GetFilteredOperations(Operation filters)
{
List <SingleOperationViewModel> list = new List <SingleOperationViewModel>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
string staffQuery = "";
if (filters.staffIDs != null)
{
string staffNumbers = filters.staffIDs[0].ToString();
for (int i = 1; i < filters.staffIDs.Count(); i++)
{
staffNumbers = staffNumbers + "," + filters.staffIDs[i].ToString();
}
staffNumbers = "(" + staffNumbers + ")";
staffQuery = "AND operation.operationID in ( select operations_staff.operationID FROM operations_staff WHERE operations_staff.staffID in " + staffNumbers + " group by operations_staff.operationID having count(operation.operationID)=" + filters.staffIDs.Count().ToString() + ")";
}
cmd.CommandText =
"select operation.operationID,hospital.name AS 'Hospital Name',hospital_operating_room.roomNO,operation.dateStamp,patient.firstName AS 'Patients first name',patient.lastName AS 'Patients last name',patient.patientID" +
" from operation inner join hospital ON operation.hospitalID = hospital.hospitalID" +
" inner join hospital_operating_room ON operation.roomNO = hospital_operating_room.roomNO" +
" inner join patient ON operation.patientID = patient.patientID" +
" where (operation.hospitalID=?hospitalID OR ?hospitalID=0) AND (operation.roomNO=?roomNO OR ?roomNO IS NULL) AND (operation.dateStamp>?fromDate OR ?fromDate IS NULL)" +
" AND (operation.dateStamp<?toDate OR ?toDate IS NULL) AND (operation.patientID=?patientID OR ?patientID=0) " + staffQuery;
cmd.Parameters.AddWithValue("?hospitalID", (filters.hospitalID != 0) ? filters.hospitalID : 0);
cmd.Parameters.AddWithValue("?roomNO", (filters.roomNO != null) ? filters.roomNO : null);
cmd.Parameters.AddWithValue("?fromDate", (filters.fromDate != new DateTime()) ? filters.fromDate.ToString("yyyy-MM-dd HH:mm:ss") : null);
cmd.Parameters.AddWithValue("?toDate", (filters.toDate != new DateTime()) ? filters.toDate.ToString("yyyy-MM-dd HH:mm:ss") : null);
cmd.Parameters.AddWithValue("?patientID", (filters.patientID != 0) ? filters.patientID : 0);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SingleOperationViewModel operation = new SingleOperationViewModel();
operation.date = (DateTime)reader.GetMySqlDateTime("dateStamp");
operation.hospitalName = reader.GetString("Hospital Name");
operation.operationID = reader.GetInt64("operationID");
operation.patient = new Patient();
operation.patient.firstName = reader.GetString("Patients first name");
operation.patient.lastName = reader.GetString("Patients last name");
operation.patient.patientID = reader.GetInt64("patientID");
operation.roomNO = reader.GetString("roomNO");
operation.staff = GetStaffForOperationID(operation.operationID);
list.Add(operation);
}
}
}
return(list);
}
public SingleOperationView(
OperationModel model)
{
DataContext = new SingleOperationViewModel(
model,
this);
InitializeComponent();
}
public IActionResult Details(long id)
{
SingleOperationViewModel model = _context.GetFullDetailsOfOperation(id);
if (model == null)
{
return(RedirectToAction("Index"));
}
return(View(model));
}
public SingleOperationViewModel GetFullDetailsOfOperation(long id)
{
SingleOperationViewModel operation = new SingleOperationViewModel();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(
"select operation.operationID, operation.duration_ms, hospital.name AS 'Hospital Name', hospital_operating_room.roomNO, operation.dateStamp, patient.firstName AS 'Patients first name', patient.lastName AS 'Patients last name', patient.patientID, type_of_operation.description" +
" from operation inner join hospital ON operation.hospitalID = hospital.hospitalID" +
" inner join hospital_operating_room ON operation.roomNO = hospital_operating_room.roomNO" +
" inner join patient ON operation.patientID = patient.patientID" +
" inner join type_of_operation ON operation.operationTypeID = type_of_operation.operationTypeID" +
" where operation.operationID='" + id + "'", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
operation.audioFiles = GetAudiosForOperationID(id);
operation.staff = GetStaffForOperationID(id);
operation.videoFiles = GetVideosForOperationID(id);
operation.patientsMonitoringFile = GetMonitoringFileForOperationID(id);
operation.date = (DateTime)reader.GetMySqlDateTime("dateStamp");
operation.hospitalName = reader.GetString("Hospital Name");
operation.operationID = reader.GetInt64("operationID");
operation.patient = new Patient();
operation.patient.firstName = reader.GetString("Patients first name");
operation.patient.lastName = reader.GetString("Patients last name");
operation.patient.patientID = reader.GetInt64("patientID");
operation.roomNO = reader.GetString("roomNO");
operation.type = reader.GetString("description");
operation.duration = (double)reader.GetInt64("duration_ms") / 1000 / 60;
}
}
}
return(operation);
}
public IEnumerable <SingleOperationViewModel> Get20MostRecentOperations()
{
List <SingleOperationViewModel> list = new List <SingleOperationViewModel>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(
"select twentyoperations.operationID,hospital.name AS 'Hospital Name',uploadedDate,hospital_operating_room.roomNO,twentyoperations.dateStamp,patient.firstName AS 'Patients first name',patient.lastName AS 'Patients last name',patient.patientID " +
" from twentyoperations inner join hospital ON twentyoperations.hospitalID = hospital.hospitalID "+
" inner join hospital_operating_room ON twentyoperations.roomNO = hospital_operating_room.roomNO" +
" inner join patient ON twentyoperations.patientID = patient.patientID ", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SingleOperationViewModel operation = new SingleOperationViewModel();
operation.date = (DateTime)reader.GetMySqlDateTime("dateStamp");
operation.date = (DateTime)reader.GetMySqlDateTime("uploadedDate");
operation.hospitalName = reader.GetString("Hospital Name");
operation.operationID = reader.GetInt64("operationID");
operation.patient = new Patient();
operation.patient.firstName = reader.GetString("Patients first name");
operation.patient.lastName = reader.GetString("Patients last name");
operation.patient.patientID = reader.GetInt64("patientID");
operation.roomNO = reader.GetString("roomNO");
operation.staff = GetStaffForOperationID(operation.operationID);
list.Add(operation);
}
}
}
List <SingleOperationViewModel> SortedList = list.OrderBy(o => o.uploadedDate).ToList();
return(SortedList);
}
