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)