def find_by_retailer(self, retailer):
"""
Pascal retailer:Retailer
get a specific listentry by its retailer from the database
:return: a single listentry bo
"""
result = []
cursor = self._cnx.cursor()
cursor.execute(
"SELECT ID, Article_ID, Retailer_ID, Shoppinglist_ID, User_ID, Group_ID, amount, bought,creationdate from `Listentry` WHERE Retailer_ID={}"
.format(retailer))
tuples = cursor.fetchall()
try:
for (id, article_id, retailer_id, shoppinglist_id, user_id,
group_id, amount, bought, cd) in tuples:
le = ListEntry()
le.set_id(id)
le.set_article(article_id)
le.set_retailer(retailer_id)
le.set_shoppinglist(shoppinglist_id)
le.set_user(user_id)
le.set_group(group_id)
le.set_amount(amount)
le.set_buy_date(bought)
le.set_creationdate(cd)
result.append(le)
print(result)
except IndexError:
result = None
self._cnx.commit()
cursor.close()
return result
def post(self):
"""
Creates an specific ListEntry object.
"""
adm = ShoppingAdministration()
proposal = ListEntry.from_dict(api.payload)
if proposal is not None:
le = ListEntry()
le.set_id(proposal.get_id())
le.set_article(proposal.get_article())
le.set_retailer(proposal.get_retailer())
le.set_user(proposal.get_user())
le.set_amount(proposal.get_amount())
le.set_unit(proposal.get_unit())
le.set_buy_date(proposal.get_buy_date())
le.set_group(proposal.get_group())
le.set_shoppinglist(proposal.get_shoppinglist())
res = adm.insert_listentry(le)
return res, 200
else:
return "", 500
def find_by_key(self, key):
"""
Niklas key:int
gets the specific listentry with that specific from the database
:return: a single listentry bo
"""
result = None
cursor = self._cnx.cursor()
cursor.execute(
"SELECT ID, Article_ID, Retailer_ID, Shoppinglist_ID, User_ID, Group_ID, amount, bought, creationdate from `Listentry` WHERE ID={}"
.format(key))
tuples = cursor.fetchall()
print(tuples)
try:
for (id, article_id, retailer_id, shoppinglist_id, user_id,
group_id, amount, bought, cd) in tuples:
le = ListEntry()
le.set_id(id)
le.set_article(article_id)
le.set_retailer(retailer_id)
le.set_shoppinglist(shoppinglist_id)
le.set_user(user_id)
le.set_group(group_id)
le.set_amount(amount)
le.set_buy_date(bought)
le.set_creationdate(cd)
result = le
except IndexError:
result = None
self._cnx.commit()
cursor.close()
return result
def get_all_items_of_group(self, group_id, shoppinglist_id):
"""
Julius -
returns all listentries where group_id and shoppinglist_id are like params
similar too "get_items_of_group" but includes bought items
"""
result = []
cursor = self._cnx.cursor()
statement = "SELECT Listentry.ID, Article.name as 'name', Category.name as 'category', Listentry.amount, Listentry.unit, Listentry.Retailer_ID, Listentry.Shoppinglist_ID as 'shoppinglist_id', Listentry.User_ID as 'user_id', Retailer.name as 'retailer', Listentry.Group_ID as 'group_id', Listentry.Article_ID as 'article_id' FROM Listentry LEFT JOIN Retailer ON Listentry.Retailer_ID = Retailer.ID LEFT JOIN Article ON Listentry.Article_ID = Article.ID LEFT JOIN Category ON Article.CategoryID = Category.ID WHERE (Group_ID={0} AND Shoppinglist_ID={1})".format(
group_id, shoppinglist_id)
cursor.execute(statement)
tuples = cursor.fetchall()
for (id, name, category, amount, unit, retailer_id, shoppinglist_id,
user_id, retailer, group_id, article_id) in tuples:
listentry = ListEntry()
listentry.set_id(id)
listentry.set_name(name)
listentry.set_category(category)
listentry.set_amount(amount)
listentry.set_unit(unit)
listentry.set_retailer_id(retailer_id)
listentry.set_shoppinglist(shoppinglist_id)
listentry.set_purchaser(user_id)
listentry.set_retailer(retailer)
listentry.set_group(group_id)
listentry.set_article(article_id)
result.append(listentry)
self._cnx.commit()
cursor.close()
return result
def get_items_of_group(self, group_id, shoppinglist_id):
"""
Niklas -
gets all listentries from the database for a specific group id, shoppinglist idcombination
:return: a list of listentry bos
"""
result = []
cursor = self._cnx.cursor()
statement = "SELECT Listentry.ID, Article.name as 'name', Category.name as 'category', Listentry.amount, Listentry.unit, Listentry.Retailer_ID, Listentry.Shoppinglist_ID as 'shoppinglist_id', Listentry.User_ID as 'user_id', Retailer.name as 'retailer', Listentry.Group_ID as 'group_id', Listentry.Article_ID as 'article_id' FROM Listentry LEFT JOIN Retailer ON Listentry.Retailer_ID = Retailer.ID LEFT JOIN Article ON Listentry.Article_ID = Article.ID LEFT JOIN Category ON Article.CategoryID = Category.ID WHERE (Group_ID={0} AND Shoppinglist_ID={1} AND (bought is NULL))".format(
group_id, shoppinglist_id)
cursor.execute(statement)
tuples = cursor.fetchall()
for (id, name, category, amount, unit, retailer_id, shoppinglist_id,
user_id, retailer, group_id, article_id) in tuples:
listentry = ListEntry()
listentry.set_id(id)
listentry.set_name(name)
listentry.set_category(category)
listentry.set_amount(amount)
listentry.set_unit(unit)
listentry.set_retailer_id(retailer_id)
listentry.set_shoppinglist(shoppinglist_id)
listentry.set_purchaser(user_id)
listentry.set_retailer(retailer)
listentry.set_group(group_id)
listentry.set_article(article_id)
result.append(listentry)
self._cnx.commit()
cursor.close()
return result
def find_by_date_of_purchase(self, date):
"""
Pascal date:Date
"""
result = []
cursor = self._cnx.cursor()
cursor.execute(
"SELECT ID, Article_ID, Retailer_ID, Shoppinglist_ID, User_ID, Group_ID, amount, bought,creationdate from `Listentry` WHERE bought={}"
.format(date.get_bought))
tuples = cursor.fetchall()
print(tuples)
try:
for (id, article_id, retailer_id, shoppinglist_id, user_id,
group_id, amount, bought, cd) in tuples:
le = ListEntry()
le.set_id(id)
le.set_article(article_id)
le.set_retailer(retailer_id)
le.set_shoppinglist(shoppinglist_id)
le.set_user(user_id)
le.set_group(group_id)
le.set_amount(amount)
le.set_buy_date(bought)
le.set_creationdate(cd)
result.append(le)
print(result)
except IndexError:
result = None
self._cnx.commit()
cursor.close()
return result
def find_all(self):
"""
Niklas
get all listentries from the database
:return: a list of listentry bos
"""
result = []
cursor = self._cnx.cursor()
cursor.execute(
"SELECT ID, Article_ID, Retailer_ID, Shoppinglist_ID, User_ID, Group_ID, amount, bought,creationdate from `Listentry`"
)
tuples = cursor.fetchall()
try:
for (id, article_id, retailer_id, shoppinglist_id, user_id,
group_id, amount, bought, cd) in tuples:
le = ListEntry()
le.set_id(id)
le.set_article(article_id)
le.set_retailer(retailer_id)
le.set_shoppinglist(shoppinglist_id)
le.set_user(user_id)
le.set_group(group_id)
le.set_amount(amount)
le.set_buy_date(bought)
le.set_creationdate(cd)
result.append(le)
except IndexError:
result = None
self._cnx.commit()
cursor.close()
return result