user_game_records.py

/app/tasks/bi_user_statistic/user_game_records.py

https://bitbucket.org/fanhaipeng/bi-backend
Python | 907 lines | 893 code | 9 blank | 5 comment | 1 complexity | 2ba332f4297fc1c20ba4c4dde4206be5 MD5 | raw 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("""
            #                                         SELECT DISTINCT r.username                                              AS
            #                                                         og_account,
            #                                                         DATE(CONVERT_TZ(r.endtime, '+08:00', :timezone_offset)) AS
            #                                                         stats_date,
            #                                                         SUM(rebuycount)                                         AS
            #                                                         mtt_gold_rebuy_count
            #                                         FROM   usermatchrecord r
            #                                                INNER JOIN tj_matchinfo m
            #                                                        ON r.matchid = m.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(rebuycount) AS mtt_gold_rebuy_count
            #                                         FROM   usermatchrecord r
            #                                                INNER JOIN tj_matchinfo m
            #                                                        ON r.matchid = m.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_count_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_count']} 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_count']} for row in result_proxy ]
            #
            # if rows:
            #
            #     def sync_collection_user_mtt_rebuy_count_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_count': bindparam('mtt_gold_rebuy_count')}]
            #
            #         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 count transaction.rollback'.format(str(stats_date)))
            #             transaction.rollback()
            #             raise
            #         else:
            #             transaction.commit()
            #             print('process bi_user_statistic_{} about mtt rebuy  count 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_count_reocrds, stats_date = str(stats_date), group_rows=group_rows)