def do_worker_stat(user_id, task_ident, recv_domain, detail_data): count_success = detail_data['count_success'] count_error = detail_data['count_err_1'] + detail_data[ 'count_err_2'] + detail_data['count_err_3'] + detail_data['count_err_5'] log.info( u'worker_stat user_id={}, task_ident={}, domain={}, sned={}, success={}, err_5={}' .format(user_id, task_ident, recv_domain, count_success + count_error, count_success, detail_data['count_err_5'])) t = time.localtime(time.time()) now = time.strftime("%Y-%m-%d %H:%M:%S", t) sql = """ INSERT INTO stat_task_real (customer_id, task_ident, domain, count_send, count_error, count_err_1, count_err_2, count_err_3, count_err_5, created, updated) VALUES (%d, '%s', '%s', %d, %d, %d, %d, %d, %d, '%s', '%s') ON DUPLICATE KEY UPDATE count_send=count_send + VALUES(count_send), count_error=count_error + VALUES(count_error), count_err_1=count_err_1 + VALUES(count_err_1), count_err_2=count_err_2 + VALUES(count_err_2), count_err_3=count_err_3 + VALUES(count_err_3), count_err_5=count_err_5 + VALUES(count_err_5), updated=VALUES(updated); """ % (user_id, task_ident, recv_domain, count_success + count_error, count_error, detail_data['count_err_1'], detail_data['count_err_2'], detail_data['count_err_3'], detail_data['count_err_5'], now, now) common.time_call(db_utils.do, GLB_MY_MS_DBNAME, sql) return
def init(): global glb_p_date, glb_p_hour, glb_time_b, glb_time_e, glb_process_log_list, glb_count_task, glb_count_send task_count = 10 send_count = 100 sql = "SELECT task_count, send_count, domain FROM stat_task_setting LIMIT 1;" res = common.time_call(db_utils.query, GLB_MY_MS_DBNAME, sql) if res: task_count = res[0][0] send_count = res[0][1] stat_domain_T = res[0][2] # 统计任务个数 glb_count_task = task_count # 统计发送数量大于等于 glb_count_send = send_count t = time.localtime(time.time() - 60 * 60 * 1) # glb_p_date = time.strftime("%Y-%m-%d", t) # glb_p_hour = int(time.strftime("%H", t)) glb_p_date = '2017-08-14' glb_p_hour = 11 # 根据指定的小时生成要处理的时间区间 glb_time_b = "%02d:00:00" % glb_p_hour glb_time_e = "%02d:00:00" % (glb_p_hour + 1) log.info('StatLog, date: %s, hour: %s, time range: %s - %s' % (glb_p_date, glb_p_hour, glb_time_b, glb_time_e))
def getSmtpData(): tb_name = "maillog_%s" % (glb_p_date.replace('-', '')) if glb_time_e == '24:00:00': sql = """ SELECT log_id, smtp_account_id, mail_from, mail_to FROM %s WHERE send_time>='%s' AND is_ok='t' AND return_code='250' AND customer_id<>'0' AND smtp_account_id is not null; """ % (tb_name, glb_time_b) else: sql = """ SELECT log_id, smtp_account_id, mail_from, mail_to FROM %s WHERE send_time>='%s' AND send_time<'%s' AND is_ok='t' AND return_code='250' AND customer_id<>'0' AND smtp_account_id is not null; """ % (tb_name, glb_time_b, glb_time_e) res = common.time_call(db_utils.query, GLB_PG_LOG_DBNAME, sql) smtp_dict = {} for log_id, smtp_account_id, mail_from, mail_to in res: log_id = int(log_id) smtp_account_id = int(smtp_account_id) if smtp_account_id not in smtp_dict: smtp_dict[smtp_account_id] = [] smtp_dict[smtp_account_id].append( (log_id, mail_from, mail_to) ) return smtp_dict
def init(): global glb_p_date, glb_p_hour, glb_time_b, glb_time_e, glb_process_log_list, glb_count_task, glb_count_send task_count = 10 send_count = 100 sql = "SELECT task_count, send_count, domain FROM stat_task_setting LIMIT 1;" res = common.time_call(db_utils.query, GLB_MY_MS_DBNAME, sql) if res: task_count = res[0][0] send_count = res[0][1] stat_domain_T = res[0][2] # 统计任务个数 glb_count_task = task_count # 统计发送数量大于等于 glb_count_send = send_count t = time.localtime(time.time() - 60 * 60 * 1) glb_p_date = time.strftime("%Y-%m-%d", t) glb_p_hour = int(time.strftime("%H", t)) # 根据指定的小时生成要处理的时间区间 glb_time_b = "%02d:00:00" % glb_p_hour glb_time_e = "%02d:00:00" % (glb_p_hour + 1) log.info('StatLog, date: %s, hour: %s, time range: %s - %s' % (glb_p_date, glb_p_hour, glb_time_b, glb_time_e)) glb_process_log_list = [] datfile = os.path.join(SCRIPT, 'data', 'logstat.dat') if os.path.exists(datfile): for line in file(datfile).read().strip().split("\n"): glb_process_log_list.append(line)
def do_worker_imap_update(log_id, mail_to, return_said, error_type, mode='qq'): return_code = 450 if error_type==1 else 550 tb_name = "maillog_%s" % (glb_p_date.replace('-', '')) sql = u"UPDATE {} SET return_code=%s, return_said=%s, error_type=%s, is_ok='f' WHERE log_id=%s;".format(tb_name) args = (return_code, return_said, error_type, log_id) res = common.time_call(db_utils.do, GLB_PG_LOG_DBNAME, sql, args) log.info(u'woker_imap {} log_id={}, mail_to={}, error_type={}, return_code={}, return_said={}'.format(mode, log_id, mail_to, error_type, return_code, return_said)) # less stat_log.log | grep 'woker_imap log_id' return
def do_worker_task_update(user_id, domain, count_succ, count_succ_rej, count_sned, now): avg_score = round(count_succ * 100.00 / count_sned, 2) if count_sned else 0 avg_score_s = round(count_succ * 100.00 / count_succ_rej, 2) if count_succ_rej else 0 sql = """ INSERT INTO core_customer_score (customer_id, domain, score, score_s, created, updated) VALUES (%d, '%s', %.2f, %.2f, '%s', '%s') ON DUPLICATE KEY UPDATE score=VALUES(score), score_s=VALUES(score_s), updated=VALUES(updated); """ % (user_id, domain, avg_score, avg_score_s, now, now) common.time_call(db_utils.do, GLB_MY_MS_DBNAME, sql) log.info( u'worker_task user_id={}, domain={}, avg_score={}, avg_score_s={}'. format(user_id, domain, avg_score, avg_score_s)) return
def init(): expire_time = 5*24*60*60 sql = "SELECT `customer_id`, `domain` FROM `core_domain` WHERE `status` IN ('Y', 'T');" res = common.time_call(db_utils.query, MYSQL_MS, sql) dbl_key = "domain:dbl:check" for customer_id, domain in res: if not domain: continue key = "domain:dbl:check:{}".format(domain) if not redis.exists(key): if not customer_id: customer_id=0 p = redis.pipeline() p.lpush(dbl_key, json.dumps({ 'customer_id': customer_id, 'domain': domain })) p.set(key, 1) p.expire(key, expire_time) p.execute()
def do_woker_imap(smtp_account_id, smtp_list): sql = """ SELECT aa.account, aa.password, bb.smtp_server FROM core_mss_account aa INNER JOIN core_mss_server bb ON bb.type_id=aa.type_id WHERE aa.id=%d AND bb.smtp_server IN ('smtp.exmail.qq.com', 'smtp.mailrelay.cn') LIMIT 1; """ % (smtp_account_id, ) res = common.time_call(db_utils.query, GLB_MY_MS_DBNAME, sql) if not res: return smtp_account, smtp_password, smtp_server = res[0] if smtp_server == 'smtp.mailrelay.cn': do_worker_mailrelay(smtp_list) elif smtp_server == 'smtp.exmail.qq.com': do_worker_qq(smtp_account, smtp_password, smtp_list) return
def do_worker_task(user_id): log.info(u'worker_task user_id={}'.format(user_id)) t = time.localtime(time.time()) now = time.strftime("%Y-%m-%d %H:%M:%S", t) sql = """ SELECT count_send, count_error, count_err_5, domain FROM stat_task_real WHERE customer_id=%d AND count_send > %d ORDER BY updated DESC LIMIT %d """ % (user_id, glb_count_send, glb_count_task) res = common.time_call(db_utils.query, GLB_MY_MS_DBNAME, sql) domin_score = defaultdict(list) for count_send, count_error, count_err_5, domain in res: # 成功 count_succ = count_send - count_error # 除去拒发 count_succ_rej = count_send - count_err_5 domin_score[domain].append((count_succ, count_succ_rej, count_send)) count_send_all = 0 count_succ_all = 0 count_succ_rej_all = 0 for domain, score_T in domin_score.iteritems(): count_succ_sum = float(sum([i[0] for i in score_T])) count_succ_rej_sum = float(sum([i[1] for i in score_T])) count_send_sum = float(sum([i[2] for i in score_T])) count_succ_all += count_succ_sum count_succ_rej_all += count_succ_rej_sum count_send_all += count_send_sum if domain == '*': continue do_worker_task_update(user_id, domain, count_succ_sum, count_succ_rej_sum, count_send_sum, now) if domin_score: domain = '*' do_worker_task_update(user_id, domain, count_succ_all, count_succ_rej_all, count_send_all, now) return
def _save(j): customer_id, domain = j['customer_id'], j['domain'] sql = "INSERT INTO `dbl_domain_log`(`customer_id`, `domain`, `status`, `created`) VALUES (%s, %s, %s, %s);" args = (customer_id, domain, 1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) res = common.time_call(db_utils.do, MYSQL_MS, sql, args)
def do_redis_count(start, end, domain): sql = "SELECT COUNT(*) FROM core_customer_score WHERE score>=%d AND score<%d AND domain='%s';" % ( start, end, domain) res = common.time_call(db_utils.query, GLB_MY_MS_DBNAME, sql) return res[0][0]
def statErrorCountByDate(): # 从数据库取得统计数据 tb_name = "maillog_%s" % (glb_p_date.replace('-', '')) if glb_time_e == '24:00:00': sql = """ WITH stat_domain_tmp AS( SELECT customer_id, task_ident, recv_domain, error_type, is_ok, COUNT(*) as stat_count FROM %s WHERE send_time>='%s' AND customer_id<>'0' AND COALESCE(recv_domain, '') != '' GROUP BY customer_id, task_ident, recv_domain, error_type, is_ok ), stat_domain AS ( SELECT customer_id, task_ident, error_type, is_ok, stat_count, CASE WHEN recv_domain ='%s' THEN 'qq.com' WHEN recv_domain IN %s THEN '163.com' ELSE '*' END AS recv_domain FROM stat_domain_tmp ) SELECT customer_id, task_ident, error_type, is_ok, SUM(stat_count) AS stat_count, recv_domain FROM stat_domain GROUP BY customer_id, task_ident, error_type, is_ok, recv_domain ORDER BY customer_id, task_ident """ % (tb_name, glb_time_b, GLB_QQ_DOMAIN, str(tuple(GLB_163_DOMAIN))) else: sql = """ WITH stat_domain_tmp AS( SELECT customer_id, task_ident, recv_domain, error_type, is_ok, COUNT(*) as stat_count FROM %s WHERE send_time>='%s' AND send_time<'%s' AND customer_id<>'0' AND COALESCE(recv_domain, '') != '' GROUP BY customer_id, task_ident, recv_domain, error_type, is_ok ), stat_domain AS ( SELECT customer_id, task_ident, error_type, is_ok, stat_count, CASE WHEN recv_domain ='%s' THEN 'qq.com' WHEN recv_domain IN %s THEN '163.com' ELSE '*' END AS recv_domain FROM stat_domain_tmp ) SELECT customer_id, task_ident, error_type, is_ok, SUM(stat_count) AS stat_count, recv_domain FROM stat_domain GROUP BY customer_id, task_ident, error_type, is_ok, recv_domain ORDER BY customer_id, task_ident """ % (tb_name, glb_time_b, glb_time_e, GLB_QQ_DOMAIN, str(tuple(GLB_163_DOMAIN))) res = common.time_call(db_utils.query, GLB_PG_LOG_DBNAME, sql) # 组合数据 # stat_data = {} user_list = [] stat_data = _loop_dict() for row in res: customer_id = int(row[0]) # 初始化各域名统计数据 t_ident = row[1] if not t_ident: continue recv_domain = row[5] # 统计计数 count_err_1, count_err_2, count_err_3, count_err_5, count_success = 0, 0, 0, 0, 0 if row[2] == 1: count_err_1 = row[4] if row[2] == 2: count_err_2 = row[4] if row[2] in [0, 3, 4, 6]: count_err_3 = row[4] if row[2] == 5: count_err_5 = row[4] if row[3]: count_success = row[4] _D = stat_data[customer_id][t_ident][recv_domain] _D['count_err_1'] = _D.get('count_err_1', 0) + count_err_1 _D['count_err_2'] = _D.get('count_err_2', 0) + count_err_2 _D['count_err_3'] = _D.get('count_err_3', 0) + count_err_3 _D['count_err_5'] = _D.get('count_err_5', 0) + count_err_5 _D['count_success'] = _D.get('count_success', 0) + count_success # 取得客户列表 if customer_id not in user_list: user_list.append(customer_id) return stat_data, user_list