# coding=utf-8
import pickle
import datetime
from dateutil.relativedelta import relativedelta

from common.utils.models import PowerOAConnection, BaseConnection
from common.utils.exceptions import TooManyLinesRecv, ValidationError
from common.utils.str_utils import is_number
from common.utils.gettimes import GetRunTime
from common.utils.redis_api import dashboard_redis_api

power_db = PowerOAConnection()
base_db = BaseConnection()


class PowerOARepo(object):
    # 托管客户
    def get_io_customers(self,
                         page,
                         page_size,
                         adcode,
                         parameter=None,
                         longitude=None,
                         latitude=None):
        # order_by = None
        where = ["A.stopped=0 and A.`contract_id`>0"]
        order_by = "B.`sn`"
        limit = power_db.paginator(page, page_size)
        sql = """
        SELECT
                B.`id` as `id`,
                B.`name` as `name`,
                B.`sn` as 'number',
                B.`longitude` as `longitude`,
                B.`latitude` as `latitude`,
                B.`address` as `address`,
                B.`district` as 'adcode'
            FROM
                io_customer as A
            LEFT JOIN
                crm_customer as B
            ON
                A.`customer_id` = B.id
        """
        if adcode:
            adcode = adcode.rstrip("0")
            if len(adcode) != 6:
                where.append("""
                B.`district` like '{adcode}%'
                """.format(adcode=adcode))
            else:
                where.append("""
                B.`district`='{adcode}'
                """.format(adcode=adcode))
        if parameter:
            where.append("""
            (CONCAT(ifnull(B.`name`,''), ifnull(B.`sn`,''),
             ifnull(B.`address`,'')) like '%{parameter}%')
            """.format(parameter=parameter))

        if longitude:
            longitudes = sorted(longitude.split(","))
            if is_number(longitudes):
                where.append("""
                B.`longitude` >= {} and B.`longitude` <= {}

                """.format(longitudes[0], longitudes[-1]))
            else:
                raise ValidationError("longitude 参数不符合规范")

        if latitude:
            latitudes = sorted(latitude.split(","))
            if is_number(latitudes):
                where.append("""
                    B.`latitude` >= {} and B.`latitude` <= {}

                    """.format(latitudes[0], latitudes[-1]))
            else:
                raise ValidationError("latitude 参数不符合规范")

        if where:
            sql = ' WHERE '.join([sql, ' and '.join(where)])

        if order_by:
            sql = ' ORDER BY '.join([sql, order_by])
        if limit:
            count = 'SELECT COUNT(1) as "count" FROM( %s ) as total' % sql
            count = power_db.query(count)[0]["count"]
            sql = ' LIMIT '.join([sql, limit])
        else:
            count = 'SELECT COUNT(1) as "count" FROM( %s ) as total' % sql
            count = power_db.query(count)[0]["count"]
            if count > 100:
                raise TooManyLinesRecv
            count = None
        return power_db.query(sql), count

    # 托管客户
    def get_cache_io_customers(self):
        io_customers = dashboard_redis_api.get("io_customers1")
        if io_customers is None:
            sql = """
            SELECT
                A.`id` as `id`,
                B.`name` as `name`,
                B.`sn` as 'number',
                B.`longitude` as `longitude`,
                B.`latitude` as `latitude`,
                B.`address` as `address`,
                B.`district` as 'adcode'
            FROM
                io_customer as A
            LEFT JOIN
                crm_customer as B
            ON
                A.`customer_id` = B.id
            WHERE
                A.stopped=0 and
                A.`contract_id`>0
            ORDER BY
                B.`sn`
            """
            customers = power_db.query(sql)
            count = len(customers)
            dashboard_redis_api.delete("io_customers1")
            dashboard_redis_api.set("io_customers1", pickle.dumps(customers))
        else:
            customers = pickle.loads(io_customers)
            count = len(customers)
        return customers, count

    # 签约客户
    def get_subscribe_customer(self,
                               page,
                               page_size,
                               adcode,
                               parameter=None,
                               longitude=None,
                               latitude=None):
        where = ["1=1 and A.service_enterprise_id != 2"]
        order_by = "B.number"
        limit = base_db.paginator(page, page_size)
        sql = """
        select
            A.id,
            A.name,
            B.number,
            B.longitude,
            B.latitude,
            B.addr address,
            B.ad_code adcode,
            A.type,
            A.service_enterprise_id
        from customer A
        left join electricity_user B
        on A.id=B.customer_id
        """
        if adcode:
            adcode = adcode.rstrip("0")
            if len(adcode) != 6:
                where.append("""
                        B.`ad_code` like '{adcode}%'
                        """.format(adcode=adcode))
            else:
                where.append("""
                        B.`ad_code`='{adcode}'
                        """.format(adcode=adcode))
        if parameter:
            where.append("""
                    (CONCAT(ifnull(A.`name`,''), ifnull(B.`number`,''),
                    ifnull(B.`addr`,'')) like '%{parameter}%')
                    """.format(parameter=parameter))

        if longitude:
            longitudes = sorted(longitude.split(","))
            if is_number(longitudes):
                where.append("""
                        B.`longitude` >= {} and B.`longitude` <= {}
                        """.format(longitudes[0], longitudes[-1]))
            else:
                raise ValidationError("longitude 参数不符合规范")

        if latitude:
            latitudes = sorted(latitude.split(","))
            if is_number(latitudes):
                where.append("""
                            B.`latitude` >= {} and B.`latitude` <= {}
                            """.format(latitudes[0], latitudes[-1]))
            else:
                raise ValidationError("latitude 参数不符合规范")

        if where:
            sql = ' WHERE '.join([sql, ' and '.join(where)])

        if order_by:
            sql = ' ORDER BY '.join([sql, order_by])
        if limit:
            count = 'SELECT COUNT(1) as "count" FROM( %s ) as total' % sql
            count = base_db.query(count)[0]["count"]
            sql = ' LIMIT '.join([sql, limit])
        else:
            count = 'SELECT COUNT(1) as "count" FROM( %s ) as total' % sql
            count = base_db.query(count)[0]["count"]
            if count > 100:
                raise TooManyLinesRecv
            count = None
        return base_db.query_list(sql), count

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

    # 获取签约用户总数
    def get_cache_subscribe_customer_count(self, **kwargs):
        join_ents = kwargs.get("join_ents")
        where = ["a.`level` >= 90 and a.`level` <= 120 and b.longitude>1 and b.longitude is not null"]
        if join_ents:
            where.extend(base_db.filter(','.join(join_ents), value='a.service_enterprise_id in ({})'))
        else:
            where.extend(base_db.filter(kwargs.get("enterprise_id"), value='a.service_enterprise_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='b.adcode="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='b.city_code="{}"'))
        _sql = """
        select
            %s
        from customer a
        left join enterprise b
        on a.enterprise_id=b.id
        """
        select = """
        count(1) as `count`
        """
        count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql)
        df = base_db.query(sql)
        print(sql)
        return df[0]["count"], count

    # 监控客户
    def get_monitor_customers(self):
        sql = """
        SELECT
            B.`id` as `id`,
            B.`name` as `name`,
            B.`sn` as 'number',
            B.`longitude` as `longitude`,
            B.`latitude` as `latitude`,
            B.`address` as `address`,
            B.`district` as 'adcode'
        FROM
            im_customer as A
        RIGHT JOIN
            crm_customer as B
        ON A.customer_id=B.id
        WHERE A.customer_id > 0
        AND  B.org_id in (SELECT oa_org.id
            FROM `oa_org`
            where
                oa_org.id = 1 or
                oa_org.id in (select oa_org.id
                    FROM oa_org where superior = 1) or
                    (oa_org.id in (select oa_org.id from oa_org
                        where oa_org.superior in (
                        select oa_org.id
                        from oa_org where superior = 1))))
        """
        customers = power_db.query(sql)
        count = len(customers)
        return customers, count

    def get_monitor_device(self, **kwargs):
        join_ents = kwargs.get("join_ents")
        where = ["a.customer_id is not null and e.imei is not null"]
        if join_ents:
            where.extend(
                base_db.filter(
                    ",".join(join_ents),
                    value='b.service_enterprise_id in ({})'))
        else:
            where.extend(
                base_db.filter(
                    kwargs.get("enterprise_id"),
                    value='b.service_enterprise_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='d.adcode="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='d.city_code="{}"'))
        _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 enterprise d
        on b.enterprise_id=d.id
        left join monitor_device e
        on e.monitor_point_id=a.id
        """
        select = """
        c.number,
        b.name,
        e.imei,
        a.name `point`
        """
        count_sql = None
        sql, count = base_db.sql_splice(_sql,
                                        select,
                                        where=where,
                                        count_sql=count_sql)
        df = base_db.query(sql)
        return df, count

    def get_capacity(self):
        sql = """
        select
            ifnull(sum(capacity), 0) as capacity_count,
            ifnull(sum(real_capacity), 0) as real_capacity_count
        from crm_transformer
        inner join
            crm_source
        on
            crm_source.id = crm_transformer.source_id
        inner join
            crm_customer
        on
            crm_customer.id = crm_source.customer_id
        where
            crm_customer.id in (
                SELECT crm_customer.id
                from crm_customer
                INNER JOIN im_customer
                on im_customer.customer_id = crm_customer.id
                WHERE crm_customer.longitude<>0.000000 and 1 = 1 and (
                crm_customer.org_id in (
                    SELECT oa_org.id FROM `oa_org`
                    where oa_org.id = 1 or
                    oa_org.id in (select oa_org.id
                    FROM oa_org where superior = 1) or
                    (oa_org.id in (select oa_org.id from oa_org
                        where oa_org.superior in (
                            select oa_org.id
                            from oa_org where superior = 1))))))
        """
        count = power_db.query(sql)
        return count[0]

    def get_cache_capacity(self, **kwargs):
        where = ["crm_customer.longitude<>0.000000"]
        if kwargs.get("enterprise_id", ["1"]) != ["1"]:
            where.extend(
                base_db.filter(
                    kwargs.get("enterprise_id"),
                    value='crm_customer.org_id={}'))
        where.extend(base_db.filter(kwargs.get("province"), value='crm_customer.province="{}"'))
        where.extend(base_db.filter(kwargs.get("city"), value='crm_customer.city="{}"'))
        _sql = """
        select
            %s
        from crm_transformer
        inner join
            crm_source
        on
            crm_source.id = crm_transformer.source_id
        inner join
            crm_customer
        on
            crm_customer.id = crm_source.customer_id
        inner join
            im_customer
        on im_customer.customer_id = crm_customer.id 
        """
        select = """
        ifnull(sum(capacity), 0) as capacity_count,
        ifnull(sum(real_capacity), 0) as real_capacity_count
        """
        count_sql = None
        sql, count = power_db.sql_splice(_sql,
                                         select,
                                         where=where,
                                         count_sql=count_sql)
        df = power_db.query(sql)
        return df[0]

        # if str(ent).isdigit():
        #     if int(ent) == 1:
        #         ent_filter = " "
        #     else:
        #         ent_filter = " and crm_customer.org_id=%s" % ent
        # else:
        #     ent_filter = " "
        #
        # sql = """
        # select
        #     ifnull(sum(capacity), 0) / 1000 as capacity_count,
        #     ifnull(sum(real_capacity), 0) / 1000 as real_capacity_count
        # from crm_transformer
        # inner join
        #     crm_source
        # on
        #     crm_source.id = crm_transformer.source_id
        # inner join
        #     crm_customer
        # on
        #     crm_customer.id = crm_source.customer_id
        # where
        #     crm_customer.id in (
        #         SELECT crm_customer.id
        #         from crm_customer
        #         INNER JOIN im_customer
        #         on im_customer.customer_id = crm_customer.id
        #         WHERE crm_customer.longitude<>0.000000 {})
        # """.format(ent_filter)
        # capacity = power_db.query(sql)[0]
        # return capacity

    def get_electricity_consumption(self):
        sql = """
        SELECT
            SUM(
            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 total,
            `year_month` as 'current_year_month',
            CAST(SUBSTRING(`year_month`, 6) as SIGNED) as `month`
        FROM io_billing
        WHERE
            LEFT(`year_month`, 4)<=YEAR(now()) and
            LEFT(`year_month`, 4)>=YEAR(now()) - 1
        GROUP BY `year_month`
        ORDER BY `year_month`
        """
        rst = power_db.query(sql)
        now_time = datetime.date.today()
        before_one_month = str(now_time - relativedelta(months=+1))[:7]
        before_two_month = str(now_time - relativedelta(months=+2))[:7]
        before_one_year = str(now_time - relativedelta(years=+1) -
                              relativedelta(months=+1))[:7]
        year_and_year = 0.0
        month_and_month = 0.0
        for i in rst:
            if i["current_year_month"] == before_one_month:
                before_one_month = i
            elif i["current_year_month"] == before_two_month:
                before_two_month = i
            elif i["current_year_month"] == before_one_year:
                before_one_year = i
        now_year_month = before_one_month
        if isinstance(before_one_month, dict) and isinstance(
                before_two_month, dict):
            month_and_month = (
                before_one_month["total"] -
                before_two_month["total"]) / before_two_month["total"]
        if isinstance(before_one_year, dict) and isinstance(
                now_year_month, dict):
            year_and_year = (now_year_month["total"] - before_one_year["total"]
                             ) / before_one_year["total"]

        year_on_year = year_and_year
        month_on_month = month_and_month
        to_year_rst = filter(
            lambda x: x["current_year_month"][:4] == str(now_time)[:4], rst)
        data = {
            "data": to_year_rst,
            "year_on_year": year_on_year,
            "month_on_month": month_on_month,
        }
        return data


class StatisticsRepo(object):
    def inspection_achievement_rate(self):
        sql = """
        select
            date_format(work_date, "%Y-%m") as `current_year_month`,
            month(work_date) as `month`,
            year(work_date) as `year`,
            ifnull(count(`status`='completed' or null), 0) as completed,
            ifnull(count(`status`), 0) total,
            if(ifnull(count(`status`), 0)>0,
                ifnull(count(`status`='completed' or null),
                0
            )/ifnull(count(`status`), 0),0.0) as rate
        from inspection_task
        where
            deleted=0 and
            year(work_date)<=YEAR(now()) and
            year(work_date)>=YEAR(now()) - 1
        group by
            year(work_date), month(work_date),
            date_format(work_date, "%Y-%m")
        order by month(work_date)
        """
        inspections_rate = base_db.query(sql)
        now_time = datetime.date.today()
        before_one_month = str(now_time - relativedelta(months=+1))[:7]
        before_two_month = str(now_time - relativedelta(months=+2))[:7]
        before_one_year = str(now_time - relativedelta(years=+1) -
                              relativedelta(months=+1))[:7]
        year_and_year = 0.0
        month_and_month = 0.0

        for i in inspections_rate:
            if i["current_year_month"] == before_one_month:
                before_one_month = i
            elif i["current_year_month"] == before_two_month:
                before_two_month = i
            elif i["current_year_month"] == before_one_year:
                before_one_year = i
        now_year_month = before_one_month
        if isinstance(before_one_month, dict) and isinstance(
                before_two_month, dict):
            month_and_month = (
                before_one_month["completed"] -
                before_two_month["completed"]) / before_two_month["total"]
        if isinstance(before_one_year, dict) and isinstance(
                now_year_month, dict):
            year_and_year = (
                now_year_month["completed"] -
                before_one_year["completed"]) / before_one_year["total"]

        year_on_year = year_and_year
        month_on_month = month_and_month
        inspections_rate = filter(lambda x: x["year"] == now_time.year,
                                  inspections_rate)
        data = {
            "rate": inspections_rate,
            "year_on_year": year_on_year,
            "month_on_month": month_on_month,
        }
        return data

    def get_customer_info(self, customer_id):
        where = ["A.id=%s" % customer_id]
        sql = """
        SELECT
            A.id,
            A.`code`,
            A.`name` as `customer_name`,
            A.`service_staff_id`,
            B.`nickname` as `service_staff_name`
            from customer A
        RIGHT JOIN users B
        ON A.service_staff_id=B.id
        """
        sql = ' WHERE '.join([sql, ' and '.join(where)])
        rst = base_db.query(sql)
        return rst

    def get_customer_number(self, number_id):
        video = "http://hls01open.ys7.com/openlive/c" \
                "c9073571e0c471ca4224debb3ac5eca.m3u8"
        sql = """
        select
        A.`id`,
        A.`number`,
        A.`customer_id`,
        E.name as customer_name,
        E.service_staff_id,
        F.nickname service_staff_name,
        A.`addr`,
        A.transformer_capacity,
        A.real_capacity,
        count(distinct DDE.id) transformer_count,
        group_concat(distinct D.name) service_team_name,
        group_concat(distinct CD.phone) service_team_phone,
        CONVERT(group_concat(distinct D.id), SIGNED) service_team_id,
        concat("[", group_concat(distinct
        concat("{",'"id":', G.id, ","),
        concat('"addr":"', ifnull(G.addr, ""), '",'),
        concat('"ad_code":"', ifnull(G.ad_code, ""), '",'),
        concat('"city_code":"', ifnull(G.city_code, ""), '",'),
        concat('"operation_level":"', ifnull(G.operation_level, ""), '",'),
        concat('"status":', 1, ","),
        concat('"drawing":', "[],"),
        concat('"videos":', '["%s"],'),
        concat('"name":"',ifnull(G.name, ""), '"}')),"]"
        ) stations,
        concat("[", group_concat(distinct
        concat("{",'"id":', H.id, ","),
        concat('"name":"', ifnull(H.name, ""), '",'),
        concat('"type":"', ifnull(I.type, ""), '",'),
        concat('"factory":"', ifnull(I.manufacturer, ""), '",'),
        concat('"model":"', ifnull(I.model, ""), '",'),
        concat('"code":"', ifnull(I.production_code, ""), '",'),
        concat('"production_date":"',ifnull(I.production_date, ""), '"}')),"]"
        ) equipments,
        max(B.voltage_level) voltage_level
        from electricity_user A
        left join electrical_info B
        on A.id=B.electricity_user_id
        left join ele_team C
        on A.id=C.electricity_user_id
        left join team D
        on D.id=C.team_id
        left join users CD
        on CD.id=D.team_headman_id
        left join electrical_info DD
        on DD.electricity_user_id=A.id
        left join (select a.electricity_user_id, a.id from equipments a
                  left join equipment_data b
                  on a.equipment_data_id=b.id
                  where b.type in (1201,1202)) DDE
        on DDE.electricity_user_id=A.id
        left join customer E
        on A.customer_id=E.id
        left join users F
        on E.service_staff_id=F.id
        left join power_station G
        on G.electricity_user_id=A.id
        left join equipments H
        on H.electricity_user_id=A.id
        left join equipment_data I
        on H.equipment_data_id=I.id
        where ((H.id is not null and I.id is not null) or H.id is null) and A.id=%s
        group by A.id

        """ % (video, number_id)
        df = base_db.query_df(sql)
        df["stations"] = df['stations'].apply(base_db.json_loads_f)
        df["equipments"] = df['equipments'].apply(base_db.json_loads_f)
        return df.to_dict(orient="records")

    def get_customer_station(self, customer_id):
        sql = """
        SELECT
            `name`,
            `id`,
            addr,
            city_code,
            ad_code,
            operation_level,
            `status`
        FROM power_station
        WHERE customer_id=%s
        """ % customer_id
        rst = base_db.query(sql)
        return rst

    def get_teams_by_leader_id(selfm, leader_id):
        sql = """
        SELECT
            C.id,
            C.`name`,
            C.`type`
        FROM
        team C
        WHERE C.leader_id=%s
        """ % leader_id
        rst = base_db.query(sql)
        return rst


power_oa_repo = PowerOARepo()
statistics_repo = StatisticsRepo()
