import math
import threading

import pandas as pd

from app.api.statement import schemas
from core.config.env import env
from libs.db_link import LinkMysql
from starlette.responses import StreamingResponse


# 写入文件
def data_to_file(data, name):
    bk = pd.DataFrame(data)
    with pd.ExcelWriter(f'static/{name}.xlsx') as writer:
        bk.to_excel(writer, sheet_name='Sheet1', index=False)
    file = open(writer, 'rb')
    return StreamingResponse(file, media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')


class RechargeStatement(object):
    """充值报表"""
    def __init__(self):
        self.derive_list = []

    def check_out(self, ch_sql):
        once_res = LinkMysql(env.MysqlDB).query_mysql(ch_sql)
        self.derive_list += once_res

    def derive_query_data(self, condition_list, old_sql):
        if not condition_list:
            der_sql = f"select count(*) as total from orders o INNER JOIN v2_user v on o.userid = v.user_id order by o.id desc"
        elif len(condition_list) == 1:
            der_sql = f"select count(*) as total from orders o INNER JOIN v2_user v on o.userid = v.user_id where {condition_list[0]} order by o.id desc"
        else:
            der_sql = f"select count(*) as total from orders o INNER JOIN v2_user v on o.userid = v.user_id where {' and '.join(condition_list)} order by o.id desc"
        total = LinkMysql(env.MysqlDB).query_mysql(der_sql)
        if total:
            to = total[0].get('total')
            num = math.ceil(to / 10000)
            # 多线程
            ths = []
            # 创建线程
            for x in range(num):
                query_sql = old_sql + f" LIMIT {x*10000},{10000}"
                ths.append(threading.Thread(target=self.check_out, args=[(query_sql)]))
            # 启动线程
            for y in range(num):
                ths[y].start()
            # 等待子进程结束
            for z in range(num):
                ths[z].join()
        return self.derive_list

    def get_statements(self, param, sp=None):
        query_list = []
        if not sp:
            param: schemas.StatementList
        if param.order_number:
            query_list.append(f"o.order_number LIKE '%{param.order_number}%'")
        if param.user_id:
            query_list.append(f"o.userid={int(param.user_id)}")
        if param.sid:
            query_list.append(f"o.sid LIKE '%{param.sid}%'")
        if param.paychannel:
            query_list.append(f"o.paychannel LIKE '%{param.paychannel}%'")
        if param.start_time:
            query_list.append(f"o.current>='{param.start_time}'")
        if param.end_time:
            query_list.append(f"o.current <= '{param.end_time}'")
        fields = ["order_number", "userid", "user_number", "nick_name", "cast(money as char) as money", "paychannel",
                  "sid", "lastupdate", "cast(current as char) as current", "payment_time"]
        if not query_list:
            sql = f"select {','.join(fields)} from orders o INNER JOIN v2_user v on o.userid = v.user_id order by o.id desc"
        elif len(query_list) == 1:
            sql = f"select {','.join(fields)} from orders o INNER JOIN v2_user v on o.userid = v.user_id where {query_list[0]} order by o.id desc"
        else:
            sql = f"select {','.join(fields)} from orders o INNER JOIN v2_user v on o.userid = v.user_id where {' and '.join(query_list)} order by o.id desc"

        if sp:
            sql += f" LIMIT {(int(param.page) - 1) * param.size},{param.page * param.size}"
            query_res = LinkMysql(env.MysqlDB).query_mysql(sql)
        else:
            query_res = self.derive_query_data(query_list, sql)
        if query_res:
            df = pd.DataFrame(query_res)
            print(df)
            count = df.groupby("money").sum()
            # amount_list = list(count["money"].values)
        else:
            count = 0
        return query_res, count


class WithdrawStatement(object):
    """提现报表"""
    @staticmethod
    def get_user_withdraw_cash(param, ty=None):
        """用户提现"""
        user_list = []
        if param.user_id:
            user_list.append(f"o.userid={param.user_id}")
        if param.status:
            user_list.append(f"o.status={param.status}")
        if param.start_time:
            user_list.append(f"o.current>='{param.start_time}'")
        if param.end_time:
            user_list.append(f"o.current <= '{param.end_time}'")
        fields = ["id", "bank_code", "usernumber", "nickname", "truename", "idcard", "account", "status", "money",
                 "platformServiceFee", "thirdServiceFee", "final_money", "cast(current as char) as current"]
        if not user_list:
            sql = f"select {','.join(fields)} from tixian_order order by id desc"
        elif len(user_list) == 1:
            sql = f"select {','.join(fields)} from tixian_order where {user_list[0]} order by id desc"
        else:
            sql = f"select {','.join(fields)} from tixian_order where {' and '.join(user_list)} order by id desc"

        if ty:
            sql += f" LIMIT {(int(param.page)-1) * param.size},{param.page * param.size}"
        query_res = LinkMysql(env.MysqlDB).query_mysql(sql)
        return query_res

    @staticmethod
    def get_guild_withdraw_cash(cond, co):
        """公会提现"""
        guild_list = []
        if cond.guild_id:
            guild_list.append(f"o.guild_id={cond.guild_id}")
        if cond.status:
            guild_list.append(f"o.status LIKE '%{cond.status}%'")
        if cond.start_time:
            guild_list.append(f"o.current>='{cond.start_time}'")
        if cond.end_time:
            guild_list.append(f"o.current <= '{cond.end_time}'")
        gui_fields = ["account", "finalMoney", "guild_name", "x.id", "money", "parentid", "taxRate", "thirdServiceFee",
                      "x.type", "x.update_time"]

        if not guild_list:
            sql = f"select {','.join(gui_fields)} from guild_ti_xian x INNER JOIN guild g on x.guild_id = g.id order by o.id desc"
        elif len(guild_list) == 1:
            sql = f"select {','.join(gui_fields)} from guild_ti_xian x INNER JOIN guild g on x.guild_id = g.id where {guild_list[0]} order by o.id desc"
        else:
            sql = f"select {','.join(gui_fields)} from guild_ti_xian x INNER JOIN guild g on x.guild_id = g.id where {' and '.join(guild_list)} order by o.id desc"

        if co:
            sql += f" LIMIT {(int(cond.page) - 1) * cond.size},{cond.page * cond.size}"
        query_res = LinkMysql(env.MysqlDB).query_mysql(sql)
        return query_res


