def update_answer(answer_num, rpsn_question, answer, category_num, user,
project):
sql = "UPDATE ANSWER_BUILDER_" + user + "_" + project + " SET RPSN_QUESTION = '" + rpsn_question + "', ANSWER = '" + answer + "', CATEGORY_NUM = '" + str(
category_num
) + "', MDFC_RGSN_DATE = CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR) WHERE ANSWER_NUM = '" + str(
answer_num) + "'"
update.commit(sql)
def update_category_by_category_num(category_num, big_category,
middle_category, small_category_lv1,
small_category_lv2, small_category_lv3):
sql = "UPDATE CATEGORY_LIST SET BIG_CATEGORY = '" + big_category + "',"
sql += "MIDDLE_CATEGORY = '" + middle_category + "',"
sql += "SMALL_CATEGORY_LV1 = '" + small_category_lv1 + "',"
sql += "SMALL_CATEGORY_LV2 = '" + small_category_lv2 + "',"
sql += "SMALL_CATEGORY_LV3 = '" + small_category_lv3 + "' "
sql += "WHERE CATEGORY_NUM = " + str(category_num)
update.commit(sql)
def insert_vocab(voca_nm, t):
table = ''
if t == 'enc':
table = 'VOCAB_ENC'
elif t == 'dec':
table = 'VOCAB_DEC'
sql = "SELECT MAX(VOCA_NUM) + 1 FROM " + table
result = select.fetch(sql)
voca_num = result[0][0]
if voca_num == None:
voca_num = 1
sql = "INSERT INTO " + table + " VALUES (" + str(
voca_num) + ", '" + voca_nm + "')"
update.commit(sql)
def update_synonym_by_synonym_num(synonym_num, synonym_nm):
sql = "UPDATE SYNONYM_LIST SET SYNONYM_NM = '" + synonym_nm + "' WHERE SYNONYM_NUM = " + str(
synonym_num)
update.commit(sql)
def insert_answer(answer_num, answer, question, category_num, user_ip, rq_num,
user, project, partner_id):
sql = "INSERT INTO ANSWER_BUILDER_" + user + "_" + project + " VALUES ('" + answer_num + "', '" + answer + "', '" + category_num + "', '" + question + "', 0, '" + user_ip + "', " + rq_num + ", CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR), CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR), '" + partner_id + "')"
update.commit(sql)
def create_schedule_table():
sql = "CREATE TABLE SCHEDULE (USER_IP VARCHAR(20), MESSAGE VARCHAR(500), RESV_DATE VARCHAR(20), RESV_TIME VARCHAR(10))"
try:
update.commit(sql)
except:
print("SCHEDULE table already exists")
def insert_multiple_answer(answer_num, srno, question, answer, user, project):
sql = "INSERT INTO ANSWER_MULTIPLE_" + user + "_" + project + " VALUES ('" + answer_num + "', " + str(
srno) + ", '" + question + "', '" + answer + "', 0)"
update.commit(sql)
def update_notice_complete(user, project, notice_num):
sql = "UPDATE NOTICE_LIST_" + user + "_" + project + " SET COMPLETE_YN = 'Y' WHERE NOTICE_NUM = " + notice_num
update.commit(sql)
def update_notice(user, project, notice_num, notice_subject, notice_content, notice_start_date, notice_end_date):
sql = "UPDATE NOTICE_LIST_" + user + "_" + project + " SET NOTICE_SUBJECT = '" + notice_subject + "', NOTICE_CONTENT = '" + notice_content + "', NOTICE_START_DATE = '" + notice_start_date + "', NOTICE_END_DATE = '" + notice_end_date + "' WHERE NOTICE_NUM = " + notice_num
update.commit(sql)
def update_keyword_yn(voca_nm):
sql = "UPDATE VOCA SET KEYWORD_YN = CASE WHEN KEYWORD_YN = 'Y' THEN '' ELSE 'Y' END WHERE VOCA_NM = '" + voca_nm + "'"
update.commit(sql)
def delete_voca_by_voca_nm(voca_nm):
sql = "DELETE FROM VOCA WHERE VOCA_NM = '" + voca_nm + "'"
update.commit(sql)
def update_image_cnt(user, project, answer_num, image_cnt):
sql = "UPDATE ANSWER_BUILDER_" + user + "_" + project + " SET IMAGE_CNT = " + image_cnt + " WHERE ANSWER_NUM = '" + str(
answer_num) + "'"
update.commit(sql)
def insert_synonym(synonym_nm, synonym_tag):
synonym_num = search_next_synonym_num()
sql = "INSERT INTO SYNONYM_LIST VALUES (" + synonym_num + ", '" + synonym_nm + "', '" + synonym_tag + "')"
update.commit(sql)
def delete_entity_by_entity_nm(entity_nm):
sql = "DELETE FROM ENTITY WHERE ENTITY_NM = '" + entity_nm + "'"
update.commit(sql)
def insert_entity(entity_nm):
sql = "INSERT INTO ENTITY VALUES ('" + entity_nm + "')"
update.commit(sql)
def update_voca_entity(voca_nm, entity_nm):
sql = "UPDATE VOCA SET VOCA_ENTITY = '" + entity_nm + "' WHERE VOCA_NM = '" + voca_nm + "'"
update.commit(sql)
def create_synonym_list_table():
sql = "CREATE TABLE SYNONYM_LIST (SYNONYM_NUM INT(7), SYNONYM_NM VARCHAR(50), SYNONYM_TAG VARCHAR(50))"
try:
update.commit(sql)
except:
print("SYNONYM_LIST table already exists")
def create_question_fragment_builder_table(user, project):
sql = "CREATE TABLE QUESTION_FRAGMENT_BUILDER_" + user + "_" + project + " (ANSWER_NUM VARCHAR(500), QUESTION_SRNO INT(7), QUESTION VARCHAR(500), QUESTION_VOCA VARCHAR(500))"
try:
update.commit(sql)
except:
print("QUESTION_FRAGMENT_BUILDER table already exists")
def insert_voca(voca_nm):
sql = "INSERT INTO VOCA VALUES ('" + voca_nm + "', '" + voca_nm + "', 'Y')"
update.commit(sql)
def delete_synonym_by_synonym_tag(synonym_tag):
sql = "DELETE FROM SYNONYM_LIST WHERE SYNONYM_TAG = '" + synonym_tag + "'"
update.commit(sql)
def update_voca_synonym(voca_synonym, voca_nm):
sql = "UPDATE VOCA SET VOCA_SYNONYM = '" + voca_synonym + "' WHERE VOCA_NM = '" + voca_nm + "'"
update.commit(sql)
def delete_synonym_by_synonym_num(synonym_num):
sql = "DELETE FROM SYNONYM_LIST WHERE SYNONYM_NUM = " + synonym_num
update.commit(sql)
def insert_notice(user, project, notice_subject, notice_content, notice_start_date, notice_end_date):
notice_num = get_next_notice_num(user, project)
sql = "INSERT INTO NOTICE_LIST_" + user + "_" + project + " VALUES ('" + notice_num + "', '" + notice_subject + "', '" + notice_content + "', 0, CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR), '" + notice_start_date + "', '" + notice_end_date + "', 'N')"
update.commit(sql)
def delete_answer(answer_num, user, project):
sql = "DELETE FROM ANSWER_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
update.commit(sql)
sql = "DELETE FROM QUESTION_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
update.commit(sql)
def delete_notice(user, project, notice_num):
sql = "DELETE FROM NOTICE_LIST_" + user + "_" + project + " WHERE NOTICE_NUM = " + notice_num
update.commit(sql)
def delete_all_answer(user, project):
sql = "DELETE FROM ANSWER_BUILDER_" + user + "_" + project
update.commit(sql)
def delete_all_multiple_answer(answer_num, user, project):
sql = "DELETE FROM ANSWER_MULTIPLE_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "'"
update.commit(sql)
def delete_question(answer_num, question_srno, user, project):
sql = "DELETE FROM QUESTION_BUILDER_" + user + "_" + project + " WHERE ANSWER_NUM = '" + answer_num + "' AND QUESTION_SRNO = " + question_srno
update.commit(sql)
def insert_login_list(user_ip):
sql = "INSERT INTO LOGIN_LIST VALUES ('" + user_ip + "', CAST(DATE_FORMAT(NOW(), '%Y%m%d') AS CHAR), CAST(DATE_FORMAT(NOW(), '%H%i%s') AS CHAR))"
update.commit(sql)
def delete_all_question(user, project):
sql = "DELETE FROM QUESTION_BUILDER_" + user + "_" + project
update.commit(sql)