class Allocation:
def __init__(self):
self.sheet = Sheet()
def generate_allocation(self, candidates):
allocation = logics.get_random_allocation(candidates)
return allocation
def create_allocation(self, match, allocations):
if not self.sheet.if_exists(configs.ALLOCATIONS_SHEET_ID, match['no']):
response = self.sheet.create_sheet(configs.ALLOCATIONS_SHEET_ID,
match['no'])
values = [["Match Number", match['no'], '', '', 'Created Date Time']]
values.append([
"Teams", match["team1"] + ' - vs - ' + match["team2"], '', '',
str(datetime.datetime.now())
])
values.append(["No", "Email", "Name", "Player 1", "Player 2"])
fdic = []
for i in range(1, len(allocations) + 1):
fdic.append([
data for data in allocations
if int(data['candidate']['id']) == i
][0])
allocations = fdic
for alloc in allocations:
values.append([
alloc['candidate']['id'], alloc['candidate']['email'],
alloc['candidate']['name'], alloc['players'][0],
alloc['players'][1]
])
self.sheet.update_values(configs.ALLOCATIONS_SHEET_ID,
match['no'] + "!A1", 'USER_ENTERED', values)
return values
def get_allocation(self, match):
rows = self.sheet.get_rows(
configs.ALLOCATIONS_SHEET_ID,
"'" + match + "'!" + configs.ALLOCATIONS_GET_SHEET_RANGE_POSTFIX)
allocations = []
for row in rows:
allocations.append({
'no': row[0],
'name': row[1],
'email': row[2],
'player1': row[3],
'player2': row[4]
})
return allocations
class CandidateResult:
def __init__(self):
self.sheet = Sheet()
def add_results(self, match, results):
if not self.sheet.if_exists(configs.CANDIDATE_RESULTS_SHEET_ID,
match['no']):
response = self.sheet.create_sheet(
configs.CANDIDATE_RESULTS_SHEET_ID, match['no'])
values = [[
"Match Number", match['no'], '', '', '', '', '', '', '',
'Created Date Time'
]]
values.append([
"Teams", match["team1"] + ' - vs - ' + match["team2"], '', '', '',
'', '', '', '',
str(datetime.datetime.now())
])
values.append([
"No", "Name", "Email", "Player 1", "Player 1 runs, wickets",
"Player 1 Points", "Player 2", "Player 2 runs, wickets",
"Player 2 Points", "Total Points"
])
point_results = results['results']
for res in point_results:
values.append([
res['no'], res['email'], res['name'],
res['player1'] + '(' + res['player1_name'] + ')',
res['player1_run_wicket'], res['player1_points'],
res['player2'] + '(' + res['player2_name'] + ')',
res['player2_run_wicket'], res['player2_points'], res['total']
])
values.append(["Winners", '', '', '', '', '', '', '', '', ''])
winners = results['winners']
for winner in winners:
values.append([
winner['no'], winner['email'], winner['name'],
winner['player1'] + '(' + winner['player1_name'] + ')',
winner['player1_run_wicket'], winner['player1_points'],
winner['player2'] + '(' + winner['player2_name'] + ')',
winner['player2_run_wicket'], winner['player2_points'],
winner['total']
])
self.sheet.update_values(configs.CANDIDATE_RESULTS_SHEET_ID,
match['no'] + "!A1", 'USER_ENTERED', values)
return values
def pin_added(body, client, context, logger):
context.ack()
event = body["event"]
channel_id = event["channel_id"]
channel_name = client.conversations_info(channel=channel_id)["channel"]["name"]
pinned_at = datetime.datetime.fromtimestamp(event["item"]["created"])
pinned_at = pinned_at.strftime("%d/%m/%Y %H:%M:%S")
message = event["item"]["message"]["text"]
message_by = client.users_info(user=event["item"]["message"]["user"])
message_username = message_by["user"]["profile"]["display_name"]
permalink = event["item"]["message"]["permalink"]
row = [pinned_at, channel_name, message_username, message, permalink]
pins_sheet.append(row)
class Candidate(CandidateBase):
def __init__(self):
self.sheet = Sheet()
def get_all(self):
cands = self.sheet.get_rows(configs.CANDIDATES_SHEET_ID, configs.CANDIDATES_SHEET_RANGE)
return [{'id': cand[0], 'name': cand[1], 'email': cand[2]} for cand in cands]
def message_saver(mention_event, thread_content):
thread_start_msg = thread_content.data["messages"][0]
txt = thread_start_msg["text"]
channel = mention_event["channel"]
thread_ts = mention_event["thread_ts"]
try:
client.reactions_add(channel=channel,
timestamp=thread_ts,
name="floppy_disk")
except SlackApiError:
pass
username = client.users_info(
user=thread_start_msg["user"])["user"]["profile"]["display_name"]
dt = datetime.fromtimestamp(float(thread_ts)).strftime("%d/%m/%Y %H:%M:%S")
pins_sheet.append([channel, dt, username, txt],
subsheet=MESSAGE_SAVER_SHEET)
client.chat_postMessage(text=f"```{txt}``` Aldim bunu.",
thread_ts=thread_ts,
channel=channel)
def add_links_to_sheet(links, channel_name):
today_start = datetime.today().replace(minute=0,
hour=0,
second=0,
microsecond=0)
inserted_link_count = 0
user_map = {}
links.sort(key=lambda tup: tup[1])
for user_id, msg_ts, title, link in links:
pinned_at = datetime.fromtimestamp(msg_ts)
if pinned_at > today_start:
username = user_map.get(user_id)
if not username:
username = user_map[user_id] = client.users_info(
user=user_id)["user"]["profile"]["display_name"]
pinned_at = pinned_at.strftime("%d/%m/%Y %H:%M:%S")
pins_sheet.append([pinned_at, title, link, username],
subsheet=channel_name)
inserted_link_count += 1
return inserted_link_count
def main():
''' This program will be able to execute multiple tasks
to fetch data and put it in Google Sheets
Task:
A task is a operation of fetching data from a MySql DataBase
and uploads it to a Google Sheet
How to make a new Task:
1: Make a new directory in the 'tasks' directory
2: Create a 'credentials.json' file
- Read doc's in 'configuration.py' for layout
3: Create a 'query.sql' file
'''
tasks_dir = "tasks/"
for task in os.listdir(tasks_dir):
if os.path.isdir('{}/{}'.format(tasks_dir, task)):
config = Configuration(task_path=task)
try:
# Get data from database
dbData = DataBase(config=config).getTableData()
columns = dbData['column_names']
data = dbData['table_data']
# Upload data to sheet
sheet = Sheet(config=config)
sheet.clear_sheet()
sheet.upload_data(columns=columns, data=data, date_time=True)
printMessage("Completed task: " + task)
except Exception as error:
printMessage("{} => {}".format(task, error))
traceback.print_exc()
return 0
class Match:
def __init__(self):
self.sheet = Sheet()
def get_all(self):
vals = self.sheet.get_rows(configs.MATCHES_SHEET_ID,
configs.MATCHES_SHEET_RANGE)
matches = []
for val in vals:
matches.append({'no': val[0], 'team1': val[1], 'team2': val[2]})
return matches
def get(self, match_no):
matches = self.get_all()
return [match for match in matches if match['no'] == match_no][0]
class MatchResults:
def __init__(self):
self.sheet = Sheet()
def get(self, match_no):
vals = self.sheet.get_rows(
configs.MATCH_RESULTS_SHEET_ID,
"'" + match_no + "'" + configs.MATCH_RESULTS_SHEET_RANGE_POSTFIX)
results = []
for val in vals:
results.append({
'player': val[0],
'player_name': val[1],
'runs': val[2],
'wickets': val[3]
})
return results
from flask import Flask, request, session, g, redirect, url_for, abort, \
render_template, flash
from sheets import Sheet
from local_settings import II_SHEET_ID, II_SHEET_RANGE
app = Flask(__name__)
app.config.from_object(__name__)
innovating_instruction = Sheet(
II_SHEET_ID,
II_SHEET_RANGE)
@app.route('/')
def hello_world():
return render_template('map.html',
locations=innovating_instruction.get_locations())
def __init__(self):
self.sheet = Sheet()
from lights import Light, LightSet
from sheets import Sheet
from time import sleep
from datetime import datetime, timedelta
from people import Person
import os
datasource = f'{os.environ["GOOGLE_SHEET_KEY"]}'
# dictionary to store current status
person_status = {'Zaks-iPhone': None, 'Louise’s iPhone': None}
sheet = Sheet(datasource)
lights = LightSet()
for i in range(1, 13):
lights.add_light(i, Light(i))
# louise = Person('Louise')
# zak = Person('Zak')
previous_index = None
async def poll_event(loop, prev_datetime):
global previous_index
records_since = sheet.get_records_since_index(previous_index)