def save_jury(self): """ save data """ values1 = self.middle.save() values2 = self.right_side.save() # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # clear Jury Table cursor.execute("truncate Jury;") # add members with renewed information for jury_member in values1: if jury_member[0] == "": continue insert_jury_member(cursor, jury_member) for jury_member in values2: if jury_member[0] == "": continue insert_jury_member(cursor, jury_member) # commit data cnx.commit() # sort data self.middle.clear() self.right_side.clear() self.load_jury()
def commit(self): """ commit data""" problems = set() # find out the categories for num in range(8): button_group = ToggleButtonBehavior.get_widgets(str(num + 1)) for button in button_group: if button.state == "down": problems.add(("Problem {}".format(num + 1), button.text)) # create connection cnx = create_connection() cnx.database = self.parent_widget.database_name cursor = cnx.cursor() # push data query = """delete from Problems_{} where day={} ;""".format(self.parent_widget.category, self.parent_widget.round) cursor.execute(query) insert_problems(cursor, self.parent_widget.category, self.parent_widget.round, problems) # commit and exit cnx.commit() cnx.close() # exit popup and reload parent self.popup.dismiss() self.parent_widget.load()
def __init__(self, database_name, category, **kwargs): super(CategoryContents, self).__init__(**kwargs) self.database_name = database_name self.category = category # get round cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() cursor.execute('select value from Variables where name=\"current_round_{}\"'.format(self.category)) self.round = cursor.fetchall()[0][0] # get information for add protocols cursor.execute("select * from Matches_{} where day={};".format(self.category, self.round)) self.locked = len(cursor.fetchall()) != 0 cnx.close() self.rows = 1 self.left_widget = CategoryButtons(size_hint_x=0.1) self.middle_widget = CategoryTable(self, size_hint_x=0.45) self.right_widget = MatchTable(self, size_hint_x=0.45) self.add_widget(self.left_widget) self.add_widget(self.middle_widget) self.add_widget(self.right_widget) self.load()
def create_database(self): """ create database """ # init database and tell the screen manager to refer to it from now setup_database(self.tname) self.screen_manager.set_database_name(self.tname) # create connection and navigate to database cnx = create_connection() cnx.database = self.tname # get cursor cursor = cnx.cursor() # create variables dict and insert it into database # pylint: disable=C0330 variables = { "tournament_name": self.tname, "number_rounds": self.nrounds, "number_teams_Small": self.nteams_small, "number_teams_Medium": self.nteams_medium, "number_teams_Big": self.nteams_big, "current_round_Small": 1, "current_round_Medium": 1, "current_round_Big": 1, } # insert variables into database insert_variables(cursor, variables) # get team names teams1 = self.screen_manager.get_my_screen( "create_continued_small").team_names teams2 = self.screen_manager.get_my_screen( "create_continued_medium").team_names teams3 = self.screen_manager.get_my_screen( "create_continued_big").team_names # insert teams insert_teams(cursor, teams1, "Small") insert_teams(cursor, teams2, "Medium") insert_teams(cursor, teams3, "Big") # commit and close connection cnx.commit() cnx.close() ### make dir for this tournament # directory name directory_path = os.getcwd() + "/tournaments/" + self.tname # delete if already exists if os.path.isdir(directory_path): rmtree(directory_path) # create directory and add init file in it os.mkdir(directory_path) f_name = open(directory_path + "/__init__.py", 'w') f_name.write("# Do not delete")
def load(self): """ load previous data """ # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # get round cursor.execute('select value from Variables where name=\"current_round_{}\"'.format(self.category)) self.round = cursor.fetchall()[0][0] # get information for set problems cursor.execute("select * from Problems_{} where day={};".format(self.category, self.round)) num_problems = len(cursor.fetchall()) self.add_protocols_activate = False self.finish_day_activate = False if num_problems == 0: self.set_problems_color = [1, 0, 0, 1] elif num_problems < 8: self.set_problems_color = [1, 1, 0, 1] elif num_problems == 8: self.set_problems_color = [0, 1, 0, 1] self.add_protocols_activate = True # get information for add protocols cursor.execute("select * from Matches_{} where day={};".format(self.category, self.round)) total = len(cursor.fetchall()) cursor.execute("select * from Matches_{} where day={} and protocol_set=1;".format(self.category, self.round)) done = len(cursor.fetchall()) # close connection cnx.close() # locked and colors if total != 0: if done == 0: self.add_protocols_color = [1, 0, 0, 1] elif done != total: self.add_protocols_color = [1, 1, 0, 1] else: self.add_protocols_color = [0, 1, 0, 1] self.finish_day_activate = True else: self.add_protocols_color = [1, 1, 1, 1] # call widgets load method self.left_widget.load() self.middle_widget.load() self.right_widget.load()
def print_matches(self): """ print matches """ # create connection cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() print_matches(cursor, self.category, self.round)
def load(self): """ load previous data """ self.round = self.parent_widget.round for widget in self.widgets: widget.clear() # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # get matches cursor.execute("select * from Matches_{} where day={};".format(self.category, self.round)) matches = cursor.fetchall() #close connection cnx.close() # if no matches found, continue if matches: return # assining names num = 0 for match in matches: team_one = match[0] team_two = match[1] jury_one = match[3] jury_two = match[4] if team_one == team_two: free_team = team_one continue self.widgets[num].set_button("button_left", team_one) self.widgets[num].set_button("button_right", team_two) self.widgets[num].set_button("button_top", jury_one) if jury_two != "Null": self.widgets[num].set_button("button_bottom", jury_two) num += 1 # extra "free" widget if odd number of teams if self.nteams%2: self.widgets[-1].set_button(free_team)
def __init__(self, parent_widget, **kwargs): super(CategoryTable, self).__init__(**kwargs) self.parent_widget = parent_widget # data self.database_name = parent_widget.database_name self.category = parent_widget.category self.round = parent_widget.round # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() cursor.execute("select value from Variables where name=\"number_teams_{}\";".format(self.category)) nteams = cursor.fetchall()[0][0] self.nteams = int(nteams) # set grid self.cols = 4 self.padding = 50 # draw first row: self.add_widget(BorderedLabel(size_hint=(1, None), height=ROW_HEIGHT, text="Team Name")) self.add_widget(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT, text="Rank Scores")) self.add_widget(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT, text="Points Diff.")) self.add_widget(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT, text="Total Points")) # draw rest of the rows: self.widgets = [] for _ in range(self.nteams): widget_row = [] widget_row.append(BorderedLabel(size_hint=(1, None), height=ROW_HEIGHT)) widget_row.append(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT)) widget_row.append(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT)) widget_row.append(BorderedLabel(size_hint=(0.5, None), height=ROW_HEIGHT)) self.widgets.append(widget_row) for widget_row in self.widgets: for widget in widget_row: self.add_widget(widget) # close connection cnx.close()
def __init__(self, parent_widget, **kwargs): super(MatchTable, self).__init__(**kwargs) self.parent_widget = parent_widget self.database_name = parent_widget.database_name self.category = parent_widget.category self.round = parent_widget.round self.prev_button = None ### find number of teams and team names # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() cursor.execute("select value from Variables where name=\"number_teams_{}\";".format(self.category)) nteams = cursor.fetchall()[0][0] nteams = int(nteams) # close connection cnx.close() # set grid self.cols = 1 self.padding = [10, 10] self.spacing = [10, 10] # indicate whether previous button was pressed self.pressed = 0 # draw widgets self.nteams = nteams self.widgets = [] for i in range(nteams//2): self.widgets.append((MatchWidget(i, size_hint_y=None, height=75))) if nteams%2: self.widgets.append((FreeWidget(size_hint_y=None, height=85))) for widget in self.widgets: self.add_widget(widget) for widget in self.widgets: widget.lock()
def __init__(self, popup, parent_widget, **kwargs): super(ProtocolsPopup, self).__init__(**kwargs) self.parent_widget = parent_widget self.popup = popup self.database_name = parent_widget.database_name self.category = parent_widget.category self.round = parent_widget.round # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # get matches cursor.execute("select * from Matches_{} where day={};".format( self.category, self.round)) matches = cursor.fetchall() # set table self.cols = 1 self.rows = len(matches) + 1 self.spacing = 10 self.padding = 10 self.widgets = [] # for ACTUAL matches (exclude free wins) for match in matches: if match[0] != match[1]: self.widgets.append(ProtocolRow(self, match[0], match[1])) # free match for match in matches: if match[0] == match[1]: self.widgets.append(FreeRow(self, match[0])) # add buttons widget self.widgets.append(ProtocolButtons()) # draw widgets for widget in self.widgets: self.add_widget(widget)
def __init__(self, name, screen_manager): self.name = name self.screen_manager = screen_manager self.window_size = (500, 400) ### information string and relevant info # connect database cnx = create_connection() cnx.database = self.screen_manager.database_name cursor = cnx.cursor() # get information from database cursor.execute( "select value from Variables where name=\"tournament_name\";") tname = cursor.fetchall()[0][0] cursor.execute( "select value from Variables where name=\"number_rounds\";") nrounds = cursor.fetchall()[0][0] cursor.execute( "select value from Variables where name=\"number_teams_small\";") nsmall = cursor.fetchall()[0][0] cursor.execute( "select value from Variables where name=\"number_teams_medium\";") nmedium = cursor.fetchall()[0][0] cursor.execute( "select value from Variables where name=\"number_teams_big\";") nbig = cursor.fetchall()[0][0] self.information_string = """Name of Tournament: {} \n Number of Rounds: {} \n Number of Teams (6-7): {} \n Number of Teams (8-9): {} \n Number of Teams (10-12): {} """.format(tname, nrounds, nsmall, nmedium, nbig) # close connection cnx.close() super(SuccessfulScreen, self).__init__()
def finish_day(self): """ finish day """ # update round cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # go to next day cursor.execute('update Variables set value=value+1 where name=\"current_round_{}\"'.format(self.category)) # commit and close cnx.commit() cnx.close() # reload self.unlock() self.load()
def setup_database(db_name): """setup database and tables in it""" # create conection cnx = create_connection() # get cursor cursor = cnx.cursor() # create database and navigate to it create_database(cursor, db_name) cnx.database = db_name # create tables setup_tables(cursor) # close connection cnx.close()
def load(self): """ load previous data """ self.round = self.parent_widget.round # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() cursor.execute("select hidden_name, rank_scores, points_diff, points from Teams_{} order by rank_scores desc, points_diff desc, points desc, hidden_name;".format(self.category)) team_info = [result for result in cursor.fetchall()] for row_num in range(len(self.widgets)): for col_num in range(len(self.widgets[row_num])): self.widgets[row_num][col_num].text = str(team_info[row_num][col_num]) # close connection cnx.close()
def generate_round(database_name, category, day): """generate the round by splitting into matches and commiting to Matches_{Category} tables""" # connect to database cnx = create_connection() cnx.database = database_name cursor = cnx.cursor() # clear matches table if previous assignment cursor.execute("delete from Matches_{} where day={}".format(category, day)) cnx.commit() # split into matches matches = generate_matches(cursor, category) # insert matches insert_matches(cursor, category, day, matches) # commit data cnx.commit()
def load(self): """ load previously stored data """ self.round = self.parent_widget.round # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # find if this match protocol has been set query = "select protocol_set from Matches_{} where team_one=\"{}\" and team_two=\"{}\" and day={};".format( self.category, self.team, self.team, self.round) cursor.execute(query) result = cursor.fetchall()[0][0] if result: self.children[0].background_color = [0, 1, 0, 1] self.children[0].disabled = True else: self.children[0].background_color = [1, 0, 0, 1]
def __init__(self, parent_widget, team, **kwargs): super(TeamSide, self).__init__(**kwargs) self.parent_widget = parent_widget self.database_name = parent_widget.database_name self.category = parent_widget.category self.cols = 1 self.padding = [20, 10] self.spacing = 10 ### find team members # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # get people from team cursor.execute("select name from People_{} where team=\"{}\";".format( self.category, team)) people = [result[0] for result in cursor.fetchall()] # draw buttons for each person self.add_widget( Label(text=team, font_size='22sp', size_hint_y=None, height=ROW_HEIGHT)) # buttons for each person in the team for person in people: self.add_widget(ValueButton(self, text=person)) # add empty labels for spacing for _ in range(7 - len(people)): self.add_widget(Label(text="")) self.add_widget(NumPad(self, size_hint_y=None, height=300))
def lock(self): """ lock widgets""" # remove pressed button if it exists self.pressed = 0 # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # clear previous and create matches cursor.execute("delete from Matches_{} where day={};".format(self.category, self.round)) cnx.commit() for widget in self.widgets: info = widget.lock() cursor.execute("insert into Matches_{} values (\"{}\", \"{}\", {}, \"{}\", \"{}\", 0)".format(self.category, info[0], info[1], self.round, info[2], info[3])) # commit and renew view cnx.commit() cnx.close()
def set(self): """ add info """ # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() query = "update Matches_{} set protocol_set=1 where team_one=\"{}\" and team_two=\"{}\" and day={}".format( self.category, self.team, self.team, self.round) cursor.execute(query) # give them 2 rank scores, 48 points and 0 point diff cursor.execute( "update Teams_{} set rank_scores=rank_scores+2, points=points+48 where hidden_name=\"{}\";" .format(self.category, self.team)) cnx.commit() cnx.close() # reload self.load()
def load_jury(self): """ load previous data """ # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # get information cursor.execute( "select * from Jury order by active desc, small desc, medium desc, big desc, alone desc, name;" ) for jury_member in cursor.fetchall(): name = jury_member[0] states = jury_member[1:] if self.middle.count < 25: self.middle.load({'name': name, 'states': states}) else: self.right_side.load({'name': name, 'states': states})
def load(self): """ loading info """ self.round = self.parent_widget.round # create connection cnx = create_connection() cnx.database = self.parent_widget.database_name cursor = cnx.cursor() # get data cursor.execute("""select * from Problems_{} where day={} ;""".format(self.parent_widget.category, self.round)) problems_set = cursor.fetchall() # send to corresponding row for problem in problems_set: problem_num = int(problem[0].split(" ")[1]) problem_category = problem[1] self.widgets[problem_num - 1].set_loaded(problem_category)
def finish(self): """ finish making changes in protocol """ # remove the navigation buttons self.widgets.pop() # remove selection and redraw self.deactivate() # add Final Results Line self.add_widget( Label(text="", size_hint=(self.sizes[0], None), height=ROW_HEIGHT)) self.add_widget( Label(text="Final Results:", font_size='20sp', size_hint=(self.sizes[1], None), height=ROW_HEIGHT)) for i in range(2, 7): self.add_widget( Label(size_hint=(self.sizes[i], None), height=ROW_HEIGHT)) # get team names team_one_name = self.parent.team_one team_two_name = self.parent.team_two # get points team_one_points = 0 for i in range(8): if self.widgets[i][2].text != '': team_one_points += int(self.widgets[i][2].text) team_two_points = 0 for i in range(8): if self.widgets[i][5].text != '': team_two_points += int(self.widgets[i][5].text) jury_points = 0 for i in range(8): if self.widgets[i][6].text != '': jury_points += int(self.widgets[i][6].text) # get outcome of the match if abs(team_one_points - team_two_points) <= 3: outcome = "Tie" elif team_one_points > team_two_points: outcome = "Winner " + team_one_name else: outcome = "Winner " + team_two_name # draw results line self.add_widget( BorderedLabel(text="", size_hint=(self.sizes[0], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=team_one_name, size_hint=(self.sizes[1], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=str(team_one_points), size_hint=(self.sizes[2], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=outcome, size_hint=(self.sizes[3], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=team_two_name, size_hint=(self.sizes[4], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=str(team_two_points), size_hint=(self.sizes[5], None), height=ROW_HEIGHT)) self.add_widget( BorderedLabel(text=str(jury_points), size_hint=(self.sizes[6], None), height=ROW_HEIGHT)) # create data dictionary self.data = {} # team one data self.data["team_one_name"] = team_one_name self.data["team_one_points"] = team_one_points self.data["team_one_point_diff"] = team_one_points - team_two_points # team two data self.data["team_two_name"] = team_two_name self.data["team_two_points"] = team_two_points self.data["team_two_point_diff"] = team_two_points - team_one_points # assigning scores based on outcome if outcome == "Tie": self.data["team_one_score"] = 1 self.data["team_two_score"] = 1 elif team_one_points > team_two_points: self.data["team_one_score"] = 2 self.data["team_two_score"] = 0 else: self.data["team_one_score"] = 0 self.data["team_two_score"] = 2 # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # getting information about problems categories query = "select category from Problems_{} where day={} order by name;".format( self.category, self.round) cursor.execute(query) category_to_int = {"Alg.": 0, "Comb.": 1, "Geom.": 2, "Num.": 3} problem_categories = [ category_to_int[result[0]] for result in cursor.fetchall() ] # create for each person a list of scores # ordering: Alg, Comb, Geom, Num Th, Total query = "select name from People_{} where team=\"{}\";".format( self.category, team_one_name) cursor.execute(query) self.data["team_one_people"] = { result[0]: [0, 0, 0, 0, 0] for result in cursor.fetchall() } query = "select name from People_{} where team=\"{}\";".format( self.category, team_two_name) cursor.execute(query) self.data["team_two_people"] = { result[0]: [0, 0, 0, 0, 0] for result in cursor.fetchall() } # go through each row and assign points to people for i in range(8): # check if there is information on this row else break if self.widgets[i][0].text == "": continue problem_number = int(self.widgets[i][0].text) - 1 problem_cat = problem_categories[problem_number] person_one_name = self.widgets[i][1].text person_one_score = int(self.widgets[i][2].text) person_two_name = self.widgets[i][4].text person_two_score = int(self.widgets[i][5].text) # assign scores to people self.data["team_one_people"][person_one_name][ problem_cat] += person_one_score self.data["team_one_people"][person_one_name][ 4] += person_one_score self.data["team_two_people"][person_two_name][ problem_cat] += person_two_score self.data["team_two_people"][person_two_name][ 4] += person_two_score
def commit_data(self): """ commit data to database """ # connect to database cnx = create_connection() cnx.database = self.database_name cursor = cnx.cursor() # update Teams table t1s = self.data["team_one_score"] t2s = self.data["team_two_score"] t1p = self.data["team_one_points"] t2p = self.data["team_two_points"] t1pd = self.data["team_one_point_diff"] t2pd = self.data["team_two_point_diff"] t1n = self.data["team_one_name"] t2n = self.data["team_two_name"] query1 = """update Teams_{} set rank_scores=rank_scores+{}, points=points+{}, points_diff=points_diff+{} where hidden_name=\"{}\"""".format(self.category, t1s, t1p, t1pd, t1n) query2 = """update Teams_{} set rank_scores=rank_scores+{}, points=points+{}, points_diff=points_diff+{} where hidden_name=\"{}\"""".format(self.category, t2s, t2p, t2pd, t2n) cursor.execute(query1) cursor.execute(query2) # update Matches table query = "update Matches_{} set protocol_set=1 where team_one=\"{}\" and team_two=\"{}\" and day={}".format( self.category, t1n, t2n, self.round) cursor.execute(query) # update People table for person in self.data["team_one_people"]: alg = self.data["team_one_people"][person][0] comb = self.data["team_one_people"][person][1] geom = self.data["team_one_people"][person][2] num = self.data["team_one_people"][person][3] tot = self.data["team_one_people"][person][4] query = """update People_{} set alg_scores=alg_scores+{}, comb_scores=comb_scores+{}, geom_scores=geom_scores+{}, num_scores=num_scores+{}, total_scores=total_scores+{} where name=\"{}\" and team=\"{}\"""".format( self.category, alg, comb, geom, num, tot, person, t1n) cursor.execute(query) for person in self.data["team_two_people"]: alg = self.data["team_two_people"][person][0] comb = self.data["team_two_people"][person][1] geom = self.data["team_two_people"][person][2] num = self.data["team_two_people"][person][3] tot = self.data["team_two_people"][person][4] query = """update People_{} set alg_scores=alg_scores+{}, comb_scores=comb_scores+{}, geom_scores=geom_scores+{}, num_scores=num_scores+{}, total_scores=total_scores+{} where name=\"{}\" and team=\"{}\"""".format( self.category, alg, comb, geom, num, tot, person, t2n) cursor.execute(query) # commit and close cnx.commit() cnx.close()