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_personal_items_of_group(self, user_id, group_id):
"""
Pascal
gets all assigned listentries from the database for a specific user id, group id combination
: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.User_ID, Retailer.name as 'retailer', 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 (User_ID={1} AND (bought is NULL))".format(
group_id, user_id)
cursor.execute(statement)
tuples = cursor.fetchall()
for (id, name, category, amount, unit, user_id, retailer,
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_user(user_id)
listentry.set_retailer(retailer)
listentry.set_article(article_id)
listentry.set_shoppinglist(None)
listentry.set_group(group_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
def update(self, listentry):
"""
Pascal le:ListEntry
Niklas
updates an existing listentry in the database
if it does not exist a new one is created
:return: a single listentry bo
"""
le = ListEntry()
le = self.find_by_key(listentry.get_id())
if listentry.get_article() != "" and listentry.get_article(
) is not None:
le.set_article(listentry.get_article())
if (listentry.get_retailer() != "" or listentry.get_retailer()
is not None) and listentry.get_retailer() != "random-string":
le.set_retailer(listentry.get_retailer())
if listentry.get_shoppinglist() != "" and listentry.get_shoppinglist(
) is not None:
le.set_shoppinglist(listentry.get_shoppinglist())
if listentry.get_user() != "" or listentry.get_user() is not None:
le.set_user(listentry.get_user())
if listentry.get_group() != "" or listentry.get_group() is not None:
le.set_group(listentry.get_group())
if listentry.get_amount() != "" or listentry.get_amount() is not None:
le.set_amount(listentry.get_amount())
if listentry.get_unit() != "" or listentry.get_unit() is not None:
le.set_unit(listentry.get_unit())
if (listentry.get_buy_date() != "" and listentry.get_buy_date()
is not None) and listentry.get_retailer() != "random-string":
le.set_buy_date(listentry.get_buy_date())
try:
cursor = self._cnx.cursor()
if le.get_unit() is None:
unit = "NULL"
else:
unit = "'" + le.get_unit() + "'"
if le.get_retailer() is None:
retailer = 'NULL'
else:
retailer = le.get_retailer()
if le.get_amount() is None:
amount = 'NULL'
else:
amount = le.get_amount()
if le.get_shoppinglist() is None:
shoppinglist = "NULL"
else:
shoppinglist = le.get_shoppinglist()
if le.get_user() is None:
user = '******'
else:
user = le.get_user()
if le.get_buy_date() is None and listentry.get_buy_date(
) != "random-string":
buydate = 'NULL'
else:
date = datetime.date.today()
buydate = "'" + date.strftime("%Y/%m/%d") + "'"
command = """UPDATE Listentry SET Article_ID={0}, Retailer_ID={1}, Shoppinglist_ID={2}, User_ID={3}, Group_ID={4}, amount={5}, unit={6}, bought={7} WHERE ID={8}""".format(
le.get_article(), retailer, shoppinglist, user, le.get_group(),
amount, unit, buydate, le.get_id())
print(command)
cursor.execute(command)
self._cnx.commit()
cursor.close()
return listentry
except Exception as e:
print(str(e))
return "Error in update ListEntry ListEntryMapper: " + str(e)