def get(self, bike_id): """ Returns all available comments for a bike :param bike_id: specific id for one bike :return: Available bikes """ try: connection = init_db() sql_statement = '''SELECT * FROM comments WHERE bike_id = %s ORDER BY id DESC''' # Get all bikes cursor = connection.cursor() cursor.execute(sql_statement, bike_id) comment_list = cursor.fetchall() if comment_list is (): response_object = {'status': 'fail'} return response_object, 404 else: return comment_list, 200 except KeyError as e: service_conf.abort(500, e.__doc__, status="Could not retrieve information", statusCode="500") except Exception as e: service_conf.abort(400, e.__doc__, status="Could not retrieve information", statusCode="400") finally: cursor.close() connection.close()
def post(self): """ Stores email for newsletter :return: """ new_email = api.payload sql_statement = '''INSERT INTO newsletter_mails (email) VALUES (%s)''' try: connection = init_db() cursor = connection.cursor() cursor.execute(sql_statement, (new_email['email'])) connection.commit() response_object = { 'status': 'success', 'message': 'Successfully inserted.' } return response_object, 200 except Exception as e: response_object = {'status': 'fail'} connection.rollback() action_conf.abort(400, e.__doc__, status="Could not save information", statusCode="400") return response_object, 400 finally: cursor.close() connection.close()
def get(self, bike_id): """ Returns all dates when a bike is reserved or on loan :param bike_id: specific id for one bike :return: """ try: connection = init_db() sql_statement = '''SELECT * FROM bikes WHERE id = %s ''' # Get a specific bike cursor = connection.cursor() cursor.execute(sql_statement, bike_id) bike_list = cursor.fetchone() sql_statement_loan = '''SELECT start_date, end_date FROM loans WHERE Date(start_date) >= CURRENT_DATE() AND Locate(%s, bike_ids) > 0 ''' cursor.execute(sql_statement_loan, str(bike_list['id'])) loan_dates = cursor.fetchall() print(loan_dates) sql_statement_reservation = '''SELECT date_from, date_to FROM reservations WHERE Date(date_from) >= CURRENT_DATE() AND Locate(%s, bike_ids) > 0 ''' cursor.execute(sql_statement_reservation, str(bike_list['id'])) reservation_dates = cursor.fetchall() dates = [] for i in range(0, len(loan_dates)): date = { "start_date": loan_dates[i]["start_date"], "end_date": loan_dates[i]["end_date"] } dates.append(date) for i in range(0, len(reservation_dates)): date = { "start_date": reservation_dates[i]["date_from"], "end_date": reservation_dates[i]["date_to"] } dates.append(date) return dates, 200 except KeyError as e: service_conf.abort(500, e.__doc__, status="Could not retrieve information", statusCode="500") except Exception as e: service_conf.abort(400, e.__doc__, status="Could not retrieve information", statusCode="400") finally: cursor.close() connection.close()
def post(self): """ Stores and sends an reservation-request (Date-Format: dd.mm.YYYY.hh:mm) :return: """ new_reservation = api.payload sql_statement = '''INSERT INTO reservations (date_from, date_to, bike_ids, email) VALUES (%s, %s, %s, %s)''' try: connection = init_db() start_date = convert_date_to_mysql_date( new_reservation['date_from']) end_date = convert_date_to_mysql_date(new_reservation['date_to']) cursor = connection.cursor() cursor.execute(sql_statement, (start_date, end_date, new_reservation['bike_ids'], new_reservation['email'])) connection.commit() response_object = { 'status': 'success', 'message': 'Successfully inserted.' } return response_object, 200 except Exception as e: response_object = {'status': 'fail'} connection.rollback() action_conf.abort(400, e.__doc__, status="Could not save information", statusCode="400") return response_object, 400 finally: cursor.close() connection.close()
def post(self): """ Stores a comment on a bike in the database :return: """ new_comment = api.payload sql_statement = '''INSERT INTO comments (comment, bike_id, email) VALUES (%s, %s, %s)''' try: connection = init_db() cursor = connection.cursor() cursor.execute(sql_statement, (new_comment['comment'], new_comment['bike_id'], new_comment['email'])) connection.commit() response_object = { 'status': 'success', 'message': 'Successfully inserted.' } return response_object, 200 except Exception as e: print(e) exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] print(exc_type, fname, exc_tb.tb_lineno) response_object = {'status': 'fail'} connection.rollback() action_conf.abort(400, e.__doc__, status="Could not save information", statusCode="400") return response_object, 400 finally: cursor.close() connection.close()
def post(self): """ Stores and sends an appointment-request (Date-Format: dd.mm.YYYY.hh:mm) :return: """ new_payload = api.payload sql_statement_appointment = '''INSERT INTO appointments (date_time, type, reservation_id, loan_id) VALUES (%s, %s, %s, %s)''' sql_statement_loan = '''INSERT INTO loans (start_date, end_date, loan_duration, price, email, bike_ids, first_name, last_name, phone_number, deposit, number_of_bikes, birthday, street, location) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''' sql_statement_get_last_loan = '''SELECT * FROM loans ORDER BY id DESC''' try: connection = init_db() date_time = convert_date_to_mysql_date(new_payload['date_time']) start_date = convert_date_to_mysql_date(new_payload['start_date']) end_date = convert_date_to_mysql_date(new_payload['end_date']) cursor = connection.cursor() cursor.execute( sql_statement_loan, (start_date, end_date, new_payload['loan_duration'], new_payload['price'], new_payload['email'], new_payload['bike_ids'], new_payload['first_name'], new_payload['last_name'], new_payload['phone_number'], new_payload['deposit'], new_payload['number_of_bikes'], new_payload['birthday'], new_payload['street'], new_payload['location'])) connection.commit() cursor.execute(sql_statement_get_last_loan) last_loan = cursor.fetchone() if last_loan is (): response_object = {'status': 'fail'} return response_object, 404 loan_id = last_loan['id'] cursor.execute(sql_statement_appointment, (date_time, new_payload['type'], new_payload['reservation_id'], loan_id)) connection.commit() response_object = { 'status': 'success', 'message': 'Successfully inserted.' } return response_object, 200 except Exception as e: response_object = {'status': 'fail'} connection.rollback() action_conf.abort(400, e.__doc__, status="Could not save information", statusCode="400") return response_object, 400 finally: cursor.close() connection.close()