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 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_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;