import copy
import datetime
import json
import math

import ujson
import xlrd
import xlwt
from console.powerstation.models import PowerStation, Outline

from console.inspection.models import StationBuilding, SafetyEquipment
from django.db.models import Q, Sum

from common.utils.models import PowerOAConnection

from console.inspection.models import InspectionPlan, InspectionTask
from console.team.models import Team

from common.accounts.models import Enterprise, UserEnterprise

from console.APP.models import VersionUpdate

from common.utils.models import TestConnection

from console.inspection.models import (InspectionData, PowerInspectionData,
                                       InspectIn)

from common.utils.models import BaseConnection

from console.equipment.models import Equipment

from console.electricityuser.models import ElectricityUser

from console.electrical_info.models import ElectricalInfromation

from console.electricity_bill.models import ElectricityBill

from common.accounts.models import Roles, RolePerms

from common.accounts.models import Perms

from console.electricity_bill.models import TariffType

from console.customer.models import Customer

from console.powerstation.models import Transformer

from console.equipment.models import EquipmentData

from console.monitor.models import MonitorAlarm

from common.notify.message import MessageObject

from common.utils.models import MonitorConnection, MonitorOAConnection

from console.inspection.models import Defect

power_factor_adjust_map = {
    "0.9": {
        1: -0.75,
        0.99: -0.75,
        0.98: -0.75,
        0.97: -0.75,
        0.96: -0.75,
        0.95: -0.75,
        0.94: -0.60,
        0.93: -0.45,
        0.92: -0.30,
        0.91: -0.15,
        0.90: 0,
        0.89: 0.5,
        0.88: 1,
        0.87: 1.5,
        0.86: 2,
        0.85: 2.5,
        0.84: 3,
        0.83: 3.5,
        0.82: 4,
        0.81: 4.5,
        0.80: 5,
        0.79: 5.5,
        0.78: 6,
        0.77: 6.5,
        0.76: 7,
        0.75: 7.5
    },
    "0.90": {
        1: -0.75,
        0.99: -0.75,
        0.98: -0.75,
        0.97: -0.75,
        0.96: -0.75,
        0.95: -0.75,
        0.94: -0.60,
        0.93: -0.45,
        0.92: -0.30,
        0.91: -0.15,
        0.90: 0,
        0.89: 0.5,
        0.88: 1,
        0.87: 1.5,
        0.86: 2,
        0.85: 2.5,
        0.84: 3,
        0.83: 3.5,
        0.82: 4,
        0.81: 4.5,
        0.80: 5,
        0.79: 5.5,
        0.78: 6,
        0.77: 6.5,
        0.76: 7,
        0.75: 7.5
    },
    "0.85": {
        1: -1.1,
        0.99: -1.1,
        0.98: -1.1,
        0.97: -1.1,
        0.96: -1.1,
        0.95: -1.1,
        0.94: -1.1,
        0.93: -0.95,
        0.92: -0.8,
        0.91: -0.65,
        0.90: -0.5,
        0.89: -0.4,
        0.88: -0.3,
        0.87: -0.2,
        0.86: -0.1,
        0.85: 0,
        0.84: 0.5,
        0.83: 1,
        0.82: 1.5,
        0.81: 2,
        0.80: 2.5,
        0.79: 3,
        0.78: 3.5,
        0.77: 4,
        0.76: 4.5,
        0.75: 5
    },
    "0.8": {
        1: -1.3,
        0.99: -1.3,
        0.98: -1.3,
        0.97: -1.3,
        0.96: -1.3,
        0.95: -1.3,
        0.94: -1.3,
        0.93: -1.3,
        0.92: -1.3,
        0.91: -1.15,
        0.90: -1,
        0.89: -0.9,
        0.88: -0.8,
        0.87: -0.7,
        0.86: -0.6,
        0.85: -0.5,
        0.84: -0.4,
        0.83: -0.3,
        0.82: -0.2,
        0.81: -0.1,
        0.80: 0,
        0.79: 0.5,
        0.78: 1,
        0.77: 1.5,
        0.76: 2,
        0.75: 2.5
    },
    "0.80": {
        1: -1.3,
        0.99: -1.3,
        0.98: -1.3,
        0.97: -1.3,
        0.96: -1.3,
        0.95: -1.3,
        0.94: -1.3,
        0.93: -1.3,
        0.92: -1.3,
        0.91: -1.15,
        0.90: -1,
        0.89: -0.9,
        0.88: -0.8,
        0.87: -0.7,
        0.86: -0.6,
        0.85: -0.5,
        0.84: -0.4,
        0.83: -0.3,
        0.82: -0.2,
        0.81: -0.1,
        0.80: 0,
        0.79: 0.5,
        0.78: 1,
        0.77: 1.5,
        0.76: 2,
        0.75: 2.5
    }
}

