183 lines
5.2 KiB
Python
183 lines
5.2 KiB
Python
from lib.all import *
|
|
|
|
service_name = 'vas_day_detail'
|
|
log_dir = '/app/log'
|
|
logger = Logger(service_name, log_dir=log_dir)
|
|
|
|
hds = [
|
|
'时间',
|
|
'会员数',
|
|
'会员流水',
|
|
'空间解锁数',
|
|
'空间解锁流水',
|
|
'空间超粉数',
|
|
'空间超粉流水',
|
|
'空间动态数',
|
|
'空间动态流水',
|
|
'其他数(微信+金币)',
|
|
'其他流水(微信+金币)',
|
|
'总流水',
|
|
'主播分成',
|
|
'净收入',
|
|
'利润率',
|
|
'会员流水占比',
|
|
'空间解锁占比',
|
|
'空间超粉占比',
|
|
'空间动态占比',
|
|
'其他占比',
|
|
]
|
|
|
|
|
|
class S:
|
|
def __init__(self, st, et):
|
|
self.st = st
|
|
self.et = et
|
|
self.mysql_db_vas = Mysql(
|
|
"rm-bp11t1616a1kjvmx5.mysql.rds.aliyuncs.com", 3306, "vas", "root", "Wishpal2024"
|
|
)
|
|
self.mysql_db_bi = Mysql(
|
|
"172.31.37.71", 3306, "bi", "root", "Wishpal@2023"
|
|
)
|
|
|
|
def __del__(self):
|
|
self.mysql_db_vas.close()
|
|
self.mysql_db_bi.close()
|
|
|
|
def get_product_sold_list(self):
|
|
sql = '''
|
|
select product_id,
|
|
count(1) cnt,
|
|
sum(pay_amount) money
|
|
from vas_order
|
|
where ct>={} and ct<{}
|
|
and order_status in (1,2)
|
|
group by product_id
|
|
'''.format(self.st, self.et)
|
|
docs = self.mysql_db_vas.query(sql)
|
|
return docs
|
|
|
|
def get_streamer_dias(self):
|
|
sql = '''
|
|
select sum(`change`) dias
|
|
from vas_ch_income
|
|
where ct>={} and ct<{}
|
|
and mid>0
|
|
'''.format(self.st, self.et)
|
|
docs = self.mysql_db_vas.query(sql)
|
|
if len(docs) > 0:
|
|
return safe_get_int(docs[0], "dias")
|
|
return 0
|
|
|
|
# 获取官方钻石
|
|
def get_official_ext_dias(self):
|
|
sql = '''
|
|
select sum(`change`) dias
|
|
from vas_ch_income
|
|
where ct>={} and ct<{}
|
|
and mid in (159)
|
|
'''.format(self.st, self.et)
|
|
docs = self.mysql_db_vas.query(sql)
|
|
if len(docs) > 0:
|
|
return safe_get_int(docs[0], "dias")
|
|
return 0
|
|
|
|
def insert_day_detail(self, data: tuple):
|
|
sql = '''insert into vas_day_detail (
|
|
ptime,
|
|
membership_cnt,
|
|
membership_flow,
|
|
zone_admission_cnt,
|
|
zone_admission_flow,
|
|
zone_superfan_cnt,
|
|
zone_superfan_flow,
|
|
zone_moment_cnt,
|
|
zone_moment_flow,
|
|
other_cnt,
|
|
other_flow,
|
|
total_flow,
|
|
streamer_flow,
|
|
official_flow,
|
|
profit_rate,
|
|
membership_rate,
|
|
zone_admission_rate,
|
|
zone_superfan_rate,
|
|
zone_moment_rate,
|
|
other_rate
|
|
) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
|
|
return self.mysql_db_bi.exec(sql, data)
|
|
|
|
def proc(self):
|
|
total_money = 0 # 总流水
|
|
membership_cnt = 0
|
|
membership_money = 0
|
|
zone_admission_cnt = 0
|
|
zone_admission_money = 0
|
|
zone_superfan_cnt = 0
|
|
zone_superfan_money = 0
|
|
zone_moment_cnt = 0
|
|
zone_moment_money = 0
|
|
other_cnt = 0
|
|
other_money = 0
|
|
official_ext_dias = self.get_official_ext_dias()
|
|
|
|
sold_list = self.get_product_sold_list()
|
|
for sold in sold_list:
|
|
product_id = safe_get_str(sold, "product_id")
|
|
cnt = safe_get_int(sold, "cnt")
|
|
money = int(safe_get_int(sold, "money") / 100)
|
|
total_money += money
|
|
if product_id == "membership":
|
|
membership_cnt += cnt
|
|
membership_money += money
|
|
elif product_id == "h5_zone_admission":
|
|
zone_admission_cnt += cnt
|
|
zone_admission_money += money
|
|
elif product_id == "h5_zone_superfanship":
|
|
zone_superfan_cnt += cnt
|
|
zone_superfan_money += money
|
|
elif product_id == "h5_zone_moment":
|
|
zone_moment_cnt += cnt
|
|
zone_moment_money += money
|
|
else:
|
|
other_cnt += cnt
|
|
other_money += money
|
|
streamer_dias = self.get_streamer_dias() - official_ext_dias
|
|
streamer_money = int(streamer_dias / 10)
|
|
official_money = total_money - streamer_money
|
|
data = (
|
|
datetime.datetime.fromtimestamp(self.st).strftime('%Y-%m-%d %H:%M:%S'),
|
|
membership_cnt, membership_money,
|
|
zone_admission_cnt, zone_admission_money,
|
|
zone_superfan_cnt, zone_superfan_money,
|
|
zone_moment_cnt, zone_moment_money,
|
|
other_cnt, other_money,
|
|
total_money, streamer_money, official_money,
|
|
safe_div(official_money, total_money),
|
|
safe_div(membership_money, total_money),
|
|
safe_div(zone_admission_money, total_money),
|
|
safe_div(zone_superfan_money, total_money),
|
|
safe_div(zone_moment_money, total_money),
|
|
safe_div(other_money, total_money)
|
|
)
|
|
self.insert_day_detail(data)
|
|
print(data)
|
|
|
|
|
|
# st_et_str_map = gen_st_et_str_map_v2(
|
|
# "2024-04-16 00:00:00", "2024-05-11 00:00:00"
|
|
# )
|
|
#
|
|
# for st_str, et_str in st_et_str_map.items():
|
|
# s = S(
|
|
# get_ts_by_str(st_str), get_ts_by_str(et_str)
|
|
# )
|
|
# s.proc()
|
|
|
|
st = get_today_zero_time() - 86400
|
|
et = st + 86400
|
|
|
|
s = S(
|
|
st, et
|
|
)
|
|
s.proc()
|