# -*- 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()