prize_type_role = {
    "1112": ["【未】两部制工商业及其他用电", 0.558, 0.558, 0.558, 34.02, 22.68],
    "2011": ["【未】单一制工商业及其他用电", 0.711, 0.711, 0.711, 0, 0],
    "2041": ["【未】单一制工商业及其他用电", 0.711, 0.711, 0.711, 0, 0],
    "2042": ["【未】单一制工商业及其他用电", 0.69, 0.69, 0.69, 0, 0],
    "2102": ["【未】单一制 下水道动力用电-10KV-夏季", 0.701, 0.701, 0.701, 0, 0],
    "2421": ["【分】单一制工商业及其他用电", 0.825, 0.825, 0.396, 0, 0],
    "2422": ["【分】单一制工商业及其他用电", 0.801, 0.801, 0.371, 0, 0],
    "2521": ["【分】单一制工商业及其他用电", 0.825, 0.825, 0.396, 0, 0],
    "4031": ["【未】非居民学校养老院用电", 0.641, 0.641, 0.641, 0, 0],
    "4072": ["【未】单一制未分时居民合表用电", 0.612, 0.612, 0.612, 0, 0],
    "5092": ["【未】非居民学校养老院用电", 0.636, 0.636, 0.636, 0, 0],
    "5121": ["【未】非居民学校养老院用电", 0.641, 0.641, 0.641, 0, 0],
    "5122": ["【未】非居民学校养老院用电", 0.636, 0.636, 0.636, 0, 0],
    "8021": ["【分】两部制工商业及其他用电", 0.912, 0.562, 0.27, 34.02, 22.68],
    "8022": ["【分】两部制工商业及其他用电", 0.888, 0.538, 0.266, 34.02, 22.68],
    "8121": ["【分】两部制大工业用电", 1.06, 0.693, 0.303, 42, 28],
    "8122": ["【分】两部制大工业用电", 1.02, 0.674, 0.297, 42, 28],
    "8123": ["【分】两部制大工业用电", 1.013, 0.624, 0.294, 42, 28],
    "8321": ["【分】两部制工商业及其他用电", 0.912, 0.562, 0.27, 34.02, 22.68],
    "8322": ["【分】两部制工商业及其他用电", 0.888, 0.538, 0.266, 34.02, 22.68],
    "8323": ["【分】两部制工商业及其他用电", 0.864, 0.513, 0.261, 34.02, 22.68],
    "8421": ["【分】两部制工商业及其他用电", 0.912, 0.562, 0.27, 34.02, 2.68],
    "8422": ["【分】两部制工商业及其他用电", 0.888, 0.538, 0.266, 34.02, 22.68],
    "3322": ["【分】单一制 农业生产用电-10KV-夏季", 0.729, 0.729, 0.363, 0, 0]
}

prize_type_map = {

    "1112": ["【未】两部制 工商业及其他用电-10KV-夏季", 0.586, 0.586, 0.586, 34.02, 22.68],
    "2011": ["【未】单一制 工商业及其他用电-0.4KV-夏季", 0.738, 0.738, 0.738, 0, 0],
    "2041": ["【未】单一制 工商业及其他用电-0.4KV-夏季", 0.738, 0.738, 0.738, 0, 0],
    "2042": ["【未】单一制 工商业及其他用电-10KV-夏季", 0.719, 0.719, 0.719, 0, 0],
    "2102": ["【未】单一制 下水道动力用电-10KV-夏季", 0.701, 0.701, 0.701, 0, 0],
    "2421": ["【分】单一制 工商业及其他用电-0.4KV-夏季", 0.853, 0.853, 0.423, 0, 0],
    "2422": ["【分】单一制 工商业及其他用电-10KV-夏季", 0.828, 0.828, 0.399, 0, 0],
    "2521": ["【分】单一制 工商业及其他用电-0.4KV-夏季", 0.853, 0.853, 0.423, 0, 0],
    "8021": ["【分】两部制 工商业及其他用电-0.4KV-夏季", 0.94, 0.591, 0.218, 34.02, 22.68],
    "8022": ["【分】两部制 工商业及其他用电-10KV-夏季", 0.916, 0.567, 0.213, 34.02, 22.68],
    "8121": ["【分】两部制 大工业用电-0.4KV-夏季", 1.102, 0.735, 0.238, 42, 28],
    "8122": ["【分】两部制 大工业用电-10KV-夏季", 1.062, 0.716, 0.232, 42, 28],
    "8123": ["【分】两部制 大工业用电-35KV-夏季", 1.055, 0.666, 0.229, 42, 28],
    "8321": ["【分】两部制 工商业及其他用电-0.4KV-夏季", 0.94, 0.591, 0.218, 34.02, 22.68],
    "8322": ["【分】两部制 工商业及其他用电-10KV-夏季", 0.916, 0.567, 0.213, 34.02, 22.68],
    "8323": ["【分】两部制 工商业及其他用电-35KV-夏季", 0.891, 0.542, 0.208, 34.02, 22.68],
    "8421": ["【分】两部制 工商业及其他用电-0.4KV-夏季", 0.94, 0.591, 0.218, 34.02, 22.68],
    "8422": ["【分】两部制 工商业及其他用电-10KV-夏季", 0.916, 0.567, 0.213, 34.02, 22.68],
    "3322": ["【分】单一制 农业生产用电-10KV-夏季", 0.729, 0.729, 0.363, 0, 0]
}

level = {
    0: "",
    1: "0.4KV",
    2: "10KV",
    3: "35KV",
    4: "110KV",
    5: "220KV",
    6: "20KV"
}


def add_outline_data():
    """
    所有未添加出线侧的电源编号添加出线侧
    :return:
    """
    ele_infos = ElectricalInfromation.objects.filter(deleted=False)
    outline = []
    for item in ele_infos:
        print(item.id)
        outlint_count = Outline.objects.filter(power_number=item.id,
                                               deleted=False).count()
        if not outlint_count:
            outline.append(
                Outline(power_number=item.id,
                        name="{}-1".format(item.meter_number),
                        powerstation=item.powerstation))
    Outline.objects.bulk_create(outline)


