import datetime
import math

from common.utils.models import MonitorConnection, MonitorOAConnection, BaseConnection
import logging
import pandas as pd
import numpy as np
from console.home_page.services import get_hour_ele

from console.electricityuser.models import ElectricityUser

from console.electricity_bill.models import TariffType

LOG = logging.getLogger()
HOUR_LIST = ["00", "01", "02", "03", "04", "05", "06", "07", "08", "09",
             "10", "11", "12", "13", "14", "15", "16", "17", "18", "19",
             "20", "21", "22", "23"]


def get_ele_user_power_data(**kwargs):
    """
    获取客户户号的实时电能曲线
    :param kwargs:
    :return:
    """
    conn = MonitorConnection()
    oa_conn = MonitorOAConnection()
    base_conn = BaseConnection()
    try:
        sql = """
        select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
         left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
        """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        vr = ratio[0].get("vr") if ratio else 1
        cr = ratio[0].get("cr") if ratio else 1
        point_id = ratio[0].get("id") if ratio else None
        sql = """
        select round(ept*{}*{}) ept,created_time from mo_{} where date_format(created_time,"%Y-%m-%d")={} and point_id={}
        """.format(vr, cr, kwargs.get("ele_user"), datetime.datetime.today(), point_id)

        data = conn.query(sql)
        result = {}
        time_list = []
        data_list = []
        for item in data:
            time_list.append(item.get("created_time"))
            data_list.append(item.get("ept"))
        result["time"] = time_list
        result["data"] = data_list

        return data
    except Exception as e:
        sql = """
        select tm,ep from tb_{} where date(tm)={}
        """.format(kwargs.get("ele_user"), datetime.datetime.today().date())
        try:
            data = oa_conn.query(sql)
        except Exception as exc:
            LOG.error(exc)
            return {}
        tm = []
        ep = []
        for item in data:
            tm.append(item.get("tm"))
            ep.append(item.get("ep"))
        ep_raw = pd.Series(ep, index=tm)
        result = get_hour_ele(ep_raw)
        if result is None or result.empty:
            return {}
        rst = {}
        rst["time"] = result.index,
        rst["data"] = result.values
        result = rst

        return result


def get_customer_power_data(**kwargs):
    """
    返回客户当天的用能曲线
    :param kwargs:
    :return:
    """
    result = {}
    power_data = []
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id")).values_list("number", flat=True)
    # 获取每个户号的当天的电能
    power_datas = []
    result_time = None
    for ele_user in ele_users:
        power_data = get_ele_hour_power_data(ele_user=ele_user)
        if kwargs.get("customer_id") in [80824, 33292]:
            return power_data
        power_datas.append(power_data.get("data"))
        result_time = power_data.get("time")
    if power_data:
        power_data = np.sum(power_datas, axis=0).tolist() if power_datas else None
        result["time"] = result_time
        result["data"] = power_data
    return result


