import copy
import logging
import random
import demjson
import pandas as pd
import datetime
import requests
import calendar as cal
from django.db.models import Q
from django.conf import settings
from requests.cookies import RequestsCookieJar

import json
from dateutil.relativedelta import relativedelta
from .repositories import power_oa_repo, statistics_repo
from common.third_party_api.hcbgps.openapi import HuCheBaoApi
from common.third_party_api.amap.openapi import AmapApi
from common.utils.models import BaseConnection
from common.utils.models import MonitorOAConnection
from common.utils.models import PowerOAConnection
from common.third_party_api.surveillance import video_client
from console.dashboard.models import VideosModel
from console.team.models import Team
from console.video_management.models import VideoSystem
from common.utils.epkeeperlib.ep_fix import get_hour_ele

from console.APP.models import UserLocation

from common.utils.redis_api import redis_api_base, dashboard_redis_api

from common.accounts.models import Users

# amap_api = AmapApi()
from common.accounts.models import UserEnterprise

from common.accounts.models import Enterprise

from common.utils.models import ScoreConnection

base_db = BaseConnection()
power_oa = PowerOAConnection()
monitor_db = MonitorOAConnection()
logger = logging.getLogger("")


class PowerOAService(object):
    def get_cars(self, page, page_size, parameter):
        c_list = []
        car_list = []
        sql = """
        select
            A.id team_id,
            A.name team_name,
            A.car_number,
            A.type,
            B.nickname team_headman_name,
            B.phone team_headman_phone
        from team A
        left join users B
        on A.team_headman_id=B.id
        """
        teams = base_db.query(sql)
        rest_car_list = HuCheBaoApi().get_car_list()
        if rest_car_list is not None:
            car_list = rest_car_list
        for car in car_list:
            rst = {}
            rst["imei"] = car["1"]
            rst["name"] = car["2"]
            rst["latitude"] = car.get("9")
            rst["longitude"] = car.get("10")
            rst["number_plate"] = car.get("22")
            rst["status"] = car.get("4")
            rst["direction"] = car.get("12")
            rst["speed"] = car.get("13")
            rst["team_name"] = None
            rst["team_id"] = None
            rst["team_headman_name"] = None
            rst["team_headman_phone"] = None
            for team in teams:
                if team["car_number"] == rst["number_plate"]:
                    rst["team_name"] = team["team_name"]
                    rst["team_headman_name"] = team["team_headman_name"]
                    rst["team_headman_phone"] = team["team_headman_phone"]
                    rst["team_id"] = team["team_id"]
                    break
            c_list.append(rst)
        count = len(c_list)
        if parameter:
            c_list = list(
                filter(
                    lambda x: parameter in ''.join([
                        x["name"] if x["name"] else '', x["number_plate"]
                        if x["number_plate"] else ''
                    ]), c_list))
        c_list = sorted(c_list, key=lambda x: x["imei"])
        if page and page_size:
            c_list = c_list[(page - 1) * page_size:page * page_size]
        return c_list, count

    def get_cache_cars(self):
        c_list = []
        car_list = []
        # teams = Team.objects.filter(deleted=False)
        sql = """
        select
            A.id team_id,
            A.name team_name,
            A.car_number,
            A.type,
            B.nickname team_headman_name,
            B.phone team_headman_phone
        from team A
        left join users B
        on A.team_headman_id=B.id
        """
        teams = base_db.query(sql)

        rest_car_list = HuCheBaoApi().get_car_list()
        if rest_car_list is not None:
            car_list = rest_car_list
        for car in car_list:
            rst = {}
            rst["imei"] = car["1"]
            rst["name"] = car["2"]
            rst["latitude"] = car.get("9")
            rst["longitude"] = car.get("10")
            rst["number_plate"] = car.get("22")
            rst["status"] = car.get("4")
            rst["direction"] = car.get("12")
            rst["speed"] = car.get("13")
            rst["team_name"] = None
            rst["team_id"] = None
            rst["team_headman_name"] = None
            rst["team_headman_phone"] = None
            for team in teams:
                if team["car_number"] == rst["number_plate"]:
                    rst["team_name"] = team["team_name"]
                    rst["team_headman_name"] = team["team_headman_name"]
                    rst["team_headman_phone"] = team["team_headman_phone"]
                    rst["team_id"] = team["team_id"]
                    break
            c_list.append(rst)
        count = len(c_list)
        c_list = sorted(c_list, key=lambda x: x["imei"])
        return c_list, count

    def get_io_customers(self, page, page_size, adcode, parameter, longitude,
                         latitude):
        return power_oa_repo.get_io_customers(page, page_size, adcode,
                                              parameter, longitude, latitude)

    def get_cache_io_customers(self):
        return power_oa_repo.get_cache_io_customers()

    def get_subscribe_customer(self, page, page_size, adcode, parameter,
                               longitude, latitude):
        return power_oa_repo.get_subscribe_customer(page, page_size, adcode,
                                                    parameter, longitude,
                                                    latitude)

    def get_cache_subscribe_customer(self):
        return power_oa_repo.get_cache_subscribe_customer()

    def get_cache_subscribe_customer_count(self, **kwargs):
        return power_oa_repo.get_cache_subscribe_customer_count(**kwargs)

    def get_monitor_device(self, **kwargs):
        return power_oa_repo.get_monitor_device(**kwargs)

    def get_capacity(self):
        return power_oa_repo.get_capacity()

    def get_cache_capacity(self, **kwargs):
        return power_oa_repo.get_cache_capacity(**kwargs)

    def get_real_data_true(self, **kwargs):
        join_ents = kwargs.get("join_ents")
        entids = []
        where = ["1=1 and A.tm>=date_format(NOW(), '%Y-%m-%d') and C.capacity is not null"]
        if join_ents:
            if "1" in join_ents:
                entids = ["75", "76", "80", "52",
                          "49",
                          "6",
                          "29", "30", "36", "20", "54",
                          "23",
                          "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"]
                if "54" in join_ents:
                    entids = list(set(entids) ^ set(["29", "30", "36", "20", "54"]))
                if "52" in join_ents:
                    entids = list(set(entids) ^ set(["75", "76", "80", "52"]))
                if "6" in join_ents:
                    entids = list(set(entids) ^ set(["6"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "49" in join_ents:
                    entids = list(set(entids) ^ set(["49"]))
                if "70" in join_ents:
                    entids = list(set(entids) ^ set(["28", "70"]))
                if "26" in join_ents:
                    entids = list(set(entids) ^ set(["26"]))
                if "77" in join_ents:
                    entids = list(set(entids) ^ set(["77"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "19" in join_ents:
                    entids = list(set(entids) ^ set(["19", "31", "32", "33", "34", "35", "37",
                                                     "43", "44", "45", "46", "47", "48", "53"]))
                if "41" in join_ents:
                    entids = list(set(entids) ^ set(["41", "39", "40", "42", "50"]))
                if "27" in join_ents:
                    entids = list(set(entids) ^ set(["27"]))
                if "78" in join_ents:
                    entids = list(set(entids) ^ set(["25", "78"]))
                if "24" in join_ents:
                    entids = list(set(entids) ^ set(["24"]))
                if entids:
                    where.extend(['E.org_id not in (%s)' % ','.join(entids)])
                else:
                    where.extend(['E.org_id in (%s)' % ','.join(join_ents)])
            else:
                if "54" in join_ents:
                    entids.extend(["29", "30", "36", "20", "54"])
                if "52" in join_ents:
                    entids.extend(["75", "76", "80", "52"])
                if "6" in join_ents:
                    entids.extend(["6"])
                if "49" in join_ents:
                    entids.extend(["49"])
                if "23" in join_ents:
                    entids.extend(["23"])
                if "70" in join_ents:
                    entids.extend(["28", "70"])
                if "26" in join_ents:
                    entids.extend(["26"])
                if "77" in join_ents:
                    entids.extend(["77"])
                if "23" in join_ents:
                    entids.extend(["23"])
                if "19" in join_ents:
                    entids.extend(["19", "31", "32", "33", "34", "35", "37",
                                   "43", "44", "45", "46", "47", "48", "53"])
                if "41" in join_ents:
                    entids.extend(["41", "39", "40", "42", "50"])
                if "27" in join_ents:
                    entids.extend(["27"])
                if "78" in join_ents:
                    entids.extend(["25", "78"])
                if "24" in join_ents:
                    entids.extend(["24"])
                if entids:
                    where.extend(['E.org_id in (%s)' % ','.join(entids)])
                else:
                    where.extend(['E.org_id in (%s)' % ','.join(join_ents)])
        else:
            if kwargs.get("enterprise_id", ["0"]) != ["0"]:
                if kwargs.get("enterprise_id", ['1']) == ['1']:
                    where.extend(['E.org_id not in %s' % settings.BRANCH_ENTIDS])
                elif kwargs.get("enterprise_id", ['1']) == ['54']:
                    where.extend(['E.org_id in (29,30,36,20,54)'])
                elif kwargs.get("enterprise_id", ['1']) == ['52']:
                    where.extend(['E.org_id in (75,76,80,52)'])
                elif kwargs.get("enterprise_id", ['1']) == ['70']:
                    where.extend(['E.org_id in (28,70)'])
                elif kwargs.get("enterprise_id", ['1']) == ['19']:
                    where.extend(['E.org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53)'])
                elif kwargs.get("enterprise_id", ['1']) == ['41']:
                    where.extend(['E.org_id in (41,39,40,42,50)'])
                elif kwargs.get("enterprise_id", ['1']) == ['78']:
                    where.extend(['E.org_id in (25,78)'])
                else:
                    where.extend(
                        base_db.filter(
                            kwargs.get("enterprise_id"),
                            value='E.org_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='E.province="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='E.city="{}"'))
        _sql = """
        select
            %s
        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 (select id, case when 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 org_id in (29,30,36,20,54) then 54
        	 	 when org_id in (75,76,80,52) then 52
        	 	 when org_id in (28,70) then 70
        	 	 when org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) then 19
        	 	 when org_id in (41,39,40,42,50) then 41
        	 	 when org_id in (25,78) then 78
        		 else org_id end org_id, province from crm_customer) E
        on D.customer_id=E.id
        """
        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
        """
        count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql)
        print(sql)
        try:
            real_data = power_oa.query(sql)
            if real_data:
                return real_data, None
            return None, None
        except Exception as e:
            logger.debug(e)
            return None, None

    def get_real_data(self, **kwargs):
        if kwargs.get("real", 0) == ['1']:
            return self.get_real_data_true(**kwargs)
        where = ["1=1"]
        entids = []
        join_ents = kwargs.get("join_ents")
        if not kwargs.get("enterprise_id") and join_ents:
            if "1" in join_ents:
                entids = ["75", "76", "80", "52",
                          "49",
                          "6",
                          "23",
                          "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",
                          ]
                if "54" in join_ents:
                    entids = list(set(entids) ^ set(["29", "30", "36", "20", "54"]))
                if "52" in join_ents:
                    entids = list(set(entids) ^ set(["75", "76", "80", "52"]))
                if "6" in join_ents:
                    entids = list(set(entids) ^ set(["6"]))
                if "49" in join_ents:
                    entids = list(set(entids) ^ set(["49"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "70" in join_ents:
                    entids = list(set(entids) ^ set(["28", "70"]))
                if "26" in join_ents:
                    entids = list(set(entids) ^ set(["26"]))
                if "77" in join_ents:
                    entids = list(set(entids) ^ set(["77"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "19" in join_ents:
                    entids = list(set(entids) ^ set(["19", "31", "32", "33", "34", "35", "37",
                                                     "43", "44", "45", "46", "47", "48", "53"]))
                if "41" in join_ents:
                    entids = list(set(entids) ^ set(["41", "39", "40", "42", "50"]))
                if "27" in join_ents:
                    entids = list(set(entids) ^ set(["27"]))
                if "78" in join_ents:
                    entids = list(set(entids) ^ set(["25", "78"]))
                if "24" in join_ents:
                    entids = list(set(entids) ^ set(["24"]))
                if entids:
                    where.extend(['enterprise_id in (%s)' % ','.join(entids)])
                else:
                    where.extend(['enterprise_id in (%s)' % ','.join(join_ents)])
        else:
            if kwargs.get("enterprise_id", ["0"]) != ["0"]:
                if kwargs.get("enterprise_id", ['1']) == ['1']:
                    where.extend(['enterprise_id not in %s' % settings.BRANCH_ENTIDS])
                elif kwargs.get("enterprise_id", ['1']) == ['54']:
                    where.extend(['enterprise_id in (29,30,36,20,54)'])
                elif kwargs.get("enterprise_id", ['1']) == ['52']:
                    where.extend(['enterprise_id in (75,76,80,52)'])
                elif kwargs.get("enterprise_id", ['1']) == ['70']:
                    where.extend(['enterprise_id in (28,70)'])
                elif kwargs.get("enterprise_id", ['1']) == ['19']:
                    where.extend(['enterprise_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53)'])
                elif kwargs.get("enterprise_id", ['1']) == ['41']:
                    where.extend(['enterprise_id in (41,39,40,42,50)'])
                elif kwargs.get("enterprise_id", ['1']) == ['78']:
                    where.extend(['enterprise_id in (25,78)'])
                else:
                    where.extend(
                        base_db.filter(
                            kwargs.get("enterprise_id"),
                            value='enterprise_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='A.province="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='A.city="{}"'))
        where.extend(base_db.filter(kwargs.get("start_time"), value='A.tm>="{}"'))
        where.extend(base_db.filter(kwargs.get("ent_time"), value='A.tm<="{}"'))
        _sql = """
        select
            %s
        from oa_real_data A
        """
        select = """
        sum(capacity)*2 capacity,
        sum(power)*2 power,
        sum(power)/sum(capacity)*100 load_rate,
        sum(ep) ep,
        sum(eq) eq,
        tm
        """
        count_sql = None
        group_by = "A.tm"
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        group_by=group_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_summary(self, **kwargs):
        if kwargs["join_ents"] == ["1003"]:
            rst = {
                "number_count": 1,
                "station_count": 1,
                "monitor_device_count": 3,
                "capacity_count": 3250,
            }
            return rst
        if kwargs["join_ents"] == ["1001"]:
            sql = """
            select "number" `type`, count(1) count,
            sum(transformer_capacity) capacity
            from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            where aa.service_enterprise_id=6
            union 
            select "station" `type`, count(1) count, 0 capacity from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            left join power_station b
            on a.id=b.electricity_user_id
            where aa.service_enterprise_id=6 and b.deleted=0
            union
            select "monitor_device" `type`, count(1) count, 0 capacity from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            left join monitor_points b
            on a.id=b.electricity_user_id
            left join monitor_device c
            on b.id=c.monitor_point_id
            where aa.service_enterprise_id=6 and c.id is not null
            """
            rows = base_db.query(sql)
            rst = {
                "number_count": rows[0]["count"],
                "station_count": rows[1]["count"],
                "monitor_device_count": rows[2]["count"] + 705,
                "capacity_count": rows[0]["capacity"],
            }
            return rst
        elif kwargs["join_ents"] == ["1004"]:
            sql = """
            select "number" `type`, count(1) count,
            sum(transformer_capacity) capacity
            from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            where aa.service_enterprise_id=1004
            union 
            select "station" `type`, count(1) count, 0 capacity from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            left join power_station b
            on a.id=b.electricity_user_id
            where aa.service_enterprise_id=1004 and b.deleted=0
            union
            select "monitor_device" `type`, count(1) count, 0 capacity from customer aa
            left join electricity_user a
            on a.customer_id=aa.id
            left join monitor_points b
            on a.id=b.electricity_user_id
            left join monitor_device c
            on b.id=c.monitor_point_id
            where aa.service_enterprise_id=1004 and c.id is not null
            """
            rows = base_db.query(sql)
            rst = {
                "number_count": rows[0]["count"],
                "station_count": rows[1]["count"],
                "monitor_device_count": rows[2]["count"],
                "capacity_count": rows[0]["capacity"],
            }
            return rst
        device_list, _ = self.get_monitor_device(**kwargs)
        number_count, _ = self.get_cache_subscribe_customer_count(**kwargs)
        if kwargs["join_ents"] == ["1002"]:
            station_count = int(number_count)
        else:
            station_count = int(number_count * 1.4)
        cp_query = copy.deepcopy(kwargs)
        cp_query.update({"real": ['1']})
        capacity_result, _ = self.get_real_data(**cp_query)
        rst = {
            "number_count": number_count,
            "station_count": station_count,
            "monitor_device_count": 0 if int(len(device_list) * 6.6) - 5 < 0 else int(len(device_list) * 6.6) - 5,
        }
        rst["capacity_count"] = capacity_result[0].get("capacity", 0)
        return rst

    def get_weather(self, request):
        adcode = request.GET.get("adcode")
        if not adcode:
            ip = get_ip(request)
            if ip is None:
                adcode = "310000"
            else:
                rst = AmapApi().ip(ip=ip)
                adcode = rst.get("adcode")
        if not adcode:
            adcode = "310000"
        weather_data = AmapApi().weather(city=adcode, extensions="base")
        lives = weather_data.get("lives")
        if isinstance(lives, list):
            weather = lives[0].get("weather")
        else:
            weather = None
        return {"weather": weather}


def get_ip(request):
    '''获取请求者的IP信息'''
    x_forwarded_for = request.META.get('HTTP_X_FORWARDED_FOR')  # 判断是否使用代理
    if x_forwarded_for:
        ip = x_forwarded_for.split(',')[0]  # 使用代理获取真实的ip
    else:
        ip = None
    return ip


class StatisticsService(object):
    def inspection_achievement_rate(self):
        return {
            "inspection_achievement":
                statistics_repo.inspection_achievement_rate()
        }

    def exception_handling_rate(self):
        return {
            "exception_handling": {
                "rate": [
                    {
                        "current_year_month": "2020-07",
                        "month": 7,
                        "completed": 56,
                        "total": 56,
                        "rate": 0.965
                    },
                    {
                        "current_year_month": "2020-08",
                        "month": 8,
                        "completed": 42,
                        "total": 42,
                        "rate": 1
                    },
                    {
                        "current_year_month": "2020-09",
                        "month": 9,
                        "completed": 56,
                        "total": 56,
                        "rate": 1
                    },
                    {
                        "current_year_month": "2020-10",
                        "month": 10,
                        "completed": 32,
                        "total": 33,
                        "rate": 0.969
                    },
                    {
                        "current_year_month": "2020-11",
                        "month": 11,
                        "completed": 56,
                        "total": 60,
                        "rate": 0.93
                    },
                    {
                        "current_year_month": "2020-12",
                        "month": 12,
                        "completed": 2,
                        "total": 2,
                        "rate": 1
                    },
                ],
                "year_on_year":
                    0.11,
                "month_on_month":
                    -0.429,
            },
        }

    def get_electricity_consumption(self):
        return {
            "electricity_consumption":
                power_oa_repo.get_electricity_consumption()
        }

    def get_statistics(self):
        data = {}
        inspection_achievement_rate = self.inspection_achievement_rate()
        exception_handling_rate = self.exception_handling_rate()
        get_electricity_consumption = self.get_electricity_consumption()
        data.update(inspection_achievement_rate)
        data.update(exception_handling_rate)
        data.update(get_electricity_consumption)
        return data

    def get_power_curve(self, number=None):
        ep_data_map = {}
        power_curve = {"working_day": [], "rest_day": [], "today": []}
        if number:
            number = number.split("-")[-1]
        total_lines_df_data = pd.Series(dtype=float)
        monitor_db = MonitorOAConnection()

        all_day = []
        td_bf_week = datetime.date.today() - relativedelta(weeks=+1)
        dy = td_bf_week
        td = datetime.date.today()
        while dy <= td:
            all_day.append(dy)
            dy += relativedelta(days=+1)
        str_all_day = list(map(lambda x: '"' + str(x) + '"', all_day))
        sql = """
            select
            distinct line as `line`
            from tb_%s
            where left(tm,10) in (%s)
            order by `line`
            """ % (number, ",".join(str_all_day))
        try:
            lines_query = monitor_db.query(sql)
        except Exception as e:
            logger.debug(e)
            return {}
        for i in lines_query:
            ep_data_map.setdefault(i["line"], [])

        sql = """
        SELECT `line`, ep, tm
        FROM tb_%s
        WHERE
        left(tm,10) in (%s)
        ORDER BY `line`, tm
        """ % (number, ",".join(str_all_day))
        week_data = monitor_db.query(sql)
        if not week_data:
            return power_curve
        for i in week_data:
            i["ep"] = float(i["ep"])
            ep_data_map[i["line"]].append(i)

        for i in lines_query:
            if ep_data_map[i["line"]]:
                line_week_df_data = pd.DataFrame(ep_data_map[i["line"]])
                line_week_df_data.set_index("tm", inplace=True)
                line_week_ep_raw = line_week_df_data.ep.copy()
                line_week_hour_ele = get_hour_ele(line_week_ep_raw)
                total_lines_df_data = total_lines_df_data.append(
                    line_week_hour_ele)
        if not total_lines_df_data.empty:
            all_df_data = total_lines_df_data.resample("H").sum()

            before_week = (pd.Timestamp.now() -
                           pd.Timedelta(days=7)).strftime("%Y-%m-%d")
            t_day = pd.Timestamp.now().day

            all_time_idx = pd.date_range(start=before_week,
                                         freq="H",
                                         periods=192,
                                         closed="left")

            all_df_data = all_df_data.reindex(all_time_idx).fillna(0.0)
            today_df_data = all_df_data.loc[all_df_data.index.day.isin([t_day
                                                                        ])]
            week_df_data = all_df_data.loc[~all_df_data.index.day.isin([t_day]
                                                                       )]

            weekday = week_df_data.loc[week_df_data.index.weekday.isin(
                range(0, 5))]
            weekend = week_df_data.loc[week_df_data.index.weekday.isin(
                range(5, 7))]

            power_curve["today"] = today_df_data.groupby(
                today_df_data.index.hour).mean().to_list()
            power_curve["working_day"] = weekday.groupby(
                weekday.index.hour).mean().to_list()
            power_curve["rest_day"] = weekend.groupby(
                weekend.index.hour).mean().to_list()
            to_day = today_df_data.groupby(
                today_df_data.index.hour).mean().to_list()
            index = datetime.datetime.now().hour
            power_curve["today"] = to_day[:index]
            power_curve["today"].extend([None for _ in range(index, 24)])
            return power_curve

    def get_new_monitor_alarm(self):
        sql = """
        SELECT
            b.customer_id,
            c.name as customer_name,
            b.name as point_name,
            d.imei as device_id,
            d.manufacturer as device_type,
            d.status as device_status,
            a.type as alarm_type,
            a.level as alarm_level,
            a.name as alarm_value,
            a.duration as alarm_times,
            a.tm start_time,
            a.updated_time last_time
            
        FROM monitor_alarm a
        LEFT JOIN monitor_points b
        ON a.monitor_point_id=b.id
        left join customer c
        on c.id=b.customer_id
        left join monitor_device d
        on d.monitor_point_id=b.id
        where service_enterprise_id=1000
        """
        return base_db.query(sql)

    def get_monitor_alarm(self):
        nw = datetime.datetime.now()
        where = [
            "A.status=0",
            "A.start_time>='%s'" %
            (nw - datetime.timedelta(hours=nw.hour,
                                     minutes=nw.minute,
                                     seconds=nw.second,
                                     microseconds=nw.microsecond))
        ]
        order_by = " ORDER BY A.times, A.start_time DESC, C.name"
        sql = """
        SELECT
            C.id as customer_id,
            C.name as customer_name,
            B.name as point_name,
            B.sn as device_id,
            B.type as device_type,
            B.status as device_status,
            A.type as alarm_type,
            A.level as alarm_level,
            A.value as alarm_value,
            A.times as alarm_times,
            A.start_time,
            A.last_time,
            TIMESTAMPDIFF(SECOND,A.start_time,
            IFNULL(A.last_time, NOW())) as duration
        FROM im_alert_list A
        LEFT JOIN im_device B
        ON A.device_id=B.id
        LEFT JOIN (SELECT
                    A.id as customer_id,
                    B.name, B.id FROM im_customer A
                    LEFT JOIN crm_customer B
                    ON B.id=A.customer_id) C
        ON B.im_customer_id=C.customer_id
        """
        if where:
            sql = ' WHERE '.join([sql, ' and '.join(where)])
        sql += order_by
        rst = power_oa.query(sql)
        return rst

    def get_number_monitor_alarm(self, number_id=None):
        where = ["A.status=0"]
        if number_id:
            where.append("C.id=%s" % number_id)
        order_by = " ORDER BY A.times, A.start_time DESC, C.name"
        sql = """
        SELECT
            C.id as customer_id,
            C.name as customer_name,
            B.name as point_name,
            B.sn as device_id,
            B.type as device_type,
            B.status as device_status,
            A.type as alarm_type,
            A.level as alarm_level,
            A.value as alarm_value,
            A.times as alarm_times,
            A.start_time,
            A.last_time,
            TIMESTAMPDIFF(SECOND,A.start_time,
            IFNULL(A.last_time, NOW())) as duration
        FROM im_alert_list A
        LEFT JOIN im_device B
        ON A.device_id=B.id
        LEFT JOIN (SELECT
                    A.id as customer_id,
                    B.name, B.id FROM im_customer A
                    LEFT JOIN crm_customer B
                    ON B.id=A.customer_id) C
        ON B.im_customer_id=C.customer_id
        """
        if where:
            sql = ' WHERE '.join([sql, ' and '.join(where)])
        sql += order_by
        rst = power_oa.query(sql)
        return rst

    def get_real_data(self, **kwargs):
        if kwargs.get("real", 0) == ['1']:
            return self.get_real_data_true(**kwargs)
        where = ["1=1"]
        entids = []
        jet = None
        join_ents = kwargs.get("join_ents")
        if join_ents == ["1001"]:
            kwargs.pop("enterprise_id", None)
            jet = ["6"]
            join_ents = ["6"]
        if not kwargs.get("enterprise_id") and join_ents:
            if "1" in join_ents:
                entids = ["75", "76", "80", "52",
                          "49",
                          "6",
                          "29", "30", "36", "20", "54",
                          "23",
                          "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"]
                if "54" in join_ents:
                    entids = list(set(entids) ^ set(["29", "30", "36", "20", "54"]))
                if "52" in join_ents:
                    entids = list(set(entids) ^ set(["75", "76", "80", "52"]))
                if "6" in join_ents:
                    entids = list(set(entids) ^ set(["6"]))
                if "49" in join_ents:
                    entids = list(set(entids) ^ set(["49"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "70" in join_ents:
                    entids = list(set(entids) ^ set(["28", "70"]))
                if "26" in join_ents:
                    entids = list(set(entids) ^ set(["26"]))
                if "77" in join_ents:
                    entids = list(set(entids) ^ set(["77"]))
                if "23" in join_ents:
                    entids = list(set(entids) ^ set(["23"]))
                if "19" in join_ents:
                    entids = list(set(entids) ^ set(["19", "31", "32", "33", "34", "35", "37",
                                                     "43", "44", "45", "46", "47", "48", "53"]))
                if "41" in join_ents:
                    entids = list(set(entids) ^ set(["41", "39", "40", "42", "50"]))
                if "27" in join_ents:
                    entids = list(set(entids) ^ set(["27"]))
                if "78" in join_ents:
                    entids = list(set(entids) ^ set(["25", "78"]))
                if "24" in join_ents:
                    entids = list(set(entids) ^ set(["24"]))
                if entids:
                    where.extend(['enterprise_id not in (%s)' % ','.join(entids)])
            else:
                if "54" in join_ents:
                    entids.extend(["29", "30", "36", "20", "54"])
                if "52" in join_ents:
                    entids.extend(["75", "76", "80", "52"])
                if "6" in join_ents:
                    entids.extend(["6"])
                if "49" in join_ents:
                    entids.extend(["49"])
                if "23" in join_ents:
                    entids.extend(["23"])
                if "70" in join_ents:
                    entids.extend(["28", "70"])
                if "26" in join_ents:
                    entids.extend(["26"])
                if "77" in join_ents:
                    entids.extend(["77"])
                if "23" in join_ents:
                    entids.extend(["23"])
                if "19" in join_ents:
                    entids.extend(["19", "31", "32", "33", "34", "35", "37",
                                   "43", "44", "45", "46", "47", "48", "53"])
                if "41" in join_ents:
                    entids.extend(["41", "39", "40", "42", "50"])
                if "27" in join_ents:
                    entids.extend(["27"])
                if "78" in join_ents:
                    entids.extend(["25", "78"])
                if "24" in join_ents:
                    entids.extend(["24"])
                if entids:
                    where.extend(['enterprise_id in (%s)' % ','.join(entids)])
                else:
                    where.extend(['enterprise_id in (%s)' % ','.join(join_ents)])
        else:
            if kwargs.get("enterprise_id", ["0"]) != ["0"]:
                if kwargs.get("enterprise_id", ['1']) == ['1']:
                    where.extend(['enterprise_id not in %s' % settings.BRANCH_ENTIDS])
                elif kwargs.get("enterprise_id", ['1']) == ['54']:
                    where.extend(['enterprise_id in (29,30,36,20,54)'])
                elif kwargs.get("enterprise_id", ['1']) == ['52']:
                    where.extend(['enterprise_id in (75,76,80,52)'])
                elif kwargs.get("enterprise_id", ['1']) == ['70']:
                    where.extend(['enterprise_id in (28,70)'])
                elif kwargs.get("enterprise_id", ['1']) == ['19']:
                    where.extend(['enterprise_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53)'])
                elif kwargs.get("enterprise_id", ['1']) == ['41']:
                    where.extend(['enterprise_id in (41,39,40,42,50)'])
                elif kwargs.get("enterprise_id", ['1']) == ['78']:
                    where.extend(['enterprise_id in (25,78)'])
                else:
                    where.extend(
                        base_db.filter(
                            kwargs.get("enterprise_id"),
                            value='enterprise_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='A.province="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='A.city="{}"'))
        where.extend(base_db.filter(kwargs.get("start_time"), value='A.tm>="{}"'))
        where.extend(base_db.filter(kwargs.get("ent_time"), value='A.tm<="{}"'))
        _sql = """
        select
            %s
        from oa_real_data A
        """
        if jet == ["1001"]:
            select = """
            sum(capacity)*2 capacity,
            sum(power)*2 power,
            sum(power)/sum(capacity)*100 load_rate,
            sum(ep) ep,
            sum(eq) eq,
            tm
            """
        else:
            select = """
            sum(capacity)*2 capacity,
            sum(power)*2 power,
            sum(power)/sum(capacity)*100 load_rate,
            sum(ep) ep,
            sum(eq) eq,
            tm
            """
        count_sql = None
        group_by = "A.tm"
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        group_by=group_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_real_data_true(self, **kwargs):
        enterprise_id = kwargs.get("enterprise_id")
        dt = {
            "capacity": 0,
            "power": 0,
            "load_rate": 0,
            "ep": 0,
            "eq": 0,
            "tm": None,
        }
        dst = []
        if isinstance(enterprise_id, list):
            enterprise_id = enterprise_id[0]
        if enterprise_id is None:
            enterprise_id = kwargs.get("join_ents")
            if enterprise_id == ["1001"]:
                s_dt = dashboard_redis_api.get("dashboard_real_data_%s" % str("6"))
                if s_dt:
                    js_dt = json.loads(s_dt)
                    dt["capacity"] += js_dt["capacity"]
                    dt["power"] += js_dt["power"]
                    dt["ep"] += js_dt["ep"]
                    dt["eq"] += js_dt["eq"]
                    dt["tm"] = js_dt["tm"]
                    dt["load_rate"] = dt["power"] / dt["capacity"] * 100
                    return [dt], None
            for i in enterprise_id:
                s_dt = dashboard_redis_api.get("dashboard_real_data_%s" % str(i))
                print(s_dt, "dashboard_real_data_%s" % str(i))
                if s_dt:
                    js_dt = json.loads(s_dt)
                    dt["capacity"] += js_dt["capacity"]
                    dt["power"] += js_dt["power"]
                    dt["ep"] += js_dt["ep"]
                    dt["eq"] += js_dt["eq"]
                    dt["tm"] = js_dt["tm"]
            if dt["capacity"] != 0:
                dt["load_rate"] = dt["power"] / dt["capacity"] * 100
            dst = [dt]
        else:
            dt = dashboard_redis_api.get(
                "dashboard_real_data_%s" % str(enterprise_id))
            if dt:
                dst = [json.loads(dt)]
        if dst:
            return dst, None
        where = ["1=1 and A.tm>=date_format(NOW(), '%Y-%m-%d') and C.capacity is not null"]
        if kwargs.get("enterprise_id", ["0"]) != ["0"]:
            if kwargs.get("enterprise_id", ['1']) == ['1']:
                where.extend(['E.org_id not in %s' % settings.BRANCH_ENTIDS])
            elif kwargs.get("enterprise_id", ['1']) == ['54']:
                where.extend(['E.org_id in (29,30,36,20,54)'])
            elif kwargs.get("enterprise_id", ['1']) == ['52']:
                where.extend(['E.org_id in (75,76,80,52)'])
            elif kwargs.get("enterprise_id", ['1']) == ['70']:
                where.extend(['E.org_id in (28,70)'])
            elif kwargs.get("enterprise_id", ['1']) == ['19']:
                where.extend(['E.org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53)'])
            elif kwargs.get("enterprise_id", ['1']) == ['41']:
                where.extend(['E.org_id in (41,39,40,42,50)'])
            elif kwargs.get("enterprise_id", ['1']) == ['78']:
                where.extend(['E.org_id in (25,78)'])
            else:
                where.extend(
                    base_db.filter(
                        kwargs.get("enterprise_id"),
                        value='E.org_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='E.province="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='E.city="{}"'))
        _sql = """
        select
            %s
        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
        """
        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
        """
        count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql)
        try:
            real_data = power_oa.query(sql)
            if real_data:
                return real_data, None
            return None, None
        except Exception as e:
            logger.debug(e)
            return None, None

    def get_month_ep(self, enterprise_id):
        dt = 0
        if enterprise_id is None:
            dt = dashboard_redis_api.get("dashboard_month_ep_")
        else:
            if isinstance(enterprise_id, list):
                if enterprise_id == ["1001"]:
                    return dashboard_redis_api.get("dashboard_month_ep_%s" % "6").decode()
                for i in enterprise_id:
                    s_dt = dashboard_redis_api.get("dashboard_month_ep_%s" % i)
                    if s_dt:
                        dt += float(s_dt.decode())
                    else:
                        return dt
            else:
                s_dt = dashboard_redis_api.get("dashboard_month_ep_%s" % enterprise_id)
                if s_dt:
                    dt = s_dt.decode()
                else:
                    dt = None
                    return dt
        if dt is not None:
            return dt
        where1 = ""
        where2 = ""
        if enterprise_id:
            if isinstance(enterprise_id, list):
                where1 = " and org_id not in (%s) " % ','.join(enterprise_id)
                where2 = " and enterprise_id not in (%s) " % ','.join(enterprise_id)
            elif str(enterprise_id) == "1":
                where1 = " and org_id not in %s " % settings.BRANCH_ENTIDS
                where2 = " and enterprise_id not in %s " % settings.BRANCH_ENTIDS
            elif str(enterprise_id) == '54':
                where1 = " and org_id in (29,30,36,20,54) "
                where2 = " and enterprise_id in (29,30,36,20,54) "
            elif str(enterprise_id) == '52':
                where1 = " and org_id in (75,76,80,52) "
                where2 = " and enterprise_id in (75,76,80,52) "
            elif str(enterprise_id) == '70':
                where1 = " and org_id in (28,70) "
                where2 = " and enterprise_id in (28,70) "
            elif str(enterprise_id) == '19':
                where1 = " and org_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) "
                where2 = " and enterprise_id in (19,31,32,33,34,35,37,43,44,45,46,47,48,53) "
            elif str(enterprise_id) == '41':
                where1 = " and org_id in (41,39,40,42,50) "
                where2 = " and enterprise_id in (41,39,40,42,50) "
            elif str(enterprise_id) == '78':
                where1 = " and org_id in (25,78) "
                where2 = " and enterprise_id in (25,78) "
            else:
                where1 = " and org_id=%s " % enterprise_id
                where2 = " and enterprise_id=%s " % enterprise_id
        _sql1 = """
        select sum(ep) max_ep from (
        select
        sum(ifnull(A.ep,0)) ep,
        E.org_id enterprise_id, E.province, E.city,
        concat(date_format(DATE_ADD(now(),Interval 8 hour),
        '%Y-%m-%d %H:%i:'),'00') tm
        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     
        """.format(where1)
        rst1 = power_oa.query(_sql1)[0]["max_ep"]
        _sql2 = """
        select sum(ep) min_ep
        from (
        select ep 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
        """.format(where2)
        rst2 = base_db.query(_sql2)[0]["min_ep"]
        return round(abs(rst1 - rst2), 2) / 15

    def get_number_psum(self, number):
        sql = """
            select sum(A.psum) psum from tb_%s A
            right join
            (select
            max(tm) tm,
            line
            from tb_%s
            group by line) B
            on A.line=B.line and A.tm=B.tm
            """ % (number, number)
        try:
            psum = monitor_db.query(sql)
            return psum[0]["psum"]
        except Exception as e:
            logger.debug(e)
            return None

    def get_ep_px(self, number):
        nw = datetime.datetime.now()
        year = nw.year
        d = cal.monthrange(year, nw.month)
        sql = """
        select
        sum(ep) ep,
        sum(px) px
        from(
            select max(ep)-min(ep)
            ep, max(px) `px`
            from tb_%s A
            where tm >="%s"
            and tm <="%s"
            group by A.line) B
        """ % (number, "%d-%d-%d" % (year, nw.month, 1), "%d-%d-%d" %
               (year, nw.month, d[1]))
        try:
            rst = monitor_db.query(sql)[0]
            return rst
        except Exception:
            return None

    def get_number_detail(self, number_id):
        dt = {"customer": {}}
        customer = statistics_repo.get_customer_number(number_id)
        if not customer:
            return dt
        ### mock 数据
        if int(number_id) in settings.MOCK_NUMBER:
            dt["customer"] = customer[0]
            if not dt["customer"]["transformer_capacity"]:
                psum = 1029
            else:
                psum = dt["customer"]["transformer_capacity"] * 0.217
            dt["customer"].update({
                "psum": psum, "rate": 0.217, "ep": psum + 1063.3,
                "px": 3200, "month_ep": 283445})
            return dt
        ### mock 数据
        customer = customer[0]
        customer_id = customer.get("customer_id", None)
        if not customer_id:
            return dt
        number = customer["number"]
        psum = self.get_number_psum(number)
        ep_px = self.get_ep_px(number)
        if ep_px:
            ep = ep_px["ep"]
            px = ep_px["px"]
        else:
            ep = None
            px = None
        dt["customer"] = customer
        alarm = self.get_number_monitor_alarm(customer_id)
        try:
            rate = int(psum) / int(dt["customer"]["transformer_capacity"])
        except Exception:
            rate = None
        try:
            ep = float(ep)
        except Exception:
            ep = None
        dt["customer"].update({"alarm": alarm})
        dt["customer"].update({"psum": psum, "rate": rate, "ep": ep / 1000 if ep else None, "px": px})
        return dt

    def get_360_videos(self):
        dt = VideosModel.objects.filter(name="360").first()
        session = requests.Session()
        adapter = requests.adapters.HTTPAdapter(pool_connections=10,
                                                pool_maxsize=100,
                                                max_retries=3)
        session.mount("http://", adapter)
        session.mount("https://", adapter)
        self.default_json_headers = {
            "User-Agent":
                "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 \
                        (KHTML, like Gecko) Chrome/84.0.4147.135 Safari/537.36",
            "Content-Type": "application/json;charset=UTF-8",
            "Connection": "close"
        }
        cookies = json.loads(dt.token)
        cookie_jar = RequestsCookieJar()
        for k, i in cookies.items():
            cookie_jar.set(name=k, value=i)
        session.post(url="https://my.jia.360.cn/web/login?from=mpc_ipcam_web&"
                         "taskid=160621180386716",
                     headers=self.default_json_headers,
                     verify=False,
                     cookies=cookie_jar)
        data = session.get(
            url="https://my.jia.360.cn/app/playV2?taskid=1606213479413&"
                "from=mpc_ipcam_web&sn=360B1865333&mode=0",
            headers=self.default_json_headers,
            verify=False,
            cookies=cookie_jar)
        a = data.json()
        session.close()
        return a

    def get_station_video(self, station_id):
        video_list = []
        _sql = """
        select
            a.id device_id,
            a.name,
            a.deviceSerial,
            b.app_key,
            b.app_secret,
            b.home_url,
            b.system,
            b.access_token
        from device a
        left join video_system b
        on a.system_id=b.id
        where station_id=%s
        """ % station_id
        rst = base_db.query(_sql)

        for i in rst:
            if i["device_id"]:
                dt = {
                    "id": i["device_id"],
                    "access_token": i["access_token"],
                    "system": i["system"],
                    "home_url": i["home_url"],
                    "app_secret": i["app_secret"],
                    "app_key": i["app_key"],
                }
                instance = VideoSystem(**dt)
                client = video_client.map(i["system"], instance)
                dt = client.get_video_url(deviceSerial=i["deviceSerial"])
                for j in dt:
                    j["name"] = i["name"]
                    j["deviceId"] = i["device_id"]
                    j["deviceSerial"] = i["deviceSerial"]
                video_list.extend(dt)
        return video_list


power_oa_service = PowerOAService()
statistics_service = StatisticsService()


def get_all_user_history_last_location():
    user_ids = UserLocation.objects.values_list("user_id",
                                                flat=True).distinct()
    result = []
    for user_id in user_ids:
        last_location = UserLocation.objects.filter(
            user_id=user_id).order_by("-created_time").first()
        location = last_location.location
        location = location.strip("(").strip(")").split(",")
        longitude = location[0]
        latitude = location[1]
        user = Users.objects.filter(id=user_id).first()
        team = Team.objects.filter(Q(team_headman=user) | Q(member=user),
                                   deleted=False).first()
        result.append({
            "user_id": str(user_id),
            "name": user.nickname,
            "phone": user.phone,
            "longitude": longitude,
            "latitude": latitude,
            "group": team.name if team else None,
            "group_type": team.type if team else None,
            "rate": None
        })
    return result


def get_all_user_recent_location(enterprise_id):
    # user_ids = UserEnterprise.objects.filter(enterprise_id__id__in=enterprise_id).values_list("user_id",
    #                                                                                           flat=True).distinct()
    #
    # user_ids = UserLocation.objects.filter(user_id__in=user_ids).values_list("user_id",
    #                                                                          flat=True).distinct()
    conn = BaseConnection()
    sql = """
    SELECT DISTINCT user_id from user_location where user_id in (
        SELECT a.user_id from user_enterprise a
        LEFT JOIN enterprise b on a.enterprise_id=b.enterprise_id where b.id in ({}))""".format(",".join(enterprise_id))
    user_ids = conn.query(sql)
    user_ids = [str(item.get("user_id")) for item in user_ids]
    redis = redis_api_base.connect()
    redis = redis.redis
    data = redis.hgetall("user_location")
    result = []
    result_data = {}
    for k, v in data.items():
        value = demjson.decode(v)
        location = value.get("location").strip("(").strip(")").split(",")
        longitude = location[0]
        latitude = location[1]
        result.append({
            "user_id": k.decode("utf-8"),
            "name": value.get("nickname"),
            "phone": value.get("phone"),
            "enterprise": value.get("enterprise"),
            "longitude": longitude,
            "latitude": latitude,
            "group": value.get("team_name"),
            "type": value.get("team_type"),
            "rate": None
        })
    result_data["recent_location"] = result

    sql = """
select a.id user_id,a.nickname,a.phone,b.name,b.type,SUBSTR(substring_index(c.location,",",1),2) longitude,
           substring_index(substring_index(c.location,",",-1),")",1) latitude, GROUP_CONCAT(DISTINCT e.id) enterprise from users a
            left join team b on a.id=b.team_headman_id
            left join user_location c on a.id=c.user_id 
LEFT JOIN user_enterprise d on a.id=d.user_id
left join enterprise e on d.enterprise_id=e.enterprise_id
where a.id in 
            ({})  and b.type in (2,3) and a.nickname is not null
 GROUP BY a.id
            """.format(",".join(user_ids))
    data = conn.query(sql) if user_ids else []

    result_data["history_location"] = data
    return result_data


def get_economic_score(**kwargs):
    conn = ScoreConnection()
    now = datetime.datetime.now()
    year = now.year
    last_year = year
    this_month = now.month
    last_month = this_month - 1
    if last_month < 1:
        last_month = 12
        last_year -= 1
    sql = """
        SELECT case when industry="nan" then "default"
    when industry="None"  then  "default"
    when industry="-"  then  "default"
    else left(industry,1)
    end industry
       from eval_report_score where sn={}
        """.format(kwargs.get("power_number"))
    # 获取行业
    industry = conn.query(sql)

    sql = """
       select ifnull(score1_1,0.8)/100*100 score1_1,ifnull(score1_1_4_basic_ratio,0)/20*100 score1_1_4_basic_ratio,
       ifnull(score1_1_4_power_factor,0.8)/20*100 score1_1_4_power_factor,
       ifnull(score1_1_4_peak_valley_ratio,0.8)/20*100 score1_1_4_peak_valley_ratio,
       ifnull(score1_1_4_unit_fee,0.8)/20*100 score1_1_4_unit_fee,
       ifnull(score1_1_4_max_utilize,0.8)/20*100 score1_1_4_max_utilize
       from eval_industrial_score where industry= "{}" and `type`="avg" and `month`="{}-{}"
       """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))
    # 获取行业平均分
    avg_score = conn.query(sql)

    sql = """
           select ifnull(score1_1,0.8)/100*100 score1_1,ifnull(score1_1_4_basic_ratio,0)/20*100 score1_1_4_basic_ratio,
           ifnull(score1_1_4_power_factor,0.8)/20*100 score1_1_4_power_factor,
           ifnull(score1_1_4_peak_valley_ratio,0.8)/20*100 score1_1_4_peak_valley_ratio,
           ifnull(score1_1_4_unit_fee,0.8)/20*100 score1_1_4_unit_fee,
           ifnull(score1_1_4_max_utilize,0.8)/20*100 score1_1_4_max_utilize
        from eval_industrial_score where industry="{}" and `type`="avg" and `month`="{}-{}"
           """.format(industry[0].get("industry") if industry else "null", last_year, str(last_month).zfill(2))

    avg_score2 = conn.query(sql)

    sql = """
    select ifnull(score1_1,{})/100*100 score1_1,
    ifnull(score1_1_4_basic_ratio,{})/20*100 score1_1_4_basic_ratio,
    ifnull(score1_1_4_power_factor,{})/20*100 score1_1_4_power_factor,
    ifnull(score1_1_4_peak_valley_ratio,{})/20*100 score1_1_4_peak_valley_ratio,
    ifnull(score1_1_4_unit_fee,{})/20*100 score1_1_4_unit_fee,
    ifnull(score1_1_4_max_utilize,{})/20*100 score1_1_4_max_utilize
    ,comment1_1_4,suggestion1_1_4 from eval_report_score where `month`="{}-{}" and sn="{}"
    """.format(avg_score[0].get("score1_1") if avg_score else 0,
               avg_score[0].get("score1_1_4_basic_ratio") if avg_score else 0,
               avg_score[0].get("score1_1_4_power_factor") if avg_score else 0,
               avg_score[0].get("score1_1_4_peak_valley_ratio") if avg_score else 0,
               avg_score[0].get("score1_1_4_unit_fee") if avg_score else 0,
               avg_score[0].get("score1_1_4_max_utilize") if avg_score else 0,
               year, str(this_month).zfill(2), kwargs.get("power_number"))
    # 获取当月评分
    this_month_score = conn.query(sql)
    sql = """
        select ifnull(score1_1,{})/100*100 score1_1,
        ifnull(score1_1_4_basic_ratio,{})/20*100 score1_1_4_basic_ratio,
        ifnull(score1_1_4_power_factor,{})/20*100 score1_1_4_power_factor,
        ifnull(score1_1_4_peak_valley_ratio,{})/20*100 score1_1_4_peak_valley_ratio,
        ifnull(score1_1_4_unit_fee,{})/20*100 score1_1_4_unit_fee,
        ifnull(score1_1_4_max_utilize,{})/20*100 score1_1_4_max_utilize
        ,comment1_1_4,suggestion1_1_4 from eval_report_score where `month`="{}-{}" and sn={}
        """.format(avg_score2[0].get("score1_1") if avg_score2 else 0,
                   avg_score2[0].get("score1_1_4_basic_ratio") if avg_score2 else 0,
                   avg_score2[0].get("score1_1_4_power_factor") if avg_score2 else 0,
                   avg_score2[0].get("score1_1_4_peak_valley_ratio") if avg_score2 else 0,
                   avg_score2[0].get("score1_1_4_unit_fee") if avg_score2 else 0,
                   avg_score2[0].get("score1_1_4_max_utilize") if avg_score2 else 0,
                   last_year, str(last_month).zfill(2), kwargs.get("power_number"))
    # 获取上月评分
    last_month_score = conn.query(sql)

    sql = """ 
    select ifnull(score1_1,0.8)/100*100 score1_1,ifnull(score1_1_4_basic_ratio,0)/20*100 score1_1_4_basic_ratio,
    ifnull(score1_1_4_power_factor,0.8)/20*100 score1_1_4_power_factor,
    ifnull(score1_1_4_peak_valley_ratio,0.8)/20*100 score1_1_4_peak_valley_ratio,
    ifnull(score1_1_4_unit_fee,0.8)/20*100 score1_1_4_unit_fee,
    ifnull(score1_1_4_max_utilize,0.8)/20*100 score1_1_4_max_utilize
     from eval_industrial_score where industry="{}" and `type`="std" and `month`="{}-{}"
    """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))

    std_score = conn.query(sql)
    if this_month_score:
        this_month_score[0]["suggestion1_1_4"] = "  ".join(json.loads(
            this_month_score[0]["suggestion1_1_4"].replace("'", '"'))) if this_month_score[0][
            "suggestion1_1_4"] else None
    if last_month_score:
        last_month_score[0]["suggestion1_1_4"] = "  ".join(json.loads(
            last_month_score[0]["suggestion1_1_4"].replace("'", '"'))) if last_month_score[0][
            "suggestion1_1_4"] else None
    return {
        "this_month_score": this_month_score[0] if this_month_score else {
            "score1_1": 0,
            "score1_1_4_basic_ratio": 0,
            "score1_1_4_power_factor": 0,
            "score1_1_4_peak_valley_ratio": 0,
            "score1_1_4_unit_fee": 0,
            "score1_1_4_max_utilize": 0,
            "comment1_1_4": None,
            "suggestion1_1_4": None
        },
        "last_month_score": last_month_score[0] if last_month_score else {
            "score1_1": 0,
            "score1_1_4_basic_ratio": 0,
            "score1_1_4_power_factor": 0,
            "score1_1_4_peak_valley_ratio": 0,
            "score1_1_4_unit_fee": 0,
            "score1_1_4_max_utilize": 0,
            "comment1_1_4": None,
            "suggestion1_1_4": None
        },
        "std_score": std_score[0] if std_score else {
            "score1_1": 0,
            "score1_1_4_basic_ratio": 0,
            "score1_1_4_power_factor": 0,
            "score1_1_4_peak_valley_ratio": 0,
            "score1_1_4_unit_fee": 0,
            "score1_1_4_max_utilize": 0
        },
        "avg_score": avg_score[0] if avg_score else {
            "score1_1": 0,
            "score1_1_4_basic_ratio": 0,
            "score1_1_4_power_factor": 0,
            "score1_1_4_peak_valley_ratio": 0,
            "score1_1_4_unit_fee": 0,
            "score1_1_4_max_utilize": 0,

        }
    }


def get_security_score(**kwargs):
    conn = ScoreConnection()
    now = datetime.datetime.now()
    year = now.year
    last_year = year
    this_month = now.month
    last_month = this_month - 1
    if last_month < 1:
        last_month = 12
        last_year -= 1
    sql = """
        SELECT case when industry="nan" then "default"
    when industry="None"  then  "default"
    when industry="-"  then  "default"
    else left(industry,1)
    end industry
       from eval_report_score where sn={}
        """.format(kwargs.get("power_number"))
    # 获取行业
    industry = conn.query(sql)

    sql = """
       select ifnull(score2_1,0.8)/100*100 score2_1,
       ifnull(score2_1_surrounding,0)/10*100 score2_1_surrounding,
       ifnull(score2_1_facility,0.8)/30*100 score2_1_facility,
       ifnull(score2_1_equipment,0.8)/12*100 score2_1_equipment,
       ifnull(score2_1_device,0.8)/48*100 score2_1_device
      
       from eval_industrial_score where industry= "{}" and `type`="avg" and `month`="{}-{}"
       """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))
    # 获取行业平均分
    avg_score = conn.query(sql)

    sql = """
           select ifnull(score2_1,0.8)/100*100 score2_1,
       ifnull(score2_1_surrounding,0)/10*100 score2_1_surrounding,
       ifnull(score2_1_facility,0.8)/30*100 score2_1_facility,
       ifnull(score2_1_equipment,0.8)/12*100 score2_1_equipment,
       ifnull(score2_1_device,0.8)/48*100 score2_1_device
        from eval_industrial_score where industry="{}" and `type`="avg" and `month`="{}-{}"
           """.format(industry[0].get("industry") if industry else "null", last_year, str(last_month).zfill(2))

    avg_score2 = conn.query(sql)

    sql = """
    select ifnull(score2_1,{})/100*100 score2_1,
       ifnull(score2_1_surrounding,{})/10*100 score2_1_surrounding,
       ifnull(score2_1_facility,{})/30*100 score2_1_facility,
       ifnull(score2_1_equipment,{})/12*100 score2_1_equipment,
       ifnull(score2_1_device,{})/20*100 score2_1_device
    ,comment2_1,suggestion2_1 from eval_report_score where `month`="{}-{}" and sn="{}"
    """.format(avg_score[0].get("score2_1") if avg_score else 0,
               avg_score[0].get("score2_1_surrounding") if avg_score else 0,
               avg_score[0].get("score2_1_facility") if avg_score else 0,
               avg_score[0].get("score2_1_equipment") if avg_score else 0,
               avg_score[0].get("score2_1_device") if avg_score else 0,

               year, str(this_month).zfill(2), kwargs.get("power_number"))
    # 获取当月评分
    this_month_score = conn.query(sql)
    sql = """
     select ifnull(score2_1,{})/100*100 score2_1,
        ifnull(score2_1_surrounding,{})/10*100 score2_1_surrounding,
        ifnull(score2_1_facility,{})/30*100 score2_1_facility,
        ifnull(score2_1_equipment,{})/12*100 score2_1_equipment,
        ifnull(score2_1_device,{})/48*100 score2_1_device
     ,comment2_1,suggestion2_1 from eval_report_score where `month`="{}-{}" and sn="{}"
     """.format(avg_score2[0].get("score2_1") if avg_score2 else 0,
                avg_score2[0].get("score2_1_surrounding") if avg_score2 else 0,
                avg_score2[0].get("score2_1_facility") if avg_score2 else 0,
                avg_score2[0].get("score2_1_equipment") if avg_score2 else 0,
                avg_score2[0].get("score2_1_device") if avg_score2 else 0,

                last_year, str(last_month).zfill(2), kwargs.get("power_number"))
    # 获取上月评分
    last_month_score = conn.query(sql)

    sql = """ 
     select ifnull(score2_1,0.8)/100*100 score2_1,
       ifnull(score2_1_surrounding,0)/10*100 score2_1_surrounding,
       ifnull(score2_1_facility,0.8)/30*100 score2_1_facility,
       ifnull(score2_1_equipment,0.8)/12*100 score2_1_equipment,
       ifnull(score2_1_device,0.8)/48*100 score2_1_device
     from eval_industrial_score where industry="{}" and `type`="std" and `month`="{}-{}"
    """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))

    std_score = conn.query(sql)

    return {
        "this_month_score": this_month_score[0] if this_month_score else {
            "score2_1": 0,
            "score2_1_surrounding": 0,
            "score2_1_facility": 0,
            "score2_1_equipment": 0,
            "score2_1_device": 0,
            "comment2_1": None,
            "suggestion2_1": None
        },
        "last_month_score": last_month_score[0] if last_month_score else {
            "score2_1": 0,
            "score2_1_surrounding": 0,
            "score2_1_facility": 0,
            "score2_1_equipment": 0,
            "score2_1_device": 0,
            "comment2_1": None,
            "suggestion2_1": None
        },
        "std_score": std_score[0] if std_score else {
            "score2_1": 0,
            "score2_1_surrounding": 0,
            "score2_1_facility": 0,
            "score2_1_equipment": 0,
            "score2_1_device": 0,
            "comment2_1": None,
            "suggestion2_1": None
        },
        "avg_score": avg_score[0] if avg_score else {
            "score2_1": 0,
            "score2_1_surrounding": 0,
            "score2_1_facility": 0,
            "score2_1_equipment": 0,
            "score2_1_device": 0,
            "comment2_1": None,
            "suggestion2_1": None
        }
    }


