def test_sheets_returns_false_for_empty_values(mocker):
patient = GSheetChecker('fake.table', 'sheet_id', 'worksheet_name',
'TESTING')
patient.build_header_row_index([
'Issue', 'Authoritative Access From', 'SGID Data Layer',
'Refresh Cycle (Days)', 'Last Update', 'Days From Last Refresh',
'Days to Refresh', 'Description', 'Data Source', 'Use Restrictions',
'Website URL', 'Anchor', 'Data Type', 'PEL Layer', 'PEL Status',
'Governance/Agreement', 'PEL Inclusion', 'Agency Contact Name',
'Agency Contact Email', 'SGID Coordination', 'Archival Schedule',
'Endpoint', 'Tier', 'Webapp', 'Notes', 'Deprecated'
])
patient._get_data = mocker.Mock(return_value=[Cell('A1', 'fake.table')])
mocker.patch('pygsheets.Cell.link')
mocker.patch('pygsheets.Cell.neighbour', return_value=Cell('A2'))
response = patient.exists()
assert response.valid == True
assert response.messages == {
'Description': False,
'Data Source': False,
'Website URL': False,
'Data Type': False,
'Endpoint': False,
'Deprecated': False,
}
def model_cell(wks: Worksheet, index: str) -> Cell:
'''Prepare model_cell to apply to DataRange in main program'''
cell = Cell(index)
wks.unlink()
cell.text_format['fontSize'] = 11
cell.text_format['bold'] = True
cell.borders = {'top': {'style': 'SOLID'}}
wks.link()
return cell
def write(self, google_doc_name, dictionary):
worksheet = self._get_worksheet(google_doc_name)
log_step("Clear spreadsheet...")
self._clear_worksheet(worksheet)
log_step("Writing cells...")
start_time = time.time()
cells = []
row = 1
languages = dictionary.languages
languages = sorted(languages)
for index, lang in enumerate(languages):
column = index + 2
cell = Cell((row, column))
cell.value = lang
cells.append(cell)
row = 2
for key in dictionary.keys():
cell = Cell((row, 1))
cell.value = key
cells.append(cell)
for index, lang in enumerate(languages):
column = index + 2
translated_value = dictionary.get_translation(key, lang)
cell = Cell((row, column))
cell.value = translated_value
cells.append(cell)
row += 1
worksheet.update_cells(cell_list=cells)
log_step("Done writing cells ({} seconds)".format(
int(time.time() - start_time)))
def test_sheets_with_duplicate_cells_returns_false(mocker):
patient = GSheetChecker('fake.table', 'sheet_id', 'worksheet_name',
'TESTING')
mocker.patch('pygsheets.Cell.link')
patient._get_data = mocker.Mock(return_value=[
Cell('A1', val='fake.table'),
Cell('A2', val='fake.table')
])
response = patient.exists()
assert response.valid == False
assert response.messages == 'There are multiple items with this name on rows 1, 2. Please remove the duplicates.'
def sync_export(worksheet, users_participants, rounds_rolls, pools_titles,
all_titles, allow_hidden):
writer = ColWriter(users_participants)
writer.write_header('Participants')
sorted_participants = list(
map(lambda x: x[1], sorted(users_participants,
key=lambda x: x[0].name)))
for participant in sorted_participants:
writer.write_participant(participant)
writer.next_col()
titles = {t.id: t for t in all_titles}
stats = {}
for round, rolls in rounds_rolls:
roll_by_participant_id = {r.participant_id: r for r in rolls}
fmt = '%d.%m'
writer.write_header(
f'Round { round.num } ({round.start_time.strftime(fmt)}-{round.finish_time.strftime(fmt)})'
)
for participant in sorted_participants:
if participant.id not in roll_by_participant_id:
writer.write_fail()
else:
title = titles[roll_by_participant_id[participant.id].title_id]
writer.write_title(title,
participant.failed_round_id == round.id)
writer.next_col()
writer.write_header('Score')
for participant in sorted_participants:
score = roll_by_participant_id[
participant.
id].score if participant.id in roll_by_participant_id else None
update_stats(stats, participant, score, len(rounds_rolls))
writer.write_score(score)
writer.next_col()
stats = {
k: (v[0], v[1], None if v[2] is None else v[2] / v[3])
for k, v in stats.items()
}
for i, col in enumerate(['Min', 'Max', 'Avg']):
writer.write_header(col)
for participant in sorted_participants:
stat = None if participant.id not in stats else stats[
participant.id][i]
writer.write_score(stat)
writer.next_col()
for pool, titles in pools_titles:
writer.write_header(f'{ pool.name } (unused titles)')
for title in titles:
if not title.is_used:
writer.write_title(title, allow_hidden=allow_hidden)
writer.next_col()
# Cell((0, 0)) clears the entire screen
worksheet.update_cells([Cell((0, 0))] + writer.cells)
worksheet.adjust_column_width(1, worksheet.cols)
def get_values(self,
start,
end,
returnas='matrix',
majdim='ROWS',
include_empty=True,
value_render=ValueRenderOption.UNFORMATTED):
values = self.client.get_range(self.spreadsheet.id,
self._get_range(start, end),
majdim.upper(),
value_render=value_render)
start = format_addr(start, 'tuple')
if not include_empty:
matrix = values
else:
max_cols = len(max(values, key=len))
matrix = [list(x + [''] * (max_cols - len(x))) for x in values]
if returnas == 'matrix':
return matrix
else:
cells = []
for k in range(len(matrix)):
row = []
for i in range(len(matrix[k])):
if majdim == 'COLUMNS':
row.append(
Cell((start[0] + i, start[1] + k), matrix[k][i],
self))
elif majdim == 'ROWS':
row.append(
Cell((start[0] + k, start[1] + i), matrix[k][i],
self))
else:
raise InvalidArgumentValue('majdim')
cells.append(row)
return cells
def update_variables(self, **kwargs):
for var_name, var_value in kwargs.items():
if var_name not in self.template_cells:
continue
template_cell = self.template_cells[var_name]
if template_cell.direction == "fixed":
template_cell.template_cell.value = str(var_value)
self._cell_buffer.append(template_cell.template_cell)
else:
cell = Cell(tuple(template_cell.position))
cell.value = str(var_value)
# Update style
if None not in template_cell.template_cell.color:
cell.color = template_cell.template_cell.color
# Increase the position for the next entry of this cell
if template_cell.direction == "row":
self.template_cells[var_name].inc_column()
elif template_cell.direction == "column":
self.template_cells[var_name].inc_row()
self._cell_buffer.append(cell)
def test_get_values(self):
self.worksheet.resize(10, 10)
self.worksheet.clear()
self.worksheet.update_cells('A1:C2', [[1, 2, ''], [2, 3, 4]])
assert self.worksheet.get_values('A1',
'E5') == [[u'1', u'2', '', '', ''],
[u'2', u'3', u'4', '', '']]
# @TODO not working
# assert self.worksheet.get_values('A1','D3', returnas="cells") == [[Cell('A1', '1'), Cell('B1','2'), Cell('C1',''), Cell('D1','')],
# [Cell('A2','2'), Cell('B2','3'), Cell('C2','4'), Cell('D2','')]]
assert self.worksheet.get_values(
'A1', 'D3', returnas="cells", include_empty=False) == [[
Cell('A1', '1'), Cell('B1', '2')
], [Cell('A2', '2'),
Cell('B2', '3'),
Cell('C2', '4')]]
assert self.worksheet.get_values('D1',
'D3',
returnas="cells",
include_all=True) == [[
Cell('D1', '')
], [Cell('D2', '')], [Cell('D3', '')]]
def test_get_values(self):
self.worksheet.resize(10, 10)
self.worksheet.clear()
assert self.worksheet.get_values('A1',
'E5',
include_tailing_empty=True,
include_tailing_empty_rows=True)
self.worksheet.update_values(
'A1:D4',
[[1, 2, '', 3], ['', '', '', ''], [4, 5, '', ''], ['', 6, '', '']])
# matrix testing
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=True, include_tailing_empty_rows=True) == \
[[u'1', u'2', u'', u'3', ''], ['', '', '', '', ''], [u'4', u'5', '', '', ''], [u'', u'6', '', '', ''], ['', '', '', '', '']]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=True, include_tailing_empty_rows=False) == \
[[u'1', u'2', u'', u'3', ''], ['', '', '', '', ''], [u'4', u'5', '', '', ''], [u'', u'6', '', '', ''] ]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=False, include_tailing_empty_rows=True) == \
[[u'1', u'2', u'', u'3'], [], [u'4', u'5'], [u'', u'6'], []]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=False, include_tailing_empty_rows=False) == \
[[u'1', u'2', u'', u'3'], [], [u'4', u'5'], [u'', u'6']]
# matrix testing columns
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=True, include_tailing_empty_rows=True, majdim="COLUMNS") == \
[[u'1', u'', u'4', '', ''],[u'2', u'', u'5', u'6', ''], ['', '', '', '', ''], [u'3', '', '', '', ''], ['', '', '', '', '']]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=True, include_tailing_empty_rows=False, majdim="COLUMNS") == \
[[u'1', u'', u'4', '', ''],[u'2', u'', u'5', u'6', ''], ['', '', '', '', ''], [u'3', '', '', '', '']]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=False, include_tailing_empty_rows=True, majdim="COLUMNS") == \
[[u'1', u'', u'4'], [u'2', u'', u'5', u'6'], [], [u'3'], []]
assert self.worksheet.get_values('A1', 'E5', include_tailing_empty=False, include_tailing_empty_rows=False, majdim="COLUMNS") == \
[[u'1', u'', u'4'], [u'2', u'', u'5', u'6'], [], [u'3']]
# Cells testing rows
self.worksheet.clear()
self.worksheet.update_values('A1:B2', [[1, 2], [3, '']])
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=True, include_tailing_empty_rows=True,returnas="cells") == \
[[Cell('A1', '1'), Cell('B1', '2'), Cell('C1', '')],
[Cell('A2', '3'), Cell('B2', ''), Cell('C2', '')],
[Cell('A3', ''), Cell('B3', ''), Cell('C3', '')]]
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=True, include_tailing_empty_rows=False,returnas="cells") == \
[[Cell('A1', '1'), Cell('B1', '2'), Cell('C1', '')],
[Cell('A2', '3'), Cell('B2', ''), Cell('C2', '')]]
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=False, include_tailing_empty_rows=True ,returnas="cells") == \
[[Cell('A1', '1'), Cell('B1', '2')], [Cell('A2', '3')], []]
assert self.worksheet.get_values('A1', 'D3', include_tailing_empty=False, include_tailing_empty_rows=False,returnas="cells" ) == \
[[Cell('A1', '1'), Cell('B1', '2')], [Cell('A2', '3')]]
# Cells testing cols
self.worksheet.clear()
self.worksheet.update_values('A1:B2', [[1, 2], [3, '']])
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=True, include_tailing_empty_rows=True,returnas="cells",majdim="COLUMNS") == \
[[Cell('A1', '1'), Cell('A2', '2'), Cell('A3', '')],
[Cell('B1', '3'), Cell('B2', ''), Cell('B3', '')],
[Cell('C1', ''), Cell('C2', ''), Cell('C3', '')]]
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=True, include_tailing_empty_rows=False,returnas="cells",majdim="COLUMNS") == \
[[Cell('A1', '1'), Cell('A2', '2'), Cell('A3', '')],
[Cell('B1', '3'), Cell('B2', ''), Cell('B3', '')]]
assert self.worksheet.get_values('A1', 'C3', include_tailing_empty=False, include_tailing_empty_rows=True ,returnas="cells",majdim="COLUMNS") == \
[[Cell('A1', '1'), Cell('A2', '2')], [Cell('B1', '3')], []]
assert self.worksheet.get_values('A1', 'D3', include_tailing_empty=False, include_tailing_empty_rows=False,returnas="cells",majdim="COLUMNS") == \
[[Cell('A1', '1'), Cell('A2', '2')], [Cell('B1', '3')]]
async def on_message(self, message: Message):
"""Listener about message"""
await self.wait_until_ready()
content = str(message.content).lower()
if content == 'seria update roster':
await self.send_message(message.channel, '開始紀錄總表')
worksheet = spreadsheet.worksheet_by_title(
config.WorkSheetType.PlayerRoster)
roster_matrix = worksheet.range('A3:D', returnas='matrix')
redis_keys = redis_db.keys()
if redis_keys:
redis_db.delete(*redis_keys)
with redis_db.pipeline() as pipe:
redis_key = None
mapping = {}
for player_name, discord_id, character_name, job, in roster_matrix:
if not player_name and not character_name and not job:
break
if player_name:
if redis_key:
pipe.hmset(redis_key, mapping)
mapping = {}
redis_key = player_name
mapping['discord_id'] = discord_id
mapping[job] = character_name
pipe.hmset(redis_key, mapping)
pipe.execute()
return await self.send_message(message.channel, '紀錄總表完成')
elif content.startswith('seria anton rollcall'):
cmd_args = content.split(' ')
if len(cmd_args) != 5:
await self.send_message(message.channel, '指令錯誤')
return
weekday, group_number = cmd_args[3], int(cmd_args[4])
weekday_mapping = {
'wed': config.WorkSheetType.AntonRaidWed,
'sat': config.WorkSheetType.AntonRaidSat,
'sun': config.WorkSheetType.AntonRaidSun
}
if weekday not in weekday_mapping and not (1 <= group_number <=
16):
await self.send_message(message.channel, '指令錯誤')
return
_, _, group_cell_range = config.GROUP_INFO_SEQUENCE[group_number -
1]
msg = f'{group_number} 團該集合囉！\n\n'
antion_raid_worksheet = spreadsheet.worksheet_by_title(
weekday_mapping[weekday])
group_matrix = antion_raid_worksheet.range(group_cell_range,
returnas='matrix')
for team_index, team in enumerate(group_matrix, start=1):
msg += f'{team_index} 隊:\n'
for player_with_job in team:
if not player_with_job:
continue
prog = player_with_job_pattern.match(player_with_job)
if prog is None:
msg += f'{player_with_job} <-- 文字分析錯誤\n'
continue
matched_keyword_mapping = prog.groupdict()
player = matched_keyword_mapping['player']
job = matched_keyword_mapping['job']
discord_id = redis_db.hget(player, 'discord_id')
character_name = redis_db.hget(player, job)
member = self.discord_server.get_member(discord_id)
player = member.mention if member is not None else player
if character_name:
msg += f'{character_name}({job}) cc {player}\n'
else:
msg += f'{character_name}({job}) cc {player} <-- 您的出戰角色於總表查無資料，請於總表修正，謝謝\n'
msg += '\n'
await self.send_message(message.channel, msg)
return await self.change_presence(game=Game(
name=f'Anton {group_number} 團，出征！'))
elif content.startswith('seria status reset'):
await self.change_presence()
return
elif content.startswith('seria anton check'):
cmd_args = content.split(' ')
if len(cmd_args) != 4:
return await self.send_message(message.channel, '指令錯誤')
weekday = cmd_args[3]
weekday_mapping = {
'wed': config.WorkSheetType.AntonRaidWed,
'sat': config.WorkSheetType.AntonRaidSat,
'sun': config.WorkSheetType.AntonRaidSun
}
if weekday not in weekday_mapping:
return await self.send_message(message.channel, '指令錯誤')
worksheet_title = weekday_mapping[weekday]
await self.send_message(message.channel,
f'檢查 {worksheet_title} 的團表')
antion_raid_worksheet = spreadsheet.worksheet_by_title(
worksheet_title)
YELLOW = (1, 1, 0, 0)
player_with_job_list = []
player_with_job_error_list = []
group_error_collection = {}
for group_number, group_number_cell_pos, group_cell_range in config.GROUP_INFO_SEQUENCE:
group_number_cell = Cell(group_number_cell_pos,
worksheet=antion_raid_worksheet)
if group_number_cell.color != YELLOW:
break
player_list = []
player_error_list = []
group = antion_raid_worksheet.range(group_cell_range,
returnas='matrix')
for team in group:
for player_with_job in team:
if not player_with_job:
continue
if player_with_job in player_with_job_list:
player_with_job_error_list.append(player_with_job)
else:
player_with_job_list.append(player_with_job)
prog = player_with_job_pattern.match(player_with_job)
if prog is None:
continue
matched_keyword_mapping = prog.groupdict()
player = matched_keyword_mapping['player']
if player in player_list:
player_error_list.append(player)
else:
player_list.append(player)
if player_error_list:
group_error_collection[
group_number] = player_error_list
if player_with_job_error_list or group_error_collection:
msg = ''
if player_with_job_error_list:
player_with_job_error_list = ', '.join(
player_with_job_error_list)
msg += f'* 總表重複人物： {player_with_job_error_list}\n'
if group_error_collection:
for group_number, players in group_error_collection.items(
):
players = ', '.join(players)
msg += f'* {group_number} 團表重複人物： {players}\n'
return await self.send_message(message.channel, msg)
else:
return await self.send_message(message.channel,
'沒有任何重複安排的人員唷~！ :heart:')
def new_cell(self, value):
return Cell((self.row + 1, self.col + 1), value)