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
from collections import deque
class JianYeReport(object):
"""
建业报表数据处理
"""
# customer_mails = ['liutt@elab-plus.com', 'binrenzhang@qq.com', 'plf@centralchina.com']
# customer_mails = ['liutt@elab-plus.com', 'binrenzhang@qq.com', 'lijm@elab-plus.com', 'xuanxc@elab-plus.com']
customer_mails = ['binrenzhang@qq.com', 'liutt@elab-plus.com']
#
index_type = [
'fenxianghuodian',
'liebianhuodian',
'saomadaofang',
'quanminjingjiren',
'laoyezhu',
'baobeichenggong',
'baobeidaofang'
]
head_1 = ['区域', '城市', '项目名称', '可分派客户数', '已分派客户数', '浏览量', '浏览人数', '新增用户', '新增获电', '推荐用户', '分享获电', '裂变获电',
'扫码到访数', '全民经纪人注册数', '老业主注册数', '报备成功数', '报备到访数']
brand_id = '13'
head_2 = ['区域名称', '城市', '项目名称', '推荐人姓名', '推荐人手机号', '组织', '分享获电', '经纪人注册数', '报备成功数']
sheet_names_1 = ['当日数据', '当月数据', '上线以来所有数据']
def __init__(self):
self.db = MysqlDB('bi_report')
self.time_range_1 = rpfu.get_prd_day()
self.time_range_2 = rpfu.get_time_range_month()
self.time_range_3 = rpfu.get_all_time_data_range()
self.pv_data_1 = self.pv_count(self.time_range_1)
self.pv_data_2 = self.pv_count(self.time_range_2)
self.pv_data_3 = self.pv_count(self.time_range_3)
self.house_region = self.get_house_region_info()
self.mail_util = EmailUtil()
def get_house_region(self):
return self.db.select(Sql.sql_15)
def get_house_region_name(self, house_id):
for x in self.house_region:
if str(x[1]) in house_id:
return x[3]
return ' '
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_month_day()
if type == 1:
return ['[{}]{}数据报表_{}_项目明细'.format(month_day, region_name, name), '[{}]{}数据报表_{}_置业顾问完成情况'.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):
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 get_dispatchable_mobile_qutty_brand(self, time_range):
data = self.db.select(Sql.sql_20, [str(time_range[0]) + ' 00:00:00', str(time_range[1]) + ' 59:59:59'])
if len(data) == 0:
return [0, 0]
return [data[0][2], data[0][3]]
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)
data_4 = self.get_dispatchable_mobile_qutty_brand(time_range)
# 4
result.extend(data_1)
# 1
result.extend(data_2)
# 7
result.extend(data_3)
# 2
result.extend(data_4)
return result
def brand_data(self):
"""
集团数据总览, 表一。
:return:
"""
result = []
result.extend(self.brand_data_of_time(self.time_range_1, 1))
result.extend(self.brand_data_of_time(self.time_range_2, 2))
result.extend(self.brand_data_of_time(self.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) and str(x[0]) == self.brand_id:
return x[3:]
return [0, 0, 0, 0]
def get_dispatchable_mobile_qutty_house(self, time_range, house_ids):
data = self.db.select(Sql.sql_19, [str(time_range[0]) + ' 00:00:00', str(time_range[1]) + ' 59:59:59', house_ids])
return data
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 = []
recommend_data = self.get_recommend_data(time_range)
for id in house_ids:
sub = [id]
number = 0
for x in recommend_data:
if str(id) == str(x[0]):
number = x[1]
sub.append(number)
data_2.append(sub)
data_3 = []
house_data_detail = self.house_data_detail(time_range)
for house_id in house_ids:
sub = []
for x in house_data_detail:
# 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)
data_4 = self.get_dispatchable_mobile_qutty_house(time_range, house_ids)
result = []
brand_data = []
for id in house_ids:
sub = []
if id == '0':
sub.extend(['品牌层', '品牌层', '品牌层'])
else:
for x in self.house_region:
if str(x[1]) == str(id):
sub.append(x[3])
if len(sub) == 0:
sub.append(' ')
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'])
a, b = 0, 0
for x in data_4:
if str(id) == x[0]:
a, b = x[2], x[3]
sub.append(a)
sub.append(b)
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:])
if id == '0':
brand_data = sub
else:
result.append(sub)
result.sort(key=lambda obj: obj[8], reverse=True)
if len(brand_data) == 17:
all_dat = [brand_data]
all_dat.extend(result)
return all_dat
return result
def house_data(self, house_ids):
result = [self.house_data_of_time(self.time_range_1, house_ids, 1), self.house_data_of_time(self.time_range_2, house_ids, 2),
self.house_data_of_time(self.time_range_3, house_ids, 3)]
return result
def get_table_3(self, house_ids):
day_data = self.db.select(Sql.sql_16, [self.time_range_1[0], house_ids])
month_data = self.db.select(Sql.sql_17, [self.time_range_2[1], house_ids])
all_data = self.db.select(Sql.sql_18, [self.time_range_3[1], house_ids])
return [day_data, month_data, all_data]
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:
"""
message = {}
send_mail_info = []
rfu = ReportFileUtils()
customers = self.get_report_customers(task_key)
send_mail_log = []
brand_table_one = None
brand_table_two = None
try:
save_path = '/tmp'
send_data = []
if task_key == 12:
brand_table_one = self.brand_data()
house_ids = self.get_house_id_by_brand_id('13')
brand_table_two = self.house_data(house_ids)
for customer in customers:
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)
try:
if customer_type == 2:
file_path = save_path + '/' + title + '.xls'
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 == 1:
title_1, title_2 = title[0], title[1]
table_2 = self.house_data(self.get_house_ids(ids))
# table_3 = self.get_table_3(self.get_house_ids(ids))
file_path = save_path + '/' + title_1 + '.xls'
file_path_1 = save_path + '/' + title_2 + '.xls'
send_data.append(
[table_2, self.sheet_names_1, title_1, [self.head_1, self.head_1, self.head_1], file_path,
content, name, mail])
# send_data.append(
# [table_2, self.sheet_names_1, title_1, [self.head_1, self.head_1, self.head_1], file_path,
# content, name, mail, title_2, [self.head_2, self.head_2, self.head_2], table_3, file_path_1])
elif customer_type == 3:
file_path = save_path + '/' + title + '.xls'
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])
else:
send_mail_log.append([name, mail, title, -1, '客户类型错误{}'.format(customer_type)])
pass
except Exception as e:
print(str(e))
send_mail_log.append([name, mail, title, -1, '数据查询失败:{}'.format(str(e))])
message['query_data'] = 'success'
for data in send_data:
try:
send_mai_info_sub = []
# data, sheet_names, title, headers, file_path
rfu.create_excel_file(data[0], data[1], data[2], data[3], data[4])
send_mai_info_sub.extend([data[2], data[5], data[7], data[4], data[2] + '.xls', data[6]])
if len(data) == 12:
rfu.create_excel_file(data[10], data[1], data[8], data[9], data[11])
send_mai_info_sub.extend([data[11], data[8] + '.xls'])
send_mail_info.append(send_mai_info_sub)
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:
self.send_mail(mail, send_mail_log)
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_month_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[14], brand_table_one[15], brand_table_one[16], brand_table_one[17],
brand_table_one[18],
brand_table_one[19],
brand_table_one[20], brand_table_one[22], brand_table_one[24], brand_table_one[25],
brand_table_one[28], brand_table_one[29], brand_table_one[30], brand_table_one[31],
brand_table_one[32],
brand_table_one[33],
brand_table_one[34], brand_table_one[36], brand_table_one[38], brand_table_one[39])
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:
save_path = '/tmp'
for customer in customers:
try:
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)
if customer_type == 1:
# 项目
# 项目
title_1, title_2 = title[0], title[1]
table_2 = self.house_data(ids)
# table_3 = self.get_table_3(ids)
file_path = save_path + '/' + title_1 + '.xls'
file_path_1 = save_path + '/' + title_2 + '.xls'
send_data.append(
[table_2, self.sheet_names_1, title_1, [self.head_1, self.head_1, self.head_1], file_path,
content, name, mail])
# send_data.append(
# [table_2, self.sheet_names_1, title_1, [self.head_1, self.head_1, self.head_1], file_path,
# content, name, mail, title_2, [self.head_2, self.head_2, self.head_2], table_3,
# file_path_1])
elif customer_type == 2:
# 集团
file_path = save_path + '/' + title + '.xls'
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:
# 区域
file_path = save_path + '/' + title + '.xls'
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:
send_mai_info_sub = []
rfu.create_excel_file(data[0], data[1], data[2], data[3], data[4])
send_mai_info_sub.extend([data[2], data[5], data[7], data[4], data[2] + '.xls', data[6]])
if len(data) == 12:
rfu.create_excel_file(data[10], data[1], data[8], data[9], data[11])
send_mai_info_sub.extend([data[11], data[8] + '.xls'])
send_mail_info.append(send_mai_info_sub)
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的数据空'
message['data'] = send_mail_info
# 发送邮件
if len(send_data) > 0:
for mail in send_mail_info:
try:
if new_mail:
mail[2] = new_mail
self.send_mail(mail, send_mail_log)
else:
for m in self.customer_mails:
self.send_mail(m, send_mail_log)
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 send_mail(self, mail, send_mail_log):
result = None
# for m in self.customer_mails:
if len(mail) == 6:
result = self.mail_util.send_mail_by_admin(mail[0], mail[1], mail[2], mail[3], mail[4])
elif len(mail) == 8:
result = self.mail_util.send_mail_by_admin(mail[0], mail[1], mail[2], mail[3], mail[4], mail[6],
mail[7])
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, '调用邮件接口失败:{}'.format(str(result))])
return send_mail_log
def get_house_ids(self, ids_str):
if str(ids_str).find(',') == -1:
return [str(ids_str)]
else:
return [str(x) for x in str(ids_str).split(',')]
if __name__ == '__main__':
jianye = JianYeReport()
jianye.send_mail_to_customer(12)