def get_video(user_channel={}, nday=1):
data = {}
data["all_channel"] = {}
data["all_channel"]["num_video"] = 0
data["all_channel"]["num_user"] = 0
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
sql = "select count(user_id), count(distinct user_id) from video_effective_readings where user_id in (%s) and created_at >= \"%s\" and created_at < \"%s\" and effective = 1"
for k, user_arr in user_channel.items():
data[k] = {}
data[k]["num_video"] = 0
data[k]["num_user"] = 0
for i in range(0, len(user_arr), 1000):
user_to_sql = user_arr[i:i+1000]
sql_use = sql % (",".join(user_to_sql), day1, day2)
rt = mysql_tool.querydb(
sql_use, logger, "get video count for %d users" % len(user_to_sql))
if len(rt) > 0 and rt[0][0] is not None:
data[k]["num_video"] += int(rt[0][0])
data[k]["num_user"] += int(rt[0][1])
data["all_channel"]["num_video"] += data[k]["num_video"]
data["all_channel"]["num_user"] += data[k]["num_user"]
return data
def get_edit_video_info(nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
tommorow = time_tool.get_someday_str(1)
# 获取视频名称
video_categories = {}
sql = "select id, name from video_categories"
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
video_categories[int(v[0])] = v[1]
# 获取新增视频
video_new_count = {}
sql = "select category_id, count(*) from videos where published_at >= \"%s\" and published_at < \"%s\" group by category_id" % (day1, day2)
handle_sql_hash_total(video_new_count, sql, logger, TOTAL_ID)
# 获取视频pv
sql = "select category_id, sum(real_pv) from videos group by category_id"
rt = mysql_tool.querydb(sql, logger, sql)
total = 0
for v in rt:
k = int(v[0])
total += int(v[1])
data[k] = {}
data[k]['channel_name'] = u"视频"
data[k]['channel'] = "video"
data[k]['category_id'] = k
data[k]['pv_total'] = int(v[1])
set_hash(video_categories, data[k], k, 'category_name')
set_hash(video_new_count, data[k], k, 'new_published_count')
data[TOTAL_ID] = {}
data[TOTAL_ID]['channel_name'] = u"视频"
data[TOTAL_ID]['channel'] = "video"
data[TOTAL_ID]['category_name'] = TOTAL_SHOW
data[TOTAL_ID]['category_id'] = TOTAL_ID
data[TOTAL_ID]['pv_total'] = total
set_hash(video_new_count, data[TOTAL_ID], TOTAL_ID, 'new_published_count')
# 获取热点视频
data[HOT_VIDEO_CATEGORY_ID] = {}
sql = "select count(*) from videos where hot_at >= \"%s\" and hot_at < \"%s\" and published_at < \"%s\"" % (day1, day2, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_VIDEO_CATEGORY_ID]['new_published_count'] = int(v[0])
# 获取热点视频的pv
sql = "select sum(real_pv) from videos where hot_at < \"%s\"" % tommorow
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_VIDEO_CATEGORY_ID]['channel_name'] = u"视频"
data[HOT_VIDEO_CATEGORY_ID]['channel'] = "video"
data[HOT_VIDEO_CATEGORY_ID]['category_id'] = HOT_VIDEO_CATEGORY_ID
data[HOT_VIDEO_CATEGORY_ID]['pv_total'] = int(v[0])
data[HOT_VIDEO_CATEGORY_ID]['category_name'] = HOT_SHOW
return data
def get_news_effective_readings(user_arr=[], nday=1):
# 获取有效阅读
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
sql = "select news_id, count(*) from news_effective_readings where user_id in (%s) and created_at >= \"" + \
day1 + "\" and created_at < \"" + day2 + \
"\" and effective != 0 group by news_id"
ner = get_effective_reading(
sql, user_arr, "select from news_effective_readings")
return ner
def get_hot_list_info(user_arr=[], ner={}, data={}, nday=1):
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
for table, category_id, category_name in [
("news_hot_lists", edit_info.NEWS_HOT_LISTS,
edit_info.NEWS_HOT_LISTS_SHOW),
("news_hot_seven_day_lists", edit_info.NEWS_HOT_SEVEN,
edit_info.NEWS_HOT_SEVEN_SHOW),
("news_hot_total_lists", edit_info.NEWS_HOT_TOTAL, edit_info.NEWS_HOT_TOTAL_SHOW)]:
data[category_id] = {}
data[category_id]['user_type'] = "new"
data[category_id]['channel_name'] = u"资讯"
data[category_id]['channel'] = "news"
data[category_id]['category_id'] = category_id
data[category_id]['category_name'] = category_name
# 获取有效阅读
news_id_arr = [str(news_id) for news_id in ner.keys()]
sql = "select news_id from " + table + " where news_id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select news_id from %s for %d news" % (table, len(id_arr)))
for v in rt:
news_id = int(v[0])
if 'effective_reading' in data[category_id].keys():
data[category_id]['effective_reading'] += ner[news_id]
else:
data[category_id]['effective_reading'] = ner[news_id]
# 获取评论数
sql = "select count(*) from news_comments as nc join news as n on(nc.news_id = n.id) where n.id in (select news_id from " + \
table + ") and nc.created_at >= \"" + day1 + \
"\" and nc.created_at < \"" + day2 + "\" and nc.user_id in (%s)"
set_common_hot_info(
sql, user_arr, data[category_id], 'comments_count', "select news_comments")
# 获取收藏数
sql = "select count(*) from user_like_news as uln join news as n on(uln.news_id = n.id) where n.id in (select news_id from " + \
table + ") and uln.created_at >= \"" + day1 + \
"\" and uln.created_at < \"" + day2 + "\" and uln.user_id in (%s)"
set_common_hot_info(
sql, user_arr, data[category_id], 'like_count', "select user_like_newsews_comments")
# 获取点赞数
sql = "select count(*) from user_zan_news_comments as uznc join news_comments as nc on (uznc.comment_id = nc.id) join news as n on(nc.news_id = n.id) where n.id in (select news_id from " + \
table + ") and uznc.created_at >= \"" + day1 + \
"\" and uznc.created_at < \"" + \
day2 + "\" and uznc.user_id in (%s)"
set_common_hot_info(
sql, user_arr, data[category_id], 'zan_count', "select user_zan_news_comments")
return data
def get_edit_video_info(user_arr=[], user_type="new", ver={}, nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
# 获取视频名称
video_categories = {}
sql = "select id, name from video_categories"
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
video_categories[int(v[0])] = v[1]
video_id_arr = [str(video_id) for video_id in ver.keys()]
# 获取普通类目有效阅读
sql = "select id, category_id from videos where id in (%s)"
for i in range(0, len(video_id_arr), 1000):
id_arr = video_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id, category_id from videos for %d video" % len(id_arr))
for v in rt:
video_id = int(v[0])
category_id = int(v[1])
if category_id in data.keys():
data[category_id]["effective_reading"] += ver[video_id]
else:
set_for_one_category(
data, category_id, user_type, u"视频", "video", video_categories)
data[category_id]["effective_reading"] = ver[video_id]
# 获取热点视频有效阅读
sql = "select id from videos where id in (%s) and hot_at < \"" + \
day2 + "\""
for i in range(0, len(video_id_arr), 1000):
id_arr = video_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id from videos for %d video" % len(id_arr))
for v in rt:
video_id = int(v[0])
if edit_info.HOT_VIDEO_CATEGORY_ID in data.keys():
data[edit_info.HOT_VIDEO_CATEGORY_ID]['effective_reading'] += ver[video_id]
else:
data[edit_info.HOT_VIDEO_CATEGORY_ID] = {}
data[edit_info.HOT_VIDEO_CATEGORY_ID]['effective_reading'] = ver[video_id]
data[edit_info.HOT_VIDEO_CATEGORY_ID]['user_type'] = "new"
data[edit_info.HOT_VIDEO_CATEGORY_ID]['channel_name'] = u"视频"
data[edit_info.HOT_VIDEO_CATEGORY_ID]['channel'] = "video"
data[edit_info.HOT_VIDEO_CATEGORY_ID]['category_id'] = edit_info.HOT_VIDEO_CATEGORY_ID
data[edit_info.HOT_VIDEO_CATEGORY_ID]['category_name'] = edit_info.HOT_SHOW
return data
def process(nday=1):
data_pv_uv = edit_pv_uv.process(nday)
mysql_tool.connectdb(host="47.96.238.205", database="taozuiredian-news")
mysql_tool.querydb("SET NAMES utf8mb4")
data_news = get_edit_news_info(nday)
data_news = get_hot_list_info(nday, data_news)
data_news = get_hot_news_info(nday, data_news)
data_news = get_news_effective_reading(nday, data_news)
data_video = get_edit_video_info(nday)
data_video = get_video_effective_reading(nday, data_video)
mysql_tool.closedb()
for k, v in data_pv_uv.items():
if k in data_news.keys():
for k_, v_ in v.items():
data_news[k][k_] = v_
for data in [data_news, data_video]:
for k, v in data.items():
# 获取前一天
yid = time_tool.get_someday_str(-nday-1)
url = URL_ELASTICSEARCH_EDIT_INFO + \
"/" + yid + "_" + v["channel"] + "_" + str(k)
r = requests.get(url, headers=JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
logger.error("request edit_info index failed, status_code:%d, reason:%s, k:%s",
r.status_code, r.reason, k)
else:
r_json = r.json()
if "_source" in r_json.keys():
yd = r_json["_source"]
if "pv_total" in yd.keys():
v["pv"] = v["pv_total"] - yd["pv_total"]
set_pv_(v, "pv", "dau_count", "pv_dau")
set_pv_(v, "pv", "new_published_count", "pv_published")
set_pv_(v, "effective_reading", "pv", "reading_pv")
set_pv_(v, "comments_count", "pv", "comments_pv")
set_pv_(v, "zan_count", "pv", "zan_pv")
set_pv_(v, "like_count", "pv", "like_pv")
set_pv_(v, "share_count", "pv", "share_pv")
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
set_pv_(v, "show_all_pv_uv", "new_published_count", "show_all_pv_uv_new_published")
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_EDIT_INFO + \
"/" + _id + "_" + v['channel'] + "_" + str(k)
r = requests.post(url, headers=JSON_HEADER,
data=json.dumps(v), timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error("request edit index failed, status_code:%d, reason:%s, %s, %s",
r.status_code, json.dumps(r.json()), url, json.dumps(v))
def get_news(nday=1):
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
news = {}
sql = "select id, published_at, title from news where published_at >= \"%s\" and published_at < \"%s\"" % (
day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
news_id = int(v[0])
news[news_id] = {}
news[news_id]['@timestamp'] = str(v[1]).replace(" ", "T") + "+08:00"
news[news_id]['news_id'] = news_id
news[news_id]['title'] = v[2]
return news
def process(nday=1):
mysql_tool.connectdb(host="47.96.238.205", database="taozuiredian-news")
list_pv_total = get_hot_list_pv_info(nday)
mysql_tool.closedb()
for k, v in list_pv_total.items():
# 总榜和蹿红榜的所有资讯会在凌晨一点更新，这样在凌晨计算的这两个榜的pv_total就
# 失效了, 需要重新计算一次
_id = time_tool.get_someday_str(-nday)
url = config.URL_EDIT_INFO + "/" + _id + "_news_" + str(k)
r = requests.get(url, headers=config.JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
logger.error(
"request edit_info index failed, status_code:%d, reason:%s, k:%s",
r.status_code, r.reason, k)
else:
r_json = r.json()
if "_source" in r_json.keys():
yd = r_json["_source"]
yd['pv_total'] = v
yd['@timestamp'] = time_tool.get_someday_es_format(-nday)
url = config.URL_EDIT_INFO + "/" + _id + "_news_" + str(k)
r = requests.post(url,
headers=config.JSON_HEADER,
data=json.dumps(yd),
timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request edit index failed, status_code:%d, reason:%s, %s, %s",
r.status_code, json.dumps(r.json()), url,
json.dumps(v))
def process_old(nday=1):
mysql_tool.connectdb(host="47.96.238.205", database="taozuiredian-news")
mysql_tool.querydb("SET NAMES utf8mb4")
data_news = edit_info.get_edit_news_info(nday)
data_news = edit_info.get_hot_news_info(nday, data_news)
data_news = edit_info.get_hot_list_info(nday, data_news)
data_news = edit_info.get_news_effective_reading(nday, data_news)
data_video = edit_info.get_edit_video_info(nday)
data_video = edit_info.get_video_effective_reading(nday, data_video)
mysql_tool.closedb()
for data in [data_news, data_video]:
for k , v in data.items():
tid = time_tool.get_someday_str(-nday)
url = edit_info.URL_ELASTICSEARCH_EDIT_INFO + \
"/" + tid + "_" + v["channel"] + "_" + str(k)
r = requests.get(url, headers=edit_info.JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
print r.status_code, r.reason, r.json()
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
r = requests.post(url, headers=edit_info.JSON_HEADER,
data=json.dumps(v), timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
print r.status_code, r.reason, r.json()
else:
r_json = r.json()
if "_source" in r_json.keys():
td = r_json["_source"]
if 'effective_reading' in td.keys():
# td['effective_reading'] = v['effective_reading']
del td['effective_reading']
print td
def get_news_category(nday=1, news_id_arr=[]):
day = time_tool.get_someday_str(-nday + 1)
list_news = {}
sql = "select id, category_id from news where id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = [str(nid) for nid in news_id_arr[i:i + 1000]]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger,
"select id, category_id from news for %d news" % len(id_arr))
for v in rt:
news_id = int(v[0])
category_id = int(v[1])
if category_id in list_news.keys():
list_news[category_id].append(news_id)
else:
list_news[category_id] = [news_id]
list_news[define.HOT_NEWS_CATEGORY_ID] = []
sql = "select id from news where hot_at < \"" + day + "\" and id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = [str(nid) for nid in news_id_arr[i:i + 1000]]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger,
"select hot id from news for %d news" % len(id_arr))
for v in rt:
list_news[define.HOT_NEWS_CATEGORY_ID].append(int(v[0]))
list_news = get_list_news_category(list_news)
return list_news
def process(nday=1):
mysql_tool.connectdb()
mysql_tool.querydb("SET NAMES utf8mb4")
data = get_task_info(nday)
mysql_tool.closedb()
dau, _ = active.get_user_device_count(active.get_query(-nday))
for k, v in data.items():
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
if k != 0 and 'num' in v.keys() and 'num_for_parent' in v.keys():
v['num_for_no_child'] = v['num'] - v['num_for_parent']
v['n_for_no_child'] = v['n'] - v['n_for_parent']
if dau > 0:
v['num_dau'] = float(v['num']) / dau
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_TASK_INFO + "/" + _id + "_" + str(k)
r = requests.post(url,
headers=JSON_HEADER,
data=json.dumps(v),
timeout=(10, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request task_info index failed, status_code:%d, reason:%s",
r.status_code, r.reason)
def process(nday=1):
mysql_tool.connectdb()
data = get_parent_info(nday)
mysql_tool.closedb()
for k, v in data.items():
# 获取前一天各vip等级师傅数量, 各vip新增师傅数量 = 今日各vip师傅数量 - 昨天各vip师傅数量
yid = time_tool.get_someday_str(-nday - 1)
url = URL_ELASTICSEARCH_PARENT_VIP_INFO + "/" + yid + "_" + k
r = requests.get(url, headers=JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
logger.error(
"request parent_info index failed, status_code:%d, reason:%s, k:%s",
r.status_code, r.reason, k)
else:
r_json = r.json()
if "_source" in r_json.keys():
yd = r_json["_source"]
if "num" in yd.keys():
v["num_new"] = v["num"] - yd["num"]
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
v["vip"] = k
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_PARENT_VIP_INFO + "/" + _id + "_" + k
r = requests.post(url,
headers=JSON_HEADER,
data=json.dumps(v),
timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request parent_info index failed, status_code:%d, reason:%s",
r.status_code, r.reason)
data_num_user = {}
data_num_user['@timestamp'] = time_tool.get_someday_es_format(-nday)
user_per_channel = nui.get_new_user(nday=nday)
data_num_user["num_user_new"] = user_per_channel["all_channel"]
data_num_user["vip"] = ""
url = URL_ELASTICSEARCH_PARENT_VIP_INFO + "/" + time_tool.get_someday_str(
-nday)
r = requests.post(url,
headers=JSON_HEADER,
data=json.dumps(data_num_user),
timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request parent_info index failed, status_code:%d, reason:%s",
r.status_code, r.reason)
def get_edit_news_info(user_arr=[], user_type="new", ner={}, nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
# 获取资讯名称
news_categories = {}
sql = "select id, name from news_categories"
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
news_categories[int(v[0])] = v[1]
news_id_arr = [str(news_id) for news_id in ner.keys()]
# 获取普通类目有效阅读
sql = "select id, category_id from news where id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id, category_id from news for %d news" % len(id_arr))
for v in rt:
news_id = int(v[0])
category_id = int(v[1])
if category_id in data.keys():
data[category_id]["effective_reading"] += ner[news_id]
else:
set_for_one_category(
data, category_id, user_type, u"资讯", "news", news_categories)
data[category_id]["effective_reading"] = ner[news_id]
# 获取评论
sql = "select n.category_id, count(*) from news_comments as nc join news as n on (nc.news_id = n.id) where nc.user_id in (%s) and nc.created_at >= \"" + \
day1 + "\" and nc.created_at < \"" + day2 + "\" group by n.category_id"
set_common_edit_info(sql, user_arr, data, "select from news_comments",
"comments_count", user_type, "news", u"资讯", news_categories)
# 获取收藏数
sql = "select n.category_id, count(*) from user_like_news as uln join news as n on(uln.news_id = n.id) where uln.user_id in (%s) and uln.created_at >= \"" + \
day1 + "\" and uln.created_at < \"" + day2 + "\" group by n.category_id"
set_common_edit_info(sql, user_arr, data, "select from user_like_news",
"like_count", user_type, "news", u"资讯", news_categories)
# 获取点赞数
sql = "select n.category_id, count(*) from user_zan_news_comments as uznc join news_comments as nc on (uznc.comment_id = nc.id) join news as n on(nc.news_id = n.id) where uznc.user_id in (%s) and uznc.created_at >= \"" + \
day1 + "\" and uznc.created_at < \"" + day2 + "\" group by n.category_id"
set_common_edit_info(sql, user_arr, data, "select from user_zan_news_comments",
"zan_count", user_type, "news", u"资讯", news_categories)
return data
def get_hot_news_info(user_arr=[], ner={}, data={}, nday=1):
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
data[edit_info.HOT_NEWS_CATEGORY_ID] = {}
data[edit_info.HOT_NEWS_CATEGORY_ID]['user_type'] = "new"
data[edit_info.HOT_NEWS_CATEGORY_ID]['channel_name'] = "资讯"
data[edit_info.HOT_NEWS_CATEGORY_ID]['channel'] = "news"
data[edit_info.HOT_NEWS_CATEGORY_ID]['category_id'] = edit_info.HOT_NEWS_CATEGORY_ID
data[edit_info.HOT_NEWS_CATEGORY_ID]['category_name'] = edit_info.HOT_SHOW
# 获取热点资讯有效阅读
news_id_arr = [str(news_id) for news_id in ner.keys()]
sql = "select id from news where id in (%s) and hot_at < \"" + day2 + "\""
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id from news for %d news" % len(id_arr))
for v in rt:
news_id = int(v[0])
if 'effective_reading' in data[edit_info.HOT_NEWS_CATEGORY_ID].keys():
data[edit_info.HOT_NEWS_CATEGORY_ID]['effective_reading'] += ner[news_id]
else:
data[edit_info.HOT_NEWS_CATEGORY_ID]['effective_reading'] = ner[news_id]
# 获取热点资讯评论数
sql = "select count(*) from news_comments as nc join news as n on(nc.news_id = n.id) where n.id in (select id from news where hot_at < \"" + \
day2 + "\") and nc.created_at >= \"" + day1 + \
"\" and nc.created_at < \"" + day2 + "\" and nc.user_id in (%s)"
set_common_hot_info(
sql, user_arr, data[edit_info.HOT_NEWS_CATEGORY_ID], 'comments_count', "select from news_comments")
# 获取热点资讯收藏数
sql = "select count(*) from user_like_news as uln join news as n on(uln.news_id = n.id) where n.id in (select id from news where hot_at < \"" + \
day2 + "\") and uln.created_at >= \"" + day1 + \
"\" and uln.created_at < \"" + day2 + "\" and uln.user_id in (%s)"
set_common_hot_info(
sql, user_arr, data[edit_info.HOT_NEWS_CATEGORY_ID], 'like_count', "select from user_like_news")
# 获取热点资讯点赞数
sql = "select count(*) from user_zan_news_comments as uznc join news_comments as nc on (uznc.comment_id = nc.id) join news as n on(nc.news_id = n.id) where n.id in (select id from news where hot_at < \"" + \
day2 + "\") and uznc.created_at >= \"" + day1 + \
"\" and uznc.created_at < \"" + day2 + "\" and uznc.user_id in (%s)"
set_common_hot_info(sql, user_arr, data[edit_info.HOT_NEWS_CATEGORY_ID],
'zan_count', "select from user_zan_news_comments")
return data
def get_task_info(nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday + 1)
sql_task_name = "select id, name from user_tasks"
rt = mysql_tool.querydb(sql_task_name, logger, sql_task_name)
for v in rt:
data[v[0]] = {}
data[v[0]]['task_id'] = int(v[0])
data[v[0]]['task_name'] = v[1]
sql_task_num = "select task_id, sum(today_count), count(distinct user_id) from user_task_day_records where day = \"%s\" and today_count !=0 group by task_id" % day1
rt = mysql_tool.querydb(sql_task_num, logger, sql_task_num)
for v in rt:
if v[0] in data.keys():
data[v[0]]['num'] = int(v[1])
data[v[0]]['n'] = int(v[2])
sql_task_parent_num = "select task_id, sum(today_count), count(distinct user_id) from user_task_day_records where user_id in (select distinct parent_id from users where bind_parent_at < \"%s\") and day = \"%s\" and today_count !=0 group by task_id" % (
day2, day1)
rt = mysql_tool.querydb(sql_task_parent_num, logger, sql_task_parent_num)
for v in rt:
if v[0] in data.keys():
data[v[0]]['num_for_parent'] = int(v[1])
data[v[0]]['n_for_parent'] = int(v[2])
sql = "select sum(today_count), count(distinct user_id) from user_task_day_records where day = \"%s\" and user_id in (select id from users where registered_at >= \"%s\" and registered_at < \"%s\") and task_id in(1,2,3,22,4,5,29,30,31,33)" % (
day1, day1, day2)
rta = mysql_tool.querydb(sql, logger, sql)
data[0] = {}
data[0]['task_id'] = 0
data[0]['task_name'] = u"今日新增注册用户完成新手任务平均个数"
if rta[0][1] > 0:
data[0]['num'] = round(float(rta[0][0]) / float(rta[0][1]), 2)
sql = "select sum(today_count), count(distinct user_id) from user_task_day_records where day = \"%s\" and user_id in (select id from users where id in (select distinct parent_id from users where bind_parent_at >= \"%s\" and bind_parent_at < \"%s\") and registered_at >= \"%s\" and registered_at < \"%s\") and task_id in(1,2,3,22,4,5,29,30,31,33)" % (
day1, day1, day2, day1, day2)
rtp = mysql_tool.querydb(sql, logger, sql)
if rtp[0][1] > 0:
data[0]['num_for_parent'] = round(
float(rtp[0][0]) / float(rtp[0][1]), 2)
if rta[0][1] - rtp[0][1] > 0:
data[0]['num_for_no_child'] = round(
float(rta[0][0] - rtp[0][0]) / float(rta[0][1] - rtp[0][1]), 2)
return data
def get_video_effective_reading(nday=1, data_video={}):
# 获取视频有效阅读
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
ver = {}
sql = "select video_id, count(*) from video_effective_readings where created_at >= \"%s\" and created_at < \"%s\" and effective != 0 group by video_id" % (day1, day2)
sob = mysql_tool.sql_obj()
rt = sob.querydb(sql, logger, sql)
total = 0
for v in rt:
ver[int(v[0])] = int(v[1])
total += int(v[1])
sob.closedb()
data_video[TOTAL_ID]['effective_reading'] = total
video_id_arr = [str(video_id) for video_id in ver.keys()]
# 获取普通类目有效阅读
sql = "select id, category_id from videos where id in (%s)"
for i in range(0, len(video_id_arr), 1000):
id_arr = video_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id, category_id from videos for %d videos" % len(id_arr))
for v in rt:
video_id = int(v[0])
category_id = int(v[1])
if category_id in data_video.keys():
if 'effective_reading' in data_video[category_id].keys():
data_video[category_id]['effective_reading'] += ver[video_id]
else:
data_video[category_id]['effective_reading'] = ver[video_id]
# 获取热点有效阅读
if HOT_VIDEO_CATEGORY_ID in data_video.keys():
sql = "select id from videos where id in (%s) and hot_at < \"" + \
day2 + "\""
for i in range(0, len(video_id_arr), 1000):
id_arr = video_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id from hot videos for %d videos" % len(id_arr))
for v in rt:
video_id = int(v[0])
if 'effective_reading' in data_video[HOT_VIDEO_CATEGORY_ID].keys():
data_video[HOT_VIDEO_CATEGORY_ID]['effective_reading'] += ver[video_id]
else:
data_video[HOT_VIDEO_CATEGORY_ID]['effective_reading'] = ver[video_id]
return data_video
def get_parent_info(nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday + 1)
# 各vip等级师傅数量
sql_vip_parent_count = "select level, count(id) from users where id in (select distinct parent_id from users where bind_parent_at < \"%s\") group by level" % day2
rt = mysql_tool.querydb(sql_vip_parent_count, logger, sql_vip_parent_count)
for v in rt:
k = str(v[0])
if k in data.keys():
data[k]['num'] = int(v[1])
else:
data[k] = {}
data[k]['num'] = int(v[1])
return data
def get_child(nday=1):
data = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday + 1)
sql = "select us.channel, count(us.channel) from users as u left join user_statistics as us on (u.id = us.user_id) where u.parent_id in (select id from users where registered_at >= \"%s\" and registered_at < \"%s\") and u.bind_parent_at >= \"%s\" and u.bind_parent_at < \"%s\" group by us.channel" % (
day1, day2, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[v[0]] = int(v[1])
sql_all = "select count(*) from users where parent_id in (select id from users where registered_at >= \"%s\" and registered_at < \"%s\") and bind_parent_at >= \"%s\" and bind_parent_at < \"%s\"" % (
day1, day2, day1, day2)
rt_all = mysql_tool.querydb(sql_all, logger, sql_all)
if len(rt_all) > 0:
data["all_channel"] = int(rt_all[0][0])
return data
def get_hot_list_info(nday=1, data={}):
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
for table, category_id, category_name in [("news_hot_lists", NEWS_HOT_LISTS, NEWS_HOT_LISTS_SHOW),
("news_hot_seven_day_lists",
NEWS_HOT_SEVEN, NEWS_HOT_SEVEN_SHOW),
("news_hot_total_lists", NEWS_HOT_TOTAL, NEWS_HOT_TOTAL_SHOW)]:
data[category_id] = {}
# 获取新增
sql = "select count(*) from %s where created_at >= \"%s\" and created_at < \"%s\"" % (
table, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[category_id]['new_published_count'] = int(v[0])
# 获取评论数
sql = "select count(*) from news_comments as nc join news as n on(nc.news_id = n.id) where n.id in (select news_id from %s) and nc.created_at >= \"%s\" and nc.created_at < \"%s\"" % (table, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[category_id]['comments_count'] = int(v[0])
# 获取收藏数
sql = "select count(*) from user_like_news as uln join news as n on(uln.news_id = n.id) where n.id in (select news_id from %s) and uln.created_at >= \"%s\" and uln.created_at < \"%s\"" % (table, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[category_id]['like_count'] = int(v[0])
# 获取点赞数
sql = "select count(*) from user_zan_news_comments as uznc join news_comments as nc on (uznc.comment_id = nc.id) join news as n on(nc.news_id = n.id) where n.id in (select news_id from %s) and uznc.created_at >= \"%s\" and uznc.created_at < \"%s\"" % (table, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[category_id]['zan_count'] = int(v[0])
# 获取pv.
sql = "select sum(real_pv) from news where id in (select news_id from %s)" % table
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[category_id]['channel_name'] = u"资讯"
data[category_id]['channel'] = "news"
data[category_id]['category_id'] = category_id
data[category_id]['pv_total'] = int(v[0])
data[category_id]['category_name'] = category_name
return data
def get_child(user_channel={}, nday=1):
data = {}
data["all_channel"] = 0
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
sql = "select count(us.channel) from users as u left join user_statistics as us on (u.id = us.user_id) where u.parent_id in (%s) and u.bind_parent_at >= \"%s\" and u.bind_parent_at < \"%s\""
for k, user_arr in user_channel.items():
data[k] = 0
for i in range(0, len(user_arr), 1000):
user_to_sql = user_arr[i:i+1000]
sql_use = sql % (",".join(user_to_sql), day1, day2)
rt = mysql_tool.querydb(
sql_use, logger, "get child count for %d users" % len(user_to_sql))
if len(rt) > 0 and rt[0][0] is not None:
data[k] += int(rt[0][0])
data["all_channel"] += data[k]
return data
def process(nday=1):
mysql_tool.connectdb()
data = get_parent_info(nday)
mysql_tool.closedb()
for k, v in data.items():
# 获取前一天师傅的总收入和师傅的进贡收入来计算当日的收入
yid = time_tool.get_someday_str(-nday - 1)
url = URL_ELASTICSEARCH_PARENT_INFO + "/" + yid + "_" + k
r = requests.get(url, headers=JSON_HEADER, timeout=(10, 20))
if 200 != r.status_code:
logger.error(
"request parent_info index failed, status_code:%d, reason:%s, k:%s",
r.status_code, r.reason, k)
else:
r_json = r.json()
if "_source" in r_json.keys():
yd = r_json["_source"]
# if "total_income" in v.keys():
# ydti = yd["total_income"] if "total_income" in yd.keys() else 0
# v["today_income"] = v["total_income"] - ydti
if "total_rebate" in v.keys():
ydtr = yd["total_rebate"] if "total_rebate" in yd.keys(
) else 0
v["today_rebate"] = v["total_rebate"] - ydtr
if "num_active_child" in v.keys(
) and v["num_active_child"] > 0:
# 平均进贡值 = 当日进贡收入 / 活跃徒弟数 详见《后台数据维度(3).xlsx》
v["average_rebate_per_child"] = round(
v["today_rebate"] / float(v["num_active_child"]),
2)
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
v["channel"] = k
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_PARENT_INFO + "/" + _id + "_" + k
r = requests.post(url,
headers=JSON_HEADER,
data=json.dumps(v),
timeout=(10, 20))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request parent_info index failed, status_code:%d, reason:%s",
r.status_code, r.reason)
def get_task(nday=1):
data = {}
data_user_num = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday + 1)
sql = "select us.channel, sum(ut.today_count), count(distinct u.id) from users as u left join user_statistics as us on (u.id = us.user_id) right join user_task_day_records as ut on (u.id = ut.user_id and u.id = ut.from_user_id) where u.registered_at >= \"%s\" and u.registered_at < \"%s\" and ut.day = \"%s\" group by us.channel" % (
day1, day2, day1)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[v[0]] = int(v[1])
data_user_num[v[0]] = int(v[2])
sql_all = "select sum(ut.today_count), count(distinct u.id) from users as u right join user_task_day_records as ut on (u.id = ut.user_id and u.id = ut.from_user_id) where u.registered_at >= \"%s\" and u.registered_at < \"%s\" and ut.day = \"%s\"" % (
day1, day2, day1)
rt_all = mysql_tool.querydb(sql_all, logger, sql_all)
if len(rt_all) > 0:
data["all_channel"] = int(rt_all[0][0])
data_user_num["all_channel"] = int(rt_all[0][1])
logger.debug(data)
return data, data_user_num
def process(nday=1):
tid = time_tool.get_someday_str(-nday)
tid_ = time_tool.get_someday_str(-nday - 1)
failed_url = []
for url in check_url:
if url.find("remain") != -1:
url = CHECK_DOMAIN + url % tid_
else:
url = CHECK_DOMAIN + url % tid
r = requests.get(url, headers=JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
failed_url.append(url)
else:
r_json = r.json()
if False == r_json["found"]:
failed_url.append(url)
if len(failed_url) > 0:
failed_url_str = u"下面数据更新失败<br>" + "<br>".join(failed_url)
myEmail.send_email("*****@*****.**",
failed_url_str,
subject=u"淘热点数据统计监测")
def get_video(nday=1):
data = {}
data_user_num = {}
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday + 1)
sql = "select us.channel, count(vr.user_id), count(distinct u.id) from users as u left join user_statistics as us on (u.id = us.user_id) right join video_effective_readings as vr on (u.id = vr.user_id) where u.registered_at >= \"%s\" and u.registered_at < \"%s\" and vr.created_at >= \"%s\" and vr.created_at < \"%s\" and vr.effective = 1 group by us.channel" % (
day1, day2, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[v[0]] = int(v[1])
data_user_num[v[0]] = int(v[2])
sql_all = "select count(vr.user_id), count(distinct u.id) from users as u right join video_effective_readings as vr on (u.id = vr.user_id) where u.registered_at >= \"%s\" and u.registered_at < \"%s\" and vr.created_at >= \"%s\" and vr.created_at < \"%s\" and vr.effective = 1" % (
day1, day2, day1, day2)
rt_all = mysql_tool.querydb(sql_all, logger, sql_all)
if len(rt_all) > 0:
data["all_channel"] = int(rt_all[0][0])
data_user_num["all_channel"] = int(rt_all[0][1])
logger.debug(data)
return data, data_user_num
def process(nday=1):
mysql_tool.connectdb()
data = get_cash_user(nday)
mysql_tool.closedb()
data['@timestamp'] = time_tool.get_someday_es_format(-nday)
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_CASH_INFO + "/" + _id
r = requests.post(url,
headers=JSON_HEADER,
data=json.dumps(data),
timeout=(10, 20))
if 200 != r.status_code and 201 != r.status_code:
logger.error(
"request active_user_info index failed, status_code:%d, reason:%s",
r.status_code, r.reason)
def process(nday=1):
new_user_arr = get_new_user_arr(nday)
mysql_tool.connectdb(host="47.96.238.205", database="taozuiredian-news")
mysql_tool.querydb("SET NAMES utf8mb4")
ner = get_news_effective_readings(new_user_arr, nday)
data_news = get_edit_news_info(new_user_arr, "new", ner, nday)
data_news = get_hot_news_info(new_user_arr, ner, data_news, nday)
data_news = get_hot_list_info(new_user_arr, ner, data_news, nday)
ver = get_video_effective_readings(new_user_arr, nday)
data_video = get_edit_video_info(new_user_arr, "new", ver, nday)
mysql_tool.closedb()
for data in [data_news, data_video]:
for k, v in data.items():
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
_id = time_tool.get_someday_str(-nday)
url = URL_ELASTICSEARCH_EDIT_USER_INFO + \
"/" + _id + "_" + v['user_type'] + \
"_" + v['channel'] + "_" + str(k)
r = requests.post(url, headers=JSON_HEADER,
data=json.dumps(v), timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error("request edit index failed, status_code:%d, reason:%s, %s, %s",
r.status_code, json.dumps(r.json()), url, json.dumps(v))
url_edit = edit_info.URL_ELASTICSEARCH_EDIT_INFO + \
"/" + _id + "_" + v['channel'] + "_" + str(k)
r = requests.get(url_edit, headers=JSON_HEADER, timeout=(30, 120))
if 200 != r.status_code:
logger.error("request edit_user_info failed, status_code:%d, reason:%s",
r.status_code, json.dumps(r.json()))
else:
r_json = r.json()
if "_source" in r_json.keys():
info = r_json["_source"]
v_ = copy.deepcopy(v)
v_["user_type"] = "not_new"
set_a_key(v, info, v_, "effective_reading")
set_a_key(v, info, v_, "comments_count")
set_a_key(v, info, v_, "like_count")
set_a_key(v, info, v_, "zan_count")
url_ = URL_ELASTICSEARCH_EDIT_USER_INFO + \
"/" + _id + "_not_new_" + v['channel'] + "_" + str(k)
r = requests.post(url_, headers=JSON_HEADER,
data=json.dumps(v_), timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error("request edit index failed, status_code:%d, reason:%s",
r.status_code, json.dumps(r.json()))
def process(nday=1):
mysql_tool.connectdb(host="47.96.238.205", database="taozuiredian-news")
mysql_tool.querydb("SET NAMES utf8mb4")
data_news = edit_info.get_edit_news_info(nday)
data_news = edit_info.get_hot_news_info(nday, data_news)
# data_news = edit_info.get_hot_list_info(nday, data_news)
# data_news = edit_info.get_news_effective_reading(nday, data_news)
# data_video = edit_info.get_edit_video_info(nday)
# data_video = edit_info.get_video_effective_reading(nday, data_video)
mysql_tool.closedb()
for data in [data_news]:
for k, v in data.items():
_id = time_tool.get_someday_str(-nday)
v['@timestamp'] = time_tool.get_someday_es_format(-nday)
url = "http://localhost:8200/edit_info/doc" + \
"/" + _id + "_" + v['channel'] + "_" + str(k)
r = requests.post(url, headers=edit_info.JSON_HEADER,
data=json.dumps(v), timeout=(30, 120))
if 200 != r.status_code and 201 != r.status_code:
logger.error("request edit index failed, status_code:%d, reason:%s, %s, %s",
r.status_code, json.dumps(r.json()), url, json.dumps(v))
def get_task(user_channel={}, nday=1):
data = {}
data["all_channel"] = {}
data["all_channel"]["num_task"] = 0
data["all_channel"]["num_user"] = 0
day_str = time_tool.get_someday_str(-nday)
sql = "select sum(today_count), count(distinct user_id) from user_task_day_records where user_id in (%s) and user_id = from_user_id and day = \"%s\""
for k, user_arr in user_channel.items():
data[k] = {}
data[k]["num_task"] = 0
data[k]["num_user"] = 0
for i in range(0, len(user_arr), 1000):
user_to_sql = user_arr[i:i+1000]
sql_use = sql % (",".join(user_to_sql), day_str)
rt = mysql_tool.querydb(
sql_use, logger, "get task count for %d users" % len(user_to_sql))
if len(rt) > 0 and rt[0][0] is not None:
data[k]["num_task"] += int(rt[0][0])
data[k]["num_user"] += int(rt[0][1])
data["all_channel"]["num_task"] += data[k]["num_task"]
data["all_channel"]["num_user"] += data[k]["num_user"]
return data
def get_news_effective_reading(nday=1, data_news={}):
# 获取资讯有效阅读
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
ner = {}
sql = "select news_id, count(*) from news_effective_readings where created_at >= \"%s\" and created_at < \"%s\" and effective != 0 group by news_id" % (day1, day2)
sob = mysql_tool.sql_obj()
rt = sob.querydb(sql, logger, sql)
total = 0
for v in rt:
ner[int(v[0])] = int(v[1])
total += int(v[1])
sob.closedb()
data_news[TOTAL_ID]['effective_reading'] = total
news_id_arr = [str(news_id) for news_id in ner.keys()]
# 获取榜单有效阅读
for table, category_id in [("news_hot_seven_day_lists", NEWS_HOT_SEVEN),
("news_hot_lists", NEWS_HOT_LISTS),
("news_hot_total_lists", NEWS_HOT_TOTAL)]:
if category_id in data_news.keys():
sql = "select news_id from %s where news_id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (table, ",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select news_id for %d %s news" % (len(id_arr), table))
for v in rt:
news_id = int(v[0])
if 'effective_reading' in data_news[category_id].keys():
data_news[category_id]['effective_reading'] += ner[news_id]
else:
data_news[category_id]['effective_reading'] = ner[news_id]
# 获取普通类目有效阅读
sql = "select id, category_id from news where id in (%s)"
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id, category_id from news for %d news" % len(id_arr))
for v in rt:
news_id = int(v[0])
category_id = int(v[1])
if category_id in data_news.keys():
if 'effective_reading' in data_news[category_id].keys():
data_news[category_id]['effective_reading'] += ner[news_id]
else:
data_news[category_id]['effective_reading'] = ner[news_id]
# 获取热点有效阅读
if HOT_NEWS_CATEGORY_ID in data_news.keys():
sql = "select id from news where id in (%s) and hot_at < \"" + \
day2 + "\""
for i in range(0, len(news_id_arr), 1000):
id_arr = news_id_arr[i:i+1000]
sql_use = sql % (",".join(id_arr))
rt = mysql_tool.querydb(
sql_use, logger, "select id from news for %d news" % len(id_arr))
for v in rt:
news_id = int(v[0])
if 'effective_reading' in data_news[HOT_NEWS_CATEGORY_ID].keys():
data_news[HOT_NEWS_CATEGORY_ID]['effective_reading'] += ner[news_id]
else:
data_news[HOT_NEWS_CATEGORY_ID]['effective_reading'] = ner[news_id]
return data_news
def get_hot_news_info(nday=1, data={}):
day0 = time_tool.get_someday_str(-nday-1)
day1 = time_tool.get_someday_str(-nday)
day2 = time_tool.get_someday_str(-nday+1)
tommorow = time_tool.get_someday_str(1)
# 获取当天发布热点资讯
hot_new_published_count = 0
data[HOT_NEWS_CATEGORY_ID] = {}
sql = "select count(*) from news where hot_at >= \"%s\" and hot_at < \"%s\" and published_at < \"%s\"" % (day1, day2, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['new_published_count'] = int(v[0])
hot_new_published_count = int(v[0])
# 获取今日发布的热点旧资讯占比数
sql = "select count(*) from news as n join news_raws as nr on (n.raw_id = nr.id) where n.hot_at >= \"%s\" and n.hot_at < \"%s\" and n.published_at < \"%s\" and nr.created_at < \"%s\"" % (day1, day2, day2, day1)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
if hot_new_published_count > 0:
data[HOT_NEWS_CATEGORY_ID]['old_published_percentage_f'] = float(
v[0])/hot_new_published_count
# 获取热点资讯发布与抓取平均时间间隔
sql = "select avg(timestampdiff(second, nr.created_at, n.published_at)) from news as n join news_raws as nr on (n.raw_id = nr.id) where n.hot_at >= \"%s\" and n.hot_at < \"%s\" and n.published_at < \"%s\"" % (day1, day2, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['interval_pub_crawl'] = int(v[0])
data[HOT_NEWS_CATEGORY_ID]['interval_pub_crawl_show'] = transfer_seconds2time(
int(v[0]))
# 获取当天挑选热点资讯
sql = "select count(*) from news_raw_auto_releases where created_at >= \"%s\" and created_at < \"%s\" and is_hot !=0" % (day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['new_choosed_count'] = int(v[0])
# 获取昨天挑选热点资讯
sql = "select count(*) from news_raw_auto_releases where created_at >= \"%s\" and created_at < \"%s\" and is_hot !=0" % (day0, day1)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['yd_choosed_count'] = int(v[0])
# 获取热点资讯评论数
sql = "select count(*) from news_comments as nc join news as n on(nc.news_id = n.id) where n.id in (select id from news where hot_at < \"%s\") and nc.created_at >= \"%s\" and nc.created_at < \"%s\"" % (day2, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['comments_count'] = int(v[0])
# 获取热点资讯收藏数
sql = "select count(*) from user_like_news as uln join news as n on(uln.news_id = n.id) where n.id in (select id from news where hot_at < \"%s\") and uln.created_at >= \"%s\" and uln.created_at < \"%s\"" % (day2, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['like_count'] = int(v[0])
# 获取热点资讯点赞数
sql = "select count(*) from user_zan_news_comments as uznc join news_comments as nc on (uznc.comment_id = nc.id) join news as n on(nc.news_id = n.id) where n.id in (select id from news where hot_at < \"%s\") and uznc.created_at >= \"%s\" and uznc.created_at < \"%s\"" % (day2, day1, day2)
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['zan_count'] = int(v[0])
# 获取热点资讯的pv.
sql = "select sum(real_pv) from news where hot_at < \"%s\"" % tommorow
rt = mysql_tool.querydb(sql, logger, sql)
for v in rt:
data[HOT_NEWS_CATEGORY_ID]['channel_name'] = u"资讯"
data[HOT_NEWS_CATEGORY_ID]['channel'] = "news"
data[HOT_NEWS_CATEGORY_ID]['category_id'] = HOT_NEWS_CATEGORY_ID
data[HOT_NEWS_CATEGORY_ID]['pv_total'] = int(v[0])
data[HOT_NEWS_CATEGORY_ID]['category_name'] = HOT_SHOW
return data
