if __name__ == '__main__':
    # 全局變量初始化
    gol._init()
    # # 課題背景信息
    # menu.course_background()
    #
    # # 打印小組成員信息
    # menu.team_info()
    # 連接數(shù)據(jù)庫(kù)信息
    db.connect_to_db(config.db_host, config.db_user, config.db_password, config.db_name)
    # 跳出數(shù)據(jù)庫(kù)的標(biāo)志 
    flag = True
    while (flag):
        menu.main_menu()
        cin = input("請(qǐng)輸入要執(zhí)行操作：")
        choice = int(cin) if cin.isdigit() else config.DEFAULT_INPUT
        if choice == 0:
            print("您已選擇退出，感謝您的操作體驗(yàn)")
            break
        elif choice == config.OP_TYPE_ADD:
            # ----------------------添加----------------------------
            mgr_add.add_data()
            print(config.END_OP_TEXT)
            input()
        elif choice == config.OP_TYPE_DELETE:
            # ----------------------刪除----------------------------
            mgr_delete.do_delete()
            print(config.END_OP_TEXT)
            input()
        elif choice == config.OP_TYPE_SELECT:
            # ----------------------查找----------------------------
            mgr_selector.do_select()
            print(config.END_OP_TEXT)
            input()
        elif choice == config.OP_TYPE_UPDATE:
            # ----------------------修改----------------------------
            mgr_update.do_update()
            print(config.END_OP_TEXT)
            input()
        else:
            print("操作選項(xiàng)輸入錯(cuò)誤，請(qǐng)重新輸入")
    db.close_db_link()

def do_update():
    menu.update_menu_total()
    cin = util.get_user_input(5)
    if cin == 0:
        return
    if cin == config.TYPE_1:
        do_update_person()
    if cin == config.TYPE_2:
        do_update_inject_info()
    if cin == config.TYPE_3:
        do_update_vaccine()
    if cin == config.TYPE_4:
        do_update_manufacturer()
def do_update_person():
    # 更新接種人員表
    menu.update_menu_1()
    cin_update = util.get_user_input(4)
    if cin_update == 0:
        return
    # 根據(jù)身份證號(hào)更新
    cert_no = input("請(qǐng)輸入身份證號(hào)：")
    table = config.table_dict[config.TYPE_1]
    table_inject_info = config.table_dict[config.TYPE_2]
    if cin_update == 1:
        # 更新姓名
        new_name = input("請(qǐng)輸入修改后的姓名：")
        sql1 = "UPDATE %s SET name='%s' WHERE cert_no='%s';" % (table, new_name, cert_no)
        sql2 = "UPDATE %s SET person_name='%s' WHERE person_cert_no='%s';" % (table_inject_info, new_name, cert_no)
        db.execute_sql(sql1)
        db.execute_sql(sql2)
    elif cin_update == 2:
        # 更新電話(huà)
        new_phone = input("請(qǐng)輸入修改后的接種人聯(lián)系電話(huà)：")
        sql1 = "UPDATE %s SET phone='%s' WHERE cert_no='%s';" % (table, new_phone, cert_no)
        db.execute_sql(sql1)
    elif cin_update == 3:
        # 更新住址
        new_address = input("請(qǐng)輸入修改后的家庭住址：")
        sql1 = "UPDATE %s SET address='%s' WHERE cert_no='%s';" % (table, new_address, cert_no)
        db.execute_sql(sql1)

