import logging
import datetime
import pandas as pd

from django.db.models import Q

from common.utils.models import MonitorOAConnection
from common.utils.models import BaseConnection
from common.utils.exceptions import ValidationError
from common.accounts.models import Users

from console.inspection.models import InspectionTask
from console.order.models import Order
from console.task.models import Task
from console.team.models import Team

from console.home_page.ep_fix import fix_ep

LOG = logging.getLogger()


def get_hour_ele(ep_raw):
    """
    通过输入电能的时间序列ep_raw，对电能数据进行修正后，计算每小时的用电量hour_ele并返回（仅计算电能值跨度为完整小时的用电量）。

    如果ep_raw存在以下两种情况，则返回值为None：
    1. ep_raw的有效数据点（即非空值点）小于2个，无法计算用电量。
    2. 负向电能值超过1000个。（负向电能值指后一个电能值比前一个电能值小）

    :param ep_raw: pd.Series
            原始电能数据时间序列。index为时间，data为电能值：
                index
                2020-09-04 23:35:24    8603.40
                2020-09-04 23:40:22    8612.40
                2020-09-04 23:45:20    8620.20
                2020-09-04 23:50:19    8628.00
                2020-09-04 23:55:17    8635.80
                ...
    :return: hour_ele: pd.Series
            每小时用电量数据。index为时间，data为用电量：
                index
                2020-09-04 19:00:00    109.80
                2020-09-04 20:00:00    106.20
                2020-09-04 21:00:00    104.40
                2020-09-04 22:00:00    105.00
                2020-09-04 23:00:00     96.00
                ...
    """

    if ep_raw.dropna().shape[0] < 2:  # 有效电能数据点少于2个，无法计算用电量，返回None（情况1）
        return
    else:
        ep = ep_raw.copy()
        ep = fix_ep(ep)  # 修正电能数据

        if ep is None:  # 如果存在负向电能过量的情况，无法计算用电量，返回None（情况2）
            return
        else:
            ep = ep.resample("5T").mean().interpolate("linear",
                                                      limit=12)  # 对1小时内的缺失值做插值
            ele = -ep.diff(
                periods=-1).dropna().resample("1H").sum()  # 计算每小时用电量

            ep_size = ep.dropna().resample("1H").size()  # 计算每小时有效数据点个数
            notna_idx = ep_size.loc[ep_size ==
                                    12].index  # 取有效数据点为12个（即每5分钟都有数据）的小时数
            hour_ele = ele.loc[notna_idx]
            return hour_ele


def get_station_ep(**data):
    # transformer = Transformer.objects.filter(station=station)
    # t_ids = [item.id for item in transformer]
    #
    # if not t_ids:
    #     return
    # if len(t_ids) < 2:
    #     t_ids = "(%s)" % t_ids[0]
    # else:
    #     t_ids = tuple(t_ids)
    # power_oa = PowerOAConnection()
    # sql = """
    # select no from im_device where transformer_id in %s
    # """ % t_ids
    #
    # lines = [data.get("no") for data in power_oa.query(sql)]
    # print(lines)
    monitor = MonitorOAConnection()
    # 获取当天电能数据
    type = data.get("type")
    week_start = (datetime.datetime.today() - datetime.timedelta(
        datetime.datetime.weekday(datetime.datetime.today()))).date()
    year = datetime.datetime.today().year
    month = datetime.datetime.today().month
    ele_number = data.get("station").electricity_user.number
    sql = ""
    sql_map = {
        "day":
            """
            select tm,ep from tb_{} where date(tm)='{}'
            """.format(ele_number,
                       datetime.datetime.today().date()),
        "week":
            """
            select tm,ep from tb_{} where tm>'{}'
            """.format(ele_number, week_start),
        "month":
            """
            select
             tm ,ep
            from tb_{}
            where year(tm) = {} and month(tm)={}
            """.format(ele_number, year, month),
        "year":
            """
            select
            sum(ep) as data, date_format(tm,"%Y-%m") as `time`
            from tb_{}
            where year(tm)={}
            group by `time`
            """.format(ele_number, year)
    }

    if data.get("start_time"):
        sql = """
        select tm,ep from tb_{} where tm>'{}'
        """.format(ele_number, data.get("start_time"))
    if data.get("end_time"):
        sql = """
                select tm,ep from tb_{} where tm<'{}'
                """.format(ele_number, data.get("end_time"))
    if data.get("start_time") and data.get("end_time"):
        sql = """
            select tm,ep from tb_{} where tm<'{}' and tm>'{}'
            """.format(ele_number, data.get("end_time"),
                       data.get("start_time"))
    if type:
        sql = sql_map.get(type)

    try:
        data = monitor.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 []
    if type == "day":
        rst = {}
        rst["time"] = result.index,
        rst["data"] = result.values
        result = rst
    else:
        day_result = result.resample("D").sum()
        result = {}
        result["time"] = day_result.index
        result["data"] = day_result.values

    return result


