def write_data(apmnt_check, arg2, doc_check, day_stamp): ''' wites the data, or returns "Doctor Unavailable; please select different time." ''' try: if (apmnt_check != 0 | doc_check != 0): return "Doctor Unavailable; please select a different time." elif (apmnt_check == 0 & doc_check == 0): cursor.execute( 'INSERT INTO appointments (day_stamp, doctor_id, location_id, ' 'apmnt_time, is_canceled)' 'VALUES (?, ?, ?, ?, 0)', (day_stamp, arg2[0], arg2[1], arg2[2])) appointment_id = cursor.lastrowid db.get_db().commit() cursor.close() return appointment_id else: return "Please select a different Time." except Exception as e: return str(e)
def delete_doctors(): ''' Deletes Doctor from the DB... Should probably be handled as a flag instead(?) To prevent any potential abuse from outside forces? ''' try: req_data = request.get_json() try: doctor_id = req_data['doctor_id'] except KeyError as e: return jsonify({'error_detail': 'Missing required field'}), 400 cursor = db.get_db().cursor() cursor.execute("DELETE FROM doctors WHERE id == ?", (doctor_id, )) db.get_db().commit() cursor.close() except Exception as e: return jsonify({'error_detail': str(e)}), 404 return jsonify({"Removed doctor with ID: ": doctor_id}), 200
def cancel_appointment(): ''' Cancels appointments by Appointment ID. Each Appointment ID is unique to each appointment, so each date / time code and location are bound to the unique ID. returns appointment cancelled (and appointment ID of said appointment) Also makes the Appointment available for other requests. ALternatively could be handled by a delete request. ''' try: cursor = db.get_db().cursor() req_data = request.get_json() appointment_id = req_data['appointment_id'] cursor.execute( "UPDATE appointments SET is_canceled = 1 WHERE id == ?", (appointment_id, )) db.get_db().commit() cursor.close() except Exception as e: return jsonify({'error_detail': str(e)}), 404 return jsonify({"Canceled Appointment_id": appointment_id}), 200
def add_doctor(): """ Create a doctor :param first_name: The doctor's first name "param last_name: The doctor's last name :return: The id of the newly created doctor """ req_data = request.get_json() try: first_name = req_data['first_name'] last_name = req_data['last_name'] except KeyError: return jsonify({'error_detail': 'Missing required field'}), 400 try: cursor = db.get_db().cursor() cursor.execute( 'INSERT INTO doctors (first_name, last_name) ' 'VALUES (?, ?)', (first_name, last_name) ) doctor_id = cursor.lastrowid cursor.close() db.get_db().commit() except Exception as e: return jsonify({'error_detail': e.message}), 400 return jsonify({'id': doctor_id}), 200
def set_hours(): try: cursor = db.get_db().cursor() req_data = request.get_json() try: doc_id = req_data['doctor_id'] shift_start = req_data['shift_start'] shift_end = req_data['shift_end'] except KeyError: return jsonify({'error_detail': 'Missing required field'}), 400 cursor.execute( "INSERT INTO doctor_hours(doctor_id, shift_start, shift_end) VALUES (?, ?, ?)", (doc_id, shift_start, shift_end)) db.get_db().commit() doctor_hours_id = cursor.lastrowid cursor.close() except Exception as e: return jsonify({"error detail:": e}), 404 return jsonify({"shift start / end ID: ": doctor_hours_id}), 200
def assign_locations(): try: cursor = db.get_db().cursor() req_data = request.get_json() try: loc_id = req_data['location_id'] doc_id = req_data['doctor_id'] except KeyError: return jsonify({'error_detail': 'Missing required field'}), 400 cursor.execute( "INSERT INTO doctor_locations(doctor_id, location_id) VALUES (?, ?)", (doc_id, loc_id)) db.get_db().commit() doctor_location_id = cursor.lastrowid cursor.close() except Exception as e: return jsonify(str(e)), 404 return jsonify({"doctor_location_id: ": doctor_location_id}), 200
def update_doctor(doctor_id): """ Update doctor information :param doctor_id: The id of the doctor :param (payload) first_name: First name of the doctor :param (payload) last_name: Last name of the doctor :return: 204 if doctor updated """ req_data = request.get_json() try: first_name = req_data['first_name'] last_name = req_data['last_name'] except KeyError: return jsonify({'error_detail': 'Missing required field'}), 400 try: cursor = db.get_db().cursor() cursor.execute( 'UPDATE doctors SET first_name = ?, last_name = ?' ' WHERE id = ?', (first_name, last_name, doctor_id) ) cursor.close() db.get_db().commit() return '', 204 except Exception as e: return jsonify({'error_detail': e.message}), 400
def test_get_close_db(app): with app.app_context(): db = get_db() assert db is get_db() with pytest.raises(sqlite3.ProgrammingError) as e: db.execute('SELECT 1') assert 'closed' in str(e)
def get_doctor_appointments(doctor_id): """ Get all appointments for each doctor by Doctor ID. :return: Full appointments row for the doctor. """ cursor = db.get_db().cursor() result = cursor.execute( 'SELECT d.first_name, d.last_name, l.address, apmnt.apmnt_time ' 'FROM appointments apmnt ' 'INNER JOIN locations l ON apmnt.location_id = l.id ' 'INNER JOIN doctors d ON apmnt.doctor_id = d.id ' 'WHERE apmnt.doctor_id = ?', (doctor_id, )).fetchall() if result is None: return jsonify({'error_detail': 'not found'}), 404 appointments = [ dict(zip([key[0] for key in cursor.description], row)) for row in result ] cursor.close() return jsonify(appointments), 200
def list_doctor_locations(doctor_id): """ Get the locations for a single doctor :param doctor_id: The id of the doctor :return: List of full location rows """ cursor = db.get_db().cursor() result = cursor.execute( 'SELECT l.id, l.address ' 'FROM doctor_locations dl ' 'INNER JOIN locations l ON dl.location_id = l.id ' 'WHERE dl.doctor_id = ?', (doctor_id, )).fetchall() # See https://medium.com/@PyGuyCharles/python-sql-to-json-and-beyond-3e3a36d32853 locations = [ dict(zip([key[0] for key in cursor.description], row)) for row in result ] cursor.close() return jsonify(locations), 200
def list_doctor(doctor_id): """ Get one doctor by doc_id :param doctor_id: The id of the doctor :return: Full doctor row """ try: cursor = db.get_db().cursor() result = cursor.execute( 'SELECT id, first_name, last_name ' 'FROM doctors ' 'WHERE id = ?', (doctor_id, )).fetchone() if result is None: return jsonify({'error_detail': 'Doctor not found'}), 404 # See https://medium.com/@PyGuyCharles/python-sql-to-json-and-beyond-3e3a36d32853 doctor = dict(zip([key[0] for key in cursor.description], result)) cursor.close() except Exception as e: return e return jsonify(doctor), 200
def get_slots(self, doctor_id=None): today = datetime.datetime.utcnow().date() db = get_db() if doctor_id is None: slots = db.execute(SlotsListAPIView.Sql_SelectAll, [today]).fetchall() else: slots = db.execute(SlotsListAPIView.Sql_SelectByDoctorID, [today, doctor_id]).fetchall() return slots
def get_slot(self, id): db = get_db() cursor = db.execute(SlotsAPIView.Sql_GetById, (id, )) slot = cursor.fetchone() if slot is None: abort(404, message=f"slot is with {id} does not exists") if cursor.fetchone() is not None: # http-500 justified ?!! abort(500, message=f"slot is with {id} does not exists") return slot
def app(): """Create and configure a new app instance for each test.""" # create a temporary file to isolate the database for each test db_fd, db_path = tempfile.mkstemp() # create the app with common test config app = create_app({ 'TESTING': True, 'DATABASE': db_path, }) # create the database and load test data with app.app_context(): init_db() get_db().executescript(_data_sql) yield app # close and remove the temporary database os.close(db_fd) os.unlink(db_path)
def add_locations(): try: cursor = db.get_db().cursor() req_data = request.get_json() try: address = req_data['address'] except KeyError: return jsonify({'error_detail': 'Missing required field'}), 400 cursor.execute("INSERT INTO locations(address) VALUES (?)", (address, )) db.get_db().commit() loc_id = cursor.lastrowid cursor.close() except Exception as e: return jsonify(str(e)), 404 return jsonify({"Added Location_ID:": loc_id}), 200
def doc_update(): ''' updates doctor information by doctor ID returns "Updated from (old) to (new)" ''' try: cursor = db.get_db().cursor() req_data = request.get_json() try: doctor_id = req_data['doctor_id'] first_name = req_data['first_name'] last_name = req_data['last_name'] except KeyError as e: return jsonify({'error_detail': 'Missing required field(s)'}), 400 cursor.execute( "UPDATE doctors SET " "first_name = ?, " "last_name = ? " "WHERE id == ?", (first_name, last_name, doctor_id)) db.get_db().commit() update_result = cursor.execute( "SELECT * FROM doctors " "WHERE id = ?", (doctor_id, )).fetchone() doctor_update = dict( zip([key[0] for key in cursor.description], update_result)) cursor.close() return jsonify(doctor_update) except Exception as e: return jsonify({'error_detail': str(e)}), 404
def delete_doctor(doctor_id): """ Delete doctor :param doctor_id: The id of the doctor :return: 204 if successful """ doctor = get_doctor(doctor_id) if doctor is None: return jsonify({'error_detail': 'Doctor not found'}), 404 try: cursor = db.get_db().cursor() cursor.execute( 'DELETE from doctors WHERE id = ?', (doctor_id,) ) cursor.close() db.get_db().commit() return '', 204 except Exception as e: return jsonify({'error_detail': e.message}), 400
def test_delete_doctor(client, app): rv = client.delete('/doctors/1') assert rv.status_code == 204 # But did it really delete it? with app.app_context(): cursor = get_db().cursor() result = cursor.execute( 'SELECT * FROM doctors where id = ?', (1,) ).fetchone() cursor.close() assert result is None
def get_doctor(doctor_id): """ Helper to get doctor :param doctor_id: id of the doctor :return: doctor row """ cursor = db.get_db().cursor() result = cursor.execute( 'SELECT id, first_name, last_name ' 'FROM doctors ' 'WHERE id = ?', (doctor_id,) ).fetchone() cursor.close() return result
def list_doctors(): """ Get all doctors :return: List of full doctor rows """ cursor = db.get_db().cursor() result = cursor.execute( 'SELECT id, first_name, last_name ' 'FROM doctors' ).fetchall() doctors = [dict(row) for row in result] cursor.close() return jsonify(doctors), 200
def list_doctors(): """ Get all doctors :return: List of full doctor rows """ cursor = db.get_db().cursor() result = cursor.execute('SELECT id, first_name, last_name ' 'FROM doctors').fetchall() # See https://medium.com/@PyGuyCharles/python-sql-to-json-and-beyond-3e3a36d32853 doctors = [ dict(zip([key[0] for key in cursor.description], row)) for row in result ] cursor.close() return jsonify(doctors), 200
def post(self): """ curl http://localhost:5000/slots -d '{"start": "03:00:00", "end": "03:30:00", "doctor_locations_id": 1 }' -H "Content-Type:application/json" -X POST """ args = request.json args['start'] = self.string_to_datetime(args['start']) args['end'] = self.string_to_datetime(args['end']) db = get_db() doctor_location = db.execute( """ select * from doctor_locations where id = ? """, [int(args['doctor_locations_id'])]).fetchone() if doctor_location is None: abort(422, message="Invalid (doctor, location) pair!") doctor_id = doctor_location['doctor_id'] slots = self.get_slots(doctor_id) for slot in slots: start = self.string_to_datetime(slot['start']) end = self.string_to_datetime(slot['end']) overlaps = (start <= args['start'] <= end or args['start'] <= start <= args['end']) if overlaps: abort(400, message="New Slot overlaps existing slot") db.execute( """ INSERT INTO slots (start, end, doctor_locations_id) VALUES (?, ?, ?) """, (args['start'].strftime("%H:%M:%S"), args['end'].strftime("%H:%M:%S"), args['doctor_locations_id'])) db.commit() return {'detail': "slot successfully created"}
def update(self, id, booked): db = get_db() db.execute(SlotBookAPI.Sql_BookByID, (booked, id)) db.commit()
def schedule_appointment(): ''' Schedules the appointment checks if the doctor has working hours. Prevents overlapping appointments +/- 15 minutes(14:59, to be exact...) Should update / add field for appointment type; provide a lock on +/- time where the Returns: Appointment ID or Appointment Taken ''' cursor = db.get_db().cursor() #Gets schedule Day / Date def get_day(apmnt_time): try: string_date = datetime.fromtimestamp(apmnt_time).strftime( '%m %d %Y') my_date = datetime.strptime(string_date, '%m %d %Y') tstamp = datetime.timestamp(my_date) except Exception as e: return str(e) return int(tstamp) #Check doctor availability (is the doctor in the office / have working hours) def doc_avail(arg1): try: result = cursor.execute( "SELECT doctor_id, shift_start, shift_end FROM doctor_hours " "WHERE doctor_id == ?", (arg1[0], )).fetchall() doctor_availability = [ dict(zip([key[0] for key in cursor.description], row)) for row in result ] avail_dict_list = list( filter( lambda start_time: start_time['shift_start'] <= arg1[ 2], doctor_availability)) shift_end_time = avail_dict_list[0].get("shift_end", False) if (shift_end_time <= arg1[2]): return 1 elif (shift_end_time >= arg1[2]): return 0 except Exception as e: return str(e) #Checks if there is an appointment scheduled in the next +- 15 minutes (14:59, technically) def check_apmnt(arg1): try: ''' checks for an appointment in the specified range +/- 899 seconds, or 14:59 minutes:seconds. :return 0: if there are no scheduled appointemnts :return 1: if there is a scheudled appointment :return 2: on error. ''' sched_time = arg1[2] offset = 899 ''' Determines block of time to block off ''' time_block_add = sched_time + offset time_block_sub = sched_time - offset time_block_check = cursor.execute( 'SELECT id FROM appointments ' 'WHERE doctor_id == ? ' 'AND apmnt_time BETWEEN ? AND ?', (arg1[0], time_block_sub, time_block_add)).fetchone() if time_block_check is None: return 0 else: return 1 except Exception as e: return str(e) #Writes the Data def write_data(apmnt_check, arg2, doc_check, day_stamp): ''' wites the data, or returns "Doctor Unavailable; please select different time." ''' try: if (apmnt_check != 0 | doc_check != 0): return "Doctor Unavailable; please select a different time." elif (apmnt_check == 0 & doc_check == 0): cursor.execute( 'INSERT INTO appointments (day_stamp, doctor_id, location_id, ' 'apmnt_time, is_canceled)' 'VALUES (?, ?, ?, ?, 0)', (day_stamp, arg2[0], arg2[1], arg2[2])) appointment_id = cursor.lastrowid db.get_db().commit() cursor.close() return appointment_id else: return "Please select a different Time." except Exception as e: return str(e) try: req_data = request.get_json() doc_id = req_data['doctor_id'] loc_id = req_data['location_id'] apmnt_time = int(req_data['apmnt_time']) apmnt_tup = (doc_id, loc_id, apmnt_time) apmnt_check = check_apmnt(apmnt_tup) doc_check = doc_avail(apmnt_tup) apmnt_date = get_day(apmnt_time) appointment_id = write_data(apmnt_check, apmnt_tup, doc_check, apmnt_date) except Exception as e: return jsonify({'error_detail': str(e)}), 404 return jsonify({"Appointment ID: ": appointment_id}), 200
def get_doctor_sched(doctor_id): """ Get all appointments for each doctor by Doctor ID for next week. :return: Full appointments row for the next week. """ cursor = db.get_db().cursor() def get_day(): try: string_date = datetime.today().strftime('%m %d %Y') my_date = datetime.strptime(string_date, '%m %d %Y') tstamp = datetime.timestamp(my_date) except Exception as e: return str(e) return int(tstamp) current_day = get_day() one_week = current_day + 604800 def get_doc_work_hrs(doctor_id): try: doc_work_hours = cursor.execute( 'SELECT dhrs.day_stamp, d.first_name, d.last_name, dhrs.shift_start, dhrs.shift_end ' 'FROM doctor_hours dhrs ' 'INNER JOIN doctors d ON dhrs.doctor_id = d.id ' 'WHERE dhrs.doctor_id = ? ' 'AND dhrs.day_stamp BETWEEN ? AND ?', (doctor_id, current_day, one_week)).fetchall() dhrs = [ dict(zip([key[0] for key in cursor.description], row)) for row in doc_work_hours ] if doc_work_hours is None: return jsonify({'error_detail': 'not found'}), 404 except Exception as e: return str(e) return dhrs def get_doctors_appointments(doctor_id): try: week_appointments = cursor.execute( 'SELECT apmnt.day_stamp, d.first_name, d.last_name, l.address, apmnt.apmnt_time ' 'FROM appointments apmnt ' 'INNER JOIN locations l ON apmnt.location_id = l.id ' 'INNER JOIN doctors d ON apmnt.doctor_id = d.id ' 'WHERE apmnt.doctor_id = ? ' 'AND apmnt.day_stamp BETWEEN ? AND ?', (doctor_id, current_day, one_week)).fetchall() doctors_appointments = [ dict(zip([key[0] for key in cursor.description], row)) for row in week_appointments ] if week_appointments is None: return jsonify({'error_detail': 'not found'}), 404 except Exception as e: return e return doctors_appointments def combine_data(arg1, arg2): try: combined_list = [*arg1, *arg2] except Exception as e: return str(e) return combined_list try: doc_hours = get_doc_work_hrs(doctor_id) doc_apmnts = get_doctors_appointments(doctor_id) result = combine_data(doc_hours, doc_apmnts) cursor.close() except Exception as e: return jsonify({"error detail:": str(e)}), 404 return jsonify(result), 200