def get_quality_score(**kwargs):
    conn = ScoreConnection()
    now = datetime.datetime.now()
    year = now.year
    last_year = year
    this_month = now.month
    last_month = this_month - 1
    if last_month < 1:
        last_month = 12
        last_year -= 1
    sql = """
        SELECT case when industry="nan" then "default"
    when industry="None"  then  "default"
    when industry="-"  then  "default"
    else left(industry,1)
    end industry
       from eval_report_score where sn={}
        """.format(kwargs.get("power_number"))
    # 获取行业
    industry = conn.query(sql)

    sql = """
       select ifnull(score2_2,0.8)/100*100 score2_2,
       ifnull(score2_2_voltage,0)/25*100 score2_2_voltage,
       ifnull(score2_2_load_rate,0.8)/25*100 score2_2_load_rate,
       ifnull(score2_2_lvur,0.8)/20*100 score2_2_lvur,
       ifnull(score2_2_thu,0.8)/15*100 score2_2_thu,
       ifnull(score2_2_power_factor,0.8)/15*100 score2_2_power_factor
       from eval_industrial_score where industry= "{}" and `type`="avg" and `month`="{}-{}"
       """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))
    # 获取行业平均分
    avg_score = conn.query(sql)

    sql = """
            select ifnull(score2_2,0.8)/100*100 score2_2,
       ifnull(score2_2_voltage,0.8)/25*100 score2_2_voltage,
       ifnull(score2_2_load_rate,0.8)/15*100 score2_2_load_rate,
       ifnull(score2_2_lvur,0.8)/25*100 score2_2_lvur,
       ifnull(score2_2_thu,0.8)/20*100 score2_2_thu,
       ifnull(score2_2_power_factor,0.8)/15*100 score2_2_power_factor
        from eval_industrial_score where industry="{}" and `type`="avg" and `month`="{}-{}"
           """.format(industry[0].get("industry") if industry else "null", last_year, str(last_month).zfill(2))

    avg_score2 = conn.query(sql)

    sql = """
     select ifnull(score2_2,{})/100*100 score2_2,
       ifnull(score2_2_voltage,{})/25*100 score2_2_voltage,
       ifnull(score2_2_load_rate,{})/15*100 score2_2_load_rate,
       ifnull(score2_2_lvur,{})/25*100 score2_2_lvur,
       ifnull(score2_2_thu,{})/20*100 score2_2_thu,
       ifnull(score2_2_power_factor,{})/15*100 score2_2_power_factor
    ,comment2_2,suggestion2_2 from eval_report_score where `month`="{}-{}" and sn="{}"
    """.format(avg_score[0].get("score2_2") if avg_score else 0,
               avg_score[0].get("score2_2_voltage") if avg_score else 0,
               avg_score[0].get("score2_2_load_rate") if avg_score else 0,
               avg_score[0].get("score2_2_lvur") if avg_score else 0,
               avg_score[0].get("score2_2_thu") if avg_score else 0,
               avg_score[0].get("score2_2_power_factor") if avg_score else 0,
               year, str(this_month).zfill(2), kwargs.get("power_number"))
    # 获取当月评分
    this_month_score = conn.query(sql)
    sql = """
         select ifnull(score2_2,{})/100*100 score2_2,
           ifnull(score2_2_voltage,{})/25*100 score2_2_voltage,
           ifnull(score2_2_load_rate,{})/15*100 score2_2_load_rate,
           ifnull(score2_2_lvur,{})/25*100 score2_2_lvur,
           ifnull(score2_2_thu,{})/20*100 score2_2_thu,
           ifnull(score2_2_power_factor,{})/15*100 score2_2_power_factor
        ,comment2_2,suggestion2_2 from eval_report_score where `month`="{}-{}" and sn="{}"
        """.format(avg_score2[0].get("score2_2") if avg_score2 else 0,
                   avg_score2[0].get("score2_2_voltage") if avg_score2 else 0,
                   avg_score2[0].get("score2_2_load_rate") if avg_score2 else 0,
                   avg_score2[0].get("score2_2_lvur") if avg_score2 else 0,
                   avg_score2[0].get("score2_2_thu") if avg_score2 else 0,
                   avg_score2[0].get("score2_2_power_factor") if avg_score2 else 0,
                   last_year, str(last_month).zfill(2), kwargs.get("power_number"))
    # 获取上月评分
    last_month_score = conn.query(sql)

    sql = """ 
   select ifnull(score2_2,0.8)/100*100 score2_2,
       ifnull(score2_2_voltage,0.8)/25*100 score2_2_voltage,
       ifnull(score2_2_load_rate,0.8)/15*100 score2_2_load_rate,
       ifnull(score2_2_lvur,0.8)/25*100 score2_2_lvur,
       ifnull(score2_2_thu,0.8)/20*100 score2_2_thu,
       ifnull(score2_2_power_factor,0.8)/15*100 score2_2_power_factor
     from eval_industrial_score where industry="{}" and `type`="std" and `month`="{}-{}"
    """.format(industry[0].get("industry") if industry else "null", year, str(this_month).zfill(2))

    std_score = conn.query(sql)
    return {
        "this_month_score": this_month_score[0] if this_month_score else {
            "score2_2": 0,
            "score2_2_voltage": 0,
            "score2_2_load_rate": 0,
            "score2_2_lvur": 0,
            "score2_2_thu": 0,
            "score2_2_power_factor": 0,
            "comment2_2": None,
            "suggestion2_2": None

        },
        "last_month_score": last_month_score[0] if last_month_score else {
            "score2_2": 0,
            "score2_2_voltage": 0,
            "score2_2_load_rate": 0,
            "score2_2_lvur": 0,
            "score2_2_thu": 0,
            "score2_2_power_factor": 0,
            "comment2_2": None,
            "suggestion2_2": None

        },
        "std_score": std_score[0] if std_score else {
            "score2_2": 0,
            "score2_2_voltage": 0,
            "score2_2_load_rate": 0,
            "score2_2_lvur": 0,
            "score2_2_thu": 0,
            "score2_2_power_factor": 0,
            "comment2_2": None,
            "suggestion2_2": None

        },
        "avg_score": avg_score[0] if avg_score else {
            "score2_2": 0,
            "score2_2_voltage": 0,
            "score2_2_load_rate": 0,
            "score2_2_lvur": 0,
            "score2_2_thu": 0,
            "score2_2_power_factor": 0,
            "comment2_2": None,
            "suggestion2_2": None
        }
    }
