def calc_badge(data):
"""
팀 뱃지
* 0 : '입문자' : 10판 플레이
1 : '세계정복' : 모든 채널에 봇이 초대됨
* 2 : '동작그만' : 게임취소 명령어를 1회 사용
* 3 : '게임중독' : 200판 플레이
4 : '만장일치' : 팀 내 모든 플레이어가 1회이상 게임 참여
개인 뱃지
0 : 'POTG' : 1등을 연속으로 3번 했을때
1 : '동반입대' : 특정플레이어와 2명이서 10판 이상 플레이
2 : '저승사자' : 연속 5번 1위
3 : '콩진호' : 22번 연속 2위
4 : '도와줘요 스피드웨건' : /help 명령어 1회 사용
"""
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
badgeRows = util.fetch_all_json(
db_manager.query(
"SELECT * "
"FROM TEAM_BADGE "
"WHERE "
"team_id = %s ", (teamId, )))
if check_badge_exist(badgeRows, 0) == False:
rows = util.fetch_all_json(
db_manager.query(
"SELECT COUNT(game_id) as game_num "
"FROM GAME_INFO "
"WHERE "
"team_id = %s", (teamId, )))
if rows[0]['game_num'] >= 10:
reward_badge(data, 0)
if check_badge_exist(badgeRows, 2) == False:
if data['text'] == static.GAME_COMMAND_EXIT:
reward_badge(data, 2)
if check_badge_exist(badgeRows, 3) == False:
rows = util.fetch_all_json(
db_manager.query(
"SELECT COUNT(game_id) as game_num "
"FROM GAME_INFO "
"WHERE "
"team_id = %s", (teamId, )))
if rows[0]['game_num'] >= 200:
reward_badge(data, 3)
return 0
def command_badge(data):
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
rows = util.fetch_all_json(
db_manager.query(
"SELECT * "
"FROM TEAM_BADGE "
"WHERE "
"team_id = %s ", (teamId, )))
resultString = ""
for row in rows:
resultString += static.getText(
static.CODE_TEXT_TEAM_BADGES[row['badge_id']], teamLang) + "\n"
slackApi.chat.postMessage({
'channel':
channelId,
'text':
'TEAM BADGES',
'attachments':
json.dumps([{
"text": resultString,
"fallback": "fallbacktext",
"callback_id": "wopr_game",
"color": "#2f35a3",
"attachment_type": "default",
}])
})
def command_rank(data):
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
print('rank_data' + str(data))
# 내 게임 결과들 가져오기
result = db_manager.query(
"SELECT user_id, MAX(score) as max_score, AVG(score) as avg_score, AVG(score) as recent_score "
"FROM GAME_RESULT "
"WHERE game_id in ( "
"SELECT GAME_INFO.game_id "
"FROM GAME_INFO "
"WHERE "
"GAME_INFO.channel_id = %s "
") "
"GROUP BY GAME_RESULT.user_id "
"order by avg_score DESC; ", (channelId, ))
rows = util.fetch_all_json(result)
# 출력할 텍스트 생성
result_string = ""
rank = 1
for row in rows:
result_string = result_string + (
static.getText(static.CODE_TEXT_RANK_FORMAT_3, teamLang) %
(pretty_rank(rank), "*" +
str(get_user_info(slackApi, row["user_id"])["user"]["name"]) +
"*", pretty_score(row["max_score"]), pretty_score(
row["avg_score"]), pretty_score(row["avg_score"])))
rank = rank + 1
# 10위 까지만 출력
if (rank == 11):
break
slackApi.chat.postMessage({
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_RANK, teamLang),
'as_user':
'******',
"attachments":
json.dumps([{
"title": static.getText(static.CODE_TEXT_RECORD, teamLang),
"text": result_string,
"mrkdwn_in": ["text", "pretext"],
"color": "#764FA5"
}])
})
def command_score(data):
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
# 게임 결과들 가져오기
result = db_manager.query(
"SELECT * from GAME_RESULT "
"RESULT inner join GAME_INFO INFO "
"on INFO.game_id = RESULT.game_id "
"inner join USER U "
"on U.user_id = RESULT.user_id "
"WHERE INFO.channel_id = %s "
"ORDER BY score desc;", (channelId, ))
rows = util.fetch_all_json(result)
result_string = ""
rank = 1
for row in rows:
logger_celery.info(row)
result_string = result_string + (
static.getText(static.CODE_TEXT_RANK_FORMAT_2, teamLang) %
(pretty_rank(rank), "*" +
str(get_user_info(slackApi, row["user_id"])["user"]["name"]) +
"*", pretty_score(row["score"]), row["answer_text"]))
rank = rank + 1
# 10위 까지만 출력
if (rank == 11):
break
slackApi.chat.postMessage({
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_SCORE, teamLang),
'as_user':
'******',
"attachments":
json.dumps([{
"title": static.getText(static.CODE_TEXT_RECORD, teamLang),
"text": result_string,
"mrkdwn_in": ["text", "pretext"],
"color": "#764FA5"
}])
})
def get_rand_game(channel, teamLang):
result = db_manager.query(
"SELECT * "
"FROM CHANNEL_PROBLEM "
"INNER JOIN (select *from PROBLEM where problem_language = %s) as pb on pb.problem_id = CHANNEL_PROBLEM.problem_id "
"WHERE CHANNEL_PROBLEM.problem_cnt = ( "
"SELECT MIN(CHANNEL_PROBLEM.problem_cnt) "
"FROM CHANNEL_PROBLEM "
"WHERE CHANNEL_PROBLEM.channel_id = %s "
"LIMIT 1 "
") "
"AND CHANNEL_PROBLEM.channel_id = %s "
"ORDER BY RAND() "
"LIMIT 1", (teamLang, channel, channel))
rows = util.fetch_all_json(result)
logger_celery.info('[problem_id]==> ' + str(rows))
if len(rows) == 0:
result = db_manager.query(
"SELECT * "
"FROM PROBLEM "
"where problem_language = %s "
"ORDER BY RAND() "
"LIMIT 1", (teamLang, ))
rows = util.fetch_all_json(result)
return rows[0]["problem_id"]
else:
db_manager.query(
"UPDATE CHANNEL_PROBLEM "
"SET `problem_cnt` = `problem_cnt` + 1 "
"WHERE "
"channel_id = %s and "
"problem_id = %s ", (channel, rows[0]["problem_id"]))
return rows[0]["problem_id"]
def open_socket(teamId, data):
redis_manager.redis_client.hset('rtm_status_' + teamId, 'status',
SOCKET_STATUS_CONNECTING)
redis_manager.redis_client.hset('rtm_status_' + teamId, 'expire_time',
time.time() + SOCKET_EXPIRE_TIME)
result = db_manager.query(
"SELECT team_bot_access_token "
"FROM TEAM "
"WHERE "
"team_id = %s "
"LIMIT 1", (teamId, ))
bot_token = util.fetch_all_json(result)[0]['team_bot_access_token']
_connect(teamId, bot_token, data)
def is_mission_clear(channel_id,game_id):
logger_celery.info('[MISSION]==>mission clear check')
logger_celery.info('[MISSION_channel_id]==>'+str(channel_id))
logger_celery.info('[MISSION_game_id]==>'+str(game_id))
global arr_cond_oper
arr_cond_oper = []
mission_condi = json.loads(redis_client.get(static.GAME_MISSION_CONDI+channel_id));
logger_celery.info('[MISSION_CONDI]==>'+str(mission_condi))
result = db_manager.query(
"select * from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id where gi.game_id = %s",
(game_id,)
)
rows = util.fetch_all_json(result)
user_num = len(rows)
mission_success_num = 0
#총 참여인원이 모자라라서 미션을 하지못하였다
if(check_enter_member(mission_condi,user_num)==False):
return static.GAME_MISSION_ABSENT
else:
logger_celery.info('[MISSION_RESULT]==> Enough member')
#미션조건으로부터 연산 조건을 뽑아와 arr_cond_oper에 넣는다.
check_conditions(mission_condi)
for oper in arr_cond_oper:
logger_celery.info('[MISSION_RESULT_oper]==> '+str(oper))
for row in rows:
chcked_user = checking_user(row,mission_condi)
#미션성공시+1 을 해준다.
if(chcked_user == True):
mission_success_num = mission_success_num + 1
logger_celery.info('[MISSION_RESULT_success_num]==> '+str(mission_success_num))
if(check_nec_member(mission_condi,mission_success_num,user_num)==True):
return static.GAME_MISSION_SUC
else:
return static.GAME_MISSION_FAILE
def command_typing(data):
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
gamemode = redis_client.get('game_mode_' + channelId)
if gamemode == "kok":
game_id = redis_client.get('kokgame_id_' + channelId)
users = redis_client.hgetall('kokusers_' + game_id)
sw = False
for key, value in users.items():
if key == data['user'] and value == "1":
sw = True
break
if sw == False:
print("=====================================")
return 0
# 부정 복사 판단
if static.CHAR_PASTE_ESCAPE in data['text']:
sendMessage(slackApi, channelId,
static.getText(static.CODE_TEXT_WARNING_PASTE, teamLang))
return
#미션일경우.
if (redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId) != 'None'):
#리버스 미션일경우.
if (int(redis_client.get(static.GAME_MISSION_NOTI_CODE +
channelId)) == static.GAME_MISSION_REVERSE):
logger_celery.info('[MISSION_REVERESE]')
reverse_text = ''.join(reversed(data["text"]))
logger_celery.info('[MISSION_REVERESE] ==> user:'******' problem' +
redis_client.get("problem_text_" + channelId))
distance = util.get_edit_distance(
reverse_text, redis_client.get("problem_text_" + channelId))
# 랜덤 swap일경우. 원래본문을 swap된것으로 바꿔서 비교한다.
elif (int(redis_client.get(static.GAME_MISSION_NOTI_CODE +
channelId)) == static.GAME_MISSION_SWAP):
logger_celery.info('[MISSION_SWAP]')
pre = redis_client.get(static.GAME_MISSION_SWAP_CHOSUNG +
channelId)
after = redis_client.get(static.GAME_MISSION_SWAP_AFTER +
channelId)
distance = util.get_edit_distance_for_swap(
data["text"], redis_client.get("problem_text_" + channelId),
pre, after)
else:
distance = util.get_edit_distance(
data["text"], redis_client.get("problem_text_" + channelId))
else:
distance = util.get_edit_distance(
data["text"], redis_client.get("problem_text_" + channelId))
start_time = redis_client.get("start_time_" + channelId)
current_time = time.time() * 1000
elapsed_time = (current_time - float(start_time)) * 1000
game_id = redis_client.get("game_id_" + channelId)
# 점수 계산
speed = round(util.get_speed(data["text"], elapsed_time), 3)
problem_text = redis_client.get("problem_text_" + channelId)
accuracy = round(
util.get_accuracy(max([data['text'], problem_text], key=len),
distance), 3)
score = util.get_score(speed, accuracy)
accuracy = accuracy * 100
logger_celery.info('distance : ' + str(distance))
logger_celery.info('speed : ' + str(speed))
logger_celery.info('elapsed_time : ' + str(elapsed_time))
logger_celery.info('accur : ' + str(accuracy))
logger_celery.info('text : ' + str(data["text"]))
result = db_manager.query(
"SELECT game_id "
"FROM GAME_RESULT "
"WHERE "
"game_id = %s and user_id = %s "
"LIMIT 1", (game_id, data["user"]))
rows = util.fetch_all_json(result)
if len(rows) == 0:
# 게임 결과 저장
db_manager.query(
"INSERT INTO GAME_RESULT "
"(game_id, user_id, answer_text, score, speed, accuracy, elapsed_time) "
"VALUES"
"(%s, %s, %s, %s, %s, %s, %s)",
(game_id, data["user"], data["text"].encode('utf-8'), score, speed,
accuracy, elapsed_time))
user_name = get_user_info(slackApi, data["user"])["user"]["name"]
try:
#이후 채널 랭크 업데이트.
result = db_manager.query(
"SELECT * , "
"( "
"SELECT count(*) "
"FROM ( "
"SELECT user_id,avg(score) as scoreAvgUser FROM GAME_RESULT GROUP BY user_id order by scoreAvgUser desc "
") "
"userScoreTB "
") as userAllCnt "
"FROM ( "
"SELECT @counter:=@counter+1 as rank ,userScoreTB.user_id,userScoreTB.scoreAvgUser as average "
"FROM ( "
" SELECT user_id,avg(score) as scoreAvgUser FROM GAME_RESULT GROUP BY user_id order by scoreAvgUser desc "
") "
"userScoreTB "
"INNER JOIN (SELECT @counter:=0) b "
") as rankTB where user_id = %s ", (data["user"], ))
rows = util.fetch_all_json(result)
userAll = rows[0]["userAllCnt"]
rank = rows[0]["rank"]
levelHirechy = rank / userAll * 100
level = 3
#100~91
if levelHirechy > 90:
level = 1
#90~71
elif levelHirechy > 70 and levelHirechy < 91:
level = 2
#70~31
elif levelHirechy > 30 and levelHirechy < 81:
level = 3
#30~10
elif levelHirechy > 10 and levelHirechy < 31:
level = 4
#10~0
elif levelHirechy > -1 and levelHirechy < 11:
level = 5
#이후 채널 랭크 업데이트.
result = db_manager.query(
"UPDATE USER SET user_level = %s WHERE user_id = %s",
(level, data["user"]))
except Exception as e:
logger_celery.error(str(e))
try:
result = db_manager.query(
"SELECT user_id "
"FROM USER "
"WHERE "
"user_id = %s "
"LIMIT 1", (data["user"], ))
rows = util.fetch_all_json(result)
if len(rows) == 0:
db_manager.query(
"INSERT INTO USER "
"(team_id, user_id, user_name) "
"VALUES "
"(%s, %s, %s) ", (teamId, data["user"], user_name))
except exc.SQLAlchemyError as e:
logger_celery.error("[DB] err==>" + str(e))
def command_myscore(data):
teamId = data["team_id"]
teamLang = util.get_team_lang(teamId)
channelId = data['channel']
userId = data["user"]
slackApi = util.init_slackapi(teamId)
# user_name 가져오기
user_info = get_user_info(slackApi, userId)
user_name = user_info['user']['name']
# 내 게임 결과들 가져오기
result = db_manager.query(
"SELECT * , ( "
"SELECT COUNT(*) + 1 "
"FROM GAME_RESULT "
"WHERE "
"score > a.score and "
"game_id = a.game_id"
") as rank "
"FROM GAME_RESULT as a "
"WHERE "
"user_id = %s order by score desc;", (userId, ))
rows = util.fetch_all_json(result)
# 해당 팀의 언어설정 가져오기
result_team_lang = db_manager.query(
"SELECT * FROM TEAM "
"WHERE "
"team_id = %s;", (teamId, ))
team_rows = util.fetch_all_json(result_team_lang)
teamLang = team_rows[0]['team_lang']
result_myscore_announcements = db_manager.query(
"SELECT * FROM slackbot.MY_SCORE_ANNOUNCEMENT "
"WHERE "
"language = %s;", (teamLang, ))
myscore_announcements = util.fetch_all_json(result_myscore_announcements)
myscore_announcement = myscore_announcements[int(
len(myscore_announcements) * random.random())]['announcement']
# 출력할 텍스트 생성
result_string = "Name : " + "*" + user_name + "*" + "\n"
result_string = result_string + myscore_announcement + "\n"
rank = 1
for row in rows:
result_string = result_string + (
static.getText(static.CODE_TEXT_RANK_FORMAT_1, teamLang) %
(pretty_rank(rank), pretty_score(row["score"]),
pretty_accur(row["accuracy"]), pretty_speed(
row["speed"]), " " + pretty_speed(row["rank"]) + " "))
rank = rank + 1
# 10위 까지만 출력
if (rank == 11):
break
slackApi.chat.postMessage({
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_MY_SCORE, teamLang),
'as_user':
'******',
"attachments":
json.dumps([{
"title": static.getText(static.CODE_TEXT_RECORD, teamLang),
"text": result_string,
"mrkdwn_in": ["text", "pretext"],
"color": "#764FA5"
}])
})
def command_start(data, round=0):
teamId = data["team_id"]
channelId = data['channel']
slackApi = util.init_slackapi(teamId)
teamLang = util.get_team_lang(teamId)
result = db_manager.query("select *from GAME_INFO where channel_id = %s",
(channelId, ))
rows = util.fetch_all_json(result)
if len(rows) == 0:
#게임 카운터를 1로 설정한다.
redis_client.set(static.GAME_MANAGER_PLAY_COUNTER + channelId, '1')
logger_celery.info('start')
if not is_channel_has_bot(teamId, channelId):
redis_client.set("status_" + channelId, static.GAME_STATE_IDLE)
#게임 카운터를 1로 설정한다.
redis_client.set(static.GAME_MANAGER_PLAY_COUNTER + channelId, '1')
print("First !! =>" +
redis_client.get(static.GAME_MANAGER_PLAY_COUNTER + channelId))
slackApi.chat.postMessage({
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_BOT_NOTFOUND, teamLang),
'as_user':
'******',
"attachments":
json.dumps([{
"text":
static.getText(static.CODE_TEXT_INVITE_BOT, teamLang),
"fallback":
"fallbacktext",
"callback_id":
"wopr_game",
"color":
"#3AA3E3",
"attachment_type":
"default",
"actions": [{
"name":
"invite_bot",
"text":
static.getText(static.CODE_TEXT_INVITE, teamLang),
"type":
"button",
"value":
"invite_bot",
"confirm": {
"title":
static.getText(static.CODE_TEXT_INVITE_ASK, teamLang),
"text":
static.getText(static.CODE_TEXT_CAN_REMOVE, teamLang),
"ok_text":
static.getText(static.CODE_TEXT_OPTION_INVITE,
teamLang),
"dismiss_text":
static.getText(static.CODE_TEXT_OPTION_LATER,
teamLang),
}
}]
}])
})
return
redis_client.set("status_" + channelId, static.GAME_STATE_STARTING)
# 채널 정보가 DB에 있는지 SELECT문으로 확인 후 없으면 DB에 저장
result = db_manager.query(
"SELECT * FROM slackbot.CHANNEL WHERE slackbot.CHANNEL.channel_id = %s;",
(data['channel'], ))
# DB에 채널 정보가 없다면
if (result.fetchone() is None):
ctime = datetime.datetime.now()
# 채널 이름 가져오기
channel_list = get_channel_list(slackApi)
logger_celery.info(channel_list)
channels = channel_list['channels']
channel_name = ""
for channel_info in channels:
# id가 같으면 name을 가져온다/
if (channel_info['id'] == data['channel']):
channel_name = channel_info['name']
try:
db_manager.query(
"INSERT INTO CHANNEL"
"(team_id, channel_id, channel_name, channel_joined_time)"
"VALUES"
"(%s, %s, %s, %s);",
(teamId, data['channel'], channel_name, ctime))
result = db_manager.query("SELECT * from PROBLEM")
rows = util.fetch_all_json(result)
arrQueryString = []
arrQueryString.append(
'INSERT INTO CHANNEL_PROBLEM (channel_id,problem_id) values ')
for row in rows:
arrQueryString.append('("' + data['channel'] + '","' +
str(row['problem_id']) + '")')
arrQueryString.append(',')
arrQueryString.pop()
lastQuery = "".join(arrQueryString)
result = db_manager.query(lastQuery)
except Exception as e:
logger_celery.error('error : ' + str(e))
#만약 미션이 선택되었다면?!
#미션에해당하는 멘트들을 가져오고 해당 멘트를 레디스에서 긁어와서 메시지로 뿌려준다.
if (mission_manager.pickUpGameEvent(data['channel'],
teamId) == static.GAME_TYPE_MISSION):
logger_celery.info('[MISSION]==>START!')
sendMessage(
slackApi, channelId,
redis_client.get(static.GAME_MISSION_NOTI + data['channel']))
logger_celery.info('[MISSION_CONDI_inREDSI]==> ' +
static.GAME_MISSION_CONDI + data['channel'])
# print(redis_client.get(static.GAME_MISSION_CONDI+data['channel']))
# 문제들 가져오기
texts = util.get_problems(teamLang)
logger_celery.info('[LANG_TEAM]==> ' + teamLang)
# 문제 선택하기
problem_id = get_rand_game(data['channel'], teamLang)
logger_celery.info('[problem_id]==> ' + str(problem_id))
problem_text = texts[problem_id]
#미션인지확인하고,
if (redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId) != 'None'):
# 랜덤 초이스인경우엔
if (int(redis_client.get(static.GAME_MISSION_NOTI_CODE +
channelId)) == static.GAME_MISSION_SWAP):
randomChar = mission_manager.mission_swap_get_Random_Chosung(
problem_text, channelId, teamLang)
sendMessage(
slackApi, channelId,
mission_manager.mission_swap_get_options_centence(
randomChar, channelId, teamLang))
titleResponse = sendMessage(slackApi, channelId,
util.get_start_centence(teamLang))
# sendMessage(slackApi, channelId, util.get_start_centence(teamLang))
response = sendMessage(slackApi, channelId,
static.getText(static.CODE_TEXT_COUNT_1, teamLang))
text_ts = response['ts']
title_ts = titleResponse['ts']
time.sleep(1)
strs = [
static.getText(static.CODE_TEXT_COUNT_2, teamLang),
static.getText(static.CODE_TEXT_COUNT_3, teamLang)
]
logger_celery.info('strs => ' + str(strs))
for i in range(0, 2):
slackApi.chat.update({
"ts": text_ts,
"channel": channelId,
"text": strs[i],
'as_user': '******'
})
time.sleep(1.0)
slackApi.chat.update({
"ts":
text_ts,
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_SUGGEST_PROBLEM, teamLang) %
(static.CHAR_PASTE_ESCAPE.join(problem_text)),
'as_user':
'******'
})
redis_client.set("status_" + channelId,
static.GAME_STATE_PLAYING) # 현재 채널 상태 설정
redis_client.set(
"start_time_" + channelId,
time.time() * 1000,
) # 시작 시간 설정
redis_client.set("problem_text_" + channelId, problem_text) # 해당 게임 문자열 설정
redis_client.set("problem_id_" + channelId, problem_id) # 해당 게임 문자열 설정
redis_client.set("game_id_" + channelId,
util.generate_game_id()) # 현재 게임의 ID
#threading.Timer(10, game_end, [slackApi, teamId, channelId, title_ts]).start()
timeout = util.get_time(problem_text)
for i in range(1, timeout):
stTime = time.time()
slackApi.chat.update({
"ts":
title_ts,
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_START_GAME_COUNT, teamLang) %
(str(timeout - i)),
'as_user':
'******'
})
if time.time() - stTime <= 1:
time.sleep(1 - (time.time() - stTime))
slackApi.chat.update({
"ts":
title_ts,
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_START_GAME_END, teamLang),
'as_user':
'******'
})
game_end(slackApi, data, round)
def game_end(slackApi, data, round=0):
teamId = data['team_id']
channelId = data['channel']
teamLang = util.get_team_lang(teamId)
sendMessage(slackApi, channelId,
static.getText(static.CODE_TEXT_GAME_DONE, teamLang))
start_time = redis_client.get("start_time_" + channelId)
game_id = redis_client.get("game_id_" + channelId)
problem_id = redis_client.get("problem_id_" + channelId)
logger_celery.info(start_time)
start_time_to_time_tamp = datetime.datetime.utcfromtimestamp(
(float(start_time) / 1000) +
(9 * 3600)).strftime('%Y-%m-%d %H:%M:%S.%f')
# 현재 상태 변경
redis_client.set("status_" + channelId, static.GAME_STATE_CALCULATING)
sendMessage(slackApi, channelId,
static.getText(static.CODE_TEXT_CALC_SCORE, teamLang))
time.sleep(2)
# 참여유저수 query로 가져오기
result = db_manager.query(
"SELECT * FROM slackbot.GAME_RESULT WHERE slackbot.GAME_RESULT.game_id = %s;",
(game_id, ))
# 가져온 쿼리 결과로 user_num을 계산
rows = util.fetch_all_json(result)
user_num = len(rows)
ctime = datetime.datetime.now()
db_manager.query(
"INSERT INTO GAME_INFO "
"(game_id, channel_id, team_id, start_time, end_time, problem_id, user_num)"
"VALUES"
"(%s, %s, %s, %s, %s, %s, %s) ",
(game_id, channelId, teamId, start_time_to_time_tamp, ctime,
problem_id, user_num))
result = db_manager.query(
"SELECT * FROM GAME_RESULT "
"WHERE game_id = %s order by score desc", (game_id, ))
rows = util.fetch_all_json(result)
logger_celery.info('game result orow' + str(rows))
#참여인원에대한 플래그는 하상 0 이다.
redis_client.set(static.GAME_MISSION_FLG_MIN_MEMBER + channelId, 0)
# #none이아니면 미션이라는 이야기이다.
# logger_celery.info('isMission? '+redis_client.get(static.GAME_MISSION_NOTI_CODE+ channelId))
# logger_celery.info(redis_client.get(static.GAME_MISSION_NOTI_CODE+ channelId)!='None')
# logger_celery.info(redis_client.get(static.GAME_MISSION_NOTI_CODE+ channelId) is not None)
if (str(redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId)) !=
'None'):
# code가 102인경우 ==> 1등과 2등을 바꿔져야한다.
# 단 로우가 2이상일경우에만..
if (int(redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId))
== static.GAME_MISSION_SENCONDORY and len(rows) > 1):
redis_client.set(static.GAME_MISSION_FLG_MIN_MEMBER + channelId, 1)
first_user_id = rows[0]['user_id']
second_user_id = rows[1]['user_id']
# logger_celery.info('[MISSION_SECONDORY] UPDATE 1th = '+get_user_info(slackApi,first_user_id)["user"]["name"] +' 2th = '+get_user_info(slackApi,second_user_id)["user"]["name"])
#1등자리에 2등을 넣고
try:
#first에 #을붙인다.
db_manager.query(
"UPDATE GAME_RESULT "
"set user_id = %s where game_id = %s and user_id = %s ",
(first_user_id + '#', game_id, first_user_id))
#그리고 second에 first를 넣고
db_manager.query(
"UPDATE GAME_RESULT "
"set user_id = %s where game_id = %s and user_id = %s ",
(first_user_id, game_id, second_user_id))
db_manager.query(
"UPDATE GAME_RESULT "
"set user_id = %s where game_id = %s and user_id = %s ",
(second_user_id, game_id, first_user_id + '#'))
except Exception as e:
logger_celery.error(str(e))
else:
redis_client.set(static.GAME_MISSION_FLG_MIN_MEMBER + channelId, 0)
result = db_manager.query(
"SELECT * FROM GAME_RESULT "
"WHERE game_id = %s order by score desc", (game_id, ))
rows = util.fetch_all_json(result)
result_string = ""
rank = 1
if data['mode'] == "kok":
kokgame_id = redis_client.get('kokgame_id_' + channelId)
users = redis_client.hgetall('kokusers_' + kokgame_id)
for key, value in users.items():
redis_client.hset("kokusers_" + kokgame_id, key, "0")
for row in rows:
result_string = result_string + (
static.getText(static.CODE_TEXT_RANK_FORMAT_4, teamLang) %
(pretty_rank(rank),
str(get_user_info(slackApi, row["user_id"])["user"]["name"]),
str(int(row["score"])), pretty_accur(
row["accuracy"]), str(int(row["speed"]))))
rank = rank + 1
if data['mode'] == "kok":
if rank == len(rows) + 1:
redis_client.hset("kokusers_" + kokgame_id, row["user_id"],
"0")
else:
redis_client.hset("kokusers_" + kokgame_id, row["user_id"],
"1")
sendResult = str(result_string)
logger_celery.info(channelId)
slackApi.chat.postMessage({
"channel":
channelId,
"text":
static.getText(static.CODE_TEXT_GAME_RESULT, teamLang),
'as_user':
'******',
"attachments":
json.dumps([{
"title": static.getText(static.CODE_TEXT_RECORD, teamLang),
"text": sendResult,
"mrkdwn_in": ["text", "pretext"],
"color": "#764FA5"
}])
})
#### 게임이 끝나고 미션 클리어했는지 판단해주는 로직이다.
#none이아니면 미션이라는 이야기이다.
if (redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId) != 'None'):
#code가 100보다작을경우 => genral한 미션일경우다
if (int(redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId)) <
100):
mission_result = mission_manager.is_mission_clear(
channelId, game_id)
if (mission_result == static.GAME_MISSION_ABSENT):
logger_celery.info('[MISSION_RESULT]==> notEnough member')
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_MIN_MEMBER,
teamLang))
elif (mission_result == static.GAME_MISSION_SUC):
logger_celery.info('[MISSION_RESULT]==> MISSION SUCCESS')
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_SUCCESS,
teamLang))
elif (mission_result == static.GAME_MISSION_FAILE):
logger_celery.info('[MISSION_RESULT]==> MISSION FAILE')
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_FAIL,
teamLang))
#Random일 경우
elif (int(redis_client.get(static.GAME_MISSION_NOTI_CODE +
channelId)) == static.GAME_MISSION_REVERSE):
logger_celery.info('[MISSION_RESULT]==> REVERSE MISSION END')
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_REVERSE,
teamLang))
#2등만보여줄경우.
elif (int(redis_client.get(static.GAME_MISSION_NOTI_CODE + channelId))
== static.GAME_MISSION_SENCONDORY):
if (int(
redis_client.get(static.GAME_MISSION_FLG_MIN_MEMBER +
channelId)) == 1):
logger_celery.info('[MISSION_RESULT]==> SECONDORY MISSION END')
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_SECONDORY,
teamLang))
elif (int(
redis_client.get(static.GAME_MISSION_FLG_MIN_MEMBER +
channelId)) == 0):
logger_celery.info(
'[MISSION_RESULT]==> SECONDORY MISSION END not enough member'
)
sendMessage(
slackApi, channelId,
static.getText(static.CODE_TEXT_MISSION_RESULT_MIN_MEMBER,
teamLang))
# mission_manager.mission_reverse_typing()
#게임한것이 10개인지 판단 하여 채널 레벨을 업데이트 시켜준다.
try:
result = db_manager.query(
"SELECT IF(COUNT(*)>10,true,false) as setUpChannelLevel "
"FROM GAME_INFO as gi WHERE channel_id = %s "
"ORDER BY gi.start_time DESC LIMIT 10", (channelId, ))
rows = util.fetch_all_json(result)
logger_celery.info(rows)
# 레벨을 산정한다.
if rows[0]['setUpChannelLevel'] == 1:
logger_celery.info("true")
result = db_manager.query(
"SELECT u.user_id,u.user_level FROM ( "
"SELECT * FROM GAME_INFO as gi WHERE channel_id = %s ORDER BY gi.start_time DESC LIMIT 10 "
") as recentGameTB "
"inner join GAME_RESULT as gr on recentGameTB.game_id = gr.game_id "
"inner join USER as u on u.user_id = gr.user_id group by u.user_id ",
(channelId, ))
rows = util.fetch_all_json(result)
logger_celery.info(rows)
levelSum = 0
for row in rows:
levelSum = row["user_level"]
logger_celery.info(levelSum)
#이후 반올림하여 채널랭크를 측정.
channelRank = round(levelSum / len(row))
#이후 채널 랭크 업데이트.
result = db_manager.query(
"UPDATE CHANNEL SET channel_level = %s WHERE channel_id = %s",
(channelRank, channelId))
#아무일도일어나지 않는다.
else:
logger_celery.info("false")
except Exception as e:
logger_celery.error(str(e))
# 현재 상태 변경
redis_client.set("status_" + channelId, static.GAME_STATE_IDLE)
if data['mode'] == "kok":
print("start next round")
start_kok(data, round + 1)
badge_manager.calc_badge(data)
# if(redis_client.get(static.GAME_MANAGER_PLAY_COUNTER+channelId) is not None):
game_cnt = int(
redis_client.get(static.GAME_MANAGER_PLAY_COUNTER + channelId))
#6보다 카운트가작으면 ++
if (game_cnt < 7):
redis_client.set(static.GAME_MANAGER_PLAY_COUNTER + channelId,
str(game_cnt + 1))
#6이면 마지막이니까 kok알림.
elif (game_cnt == 7):
time.sleep(3)
slackApi.chat.postMessage({
'channel':
channelId,
'text':
static.getText(static.CODE_TEXT_GUID_KOK, teamLang)
})
redis_client.set(static.GAME_MANAGER_PLAY_COUNTER + channelId,
str(game_cnt + 1))
def pickUpGameEvent(channelId,teamId):
logger_celery.info('[MISSION]==>pickUp GameEvent')
teamLang = util.get_team_lang(teamId)
#기존 레디스정보가있다면 None 로 초기화시켜라 ==> None 은 없는것이나 마찬가지
# redis_client.set(static.GAME_MISSION_ID + channelId, None)
redis_client.set(static.GAME_MISSION_NOTI + channelId, 'None')
redis_client.set(static.GAME_MISSION_CONDI + channelId, 'None')
redis_client.set(static.GAME_MISSION_TYPE + channelId, 'None')
redis_client.set(static.GAME_MISSION_NOTI_CODE + channelId,'None')
#첫판일경우 100
play_cnt = redis_client.get(static.GAME_MANAGER_PLAY_COUNTER+channelId)
if( play_cnt == '1' or play_cnt == '2'):
play_mission_per = 0
play_mission_general = 'None'
play_mission_id = 'None'
elif(play_cnt == '3'):
play_mission_per = 100
play_mission_general = 0
play_mission_id = 101
elif(play_cnt == '4'):
play_mission_per = 100
play_mission_general = 0
play_mission_id = 103
elif(play_cnt == '5'):
play_mission_per = 100
play_mission_general = 0
play_mission_id = 102
elif(play_cnt == '6'):
play_mission_per = 100
play_mission_general = 100
play_mission_id = 1
elif(play_cnt == '7'):
play_mission_per = 100
play_mission_general = 100
play_mission_id = 2
else:
play_mission_per = 50
play_mission_general = 50
play_mission_id = 'None'
print(' playCnt => ' +str(play_cnt)+' missionper => '+str(play_mission_per)+ ' play_general=> '+str(play_mission_general)+' play_di => '+str(play_mission_id))
#미션실행 모드이다.
#현재 테스트용으로 50% 확률로 미션게임이 나오도록 작업하였다.
if util.getRandomPercent(play_mission_per) :
#다시 50% 확률로 general/special 한 미션이 나온다.
if util.getRandomPercent(play_mission_general) :
logger_celery.info('[MISSION]==> general Mission')
if(play_cnt=='6' or play_cnt=='7'):
result = db_manager.query(
"select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='general' and mission_noti_code =%s",
(teamLang,play_mission_id)
)
else:
result = db_manager.query(
"select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='general' ORDER BY rand() LIMIT 1 ",
(teamLang,)
)
rows = util.fetch_all_json(result)
mission_noti_code = rows[0]['mission_noti_code']
mission_noti = rows[0]['mission_noti']
mission_condi = rows[0]['condi'];
mission_type = rows[0]['type'];
redis_client.set(static.GAME_MISSION_NOTI_CODE + channelId,mission_noti_code)
redis_client.set(static.GAME_MISSION_TYPE + channelId,mission_type)
redis_client.set(static.GAME_MISSION_NOTI + channelId,mission_noti)
redis_client.set(static.GAME_MISSION_CONDI + channelId,mission_condi)
else:
logger_celery.info('[MISSION]==> special Mission')
if(play_cnt=='3' or play_cnt=='4' or play_cnt=='5'):
result = db_manager.query(
# "select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='special' ORDER BY rand() LIMIT 1 ",
"select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='special' and mission_noti_code =%s",
(teamLang,play_mission_id)
)
else:
result = db_manager.query(
"select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='special' ORDER BY rand() LIMIT 1 ",
# "select *from GAME_MISSION_NOTI as gnoti inner join GAME_MISSION_INFO as ginfo on gnoti.id = ginfo.mission_noti_code where ginfo.validity = 1 and gnoti.lang =%s and type ='special' and mission_noti_code =103",
(teamLang,)
)
rows = util.fetch_all_json(result)
print(rows)
mission_noti_code = rows[0]['mission_noti_code']
mission_noti = rows[0]['mission_noti']
mission_type = rows[0]['type'];
redis_client.set(static.GAME_MISSION_NOTI_CODE + channelId,mission_noti_code)
redis_client.set(static.GAME_MISSION_TYPE + channelId,mission_type)
redis_client.set(static.GAME_MISSION_NOTI + channelId,mission_noti)
return static.GAME_TYPE_MISSION
#노말 모드이다.
else :
logger_celery.info('[MISSION]==>NOPE! just Normal mode')
return static.GAME_TYPE_NORMAL
def get(self, types):
if types == "getAllUser":
try:
print("[ADMIN]_GET_ALLUSER")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT slackbot.USER.user_id, slackbot.USER.user_name, slackbot.TEAM.team_name, slackbot.TEAM.team_id, MAX(slackbot.GAME_INFO.start_time) latest_time "
"FROM slackbot.USER "
"INNER JOIN slackbot.TEAM ON slackbot.USER.team_id = slackbot.TEAM.team_id "
"INNER JOIN slackbot.GAME_RESULT ON slackbot.USER.user_id = slackbot.GAME_RESULT.user_id "
"INNER JOIN slackbot.GAME_INFO ON slackbot.GAME_INFO.game_id = slackbot.GAME_RESULT.game_id "
"GROUP BY user_id;")
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getGameResult":
try:
channel_id = request.args.get('channel_id')
print("[ADMIN]getGameResult With channelID")
conn = db_manager.engine.connect()
result = ""
# print("channelID= "+channel_id)
if channel_id == None:
result = conn.execute("SELECT * FROM GAME_RESULT ")
else:
result = conn.execute(
"SELECT GAME_RESULT.* FROM GAME_INFO "
"inner join GAME_RESULT on GAME_INFO.game_id = GAME_RESULT.game_id where GAME_INFO.channel_id = %s",
(channel_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getTeamInfo":
try:
teamId = request.args.get('teamId')
print("[ADMIN]getTeamInfo With channelID")
conn = db_manager.engine.connect()
result = ""
# print("channelID= "+channel_id)
result = conn.execute(
"SELECT *, ( "
" SELECT "
" count(user_id) "
" FROM USER "
" where "
" USER.team_id = TEAM.team_id "
") as user_num, "
"( "
" SELECT "
" count(team_id) "
" FROM GAME_INFO "
" where "
" GAME_INFO.team_id = TEAM.team_id "
") as game_num, "
"( "
" SELECT "
" AVG(score) "
" FROM GAME_RESULT "
" INNER JOIN USER "
" ON USER.user_id = GAME_RESULT.user_id "
" where "
" USER.team_id = TEAM.team_id "
") as avg_score, "
"( "
" SELECT "
" MAX(score) "
" FROM GAME_RESULT "
" INNER JOIN USER "
" ON USER.user_id = GAME_RESULT.user_id "
" where "
" USER.team_id = TEAM.team_id "
") as max_score, "
"( "
" SELECT "
" MAX(start_time) "
" FROM GAME_INFO "
" where "
" GAME_INFO.team_id = TEAM.team_id "
") as recent_play_time "
"from TEAM "
"where TEAM.team_id = %s; ", teamId)
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getAllTeam":
try:
print("[ADMIN]_GET_ALL team")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT *, ( "
" SELECT "
" count(user_id) "
" FROM USER "
" where "
" USER.team_id = TEAM.team_id "
") as user_num, "
"( "
" SELECT "
" count(team_id) "
" FROM GAME_INFO "
" where "
" GAME_INFO.team_id = TEAM.team_id "
") as game_num, "
"( "
" SELECT "
" AVG(score) "
" FROM GAME_RESULT "
" INNER JOIN USER "
" ON USER.user_id = GAME_RESULT.user_id "
" where "
" USER.team_id = TEAM.team_id "
") as avg_score, "
"( "
" SELECT "
" MAX(score) "
" FROM GAME_RESULT "
" INNER JOIN USER "
" ON USER.user_id = GAME_RESULT.user_id "
" where "
" USER.team_id = TEAM.team_id "
") as max_score, "
"( "
" SELECT "
" MAX(start_time) "
" FROM GAME_INFO "
" where "
" GAME_INFO.team_id = TEAM.team_id "
") as recent_play_time "
"from TEAM ; ")
conn.close()
rows = util.fetch_all_json(result)
print(rows)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getAllChannel":
try:
team_id = request.args.get('teamId')
print("[ADMIN]_GET_ALLChannel")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT * ,"
"( "
" SELECT "
" MAX(start_time) "
" FROM GAME_INFO "
" WHERE "
" GAME_INFO.channel_id = CHANNEL.channel_id "
") as recent_play_time "
"FROM CHANNEL "
"WHERE team_id = %s ", (team_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getChannelInfo":
try:
channel_id = request.args.get('channelId')
print("[ADMIN]_GET_ALLChannel")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT CHANNEL.channel_id, CHANNEL.channel_name, CHANNEL.channel_joined_time,"
"( "
" SELECT "
" MAX(GAME_INFO.start_time) "
" FROM GAME_INFO "
" WHERE "
" GAME_INFO.channel_id = CHANNEL.channel_id "
") as recent_play_time "
"FROM CHANNEL "
"WHERE channel_id = %s ", (channel_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getAllGame":
try:
channel_id = request.args.get('channelId')
print("[ADMIN]_GET_ALLChannel")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT * ,"
"( "
" SELECT problem_text "
" FROM PROBLEM "
" WHERE PROBLEM.problem_id = GAME_INFO.problem_id"
") as problem_text, "
"( "
" SELECT MAX(score) "
" FROM GAME_RESULT "
" WHERE GAME_RESULT.game_id = GAME_INFO.game_id"
") as max_score, "
"( "
" SELECT AVG(score) "
" FROM GAME_RESULT "
" WHERE GAME_RESULT.game_id = GAME_INFO.game_id"
") as avg_score "
"FROM GAME_INFO "
"WHERE "
" channel_id=%s", (channel_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getAllGameResult":
try:
game_id = request.args.get('gameId')
print("[ADMIN]_GET_ALL Games")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT * ,"
"( "
" SELECT user_name"
" FROM USER"
" WHERE"
" USER.user_id = GAME_RESULT.user_id"
") as user_name "
"FROM GAME_RESULT "
"WHERE game_id=%s ", (game_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getGameInfo":
try:
game_id = request.args.get('gameId')
print("[ADMIN]_GET_ALL Games")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT * ,"
"( "
" SELECT count(user_id)"
" FROM GAME_RESULT"
" WHERE"
" GAME_RESULT.game_id = GAME_INFO.game_id"
") as user_num, "
"( "
" SELECT MAX(score)"
" FROM GAME_RESULT"
" WHERE"
" GAME_RESULT.game_id = GAME_INFO.game_id"
") as max_score, "
"( "
" SELECT AVG(score)"
" FROM GAME_RESULT"
" WHERE"
" GAME_RESULT.game_id = GAME_INFO.game_id"
") as avg_score, "
"( "
" SELECT problem_text"
" FROM PROBLEM"
" WHERE"
" PROBLEM.problem_id = GAME_INFO.problem_id"
") as problem_text "
"FROM GAME_INFO "
"WHERE game_id=%s ", (game_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getAllProblem":
try:
print("Get All Problems")
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT slackbot.PROBLEM.problem_id, "
"slackbot.PROBLEM.problem_text, "
"AVG(slackbot.GAME_RESULT.accuracy) AVG_OF_ACC, "
"AVG(slackbot.GAME_RESULT.speed)AVG_OF_SPD, "
"slackbot.PROBLEM.difficulty, "
"slackbot.PROBLEM.validity "
"FROM slackbot.PROBLEM "
"LEFT OUTER JOIN slackbot.GAME_INFO ON slackbot.PROBLEM.problem_id = slackbot.GAME_INFO.problem_id "
"LEFT OUTER JOIN slackbot.GAME_RESULT ON slackbot.GAME_INFO.game_id = slackbot.GAME_RESULT.game_id "
"GROUP By slackbot.PROBLEM.problem_id;")
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getSpecificUserInfoById":
try:
user_id = request.args.get('user_id')
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT slackbot.USER.user_id, slackbot.USER.user_name, slackbot.TEAM.team_name, slackbot.TEAM.team_id, MAX(slackbot.GAME_INFO.start_time) latest_time "
"FROM slackbot.USER "
"INNER JOIN slackbot.TEAM ON slackbot.USER.team_id = slackbot.TEAM.team_id "
"INNER JOIN slackbot.GAME_RESULT ON slackbot.USER.user_id = slackbot.GAME_RESULT.user_id "
"INNER JOIN slackbot.GAME_INFO ON slackbot.GAME_INFO.game_id = slackbot.GAME_RESULT.game_id "
"GROUP BY user_id "
"HAVING slackbot.USER.user_id = %s;", (user_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getSpecificProblemInfoById":
try:
problem_id = request.args.get('problem_id')
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT * FROM slackbot.PROBLEM where slackbot.PROBLEM.problem_id = %s;",
(problem_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getSpecificUserGameResultById":
try:
user_id = request.args.get('user_id')
conn = db_manager.engine.connect()
result = conn.execute(
"SELECT slackbot.USER.user_id"
", slackbot.GAME_RESULT.game_id"
", slackbot.PROBLEM.problem_text"
", slackbot.GAME_RESULT.answer_text"
", slackbot.GAME_INFO.start_time"
", slackbot.GAME_INFO.end_time"
", slackbot.GAME_RESULT.score"
", slackbot.GAME_RESULT.accuracy"
", slackbot.GAME_RESULT.speed"
", slackbot.GAME_RESULT.elapsed_time "
"FROM slackbot.GAME_RESULT "
"INNER JOIN slackbot.GAME_INFO ON slackbot.GAME_RESULT.game_id = slackbot.GAME_INFO.game_id "
"INNER JOIN slackbot.PROBLEM ON slackbot.GAME_INFO.problem_id = slackbot.PROBLEM.problem_id "
"INNER JOIN slackbot.USER ON slackbot.USER.user_id = slackbot.GAME_RESULT.user_id "
"WHERE slackbot.USER.user_id = %s;", (user_id))
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
def get(self, types):
if types == "getIndicator":
try:
data = {}
print("[DashBoard]_GETINDICATOR")
conn = db_manager.engine.connect()
todayInstall = conn.execute(
"select count(*) as todayInstall from TEAM WHERE DATE(TEAM.team_joined_time) = CURDATE()"
)
todayPlay = conn.execute(
"select count(*) as todayPlay from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id WHERE DATE(gi.start_time) = CURDATE() "
)
inActiveUsers = conn.execute(
"select gr.user_id as userId,Max(gi.end_time) as recentTime, Max(gi.end_time)<DATE_SUB(CURDATE() , INTERVAL 7 DAY) as isInActive from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id group by gr.user_id"
)
inActiveTeams = conn.execute(
"select team_id,gameTotal,inActiveGameTotal,gameTotal=inActiveGameTotal as isInActive from"
"(select gi.team_id as team_id,("
"select count(*) from GAME_INFO where team_id in( "
"select team_id from GAME_INFO where team_id =gi.team_id"
")"
") AS gameTotal,count(*) as inActiveGameTotal"
" from GAME_INFO as gi where gi.end_time < DATE_SUB(CURDATE(), INTERVAL 7 DAY) group by gi.team_id) as sub"
)
conn.close()
row_todayInstall = util.fetch_all_json(todayInstall)
row_todayPlay = util.fetch_all_json(todayPlay)
row_inActiveUsers = util.fetch_all_json(inActiveUsers)
row_inActiveTeams = util.fetch_all_json(inActiveTeams)
# print(row_inActiveUsers)
# print(row_inActiveUsers)
team_inActiveSum = 0
for row in row_inActiveTeams:
team_inActiveSum = team_inActiveSum + int(
row["isInActive"])
user_inActiveSum = 0
for row in row_inActiveUsers:
user_inActiveSum = user_inActiveSum + int(
row["isInActive"])
# print(team_inActiveSum)
data["todayInstall"] = row_todayInstall[0]["todayInstall"]
data["todayPlay"] = row_todayPlay[0]["todayPlay"]
data["inActiveUsers"] = user_inActiveSum
data["inActiveTeams"] = team_inActiveSum
return json.dumps(static.RES_DEFAULT(200, data),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getActiveGraph":
who = request.args.get('who')
period = request.args.get('period')
if who == "user":
if period == "day":
try:
print("[DashBoard]_GET_ActiveUserDay")
conn = db_manager.engine.connect()
getActiveUserDay = conn.execute(
"select hour(gi.end_time) as hour ,count(*) as cnt from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id where gi.end_time > curdate() group by hour(gi.end_time)"
)
conn.close()
row_getActiveUserDay = util.fetch_all_json(
getActiveUserDay)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveUserDay),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "month":
try:
print("[DashBoard]_GET_ActiveUserMonth")
conn = db_manager.engine.connect()
getActiveUserMonth = conn.execute(
"select day(gi.end_time) as day ,count(*) as cnt from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id where gi.end_time > date_sub(now(),INTERVAL 1 month) group by day(gi.end_time)"
)
conn.close()
row_getActiveUserMonth = util.fetch_all_json(
getActiveUserMonth)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveUserMonth),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "year":
try:
print("[DashBoard]_GET_ActiveUserYear")
conn = db_manager.engine.connect()
getActiveUserYear = conn.execute(
"select month(gi.end_time) as month ,count(*) as cnt from GAME_INFO as gi inner join GAME_RESULT as gr on gi.game_id = gr.game_id where gi.end_time > date_sub(now(),INTERVAL 1 year) group by month(gi.end_time) "
)
conn.close()
row_getActiveUserYear = util.fetch_all_json(
getActiveUserYear)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveUserYear),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif who == "team":
if period == "day":
try:
print("[DashBoard]_GET_ActiveTeamDay")
conn = db_manager.engine.connect()
getActiveTeamDay = conn.execute(
"select hour(gi.end_time)as hour ,count(*) as cnt from GAME_INFO as gi where gi.end_time > curdate() group by hour(gi.end_time)"
)
conn.close()
row_getActiveteamDay = util.fetch_all_json(
getActiveTeamDay)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveteamDay),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "month":
try:
print("[DashBoard]_GET_ActiveTeamMonth")
conn = db_manager.engine.connect()
getActiveTeamMonth = conn.execute(
"select day(gi.end_time)as day,count(*) as cnt from GAME_INFO as gi where gi.end_time > date_sub(now(),INTERVAL 1 month) group by day(gi.end_time)"
)
conn.close()
row_getActiveTeamMonth = util.fetch_all_json(
getActiveTeamMonth)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveTeamMonth),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "year":
try:
print("[DashBoard]_GET_ActiveTeamYear")
conn = db_manager.engine.connect()
getActiveTeamYear = conn.execute(
"select month(gi.end_time)as month,count(*) as cnt from GAME_INFO as gi where gi.end_time > date_sub(now(),INTERVAL 1 year) group by month(gi.end_time)"
)
conn.close()
row_getActiveTeamYear = util.fetch_all_json(
getActiveTeamYear)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveTeamYear),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getTopTwenty":
try:
print('gettop')
conn = db_manager.engine.connect()
result = conn.execute(
"select pb.problem_text,ga.answer_text, ga.score,ga.speed,ga.accuracy,ga.elapsed_time,u.user_name from GAME_RESULT as ga inner join USER as u on u.user_id = ga.user_id inner join GAME_INFO as gr on gr.game_id = ga.game_id inner join PROBLEM as pb on gr.problem_id = pb.problem_id order by score desc limit 20"
)
conn.close()
rows = util.fetch_all_json(result)
return json.dumps(static.RES_DEFAULT(200, rows),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif types == "getInActiveGraph":
who = request.args.get('who')
period = request.args.get('period')
print('getInactive')
if who == "user":
if period == "month":
try:
print("[DashBoard]_GET_InActiveUserMonth")
conn = db_manager.engine.connect()
getActiveUserMonth = conn.execute(
"select day(date) as day ,count(*) as cnt from USER_DAILY_ACTIVE where date > date_sub(now(),INTERVAL 1 month) and active = 0 group by day(date) "
)
conn.close()
row_getActiveUserMonth = util.fetch_all_json(
getActiveUserMonth)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveUserMonth),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "year":
try:
print("[DashBoard]_GET_InActiveUserYear")
conn = db_manager.engine.connect()
getActiveUserYear = conn.execute(
"select month(date) as month ,count(*) as cnt from USER_DAILY_ACTIVE where date > date_sub(now(),INTERVAL 1 year) and active=0 group by month(date) "
)
conn.close()
row_getActiveUserYear = util.fetch_all_json(
getActiveUserYear)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveUserYear),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
# team일떄는 후에 구현한다.
elif who == "team":
if period == "month":
try:
print("[DashBoard]_GET_InActiveTeamMonth")
conn = db_manager.engine.connect()
getActiveTeamMonth = conn.execute(
"select day(date) as day ,count(*) as cnt from TEAM_DAILY_ACTIVE where date > date_sub(now(),INTERVAL 1 month) and active =0 group by day(date) "
)
conn.close()
row_getActiveTeamMonth = util.fetch_all_json(
getActiveTeamMonth)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveTeamMonth),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
elif period == "year":
try:
print("[DashBoard]_GET_InActiveTeamYear")
conn = db_manager.engine.connect()
getActiveTeamYear = conn.execute(
"select month(date) as month ,count(*) as cnt from TEAM_DAILY_ACTIVE where date > date_sub(now(),INTERVAL 1 year) and active =0 group by month(date) "
)
conn.close()
row_getActiveTeamYear = util.fetch_all_json(
getActiveTeamYear)
return json.dumps(static.RES_DEFAULT(
200, row_getActiveTeamYear),
sort_keys=True,
indent=4)
except Exception as e:
print(str(e))
# logging.warning(str(e))
return json.dumps(static.RES_DEFAULT(400, "err"),
sort_keys=True,
indent=4)
def slack_oauth():
code = request.args.get('code')
r = requests.post("https://slack.com/api/oauth.access",
data={
'client_id': key['slackapp']['client_id'],
'client_secret': key['slackapp']['client_secret'],
'code': code
})
response = json.loads(r.text)
app.logger.info(response)
ctime = datetime.datetime.now()
result = db_manager.query(
"SELECT * FROM TEAM "
"WHERE "
"team_id = %s "
"LIMIT 1", (response['team_id'], ))
rows = util.fetch_all_json(result)
if len(rows) == 0:
db_manager.query(
"INSERT INTO TEAM "
"(`team_id`, `team_name`, `team_joined_time`, `team_access_token`, `team_bot_access_token`, `bot_id`)"
"VALUES"
"(%s, %s, %s, %s, %s, %s)",
(response['team_id'], response['team_name'], ctime,
response['access_token'], response['bot']['bot_access_token'],
response['bot']['bot_user_id']))
else:
db_manager.query(
"UPDATE TEAM "
"SET "
"team_bot_access_token = %s , "
"team_access_token = %s , "
"bot_id = %s "
"WHERE "
"team_id = %s",
(response['bot']['bot_access_token'], response['access_token'],
response['bot']['bot_user_id'], response['team_id']))
accessToken = response['access_token']
teamLang = util.get_team_lang(response['team_id'])
"""
slackApi = SlackApi(accessToken)
slackBotApi = SlackApi(response['bot']['bot_access_token'])
slackMembers = slackApi.im.list()['ims']
for member in slackMembers:
slackBotApi.chat.postMessage(
{
'as_user' : 'true',
'channel' : member['user'],
'username' : 'surfinger',
'icon_url' : 'http://icons.iconarchive.com/icons/vcferreira/firefox-os/256/keyboard-icon.png',
'text' : static.getText(static.CODE_TEXT_JOIN_BOT, teamLang),
'attachments' : json.dumps(
[
{
"text": "",
"fallback": "fallbacktext",
"callback_id": "wopr_game",
"color": "#3AA3E3",
"attachment_type": "default",
"actions": [
{
"name": "lang_en",
"text": ":us: English",
"type": "button",
"value": "lang_en"
},
{
"name": "lang_kr",
"text": ":kr: 한국어",
"type": "button",
"value": "lang_kr"
}
]
}
]
)
}
)
"""
return redirect(url_for('static', filename='successView.html'))
fileHandler.setFormatter(formatter)
stream_handler = logging.StreamHandler()
app.logger.addHandler(fileHandler)
app.logger.addHandler(stream_handler)
# set log level
app.logger.setLevel(logging.DEBUG)
#load josn key file
with open('../key.json', 'r') as f:
key = json.load(f)
##reset all socket status
result = db_manager.query("SELECT team_id " "FROM TEAM ")
rows = util.fetch_all_json(result)
for row in rows:
redis_manager.redis_client.hset('rtm_status_' + row['team_id'], 'status',
static.SOCKET_STATUS_IDLE)
# @app.route('/success', methods=['GET'])
# def redirect_to_index():
# return redirect(url_for('static', filename='index2.html'))
# @app.route('/success')
# def hello(name=None):
# return render_template('index2.html', name=name)
@app.route('/', methods=['GET', 'POST'])
def home():
