/** * Method to read operators from the database */ public async Task <List <Operator> > readOperators() { try { string query = $@" SELECT role.id as roleId, role.name as roleName, user.id as userId, user.status as userStatus, user.email as userEmail, operator.id as operatorId, operator.first_name as firstName, operator.last_name as lastName, operator.date_of_birth as dateOfBirth FROM {DBTables.OPERATOR} as operator INNER JOIN {DBTables.USER} as user ON {DBTables.OPERATOR}.user = {DBTables.USER}.id INNER JOIN {DBTables.ROLE} as role ON {DBTables.USER}.role = {DBTables.ROLE}.id"; MySqlConnection connection = new MySqlConnection(DB.connectionString); connection.Open(); MySqlCommand cmd = new MySqlCommand(query, connection); cmd.Prepare(); DbDataReader reader = await cmd.ExecuteReaderAsync(); List <Operator> operators = new List <Operator>(); while (reader.Read()) { int roleId = reader.GetInt32(reader.GetOrdinal("roleId")); string roleName = reader.GetString(reader.GetOrdinal("roleName")); int userId = reader.GetInt32(reader.GetOrdinal("userId")); int userStatus = reader.GetInt32(reader.GetOrdinal("userStatus")); string userEmail = reader.GetString(reader.GetOrdinal("userEmail")); int operatorId = reader.GetInt32(reader.GetOrdinal("operatorId")); string firstName = reader.GetString(reader.GetOrdinal("firstName")); string lastName = reader.GetString(reader.GetOrdinal("lastName")); DateTime dob = reader.GetDateTime(reader.GetOrdinal("dateOfBirth")); Role currentRole = new Role(roleId, roleName); User currentUser = new User(userId, currentRole, userStatus, userEmail, null); Operator currentOperator = new Operator( currentUser, operatorId, firstName, lastName, dob ); operators.Add(currentOperator); } return(operators); } catch (Exception e) { throw e; } }
/** * Method to read reservations from the database */ public async Task <List <Reservation> > readReservations() { try { string query = $@" SELECT reservation.id as reservationId, reservation.start_datetime as reservationStart, reservation.end_datetime as reservationEnd, service.id as serviceId, service.name as serviceName, service.fee as serviceFee, service.description as serviceDescription, operator.id as operatorId, operator.first_name as operatorFirstName, operator.last_name as operatorLastName, patient.id as patientId, patient.first_name as patientFirstName, patient.last_name as patientLastName, nurse.id as nurseId, nurseEmployee.first_name as nurseFirstName, nurseEmployee.last_name as nurseLastName, doctor.id as doctorId, doctorEmployee.first_name as doctorFirstName, doctorEmployee.last_name as doctorLastName FROM {DBTables.RESERVATION} as reservation INNER JOIN {DBTables.SERVICE} as service ON {DBTables.RESERVATION}.service = {DBTables.SERVICE}.id INNER JOIN {DBTables.OPERATOR} as operator ON {DBTables.RESERVATION}.created_by = {DBTables.OPERATOR}.id INNER JOIN {DBTables.PATIENT} as patient ON {DBTables.RESERVATION}.patient = {DBTables.PATIENT}.id INNER JOIN ( {DBTables.NURSE} as nurse INNER JOIN {DBTables.EMPLOYEE} as nurseEmployee ON {DBTables.NURSE}.employee = nurseEmployee.id ) ON {DBTables.RESERVATION}.nurse = {DBTables.NURSE}.id INNER JOIN ( {DBTables.DOCTOR} as doctor INNER JOIN {DBTables.EMPLOYEE} as doctorEmployee ON {DBTables.DOCTOR}.employee = doctorEmployee.id ) ON {DBTables.RESERVATION}.doctor = {DBTables.DOCTOR}.id"; MySqlConnection connection = new MySqlConnection(DB.connectionString); connection.Open(); MySqlCommand cmd = new MySqlCommand(query, connection); cmd.Prepare(); DbDataReader reader = await cmd.ExecuteReaderAsync(); List <Reservation> reservations = new List <Reservation>(); while (reader.Read()) { Service currentService = new Service( reader.GetInt32(reader.GetOrdinal("serviceId")), reader.GetString(reader.GetOrdinal("serviceName")), reader.GetInt32(reader.GetOrdinal("serviceFee")), reader.GetString(reader.GetOrdinal("serviceDescription")) ); Operator currentOperator = new Operator( null, reader.GetInt32(reader.GetOrdinal("operatorId")), reader.GetString(reader.GetOrdinal("operatorFirstName")), reader.GetString(reader.GetOrdinal("operatorLastName")), DateTime.Now ); Patient currentPatient = new Patient( reader.GetInt32(reader.GetOrdinal("patientId")), reader.GetString(reader.GetOrdinal("patientFirstName")), reader.GetString(reader.GetOrdinal("patientLastName")), "", "", DateTime.Now, "" ); Employee nurseEmployee = new Employee( -1, reader.GetString(reader.GetOrdinal("nurseFirstName")), reader.GetString(reader.GetOrdinal("nurseLastName")), "", "", DateTime.Now, null ); Nurse currentNurse = new Nurse( reader.GetInt32(reader.GetOrdinal("nurseId")), nurseEmployee ); Employee doctorEmployee = new Employee( -1, reader.GetString(reader.GetOrdinal("doctorFirstName")), reader.GetString(reader.GetOrdinal("doctorLastName")), "", "", DateTime.Now, null ); Doctor currentDoctor = new Doctor( reader.GetInt32(reader.GetOrdinal("doctorId")), doctorEmployee, "" ); Reservation currentReservation = new Reservation( reader.GetInt32(reader.GetOrdinal("reservationId")), reader.GetDateTime(reader.GetOrdinal("reservationStart")), reader.GetDateTime(reader.GetOrdinal("reservationEnd")), currentService, currentOperator, currentPatient, currentNurse, currentDoctor ); reservations.Add(currentReservation); } return(reservations); } catch (Exception e) { throw e; } }