class DTORepository:
# initialize DTORepository
def __init__(self):
self.db = Database()
def __getKeys(self, entity):
props = []
for key in entity.keys():
props.append(key)
return props
# returns the table passed
def selectAll(self, table):
dt = self.db.select(['*'], table)
print('asdf')
print(str(dt))
print('asdf')
return eval(str(dt))
# retrieve information for the get id method on the controller
def insert(self, table, entity):
props = self.__getKeys(entity)
return self.db.insertOne(table, props, entity)
def select(self, table, IDColumn, ID):
dt = self.db.select(['*'], table, f"{IDColumn} = %s", [ID])
print(str(dt))
return eval(str(dt))
def update(self, table, entity, where = '', values = []):
props = self.__getKeys(entity)
return self.db.update(table, props, entity, where, values)
def delete(self, table, where = '', values = []):
dt = self.db.delete(table, where, values)
return eval(str(dt))
class SocialMediaRepository:
# initialize SocialMediaRepository
def __init__(self):
self.db = Database()
self._commonRepository = CommonRepository()
# Lists the different social media platforms that this bot supports
def list_all(self):
try:
# SELECT Title AS Platform, URL AS Link, Link AS Icon
# FROM socialmedias
# JOIN resources ON socialmedias.ResourceID = resources.ResourceID;
socialmedias = self.db.select(['Title AS Platform', 'URL AS Link', 'Link AS Icon'], \
'socialmedias JOIN resources ON socialmedias.ResourceID = resources.ResourceID')
return eval(str(socialmedias)), StatusCodes.OK
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# Adds the specified <socialmedia> platform and <handle> to the sending user's profile
def add_social_media(self, rDiscordUserID, rPlatform, rLink):
try:
# SELECT * FROM socialmedias WHERE Title = 'Facebook';
socialMedia = self.db.select(['SocialMediaID'], 'socialmedias',
'Title = %s', [rPlatform]).getRows()
if len(socialMedia) > 0:
smID = socialMedia[0]['SocialMediaID']
# INSERT INTO discordusersocialmedias (DiscordUserID, SocialMediaID, Handle)
# VALUES ('309176098590294026', '1', 'Nathan Gawith');
if len(
self.db.select(
['*'], 'discordusersocialmedias',
'DiscordUserID = %s AND SocialMediaID = %s',
[rDiscordUserID, smID]).getRows()) > 0:
self.db.update(
'discordusersocialmedias', ['Handle'],
{'Handle': rLink},
'DiscordUserID = %s AND SocialMediaID = %s',
[rDiscordUserID, smID])
else:
self.db.insertOne(
'discordusersocialmedias',
['DiscordUserID', 'SocialMediaID', 'Handle'], {
'DiscordUserID': rDiscordUserID,
'SocialMediaID': smID,
'Handle': rLink
})
return '', StatusCodes.OK
else:
return f"'{rPlatform}' was not found in the socialmedias table", StatusCodes.NOT_FOUND
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# returns information about the specific social media for the specified user
def get_social_media(self, rDiscordUserID, rSocialMediaName):
try:
# SELECT * FROM socialmedias WHERE Title = 'Facebook';
socialMedia = self.db.select(['SocialMediaID'], 'socialmedias',
'Title LIKE %s',
[rSocialMediaName]).getRows()
if len(socialMedia) > 0:
# SELECT Title AS Platform, Handle AS Link, Link AS ICON
# FROM discordusersocialmedias
# INNER JOIN socialmedias ON discordusersocialmedias.SocialMediaID = socialmedias.SocialMediaID
# INNER JOIN resources ON socialmedias.ResourceID = resources.ResourceID
# WHERE DiscordUserID = '123456789123456789' AND Title LIKE 'Facebook';
qeryResult = self.db.select(['Title AS Platform', 'Handle AS Link', 'Link AS ICON'], \
'''discordusersocialmedias
INNER JOIN socialmedias ON discordusersocialmedias.SocialMediaID = socialmedias.SocialMediaID
INNER JOIN resources ON socialmedias.ResourceID = resources.ResourceID''',
'DiscordUserID = %s AND Title LIKE %s', [rDiscordUserID, rSocialMediaName])
return eval(str(qeryResult)), StatusCodes.OK
else:
return f"'{rSocialMediaName}' was not found in the socialmedias table", StatusCodes.NOT_FOUND
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# Adds the specified <socialmedia> platform and <handle> to the sending user's profile
def get_social_medias(self, rDiscordUserID):
try:
return self.get_social_media(rDiscordUserID, '%')
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# deletes from discordusersocialmedias for the specific social media and discorduser
def delete_social_media(self, rDiscordUserID, rSocialMediaName):
try:
# SELECT * FROM socialmedias WHERE Title = 'Facebook';
socialMedia = self.db.select(['SocialMediaID'], 'socialmedias',
'Title LIKE %s',
[rSocialMediaName]).getRows()
if len(socialMedia) > 0:
# SELECT Title AS Platform, Handle AS Link, Link AS ICON
# FROM discordusersocialmedias
# INNER JOIN socialmedias ON discordusersocialmedias.SocialMediaID = socialmedias.SocialMediaID
# INNER JOIN resources ON socialmedias.ResourceID = resources.ResourceID
# WHERE DiscordUserID = '123456789123456789' AND Title LIKE 'Facebook';
if len(self.db.select(['Title AS Platform', 'Handle AS Link', 'Link AS ICON'], \
'''discordusersocialmedias
INNER JOIN socialmedias ON discordusersocialmedias.SocialMediaID = socialmedias.SocialMediaID
INNER JOIN resources ON socialmedias.ResourceID = resources.ResourceID''',
'DiscordUserID = %s AND Title LIKE %s', [rDiscordUserID, rSocialMediaName]).getRows()) > 0:
self.db.delete(
'discordusersocialmedias',
'DiscordUserID = %s AND SocialMediaID = %s',
[rDiscordUserID,
str(socialMedia[0]['SocialMediaID'])])
return 'Success', StatusCodes.OK
else:
return 'This user does not have this specific social media', StatusCodes.NOT_FOUND
else:
return f"'{rSocialMediaName}' was not found in the socialmedias table", StatusCodes.NOT_FOUND
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
class RaffleRepository:
# initialize RaffleRepository
def __init__(self):
self.db = Database()
self._commonRepository = CommonRepository()
# returns JSON representing all of the users participating in this raffle
def __get_discordusers_in_raffle(self, raffleID):
return eval(
str(
self.db.select([
'discordusers.DiscordUserID', 'discordusers.UserName',
'discordusers.Currency', 'discordusers.UserHash',
'resources.Link AS ProfilePicture'
], '''discorduserraffles
LEFT OUTER JOIN discordusers ON discordusers.DiscordUserID = discorduserraffles.DiscordUserID
LEFT OUTER JOIN resources ON resources.ResourceID = discordusers.ResourceID''',
f"discorduserraffles.RaffleID = '{raffleID}'")))
# starts a new raffle
def start_raffle(self, rName, rDiscordUserID, rServerID, rDuration,
rSeedAmount):
try:
userCurrenyQuery = self.db.select(
['Currency'], 'discordusers',
f"DiscordUserID = '{rDiscordUserID}'").getRows()
if len(userCurrenyQuery) > 0:
userCurrency = userCurrenyQuery[0]['Currency']
# make sure the user has enough currency to start this raffle
if userCurrency >= rSeedAmount:
endTime = None
# calculate end datetime of this raffle
if rDuration >= 0:
nowtime = datetime.datetime.now().timestamp()
endTime = str(
datetime.datetime.fromtimestamp(
int(nowtime + (rDuration / 1000))))
# create raffle in Raffles table
if self.db.insertOne(
'raffles', [
'ServerID', 'Name', 'EndTime', 'Currency',
'DiscordUserID'
], {
'ServerID': rServerID,
'Name': rName,
'EndTime': endTime,
'Currency': rSeedAmount,
'DiscordUserID': rDiscordUserID
}):
# get the new RaffleID
maxID = self.db.select(['MAX(RaffleID) AS NewID'],
'raffles').getRows()
newRaffleID = 1
if (len(maxID) > 0):
newRaffleID = maxID[0]['NewID']
# insert into DiscordUserRaffles table
self.db.insertOne(
'discorduserraffles',
['DiscordUserID', 'RaffleID', 'JoinDate'], {
'DiscordUserID': rDiscordUserID,
'RaffleID': newRaffleID,
'JoinDate': str(datetime.datetime.now())
})
# decrement the user's currency
self._commonRepository.subtract_from_user_currency(
rDiscordUserID, rSeedAmount)
# return OK
return '', StatusCodes.OK
else:
# conflict when inserting, so a raffle with this name already exists
return f"A raffle with the name {rName} already exists on this server", StatusCodes.CONFLICT
# the user does not have enough currency to start this raffle
return 'Insufficient funds', StatusCodes.IM_A_TEAPOT
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# adds currency to a raffle
def join_raffle(self, rDiscordUserID, rServerID, rRaffle, rAmount):
try:
userCurrenyQuery = self.db.select(
['Currency'], 'discordusers',
f"DiscordUserID = '{rDiscordUserID}'").getRows()
if len(userCurrenyQuery) > 0:
userCurrency = userCurrenyQuery[0]['Currency']
# make sure the user has enough currency to start this raffle
if userCurrency >= rAmount:
# if a raffle exists on this server with the same name
raffleQueryDataTable = self.db.select(
['RaffleID', 'Currency'], 'raffles',
f"Name = '{rRaffle}' AND ServerID = '{rServerID}'")
print(str(raffleQueryDataTable))
raffleQueryDataTable = raffleQueryDataTable.getRows()
if len(raffleQueryDataTable) == 1:
# get the RaffleID
raffleID = raffleQueryDataTable[0]['RaffleID']
currentCurrency = raffleQueryDataTable[0]['Currency']
# insert into DiscordUserRaffles table
self.db.insertOne(
'discorduserraffles',
['DiscordUserID', 'RaffleID', 'JoinDate'], {
'DiscordUserID': rDiscordUserID,
'RaffleID': raffleID,
'JoinDate': str(datetime.datetime.now())
})
# update the Raffles table
self._commonRepository.add_currency_to_raffle(
raffleID, rAmount)
# decrement the user's currency
self._commonRepository.subtract_from_user_currency(
rDiscordUserID, rAmount)
# query the DB for the return statement
raffle = eval(
str(
self.db.select(
['*'], 'raffles',
f"RaffleID = '{raffleID}'").getRows()[0]))
discordusers = self.__get_discordusers_in_raffle(
raffleID)
# return OK
return {
'DiscordUsers': discordusers,
'Raffle': raffle
}, StatusCodes.OK
else:
# raffle with the passed name was not found on the server
return f"A raffle with the name {rRaffle} was not found on this server",\
StatusCodes.NOT_FOUND
# the user does not have enough currency to start this raffle
return 'Insufficient funds', StatusCodes.IM_A_TEAPOT
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# returns all of the raffles that are currently available on the specified server
def get_raffles(self, rServerID):
try:
raffles = self.db.select(['*'], 'raffles',
f"ServerID = '{rServerID}'").getRows()
# return list of raffleinfos
return [{
'DiscordUsers':
self.__get_discordusers_in_raffle(raffle['RaffleID']),
'Raffle':
eval(str(raffle))
} for raffle in raffles], StatusCodes.OK
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# returns all of the raffles that are currently available on the specified server
def get_historic_raffles(self, rDiscordUserID):
try:
result = self.db.select([
'discorduserraffles.RaffleID',
'CASE WHEN rafflehistory.DiscordUserID IS NOT NULL THEN rafflehistory.DiscordUserID ELSE raffles.DiscordUserID END AS DiscordUserID',
'CASE WHEN rafflehistory.ServerID IS NOT NULL THEN rafflehistory.ServerID ELSE raffles.ServerID END AS ServerID',
'CASE WHEN rafflehistory.Name IS NOT NULL THEN rafflehistory.Name ELSE raffles.Name END AS Name',
'CASE WHEN rafflehistory.EndTime IS NOT NULL THEN rafflehistory.EndTime ELSE raffles.EndTime END AS EndTime',
'CASE WHEN rafflehistory.Currency IS NOT NULL THEN rafflehistory.Currency ELSE raffles.Currency END AS Currency',
'rafflehistory.WinnerDiscordUserID'
], '''
discorduserraffles
LEFT JOIN rafflehistory ON discorduserraffles.RaffleID = rafflehistory.RaffleID
LEFT JOIN raffles ON discorduserraffles.RaffleID = raffles.RaffleID''',
'discorduserraffles.DiscordUserID = %s AND (raffles.DiscordUserID IS NOT NULL OR rafflehistory.DiscordUserID IS NOT NULL)',
[rDiscordUserID])
# return list of raffleinfos
return eval(str(result)), StatusCodes.OK
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# returns all of the raffles that are going to end within the given number of milliseconds
def get_raffles_ending_in_millis(self, rMillis):
try:
# calculate time
nowtime = datetime.datetime.now().timestamp()
endTime = str(
datetime.datetime.fromtimestamp(int(nowtime +
(rMillis / 1000))))
# query Raffles table
raffles = self.db.select(['*'], 'raffles',
f"EndTime < '{endTime}'")
# return list of raffles
return eval(str(raffles)), StatusCodes.OK
except:
# some error has occurred
return '', StatusCodes.INTERNAL_SERVER_ERROR
# ends the specified raffle if the user who
# requested this is qualified to end the raffle
def end_raffle(self, rDiscordUserID, rServerID, rRaffle):
try:
print('end_raffle')
raffle = self.db.select(['*'], 'raffles',\
f"ServerID = '{rServerID}' AND Name = '{rRaffle}'").getRows()
if len(raffle) == 1:
# if the user who is ending this raffle is the bot or
# is the user who started this raffle
if rDiscordUserID in ['0', raffle[0]['DiscordUserID']]:
discordusersinraffle = self.__get_discordusers_in_raffle(
raffle[0]['RaffleID'])
winner = random.choice(discordusersinraffle)
# add the total currency for this raffle to the winner's currency
self._commonRepository.add_to_user_currency(
winner['DiscordUserID'], raffle[0]['Currency'])
nowtime = datetime.datetime.now().timestamp()
endTime = datetime.datetime.fromtimestamp(int(nowtime))
self.db.insertOne(
'rafflehistory', [
'RaffleID', 'ServerID', 'Name', 'EndTime',
'Currency', 'DiscordUserID', 'WinnerDiscordUserID'
], {
'RaffleID': raffle[0]['RaffleID'],
'ServerID': raffle[0]['ServerID'],
'Name': raffle[0]['Name'],
'EndTime': str(endTime),
'Currency': raffle[0]['Currency'],
'DiscordUserID': raffle[0]['DiscordUserID'],
'WinnerDiscordUserID': winner['DiscordUserID']
})
print('before delete')
# delete the raffle
self.db.delete('raffles', 'RaffleID = %s',
[raffle[0]['RaffleID']])
print('after delete')
return {
'Winner': winner,
'RaffleInfo': {
'DiscordUsers': discordusersinraffle,
'Raffle': eval(str(raffle[0]))
}
}, StatusCodes.OK
else:
# only the user who started the raffle or the bot can end a raffle
return 'You do not have the authority to end this raffle',\
StatusCodes.FORBIDDEN
else:
# raffle with the passed name was not found on the server
return f"A raffle with the name {rRaffle} was not found on this server",\
StatusCodes.NOT_FOUND
except Exception as e:
# some error has occurred
return e, StatusCodes.INTERNAL_SERVER_ERROR