def get_ele_hour_power_data(**kwargs):
    """
    获取当天每个小时的电能
    :param kwargs:
    :return:
    """
    new_monitor = MonitorConnection()
    base_conn = BaseConnection()
    try:
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" and a.id=5467
                            """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]

            sql = """select ept,tm from mo_{} where point_id=5467 and tm>"{}"
                        """.format(kwargs.get("ele_user"),
                                   datetime.datetime.now().date())
            data = new_monitor.query(sql)
            tm = []
            ep = []
            for item in data:
                tm.append(item.get("tm"))
                ep.append(float(item.get("ept", 0)))
            ep_raw = pd.Series(ep, index=tm)
            result = get_hour_ele(ep_raw)
            if result is None or result.empty:
                return {}
            rst = {}
            rst["time"] = result.index,
            rst["data"] = result.values
            rst = recent_power_handle(rst, float(ratio.get("vr")), float(ratio.get("cr")))
            return rst
        elif kwargs.get("ele_user") == "0062633473":
            sql = """
                select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                """
            ratio = base_conn.query(sql)
            rst_list = []
            for rt in ratio:
                sql = """select ept,tm from mo_{} where point_id={} and tm>"{}"
                                                    """.format(kwargs.get("ele_user"),
                                                               rt.get("id"),
                                                               datetime.datetime.now().date())
                data = new_monitor.query(sql)
                tm = []
                ep = []
                for item in data:
                    tm.append(item.get("tm"))
                    ep.append(float(item.get("ept", 0)))
                ep_raw = pd.Series(ep, index=tm)
                result = get_hour_ele(ep_raw)
                if result is None or result.empty:
                    continue
                rst = {}
                rst["time"] = result.index,
                rst["data"] = result.values
                rst = recent_power_handle(rst, float(rt.get("vr")), float(rt.get("cr")))
                rst_list.append(rst)
            return organize_data(rst_list)
        else:
            sql = """
                   select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                    left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                   """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)
            result = {}
            time_list = []
            data_list = []
            for hour in HOUR_LIST:
                sql = """
                    select max(ept) ept,point_id from (
                    select ifnull(max(ept)-min(ept),0) ept,point_id from mo_{} where date_format(created_time,"%Y-%m-%d %H")='{}' group by point_id
                    ) A
                        """.format(kwargs.get("ele_user"), "{} {}".format(datetime.datetime.today().date(), hour))
                data = new_monitor.query(sql)
                data = data[0] if data else {}
                if data:
                    for item in ratio:
                        if item.get("id") == data.get("point_id"):
                            data["ept"] = round(data["ept"] * float(item.get("vr")) * float(item.get("cr")), 2)
                            break

                time_list.append(
                    datetime.datetime.strptime("{} {}:00:00".format(datetime.datetime.today().date(), hour),
                                               "%Y-%m-%d %H:%M:%S"))
                data_list.append(data.get("ept"))
            result["time"] = time_list
            result["data"] = data_list
            return result
    # 没有安装新的检测设备
    except Exception as e:
        LOG.error(e)
        oa_conn = MonitorOAConnection()
        sql = """
                select tm,sum(ep) ep from (select date_format(tm,"%Y-%m-%d %H") tm,
                max(ifnull(ep,0)) ep,line from tb_{} where date(tm)='{}' group by line)A group by tm
                """.format(kwargs.get("ele_user"), datetime.datetime.today().date())
        try:
            data = oa_conn.query(sql)
        except Exception as exc:
            LOG.error(exc)
            return {}
        tm = []
        ep = []
        for item in data:
            tm.append(item.get("tm") + ":00:00")
            ep.append(float(item.get("ep", 0)))
        ep_raw = pd.Series(ep, index=tm)
        result = get_hour_ele(ep_raw)
        if result is None or result.empty:
            return {}

        rst = {}
        rst["time"] = result.index,
        rst["data"] = result.values
        result = rst

        return result


def organize_data(data_list):
    rst = {}
    rst["data"] = []
    for data in data_list:
        rst["time"] = data.get("time")
        for i in range(len(data.get("data"))):
            if len(rst["data"]) > i:
                rst["data"][i] += data["data"][i]
            else:
                rst["data"].append(data["data"][i])
    return rst


def get_ele_user_power_statistic(**kwargs):
    """
    获取户号的电量统计数据（统计本月，当日，昨日，上月的电量）
    :param kwargs:
    :return:
    """
    monitor_conn = MonitorConnection()
    base_conn = BaseConnection()
    today = datetime.datetime.today()
    year = last_year = today.year
    month = today.month
    last_month = month - 1
    if last_month < 0:
        last_month = 12,
        last_year -= 1

    try:
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                    select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                    left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                    and a.id=5467
                    """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """
                    select round(max(ept)*{}*{},2) ept,`type` from (
                    select ifnull(max(ept)-min(ept),0) ept,
                    case when date_format(tm,"%Y-%m-%d")="{}" then "today"
                    when date_format(tm,"%Y-%m-%d")="{}" then "yesterday"
                     end `type`
                     from mo_{} where point_id=5467 group by `type`
                    ) A group by `type`
                    """.format(ratio.get("vr"), ratio.get("cr"), today.strftime("%Y-%m-%d"),
                               (today - datetime.timedelta(1)).strftime("%Y-%m-%d"),
                               kwargs.get("ele_user"))
            result1 = monitor_conn.query(sql)
            sql = """
                                select round(max(ept)*{}*{},2) ept,`type` from (
                                select ifnull(max(ept)-min(ept),0) ept,
                                case when date_format(tm,"%Y-%m")="{}" then "this_month"
                                when date_format(tm,"%Y-%m")="{}" then "last_month"
                                 end `type`
                                 from mo_{} where point_id=5467 group by `type`
                                ) A group by `type`
                                """.format(ratio.get("vr"), ratio.get("cr"),
                                           "{}-{}".format(year, str(month).zfill(2)),
                                           "{}-{}".format(last_year, str(last_month).zfill(2)),
                                           kwargs.get("ele_user"))
            result2 = monitor_conn.query(sql)
            result = result1 + result2
            return result
        if kwargs.get("ele_user") == "0062633473":
            sql = """
                    select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                    """
            ratio = base_conn.query(sql)
            result = []
            for rt in ratio:
                sql = """
                        select round(max(ept)*{}*{},2) ept,`type` from (
                        select ifnull(max(ept)-min(ept),0) ept,
                        case when date_format(tm,"%Y-%m-%d")="{}" then "today"
                        when date_format(tm,"%Y-%m-%d")="{}" then "yesterday"
                         end `type`
                         from mo_{} where point_id={} group by `type`
                        ) A group by `type`
                        """.format(ratio.get("vr"), ratio.get("cr"), today.strftime("%Y-%m-%d"),
                                   (today - datetime.timedelta(1)).strftime("%Y-%m-%d"),
                                   kwargs.get("ele_user"), rt.get("id"))
                result1 = monitor_conn.query(sql)
                sql = """
                                    select round(max(ept)*{}*{},2) ept,`type` from (
                                    select ifnull(max(ept)-min(ept),0) ept,
                                    case when date_format(tm,"%Y-%m")="{}" then "this_month"
                                    when date_format(tm,"%Y-%m")="{}" then "last_month"
                                     end `type`
                                     from mo_{} where point_id={} group by `type`
                                    ) A group by `type`
                                    """.format(ratio.get("vr"), ratio.get("cr"),
                                               "{}-{}".format(year, str(month).zfill(2)),
                                               "{}-{}".format(last_year, str(last_month).zfill(2)),
                                               kwargs.get("ele_user"),
                                               rt.get("id"))
                result2 = monitor_conn.query(sql)
                result += result1 + result2
            return result

        sql = """
                      select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                       left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                      """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        sql = """
        select max(ept) ept,`type`,point_id from (
        select ifnull(max(ept)-min(ept),0) ept,
        case when date_format(tm,"%Y-%m-%d")="{}" then "today"
        when date_format(tm,"%Y-%m-%d")="{}" then "yesterday"
        when date_format(tm,"%Y-%m")="{}" then "this_month"
        when date_format(tm,"%Y-%m")="{}" then "last_month"
         end `type`,
         point_id
         from mo_{}  group by point_id,`type`
        ) A group by `type`
        """.format(today.strftime("%Y-%m-%d"),
                   (today - datetime.timedelta(1)).strftime("%Y-%m-%d"),
                   "{}-{}".format(year, str(month).zfill(2)),
                   "{}-{}".format(last_year, str(last_month).zfill(2)),
                   kwargs.get("ele_user"))
        result = monitor_conn.query(sql)
        for item in result:
            for it in ratio:
                if it.get("id") == item.get("point_id"):
                    item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
        return result

    except Exception as e:
        LOG.error(e)
        try:
            oa_conn = MonitorOAConnection()
            sql = """
                    select max(ept) ept,`type` from (
                    select ifnull(max(ep)-min(ep),0) ept,
                    case when date_format(tm,"%Y-%m-%d")="{}" then "today"
                    when date_format(tm,"%Y-%m-%d")="{}" then "yesterday"
                    when date_format(tm,"%Y-%m")="{}" then "this_month"
                    when date_format(tm,"%Y-%m")="{}" then "last_month"
                     end `type`
                     from tb_{}  group by line,`type`
                    ) A group by `type`
                    """.format(today.strftime("%Y-%m-%d"),
                               (today - datetime.timedelta(1)).strftime("%Y-%m-%d"),
                               "{}-{}".format(year, str(month).zfill(2)),
                               "{}-{}".format(last_year, str(last_month).zfill(2)),
                               kwargs.get("ele_user"))
            result = oa_conn.query(sql)
            return result
        except Exception as e:
            LOG.error(e)
            return [
            ]


