def getRank(): dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `rank`") myRank = dict_cursor.fetchall() dict_cursor.close() return jsonify(myRank)
def swimmer_creation(number_of_swimmer): db, c = connector.connection() dict_cursor = connector.getDictCursor() list_swimmer_account = [] i = 0 while i < int(number_of_swimmer): rand_num = str(random.randint(1, 9999)).zfill(4) # format 55 to 0055 this_year = str(datetime.datetime.now().year) # get this year randuser = '******' + this_year + '_' + rand_num # format st<this year>_<random number> my_dict = {"username": randuser, "password": "******"} dict_cursor.execute('SELECT username FROM user WHERE username = %s', randuser) my_username = dict_cursor.fetchone() if not my_username: list_swimmer_account.append(my_dict) i += 1 return jsonify({ "values": { "swimmers": list_swimmer_account }, "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) })
def getHeartBeat(): dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `heart_beat`") myHeartBeat = dict_cursor.fetchall() dict_cursor.close() return jsonify(myHeartBeat)
def delete_swimmer(username, swimmer_username): """just delete a swimmer by his/her username""" db, c = connector.connection() dict_cursor = connector.getDictCursor() try: c.execute('DELETE FROM user WHERE username = %s', swimmer_username) db.commit() except: db.rollback() return jsonify({ "values": "Error", "success": False, "errorMessage": "Something went wrong.", "message": None, "created_date": str(datetime.datetime.now()) }) return jsonify({ "values": swimmer_username + " has been deleted by " + username + ".", "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) })
def change_password(username, js_data): """ username is GET method js_data are password, new_password""" db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute('SELECT password FROM user WHERE username = %s', username) my_password = dict_cursor.fetchone() hashed_pw = bcrypt.hashpw(js_data['password'].encode(), my_password['password'].encode()) if my_password['password'].encode() == hashed_pw: try: c.execute('UPDATE user SET password = %s WHERE username = %s', (bcrypt.hashpw(js_data['new_password'].encode(), bcrypt.gensalt()), username)) db.commit() return jsonify({ "values": "Password of " + username + " has been changed.", "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) }) except: db.rollback() return jsonify({ "values": "Error", "success": False, "errorMessage": "Something went wrong.", "message": None, "created_date": str(datetime.datetime.now()) })
def viewListLessonOfCoach(username): db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM user WHERE username = %s", username) myCoach = dict_cursor.fetchone() dict_cursor.execute("SELECT id,name FROM team WHERE coach_id = %s", myCoach['id']) myListTeam = dict_cursor.fetchall() result = [] columns = ['lesson_id', 'team_name', 'lesson_name', 'date', 'check_record'] for myTeam in myListTeam: dict_cursor.execute( "SELECT L.id,L.name, DATE(LT.date) as date, LT.check_record FROM `lesson` L, `lesson-team` LT WHERE L.id = LT.lesson_id AND LT.team_id = %s ", myTeam['id']) myLesson = dict_cursor.fetchall() for lesson in myLesson: listRow = [ lesson['id'], myTeam['name'], lesson['name'], lesson['date'].strftime("%Y-%m-%d"), lesson['check_record'] ] info = dict(zip(columns, listRow)) result.append(info) dict_cursor.close() c.close() db.close() return jsonify({ "values": result, "success": True, "errorMessage": "", "message": None })
def view(team_id): db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `lesson-team` WHERE team_id = %s ", team_id) myLessonPlan = dict_cursor.fetchall() if myLessonPlan: print(type(myLessonPlan)) dict_cursor.close() c.close() db.close() return jsonify({ "values": myLessonPlan, "success": True, "errorMessage": "", "message": None }) dict_cursor.close() c.close() db.close() return jsonify({ "values": "", "success": False, "errorMessage": "Invalid team", "message": None })
def login(js_data): db = connector.connection() dict_cursor = connector.getDictCursor() try: dict_cursor.execute('SELECT * FROM user WHERE username = %s', js_data['username']) except: return jsonify({ "values": "Error", "success": False, "errorMessage": "Invalid user.", "message": None, "created_date": str(datetime.datetime.now()) }) db_data = dict_cursor.fetchone() if db_data: # The next 3 lines check hashed password is valid for login hashed_pw = bcrypt.hashpw(js_data['password'].encode(), db_data['password'].encode()) if db_data['password'].encode() == hashed_pw: # The next 4 lines is set up expires time expires = datetime.timedelta( seconds=constant.ACCESS_TOKEN_EXPIRES_TIME) # the identity is username access_token = create_access_token(identity=js_data['username'], expires_delta=expires) return jsonify({ "values": { "token": access_token, "expiresIn": constant.ACCESS_TOKEN_EXPIRES_TIME, "username": js_data['username'] }, "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) }) else: return jsonify({ "values": "Error", "success": False, "errorMessage": "Authentication failed. Wrong password.", "message": None, "created_date": str(datetime.datetime.now()) }) else: return jsonify({ "values": "Error", "success": False, "errorMessage": "User not found.", "message": None, "created_date": str(datetime.datetime.now()) })
def add(username, data): '''input json key : lesson_id,repetition, username, exercise_id, milisec, sec, min, heart_beat_id ''' db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `user` WHERE username = %s", username) myCoach = dict_cursor.fetchone() for record in data['record']: dict_cursor.execute( "SELECT * FROM `user` WHERE `id`= %s AND role_id = 2", record['user_id']) mySwimmer = dict_cursor.fetchone() dict_cursor.execute( "SELECT COUNT(*) FROM `record` WHERE user_id = %s AND exercise_id =%s ", (mySwimmer['id'], data['exercise_id'])) myRep = dict_cursor.fetchone()['COUNT(*)'] dict_cursor.execute("SELECT * FROM `exercise` WHERE id= %s ", data['exercise_id']) myExercise = dict_cursor.fetchone() if myRep < myExercise['repetition']: c.execute( "INSERT INTO `record` (user_id, coach_id, lesson_id, exercise_id, swim_millisec , swim_sec , swim_min, heart_beat_id ) VALUES (%s, %s, %s, %s, %s, %s, %s ,%s)", (record['user_id'], myCoach['id'], data['lesson_id'], data['exercise_id'], record['millisec'], record['sec'], record['min'], record['heart_beat_id'])) db.commit() else: dict_cursor.close() c.close() db.close() return jsonify({ "values": "", "success": False, "errorMessage": "", "message": None }) dict_cursor.close() c.close() db.close() return jsonify({ "values": "", "success": True, "errorMessage": "", "message": None })
def edit(data, username): db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `lesson` WHERE id = %s", data['lesson_id']) myLesson = dict_cursor.fetchone() if myLesson: c.execute( "UPDATE `lesson` SET name = %s, updated_at = %s WHERE id = %s", (data['lesson_name'], str( datetime.datetime.now()), data['lesson_id'])) db.commit() c.execute("UPDATE `lesson-team` SET date = %s WHERE lesson_id = %s", (data['date'], data['lesson_id'])) db.commit() for exercise in data['exercise']: dict_cursor.execute( "SELECT * FROM distance WHERE swim_distance = %s", exercise['swim_distance']) myDistanceID = dict_cursor.fetchone() dict_cursor.execute("SELECT * FROM style WHERE swim_name = %s", exercise['swim_name']) myStyleID = dict_cursor.fetchone() c.execute( "UPDATE `exercise` SET style_id = %s,distance_id = %s, repetition=%s, description = %s WHERE lesson_id = %s AND type_id = %s", (myStyleID['id'], myDistanceID['id'], exercise['repetition'], exercise['description'], data['lesson_id'], exercise['type_id'])) db.commit() db.close() return jsonify({ "values": "", "success": True, "errorMessage": "", "message": None }) return jsonify({ "values": "", "success": False, "errorMessage": "Invalid lesson", "message": None })
def view(username): dict_cursor = connector.getDictCursor() try: dict_cursor.execute("SELECT id FROM `user` WHERE username = %s", username) coach = dict_cursor.fetchone() dict_cursor.execute("SELECT * FROM `lesson` WHERE coach_id = %s", coach['id']) myListLesson = dict_cursor.fetchall() except: return jsonify({ "values": "", "success": False, "errorMessage": "", "message": None }) dict_cursor.close() return jsonify(myListLesson)
def swimmer_add(js_data): # GET methods """receive number of swimmer need to create""" db, c = connector.connection() dict_cursor = connector.getDictCursor() my_account_list = "" for swimmer in js_data['swimmers']: try: c.execute( '''INSERT INTO user (username, password, role_id, is_verified, created_at, dob) VALUES (%s, %s, %s, %s, %s, %s)''', (swimmer['username'], swimmer['password'], 2, 0, str(datetime.datetime.now()), '2000-1-1')) # add account to string my_account_list += ('tai khoan: ' + swimmer['username'] + '\n' + 'mat khau: ' + swimmer['password'] + '\n' + '-' * 40 + '\n') db.commit() except: db.rollback() return jsonify({ "values": "Error", "success": False, "errorMessage": "Something went wrong.", "message": None, "created_date": str(datetime.datetime.now()) }) # the next 4 lines store account to swimmer.txt f = open('swimmer.txt', 'w') temp = '-' * 40 + '\n' + my_account_list encoded = base64.b64encode(temp.encode()) # encode before store in a text file f.write(str(encoded.decode())) return jsonify({ "values": "Swimmer created.", "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) })
def get_info(username): """return all information of a username""" db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute('SELECT * FROM user WHERE username = %s', username) value = dict_cursor.fetchone() if not value: return jsonify({ "values": "Error", "success": False, "errorMessage": "No username like this.", "message": None, "created_date": str(datetime.datetime.now()) }) return jsonify({ "values": { "user": value }, "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) })
def edit_info(username, js_data): ''' username is GET method, js_data are first_name, last_name, gender, dob, weight, height, address, phone, email, parent_name, parent_phone protected''' db, c = connector.connection() dict_cursor = connector.getDictCursor() try: c.execute( '''UPDATE user SET first_name = %s, last_name = %s, gender = %s, dob = %s, weight = %s, height = %s, address = %s, phone = %s, email = %s, parent_name = %s, parent_phone = %s WHERE username = %s''', (js_data['first_name'], js_data['last_name'], js_data['gender'], js_data['dob'], js_data['weight'], js_data['height'], js_data['address'], js_data['phone'], js_data['email'], js_data['parent_name'], js_data['parent_phone'], username)) db.commit() return jsonify({ "values": "Profile of " + username + " has been changed.", "success": True, "errorMessage": None, "message": "Accepted.", "created_date": str(datetime.datetime.now()) }) except: db.rollback() return jsonify({ "values": "Error", "success": False, "errorMessage": "Something went wrong.", "message": None, "created_date": str(datetime.datetime.now()) })
def delete(lesson_id): db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `lesson` WHERE id = %s", lesson_id) myLesson = dict_cursor.fetchone() if myLesson: c.execute("DELETE FROM `lesson` WHERE id = %s", lesson_id) c.execute("DELETE FROM `exercise` WHERE lesson_id = %s", lesson_id) db.commit() db.close() return jsonify({ "values": "Lesson " + myLesson['name'] + " has deleted", "success": True, "errorMessage": "", "message": None }) return jsonify({ "values": "", "success": False, "errorMessage": "Invalid lesson", "message": None })
def add(username, data): '''JSON include key : lesson_name,team_name,date''' db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM team where name = %s", data['team_name']) myTeam = dict_cursor.fetchone() dict_cursor.execute("SELECT * FROM lesson WHERE name = %s", data['lesson_name']) myLesson = dict_cursor.fetchone() dict_cursor.execute( "SELECT * FROM `lesson-team` WHERE team_id = %s AND lesson_id = %s ", (myTeam['id'], myLesson['id'])) if not dict_cursor.fetchone(): c.execute( "INSERT INTO `lesson-team` (lesson_id,team_id,date) VALUES (%s, %s, %s)", (myLesson['id'], myTeam['id'], data['date'])) db.commit() dict_cursor.close() c.close() db.close() return jsonify({ "values": "", "success": True, "errorMessage": "", "message": None }) dict_cursor.close() c.close() db.close() return jsonify({ "values": "", "success": False, "errorMessage": "this team already has the same lesson", "message": None })
def add(data, username): '''Json include keys : lesson_name, exercise (value is array include keys : style, distance,repetition,description,type_id)''' try: db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `user` WHERE username = %s", username) coach = dict_cursor.fetchone() c.execute("SELECT * FROM `lesson` WHERE name = %s AND coach_id = %s ", (data['lesson_name'], coach['id'])) myLesson = c.fetchall() if not myLesson: c.execute( "INSERT INTO `lesson` (name,created_at,coach_id) VALUES (%s,%s,%s)", ( data['lesson_name'], str(datetime.datetime.now()), coach['id'], )) db.commit() dict_cursor.close() db.close() c.close() db2, c = connector.connection() dict_cursor2 = connector.getDictCursor() dict_cursor2.execute("SELECT * FROM `lesson` WHERE name = %s", data['lesson_name']) myLesson2 = dict_cursor2.fetchone() for i in data['exercise']: c.execute( "SELECT * FROM `exercise` WHERE type_id = %s AND lesson_id = %s", (i['type_id'], myLesson2['id'])) if not c.fetchall(): dict_cursor2.execute( "SELECT id FROM `distance` WHERE swim_distance = %s", i['distance']) myDistanceID = dict_cursor2.fetchone() dict_cursor2.execute( "SELECT id FROM `style` WHERE swim_name = %s", i['style']) myStyleID = dict_cursor2.fetchone() c.execute( "INSERT INTO `exercise` (style_id,distance_id,repetition,description,type_id,lesson_id) VALUES(%s,%s,%s,%s,%s,%s)", (myStyleID['id'], myDistanceID['id'], i['repetition'], i['description'], i['type_id'], myLesson2['id'])) db2.commit() else: dict_cursor2.close() db2.close() c.close() return jsonify({ "values": "", "success": False, "errorMessage": "type_id exist", "message": None }) db2.close() return jsonify({ "values": myLesson2, "success": True, "errorMessage": "", "message": None }) except: return jsonify({ "values": "ERROR", "success": False, "errorMessage": "", "message": None })
def viewMember(username, data): db, c = connector.connection() dict_cursor = connector.getDictCursor() dict_cursor.execute("SELECT * FROM `team-swimmer` WHERE team_id = %s", data['team_id'])