def create_station_building():
    """
    创建电站站内建筑信息
    :return:
    """
    power_station = PowerStation.objects.filter(deleted=False)
    for station in power_station:
        building = StationBuilding.objects.filter(station=station).first()
        if not building:
            StationBuilding.objects.create(station=station,
                                           cable_conduit_status="无积水/完好",
                                           frame_status="完好",
                                           ground_status="完好",
                                           window_status="完好",
                                           house_status="完好",
                                           cable_holes_status="完好")
        equipment = SafetyEquipment.objects.filter(station=station).first()
        if not equipment:
            SafetyEquipment.objects.create(station=station,
                                           electroprobe_status="正常",
                                           es_check_date="2020-12-31",
                                           ground_wire="正常",
                                           gw_check_date="2020-12-31",
                                           insulating_mat="正常",
                                           im_check_date="2020-12-31",
                                           insulating_gloves="正常",
                                           ig_check_date="2020-12-31",
                                           insulating_shoes="正常",
                                           is_check_date="2020-12-31",
                                           extinguisher="完好",
                                           ex_check_date="2020-12-31")


def relation_station_and_team():
    """
    关联电站和服务班组
    :return:
    """
    oa_conn = PowerOAConnection()
    # 拿到所有的托管客户以及负责该托管客户的班组长
    sql = """
    select b.customer_id,a.nick from io_customer b left join oa_user a
    on a.id=b.inspector_id
    """
    data = oa_conn.query(sql)
    for item in data:
        # 找到班组长所在的班组
        team_id = None
        if item.get("nick"):
            team = Team.objects.filter(
                team_headman__nickname__icontains=item.get("nick")).first()
            team_id = team.id if team else None
        # 找到客户的电站
        # 添加电站的服务班组
        station = PowerStation.objects.filter(
            Q(customer_id=item.get("customer_id") + 5000)
            | Q(electricity_user_id=item.get("customer_id") + 5000))
        PowerStation.objects.filter(
            Q(customer_id=item.get("customer_id") + 5000)
            | Q(electricity_user_id=item.get("customer_id") + 5000)).update(
            **{
                "service_team": team_id,
                "inspector_ids": team_id
            })
        plan = InspectionPlan.objects.filter(station__in=station)
        InspectionTask.objects.filter(plan__in=plan).update(
            **{"team_id": team_id})


def assign_inspection_task():
    """
    分派巡检任务
    :return:
    """
    tasks = InspectionTask.objects.filter(team=None,
                                          work_date__gte="2021-02-01")
    for task in tasks:
        task.team_id = task.plan.station.service_team \
            if task.plan.station else None
        task.status = "pending"
        task.assign_date = datetime.datetime.now()
        task.save()


def create_permission(user_id):
    enterprise = Enterprise.objects.filter(id__lte=82)
    for item in enterprise:
        UserEnterprise.objects.create(user_id=user_id, enterprise=item)


def create_version_message(package_url=None, version=None):
    """
    创建版本信息
    :param package_url:
    :param version:
    :return:
    """
    teams = Team.objects.filter(deleted=False, type__in=[2, 3])
    users = [team.team_headman for team in teams]
    for team in teams:
        users += team.member.all()
    for user in list(set(users)):
        version_update = VersionUpdate.objects.filter(user=user).first()
        if not version_update:
            VersionUpdate.objects.create(user=user,
                                         version=version,
                                         package_url=package_url,
                                         is_updated=False)
        else:
            version_update.package_url = package_url
            version_update.is_updated = False
            version_update.version = version
            version_update.save()


def move_inspection_data():
    """
    迁移巡检数据----从测试环境迁移到线上环境
    :return:
    """
    test_conn = TestConnection()
    conn = BaseConnection()
    sql = """
    SELECT *
    from inspection_plan a
    left join inspection_task b
    on a.id =b.plan_id
    where b.team_id=1947
    and b.`status`="completed"
    and DATE_FORMAT(work_date,"%Y-%m")="2021-02"
    """
    datas = test_conn.query(sql)
    for data in datas:
        plan = InspectionPlan.objects.filter(plan_date__year=2021,
                                             plan_date__month=2,
                                             station_id=data.get("station_id"))
        # 迁移巡检任务
        task = InspectionTask.objects.filter(status="pending",
                                             plan__in=plan).first()
        task.status = "completed"
        task.end_time = data.get("end_time")
        task.start_time = data.get("start_time")
        task.save()
        print(task.id)
        # 迁移巡检记录
        sql = """
        select * from inspection_data where inspection_task_id={}
        """.format(data.get("id"))
        inspection_data = test_conn.query(sql)[0]
        inspection_data.pop("id")
        inspection_data["inspection_task_id"] = task.id
        InspectionData.objects.create(**inspection_data)
        # 迁移抄表数据
        sql = """
        SELECT * from power_inspection_data where inspection_task_id={}
        """.format(data.get("id"))
        power_inspection_data = test_conn.query(sql)
        for item in power_inspection_data:
            item_copy = copy.deepcopy(item)
            item_copy.pop("id")

            item_copy["inspection_task_id"] = task.id
            PowerInspectionData.objects.create(**item_copy)
        # 迁移进线数据
        sql = """
        SELECT * from inspection_in where inspect_task_id={}
        """.format(data.get("id"))
        spect_in = test_conn.query(sql)
        for item in spect_in:
            item_copy = copy.deepcopy(item)
            item_copy.pop("id")

            item_copy["inspect_task_id"] = task.id
            InspectIn.objects.create(**item_copy)
        # 迁移出线数据
        sql = """
        select * from inspection_out where inspect_task_id={}
        """.format(data.get("id"))
        spect_out = test_conn.query(sql)
        for item in spect_out:
            item_copy = copy.deepcopy(item)
            item_copy.pop("id")
            item_copy["inspect_task_id"] = task.id
            item_copy["created_time"] = str(item.get("created_time"))
            item_copy["updated_time"] = str(item.get("updated_time"))
            key = item_copy.keys()
            value = item_copy.values()
            sql = """
            insert into inspection_out {}
            """.format(tuple(key)).replace("'", "").replace(
                '"', "") + """values {}""".format(tuple(value))
            sql = sql.replace("None", "null")
            conn.query(sql)


