def select_winners(self, divide_prize):
"""
Selects user that had at least one lottery ticket that matched at least 3 numbers, assigns prize
and saves them to DB.
:param divide_prize: Dictionary that stores amount of matches(keys) and amount of winning tickets(values)
:return: None
"""
winners = database_cursor(sql='''SELECT * FROM
(SELECT t.user_id, tn.ticket_id, COUNT(lo.number) AS matching
FROM tickets t
LEFT JOIN ticket_numbers tn ON t.ticket_id=tn.ticket_id
LEFT JOIN lottery_outcomes lo ON t.lottery_id=lo.lottery_id AND tn.number=lo.number
WHERE t.lottery_id=%s
GROUP BY t.user_id, tn.ticket_id)
AS lvl WHERE matching > %s;''',
variable=(self.id, 2),
cursor_type='fetchall')
for winner in winners:
database_cursor(
sql=
'''INSERT INTO lottery_winners VALUES(%s, %s, %s, %s, %s);''',
variable=(None, self.id, winner['ticket_id'],
winner['user_id'],
divide_prize[str(winner['matching'])]))
def set_inactive(self):
"""
Sets object of lottery class to inactive.
:return: None
"""
database_cursor(
sql='''UPDATE lottery SET active=%s WHERE lottery_id=%s;''',
variable=(0, self.id))
def set_inactive(self):
"""
Sets user object stored in DB to inactive.
:return: None
"""
database_cursor(
sql='''UPDATE users SET active=%s WHERE user_id=%s;''',
variable=(0, self.id))
def update(self, first_name, last_name, email):
"""
Updates user object stored in DB.
:param first_name: User new first name
:param last_name: User new last name
:param email: User new email
:return: None
"""
database_cursor(
sql='''UPDATE users
SET first_name=%s,
last_name=%s,
email=%s
WHERE user_id=%s;''',
variable=(first_name, last_name, email, self.id))
def make_draw(self):
"""
Selects 6 random numbers in range 1-49 and saves them to DB.
:return: None
"""
# Selects random numbers
draw_numbers = list()
while len(draw_numbers) < 6:
random_number = randint(1, 49)
if random_number in draw_numbers:
continue
draw_numbers.append(random_number)
# Saves selected numbers to DB
for number in draw_numbers:
database_cursor(
sql='''INSERT INTO lottery_outcomes VALUES (%s, %s, %s);''',
variable=(None, self.id, number))
def create(cls, lottery_id, user_id, numbers):
"""
Creates Ticket object and stores it in DB.
:param lottery_id: Lottery id that indicates to which lottery ticket was bought
:param user_id: User id that indicates to which user ticket belong
:param numbers: List of numbers chosen by user
:return: Ticket object saved in DB
"""
ticket_id = database_cursor(
sql='''INSERT INTO tickets VALUES (%s, %s, %s, %s);''',
variable=(None, lottery_id, user_id, TODAY),
cursor_type='last_id')
for number in numbers:
database_cursor(
sql='''INSERT INTO ticket_numbers VALUES (%s, %s, %s);''',
variable=(None, ticket_id, number))
return cls(ticket_id, lottery_id, user_id, TODAY, numbers)
def get(cls, lottery_id):
"""
Select lottery class object from DB.
:param lottery_id: ID of lottery class object stored in DB
:return: Lottery class object if exists otherwise returns None
"""
lottery = database_cursor(
sql='''SELECT * FROM lottery WHERE lottery_id=%s;''',
variable=(lottery_id, ),
cursor_type='fetchone')
return cls(**lottery) if lottery else None
def get_all():
"""
Finds all active users stored in DB.
:return: List of all active users
"""
users = database_cursor(
sql='''SELECT * FROM users WHERE active=%s;''',
variable=(1,),
cursor_type='fetchall')
return list(users)
def get(cls, user_id):
"""
Finds user objects stored in DB.
:param user_id: User id
:return: User object if it exists in DB otherwise returns None
"""
user = database_cursor(
sql='''SELECT * FROM users WHERE user_id=%s;''',
variable=(user_id,),
cursor_type='fetchone')
return cls(**user) if user else None
def create(cls, prize, lottery_date):
"""
Creates and saves to DB lottery class object.
:param prize: cumulated amount of money that will be divided between winners
:param lottery_date: date of lottery draw
:return: Lottery class object
"""
lottery_id = database_cursor(
sql='''INSERT INTO lottery VALUES (%s, %s, %s, %s);''',
variable=(None, prize, lottery_date, 1),
cursor_type='last_id')
return cls(lottery_id, prize, lottery_date, 1)
def create(cls, first_name, last_name, email, password, **kwargs):
"""
Creates User object and saves it to DB.
:param first_name: user first name
:param last_name: user last name
:param email: user email
:param password: user password
:return: User object
"""
from app import bcrypt
hashed_psw = bcrypt.generate_password_hash(password).decode('utf-8')
user_id = database_cursor(
sql='''INSERT INTO users VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);''',
variable=(None, first_name, last_name, email, 0, hashed_psw, None, 0, 1),
cursor_type='last_id')
return cls(user_id, first_name, last_name, email, 0, hashed_psw, None, 0, 1)
def divide_prize(self):
"""
Checks how many tickets had at least 3 matches and divide lottery prizes
based on amount of matches and amount of winning tickets.
:return:
- None if no number from ticket matched numbers from lottery draw
- Dictionary that stores amount of matches(keys) and amount of winning tickets(values)
"""
outcomes = database_cursor(
sql='''SELECT category, COUNT(category) AS winners FROM
(SELECT t.user_id, tn.ticket_id, COUNT(lo.number) AS category
FROM tickets t
LEFT JOIN ticket_numbers tn ON t.ticket_id=tn.ticket_id
LEFT JOIN lottery_outcomes lo ON t.lottery_id=lo.lottery_id AND tn.number=lo.number
WHERE t.lottery_id=%s
GROUP BY t.user_id, tn.ticket_id)
AS lvl WHERE category > %s
GROUP BY category;''',
variable=(self.id, 2),
cursor_type='fetchall')
if not outcomes:
return None
# Dictionary that stores: (keys) amount of numbers that can be matched
# (values) amount of money that will be divided between winners
prizes = {
'6': self.prize * 0.4,
'5': self.prize * 0.1,
'4': self.prize * 0.05,
'3': self.prize * 0.02
}
# Dictionary that stores: (keys) amount of numbers that where matched
# (values) amount of money that will be given to winner
divided_prizes = dict()
for outcome in outcomes:
category = str(outcome['category'])
divided_prizes[category] = prizes[category] / outcome['winners']
self.select_winners(divided_prizes)
return divided_prizes
def get_all(user_id):
"""
Finds all user tickets stored in DB.
:param user_id: User id
:return: List of all user tickets and chosen numbers stored in DB
"""
tickets = database_cursor(
sql=
'''SELECT t.ticket_id, t.lottery_id, t.create_date, GROUP_CONCAT(tn.number) AS numbers
FROM tickets t
LEFT JOIN ticket_numbers tn ON t.ticket_id=tn.ticket_id
WHERE t.user_id=%s
GROUP BY t.ticket_id
ORDER BY t.ticket_id DESC''',
variable=(user_id, ),
cursor_type='fetchall')
# For all user tickets changes founded numbers to sorted list
for ticket in tickets:
numbers = ticket['numbers'].split(',')
numbers.sort()
ticket['numbers'] = numbers
return list(tickets)