# -*- coding:utf-8 -*-
import datetime
import json
import time

import xlwt
from django.db.models import Q
from django.conf import settings
from notifications.signals import notify

from common.utils.models import BaseConnection
from common.forms.models import Form, FormAttribute
from common.accounts.models import Users, Accounts
from common.third_party_api.qucloud.clients import cos_client
from common.notify.message import client as message_client

from console.inspection.models import InspectionTask
from console.team.models import Team
from console.customer.models import Customer, CustomerAdmin
from console.inspection.models import InspectionPlan
from console.powerstation.models import PowerStation

from common.utils.models import PowerOAConnection

from console.electricityuser.models import ElectricityUser

from console.inspection.models import InspectionData, \
    PowerInspectionData, InspectIn, InspectOut
from console.powerstation.models import Outline

from console.powerstation.models import Transformer

from console.task.utils import get_serial_number

from console.electrical_info.models import ElectricalInfromation

from common.notify.message import MessageObject

from console.customer.models import Contact

file_path = settings.UPLOAD_FILE_PATH


def safe_quirpment_check(obj, dict_data):
    now = datetime.datetime.now().date()
    if "electroprobe_status" in dict_data:
        obj.electroprobe_status = dict_data.get("electroprobe_status")
        obj.es_check_date = now
    if "ground_wire" in dict_data:
        obj.ground_wire = dict_data.get("ground_wire")
        obj.gw_check_date = now
    if "insulating_mat" in dict_data:
        obj.insulating_mat = dict_data.get("insulating_mat")
        obj.im_check_date = now
    if "insulating_gloves" in dict_data:
        obj.insulating_gloves = dict_data.get("insulating_gloves")
        obj.ig_check_date = now
    if "insulating_shoes" in dict_data:
        obj.insulating_shoes = dict_data.get("insulating_shoes")
        obj.is_check_date = now
    if "extinguisher" in dict_data:
        obj.extinguisher = dict_data.get("extinguisher")
        obj.ex_check_date = now
    obj.save()


def get_data_item_info(station):
    conn = BaseConnection()
    sql = """
    select data_item from inspection_template where id={}
    """.format(station.inspection_template)
    data_item = conn.query(sql)
    data = json.loads(data_item[0].get("data_item"))
    sql = """
    select name from form where `table` in {}
    """.format(tuple(data))
    name = conn.query(sql)
    result = []
    for i in range(len(data)):
        item = {"item": data[i], "name": name[i].get("name")}
        result.append(item)
    return result


def get_data_item_attribute(table, inspection_task):
    form = Form.objects.filter(table=table).first()
    result = FormAttribute.objects.filter(form=form, data_type="float").values(
        "attribute", "data_type", "explain")
    conn = BaseConnection()
    sql = "select * from {} where inspection_task={}".format(
        table, inspection_task)
    data = conn.query(sql)
    data = {"attribute": result, "data": data[0] if data else None}
    return data


def data_item_commit(data):
    table = data.pop("table")
    keys = [key for key in data.keys()]
    values = [value for value in data.values()]
    conn = BaseConnection()
    sql = """
    insert into {} {} values {}
    """.format(table, tuple(keys), tuple(values))
    conn.query(sql.replace("'", "").replace('"', ""))
    inspection_task = InspectionTask.objects.filter(
        id=data.get("inspection_task")).first()
    inspection_task.status = "completed"
    inspection_task.save()


