import datetime

import ujson
import pandas as pd

from common.utils.exceptions import ValidationError
from common.utils.models import BaseConnection

from console.customer.models import Customer, CustomerAdmin, Contact
from console.customer.apply_func import json_loads_f, split_f

from console.powerstation.models import PowerStation

base_db = BaseConnection()


class CustomerQuerySet(object):
    @property
    def get_all_customer(self):
        return Customer.objects.filter(deleted=False,
                                       level__lte=120).prefetch_related(
            "customer_admin", "service_staff",
            "enterprise").order_by("-id")

    @property
    def get_customer_admin(self):
        return CustomerAdmin.objects.filter(deleted=False).order_by("-id")

    @property
    def contact_lc(self):
        return Contact.objects.select_related().filter(
            deleted=False).prefetch_related("contact_tags")


class CustomerRepo(object):
    def get_customer(self, id):
        return Customer.objects.filter(
            deleted=False, id=id,
            level__lte=120).prefetch_related("customer_admin").first()

    def get_contacts(self, **kwargs):
        where = ["1=1"]
        if not kwargs.get("customer_id"):
            raise ValidationError("缺少关键参数: customer_id")
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='A.customer_id={}'))
        where.extend(base_db.filter(kwargs.get("id"), value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("name"), value='A.name LIKE "%%{}%%"'))
        group_by = 'A.id'
        _sql = """
        SELECT
            %s
        FROM `contact` A
        left join customer AA
        on A.customer_id=AA.id
        left join
        `contact_tags` B
        on A.id=B.contact_id
        left join tags C
        on B.tag_id=C.id
        """
        select = """
        A.id, A.name, A.phone, A.tel, A.email, A.qq,
        A.wechat, A.is_urge, A.is_quit, A.comments,
        A.customer_id, AA.name as customer_name,
        concat("[", group_concat(concat(
        "{",'"id":', C.id, ","),
        concat('"name":"',C.name, '"}')),"]"
        ) as tags
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select=select,
                                    where=where,
                                    group_by=group_by)
        df = base_db.query_df(sql)
        df["tags"] = df['tags'].apply(lambda x: (ujson.loads(x) if x else []))
        return df.to_dict(orient="records")

    def get_customer_map(self, **kwargs):
        where = ["1=1"]
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("number"),
                           value='B.number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("electricity_user_id"),
                           value='B.id={}'))
        where.extend(
            base_db.filter(kwargs.get("station"),
                           value='C.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("power_station_id"),
                           value='C.id={}'))
        where[0] += " and (A.service_enterprise_id in ({}) or D.id in ({}) )".format(
            ",".join(kwargs.get("enterprise_ids")[0]), ",".join(kwargs.get("enterprise_ids")[0]))

        if not where:
            raise ValidationError("缺少过滤参数")

        group_by = 'A.id'
        _sql = """
        SELECT
            %s
        FROM customer A
        left join enterprise D on A.enterprise_id=D.id
        INNER JOIN electricity_user B
        ON A.id=B.customer_id
        INNER JOIN power_station C
        ON C.electricity_user_id=B.id
        """
        select = """
        A.id,
        A.name,
        D.address,
        concat('[', group_concat(DISTINCT
            concat('{','"id":',B.id,","),
            concat('"number":"',replace(IFNULL(B.number,""),'"','\\\\"'),'",'),
            concat('"addr":"',replace(IFNULL(B.addr,""),'"','\\\\"'),'",'),
            '"stations":[',
            concat(IF(C.id is null,'','{"id":'),
            IF(C.id is null,'',C.id),IF(C.id is null,'',",")),
            concat(IF(C.id is null,'','"name":"'),
            IF(C.id is null,'',replace(IFNULL(C.name,""),'"','\\\\"')),
            IF(C.id is null,'','"}')),']}'),']'
        ) as electricity_users
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select=select,
                                    where=where,
                                    group_by=group_by)
        df = base_db.query_df(sql)
        df["electricity_users"] = df['electricity_users'].apply(
            lambda x: (ujson.loads(x) if x else []))
        return df.to_dict(orient="records")

    def get_ele_map(self, **kwargs):
        where = ["1=1"]
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("number"),
                           value='B.number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("electricity_user_id"),
                           value='B.id={}'))
        where.extend(
            base_db.filter(kwargs.get("power_number"),
                           value='C.power_number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("electrical_info_id"),
                           value='C.id={}'))
        where.extend(
            base_db.filter(kwargs.get("outline_id"),
                           value='E.id={}'))
        where.extend(
            base_db.filter(kwargs.get("outline_name"),
                           value='E.name LIKE "%%{}%%"'))
        _sql = """
            SELECT
                %s
            FROM customer A
            left join enterprise D on A.enterprise_id=D.id
            left JOIN electricity_user B
            ON A.id=B.customer_id
            left JOIN electrical_info C
            ON C.electricity_user_id=B.id
            left JOIN outline E
            ON E.power_number=C.id
            """
        select = """
                A.id customer_id,
                A.name customer_name,
                D.address customer_address,
                B.id electricity_user_id,
                B.number,
                B.addr electricity_user_addr,
                C.id electrical_info_id,
                C.power_number,
                C.meter_number,
                E.id outline_id,
                E.name outline_name
            """
        sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records")

    def get_customer_electric_map(self, **kwargs):
        where = ["1=1"]
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("number"),
                           value='B.number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("power_number"),
                           value='C.power_number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("electricity_user_id"),
                           value='B.id={}'))
        where.extend(
            base_db.filter(kwargs.get("electrical_info_id"),
                           value='C.id={}'))
        if not where:
            raise ValidationError("缺少过滤参数")

        _sql = """
        SELECT
            %s
        FROM customer A
        left join enterprise D on A.enterprise_id=D.id
        INNER JOIN electricity_user B
        ON A.id=B.customer_id
        INNER JOIN electrical_info C
        ON C.electricity_user_id=B.id
        INNER JOIN outline E
        ON E.power_number=C.id
        """
        select = """
        A.id customer_id,
        A.name customer_name,
        D.address customer_address,
        B.id electricity_user_id,
        B.number,
        B.addr electricity_user_addr,
        C.id electrical_info_id,
        C.power_number,
        C.meter_number,
        E.id outline_id,
        E.name outline_name
        """
        sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        df = base_db.query_df(sql)
        return df.to_dict(orient="records")

    def get_map_customer(self, enterprise_ids, **kwargs):
        where = ["a.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        if kwargs.get("trusteeship"):
            where.append("a.type!=0")
        where.extend(
            base_db.filter(kwargs.get("vl"),
                           value='a.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("id"),
                           value='a.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='a.id={}'))
        _sql = """
        select
            %s
        from customer a
        """
        select = """
        a.id,
        a.name
        """
        if len(where) > 1:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        else:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where,
                                        limit="0,200", limit_idx="a.id", order_by="a.id desc")
        print(sql)
        return base_db.query(sql)

    def get_map_ele_user(self, enterprise_ids, **kwargs):
        where = ["a.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("vl"),
                           value='b.number LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("id"),
                           value='b.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='a.id={}'))
        where.extend(
            base_db.filter(kwargs.get("ele_user_id"),
                           value='b.id={}'))
        _sql = """
        select
            %s
        from electricity_user b
        left join customer a
        ON a.id=b.customer_id
        """
        select = """
        a.id customer_id,
        a.name customer_name,
        b.id ele_user_id,
        b.number
        """
        if len(where) >= 1:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        else:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where, limit="0,100", limit_idx="b.id desc")
        return base_db.query(sql)

    def get_map_ele_info(self, enterprise_ids, **kwargs):
        where = ["a.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("vl"),
                           value='concat(ifnull(c.power_number,""), ifnull(c.meter_number,"")) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("id"),
                           value='c.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='a.id={}'))
        where.extend(
            base_db.filter(kwargs.get("ele_user_id"),
                           value='b.id={}'))
        where.extend(
            base_db.filter(kwargs.get("ele_info_id"),
                           value='c.id={}'))
        _sql = """
        select
            %s
        from electrical_info c
        left join electricity_user b
        on c.electricity_user_id=b.id
        left join customer a
        ON a.id=b.customer_id
        """
        select = """
        a.id customer_id,
        a.name customer_name,
        b.id ele_user_id,
        b.number,
        c.id ele_info_id,
        c.power_number,
        c.meter_number
        """
        if len(where) >= 1:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        else:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where, limit="0,100", limit_idx="c.id desc")
        return base_db.query(sql)

    def get_map_outline(self, enterprise_ids, **kwargs):
        where = ["a.service_enterprise_id in (%s)" % ','.join(enterprise_ids)]
        where.extend(
            base_db.filter(kwargs.get("vl"),
                           value='d.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("id"),
                           value='d.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer_id"),
                           value='a.id={}'))
        where.extend(
            base_db.filter(kwargs.get("ele_user_id"),
                           value='b.id={}'))
        where.extend(
            base_db.filter(kwargs.get("ele_info_id"),
                           value='c.id={}'))
        where.extend(
            base_db.filter(kwargs.get("outline_id"),
                           value='d.id={}'))
        _sql = """
        select
            %s
        from outline d
        left join electrical_info c
        on d.power_number=c.id
        left join electricity_user b
        on c.electricity_user_id=b.id
        left join customer a
        ON a.id=b.customer_id
        """
        select = """
        a.id customer_id,
        a.name customer_name,
        b.id ele_user_id,
        b.number,
        c.id ele_info_id,
        c.power_number,
        c.meter_number,
        d.id outline_id,
        d.name outline_name
        """
        if len(where) >= 1:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where)
        else:
            sql, _ = base_db.sql_splice(_sql, select=select, where=where, limit="0,100", limit_idx="d.id desc")
        return base_db.query(sql)


class CustomerAdminQuerySet(object):
    @property
    def get_all_customer_admin(self):
        return CustomerAdmin.objects.filter(deleted=False).order_by("-id")


class CustomerAdminRepo(object):
    def get_customer_admin(self, pk):
        queryset = CustomerAdmin.objects.filter(deleted=False, id=pk)
        if not queryset.exists():
            raise ValidationError("没有找到该id")
        return queryset.first()

    def get_customer_admins(self, pks):
        queryset = CustomerAdmin.objects.filter(deleted=False, id__in=pks)
        if len(pks) != len(queryset):
            raise ValidationError("id错误")
        return queryset


class CustomerSqlRepo(object):
    def customers_report(self, enterprise_ids, year_month, customer_ids):
        _sql = """
            SELECT
                %s
            FROM
            (select AA.id from customer AA
            where FIND_IN_SET('1', AA.type)) AA
            LEFT JOIN
            customer A
            ON A.id=AA.id
            RIGHT JOIN (
                select group_concat(DISTINCT D.nickname) service_team_name,
                    group_concat(DISTINCT B.service_team) service_team,
                    A.id, A.number, A.customer_id,A.billing_method,A.ep_factor
                from electricity_user A
                LEFT JOIN power_station B on A.id=B.electricity_user_id
                LEFT JOIN team C on FIND_IN_SET(C.id, B.service_team)
                LEFT JOIN users D ON D.id=C.team_headman_id
                WHERE LEFT(B.end_time,7) >= date_format(
                DATE_SUB("{}-01", INTERVAL 1 MONTH),'%%Y-%%m')
                GROUP BY A.id) E
            ON A.id=E.customer_id
            LEFT JOIN users B
            ON A.last_service_staff_id=B.id
            LEFT JOIN users C
            ON A.service_staff_id=C.id
            LEFT JOIN electrical_info D
            ON A.id=D.customer_id
            LEFT JOIN (select * from electricity_bill FF
                where FF.year_month="%s") F
            ON F.electrical_id=D.id
            # left join customer_report H on H.power_number=E.number
            WHERE D.id is not null and A.service_enterprise_id in(%s)
        """.format(year_month)
        if customer_ids:
            if len(customer_ids) == 1:
                _sql += " and A.id=%s" % customer_ids[0]
            else:
                _sql += " and A.id in (%s)" % ",".join(customer_ids)
        select = ("""
            D.id,
            A.name,
            A.id customer_id,
            E.number,
            E.billing_method,
            CAST(E.id as CHAR) as electricity_user_id,
            D.power_number,
            D.meter_number,
            D.voltage_level,
            D.magnification,
            D.transformer_capacity,
            D.real_capacity,
            CAST(A.service_staff_id as CHAR) service_staff_id,
            C.nickname as service_staff_name,
            F.amount_adjust,
            F.amount,
            F.capacity,
            F.max_md,
            # F.power_factor,
            F.power_factor_real,
            E.ep_factor as power_factor,
            IFNULL(peak_volume,0)+IFNULL(peak_volume2,0)+IFNULL(tip_volume,0)+IFNULL(tip_volume2,0)+
            IFNULL(usual_volume,0)+IFNULL(usual_volume2,0)+IFNULL(valley_volume,0)+
            IFNULL(valley_volume2,0)+IFNULL(other_volume,0) as volume,
            IF(F.id,1,0) as finish,
            E.service_team,
            E.service_team_name,
            # H.basic_confirm,
            # H.basic_confirm_time,
            # H.supper_confirm,
            # H.supper_confirm_time,
            "%s" as `year_month`
        """ % year_month, year_month, ','.join(enterprise_ids))
        count_sql = """
        select COUNT(AA.id) as `count` from customer AA
        where FIND_IN_SET('1', AA.type)
        and AA.service_enterprise_id in(%s)
        """ % ','.join(enterprise_ids)
        if customer_ids:
            if len(customer_ids) == 1:
                count_sql += " and AA.id=%s" % customer_ids[0]
            else:
                count_sql += " and AA.id in (%s)" % ",".join(customer_ids)
        sql, count = base_db.sql_splice(_sql,
                                        select=select,
                                        count_sql=count_sql)
        cols, rows = base_db._sql_query(sql)
        df = pd.DataFrame(columns=cols, data=rows)
        df = df.where(df.notnull(), None)
        return df.to_dict(orient="records"), count

    def customers_report_info(self, electrical_user_id, year_month, old_month):
        _sql = """
        select
            B.id as electricity_user_id,
            B.customer_id,
            B.transformer_capacity,
            B.real_capacity,
            C.name,
            B.number,
            D.address,
            E.name contact_name,
            E.phone contact_phone,
            "%s" `year_month`
        from electricity_user B
        left join customer C
        on C.id=B.customer_id
        left join enterprise D
        on C.enterprise_id=D.id
        left join contact E
        on E.id=B.report_contact_id
        where FIND_IN_SET('1', C.type) and B.id=%s
        """ % (year_month, electrical_user_id)
        base_info = base_db.query(_sql)[0]
        _bill_sql = """
        select
            A.number,
            A.ep_factor as power_factor,
            B.id as electrical_info_id,
            B.power_number,
            B.meter_number,
            B.transformer_capacity capacity,
            B.real_capacity,
            IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0) peak_volume,
            IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0) tip_volume,
            IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0) usual_volume,
            IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0) valley_volume,
            C.other_volume,
            IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0)+
            IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0)+
            IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0)+
            IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0)+
            IFNULL(C.other_volume,0) as volume,
            IFNULL(C.peak_price,0)*IFNULL(C.peak_volume,0)+
            IFNULL(C.peak_price2,0)*IFNULL(C.peak_volume2,0) peak_price,
            IFNULL(C.tip_price,0)*IFNULL(C.tip_volume,0)+
            IFNULL(C.tip_price2,0)*IFNULL(C.tip_volume2,0) tip_price,
            IFNULL(C.usual_price,0)*IFNULL(C.usual_volume,0)+
            IFNULL(C.usual_price2,0)*IFNULL(C.usual_volume2,0) usual_price,
            IFNULL(C.valley_price,0)*IFNULL(C.valley_volume,0)+
            IFNULL(C.valley_price2,0)*IFNULL(C.valley_volume2,0) valley_price,
            IFNULL(C.other_price,0)*IFNULL(C.other_volume,0) other_price,
            C.levy_fee,
            # C.power_factor,
            C.power_factor_real,
            C.power_factor_adjust,
            C.report_md,
            C.max_md,
            C.basic_volume,
            C.basic_price,
            C.idle_volume,
            C.amount,
            C.amount_adjust,
            IFNULL(D.peak_volume,0)+IFNULL(D.peak_volume2,0)+
            IFNULL(D.tip_volume,0)+IFNULL(D.tip_volume2,0)+
            IFNULL(D.usual_volume,0)+IFNULL(D.usual_volume2,0)+
            IFNULL(D.valley_volume,0)+IFNULL(D.valley_volume2,0)+
            IFNULL(D.other_volume,0) as old_volume,
            D.amount old_amount
        from
        electricity_user A
        left join electrical_info B
        on A.id=B.electricity_user_id
        left join electricity_bill C
        on C.electrical_id=B.id
        left join (SELECT * FROM electricity_bill DD
                    where DD.year_month="%s") D
        on C.electrical_id=D.electrical_id
        where C.year_month="%s"
        and A.id=%s
        """ % (old_month, year_month, electrical_user_id)
        bill = base_db.query(_bill_sql)
        base_info.update({"bill": bill})
        _inspect_data = """
        SELECT
            DATE_FORMAT(B.work_date,"%%Y-%%m-%%d %%H:%%i:%%s") work_date,
            B.id task_id,
            DATE_FORMAT(B.end_time,"%%Y-%%m-%%d %%H:%%i:%%s") end_time,
            C.inspector,
            BC.id as team_headman_id,
            BC.nickname as team_headman_name,
            C.temperature,
            C.humidity,
            ifnull(C.weather,"晴") as weather,
            C.remarks,
            C.battery_voltage,
            C.direct_voltage,
            C.thermal_number,
            AA.cable_conduit_status,
            AA.frame_status,
            AA.ground_status,
            AA.window_status,
            AA.house_status,
            AA.cable_holes_status,
            AAA.electroprobe_status,
            AAA.ground_wire,
            AAA.insulating_mat,
            AAA.insulating_gloves,
            AAA.insulating_shoes,
            AAA.extinguisher,
            E.power_number,
            E.meter_number,
            E.id as electrical_info_id,
            D.multiplying_power,
            D.power_factor,
            D.total_active_power,
            D.peak,
            D.flat_1,
            D.flat_2,
            D.valley,
            D.peak_md,
            D.flat_1_md,
            D.flat_2_md,
            D.valley_md,
            D.max_md,
            D.declare_md,
            E.transformer_capacity as capacity,
            E.voltage_level,
            E.real_capacity,
            D.real_power_factor,
            D.reactive_power_1,
            D.reactive_power_2,
            F.v_ab,
            F.v_bc,
            F.v_ca,
            F.i_a,
            F.i_b,
            F.i_c,
            F.monitor_a as i_ma,
            F.monitor_b as i_mb,
            F.monitor_c as i_mc,
            G.outline,
            G.o_ia,
            G.o_ib,
            G.o_ic,
            G.cos,
            G.power,
            G.monitor_a as o_ma,
            G.monitor_b as o_mb,
            G.monitor_c as o_mc,
            G.voice,
            G.fan,
            G.temperature o_temperature,
            G.oil_leak,
            G.dry,
            G.abnormal,
            G.switch_v_ab,
            G.switch_v_bc,
            G.switch_ia,
            G.switch_ib,
            G.switch_v_ca,
            G.switch_ic,
            G.GGJ,
            G.temperature_a,
            G.temperature_b,
            G.temperature_c
        FROM
        (select id,station_id from inspection_plan
        where station_id in (select B.id from electricity_user A
        left join power_station B
        on A.id=B.electricity_user_id
        where A.id=%s)) A
        left join station_building AA
        on AA.station_id=A.station_id
        left join safety_equipment AAA
        on AAA.station_id=A.station_id
        left join
        inspection_task B
        ON B.plan_id=A.id
        left join team BB
        on BB.id=B.team_id
        left join users BC
        ON BB.team_headman_id=BC.id
        left join inspection_data C
        ON C.inspection_task_id=B.id
        left join power_inspection_data D
        on D.inspection_task_id=B.id
        LEFT JOIN electrical_info E
        on D.power_number=E.id
        LEFT JOIN inspection_in F
        on F.power_number=E.id and F.inspect_task_id=B.id
        left join inspection_out G
        on G.power_number=E.id and G.inspect_task_id=B.id
        WHERE B.status="completed" and left(B.work_date,7)="%s"
        """ % (electrical_user_id, year_month)
        df = base_db.query_df(_inspect_data)
        data = base_db.query(_inspect_data)
        inspect = []

        for task_id, task_data in df.groupby("task_id"):
            task_result = {
                "task_id": task_id,
                "work_date": task_data.work_date.values[0],
                "end_time": task_data.end_time.values[0],
                "inspector": task_data.inspector.values[0],
                "team_headman_id": task_data.team_headman_id.values[0],
                "team_headman_name": task_data.team_headman_name.values[0],
                "temperature": task_data.temperature.values[0],
                "humidity": task_data.humidity.values[0],
                "weather": task_data.weather.values[0],
                "remarks": task_data.remarks.values[0],
                "battery_voltage": task_data.battery_voltage.values[0],
                "direct_voltage": task_data.direct_voltage.values[0],
                "thermal_number": task_data.thermal_number.values[0],
                "cable_conduit_status":
                    task_data.cable_conduit_status.values[0],
                "frame_status": task_data.frame_status.values[0],
                "ground_status": task_data.ground_status.values[0],
                "window_status": task_data.window_status.values[0],
                "house_status": task_data.house_status.values[0],
                "cable_holes_status": task_data.cable_holes_status.values[0],
                "electroprobe_status": task_data.electroprobe_status.values[0],
                "ground_wire": task_data.ground_wire.values[0],
                "insulating_mat": task_data.insulating_mat.values[0],
                "insulating_gloves": task_data.insulating_gloves.values[0],
                "insulating_shoes": task_data.insulating_shoes.values[0],
                "extinguisher": task_data.extinguisher.values[0],
                "inspect_in": []
            }

            for electrical_info_id, power_data in task_data.groupby(
                    "electrical_info_id"):
                inspect_in = {
                    "power_number_id": electrical_info_id,
                    "power_number": power_data.power_number.values[0],
                    "meter_number": power_data.meter_number.values[0],
                    "magnification": power_data.multiplying_power.values[0],
                    "power_factor": power_data.power_factor.values[0],
                    "total_active_power":
                        power_data.total_active_power.values[0],
                    "peak": power_data.peak.values[0],
                    "flat_1": power_data.flat_1.values[0],
                    "flat_2": power_data.flat_2.values[0],
                    "valley": power_data.valley.values[0],
                    "peak_md": power_data.peak_md.values[0],
                    "flat_1_md": power_data.flat_1_md.values[0],
                    "flat_2_md": power_data.flat_2_md.values[0],
                    "valley_md": power_data.valley_md.values[0],
                    "max_md": power_data.max_md.values[0],
                    "declare_md": power_data.declare_md.values[0],
                    "capacity": power_data.capacity.values[0],
                    "voltage_level": power_data.voltage_level.values[0],
                    "real_capacity": power_data.real_capacity.values[0],
                    "real_power_factor":
                        power_data.real_power_factor.values[0],
                    "reactive_power_1": power_data.reactive_power_1.values[0],
                    "reactive_power_2": power_data.reactive_power_2.values[0],
                    "v_ab": power_data.v_ab.values[0],
                    "v_bc": power_data.v_bc.values[0],
                    "v_ca": power_data.v_ca.values[0],
                    "i_a": power_data.i_a.values[0],
                    "i_b": power_data.i_b.values[0],
                    "i_c": power_data.i_c.values[0],
                    "i_ma": power_data.i_ma.values[0],
                    "i_mb": power_data.i_mb.values[0],
                    "i_mc": power_data.i_mc.values[0],
                    "outline": []
                }
                for item in data:
                    if task_id == item.get("task_id"):
                        outline_result = {
                            "outline_id": item.get("outline"),
                            "o_ia": item.get("o_ia"),
                            "o_ib": item.get("o_ib"),
                            "o_ic": item.get("o_ic"),
                            "cos": item.get("cos"),
                            "power": item.get("power"),
                            "o_ma": item.get("o_ma"),
                            "o_mb": item.get("o_mb"),
                            "o_mc": item.get("o_mc"),
                            "voice": item.get("voice"),
                            "fan": item.get("fan"),
                            "o_temperature": item.get("o_temperature"),
                            "oil_leak": item.get("oil_leak"),
                            "dry": item.get("dry"),
                            "abnormal": item.get("abnormal"),
                            "switch_v_ab": item.get("switch_v_ab"),
                            "switch_v_bc": item.get("switch_v_bc"),
                            "switch_ia": item.get("switch_ia"),
                            "switch_ib": item.get("switch_ib"),
                            "switch_v_ca": item.get("switch_v_ca"),
                            "switch_ic": item.get("switch_ic"),
                            "GGJ": item.get("GGJ"),
                            "temperature_a": item.get("temperature_a"),
                            "temperature_b": item.get("temperature_b"),
                            "temperature_c": item.get("temperature_c"),
                        }

                        inspect_in["outline"].append(outline_result)

                # for outline, outline_data in power_data.groupby("outline"):

                task_result["inspect_in"].append(inspect_in)
            inspect.append(task_result)
        base_info.update({"inspect": inspect})
        return base_info

    def customers_report_list_info(self, electrical_user_id, year_month, old_month):
        _sql = """
        select
            B.id as electricity_user_id,
            B.customer_id,
            B.transformer_capacity,
            B.real_capacity,
            C.name,
            B.number,
            D.address,
            E.name contact_name,
            E.phone contact_phone,
            "{}" `year_month`
        from electricity_user B
        left join customer C
        on C.id=B.customer_id
        left join enterprise D
        on C.enterprise_id=D.id
        left join contact E
        on E.id=B.report_contact_id
        where FIND_IN_SET('1', C.type) and B.id in ({})
        """.format(year_month, ",".join(electrical_user_id))
        data_ret = []
        result = base_db.query(_sql)
        for ret in result:
            base_info = ret
            _bill_sql = """
                    select
                        A.number,
                        A.ep_factor as power_factor,
                        B.id as electrical_info_id,
                        B.power_number,
                        B.meter_number,
                        B.transformer_capacity capacity,
                        B.real_capacity,
                        IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0) peak_volume,
                        IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0) tip_volume,
                        IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0) usual_volume,
                        IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0) valley_volume,
                        C.other_volume,
                        IFNULL(C.peak_volume,0)+IFNULL(C.peak_volume2,0)+
                        IFNULL(C.tip_volume,0)+IFNULL(C.tip_volume2,0)+
                        IFNULL(C.usual_volume,0)+IFNULL(C.usual_volume2,0)+
                        IFNULL(C.valley_volume,0)+IFNULL(C.valley_volume2,0)+
                        IFNULL(C.other_volume,0) as volume,
                        IFNULL(C.peak_price,0)*IFNULL(C.peak_volume,0)+
                        IFNULL(C.peak_price2,0)*IFNULL(C.peak_volume2,0) peak_price,
                        IFNULL(C.tip_price,0)*IFNULL(C.tip_volume,0)+
                        IFNULL(C.tip_price2,0)*IFNULL(C.tip_volume2,0) tip_price,
                        IFNULL(C.usual_price,0)*IFNULL(C.usual_volume,0)+
                        IFNULL(C.usual_price2,0)*IFNULL(C.usual_volume2,0) usual_price,
                        IFNULL(C.valley_price,0)*IFNULL(C.valley_volume,0)+
                        IFNULL(C.valley_price2,0)*IFNULL(C.valley_volume2,0) valley_price,
                        IFNULL(C.other_price,0)*IFNULL(C.other_volume,0) other_price,
                        C.levy_fee,
                        # C.power_factor,
                        C.power_factor_real,
                        C.power_factor_adjust,
                        C.report_md,
                        C.max_md,
                        C.basic_volume,
                        C.basic_price,
                        C.idle_volume,
                        C.amount,
                        C.amount_adjust,
                        IFNULL(D.peak_volume,0)+IFNULL(D.peak_volume2,0)+
                        IFNULL(D.tip_volume,0)+IFNULL(D.tip_volume2,0)+
                        IFNULL(D.usual_volume,0)+IFNULL(D.usual_volume2,0)+
                        IFNULL(D.valley_volume,0)+IFNULL(D.valley_volume2,0)+
                        IFNULL(D.other_volume,0) as old_volume,
                        D.amount old_amount
                    from
                    electricity_user A
                    left join electrical_info B
                    on A.id=B.electricity_user_id
                    left join electricity_bill C
                    on C.electrical_id=B.id
                    left join (SELECT * FROM electricity_bill DD
                                where DD.year_month="{}") D
                    on C.electrical_id=D.electrical_id
                    where C.year_month="{}"
                    and A.id = {}
                    """.format(old_month, year_month, ret.get("electricity_user_id"))
            bill = base_db.query(_bill_sql)
            base_info.update({"bill": bill})
            _inspect_data = """
                    SELECT
                        DATE_FORMAT(B.work_date,"%Y-%m-%d %H:%i:%s") work_date,
                        B.id task_id,
                        DATE_FORMAT(B.end_time,"%Y-%m-%d %H:%i:%s") end_time,
                        C.inspector,
                        BC.id as team_headman_id,
                        BC.nickname as team_headman_name,
                        C.temperature,
                        C.humidity,
                        ifnull(C.weather,"晴") as weather,
                        C.remarks,
                        C.battery_voltage,
                        C.direct_voltage,
                        C.thermal_number,
                        AA.cable_conduit_status,
                        AA.frame_status,
                        AA.ground_status,
                        AA.window_status,
                        AA.house_status,
                        AA.cable_holes_status,
                        AAA.electroprobe_status,
                        AAA.ground_wire,
                        AAA.insulating_mat,
                        AAA.insulating_gloves,
                        AAA.insulating_shoes,
                        AAA.extinguisher,
                        E.power_number,
                        E.meter_number,
                        E.id as electrical_info_id,
                        D.multiplying_power,
                        D.power_factor,
                        D.total_active_power,
                        D.peak,
                        D.flat_1,
                        D.flat_2,
                        D.valley,
                        D.peak_md,
                        D.flat_1_md,
                        D.flat_2_md,
                        D.valley_md,
                        D.max_md,
                        D.declare_md,
                        E.transformer_capacity as capacity,
                        E.voltage_level,
                        E.real_capacity,
                        D.real_power_factor,
                        D.reactive_power_1,
                        D.reactive_power_2,
                        F.v_ab,
                        F.v_bc,
                        F.v_ca,
                        F.i_a,
                        F.i_b,
                        F.i_c,
                        F.monitor_a as i_ma,
                        F.monitor_b as i_mb,
                        F.monitor_c as i_mc,
                        G.outline,
                        G.o_ia,
                        G.o_ib,
                        G.o_ic,
                        G.cos,
                        G.power,
                        G.monitor_a as o_ma,
                        G.monitor_b as o_mb,
                        G.monitor_c as o_mc,
                        G.voice,
                        G.fan,
                        G.temperature o_temperature,
                        G.oil_leak,
                        G.dry,
                        G.abnormal,
                        G.switch_v_ab,
                        G.switch_v_bc,
                        G.switch_ia,
                        G.switch_ib,
                        G.switch_v_ca,
                        G.switch_ic,
                        G.GGJ,
                        G.temperature_a,
                        G.temperature_b,
                        G.temperature_c
                    FROM
                    (select id,station_id from inspection_plan
                    where station_id in (select B.id from electricity_user A
                    left join power_station B
                    on A.id=B.electricity_user_id
                    where A.id = {})) A
                    left join station_building AA
                    on AA.station_id=A.station_id
                    left join safety_equipment AAA
                    on AAA.station_id=A.station_id
                    left join
                    inspection_task B
                    ON B.plan_id=A.id
                    left join team BB
                    on BB.id=B.team_id
                    left join users BC
                    ON BB.team_headman_id=BC.id
                    left join inspection_data C
                    ON C.inspection_task_id=B.id
                    left join power_inspection_data D
                    on D.inspection_task_id=B.id
                    LEFT JOIN electrical_info E
                    on D.power_number=E.id
                    LEFT JOIN inspection_in F
                    on F.power_number=E.id and F.inspect_task_id=B.id
                    left join inspection_out G
                    on G.power_number=E.id and G.inspect_task_id=B.id
                    WHERE B.status="completed" and left(B.work_date,7)="{}"
                    """.format(ret.get("electricity_user_id"), year_month)
            df = base_db.query_df(_inspect_data)
            data = base_db.query(_inspect_data)
            inspect = []

            for task_id, task_data in df.groupby("task_id"):
                task_result = {
                    "task_id": task_id,
                    "work_date": task_data.work_date.values[0],
                    "end_time": task_data.end_time.values[0],
                    "inspector": task_data.inspector.values[0],
                    "team_headman_id": task_data.team_headman_id.values[0],
                    "team_headman_name": task_data.team_headman_name.values[0],
                    "temperature": task_data.temperature.values[0],
                    "humidity": task_data.humidity.values[0],
                    "weather": task_data.weather.values[0],
                    "remarks": task_data.remarks.values[0],
                    "battery_voltage": task_data.battery_voltage.values[0],
                    "direct_voltage": task_data.direct_voltage.values[0],
                    "thermal_number": task_data.thermal_number.values[0],
                    "cable_conduit_status":
                        task_data.cable_conduit_status.values[0],
                    "frame_status": task_data.frame_status.values[0],
                    "ground_status": task_data.ground_status.values[0],
                    "window_status": task_data.window_status.values[0],
                    "house_status": task_data.house_status.values[0],
                    "cable_holes_status": task_data.cable_holes_status.values[0],
                    "electroprobe_status": task_data.electroprobe_status.values[0],
                    "ground_wire": task_data.ground_wire.values[0],
                    "insulating_mat": task_data.insulating_mat.values[0],
                    "insulating_gloves": task_data.insulating_gloves.values[0],
                    "insulating_shoes": task_data.insulating_shoes.values[0],
                    "extinguisher": task_data.extinguisher.values[0],
                    "inspect_in": []
                }

                for electrical_info_id, power_data in task_data.groupby(
                        "electrical_info_id"):
                    inspect_in = {
                        "power_number_id": electrical_info_id,
                        "power_number": power_data.power_number.values[0],
                        "meter_number": power_data.meter_number.values[0],
                        "magnification": power_data.multiplying_power.values[0],
                        "power_factor": power_data.power_factor.values[0],
                        "total_active_power":
                            power_data.total_active_power.values[0],
                        "peak": power_data.peak.values[0],
                        "flat_1": power_data.flat_1.values[0],
                        "flat_2": power_data.flat_2.values[0],
                        "valley": power_data.valley.values[0],
                        "peak_md": power_data.peak_md.values[0],
                        "flat_1_md": power_data.flat_1_md.values[0],
                        "flat_2_md": power_data.flat_2_md.values[0],
                        "valley_md": power_data.valley_md.values[0],
                        "max_md": power_data.max_md.values[0],
                        "declare_md": power_data.declare_md.values[0],
                        "capacity": power_data.capacity.values[0],
                        "voltage_level": power_data.voltage_level.values[0],
                        "real_capacity": power_data.real_capacity.values[0],
                        "real_power_factor":
                            power_data.real_power_factor.values[0],
                        "reactive_power_1": power_data.reactive_power_1.values[0],
                        "reactive_power_2": power_data.reactive_power_2.values[0],
                        "v_ab": power_data.v_ab.values[0],
                        "v_bc": power_data.v_bc.values[0],
                        "v_ca": power_data.v_ca.values[0],
                        "i_a": power_data.i_a.values[0],
                        "i_b": power_data.i_b.values[0],
                        "i_c": power_data.i_c.values[0],
                        "i_ma": power_data.i_ma.values[0],
                        "i_mb": power_data.i_mb.values[0],
                        "i_mc": power_data.i_mc.values[0],
                        "outline": []
                    }
                    for item in data:
                        if task_id == item.get("task_id"):
                            outline_result = {
                                "outline_id": item.get("outline"),
                                "o_ia": item.get("o_ia"),
                                "o_ib": item.get("o_ib"),
                                "o_ic": item.get("o_ic"),
                                "cos": item.get("cos"),
                                "power": item.get("power"),
                                "o_ma": item.get("o_ma"),
                                "o_mb": item.get("o_mb"),
                                "o_mc": item.get("o_mc"),
                                "voice": item.get("voice"),
                                "fan": item.get("fan"),
                                "o_temperature": item.get("o_temperature"),
                                "oil_leak": item.get("oil_leak"),
                                "dry": item.get("dry"),
                                "abnormal": item.get("abnormal"),
                                "switch_v_ab": item.get("switch_v_ab"),
                                "switch_v_bc": item.get("switch_v_bc"),
                                "switch_ia": item.get("switch_ia"),
                                "switch_ib": item.get("switch_ib"),
                                "switch_v_ca": item.get("switch_v_ca"),
                                "switch_ic": item.get("switch_ic"),
                                "GGJ": item.get("GGJ"),
                                "temperature_a": item.get("temperature_a"),
                                "temperature_b": item.get("temperature_b"),
                                "temperature_c": item.get("temperature_c"),
                            }

                            inspect_in["outline"].append(outline_result)

                    # for outline, outline_data in power_data.groupby("outline"):

                    task_result["inspect_in"].append(inspect_in)
                inspect.append(task_result)
            base_info.update({"inspect": inspect})
            data_ret.append(base_info)
        return data_ret

    def get_customers(self, enterprise_ids, **kwargs):
        page = kwargs.get("page")
        page_size = kwargs.get("page_size")
        customer_id = PowerStation.objects.filter(deleted=False).values_list("customer_id", flat=True)
        where = ["A.deleted=false and (A.service_enterprise_id in (%s) or B.id in (%s))" % (
            ",".join(enterprise_ids), ",".join(enterprise_ids))
                 ]
        if kwargs.get("trusteeship"):
            where = ["A.service_enterprise_id in (%s)" % ",".join(enterprise_ids) + " and A.id in {}".format(
                tuple(filter(None, customer_id)))
                     ]
        where.extend(base_db.filter(kwargs.get("id"), value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("name"), value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(A.name,B.address) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("adcode"),
                           value='B.adcode LIKE "{}%%"',
                           func=lambda x: x.strip("0")))
        where.extend(
            base_db.filter(kwargs.get("type"),
                           value='FIND_IN_SET("{}",A.type)',
                           multi=True))
        group_by = 'A.id'
        limit = base_db.paginator(page, page_size)
        _sql = """
            SELECT
                %s
            FROM customer A
            LEFT JOIN enterprise B
            ON A.enterprise_id=B.id
            LEFT JOIN enterprise BB
            ON A.service_enterprise_id=BB.id
            LEFT JOIN electricity_user I
            ON I.customer_id=A.id
        """
        limit_sql = """
        SELECT
            %s
        FROM customer A
        LEFT JOIN enterprise B
        ON A.enterprise_id=B.id
        """
        count_select = ' COUNT(A.id) as `count` '
        count_sql, _ = base_db.sql_splice(_sql,
                                          count_select,
                                          where=where,
                                          group_by=group_by)
        select = """
            A.id,
            A.name,
            A.code,
            A.service_staff_id,
            A.level,
            A.type,
            A.service_enterprise_id,
            BB.name service_enterprise_name,
            COUNT(DISTINCT I.id) electricity_user_count,
            concat(group_concat(DISTINCT
                    concat('{','"id":',B.id,","),
                    concat('"name":"',IFNULL(B.name,""),'",'),
                    concat('"address":"',IFNULL(B.address,""),'",'),
                    concat('"enterprise_id":"',IFNULL(B.enterprise_id,""),'"}')
                  )
            ) enterprise
        """
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql,
                                        group_by=group_by,
                                        limit=limit,
                                        limit_sql=limit_sql,
                                        limit_idx="A.id")

        df = base_db.query_df(sql)
        df["type"] = df['type'].apply(split_f, value=",")
        df["enterprise"] = df['enterprise'].apply(json_loads_f)
        return df.to_dict(orient="records"), count

    def get_customer_admin_tags_sql(self, enterprise_ids, **kwargs):
        where = ["1=1"]
        where.extend(base_db.filter(kwargs.get("id"), value='a.customer_id={}'))
        group_by = "a.id"
        _sql = """
        select
            %s
        from customer_admin a
        left join users b
        on a.user_id=b.id
        left join accounts c
        on c.id=b.account_id
        left join user_tags d
        on a.user_id=d.user_id
        left join tags e
        on d.tag_id=e.id
        """
        select = """
        b.id,
        a.customer_id,
        b.phone,
        b.nickname,
        b.email,
        b.wechat,
        c.username,
        concat("[", ifnull(group_concat(concat(
                "{",'"id":', e.id, ","),
                concat('"name":"',e.name, '"}')),""),"]"
                ) as tags
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select,
                                    where=where,
                                    group_by=group_by)
        return sql

    def get_contract_tags_sql(self, enterprise_ids, **kwargs):
        where = ["A.deleted=false and AA.service_enterprise_id in (%s)" % (",".join(enterprise_ids))]
        where.extend(base_db.filter(kwargs.get("id"), value='AA.id={}'))
        group_by = "A.id"
        _sql = """
        SELECT
            %s
        FROM `contact` A
        left join customer AA
        on A.customer_id=AA.id
        left join
        `contact_tags` B
        on A.id=B.contact_id
        left join tags C
        on B.tag_id=C.id
        """
        select = """
        A.id, A.name, A.phone, A.tel, A.email, A.qq,
        A.wechat, A.is_urge, A.is_quit, A.comments,
        A.customer_id, AA.name as customer_name,
        concat("[", ifnull(group_concat(concat(
        "{",'"id":', C.id, ","),
        concat('"name":"',C.name, '"}')),""),"]"
        ) as tags
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select,
                                    where=where,
                                    group_by=group_by)
        return sql

    def customers_info(self, enterprise_ids, **kwargs):
        where = ["A.deleted=false and (A.service_enterprise_id in (%s) or B.id in (%s))" % (
            ",".join(enterprise_ids), ",".join(enterprise_ids))
                 ]
        where.extend(base_db.filter(kwargs.get("id"), value='A.id={}'))
        where.extend(
            base_db.filter(kwargs.get("customer"),
                           value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("name"), value='A.name LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("keyword"),
                           value='concat(A.name,B.address) LIKE "%%{}%%"'))
        where.extend(
            base_db.filter(kwargs.get("adcode"),
                           value='B.adcode LIKE "{}%%"',
                           func=lambda x: x.strip("0")))
        where.extend(
            base_db.filter(kwargs.get("type"),
                           value='FIND_IN_SET("{}",A.type)',
                           multi=True))
        group_by = 'A.id'
        customer_admin_tags_sql = self.get_customer_admin_tags_sql(enterprise_ids, **kwargs)
        contract_tags_sql = self.get_contract_tags_sql(enterprise_ids, **kwargs)
        _sql = """
            SELECT
                %s
            FROM customer A
            LEFT JOIN enterprise B
            ON A.enterprise_id=B.id
            LEFT JOIN enterprise BB
            ON A.service_enterprise_id=BB.id
            LEFT JOIN organization BC
            ON A.service_organization_id=BC.id
            LEFT JOIN users C
            ON C.id=A.last_service_staff_id
            LEFT JOIN users D
            ON D.id=A.service_staff_id
            LEFT JOIN ({}) E
            ON E.customer_id=A.id
            LEFT JOIN ({}) G
            ON A.id=G.customer_id
            LEFT JOIN (SELECT
                HH.id, HH.code, HH.entry_date,
                HH.effective_date, HH.end_date,
                HH.customer_id
                FROM contract HH WHERE FIND_IN_SET(HH.type,"1,2,13")
            ) H
            ON H.customer_id=A.id
            LEFT JOIN electricity_user I
            ON I.customer_id=A.id
        """.format(customer_admin_tags_sql, contract_tags_sql)
        select = """
            A.id,
            A.name,
            A.code,
            A.last_service_staff_id,
            C.nickname as last_service_staff_name,
            D.nickname as service_staff_name,
            A.service_staff_id,
            A.level,
            A.type,
            A.service_enterprise_id,
            BB.name service_enterprise_name,
            A.service_organization_id,
            BC.name service_organization_name,
            COUNT(DISTINCT I.id) electricity_user_count,
            concat(group_concat(DISTINCT
                    concat('{','"id":',B.id,","),
                    concat('"name":"',IFNULL(B.name,""),'",'),
                    concat('"address":"',IFNULL(B.address,""),'",'),
                    concat('"enterprise_id":"',IFNULL(B.enterprise_id,""),'",'),
                    concat('"longitude":"',IFNULL(B.longitude,""),'",'),
                    concat('"latitude":"',IFNULL(B.latitude,""),'",'),
                    concat('"scale":"',IFNULL(B.scale,""),'",'),
                    concat('"asset":"',IFNULL(B.asset,""),'",'),
                    concat('"covered_area":"',IFNULL(B.covered_area,""),'",'),
                    concat('"legal_person":"',IFNULL(B.legal_person,""),'",'),
                    concat('"legal_person_phone":"',IFNULL(B.legal_person_phone,""),'",'),
                    concat('"nature":"',IFNULL(B.nature,""),'",'),
                    concat('"industry":"',IFNULL(B.industry,""),'",'),
                    concat('"parent_enterprise_id":"',IFNULL(B.parent_enterprise_id,""),'",'),
                    concat('"postcode":"',IFNULL(B.postcode,""),'",'),
                    concat('"tax_num":"',IFNULL(B.tax_num,""),'",'),
                    concat('"bank_account_name":"',IFNULL(B.bank_account_name,""),'",'),
                    concat('"bank_name":"',IFNULL(B.bank_name,""),'",'),
                    concat('"logo":"',IFNULL(B.logo,""),'",'),
                    concat('"adcode":"',IFNULL(B.adcode,""),'"}')
                  )
            ) enterprise,
            concat('[', group_concat(DISTINCT
                    concat('{','"id":',E.id,","),
                    concat('"phone":"',IFNULL(E.phone,""),'",'),
                    concat('"nickname":"',replace(IFNULL(E.nickname,""),'"','\\\\"'),'",'),
                    concat('"username":"',IFNULL(E.username,""),'",'),
                    concat('"email":"',IFNULL(E.email,""),'",'),
                    concat('"phone":"',IFNULL(E.phone,""),'",'),
                    concat('"wechat":"',IFNULL(E.wechat,""),'",'),
                    concat('"tags":',IFNULL(E.tags,""),""),
                    '}'),']'
                    ) customer_admin,
            concat('[', group_concat(DISTINCT concat(
                    '{','"id":', G.id, ","),
                    concat('"phone":"',IFNULL(G.phone,""),'",'),
                    concat('"tel":"',IFNULL(G.tel,""),'",'),
                    concat('"qq":"',IFNULL(G.qq,""),'",'),
                    concat('"wechat":"',IFNULL(G.wechat,""),'",'),
                    concat('"email":"', IFNULL(G.email,""),'",'),
                    concat('"is_urge":',IFNULL(G.is_urge,""),','),
                    concat('"is_quit":',IFNULL(G.is_quit,""),','),
                    concat('"tags":',IFNULL(G.tags,""),","),
                    concat('"comments":"',IFNULL(G.comments,""),'",'),
                    concat('"name":"',IFNULL(G.name,""),'"}')),']'
                    ) contacts,
            concat('[', group_concat(DISTINCT concat(
                    '{','"id":', H.id, ","),
                    concat('"code":"',IFNULL(H.code,""),'",'),
                    concat('"entry_date":"',IFNULL(H.entry_date,""),'",'),
                    concat('"effective_date":"',IFNULL(H.effective_date,""),'",'),
                    concat('"end_date":"',IFNULL(H.end_date,""),'"}')),']'
                    ) contract
        """
        sql, _ = base_db.sql_splice(_sql,
                                    select,
                                    where=where,
                                    group_by=group_by)

        df = base_db.query_df(sql)
        df["type"] = df['type'].apply(split_f, value=",")
        df["enterprise"] = df['enterprise'].apply(json_loads_f)
        df["customer_admin"] = df['customer_admin'].apply(json_loads_f)
        df["contacts"] = df['contacts'].apply(json_loads_f)
        df["contract"] = df['contract'].apply(json_loads_f)
        rst = df.to_dict(orient="records")
        if rst:
            return rst[0]
        return None


customer_queryset = CustomerQuerySet()
custom_repo = CustomerRepo()
custom_sql_repo = CustomerSqlRepo()
custom_admin_queryset = CustomerAdminQuerySet()
custom_admin_repo = CustomerAdminRepo()
