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 report_file_utils import ReportFileUtils


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

    #
    index_type = {
        6: 'fenxianghuodian',
        7: 'liebianhuodian',
        8: 'saomadaofang',
        9: 'quanminjingjiren',
        10: 'laoyezhu',
        11: 'baobeichenggong',
        12: 'baobeidaofang'
    }

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

    def __init__(self):
        self.db = MysqlDB('bi_report')

    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, name):
        """
            获取邮件名称
        :param type:1：项目，2：集团，3：区域
        :param name: 项目 区域名称
        :return:
        """
        month_day = rpfu.get_montho_day()
        if type == 1:
            return '[{}]{}数据报表'.format(month_day, name)
        elif type == 2:
            return '[{}]建业云集团数据报表'.format(month_day)
        elif type == 3:
            return '[{}]建业云{}数据报表'.format(month_day, 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 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]) == brand_id:
                result.append(x[1])
        return result

    def brand_data_of_time(self, time_range):
        result = []
        ids = self.get_house_id_by_brand_id(self.brand_id)
        data_1 = self.user_data_volume_statistics(time_range, ids)
        data_2 = 0
        for x in self.get_recommend_data(time_range):
            if str(x[0]) == self.brand_id:
                data_2 = x[1]
        data_3 = []

        for key in self.index_type.keys():
            type_value = self.index_type.get(key)
            for x in self.db.select(Sql.sql_10, [time_range[0], time_range[1]]):
                if x[0] == type_value:
                    data_3.append(x[1])

        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))
        result.extend(self.brand_data_of_time(time_rang_2))
        result.extend(self.brand_data_of_time(time_range_3))
        return result

    def house_data_of_time(self, time_range, house_ids):
        # 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.user_data_volume_statistics(time_range, [id]))
            data_1.append(sub)
        data_2 = []
        for id in house_ids:
            for x in self.get_recommend_data(time_range):
                if id == x[0]:
                    sub = [id]
                    sub.append(x[1])
                    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 house_id == x[0]:
                    sub.append(x)
            house_data = []
            for key in self.index_type.keys():
                value = self.index_type.get(key)
                for x in sub:
                    if value == x[1]:
                        house_data.append(x[2])
            data_3.append(house_data)
        result = []
        for id in house_ids:
            sub = []
            for x in city_info:
                if id == x[1]:
                    sub.extend([x[4], x[3]])
            for x in data_1:
                if id == x[0]:
                    sub.extend(x[1:])
            for x in data_2:
                if id == x[0]:
                    sub.extend(x[1:])
            for x in data_3:
                if id == x[0]:
                    sub.extend(x[1:])
            result.append(sub)
        return result

    def house_data(self, house_ids):
        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 = []
        result.append(self.house_data_of_time(time_rang_1, house_ids))
        result.append(self.house_data_of_time(time_rang_2, house_ids))
        result.append(self.house_data_of_time(time_range_3, house_ids))
        pass

    def send_mail_to_customer(self, task_key):
        """
            统计数据推送给客户， 表二
        :param: task_key
        :return:
        """
        send_mail_info = []
        customers = self.get_report_customers(task_key)
        for customer in customers:
            name = customer[3]
            customer_type = customer[2]
            mail = customer[4]
            ids = customer[5]
            if customer_type == 2:
                # 集团
                
                pass
            else:
                # 项目或区域
                pass
        pass


if __name__ == '__main__':
    pass