elab_mvp/jianye_report.py

from mysql_db import MysqlDB
from sql import Sql
from report_public_funs_utils import ReportPublicFunsUtils as rpfu
from mail_content_text import MailContentText
from email_util import EmailUtil
from file_util import FileUtil
from report_file_utils import ReportFileUtils


class JianYeReport(object):
    """
        建业报表数据处理
    """
    # customer_mails = ['liutt@elab-plus.com', 'binrenzhang@qq.com', 'plf@centralchina.com']
    customer_mails = ['liutt@elab-plus.com', 'binrenzhang@qq.com', 'zhuyl@elab-plus.com', 'lijm@elab-plus.com', 'xuanxc@elab-plus.com']

    #
    index_type = [
        'fenxianghuodian',
        'liebianhuodian',
        'saomadaofang',
        'quanminjingjiren',
        'laoyezhu',
        'baobeichenggong',
        'baobeidaofang'
    ]

    head_1 = ['城市', '项目名称', '浏览量', '浏览人数', '新增用户', '新增获电', '推荐用户', '分享获电', '裂变获电',
              '扫码到访数', '全民经纪人注册数', '老业主注册数', '报备成功数', '报备到访数']
    brand_id = '13'

    sheet_names_1 = ['当日数据', '当月数据', '上线以来所有数据']

    def __init__(self):
        self.db = MysqlDB('bi_report')
        time_range_1 = rpfu.get_prd_day()
        time_range_2 = rpfu.get_time_range_month()
        time_range_3 = rpfu.get_all_time_data_range()
        self.pv_data_1 = self.pv_count(time_range_1)
        self.pv_data_2 = self.pv_count(time_range_2)
        self.pv_data_3 = self.pv_count(time_range_3)

    def get_city_house_id(self):
        return self.db.select(Sql.sql_1)

    def get_report_customers(self, task_key):
        return self.db.select(Sql.sql_2, [task_key])

    def get_mail_title(self, type, region_name, name):
        """
            获取邮件名称
        :param region_name:
        :param type:1：项目，2：集团，3：区域
        :param name: 项目 区域名称
        :return:
        """
        month_day = rpfu.get_montho_day()
        if type == 1:
            return '[{}]{}数据报表_{}'.format(month_day, region_name, name)
        elif type == 2:
            return '[{}]建业云集团数据报表_{}'.format(month_day, name)
        elif type == 3:
            return '[{}]建业云{}数据报表_{}'.format(month_day, region_name, name)

    def get_mail_content(self, customer_type):
        """
            根据客户类型获取邮件正文
        :param customer_type:
        :return:
        """
        if customer_type == 2:
            return MailContentText.text_1
        else:
            return '本期数据报告已经准备完成,请点击附件查阅.'

    # 项目级别的统计
    def house_data_detail(self, time_range):
        result = self.db.select(Sql.sql_8, [time_range[0], time_range[1]])
        return result

    def pv_count(self, time_range):
        """
            新的用户行为统计
        :param time_range:
        :return:
        """
        sql_result = self.db.select(Sql.sql_13, [time_range[0], time_range[1]])
        return sql_result

    def user_data_volume_statistics(self, time_range, house_ids):
        """
            用户浏览量，人数，新增获客，新增获点数据统计
        :param time_range:
        :param house_ids:
        :return:
        """
        # 1:总浏览量
        result = []
        data_1_1 = self.db.select(Sql.sql_3, [time_range[0], time_range[1], house_ids])
        number_1_1 = data_1_1[0][0]
        data_1_2 = self.db.select(Sql.sql_4, [time_range[0], time_range[1], house_ids])
        number_1_2 = data_1_2[0][0]
        number_1 = rpfu.add(number_1_1, number_1_2)
        result.append(number_1)
        # 2: 总浏览人数
        data_2 = self.db.select(Sql.sql_5,
                                [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(Sql.sql_6, [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(Sql.sql_7, [time_1, time_2, house_ids, house_ids, time_1, time_2])
        number_4 = data_4[0][0]
        result.append(number_4)
        return result

    def region_house_id(self):
        return self.db.select(Sql.sql_11)

    def get_recommend_data(self, time_range):
        return self.db.select(Sql.sql_9, [time_range[0], time_range[1]])

    def get_house_id_by_brand_id(self, brand_id):
        result = []
        for x in self.get_city_house_id():
            if str(x[0]) == str(brand_id) and x[1] is not None and len(x[1]) > 4:
                result.append(x[1])
        return result

    def get_pv_data(self, time_type):
        pv_data = None
        if time_type == 1:
            pv_data = self.pv_data_1
        elif time_type == 2:
            pv_data = self.pv_data_2
        elif time_type == 3:
            pv_data = self.pv_data_3
        return pv_data

    def brand_pv_by_time_type(self, time_type):
        pv_data = self.get_pv_data(time_type)
        if pv_data:
            for x in pv_data:
                if str(x[0]) == self.brand_id and x[2] is not None and x[2] == '集团':
                    return x[3:]
        return [0, 0, 0, 0]

    def brand_data_of_time(self, time_range, time_type):
        result = []
        data_1 = self.brand_pv_by_time_type(time_type)
        data_2 = []
        number_2 = 0
        for x in self.get_recommend_data(time_range):
            if str(x[0]) == str(self.brand_id):
                number_2 += x[1]
        data_2.append(number_2)
        data_3 = []
        sql_data_3 = self.db.select(Sql.sql_10, [time_range[0], time_range[1]])
        for key in self.index_type:
            number = 0
            for x in sql_data_3:
                if str(x[0]) == str(key):
                    number = x[1]
            data_3.append(number)
        result.extend(data_1)
        result.extend(data_2)
        result.extend(data_3)
        return result

    def brand_data(self):
        """
            集团数据总览, 表一。
        :return:
        """
        result = []
        time_rang_1 = rpfu.get_prd_day()
        time_rang_2 = rpfu.get_time_range_month()
        time_range_3 = rpfu.get_all_time_data_range()
        result.extend(self.brand_data_of_time(time_rang_1, 1))
        result.extend(self.brand_data_of_time(time_rang_2, 2))
        result.extend(self.brand_data_of_time(time_range_3, 3))
        return result

    def get_house_pv_data(self, house_id, time_type):
        pv_data = self.get_pv_data(time_type)
        if pv_data is not None:
            for x in pv_data:
                if str(x[1]) == str(house_id):
                    return x[3:]
        return [0, 0, 0, 0]

    def house_data_of_time(self, time_range, house_ids, time_type):
        # brand_id, house_id, house_name, city
        city_info = self.get_city_house_id()
        data_1 = []
        for id in house_ids:
            sub = [id]
            sub.extend(self.get_house_pv_data(id, time_type))
            data_1.append(sub)
        data_2 = []
        for id in house_ids:
            sub = [id]
            number = 0
            for x in self.get_recommend_data(time_range):
                if str(id) == str(x[0]):
                    number = x[1]
            sub.append(number)
            data_2.append(sub)
        data_3 = []
        for house_id in house_ids:
            sub = []
            for x in self.house_data_detail(time_range):
                # house_id, type, COUNT(DISTINCT customer_mobile)
                if str(house_id) == str(x[0]):
                    sub.append(x)
            house_data = [house_id]
            for key in self.index_type:
                number = 0
                for x in sub:
                    if str(key) == str(x[1]):
                        number = x[2]
                house_data.append(number)
            data_3.append(house_data)
        result = []
        for id in house_ids:
            sub = []
            for x in city_info:
                if str(id) == str(x[1]):
                    sub.extend([x[3], x[2]])
            if len(sub) == 0:
                sub.extend(['0000', '0000'])
            for x in data_1:
                if str(id) == str(x[0]):
                    sub.extend(x[1:])
            for x in data_2:
                if str(id) == str(x[0]):
                    sub.extend(x[1:])
            for x in data_3:
                if str(id) == str(x[0]):
                    sub.extend(x[1:])
            result.append(sub)
        result.sort(key=lambda obj: obj[5], reverse=True)
        return result

    def house_data(self, house_ids):
        time_range_1 = rpfu.get_prd_day()
        time_range_2 = rpfu.get_time_range_month()
        time_range_3 = rpfu.get_all_time_data_range()
        result = [self.house_data_of_time(time_range_1, house_ids, 1), self.house_data_of_time(time_range_2, house_ids, 2),
                  self.house_data_of_time(time_range_3, house_ids, 3)]
        return result

    def get_house_region_info(self):
        return self.db.select(Sql.sql_15)

    def send_mail_to_customer(self, task_key):
        """
            统计数据推送给客户， 表二
        :param: task_key
        :return:
        """
        # 邮件发送参数
        #                             mail_title,
        #                             content,
        #                             receiver,
        #                             mail_excel,
        #                             file_name,
        #                             mail_excel_1=None,
        #                             file_name_1=None
        message = {}
        send_mail_info = []
        mail_util = EmailUtil()
        rfu = ReportFileUtils()
        customers = self.get_report_customers(task_key)
        message[0] = '客户信息获取成功'
        send_mail_log = []
        brand_table_one = None
        brand_table_two = None
        try:
            save_path = FileUtil().save_path_create()
            send_data = []
            # 查询数据
            if task_key == 12:
                # 集团信息计算一次发送给多个人
                brand_table_one = self.brand_data()
                message['brand'] = 'success'
                house_ids = self.get_house_id_by_brand_id('13')
                brand_table_two = self.house_data(house_ids)
                message['house'] = 'success'
                # 总浏览量：xx ，总浏览人数:xx
                #
                # 新增获客: xx， 新增获电:xx
                #
                # 推荐用户数:xx，分享获电:xx，裂变获电:xx
                #
                # 全民经纪人注册数：xx
                #
                # 报备成功数:xx,报备到访数:xx
            for customer in customers:
                # a.task_key, b.customer_type, b.name, b.mail, b.house_or_region, a.customer_id, GROUP_CONCAT(c.house_or_brand_id) as ids
                name = customer[2]
                customer_type = customer[1]
                mail = customer[3]
                ids = customer[6]
                region_name = customer[4]
                title = self.get_mail_title(customer_type, region_name, name)
                content = self.get_mail_content(customer_type)
                file_path = save_path + '/' + title + '.xls'
                try:
                    if customer_type == 2:
                        # 集团
                        content = self.get_brand_content(content, brand_table_one)
                        send_data.append(
                            [brand_table_two, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1],
                             file_path, content, name, mail])
                        # rfu.create_excel_file(table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path)
                    elif customer_type == 1:
                        # 项目
                        table_2 = self.house_data(self.get_house_ids(ids))
                        send_data.append(
                            [table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path,
                             content, name, mail])
                        # rfu.create_excel_file(table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path)
                    elif customer_type == 3:
                        # 区域
                        table_2 = self.house_data(self.get_house_ids(ids))
                        send_data.append(
                            [table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path,
                             content, name, mail])
                        # rfu.create_excel_file(table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path)
                    else:
                        send_mail_log.append([name, mail, title, -1, '客户类型错误{}'.format(customer_type)])
                        pass
                except Exception as e:
                    print(str(e))
                    #  # name, mail, report_name, push_time, send_status, status, error_message
                    send_mail_log.append([name, mail, title, -1, '数据查询失败:{}'.format(str(e))])
            message['query_data'] = 'success'
            # 生成文件
            for data in send_data:
                try:
                    rfu.create_excel_file(data[0], data[1], data[2], data[3], data[4])
                    # [title, content, mail, file_path, title + '.xlsx', name]
                    send_mail_info.append([data[2], data[5], data[7], data[4], data[2] + '.xls', data[6]])
                except Exception as e:
                    print(e)
                    send_mail_log.append([data[6], data[7], data[2], -1, '文件创建失败：{}'.format(str(e))])
            message['file'] = 'success'
            # 发送邮件
            for mail in send_mail_info:
                try:
                    # for m in self.customer_mails:
                    result = mail_util.send_mail_by_admin(mail[0], mail[1], mail[2], mail[3], mail[4])
                    if result:
                        # name, mail, report_name, push_time, send_status, status, error_message
                        send_mail_log.append([mail[5], mail[2], mail[3], 1, 'success'])
                    else:
                        send_mail_log.append([mail[5], mail[2], mail[3], -1, 'fail'])
                except Exception as e:
                    print(str(e))
                    message['error1'] = str(e)
                    send_mail_log.append([mail[5], mail[2], mail[3], -1, '邮件发送失败:{}'.format(str(e))])
            message['mail'] = 'success'
            # 写入日志
            self.db.add_some(Sql.sql_12, send_mail_log)
            message['log'] = 'success'
        except Exception as e:
            message['error'] = str(e)
        finally:
            return message

    def get_brand_content(self, content, brand_table_one):

        content = content.format(rpfu.get_montho_day(),
                                 brand_table_one[0], brand_table_one[1], brand_table_one[2], brand_table_one[3],
                                 brand_table_one[4], brand_table_one[5],
                                 brand_table_one[6], brand_table_one[8], brand_table_one[10], brand_table_one[11],
                                 rpfu.get_month(),
                                 brand_table_one[12], brand_table_one[13], brand_table_one[14], brand_table_one[15],
                                 brand_table_one[16],
                                 brand_table_one[17],
                                 brand_table_one[18], brand_table_one[20], brand_table_one[22], brand_table_one[23],
                                 brand_table_one[24], brand_table_one[25], brand_table_one[26], brand_table_one[27],
                                 brand_table_one[28],
                                 brand_table_one[29],
                                 brand_table_one[30], brand_table_one[32], brand_table_one[34], brand_table_one[35]
                                 )
        return content

    def get_customer_info_by_id(self, customer_id):
        return self.db.select(Sql.sql_14, [customer_id])

    def send_mail_for_customer_id(self, customer_id, new_mail=None):
        """
            获取指定客户的邮件信息
        :param customer_id:
        :param new_mail:
        :return:
        """
        message = {}
        customers = self.get_customer_info_by_id(customer_id)
        message['customer'] = str(customer_id)
        send_data = []
        if customers:
            for customer in customers:
                try:
                    save_path = FileUtil().save_path_create()
                    name = customer[1]
                    mail = customer[2]
                    customer_type = customer[3]
                    ids = self.get_house_ids(customer[5])
                    region_name = customer[4]
                    title = self.get_mail_title(customer_type, region_name, name)
                    content = self.get_mail_content(customer_type)
                    file_path = save_path + '/' + title + '.xls'
                    if customer_type == 1:
                        # 项目
                        table_2 = self.house_data(ids)
                        send_data.append(
                            [table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path,
                             content, name, mail])
                    elif customer_type == 2:
                        # 集团
                        brand_table_one = self.brand_data()
                        house_ids = self.get_house_id_by_brand_id('13')
                        brand_table_two = self.house_data(house_ids)
                        content = self.get_brand_content(content, brand_table_one)
                        send_data.append(
                            [brand_table_two, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1],
                             file_path, content, name, mail])
                    elif customer_type == 3:
                        # 区域
                        table_2 = self.house_data(ids)
                        send_data.append(
                            [table_2, self.sheet_names_1, title, [self.head_1, self.head_1, self.head_1], file_path,
                             content, name, mail])
                    else:
                        message['customer_type'] = '客户类型错误'
                except Exception as e:
                    message['数据查询失败'] = str(e)
        else:
            message['error'] = '客户id错误：{}'.format(customer_id)

        # 生成文件
        rfu = ReportFileUtils()
        send_mail_log = []
        send_mail_info = []
        if len(send_data) > 0:
            for data in send_data:
                try:
                    rfu.create_excel_file(data[0], data[1], data[2], data[3], data[4])
                    send_mail_info.append([data[2], data[5], data[7], data[4], data[2] + '.xls', data[6]])
                except Exception as e:
                    print(e)
                    message['excel文件创建失败'] = str(e)
                    send_mail_log.append([data[6], data[7], data[2], -1, '文件创建失败：{}'.format(str(e))])
        else:
            message['excel_info'] = '需要生成excel的数据空'
        # 发送邮件
        mail_util = EmailUtil()
        if len(send_data) > 0:
            for mail in send_mail_info:
                try:
                    if new_mail:
                        result = mail_util.send_mail_by_admin(mail[0], mail[1], new_mail, mail[3], mail[4])
                        if result:
                            # name, mail, report_name, push_time, send_status, status, error_message
                            send_mail_log.append([mail[5], mail[2], mail[3], 1, 'success'])
                        else:
                            send_mail_log.append([mail[5], mail[2], mail[3], -1, 'fail'])
                    else:
                        for m in self.customer_mails:
                            result = mail_util.send_mail_by_admin(mail[0], mail[1], m, mail[3], mail[4])
                            if result:
                                # name, mail, report_name, push_time, send_status, status, error_message
                                send_mail_log.append([mail[5], mail[2], mail[3], 1, 'success'])
                            else:
                                send_mail_log.append([mail[5], mail[2], mail[3], -1, 'fail'])
                except Exception as e:
                    print(str(e))
                    message['邮件发送失败'] = str(e)
                    send_mail_log.append([mail[5], mail[2], mail[3], -1, '邮件发送失败:{}'.format(str(e))])
        else:
            message['mail_info'] = '需要发送邮件的数据为空'
        # 写入日志
        self.db.add_some(Sql.sql_12, send_mail_log)
        return message

    def get_house_ids(self, ids_str):
        if str(ids_str).find(',') == -1:
            return [ids_str]
        else:
            return [x for x in str(ids_str).split(',')]


if __name__ == '__main__':
    pass