scripts/vas/official_tp_streamers.py

from lib.all import *

csv_w = Csv(
    "income_11月_官方.csv", ["用户id", "昵称", "空间解锁", "主播收入钻石"]
)


class S:
    def __init__(self):
        self.mysql_db_vas = Mysql(
            "rm-bp11t1616a1kjvmx5.mysql.rds.aliyuncs.com", 3306, "vas", "root", "Wishpal2024"
        )
        self.col_ztp = MongoDB(
            host="mongodb://root:Wishpal2024@dds-bp1da1ddd62bede41.mongodb.rds.aliyuncs.com:3717,dds-bp1da1ddd62bede42.mongodb.rds.aliyuncs.com:3717/admin?replicaSet=mgset-77304659",
            port=3717,
            db="zone_third_partner",
            collection="zone_third_partner"
        )
        self.col_account = MongoDB(
            host="mongodb://root:Wishpal2024@dds-bp1da1ddd62bede41.mongodb.rds.aliyuncs.com:3717,dds-bp1da1ddd62bede42.mongodb.rds.aliyuncs.com:3717/admin?replicaSet=mgset-77304659",
            port=3717,
            db="account",
            collection="account"
        )
        self.col_zone = MongoDB(
            host="mongodb://root:Wishpal2024@dds-bp1da1ddd62bede41.mongodb.rds.aliyuncs.com:3717,dds-bp1da1ddd62bede42.mongodb.rds.aliyuncs.com:3717/admin?replicaSet=mgset-77304659",
            port=3717,
            db="zone",
            collection="zone"
        )

    def __del__(self):
        self.mysql_db_vas.close()
        self.col_ztp.close()
        self.col_account.close()
        self.col_zone.close()

    def uid2mid(self, uid):
        q = {
            "user_id": uid
        }
        doc = self.col_account.find_one(q)
        return safe_get_int(doc, "_id")

    def zid2mid(self, zid):
        q = {
            "_id": zid
        }
        doc = self.col_zone.find_one(q)
        return safe_get_int(doc, "mid")

    def mid2uid_name(self, mid):
        q = {
            "_id": mid
        }
        doc = self.col_account.find_one(q)
        return safe_get_int(doc, "user_id"), safe_get_str(doc, "name")

    def get_zone_admission_cnt(self, zids, st, et):
        sql = '''
select zid, count(1) as admission_cnt from vas_zone_member where zid in (%s) and member_type=1 and ct>=%d and ct<%d group by zid
        ''' % (get_list_str(zids), st, et)
        print(sql)
        rows = self.mysql_db_vas.query(sql)
        zid_cnt_map = dict()
        for row in rows:
            zid = safe_get_int(row, "zid")
            ac = safe_get_int(row, "admission_cnt")
            zid_cnt_map[zid] = ac
        return zid_cnt_map

    # 获取主播净收入
    def get_streamer_income(self, mids, st, et):
        sql = '''
select mid, sum(`change`) as income from vas_ch_income where mid in (%s) and ct>=%d and ct<%d group by mid
        ''' % (get_list_str(mids), st, et)
        print(sql)
        rows = self.mysql_db_vas.query(sql)
        mid_income_map = dict()
        for row in rows:
            mid = safe_get_int(row, "mid")
            income = safe_get_int(row, "income")
            mid_income_map[mid] = income
        return mid_income_map

    def proc(self):
        q = {
            "third_partner_mid": 159
        }
        docs = self.col_ztp.find(q)

        zids = list()
        for d in docs:
            zid = safe_get_int(d, "zid")
            zids.append(zid)

        # 空间解锁数
        zid_cnt_map = self.get_zone_admission_cnt(
            zids,
            get_ts_by_str("2024-11-01 00:00:00"),
            get_ts_by_str("2024-12-05 00:00:00")
        )
        print(zid_cnt_map)

        mids = list()
        for zid in zids:
            mid = self.zid2mid(zid)
            mids.append(mid)

        # 收入
        mid_income_map = self.get_streamer_income(
            mids,
            get_ts_by_str("2024-11-01 00:00:00"),
            get_ts_by_str("2024-12-05 00:00:00")
        )
        print(mid_income_map)

        for zid in zids:
            mid = self.zid2mid(zid)
            uid, name = self.mid2uid_name(mid)
            ac = safe_get_int(zid_cnt_map, zid)
            income = safe_get_int(mid_income_map, mid)

            doc = [uid, name, ac, income]
            print(doc)
            csv_w.append([doc])


s = S()
s.proc()