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); }