<commit_before>from .models import *
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from heltour import settings
from datetime import datetime, timedelta
from django.utils import timezone
from django.db import transaction
import re
from gspread.exceptions import WorksheetNotFound

def _open_doc(url):
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(settings.GOOGLE_SERVICE_ACCOUNT_KEYFILE_PATH, scope)
    gc = gspread.authorize(credentials)
    try:
        return gc.open_by_url(url)
    except gspread.SpreadsheetNotFound:
        raise SpreadsheetNotFound

def import_team_season(league, url, name, tag, rosters_only=False, exclude_live_pairings=False):
    doc = _open_doc(url)

    with transaction.atomic():

        sheet_standings = _trim_cells(doc.worksheet('Standings').get_all_values())

        # Read the round count
        round_ = 1
        round_cols = []
        while True:
            try:
                round_cols.append(sheet_standings[0].index('Round %d' % round_))
            except ValueError:
                # No more rounds
                break
            round_ += 1
        round_count = round_ - 1

        try:
            sheet_rosters = _trim_cells(doc.worksheet('Rosters').get_all_values())

            # Read the board count
            board = 1
            while True:
                try:
                    sheet_rosters[0].index('Board %d' % board)
                except ValueError:
                    # No more boards
                    break
                board += 1
            board_count = board - 1

            # Create the season
            season = Season.objects.create(league=league, name=name, tag=tag, rounds=round_count, boards=board_count, is_active=True)

            # Read the teams
            team_name_col = sheet_rosters[0].index('Teams')
            team_name_row = 1
            teams = []
            while True:
                team_name = sheet_rosters[team_name_row][team_name_col]
                if len(team_name) == 0:
                    break
                team = Team.objects.create(season=season, number=len(teams) + 1, name=team_name)
                TeamScore.objects.create(team=team)
                teams.append(team)
                team_name_row += 1

            # Read the team members and alternates
            alternates_start_row = [row[0] == 'Alternates' for row in sheet_rosters].index(True)
            for i in range(season.boards):
                board = i + 1
                player_name_col = sheet_rosters[0].index('Board %d' % board)
                player_rating_col = sheet_rosters[0].index('Rating %d' % board)
                # Team members
                for i in range(len(teams)):
                    player_row = i + 1
                    player_name, is_captain = _parse_player_name(sheet_rosters[player_row][player_name_col])
                    player_rating = sheet_rosters[player_row][player_rating_col]
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    SeasonPlayer.objects.get_or_create(season=season, player=player)
                    TeamMember.objects.get_or_create(team=teams[i], board_number=board, defaults={'player': player, 'is_captain':is_captain})
                # Alternates
                alternates_row = alternates_start_row
                while True:
                    player_name = sheet_rosters[alternates_row][player_name_col]
                    player_rating = sheet_rosters[alternates_row][player_rating_col]
                    if len(player_name) == 0 or len(player_rating) == 0:
                        break
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    season_player, _ = SeasonPlayer.objects.get_or_create(season=season, player=player)
                    Alternate.objects.get_or_create(season_player=season_player, defaults={'board_number': board})
                    alternates_row += 1

        except WorksheetNotFound:
            # Try using the alternative rosters format
            sheet_rosters = _trim_cells(doc.worksheet('Team Rosters').get_all_values())

            # Read the board count
            board_count = 0
            for row in sheet_rosters:
                match = re.match('Board ([0-9]+)', row[0])
                if match is not None:
                    board_count = int(match.group(1))
                if row[0] == 'Avg Rating':
                    break

            # Create the season
            season = Season.objects.create(league=league, name=name, tag=tag, rounds=round_count, boards=board_count, is_active=True)

            # Read the teams
            teams = []
            for col in range(1, len(sheet_rosters[0])):
                team_name = sheet_rosters[0][col]
                if len(team_name) == 0:
                    break
                team = Team.objects.create(season=season, number=len(teams) + 1, name=team_name)
                TeamScore.objects.create(team=team)
                teams.append(team)

            print teams

            # Read the team members
            for i in range(season.boards):
                board = i + 1
                name_row = board * 3 - 2
                rating_row = board * 3 - 1
                # Team members
                for i in range(len(teams)):
                    player_col = i + 1
                    player_name, is_captain = _parse_player_name(sheet_rosters[name_row][player_col])
                    player_rating = sheet_rosters[rating_row][player_col]
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    SeasonPlayer.objects.get_or_create(season=season, player=player)
                    TeamMember.objects.get_or_create(team=teams[i], board_number=board, defaults={'player': player, 'is_captain':is_captain})

        if not rosters_only:

            try:
                # Try using the alternative pairings format
                sheet_fixed_pairings = _trim_cells(doc.worksheet('Fixed Pairings').get_all_values())

                rounds = Round.objects.filter(season=season).order_by('number')

                pairing_order = 0
                board_number = board_count

                for row in sheet_fixed_pairings:
                    try:
                        round_number = int(row[0])
                    except ValueError:
                        continue

                    white_team_name = row[1] if board_number == board_count or board_number % 2 == 0 else row[4]
                    print white_team_name
                    white_team = Team.objects.get(season=season, name__iexact=white_team_name)

                    black_team_name = row[4] if board_number == board_count or board_number % 2 == 0 else row[1]
                    print black_team_name
                    black_team = Team.objects.get(season=season, name__iexact=black_team_name)

                    team_pairing, created = TeamPairing.objects.get_or_create(round=rounds[round_number - 1], white_team=white_team, black_team=black_team, defaults={'pairing_order': pairing_order})
                    if created:
                        pairing_order += 1
                        board_number = 1
                    else:
                        board_number += 1

                    white_player_name = row[2]
                    white_player, _ = Player.objects.get_or_create(lichess_username__iexact=white_player_name, defaults={'lichess_username': white_player_name})
                    SeasonPlayer.objects.get_or_create(season=season, player=white_player)

                    black_player_name = row[3]
                    black_player, _ = Player.objects.get_or_create(lichess_username__iexact=black_player_name, defaults={'lichess_username': black_player_name})
                    SeasonPlayer.objects.get_or_create(season=season, player=black_player)

                    game_link = row[5]
                    result = row[6]

                    colors_reversed = len(row[7]) > 0

                    # TODO: Scheduled time

                    TeamPlayerPairing.objects.create(team_pairing=team_pairing, board_number=board_number, white=white_player, black=black_player, result=result, game_link=game_link, colors_reversed=colors_reversed, scheduled_time=None)

                for round_ in rounds:
                    round_.publish_pairings = True
                    round_.is_completed = True
                    round_.save()<commit_msg>Spreadsheet import WIP for season 1<commit_after>from .models import *
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from heltour import settings
from datetime import datetime, timedelta
from django.utils import timezone
from django.db import transaction
import re
from gspread.exceptions import WorksheetNotFound