def spection_task_notify(**data):
    team = Team.objects.filter(
        id=data.get("team_id")).prefetch_related("member").first()
    user_ids = [team.team_headman.id] if team.team_headman else []
    user_ids += [item.id for item in team.member.all()]

    # account = Users.objects.filter(deleted=False,
    #                                id__in=user_ids).values_list("account",
    #                                                             flat=True)
    tasks = InspectionTask.objects.filter(id__in=data.get("task_list"))
    receive_list = []
    for task in tasks:
        # param = {
        #     "recipient":
        #         Accounts.objects.filter(id__in=account),
        #     "verb":
        #         "派发巡检任务",
        #     "action_object":
        #         task,
        #     "description":
        #         "{} {}给您分派了一个巡检任务 {}".format(
        #             data.get("user").username,
        #             datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        #             task.id),
        # }
        # notify.send(data.get("user"), **param)
        receive_ids = user_ids
        receive_list.extend(message_client.send_station_model(
            title="派发巡检任务",
            content= "{} {}给您分派了一个巡检任务 {}".format(
                data.get("user").username,
                datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                task.id),
            sender_id=1,
            receive_ids=receive_ids,
            type=3,
            enterprise_id=data.get("enterprise_id")))
    message_client.send_station_multi(receive_list)


def create_inspection_plan_and_task(leader, month=None):
    customer = Customer.objects.filter(id__in=leader)
    power_stations = PowerStation.objects.filter(
        Q(customer__in=customer) | Q(electricity_user__customer__in=customer))
    plan_date = datetime.datetime.today() if not month else \
        datetime.datetime.strptime(month + "-01", "%Y-%m-%d")
    month = plan_date.month
    day = plan_date.day
    year = plan_date.year
    # 如果超过25号，则生成下个月
    if day > 25:
        month += 1
    if month > 12:
        year += 1
        month = month % 12
    for station in power_stations:
        # 没有巡检次数的情况
        if not station.inspections_number:
            continue
        month_now = plan_date.month

        if month != month_now:
            plan_date = str(plan_date.year) + "-" + str(month) + "-" + "01"
            plan_date = datetime.datetime.strptime(plan_date, "%Y-%m-%d")
        # 判断一下该电站本月的巡检计划是否已经做完
        plan_num = InspectionPlan.objects.filter(
            station=station,
            plan_date__year=plan_date.year,
            plan_date__month=month).count()
        count = station.inspections_number - plan_num
        if count <= 0:
            continue
        for i in range(count):
            plan = InspectionPlan.objects.create(
                station=station,
                leader=station.electricity_user.customer.service_staff,
                plan_date=plan_date)
            InspectionTask.objects.create(
                plan=plan,
                work_date=plan_date,
                type="0",
                team_id=station.service_team,
                assign_date=datetime.datetime.now()
                if station.service_team else None,
                status="waiting_dispatch"
                if not station.service_team else "pending",
                name="IST" +
                     (datetime.datetime.now().date().strftime("%Y%m%d") +
                      str(get_serial_number()).zfill(5)))


def send_defect_to_customer(enterprise_id, defect):
    receive_ids = CustomerAdmin.objects.filter(customer=defect.customer).values_list(
        "user_id", flat=True)
    # account = Users.objects.filter(id__in=users).values_list("account",
    #                                                          flat=True)
    # param = {
    #     "recipient":
    #         Accounts.objects.filter(id__in=account),
    #     "verb":
    #         "缺陷通知",
    #     "action_object":
    #         defect,
    #     "description":
    #         "您的配电房:{} 有一个缺陷消息，缺陷内容如下：{}".format(defect.station.name, {
    #             "缺陷内容": defect.content,
    #             "建议措施": defect.proposal
    #         }),
    # }
    # notify.send(user, **param)
    message_client.send_station(
        title="缺陷通知",
        content= "您的配电房:{} 有一个缺陷消息，缺陷内容如下：{}".format(defect.station.name, {
            "缺陷内容": defect.content,
            "建议措施": defect.proposal
        }),
        sender_id=1,
        receive_ids=receive_ids,
        type=1,
        enterprise_id=enterprise_id)