def create_equipment():
    book = xlrd.open_workbook("D:/PycharmProject/industrial_internet_wss/"
                              "src/backend/console/APP/设备台账-去重修正终版.xlsx")
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows

    for i in range(1, rows):
        print(i)
        if i == 0:
            continue
        power_number = sheet.cell(i, 2).value
        if not isinstance(power_number, str):
            power_number = str(int(power_number))
        power_number = power_number.strip("（乙）").strip("（甲）").strip(
            "(乙)").strip("(甲)").strip()
        print(power_number)
        if len(power_number) < 10:
            power_number = power_number.zfill(10)
        print(power_number)
        ele_user = ElectricityUser.objects.filter(number=power_number).first()
        equipment_count = Equipment.objects.filter(
            electricity_user=ele_user).count()
        startDate = sheet.cell_value(i, 14)
        startDate = xlrd.xldate.xldate_as_datetime(startDate,
                                                   0) if startDate else None
        startDate = startDate.strftime(
            '%Y-%m-%d') if startDate else None  # 即可转换为日期格式
        power_station = PowerStation.objects.filter(
            electricity_user=ele_user).first()
        Equipment.objects.create(
            name=str(sheet.cell(i, 6).value)
            if sheet.cell(i, 6).value else None,
            type=sheet.cell(i, 5).value + ":" + sheet.cell(i, 8).value,
            factory=str(sheet.cell(i, 9).value)
            if sheet.cell(i, 9).value else None,
            model=str(sheet.cell(i, 10).value)
            if sheet.cell(i, 10).value else None,

            # price=sheet.cell(i, ).value,
            code=power_number + "-" + str(equipment_count).zfill(5),
            voltage=str(sheet.cell(i, 11).value)
            if sheet.cell(i, 11).value else None,
            electricity=str(sheet.cell(i, 12).value)
            if sheet.cell(i, 12).value else None,
            production_code=str(sheet.cell(i, 13).value)
            if sheet.cell(i, 13).value else None,
            production_date=startDate,
            customer=ele_user.customer if ele_user else None,
            electricity_user=ele_user,
            station=power_station,
            transformer_capacity=sheet.cell(i, 22).value,

            # file=sheet.cell(i, ).value,
            line_type=str(sheet.cell(i, 29).value)
            if sheet.cell(i, 29).value else None,
        )


def sync_transformer_capacity():
    # ele_user = ElectricityUser.objects.filter()
    # for item in ele_user:
    #     ele_info = ElectricalInfromation.objects.filter(electricity_user=item)
    #     real_data = ele_info.aggregate(Sum("real_capacity"))
    #     data = ele_info.aggregate(Sum("transformer_capacity"))
    #     item.transformer_capacity = data.get("transformer_capacity__sum")
    #     item.real_capacity = real_data.get("real_capacity")
    #     item.save()
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\长沙综保区层级关系（6.28）.xls")
    sheet = book.sheet_by_index(3)
    rows = sheet.nrows
    for i in range(1, rows):
        power_name = sheet.cell_value(i, 1).split("-")[0]
        if not power_name:
            continue
        power_station = PowerStation.objects.filter(name__icontains=power_name).first()
        # equipment_data = EquipmentData.objects.create(
        #     modular=1,
        #     type=1201,
        #     capacity=sheet.cell_value(i, 2),
        #     real_capacity=sheet.cell_value(i, 2)
        # )
        equip = Equipment.objects.filter(
            customer=power_station.customer,
            name=sheet.cell_value(i, 1).split("-")[1]
        ).first()
        equip.name = sheet.cell_value(i, 1)
        equip.save()


