def sync_db(data_list):
'''Synchron potential student from excel to PrepareToCrm
'''
push_list = []
m_q = {i['mobile']: i['qudao_details'] for i in
PrepareToCrm.objects.values('mobile', 'qudao_details')}
[m_q.setdefault(i['phone'], i['qudao_details'])
for i in PrepareToCrm.objects.filter(phone__isnull=False).values(
'phone', 'qudao_details')]
[m_q.setdefault(i['other_phone'], i['qudao_details'])
for i in PrepareToCrm.objects.filter(other_phone__isnull=False).values(
'other_phone', 'qudao_details')] for data_dict in data_list:
try:
if data_dict['mobile'] == data_dict['phone']:
data_dict['phone'] = None
except KeyError:
data_dict['phone'] = None
try:
if data_dict['mobile'] == data_dict['other_phone']:
data_dict['other_phone'] = None
except KeyError:
data_dict['other_phone'] = None
try:
if data_dict['phone'] == data_dict['other_phone']:
data_dict['other_phone'] = None
except KeyError:
data_dict['other_phone'] = None mobile = str(data_dict['mobile']) if data_dict['mobile'] else None
phone = str(data_dict['phone']) if data_dict['phone'] else None
other_phone = str(
data_dict['other_phone']) if data_dict['other_phone'] else None mobiles = [i for i in [mobile, phone, other_phone] if i] if not mobiles:
data_dict['push_result'] = '啥电话都没有'
data_dict['feedback'] = True
else:
for mobile in mobiles:
if mobile not in m_q:
m_q[mobile] = data_dict['qudao_details']
else:
qudao_details = m_q[mobile]
data_dict['push_result'] = '重复推送({})'.format(
qudao_details)
data_dict['feedback'] = True
if not data_dict['create_user']:
data_dict['create_user'] = ''
push_list.append(PrepareToCrm(**data_dict)) PrepareToCrm.objects.bulk_create(push_list)
写入数据库
def prepare_crm_data_from_excel(request):
'''Get potential student from excel
'''
if request.method == 'POST':
user = request.user
result_dict = {}
if not request.FILES:
return HttpResponse('请选择上传文件')
f = request.FILES['file']
if str(f).split('.')[-1] not in ['xlsx', 'xls']:
return HttpResponse('请上传(xlsx, xls)')
wb2 = openpyxl.load_workbook(f)
fields = []
datas = []
crm_list = []
for table in wb2.worksheets: # .xsl里的每个sheet
for row in table.rows: # row 每一行 数据
if not fields: # 这个fields只有字头
fields.extend([column.value for column in row])
continue
d = {}
index = 0
for column in row:
try:
d[index] = column.value.strip()
except AttributeError:
d[index] = column.value
index += 1
if any(d.values()):
datas.append(d) order_fields = {i[0]: i[1] for i in enumerate(fields)} for data in datas:
d = {'user': user}
for k, v in list(data.items()):
key_index = order_fields[k]
if not key_index:
continue
crm_key = ALL_FIELDS[key_index]
d[crm_key] = v
crm_list.append(d)
sync_db(crm_list) result_dict['result'] = 'ok'
return Response(result_dict, status=status.HTTP_200_OK) html = """<form action="" method="POST" enctype="multipart/form-data">
<input type="file" name="file" /><input type="submit"/></form>"""
response = HttpResponse(html)
response["Allow-Control-Allow-Origin"] = '*'
return response
上传读取excel
ALL_FIELDS = {i.verbose_name: i.name for i in
PrepareToCrm._meta.fields if i.name not in EXCLUDES} EXCLUDES = [
'id', 'push_result', 'success', 'pushing', 'exported', 'upload_time'
]
程序写成Excel 发送邮箱
def run():
EXCEL_CACHE_PATH = "/tmp/_BI_EXCEL_CACHE"
xlsxfile = '{}/push_result.xlsx'.format(EXCEL_CACHE_PATH) custom_set = PrepareToCrm.objects.filter(user__isnull=False, feedback=True)
if custom_set.exists():
user_list = set([custom_obj.user_id for custom_obj in custom_set])
for user_id in user_list:
mobile_set = custom_set.filter(user_id=user_id).values('mobile', 'name', 'success', 'apply_contry')
result_list = [['学生姓名', '电话', '申请国家', '申请项目', '跟进顾问', '分校',
'是否成功推送', 'BI客户状态', 'CRM客户状态', '备注'], ]
for mo_name in mobile_set:
mobile = mo_name.get('mobile')
name = mo_name.get('name')
success = '推送成功' if mo_name.get('success') else '推送失败'
apply_contry = mo_name.get('apply_contry') crp_status = '无状态'
crm_status = '无状态' ad_set = Advisor_potential_student.objects.filter(
Q(mobile__contains=mobile) | Q(phone__contains=mobile) |
Q(other_phone__contains=mobile)).values('pk', 'status', 'education', 'advisor__address_city',
'advisor__full_name')
crm_set = CrmDB.objects.using('crm').filter(
Q(mobile__contains=mobile) | Q(phone__contains=mobile) |
Q(other_phone__contains=mobile)).values('latest_status') education = '无'
address_city = '无'
full_name = '无'
con_l = []
con_str = ''
if ad_set.exists():
status_num = ad_set[0]['status']
education = ad_set[0]['education']
address_city = ad_set[0]['advisor__address_city']
full_name = ad_set[0]['advisor__full_name']
potential_id = ad_set[0]['pk']
crp_status = SOURCE_STATUS.get(status_num) remark_set = Advisor_student_remark.objects.filter(potential_id=potential_id).values('create_at', 'content').order_by('-create_at') for remark in remark_set:
time = remark['create_at'].strftime('%Y-%m-%d %H:%M:%S')
content = remark['content']
w_content = '【' + time + '】' + ' ' + content + '\n' # 拼接备注的字符串
con_l.append(w_content)
content_str = con_str.join(con_l)
else:
content_str = '无' if crm_set.exists():
try:
status_num = crm_set[0]['latest_status']
crm_status = crm_status_dict.get(int(status_num))
except Exception as e:
crm_status = '无状态'
result_list.append(
[name, mobile, apply_contry, education, full_name, address_city, success, crp_status, crm_status, content_str]) xlsx = openpyxl.Workbook()
table = xlsx.active
table.title = 'data' for line in result_list:
table.append(line) xlsx.save(xlsxfile)
send_mail(user_id)
def send_mail(user_id):
email_obj = User.objects.filter(id=user_id).values('email')[0]
to_email = email_obj['email']
if not to_email:
return
SMTP_HOST = "xxxxxx.qq.com"
AUTH_USER = "[email protected]"
AUTH_PASS = "xxxxxxxxxxxx123" email = MIMEMultipart() to_list = [
to_email
] from_addr = 'xxxxxxxxxxxx.com'
email['From'] = from_addr
email['To'] = Header(COMMASPACE.join(to_list), 'utf8')
email['Subject'] = Header('推送失败反馈', 'utf-8')
xlsxfile = '/tmp/_BI_EXCEL_CACHE/push_result.xlsx'
with open(xlsxfile, 'rb') as f:
attach = MIMEApplication(f.read())
attach.add_header('Content-Disposition', 'attachment',
filename='push_result.xlsx')
email.attach(attach) smtp = smtplib.SMTP_SSL(SMTP_HOST)
smtp.login(AUTH_USER, AUTH_PASS)
smtp.sendmail(from_addr, to_list, email.as_string())