def make_inspection_data_from_oa(date=None):
    oa_conn = PowerOAConnection()
    sql = """
    select
    a.id,b.customer_id,date_format(a.date,"%d") as date,
    a.temp,a.humi,a.inspector,
    a.comments,a.`year_month`,a.date as end_time
    from io_inspect a left join io_customer b on a.customer_id=b.id
    """
    if date:
        sql += " where `year_month` ='{}'".format(date)
    spect_records = oa_conn.query(sql)
    for item in spect_records:
        # 获取客户信息
        customer = Customer.objects.filter(id=item.get("customer_id") +
                                              5000).first()
        # 获取户号信息
        ele_user = ElectricityUser.objects.filter(customer=customer).first()
        # 获取电站信息
        power_station = PowerStation.objects.filter(
            Q(electricity_user=ele_user) | Q(customer=customer)).first()
        team_headman = item.get("inspector").split()[0]
        team = Team.objects.filter(team_headman__nickname=team_headman,
                                   deleted=False).first()
        plan = InspectionPlan.objects.create(
            station_id=ele_user.id if ele_user else None,
            plan_date=item.get("year_month") + "-" + item.get("date"),
            leader=customer.service_staff if customer else None)
        task = InspectionTask.objects.create(plan=plan,
                                             work_date=item.get("year_month") +
                                                       "-" + item.get("date"),
                                             team=team,
                                             status="completed",
                                             end_time=item.get("end_time"))
        InspectionData.objects.create(station=power_station,
                                      inspection_task=task,
                                      team=team,
                                      temperature=item.get("temp"),
                                      humidity=item.get("humi"),
                                      remarks=item.get("comments"),
                                      inspector=item.get("inspector"))
        sql = """
        select
        a.*,b.sn,b.meter_sn,b.voltage_level,b.override,b.power_factor
        from io_inspect_in a
        left join crm_source b on a.source_id=b.id
        where inspect_id={}
        """.format(item.get("id"))
        spect_ins = oa_conn.query(sql)
        for spect_in in spect_ins:
            ele_info = ElectricalInfromation.objects.filter(
                power_number=spect_in.get("sn")).first()
            PowerInspectionData.objects.create(
                inspection_task=task,
                power_number=ele_info.id if ele_info else None,
                meter_number=spect_in.get("meter_sn"),
                multiplying_power=spect_in.get("override"),
                power_factor=spect_in.get("power_factor"),
                voltage_level=spect_in.get("voltage_level"),
                total_active_power=spect_in.get("active_volume"),
                peak=spect_in.get("peak_volume"),
                flat_1=spect_in.get("usual1_volume"),
                flat_2=spect_in.get("usual2_volume"),
                valley=spect_in.get("valley_volume"),
                peak_md=spect_in.get("peak_md"),
                flat_1_md=spect_in.get("usual1_md"),
                flat_2_md=spect_in.get("usual2_md"),
                valley_md=spect_in.get("valley_md"),
                max_md=spect_in.get("max_md"),
                declare_md=spect_in.get("peak_volume"),
                real_power_factor=spect_in.get("power_factor_real"),
                reactive_power_1=spect_in.get("idle1_volume"),
                reactive_power_2=spect_in.get("idle2_volume"),
            )
            InspectIn.objects.create(
                inspect_task=task,
                power_number=ele_info.id if ele_info else None,
                v_ab=spect_in.get("v_ab"),
                v_bc=spect_in.get("v_bc"),
                v_ca=spect_in.get("v_ca"),
                monitor_a="亮" if spect_in.get("pd_a") else "无",
                monitor_b="亮" if spect_in.get("pd_b") else "无",
                monitor_c="亮" if spect_in.get("pd_c") else "无",
            )
        sql = """
        select
         a.*,b.sn
        from io_inspect_out a left join crm_source b on a.source_id=b.id
        where inspect_id={}
        """.format(item.get("id"))
        spect_outs = oa_conn.query(sql)
        i = 0
        outline = Outline.objects.filter(
            powerstation_id=ele_user.id) if ele_user else []
        # 出线侧数据不匹配
        if len(spect_outs) != len(outline):
            outline = []

        for spect_out in spect_outs:
            ele_info = ElectricalInfromation.objects.filter(
                power_number=spect_out.get("sn")).first()
            InspectOut.objects.create(
                inspect_task=task,
                power_number=ele_info.id if ele_info else None,
                power_station_id=ele_user.id if ele_user else None,
                outline=outline[i] if outline else None,
                o_ia=spect_out.get("o_ia"),
                o_ib=spect_out.get("o_ia"),
                o_ic=spect_out.get("o_ia"),
                monitor_a=spect_out.get("o_pda"),
                monitor_b=spect_out.get("o_pdb"),
                monitor_c=spect_out.get("o_pdc"),
                voice=spect_out.get("t_voice"),
                fan=spect_out.get("t_fan"),
                temperature=spect_out.get("t_temp"),
                oil_leak=spect_out.get("t_oil"),
                dry=spect_out.get("t_dry"),
                abnormal=spect_out.get("t_exception"),
                switch_v_ab=spect_out.get("s_vab"),
                switch_v_bc=spect_out.get("s_vbc"),
                switch_ia=spect_out.get("s_ia"),
                switch_ib=spect_out.get("s_ib"),
                switch_v_ca=spect_out.get("s_vca"),
                switch_ic=spect_out.get("s_ic"),
                GGJ=spect_out.get("ec_status"))
            i += 1


