Beispiel #1
0
def save_database_csv(type: str, output_filepath: str) -> True:
    tables = ['{}_game_analysis'.format(type)]
    if type == 'current':
        tables = ['current_game_watch'] + tables
    if type == 'past':
        tables = ['past_game_stats'] + tables

    joined_tables = [
        '{}_game_player_stats'.format(type), '{}_game_team_stats'.format(type)
    ]
    if type == 'current':
        joined_tables = [
            'current_game_stats', 'current_game_unchanged',
            'current_game_team_unchanged', 'current_game_player_unchanged'
        ] + joined_tables

    # Save database data in CSV files
    sql = LibPool().libsql
    sql.start_trans_mode()
    cur = sql.cursor()
    tmp_files = {}
    queries = []

    for table in tables:
        csv_tmp_file_path = os.path.normpath(
            os.path.join(app_config['path_storage'], 'tmp', str(uuid.uuid4())))
        tmp_files['{}.csv'.format(table)] = csv_tmp_file_path
        queries.append(
            '''COPY (SELECT * FROM "{}") TO '{}' WITH CSV HEADER;'''.format(
                table, csv_tmp_file_path))

    i = 0
    for table in joined_tables:
        i += 1
        csv_tmp_file_path = os.path.normpath(
            os.path.join(app_config['path_storage'], 'tmp', str(uuid.uuid4())))
        tmp_files['{}.csv'.format(table)] = csv_tmp_file_path
        if i == 1 and type == 'current':
            queries.append('''
                COPY (
                    SELECT "S".*
                    FROM "current_game_watch" "W"
                    INNER JOIN "{}" "S"
                        ON "S"."watch_game_id" = "W"."id"
                ) TO '{}' WITH CSV HEADER;'''.format(table, csv_tmp_file_path))
        elif type == 'current':
            queries.append('''
                COPY (
                    SELECT "TABLE".*
                    FROM "current_game_watch" "W"
                    INNER JOIN "current_game_stats" "S"
                        ON "S"."watch_game_id" = "W"."id"
                    INNER JOIN "{}" "TABLE"
                        ON "TABLE"."stats_game_id" = "S"."id"
                ) TO '{}' WITH CSV HEADER;'''.format(table, csv_tmp_file_path))
        elif type == 'past':
            queries.append('''
                COPY (
                    SELECT "TABLE".*
                    FROM "past_game_stats" "S"
                    INNER JOIN "{}" "TABLE"
                        ON "TABLE"."stats_game_id" = "S"."id"
                ) TO '{}' WITH CSV HEADER;'''.format(table, csv_tmp_file_path))

    for query in queries:
        cur.q(query)
    sql.finish_trans_mode()

    # ZIP files into final package
    with ZipFile(output_filepath, 'w') as myzip:
        for tmp_name, tmp_file in tmp_files.items():
            myzip.write(tmp_file, tmp_name)
            os.unlink(tmp_file)

    return True
    def process_data(self,
                     type: str,
                     data_src: str,
                     game_name: StrNone = None,
                     data_src_tournament_id: IntNone = None) -> OrderedDict:
        if type == 'past':
            # There is online analysis when grabbing data , so no need for this
            raise NotImplementedError

        # Current data
        sql = LibPool().libsql
        sql.start_trans_mode()
        cur = sql.cursor()

        # Games
        # games_watch_count
        query_addon = ''
        if game_name is not None:
            query_addon = """\nAND "W"."game_name" = %(game_name)s"""
        if data_src_tournament_id is not None:
            query_addon += """\nAND "W"."data_src_tournament_id" = %(data_src_tournament_id)s"""
        games_watch_data = cur.qfo(
            '''
            SELECT COUNT("W"."id") AS "count"
            FROM "current_game_watch" "W"
            WHERE "W"."data_src" = %(data_src)s
                AND "W"."is_deleted" = false {}
        '''.format(query_addon), {
                'data_src': data_src,
                'game_name': game_name,
                'data_src_tournament_id': data_src_tournament_id
            })
        games_watch_count = games_watch_data['count']
        if games_watch_count == 0:
            return 'No games to watch'

        # games_watch_with_stats_count
        games_watch_with_stats_data = cur.qfa(
            '''
            SELECT "W"."id"
            FROM "current_game_watch" "W"
            INNER JOIN "current_game_stats" "S"
                ON "S"."watch_game_id" = "W"."id"
            WHERE "W"."data_src" = %(data_src)s
                AND "W"."is_deleted" = false {}
            GROUP BY "W"."id"
        '''.format(query_addon), {
                'data_src': data_src,
                'game_name': game_name,
                'data_src_tournament_id': data_src_tournament_id
            })
        games_watch_with_stats_count = len(games_watch_with_stats_data)
        if games_watch_with_stats_count == 0:
            return 'No games to watch with stats to analyze'

        # games_correction_percent
        games_watch_with_stats_percent = 0
        if games_watch_with_stats_count > 0:
            games_watch_with_stats_percent = round(
                games_watch_with_stats_count * 100 / games_watch_count, 2)

        # Get stats data for later analysis
        games_stats_data = cur.qfa(
            '''
            SELECT
                "W"."id" AS "watch_id",
                "W"."game_name",
                "W"."data_src_finish_datetime",
                COUNT("S"."id") AS "stats_count",
                ARRAY_AGG ("S"."id"::int8) "stats"
            FROM "current_game_watch" "W"
            INNER JOIN "current_game_stats" "S"
                ON "S"."watch_game_id" = "W"."id"
            WHERE "W"."data_src" = %(data_src)s
                AND "W"."is_deleted" = false {}
            GROUP BY "W"."id"
            ORDER BY "W"."id"
        '''.format(query_addon), {
                'data_src': data_src,
                'game_name': game_name,
                'data_src_tournament_id': data_src_tournament_id
            })
        games_stats_watch_ids = [x['watch_id'] for x in games_stats_data]
        games_stats_counts = [x['stats_count'] for x in games_stats_data]
        games_stats_corrected_ids = [
            x['stats'] for x in games_stats_data if x['stats_count'] > 1
        ]
        games_stats_corrected_first_last_ids = [(min(x['stats']),
                                                 max(x['stats']))
                                                for x in games_stats_data
                                                if x['stats_count'] > 1]

        # games_watch_with_stats_corrected_count
        games_watch_with_stats_corrected_count = len(games_stats_corrected_ids)

        # games_watch_with_stats_corrected_percent
        games_watch_with_stats_corrected_percent = 0
        if games_watch_with_stats_corrected_count > 0:
            games_watch_with_stats_corrected_percent = round(
                games_watch_with_stats_corrected_count * 100 /
                games_watch_with_stats_count, 2)

        # games_correction_count
        correction_count_list = [x for x in games_stats_counts if x > 1]
        games_stats_correction_count = sum(correction_count_list) - len(
            correction_count_list)  # Minus first game

        # games_stats_correction_per_game_average_count
        games_stats_correction_per_game_average_count = 0
        if games_watch_with_stats_corrected_count > 0:
            games_stats_correction_per_game_average_count = round(
                games_stats_correction_count /
                games_watch_with_stats_corrected_count, 2)

        # games_stats_game_end_save_stats_average_seconds_diff
        games_stats_save_times = []
        if games_stats_watch_ids:
            for watch_game_id in games_stats_watch_ids:
                data = cur.qfo('''
                    SELECT "W"."data_src_finish_datetime", "S"."insert_datetime"
                    FROM "current_game_watch" "W"
                    INNER JOIN "current_game_stats" "S"
                        ON "S"."watch_game_id" = "W"."id"
                    WHERE "W"."id" = %s
                    ORDER BY "W"."id", "S"."id"
                    LIMIT 1
                ''',
                               params=[watch_game_id])
                games_stats_save_times.append(
                    (data['insert_datetime'] -
                     data['data_src_finish_datetime']).total_seconds())
        games_stats_game_end_save_stats_average_seconds_diff = 0
        if games_stats_save_times:
            games_stats_game_end_save_stats_average_seconds_diff = round(
                sum(games_stats_save_times) / len(games_stats_save_times), 2)

        # games_stats_save_stats_last_correction_average_seconds_diff
        games_stats_correction_times = []
        if games_stats_corrected_first_last_ids:
            for first_game_id, last_game_id in games_stats_corrected_first_last_ids:
                first_last = cur.qfa('''
                    SELECT "id", "insert_datetime" FROM "current_game_stats"
                    WHERE "id" IN %s
                ''',
                                     params=[(first_game_id, last_game_id)],
                                     key='id')
                games_stats_correction_times.append(
                    (first_last[last_game_id]['insert_datetime'] -
                     first_last[first_game_id]['insert_datetime']
                     ).total_seconds())
        games_stats_save_stats_last_correction_average_seconds_diff = 0
        if games_stats_correction_times:
            games_stats_save_stats_last_correction_average_seconds_diff = round(
                sum(games_stats_correction_times) /
                len(games_stats_correction_times), 2)

        # Datapoints
        # Each team in all games has 5 players
        # datapoints_total_count
        if not game_name:
            datapoints_stats_count = (sum([
                5 * api_config[x['game_name']]['datapoints'] * x['stats_count']
                for x in games_stats_data
            ]))
        else:
            # Total coun of saved games is `games_stats_correction_count` + `games_stats_correction_count`
            datapoints_stats_count = (
                5 * api_config[game_name]['datapoints'] *
                (games_stats_correction_count + games_watch_with_stats_count))

        # datapoints_correction_count
        datapoints_stats_correction_list = []
        if games_stats_corrected_ids:
            for game_stats_ids in games_stats_corrected_ids:
                # Teams
                teams_data = cur.qfa(
                    '''
                    SELECT "TS".*, "S"."insert_datetime"
                    FROM "current_game_stats" "S"
                    INNER JOIN "current_game_team_stats" "TS"
                        ON "S"."id" = "TS"."stats_game_id"
                    WHERE "S"."id" IN %s
                    ORDER BY "TS"."data_src_team_id", "TS"."id"
                ''', [(game_stats_ids)])
                if teams_data:
                    teams_data_final = self._transform_stats_data(
                        teams_data, 'data_src_team_id')
                    team_changes_count_per_game = self._check_stats_for_changes(
                        teams_data_final)

                # Players
                players_data = cur.qfa(
                    '''
                    SELECT "PS".*
                    FROM "current_game_stats" "S"
                    INNER JOIN "current_game_player_stats" "PS"
                        ON "S"."id" = "PS"."stats_game_id"
                    WHERE "S"."id" IN %s
                    ORDER BY "PS"."id"
                ''', [(game_stats_ids)])
                if players_data:
                    players_data_final = self._transform_stats_data(
                        players_data, 'data_src_player_id')
                    player_changes_count_per_game = self._check_stats_for_changes(
                        players_data_final)

                for game_id in game_stats_ids:
                    # Changes has to be saved per stats game due max and median analyze needs
                    changes_count = 0
                    if game_id in team_changes_count_per_game:
                        changes_count += team_changes_count_per_game[game_id]
                    if game_id in player_changes_count_per_game:
                        changes_count += player_changes_count_per_game[game_id]
                    datapoints_stats_correction_list.append(changes_count)

        # assign datapoints_correction_count
        datapoints_stats_correction_count = len(
            datapoints_stats_correction_list)

        # datapoints_correction_percent
        datapoints_stats_correction_percent = 0
        if datapoints_stats_correction_count > 0:
            datapoints_stats_correction_percent = round(
                datapoints_stats_correction_count * 100 /
                datapoints_stats_count, 2)

        # datapoints_correction_max
        datapoints_stats_correction_per_game_max = 0
        if datapoints_stats_correction_list:
            datapoints_stats_correction_per_game_max = max(
                datapoints_stats_correction_list)

        # datapoints_correction_median
        datapoints_stats_correction_per_game_median = 0
        if datapoints_stats_correction_list:
            datapoints_stats_correction_per_game_median = statistics.median(
                datapoints_stats_correction_list)

        result = OrderedDict([
            ('games_watch_count', games_watch_count),
            ('games_watch_with_stats_count', games_watch_with_stats_count),
            ('games_watch_with_stats_percent', games_watch_with_stats_percent),
            ('games_watch_with_stats_corrected_count',
             games_watch_with_stats_corrected_count),
            ('games_watch_with_stats_corrected_percent',
             games_watch_with_stats_corrected_percent),
            ('games_stats_correction_count', games_stats_correction_count),
            ('games_stats_correction_per_game_average_count',
             games_stats_correction_per_game_average_count),
            ('games_stats_game_end_save_stats_average_minutes_diff',
             round(games_stats_game_end_save_stats_average_seconds_diff / 60,
                   2)),
            ('games_stats_save_stats_last_correction_average_minutes_diff',
             round(
                 games_stats_save_stats_last_correction_average_seconds_diff /
                 60, 2)), ('datapoints_stats_count', datapoints_stats_count),
            ('datapoints_stats_correction_count',
             datapoints_stats_correction_count),
            ('datapoints_stats_correction_percent',
             datapoints_stats_correction_percent),
            ('datapoints_stats_correction_per_game_max',
             datapoints_stats_correction_per_game_max),
            ('datapoints_stats_correction_per_game_median',
             datapoints_stats_correction_per_game_median)
        ])

        # Update analysis results in database
        update_data = dict(deepcopy(result))
        for var in [
            ('games_stats_game_end_save_stats_average_seconds_diff',
             games_stats_game_end_save_stats_average_seconds_diff),
            ('games_stats_save_stats_last_correction_average_seconds_diff',
             games_stats_save_stats_last_correction_average_seconds_diff)
        ]:
            del (update_data[var[0].replace('seconds', 'minutes')])
            update_data[var[0]] = var[1]
        cur.update('current_game_analysis',
                   update_data,
                   conditions={
                       'data_src': data_src,
                       'game_name': game_name
                   })

        sql.finish_trans_mode()

        # Return analysis informations
        return result