from datetime import datetime
from middleware.config_handler import db_connect
"""針對產品返回所有bug資訊"""
def sql_pakeage(productid):
    """
    BUG狀態統計SQL封裝
    :param productid:
    :return:
    """
    bug_sql = "select count(*) from zt_bug where product in %s and deleted='0'"% (
    productid)

    resolved_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` = 'resolved' and resolution <> 'postponed' " % (
        productid)

    not_resolved_bug_sql = "select count(*) from zt_bug where product in %s  and deleted = '0' and `status` =  'active' " % (
        productid)

    postponed_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` <> 'closed' and resolution = 'postponed' " % (
        productid)

    closed_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` = 'closed' " % (
        productid)

    return  bug_sql,resolved_bug_sql,not_resolved_bug_sql,postponed_bug_sql,closed_bug_sql


def  test_product_bug(productid):
    """
    產品BUG情況統計
    :param productid:
    :return:
    """
    #總bug數
    all_bug=db_connect.query_sql_df(sql_pakeage(productid)[0])

    #已解決bug數
    resolved_bug = db_connect.query_sql_df(sql_pakeage(productid)[1])

    # 未解決BUG數（當前顯示BUG狀態為未解決的。包含當前還沒被解決的、之前遺留的未解決、以及reopen的BUG（累計數據））
    not_resolved_bug = db_connect.query_sql_df(sql_pakeage(productid)[2])

    # 延期BUG數
    postponed_bug= db_connect.query_sql_df( sql_pakeage(productid)[3])

    # 已關閉BUG數
    closed_bug = db_connect.query_sql_df(sql_pakeage(productid)[4])

    statistics_bug = { "總BUG數":all_bug[0],"已解決BUG": resolved_bug[0], "未解決BUG": not_resolved_bug[0], "已關閉BUG": closed_bug[0],
                      "延期解決BUG": postponed_bug[0]}
    print(statistics_bug)

    return  statistics_bug

def test_product_bug_near(day, product):
    """
        最近總的BUG情況統計統計
        :param: day 根據輸入天數
        :return:
        """
    now = (datetime.datetime.now() + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S') #伺服器時區要 + 8h
    recent_sevenday = (datetime.datetime.now() - datetime.timedelta(days=day)).strftime("%Y-%m-%d %H:%M:%S")
    new_near_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where  b.product in %s and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
    open_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where  b.product  in %s and  b.STATUS = "active" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
    close_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where  b.product  in %s and  b.STATUS = "closed" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
    close_unbug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where  b.product  in %s and  b.STATUS = "resolved" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
    statistics_bug = { "本周新增BUG數":new_near_bug[0], "本周未解決BUG數":open_bug[0],"本周已解決BUG數":close_bug[0],"本周已解決待驗證BUG數":close_unbug[0]}
    return statistics_bug

def bug_count(day, product):
    """
    最近總的BUG情況統計明細數據
    :param: day 根據輸入天數
    :return:
    """
    now = (datetime.datetime.now() + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S') #伺服器時區要 + 8h
    now_day = datetime.datetime.now().strftime('%Y-%m-%d')
    recent_sevenday = (datetime.datetime.now()-datetime.timedelta(days=day)).strftime("%Y-%m-%d %H:%M:%S")
    bug_sql = """
SELECT p.name,b.title,b.assignedTo,b.severity,b.type,b.status,b.openedBy, CAST(openedDate AS CHAR) AS openedDate from zt_bug b left join zt_product p on b.product = p.id 
where b.STATUS <> 'closed' and b.product in %s and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now)
    print(bug_sql)
    recent_sevenday_bug = db_connect.query_sql_df(bug_sql)
    return recent_sevenday_bug

import json
import urllib.request

from config.path_config import data_file
from handler.loguru_handler import logger
def send_bug(url, data_file):
    # url = r"//oapi.dingtalk.com/robot/send?access_token=XXXXXXX"
    logger.info("構建釘釘機器人的webhook")
    header = {"Content-Type": "application/json","Charset": "UTF-8"}

    with open(data_file, "r", encoding="utf-8") as f:
        conntent = f.read()

    data = {
        "msgtype": "markdown",
        "markdown": {
            "title": "測試bug統計",
            "text": conntent
        },
        "at": {
             "isAtAll": False     #@全體成員（在此可設置@特定某人）
        }
    }
    logger.info(f"構建請求內容:{data}")
    sendData = json.dumps(data)
    sendData = sendData.encode("utf-8")
    request = urllib.request.Request(url=url, data=sendData, headers=header)
    logger.info("發送請求")
    opener = urllib.request.urlopen(request)
    logger.info("請求求發回的數據構建成為文件格式")
    res = opener.read()
    logger.info(f"返回結果為:{res}")

def bug_html(lis ,html_file):
    """
    對查詢bug明細轉html文件
    :param lis
    :param html_file
    """
    conten_title = []
    for key in lis[0]:
        conten_title.append(key)
    a = "</th><th>".join(conten_title)
    con_title = "<tr><th>" + a + "</th></tr>"
    conten_val = []
    con = ""
    for i in range(0, len(lis)):
        for index, v in enumerate(lis[i]):
            if index ==0:
                lis[i][v] ="<tr><td>" + lis[i][v]
            con = con + str(lis[i][v]) + "</td><td>"
        con = con[0:-2] +"r>"
        con = con + "\n"
    head = """<meta charset="utf-8">
    <style type="text/css">
    table.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #9dcc7a;border-collapse: collapse;}
    table.tftable th {font-size:12px;background-color:#abd28e;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;text-align:left;}
    table.tftable tr {background-color:#ffffff;}
    table.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;}
    </style>\n<table id="tfhover" class="tftable" border="1">\n"""
    last =  "</table>"
    htm = head + con_title + con + last

    with open(html_file, "w", encoding="utf-8") as f:
        f.write(htm)