import queue
import threading
import time
import traceback
from tkinter import Tk, Button, messagebox, Label, Frame, Entry, IntVar, Radiobutton, StringVar, \
    constants, filedialog, Text, font, Scrollbar, BooleanVar, Checkbutton
from tkinter.font import Font
from tkinter.messagebox import showinfo, showerror
from tkinter.ttk import Progressbar
from enum import Enum
import logging
from pymysql import OperationalError
class MsgEnum(Enum):
    """
        消息類型枚舉
    """
    START = 0
    STOP = 1
    EXIT = 3
class InputValue:
    def __init__(self, host, port, user, password, database, sql,calc_mode,isAllTable):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.sql = sql
        self.calc_mode = calc_mode
        self.isAllTable = isAllTable
class GuiTempldate:
    class Cache:
        RUNING = False
    def __init__(self) -> None:
        # 1、Gui消息隊列
        self.msg_center = MsgCenter(self)
        # 2、窗口設置
        self.root = Tk()
        self.root.title("數(shù)據庫導出工具")
        # self.root.wm_resizable(False, False) # 設置窗口大小不可拉伸
        self.root.geometry('600x700+500+200')  # 設置窗口： 寬 x 高 + 窗口位置x坐標 + 窗口位置y坐標
        self.root.protocol("WM_DELETE_WINDOW", self.close_event)
        # 4、初始化各個組件和布局
        self.initGui()
    def initGui(self):
        # 1- 標簽
        text_str = """版本: 1.0.0
        #author burukeyou
        #說明:
            1) 支持導出數(shù)據庫所有表結構信息
            2）支持導出數(shù)據庫表清單
            3）支持自定義sql導出
        """
        Label(self.root, text=text_str, justify='left', fg='red').pack(anchor=constants.W)
        # 4- 文本框
        fm02 = Frame(self.root)
        self.ip_address = StringVar(value="localhost:3306")
        fm02.pack(anchor=constants.W, fill=constants.X)
        Label(fm02, text='服務器地址 ').pack(side=constants.LEFT)
        self.name_btn = Entry(fm02, width=40, textvariable=self.ip_address)
        self.name_btn.pack(side=constants.RIGHT)
        # 4- 文本框
        fm02 = Frame(self.root)
        self.user_name = StringVar(value="root")
        fm02.pack(anchor=constants.W, fill=constants.X)
        Label(fm02, text='帳號').pack(side=constants.LEFT)
        self.name_btn = Entry(fm02, width=40, textvariable=self.user_name)
        self.name_btn.pack(side=constants.RIGHT)
        # 4- 文本框
        fm02 = Frame(self.root)
        self.password = StringVar(value="123456")
        fm02.pack(anchor=constants.W, fill=constants.X)
        Label(fm02, text='密碼').pack(side=constants.LEFT)
        self.name_btn = Entry(fm02, width=40, textvariable=self.password)
        self.name_btn.pack(side=constants.RIGHT)
        # 4- 文本框
        fm02 = Frame(self.root)
        self.database = StringVar(value="")
        fm02.pack(anchor=constants.W, fill=constants.X)
        Label(fm02, text='數(shù)據庫').pack(side=constants.LEFT)
        self.name_btn = Entry(fm02, width=40, textvariable=self.database)
        self.name_btn.pack(side=constants.RIGHT)
        # 3- 單選框
        self.mode_var = IntVar(value=1)
        fm01 = Frame(self.root)  # , bg='blue'
        fm01.pack(anchor=constants.W)
        Label(fm01, text='導出模式').grid(row=0, column=0, sticky='W')
        Radiobutton(fm01, text='表結構', variable=self.mode_var, value=1).grid(row=0, column=1, sticky='E', padx=40)
        Radiobutton(fm01, text='表清單', variable=self.mode_var, value=2).grid(row=0, column=2, sticky='E', padx=40)
        Radiobutton(fm01, text='自定義SQL', variable=self.mode_var, value=3).grid(row=0, column=3, sticky='E', padx=40)
        # 5- 勾選框
        self.is_all_flag = BooleanVar()
        self.is_all_table_btn = Checkbutton(self.root, variable=self.is_all_flag, text='所有表執(zhí)行一遍(自定義SQL可使用模版變量#{tableName})', onvalue=True, offvalue=False)
        self.is_all_table_btn.pack(anchor=constants.W)
        # 9- 多行文本框
        Label(self.root, text='自定義SQL輸入').pack(anchor=constants.W)
        fm22 = Frame(self.root)
        fm22.pack(anchor=constants.W, fill=constants.X)
        scroll = Scrollbar(fm22)
        scroll.pack(side=constants.RIGHT, fill=constants.Y)
        ft = Font(family='宋體', size=14, weight=font.BOLD)
        self.text_btn = Text(fm22, height=12, fg="black", font=ft, bg="white", insertbackground="black")
        self.text_btn.pack(side=constants.LEFT)
        # text 聯(lián)動 scroll
        scroll.config(command=self.text_btn.yview)
        self.text_btn.config(yscrollcommand=scroll.set)
        # 進度條
        self.progressbar = Progressbar(self.root, length=600, mode="determinate", orient=constants.HORIZONTAL)
        self.progressbar.pack(anchor=constants.W)
        self.progressbar['maximum'] = 100
        # 10、開始結束按鈕
        fm06 = Frame(self.root)
        fm06.pack(side=constants.BOTTOM)
        self.start_btn = Button(fm06, text="導出", width=6, height=1, command=self.start_event)
        self.start_btn.grid(row=0, column=0, sticky='W', padx=20, pady=20)
        self.stop_btn = Button(fm06, text="停止", width=6, height=1, command=self.stop_event)
        self.stop_btn.grid(row=0, column=1, sticky='E', padx=20, pady=20)
        self.test_btn = Button(fm06, text="測試連接", width=6, height=1, command=self.test_connection)
        self.test_btn.grid(row=0, column=2, sticky='E', padx=20, pady=20)
    def start_event(self):
        self.msg_center.put(MsgEnum.START)
        self.Cache.RUNING = True
        self.main_calc_thread: threading.Thread = threading.Thread(target=start_clac, args=(self,))
        self.main_calc_thread.start()
    def stop_event(self):
        self.msg_center.put(MsgEnum.STOP)
        self.Cache.RUNING = False
    def close_event(self):
        # 關閉窗口
        if self.Cache.RUNING and not messagebox.askokcancel("警告", "任務還在執(zhí)行中，確定要關閉嗎?"):
            return
        self.root.destroy()
        self.msg_center.put(MsgEnum.EXIT)
    def test_connection(self) -> bool:
        input_value = self.getInputValue()
        if not checkParam(input_value):
            return False
        conn = None
        try:
            conn = connectDB(input_value)
            showinfo(message="測試連接成功")
            return True
        except OperationalError as e:
            showerror(message="測試連接數(shù)據庫失敗,請確認數(shù)據庫信息是否正確。 \n" + str(e))
        finally:
            if conn:
                conn.close()
    def showUI(self):
        # 啟動消息隊列
        threading.Thread(target=self.msg_center.mainloop).start()
        # 這個方法會循環(huán)阻塞住，監(jiān)聽gui的事件,得放到主線程最后
        self.root.mainloop()
    def getInputValue(self) -> InputValue:
        ipaddress: str = self.ip_address.get().strip()
        if ipaddress == "" or ipaddress.find(":") < 0:
            showerror(message="請輸入正確的ip地址")
            return
        ip = ipaddress.split(":")[0].strip()
        port = ipaddress.split(":")[1].strip()
        database = self.database.get().strip()
        user_name = self.user_name.get().strip()
        password = self.password.get().strip()
        calc_mode = self.mode_var.get()
        custom_sql = self.text_btn.get("1.0", "end-1c").strip()
        isAllTable: bool = self.is_all_flag.get()
        return InputValue(ip, port, user_name, password,database, custom_sql,calc_mode,isAllTable)
