def search_question_by_question_nm_and_answer_num(question_nm, answer_num,
user, project, partner_id,
admin_yn, readonly_yn):
where_question_nm = ''
where_answer_num = ''
if question_nm != None and question_nm != '':
where_question_nm = " AND A.QUESTION LIKE '%" + question_nm + "%'"
if answer_num != None and answer_num != '':
where_answer_num = " AND B.ANSWER_NUM = '" + answer_num + "'"
sql = "SELECT A.ANSWER_NUM, A.QUESTION_SRNO, A.QUESTION, A.QUESTION_TAG, A.QUESTION_VOCA FROM QUESTION_BUILDER_" + user + "_" + project + " A, ANSWER_BUILDER_" + user + "_" + project + " B WHERE 1=1 AND A.ANSWER_NUM = B.ANSWER_NUM" + where_question_nm + where_answer_num
if admin_yn == 'N' and readonly_yn == 'N':
sql += " AND B.PARTNER_ID = '" + partner_id + "'"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['answer_num'] = r[0]
res_dict['question_srno'] = r[1]
res_dict['question'] = r[2]
res_dict['question_tag'] = r[3]
res_dict['question_voca'] = r[4]
res_dict['fragment_yn'] = 'N'
res.append(res_dict)
return res
def get_emno(user_ip):
sql = "SELECT EMNO FROM EMPL_INFO WHERE USER_IP = '" + user_ip + "'"
result = select.fetch(sql)
emno = 'anonymous'
for r in result:
emno = r[0]
return emno
def search_answer(gubun, subject, user, project, partner_id, admin_yn,
readonly_yn):
category_select = "(SELECT CONCAT(BIG_CATEGORY,' > ',MIDDLE_CATEGORY,' > ',SMALL_CATEGORY_LV1,' > ',SMALL_CATEGORY_LV2,' > ',SMALL_CATEGORY_LV3) FROM CATEGORY_LIST WHERE CATEGORY_NUM = X.CATEGORY_NUM) AS CATEGORY_NM"
sql = "SELECT X.RPSN_QUESTION, X.ANSWER_NUM, X.ANSWER, X.CATEGORY_NUM, " + category_select + ", X.IMAGE_CNT, X.RGSN_USER_IP FROM ANSWER_BUILDER_" + user + "_" + project + " X WHERE 1=1"
if subject != '':
if gubun == '1':
sql += " AND X.ANSWER_NUM IN (SELECT ANSWER_NUM FROM QUESTION_BUILDER_" + user + "_" + project + " WHERE QUESTION LIKE '%" + subject + "%')"
elif gubun == '2':
sql += " AND X.ANSWER LIKE '%" + subject + "%'"
elif gubun == '3':
sql += " AND X.RGSN_USER_IP = '" + subject + "'"
if admin_yn == 'N' and readonly_yn == 'N':
sql += " AND X.PARTNER_ID = '" + partner_id + "'"
sql += " ORDER BY X.ANSWER_NUM DESC"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['rpsn_question'] = r[0]
res_dict['answer_num'] = r[1]
res_dict['answer'] = r[2]
res_dict['category_num'] = r[3]
res_dict['category_nm'] = ''
if r[4] != None:
arr = r[4].split(" > ")
new_arr = []
for i in range(len(arr)):
if arr[i] != '':
new_arr.append(arr[i])
res_dict['category_nm'] = " > ".join(new_arr)
res.append(res_dict)
res_dict['image_cnt'] = r[5]
res_dict['rgsn_user'] = r[6]
return res
def get_project(user_id, project):
sql = "SELECT PROJECT FROM PROJECT_LIST WHERE USER_ID = '" + user_id + "' AND PROJECT = '" + project + "'"
result = select.fetch(sql)
project = None
for r in result:
project = r[0]
return project
def word_tokenizer(sentence):
token_arr = sentence.split(" ")
new_words = []
tokenized_arr = []
while len(token_arr) > 0:
cur_token = token_arr[0]
remain_token = ""
find = False
while cur_token != "":
sql = "SELECT VOCA_NM FROM VOCA WHERE VOCA_NM ='" + cur_token + "'"
result = select.fetch(sql)
if len(result) == 0:
word = ""
else:
word = result[0][0]
if word != "":
find = True
if remain_token != "":
token_arr[0] = remain_token
else:
token_arr.pop(0)
break
remain_token = cur_token[len(cur_token) - 1] + remain_token
cur_token = cur_token[0:-1]
if find == False:
token_arr.pop(0)
new_words.append(remain_token)
tokenized_arr.append(remain_token)
else:
tokenized_arr.append(cur_token)
return tokenized_arr
def get_password(user_id):
sql = "SELECT PASSWORD FROM USER_INFO WHERE USER_ID = '" + user_id + "'"
result = select.fetch(sql)
password = None
for r in result:
password = r[0]
return password
def get_project_language(user, project):
sql = "SELECT LANGUAGE FROM PROJECT_LIST WHERE USER_ID = '" + user + "' AND PROJECT = '" + project + "'"
result = select.fetch(sql)
res = ''
for r in result:
res = r[0]
return res
def get_answer_and_answer_num():
sql = "SELECT ANSWER_NUM, ANSWER FROM ANSWER_LIST_GAD"
result = select.fetch(sql)
answer_dict = {}
for r in result:
answer_dict[r[0]] = r[1]
return answer_dict
def get_all_answer_num_and_rpsn_question(user, project):
sql = "SELECT ANSWER_NUM, RPSN_QUESTION FROM ANSWER_BUILDER_" + user + "_" + project
result = select.fetch(sql)
res = {}
for r in result:
res[r[0]] = r[1]
return res
def get_dialogue_list_by_function_nm_and_argument_nm(function_nm, argument_nm):
sql = "SELECT MAX(DIALOGUE_TEXT) FROM DIALOGUE_LIST WHERE FUNCTION_NM = '" + function_nm + "' AND ARGUMENT_NM = '" + argument_nm + "'"
result = select.fetch(sql)
dialogue_text = ''
if len(result) > 0:
dialogue_text = result[0][0]
return dialogue_text
def get_all_brnc_nm_list():
sql = "SELECT BRNC_NM FROM BRNC_LIST"
result = select.fetch(sql)
brnc_nm_list = []
for i in range(len(result)):
brnc_nm_list.append(result[i][0])
return brnc_nm_list
def get_latest_question(user_ip):
sql = "SELECT QUESTION FROM QUESTION_LIST WHERE USER_IP = '" + user_ip + "' AND RGSN_DATE = CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR) ORDER BY RGSN_DATE DESC, RGSN_TIME DESC"
result = select.fetch(sql)
msg = []
for i in range(len(result)):
msg.append(result[i][0])
return msg
def get_bucket(user, project):
sql = "SELECT CONFIG_VALUE FROM TRAINING_CONFIG_LIST_" + user + "_" + project + " WHERE CONFIG_NAME = 'BUCKET'"
result = select.fetch(sql)
bucket = 1
if len(result) > 0:
bucket = result[0][0]
return bucket
def get_kor_nm_by_tag_nm(tag_nm):
sql = "SELECT KOR_NM FROM TAG_LIST WHERE TAG_NM = '" + tag_nm + "'"
result = select.fetch(sql)
tag_nm = ''
if len(result) > 0:
tag_nm = result[0][0]
return tag_nm
def get_training_question_list(user, project, answer_num):
sql = "SELECT QUESTION FROM QUESTION_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
result = select.fetch(sql)
question_list = []
for r in result:
question_list.append(r[0])
return question_list
def search_multiple_answer_by_srno(answer_num, srno, user, project):
sql = "SELECT QUESTION, ANSWER FROM ANSWER_MULTIPLE_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "' AND SRNO = " + srno
result = select.fetch(sql)
res_dict = {}
res_dict['question'] = result[0][0]
res_dict['answer'] = result[0][1]
return res_dict
def search_max_answer_num(user, project):
sql = "SELECT MAX(ANSWER_NUM) FROM ANSWER_BUILDER_" + user + "_" + project
result = select.fetch(sql)
max_answer_num = ''
for r in result:
max_answer_num = r[0]
return max_answer_num
def get_next_notice_num(user, project):
sql = "SELECT MAX(NOTICE_NUM) + 1 FROM NOTICE_LIST_" + user + "_" + project
result = select.fetch(sql)
max_notice_num = 1
if result[0][0] != None:
max_notice_num = result[0][0]
return str(max_notice_num)
def get_my_question(user_ip):
sql = "SELECT QUESTION FROM MY_QUESTION WHERE USER_IP = '" + user_ip + "'"
result = select.fetch(sql)
msg = []
for i in range(len(result)):
msg.append(result[i][0])
return msg
def get_same_category_question_list(user, project, answer_num):
table_name = "ANSWER_BUILDER_" + user + "_" + project
sql = "SELECT RPSN_QUESTION FROM " + table_name + " WHERE CATEGORY_NUM != '' AND CATEGORY_NUM IN (SELECT CATEGORY_NUM FROM " + table_name + " WHERE ANSWER_NUM = '" + answer_num + "')"
result = select.fetch(sql)
question_list = []
for r in result:
question_list.append(r[0])
return question_list
def get_voca_by_voca_nm_list(a):
res = []
for aa in a:
sql = "SELECT VOCA_NM FROM VOCA WHERE VOCA_NM ='" + aa + "'"
result = select.fetch(sql)
if len(result) > 0:
res.append(result[0][0])
return res
def get_answer_by_answer_num(user, project, answer_num):
sql = "SELECT ANSWER, MDFC_RGSN_DATE FROM ANSWER_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
result = select.fetch(sql)
answer = ''
mdfc_rgsn_date = ''
if len(result) > 0:
answer = str(result[0][0])
mdfc_rgsn_date = str(result[0][1])
return answer, mdfc_rgsn_date
def search_question_voca_by_answer_num(user, project, answer_num):
sql = "SELECT QUESTION_VOCA FROM QUESTION_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['question_voca'] = r[0]
res.append(res_dict)
return res
def get_most_frequent_list(answer_num, rank):
sql = "SELECT ROWNUM, ANSWER_NUM, COUNT FROM (SELECT ROWNUM, ANSWER_NUM, COUNT FROM (SELECT @ROWNUM := @ROWNUM + 1 AS ROWNUM, ANSWER_NUM, COUNT(QUESTION) COUNT FROM QUESTION_LIST WHERE ANSWER_NUM != '" + answer_num + "' GROUP BY ANSWER_NUM) A ORDER BY COUNT DESC, ANSWER_NUM ASC) B"
result = select.fetch(sql)
answer_num_list = []
count = []
for r in result:
answer_num_list.append(r[1])
count.append(r[2])
return answer_num_list, count
def search_synonym_nm_list_by_synonym_nm(synonym_nm):
sql = "SELECT SYNONYM_NM FROM SYNONYM_LIST WHERE SYNONYM_TAG = (SELECT SYNONYM_TAG FROM SYNONYM_LIST WHERE SYNONYM_NM = '" + synonym_nm + "')"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['synonym_nm'] = r[0]
res.append(res_dict)
return res
def search_entity_by_equal_entity_nm(entity_nm):
sql = "SELECT ENTITY_NM FROM ENTITY WHERE ENTITY_NM = '" + entity_nm + "'"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['entity_nm'] = r[0]
res.append(res_dict)
return res
def search_multiple_answer(answer_num, user, project):
sql = "SELECT QUESTION, ANSWER FROM ANSWER_MULTIPLE_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
result = select.fetch(sql)
res = []
for r in result:
res_dict = {}
res_dict['question'] = r[0]
res_dict['answer'] = r[1]
res.append(res_dict)
return res
def search_next_question_srno(user, project):
sql = "SELECT MAX(QUESTION_SRNO) + 1 FROM QUESTION_BUILDER_" + user + "_" + project
result = select.fetch(sql)
next_question_srno = 1
for r in result:
if r[0] == None:
next_question_srno = 1
else:
next_question_srno = int(r[0])
return str(next_question_srno)
def search_next_synonym_num():
sql = "SELECT MAX(SYNONYM_NUM) + 1 FROM SYNONYM_LIST"
result = select.fetch(sql)
next_synonym_num = 1
for r in result:
if r[0] == None:
next_synonym_num = 1
else:
next_synonym_num = int(r[0])
return str(next_synonym_num)
def search_next_tag_num():
sql = "SELECT MAX(TAG_NUM) + 1 FROM TAG_LIST"
result = select.fetch(sql)
next_tag_num = 1
for r in result:
if r[0] == None:
next_tag_num = 1
else:
next_tag_num = int(r[0])
return str(next_tag_num)
