def player_bad_last_shot(self, tour_id, p_id, round_no, hole_list_text):
data_frame = None
try:
data_frame = self.sql_helper.execute("""
SELECT season_id, tour_id, p_id, round_cn, hole_cn, shot_cn, `distance`, rest_distance
FROM
(
SELECT season_id, tour_id, p_id, round_cn, hole_cn, shot_cn, `distance`, rest_distance, shot_distance,
ROW_NUMBER() OVER (PARTITION BY season_id, tour_id, p_id, round_cn ORDER BY `shot_distance` ASC) AS ROW_NUM_2
FROM
(
SELECT season_id, tour_id, p_id, round_cn, hole_cn, shot_cn, `distance`, rest_distance, (`distance` - rest_distance) AS shot_distance,
ROW_NUMBER() OVER (PARTITION BY season_id, tour_id, p_id, round_cn, hole_cn ORDER BY `distance` ASC) AS ROW_NUM
FROM player_shot
WHERE season_id = 2020 AND tour_id = {tour_id} and p_id = {p_id} AND round_cn = {round_no}
AND rest_distance > 0
AND hole_cn IN ({hole_list_text})
) AS A
WHERE ROW_NUM = 1
) AS A
WHERE ROW_NUM_2 = 1
""".format(tour_id=tour_id,
p_id=p_id,
round_no=round_no,
hole_list_text=hole_list_text))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def generate_article(self):
print('>>>>>>>>')
print(CacheHelper.get_cache_dict())
result = super().success_result()
try:
result['article_type'] = 'player'
article_var = PlayerArticleVariables(p_id=self.p_id,
tour_id=self.tour_id,
round_no=self.round_no)
template_sentence = SentenceTemplate(article_var)
sentence_list = template_sentence.set_sentence()
contents = ''
if len(sentence_list) > 0:
contents = '\n\n'.join(sentence_list)
result['contents'] = contents
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
result = super().failure_result()
result['result_msg'] = str(ex)
return result
def get_1st_sentence(self):
"""
첫문단
:return:
"""
var = NamedVariable()
try:
start_date = self.tour_list_df.iloc[-1].start_date
current = datetime.datetime.strptime(start_date, '%Y%m%d') + \
datetime.timedelta(days=self.competition_round - 1)
current_date = datetime.datetime.strftime(current, '%Y%m%d')
tour_place = self.tour_list_df.iloc[-1].place
tour_course = self.tour_list_df.iloc[-1].course
tour_total_prize = self.get_won(
self.tour_list_df.iloc[-1].total_prize_money)
setattr(var, '경기일자', current_date[-2:])
setattr(var, '경기장소', tour_place)
setattr(var, '경기코스', tour_course)
setattr(var, '상금', tour_total_prize)
setattr(var, '라운드', self.competition_round)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return var
def __init__(self, p_id):
try:
self.p_id = p_id
self.player_df = DbRecord().player_profile(p_id=self.p_id)
g.define_method(self, g.method_info_player)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
def shot_dynamic_variable(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT T_INDEX, T_GROUP, T_NAME, T_RANK, T_USE, T_CONDITION, T_EVAL, T_SENTENCE '
'FROM SHOT_DYNAMIC_VARIABLE')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def __init__(self, tour_id, round_no):
try:
self.tour_id = tour_id
self.round_no = round_no
self.tour_df = DbRecord().tour_list(tour_id=self.tour_id)
self.is_first_major()
g.define_method(self, g.method_info_tour)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
def base_template(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT T_INDEX, T_GROUP, T_NAME, T_RANK, T_USE, T_CONDITION, T_EVAL, T_SENTENCE, T_TEMPLATE_TAB '
'FROM BASE_TEMPLATE')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def player_list(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, MEMBER_TYPE, P_ID, P_NM, REG_DT '
'FROM PLAYER_LIST')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def cd_master(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT CD_GROUP, CD_SE, GROUP_NM, CD_ID, CD_NM '
'FROM CD_MASTER')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def player_test_sentence(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT T_INDEX, T_GROUP, T_NAME, T_RANK, T_USE, T_CONDITION, T_EVAL, T_SENTENCE '
'FROM PLAYER_TEST_SENTENCE')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def tour_player_group(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, ROUND_NO, GROUP_NO, P_ID, REG_DT '
'FROM TOUR_PLAYER_GROUP')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def tour_participant(self):
data_frame = None
try:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, PARTICIPANT_TYPE, P_ID, REG_DT '
'FROM TOUR_PARTICIPANT')
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def get_var(self):
"""
선수_테스트문장
:return:
"""
var = NamedVariable()
try:
setattr(var, '공통', self.get_common_sentence())
setattr(var, '첫문단', self.get_1st_sentence())
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return var
def get(cache_key=None, time=None):
result = None
if CacheHelper._dict_cache is not None and cache_key is not None:
if time is None:
time = CacheHelper.Time.DEFAULT
try:
result = CacheHelper.get_cache_dict(time).get(cache_key)
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return result
def set(cache_key=None, cache_value=None, time=None):
result = None
if CacheHelper._dict_cache is not None and cache_key is not None:
if time is None:
time = CacheHelper.Time.DEFAULT
CacheHelper.get_cache_dict(time).set(cache_key, cache_value)
else:
message = 'CacheHelper is not initialize or cache_key is None > {cache_key}'.format(
cache_key=cache_key)
LogHelper.instance().d(message,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return result
def get_common_sentence(self):
var = NamedVariable()
try:
# 선수프로필관련
birth_date = self.player_info.get_birth_date()
age = self.season - int(birth_date[0:4])
admission_date = self.player_info.get_admission_date()
player_year = self.season - int(admission_date[0:4])
setattr(var, '선수나이', age)
setattr(var, '선수연차', player_year)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return var
def method_info(self, name=None):
data_frame = None
try:
if name is None:
data_frame = self.sql_helper.execute(
'SELECT T_INDEX, T_NAME, T_KOR, T_METHOD FROM METHOD_INFO')
else:
data_frame = self.sql_helper\
.execute("SELECT T_INDEX, T_NAME, T_KOR, T_METHOD FROM METHOD_INFO "
"WHERE `T_NAME` = '{name}'".format(name=name))
if len(data_frame) > 0:
data_frame = data_frame.astype({'t_index': 'int'})
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def get_won(prize):
result = None
try:
length = len(prize)
if length >= 9:
result = int(prize) // 100000000
if str(int(prize) % 100000000) != '0':
result = str(result) + '억' + str(
int(prize) % 100000000)[0] + '천만'
else:
result = str(result) + '억'
elif length == 8:
result = int(prize) // 10000000
result = str(result) + '천만'
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return result
def player_shot(self, tour_id=None):
data_frame = None
try:
if tour_id is None:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, P_ID, ROUND_CN, HOLE_CN, SHOT_CN, SHOT_TEXT, DISTANCE, '
'REST_DISTANCE, REG_DT FROM PLAYER_SHOT')
else:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, P_ID, ROUND_CN, HOLE_CN, SHOT_CN, SHOT_TEXT, DISTANCE, '
'REST_DISTANCE, REG_DT FROM PLAYER_SHOT '
'WHERE TOUR_ID={tour_id}'.format(tour_id=tour_id))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def get_result_string(self, param_dict, sentence, var, final=False):
result = sentence
reg = r"\{(.+?)\}"
param_list = re.findall(reg, result)
if param_list:
for param in param_list:
p = param.split('.')
if param == '생략':
param_dict.update({param: ''})
else:
param_dict.update({param: self.get_attr(var, p)})
if final and result != '{생략}':
LogHelper.instance().d("{0}: {1}".format(result, param_dict))
for param in param_list:
result = result.replace("{%s}" % param, "%s" % param_dict[param])
if final:
LogHelper.instance().d("[Condition]: {0}: {1}".format(result, param_dict))
return result
def tour_course_hole_info(self, tour_id=None):
data_frame = None
try:
if tour_id is None:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, HOLE_TYPE, HOLE_NO, YARD, METER, PAR, GREEN, REG_DT '
'FROM TOUR_COURSE_HOLE_INFO')
else:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, HOLE_TYPE, HOLE_NO, YARD, METER, PAR, GREEN, REG_DT '
'FROM TOUR_COURSE_HOLE_INFO '
'WHERE TOUR_ID={tour_id}'.format(tour_id=tour_id))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def __init__(self, tour_id, p_id, round_no):
try:
self.tour_id = tour_id
self.p_id = p_id
self.round_no = round_no
self.db_record = DbRecord()
self.shot_df = self.db_record.player_shot(tour_id=self.tour_id)
self.player_shot_df = self.shot_df[self.shot_df.p_id == self.p_id]
self.hole_df = Hole(tour_id=self.tour_id).get_hole_par_no().astype(
{
'hole_no': 'int64',
'par': 'int64'
})
g.define_method(self, g.method_info_shot)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
def execute(self, query):
data_frame = None
conn = None
try:
# Open database connection
conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password, db=self.db_name
, charset="utf8", autocommit=True, cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
data_frame = pd.DataFrame(result)
data_frame.columns = map(str.lower, data_frame.columns)
except Exception as e:
LogHelper.instance().e(e)
finally:
if conn is not None:
# disconnect from server
conn.close()
return data_frame
def tour_list(self, tour_id=None):
data_frame = None
try:
if tour_id is None:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_TYPE, TOUR_ID, MONTH_ID, TOUR_NAME, START_DATE, END_DATE, '
'START_WEEK, END_WEEK, START_DATE_ORIGINAL, END_DATE_ORIGINAL, PLACE, COURSE, TOTAL_PRIZE_MONEY, '
'TOTAL_PRIZE_MONEY_UNIT, TOTAL_PRIZE_MONEY_ORIGINAL, WINNER, REG_DT '
'FROM TOUR_LIST')
else:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_TYPE, TOUR_ID, MONTH_ID, TOUR_NAME, START_DATE, END_DATE, '
'START_WEEK, END_WEEK, START_DATE_ORIGINAL, END_DATE_ORIGINAL, PLACE, COURSE, TOTAL_PRIZE_MONEY, '
'TOTAL_PRIZE_MONEY_UNIT, TOTAL_PRIZE_MONEY_ORIGINAL, WINNER, REG_DT '
'FROM TOUR_LIST '
'WHERE TOUR_ID = {tour_id}'.format(tour_id=tour_id))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def tour_total_score(self, tour_id=None):
data_frame = None
try:
if tour_id is None:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, P_ID, TOTAL_RANK, TOTAL_RANK_NO, TOTAL_SCORE, ROUND_1_STROKE, '
'ROUND_1_RANK, ROUND_2_STROKE, ROUND_2_RANK, ROUND_3_STROKE, ROUND_3_RANK, ROUND_4_STROKE,'
'ROUND_4_RANK, TOTAL_STROKE, PRIZE_MONEY, REG_DT '
'FROM TOUR_TOTAL_SCORE')
else:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, TOUR_ID, P_ID, TOTAL_RANK, TOTAL_RANK_NO, TOTAL_SCORE, ROUND_1_STROKE, '
'ROUND_1_RANK, ROUND_2_STROKE, ROUND_2_RANK, ROUND_3_STROKE, ROUND_3_RANK, ROUND_4_STROKE,'
'ROUND_4_RANK, TOTAL_STROKE, PRIZE_MONEY, REG_DT '
'FROM TOUR_TOTAL_SCORE '
'WHERE TOUR_ID={tour_id}'.format(tour_id=tour_id))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def player_profile(self, p_id=None):
data_frame = None
try:
if p_id is None:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, MEMBER_TYPE, P_ID, P_NM, P_NM_ENG, P_NM_CHN, BIRTH_DATE, BIRTH_DATE_ORIGINAL, '
'HEIGHT, HEIGHT_ORIGINAL, BLOOD_TYPE, ADMISSION_DATE, ADMISSION_DATE_ORIGINAL, MEMBER_ID, '
'SPONSOR, USE_BALL, SELF_INTRODUCTION, REG_DT '
'FROM PLAYER_PROFILE')
else:
data_frame = self.sql_helper.execute(
'SELECT SEASON_ID, MEMBER_TYPE, P_ID, P_NM, P_NM_ENG, P_NM_CHN, BIRTH_DATE, BIRTH_DATE_ORIGINAL, '
'HEIGHT, HEIGHT_ORIGINAL, BLOOD_TYPE, ADMISSION_DATE, ADMISSION_DATE_ORIGINAL, MEMBER_ID, '
'SPONSOR, USE_BALL, SELF_INTRODUCTION, REG_DT '
'FROM PLAYER_PROFILE '
'WHERE P_ID = {p_id}'.format(p_id=p_id))
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
return data_frame
def get_total_record(self):
"""
전체기록
:return:
"""
var = NamedVariable()
try:
hole_shot_df = self.player_shot_df[
self.player_shot_df.round_cn <= self.round_no]
hole_shot_df = hole_shot_df[['round_cn', 'hole_cn', 'shot_cn']]
hole_shot_df = hole_shot_df.drop_duplicates(
subset=['round_cn', 'hole_cn'], keep='last')
hole_shot_df = pd.merge(self.hole_df,
hole_shot_df,
left_on='hole_no',
right_on='hole_cn')
hole_shot_df[
'score'] = hole_shot_df['shot_cn'] - hole_shot_df['par']
# 스코어
score = hole_shot_df.score.values.sum()
setattr(var, '스코어', score)
# 스코어절대값
abs_score = abs(score)
setattr(var, '절대값스코어', abs_score)
# 스트로크수
stroke_cn = hole_shot_df.shot_cn.values.sum()
setattr(var, '스트로크수', stroke_cn)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=__name__,
func_name=sys._getframe().f_code.co_name)
return var
def do_stuff():
global yourThread
global callbackFunction
global is_init
with dataLock:
if callbackFunction is not None:
try:
if is_init:
now_hour = int(datetime.datetime.now().strftime('%H'))
if START_TIME <= now_hour < END_TIME:
callbackFunction()
else:
is_init = True
callbackFunction()
except Exception as ex:
LogHelper.instance().e(
ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
yourThread = threading.Timer(POOL_TIME, do_stuff, ())
yourThread.start()
def initialize():
try:
starttime = time.time()
global MODEL_DICT
global DYNAMIC_MODEL_DICT
global DF_GROUP_DICT
global VARIABLE_DICT
global method_info_common
global method_info_player
global method_info_tour
global method_info_round
global method_info_shot
global major_competition
# region Clear Values
MODEL_DICT = {}
DYNAMIC_MODEL_DICT = {}
DF_GROUP_DICT = {}
VARIABLE_DICT = {}
method_info_common = None
method_info_player = None
method_info_tour = None
method_info_round = None
method_info_shot = None
major_competition = []
# endregion Clear Values
MODEL_DICT = {
'method_info': DbTemplate().method_info(),
'base_template': DbTemplate().base_template(),
'player_test_sentence': DbTemplate().player_test_sentence(),
}
DYNAMIC_MODEL_DICT = {
'common_dynamic_variable': DbTemplate().common_dynamic_variable(),
'shot_dynamic_variable': DbTemplate().shot_dynamic_variable(),
'round_dynamic_variable': DbTemplate().round_dynamic_variable(),
}
for (name, model) in DYNAMIC_MODEL_DICT.items():
VARIABLE_DICT[name] = init_dynamic_variable(model)
method_info_common = get_dict_method(
DbTemplate().method_info('common'))
method_info_player = get_dict_method(
DbTemplate().method_info('player'))
method_info_tour = get_dict_method(DbTemplate().method_info('tour'))
method_info_round = get_dict_method(DbTemplate().method_info('round'))
method_info_shot = get_dict_method(DbTemplate().method_info('shot'))
major_competition = [
'한국여자오픈', '한화 클래식', 'KLPGA 챔피언십', 'KB금융 스타챔피언십', '하이트진로 챔피언십'
]
LogHelper.instance().d('globals initialize')
endtime = time.time()
print('초기화 걸리는 시간 : ', endtime - starttime)
except Exception as ex:
LogHelper.instance().e(ex,
file_name=sys._getframe().f_code.co_filename,
func_name=sys._getframe().f_code.co_name)
@app.route("/")
def index():
return render_template("index.html")
@app.errorhandler(404)
def page_not_found(error):
return render_template('404.html')
# KLPGA 선수별 기사
@app.route("/player_article/<tour_id>/<p_id>/<round_no>")
def player_test(p_id, tour_id, round_no):
g.initialize() # <TODO> 개발때만 쓰고 지워라!
result = PlayerArticle(p_id=int(p_id),
tour_id=tour_id,
round_no=int(round_no)).generate_article()
return ResponseHelper().write(result)
if __name__ == '__main__':
try:
LogHelper.instance().i('Server Start')
g.initialize()
app.run(debug=True, host='127.0.0.1', port=21215)
except Exception as ex:
LogHelper.instance().e(ex)