def export_ele_billing_data():
    book = xlrd.open_workbook(
        "C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\奉贤4月电费账单整理结果.xlsx")
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    for i in range(1, rows):
        print(i)
        ele_info = ElectricalInfromation.objects.filter(
            power_number=str(sheet.cell_value(i, 5)).strip()).first()
        if not ele_info:
            continue
        ele_bill = ElectricityBill.objects.filter(electrical_id=ele_info, year_month="2021-04").first()
        if not ele_bill:
            ele_bill = ElectricityBill.objects.create(
                electrical_id=ele_info,
                year_month="{}-{}".format(sheet.cell_value(i, 1)[:4], sheet.cell_value(i, 1)[4:]),
                peak_volume=sheet.cell_value(i, 13) if sheet.cell_value(i, 13) else 0,
                usual_volume=sheet.cell_value(i, 14) if sheet.cell_value(i, 14) else 0,
                valley_volume=sheet.cell_value(i, 15) if sheet.cell_value(i, 15) else 0,
                peak_price=sheet.cell_value(i, 23) if sheet.cell_value(i, 23) else 0,
                peak_price2=sheet.cell_value(i, 23) if sheet.cell_value(i, 23) else 0,
                usual_price=sheet.cell_value(i, 24) if sheet.cell_value(i, 24) else 0,
                usual_price2=sheet.cell_value(i, 24) if sheet.cell_value(i, 24) else 0,
                valley_price=sheet.cell_value(i, 25) if sheet.cell_value(i, 25) else 0,
                valley_price2=sheet.cell_value(i, 25) if sheet.cell_value(i, 25) else 0,
                levy_fee=sheet.cell_value(i, 18) if sheet.cell_value(i, 18) else 0,
                power_factor=ele_info.ep_factor,
                power_factor_real=sheet.cell_value(i, 7) if sheet.cell_value(i, 7) else 0,
                # power_factor_adjust=sheet.cell_value(i, 13) if sheet.cell_value(i, 13) else 0,
                report_md=sheet.cell_value(i, 28) if sheet.cell_value(i, 28) else 0,
                max_md=sheet.cell_value(i, 11) if sheet.cell_value(i, 11) else 0,
                basic_price=sheet.cell_value(i, 26) if sheet.cell_value(i, 26) else 0,
                capacity=ele_info.real_capacity,
                idle_volume=sheet.cell_value(i, 12) if sheet.cell_value(i, 12) else 0,
                amount=sheet.cell_value(i, 21) if sheet.cell_value(i, 21) else 0,
                amount_adjust=sheet.cell_value(i, 17) if sheet.cell_value(i, 17) else 0,
            )
        ele_bill.peak_volume = sheet.cell_value(i, 13) if sheet.cell_value(i, 13) else 0
        ele_bill.usual_volume = sheet.cell_value(i, 14) if sheet.cell_value(i, 14) else 0
        ele_bill.valley_volume = sheet.cell_value(i, 15) if sheet.cell_value(i, 15) else 0
        ele_bill.peak_price = sheet.cell_value(i, 23) if sheet.cell_value(i, 23) else 0
        ele_bill.peak_price2 = sheet.cell_value(i, 23) if sheet.cell_value(i, 23) else 0
        ele_bill.usual_price = sheet.cell_value(i, 24) if sheet.cell_value(i, 24) else 0
        ele_bill.usual_price2 = sheet.cell_value(i, 24) if sheet.cell_value(i, 24) else 0
        ele_bill.valley_price = sheet.cell_value(i, 25) if sheet.cell_value(i, 25) else 0
        ele_bill.valley_price2 = sheet.cell_value(i, 25) if sheet.cell_value(i, 25) else 0
        ele_bill.levy_fee = sheet.cell_value(i, 18) if sheet.cell_value(i, 18) else 0
        ele_bill.power_factor = ele_info.ep_factor
        ele_bill.power_factor_real = sheet.cell_value(i, 7) if sheet.cell_value(i, 7) else 0
        # power_factor_adjust=sheet.cell_value(i, 13) if sheet.cell_value(i, 13) else 0,
        ele_bill.report_md = sheet.cell_value(i, 28) if sheet.cell_value(i, 28) else 0
        ele_bill.max_md = sheet.cell_value(i, 11) if sheet.cell_value(i, 11) else 0
        ele_bill.basic_price = sheet.cell_value(i, 26) if sheet.cell_value(i, 26) else 0
        ele_bill.capacity = ele_info.real_capacity
        ele_bill.idle_volume = sheet.cell_value(i, 12) if sheet.cell_value(i, 12) else 0
        ele_bill.amount = sheet.cell_value(i, 21) if sheet.cell_value(i, 21) else 0
        ele_bill.amount_adjust = sheet.cell_value(i, 17) if sheet.cell_value(i, 17) else 0

        ele_bill.save()


def update_ele_bill_data():
    ele_bill = ElectricityBill.objects.filter(power_factor=None)
    for item in ele_bill:
        item.power_factor = item.electrical_id.ep_factor if item.electrical_id else None
        item.save()


def create_perms(role_id):
    role = Roles.objects.filter(id=role_id).first()
    if not role:
        return
    perms_ids = RolePerms.objects.filter(role_id=40).values_list("perms_id", flat=True)
    data = []
    for id in perms_ids:
        data.append(RolePerms(
            perms_id=id,
            role_id=role_id

        ))
    RolePerms.objects.bulk_create(data)


def create_perm_by_role(role_id, perm):
    perm = Perms.objects.filter(desc__icontains=perm)
    data = []
    role = Roles.objects.filter(id=role_id).first()
    if not role:
        return
    for i in perm:
        role_perm = RolePerms.objects.filter(role_id=role_id, perms=i).first()
        if role_perm:
            continue
        data.append(RolePerms(role_id=role_id,
                              perms=i))
    RolePerms.objects.bulk_create(data)