def get_customer_power_statistic(**kwargs):
    """
    获取客户的电能统计数据
    :param kwargs:
    :return:
    """
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id"), deleted=False)
    data = []
    for item in ele_users:
        data += get_ele_user_power_statistic(**{"ele_user": item.number})
    result = {}
    for item in data:
        if item.get("type") not in result:
            result[item.get("type")] = item.get("ept")
        else:
            result[item.get("type")] += item.get("ept")
    return result


def get_recent_month_power_data(**kwargs):
    """
    获取当月的电能
    :param kwargs:
    :return:
    """
    monitor_conn = MonitorConnection()
    base_conn = BaseConnection()
    try:
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" and a.id=5467
                            """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]

            sql = """select ept,tm from mo_{} where point_id=5467 and tm>"{}"
                        """.format(kwargs.get("ele_user"),
                                   kwargs.get("day")
                                   )
            data = monitor_conn.query(sql)
            tm = []
            ep = []
            for item in data:
                tm.append(item.get("tm"))
                ep.append(float(item.get("ept", 0)))
            ep_raw = pd.Series(ep, index=tm)
            result = get_hour_ele(ep_raw)
            if result is None or result.empty:
                return {}
            rst = {}
            rst["time"] = result.index,
            rst["data"] = result.values
            power_data = power_handle(rst, float(ratio.get("vr")), float(ratio.get("cr")))
            return power_data
        if kwargs.get("ele_user") == "0062633473":
            sql = """
                            select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                            """
            ratio = base_conn.query(sql)
            rst_data = []
            for rt in ratio:
                sql = """select ept,tm from mo_{} where point_id={} and tm>"{}"
                                   """.format(kwargs.get("ele_user"),
                                              rt.get("id"),
                                              kwargs.get("day")
                                              )
                data = monitor_conn.query(sql)
                tm = []
                ep = []
                for item in data:
                    tm.append(item.get("tm"))
                    ep.append(float(item.get("ept", 0)))
                ep_raw = pd.Series(ep, index=tm)
                result = get_hour_ele(ep_raw)
                if result is None or result.empty:
                    continue
                rst = {}
                rst["time"] = result.index,
                rst["data"] = result.values
                power_data = sh_power_handle(rst, float(rt.get("vr")), float(rt.get("cr")))
                rst_data.append(power_data)
            return organize_uvf_data(rst_data)

        sql = """
                select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        result = {}
        result_data = []
        for item in ratio:
            sql = """
            SELECT round(max(ept),2) ept,type,`day` from (
    SELECT ifnull(max(ept) -min(ept),0)*{}*{} ept,
    case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
    when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
    else "u" end `type`,
    date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id={}  group by type,`day`
    )A GROUP BY type, `day`
            """.format(float(item.get("vr")), float(item.get("cr")), kwargs.get("ele_user"), kwargs.get("day"),
                       item.get("id"))
            data = monitor_conn.query(sql)
            result_data += data
        for it in result_data:
            if it.get("type") not in result.keys():
                result[it.get("type")] = {it.get("day"): it.get("ept")}
            else:
                if it.get("day") not in result[it.get("type")].keys():
                    result[it.get("type")][it.get("day")] = it.get("ept")
                else:
                    result[it.get("type")][it.get("day")] += it.get("ept")
        return result


    except Exception as e:
        LOG.error(e)
        try:
            oa_con = MonitorOAConnection()
            sql = """
            SELECT max(ept) ept,type,`day` from (
SELECT ifnull(max(ep) -min(ep),0) ept,
case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
else "u" end `type`,
date_format(tm,"%Y-%m-%d") `day` from tb_{}  where tm >"{}" GROUP BY line,type,`day`
)A GROUP BY type, `day`
            """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_con.query(sql)
            result = {}
            # result_data = []
            for it in data:
                if it.get("type") not in result.keys():
                    result[it.get("type")] = {it.get("day"): it.get("ept")}
                else:
                    if it.get("day") not in result[it.get("type")].keys():
                        result[it.get("type")][it.get("day")] = it.get("ept")
                    else:
                        result[it.get("type")][it.get("day")] += it.get("ept")
            return result
        except Exception as e:
            LOG.error(e)
            return None


def get_recent_month_date():
    date_list = []
    now = datetime.datetime.now()
    year = now.year
    month = now.month
    day = now.day
    for i in range(day):
        date_list.insert(0, "{}-{}-{}".format(year, str(month).zfill(2), str(day - i).zfill(2)))
    return date_list


def get_customer_recent_month_power_data(**kwargs):
    """
    获取当月客户的用能数据
    :param kwargs:
    :return:
    """
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id"), deleted=False)
    result = []
    year = datetime.datetime.now().year
    month = datetime.datetime.now().month
    for item in ele_users:
        data = get_recent_month_power_data(**{
            "ele_user": item.number,
            "day": "{}-{}-01".format(year, month)
        })
        if data:
            if isinstance(data, list):
                result += data
            else:
                result.append(data)
    result_data = {}
    for data in result:
        for k, v in data.items():
            if k not in result_data.keys():
                result_data[k] = v
            else:
                for key, value in v.items():
                    if key not in result_data[k].keys():
                        result_data[k][key] = value
                    else:
                        result_data[k][key] += value

    return result_data


