import json
import re
import os
import ujson
import datetime
import time
import random
import demjson
import pickle
import hashlib
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed

from dateutil.relativedelta import relativedelta
from django.db.models import Sum
from django.db.utils import ProgrammingError
from etc.celery import app
from django.conf import settings

from common.utils.models import BaseConnection, PowerOAConnection
from common.utils.redis_api import redis_api_base
from common.notify.message import client as message_client

from console.inspection.models import InspectionPlan, InspectionTask
from console.monitor.models import MonitorMetaData, MonitorRealData
from console.team.models import Team
from console.monitor.models import MonitorAlarm, MonitorDevices, MonitorMetaDataForTest

# 按日期分割celery日志
from console.powerstation.models import PowerStation
from console.task.utils import get_serial_number

from console.electrical_info.models import ElectricalInfromation
from console.electricityuser.models import ElectricityUser
from common.utils.redis_api import dashboard_redis_api

from console.APP.models import UserLocation

base_db = BaseConnection()
mo_db = BaseConnection(db_alias="monitor")
power_db = PowerOAConnection()
create_monitor_tb = """
CREATE TABLE `%s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) NOT NULL,
  `created_time` datetime(6) DEFAULT NULL,
  `updated_time` datetime(6) DEFAULT NULL,
  `deleted_time` datetime(6) DEFAULT NULL,
  `point_id` int(11) DEFAULT NULL,
  `ua` double DEFAULT NULL,
  `ub` double DEFAULT NULL,
  `uc` double DEFAULT NULL,
  `uab` double DEFAULT NULL,
  `ubc` double DEFAULT NULL,
  `uca` double DEFAULT NULL,
  `ia` double DEFAULT NULL,
  `ib` double DEFAULT NULL,
  `ic` double DEFAULT NULL,
  `ir` double DEFAULT NULL,
  `pa` double DEFAULT NULL,
  `pb` double DEFAULT NULL,
  `pc` double DEFAULT NULL,
  `p` double DEFAULT NULL,
  `qa` double DEFAULT NULL,
  `qb` double DEFAULT NULL,
  `qc` double DEFAULT NULL,
  `q` double DEFAULT NULL,
  `sa` double DEFAULT NULL,
  `sb` double DEFAULT NULL,
  `sc` double DEFAULT NULL,
  `s` double DEFAULT NULL,
  `pfa` double DEFAULT NULL,
  `pfb` double DEFAULT NULL,
  `pfc` double DEFAULT NULL,
  `pf` double DEFAULT NULL,
  `f` double DEFAULT NULL,
  `ept` double DEFAULT NULL,
  `eqi` double DEFAULT NULL,
  `eqe` double DEFAULT NULL,
  `p_d` double DEFAULT NULL,
  `lvur` double DEFAULT NULL,
  `iur` double DEFAULT NULL,
  `ua_thd` double DEFAULT NULL,
  `ub_thd` double DEFAULT NULL,
  `uc_thd` double DEFAULT NULL,
  `ia_thd` double DEFAULT NULL,
  `ib_thd` double DEFAULT NULL,
  `ic_thd` double DEFAULT NULL,
  `t1` double DEFAULT NULL,
  `t2` double DEFAULT NULL,
  `t3` double DEFAULT NULL,
  `t4` double DEFAULT NULL,
  `segment1` double DEFAULT NULL,
  `segment2` double DEFAULT NULL,
  `segment3` double DEFAULT NULL,
  `segment4` double DEFAULT NULL,
  `segment5` varchar(32) DEFAULT NULL,
  `segment6` varchar(32) DEFAULT NULL,
  `tm` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
DATE_LIST = [
    "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12",
    "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24",
    "25"
]


@app.task(bind=True)
def shard_log(_):
    log_path = settings.LOG_PATH
    log_file = os.path.join(log_path, 'celery.log')
    day = datetime.date.today() - relativedelta(days=+1)
    new_file = os.path.join(log_path, "celery-%s.log" % day)
    if not os.path.exists(log_file):
        try:
            os.mknod(log_file)
        except Exception:
            pass
    if not os.path.exists(new_file):
        os.rename(log_file, new_file)
        try:
            os.mknod(log_file)
        except Exception:
            pass


@app.task()
def create_plan():
    now = datetime.datetime.now()
    year = now.year
    month = now.month
    day = now.day
    # 如果超过25号，则生成下个月
    if day > 25:
        month += 1
    if month > 12:
        year += 1
        month = month % 12

    end_date = str(year) + "-" + str(month) + "-01"
    stations = PowerStation.objects.filter(
        end_time__gte=end_date,
        start_time__lte=datetime.datetime.now(),
        inspection_type=0,
        deleted=False,
        is_stop=False
    ).prefetch_related(
        "customer", "electricity_user", "customer__service_staff",
        "electricity_user__customer",
        "electricity_user__customer__service_staff")
    # 拿到所有电站信息
    for station in stations:
        # 拿到电站本月巡检次数
        sql = """
                select
                 count(id) as count
                from inspection_plan
                where
                 year(plan_date)={} and month(plan_date)={}
                and station_id={}""".format(year, month, station.id)
        planed_count = base_db.query(sql)
        # 剩余待生成巡检任务次数
        multiple_number = (station.inspections_number -
                           planed_count[0].get("count"))
        # 生成巡检计划和巡检任务
        for i in range(multiple_number):
            # 巡检计划和任务随机分配到1-25号
            day = random.choices(DATE_LIST)[0]
            plan_date = str(year) + "-" + str(month).zfill(2) + "-{}".format(
                day)
            plan = InspectionPlan.objects.create(
                station=station,
                plan_date=plan_date,
                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="waiting_dispatch"
                if not station.service_team else "pending",
                work_date=plan.plan_date,
                team_id=station.service_team,
                assign_date=datetime.datetime.now()
                if station.service_team else None)
        plan = InspectionPlan.objects.filter(station=station, plan_date__year=year, plan_date__month=month)
        inspection_task = InspectionTask.objects.filter(plan__in=plan)
        inspection_task.update(
            team_id=station.service_team)
        for item in inspection_task:
            item.status = "waiting_dispatch" if not station.service_team else "pending" \
                if item.status != "completed" else "completed"
            item.save()


def check_plan_date(date, conn, leader):
    # 获取计划日期当天已存在的巡检计划数量
    sql = "select count(id) as count from inspection_plan" + \
          " where plan_date='{}' and leader_id={}".format(
              date, leader)
    data = conn.query(sql)
    # 判断计划日期巡检计划是否已达到最大值
    if data[0].get("count") < 20:
        return date
    # 计划日期已达到最大值，计划日期往后延一天
    date = date + datetime.timedelta(1)
    # 如果已经往后延到下个月，则从1号开始
    month_now = datetime.datetime.now().month + 1

    month_plan = date.month
    if month_now != month_plan:
        year, month = datetime.datetime.now().year, datetime.datetime.now(
        ).month
        date_str = "{}-{}-01".format(year, month)
        date = datetime.datetime.strptime(date_str, "%Y-%m-%d")
    check_plan_date(date, conn, leader)
    return date


@app.task()
def del_timeout_user_location():
    redis = redis_api_base.connect()
    redis = redis.redis
    data = redis.hgetall("user_location")
    for k, v in data.items():
        if time.time() - float(demjson.decode(v).get("time")) > 10:
            redis.hdel("user_location", k)


def save_user_location():
    redis = redis_api_base.connect()
    redis = redis.redis
    data = redis.hgetall("user_histoory_location")
    for k, v in data.items():
        UserLocation.objects.create(
            user_id=k,
            location=demjson.decode(v).get("location")
        )


@app.task()
def create_day_inspection_task():
    """
    日巡检任务创建
    :return:
    """
    # 获取需要日巡检的电站
    sql = """
    select
     id,inspection_time,customer_id,electricity_user_id,service_team
    from power_station
    where inspection_time is not null and end_time>"{} and deleted=0 and is_stop=0"
    """.format(datetime.datetime.now().date())
    power_stations = base_db.query(sql)
    for power_station in power_stations:
        if not power_station.get("electricity_user_id") and \
                not power_station.get("customer_id"):
            continue
        # 获取电站所对应的客户代表
        if power_station.get("electricity_user_id"):
            sql = """
            select
             a.service_staff_id as leader
            from
             customer a
            left join
             electricity_user b
            on b.customer_id=a.id
            where b.id={}
            """.format(power_station.get("electricity_user_id"))
        if power_station.get("customer_id"):
            sql = """
                        select
                         a.service_staff_id as leader
                        from
                         customer a
                        left join
                         electricity_user b
                        on b.customer_id=a.id
                        where a.id={}
                        """.format(power_station.get("customer_id"))
        leader = base_db.query(sql)
        # 获取巡检时间
        plan_time = power_station.get("inspection_time")
        plan_time = plan_time.split(",")
        for item in plan_time:
            todey = datetime.datetime.today().strftime("%Y-%m-%d")
            plan_date = "{} {}:00".format(todey, item)
            # 判断巡检计划是否已经存在
            plan = InspectionPlan.objects.filter(
                station_id=power_station.get("id"),
                plan_date=plan_date).first()
            if plan:
                continue
            plan = InspectionPlan.objects.create(
                station_id=power_station.get("id"),
                plan_date=plan_date,
                leader_id=leader[0].get("leader"))

            team = Team.objects.filter(
                id=power_station.get("service_team")).first()
            InspectionTask.objects.create(
                plan=plan,
                name="IST-" +
                     (datetime.datetime.now().date().strftime("%Y%m%d") +
                      str(get_serial_number()).zfill(5)),
                status="pending" if team else "waiting_dispatch",
                work_date=plan_date,
                team=team if team else None,
                assign_date=datetime.datetime.now(),
                type=1)
            # if team:
            #     # 发送站内消息
            #     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)
            # param = {
            #     "recipient": Accounts.objects.filter(id__in=account),
            #     "verb": "系统派发巡检任务",
            #     "action_object": inspectiontask,
            #     "description":
            #         "系统给您分派了一个巡检任务 {}".format(inspectiontask.id),
            # }
            # sender = Accounts.objects.filter(username="admin").first()
            # notify.send(sender, **param)


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()


@app.task()
def dashboard_customers():
    sql = """
        select
            distinct
            s.id,
            s.name,
            s.number,
            s.longitude,
            s.latitude,
            s.address,
            s.adcode,
            concat('0',
            if(s.im is not null,concat(',',s.im),''),
            if(s.eq is not null,concat(',',s.eq),''),
            if(s.rp is not null,concat(',',s.rp),''),
            if(s.mg is not null,concat(',',s.mg),''),
            if(s.na is not null,concat(',',s.na),'')) nature,
            s.enterprise_id,
            s.province,
            s.city,
            s.year_month
        from (select
            c.id,
            a.name,
            c.number,
            b.longitude,
            b.latitude,
            c.addr address,
            b.district adcode,
            if(FIND_IN_SET("10", a.type),1,null) im,
            if(d.customer_id,2,null) eq,
            if(f.customer_id,4,null) rp,
            if(e.id,6,null) mg,
            if(b.nature not in (0,1,2,4,6),b.nature,7) na,
            a.service_enterprise_id enterprise_id,
            b.adcode province,
            b.city_code city,
            f.`year_month`
        from customer a
        left join enterprise b
        on a.enterprise_id=b.id
        left join electricity_user c
        on c.customer_id=a.id
        left join (select customer_id from equipments group by customer_id) d
        on d.customer_id=a.id
        left join microgrid e
        on e.customer_id=a.id
        left join (
                select customer_id, max(`year_month`) `year_month`
                from report_upload_record group by customer_id) f
        on f.customer_id=a.id
        where a.level >=90 and a.level <=120
        and a.deleted=0
        and b.longitude>1
        and b.longitude is not null
        order by c.number) s
        """
    customers = base_db.query_list(sql)
    customers = pickle.dumps(customers)
    dashboard_redis_api.set("subscribe_number", customers)


@app.task()
def clear_dashboard_zero_eq():
    sql1 = """
    delete from oa_real_data where right(tm,8)="00:00:00"
    """
    base_db.query(sql1)
    sql2 = """
    delete from oa_real_data
    where left(tm,7) < (select tm
        from (select max(left(tm,7)) tm from oa_real_data) k)
    """
    base_db.query(sql2)


@app.task()
def check_monitor_over_tm():
    # 504 设备失联
    sql = """
        select
        TIMESTAMPDIFF(second,a.tm,NOW()) duration,
        c.id point_id,c.name point_name, a.tm,
        c.customer_id,c.phone_list,d.name customer_name,
        group_concat(distinct f.user_id) user_ids,
        date_sub(NOW(), interval 30 minute) ttag
        from mo_real_data a
        inner join monitor_device b
        on a.imei=b.imei
        inner join monitor_points c
        on b.monitor_point_id=c.id
        inner join customer d
        on c.customer_id=d.id
        left join enterprise e
        on e.id=d.enterprise_id
        left join user_enterprise f
        on e.enterprise_id=f.enterprise_id
        where a.updated_time <= date_sub(NOW(),interval 30 minute)
        group by a.id
        """
    rst = base_db.query(sql)
    mo_alarm = []
    if rst:
        receive_list = []
        for i in rst:
            has_alarm = MonitorAlarm.objects.filter(
                code="504", tm=i["tm"], monitor_point_id=i["point_id"])
            if has_alarm:
                has_alarm.update(duration=i["duration"])
            else:
                MonitorAlarm.objects.filter(code="504", monitor_point_id=i["point_id"]).update(is_end=1)
                mo_d = {
                    "name": "设备失联",
                    "code": "504",
                    "type": 5,
                    "level": 2,
                    "duration": i["duration"],
                    "status": 1,
                    "is_end": 0,
                    "is_send": 1,
                    "is_alarm": 1,
                    "monitor_point_id": i["point_id"],
                    "tm": i["tm"],
                }
                receive_ids = i["user_ids"].split(",") if i["user_ids"] else []
                receive_ids.extend([1, 90014])
                receive_list.extend(message_client.send_station_model(
                    title="设备失联",
                    content="客户: {} 的监控点: {} 发生告警: {}".format(
                        i["customer_name"], i["point_name"], "设备失联"),
                    sender_id=1,
                    receive_ids=receive_ids,
                    type=4))
                if i["phone_list"]:
                    m, s = divmod(i["duration"], 60)
                    h, m = divmod(m, 60)
                    d, h = divmod(h, 24)
                    message_client.send_sms_multi(
                        i["phone_list"].split(","), 960651,
                        [i["customer_name"], i["point_name"], "设备失联",
                         f"{int(d)}天{int(h)}时{int(m)}"])
                mo_alarm.append(MonitorAlarm(**mo_d))
        message_client.send_station_multi(receive_list)
        if mo_alarm:
            MonitorAlarm.objects.bulk_create(mo_alarm)


@app.task()
def check_monitor_data_density():
    nw = datetime.date.today()
    to_day = str(datetime.date.today()) + " 00:00:00"
    yes_day = str(nw +
                  datetime.timedelta(days=-1)) + " 00:00:00"
    sql = """
            SELECT
            SUBSTRING(`table_name`, 4) number
            FROM 
                information_schema.tables 
            WHERE 
                table_schema = 'monitor'
                and table_name like "mo_%"
                and table_name != "mo_test"
            """
    rst = base_db.query(sql)
    number_list = ["'%s'" % i["number"] for i in rst]
    sql1 = """
            select
                c.customer_id,d.name customer_name,
                c.id point_id, d.enterprise_id,
                concat("mo_",e.number) table_name,
                concat('{',
                group_concat(
                concat('"point_id":', c.id, ','),
                concat('"point_name":"', ifnull(c.name,''), '",'),
                concat('"customer_name":"', ifnull(d.name,''), '",'),
                concat('"enterprise_id":"', ifnull(d.enterprise_id,''), '",'),
                concat('"phone_list":"', ifnull(c.phone_list,''), '"')
                ),'}') point_info
            from monitor_device b
            inner join monitor_points c
            on b.monitor_point_id=c.id
            inner join customer d
            on c.customer_id=d.id
            inner join electricity_user e
            on d.id=e.customer_id
            where b.from_oa =0
            and c.created_time<="%s"
            and e.number in (%s)
            group by d.id, e.number, c.id
            """ % (yes_day, ','.join(number_list))
    rst1 = base_db.query(sql1)
    table_map = {}
    for i in rst1:
        table_map.setdefault(i["table_name"], {})
        table_map[i["table_name"]].update({i["point_id"]: ujson.loads(i["point_info"])})
    sql2 = """
        select
        d.enterprise_id,
        group_concat(distinct g.user_id) user_ids
        from monitor_device b
        inner join monitor_points c
        on b.monitor_point_id=c.id
        inner join customer d
        on c.customer_id=d.id
        inner join electricity_user e
        on d.id=e.customer_id
        left join enterprise f
        on f.id=d.enterprise_id
        left join user_enterprise g
        on f.enterprise_id=g.enterprise_id
        where b.from_oa =0
        and c.created_time<="%s"
        and e.number in (%s)
        group by d.id, e.number, c.id
    """ % (yes_day, ','.join(number_list))
    rst2 = base_db.query(sql2)
    ent_map = {}
    for row in rst2:
        ent_map[str(row["enterprise_id"])] = row["user_ids"].split(",") if row["user_ids"] else []

    mo_alarm = []
    receive_list = []
    for table, mp in table_map.items():
        for i, j in mp.items():
            sq = """
                select count(id) count
                from %s
                where tm >= "%s"
                and tm <"%s"
                and point_id=%s
                """ % (table, yes_day, to_day, str(i))
            rt = mo_db.query(sq)
            if not rt:
                continue
            count = rt[0]["count"]
            if count < 172:
                mo_d = {
                    "name": "设备上传率不足60%",
                    "code": "605",
                    "type": 6,
                    "level": 2,
                    "duration": 86400,
                    "status": 1,
                    "is_end": 1,
                    "is_send": 1,
                    "is_alarm": 1,
                    "monitor_point_id": i,
                    "tm": nw + datetime.timedelta(days=-1),
                }
                mo_alarm.append(MonitorAlarm(**mo_d))

                receive_ids = ent_map[j["enterprise_id"]] if ent_map.get(j["enterprise_id"]) else []
                receive_ids.extend([1, 90014])
                receive_list.extend(message_client.send_station_model(
                    title="设备上传率不足60%",
                    content="客户: {} 的监控点: {} 发生告警: {}".format(
                        j["customer_name"], j["point_name"], "设备上传率不足60%"),
                    sender_id=1,
                    receive_ids=receive_ids,
                    type=4))
                # phone_list = j["phone_list"]
                # if phone_list:
                #     message_client.send_sms_multi(
                #         phone_list.split(","), 960651,
                #         [j["customer_name"], j["point_name"], "设备上传率不足60%",
                #          "1天0时0"])
    message_client.send_station_multi(receive_list)
    if mo_alarm:
        MonitorAlarm.objects.bulk_create(mo_alarm)

@app.task()
def insert_oa_real_data():
    sql = """
    select
        round(sum(a.ept),2) ep,
        round(sum(a.eqi)+sum(a.eqe),2) eq, 
        round(sum(c.power),0) capacity,
        round(sum(a.p),0) power, 
        ifnull(e.adcode,"") province,
        ifnull(e.city_code,"") city,
        d.service_enterprise_id enterprise_id,
        concat(date_format(now(), '%Y-%m-%d %H:%i:'),'00') tm
    from mo_real_data a
    left join monitor_device b
    on a.imei=b.imei
    left join monitor_points c
    on c.id=b.monitor_point_id
    left join customer d
    on c.customer_id=d.id
    left join enterprise e
    on e.id=d.enterprise_id
    where d.service_enterprise_id=1000
    group by d.service_enterprise_id, e.adcode, e.city_code
    """
    rst = base_db.query(sql)
    for i in rst:
        i["tm"] = str(i["tm"])
        dashboard_redis_api.set(
            "dashboard_real_data_%s" % str(i["enterprise_id"]),
            json.dumps(i))
        insert_sql = """
        INSERT INTO `oa_real_data`
        (`capacity`, `power`, `ep`, `eq`, `enterprise_id`, `province`, `city`, `tm`)
        VALUES
        ({}, {}, {}, {}, '{}', '{}', '{}', '{}')
        """.format(i["capacity"], i["power"], i["ep"], i["eq"],
                   i["enterprise_id"],i["province"],i["city"], i["tm"])
        base_db.query(insert_sql)

@app.task()
def get_dashboard_month_ep():
    _sql1 = """
    select sum(ep) max_ep, enterprise_id from (
        select
        sum(ifnull(A.ep,0)) ep,
        case when E.org_id not in (75,76,80,52,49,23,6,29,30,36,20,54,28,70,26,77,23,
        19,31,32,33,34,35,37,43,44,45,46,47,48,53,41,39,40,42,50,27,25,78,24) then 1
        when E.org_id in (29,30,36,20,54) then 54
        when E.org_id in (75,76,80,52) then 52
        when E.org_id in (28,70) then 70
        when E.org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19
        when E.org_id in (41,39,40,42,50) then 41
        when E.org_id in (25,78) then 78
        else E.org_id end enterprise_id
        from  im_real A 
        left join im_device B on A.imei=B.sn
        left join crm_transformer C on B.transformer_id = C.id
        left join crm_source D on C.source_id=D.id
        left join crm_customer E on D.customer_id=E.id
        where A.tm>=date_format(NOW(), '%Y-%m-%d')
        and C.capacity is not null
        and E.province is not null
        and E.city is not null and E.province!=0
        and E.city!=0
        and E.level >= 90 and E.level <= 120
        group by E.org_id,E.province,E.city) A
        group by enterprise_id
        union
        select sum(ep) max_ep, enterprise_id from (
        select
        sum(ifnull(A.ep,0)) ep,
        1003 enterprise_id
        from  im_real A 
        left join im_device B on A.imei=B.sn
        left join crm_transformer C on B.transformer_id = C.id
        left join crm_source D on C.source_id=D.id
        left join crm_customer E on D.customer_id=E.id
        where A.tm>=date_format(NOW(), '%Y-%m-%d')
        and E.id=18766
        group by E.org_id,E.province,E.city) A
        union
        select sum(ep) max_ep, enterprise_id from (
        select
        sum(ifnull(A.ep,0)) ep,
        1004 enterprise_id
        from  im_real A 
        left join im_device B on A.imei=B.sn
        left join crm_transformer C on B.transformer_id = C.id
        left join crm_source D on C.source_id=D.id
        left join crm_customer E on D.customer_id=E.id
        where A.tm>=date_format(NOW(), '%Y-%m-%d')
        and E.id in (5100, 5473, 5987, 75852, 75853, 75854)
        group by E.province,E.city) B
        group by enterprise_id
        order by enterprise_id
    """
    rst1 = power_db.query(_sql1)
    _sql2 = """
    select sum(ep) min_ep, enterprise_id
        from (
        select ep, case when b.enterprise_id not in (75,76,80,52,49,23,6,29,30,36,20,54,28,70,26,77,23,
        19,31,32,33,34,35,37,43,44,45,46,47,48,53,41,39,40,42,50,27,25,78,24,1003,1004,1000) then 1
        when b.enterprise_id in (29,30,36,20,54) then 54
        when b.enterprise_id in (75,76,80,52) then 52
        when b.enterprise_id in (28,70) then 70
        when b.enterprise_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19
        when b.enterprise_id in (41,39,40,42,50) then 41
        when b.enterprise_id in (25,78) then 78
        else b.enterprise_id end enterprise_id from oa_real_data a right join
        (select min(tm) tm,
        enterprise_id,
        province,city  from oa_real_data
        where DATE_FORMAT(tm,'%Y%m')=DATE_FORMAT(CURDATE( ) , '%Y%m')
        group by enterprise_id,province,city) b
        on a.enterprise_id=b.enterprise_id
        and a.province=b.province and a.city=b.city and a.tm=b.tm) A
        group by enterprise_id
        order by enterprise_id
    """
    rst2 = base_db.query(_sql2)
    max_ep = 0
    min_ep = 0
    for i in range(len(rst1)):
        max_ep += rst1[i]["max_ep"]
        min_ep += rst2[i]["min_ep"]
        dashboard_redis_api.set(
            "dashboard_month_ep_%s" % str(rst2[i]["enterprise_id"]),
            round(rst1[i]["max_ep"] - rst2[i]["min_ep"], 2) / 15)
    dashboard_redis_api.set(
        "dashboard_month_ep_", round(max_ep - min_ep, 2) / 15)


@app.task()
def get_dashboard_real_data():
    sql = """
    select 
            CAST(sum(ifnull(C.capacity,0)) AS SIGNED)*2 capacity,
            sum(ifnull(A.psum,0))*2 power,
            sum(ifnull(A.psum,0))/sum(ifnull(C.capacity,0))*100 load_rate,
            sum(ifnull(A.ep,0)) ep,
            (sum(ifnull(A.eq1,0))+sum(ifnull(A.eq2,0))) eq,
            now() tm,
            case when E.org_id not in (75,76,80,52,49,23,6,29,30,36,20,54,28,70,26,77,23,
        19,31,32,33,34,35,37,43,44,45,46,47,48,53,41,39,40,42,50,27,25,78,24) then 1
        	when E.org_id in (29,30,36,20,54) then 54
        	when E.org_id in (75,76,80,52) then 52
        	when E.org_id in (28,70) then 70
        	when E.org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19
        	when E.org_id in (41,39,40,42,50) then 41
        	when E.org_id in (25,78) then 78
        	else E.org_id end enterprise_id
        from  im_real A
        left join im_device B
        on A.imei=B.sn
        left join crm_transformer C
        on B.transformer_id = C.id
        left join crm_source D
        on C.source_id=D.id
        left join crm_customer E
        on D.customer_id=E.id
        WHERE A.tm>=date_format(NOW(), '%Y-%m-%d')
        and C.capacity is not null
        and E.org_id is not null
        group by case when E.org_id not in (75,76,80,52,49,23,6,29,30,36,20,54,28,70,26,77,23,
        19,31,32,33,34,35,37,43,44,45,46,47,48,53,41,39,40,42,50,27,25,78,24) then 1
        		 when E.org_id in (29,30,36,20,54) then 54
        	 	 when E.org_id in (75,76,80,52) then 52
        	 	 when E.org_id in (28,70) then 70
        	 	 when E.org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19
        	 	 when E.org_id in (41,39,40,42,50) then 41
        	 	 when E.org_id in (25,78) then 78
        		 else E.org_id end
       	union
        select 
            CAST(sum(ifnull(C.capacity,0)) AS SIGNED) capacity,
            sum(ifnull(A.psum,0)) power,
            sum(ifnull(A.psum,0))/sum(ifnull(C.capacity,0))*100 load_rate,
            sum(ifnull(A.ep,0)) ep,
            (sum(ifnull(A.eq1,0))+sum(ifnull(A.eq2,0))) eq,
            now() tm,
            1003 enterprise_id
        from  im_real A
        left join im_device B
        on A.imei=B.sn
        left join crm_transformer C
        on B.transformer_id = C.id
        left join crm_source D
        on C.source_id=D.id
        left join crm_customer E
        on D.customer_id=E.id
        WHERE A.tm>=date_format(NOW(), '%Y-%m-%d')
        and C.capacity is not null
        and E.org_id is not null
        and A.imei in("44003A19473832383636301F",
        "44004519473832383636301F",
        "44001C19473832383636301F")
        union
        select 
            CAST(sum(ifnull(C.capacity,0)) AS SIGNED) capacity,
            sum(ifnull(A.psum,0)) power,
            sum(ifnull(A.psum,0))/sum(ifnull(C.capacity,0))*100 load_rate,
            sum(ifnull(A.ep,0)) ep,
            (sum(ifnull(A.eq1,0))+sum(ifnull(A.eq2,0))) eq,
            now() tm,
            1004 enterprise_id
        from  im_real A
        left join im_device B
        on A.imei=B.sn
        left join crm_transformer C
        on B.transformer_id = C.id
        left join crm_source D
        on C.source_id=D.id
        left join crm_customer E
        on D.customer_id=E.id
        WHERE A.tm>=date_format(NOW(), '%Y-%m-%d')
        and C.capacity is not null
        and E.org_id is not null
        and E.id in (5100, 5473, 5987, 75852, 75853, 75854)
        order by enterprise_id
    """
    rst = power_db.query(sql)
    dt = {
        "capacity": 0,
        "power": 0,
        "load_rate": 0,
        "ep": 0,
        "eq": 0,
        "tm": None,
    }
    for i in rst:
        i["tm"] = str(i["tm"])
        dashboard_redis_api.set(
            "dashboard_real_data_%s" % str(i["enterprise_id"]),
            json.dumps(i))
        dt["capacity"] += i["capacity"]
        dt["power"] += i["power"]
        dt["ep"] += i["ep"]
        dt["eq"] += i["eq"]
        dt["tm"] = i["tm"]
    dt["load_rate"] = dt["power"] / dt["capacity"] * 100
    dashboard_redis_api.set("dashboard_real_data_", json.dumps(dt))


def monitor_sync_record(session, sid, monitor_model, record_raw):
    start = time.strptime(str(record_raw["tm"]), "%Y-%m-%d %H:%M:%S")
    # end = time.strptime("2021-07-03 23:59:59", "%Y-%m-%d %H:%M:%S")
    rq_payload = {
        "imei": record_raw["imei"],
        "startDate": int(time.mktime(start)) * 1000,
        "endDate": int(time.time()) * 1000
    }
    rst = session.post(
        "https://www.epkeeper.net/a/partyParkExternal/queryImeiHistory?__sid=%s" %
        sid, data=rq_payload)
    print(rq_payload)
    print(rst.url)
    print(rst.json())
    dt = rst.json()
    obj = dt.get("obj")
    mo_list = []
    if obj and isinstance(obj, list):
        for i in obj:
            mo_list.append(
                monitor_model(
                    point_id=record_raw["point_id"],
                    ua=i.get("ady", 0.0),
                    ub=i.get("bdy", 0.0),
                    uc=i.get("cdy", 0.0),
                    ia=i.get("adl", 0.0),
                    ib=i.get("bdl", 0.0),
                    ic=i.get("cdl", 0.0),
                    pa=i.get("ayggl", 0.0),
                    pb=i.get("byggl", 0.0),
                    pc=i.get("cyggl", 0.0),
                    p=i.get("totalyggl", 0.0),
                    q=i.get("totalwggl", 0.0),
                    pfa=i.get("aglys", 0.0),
                    pfb=i.get("bglys", 0.0),
                    pfc=i.get("cglys", 0.0),
                    pf=i.get("totalglys", 0.0),
                    f=i.get("epiJ", 0.0),
                    p_d=i.get("epiF", 0.0),
                    ept=i.get("epi", 0.0),
                    eqi=i.get("epiP", 0.0),
                    eqe=i.get("epiG", 0.0),
                    tm=i.get("createDate", 0.0)))
        if mo_list:
            monitor_model.objects.using("monitor").bulk_create(mo_list)
    base_db.query("""
        update monitor_sync_record set tm='%s' where device_id=%s
        """ % (str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
               record_raw["device_id"]))
    return record_raw["device_id"]


def insert_to_real():
    sql = """
        select distinct
        c.imei, `ua`, `ub`, `uc`, `uab`, `ubc`, `uca`, `ia`, `ib`,
        `ic`, `ir`, `pa`, `pb`, `pc`, `p`, `qa`, `qb`, `qc`, `q`, `sa`,
        `sb`, `sc`, `s`, `pfa`, `pfb`, `pfc`, `pf`, `f`, `ept`, `eqi`,
        `eqe`, `p_d`, `lvur`, `iur`, `ua_thd`, `ub_thd`, `uc_thd`, `ia_thd`,
        `ib_thd`, `ic_thd`, `t1`, `t2`, `t3`, `t4`, `segment1`, `segment2`,
        `segment3`, `segment4`, `segment5`, `segment6`, b.`tm`
        from (select distinct max(tm) tm,point_id from mo_3107593345 group by point_id) a
        left join mo_3107593345 b
        on a.tm=b.tm and a.point_id=b.point_id
        left join console.monitor_device c
        on b.point_id=c.monitor_point_id
        """
    rows = mo_db.query(sql)
    for row in rows:
        MonitorRealData.objects.update_or_create(defaults=row,
                                                 imei=row["imei"])


@app.task()
def get_changsha_real_data():
    sql = """
    select a.*,b.capacity, a.power/b.capacity load_rate, concat(left(now(),17), "00") tm 
    from (select 1001 enterprise_id, sum(ifnull(p,0))*2 power, sum(ept) ep,
    (sum(ifnull(eqi,0))+sum(ifnull(eqe,0))) eq from mo_real_data
    where imei like "PN%") a
    left join
    (select aa.service_enterprise_id enterprise_id, sum(transformer_capacity) capacity
    from customer aa
    left join electricity_user a
    on a.customer_id=aa.id
    where aa.service_enterprise_id=1001) b
    on a.enterprise_id=b.enterprise_id
    """
    rst = base_db.query(sql)
    dt = {
        "capacity": 0,
        "power": 0,
        "load_rate": 0,
        "ep": 0,
        "eq": 0,
        "tm": None,
    }
    for i in rst:
        i["tm"] = str(i["tm"])
        dashboard_redis_api.set(
            "dashboard_real_data_%s" % str(i["enterprise_id"]),
            json.dumps(i))
        dt["capacity"] += i["capacity"]
        dt["power"] += i["power"]
        dt["ep"] += i["ep"]
        dt["eq"] += i["eq"]
        dt["tm"] = i["tm"]
        insert_sql = """
        INSERT INTO `oa_real_data`
        (`capacity`, `power`, `ep`, `eq`, `enterprise_id`, `province`, `city`, `tm`)
        VALUES
        ({}, {}, {}, {}, 1001, '430000', '430100', '{}')
        """.format(i["capacity"], i["power"], i["ep"], i["eq"], i["tm"])
        base_db.query(insert_sql)


@app.task()
def get_changsha_month_ep():
    _sql1 = """
    select sum(b.ept) max_ep, 1001 enterprise_id
    from (select max(tm) tm, imei from mo_real_data where imei like "PN%"
    and tm>=date_format(NOW(), '%Y-%m-%d') group by imei) a
    left join mo_real_data b
    on a.tm=b.tm and a.imei=b.imei
    """
    rst1 = base_db.query(_sql1)
    _sql2 = """
    select sum(b.ep) min_ep, a.enterprise_id
    from  (select min(tm) tm, enterprise_id from oa_real_data where enterprise_id=1001
    and DATE_FORMAT(tm,'%Y%m')=DATE_FORMAT(CURDATE( ) , '%Y%m')) a
    left join oa_real_data b
    on a.tm=b.tm and a.enterprise_id=b.enterprise_id
    group by a.enterprise_id
    """
    rst2 = base_db.query(_sql2)
    max_ep = 0
    min_ep = 0
    for i in range(len(rst1)):
        max_ep += rst1[i]["max_ep"]
        min_ep += rst2[i]["min_ep"]
        dashboard_redis_api.set(
            "dashboard_month_ep_%s" % str(rst2[i]["enterprise_id"]),
            round(rst1[i]["max_ep"] - rst2[i]["min_ep"], 2))


@app.task(autoretry_for=(Exception,), retry_kwargs={'max_retries': 3, 'countdown': 5})
def insert_changsha_mo_dt_sync():
    class MonitorMetaDataChangSha(MonitorMetaData):
        class Meta:
            db_table = "mo_" + "3107593345"

    session = requests.Session()
    adapter = requests.adapters.HTTPAdapter(pool_connections=10,
                                            pool_maxsize=100,
                                            max_retries=3)
    session.mount("http://", adapter)
    session.mount("https://", adapter)

    record_sql = """
            select a.device_id, a.imei,
            a.tm, b.monitor_point_id point_id
            from monitor_sync_record a
            left join monitor_device b
            on a.device_id=b.id
            """
    record_raws = base_db.query(record_sql)
    m = hashlib.md5(("f659645bcb2ca1cf0e6acff33c7eef0c,%s" %
                     datetime.datetime.now().strftime('%Y%m%d%H%M')).encode())
    rst = session.get("https://www.epkeeper.net/party/faladi/%s" % m.hexdigest())
    sid = rst.content.decode().split(":")[-1]
    request_list = []
    with ThreadPoolExecutor(max_workers=5) as t:
        for record_raw in record_raws:
            obj = t.submit(
                monitor_sync_record, session, sid,
                MonitorMetaDataChangSha, record_raw)
            request_list.append(obj)
        for future in as_completed(request_list):
            data = future.result()
            print(data)
            print('*' * 50)
    session.close()
    insert_to_real()


@app.task()
def sync_monitor_points_and_devices_from_oa():
    _sql1 = """
    select
        a. id,
        if(a.online_time='0000-00-00 00:00:00',null,a.online_time) created_time,
        a.sn imei,
        case when a.`type` in (4,5) then 4
        when a.`type`=7 then 2
        when a.`type` in (2,3) then 3
        when a.`type`=6 then 5
        else a.`type` end manufacturer,
        case when a.`type`=2 then 4
        when a.`type`=3 then 5
        when a.`type`=4 then 6
        when a.`type`=5 then 7
        when a.`type`=7 then 3
        when a.`type`=8 then 1
        when a.`type`=6 then 8
        else `type` end model,
        2 network_type,
        a.sim_no sim,
        1 `status`,
        a.id `monitor_point_id`,
        1 enterprise_id,
        1 from_oa
    from im_device a
    left join im_customer b
    on a.im_customer_id=b.id
    left join crm_customer c
    on c.id=b.customer_id
    where a.sn not like "8649%" and a.sn not like "8675%"
    and a.sn not like "8637%" and a.sn not like "8679%"
    and a.status in (2,3,4,5,6,7,8)
    """
    _sql2 = """
    select
        A.id,
        0 deleted,
        if(A.online_time='0000-00-00 00:00:00',null,A.online_time) created_time,
        if(A.online_time='0000-00-00 00:00:00',null,A.online_time) updated_time,
        B.id as electrical_info_id,
        A.`name`,
        B.customer_id + 5000 customer_id,
        B.customer_id + 5000 electricity_user_id,
        B.customer_id + 5000 station_id,
        1 template_id,
        0 `status`,
        300 frequency,
        1 from_oa,
        D.capacity power,
        concat("tb_", C.sn) oa_table,
        A.no oa_line
    from im_device A
    left join crm_source B
    ON A.source_id=B.id
    left join crm_customer C
    on C.id=B.customer_id
    left join crm_transformer D
    on A.transformer_id=D.id
    where A.sn not like "8649%" and A.sn not like "8675%"
    and A.sn not like "8637%" and A.sn not like "8679%"
    """
    insert_sql1 = power_db.get_insert_or_update_sql("monitor_device", _sql1)
    insert_sql2 = power_db.get_insert_or_update_sql("monitor_points", _sql2)
    base_db.query(insert_sql1)
    base_db.query(insert_sql2)

@app.task()
def sync_monitor_points_from_oa():
    _sql1 = """
        select imei from monitor_device
        where monitor_point_id is null
        and (imei like "8649%" or imei  like "8675%" or imei like "8637%" or imei like "8679%")
        """
    imei_list = base_db.query(_sql1)
    if imei_list:
        imei_list = ['"%s"' % i["imei"] for i in imei_list]
    else:
        imei_list = []
    if imei_list:
        _sql1 = """ 
            select
                a.id,
                if(a.online_time='0000-00-00 00:00:00',null,a.online_time) created_time,
                if(a.online_time='0000-00-00 00:00:00',null,a.online_time) updated_time,
                b.customer_id + 5000 customer_id,
                b.customer_id + 5000 electricity_user_id,
                b.customer_id + 5000 station_id,
                b.id as electrical_info_id,
                a.name,
                a.v_c voltage_ratio,
                a.i_c current_ratio,
                d.capacity power,
                1 template_id,
                0 `status`,
                300 frequency,
                0 from_oa
            from im_device a
            left join crm_source b
            ON A.source_id=B.id
            left join crm_customer c
            on C.id=B.customer_id
            left join crm_transformer d
            on A.transformer_id=D.id
            where b.customer_id is not null and a.sn in ({})
            """.format(",".join(imei_list))
        _sql2 = """
            select
                a.sn imei,
                a.id monitor_point_id,
                c.sn number
            from im_device a
            left join crm_source b
            ON A.source_id=B.id
            left join crm_customer c
            on C.id=B.customer_id
            left join crm_transformer d
            on A.transformer_id=D.id
            where b.customer_id is not null and a.sn in ({})
            """.format(",".join(imei_list))
        insert_sql1 = power_db.get_insert_or_update_sql("monitor_points", _sql1)
        if insert_sql1:
            base_db.query(insert_sql1)
            rst2 = power_db.query(_sql2)
            for i in rst2:
                MonitorDevices.objects.filter(
                    imei=i["imei"]).update(monitor_point_id=i["monitor_point_id"])
                move_data_to_number(i["number"], i["imei"], i["monitor_point_id"])

def move_data_to_number(number, imei, point_id):
    class MonitorMetaDataA(MonitorMetaData):
        class Meta:
            db_table = "mo_" + number

    dt_list = []
    dts = MonitorMetaDataForTest.objects.using("monitor").filter(imei=imei)
    for i in dts.values(
            "ua", "ub", "uc", "uab", "ubc", "uca", "ia", "ib", "ic", "ir", "pa", "pb", "pc", "p",
            "qa", "qb", "qc", "q", "sa", "sb", "sc", "s", "pfa", "pfb", "pfc", "pf", "f", "ept",
            "eqi", "eqe", "p_d", "lvur", "iur", "ua_thd", "ub_thd", "uc_thd", "ia_thd", "ib_thd",
            "ic_thd", "t1", "t2", "t3", "t4", "segment1", "segment2", "segment3", "segment4",
            "segment5", "segment6", "tm",
    ):
        i.update({"point_id": point_id})
        dt_list.append(MonitorMetaDataA(**i))
    try:
        MonitorMetaDataA.objects.using("monitor").bulk_create(dt_list)
        dts.delete()
    except ProgrammingError as e:
        rst = re.search(r"^.*Table '(.*)' doesn't exist", str(e))
        if rst:
            table = rst.group(1).split(".")[-1]
            mo_db.query(create_monitor_tb % table)
            MonitorMetaDataA.objects.using("monitor").bulk_create(dt_list)
            dts.delete()