def move_transfer():
    conn = PowerOAConnection()
    sql = """
    select
     a.*,b.sn
    from crm_transformer a
    left join crm_source b on a.source_id=b.id
    """
    transfers = conn.query(sql)
    for item in transfers:
        Transformer.objects.create(number=item.get("no"),
                                   capacity=item.get("capacity"),
                                   real_capacity=item.get("real_capacity"),
                                   comments=item.get("comments"),
                                   ele_number=item.get("sn"))


def export_inspection_report(**data):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = 'SimSun'  # 指定“宋体”
    style.font = font
    book = xlwt.Workbook(encoding='utf-8')
    sheet = book.add_sheet("sheet1")
    row = 4
    conn = BaseConnection()
    date = datetime.datetime.now()
    year = date.year
    month = date.month
    SQL_MAP = {
        "day":
            """
                                select
                                a.id,a.work_date
                                from inspection_task a
                                left join inspection_plan b on a.plan_id=b.id
                                where b.station_id={} and a.status="completed"
                                and date_format(a.work_date,"%Y-%m-%d")={}
                                """.format(data.get("station_id"),
                                       date.strftime("%Y-%m-%d")),
        "month":
            """
                                select
                                a.id,a.work_date
                                from inspection_task a
                                left join inspection_plan b on a.plan_id=b.id
                                where b.station_id={} and a.status="completed"
                                and year(a.work_date)={}
                                and month(a.work_date)={}
                                """.format(data.get("station_id"), year, month),
        "year":
            """
                                select
                                a.id,a.work_date
                                from inspection_task a
                                left join inspection_plan b on a.plan_id=b.id
                                where b.station_id={} and a.status="completed"
                                and year(a.work_date)={}
                                """.format(data.get("station_id"), year)
    }
    sql = SQL_MAP.get(data.get("type"))
    if data.get("start_time"):
        sql = """
        select
            a.id,a.work_date
        from inspection_task a
        left join inspection_plan b on a.plan_id=b.id
        where b.station_id={} and a.status="completed"
        and a.work_date>'{}'
        """.format(data.get("station_id"), data.get("start_time"))
    if data.get("end_time"):
        sql = """
        select
            a.id,a.work_date
        from inspection_task a
        left join inspection_plan b on a.plan_id=b.id
        where b.station_id={} and a.status="completed"
        and a.work_date<'{}'
        """.format(data.get("station_id"), data.get("end_time"))
    if data.get("start_time") and data.get("end_time"):
        sql = """
        select
        a.id,a.work_date
        from inspection_task a
        left join inspection_plan b on a.plan_id=b.id
        where b.station_id={} and a.status="completed"
        and a.work_date<'{}' and a.work_date>'{}'
        """.format(data.get("station_id"), data.get("end_time"),
                   data.get("start_time"))
    if data.get("year_month"):
        sql = """
        select
        a.id,a.work_date
        from inspection_task a
        left join inspection_plan b on a.plan_id=b.id
        where b.station_id={} and a.status="completed"
        and left(a.work_date,7)='{}'
        """.format(data.get("station_id"), data.get("year_month"))
    sheet.write_merge(1, 3, 0, 0, "巡检时间", style)
    sheet.write_merge(1, 3, 1, 1, "巡检员", style)
    sheet.write_merge(1, 3, 2, 2, "直流电池电压", style)
    sheet.write_merge(1, 3, 3, 3, "直流母控电压", style)
    completed_tasks = conn.query(sql)
    in_merge_num = 0
    out_merge_num = 0
    for task in completed_tasks:
        column = 0
        sql = """
        select * from inspection_data where inspection_task_id={}
        """.format(task.get("id"))
        indpection_data = conn.query(sql)[0]
        sheet.write(row, column, str(task.get("work_date", "")))
        column += 1
        sheet.write(row, column, indpection_data["inspector"], style)
        column += 1
        sheet.write(row, column, indpection_data.get("battery_voltage"))
        column += 1
        sheet.write(row, column, indpection_data.get("direct_voltage"))
        column += 1
        sql = """
        select * from inspection_in where inspect_task_id={}
        """.format(task.get("id"))
        spection_ins = conn.query(sql)
        in_merge_num = len(spection_ins)
        # 进线侧数据
        for in_obj in spection_ins:
            sheet.write(row, column, in_obj.get("v_ab"))

            column += 1
            sheet.write(row, column, in_obj.get("v_bc"))

            column += 1
            sheet.write(row, column, in_obj.get("v_ca"))

            column += 1
            sheet.write(row, column, in_obj.get("i_a"))

            column += 1
            sheet.write(row, column, in_obj.get("i_b"))

            column += 1
            sheet.write(row, column, in_obj.get("i_c"))

            column += 1
        sql = """
        select * from inspection_out where inspect_task_id={}
        """.format(task.get("id"))
        inspection_outs = conn.query(sql)
        out_merge_num = len(inspection_outs)
        # 出线侧数据
        for out_obj in inspection_outs:
            sheet.write(row, column, out_obj.get("switch_v_ab"))

            column += 1
            sheet.write(row, column, out_obj.get("switch_v_bc"))

            column += 1
            sheet.write(row, column, out_obj.get("switch_v_ca"))

            column += 1
            sheet.write(row, column, out_obj.get("switch_ia"))

            column += 1
            sheet.write(row, column, out_obj.get("switch_ib"))

            column += 1
            sheet.write(row, column, out_obj.get("switch_ic"))

            column += 1
            sheet.write(row, column, out_obj.get("temperature_a"))

            column += 1
            sheet.write(row, column, out_obj.get("temperature_b"))

            column += 1
            sheet.write(row, column, out_obj.get("temperature_c"))

            column += 1
        row += 1
    # 总列数
    cell_num = in_merge_num * 6 + out_merge_num * 9 + 4
    # 合并表名单元格
    sheet.write_merge(0, 0, 0, cell_num - 1,
                      "{}月度巡检报告".format(data.get("station_name")), style)
    # 合并进线表头单元格
    for i in range(in_merge_num):
        sheet.write_merge(1, 1, i * 6 + 4, (i + 1) * 6 + 3, "进线%s" % (i + 1),
                          style)
        sheet.write_merge(2, 2, i * 6 + 4, i * 6 + 6, "电压", style)
        sheet.write_merge(2, 2, i * 6 + 7, i * 6 + 9, "电流", style)
        sheet.write(3, i * 6 + 4, "v_ab")
        sheet.write(3, i * 6 + 5, "v_bc")
        sheet.write(3, i * 6 + 6, "v_ca")
        sheet.write(3, i * 6 + 7, "i_a")
        sheet.write(3, i * 6 + 8, "i_b")
        sheet.write(3, i * 6 + 9, "i_c")
    # 合并出线表头单元格
    for i in range(out_merge_num):
        sheet.write_merge(1, 1, in_merge_num * 6 + 4 + i * 9,
                          in_merge_num * 6 + 12 + i * 9, "出线%s" % (i + 1),
                          style)
        sheet.write_merge(2, 2, in_merge_num * 6 + 4 + i * 9,
                          in_merge_num * 6 + 6 + i * 9, "电压", style)
        sheet.write_merge(2, 2, in_merge_num * 6 + 7 + i * 9,
                          in_merge_num * 6 + 9 + i * 9, "电流", style)
        sheet.write_merge(2, 2, in_merge_num * 6 + 10 + i * 9,
                          in_merge_num * 6 + 12 + i * 9, "温度", style)
        sheet.write(3, in_merge_num * 6 + 4 + i * 9, "v_ab")
        sheet.write(3, in_merge_num * 6 + 5 + i * 9, "v_bc")
        sheet.write(3, in_merge_num * 6 + 6 + i * 9, "v_ca")
        sheet.write(3, in_merge_num * 6 + 7 + i * 9, "i_a")
        sheet.write(3, in_merge_num * 6 + 8 + i * 9, "i_b")
        sheet.write(3, in_merge_num * 6 + 9 + i * 9, "i_c")
        sheet.write(3, in_merge_num * 6 + 10 + i * 9, "t_ab")
        sheet.write(3, in_merge_num * 6 + 11 + i * 9, "t_bc")
        sheet.write(3, in_merge_num * 6 + 12 + i * 9, "t_ca")
    file_name = file_path + "/" + str(time.time() * 1000) + ".xls"
    book.save(file_name)
    bucket = settings.CLOUD_COS.UPLOAD_BUCKET
    key = str(time.time() * 1000) + ".xls"
    with open(file_name, "rb") as f:
        cos_client.put_object(Bucket=bucket,
                              Body=f.read(),
                              Key=key,
                              EnableMD5=False)
    url = cos_client._conf.uri(bucket=bucket, path=key)
    return url