def export_ele_billing():
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\奉贤9月匹配数据20211009.xls")
    sheet = book.sheet_by_index(0)
    lows = sheet.nrows
    # record_list = []
    for i in range(304, lows):
        print(i)
        ele_info = ElectricalInfromation.objects.filter(power_number=sheet.cell_value(i, 5).strip()).first()
        # ele_user = ElectricityUser.objects.filter(number=sheet.cell_value(i, 2).strip()).first()
        type_name = prize_type_role.get(str(sheet.cell_value(i, 6)))
        type_prize = prize_type_map.get(str(sheet.cell_value(i, 6)))

        if not ele_info:
            continue
        # ElectricityBill.objects.filter(electrical_id=ele_info,
        #                                               year_month="{}-{}".format(sheet.cell_value(i, 1)[:4],
        #                                                                         sheet.cell_value(i, 1)[4:])).delete()
        # continue

        ele_bill_new = ElectricityBill.objects.filter(id__gt=82918, electrical_id=ele_info,
                                                      year_month="{}-{}".format(sheet.cell_value(i, 1)[:4],
                                                                                sheet.cell_value(i, 1)[4:])).first()
        power_factor_adjust = power_factor_adjust_map.get(ele_info.ep_factor).get(
            sheet.cell_value(i, 7)) if ele_info.ep_factor and power_factor_adjust_map.get(ele_info.ep_factor) else None
        base_prise = 0
        base_money = sheet.cell_value(i, 19) if sheet.cell_value(i, 19) else 0
        print(base_money / sheet.cell_value(i, 11) if sheet.cell_value(i, 11) else sheet.cell_value(i, 11),
              base_money / ele_info.real_capacity if ele_info.real_capacity else ele_info.real_capacity)
        report_md = None
        # 按最大需量计算基本电费
        if sheet.cell_value(i, 11) and round(base_money / sheet.cell_value(i, 11), 2) in [42.0, 34.02]:
            base_prise = round(base_money / sheet.cell_value(i, 11), 2)
            report_md = sheet.cell_value(i, 11)
        # 按申报md计算基本电费
        elif sheet.cell_value(i, 8) and round(base_money / sheet.cell_value(i, 8), 2) in [42.0, 34.02]:
            base_prise = round(base_money / sheet.cell_value(i, 8), 2)
            report_md = sheet.cell_value(i, 8)
        # 按容量计算基本电费
        elif ele_info.real_capacity and round(base_money / ele_info.real_capacity, 2) in [28.0, 22.68]:
            base_prise = round(base_money / ele_info.real_capacity, 2)
            report_md = ele_info.real_capacity
        if not base_prise and base_money:
            print("######## 基本电价计算出错 #########")
            # 已填报的数据，获取到重复电源编号的数据，有功电量和电费进行累加，无功电量取有有功电量的数据的值
        # 核准电度电费
        if sheet.cell_value(i, 13) and sheet.cell_value(i, 14) and sheet.cell_value(i, 15):
            power_money = type_name[1] * sheet.cell_value(i, 13) + type_name[2] * sheet.cell_value(i, 14) + type_name[
                3] * sheet.cell_value(i, 15)
            print(power_money, sheet.cell_value(i, 16))
            if math.fabs(power_money - sheet.cell_value(i, 16)) > 0.1:
                if not type_prize:
                    print("##########电度电费计算有问题****************")
                else:
                    power_money = type_prize[1] * sheet.cell_value(i, 13) + type_prize[2] * sheet.cell_value(i, 14) + \
                                  type_prize[
                                      3] * sheet.cell_value(i, 15)
                    if math.fabs(power_money - sheet.cell_value(i, 16)) <= 0.1:
                        type_name = type_prize
                    else:
                        print("##########电度电费计算有问题****************")

        # 导入的数据，重复电源编号的情况，电量累加
        if ele_bill_new:

            ele_bill_new.levy_fee += sheet.cell_value(i, 18) if sheet.cell_value(i, 18) else 0
            ele_bill_new.amount += sheet.cell_value(i, 21) if sheet.cell_value(i, 21) else 0
            ele_bill_new.amount_adjust += sheet.cell_value(i, 17) if sheet.cell_value(i, 17) else 0
            if base_prise:
                ele_bill_new.basic_price = base_prise
            if sheet.cell_value(i, 13):
                ele_bill_new.idle_volume = sheet.cell_value(i, 12)
            if power_factor_adjust:
                ele_bill_new.power_factor_adjust = power_factor_adjust
            if report_md:
                ele_bill_new.basic_volume = report_md

            if ele_bill_new.peak_price != type_name[1]:
                ele_bill_new.peak_price2 = type_name[1]
                ele_bill_new.usual_price2 = type_name[2]
                ele_bill_new.valley_price2 = type_name[3]
                ele_bill_new.peak_volume2 = float(sheet.cell_value(i, 13)) if sheet.cell_value(i, 13) else 0
                ele_bill_new.usual_volume2 = float(sheet.cell_value(i, 14)) if sheet.cell_value(i, 14) else 0
                ele_bill_new.valley_volume2 = float(sheet.cell_value(i, 15)) if sheet.cell_value(i, 15) else 0
            else:
                ele_bill_new.peak_volume += float(sheet.cell_value(i, 13)) if sheet.cell_value(i, 13) else 0
                ele_bill_new.usual_volume += float(sheet.cell_value(i, 14)) if sheet.cell_value(i, 14) else 0
                ele_bill_new.valley_volume += float(sheet.cell_value(i, 15)) if sheet.cell_value(i, 15) else 0
            ele_bill_new.save()
        else:
            print("***********新增******************")
            ElectricityBill.objects.create(
                electrical_id=ele_info,
                year_month="{}-{}".format(sheet.cell_value(i, 1)[:4], sheet.cell_value(i, 1)[4:]),
                peak_volume=sheet.cell_value(i, 13) if sheet.cell_value(i, 13) else 0,
                usual_volume=sheet.cell_value(i, 14) if sheet.cell_value(i, 14) else 0,

                valley_volume=sheet.cell_value(i, 15) if sheet.cell_value(i, 15) else 0,

                peak_price=type_name[1] if type_name else 0,

                usual_price=type_name[2] if type_name else 0,

                valley_price=type_name[3] if type_name else 0,
                peak_price2=type_name[1] if type_name else 0,

                usual_price2=type_name[2] if type_name else 0,

                valley_price2=type_name[3] if type_name else 0,

                levy_fee=sheet.cell_value(i, 18) if sheet.cell_value(i, 18) else 0,
                power_factor=ele_info.ep_factor,
                power_factor_real=sheet.cell_value(i, 7) if sheet.cell_value(i, 7) else 0,
                power_factor_adjust=power_factor_adjust_map.get(ele_info.ep_factor).get(
                    sheet.cell_value(i, 7)) if ele_info.ep_factor and power_factor_adjust_map.get(
                    ele_info.ep_factor) else None,
                basic_volume=report_md,
                max_md=sheet.cell_value(i, 11) if sheet.cell_value(i, 11) else 0,
                basic_price=base_prise,
                capacity=ele_info.real_capacity,
                idle_volume=sheet.cell_value(i, 12) if sheet.cell_value(i, 12) else 0,
                amount=sheet.cell_value(i, 21) if sheet.cell_value(i, 21) else 0,
                amount_adjust=sheet.cell_value(i, 17) if sheet.cell_value(i, 17) else 0,

            )


