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