elab_mvp/report_push.py

from mysql_db import MysqlDB
import datetime
from itertools import groupby
from xlwt_util import XlwtUtil
from file_util import FileUtil
from email_util import EmailUtil


class ReportPush(object):
    """
        报表推送功能实现类
    """
    pass

    # 1:总浏览量
    #
    #   集团部分+项目部分!!!

    #  ----项目PV--权限项目范围内，求和
    # 参数：时间区间 和 项目列表
    sql_1_1 = """
            SELECT SUM(pv) AS pv1 FROM a_idfa_behavior_sum
            WHERE report_d >= %s and report_d <= %s AND house_id IN %s
    """

    # ----集团PV--权限项目范围内，求和
    # 参数：数据区间和项目列表
    sql_1_2 = """
        SELECT SUM(pv) AS pav2 FROM a_behavior_brand_mini_day
        WHERE report_d >= %s and report_d <= %s AND house_id IN %s
    """
    # 2:总浏览人数
    #
    # 参数，数据区间， 项目列表
    sql_1_3 = """
            SELECT
                count(
                    DISTINCT IFNULL(mobile, user_id)
                ) as people
            FROM
                (
                    SELECT
                        A.user_id,
                        B.mobile
                    FROM
                        a_idfa_behavior_sum A
                    LEFT JOIN d_user B ON A.user_id = B.user_id
                    WHERE
                        A.report_d >= %s and A.report_d <= %s
                    AND A.house_id IN %s
                    UNION
                        SELECT
                            A.brand_user_id AS user_id,
                            B.mobile
                        FROM
                            a_behavior_brand_mini_day A
                        LEFT JOIN a_brand_app_customer B ON A.brand_user_id = B.brand_customer_id
                        WHERE
                            A.report_d >= %s and A.report_d <= %s
                        AND A.house_id IN %s
                ) t1
    """

    # 3：新增获客
    sql_1_4 = """
            SELECT
                count(
                    DISTINCT IFNULL(mobile, user_id)
                )
            FROM
                (
                    SELECT
                        user_id,
                        mobile,
                        created
                    FROM
                        d_user
                    WHERE
                        created >= %s
                    AND created <= %s
                    AND house_id IN %s
                    AND source ='3'
                    UNION
                        SELECT
                            brand_customer_id AS user_id,
                            mobile,
                            rlat_created
                        FROM
                            a_brand_app_customer_house_rlat
                        WHERE
                            rlat_created >= %s
                        AND rlat_created <= %s
                        AND rlat_house_id IN %s
                        AND cust_house_flag ='1'
                ) t1
    """

    # 4:新增获电
    sql_1_5 = """
        SELECT
            COUNT(DISTINCT mobile)
        FROM
            (
                SELECT
                    user_id,
                    mobile,
                    wx_phone_time AS created
                FROM
                    d_user
                WHERE
                    wx_phone_time >= %s
                AND wx_phone_time <= %s
                AND house_id IN %s
                and source = '3'
                UNION
                    SELECT
                        brand_customer_id,
                        mobile,
                        houdian_time AS created
                    FROM
                        (
                            SELECT
                                *,
                                CASE
                            WHEN rlat_created > shouquan_time THEN
                                rlat_created
                            ELSE
                                shouquan_time
                            END AS houdian_time
                            FROM
                                a_brand_app_customer_house_rlat
                            WHERE
                                mobile IS NOT NULL
                            AND rlat_house_id IN %s
                            AND cust_house_flag = '1'
                        ) t1
                    WHERE
                        houdian_time >= %s
                    AND houdian_time <= %s
            ) t1
    """

    # 5:集团小程序总浏览量（针对香港置地要命名为【CNC小程序总浏览量】）
    # 集团部分

    # 6:集团小程序总浏览人数（针对香港置地要命名为【CNC小程序总浏览人数】）
    #
    #         集团小程序整体UV（见SQL3）
    sql_1_6 = """
        SELECT
            COUNT(DISTINCT brand_user_id) AS UV4
        FROM
            a_behavior_brand_mini_day
        WHERE
            report_d > %s
        AND report_d < %s
        AND house_id IN %s
    """

    # 7: 集团小程序新增获客（针对香港置地要命名为【CNC小程序新增获客】）
    #
    #         权限项目范围内，集团维度的获客

    # 8: 集团小程序新增获电（针对香港置地要命名为【CNC小程序新增获电】）
    #
    #         权限项目范围内，集团维度的获电

    # 9.单项目小程序总浏览量
    #
    #         项目部分
    #
    # 10.单项目小程序总浏览人数
    #
    #         权限项目未授权部分求和+去重的授权部分（见SQL2）
    #

    # 11.单项目小程序新增获客
    #
    #         权限项目未授权部分求和+去重的授权部分（见SQL7）
    #

    # 12.单项目小程序新增获电
    #
    #         权限项目所有授权手机号去重（见SQL8）

    #  1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N
    sql_2_1 = """
            SELECT
                    a.house_id,
                    a.house_name,
                    ifnull(SUM(a.pv), 0),
                    SUM(a.uv),
                    SUM(a.new_cust_num),
                    SUM(a.wx_num)
                    from
                        (
            SELECT
                a.*, b.house_name,
                c.interested_num,
                d.wx_num,
                e.new_cust_num
            FROM
                (
                    SELECT
                        house_id,
                        count(
                            DISTINCT ifnull(user_id, idfa)
                        ) uv,
                        sum(session_times) session_times,
                        sum(sum_session_time) sum_session_time,
                        sum(pv) pv,
                        sum(page_num) page_num
                    FROM
                        a_idfa_behavior_sum
                    WHERE
                        report_d >= %s
                    AND report_d <= %s
                    GROUP BY
                        house_id
                ) a
            JOIN d_house b ON a.house_id = b.house_id
            LEFT JOIN (
                SELECT
                    house_id,
                    count(DISTINCT customer_id) interested_num
                FROM
                    f_interested_custlist
                WHERE
                    report_d >= %s
                AND report_d <= %s
                GROUP BY
                    house_id
            ) c ON a.house_id = c.house_id
            LEFT JOIN (
                SELECT
                    house_id,
                    count(DISTINCT mobile) wx_num
                FROM
                    f_customer_dynamic
                WHERE
                    dynamic IN (1, 2, 4)
                AND report_d >= %s
                AND report_d <= %s
                GROUP BY
                    house_id
            ) d ON a.house_id = d.house_id
            LEFT JOIN (
                SELECT
                    house_id,
                    count(DISTINCT user_id) new_cust_num
                FROM
                    d_user
                WHERE
                    source IN (1, 2, 3, 4, 10)
                AND created >= %s
                AND created < DATE_ADD(
                    %s, INTERVAL 1 DAY
                )
                GROUP BY
                    house_id
            ) e ON a.house_id = e.house_id)
            a
                    GROUP BY
                        a.house_id,
                        a.house_name
                    order by a.pv desc
    """

    # 2.默认值/006_大麦（集团）/集团项目排行榜v1.3/集团排行榜
    sql_2_2 = """
                select 
                    x.brand_id,
                    x.house_id,
                    x.house_name,
                    ifnull(x.pv, 0),
                    x.uv,
                    x.new_cust,
                    x.shouquan_cust
                     from (
                    SELECT
                        c.pv,
                        c.uv,
                        a.brand_id,
                        a.house_id,
                        a.house_name,
                        a.brand_name,
                        ifnull(b.house_layout_num, 0) house_layout_num,
                        ifnull(d.launch_time, '--') launch_time,
                        c.new_cust,
                        c.shouquan_cust,
                        c.revisit_cust
                    FROM
                        (
                            SELECT
                                brand_id,
                                ifnull(house_id, '0') house_id,
                                sum(pv) pv,
                                count(DISTINCT brand_user_id) uv,
                                count(
                                    DISTINCT CASE
                                    WHEN is_new_user = 1 THEN
                                        brand_user_id
                                    END
                                ) new_cust,
                                count(
                                    DISTINCT CASE
                                    WHEN is_shouquan_user = 1 THEN
                                        brand_user_id
                                    END
                                ) shouquan_cust,
                                count(
                                    DISTINCT CASE
                                    WHEN is_new_user = 0 THEN
                                        brand_user_id
                                    END
                                ) revisit_cust
                            FROM
                                a_behavior_brand_mini_day
                            WHERE
                                report_d >= %s
                            AND report_d <= %s
                            GROUP BY
                                brand_id,
                                ifnull(house_id, '0')
                        ) c
                    LEFT JOIN (
                        SELECT
                            house_id,
                            count(1) house_layout_num
                        FROM
                            d_content_layout
                        WHERE
                            `status` = '1'
                        AND house_id <> 1
                        GROUP BY
                            house_id
                        UNION ALL
                            SELECT
                                bb.brand_id house_id,
                                count(1) house_layout_num
                            FROM
                                d_content_layout aa
                            JOIN d_house bb ON aa.house_id = bb.house_id
                            WHERE
                                aa.`status` = '1'
                            AND bb. STATUS = '1'
                            AND aa.house_id <> 1
                            GROUP BY
                                bb.brand_id
                    ) b ON c.house_id = b.house_id
                    JOIN d_house a ON a.house_id = c.house_id
                    AND a.brand_id = c.brand_id
                    LEFT JOIN d_house_attr d ON c.house_id = d.house_id
                    AND c.brand_id = d.brand_id ) x
    """

    # 默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细
    sql_3_1 = """
            SELECT
                house_id,
                house_name,
                label_wx,
                COUNT(a.id) as counts
            FROM
                d_user_attr a
            LEFT JOIN d_scene b ON a.scene = b. CODE
            WHERE
                a.source IN (1, 2, 3, 4, 10)
            AND a.report_d >= %s
            AND a.report_d <= %s
            GROUP BY
                house_id,
                house_name,
                label_wx
    """

    # 默认值/006_大麦（集团）/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细
    sql_3_2 = """
        select  
            brand_id,
            x.brand_name,
            house_id,
            house_name,
            label_wx, 
            COUNT(1)
	    from						
	        (SELECT
            a.scene,
            a.brand_id,
            b.*, a.share_brand_customer_id,
            '2' adviser_agent,
            a.house_id house_id,
            c.house_name house_name,
            c.brand_name
        FROM
                (
                    SELECT
                        scene,
                        brand_id,
                        share_brand_customer_id,
                        house_id
                    FROM
                        d_brand_app_customer
                    WHERE
                        created >= %s
                    AND created < DATE_ADD(
                        %s, INTERVAL 1 DAY
                    )
                    UNION ALL
                        SELECT
                            scene,
                            brand_id,
                            share_brand_customer_id,
                            brand_id house_id
                        FROM
                            d_brand_app_customer
                        WHERE
                            created >= %s
                        AND created < DATE_ADD(
                            %s, INTERVAL 1 DAY
                        )
                ) a
            LEFT JOIN d_scene b ON a.scene = b. CODE
            JOIN d_house c ON a.house_id = c.house_id
            AND a.brand_id = c.brand_id) x
            group by x.brand_id, x.brand_name, x.house_id, x.house_name, x.label_wx
    """

    # 根据任务id获取推送客户信息
    sql_4 = """
        select a.task_key, a.customer_id, b.customer_type, b.name, b.mail, GROUP_CONCAT(c.house_or_brand_id) as ids
        from report_task_info a left join report_push_customer_info b on b.id = a.customer_id
        left join report_customer_authority_info c on b.id = c.customer_id
        where a.task_key = %s and a.status = b.status = c.status = 1
        group by a.task_key, a.customer_id, b.customer_type, b.name, b.mail
    """

    # 根据集团id获取项目id
    sql_5 = """
        select house_id, house_name from d_house where brand_id = %s and house_id > 300
    """

    sql_5_1 = """
        select house_id from d_house where brand_id in %s    
    """

    sql_6 = """insert into report_push_log(name, mail, report_name, push_time, send_status, status, error_message) values(%s, %s, 
    %s, now(), %s, 1, %s) """

    # 根据项目id获取集团id和名称
    sql_7 = """
        select a.brand_id, a.brand_name from d_house a where a.house_id = %s
    """

    sql_8 = """
        select DISTINCT a.brand_id from d_house a where a.house_id in %s
    """

    def __init__(self, db_name):
        self.db = MysqlDB(db_name)
        pass

    mails = ['1285211525@qq.com', 'liutt@elab-plus.com']

    # 线上
    url_online = "http://dm-api.elab-plus.cn/elab-marketing-sms//mail/sendEmailForReport"

    # 开发环境
    url_dev = "http://43.254.221.77:5555/elab-marketing-sms//mail/sendEmailForReport"  # 接口地址

    # 测试环境
    url_test = "http://gatewaytest.elab-plus.com/elab-marketing-sms//mail/sendEmailForReport"

    url = url_dev

    # 消息头数据线上
    headers_online = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36',
        'Referer': url_online,
    }

    # 开发环境
    headers_dev = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36',
        'Referer': url_dev,
    }

    def report_push(self, task_key):
        message = {}
        try:
            report_data = self.report_data_query(task_key)
            message[1] = '数据查询成功:{}'.format(len(report_data))
            time_rang = self.get_time_range(task_key)
            xu = XlwtUtil()
            send_info = xu.create_excel(report_data, time_rang, task_key)
            message[2] = '报表文件生成成功'
            email_util = EmailUtil()
            title, content = self.get_title_content(task_key, time_rang)
            logs = []
            for value in send_info:
                log_data = []
                try:
                    if value[2]:
                        # result = email_util.send_mail_by_admin(title, content, value[1], value[2], value[3], self.url_online, self.headers_online)
                        # if result:
                        #     log_data = [value[0], value[1], value[2], 1, 'success!!!']
                        # else:
                        result = email_util.send_mail_by_admin(title, content, value[1], value[2], value[3], self.url_dev, self.headers_dev)
                        if not result:
                            log_data = [value[0], value[1], value[2], -1, '第二次发送失败']
                except Exception as e:
                    result = email_util.send_mail_by_admin(title, content, value[1], value[2], value[3], self.url_dev,
                                                           self.headers_dev)
                    if not result:
                        log_data = [value[0], value[1], value[2], -1, str(e)]
                    print(str(e))
                logs.append(log_data)
            message[3] = '邮件发送成功'
            message['data'] = send_info
            self.db.add_some(self.sql_6, logs)
            self.db.close()
            FileUtil.remove_files(7, xu.save_path)
        except Exception as e:
            print(str(e))
            message['error'] = str(e)
            pass
        finally:
            return message

    def report_push_test(self, task_key):
        message = {}
        try:
            report_data = self.report_data_query(task_key)
            message[1] = '数据查询成功:{}'.format(len(report_data))
            time_rang = self.get_time_range(task_key)
            xu = XlwtUtil()
            send_info = xu.create_excel(report_data, time_rang, task_key)
            message[2] = '报表文件生成成功'
            email_util = EmailUtil()
            title, content = self.get_title_content(task_key, time_rang)
            logs = []
            for value in send_info:
                log_data = []
                try:
                    if value[2]:
                        for mail in self.mails:
                            result = email_util.send_mail_by_admin(title, content, mail, value[2], value[3], self.url_dev, self.headers_dev)
                            if result:
                                log_data = [value[0], value[1], value[2], 1, 'success!!!']
                            else:
                                result = email_util.send_mail_by_admin(title, content, mail, value[2], value[3], self.url_online, self.headers_online)
                                if not result:
                                    log_data = [value[0], value[1], value[2], -1, '第二次发送失败']
                except Exception as e:
                    log_data = [value[0], value[1], value[2], -1, str(e)]
                    print(str(e))
                logs.append(log_data)
            message[3] = '邮件发送成功'
            message['data'] = send_info
            self.db.add_some(self.sql_6, logs)
            self.db.close()
            FileUtil.remove_files(7, xu.save_path)
        except Exception as e:
            print(str(e))
            message['error'] = str(e)
            pass
        finally:
            return message

    def get_title_content(self, task_key, time_range):
        if task_key in (1, 4):
            return '移动案场订阅日报[{}]'.format(time_range[0]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅。'.format(time_range[0], time_range[1])
        elif task_key in (2, 3):
            return '移动案场订阅周报[{}]至[{}]'.format(time_range[0], time_range[1]), '本期间内【{}】至【{}】的数据报告已经准备完成。请点击附件查阅.'.format(time_range[0], time_range[1])

    def report_data_query(self, task_key):
        """
            定时任务推送数据准备
        :param task_key:
        :return:
        """
        result = {}
        try:
            # 根据任务key获取需要推送的客户以及可以的权限
            customers = self.db.select(self.sql_4, [task_key])
            # a.task_key, a.customer_id, b.customer_type, b.`name`, b.mail, GROUP_CONCAT(c.house_or_brand_id)
            time_range = self.get_time_range(task_key)
            all_time_rang = self.get_time_range(9999)
            # 有限时间范围内的数据
            xcx_top_data = self.xcx_top(time_range)
            brand_top_data = self.brand_top(time_range)
            customer_channel_details_data = self.customer_channel_details(time_range)
            brand_customer_channel_details = self.brand_customer_channel_details(time_range)
            # 所有历史数据
            xcx_top_data_all = self.xcx_top(all_time_rang)
            brand_top_data_all = self.brand_top(all_time_rang)
            for customer in customers:
                customer_data = {}
                name = customer[3]
                mail = customer[4]
                customer_type = customer[2]
                house_ids = []
                brand_id_list = []
                if customer_type == 1:
                    # 项目
                    ids = customer[5]
                    if str(ids).find(',') != -1:
                        house_ids = [x for x in str(ids).split(',')]
                    else:
                        house_ids = [ids]
                    brand_id_list = self.get_brand_ids_by_house_ids(house_ids)
                    pass
                elif customer_type == 2:
                    # 集团
                    brand_ids = customer[5]
                    if str(brand_ids).find(',') != -1:
                        brands = [x for x in str(brand_ids).split(',')]
                    else:
                        brands = [brand_ids]
                    for id in brands:
                        house_ids.extend([x[0] for x in self.get_house_ids_by_brand_id(id)])
                    brand_id_list = brands

                result_data_1 = []
                result_data_2 = []
                result_data_3 = []
                result_data_4 = []
                result_data_5 = []
                result_data_7 = []
                result_data_8 = []

                all_house_ids = self.get_house_ids_by_brand_ids(brand_id_list)
                xcx_top_data_part = self.filter_by_house_ids(xcx_top_data, all_house_ids)
                brand_top_data_part = self.filter_by_brand_ids(brand_top_data, brand_id_list)
                xcx_top_data_all_part = self.filter_by_house_ids(xcx_top_data_all, all_house_ids)
                brand_top_data_all_part = self.filter_by_brand_ids(brand_top_data_all, brand_id_list)

                # 1 数据总览 12个统计指标
                data_overview = self.data_overview(time_range, house_ids, xcx_top_data_part, brand_top_data_part)
                result_data_1.extend(data_overview)
                # 4：单个项目小程序数据排行榜
                # 排名	项目名称	总浏览量	总浏览人数	新增获客	新增获电
                for index, x in enumerate(xcx_top_data_part):
                    if x[0] in house_ids:
                        result_data_4.append([index + 1, x[1], x[2], x[3], x[4], x[5]])
                # 5: 集团项目数据排行榜
                # 排名	项目名称	总浏览量	总浏览人数	新增获客	新增获电
                if customer_type == 2:
                    for index, x2 in enumerate(brand_top_data_part):
                        if x2[1] in house_ids or x2[0] in brand_id_list:
                            result_data_5.append([index + 1, x2[2], x2[3], x2[4], x2[5], x2[6]])
                        pass
                    pass
                elif customer_type == 1:
                    for index, x1 in enumerate(brand_top_data_part):
                        if x1[1] in house_ids:
                            result_data_5.append([index + 1, x1[2], x1[3], x1[4], x1[5], x1[6]])
                else:
                    pass
                # 2: 项目数据排行榜
                # 排名 项目名称 总浏览量 总浏览人数 新增获客 新增获电
                house_with_brand_data = self.house_with_brand(xcx_top_data_part, brand_top_data_part)
                for x in house_with_brand_data:
                    if x[2] in house_ids:
                        x.pop(1)
                        x.pop(1)
                        result_data_2.append(x)

                # 3: 项目历史累计总数
                # 排名 项目名称	总浏览量	总浏览人数 新增获客 新增获电
                if customer_type == 2:
                    all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part, brand_id_list)
                    for x in all_data_history:
                        if x[2] in house_ids:
                            x.pop(1)
                            x.pop(1)
                            result_data_3.append(x)
                else:
                    all_data_history = self.house_with_brand(xcx_top_data_all_part, brand_top_data_all_part)
                    for x in all_data_history:
                        if x[2] in house_ids:
                            x.pop(1)
                            x.pop(1)
                            result_data_3.append(x)

                # 7: 单个项目小程序获客来源场景分析
                # 项目 合计 长按识别二维码 会话 公众号菜单 公众号文章 小程序历史列表 扫一扫二维码 搜索 相册选取二维码 其他小程序 其他
                for x in customer_channel_details_data:
                    if x[0] in house_ids:
                        result_data_7.append(x)

                # 8: 集团项目获客来源场景分析
                if customer_type == 2:
                    for x in brand_customer_channel_details:
                        if x[2] in house_ids or x[0] in brand_id_list:
                            result_data_8.append(x)
                elif customer_type == 1:
                    for x in brand_customer_channel_details:
                        if x[2] in house_ids:
                            result_data_8.append(x)

                # 6: 项目获客来源场景分析
                result_data_6 = self.house_with_brand_for_share(result_data_7, result_data_8)

                customer_data[1] = result_data_1
                result_data_2.sort(key=lambda obj: obj[0])
                customer_data[2] = result_data_2
                result_data_3.sort(key=lambda obj: obj[0])
                customer_data[3] = result_data_3
                result_data_4.sort(key=lambda obj: obj[0])
                customer_data[4] = result_data_4
                result_data_5.sort(key=lambda obj: obj[0])
                customer_data[5] = result_data_5
                self.sort(result_data_6, 1)
                customer_data[6] = result_data_6
                result_data_7_format = []
                for x in result_data_7:
                    house_name = x[1]
                    ele = [house_name]
                    data = x[2:]
                    total = sum(data)
                    ele.append(total)
                    ele.extend(data)
                    result_data_7_format.append(ele)
                self.sort(result_data_7_format, 1)
                customer_data[7] = result_data_7_format
                result_data_8_format = []
                for x in result_data_8:
                    ele = []
                    house_name = x[3]
                    data = x[4:]
                    total = sum(data)
                    ele.append(house_name)
                    ele.append(total)
                    ele.extend(data)
                    result_data_8_format.append(ele)
                self.sort(result_data_8_format, 1)
                customer_data[8] = result_data_8_format
                customer_data[0] = mail
                result[name] = customer_data
            # return result
        except Exception as e:
            result['error'] = str(e)
            pass
        finally:
            return result

    def sort(self, data, idnex):
        data.sort(key=lambda obj: obj[idnex])
        data.reverse()

    def house_with_brand_for_share(self, house_data, brand_data):
        house_ids = []
        result = []
        for x in house_data:
            if x[0] not in [a[0] for a in house_ids]:
                house_ids.append([x[0], x[1]])
        for x in brand_data:
            if x[2] not in [a[0] for a in house_ids]:
                house_ids.append([x[2], x[3]])
        for id in house_ids:
            house_id = id[0]
            house_name = id[1]
            house_result = [house_name]
            equal_house = self.equal_by_house_id(house_data, house_id, 0)
            equal_brand = self.equal_by_house_id(brand_data, house_id, 2)
            house_result_part = []
            if equal_house and equal_brand:
                equal_house = equal_house[2:]
                equal_brand = equal_brand[4:]
                for i in range(0, 10):
                    house_result_part.append(self.add(equal_house[i], equal_brand[i]))
            elif equal_house and not equal_brand:
                equal_house = equal_house[2:]
                for i in range(0, 10):
                    house_result_part.append(equal_house[i])
            elif not equal_house and equal_brand:
                equal_brand = equal_brand[4:]
                for i in range(0, 10):
                    house_result_part.append(equal_brand[i])
            house_result.append(sum(house_result_part))
            house_result.extend(house_result_part)
            result.append(house_result)
        result.sort(key=lambda obj: obj[1])
        result.reverse()
        return result

    def equal_by_house_id(self, data, house_id, index):
        for x in data:
            if x[index] == house_id:
                return x

    def filter_by_brand_ids(self, data, brand_ids):
        result = []
        for x in data:
            if x[0] in brand_ids:
                result.append(x)
        result.sort(key=lambda obj: obj[3])
        result.reverse()
        return result

    def filter_by_house_ids(self, data, house_ids):
        result = []
        for x in data:
            if x[0] in house_ids:
                result.append(x)
        result.sort(key=lambda obj: obj[2])
        result.reverse()
        return result

    def data_overview(self, time_range, house_ids, xcx_top_data, brand_top_data):
        """
            统计数据总览
        :param time_range:
        :param house_ids:
        :return:
        """
        result = []
        # 1:总浏览量
        data_1_1 = self.db.select(self.sql_1_1, [time_range[0], time_range[1], house_ids])
        number_1_1 = data_1_1[0][0]
        data_1_2 = self.db.select(self.sql_1_2, [time_range[0], time_range[1], house_ids])
        number_1_2 = data_1_2[0][0]
        number_1 = self.add(number_1_1, number_1_2)
        result.append(number_1)
        # 2: 总浏览人数
        data_2 = self.db.select(self.sql_1_3, [time_range[0], time_range[1], house_ids, time_range[0], time_range[1], house_ids])
        number_2 = data_2[0][0]
        result.append(number_2)
        # 3:新增获客
        time_1 = time_range[0] + ' 00:00:00'
        time_2 = time_range[1] + ' 23:59:59'
        data_3 = self.db.select(self.sql_1_4, [time_1, time_2, house_ids, time_1, time_2, house_ids])
        number_3 = data_3[0][0]
        result.append(number_3)
        # 4:新增获电
        data_4 = self.db.select(self.sql_1_5, [time_1, time_2, house_ids, house_ids, time_1, time_2])
        number_4 = data_4[0][0]
        result.append(number_4)
        xcx_top_data_part = [x for x in xcx_top_data if x[0] in house_ids]
        brand_top_data_part = [x for x in brand_top_data if x[1] in house_ids]
        # 5 6 7 8
        number_5 = 0
        number_6 = 0
        number_7 = 0
        number_8 = 0
        for x in brand_top_data_part:
            number_5 = self.add(number_5, x[3])
            number_6 = self.add(number_6, x[4])
            number_7 = self.add(number_7, x[5])
            number_8 = self.add(number_8, x[6])

        result.append(number_5)
        result.append(number_6)
        result.append(number_7)
        result.append(number_8)
        # 9 10 11 12
        number_9 = 0
        number_10 = 0
        number_11 = 0
        number_12 = 0
        for x in xcx_top_data_part:
            number_9 = self.add(number_9, x[2])
            number_10 = self.add(number_10, x[3])
            number_11 = self.add(number_11, x[4])
            number_12 = self.add(number_12, x[5])
        result.append(number_9)
        result.append(number_10)
        result.append(number_11)
        result.append(number_12)
        return result

    def house_with_brand(self, xcx_top_data, brand_top_data, brands=None):
        """
            项目数据和集团数据的求和
        :param brands:
        :param xcx_top_data:
        :param brand_top_data:
        :return:
        """
        result = []
        house_ids = []
        for x in xcx_top_data:
            if x[0] not in house_ids:
                house_ids.append(x[0])
        for x in brand_top_data:
            if x[1] not in house_ids and x[1] is not None and int(x[1]) > 300:
                house_ids.append(x[1])
        for house_id in house_ids:
            a = []
            a_order = 0
            for index, x in enumerate(xcx_top_data):
                if str(house_id) == str(x[0]):
                    a.extend(x)
                    a_order = index + 1
            b = []
            b_order = 0
            for index, y in enumerate(brand_top_data):
                if str(house_id) == str(y[1]):
                    b.extend(y)
                    b_order = index + 1

            order = b_order if b_order > 0 else a_order
            if len(a) > 0 and len(b) > 0:
                result.append([order, b[0], a[0], a[1], self.add(a[2], b[3]), self.add(a[3], b[4]), self.add(a[4], b[5]), self.add(a[5], b[6])])
            elif len(a) > 0 and len(b) == 0:
                _a = [order, 1]
                for x in a:
                    _a.append(x)
                result.append(_a)
            elif len(a) == 0 and len(b) > 0:
                _b = [order]
                _b.extend(b)
                result.append(_b)
            else:
                pass
        if brands:
            for index, x in enumerate(brand_top_data):
                if x[0] in brands and x[1] is not None and int(x[1]) == 0:
                    _x = [index + 1]
                    _x.extend(x)
                    result.append(_x)
        result.sort(key=lambda obj: obj[3])
        result.reverse()
        return result

    def add(self, a=None, b=None):
        if a and b:
            return a + b
        elif a and not b:
            return a
        elif b and not a:
            return b
        return 0

    def xcx_top(self, time_range):
        """
            获取 1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N
        :return:
        """
        params = []
        params.extend(time_range)
        params.extend(time_range)
        params.extend(time_range)
        params.extend(time_range)
        xcx_top_data = self.db.select(self.sql_2_1, params)
        result = []
        for x in xcx_top_data:
            result.append([n for n in x])
        # xcx_top_data的结果结构
        #       0  a.house_id, 项目id
        #       1  a.house_name, 项目名称
        #       2  SUM(a.pv), 浏览总量
        #       3  SUM(a.uv), 浏览人数
        #       4  SUM(a.new_cust_num), 新增获客
        #       5  SUM(a.wx_num) 授权手机号
        result.sort(key=lambda obj: obj[2])
        result.reverse()
        return result

    def brand_top(self, time_range):
        """
            2.默认值/006_大麦（集团）/集团项目排行榜v1.3/集团排行榜
        :param task_key:
        :return:
        """
        params = [time_range[0], time_range[1]]
        brand_top_data = self.db.select(self.sql_2_2, params)
        result = []
        for x in brand_top_data:
            result.append([n for n in x])
        # brand_top_data结果的结构
        #                0 a.brand_id, 集团id
        #                1 a.house_id, 项目id
        #                2 a.house_name, 项目名称
        #                3 SUM(a.pv), 浏览总量
        #                4 SUM(a.uv), 浏览人数
        #                5 SUM(a.new_cust), 新增获客
        #                6 SUM(a.shouquan_cust) 授权手机号
        result.sort(key=lambda obj: obj[3])
        result.reverse()
        return result

    """
        数据分享类别
        1:长按识别二维码	2:会话	3:公众号菜单	4:公众号文章	5:小程序历史列表	6:扫一扫二维码	
        7:搜索	8:相册选取二维码	9:其他小程序	10:其他

    """
    share_way = {
        "长按识别二维码": 1,
        "会话": 2,
        "公众号菜单": 3,
        '公众号文章': 4,
        '小程序历史列表': 5,
        '扫一扫二维码': 6,
        '搜索': 7,
        '相册选取二维码': 8,
        '其他小程序': 9,
        '其他': 10
    }

    def customer_channel_details(self, time_range):
        """
            1.默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细
        :param task_key:
        :return:
        """
        params = []
        params.extend(time_range)
        customer_channel_details_data = self.db.select(self.sql_3_1, params)
        result = []
        for x in customer_channel_details_data:
            ele = []
            order = self.share_way.get(x[2])
            if order:
                ele.append(x[0])
                ele.append(x[1])
                ele.append(order)
                ele.append(x[3])
                result.append(ele)
        result.sort(key=lambda obj: obj[0])
        end_data = []
        for key, data in groupby(result, key=lambda obj: obj[0]):
            others_data = []
            for ot in data:
                others_data.append([x for x in ot])
            lable_data = []
            if len(others_data) > 0:
                lable_data.append(others_data[0][0])
                lable_data.append(others_data[0][1])
                for i in range(1, 11):
                    number = 0
                    for od in others_data:
                        if i == od[2]:
                            number = od[3]
                        else:
                            pass
                    lable_data.append(number)
                pass
            end_data.append(lable_data)
        # customer_channel_details_data数据结构
        # 	house_id, 项目id
        # 	house_name, 项目名称
        # 	label_wx, 分享类别
        # 	COUNT(a.id) as counts， 数量
        return end_data

    def brand_customer_channel_details(self, time_range):
        """
            2.默认值/006_大麦（集团）/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细
        :param frequency:
        :return:
        """
        params = [time_range[0], time_range[1], time_range[0], time_range[1]]
        brand_customer_channel_details_data = self.db.select(self.sql_3_2, params)
        # brand_customer_channel_details_data数据结构
        #             0 brand_id, 集团id
        #             1 x.brand_name, 集团名称
        #             2 house_id, 项目id
        #             3 house_name, 项目名称
        #             4 label_wx,  分享类别
        #             5 COUNT(1) 数量
        result = []
        for x in brand_customer_channel_details_data:
            ele = []
            order = self.share_way.get(x[4])
            if order:
                ele.append(x[0])
                ele.append(x[1])
                ele.append(x[2])
                ele.append(x[3])
                ele.append(order)
                ele.append(x[5])
                result.append(ele)
        result.sort(key=lambda obj: obj[2])
        end_data = []
        for key, data in groupby(result, key=lambda obj: obj[2]):
            others_data = []
            for ot in data:
                others_data.append([x for x in ot])
            lable_data = []
            if len(others_data) > 0:
                lable_data.extend(others_data[0][0: 4])
                for i in range(1, 11):
                    number = 0
                    for od in others_data:
                        if i == od[4]:
                            number = od[5]
                        else:
                            pass
                    lable_data.append(number)
                pass
            end_data.append(lable_data)
        return end_data

    def push_log_recording(self, push_message):
        """
            报表推送日志记录
        :param push_message:
        :return:
        """
        self.db.add_some(self.sql_6, push_message)

    def get_house_ids_by_brand_id(self, brand_id):
        return self.db.select(self.sql_5, [brand_id])

    def get_brand_info_by_house_id(self, house_id):
        """
            根据项目id或者相应的集团信息
        :param house_id:
        :return:
        """
        brand_info = self.db.select(self.sql_7, [house_id])
        if len(brand_info) == 1:
            return brand_info[0][0]
        return

    def get_brand_ids_by_house_ids(self, house_ids):
        brand_ids = self.db.select(self.sql_8, [house_ids])
        ids = []
        for x in brand_ids:
            if x and x[0]:
               ids.append(x[0])
        return ids

    def get_house_ids_by_brand_ids(self, brand_ids):
        result = []
        ids = self.db.select(self.sql_5_1, [brand_ids])
        for x in ids:
            if x[0] not in result:
                result.append(x[0])
        return result

    def get_time_range(self, task_key):
        """
         根据定时任务id获取时间区间
         时间格式 yyyy-mm-dd
        :param task_key:1: 日报，2：周报, 3:all
        :return:
        """
        now_time = datetime.datetime.now()
        pre_time = None
        if task_key in (2, 3):
            # 上周，上周一到上周天
            pre_time = now_time + datetime.timedelta(days=-7)
            now_time = now_time + datetime.timedelta(days=-1)
            pass
        elif task_key in (1, 4):
            # 昨天
            pre_time = now_time + datetime.timedelta(days=-1)
            now_time = now_time + datetime.timedelta(days=-1)
            pass
        elif task_key in (9999, 9999):
            # 不限时间
            pre_time = now_time + datetime.timedelta(days=-2999)
        return [pre_time.strftime('%Y-%m-%d'), now_time.strftime('%Y-%m-%d')]


if __name__ == '__main__':
    rp = ReportPush('linshi')
    times = rp.get_time_range(1)