def start_clac(gui: GuiTempldate):
    try:
        # 實際計算任務
        start_demo(gui)
    finally:
        # 發(fā)布結束事件
        gui.stop_event()
class MsgCenter:
    """
        消息隊列
            主要處理窗口控件消息
    """
    def __init__(self, obj: GuiTempldate) -> None:
        self.queue = queue.Queue()
        self.obj = obj
    def put(self, msg: Enum):
        self.queue.put(msg)
    def mainloop(self):
        while True:
            try:
                # 阻塞獲取消息
                msg = self.queue.get()
                print("消費消息: {}".format(msg))
                if msg == MsgEnum.START:
                    MsgStrategy.start_strategy(self.obj)
                elif msg == MsgEnum.STOP:
                    MsgStrategy.stop_strategy(self.obj)
                elif msg == MsgEnum.EXIT:
                    break
                else:
                    pass
            except queue.Empty:
                traceback.print_exc()
class MsgStrategy:
    @classmethod
    def start_strategy(cls, gui: GuiTempldate):
        gui.start_btn.config(state=constants.DISABLED)
        gui.stop_btn.config(state=constants.NORMAL)
        #
        gui.progressbar['value'] = 0
        for i in range(100):
            time.sleep(0.1)
            gui.progressbar['value'] += 1
            if i >= 50:
                gui.progressbar['value'] = 100
                gui.root.update()
                break
            gui.root.update()
    @classmethod
    def stop_strategy(cls, gui: GuiTempldate):
        gui.start_btn.config(state=constants.NORMAL)
        gui.stop_btn.config(state=constants.DISABLED)
