user_game_records.py

/app/tasks/bi_user_statistic/user_game_records.py

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