import logging
import re
import pandas as pd
import datetime
import time
import ujson
import hashlib
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed
from addict import Dict

from dateutil.relativedelta import relativedelta
from common.utils.epkeeperlib.ep_fix import get_hour_ele
from django.db.utils import ProgrammingError

from common.utils.models import (BaseConnection, MonitorOAConnection,
                                 PowerOAConnection)
from common.utils.exceptions import ValidationError
from common.utils.redis_api import dashboard_redis_api
from common.utils.ele_handle import get_ep_by_hour
from common.notify.message import client as message_client

from console.customer.apply_func import json_loads_f, split_f

from console.monitor.models import (MonitorDevices, MonitorPoints,
                                    MonitorMetaData, MonitorTemplates,
                                    MonitorDevicesRecord, MonitorMetaDataForTest,
                                    MonitorAlarm, MonitorRealData)
from console.electrical_info.models import ElectricalInfromation
from console.powerstation.models import PowerStation

base_db = BaseConnection()
base_mo_db = BaseConnection(db_alias="monitor")
moa_db = MonitorOAConnection()
oa_db = PowerOAConnection()
logger = logging.getLogger('')
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;
"""


class MonitorQuerySet(object):
    @property
    def monitor_devices_lc(self):
        return MonitorDevices.objects.filter(deleted=False)

    @property
    def monitor_pointers_l(self):
        return MonitorPoints.objects.filter(
            deleted=False).prefetch_related("ptdv")

    @property
    def monitor_templates_l(self):
        return MonitorTemplates.objects.filter(deleted=False)


class MonitorRepo(object):
    def get_templates(self):
        return MonitorTemplates.objects.filter(deleted=False)

    def get_template(self, id):
        return MonitorTemplates.objects.filter(deleted=False, id=id).first()

    def create_templates(self, **kwargs):
        return MonitorTemplates.objects.create(**kwargs)

    def update_template(self, **kwargs):
        id = kwargs.pop("id")
        MonitorTemplates.objects.filter(id=id).update(**kwargs)
        return MonitorTemplates.objects.filter(deleted=False, id=id).first()

    def delete_template(self, id):
        if MonitorPoints.objects.filter(template_id=id):
            raise ValidationError("该模板已被使用，无法删除")
        MonitorTemplates.objects.filter(deleted=False, id=id).delete()

    def get_monitor_devices(self):
        return MonitorDevices.objects.filter(deleted=False)

    def get_monitor_device(self, device_id):
        return self.get_monitor_devices().filter(id=device_id).first()

    def import_monitor_devices(self, devices):
        device_list = []
        for device in devices:
            device_list.append(MonitorDevices(**device))
        MonitorDevices.objects.bulk_create(device_list)

    def get_device_by_imei(self, imei):
        return self.get_monitor_devices().filter(imei=imei).first()

    def create_monitor_device(self, enterprise_id, **kwargs):
        kwargs.update({"enterprise_id": enterprise_id})
        return MonitorDevices.objects.create(**kwargs)

    def update_monitor_device(self, enterprise_id, **kwargs):
        kwargs.update({"enterprise_id": enterprise_id})
        return MonitorDevices.objects.filter(id=kwargs["id"]).update(**kwargs)

    def get_monitor_point(self, point_id):
        point = MonitorPoints.objects.prefetch_related("ptdv").filter(
            id=point_id, deleted=False).first()
        if not point:
            return None
        pt = point.ptdv.first()
        point.device_id = pt.id if pt else None
        point.imei = pt.imei if pt else None
        return point

    def update_alarm(self, **kwargs):
        alarm_id = kwargs.pop("alarm_id", None)
        status = kwargs.get("status", 1)
        if status == 2:
            kwargs.update({"is_end": 1})
        MonitorAlarm.objects.filter(id=alarm_id).update(**kwargs)

    def delete_alarm(self, alarm_id):
        MonitorAlarm.objects.filter(id=alarm_id).delete()

    def get_range_data(self, **kwargs):
        point_id = kwargs.pop("point_id", None)
        if isinstance(point_id, list):
            point_id = point_id[0]
        alarm_id = int(kwargs.pop("alarm_id", 0))
        if isinstance(alarm_id, list):
            alarm_id = alarm_id[0]
        start_time = kwargs.pop("start_time", None)
        end_time = kwargs.pop("end_time", None)
        value = kwargs.pop("value", None)
        if not point_id and not alarm_id:
            raise ValidationError("缺少point_id参数")
        if alarm_id:
            os_sql = """
            select a.id,b.number,c.imei,a.from_oa,a.oa_line,
            date_add(d.updated_time, interval 1 hour) end_time,
            date_add(d.tm, interval -1 hour) start_time, 1 is_alarm,
            a.oa_line,a.oa_table
            from monitor_points a
            left join electricity_user b
            on a.electricity_user_id=b.id
            left join monitor_device c
            on a.id=c.monitor_point_id
            left join monitor_alarm d
            on a.id=d.monitor_point_id
            where d.id=%s
            """ % alarm_id
        else:
            os_sql = """
            select
            a.id,a.power,b.number,
            c.imei,a.from_oa,a.oa_line,
            a.electricity_user_id,
            a.voltage_ratio, a.current_ratio,
            null end_time,null start_time,
            0 is_alarm,a.oa_line,a.oa_table
            from monitor_points a
            left join electricity_user b
            on a.electricity_user_id=b.id
            left join monitor_device c
            on a.id=c.monitor_point_id
            where a.id=%s
            """ % point_id
        rst = base_db.query(os_sql)[0]
        if value:
            if "power_curve" in value:
                return self._get_power_curve(point_id, rst)
            elif "price" in value:
                return self._get_ep_price(point_id, rst, start_time, end_time)
            else:
                return self._get_range_data(point_id, value, rst, start_time, end_time)
        else:
            return self._get_range_data(point_id, value, rst, start_time, end_time)

    def _get_range_data(self, point_id, value, payload, start_time, end_time):
        where = ["1=1"]
        is_alarm = payload["is_alarm"]
        number = payload["number"]
        imei = payload["imei"]
        from_oa = int(payload["from_oa"]) if payload["from_oa"] else None
        oa_table = payload["oa_table"] if payload["oa_table"] else None
        oa_line = payload["oa_line"] if payload["oa_line"] else None
        power = int(payload["power"]) if payload["power"] else 0
        voltage_ratio = float(payload["voltage_ratio"]) if payload["voltage_ratio"] else 1.0
        current_ratio = float(payload["current_ratio"]) if payload["current_ratio"] else 1.0
        if not number:
            raise ValidationError("户号为空,数据错误")
        if not start_time and not end_time:
            if is_alarm:
                start_time = payload["start_time"]
                end_time = payload["end_time"]
        if start_time:
            where.extend(
                base_db.filter(start_time,
                               value='tm>="{}"'))
        if end_time:
            where.extend(
                base_db.filter(end_time,
                               value='tm<="{}"'))
        if from_oa:
            table = "tb_" + number
            if not oa_table or not oa_line:
                return []
            where.append("line=%s" % oa_line)
            _value = []
            if value:
                if "p_rate" in value:
                    value.remove("p_rate")
                    _value.append("ROUND(psum/%s, 4) as p_rate" % power)
                if "px" in value:
                    value.remove("px")
                    _value.append("round(px,2) p_d")
                if "psum" in value:
                    value.remove("psum")
                    _value.append("round(psum,2) p")
                if "pfsum" in value:
                    value.remove("pfsum")
                    _value.append("round(pfsum,2) pf")
                _value.extend(["round(`%s`,2) `%s`" % (i, i) for i in value])
                select = """
                "%s" imei, %s, 1 is_bind, `tm`, concat(left(`tm`, 17), "00") a_tm
                """ % (imei, ",".join(_value))
            else:
                select = """
                "%s" imei, round(`ua`,2) ua,round(`ub`,2) ub,round(`uc`,2) uc,
                    null `uab`,null `ubc`,null `uca`,
                    round(`ia`,2) ia,round(`ib`,2) ib,round(`ic`,2) ic,null `ir`,
                    round(`pa`,2) pa,round(`pb`,2) pb,round(`pc`,2) pc,round(`psum`,2) `p`,
                    round(`qa`,2) qa,round(`qb`,2) qb,round(`qc`,2) qc,round(`qsum`,2) `q`,
                    null `sa`,null `sb`,null `sc`,null `s`,
                    round(`pfa`,2) pfa,round(`pfb`,2) pfb,round(`pfc`,2) pfc,
                    round(`pfsum`,2) `pf`,round(`fr`,2) `f`,
                    round(`ep`,2) `ept`,round(`eq1`,2) `eqi`,round(`eq2`,2) `eqe`,
                    round(`px`,2) p_d,round(`udis`,2) `lvur`,round(`idis`,2) `iur`,
                    round(`uah`,2) `ua_thd`,round(`ubh`,2) `ub_thd`,round(`uch`,2) `uc_thd`,
                    round(`iah`,2) `ia_thd`,round(`ibh`,2) `ib_thd`,round(`ich`,2) `ic_thd`,
                    round(`tc1`,2) `t1`,round(`tc2`,2) `t2`,round(`tc3`,2) `t3`,round(`tc4`,2) `t4`,
                    concat(left(`tm`, 17), "00") a_tm,
                    `tm`, 1 is_bind, ROUND(psum/%s, 4) as p_rate
                """ % (imei, power)
            _sql = """
                select
                    %s from `%s`
                """ % (select, table)
            if where:
                _sql = _sql + " WHERE " + " and ".join(where)
            _slq = _sql + """ group by concat(left(`tm`, 17), "00") order by `tm`"""
            try:
                rst = moa_db.query(_sql)
                return rst
            except Exception as e:
                logger.debug(e)
                return []
        else:
            table = "mo_" + number
            where.append(" point_id=%s " % point_id)
            if value:
                vl_map = {
                    "ua": "round(`ua`*{v_r},2) ua".format(v_r=voltage_ratio),
                    "ub": "round(`ub`*{v_r},2) ub".format(v_r=voltage_ratio),
                    "uc": "round(`uc`*{v_r},2) uc".format(v_r=voltage_ratio),
                    "uab": "round(`uab`*{v_r},2) uab".format(v_r=voltage_ratio),
                    "ubc": "round(`ubc`*{v_r},2) ubc".format(v_r=voltage_ratio),
                    "uca": "round(`uca`*{v_r},2) uca".format(v_r=voltage_ratio),
                    "ia": "round(`ia`*{c_r},2) ia".format(c_r=current_ratio),
                    "ib": "round(`ib`*{c_r},2) ib".format(c_r=current_ratio),
                    "ic": "round(`ic`*{c_r},2) ic".format(c_r=current_ratio),
                    "ir": "round(`ir`*{c_r},2) ir".format(c_r=current_ratio),
                    "pa": "round(`pa`*{v_r}*{c_r},2) pa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "pb": "round(`pb`*{v_r}*{c_r},2) pb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "pc": "round(`pc`*{v_r}*{c_r},2) pc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "p": "round(`p`*{v_r}*{c_r},2) p".format(v_r=voltage_ratio, c_r=current_ratio),
                    "qa": "round(`qa`*{v_r}*{c_r},2) qa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "qb": "round(`qb`*{v_r}*{c_r},2) qb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "qc": "round(`qc`*{v_r}*{c_r},2) qc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "q": "round(`q`*{v_r}*{c_r},2) q".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sa": "round(`sa`*{v_r}*{c_r},2) sa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sb": "round(`sb`*{v_r}*{c_r},2) sb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sc": "round(`sc`*{v_r}*{c_r},2) sc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "s": "round(`s`*{v_r}*{c_r},2) s".format(v_r=voltage_ratio, c_r=current_ratio),
                    "ept": "round(`ept`*{v_r}*{c_r},2) ept".format(v_r=voltage_ratio, c_r=current_ratio),
                    "eqi": "round(`eqi`*{v_r}*{c_r},2) eqi".format(v_r=voltage_ratio, c_r=current_ratio),
                    "eqe": "round(`eqe`*{v_r}*{c_r},2) eqe".format(v_r=voltage_ratio, c_r=current_ratio),
                    "p_d": "round(`p_d`*{v_r}*{c_r},2) p_d".format(v_r=voltage_ratio, c_r=current_ratio),
                }
                if "px" in value:
                    value.remove("px")
                    value.append("p_d")
                if "p_rate" in value:
                    value.remove("p_rate")
                    value.append("ROUND(round(`p`*%s*%s,2)/%s, 4) as p_rate" % (voltage_ratio, current_ratio, power))
                if "psum" in value:
                    value.remove("psum")
                    value.append("p")
                if "pfsum" in value:
                    value.remove("pfsum")
                    value.append("pf")
                select = """
                "%s" imei, %s, 1 is_bind, `tm`, concat(left(`tm`, 17), "00") a_tm
                """ % (imei, ",".join([vl_map[i] if i in vl_map else i for i in value]))
            else:
                select = """
                "{imei}" imei, round(`ua`*{v_r},2) ua,round(`ub`*{v_r},2) ub,round(`uc`*{v_r},2) uc,
                round(`uab`*{v_r},2) uab,round(`ubc`*{v_r},2) ubc,round(`uca`*{v_r},2) uca,
                round(`ia`*{c_r},2) ia,round(`ib`*{c_r},2) ib,round(`ic`*{c_r},2) ic,round(`ir`*{c_r},2) ir,
                round(`pa`*{v_r}*{c_r},2) pa,round(`pb`*{v_r}*{c_r},2) pb,round(`pc`*{v_r}*{c_r},2) pc,
                round(`p`*{v_r}*{c_r},2) p,round(`qa`*{v_r}*{c_r},2) qa,round(`qb`*{v_r}*{c_r},2) qb,
                round(`qc`*{v_r}*{c_r},2) qc,round(`q`*{v_r}*{c_r},2) q,round(`sa`*{v_r}*{c_r},2) sa,
                round(`sb`*{v_r}*{c_r},2) sb,round(`sc`*{v_r}*{c_r},2) sc,round(`s`*{v_r}*{c_r},2) s,
                round(`pfa`,2) pfa,round(`pfb`,2) pfb,round(`pfc`,2) pfc,round(`pf`,2) pf,round(`f`,2) f,
                round(`ept`*{v_r}*{c_r},2) ept,round(`eqi`*{v_r}*{c_r},2) eqi,round(`eqe`*{v_r}*{c_r},2) eqe,
                round(`p_d`*{v_r}*{c_r},2) p_d,round(`lvur`,2) lvur,round(`iur`,2) iur,
                round(`ua_thd`,2) ua_thd,round(`ub_thd`,2) ub_thd,round(`uc_thd`,2) uc_thd,
                round(`ia_thd`,2) ia_thd,round(`ib_thd`,2) ib_thd,round(`ic_thd`,2) ic_thd,
                round(`t1`,2) t1,round(`t2`,2) t2,round(`t3`,2) t3,round(`t4`,2) t4,
                concat(left(`tm`, 17), "00") a_tm,
                `tm`, 1 is_bind, ROUND(round(`p`*{v_r}*{c_r},2)/{power}, 4) as p_rate
                """.format(imei=imei, power=power, v_r=voltage_ratio, c_r=current_ratio)
            _sql = """
                select
                    %s from `%s`
                """ % (select, table)
            if where:
                _sql = _sql + " WHERE " + " and ".join(where)
            _sql = _sql + """ group by concat(left(`tm`, 17), "00") order by `tm`  """
            try:
                rst = base_mo_db.query(_sql)
                return rst
            except Exception as e:
                logger.debug(e)
                return []

    def _get_power_curve(self, point_id, payload):
        number = payload["number"]
        from_oa = int(payload["from_oa"]) if payload["from_oa"] else None
        oa_table = payload["oa_table"] if payload["oa_table"] else None
        oa_line = payload["oa_line"] if payload["oa_line"] else None
        voltage_ratio = float(payload["voltage_ratio"]) if payload["voltage_ratio"] else 1.0
        current_ratio = float(payload["current_ratio"]) if payload["current_ratio"] else 1.0
        ep_data_map = {}
        power_curve = {"working_day": [], "rest_day": [], "today": []}
        number = number.split("-")[-1]
        total_lines_df_data = pd.Series(dtype=float)
        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))
        if not number or not point_id:
            return None
        if from_oa:
            _sql1 = """
                select
                distinct line as `line`
                from %s
                where left(tm,10) in (%s)
                and line=%s
                """ % (oa_table, ",".join(str_all_day), oa_line)
            try:
                lines_query = moa_db.query(_sql1)
            except Exception as e:
                logger.debug(e)
                return {}
            for i in lines_query:
                ep_data_map.setdefault(i["line"], [])
            _sql2 = """
            SELECT `line`, ep, tm
            FROM %s
            WHERE
            left(tm,10) in (%s)
            and line=%s
            ORDER BY `line`, tm
            """ % (oa_table, ",".join(str_all_day), oa_line)
            week_data = moa_db.query(_sql2)
        else:
            _sql1 = """
            select
            distinct point_id as `line`
            from mo_%s
            where left(tm,10) in (%s)
            and point_id=%s
            """ % (number, ",".join(str_all_day), point_id)
            try:
                lines_query = base_mo_db.query(_sql1)
            except Exception as e:
                logger.debug(e)
                return {}
            for i in lines_query:
                ep_data_map.setdefault(i["line"], [])
            _sql2 = """
            SELECT point_id `line`, round(`ept`*{}*{},2) ep, tm
            FROM mo_{}
            WHERE
            left(tm,10) in ({})
            and point_id={}
            ORDER BY `point_id`, `tm`
            """.format(voltage_ratio, current_ratio,
                       number, ",".join(str_all_day), point_id)
            week_data = base_mo_db.query(_sql2)

        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().round(4).to_list()
            power_curve["working_day"] = weekday.groupby(
                weekday.index.hour).mean().round(4).to_list()
            power_curve["rest_day"] = weekend.groupby(
                weekend.index.hour).mean().round(4).to_list()
            to_day = today_df_data.groupby(
                today_df_data.index.hour).mean().round(4).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_monitor_payload(self, customer_id=None, number=None, point_id=None):
        payload = {}
        where = ["1=1"]
        if not customer_id and not number and not point_id:
            raise ValidationError("缺少参数")
        where.extend(
            base_db.filter(customer_id,
                           value="a.customer_id={}"))
        where.extend(
            base_db.filter(point_id,
                           value="a.id={}"))
        where.extend(
            base_db.filter(number,
                           value="b.number={}"))
        _sql = """
        select
            %s
        from monitor_points a
        left join electricity_user b
        on a.electricity_user_id=b.id
        """
        select = """
            a.id,a.power,b.number,
            a.electricity_user_id,
            a.voltage_ratio,a.current_ratio,
            a.oa_line,a.oa_table,a.from_oa
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select=select,
                                    where=where)

        rst = base_db.query(sql)
        if rst:
            for i in rst:
                payload[i["id"]] = {
                    "number": i["number"],
                    "power": float(i["power"]),
                    "v_r": float(i["voltage_ratio"]),
                    "c_r": float(i["current_ratio"]),
                    "from_oa": i["from_oa"],
                    "oa_line": i["oa_line"],
                    "oa_table": i["oa_table"],
                }
        return payload

    def get_range_day_ep(self, payload, start_date, end_date, with_price=False):
        dt = {
            "tip": 0.00,
            "peak": 0.00,
            "usual": 0.00,
            "valley": 0.00,
        }
        rst = {}
        if with_price:
            dt.update({"tip_fee": 0.00, "peak_fee": 0.00, "usual_fee": 0.00, "valley_fee": 0.00})
        for i, j in payload.items():
            payload[i]["price"] = self.price_map(i)
        where_sql = """ where tm>="{} 00:00:00" and tm <= "{} 23:59:59" """.format(start_date, end_date)
        for p in payload.keys():
            if payload[p]["from_oa"]:
                where_sql += " and line=%s" % payload[p]["oa_line"]
                sql = """
                select round(ep,2) ept, tm from {} {}
                """.format(payload[p]["oa_table"], where_sql)
                df = moa_db.query_df(sql)
            else:
                where_sql += " and point_id=%s" % p
                sql = """
                    select round(ept*{}*{},2) ept, tm from mo_{} {}
                """.format(payload[p]["v_r"], payload[p]["c_r"], payload[p]["number"], where_sql)
                df = base_mo_db.query_df(sql)
            if df.empty:
                return
            df.set_index("tm", inplace=True)
            se = get_ep_by_hour(df.ept, negative_trend_check=False)
            for month, month_se in se.resample("MS"):
                if month.month in payload[p]["price"].summer.month:
                    for day, day_se in month_se.resample("D"):
                        dt["tip"] += day_se[day_se.index.hour.isin(payload[p]["price"].summer.hour.tip)].sum()
                        dt["peak"] += day_se[day_se.index.hour.isin(payload[p]["price"].summer.hour.peak)].sum()
                        dt["usual"] += day_se[day_se.index.hour.isin(payload[p]["price"].summer.hour.usual)].sum()
                        dt["valley"] += day_se[day_se.index.hour.isin(payload[p]["price"].summer.hour.valley)].sum()
                        if with_price:
                            dt["tip_fee"] += dt["tip"] * payload[p]["price"].summer.price.tip
                            dt["peak_fee"] += dt["peak"] * payload[p]["price"].summer.price.peak
                            dt["usual_fee"] += dt["usual"] * payload[p]["price"].summer.price.usual
                            dt["valley_fee"] += dt["valley"] * payload[p]["price"].summer.price.valley
                        dt["date"] = day.strftime("%Y-%m-%d")
                else:
                    for day, day_se in month_se.resample("D"):
                        dt["tip"] += day_se[day_se.index.hour.isin(payload[p]["price"].un_summer.hour.tip)].sum()
                        dt["peak"] += day_se[day_se.index.hour.isin(payload[p]["price"].un_summer.hour.peak)].sum()
                        dt["usual"] += day_se[day_se.index.hour.isin(payload[p]["price"].un_summer.hour.usual)].sum()
                        dt["valley"] += day_se[day_se.index.hour.isin(payload[p]["price"].un_summer.hour.valley)].sum()
                        if with_price:
                            dt["tip_fee"] += dt["tip"] * payload[p]["price"].un_summer.price.tip
                            dt["peak_fee"] += dt["peak"] * payload[p]["price"].un_summer.price.peak
                            dt["usual_fee"] += dt["usual"] * payload[p]["price"].un_summer.price.usual
                            dt["valley_fee"] += dt["valley"] * payload[p]["price"].un_summer.price.valley
                        dt["date"] = day.strftime("%Y-%m-%d")
                dt["tip"] = round(dt["tip"], 2)
                dt["peak"] = round(dt["peak"], 2)
                dt["usual"] = round(dt["usual"], 2)
                dt["valley"] = round(dt["valley"], 2)
                if with_price:
                    dt["tip_fee"] = round(dt["tip_fee"], 2)
                    dt["peak_fee"] = round(dt["peak_fee"], 2)
                    dt["usual_fee"] = round(dt["usual_fee"], 2)
                    dt["valley_fee"] = round(dt["valley_fee"], 2)
                if dt["date"] not in rst.keys():
                    rst.setdefault(dt["date"], {})
                    rst[dt["date"]][p] = dt
                else:
                    rst[dt["date"]][p] = dt
        ep_dt={}
        for date, dt in rst.items():
            tip = 0.00
            peak = 0.00
            usual = 0.00
            valley = 0.00
            if with_price:
                tip_fee = 0.00
                peak_fee = 0.00
                usual_fee = 0.00
                valley_fee = 0.00
            for p, v in dt.items():
                tip += v["tip"]
                peak += v["peak"]
                usual += v["usual"]
                valley += v["valley"]
                if with_price:
                    tip_fee += v["tip_fee"]
                    peak_fee += v["peak_fee"]
                    usual_fee += v["usual_fee"]
                    valley_fee += v["valley_fee"]
            ep_dt = {
                    "date": date,
                    "tip": round(tip, 2),
                    "peak": round(peak, 2),
                    "usual": round(usual, 2),
                    "valley": round(valley, 2),
            }
            if with_price:
                ep_dt["tip_fee"] = round(tip_fee, 2)
                ep_dt["peak_fee"] = round(peak_fee, 2)
                ep_dt["usual_fee"] = round(usual_fee, 2)
                ep_dt["valley_fee"] = round(valley_fee, 2)

        return ep_dt

    def _get_ep_price(self, point_id, payload, start_time, end_time):
        dt = {
            "ele": {
                "tip": 0.00,
                "peak": 0.00,
                "usual": 0.00,
                "valley": 0.00,
            },
            "fee": {
                "tip": 0.00,
                "peak": 0.00,
                "usual": 0.00,
                "valley": 0.00,
            }
        }

        number = payload["number"]
        voltage_ratio = float(payload["voltage_ratio"]) if payload["voltage_ratio"] else 1.0
        current_ratio = float(payload["current_ratio"]) if payload["current_ratio"] else 1.0
        from_oa = int(payload["from_oa"]) if payload["from_oa"] else None
        oa_table = payload["oa_table"] if payload["oa_table"] else None
        oa_line = payload["oa_line"] if payload["oa_line"] else None
        where = ["1=1"]
        if start_time:
            where.extend(
                base_db.filter(start_time,
                               value='tm>="{}"'))
        if end_time:
            where.extend(
                base_db.filter(end_time,
                               value='tm<"{}"'))
        if not start_time or not end_time:
            raise ValidationError(msg_show="缺少start_time或end_time参数")
        if where:
            where_sql = ' WHERE ' + ' and '.join(where)
        if from_oa:
            where_sql += " and line=%s" % oa_line
            sql = """
            select round(ep,2) ept, tm from {} {}
            """.format(oa_table, where_sql)
            df = moa_db.query_df(sql)
        else:
            where_sql += " and point_id=%s" % point_id
            sql = """
                select round(ept*{}*{},2) ept, tm from mo_{} {}
            """.format(voltage_ratio, current_ratio, number, where_sql)
            df = base_mo_db.query_df(sql)
        if not df.empty:
            price_map = self.price_map(point_id)
            df.set_index("tm", inplace=True)
            se = get_ep_by_hour(df.ept, negative_trend_check=False)
            for month, month_se in se.resample("MS"):
                if month.month in price_map.summer.month:
                    dt["ele"]["tip"] += month_se[month_se.index.hour.isin(price_map.summer.hour.tip)].sum()
                    dt["ele"]["peak"] += month_se[month_se.index.hour.isin(price_map.summer.hour.peak)].sum()
                    dt["ele"]["usual"] += month_se[month_se.index.hour.isin(price_map.summer.hour.usual)].sum()
                    dt["ele"]["valley"] += month_se[month_se.index.hour.isin(price_map.summer.hour.valley)].sum()
                    dt["fee"]["tip"] += dt["ele"]["tip"] * price_map.summer.price.tip
                    dt["fee"]["peak"] += dt["ele"]["peak"] * price_map.summer.price.peak
                    dt["fee"]["usual"] += dt["ele"]["usual"] * price_map.summer.price.usual
                    dt["fee"]["valley"] += dt["ele"]["valley"] * price_map.summer.price.valley
                else:
                    dt["ele"]["tip"] += month_se[month_se.index.hour.isin(price_map.un_summer.hour.tip)].sum()
                    dt["ele"]["peak"] += month_se[month_se.index.hour.isin(price_map.un_summer.hour.peak)].sum()
                    dt["ele"]["usual"] += month_se[month_se.index.hour.isin(price_map.un_summer.hour.usual)].sum()
                    dt["ele"]["valley"] += month_se[month_se.index.hour.isin(price_map.un_summer.hour.valley)].sum()
                    dt["fee"]["tip"] += dt["ele"]["tip"] * price_map.un_summer.price.tip
                    dt["fee"]["peak"] += dt["ele"]["peak"] * price_map.un_summer.price.peak
                    dt["fee"]["usual"] += dt["ele"]["usual"] * price_map.un_summer.price.usual
                    dt["fee"]["valley"] += dt["ele"]["valley"] * price_map.un_summer.price.valley
            dt["ele"]["tip"] = round(dt["ele"]["tip"], 2)
            dt["ele"]["peak"] = round(dt["ele"]["peak"], 2)
            dt["ele"]["usual"] = round(dt["ele"]["usual"], 2)
            dt["ele"]["valley"] = round(dt["ele"]["valley"], 2)
            dt["fee"]["tip"] = round(dt["fee"]["tip"], 2)
            dt["fee"]["peak"] = round(dt["fee"]["peak"], 2)
            dt["fee"]["usual"] = round(dt["fee"]["usual"], 2)
            dt["fee"]["valley"] = round(dt["fee"]["valley"], 2)
        return dt

    def price_map(self, point_id):
        dt = {
            "summer": {
                "month": [],
                "price": {
                    "tip": 0,
                    "peak": 0,
                    "usual": 0,
                    "valley": 0,
                },
                "hour": {
                    "tip": [],
                    "peak": [],
                    "usual": [],
                    "valley": [],
                }
            },
            "un_summer": {
                "month": [],
                "price": {
                    "tip": 0,
                    "peak": 0,
                    "usual": 0,
                    "valley": 0,
                },
                "hour": {
                    "tip": [],
                    "peak": [],
                    "usual": [],
                    "valley": [],
                }
            }
        }
        b_sql = """
        select
            a.id point_id,
            a.customer_id,
            a.electrical_info_id,
            bb.voltage_level voltage_level,
            b.`charge_type` charge_type,
            b.electric_type electric_type,
            b.distinguish_time distinguish_time,
            c.tip_price,c.peak_price,c.usual_price,c.valley_price,
            d.tip_hour,d.peak_hour,d.usual_hour,d.valley_hour,
            d.summer_tip_hour,d.summer_peak_hour,d.summer_usual_hour,d.summer_valley_hour,
            c.is_summer,abn.adcode,e.summer_month
        from monitor_points a
        left join electrical_info bb
        on bb.id=a.electrical_info_id
        left join customer ab
        on a.customer_id=ab.id
        left join enterprise abn
        on ab.enterprise_id=abn.id
        left join customer_ele_price b
        on a.electrical_info_id=b.ele_info_id
        inner join ele_price_map c
        on bb.voltage_level=c.voltage_level
        and b.`charge_type`=c.charge_type
        and b.electric_type=c.electric_type
        and b.distinguish_time=c.distinguish_time
        and c.province=abn.adcode
        inner join ele_distinguish_time d
        on c.province=d.province and c.charge_type=d.charge_type
        inner join ele_price_season e
        on c.province=e.province
        where a.id={}
        """.format(point_id)
        rst = base_db.query(b_sql)
        if not rst:
            sql = """
            select
                a.id point_id,
                a.customer_id,
                a.electrical_info_id,
                ifnull(bb.voltage_level, 2) voltage_level,
                ifnull(b.`charge_type`, 2) charge_type,
                ifnull(b.electric_type, 1) electric_type,
                ifnull(b.distinguish_time, 1) distinguish_time,
                c.tip_price,c.peak_price,c.usual_price,c.valley_price,
                d.tip_hour,d.peak_hour,d.usual_hour,d.valley_hour,
                d.summer_tip_hour,d.summer_peak_hour,d.summer_usual_hour,d.summer_valley_hour,
                c.is_summer,e.summer_month
            from monitor_points a
            left join electrical_info bb
            on bb.id=a.electrical_info_id
            left join customer_ele_price b
            on a.electrical_info_id=b.ele_info_id
            left join ele_price_map c
            on ifnull(bb.voltage_level, 2)=c.voltage_level
            and ifnull(b.`charge_type`, 2)=c.charge_type
            and ifnull(b.electric_type, 1)=c.electric_type
            and ifnull(b.distinguish_time, 1)=c.distinguish_time
            left join ele_distinguish_time d
            on ifnull(c.province,310000)=d.province and c.charge_type=d.charge_type
            left join ele_price_season e
            on e.province=c.province
            where a.id={} and c.province=310000
            """.format(point_id)
            rst = base_db.query(sql)
        for i in rst:
            all_month = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
            summer_month = [int(j) for j in (i["summer_month"].split(",") if i["summer_month"] else [])]
            un_summer_month = list(set(summer_month) ^ set(all_month))
            tip_price = i["tip_price"] if i["tip_price"] else 0
            peak_price = i["peak_price"] if i["peak_price"] else 0
            usual_price = i["usual_price"] if i["usual_price"] else 0
            valley_price = i["valley_price"] if i["valley_price"] else 0
            summer_tip_hour = [int(j) for j in (i["summer_tip_hour"].split(",") if i["summer_tip_hour"] else [])]
            summer_peak_hour = [int(j) for j in (i["summer_peak_hour"].split(",") if i["summer_peak_hour"] else [])]
            summer_usual_hour = [int(j) for j in (i["summer_usual_hour"].split(",") if i["summer_usual_hour"] else [])]
            summer_valley_hour = [int(j) for j in
                                  (i["summer_valley_hour"].split(",") if i["summer_valley_hour"] else [])]
            tip_hour = [int(j) for j in (i["tip_hour"].split(",") if i["tip_hour"] else [])]
            peak_hour = [int(j) for j in (i["peak_hour"].split(",") if i["peak_hour"] else [])]
            usual_hour = [int(j) for j in (i["usual_hour"].split(",") if i["usual_hour"] else [])]
            valley_hour = [int(j) for j in (i["valley_hour"].split(",") if i["valley_hour"] else [])]

            if i["is_summer"]:
                dt["summer"]["month"] = summer_month
                dt["summer"]["price"]["tip"] = tip_price
                dt["summer"]["price"]["peak"] = peak_price
                dt["summer"]["price"]["usual"] = usual_price
                dt["summer"]["price"]["valley"] = valley_price
                dt["summer"]["hour"]["tip"] = summer_tip_hour
                dt["summer"]["hour"]["peak"] = summer_peak_hour
                dt["summer"]["hour"]["usual"] = summer_usual_hour
                dt["summer"]["hour"]["valley"] = summer_valley_hour
            else:
                dt["un_summer"]["month"] = un_summer_month
                dt["un_summer"]["price"]["tip"] = tip_price
                dt["un_summer"]["price"]["peak"] = peak_price
                dt["un_summer"]["price"]["usual"] = usual_price
                dt["un_summer"]["price"]["valley"] = valley_price
                dt["un_summer"]["hour"]["tip"] = tip_hour
                dt["un_summer"]["hour"]["peak"] = peak_hour
                dt["un_summer"]["hour"]["usual"] = usual_hour
                dt["un_summer"]["hour"]["valley"] = valley_hour
        return Dict(dt)

    def _get_price_map(self, customer_id):
        rt = {
            "summer": {
                "peak_price": 0,
                "usual_price": 0,
                "valley_price": 0
            },
            "un_summer": {
                "peak_price": 0,
                "usual_price": 0,
                "valley_price": 0
            },
        }
        sql = """
        select 
            peak_price, usual_price,
            valley_price, basic_price,
            is_summer
        from customer_ele_price a
        left join electrical_info b
        on a.ele_info_id=b.id
        left join ele_price_map c
        on b.voltage_level=c.voltage_level
        and a.electric_type=c.electric_type
        where a.ele_user_id='%s'
        """ % customer_id
        rst = base_db.query(sql)
        for i in rst:
            if i["is_summer"]:
                rt["summer"]["peak_price"] = i["peak_price"]
                rt["summer"]["usual_price"] = i["usual_price"]
                rt["summer"]["valley_price"] = i["valley_price"]
            else:
                rt["un_summer"]["peak_price"] = i["peak_price"]
                rt["un_summer"]["usual_price"] = i["usual_price"]
                rt["un_summer"]["valley_price"] = i["valley_price"]
        return rt

    def move_data_to_number(self, number, imei, point):
        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["ua"] = i["ua"] * point.voltage_ratio
            # i["ub"] = i["ub"] * point.voltage_ratio
            # i["uc"] = i["uc"] * point.voltage_ratio
            #
            # i["uab"] = i["uab"] * point.voltage_ratio
            # i["ubc"] = i["ubc"] * point.voltage_ratio
            # i["uca"] = i["uca"] * point.voltage_ratio
            #
            # i["ia"] = i["ia"] * point.current_ratio
            # i["ib"] = i["ib"] * point.current_ratio
            # i["ic"] = i["ic"] * point.current_ratio
            # i["ir"] = i["ir"] * point.current_ratio
            #
            # i["pa"] = i["pa"] * point.current_ratio * point.voltage_ratio
            # i["pb"] = i["pb"] * point.current_ratio * point.voltage_ratio
            # i["pc"] = i["pc"] * point.current_ratio * point.voltage_ratio
            # i["p"] = i["p"] * point.current_ratio * point.voltage_ratio
            # i["qa"] = i["qa"] * point.current_ratio * point.voltage_ratio
            # i["qb"] = i["qb"] * point.current_ratio * point.voltage_ratio
            # i["qc"] = i["qc"] * point.current_ratio * point.voltage_ratio
            # i["q"] = i["q"] * point.current_ratio * point.voltage_ratio
            # i["sa"] = i["sa"] * point.current_ratio * point.voltage_ratio
            # i["sb"] = i["sb"] * point.current_ratio * point.voltage_ratio
            # i["sc"] = i["sc"] * point.current_ratio * point.voltage_ratio
            # i["s"] = i["s"] * point.current_ratio * point.voltage_ratio
            # i["p_d"] = i["p_d"] * point.current_ratio * point.voltage_ratio
            # i["ept"] = i["ept"] * point.current_ratio * point.voltage_ratio
            # i["eqi"] = i["eqi"] * point.current_ratio * point.voltage_ratio
            # i["eqe"] = i["eqe"] * point.current_ratio * point.voltage_ratio
            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]
                base_mo_db.query(create_monitor_tb % table)
                MonitorMetaDataA.objects.using("monitor").bulk_create(dt_list)
                dts.delete()

    def create_monitor_points(self, **kwargs):
        device_id = kwargs.pop("device_id", None)
        electrical_info_id = kwargs.get("electrical_info_id", None)
        electrical_info = ElectricalInfromation.objects.filter(id=electrical_info_id).first()
        if not electrical_info:
            raise ValidationError(msg_show="未匹配到电站，请检查电源编号是否绑定到电站")
        kwargs.update({
            "station_id": electrical_info.powerstation.id
        })
        point = MonitorPoints.objects.create(**kwargs)
        if device_id:
            devices = MonitorDevices.objects.filter(id=device_id)
            devices.update(monitor_point_id=point.id)
            device = devices.first()
            self.move_data_to_number(
                point.electricity_user.number, device.imei, point)
        pt = point.ptdv.first()
        point.device_id = pt.id if pt else None
        point.imei = pt.imei if pt else None
        return point

    def update_monitor_points(self, **kwargs):
        device_id = kwargs.pop("device_id", None)
        point_id = kwargs.get("id", None)
        MonitorPoints.objects.filter(id=kwargs["id"]).update(**kwargs)
        has_device = MonitorDevices.objects.filter(monitor_point_id=point_id)
        if has_device:
            if not device_id:
                has_device.delete()
        else:
            if device_id:
                MonitorDevices.objects.filter(id=device_id).update(monitor_point_id=point_id)
        # point = MonitorPoints.objects.filter(id=kwargs["id"],
        #                                      deleted=False).first()
        # if device_id:
        #     devices = MonitorDevices.objects.filter(id=device_id)
        #     devices.update(monitor_point_id=point.id)
        #     device = devices.first()
        #     self.move_data_to_number(
        #         point.electricity_user.number, device.imei, point.id)
        # pt = point.ptdv.first()
        # point.device_id = pt.id if pt else None
        # point.imei = pt.imei if pt else None
        # return point

    def create_monitor_device_record(self, **kwargs):
        record = MonitorDevicesRecord.objects.create(**kwargs)
        return record

    def get_monitor_device_records(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")[0] if kwargs.get("page") else 1
        page_size = kwargs.get("page_size")[0] if kwargs.get("page_size") else 10
        if not enterprise_ids:
            return None, 0
        where = ["1=1", "B.enterprise_id in (%s)" % ",".join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(ifnull(A.order_code,""),'
                                 'ifnull(A.name,""),ifnull(G.nickname,""),'
                                 'ifnull(H.nickname,""),ifnull(B.imei,""),'
                                 'ifnull(C.name,""),ifnull(D.name,""),'
                                 'ifnull(E.number,""))'
                                 ' LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("manufacturer"),
                           value='B.manufacturer={}'))
        where.extend(base_db.filter(kwargs.get("model"), value='B.model={}'))
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='D.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("number"),
                           value='E.number LIKE "%%{}%%"'))
        # where.extend(
        #     base_db.filter(kwargs.get("station"),
        #                    value='F.name LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("record_id"), value='A.id={}'))
        where.extend(base_db.filter(kwargs.get("imei"), value='B.imei="{}"'))
        where.extend(
            base_db.filter(kwargs.get("status"), value='A.status={}'))
        limit = base_db.paginator(page, page_size)
        count_sql = """
                SELECT COUNT(A.id) as `count` FROM monitor_device_record A
                """
        _sql = """
                    SELECT
                        %s
                    FROM monitor_device_record A
                    LEFT JOIN monitor_device B
                    ON A.device_id=B.id
                    LEFT JOIN monitor_points C
                    ON B.monitor_point_id=C.id
                    LEFT JOIN outline CCC
                    ON C.outline_id = CCC.id
                    LEFT JOIN equipments CCDD
                    ON C.equipment_id=CCDD.id
                    LEFT JOIN monitor_templates CC
                    ON C.template_id=CC.id
                    LEFT JOIN customer D
                    ON A.customer_id=D.id
                    LEFT JOIN electricity_user E
                    ON A.electricity_user_id=E.id
                    LEFT JOIN electrical_info F
                    ON A.electrical_info_id=F.id
                    LEFT JOIN users G
                    ON A.worker_id=G.id
                    LEFT JOIN users H
                    ON A.approver_id=H.id
                """
        if len(where) > 1:
            count_select = ' COUNT(A.id) as `count` '
            count_sql, _ = base_db.sql_splice(_sql, count_select, where=where)
        select = """
                    A.id,
                    A.created_time,
                    A.updated_time,
                    A.order_code,
                    A.name,
                    A.worker_id,
                    G.nickname worker_name,
                    A.approver_id,
                    H.nickname approver_name,
                    A.approval_time,
                    A.online_time,
                    A.status,
                    A.device_id,
                    B.imei,
                    B.sim,
                    B.iccid,
                    B.network_type,
                    B.manufacturer,
                    B.model,
                    B.monitor_point_id,
                    C.name monitor_point_name,
                    C.outline_id,
                    if(A.trans_capacity,A.trans_capacity,C.power) power,
                    C.station_id,
                    CCC.name outline_name,
                    C.equipment_id,
                    CCDD.name equipment_name,
                    C.template_id,
                    CC.name template_name,
                    A.customer_id,
                    D.name customer_name,
                    A.electricity_user_id,
                    E.number,
                    A.electrical_info_id,
                    F.power_number,
                    if(F.meter_number is null,A.meter_number,F.meter_number) meter_number,
                    B.device_img,
                    A.meter_img,
                    A.trans_nameplate_img,
                    A.complete_img,
                    A.data_img,
                    A.other_img,    
                    A.trans_number,
                    A.trans_capacity,
                    A.voltage_ratio,
                    A.current_ratio,
                    A.comments
                """

        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        order_by="A.created_time",
                                        limit_idx="A.id")
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_real_data_from_oa(self, imei, power):
        sql = """
        select 
            `imei`,round(`ua`,2) `ua`,round(`ub`,2) `ub`,round(`uc`,2) `uc`,
            null `uab`,null `ubc`,null `uca`,
            round(`ia`,2) `ia`,round(`ib`,2) `ib`,round(`ic`,2) `ic`,null `ir`,
            round(`pa`,2) `pa`,round(`pb`,2) `pb`,round(`pc`,2) `pc`,round(`psum`,2) `p`,
            round(`qa`,2) `qa`,round(`qb`,2) `qb`,round(`qc`,2) `qc`,round(`qsum`,2) `q`,
            null `sa`,null `sb`,null `sc`,null `s`,
            round(`pfa`,2) `pfa`,round(`pfb`,2) `pfb`,round(`pfc`,2) `pfc`,
            round(`pfsum`,2) `pf`,round(`fr`,2) `f`,
            round(`ep`,2) `ept`,round(`eq1`,2) `eqi`,round(`eq2`,2) `eqe`,
            round(`px`,2) p_d,null `lvur`,null `iur`,
            round(`uah`,2) `ua_thd`,round(`ubh`,2) `ub_thd`,round(`uch`,2) `uc_thd`,
            round(`iah`,2) `ia_thd`,round(`ibh`,2) `ib_thd`,round(`ich`,2) `ic_thd`,
            round(`tc1`,2) `t1`,round(`tc2`,2) `t2`,round(`tc3`,2) `t3`,round(`tc4`,2) `t4`,
            `tm`, 1 is_bind, ROUND(psum/%s, 4) as p_rate
        from im_real
        where imei="%s"
        """ % (power if power else 0, imei)
        dt = oa_db.query(sql)
        if dt:
            return oa_db.query(sql)[0], 1, 1, True
        return None, 1, 1, True

    def get_real_data(self, imei, power=0):
        sql = """
        select
            A.imei, B.id point_id,
            A.voltage_ratio imei_voltage_ratio,
            A.current_ratio imei_current_ratio,
            B.current_ratio, B.voltage_ratio,
            B.power
        from monitor_device A
        left join monitor_points B
        on A.monitor_point_id=B.id
        where A.imei="%s"
        """ % imei
        dt = base_db.query(sql)
        if dt:
            imei_voltage_ratio = dt[0]["imei_voltage_ratio"]
            imei_current_ratio = dt[0]["imei_current_ratio"]
            voltage_ratio = float(dt[0]["voltage_ratio"] if dt[0]["voltage_ratio"] else 1.0)
            current_ratio = float(dt[0]["current_ratio"] if dt[0]["current_ratio"] else 1.0)
            power = dt[0]["power"]
            mo_sql = """
                select
                    "{imei}" imei,round(`ua`*{v_r},2) ua,round(`ub`*{v_r},2) ub,
                    round(`uc`*{v_r},2) uc,round(`uab`*{v_r},2) uab,
                    round(`ubc`*{v_r},2) ubc,round(`uca`*{v_r},2) uca,
                    round(`ia`*{c_r},2) ia,round(`ib`*{c_r},2) ib,
                    round(`ic`*{c_r},2) ic,round(`ir`*{c_r},2) ir,
                    round(`pa`*{v_r}*{c_r},2) pa,round(`pb`*{v_r}*{c_r},2) pb,
                    round(`pc`*{v_r}*{c_r},2) pc,round(`p`*{v_r}*{c_r},2) p,
                    round(`qa`*{v_r}*{c_r},2) qa,round(`qb`*{v_r}*{c_r},2) qb,
                    round(`qc`*{v_r}*{c_r},2) qc,round(`q`*{v_r}*{c_r},2) q,
                    round(`sa`*{v_r}*{c_r},2) sa,round(`sb`*{v_r}*{c_r},2) sb,
                    round(`sc`*{v_r}*{c_r},2) sc,round(`s`*{v_r}*{c_r},2) s,
                    round(`pfa`,2) pfa,round(`pfb`,2) pfb,round(`pfc`,2) pfc,
                    round(`pf`,2) pf,round(`f`,2) f,
                    round(`ept`*{v_r}*{c_r},2) ept,round(`eqi`*{v_r}*{c_r},2) eqi,
                    round(`eqe`*{v_r}*{c_r},2) eqe,round(`p_d`*{v_r}*{c_r},2) p_d,
                    round(`lvur`,2) lvur,round(`iur`,2) iur,
                    round(`ua_thd`,2) ua_thd,round(`ub_thd`,2) ub_thd,round(`uc_thd`,2) uc_thd,
                    round(`ia_thd`,2) ia_thd,round(`ib_thd`,2) ib_thd,round(`ic_thd`,2) ic_thd,
                    round(`t1`,2) t1,round(`t2`,2) t2,round(`t3`,2) t3,round(`t4`,2) t4,
                    `tm`, 1 is_bind, ROUND(p/{power}, 4) as p_rate
                from mo_real_data where imei="{imei}"
                """.format(power=power if power else 0,
                           imei=imei, v_r=voltage_ratio, c_r=current_ratio)
            rst = base_db.query(mo_sql)
            if rst:
                return rst[0], imei_current_ratio, imei_voltage_ratio, False
        return {}, 1, 1, False

    def get_monitor_data(self, number, point_id):
        table = "mo_" + number

        class MonitorData(MonitorMetaData):
            class Meta:
                db_table = table

        return MonitorData.objects.using("monitor").filter(
            point_id=point_id).order_by("created_time")

    def get_point_transformer_capacity(self, number, point_id):
        sql = """
        SELECT D.capacity
        FROM im_device A
        LEFT JOIN im_customer B
        ON A.im_customer_id = B.id
        LEFT JOIN crm_customer C
        ON B.customer_id = C.id
        LEFT JOIN crm_transformer D
        ON A.transformer_id = D.id
        WHERE C.sn='%s' and A.no='%s'
        """ % (number.split("-")[-1], point_id)
        logger.debug(sql)
        try:
            return oa_db.query(sql)[0]["capacity"]
        except Exception as e:
            logger.debug(e)
            return 0

    def get_old_station_points(self, number):
        _sql = """
        select service_enterprise_id
        from electricity_user a
        left join customer b
        on a.customer_id=b.id
        where a.number='%s'
        """ % number.split("-")[-1]
        flag = base_db.query(_sql)
        if flag and flag[0]["service_enterprise_id"] == 1000:
                sql = """
                select
                a.name, c.imei, a.id as line
                from monitor_points a
                left join electricity_user b
                on a.electricity_user_id=b.id
                left join monitor_device c
                on a.id=c.monitor_point_id
                where b.number='%s'
                """ % number.split("-")[-1]
                return base_db.query(sql)

        # CONCAT("tb_", C.sn) as `table`
        if number in ("3107593345", "3111816456", "3124077154", "3116229815"):
            sql = """
            select b.name, c.imei, b.id line
            from electricity_user a
            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 a.number="%s"
            """ % number
            return base_db.query(sql)
        else:
            sql = """
            SELECT A.name, A.sn as imei, A.no as line
            FROM im_device A
            LEFT JOIN im_customer B
            ON A.im_customer_id = B.id
            LEFT JOIN crm_customer C
            ON B.customer_id = C.id
            WHERE C.sn='%s'
            """ % number.split("-")[-1]
            logger.debug(sql)
            try:
                return oa_db.query(sql)
            except Exception as e:
                logger.debug(e)
                return []

    def get_real_old_station_point_realdata(self, number, point_id):
        _sql = """
        select service_enterprise_id
        from electricity_user a
        left join customer b
        on a.customer_id=b.id
        where a.number='%s'
        """ % number.split("-")[-1]
        flag = base_db.query(_sql)
        if flag and flag[0]["service_enterprise_id"] == 1000:
            sql1 = """
                select
                c.imei
                from monitor_points a
                left join electricity_user b
                on a.electricity_user_id=b.id
                left join monitor_device c
                on a.id=c.monitor_point_id
                where a.id='%s'
                """ % point_id

            rst1 = base_db.query(sql1)
            if rst1:
                imei = rst1[0]["imei"]
                sql2 = """
                select * from mo_real_data where imei="%s"
                """ % imei
                return base_db.query(sql2)
            else:
                return []
        sql = """
        SELECT A.name, A.sn as imei, A.no as line
        FROM im_device A
        LEFT JOIN im_customer B
        ON A.im_customer_id = B.id
        LEFT JOIN crm_customer C
        ON B.customer_id = C.id
        WHERE C.sn='%s' and A.no='%s'
        """ % (number.split("-")[-1], point_id)
        logger.debug(sql)
        rst = oa_db.query(sql)
        imei = None
        if rst:
            imei = rst[0]["imei"]
        if not imei:
            return []
        sql = """
        select * from im_real where imei="%s"
        """ % imei
        logger.debug(sql)
        return oa_db.query(sql)

    def get_real_old_station_point_realdata_trans(self, point_id):
        sql = """
        select
        a.imei,`ua`,`ub`,`uc`,`ia`,`ib`,`ic`,`ir`,`pa`,`pb`,`pc`,
        `p` `psum`,`qa`,`qb`,`qc`,`q` `qsum`,`pfa`,`pfb`,`pfc`,
        `pf` `pfsum`,`f` `fr`,`ept` `ep`,`eqi` `eq1`, `eqe` `eq2`, `p_d` `px`,
        `ua_thd` `uah`,`ub_thd` `ubh`,`uc_thd` `uch`,
        `ia_thd` `iah`,`ib_thd` `ibh`,`ic_thd` `ich`,
        `t1` `tc1`,`t2` `tc2`,`t3` `tc3`,`t4` `tc4`, a.updated_time tm
        from mo_real_data a
        left join monitor_device b
        on a.imei=b.imei
        where b.monitor_point_id=%s
        """ % point_id
        logger.debug(sql)
        return base_db.query(sql)

    def get_old_range_data(self, **kwargs):
        point_id = kwargs.pop("point_id", None)
        if isinstance(point_id, list):
            point_id = point_id[0]
        alarm_id = int(kwargs.pop("alarm_id", 0))
        if isinstance(alarm_id, list):
            alarm_id = alarm_id[0]
        start_time = kwargs.pop("start_time", None)
        end_time = kwargs.pop("end_time", None)
        value = kwargs.pop("value", None)
        if not point_id and not alarm_id:
            raise ValidationError("缺少point_id参数")
        if alarm_id:
            os_sql = """
            select a.id,b.number,c.imei,a.from_oa,a.oa_line,
            date_add(d.updated_time, interval 1 hour) end_time,
            date_add(d.tm, interval -1 hour) start_time, 1 is_alarm,
            a.oa_line,a.oa_table
            from monitor_points a
            left join electricity_user b
            on a.electricity_user_id=b.id
            left join monitor_device c
            on a.id=c.monitor_point_id
            left join monitor_alarm d
            on a.id=d.monitor_point_id
            where d.id=%s
            """ % alarm_id
        else:
            os_sql = """
            select
            a.id,a.power,b.number,
            c.imei,a.from_oa,a.oa_line,
            a.electricity_user_id,
            a.voltage_ratio, a.current_ratio,
            null end_time,null start_time,
            0 is_alarm,a.oa_line,a.oa_table
            from monitor_points a
            left join electricity_user b
            on a.electricity_user_id=b.id
            left join monitor_device c
            on a.id=c.monitor_point_id
            where a.id=%s
            """ % point_id
        rst = base_db.query(os_sql)[0]
        if value:
            if "power_curve" in value:
                return self._get_power_curve(point_id, rst)
            elif "price" in value:
                return self._get_ep_price(point_id, rst, start_time, end_time)
            else:
                return self._get_old_range_data(point_id, value, rst, start_time, end_time)
        else:
            return self._get_old_range_data(point_id, value, rst, start_time, end_time)

    def _get_old_range_data(self, point_id, value, payload, start_time, end_time):
        where = ["1=1"]
        is_alarm = payload["is_alarm"]
        number = payload["number"]
        imei = payload["imei"]
        from_oa = int(payload["from_oa"]) if payload["from_oa"] else None
        oa_table = payload["oa_table"] if payload["oa_table"] else None
        oa_line = payload["oa_line"] if payload["oa_line"] else None
        power = int(payload["power"]) if payload["power"] else 0
        voltage_ratio = float(payload["voltage_ratio"]) if payload["voltage_ratio"] else 1.0
        current_ratio = float(payload["current_ratio"]) if payload["current_ratio"] else 1.0
        if not number:
            raise ValidationError("户号为空,数据错误")
        if not start_time and not end_time:
            if is_alarm:
                start_time = payload["start_time"]
                end_time = payload["end_time"]
        if start_time:
            where.extend(
                base_db.filter(start_time,
                               value='tm>="{}"'))
        if end_time:
            where.extend(
                base_db.filter(end_time,
                               value='tm<="{}"'))
        if from_oa:
            table = "tb_" + number
            if not oa_table or not oa_line:
                return []
            where.append("line=%s" % oa_line)
            _value = []
            if value:
                if "p_rate" in value:
                    value.remove("p_rate")
                    _value.append("ROUND(psum/%s, 4) as psum" % power)
                if "px" in value:
                    value.remove("px")
                    _value.append("round(px,2) px")
                if "psum" in value:
                    value.remove("psum")
                    _value.append("round(psum,2) psum")
                if "pfsum" in value:
                    value.remove("pfsum")
                    _value.append("round(pfsum,2) pfsum")
                _value.extend(["round(`%s`,2) `%s`" % (i, i) for i in value])
                select = """
                "%s" imei, %s, 1 is_bind, `tm`, concat(left(`tm`, 17), "00") a_tm
                """ % (imei, ",".join(_value))
            else:
                select = """
                "%s" imei, round(`ua`,2) ua,round(`ub`,2) ub,round(`uc`,2) uc,
                    null `uab`,null `ubc`,null `uca`,
                    round(`ia`,2) ia,round(`ib`,2) ib,round(`ic`,2) ic,null `ir`,
                    round(`pa`,2) pa,round(`pb`,2) pb,round(`pc`,2) pc,round(`psum`,2) `p`,
                    round(`qa`,2) qa,round(`qb`,2) qb,round(`qc`,2) qc,round(`qsum`,2) `q`,
                    null `sa`,null `sb`,null `sc`,null `s`,
                    round(`pfa`,2) pfa,round(`pfb`,2) pfb,round(`pfc`,2) pfc,
                    round(`pfsum`,2) `pfsum`,round(`fr`,2) `f`,
                    round(`ep`,2) `ept`,round(`eq1`,2) `eqi`,round(`eq2`,2) `eqe`,
                    round(`px`,2) px,round(`udis`,2) `lvur`,round(`idis`,2) `iur`,
                    round(`uah`,2) `ua_thd`,round(`ubh`,2) `ub_thd`,round(`uch`,2) `uc_thd`,
                    round(`iah`,2) `ia_thd`,round(`ibh`,2) `ib_thd`,round(`ich`,2) `ic_thd`,
                    round(`tc1`,2) `t1`,round(`tc2`,2) `t2`,round(`tc3`,2) `t3`,round(`tc4`,2) `t4`,
                    concat(left(`tm`, 17), "00") a_tm,
                    `tm`, 1 is_bind, ROUND(psum/%s, 4) as p_rate
                """ % (imei, power)
            _sql = """
                select
                    %s from `%s`
                """ % (select, table)
            if where:
                _sql = _sql + " WHERE " + " and ".join(where)
            _slq = _sql + """ group by concat(left(`tm`, 17), "00") order by `tm`"""
            try:
                rst = moa_db.query(_sql)
                return rst
            except Exception as e:
                logger.debug(e)
                return []
        else:
            table = "mo_" + number
            where.append(" point_id=%s " % point_id)
            if value:
                vl_map = {
                    "ua": "round(`ua`*{v_r},2) ua".format(v_r=voltage_ratio),
                    "ub": "round(`ub`*{v_r},2) ub".format(v_r=voltage_ratio),
                    "uc": "round(`uc`*{v_r},2) uc".format(v_r=voltage_ratio),
                    "uab": "round(`uab`*{v_r},2) uab".format(v_r=voltage_ratio),
                    "ubc": "round(`ubc`*{v_r},2) ubc".format(v_r=voltage_ratio),
                    "uca": "round(`uca`*{v_r},2) uca".format(v_r=voltage_ratio),
                    "ia": "round(`ia`*{c_r},2) ia".format(c_r=current_ratio),
                    "ib": "round(`ib`*{c_r},2) ib".format(c_r=current_ratio),
                    "ic": "round(`ic`*{c_r},2) ic".format(c_r=current_ratio),
                    "ir": "round(`ir`*{c_r},2) ir".format(c_r=current_ratio),
                    "pa": "round(`pa`*{v_r}*{c_r},2) pa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "pb": "round(`pb`*{v_r}*{c_r},2) pb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "pc": "round(`pc`*{v_r}*{c_r},2) pc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "p": "round(`p`*{v_r}*{c_r},2) psum".format(v_r=voltage_ratio, c_r=current_ratio),
                    "pf": "round(`pf`,2) pfsum",
                    "qa": "round(`qa`*{v_r}*{c_r},2) qa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "qb": "round(`qb`*{v_r}*{c_r},2) qb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "qc": "round(`qc`*{v_r}*{c_r},2) qc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "q": "round(`q`*{v_r}*{c_r},2) q".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sa": "round(`sa`*{v_r}*{c_r},2) sa".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sb": "round(`sb`*{v_r}*{c_r},2) sb".format(v_r=voltage_ratio, c_r=current_ratio),
                    "sc": "round(`sc`*{v_r}*{c_r},2) sc".format(v_r=voltage_ratio, c_r=current_ratio),
                    "s": "round(`s`*{v_r}*{c_r},2) s".format(v_r=voltage_ratio, c_r=current_ratio),
                    "ept": "round(`ept`*{v_r}*{c_r},2) ept".format(v_r=voltage_ratio, c_r=current_ratio),
                    "eqi": "round(`eqi`*{v_r}*{c_r},2) eqi".format(v_r=voltage_ratio, c_r=current_ratio),
                    "eqe": "round(`eqe`*{v_r}*{c_r},2) eqe".format(v_r=voltage_ratio, c_r=current_ratio),
                    "p_d": "round(`p_d`*{v_r}*{c_r},2) px".format(v_r=voltage_ratio, c_r=current_ratio),
                }
                if "px" in value:
                    value.remove("px")
                    value.append("p_d")
                if "p_rate" in value:
                    value.remove("p_rate")
                    value.append("ROUND(round(`p`*%s*%s,2)/%s, 4) as p_rate" % (voltage_ratio, current_ratio, power))
                if "psum" in value:
                    value.remove("psum")
                    value.append("p")
                if "pfsum" in value:
                    value.remove("pfsum")
                    value.append("pf")
                select = """
                "%s" imei, %s, 1 is_bind, `tm`, concat(left(`tm`, 17), "00") a_tm
                """ % (imei, ",".join([vl_map[i] if i in vl_map else i for i in value]))
            else:
                select = """
                "{imei}" imei, round(`ua`*{v_r},2) ua,round(`ub`*{v_r},2) ub,round(`uc`*{v_r},2) uc,
                round(`uab`*{v_r},2) uab,round(`ubc`*{v_r},2) ubc,round(`uca`*{v_r},2) uca,
                round(`ia`*{c_r},2) ia,round(`ib`*{c_r},2) ib,round(`ic`*{c_r},2) ic,round(`ir`*{c_r},2) ir,
                round(`pa`*{v_r}*{c_r},2) pa,round(`pb`*{v_r}*{c_r},2) pb,round(`pc`*{v_r}*{c_r},2) pc,
                round(`p`*{v_r}*{c_r},2) psum,round(`qa`*{v_r}*{c_r},2) qa,round(`qb`*{v_r}*{c_r},2) qb,
                round(`qc`*{v_r}*{c_r},2) qc,round(`q`*{v_r}*{c_r},2) q,round(`sa`*{v_r}*{c_r},2) sa,
                round(`sb`*{v_r}*{c_r},2) sb,round(`sc`*{v_r}*{c_r},2) sc,round(`s`*{v_r}*{c_r},2) s,
                round(`pfa`,2) pfa,round(`pfb`,2) pfb,round(`pfc`,2) pfc,round(`pf`,2) pfsum,round(`f`,2) f,
                round(`ept`*{v_r}*{c_r},2) ept,round(`eqi`*{v_r}*{c_r},2) eqi,round(`eqe`*{v_r}*{c_r},2) eqe,
                round(`p_d`*{v_r}*{c_r},2) px,round(`lvur`,2) lvur,round(`iur`,2) iur,
                round(`ua_thd`,2) ua_thd,round(`ub_thd`,2) ub_thd,round(`uc_thd`,2) uc_thd,
                round(`ia_thd`,2) ia_thd,round(`ib_thd`,2) ib_thd,round(`ic_thd`,2) ic_thd,
                round(`t1`,2) t1,round(`t2`,2) t2,round(`t3`,2) t3,round(`t4`,2) t4,
                concat(left(`tm`, 17), "00") a_tm,
                `tm`, 1 is_bind, ROUND(round(`p`*{v_r}*{c_r},2)/{power}, 4) as p_rate
                """.format(imei=imei, power=power, v_r=voltage_ratio, c_r=current_ratio)
            _sql = """
                select
                    %s from `%s`
                """ % (select, table)
            if where:
                _sql = _sql + " WHERE " + " and ".join(where)
            _sql = _sql + """ group by concat(left(`tm`, 17), "00") order by `tm`  """
            try:
                rst = base_mo_db.query(_sql)
                return rst
            except Exception as e:
                logger.debug(e)
                return []

    def get_real_old_station_point_data(self, number, point_id, capacity,
                                        **kwargs):
        _sql = """
        select service_enterprise_id
        from electricity_user a
        left join customer b
        on a.customer_id=b.id
        where a.number='%s'
        """ % number.split("-")[-1]
        flag = base_db.query(_sql)
        if flag and flag[0]["service_enterprise_id"] == 1000:
            return self.get_old_range_data(point_id=point_id, **kwargs)

        values = kwargs.get("value", [])
        start_time = kwargs.get("start_time")[0]
        end_time = kwargs.get("end_time")[0]
        if not capacity:
            capacity = "0"
        if "p_rate" in values:
            values[values.index('p_rate')] = "ROUND(psum/%s,4) as p_rate" % capacity
        select = ','.join(values)
        if not select:
            select = '*, ROUND(psum/%s,4) as p_rate' % capacity
        sql = """
        SELECT A.name, A.sn as imei, A.no as line,
        CONCAT("tb_", C.sn) as `table`
        FROM im_device A
        LEFT JOIN im_customer B
        ON A.im_customer_id = B.id
        LEFT JOIN crm_customer C
        ON B.customer_id = C.id
        WHERE C.sn='%s' and A.no='%s'
        """ % (number.split("-")[-1], point_id)
        logger.debug(sql)
        rst = oa_db.query(sql)
        table = None
        if rst:
            table = rst[0]["table"]
        if not table:
            return []
        where = ["line=%s" % point_id]
        if start_time:
            where.append('tm>="%s"' % start_time)
        if end_time:
            where.append('tm<"%s"' % end_time)
        _sql = """
        select
            %s, tm
        from %s
        """ % (select, table)
        if where:
            sql = ' WHERE '.join([_sql, ' and '.join(where)])
        return moa_db.df_query(sql)

    def get_real_old_station_point_data_trans(self, point_id, **kwargs):
        sql = """select `power` from monitor_points where id=%s""" % point_id
        rst = base_db.query(sql)[0]
        values = kwargs.get("value", [])
        start_time = kwargs.get("start_time")[0]
        end_time = kwargs.get("end_time")[0]
        power = rst.get("power")
        if power is None:
            power = "0"
        if not values:
            select = """
            `ua`,`ub`,`uc`,`ia`*60,`ib`*60,`ic`*60,`ir`*60,`pa`*60,`pb`*60,`pc`*60,
            `p`*60 `psum`*60, `qa`*60,`qb`*60,`qc`*60,`q`*60 `qsum`*60,`pfa`,`pfb`,`pfc`,
            `pf` `pfsum`,`f` `fr`,`ept` `ep`,`eqi` `eq1`, `eqe` `eq2`, `p_d` `px`,
            `ua_thd` `uah`,`ub_thd` `ubh`,`uc_thd` `uch`,
            `ia_thd` `iah`,`ib_thd` `ibh`,`ic_thd` `ich`,
            `t1` `tc1`,`t2` `tc2`,`t3` `tc3`,`t4` `tc4`, ifnull(ROUND(p/%s,4),0.0) as p_rate """ % power
        else:
            if "px" in values:
                values[values.index('px')] = "p_d*60 px"
            if "psum" in values:
                values[values.index('psum')] = "p*60 psum"
            if "p_rate" in values:
                values[values.index('p_rate')] = "ifnull(ROUND(p/%s,4),0.0)*60 as p_rate" % power
            if "pfsum" in values:
                values[values.index('pfsum')] = "pf pfsum"
            if "ia" in values:
                values[values.index('ia')] = "ia*60 ia"
            if "ib" in values:
                values[values.index('ib')] = "ib*60 ib"
            if "ic" in values:
                values[values.index('ic')] = "ic*60 ic"

            select = ','.join(values)

        where = ["point_id=%s" % point_id]
        if start_time:
            where.append('tm>="%s"' % start_time)
        if end_time:
            where.append('tm<"%s"' % end_time)

        _sql = """
        select
            %s, tm
        from mo_3107593345
        """ % (select)
        if where:
            sql = ' WHERE '.join([_sql, ' and '.join(where)])
        return base_mo_db.df_query(sql)

    def get_customer_price(self, sn):
        rt = {
            "summer": {
                "peak_price": 0,
                "usual_price": 0,
                "valley_price": 0
            },
            "un_summer": {
                "peak_price": 0,
                "usual_price": 0,
                "valley_price": 0
            },
        }
        sql = """
        select peak_price, usual_price, valley_price,
        other_price, basic_price, is_summer
        from crm_customer A
        left join io_price B
        on A.prize_type=B.price_type and
        A.p_vol_level=B.voltage_level
        where B.oa_org_id=2 and sn='%s'
        """ % sn
        rst = oa_db.query(sql)
        for i in rst:
            if i["is_summer"]:
                rt["summer"]["peak_price"] = i["peak_price"]
                rt["summer"]["usual_price"] = i["usual_price"]
                rt["summer"]["valley_price"] = i["valley_price"]
            else:
                rt["un_summer"]["peak_price"] = i["peak_price"]
                rt["un_summer"]["usual_price"] = i["usual_price"]
                rt["un_summer"]["valley_price"] = i["valley_price"]
        return rt

    def get_power_curve(self, number, point_id):
        if not number or not point_id:
            return None
        ep_data_map = {}
        power_curve = {"working_day": [], "rest_day": [], "today": []}
        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)
            and line=%s
            """ % (number, ",".join(str_all_day), point_id)
        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)
        and line=%s
        ORDER BY `line`, tm
        """ % (number, ",".join(str_all_day), point_id)
        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().round(4).to_list()
            power_curve["working_day"] = weekday.groupby(
                weekday.index.hour).mean().round(4).to_list()
            power_curve["rest_day"] = weekend.groupby(
                weekend.index.hour).mean().round(4).to_list()
            to_day = today_df_data.groupby(
                today_df_data.index.hour).mean().round(4).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_power_curve_trans(self, number, point_id):
        if not number or not point_id:
            return None
        ep_data_map = {}
        power_curve = {"working_day": [], "rest_day": [], "today": []}
        number = number.split("-")[-1]
        total_lines_df_data = pd.Series(dtype=float)

        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 point_id as `line`
            from mo_%s
            where left(tm,10) in (%s)
            and point_id=%s
            """ % (number, ",".join(str_all_day), point_id)
        try:
            lines_query = base_mo_db.query(sql)
        except Exception as e:
            logger.debug(e)
            return {}
        for i in lines_query:
            ep_data_map.setdefault(i["line"], [])

        sql = """
        SELECT point_id `line`, ept ep, tm
        FROM mo_%s
        WHERE
        left(tm,10) in (%s)
        and point_id=%s
        ORDER BY `point_id`, `tm`
        """ % (number, ",".join(str_all_day), point_id)
        week_data = base_mo_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().round(4).to_list()
            power_curve["working_day"] = weekday.groupby(
                weekday.index.hour).mean().round(4).to_list()
            power_curve["rest_day"] = weekend.groupby(
                weekend.index.hour).mean().round(4).to_list()
            to_day = today_df_data.groupby(
                today_df_data.index.hour).mean().round(4).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_real_old_station_point_statistic_data(self, number, point_id,
                                                  start_time, end_time):
        rst_sub = {
            "consumption": {
                "valley": 0.00,
                "usual": 0.00,
                "peak": 0.00
            },
            "cost": {
                "valley": 0.00,
                "usual": 0.00,
                "peak": 0.00
            }
        }
        _sql = """
        select service_enterprise_id
        from electricity_user a
        left join customer b
        on a.customer_id=b.id
        where a.number='%s'
        """ % number.split("-")[-1]
        flag = base_db.query(_sql)
        if flag and flag[0]["service_enterprise_id"] == 1000:
            os_sql = """
                select
                a.id,a.power,b.number,
                c.imei,a.from_oa,a.oa_line,
                a.electricity_user_id,
                a.voltage_ratio, a.current_ratio,
                null end_time,null start_time,
                0 is_alarm,a.oa_line,a.oa_table
                from monitor_points a
                left join electricity_user b
                on a.electricity_user_id=b.id
                left join monitor_device c
                on a.id=c.monitor_point_id
                where a.id=%s
                """ % point_id
            rst = base_db.query(os_sql)[0]
            return self._get_power_curve(point_id, rst)
        pr_map = self.get_customer_price(number)
        sql = """
        SELECT A.name, A.sn as imei, A.no as line,
        CONCAT("tb_", C.sn) as `table`
        FROM im_device A
        LEFT JOIN im_customer B
        ON A.im_customer_id = B.id
        LEFT JOIN crm_customer C
        ON B.customer_id = C.id
        WHERE C.sn='%s' and A.no='%s'
        """ % (number.split("-")[-1], point_id)
        logger.debug(sql)
        rst = oa_db.query(sql)
        table = None
        if rst:
            table = rst[0]["table"]
        if not table:
            return []
        where = ["line=%s" % point_id]
        if start_time:
            where.append('tm>="%s"' % start_time)
        if end_time:
            where.append('tm<"%s"' % end_time)
        if where:
            where_sql = ' WHERE ' + ' and '.join(where)
        else:
            where_sql = ''
        sql = """
             select sum(B.ep) degrees, subsection, summer from
             (select ep, case when hur in (8, 9 ,10,18,19,20) then 2
             when hur in (6,7,11,12,15,16,17,21) then 1
             when hur in (0,1,2,3,4,5,22,23) then 0
             when hur in (13, 14) and summer=1 then 2
             else 1 end as subsection, summer
             from (select case when month(tm) in (5,6,7,8,9,10) then 1
             else 0 end as summer, HOUR(tm) hur, max(ep)-min(ep) ep
             from (select ep, tm from %s %s union all
             select min(ep) as ep,
             date_add(CONCAT(left(tm,13), ":59:59"),
             interval -1 hour) as tm
             from %s %s group by left(tm,13)) AA
             group by HOUR(tm), summer) A
             ) B group by subsection, summer
            """ % (table, where_sql, table, where_sql)
        logger.debug(sql)
        result = moa_db.query(sql)
        for i in result:
            degrees = round(float(i["degrees"]), 4)
            if i["summer"]:
                if i["subsection"] == 0:
                    rst_sub["consumption"]["valley"] = degrees
                    rst_sub["cost"]["valley"] += round(float(
                        pr_map["summer"]["valley_price"]) * degrees, 2)
                elif i["subsection"] == 1:
                    rst_sub["consumption"]["usual"] = degrees
                    rst_sub["cost"]["usual"] += round(float(
                        pr_map["summer"]["usual_price"]) * degrees, 2)
                elif i["subsection"] == 2:
                    rst_sub["consumption"]["peak"] = degrees
                    rst_sub["cost"]["peak"] += round(float(
                        pr_map["summer"]["peak_price"]) * degrees, 2)
            else:
                if i["subsection"] == 0:
                    rst_sub["consumption"]["valley"] = degrees
                    rst_sub["cost"]["valley"] += round(float(
                        pr_map["un_summer"]["valley_price"]) * degrees, 2)
                elif i["subsection"] == 1:
                    rst_sub["consumption"]["usual"] = degrees
                    rst_sub["cost"]["usual"] += round(float(
                        pr_map["un_summer"]["usual_price"]) * degrees, 2)
                elif i["subsection"] == 2:
                    rst_sub["consumption"]["peak"] = degrees
                    rst_sub["cost"]["peak"] += round(float(
                        pr_map["un_summer"]["peak_price"]) * degrees, 2)
        rst_sub.update({"power_curve": self.get_power_curve(number, point_id)})
        return rst_sub

    ### mock
    def get_real_old_station_point_statistic_data_trans(self, number, point_id,
                                                        start_time, end_time):
        rst_sub = {
            "consumption": {
                "valley": 0.00,
                "usual": 0.00,
                "peak": 0.00,
                "tip": 0.00
            },
            "cost": {
                "valley": 0.00,
                "usual": 0.00,
                "peak": 0.00,
                "tip": 0.00,
            }
        }
        # pr_map = self.get_customer_price(number)
        pr_map = {
            "summer": {
                "tip_price": 0.884,
                "peak_price": 0.784,
                "usual_price": 0.634,
                "valley_price": 0.434
            },
            "un_summer": {
                "tip_price": 0.884,
                "peak_price": 0.784,
                "usual_price": 0.634,
                "valley_price": 0.434
            },
        }

        where = ["point_id=%s" % point_id]
        if start_time:
            where.append('tm>="%s"' % start_time)
        if end_time:
            where.append('tm<"%s"' % end_time)
        if where:
            where_sql = ' WHERE ' + ' and '.join(where)
        else:
            where_sql = ''
        sql = """
             select sum(B.ep) degrees, subsection, summer from
             (select ep,
             case
             when hur in (7,11,12,13,14,22) then 1
             when hur in (0,1,2,3,4,5,6,23) then 0
             when hur in (8,9,10,15,16,17,18) then 2
             when hur in (19,20,21) then 2
             else 1 end as subsection, summer
             from (select case when month(tm) in (5,6,7,8,9,10) then 1
             else 0 end as summer, HOUR(tm) hur, max(ep)-min(ep) ep
             from (select ept ep, tm from mo_3107593345 %s union all
             select ep,
             date_add(CONCAT(tm_hour, ":59:59"),
             interval -1 hour) as tm
             from (select min(ept) as ep, left(tm,13) tm_hour from mo_3107593345 %s group by left(tm,13)) a) AA
             group by HOUR(tm), summer) A
             ) B group by subsection, summer
            """ % (where_sql, where_sql)
        logger.debug(sql)
        result = base_mo_db.query(sql)
        for i in result:
            degrees = round(float(i["degrees"]), 4)
            if i["summer"]:
                if i["subsection"] == 0:
                    rst_sub["consumption"]["valley"] = degrees
                    rst_sub["cost"]["valley"] += round(float(
                        pr_map["summer"]["valley_price"]) * degrees, 2)
                elif i["subsection"] == 1:
                    rst_sub["consumption"]["usual"] = degrees
                    rst_sub["cost"]["usual"] += round(float(
                        pr_map["summer"]["usual_price"]) * degrees, 2)
                elif i["subsection"] == 2:
                    rst_sub["consumption"]["peak"] = degrees
                    rst_sub["cost"]["peak"] += round(float(
                        pr_map["summer"]["peak_price"]) * degrees, 2)
                elif i["subsection"] == 3:
                    rst_sub["consumption"]["tip"] = degrees
                    rst_sub["cost"]["tip"] += round(float(
                        pr_map["summer"]["tip_price"]) * degrees, 2)
            else:
                if i["subsection"] == 0:
                    rst_sub["consumption"]["valley"] = degrees
                    rst_sub["cost"]["valley"] += round(float(
                        pr_map["un_summer"]["valley_price"]) * degrees, 2)
                elif i["subsection"] == 1:
                    rst_sub["consumption"]["usual"] = degrees
                    rst_sub["cost"]["usual"] += round(float(
                        pr_map["un_summer"]["usual_price"]) * degrees, 2)
                elif i["subsection"] == 2:
                    rst_sub["consumption"]["peak"] = degrees
                    rst_sub["cost"]["peak"] += round(float(
                        pr_map["un_summer"]["peak_price"]) * degrees, 2)
        rst_sub["consumption"]["valley"] = rst_sub["consumption"]["valley"] * 10
        rst_sub["consumption"]["usual"] = rst_sub["consumption"]["usual"] * 10
        rst_sub["consumption"]["peak"] = rst_sub["consumption"]["peak"] * 10
        rst_sub["consumption"]["tip"] = rst_sub["consumption"]["tip"] * 10
        rst_sub["cost"]["valley"] = rst_sub["cost"]["valley"] * 10
        rst_sub["cost"]["usual"] = rst_sub["cost"]["usual"] * 10
        rst_sub["cost"]["peak"] = rst_sub["cost"]["peak"] * 10
        rst_sub["cost"]["tip"] = rst_sub["cost"]["tip"] * 10
        # rst_sub.update({"power_curve": self.get_power_curve_trans(number, point_id)})
        if str(point_id) == "5377":
            rst_sub.update({"power_curve": self.get_power_curve("7565947261", "1")})
        else:
            rst_sub.update({"power_curve": self.get_power_curve("7565947261", "2")})
        return rst_sub
    ### mock


class MonitorSqlRepo(object):
    def get_templates(self, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        get_all = kwargs.get("get_all", False)
        where = ["1=1"]
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(A.name) LIKE "%%{}%%"'))
        limit = base_db.paginator(page, page_size)
        count_sql = """
                SELECT COUNT(A.id) as `count` FROM monitor_templates A
                """
        _sql = """
                    SELECT
                        %s
                    FROM monitor_templates A
                """
        if len(where) > 1:
            count_select = ' COUNT(A.id) as `count` '
            count_sql, _ = base_db.sql_splice(_sql, count_select, where=where)
        select = """
                    A.id,
                    A.created_time,
                    A.updated_time,
                    A.name,
                    A.role,
                    A.notification_type,
                    A.comments
                """
        if get_all:
            limit = None
            count_sql = None
            select = """
                A.id,
                A.name
            """
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="A.id")
        df = base_db.query_df(sql)
        df["role"] = df['role'].apply(json_loads_f)
        df["notification_type"] = df['notification_type'].apply(split_f,
                                                                value=",")
        return df.to_dict(orient="records"), count

    def get_monitor_devices(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        get_all = kwargs.get("get_all", False)
        order_map = {
            "real_tm": "F.tm",
            "device_id": "A.id",
            "manufacturer": "A.manufacturer",
            "model": "A.model",
            "imei": "A.imei",
            "network_type": "A.network_type",
            "sim": "A.sim",
            "iccid": "A.iccid",
            "monitor_point_id": "A.monitor_point_id",
            "customer_id": "B.customer_id",
            "electricity_user_id": "B.electricity_user_id",
            "electrical_info_id": "B.electrical_info_id",
            "created_time": "A.created_time",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), order_map)
        where = ["A.enterprise_id in(%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(ifnull(A.imei,""),ifnull(A.sim,""),'
                                 'ifnull(A.comments,""),ifnull(B.name,"")) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("imei"), value='A.imei="{}"'))
        where.extend(base_db.filter(kwargs.get("device_id"), value='A.id={}'))
        where.extend(base_db.filter(kwargs.get("status"), value='A.status={}'))
        where.extend(
            base_db.filter(kwargs.get("manufacturer"),
                           value='A.manufacturer like "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("model"), value='A.model="{}"'))
        if kwargs.get("unbound", False):
            where.extend(["A.monitor_point_id is null"])
        limit = base_db.paginator(page, page_size)
        if not order_by:
            order_by = "A.created_time DESC"
        count_sql = """
                SELECT COUNT(A.id) as `count` FROM monitor_device A
                WHERE A.enterprise_id in(%s)
                """ % ','.join(enterprise_ids)
        _sql = """
                    SELECT
                        %s
                    FROM monitor_device A
                    LEFT JOIN monitor_points B
                    ON A.monitor_point_id=B.id
                    LEFT JOIN electricity_user C
                    ON B.electricity_user_id=C.id
                    LEFT JOIN customer D
                    ON D.id=B.customer_id
                    LEFT JOIN electrical_info E
                    ON B.electrical_info_id=E.id
                    LEFT JOIN mo_real_data F
                    ON A.imei=F.imei
                """
        if len(where) > 1:
            count_select = ' COUNT(A.id) as `count` '
            count_sql, _ = base_db.sql_splice(_sql, count_select, where=where, order_by=order_by)
        select = """
                    A.id,
                    A.created_time,
                    A.updated_time,
                    A.manufacturer,
                    A.model,
                    A.imei,
                    A.network_type,
                    A.sim,
                    A.iccid,
                    A.`status`,
                    A.device_img,
                    A.voltage_ratio,
                    A.current_ratio,
                    A.monitor_point_id,
                    B.customer_id,
                    B.electricity_user_id,
                    B.electrical_info_id,
                    B.name as monitor_point_name,
                    C.number,
                    D.name customer_name,
                    E.power_number,
                    E.meter_number,
                    A.comments,
                    F.tm real_tm
                """
        limit_sql = """
        SELECT
            %s
        FROM monitor_device A
        LEFT JOIN monitor_points B
        ON A.monitor_point_id=B.id
        """
        if get_all:
            limit = None
            count_sql = None
            select = """
                A.id,
                A.imei,
                A.sim
            """
            limit_sql = None
        if kwargs.get("unbound", False):
            limit = None
            count_sql = None
            select = """
                A.id,
                A.imei,
                A.sim
            """
            limit_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit_sql=limit_sql,
                                        limit=limit,
                                        limit_idx="A.id",
                                        order_by=order_by)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_monitor_points(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        get_all = kwargs.get("get_all", False)
        where = ["B.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(ifnull(A.name,""),ifnull(B.name,""),'
                                 'ifnull(C.`number`,""),ifnull(D.power_number,""),'
                                 'ifnull(E.name,""), ifnull(F.imei,"")) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("station_id"),
                           value="A.station_id={}"))
        where.extend(
            base_db.filter(kwargs.get("point_id"),
                           value="A.id={}"))
        where.extend(
            base_db.filter(kwargs.get("electricity_user_id"),
                           value="C.id={}"))
        limit = base_db.paginator(page, page_size)
        count_sql = """
            SELECT COUNT(A.id) as `count` FROM monitor_points A
            LEFT JOIN customer B
            ON A.customer_id=B.id
            where B.service_enterprise_id in(%s)
            """ % ','.join(enterprise_ids)
        _sql = """
                SELECT
                    %s
                FROM monitor_points A
                LEFT JOIN customer B
                ON A.customer_id=B.id
                LEFT JOIN electricity_user C
                ON A.electricity_user_id=C.id
                LEFT JOIN electrical_info D
                ON A.electrical_info_id=D.id
                LEFT JOIN outline DD
                ON A.outline_id=DD.id
                LEFT JOIN equipments E
                ON A.equipment_id=E.id
                LEFT JOIN monitor_device F
                ON A.id=F.monitor_point_id
                LEFT JOIN monitor_templates G
                ON A.template_id=G.id
            """
        if len(where) > 1:
            count_select = ' COUNT(A.id) as `count` '
            count_sql, _ = base_db.sql_splice(_sql, count_select, where=where)
        select = """
                A.id,
                A.name,
                A.created_time,
                A.updated_time,
                A.customer_id,
                A.power,
                A.phone_list,
                B.name as customer_name,
                A.electricity_user_id,
                C.`number`,
                A.electrical_info_id,
                D.power_number,
                A.outline_id,
                DD.name outline_name,
                A.equipment_id,
                E.name equipment_name,
                F.manufacturer, 
                A.frequency,
                A.current_ratio,
                A.voltage_ratio,
                A.comments,
                A.status,
                F.imei,
                F.iccid iccid,
                F.model,
                CONVERT(F.id,char) as device_id,
                CONVERT(A.template_id,char) as template_id,
                G.name template_name
            """
        if get_all:
            limit = None
            count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="A.id",
                                        order_by="A.created_time desc")
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_alarm(self, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        get_all = kwargs.get("get_all", False)
        order_map = {
            "created_time": "A.created_time",
            "duration": "A.duration",
            "status": "A.status",
            "type": "A.type",
            "level": "A.level",
            "number": "C.number",
            "customer_id": "B.customer_id",
            "tm": "A.tm",
        }
        order_by = base_db.order_by(kwargs.get("_sort"), order_map)
        where = ["1=1 and A.is_alarm=1"]
        where.extend(
            base_db.filter(
                kwargs.get("keyword"),
                value=
                'concat(A.name,B.name,C.number,D.name,BB.imei) LIKE "%%{}%%"'))
        where.extend(base_db.filter(kwargs.get("type"), value='A.type={}'))
        where.extend(base_db.filter(kwargs.get("customer_id"), value='D.id={}'))
        where.extend(base_db.filter(kwargs.get("alarm_id"), value='A.id={}'))
        if kwargs.get("enterprise_ids"):
            where[0] += " and D.service_enterprise_id in ({})".format(",".join(kwargs.get("enterprise_ids")[0]))
        limit = base_db.paginator(page, page_size)
        if not order_by:
            order_by = "A.id"
        _sql = """
                    SELECT
                        %s
                    FROM monitor_alarm A
                    LEFT JOIN monitor_points B
                    ON A.monitor_point_id=B.id
                    LEFT JOIN monitor_device BB
                    ON B.id=BB.monitor_point_id
                    LEFT JOIN electricity_user C
                    ON B.electricity_user_id=C.id
                    LEFT JOIN customer D
                    ON B.customer_id=D.id
                """
        count_select = ' COUNT(A.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql, count_select, where=where, order_by=order_by)
        select = """
                    A.id,
                    A.name,
                    A.type,
                    A.level,
                    A.duration,
                    A.status,
                    A.comments,
                    A.tm created_time,
                    A.tm,
                    A.monitor_point_id,
                    BB.imei,
                    B.name monitor_point_name,
                    B.electricity_user_id,
                    C.number,
                    B.customer_id,
                    D.name customer_name
                """
        if get_all:
            limit = ""
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="A.id",
                                        order_by=order_by)
        page_mata = limit.split(",")
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count, page_mata[0], page_mata[-1]

    def check_monitor_over_tm(self):
        # 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.tm <= 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)

    def check_monitor_data_density(self):
        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 != "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 = base_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": 0,
                        "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%",
                             "0天0时0"])
        message_client.send_station_multi(receive_list)
        if mo_alarm:
            MonitorAlarm.objects.bulk_create(mo_alarm)

    def insert_changsha_mo_dt(self):
        m = hashlib.md5(("f659645bcb2ca1cf0e6acff33c7eef0c,%s" %
                         datetime.datetime.now().strftime('%Y%m%d%H%M')).encode())
        rst = requests.get("https://www.epkeeper.net/party/faladi/%s" % m.hexdigest())
        start = time.strptime("2021-07-03 00:00:00", "%Y-%m-%d %H:%M:%S")
        end = time.strptime("2021-07-03 23:59:59", "%Y-%m-%d %H:%M:%S")
        rq_payload = {
            "imei": "PN--D25410748mS5mi304-1",
            "startDate": int(time.mktime(start)) * 1000,
            "endDate": int(time.mktime(end)) * 1000
        }
        rst = requests.post(
            "https://www.epkeeper.net/a/partyParkExternal/queryImeiHistory?__sid=%s" %
            rst.content.decode().split(":")[-1], data=rq_payload)
        dt = rst.json()
        obj = dt.get("obj")

        class MonitorMetaDataChangSha(MonitorMetaData):
            class Meta:
                db_table = "mo_" + "3107593345"

        mo_list = []
        if obj and isinstance(obj, list):
            for i in obj:
                mo_list.append(
                    MonitorMetaDataChangSha(
                        point_id=5378,
                        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("epiG", 0.0),
                        tm=i.get("createDate", 0.0)))
            MonitorMetaDataChangSha.objects.using("monitor").bulk_create(mo_list)

    def monitor_sync_record(self, session, sid, monitor_model, record_raw):
        # start = time.strptime(str(record_raw["tm"]), "%Y-%m-%d %H:%M:%S")

        start = time.strptime("2021-07-07 00:00:00", "%Y-%m-%d %H:%M:%S")
        end = time.strptime("2021-07-07 23:59:59", "%Y-%m-%d %H:%M:%S")
        rq_payload = {
            "imei": record_raw["imei"],
            "startDate": int(time.mktime(start)) * 1000,
            "endDate": int(time.mktime(end)) * 1000
        }
        # 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)
        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_changsha_mo_dt_sync(self):
        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(
                    self.monitor_sync_record, session, sid,
                    MonitorMetaDataChangSha, record_raw)
                request_list.append(obj)
            for future in as_completed(request_list):
                data = future.result()
        self.insert_to_real()

    def insert_to_real(self):
        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 = base_mo_db.query(sql)
        for row in rows:
            MonitorRealData.objects.update_or_create(defaults=row,
                                                     imei=row["imei"])

    # def get_changsha_month_ep(self):
    #     _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 tm>=date_format(NOW(), '%Y-%m-%d')) a
    #     left join oa_real_data b
    #     on a.tm=b.tm
    #     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))

    def get_changsha_month_ep(self):
        _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))

    def sync_monitor_points_and_devices_from_oa(self):
        _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 = oa_db.get_insert_or_update_sql("monitor_device", _sql1)
        insert_sql2 = oa_db.get_insert_or_update_sql("monitor_points", _sql2)
        base_db.query(insert_sql1)
        base_db.query(insert_sql2)

    def sync_monitor_points_from_oa(self):
        _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,
                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 = oa_db.get_insert_or_update_sql("monitor_points", _sql1)
            if insert_sql1:
                base_db.query(insert_sql1)
                rst2 = oa_db.query(_sql2)
                for i in rst2:
                    print(i)
                    MonitorDevices.objects.filter(
                        imei=i["imei"]).update(monitor_point_id=i["monitor_point_id"])
                    self.move_data_to_number(i["number"], i["imei"], i["monitor_point_id"])

    def move_data_to_number(self, 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]
                base_mo_db.query(create_monitor_tb % table)
                MonitorMetaDataA.objects.using("monitor").bulk_create(dt_list)
                dts.delete()

    def get_monitor_ht_points(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        get_all = kwargs.get("get_all", False)
        where = ["B.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(ifnull(A.name,""),ifnull(B.name,""),'
                                 'ifnull(C.`number`,""),ifnull(D.power_number,""),'
                                 'ifnull(E.name,""), ifnull(F.imei,"")) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("station_id"),
                           value="A.station_id={}"))
        where.extend(
            base_db.filter(kwargs.get("point_id"),
                           value="A.id={}"))
        where.extend(
            base_db.filter(kwargs.get("electricity_user_id"),
                           value="C.id={}"))
        limit = base_db.paginator(page, page_size)
        count_sql = """
            SELECT COUNT(A.id) as `count` FROM monitor_points A
            LEFT JOIN customer B
            ON A.customer_id=B.id
            where B.service_enterprise_id in(%s)
            """ % ','.join(enterprise_ids)
        _sql = """
                SELECT
                    %s
                FROM monitor_ht_points A
                LEFT JOIN customer B
                ON A.customer_id=B.id
                LEFT JOIN electricity_user C
                ON A.electricity_user_id=C.id
                LEFT JOIN electrical_info D
                ON A.electrical_info_id=D.id
                LEFT JOIN outline DD
                ON A.outline_id=DD.id
                LEFT JOIN equipments E
                ON A.equipment_id=E.id
                LEFT JOIN monitor_ht_device F
                ON A.id=F.point_id
                LEFT JOIN monitor_templates G
                ON A.template_id=G.id
            """
        if len(where) > 1:
            count_select = ' COUNT(A.id) as `count` '
            count_sql, _ = base_db.sql_splice(_sql, count_select, where=where)
        select = """
                A.id,
                A.name,
                A.created_time,
                A.updated_time,
                A.customer_id,
                A.phone_list,
                B.name as customer_name,
                A.electricity_user_id,
                C.`number`,
                A.electrical_info_id,
                D.power_number,
                A.outline_id,
                DD.name outline_name,
                A.equipment_id,
                E.name equipment_name,
                F.manufacturer, 
                A.frequency,
                A.comments,
                A.status,
                F.imei,
                F.iccid iccid,
                F.model,
                CONVERT(F.id,char) as device_id,
                CONVERT(A.template_id,char) as template_id,
                G.name template_name
            """
        if get_all:
            limit = None
            count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        limit=limit,
                                        limit_idx="A.id",
                                        order_by="A.created_time desc")
        df = base_db.query_df(sql)
        return df.to_dict(orient="records"), count

    def get_ht_real_data(self, imei):
        mo_sql = """
        select
            imei,
            round(`h`,2) h,
            round(`t`,2) t,
            `tm`
        from ht_mo_real_data where imei="{imei}"
        """.format(imei=imei)
        rst = base_db.query(mo_sql)
        if rst:
            return rst[0]
        return None

    def insert_oa_real_data(self):
        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"]),
                ujson.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"])
            print(insert_sql)
            base_db.query(insert_sql)


monitor_repo = MonitorRepo()
monitor_sql_repo = MonitorSqlRepo()
