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]