/app/tasks/bi_user_statistic/user_game_records.py
Python | 907 lines | 893 code | 9 blank | 5 comment | 1 complexity | 2ba332f4297fc1c20ba4c4dde4206be5 MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- from itertools import groupby
- from arrow import Arrow
- from operator import itemgetter
- from sqlalchemy import text, and_
- from sqlalchemy.sql.expression import bindparam
- from app.extensions import db
- from app.models.bi import BIUserStatisticSharding
- from app.tasks import with_db_context
- from app.utils import generate_sql_date, get_timezone_offset_of_from_Beijing_to_EST
- timezone_offset_of_from_Beijing_to_EST = get_timezone_offset_of_from_Beijing_to_EST()
- def process_bi_user_statistic_game_records(target):
- today, someday, index_time, timezone_offset = generate_sql_date(target)
- someday_arrow = Arrow.strptime(someday, '%Y-%m-%d')
- start_time = someday_arrow.replace(hours=timezone_offset_of_from_Beijing_to_EST).format('YYYY-MM-DD HH:mm:ss')
- end_time = someday_arrow.replace(days=1, hours=timezone_offset_of_from_Beijing_to_EST).format('YYYY-MM-DD HH:mm:ss')
- # ring_game 的抽水, 分两部分
- # 对于beginner层次的,从tj_cgz_flow_userpaninfo的scharges获取,根据每局的大盲值 ,抽取服务费
- # 对于其他层次的,从tj_super_game_scharges_record获取,根据底池大小,抽取服务费
- # 其他:优秀牌局这个概念适用于ring_game
- #
- def collection_user_ring_game_rake(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT stats_date username,
- Sum(rake) AS rake
- FROM (SELECT TO_CHAR(CAST(CAST(time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd') AS
- stats_date,
- rolename
- AS username,
- Sum(pay_num)
- AS rake
- FROM ogdzweb.tj_super_game_scharges_record
- GROUP BY TO_CHAR(CAST(CAST(time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd') ,
- rolename
- UNION
- SELECT TO_CHAR(CAST(CAST(cgz.time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- cgz.username AS username,
- Sum(cgz.scharges) AS rake
- FROM ogdzweb.tj_cgz_flow_userpaninfo cgz
- GROUP BY cgz.username,
- TO_CHAR(CAST(CAST(cgz.time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
-
- ) a
- GROUP BY stats_date,
- a.username
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT a.username AS username,
- SUM(a.rake) AS rake
- FROM (SELECT rolename AS username,
- SUM(pay_num) AS rake
- FROM ogdzweb.tj_super_game_scharges_record
- WHERE time_update <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND time_update >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY rolename
- UNION
- SELECT cgz.username AS username,
- SUM(cgz.scharges) AS rake
- FROM ogdzweb.tj_cgz_flow_userpaninfo cgz
- WHERE time_update <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND time_update >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY cgz.username) a
- GROUP BY a.username
- """), end_time=end_time, start_time=start_time)
- ring_game_rake_records = with_db_context(db, collection_user_ring_game_rake, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_username': row['username'], 'rake': row['rake']} for row in
- ring_game_rake_records]
- else:
- rows = [{'_stats_date': someday, '_username': row['username'], 'rake': row['rake']} for
- row in ring_game_rake_records]
- if rows:
- def sync_collection_user_ring_game_rake(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'ring_rake': bindparam('rake')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print(
- 'process bi_user_statistic_{} about ring game rake records related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about ring game rake related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_ring_game_rake, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_user_ring_game_hands(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT TO_CHAR(CAST(CAST(cgz.time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- cgz.username AS username,
- Count(DISTINCT cgz.pan_id) AS hands
- FROM ogdzweb.tj_cgz_flow_userpaninfo cgz
- GROUP BY TO_CHAR(CAST(CAST(cgz.time_update AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd'),
- cgz.username;
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT cgz.username AS username,
- COUNT(DISTINCT cgz.pan_id) AS hands
- FROM ogdzweb.tj_cgz_flow_userpaninfo cgz
- WHERE time_update <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND time_update >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY cgz.username
- """), end_time=end_time, start_time=start_time)
- ring_game_hands_records = with_db_context(db, collection_user_ring_game_hands, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_username': row['username'], 'hands': row['hands']} for row in
- ring_game_hands_records]
- else:
- rows = [{'_stats_date': someday, '_username': row['username'], 'hands': row['hands']} for
- row in ring_game_hands_records]
- if rows:
- def sync_collection_user_ring_game_hands(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'ring_hands': bindparam('hands')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print(
- 'process bi_user_statistic_{} about ring game hands records related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about ring game hands related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_ring_game_hands, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_sng_and_mtt_buyins_and_rake(connection, transaction, game_type_id):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT usersign.username
- username,
- TO_CHAR(CAST(CAST(usersign.time AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- Sum(CASE
- WHEN usersign.type = 2 THEN usersign.sign_totals
- WHEN usersign.type = 1 THEN usersign.sign_totals * -1
- ELSE 0
- END)
- AS buy_ins,
- Sum(CASE
- WHEN usersign.type = 2 THEN usersign.tax_totals
- WHEN usersign.type = 1 THEN usersign.tax_totals * -1
- ELSE 0
- END)
- AS rake
- FROM ogdzweb.tj_flow_usersign usersign
- INNER JOIN ogdzweb.tj_matchinfo matchinfo
- ON usersign.matchid = matchinfo.matchid
- WHERE matchinfo.type = 1
- GROUP BY usersign.username,
- TO_CHAR(CAST(CAST(usersign.time AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- """), timezone_offset=timezone_offset, game_type_id=game_type_id)
- else:
- return connection.execute(text("""
- SELECT usersign.username username,
- SUM(CASE
- WHEN usersign.type = 2 THEN usersign.sign_totals
- WHEN usersign.type = 1 THEN usersign.sign_totals * -1
- ELSE 0
- END) AS buy_ins,
- SUM(CASE
- WHEN usersign.type = 2 THEN usersign.tax_totals
- WHEN usersign.type = 1 THEN usersign.tax_totals * -1
- ELSE 0
- END) AS rake
- FROM ogdzweb.tj_flow_usersign usersign
- INNER JOIN ogdzweb.tj_matchinfo matchinfo
- ON usersign.matchid = matchinfo.matchid
- WHERE matchinfo.type = 1
- AND usersign.time <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND usersign.time >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY usersign.username
- """), end_time=end_time, start_time=start_time, game_type_id=game_type_id)
- def collection_winnings_records(connection, transaction, game_type_id):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT userreward.username AS username,
- TO_CHAR(CAST(CAST(userreward.time AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- Sum(CASE
- WHEN userreward.rewardtype = 3 THEN userreward.totals
- ELSE 0
- END) AS winnings
- FROM ogdzweb.tj_flow_userreward userreward
- INNER JOIN ogdzweb.tj_matchinfo matchinfo
- ON matchinfo.matchid = userreward.matchid
- WHERE matchinfo.type = :game_type_id
- GROUP BY userreward.username,
- TO_CHAR(CAST(CAST(userreward.time AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- """), timezone_offset=timezone_offset, game_type_id=game_type_id)
- else:
- return connection.execute(text(""" SELECT userreward.username AS username,
- SUM(CASE
- WHEN userreward.rewardtype = 3 THEN userreward.totals
- ELSE 0
- END) AS winnings
- FROM ogdzweb.tj_flow_userreward userreward
- INNER JOIN ogdzweb.tj_matchinfo matchinfo
- ON matchinfo.matchid = userreward.matchid
- WHERE matchinfo.type = :game_type_id
- AND userreward.time <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND userreward.time >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY userreward.username
- """), end_time=end_time, start_time=start_time, game_type_id=game_type_id)
- if target == 'lifetime':
- result_proxy_for_buy_ins_and_rake = []
- result_proxy_for_winnings = []
- for game_type_id, game_type in [(1, 'sng'), (2, 'mtt')]:
- buy_ins_and_rake_records = with_db_context(db, collection_sng_and_mtt_buyins_and_rake,
- game_type_id=game_type_id, bind='orig_wpt_ods')
- buy_ins_and_rake_records_rows = [
- {'_stats_date': row['stats_date'], '_username': row['username'], 'rake': row['rake'],
- 'buy_ins': row['buy_ins']} for row in buy_ins_and_rake_records]
- buy_ins_and_rake_records_rows_dict = dict([(game_type, buy_ins_and_rake_records_rows)])
- result_proxy_for_buy_ins_and_rake.append(buy_ins_and_rake_records_rows_dict)
- winnings_records = with_db_context(db, collection_winnings_records, game_type_id=game_type_id,
- bind='orig_wpt_ods')
- winnings_records_rows = [
- {'_stats_date': row['stats_date'], '_username': row['username'], 'winnings': row['winnings']} for row in
- winnings_records]
- winnings_records_rows_dict = dict([(game_type, winnings_records_rows)])
- result_proxy_for_winnings.append(winnings_records_rows_dict)
- else:
- result_proxy_for_buy_ins_and_rake = []
- result_proxy_for_winnings = []
- for game_type_id, game_type in [(1, 'sng'), (2, 'mtt')]:
- buy_ins_and_rake_records = with_db_context(db, collection_sng_and_mtt_buyins_and_rake,
- game_type_id=game_type_id, bind='orig_wpt_ods')
- buy_ins_and_rake_records_rows = [
- {'_stats_date': someday, '_username': row['username'], 'rake': row['rake'], 'buy_ins': row['buy_ins']}
- for row in buy_ins_and_rake_records]
- buy_ins_and_rake_records_rows_dict = dict([(game_type, buy_ins_and_rake_records_rows)])
- result_proxy_for_buy_ins_and_rake.append(buy_ins_and_rake_records_rows_dict)
- winnings_records = with_db_context(db, collection_winnings_records, game_type_id=game_type_id,
- bind='orig_wpt_ods')
- winnings_records_rows = [{'_stats_date': someday, '_username': row['username'], 'winnings': row['winnings']}
- for row in winnings_records]
- winnings_records_rows_dict = dict([(game_type, winnings_records_rows)])
- result_proxy_for_winnings.append(winnings_records_rows_dict)
- for game_buy_ins_and_rake_records_rows_dict in result_proxy_for_buy_ins_and_rake:
- for game_type, rows in game_buy_ins_and_rake_records_rows_dict.items():
- if rows:
- def sync_collection_game_buy_ins_and_rake_records(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(stats_date)
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'{}_gold_buyins'.format(game_type): bindparam('buy_ins'),
- '{}_rake'.format(game_type): bindparam('rake')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print(
- 'process bi_user_statistic_{} about {} buy_ins and rake related transaction.rollback()'.format(
- str(stats_date), game_type))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print(
- 'process bi_user_statistic_{} about {} buy_ins and rake related transaction.commit()'.format(
- str(stats_date), game_type))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_game_buy_ins_and_rake_records, stats_date=str(stats_date),
- group_rows=group_rows, bind='bi_user_statistic')
- for game_winnings_records_rows_dict in result_proxy_for_winnings:
- for game_type, rows in game_winnings_records_rows_dict.items():
- if rows:
- def sync_collection_game_winnings_records(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(stats_date)
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'{}_gold_winnings'.format(game_type): bindparam('winnings')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about {} winnings related transaction.rollback()'.format(
- str(stats_date), game_type))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about {} winnings related transaction.commit()'.format(
- str(stats_date), game_type))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_game_winnings_records, stats_date=str(stats_date),
- group_rows=group_rows, bind='bi_user_statistic')
- def collection_user_sng_entries(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT TO_CHAR(CAST(CAST(m.time_begin AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- u.username
- username,
- Sum(CASE
- WHEN u.type = 1 THEN 1
- WHEN u.type = 2 THEN -1
- ELSE 0
- END)
- AS entries
- FROM ogdzweb.tj_flow_usersign u
- LEFT JOIN ogdzweb.tj_matchinfo m
- ON u.matchid = m.matchid
- WHERE m.type = 2
- GROUP BY TO_CHAR(CAST(CAST(m.time_begin AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd') ,
- username;
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT SUM(CASE
- WHEN u.type = 1 THEN 1
- WHEN u.type = 2 THEN -1
- ELSE 0
- END) AS entries,
- u.username username
- FROM OGDZWEB.tj_flow_usersign u
- LEFT JOIN OGDZWEB.tj_matchinfo m ON u.matchid = m.matchid
- WHERE m.type = 1
- AND m.time_begin <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND m.time_begin >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY username
- """), end_time=end_time, start_time=start_time)
- sng_entries_records = with_db_context(db, collection_user_sng_entries, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_username': row['username'], 'entries': row['entries']} for row in
- sng_entries_records if row['stats_date'] is not None]
- else:
- rows = [{'_stats_date': someday, '_username': row['username'], 'entries': row['entries']} for row in
- sng_entries_records]
- if rows:
- def sync_collection_user_sng_entries(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'sng_gold_entries': bindparam('entries')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about sng entries related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about sng entries related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_sng_entries, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_user_mtt_entries(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT TO_CHAR(CAST(CAST(m.time_begin AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- u.username username,
- SUM(CASE
- WHEN u.type = 1 THEN 1
- WHEN u.type = 2 THEN -1
- ELSE 0
- END) AS entries
- FROM OGDZWEB.tj_flow_usersign u
- LEFT JOIN OGDZWEB.tj_matchinfo m
- ON u.matchid = m.matchid
- WHERE m.type = 2
- GROUP BY TO_CHAR(CAST(CAST(m.time_begin AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd'),
- username;
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT SUM(CASE
- WHEN u.type = 1 THEN 1
- WHEN u.type = 2 THEN -1
- ELSE 0
- END) AS entries,
- u.username username
- FROM OGDZWEB.tj_flow_usersign u
- LEFT JOIN OGDZWEB.tj_matchinfo m ON u.matchid = m.matchid
- WHERE m.type = 2
- AND m.time_begin <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND m.time_begin >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY username
- """), end_time=end_time, start_time=start_time)
- mtt_entries_records = with_db_context(db, collection_user_mtt_entries, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_username': row['username'], 'entries': row['entries']} for row in
- mtt_entries_records if row['stats_date'] is not None]
- else:
- rows = [{'_stats_date': someday, '_username': row['username'], 'entries': row['entries']} for row in
- mtt_entries_records]
- if rows:
- def sync_collection_user_mtt_entries(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.username == bindparam('_username'))
- values = {'mtt_gold_entries': bindparam('entries')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about mtt entries related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about mtt entries related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_mtt_entries, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_user_slots_spins_records(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT username
- AS
- og_account,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- AS stats_date,
- Count(*)
- AS slots_spins
- FROM gl.gamecoin_detail
- WHERE DATE (CONVERT_TZ(recdate, '+08:00', :timezone_offset)) >= '2016-06-01'
- GROUP BY username,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- )
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT username AS og_account,
- COUNT(*) AS slots_spins
- FROM gl.gamecoin_detail
- WHERE recdate <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND recdate >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY username
- """), end_time=end_time, start_time=start_time)
- slots_related_records = with_db_context(db, collection_user_slots_spins_records, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_og_account': row['og_account'], 'slots_spins': row['slots_spins']}
- for row in slots_related_records if row['stats_date'] is not None]
- else:
- rows = [{'_stats_date': someday, '_og_account': row['og_account'], 'slots_spins': row['slots_spins']} for row in
- slots_related_records]
- if rows:
- def sync_collection_user_slots_spins(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.og_account == bindparam('_og_account'))
- values = {'slots_spins': bindparam('slots_spins')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about slots spins records transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about slots spins records transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_slots_spins, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_user_slots_winnings(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT username AS og_account,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd') AS stats_date,
- SUM(gamecoin) AS slots_winnings
- FROM gl.gamecoin_detail
- WHERE gamecoin > 0
- GROUP BY username,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT username AS og_account,
- SUM(gamecoin) AS slots_winnings
- FROM gl.gamecoin_detail
- WHERE gamecoin > 0
- AND recdate <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND recdate >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY username
- """), end_time=end_time, start_time=start_time)
- slots_related_records = with_db_context(db, collection_user_slots_winnings, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [{'_stats_date': row['stats_date'], '_og_account': row['og_account'],
- 'slots_winnings': row['slots_winnings']} for row in slots_related_records if
- row['stats_date'] is not None]
- else:
- rows = [{'_stats_date': someday, '_og_account': row['og_account'], 'slots_winnings': row['slots_winnings']} for
- row in slots_related_records]
- if rows:
- def sync_collection_user_slots_related(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.og_account == bindparam('_og_account'))
- values = {'slots_winnings': bindparam('slots_winnings')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about slots winnings related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about slots winnings related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_slots_related, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- def collection_user_slots_wagered(connection, transaction):
- if target == 'lifetime':
- return connection.execute(text("""
- SELECT username AS og_account,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd') AS stats_date,
- SUM(gamecoin) AS slots_wagered
- FROM gl.gamecoin_detail
- WHERE gamecoin < 0
- GROUP BY username,
- TO_CHAR(CAST(CAST(recdate AS TIMESTAMP) AT TIME ZONE '-8:00' AS TIMESTAMP) AT TIME ZONE 'America/New_York', 'yyyy-mm-dd')
- """), timezone_offset=timezone_offset)
- else:
- return connection.execute(text("""
- SELECT username AS og_account,
- SUM(gamecoin) AS slots_wagered
- FROM gl.gamecoin_detail
- WHERE gamecoin < 0
- AND recdate <= TO_DATE(:end_time, 'yyyy-mm-dd,hh24:mi:ss')
- AND recdate >= TO_DATE(:start_time, 'yyyy-mm-dd,hh24:mi:ss')
- GROUP BY username
- """), end_time=end_time, start_time=start_time)
- slots_related_records = with_db_context(db, collection_user_slots_wagered, bind='orig_wpt_ods')
- if target == 'lifetime':
- rows = [
- {'_stats_date': row['stats_date'], '_og_account': row['og_account'], 'slots_wagered': row['slots_wagered']}
- for row in slots_related_records if row['stats_date'] is not None]
- else:
- rows = [{'_stats_date': someday, '_og_account': row['og_account'], 'slots_wagered': row['slots_wagered']} for
- row in slots_related_records]
- if rows:
- def sync_collection_user_slots_related(connection, transaction, stats_date, group_rows):
- someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- someday_bi_user_statistic.__table__.c.og_account == bindparam('_og_account'))
- values = {'slots_wagered': bindparam('slots_wagered')}
- try:
- connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values),
- list(group_rows))
- except:
- print('process bi_user_statistic_{} about slots wagered related transaction.rollback()'.format(
- str(stats_date)))
- transaction.rollback()
- raise
- else:
- transaction.commit()
- print('process bi_user_statistic_{} about slots wagered related transaction.commit()'.format(
- str(stats_date)))
- rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- for stats_date, group_rows in rows_group_by_states:
- with_db_context(db, sync_collection_user_slots_related, stats_date=str(stats_date), group_rows=group_rows,
- bind='bi_user_statistic')
- #
- #
- # def collection_user_mtt_rebuy_value_records(connection, transaction):
- #
- # if target == 'lifetime':
- #
- # return connection.execute(text("""
- # SELECT DISTINCT r.username AS
- # og_account,
- # DATE(CONVERT_TZ(r.endtime, '+08:00', :timezone_offset)) AS
- # stats_date,
- # SUM(r.rebuycount * s.sign_totals) AS
- # mtt_gold_rebuy_value
- # FROM usermatchrecord r
- # INNER JOIN tj_matchinfo m
- # ON r.matchid = m.matchid
- # INNER JOIN tj_flow_usersign s
- # ON r.matchid = s.matchid
- # WHERE m.type = 2
- # AND r.rank IS NOT NULL
- # AND r.rank > 0
- # GROUP BY og_account,
- # stats_date
- # """), timezone_offset=timezone_offset)
- #
- # else:
- #
- # return connection.execute(text("""
- # SELECT DISTINCT r.username AS
- # og_account,
- # SUM(r.rebuycount * s.sign_totals) AS
- # mtt_gold_rebuy_value
- # FROM usermatchrecord r
- # INNER JOIN tj_matchinfo m
- # ON r.matchid = m.matchid
- # INNER JOIN tj_flow_usersign s
- # ON r.matchid = s.matchid
- # WHERE m.type = 2
- # AND r.rank IS NOT NULL
- # AND r.rank > 0
- # AND DATE(CONVERT_TZ(r.endtime, '+08:00', :timezone_offset)) = :stats_date
- # GROUP BY og_account
- # """), timezone_offset=timezone_offset, stats_date=someday)
- #
- # result_proxy = with_db_context(db, collection_user_mtt_rebuy_value_records, bind='orig_wpt_ods')
- #
- # if target == 'lifetime':
- #
- # rows = [{'_stats_date': row['stats_date'], '_og_account': row['og_account'], 'mtt_gold_rebuy_value': row['mtt_gold_rebuy_value']} for row in result_proxy if row['stats_date'] is not None]
- #
- # else:
- #
- # rows = [{'_stats_date': someday, '_og_account': row['og_account'], 'mtt_gold_rebuy_value': row['mtt_gold_rebuy_value']} for row in result_proxy ]
- #
- # if rows:
- #
- # def sync_collection_user_mtt_rebuy_values_reocrds(connection, transaction, stats_date, group_rows):
- #
- # someday_bi_user_statistic = BIUserStatisticSharding.model(str(stats_date))
- #
- # where = and_(someday_bi_user_statistic.__table__.c.stats_date == bindparam('_stats_date'),
- # someday_bi_user_statistic.__table__.c.og_account == bindparam('_og_account'))
- #
- # values = [{'mtt_gold_rebuy_value': bindparam('mtt_gold_rebuy_value')}]
- #
- # try:
- # connection.execute(someday_bi_user_statistic.__table__.update().where(where).values(values), list(group_rows))
- # except:
- # print('process bi_user_statistic_{} about mtt rebuy value transaction.rollback'.format(str(stats_date)))
- # transaction.rollback()
- # raise
- # else:
- # transaction.commit()
- # print('process bi_user_statistic_{} about mtt rebuy value transaction.commit()'.format(str(stats_date)))
- #
- # rows_group_by_states = groupby(sorted(rows, key=itemgetter('_stats_date')), itemgetter('_stats_date'))
- # for stats_date, group_rows in rows_group_by_states:
- #
- # with_db_context(db, sync_collection_user_mtt_rebuy_values_reocrds, stats_date = str(stats_date), group_rows=group_rows)
- #
- #
- # def collection_user_mtt_rebuy_count_records(connection, transaction):
- #
- # if target == 'lifetime':
- #
- # return connection.execute(text…
Large files files are truncated, but you can click here to view the full file