def get_recent_month_activate_power(**kwargs):
    """
    获取户号当月的累积电量
    :param kwargs:
    :return:
    """
    try:
        monitor_conn = MonitorConnection()
        base_conn = BaseConnection()
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                        select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                        left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                        and a.id=5467
                        """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """  
                    select round(ifnull(max(ept),0)*{}*{},2) ept ,`day` from (
                    SELECT ifnull(max(ept),0) ept,
            date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id=5467 GROUP BY `day`
                    )A group by `day`
                    """.format(float(ratio.get("vr")), float(ratio.get("cr")), kwargs.get("ele_user"),
                               kwargs.get("day"))
            data = monitor_conn.query(sql)
            return data
        if kwargs.get("ele_user") == "0062633473":
            sql = """
                select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                                        """
            ratio = base_conn.query(sql)
            data = []
            for rt in ratio:
                sql = """  
                        select round(ifnull(max(ept),0)*{}*{},2) ept ,`day` from (
                        SELECT ifnull(max(ept),0) ept,
                date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id={} GROUP BY `day`
                        )A group by `day`
                        """.format(float(rt.get("vr")), float(rt.get("cr")), kwargs.get("ele_user"),
                                   kwargs.get("day"), rt.get("id"))
                data += monitor_conn.query(sql)
            return data
        sql = """
            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
            """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        sql = """  
        
        SELECT ifnull(max(ept),0) ept,point_id,date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" GROUP BY point_id,`day`
        
        """.format(kwargs.get("ele_user"), kwargs.get("day"))
        data = monitor_conn.query(sql)
        for item in data:
            for it in ratio:
                if it.get("id") == item.get("point_id"):
                    item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
        return data
    except Exception as e:
        LOG.error(e)
        oa_conn = MonitorOAConnection()
        try:
            sql = """
          
            select sum(ept) ept,`day` from 
            (SELECT ifnull(max(ep),0) ept,date_format(tm,"%Y-%m-%d") `day`,line from tb_{}  where tm >"{}" GROUP BY line, `day`)A
            group by `day`
           
            """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_conn.query(sql)

            return data
        except Exception as e:
            LOG.error(e)
            return []


def get_customer_recent_month_activate_power(**kwargs):
    """
    获取客户当月累积电能
    :param kwargs:
    :return:
    """
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id"), deleted=False)
    result = []
    year = datetime.datetime.now().year
    month = datetime.datetime.now().month
    for item in ele_users:
        data = get_recent_month_activate_power(**{
            "ele_user": item.number,
            "day": "{}-{}-01".format(year, month)
        })
        # if kwargs.get("customer_id") == 80824:
        #     return data
        result += data
    result_data = {}
    for data in result:
        if data.get("day") not in result_data:
            result_data[data.get("day")] = data.get("ept")
        else:
            result_data[data.get("day")] += data.get("ept")
    return result_data


def get_eleuser_power(**kwargs):
    """
    获取户号的某天的有功功率（平均值）
    :param kwargs:
    :return:
    """
    try:
        monitor_conn = MonitorConnection()
        base_conn = BaseConnection()
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                                select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                                left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                                and a.id=5467
                                """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """
                    select round(ifnull(max(ept),0)*{}*{},2) ept,`day`,`type` from (
                    SELECT ifnull(avg(p),0) ept,
            case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
            when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
            else "u" end `type`,
            date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id=5467 GROUP BY type,`day`
                    )A group by `day`,`type`
                    """.format(float(ratio.get("vr")), float(ratio.get("cr")), kwargs.get("ele_user"),
                               kwargs.get("day"))
            data = monitor_conn.query(sql)
            return data
        if kwargs.get("ele_user") == "0062633473":
            sql = """
                            select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                                                    """
            ratio = base_conn.query(sql)
            data = []
            for rt in ratio:
                sql = """
                        select round(ifnull(max(ept),0)*{}*{},2) ept,`day`,`type` from (
                        SELECT ifnull(avg(p),0) ept,
                case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
                when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
                else "u" end `type`,
                date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id={} GROUP BY type,`day`
                        )A group by `day`,`type`
                        """.format(float(rt.get("vr")), float(rt.get("cr")), kwargs.get("ele_user"),
                                   kwargs.get("day"),
                                   rt.get("id"))
                data += monitor_conn.query(sql)
            return data
        sql = """
                    select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                    left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                    """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        sql = """
        select ifnull(max(ept),0) ept,`day`,`type`,point_id from (
        SELECT ifnull(avg(p),0) ept,point_id,
case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
else "u" end `type`,
date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" GROUP BY point_id,type,`day`
        )A group by `day`,`type`
        """.format(kwargs.get("ele_user"), kwargs.get("day"))
        data = monitor_conn.query(sql)
        for item in data:
            for it in ratio:
                if it.get("id") == item.get("point_id"):
                    item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
        return data
    except Exception as e:
        LOG.error(e)
        try:
            oa_conn = MonitorOAConnection()
            sql = """
            select ifnull(max(ept),0) ept,`day`,`type` from (
        SELECT ifnull(avg(psum),0) ept,
case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
else "u" end `type`,
date_format(tm,"%Y-%m-%d") `day` from tb_{}  where tm >"{}" GROUP BY line,type,`day`
        )A group by `day`,`type`
            """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_conn.query(sql)
            return data
        except Exception as e:
            LOG.error(e)
            return []


def get_customer_recent_month_power(**kwargs):
    """
    获取客户当月有功功率
    :param kwargs:
    :return:
    """
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id"), deleted=False)
    result = []
    year = datetime.datetime.now().year
    month = datetime.datetime.now().month
    for item in ele_users:
        result += get_eleuser_power(**{
            "ele_user": item.number,
            "day": "{}-{}-01".format(year, month)
        })

    result_data = {}
    for data in result:
        if data.get("type") not in result_data.keys():
            result_data[data.get("type")] = {}
            result_data[data.get("type")][data.get("day")] = data.get("ept")
        else:
            if data.get("day") not in result_data[data.get("type")]:
                result_data[data.get("type")][data.get("day")] = data.get("ept")
            else:
                result_data[data.get("type")][data.get("day")] += data.get("ept")
    for type, value in result_data.items():
        for k, v in value.items():
            value[k] = v / len(ele_users)
    return result_data


