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 exportList(cid, list_id):
sql_str = '''
select
email
from
l%s_subscriber
into outfile
'%s'
'''
list_conn = mdb.get_list_conn(cid)
mdb.exe_sql(list_conn, sql_str % (list_id, filename), False, True)
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 insertData(data):
insert_sql = '''
INSERT INTO `headerinfo1`
(`re_time`, `uid`, `mid`, `box_status`, `from_addree`, `seed_email`, `domain_name`, `sender`, `subject`, `box_id`)
VALUES
('%s', %s, %s, %s, '%s', '%s', '%s', '%s', '%s', '%s');
'''
try:
insert_conn = mdb.get_vmware_conn()
datas = mdb.exe_sql(insert_conn, insert_sql % data, False, True)
except Exception, e:
print 'Insert data to Mysql error: %s' %e
def getcid(mid):
sql_str = '''
select
m.client_id, c.client_name
from
message m, client c
where
m.client_id = c.client_id and m.message_id = %s
'''
global_conn = mdb.get_global_conn();
client_id = mdb.exe_sql(global_conn, sql_str % mid, False, True)
return client_id[0][0]
def getmid(lid):
sql_str = '''
select
message_id
from
message_list ml
where
ml.list_id = %s
'''
global_conn = mdb.get_global_conn()
message_id = mdb.exe_sql(global_conn, sql_str % lid, False, True)
return message_id[0][0]
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
def get_group_mail(group_id):
get_group_sql = '''
select
mg.group_name,m.client_id,group_concat(m.message_id) as messages
from
message m, message_group mg
where
mg.group_id = m.group_id and m.group_id = %s
group by
m.group_id;
'''
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, get_group_sql % (group_id), True, True);
return datas
def getEmailuid(email_address):
get_maxid_sql = '''
select
max(h.box_id) as max_uid
from
headerinfo1 h
where
h.seed_email = '%s'
'''
try:
select_conn = mdb.get_vmware_conn()
uid = mdb.exe_sql(select_conn, get_maxid_sql % email_address, True, True)
return uid[0]['max_uid']
except Exception, e:
print 'Select the max uid error: %s' %e
def get_all_message():
find_msg_sql = """
select
d.client_id,d.mail_id,d.the_domain
from
yulin.day_deliver_report d
where
d.the_date = '%s' and d.num_fail/(d.num_fail+d.num_success) >= 0.1 and d.num_total >= 100
and d.the_domain in ('sina.com')
order by
d.the_domain
""";
daily_conn = mdb.get_daily_conn();
aa = mdb.exe_sql(daily_conn, find_msg_sql % (yestoday), True, True);
return aa
def getSeedemail():
get_seed_sql = '''
select
ge.email_address,ge.domain_name,ge.password
from
global_seed_emails ge
where
ge.domain_name in ('163.com', '126.com', 'sina.com', 'sina.cn', 'sohu.com')
order by
ge.domain_name;
'''
try:
get_seed_conn = mdb.get_vmware_conn()
datas = mdb.exe_sql(get_seed_conn, get_seed_sql, True, True)
return datas
except Exception, e:
print 'Get data from Mysql error: %s' %e
def getSeedemail(domain_name):
get_seed_sql = '''
select
ge.email_address,ge.domain_name,ge.password
from
global_seed_emails ge
where
ge.domain_name in ( %s )
order by
ge.domain_name;
'''
try:
get_seed_conn = mdb.get_vmware_conn()
# print get_seed_sql % domain_name
datas = mdb.exe_sql(get_seed_conn, get_seed_sql % domain_name, True, True)
return datas
except Exception, e:
print 'Get data from Mysql error: %s' %e
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 getUserInfo(cid=None, email=None):
get_user_info = '''
select
u.client_id, u.email_address, u.password, c.client_name, c.branch_id
from
users u,client c
where
u.client_id = c.client_id and
'''
if cid:
get_user_info += ''' u.client_id = '%s' ''' % cid
if email :
get_user_info += '''u.email_address = '%s' ''' % email
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, get_user_info, True, True)
return datas
def get_error_mail():
yestoday = date.today() - timedelta(hours=24);
get_error_sql = '''
select
dr.the_domain, group_concat(dr.client_id) as cids, group_concat(dr.mail_id) as mids
from
day_deliver_report dr
where
dr.the_date = '%s' and dr.num_total > 100 and dr.per_fail > 0.1
and dr.the_domain in ('qq.com','vip.qq.com','sina.com','sina.com.cn','sina.cn','163.com','126.com','sohu.com','yeah.net',
'yahoo.com','yahoo.com.cn','yahoo.cn','21cn.com','139.com','gmail.com','tom.com','hotmail.com','vip.sina.com')
group by
dr.the_domain
order by
dr.client_id;
'''
report_conn = mdb.get_error_conn();
datas = mdb.exe_sql(report_conn, get_error_sql % (yestoday), True, True);
return datas;
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
export('soft',soft_datas)
print '%s soft done!' % (message_id)
mdb.close_conn(bounce_conn)
report_conn = mdb.get_report_conn(client_id);
try:
open_datas = mdb.exe_sql(report_conn, get_open_sql % (client_id,message_id), False, False);
except Exception,e:
continue
export('open',open_datas)
print '%s open done!' % (message_id)
try:
dict_datas = mdb.exe_sql(report_conn, get_dict_sql % (client_id,message_id), False, False);
except Exception,e:
continue
export('dict',dict_datas)
print '%s dict done!' % (message_id)
mdb.close_conn(report_conn)
else:
print '%s is end!' % (group_id)
if sum == 0:
def getipid_city(city):
sql_str = "select place_id from ip_place ip where city='%s' order by place_id limit 1 "
global_conn = mdb.get_global_conn();
ip_place_id = mdb.exe_sql(global_conn, sql_str % city, False, True)
return ip_place_id[0][0]
def getipid_province(province):
sql_str = "select place_id from ip_place ip where province='%s' order by place_id limit 1"
global_conn = mdb.get_global_conn();
ip_place_id = mdb.exe_sql(global_conn, sql_str % province, False, True)
return ip_place_id[0][0]
def getBranchdomain(branch_id):
sql = '''select bd.domain_name from branch_domain bd where bd.branch_id = %s '''
global_conn = mdb.get_global_conn();
datas = mdb.exe_sql(global_conn, sql % branch_id, True, True)
return datas
def getActiveService(server):
sql_str = '''select group_concat(machine_ip) as ips from service s where s.status="active" and s.server_type = "%s" '''
global_conn = mdb.get_global_conn();
ips = mdb.exe_sql(global_conn, sql_str % server, True, True)
return ips[0]['ips'].split(',')