# ===============================業(yè)務邏輯 ======================================================
from typing import List
import pymysql
import pandas as pd
from docx import Document
from docx.oxml.ns import nsdecls
from docx.oxml import parse_xml
from datetime import datetime
from docx.table import Table, _Cell
def checkParam(input_value: InputValue) -> bool:
    if input_value.database == "" or input_value.user == "" or input_value.password == "" or input_value.host == "" or input_value.port == "":
        showerror(message="請?zhí)顚懲暾臄?shù)據庫連接參數(shù)")
        return False
    return True
def connectDB(input_value: InputValue):
     return pymysql.connect(host=input_value.host,
                            user=input_value.user,
                            password=input_value.password,
                            db=input_value.database,
                            port=int(input_value.port), charset="utf8")
def start_demo(gui: GuiTempldate):
    """ 自定義計算任務
    :param gui:             gui組件對象
    :return:
    """
    input_value = gui.getInputValue()
    # 校驗參數(shù)
    if not checkParam:
        return
    conn = None
    try:
        conn: pymysql.Connection = connectDB(input_value)
        print("建立連接成功")
        download(conn, input_value)
    except OperationalError as e:
        logging.error(e)
        showerror(message="連接數(shù)據庫失敗,請確認數(shù)據庫信息是否正確")
    except Exception as e1:
        # 打印堆棧信息
        e1.with_traceback(None)
        showerror(message="導出失敗")
    finally:
        if conn is not None:
            conn.close()
            print("關閉連接成功")
"""
    表結構SQL
"""
def tableSchemaSql(table_name,dataBase):
   return f"""
   select ORDINAL_POSITION 序號,
    COLUMN_NAME 字段名,
    COLUMN_TYPE 字段類型,
    -- DATA_TYPE 字段類型,
    -- CHARACTER_MAXIMUM_LENGTH 長度,
    -- IS_NULLABLE 是否為空,
    case when COLUMN_DEFAULT is null then '無' else COLUMN_DEFAULT end 默認值,
    COLUMN_COMMENT 字段說明
    FROM
    INFORMATION_SCHEMA.COLUMNS
    where
    table_schema ='{dataBase}'
    and table_name = '{table_name}';
   """
"""
    數(shù)據庫表清單SQL
"""
def databaseTableListSql(database):
    return f"""select table_name as '表名',TABLE_COMMENT '表中文名稱','業(yè)務表',TABLE_COMMENT '說明'
               from information_schema.tables
               where table_schema='{database}' and  LOWER(table_type) ='base table'
    """
def readAllTableToDataFrame(conn,dataBase) -> dict:
    # 獲取所有表的信息
    tables = pd.read_sql_query("SHOW TABLES;", conn)
    df_map = {}
    for _, table in tables.iterrows():
        table_name = table[0]
        if table_name is None or table_name == "":
            continue
        sql = tableSchemaSql(table_name,dataBase)
        df = pd.read_sql_query(sql, conn)
        df = df.reset_index(drop=True)
        df.index = df.index + 1
        df_map[table_name] = df
    return df_map