def get_monitor_data():
    monitor_db = MonitorOAConnection()
    sql = "select * from tb_%s where left(tm,10)='%s'" % (
        "0000725066", datetime.datetime.today().strftime("%Y-%m-%d"))
    data = monitor_db.query(sql)
    return data


def web_homepage_statistic(user, enterprise_ids):
    user = Users.objects.filter(account=user).first()
    team = Team.objects.filter(Q(team_headman=user) | Q(member=user),
                               deleted=False).first()
    task = Task.objects.filter(status="completed", customer__service_enterprise_id__in=enterprise_ids)
    task1 = Task.objects.filter(customer__service_enterprise_id__in=enterprise_ids)
    order_completed = Order.objects.filter(status="completed", task__in=task1)

    inspe_task = InspectionTask.objects.filter(status="completed", team__enterprise_id__in=enterprise_ids)
    if team and team.type in ["2", "3"]:
        order_completed = order_completed.filter(team=team)
        inspe_task = inspe_task.filter(team=team, team__enterprise_id__in=enterprise_ids)
    data = {}
    # 完成工单数
    order_data = {}
    order_data["total_count"] = order_completed.count()
    now = datetime.datetime.today().date()
    last_week_start = now - datetime.timedelta(now.weekday() + 7)
    last_week_end = now - datetime.timedelta(now.weekday() + 1)
    week_start = now - datetime.timedelta(now.weekday())
    y_day = now - datetime.timedelta(1)
    last_week_count = order_completed.filter(finish_time__gte=last_week_start,
                                             finish_time__lte=last_week_end +
                                                              datetime.timedelta(1)).count()
    this_week_count = order_completed.filter(
        finish_time__gte=week_start).count()
    order_data["week_compare"] = (this_week_count -
                                  last_week_count) / last_week_count \
        if last_week_count else this_week_count
    y_day_count = order_completed.filter(finish_time__gte=y_day,
                                         finish_time__lte=now).count()
    today_count = order_completed.filter(finish_time__gte=now).count()
    order_data["day_compare"] = (today_count - y_day_count) / y_day_count if \
        y_day_count else today_count
    # 完成任务数

    task_data = {}
    task_data["total_count"] = task.count()
    last_week_count = task.filter(finish_time__gte=last_week_start,
                                  finish_time__lte=last_week_end +
                                                   datetime.timedelta(1)).count()
    this_week_count = task.filter(finish_time__gte=week_start).count()
    task_data["week_compare"] = (this_week_count -
                                 last_week_count) / last_week_count if \
        last_week_count else this_week_count
    y_day_count = task.filter(finish_time__gte=y_day,
                              finish_time__lte=now).count()
    today_count = task.filter(finish_time__gte=now).count()
    task_data["day_compare"] = (today_count -
                                y_day_count) / y_day_count if \
        y_day_count else today_count
    # 完成巡检数

    inspe_data = {}
    inspe_data["total_count"] = inspe_task.count()
    last_week_count = inspe_task.filter(end_time__gte=last_week_start,
                                        end_time__lte=last_week_end +
                                                      datetime.timedelta(1)).count()
    this_week_count = inspe_task.filter(end_time__gte=week_start).count()
    inspe_data["week_compare"] = (this_week_count -
                                  last_week_count) / last_week_count if \
        last_week_count else this_week_count
    y_day_count = inspe_task.filter(end_time__gte=y_day,
                                    end_time__lte=now).count()
    today_count = inspe_task.filter(end_time__gte=now).count()
    inspe_data["day_compare"] = (today_count -
                                 y_day_count) / y_day_count if \
        y_day_count else today_count
    data["order_data"] = order_data
    data["task_data"] = task_data
    data["inspe_data"] = inspe_data
    return data


