def get_bounceinfo(cid, mid, domain, group='error', lid = None): get_data = ''' select "%s_%s", substring(h.real_from,instr(h.real_from,'@')+1) as real_from, h.from_ip, h.from_inner_ip, to_ip, h.error, count(0) from msg_%s_%s_h h where h.domain_name = '%s' and h.return_type_id = 2 ''' if lid: get_data += ''' and h.list_id = 0 ''' get_data += ''' group by %s order by count(0) ''' # print get_data % (cid, mid, cid, mid, domain, group) bounce_conn = mdb.get_bounce_conn(cid); datas = mdb.exe_sql(bounce_conn, get_data % (cid, mid, cid, mid, domain, group), False, True) # display(['msg','real_from','from_ip','from_inner_ip','to_ip','error','count'], datas, ["c","c", "c", "c", "c", "l", "c"]) display(['msg','rfrom','wip','lip','toip','error','count'], datas, ["c","c", "c", "c", "c", "l", "c"])
def get_statd():
stat_sql = '''
select
"msg_%s_%s_h",h.begin_time,h.from_ip,h.from_inner_ip,h.to_ip,h.error,count(0) as count
from
msg_%s_%s_h h
where
h.domain_name = '%s' and h.return_type_id = 2
group by
h.error,h.to_ip limit 20
'''
message_detail = get_all_message();
display = [];
for row in message_detail :
client_id = row["client_id"];
message_id = row["mail_id"];
domain_name = row["the_domain"]
bounce_conn = mdb.get_bounce_conn(client_id);
domain_stat = mdb.exe_sql(bounce_conn, stat_sql % (client_id, message_id, client_id, message_id, domain_name), True, True);
display.extend(domain_stat);
print display;
def get_data(group_id):
the_date = date.today()
print '%s is begin!' % (group_id)
sum=0
get_sucess_sql = '''select u.message_id,u.email from msg_%s_%s_u u where u.last_rt_id = 1;'''
get_soft_sql = '''select u.message_id,u.email from msg_%s_%s_u u where u.last_rt_id = 2;'''
get_open_sql = '''select ho.message_id,ho.email_address,ho.ip,ho.country,ho.province,ho.city from msg_%s_%s_ho ho;'''
get_dict_sql = '''select ct.message_id,ct.url_value,ct.email_address,ct.ip,ct.country,ct.province,ct.city from msg_%s_%s_ct ct;'''
report = get_group_mail(group_id);
group_name = report[0]["group_name"]
client_id = report[0]["client_id"]
messages = report[0]["messages"].split(',')
the_dir = "E:\\groupdata\\%s.%s" % (group_id,group_name);
if os.path.exists(the_dir):
os.rename(the_dir,'%s.%s' % (the_dir,the_date))
os.mkdir('%s' % (the_dir))
os.chdir('%s' % (the_dir))
for message_id in messages:
bounce_conn = mdb.get_bounce_conn(client_id);
try:
sucess_datas = mdb.exe_sql(bounce_conn, get_sucess_sql % (client_id,message_id), False, False);
except Exception,e:
continue
export('sucess',sucess_datas)
sum+=1
print '%s sucess done!' % (message_id)
try:
soft_datas = mdb.exe_sql(bounce_conn, get_soft_sql % (client_id,message_id), False, False);
except Exception,e:
continue
def export():
yestoday = date.today() - timedelta(hours=24);
datas = get_error_mail();
the_sql = '''
select
"msg_%s_%s_h", h.begin_time, h.real_from, h.from_ip, h.from_inner_ip, h.to_ip, h.error, count(0) as count
from
msg_%s_%s_h h
where
h.domain_name = '%s' and h.return_type_id = 2
group by
error
order by
count desc
limit 20;
'''
result = []
the_dir = "C:\\Users\\zhang\\Desktop\\%s" % (yestoday);
if os.path.exists(the_dir) :
os.rename(the_dir,'%s.bak' % (the_dir))
os.mkdir('%s' % (the_dir))
os.chdir('%s' % (the_dir))
for data in datas:
domain = data["the_domain"]
cids = data["cids"];
mids = data["mids"]
client_id = cids.split(',')
message = mids.split(',')
the_file = '%s-%s.csv' % (domain,yestoday)
csvfile = file('%s' % (the_file), 'ab')
writer = csv.writer(csvfile)
writer.writerow(['msg_h', 'begin_time', 'real_from', 'from_ip', 'from_inner_ip', 'to_ip', 'error', 'count'])
for i in range(len(client_id)):
bounce_conn = mdb.get_bounce_conn(client_id[i]);
reports = mdb.exe_sql(bounce_conn, the_sql % (client_id[i], message[i], client_id[i], message[i], domain), False, True);
writer.writerows(reports)
csvfile.close();
def get_bounceinfo(cid, mid, email=None, test = False):
get_data = '''
select
*
from
msg_%s_%s_h h
where
h.email = '%s'
'''
get_test_data = '''
select
*
from
msg_tester_result mr
where
mr.message_id = %s
'''
if email :
get_test_data += " and mr.email_address = '%s'" % (email)
if test :
sql = get_test_data % (mid)
global_conn = mdb.get_global_conn()
datas = mdb.exe_sql(global_conn, sql, True, True)
else :
sql = get_data % (cid, mid, email)
bounce_conn = mdb.get_bounce_conn(cid);
datas = mdb.exe_sql(bounce_conn, sql, True, True)
# print datas
for data in datas:
for key,values in data.iteritems():
print '%-20s %s' %(key, values)
print '-'*50
