def get_ip_area(ip):
try:
qqzeng = IpSearch()
result = ' '.join(qqzeng.Find(ip).split('|')[:6])
except:
result = ''
return result
def sub_account_login(request):
from django.contrib.auth.models import update_last_login
from django.contrib.auth.views import logout, auth_login
from django.contrib.auth import authenticate
from django.contrib.auth.signals import user_logged_in
parent_user_id = request.user.id
client_ip = get_client_ip(request)
agent = request.META.get('HTTP_USER_AGENT', None)
ip_search = IpSearch()
ip_info = ip_search.Find(client_ip)
area, title = split_ip_to_area_title(ip_info)
customer_id = request.POST.get('subloging_customer_id', '')
auth = request.POST.get('subloging_auth', '')
obj = get_customer_child_obj(request, customer_id)
if auth == hashlib.md5(
'%s-%s' %
(settings.WEB_API_AUTH_KEY,
datetime.datetime.now().strftime("%Y%m%d"))).hexdigest():
logout(request)
CoreLog.objects.create(
user_id=parent_user_id,
user_type='users',
target_id=customer_id,
target_name=u'{0} - {0}'.format(obj.username),
action='user_login',
ip=client_ip,
desc=u'登录IP：{}<br>登录地区：{}<br>浏览器信息：{}'.format(
client_ip, title, agent),
)
user = authenticate(username=obj.username,
password='',
t_password=obj.password)
user_logged_in.disconnect(update_last_login)
auth_login(request, user)
user_logged_in.connect(update_last_login)
return HttpResponseRedirect(reverse('home'))
raise Http404
def track_export_email(request):
if not request.user.service().is_track_export:
raise Http404
data = request.GET
action = data.get('action', '')
link_id = data.get('link_id', '')
user_id = request.user.id
ident = data.get('ident', '')
content_id = data.get('content_id', '')
stat_objs = TrackStat.objects.filter(task_ident=ident, customer_id=user_id)
if content_id:
stat_objs = stat_objs.filter(content_id=content_id)
track_ids = stat_objs.values_list('id', flat=True)
cr = connections['mm-track'].cursor()
tablename = '{}_track_email'.format(user_id)
tablename_click = '{}_track_click'.format(user_id)
if request.user.lang_code == 'en-us':
list = [[
u'邮件地址', u'首次使用IP', u'首次IP区域', u'最后使用IP', u'最后IP区域', u'打开总数',
u'首次打开时间', u'最后打开时间', u'点击总数', u'首次点击时间', u'最后点击时间'
]]
else:
list = [[
u'邮件地址', u'首次使用IP', u'首次IP区域', u'最后使用IP', u'最后IP区域', u'打开总数',
u'首次打开时间', u'最后打开时间', u'点击总数', u'首次点击时间', u'最后点击时间'
]]
sql = """
SELECT email, ip_first, COALESCE(ip_last, '') ip_last,
open_total, open_first, COALESCE(open_last, '') open_last,
click_total, COALESCE(click_first, '') click_first, COALESCE(click_last, '') click_last
FROM {} WHERE track_id in ({})""".format(
tablename, ','.join(map(lambda s: str(s), track_ids)))
where_str = ''
if action == 'click':
where_str += " AND click_total > 0 "
if link_id:
where_str += " AND email_id IN (SELECT email_id FROM {} WHERE link_id={})".format(
tablename_click, link_id)
sql += where_str
cr.execute(sql)
rows = cr.fetchall()
ip_search = IpSearch()
# pattern ='([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])'
pattern = '(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)'
ip_compile = re.compile(pattern)
lang_code = (request.user.lang_code == 'en-us' and 'en-us') or "zh-hans"
for r in rows:
title_f, title_l = '', ''
email, ip_first, ip_last, open_total, open_first, open_last, click_total, click_first, click_last = r
open_last = '' if open_last == '0000-00-00 00:00:00' else open_last
click_first = '' if click_first == '0000-00-00 00:00:00' else click_first
click_last = '' if click_last == '0000-00-00 00:00:00' else click_last
if ip_first:
m = ip_compile.search(ip_first)
if m:
ip_first = m.group(0)
ip_info = ip_search.Find(ip_first)
area, title_f = ipparse.get_ip_detail(ip_info, lang_code)
if ip_last:
m = ip_compile.search(ip_last)
if m:
ip_last = m.group(0)
ip_info = ip_search.Find(ip_last)
area, title_l = ipparse.get_ip_detail(ip_info, lang_code)
list.append([
email, ip_first, title_f, ip_last, title_l, open_total, open_first,
open_last, click_total, click_first, click_last
])
return ExcelResponse(list, 'mail', encoding='gbk')
def ajax_track_email(request):
data = request.GET
user_id = request.user.id
cr = connections['mm-track'].cursor()
tablename = '{}_track_email'.format(user_id)
ident = data.get('ident', '')
content_id = data.get('content_id', '')
stat_objs = TrackStat.objects.filter(task_ident=ident, customer_id=user_id)
if content_id:
stat_objs = stat_objs.filter(content_id=content_id)
track_ids = stat_objs.values_list('id', flat=True)
order_column = data.get('order[0][column]', '')
order_dir = data.get('order[0][dir]', '')
search = data.get('search[value]', '')
colums = [
'email_id', 'email', 'open_total', 'browser', 'ip_first', 'open_first',
'click_first'
]
where_str = u'track_id in ({})'.format(','.join(
map(lambda s: str(s), track_ids)))
if search:
where_str += u""" and email like '%{0}%' """.format(search)
order_by_str = ''
if order_column and int(order_column) < len(colums):
if order_dir == 'desc':
order_by_str = u'order by %s desc' % colums[int(order_column)]
else:
order_by_str = u'order by %s asc' % colums[int(order_column)]
sql = u"SELECT COUNT(1) FROM %s WHERE %s;" % (tablename, where_str)
cr.execute(sql)
rows = cr.fetchall()
count = rows[0][0]
try:
length = int(data.get('length', 1))
except ValueError:
length = 1
try:
start_num = int(data.get('start', '0'))
except ValueError:
start_num = 0
limit_str = u'limit %s offset %s' % (length, start_num)
sql = u"""
SELECT email, open_total, click_total, browser, os, ip_first, ip_last,
open_first, open_last, click_first, click_last, email_id
FROM %s WHERE %s %s %s;
""" % (tablename, where_str, order_by_str, limit_str)
cr.execute(sql)
rows = cr.fetchall()
rs = {
"sEcho": 0,
"iTotalRecords": count,
"iTotalDisplayRecords": count,
"aaData": []
}
page = start_num / length + 1
number = length * (page - 1) + 1
ip_search = IpSearch()
# pattern ='([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])'
pattern = '(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)\.(25[0-5]|2[0-4]\d|[01]?\d\d?)'
ip_compile = re.compile(pattern)
lang_code = (request.user.lang_code == 'en-us' and 'en-us') or "zh-hans"
for r in rows:
email, open_total, click_total, browser, os, ip_first, ip_last, open_first, open_last, click_first, click_last, email_id = r
open_first = open_first if open_first else u'-'
open_last = open_last if open_last else u'-'
click_first = click_first if click_first else u'-'
click_last = click_last if click_last else u'-'
ip_first_html = _(
u'首：<span style="cursor: default;color: #004F99;font-size: 11px;">-</span>'
) % {}
ip_last_html = _(
u'<br>后：<span style="cursor: default;color: #CC0000;font-size: 11px;">-</span>'
) % {}
if ip_first:
m = ip_compile.search(ip_first)
if m:
ip_first = m.group(0)
ip_info = ip_search.Find(ip_first)
area, title = ipparse.get_ip_detail(ip_info, lang_code)
ip_first_html = _(
u'首：<span style="color: Gray;" title="%(title)s">[%(area)s] </span><span style="cursor: default;color: #004F99;font-size: 11px;">%(ip_first)s</span>'
) % {
'title': title,
'area': area,
'ip_first': ip_first
}
if ip_last:
m = ip_compile.search(ip_last)
if m:
ip_last = m.group(0)
ip_info = ip_search.Find(ip_last)
area, title = ipparse.get_ip_detail(ip_info, lang_code)
ip_last_html = _(
u'<br>后：<span style="color: Gray;" title="%(title)s">[%(area)s] </span><span style="cursor: default;color: #004F99;font-size: 11px;">%(ip_last)s</span>'
) % {
'title': title,
'area': area,
'ip_last': ip_last
}
ip_html = ip_first_html + ip_last_html
rs["aaData"].append([
number,
email,
_(u""" %(open_total)d / <a href='/track/click/?ident=%(ident)s&content_id=%(content_id)s&email_id=%(email_id)d' title='查看点击详情'>%(click_total)d</a>"""
) % {
'open_total': int(open_total),
'ident': ident,
'content_id': content_id,
'email_id': int(email_id),
'click_total': int(click_total)
},
u'{}<br>{}'.format(browser, os),
ip_html,
_(u'首：<span style="cursor: default;color: #004F99;font-size: 11px;">%(open_first)s</span><br>后：<span style="cursor: default;color: #CC0000;font-size: 11px;">%(open_last)s</span>'
) % {
'open_first': open_first,
'open_last': open_last
},
_(u'首：<span style="cursor: default;color: #004F99;font-size: 11px;">%(click_first)s</span><br>后：<span style="cursor: default;color: #CC0000;font-size: 11px;">%(click_last)s</span>'
) % {
'click_first': click_first,
'click_last': click_last
},
"",
])
number += 1
return HttpResponse(json.dumps(rs, ensure_ascii=False),
content_type="application/json")
def mail_statistics_report_pdf_context(request, task_id):
# key = ":django:edmweb:statistics:mail_statistics_report_pdf_context:report:{task_id}:".format(
# task_id=task_id)
# context = cache.get(key, None)
context = None
if not context:
obj = get_object(SendTask, request.user, task_id)
if obj.send_status != 3:
raise Http404
# 任务发送数
task_send_total = obj.get_real_send_qty()
# 取得任务实际发送量
value = StatTask.objects.filter(customer_id=request.user.id,
task_ident=obj.send_name).aggregate(
count_send=Sum('count_send'),
count_error=Sum('count_error'),
count_err_1=Sum('count_err_1'),
count_err_2=Sum('count_err_2'),
count_err_3=Sum('count_err_3'),
count_err_5=Sum('count_err_5'),
)
count_send = value['count_send'] if value['count_send'] else 0
count_error = int(
value['count_error']) if value['count_error'] else 0 # 发送失败
count_succes = count_send - count_error # 发送成功数
# 格式错误、无效
# count_invalid = send_total - count_send
# -------------------
# 格式错误、无效
count_invalid = obj.error_count
# 一共发送数
send_total = count_send + count_invalid
# -------------------
# 邮箱不存在
count_err_1 = value['count_err_1'] if value['count_err_1'] else 0
# 空间不足
count_err_2 = value['count_err_2'] if value['count_err_2'] else 0
# 用户拒收
count_err_3 = value['count_err_3'] if value['count_err_3'] else 0
# 垃圾拒绝发送
count_err_5 = value['count_err_5'] if value['count_err_5'] else 0
total_error = count_invalid + count_err_1 + count_err_2 + count_err_3 + count_err_5
# 邮件打开
stat_objs = TrackStat.objects.filter(task_ident=obj.send_name,
customer_id=request.user.id)
stat_obj = stat_objs[0] if stat_objs else None
open_unique, click_unique = 0, 0
show_stat_rate, show_link_rate, show_link_rate_suc = '0%', '0%', '0%'
# 浏览器、操作系统、 地域 点击链接 统计
link_objs = None
ip_search = IpSearch()
browser_count = {'ie': 0, 'firefox': 0, 'chrome': 0, 'other': 0}
os_count = {'windows': 0, 'linux': 0, 'macintosh': 0, 'other': 0}
area_list, area_lists, country_list, country_lists = [], {}, [], {}
domain_data_click, domain_data_all = [], []
track_id = 0
pattern = '([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])'
ip_compile = re.compile(pattern)
if stat_objs:
track_id = stat_obj.id
# open_total = stat_obj.open_total
open_unique = stat_obj.open_unique
click_unique = stat_obj.click_unique
show_stat_rate = statistics_tools.get_rate(stat_obj.open_unique,
count_succes)
show_link_rate = statistics_tools.get_rate(click_unique,
open_unique)
show_link_rate_suc = statistics_tools.get_rate(
click_unique, count_succes)
link_objs = TrackLink.objects.filter(track_id=track_id).order_by(
'-click_unique', '-click_total')[:50]
cr = connections['mm-track'].cursor()
tablename = '{}_track_email'.format(request.user.id)
sql = u""" SELECT email FROM {0} WHERE track_id={1} AND click_total > 0; """.format(
tablename, track_id)
cr.execute(sql)
domain_data_click = statistics_tools.get_domain_data(cr.fetchall())
sql = u""" SELECT email FROM {0} WHERE track_id={1} AND open_total > 0; """.format(
tablename, track_id)
cr.execute(sql)
domain_data_all = statistics_tools.get_domain_data(cr.fetchall())
# 地域分布
sql = u"""
SELECT browser, os, ip_first, ip_last, open_total, click_total, email
FROM {0} WHERE track_id={1};
""".format(tablename, track_id)
cr.execute(sql)
rows = cr.fetchall()
for row in rows:
if row[0].lower().startswith('msie'):
browser_count['ie'] += 1
elif row[0].lower().startswith('firefox'):
browser_count['firefox'] += 1
elif row[0].lower().startswith('chrome'):
browser_count['chrome'] += 1
else:
browser_count['other'] += 1
if row[1].lower().startswith('windows'):
os_count['windows'] += 1
elif row[1].lower().startswith('linux'):
os_count['linux'] += 1
elif row[1].lower().startswith('macintosh'):
os_count['macintosh'] += 1
else:
os_count['other'] += 1
ip = ''
if row[2]:
ip = row[2]
elif row[3]:
ip = row[3]
if not ip:
continue
m = ip_compile.search(ip)
if m:
ip = m.group(0)
else:
continue
ip_info = ip_search.Find(ip)
country, area = statistics_tools.split_ip_info(ip_info)
if not country:
continue
if country not in country_list:
country_list.append(country)
country_lists.update({country: [row[4], 1]})
else:
country_lists.update({
country: [
country_lists[country][0] + row[4],
country_lists[country][1] + 1,
]
})
if not area:
continue
if area not in area_list:
area_list.append(area)
area_lists.update({area: [row[4], 1]})
else:
area_lists.update({
area: [
area_lists[area][0] + row[4],
area_lists[area][1] + 1,
]
})
area_sort_tmp = sorted(area_lists.items(),
key=lambda e: e[1][0],
reverse=True)[:10]
country_sort_tmp = sorted(country_lists.items(),
key=lambda e: e[1][0],
reverse=True)[:10]
area_data, country_data = {}, {}
area_sort, country_sort = [], []
for d in area_sort_tmp:
if d[0] in AREA_CODE.keys():
area_data.update({
AREA_CODE[d[0]]: int(d[1][0]),
})
area_sort.append((d[0], int(d[1][0]), int(d[1][1])))
for d in country_sort_tmp:
if d[0] in JC_CODE_COUNTRY:
country_data.update({
JC_CODE_COUNTRY[d[0]]: int(d[1][0]),
})
country_sort.append((d[0], int(d[1][0]), int(d[1][1])))
area_max = statistics_tools.get_gfc_max_data(
area_sort_tmp[0][1][0]) if area_sort_tmp else 5000
country_max = statistics_tools.get_gfc_max_data(
country_sort_tmp[0][1][0]) if area_sort_tmp else 5000
context = {
'task_obj':
obj,
'task_id':
task_id,
'task_send_total':
task_send_total,
'send_total':
send_total,
'count_send':
count_send,
'count_succes':
count_succes,
'count_succes_rate':
statistics_tools.get_rate(count_succes, count_send - count_err_5),
'count_error':
count_error,
'count_error_rate':
statistics_tools.get_rate(count_error - count_err_5,
count_send - count_err_5),
'total_error':
total_error,
'count_invalid': {
'value': count_invalid,
'width': statistics_tools.get_width(count_invalid,
total_error),
'rate': statistics_tools.get_rate(count_invalid, total_error)
},
'count_err_1': {
'value': count_err_1,
'width': statistics_tools.get_width(count_err_1, total_error),
'rate': statistics_tools.get_rate(count_err_1, total_error)
},
'count_err_2': {
'value': count_err_2,
'width': statistics_tools.get_width(count_err_2, total_error),
'rate': statistics_tools.get_rate(count_err_2, total_error)
},
'count_err_3': {
'value': count_err_3,
'width': statistics_tools.get_width(count_err_3, total_error),
'rate': statistics_tools.get_rate(count_err_3, total_error)
},
'count_err_5': {
'value': count_err_5,
'width': statistics_tools.get_width(count_err_5, total_error),
'rate': statistics_tools.get_rate(count_err_5, total_error)
},
'stat_obj':
stat_obj,
'show_stat_rate':
show_stat_rate,
'open_unique':
open_unique,
'open_unique_rate':
statistics_tools.get_rate(open_unique, count_succes),
'no_open':
count_succes - open_unique,
'no_open_rate':
statistics_tools.get_rate(count_succes - open_unique,
count_succes),
'click_unique':
click_unique,
'show_link_rate':
show_link_rate,
'show_link_rate_suc':
show_link_rate_suc,
'no_click':
count_succes - click_unique,
'no_click_rate':
statistics_tools.get_rate(count_succes - click_unique,
count_succes),
'link_objs':
link_objs,
'track_id':
track_id,
'domain_data_click':
domain_data_click,
'domain_data_all':
domain_data_all,
'area_sort':
area_sort,
'country_sort':
country_sort,
'area_data':
area_data,
'country_data':
country_data,
'area_max':
area_max,
'country_max':
country_max,
'os_count':
os_count,
'browser_count':
browser_count,
'filename':
u"{}_{}.pdf".format(obj.send_name, task_id)
}
# cache.set(key, context, 300)
return context
def clean(self):
username = self.cleaned_data.get('username')
password = self.cleaned_data.get('password')
captcha = self.cleaned_data.get('captcha')
redis = get_redis_connection()
key = 'captcha:{}'.format(captcha)
if redis.hget(key, 'res') != 'True':
raise forms.ValidationError(
self.error_messages_captcha['invalid_login'],
code='invalid_login',
params={'username': self.username_field.verbose_name},
)
redis.delete(key)
if username and password:
self.user_cache = authenticate(username=username,
password=password)
login_ip = get_client_ip(self.request)
##### 地区登录保护 #####
if self.user_cache:
if self.user_cache.disabled == '1':
raise forms.ValidationError(
self.error_messages_disabled['invalid_login'],
code='invalid_login',
params={'username': self.username_field.verbose_name},
)
if self.user_cache.parent and self.user_cache.parent.disabled == '1':
raise forms.ValidationError(
self.error_messages_disabled2['invalid_login'],
code='invalid_login',
params={'username': self.username_field.verbose_name},
)
weixin_customer_id = self.user_cache.weixin_customer_id
customer_id = self.user_cache.id
ip_search = IpSearch()
ip_info = ip_search.Find(login_ip)
login_area1, login_area2 = parse_login_area(ip_info)
obj, _created = CoreLoginAreaIp.objects.get_or_create(
user_id=customer_id)
is_open = False if _created else obj.is_open
_exists = CoreLoginAreaIp.objects.filter(
Q(user_id=customer_id, ip__icontains=login_ip)
| Q(user_id=customer_id, area__icontains=login_area1)
| Q(user_id=customer_id, area__icontains=login_area2)
).exists()
if is_open and not _exists:
j_area, j_title = split_ip_to_area_title(ip_info)
redis.rpush(
'edm_web_notice_queue',
json.dumps({
"type": "1",
'customer_id': customer_id,
"area": j_title,
'point': '',
'domain': '',
'task': '',
}))
if weixin_customer_id:
raise forms.ValidationError(
self.error_messages_loginsafe['invalid_login'],
code='invalid_login',
params={
'username': self.username_field.verbose_name
},
)
else:
raise forms.ValidationError(
self.error_messages_loginsafe2['invalid_login'],
code='invalid_login',
params={
'username': self.username_field.verbose_name
},
)
if self.user_cache is None:
raise forms.ValidationError(
self.error_messages['invalid_login'],
code='invalid_login',
params={'username': self.username_field.verbose_name},
)
else:
self.user_cache.last_ip = login_ip
self.user_cache.last_login = timezone.now()
self.user_cache.save(update_fields=['last_ip', 'last_login'])
agent = self.request.META.get('HTTP_USER_AGENT', None)
self.user_cache.save_login_log(login_ip, ip_info, agent)
self.confirm_login_allowed(self.user_cache)
# 关注用户上线通知
s = self.user_cache.service()
if s and s.is_pushcrew:
action = "service"
title = u"登录提醒"
message = u"{}（ID: {}） 于 {} 时间登录平台".format(
self.user_cache.company, self.user_cache.id,
datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
pushcrew_notice(action, title, message)
return self.cleaned_data