def order_status(start_time=None, end_time=None, type=None, enterprise_ids=[]):
    conn = BaseConnection()
    today = datetime.datetime.today().date()
    year = today.year
    month = today.month
    week_start = today - datetime.timedelta(today.weekday())
    sql = None
    # 柱状图数据
    sql_map = {
        "week":
            """
            select count(a.id) as count,
                date_format(a.finish_time,'%Y-%m-%d') as date
            from `order` a left join  task b on a.task_id=b.id
            left join customer c on b.customer_id=c.id
            where
                a.status='completed' and  a.finish_time 
            between  '{}' and '{}' and c.service_enterprise_id in ({})
            group by date
            """.format(week_start, today + datetime.timedelta(1), ",".join(enterprise_ids)),
        "month":
            """
            select count(a.id) as count,
            date_format(a.finish_time,'%Y-%m-%d') as date
            from `order` a left join  task b on a.task_id=b.id
            left join customer c on b.customer_id=c.id
            where a.status='completed' 
            and year(a.finish_time)={}
            and month(a.finish_time)={}
            and  c.service_enterprise_id in ({})
            group by date
            """.format(year, month, ",".join(enterprise_ids)),
        "year":
            """
                                 select
                                    count(a.id) as count,
                                    date_format(a.finish_time,'%m')as date
                                 from `order` a left join  task b on a.task_id=b.id
                                 left join customer c on b.customer_id=c.id
                                 where a.status='completed' and year(a.finish_time)={} and  c.service_enterprise_id in ({})
                                 group by date""".format(year, ",".join(enterprise_ids))
    }
    if start_time:
        sql = """select count(a.id) as count,date_format(a.finish_time,'%Y-%m-%d')
              as date from `order` a left join  task b on a.task_id=b.id
            left join customer c on b.customer_id=c.id
              where a.status='completed' 
            and a.finish_time > '{}' 
            and c.service_enterprise_id in ({})
            group by date""".format(start_time, ",".join(enterprise_ids))
    if end_time:
        sql = """
        select count(a.id) as count,date_format(a.finish_time,'%Y-%m-%d') as date
        from `order` a left join  task b on a.task_id=b.id
                                 left join customer c on b.customer_id=c.id
        where a.status='completed' and a.finish_time < '{}' and c.service_enterprise_id in ({})
        group by date""".format(end_time, ",".join(enterprise_ids))
    if start_time and end_time:
        sql = """
        select count(a.id) as count,date_format(a.finish_time,'%Y-%m-%d') as date
        from `order` a left join  task b on a.task_id=b.id
                                 left join customer c on b.customer_id=c.id
        where a.status='completed' and a.finish_time < '{}' and finish_time > '{}' and c.service_enterprise_id in ({})
        group by date""".format(end_time, start_time, ",".join(enterprise_ids))

    if type:
        sql = sql_map.get(type)

    if not sql:
        raise ValidationError("请传递查询条件")
    order_data = conn.query(sql) if sql else None
    # 排名数据
    sql_map = {
        "week":
            """
                        select count(a.id) as count,
                        b.id as id, b.name name
                        from `order` a
                        left join
                        team b on a.team_id=b.id
                        where a.status='completed' and a.finish_time
                        between '{}' and '{}' and b.enterprise_id in ({})
                        group by id
                        order by count desc """.format(
                week_start, today + datetime.timedelta(1), ",".join(enterprise_ids)),
        "month":
            """
            select count(a.id) as count,b.id as id, b.name name
            from `order` a
            left join
                team b on a.team_id=b.id
            where a.status='completed' and
                year(a.finish_time)= {} and month(a.finish_time)={} and b.enterprise_id in ({})
            group by id order by count desc
            """.format(year, month, ",".join(enterprise_ids)),
        "year":
            """
            select count(a.id) as count,b.id as id, b.name name
            from `order` a
            left join team b on a.team_id=b.id
            where a.status='completed'
            and year(a.finish_time)= {} and b.enterprise_id in ({})
            group by id
            order by count desc
            """.format(year, ",".join(enterprise_ids)),
    }
    sql = sql_map.get(type)
    if start_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `order` a
        left join team b on a.team_id=b.id
        where a.status='completed' and a.finish_time > '{}' and b.enterprise_id in ({})
        group by id
        order by count desc """.format(start_time, ",".join(enterprise_ids))
    if end_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `order` a
        left join team b on a.team_id=b.id
        where a.status='completed' and a.finish_time < '{}' and b.enterprise_id in ({})
        group by id
        order by count desc """.format(end_time, ",".join(enterprise_ids))
    if start_time and end_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `order` a
        left join team b on a.team_id=b.id
        where a.status='completed' and a.finish_time < '{}' 
        and a.finish_time> '{}' and b.enterprise_id in ({})
        group by id
        order by count desc """.format(end_time, start_time, ",".join(enterprise_ids))
    if not sql:
        raise ValueError("请传递查询条件")
    rank_data = conn.query(sql) if sql else None
    data = {}
    data["order_data"] = order_data
    data["rank_data"] = rank_data
    return data


def inspection_task_data(type=None, start_time=None, end_time=None, enterprise_ids=[]):
    conn = BaseConnection()
    today = datetime.datetime.today().date()
    year = today.year
    month = today.month
    week_start = today - datetime.timedelta(today.weekday())
    sql = None
    # 柱状图数据
    sql_map = {
        "week":
            """
            select
            count(a.id) as count,
            date_format(a.end_time,'%Y-%m-%d') as date
            from inspection_task a left join team b on a.team_id=b.id
            where a.status='completed'
            and a.end_time between '{}' and '{}' and b.enterprise_id in ({})
            group by date
            """.format(week_start, today + datetime.timedelta(1), ",".join(enterprise_ids)),
        "month":
            """
            select count(a.id) as count,
                date_format(a.end_time,'%Y-%m-%d') as date
            from `inspection_task` a left join team b on a.team_id=b.id
            where a.status='completed' and year(a.end_time)={}
                and month(a.end_time)={} and b.enterprise_id in ({})
            group by date
            """.format(year, month, ",".join(enterprise_ids)),
        "year":
            """
            select
                count(a.id) as count,
                date_format(a.end_time,'%m') as date
            from `inspection_task` a left join  team b on a.team_id=b.id
            where status='completed' and year(end_time)={} and b.enterprise_id in ({})
            group by date
            """.format(year, ",".join(enterprise_ids))
    }

    if start_time:
        sql = """
        select count(a.id) as count,date_format(a.end_time,'%Y-%m-%d') as date
        from `inspection_task` a left join team b on a.team_id=b.id 
        where a.status='completed' and a.end_time > '{}' and b.enterprise_id in ({})
        group by date""".format(start_time, ",".join(enterprise_ids))
    if end_time:
        sql = """
        select count(a.id) as count,date_format(a.end_time,'%Y-%m-%d') as date
        from `inspection_task` a left join team b on a.team_id=b.id
        where a.status='completed' and a.end_time < '{}' and b.enterprise_id in ({})
        group by date""".format(end_time, ",".join(enterprise_ids))
    if start_time and end_time:
        sql = """
        select count(a.id) as count,date_format(a.end_time,'%Y-%m-%d') as date
        from `inspection_task` a left join team b on a.team_id=b.id
        where a.status='completed' and a.end_time < '{}' and a.end_time > '{}'
        and b.enterprise_id in ({})
        group by date""".format(end_time, start_time, ",".join(enterprise_ids))

    if type:
        sql = sql_map.get(type)

    order_data = conn.query(sql) if sql else None
    # 排名数据
    sql_map = {
        "week":
            """
            select count(a.id) as count,b.id as id, b.name name
            from `inspection_task` a
            left join team b on a.team_id=b.id
            where a.status='completed' and a.end_time
            between '{}' and '{}'
            and b.enterprise_id in ({})
            group by id
            order by count desc
            """.format(week_start, today + datetime.timedelta(1), ",".join(enterprise_ids)),
        "month":
            """
            select count(a.id) as count,b.id as id, b.name name
            from `inspection_task` a
            left join team b on a.team_id=b.id
            where a.status='completed' and year(a.end_time)= {}
                and month(a.end_time)={}
                and b.enterprise_id in ({})
            group by id
            order by count desc
            """.format(year, month, ",".join(enterprise_ids)),
        "year":
            """select count(a.id) as count,b.id as id, b.name name
            from `inspection_task` a
            left join team b on a.team_id=b.id
            where a.status='completed' and year(a.end_time)= {}
            and b.enterprise_id in ({})
            group by id order by count desc
            """.format(year, ",".join(enterprise_ids)),
    }

    if start_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `inspection_task` a
        left join team b on a.team_id=b.id
        where a.status='completed' anda.end_time > '{}'
        and b.enterprise_id in ({})
        group by id
        order by count desc """.format(start_time, ",".join(enterprise_ids))
    if end_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `inspection_task` a
        left join team b on a.team_id=b.id
        where a.status='completed' and a.end_time < '{}'
        and b.enterprise_id in ({})
        group by id
        order by count desc """.format(end_time, ",".join(enterprise_ids))
    if start_time and end_time:
        sql = """
        select count(a.id) as count,b.id as id, b.name name
        from `inspection_task` a
        left join team b on a.team_id=b.id
        where a.status='completed' and a.end_time < '{}' and a.end_time> '{}'
        and b.enterprise_id in ({})
        group by id
        order by count desc """.format(end_time, start_time, ",".join(enterprise_ids))

    if type:
        sql = sql_map.get(type)
    rank_data = conn.query(sql) if sql else None
    data = {}
    data["inspection_task_data"] = order_data
    data["rank_data"] = rank_data
    return data


def temperature_humidity():
    monitor_db = MonitorOAConnection()
    sql = "select t,s from tb_{} order by tm desc limit 1".format("0000725066")
    data = monitor_db.query(sql)[0]
    data["equipment_status"] = 1
    data["t_status"] = 1 if data.get("t", 0) < 40 else 0
    data["s_status"] = 1 if data.get("s", 0) < 50 else 0
    return data
