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'])