def change_inspection_task():
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\新建 XLS 工作表 (2).xls")
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    team = Team.objects.filter(name__icontains="吴伟组").first()
    for i in range(0, rows):
        print(i)
        number = sheet.cell_value(i, 0)
        if isinstance(number, float):
            number = str(number).strip(".0")
        number = number.strip("'")
        if len(number) < 10:
            number += "0" * (10 - len(number))
        print(number)
        ele_user = ElectricityUser.objects.filter(number=number).first()
        customer = Customer.objects.filter(name__icontains=sheet.cell_value(i, 2))
        if not ele_user and not customer:
            print("***************户号处理不对或不存在***********", number)
            continue
        power_station = PowerStation.objects.filter(Q(electricity_user=ele_user) | Q(customer__in=customer))
        plan = InspectionPlan.objects.filter(station__in=power_station, plan_date__year=2021, plan_date__month=6)
        power_station.update(service_team=team.id, inspector_ids=team.id)
        InspectionTask.objects.filter(plan__in=plan).update(team_id=team.id)


def change_inspection_task_2():
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\修改明细--刘朱杰+韩明+何思晓.xlsx")
    sheet = book.sheet_by_index(0)
    row = sheet.nrows
    for i in range(1, row):
        print(i)
        team = Team.objects.filter(name__icontains="{}组".format(sheet.cell_value(i, 7))).first()
        number = sheet.cell_value(i, 0)
        if isinstance(number, float):
            number = str(number).strip(".0")
        number = number.strip("'")
        if len(number) < 10:
            number += "0" * (10 - len(number))
        print(number)
        ele_user = ElectricityUser.objects.filter(number=number).first()
        customer = Customer.objects.filter(name__icontains=sheet.cell_value(i, 1))
        if not ele_user and not customer:
            print("***************户号处理不对或不存在***********", number)
            continue
        power_station = PowerStation.objects.filter(Q(electricity_user=ele_user) | Q(customer__in=customer))
        plan = InspectionPlan.objects.filter(station__in=power_station, plan_date__year=2021, plan_date__month=6)
        power_station.update(service_team=team.id if team else None, inspector_ids=team.id if team else None)
        InspectionTask.objects.filter(plan__in=plan).update(team_id=team.id if team else None)


def change_task():
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\工作簿1.xlsx")
    sheet = book.sheet_by_index(0)
    row = sheet.nrows
    for i in range(2, row):
        print(i)
        number = sheet.cell_value(i, 0)
        if isinstance(number, float):
            number = str(int(number))
        number = "0" * (10 - len(number)) + number
        print(number)
        ele_user = ElectricityUser.objects.filter(number=number).first()
        team = Team.objects.filter(name__icontains="{}组".format(sheet.cell_value(i, 7))).first()
        power_station = PowerStation.objects.filter(electricity_user=ele_user)
        power_station.update(service_team=team.id, inspector_ids=team.id)
        plan = InspectionPlan.objects.filter(station__in=power_station, plan_date__year=2021, plan_date__month=8)
        InspectionTask.objects.filter(plan__in=plan).update(team_id=team.id)


def power_station_add_customer_data():
    power_stations = PowerStation.objects.filter(customer=None)
    for item in power_stations:
        item.customer = item.electricity_user.customer
        item.save()


def inport_changsha_equipment():
    '''
    导入长沙国网的设备台账
    :return:
    '''
    EQ_MAP = {
        "变压器": 1,
        "高压开关柜": 0,
        "400V开关柜": 2,
        "辅助屏": 4,
        "400V电容器柜": 3,
        "线缆": 6
    }

    EQ_TYPE = {
        "干式变压器": 1201,
        "10kV高压线缆": 2201,
        "1#进线柜": 1001,
        "断路器": 4001,
        "保护装置": 6001,
        "直流屏": 20001,
        "电池屏": 22002,
        "监控模块": 20005,
        "充电模块": 21001,
        "400V开关柜": 1003,
        "电容屏": 20005,
        "双电源切换屏": 20007

    }

    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\标准配置.xlsx")
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    customer = Customer.objects.filter(name__contains="长沙综保投资有限公司").first()
    power_station = ElectricityUser.objects.filter(customer=customer)
    for station in power_station:
        print(station.id)
        for i in range(2, rows):
            print(i, "***********")
            date1 = str(sheet.cell_value(i, 14)).split(".") if sheet.cell_value(i, 14) else None
            date2 = str(sheet.cell_value(i, 15)).split(".") if sheet.cell_value(i, 15) else None
            equipment = EquipmentData.objects.create(
                modular=EQ_MAP.get(sheet.cell_value(i, 1)),
                type=EQ_TYPE.get(sheet.cell_value(i, 2)),
                manufacturer=sheet.cell_value(i, 8),
                model=sheet.cell_value(i, 9),
                production_date="{}-{}-01".format(date1[0], date1[1]) if date1 else None,
                operation_date="{}-{}-01".format(date2[0], date2[1]) if date2 else None,
                production_code=sheet.cell_value(i, 13),
                voltage=sheet.cell_value(i, 10),
                current=sheet.cell_value(i, 11),
                capacity=sheet.cell_value(i, 12),
            )
            Equipment.objects.create(
                name=sheet.cell_value(i, 2),
                customer=customer,
                equipment_data=equipment,
                electricity_user=station
            )


