145 lines
4.4 KiB
Python
145 lines
4.4 KiB
Python
from lib.all import *
|
|
|
|
csv_w = Csv(
|
|
"income_08.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_tp_zids(self, tp_mids):
|
|
q = {
|
|
"third_partner_mid": {"$in": tp_mids}
|
|
}
|
|
docs = self.col_ztp.find(q)
|
|
zids = list()
|
|
for d in docs:
|
|
zid = safe_get_int(d, "zid")
|
|
zids.append(zid)
|
|
return zids
|
|
|
|
# 获取zid解锁空间
|
|
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):
|
|
tp_uids = [983565, 231638]
|
|
tp_mids = list()
|
|
for uid in tp_uids:
|
|
tp_mids.append(self.uid2mid(uid))
|
|
print(tp_mids)
|
|
|
|
# 获取代运营管理的空间
|
|
zids = self.get_tp_zids(tp_mids)
|
|
print(zids)
|
|
|
|
# 空间解锁数
|
|
zid_cnt_map = self.get_zone_admission_cnt(
|
|
zids,
|
|
get_ts_by_str("2024-08-01 00:00:00"),
|
|
get_ts_by_str("2024-09-01 00:00:00")
|
|
)
|
|
print(zid_cnt_map)
|
|
|
|
# 获取空间对应的mid
|
|
streamer_mids = list()
|
|
for zid in zids:
|
|
streamer_mids.append(self.zid2mid(zid))
|
|
print(streamer_mids)
|
|
|
|
# 收入
|
|
mid_income_map = self.get_streamer_income(
|
|
streamer_mids,
|
|
get_ts_by_str("2024-08-01 00:00:00"),
|
|
get_ts_by_str("2024-09-01 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()
|