def _open_doc(url):
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(settings.GOOGLE_SERVICE_ACCOUNT_KEYFILE_PATH, scope)
    gc = gspread.authorize(credentials)
    try:
        return gc.open_by_url(url)
    except gspread.SpreadsheetNotFound:
        raise SpreadsheetNotFound

def import_team_season(league, url, name, tag, rosters_only=False, exclude_live_pairings=False):
    doc = _open_doc(url)

    with transaction.atomic():

        sheet_standings = _trim_cells(doc.worksheet('Standings').get_all_values())

        # Read the round count
        playoffs = 0
        round_ = 1
        round_cols = []
        while True:
            try:
                round_cols.append(sheet_standings[0].index('Round %d' % round_))
            except ValueError:
                try:
                    round_cols.append(sheet_standings[0].index('Semifinal'))
                    playoffs = 2
                    round_ += 1
                    round_cols.append(sheet_standings[0].index('Final'))
                    round_ += 1
                    break
                except ValueError:
                    # No more rounds
                    break
            round_ += 1
        round_count = round_ - 1

        try:
            sheet_rosters = _trim_cells(doc.worksheet('Rosters').get_all_values())

            # Read the board count
            board = 1
            while True:
                try:
                    sheet_rosters[0].index('Board %d' % board)
                except ValueError:
                    # No more boards
                    break
                board += 1
            board_count = board - 1

            # Create the season
            season = Season.objects.create(league=league, name=name, tag=tag, rounds=round_count, boards=board_count, playoffs=playoffs, is_active=True)

            # Read the teams
            team_name_col = sheet_rosters[0].index('Teams')
            team_name_row = 1
            teams = []
            while True:
                team_name = sheet_rosters[team_name_row][team_name_col]
                if len(team_name) == 0:
                    break
                team = Team.objects.create(season=season, number=len(teams) + 1, name=team_name)
                TeamScore.objects.create(team=team)
                teams.append(team)
                team_name_row += 1

            # Read the team members and alternates
            alternates_start_row = [row[0] == 'Alternates' for row in sheet_rosters].index(True)
            for i in range(season.boards):
                board = i + 1
                player_name_col = sheet_rosters[0].index('Board %d' % board)
                player_rating_col = sheet_rosters[0].index('Rating %d' % board)
                # Team members
                for i in range(len(teams)):
                    player_row = i + 1
                    player_name, is_captain = _parse_player_name(sheet_rosters[player_row][player_name_col])
                    player_rating = sheet_rosters[player_row][player_rating_col]
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    SeasonPlayer.objects.get_or_create(season=season, player=player)
                    TeamMember.objects.get_or_create(team=teams[i], board_number=board, defaults={'player': player, 'is_captain':is_captain})
                # Alternates
                alternates_row = alternates_start_row
                while True:
                    player_name = sheet_rosters[alternates_row][player_name_col]
                    player_rating = sheet_rosters[alternates_row][player_rating_col]
                    if len(player_name) == 0 or len(player_rating) == 0:
                        break
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    season_player, _ = SeasonPlayer.objects.get_or_create(season=season, player=player)
                    Alternate.objects.get_or_create(season_player=season_player, defaults={'board_number': board})
                    alternates_row += 1

        except WorksheetNotFound:
            # Try using the alternative rosters format
            sheet_rosters = _trim_cells(doc.worksheet('Team Rosters').get_all_values())

            # Read the board count
            board_count = 0
            for row in sheet_rosters:
                match = re.match('Board ([0-9]+)', row[0])
                if match is not None:
                    board_count = int(match.group(1))
                if row[0] == 'Avg Rating':
                    break

            # Create the season
            season = Season.objects.create(league=league, name=name, tag=tag, rounds=round_count, boards=board_count, playoffs=playoffs, is_active=True)

            # Read the teams
            teams = []
            for col in range(1, len(sheet_rosters[0])):
                team_name = sheet_rosters[0][col]
                if len(team_name) == 0:
                    break
                team = Team.objects.create(season=season, number=len(teams) + 1, name=team_name)
                TeamScore.objects.create(team=team)
                teams.append(team)

            print teams

            # Read the team members
            for i in range(season.boards):
                board = i + 1
                name_row = board * 3 - 2
                rating_row = board * 3 - 1
                # Team members
                for i in range(len(teams)):
                    player_col = i + 1
                    player_name, is_captain = _parse_player_name(sheet_rosters[name_row][player_col])
                    player_rating = sheet_rosters[rating_row][player_col]
                    player, _ = Player.objects.update_or_create(lichess_username__iexact=player_name,
                                                                defaults={'lichess_username': player_name, 'rating': int(player_rating)})
                    SeasonPlayer.objects.get_or_create(season=season, player=player)
                    TeamMember.objects.get_or_create(team=teams[i], board_number=board, defaults={'player': player, 'is_captain':is_captain})

        if not rosters_only:

            try:
                # Try using the alternative pairings format
                sheet_fixed_pairings = _trim_cells(doc.worksheet('Fixed Pairings').get_all_values())

                rounds = Round.objects.filter(season=season).order_by('number')

                pairing_order = 0
                board_number = board_count

                for row in sheet_fixed_pairings:
                    try:
                        round_number = int(row[0])
                    except ValueError:
                        continue

                    white_team_name = row[1] if board_number == board_count or board_number % 2 == 0 else row[4]
                    print white_team_name
                    white_team = Team.objects.get(season=season, name__iexact=white_team_name)

                    black_team_name = row[4] if board_number == board_count or board_number % 2 == 0 else row[1]
                    print black_team_name
                    black_team = Team.objects.get(season=season, name__iexact=black_team_name)

                    team_pairing, created = TeamPairing.objects.get_or_create(round=rounds[round_number - 1], white_team=white_team, black_team=black_team, defaults={'pairing_order': pairing_order})
                    if created:
                        pairing_order += 1
                        board_number = 1
                    else:
                        board_number += 1

                    white_player_name = row[2]
                    white_player, _ = Player.objects.get_or_create(lichess_username__iexact=white_player_name, defaults={'lichess_username': white_player_name})
                    SeasonPlayer.objects.get_or_create(season=season, player=white_player)

                    black_player_name = row[3]
                    black_player, _ = Player.objects.get_or_create(lichess_username__iexact=black_player_name, defaults={'lichess_username': black_player_name})
                    SeasonPlayer.objects.get_or_create(season=season, player=black_player)

                    game_link = row[5]
                    result = row[6]

                    colors_reversed = sheet_fixed_pairings[0][7] == 'reversed' and len(row[7]) > 0

                    # TODO: Scheduled time

                    TeamPlayerPairing.objects.create(team_pairing=team_pairing, board_number=board_number, white=white_player, black=black_player, result=result, game_link=game_link, colors_reversed=colors_reversed, scheduled_time=None)

                for round_ in rounds:
                    round_.publish_pairings = True
                    round_.is_completed = True
                    round_.save()
{'fraction_non_alphanumeric': 0.08091431024306488, 'fraction_numerical': 0.00681440145946236, 'mean_word_length': 1.8077734257306417, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '798922', 'n_tokens_mistral': 5162, 'n_tokens_neox': 4792, 'n_words': 961}