def readDataBaseTableListToDataFrame(conn, dataBase):
    df_map = {}
    sql = databaseTableListSql(dataBase)
    df = pd.read_sql_query(sql, conn)
    df = df.reset_index(drop=True)
    df.index = df.index + 1
    df_map["tmp"] = df
    return df_map
def readByCustomSqlToDataFrame(conn, input_value):
    # sql模版
    sql = input_value.sql
    # 獲取所有表的信息
    tables = pd.read_sql_query("SHOW TABLES;", conn)
    df_map = {}
    for _, table in tables.iterrows():
        table_name = table[0]
        if table_name is None or table_name == "":
            continue
        # 替換變量名 #{taleName}
        tmp_sql = sql.replace("#{tableName}", table_name)
        df = pd.read_sql_query(tmp_sql, conn)
        df = df.reset_index(drop=True)
        df.index = df.index + 1
        df_map[table_name] = df
    return df_map
def downloadToWord(df_map, save_path, heading):
    # 創(chuàng)建Word文檔
    doc = Document()
    for table_name in df_map:
        df = df_map[table_name]
        # 添加標題
        if heading:
            doc.add_heading(heading, level=3)
        else:
            doc.add_heading(f"【{table_name}表】", level=3)
        # 轉成list表格數(shù)據
        table_data: List = [df.columns.tolist()] + df.values.tolist()
        # 創(chuàng)建 rows x col 表格
        table: Table = doc.add_table(rows=len(table_data), cols=len(table_data[0]))
        # 設置表格樣式
        table.style = "Table Grid"
        # 設置表格字體和大小
        #table.style.font.name = "宋體"
        #table.style.font.size = 5
        # 填充表格內容
        for i, row in enumerate(table_data):
            for j, value in enumerate(row):
                cell: _Cell = table.cell(i, j)
                cell.text = str(value)
                # 設置第一行
                if i == 0:
                    # 設置文本加粗
                    for run in cell.paragraphs[0].runs:
                        run.bold = True
        #  設置第一行背景色為淺灰色D9D9D9
        for i, cell in enumerate(table.rows[0].cells):
            shading_elm_1 = parse_xml(r'<w:shd {} w:fill="D9D9D9"/>'.format(nsdecls('w')))
            cell._tc.get_or_add_tcPr().append(shading_elm_1)
    # 保存Word文檔
    doc.save(save_path)
    print("導出" + save_path + "成功")
def download(conn, input_value):
    dataBase = input_value.database
    calc_mode = input_value.calc_mode
    custom_sql = input_value.sql
    # 讀取所有表結構到DataFrame
    heading = None
    df_map = {}
    if calc_mode == 1:
        # 所有表結構
        print("執(zhí)行模式1")
        df_map = readAllTableToDataFrame(conn, dataBase)
    elif calc_mode == 2:
        # 表清單
        print("執(zhí)行模式2")
        df_map = readDataBaseTableListToDataFrame(conn, dataBase)
        heading = f"【{dataBase}數(shù)據庫表清單】"
    else:
        # 自定義sql
        if custom_sql is None or custom_sql == "":
            showinfo("", "請輸入自定義SQL")
            return
        # 配置了都執(zhí)行一遍并且sql模版真中包含#{tableName}變量
        if input_value.isAllTable == True and custom_sql.find("#{tableName}") != -1:
            print("自定義SQL對所有表執(zhí)行一遍")
            df_map = readByCustomSqlToDataFrame(conn, input_value)
        else:
            df = pd.read_sql_query(custom_sql, conn)
            df_map["tmp"] = df
            heading = f"【{dataBase}自定義SQL數(shù)據】"
    # 選擇導出位置
    now = datetime.now().strftime("%y%m%d%H%M%S")
    file_name = dataBase + f"數(shù)據庫數(shù)據-{now}.docx"
    save_path = filedialog.asksaveasfilename(title=u'保存文件', filetypes=[("word文件", ".docx")], initialfile=file_name)
    print("保存位置目錄", save_path)
    if save_path == "" or save_path is None:
        return
    # 導出到word
    downloadToWord(df_map, save_path, heading)
    showinfo("", "導出成功")
if __name__ == '__main__':
    gui = GuiTempldate()
    gui.showUI()