def inspection_task_filter(queryset, user):
    # 公司管理层，获取全部数据
    if user.account.account_type == "manager":
        return queryset
    team = Team.objects.filter(Q(team_headman=user) | Q(member=user)).first()
    # 调度组成员，获取全部数据
    if team and team.type == "0":
        return queryset
    # 巡检、抢修组员
    if team and team.type != "0":
        queryset = queryset.filter(team=team)
        return queryset
    queryset = queryset.filter(
        Q(plan__station__customer__service_staff=user)
        | Q(plan__station__electricity_user__customer__service_staff=user))
    return queryset


def send_message_to_customer(customer_ids):
    sms = MessageObject()
    customers = Customer.objects.filter(id__in=customer_ids)
    for customer in customers:
        # phone_list = Contact.objects.filter(
        #     customer=customer).values("phone")
        sms.send_sms_single(["17343184154", "18601179258"], "859652",
                            customer.name)


def create_inspection_task_by_team(team_id):
    power_station = PowerStation.objects.filter(service_team=team_id)
    now = datetime.datetime.now()
    year = now.year
    month = now.month
    for station in power_station:
        plan = InspectionPlan.objects.filter(station=station,
                                             plan_date__year=year,
                                             plan_date__month=month)
        if station.inspections_number - plan.count() <= 0:
            continue
        # 剩余巡检计划数量
        number = station.inspections_number - plan.count()
        for i in range(number):
            plan = InspectionPlan.objects.create(
                station=station,
                plan_date=datetime.datetime.today(),
                leader=station.electricity_user.customer.service_staff
                if station.electricity_user
                   and station.electricity_user.customer else None)
            InspectionTask.objects.create(
                plan=plan,
                name="IST-" +
                     (datetime.datetime.now().date().strftime("%Y%m%d") +
                      str(get_serial_number()).zfill(5)),
                status="pending",
                work_date=plan.plan_date,
                team_id=station.service_team,
                assign_date=datetime.datetime.now()
                if station.service_team else None)
