def update_user(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) name = p.get("name", None) surname = p.get("surname", None) email = p.get("email", None) birth_date = p.get("birth_date", None) address = p.get("address", None) subscription = p.get("subscription", None) end_subscription = p.get("end_subscription", None) check_mandatory_parameters([ id_user, name, surname, email, birth_date, address, subscription, end_subscription ], [ "id", "search", "surname", "email", "birth_date", "address", "subscription", "end_subscription" ]) # execute the query query_update = "UPDATE user " \ "SET name=?, surname=?, email=?,birth_date=?, address=?,subscription=?, end_subscription=?" \ "WHERE id_user = ?" query_params = (name, surname, email, birth_date, address, subscription, end_subscription, int(id_user)) self.db_perform_query(query_update, query_params) # final result if self.db_manager.row_count == 0: return json.dumps({"status": 'user_not_updated'}) else: return json.dumps({"status": 'user_updated'})
def update_exercise(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_list = p.get("id", None) day = p.get("day", None) repetitions = p.get("repetitions", None) weight = p.get("weight", None) details = p.get("details", None) check_mandatory_parameters( [id_list, day, repetitions, weight, details], ["id", "day", "repetitions", "weight", "details"]) # Update general info inside exercise_schedules table query = "UPDATE exercise_schedules " \ "SET day=?, repetitions=?, weight=?, details=? " \ "WHERE id_list = ?" query_params = (int(day), int(repetitions), int(weight), details, int(id_list)) self.db_perform_query(query, query_params) # check the exercise has been updated if self.db_manager.row_count == 0: return json.dumps({"status": kSTATUS_ERR}) else: return json.dumps({"status": kSTATUS_OK})
def look_updated_exercise_list(p): # retrieve exercise info self._auth() id_exercise = p.get("id_exercise", None) check_mandatory_parameters([id_exercise], ["id_exercise"]) query = "SELECT name, description, muscular_zone, url " \ "FROM exercise " \ "WHERE id_exercise = ?" response = self.db_perform_query(query, (int(id_exercise), ), single_res=True) # check exercise exists if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # final result exercise_json = { "status": kSTATUS_OK, "name": response[0], "description": response[1], "muscular_zone": response[2], "url": response[3], } return self.to_json(exercise_json)
def logging(p): # check if the the application is allowed to access the service self._auth_app() # retrieve parameters email = p.get("email", None) password = p.get('password') check_mandatory_parameters([email, password], ["email", "password"]) # perform the query query = "SELECT id_account, first_name, last_name, password FROM account WHERE email = ?" response = self.db_perform_query(query, (email, ), single_res=True) if response is None: return json.dumps({"status": kSTATUS_ERR}) else: if not pbkdf2_sha256.verify(password, response[3]): return json.dumps({"status": 'wrong-pass'}) cherrypy.response.cookie["token"] = self.set_logged_user( response[0]) user_json = { "first_name": response[1], "last_name": response[2], "status": "successful" } return self.to_json(user_json)
def read_one_message(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id = p.get("id", None) check_mandatory_parameters([id], ["id"]) # perform the query query = "SELECT id_message, title, body, send_date, destination " \ "FROM messages " \ "WHERE id_message = ?" response = self.db_perform_query(query, (int(id), ), single_res=True) # final result if response is None: return json.dumps({"status": kSTATUS_ERR}) else: send_date = "{}-{}-{}".format(response[3].year, response[3].month, response[3].day) res = { "status": kSTATUS_OK, "id_message": response[0], "title": response[1], "body": response[2], "send_date": send_date, "destination": response[4] } return self.to_json(res)
def search_exercise_list(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters name = p.get("search", None) check_mandatory_parameters([name], ["search"]) query = "SELECT id_exercise, name, description, muscular_zone " \ "FROM exercise " \ "WHERE name = ? LIMIT 0,1" response = self.db_perform_query(query, (name, ), single_res=True) # check something have been found if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # represent exercises as dictionaries exercise = [] ex = { "id_exercise": response[0], "name": response[1], "description": response[2], "muscular_zone": response[3] } exercise.append(ex) # final result res = {"status": kSTATUS_OK, "exercises": exercise} return self.to_json(res)
def create_exercise(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_schedule = p.get("id", None) name = p.get("name", None) day = p.get("day", None) series = p.get("series", None) weight = p.get("weight", None) details = p.get("details", None) check_mandatory_parameters( [id_schedule, name, day, series, weight, details], ["id", "name", "day", "series", "weight", "details"]) # check the exercise exists query = "SELECT id_exercise FROM exercise WHERE name = ?" id_exercise = self.db_perform_query(query, (name, ), single_res=True) if id_exercise is None: return json.dumps({"status": kSTATUS_ERR}) else: # assign the exerise to the schedule query_ins = "INSERT INTO exercise_schedules " \ "(id_schedule, id_exercise, day, repetitions, weight, details) " \ "VALUES (?,?,?,?,?,?)" query_params = (int(id_schedule), int(id_exercise[0]), int(day), int(series), int(weight), details) self.db_perform_query(query_ins, query_params) # final result status = "not-inserted" if self.db_manager.row_count( ) == 0 else "successful" return json.dumps({"status": status})
def create_schedule(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) name = p.get("name", None) details = p.get("details", None) start_date = p.get("start_date", None) end_date = p.get("end_date", None) num_days = p.get("num_days", None) objective = p.get("objective", None) check_mandatory_parameters([ id_user, name, details, start_date, end_date, num_days, objective ], [ "id", "name", "details", "start_date", "end_date", "num_days", "objective" ]) # perform the query query = "INSERT INTO schedules (id_user,name,details,start_date,end_date,num_days,objective) " \ "VALUES (?,?,?,?,?,?,?)" params = (int(id_user), name, details, start_date, end_date, int(num_days), objective) self.db_perform_query(query, params) status = "not-inserted" if self.db_manager.row_count == 0 else "successful" return json.dumps({"status": status})
def read_one_exercise(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_exercise = p.get("id", None) check_mandatory_parameters([id_exercise], ["id"]) # perform the query query = "SELECT name, description, muscular_zone, url " \ "FROM exercise " \ "WHERE id_exercise = ?" response = self.db_perform_query(query, (int(id_exercise), ), single_res=True) # check the exercise exists if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # final result exercise_json = { "status": kSTATUS_OK, "name": response[0], "description": response[1], "muscular_zone": response[2], "url": response[3] } return self.to_json(exercise_json)
def delete_schedules(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) check_mandatory_parameters([id_user], ["id"]) # delete all the schedules for a given user query = "SELECT id_schedule FROM schedules WHERE id_user = ?" response = self.db_perform_query(query, (int(id_user), )) if response is not None: # for each schedule, delete all the references in tables 'schedules' and 'exercise_schedule' for i in range(len(response)): # 2. Delete schedule query_sc = "DELETE FROM schedules WHERE id_schedule = ?" delete_sc = self.db_perform_query(query_sc, (int(response[i][0]), )) if delete_sc is None: return json.dumps({"status": "not-deleted"}) return json.dumps({"status": "successful"}) else: return json.dumps({"status": "not-deleted"})
def update_exercise_list(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_exercise = p.get("id", None) name = p.get("name", None) description = p.get("description", None) muscular_zone = p.get("muscular_zone", None) url = p.get("url", None) check_mandatory_parameters( [id_exercise, name, description, muscular_zone, url], ["id", "name", "description", "muscular_zone", "url"]) # perform the query query = "UPDATE exercise " \ "SET name=?, description=?, muscular_zone=?, url=? " \ "WHERE id_exercise = ?" params = (name, description, muscular_zone, url, int(id_exercise)) self.db_perform_query(query, params) if self.db_manager.row_count() == 0: return json.dumps({"status": kSTATUS_ERR}) else: return json.dumps({"status": kSTATUS_OK})
def manage_schedules(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters( [id_user, records_per_page, from_record_num], ["id", "records_per_page", "from_record_num"]) # perform the query (all the schedules of a given user) query = "SELECT id_schedule, name, details, start_date, end_date, num_days, objective " \ "FROM schedules " \ "WHERE id_user = ? ORDER BY end_date DESC LIMIT ?,?" response = self.db_perform_query( query, (int(id_user), int(from_record_num), int(records_per_page))) # check there are schedules for the user if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # evaluate the number of schedules found query = "SELECT COUNT(*) FROM schedules WHERE id_user = ?" total_record = self.db_perform_query(query, (int(id_user), ), single_res=True) exercises = [] # represent exercises as array of dictionaries for i in range(len(response)): start_date = "{}-{}-{}".format(response[i][3].year, response[i][3].month, response[i][3].day) end_date = "{}-{}-{}".format(response[i][4].year, response[i][4].month, response[i][4].day) ex = { "id_schedule": response[i][0], "name": response[i][1], "details": response[i][2], "start_date": start_date, "end_date": end_date, "num_days": response[i][5], "objective": response[i][6] } exercises.append(ex) # final result res = { "status": kSTATUS_OK, "schedules": exercises, "total_rows": total_record[0] } return self.to_json(res)
def search_messages(p): # check if the user and the application are allowed to access the service self._auth() # retrieve the parameters title = p.get("search", None) records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters( [title, records_per_page, from_record_num], ["search", "records_per_page", "from_record_num"]) # retrieve the messages from DB (given the title) query = "SELECT id_message, title, body, send_date, destination " \ "FROM messages " \ "WHERE title = ? " \ "ORDER BY send_date DESC LIMIT ?,?" response = self.db_perform_query(query, ( title, int(from_record_num), int(records_per_page), )) # check if some messages have been retrieved if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # evaluate the number of messages found query = "SELECT COUNT(*) FROM messages WHERE title = ?" total_record = self.db_perform_query(query, (title, ), single_res=True) messages = [] # represent exercises as array of dictionaries for i in range(len(response)): send_date = "{}-{}-{}".format(response[i][3].year, response[i][3].month, response[i][3].day) mes = { "id_message": response[i][0], "title": response[i][1], "body": response[i][2], "send_date": send_date, "destination": response[i][4] } messages.append(mes) # final result res = { "status": kSTATUS_OK, "messages": messages, "total_rows": total_record[0] } return self.to_json(res)
def delete_single_exercise_list(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_exercise = p.get("id", None) check_mandatory_parameters([id_exercise], ["id"]) # delete the exercise query = "DELETE FROM exercise WHERE id_exercise = ?" self.db_perform_query(query, (int(id_exercise), )) status = "not-deleted" if self.db_manager.row_count == 0 else "successful" return json.dumps({"status": status})
def delete_user(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) check_mandatory_parameters([id_user], ["id"]) # execute the query query = "DELETE FROM user WHERE id_user = ?" self.db_perform_query(query, (int(id_user), )) # final result status = "not-deleted" if self.db_manager.row_count == 0 else "successful" return json.dumps({"status": status})
def autocomplete(p, query, dict_key): # check application can access the service self._auth() # retrieve parameters q = p.get("query", None) check_mandatory_parameters([q], ["query"]) query_param = "%{}%".format(q) # perform the query auto_complete = self.db_perform_query(query, (query_param, )) # final result res = kSTATUS_ERR if auto_complete is None else auto_complete return json.dumps({dict_key: res})
def create_user(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters name = p.get("name", None) surname = p.get("surname", None) email = p.get("email", None) password = p.get("password", None) address = p.get("address", None) birth_date = p.get("birth_date", None) phone = p.get("phone", None) subscription = p.get("subscription", None) end_subscription = p.get("end_subscription", None) check_mandatory_parameters([ name, surname, email, password, address, birth_date, phone, subscription, end_subscription ], [ "name", "surname", "email", "password", "address", "birth_date", "phone", "subscription", "end_subscription" ]) hash = pbkdf2_sha256.hash(password) # check errors (user already existing or DB error) query_check = "SELECT id_user FROM user WHERE email = ?" response_check = self.db_perform_query(query_check, (email, ), single_res=True) if response_check is None: # try to create the user query = "INSERT INTO user " \ "(name, surname, email, password, address, birth_date, phone, subscription, end_subscription) " \ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" query_params = (name, surname, email, hash, address, birth_date, phone, subscription, end_subscription) self.db_perform_query(query, query_params, single_res=True) # final result if self.db_manager.row_count != 0: return json.dumps({"status": "successful"}) else: return json.dumps({"status": "not-inserted"}) else: return json.dumps({"status": "already-registered"})
def delete_single_schedule(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_schedule = p.get("id", None) check_mandatory_parameters([id_schedule], ["id"]) # perform the queries # 1. Delete the schedule query_sc = "DELETE FROM schedules WHERE id_schedule = ?" self.db_perform_query(query_sc, (int(id_schedule), )) if self.db_manager.row_count == 0: return json.dumps({"status": "not-deleted"}) return json.dumps({"status": "successful"})
def search_users_by_surname(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters surname = p.get("search", None) records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters( [surname, records_per_page, from_record_num], ["search", "records_per_page", "from_record_num"]) # perform the query (list of users with the same surname?) query = "SELECT id_user, name, surname, email " \ "FROM user " \ "WHERE surname = ? ORDER BY id_user DESC LIMIT ?,?" response = self.db_perform_query( query, (surname, int(from_record_num), int(records_per_page))) # check if some users have been found if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # retrieve the number of users found query = "SELECT COUNT(*) FROM user WHERE surname = ?" total_record = self.db_perform_query(query, (surname, ), single_res=True) users = [] # represent exercises as array of dictionaries for i in range(len(response)): us = { "id_user": response[i][0], "name": response[i][1], "surname": response[i][2], "email": response[i][3] } users.append(us) # final result user_json = { "status": kSTATUS_OK, "users": users, "total_rows": total_record[0] } return self.to_json(user_json)
def manage_users(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters([records_per_page, from_record_num], ["records_per_page", "from_record_num"]) # perform the query query = "SELECT id_user, name, surname, email " \ "FROM user " \ "ORDER BY id_user DESC LIMIT ?,?" response = self.db_perform_query( query, (int(from_record_num), int(records_per_page))) # check that some users exist if not response: return json.dumps({"status": kSTATUS_ERR}) else: # evaluate the number of users found query = "SELECT COUNT(*) FROM user" total_record = self.db_perform_query(query, single_res=True) users = [] # represent exercises as array of dictionaries for i in range(len(response)): us = { "id_user": response[i][0], "name": response[i][1], "surname": response[i][2], "email": response[i][3] } users.append(us) # final result res = { "status": kSTATUS_OK, "users": users, "total_rows": total_record[0] } return self.to_json(res)
def read_exercises(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters([records_per_page, from_record_num], ["records_per_page", "from_record_num"]) # perform the query query = "SELECT * FROM exercise " \ "LIMIT ?,?" response = self.db_perform_query( query, (int(from_record_num), int(records_per_page))) # check some exercises have been found if not response: return json.dumps({"status": kSTATUS_ERR}) else: query = "SELECT COUNT(*) FROM exercise " total_record = self.db_perform_query(query) # number of exercises found exercises = [] # represent exercises as array of dictionaries for i in range(len(response)): ex = { "id_exercise": response[i][0], "name": response[i][1], "description": response[i][2], "muscular_zone": response[i][3] } exercises.append(ex) # final result res = { "status": kSTATUS_OK, "exercises": exercises, "total_rows": total_record[0] } return self.to_json(res)
def look_updated_exercise(p): # retrieve exercise schedule info # check if the user and the application are allowed to access the service self._auth() id_list = p.get("id_list", None) check_mandatory_parameters([id_list], ["id_list"]) query = "SELECT id_exercise, day, details, weight, repetitions " \ "FROM exercise_schedules " \ "WHERE id_list = ?" response = self.db_perform_query(query, (int(id_list), ), single_res=True) # check it has been found if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # retrieve exercise's name query_name = "SELECT name FROM exercise " \ "WHERE id_exercise = ?" response_name = self.db_perform_query(query_name, (int(response[0]), ), single_res=True) if response_name is None: return json.dumps({"status": "exercise_name-not-found"}) else: # final result exercise_json = { "status": kSTATUS_OK, "id_exercise": response[0], "day": response[1], "details": response[2], "weight": response[3], "repetitions": response[4], "name": response_name[0] } return self.to_json(exercise_json)
def register_account(p): # check if the application is allowed to access the service self._auth_app() # retrieve parameters email = p.get("email", None) first_name = p.get("first_name", None) last_name = p.get("last_name", None) password = p.get("password", None) check_mandatory_parameters( [email, first_name, last_name, password], ["email", "first_name", "last_name", "password"]) # maybe the user was already registered query = "SELECT id_account FROM account WHERE email = ?" control = self.db_perform_query(query, (email, ), single_res=True) if control is None: hash = pbkdf2_sha256.hash(password) # try to register the user query = "INSERT INTO account (first_name, last_name, email, password) VALUES (?,?,?,?)" params = (first_name, last_name, email, hash) self.db_perform_query(query, params, single_res=True) if self.db_manager.row_count == 0: return json.dumps({"status": "not-registered"}) # retrieve the generated user ID query = "SELECT id_account FROM account WHERE email = ?" account_id = self.db_perform_query(query, (email, ), single_res=True) cherrypy.response.cookie["token"] = self.set_logged_user( account_id[0]) return json.dumps({"status": "successful"}) else: return json.dumps({"status": "already-registered"})
def create_exercise_list(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters name = p.get("name", None) description = p.get("description", None) muscular_zone = p.get("muscular_zone", None) url = p.get("url", None) check_mandatory_parameters( [name, description, muscular_zone, url], ["name", "description", "muscular_zone", "url"]) # create the exercise query = "INSERT INTO exercise (name, description, muscular_zone, url) VALUES (?,?,?,?)" query_params = (name, description, muscular_zone, url) self.db_perform_query(query, query_params) # final result status = "not-inserted" if self.db_manager.row_count == 0 else "successful" return json.dumps({"status": status})
def create_message(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters title = p.get("title", None) body = p.get("body", None) send_date = p.get("send_date", None) destination = p.get("destination", None) check_mandatory_parameters( [title, body, send_date, destination], ["title", "body", "send_date", "destination"]) # create the message query = "INSERT INTO messages " \ "(title, body, send_date, destination)" \ "VALUES (?,?,?,?)" query_params = (title, body, send_date, destination) self.db_perform_query(query, query_params) # final result status = "not-inserted" if self.db_manager.row_count == 0 else "successful" return json.dumps({"status": status})
def read_one_user(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_user = p.get("id", None) check_mandatory_parameters([id_user], ["id"]) # perform the query query = "SELECT id_user, name, surname, email, birth_date, address, subscription, end_subscription " \ "FROM user " \ "WHERE id_user = ?" response = self.db_perform_query(query, (int(id_user), ), single_res=True) # final result if response is None: return json.dumps({"status": kSTATUS_ERR}) else: birth_date = "{}-{}-{}".format(response[4].year, response[4].month, response[4].day) end_subscription = "{}-{}-{}".format(response[7].year, response[7].month, response[7].day) user_json = { "status": kSTATUS_OK, "id_user": response[0], "name": response[1], "surname": response[2], "email": response[3], "birth_date": birth_date, "address": response[5], "subscription": response[6], "end_subscription": end_subscription } return self.to_json(user_json)
def account_profile(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id = p.get("id", None) check_mandatory_parameters([id], ["id"]) # perform the query query = "SELECT name, surname " \ "FROM user " \ "WHERE id_user = ?" profile = self.db_perform_query(query, (id, ), single_res=True) # check the user exists if profile is None: return json.dumps({"status": kSTATUS_ERR}) else: user_json = { "status": kSTATUS_OK, "name": profile[0], "surname": profile[1], } return self.to_json(user_json)
def look_updated_user(p): self._auth() id_user = p.get("id_user", None) check_mandatory_parameters([id_user], ["id_user"]) # retrieve user fresh information query = "SELECT id_user, name, surname, email, birth_date, address, subscription, end_subscription " \ "FROM user " \ "WHERE id_user = ? LIMIT 0,1" response = self.db_perform_query(query, (int(id_user), ), single_res=True) # check the user exists if response is None: return json.dumps({"status": kSTATUS_ERR}) else: birth_date = "{}-{}-{}".format(response[4].year, response[4].month, response[4].day) end_subscription = "{}-{}-{}".format(response[7].year, response[7].month, response[7].day) user_json = { "status": kSTATUS_OK, "id_user": response[0], "name": response[1], "surname": response[2], "email": response[3], "birth_date": birth_date, "address": response[5], "subscription": response[6], "end_subscription": end_subscription } return self.to_json(user_json)
def manage_exercises(p): # check if the user and the application are allowed to access the service self._auth() # retrieve parameters id_schedule = p.get("id", None) records_per_page = p.get("records_per_page", None) from_record_num = p.get("from_record_num", None) check_mandatory_parameters( [id_schedule, records_per_page, from_record_num], ["id", "records_per_page", "from_record_num"]) # perform the query to retrieve the exercises of a given schedule query = "SELECT id_list, id_exercise, day, repetitions, weight, details " \ "FROM exercise_schedules " \ "WHERE id_schedule = ? ORDER BY day DESC LIMIT ?,?" response = self.db_perform_query( query, (int(id_schedule), int(from_record_num), int(records_per_page))) # check the schedule has some exercises if response is None: return json.dumps({"status": kSTATUS_ERR}) else: # evaluate the number of exercises found query = "SELECT COUNT(*) FROM exercise_schedules WHERE id_schedule = ?" total_record = self.db_perform_query(query, (int(id_schedule), ), single_res=True) exercises = [] # represent exercises as array of dictionaries for i in range(len(response)): ex = { "id_list": response[i][0], "id_exercise": response[i][1], "day": response[i][2], "repetitions": response[i][3], "weight": response[i][4], "details": response[i][5] } exercises.append(ex) # for each exercise, retrieve also the name for i in range(len(exercises)): query_name = "SELECT name FROM exercise WHERE id_exercise = ?" response_name = self.db_perform_query( query_name, (exercises[i]["id_exercise"], ), single_res=True) # check the exercise exist in the exercise table if not response_name: return json.dumps( {"status": 'exercise-' + kSTATUS_ERR}) else: # save the name exercises[i]["name"] = response_name[0] # final result res = { "status": kSTATUS_OK, "exercises": exercises, "total_rows": total_record[0] } return self.to_json(res)