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_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 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 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 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 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 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(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 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]
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]
