def on_loan_for(self, loan_duration):
today = datetime.today().strftime('%Y-%m-%d')
dur_res = self.connection.execute('SELECT * FROM users JOIN borrows ON users.id = borrows.user_id WHERE borrows.return_date IS NULL AND (borrows.borrow_date + \' '+ str(loan_duration) + ' day\'::interval) < \'' + str(today) +'\'')
users = []
for res in dur_res:
user = User(input_tuple=res)
users.append(user.return_as_dict())
users = [dict(t) for t in {tuple(d.items()) for d in users}]
return users
def get_users(self):
select_query = select([self.tables.get_users_table()])
result = self.connection.execute(select_query)
users = []
for res in result:
user = User(input_tuple=res)
users.append(user.return_as_dict())
return users
def get_user(self, user_id):
user_table = self.tables.get_users_table()
select_query = select([user_table]).where(user_table.c.id == user_id)
result = self.connection.execute(select_query)
result = result.fetchone()
if result is None:
return None
else:
user = User(input_tuple=result)
return user.return_as_dict()
def on_loan_for_and_at(self, loan_date, loan_duration):
query = 'SELECT * FROM users JOIN borrows ON users.id = borrows.user_id WHERE (borrows.borrow_date + \''+ str(loan_duration) + 'day\'::interval) < \''+ str(loan_date)+'\'AND (borrows.return_date > \''+str(loan_date)+'\' OR borrows.return_date IS NULL)'
_log.info(query)
loan_res = self.connection.execute(query)
users = []
for res in loan_res:
user = User(input_tuple=res)
users.append(user.return_as_dict())
users = [dict(t) for t in {tuple(d.items()) for d in users}]
return users
def on_loan_at(self, loan_date):
borrow_table = self.tables.get_borrow_table()
user_table = self.tables.get_users_table()
select_query = select(['*']).select_from(user_table.join(borrow_table)).where(
and_(borrow_table.c.borrow_date <= loan_date, or_(borrow_table.c.return_date > loan_date, borrow_table.c.return_date == None)))
loan_res = self.connection.execute(select_query)
users = []
for res in loan_res:
user = User(input_tuple=res)
users.append(user.return_as_dict())
users = [dict(t) for t in {tuple(d.items()) for d in users}]
return users
def get_user_reviews(self, user_id):
users_table = self.tables.get_users_table()
tapes_table = self.tables.get_tapes_table()
review_table = self.tables.get_review_table()
user_query = select(['*']).select_from(users_table).where(
users_table.c.id == user_id
)
user_res = self.connection.execute(user_query)
user_res = user_res.fetchone()
if user_res is None:
return None
user = User(input_tuple=user_res)
result_dict = user.return_as_dict()
get_query = select(['*']).select_from(
tapes_table.join(review_table)).where(review_table.c.user_id == user_id)
result = self.connection.execute(get_query)
results = []
for res in result:
tape = Tape(input_tuple=res)
review_dict = tape.return_as_dict()
review_dict['rating'] = res[-1]
results.append(review_dict)
result_dict['Reviews'] = results
if len(results) == 0:
result_dict['Reviews'] = 'No review for this user'
return result_dict
def get_review(self, tape_id, user_id):
users_table = self.tables.get_users_table()
tapes_table = self.tables.get_tapes_table()
review_table = self.tables.get_review_table()
user_query = select(
['*']).select_from(users_table).where(users_table.c.id == user_id)
user_res = self.connection.execute(user_query)
user_res = user_res.fetchone()
if user_res is None:
return None
user = User(input_tuple=user_res)
result_dict = user.return_as_dict()
get_query = select(['*']).select_from(
tapes_table.join(review_table)).where(
and_(review_table.c.user_id == user_id,
review_table.c.tape_id == tape_id))
result = self.connection.execute(get_query)
result = result.fetchone()
# Returns all the tapes from the sql result
if result is None:
result_dict['Review'] = 'No review for this tape for this user'
else:
tape = Tape(input_tuple=result)
review_dict = tape.return_as_dict()
review_dict['rating'] = result[-1]
result_dict['Review'] = review_dict
return result_dict
def read_json(self):
with open('files/SC-T-302-HONN_2018_Friends.json', 'r') as file:
friends = json.load(file)
with open('files/SC-T-302-HONN_2018_Videotapes.json', 'r') as file:
tapes = json.load(file)
# Because borrows don't have unique ids we have to greate one
current_borrow_id = 1
# Create users
for friend in friends:
user_id = friend['id']
first_name = friend['first_name']
last_name = friend['last_name']
email = friend['email']
phone = friend['phone']
address = friend['address']
user = User(user_id, first_name, last_name, email, phone, address)
self.user_list.append(user)
# Only for friends that have borrowed tapes
if 'tapes' in friend:
for tape in friend['tapes']:
tape_id = tape['id']
borrow_date = tape['borrow_date']
return_date = tape['return_date']
borrow = Borrow(current_borrow_id, user_id, tape_id,
borrow_date, return_date)
current_borrow_id += 1
self.borrow_list.append(borrow)
# Create tapes
for tape in tapes:
tape_id = tape['id']
title = tape['title']
first_name = tape['director_first_name']
last_name = tape['director_last_name']
type = tape['type']
release_date = tape['release_date']
eidr = tape['eidr']
tape = Tape(tape_id, title, first_name, last_name, type,
release_date, eidr)
self.tape_list.append(tape)