def get_user_connection_details(request: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.conn.cursor()
jsondata = (request.get_json())
if jsondata is None:
return {'serverCode': 400}
else:
username = jsondata['username']
cursor.execute('SELECT ipaddress FROM user WHERE username=?',
(username, ))
ip = cursor.fetchone()
cursor.execute('SELECT port FROM user WHERE username=?', (username, ))
port = cursor.fetchone()
cursor.execute('SELECT netplaymode FROM user WHERE username=?',
(username, ))
mode = cursor.fetchone()
data = {
'ipAddress': '\'' + ip[0] + '\'',
'port': '\'' + port[0] + '\'',
'mode': '\'' + mode[0] + '\'',
'serverCode': 200
}
return data
def get_user_info(reqeust: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.conn.cursor()
jsondata = (reqeust.get_json())
if jsondata is None:
return {'serverCode': 400}
else:
username = jsondata['username']
cursor.execute('SELECT ipaddress FROM user WHERE username=?;',
(username, ))
ip = cursor.fetchone()
cursor.execute('SELECT port FROM user WHERE username=?;', (username, ))
port = cursor.fetchone()
cursor.execute('SELECT mostplayedgame FROM user WHERE username=?;',
(username, ))
mostplayedgame = cursor.fetchone()
cursor.execute('SELECT mostplayedemulator FROM user WHERE username=?;',
(username, ))
mostplayedemulator = cursor.fetchone()
cursor.execute('SELECT mode FROM user WHERE username=?', (username, ))
mode = cursor.fetchone()
jsonlist = {
'ipAddress': '\'' + ip[0] + '\'',
'port': '\'' + port[0] + '\'',
'mostPlayedGame': '\'' + mostplayedgame[0] + '\'',
'mostPlayedEmulator': '\'' + mostplayedemulator[0] + '\'',
'serverCode': 200,
'mode': '\'' + mode[0] + '\''
}
return jsonlist
def get_all_players():
sql = "SELECT player_id, first_name, last_name, team_id FROM player;"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
return [map_row_to_player(row) for row in rows]
def get_all_fixtures():
get_all_fixtures_query = "SELECT team_fixture_id, home_team_id, away_team_id, fixture_date FROM team_fixture"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(get_all_fixtures_query)
rows = cursor.fetchall()
cursor.close()
return [map_row_to_fixture(row) for row in rows]
def get_divisions(league_id):
get_divisions_query = "SELECT division_id, name FROM division WHERE league_id=%s;"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(get_divisions_query, (league_id, ))
rows = cursor.fetchall()
cursor.close()
return [map_row_to_division(row) for row in rows]
def add_player(player):
sql = "INSERT INTO player (first_name, last_name, team_id) VALUES (%s, %s, %s, %s);"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(
sql,
(player["first_name"], player["last_name"], player["team_id"]))
db.commit()
cursor.close()
def update_leaderboard_stats(req: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.get_connection().cursor()
jsonData = (req.get_json())
if jsonData is None:
return {'serverCode': 400}
else:
username = jsonData['username']
mostplayedgame = jsonData['mostPlayedGame']
mostplayedsystem = jsonData['mostPlayedSystem']
cursor.execute(
'UPDATE USER SET mostplayedgame=?, mostplayedsystem=? WHERE username=?;',
(mostplayedgame, mostplayedsystem, username))
connection.conn.commit()
return {'serverCode': 200}
def get_league_data():
get_league_query = "SELECT league_id, name FROM league;"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(get_league_query)
row = cursor.fetchone()
cursor.close()
league = map_row_to_league(row)
league["divisions"] = division_service.get_divisions(
league["league_id"])
return league
def read(self):
'''
Get the database connection properties
'''
dbProps = self.regConfig.read([self.dbHost, self.dbPort, self.dbName])
if len(dbProps) < 3:
return None
else:
return DatabaseConnection(dbProps[self.dbHost],
dbProps[self.dbPort],
dbProps[self.dbName])
def add_team(team):
sql = "INSERT INTO player (first_name, last_name, team_id) VALUES (%s, %s, %s, %s);"
with DatabaseConnection() as db:
cursor = db.cursor()
cursor.execute(sql, (
team["first_name"],
team["last_name"],
team["team_id"]
))
team_id = cursor.fetchone()
db.commit()
cursor.close()
return team_id
def create_new_user(req: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.conn.cursor()
jsonData = (req.get_json())
if jsonData is None:
return {'serverCode': 400}
else:
cursor.execute("INSERT INTO user VALUES (?, ?, ?, ?, ?, ?);",
(jsonData['username'], jsonData['ipAddress'],
jsonData['port'], 'null', 'null', jsonData['mode']))
connection.conn.commit()
return {'serverCode': 200}
def update_ip(req: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.conn.cursor()
jsonData = (req.get_json())
if jsonData is None:
return {'serverCode': 400}
else:
username = jsonData['username']
newaddress = jsonData['ipAddress']
port = jsonData['port']
cursor.execute('UPDATE USER SET ipaddress=?, port=? WHERE username=?;',
(newaddress, port, username))
connection.conn.commit()
return {'serverCode': 200}
def change_seller_status(*args):
""" 셀러 상태 변경
마스터 권한을 가진 어카운트가 셀러의 상태를 변경 하는 기능.
path parameter 로 셀러 계정 번호를 받고 body 로 변경하고자 하는 셀러 상태 번호를 받는다.
Args:
args: 유효성 검사를 통과한 파라미터 리스트
seller_account_id: path parameter 를 통해서 들어온 셀러 계정 번호
seller_status_id: request body 를 통해서 들어온
Returns:
status_change_result: seller_service 에서 넘겨받은 결과 값.
400: seller_service 의 클래스 호출 실패 또는 parameter 를 제대로 넘겨지주 못했을 경우
500: 데이터베이스 연결 실패
Authors:
[email protected] (윤희철)
History:
2020-04-05 ([email protected]): 초기 생성
2020-04-09 ([email protected]): 선분이력을 반영하여 상태변경 작성
"""
# 유저정보를 가져와 서비스로 넘김
user = g.account_info
# 유효성검사를 통과한 parameter 를 딕셔너리 담는다.
target_seller_info = {
'seller_account_id': args[0],
'seller_status_id': args[1]
}
try:
db_connection = DatabaseConnection()
if db_connection:
seller_service = SellerService()
status_change_result = seller_service.change_seller_status(
target_seller_info, user, db_connection)
return status_change_result
else:
return jsonify({'message': 'NO_DATABASE_CONNECTION'}), 500
except Exception as e:
return ({'message': f'{e}'}), 400
def wrapper(*args, **kwargs):
access_token = request.headers.get('Authorization', None)
if access_token:
try:
payload = jwt.decode(access_token,
SECRET['secret_key'],
algorithm=SECRET['algorithm'])
account_no = payload['account_no']
db_connection = DatabaseConnection()
if db_connection:
try:
with db_connection as db_cursor:
get_account_info_stmt = ("""
SELECT auth_type_id, is_deleted FROM accounts WHERE account_no=%(account_no)s
""")
db_cursor.execute(get_account_info_stmt,
{'account_no': account_no})
account = db_cursor.fetchone()
if account:
if account['is_deleted'] == 0:
g.account_info = {
'account_no': account_no,
'auth_type_id': account['auth_type_id']
}
return func(*args, **kwargs)
return jsonify({'message':
'DELETED_ACCOUNT'}), 400
return jsonify(
{'message': 'ACCOUNT_DOES_NOT_EXIST'}), 404
except Error as e:
print(f'DATABASE_CURSOR_ERROR_WITH {e}')
return jsonify({'message': 'DB_CURSOR_ERROR'}), 400
except jwt.InvalidTokenError:
return jsonify({'message': 'INVALID_TOKEN'}), 401
return jsonify({'message': 'NO_DATABASE_CONNECTION'}), 400
return jsonify({'message': 'INVALID_TOKEN'}), 401
def get_leaderboard_details(req: Request):
connection = DatabaseConnection('userbase.db')
cursor = connection.conn.cursor()
jsondata = (req.get_json())
if jsondata is None:
return {'serverCode': 400}
else:
username = jsondata['username']
cursor.execute('SELECT mostplayedgame FROM user WHERE username=?;',
(username, ))
mostplayedgame = cursor.fetchone()
cursor.execute('SELECT mostplayedemulator FROM user WHERE username=?;',
(username, ))
mostplayedemulator = cursor.fetchone()
jsonlist = {
'mostPlayedGame': '\'' + mostplayedgame[0] + '\'',
'mostPlayedEmulator': '\'' + mostplayedemulator[0] + '\'',
'serverCode': 200
}
return jsonlist
def __init__(self):
super(Processing).__init__()
self.database_connection = DatabaseConnection()
class Processing(object):
"""docstring for Processing"""
def __init__(self):
super(Processing).__init__()
self.database_connection = DatabaseConnection()
def check_login(self, username, password):
return self.database_connection.check_login(username, password)
def get_class_session_from_teacher(self, memberID):
return self.database_connection.get_teachID_from_memberID(memberID)
def get_information_sections(self, teacherID):
return self.database_connection.get_information_sections(teacherID)
def get_statistic_remart_student(self, sectionID):
return self.database_connection.get_statistic_remart_student(sectionID)
def add_student(self, data):
self.database_connection.insert_student(data)
def add_teacher(self, data):
self.database_connection.insert_teacher(data)
def add_remark(self, data):
self.database_connection.insert_remark_student(data)
def add_image_data(self, data):
self.database_connection.insert_inform_image(data)
def get_students_from_class_session(self, sectionID):
return self.database_connection.get_students_from_class_session(
sectionID)
def get_information_students(self, list_studentID):
data_student = []
for studentID in list_studentID:
name = self.database_connection.get_inform_student(studentID)
data_student.append([studentID, name])
return data_student
def get_embeddings_students(self, list_studentID):
data_image = []
for studentID in list_studentID:
data = self.database_connection.get_embeddings_student(studentID)
if data is not None:
data_image.extend(data)
return data_image
def write_result_csv_section(self, sectionID):
data = self.database_connection.get_statistic_remart_student(sectionID)
timestamp = int(datetime.timestamp(datetime.now()))
date = str(datetime.fromtimestamp(timestamp))
filename = os.path.join("file_writer", date + ".csv")
with open(filename, "w") as file:
writer = csv.writer(file, delimiter=',')
writer.writerow([
"STT", "MSV", "Tên sinh viên", "Môn học", 'số tín chỉ',
'tiết bắt đầu', 'ngày', 'điểm danh'
])
i = 1
if data is None:
return filename
for row in data:
data_write = []
data_write.extend([i])
data_write.extend(list(row))
writer.writerow(data_write)
i += 1
return filename
def get_information_group_from_teachID(self, teacherID):
return self.database_connection.get_information_group_from_teachID(
teacherID)
def get_group_from_teachID(self, teacherID):
data = self.database_connection.get_information_group_from_teachID(
teacherID)
map_check = {}
message = []
for result in data:
if map_check.get(result[1]) is None:
message.append(result)
map_check[result[1]] = 1
data_tmp = {}
data_tmp['groupID'] = []
data_tmp['groupName'] = []
data_tmp['subjectName'] = []
data_tmp['personName'] = []
data_tmp['credits'] = []
for sectionID, groupID, groupName, subjectName, credits, personName in message:
data_tmp['groupID'].append(groupID)
data_tmp['groupName'].append(groupName)
data_tmp['subjectName'].append(subjectName)
data_tmp['personName'].append(personName)
data_tmp['credits'].append(credits)
return data_tmp
def write_result_csv_group(self, groupID):
list_sectionID = self.database_connection.get_section_from_groupID(
groupID)
timestamp = int(datetime.timestamp(datetime.now()))
date = str(datetime.fromtimestamp(timestamp))
filename = os.path.join("file_writer", date + ".csv")
i = 0
data_map = {}
list_studentCode = []
with open(filename, "w") as file:
writer = csv.writer(file, delimiter=',')
if list_sectionID is None:
return filename
for sectionID in list_sectionID:
data = self.database_connection.get_statistic_remart_student(
sectionID)
if data is None:
continue
for elements in data:
if data_map.get(elements[0]) is None:
data_map[elements[0]] = []
list_studentCode.append(elements[0])
data_map[elements[0]].append(elements)
data_result = []
list_time = []
i = 1
for studentCode in list_studentCode:
tmp = []
index = 0
for elements in data_map.get(studentCode):
# print (elements)
if index == 0:
tmp = list(elements)[:4]
tmp.append(elements[-1])
else:
tmp.append(elements[-1])
index += 1
if i == 1:
list_time.append(
(elements[5] + " - tiết " + str(elements[4])))
data_result.append(tmp)
i += 1
title = ["STT", "MSV", "Tên sinh viên", "Môn học", 'số tín chỉ']
title.extend(list_time)
writer.writerow(title)
i = 1
for row in data_result:
data_write = []
data_write.extend([i])
data_write.extend(list(row))
writer.writerow(data_write)
i += 1
return filename
def get_seller_list(*args):
""" 가입된 모든 셀러 정보 리스트를 표출
유효성검사를 통과한 값을 request 에 넣어줌.
Args:
g.account_info: 데코레이터에서 넘겨받은 수정을 수행하는 계정 정보
g.account_info.seller_account_no: 검색 셀러 번호
g.account_info.account_no: 데코레이터에서 확인된 계정번호드
args: path parameter 를 통해서 들어온 검색 키워
Returns:
seller_list_result: 가입된 모든 셀러 및 셀러 세부 정보 리스트로 표출(seller_service 에서 받은 리턴 값.)
400: seller_service 로 값을 넘겨줄 때 애러가나면 400 리턴
500: database 연결에 실패하면 500리턴
Authors:
[email protected] (윤희철)
History:
2020-04-03 ([email protected]): 초기 생성
2020-04-07 ([email protected]): 파라미터 유효성검사 추가
2020-04-10 ([email protected]): 애러 처리 추가
2020-04-14 ([email protected]): offset 과 limit 도 유효성검사 실시
"""
# 유효성 확인 위해 기간 데이터 먼저 정의
start_time = args[10]
close_time = args[11]
# 두 값이 모두 들어왔을 때, 시작 기간이 종료 기간보다 늦으면 시작기간 = 종료기간
if start_time and close_time:
if start_time > close_time:
start_time = close_time
# validation 을 통과한 값을 담을 딕셔너리를 만들어줌.
valid_param = {}
# valid_param 딕셔너리에 validation 을 통과한 query parameter 을 넣어줌.
valid_param['seller_account_no'] = args[0]
valid_param['login_id'] = args[1]
valid_param['name_en'] = args[2]
valid_param['name_kr'] = args[3]
valid_param['brandi_app_user_id'] = args[4]
valid_param['manager_name'] = args[5]
valid_param['manager_email'] = args[6]
valid_param['seller_status'] = args[7]
valid_param['manager_contact_number'] = args[8]
valid_param['seller_type_name'] = args[9]
valid_param['start_time'] = start_time
valid_param['close_time'] = close_time
valid_param['excel'] = args[12]
valid_param['offset'] = args[13] if args[13] else 0
valid_param['limit'] = args[14] if args[14] else 10
# 유저 정보를 g에서 읽어와서 service 에 전달
user = g.account_info
# 데이터베이스 커넥션을 열어줌.
try:
db_connection = DatabaseConnection()
if db_connection:
seller_service = SellerService()
seller_list_result = seller_service.get_seller_list(
valid_param, user, db_connection)
return seller_list_result
else:
return jsonify({'message': 'NO_DATABASE_CONNECTION'}), 500
except Exception as e:
return jsonify({'message': f'{e}'}), 500
finally:
try:
db_connection.close()
except Exception as e:
return jsonify({'message': f'{e}'}), 500