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():