def add_data():
    # 添加操作
    add_menu_1()
    # 獲取用戶(hù)輸入
    cin = util.get_user_input(5)
    if cin == 0:
        return
    # 獲取數(shù)據(jù)表列
    table = config.table_dict[cin]
    columns = get_table_col(table)
    util.print_log("表的列信息: ",  columns)
    field_list = []
    data_list = []
    for i in columns:
        col_name = i[0]     # 列名
        col_type = i[1]     # 列類(lèi)型
        col_index_type = i[4]     # 列索引類(lèi)型
        col_desc = i[8]     # 列值
        # 如果是不需要錄入則跳過(guò), 比如 id，person_id 等
        if(col_name in config.SKIP_FIELD_SET):
            continue
        util.print_log("當(dāng)前列信息", i)
        # 輸入值
        if col_type == config.FIELD_TYPE_DATETIME:
            # 日期檢查
            a = input("請(qǐng)輸入【%s】, 例如 2022-05-02 : " % col_desc)
            input_data_invalid = True
            while(input_data_invalid):
                if(util.is_valid_date(a) == True):
                    break
                a = input("您輸入日期不合法，請(qǐng)重新輸入，例如 2022-05-02 : ")
        else:
            a = input("請(qǐng)輸入【%s】: " % col_desc)
        # 判斷唯一關(guān)鍵字是否重復(fù)
        if col_index_type == 'UNI':
            sql = '''
                SELECT 1 FROM  %s  
                WHERE %s = %s 
            ''' % (table, col_name, a)
            util.print_log("查重 sql: ", sql)
            cursor = gol.get_value('cursor')
            exist_in_table = 1
            while exist_in_table == 1:
                dup_result = cursor.execute(sql)
                if dup_result == 0:
                    break
                # 如果重復(fù)則重新輸入
                a = input("您輸入與表中信息重復(fù)，請(qǐng)重新輸入【%s】" % col_name)
        field_list.append(col_name)
        data_list.append(a)
    # 輸入完成，組裝數(shù)據(jù)
    field_str = ",".join(field_list)
    data_tuple = tuple(data_list)
    # 拼接數(shù)據(jù)
    sql = '''
        INSERT INTO %s(%s) VALUES %s;
    ''' % (table, field_str, data_tuple)
    util.print_log(sql)
    cursor = gol.get_value('cursor')
    cursor.execute(sql)
    # 提交數(shù)據(jù)
    conn = gol.get_value('conn')
    conn.commit()
    util.print_log("%s 表成功插入數(shù)據(jù) %s" % (table, data_tuple))
    return 0

def do_delete():
    # 刪除操作
    menu.delete_menu_total()
    # 獲取輸入
    cin = util.get_user_input(5)
    if cin == 0:
        return
    if cin == config.TYPE_1:
        do_delete_person()
    if cin == config.TYPE_2:
        do_delete_inject_info()
    if cin == config.TYPE_3:
        do_delete_vaccine()
    if cin == config.TYPE_4:
        do_delete_manufacturer()
def do_delete_vaccine():
    # 刪除疫苗信息
    # 打印菜單
    menu.delete_menu_3()
    # 獲取用戶(hù)輸入
    cin = util.get_user_input(2)
    if cin == 0:
        return
    delete_value = input("請(qǐng)輸入具體信息：")
    # 獲取相關(guān)表
    table = config.table_dict[config.TYPE_3]
    table2 = config.table_dict[config.TYPE_2]
    # 接種編號(hào)
    if cin == 1:
        sql1 = '''
            DELETE FROM %s WHERE vaccine_no='%s' 
            ''' % (table, delete_value)
        sql2 = '''
            DELETE FROM %s WHERE vaccine_no='%s' 
            ''' % (table2, delete_value)
        db.execute_sql(sql1)
        db.execute_sql(sql2)

def do_select():
    # 執(zhí)行查詢(xún)
    menu.select_menu_total()
    # 獲取輸入
    cin = util.get_user_input(5)
    if cin == 0:
        return
    if cin == config.TYPE_1:
        # ------------------查找接種人員相關(guān)信息----------------
        do_select_person()
    elif cin == config.TYPE_2:
        # ------------------查找接種信息-----------------------
        do_select_inject_info()
    elif cin == config.TYPE_3:
        # ------------------查找疫苗信息-----------------------
        do_select_vaccine()
    elif cin == config.TYPE_4:
        # ------------------查找生產(chǎn)企業(yè)相關(guān)信息-----------------------
        do_select_manufacturer()
def do_select_vaccine():
    # 查找疫苗信息
    menu.select_menu_3()
    cin = util.get_user_input(5)
    if cin == 0:
        return
    # 獲取搜索數(shù)據(jù)
    cin2 = input("請(qǐng)輸入搜索數(shù)據(jù)：")
    table = config.table_dict[config.TYPE_3]
    query_value = cin2
    # 結(jié)果集合
    result = []
    # 疫苗編號(hào)
    if cin == 1:
        result = db.query_by_table_field(table, 'vaccine_no', query_value)
    # 廠(chǎng)家編號(hào)
    if cin == 2:
        result = db.query_by_table_field(table, 'company_no', query_value)
    # 生產(chǎn)日期
    if cin == 3:
        sql = ''' 
            SELECT * FROM %s  WHERE %s >= '%s' 
            ''' % (table, 'production_at', query_value)
        cursor = gol.get_value('cursor')
        cursor.execute(sql)
        result = cursor.fetchall()
    # 過(guò)期時(shí)間
    if cin == 4:
        sql = ''' 
            SELECT * FROM %s  WHERE %s <= '%s' 
            ''' % (table, 'expired_at', query_value)
        cursor = gol.get_value('cursor')
        cursor.execute(sql)
        result = cursor.fetchall()
    show_select_vaccine_result(result)