async def book(self, ctx, duration='1'):
"""
Books an account.
Arguments:
duration The duration(in h) to book the account for.
"""
book_duration = 1
if duration.isnumeric() and int(duration) > 0:
book_duration = int(duration)
if book_duration > BOOKING_DURATION_LIMIT:
raise BookingDurationLimitExceededError(
f"Can not book a account for longer than 12 hours. ({duration} > 12 By the way :stuck_out_tongue_winking_eye:)"
)
async with dbPool.acquire() as conn:
url = await conn.fetchval(
"SELECT url FROM sheet_urls WHERE fk = (SELECT id FROM guilds WHERE guild_id = $1);",
ctx.guild.id)
if url is None:
raise NoSheetsUrlException(
"There is no google sheets url associated with this guild.")
sheet_data = await SheetData.from_url(self.bot, ctx, url)
# Necessary because author.nick is None if the user has not changed his name on the server
name = ctx.author.name
if ctx.author.nick is not None:
name = ctx.author.nick
# Try to assign accounts to the person that last had it as often as possible.
for account in sheet_data.accounts:
if account.last_user == name:
if account.is_booked:
await ctx.reply(
f"You have already been assigned: `{account.name}`.\n"
"Please check your PMs for the login details.")
return
else:
await sheet_data.insert_bookings(self.bot, ctx, [account],
book_duration)
await ctx.author.send(embed=account.embed)
return
# If the user does not have any prior accounts, assign the first free one
for account in sheet_data.accounts:
if account.is_booked:
continue
else:
account.last_user = name
await sheet_data.insert_bookings(self.bot, ctx, [account],
book_duration)
await ctx.author.send(embed=account.embed)
return
# If the function made it this far, there are no free accounts available
await ctx.author.send(
"```There are currently no free accounts.\nIf you need one urgently, talk to your OVO rep.```"
)
async def _get_accounts(self):
"""Parses accounts out of the raw data"""
async with dbPool.acquire() as conn:
utcoffset = await conn.fetchval("SELECT utcoffset FROM guilds WHERE guild_id = $1;", self.ctx.guild.id)
accounts = []
# This is for writing to sheet later. Don't need to iterate over accounts again, if just save row index of account
account_row = 2
for row in self.raw_data[1:]:
name, password = row[0:2]
indexed_row = enumerate(row)
last_user = None
for index, entry in reversed(list(indexed_row)):
if index > 1:
if entry != "":
match = re.match(
r"(.+)\((\d?\d:\d\d[AP]M)(?:-(\d?\d:\d\d[AP]M))?\)", entry)
if match:
last_user = match.group(1)
from_time_str = match.group(2)
to_time_str = match.group(3)
else:
raise InvalidSheetsValue("There seems to be a time (username) formatting error in the google sheets document.")
date_str = self.raw_data[:1][0][index]
from_datetime_str = date_str + "_" + from_time_str
try:
last_booked_from = datetime.datetime.strptime(
from_datetime_str, "%m/%d/%Y_%I:%M%p")
last_booked_from = last_booked_from.replace(tzinfo=datetime.timezone(
datetime.timedelta(hours=utcoffset))) # Makes datetime object timezone aware
last_booked_to = None
if to_time_str:
to_datetime_str = date_str + "_" + to_time_str
last_booked_to = datetime.datetime.strptime(
to_datetime_str, "%m/%d/%Y_%I:%M%p")
last_booked_to = last_booked_to.replace(tzinfo=datetime.timezone(
datetime.timedelta(hours=utcoffset))) # Makes datetime object timezone aware
# Handle booking across days
if last_booked_from > last_booked_to:
last_booked_to += datetime.timedelta(
hours=24)
break
except ValueError:
raise InvalidSheetsValue("There seems to be a date (top row) or time (username) formatting error in the google sheets document.")
else:
last_user = None
last_booked_from = None
last_booked_to = None
accounts.append(Account(name, password, last_user,
last_booked_from, last_booked_to, account_row))
account_row += 1
return accounts
async def insert_bookings(self, bot: commands.Bot, ctx: commands.Context, url: str, accounts_to_write: List[Account], book_duration: int):
# Will need to compare dates
async with dbPool.acquire() as conn:
utcoffset = await conn.fetchval("SELECT utcoffset FROM guilds WHERE guild_id = $1;", ctx.guild.id)
raw_dates = self.raw_data[0]
indexed_dates = enumerate(raw_dates)
last_date = None
last_index = len(raw_dates)
for index, date in reversed(list(indexed_dates)):
try:
last_date = datetime.datetime.strptime(date, "%m/%d/%Y")
last_index = index + 1
break
except ValueError:
logging.info(f"`{date}` could not be parsed when when inserting booking")
# Compare last date, so can write to same column
now = datetime.datetime.now(tz=datetime.timezone(datetime.timedelta(hours=utcoffset)))
today = now.date()
last_account_booked_date = None
for account in accounts_to_write:
if account.last_booked_to is None:
continue
if last_account_booked_date is None:
last_account_booked_date = account.last_booked_to.date()
continue
if account.last_booked_to.date() > last_account_booked_date:
last_account_booked_date = account.last_booked_to.date()
create_new_column = False
# If last date in sheet is before today, create new row
if last_date is None or today > last_date.date():
last_date = today
last_index = len(raw_dates) + 1
create_new_column = True
# if last day in sheet is today, but account was already booked for today, create new date column
elif last_account_booked_date and last_account_booked_date == today:
last_date = today
last_index = len(raw_dates) + 1
create_new_column = True
# Add new date column if needed
if create_new_column:
await bot.loop.run_in_executor(None, self._write_sheet_data, url, 1, last_index, last_date.strftime("%m/%d/%Y"))
# Prepare data to write
hrs_now = now.strftime("%I:%M%p")
hrs_after_x = (now + datetime.timedelta(hours=book_duration)).strftime("%I:%M%p")
for account in accounts_to_write:
write_data = f"{account.last_user}({hrs_now}-{hrs_after_x})"
write_row = account.account_row
write_col = last_index
await bot.loop.run_in_executor(None, self._write_sheet_data, url, write_row, write_col, write_data)
async def account(self, ctx):
async with dbPool.acquire() as conn:
url = await conn.fetchval("SELECT url FROM sheet_urls WHERE fk = (SELECT id FROM guilds WHERE guild_id = $1);", ctx.guild.id)
if url is None:
raise NoSheetsUrlException(
"There is no google sheets url associated with this guild.")
sheet_data = await SheetData.from_url(self.bot, ctx, url)
account = await sheet_data.user_has_account()
if account is not None:
await ctx.reply(f"Your currently assigned account is: `{account.name}`.\n"
"Please check your PMs for the login details.")
else:
await ctx.reply("You have not been assigned any accounts for today.\n"
"Please use the `!account book` command or ask your OVO rep for account assignment.")
async def distribute_accounts(self, ctx: commands.Context, *args):
"""
Distributes accounts to all mentioned users.
Arguments:
force Distributes accounts regardless of prior allocation
duration The duration(in h) to book the account for.
"""
force = False
book_duration = 1
for arg in args:
if arg in ["force", "Force"]:
force = True
elif arg.isdigit() and int(arg) > 0:
book_duration = int(arg)
if book_duration > BOOKING_DURATION_LIMIT:
raise BookingDurationLimitExceededError(f"Can not book a account for longer than 12 hours. ({book_duration} > 12 By the way :stuck_out_tongue_winking_eye:)")
async with dbPool.acquire() as conn:
url = await conn.fetchval("SELECT url FROM sheet_urls WHERE fk = (SELECT id FROM guilds WHERE guild_id = $1);", ctx.guild.id)
if url is None:
raise NoSheetsUrlException(
"There is no google sheets url associated with this guild.")
sheet_data = await SheetData.from_url(self.bot, ctx, url)
available_accounts = []
# This is done in effort to allow users to consistently book same accounts
mentioned_users_accounts = {}
for member in ctx.message.mentions:
name = member.nick if member.nick is not None else member.name
mentioned_users_accounts[name] = {"member": member}
# Find accounts that are not booked or were booked for some of mentioned users or assign all if force flag is set
for account in sheet_data.accounts:
if account.last_user in mentioned_users_accounts:
mentioned_users_accounts[account.last_user]["account"] = account
elif force or not account.is_booked:
available_accounts.append(account)
# Shuffle available accounts so more accounts are being used
random.shuffle(available_accounts)
accounts_to_assign = []
for member_name, pairing in mentioned_users_accounts.items():
member = pairing["member"]
# If user already has previous account, check if its booked and send corresponding info
if "account" in pairing:
account = pairing["account"]
if account.is_booked:
await member.send(f"You have already been assigned: `{account.name}`.\nPlease check your PMs for the login details.")
else:
accounts_to_assign.append(account)
await member.send(embed=account.embed)
await ctx.author.send(f"Member {member_name} has been assigned account {account.name}.")
# If user doesnt have previously assigned account, find available and send
elif (len(available_accounts) > 0):
account = available_accounts.pop()
account.last_user = member_name
accounts_to_assign.append(account)
await member.send(embed=account.embed)
await ctx.author.send(f"Member {member_name} has been assigned account {account.name}.")
# In case if ran out of accounts, notify requester about that
if len(available_accounts) == 0:
await ctx.author.send("You have used all available account!")
else:
await ctx.author.send(f"Can not assign account to {member_name}, no more available accounts left!")
await sheet_data.insert_bookings(self.bot, ctx, url, accounts_to_assign, book_duration)