159 lines
7.1 KiB
Python
159 lines
7.1 KiB
Python
# -*- coding: utf-8 -*-
|
|
from ops.mmysql import MysqlBase
|
|
from ops.plog import define_logger
|
|
import logging
|
|
import datetime
|
|
import json
|
|
from collections import defaultdict
|
|
import pdb
|
|
import sys
|
|
define_logger("/data/logs/ops/tap_weekly.log")
|
|
log = logging.getLogger(__name__)
|
|
db_conf = {'user': 'mytga', 'pswd': 'gzVwh4HGR68G', 'host': '10.10.3.5', 'db': 'external_data'}
|
|
|
|
|
|
class TapWeeklyReport:
|
|
def __init__(self, day):
|
|
self.day = day
|
|
self.db_conn = MysqlBase(**db_conf)
|
|
self.limit = 20
|
|
|
|
def get_weekly_days(self):
|
|
weekly_days = []
|
|
for i in range(0, 7):
|
|
current_day = (datetime.datetime.strptime(self.day, '%Y-%m-%d') - datetime.timedelta(days=i)).strftime(
|
|
'%Y-%m-%d')
|
|
weekly_days.append(current_day)
|
|
return weekly_days
|
|
|
|
|
|
def build(self):
|
|
|
|
weekly_day = json.dumps(self.get_weekly_days()).strip('[]')
|
|
tap_types = ("new", "download", "reserve", "sell", "played")
|
|
all_data = dict()
|
|
for tap_type in tap_types:
|
|
sql = f"""select
|
|
gameid,
|
|
min(`order`), max(`order`)
|
|
from
|
|
taptap_collect
|
|
where
|
|
catename='{tap_type}'
|
|
and date in ({weekly_day})
|
|
group by
|
|
gameid
|
|
"""
|
|
|
|
data = self.db_conn.query(sql)
|
|
if data:
|
|
try:
|
|
for line in data:
|
|
gameid, min_order, max_order = line
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["min_order"] = min_order
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["max_order"] = max_order
|
|
|
|
# all_data[tap_type].append(data)
|
|
except Exception:
|
|
log.error(f"get {tap_type} min/max order failed!", exc_info=True)
|
|
|
|
sql02 = f"""select gameid,title,score,tags,`order`,watch,download,sell,review,reserve,topic from
|
|
taptap_collect
|
|
where
|
|
catename='{tap_type}'
|
|
and date ='{self.day}'
|
|
"""
|
|
data02 = self.db_conn.query(sql02)
|
|
if data02:
|
|
try:
|
|
for line in data02:
|
|
gameid, title, score, tags, order, watch, download, sell, review, reserve, topic = line
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["title"] = title
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["score"] = score
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["tags"] = ",".join(
|
|
tags.split(',')[0:3])
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["order"] = order
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["watch"] = watch
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["download"] = download
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["sell"] = sell
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["review"] = review
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["reserve"] = reserve
|
|
all_data.setdefault(gameid, {}).setdefault(tap_type, {})["topic"] = topic
|
|
|
|
except Exception:
|
|
log.error(f"get {tap_type} details failed!", exc_info=True)
|
|
# check data if not title ,get from last day
|
|
for gameid in all_data.keys():
|
|
for catename in all_data[gameid]:
|
|
if not all_data[gameid][catename].get('title'):
|
|
all_data[gameid][catename]["title"], all_data[gameid][catename]["score"], \
|
|
all_data[gameid][catename]["tags"], all_data[gameid][catename]["order"], all_data[gameid][catename][
|
|
"watch"], all_data[gameid][catename]["download"], all_data[gameid][catename]["sell"], \
|
|
all_data[gameid][catename]["review"], all_data[gameid][catename]["reserve"], \
|
|
all_data[gameid][catename]["topic"] = self.get_last_data(gameid, catename)
|
|
if not all_data[gameid][catename]["title"]:
|
|
all_data[gameid].pop(catename)
|
|
else:
|
|
all_data[gameid][catename]["tags"] = ",".join(
|
|
all_data[gameid][catename]["tags"].split(',')[0:3])
|
|
|
|
return all_data
|
|
|
|
def get_last_data(self, gameid, catename):
|
|
sql = f"""select title,score,tags,`order`,watch,download,sell,review,reserve,topic from
|
|
taptap_collect
|
|
where
|
|
catename='{catename}'
|
|
and gameid ={gameid} order by date desc limit 1 """
|
|
data = self.db_conn.query(sql)
|
|
|
|
try:
|
|
title, score, tags, order, watch, download, sell, review, reserve, topic = data[0]
|
|
except Exception:
|
|
title, score, tags, order, watch, download, sell, review, reserve, topic = None
|
|
log.error(f"get last info data failed", exc_info=True)
|
|
return (title, score, tags, order, watch, download, sell, review, reserve, topic)
|
|
|
|
def run(self):
|
|
table_name = "tap_weekly"
|
|
all = dict()
|
|
data = self.build()
|
|
for gameid in data.keys():
|
|
for catename in data[gameid]:
|
|
all['gameid'] = gameid
|
|
all['catename'] = catename
|
|
all['min_order'] = data[gameid][catename].get('min_order', 0)
|
|
all['max_order'] = data[gameid][catename].get('max_order', 0)
|
|
all['diff_order'] = int(all['max_order']) - int(all['min_order'])
|
|
all['title'] = data[gameid][catename].get('title', "")
|
|
all['score'] = data[gameid][catename].get('score', 0)
|
|
all['tags'] = data[gameid][catename].get('tags', "")
|
|
all['order'] = data[gameid][catename].get('order', 0)
|
|
all['watch'] = data[gameid][catename].get('watch', 0)
|
|
all['download'] = data[gameid][catename].get('download', 0)
|
|
all['sell'] = data[gameid][catename].get('sell', 0)
|
|
all['review'] = data[gameid][catename].get('review', 0)
|
|
all['reserve'] = data[gameid][catename].get('reserve', 0)
|
|
all['topic'] = data[gameid][catename].get('topic', 0)
|
|
all['date'] = self.day
|
|
if abs(all['order'] - all['min_order']) <= abs(all['order'] - all['max_order']):
|
|
all["order_posi"] = 1
|
|
else:
|
|
all["order_posi"] = 0
|
|
|
|
self.db_conn.insert(table_name, all)
|
|
print(all)
|
|
|
|
|
|
def main():
|
|
if len(sys.argv) == 2:
|
|
day = sys.argv[1]
|
|
else:
|
|
day = (datetime.date.today() - datetime.timedelta(days=2)).strftime('%Y-%m-%d')
|
|
twr = TapWeeklyReport(day)
|
|
twr.run()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|