def test():
    conn = BaseConnection()
    sql = """
    
        SELECT
            
        A.id,
        A.name,
        D.address,
        concat('[', group_concat(DISTINCT
            concat('{','"id":',B.id,","),
            concat('"number":"',replace(IFNULL(B.number,""),'"','\\"'),'",'),
            concat('"addr":"',replace(IFNULL(B.addr,""),'"','\\"'),'",'),
            '"stations":[',
            concat(IF(C.id is null,'','{"id":'),
            IF(C.id is null,'',C.id),IF(C.id is null,'',",")),
            concat(IF(C.id is null,'','"name":"'),
            IF(C.id is null,'',replace(IFNULL(C.name,""),'"','\\"')),
            IF(C.id is null,'','"}')),']}'),']'
        ) as electricity_users
        
        FROM customer A
        left join enterprise D on A.enterprise_id=D.id
        INNER JOIN electricity_user B
        ON A.id=B.customer_id
        INNER JOIN power_station C
        ON C.electricity_user_id=B.id
         WHERE 1=1 and (A.service_enterprise_id in (1,6,49,52,54,1001,1002) or D.id in (1,6,49,52,54,1001,1002) ) GROUP BY A.id
     
    """
    datas = conn.query(sql)
    for item in datas:
        print(item.get("id"))
        ujson.loads(item.get("electricity_users"))


def send_monitor_msg():
    monitor_alamr = MonitorAlarm.objects.filter(id=3034)
    sms = MessageObject()
    for item in monitor_alamr:
        sms.send_sms_multi(
            item.monitor_point.phone_list.split(","), 960651,
            [item.monitor_point.customer.name, item.monitor_point.name, item.name,
             item.duration])


def get_fengxian_power():
    book = xlwt.Workbook(encoding="utf-8")
    sheet = book.add_sheet("sheet1")
    rows = 0
    sheet.write(rows, 0, "户名")
    sheet.write(rows, 1, "户号")
    sheet.write(rows, 2, "7月用电量")
    monitor_conn = MonitorConnection()
    oa_conn = MonitorOAConnection()
    customer = Customer.objects.filter(service_enterprise_id=6)
    ele_user = ElectricityUser.objects.filter(customer__in=customer)

    result = []
    for item in ele_user:
        rows += 1
        power = 0
        try:
            sql = """
            SELECT point_id from mo_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" GROUP BY point_id
            """.format(item.number)
            point_ids = monitor_conn.query(sql)
            for point in point_ids:
                sql = """
                select ept from mo_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" and point_id={} limit 0,1
                """.format(item.number, point.get("point_id"))
                power_data_start = monitor_conn.query(sql)
                sql = """
                select ept from mo_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" and point_id={} order by tm desc limit 0,1
                """.format(item.number, point.get("point_id"))
                power_data_end = monitor_conn.query(sql)
                power += power_data_end[0].get("ept") - power_data_start[0].get("ept")
        except Exception as e:
            print(e)
            try:
                sql = """
                            SELECT line from tb_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" GROUP BY line
                            """.format(item.number)
                point_ids = oa_conn.query(sql)
                for point in point_ids:
                    sql = """
                                select ep as ept from tb_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" and line={} limit 0,1
                                """.format(item.number, point.get("line"))
                    power_data_start = oa_conn.query(sql)
                    sql = """
                                select ep as ept from tb_{} where DATE_FORMAT(tm,"%Y-%m")="2021-07" and line={} order by tm desc limit 0,1
                                """.format(item.number, point.get("line"))
                    power_data_end = oa_conn.query(sql)
                    power += power_data_end[0].get("ept") - power_data_start[0].get("ept")
            except Exception as e:
                print(e)
                pass
        result.append({"customer": item.customer.name, "power_number": item.number, "power_data": power})
        sheet.write(rows, 0, item.customer.name)
        sheet.write(rows, 1, item.number)
        sheet.write(rows, 2, power)

    book.save("奉贤客户7月份用电量.xlsx")
    return result


def get_defect():
    book = xlwt.Workbook(encoding="utf-8")
    sheet = book.add_sheet("sheet1")
    defect = Defect.objects.filter(team__name__contains="吴正荣组", created_time__gte="2021-02-01")
    rows = 0
    sheet.write(rows, 0, "客户名称")
    sheet.write(rows, 1, "户号")
    sheet.write(rows, 2, "缺陷描述")
    sheet.write(rows, 3, "建议措施")
    sheet.write(rows, 4, "可能后果")
    sheet.write(rows, 5, "跟进情况")
    sheet.write(rows, 6, "缺陷上报时间")
    sheet.write(rows, 7, "处理状态")

    for item in defect:
        rows += 1
        sheet.write(rows, 0, item.customer.name)
        sheet.write(rows, 1, item.station.electricity_user.number)
        sheet.write(rows, 2, item.content)
        sheet.write(rows, 3, item.proposal)
        sheet.write(rows, 4, item.possible_result)
        sheet.write(rows, 5, item.follow_up)
        sheet.write(rows, 6, str(item.created_time))
        sheet.write(rows, 7, "未处理" if item.status == "pending" else "已处理")
    book.save("吴正荣组缺陷.xls")


def change_jinshan_task():
    book = xlrd.open_workbook("C:\Projects\AFA\industrial_internet_wss\src\\backend\console\APP\金山客户.xlsx")
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    for i in range(1, rows):
        Customer.objects.filter(name__icontains=sheet.cell_value(i, 1)).update(**{
            "service_enterprise_id": 70
        })
        PowerStation.objects.filter(electricity_user__number=sheet.cell_value(i, 0)).update(**{
            "service_enterprise_id": 70,
            "inspector_ids": 2509,
            "service_team": 2509
        })
