def get_all_message(start = None, end = None, branch = None, client = None, message_id = None):
today = datetime.combine(datetime.today(), time(9, 0));
yestoday = today - timedelta(hours=24);
if(not message_id):
find_msg_sql = """
select
DATE_FORMAT(m.schedule_time, '%%y-%%m-%%d') as rq, c.branch_id, m.client_id, m.subject, c.client_name, group_concat(m.message_id) as message_ids
from
message m, client c
where
m.client_id = c.client_id and m.begin_time between '%s' and '%s' and m.total_count > 100 and m.status_id = 50
""";
else:
find_msg_sql = '''
select
DATE_FORMAT(m.schedule_time, '%%y-%%m-%%d') as rq, c.branch_id, m.client_id, m.subject, c.client_name, m.message_id as message_ids
from
message m, client c
where
m.client_id = c.client_id and m.message_id = %s
'''
if(not start):
start = yesterday;
elif (not end):
end = datetime.combine(datetime.strptime(start, "%Y-%m-%d"), time(9, 0)) + timedelta(days=1);
if(not end):
end = today;
if(client):
find_msg_sql += " and m.client_id in (%s)" % (client);
if(branch):
find_msg_sql += " and c.branch_id in (%s)" % (branch);
find_msg_sql += '''
group by
DATE_FORMAT(m.schedule_time, '%%y-%%m-%%d'), c.client_id, c.client_name, m.subject
order by
DATE_FORMAT(m.schedule_time, '%%y-%%m-%%d') asc, c.client_id asc, m.subject asc
'''
# print find_msg_sql % (start, end)
global_conn = mdb.get_global_conn();
if(not message_id):
# aa = mdb.exe_sql(global_conn, find_msg_sql % (yestoday, today), True, True);
aa = mdb.exe_sql(global_conn, find_msg_sql % (start, end), True, True);
return aa;
else:
aa = mdb.exe_sql(global_conn, find_msg_sql % (message_id), True, True);
return aa;
def geturl(mid):
sql = '''
select url_id, value from track_url t where t.message_id = %s limit 1
'''
global_conn = mdb.get_global_conn()
data = mdb.exe_sql(global_conn, sql % (mid), True, True)
return data[0]
def get_analyse(branch_id, client_id, message_ids, domain):
stat_sql = '''
select
'%s' as message_id, h.real_from,h.from_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
order by
count desc
limit 15
'''
bounce_conn = mdb.get_bounce_conn(client_id);
display = [];
messages = message_ids.split(',')
for message in messages:
domain_stat = mdb.exe_sql(bounce_conn, stat_sql % (message, client_id, message, domain), True);
display.extend(domain_stat);
mdb.close_conn(bounce_conn);
return display;
def get_msg_report(start=None):
today = datetime.combine(datetime.today(), time(0, 0));
today_9 = datetime.combine(datetime.today(), time(9, 0));
yesterday = today - timedelta(hours=24);
select_msg_sql = '''
select
m.client_id, m.message_id, m.schedule_time, m.status_id, m.send_count, m.successful_count, m.softbounce_count, m.unstart_count
from
message m
where
m.schedule_time >= '%s' and m.schedule_time <= '%s'
'''
if(not start):
start = yesterday;
end = today_9
else:
end = datetime.strptime(start, '%Y-%m-%d').date() + timedelta(hours=24)
sw_global_conn = mdb.get_global_conn()
sw_datas = mdb.exe_sql(sw_global_conn, select_msg_sql % (start, end), False, True)
#c_global_conn = cmdb.get_global_conn()
#c_datas = mdb.exe_sql(c_global_conn, select_msg_sql % (start, end), False, True)
display(['cid','mid', 'stime', 'status','send','succ','soft','unstart'], sw_datas)
def get_analyse(branch_id, client_id, message_ids, domain, group = False):
stat_sql = '''
select
'%s' as message_id, DATE_FORMAT(h.begin_time, '%%Y-%%m-%%d %%H:%%i') as begin_time, substring(h.real_from,instr(h.real_from,'@')+1) as 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
'''
if(group):
stat_sql += '''group by %s''' % (group)
else:
stat_sql += '''group by h.error '''
stat_sql += '''order by count desc limit 20'''
bounce_conn = mdb.get_bounce_conn(client_id);
display = [];
messages = message_ids.split(',')
for message in messages:
domain_stat = mdb.exe_sql(bounce_conn, stat_sql % (message, client_id, message, domain), True);
display.extend(domain_stat);
mdb.close_conn(bounce_conn);
return display;
def getclients(branch_id):
sql = '''
select group_concat(c.client_id) as clients from client c where c.branch_id in (%s)
'''
global_conn = mdb.get_global_conn()
others_clients = mdb.exe_sql(global_conn, sql % (branch_id), True, True)
#print others_clients[0]['clients']
return others_clients[0]['clients']
def selectstg(cid):
sql = " select * from strategy s where s.domain_key = 'qqdomain' and s.owner_value = %s "
try:
resource_conn = mdb.get_resource_conn()
result = mdb.exe_sql(resource_conn, sql % (cid), False, True)
return result
except Exception,e:
print e
sys.exit()
def geturl(mid):
sql = '''
select url_id, value from track_url t where t.message_id = %s limit 1
'''
try:
global_conn = mdb.get_global_conn()
data = mdb.exe_sql(global_conn, sql % (mid), True, True)
return data[0]
except Exception,e:
print "Get message url failed!"
def getUserInfo(user_name):
get_user_info = '''
select a.user_name, a.password from admin_user a where a.user_name = '%s'
'''
try:
admin_conn = mdb.get_admin_conn();
datas = mdb.exe_sql(admin_conn, get_user_info % user_name, True, True)
return datas
except Exception,e:
print e
sys.exit()
def selectMessage(mid):
select_sql = '''
select
count(0)
from
email_package ep
where
ep.task_status_id = 0 and ep.object_id = %s
'''
carrier_conn = mdb.get_mesher_conn()
data = mdb.exe_sql(carrier_conn, select_sql % (mid), False, True)
return data[0]
def getMsgInfo(message_id):
message_info_sql = '''
select
m.client_id,m.message_id,m.subject,m.status_id,m.from_address,m.schedule_time,m.total_count,m.send_count,m.successful_count,m.softbounce_count,m.hardbounce_count,m.unstart_count,m.dist_opens,m.dist_url_clicks
from
message m
where
m.message_id = %s
'''
global_conn = mdb.get_global_conn()
datas = mdb.exe_sql(global_conn, message_info_sql % (message_id), True, True)
print datas[0]['subject']
print datas[0]['status_id']
def defaultloadinfo(start_time, end_time, client_group):
sql = '''
select
day(ep.start_time) as day, ep.client_id, sum(ep.successful_count) as success, sum(ep.softbounce_count) as soft, sum(ep.unstart_count) as unstart
from
email_package ep
where
ep.start_time >= '%s' and ep.start_time < '%s' and ep.client_id not in (%s) and ep.domain_name = 'qq.com'
'''
carrier_conn = mdb.get_mesher_conn()
data = mdb.exe_sql(carrier_conn, sql % (start_time, end_time, client_group), True, True)
#print data[0]
return data[0]
def sendinfo(start_time, end_time, client_list, group_by=False):
sql = '''
select
day(ep.start_time) as day, ep.client_id, sum(ep.successful_count) as success, sum(ep.softbounce_count) as soft, sum(ep.unstart_count) as unstart
from
email_package ep
where
ep.start_time >= '%s' and ep.start_time < '%s' and ep.client_id in (%s) and ep.domain_name = 'qq.com'
'''
if(group_by):
sql += '''group by ep.client_id'''
carrier_conn = mdb.get_mesher_conn()
data = mdb.exe_sql(carrier_conn, sql % (start_time, end_time, client_list), True, True)
return data
def showshortstrategy():
sql = '''
select
st.resource_ids, st.server_ip, st.owner_type, group_concat(st.owner_value) as owner_values
from
strategy st
where
st.domain_key = 'qqdomain' and owner_value <> '' and st.for_test_msg = 0 and st.owner_value not like '-%'
group by
st.resource_ids, st.owner_type
'''
resource_conn = mdb.get_resource_conn()
datas = list(mdb.exe_sql(resource_conn, sql, True, True))
return datas
def get_task_status(message_id):
task_status_sql = '''
select
task_id, task_type_id, service_id, client_id, object_id, task_status_id, schedule_time, claimed_process
from
task t
where
t.task_status_id <> 3 and t.object_id = %s
'''
global_conn = mdb.get_global_conn()
datas = mdb.exe_sql(global_conn, task_status_sql % (message_id), False, True)
width = [10] * 6 + [20] + [10]
mytt.display(['t_id', 't_type', 's_id', 'cid', 'mid', 't_status', 's_time', 'claimed'], datas, width)
def get_bounce_conn(client_id):
get_bouncedb_sql = '''
select
d.db_name
from
client c,db d
where
c.bounce_db_id=d.db_id and c.client_id = %s
'''
# print get_bouncedb_sql % client_id
global_conn = mdb.get_global_conn();
bounce_db_name = mdb.exe_sql(global_conn, get_bouncedb_sql % (client_id), True, True);
return bounce_db_name[0]['db_name']
def get_task_status(task_id):
select_msg_sql = '''
select
task_id, task_status_id, domain_name, schedule_time, claimed_process, successful_count, softbounce_count, unstart_count
from
email_package ep
where
ep.task_id = '%s'
'''
carrier_conn = mdb.get_mesher_conn()
datas = mdb.exe_sql(carrier_conn, select_msg_sql % (task_id), False, True)
width = [10] * 3 + [20] + [10] * 4
mytt.display(['t_id','t_status','domain','s_time','mesher','succ','soft','unt'], datas, width)
def get_unfinsh_task(message_id):
select_msg_sql = '''
select
task_id, client_id, object_id, task_status_id, weight, domain_name, schedule_time, claimed_process
from
email_package ep
where
ep.task_status_id <> 3 and ep.object_id = %s
'''
carrier_conn = mdb.get_mesher_conn()
datas = mdb.exe_sql(carrier_conn, select_msg_sql % (message_id), False, True)
width = [10] * 6 + [20] + [10]
mytt.display(['t_id', 'cid', 'mid', 't_status','weight','domain','s_time','mesher'], datas, width)
def showstrategy():
sql = '''
select
st.server_id,st.domain_key,st.resource_ids,st.owner_type,st.owner_value,st.for_test_msg
from
strategy st
where
st.domain_key = 'qqdomain' and st.owner_value not like '-%' and owner_type <> 'Common'
order by
st.resource_ids
'''
resource_conn = mdb.get_resource_conn()
datas = list(mdb.exe_sql(resource_conn, sql, True, True))
return datas
def showresource():
sql = '''
select
r.domains,group_concat(r.id) as rids, r.server_id, sum(to_index+1) - sum(from_index) as count
from
resource r
group by
r.domains,r.server_id
order by
r.domains
'''
try:
resource_conn = mdb.get_resource_conn()
loadinfo = mdb.exe_sql(resource_conn, sql, True, True)
return loadinfo
except Exception,e:
print e
sys.exit()
def showLoadInfo():
sql = '''
select
r.domains,group_concat(r.id) as rids
from
resource r
where
r.id <> 796 and r.domains like 'load%%'
group by
r.domains,r.server_id
order by r.domains
'''
try:
resource_conn = mdb.get_resource_conn()
loadinfo = mdb.exe_sql(resource_conn, sql, True, True)
return loadinfo
except Exception,e:
print e
sys.exit()
def showBymessage(start_time, end_time, clients):
show_sql = '''
select
ep.client_id,ep.object_id,sum(ep.successful_count),sum(ep.softbounce_count),sum(ep.hardbounce_count),sum(ep.unstart_count)
from
email_package ep
where
ep.start_time > '%s' and ep.end_time < '%s' and ep.client_id in (%s)
group by
ep.object_id
'''
try:
carrier_conn = mdb.get_mesher_conn()
data = mdb.exe_sql(carrier_conn, show_sql % (start_time, end_time, clients), False, True)
head = ['cid', 'mid', 'success', 'soft', 'hard', 'unstart' ]
width = [10] * 6
nt.display(head,data,width)
except Exception,e:
print e
sys.exit()
def getLoadInfo(id):
sql = '''
select
r.domains,group_concat(r.id) as rids, r.server_id, sum(to_index+1) - sum(from_index) as count
from
resource r
where
r.id <> 796 and r.domains = "load%s.com"
group by
r.domains,r.server_id
order by r.domains
'''
#print sql % (id)
try:
resource_conn = mdb.get_resource_conn()
loadinfo = mdb.exe_sql(resource_conn, sql % id, True, True)
return loadinfo
except Exception,e:
print e
sys.exit()
def showshortstrategy():
sql = '''
select case st.resource_ids
when '764,776' then '通道1'
when '756,797' then '通道2'
when '781' then '通道3'
when '765,777' then '通道4'
when '747' then '通道6'
when '780' then '通道7'
when '782' then '通道8'
when '798' then '通道9'
end 'load', st.server_ip, st.owner_type, group_concat(st.owner_value) as client_list
from
strategy st
where
st.domain_key = 'qqdomain' and owner_value <> '' and st.for_test_msg = 0 and st.owner_value not like '-%'
group by
st.resource_ids, st.owner_type
'''
resource_conn = mdb.get_resource_conn()
datas = mdb.exe_sql(resource_conn, sql, True, True)
return datas
def get_statd(start = None, end = None, branch = None, client = None, message_id = None, domain = None): stat_sql = ''' select '%s' as rq, '%s' as branch_id, '%s' as client_id, '%s' as mids, '%s' as subject, a.domain_name,a.total,a.success,a.success/(a.success+a.soft) as lv,a.soft,a.soft*100/(a.success+a.soft) as sv, a.hard,a.hard/a.total as hv,a.block,a.block/a.total as bv,b.dist_ho,b.dist_ho/a.success as ho_lv,c.dist_ct, c.dist_ct/a.success as ct_lv from ( select sd.domain_name, sum(sd.group_count) as total, sum(if(sd.return_type_id = 1, sd.group_count, 0)) as success, sum(if(sd.return_type_id = 2, sd.group_count, 0)) as soft, sum(if(sd.return_type_id = 3, sd.group_count, 0)) as hard, sum(if(sd.return_type_id = 0, sd.group_count, 0)) as block from summary_%s_delivery sd where sd.message_id in (%s) %s group by sd.domain_name order by sd.domain_name desc ) as a left join ( select domain_name, sum(group_count) as ho, sum(group_distinct_count) as dist_ho from summary_%s_domain_ho where message_id in (%s) %s group by domain_name order by domain_name desc ) as b on a.domain_name = b.domain_name left join ( select domain_name, sum(group_count) as ct, sum(group_distinct_count) as dist_ct from summary_%s_domain_ct where message_id in (%s) %s group by domain_name order by domain_name desc ) as c on a.domain_name = c.domain_name ''' message_detail = get_all_message(start = start, end = end, branch = branch, client = client, message_id = message_id); archive_conn = mdb.get_archive_conn(); domain_limit = '' if not domain else " and domain_name = '%s' " % (domain); display = []; for row in message_detail : branch_id = str(row["branch_id"]); client_id = str(row["client_id"]); message_ids = row["message_ids"]; subject = row["subject"] format_args = tuple([row["rq"], branch_id, client_id, message_ids, subject] + [client_id, message_ids, domain_limit] * 3); domain_stat = mdb.exe_sql(archive_conn, stat_sql % (format_args), True); display.extend(domain_stat); mdb.close_conn(archive_conn); return display;
def getIpInfo(city):
sql = ''' select place_id, country, province, city from ip_place where city = '%s' '''
#print city.__class__
global_conn = mdb.get_global_conn()
data = mdb.exe_sql(global_conn, sql % (city), True, True)
return data[0]
def src_dist_ho_ct(mid):
sql = '''select message_id, message_name, client_id, end_time, total_count, send_count, successful_count, softbounce_count, hardbounce_count, unstart_count, html_opens, dist_opens, url_clicks, dist_url_clicks from message m where m.message_id = %s'''
global_conn = mdb.get_global_conn()
data = mdb.exe_sql(global_conn, sql % (mid), True, True)
return data[0]