def get_eleuser_all_power(**kwargs):
    '''
    获取户号有功和无功功率
    :param kwargs:
    :return:
    '''
    try:
        monitor_conn = MonitorConnection()
        base_conn = BaseConnection()
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                                        select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                                        left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                                        and a.id=5467
                                        """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """ 
                    select round(ifnull(max(ept),0)*{}*{},2) ept,ifnull(max(eq),0) eq,`day`,`type` from (
                        SELECT ifnull(avg(p),0) ept,ifnull(avg(q),0) eq,
            case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
            when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
            else "u" end `type`,
            date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" and point_id=5467 GROUP BY type,`day`
                       )A group by `day`,`type`
                    """.format(float(ratio.get("vr")), float(ratio.get("cr")), kwargs.get("ele_user"),
                               kwargs.get("day"))
            data = monitor_conn.query(sql)
            return data

        sql = """
                            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                            """.format(kwargs.get("ele_user"))
        ratio = base_conn.query(sql)
        sql = """ 
        select ifnull(max(ept),0) ept,ifnull(max(eq),0) eq,`day`,`type`,point_id from (
            SELECT ifnull(avg(p),0) ept,ifnull(avg(q),0) eq,point_id,
case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
else "u" end `type`,
date_format(tm,"%Y-%m-%d") `day` from mo_{}  where tm >"{}" GROUP BY point_id,type,`day`
           )A group by `day`,`type`
        """.format(kwargs.get("ele_user"), kwargs.get("day"))
        data = monitor_conn.query(sql)
        for item in data:
            for it in ratio:
                if it.get("id") == item.get("point_id"):
                    item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
                    item["eq"] = round(item["eq"] * float(it.get("vr")) * float(it.get("cr")), 2)
        return data
    except Exception as e:
        LOG.error(e)
        try:
            oa_conn = MonitorOAConnection()
            sql = """
            select ifnull(max(ept),0) ept,ifnull(max(eq),0) eq,`day`,`type` from (
            SELECT ifnull(avg(psum),0) ept,ifnull(avg(qsum),0) eq,
case when hour(tm) in (8,9,10,18,19,20) or (hour(tm) in (13,14) and month(tm) in (5,6,7,8,9,10)) then "f"
when hour(tm) in (22,23,0,1,2,3,4,5) then "v"
else "u" end `type`,
date_format(tm,"%Y-%m-%d") `day` from tb_{}  where tm >"{}" GROUP BY line,type,`day`
           )A group by `day`,`type`
            """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_conn.query(sql)
            return data
        except Exception as e:
            LOG.error(e)
            return []


def get_customer_power_factor(**kwargs):
    ele_users = ElectricityUser.objects.filter(
        customer_id=kwargs.get("customer_id"), deleted=False)
    result = []
    year = datetime.datetime.now().year
    month = datetime.datetime.now().month
    for item in ele_users:
        result += get_eleuser_all_power(**{
            "ele_user": item.number,
            "day": "{}-{}-01".format(year, month)
        })
    result_data = {}
    for data in result:
        if data.get("type") not in result_data:
            result_data[data.get("type")] = {}
            result_data[data.get("type")][data.get("day")] = {
                "ept": data.get("ept"),
                "eq": data.get("eq")
            }
        else:
            if data.get("day") not in result_data[data.get("type")]:
                result_data[data.get("type")][data.get("day")] = {
                    "ept": data.get("ept"),
                    "eq": data.get("eq")
                }
            else:
                result_data[data.get("type")][data.get("day")]["ept"] += data.get("ept")
                result_data[data.get("type")][data.get("day")]["eq"] += data.get("eq")
    for key, value in result_data.items():
        for k, v in value.items():
            value[k] = float(v.get("ept", 0)) / math.sqrt(math.pow(v.get("ept", 0), 2) + math.pow(v.get("eq", 0), 2))

    return result_data


def get_10_day_power(**kwargs):
    ele_user = ElectricityUser.objects.filter(customer_id=kwargs.get("customer_id"))
    customer_data = []
    for item in ele_user:
        billing_method_obj = TariffType.objects.filter(id=item.billing_method).first()
        billing_method = {
            "peak_prise": billing_method_obj.peak_prise,
            "flat_prise": billing_method_obj.flat_prise,
            "valley_prise": billing_method_obj.valley_prise,
        } if billing_method_obj else {}

        param = {
            "ele_user": item.number,
            "day": " tm > '{}'".format((datetime.datetime.now() - datetime.timedelta(9)).strftime("%Y-%m-%d")),
            "billing": billing_method
        }
        data = get_10_day_power_data_and_money(**param)
        if kwargs.get("customer_id") in [80824, 33292]:
            return data
        if data:
            customer_data.append(data)
    result_power = {}
    money = {}
    for item in customer_data:
        for power in item.get("power"):
            if power.get("type") not in result_power:
                result_power[power.get("type")] = {}
                result_power[power.get("type")][power.get("date")] = power.get("ept")
            else:
                if power.get("date") not in result_power[power.get("type")]:
                    result_power[power.get("type")][power.get("date")] = power.get("ept")
                else:
                    result_power[power.get("type")][power.get("date")] += power.get("ept")
        for k, v in item.get("money").items():
            if k not in money.keys():
                money[k] = v
            else:
                money[k] += v

    return {
        "power": result_power,
        "money": money
    }


def get_recent_10_day_date():
    date_list = []
    for i in range(10):
        date_list.insert(0, (datetime.datetime.today() - datetime.timedelta(i)).date())
    return date_list


def union_dict_values(list):
    result = {
        "consumption": {
            "valley": 0.00,
            "usual": 0.00,
            "peak": 0.00
        },
        "cost": {
            "valley": 0.00,
            "usual": 0.00,
            "peak": 0.00
        }
    }
    for item in list:
        for k, v in item.items():
            if k in ["consumption", "cost"]:
                for key, value in v.items():
                    result[k][key] += value
    return result


def get_6_month_power(**kwargs):
    ele_user = ElectricityUser.objects.filter(customer_id=kwargs.get("customer_id"))
    date_list = get_next_6_month()
    customer_data = []
    for item in ele_user:
        billing_method_obj = TariffType.objects.filter(id=item.billing_method).first()
        billing_method = {
            "peak_prise": billing_method_obj.peak_prise,
            "flat_prise": billing_method_obj.flat_prise,
            "valley_prise": billing_method_obj.valley_prise,
        } if billing_method_obj else {}

        param = {
            "ele_user": item.number,
            "day": " tm > '{}'".format(date_list[0]),
            "billing": billing_method
        }
        data = get_6_month_power_data_and_money(**param)
        if kwargs.get("customer_id") in [80824, 33292]:
            return data
        if data:
            customer_data.append(data)
    result_power = {}
    money = {}
    for item in customer_data:
        for power in item.get("power", []):
            if power.get("type") not in result_power:
                result_power[power.get("type")] = {}
                result_power[power.get("type")][power.get("date")] = power.get("ept")
            else:
                if power.get("date") not in result_power[power.get("type")]:
                    result_power[power.get("type")][power.get("date")] = power.get("ept")
                else:
                    result_power[power.get("type")][power.get("date")] += power.get("ept")
        for k, v in item.get("money").items():
            if k not in money.keys():
                money[k] = v
            else:
                money[k] += v
    return {

        "power": result_power,
        "money": money

    }


def get_next_6_month():
    year = datetime.datetime.now().year
    month = datetime.datetime.now().month
    date_list = []
    for i in range(6):
        date_list.insert(0, "{}-{}".format(year, str(month).zfill(2)))
        month -= 1
        if month == 0:
            year -= 1
            month = 12
    return date_list


def get_10_day_power_data_and_money(**kwargs):
    try:
        monitor_conn = MonitorConnection()
        base_conn = BaseConnection()
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" and a.id=5467
                        """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """select ept,tm from mo_{} where point_id=5467 and {}
                                    """.format(kwargs.get("ele_user"),
                                               kwargs.get("day")
                                               )
            data = monitor_conn.query(sql)
            tm = []
            ep = []
            for item in data:
                tm.append(item.get("tm"))
                ep.append(float(item.get("ept", 0)))
            ep_raw = pd.Series(ep, index=tm)
            result = get_hour_ele(ep_raw)
            if result is None or result.empty:
                return {}
            rst = {}
            rst["time"] = result.index,
            rst["data"] = result.values
            power_data = power_handle(rst, float(ratio.get("vr")), float(ratio.get("cr")))
            result = {}
            result["power"] = power_data
            money = {}
            for k, v in power_data.items():
                for key, value in v.items():
                    if key not in money:
                        if k == "f":
                            money[key] = kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] = kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] = kwargs.get("billing").get("valley_prise", 0) * value
                    else:
                        if k == "f":
                            money[key] += kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] += kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] += kwargs.get("billing").get("valley_prise", 0) * value
            result["money"] = money
            return result
        elif kwargs.get("ele_user") == "0062633473":
            sql = """
                    select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                                                                """
            ratio = base_conn.query(sql)
            rst_data = []
            result_data = {}
            for rt in ratio:
                sql = """select ept,tm from mo_{} where point_id={} and {}
                                        """.format(kwargs.get("ele_user"),
                                                   rt.get("id"),
                                                   kwargs.get("day")
                                                   )
                data = monitor_conn.query(sql)
                tm = []
                ep = []
                for item in data:
                    tm.append(item.get("tm"))
                    ep.append(float(item.get("ept", 0)))
                ep_raw = pd.Series(ep, index=tm)
                result = get_hour_ele(ep_raw)
                if result is None or result.empty:
                    continue
                rst = {}
                rst["time"] = result.index,
                rst["data"] = result.values
                power_data = sh_power_handle(rst, float(rt.get("vr")), float(rt.get("cr")))
                rst_data.append(power_data)

            money = {}
            result_data["power"] = organize_uvf_data(rst_data)
            for k, v in result_data["power"].items():
                for key, value in v.items():
                    if key not in money:
                        if k == "f":
                            money[key] = kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] = kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] = kwargs.get("billing").get("valley_prise", 0) * value
                    else:
                        if k == "f":
                            money[key] += kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] += kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] += kwargs.get("billing").get("valley_prise", 0) * value
            result_data["money"] = money

            return result_data
        else:
            sql = """
                    select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                    left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)
            sql = """
            select ifnull(max(ept),0) ept,`date`,`type`,point_id from 
            ( select max(ept)-min(ept) ept,date_format(tm,"%Y-%m-%d") `date`,point_id,
            case 
                when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                when hour(tm) in (23,0,1,2,3,4,5) then "v"
                else "f"
                end `type`
             from mo_{} where {} group by point_id,`date`,`type` )A group by point_id,`date`,`type`
            """.format(kwargs.get("ele_user"), kwargs.get("day"))

            power_data = monitor_conn.query(sql)
            for item in power_data:
                for it in ratio:
                    if it.get("id") == item.get("point_id"):
                        item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
            sql = """
             SELECT ifnull(max(ept),0) ept,type,`date`,point_id from (
                select max(ept)-min(ept) ept,point_id,
                case 
                when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                when hour(tm) in (23,0,1,2,3,4,5) then "v"
                else "f"
                end `type`,
                date_format(tm,"%Y-%m-%d") `date` 
                from mo_{} where {} group by point_id,`type`,`date`
            )A GROUP BY point_id,type,`date`
            """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = monitor_conn.query(sql)
            for item in data:
                for it in ratio:
                    if it.get("id") == item.get("point_id"):
                        item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
        result = {}
        result["power"] = power_data
        money = {}
        for item in data:
            if item.get("date") not in money.keys():
                money[item.get("date")] = 0
            if item.get("type") == "f":
                money[item.get("date")] += kwargs.get("billing").get("peak_prise", 0) * item.get("ept", 0)
            elif item.get("type") == "u":
                money[item.get("date")] += kwargs.get("billing").get("flat_prise") * item.get("ept")
            elif item.get("type") == "v":
                money[item.get("date")] += kwargs.get("billing").get("valley_prise") * item.get("ept")

        result["money"] = money
        return result

    except Exception as e:
        LOG.error(e)
        try:
            oa_conn = MonitorOAConnection()
            sql = """
                    select ifnull(max(ept),0) ept,`date`,`type` from 
                    ( select max(ep)-min(ep) ept,
                    case 
            when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
            when hour(tm) in (23,0,1,2,3,4,5) then "v"
		    else "f"
            end `type`,
                    date_format(tm,"%Y-%m-%d") `date` from tb_{} where {} group by line,`date`,`type` )A group by `date`,`type`
                    """.format(kwargs.get("ele_user"), kwargs.get("day"))
            result = {}
            power_data = oa_conn.query(sql)
            sql = """
                     SELECT ifnull(max(ept),0) ept,type,`date` from (
                        select max(ep)-min(ep) ept,
                        case 
                        when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                        when hour(tm) in (23,0,1,2,3,4,5) then "v"
            		    else "f"
                        end `type`,
                        date_format(tm,"%Y-%m-%d") `date`
                        from tb_{} where {} group by line,`type`,`date`
                    )A GROUP BY type,`date`
                    """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_conn.query(sql)
            result["power"] = power_data
            money = {}
            for item in data:
                if item.get("date") not in money.keys():
                    money[item.get("date")] = 0
                if item.get("type") == "f":
                    money[item.get("date")] += kwargs.get("billing").get("peak_prise", 0) * item.get("ept", 0)
                elif item.get("type") == "u":
                    money[item.get("date")] += kwargs.get("billing").get("flat_prise", 0) * item.get("ept", 0)
                elif item.get("type") == "v":
                    money[item.get("date")] += kwargs.get("billing").get("valley_prise", 0) * item.get("ept", 0)

            result["money"] = money
            return result

        except Exception as e:
            LOG.error(e)
            return {
                "power": [],
                "money": {}
            }


def organize_uvf_data(data_list):
    rst = {}
    for item in data_list:
        for k, v in item.items():
            for key, value in v.items():
                if k not in rst:
                    rst[k] = {key: value}
                else:
                    if key not in rst[k]:
                        rst[k][key] = value
                    else:
                        rst[k][key] += value
    return rst


def get_6_month_power_data_and_money(**kwargs):
    try:
        monitor_conn = MonitorConnection()
        base_conn = BaseConnection()
        if kwargs.get("ele_user") == "0710000016696424":
            sql = """
                                    select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                                    left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                                    and a.id=5467
                                """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)[0]
            sql = """select ept,tm from mo_{} where point_id=5467 and {}
                                                """.format(kwargs.get("ele_user"),
                                                           kwargs.get("day")
                                                           )
            data = monitor_conn.query(sql)
            tm = []
            ep = []
            for item in data:
                tm.append(item.get("tm"))
                ep.append(float(item.get("ept", 0)))
            ep_raw = pd.Series(ep, index=tm)
            result = get_hour_ele(ep_raw)
            if result is None or result.empty:
                return {}
            rst = {}
            rst["time"] = result.index,
            rst["data"] = result.values
            power_data = month_power_handle(rst, float(ratio.get("vr")), float(ratio.get("cr")))
            result = {}
            result["power"] = power_data
            money = {}
            for k, v in power_data.items():
                for key, value in v.items():
                    if key not in money:
                        if k == "f":
                            money[key] = kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] = kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] = kwargs.get("billing").get("valley_prise", 0) * value
                    else:
                        if k == "f":
                            money[key] += kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] += kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] += kwargs.get("billing").get("valley_prise", 0) * value
            result["money"] = money
            return result
        elif kwargs.get("ele_user") == "0062633473":
            sql = """
                                select voltage_ratio vr,current_ratio cr, id from monitor_points where id in (2794,2797)
                                                                            """
            ratio = base_conn.query(sql)
            rst_data = []
            result_data = {}
            for rt in ratio:
                sql = """select ept,tm from mo_{} where point_id={} and {}
                                                    """.format(kwargs.get("ele_user"),
                                                               rt.get("id"),
                                                               kwargs.get("day")
                                                               )
                data = monitor_conn.query(sql)
                tm = []
                ep = []
                for item in data:
                    tm.append(item.get("tm"))
                    ep.append(float(item.get("ept", 0)))
                ep_raw = pd.Series(ep, index=tm)
                result = get_hour_ele(ep_raw)
                if result is None or result.empty:
                    continue
                rst = {}
                rst["time"] = result.index,
                rst["data"] = result.values
                power_data = sh_month_power_handle(rst, float(rt.get("vr")), float(rt.get("cr")))
                rst_data.append(power_data)

            result_data["power"] = organize_uvf_data(rst_data)
            money = {}
            for k, v in result_data["power"].items():
                for key, value in v.items():
                    if key not in money:
                        if k == "f":
                            money[key] = kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] = kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] = kwargs.get("billing").get("valley_prise", 0) * value
                    else:
                        if k == "f":
                            money[key] += kwargs.get("billing").get("peak_prise", 0) * value
                        elif k == "u":
                            money[key] += kwargs.get("billing").get("flat_prise", 0) * value
                        else:
                            money[key] += kwargs.get("billing").get("valley_prise", 0) * value
            result_data["money"] = money
            return result_data
        else:
            sql = """
                            select a.voltage_ratio vr,a.current_ratio cr,a.id from monitor_points a
                            left join electricity_user b on a.electricity_user_id=b.id where b.number="{}" 
                        """.format(kwargs.get("ele_user"))
            ratio = base_conn.query(sql)
            sql = """
                select ifnull(max(ept),0) ept,`date`,`type`,point_id from 
                ( select max(ept)-min(ept) ept,date_format(tm,"%Y-%m") `date`,point_id,
                case 
                    when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                    when hour(tm) in (23,0,1,2,3,4,5) then "v"
                    else "f"
                    end `type`
                from mo_{} where {} group by point_id,`date`,`type` )A group by point_id,`date`,`type` order by `date`
                """.format(kwargs.get("ele_user"), kwargs.get("day"))

            power_data = monitor_conn.query(sql)
            for item in power_data:
                for it in ratio:
                    if it.get("id") == item.get("point_id"):
                        item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
            sql = """
                 SELECT ifnull(max(ept),0) ept,type,`date`,point_id from (
                    select max(ept)-min(ept) ept,point_id,
                    case 
                    when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                    when hour(tm) in (23,0,1,2,3,4,5) then "v"
                    else "f"
                    end `type`,
                    date_format(tm,"%Y-%m") `date`
                    from mo_{} where {} group by point_id,`type`,`date`
                )A GROUP BY point_id,type,`date` order by `date`
                """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = monitor_conn.query(sql)
            for item in data:
                for it in ratio:
                    if it.get("id") == item.get("point_id"):
                        item["ept"] = round(item["ept"] * float(it.get("vr")) * float(it.get("cr")), 2)
        result = {}
        result["power"] = power_data
        money = {}
        for item in data:
            if item.get("date") not in money.keys():
                money[item.get("date")] = 0
            if item.get("type") == "f":
                money[item.get("date")] += kwargs.get("billing").get("peak_prise", 0) * item.get("ept")
            elif item.get("type") == "u":
                money[item.get("date")] += kwargs.get("billing").get("flat_prise") * item.get("ept")
            elif item.get("type") == "v":
                money[item.get("date")] += kwargs.get("billing").get("valley_prise") * item.get("ept")

        result["money"] = money
        return result

    except Exception as e:
        LOG.error(e)
        try:
            oa_conn = MonitorOAConnection()
            sql = """
                        select ifnull(max(ept),0) ept,`date`,`type` from 
                        ( select max(ep)-min(ep) ept,
                        case 
                when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                when hour(tm) in (23,0,1,2,3,4,5) then "v"
    		    else "f"
                end `type`,
                        date_format(tm,"%Y-%m") `date` from tb_{} where {} group by line,`date`,`type` )A group by `date`,`type`
                        """.format(kwargs.get("ele_user"), kwargs.get("day"))
            result = {}
            power_data = oa_conn.query(sql)
            sql = """
                         SELECT ifnull(max(ept),0) ept,type,`date` from (
                            select max(ep)-min(ep) ept,
                            case 
                            when hour(tm) in (11,12,13,14,15,16,17,18,19) then "u"
                            when hour(tm) in (23,0,1,2,3,4,5) then "v"
                		    else "f"
                            end `type`,
                            date_format(tm,"%Y-%m") `date`
                            from tb_{} where {} group by line,`type`,`date`
                        )A GROUP BY type,`date`
                        """.format(kwargs.get("ele_user"), kwargs.get("day"))
            data = oa_conn.query(sql)
            result["power"] = power_data
            money = {}
            for item in data:
                if item.get("date") not in money.keys():
                    money[item.get("date")] = 0
                if item.get("type") == "f":
                    money[item.get("date")] += kwargs.get("billing").get("peak_prise", 0) * item.get("ept", 0)
                elif item.get("type") == "u":
                    money[item.get("date")] += kwargs.get("billing").get("flat_prise", 0) * item.get("ept", 0)
                elif item.get("type") == "v":
                    money[item.get("date")] += kwargs.get("billing").get("valley_prise", 0) * item.get("ept", 0)

            result["money"] = money
            return result

        except Exception as e:
            LOG.error(e)
            return {}


def power_handle(rst, vr=1, cr=1):
    date_list = rst["time"][0]
    result = {
        "f": {},
        "v": {},
        "u": {}
    }
    for i in range(len(date_list)):
        hour = str(date_list[i])[11:13]
        date = str(date_list[i])[:10]

        if int(hour) in [10, 11, 16, 17, 18, 19, 20, 21]:
            if date not in result["f"]:
                result["f"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["f"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        elif int(hour) in [7, 8, 9, 12, 13, 14, 15, 22]:
            if date not in result["u"]:
                result["u"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["u"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        else:
            if date not in result["v"]:
                result["v"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["v"][date] += round(float(rst["data"][i]) * vr * cr, 2)
    return result


def sh_power_handle(rst, vr=1, cr=1):
    date_list = rst["time"][0]
    result = {
        "f": {},
        "v": {},
        "u": {}
    }
    for i in range(len(date_list)):
        hour = str(date_list[i])[11:13]
        date = str(date_list[i])[:10]

        if int(hour) in [8, 9, 10, 18, 19, 20]:
            if date not in result["f"]:
                result["f"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["f"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        elif int(hour) in [6, 7, 11, 12, 13, 14, 15, 16, 17, 21]:
            if date not in result["u"]:
                result["u"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["u"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        else:
            if date not in result["v"]:
                result["v"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["v"][date] += round(float(rst["data"][i]) * vr * cr, 2)
    return result


def month_power_handle(rst, vr=1, cr=1):
    date_list = rst["time"][0]
    result = {
        "f": {},
        "v": {},
        "u": {}
    }
    for i in range(len(date_list)):
        hour = str(date_list[i])[11:13]
        date = str(date_list[i])[:7]

        if int(hour) in [10, 11, 16, 17, 18, 19, 20, 21]:
            if date not in result["f"]:
                result["f"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["f"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        elif int(hour) in [7, 8, 9, 12, 13, 14, 15, 22]:
            if date not in result["u"]:
                result["u"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["u"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        else:
            if date not in result["v"]:
                result["v"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["v"][date] += round(float(rst["data"][i]) * vr * cr, 2)
    return result


def sh_month_power_handle(rst, vr=1, cr=1):
    date_list = rst["time"][0]
    result = {
        "f": {},
        "v": {},
        "u": {}
    }
    for i in range(len(date_list)):
        hour = str(date_list[i])[11:13]
        date = str(date_list[i])[:7]

        if int(hour) in [8, 9, 10, 18, 19, 20]:
            if date not in result["f"]:
                result["f"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["f"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        elif int(hour) in [6, 7, 11, 12, 13, 14, 15, 16, 17, 21]:
            if date not in result["u"]:
                result["u"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["u"][date] += round(float(rst["data"][i]) * vr * cr, 2)
        else:
            if date not in result["v"]:
                result["v"][date] = round(float(rst["data"][i]) * vr * cr, 2)
            else:
                result["v"][date] += round(float(rst["data"][i]) * vr * cr, 2)
    return result


def recent_power_handle(rst, vr=1, cr=1):
    date_list = rst["time"][0]
    for i in range(len(date_list)):
        rst["data"][i] = round(float(rst["data"][i]) * vr * cr, 2)
    return rst
